January 18, 2008 at 4:21 pm
I am trying to teach myself SQL Server, MS Press book shows how to create a linked server, but never shows how to access it in a query.
I have a machine named MIAMI with two instances of SQL Server 2005 installed. One as default instance and one as named instance SQLINSTANCE2. (This is really the MOC 2779b-MIA-SQL-01 machine)
I ran SSMS and created the linked server on MIAMI linked to MIAMI\SQLINSTANCE2. THe book was not really clear on the parameters i needed to specify.
When I tried the following query, I get an error about the \ character. How can I reference the instance in the query?
select * from MIAMI\SQLINSTANE2.adventureworks.humanResources.department ;
I asked SSMS to script the linked server and here it is:
/****** Object: LinkedServer [MIAMI\SQLINSTANCE2] Script Date: 01/18/2008 15:12:32 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MIAMI\SQLINSTANCE2', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'MIAMI\SQLINSTANCE2', @optname=N'use remote collation', @optvalue=N'true'
Thanks in advnace for your help,
Jesse Tutterrow
The error message is: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '\'
January 19, 2008 at 5:04 am
In order to get SQL to accept the special character ('\') you put open brackets [] around the phrase. Trusting the rest of your statements are fine, try this:
select * from [MIAMI\SQLINSTANE2].adventureworks.humanResources.department
Let me know if you need more assistance.
Toni
January 21, 2008 at 7:19 am
I added the square brackets and the error message changed to:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'miami\sqlinstance2.adventureworks.humanresources.department'
Any Ideas?
--Jesse
January 21, 2008 at 9:06 am
I tried it on a named instance on my "lab" (home set up) servers and it worked for me with
select * from [TMM\TRAINING].pubs.dbo.authors
Did you also run an sp_addlinkedsrvlogin command? That is one difference I think.
Toni
January 21, 2008 at 9:15 am
Have you verified that the user that you are impersonating against linked server have the appropriate permissions?
Couldn't you create the linked server with a name other than MIAMI\SQLINSTANCE2, for instance MIAMI_2?
Regards Ramon
Regards Ramon
June 17, 2009 at 7:51 am
Hi i had a similar situation so i put brackets in the query may this can help you.
select * from [linkeservername\instance].[database].[owner].
i have a linked server from sqlserver 2005 64 bits to sqlserver 2000 32 bit and i have to upgrade some store procedures for this achievement.
i hope this can help you out.
😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply