Why SQL queries are not saving in agentry editor for first publish?

[sravanthi_polu]

Hello Experts,

     In the process of developing an agentry application i have defined SQL steps and saved in agentry editor with out any errors .When i have published definations for first time it is displaying error 'Invalid object some where' in back end SQL log file ,when i have seen in editor some times the query is not saved,some times even it is defined then also the same  error.So i have copied the steps and saved again.Now i have done a publish,it is working fine.This is happening only when i implement exchange mechanism(Showing error in ATE getfetch error).But with out exchange mechanism  it is working fine.Is this related to the installation of editor plig-in is not correct?. I have done preferences setting in my workspace correctly.

          Help me in this regared.Thanks in advance.

Thanks & Regard,

Sravanthi Polu

[mark_pe · Accepted solution]

Sravanthi,

For your error: displaying error 'Invalid object some where' in back end SQL log file.. This is happening only when i implement exchange mechanism(Showing error in ATE getfetch error)

Two suggestions (My best suggestion is item 2 below - I think that is where you have the error)

Item 1: Are you using the right encoding? In your Eclipse Editor under the main menu Windows - Preference -> General -> Workspace. What is your Text file encoding set to? Cp1252? Try setting it to UTF-16.

There is a tutorial about this in:

https://service.sap.com/sap/bc/bsp/spn/esa_redirect/index.htm?gotocourse=X&courseid=70246375 (Enterprise Academy classes). The course is really a simple < 5 minutes discussion on different types of encoding and why they exist and how to change the Eclipse to what you wanted.  SQL is normally done in UTF-16 for 16 bit.

I am assuming that the encoding could be wrong.

Item 2: Another thing that could be wrong is how you specify the SDML (Flunky code). 

In your SQL Script: Anything that has the "<<" and ">>" open and close marker is an SDML (Data Markup Language) and needs to be pre-populated before the Agentry Server submits your SQL query to the backend. In essence, this is use to populate the SQL query with predefine information like your userID or location and when the SQL gets process it will populate the script with the correct information.

So if you are not properly using the flunky (ex: <<object.address>> where address is not a key ID in a Fetch step (Check manual for usage of SDML AgentryLanguageReference_6.0.0.0.pdf) then you may get invalid object.

When you do a SQL "Fetch" Client Exchange and Server Exchange Steps, you can select a few options:

For this discussion let us talk about these 2 items below.

1. Run Attribute: Run One Time

or

2. Run Attribute: Run Once Per Object.

When you select Run Once Per Object the only available Flunky or SDML commands you have are:

object.keyPropertyName

lastUpdate

fetch.messageNumber

The 3 above are your only options

If you try to do something like object.property1 where property1 is part of your object properties then it will not work for Run Once Per Object.

It may work for Run One time as fetch.propertyname.

From your symptoms where you are complaining about an issue in exchange steps, your error is a typo or a misused or a misunderstanding of how fetches and SDML works (documented in page 398 or 399 of AgentryLanguageReference_6.0.0.0.pdf).

See pictures just in case you don't have access to that pdf file (The picture below simply proves what SDML options is available for your Fetch Client Exchange Step).

Hope this helps.

Regards,

Mark Pe

SAP Senior Product Support Engineer

[sravanthi_polu · Accepted solution]

Hello All,

Thanks for your reply's.Some scripts iam using are as follows

DeleteCustomerExchange.sql

DELETE FROM

SycloCustomersExchange

WHERE

UserID = '<<user.agentryID>>' AND

ServerID = '<<server.serialNumber>>'

FindNewCustomers.sql

INSERT INTO

SycloCustomersExchange

(CustomerID, UserID, ServerID, Action, LastUpdate)

SELECT

C.CustomerID,

'<<user.agentryID>>',

'<<server.serialNumber>>',

'R',

GETDATE()

FROM

Customers C

WHERE

NOT EXISTS

(SELECT

EX.CustomerID

FROM

SycloCustomersExchange EX

WHERE

EX.ServerID = '<<server.serialNumber>>'AND

EX.CustomerID = C.CustomerID AND

EX.UserID = '<<user.agentryID>>')

I have Stopped and restarted the server even then it is not working.My eclipse workspace preferences are set to UTF-16.In my fetch definitions all steps RUN attribute is set correct.After i have done a publish  to server for first time,it showing error in ATE 'GetCustomer' fetch and in back end SQL steps log file

"GetCustomers" ClientExchange "DeleteCustomerExchange1"

          SELECT something FROM somewhere

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'somewhere'.

42000 [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Now i have copy and pasted the 'Delete Customer Exchange query' again.Now after i have published,it is showing error in 'FindNewCustomer',Again after publish, 'Find Updated Customers' similarly for all steps.After i have copied all steps second time iam getting the desired output.

I think it is recognizing the script after the second publish.

Thanks & Regards

    Sravanthi

[mark_pe · Accepted solution]

Sravanthi,

From your error:

GetCustomers" ClientExchange "DeleteCustomerExchange1"

          SELECT something FROM somewhere

This is an indication that during your testing or publishing it is still using a template script wherein whenever you add a new SQL it will be shown as "Select something from somewhere" this is just a example template that you need to add your query in it.  It signifies taht you are adding a new query.  In practice you need to modify this with your current values.  For some reason your data is not getting saved and it is using the old values (or template values). 

Also look at the name of it. It is "DeleteCustomerExchange1".  It has a 1 at the end. The scripts you added above is called "DeleteCustomerExchange.sql" So there is some discrepancy on what the server is running versus what you are expecting.

Suggestions:

1) You may need to make sure to press the save button every time you enter your values and before publishing.  There is a small save icon (picture of a disk) that you need to save.  This is important. You need to find that really tiny save button. Then publish.

2) Once you saved it then go into the Server directory ServerDev and look for Applications/Development/Scripts. In this directory you will have your scripts or the module names that contains your scripts.  Find the SQL script that you saved from item 1. Does it have your changes?

If Yes, you will need to restart your Agentry Server to make sure that the Agentry Server reloads the correct one.

One other technique that I found is sometimes you may need to close your Windows Explorer window (open a new one) before starting the Agentry Server. I think it may be holding on to old values but I can't remember when I needed to do it.

3) Clear the client (remove all the data in it).

4) Once you have started the Agentry server and cleared the ATE or PDA client transmit again and see if this helps you out.

Hope this helps you out.

Regards,

Mark Pe

SAP Senior Support Engineer

[Former Member · Accepted solution]

Question are you using the stand-alone Agentry Server or SMP 2.3 Server?

If you are using the SMP server, please restart the whole SMP server and not just the Agentry Server.

[jason_latko]

Sravanthi,

After creating a new Step (SQL or Java or XML) in the Eclipse Editor and publishing to your server, always stop and restart the server for these scripts to be recognized.

Jason Latko - Senior Product Developer at SAP

[Former Member]

Can you please post a copy of the script in the editor, and also, what error you are seeing in the log files.

[sravanthi_polu]

Hello,

          Thanks for the reply. Iam using the same scripts defined in 'Agentry Self Study Tutorial'.

It is showing error in backend log files as 'Invalid Object Name Somewhere' query could not be prepared.

Thanks & Regards

    Sravanthi

[Former Member]

There are a lot of scripts in the tutorial.  To better understand your issue we need to see what script you are using, and what the what the error is in the log files.

Is the script "InsertCustoemrExchange"?  If so, do you have the step defined in the"Client Exchange Steps" section of the fetch, and also the run is set to "Run Once per Object".