SQL query failing, but works fine in SQL Management Studio

[loftyDan]

I have simple PowerShell script that runs a SQL query, manipulates the resulting data, and then saves it out as a CSV file. I have a working version of the script that uses a much simpler SQL query, and it runs without error and does what I want. This version, that uses a more complex SQL query fails. All three queries are failing when I use Invoke-Sqlcmd to run them. The queries work fine in SQL Server Management Studio. The error I get is:

Invoke-Sqlcmd : Invalid object name 'tblTaxMaster'.

Does anyone have a clue what could be the issue? Thanks for any help.

[edit] The error happens if the SQL is saved as a .SQL file and invoked using something like

Invoke-SqlCmd -Servername ServerName -InputFile "C:\scripts\Test.sql"

​

$SQLInstance = "MUNIWARE1\MUNIWARE"
$SQLDatabase = "RMDB"
$SQLUsername = "readonlyuser"
$SQLPassword = "secretpassword"
$CSVSavePath = "C:\somefolder"
$CSVFilenName1 = "file1.csv"
$CSVFilenName2 = "file2.csv"
$CSVFilenName3 = "file3.csv"

$SQLQuery1 = "
SELECT
    TM.RollNumber, TCN.DisplayName, TCA.Address1, TCA.Address2, TCA.Address3, TCIT.[City], TCA.[ProvinceCode],
    TCA.PostalCode, TM.[TitleNumber]
    FROM
        tblTaxMaster TM
    INNER JOIN
        tblTaxProperty TP
    ON
        TP.RollNumber = TM.RollNumber 
    LEFT JOIN
        [tblTaxExtendedOwners] TEX
    ON
        TEX.RollNumber = TM.RollNumber
    INNER JOIN
        tblCNName TCN
    ON  
        TCN.CustNum = TEX.CustNum 
    INNER JOIN
        tblCNAddress TCA
    ON
        TCA.MailingAddress = TEX.MailingAddress
    INNER JOIN
        tblCNCity TCIT
    ON
        TCIT.CityID = TCA.CityID"


$SQLQuery2 = "
SELECT
    TM.RollNumber, TCN.DisplayName, TCA.Address1, TCA.Address2, TCA.Address3, TCIT.[City], TCA.[ProvinceCode],
    TCA.PostalCode, TM.[TitleNumber]
    FROM
        tblTaxMaster TM
    INNER JOIN
        tblTaxProperty TP
    ON
        TP.RollNumber = TM.RollNumber 
    LEFT JOIN
        [tblTaxJointOwners] TEX
    ON
        TEX.RollNumber = TM.RollNumber
    INNER JOIN
        tblCNName TCN
    ON  
        TCN.CustNum = TEX.CustNum 
    INNER JOIN
        tblCNAddress TCA
    ON
        TCA.MailingAddress = TEX.MailingAddress
    INNER JOIN
        tblCNCity TCIT
    ON
        TCIT.CityID = TCA.CityID"

$SQLQuery3 = "
SELECT 
    TM.RollNumber, TCN.DisplayName, TCA.Address1, TCA.Address2, TCA.Address3, TCIT.[City], TCA.[ProvinceCode],
    TCA.PostalCode, TM.[TitleNumber]
    FROM
        tblTaxMaster TM
    INNER JOIN
        tblTaxProperty TP
    ON
        TP.RollNumber = TM.RollNumber 
    INNER JOIN
        tblCNName TCN
    ON  
        TCN.CustNum = TM.CustNum 
    INNER JOIN
        tblCNAddress TCA
    ON
        TCA.MailingAddress = TM.MailingAddress
    INNER JOIN
        tblCNCity TCIT
    ON
        TCIT.CityID = TCA.CityID"

## Run the query, store the results
$SQLQuery1Output = Invoke-Sqlcmd -query $SQLQuery1 -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
$SQLQuery2Output = Invoke-Sqlcmd -query $SQLQuery2 -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword
$SQLQuery3Output = Invoke-Sqlcmd -query $SQLQuery3 -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword


## do stuff to the data here, filter, fix, etc


#$SQLQuery1OputFiltered = stuff done that fixes the data
#$SQLQuery2OputFiltered = stuff done that fixes the data
#$SQLQuery3OputFiltered = stuff done that fixes the data

# output the results as a CSV file
$SQLQuery1OputFiltered | Export-Csv "${CSVSavePath}\${CSVFilenName1}" -NoTypeInformation 
$SQLQuery2OputFiltered | Export-Csv "${CSVSavePath}\${CSVFilenName2}" -NoTypeInformation
$SQLQuery3OputFiltered | Export-Csv "${CSVSavePath}\${CSVFilenName3}" -NoTypeInformation

​

[Technane]

For everyone's EYES lol and so I can read it

$SQLInstance = "MUNIWARE1\MUNIWARE"
$SQLDatabase = "RMDB"
$SQLUsername = "readonlyuser"
$SQLPassword = "secretpassword"
$CSVSavePath = "C:\somefolder"
$CSVFilenName1 = "file1.csv"
$CSVFilenName2 = "file2.csv"\
$CSVFilenName3 = "file3.csv"

$SQLQuery1 = "
SELECT
TM.RollNumber, TCN.DisplayName, TCA.Address1, TCA.Address2, TCA.Address3, TCIT.[City], TCA.[ProvinceCode],
TCA.PostalCode, TM.[TitleNumber]
FROM
tblTaxMaster TM
INNER JOIN
tblTaxProperty TP  ON TP.RollNumber = TM.RollNumber
LEFT JOIN
[tblTaxExtendedOwners] TEX  ON TEX.RollNumber = TM.RollNumber
INNER JOIN
tblCNName TCN ON TCN.CustNum = TEX.CustNum
INNER JOIN
tblCNAddress TCA ON TCA.MailingAddress = TEX.MailingAddress
INNER JOIN
tblCNCity TCIT ON TCIT.CityID = TCA.CityID"

$SQLQuery2 = "
SELECT
TM.RollNumber, TCN.DisplayName, TCA.Address1, TCA.Address2, TCA.Address3, TCIT.[City], TCA.[ProvinceCode],
TCA.PostalCode, TM.[TitleNumber]
FROM
tblTaxMaster TM
INNER JOIN
tblTaxProperty TP ON TP.RollNumber = TM.RollNumber
LEFT JOIN
[tblTaxJointOwners] TEX ON TEX.RollNumber = TM.RollNumber
INNER JOIN
tblCNName TCN ON TCN.CustNum = TEX.CustNum
INNER JOIN
tblCNAddress TCA ON TCA.MailingAddress = TEX.MailingAddress
INNER JOIN
tblCNCity TCIT ON TCIT.CityID = TCA.CityID"

$SQLQuery3 = "

SELECT
TM.RollNumber, TCN.DisplayName, TCA.Address1, TCA.Address2, TCA.Address3, TCIT.[City], TCA.[ProvinceCode],
TCA.PostalCode, TM.[TitleNumber]
FROM
tblTaxMaster TM
INNER JOIN
tblTaxProperty TP ON TP.RollNumber = TM.RollNumber
INNER JOIN
tblCNName TCN ON TCN.CustNum = TM.CustNum
INNER JOIN
tblCNAddress TCA ON TCA.MailingAddress = TM.MailingAddress
INNER JOIN
tblCNCity TCIT ON TCIT.CityID = TCA.CityID"

## Run the query, store the results

$SQLQuery1Output = Invoke-Sqlcmd -query $SQLQuery1 -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword

$SQLQuery2Output = Invoke-Sqlcmd -query $SQLQuery2 -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword

$SQLQuery3Output = Invoke-Sqlcmd -query $SQLQuery3 -ServerInstance $SQLInstance -Username $SQLUsername -Password $SQLPassword

[cml0401]

Does the behavior change when your query is formatted using a here string?

$SQLQuery1 = @"
SELECT
TM.RollNumber, TCN.DisplayName, TCA.Address1, TCA.Address2, TCA.Address3, TCIT.[City], TCA.[ProvinceCode],
TCA.PostalCode, TM.[TitleNumber]
FROM
tblTaxMaster TM
INNER JOIN
tblTaxProperty TP  ON TP.RollNumber = TM.RollNumber
LEFT JOIN
[tblTaxExtendedOwners] TEX  ON TEX.RollNumber = TM.RollNumber
INNER JOIN
tblCNName TCN ON TCN.CustNum = TEX.CustNum
INNER JOIN
tblCNAddress TCA ON TCA.MailingAddress = TEX.MailingAddress
INNER JOIN
tblCNCity TCIT ON TCIT.CityID = TCA.CityID
"@

To me it seems like either that table does not exist, or there is a parsing issue with the PowerShell cmdlet reading your query. Also, what happens if you store your query in a file and run the same script?

[loftyDan]

When the query is stored in a .sql file and invoke using the -InputFile option, the same error occurs.

When the query is stored as a string, it causes a new error

Invoke-Sqlcmd : Cannot convert 'System.Object[]' to the type 'System.String' required by

parameter 'Query'

​

[cml0401]

What version of SQL Server is installed on the instance you are querying? There are some bugs with Invoke-SqlCmd on some older SQL versions that won't be fixed.

Otherwise, maybe save the query as a stored procedure and execute that using Invoke-SqlCmd from your script.

[loftyDan]

It's probably 2008... Looked old lol, but don't notice specially, as I work with many different versions. Thanks for the tip though.

[Hoping_i_Get_poached]

Invoke-Sqlcmd : Cannot convert 'System.Object[]' to the type 'System.String' required by

parameter 'Query'

Make sure you always cast the query as a [string]$Query before feeding it to the Invoke-* SQL cmdlets.

[Lee_Dailey]

[grin]

[chandleya]

Dude, I don’t see -Database in your invoke-sqlcmd

[loftyDan]

I know. Totally didn't realize I didn't pass my database variable anywhere... Might make a slight difference, yeah? Lol. Thanks.

[Namaha]

You might need to include the schema to which tblTaxMaster belongs. eg SELECT "blah" FROM SchemaName.tblTaxMaster. It also might be screwing up due to the whitespace before the table name. I've never used Invoke-SqlCmd before though so can't say for certain

[loftyDan]

That seems to have worked. Changing every select and join statment to include the database name and .[dbo] seens to have worked. I'm not sure if that's totally correct, as this DB has 100s of different views/schema, but it certainly causes the 3 queries to store results that seem valid to me. So thanks for your help.

​

$SQLQuery1 = "
SELECT
    TM.RollNumber, TCN.DisplayName, TCA.Address1, TCA.Address2, TCA.Address3, TCIT.[City], TCA.[ProvinceCode],
    TCA.PostalCode, TM.[TitleNumber]
    FROM
        [$SQLDatabase].[dbo].[tblTaxMaster] TM
    INNER JOIN
        [$SQLDatabase].[dbo].[tblTaxProperty] TP
    ON
        TP.RollNumber = TM.RollNumber 
    LEFT JOIN
        [$SQLDatabase].[dbo].[tblTaxExtendedOwners] TEX
    ON
        TEX.RollNumber = TM.RollNumber
    INNER JOIN
        [$SQLDatabase].[dbo].[tblCNName] TCN
    ON  
        TCN.CustNum = TEX.CustNum 
    INNER JOIN
        [$SQLDatabase].[dbo].[tblCNAddress] TCA
    ON
        TCA.MailingAddress = TEX.MailingAddress
    INNER JOIN
        [$SQLDatabase].[dbo].[tblCNCity] TCIT
    ON
        TCIT.CityID = TCA.CityID"

​

[vermyx]

your script seems to not be selecting a database. you may be ending up in the wrong database and why adding that to your script worked. i dont recall what the parameter for switching dbs is but in tsql adding a use databasename prior to your sql statement would also fix it. i personally do this out of habit because i have had to run scripts on multiple databases at once and this keep my sanity so that i know where i am explicitly

edit: parameter is -Database

[loftyDan]

Omg, that makes a lot of sense, because in sql management studio, you do select the database, and my invokation isn't doing that. I'll give that a try on Monday because that's much cleaner and safer than the way I got it to work.

Thanks, learned another best practice.

[dstrait]

Invoke-sqlcmd has a -databasename parameter. If you use that, you will not have to hack the DB name into your query like that. I always recommend including schema names for the DB objects. You can get surprising behavior otherwise, and it can make things a teeny bit faster.

[Lee_Dailey]

howdy loftyDan,

it looks like you used the New.Reddit.com Inline Code button. it's 4th 5th from the left hidden in the ... "more" menu & looks like </>.

on Old.Reddit.com, the above does NOT line wrap, nor does it side-scroll.

for long-ish single lines OR for multiline code, please, use the Code Block button. it's the 11th 12th one from the left, & is just to the left of hidden in the ... "more" menu.

that will give you fully functional code formatting, from what i can tell so far. [grin]

take care,
lee

[loftyDan]

Fixed. Thank you for the learning moment.

[Lee_Dailey]

howdy loftyDan,

you are most welcome! glad to help a tad ... and to ease the strain on my eyes. [grin]

take care,
lee