July 9, 2015 at 2:30 pm
I am attempting to create a snapshot replication publication.
When the snapshot is generated, any table that consists of all numeric columns creates a script with SET ANSI PADDING OFF
I googled around a bit and found that if a DDL trigger is in place on the database, it will cause this to happen.
I dropped the triggers, re-created the publication and subscription, same thing.
I need to either find a solution or a work around to this issue.
Any ideas???
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 9, 2015 at 3:21 pm
Michael L John (7/9/2015)
I am attempting to create a snapshot replication publication.When the snapshot is generated, any table that consists of all numeric columns creates a script with SET ANSI PADDING OFF
I googled around a bit and found that if a DDL trigger is in place on the database, it will cause this to happen.
I dropped the triggers, re-created the publication and subscription, same thing.
I need to either find a solution or a work around to this issue.
Any ideas???
While I know nothing about this, I was just curious if your tables themselves had that ansi padding set? I know I've seen some table definitions that apparently had columns added with different ansi padding settings, and I'm guessing that it was altered from a session that simply had a different setting in that connection.
Maybe script a "create table" from ssms and see if any columns in that table do that?
edit: also, you should make the url in your signature clickable like it is in my reply. It keeps superficial people like me from judging you LOLOL ok I wasn't really serious but you should make it clickable so more people will actually read the article.
July 9, 2015 at 4:18 pm
While I know nothing about this, I was just curious if your tables themselves had that ansi padding set? I know I've seen some table definitions that apparently had columns added with different ansi padding settings, and I'm guessing that it was altered from a session that simply had a different setting in that connection.
Been down that road. I actually dropped and re-created tables and explicitly set ansi padding on.
Same result.
edit: also, you should make the url in your signature clickable like it is in my reply. It keeps superficial people like me from judging you LOLOL ok I wasn't really serious but you should make it clickable so more people will actually read the article.
I'm technically challenged at times!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 10, 2015 at 6:54 am
Michael L John (7/9/2015)
While I know nothing about this, I was just curious if your tables themselves had that ansi padding set? I know I've seen some table definitions that apparently had columns added with different ansi padding settings, and I'm guessing that it was altered from a session that simply had a different setting in that connection.
Been down that road. I actually dropped and re-created tables and explicitly set ansi padding on.
Same result.
edit: also, you should make the url in your signature clickable like it is in my reply. It keeps superficial people like me from judging you LOLOL ok I wasn't really serious but you should make it clickable so more people will actually read the article.
I'm technically challenged at times!
Nah you got the link working, its just a "tick" of mine to point things out like this, but I'll work on it!
Looks like your situation is known and marked as "won't fix" (if I'm correct in identifying this)
July 10, 2015 at 7:51 am
I've been down that road also!
If you keep going on this thread, as well as others, the "answer" is to drop the trigger, drop the publication and subscription, and re-do everything leaving the trigger off.
That did not work. I went through 20 different sets of steps.
The second work around was to set the publication to not build the schema. That's what I did.
The problem is that this is a dev database, so changes occur regularly. The replication was to transfer the data to a testing reporting database. The dev's needs the same data in both places to test any new reports.
Backup and restore will not work. The reporting database is only a subset of the applicaiton database. Plus, the indexes, procedures, etc. are completely different.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 10, 2015 at 8:27 am
Michael L John (7/10/2015)
I've been down that road also!If you keep going on this thread, as well as others, the "answer" is to drop the trigger, drop the publication and subscription, and re-do everything leaving the trigger off.
That did not work. I went through 20 different sets of steps.
The second work around was to set the publication to not build the schema. That's what I did.
The problem is that this is a dev database, so changes occur regularly. The replication was to transfer the data to a testing reporting database. The dev's needs the same data in both places to test any new reports.
Backup and restore will not work. The reporting database is only a subset of the applicaiton database. Plus, the indexes, procedures, etc. are completely different.
Gotcha! sorry to be of no help here!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply