July 1, 2014 at 6:28 pm
I am having problem with my script. at first time, it runs fine, but when second time, when their is no data in schema ABC tables, it still make change to original schema tables. here is code. I think, it is bypassing step2 or 3.
DECLARE TCursor CURSOR
FOR
--- (1) get table names from [dbo].[Tablelist]
SELECT [TableName] from [dbo].[Tablelist] where SchemaName = 'abc' --have 5 schemas, but only need 3
DECLARE @TableName varchar(255)
OPEN TCursor
FETCH NEXT FROM TCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Stmt3 nvarchar(max)
--(2) check if jobhistory table have new date and batchjob status is success.which is on server1
SET @Stmt3 = ('select tablename FROM .[dbo].[JobHistory] where tableName = '''+ (@TableName) + '''and FLSLoadedDate > (getdate() -1) and JobBatchStatus <>''fail') ---(2)
BEGIN
---(3) also need to check if table has records inserted. which is on server2
SET @Stmt3 = ('If (Exists (Select * From abc.' +@tablename + '))' )
BEGIN
--(4) if records are in schema abc, flip original schema. otherwise leave original schema as it is.
SET @Stmt3 = 'ALTER SCHEMA abc1 TRANSFER Original.' +@tablename
EXEC (@Stmt3)
SET @Stmt3 = 'ALTER SCHEMA Original TRANSFER abc.'+@tablename
EXEC (@Stmt3)
SET @Stmt3 = 'ALTER SCHEMA abc TRANSFER abc1.' +@tablename
EXEC (@Stmt3)
SET @Stmt3 = 'TRUNCATE TABLE abc.' +@tablename
EXEC (@Stmt3)
END
FETCH NEXT FROM TCursor INTO @TableName
END
END
CLOSE TCursor
DEALLOCATE TCursor
thanks,
Navie
July 1, 2014 at 10:02 pm
navie22 (7/1/2014)
I am having problem with my script. at first time, it runs fine, but when second time, when their is no data in schema ABC tables, it still make change to original schema tables. here is code. I think, it is bypassing step2 or 3.DECLARE TCursor CURSOR
FOR
--- (1) get table names from [dbo].[Tablelist]
SELECT [TableName] from [dbo].[Tablelist] where SchemaName = 'abc' --have 5 schemas, but only need 3
DECLARE @TableName varchar(255)
OPEN TCursor
FETCH NEXT FROM TCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Stmt3 nvarchar(max)
--(2) check if jobhistory table have new date and batchjob status is success.which is on server1
SET @Stmt3 = ('select tablename FROM .[dbo].[JobHistory] where tableName = '''+ (@TableName) + '''and FLSLoadedDate > (getdate() -1) and JobBatchStatus <>''fail') ---(2)
BEGIN
---(3) also need to check if table has records inserted. which is on server2
SET @Stmt3 = ('If (Exists (Select * From abc.' +@tablename + '))' )
BEGIN
--(4) if records are in schema abc, flip original schema. otherwise leave original schema as it is.
SET @Stmt3 = 'ALTER SCHEMA abc1 TRANSFER Original.' +@tablename
EXEC (@Stmt3)
SET @Stmt3 = 'ALTER SCHEMA Original TRANSFER abc.'+@tablename
EXEC (@Stmt3)
SET @Stmt3 = 'ALTER SCHEMA abc TRANSFER abc1.' +@tablename
EXEC (@Stmt3)
SET @Stmt3 = 'TRUNCATE TABLE abc.' +@tablename
EXEC (@Stmt3)
END
FETCH NEXT FROM TCursor INTO @TableName
END
END
CLOSE TCursor
DEALLOCATE TCursor
thanks,
Navie
I think that you are assuming that the statement (2) and (3) are executing the code when in fact all they are doing is assigning a string to @Stmt3.
I think you will need to make some changes to how these statements are being used. Since you are using dynamic SQL, you will need to adjust your code so that it returns something that can then be used in an if statement. Perhaps something along the lines of
DECLARE #TempResult table (Counter INT) -- near the start of your code
-- SET @Stmt3 = ('Select COUNT(*) AS Counter From abc.' +@tablename )
DELETE FROM #TempResult -- make sure there is nothing in the table
INSERT INTO #TempResult
EXEC (@Stmt3)
IF (SELECT Counter FROM @TempResults) >= 1
BEGIN
July 2, 2014 at 8:20 am
will you please help me. I am not sure I follow the tempt able part.
thanks,
Navie
July 2, 2014 at 8:43 am
That's because you're not executing steps 2 and 3.
Step 3 will fail because you only have a condition. I'm not sure what you are trying to do with step 2, but I corrected step 3.
DECLARE TCursor CURSOR
FOR
--- (1) get table names from [dbo].[Tablelist]
SELECT [TableName]
FROM [dbo].[Tablelist]
WHERE SchemaName = 'abc' --have 5 schemas, but only need 3
DECLARE @TableName VARCHAR(255)
OPEN TCursor
FETCH NEXT
FROM TCursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @Stmt3 NVARCHAR(max)
--(2) check if jobhistory table have new date and batchjob status is success.which is on server1
SET @Stmt3 = ('select tablename FROM .[dbo].[JobHistory] where tableName = ''' + (@TableName) + '''and FLSLoadedDate > (getdate() -1) and JobBatchStatus <>''fail') ---(2)
BEGIN
---(3) also need to check if table has records inserted. which is on server2
SET @Stmt3 = 'If (Exists (Select * From abc.' + @tablename + '))' + CHAR(13)
+ 'BEGIN'
+ 'ALTER SCHEMA abc1 TRANSFER Original.' + @tablename + CHAR(13)
+ 'ALTER SCHEMA Original TRANSFER abc.' + @tablename + CHAR(13)
+ 'ALTER SCHEMA abc TRANSFER abc1.' + @tablename + CHAR(13)
+ 'TRUNCATE TABLE abc.' + @tablename + CHAR(13)
+ 'END'
PRINT @Stmt3
EXEC (@Stmt3)
FETCH NEXT
FROM TCursor
INTO @TableName
END
END
CLOSE TCursor
DEALLOCATE TCursor
Hopefully you can identify what's wrong and be able to fix it.
July 4, 2014 at 3:23 am
Hi,
There are 2 reasons,
1. You are not executing the 2nd and 3rd. only you assign the value to stmt3.
2. in your query, there is miss sequence of Begin - End
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply