UDL Creation

[JayKusch]

Have an intersting situation in creating a UDL file. Have made plenty of them through the UDL creation app but in this case I need to create on manually.

SO ... I did as all the MS KB articles suggest by opening a text file, adding the needed connection strings and associated text and then saving it as a UDL.

The new file changes its icon to that of a proper UDL file and all looks great UNTIL you try to click and open it. It comes back w/ the error of:

C:\Documents and Settings\User\Desktop\Test.udl : File cannot be opened. Ensure it is a valid Data Link file.

I am using MDAC 2.8 RTM.

ANY IDEAS OR SUGGESTIONS????

Thanks

J. Kusch

 

[yksvaan]

Make an empty text file, rename it as .udl and open it. Later you can manually edit .udl with text editor.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[JayKusch]

I was able to finally accomplish the creation of a Dynamically created UDL. Here is the code for the SP...

ALTER  PROCEDURE CreateDynamicUDL

           @ConnectionString      nVarChar(4000),
           @FileName              nVarChar(256)

AS

CREATE TABLE ##UDL (

           LineNumber     Int IDENTITY PRIMARY KEY,
           UDLtxt             nVarchar(4000) NOT NULL
)

INSERT INTO ##UDL VALUES ('[oledb]')

INSERT INTO ##UDL VALUES ('; Everything after this line is an OLE DB initstring')

INSERT INTO ##UDL VALUES (@ConnectionString)

DECLARE @SQL_Command            nVarChar(4000)

SET    @SQL_Command       = 'BCP "SELECT UDLtxt FROM ##UDL ORDER BY LineNumber" queryout "'+@FileName+'" -T -w'

EXEC Master..xp_cmdshell @SQL_command

DROP TABLE ##UDL

To execute it, pass the init string and the udl file name and path where you want the udl file created.

EXEC  CreateDynamicUDL  

‘Provider=SQLOLEDB.1;Password=Password#1;Persist Security Info=True;User ID=My_Access;Initial Catalog=MyDatabase;Data Source=MySQLSERVER’,

‘C:\DynamicUDL.udl’

Thanks

J. Kusch

 

[yksvaan]

Why ?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]