By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

How to query another server with stored procedure

P: 4
Hello!
I have this stored procedure:
Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. GO
  4.  
  5. ALTER PROCEDURE [dbo].[test_proc] 
  6. AS
  7. BEGIN
  8.     DECLARE @DestDB nvarchar(100)
  9.     DECLARE @SQL nvarchar (2000)
  10.     SET @SQL = NULL
  11.     SELECT @DestDB = param1 from [SERVER1\SERVER1].master.dbo.CAConfig    
  12.     SET @DestDB = '[SERVER1\SERVER1].' + @DestDB + '.dbo.TestDBFromServer1'
  13.    SELECT * FROM @DestDB
  14. END
  15.  
This stored procedure is on a machine called Server2 and i try to read some tables from Server1.
The database that i need to work with is on Server1,but the name changes every time that i reboot the machine and the name of this database is in the colon "param1" from [SERVER1\SERVER1].master.dbo.CAConfig so I have to get the database's name before doing a query to the desired table. (it is a database for WINCC software from Siemens)
Ok..the problem is that the procedure stops with an error at the SELECT command " Must declare the variable '@DestDB'."
Why is that??
If i change the SELECT code to
Expand|Select|Wrap|Line Numbers
  1.  SET SQL='SELECT * FROM '+@DestDB  
  2. EXEC(SQL)
it works!
Nov 23 '09 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
Because in
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM @DestDB 
  2.  
@DestDB is interpreted as a table variable
but you have declared it as nvarchar(100) which is not a table variable
Therefore you get the error which might be a bit cryptic because you have declared it.
A more sensible message might be "Type mismatch on @DestDB" because that is what it is.

Then again many error messages tend to be cryptic




In your second example you are using @DestDB
as a string and concatenating it to another string
The resulting string is then used as a dynamic query.
This is legal and correct and therefore works
Nov 27 '09 #2

Delerna
Expert 100+
P: 1,134
In case you didn't know. A table variable is declared and used thus
Expand|Select|Wrap|Line Numbers
  1. declare @tblVar table (field1 int,field2 varchar(10),field3 float)
  2.  
  3. insert into @tblVar select 1,'Test',1.2
  4. insert into @tblVar select 2,'String',5.2
  5.  
  6. select * from @tblVar 
  7.  
it is a table that exists in RAM instead of on a disc
Nov 27 '09 #3

P: 4
Thanks!
It makes sense!
I do not have very much experience with SQL and the code that I have is a mixture from diferents howto's!
Nov 28 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.