sql-server

SQL Query Theory Question...

I have a large historical transaction table (15-20 million rows MANY columns) and a table with one row one column. The table with one row contains a date (last processing date) which will be used to pull the data in the trasaction table ('process_date'). Question: Should I inner join the 'process_date' table to the transaction table or...

Write utf-8 to a sql server Text field using ADO.Net and maintain the UTF-8 bytes

I have some xml encoded as UTF-8 and I want to write this to a Text field in SQL Server. UTF-8 is byte compatible with Text so it should be able to do this and then read out the xml later still encoded as utf-8. However special characters such as ÄÅÖ, which are multi-byte in UTF-8 get changed on the way. I have code like this: byte[] ...

Get DTS Step Description from TSQL?

I am trying to get the DTS Step Name/Description of a given DTS Package in SQL2000. I am not able to see anything in the msdb database. I can see the initial DTS name, however I dont see anything to get the details. Anybody knows where this info is stored? ...

What is the performance penalty of XML data type in SQL Server when compared to NVARCHAR(MAX)?

I have a DB that is going to keep log entries. One of the columns in the log table contains serialized (to XML) objects and a guy on my team proposed to go with XML data type rather than NVARCHAR(MAX). This table will have logs kept "forever" (archiving some very old entries may be considered in the future). I'm a little worried about th...

Use a specific database depending on condition

Can I somehow use a specific database given a specific condition? To clarify I will give a naive example: CASE WHEN @dbnum = 1 THEN USE Db1 ELSE USE DefaultDb END ...

Permissions required to run REPLMERG.EXE

We use merge replication in one of our programs and I would like to allow our users to force synchronization of their laptops with the publisher on an as-needed basis (we are using push subscriptions). I got this working using REPLMERG.EXE (see my previous question). However, when the users trid to run the script they received the foll...

pyodbc on SQL Server - How can I do an insert and get the row ID back?

I'm using pyodbc with SQL Server 2000. I want to be able to insert a row and get the auto incremented row id value back? Any ideas? Here's what I have so far: cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')") cnxn.commit() ...

Lib for LIMIT statement in SQL SERVER?

Is there some lib or function i can use to take my basic sql statement and transform the limit statement to a sql server compatible statement? ...

Convert dependencies to point to View instead of Table

I currently have a SQL Server 2008 database in which I am planning to separate out some tables to other databases. I want to be able to replace all references to the separated tables from the original database using views. Is there a better way (other than manually changing all FK and SProc references) to switch all the dependencies to...

Doctrine + SQL Server Stored Procedures.

How can I call a stored procedure using Doctrine? ...

SQL Server cast fails with arithmetic overflow

According to the entry for decimal and numeric data types in SQL Server 2008 Books Online, precision is: p (precision) The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precisio...

How to commit inside a CURSOR Loop?

Hi, I am trying to see if its possible to perform Update within a cursor loop and this updated data gets reflected during the second iteration in the loop. DECLARE cur CURSOR FOR SELECT [Product], [Customer], [Date], [Event] FROM MyTable WHERE [Event] IS NULL OPEN cur FETCH NEXT INTO @Product, @Customer, @Date, @Event WHILE @@FETCH_STA...

Help with constructing a conditional SQL statement

Hello, I want to construct a SELECT statement with a conditional IF. Like, IF there is no records with the language code 'Swedish': SELECT * FROM Entries WHERE Language = 'Swedish' THEN use 'English' SELECT * FROM Entries WHERE Language = 'English' How would I construct this statement using MSSQL? Thanks, Stefan ...

SQL Server stored procedure meaning

Hi. I'm developing a simple database architecture in VisualParadigm and lately ran over next code excerpt. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'getType') AND type in (N'P', N'PC')) DROP PROCEDURE getType; Next goes my stored procedure: CREATE PROCEDURE getType @typeId int AS SELECT * FROM type t WHERE ...

How do i use the top keyword in an insert statement?

I was using it as LIMIT when I got the exception Incorrect syntax near the keyword 'TOP'. Maybe I can omit it in this case without a problem, but if I couldn't where do I put top? INSERT INTO [user_data] (...) SELECT ... @14 WHERE not exists (SELECT * FROM [user_data] WHERE [email] = @15 OR [name] = @16 OR [unconfirmed_email] = @17 ...

Is it faster to compute values in a query, call a Scalar Function (decimal(28,2) datatype) 4 times, or pull it all back in a Table Function?

I have a handful of queries I need to write in SQL Server 2005. Each Query will be calculating 4 unit cost values based on a handful of (up to 11) fields. Any time I want 1 of these 4 unit cost values, I'll want all 4. Which is quicker? Computing in the SQL Query ((a+b+c+d+e+f+g+h+i)/(j+k)), calling ComputeScalarUnitCost(datapoint.ID...

Build an unbound form in Acess 2007

I have an access application that has a form that allows the user to enter case notes. The main field of this form is tied to a SQL Server varchar(MAX) field in the source table. Since the users switched to Access 2007, their program keeps crashing when they are on the case notes form. As a possible solution to this problem, I would like...

make select @@IDENTITY; a long?

I am grabbing the last rowid and i am doing this select @@IDENTITY pk = (long)cmd.ExecuteScalar(); I get an invalid typecast bc this is int instead of long. Why doesnt this return a long? can i make it return long? Solution for now is to use pk = Convert.ToInt64(cmd.ExecuteScalar()); ...

Violation of UNIQUE KEY constraint on null

Exception: Violation of UNIQUE KEY constraint 'UQ__user_dat__AB6E61641273C1CD'. Cannot insert duplicate key in object 'dbo.user_data'. The statement has been terminated. AB6E61641273C1CD = email which is unique and NULL My SQL statement does NOT insert a value for email. So i assume it will have the default as null (although i never...

SQL query that returns columns form table A plus a bit column that specifies with the PK of table A exists in table B

How can I write an efficient SQL query that returns columns form table A plus a bit column that specifies whether the PK of table A exists in table B? I am using MS SQLServer 2005. Thanks. ...