Technical and Installation Discussions

[JSousa]

As I am attempting to do some exporting to a ODBC SQL 2008 table using the ODBC connectivity function in the MAS 90 Visual intergrator I am recieving an error 15 and the message tells me I have Inncorrect Syntax near 'E' which is very unhelpful. I was wondering if there was a way to very the MAS 90 query that is running to do this as I am finding it nearly impossible to effectively debug without this knowledge.

[connex]

Only V/I import provides access to the SQL statement.  Have you tried initiating ODBC trace from the ODBC Administrator?

[JSousa]

I have the log file open and the only issue I could possible think of, since the Incorrect Syntax is near 'E' is when it tries to insert  E. as a part of the value into my address column. could the Period be causing the error?

[JSousa]

After turning on trace I can see the issue is that the constructed SQL statement does not put single quotes around the string values in the value part of the insert language, so a statement that should be

insert (a, b, c) values ('dog', 'cat', 'bird') is being sent as insert(a, b, c) values (dog, cat, bird)

do you know how to get VI to put quotes around text (string) values when it makes its odbc insert call?

[connex]

Can you use some other tool like MS Access or Crystal to extract MAS data?  It will also be must faster than V/I.

[JSousa]

No I am not able to do so as all data for my client is stored in MAS 90 and SQlL database and we are simply trying to make sure the SQL is updated regularly as MAS 90 is edited. Speed does not matter we are more concerned with accuracy in the data as third party programs access the data from the SQL faster than they would from access.

There has to be a way to concatenate the single quote around the value for each field as I do my export that is all I want to know.

[connex]

I don't think you'll find a way to fix VI except through Sage. 

Others who export to SQL server create a "linked server" and "stored procedure" see:

http://community.sagemas.com/t5/Technical-and-Installation/Using-Visual-Integrator-to-Update-E-Comme...

[JSousa]

I'm not really attempting to fix VI I'm just looking for a way to edit the fields on export or 780, which will likely grow but probably never exceed 20,000, records which I really cant see taking any longer than a few minutes.

As I can write the middle man code needed to do this with a console app. I was simply attempting to bypass that as the link to the ODBC connectivity is provided within MAS 90 and it seems that there has to be some script some where i can do with perform logic that would allow me to edit the data as it is exported since it is possible to do other things with the perform functionality through MAS 90.

I will look into this linked server and stored procedure but it seems a roundabout way to do something that should be as simple as saying. " ' " + {PR.EMPL:EMPLOYEE_NO} + " ' " in some script and putting it in some before assign logic item.

[JSousa]

I cannot create the linked server do to a mismatch between architecture and application error. and I can't change or add the MAS 90 datasource that was created due to a missing driver.

I will keep looking though