RE: ADO (VB6 to SQLExpress2005
- From: Tarh ik <Tarhik@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 13 Feb 2008 11:59:02 -0800
Hi Alan!
I had this same nasty issue long time ago. What I did to solve it is to
create an ODBC DSN to ensure I had communication with the database itself
using the user ID and password specified. Basically, I had four mayor
problems:
1. MSSQL 2005 Express installer likes to create an "instance" of the "SQL
Engine", which results in an independent Windows Service (kinda like Oracle
does with their listeners). So, to specify which "instance" I wanted to talk
to, I had to add it to the server name. Following your example, I had to
specify the server name as ".\SQLEXPRESS".
2. SQL has an option to listen to different ports. The ODBC Driver has the
option to try to find which one is. However, in order to be able to use this
feature, the current logged Windows User must have enough authority to look
for these ports. My user ID didn't have any authority with the server, so I
had to tell the ODBC DSN to not dynamically detrmine the port, and always use
the 1433 port instead.
3. Once I solved the last problem, I was then able to work around the
Firewall. I had to manually create an entry in the Windows Firewall to
specifically allow connections to the port 1433.
4. SQL Express by default does not allow SQL authentication. In order to be
able to connect, I had to enable this feature.
After these problems were solved, my VB6 app connected
successfully!!!!... but then there was this nasty performance issue with
ADODB that I had to deal with... Then again, that is another story, and will
be told in another post.
I hope it helps!!!!
Best Regards,
Tarh Ik
PS: This posting has been posted "AS IS"
"Alan Gillott" wrote:
I suspect that this has been answered 1000 times but....
I am connecting from an older VB6 program running on Vista with
SQLExpress2005 installed. it was a standard install and everything can't be
completely wrong because the Management Studio works. I added the database
(but no tables as the first thing the app does is deletes them all and
readds them). However, as creating a mixed mode instance is beyond the
abilities of my tiny mind (or rather MS has made it exceptionally
inconvenient) I have to recast my connection string (yes, naughty me was
using sa with no password), and, as is usual when messing with something
that works, it doesn't any more.
clearly I have misstepped somewhere: can any one lead me by the nose to
nirvana...
The latest attempt at connection is:
Provider=SQLOLEDB.1;Data Source=.;Initial Catalog=Circles;Integrated
Security=SSPI;Persist Security Info=False
According to my info Data source can be either . or (local) if i am just
using the default memory management option.
Error is [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
80004005
Any advice gratefully received: hair transplant also required <grin>
A
- References:
- ADO (VB6 to SQLExpress2005
- From: Alan Gillott
- ADO (VB6 to SQLExpress2005
- Prev by Date: Timeout problem when connecting from Vista clients to SQL Server
- Next by Date: Sql Server 2000 & Vb.net Connection Problem
- Previous by thread: Re: ADO (VB6 to SQLExpress2005
- Next by thread: Can't join tables from two databases using linked servers when DBs on same SQL instance
- Index(es):
Relevant Pages
|
Loading
