locked
using PowerShell Functions in t-SQL scripts RRS feed

  • Question

  • experts,

    Can I use a power shell function that gets file size given file path by cross applying it to a table in SQL Server that holds file path of files?

    fileID    filepath
    1         L:\DemoWebPages\About.cshtml.txt
    2         L:\DemoWebPages\default.cshtml.txt.txt
    3         L:\DemoWebPages\Layout.cshtml.txt
    4         L:\DemoWebPages\Site.css.txt

    if the above if the table in SQL Server, can I cross apply a power shell function that gets the file size when file path is passed from the table above?

    Help Much Appreciated!


    ebro

    Tuesday, April 15, 2014 7:36 PM

Answers

  • Make sure that you are running on the machine where you've full permission to access file and 

    Sample data

    create table filelist
    (
    fileid int,
    path varchar(200))
    
    insert into filelist values(1,'c:\Demystifying tempdb whitepaper.pdf')
    insert into filelist values(2,'C:\Appleton Papers - 23 SQL DM - dani 6.28.13.pdf')
    Powershell Scripts:-

    Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction silentlycontinue
    Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction silentlycontinue
    
    Function filesize
    {
    param([String] $path)
    try {
    if(Test-Path $path)
    {
    $size=(Get-Item $path).length/1024 
    write-host "$path size is $size KB"
    }
    }
    catch [System.Exception] {
    write-host "File not found"
    }
    }
    
    
    $params = @{server='hqvd0026\kat';database='master'}
    $Srv = invoke-sqlcmd @params  -Query "SELECT * from dbo.filelist"
    $srv
    foreach ($db in $Srv)
    {
    filesize -path $db.path
    }

    • Marked as answer by ebrolove Tuesday, April 15, 2014 10:02 PM
    Tuesday, April 15, 2014 8:55 PM

All replies

  • There are many ways to skin a cat!

    1- Here is one solution.  

    The SQL Server Agent allows you to schedule a PS snippet.

    http://technet.microsoft.com/en-us/library/cc280490.aspx

    Load a staging table will the information that you need.  

    Write a TSQL query to use the PS information in a SELECT query of your choosing.

    2 - Call a PS program from xp_cmdshell.  Most DBA's will frown on this because of the security risk.

    However a proxy account can be used to reduce the surface area caused by this opening.

    http://technet.microsoft.com/en-us/library/ms190359.aspx

    • Edited by John Miner Tuesday, April 15, 2014 8:11 PM
    Tuesday, April 15, 2014 8:07 PM
  • so, how will the connection between the values in the file path column of the table will be passed to the function written in power shell?

    As a step I can have a power shell script in my job. But how is the link made between the values in file path column and the psFunction?

    For instance,  MyPSfunction(filePathObtainedfromcolumn)

    -----


    ebro

    Tuesday, April 15, 2014 8:34 PM
  • Make sure that you are running on the machine where you've full permission to access file and 

    Sample data

    create table filelist
    (
    fileid int,
    path varchar(200))
    
    insert into filelist values(1,'c:\Demystifying tempdb whitepaper.pdf')
    insert into filelist values(2,'C:\Appleton Papers - 23 SQL DM - dani 6.28.13.pdf')
    Powershell Scripts:-

    Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction silentlycontinue
    Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction silentlycontinue
    
    Function filesize
    {
    param([String] $path)
    try {
    if(Test-Path $path)
    {
    $size=(Get-Item $path).length/1024 
    write-host "$path size is $size KB"
    }
    }
    catch [System.Exception] {
    write-host "File not found"
    }
    }
    
    
    $params = @{server='hqvd0026\kat';database='master'}
    $Srv = invoke-sqlcmd @params  -Query "SELECT * from dbo.filelist"
    $srv
    foreach ($db in $Srv)
    {
    filesize -path $db.path
    }

    • Marked as answer by ebrolove Tuesday, April 15, 2014 10:02 PM
    Tuesday, April 15, 2014 8:55 PM
  • excellent excellent work....

    but the problem is my table is over 5 million rows!!

    do you think foreach will not kill me?


    ebro

    Tuesday, April 15, 2014 9:12 PM
  • Yes. It will kill the performance.

    You can include where condition in -Query part and run Part by Part.

    --Prashanth

    Tuesday, April 15, 2014 9:53 PM
  • thanks so much Sir!

    Is there any way I can also select the fileID, and filepath, apart from the file size?


    ebro

    Tuesday, April 15, 2014 10:03 PM