Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Angry Unanswered: Importing date from excel to sql server 2000 error



    MY problem irrirates me v mutch when i use this statment :
    Select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Libya Fiyat Çalışması.xls;HDR=YES', 'SELECT * FROM [libya$]') for exaple ?

    this statment work v good in sql query analyzer

    but when i use this code in stored procedure in my datebase like:

    CREATE PROCEDURE exl_to_sql @state int
    AS
    SET ANSI_WARNINGS OFF
    SET ANSI_NULLS OFF
    if @state=0
    BEGIN
    Select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Libya Fiyat Çalışması.xls;HDR=YES', 'SELECT * FROM [libya$]')
    END
    SET ANSI_NULLS OFF
    SET ANSI_WARNINGS ON

    GO


    Everytime I try to save the procedure I get "error 7405: Heterogenous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection."

    please i want solusion for my prblem
    note : i used sql server 2000

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by fahd1979 View Post


    MY problem irrirates me v mutch when i use this statment :
    Select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Libya Fiyat Çalışması.xls;HDR=YES', 'SELECT * FROM [libya$]') for exaple ?

    this statment work v good in sql query analyzer

    but when i use this code in stored procedure in my datebase like:

    CREATE PROCEDURE exl_to_sql @state int
    AS
    SET ANSI_WARNINGS OFF
    SET ANSI_NULLS OFF
    if @state=0
    BEGIN
    Select * into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\Libya Fiyat Çalışması.xls;HDR=YES', 'SELECT * FROM [libya$]')
    END
    SET ANSI_NULLS OFF
    SET ANSI_WARNINGS ON

    GO


    Everytime I try to save the procedure I get "error 7405: Heterogenous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection."

    please i want solusion for my prblem
    note : i used sql server 2000
    Try moving these to front of sp and execute.

    SET ANSI_WARNINGS OFF
    SET ANSI_NULLS OFF

  3. #3
    Join Date
    Sep 2012
    Posts
    6

    sorry

    sorry
    i tried your comment but sp not work yet
    please i want abstract solution
    as soon as posible
    please please

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Try going to the query options in SSMS and changing them.

  5. #5
    Join Date
    Sep 2012
    Posts
    6
    please can u clear more

  6. #6
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Suggest you talk with your database support or some senior on your project.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by papadi View Post
    Suggest you talk with your database support or some senior on your project.
    Suggest you let other forum members have a chance to respond the poster's questions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2012
    Posts
    6
    i want good solustion because i big problem with my boss if i do not do what he want
    pleaze last time

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by fahd1979 View Post
    i want good solustion because i big problem with my boss if i do not do what he want
    pleaze last time
    Do you know how to use Management Studio and select the query dropdown menu item? I believe the options menu item is where you want to de-select the various ansi options.

  10. #10
    Join Date
    Sep 2012
    Posts
    6
    ok give me the link to download Management Studio first becouse i used sql server 2000 only and what the version can i use and how
    Last edited by fahd1979; 09-27-12 at 21:41.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •