Post reply

Close rowset?

  • December 20, 2005 at 6:09 pm

    #90259

    With the following code I can import a sheet called AlphaByMarket from an excel spreadsheet file called Market.xls into a dynamically generated sql server 2000 table.

    SELECT * INTO MarketsPreTemp

    FROM OpenRowSet('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};

    DBQ=C:\MarketsImport\Markets.xls;

    DriverID=790;',

    'SELECT * FROM [AlphaByMarket$]')

    However, if I then try to import the second sheet "AlphaByState"

    SELECT * INTO MarketsPreTemp

    FROM OpenRowSet('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};

    DBQ=C:\MarketsImport\Markets.xls;

    DriverID=790;',

    'SELECT * FROM [AlphaByState$]')

    It fails, unless I wait a few minutes.

    It appears that SQL Server is locking/keeping open a connection to the file for some amount of time right?

    So, how can I tell the server, once the first sheet is imported (AlphaByMarket), to close the connection, so that I can then initiate a new connection for the second sheet to be imported (AlphabyState)?

    Is there something like CloseRowSet? I haven't so far been able to find such a command in the Books online or elsewhere. These needs to be tsql based, not VB or other programming.

    Thanks.

  • December 23, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • December 23, 2005 at 8:47 am

    #611317

    If you are importing multiple sheets from one xls file, it's better to define a linked server to the xls file. Then you can use the four part name to inport data or use OPENQUERY.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply