running tsql from filetable

Question

running tsql from filetable

AK1516

SSCrazy

Points: 2705
More actions
October 11, 2013 at 11:21 am

#299638

I have used the filetable to store the tsql scripts. what can be the best way to run them against the server. the only road block i have using the exec(@sql) or execute_sql (@command) is the 'GO' statement in the filetable script.
Any help will be appriciated. Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

AK1516 (10/11/2013)
I have used the filetable to store the tsql scripts. what can be the best way to run them against the server. the only road block i have using the exec(@sql) or execute_sql (@command) is the 'GO' statement in the filetable script.
Any help will be appriciated. Thanks

Take the GO out. It won't work with EXEC or EXECUTE_SQL. If the GO is important to the success of the script, then you will need to split the script at the GO and execute each section separately.

--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)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

I have used the filetable to store the tsql scripts. what can be the best way to run them against the server. the only road block i have using the exec(@sql) or execute_sql (@command) is the 'GO' statement in the filetable script.
Any help will be appriciated. Thanks

There are different methods for querying the contents of a document stored in a filetable. I haven't done it personally but have seen Sam Nasr do it (SQL Saturday Detroit :w00t:). Sam Nasr has some articles out there about this.

That said, what you are doing sounds like a bad idea to me. Filetables are not intended to for storing documents that contain scripts (SQL or other) that are executed directly from the filetable. The idea of a file on a file share (secured or otherwise) with scripts that can be executed from inside SQL Server. That sounds scary for too many reasons to detail in one post.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

And with that and the fact that I've seen many people try to do the same thing, I guess now would be a great time to ask...

... what's wrong with using stored procedures, etc, instead of all this saving of scripts either in a file or in a table? It just doesn't make sense to me to do such a thing.

--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)