Solved

Powershell Error with invoke-sqlcmd

Posted on 2013-05-17
10
1,542 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 18

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 18

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 69

Accepted Solution

by:
Qlemo earned 500 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 69

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

A brief introduction to what I consider to be the best editor for PowerShell.
The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

809 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