SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Inserting into table with Powershell


Inserting into table with Powershell

Author
Message
Del Lee
Del Lee
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1337
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
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30333 Visits: 8986
I use SQLPSx sqlpsx.codeplex.com , just for convenience :-D


[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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
schleep
schleep
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2775 Visits: 1406
What do you see in SQL Profiler?



ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30333 Visits: 8986
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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
MG-148046
MG-148046
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3769 Visits: 2828
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.
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30333 Visits: 8986
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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
schleep
schleep
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2775 Visits: 1406
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...;-)



Del Lee
Del Lee
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1337
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
ALZDBA
ALZDBA
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30333 Visits: 8986
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


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


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


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Del Lee
Del Lee
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 1337
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search