I have installed SQL 2005 and have noticed that older applications using DB Library now connect with TCP/IP instead of Shared Memory. Has anyone else seen this problem? I have tried everything I know to get a shared memory connection, nothing works.
I have created a test program using the SQLTESTC,c sample program - just plain vanilla and it will not connect to the default instance (SQL 2000) using shared memory:
// Get server's computer name
Servername[0] = '\0';
printf ("\nEnter Name of SQL Server: ");
gets (Servername);
// Get Username
Username[0] = '\0';
printf ("\nEnter Username: ");
gets (Username);
// Get Password
Password[0] = '\0';
printf ("\nEnter Password: ");
gets (Password);
login = dblogin(); // get login record from DB-LIB
if ( Username[0] != '\0' )
{
// set the username
DBSETLUSER (login, Username);
// set the Password
DBSETLPWD (login, Password);
} else {
DBSETLSECURE( login );
}
DBSETLAPP(login, (char *)"Connection Test"); // set the application name
DBSETLVERSION(login, DBVER60);
// Now attempt to create and initialize a DBPROCESS structure
if ((dbproc = dbopen (login, Servername)) == NULL)
{
printf ("dbopen failed\n");
return (1); // exit program
}
// Get proc_info
proc_info.SizeOfStruct = sizeof(DBPROCINFO);
result_code = dbprocinfo(dbproc, &proc_info);
printf ("DBPROCINFO structure:\n");
printf ("ServerName: %s\n", proc_info.ServerName);
printf ("NetLibName: %s\n", proc_info.NetLibName);
printf ("NetLibConnStr: %s\n", proc_info.NetLibConnStr);
Viola, TCP/IP not shared memory.
Anyone have any ideas?
SQL Server 2005 implement Shared Memory using a different mechanism than SQL Server 2000 and previous version. In SQL Server 2005, Shared Memory is actually Local Only Named Pipes.
DB-Library is old style client. It won't be able to talk to SQL Server 2005 Shared Memory. By default, TCP will be tried after the connection fails with Shared Memory. That's why you see the behavior. You can enable Named Pipes on the server and configure the client protocols to try NP before TCP.
Is there any reason you want to use Shared Memory specifically? DB-Library is depeciated, I suggest to you move to SNAC. You will be able to use SQL Server 2005 Shared Memory using SNAC.
Thanks.
|||
Thanks for your input. However, as I mentioned, this is a legacy application and I am trying to connect to SQL Server 2000 or SQL Server 2005. You have answered part of the question, that I will not be able to connect to SQL Server 2005 via shared memory, but the question of why I cannot connect to SQL Server 2000 via shared memory remains. Any ideas?
Regards,
Bob H.
You should be able to connect to SS2000 using Shared Memory. Please use c:\windows\system32\cliconfg.exe to make sure you enable Shared Memory for clients (note a small check box). Let us know if you still cannot connect using Shared Memory.
Thanks.
|||
The Shared Memory protocol is enabled. I am unable to connect using Shared Memory. This is the problem I am writing about!
I can provide screen shots if you tell me where to send them.
Regards,
Bob H.
|||
Can you expose your registry tree under:
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\
here, you can ignore things under HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0
When was the last time you were able to use Shared Memory? Any major change since then? Also, make sure you have this file: \WINDOWS\system32\dbmslpcn.dll.
Thanks,
Here are the answers to your questions:
Xinwei Hong - MSFT wrote:
Can you expose your registry tree under:
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\
here, you can ignore things under HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client]
"SharedMemoryOn"=dword:00000001When was the last time you were able to use Shared Memory?
I don't know for sure, I just noticed this problem.
Any major change since then?
Installed SQL Server 2005.
Also, make sure you have this file: \WINDOWS\system32\dbmslpcn.dll.
I have it:
\WINDOWS\system32
20.0 KB (20,480 bytes)
Version: 2000.80.2039.0
Client-Side Local Inter-Process Communication (LPC) Net Library
Regards,
Bob H.
Thanks.|||
Here are the answers to your questions.
Sorry, I want the registry TREE, not just SharedMemoryOn. Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client]
"SharedMemoryOn"=dword:00000001
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
"DSQUERY"="DBNETLIB"
"WIN2KSRVB"="DBMSSOCN,WIN2KSRVB"
"Trillium"="DBMSSOCN,134.39.6.129"
"HEWLETT-FBHX3SO"="DBNMPNTW,HEWLETT-FBHX3SO"
"HEWLETT-F2D0B0E"="DBMSSOCN,192.168.3.5"
"BOBH-WIN-XP\\SQL_2005"="DBMSSOCN,BOBH-WIN-XP\\SQL_2005"
"BOBH-WIN-XP-SM"="DBMSSHRN,."
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\DB-Lib]
"AutoAnsiToOem"="ON"
"UseIntlSettings"="ON"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0]
"ProtocolsSupported"=hex(7):73,00,6d,00,00,00,74,00,63,00,70,00,00,00,6e,00,70,\
00,00,00,76,00,69,00,61,00,00,00,00,00
"ProtocolOrder"=hex(7):73,00,6d,00,00,00,74,00,63,00,70,00,00,00,6e,00,70,00,\
00,00,00,00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags]
"NumberOfFlags"=dword:00000002
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag1]
"Label"="Force protocol encryption"
"Value"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\GeneralFlags\Flag2]
"Label"="Trust Server Certificate"
"Value"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\LastConnect]
"BOBH-WIN-XP"="369229832:lpc:BOBH-WIN-XP"
"."="369229832:tcp:BOBH-WIN-XP,1433"
"134.39.6.129"="839057416:tcp:134.39.6.129,1433"
"(local)"="-33423352:lpc:BOBH-WIN-XP"
"192.168.3.5"="-33423352:tcp:192.168.3.5,1433"
"BOBH-WIN-XP\\sql_2005"="0:lpc:BOBH-WIN-XP\\sql_2005"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\np]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000001
"ProtocolName"="Named Pipes"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\np\Property1]
"Name"="Default Pipe"
"Value"="\\\\.\\pipe\\sql\\query"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\sm]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000000
"ProtocolName"="Shared Memory"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\tcp]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000003
"ProtocolName"="TCP/IP"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\tcp\Property1]
"Name"="Default Port"
"Value"=dword:00000599
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\tcp\Property2]
"Name"="KEEPALIVE (in milliseconds)"
"Value"=dword:00007530
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\tcp\Property3]
"Name"="KEEPALIVEINTERVAL (in milliseconds)"
"Value"=dword:000003e8
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\VIA]
"DLLname"="SQLNCLI"
"NumberOfFlags"=dword:00000000
"NumberOfProperties"=dword:00000002
"ProtocolName"="VIA"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\VIA\Property1]
"Name"="Default Server Port"
"Value"="0:1433"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SNI9.0\VIA\Property2]
"Name"="Default Client NIC"
"Value"="0"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib]
"ProtocolOrder"=hex(7):74,00,63,00,70,00,00,00,00,00
"Encrypt"=dword:00000000
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\LastConnect]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Np]
"DefaultPipe"="sql\\query"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\rpc]
"RPCProtocols"=hex(7):6e,00,6c,00,72,00,70,00,63,00,00,00,6e,00,61,00,63,00,6e,\
00,5f,00,6e,00,70,00,00,00,6e,00,61,00,63,00,6e,00,5f,00,69,00,70,00,5f,00,\
74,00,63,00,70,00,00,00,6e,00,61,00,63,00,6e,00,5f,00,73,00,70,00,78,00,00,\
00,00,00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\Tcp]
"DefaultPort"=dword:00000599
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSocketNetLib\VIA]
"RecognizedVendors"="Giganet,QLogic,ServerNet II"
"Vendor"=""
"DefaultServerPort"="0:1433"
"DefaultClientNIC"="0"
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\TDS]
"BOBH-WIN-XP"="7.0"
"lpc:BOBH-WIN-XP"="7.0"
"(local)"="7.0"
"Local"="7.0"
@.="7.0"
"."="7.0"
Can you make sure your SS2000 instance is running?
Yes it is running. Here is the output from the test program and the connection info.
Enter Name of SQL Server: .
Enter Username:
Enter Password:
SQL Server message 5701, state 2, severity 0:
Changed database context to 'master'.
DBPROCINFO structure:
ServerName: BOBH-WIN-XP
NetLibName: DBNETLIB
NetLibConnStr:
IDClientLoginNet LibraryApplication
- - --
51BOBH-WIN-XP\BobHTCP/IPConnection Test
52BOBH-WIN-XPBOBH-WIN-XP\BobHShared Memory Microsoft SQL Server Management Studio
Thanks.
Regards,
Bob H.
So, your SQL Server management studio is actually connected with SM. What's the result if you input your machine name? In addition, please delete any unused alias using cliconfg.exe.
Thanks,
|||Please find my answers inserted below.
So, your SQL Server management studio is actually connected with SM. What's the result if you input your machine name?
I get the same result.
In addition, please delete any unused alias using cliconfg.exe.
There are no unused aliases.
Thanks,
Why don't you simply try the sample program on your machine and see if it connects with Shared Memory or not?
Regards,
Bob H.
No comments:
Post a Comment