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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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?
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.
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

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...
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
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.
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?
> 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.
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?
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?

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);

The serverpath is "local" and the databasename is "dbBooks"

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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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...
rmmarshAuthor Commented:
At the time I was doing the connect, the database name was not available yet (not instantiated)... thanks for the help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.