How to query another server with stored procedure

[Mirku]

Hello!
I have this stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[test_proc] 
AS
BEGIN
	DECLARE @DestDB nvarchar(100)
	DECLARE @SQL nvarchar (2000)
	SET @SQL = NULL
	SELECT @DestDB = param1 from [SERVER1\SERVER1].master.dbo.CAConfig	
    SET @DestDB = '[SERVER1\SERVER1].' + @DestDB + '.dbo.TestDBFromServer1'
   SELECT * FROM @DestDB
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.CAC onfig 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

 SET SQL='SELECT * FROM '+@DestDB  
EXEC(SQL)

it works!

[Delerna]

Because in
[code=sql]
SELECT * FROM @DestDB
[/code]
@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

[Delerna]

In case you didn't know. A table variable is declared and used thus
[code=sql]
declare @tblVar table (field1 int,field2 varchar(10),fie ld3 float)

insert into @tblVar select 1,'Test',1.2
insert into @tblVar select 2,'String',5.2

select * from @tblVar
[/code]

it is a table that exists in RAM instead of on a disc

[Mirku]

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!