November 21, 2015 at 3:07 am
I have this powershell script as step 1 in a sql job.
[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
It creates a text extract with 4 columns as below.
ServerName InstanceName IsClustered Version
---------- ------------ ----------- -------
ABCDEFGHI No 10.0.4000.0
ABCDEFGHI MSSQLSERVER2012 No 11.0.3000.0
SFVSDVSDV No 11.0.5058.0
HDDBHDGHH No 10.50.1600.1
I have this powershell script as step 2 in a sql job which is failing to an error stating sysntax issues.
$SQL = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | ' foreach {
"INSERT INTO dbo.FoundSQLServers VALUES ('$($_.ServerName)', '$($_.InstanceName)', '$($_.IsClustered)', '$($_.Version)')" ' >> E:\ServersList.txt
}
Error message:
Date11/21/2015 1:52:58 AM
LogJob History (fgdfb)
Step ID2
Serverfffffffff
Job Nameffffff
Step Nameffff
Duration00:00:00
Message
Unable to start execution of step 2 (reason: line(2): Syntax error). The step failed.
The above script i supposed to extract data from E:\ServersList.txt and insert into this SQL table
CREATE TABLE [dbo].[FoundSQLServers](
[ServerName] [varchar](128) NULL,
[InstanceName] [varchar](128) NULL,
[IsClustered] [varchar](3) NULL,
[VersionNumber] [varchar](64) NULL
)
Can someone please help figure this.
Thanks
November 21, 2015 at 1:04 pm
How does the code fail from the PowerShell command line?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
November 21, 2015 at 4:14 pm
sqlnewbie17 (11/21/2015)
I have this powershell script as step 1 in a sql job.[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
It creates a text extract with 4 columns as below.
ServerName InstanceName IsClustered Version
---------- ------------ ----------- -------
ABCDEFGHI No 10.0.4000.0
ABCDEFGHI MSSQLSERVER2012 No 11.0.3000.0
SFVSDVSDV No 11.0.5058.0
HDDBHDGHH No 10.50.1600.1
I have this powershell script as step 2 in a sql job which is failing to an error stating sysntax issues.
$SQL = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | ' foreach {
"INSERT INTO dbo.FoundSQLServers VALUES ('$($_.ServerName)', '$($_.InstanceName)', '$($_.IsClustered)', '$($_.Version)')" ' >> E:\ServersList.txt
}
The code I see you are using is never going to run because of syntax errors. The script you have in step 2 shows there is a single quote after that pipeline: "...GetDataSources() | ' foreach {". This is where the syntax error is coming from as it sees that as an expression, which is not allowed as the first command in a pipeline.
I am assuming you got the code from Aaron Nelson's post: http://sqlvariant.com/2010/09/finding-sql-servers-with-powershell/%5B/url%5D. The syntax error you are getting is likely from copy/paste. The single quote you have should actually be a back-tic [ ` ]. You should copy/paste code from web sites into notepad or something prior to pasting it into a SQL Agent job or other script to ensure special characters such as this do not get modified.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
November 22, 2015 at 3:35 am
Shawn Melton (11/21/2015)
...You should copy/paste code from web sites into notepad or something prior to pasting it into a SQL Agent job or other script to ensure special characters such as this do not get modified.
Excellent advice. Also it demonstrates that you haven't checked the code before running it all from a SQL Agent job. This is a dangerous practice. Are you certain what it does? Have you tested the code first? (This is why I previously tried to lead you towards running it on the command line.)
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy