Replacing Merge Objects

I have a merge publication that I need to change some views and get the new
views to the subscribers. At first, i tried the following steps and it
failed when trying to synch from subscriber:
exec sp_dropmergearticle ...
ALTER VIEW ....
exec sp_addmergearticle ...
re-created snapshot
Do I only need the ALTER VIEW and then re-create snapshot? I want to keep
the VIEW in the articles. Thanks.
David
Or, do I need to drop and re-create the subscriber snapshot. We are using
SQL 2000 and have laptops as anonymous subscribers. I don't want to lose
the data changes that have been made on the laptops. I prefer to have them
synch first, then recreate their snapshot. However, this is very difficult
as laptops synch at different times. Can anyone give me advice or point me
to a "how-to" article? I'm sure our situation is no different than a lot of
other Merge replications with disconnected subscribers. I am very new to
SQL Server replication so I want to do it right the first time. Thanks.
David
"David" <dlchase@.lifetimeinc.com> wrote in message
news:eCgHjbUGGHA.3984@.TK2MSFTNGP14.phx.gbl...
>I have a merge publication that I need to change some views and get the new
>views to the subscribers. At first, i tried the following steps and it
>failed when trying to synch from subscriber:
> exec sp_dropmergearticle ...
> ALTER VIEW ....
> exec sp_addmergearticle ...
> re-created snapshot
> Do I only need the ALTER VIEW and then re-create snapshot? I want to keep
> the VIEW in the articles. Thanks.
> David
>
|||David,
have you given up trying to isolate the views into a different publication?
This will be much better than reinitializing to send down view changes,
which is what you'll be obliged to do this way. Add the views to a new
publication and have all your subscribers subscribe to this new publication.
Typically this'll be a snapshot one for the sake of clarity. Each time the
views change, you generate a new snapshot and synchronize. Disable the
snapshot job in this case because typically you won't synchronize often, and
it'll be manually controlled. Pls let me know if any of this is unclear.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||No, I haven't given up on that. I plan to do that in near future. But
I still don't understand why I can't synch now when I change a view.
The laptop synch fails with:
The schema script
'\\LIFEDEVTEST\E$\Snapshots\unc\LIFEDEVTEST_MCFIDa ta_MCFIDataPubAll\2006
0114140533\vw_BillingDetail_1737.sch' could not be propagated to the
subscriber.
Cannot drop the view 'dbo.vw_BillingDetail' because it is being used for
replication.
What I did on the publisher was 1. alter view 2. sp_addmergearticle 3.
run new snapshot.
Then, when I tried to synch from a laptop I got the error above.
Wouldn't I get this even with a separate publication? Or would I have to
know that a new snapshot exists at the subscriber (laptop) before
synching.
As you can tell, the light still has not gone on in my brain about this.
I want it to be simple so user of laptop doesn't have to do special
things. What am I missing? Thanks. I really appreciate your help. I
did a lot of replication with Access in the past and this is very
different.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||David,
it'll be fine if you don't drop the view from the publication. If it is left
there, you just alter the view on the publisher then reinitialize. This
process will call a system proc (sp_MSunmarkreplinfo) behind the scenes that
will remove the replication flag and then allow the article to be dropped
during the replication process. The way you're doing it means the
replication engine 'thinks' this is the first time it's seen the view so the
flag is not reset on the subscriber and you get the same error you'd get if
you tried manually to alter the view on the subscriber.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I'll give that a try. Thanks.
David
*** Sent via Developersdex http://www.codecomments.com ***
|||When you refer to "reinitialize" do you mean just try the synch again with
laptop subscriber? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23hAUlrhGGHA.2684@.TK2MSFTNGP11.phx.gbl...
> David,
> it'll be fine if you don't drop the view from the publication. If it is
> left there, you just alter the view on the publisher then reinitialize.
> This process will call a system proc (sp_MSunmarkreplinfo) behind the
> scenes that will remove the replication flag and then allow the article to
> be dropped during the replication process. The way you're doing it means
> the replication engine 'thinks' this is the first time it's seen the view
> so the flag is not reset on the subscriber and you get the same error
> you'd get if you tried manually to alter the view on the subscriber.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
to reinitialize, you right-click on the publication and select to
'reinitialize all subscriptions'. This means that you'll have to create a
new snapshot then synchronize.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Wait. What about the laptops that are out in the field that have not yet
updated the Publisher with their changes yet? If I create a new snapshot,
won't that kill their updates?
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OoecZ5qGGHA.1628@.TK2MSFTNGP12.phx.gbl...
> David,
> to reinitialize, you right-click on the publication and select to
> 'reinitialize all subscriptions'. This means that you'll have to create a
> new snapshot then synchronize.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||AFAIR, they'll have the option of uploading their changes before receiving
the snapshot.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replacing Database Template values

I have been working on creating templates to have for when creating
databases, tables, views, etc.
When I do a replace template values on this particular template all values
get replaced except for the drop database line. The strange thing is if I
replace the values and then do replace values again it works?
Any suggestions on why this is happening and what I can do to resolve it.
/ ****************************************
********************************/
/* Name : <Database_Name ,VCHAR,'SP_Create_Database'>
*/
/* Version : <Version ,VCHAR,'1.0'>
*/
/* Author : <Author ,VCHAR,'Joseph Pruiett'>
*/
/* Initials : <Author_INT ,VCHAR,'JEP'>
*/
/* Date : <Date , date ,'04/29/2005'>
*/
/* Description : <Desc , CHAR,'TEST'>
*/
/ ****************************************
********************************/
/* <Date , date ,'04/29/2005'> : <Version ,VCHAR,'1.0'>: <Author
,VCHAR,'Joseph Pruiett'>: Description <Desc , CHAR,'TEST'> */
/ ****************************************
********************************/
/ ****************************************
********************************/
/* @.d_name -- Change data value for this to the name of Database */
/ ****************************************
********************************/
--Check to see if database exist
IF EXISTS (SELECT name
FROM master..sysdatabases
WHERE name = N'<@.d_name, sysname, new_db>')
BEGIN
DROP DATABASE <@.d_name, sysname, new_db>
END
GO
--Create database
CREATE DATABASE <@.d_name, sysname, new_db>
ON PRIMARY
(
NAME = <logical_file_name_1, , new_db_file_1>,
FILENAME = N'<os_file_name_1, , c:\program files\microsoft sql
server\mssql\data\new_db.mdf>',
SIZE = 1MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%
),
(
NAME = <logical_file_name_2, , new_db_file_2>,
FILENAME = N'<os_file_name_2, , c:\program files\microsoft sql
server\mssql\data\new_db.ndf>',
SIZE = 1MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%
),
LOG ON
(
NAME = <logical_log_file_name_1, , new_db_log_file_1>,
FILENAME = N'<os_log_file_name_1, , c:\program files\microsoft sql
server\mssql\data\new_db_log_1.ldf>',
SIZE = 1MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%
)
GOYou can try to use a decent text editor to see if there are any hidden
characters. Textpad is a good one to try.
-oj
"JosephPruiett" <JosephPruiett@.discussions.microsoft.com> wrote in message
news:0FB8C772-DA23-4856-BD31-619C118D8664@.microsoft.com...
>I have been working on creating templates to have for when creating
> databases, tables, views, etc.
> When I do a replace template values on this particular template all values
> get replaced except for the drop database line. The strange thing is if I
> replace the values and then do replace values again it works?
> Any suggestions on why this is happening and what I can do to resolve it.
> / ****************************************
********************************/
> /* Name : <Database_Name ,VCHAR,'SP_Create_Database'>
> */
> /* Version : <Version ,VCHAR,'1.0'>
> */
> /* Author : <Author ,VCHAR,'Joseph Pruiett'>
> */
> /* Initials : <Author_INT ,VCHAR,'JEP'>
> */
> /* Date : <Date , date ,'04/29/2005'>
> */
> /* Description : <Desc , CHAR,'TEST'>
> */
> / ****************************************
********************************/
> /* <Date , date ,'04/29/2005'> : <Version ,VCHAR,'1.0'>: <Author
> ,VCHAR,'Joseph Pruiett'>: Description <Desc , CHAR,'TEST'> */
> / ****************************************
********************************/
> / ****************************************
********************************/
> /* @.d_name -- Change data value for this to the name of Database */
> / ****************************************
********************************/
> --Check to see if database exist
> IF EXISTS (SELECT name
> FROM master..sysdatabases
> WHERE name = N'<@.d_name, sysname, new_db>')
> BEGIN
> DROP DATABASE <@.d_name, sysname, new_db>
> END
> GO
> --Create database
> CREATE DATABASE <@.d_name, sysname, new_db>
> ON PRIMARY
> (
> NAME = <logical_file_name_1, , new_db_file_1>,
> FILENAME = N'<os_file_name_1, , c:\program files\microsoft sql
> server\mssql\data\new_db.mdf>',
> SIZE = 1MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%
> ),
> (
> NAME = <logical_file_name_2, , new_db_file_2>,
> FILENAME = N'<os_file_name_2, , c:\program files\microsoft sql
> server\mssql\data\new_db.ndf>',
> SIZE = 1MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%
> ),
> LOG ON
> (
> NAME = <logical_log_file_name_1, , new_db_log_file_1>,
> FILENAME = N'<os_log_file_name_1, , c:\program files\microsoft sql
> server\mssql\data\new_db_log_1.ldf>',
> SIZE = 1MB,
> MAXSIZE = 200MB,
> FILEGROWTH = 10%
> )
> GO
>

Replace Function? SQL2K/EM

Hi,
I need to change some table and field names - is there a way to update
all the occurances in Views and Stored procedures?Update the source code for the views and stored procedures and then redeploy
them. You can use the rows from sysdepends to determine which views and
stored procs are affected by name change of a table.
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...
> Hi,
> I need to change some table and field names - is there a way to update
> all the occurances in Views and Stored procedures?
>|||Do they have to be updated manually?
Visual Studio has tools to do this automatically, does EM have nothing
similar for its source code ?
Tim Dot NoSpam wrote:
> Update the source code for the views and stored procedures and then redepl
oy
> them. You can use the rows from sysdepends to determine which views and
> stored procs are affected by name change of a table.
> "hals_left" <cc900630@.ntu.ac.uk> wrote in message
> news:1150108178.447216.308320@.f14g2000cwb.googlegroups.com...|||> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
EM/SSMS will not automatically rename objects because there is nothing on
the server side that will track dependencies. However, this one of the many
new features included in the upcoming Visual Studio 2005 Team Edition for
Database Professionals.
See http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"hals_left" <cc900630@.ntu.ac.uk> wrote in message
news:1150110493.525079.29150@.i40g2000cwc.googlegroups.com...
> Do they have to be updated manually?
> Visual Studio has tools to do this automatically, does EM have nothing
> similar for its source code ?
> Tim Dot NoSpam wrote:
>

Rendering report when one dataset times out

We use Report Server reports to provide our support staff with views into several production databases on various servers. The most commonly used report is a summary of the data on several production servers. The report contains multiple datasets. Each dataset points at a different server. If one of the servers is down some reason, one dataset fails to execute, and the entire report fails to to render.

What I would like is some control over this behaviour. If a given data set fails to execute, I would like the report to continue and render the results. The failed data set would obviously show no data but this is fine by me.

I know I can place timeout values on each dataset. But this doesn't help me.

Can I structure my report or SQL in such a way that the report survives the failure of a dataset to execute because a remote server is down?

Thanks in advance for your help

Hi,

no actually not. YOu have to put the query within a stored procedure, catching it up with exception handling (Hope you are on SQL 2k5) or create a own data extension with .NET to provide a dataset to the report. In the custom coded data extension you will have all the functionalities of the .NET Framework to catch those errors.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||You may use link server to collect the data on others DataBase, And put them in Temp DB...