All geek questions in one place

How to create the correct connection string using the shared memory protocol to connect a named instance of SQL server?

How to create a valid connection string using the shared memory protocol to connect a named instance of SQL server when the SQL server browser is disabled?

+4
source share
2 answers

The format of the connection string that I use to create the connection string in a C ++ application using ADO to access SQL Server is as follows.

"Provider=MSDASQL;DRIVER={SQL Server};SERVER=lpc:(local);DATABASE=%s;UID=; Password=;"

lpc: in the SERVER = value, the shared memory protocol is used to connect the server. % S is used to put the specific database name in the actual control string generated from this format.

See SQL Connect String

+8
source

If you use [server_name] [instance name], (local) [instance name] or LOCALHOST [instance name] from the local computer, it will use shared memory while shared memory is turned on (directing it to the User Interface is different in 2005 and 2008 so I don’t know which version should I help you with). Unlike SQLCMD, etc., there is no way to tell the provider that you want to use shared memory , if for some reason it cannot use shared memory, it will use a different protocol by default (named pipes or TCP / IP) . You can check which protocols are used by looking at sys.dm_exec_connections:

 SELECT session_id, net_transport FROM sys.dm_exec_connections;

I did not test this with the SQL browser turned off, but I think this should not have any effect. FWIW, I always use TCP / IP, but I almost always avoid having applications on the local machine that need to connect to SQL Server - this is what lower-spectrum application servers use instead of taking memory and processor on the base server data (and therefore from SQL Server).

+3
source

Source: https://habr.com/ru/post/1301334/

More articles:


All Articles