|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
October 14th, 2004, 10:19 AM
|
||||
|
||||
MS SQL 6.5 triggers
OK guys - I'm really stumped. I've got a SQL 6.5 with an older MRP (I KNOW, it's ugly, but I can't migrate to 2000 just yet). The MRP uses triggers to accomplish a lot of things in the DB. The problem is that they were encrypted when they were created, so I can't view them in the Query tool. I thought, though, that if I did a
Code:
SELECT TEXT from syscomments C INNER JOIN sysobjects O ON C.ID = O.ID WHERE NAME LIKE 'triggername' that it would give me the encrypted (garbled) text. What I'm trying to do is run it through a decrypter because I need to see how it's processing an order so that I can remove an erroneous order from the system. My problem is that, although I know the trigger exists, when I run the above query, it produces no results. I found it odd, so I went to sysobjects and found the ID that matches the trigger name, and tried querying syscomments for all fields that match that ID - no dice. Can anyone tell me why there is no record of the trigger in syscomments?
__________________
Fisherman "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein Last edited by Fisherman : October 19th, 2004 at 12:50 PM.
|
|
#2
October 18th, 2004, 06:54 AM
|
||||
|
||||
|
wow... no dice, huh? If anyone can think of something I can do, please let me know. I can't even get service for this MRP (Millenia by FocusSoft) because the company is out of business. While I'm on the subject - we're planning a conversion. Does anyone know anything about Microsoft's NaVision MRP?
|
|
#3
October 18th, 2004, 09:42 AM
|
||||
|
||||
|
Start reading http://www.planet-source-code.com/v...Id=505&lngWId=5 it might be adaptable to your version.
__________________
My blog Tutorials about OSS databases, DBMonster ... Contribute to OSS Development, fill bug reports! Developer Shed eSupport Commented my.ini/my.cnf (ADD YOUR OWN CONFIG TRICK) An introduction to database normalization Natural or Surrogate key Custom ordering for your results Correlated and uncorrelated subqueries Don't turn your outer joins into inner joins Random data (with a bias) Last edited by pabloj : October 18th, 2004 at 10:29 AM.
|
|
#5
October 19th, 2004, 10:34 AM
|
||||
|
||||
|
WHOOOOHOOOOOOO - workaround! wrote a stored procedure that does the following...
1. exec sp_sqlexec 'exec sp_configure ''allow updates'', 1' 2. RECONFIGURE WITH OVERRIDE 3. Capture update trigger ID's for 2 affected tables 4. Set updated trigger ID's = 0 in sysobjects for 2 affected tables 5. Update 2 affected tables 6. Reset trigger ID's using captured ID numbers in sysobjects for 2 affected tables 7. exec sp_sqlexec 'exec sp_configure ''allowupdates'',0' 8. RECONFIGURE WITH OVERRIDE Wrap all that up in a transaction with lots of @@ERROR checking and Rollbacks, and you've got yourself a workaround!
|
|
#6
October 22nd, 2004, 09:29 AM
|
||||
|
||||
|
pab- I looked at the code you suggested - it requires SQL 7.0 or 2000 because varchars and chars in SQL 6.5 are limited to 255 - I don't think it's going to be large enough to hold what I need. My work around seems to be working for now, and we're upgrading to a new version of SQL Server and a new MRP soon, so hopefully this is a temporary issue. Thanks though!
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > MS SQL 6.5 triggers |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
