call an ASP page using xp_cmdShell

Hi, here is what I'm trying to accomplish. I'm using Win2k, IIS, SQL Server 2000 sp3a. I'm trying to call an ASP page from the SQL environment. The ASP page updates a record in a database.

Code in Query Analyzer:
exec master..xp_cmdshell 'c:\temp\test.vbs'

If I double-click the vbs file from Windows Explorer, the vbs file calls the asp file and the database updates. When I run the code in SQL I get a message telling me 1 row was affected, yet no record was written to the DB. I've read an article telling me it was possible to call an ASP page from SQL. http://www.eggheadcafe.com/articles/20010426.asp . But it does not work for me?

Code for VBS and ASP files, I don't think the problem is here though?

VBS:
Set WshShell = WScript.CreateObject("WScript.Shell")
URL = "http://localhost/spg/sprocData.asp"
WshShell.Run(URL)
set WshShell = Nothing

ASP:
dim objConn1,objCmd1, strSQL, adCmdText
adCmdText = 1
strSQL = "INSERT INTO tblXMLInsert (SEQ, VENDORID) VALUES ('test','test')"

set objConn1 = Server.CreateObject("ADODB.Connection")
objConn1.Open "Provider=SQLOLEDB;Server=SERVER;Database=SPG;UID=t est;PWD=test"
set objCmd1 = Server.CreateObject("ADODB.Command")
set objCmd1.ActiveConnection = objConn1
objCmd1.CommandText = strSQL
objCmd1.CommandType = adCmdText
objCmd1.Execute
set objConn1 = Nothing
set objCmd1 = Nothing

Thanks for your help.Sorry dude....

I have to say this...Why the heck are you making life hard for yourself?

Web stuff stays on the web server
SQL stuff stays on the SQL Server.

xp_cmdshell is a VERY DANGEROUS utility - it usually operates at sa level and unless you want to trash your SQL server - dont use it. Just dont!

It may be the permissions of the user you are using to access SQL server from the seb server. Make sure the test user has change permissions on the table you are trying to alter. Start SQL Analyser & connect to the database using the test user. If you manually run the TSQL code in SQL Analyser on the server & it works, then its not permissions issue for the user.
It may be your vbs code.

Post back if problems.

Cheers,

SG|||hahahaha... thats right, dont mix...

I never cant do that... If you can... tell me how :)|||Let me explain what I'm trying to accomplish, it sounds like there is a better way to do this. I have an insert/update trigger that will use the xp_cmdshell command. I call the ASP page with 1 variable. The ASP page queries the database for a bunch of records, based on the variable I receive from the trigger. The returned recordset is parsed and converted to XML then sent to an external server. The ASP page receives the status of the file transfer and I update our database accordingly. The code posted above obviously does not show all these steps. Thanks|||Hi

CAN I BE MORE BLUNT???

DONT USE XP_CMDSHELL FOR THIS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

ASP is designed to interact with a database, and send/retrieve information and I'm guessing you are still learning how to do this. Best method of attack is not to short circuit security ( and any good DBA will tell you this ) by not uising xp_cmdshell ( A good DBA will also "break" your fingers for using xp_cmdshell so be warned ). Not all you read on development boards is correct nor security minded.

Get all your development in ASP correct from the ground up & with solid engineering approach. You will be better off in the long run AND if someone managed to exploit this xp_cmdshell link, they could easily compromise your site security - ( www.sqlsecurity.com ) .

Fancy a pink slip for this effort? It would be worth doing to get rid of someone who KNOWINGLY compromised my site security.

SQL 2000 will return records in XML natively so you can do everything within TSQL and pass the trigger result back to an ASP page easily enough. Work it out correctly - dont make nasty short cuts.

HTH

Cheers,

SG.|||Hi, sqlguy7777. Your last statement says:
SQL 2000 will return records in XML natively so you can do everything within TSQL and pass the trigger result back to an ASP page easily enough. Work it out correctly - dont make nasty short cuts.

Creating XML in TSQL is no problem, my problem is passing the trigger results back to the ASP page. How do I do that, if xp_cmdShell is not the way to go what is the better way. Thanks|||Howdy,

I think you may have to change the approach to the problem & how you are handling the changes to the data & the database.

I was thinking through the problem, and it occurs to me you are making life difficult for yourself.

Instead of messing around with vbs scripts & triggers shooting info backwards & forwards, keep all the database functionality within the database. You could write a trigger result to a table, then scan the table frequently via a script to look for a change and then kick an action off. If you are only scanning a small table it would take milliseconds for a scan so unless your server is being flogged into the ground, speed should not be an issue. ( assuming you are not doing this 1000s of times a second, in which case using a RAM based pinned table would be much quicker. )

Keep it simple & it stays robust/reliable. As soon as it gets messy, the chance of things going wrong goes throught he roof. I speak from hard learned experience. Of course, you can totally dismiss what I'm saying ( which is you choice of course ) but I am trying to save you some grief.

I'd also suggest you talk to your DBA - quite often they can help with design issues and it makes long term support of systems easier & cheaper & faster.

HTH,

Cheers,

SG.