Objects related to DB: failing

Showing posts with label failing. Show all posts

Friday, March 23, 2012

ODBC connection to SQL server failing

Hi, I have an access 2003 database which connect to sql server via a
DSN that goes through a VPN. The database works fine and everyone can
coonect from their normal computers but for anyone working on their
Laptops (IBM Think Pad T42's) they cannnot connect to the datasource.
The DSN fails upon connection attempt with :

Connection Failed:
SQLState: '01000'
SQL Server Error: 1326
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
(Connect()).
Connection Failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
or access is denied.

This has been driving me insane as i cannot understand why it works for
our desktop computers and not for our laptops. The version of MDAC is
the same on a computer that connects as it is on a laptop that doesn't.
TCP/IP is enabled on the server and is using the default 1433 port.
severs, desktops and laptops all patches and up to date with the latest
SP's. DSN has been dropped and re-created. Domain user has been put in
localAdmin group for laptop.

I hope i'm mising something really obvious...can someone please please
put me out of my misery and tell me they have come accross this before
and have a solution. Ive tried everything i can think of.

Cheers

DanDan (dan_barber2003@.hotmail.com) writes:
> Hi, I have an access 2003 database which connect to sql server via a
> DSN that goes through a VPN. The database works fine and everyone can
> coonect from their normal computers but for anyone working on their
> Laptops (IBM Think Pad T42's) they cannnot connect to the datasource.
> The DSN fails upon connection attempt with :
> Connection Failed:
> SQLState: '01000'
> SQL Server Error: 1326
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen
> (Connect()).
> Connection Failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
> or access is denied.

The problem is that the laptop somehow does not find the SQL Server.
This KB article discusses posible reasons:
http://support.microsoft.com/defaul...B;EN-US;q328306

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland. I had found that article and gone through (as best i
could) all the options but still had no joy. I have finally solved it
though. On our desktops we have microsoft firewall client installed and
enabled but on the laptops we dont. For some reason it seems that we
cannot connect through our proxy server and a connection to sql server
has to be done through this firewall client?? Even though i have solved
the problem i am still not sure why this happened??

Thanks again

Dan|||Dan (dan_barber2003@.hotmail.com) writes:
> Thanks, Erland. I had found that article and gone through (as best i
> could) all the options but still had no joy. I have finally solved it
> though. On our desktops we have microsoft firewall client installed and
> enabled but on the laptops we dont. For some reason it seems that we
> cannot connect through our proxy server and a connection to sql server
> has to be done through this firewall client?? Even though i have solved
> the problem i am still not sure why this happened??

It sounds a little funny, I will have to admit. But being nowhere
close to a Windows networking expert, I don't have any explanations.

But if you have the SQL connection working, and also have the firewall
enabled on the laptops, that sounds like a double win to me.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 21, 2012

ODBC connection failing on 64 version of Windows 2003 server

I have an application that talks to a SQL backend using an ODBC DSN connection. It works fine when running on a 32bit machine but fails in the 64bit environment. The DSN is setup properly on the 64bit box and the Test Connection returns sucess. However when my application tries to use the DSN to connect to the database I recieve the following error.

IM002 - Data source name not found and no default driver specified.

The data source name does exist, it's in the ODBC.INI and in registry. As mentioned above the Test Connection succeeds in the ODBC Administrator so logic would dictate that it's setup correctly.

Any help would be appreciated.

Is your application 64-bit or 32-bit?

One possibility is that the Test Connection dialog uses 64-bit version of the driver and yoru application looks for a 32-bit version (or vice-versa).

|||Yeah, I thought of that too but both my application and MDAC are 32 bit.|||

DSN’s for 32bit and 64bit are stored separately in registry, those in HKLM/HKCR are for 64bit, 32bit DSN’s are stored in WOW registry.

You need to use the 32bit odbc admin tool (from windows\syswow64) to create 32bit DSN’s for your 32bit application.

|||thank you so muchhhhhhh|||Thank you, Thank you, Thank you, Thank you!|||

Question - after created System DSN using 32bit odbc, I created a linked server using the DSN,

sp_addlinkedserver 'MyLinkedSrv', ' ', 'MSDASQL', 'MyDSN'

and run a open-query in the SQL Management Studio to the linked server, i get error returns, - The OLE DB provider "MSDASQL" has not been registered. This error is because of SQL Native Client but when I created a LinkedServer with SQLNCLI, I get an error - OLE DB provider "SQLNCLI" for linked server "MAMSLS1" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MAMSLS1" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

I am thinking because of the SQL management studio is 64bit, it won't use 32bit DSN.. Is there any way to run a query against to the linked server?

My system is Win2003 Enterprise 64bit running on Itenium and SQL 2005 server 64bit Enterprise edition. From this SQL server, I am trying to make a LinkedServer to InterBase database system. -- No problem with 32bit SQL 2005 (development) but this 64bit (production). Any insight would be appreciated.

Regards,

Sunny

|||

If you are setting up a linked server a connection is made from one SQL Server to another. If the first SQL Server is 64-bit then you need to create a 64-bit DSN. If it is 32-bit you need a 32-bit DSN.

|||

did anyone succeed in this on this with a sql2005 EE x64 sp1 to sql2000sp3a ?

Or it states the is an NCLI problem

- connectiontimeout (again and again and ......)

- OLE DB provider "SQLNCLI" for linked server "MySQl2000sp3a_03" returned message "Unspecified error".

OLE DB provider "SQLNCLI" for linked server "MySQl2000sp3a_03" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".

Msg 7311, Level 16, State 2, Line 3

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "MySQl2000sp3a_03". The provider supports the interface, but returns a failure code when it is used.

or it says :
Msg 7403, Level 16, State 1, Line 3

The OLE DB provider "MSDASQL" has not been registered.

None of the folowing definitions work :

|||

I just discouvered this thread with a KB link that may do the trick : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1

kb906954 did the trick

|||

I'm having similar issues

There is no equivalent of MSDASQL within 64 bit SQL, and from what i've read SQL Native client is meant to be used.

However I can't get this to see the 64 bit IBM ISeries ODBC DSN that I've configured within the 64bit ODBC setup

Anybody else having these problems?

|||

YES !!!

And the solution is: Execute odbccad32.exe in WOW directory. That's it !

www.mcfly.dj

|||

Hi,

I'm running SQL 2005 64 bit on the windows 2003 (64 bit box)

I'm trying to create a linked server using an ODBC driver which has a corresponding DSN

Using odbccad32.exe will work if you are using a 32 bit install of SQL 2005 on Windows 2003 (64bit)

but this doesn't work with a 64 bit instance of SQL 2005 as there is no MSDASQL provider to work with.

Has anybody managed to get the SQL native client provider SQLNCLI

in 64 bit SQL 2005 connected through a DSN (32bit or 64bit)?

Thanks, Richard

|||

Richard...see the following thread.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1239339&SiteID=1

|||

hey guys,

i have C# windows application which calles ODBC 32 bit provider. it is working very well with the 32 bit operating systems. My application is compiled targetting to any operating system.

When i install our application on the 64 bit operating systems, i am unable to connect to the 32 ODBC. Any one have idea how to call 32 bit ODBC from the 64 bit applications on the 64 bit operating systems

Please help in this.

regards

ODBC connection failing on 64 version of Windows 2003 server

IM002 - Data source name not found and no default driver specified.

The data source name does exist, it's in the ODBC.INI and in registry. As mentioned above the Test Connection succeeds in the ODBC Administrator so logic would dictate that it's setup correctly.

Any help would be appreciated.

Is your application 64-bit or 32-bit?

One possibility is that the Test Connection dialog uses 64-bit version of the driver and yoru application looks for a 32-bit version (or vice-versa).

|||Yeah, I thought of that too but both my application and MDAC are 32 bit.|||

DSN’s for 32bit and 64bit are stored separately in registry, those in HKLM/HKCR are for 64bit, 32bit DSN’s are stored in WOW registry.

You need to use the 32bit odbc admin tool (from windows\syswow64) to create 32bit DSN’s for your 32bit application.

|||thank you so muchhhhhhh|||Thank you, Thank you, Thank you, Thank you!|||

Question - after created System DSN using 32bit odbc, I created a linked server using the DSN,

sp_addlinkedserver 'MyLinkedSrv', ' ', 'MSDASQL', 'MyDSN'

I am thinking because of the SQL management studio is 64bit, it won't use 32bit DSN.. Is there any way to run a query against to the linked server?

Regards,

Sunny

|||

did anyone succeed in this on this with a sql2005 EE x64 sp1 to sql2000sp3a ?

Or it states the is an NCLI problem

- connectiontimeout (again and again and ......)

- OLE DB provider "SQLNCLI" for linked server "MySQl2000sp3a_03" returned message "Unspecified error".

Msg 7311, Level 16, State 2, Line 3

or it says :
Msg 7403, Level 16, State 1, Line 3

The OLE DB provider "MSDASQL" has not been registered.

None of the folowing definitions work :

|||

I just discouvered this thread with a KB link that may do the trick : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1

kb906954 did the trick

|||

I'm having similar issues

There is no equivalent of MSDASQL within 64 bit SQL, and from what i've read SQL Native client is meant to be used.

However I can't get this to see the 64 bit IBM ISeries ODBC DSN that I've configured within the 64bit ODBC setup

Anybody else having these problems?

|||

YES !!!

And the solution is: Execute odbccad32.exe in WOW directory. That's it !

www.mcfly.dj

|||

Hi,

I'm running SQL 2005 64 bit on the windows 2003 (64 bit box)

I'm trying to create a linked server using an ODBC driver which has a corresponding DSN

Using odbccad32.exe will work if you are using a 32 bit install of SQL 2005 on Windows 2003 (64bit)

but this doesn't work with a 64 bit instance of SQL 2005 as there is no MSDASQL provider to work with.

Has anybody managed to get the SQL native client provider SQLNCLI

in 64 bit SQL 2005 connected through a DSN (32bit or 64bit)?

Thanks, Richard

|||

Richard...see the following thread.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1239339&SiteID=1

|||

hey guys,

i have C# windows application which calles ODBC 32 bit provider. it is working very well with the 32 bit operating systems. My application is compiled targetting to any operating system.

Please help in this.

regards

ODBC connection failing on 64 version of Windows 2003 server

IM002 - Data source name not found and no default driver specified.

The data source name does exist, it's in the ODBC.INI and in registry. As mentioned above the Test Connection succeeds in the ODBC Administrator so logic would dictate that it's setup correctly.

Any help would be appreciated.

Is your application 64-bit or 32-bit?

One possibility is that the Test Connection dialog uses 64-bit version of the driver and yoru application looks for a 32-bit version (or vice-versa).

|||Yeah, I thought of that too but both my application and MDAC are 32 bit.|||

DSN’s for 32bit and 64bit are stored separately in registry, those in HKLM/HKCR are for 64bit, 32bit DSN’s are stored in WOW registry.

You need to use the 32bit odbc admin tool (from windows\syswow64) to create 32bit DSN’s for your 32bit application.

|||thank you so muchhhhhhh|||Thank you, Thank you, Thank you, Thank you!|||

Question - after created System DSN using 32bit odbc, I created a linked server using the DSN,

sp_addlinkedserver'MyLinkedSrv',' ','MSDASQL','MyDSN'

I am thinking because of the SQL management studio is 64bit, it won't use 32bit DSN.. Is there any way to run a query against to the linked server?

Regards,

Sunny

|||

did anyone succeed in this on this with a sql2005 EE x64 sp1 to sql2000sp3a ?

Or it states the is an NCLI problem

- connectiontimeout (again and again and ......)

- OLE DB provider "SQLNCLI" for linked server "MySQl2000sp3a_03" returned message "Unspecified error".

Msg 7311, Level 16, State 2, Line 3

or it says :
Msg 7403, Level 16, State 1, Line 3

The OLE DB provider "MSDASQL" has not been registered.

None of the folowing definitions work :

|||

I just discouvered this thread with a KB link that may do the trick : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1

kb906954 did the trick

|||

I'm having similar issues

There is no equivalent of MSDASQL within 64 bit SQL, and from what i've read SQL Native client is meant to be used.

However I can't get this to see the 64 bit IBM ISeries ODBC DSN that I've configured within the 64bit ODBC setup

Anybody else having these problems?

|||

YES !!!

And the solution is: Execute odbccad32.exe in WOW directory. That's it !

www.mcfly.dj

|||

Hi,

I'm running SQL 2005 64 bit on the windows 2003 (64 bit box)

I'm trying to create a linked server using an ODBC driver which has a corresponding DSN

Using odbccad32.exe will work if you are using a 32 bit install of SQL 2005 on Windows 2003 (64bit)

but this doesn't work with a 64 bit instance of SQL 2005 as there is no MSDASQL provider to work with.

Has anybody managed to get the SQL native client provider SQLNCLI

in 64 bit SQL 2005 connected through a DSN (32bit or 64bit)?

Thanks, Richard

|||

Richard...see the following thread.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1239339&SiteID=1

|||

hey guys,

i have C# windows application which calles ODBC 32 bit provider. it is working very well with the 32 bit operating systems. My application is compiled targetting to any operating system.

Please help in this.

regards

sql

ODBC connection failing on 64 version of Windows 2003 server

IM002 - Data source name not found and no default driver specified.

The data source name does exist, it's in the ODBC.INI and in registry. As mentioned above the Test Connection succeeds in the ODBC Administrator so logic would dictate that it's setup correctly.

Any help would be appreciated.

Is your application 64-bit or 32-bit?

One possibility is that the Test Connection dialog uses 64-bit version of the driver and yoru application looks for a 32-bit version (or vice-versa).

|||Yeah, I thought of that too but both my application and MDAC are 32 bit.|||

DSN’s for 32bit and 64bit are stored separately in registry, those in HKLM/HKCR are for 64bit, 32bit DSN’s are stored in WOW registry.

You need to use the 32bit odbc admin tool (from windows\syswow64) to create 32bit DSN’s for your 32bit application.

|||thank you so muchhhhhhh|||Thank you, Thank you, Thank you, Thank you!|||

Question - after created System DSN using 32bit odbc, I created a linked server using the DSN,

sp_addlinkedserver'MyLinkedSrv',' ','MSDASQL','MyDSN'

I am thinking because of the SQL management studio is 64bit, it won't use 32bit DSN.. Is there any way to run a query against to the linked server?

Regards,

Sunny

|||

did anyone succeed in this on this with a sql2005 EE x64 sp1 to sql2000sp3a ?

Or it states the is an NCLI problem

- connectiontimeout (again and again and ......)

- OLE DB provider "SQLNCLI" for linked server "MySQl2000sp3a_03" returned message "Unspecified error".

Msg 7311, Level 16, State 2, Line 3

or it says :
Msg 7403, Level 16, State 1, Line 3

The OLE DB provider "MSDASQL" has not been registered.

None of the folowing definitions work :

|||

I just discouvered this thread with a KB link that may do the trick : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1

kb906954 did the trick

|||

I'm having similar issues

There is no equivalent of MSDASQL within 64 bit SQL, and from what i've read SQL Native client is meant to be used.

However I can't get this to see the 64 bit IBM ISeries ODBC DSN that I've configured within the 64bit ODBC setup

Anybody else having these problems?

|||

YES !!!

And the solution is: Execute odbccad32.exe in WOW directory. That's it !

www.mcfly.dj

|||

Hi,

I'm running SQL 2005 64 bit on the windows 2003 (64 bit box)

I'm trying to create a linked server using an ODBC driver which has a corresponding DSN

Using odbccad32.exe will work if you are using a 32 bit install of SQL 2005 on Windows 2003 (64bit)

but this doesn't work with a 64 bit instance of SQL 2005 as there is no MSDASQL provider to work with.

Has anybody managed to get the SQL native client provider SQLNCLI

in 64 bit SQL 2005 connected through a DSN (32bit or 64bit)?

Thanks, Richard

|||

Richard...see the following thread.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1239339&SiteID=1

|||

hey guys,

i have C# windows application which calles ODBC 32 bit provider. it is working very well with the 32 bit operating systems. My application is compiled targetting to any operating system.

Please help in this.

regards

ODBC connection failing on 64 version of Windows 2003 server

IM002 - Data source name not found and no default driver specified.

The data source name does exist, it's in the ODBC.INI and in registry. As mentioned above the Test Connection succeeds in the ODBC Administrator so logic would dictate that it's setup correctly.

Any help would be appreciated.

Is your application 64-bit or 32-bit?

One possibility is that the Test Connection dialog uses 64-bit version of the driver and yoru application looks for a 32-bit version (or vice-versa).

|||Yeah, I thought of that too but both my application and MDAC are 32 bit.|||

DSN’s for 32bit and 64bit are stored separately in registry, those in HKLM/HKCR are for 64bit, 32bit DSN’s are stored in WOW registry.

You need to use the 32bit odbc admin tool (from windows\syswow64) to create 32bit DSN’s for your 32bit application.

|||thank you so muchhhhhhh|||Thank you, Thank you, Thank you, Thank you!|||

Question - after created System DSN using 32bit odbc, I created a linked server using the DSN,

sp_addlinkedserver 'MyLinkedSrv', ' ', 'MSDASQL', 'MyDSN'

I am thinking because of the SQL management studio is 64bit, it won't use 32bit DSN.. Is there any way to run a query against to the linked server?

Regards,

Sunny

|||

did anyone succeed in this on this with a sql2005 EE x64 sp1 to sql2000sp3a ?

Or it states the is an NCLI problem

- connectiontimeout (again and again and ......)

- OLE DB provider "SQLNCLI" for linked server "MySQl2000sp3a_03" returned message "Unspecified error".

Msg 7311, Level 16, State 2, Line 3

or it says :
Msg 7403, Level 16, State 1, Line 3

The OLE DB provider "MSDASQL" has not been registered.

None of the folowing definitions work :

|||

I just discouvered this thread with a KB link that may do the trick : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1

kb906954 did the trick

|||

I'm having similar issues

There is no equivalent of MSDASQL within 64 bit SQL, and from what i've read SQL Native client is meant to be used.

However I can't get this to see the 64 bit IBM ISeries ODBC DSN that I've configured within the 64bit ODBC setup

Anybody else having these problems?

|||

YES !!!

And the solution is: Execute odbccad32.exe in WOW directory. That's it !

www.mcfly.dj

|||

Hi,

I'm running SQL 2005 64 bit on the windows 2003 (64 bit box)

I'm trying to create a linked server using an ODBC driver which has a corresponding DSN

Using odbccad32.exe will work if you are using a 32 bit install of SQL 2005 on Windows 2003 (64bit)

but this doesn't work with a 64 bit instance of SQL 2005 as there is no MSDASQL provider to work with.

Has anybody managed to get the SQL native client provider SQLNCLI

in 64 bit SQL 2005 connected through a DSN (32bit or 64bit)?

Thanks, Richard

|||

Richard...see the following thread.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1239339&SiteID=1

|||

hey guys,

i have C# windows application which calles ODBC 32 bit provider. it is working very well with the 32 bit operating systems. My application is compiled targetting to any operating system.

Please help in this.

regards

ODBC connection failing on 64 version of Windows 2003 server

IM002 - Data source name not found and no default driver specified.

The data source name does exist, it's in the ODBC.INI and in registry. As mentioned above the Test Connection succeeds in the ODBC Administrator so logic would dictate that it's setup correctly.

Any help would be appreciated.

Is your application 64-bit or 32-bit?

One possibility is that the Test Connection dialog uses 64-bit version of the driver and yoru application looks for a 32-bit version (or vice-versa).

|||Yeah, I thought of that too but both my application and MDAC are 32 bit.|||

DSN’s for 32bit and 64bit are stored separately in registry, those in HKLM/HKCR are for 64bit, 32bit DSN’s are stored in WOW registry.

You need to use the 32bit odbc admin tool (from windows\syswow64) to create 32bit DSN’s for your 32bit application.

|||thank you so muchhhhhhh|||Thank you, Thank you, Thank you, Thank you!|||

Question - after created System DSN using 32bit odbc, I created a linked server using the DSN,

sp_addlinkedserver 'MyLinkedSrv', ' ', 'MSDASQL', 'MyDSN'

I am thinking because of the SQL management studio is 64bit, it won't use 32bit DSN.. Is there any way to run a query against to the linked server?

Regards,

Sunny

|||

did anyone succeed in this on this with a sql2005 EE x64 sp1 to sql2000sp3a ?

Or it states the is an NCLI problem

- connectiontimeout (again and again and ......)

- OLE DB provider "SQLNCLI" for linked server "MySQl2000sp3a_03" returned message "Unspecified error".

Msg 7311, Level 16, State 2, Line 3

or it says :
Msg 7403, Level 16, State 1, Line 3

The OLE DB provider "MSDASQL" has not been registered.

None of the folowing definitions work :

|||

I just discouvered this thread with a KB link that may do the trick : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1

kb906954 did the trick

|||

I'm having similar issues

There is no equivalent of MSDASQL within 64 bit SQL, and from what i've read SQL Native client is meant to be used.

However I can't get this to see the 64 bit IBM ISeries ODBC DSN that I've configured within the 64bit ODBC setup

Anybody else having these problems?

|||

YES !!!

And the solution is: Execute odbccad32.exe in WOW directory. That's it !

www.mcfly.dj

|||

Hi,

I'm running SQL 2005 64 bit on the windows 2003 (64 bit box)

I'm trying to create a linked server using an ODBC driver which has a corresponding DSN

Using odbccad32.exe will work if you are using a 32 bit install of SQL 2005 on Windows 2003 (64bit)

but this doesn't work with a 64 bit instance of SQL 2005 as there is no MSDASQL provider to work with.

Has anybody managed to get the SQL native client provider SQLNCLI

in 64 bit SQL 2005 connected through a DSN (32bit or 64bit)?

Thanks, Richard

|||

Richard...see the following thread.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1239339&SiteID=1

|||

hey guys,

i have C# windows application which calles ODBC 32 bit provider. it is working very well with the 32 bit operating systems. My application is compiled targetting to any operating system.

Please help in this.

regards

ODBC connection failing

First of all i apologise if this is in the wrong area. My problem is as follows. I had set up a couple of aspx pages on our webserver which accessed a sql database on another server. These pages were set up using frontpage 2003 and everything worked fine. A week ago the pages stopped working giving the error "Server Error in '/' Application". As far as I know nothing has been changed on the servers. On top of this frontpage has started having connection problems. Frontpage will connect to the database but is unable to access the fields giving the following error.

Server error: Unable to retrieve schema information from the record source ' NAME' in a database using the connection string:

'DRIVER={SQL Server};SERVER=servername;DATABASE=live;UID=********;PWD=********'.

The following error message comes from the database driver software; it may appear in a different language depending on how the driver is configured.
-----------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'NAME'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

I have been trying for the past few days to solve this one, but am reasonable new to SQL, so this may be an easy one.

Hope someone can help me

If all your pages are in aspx you might want to use the native support for SQL Server and use a SQL Connection string. Also, you might want to move your code to .NET.|||I appreciate the info, but i still need to solve my current problem.|||can you post your connectionstring and some code.|||

Here is the full global.asa page generated by Frontpage. I know the connection string is correct as all my other office apps can connect and work on any of the tables in this database without a problem, frontpage is the only one that will not access the tables, although it can see the tables. On top of this the webserver will aslo not access the tables either. For whatever reason Frontpage will no longer access the tables, when it used to quite happily. We do have another SQL DB on another server which it will connect to no problem, so I am wondering if it is a security issue, but I have been unable to find any settings between the two servers which are different.

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==FrontPage Generated - startspan==
Dim FrontPage_UrlVars(1)
'--Project Data Connection
Application("sage_ConnectionString") = "DRIVER={SQL Server};SERVER=SERVERNAME;DATABASE=DBNAME;UID=LOGIN;PWD=PASSWORD"
Application("sage_ConnectionTimeout") = 15
Application("sage_CommandTimeout") = 30
Application("sage_CursorLocation") = 3
Application("sage_RuntimeUserName") = "LOGIN"
Application("sage_RuntimePassword") = "PASSWORD"
'--
Application("FrontPage_UrlVars") = FrontPage_UrlVars
'==FrontPage Generated - endspan==
End Sub
Sub Session_OnStart
FrontPage_StartSession '==FrontPage Generated==
FrontPage_ConvertFromODBC '==FrontPage Generated==
End Sub
Sub FrontPage_StartSession
On Error Resume Next
if Len(Application("FrontPage_VRoot")) > 0 then Exit Sub

sFile = "global.asa"
sRootPath = Request.ServerVariables("APPL_PHYSICAL_PATH")
if Left(sRootPath,1) = "/" then sSep = "/" else sSep = "\"
if Right(sRootPath,1) <> sSep then sRootPath = sRootPath & sSep
sRootPath = sRootPath & sFile

' discover the VRoot for the current page;
' walk back up VPath until we match VRoot
Vroot = Request.ServerVariables("PATH_INFO")
iCount = 0
do while Len(Vroot) > 1
idx = InStrRev(Vroot, "/")
if idx > 0 then
Vroot = Left(Vroot,idx)
else
' error; assume root web
Vroot = "/"
end if
if Server.MapPath(Vroot & sFile) = sRootPath then exit do
if Right(Vroot,1) = "/" then Vroot = Left(Vroot,Len(Vroot)-1)
iCount = iCount + 1
if iCount > 100 then
' error; assume root web
Vroot = "/"
exit do
end if
loop
' map all URL= attributes in _ConnectionString variables
Application.Lock
if Len(Application("FrontPage_VRoot")) = 0 then
Application("FrontPage_VRoot") = Vroot
UrlVarArray = Application("FrontPage_UrlVars")
for i = 0 to UBound(UrlVarArray)
if Len(UrlVarArray(i)) > 0 then FrontPage_MapUrl(UrlVarArray(i))
next
end if
Application.Unlock
End Sub
Sub FrontPage_MapUrl(AppVarName)
' convert URL attribute in conn string to absolute file location
strVal = Application(AppVarName)
strKey = "URL="
idxStart = InStr(strVal, strKey)
If idxStart = 0 Then Exit Sub
strBefore = Left(strVal, idxStart - 1)
idxStart = idxStart + Len(strKey)
idxEnd = InStr(idxStart, strVal, ";")
If idxEnd = 0 Then
strAfter = ""
strURL = Mid(strVal, idxStart)
Else
strAfter = ";" & Mid(strVal, idxEnd + 1)
strURL = Mid(strVal, idxStart, idxEnd - idxStart)
End If
strOut = strBefore & Server.MapPath(Application("FrontPage_VRoot") & strURL) & strAfter
Application(AppVarName) = strOut
End Sub
Sub FrontPage_ConvertFromODBC
On Error Resume Next
if Len(Application("ASP_OS")) > 0 then exit sub
str = "_ConnectionString"
slen = Len(str)
set oKnown = Server.CreateObject("Scripting.Dictionary")
oKnown.Add "DRIVER",""
oKnown.Add "DBQ",""
oKnown.Add "SERVER",""
oKnown.Add "DATABASE",""
oKnown.Add "UID",""
oKnown.Add "PWD",""
Application.Lock
For each item in Application.Contents
if UCase(Right(item,slen)) = UCase(str) then
sName = Left(item,Len(item)-slen)
sConn = Application(item)
if InStr(LCase(sConn),"provider=") < 1 and Len(Application(sName & "_ConnectionTimeout"))>0 then
sArr = Split(sConn,";")
set oDict = Server.CreateObject("Scripting.Dictionary")
bUnknown = False
for i = 0 to UBound(sArr)
s = sArr(i)
idx = InStr(s,"=")
sKey = UCase(Trim(Left(s,idx-1)))
sVal = Trim(Mid(s,idx+1))
oDict.Add sKey, sVal
if Not oKnown.Exists(sKey) then bUnknown = True
next
if bUnknown = False and oDict.Exists("DRIVER") then
sDrv = oDict.Item("DRIVER")
sNew = ""
if InStr(sDrv,"Microsoft Access") > 0 and oDict.Exists("DBQ") and not (oDict.Exists("UID") or oDict.Exists("PWD")) then
sNew = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & oDict.Item("DBQ")
elseif InStr(sDrv,"SQL Server") > 0 and oDict.Exists("SERVER") and oDict.Exists("DATABASE") then
sNew = "Provider=SQLOLEDB;Data Source=" & oDict("SERVER") & ";Initial Catalog=" & oDict("DATABASE")
if oDict.Exists("UID") then sNew = sNew & ";User ID=" & oDict("UID")
if oDict.Exists("PWD") then sNew = sNew & ";Password=" & oDict("PWD")
end if
if sNew <> "" then
Application(item) = sNew
end if
end if
set oDict = Nothing
end if
end if
Next
Application.Unlock
Set oKnown = Nothing
End Sub
</SCRIPT>
<head><title>Web Site Settings for Active Server Pages</title><html xmlns:mso="urn:schemas-microsoft-com:office:office" xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">

</head>

|||Sorry I got side tracked. I have no idea what to suggest. Are you creating an Access DB from your code? I see some Jet OLEDB and SQL OLEDB related connection strings.|||No, no Access DB is being created. This script is just setting the environment for the site itself. I can paste in the coding for the page itself if you require, but all the connection information is in the above script anyway. But the strange thing is, like i mentioned before, if i open frontpage now and create a new site (or open a present one) frontpage will not open any table on this DB. It can connect OK but gives me the error "object does not exist" when i try and open the table itself. this is the same for any table and any DB on this server. I assumed it was a security setting but can not find anything that is set to block this user account from accessing the tables.

Monday, March 19, 2012

ODBC Call To sp_prepexec Failing

Hi all,
I'm working on creating a ms-sql database that will work with an existing
third-party application. As such, I have full control over the DB schema,
but no control over the third-party source.
The application is using ODBC to talk to my DB.
I have everything working fine, up to where an INSERT is performed by the
ODBC driver. The INSERT statement is translated to the following by the
driver:
declare @.p1 int
set @.p1=9
exec sp_prepexec @.p1 output,N'@.P1 varchar(80),@.P2 varchar(80),@.P3
varchar(80),@.P4 int,@.P5 varchar(80)',N'INSERT INTO tracks (titlesort, url,
title, tag, ct)
VALUES (@.P1, @.P2, @.P3, @.P4, @.P5)
','MP3','file:///D:/MP3','MP3',1,'dir'
select @.p1
This fails with the following error:
Msg 8179, Level 16, State 2, Procedure sp_prepexec, Line 1
Could not find prepared statement with handle 9.
(1 row(s) affected)
Now, if I manually try the same query but set @.p1 to NULL instead of 9, THEN
it works. Is there anything I can tweak in the SQL Server ODBC driver to fi
x
this behaviour? Or does anyone know what I can change in the database to
make this work correctly?
Thanks,
GeoffHi
I am not sure if sp_prepexec would be called from ODBC directly, are you
using ADO?
In which case unless you are repeating the statement multiple times you do
not need to prepare it. See
http://msdn.microsoft.com/library/d...
etchapt12.asp
The value 9 is a previously prepared handle, it seems this may have gone out
of scope. You may want to use profiler to see what is happening on a longer
time frame.
John
"GeoffB" <GeoffB@.discussions.microsoft.com> wrote in message
news:DC2D0A76-BB47-49CA-A65E-1F6685803B27@.microsoft.com...
> Hi all,
> I'm working on creating a ms-sql database that will work with an existing
> third-party application. As such, I have full control over the DB schema,
> but no control over the third-party source.
> The application is using ODBC to talk to my DB.
> I have everything working fine, up to where an INSERT is performed by the
> ODBC driver. The INSERT statement is translated to the following by the
> driver:
> declare @.p1 int
> set @.p1=9
> exec sp_prepexec @.p1 output,N'@.P1 varchar(80),@.P2 varchar(80),@.P3
> varchar(80),@.P4 int,@.P5 varchar(80)',N'INSERT INTO tracks (titlesort, url,
> title, tag, ct)
> VALUES (@.P1, @.P2, @.P3, @.P4, @.P5)
> ','MP3','file:///D:/MP3','MP3',1,'dir'
> select @.p1
> This fails with the following error:
> Msg 8179, Level 16, State 2, Procedure sp_prepexec, Line 1
> Could not find prepared statement with handle 9.
> (1 row(s) affected)
> Now, if I manually try the same query but set @.p1 to NULL instead of 9,
> THEN
> it works. Is there anything I can tweak in the SQL Server ODBC driver to
> fix
> this behaviour? Or does anyone know what I can change in the database to
> make this work correctly?
> Thanks,
> Geoff|||Hi John,
Thanks a lot for your reply. I'm still stuck though - I

> I am not sure if sp_prepexec would be called from ODBC directly, are you
> using ADO?
I'm actually just using a System DSN ODBC entry. The application that is
doing the calling is written in Perl, so I assume this means that ADO is
definitely not being used.

> The value 9 is a previously prepared handle, it seems this may have gone o
ut
> of scope. You may want to use profiler to see what is happening on a longe
r
> time frame.
The statement being executed by the application is (I believe):
INSERT INTO tracks (titlesort, url, title, tag, ct)
VALUES ('MP3', 'file:///D:/MP3', 'MP3', 1, 'dir')
Of course, if I execute this in Query Analyzer, it works fine. Profiler is
where I spotted that this was being translated. When I look up sp_prepexec,
everything I can find indicates that this is what the ODBC driver translates
any INSERT statement into.
The statements in Profiler leading up to the error are:
SQL:BatchStarting declare @.p1 int [...]
SQL:StmtStarting set @.p1=9
SQL:StmtCompleted set @.p1=9
SQL:StmtStarting exec sp_prepexec @.p1 output,[...]
Exception Error: 8179, Severity: 16, State: 2
User Error Message Could not find prepared statement with handle 9
Any other ideas?
Thanks
Geoff|||Hi
You don't post your code, but look at using a pass through query
instead and then it should not call sp_prepexec.
John|||"John Bell" wrote:
> You don't post your code, but look at using a pass through query
> instead and then it should not call sp_prepexec.
Unfortunately, I don't have access to the application code, since it is
contained in a third-party app. I have no way to control how the app talks
to the database, and was hoping to find out how I can 'tweak' the ODBC drive
r
behaviour, or prevent the sp_prepexec from causing an error.
Anyone have any other ideas?
Thanks
Geoff|||Hi
You should report this back to the application vendor as they seem to
be incorrectly releasing handles. You may want to check to see if they
already have any patches!
John|||"John Bell" wrote:
> You should report this back to the application vendor as they seem to
> be incorrectly releasing handles. You may want to check to see if they
> already have any patches!
>
Thanks John - I think I'll do as you suggest. I am already running their
latest release; the problem is that they run against MySQL by default, and
I'm trying to generate a SQL Server schema that works.
My gritty alternative is to learn Perl, and start trying to work my way
through the code myself!
Cheers
Geoff

Friday, March 23, 2012

ODBC connection to SQL server failing

Wednesday, March 21, 2012

ODBC connection failing on 64 version of Windows 2003 server

ODBC connection failing on 64 version of Windows 2003 server

ODBC connection failing on 64 version of Windows 2003 server

ODBC connection failing on 64 version of Windows 2003 server

ODBC connection failing on 64 version of Windows 2003 server

ODBC connection failing on 64 version of Windows 2003 server

ODBC connection failing

Monday, March 19, 2012

ODBC Call To sp_prepexec Failing

Objects related to DB

Blog Archive

About Me