Exec problem in SQL Server 2005
- by IordanTanev
Hi,
I have the situation where i have two databases with same structure. The first have some data in its data tables. 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 the 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 tries 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
