SQL Server 2008 R2 database name cannot be replaced by a string?

Question

I am trying to run a SQL Server 2008 R2 query in Management Studio on Win7.

I need to access 50+ databases with similar names.

But, I got error for a database name :

DECLARE @name1 VARCHAR(16)
SET @name1 = 'HisName_' 
DECLARE @whole_name VARCHAR(18)

DECLARE @a_person VARCHAR(2)
DECLARE @a_curosr CURSOR 
SET @a_curosr = CURSOR FAST_FORWARD 
FOR
   SELECT personName FROM @person_names -- @person_names  is a table that hold person names

OPEN @a_curosr
FETCH NEXT FROM @a_curosr INTO @a_person    

WHILE @@Fetch_Status=0
BEGIN
    SET @whole_name = @name1 + @a_person 

    INSERT INTO [dbo].[mytable]

       SELECT a.person_id
       FROM [@full_name].[dbo].[myOldTable] as a  -- error here, @full_name is Invalid object name

    FETCH NEXT FROM @a_curosr INTO @a_person
END

I have created the table with name as [@full_name].[dbo].[myOldTable] hre, @full_name is a string , e.g. HisName_ + a two-letter string.

Why the database name cannot be replaced as a string so that I can access 50+ databases in a loop ?

Thanks

@GoatCO, ok, so how to implement the function that I need ? thx ! — user3601704, Jul 21 '14 at 22:27

score 1 · Answer 1 · answered Jul 22 '14 at 09:40

I've tried to write your script with a dynamic sql statement. Please pay attention on the commented section (the sp_executeSQL part). Here is:

DECLARE @name1 VARCHAR(16)
SET @name1 = 'HisName_' 
DECLARE @whole_name VARCHAR(18)

DECLARE @person_names TABLE (personName varchar(2))

INSERT INTO @person_names (personName) VALUES ('AA')
INSERT INTO @person_names (personName) VALUES ('MD')
INSERT INTO @person_names (personName) VALUES ('AS')

DECLARE @sqlString AS nvarchar(MAX);

DECLARE @a_person VARCHAR(2)
DECLARE @a_curosr CURSOR 
SET @a_curosr = CURSOR FAST_FORWARD 
FOR
   SELECT personName FROM @person_names -- @person_names  is a table that hold person names

OPEN @a_curosr
FETCH NEXT FROM @a_curosr INTO @a_person    

WHILE @@Fetch_Status=0
BEGIN
    SET @whole_name = @name1 + @a_person 

    SET @sqlString = 'INSERT INTO [dbo].[mytable]
                        SELECT a.person_id
                        FROM [' + @whole_name + '].[dbo].[myOldTable] as a'

    SELECT @sqlString -- execute the statement on the next line instead
    --EXEC sp_executeSql @sqlString;

    FETCH NEXT FROM @a_curosr INTO @a_person
END

Hope this helps.

score 0 · Answer 2 · edited May 23 '17 at 11:44

0

The problem is that SQL Server will not expand variables into named identifiers. So [@full_name] is seeking a database by the name of "@full_name", which doesn't exist.

There are two approaches here.

One, use an SP to iterate all DBs in the instance: sp_msforeachdb

Two, use Dynamic SQL and modify it in your loop for execution: See answer here

edited May 23 '17 at 11:44

Community

1
1

answered Jul 21 '14 at 22:26

Jaaz Cole

3,119
1
15
20

SQL Server 2008 R2 database name cannot be replaced by a string?

2 Answers2