Problems running stored procedures

[Ryan]

I have two similar stored procedures which I'm running. One runs and
one doesn't. I can run both with no problems in SQL Enterprise (7.0
standard) and have checked the permissions and am happy with them.
Whilst the statements in each sp are different, I'm calling them in
exactly the same way (using Delphi 5 Windows 2000). I don't get any
trappable errors.

I ran a trace on what was happening and I get two different set of
results. This is what I'm trying to get my head around.

Client Trace (runs)

1 09:09:44 Log started for: Swift Client Import Utility
2 09:09:48 SQL Prepare: MSSQL - :1 =
dbo.CBFAUpdateS wiftClient;1
3 09:09:48 SQL Misc: MSSQL - Set stored procedure on or off
4 09:09:48 SQL Data In: MSSQL - Param = 1, Name = Result, Type
= fldINT32, Precision = 0, Scale = 0, Data = NULL
5 09:09:48 SQL Misc: MSSQL - Set statement type
6 09:09:48 SQL Execute: MSSQL - :Result =
dbo.CBFAUpdateS wiftClient;1
7 09:09:48 SQL Stmt: MSSQL - Close
8 09:09:53 SQL Connect: MSSQL - Disconnect NEW
9 09:09:53 SQL Connect: MSSQL - Disconnect NEW
10 09:09:53 SQL Connect: MSSQL - Disconnect PASSTHRU

Enquiry Trace (Doesn't run)

1 09:08:21 Log started for: Swift Client Import Utility
2 09:08:24 SQL Prepare: MSSQL - :1 =
dbo.CBFAUpdateS wiftEnquiries;1
3 09:08:24 SQL Execute: MSSQL - :Result =
dbo.CBFAUpdateS wiftEnquiries;1
4 09:08:50 SQL Prepare: MSSQL - :1 =
dbo.CBFAUpdateS wiftEnquiries;1
5 09:08:50 SQL Misc: MSSQL - Set stored procedure on or off
6 09:08:50 SQL Data In: MSSQL - Param = 1, Name = Result, Type
= fldINT32, Precision = 0, Scale = 0, Data = NULL
7 09:08:50 SQL Misc: MSSQL - Set statement type
8 09:08:50 SQL Execute: MSSQL - :Result =
dbo.CBFAUpdateS wiftEnquiries;1
9 09:08:50 SQL Vendor: MSSQL - dbrpcinit
10 09:08:50 SQL Vendor: MSSQL - dbrpcexec
11 09:08:50 SQL Vendor: MSSQL - dbsqlok
12 09:08:50 SQL Vendor: MSSQL - dbresults
13 09:08:50 SQL Vendor: MSSQL - dbnumcols
14 09:08:50 SQL Vendor: MSSQL - dbcount
15 09:08:50 SQL Stmt: MSSQL - Close
16 09:08:50 SQL Vendor: MSSQL - dbdead
17 09:08:50 SQL Vendor: MSSQL - dbcancel
18 09:08:56 SQL Connect: MSSQL - Disconnect NEW
19 09:08:56 SQL Connect: MSSQL - Disconnect NEW
20 09:08:56 SQL Connect: MSSQL - Disconnect PASSTHRU
21 09:08:56 SQL Vendor: MSSQL - dbdead
22 09:08:56 SQL Vendor: MSSQL - dbfreelogin
23 09:08:56 SQL Vendor: MSSQL - dbclose

I would have thought that these would be nearly identical. They call
sp's on the same servers in the same way, so the call to do this
should (in my mind) be the same.

I can post the sp's if anyone thinks they are of relevance.

I can of course set up these stored procedures to run at a certain
time, but I'd like to try and understand this a little more.

Thanks in advance.

Ryan

[Erland Sommarskog]

Re: Problems running stored procedures

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> I have two similar stored procedures which I'm running. One runs and
> one doesn't. I can run both with no problems in SQL Enterprise (7.0
> standard) and have checked the permissions and am happy with them.
> Whilst the statements in each sp are different, I'm calling them in
> exactly the same way (using Delphi 5 Windows 2000). I don't get any
> trappable errors.
>
> I ran a trace on what was happening and I get two different set of
> results. This is what I'm trying to get my head around.[/color]

There wasn't a wealth of information in your posting, but at least
I could conclude that you are using DB-Library, directly or indirectly.
Not that I know Delphi, but maybe you should investigate some other
client library. Not that DB-Library is bad, but unfortunately Microsoft
is not developing it any more, and you don't full support for new and
improved data types in SQL 7 and later with DB-Library.

One thing I notice is that there is a call to dbdead in the log. Possibly
this indicates that the procedure runs into a fatal error which causes
a crash on the SQL Server side.

One thing to try is to run the procedures from command-line ISQL,
because this utility also uses DB-Library, so you get the same settings
as you do with your Delphi app.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp