How to create a view based on a linked server/instance

Question

Post reply

How to create a view based on a linked server/instance

RandomStream

Hall of Fame

Points: 3735
More actions
February 15, 2005 at 10:23 am

#85088

When I needed to create a view to a table on a different server, I'd run the following set of commands:
exec sp_addlinkedserver 'primary_db_server', N'SQL Server'
exec sp_addlinkedsrvlogin 'primary_db_server',FALSE,'sa','sa','service'
create view dbo.countrycode as select * from primary_db_server.mydb.dbo.countrycode
Now I'm having problem doing the same because the primary database has an instance name primary_db_server\SQL2000. I can still do the first two steps without a problem:
exec sp_addlinkedserver 'primary_db_server\sql2000', N'SQL Server'
exec sp_addlinkedsrvlogin 'primary_db_server\sql2000',FALSE,'sa','sa','service'
But the last step is returning a syntax error due to the \ in the instance name:
create view dbo.country as select * from primary_db_server\sql2000.mydb.dbo.countrycode
I've tried to enclose primary_db_server\sql2000 in single and double quotes but still can't get it to work.
Does anyone know how to work around this?
Thanking in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Put square brackets around it:

[primary_db_server\sql2000]

Good Hunting!

AJ Ahrens

webmaster@kritter.net

RandomStream Hall of Fame Points: 3735 More actions · Answer 2

Thanks, AJ.

Here is what I've done and got:

exec sp_addlinkedserver [GN5DB1\SQL2000], N'SQL Server'

(1 row(s) affected)

exec sp_addlinkedsrvlogin [GN5DB1\SQL2000],FALSE,'sa','sa','service'

(0 row(s) affected)

(1 row(s) affected)

create view dbo.ccsystemcontrol as select * from [GN5DB1\SQL2000].cms.dbo.ccsystemcontrol

Server: Msg 18452, Level 14, State 1, Procedure ccsystemcontrol, Line 11

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

I do see, at the end of step 2, a linked server called GN5DB1\SQL2000. But the result of step 3 still puzzles me.

Any ideas?

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 3

I think the problem is now you are trying to login with TRUSTED AUTHENTICATION but your server isn't configured for it. Check the linked server (under security) and check to see how you are configured to login... Something about impersonate or something to that affect

Good Hunting!

AJ Ahrens

webmaster@kritter.net

philcart SSC-Forever Points: 47794 More actions · Answer 4

A good tip for dealing with instances is to use a variation of the standard sp_addlinkedserver syntax and give the linked server a different name.

EG: sp_addlinkedserver 'Server_Inst', ' ', 'SQLOLEDB', 'Server\Inst'

Removes the need to worry about the square brackets

--------------------
Colt 45 - the original point and click interface

RandomStream Hall of Fame Points: 3735 More actions · Answer 5

Thanks, AJ, for pointing me to the security tab on the linked server. I manually typed in sa and its password and it helped.

And also thanks to Phill. Your tip has cleared a major headache for me.

And there was also something else wrong with my original statement:

create view dbo.ccsystemcontrol as select * from [GN5DB1\SQL2000].cms.dbo.ccsystemcontrol

The database name cms should have been cmsdb1. It varies from installation to installation and I missed this detail this time.