Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simondeutsch
Aged Yak Warrior
547 Posts |
 Posted - 2002-02-27 : 11:24:06
|
| Hi!Am using VB6, SQL 7, and just changed the provider in the connectionstring from MSDASQL to SQLOLEDB bec. MSDASQL wasn't functioning properly with batch updates, and now the connection only goes through on the server running SQL. Another machine in the office which runs the VB files off the server freezes when it tries to connect, and after a little while gets an error "Specified SQL Server not found",nativeerror 6. The connectionstring syntax is "Provider=SQLOLEDB.1;DSN=MyDSN;UID=SomeUID;PWD=SomePWD;Persist Security Info=False". When the provider is MSDASQL, this problem doesn't occur, and the DSN definitely is pointing to a valid SQL server. Also, why isn't this error happening on the server running SQL server?Thanks!Sarah Berger MCSD |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-27 : 11:46:39
|
| OLE DB connection strings that use a native provider (like SQLOLEDB) shouldn't use DSNs, but rather the server and database name directly. Something like this:Provider=SQLOLEDB;Data Source=serverName;Initial Catalog=databaseName;User ID=username;Password=passwordPut the proper server name in place for Data Source, and the database for Initial Catalog, and you should be all set. |
 |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-02-27 : 11:58:24
|
| Thanks, but I think I do need to use DSNs, if possible, because I don't want to hardcode server and db details in the connectionstring. This is for an app with many clients, and them having all the same server name and database name can't be enforced. Right now I was using a dropdown which gave the user logging on a choice of what DSN they want to use to connect, and according to that they'd connect to a specific db and server.Sarah Berger MCSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-27 : 12:27:20
|
| I don't see how that's different from using a DSN; DSNs have a data source just like an OLE DB source. Simply modify your current application to list the available sources, but use the appropriate OLE DB connection string instead of DSN name. It's actually far more flexible than using DSNs because you can construct them on the fly very easily.If you must stick with DSNs then you have to stick with the ODBC provider. |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-02-27 : 12:35:48
|
My point about to DSNs is that they are external files independent of the program, and so can be easily changed. Also, passing a DSN means passing only one parameter which encapsulates several others.quote:
Simply modify your current application to list the available sources, but use the appropriate OLE DB connection string instead of DSN name. It's actually far more flexible than using DSNs because you can construct them on the fly very easily.
How do I get available sources?Thanks!!Sarah Berger MCSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-27 : 13:00:20
|
| Well let me ask this:1. If the user knows which DSN to use, it's fair to say they know which server they're connecting to, right?2. Also, if there are multiple DSNs that connect to the same server, but different databases, then the user knows which database they're using, correct? If there is only one DSN for that server, which database are they using? They'd have to know which one to choose, right?I don't know if this is the case or not, but it strikes me as pretty amazing that someone would say "I don't know what I want to connect to" and just grab a DSN at random. They'd have to have some idea where they're going.On that assumption, you could have:1. A dropdown list of the available servers. This is either predefined/hardcoded, or can be queried on one central server (not necessarily the one they want to connect to) that provides the list.2. A dropdown of available databases for that server, again either predefined or dynamically generated from a central database.3. A combination of the two that list the available server/database combinations, so that the user only has to choose one.Based on these inputs, it's very easy to construct the appropriate OLE DB connection string.Having a central server that lists the available servers and database is, IMHO, a lot easier to maintain than file DSNs. You can keep all the information for hundreds or even thousands in a single table. If you can provide some sample information on how you're keeping the DSNs now, and how the user chooses which one to use, I should be able to come up with some code that illustrates a way to do this. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-02-27 : 13:22:23
|
| Instead of Using DSN's you could store the CNN string to the different Servers in the registry. That gives you an easy way to update the servers on each of the boxes, plus you can iterate throught the list of servers from teh registey.If they are Windows 2000 Pro boxes, you could use a COM+ package, and use the IObject_Contruct to store your CNN string(s).Michael |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-02-27 : 13:22:25
|
| The user doesn't know where they're connecting to because they're being shielded by the DSN. E.G. if a doctor runs two practices (separate locations, different patients, and separate billing) that makes sense as two databases on one server. So I'd make two DSNs named Location1 and Location2 connecting to different databases on the same server, and the user chooses the practice to work in when logging in. The front end app is the same for both practices, of course. This is the most common situation. Now the user doesn't know anything about databases, servers, etc., they're simply logging in by choosing a DSN with a familiar name, while the server or db might have cryptic names with no meaning to the end user. (Try to ask them what server or db they're in, and you'll get a blank stare)It's definitely possible to use the central server idea, but that involves hardcoding the central server's name, and establishing a connection only to fill up the login screen, and then destroying that connection. I would like to avoid that... Also, as mentioned above, most common scenario involves users having only one server, with multiple databases. The combo I'm currently using with the DSN names is a usercontrol I found on the web which lists all available DSNs on the computer. Another way to do this is to use the ODBCTools from Microsoft. The DSNs would have to be created manually on each machine for each client. (The app is currently still in the dev. stage)Sarah Berger MCSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-27 : 13:51:04
|
How about a single file on the hard drive, just plain text, something like this:Name Server DatabaseMain St. Office SQL_DB1 Main_StBroadway Office SQL_DB1 BroadwayMercy Hospital MERCY_DB MercyDr. Smith's Office SQL_DB1 Dr_Smith This could also be done as a tab or comma-delimited file. The app can read this file off the hard drive and create an array listing the available locations by name. You could also popluate a multi-column dropdown box and set the other non-visible columns' width to zero. In any case, you have a list of familiar locations that also contain the server and database details. You could even do this:Name Connect Main St. Office Provider=SQLOLEDB;Data Source=SQL_DB1;Initial Catalog=Main_StBroadway Office Provider=SQLOLEDB;Data Source=SQL_DB1;Initial Catalog=BroadwayMercy Hospital Provider=SQLOLEDB;Data Source=MERCY_DB;Initial Catalog=MercyDr. Smith's Office Provider=SQLOLEDB;Data Source=SQL_DB1;Initial Catalog=Dr_Smith You can use this string as a foundation, and add the additional options to it as needed (user, password) within the app, based on username and password entered. You can make the file read-only so no one changes it. And if someone is determined to find out how to connect to the server, if they know enough to read this file then they know enough to get it from the DSNs too, so it's just as secure as a DSN (in fact, it's more secure since DSNs could contain usernames)The nice thing is that this file can be updated on demand. You could add a feature to your app to query a central location and have it generate this file. That way you don't have to email or install the files yourself, and the user only does it when necessary. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-02-27 : 13:56:41
|
| I like Rob's idea as well. That was the same idea I had, except in registry. .Net does many things similar to Rob's idea, but with an XML file.Michael |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2002-02-27 : 14:26:25
|
| Thanks a lot! I think I'll give it a shot!!SarahSarah Berger MCSD |
|
|
|
|
|
|
|
|
SQLOLEDB provider in connection freezes