none
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed] RRS feed

  • Question

  • The SQL Server is 2000 Entr Edtn.

    I am not sure what else information needs to be provided if someone is looking into it.

    Please let me know.

    Thanks Again

    ----------------

    Hi,

    I have a script written by a developer, who left the company, it inserts the list csv files from a UNC Path location on network, called via Linkd Server having System DSN on the Production Server.

     

    Now the IP Address of the File location changed but I have already pointed the correct location to that same DSN being referred in the Script, which exists as a Linked Server.

     

    The openquery part in the script looks like this:

    (SELECT * FROM OPENQUERY([Linked_Server_Name], ''Select * from [' +@TargetFile + ']'')) Target_Info

     

    and the Linked Server properties looks like this:

    Provider=MSDASQL.1;Persist Security Info=False;Data Source=Linked_Server_Name;Extended Properties="text;HDR=YES"

     

    Please let me know what needs to be changed, Thanks in advance.

    The script throws errors :

     

    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute HDR]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Extended Properties]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Data Source]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Persist Security Inf]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Provider]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute HDR]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Extended Properties]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Data Source]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Persist Security Inf]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Provider]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute HDR]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Extended Properties]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Data Source]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Persist Security Inf]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Provider]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute HDR]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Extended Properties]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Data Source]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Persist Security Inf]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver]Invalid connection string attribute Provider]
    [OLE/DB provider returned message: [Microsoft][ODBC Text Driver] '(unknown)' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].
    Msg 7399, Level 16, State 1, Line 31
    OLE DB provider 'MSDASQL' reported an error. 
    • Edited by ak786 Tuesday, July 13, 2010 5:12 PM more info
    Tuesday, July 13, 2010 4:41 AM

Answers

  • I am not sure about openquery, but the openrowset works well.

    sp_configure 'show advanced options',1
    RECONFIGURE WITH override 
    go
    sp_configure 'Ad Hoc Distributed Queries',1
    RECONFIGURE WITH override
    go
    
    select * FROM OPENROWSET(BULK N'C:\Data.dat', FORMATFILE='C:\FormatFile.xml') as d
    

    Where the data file looks like:

    1,abc
    2,def
    

    And the format file looks like:

    <?xml version="1.0"?>
    <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <RECORD>
     <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
     <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="3" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
     </RECORD>
     <ROW>
     <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLNVARCHAR"/>
     <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
     </ROW>
    </BCPFORMAT>
    
    

    Friday, July 30, 2010 1:36 AM