Autoprepare on query

ccmcbride · 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 · Post by **Dimon** » Thu 29 Apr 2010 09:34

Are you using several SQL servers or only one?

ccmcbride · 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 · 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 · 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 · Post by **ccmcbride** » Tue 04 May 2010 20:16

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

Dimon · 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 · Post by **kme** » Wed 05 May 2010 15:34

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

Dimon · 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.