Replication Error in Store Procedure that is not in Articles list

Hello,
I have a replication error related to the schema script of a procedure that
is
not longer on the list of articles to replicate from the publisher and also
it is
not part of the replicated store procedures in the replicas.
I someone has an idea what could it be the problem. Please let me know.
Thanks,
Car.
What is the error message you are getting.
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
"Car" <Car@.discussions.microsoft.com> wrote in message
news:ECA4299C-6D87-4F2B-B4C5-93039FCC0165@.microsoft.com...
> Hello,
> I have a replication error related to the schema script of a procedure
> that
> is
> not longer on the list of articles to replicate from the publisher and
> also
> it is
> not part of the replicated store procedures in the replicas.
> I someone has an idea what could it be the problem. Please let me know.
> Thanks,
> Car.
|||I delete the subscriptions and recreate them and I don't have any more this
message It was something like comErrorException(0x80004005). However when I
delete an article from the publisher. It does not work. it is not letting me
to do the schema changes I used to do before.
"Hilary Cotter" wrote:

> What is the error message you are getting.
> --
> 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
>
> "Car" <Car@.discussions.microsoft.com> wrote in message
> news:ECA4299C-6D87-4F2B-B4C5-93039FCC0165@.microsoft.com...
>
>

replication between MS Access and SQL Server

Does anyone know the correct procedure to setup a pull relationship from MS Access to a SQL database and vice versa?http://vyaskn.tripod.com/repl_ans5.htm#sqljetsub for information.
And refer to books online

Replication and table/SP change?

I build a table and stored procedure replication between 2 database.
seems when I want to change the stored procedure / or Table, the replication still hold the objects and won't let me to do.

is that the only way drop the replication( publisher/disbtribution/subscrib)
make the table / store procedure change, than rebuild replication again?? any other short cut??

GUYANGHi

SQL2K has a lot of improvements when it comes to changing the schema of replicated tables.

What Ver SQLServer R U Using & what types of replication merge/snapshot etc

Changes to tables must be done through the publication properties in 2K Enterprise Manager or using the system Stored Procedures provided for the purpose.

Stored Procedures can only be edited at the publisher U Can't merge changes to them - Urghhh What a thought. - U can Snapshot these out to subscribers.

If U R Using SQL7 you are out of Luck

GW|||Valid references by GW, for any schema changes on the source table, its advised to redefine the replication settings once its finished.

Replicating stored procedure/trigger permissions to another database

I need to come up with a script that when executed it will create a stored procedure and trigger along with permissions. Is there a way to make this into a package. Any ideas?So you want to take a stored procedure that is in the current database, and somehow "automagically project" that procedure intact to another (subscriber) database?

That's a novel thought.. It could be done, and it wouldn't be terribly hard.

-PatP|||There is no "automagic" in it, just use SQL-DMO object model. Just one method is involved. Examples are in BOL.|||things to automate
-add a user to master table
-add Permissions to xp_cmdshell
-create trigger in table
-create stored procedure in needed database
-uncheck Non-SysAdmin job setup Proxy Account box and create proxy account

I image that I could write this all out by using a T-SQL stored procedure. But, some things that I don't know is: where it should be stored? or what would be the most efficient way of going about this?

Don't know where to start but to create the procedure and then I'll figure out what to do from there.

Replicating Stored Procedure permissions issue

I'm having a problem replicating stored procedure permssions accross two sql server 2005 servers, using transactional replication. When I replicate tables, it's easy enough to send the permissions over from the subscriber since there is an option under the articles properties for "Copy permissions." I can't seem to find any such option for SPs, UDFs or views. Am I missing something?

So the other idea I had was to create a script to grant the proper permissions, and run it using sp_addexecscript to my publication. Currently I have two one-way transactional publications. One publication for all my tables, and another for SPs, UDFs and views. I can run sp_addexecscript on the publication containing all my tables with out a problem, but when I run it against the publication containing my SPs, UDFs and views I get the following error:

Msg 21332, Level 16, State 1, Procedure sp_MSrepl_addscriptexec, Line 57
Failed to retrieve information about the publication : SP_UDF_Views_transactional. Check the name again.

I'm not sure why I'm getting this message. If anyone can help me out, I'd greatly appreciate it.

sp_addexecscript must be some user-defined stored procedure, I've never heard of it so I don't know what would cause be the problem. However the workaround would be to use parameter @.post_snapshot_script in sp_addpublication. Supply your permissions TSQL file here, it will automatically be applied at the subscriber after the snapshot is applied.

|||

While it is unfortunate that the option for replicating permissions with SPs\VWs\UDFs is not exposed in the UI (please file a bug at the Microsoft Connect site), you can enable the schema option (0x40000000) for replicating permissions via sp_addarticle.

Hope that helps,

-Raymond

|||Greg,

Thanks for the help, I will give that a try. Also, to clarify, I mistyped in my first post what command I'm using. The command is sp_addscriptexec. Sorry about the confussion.|||Excellent, both methods worked great! Thanks for the help!|||On the first test of altering the schema option, everything worked fine, but now I'm running into errors.

My original @.schema_option value was 0x0000000008000001, I changed it to 0x0000000048000001 to enable permission replication (0x40000000). Once I changed that value it tries to add "WITH EXECUTE AS CALLER" when it replicates the SPs. Then I get the following errors:

Error messages:
Incorrect syntax near 'dbo'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near 'dbo'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near the keyword 'AS'. (Source: MSSQLServer, Error number: 156)
Get help: http://help/156
Must declare the scalar variable "@.sServer". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137
Must declare the scalar variable "@.iType". (Source: MSSQLServer, Error number: 137)
Get help: http://help/137

Once I change the schema option back to 0x0000000008000001, replication works, but without permissions...
|||Can you post the schema script for the problematic stored procedure generated by the snapshot agent? Thanks.|||

Just to rule out the obvious, can you post the schema option value returned by running sp_helparticle at the publisher as well? Thanks much.

-Raymond

|||Raymond,

Sorry to not get back to you sooner. I was out of the office for a few days. Here is the script I'm using to add the article (if this isn't what you wanted, please let me know).

exec sp_addarticle @.publication = N'dismal_v8_SP_UDF_Views_transactional', @.article = N'ct_getContentItem', @.source_owner = N'dbo', @.source_object = N'ct_getContentItem', @.type = N'proc schema only', @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'drop', @.schema_option = 0x0000000048000001, @.destination_table = N'ct_getContentItem', @.destination_owner = N'dbo', @.status = 16

Also, the schema_option value returned by sp_helparticle: 0x0000000048000001

It's weird, I'm getting this error on nearly every stored procedure in my database. If I right click, modify, on the stored procedure and then hit execute without making any changes to the SP, this error goes away. Unfotunetly we have about 20 databases that will need to replicate 100 or so stored procedures, so I don't think "recompiling" the stored procedure is an acceptable solution. Let me know what you think. Thanks!|||

Are you getting the error when the initial snapshot is delivered or after you have changed your stored procedures at the publisher and have the "alter procedure" replicated to the subscriber (ddl replication). Not all schema options are honored during ddl replication. Ddl replication also doesn't handle the use of " for delimiting string literals.

If your subscribers are SQL2005 servers, they should have no trouble handling the "execute as" syntax so the problem that you are experiencing may be due to something else. It would be great if you can post the create (alter?) procedure statement that is executed at the subcriber, you should be able to get that from SQL Profiler.

Modifying text objects such as stored procedures by right clicking on the object can modify the object header in unexpected ways, you may want to use direct query to modifying your stored procedures instead.

-Raymond

|||Raymond,

I'm getting the error when the initial snapshot is delivered.

I will try to post the create procedure statement that is executed at the subscriver later today or tomorrow. I've since recreated the publication without the schema option to copy permissions in order to test other items.

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

Replicating Stored Procedure

Hi:
I made a change to the stored proc in the publisher database but the change
did not get replicated. I should not have to run the snapshot agent again to
do that. Please let me know how can this be easily done.
Thanks
Mike,
this is better handled in SQL 2005, but in 2000 we can use sp_addscriptexec,
linked servers or manual addition of the new code. For this reason I
generally add the code parts of my publications (sps, views, functions) to a
separate publication, so if I decide to use replication to send them over, it
doesn't disturb my main publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Use sp_addscriptexec to send the commands if your subscribers were deployed
through UNC(file copies). If there were deployed through ftp you will have
to connect to them individual and run the commands individually.
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
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:15028C08-3FA7-4647-9C23-7A2698AF8C41@.microsoft.com...
> Hi:
> I made a change to the stored proc in the publisher database but the
> change
> did not get replicated. I should not have to run the snapshot agent again
> to
> do that. Please let me know how can this be easily done.
> Thanks
>