October 11, 2011 at 2:34 pm
Hi,
I have a sp that looks like this:
CREATE PROCEDURE [dbo].[TEST]
(
@ServerName VARCHAR(100),
@DatabaseName VARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM [@ServerName].[@DatabaseName].dbo.Abcd
END
I want to add the [] in front of the Servernames and database name.
The above code fails with the following error below :
Could not find server '@ServerName' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
I have to use it in an execute sql task and get values for these parameters from SSIS variables .
Please help .
October 11, 2011 at 2:44 pm
This is really an SSIS question it seems..
First, you can't do it that way, period.
Why don't you explain what you are trying to accomplish and we'll help you try to solve that.
CEWII
October 11, 2011 at 2:48 pm
My server name is like for example ServerName\ABCD . So if I donot use the square bracket it will fail.
I have these Variables set up in SSIS .(ServerName and DatabaseName) .
I am not sure how to use them in the stored proc with the [] .
October 11, 2011 at 2:51 pm
What Elliot was eluding to is that you simply can't use a variable in the place of a server name like that. He was trying to find out some details about the task you are trying to accomplish to see if maybe there is a better way of doing what you are trying to do. This is mostly due to the fact that in order to do this the way you are trying requires dynamic sql. Often times there are better ways of handling this type thing. If you can explain your task we may have a good way of making it work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 2:54 pm
My point is you can't.
You are going about this the wrong way.
It sounds like you have a list of servers that you want to run basically the same command on, correct?
There is a fairly easy way to do that in SSIS. The way you are trying to do it requires a linked server for every server. This is not advised since an error on any server will usually kill the whole process. Within SSIS you can easily work around that.
CEWII
October 11, 2011 at 3:02 pm
You should be able to do this if you use an expression over creating a sql statement with parameters.
though i agree with the others this is not the best approach for looping through servers , a better way would be to use a dynamic connection manager.
October 11, 2011 at 3:15 pm
That would be one long expression...
CEWII
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply