CODEWITHSUNDEEP
sql-server
TimeTrap
TimeTrap

Reputation: 1142

Trying to select database based on server

Can anyone tell me why this doesn't work? 

if ((select @@servername) = 'ServerA')
begin
    use DatabaseA
    select top 5 * from dbo.SignUPRequest
end
else if ((select @@servername) = 'ServerB')
begin
    use DatabaseB
    select top 5 * from dbo.SignUPRequest
end

When I run this on ServerA, I get a message that DatabaseB doesn't exist on ServerA, which it doesnt, but I don't understand why it's trying to read if the second if evaluates to false.

Msg 911, Level 16, State 1, Line 8
Database 'DatabaseB' does not exist. Make sure that the name is entered correctly.

Upvotes: 1

Views: 1353

Answers (2)

Colin Mackay
Colin Mackay

Reputation: 19175

The code is parsed before it is run. When it is parsed SQL Server checks that it can access everything in the code, it cannot access the database that exists on the other server so the parsing step of running the code fails. As a result, you get the error message you've shown.

If you want to get around that you can put the code in the IF blocks as dynamically executed code (I always feel this is a bit of a hack/workaround).

DECLARE @sql NVARCHAR(4000);
if ((select @@servername) = 'ServerA')
begin
    SET @sql = 'use DatabaseA; select top 5 * from dbo.SignUPRequest;'
end
else if ((select @@servername) = 'ServerB')
begin
    SET @sql = 'use DatabaseB; select top 5 * from dbo.SignUPRequest'
end

EXEC (@sql)

So, what happens here is that you defer the parsing & running of the code that uses the appropriate database for the server to run time, as that what the EXEC statement at the end does.

UPDATE

Based on the additional comment below you could also rewrite this as:

DECLARE @sql NVARCHAR(4000);
if ((select @@servername) = 'ServerA')
begin
    select top 5 * from DatabaseA.dbo.SignUPRequest;
end
else if ((select @@servername) = 'ServerB')
begin
    select top 5 * from DatabaseB.dbo.SignUPRequest;
end

So, instead of putting in a USE <database-name> at the start, you can also more fully qualify the name of the table in your select statement. If you only have one line of SQL to deal with this could be more effective.

Upvotes: 2

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You get the error when the query is compiled, not on execution. You can execute the statements with exec to get them in a batch that compiles only if the database exists.

if ((select @@servername) = 'ServerA')
begin
   exec(N'use DatabaseA
          select top 5 * from dbo.SignUPRequest')
end
else if ((select @@servername) = 'ServerB')
begin
   exec(N'use DatabaseB
          select top 5 * from dbo.SignUPRequest')
end

Upvotes: 1

Related Questions