Execute .sql script within a stored procedure without using xp_cmdshell

  • juniorDBA13

    SSC Eights!

    Points: 990

    Hi,

    I want to execute a .sql script within a stored procedure but I want to do it without enabling xp_cmdshell.

    Any ideas?

  • martinkuen

    SSC Rookie

    Points: 40

    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: 148245

    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: 98211

    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.

  • Jeff Moden

    SSC Guru

    Points: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

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

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