Solved

Powershell Error with invoke-sqlcmd

Posted on 2013-05-17
10
1,504 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
 
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 68

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 68

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 Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
A brief introduction to what I consider to be the best editor for PowerShell.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now