Changing MSSQL Data Connection
Is there a way to get smart connect to create this, or can I copy the structure from the test database?
I’ve found this was was due to a permission but I’m now getting the following error:
The variable name ‘@AirportId’ has already been declared. Variable names must be unique within a query batch or stored procedure
Chris, which version of SmartConnect are you using? Could you provide the source SQL Table definition?
I’ve found the problem was a bug in the old variables weren’t removed from the database. With help from support we figured out how to remove the correct rows and the problems was resolved.
Can you provide more information? I am having the same issue.
I’d suggest contacting support as it involved directly editing the database used by Smart Connect
I am getting the same error as I have made a duplicate of the default map which only did about half the fields I require. Is the solution to drop the sc_… table from the database that was generated for the default map?
I wrote a script that seems to find and fix these issues for you – make a SQL Backup of the SmartConnect database before you run this just in case!
/* –Patrick Roth, eOne Dec 2017
–couldn’t think of a way to do it in one shot, so took this approach
–find any duplicates per datasourceid & field name
–if you find any, delete the maximum by id you find for each
–check @@ROWCOUNT and loop and do again because i could have gotten myself
–into a situation where i have 3 duplicates ( i got that when i tried hard to force the error)
while @@ROWCOUNT > 0
delete from MssqlChangeTableField where MssqlChangeTableFieldId in (
select max(MssqlChangeTableFieldId ) from MssqlChangeTableField
group by MssqlChangeDataSourceId,FieldName having count(*) > 1)
/*–verify ok, look for any remaining duplicates (should be none) */
select MssqlChangeDataSourceId,FieldName from MssqlChangeTableField
group by MssqlChangeDataSourceId,FieldName having count(*) > 1
If you would like to submit an answer or comment, please sign in to the eOne portal.