Cannot create ADO connection to DB anymore...

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

Maybe it's a SID type problem! Have you tried a trusted connection?

John

"(Pete Cresswell)" <x@.y.z> wrote in message
news:lkd4vv89m3cdb5fnlt4rt661c47sb56jdv@.4ax.com...
> 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

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

Cannot create a row of size 8075 which is greater than... (-214721

Hello all,
I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box that
sometimes fails when trying to update an ADO field type of adLongVarChar
(stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
mode) and produces the following error:
Error Number: -21472179
Error Description: Cannot create a row of size 8075 which is greater than
the allowable maximum of 8060.
The error seems to be occurring when we try to update the field with a large
string value. The strange thing is that it works without producing an error
sometimes. The reason I say this is that I can see some entries in my table
that have a DATALENGTH of the text field much larger than some of the ones
that are failing.
My VB6/ADO code looks like so:
'inserts a new record into a table with default values
sql = "call mydatadatable_insert "
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
myID= objRecordset.Fields.Item("ID").Value
objRecordset.Close
Set objRecordset = Nothing
'loads a 23 KB xml file
sXML = ReadFile("c:\temp\test.xml")
'...more processing here (non database)
sql = "exec mydatadatable_update" & cstr(myID)
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
'this is the problem area!!
objRecordset.Fields("FileTxt").Value = sXML
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing
I was thinking it might have something to do with the reference to ADO 2.5,
rather than 2.8 which is what comes with Windows Server 2003. But again, it
seems to be working some of the time.
I found this article on Microsoft's site:
http://msdn.microsoft.com/library/d...serr_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCulloughHi,
After reading your post I have one question.
Is XML file always the same size?
My opinion is that sometimes XML file (or string) is small enough to fit
into varchar (which I think you're using for data-type) and sometimes it os
too large (8075).
You could generaly solve the problem with using text or ntext as a datatype
in the affected column.
Danijel
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:593E87AE-3407-4ACB-9908-7F5A8969EAB8@.microsoft.com...
> Hello all,
> I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box
> that
> sometimes fails when trying to update an ADO field type of adLongVarChar
> (stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
> mode) and produces the following error:
> Error Number: -21472179
> Error Description: Cannot create a row of size 8075 which is greater than
> the allowable maximum of 8060.
> The error seems to be occurring when we try to update the field with a
> large
> string value. The strange thing is that it works without producing an
> error
> sometimes. The reason I say this is that I can see some entries in my
> table
> that have a DATALENGTH of the text field much larger than some of the ones
> that are failing.
> My VB6/ADO code looks like so:
> 'inserts a new record into a table with default values
> sql = "call mydatadatable_insert "
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> myID= objRecordset.Fields.Item("ID").Value
> objRecordset.Close
> Set objRecordset = Nothing
> 'loads a 23 KB xml file
> sXML = ReadFile("c:\temp\test.xml")
> '...more processing here (non database)
> sql = "exec mydatadatable_update" & cstr(myID)
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> 'this is the problem area!!
> objRecordset.Fields("FileTxt").Value = sXML
> objRecordset.Update
> objRecordset.Close
> Set objRecordset = Nothing
>
> I was thinking it might have something to do with the reference to ADO
> 2.5,
> rather than 2.8 which is what comes with Windows Server 2003. But again,
> it
> seems to be working some of the time.
> I found this article on Microsoft's site:
> http://msdn.microsoft.com/library/d...serr_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough

Cannot create a row of size 8075 which is greater than... (-214721

Hello all,
I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box that
sometimes fails when trying to update an ADO field type of adLongVarChar
(stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
mode) and produces the following error:
Error Number: -21472179
Error Description: Cannot create a row of size 8075 which is greater than
the allowable maximum of 8060.
The error seems to be occurring when we try to update the field with a large
string value. The strange thing is that it works without producing an error
sometimes. The reason I say this is that I can see some entries in my table
that have a DATALENGTH of the text field much larger than some of the ones
that are failing.
My VB6/ADO code looks like so:
'inserts a new record into a table with default values
sql = "call mydatadatable_insert "
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
myID= objRecordset.Fields.Item("ID").Value
objRecordset.Close
Set objRecordset = Nothing
'loads a 23 KB xml file
sXML = ReadFile("c:\temp\test.xml")
'...more processing here (non database)
sql = "exec mydatadatable_update" & cstr(myID)
Set objRecordset = New ADODB.Recordset
objRecordset.CursorLocation = adUseClient
Call objRecordset.Open(sql, objConnection, adOpenKeyset, adLockOptimistic)
'this is the problem area!!
objRecordset.Fields("FileTxt").Value = sXML
objRecordset.Update
objRecordset.Close
Set objRecordset = Nothing
I was thinking it might have something to do with the reference to ADO 2.5,
rather than 2.8 which is what comes with Windows Server 2003. But again, it
seems to be working some of the time.
I found this article on Microsoft's site:
http://msdn.microsoft.com/library/de...err_1_20hd.asp
Any help would be greatly appreciated!!!
Thanks in advance!
Brian McCullough
Hi,
After reading your post I have one question.
Is XML file always the same size?
My opinion is that sometimes XML file (or string) is small enough to fit
into varchar (which I think you're using for data-type) and sometimes it os
too large (8075).
You could generaly solve the problem with using text or ntext as a datatype
in the affected column.
Danijel
"brianpmccullough" <bmccullough11@.comcast.net> wrote in message
news:593E87AE-3407-4ACB-9908-7F5A8969EAB8@.microsoft.com...
> Hello all,
> I have some VB6 code using ADO 2.5 running on a Windows 2003 Server box
> that
> sometimes fails when trying to update an ADO field type of adLongVarChar
> (stored as a text column in a SQL Server 2000 database, 6.5 compatiblity
> mode) and produces the following error:
> Error Number: -21472179
> Error Description: Cannot create a row of size 8075 which is greater than
> the allowable maximum of 8060.
> The error seems to be occurring when we try to update the field with a
> large
> string value. The strange thing is that it works without producing an
> error
> sometimes. The reason I say this is that I can see some entries in my
> table
> that have a DATALENGTH of the text field much larger than some of the ones
> that are failing.
> My VB6/ADO code looks like so:
> 'inserts a new record into a table with default values
> sql = "call mydatadatable_insert "
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> myID= objRecordset.Fields.Item("ID").Value
> objRecordset.Close
> Set objRecordset = Nothing
> 'loads a 23 KB xml file
> sXML = ReadFile("c:\temp\test.xml")
> '...more processing here (non database)
> sql = "exec mydatadatable_update" & cstr(myID)
> Set objRecordset = New ADODB.Recordset
> objRecordset.CursorLocation = adUseClient
> Call objRecordset.Open(sql, objConnection, adOpenKeyset,
> adLockOptimistic)
> 'this is the problem area!!
> objRecordset.Fields("FileTxt").Value = sXML
> objRecordset.Update
> objRecordset.Close
> Set objRecordset = Nothing
>
> I was thinking it might have something to do with the reference to ADO
> 2.5,
> rather than 2.8 which is what comes with Windows Server 2003. But again,
> it
> seems to be working some of the time.
> I found this article on Microsoft's site:
> http://msdn.microsoft.com/library/de...err_1_20hd.asp
> Any help would be greatly appreciated!!!
> Thanks in advance!
> Brian McCullough

Cannot connect using ADO with Excel Microsoft Jet Provider 4.0

Hi:

I have a vb 6 app that exports data from sql to excel. The user has the ability to select either local or server. When they select server, the connection string is modified to include the server name instead of the local msde instance to look like this:

"provider=sqloledb;data source=MyServer;initial catalog=MyDatabase;user id=UserX;password=PasswordX;database=MyDatabase"

I'm using Microsoft Jet Provider 4.0 and OpenDataSource.

SELECT * INTO TempXL FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Sheet1$]

This code works fine when user is accessing msde on local, but when using above DSN, I receive the following message:

"2147217900 - OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEdB.4.0' IDBInitialize:: Initialize returned 0x800040005: The provider did not give any information about the error.]

When I trace the connection, these error messages occur:

Failed to set proper user name ('NT AUTHORITY\SYSTEM') for the connection

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot alter table 'TblXMyTable' because this table does not exist in database 'master'. (State 42S02) (Code 4902)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'TblxMytable'. (State 42S02) (Code 208)

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error. (State 42000) (Code 7399)

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]. (State 01000) (Code 7300)

I am especially puzzled by the second message because I am specifying the database name in my connection string as:

"provider=sqloledb;data source=MyServer;initial catalog=MyDatabase;user id=UserX;password=PasswordX;database=MyDatabase"

I can read from the server copy and copy to Excel, but I cannot write back to SQL. Can someone please tell me what I'm doing wrong?

Thanks in advance for any help.,

I had a problem similar to this when working with VB6, DAO, ADO and SQL Server 2005.

One user could do a query, another user could not.

In fact, the error message was similar to yours. The error message claimed that the table we were trying to query did NOT EXIST.

So, I believe your problem lies with Security.

Try making the user a database owner of the database in question. If this solves your problem, then you can look for a way to set the permission/security correctly for the specifc tables they need access to.

Cannot connect using ADO with Excel Microsoft Jet Provider 4.0

Hi:

I have a vb 6 app that exports data from sql to excel. The user has the ability to select either local or server. When they select server, the connection string is modified to include the server name instead of the local msde instance to look like this:

"provider=sqloledb;data source=MyServer;initial catalog=MyDatabase;user id=UserX;password=PasswordX;database=MyDatabase"

I'm using Microsoft Jet Provider 4.0 and OpenDataSource.

SELECT * INTO TempXL FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Sheet1$]

This code works fine when user is accessing msde on local, but when using above DSN, I receive the following message:

"2147217900 - OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEdB.4.0' IDBInitialize:: Initialize returned 0x800040005: The provider did not give any information about the error.]

When I trace the connection, these error messages occur:

Failed to set proper user name ('NT AUTHORITY\SYSTEM') for the connection

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot alter table 'TblXMyTable' because this table does not exist in database 'master'. (State 42S02) (Code 4902)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'TblxMytable'. (State 42S02) (Code 208)

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error. (State 42000) (Code 7399)

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]. (State 01000) (Code 7300)

I am especially puzzled by the second message because I am specifying the database name in my connection string as:

"provider=sqloledb;data source=MyServer;initial catalog=MyDatabase;user id=UserX;password=PasswordX;database=MyDatabase"

I can read from the server copy and copy to Excel, but I cannot write back to SQL. Can someone please tell me what I'm doing wrong?

Thanks in advance for any help.,

I had a problem similar to this when working with VB6, DAO, ADO and SQL Server 2005.

One user could do a query, another user could not.

In fact, the error message was similar to yours. The error message claimed that the table we were trying to query did NOT EXIST.

So, I believe your problem lies with Security.

Try making the user a database owner of the database in question. If this solves your problem, then you can look for a way to set the permission/security correctly for the specifc tables they need access to.

Cannot connect to locally SQL Server in C#.net 2005 Express

I am trying to do the Chapter 1 project of Getting Started with ADO.net 2.0 Step by Step (microsoft press)...

I enter the data source configuration wizard and select database, and press next.

I then press the new connection button, and the Choose data source screen appears.

In the List are only 2 options:

Microsoft Access Database File

Microsoft SQL Server Database File.

The option they want me to choose does not appear in the list.

Microsoft SQL Server sqlclient

I tried following the instructions in this thread [http://forums.microsoft.com/msdn/showpost.aspx?postid=85686&siteid=1] where a user had a similar problem, but had no success.

I have reinstalled C#, reinstalled SQL Server Express 2005 with Advanced Tools, but neither helped.

Any help would be greatly appriciated.

sao123

This seems like a difference between Express and Pro edtions of these products. The connections between VS and SQL are slightly different in the Express Edtions of VS. I've read parts of this book and you have to modify the connection instruction when you're using an Express Edition, the book assumes you're using VS Pro.

I found you could either must make a copy of the database into each project and change the connections to be User Instance based, our you could manually Attach the database to the SQL Express Parent and then modify the connection strings manually an not follow the instructions in the book.

Mike

Cannot connect to locally SQL Server in C#.net 2005 Express

I am trying to do the Chapter 1 project of Getting Started with ADO.net 2.0 Step by Step (microsoft press)...

I enter the data source configuration wizard and select database, and press next.

I then press the new connection button, and the Choose data source screen appears.

In the List are only 2 options:

Microsoft Access Database File

Microsoft SQL Server Database File.

The option they want me to choose does not appear in the list.

Microsoft SQL Server sqlclient

I tried following the instructions in this thread [http://forums.microsoft.com/msdn/showpost.aspx?postid=85686&siteid=1] where a user had a similar problem, but had no success.

I have reinstalled C#, reinstalled SQL Server Express 2005 with Advanced Tools, but neither helped.

Any help would be greatly appriciated.

sao123

This seems like a difference between Express and Pro edtions of these products. The connections between VS and SQL are slightly different in the Express Edtions of VS. I've read parts of this book and you have to modify the connection instruction when you're using an Express Edition, the book assumes you're using VS Pro.

I found you could either must make a copy of the database into each project and change the connections to be User Instance based, our you could manually Attach the database to the SQL Express Parent and then modify the connection strings manually an not follow the instructions in the book.

Mike