I have this stored procedure:
Expand|Select|Wrap|Line Numbers
- 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
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
- SET SQL='SELECT * FROM '+@DestDB
- EXEC(SQL)