Unanswered

Salesforce to SQL Server 2005 Datetime Error

(0) Share

Report

Posted on by BarryHicks

I am trying to updte a SQL Server table when a new record is inserted into Salesforce. The flow works perfectly but the datetime values in Salesforce create a "Type datetime2 is not a defined system type" error. I undersand this is because SQL Server 2005 does not support the datetime2 datatype. To get around this I tried passing the datetime as a text field then using an After Insert trigger to update the datetime fields in the record from the text field. However now I get a "the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause" error.

Two questions:

1) Anyone know how to get around the datetime2 issue in SQL Server 2005?

2) Is there a way to use the flow to update records in a table with a trigger enabled?

Grateful for any ideas.

Barry

Categories:

Using connectors

I have the same question (0)

All responses (3)

Answers (0)

v-micsh-msft on at

Like (0)

Report

Hi BarryHicks,

Please take a try to format the data time value with the formatDateTime function:

"@formatDateTime(Datavalue,'yyyy-MM-dd hh:mm:ss')"

Function reference:

WorkFlow Definition Language

Please post back if there are any syntax errors, with a screenshot, I will then take a further testing based on that.

For the trigger errors, no, currnetly there is no workaround to deal with that part.

Regards

Was this reply helpful? Yes No
BarryHicks 2 on at

Like (0)

Report

Thanks for the quick response. I added the formula @formatDateTime('2016-12-15','yyyy-MM-dd hh:mm:ss') to my SQL Server Insert Row date field and I get an error "We cannot convert the value \" @formatDateTime('201...\" to type DateTime."

I can't figure out how to upload a screenshot. Do I need permission to upload images?

Thanks again.

Was this reply helpful? Yes No
v-micsh-msft on at

Like (0)

Report

Hi @BarryHicks,

Please see the following image:

Click the Photos, then it would allow you to select image to upload. From the error message, it seems the data didn't convert to the right format.

Try to change the formula with the following:

"@{formatDateTime('2016-12-15','yyyy-MM-dd hh:mm:ss')}"

If this still not working, take use of the compose action, then in the inputs, type the formula below:

"@formatDateTime('2016-12-15','yyyy-MM-dd hh:mm:ss')"

After that, use the output from the compose to insert the corresponding rows in SQL server.

Regards

Was this reply helpful? Yes No