CODEWITHSUNDEEP
sql-serverasp-classicado
JustNod
JustNod

Reputation: 53

Limits on ADO connection execute queries

I have a SQL script that does quite a few updates. When I retrieve the script (in a Classic ASP page) from a text file and attempt to execute it, it does not appear to apply all the updates I expect yet if I copy and paste the content into Management Studio, the query works fine. I cannot work out why this might be the case. Is there a limit as to how many statements/operations you can include in such a script?

Also the execute command oConn.Execute strSql does not fail, it just moves to the next line of classic ASP code. Is there any way I can test for errors?

Upvotes: 1

Views: 370

Answers (2)

user1945782
user1945782

Reputation:

Unfortunately I don't have enough reputation to comment on your question, but have you tried wrapping your code in TRANSACTION blocks? Very useful if things go wrong.

As for error handling, you can refer to the @@ERROR...

DECLARE @ErrorVar INT

RAISERROR(N'Message', 16, 1);
IF @@ERROR <> 0
    -- This PRINT statement prints 'Error = 0' because
    -- @@ERROR is reset in the IF statement above.
    PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8));
GO

Upvotes: 2

JustNod
JustNod

Reputation: 53

Paul set me on the right track here, thank you. I've been coding SQL for years but confess I have dabbled only a little in the world of transactions. After the code below I put other stats and the failure message into a table that I interrogate in the ASP code after calling the script.

BEGIN TRY BEGIN TRANSACTION --My long update script placed in here
COMMIT TRANSACTION END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION SET @FailMsg = ERROR_MESSAGE() + ' Failed at line ' + CAST(ERROR_LINE() as varchar) + '.' END END CATCH

Upvotes: 1

Related Questions