"SQL Server does not exist or access denied" error on SQL Server 2005 Express -- sql server new users

sql server new users : "SQL Server does not exist or access denied" error on SQL Server 2005 Express

Robert D.
2/15/2006 2:09:20 PM

I've just installed SQL server express on my WinXP pro workstation. I'm
running everything locally. When I try to connect to the SQL server from an
Access project I get this error :

[DBNETLIB][ConnectionOpen (Connect())).] SQL Server is unavailable or does
not exist or access denied.

When I try to create a system DSN using the SQL driver or SQL native client
driver I get this error :

Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist
or access denied.

I'm desperate. I've disabled the Windows firewall, configured SQL server to
use both TCP and named pipes.

This KB article explains clearly my problem :

http://support.microsoft.com/default.aspx?scid=kb;en-us;328306

Anything else I should do or try?

Thanks!

Rick Byham [MS]
2/16/2006 8:48:44 AM

SQL Server 2005 Express installs as a named instance, by default
<computer_name>\sqlexpress. You may be having trouble resolving the instance
name, to the port number. The SQL Server Browser service will do that for
you. Try starting the service and opening UDP port 1434 in the firewall, or,
check SQL Server Configuration Manager to see what port Express is using,
configure it to use a fixed port, and then connect to <computer_name>,
<port_number>. Some detailed steps at
http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.

[quoted text, click to view]

Ken Halter
2/16/2006 10:42:37 AM

[quoted text, click to view]

fwiw, my experience is the opposite. I had to get rid of the \sqlexpress to
connect at all... the 3 PCs I have SQL Express installed on, all refuse to
let the Express service start... SQL Server's running, the browsers running,
Express service refuses to even start.... and finding help is a huge pain
(I'm used to VB5/6 where you type something into a code window, leave the
cursor where it is, hit F1 and help (with code samples) shows up on the
screen.)

It took me, literally, a full day to find out how to connect with SQL-DMO
using Windows Authentication. Only after downloading some 30+ samples from
PlanetSourceCode did I find a single sample of how to do it. One line of
code is all it took.....

SQL_DMOServerObject.LoginSecure = True

So, now, using SQL-DMO, I can create new DBs, new Tables, new Fields (or
"Columns" as they like to say 'round here <g>)... but can't add/change
records or figure out how to set the ID field to AutoIncrement (or, even if
I need to do so).... off to Google/PlanetSourceCode I go again <g> It's
either that or read the entire books on line to (hopefully) find a another
one line "fix" for my code... btw, I'm using SQL Server Express from VB6 so
there's very little sample code out there and even less help on the MS site
(VB6, despite being the most popular programming environment in the history
of the PC, is somehow "Evil" now, so....)... I end up having to use code
targetted at different SQL Server versions.

I don't mind admitting that I'm a complete newbie in SQL Server. We don't
write data driven apps here (not even close). I can read records all day
long from SQL db's but when it comes to creating/maintaining them, I'm lost
(for now)... Up until this point, all the DB access I ever had to do was
centered around Jet and Access DBs... easy stuff... for the most part....
and, dag-nabit, I want to use VB6 code to manipulate the DBs... not some
"Enterprise Manager" (that Express doesn't even have). We currenly only have
one project that requires "the server" and may never get another. Even so, I
want to get under the sheets a little bit <g> I'd much rather spend a week
or two on a utility that I fully understand ('cause I wrote it <g>) than
have to constantly do the same repetitive tasks over and over, forever,
using some full blown DB management software that's packed with options that
I'll never need (believe me... I do very little DB work. <g>)

--
Ken Halter - MS-MVP-VB (visiting from VB6 world) - http://www.vbsight.com
Please keep all discussions in the groups..

Ken Halter
2/16/2006 12:26:16 PM

[quoted text, click to view]

Now you tell me <g> Ok... got the stuff working with DMO (incredible <g>)...
off to the hunting grounds for SMO info..

--
Ken Halter - MS-MVP-VB (visiting from VB6 world) - http://www.vbsight.com
Please keep all discussions in the groups..

Ken Halter
2/16/2006 12:48:30 PM

[quoted text, click to view]

Well... so far, no luck finding SMO stuff for VB6. dotNet only, it looks
like.

The good news is... I'm in full control of the PCs this app will be running
on so, if it works here with DMO, it'll work there. Like I said, the db
stuff I have to do is simple, once it's setup. Basically, I'm interfacing
with a SCADA factory automation system that will be filling one DB with
records that I'll need and I process their requests and fill in a couple of
rows in another DB that they have. Nothing fancy. If their system was
compatible with access DBs, I'd still be using those. The most data I expect
to process is, maybe, 100-300 records in any of the DBs.... and that's
stretched over a period of several minutes to several hours (depends on the
process). No sorting, no reports, no nuttin'. Support for "bells and
whistles" is their responsibility (whew! <g>).

--
Ken Halter - MS-MVP-VB (visiting from VB6 world) - http://www.vbsight.com
Please keep all discussions in the groups..

Robert D.
2/16/2006 12:53:16 PM

Rick,

Thanks for the link! I needed that basic tutorial. It seem I forgot to
specify the instance name. I was trying to connect with the <computer_name>
only. Adding \sqlexpress solved eveything. I closed the ports, allowed only
local connections and everything is fine!

Thanks again!

[quoted text, click to view]

Ken Halter
2/16/2006 1:55:33 PM

[quoted text, click to view]

Well... I can say that... I use it every single day and plan to continue
using it until MS breaks their OS in a way that makes it impossible to use.
I've mentioned this in other groups too and, one issue for me is, code
reuse. How can I reuse code that's not compatible with dotNet? We have
countless DLLs that are fully debugged, documented, tested, re-tested and
tested again, sent to a 3rd party for testing and certification (we have
medical and food related customers) and migrating all of that to dotNet for,
really no benefit, other than a shiny new (but much slower) IDE would cost
this company far too much to even consider. After the conversion, we'd be
back at square one with the debugging, docs, testing, 3rd party tests, etc,
etc so it's just not an option.

Thinking about this little utility though, since it's basically "All New" I
may be able to get approval to write this in B#. As long as I can still
reference the B# wrapper in VB6 and get to the methods/events and properties
I need. I may experiment a bit with that (which is sad because the VB6
version is just about as complete as it needs to be). I should try it though
because "All New" is extremely rare around here. Usually, building an app
here consists of grabbing all DLLs in the list, creating an EXE that
references all of them and calls their OpenScreen method and traps their
CloseScreen event. Not much more to it than that and it's truly "RAD" and
the code is truly reusable.

[quoted text, click to view]

Thanks for that... already have it. Everything must be done using code
though. There are several reasons for that. First of all, being a
programmer, I'm a "control freak" <g>... secondly, the people that operate
the machinery we build aren't guaranteed to have an education, at all. Our
machines are shipped world wide and all we ever hear from sales is "Using
the UI can't be any harder than operating a cell phone". They'd probably
have a melt down if they had to open another app and do something <g>

[quoted text, click to view]

--
Ken Halter - MS-MVP-VB (visiting from VB6 world) - http://www.vbsight.com
Please keep all discussions in the groups..

Andrew J. Kelly
2/16/2006 3:05:55 PM

Just so you know it would be a mistake to create anew app that is made to
work against SQLExpress or SQL2005 using DMO. DMO has been replaced with
SMO and DMO will not support some of the feature set in 2005.

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Andrew J. Kelly
2/16/2006 4:11:14 PM

Ken,

Sorry I didn't catch the VB6 part. I didn't know anyone still used VB6<g>.
Just FYI there is a Beta of the Express Management tool available for
download. The final version should be out relatively soon but this may be
of help or save you some time:

http://www.microsoft.com/downloads/details.aspx?familyid=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&displaylang=en

--
Andrew J. Kelly SQL MVP

[quoted text, click to view]

Rick Byham [MS]
2/17/2006 8:00:06 AM

You sound like someone who may also be interested in using the sqlcmd.exe
command line utility that ships with all versions of SQL Server 2005. Just
letting you know it is there. For a 2 minute tutorial, see
http://msdn2.microsoft.com/en-us/library/ms170207(SQL.90).aspx
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.

[quoted text, click to view]

Ken Halter
2/17/2006 8:28:59 AM

[quoted text, click to view]

Thanks for that.... I guess I do need to learn at least some SQL scripting.
I'm sure it'll come in handy eventually. Especially if we get more customers
that want this functionality.

--
Ken Halter - MS-MVP-VB (visiting from VB6 world) - http://www.vbsight.com
Please keep all discussions in the groups..

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn