Execute .sql file through ps

[kokser]

I have been googling around since my last thread, and can't find any way to execute a sql file.

I'll try and explan what I want to do:

My script first exports information from my exchange server. It retrieves the mailboxes, items in them, the size of them, and the dn for each. All this is exported to a csv file.
The script then proceeds to convert the csv to an sql file with proper arguments.

What I need now, is to make the script execute this sql file into my database table.

Script:

Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin
$date = get-date -format d
$file = $date+".csv"
$output = get-mailbox | get-mailboxstatistics | where {$_.ObjectClass -eq "Mailbox"} | Select-Object @{name="Username";expression={$_.DisplayName}},@{name="Size";expression={$_.TotalItemSize.Value.ToKB()}}, @{name="Items";expression={$_.ItemCount}}, @{name="DN";expression={(get-mailbox $_.legacydn).distinguishedname}}, @{name="Dato";expression={$date}}
$output | export-csv -path $file -encoding ascii -notypeinformation

$tableName = "Brugere"

# Generate an informative header for the sql file.
# Note that the out-file command will overwrite any existing file. 
$output = $file.ToLower().TrimEnd(".csv") + ".sql"

# Loop through the rows in the csv file.
Import-Csv $file | % {
    # The insert variable is used to build a single insert statement 
    $insert = "INSERT INTO $tableName ("

    # We only care about the noteproperties, no use dealing with methods and the such. 
    $properties = $_ | Get-Member | where { $_.MemberType -eq "NoteProperty" }

    # Create a comma delimited string of all the property names to use in the insert statement. 
    # You should make sure that the column headings in the CSV file match the field names in  
    # your table before you run the script.
    $properties | % { $insert += $_.Name + ", " }
    $insert = $insert.TrimEnd(", ") + ") VALUES ("

    # Couldn't figure out how to access the value directly.  So here I'm forced to use
    # substring to get it.  The Definition looks like "System.String PropertyName=PropertyValue"
    # Since the value will be enclosed in single quotes, you will run into trouble if the value  
    # contains a single quote.  To escape the single quote in T-SQL, just put another single quote
    # directly in front of it.
    $properties | % { 
        $value = $_.Definition.SubString($_.Definition.IndexOf("=") + 1) 
        $insert += "'" + $value.Replace("'", "''") + "', "  
    } 
    $insert = $insert.TrimEnd(", ") + ")"

    # Append the insert statement to the end of the output file. 
    $insert | out-file -filepath $output -append
}

csv output

Username,Size,Items,DN,Dato
lb1,11498,7,"CN=lb1,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local",04-11-2009
lb2,5734,6,"CN=lb2,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local",04-11-2009
nb1,11454,7,"CN=nb1,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local",04-11-2009
nb2,5777,5,"CN=nb2,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local",04-11-2009
nbking,7,2,"CN=nbking,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local",04-11-2009
lbking,7,2,"CN=lbking,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local",04-11-2009

sql output

INSERT INTO Brugere (Dato, DN, Items, Size, Username) VALUES ('04-11-2009', 'CN=lb1,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local', '7', '11498', 'lb1')
INSERT INTO Brugere (Dato, DN, Items, Size, Username) VALUES ('04-11-2009', 'CN=lb2,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local', '6', '5734', 'lb2')
INSERT INTO Brugere (Dato, DN, Items, Size, Username) VALUES ('04-11-2009', 'CN=nb1,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local', '7', '11454', 'nb1')
INSERT INTO Brugere (Dato, DN, Items, Size, Username) VALUES ('04-11-2009', 'CN=nb2,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local', '5', '5777', 'nb2')
INSERT INTO Brugere (Dato, DN, Items, Size, Username) VALUES ('04-11-2009', 'CN=nbking,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local', '2', '7', 'nbking')
INSERT INTO Brugere (Dato, DN, Items, Size, Username) VALUES ('04-11-2009', 'CN=lbking,OU=Brugere,OU=Legepladsen,DC=kokslegeplads,DC=local', '2', '7', 'lbking')

 

[crobin1]

To connect to SQL from PowerShell there are several steps involved. Take a look at both thread1619-1574904 and the Google search posted there.

 

[kokser]

Is it not possible with some invoke-sqlcmd command? I keep getting login failure, so I can't test it, I'm probabl just doing somthing wrong.

 

[crobin1]

So you're using SQL 2008? You might look at these two pages for ideas:

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

http://www.mssqltips.com/tip.asp?tip=1684

 

[kokser]

ah, s something like this SHOULD work.

invoke-sqlcmd -inputfile "c:\05-11-2009.sql" -serverinstance "kokslegeplads\sqlexpress"

But I get this error :S

Invoke-Sqlcmd : Invalid object name 'Brugere'.
At line:1 char:14
+ invoke-sqlcmd <<<<  -inputfile "c:\05-11-2009.sql" -serverinstance "kokslegeplads\sqlexpress"
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

I think this implies that the talbe I'm the .sql file wants to write to, does not exist.
But when I execute the .sql file through sql studio, it works perfectly fine.

Any ideas?

 

[kokser]

ah, simple adding the -database parameter did it.

invoke-sqlcmd -inputfile "c:\05-11-2009.sql" -serverinstance "kokslegeplads\sqlexpress" -database "powershell"