September 24, 2003 at 8:19 am
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,
September 24, 2003 at 10:08 am
maybe add a cscript to the front of the call? Might have a pathing issue.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 24, 2003 at 12:18 pm
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?
September 24, 2003 at 4:52 pm
It's possible. xp_cmdshell runs under the SQLAgent context. run this as an account and then try it.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 25, 2003 at 4:02 am
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.
September 25, 2003 at 6:39 am
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.
September 25, 2003 at 6:46 am
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
September 25, 2003 at 6:52 am
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?
September 25, 2003 at 8:08 am
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
September 25, 2003 at 9:25 am
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.
September 25, 2003 at 9:32 am
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
September 25, 2003 at 7:40 pm
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?
September 25, 2003 at 7:51 pm
Hi, all the action is happening on a Windows 2000 server.
September 26, 2003 at 7:03 am
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
September 26, 2003 at 7:26 am
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