Execute .sql script within a stored procedure without using xp_cmdshell

Question

Execute .sql script within a stored procedure without using xp_cmdshell

juniorDBA13

Ten Centuries

Points: 1049
More actions
December 20, 2017 at 6:42 am

#104433

Hi,
I want to execute a .sql script within a stored procedure but I want to do it without enabling xp_cmdshell.
Any ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

martinkuen SSC Rookie Points: 40 More actions · Answer 1

I didn't try this way but it could work. You could use a BULK INSERT and write the content of the .sql file into a table and then execute this content as dynamic SQL.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

juniorDBA13 - Wednesday, December 20, 2017 6:42 AM
Hi,
I want to execute a .sql script within a stored procedure but I want to do it without enabling xp_cmdshell.
Any ideas?

Are you sure that's a good idea? You'd want to make very sure that nobody has access to modify the .sql file, otherwise all sorts of damage could be done.

John

Thom A SSC Guru Points: 99126 More actions · Answer 3

What is the .sql file doing? Why are you wanting to execute SQL from a file in a (shared) file path? Is it so that it can be dynamic? Is it procedurally generated perhaps? There are far safer ways than just executing a .sql file. Like John said, if anyone has access to modify it, they could easily do a huge amount of damage.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Thom A - Thursday, January 4, 2018 3:54 AM
What is the .sql file doing? Why are you wanting to execute SQL from a file in a (shared) file path? Is it so that it can be dynamic? Is it procedurally generated perhaps? There are far safer ways than just executing a .sql file. Like John said, if anyone has access to modify it, they could easily do a huge amount of damage.

+ 1,000,000 to that!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

juniorDBA13 - Wednesday, December 20, 2017 6:42 AM
Hi,
I want to execute a .sql script within a stored procedure but I want to do it without enabling xp_cmdshell.
Any ideas?

This is a form of "Death by SQL" no matter how you do it. Perhaps if you explained why there's a need to do such a thing and what the script(s) will actually do, we might be able to recommend a much safer method.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)