468,107 Members | 1,315 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

How to query another server with stored procedure

I have this stored procedure:
Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  3. GO
  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
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
3 2753
1,134 Expert 1GB
Because in
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM @DestDB 
@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
1,134 Expert 1GB
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)
  3. insert into @tblVar select 1,'Test',1.2
  4. insert into @tblVar select 2,'String',5.2
  6. select * from @tblVar 
it is a table that exists in RAM instead of on a disc
Nov 27 '09 #3
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.

Similar topics

5 posts views Thread by Rob Wahmann | last post: by
10 posts views Thread by Thomas R. Hummel | last post: by
12 posts views Thread by John Scott | last post: by
2 posts views Thread by Adam Rogas | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.