| Author |
Topic |
|
mmayrell
Starting Member
6 Posts |
 Posted - 2004-07-13 : 13:05:21
|
| I have a massive trigger that is used to track database activity. I spent an hour removing lines of code from this trigger and found out which line was causing the error and it turns out any line that uses a select statement errors out the classic asp. ****************************************************************CREATE TRIGGER Users_audit on etalk_live_user.Users FOR insert, update, DELETEasDECLARE @ICM int SELECT @ICM = ICM FROM inserted****************************************************************The Trigger functions correctly, it does as it should for ASP.NET and works correctly in for Query Analyzer. But for some reason I get this error in classic ASP:********************************************************Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ','./admin/v2/newuser_profile_script.asp, line 503********************************************************It is strange that the page that has the error only has 200 lines of code. If anyone has any suggestions or any input into my issue your help would be greatly appreciated. |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-13 : 13:34:10
|
| Are you sure the error is in the Trigger?I would suggest that you capture the actual SQL you are executing (at line 503) and try that in Query Analyser and see if you get an error in the "Users_audit" trigger or somewhere else.Only other thought: is the "ICM" column, in the table, of type INT or something else? If its varchar the inplicit cast to INT (@ICM is an INT) may be doing the damage.(Also, not sure I like the look of "SELECT @ICM = ICM FROM inserted" - that is going to store ONE value into @ICM but the trigger will process how ever many records there are in the INSERT/UPDATE - is that what you are intending?)Kristen |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-13 : 13:53:51
|
yes, I agree with Kristen that your SELECT @ICM = ICM FROM inserted piece of your trigger needs a WHERE clause to ensure that you don't try to load more than one row into your variable. maybe you could probably put a TOP 1 clause in there to test to see if this is in fact the problem.-ec |
|
|
|
mmayrell
Starting Member
6 Posts |
Posted - 2004-07-13 : 14:30:57
|
| Thank you for your comments, but as I stated this is a very small portion of the code, this code is usually executed in side an if statment if(@@Rowcount = 1), so this statement can only execute for one row. Also as stated, my code on the page only has 200 lines of code, so it isn't possible to debug the code at line 509. I also know that the error is a result of the trigger, if the trigger is removed the page works correctly. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-13 : 14:44:04
|
| The trigger should have nothing to do with the page...It's an event that occurs based on what you define...it's something with the asp code.....Brett8-) |
|
|
|
mmayrell
Starting Member
6 Posts |
Posted - 2004-07-13 : 14:46:53
|
| That would be my guess to, but there is no error when the trigger is removed therefor the trigger is the cause. For some reason it looks as if the trigger is trying to return some value to classic asp, causing the error. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-07-13 : 14:57:44
|
| Triggers don't return values....there's nothing for it to return it to....And the error say it's at line 503 in the asp....where's that code?Brett8-) |
|
|
|
mmayrell
Starting Member
6 Posts |
Posted - 2004-07-13 : 15:02:48
|
| the page /admin/v2/newuser_profile_script.asp only has 210 lines of code so it is making this issue very difficutl to debug. I aslo noticed that if I have a print statement, print 'this', in the trigger it errors saying [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'this'. But this works just fine in asp.net, and in Query Analyzer. I am begining to think there is a bug in the ASP ADO driver |
|
|
|
mmayrell
Starting Member
6 Posts |
Posted - 2004-07-13 : 15:03:38
|
| the page /admin/v2/newuser_profile_script.asp only has 210 lines of code so it is making this issue very difficutl to debug. I aslo noticed that if I have a print statement, print 'this', in the trigger it errors saying [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'this'. But this works just fine in asp.net, and in Query Analyzer. I am begining to think there is a bug in the ASP ADO driver |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-13 : 15:07:08
|
quote:
Originally posted by mmayrell
That would be my guess to, but there is no error when the trigger is removed therefor the trigger is the cause. For some reason it looks as if the trigger is trying to return some value to classic asp, causing the error.
How many rows are you inserting? If you insert more than one row, your trigger will blow up. You can only put one value in your @ICM variable. -ec |
|
|
|
mmayrell
Starting Member
6 Posts |
Posted - 2004-07-13 : 15:10:46
|
| Thank you ec but this issue has already been addresed in this Topic "this code is usually executed in side an if statment if(@@Rowcount = 1), so this statement can only execute for one row" |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-07-13 : 16:44:26
|
quote:
Originally posted by mmayrell
Thank you ec but this issue has already been addresed in this Topic
oh |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-14 : 00:26:50
|
| show us the ado objects you are using for the insert. thanks. |
|
|
|
chacha
Starting Member
39 Posts |
Posted - 2004-07-14 : 00:45:58
|
| It probably says line 503 in the asp code because there are includes, which are 'included' into the main body of the page. If you had 300 or so lines of code in you includes you would see this error on line 503. Add the lines of code in the include, then subtract those from the 503 number and that is the line in your page that you are getting the error. Then post the relevant lines of asp - all the ado stuff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-14 : 02:18:12
|
| I would still like to see the exact SQL that is executed at line 503 - put a "PRINT" statement in there, or somesuch, to capture it (or use PROFILER to see what is being passed to SQL). I reckon that will lead us to the problem.Kristen |
|
|
|
|
Triggers firing from classic ASP code