All
building SQL query in dynamic content / expression builder, I still find it hard.
Maybe someone can help.
This is in the dynamic content of a query in a Lookup activity, reading a SQLServer table
@markus.bohland@hotmail.de ('select * from dnb.selectie where prd_code = ', pipeline().parameters.p_product, ' and strt_dt <= ', activity('lk_hlp_report_period').output.firstRow.STRT_DT, ' and end_dt >= ', activity('lk_hlp_report_period').output.firstRow.END_DT)
prd-code = Varchar 10 defined in SQLServer and in the pipeline parameter as String. Value parameter 0010.
start-date and end-date are both defined as Date in SQLServer. They ares compared with the output of another Lookup (lk_hlp_report_period) that reads a table where dates are also stored as Date in SQLServer.
When I run the pipeline, this is the outcome
Input
{
"source": {
"type": "AzureSqlSource",
"sqlReaderQuery": "select * from dnb.selectie where prd_code = 0010 and strt_dt <= 2020-10-01T00:00:00 and end_dt >= 2021-04-01T00:00:00",
"queryTimeout": "02:00:00",
"partitionOption": "None"
},
"dataset": {
"referenceName": "ds_generic_ifp_modelled",
"type": "DatasetReference",
"parameters": {
"p_modelled_schema": "DML",
"p_modelled_table": "DNB_SELECTIE"
}
}
}
And this the error
Error details
Error code
2100
Troubleshooting guide
Failure type
User configuration issue
Details
Failure happened on 'Source' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Incorrect syntax near '00'. The label 'T00' has already been declared. Label names must be unique within a query batch or stored procedure.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Incorrect syntax near '00'. The label 'T00' has already been declared. Label names must be unique within a query batch or stored procedure.,Source=.Net SqlClient Data Provider,SqlErrorNumber=102,Class=15,ErrorCode=-2146232060,State=1,Errors=[{Class=15,Number=102,State=1,Message=Incorrect syntax near '00'.,},{Class=15,Number=132,State=1,Message=The label 'T00' has already been declared. Label names must be unique within a query batch or stored procedure.,},],'
Source
Pipeline
pl_dnb_process
What am I doing wrong?
How do I fix that 'where prod_code = 0010' becomes 'where prod_code = '0010'' ?? Since it is a varchar
How do I delete the time part of those data fields coming from the other Lookup, like 2020-10-01T00:00:00 ??
Hope someone can help and maybe point to some side / url / video were this is explained?
Regards
Ron