April 17, 2009 at 7:41 am
Hello Everyone,
I have setup the transaction replication on the following configuration.
1.Publishers (SQL 2000)
2.A remote distribution (SQL 2008)
3.A subscriber (SQL 2008)
In non-trusted domain or network.
There are two tables that have 387 columns in the Publisher database. When I setup the publications, it complained on those two tables. The publications can not have more than 254 columns.
So, I created Index view to split those two tables.
CREATE VIEW dbo.idx_Table_FirstView WITH SCHEMABINDING AS
SELECT
TABLE_A.Column1
……
……
TABLE_A.Column200
FROM dbo.TABLE_A;
Go
CREATE VIEW dbo.idx_Table_SecondView WITH SCHEMABINDING AS
SELECT
TABLE_A.Column201 ……
TABLE_A.Column387
FROM dbo.TABLE_A;
Go
Then, I recreated the publications that have index views and tables articles.
Run the initial snapshot, the Distributor to Subscriber history displayed errors:
Command attempted:
CREATE VIEW [t_TurbDataMWT92_EighthView] WITH SCHEMABINDING AS SELECT SiteID, NodeID, TStampOfMidPoint, ValidTo, rActPowDemStator_Max, rReactPowDemStator_Mean, rReactPowDemStator_SD, rReactPowDemStator_Min, rReactPowDemStator_Max, rFreqNet_Mean, rFreqNet_SD, rFreqNet_Min, rFreqNet_Max, rFreqStator_Mean, rFreqStator_SD, rFreqStator_Min, rFreqStator_Max, rPFNet_Mean, rPFNet_SD, rPFNet_Min, rPFNet_Max, rPrsGasPitBrake1_Mean, rPrsGasPitBrake1_SD,
(Transaction sequence number: 0x00002CF0000024F900C600000002, Command ID: 21)
Error messages:
Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL20164) Get help: http://help/MSSQL_REPL20164
Invalid object name 'dbo.t_TurbDataMWT92'. (Source: MSSQLServer, Error number: 208) Get help: http://help/208
Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL) Get help: http://help/MSSQL_REPL
Can anyone help and advice on this problems?
May you provide me the solutions?
Thanks in advance.
Edwin
April 17, 2009 at 8:03 am
Did you specified on the publication that the indexed view was to be replicated as "indexed view logbased" ?
I don't remember if 2000 supported that option.
Upgrading to 2005+ version the Publisher will remove the limitation and that in my opinion is the best solution.
I have seen other solutions but involve schema manipulations. Ex:
1. Split the table into two and create a view that joins them in a 1-to-1 fashion and add an instead-of-trigger on the view to handle data manipulation
2. Add a trigger to the table that maintains two separated tables. Don't publish the original and at subscriber create a view that joins these two tables.
* Noel
April 17, 2009 at 10:23 am
Hi Noeld,
I’m not familiar with indexed view logbased. Can you give me some hint?
How to implement the indexed view logbased?
Thanks in advance.
Edwin
April 17, 2009 at 11:31 am
Edwin (4/17/2009)
Hi Noeld,I’m not familiar with indexed view logbased. Can you give me some hint?
How to implement the indexed view logbased?
Thanks in advance.
Edwin
UI is probably not good for doing this so you should follow this sample script and it will get you there:
CREATE VIEW dbo.vw_sourcetable WITH SCHEMABINDING
AS
SELECT col1,col2,col3
FROM dbo.sourcetable
WHERE col1 = 1
CREATE UNIQUE CLUSTERED INDEX iv_sourcetable on vw_sourcetable(col1)
EXEC sp_addarticle @publication = N'dbname',
@article = N'vw_sourcetable', @source_owner = N'dbo', @source_object = N'vw_sourcetable',
@destination_table = N'destination_tablename', @type = N'indexed view logbased',
@creation_script = null, @description = null,
@pre_creation_cmd = N'drop', @schema_option = 0x00000000000030F1, @status =16
EXEC sp_addsubscription @publication = N'dbname',
@subscriber = N'destination_servername', @destination_db = N'destiantion_dbname',
@sync_type = N'automatic', @subscription_type = N'pull',
@update_mode = N'read only'
* Noel
April 17, 2009 at 11:33 am
Note that I used a 'pull' subscription and automatic synchronization those two can be different in your case but is all a matter of changing that to your needs.
* Noel
April 17, 2009 at 11:54 am
Noel,
I have setup a pull subscription.
Enclosed is my t-sql script to setup my remote distribution:
---Create an entry on the sysserver tables, mark it as the Distributor
--- and stores property information
--- heartbeat_interval parameter in minuts
exec sp_adddistributor @distributor = N'SERVER2\SQL2008SERVER',
@heartbeat_interval = 20,
@password = N'P@ssw0rd'
GO
exec sp_adddistributiondb
@database = N'SERVER2_Distribution',
@data_folder = N'D:\SQL_Data',
@data_file = N'SERVER2_Distribution.MDF',
@data_file_size = 20000,
@log_folder = N'E:\SQL_Log',
@log_file = N' FWSQL2008SERVER_Distribution.LDF',
@log_file_size = 5000,
@min_distretention = 0,
@max_distretention = 336,
@history_retention = 168,
@security_mode = 0,
@login = N'User1',
@password = N'P@ssw0rd';
GO
use [SERVER2_Distribution];
go
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\SERVER2\SERVER2_ReplData', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', N'\\SERVER2\SERVER2_ReplData', 'user', dbo, 'table', 'UIProperties'
GO
--- Configure a Publsiher to use a specified distribution database
exec sp_adddistpublisher
@publisher = N'SERVER2\SQL2008SERVER',
@distribution_db = N'SERVER2_Distribution',
@security_mode = 0,
@login = N'User1',
@password = N'P@ssw0rd',
@working_directory = N'\\SERVER2\SERVER2_ReplData',
@thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER'
GO
Can you share your t-sql transaction replication scripts? Please send to tjedwin@gmail.com
I will work on the Indexed View logbased and posted my update soon.
Best regards,
Edwin
April 17, 2009 at 1:05 pm
Edwin these script are independent of the use or not of a remote distributor.
These are just run at publisher on the published database.
After you run it. You only need to execute the snpashot agent so that the newly published indexed view arrives at the destination and you are all set.
We employ a set of scripts that generate out tsql on the fly based on a set of configuration parameters/files.
We don't save the actual tsql ever.
Hopefully you can get to the finish line. Keep us posted.
Good Luck
* Noel
April 17, 2009 at 4:01 pm
Hi Noeld,
The Publisher table with (367 Columns) has several primary keys.
Would you please check my t-sql code below:
CREATE VIEW dbo.t_TurbDataMWT92_FirstView WITH SCHEMABINDING AS
SELECT
--The Primary Keys SiteID, NodeID, TStampOfMidPoint, and ValidTo
SiteID, NodeID, TStampOfMidPoint, ValidTo,
xxxx_TStamp, TimeError_secs, Duration_secs, NumOfReadings,
PeriodID, rVibNacelleX_Mean, rVibNacelleX_SD, rVibNacelleX_Min
continue n-tables
WHERESiteID =1
CREATEUNIQUE CLUSTERED INDEX iv_dbo.t_TurbDataMWT92 on
dbo.t_TurbDataMWT92_FirstView(SiteID) ;
go
Thanks,
Edwin
April 17, 2009 at 7:00 pm
Hi Noel,
I created two index views:
-- t_Table_A_FirstView
-- t_Table_A_SecondView
on the Publisher database instance.
Next, I create a test publications with one article.
Then, I run the following t-sql scripts:
EXEC
sp_addarticle @publication = N'SiteData',
@article = N't_TurbDataMWT92_FirstView',
@source_owner = N'dbo', @source_object = N't_TurbDataMWT92_FirstView',
@destination_table = N'destination_tablename', @type = N'indexed view logbased',
@creation_script = null, @description = null, @pre_creation_cmd
= N'drop', @schema_option = 0x00000000000030F1, @status =16
go
Run into some errors: Cannot create article 'TABLE_A_FirstView'. All articles that are part of a concurrent synchronization publication must use stored procedures to apply changes to the Subsciber.
Question on your @destination_table= N'destination_tablename'
Is the "destnation_tablename" parameter to be supplied?
Did I need to create table in Subscriber database?
Thanks,
Edwin
April 20, 2009 at 9:50 am
Edwin (4/17/2009)
Hi Noel,I created two index views:
-- t_Table_A_FirstView
-- t_Table_A_SecondView
on the Publisher database instance.
Next, I create a test publications with one article.
Then, I run the following t-sql scripts:
EXEC
sp_addarticle @publication = N'SiteData',
@article = N't_TurbDataMWT92_FirstView',
@source_owner = N'dbo', @source_object = N't_TurbDataMWT92_FirstView',
@destination_table = N'destination_tablename', @type = N'indexed view logbased',
@creation_script = null, @description = null, @pre_creation_cmd
= N'drop', @schema_option = 0x00000000000030F1, @status =16
go
Run into some errors: Cannot create article 'TABLE_A_FirstView'. All articles that are part of a concurrent synchronization publication must use stored procedures to apply changes to the Subsciber.
Question on your @destination_table= N'destination_tablename'
Is the "destnation_tablename" parameter to be supplied?
Did I need to create table in Subscriber database?
Thanks,
Edwin
Hi Edwin,
I was a bit busy to reply but here I am again.
"destination_tablename" is a parameter that you supply and it will be created automatically on the subscriber when you run the snapshot.
If it exists it will be dropped and recreated.
I'll get back to you on the error later today.
* Noel
April 21, 2009 at 2:46 pm
Noel,
The Publisher tables have more than single primary keys.
For example: Table A has
S_ID, N_ID, TStampPoint, DayStampPoint Keys.
How should I create the Unique Clustered Index?
Should I those all keys or use one of those keys ?
Thanks,
Edwin
April 24, 2009 at 12:12 pm
Edwin (4/21/2009)
Noel,The Publisher tables have more than single primary keys.
For example: Table A has
S_ID, N_ID, TStampPoint, DayStampPoint Keys.
How should I create the Unique Clustered Index?
Should I those all keys or use one of those keys ?
Thanks,
Edwin
Sorry I have been busy.
You must use all the columns. Is the only way you are going to guarantee uniqueness.
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply