DECLARE statement

[cooper_yonk]

Could anyone help me as after much googleing ive still not been able to get the below script to work...

I get an error (there are subsequent ones but this is the first one that pops up)

Its connecting to the database through ODBC connection.  Any help would be much appreciated.

OleDb error

Connector reply error: ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near ''.

SQL

DECLARE '$(@liststr)' NVARCHAR(MAX) = null;
SQL WITH [BespokeItem] AS
(
SELECT DISTINCT [BespokeDefinition] FROM qlikproject.[dbo].[BespokeFieldItems_VIEW]
)
SELECT '$(@liststr)' = COALESCE('$(@liststr)' +'],[','') + [BespokeDefinition]
FROM [BespokeItem]

DECLARE '$(@liststr2)' NVARCHAR(MAX) = null;
SQL WITH MAXfieldValues AS
(
SELECT DISTINCT [BespokeDefinition], [BespokeDefinition] AS FN FROM qlikproject.[dbo].[BespokeFieldItems_VIEW]
)
SELECT '$(@liststr2)' = COALESCE('$(@liststr2)','') +'],MAX([' + [BespokeDefinition] + ']) AS [' + [BespokeDefinition]
FROM MAXfieldValues ORDER BY [BespokeDefinition]

DECLARE '$(@sql)'
NVARCHAR(MAX) = N'SELECT [CommonRef],referralref, ' + SUBSTRING('$(@liststr2)',3, LEN('$(@liststr2)')) + ']' +
' FROM
(
SELECT [CommonRef], [ReferralRef], [BespokeDefinition],[BespokeItem]
FROM qlikproject.[dbo].[BespokeFieldItems_VIEW] where BespokeItem is not null and commonref = 101847733
) a
PIVOT
(
MAX([BespokeItem])
FOR [BespokeDefinition] IN ([' + '$(@liststr)' + '] )) AS pvt
GROUP BY commonref,referralref
'

Exec sp_executesql '$(@sql)'

[NadiaB · Accepted solution]

Hi @cooper_yonk 

This is non Qlik related issue, my suggestions would be the following:

1. To rule out that there is a problem with the driver, user the SQL Management Studio tool to run the script and see if its successful there. 

2. If the script fails it probably show a line where is failing, if not, copy sections of the script from the top to the bottom until you get the error to see at what point fails. 

Kind Regards.