ERROR 7399 openrowset ( ms sql server 2000) excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ERROR 7399 openrowset ( ms sql server 2000) excel

Author

Topic

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-03-18 : 12:20:50

I am trying to create an excel file using openrowset in ms sql 2000.

but i get the following error when I try to create the file...My stored procedure code is below as well...

Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error.Authentication failed.

What can I do?
-- here is my code

Create PROCEDURE MCA_SP_CREATE_EXCEL @File_Name as varchar(50) = ''
AS
BEGIN
SET NOCOUNT ON
declare @File_Name as varchar(50)
DECLARE @Cmd varchar(1000)
DECLARE @fn varchar(500)
DECLARE @provider varchar(100)
DECLARE @ExcelString varchar(100)

-- New File Name to be created
IF @File_Name = ''
Select @fn = 'C:\Test.xls'
ELSE
Select @fn = 'C:\' + @File_Name + '.xls'

-- FileCopy command string formation
SELECT @Cmd = 'Copy C:\Template.xls ' + @fn

-- FielCopy command execution through Shell Command
EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT

-- Mentioning the OLEDB Rpovider and excel destination filename
set @provider = 'Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0;Database=' + @fn

-- Executing the OPENROWSET Command for copying the select contents to Excel sheet.
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]'')
select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors')

exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT StoreId, OrderNo, OrderDate, Quantity FROM [Sheet2$]'')
select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as OrderDate,qty as Quantity from sales')

SET NOCOUNT OFF
END

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-18 : 15:27:18

Dup post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=99264

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-03-21 : 20:14:48

this was a permissions issue with the xp_command shell and is resolved. one needed to giev the user permissions to run teh xp_cmd _shell sp.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources