I am using Transactional replication and from time to time I get an Invalid
column error on the distribution agent for either a stored procedure or view.
When I take a look at the table definition script I see that it is missing
the newest columns that were added. Does anyone else have this problem?
Does anyone know what causes this and how to fix it? The only fix I've found
so far is to drop the table from replication and add it again and produce a
new snapshot and then it seems to see the new columns.
Where are these extra columns? On the Publisher or subscriber?
It sounds like they are on the subscriber, which means someone is changing
the schema there. Make schema changes on the publisher using
sp_repladdcolumn or sp_repldropcolumn.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> I am using Transactional replication and from time to time I get an
Invalid
> column error on the distribution agent for either a stored procedure or
view.
> When I take a look at the table definition script I see that it is
missing
> the newest columns that were added. Does anyone else have this problem?
> Does anyone know what causes this and how to fix it? The only fix I've
found
> so far is to drop the table from replication and add it again and produce
a
> new snapshot and then it seems to see the new columns.
|||The Extra columns are ones we added with scripts to the publisher database.
But then when we push a snapshot those new columns are not getting scripted.
I'm not sure why the replication script generator would generate the scripts
any differently than when you use Enterprise manager's script generating
tools, but they are not working the same.
"Hilary Cotter" wrote:
> Where are these extra columns? On the Publisher or subscriber?
> It sounds like they are on the subscriber, which means someone is changing
> the schema there. Make schema changes on the publisher using
> sp_repladdcolumn or sp_repldropcolumn.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> Invalid
> view.
> missing
> found
> a
>
>
|||Reply at bottom.
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...[vbcol=seagreen]
> The Extra columns are ones we added with scripts to the publisher
> database.
> But then when we push a snapshot those new columns are not getting
> scripted.
> I'm not sure why the replication script generator would generate the
> scripts
> any differently than when you use Enterprise manager's script generating
> tools, but they are not working the same.
> "Hilary Cotter" wrote:
Don't you have to mark the replication for reinitialistion prior to creating
the new snapshot? I'm still a beginner on the replication side (well, most
of SQL Server :P), but whenever I make changes to publication properties the
EM dialog always points out that the publication has to be reinitialised, so
I'd assume that for the snapshot agent to pick up changes to the schema the
same thing would need to be done.
Dan
|||Yes, you do have to reinitialize and I am doing that as well. Basically we
drop the article (table) from both the subscription and publication, then I
modify the table structure, add the table back into the publication and
subscription and then reinitialize the subscription. Then I push the new
snapshot and look at the generated files and see that the table was not
scripted with the new columns.
"Daniel Crichton" wrote:
> Reply at bottom.
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...
> Don't you have to mark the replication for reinitialistion prior to creating
> the new snapshot? I'm still a beginner on the replication side (well, most
> of SQL Server :P), but whenever I make changes to publication properties the
> EM dialog always points out that the publication has to be reinitialised, so
> I'd assume that for the snapshot agent to pick up changes to the schema the
> same thing would need to be done.
> Dan
>
>
|||"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:5EA098FA-2F35-421C-B426-038EF2AC1158@.microsoft.com...
> Yes, you do have to reinitialize and I am doing that as well. Basically
> we
> drop the article (table) from both the subscription and publication, then
> I
> modify the table structure, add the table back into the publication and
> subscription and then reinitialize the subscription. Then I push the new
> snapshot and look at the generated files and see that the table was not
> scripted with the new columns.
Oh well, that's my involvement finished then - so far I've not needed to
modify any replicated tables, and as everything is still in development I'd
likely be lazy and use EM to rebuild them anyway. Sorry.
Dan
Showing posts with label agent. Show all posts
Showing posts with label agent. Show all posts
Wednesday, March 28, 2012
Monday, March 19, 2012
Incorrect Information in SQL Agent\Operator History
Apologies if this is in the wrong forum.
I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.
Not a major bug for me, but does anyone else have this problem?
--
DDL statement for operator.
EXEC msdb.dbo.sp_add_operator @.name=N'Rich',
@.enabled=1,
@.weekday_pager_start_time=90000,
@.weekday_pager_end_time=180000,
@.saturday_pager_start_time=90000,
@.saturday_pager_end_time=180000,
@.sunday_pager_start_time=90000,
@.sunday_pager_end_time=180000,
@.pager_days=0,
@.email_address=N'rich@.rich.net,
@.category_name=N'[Uncategorized]'
Yup.
Same problem here.
Rob
|||There is a hotfix for this.
I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.
|||This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.
Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.
(Please move to the Tools forum)
Incorrect Information in SQL Agent\Operator History
Apologies if this is in the wrong forum.
I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.
Not a major bug for me, but does anyone else have this problem?
--
DDL statement for operator.
EXEC msdb.dbo.sp_add_operator @.name=N'Rich',
@.enabled=1,
@.weekday_pager_start_time=90000,
@.weekday_pager_end_time=180000,
@.saturday_pager_start_time=90000,
@.saturday_pager_end_time=180000,
@.sunday_pager_start_time=90000,
@.sunday_pager_end_time=180000,
@.pager_days=0,
@.email_address=N'rich@.rich.net,
@.category_name=N'[Uncategorized]'
Yup.
Same problem here.
Rob
|||There is a hotfix for this.
I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.
|||This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.
Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.
(Please move to the Tools forum)
Incorrect Information in SQL Agent\Operator History
Apologies if this is in the wrong forum.
I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.
Not a major bug for me, but does anyone else have this problem?
--
DDL statement for operator.
EXEC msdb.dbo.sp_add_operator @.name=N'Rich',
@.enabled=1,
@.weekday_pager_start_time=90000,
@.weekday_pager_end_time=180000,
@.saturday_pager_start_time=90000,
@.saturday_pager_end_time=180000,
@.sunday_pager_start_time=90000,
@.sunday_pager_end_time=180000,
@.pager_days=0,
@.email_address=N'rich@.rich.net,
@.category_name=N'[Uncategorized]'
Yup.
Same problem here.
Rob
|||There is a hotfix for this.
I couldn't find the link, but I've been researching the hotfixes for some of the problems I've been having, and this was definately one of them.
|||This issue still exists in SQL2005 Standard 9.00.3042.00. Its been raised as a bug (#124871) back in Feb 2006 but is still present.
Does anyone know if\when this is going to be fixed for good? I've not seen any hotfix information regarding it.
(Please move to the Tools forum)
Subscribe to:
Posts (Atom)