Executing large sql file against a database using powershell

Question

I'm trying to execute a QC script against a database. This script contains usage of cursors, a lot of sql statements (~400 lines), uses table variables and all kinds of fun stuff.

The script executes fine in SSMS.

I'm trying this to run it via powershell:

$adOpenStatic = 3
$adLockOptimistic = 3
$TargetDatabaseName = 'databaseCatalogName'
$DatabaseName = 'DatabaseName'
$QCChecksLocation = 'C:\thisPathWorks\QC Checks.sql'
Start-Process odbcconf -wait -ArgumentList "/A {CONFIGSYSDSN ""SQL Server Native Client 11.0"" ""DSN=$TargetDatabaseName|Server=$DatabaseName|Database=$TargetDatabaseName|Trusted_Connection=Yes""}"


$TargetConn = New-Object -comObject ADODB.Connection
$QCResults = New-Object -comobject ADODB.Recordset
$TargetConn.ConnectionString = "DSN=$TargetDatabaseName"

$TargetConn.Open()

$SQL = get-content $QCChecksLocation
$QCResults.Open($SQL,$TargetConn,$adOpenStatic,$adLockOptimistic)

This results in the error: Exception calling "Open" with "4" argument(s): "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

Any help?

If I had to take a stab at it I would imagine it is because you are passing an array from $sql. Does changing this line help? $SQL = get-content -Raw $QCChecksLocation — Matt
– Matt, Commented Feb 4, 2016 at 0:56
YUP! That did it. Thank you, Matt! If you wanna post that as an answer, I'll mark it solved (sorry, not sure how the gamification works to your advantage here). — Jesse Hofmann
– Jesse Hofmann, Commented Feb 4, 2016 at 16:04

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Looking at what I hope is the correct reference for ADODB.Recordset Open Method I had a suspicion that it didn't like arrays.

Source

Optional. A Variant that evaluates to a valid Command object, an SQL statement, a table name, a stored procedure call, a URL, or the name of a file or Stream object containing a persistently stored Recordset.

It says variant but I am sure that it does not want arrays. Get-Content will return a string array for a multiline file. Clean written SQL statements use more than one line so it wasn't a stretch to expect your was.

If you have at least PowerShell v3.0 then you can use the -Raw switch of Get-Content to return the file as a single string. With v2.0 you can just pipe into Out-String

$SQL = Get-Content -Raw $QCChecksLocation
$SQL = Get-Content $QCChecksLocation | Out-String

Both of the above commands should result with the same $SQL. That should work in your Open method now.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Feb 4, 2016 at 16:24

Matt

47k9 gold badges90 silver badges125 bronze badges

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

Collectives™ on Stack Overflow

Executing large sql file against a database using powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related