Changing MSSQL Data Connection
Chris asked 7 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?
Is there a way to get smart connect to create this, or can I copy the structure from the test database?
Answers
Patrick Roth Staff answered 3 years 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
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
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?
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
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?