January 31, 2011 at 1:07 pm
I have a stored procedure that has the name of a SQL Server as one of its parameters. It uses this parameter to build a SQL statement that it then runs using EXEC(). This works fine in all cases:
SET @strSQL = '[' + @REMSServer + '].rems.dbo.AddUserID @user-id=' + CAST(@UserID AS varchar) + ', @OrderID=' + CAST(@OrderID aS varchar)
EXEC(@strSQL)
HOWEVER, if I try this:
SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @REMSServer + '].rems.dbo.[Order] WHERE OrderID = ' + CAST(@OrderID AS varchar) + ' AND UserID = ' + CAST(@UserID AS varchar)
EXECUTE sp_executesql @SQLString, @Parms, @CountOUT = @NumberofRows OUTPUT;
it works fine if the value of @REMSServer is a simple SQL Server name, for example DEV30. However, if @REMSServer is an instance, e.g., SQL004\SQL004, I get an error message:
Invalid object name '[SQL004\SQL004].rems.dbo.[Order'.
I suspect the difference is in my use of sp_executesql instead of EXEC(), but as far as I understand, I need to use sp_executesql in order to fill my OUTPUT parameter @NumberofRows. Does anyone know how to get around this?
Also, while I'm at it, I need to do the same thing within a cursor loop:
SET @CursorVar1 = CURSOR LOCAL FAST_FORWARD
FOR
SELECT TEXTPTR(Descript)
FROM Tasks
WHERE WO_NUM in
(SELECT ticketnum FROM @REMSServer.rems.dbo.request r
INNER JOIN @REMSServer.rems.dbo.[order] o
ON r.orderid = o.orderid
WHERE o.userid = @user-id
AND o.statusid <> 4)
when I try this it won't even compile; I get:
Incorrect syntax near '.'.
Any ideas?
Thanks!
January 31, 2011 at 1:19 pm
Melanie Peterson (1/31/2011)
it works fine if the value of @REMSServer is a simple SQL Server name, for example DEV30. However, if @REMSServer is an instance, e.g., SQL004\SQL004, I get an error message:Invalid object name '[SQL004\SQL004].rems.dbo.[Order'.
I suspect the difference is in my use of sp_executesql instead of EXEC(), but as far as I understand, I need to use sp_executesql in order to fill my OUTPUT parameter @NumberofRows. Does anyone know how to get around this?
Linked server definition, I believe will circumvent that.
Also, while I'm at it, I need to do the same thing within a cursor loop:
SET @CursorVar1 = CURSOR LOCAL FAST_FORWARD
FOR
SELECT TEXTPTR(Descript)
FROM Tasks
WHERE WO_NUM in
(SELECT ticketnum FROM @REMSServer.rems.dbo.request r
INNER JOIN @REMSServer.rems.dbo.[order] o
ON r.orderid = o.orderid
WHERE o.userid = @user-id
AND o.statusid <> 4)
when I try this it won't even compile; I get:
Incorrect syntax near '.'.
No can do, can't use a variable in a definition of an object like that. That's why you're stuck using the dynamic SQL you did above.
There isn't a workaround for this, not directly. You could load the process into each server that needs this in the master and call it via Dynamic SQL where you can load it properly, but there's no direct cure I'm aware of. Cursor's won't take dynamic SQL... well, not unless the *entire* code is built dynamically in a single batch... which opens up all sorts of pain.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2011 at 1:21 pm
How does the linked server definition (which exists) need to be changed?
Also - I should've noted that the server where this sp resides is 2005, but REMSServer is SQL Server 2000
Thanks!
January 31, 2011 at 1:31 pm
Melanie Peterson (1/31/2011)
How does the linked server definition (which exists) need to be changed?Also - I should've noted that the server where this sp resides is 2005, but REMSServer is SQL Server 2000
Thanks!
Ummm, then it should work.
Was this your typo, or the actual error message?
Invalid object name '[SQL004\SQL004].rems.dbo.[Order'.
If it's from the system, you're missing an ]
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2011 at 1:43 pm
That is the exact error message I get from the computer. However, what's in the sp has the correct number of square brackets:
SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @REMSServer + '].rems.dbo.[Order] WHERE OrderID = ' + CAST(@OrderID AS varchar) + ' AND UserID = ' + CAST(@UserID AS varchar)
January 31, 2011 at 2:25 pm
SELECT @CountOUT = COUNT(*) FROM [' + @REMSServer + '].rems.dbo.[Order
the above string is exactly 70 characters; if @SQLString is too small, it'd truncate and give that error message. what is the definition of @SQLString? NVarchar(70)? maybe NVarchar(2000) would be better
Lowell
January 31, 2011 at 2:37 pm
@SQLString is an nvarchar(200). If, instead of executing, I just PRINT it, I get
SELECT @CountOUT = COUNT(*) FROM [SQL004\SQL004].rems.dbo.[Order] WHERE OrderID = 14 AND UserID = 533
which is what I want
January 31, 2011 at 3:31 pm
A little more information - if I just run this code outside of a stored proc, it works fine. So:
DECLARE @SQLString AS nvarchar(200);
DECLARE @Parms AS nvarchar(200);
DECLARE @NumberofRows int;
DECLARE @OneSlashServer AS varchar(30)
DECLARE @OrderID as int
DECLARE @user-id as int
DECLARE @REMSServer as nvarchar(200)
SET @OrderID = 14
SET @user-id = 533
SET @REMSServer = 'SQL004\SQL004'
SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @REMSServer + '].rems.dbo.[Order] WHERE OrderID = ' + CAST(@OrderID AS varchar) + ' AND UserID = ' + CAST(@UserID AS varchar)
SET @Parms = N'@CountOUT int OUTPUT';
EXECUTE sp_executesql @SQLString, @Parms, @CountOUT = @NumberofRows OUTPUT;
PRINT @NumberofRows
works fine. It's only within the stored proc that it throws this error.
January 31, 2011 at 3:46 pm
I don't have the setup to test this, so sorry for using you as the guinea pig, but can you try changing this:
SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @REMSServer + '].rems.dbo.[Order] WHERE OrderID = ' + CAST(@OrderID AS varchar) + ' AND UserID = ' + CAST(@UserID AS varchar)
to this:
SET @SQLString = N'SELECT @CountOUT = COUNT(*) FROM [' + @REMSServer + '].rems.dbo.[Order] WHERE OrderID = ' + CAST(@OrderID AS Nvarchar(200)) + ' AND UserID = ' + CAST(@UserID AS Nvarchar(200))
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2011 at 9:07 am
OK, that worked. Any idea why?
And thank you!
February 1, 2011 at 10:36 am
Melanie Peterson (2/1/2011)
OK, that worked. Any idea why?And thank you!
Yes, because of conversion to different types and the defaulting of VARCHAR when you don't put numbers on it.
I modified it so they're all nVARCHAR instead of a multi-type of VARCHAR and nVARCHAR, and all the CASTS were explicitly defined. Something was forcing a truncation. The only thing I saw that could cause issues after seeing your declares was conversion issues. I don't remember all the rules to the conversion issues offhand, but it was worth taking a swing at since it was a quick and easy fix.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2011 at 10:57 am
OK, good to know, thank you very much!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply