Calling 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 from SQL. http://www.eggheadcafe.com/articles/20010426.asp . But it does not work for me? Maybe there is a better way to do tis?

    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=test;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 a lot,

  • maybe add a cscript to the front of the call? Might have a pathing issue.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • Hi Steve, I don't think it's a path issue. If I double-click the VBS file from Windows Explorer the ASP file gets called IE opens and a record is added to the database. I think it has to do with the xp_cmdShell call from within SQL?

  • It's possible. xp_cmdshell runs under the SQLAgent context. run this as an account and then try it.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

    The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

    http://www.dkranch.net

  • hi!

    sorry, but why *the hell* would you want to do that. i'd understand if the ASP page you'd have to call cannot be maintained by yourself, but if you've access to the SQL logic that is done by that page, you should just run the respective statements in your database context (maybe a job).

    if your ASP guys use the same logic, the better: create a procedure that accomplishes your tasks and make the ASP guys call that procedure -> single code base, minimized maintainance effort.

    why am i insisting on avoiding something you want to do: leaving SQL server context (shall mean, the "core" stuff SQL server here for), adds instability to your whole system. we even avoid using extended stored procedures where possible, and all these precautions only have had positive effects to (1) the stability and (2) the performance of our systems.

    best regards,

    chris.

  • I have to agree with Chris. It doesn't make logical sense to call an ASP page that performs a SQL Query when you can just write a stored procedure and call it from the ASP Page as well as SQL Server, weather it be from Query Analyser or a DTS package or something else.

  • 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

  • quote:


    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


    That makes more sense but I think there is still a better way to accomplish this. So I have a few questions,

    1) How is the data inserted/updated originally? Application? DTS?

    2) Do you have access to the external server via VPN or some other method?

  • Answers:

    1) an end-user application

    2) I've installed the MS xml SDK and I'm using ServerXMLHTTP to connect to the remote server through SSL.

    My only problem though is triggering the ASP page from SQL.

    Thanks

  • Trigger the asp page from the End user app. The only catch here is that the end user has to always have network connectivity to that page.

    1- Call a stored procedure from the end user app.

    2- Within that stored procedure, do your update/insert (use different SProcs for Insert or update)

    3- Get the data back you are using for your ASP Page variable.

    4- Return the data to your end user app.

    5- End user app calls the ASP page via xmlhttp or some other component passing your variable to the page.

    The last step there would work better if you had network connectivity to the external server. Then you could just insert data directly via a linked server within the stored procedure.

  • The application is a purchased product and we have no access to the code. We only have access to the SQL database, that is why we thought a trigger would be best to call the ASP page. I can use the xp_cmdshell command to return operating system function like dir c:\temp\*.* ie. exec master..xp_cmdshell 'dir c:\temp\*.*' . This is why I do not think it is a security problem. Also I found an article, http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm , it explains some security setting changes since ServicePack 3 was installed. I have service pack 3 installed, so I loaded another machine with SQL Server 200 SP1 and I still could not get the xp_cmdshell to call the ASP page? I'm really stumped here. Thanks for your help.

    Edited by - asd on 09/25/2003 09:37:56 AM

  • Just a thought.

    Is this vbs file on the c: of the machine where the SQL server sits, or is it on the client machine, and you run QA on your client?

  • Hi, all the action is happening on a Windows 2000 server.

  • Sorry guys i thot this might be related...

    the code below doesnt work for me...any ideas

     
    
    declare @x varchar(20)
    set @x = 'notepad'
    exec xp_cmdshell @x

    cheers!

    Arvind


    Arvind

  • Change your vbscript to this:

    Dim XMLHTTP

    URL = "http://localhost/test.asp"

    set XMLHTTP= CreateObject("Microsoft.XMLHTTP")

    XMLHTTP.Open "POST", URL, false

    XMLHTTP.Send

    I tested the code sample given there and found the same issue. The code above will work however. If you want to pass variables to the web page just add them to the URL variable.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply