Link to home
Start Free TrialLog in
Avatar of D B
D BFlag for United States of America

asked on

Powershell Error with invoke-sqlcmd

why does this fail?
Invoke-sqlcmd -serverInstance $FQDN -Database $TARGET -Query ("EXEC sp_addextendedproperty @name = N'CreateDate', @value = GETDATE();") -QueryTimeout 65535 -ConnectionTimeout 65535 -verbose -Variable $ARGRAY 

Open in new window

It gives me the following error:
Invoke-sqlcmd : Incorrect syntax near ')'.
At line:57 char:1
+ Invoke-sqlcmd -serverInstance $FQDN -Database $TARGET -Query ("EXEC sp_addextend ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Open in new window

Avatar of didnthaveaname
didnthaveaname

Try:

Invoke-sqlcmd -serverInstance $FQDN -Database $TARGET -Query "EXEC sp_addextendedproperty @name = N'CreateDate', @value = GETDATE();" -QueryTimeout 65535 -ConnectionTimeout 65535 -verbose -Variable $ARGRAY 

Open in new window

Avatar of D B

ASKER

Same error.
Is the $target variable a database smo object or just a string ?
Avatar of Raheman M. Abdul
Invoke-sqlcmd -serverInstance $FQDN -Database $TARGET -Query ("EXEC sp_addextendedproperty @name = 'CreateDate', @value = "GETDATE()" ") -QueryTimeout 65535 -ConnectionTimeout 65535 -verbose -Variable $ARGRAY
Try:
Invoke-sqlcmd -serverInstance $FQDN -Database $TARGET -Query ("EXEC sp_addextendedproperty @name = N'CreateDate', @value = 'GETDATE()';") -QueryTimeout 65535 -ConnectionTimeout 65535 -verbose -Variable $ARGRAY
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D B

ASKER

Qlemo: Yes. Also, I tried this in SSMS and it works (the query part), using GETDATE() and it works. The timestamp get stored as the text of the property. I've also tried @value = CAST(GETDATE() AS VARCHAR(20)) but get the same error.

I may be incorrect, but if I recall from memory, @value is actually a SQL_VARIANT data type.
sp_addextendedproperty is not an extended procedure, and if you cannot pass an expression to a stored procedure, I've got to go back and rewrite several hundred procs.
Avatar of D B

ASKER

I solved this be creating a $now valrable in PoSH and passing that to the query.
Don't know why it works in SSMS - I suppose it is doing something tricky, like using an intermediate var like you did in PS.
And yes, @value is sql_variant, but nevertheless you cannot provide an expression, only a literal.
Avatar of D B

ASKER

Although this solution did not directly solve the problem, it got me to thinking, and I resolved by creating a datetime stamp in PoSH, and passing it as a quoted variable (e.g. @value = '$now').

FYI, Contrary to my indication that an expression can be passed, you were correct in your statement that it cannot. Not sure what I had done in SSMS, but further testing indicated you were correct.