Creating Snapshot / Transactional Table By Appending

Feb 6, 2015

I am very new to SQL coming from a SAS background.

I work at a College where I am trying to create a table that basically stores application data so that I can record how many applications we had on a set day.

For example I want to run it say bi-weekly and create a date/time stamp for each row and then the next time it runs it appends to the original table but with the new date/time stamp.

I have managed to create the table and the date/time stamp but I'm not sure how to do the next stage.

This is not the best practice as the table will get "big" quite quickly (although we're only dealing in the hundreds in terms of rows). Ideally I'd have a more transactional database that only appends a record if there is a change i.e. the status of the application has changed from "Received" to "Interview" or similar.

I'm not sure how to copy and paste the code dynamically in this forum but this is what I have got so far (see below).

CC.cc_name as Academy,
SUBSTRING(CC2.cc_reference,1,4) as Cost_Centre,
MD2.m_reference as Course,
MD2.m_name as Course_Name,
CAST(MD2.m_start AS DATE) as Start_Date,

[Code] ...

View 4 Replies


Transactional Replication And Snapshot Schedule

May 21, 2002

I've set up Transactional replication Pushed from SQL 7 to SQL 2000. All works fine accept I have noticed that the snapshot agent is running once a week and storing a lot of data I don't really need in c:msql7
eplicationnamedatastamp. This is gradually building up and as C is a smallish partition it'll only survive for another week.

My questions are:
1) Can the Snapshot schedule be disabled? I'm under the impression that it's only required when a new subscriber comes along which is never! If I do need a new subscriber I could just manually produce another snapshot.

2) Can I delete the previous snapshot folders under ReplData? On a development server I renamed one of the folders and the ground didn't open up below my feet.

3) What effect would changing the working folder used by the distribution agen have, the distributer is on the publisher (which as I mentioned is pushing) I've tried changing the working folder, on a development server, I manually ran a snapshot which whose BCP files all ended up in my new location and the Transactional Replication didn't seem to care.

I've looked in a few places for a difinitive answer and I'm not having much luck so any help (preferably before saturday) would be greatly appreciated.


View 1 Replies View Related

Combining Transactional And Snapshot Replication

Feb 2, 2004


I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)

We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)

When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.

If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.

Can I combine the two replication schemes to deliver updates to the same target database:
- transactional replication delivering realtime updates to the tables with PKs during the day and
- snapshot replication updating all the tables once per 24hrs at night?

Or is there a better way of doing this?

I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...


View 2 Replies View Related

Transactional Replication && Snapshot Agent

Jun 27, 2007

Why would you schedule a Snapshot Agent to run daily, weekly, monthly, etc. for Transactional replication when the transactions are replicated once the change is committed to the Publication database?

Does this help to replicate DDL against the Subscriber?


Thomas LeBlanc

View 1 Replies View Related

Transactional Replication- Snapshot Agent

Sep 7, 2006

When you create a transactional Replication.Is there a way to disable to snapshot agent scheduler. I believe that there is a way to disble it when you create a publisher.

Help ME!!!!!!!!!!!!!!!

View 3 Replies View Related

How Often To Rebuild Snapshot In Transactional Replication

Mar 27, 2007


We are running transactional replication in a high volume environment and we are starting to see high IO on the distribution database when the distribution cleanup runs. We only build the snapshot once a week and I was wondering if I rebuilt the snapshot more often it would reduce the size of the distribution database and the amount of IO that are being generated during the cleanup?

Thanks in advance

View 4 Replies View Related

Alternate Snapshot Location For Transactional Replication

Jun 2, 2006

I have transactional replication setup with SQL 2000 on W2K3 cluster using updateable push subscribers. While setting up replication, we chose default location for snapshot folder that resides on non-clustered drive. Is there a way to change this location without disturbing current replication setup. I looked at the 'alternate snapshot location' solution, but it requires snapshot re-initialization. I am trying to do this with minimal effort and downtime.

View 3 Replies View Related

Transactional Replication - Automating A Regular Snapshot

Feb 13, 2004

I have transactional replication running. I would like the Snapshot Agent to run regularly (once per day, probably, at 23:00) so that the target data are completely refreshed so that there is little possibility of the target getting out of sync.

The Snapshot Agent's job is kicking off OK, but the "snapshot was not generated because no subscriptions needed initialization". I know I can initialise the subscriptions manually - is there a way to do this so that the initialisation happens just before the snapshot job launches?

Or is this a bad thing to want to do?


View 1 Replies View Related

Initializing Transactional Replication Without A Snapshot In SQL 2000

Nov 10, 2006

SQL Server 2005 Books Online provides an article entitled, "Initializing a Transactional Subscription without a Snapshot". Is it possible in SQL Server 2000 to initialize transactional replication without a snapshot?

So far, I have been unable to find a similar procedure mentioned in the SQL 2000 Books Online. I was able to follow the 2005 procedure using SQL 2000 until I got to the step that says to enable the "Allow initialization from backup files" option on the "Subscription Options" tab of the "Publication Properties" dialog. But that option does not appear in the SQL 2000 version of the specified dialog box.

Any ideas?

View 4 Replies View Related

Do I Really Need A Snapshot (to Initialize Transactional Replication, In SQL2000)?

Feb 20, 2006

I have a pretty big (350 gb) OLTP database that I want to replicate in its entirety. I'm concerned about the impact of taking a snapshot of it (it is processing at some level pretty much 24x7). I know on SQL2005 there is the option to initialize from backup, but unfortunately we won't be on 2005 in time.

I'm thinking of doing something like this:

Set up the distributor, publication, and subscription
Turn off distribution agent
Set the publisher to "sync with backup"
Backup the publisher, full then log
Truncate tables MSrepl_transactions and MSrepl_commands in the distribution db (I don't have any other replication going on)
Turn off "sync with backup"
Restore the full and tran log backups to new subscriber db
Create subscriber stored procs in subscriber
Start up distribution agent

I'm looking for opinions on whether it's worth going this route to avoid taking the snapshot. Data integrity is the number one priority -- if I have to do a snapshot to ensure that, I will do it.

Thanks in advance!


View 1 Replies View Related

SQL 2012 :: Script Failure During Snapshot For Transactional Replication

Apr 9, 2014

I built a number of publications on a SQL Server 2005 box to replicate to a SQL Server 2012 subscriber. All the publications except one are fine. During the snapshot phase of schema script generation I get Script Failed for Table 'dbo.MediaDisplayLibraryFileData'. From the Replication monitor for the Snapshot Agent on the Publication I get, "Column FileData in object MediaDisplayLibraryFileData contains type VarBinaryMax, which is not supported in the target server version, SQL Server 2000." This message makes no sense since the target server version is 2012. I have even checked that the compatibility level was set to 110 before I started the process of setting up replication. How do I resolve this error?

View 0 Replies View Related

SQL Server 2008 :: Transactional Replication (Snapshot Agent)

Mar 19, 2015

Is there a way to force the snapshot to use a format file when bcp'ng the data to the subscriber?

The publisher and subscriber schemas are slightly different. Mainly added columns to the subscriber.

We have a post snapshot script that updates these additional columns on the subscriber.

I'd like to force the snapshot to use a format file, so it doesn't error when initially loading the data to the subscriber.

Is this possible?

View 1 Replies View Related

Can Not Generate Snapshot (SQl Server 2005 Transactional Repl )

Sep 15, 2005


View 6 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot In Multiple Files

Sep 15, 2015

We have a large database with a small number of large tables in it (and a larger number of SMALLER tables), and it is a publisher for a transactional replication scenario. When I create a snapshot to initialize a new subscription, I notice with the larger tables that sometimes it generates multiple files in the snapshot folder, usually in multiples of 16, and numbers them like this:


With other tables, I'll get just one LARGE snapshot file, named:


In the latter case, the file can be very large (most recent is 38GB).

In both cases, the subscription will eventually be initialized, but the smaller files will generate separate log entries every few minutes in the Replication Monitor, showing 'Bulk Copied data into 'MyTable' (34231221 rows)', whereas the larger table will generate only ONE log entry, showing 'Bulk coping data into table 'MyOtherTable', and it may take a couple of hours before there is anything else showing...except for an entry saying, 'The process is running and is waiting for a response from the server.'

My question is: what would be the difference between the two tables that would result in one generating MULTIPLE snapshot files, the other only a single, much larger one? The only difference I can see in the table definition is that the one generating multiple files has a clustered index, whereas the others do not.

View 0 Replies View Related

SQL Tools :: Script To Monitor Server Transactional And Snapshot Replication

Apr 28, 2015

Any TSQL script to monitor replication(Transactional, Snapshot) with current status ? I have tried below script but it giving error.

"Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested."

DECLARE @srvname VARCHAR(100)
DECLARE @pub_db VARCHAR(100)

View 4 Replies View Related

Transactional Replication - Pull Subscription Snapshot Being Applied Continuously

Jan 25, 2007


We have setup transactional replication between 2 databases on SQL Server 2000 SP3a  (~70GB), using a concurrent snapshot (to prevent locking out of the live database) to initilaise the data and a pull subscription from the second database.

From analysing the msdistribution_history table in the distribution database on the subscriber it appears that the snapshot is being applied in a continuous loop to the subscriber database. Viewing the comments column in the msdistribution_history table we can see the following sequence of events occuring

Applied script 'snapshot.pre'
Then it applies all the schema files .sch
Then it applies all the index files .idx
The it bulk copies the data in (bcp)
Then it creates the Primary Keys
Then it applies all the trigger files .trg
Then it applies all the referential integrity files .dri

These all complete successfully but then the process kicks off again immediately after reapplying the snapshot.  We are unaware of any settings that may be causing this.

Any help on what maybe causing this would be much appreciated.



View 5 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot - Does Not Generate Any Files For New Article

Oct 22, 2015

There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.

When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).

When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.

The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.

I have tried:

- dropping/re-adding the article in question.

- Setting up a new Snapshot Folder

- Validated all the settings and configurations

I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.

View 0 Replies View Related

Stored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.

Jan 26, 2006

Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.

View 2 Replies View Related

Help With Creating A Snapshot Please

Jan 23, 2008

hello everyone

i am going through the self kit book to get my MCTS and i am stuck on
one of the practices. Creating a snapshot

I typed in

create database snapshottest
Name= 'Adventureworks_data',
Filename = 'C:program filesMicrosoft SQL servermssql.1mssqldatasnapshottest.ds')

as snapshot of adventureworks
i get the error message

msg 5127 level 16 state 1 line 1
all files must be specified for database snapshot creation
missing file "AdventureworksFT_data".

i have not stated AdventureworksFT_data

i stated Adventureworks_data

why is it asking for AdventureworksFT_data

can someone please tell me if the syntax is wrong or is there something missing



View 7 Replies View Related

While Creating A Snapshot...

Apr 20, 2007


Here's the idea: There is a central SQL Cluster Server and there are branches in different cities. I'll replicate databases from branch SQL Servers to the central SQL Server. Every city will have a database in the central SQL Server.

While i was creating publication for replication, i get the following errors from different (6-7) tables among 289 articles.


2007-04-20 12:00:45.18 [46%] The replication agent had encountered an exception.
2007-04-20 12:00:45.18 Source: Replication
2007-04-20 12:00:45.18 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-04-20 12:00:45.18 Exception Message: Incorrect syntax near the keyword 'percent'.
2007-04-20 12:00:45.18 Must declare the scalar variable "@metadata_type".
2007-04-20 12:00:45.18 Must declare the scalar variable "@compatlevel".
2007-04-20 12:00:45.18 Must declare the scalar variable "@lineage_old".
2007-04-20 12:00:45.18 Must declare the scalar variable "@lineage_old".
2007-04-20 12:00:45.18 Must declare the scalar variable "@compatlevel".
2007-04-20 12:00:45.18 Must declare the scalar variable "@p8".
2007-04-20 12:00:45.18 Failed to generate merge replication stored procedures for article 'mf_pro_processeditem_financial'.
2007-04-20 12:00:45.18 Message Code: 156
2007-04-20 12:00:45.18


These articles are belong to a software which is developed by another company.

Any ideas?

SQL Version : Enterprise 2005 with SP2

Replication type: Merge



View 6 Replies View Related

Problem Creating A New Transactional Publication

Oct 23, 2007


I'm creating a new transactional publication and when the process begins to add articles i got the follow error:

* SQL Server Management Studio could not create article 'Tbl_Menu'. (New Publication Wizard)


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Automatic identity range support is useful only for publications that allow updating subscribers.
Changed database context to 'sodimprumde4'. (Microsoft SQL Server, Error: 21231)

For help, click:

Thanks for any help, If anyone know how to resolve this problem.

View 2 Replies View Related

Creating A Snapshot For Merge Replication

Feb 14, 2006

I'm trying to test merge replication, but am unable to create the publication, the snapshot agent keeps failing. The error I get from replication monitor is "Script failed for Table <tablename>"

Our database has quite a few XML datatype columns, when I take out the xml columns the snapshot succeeds OK, but fails with the xml column.

I created a simple table containing a varchar column and xml column. I created this table in our database, and in Adventure Works. Was able to create a publication with this test table in the adventureworks database, but was unable to create publication with this table in our database. The snapshot failed with error "Script failed for table testtable". I removed the xml column on the test table in our database and was able to create the publication successfully. Creating a publication in the adventureworks database with an xml column has no problems, but does in our database.

Anyone have any ideas?

View 4 Replies View Related

Creating Triggers In Transactional Replication On The Subscriber Side

Jun 17, 2007

Hi all

i have setup default transactional replication using locat distributor scheme. I need to create triggers on tables at subscriber side. Can this be done using transaction replication?



View 2 Replies View Related

Appending Data On Same Clumn From Other Table

Apr 12, 2006


unfortunaly i have two tables where usernames are stored. in the one table the column is called 'remote_U' in the other 'remote_u' ... note the upper case

so want to generate listof all usernames and retrieve them in a single a column.


i have tried joins and groups, but is not working. can i also do an alphabetical order after appending the second select? distinct is needed, 'cos one user may have more then one entry in the table, but may be also in both tables ....

View 4 Replies View Related

Appending And Deleting Entries In Table

Jul 11, 2014

I have a question on appending and deleting entries in mysql table. This is my sample table.

table name: details

id_name | model | mode | media| first | end | id | level |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |

[Code] ....

I have numerous entries of same id name belonging to same median number.However,I want to only retain the entries having the longest first and end position and discard the remaining entries

E.g. for id name ="PSK_30s1207681L002" AND median = 5 we have four entries

id_name | model | mode | media| first | end | id | level |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |

But I want to retain only the one having longest first and end points ie

| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |

Here ,since for id name ="PSK_30s1207681L002" & median = N we have only 1 entry I want retain that one.

My final output in mysql table should look like this.

id_name | model | mode | media| first | last | id | level |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 3 | 14033 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | T0 | 15550 | 16050 | 6255 | q |
| PSK_30s1189731L001 | -1 | 1 | 3 | 999 | 7531 | 9808 | c |
| PSK_30s1189731L001 | -1 | 1 | 5 | 6609 | 8257 | 9808 | c |

[Code] ....

Is there anything that I could do to append it?

View 1 Replies View Related

Appending Data To A Table But Not Duplicates

Jul 23, 2005

Hiya everyone,I have two tables in SQL 2000. I would like to append the contents ofTableA to TableB.Table A has around 1.1 Million Records.Table B has around 1 Million Reocords.Basically TableA has all of the data held in TableB plus 100,000additional records. I would only like to import or append these newadditional records. I have a unique index already setup on Table B.Any ideas pretty pretty please?Paul.Ps. (Have been messing around with DTS but get a unique violation error- Which is kinda what I want I guess, but would like SQL to ignore theerror and only copy the new data - if only)

View 9 Replies View Related

Appending Records To The Existing MS SQL EXPRESS SERVER Table.

May 19, 2007

Dear All,I am Using  MS SQL EXPRESS SERVER .I have installed all tools available to Express Edition site. Now I have created my database on this .I have imported a table from my MS ACCESS database (Using ODBC Datasource).This table contains 10,000 records ,Now I want to append 1 more access Table(5500 records) to the existing table   having same fields.How to do this.Can any body tell me? Thanks and Regardsmukesh 

View 4 Replies View Related

SQL 2012 :: Creating Snapshot From Mirror Database Failed - Could Not Redo Log Record

Sep 1, 2015

Process to create snapshot failed (twice per day snapshot of the database is being created for some people to work on). Morning snapshot worked fine but the afternoon one failed. The snapshot is being created from Mirror database. I used this code:

(name = N'DB',filename = N'D:SnapshotDB.SQLSnapshot' )
,(name = N'indexes',filename = N'D:SnapshotDB_indexes.SQLSnapshot' )

And the error message i get

Msg 1823, Level 16, State 6, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 7, Line 1
A database snapshot cannot be created because it failed to start.
Msg 3456, Level 21, State 1, Line 1
Could not redo log record (202011:19306:2), for transaction ID (0:0), on page (1:1823948), allocation unit 281474979397632, database 'DB_snapshot' (database ID 6). Page: LSN = (201954:220201:1), allocation unit = 281474979397632, type = 1. Log: OpCode = 4, context 18, PrevPageLSN: (202010:23679:1). Restore from a backup of the database, or repair the database.
Msg 3313, Level 21, State 1, Line 1

During redoing of a logged operation in database 'DB_snapshot', an error occurred at log record ID (202011:19306:2). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.

What might be causing this error? Mirroring seems to be working OK. status is "Synchronized"

View 1 Replies View Related

Snapshot Agent Fails On Table With XML Datatype : Script Failed For Table 'dbo.TableName'

Sep 11, 2007

SQL Server 2005 SP2 Error: Script failed for Table 'dbo.TableName'

I keep getting this error when trying to generate a snapshot for transact replication.
Here is the publishing table schema:

CREATE TABLE [dbo].[viMediaPlaylist](
[MemberID] [bigint] NOT NULL,
[xmlPlaylist] [xml](CONTENT [dbo].[viMediaPlaylistCollectionSchema]) NOT NULL,
[MemberID] ASC

I tried the following:
#1)Create table schema at the subscriber manually, and change package to Keep object as is. Got the same error during snapshot.

#2Filtered replication to NOT include XML column. This worked, and no error was generated, replication was up and running.

So my question is, what is the problem with XML column being replicated?
Any ideas how i can make this work? At this point i'm not sure what the problem is with the xml column, but it seems like ti tries to run schema script, but i'm not asking it to create the schema i already did it myself. Thanks!

Here is a log dump from the distributor:

2007-09-11 16:40:14.56 SQL Command dump
2007-09-11 16:40:14.56 ================
2007-09-11 16:40:14.56 Server: SQL02
2007-09-11 16:40:14.56 Database: Video
2007-09-11 16:40:14.56 Command Text: sys.sp_releaseapplock
2007-09-11 16:40:14.56 Parameters:
2007-09-11 16:40:14.56 @Resource = SQL02-Video_viMediaPlaylis-71
2007-09-11 16:40:14.56 @LockOwner = Session
2007-09-11 16:40:14.56 @DbPrincipal = db_owner
2007-09-11 16:40:15.17 [0%] The replication agent had encountered an exception.
2007-09-11 16:40:15.17 Source: Unknown
2007-09-11 16:40:15.17 Exception Type: Microsoft.SqlServer.Management.Smo.FailedOperationException
2007-09-11 16:40:15.17 Exception Message: Script failed for Table 'dbo.viMediaPlaylist'.
2007-09-11 16:40:15.17 Message Code: Not Applicable
2007-09-11 16:40:15.17
2007-09-11 16:40:15.17 Call Stack:
2007-09-11 16:40:15.17 Microsoft.SqlServer.Management.Smo.FailedOperationException: Script failed for Table 'dbo.viMediaPlaylist'. ---> Microsoft.SqlServer.Management.Smo.UnsupportedVersionException: Either the object or one of its properties is not supported on the target server version.
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.GetTypeDefinitionScript(ScriptingOptions so, SqlSmoObject oObj, String sTypeNameProperty, Boolean bSquareBraketsForNative)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.UserDefinedDataType.AppendScriptTypeDefinition(StringBuilder sb, ScriptingOptions so, SqlSmoObject oObj, SqlDataType sqlDataType)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingOptions so)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingOptions so)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingOptions so, StringBuilder sb, ColumnCollection columns, IndexCollection indexes)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingOptions so, StringBuilder sb)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingOptions so)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17 --- End of inner exception stack trace ---
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
2007-09-11 16:40:15.17 at Microsoft.SqlServer.Replication.AgentCore.Run()

View 6 Replies View Related

Guidelines For Creating A Database Snapshot On A Mirror Database

Nov 24, 2006

Hi guys, can I know the steps on creating a database snapshot on a mirror database? Thx for the assistance. :)

Best Regards,


View 1 Replies View Related

Problem When Applying A Snapshot When Tables Have Been Updated During Snapshot Generation

Jun 20, 2007


I seem to have a strange problem when applying a snapshot when the tables in the publication have been updated while the snapshot was being generated.

Say for example there is a table called RMAReplacedItem in the publication. When the snapshot starts being applied to the subscriber, a stored procedure called sp_MSins_RMAReplacedItem_msrepl_css gets created that handles an insert if the row already exists (ie it updates the row rather than inserting it). However, after all the data has been loaded into the tables, instead of calling this procedure, it tries to call one called sp_MSins_RMAReplacedIte_msrepl_cssm - it takes the last letter of the table name and adds it to the end of the procedure name.

The worst part is that this causes the application of the snapshot to fail, but it doesnt report what the error is, and instead it just tries applying the snapshot again. The only way i have managed to find which call is failing is to run profiler against the subscriber while the snapshot is being applied and see what errors.

I have run sp_broswereplcmds and the data in there is what is applied to the subscriber - ie the wrong procedure name.

All the servers involved are running sql 2005 service pack 2. The publisher and subscriber were both upgraded from sql 2000, but the distribution server is a fresh install of sql 2005.

View 1 Replies View Related

Transactional Replication: Dropping A Replicated Table.

Jan 19, 2007

For SQL 2005 transactional replication I have 1400 articles (tables, views, functions, sp) that are all replicated.

Will SQL 2005 allow me to drop a table from from the publication and drop it from the publication database that is referenced by exising views or sps that are replicated or does it somehow check that?


View 1 Replies View Related

New Table Added To P-t-p Transactional Publication Does Not Replicate - Why?

Feb 15, 2007


We are new to replication and are testing it in our development environment. We have a peer-to-peer transactional publication on our three servers. The single table in the original publication replicated fine to the two subscribing servers. We next added a new table (article) to the publication. Adding it to the original publication worked fine but the table did not replicate to the other servers. (We previously had changed the schema of the original table and the schema changes replicated properly.) We attempted to recreate the snapsnot using the "View Snapshot Agent Status" option. Clicking the Start button resulted in the display of this message: "[0%] A snapshot was not generated because no subscriptions needed initialization."

This seems odd because a new table was added to the publication and Microsoft help states that the snapshot must be rebuilt. I have read other topics that refer to a @immediate_sync property that must be set to zero. I'm not sure if this is our problem or even how to set this value. Meanwhile, the other servers, as viewed through the Replication Monitor, are complaining that their snapshots do not match the publication snapshot.

Can someone point me in the right direction?



View 1 Replies View Related

Copyrights 2005-15, All rights reserved