SC 2015 – Can't Delete Map, Save Record Error
Ryan Connolly asked 6 years ago
Any help would be appreciated.
Ryan
Marc answered 6 years ago
Ryan replied 6 years ago Same here, I am able to use duplicated maps and also follow a certain naming convention.
Brady Swenson answered 6 years ago
Ryan and Marc,
Can you tell us which version of SmartConnect you have installed and which build number you came from?
There was a release of SmartConnect that had known issues with a System Permissions table, which are similar to the issues you have described.
There is a new release available on our website, which we would recommend you install and upgrade to address those issues.
http://www.eonesolutions.com/smartconnect/downloads/
Marc replied 6 years ago Version 20.15.0.51 is where I started encountering the issues. I’m not exactly sure what version of 2014 I was running before, but I typically upgrade every couple months so it wasn’t very old.
Ryan answered 6 years ago
Brady Swenson answered 6 years ago
I’m beginning to wonder if you have a lot of data saved in your SmartConnect Log tables, which could be related to the issues you are seeing when trying to delete a map.
Here are some queries you can run to review your SmartConnect Log tables.
SELECT COUNT (*) FROM SmartConnect..LogMapDetail
SELECT COUNT (*) FROM SmartConnect..LogDataTable
SELECT COUNT (*) FROM SmartConnect..LogDocumentDetail
SELECT COUNT (*) FROM SmartConnect..LogMapTaskDetail
SELECT COUNT (*) FROM SmartConnect..LogDocumentTaskDetail
SELECT COUNT (*) FROM SmartConnect..LogDocumentTaskDetail
SELECT COUNT (*) FROM SmartConnect..LogDocumentDetail
SELECT COUNT (*) FROM SmartConnect..LogDocumentTask
SELECT COUNT (*) FROM SmartConnect..LogDocumentDetail
SELECT COUNT (*) FROM SmartConnect..LogDocument
SELECT COUNT (*) FROM SmartConnect..LogMapTask
SELECT COUNT (*) FROM SmartConnect..LogDocument
SELECT COUNT (*) FROM SmartConnect..LogMaster
SELECT COUNT (*) FROM SmartConnect..TraceLine
SELECT COUNT (*) FROM SmartConnect..TraceHeader
SELECT COUNT (*) FROM SmartConnect..ProcessErrors
SELECT COUNT (*) FROM SmartConnect..LogEventLog
If you return a lot of data from these queries, one suggestion we’d have for you is to clear out these tables.
Below are the scripts to remove everything, except for the last 14 days, from the SmartConnect Log tables. We’d recommend running this in test environment or ensure you have a backup of your SmartConnect database before proceeding.
If you require any assistance with running these scripts, then we’d ask that you submit a support request to support@eonesolutions.com.
delete b
FROM SmartConnect..LogMapDetail b
JOIN SmartConnect..LogMaster a ON b.LogMasterId = a.LogMasterId
WHERE a.RunDate < GETDATE() – 14
delete b
FROM SmartConnect..LogDataTable b
JOIN SmartConnect..LogMaster a ON b.LogMasterId = a.LogMasterId
WHERE a.RunDate < GETDATE() – 14
delete b
FROM SmartConnect..LogMaster a
JOIN SmartConnect..LogDocument c ON c.LogMasterId = a.LogMasterId
JOIN SmartConnect..LogDocumentDetail b ON b.LogDocumentDetailId = c.LogDocumentId
WHERE a.RunDate < GETDATE() – 14
delete b
FROM SmartConnect..LogMaster a
JOIN SmartConnect..LogDocument c ON c.LogMasterId = a.LogMasterId
JOIN SmartConnect..LogDocumentTask b ON b.LogDocumentId = c.LogDocumentId
WHERE a.RunDate < GETDATE() – 14
delete b
FROM SmartConnect..LogMaster a
JOIN SmartConnect..LogDocument c ON c.LogMasterId = a.LogMasterId
JOIN SmartConnect..LogDocumentTask d ON d.LogDocumentId = c.LogDocumentId
JOIN SmartConnect..LogDocumentTaskDetail b ON b.LogDocumentTaskId = d.LogDocumentTaskId
WHERE a.RunDate < GETDATE() – 14
delete b
FROM SmartConnect..LogDocument b
JOIN SmartConnect..LogMaster a ON b.LogMasterId = a.LogMasterId
WHERE a.RunDate < GETDATE() – 14
delete b
FROM SmartConnect..LogMaster a
JOIN SmartConnect..LogMapTask c ON c.LogMasterId = a.LogMasterId
JOIN SmartConnect..LogMapTaskDetail b ON b.LogMapTaskId = c.LogMapTaskId
WHERE a.RunDate < GETDATE() – 14
delete b
FROM SmartConnect..LogMapTask b
JOIN SmartConnect..LogMaster a ON b.LogMasterId = a.LogMasterId
WHERE a.RunDate < GETDATE() – 14
delete a
FROM SmartConnect..LogMaster a WHERE a.RunDate < GETDATE()-14
DELETE b
FROM SmartConnect..TraceLine b
JOIN SmartConnect..TraceHeader a ON b.TraceHeaderId = a.TraceHeaderId
WHERE a.EndDate < GETDATE() – 14
DELETE a
FROM SmartConnect..TraceHeader a WHERE a.EndDate < GETDATE()-14
Marc replied 6 years ago I ran the queries you provided and only had minimal record is 4 tables:
TraceLine (13)
TraceHeader (18)
ProcessErrors (4)
LogEventLog (652)
I ran the delete scripts you provided but none of the records in TraceLine and TraceHeader were older than 14 days and there were no delete scripts provided for the ProcessErrors and LogEventLog tables.
Devin McMahon answered 6 years ago
If I try to delete the record I receive “Failed to delete map: Transaction not connected or was disconnected”
Essentially, I can do nothing with these maps. I cannot re-import them, I cannot delete them, or modify them.
Again – this symptom only pertains to maps connected to a GP Change Data Source. I have already run SmartConnect maintenance on the GP company to which these maps are connected
Devin McMahon replied 6 years ago Copying the map resolves the issue for the copied map, but you are still left with the old maps that you cannot delete, or edit.
I dont think this has anything to do with excess log records, it is something that occurs with maps connected to the GP Change Data Source
Marc replied 6 years ago I ran a SQL profile on the delete operation which showed that the only thing getting called in sql is a delete stored procedure with an argument identifying the map to delete. I ran that procedure manually in SSMS and debugged it and found that it got all the way to the last step before hitting an error. But even though it hit an error, the map was successfully deleted inside SmartConnect.
So it appears this is an issue of the program not being able to properly handle an error returned from the SQL sproc during the delete process. But running the sql sproc manually takes care of the issue and allows us to move on.
Devin McMahon replied 6 years ago would you be willing to share the name of that Stored Procedure, so I can avoid having to profile my environment to find it?
Lorren Zemke Staff replied 6 years ago Devin, there is only one stored procedure in the SmartConnect database. E1_SCDeleteMap.
Marc, what was the error message you received when you ran it using SSMS. We can get a bug written for handling that error.
Marc replied 6 years ago It the only one in the db which you can execute like this in SQL Management Studio:
exec dbo.E1_SCDeleteMap ‘
Marc replied 6 years ago Sorry Devin. I had all the details and pasted it into a comment in this discussion last week, but I don’t see it here today so it must not have saved. I don’t have the details anymore but the error was literally from the last line in the sproc. I think it stated that the foreign key or constraint already existed or something along those lines.
Devin McMahon replied 6 years ago Lorren – thank you. I have stayed out of the SmartConnect database for the most part, so I did not realize. I will attempt this on our test environment and report the results
Devin McMahon replied 6 years ago I will forward the detailed error messages to Lorren when I perform my tests. Thank you
Jeanenne Turpin answered 5 years ago
Jeanenne Turpin replied 5 years ago this is what I get when I run the stored procedure in SSMS
Msg 208, Level 16, State 1, Procedure E1_SCDeleteMap, Line 51 [Batch Start Line 2]
Invalid object name ‘MsCrm2011BulkDestination’.
Sherry Whitten answered 2 years ago
–These four return results
SELECT COUNT (*) FROM SmartConnect..TraceLine
–984,306
SELECT COUNT (*) FROM SmartConnect..TraceHeader
–233,007
SELECT COUNT (*) FROM SmartConnect..ProcessErrors
–469
SELECT COUNT (*) FROM SmartConnect..LogEventLog
–935,160
Sherry Whitten replied 2 years ago Our Log Maintenance is set to Keep logs for 15 days. The eOne SmartConnect service is running on this server. But when I deleted logs older than two weeks, they were much older than 15 days; many of them went back to 2018. Are the logs above managed by the Log Maintenance/Keep Logs setting?