April 6, 2012 at 2:06 pm
Hello all. SQL Server 2008 T-SQL question but not necessarily about new features.
I am stumped. I am trying to run some T-SQL to fix some problems in one of our DBs. I just want to drop code into Management Studio and let it rip.
I am aware of the compiled nature of T-SQL and its need to have GO as a batch separator. However, I can’t make it work at all! I have already read many posts, including these:
http://stackoverflow.com/questions/6376866/tsql-how-to-use-go-inside-of-a-begin-end-block
http://stackoverflow.com/questions/2668529/t-sql-go-statement
But nothing helps. I have a section of code that loops a temp table like so:
WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
END
Inside that loop, once a row has been processed it has its Processed column tripped to 1, like so:
UPDATE #temp
SET Processed = 1
WHERE
Foo = @Foo
AND (Bar = @bar);
But the UPDATE code is never actually processed by SQL server and this creates an infinite loop. If I cancel execution I can see that the very first row has had its Processed value tripped to 1, but none of the other rows ever gets tripped. My assumption is that without a GO statement in there somewhere, the value is never being written to the table – until I cancel the execution and that acts as my soft GO.
So, armed with the articles above, I attempt to add a GO into the loop. Here’s a bit more detail:
<DECLARE variables.>
WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
<Reset variables to defaults>
<SELECT variable values from temp #temp table>
<Use variables to do stuff – not important here yet>
UPDATE #temp
SET Processed = 1
WHERE
Foo = @Foo
AND (Bar = @bar);
GO
END
The GO causes syntax errors all over the place. Using a ; terminator makes no difference. The posts above seem to say you SHOULD be able to do this.
I have tried placing the GO in every logical place I can think of, but no good.
Can anyone see an issue here? How can I force the temp table to accept the value in its Processed column so I can move to the next record? If I weren’t bald I’d be tearing my hair out.
Kurt
April 6, 2012 at 2:14 pm
The key behind your trouble is the misunderstanding of the concept of a batch separator A batch separator is not a T-SQL concept, it is a data client concept. In other words, Management Studio is the only thing that cares about the GO. The GO is never actually submitted to SQL Server, it just tells Management Studio how to break up the code in the window as batches to send to to SQL Server.
Consider this proper T-SQL batch:
--<DECLARE variables.>
WHILE (
SELECT COUNT(*)
FROM #temp
WHERE Processed = 0
) > 0
BEGIN
--<Reset variables to defaults>
--<SELECT variable values from temp #temp table>
--<Use variables to do stuff – not important here yet>
UPDATE #temp
SET Processed = 1
WHERE Foo = @Foo
AND (Bar = @bar);
END
Now consider if I were to submit two separate batches to SQL Server that looked like this:
Batch 1:
--<DECLARE variables.>
WHILE (
SELECT COUNT(*)
FROM #temp
WHERE Processed = 0
) > 0
BEGIN
--<Reset variables to defaults>
--<SELECT variable values from temp #temp table>
--<Use variables to do stuff – not important here yet>
UPDATE #temp
SET Processed = 1
WHERE Foo = @Foo
AND (Bar = @bar);
Batch 2:
END
This is what SQL Server sees when you add the GO to the query window and you execute all code in Management Studio...and is why it throws an error since the WHILE loop has not END.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2012 at 2:20 pm
Thanks SS:
That makes sense. But what can be done about the loop issue, then? It seems to me that the call to:
UPDATE #temp SET Processed = 1 WHERE Foo = @Fool AND (Bar = @bar); doesn't get processed until the script is terminated. I believe this to be the case because it sets up an infinite loop and only after I manually terminate the script and dump #temp can I see that the first record has had its 'Processed' value set to 1 but none of the others do.
Do I need to encase that UPDATE in a transaction so each pass through #temp is explicitly committed?
Thanks.
Kurt
PS - I should add that debugging also showed that each pass through the loop was processing the exact same variables. It was all data from the first row.
April 6, 2012 at 2:25 pm
Hard to say, you have only given us part of the picture. We need the whole picture. DDL (CREATE TABLE statement), sample data (INSERT INTO statements), expected results, and your entire piece of code so we know what you are doing.
April 6, 2012 at 2:28 pm
kpwimberger (4/6/2012)
Thanks SS:That makes sense. But what can be done about the loop issue, then? It seems to me that the call to:
UPDATE #temp SET Processed = 1 WHERE Foo = @Fool AND (Bar = @bar);doesn't get processed until the script is terminated.
Not the case unless you turned implicit transactions on (i.e. auto-commit off) or were within an explicit transaction that you did not explicitly commit. By default, unlike in Oracle where you have to provide an explicit COMMIT to commit changes, SQL Server will commit your update statements as they are issued.
I believe this to be the case because it sets up an infinite loop and only after I manually terminate the script and dump #temp can I see that the first record has had its 'Processed' value set to 1 but none of the others do.
This may be how it appears, and some are misled thinking there is something wrong with SQL Server, but at the end of the day it usually boils down to a problem in the code.
------
Backing up for a minute, may I ask why you think you need a loop to do this processing? It is likely that you do not need it at all, and could do all your processing in a set-based way with a carefully crafted UPDATE statement.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2012 at 2:31 pm
We must have been typing at the same time...ditto to everything Lynn said. In order to help further we need to see DDL (i.e. CREATE TABLE statements), DML to create sample data (i.e. INSERT statements) and code that illustrates what you're attempting to do.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2012 at 2:39 pm
Lynn and SS:
Right now there isn't actually a whole lot I am actually doing. Given that this hasn't been working I am trying to get to the root of the issue so I have the code pared to do, essentially, this:
IF @GO = 1
BEGIN
SELECT * INTO #temp FROM dbo.DateL2;
UPDATE #temp SET Processed = 0;
END
WHILE (SELECT COUNT(*) FROM #temp WHERE Processed = 0) > 0
BEGIN
/*-------------------------------------
Clear the variables.
--------------------------------------*/
SET @CollectionPrefix = NULL;
SET @Box = NULL;
SET @Section = NULL;
SET @Row = NULL;
SET @Acc = NULL;
SET @AccId1 = NULL;
SET @AccId2 = NULL;
SET @Locator1 = NULL;
SET @Locator2 = NULL;
SET @Date1 = NULL;
SET @Date2 = NULL;
/*-------------------------------------
Pull variable set for this row of
#temp.
--------------------------------------*/
IF @GO = 1
BEGIN
SELECT TOP 1
@CollectionPrefix = CollectionPrefix
, @Acc = DisplayAcc
, @Box = Box
, @Section = Section
, @Row = Row
, @Date1 = LDate1
, @Date2 = LDate2
FROM
dbo.DateL2
WHERE
Processed = 0;
SET @OUT = @OUT + 'Pulled Box: ' +COALESCE(CAST(@Box AS VarChar), 'NULL')
+ ', Section: ' + COALESCE(CAST(@Section AS VarChar), 'NULL')
+ ', Row: ' + COALESCE(@Row, 'NULL')
+ ', Acc: ' + COALESCE(@Acc, 'NULL')
+ ', Date1: ' + COALESCE(CONVERT(VarChar(10), @Date1, 101), 'NULL')
+ '.' + CHAR(13) + CHAR(13);
END
/*-------------------------------------
Set current record as processed.
--------------------------------------*/
IF @GO = 1
BEGIN
UPDATE #temp
SET Processed = 1
WHERE
CollectionPrefix = @CollectionPrefix
AND (Box = @Box)
AND (Section = @Section)
AND (DisplayAcc = @Acc)
AND (LDate1 = @Date1);
END
END
This is somewhat 'proof-of-concept' until I can see what's not working. The odd thing is that I have used this approcah before with success but can't see the difference. Well, that's not completely true: I have always used a real table rather than a temp. Could that be the issue? I'll have to explore that.
Anyway, I have no doubt that a well-crafted set-based solution might be slicker, but this was sup[posed to be a simple, down-n-dirty (HA!) patch and so I felt fine with a basic loop. And I guess I'm a bit of a code neandetal as I think better in loops than in sets.
Kurt
April 6, 2012 at 2:46 pm
Can you post the DDL for DateL2?
This part of your update statement will not tolerate NULLs:
WHERE CollectionPrefix = @CollectionPrefix
AND (Box = @Box)
AND (Section = @Section)
AND (DisplayAcc = @Acc)
AND (LDate1 = @Date1);
So, if any of those columns are NULL in DateL2, then the UPDATE will fail to update any rows because nothing can be equal to NULL. In this scenario you'll continue looping over that same row, always selecting it because Processed stays at 0.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2012 at 2:57 pm
I think I can tell you what's wrong....
The table you're pulling the TOP (1) columns from is a different table to the one you're updating. So yes, it'll just be fetching the same row again and again and again.
kpwimberger (4/6/2012)
------------ code removed for clarity
SELECT TOP 1
@CollectionPrefix = CollectionPrefix
, @Acc = DisplayAcc
, @Box = Box
, @Section = Section
, @Row = Row
, @Date1 = LDate1
, @Date2 = LDate2
FROM
dbo.DateL2 ----- SELECT from DateL2
WHERE
Processed = 0;
------------ code removed for clarity
UPDATE #temp ------- but update #temp
SET Processed = 1
WHERE
CollectionPrefix = @CollectionPrefix
AND (Box = @Box)
AND (Section = @Section)
AND (DisplayAcc = @Acc)
AND (LDate1 = @Date1);
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 6, 2012 at 3:22 pm
Apologies opc, I have been referring to you as SS, as in your forum title.
Anyway, here’s the scoop. Client supplies me with a spreadheet and that gets imported into SS using a SSIS package that creates this table:
CREATE TABLE [dbo].[DateL2](
[CollectionPrefix] [nvarchar](2) NULL,
[Box] [int] NULL,
[Section] [tinyint] NULL,
[Row] [varchar](1) NULL,
[DisplayAcc] [varchar](50) NULL,
[lDate1] [datetime] NULL,
[qtyDate1] [tinyint] NULL,
[lDate2] [datetime] NULL,
[qtyDate2] [tinyint] NULL,
[Processed] [bit] NULL,
[AccId1] [int] NULL,
[AccId2] [int] NULL,
[Locator1] [int] NULL,
[Locator2] [int] NULL
) ON [PRIMARY]
Next, this DDL is run in a discreet query window:
/*-------------------------------------
Locals.
--------------------------------------*/
DECLARE
@CONTEXT varChar(4)
, @OUT varChar(Max)
, @GO bit
, @HaveRow Bit;
/*-------------------------------------
Setup local vars.
--------------------------------------*/
SET @CONTEXT = 'DEV';
--'PROD';
SET @OUT = '';
/*-------------------------------------
Test context.
--------------------------------------*/
IF @CONTEXT = 'PROD'
BEGIN
IF @@SERVERNAME = 'SQP-aaaa'
SET @GO = 1;
ELSE
SET @GO = 0;
END
IF @CONTEXT = 'DEV'
BEGIN
IF @@SERVERNAME = 'SQD-bbbb'
SET @GO = 1;
ELSE
SET @GO = 0;
END
USE BLocations;
/*----------------------------------------------
Add some missing columns to dbo.dateL2
and populate Processed column.
-----------------------------------------------*/
IF @GO = 1
BEGIN
IF COL_LENGTH('dbo.DateL2', 'Processed') IS NULL
BEGIN
ALTER TABLE dbo.DateL2 ADD Processed bit NULL;
SET @OUT = @OUT + 'Created col Processed in dbo.DateL2.' + CHAR(13);
END
IF COL_LENGTH('dbo.DateL2', 'AccId1') IS NULL
BEGIN
ALTER TABLE dbo.DateL2 ADD AccId1 Int NULL;
SET @OUT = @OUT + 'Created col AccId1 in dbo.DateL2.' + CHAR(13);
END
IF COL_LENGTH('dbo.DateL2', 'AccId2') IS NULL
BEGIN
ALTER TABLE dbo.DateL2 ADD AccId2 Int NULL;
SET @OUT = @OUT + 'Created col AccId2 in dbo.DateL2.' + CHAR(13);
END
IF COL_LENGTH('dbo.DateL2', 'Locator1') IS NULL
BEGIN
ALTER TABLE dbo.DateL2 ADD Locator1 Int NULL;
SET @OUT = @OUT + 'Created col Locator1 in dbo.DateL2.' + CHAR(13);
END
IF COL_LENGTH('dbo.DateL2', 'Locator2') IS NULL
BEGIN
ALTER TABLE dbo.DateL2 ADD Locator2 Int NULL;
SET @OUT = @OUT + 'Created col Locator2 in dbo.DateL2.' + CHAR(13);
END
UPDATE dbo.DateL2 SET Processed = 0;
/*---------------
Success?
----------------*/
IF (@@ERROR <> 0) OR (@@ROWCOUNT = 0)
BEGIN
SET @GO = 0;
SET @OUT = @OUT + 'ERROR: Failed to set Processed = 1.' + CHAR(13);
END
ELSE
SET @OUT = @OUT + 'Success: Set Processed = 1 for current record.' + CHAR(13);
END
END
PRINT @OUT;
Then we move on to the last script I posted. I know that NULLS would crash that code but they will not be an issue.
April 6, 2012 at 3:27 pm
No worries on the name, happens all the time.
Did you see Gail's post? I think she is onto something 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 6, 2012 at 3:27 pm
!!!!!!!!!!!!!!! AARRRGGHHHHHHHHHHHH !!!!!!!!!!!!!!
Gila Monster, Gail, SPOT ON!
THAT was the issue. Oh god, I feel stupid. I have been staring at this code for HOURS and never caught that. THANK YOU, you saved my weekend. Thanks to you opc and Lynn as well for your time and effort.
Kurt
April 6, 2012 at 3:35 pm
Gail:
I am currently doing a 'gratitude project' on facebook. Just thought you should know YOU are today's gratitude!
"Today's gratitude: database consultant Gail Shaw of Johannesburg, South Africa, for her wonderful spotting of a simple and (at least to me) invisible mistake I made in code. She saved my weekend."
Namaste!
Kurt
April 6, 2012 at 4:58 pm
You're welcome.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply