SQLRally 2011 Scripting Guy Guest Blog Post

Ed Wilson (Blog|Twitter) aka Scripting Guy has series of SQL Server related posts the week of  May 2nd 2011 including my guest blog post. The post, Use ACE Drivers and PowerShell to Talk to Access and Excel, demonstrates querying Excel and Access files from PowerShell and loading the data into a SQL Server table. There several ways to get data from Excel and Access, but I find using ADO.NET to be the most straight forward.

An important consideration when using ADO.NET against Excel and Access files is selecting the right OLE DB drivers. In the post I talk about using Access Control Entry (ACE) drivers.  ACE is completely free, and it even includes a 64-bit version. For SQL Server professionals having a 64-bit driver for Excel and Access is a big deal as ACE’s predecessor, JET only supported x86. ACE is included with Office 2007 or higher and Office 2010 has a 64-bit version. If you don’t have Office or you’re installing on a server go to Microsoft Access Database Engine 2010 Redistributable, and download AccessDatabaseEngine.exe or AccessDatabaseEngine_x64.exe, depending on your operating system.

I’ve mentioned this in the post, but I think this is a key takeaway which I’ll restate–When you have ACE drivers, there is no reason to use the old deprecated JET drivers—even for older versions of Microsoft Access and Excel. A common mistake I see, even with seasoned developers, is to drop to JET for .mdb and .xls files when you don’t need to. I’ve even made this mistake myself.

I found a helpful blog post on MSDN from the CSS SQL Server Engineers that talks about different data providers and discusses a migration strategy.

My guest blog post Use ACE Drivers and PowerShell to Talk to Access and Excel doesn’t delve into other uses of the ACE driver including working with delimited text files which I’ll blog about in a future post.

{ 8 comments… add one }

  • kevin lan May 26, 2011 at 6:44 pm

    chad,thanks for you kind help.

    i encountered another question

    $10minutes = new-timespan -Minutes 10
    $10minutesDiff=(get-date) – $10minutes
    $dt = Get-SqlErrorLog “SZPC750GMorningStar” $10minutesDiff
    $connectionString = “Data Source=SZPC750GMorningStar;Integrated Security=true;Initial Catalog=Monitoring;”
    $bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $connectionString
    $bulkCopy.DestinationTableName = “SqlErrorLog”
    if $dt.table(0).count>0
    $bulkCopy.WriteToServer($dt)

    if there hasn’t any errorlog in the time, it will raise
    an error.

    Multiple ambiguous overloads found for “WriteToServer” and the argument count: “1″.

    thanks

    Reply edit
  • Chad Miller May 26, 2011 at 7:24 pm

    Keep in mind PowerShell uses -gt instead of >. Also an array of datarows does not have a table property. What’s the definition of your Get-SqlErrorLog function? The check should be written like this:

    if ($dt -ne $null)
    {
    $bulkcopy.writetoserver($dt)
    }

    or since you are just checking for NULL:
    if ($dt -ne $null)
    {
    $bulkcopy.writetoserver($dt)
    }

    Reply edit
  • Aaron June 12, 2013 at 2:01 pm

    How would you add instance name and bulkcopy instance name and errorlog contents into destination table?

    Reply edit
    • Chad Miller June 21, 2013 at 7:19 pm

      The easiest thing would be to use T-SQL with xp_readerrorlog into a temp table then add the two elements in select statement. From there run query with invoke-sqlcmd into the $dt variable.

      Reply edit
  • kevin lan May 25, 2011 at 10:17 pm

    hi chad, when i used this script , i came cross a problem
    “Exception calling “Fill” with “1″ argument(s): “Query must have at least one destination field.”

    the excel file is empty.

    would you help me how to fix it.

    thanks

    Reply edit
  • Chad Miller May 25, 2011 at 10:28 pm

    I can try, but I need more information or even a test file. What version of Excel? Are you specifying a query? Are you trying to query an empty Excel file?

    Reply edit
  • kevin lan May 25, 2011 at 10:31 pm

    i want to catch the sql errorlog by using powershell script.

    sometimes the errorlog csv file is empty.

    here is my script:

    $10minutes = new-timespan -Minutes 10
    $10minutesDiff=(get-date) – $10minutes
    Get-SqlErrorLog “SZPC750GMorningStar” $10minutesDiff |export-csv d:/sqlerrorlog.csv -NoTypeInformation -Force

    $filepath = “d:”

    $connString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`”$filepath`”;Extended Properties=`”text;HDR=yes;FMT=Delimited`”;”

    $qry = ‘select * from [sqlerrorlog.csv]‘
    $conn = new-object System.Data.OleDb.OleDbConnection($connString)
    $conn.open()
    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn)
    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
    $dt = new-object System.Data.dataTable

    $null = $da.fill($dt)

    #$dataTable = Get-SqlData “SZPC750GMorningStar” Monitoring “SELECT top 10 * FROM dbo.DimDate”
    $connectionString = “Data Source=SZPC750GMorningStar;Integrated Security=true;Initial Catalog=Monitoring;”
    $bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $connectionString
    $bulkCopy.DestinationTableName = “SqlErrorLog”
    $bulkCopy.WriteToServer($dt)

    thanks

    Reply edit
  • Chad Miller May 26, 2011 at 12:45 pm

    One thing you could do is check your file is greater than 0 bytes:

    if ($filepath).length -gt 0
    {

    }

    Another alternative which actually is easier. If your Get-SqlErrorLog function uses SMO or xp_readerrorlog it already returns an array of type dataRow, so you don’t need to convert to CSV and read CSV you just do this:

    [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) > $null
    $server = new-object (“Microsoft.SqlServer.Management.Smo.Server”) ‘Z002SQLEXPRESS’
    $dt = $server.ReadErrorLog(0)
    $connectionString = “Data Source=SZPC750GMorningStar;Integrated Security=true;Initial Catalog=Monitoring;”
    $bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $connectionString
    $bulkCopy.DestinationTableName = “SqlErrorLog”
    $bulkCopy.WriteToServer($dt)

    Reply edit

Leave a Comment


eight × = 48

%d bloggers like this: