February 27, 2017 at 5:47 pm
DECLARE @databases table
(
RowID INT not null IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(500),
Environment_ID INT,
Trace_Database VARCHAR(500)
)
INSERT INTO @databases(Name,Environment_ID,Trace_Database)
SELECT TOP(1) TableName ,iD,Database FROM sample_data
DECLARE @i INT
SELECT @i = min(RowID) FROM @databases
DECLARE @max-2 int
SELECT @max-2 = max(RowID) FROM @databases
DECLARE @sa DATETIME
SET @sa=CONVERT(VARCHAR(10),DATEADD(DAY,3,GETDATE()),101)
--SELECT @sa
WHILE (@i <= @max-2)
BEGIN
DECLARE @sqlrun VARCHAR(max),
@name VARCHAR(50),
@env int
DECLARE @r int,
@Trace_Database VARCHAR(50)
SET @r =1
SELECT @name= Name FROM @databases WHERE RowID = @i
SELECT @Trace_Database= Trace_Database FROM @databases WHERE RowID = @i
SELECT @env= min(Environment_ID) FROM @databases WHERE RowID = @i
WHILE (@env <= @max-2)
BEGIN
SELECT @env
SET @sqlrun='WHILE' + SPACE(1) + CAST(@r AS VARCHAR(10)) + '>0' +
+ CHAR(13) + 'BEGIN' + CHAR(13)
+'DELETE TOP(20000) FROM'+ SPACE(1)+ +@Trace_Database+'.dbo.' + @name + SPACE(1) + 'where created_date >' + '''' + CONVERT(VARCHAR(50),@purge) +''''
+ CHAR(13) + 'SET'+ SPACE(1)+ CAST(@r AS VARCHAR(10)) + SPACE(1)+ '='+CAST (@@ROWCOUNT AS VARCHAR(10)) + CHAR(13) +
'END'
SET @env=@env + 1
--EXEC(@sqlrun)
--PRINT @sqlrun
END
EXEC(@sqlrun)
PRINT @sqlrun
SET @i = @i + 1
END
I SEE BELOW ERROR
(1 row(s) affected)
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1'.
WHILE 1>0BEGINDELETE TOP(20000) FROM sample_data where created_date >'Mar 2 2017 12:00AM'SET 1 =1END
February 27, 2017 at 8:02 pm
'SET @r ' + '='+ CAST (@@ROWCOUNT AS VARCHAR(10)) + CHAR(13) +
For the error, I would guess you ended up with a SET 1 = 1 to get that error.
Sue
February 28, 2017 at 2:01 am
The sql string that you are creating to execute is just wrong. There's the change Sue_H explained, but you also need to declare @r in @sqlrun, set it to 1, and the start of your while loop should be 'while @r>0'.
If you are creating dynamic SQL it is usually easier to write the SQL code first normally, make sure it works, then convert it to dynamic sql.
There are other parts of your code that aren't very nice either, your 3 select statements can be written like this:
SELECT @name= Name , @Trace_Database= Trace_Database, @env= min(Environment_ID) FROM @databases WHERE RowID = @i
I'm also not convinced your logic is right, but that's maybe just because you've changed so much of the code from reality that it doesn't make sense now.
February 28, 2017 at 8:29 am
alastair.beveridge - Tuesday, February 28, 2017 2:01 AMThe sql string that you are creating to execute is just wrong. There's the change Sue_H explained, but you also need to declare @r in @sqlrun, set it to 1, and the start of your while loop should be 'while @r>0'.If you are creating dynamic SQL it is usually easier to write the SQL code first normally, make sure it works, then convert it to dynamic sql.
There are other parts of your code that aren't very nice either, your 3 select statements can be written like this:
SELECT @name= Name , @Trace_Database= Trace_Database, @env= min(Environment_ID) FROM @databases WHERE RowID = @iI'm also not convinced your logic is right, but that's maybe just because you've changed so much of the code from reality that it doesn't make sense now.
@r is set to 1 before the the @sqlrun string but thanks as your post shows the second one where it's setting the value of @r instead of @r itself. As you said, it's in the first part of the @sqlrun: SET @sqlrun='WHILE' + SPACE(1) + CAST(@r AS VARCHAR(10)) + '>0' +
It's hard to follow this and I have some questions about the logic as well but I think there are too many missing pieces.
But with your mention of the second issue with @r it looks like the same problem where the value of @r is being used instead of @r itself.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply