tsql

How to increment DateTime field in SQL Server 2005 by a month?

I have a DateTime field in SQL Server for some products expiration (ExpirationDate). I need to increment all the items manually, and set their expiration a month later than the date stored in the field currently. How can I do that? ...

Incorrect syntax near 'E'.

How can I correct the following so that I don't receive a syntax error in Microsoft SQL Server 2005? UPDATE Emp E SET UserName = Left(FirstName,1)+LastName WHERE EmpID=1 AND NOT EXISTS( SELECT * FROM Emp WHERE UserName=Left(E.FirstName,1)+E.LastName ) ...

SQL Server 2005 Pivot Table question

I have an Entity-Value set of tables, and would like to pivot the results. Here's the effect that I'm looking for, except you see that the SELECT stmt using Common Table Expressions isn't working exactly right yet. My question is: Am I on the right track, or is there some sort of easy pivot command? USE TempDB Declare @Person TABLE( Per...

SQL Server 2005 Cursors

what are some of the cases that will cause a SQL Server 2005 cursor to deallocate prematurely? ...

Find GUID in database

I am currently working on a project where some (significant) data corruption has occurred. Specifically the database is using GUIDs as primary keys on most tables, however it is not enforcing data integrity between those tables (due to a long-winded "this was bought from another company and integrated with our stuff" discussion). There...

SQL Server 2005 T-SQL remap columns

I have an application that is ready to go live, once we take data from a MS Access DB and import it into SQL Server 2005. I have used the Migration - Access tool to get the Access db into SQL Server, but now I need to take the data from that table and put it into the tables that our app is going to use. Is there a T-Sql way to Insert m...

Is it possible to constrain a table to have a value in only one of a set of columns

I have a table which needs to link one of three seperate tables, but it should only link to one of them, e.g. Main_Table id UNIQUEIDENTIFIER t1_id UNIQUEIDENTIFIER t2_id INT t3_id INT T1 id UNIQUEIDENTIFIER name VARCHAR(255) T2 id INT name VARCHAR(255) T3 id INT name VARCHAR(255) Is it possible to have a constraint whereby only one...

T-SQL: A proper way to CLOSE/DEALLOCATE cursor in the update trigger

Hello Everyone, This is my first question on StackOverflow, so please be nice ;) Let's say I've got a trigger like this: CREATE TRIGGER trigger1 ON [dbo].[table1] AFTER UPDATE AS BEGIN --declare some vars DECLARE @Col1 SMALLINT DECLARE @Col1 TINYINT --declare cursor DECLARE Cursor1 CURSOR FOR ...

TSQL / SP INSERT statement with text & values

HI, I have an insert statement in a Stored Procedure that requires the value data to be a combination of text and variables/inputs. However my SQL Manager is giving an error on this 'camp' value when trying to compile it. Here is the statement as it currently exists; BEGIN TRAN INSERT INTO TBL_DONATIONS ([donations_MemberID], [d...

SQL Server 2008 R2 hemisphere limitation

Does any one know if the limitaition on SQL Server 2008 that a geography cannot be larger than a hemisphere (described here) has been fixed/relaxed in 2008 R2? ...

SQL Server 'Resume Next' Equivalent

I'm working on a project in VB.net which takes large text files containing T-SQL and executes them against a local SQL database, but I've hit a problem in regards to error handling. I'm using the following technologies : VB.net Framework 3.5 SQL Express 2005 The SQL I'm trying to execute is mostly straight-forwards but my app is co...

Parse a date from unformatted text in SQL

I'm trying to figure out an elegant way to get a date from a text column that has data like this "YYYYMMDD"...so we might see "20060508" as a value in the column, which I would like to be able to return from a query as a date (May 8, 2006). I'm sure I can hack something together given enough time, but the approaches I'm thinking of seem...

SQL Server Stored Procedure Call in PHP - Can't get xml data it returns back into php

Trying to call an SQL SERVER stored procedure from php. Think I've got it working but I can't get the data it returns back into php. I'm copying my php code and also the sample SQL SERVER code below. Believe my problem is how do get the data back from reportData? mssql_execute() just returns boolean true. Looks to me like the procedure...

Sql Server check case sensitivity?

How can I check to see if an SQL Server is case sensitive? I have previously been running the query: SELECT CASE WHEN 'A' = 'a' THEN 'NOT CASE SENSITIVE' ELSE 'CASE SENSITIVE' END But I am looking for other ways as this has actually given me issues in the past. Edit - A little more info: An existing product has many pre-written Store...

" If condition" as one of the constraints to a Column Field ?

I have a Sales tax column in one of my SQL Table where Data is stored. I was wondering is it possible to place an constraint on the table itself for the Sales Tax Column saying " if > June 2008 tax is 2 % " else "tax is 4 %"? Should that come from stored procs related to that Table? ...

Comparison Query to Compare Two SQL Server Tables

I would like to know how to compare two different database table records. What I mean is I will compare two database tables which may have different column names but same data. But one of them may have more records than the other one so I want to see what the difference is between those two tables. To do that how to write the sql query ?...

carriage return and line-feeds

'DateM (Monday): ' + ISNULL(@Mon,'')+CHAR(13)+CHAR(10) 'DateT (Tuesday): ' + ISNULL(@Tues,'')+CHAR(13)+CHAR(10) RESULTS: Is not wrapping. DateM (Monday): Sep 8 2009 12:00AM DateT(Tuesday): Sep 9 2009 12:00AM I want it to wrap. DateM (Monday): Sep 8 2009 12:00AM DateT(Tuesday): Sep 9 2009 12:00AM ...

BRACKETS in SQL if conditions

I have a Rate column in one of my SQL Table where Data is stored. The data in the rate column , is coming thro an Stored proc. In that existing stored proc, I found the condition BEGIN IF @taxPrd < '07/01/2000' OR @taxPrd >= '11/01/2001' AND @taxPrd < '04/01/2003' BEGIN I changed it to ...

how to find a value between ranges in sql server

My condition is My query is returning this 2 10 150000 where 2=startingslab and 10 = endingslab 11 20 250000 21 31 150000 now i want to get the price details for startingslab =3. i.e 15000.I know i need to process it row by row as between is not working. Is there any ...

INNER JOINs with where on the joined table

Hi, Let's say we have SELECT * FROM A INNER JOIN B ON [....] Assuming A has 2 rows and B contains 1M rows including 2 rows linked to A: B will be scanned only once with "actual # of rows" of 2 right? If I add a WHERE on table B: SELECT * FROM A INNER JOIN B ON [....] WHERE B.Xyz > 10 The WHERE will actually be executed before the...