cancel
Showing results for 
Search instead for 
Did you mean: 

Database Query missing Schema specification - Gives DB error 2812

Former Member
0 Kudos

Hi All,

I have spent the better part of the day trying to find out why, when I export my report, I am getting an exception with a DB error of 2812, Stored procedure not found.

My program is a system service running on a Windows Server 2008 64-bit box. It is connecting to a SQL Server 2008 R2 instance using an OLE DB (ADO) connection.

When I load the data for this report from within CR, it works fine.

When I run my system service on my Windows 7 64-bit development system, it works fine.

However, when I run the service on the server, it fails.

I have tried running it in 32-bit mode and 64-bit mode. The report was using the SQL Server Native Client driver and I updated it to SQL Server Native Client 10. I put the latest version of that driver on the server so it matched what was on my development machine. I made sure that the server had the latest runtime of CR for VS 2010 (13.1). Nothing was making any difference.

I couldn't figure out what was going on. Then I turned on logging. When the service is run on my local system, the database query that is generated to call the stored procedure includes the database name and the schema. When the service is run on my local system, the query only includes the stored procedure name. This perfectly explains the error I am getting.

So the question is why is it behaving differently on my local system than on the server. The only difference is that on my local system I build the service in Debug mode and for the Server it is built in Release mode. It may make a difference but it seems unlikely.

I will include the text of the two log files. If anyone has any idea of why it is behaving differently on the two systems I would greatly appreciate hearing from you.

(It won't format correctly if I include both logs. I'll include one here and post a reply with the other.)

Thanks,

David

-


Log file from Server:

TIMESTAMP THREAD_ID FILENAME LINE_NUMBER LOGGED_DATA LEVEL

2011-8-24-18-53-35 8412 .\ado.cpp 2943 Begin DbMatchLogonInfo 20

2011-8-24-18-53-36 8412 .\ado.cpp 2974 Begin DbMatchLogonInfo 20

2011-8-24-18-53-37 8412 .\ado.cpp 2943 Begin DbMatchLogonInfo 20

2011-8-24-18-53-37 8412 .\ado.cpp 2974 Begin DbMatchLogonInfo 20

2011-8-24-18-53-37 8412 .\ado.cpp 2943 Begin DbMatchLogonInfo 20

2011-8-24-18-53-37 8412 .\ado.cpp 2974 Begin DbMatchLogonInfo 20

2011-8-24-18-53-37 8412 .\ado.cpp 2943 Begin DbMatchLogonInfo 20

2011-8-24-18-53-37 8412 .\ado.cpp 2974 Begin DbMatchLogonInfo 20

2011-8-24-18-53-37 8412 .\ado.cpp 377 Beginning DbLogonServer 20

2011-8-24-18-53-37 8412 .\serverh.cpp 223 Beginning DbServerHandle::logon 20

2011-8-24-18-53-37 8412 .\serverh.cpp 3976 adoConnection->Open succeeded: dataLinkFile=,provider=SQLNCLI10,dataSource=TLI-Core,userID=name,password=********* 10

2011-8-24-18-53-37 8412 .\serverh.cpp 3988 initialCatalog=TLI_AuditInvoice,jetSystemDatabase=,jetDatabaseType=Access,jetDatabasePassword=,useIntegratedSecurity=0,includeInitialCatalogProp=1,includeSecurityProps=1,includeJetSecurityProps=0 10

2011-8-24-18-53-37 8412 .\serverh.cpp 414 Finishing DbServerHandle::logon 20

2011-8-24-18-53-37 8412 .\DbQueryBuilder.cpp 512 Query Targets: SQLNCLI.1, NativeSQLServer 10

2011-8-24-18-53-37 8412 .\DbQueryBuilder.cpp 523 Successfully built query: "RptShipmentTransitByOwnerID";1 206, 4, '' 10

2011-8-24-18-53-37 8412 .\ado.cpp 1643 Beginning DbExecuteQuery 20

2011-8-24-18-53-37 8412 .\serverh.cpp 2749 Beginning DbServerHandle::openRowset 20

2011-8-24-18-53-37 8412 .\serverh.cpp 2909 adoRecordset->Open failed: "RptShipmentTransitByOwnerID";1 206, 4, '' 1

2011-8-24-18-53-37 8412 .\adocommon.cpp 527 ADO Error Code: 0x80040e14 Source: Microsoft SQL Server Native Client 10.0 Description: Could not find stored procedure 'RptShipmentTransitByOwnerID'. SQL State: 42000 Native Error: 2812 1

Edited by: Don Williams on Aug 25, 2011 8:45 AM

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Sorry, I had a mis-type. The above post should have read:

I couldn't figure out what was going on. Then I turned on logging. When the service is run on my local system, the database query that is generated to call the stored procedure includes the database name and the schema. When the service is run on my SERVER , the query only includes the stored procedure name. This perfectly explains the error I am getting.

Log file from development system:

TIMESTAMP THREAD_ID FILENAME LINE_NUMBER LOGGED_DATA LEVEL

2011-8-24-18-59-51 23808 .\ado.cpp 2943 Begin DbMatchLogonInfo 20

2011-8-24-18-59-51 23808 .\ado.cpp 2974 Begin DbMatchLogonInfo 20

2011-8-24-19-0-54 23808 .\ado.cpp 377 Beginning DbLogonServer 20

2011-8-24-19-0-54 23808 .\serverh.cpp 223 Beginning DbServerHandle::logon 20

2011-8-24-19-0-55 23808 .\serverh.cpp 3976 adoConnection->Open succeeded: dataLinkFile=,provider=SQLNCLI10,dataSource=dsname,port,userID=user,password=********* 10

2011-8-24-19-0-55 23808 .\serverh.cpp 3988 initialCatalog=TLI_AuditInvoice,jetSystemDatabase=,jetDatabaseType=Access,jetDatabasePassword=,useIntegratedSecurity=0,includeInitialCatalogProp=1,includeSecurityProps=1,includeJetSecurityProps=0 10

2011-8-24-19-0-55 23808 .\serverh.cpp 414 Finishing DbServerHandle::logon 20

2011-8-24-19-0-55 23808 .\DbQueryBuilder.cpp 512 Query Targets: SQLNCLI.1, NativeSQLServer 10

2011-8-24-19-0-55 23808 .\DbQueryBuilder.cpp 523 Successfully built query: "TLI_AuditInvoice"."Gideon"."RptShipmentTransitByOwnerID";1 206, 4, '' 10

2011-8-24-19-0-55 23808 .\ado.cpp 1643 Beginning DbExecuteQuery 20

2011-8-24-19-0-55 23808 .\serverh.cpp 2749 Beginning DbServerHandle::openRowset 20

2011-8-24-19-0-56 23808 .\serverh.cpp 2925 adoRecordset->Open succeeded without async option: "TLI_AuditInvoice"."Gideon"."RptShipmentTransitByOwnerID";1 206, 4, '' 10

2011-8-24-19-0-56 23808 .\serverh.cpp 2942 Finishing DbServerHandle::openRowset 20

2011-8-24-19-0-56 23808 .\ado.cpp 1859 Finishing DbExecuteQuery 20

2011-8-24-19-0-56 23808 .\DbQueryBuilder.cpp 512 Query Targets: SQLNCLI.1, NativeSQLServer 10

2011-8-24-19-0-56 23808 .\DbQueryBuilder.cpp 523 Successfully built query: SELECT "Owner"."AccountName", "Owner"."OwnerID" FROM "TLI_AuditInvoice"."dbo"."Owner" "Owner" WHERE "Owner"."OwnerID"=206 10

2011-8-24-19-0-56 23808 .\ado.cpp 1643 Beginning DbExecuteQuery 20

2011-8-24-19-0-56 23808 .\serverh.cpp 2749 Beginning DbServerHandle::openRowset 20

2011-8-24-19-0-56 23808 .\serverh.cpp 2925 adoRecordset->Open succeeded without async option: SELECT "Owner"."AccountName", "Owner"."OwnerID" FROM "TLI_AuditInvoice"."dbo"."Owner" "Owner" WHERE "Owner"."OwnerID"=206 10

2011-8-24-19-0-56 23808 .\serverh.cpp 2942 Finishing DbServerHandle::openRowset 20

2011-8-24-19-0-56 23808 .\ado.cpp 1859 Finishing DbExecuteQuery 20

Edited by: CDavidOrr on Aug 25, 2011 1:52 AM

Edited by: Don Williams on Aug 25, 2011 8:46 AM

0 Kudos

Hi David,

It's likely due to the user name you are passing, it may be it is trusted or could be it's not trusted. Try hard coding your user name and password, it may be your account is not trusted due to the Server likely running under a local Domain Admin possibly.

Could also be a Registry key, try ProcessMonitor and compare registry keys being read.

Did you install the 32 bit client on the Server also?

Don

Former Member
0 Kudos

Hi Don,

Thanks for responding.

Well, while I was looking at this more closely while I was getting ready to respond to you, I noticed a diference that I hasn't before. The server names for the SQL connection were different. They both point to the same SQL server but one is a local name and one is a network name. I altered the service to use the network name and now it is working.

I'm not sure why it makes a difference. It must have something to do with the information CR gets from SQL when it is trying to build the SQL query. It seems that it shouldn't matter, but it does.

Thanks,

David

Answers (0)