<martin.douglas@lycos.com> wrote:

>I am posting the following information in hopes that it will help someone
>trying to accomplish a similar task. My search for this solution was
>fruitless across newsgroups, MSDN and Google.
>
>On my current project, there is a need for adhoc ODBC SQL Server server
>links created from a .NET Win32 application. Generally, to get a server
>link in SQL Server, one creates a system DSN on the database box and then
>sets up the link in the database. SQL Server Enterprise Manager provides a
>UI for this task. Additionally, there are stored procedures in the master
>database that can accomplish the same task.
>
>But if the goal is to create server links adhoc, this implies that system
>DSN's need to be created adhoc as well. My first step to ridding myself of
>that requirement was to limit the contents of the system DSN to just a few
>registry keys. This allowed me to only need to have one system DSN for any
>number of linked servers that utilize the SQL Server ODBC driver. The DSN
>registry file looks as follows.
>
>On Windows 2000:
>
>Windows Registry Editor Version 5.00
>
>[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SQL Server]
>"Driver"="C:\\WINNT\\System32\\SQLSRV32.dll"
>"Description"="SQL Server"
>
>On Windows XP or 2003:
>
>Windows Registry Editor Version 5.00
>
>[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SQL Server]
>"Driver"="C:\\WINDOWS\\System32\\SQLSRV32.dll"
>"Description"="SQL Server"
>
>You will notice that there are no keys that limit the system DSN to any
>server or catalog. From here one could configure any number of linked
>server entries without issues. However, my Win32 application does not have
>rights to create the DSN to start with. Additionally, the application needs
>to connect to many servers. So how do we go about completely ridding
>ourselves of the DSN? The answer is simply by supplying the linked server
>stored procedure provider string property with a full set of information.
>Here is my stored procedure that has no dependency on a system DSN.
>
>CREATE PROCEDURE [dbo].[sp_link_server](
> @server SYSNAME,
> @catalog SYSNAME,
> @username SYSNAME,
> @password SYSNAME
>)
>AS
>BEGIN
> DECLARE @provstr SYSNAME
> SET @provstr = 'Driver={SQL Server};Server=' + @server + ';Database=' +
>@catalog + ';Uid=' + @username + ';Pwd=' + @password
> -- Create the linked server
> EXEC [master].[dbo].[sp_addlinkedserver]
> @server = @server,
> @srvproduct = 'My Description',
> @provider = 'MSDASQL',
> @provstr = @provstr
>END
>GO
>
>EXEC [dbo].[sp_link_server] 'myserver', 'mycatalog', 'myuid', 'mypwd'
>
>Notice that there is NO call to [master].[dbo].[sp_addlinkedsrvlogin] since
>the credentials are passed on @provstr. One could make calls to it if
>additional security settings are required.
>
>So the above stored procedure allows my application to make adhoc linked
>server connections to any server over ODBC. The only requirement is that
>the SQL Server ODBC driver be installed on the machine -- to my best
>knowledge, this is the case with any machine that has a recent SQL Server
>installation.
>
>I have tested my stored procedure connecting even from version 8.0 to 4.2!
>
>I hope this helps someone speed up their development in the future. For me,
>this has been a speed bump until now.
>
>-MD-
>