Inserting into table with Powershell

  • I have a script that I'm adapting from something I found on the internet where I'm getting database size information and insert that into a table I've created. I've been working with PS for a couple of months, but I'm still a bit green. Below is the section of relevant code - I looked at this Don Jones article here to get an idea how to do this. I'm not sure what the -f is for and he doesn't address it in the article. In any case, the variables have the right data in them at this point in the script (verified with the debugger) but the INSERT command is not working. That is, no data is in the table after the script is completed. No error is thrown, so I'm wanting to use Try...Catch to find out what the problem is. Does anyone have a recommendation as to what to put in the catch in this case? The info I've found on the internet is pretty generic and I don't know what kind of exception to interrogate for a SQL Server command.

    $cmd.commandtext = "INSERT INTO dbo.db_sizes (Server, DatabaseName, DataFileName, DataSize, DataUsedSpace) VALUES('{0}','{1}','{2}','{3}', '{4})" -f

    $dbname, $mdfInfo.Name, $mdfInfo.FileName, ($mdfInfo.size / 1000), ($mdfInfo.UsedSpace / 1000)

    Try {

    $cmd.executenonquery()

    }

    catch {

    }



    Del Lee

  • I use SQLPSx sqlpsx.codeplex.com , just for convenience 😀

    [HashTable]$SprocParams = @{}

    foreach ( $c in $RecordFields ) {

    $SprocParams.Add($c.Name , [string]$CurrentRow.$($c.Name) )

    }

    $rc = Invoke-StoredProcedure -connection "Server=$TargetSQLServer;Database=$SQLDb;Trusted_Connection=True;Connect Timeout=5;Application Name=$Global:ApplicationName;Workstation Id=$env:COMPUTERNAME;" -timeout 5 -storedProcName 'myschema.mysproc' -parameters $SprocParams

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • What do you see in SQL Profiler?

  • of course - to get started - you can always use this catch block

    catch {

    write-host 'Catch block' -BackgroundColor Yellow -ForegroundColor Black

    Write-Host $_.Exception.message -BackgroundColor Red -ForegroundColor Black

    }

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I can address the -f operator. It is used to format a string. You can specify how to format a series of numeric or alphanumeric values.On the left side of the operator you specify the formatting and on the right is the comma-separated value list.

    In your example the formatting instruction (left side of -f) is the syntax that includes {0}, {1}, etc. Each of these will be replaced by the values on the right side of the -f operator - $variable1, $variable2, etc.

    As for why there is not data, using ALZDBAs' Write-Host syntax should help.

    Hope this helps you and good luck with Powershell. It is an amazing tool!

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • Looking at your code a bit more in detail ... :blush:

    Why doesn't it hit a syntax error ??

    IMO for your code to work it should be something like this :

    $cmd.commandtext = $("INSERT INTO dbo.db_sizes (Server, DatabaseName, DataFileName, DataSize, DataUsedSpace)

    VALUES('{0}','{1}','{2}','{3}', '{4})"

    -f $dbname, $mdfInfo.Name, $mdfInfo.FileName, ($mdfInfo.size / 1000), ($mdfInfo.UsedSpace / 1000)

    )

    (added line breaks to make the code a bit more readable in this thread )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Missing quote after '{4}), should be '{4}')

    Server = $dbname

    DatabaseName = $mdfInfo.Name

    DataFileName = $mdfInfo.FileName

    DataSize = ($mdfInfo.size / 1000)

    DataUsedSpace = ($mdfInfo.UsedSpace / 1000)

    The first two look somewhat suspicious to me...;-)

  • Thanks for all the replies. I got past my original question with the following (can't remember where I found it):

    catch [System.Exception] {

    Write-Host $_.Exception.Message

    }

    Once I got that, it helped me solve the other issues I was having and it works now. Here is part of the code as it is now:

    foreach ($db in $dbs)

    {

    $dbname = $db.Name

    $fileGroups = $db.FileGroups

    ForEach ($fg in $fileGroups)

    {

    If ($fg)

    {

    $mdfInfo = $fg.Files | Select Name, FileName, size, UsedSpace

    $logInfo = $db.LogFiles | Select Name, FileName, Size, UsedSpace

    $date = get-date

    $cmd.commandtext = "INSERT INTO dbo.db_sizes (Server, DatabaseName, DataFileName, DataSize, DataUsedSpace, LogName, LogFileName, LogSize, LogUsedSpace, SDate) VALUES('{0}','{1}','{2}','{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}')" -f

    $instance, $mdfInfo.Name, $mdfInfo.FileName, ($mdfInfo.size / 1000), ($mdfInfo.UsedSpace / 1000), $logInfo.Name, $logInfo.FileName, ($logInfo.size / 1000), ($logInfo.UsedSpace / 1000), $date

    Try {

    $cmd.executenonquery()

    }

    catch [System.Exception] {

    Write-Host $_.Exception.Message

    }

    }

    }

    }



    Del Lee

  • Del Lee (12/6/2012)


    Thanks for all the replies. ..

    Thank you for the feedback !

    Keep in mind you will - sooner or later - hit localization problems because you convert stuff to its string format.

    e.g. $date = get-date

    To what format is it being translated in your $cmd.commandtext ?

    Well, you aren't formatting it yourself, so the result is depedent on the local user settings of the computer your script is being run on !

    There is a simple cure: Take control!

    $date = get-date -format 'yyyy-MM-dd HH:mm.ss.fff'

    Same goes with decimal point vs comma

    There is also a solution where you build your query as a parameterized query and pass the values to be stored as parameters. This way you also avoid SQLInjection issues ! :w00t:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Gotcha on the date formatting. I've implemented that, thanks.

    Not sure why we are concerned about SQL Injection on this, though. I'm doing a straight INSERT. Care to elaborate?



    Del Lee

  • Del Lee (12/6/2012)


    Gotcha on the date formatting. I've implemented that, thanks.

    Not sure why we are concerned about SQL Injection on this, though. I'm doing a straight INSERT. Care to elaborate?

    I just always try to avoid any issues SQL Injection related.

    Mainly to avoid copy/paste behaviour issues in the spirit of "That's how our DBA did it, so it must be OK" :crazy:

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • I just always try to avoid any issues SQL Injection related.

    Mainly to avoid copy/paste behaviour issues in the spirit of "That's how our DBA did it, so it must be OK" :crazy:

    I'm certainly interested in preventing SQL Injection, but I'm not really following how there is a danger of SQL Injection from doing a straight INSERT from within a powershell script.



    Del Lee

  • Guess what was the first script my win-admin produced using powershell: A nice GUI :hehe:

    A wpf window requesting user input.

    Just like I did, he started off with "copy/paste"-ing scripts to assemble the stuff he needed.

    That's one of the reasons I try to keep SQL Injection on top of my focus when producing scripts.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Ah, I see. You had a user input screen that ulimately called a powershell script. I'm with ya now.



    Del Lee

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply