Data Import In SQL 2000

Question

Data Import In SQL 2000

Jay Sapani

Say Hey Kid

Points: 683
More actions
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.

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

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

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)

Jay Sapani Say Hey Kid Points: 683 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

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)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

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

Jay Sapani Say Hey Kid Points: 683 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

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)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

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

Jay Sapani Say Hey Kid Points: 683 More actions · Answer 9

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

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]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

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)