Creating Excel XLS from T-SQL using UNC

Question

Post reply

Creating Excel XLS from T-SQL using UNC

anthonyc

SSC Veteran

Points: 241
More actions
January 7, 2004 at 1:55 pm

#160449
Hello all,
I used the script from here:
http://www.sqlservercentral.com/scripts/contributions/763.asp
It works fine if I create the file on the server. However, I wanted to create the file on a network share on another machine. The share has full access to Everyone. When I run the script, the file is created fine (with column names and worksheet names correct), but when the script populates the sheet, I get this error:
```
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  

[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file

    '\\ac1\test_export\export_20040107.xls'.  It is already opened exclusively by another user,

    or you need permission to view its data.]

OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].
```
Why is it that the script can create the file but then not be able to use the file? Thanks in advance for any help.
-AnthonyC

Viewing 6 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736478

January 7, 2004 at 1:58 pm

#489042

might not be closing it.

anthonyc SSC Veteran Points: 241 More actions · Answer 2

Thanks for the reply.

How can I make sure it's closed? The script destroys the ADO.Connection object before populating the spreadsheet.

-AnthonyC

BobAtDBS SSCarpal Tunnel Points: 4590 More actions · Answer 3

You have to explicitly close the object after creation before you destroy the Connection if you are going to update it in the same SP.

Simply add an 'Object.Close' line.

Been there, done that, scratched head, never understood why.

Student of SQL and Golf, Master of Neither

Matt Simmons SSCrazy Points: 2846 More actions · Answer 4

I've found that it is often easiest to export my data to a folder on the server, manipulate the xls then use the file system object to move the file to the appropriate network dir.

Just add one more active-x step in the DTS that moves the file.

Regards,
Matt

anthonyc SSC Veteran Points: 241 More actions · Answer 5

Thanks for the replies. I tried calling the close method explicitly but I still had the same problem. I will try what mjsimmons suggested. Thanks again.

-AnthonyC