?
Solved

Powershell Error with invoke-sqlcmd

Posted on 2013-05-17
10
Medium Priority
?
1,862 Views
Last Modified: 2013-05-22
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

0
Comment
Question by:dbbishop
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39176201
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

0
 
LVL 15

Author Comment

by:dbbishop
ID: 39177223
Same error.
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39177370
Is the $target variable a database smo object or just a string ?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39177398
Invoke-sqlcmd -serverInstance $FQDN -Database $TARGET -Query ("EXEC sp_addextendedproperty @name = 'CreateDate', @value = "GETDATE()" ") -QueryTimeout 65535 -ConnectionTimeout 65535 -verbose -Variable $ARGRAY
0
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39177408
Try:
Invoke-sqlcmd -serverInstance $FQDN -Database $TARGET -Query ("EXEC sp_addextendedproperty @name = N'CreateDate', @value = 'GETDATE()';") -QueryTimeout 65535 -ConnectionTimeout 65535 -verbose -Variable $ARGRAY
0
 
LVL 71

Accepted Solution

by:
Qlemo earned 1500 total points
ID: 39177410
You can't provide an expression for @value of an extended property, only string literals. If you replace the double quotes around GETDATE in http:#a39177398 by single quotes, that string will be stored - not very useful, however.

What are you after? The creation date of the DB?
0
 
LVL 15

Author Comment

by:dbbishop
ID: 39177834
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.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 39177839
I solved this be creating a $now valrable in PoSH and passing that to the query.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39177866
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.
0
 
LVL 15

Author Closing Comment

by:dbbishop
ID: 39188591
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.
0

Featured Post

A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question