0

I have a stored procedure, in wWhich I m passing the script file (.sql file) as a parameter.

I want to know how .sql file gets executed through command (not command prompt).

exec my_sp VersionNumber, SqlDeltaScript.sql (it is a file)

I want my stored procedure to execute SqlDeltaScript.sql

Can anyone please help regarding this ...

Thanks in advance ...

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Praveen Kasani
  • 11
  • 3
  • At the very least, your stored procedure (and therefore your SQL Server service) needs access to the .SQL file. Have you established that? Perhaps you should try and explain the real problem as what you're doing doesn't make much sense, – Nick.Mc Mar 16 '15 at 10:05
  • I'm curious, does this have anything to do with this? http://stackoverflow.com/questions/29075083/how-to-pass-a-script-file-sql-as-a-parameter-to-the-sp-and-execute-in-ms-sqls – Nick.Mc Mar 16 '15 at 10:58
  • can u please tell me , 1) can I pass a .sql file as a parameter in stored Procedure?? 2) What if i pass the file path as a parameter and inside my sp can i execute that .sql file? – Praveen Kasani Mar 16 '15 at 11:04
  • 1) Yes 2) Yes. In fact the other very similar question I linked to looks like it's doing mostly that. If you'd taken the time to read my post you may have discovered that yourself – Nick.Mc Mar 16 '15 at 11:08
  • What exactly is going here? The other question was asked within minutes of yours and it has identical parameters - version number and script name. – Nick.Mc Mar 16 '15 at 11:09
  • another guy also working on the same issue. what we need exactly is 1. Can I Pass a .sql File as a parameter to an SP or not ?? – Praveen Kasani Mar 16 '15 at 11:11
  • Your colleague appears to already be doing it in his stored procedure. He is using `sp_executesql` to run it. There is also a suggestion below. Why don't you try some of these suggestions and post back specific errors. – Nick.Mc Mar 16 '15 at 11:13
  • Hi Nick, Thanks for the replies , can u please tell me, EXEC SP_EXECUTESQL @Script : Script is a .sql file which contains (my db changes script from sql delta comparision) which i want to execute. – Praveen Kasani Mar 16 '15 at 11:35
  • Did you look up the definition of `sp_executesql`???? This simply runs a SQL command, so you'd need to pass it the _contents_ of your SQL script (not the filename). How do you get the contents of a SQL script from a filename? You'd need to load the contents of the SQL script into a variable. To do this you could use `OPENROWSET` or `BULK INSERT` or even `xp_cmdshell`. But regardless of all this..... it's a **BAD IDEA**, because someone could sneak `DROP DATABASE` into it and you would run it. Also there are many reasons a SQL script may not execute nicely in `sp_executesql` – Nick.Mc Mar 16 '15 at 11:40
  • There are many tools now for database version control. I suggest you do not create your own. – Nick.Mc Mar 16 '15 at 11:40
  • Hi Nick, tried with OPENROWSET, didnt work for me (error :Cannot bulk load. The file "C:\Mytext.txt" does not exist.) My Script.sql file is having more than 30,000 lines, not sure how do i fix this now – Praveen Kasani Mar 16 '15 at 12:43
  • Is `C:\MyText` on your SQL Server? Does the SQL Server account have access to it? – Nick.Mc Mar 16 '15 at 12:53
  • A SQL script with 30,000 lines is almost definitely not going to run correctly inside `sp_executesql` – Nick.Mc Mar 16 '15 at 12:53
  • Why don't you want to just run it in `SQLCMD.EXE`? That's the way most existing version control tools work. – Nick.Mc Mar 16 '15 at 12:55

1 Answers1

0

This does not sound like an ideal situation, but if you have to do this then you could use xp_cmdshell to run the sqlcmd utility to run your script file.

The xp_cmdshell SP must be enabled in order ot use it - see Enable 'xp_cmdshell' SQL Server.

Community
  • 1
  • 1
Rhys Jones
  • 5,348
  • 1
  • 23
  • 44