Invoke-Sqlcmd : Invalid object name 'Inventar'

0
votes

So, i am tryingt to run a SQL-Query via Powershell with the Invoke-Sqlcmd-CMDLet. Pretty straight Forward:

Invoke-Sqlcmd -Query $Query -ServerInstance $DBServer -Database $DBName -Username $DBUser -Password $DBPass

($Query is "Select * FROM Inventar;")

Now im getting an Error :

Invoke-Sqlcmd : Invalid object name 'Inventar'. At line:1 char:1 + Invoke-Sqlcmd -Query $Query -ServerInstance $DBServer -Database $DBName -Usernam ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

ANYWAYS, i was able to run the Command with the following Query:

SELECT * FROM INFORMATION_SCHEMA.TABLES;

And it listed me all the Tables from the Database.

From there i've got the Table "Inventar", Kind of the error from This Question:

Azure SQL: Invalid Object Name using Powershell's "Invoke-sqlcmd" on Adventureworks

Except for that i already have the -Database attribute.

Maybe someone can help me out?

1
sqlpowershell
You probably need to qualify the schema where the table resides: schema.Inventar – HoneyBadger
I think the Schema contains of Domain\username, as it is shown like this under TABLE_SCHEMA, but when i set this in front of the Table (****** \ ******.Inventar) it wont accept the "\", so i tried it with the Username only and User@Domain, but this also wont work. – GenesisIT
And if you use [****** \ ******].[Inventar]? Mind you, its a weird schema name that may need to be fixed. – HoneyBadger
You are absolutely right, it is dumb. Good that i wasn't the One who set it up :-) I've set the Variable to 'Select * From "Domain\User".Inventar', that worked.. Thanks! – GenesisIT
Can i set your Comment as answer anywhere..? – GenesisIT

1 Answers

0
votes

If you do something like Select * FROM Inventar sql server will assume the table is in the dbo schema. If it isn't, you'll get an error like you're getting. The solution is to qualify the schema: (database).schema.table. Your table is located in a schema named domain\user. Since a backslash is not allowed in an object name, you need to escape it. You can do that by surrounding the schema in [] or "":

[****** \ ******].[Inventar] or

"****** \ ******"."Inventar".