SP + Triggers + ODBC - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SP + Triggers + ODBC

Author

Topic

Gaby
Starting Member

2 Posts

Posted - 2007-09-13 : 10:59:12

SQL Server
sp TEST
begin
insert table1 (inserts one record)
insert table2 (records to insert may vary from 1 - n)
insert table3 - this one executes a trigger which does updates, inserts and finally writes a file to disk with the info in tables 1, 2 & 3
insert
delete
delete
delete
end

Client App using ODBC:

begin tran
call sp_1
call sp_2
call sp_3
commit tran

If the sp TEST has to insert up to 5 records in table 2, everything goes fine.
But if it has to insert more than 8 records, fail with no errors. It seems it doesn't process the commit stmt.
But I don't know why. If I add to the sp SET NOCOUNT ON, it works. But I have no guarranty of what could happen if I add more records...
If I run the sp in the Query Analizer works OK...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-13 : 11:05:01

my first guess would be you're processing row by row and not set based.
because of this and writing to a file you somehow get file access denied.
that would be my first assumption.

however you should post the code of your stored procedure and your trigger.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Gaby
Starting Member

2 Posts

Posted - 2007-09-13 : 11:09:25

No, I don't think that's the problem because the file is written OK. But all the info in the tables disappeared (rollback). It seems it ignores my commit stmt. It has to be sth between the sp and the client app....

quote:
Originally posted by spirit1

my first guess would be you're processing row by row and not set based.
because of this and writing to a file you somehow get file access denied.
that would be my first assumption.

however you should post the code of your stored procedure and your trigger.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:13:44

SET NOCOUNT ON

is the number on rule to add to your list of todo's for all sp.
Otherwise the client might interpret (0 record(s) affected) as a resultset. but that will fail.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources