SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
RandomStream
RandomStream
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 130
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.
AJ Ahrens
AJ Ahrens
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4788 Visits: 9

Put square brackets around it:

[primary_db_server\sql2000]





Good Hunting!

AJ Ahrens


webmaster@kritter.net
RandomStream
RandomStream
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 130
Thanks, AJ.

Here is what I've done and got:

exec sp_addlinkedserver [GN5DB1\SQL2000], N'SQL Server'
(1 row(s) affected)
(1 row(s) affected)

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

(0 row(s) affected)
(0 row(s) affected)
(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
AJ Ahrens
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4788 Visits: 9
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
philcart
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9377 Visits: 1441

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




Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
RandomStream
RandomStream
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 130
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search