Hi,

I am using Pervasive Sql version .... 9.60.016.000.

MY trigger looks as follows:

CREATE TRIGGER "MMSPO"

ON HistoryLines
AFTER INSERT
AS
BEGIN

SET NOCOUNT ON;
declare @documentnumber nvarchar(8);
declare @itemcode nvarchar(15);
declare @ddate Date;
declare @unitused nvarchar(4);
declare @description nvarchar(40);
declare @qty double;
declare @unitprice double;
declare @taxamt double;
declare @mms bit;
declare @buyout bit;
declare @budget bit;

select @documentnumber,@itemcode,@ddate,@unitused,@descri ption,@qty,@unitprice,@taxamt,0,0,0
from inserted where documentnumber like 'PN%' and itemcode like 'RM%' or itemcode like 'BO%';

INSERT INTO MmsPo (documentnumber,itemcode,ddate,unitused,descriptio n,qty,unitprice,taxamt,mms,buyout,budget)
values (@documentnumber,@itemcode,@ddate,@unitused,@descr iption,@qty,@unitprice,@taxamt,0,0,0);
END
GO

The error I am getting:

[LNA][PERVASIVE][ODBC Engine Interface]Syntax Error: CREATE TRIGGER<<???>> MMSPO
on HistoryLines
AFTER INSERT
AS BEGIN
SET NOCOUNT ON

Any Ideas??
Thanks

That looks like SQL Server syntax. PSQL does not use the same syntax for triggers or stored procedures.
Things like NOCOUNT don't exist in PSQL. The DECLARE statements are like:
DECLARE :ddate date;
Docs can be found at Pervasive's web site.

One very important thing to know about triggers in PSQL:
When you create a trigger, Btrieve access for that operation is disabled. For example, if you create an INSERT trigger on a table, a Btrieve based application will receive a status 149 which is defined as:

Thanks for the response. So actually you said that the trigger will give me errors via the Betrieve app.

We are using Pastel accounting.....any suggestions how to populate that temp table. Our Manufacturing App is written in VB.Net using a SQL db and I need the info to be available in this app. I did setup a linked server with views to the HistoryLines table, but somehow I started to get an error from the view that the Date, DateTime field is not valid.

OLE DB provider "MSDASQL" for linked server "Pioneer" returned message "[Pervasive][ODBC Client Interface]Invalid date, time or timestamp value.".
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "Pioneer".

Although, I do not use the DDate field in the view or any other Date, DateTime field.

When I Populate the MMSPO table in Pervasive, The view in SQL is working perfect.

Any Suggestions.
Thanks Again.

Just to make it more clear. I created a view from SQL and the Linked Server on the MMSPO table in Pervasive. The view on the Pervasive HistoryLines table is still giving me errors.

Thanks

If the trigger is going to give me trouble, I can always use a Stored Procedure that run once a day.

The only thing that I do not know at this stage, is how to fire the store procedure once a day. Can I fire it from a VB app, as I am having a background app running on the server, doing some work on the SQL db.

Thanks

You can fire a stored procedure from a VB app. Simply open an ODBC connection from the VB app to the PSQL database and execute the SP. Another option is a scheduled task using a command line tool like PvQuery.
As for the "invalid date..." error, it sounds like you've got a date, datetime, or timestamp field in the query and the value in the database is not valid. Usually this is caused by a Btrieve app that writes something like 0000-00-00 to the database which ODBC treats as invalid rather than null. Check your query, run it in the PCC, and see if it throws any errors. Check each of the fields to find out if one of them is a date, datetime, or timestamp and verify the data is valid for each record.

Thanks again for the help.

If I run the query in PCC, I am getting all the records. It is the query in my SQL view that give the error...even if I query it without all the dat, time and datetime fields...Strange.

I will work on the Stored Procedure and see where I end up.

Thanks again.

Hi,

I am running Pervasive version 9.6

I am totally lost with the Syntax of a SP..even when I looked at the manual you posted me before.

Please help me with the following SP:

CREATE PROCEDURE BuyOutPriceCheck

BEGIN
INSERT INTO MmsPo (documentnumber,itemcode,ddate,unitused,descriptio n,qty,unitprice,taxamt,mms,buyout,budget)
select documentnumber,itemcode,ddate,unitused,description ,qty,unitprice,taxamt,0,1,0
from historylines where documentnumber like 'PN%' and itemcode like 'BO%';
END;

I also need a similar SP that will do the same but where DDATE = Current Date.

The error I am getting is:
[LNA][Pervasive][ODBC Engine Interface][Data Record Manager][SPEng]3: 'BEGIN' Syntax Error.

Thanks again in advance.

This syntax created the SP for me:
I couldn't run it because I don't have the tables. Once you have the first SP, you should be able to create the second with your DDATE restriction.

Thanks a million...the syntax seems to be correct now, however I am getting an error when trying to save it:

The Application encountered a Permission error(Btrieve Error 94).
I do have access to everything...I can drop and create tables etc.

Where can I start digging now?

Thanks Again.

Which application? What do you mean "save it"?

Sorry,

I am in PCC, right click on Store Procedures, New.
Then I copy the code and click the Save Icon in PCC.

Then the error follows.

Thanks

The status 94 is a permission error. The engine doesn't believe you have rights to the files for some reason. Specifically, the files you need rights to are the DDF files (FILE.DDF, FIELD.DDF, PROC.DDF, etc).
Are you running these commands at the server or a client pointing to a remote server?
Does the database require a login? If so, are you using the Master username/password?

Ok, here is the complete picture.
Pastel Accounting is using the Pervasive DB that is on a server. The clients are loaded on each employee's pc that connects via the ethernet network.

I am working on the server without any client or server software.

I open PCC, without any username or password, expand Engines, Expand the Server where the db is (local server), Expand Databases, Expand the database where I want to store the SP, Right Click Stored Procedures, New, Store Procedure.

As I try to save the SP, I encounter the error. I can drop tables and create new tables without any problem. I also must admit that I know nothing about Pervasive.

If I can populate my temp Table "MMSPO", then I will work with it via SQL server's Linked Server and views.

Thanks so much for your answers so far.

I took a backup of the db after everyone was out...restored it on my machine, and the Stored Procedure worked like a charm.

It seems that I had a copy of the db while there was still user activity on it.

Thanks so much for all the help...you are an