Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Powershell and SQL 2012

Posted on 2013-06-12
5
Medium Priority
?
1,249 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
Meir Rivkin earned 2000 total points
ID: 39241141
do u get any errors?
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39241322
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
ID: 39241324
Thank for the help!!
0
 
LVL 42

Expert Comment

by:Meir Rivkin
ID: 39241345
sorry i missed it, cheers.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39241706
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn the basics of modules and packages in Python. Every Python file is a module, ending in the suffix: .py: Modules are a collection of functions and variables.: Packages are a collection of modules.: Module functions and variables are accessed us…
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…
Suggested Courses

596 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