HKLM\\SOFTWARE\\ODBC\\ODBC.INI\\Name You Gave the DSN\n<\/PRE>\nTake a look at the registry keys and values required for that type of database, and then plug that information into our script. If you aren\u2019t sure how to do that, give us a week or two, and we\u2019ll see what we can do about getting some sample scripts for different ODBC databases posted in the Script Center.<\/P>\n
At any rate, here\u2019s a script that creates a DSN that uses SQL Server:<\/P>
Const HKEY_LOCAL_MACHINE = &H80000002<\/p>\nstrComputer = “.”<\/p>\n
Set objReg=GetObject(“winmgmts:{impersonationLevel=impersonate}!\\\\” & _ \n strComputer & “\\root\\default:StdRegProv”)<\/p>\n
strKeyPath = “SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources”\nstrValueName = “Script Repository”\nstrValue = “SQL Server”\nobjReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue<\/p>\n
strKeyPath = “SOFTWARE\\ODBC\\ODBC.INI\\Script Repository”<\/p>\n
objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath<\/p>\n
strKeyPath = “SOFTWARE\\ODBC\\ODBC.INI\\Script Repository”<\/p>\n
strValueName = “Database”\nstrValue = “Script Center”\nobjReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue<\/p>\n
strValueName = “Driver”\nstrValue = “C:\\WINDOWS\\System32\\SQLSRV32.dll”\nobjReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue<\/p>\n
strValueName = “Server”\nstrValue = “atl-sql-01”\nobjReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue<\/p>\n
strValueName = “Trusted_Connection”\nstrValue = “Yes”\nobjReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue\n<\/PRE>\n
Yes, we know, it\u2019s a bit long, but that\u2019s because there are a number of registry keys and values that have to be created. For the most part, though, the script is pretty straightforward. We start by setting the constant HKEY_LOCAL_MACHINE to &H80000002, the value required to connect to the HKLM portion of the registry. We then bind to the WMI service, and to the StdRegProv class (which – as we always remind you – happens to be in the root\\default namespace).<\/P>\n
From there we create a new registry value (HKLM\\SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources\\Script Repository), with Script Repository<\/B> the name we\u2019re giving to our new DSN.We set the value of this, well, value to SQL Server<\/B>, because that\u2019s the type of database we\u2019re connecting to.<\/P>\n
Got that? After that we create a new registry key<\/I>: HKLM\\SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources\\Script Repository (note that the key name is the same as our DSN Name). We then need to configure the following registry values:<\/P>\n
Database — <\/B>Name of the actual database we want to connect to. In this example, the database is named ScriptCenter<\/B>.<\/P>\n
Driver — <\/B>Path to SQL Server ODBC drive. In this example, that path is C:\\WINDOWS\\System32\\SQLSRV32.dll.<\/P>\n
Server — <\/B>Name of the server where the database is found. In this case, the server name is atl-sql-01<\/B>.<\/P>\n
Trusted_Connection — <\/B>Tells SQL Server to use our logon credentials when we access the database. This enables us to connect to the database without having to supply a user name and password.<\/P>\n
That\u2019s it; run the script, and you\u2019ll have yourself a fully-functioning DSN.<\/P>\n
And what about getting rid of that DSN once you\u2019re finished with it? Well, that\u2019s equally easy: after all, we just need to delete the registry keys and values our first script created. Here\u2019s a script that will delete the DSN Script Repository<\/B> : <\/P>Const HKEY_LOCAL_MACHINE = &H80000002<\/p>\nstrComputer = “.”<\/p>\n
Set objReg=GetObject(“winmgmts:{impersonationLevel=impersonate}!\\\\” & _ \n strComputer & “\\root\\default:StdRegProv”)<\/p>\n
strKeyPath = “SOFTWARE\\ODBC\\ODBC.INI\\Script Repository”\nobjReg.DeleteKey HKEY_LOCAL_MACHINE, strKeyPath<\/p>\n
strKeyPath = “SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources”\nstrValueName = “Script Repository”\nobjReg.DeleteValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName\n<\/PRE>\n
Note that what this script does is delete the first registry value we created (Script Repository), and then deletes the registry key we created. We don\u2019t have to delete the individual registry values within that key, because deleting HKLM\\SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources\\Script Repository will delete all the values as well.<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! Is it possible to create an ODBC Data Sources Name (DSN) on the fly and then remove it when done?— KC Hey, KC. Is it possible to create and then delete an ODBC DSN? To tell you the truth, we weren\u2019t entirely sure. As it turns out, however the answer is this: […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[31,26,3,5],"class_list":["post-71033","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-operating-system","tag-registry","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! Is it possible to create an ODBC Data Sources Name (DSN) on the fly and then remove it when done?— KC Hey, KC. Is it possible to create and then delete an ODBC DSN? To tell you the truth, we weren\u2019t entirely sure. As it turns out, however the answer is this: […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/71033","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=71033"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/71033\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=71033"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=71033"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=71033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n