exec problem in sql 2005
- by IordanTanev
Hi,
i have the situation where i have two databases whith same structure. The first have some data in its datatables. I need to create a script that will transfer the data from the first database to the second. I have created this script.
DECLARE @table_name nvarchar(MAX),
@query nvarchar(MAX)
DECLARE @table_cursor CURSOR
SET @table_cursor = CURSOR FAST_FORWARD
FOR
Select TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
OPEN @table_cursor
FETCH NEXT FROM @table_cursor
INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = 'INSERT INTO ' + @table_name + ' SELECT * FROM MyDataBase.dbo.' + @table_name
print @query
exec @query
FETCH NEXT FROM @table_cursor
INTO @table_name
END
CLOSE @table_cursor
DEALLOCATE @table_cursor
The problem is that when i run th script the "print @query" statement prints statement like this
INSERT INTO table SELECT * FROM MyDataBase.dbo.table
When i copy this and run it from Management studio it works fine. But when the script trys to run it with exec i get this error
Msg 911, Level 16, State 1, Line 21
Could not locate entry in sysdatabases for database 'INSERT INTO table SELECT * FROM MPDEV090314'. No entry found with that name. Make sure that the name is entered correctly.
Hope someone can tell me whot is wront with this.
Best Regards,
Iordan Tanev
