Copy Job On-Prem SQL Server Error: 'FORMAT' is not a recognized built-in function name. [Moved Post]

[jisaac]

Hello,

I was trying out the Copy Job (Preview) item as what it is meant to be will be very useful for me. I attempted to pull data from a SQL Server running an older version. I was greeted with this error:

Failure happened on 'Source' side.

ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: ''FORMAT' is not a recognized built-in function name.',Source=,''Type=System.Data.SqlClient.SqlException,Message='FORMAT' is not a recognized built-in function name.,Source=.Net SqlClient Data Provider,SqlErrorNumber=195,Class=15,ErrorCode=-2146232060,State=10,Errors=[{Class=15,Number=195,State=10,Message='FORMAT' is not a recognized built-in function name.,},],'

According to this article, the 'FORMAT' function was introduced in SQL Server 2012. My server is running a version prior to that. Is there anyway to work around this to allow the Copy Job to work?

Thanks,

Jacob

[This is a repost from another section. Someone asked me to post it here instead.]

[jisaac]

Still looking for a solution to this. Is it possible to create my own FORMAT function on the sys schema that Copy Job will call?

[FabianSchut]

Hi @jisaac,

Could you try to use the Copy Activity within the Data Pipeline instead of the Copy Job? I'm not sure whether Copy Activity does not use FORMAT, but you could try that out.

[jisaac]

Hi @FabianSchut,

Copy activity from pipelines work on my SQL Server 2008 R2 database, yes. I have been using them for several months. I was interested in the automatic Incremental Loading feature of the Copy Job, so I'm hoping to switch if I can get it to work.

(I already have an incremental loading system working with Notebooks, but it's rather clunky.)

[lbendlin]

FORMAT function not working in sql server 2008 R2 - Stack Overflow

[jisaac]

Thanks for your response, but this is not helpful. I am not the one making the FORMAT call, it is the Copy Job doing it natively. I'm asking if I can change Copy Job to not use that function call, or if there is another work around.

Also, I already linked a similar post in the original post.

[lbendlin]

I don't think Microsoft will want to support that legacy setup.

[jisaac]

Then is there a workaround I could do on my end?

[lbendlin]

you mean, apart from upgrading your database?

[jisaac]

Yes, of course. The program using the SQL server is being replaced, so there's no benefit in upgrading the SQL server version. I also don't know if the program would support the newer version of SQL server. After a year or two, data will stop changing on the program as we'd be completely moved over to the new program. Until then, I need to pull data incrementally from both systems.