ok so i used the addlinkedserver sp...but i cant see the tables

[oakpark5]

ok so i used the stored procedure sp_addlinkedserver, and it seemed to work just fine, except when i went to look at the tables there were none there. I pointed it to a file where a few foxpro dbf files are but it didnt seem to find them. Any suggestions? Thanks in advance...

Software Engineer
Team Lead
damn bill gates, he has everything

 

[SQLBill]

Do you have access to the other tables? Even though you linked the server, you still need to have the access to the databases on the other server.

-SQLBill

 

[oakpark5]

Its not an actual database, its a bunch of free tables, but i will check and see if it has access but i'm pretty sure it does...

Software Engineer
Team Lead
damn bill gates, he has everything

 

[SQLBill]

SQL Server tables have to be part of a database. You can link to the server, but if you don't have permission to the remote database and tables, you aren't going to access them through the link either.

I link ServerA (remote) with ServerB (mine). I have access to the databases on ServerB. If I go to ServerA (remote), I have to be able to log on and access the databases/tables on that server. If I can't then my link won't allow me to access ServerA either.

-SQLBill

 

[oakpark5]

Ok i think i got it..I'm still a little fuzzy on the remote server. I got the table to come up but its not giving me the options of querying or creating a new table. Keep in mind this is a foxpro table. Can i do this through the enterprise manager or do i have to use the query analyzer? Thanks for the help, i really appreciate it!

Software Engineer
Team Lead
damn bill gates, he has everything

 

[SQLBill]

I don't use FoxPro so can't really help you with that.

With a linked server, you should be able to access it both ways (EM and QA). You just have to make sure that you have the proper privileges to do what you want and you need to use the full naming convention.
//servername.dbname.owner.table

Refer to the BOL for more information on linked servers.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[philhege]

Hi oakpark5,

Fox isn't aware of the four-part naming convention, so you have to use OPENQUERY() or OPENROWSET() functions to get your data back:

select * from OPENQUERY(linkservername,'SELECT Myfields from MyFoxTable')

Caveats: These are good for SELECTs only. MS does not support INSERT, UPDATE, DELETE, or DDL through a SQL link server to an ODBC data source. Believe me, I went 'round and 'round with them on this. Attempting to do data manipulation usually results in locking up the link server, and (since it's in-process) the whole SQL instance.

Good luck,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[oakpark5]

SQLBill, philhege,

Thanks for all the help, I can run SQL selects in EM now, of course I really wanted to be able to do updates and inserts. I'm assuming that I cannot write a sp_ to update or insert the foxpro tables, but what about the other way around, could i write a stored procedure so the foxpro tables update the SQL tables? Thanks for all the help!

Software Engineer
Team Lead
damn bill gates, he has everything

 

[philhege]

Either way. What we've done to circumvent the update problem for SQL data that must be replicated to Fox is to write a Fox executable that reads a queue table containing the PKs of the records to update. Then it queries for those records into a cursor, SCANs the cursor, and SCATTERs and GATHERs for each row. INSERTs have an APPEND BLANK inserted into that sequence.

For a Fox interface, just write the code into DBC triggers for the Fox tables. If they're free tables, you'll have to hard-code the updates, of course.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[oakpark5]

Yeah thats what i do now, i write programs that update the SQL tables from foxpro...I was just hoping I could get rid of them and take more advantage of the tools in EM. Thanks for the help.

Software Engineer
Team Lead
damn bill gates, he has everything

 

[oakpark5]

Are there any programs that would allow me to run updates, inserts, and delete functions between, SQL server and FoxPro, someone told me something from Embarcadero could do it. Any suggestions?

Software Engineer
Team Lead
damn bill gates, he has everything

 

[philhege]

Once again, it depends. It depends on the application and the environment. What application is manipulating the data? Canned? Custom? What language? What platform?

You can write code in darn near any Windows language to manipulate the data across the two environments, including (I wager) the particular app you have that's supposed to be doing the job. I don't know of any "magic bullet" solutions that you can just "plug in" to an existing application and have it -poof- update SQL and Fox for you.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?

 

[oakpark5]

HA, I have figured it out!!! This is how you do it:

update openquery(linkedserver1, select columnname from table where columnname = 2') set columnname = columnname+1

insert openquery(linkedserver1, select columnname from table where 1=0')values(yourvalue)

delete openquery (linkedserver1, select columnname from table where columnname>100')

Software Engineer
Team Lead
damn bill gates, he has everything

 

[philhege]

Right, that works for a while, and then (depending on how "busy" your Fox tables are) you'll hose the link server, and by association the SQL instance.

Believe me.

We were dealing with link server crashes on an almost daily basis. (Maybe the fly in the ointment is a DBC instead of free tables. Unfortunately I have no choice.) So we backed away from the manipulation statements.

If this approach proves to be stable with free tables, more power to ya. Like I said, I don't have that option.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
A priest, a lawyer, and a rabbi walk into a bar. The bartender looks up and says "What is this, a joke?