Run a SQL script file from an ASP.NET page

Hi there,

I would like to know how can I run a SQL Server 2000 script file (*.sql) from an ASP.NET page. I don't wanna run it from the SQL Query Analyzer, but run it the page itself, I mean dynamically.

Is there any object, method, or whatever to do it?

Thanks in advanceHmpf.

1: Load file into stroing variable.
2: open connection to server.
3: create SqlCommand object.

and now carefull - the HUGH trick:

4: assign string from file to CommandText property of the command.

and...

5: execute.

That simple, really.|||Hi again,

It works!

Thank you very much!|||you mean MyCommand.Execute("myFile.sql") ?
or you must do a readfile (readAll) first ?
thank you
|||it works for CREATE TABLE but for view or procedure I get

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]'CREATE VIEW' must be the first statement in a query batch.

do you know why ?
my script >>
CREATE TABLEdbo.Label
(
[id] [int] IDENTITY (1, 1) NOT NULL ,
[value] [varchar] (255) COLLATE French_CI_AS NULL ,
)
ON [PRIMARY]

CREATE VIEW dbo.Label
AS
SELECT value, id
FROM dbo.Label
WHERE (value = '100')

thank you for helping

|||it seems that nobody knows how to run VREATE VIEW or PROCEDURE from sql file
|||You need to put a GO statement after each command.
|||no it doesn't work !
I can run a .sql file with create TABLE but not with create VIEW or PROCEDURE
|||Are you certain of your current error message? In the script youhave provided you are calling both objects you are attempting to create"Label", which is not possible. Also, you will receive an errorif the object "Label" already exists in your database when you run thescript.
|||now it works !!
thank you
but how can I insert with a procedure from queryAnalyser ?
If I run
CREATE PROCEDURE [dbo].Insert_all AS
INSERT INTO Config (nom, valeur, valide) VALUES ('langDef','0',1)
INSERT INTO Config (nom, valeur, valide) VALUES ('Login','1',1)
INSERT INTO Config (nom, valeur, valide) VALUES ('Open','1',1)
GO
nothing is inserted in my database

|||You have to EXECUTE the stored procedure in order for the commands it contains to be performed.

EXECUTE dbo.Insert_all

|||it works fine !!!:)))
thanks a ton
|||I can run it from QueryAnalyser but from ASP NET ...first iof all : I must remove the GO
then it works for CREATE TABLE
but if i add this statement >>
CREATE PROCEDURE [dbo].dt_Component
AS
DELETE FROM Component
it doesn't work ... even the tables are not created
for any pROCEDURE or VIEW it doesn't work at all


|||Quentin, it is impossible to help you when you only tell us "it doesn'twork". Certainly there must be associated error messages. Withoutthose error messages and your vague descriptions it is extremelyfrustrating to try to help you. And at this point you havesupplied us with so many varying pieces of code and scenarios that Ihave no idea what you are asking.
Could you please try again, stating exactly what code you are running,from where, and what the behavior you are seeing is, along with anyerror messages?
|||I am trying a very simple .sql file
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Config]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Config]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dt_Config]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dt_Config]
CREATE TABLE [dbo].[Config] (
[id_Config] [int] IDENTITY (1, 1) NOT NULL ,
[nom] [varchar] (50) COLLATE French_CI_AS NULL ,
[valeur] [varchar] (255) COLLATE French_CI_AS NULL ,
[valide] [bit] NULL
) ON [PRIMARY]
but if I add >>
CREATE PROCEDURE [dbo].dt_Config
AS
DELETE FROM Config

it does not work ... it is just impossible to create view or procedure from ASP NET
and if I add the GO between each command it bugs at the first GO