This topic is locked
[SOLVED]

 SQL 2005 ODBC Error

8/5/2006 2:22:25 PM
ASPRunnerPro General questions
F
funklet author

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][SQL Native Client]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
/UndeliveredParts_list.asp, line 777
I am getting the above error when the list page is run. It tries to get records from a view.
Line 777 is setupRs.open strSQL, dbConnection, 1,2
I have output the strSQL and run that in a query window in Management Studio and it runs fine.
I have rewritten the project and tried different ODBC methods to access the database. I still get the same error.
I am using about 50 other projects with no problem and have never seen this error before.
This seems to have become a problem since upgrading from SQL 2000 to SQL 2005.
OS: Windows Server 2003 Standard Service Pack 1

MDAC: 2.8 sp2

SQL Server Info:

Microsoft SQL Server Management Studio 9.00.2047.00

Microsoft Analysis Services Client Tools 2005.090.2047.00

Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)

Microsoft MSXML 2.6 3.0 5.0 6.0

Microsoft Internet Explorer 6.0.3790.1830

Microsoft .NET Framework 2.0.50727.42

Operating System 5.2.3790
I have narrowed it down to the query that is being accessed but can find no reason why it should be a problem...
"select [SuppCode], [SuppName], [OrdNo], [JobNumber], [OrdDate], [Ordered], [Delivered], [PartCode], [Stock Code], [PartDesc], [DeliverTo], [PartAuto], [Expected], [OrdNotes] From [dbo].[UndeliveredParts]"
Is there something about the syntax of this that could conflict?
Please can you advise.
Kind Regards,
Spencer.

J
Jane 8/8/2006

Hi,
it seems that you use old files with SQL 2005.

Connection strings are different for SQL 2000 and SQL 2005.

You can edit your connection string in the include/dbconnection.asp file or create new project, select SQL Native Client as ODBC driver and then copy new connection string and paste it to the old files.

F
funklet author 8/9/2006

Hi,
I am already using the Native driver.

I already many other projects working most have not needed to be changed at all since the upgrade to SQL 2005. This one has not worked since the change.
I have completely rebuilt the project and still cannot get this query to return data without the error.
This query is a detail from a Master Detail relationship, but I have also made a project with the detail table only and the query will still not process and it is always the same error.
[Microsoft][SQL Native Client]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
Kind Regards,
Funklet

J
Jane 8/11/2006

Please zip and send generated ASP files to support@xlinesoft.com along with project file and your sample database for investigation.

I'll find what's wrong with your project running it on my test box.

F
funklet author 8/27/2006

I can't find a way to export my database without the data.
With the data it is 405Mb / zipped 95Mb.
Any ideas?
Funklet...

admin 8/28/2006

To generate this script you need to open SQL Server Management Studio and right click on this table.
Choose "All tasks"->"Generate script ...". Click OK, save generated script to file and send it to me.

F
funklet author 9/15/2006

I have resolved the problem using a workaround.
The view I was using contained ISNULL this was causing the problem.

I removed all the ISNULL and put them in the 'Edit SQL query' page.

I think there must be a bug in SQL server regarding this.

I tried using COALESCE but that had the same error also.
The error is in SQL Server and is:
FATAL_EXCEPTION_ERROR

This is caused by the process ID that calls the view.
I wonder if anyone else has found this. I did find some documentation regarding a similar bug that Service Pack 2 for SQL Server 2000 was supposed to fix. However, I am using SQL Server 2005 with Service Pack 1 and the latest updates.
Kind Regards,
Funklet.

admin 9/19/2006

I can take a closer look if you send me SQL script that generates required tables in SQL Server.