Linked Server For Visual Foxpro (DBF) Table

Jul 13, 2015

I'm running SQL Server 2008 (x64) version.

How can i create a linked server for Microsoft Visual Foxpro databases ?

I'm using Microsoft.ACE.OLEDB.12.0 driver.

I success create a linked server, and can browse all tables from linked server connections, but why when query data using

SELECT * FROM OPENQUERY(PIP_TEST,'select * from tbctrl')

It getting error message

Cannot process the object "select * from tbctrl". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "pip_test" indicates that either the object has no columns or the current user does not have permissions on that object.

View 6 Replies


ADVERTISEMENT

Visual FoxPro Linked Server

Jun 19, 2007

I am trying to add a FoxPro linked server to MS SQL 2005, and I can't seam to create a linked server that works. What am I doing wrong in linking the server?



I have an ODBC connection that worked but not OLEDB; how can I do this with OLEDB (either VFPOLEDB or Jet, if it will work) and not ODBC.



This is what I thought was right




Code Snippet

sp_addlinkedserver 'test',

'FoxPro',

'VFPOLEDB',

'C:DataSomeDatabase.dbc',

NULL,

NULL,

NULL



But it gives the error:

Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "test".



Also I know in MS SQL 2000 once you linked a server you could view it in EM, but when I linked the VFP via ODBC I could query agianst it, but I could not open it in Mangament Studio.



Thank you in advance.

View 11 Replies View Related

Linked Server To Visual FoxPro Not Work

Feb 20, 2005

I've downloaded and installed the latest VFPOLEDB (12/04) on 2 separate SQL Server boxes.

In both cases, If I connect to SQL Server with Query Analyzer as (local) while on a box, the linked server to my foxpro database works fine with openquery().

However, If I'm at one box and attached to SQL Server on the other box, the openquery() fails.

Here's some particulars:
---
EXEC sp_addlinkedserver
@server='VFP',
@provider='VFPOLEDB',
@datasrc='\hdmcpdctis1 isrnddata',
@srvproduct='Visual FoxPro'

--this works on either (local) box
SELECT *
FROM OPENQUERY(VFP, 'select * from tislists')
Go

--but, the same openquery() above doesn't work if the box I'm running it from is attached to the SQL Server on the other box. I get:

Server: Msg 7302, Level 16, State 1, Line 1
Could not create an instance of OLE DB provider 'VFPOLEDB'.
OLE DB error trace [Non-interface error: CoCreate of DSO for VFPOLEDB returned 0x80040154].
=====================
One other approach I tried that works while on the (local) box, but fails when attached to the SQL Server on the other box:

select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB= \hdmcpdctis1 isrnddata ',
'select * from [tislists.DBF]')


With error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Driver does not support this function]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
===========================

Any Help is greatly appreciated! Thanks,

peter :confused:

View 5 Replies View Related

DTS: Visual FoxPro To SQL Server

Jul 15, 2004

Hi,

Today was my first day to use DTS. I tried to transfer a Visual FoxPro Database to my SQL Server. I noticed that I am getting errors on the data that contains date fields. So, for testing purposes I transformed the Visual FoxPro fields that contained datefields into char fields. And this works, but obviously I cannot hunt around in a huge database looking for datefields in tables and change them manually. Plus, by changing to char fields I noticed that those fields that are empty are transformed as 1899-12-30. :p

Does anyone have any ideas?

Thanks,
Laura

View 3 Replies View Related

How To Access Visual FoxPro Data From SQL Server ?

Jul 23, 2005

I'm trying to access Visual Fox Pro Data from Sql Server 2000, Cananyone have any Idea ?Thanks

View 1 Replies View Related

DTS Import To SQL Server 2000 From Visual FoxPro

May 17, 2007

Greetings,



I am unsure if this is the correct forum to send this question, but I can't seem to find any information regarding this problem. If this is the wrong place, please direct me to the correct spot.



I am attempting to import data from a free tables FoxPro database to SQL 2000 using a DTS Package which has worked correctly every day for the past 2 years. Yesterday, I got an error.



The package has around 10 tables that it deletes, re-creates, and populates with data from the Foxpro. All of the tables except one work correctly.



When I try to do an explicit import using the ODBC connection to populate that one table, I get the following error: Context: Error calling Openrowset on the provider.



I created an access database on my local computer and setup an ODBC connection and link tables to the database to see if it would work, and it did. So I thought there might be something wrong with the ODBC data source on the SQL Server, so I deleted it and created a new one, used it and I receive the same error.



I thank you in advance for any assistance or direction you can provide me for finding an answer.



View 4 Replies View Related

Query Server Error When Accessing Visual FoxPro 9.0 Via ODBC

Nov 6, 2007



I have a report that accessed a Visual FoxPro 6.0 database via ODBC. Since I upgraded to Visual FoxPro 9.0, now I am getting the error:

Query Server Error
DMS-E-RBI_TABLE The table or view <table name> was not found in the dictionary

I've verified that the right path is setup and I've tried installing about every driver and none of them are working. I created a new database in 9.0 and the I can access it fine. Also, there are old tables that are accessible in the same path, just not the ones I need. Any ideas would be helpful..

View 1 Replies View Related

Linked Server To FoxPro

Nov 5, 2004

Hello,

I want to know if there is an way to SET an index when send a query using the OpenQuery function to a FoxPro database using a linked server in the MS-SQL that points to a System DSN with the MS ODBC Driver to FoxPro.

Thank you in advance,

Aldair.

View 5 Replies View Related

FoxPro Linked Server

Feb 19, 2008

Hi all, I'm having issues with a FoxPro linked server.

I've set up a linked server to a FoxPro dbc using the Microsoft OLE DB Provider for Visual FoxPro. When I'm on Management Studio on my server the link appears to be working fine and a stored procedure I've created to get the indo from the dbc and put it into a temp table works fine.

However, when I try to execute the sp on management studio on my local machine I get the following error:

OLE DB provider "VFPOLEDB" for linked server "tern" returned message "Invalid path or file name.".
Msg 7303, Level 16, State 1, Procedure usp_SSRS_007, Line 28
Cannot initialize the data source object of OLE DB provider "VFPOLEDB" for linked server "tern".

And I also get a similar error when I try to test the connection of the linked server on my local machince.

This is now driving me nuts , so many many thanks in advance for any help!!!

View 4 Replies View Related

SQL Triggers: Transfer Data From SQL Server 2000 To Visual FoxPro DBase

Jul 23, 2005

Hi all,I am fairly new to using triggers and was seeking some help from thosethat have experience with them. I am looking to transfer data from aSQL 2000 database to a Visual FoxPro database on another computer. Iwould like to transfer about three fields of data to a VFP table eachtime an insert is made on the SQL table. I am some what familiar withthe structure of creating the trigger but here is what I would likehelp with: Selecting the SQL data to transfer, Connecting to VFPdatabase, Insert SQL data into VFP table.CREATE TRIGGER [xyz] ON [dbo].[AAA]FOR INSERT??? Select a,b,c from SQL table??? Connect to VFP Database and Table??? Insert into VFP table Values a,b,cAny information, tips, or even an example Trigger procedure would helpand be greatly appreciated.Thank you,Brett

View 1 Replies View Related

How Is Security To FoxPro Managed Through Linked Server From SQL

Sep 14, 2006

I€™ve been working on getting a linked server through SQL 2005 to work with VFP 9.
I get access denied for any and all security set ups on the linked server. I€™ve checked the folder-level security settings and see that the user I€™m logged in as, and have tried through security settings, and they seem to have access.
What other security settings should I be checking?

View 5 Replies View Related

Pass A Variable To A Linked Server (FoxPro) Query

Nov 22, 2004

I'm having problem with an OpenQuery statement in stored procedure, which should be run on FoxPro linked server. I'm getting either an error messages or not the result I expected. I know that the problem is with WHERE part, without this part it works.


Here is the code:
-------------------------------------
DECLARE @LastDate datetime
SELECT @LastDate = MAX(DateChaged)
FROM tblPersonel
WHERE ACTIVE IN( 'A', 'T')

1. I tried:
SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=''+@LastDate+''')

This line gives me an error msg:

Could not execute query against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.]


2. I tried to use CTOD() - FOXPRO function to convert character to date.

SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=CTOD(''+@LastDate+'')')

-this doesn't give any error, but doesn't limit the result set as it should.

Thanks all.

View 2 Replies View Related

FoxPro Linked Server And Passing Variable Within OPENQUERY

Mar 8, 2005

I'm posting this because I found this solution after much digging.

The goal here is to incorporate a variable parameter within a OPENQUERY and, ultimately build a dynamic Where clause for use within a OPENQUERY linked server routine. I'm posting because I spent a lot of time trying to get this to work and also, have seen other posts here that hinted it wasn't doable.

First of all - there a good quick article that gets close for FoxPro and possibly works as is for ACCESS:

http://support.microsoft.com/default.aspx?scid=kb;en-us;314520

Here's code for a solution:

DECLARE @OPENQUERY nvarchar(4000),
@TSQL nvarchar(4000),
@FAMILY CHAR(10)

SET @FAMILY='Touring'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''

SET @TSQL = 'select cov,family,model from vinmast where family='+'['+@FAMILY+']'')'

EXEC (@OPENQUERY+@TSQL)

All shown are single quotes.

In Visual Foxpro, ' ' or " " or [ ] can be used a delimeters

In addition, if wanting to build a dynamic where clause, you could do something like:

SET @TSQL = 'select cov,family,model from vinmast '
IF <some condition met to include FAMILY filter>
Begin
SET @TSQL=@TSQL+'where family=['+@DUTFAMILY+']'''
SET @TSQL=@TSQL+ ')'
End
-----------------
Here's the entire Stored Procedure:


CREATE PROCEDURE dbo.ewo_sp_DUTLookup
(
@DUTPROJECT char(25)=NULL,--Project
@DUTFAMILY char(10)=NULL,--Family
@DUTMODEL char(20)=NULL,--Model
@DUTYEAR char(4)=NULL,--Model Year
@DUTBEGIN char(25)=NULL,--Beginning of COV/DUT number
@DEBUG int=0
)


AS

DECLARE @OPENQUERY varchar(4000),
@TSQL varchar(4000),
@TWHERE varchar(4000),
@intErrorCode int

select @intErrorCode = @@ERROR,
@TSQL='',
@TWHERE=''


IF @intErrorCode=0
Begin
SET @OPENQUERY = 'SELECT * FROM OPENQUERY(VFP,'''
SET @TSQL = ' select dut_pk,cov,family,model,project,modelyr from vinmast '
End

set @intErrorCode = @@ERROR

IF @intErrorCode = 0 and
@DUTFAMILY is not NULL or
@DUTMODEL is not NULL or
@DUTPROJECT is not NULL or
@DUTYEAR is not NULL or
@DUTBEGIN is not NULL
set @TWHERE=' where '



-- Check for Family criteria
If @intErrorCode = 0 and @DUTFAMILY is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' family=['+@DUTFAMILY+'] AND '
set @intErrorCode = @@ERROR

-- Check for Model criteria
If @intErrorCode = 0 and @DUTMODEL is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' model=['+@DUTMODEL+'] AND '
set @intErrorCode = @@ERROR

--Check for Project criteria
If @intErrorCode = 0 and @DUTPROJECT is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' project=['+@DUTPROJECT+'] AND '
set @intErrorCode = @@ERROR

--Check for Model Year
If @intErrorCode = 0 and @DUTYEAR is not NULL and Len(@TWHERE)>0
SET @TWHERE=@TWHERE+' modelyr=['+@DUTYEAR+'] AND '
set @intErrorCode = @@ERROR

--Check for beginning of DUT
If @intErrorCode = 0 and @DUTBEGIN is not NULL and Len(@TWHERE)>0
Begin
SET @DUTBEGIN=RTRIM(@DUTBEGIN)
SET @TWHERE=@TWHERE+' substr(cov,1,'+cast(len(@DUTBEGIN) as char(20))+')=['+@DUTBEGIN+'] AND '
End
set @intErrorCode = @@ERROR


IF @intErrorCode=0 AND substring(@TWHERE,Len(@TWHERE)-3,4)=' AND '
Begin
set @TWHERE=Substring(@TWHERE,1,Len(@TWHERE)-3)
select @intErrorCode=@@ERROR
End



SET @TWHERE=@TWHERE+''')'

IF @debug<>0 and @intErrorCode=0
Begin
print @intErrorCode
print @OPENQUERY
print @TSQL
print @TWHERE
print @OPENQUERY+@TSQL+@TWHERE
End

IF @intErrorCode=0
EXEC (@OPENQUERY+@TSQL+@TWHERE)
GO

Peter

View 2 Replies View Related

Severe Error After Installing SQL2005 SP2 Query To Linked Server Foxpro With Oledb

Oct 24, 2007

After installing sql2005 sp2 a simple select query to a linked server reports the following error message:

Msg 0, Level 11, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.Msg 0, Level 20, State 0, Line 0A severe error occurred on the current command. The results, if any, should be discarded.
Before installing SP2 we used sql2005 without any service packs, the linked server worked fine.

The linked server is a Visual FoxPro database.

After uninstalling and installing the 'Microsoft OLE DB Provider for Visual FoxPro 9.0' the issue stil remains.

View 3 Replies View Related

Microsoft OLE DB Provider For Visual FoxPro 9.0

Feb 27, 2007

Im quite interested to see if any one is using Microsoft OLE DB Provider for Visual FoxPro 9.0 in an SSIS package. Is it possible to use this for free table directory like you can with the ODBC driver. ????

thanks kindly



My basic proccess is as follows: The source foxpro database has hundreds of dbf files ---the SSIS procress is as follows

1: Copy only required dbf files and/or matching .fpt files over to local drive on SSIS box(we only need 10 files)

use MS Visual Foxpro ODBC driver (free table option) and set up a system DSN--dataflow tab---datareader source--and away you go

View 1 Replies View Related

Problem Querying A Visual Foxpro DBF

Jan 3, 2007

Good day,

Current Setup:

Using:
Visual Web Developer Express
SQL Express
IIS 6.0 with >net 2.0 Installed
Visual Foxpro

I actually have two questions (but am making good progress so will focus on the issue at hand).

1. I setup a Login.aspx / Default.aspx / Register.apsx / Membership.aspx as specified in the examples in MSDN...all is working ok.

2. When I register a new user, I wanted to capture their Customer ID during login to store in their Membership view table. I did this and is working ok....and to make it easy, am storing the value in Comments field.

3. I want to query a Visual Fox free table (DBF) that contains my client's Customer Invoice data...and have the ODBC Connection setup and working properly.

Here's the problem....

I'm having trouble formulating the SELECT string within the asp code to capture the Membership.Comments field.

I need the proper functions to grab the Comments field data and then use it within my select statement.

Presently, the query works if I just hard-code the value i.e.

"SELECT * from [invdata] WHERE cus_no = '1' "

I need to replace the '1' with the comments field data.

Thanks:

On a side issue: it took me days to just to get this all working to where it is...does anyone have a detailed example of how to Login to a site and pass the parms to another page to query the Logged In user's data from a database......a real world example that I have yet to see highlighted anywhere in the Forums or books I've bought.

For example: I am customer ABC123, I log in to my vendor's site and want to see all my orders placed.









View 1 Replies View Related

Importing Visual Foxpro Tables Into SS2005

Jun 28, 2007

Windows XP Pro - Sql Server 2005 Management Studio (current) - current visual foxpro oledb driver



I have an origin data folder with many foxpro dbf files



When I go through the "wizard" to import data from the origin folder using the "free table" option, I can locate the folder just fine. The form shows me a list of the files in the folder - but I cannot scroll through the list.



When I select the folder and continue, I test the connection and get a message that it is fine.



Then I go on to actually import tables. The list that is presented of tables that I can import does not include all the dbf files in the folder. There are 207 files in the origin folder, but only a fraction (about 120) of those show up in the list as candidates to be imported. I cannot see a pattern to the ones that are excluded from the list.



Does anyone know what could be causing this strange behavior?



Thanks.

View 3 Replies View Related

Does Anyone Know How To Execute A SSIS Package From Visual Foxpro?

Nov 13, 2007

This is a Execute DTS from VFP Example I found. Does anyone have a snippet for executing an SSIS package?

Thanks,
Shannon

*!* Create a DTS Package Object
dtsPkg=CREATEOBJECT('dts.package')

*!* LoadFromStorageFile() requires the following parameters:
*!* UNC path to the .DTS file to be executed
*!* User Password

*!* Load the package to be executed from a file.
dtsPkgName=dtsPkg.LoadFromStorageFile("\UNC_Path_toDTSPkgBeta.dts","User_Password")
*!* Execute the DTS Package
dtsPkg.Execute
*!* Release the DTS Package
RELEASE dtsPkg

View 1 Replies View Related

OLEDB Error When Using Visual FoxPro Driver

Aug 30, 2007

Hi all,

I created a package that extracts records from a .DBF file into an SQL Server database. I installed a Visual FoxPro plugin (vfpoledb). When I run the package from my PC, it runs smoothly. But, when I try to run the same package in another PC, it displays a DTS_E_OLEDBERROR "Class not registered" error. Anyone has an idea about what this means? thanks in advance.

View 3 Replies View Related

OPENROWSET Using Visual FoxPro Provider Error 'Could Not Fetch A Row'

Jun 20, 2006

Hi,
I have 2 separate clients experiencing this problem,
Both systems are
1.Win 2000 Server with Small Business Server 2000 (SP4)
2.SQL Server 2000 (SP2 upgraded to SP4, SP4)
3.Exchange
4.IIS etc

We use SQL Server to connect to FoxPro 2.6 Tables.

We are using OPENROWSET as follows:

Select * from openrowset('VFPOLEDB.1','C:Client Data';;,'Select * from cm')

We are now getting this error:
Server: Msg 7330, Level 16, State 2, Line 1 Could not fetch a row from OLE DB provider 'vfpoleDB.1'. OLE DB error trace [OLE/DB Provider 'vfpoleDB.1' IRowset::GetNextRows returned 0x80040155].

The statement worked up until around 20 days ago, which made us think that it may be windows updates/sps/hotfixes, but i have installed the above on a test machine and installed all windows updates and the test machine works AOK.

Has anybody seen this error?

Thanks in advance
Q

View 2 Replies View Related

SQL 2012 :: Import Visual FoxPro Tables Into Database

Aug 7, 2014

How can I add the option to import data from Visual FoxPro tables into SQL 2012 database?

View 0 Replies View Related

OPENROWSET Using Visual FoxPro Provider Error 'Could Not Fetch A Row'

Jun 20, 2006

Hi,

I have 2 separate clients experiencing this problem,

Both systems are

Win 2000 Server with Small Business Server 2000 (SP4)
SQL Server 2000 (SP2 upgraded to SP4, SP4)
Exchange
IIS etc

We use SQL Server to connect to FoxPro 2.6 Tables.

We are using OPENROWSET as follows:

Select * from openrowset('VFPOLEDB.1','C:Client Data';;,'Select * from cm')

We are now getting this error:

Server: Msg 7330, Level 16, State 2, Line 1 Could not fetch a row from OLE DB provider 'vfpoleDB.1'. OLE DB error trace [OLE/DB Provider 'vfpoleDB.1' IRowset::GetNextRows returned 0x80040155].

The statement worked up until around 20 days ago, which made us think that it may be windows updates/sps/hotfixes, but i have installed the above on a test machine and installed all windows updates and the test machine works AOK.

Has anybody seen this error?

Thanks in advance

Q

View 7 Replies View Related

The Parameter Is Incorrect. (Microsoft OLE DB Provider For Visual FoxPro) After SP1

May 1, 2006

Having installed SP1, my SSIS packages using the OLE-DB Provider for Visual Foxpro no longer work. Using the latest version obtained from here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=E1A87D8F-2D58-491F-A0FA-95A3289C5FD4&displaylang=en

When attempting to create one SSIS from scratch, the message thrown is:

"The parameter is incorrect. (Microsoft OLE DB Provider for Visual FoxPro)."

The radio button option of "Copy data from one or more tables or views" and then selecting the "Next >" throw the error.

It has to be the SP1 causing the problem because nothing else changed on the Server and everything was working fine prior to this.

Microsoft, any feedback on this??

View 25 Replies View Related

Query Data From The Linked Foxpro Database

Aug 2, 2007

hello,guys,

my question is :
how do I query data from the linked foxpro database?

more:
I have linked a visual foxpro server to my sql server database by using the addserver clause.
Two server is in a local network.
My linked foxpro server named 'fox'.
its datasourse is not a dbc file, but a directory of dbf files ,and its full path is d:foxpro object.
In the directory ,there are three table,'show2003.dbf','sysu.dbf','szszj.dbf'.
And the .dbf files are not in any database.
They are just three files in the same directory.
There is no dbc file.
Now I can see the table list on the right page of the linked server.
But there is something wrong with my sql clause.

sample:

select * from fox..sysu


then the message is:
server: message 7313,level 16,status 1,row 1
the appointed constructure or directory to the provider is inefficacious 'MSDASQL'


I know I may use 'openquery',or 'openrowset'.
The problem is that variable is not valid in 'openquery' and 'openrowset'.
But I must use variable.
so ,please give me some advice.



Thank you very much

View 4 Replies View Related

IMPORTING A TABLE INTO SQL SERVER FROM FOXPRO

Dec 10, 1999

I know that the DTS Wizard is supposed to be able to handle heterognous
data imports but I can't get it to work with a free FoxPro table. I have to export to a text file and then import from the text file and spend an hour renaming columns and farting around with datatypes.

CAN I GET A FOXPRO TABLE INTO SQL SERVER DIRECTLY OR NOT?
HOW DO I DO IT?

I don't fully understand some of the questions the DTS Wizard is asking. Can anybody give me a blow by blow account before I ring the Samaritans?

Thank you and Happy Christmas - it may be my last if I can't speed up these imports. Either I'll jump under a bus or my boss will make mince pies out of me.

Thanks

Mark

View 1 Replies View Related

Error Exporting FoxPro 2.5 DOS Table To SQL Server

Mar 1, 2006

Using the Import/Export Data Wizard, I'm trying to export a FoxPro 2.5 DOS (as dBase III) table of 15,000 records to SQL Server 2000. I keep getting this error message:
Insert Error, Column 32 ('PROG_START',DBTYPE_DBTIMESTAMP), Status 6: Data Overflow.
Invalid character value for cast specification.

I have SQL Server create the table each time I run the wizard. The new table allows NULLS in this column and I made sure to overwrite the empty date fields in the FoxPro table with blanks to make sure it would result in NULL. Originally SQL Server tried to put this as SMALL DATETIME, but when I got the message earlier, I changed it to DATETIME.

Any suggestions?

View 3 Replies View Related

FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure

Mar 10, 2005

I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.

Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.

I will do a 1 time DTS from FP into SQL Server tables.

I then create INSERT and UPDATE triggers within FoxPro.

These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.

In the end - the tables are local to both apps.

If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.

Here's the FoxPro and SQL Server code for reference for the Record Insert:

FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)

FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE

nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')

IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
RETURN

ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)

SQLDISCONNECT(nhandle)

IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF

SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))

IF m.errclose
USE IN errorlog
ENDIF
ENDIF

RETURN

SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)

AS

insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)

VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)


IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END

return @RETCODE
GO

View 2 Replies View Related

Provider For OleDbMicrosoft OLE DB Provider For Visual FoxPro

Jan 31, 2006

I am using OleDbMicrosoft OLE DB Rpovider for Visual FoxPro for my Data Source Connection and it includes the deleted records from the Visual FoxPro database, how can it have it ignore the deleted records?

HELP!

 

Thanx

Moshe

View 5 Replies View Related

The OLE DB Provider MSDAORA For Linked Server .... Does Not Contain The Table COUNTRY. The Table Either Does Not Exist Or The Current User Does Not Have Permissions On That Table.

Jun 13, 2006

I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
 
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.

View 3 Replies View Related

How To Convert Foxpro Table In To MSSQL Table

Dec 7, 2005

Hi
I am trying to convert foxpro database table example mytable.dbf in to MSSQL Database table. I have created odbc connection to foxpro database, Then i have created an data set in which i have fill the mytable (stucture and data) by odbc adapter. Now i want to import this Data Set in to the sql database. My table (stucture and data) is in memory and my proble is that how i can write to sql data set or create data table which i can see in MSSQL Database.
Please Suggest
Thanks in Advance.
 
 

View 4 Replies View Related

Convert Foxpro 3.0 Table

May 6, 2004

Hi,

I've got about 6000 tables in foxpro 3.0 and a database in sql srv 2000.

now i've to convert the data of these 6000 tables into the database. the problem is that i have to convert all the tables seperatly!

is there a tool where i can convert these tables automatically?

i know it can with DTS, but the 6000 tables are divided over 1400 directories. So with DTS i can't convert them automatically.

does anyone know a solution?

View 3 Replies View Related

Export SQL Svr '05 Table To FoxPro DBF

Dec 14, 2007

Does anyone know how I can, using SQL and/or T-SQL syntax (although I could do a CLR in C# too..), export a SQL table from SQL Server 2005 to a new Visual FoxPro 9 DBF (table)? The DBF would need to be created on the fly too because this will have to basically be able to export any SQL table...

Thanks ahead of time for any and all assistance...

View 1 Replies View Related

Convert Foxpro Memo Field To SQL Table

Sep 12, 2000

Hello All,

Does anyone know how to convert data in a Memo field from Foxpro into a Text field in SQL table? Straight import via DTS package seems to entirely ignore this memo field and result in blank.
Thanks in advance for your reply.

Koann

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved