CODEWITHSUNDEEP
sql-serverpowershellt-sql
Tom
Tom

Reputation: 31

Can a PowerShell script file call a SQL file which references another SQL file without using xp_cmdshell

I had originally built out a set of SQL files where the main SQL file calls another SQL file with by passing variables and using xp_cmdshell to run a PowerShell command for the Invoke-Sqlcmd cmdlet. Got everything working nice and neat (here's the link), but then tried to take it "up a notch" by starting the process with a PowerShell script to call the main SQL file via a set of variables, which then calls the relevant "support" SQL file with a set of variables. (PS => SQL => SQL)

All working fine on the dev machine, but no worky at customer server due to needing a proxy account to run PS that calls xp_cmdshell. Not a big issue, but definitely starts adding more "baggage" to the deployment. And of course, I would like to move completely away from xp_cmdshell, which is why I was trying to call the system of SQL files from PS.

There is quite a bit of query logic in the main SQL file, so converting from SQL to PS is quite daunting, but a possibility. I also tried using 'sp_executesql' to call the "support" SQL files, but the output from BulkColumn via OPENROWSET only returns the text output, and the parameters do not actually process within the SQL file like when called via 'Invoke-Sqlcmd' using variables. Use SQL OpenRowSet BulkColumn to Insert data from .txt File

If I cannot find another way to do this, I could save the "support" SQL files as stored procedures to prevent the extra SQL file calls from the "main" SQL, which should work, but I was hoping not to introduce additional objects into a customer's on-premise database.

Any ideas on this, or has the "mad scientist" approach gotten a little too mad...

Upvotes: 0

Views: 201

Answers (1)

Seva Alekseyev
Seva Alekseyev

Reputation: 61341

Call both SQL files from Powershell, pass the data in connection specific temporary table(s) instead of variables - tables with names starting with #. Create them in the usual way, with create table. They live as long as the current connection lives, so no need to destroy explicitly, although it won't hurt. When creating, though, do a "drop if exists" first, just in case connection pooling gives you a connection with the temp table already in it.

This way, there is no need to execute SQL files from SQL. That's not a well supported scenario to begin with.

Alternatively, concatenate both SQL files within Powershell into a third one, and execute that.

Upvotes: 2

Related Questions