Solved

Powershell and SQL 2012

Posted on 2013-06-12
5
1,110 Views
Last Modified: 2013-06-12
I have the script below. Which does exactly what its supposed to do in 2008. Place the name of server and it will extract all packages to a temp folder.


MY issue is that it wont work on a 2012 server. Why and how can i Fix

Here is the error
Invoke-Sqlcmd : A network-related or instance-specific error occurred while establishing a conne
ction to SQL Server. The server was not found or was not accessible. Verify that the instance na
me is correct and that SQL Server is configured to allow remote connections. (provider: Named Pi
pes Provider, error: 40 - Could not open a connection to SQL Server)
At C:\Users\ltorres\Dropbox\Documents\Powershell\SSIS_PackageExtract.ps1:8 char:27
+ $Packages =  Invoke-Sqlcmd <<<<  -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "
WITH cte AS (
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
    + FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScr
   iptCommand

Invoke-Sqlcmd :
At C:\Users\ltorres\Dropbox\Documents\Powershell\SSIS_PackageExtract.ps1:8 char:27
+ $Packages =  Invoke-Sqlcmd <<<<  -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "
WITH cte AS (
    + CategoryInfo          : ParserError: (:) [Invoke-Sqlcmd], ParserException
    + FullyQualifiedErrorId : ExecutionFailureException,Microsoft.SqlServer.Management.PowerShe
   ll.GetScriptCommand

Open in new window


Here is the code
Param($SQLInstance = "PCPRS01")

#####Add all the SQL goodies (including Invoke-Sqlcmd)#####
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls 

$Packages =  Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "WITH cte AS (
                                                                        SELECT    cast(foldername as varchar(max)) as folderpath, folderid
                                                                        FROM    msdb..sysssispackagefolders
                                                                        WHERE    parentfolderid = '00000000-0000-0000-0000-000000000000'
                                                                        Union
                                                                        Select '\',folderid FROM msdb..sysssispackages dts
                                                                        Where folderid = '00000000-0000-0000-0000-000000000000'
                                                                        
                                                                        UNION    ALL
                                                                        SELECT    cast(c.folderpath + '\' + f.foldername  as varchar(max)), f.folderid
                                                                        FROM    msdb..sysssispackagefolders f
                                                                        INNER    JOIN cte c        ON    c.folderid = f.parentfolderid
                                                                    )
                                                                    SELECT    c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
                                                                    FROM    cte c
                                                                    INNER    JOIN msdb..sysssispackages p    ON    c.folderid = p.folderid
                                                                    WHERE    c.folderpath NOT LIKE 'Data Collector%'"

Foreach ($pkg in $Packages)
{
    $pkgName = $Pkg.name
    $folderPath = $Pkg.folderpath
    $fullfolderPath = "c:\temp\$SQLInstance\$folderPath\"
    if(!(test-path -path $fullfolderPath))
    {
        mkdir $fullfolderPath | Out-Null
    }
    $pkg.pkg | Out-File -Force -encoding ascii -FilePath "$fullfolderPath\$pkgName.dtsx"
}

Open in new window

0
Comment
Question by:Leo Torres
  • 3
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
sedgwick earned 500 total points
Comment Utility
do u get any errors?
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Hey sed sup... was looking fo ya on that other question for powershell and schedule..

I figured this out.. it was wrong name i had to enter the instance complete name server\Instance..
0
 
LVL 8

Author Closing Comment

by:Leo Torres
Comment Utility
Thank for the help!!
0
 
LVL 42

Expert Comment

by:sedgwick
Comment Utility
sorry i missed it, cheers.
0
 
LVL 8

Author Comment

by:Leo Torres
Comment Utility
Do you want me to open.. Powershell question again.. You have already have the logic in place I just need a new page..
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Set OWA language and time zone in Exchange for individuals, all users or per database.
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

744 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

17 Experts available now in Live!

Get 1:1 Help Now