Create an SQL Server DSN

 Keywords:  Create an SQL Server DSN qconfigdata

---

Question:

I am having a problem using qConfigData, to create a new SQL server user DSN. I keep getting the return value of Zero from qConfigData. Then I execute qConfigError, and it returns "Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed". I suspect it may have to do with the attributes string I am specifing. Here is my code:

ddExtender("wwodb34i.dll")
:create_datasource
c_msgtitle = "ODBC Utility"
c_datasource = "sample2"
  

;Create DSN
request=1;ODBC_ADD_DSN
driver="SQL Server";case sensitive

;attrs="DSN=TestDB|DESCRIPTION=Testing purposes|Driver=c:\winnt\system32\sqlsrv32.dll|LastUser=helpdesk|Server=%servername%|";pipe delimited
;attrs="DSN=TestDB|DESCRIPTION=Testing|Driver=C:\WINNT\System32\sqlsrv32.dll|LastUser=helpdesk|Server=ictsms01|";pipe delimited
;attrs="DSN=SAMPLE2|DESCRIPTION=Testing|DATABASE=SMS_TEST|DRIVER=C:\WINNT\System32\sqlsrv32.dll|SERVER=ictsms01|LASTUSER=helpdesk|";pipe delimited
attrs="DSN=SAMPLE2|DESCRIPTION=Testing|DATABASE=SMS_TEST|DRIVER=C:\WINNT\System32\sqlsrv32.dll|LASTUSER=helpdesk|";pipe delimited

displayflag=@TRUE
ret=qConfigData(request, driver, attrs, displayflag)
test = qConfigError() ;"Driver's ConfigDSN, ConfigDriver, or ConfigTranslator failed"

Answer:

Make sure you have the most current version of the ODBC extender. There was an issue with qConfigError, where it only returned a partial error string.

In general, you should be able to locate the necessary attribute keyword value pairs from the registry. First, manually create the user DSN from the ODBC administrator. Open Regedit.exe and locate the key [HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\YourDSNNameHere] these are the entries you will want to use in your Attributes parameter.

Also here is a link to the SQL Server documentation, for the connection strings....
http://msdn.microsoft.com/library/psdk/sql/od_odbc_c_99yd.htm

User Reply:

I finally got it to work with the following attribute string...

attrs="DSN=TESTING|DATABASE=SMS_ICT|SERVER=ICTSMS01|DESCRIPTION=TEST"

Here is the entire working code:

;Create DSN
DSNName = "SAMPLE"
database = "SMS_TEST" 
servername = "ictsms01"
DSNDesc = ""  
request=1;ODBC_ADD_DSN
driver="SQL Server";case sensitive
attrs="DSN=%DSNName%|DATABASE=%database%|SERVER=%servername%|DESCRIPTION=%DSNDesc%";pipe delimited
displayflag=@FALSE
ret=qConfigData(request, driver, attrs, displayflag)

---

Article ID:   W14778