November 17, 2009 at 12:52 pm
Hi All ~
I am trying to create an SP that will read a table which contains the reference data table names for several databases, and migrate the data from those tables to another DB.
I create an SP to do this.
The following is the code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UpdateRefData]
@FromDB varchar(50),
@todb varchar(50)
AS
SET NOCOUNT ON
SET ROWCOUNT 0
--- Syntax: UpdateRefData @fromDB, @todb
--- Example: UpdateRefData Specialty_Marine_dve, Specialty_Marine_ljb
---- Output should be displayed to Screen, if it does not, check the
---- referenceDataTables table for valid values
delete from Reftables
DECLARE @TableName varchar(128)
DECLARE @exec_sql nvarchar (2000)
DECLARE @TabCount Varchar(10)
DECLARE @ConstraintName varchar (1000)
DECLARE @Count Varchar (100)
DECLARE @String nvarchar(2000)
DECLARE @String2 nvarchar(2000)
print 'Step1 : Disable FKs'
set @string = 'Use [' + @todb +'] '
set @string = @string + 'exec sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"'
print @string
exec sp_executesql @string
print 'Step2 : delete data'
Insert INTO RefTables (TableName) Select TableName
from reference_data..ReferenceDataTables
where overlay = 'y' and DBNAme = @FromDB
order by TableName
SELECT @TabCount = Count(*) FROM RefTables
WHILE @tabCount > 0
BEGIN
SET ROWCOUNT 1
SELECT @TableName = TableName
from RefTables
set rowcount 1000000
SET @exec_sql = 'DELETE FROM ' + @todb + '..'+ @tableName
print @exec_sql
exec sp_executesql @exec_sql
--
DELETE FROM RefTables WHERE TableName = @TableName
SET @TabCount = @TabCount - 1
END
print 'Step3 : insert data'
SET ROWCOUNT 0
Insert INTO RefTables (TableName) Select TableName
from reference_data..ReferenceDataTables
where overlay = 'y' and DBNAme = @FromDB
order by TableName
SELECT @TabCount = Count(*) FROM RefTables
WHILE @tabCount > 0
BEGIN
SET ROWCOUNT 1
SELECT @TableName = TableName
from RefTables
set @exec_sql = 'INSERT INTO ' + @todb + '..' + @tableName + ' SELECT * from '
set @exec_sql = @exec_sql + @FromDB + '..' + @tablename
print @exec_sql
set rowcount 1000000
exec sp_executesql @exec_sql
SEt Rowcount 1
--
DELETE FROM RefTables WHERE TableName = @TableName
SET @TabCount = @TabCount - 1
END
print 'Step4 : Enable FKs'
set @string = 'Use [' + @todb +'] '
set @string = @string + 'exec sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"'
print @string
exec sp_executesql @string
Steps 1-3 work splendidly. Step4 is not altering the FKs!!! It works outside the SP. I am at a loss. Any suggestions???
November 17, 2009 at 2:02 pm
You might add a ";" after the use [database] statement.
Also is it just not working or is it returning any errors?
CEWII
November 17, 2009 at 2:18 pm
Added the ; with no change in execution. Step 1, disabling the FKs, works just fine as is.
The code works outside of the SP.
No error appears, but when I check my FKs they are defined as NOCHECK.
November 17, 2009 at 2:40 pm
How do you know step 1 is working, have you re-enabled the check constraints manually?
Also, I guess I would run a trace at this point to see what commands are actually being sent to the DB. You probably need the stmt:completed item in the trace.
CEWII
November 17, 2009 at 2:48 pm
1. The data is actually being inserted on the target DB. Otherwise, if they were enabled, it would violate the FK constraint.
2. I have manually checked the FKs on the target DB.
I will put a trace on the DB. Thanks
November 17, 2009 at 3:09 pm
I think the trace is your best best, you should be able to see the command that is running as opposed to what you think is running..
CEWII
November 17, 2009 at 4:21 pm
From the trace:
SP:StmtCompletedUse [Specialty_Marine_ljb]; Microsoft SQL Server Management Studio - Query
SP:StmtStartingexec sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"Microsoft SQL Server Management Studio - Query
...
Then a million (exaggeration) lines for the sp_msforeachtable
...
Then the completion statements:
SP:StmtCompletedexec sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT all"Microsoft SQL Server Management Studio - Query
SP:StmtCompletedexec sp_executesql @FKstring
Microsoft SQL Server Management Studio - Query
To me, it looks exactly like I would expect it to... I still don't see why it wouldn't execute properly...
November 18, 2009 at 10:24 am
OK. After going through the million lines between the exec statement and the completion, I notice that only one of the tables has its FKs enabled during step four. It is the first one that it hits when the disable portion runs, so I'd guess it is only hitting the first one? But why the heck would that happen???
Curse these undocumented SPs!
Anyone have any clue, why it'd only hit the first table it finds rather than looping through?
November 18, 2009 at 10:41 am
OK Ok. Now, I feel foolish!
Rowcount was still set to one!
A simple 'set rowcount 0' fixed my problem. Man, am I foolish!
Thank you for all your help! Much appreciated.
November 18, 2009 at 10:48 am
You know, all along I knew it was going to be something little, because all the big stuff looked good. And quite frankly the devil is in the details.. Glad you found it..
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply