Run SQL Script from Web App

Hi,

I have a asp.net 2.0 web app that needs to allow the user to run sql scripts on a database. I provided the user with a large text box which allows them to cut and past their script in. However the script contains complex SQL which cannot be handeled in the normal way of passing a string to a connection object and doing a ExecuteNonQuery.

The scripts contains words such as "GO" which makes teh script fail. Is there a wrapper or method for sending cuch SQL statment. Is there a stored procedures I can call or something that will allow me to excute the TSQL exactly in teh same way Query Analyser does?

Many thanks in advance

PS. yes im aware of the security implications of doing this, I have put the relevant security measures in place

check out the OSQL utility.|||

I am not sure but I think StoredProcedure can not do this because it executes SQL querys. I say I am not sure but if there is some body who is sure 100%, he should let us know:-)

|||

GO is not a valid T-SQL command, so no, you can't send it to the SQL Server and expect it to work. GO is a command to Query Analyzer/osql/sqlcmd/Management Studio, and if you want to emulate it's functionality, your program will have to handle it or call one of the above utilities to do it on your behalf.

Something like (pseudo code here):

dim cmds as string() =string.split({Your query},vbCRLf & "GO" & vbcrlf)

for each cmd in cmds

sqlcmd.commandtext=cmd

sqlcmd.executenonquery

next

|||

Hi

Thanks to everybody for their replies, I have been looking into OSQL Utility approach.

From my basic understanding of OSQL it can only be run from the Command Prompt so their for I would need to create a batch file. Will ASP.NET 2.0 let me call a batch file from within my web application?

Or is there another approach apart from using a batch file?

Many thanks in advanve