SQLServer to FoxPro tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

SQLServer to FoxPro tables

Author

Topic

dav101
Starting Member

10 Posts

Posted - 2004-05-30 : 12:51:56

I have an existing application that requires duplicate entries in SQL2k and a Foxpro App.

I have written triggers in SQL to create a table of information that has to be inserted or updated in the FP tables. There are 5 different FP tables that this data goes into.

Can I link the FP tables to the SQL server and update/insert them from a SP or do I have to create a DTS pkg that would be called from
a SP?

Thanks

Mike

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-30 : 19:33:33

You should be able to do this via Linked Servers. That would be the preferable solution.
Check out Books Online about this.

Tim

dav101
Starting Member

10 Posts

Posted - 2004-05-30 : 20:33:18

Tim,
Thank you for your response.

I've been looking all over this forum on "linked servers" and BOL but nothing shows up with FoxPro. I guess I was expecting something with FP to show up since it is a MS product.

Since I'm inserting and updating FP tables, I was concerned about the capability of the ODBC conncection.

Thanks again,
Mike

Mike

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-30 : 20:52:15

SQL's Linked Server doesn't really care what database it is - as long as the driver is ODBC compliant. I could be wrong, but I think FP is only recently become a MS product, hence the absence of any extra 'assistance'.

I suppose the only way you'll know for sure is to experiment.

Tim

dav101
Starting Member

10 Posts

Posted - 2004-05-30 : 21:02:31

Tim,
That is what I was glad/affraid to hear. FP has been a product of Ms for quite a while, but offers little help/examples getting it setup.

Trying to setup a Linked Server now to test.

Once this server link is setup, do you leave it up? I see the sp_addlinkedserver and am wondering. It will probably be used more than one time a day to do the update/insert part of the process.

It is raining cats and dogs again here so I thought I would get a jump on Tuesday.

Mike

Mike

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-30 : 21:13:35

I don't see why not - as far as I can see it's similar to a system DSN in Windows. i.e. it's not used until it's required.

I don't think it does any polling to see if the remote server is still there.

dav101
Starting Member

10 Posts

Posted - 2004-05-30 : 21:20:21

I'm using Excel and a DSN to get the correct info first. Once I can access it from Excel I will use that info to setup the LinkedServer.

Thank you,

Mike

dav101
Starting Member

10 Posts

Posted - 2004-05-30 : 22:50:09

Once I added a System DSN to the Server(instead of my machine), I added the linked server via EM to the Server. I could see my FP tables.

From QA I had to use the following to see my data:
select * from openquery( LinkedServerName, 'select * from MyTable')

Is OpenQuery my only option?
Thanks in advance,

Mike

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-31 : 02:09:57

No. You can use the other notation, although BOL recommends against it:

SELECT * FROM LinkedServerName...MyTable

You can also do UPDATE's & INSERT's in this way.

NB: They recommend against it because SQL Server reads the table into it's cache and performs the required action. So it all depends on how big your table is.

Tim

dav101
Starting Member

10 Posts

Posted - 2004-05-31 : 11:51:59

Tim,

I read about avoiding the 4part nameing.

When I try this I get an error
SELECT * FROM LinkedServerName...MyTable

Invalid schema or catalog specified for provider 'MSDASQL'.

Thanks for your help,

Mike

dav101
Starting Member

10 Posts

Posted - 2004-05-31 : 12:26:57

Thes are the srrors returned when I try

EXEC sp_catalogs LinkedServerName

OLE DB provider 'UNKNOWN' reported an error. One or more arguments were reported invalid by the provider.
[OLE/DB provider returned message: The parameter is incorrect.]
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Driver not capable]

sp_linkedservers returns
SRV_Name Provider Product DataSource ProviderString Location SRV_Cat
ART MSDASQL Fox Pro ARTData NULL NULL Main_AQ.dbc

Thought this might help

Mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources