Cann I Add New Column In Subscription Database Table
Aug 13, 2007
Hi
I am learning Sql server 2005 replication.
My requirement is.
1.) Subscribe a Master database
2.) I have to add a timestamp column in every table of subscriber database ( Note: the column will not be in publication database)
I have two sql server
server 1 : I made Merger publication
server 2 : I made subscription of the same
But when i tried to create a column in the Subscriber database table its giving the following error
Validation warnings
warnings were encountered during the pre-save validation process, and might
result in a failute during save. Do you want to continue attempt to save?
''Table1' table
- Error validating the default for column 'rowguid'.
I clicked "Yes"
Post -Save Notication
Errors were encountered during the save process. Some database objects were not saved.
'Table1' table
- Unable to modify table.
The DDL statement cannot be performed at the Subscriber or Republisher.
The DDL operation failed inside merge DDL replication manipulation.
The transaction ended in the trigger. The batch has been aborted.
clicked ok
User canceled out of save diaglog
Can i add a new column to subscriber database table if so please guide me?
View 5 Replies
ADVERTISEMENT
Mar 15, 2004
We are running SQL server 2003 with SP3. I'm trying to
shrink a data files with the emptyfile option so I can
eventually remove the file using the alter database
command. However, I get the following error message when I
run the alter database command:
Error: the file PRADATA4 cannot be removed because it is
not empty.
The file that I'm trying to remove still has 62 extents on it.
I looked MS Knowledge base 254253 and 279511 on this problem but they say it is corrected by SQL server 7.0 with service pack 3.
commands that I'm running are as follows:
1) USE PRA
DBCC TRACEON(8901)
DBCC SHRINKFILE ('PRADATA4', EMPTYFILE)
DBCC TRACEOFF(8901)
2) USE PRA
GO
ALTERDATABASE PRA
REMOVE FILE PRADATA4
GO
Can anyone help?
Thanks
View 8 Replies
View Related
Aug 13, 2015
We have our replication environment with a separate SQL 2014 publisher, distributor and subscriber. We have a table named DPSP on 14 databases which is to be published via transactional replication to 1 subscription database.Â
The source tables are identical in name, schema and clustered index. We have created 14 tables in the subscription database with a suffix to the name (see below):
Â
Each table in the subscription database has its own uniquely named primary key and clustered index:
Below are the settings for one of the published article:
The same process is repeated for each DPSP table created in 14 separate publications. Snapshots are generated and subscriptions created successfully. After some time, the log reader starts to show the following errors in replication monitor:
2015-08-12 21:48:24.156 7 transaction(s) with 11 command(s) were delivered.
2015-08-12 21:49:04.233 5 transaction(s) with 7 command(s) were delivered.
2015-08-12 21:50:04.355 No replicated transactions are available.
2015-08-12 21:50:09.378 13 transaction(s) with 17 command(s) were delivered.
2015-08-12 21:51:12.168 No replicated transactions are available.
2015-08-12 21:52:12.306 No replicated transactions are available.
2015-08-12 21:52:32.337
[Code] ....
View 3 Replies
View Related
Sep 26, 2007
28638
SSCE_M_DUPLICATETABLE
Existing subscription already contains table included in the new subscription.
What are the possible causes of this merge replication error?
Could it be caused by a SQL Server Compact Edition User trying to sync their .sdf file after their subscription has already expired on the SQL Server?
Would you expect to see a different message if a SQL Server Compact Edition user tried to sync a subscriber database (.sdf file) with merge replication if it's been longer than the subscription retention period since their last sync?
View 6 Replies
View Related
Jan 10, 2001
Hi Guys,
It sounds unbelievable that I can not schedule the DTS packages I created either on my local mechine or SQL server. I have the SA permission for all SQL servers and I can run the DTS packages manually in the EM or run it on DOS command line using dtsrun utility.
The error message I got says: "Microsoft SQL-DMO (ODBC SQL State: 42000)
[Microsoft][ODBC SQL Server][SQL Server] login failed for user '(null)'. Reason: not associated with a trusted Server connection.". I have the SP2 installed in all the SQL Server and my local mechine. All SQLAgents have Domain Admin permission. I just cound not figure out where that user 'null' come from.
I really appreciate that anyone can give me some hints to make the DTS scheduling work. Thanks!
Lunjun
View 4 Replies
View Related
Mar 17, 2008
HI
i am trying to take a backup on Network drive but i am getting following error message. althought when i try to type the address in window explorer. i can access the specific folder. so that there shouldn't be network security issue.... and even thought the backup device name is correct.
when i try to take backup on local disk, it work's fine....
Executed as user: NT AUTHORITYSYSTEM. Cannot open backup device 'FullBackup(\0.0.0.0SQL_BackupsFullBackupsFullBackup.bak)'. Operating system error 5(error not found). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
View 6 Replies
View Related
Nov 17, 2006
hi, can anyone explain, how to execute package with sql server agent:
the problem is::
i made a integration service package (in visual studio 2005). When I 'm in visual studio and do "execute" the package it works well. Then I build a dtsx file. When i double click on it, it opens and if i do execute, it works well.
I want to execute the package every week automatically. I write a job in SQL server agent, in Steps I choosed Type=SQL server Integration service packege, then General->packacge source -> file system, and then wrote where the dtsx file is (which was built). "Run as" I choose my user name.
then i'm trying to start a job, but it fails, and writes "The package execution failed."
What I'm doing wrong.
(sorry for my english)
View 2 Replies
View Related
Oct 1, 2001
How do I copy a column(or colums) from a table in one database to another table in a different database
View 1 Replies
View Related
Dec 14, 2005
I always create database tables with an id column with an autoincrementing number.
Does a table actually need this or can I remove this field if I don't refer to it?
thanks
Jon
View 5 Replies
View Related
Feb 13, 2015
I have researched how to put a URL column in my SQl Server 2008 database and table. I have tried varchar(max) and varchar(1000) and then done what I found online by inserting into the url with ' ' around it and then I ODBC into Access and it does not look like a link. Cannot link on it and have it go where it needs to. I have also tried this in the front-end application and it does not look like a link. It does not respond either.
View 4 Replies
View Related
Jan 17, 2007
Hi,
I need to provide a report that lists the databases in each instance, then the tablenames and the number of rows in each table.
Is there any easy way to do this in SQL 2005?
Cheers
Gregg
View 8 Replies
View Related
May 6, 2007
Hi,
I have a set of records in database table and I want to update one column to be the same for all of them.
Can you suggest code solution?
View 1 Replies
View Related
Oct 3, 2007
I have the folowing databases DB1,DB2,DB3,D4,DB5........
I have to loop through each of the databases and find out if the database has a tablename with the word 'Documents'( like 'tbdocuments' or 'tbemployeedocuments' and so on......)
If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database.
can someone show me the script to do it?
Thanks.
View 6 Replies
View Related
May 22, 2008
how can setup constraint for the column in table in database ??
and what will sould write in the (BLOCKED EXPRESSION field ??
View 5 Replies
View Related
Aug 18, 2006
hi
i have over 200 tables with all same column and data type locate all over the server(20)different database. so i have table call Tname to stored all the link like (databasename.dbo.tablename) so my job will call the Tname table to use cursor to insert records. but the problem is there's one or more table's column name K datatype has been modify by someone else. so is it cause the job fail.
if there a way or SQL statment that i can use Table Tname to see which one is missing column name K? and is there a way i can print out all the datatype and len for all the table column k? please help thanks
View 2 Replies
View Related
Aug 3, 2007
Can anybody tell me how to find out whether a particular column exists in any of the tables of a database and if it does, display the table names?
Thanks
View 3 Replies
View Related
Feb 10, 2007
Hello,
XPSP2
VB 2005 Express
Jet/Access database
One of the Forms of these application display a Jet database table. Why querries Min, Max, Avg do not work?
Thanks,
View 8 Replies
View Related
Feb 1, 2008
I am working with a table in SQL server. I have a column that I want to designateas an identity column. I am not able to do this, because the field for "Identity Specification" is not editiable.
What I did was I went to sql server, right clicked and selected "Modify".The column properties dialog box/edit grid is then displayed with attributesthat I can modify.
There are two major nodes in this dialog box. One is named "General" and the otheris named "Table Designer". I expand the "Table Designer" node and then go to the node labeled "Identity Specification" It is here where I would like to edit thevalues.
The values that are listed for edit are listed below. BUT, the problem is thatI can place my cursor in those fields, but I am not able to change/edit them.Can anyone tell me what the problem is here? and how I can fix it?
+Identity Specification (Is Identity) Identity Increment Identity Seed
View 3 Replies
View Related
Feb 7, 2008
I posted a thread in the Getting Started forum about how to make a counter for maximum tickets : http://forums.asp.net/t/1215258.aspx
but maybe this is a more appropriate forum for this subject.
In a school project, we are making a website for a fictional concert/festival (using Visual Studio 2005, C#). On that site users can register and order tickets.
We have access to an SQL-database, by the way, where we can create tables etc.
We want the maximum amount of tickets to be 10000 per day. The festival is supposed to last from friday to sunday.
What would be the best way to do this programatically? The counter should maybe be in an own database table?
View 3 Replies
View Related
May 22, 2008
Can somebody tell me how to transfer table and column descriptions from SQL 2005 database to dimensions and attributes in Analysis Services?
Thanks!
View 2 Replies
View Related
Dec 20, 2006
I have kind of unique situation. I am running Merge replication. In one of my publications I am only publishing procedures/functions/views. By design, these do not change that often, but when a programmability object changes, it is scripted in a way so that:
1. The article is dropped from the publication
2. the object is then changed
3. The article is added back to the publication
My question is: Is there a table or view that the subscriber or publisher can see that could tell me if reinitialization needs to occur. I am looking at adding an automated script at the subscriber that makes the determiniation and automatically reinitializes the subscription. My alternative is to force the subscriber to reinitialize every time when synchronizing with this publication, even if nothing has changed because the process has to be automated.
Thanks,
Bill
View 8 Replies
View Related
Jul 31, 2007
Hello everyone.
We've been using replication since a while now and I just discovered that some user are not getting all the data they should. Let me explain the way the whole thing is configured for us.
We have a few tables that are replicated using the Merge process with filtering on the data using SUSER_SNAME() function. My users are salesrep using Laptop and they are synchronizing on demand. The merge process is configured so that a row will go to only one subscription --> Each rep has their owns customers and related tables informations.
I noticed that some users do not have locally all data (from their customers) from some tables while they should. I didn't checked in details but it is possible that these users are new reps replacing other staff that left the company. Is it possible that the data they do not receive is marked as to be previous reps owner because the replication is set as "A row from this table will go to only one subscription" ? The new rep uses the same laptop, therefore the exactly same computer name, only the username changes.
Because of this, now I'm having a bunch of conflicts when replicating because we're generating some ID based on existing data when the user enter new records in some tables; since the data is not sitting locally for them, the ID generated is not the proper one and we now have two identical ID (one on the server and one locally) which cause the conflicts to occurs.
Any helps would be appreciated. Thanks.
View 1 Replies
View Related
Oct 30, 2015
I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB
01/01/11 100 5
02/01/11 102 4
Share Price....
Date CompA CompB
01/01/11 100 100
02/01/11 101 99
Event Data...
Date Company
01/01/11 CompA
02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance:
SELECT Event.Date, Event.Company
FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
View 1 Replies
View Related
Mar 6, 2006
Hi:
I am having lot of log problems with Subscription databases. Currently all my subscription databases are on Full recovery mode. I am thinking to change them to simple because I don't I will be doing point in time recovery of them.
Do the subcription databases have to be on Full mode? Can I change them to simple to keep my log small and then I do not have to backups of my logs also? Please let me know.
Thanks
View 7 Replies
View Related
Sep 8, 2006
Hi,
I have a simple enough task to complete that I can€™t seem to find the answer to.
The task is this €“
Select table x from the database and write it to a flat file complete with that tables column headings.
Now I€™ve managed to set up an ole db datasource and selected the table and I€™ve also linked it to the flat file output. So now I can generate a flat file from the database. However no column headings appear in the flat file.
I can€™t seem to find anywhere (like a checkbox) that will also output the column headings to the flat file.
Now I can add in Headings manually in the properties of the Flat File Destination object but the columns that appear in the flat file don€™t appear to be in the order that I requested them in the SQL.
So the question is how do I automatically have the column headings appear for flat file output (ideally without me having to manually add them in).
If it can€™t be done and I have to use a vb.net script instead then would anyone have an example script of how to do it?
Thanks in advance for anyone who manages to answer this.
Matt.
View 6 Replies
View Related
Feb 11, 2008
It seems to me that if a scheduled SSRS report subscription fails ( Status Message - An error has occurred during report processing) , that I actually need to delete the subscription and reconfigure it from scratch. The scheduled job doesn't try to run again automatically (say the next Monday on a weekly Monday schedule).
Is there a way to "reset" a failed subscription without have to recreate the entire subscription?
Thank you
Devon Kyle
View 5 Replies
View Related
May 25, 2007
Hi,
On the development environment i've got replication running with no trouble but when deploying in the production environment i get the error below
Has anyone seen this before and solved it?
Another question: How to enable verbose history when using the MS SQL Server Management Studio to create a subscription.
Any help is welcome,
Ralf
TITLE: Microsoft SQL Server Management Studio------------------------------A call to SQL Server Reconciler failed. Try to resynchronize.HRESULT 0x80004005 (29006)The schema script 'd:MSSQL
epldatauncTEST_KANSCE TEST20070522165445 blNaw_2.sch' could not be propagated to the subscriber.HRESULT 0x80070003 (0)The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write.HRESULT 0x80045017 (0)------------------------------BUTTONS:OK------------------------------
View 6 Replies
View Related
Feb 2, 2007
Hi,
We are Using Transactional Replication with Updatable Subscription in SQL Server 2005 SP1.
Subscription Type : Pull Subscription
Mode : Continuous Running Mode
Conflict Resolution Policy : Publisher Wins.
I have a table "Sample" (which is part of replication) and it has got 3 triggers. All the triggers are set NOT FOR REPLICATION.
The first trigger Updates a column of the "Sample" table in which i inserted a record.
The second trigger inserts record in to another database table and also updates a column of the table "Sample".
The third trigger does not affect any tables, it is written for some manipulations with variables.
In this scenario when I insert a record in the Sample table of the subscription database, that record is visible in the table. But during replication, it shows conflict in the Conflict Viewer and removes the record from the Sample table of the subscription database. The record is not replicated to the publisher and the other Subscriber also.
But when I comment any one update in either the first or second trigger, the insert works fine without any conflict.
Is there any issue with firing two triggers in replication which is updating the same table? I also suspect the Order of Commands moving to the Publisher from the MSReplication_Queue table, becoz the conflict viewer shows the subscriber as the Conflict loser.
Is there any issue with msrepl_tran_version, Since the conflict is decided based on this id??
It would be helpful if u could reply soon.
Thanks and Regards,
Swapna.B.
View 12 Replies
View Related
Jan 16, 2008
I have scaled out an application so that multiple servers contain identical production databases (various clients use different server databases.)
I have replication configured so that the same tables from these different databases are published into single subscription tables (to provide data warehouse and reporting across all production databases).
The publications are set up to replicate ddl.
Everything works wonderfully except I have just discovered the need to alter the schema of a table that is within the production databases.
When I apply a script to change the ddl in all the publication databases I am getting errors in replication.
I understand that I am NOT supposed to change an uderlying subscription table (this should be done through replication of the schema.)
I suspect that the replication error is caused when the schema changes are replicated from the very first Publication update. What I'm effectively doing is changing the subscription table independently (and prior to) changing the schema of the OTHER (subsequent) publication table schemas.
I experimented and unsubscribed my target table from all but ONE publication, and then my schema change is fine and replication is happy. But when I have multiple publications feeding the subscription I am not able to propagate the schema change across replicating publications without breaking replication.
I am sure that other people out there must have similar situations as mine, where multiple publishers update a single subscription. How are you able to update schema successfully?
Many thanks in advance,
Normajean
P.S. I have posted the script that I am running below....
And here is the error I get in Replication Monitor when I try to run the script:
The index 'Idx_FacilityStayPayer_facStayID_PayerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074)
Get help: http://help/5074
The index 'Idx_FacilityStayPayer_payerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074)
Get help: http://help/5074
ALTER TABLE ALTER COLUMN payerID failed because one or more objects access this column. (Source: MSSQLServer, Error number: 4922)
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-- make the facilityStayPayer.payerID column non nullable
-- drop all of the foreignKeys and indexes that reference the column
IF EXISTS (select constraint_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where table_name = 'facilityStayPayer'
and constraint_name = 'FK_FacilityStayPayer_Payer')
BEGIN
ALTER TABLE dbo.FacilityStayPayer
DROP CONSTRAINT FK_FacilityStayPayer_Payer
END
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_payerID')
BEGIN
DROP INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF )
END
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_facStayID_PayerID')
BEGIN
DROP INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF )
END
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'UNQ_FacilityStayPayer')
BEGIN
ALTER TABLE [dbo].[FacilityStayPayer] DROP CONSTRAINT [UNQ_FacilityStayPayer]
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
-- alter the column
ALTER TABLE FacilityStayPayer
ALTER COLUMN payerID UNIQUEIDENTIFIER not null
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
-- add all of the indexes and foreign keys back
ALTER TABLE dbo.FacilityStayPayer WITH NOCHECK ADD CONSTRAINT
FK_FacilityStayPayer_Payer FOREIGN KEY (payerID)
REFERENCES dbo.Payer(payerID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
CREATE NONCLUSTERED INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer]
([payerID] ASC)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
CREATE UNIQUE NONCLUSTERED INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer]
([facStayID] ASC,
[payerID] ASC)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
END
GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u')
BEGIN
ALTER TABLE [dbo].[FacilityStayPayer]
ADD CONSTRAINT [UNQ_FacilityStayPayer] UNIQUE NONCLUSTERED
([facStayID] ASC, [payerID] ASC)
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
END
GO
View 1 Replies
View Related
Sep 27, 2006
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
View 2 Replies
View Related
Mar 31, 2015
I need to move the log file of a disk and onto another disk. The log belongs to a merge subscription database.
I was going to stop/disable the merge jobs on the distributor, detach the database, move the log file to another drive, reattach, and enable the merge jobs on the distributor.
Does that sound ok, or should I employ some other method.
View 6 Replies
View Related
Oct 19, 2015
We migrated ssrs from 2008 to ssrs 2014.after that we need to move only subscriptions to  New 2014 report server DB.
How to proceed that.
I am trying to insert record in DB. But subscription ID is not identity column. How to do this.
Can I place old Subscription IDS in New 2014 reporting DB?
View 2 Replies
View Related
Sep 15, 2006
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
View 6 Replies
View Related