Martina

Thank you.

My question concerns the behavior in .NET.
The query runs slowly when i used command type Text, but when I make it a stored procedure, it flies.
In both cases I'm using the .NET connection.


>Hi, the problem cause different default MSSQL connection properties and database properties aka "Artithmetic Abort".
>
>Default values are:
>MSSQL server - OFF
>Database - ON
>SSMS - ON
>
>If you connect MSSQL from VFP, then connection use default connection's properties defined on MSSQL server.
>
>You can change default connection's properties defined on MSSQL server (and restart instance) or change the setting for your session after open connection :
> ?SQLEXEC(handle, "SET ARITHABORT ON")
>
>MartinaJ
>
>>

>> ClearParameters();
>>                    AddParms("@selectdate", reportdate, "SQL");
>>                    AddParms("@bankfedid", bankfedid, "SQL");
>>                   
>>string commandtext = @"
>>					 select COALESCE( m.storename, ' ') as storename, COALESCE(m.serialnumber, ' ') AS serialnumber, dbo.GetNextPickupdate(@selectdate,store) AS nextpickupdate, t.*, t.hundreds + t.fiftys + t.twentys + t.tens + t.fives + t.twos + t.ones + t.mixedcoin AS totaldeposit from smartsafetrans t 
>>					 LEFT JOIN view_ExpandedSafemast m ON t.safeid = m.idcol
>>                     where t.bankfedid = @bankfedid AND t.eventcode = 'DECL' AND t.postingdate <= @selectdate  and (t.verifyid = 0  OR 
>>                     (verifyid <> 0 AND  verifyid IN  (SELECT idcol FROM smartsafetrans WHERE bankfedid = @bankfedid AND eventcode =  'VER' AND postingdate > @selectdate )))";
>>

>>When I run the above in a C# program as command type Text, it runs over a minute
>>The same query runs less than a second in the SSMS query window.
>>When I put the query in a stored procedure and called command type stored procedure it ran less than a second from C#.
>>I've never seen anything like this before.
>>Any ideas why this is happening?