Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to create a view based on a linked server/instance Expand / Collapse
Author
Message
Posted Tuesday, February 15, 2005 10:23 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 1:00 PM
Points: 42, Visits: 96
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.


Post #161799
Posted Tuesday, February 15, 2005 12:06 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 22, 2011 5:35 AM
Points: 1,758, Visits: 9

Put square brackets around it:

[primary_db_server\sql2000]





Good Hunting!

AJ Ahrens


webmaster@kritter.net
Post #161839
Posted Tuesday, February 15, 2005 4:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 1:00 PM
Points: 42, Visits: 96
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?
Post #161895
Posted Tuesday, February 15, 2005 4:27 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 22, 2011 5:35 AM
Points: 1,758, 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
Post #161897
Posted Tuesday, February 15, 2005 4:46 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:01 AM
Points: 2,693, Visits: 1,173

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
Post #161902
Posted Tuesday, February 15, 2005 6:02 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, February 05, 2014 1:00 PM
Points: 42, Visits: 96
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.

Post #161916
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse