Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Inserting into table with Powershell Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 10:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 239, Visits: 369
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
Post #1386480
Posted Wednesday, December 5, 2012 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:47 AM
Points: 7,005, Visits: 8,451
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #1393037
Posted Thursday, December 6, 2012 5:29 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 428, Visits: 946
What do you see in SQL Profiler?


Post #1393456
Posted Thursday, December 6, 2012 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:47 AM
Points: 7,005, Visits: 8,451
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #1393467
Posted Thursday, December 6, 2012 6:51 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 1,805, Visits: 2,183
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.

Post #1393491
Posted Thursday, December 6, 2012 7:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:47 AM
Points: 7,005, Visits: 8,451
Looking at your code a bit more in detail ...

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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #1393501
Posted Thursday, December 6, 2012 7:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 428, Visits: 946
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...



Post #1393517
Posted Thursday, December 6, 2012 7:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 239, Visits: 369
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
Post #1393522
Posted Thursday, December 6, 2012 7:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 12:47 AM
Points: 7,005, Visits: 8,451
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 !


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

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


- 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
Post #1393535
Posted Thursday, December 6, 2012 8:22 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 239, Visits: 369
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
Post #1393556
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse