0

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

Improve this question
9
  • Can you provide some very basic code samples of what you are attempting? Commented Jun 23, 2022 at 19:12
  • 3
    T-SQL is a terrible language to do general programming in, or even just batch scripting. Repent of this madness before it consumes you and any of your future maintainers in a pit of eternal despair! ...well maybe that's a bit too dramatic but basically, whenever you find yourself using xp_cmdshell you should seriously reconsider using something like scheduled tasks (on the OS end). Also, Invoke-Sqlcmd is a poor re-implementation of sqlcmd and lacks support for properly parameterized statements -- sqlcmd variables suck. Consider using SqlConnection/SqlCommand directly. Commented Jun 23, 2022 at 19:15
  • 1
    All the stuff you're trying to do looks like it could be implemented proficiently through a handful of stored procedures (rather than individual script files) that are called "sanely" -- either from a job or a scheduled task-driven script. I'll also just leave this here in case it's of use. Commented Jun 23, 2022 at 19:18
  • If you are using sqlcmd then it does support sqlcmd commands; so you could use those to run the contents of the second SQL file within the first one. I, don't like the idea though. See sqlcmd Commands (specifically the :r command). Commented Jun 23, 2022 at 19:19
  • The PS file uses 'Invoke-Sqlcmd' to get the execution output from the "main" SQL file, which in turn handles processing of the "support" SQL file, which dynamically builds the query and outputs the result. And yes, the code would be helpful. The "support" SQL files use ELSE IF logic to determine which dynamically generated (parameterized) query runs based on the request from the "main" SQL file. The "main" SQL file determines the overall parameters of the data requested (and passed to the "support" SQL file) based on input parameters from the PS file. Commented Jun 23, 2022 at 20:21

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.