Skip to content
+1-888-319-3663

COMMUNITY FORUM

Changing MSSQL Data Connection

Chris asked 5 years ago
I’m changing my maps from a test database to production and getting the following error: “connection could not be validated. Invalid object name ‘db.sc_gen_Airport_tracking_AIRPORTS'” as the sync tracking tables aren’t in live.
Is there a way to get smart connect to create this, or can I copy the structure from the test database?
Chris replied 5 years ago

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

Lorren Zemke Staff replied 5 years ago

Chris, which version of SmartConnect are you using? Could you provide the source SQL Table definition?

Chris replied 5 years ago

Hi Lorren,
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.
Thanks,
Chris

Jeanenne Turpin replied 5 years ago

Can you provide more information? I am having the same issue.

Chris replied 5 years ago

I’d suggest contacting support as it involved directly editing the database used by Smart Connect

Mark FRASER replied 2 years ago

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?

Answers
Patrick Roth Staff answered 10 months ago
The duplicate variables is a bug on that MSSQL Change connector that will happen if you try to switch anything about the connection – such as you would do if importing to a new system or duplicating it like you did here.

 
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) 
*/
begin
 select 1
 while @@ROWCOUNT > 0
 begin
    delete from MssqlChangeTableField where MssqlChangeTableFieldId in (
       select max(MssqlChangeTableFieldId ) from MssqlChangeTableField
       group by MssqlChangeDataSourceId,FieldName having count(*) > 1)
 end
 
end
/*–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.