Help inserting data into VFP from SQL 2005

[chriscboy]

Hi,

I am in the process of trying to sucessfully insert data into our VFP database, from SQL 2005, but I am having some problems.

I have created a linked server to our VFP database, and have successfully inserted data into a test table I created. The problem comes when using our tables which have triggers and default values which get data from another VFP database.

We have a "SYSTEM" database which contains auditing tables and a table that stores the next available primary keys for the tables in our "LIVE" database.

When you insert a record into the "LIVE" database a default value calls a newid() stored procedure that then opens the system database (if it is not already open) and gets the next available ID. On the "LIVE" database there are INSERT and UPDATE triggers that call stored procedures that insert field changes into the system database.

All the above works fine when inserting data from within FoxPro, even if the system database is not open.

Trying to insert from SQL 2005 causes the following (unhelpful) error :

OLE DB provider "VFPOLEDB" for linked server "COMPANYDB" returned message "Unspecified error".
Msg 7343, Level 16, State 2, Line 4
The OLE DB provider "VFPOLEDB" for linked server "COMPANYDB" could not INSERT INTO table "[COMPANYDB]...[companies]".

How can I get this working, or even find where the error is occuring ?

Should I be considering moving all the tables from the "SYSTEM" database and put them in the "LIVE" database? (Not ideal as there are a lot of auditing tables, and I like to keep them separate, as we have Crystal users and all the additional tables will confuse them)

Is the error occuring due to the fact the data provider is only looking at the "LIVE" database. Some advice on this would be great as I am not sure how ODBC/OLEDB would handle the opening of additional databases in stored procedures.

Thanks in advance

Chris

 

[Koen Piller]

Chris,
could you please mail the VFP coding which is causing this error? Furthermore am I correct you have a system.dbc in your VFP application? I would strongly advise you to rename it into something like "dbcSystem.dbc"
Regards,
Jockey(2)

 

[chriscboy]

Hi,

I will post some example post later when I get back to the office. The stored procedure involved is used to get the next available identity for a primary key field.

I do have a system.dbc. It was been like that for years. Why would you suggest renaming it?

Regards

Chris

 

[Mike Lewis]

Chris,

The first thing I would do is to check whether it is the triggers that are causing the error. Try temporarily removing the triggers to see what happens. You will have to provide an actual value for the generated PK rather than generating it. Obviously, you should do this in a test copy of the database.

If the problem is caused by the trigger, try temporarily moving the system tables to the live database (again, do this in your test environment). If the updates and inserts now work, it will at least tell you where the problem lies.

By the way, which version of VFP are you using? If it is 8.0 or above, did you know that you can get rid of your NEWID() function and the associated primary key table? VFP 8.0 and above have an INTEGER AUTOINC data type, which works the same as SQL Server's INDENTITY property.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[chriscboy]

Hi Mike,

I have moved removed all triggers and SPs, the insert works fine.

I have moved the system table back into the 'live' database and it is still causing a problem. I can only assume it is having a problem the the SP.

Do you know of any problems using SPs with OLEDB? I am using VFP7 at the moment.

Regards

Chris

 

[Mike Lewis]

Chris,

Do you know of any problems using SPs with OLEDB?

I don't know of any specific problem, except that there's a long list of commands and functions that are disallowed (see the Help topic, Unsupported Visual FoxPro Commands and Functions in OLE DB Provider). Then again, these are not the sort of language features you are likely to use in an SP.

My inclination would be to consider other methods of achieving your goal. For example, if you don't need to update the Foxpro table in real-time, you could get SQL Server to echo the updates to an XML file which you could then import into VFP. Or maybe create a COM object to do the update, which you could then call from a SQL Server extended SP. Those are just off the top of my head -- I'm not necessarily saying they would be any easier.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[chriscboy]

Hi,

I have made further progress with this, although it is not complete. It appears that OLEDB does not provide "proper" error messages as to why the insert fails. Further debugging has identified where the insert fails, rather than using messagebox to display "got here" I returned from the SPs to find the problem lines of code. It appears that you can still access other DBCs in the SPs whilst using OLDBB, but certain commands are causing the issue.

I am working hard to get this working, and believe it can be done. Once I have got the solution complete I will report back on my findings so that others can resolve this situation, if they come across it.

Thanks for the input so far. Watch this space!

 

[chriscboy]

Hi,

I have got further with this now. I can now insert records into our database. I have kept it simple by not accessing other dbcs via the SPs and it seems to be working.

I now have a problem with updating records! On trying an update on a test table, I keep getting the following error:

OLE DB provider "VFPOLEDB" for linked server "COMPANYDB" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 7333, Level 16, State 2, Line 34
Cannot fetch a row using a bookmark from OLE DB provider "VFPOLEDB" for linked server "COMPANYDB".

The T-SQL command I am using is :
Update [COMPANYDB]...[testdata] Set ccharfield = 'NEW'

If anyone could shed some light on this it would be much apprieciated !

Thanks

Chris