sql-server-2005

How to get a data's from one database to other database?

Using SQL 2000, SQL 2005 Old Database Name is – Sysdatabase New Database Name is - Dual_Proone, Dual_Protwo In the above two database table name and column name are different, but values are same. For Example Sysdatabase (Database Name) Person (Table Name) Column Names and values are ID Date 001 23-02-2009 002 24-02-2009 So o...

Can you have a partial Identity column in SQL Server?

I have a table with an Identity column which provides my ticketNumber. I want another table to provide a ticketStepNumber. A ticket may have 0 or more steps, so I won't always be creating a ticketStepNumber. The ticketStepNumber value is a combination of the ticketNumber column (int) and the stepNumber column (int). I'd like to def...

Is this stored procedure thread-safe? (or whatever the equiv is on SQL Server)

With the help of others on SO I've knocked up a couple of Tables and Stored Procedures, this morning, as I'm far from a DB programmer. Would someone mind casting an eye over this and telling me if it's thread-safe? I guess that's probably not the term DBAs/DB developers use but I hope you get the idea: basically, what happens if this s...

SELECT FOR UPDATE with SQL Server

I'm using a Microsoft SQL Server 2005 database with isolation level READ_COMMITTED and READ_COMMITTED_SNAPSHOT=ON. Now I want to use: SELECT * FROM <tablename> FOR UPDATE ...so that other database connections block when trying to access the same row "FOR UPDATE". I tried: SELECT * FROM <tablename> WITH (updlock) WHERE id=1 ...bu...

CTE vs. IN clause performance

Alright SQL Server Gurus, fire up your analyzers. I have a list of titles in application memory (250 or so). I have a database table "books" with greater than a million records, one of the columns of the table is "title" and is of type nvarchar. the "books" table has another column called "ISBN" books.title is not a primary key, is not...

Deleting an entity from a table which is dependent on another table

I have 2 tables MachineGroups and Machines. MachineGroups has columns: MachinegroupID MachineGroupName MachineGroupDesc And Machines has columns: MachineGroupID (FK) MachineID MachineName Machinedesc Now I want to delete a machinegroup but not the ones that have machines in it. So if there are machines it should give an error mes...

Find specific byte combination inside NVARCHAR

We found out that we have a bunch of french strings with incorrect characters. We know that the hexadecimal representation of the character is 0xFDFF. Is there an easy way to SELECT the rows in a table where the NVarchar field contain this character? Thanks ...

Get comma separated value from multiple row in sql server 2005

i have this table Cream ---------- CHOCALATE GREEN TEST want out put in select query like this cream CHOCALATE,GREEN,TEST ...

Update 1 record in TBL1 when all references in TBL2 are equal to a given value

Hi, I have two tables, 1 indexing the details of an email campaign. The second table details the recipients of this campaign, and whether they have responded to the email etc. I need to create a stored procedure that can update the status of the 'master record' in TBL1 when all the references(recipients) in TBL2 have a status >1. The ...

Check if DateTime in DB is more than 90 days old via Stored Procedure

UPDATE Evidently I didn't include enough data, sorry! What I need to do is set 'campaign_Status' = 6 when 'campaign_Date' is more than 90 days old. Hi, I have a column (campaign_Date) which stores a DATETIME. Using a Stored Procedure I need to check if the stored date is 90 days old (or more). Any help would be great. Thanks. ...

How do I update n rows in a table?

I need to update the first N rows in a table meeting a condition. I know I can do an Update Top N... but the problem is that N is in a @variable. UPDATE TOP @N SET ... doesn't work. Is there a way to do this that I am just missing? No specific table definitions here because it doesn't matter what the columns are.. If I can do it for...

At which stage does SqlBulkCopy check constraints?

If SqlBulkCopyOptions.CheckConstraints option is set for SqlBulkCopy insert, does it check the constraints separately after each record, or after all records are inserted? I have a check constraint that compares some columns to the parent row (through a UDF). I'm using MS Sql Server 2005. The documentation at http://msdn.microsoft.com/e...

Memory effective way to read BLOB data in C#/SQL 2005

What's the most memory effective way to read an SQL 2005 image field using C# 3.5? Right now I have a (byte[])cm.ExecuteScalar("..."). If I could not read all field content into memory, that would be nice. ...

IF EXISTS, THEN SELECT ELSE INSERT AND THEN SELECT

How do you say the following in Microsoft SQL Server 2005: IF EXISTS (SELECT * FROM Table WHERE FieldValue='') THEN SELECT TableID FROM Table WHERE FieldValue='' ELSE INSERT INTO TABLE(FieldValue) VALUES('') SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY() END IF What I'm trying to do is to see if there is a blank fie...

Timeout expired. The timeout period elapsed prior to ...

I am trying to access SQL 2005 database.When I am trying to Login it is throwing the folllowing error. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. I was connected to the DB and was working on the Queries/procs , when suddenly i am unable to access the DB. One of ...

How can I create two temporary tables with the same structure without write twice?

How can I create two temporary tables with the same structure without write twice? Something like that: DECLARE @TEST_TABLE1, @TEST_TABLE2 TABLE ( FIELD1 INT, FIELD2 INT ) and NO: DECLARE @TEST_TABLE1 TABLE ( FIELD1 INT, FIELD2 INT ) DECLARE @TEST_TABLE2 TABLE ( FIELD1 INT, FIELD2 INT ) ...

How can I convert ticks to a date format?

I am converting a ticks value to a date like this: Convert(datetime, (MachineGroups.TimeAdded - 599266080000000000)/864000000000); Using this i get: 9/27/2009 10:50:27 PM But I want just the date in this format: October 1, 2009 My sample ticks value is 633896886277130000 What is the best way to do this? ...

SQL Statement(s)

If I have the following table: CREATE TABLE #temp ( id int, num int, question varchar(50), qversion int ); INSERT INTO #temp VALUES(1, 1, 'Question 1 v1', 1); INSERT INTO #temp VALUES(2, 1, 'Question 1 v2', 2); INSERT INTO #temp VALUES(3, 2, 'Question 2 v1', 1); INSERT INTO #temp VALUES(4, 2, 'Question 2 v2', 2); INSERT...

Performing multiplication in SQL SERVER(SET BASED APPROACH)

Suppose I have a table like the following: tblNumbers Numbers 4 5 3 6 Using SET BASED approach how can I perform a multiplication so the output will be: Output 360 N.B~ There is no hard and fast rule that there will be only four numbers, but I'd prefer the answer to be using a CTE and/or correlated subquery. ...

Ensure data integrity in SQL Server

I have to make some changes in a small system that stores data in one table as following: TransId TermId StartDate EndDate IsActiveTerm ------- ------ ---------- ---------- ------------ 1 1 2007-01-01 2007-12-31 0 1 2 2008-01-01 2008-12-31 0 1 3 2009-01-01 2009-12-31 1 1 4...