Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

ConnectionString with MSDE Case-Sensitive?

      
I'm using MSDE release A that serves as a local storage for caching.
I've setup another instance for my msde database.
Whenever I connect to the msde database, using the connectionstring property in SqlConnection(ADO.Net),
setting the Data Source Property to :
"Data Source="(LOCAL)\INSTANCENAME";Initial Catalog=Toys;User ID=sa;Password=foo;Persist Security Info=False;"

Opening the sqlconnection returns an sql exception saying "SQL Server does not exist or access denied."

however, setting datasource (local) to lowercase like :
"Data Source="(local)\INSTANCENAME";Initial Catalog=Toys;User ID=sa;Password=foo;Persist Security Info=False;"

opens the connection and works, lowercase (local) and . works but uppercase (LOCAL) doesn't!!!

I didn't encounter this problem in SQL Server...
Is MSDE case-sensitive?

mark duran
Thursday, April 21, 2005

I don't know the answer but answering these questions might help to explain what is going on:

If (local) is replaced with a server name, is that case-sensitive in the MSDE scenario as well?

Are the two (MSDE and SQL Server) instances that you are comparing set up as case-sensitive or case-insensitive?

Are the two instances that you are comparing using the same protocols?

If the quotes are removed from the Data Source specification then is the MSDE scenario still case-sensitive?

Does the SQL Server scenario that you are comparing with have quotes around the Data Source specification?

Mike Green
Friday, April 22, 2005

Thanks Mike, to answer your questions :
* If (local) is replaced with a server name, is that case-sensitive in the MSDE scenario as well?
- servername works fine, as in MYCOMPUTERNAME\INSTANCENAME, works, .(dot) and (local) lowercase works. only uppercase doesn't

* Are the two (MSDE and SQL Server) instances that you are comparing set up as case-sensitive or case-insensitive?
- case-insensitive, executing sp_helpsort on both the sql server database and msde to get the collation would get:
Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

* If the quotes are removed from the Data Source specification then is the MSDE scenario still case-sensitive?
    - removing the quotes behaves the same way with the uppercase (LOCAL)..

* Are the two instances that you are comparing using the same protocols?
- don't know, how can I tell?

* Does the SQL Server scenario that you are comparing with have quotes around the Data Source specification?
  - yes, removing quotes would work as well.

I need to find out what's causing this weird behavior.
I think it's not the msde database, but with ADO.Net don't know why, but I'm looking in that direction.

mark duran
Friday, April 22, 2005

Mark,

I thought that you would find a difference in the setup of your two configurations that would explain the discrepancy but it sounds as though you haven't. You can check on the protocols being used with the 'Server Network Utility' and the 'Client Network Utility'. If both of your configurations are only using local clients then in theory you'll be communicating via the shared memory protocol. See the following for more info:

http://support.microsoft.com/?kbid=814130

The only other question I thought of after posting is:
Is the SQL Server 2000 instance a named instance or the default unnamed instance? There is a very slim chance that it could make a difference if the connection was mapping to the default unnamed instance.

I'm inclined to agree with you that the difference in case sensitivity behaviour occurs before the connection reaches the database. Assuming that your two configurations are not both on the same single machine, I'd check for differences between machines in the following next:

- Windows type/version including patch level
- MDAC/ADO version including patch level
- .NET Framework version and patch level

Good luck.

Mike Green
Friday, April 22, 2005

*  Recent Topics

*  Fog Creek Home