Cannot create ADO connection to DB anymore.

This is a semi-crosspost - tried it on comp.databases.ms-sqlserver, but haven't
made any progress. Meanwhile, I'm getting desperate...Client wants some
changes to an app and I can't get the thing running.
I guess my primary quesion has become: "How do I confirm that
Provider=SQLOLEDB.1 us installed and functioning on my system?"
--
I rebuilt my machine a few weeks back - re-installed the developer version...
The app in question can see all it's tables in the SQL back end via ODBC and
everything looks normal via Enterprise Manager, but I can no longer create
an ADO Connection.
Source code is unchanged, what's changed is the PC rebuild and consequent
re-installatin of SQL Server developer version.
I suspect it's something to do with the provider (at least that's the only thing
I can see in the .Connect string that's not verifiably correct...)
The error looks like this after I trap and format it:
---
12/30/03 21:34:30v0.71 Userid: UPQC on SAG
Proc: basADO: ADO_ConnectionCreate
-2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
not exist or access denied.
Errors encountered when trying to connect:
'''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
--Connect String:
Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
---
Can anybody see anything obvious to look for?
I'm thinking I need to know how to verify/validate the presence of provider
"SQLOLEDB.1"... But how?
--
PeteCresswell
--
PeteCresswellPete,
Yes, it looks like SQLOLEDB version 1 is no longer installed on that box.
Is there a particular reason why you need version 1?
If not, use what's known as the "VersionIndependentProgID",
simply: SQLOLEDB and this will use the latest version installed on the
machine.
James Hokes
"(Pete Cresswell)" <x@.y.z> wrote in message
news:dum6vv4e67q257m22kemdttda3h7pjmub2@.4ax.com...
> This is a semi-crosspost - tried it on comp.databases.ms-sqlserver, but
haven't
> made any progress. Meanwhile, I'm getting desperate...Client wants some
> changes to an app and I can't get the thing running.
> I guess my primary quesion has become: "How do I confirm that
> Provider=SQLOLEDB.1 us installed and functioning on my system?"
>
> --
> I rebuilt my machine a few weeks back - re-installed the developer
version...
> The app in question can see all it's tables in the SQL back end via ODBC
and
> everything looks normal via Enterprise Manager, but I can no longer create
> an ADO Connection.
> Source code is unchanged, what's changed is the PC rebuild and consequent
> re-installatin of SQL Server developer version.
> I suspect it's something to do with the provider (at least that's the only
thing
> I can see in the .Connect string that's not verifiably correct...)
> The error looks like this after I trap and format it:
> ---
> 12/30/03 21:34:30v0.71 Userid: UPQC on SAG
> Proc: basADO: ADO_ConnectionCreate
> -2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does
> not exist or access denied.
> Errors encountered when trying to connect:
> '''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist
or
> access denied.
> --Connect String:
> Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
> ---
>
> Can anybody see anything obvious to look for?
> I'm thinking I need to know how to verify/validate the presence of
provider
> "SQLOLEDB.1"... But how?
> --
> PeteCresswell
> --
> PeteCresswell|||RE/
>Yes, it looks like SQLOLEDB version 1 is no longer installed on that box.
>Is there a particular reason why you need version 1?
>If not, use what's known as the "VersionIndependentProgID",
>simply: SQLOLEDB and this will use the latest version installed on the
>machine.
Excellant suggestin in it's own right - I've been doing a lot of this stuff by
rote and didn't think about versioning...
Removed the version, same error - which suggests to me that there's no provider
of that name on the box.
Any idea how to install one? I'm guessing it's a download from the MS Web site
or my MSDN DVD set...but am a little leery of just "try this...try that..." for
fear of hosing the installation completely.
--
PeteCresswell|||Pete,
You might want to download microsoft's MDAC Checker. It does sound like
something's hosed - most likely just need to install the latest MDAC. That
will tell you if you have any mis-matched or missing files.
Here's a URL that tells what to do:
http://support.microsoft.com/default.aspx?kbid=301202&product=mdac
James Hokes
"(Pete Cresswell)" <x@.y.z> wrote in message
news:sl79vvo56du1nhspao0uoasmqdohfvcjna@.4ax.com...
> RE/
> >Yes, it looks like SQLOLEDB version 1 is no longer installed on that box.
> >
> >Is there a particular reason why you need version 1?
> >If not, use what's known as the "VersionIndependentProgID",
> >simply: SQLOLEDB and this will use the latest version installed on the
> >machine.
> Excellant suggestin in it's own right - I've been doing a lot of this
stuff by
> rote and didn't think about versioning...
> Removed the version, same error - which suggests to me that there's no
provider
> of that name on the box.
> Any idea how to install one? I'm guessing it's a download from the MS Web
site
> or my MSDN DVD set...but am a little leery of just "try this...try
that..." for
> fear of hosing the installation completely.
> --
> PeteCresswell|||(Pete Cresswell) (x@.y.z) writes:
> The app in question can see all it's tables in the SQL back end via ODBC
> and everything looks normal via Enterprise Manager, but I can no longer
> create an ADO Connection.
> Source code is unchanged, what's changed is the PC rebuild and consequent
> re-installatin of SQL Server developer version.
> I suspect it's something to do with the provider (at least that's the
> only thing I can see in the .Connect string that's not verifiably
> correct...)
> The error looks like this after I trap and format it:
> ---
> 12/30/03 21:34:30v0.71 Userid: UPQC on SAG
> Proc: basADO: ADO_ConnectionCreate
> -2147467259: [DBNETLIB][ConnectionOpen (Connect()).]SQL
> Server does not exist or access denied.
> Errors encountered when trying to connect:
> '''[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist
> or access denied.
> --Connect String:
> Provider=SQLOLEDB.1;SERVER=SAG;DATABASE=TRETS;UID=Trets;PWD=trets
> ---
The idea that SQLOLEDB should be missing seems to be an incorrect path
to me. In that case you would get a different message. To see what,
try Provider=NISSE.
No, the problem is that the SQL Server SAG cannot be be found. What happens
if you try connect to SAG from Query Analyzer or OSQL?
You may have to add SAG as an alias in the Client Network Utility.
Another possibility is that you have not enabled the network library
that SAG is listening to. Again, this can be checked in the Client
Network Utility. And in the Server Netowrk Utility on SAG, if you have
access to that machine.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||RE/
>The idea that SQLOLEDB should be missing seems to be an incorrect path
>to me. In that case you would get a different message. To see what,
>try Provider=NISSE.
" Error# 3706: Provider cannot be found. It may not be properly installed."
Sounds like you're correct.
>No, the problem is that the SQL Server SAG cannot be be found. What happens
>if you try connect to SAG from Query Analyzer or OSQL?
Dunno what OSQL is, but no problems at all using Query Analyser to do a SELECT *
FROM tlkpVendor... or using Enterprise Manager to open the same table,
returning all records
>You may have to add SAG as an alias in the Client Network Utility.
*Seems* to already be there.
>Another possibility is that you have not enabled the network library
>that SAG is listening to. Again, this can be checked in the Client
>Network Utility. And in the Server Netowrk Utility on SAG, if you have
>access to that machine.
Could you elaborate on that one - seems like the last possibility.
The only enabled protocols I've got are Named Pipes and TCP/IP - but that's what
I'd expect. Both utilities you've mentioned show these two protocols.
--
PeteCresswell|||(Pete Cresswell) (x@.y.z) writes:
> Dunno what OSQL is, but no problems at all using Query Analyser to do a
> SELECT * FROM tlkpVendor... or using Enterprise Manager to open the
> same table, returning all records
Don't know what OSQL is? Hey, Pete, how long have you been working with
SQL Server? It must be over year by now, isn't it?
Sorry, couldn't resist. OSQL is a command-line utility for queries, a more
primitive Query Analyzer if you like. Mainly useful if you want run
prepared SQL scripts from batch files.
Anyway, it is mysterious that you can access the server from the tools,
but not from the application code. This means that we can forget about
the network protocols that I discussed in my previous posting.
Connectivity problems are not my best game, so I'm fairly clueless
myself. The one difference with regards to the tools is that the
tools connect through ODBC and not OLE DB. But I cannot see that it
should matter. You could try to replace "Provider=SQLOLEDB" with
"Driver={SQL Server}", this will change provider to MSDASQL, OLE DB
over ODBC. But this is a poorer alternative, so we don't want to use
that.
Hm, you could also try changing SERVER= into Data Source=, which I
believe is the proper name for OLE DB.
No fantastic tips - I'm just clutching at straws.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||RE/
>No fantastic tips - I'm just clutching at straws.
Thanks for trying - I need anything I can get.
Tried the various Provider/Driver combinations, still no luck.
Changed my connect string to use the SA id/pw and no change.
I'm starting to grasp at my own straw: ADO...maybe there's something amiss
with my ADO. If ODBC works and Enterprise Manager can connect, what's left?
--
PeteCresswell|||RE/
>straws
Oh yeah...I re-installed the MDAC. Put 2.8 over top of 2.6.
Everything else still works: ODBC, Enterprise Manager...
--
PeteCresswell