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.
| Author |
Topic |
|
dogli
Starting Member
32 Posts |
 Posted - 2002-09-09 : 00:57:31
|
| Hi experts here,I want to run a ASP file with a trigger. here's what I have done:1) created a trigger on a table as below:CREATE TRIGGER [testtrigger] ON [dbo].[UserInformation] FOR INSERT, UPDATE, DELETE ASexec master.dbo.xp_cmdshell 'c:\temp\test.vbs /param1:abc /param2:xyz'2) here's my test.vbs file:Set WshShell = WScript.CreateObject("WScript.Shell")param1= WScript.Arguments.Named.Item("param1")param2= WScript.Arguments.Named.Item("param2")URL = "http://www.domain.com/adduser.asp?param1="¶m1&"param2="¶m2WshShell.Run(URL) set WshShell = Nothing3) I have a ASP file called adduser.asp and database connection is correct as I have verified. The data will be inserted into a table named Newuser.When I add or delete some records from UserInformation table, the trigger is fired as I do not receive any errors. But when I check NewUser table, there is not any record.Does anybody here can help me to find the problem? Thanks in advance.Dogli |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-09-09 : 01:32:49
|
| That is a REALLY REALLY REALLY bad way to do things.I don't think that will work, but if it did, you shouldn't do it :1. Your performance will suck.2. You would have browser windows popping up all over your server.3. If the asp page isn't available, your insert would hangWhat does the adduser.asp page do ? Is it on the same server as the database ?There are better ways to do things that this, I can think of about 9 alternatives off the top of my head, but I want to find out exactly what you need to do first to save myself typing.Damian |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-09 : 07:37:04
|
| The trigger will hold lock in an implicit transaction so your asp file will be blocked if it tries to access anything held.Also it will get a new connection so will not be incorporated with the transaction and you can end up with inconsistent data in the database.Agree with Merkin that this is probably the worst way of trying to do this (whatever it is you're trying to do).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 09/09/2002 07:38:05 |
|
|
|
KHeon
Posting Yak Master
135 Posts |
Posted - 2002-09-09 : 07:49:18
|
| Not to mention allowing the use of xp_cmdshell in a web-based environment. Should your server passwords be comprimised, a hacker could do just about anything with xp_cmdshell. I've read that it is best to disallow use of xp_cmdshell through and through.Kyle HeonPixelMEDIA, Inc.Senior Application Programmer, MCPkheon@pixelmedia.com |
|
|
|
dogli
Starting Member
32 Posts |
Posted - 2002-09-09 : 08:54:09
|
| I am sorry to hear so bad news. But I do appreciate all of your help!!Yes, the addUser.asp file is in the same machine with the SQL server. I was required to try this way by my system administrator. He thinks it's easy to deal with a lot of business logic. In fact, what I want to do is:1) Retrieve data from a Oracle database to SQL server database every day at a preset specific time. 2) Then, I need to deal with many business logic to register user for a training. Say, if you are in New York state, you go to New York City. IF you are in CA, you go to LA, and so on. The system also needs to send e-mail to those participants and their managers. I am new to this field, if any of you can give me further help, It's highly appreciate!dogli |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-09-09 : 19:47:05
|
| So you are adding users and sending emails.Put these commands into a table (or tables) in the trigger.Have a scheduled task(s) which do through the table(s) actioning the commands.You will maybe have a minute of two delay but that certainly won't matter for the email and probably won't for the user add.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
why cann't I use xp_cmdshell within a trigger?