Frequently Asked Questions:

Table of content:

• Extend Stored Procedures

• Tools

Extend Stored Procedures

• xp_cmdshell does not work with XCOPY

• xp_enum_oledb_providers does not list all installed OLE-DB providers

Tools

• How can I force the use of a network protocol without changing the Client Network settings?

FAQ: xp_cmdshell does not work with XCOPY

PRB: XP_CMDSHELL Does Not Work with XCOPY (Q152134)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q152134
(Although not explicitly notes, this article/problem also applies to SQL Server 7.0 and 2000.)

Using XCOPY.EXE with xp_cmdshell does not work by default, since XCOPY.EXE expects input from the user, which is normally provided at the command prompt through a return after the command. This behavior can be mimicked by adding "< NUL:"  after the command text, which will effectively signal the XCOPY input stream.

So the following will make XCOPY work with xp_cmdshell:

exec master.dbo.xp_cmdshell N'xcopy c:\test1 c:\test2 < NUL:'

FAQ: xp_enum_oledb_providers does not list all installed OLE-DB providers

Applies to SQL Server 7.0 and 2000

xp_enum_oledb_providers does not list all installed OLE-DB providers on the system. If there are known providers that do not work in combination with SQL Server Linked Servers, these are excluded from the list. The following providers are excluded:

• DTSFlatFile

• MS Remote

• MSDataShape

• MSPersist

• Microsoft.Jet.OLEDB.3.51

• SampProv

• CustTran

These providers are excluded because they do not work in combination with Linked Servers or in case of the  Microsoft.Jet.OLEDB.3.51 provider because a later version is installed and should be used instead.

FAQ: How can I force the use of a network protocol without changing the Client Network settings?

If you know how Data Access API's like ODBC construct there connections for specific protocols, you can leverage this knowledge to force the usage of a certain Net Library protocol, without having to change any client side network settings or the creations of an client alias. (See Client Network Utility for more details in Books Online.)

The key to this information can be found in the Registry. In the LastConnect key in the Registry the Data Access API's store the last used connection.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetlib\LastConnect

For example:
Name:   GERTD00\DEV
Type:     REG_SZ
Data:     369229832:lpc:GERTD00\DEV

The name identifies the name of the server as you would normally enter this, the data part made of 3 or more parts.

Part 1: is some sort of (hash value) key (369229832 in the example above)
Part 2: is a protocol identifier (lpc in the example above)
Part 3: is the connect string that identifies the server name (GERTD00\DEV in the above example)

So part 2 and 3 are the interesting pieces.

Forcing named pipes to a named instance:

Forcing TCP sockets using port 1460:

osql -S tcp:gertd00,1460 -E -Q "select net_library from sysprocesses where spid = @@spid"

net_library
------------
TCP/IP
 

This technique even allows ISQL to SQL Server 2000 named instances!

Using named pipes:

isql -S np:\\.\pipe\mssql$dev\sql\query -E -Q "select net_library from sysprocesses where spid = @@spid"

net_library
------------------------
Named Pipes

Using shared memory (LPC)

isql -S lpc:GERTD00\DEV -E -Q "select net_library from sysprocesses where spid = @@spid"

net_library
------------------------
LPC

What are the protocol identifiers allowed:

Where can I find this information? In the Registry, specified in:
Key:        HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetlib
Value:     ProtocolsSupported
Type:       REG_MULTI_SZ
 

***

Questions or problems regarding this web site should be directed to Web Master.
Copyright © 1991-2005 SQLDev.Net. All rights reserved.
Last modified: 04/06/05.