Solved

Powershell Error with invoke-sqlcmd

Posted on 2013-05-17
10
1,602 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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 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

Synchronize a new Active Directory domain with an existing Office 365 tenant
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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 antispam), the adminiā€¦

679 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