November 19, 2012 at 10:08 am
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 {
}
December 5, 2012 at 8:12 am
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
December 6, 2012 at 5:29 am
What do you see in SQL Profiler?
December 6, 2012 at 5:52 am
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
December 6, 2012 at 6:51 am
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.
December 6, 2012 at 7:03 am
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
December 6, 2012 at 7:20 am
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...;-)
December 6, 2012 at 7:26 am
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
}
}
}
}
December 6, 2012 at 7:55 am
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
December 6, 2012 at 8:22 am
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?
December 6, 2012 at 8:43 am
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
December 6, 2012 at 8:48 am
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.
December 6, 2012 at 9:39 am
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
December 6, 2012 at 10:09 am
Ah, I see. You had a user input screen that ulimately called a powershell script. I'm with ya now.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply