Accessing Different Database on Different SQL Servers

Accessing Different Database on Different SQL Servers
I was trying to Access two databases on two different servers. I was usring four part name to SQlServer
“[sqlserver02].MyDatabase.dbo.aspnet_membership” but it was not letting me do that and was showing following error message.

Msg 7202, Level 11, State 2, Line 1
Could not find server ‘sqlserver02’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Though server was present physically and name was 100% correct. You might think I must be using four part sql server name like this “[sqlserver02sqlserver02].MyDatabase.dbo.aspnet_membership”.
I tried above but it didn’t work, I googled it and found I should if “sqlserver02” is being added to my sys.servers I tried to add using
Following Stored procedure which adds the server in sys.servers
sp_addserver ‘sqlserver02′,’local’
and I got the message
Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89
The server ‘sqlserver02’ already exists.
Then I used following select query
Select * from sys.servers
which returned all servers and I found that sqlserver02 name was different it was sqlserver02_Alias. I used “[sqlserver02_Alias].MyDatabase.dbo.aspnet_membership” and everthing worked fine.

Tags: , , ,

Comments are closed.