December 7, 2010 at 12:43 pm
Hello folks. I’m having a bit of a problem using variables in my stored procedure parms. The stored procedure inserts records pulled from linked servers into a server properties table. The stored procedure requires a servername as parameter 1 and a port number for parameter 2.
The following code sequentially reads a master table of servers and ports and then executes the stored procedure. Executing this code results in an INSERT error in the server properties table saying the servername cannot be null. If the servername and port number are hard coded, it works great.
Why can’t I use variables for the parameters? help
thx jon
DECLARE @id INT
DECLARE @CreateDate smalldatetime
DECLARE @ServerName varchar (250)
DECLARE @Port_nbr varchar (5)
DECLARE @rowNum INT
DECLARE @maxrows INT
SELECT @maxRows = COUNT(*)
FROM dbo.HOSTSERVERS
-- get very first record
SELECT TOP 1
@id = PK_ID
,@CreateDate = CreateDate
,@ServerName = ServerName
,@Port_nbr = Port_nbr
FROM dbo.HOSTSERVERS
PRINT( 'first record: ' + @servername )
SET @rowNum = 0
-- Loop until last row is reached
WHILE @rowNum < @maxRows
BEGIN
SET @rowNum = @rowNum + 1
If (@CreateDate > GETDATE() - 7
- this code works: EXEC dbo.usp_ALL_SERVERS_PROPERTIES @LinkedServer = SERVERX15,@Port = 1688
this code does not: EXEC dbo.usp_ALL_SERVERS_PROPERTIES @LinkedServer = @servername,@Port = @port_nbr
PRINT( 'new record: ' + @servername + ',' + @Port_nbr)
-- grab the next row
SELECT TOP 1
@id = PK_ID
,@CreateDate = CreateDate
,@ServerName = ServerName
,@Port_nbr = Port_nbr
FROM dbo.HOSTSERVERS
WHERE PK_ID > @id
END
December 7, 2010 at 2:50 pm
I believe you are confusing EXEC @SQL with "parameterrized dynamic SQL" which requires that you EXEC sp_executeSQL.
If you want to use EXEC by itself, you need to build the entire string (with your substituted values plugged in as constants) prior to the EXEC.
For sp_ExecuteSQL, just refer to BOL (books online or the help function in SSMS). You will find an example there.
Let us know if you have any further questions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 8, 2010 at 1:00 am
This was removed by the editor as SPAM
December 8, 2010 at 9:54 am
Thanks much for the assistance.
Below is my modified code. When executed it appears to run successfully. All the records are read from the HOSTSERVER table but the server properties table which is being called in the stored procedure is not getting populated. I placed a "print" statement in the stored procedure but it never gets posted in the messages tab. It seems like the stored procedure is never getting called...
------------------------
DECLARE @id INT
,@CreateDate smalldatetime
,@ServerName nvarchar(250)
,@LinkedServer nvarchar(250)
,@Port_nbr nvarchar(5)
,@Port nvarchar(5)
,@rowNum INT
,@maxrows INT
,@sql nvarchar (500)
,@Parms nvarchar (500)
set @sql = N'EXEC dbo.usp_ALL_SERVERS_PROPERTIES' + @Parms
set @Parms = ' @LinkedServer = @ServerName nvarchar(250),@Port = @Port_nbr nvarchar(5)'
DECLARE ServerList CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR SELECT PK_ID
, CreateDate
, ServerName
, Port_nbr
FROM dbo.HOSTSERVERS
WHERE Port_nbr IS NOT NULL
OPEN ServerList
-- get very first record
FETCH NEXT FROM ServerList
INTO @id
, @CreateDate
, @ServerName
, @Port_nbr
PRINT( 'first record: ' + @servername )
WHILE @@FETCH_STATUS = 0
BEGIN
If (@CreateDate > GETDATE() - 7 )
EXEC sp_executesql @sql
PRINT( 'new record: ' + @servername + ',' + @Port_nbr)
-- get next record
FETCH NEXT FROM ServerList
INTO @id
, @CreateDate
, @ServerName
, @Port_nbr
END
December 8, 2010 at 1:37 pm
Put a PRINT @SQL statement right before EXEC sp_ExecuteSQL and lets see what values you are passing.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
December 9, 2010 at 12:40 pm
Thanks!
I think its working coded like this:
If (@CreateDate > GETDATE() - 7 )
EXEC sp_executesql @sql, N'@servername varchar(250) , @Port_nbr varchar(5) ', @servername,@Port_nbr
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply