May 16, 2006 at 1:50 pm
I'm fairly new to SQL Server Databases. I'm currently trying to make a stored procedure in my database that tries to use a variable as a table name to insert into, but i'm getting some errors. I'm not sure if the variables can be used in this way. If this isn't possible, is there another way to accomplish what I'm trying to do? I'm basically trying to create a loop that will copy a set of tables from one database to a mirror database.
Here's my code:
CREATE PROCEDURE dbo.copy4modify
@servername varchar(50)
AS
SET NOCOUNT ON
IF (@servername <> null)
BEGIN
CREATE TABLE temp_table_list (table_name varchar(50),column_name varchar(50))
INSERT INTO temp_table_list
SELECT table_name,column_name FROM INFORMATION_SCHEMA.columns
WHERE column_name LIKE '%Server Name'
AND table_name LIKE 'Server_%'
-- declare all variables!
DECLARE @current_tablevarchar(50),
@server_col_label varchar(50),
@table_cursor CURSOR
SET @table_cursor = CURSOR FAST_FORWARD
FOR
SELECT table_name,column_name
FROM temp_table_list
OPEN @table_cursor
FETCH NEXT FROM @table_cursor
INTO @current_table, @server_col_label
WHILE (@@fetch_status = 0) AND (@@error = 0)
BEGIN
INSERT INTO Requests.dbo.[@current_table]
SELECT * FROM [Unicenter Server FinalSQL].dbo.[@current_table]
WHERE @server_col_label = @servername
FETCH NEXT FROM @table_cursor
INTO @current_table
END
CLOSE @table_cursor
DEALLOCATE @table_cursor
DROP TABLE temp_table_list
END
GO
Here's the errors:
Server: Msg 208, Level 16, State 1, Procedure copy4modify, Line 30
Invalid object name 'Requests.dbo.@current_table'.
Server: Msg 208, Level 16, State 1, Procedure copy4modify, Line 30
Invalid object name 'Unicenter Server FinalSQL.dbo.@current_table'.
May 16, 2006 at 3:41 pm
You need to make the insert into some dynamic sql as per below to achieve this
DECLARE @sql nvarchar(4000)
SET @sql = 'INSERT INTO Requests.dbo.'+[@current_table]+
' SELECT * FROM [Unicenter Server FinalSQL].dbo.'+[@current_table]+
' WHERE ' +@server_col_label+ ' = ' +@servername
exec
sp_executesql @sql;
hth
David
PS I always make typos in dynamic sql so you may need to check it first
May 16, 2006 at 4:00 pm
The only way you could do this in a stored procedure would be to use dynamic SQL as David has shown. I, personally, would come up with a different solution and stay away from dynamic SQL. Search this site for discussion on dynamic SQL. You may consider using SSIS ( SQL Server 2005's version of DTS) and schedule a data pump to move your data. You could also build a utility script to generate the INSERT statements, then use that script in osql, manually run the script, or scheudule it as a job (step 1 to generate the script, step 2 to execute). For example, this code would build your INSERTs:
declare @servername varchar(50)
set @servername = 'MyServer'
SELECT 'INSERT INTO Requests.dbo.[' + table_name + '] SELECT * FROM [Unicenter Server FinalSQL].dbo.' + table_name + 'WHERE ' + column_name + ' = ' + @servername
FROM INFORMATION_SCHEMA.columns
WHERE column_name LIKE '%Server Name' AND table_name LIKE 'Server_%'
Good Luck...
May 17, 2006 at 9:23 am
Ok I think I'm leaning towards doing it in dynamic SQL. This is only a local database so I don't have to worry about security too much. I tried what David suggested and it makes sense to me, but now I get another error.
Here's my current code:
CREATE PROCEDURE dbo.copy4modify
@servername varchar(50)
AS
SET NOCOUNT ON
IF (@servername null)
BEGIN
CREATE TABLE temp_table_list (table_name varchar(50),column_name varchar(50))
INSERT INTO temp_table_list
SELECT table_name,column_name FROM INFORMATION_SCHEMA.columns
WHERE column_name LIKE '%Server Name'
AND table_name LIKE 'Server_%'
-- declare all variables!
DECLARE@current_tablevarchar(50),
@server_col_label varchar(50),
@table_cursor CURSOR,
@sql nvarchar(4000)
SET @table_cursor = CURSOR FAST_FORWARD
FOR
SELECT table_name,column_name
FROM temp_table_list
OPEN @table_cursor
FETCH NEXT FROM @table_cursor
INTO @current_table, @server_col_label
WHILE (@@fetch_status = 0) AND (@@error = 0)
BEGIN
SET @sql = 'INSERT INTO Requests.dbo.'+[@current_table]+
' SELECT * FROM [Unicenter Server FinalSQL].dbo.'+[@current_table]+
' WHERE ' +@server_col_label+ ' = ' +@servername
EXEC sp_executesql @sql;
FETCH NEXT FROM @table_cursor
INTO @current_table
END
CLOSE @table_cursor
DEALLOCATE @table_cursor
DROP TABLE temp_table_list
END
The error I get says:
Error 207: Invalid column name '@current_table'.
Invalid column name '@current_table'
May 17, 2006 at 9:33 am
You are getting this error because your variables, as you have them defined, are scoped to your current stored procedure. When you call sp_executeSQL, you enter a new scope so you must define your variables again. sp_executeSQL allows you to pass in variable names and declarations. Look at sp_executeSQL in BOL.
May 17, 2006 at 12:46 pm
Ronald you need to lose the [ ] brackets around the variable names these make SQl server treat the variables as identifiers hence the error
SET @sql = 'INSERT INTO Requests.dbo.'+@current_table+
' SELECT * FROM [Unicenter Server FinalSQL].dbo.'+@current_table+
' WHERE ' +@server_col_label+ ' = ' +@servername
EXEC sp_executesql @sql;
Sorry my fault told you I lways made typos with dynamic sql. The scoping of the variable is fine i use this a lot in a solution I have for using a generic audit table for all my auditing.
hth
David
May 17, 2006 at 1:42 pm
Ok I think I'm starting to get somewhere, but there's still some issues. Everything seems to be passing now, but when I try to execute the stored procedure in query analyzer I get a bunch of errors like this:
Line 1: Incorrect syntax near '2'.
Server: Msg 170, Level 15, State 1, Line 1
I tried both John and David's ways of using sp_executesql, but I get the same result. Here's the code I'm using right now. I can't see any mistakes, but maybe I'm completely missing something big. I'm using David's way in this one but I have another stored procedure using John's approach.
CREATE PROCEDURE dbo.copy4modify
@servername varchar(50)
AS
SET NOCOUNT ON
IF (@servername null)
BEGIN
CREATE TABLE temp_table_list (table_name varchar(50),column_name varchar(50))
INSERT INTO temp_table_list
SELECT table_name,column_name FROM INFORMATION_SCHEMA.columns
WHERE column_name LIKE '%Server Name'
AND table_name LIKE 'Server_%'
-- declare all variables!
DECLARE@current_tablevarchar(50),
@server_col_label varchar(50),
@table_cursor CURSOR,
@sql nvarchar(4000)
SET @table_cursor = CURSOR FAST_FORWARD
FOR
SELECT table_name,column_name
FROM temp_table_list
OPEN @table_cursor
FETCH NEXT FROM @table_cursor
INTO @current_table, @server_col_label
WHILE (@@fetch_status = 0) AND (@@error = 0)
BEGIN
SET @sql = 'INSERT INTO Requests.dbo.'+@current_table+
' SELECT * FROM [Unicenter Server FinalSQL].dbo.'+@current_table+
' WHERE ' +@server_col_label+ ' = ' +@servername
EXEC sp_executesql @sql
FETCH NEXT FROM @table_cursor
INTO @current_table, @server_col_label
END
CLOSE @table_cursor
DEALLOCATE @table_cursor
DROP TABLE temp_table_list
END
GO
May 17, 2006 at 2:00 pm
Ronald
Couple of other bits I don't know what options you have set in query analyzer but
IF (@servername <> null) will not work in a default setup you need to change it to
IF (@servername IS NOT null)
Sorry i didn't notice it before but i onlt really looked at the other part of the proc.
If you are still getting errors the best thing to do is try and setup a trace with sql profiler so that you can see what sp_executesql is actually executing and you should find if the rest of errors have gone that you will see the sql statement that it is trying to execute it should be very helpful as you'll be able to see if you've actually constructed a valid statement.
hth
David
May 17, 2006 at 5:03 pm
Ronald if it helps this works on my 2005 development box , I dont have any 2000 ones left unfortunately.
CREATE
PROCEDURE dbo.copy4modify
@servername
varchar(50)
AS
SET
NOCOUNT ON
IF
(@servername IS NOT null)
BEGIN
CREATE
TABLE temp_table_list (table_name varchar(50),column_name varchar(50))
INSERT
INTO temp_table_list
SELECT
table_name,column_name FROM INFORMATION_SCHEMA.columns
WHERE
column_name LIKE '%Server Name'
AND
table_name LIKE 'Server_%'
-- declare all variables!
DECLARE
@current_table varchar(50),
@server_col_label
varchar(50),
@table_cursor
CURSOR,
nvarchar(4000)
SET
@table_cursor = CURSOR FAST_FORWARD
FOR
SELECT
table_name,column_name
FROM
temp_table_list
OPEN
@table_cursor
FETCH
NEXT FROM @table_cursor
INTO
@current_table, @server_col_label
WHILE
(@@fetch_status = 0) AND (@@error = 0)
BEGIN
SET
@sql = 'INSERT INTO Requests.dbo.'+@current_table+
' SELECT * FROM [Unicenter Server FinalSQL].dbo.'
+@current_table+
' WHERE '
+@server_col_label+ ' = ' +@servername
EXEC
sp_executesql @sql
FETCH
NEXT FROM @table_cursor
INTO
@current_table, @server_col_label
END
CLOSE
@table_cursor
DEALLOCATE
@table_cursor
DROP
TABLE temp_table_list
END
GO
David
May 18, 2006 at 9:52 am
Ok I think I've figured out the problem. The errors I was getting was caused by some data having spaces. For example, the value of @server_col_label = 'Column Name'. I forgot to add the brackets around it so it works right and the name is read correctly. Now the problem I have is making the computer recognize that @servername is a string value. I think this can be solved by adding single quote characters to the string. I'm just not sure how to do it in a stored procedure.
Would this work?
' WHERE [' +@server_col_label+ '] = ' +char(39)+@servername+char(39)
May 18, 2006 at 10:22 am
Nevermind, I got it to work. Thanks for all the help. I'm sure I'll be back.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply