SQL Server Performance

(OPENROWSET) Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.

Discussion in 'SQL Server 2005 General Developer Questions' started by bbasir, Mar 18, 2008.

  1. bbasir New Member

    Can some one tell me how to resolve this issue???? I am using sql server 2000... the procedure that I use is as below....
    Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
    /************************************************************************************************************************************************/
    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 pubs.dbo.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 pubs.dbo.sales')
    SET NOCOUNT OFF
    END
  2. Adriaan New Member

    Can you actually use INSERT INTO OPENROWSET? News to me - but then I'm a SQL 2000 kind of person.[;]
    Anyway, the error message states that the problem is with authentication. IIRC, in SQL 2005 you need to set permissions to get xp_cmdshell to work.
  3. Madhivanan Moderator

    Make sure to read this fully
    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Share This Page