PRB: An Error Message Occurs With a Distributed Query That Has A Named Instance (252225)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q252225

SYMPTOMS

When you use a distributed query that has a SQL Server 2000 named instance with an OpenRowset function like the following: 
Select * from OpenRowset ('SQLOLEDB',[machineName\Shiloh];'sa';'', 'Select * from [pubs].[dbo].[jobs]')
this error message occurs:
-- [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'machineName\Shiloh'.
When you use four part names, or an Openquery with a named instance, you must use square brackets. For example:

[server\instance]

However, the Openrowset function does not accept square brackets.

CAUSE

This is by design.

In the OpenRowset case example, the server name is really the DATASOURCE OLE DB Connection property. For the SQLOLEDB provider this happens to be the name of the server, which is syntactically a string. Brackets ([]) do not quote anything with strings because they are used for quoting identifiers, a different syntactic construct.

WORKAROUND

Do not use square brackets with named instances with the OpenRowset function.

MORE INFORMATION

The following code example describes various ways that you can use a linked server and a pass through query to a named instance of SQL Server 2000. 
EXEC sp_addlinkedserver 'myServer\Shiloh', 'SQL Server'
EXEC sp_addlinkedsrvlogin 'myServer\Shiloh', 'FALSE', NULL, 'sa', ''

--OpenQuery needs [ ]
--
Select * from OpenQuery ([myServer\Shiloh], 'Select * from [pubs].[dbo].[jobs]')
-- OK
Select * from OpenQuery (myServer\Shiloh, 'Select * from [pubs].[dbo].[jobs]')
-- [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '\'.
Select * from OpenQuery ('myServer\Shiloh', 'Select * from [pubs].[dbo].[jobs]')
-- [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'myServer\Shiloh'.

-- Four part name needs [ ]
--
Select * from [myServer\Shiloh]. [pubs].[dbo].[jobs]
-- OK
Select * from myServer\Shiloh. [pubs].[dbo].[jobs]
-- [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '\'.

-- OpenRowset does not needs [ ]
--
Select * from OpenRowset ('SQLOLEDB','myServer\Shiloh';'sa';'', 'Select * from [pubs].[dbo].[jobs]')
--OK
Select * from OpenRowset ('SQLOLEDB','[myServer\Shiloh]';'sa';'', 'Select * from [pubs].[dbo].[jobs]')
-- [Microsoft][ODBC SQL Server Driver][SQL Server][DBNETLIB]SQL Server does not exist or access denied.

Select * from OpenRowset ('SQLOLEDB',[myServer\Shiloh];'sa';'', 'Select * from [pubs].[dbo].[jobs]')
-- [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'myServer\Shiloh'.
In the non-OpenRowset cases, the server-name is an IDENTIFIER in the syntax. A backslash "\" is valid in an identifier if and only if the backslash is a quoted-identifier, which the bracket [] does for you.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbprb KB252225
©2003 Microsoft Corporation. All rights reserved.