We've set up transactional replication to continue on data consistency errors and we do see in the replication monitor that records are skipped at the subscriber. Our subscriber is SQL2K and our publisher is SQL2K5. How can we see the records that were skipped and why they were skipped.
View 4 RepliesI am using merge replication with automatic identity range management
I want to be able to give a disconnected subscriber a warning message if a certain percentage of the allocated subscriber identity range has been used
- so that they can make sure they reconnect soon to obtain a new identity range
I can get the current identity value OK, but I can't find a good way of finding the current permitted primary and secondary ranges
The information is available in the check constraint, but I don't really want to have to parse this!
CONSTRAINT [repl_identity_range_0DF907AC_40CF_4849_9BD0_2173C90A3805] CHECK NOT FOR REPLICATION (([ContactID]>(10000040000.)
AND [ContactID]<=(10000050000.) OR [ContactID]>(10000050000.) AND [ContactID]<=(10000060000.)))
I can't find any system view which will provide this information
Is there a recommended way of doing this?
Thanks
aero1
We have a filtered publisher (SQL Server 2012) with several pull and push subscriptions that have been functioning fine for years.
We have just added a new pull subscriber from a remote server. We configured an alternate folder location on the subscriber (pointing to existing path on a remote server). We generated new snapshot and partitioned snapshot, and moved to subscribing server at alternate path.We're seeing a problem we've never seen before:
When the merge agent runs, it applies the schema and performs a few hundred bulk inserts, but then proceeds to 'download changes to subscriber' as though the snapshot is old (it is brand new and should have all transactions). We have never seen this before, i.e. all data is usually applied in bulk when creating a new or reinitializing an existing subscriber.
we marked the subscriber for re-initialization (from both the publishing and subscribing server) with a fresh set of snapshots. The same problem occurred.We have also tried in our test environment (which mirrors production) and the snapshot applies as expected (via bulk inserts).
(It might be relevant to note the publisher was recently upgraded in-place from 2005 to 2012.)
Hey,
Is there somewhere in MSDE (or SQL) where you can see how many transaction are made to a sertain database or by a sertain user? At this way i could figure out witch database/user uses most (or least) recources (cpu) over a period of time.
I imported a table using DTS.
I run SQL statements in my original server database. It works well.
But when I run the same instructions within a stored procedure in my new workstation where I improrted the table I get this error:
Server: Msg 515, Level 16, State 2, Procedure InsertFichierPrix, Line 11
Cannot insert the value NULL into column 'Id', table 'myDBLive.dbo.FichierPrix'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Here is the SQL statements that I run on the original Database:
insert into fichierprix(nomfichier, version, descriptionfr, typeclient,....) values(@NomFichier,'Actuelle', @NomFourFr, 'MembreAcheteur', ....)
And here is my SP that I run on the new imported database:
CREATE PROCEDURE InsertFichierPrix @NomFichier varchar(50), @NomFr varchar(50),@NomAn varchar(50)
AS
declare @NomFourFr varchar(50)
declare @NomFourAn varchar(50)
SET @NomFourFr='liste fournisseur ' + @NomFr
set @NomFourAn='liste fournisseur ' + @NomAn
insert into fichierprix(nomfichier, version, descriptionfr, typeclient,...) values(@NomFichier,'Actuelle', @NomFourFr, 'MembreAcheteur'...)
GO
And here is the execution of my Stored proc on the destination database:
execute insertfichierprix @NomFichier='myfilename2', @NomFr='fournifr1',@NomAn='Fourniang1'
Thank you for helping me.
One of my application i am using is very slow,(SQL 2005 backend).I am trying to find out the tables and add index to it.But this time my profiler is skipping the records(Trace Skipped Records).Is there any settings?,so that it shows up all the records.
Note: i dont have the code of the application,but i can find few tables holding 2-3 millions of records.I want to know the column name,to create index.
Can someone please help in in understanding what I have done wrong. My company is using Backup Exec 7.3 as it's back up application. I have built a server, installed SQL, Built an database and added logins. I am backing up my database and Transact Log Files to specific folders on my partioned drive. Why when I run the backup ups, It backs up everything EXCEPT for my SQL folders. It seems to Skipp all SQL Folders. Please Help, I need to have this funtion working properly. What have I done wrong or what do I need to do to make this stop. Does this perhaps have something to dwil Here is an example of the backup Message in Backup Exec that I am recieving:
Media Name: "DIFF"
Backup of "FEPTESTE$ "
Backup set #41 on storage media #1
Backup set description: "Daily 6"
Backup Type: DIFFERENTIAL - Changed Files
Backup started on 6/28/02 at 12:36:21 AM.
The item Microsoft SQL ServerDataMSSQLDataTESTDB_Data.MDF in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamaster.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamastlog.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamodel.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamodellog.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamsdbdata.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatamsdblog.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDataorthwnd.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDataorthwnd.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatapubs.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDatapubs_log.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDataempdb.mdf in use - skipped.
The item Microsoft SQL ServerDataMSSQLDataemplog.ldf in use - skipped.
The item Microsoft SQL ServerDataMSSQLLOGTEST_Log.LDF in use - skipped.
Backup completed on 6/28/02 at 12:37:11 AM.
Backed up 48 files in 24 directories.
14 items were skipped.
Processed 23,876,604 bytes in 50 seconds.
Throughput rate: 27.3 MB/min
I have a serious issue with transferring binary(8) data from SQL Server 2008 R2 to Excel 2010.
A standard query is made in Excel (Data -> From Other Sources -> From SQL Server) and it works... almost perfect.Almost, because in the query result I have almost all columns in destination spreadsheet, except of those columns which are binary(8) format in my table in SQL Server.
So, in SQL there are columns - let's say:
Date
Start time
End time
User ID (as binary(8) format)
Memo (as string)
...
After Excel's query is done, in Excel there are only columns:
Date
Start time
End time
Memo (as string)
...
All columns with types = binary(8) are skipped.
I tried:
1. Export from SQL Server to CSV file and then from CSV file to Excel (all columns as string) and it works, but it has to be automated so CSV is not a good idea.
2. Adding to ConnectionString additional parameters NO_BINARY_RESULT=1 - does not work.
3. Adding to ConnectionString additional parameter IMEX=1 - does not work.
What causes SQL Profiler to show "SQL Profiler Trace Skipped Records"?
SQL2005 SP2 64bit
Thanks.
Hi There,I am having a strange problem with my identity column...... 1). I have a table of Products that have an identity column auto-incremented by 1. 2). I have my asp program working quite well in which the Data entry operators are adding the products into my database..... and they do not have any interface through which they can delete products........ 3). My Database is running at Web server(MS SQL Server)My problem is that when i cehcked my database.... there were around 1000 records but the auto increment number have reached to 1500. and when i checked in details then i saw that Auto number column is being skipped certain numbers..... like one entry is 1478 then the next one comes to be 1482..... and 1508 to 1516........ Its happening alot of times and it seems that SQL Server is skipping some numbers............Since it is Auto-Number so i do not have control over it through my code.... So i think the coding might not be the problem...... I have set Identity seed as well as Identity Increment both to 1.Is there any thing that you can suggest me to do??(Thanx)
View 12 Replies View RelatedI tried to load a fixed width flat file with around 300,000 rows. However, only the first 8xxxx rows were loaded to the destineation table and the rest row were loading blank records. There was no error message showing during package execution. I've tried to split the file in half and the result was the same. So it wasn't the data file problem.
Would there be any buffering issue I need to cater for inside the package? Thanks!
I am copying the template with header before loading the data. I tried deleting Dataflow task, Excel Connection manager etc., nothing seems to work and there are no nulls in the data. I did this several times, workaround seems to not working this time.
View 9 Replies View RelatedHi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
Has anyone seen the SQL Server error:
"tempdb is skipped. You cannot run a query that requires tempdb"?
We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks!
- Mike
Hi,
Im using transactional replication to replicate data from one database to another database.
my aim is to make a customized replication using c#. so im creating publisher and subscriber through sql code.
but main problem is if i select a table as a publisher then it will fully replace the schema of subscriber.
eg:
Before Creating a subscription
Publication Name :TestPubVer
Publisher Table-->CustomerOrder
----------------------
CustomerID
CustomerName
CustomerAddress
Subscriber Table--> Consumer
--------------------
ConsumerID
ConsumerName
ConsumerAddress
if i create a subscription , it will try to search for CustomerOrder Table , if it is not present it will create a new table with publisher schema.
if it is present then also it will replace the publisher table schema in subscriber table and a same table is created in subscriber database
To avoid this im replacing the subscriber Table name with a different table during article creation
eg:
exec sp_addarticle @publication = N'TestPubVer', @article = N'CustomerOrder', @source_owner = N'dbo', @source_object = N'CustomerOrder',
@type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none', @destination_table = N'Consumer', @destination_owner = N'dbo', @status = 24,
@vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboConsumer]', @del_cmd = N'CALL [sp_MSdel_dboConsumer]',
@upd_cmd = N'SCALL [sp_MSupd_dboConsumer]'
After this i will create a subscription
After Creating a subscription
Publication Name :TestPubVer
Publisher Table-->CustomerOrder
----------------------
CustomerID
CustomerName
CustomerAddress
Subscriber Table--> Consumer
--------------------
CustomerID
CustomerName
CustomerAddress
After subscription the subscriber tablename will be same but column names are get replaced by publisher table.
How can i avoid this? i tried many ways and im running out of time.
regards
Baji Prasad.B
Additional info,
I had recently applied SP2 on two servers (publication and not working immediate updating subscriber).
I found this code in the INSERT trigger which fails
exec @retcode = OpenDataSource
('SQLOLEDB',N'SERVER=MYSERVER;UID=sa;PWD=t00bad;') .[Packageval].[dbo]. [sp_MSsync_ins_Package_1][PDBSASQL1],[Packageval],
@c1 output,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10 output
Any idea?
Thanks
In article <8e7dqp$lii$1@nnrp1.deja.com>,
skhanal@my-deja.com wrote:
> Hi,
>
> I have two servers configured as Immediate updating subscribers to the
> publication server.
>
> Everything was ok for sometime, but now when I try to insert a record
> in any table in the published database in one of the subscriber I get
> the following error.
>
> Server: Msg 7415, Level 16, State 1, Line 3
> Ad hoc access to OLE DB provider 'SQLOLEDB' has been denied. You must
> access this provider through a linked server.
>
> Inserting the same record in the other subscriber and publisher does
> not have any problem.
>
> I could not find any documentation for this error. Any help will be
> greatly appreciated.
>
> Thanks
Hi!
How is it possible to set up a repl. scenario, where a subscriber of a DB
is also the publisher of this DB for other subscribers. i mean 3 level repliction. MAINPUB
SUB1 SUB2 SUB3
notebookSUB1...................notebooksubN
SUB1..3 are subscribing to MAINSUB and publishing to a number of notebooks!
Any help would be greatly appreciated!!!
TIA
Hi,
View 4 Replies View RelatedHi,
Can we use transactional replication with updatable subscriptions when we have more updates and transactional changes at the Subscriber side??
We are using SQL Server 2005 SP1. We will be using 6 publications and 4 subscriptions for each publications, so totally 24 subscriptions. Both the Publisher and the Distributor is the same server. It is set to run in continuous mode. Type : Pull Subscriptions.
Regards,
Swapna.B.
Hi,
View 13 Replies View RelatedHi
I'm a bit confused right now. I am using Sql 2005 merge replication. I have a publisher and a subscriber running. Today, I updated some data in my publisher which caused 300,000 updates to be sent to the subscriber. There was no problem here, it took a while though. Later, I added a new subscriber and when it started initializing I suddenly thought that I should have really created a new snapshot, one that included the 300,000 row update. Because I didn't I was epecting that once the subscription had finished initializing from the old snapshot it would then have to apply the 300,000 row update. But it didn't seem to do this last update! But yet all the data was correct!!
Does Sql replication somehow merge the snapshot with pending updates when it creates a new subscription?
Thanks for your help
Graham
I am having a problem creating a publisher.
I have a database, MobileApp, on a server called Server03, which is a subscriber to a database Stock on Server01. I create the database as a subscriber on Server01, and it gets replicated to Server03 correctly.
I now want to use MobileApp on Server03 as a publisher to mobile devices. I click to add a new publication, and add all the tables. I get all the way to the end, but when I click on finish, I get an error for each table - 'an article with a different subscriber_upload_options value already exists for object.
Any ideas please?
Thanks
Pete
Hi all,
Publisher
SQL 2000 Snapshot publication timed to run at 23:00 daily All tables & views are published articles.Snapshot agent runs and creates a snapshot of all articles.
Subscriber
SQL 2005 Pull subscription set to run continuously Manually run the SQL agent job and I get the following error... 2006-12-11 15:04:35.687 Agent message code 20164. Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber.
I've tried creating the subscription database from a backup so I know all the tables & views exist and also tried just creating a blank empty database - same error.
There are no user stored procedures or functions in the database.
If I'm publishing all tables & views then why should the data & structure fail to load at the subscriber? Do I need two publications, the first containing the metadata and the second containing the data?
Thanks in advance
Dave
I have a clustering between Server A and Server B. There is a clustered SQL Server installed.
Server A is a primary node(oltp production server) and Server B is a secondary node.
i've set up transactional replication betw serer A and B. Server A is Publisher and Server B is subscriber. B also has a distribution database. I use serve B for all the reporting purposes.
Currently both servers have sp1.
Now i want to apply sp2 to the server B first since i use it for reporting purposes (just to make sure there are no issues and then after some days i want to apply sp2 on server A which is my prodction oltp server)
So when i apply sp2 on server B , my publisher will have SP1 and subscriber (& distributor) will have sp2. Will this cause any issues with replication?
Any help would be appriciated.
Thanks
Hi There
View 1 Replies View RelatedSQL Server 7.0 SP3
I am using the "Pull Subscription Wizard" to set up merge replication. I go through all the steps of the wizard and then I hit "Finish" at the end. When it gets to the third step (Creating the Subscription), I get the old:
__________________________________________________ _________
mmc.exe has generated errors and will by closed by Windows.
You will need to restart the program.
An error log is being created.
__________________________________________________ _________
This happens whether I try to do this from my PC or from the actual server I am creating the subscription on.
Has anyone else had this issue? Is there a hotfix I need?
Any help is appreciated.
Thanks,
Craig
Hi all!
I hope anyone here can help me with this tough problem:
I have a replication enviroment with several subscribers.
Publisher:MSSQL 2000, SP3
Subscribers: MSSQL200, SP3 and MSDE2000, SP3
Since a few weeks, one of the subscribers fails when
synchronizing with the message
"This process could not enumerate changes at the subscriber"
The erroroutput with verboselevel 3 doesn't help me much, I
append it to the end of this posting. (unfortunately german)
I heard that this error can be caused by a "blank" in an image
column or different Service Pack Versions - definitely not
possible in this case!
The only strange detail is that the SQL Server Versionnumber
of this MSDE shown via EM is not the same as the number shown
in controlpanel->software. By the way, is it ok that here are
two lines for the MSDE?
SP3 is definitely installed on all machines!
Any help would be greatly appreciated - i'm already losing hope...
Best Regards, Gert
Microsoft SQL Server-Merge-Agent 7.00.623
Copyright (c) 1998 Microsoft Corporation
Percent Complete: 0
Verbindung mit Abonnent 'MyServerAT074'
Connecting to Abonnent 'MyServerAT074.MyDB'
MyServerAT074.MyDB: {call sp_MSgetversion }
Percent Complete: 0
Verbindung mit Verteiler 'MyServer1s'
Connecting to Verteiler 'MyServer1s.'
MyServer1s.: {call sp_MSgetversion }
MyServer1s.: {call sp_helpdistpublisher (N'MyServer1s') }
MyServer1s.distribution: select datasource, srvid from master..sysservers
where srvname = N'MyServer1s'
MyServer1s.distribution: select datasource, srvid from master..sysservers
where srvname = N'MyServerAT074'
MyServer1s.distribution: {call sp_MShelp_merge_agentid (0, N'MyDB',
N'MyDB', 9, N'MyDB')}
Percent Complete: 0
Initialisiert
MyServer1s.distribution: {call sp_MShelp_profile (19, 4, N'')}
Percent Complete: 1
Verbindung mit Verleger 'MyServer1s'
Connecting to Verleger 'MyServer1s.MyDB'
Connecting to Verleger 'MyServer1s.MyDB'
MyServer1s.MyDB: {call sp_MSgetversion }
Percent Complete: 3
Ruft Publikationsinformationen ab
Percent Complete: 4
Ruft Abonnementinformationen ab
Connecting to Abonnent 'MyServerAT074.MyDB'
Disconnecting from Abonnent 'MyServerAT074'
Percent Complete: 4
Uploadet Datenänderungen zum Verleger
Percent Complete: 5
Ruft die Liste der Löschungen von MyServer1s.MyDB ab
Percent Complete: 5
Verarbeitet Artikel 'First'
......
SNIP
....
Percent Complete: 5
Verarbeitet Artikel 'LastTable'
Percent Complete: 0
Der Prozess konnte die Änderungen auf dem Abonnenten nicht aufzählen.
Percent Complete: 0
Category:SQLSERVER
Source: MyServerAT074
Number: 0
Message: Attributverletzung eingeschränkter Datentypen
Attributverletzung eingeschränkter Datentypen
Disconnecting from Abonnent 'MyServerAT074'
Disconnecting from Verleger 'MyServer1s'
Disconnecting from Verleger 'MyServer1s'
Disconnecting from Verteiler 'MyServer1s'
:confused:
I want to call a stored procedure to do the insert into my published arcticle/table. Is this possible? I know you can call a stored procedure to insert into the subscriber arctiles.
The problem is the published database has a generated primary key (max +1 )that is calculated via a stored procedure during all inserts. I would bring the stored procedure over to the subscriber but that would not help since the values in the target table will not be 'current' because the subscriber database is only updated nightly.
Thoughts?
I have a sql server 2005 database that I have set as the publisher and an sql express database that I have set as the subscriber.
I set it that the 2005 db should push to the sql express db.
when i look at the replication monitor I get an error
The process could not connect to subscriber name/sqlexpress
what can I do to get this working?
I really am trying to just replicate 1 table every minute from the sql 2005 db to the sql express
Please advise?
hi,
I'm reading the MS online docs about replication. there's something not so clear until so far.
I have one central server and 7 client servers.
Sometimes new data is entered at one client server. This should be replicated (when client comes online) to the Central Server (merge replication).
Also, the other way, once the central server gets new data (that was replicated from a client), it should be again replicated to all 6 other clients (from the central server).
now, who should i configure as the distributor, publisher and subscriber?
I have problem like this.
I have 2 server with merge replication
the first as distributor and the second as subscriber.
I'll try to make trigger from database replication on distributor.
the problem is if any change data on subscriber, i need the user name from subscriber.
right now, i used user_sname to get user name.
but on distributor always get name Administrator.
it's because when subscriber update or delete data, distributor always change/delete/add data from subscriber as Administrator.
but, we know data change from subscriber it's from user. I need to retrieve user name that who change the data.
someone have any solution.
This is my triggers
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER TRIGGER [tr_insert_jour] ON [dbo].[Jour]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @date1 datetime, @person varchar(30) , @journal_code char(4), @journal_no char(8)
set @date1 = getdate()
set @person = user_name() ----this is user name
select @journal_code = JRN_CODE from inserted
select @journal_no = JRN_NO from inserted
insert into log_insert_Jour values (@date1,@person,@Journal_code,@journal_no)
END
Thanks
Thanks
Hi all,
I have a table (Products), and the table is like this:
- Product_ID (Primary Key), Not Null, Identity Specification: Yes, Increment 1, seed 1
- Name ...
- Price ...
When i insert in the publisher (PC, Sql Server 2005), it inserts correct
ex: |Product_ID | Name | Price| Inserted by|
| 1 | P1 | X | Pc |
| 2 | P2 | Y | Pc |
Then i replicate to Subscriber(Pocket PC, SqlMobile ), when i insert in Subscriber, it creates strange and big id's, is it normal? is this to avoid conflict inserts?
Ex: |Product_ID | Name | Price| Inserted by|
| 1 | P1 | X | Pc |
| 2 | P2 | Y | Pc |
| 3000 | P3 | Z | PPC |
| 6000 | P4 | Y | PPC |
Thx