Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    167

    Question Unanswered: ad hoc query crashes sql server

    We've recently migrated several sql server 2000 databases on a 32 bit server to sql server 2008 on a 64 bit server. As I result, I've had to change all of our 32bit data sources for opendatasource and openrowset to their 64bit counterparts - in this case, Microsoft.ACE.OLEDB.12.0. I've also had to change the server's registry setting of DisallowAdHocAccess to 0 (because clients without admin access are uploading this data).

    Unfortunately, I've discovered that using openrowset for xlsx files completely crashes sql server with error 17311. I have to manually restart both sql server and agent even though agent is set to autorestart sql server on failure.

    The query I use that crashes my server is:


    Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\MyMachine\public\example.xlsx;HDR= YES', 'SELECT * FROM [This_silly_sheet]')


    Has anyone else run into this and found a runaround? Unfortunately I don't have a 64bit SQL08 dev box to test workarounds and I can't chance crashing the production server again.

  2. #2
    Join Date
    Apr 2012
    Posts
    1

    Same Problem

    I have your similar trouble.

    SQL crash when server is too much busy (not for ALL ad hoc query).

    I use OPENDATASOURCE instead of OPENROWSET and Microsoft.ACE.OLEDB is configured to run "inProcess" with SQL Server Standard Edition 64bit.

    I was thinking to write a specific application that run at 32 bit and invoke it to do same works of Opendatasource but i'm not sure this is a good idea.

    This is an annoying and big problem.

    Please, if anyone has any idea is appreciated !

    Thanks.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •