Working Project cannot access Databse on new PC after Publishing & Running Setup.

Question

4.00/5 (1 vote)

See more:

It seems to me that a lot of people are getting similar problems to this, but I can't find a definitive answer, an actual explanation of how to correct it, just a lot of do's and don'ts !!!

I have created a small C# Windows Form Project using Visual Studio C# Express, SQL Server Express 2008 R2 and MS SQL Server Management Studio using this PC. It works fine as it is and I now want to test it on my laptop.

I clicked Project / Publish & followed the Wizard until it created a Folder called Application Files, a "ClickOnce Application Deployment Manifest" file and a Setup file all within my Published Projects Folder.

I then copied these 3 files to a USB and ran the Setup on my laptop. It installed a number of things and then came up with an MS .net Framework Error message saying "Unhandled exception has occurred in your application. If you click Continue, the application will ignore this error and attempt to continue. If you click Quit, the application will close immediately. Cannot open database XXX requested by the login. The Login failed. The Login failed for user AAA"

Indeed, if I click Continue, the program starts to run and works until it tries to access the database, when I get the same message. Can somebody please explain clearly what I am doing wrong, or what I need to do to resolve this problem ?!?

Posted 14-Mar-12 6:07am

Gary Heath

Add a Solution

Comments

Dave Kreskowiak 14-Mar-12 12:27pm

Without seeing your connection string it's pretty difficult to say what's going on. Chances are good, though, that you have a misunderstanding of how SQL Server security works and you've got the connection string wrong because of it.

Gary Heath 14-Mar-12 12:32pm

Well, Dave, it's very basic, but you'd be right about me not understanding many things, not just the SQL Server security !!!

@"Data Source=.\SQLEXPRESS;Initial Catalog=KAELC_DB;Integrated Security=True"

deepakaitr12345 17-Mar-12 9:29am

Please Check the Remote services for the sql server and then try for it.

2 solutions

Solution 2

Sorry I never got back on this ... I gave up to be honest !!!

I'm now rewriting from scratch using VB.net & SQLCE (C# is just so confusing !) ... it seems smoother going so far, hopefully when I get to this point again I will understand a bit more !

Posted 14-Jul-12 0:18am

Gary Heath

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-03-14T06:44:00

Solution 1

The most likely problem is that you don't have SQL server installed on any PC the target can reach, and the connection string you are trying to access your database with doesn't point at it anyway.

Try running a command prompt and typing "OSQL -L" and pressing ENTER. If it works, then it will show you all the instances you can reach from your PC. If it doesn't run at all, then SQL is not installed on that PC.

If you are only using a single user database (i.e. it doesn't share data with other users, you might want to switch to SQLite or SQLCE as they are much easier to install and manage than SQL server or MySql.

Posted 14-Mar-12 6:44am

OriginalGriff

Comments

Gary Heath 14-Mar-12 13:02pm

OK, well my first understanding is that when you Publish a Project, it includes what will be needed inside it somewhere as defined by the Prerequisites & that is why SQL Server Express & .Net Framework are installed onto the new PC (my laptop in this case, which has a newly built WinXP on it with no "extras") if they are not already there (I have .Net Framework 3.5 SP1, Microsoft .Net Framework 4 (x86 & x64), SQL Server 2008 Express, Windows Installer 3.1 & Windows Installer 4.5 checked as Prerequisites), is that not the case ?

OriginalGriff 14-Mar-12 13:04pm

You should not include SQL Server in your installation. There are a couple of reasons:
0) You can only distribute SQL Server Express for copyright reasons - not SQL Server full version.
1) They may already have SQL Server installed on the network. If so, then they will presumably want to use that version.
2) If they do have SQL server installed and you start proliferating SQL server Express instances, you are going to annoy the heck out of the database administrator...
3) A single site installation of SQl Server is a lot more likely to be backed up than a number of scattered version under user control.
4) Sql server is quite complex for a "normal" user to install and administer - it is not a good idea!
5) It will destroy the primary advantage of using Sql Server over SqlCE or SQLite - multiuser access. If everyone installs their own copy of SQL server, then you will have multiple copies of your database, each used by a single person. This will cause some confusion, and (depending on how you wrote the original database) may take some considerable effort to combine into a single instance when the problem is realized.

OriginalGriff 14-Mar-12 13:04pm

...And it doesn't mean you got the string right!

Gary Heath 14-Mar-12 13:11pm

As for those commands on the Laptop, when I entered OSQL followed by anything I got the message "Error, no user selected. Try with -U or -E command". When I typed OSQL /? it suggested I use Sqlcmd. When I did so I got messages saying "HResult 0x2, Level 16, State 1. Named Pipes Provider: Could not open a connection to SQL Server [2]" ... and a lot more !!!

With regard to SQLite or SQLCE, this small project is part of my learning curve as I am supposed to be re-writing a big Basic program soon that will use C# & SQL Server, so I need to learn as much about it as I can !!!

Gary Heath 14-Mar-12 13:16pm

Yes, thanks, I've seen these statements of yours in a number of places, but not until I had already tried installing my Project onto the laptop, so presumably the SQL Server Express option that is checked in my Prerequisites, should be unchecked for the future & SQL Server Express should be installed as a download on any PC that doesn't already have it on it, is that right ? Should I then uninstall anything on the laptop that relates to SQL Server & re-install from the MS Download ?

Gary Heath 14-Mar-12 13:16pm

Sorry, what do you mean by "...And it doesn't mean you got the string right!" ?

OriginalGriff 14-Mar-12 13:20pm

Even if you have installed SQL server, or the PC already has one, it doesn't mean that the connection string that worked fine on your PC is going to work on the target. For example, if your development server is configured for Windows Authorization, and the target requires a login. Or if your string says "GaryHeathPC\SQLEXPRESS" and the target is "LAPTOP17\SQLEXPRESS". etc.

Gary Heath 14-Mar-12 19:17pm

Sorry about the delay, my baby swallowed one of my pills and we had to rush him to the hospital !!! They've kept him in overnight, but hopefully he'll be fine !!!

So what do I need to put in the String to get it to work ?!? Surely if you are supposed to write Projects that are to be deployed to other PCs there must be a way to do it ?!?

OriginalGriff 15-Mar-12 5:59am

I've been trying to do some software to automate this for a while now, on and off - but my problem is that the SqlDataSourceEnumerator returns a blank instance name for local SQL default instances - I get the PC name, but not whether it is SQLEXPRESS or SQLSERVER. Even when I do get that working, it can't tell if it is Windows Authorization or login, and if login what the details are.
So, at the moment, it looks like you need to ask your user...
Sorry!

Gary Heath 15-Mar-12 13:22pm

So what do I have to do ? I don't understand, this works on my PC but the name of my PC (MEDESKTOP\GARY) isn't in the String at all, but it is the "Owner" of the Database in MS SQL Server Management Studio. In this case, as it just a test and my laptop is called MYLAPTOP\GARY, what do I actually have to do / change (and where) in order to get it to work on the Laptop ?!?

OriginalGriff 15-Mar-12 15:06pm

Look at the actual connection string. It will be of the forms PCNAME\SQLINSTANCE, and may be (local)\SQLEXPRESS or .\SQLSERVER.
You need to look at the actual SQL server instance you are going to connect to. Can you get in via Sql Server Management Studio? If so, then the details you enter on the initial "Connect to Server" dialog are the ones you will need to use in your connection string.

Gary Heath 17-Mar-12 4:14am

Thanks for your help here OriginalGriff, I'll try this out over the weekend & let you know how I get on on Monday ...