Settings and activity
4 results found
-
19 votes
jgonnering
supported this idea
·
-
108 votesjgonnering
supported this idea
·
An error occurred while saving the comment -
7 votes
An error occurred while saving the comment jgonnering
commented
this feature has been available since 7.0 (August 2015). Reference Actions List section of following link: https://documentation.red-gate.com/display/SP7/SQL+Prompt+7.0+release+notes
An error occurred while saving the comment jgonnering
commented
I have to use the spreadsheet method that jbelina mentions, but that is very tedious and requires I leave SSMS. It would be great if I could paste in a list that I copy out of an email, ticket system, etc., and then use Format SQL (CTRL K + CTRL Y) to insert commas to separate them and use the column's data type for the IN clause to determine whether to wrap them in single quotes (date, char data) or not (int data). I do this many times a day and this would be an awesome timesaver. Thanks
-
168 votesjgonnering
supported this idea
·
An error occurred while saving the comment jgonnering
commented
(copying this from a different post, essentially what Russell S. posted, but not hard-coding dbo schema)
a relatively easy way to accomplish this would be to run the TSQL script below and modify the WHERE clause as appropriate, then save a SQL Data compare to a file, open the .sdc file and paste the xml generated by sql script anywhere inside the <TableActions> node. In my case, I want to unmap all columns that begin with "Audit"SELECT '<value version="1" type="SelectTableEvent">
<action>UnmapColumns</action>
<ColumnName>' + [c].[name] + ':' + [c].[name] + '</ColumnName>
<TableName>[' + OBJECT_SCHEMA_NAME([c].[object_id]) + '].[' + OBJECT_NAME([c].[object_id]) + ']:[' + OBJECT_SCHEMA_NAME([c].[object_id]) + '].['
+ OBJECT_NAME([c].[object_id]) + ']</TableName>
</value>' AS [XML]
FROM [sys].[columns] AS [c]
WHERE OBJECT_SCHEMA_NAME([c].[object_id]) NOT IN ('dbo', 'sys')
AND [c].[name] LIKE 'audit%';the biggest problem in doing this is when you go to deploy, the columns that are not mapped are not included in the insert statement which is causing "cannot insert null value into ..." errors. What we really need is a way to ignore certain columns on the compare, but include them on the insert statements when deploying.
replicated tables at the subscriber is a good example. Should be able to ignore default constraints regardless of scenario. They are constraints like foreign keys, and there is currently an option to ignore foreign keys.