Hi all,
I am doing a replication of a SQL Server 2005 database from one server to another server, everything worked fine. Now, when I added some fields to this database, the replication stopped. can I do replication of a schema of a SQL database? how? Should the replication work even if I add some fields.
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies View RelatedI see from help books that table changes are not allowed using Enterprise Mgr. after a publication exists. The only way to make db changes is to use T-SQL. I found the correct sp to make changes below.
I am using
sp_repladdcolumn <tablename>,<columnname>,<datatype(25)>
sp_repladdcolumn does not allow datatype length to be supplied. Result defaults to 1 and sets to NULL.
I need a variable length on my datatype. Is this a syntax error on my part or am I not able to supply this parameter?
How to accomplish?
thx..bt
SQL Server 7.0 (Service Pack 1 )
A table is currently set up with Transactional Replication. A new field needs to be added to the table. The system needs to continue to be live. What is the best way to handle this kind of situation?
The database model cannot be frozen once replication is in place, and a fresh snapshot cannot be taken each time this occurs since this is very timely on large tables, and errors could occur with other tables replicating since there are foreign key constraint in the Target database.
I haven't been able to find a way to be able to continue replicating the old records which may have been in the log during the addition of the field, as well as the new records with the new field. I must be missing something, since the model must be able to be altered once replication is in place, without the shuting down of the entires system.
Hi
If a column is added to a table which is a published article in a transactional replication, is it sufficient to do the following?
- drop subscribers
- amend the publication by dropping the affected table, then re-adding it
- re-pushing to subscribers
Or do I have to drop the entire publication and recreate it?
TIA
Hi there,
I wonder if I can ask for some advise? I wish to use SQL 2005 and merge replication but I have a few concerns over the schema changes that I am allowed to do on the publisher.
Ideally I want sql replication to push all my schema changes from the publisher to the subscribers but I don't know if it can. or even if it is recommended.
I see the BOL suggesting that schema repl. was mainly intended for ALTER type of statemenets, but can I use it for CREATE and DROP type DDL too?
For example, if I add a new table at the publisher I presume I have to add a new article too for this table. Then how can I create a new snapshot? Is this even the right way of doing it? Similarly if I want to DROP a table at the publisher I'm guessing I have to also delete any existing articles too, then recreate the snapshot.
Are there better / safer ways of doing schema changes? Perhaps I'm best disabling publishing and asking each remote site / db to update their schema manually and then re-enabling the publisher?
Any help would be much appreciated.
Regards.
Hi I have to questionsproblems I would need help with. First I am creating a three level merge replication. I mean I have a main
publisher with its distributor, a subscriber/publisher and also with
it's own distributor and lastly a subscriber.
I start by creating the relation between subscriber/Publisher and the
subscriber and than I move up to the main Publisher and
SubscriberPublisher relation. After this second merge I get the
following errors and the articles in my SubscriberPublisher are
lost...
The merge process could not retrieve article information for
publication 'I-Test-Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147201017)
---------------------------------------------------------------------------------------------------------------
Cannot insert the value NULL into column 'tablename', table
'@tmp_table'; column does not allow nulls. INSERT fails.
(Source: DEVSERVER3MSDE (Data source); Error number: 515)
---------------------------------------------------------------------------------------------------------------
Failed to allocate new identity range.
(Source: DEVSERVER3MSDE (Data source); Error number: 21197)
---------------------------------------------------------------------------------------------------------------
Secondly
I have set up a two level merge replication with a publisher and a
subscriber and had modified the structure of a table in the publisher
with sp_repladdcolumn. During the day all was fine I continuously
tested it by adding more columns, droping columns and testing the
replication itself with data entry, but when I got back
the next morning my test table and all other table I presume had lost
its data and structure. The conflict table for the corresponding test
table had kept it's structure but no data were present since no
conflict had occured. This is a recuring problem that some colleagues
had.
Does anyone have any idea what might cause this problem and why it
happenned.
Thanx
Can you use Transactional replication for exchange data between tables where schema is not matching?
------------------------
I think, therefore I am - Rene Descartes
How can I keep schema changes in the subscriber's database when I replicate the snapshot from the publisher?
I just want to move data from a remote server, but it seems that the tables are being dropped which is not good as we use the subscriber as a development box.
Please advise
I need to setup snapshot replication for 1 large table.
Source table: SourceDatabaseName.dbo.MyTable1
Target table: TargetDatabaseName.CustomSchema.MyTable1
In the above example, how can I specify the target DB's CustomSchema name (it's currently creating dbo, not CustomSchema)
How do we know our schema changes will propagate to subscriber without breaking the replication? Is there any t-sql command to find out the option true or false?
View 2 Replies View RelatedHi all.
I 'm trying to set up merge replication between two servers that have the same schema databases. The two database have the majority of there data the same but as well as data inserted at a later time independently on the two servers. (The two servers were connected in a merge replication scheme that failed at some later time and replication was paused, but users continout to insert data indepentedly at the two servers.)
I need to get them up and running.
I cleaned replication at both servers, I recreated the publication at the puplisher distributor and all is fine.
When I create a push subscription to the subscriper I get the error invalid column name 'rowguidcol' .
I so far managed to have merge replication running on two identical databases (schema and data).
Just some thoughts. After some reading I found that it might be related to identities and identity range or indexes. I set the identity seed and increment at 2, 2 at publisher and at 1,2 at subscriper. (On the same tables at puplisher and subscriber.) Is that ok? is that the way to do it?
Digging a bit more Using the SQL Profiler I can locate the error to happen when sp_MSaddmergetriggers executes.
Thanks allot for any help
Version.
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I am using a merge publication set to synchronize schema changes. Why am I getting the following message when I try to sync after having added columns in the publication database using ALTER TABLE statements?
The schema definition of the destination table ... in the subscription database does not match the schema definition of the source table in the publication database. Reinitialize the subscription without a snapshot after ensuring that the schema definition of the destination table is the same as the source table.
Also, why doesn't reinitializing the subscription with @upload_first = false not fix the problem?
Hi
I need to setup transactional replication for a production server.
The tables (articles) at publisher being replicated can expect schema changes.
Is there any way to reflect schema changes at subscriber using transactional replication.
Early Thanks,
Salman Shehbaz.
I'm trying to set up a merge replication. Publication is created successfully and snapshot also, but when I create pull subscription on subscriber server and merge agent starts, after some time I get an error message of this type:
The schema script '\ANILREPLDATAuncANIL_BEJK_BEJK20070625142735dl_HF_vMSCene_3836.sch' could not be propagated to the subscriber.
It seams there is a problem with certain Views and SPs, because tables are successfully created, and some Views and SPs also.
I tried to exclude problematic articles, but every time another one pops up. Up until now, I excluded 7 articles from publication (1 Stored Procedure and 6 Views) but I still get errors.
I gave up because I can't exclude half of the Views and SPs just to make it work.
Is there something that can be done to solve this problem?
Thanks!
Hi,
View 9 Replies View RelatedApart the IDENTITY property, what other properties or attributes are not transferred to the target schema?
I know that one can use NOT_FOR_REPLICATION for identities, but I am interested in a (complete?!) list of metadata objects that transactional replication *prefers* not to transfer across to the target by default.
TIA
Hi all,
I'm admitedly a bit new to the world of replication, so please bear with me. I've got two SQL Server 2000 servers running in different locations. Server A does transactional replication over a push subscription to server B. If I need to make a minor change to one of the replicated tables (for example, dropping a no longer used column or changing a varchar field's length) do I need to drop the subscription, make the changes and then re-initialize the schemas and data?
For minor changes, I really hate having to knock out the site runnign off server B while the subscription is re-initialized and data is bulk copied back over. If I want to just make the changes manually on both servers will that cause problems down the line?
Any help is greatly appreciated.
tia.
-m
Question re Merge rep (pull) and processing order.  We have a group of changes associated with an app upgrade, the scripts run fine on the publisher.   Part of the change includes creation of a new table , followed by altering a view to use new table.Following the change at the publisher, when the sync is kicked off from the subscriber, it fails - the alter of the view throws an'invalid object' error with regard to the new table. Seems as if the view alter is attempted before the dependant table has been created.Â
I have tried to amend the processing order of the view using sp_changearticle, which executes (quickly) with a 0 return code.But it is to no avail , the error still occurs.  is it possible to change the processing order for a view article , which will be applied to schema changes ? Have
I have a problem with sql server merge replication in sql server 2000.
If my db owner €œdbo€? and replication setup under €œsa€? account it works with out any problem. But when I use another db owner it can not work properly.
For an example I have customer table ([dbo]. [Customer]) When I setup merge replication under sa account it€™s work properly.
Again I was setup merge replication using another db owner ([INV]. [Customer]) It doesn€™t work.
can a sql server 2000 snapshot replication
solution handle a schema change.
as far as i know i can't drop a table involved in replication
I have Snapshot set up as a schedule task between server A and server B.
If I make changes to the db schema on server A - the changes are not replicated to server B.
Is this normal?
Do I have to restart the Sanapshot agent each time there is a schema change?
Thanks,
Bianca
Whenever I am doing reinitialization, publisher table schema is applying at subscriber level
I don't want to apply all changes ...
How can I skip the schema changes for transaction replication ...
Hi there,
We have a big problem here with merge replication, specifically whenever a schema change occurs. We are replicating schema changes, and triggers/stored procedures. The example is that we changed about 150 stored procedures and about 30 triggers. This is then replicated to the subscriber database (which is also a merge publisher for further remote systems that were offline at the time) over a 10Mb link - hardly low bandwidth. However, the replication process takes about an hour and a half - considering the SQL on the primary server took less than a minute to run this is a big suprise.
We've run a trace to see if we can identitfy what is going on. There seems to be a great number of calls to sp_MSunmarkschemaobject - we are still waiting for a trace to complete to fully analyse this however it looks like it calls this repeatedly for every stored procedure in the database. We are currently re-testing to one of the remote servers with the merge agent set to the slow profile (not much hope that this will alter the poor performance).
This task looks to be excessive - and certainly does not seem to function in a sensible manner. Has anyone else had similar issues or have any suggestions. This is very infuriating as it means that the servers are effectively offline for a minimum of and hour and a half (in fact the remoter servers take over 4 hours !).
Thanks.
I recently added a new user to my database. Now I want to delete that user, but I keep getting the error above. What do I need to do to delete my recently added user?
View 4 Replies View Related
The error message:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'TestID'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created.
All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed.
Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
I need to export a database, x, of a server, X, to another database, y, of a server, Y and I need export the database schema only, not include the data.
Does anyone know how to do that?
Many thanks for replying.
Trying to get my hands around all the new security features of SQL Server 2005. In Management Studio did something I don't know how to undo. I added a database role ReadOnlyRole and clicked the box next to db_datareader in the owned schemas box. Then I tried to remove the ReadOnlyRole and could not. How do I undo what I did? Is it possible?
The below is the TSQL that generates the my issue.
Use [master]
go
create database [test]
go
USE [test]
GO
CREATE ROLE [ReadOnlyRole]
GO
USE [test]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReadOnlyRole]
GO
drop role [ReadOnlyRole]
go
What would be the best way to get the Schema from a complete database.
Thanks.
hello,
here is my problem.
I have to rebuild database after crash. there is no backup. So
I did a bcp to get data from. But I do not have the original database so my question is how to get the full schema off the data base, tables,colomns,stored procedures etc...
thanks lot for any help.
Hello All,
My project uses MS SQL server database and is not too big database (have aound 200 table).
Now I have to create Database schema for my database as my project needs to be integrated with some other product.
I don't know much about database schema and how to start with it.
Can someone please give me some inputs on following:
1) What exactly database schema should include?
2) How should I start creating the schema for my database?
3) Are there any tools for doing this?
Thanks in advance
Hi. I would like to retrieve the table names of a database, the column names nad its contraints of each table in a database.. How can this be achieved???
View 14 Replies View Related