Invoke-DbaQuery - Parse Query

[talburo]

Verified issue does not already exist?

I have searched and found no existing issue

What error did you receive?

When using -NoExec on a script that creating or altering a procedure or view you will receive an error stating “CREATE/ALTER PROCEDURE must be the first statement in a query batch” and if you have RETURN statement in the code you get “A RETURN statement with a return value cannot be used in this context.” Script parses fine in SSMS and if you remove the -NoExec flag it will execute script fine.

I use the -NoExec flag when preparing for an implementations to make sure no issues with any of the script files.

Steps to Reproduce

# provide your command(s) executed pertaining to dbatools
# please include variable values (redacted or fake if needed) for reference

Invoke-DbaQuery -SqlInstance …. -Database …. -File …. -NoExec

Please confirm that you are running the most recent version of dbatools

dbatools 2.0.4 dbatools.library 2023.5.5

Other details or mentions

No response

What PowerShell host was used when producing this error

Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe)

PowerShell Host Version

Sorry GitHub blocked at work so can’t copy and paste

PSVersion 5.1.17763.4840 Build Version 10.0.17763.4840 CLRVersion 4.0.30319.42000 WSManStackVersion 3.0 PSRemotingProtocolVersion 2.3

SQL Server Edition and Build number

Enterprise 16.0.4065

.NET Framework Version

4.8.4645.0

[niphlod]

hi @talburo , unfortunately, you're not using the right tool for the job.

Invoke-DbaQuery is meant to run queries, not scripts. It just has basic parsing of GO delimited queries in a single "batch" .

If you need to run scripts "that parse fine in SSMS", the full might of parsers is available to you via database.Query() (I know, that should be really renamed to ExecuteScript() or something like that).

BTW, @potatoqualitee : would it be possible to "alias" .Query() as .ExecuteScript() ? . I know some function use .Query() and I'm not against it, it's just to have a name better aligned with the capabilities.

[talburo]

So there isn’t a way when you prefix SET NOEXEC ON and append SET NOEXEC OFF to each batch with GO?

SET NOEXEC ON GO

GO SET NOEXEC OFF

[niphlod]

When you use -NoExec, it simply prepends and appends

$runningStatement = "SET NOEXEC ON; " + $piece + " ;SET NOEXEC OFF;"

$piece here is whatever is in between of each "GO"

so there's reaaaally no value added in you splitting it and adding your own NOEXECs.

[talburo]

Not adding my own, but seeing the example above won’t work of what I was trying to ask. Was wondering adding “GO”

$runningStatement = "SET NOEXEC ON; GO" + $piece + " ; GO SET NOEXEC OFF;"

But that won’t work it will error out, unless you can run SET NOEXEC ON on session first then execute $piece.

[niphlod]

"GO" has no "real" meaning in a query, while it does inside SSMS and full parsing

[talburo]

Yep, thanks for looking into this will close issue