Data Import In SQL 2000

  • How To Import Selective Data From Dbf File In SQL 2000. Any Method Is Welcome. Pls Provide Example In Solution.

  • Here are 3 options:

    1. Create a linked server to your DBF and select the data. Here is the T-SQL to create a linked server to a FoxPro free table directory:

    /****** Object: LinkedServer [MISS] Script Date: 12/29/2008 11:11:28 ******/

    EXEC master.dbo.sp_addlinkedserver @server = N'[name you want to use]', @srvproduct=N'VFPOLEDB', @provider=N'VFPOLEDB.1', @datasrc=N'[path to dbf]'

    /* For security reasons the linked server remote logins password is changed with ######## */

    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MISS',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'collation compatible', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'data access', @optvalue=N'true'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'dist', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'pub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'rpc', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'rpc out', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'sub', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'connect timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'collation name', @optvalue=null

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'lazy schema validation', @optvalue=N'false'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'query timeout', @optvalue=N'0'

    GO

    EXEC master.dbo.sp_serveroption @server=N'[@server used in sp_add_linkedserver]', @optname=N'use remote collation', @optvalue=N'true'

    2. Use OpenQuery or OpenRowset - see BOL for specific syntax.

    3. Create a DTS package - again see BOL for specifics.

  • Ummmm.... why not selectively export it from whatever app is using the .DBF format?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank You Guys For The Info But I Wanted 1 More Thing

    Select * From OpenRowset('MSADSQL','Driver={Microsoft dBase Driver (*.dbf)};SourceType=dbf','Select * From C:\MyApp\MyTest.Dbf')

    The Command Runs Perfect

    But

    Select * From OpenRowset('MSADSQL','Driver={Microsoft dBase Driver (*.dbf)};SourceType=dbf','Select * From C:\MyApp\MyTest.Dbf Where MyName = 'Jay' ')

    Gives Error - Pls Give Solution For The Same

  • Would you tell us what the error is, please?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jay Sapani (12/29/2008)


    Thank You Guys For The Info But I Wanted 1 More Thing

    Select * From OpenRowset('MSADSQL','Driver={Microsoft dBase Driver (*.dbf)};SourceType=dbf','Select * From C:\MyApp\MyTest.Dbf')

    The Command Runs Perfect

    But

    Select * From OpenRowset('MSADSQL','Driver={Microsoft dBase Driver (*.dbf)};SourceType=dbf','Select * From C:\MyApp\MyTest.Dbf Where MyName = 'Jay' ')

    Gives Error - Pls Give Solution For The Same

    You likely need to escape the single quotes in your where clause or you can use double quotes around Jay as at least the Foxpro ODBC and OLEDB drivers will use either single or double quotes as the string/character identifier.

  • You likely need to escape the single quotes in your where clause or you can use double quotes around Jay as at least the Foxpro ODBC and OLEDB drivers will use either single or double quotes as the string/character identifier.

    I Tried Your Solution Solution But It Gives Error 7321 -

    Syntax Error Missing Operator In Query

  • Jay,

    It's a bit of a misnomer... all single quotes must be turned into 2 single quotes... not actual "double quotes". And, start posting the actual query you used and the actual error you got... hard to read minds and computer screens from a thousand miles away before coffee. 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jay Sapani (12/31/2008)


    You likely need to escape the single quotes in your where clause or you can use double quotes around Jay as at least the Foxpro ODBC and OLEDB drivers will use either single or double quotes as the string/character identifier.

    I Tried Your Solution Solution But It Gives Error 7321 -

    Syntax Error Missing Operator In Query

    You did not tell me which way you tried it. This one uses double-quotes:

    Select * From OpenRowset('MSADSQL','Driver={Microsoft dBase Driver (*.dbf)};SourceType=dbf','Select * From C:\MyApp\MyTest.Dbf Where MyName = "Jay" ')

    This one uses escaped single quotes:

    Select * From OpenRowset('MSADSQL','Driver={Microsoft dBase Driver (*.dbf)};SourceType=dbf','Select * From C:\MyApp\MyTest.Dbf Where MyName = ''Jay'' ')

    Do either of these work?

  • Thanks Jack Bro For The Solution.

    The Double Qoutes Was Giving Error.

    The Escaped Qutes One Done The Trick For Me.

    SSChampion Now My Other Query Where I Had To Update 10 Lakhs Has Been Redeuced To 50,000 Because Of This Trick And Now My Routine Completes In 1 Hour Instead Of 3 Hrs Which It Use To Take Case In My Previous Senario Of 10 Lakhs

  • Jeff Moden (12/31/2008)


    Jay,

    It's a bit of a misnomer... all single quotes must be turned into 2 single quotes... not actual "double quotes". ... 😛

    Hmmm, maybe we should have a special name for single quotes, to make it less confusing? Maybe something like "apostrophe"? ... 😀

    Nah, Unix & C wonks will never be able to learn a big word like that. I mean next, we'll be calling curly brackets something crazy like "braces". Madness... :w00t:

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • RBarryYoung (12/31/2008)


    Jeff Moden (12/31/2008)


    Jay,

    It's a bit of a misnomer... all single quotes must be turned into 2 single quotes... not actual "double quotes". ... 😛

    Hmmm, maybe we should have a special name for single quotes, to make it less confusing? Maybe something like "apostrophe"? ... 😀

    Nah, Unix & C wonks will never be able to learn a big word like that. I mean next, we'll be calling curly brackets something crazy like "braces". Madness... :w00t:

    See... there ya go again... being all practical and all... 😛

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

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