Post reply

Data Import In SQL 2000

  • December 28, 2008 at 11:26 pm

    #216880

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

  • December 29, 2008 at 9:22 am

    #918430

    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.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • December 29, 2008 at 7:13 pm

    #918709

    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)
    Intro to Tally Tables and Functions

  • December 29, 2008 at 10:06 pm

    #918755

    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

  • December 29, 2008 at 11:25 pm

    #918767

    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)
    Intro to Tally Tables and Functions

  • December 29, 2008 at 11:45 pm

    #918772

    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.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • December 31, 2008 at 12:19 am

    #919464

    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

  • December 31, 2008 at 5:55 am

    #919568

    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)
    Intro to Tally Tables and Functions

  • December 31, 2008 at 7:08 am

    #919620

    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?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • December 31, 2008 at 9:52 pm

    #919896

    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

  • December 31, 2008 at 10:03 pm

    #919899

    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]

  • January 1, 2009 at 12:23 pm

    #919998

    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)
    Intro to Tally Tables and Functions

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

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