Autoprepare on query

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Autoprepare on query

Post by ccmcbride » Wed 28 Apr 2010 19:22

I have a procedure that uses several parameterized queries.
On 49 out of 50 databases, the procedure works.
After working with the one database where the procedure didn't work, I tracked it down to the autoprepare flag on the queries.
In every case, it returned counts of 0, even though the query in mssql manager tool returned data BUT ONLY ON THIS DATABASE. Data is comparable to data in the other databases.

I turned the flag off, and the queries started returning values again.

I can't see any differences in the settings of the database, but I gotta be missing something. Any idea what?

Using

MSSQL Server version 10.00.1798
MSSQL Client version 10.0.1798.0
SDAC version 4.80.0.55

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 29 Apr 2010 09:34

Are you using several SQL servers or only one?

ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Post by ccmcbride » Thu 29 Apr 2010 16:02

no
single sqlserver, local machine. This is a client's data, detached and zipped at client site, unzipped and attached here, then 'shrinked' - both database and files, then backed up and restored (thought it was a index issue), then reindexed completely (indexes all dropped and recreated).
Situation was duplicated on one of my support tech's machine with same database (but again, not with other databases).

Then I turned off the flag and it worked.

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 30 Apr 2010 12:18

This is a very strange situation.
Please send me database data to reproduce the problem.

ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Post by ccmcbride » Fri 30 Apr 2010 17:17

I have sent it, and an application to reproduce the problem.
Won't let me send. Keeps bouncing back, due to the attachments.
Where should I send to?

ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Post by ccmcbride » Tue 04 May 2010 20:16

Still waiting for info on how to send to you.
Attachments are rejected.

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 05 May 2010 14:09

I have not received your email. Please, send it to me once more to dmitryg*devart*com.

kme
Posts: 5
Joined: Wed 21 Oct 2009 13:00

Post by kme » Wed 05 May 2010 15:34

Use http://www.filemail.com or similar if you have large files to send.

Dimon
Devart Team
Posts: 2888
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 06 May 2010 09:12

Thank you for information.
This problem occurs because query is prepared with the length of ItemUID parameter less than maximum available parameter value.
To solve the problem you can set AutoPrepare to False and prepare the query for the empty parameter, like this:

Code: Select all

   dmInventory.qryInvTotals.ParamByName('ItemUID').DataType := ftString;
   dmInventory.qryInvTotals.Prepare;
Or you can set the ParamsInfoOldBehavior variable to True in the initialization section of your unit. This variable is declared in the OLEDBAccess unit.

Post Reply