Execute Script from File?

[Kristjan]

how to execute a script from a file in a store procedure?

example:

-- (* UPD: 08/07/2003 *)
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'pr_ExecScriptFF' AND type = 'P')
DROP PROCEDURE pr_ExecScriptFF
GO
--Ejecutar script desde un archivo.
CREATE PROCEDURE pr_ExecScriptFF @File varchar(500)
AS
BEGIN
EXEC ????sp_executesql???? @File
END
/* EJEMPLO
DECLARE @archivo varchar(500)
Set @archivo='c:\MyData\HO_ACT200307080924.sql'
EXECUTE pr_ExecScriptFF @archivo
*/

Note:
HO_ACT200307080924.sql:
--Inserts para la Tabla: HO_Energia
INSERT HO_Energia VALUES(171,'HO_1','25/06/2003','HO_2',1,1081783,1085080,1085875,1087590,1088445,1089820,NULL,1650,6387)
INSERT HO_Energia VALUES(172,'HO_1','14/06/2003','HO_1',1,993418,993418,993418,993418,993418,1000952,NULL,0,7534)
--Inserts para la Tabla: HO_RegHorario
INSERT HO_RegHorario VALUES(7683,'HO_1','8','25/06/2003 19:00:00',NULL)
INSERT HO_RegHorario VALUES(7684,'HO_2','8','25/06/2003 19:00:00',NULL)

etc....

:> I look in MSDN library for a sp_ but nothing...
*.sql may have 10000 lines.

tnx 4 help me.

 

[ClaireHCM]

Try this


IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'table1' AND type = 'u')
DROP PROCEDURE pr_ExecScriptFF
GO
--Ejecutar script desde un archivo.
CREATE PROCEDURE pr_ExecScriptFF @File varchar(500)
AS
BEGIN
EXEC (@File)
END

 

[Glasgow]

I started a similar thread myself recently and got no feedback.

One possible way is to use xp_cmdshell to fire a DOS command that then uses the osql utility - e.g.


SET @DosCmd= 'osql -E -S YourServer -i ' + @File
exec master..xp_cmdshell @DosCmd

but not exactly an ideal solution.

 

[Kristjan]

doesn´t work...
The code "ClaireHsu"... no work because "EXEC" try to run @File and @File is: "c:\MyData\HO_ACT200307080924.sql" MsSQL raise error, obvious c:\... etc has sintaxis error. I need to run the script in that file!!!

and "Glasgow" i test that code, first i learn how to use osql, i never used before "its similar to tsql(FreeTDS), or viceversa"
i run cmd command in windows 2000 and try to connect with my DB:
osql -E -S <MyServer> -d <MyDATABASE> -i <dir>
this is ok, i can select, etc...
but when i run the file:
1. all script execute ok and Inserts, etc makes well
2. next seems like don´t stop and begin to raise exceptions
like this:

Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__HO_Energia__5C6CB6D7'. Cannot insert duplicate key in object 'HO_Energia'.
The statement has been terminated.

i need to use a terminate character or something?

in MSDN osql help i find:
&quot;When using osql interactively, you can read an operating-system file into the command buffer with :r file_name. Do not include a command terminator in the file; enter the terminator interactively after you have finished editing.&quot;