I use SQL2005 with bunch of stored procedures. All worked fine. Then I set u
p
push transactional replication with sqlexpress so all agent were running on
publisher. Replication ran without errors and replicated articles. After som
e
time (about 1 hour) few stored procedures failed with 'Incorrect syntax near
E' error.
When I remove replication same stored procedures work without any error.
Every time when replication is set up error occurs on same procedures.
Stored procedures work with cursor, create and update temporary tables using
EXEC statement with dinamicaly build query.
When I remove replication same stored procedures work without any error, so
I guess it's not SQL.
I havent found nothing on this on Technet, MSDN or on internet.
Thank you for any hint.Enable logging to see exactly what is causing this error. Refer to this kb
article for more info on how to enable logging.
http://support.microsoft.com/kb/312292/en-us
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"janez" <janez@.discussions.microsoft.com> wrote in message
news:18698F9A-84F2-446F-9175-0B753EA01A99@.microsoft.com...
>I use SQL2005 with bunch of stored procedures. All worked fine. Then I set
>up
> push transactional replication with sqlexpress so all agent were running
> on
> publisher. Replication ran without errors and replicated articles. After
> some
> time (about 1 hour) few stored procedures failed with 'Incorrect syntax
> near
> E' error.
> When I remove replication same stored procedures work without any error.
> Every time when replication is set up error occurs on same procedures.
> Stored procedures work with cursor, create and update temporary tables
> using
> EXEC statement with dinamicaly build query.
> When I remove replication same stored procedures work without any error,
> so
> I guess it's not SQL.
> I havent found nothing on this on Technet, MSDN or on internet.
> Thank you for any hint.|||Thanks for advice. It will be usefuul in further analysis of the cause.
By trying I found setting scheduled snapshot to cause error. Transactional
replication with only initial snapshot works without any problem.
"Hilary Cotter" wrote:
> Enable logging to see exactly what is causing this error. Refer to this kb
> article for more info on how to enable logging.
> http://support.microsoft.com/kb/312292/en-us
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "janez" <janez@.discussions.microsoft.com> wrote in message
> news:18698F9A-84F2-446F-9175-0B753EA01A99@.microsoft.com...
>
>
Showing posts with label procedures. Show all posts
Showing posts with label procedures. Show all posts
Tuesday, March 20, 2012
Friday, March 9, 2012
Replication - PK & SP Question
If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
JLS,
you need to add PKs to tables only in the case of transactional replication. Adding an extra column to a table may cause issues with existing TSQL if it refers to the table without listing the columns - you have to look at individual cases, but insert into xxx select * from yyy where yyy is a table having had an extra column added will fail in this case. For this type of TSQL you'll need to change the syntax to use explicit column names.
Regards,
Paul Ibison
|||the stored procedures making the inserts have to be aware of the PK's if you are going to be supplying its value. If you are using the indentity column on the tables in the publisher you only need the identity property - not the identity property with the not for replication option.
You only need the not for replication option when a replication process is going to be inserting an identity value - normally the only time you need this is when you are doing some form of bi-directional replication, ie merge, queued updating, or bi-directional tranactional replication.
You don't need it for immediate updating subscribers.
You only have to worry about the added column when you are doing an unqualified insert, ie
insert into table1
select * from table2
It table2 now has an added column (an identity or GUID column) this class of inserts will fail.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23qMkojFOEHA.2560@.TK2MSFTNGP11.phx.gbl...
If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
|||"You only need the not for replication option when a replication process is going to be inserting an identity value"
I want to make sure I understand this, because I set all the tables in my publishing database which have identity columns to "Yes - Not for Replication".
I am doing one way transactional replication, so after reading the White Paper, I changed all my identity columns, and changed all my triggers to "Not for Replication" as well.
Did I not need to do the identity column bit? Is it correct that the rows from the publishing table will be inserted/updated to the subscriber table with no effect on the identity column, it will merely be updated or written?
As far as SP's, I don't have any inserts with
insert into table a
select * from table b
So this means I should not have any problems with SP's just because I added identity columns to be used as PK's, right?
I apologize if I seem to be asking the same questions of you, I just want to make sure I have it straight in my head before going to production.
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:uiOJncIOEHA.2560@.TK2MSFTNGP11.phx.gbl...
the stored procedures making the inserts have to be aware of the PK's if you are going to be supplying its value. If you are using the indentity column on the tables in the publisher you only need the identity property - not the identity property with the not for replication option.
You only need the not for replication option when a replication process is going to be inserting an identity value - normally the only time you need this is when you are doing some form of bi-directional replication, ie merge, queued updating, or bi-directional tranactional replication.
You don't need it for immediate updating subscribers.
You only have to worry about the added column when you are doing an unqualified insert, ie
insert into table1
select * from table2
It table2 now has an added column (an identity or GUID column) this class of inserts will fail.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23qMkojFOEHA.2560@.TK2MSFTNGP11.phx.gbl...
If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
|||That is correct. You only need to put the not for replication switch on the publisher when a replication process is going to update the PUBLISHER and you are not using Immediate Updating susbcribers.
These cases are 1) republishing, 2) merge, 3) bi-directional transactional, 4) queued.
You will need them on the subscriber if you want to have the replication process maintain the identity ranges it is publishing to the subscriber, but the subscriber will maintain identity ranges when a user process (Ie anything but a replication process)
does the updates.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
JLS,
you need to add PKs to tables only in the case of transactional replication. Adding an extra column to a table may cause issues with existing TSQL if it refers to the table without listing the columns - you have to look at individual cases, but insert into xxx select * from yyy where yyy is a table having had an extra column added will fail in this case. For this type of TSQL you'll need to change the syntax to use explicit column names.
Regards,
Paul Ibison
|||the stored procedures making the inserts have to be aware of the PK's if you are going to be supplying its value. If you are using the indentity column on the tables in the publisher you only need the identity property - not the identity property with the not for replication option.
You only need the not for replication option when a replication process is going to be inserting an identity value - normally the only time you need this is when you are doing some form of bi-directional replication, ie merge, queued updating, or bi-directional tranactional replication.
You don't need it for immediate updating subscribers.
You only have to worry about the added column when you are doing an unqualified insert, ie
insert into table1
select * from table2
It table2 now has an added column (an identity or GUID column) this class of inserts will fail.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23qMkojFOEHA.2560@.TK2MSFTNGP11.phx.gbl...
If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
|||"You only need the not for replication option when a replication process is going to be inserting an identity value"
I want to make sure I understand this, because I set all the tables in my publishing database which have identity columns to "Yes - Not for Replication".
I am doing one way transactional replication, so after reading the White Paper, I changed all my identity columns, and changed all my triggers to "Not for Replication" as well.
Did I not need to do the identity column bit? Is it correct that the rows from the publishing table will be inserted/updated to the subscriber table with no effect on the identity column, it will merely be updated or written?
As far as SP's, I don't have any inserts with
insert into table a
select * from table b
So this means I should not have any problems with SP's just because I added identity columns to be used as PK's, right?
I apologize if I seem to be asking the same questions of you, I just want to make sure I have it straight in my head before going to production.
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:uiOJncIOEHA.2560@.TK2MSFTNGP11.phx.gbl...
the stored procedures making the inserts have to be aware of the PK's if you are going to be supplying its value. If you are using the indentity column on the tables in the publisher you only need the identity property - not the identity property with the not for replication option.
You only need the not for replication option when a replication process is going to be inserting an identity value - normally the only time you need this is when you are doing some form of bi-directional replication, ie merge, queued updating, or bi-directional tranactional replication.
You don't need it for immediate updating subscribers.
You only have to worry about the added column when you are doing an unqualified insert, ie
insert into table1
select * from table2
It table2 now has an added column (an identity or GUID column) this class of inserts will fail.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23qMkojFOEHA.2560@.TK2MSFTNGP11.phx.gbl...
If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
|||That is correct. You only need to put the not for replication switch on the publisher when a replication process is going to update the PUBLISHER and you are not using Immediate Updating susbcribers.
These cases are 1) republishing, 2) merge, 3) bi-directional transactional, 4) queued.
You will need them on the subscriber if you want to have the replication process maintain the identity ranges it is publishing to the subscriber, but the subscriber will maintain identity ranges when a user process (Ie anything but a replication process)
does the updates.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Monday, February 20, 2012
Replicating Stored Procedure Alterations
Hi,
I've created two publications. One publication contains the tables...the second publication contains that stored procedures that work with the tables of the first publication. In another database I subscribe to the two publications...the table publicati
on first followed by the stored procedure publication. I'd like to now make an alteration to the stored procedure on the publisher using ALTER PROC and have that schema change replicated over to the subscriber. I perform the ALTER PROC on the publisher
but the change does not appear on the subscriber. How can I get the ALTER PROC to take affect on the subscriber?
Thanks
Jerry
have a look at sp_addscriptexec for this
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hillary,
Thanks. There is an item that discusses this to some degree at the SQL Mag site instant doc #26344 but I'm out of town and do not know my login info. Looks like it recommends reinitalization. Which sounds ok for the proc subscription but a default of a
drop for the proc would delete the permissions as well...agree? That doesn't sound like the best solution to me. I had thought of using the on-demand script execution as a workaround. Just wanted to know if I was missing something. Guess not?
Also, looks like you have some useful information in your books on replication. What are the costs and how are they obtainable? By the way, I'm a SQL instructor (in replication class this week). Are you willing to provide me with a copy of both replica
tion books no-fee? If so, I'd be willing to share them as a potential purchasable resource for my students in my replication classes.
Let me know.
Thanks again.
Jerry
|||You don't need to reinitialize when running sp_addscriptexec.
it will merely distribute and execute a script to all of your subscribers.
you can reinitialize if you want, but you don't have to
The book will be available on amazon eventually. It will also be available on nswu.com at some point in time as well. I'll contact you offline about your other request.
It has to be printed first
|||Right...wasn't implying that I needed to reinit when using the sp proc. Was comparing two different approaches to resolve the issue. I think the sp proc is the easiest to implement and work with.
Jerry
I've created two publications. One publication contains the tables...the second publication contains that stored procedures that work with the tables of the first publication. In another database I subscribe to the two publications...the table publicati
on first followed by the stored procedure publication. I'd like to now make an alteration to the stored procedure on the publisher using ALTER PROC and have that schema change replicated over to the subscriber. I perform the ALTER PROC on the publisher
but the change does not appear on the subscriber. How can I get the ALTER PROC to take affect on the subscriber?
Thanks
Jerry
have a look at sp_addscriptexec for this
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hillary,
Thanks. There is an item that discusses this to some degree at the SQL Mag site instant doc #26344 but I'm out of town and do not know my login info. Looks like it recommends reinitalization. Which sounds ok for the proc subscription but a default of a
drop for the proc would delete the permissions as well...agree? That doesn't sound like the best solution to me. I had thought of using the on-demand script execution as a workaround. Just wanted to know if I was missing something. Guess not?
Also, looks like you have some useful information in your books on replication. What are the costs and how are they obtainable? By the way, I'm a SQL instructor (in replication class this week). Are you willing to provide me with a copy of both replica
tion books no-fee? If so, I'd be willing to share them as a potential purchasable resource for my students in my replication classes.
Let me know.
Thanks again.
Jerry
|||You don't need to reinitialize when running sp_addscriptexec.
it will merely distribute and execute a script to all of your subscribers.
you can reinitialize if you want, but you don't have to
The book will be available on amazon eventually. It will also be available on nswu.com at some point in time as well. I'll contact you offline about your other request.
It has to be printed first
|||Right...wasn't implying that I needed to reinit when using the sp proc. Was comparing two different approaches to resolve the issue. I think the sp proc is the easiest to implement and work with.
Jerry
Subscribe to:
Posts (Atom)