AnsweredAssumed Answered

PSOS Scripting Error Glitch?

Question asked by jmcintyre on May 3, 2018


Hey all,

 

having some problems with PSOS and ODBC. Been fighting it for about our week causing tons of manual entries and errors.

Backstory

We have a SQL database for our accounting software, and Filemaker 16 as our FM. Everything is up to date. Server is called from Schedule. Action is set to go through FM records and update or insert them into the proper SQL fields.

 

Issue

For about a week now, its tripping crazy unrelated errors. It keeps failing out saying Cant convert TK-12345 to a int. Which is obvious. However, the script that is running isnt doing anything with the TK field. It kind of worked when we rebooted the server, however shortly after, it failed again. The crazier thing, If you run it on a client, it runs no problem, But as soon as you PSOS (from schedule or client) it trips the weird not possible errors.

 

 

Table Info in SQL

 

   

Column NameData typeMax Lengthprecisionscaleis_nullablePrimary Key
UDIC_UIDvarchar320001
CustomCurrencyCodevarchar30010
CreateUservarchar200010
CreateDatedatetime823310
ModUservarchar200010
ModDatedatetime823310
CustNumbervarchar200010
CustNamevarchar2550010
CustLastNamevarchar2550010
CustMiddleNamevarchar2550010
CustPreferredNamevarchar2550010
CustPrefixvarchar2550010
CustSuffixvarchar2550010
CustAddress1varchar2550010
CustAddress2varchar2550010
CustCityvarchar2550010
CustStatevarchar2550010
CustZipcodevarchar2550010
CustCountryvarchar2550010
Custphone_homevarchar2550010
Custphone_businessvarchar2550010
Custphone_cellvarchar2550010
Custphone_faxvarchar2550010
Custaeiofficevarchar2550010
Custemailbusinessvarchar2550010
Custemailpersonalvarchar2550010
Custssnvarchar2550010
Custtaxidvarchar2550010
Custdatequalpackagesentvarchar2550010
Custdatequalpackagerecvarchar2550010
Custcpmvarchar10010
Custpcavarchar10010
Custdcrvarchar10010
Custpmlselvarchar10010
Custcostsegvarchar10010
Custaltavarchar10010
Custzonevarchar10010
Custroofvarchar10010
Custmepvarchar10010
Custliftsvarchar10010
Custnepavarchar10010
Custshpovarchar10010
Custasbestosvarchar10010
Custleadvarchar10010
Custradonvarchar10010
Custmoldvarchar10010
CustphaseIIesavarchar10010
Custesavarchar10010
Custparcelvarchar2550010
Custftpaccountvarchar2550010
Custinsurancevarchar2550010
Custaherabivarchar10010
Custaherampvarchar10010
Custstateacmvarchar10010
Custmoldregvarchar10010
Custlbpvarchar10010
Custtermitevarchar10010
Custcihvarchar10010
Custpevarchar10010
Custsevarchar10010
Custmevarchar10010
Custravarchar10010
Custrgvarchar10010
Custgcvarchar10010
Custreavarchar10010
Custrepvarchar10010
Custremvarchar10010
Custcemvarchar10010
Custleedapvarchar10010
Custchmmvarchar10010
Custosha40hourvarchar10010
CustLatitudevarchar2550010
CustLongitudevarchar2550010
CustAveragedecimal919500
CustAverageTechnicalMeritdecimal919500
CustAverageProjectManagementandCommunicationdecimal919500
CustAverageReportPresentationdecimal919500
CustAverageTotaldecimal919500
CustLastdecimal919500
CustLastTechnicalMeritdecimal919500
CustLastProjectManagementandCommunicationdecimal919500
CustLastReportPresentationdecimal919500
CustLastTotaldecimal919500
CustCompanyvarchar2550010
CustFirstNamevarchar2550010
CustEntryDatedatetime823310
CustApprovalDatedatetime823310
CustVendorvarchar200010
CustStatusvarchar2550010

 

 

Update Query being used

Update UDIC_AC set CustFirstName = 'text', CustAddress1 = 'text and num', Custasbestos = 'num', CustCity = 'text', CustCountry = 'text', Custcpm = 'num', Custemailbusiness = 'text and char', Custesa = 'num', Custftpaccount = 'text', CustLastName = 'text', CustLatitude = 'dec', Custlead = 'num', CustLongitude = 'dec', Custmold = 'num', CustName = 'text', CustNumber = 'num', CustphaseIIesa = 'num', Custphone_business = 'text and char ', Custphone_cell = 'text and char  ', Custphone_home = 'text and char  ', Custradon = 'num', CustState = 'text', CustZipcode = 'num' where UDIC_UID = 'num'

 

Insert Query being used

Insert into UDIC_AC(CustFirstName, UDIC_UID, CustAddress1, Custasbestos, CustCity, CustCountry, Custcpm, Custemailbusiness, Custesa, Custftpaccount, CustLastName, CustLatitude, Custlead, CustLongitude, Custmold, CustName, CustNumber, CustphaseIIesa, Custphone_business, Custphone_cell, Custphone_home, Custradon, CustState, CustZipcode, CustAverageTotal, CustLast, CustLastTechnicalMerit, CustLastProjectManagementandCommunication, CustLastReportPresentation, CustLastTotal) values('text','num','text and num','num','text','text','num','text ','num','text','text','dec','num','dec','num','text','num','num','text and char ','text and char ','text and char ','num','text','num',(num),(num),(num),(num),(num),(num))

 

Errors (same for every record even tho TK-123 is a ID field)

 

Screen Shot 2018-05-03 at 2.42.06 PM.png

Outcomes