• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 336
  • Last Modified:

ODBC vs SQL Server Express 2005

I have a C# application that currently uses SQL Server Express 2005 for it's database.  Several of my users are having problems with connections using Windows XP.  I am considering migrating to an ODBC type of engine.  The question is which is best for my application?  Here are my requirements:

1.  I would like to use MS Access for the database.
2.  I am currently using the T-SQL "LIKE" command in sevaral of my queries.
3.  I need a database engine that will support under a hundred thousand records.
4.  I would prefer something that is compatible with the SQL Server Express 2005 command structure, so I don't have to change all of my commands, just the objects.
5.  Hopefully, would be easy to programmatically migrate from the physical SQL Server database.

Any suggestions which would be best for my application?
0
rmmarsh
Asked:
rmmarsh
  • 7
  • 3
  • 3
  • +1
2 Solutions
 
AlexFMCommented:
AFAIK, MS Access doesn't perform well on hundred thousand records. Try to make tests with Access before continuing in this direction. Maybe it is more easy to solve connection problems.
0
 
rmmarshAuthor Commented:
How about less than 50 thousand records?  

I have tried and tried to fix the connection problem... it only occurs on "some" computers running XP (both Home and Prof)... I know MS had problems with it before release, but it works on my two machines, not on others.  That's why I thought ODBC would be a better way to go...

Your thoughts on ODBC?
0
 
wlfsCommented:
I agree with AlexFM, MS Access is not the right choice for - even moderately - large amounts of data. As a gut feeling, I wouldn't recomment its usage for 50.000 records.
Possible alternative databases: PostgreSQL, mySQL, ...

> I would prefer something that is compatible with the SQL Server Express 2005 command structure
That depends very much on your commands. Do you use standard SQL only or T-SQL specific tricks and SQL-Server specific stored procedures?
LIKE is nothing specific to T-SQL, it's standard SQL and should work in every DB.

What are your connection problems? I think you should be able to solve the connection in significantly less time than migrating the db.
Which connection string do you use, i.e. do you utilize Windows integrated security or explizit username/password? For integrated security, check whether the logged in Windows user has the SQL-Server right to connect to the database. Most of my connection problems with SQL-Server are due to user right management.

You can use ODBC to connect to SQL-Server. This way you can change the connection mechanism only, but keep the database as is. It's very straight forward and quick to install a User-DSN for SQL-Server under Windows.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rmmarshAuthor Commented:
H "wlfs"... answers to your question:

Q: "do you utilize Windows integrated security or explizit username/password"  A: integrated security
 
Now another one from me:

Q: What do you mean by "check whether the logged in Windows user has the SQL-Server right to connect to the database"?  Are you talking about Administrative access?  How do I check for proper rights programatically? or can I even check?  

Q:  what are the benefits for changing to ODBC to access the SQL Server Express 2005?

Thanks so much for your help...
0
 
wlfsCommented:
SQL-Server has a user management that is totally separated from the windows users on a single PC or domain. Using integrated security means that the C# program is trying to connect to SQL-Server with the same username and password as the current windows login. BUT it might well happen that the current windows username does not have the SQL-Server right to read that database. Or even worse, the current windows username might not even exist as a SQL-Server user.

SQL-Server does *not* automatically take over windows users as SQL-Server users. As far as I know, only Admin-Accounts are created by default in SQL-Server. Obviously, you haven't been aware of that problem yet, so it's possible that: On some PCs the user is logged in as Administrator (or with admin) rights. There the connection is fine. Non-admin users can't connect. Or, on some PCs the necessary SQL-Server users exist, on others they don't.

You can check this on the windows command prompt. On one of the PCs making trouble, type
  osql -E
for connecting to SQL-Server with integrated security, or
  osql -U username
for using a specific username. If osql -E stops immediately with the error message
  Login failed for user ...
than you know for sure it's about user rights. If you get to the osql prompt, type:
  use DataBaseName
  select * from TableName
  go
If that works alright, then user rights are fine and not the cause of your trouble. If it doesn't work, the error message is giving further hints. Hopefully.

Solutions (in case the problem is about user rights):
1) Use a different connection string with explicit username/password. The Visual Studio wizard for data adapters is guiding you through the respective db connectivity dialog. The simplest and *most insecure* approach is to use user 'sa' (SQL-Server admin) with its password. Still simple but better in terms of security: Create a new SQL-Server user, which has the minimum amount of rights that allow him to perform the tasks for your C# application, and use his credentials for the connection string.
2) Create all possible windows users (using your application) also as SQL-Server users with user-specific rights. Best approach security-wise, because you can assign different rights to different users. However, most overhead as well.

> what are the benefits for changing to ODBC to access the SQL Server Express 2005?
None, I guess. I was referring to your question, where you are looking for an easy way to change the connection interface to ODBC.
0
 
rmmarshAuthor Commented:
I tried your suggestion on my computer where normally I have no problems.  I got a message "Could not open a connection to SQL Server [2]"... does it make a difference if I am using SQL Server Express 2005?
0
 
wlfsCommented:
> does it make a difference if I am using SQL Server Express 2005?
Not as far as I know.

> Could not open a connection to SQL Server [2]
I wonder about the "2". Are you using two SQL-Servers on the same machine? Which did you try to logon to? Use "osql -L" to get a list of all available servers. Use the "-S" switch of osql to connect to a specific server. "osql -?" for all switches.
If you have the Enterprise Manager of a full SQL-Server version, you don't have to bother with osql but can use the Enterprise Manager to connect to SQL-Server Express and manage it.

> Could not open a connection
Are you using a software firewall blocking osql? My SW-firewall would block a connection to SQL-Server via osql if I hadn't explicitely allowed it.
0
 
rmmarshAuthor Commented:
Well, I have heard from 5 people now; all having problems logging on with XP Home edition.  I asked them to download and install the SQL Express Server Management tool, and they all tell me that they get a Dos box that opens and closes fast.

Any ideas?
0
 
ositearCommented:
2 questions

Have you tried turning windows xp firewall off?
That firewall sometimes is a real pain in the ass.


What does your connection string look like?

0
 
rmmarshAuthor Commented:
Yes, the firewall has been turned off (at least that's what they tell me)

Connection string is:

                bookConn = new SqlConnection("server=" + tsServerPath.Text + @"\SQLEXPRESS;Integrated security=SSPI;database=" + dataBaseName);
                bookConn.Open();

The serverpath is "local" and the databasename is "dbBooks"
0
 
ositearCommented:

Try
new SqlConnection("server=" + tsServerPath.Text + @"\SQLEXPRESS;Integrated security=SSPI;database=" + dataBaseName + @";Network Library=DBMSSOCN;");

And using IP address instead of machine name.

I see you are using integrated security, are your local user has access to the server?


0
 
ositearCommented:
You can always roll back to any MSDE prior to 2005. It's free, works as fine as SQL Server Enterprise, and I've created a lot of projects both with Integrated security and SQL authentication w/o any problems.
0
 
rmmarshAuthor Commented:
I think the problem lies with SQL Server Express and Windows XP Home edition... works fine on other OS

have notified Microsoft, but so far, no reply...
0
 
rmmarshAuthor Commented:
At the time I was doing the connect, the database name was not available yet (not instantiated)... thanks for the help
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now