Microsoft KB Archive/252225

= PRB: An Error Message Occurs With a Distributed Query That Has A Named Instance =

Article ID: 252225

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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.

Keywords: kbprb KB252225

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.