I Need Away To Show The Pending Transaction From Transaction Replication In A User Friendly Format.
I want to list out the pending transaction for transaction replication by publication.
Help needed.
View Complete Forum Thread with Replies
Related Forum Messages:
Is There A Way To Show A User-friendly Error When User Did Not Select A Required Parameter ?
Hi all, I was wondering whether there is a way to show the user an error when the user did not select a specific parameter. In my case, I have two optional parameters. The user has to select either one to view the report. If the user does not select either one, I would like to show some sort of an error page indicating so. However, all i am getting is a complete blank with no report in sight. I as a developer know this is an error as a result of missing parameters, but i was wondering whether I could produce a page or direct it to a page so that the user doesn't go all horrified at an empty page ? Thanks Bernard
View Replies !
Unable To Shrink Transaction Log Under Transaction Replication With Sync With Backup Enabled On Distribution Db?
We have applied transaction replication servers with SQLServer2005 standard edition with SP1 running on them. Sync with backup is also enabled on distribution db. Now our transaction log is not shrinking using standard shrinking procedure(taking log backup with truncate option/shrinkfile). select name,log_reuse_wait, log_reuse_wait_desc from sys.databases it is showing dbname, 6, Replication respectively. declare @db int set @db=db_id('dbname') dbcc loginfo(@db) is showing 2 in status column. tried shrinking transaction file after removing sync with backup option from distribution db. still it failed. again tried shrinking after stopping log reader, it didn't worked. Regards, M. Abdullah Idris
View Replies !
Error 8525: Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
Hi All I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link. If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser. I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off. set XACT_ABORT ON Begin distributed Tran update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TRANSACTIONMAIN set REPFLAG = 0 where REPFLAG = 1 and DONE = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.WBENTRY set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.FIXED set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.ALTCHARGE set REPFLAG = 0 where REPFLAG = 1 update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 update TSADMIN.TSAUDIT set REPFLAG = 0 where REPFLAG = 1 COMMIT TRAN It's got me stumped, so any ideas gratefully received.Thx
View Replies !
SSIS, Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure. for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR. I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties. if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. and Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database. Please help me it's very urgent.
View Replies !
Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
Hi all, I've already read the thread by the same topic, but I found Kevin Yu's comment a bit confusing. I don't know how to solve my problem. I have two stored procedures in sql server. First one inserts into a bunch of tables so it has Begin Transaction and Commit in it. Second one just inserts into one table so it doesn't have the begin transaction statement in it. From my data access class in c#.net, I have a function which calls both of these stored procedures from within a "using transactionscope" block. So when it makes the call to the first stored procedure, it returns fine, but when it calls the second one, I get the above error. I think it has something to do with me declaring transactionscope in my application code, and then calling a stored procedure which also has its own begin transaction. But I don't know how to fix this. Any ideas? Thanks
View Replies !
Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction
I'm using TransactionScope object and it is giving me problems. The situation is like this. I've 2 tables in sql server express, say student and courses and other one studentcourse for courses a student has opted for. An aspx page displays list of users in listbox, when user chooses student, it fetches courses he has opted for and in the checkedlistbox (for courses) these courses are checked. Now when uses makes any changes to courses and clicks update button, i've two functions at backend first one removes the courses which were previously selected but now not selected in postback, next it inserts courses which was not selected first but now are in this postback. I use following code: protected void Update(object sender, EventArgs e) { /* get selections, existing courses etc. */ using(TransactionScope tscope=new TransactionScope()) { try { RemoveCourses(/*Student*/ s, /*IList*/ oldCourses, /*IList*/ newCourses); AddCourses(/*Student*/ s, /*IList*/ oldCourses, /*IList*/ newCourses); tscope.Complete(); } catch(Exception ex) { lblMessage.Text=ex.Message; } } } If any exception occurs during update and page is reload or I go to any other page where some data is fetched from sql server I get the exception: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction Please help!
View Replies !
Distributed Transaction Completed. Either Enlist This Session In A New Transaction Or The NULL Transaction.
I am getting this error :Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.have anybody idea?!
View Replies !
Transaction Log Doesn't Show Correct Info
Hi All, I have a production database in SQL 2000 server. It is configured to truncate on check point. when ever I check the log size by right click on database and choose property and then logfile the size is keep on showing the ever growing transaction file size. I truncated the logfile and restarted the server. No new news. The transaction log file is set to unlimited growth. Can you please tell me how do i get the file shrink to its orginal value?? The files are set to shrink files option. Any advice would be gratly appreciated. (The Transaction file size is 41 gig, initially it was set to 1gig)
View Replies !
Msdb Table Backupmediafamily Does Not Show Transaction Log Backup Sometimes
Hello, We have a self-written procedure to restore transaction logs on a standby server (Sql Server 2000 sp4) We do this by joining 2 msdb tables, to find out which backups have been performed: backupmediafamily and backupset. Sometimes backups are NOT registred in table msdb..backupmediafamily. Underneath 2 examples. OK- underneath queries shows the time the backup was created in the physical_device_name; joined with media_set_id 99% shows these correct data select media_set_id, physical_device_name from backupmediafamily where media_set_id = 258716 media_set_id physical_device_name 258716 H:Mssql2000MSSQLBACKUPPAPPS10_tlog_200701010030.TRN select media_set_id, backup_start_date , backup_finish_date, database_name from backupset where media_set_id = 258716 media_set_id backup_start_date backup_finish_date database_name 258716 2007-01-01 00:30:08.000 2007-01-01 00:30:08.000 PAPPS10 NOT ok select media_set_id, physical_device_name from backupmediafamily where media_set_id = 376467 media_set_id backup_start_date backup_finish_date database_name 376467 2007-02-17 09:00:14.000 2007-02-17 09:00:14.000 PDBTIME 376467 2007-10-31 19:15:05.000 2007-10-31 19:15:05.000 PJAVA select media_set_id, backup_start_date , backup_finish_date, database_name from backupset where media_set_id = 376467 media_set id physical_device_name 376467 H:Mssql2000MSSQLBACKUPPDBTIME_tlog_200702170900.TRN !!! No record for backup transaction log database PJAVA select media_set_id, physical_device_name from backupmediafamily where physical_device_name like '%PJAVA%2007103119%' media_set_id physical_device_name 1115347 H:Mssql2000MSSQLBACKUPPJAVA_tlog_200710311900.TRN 1115432 H:Mssql2000MSSQLBACKUPPJAVA_tlog_200710311930.TRN 1115475 H:Mssql2000MSSQLBACKUPPJAVA_tlog_200710311945.TRN Missing record for backup made on 2007-10-31 19:15 hours !!!! Hope somebody can help me. Best regards, hvdtol
View Replies !
Transaction Log Format
Is there any documentation that shows the format of the transaction log file? Are there any example programs that show how to read the log file?
View Replies !
Open A Report In A Print Friendly Format
Hi experts, What I am trying to do is to open an existing report in a Print Friendly format . What is the simplest way to do this using Visual Studio? Here's the sort of thing I hope is feasible: I can open my Report on a web page , then on that page can be a button / link for the user in case they want to open that report in a separate window. So I'd like to jump to that page by jumping to an URL and sending all parameters something like : http://Dev_Server/Reports/Pages/Report.aspx?ItemPath=%2fMy+Reports%2fTest+Report&rsaramOne=Parameters!paramOne.value + paramTwo=Parameters!paramTwo.value + etc all other parameters , I have on that report Any suggestions would be really appreciated . Regards,TIA
View Replies !
TRANSACTIONS In SSIS (error: The ROLLBACK TRANSACTION Request Has No Corresponding BEGIN TRANSACTION.&&"
I'm receiving the below error when trying to implement Execute SQL Task. "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." This error also happens on COMMIT as well and there is a preceding Execute SQL Task with BEGIN TRANSACTION tranname WITH MARK 'tran' I know I can change the transaction option property from "supported" to "required" however I want to mark the transaction. I was copying the way Import/Export Wizard does it however I'm unable to figure out why it works and why mine doesn't work. Anyone know of the reason?
View Replies !
Using Begin Transaction,End Transaction, Return,Rollback, Break,Continue
Hi, is there someone who can demonstrate the usgae of these codes in this simple process. I have a table that has (id int, Balance money) I want to write codes to utilize the Subject coding in this process. 1.Every time I withdraw money from a client, I want to verrify that he/she has enough money if not I want to roll back transaction. 2.Using Begin Tran , end Tran.. 3. Using Return ,Break,continue I really appreciate your help. Althought I read about this from a book, but it really confused me and I want to see real world example using client and withdraw from there accounts...:) regards Ali
View Replies !
How Do I Make Use Of Begin Transaction And Commit Transaction In SSIS.
Hi How do I make use of begin transaction and commit transaction in SSIS. As am not able to commit changes due to certain update commands I want to explicitly write begin and commit statements. but when i make use of begin and commit in OLEDB commnad stage it throws an error as follows: Hresult:0x80004005 descriptionyntax error or access violation. its definately not an syntax error as i executed it in sql server. also when i use it in execute sql task out side the dataflow container it doesnt throw any error but still this task doesnt serve my purpose of saving/ commiting update chanages in the database. Thanks, Prashant
View Replies !
User Friendly Error Reporting
The notorious situation: a duplicate key exception. Users register themselves using fancy nicknames.This often leads to conflicts. In addition, as I point out in error reporting -- to except or not expect or return, the conflicts can not be prevented in a distributed environment (one ensures that there in no such name and still can get a concurrent conflict). For these reasons, the apps should be prepared to the conflicts. The cases to which apps are prepared, are not exceptional. Meantime, if the engine insert process fails things naturally, we get an exception with a message like "Violation of UNIQUE KEY constraint 'col_name'. Cannot insert duplicate key in object 'table_name'. At first, it is not quite informative. Customers prefer to see something like: "a user with such a name already exists". Additionally, as explained in the referred topic, exception processing is extremely CPU consuming and should be not normally used as a function "feedback" tool. The option to prevent the exception raising by preceding the INSERT op by a table-locking SELECT is not attractive either because of 1) locking and 2) double checking. Obviously, we get the conflict exception on INSERT because the DB engine precedes the insertion by a select. How it would check the existence of the same key in the table otherwise? I want to say that the only thing we should do is to override the default DB error handler. How do I know that the error was caused by some specified constraint violation letting other issues to fail naturally? Should I parse the exception message looking for key_name?
View Replies !
Transaction Replication Help
Dear all, I set up a transaction replication (from server A to server B) for tables and stored procedures. When I update the data at server A, the data at server B will be updated by replication. However, the stored procedure can't update at server B no matter what I changed at server A's stored procedure. How can I update the stored procedure using transaction replication? The other question is the table schema. As I know, the transaction replication will be created a snapshot to project all needed tables and stored procedures at the first time. If I created a new table, stored procedure or changed the table schema (e.g. add/delete a new column), the transaction replication didn't update the new objects to the server B. What can I do to update the transaction replication except creating a new subscription? Thanks. epjames
View Replies !
Transaction Replication
Good Morning to all, I am new to SQL Server.Here is my question. I have set up transactional replication with immediately updating subscriber between to servers A & B. A is publisher/Distributor and B is subscriber. In the event A fails the applications will be directed towards B. My problem is suppose A has faliled and my data is now written to B. How can i syncronise data in A and b when A is again online and what should i do ? thanks in advance. Minesh.
View Replies !
Transaction Replication
Hi all, I am using SQL 2k on two seperate servers on win 2k advance servers without any service packs. My problem is i am using transactional replication between two servers (SML1 and SML2), SML1 is publisher/Distributor and SML2 is subscriber. My db name on both the server is same. when i am inserting data in db on SML1 it replicates immediately on SML2 but vice versa in not happening. Any help in setting up two way replication is highly appreciated. (There is no timestamp column on table which is used as article and there is PK). thanks in advance. Minesh.
View Replies !
Transaction Replication
My Transaction Replication fail with this error message pls help. This is the error message The process could not create file 'SQLMS1d$MSSQL7ReplDatauncSQLMS1_AFT_National_EMN _National - Transaction - Changes at Both Sides - EMN to Local Only20000607090006Benefit.sch'.
View Replies !
Transaction Replication Help!!!
I keep getting this error message even though I have droped and recreated the replication several times. Has anybody had the same problem? error message. "Another snapshot agent for the publication is running" Thanks for your time.
View Replies !
Transaction-Replication
Hi, Every one Can I add coulmn in my existing table which i am using for replication. This article is i am using for transaction replication. I added column to both articles , publisher & subscriber, But now it is giving me error that, Insert error. I checked in Create & Manage publications property on E.M , that cloumn is not checked & it is not allowing me to do from E.M , Does any one know which store proc I require to execute from QA. Appreciate Your Help Thanks Nirmal.
View Replies !
TRANSACTION REPLICATION
I it possible to setup transaction replication between two sql servers if both Sqlerveragents runs under different accounts and the servers are in different domains Thanks in advance Langel
View Replies !
Transaction Replication
I am trying to setup real time transaction replication between two databases on two different sql server 7.0.The ideas is to replicate all the objects from the first database to the second database including table, stored procedures and views. Both SQL servers’ runs under different service NT account. The sql server that I want to replicate runs an application that create additional tables when the application is running. How do I schedule a transaction replication so that all the objects plus those additional tables and stored procedures that the application creates at run time are also going to get replicated in real time? Thanks in advance. JACK
View Replies !
Transaction Log Replication
I had established a transaction log replication ,it's not immediate-updated. on next 2 days,it's normal.but now,it's not available ,and haven't any warning,error,informatin. I have very trouble about the 'log reader' is running but i change the data in articles it's no effect. please tell how i do?
View Replies !
Transaction Replication-Help
I am attempting to set up transactional replication between a production server (SQL1) and a “reports” server (SQL2). The production server is the publisher, the “reports” server is the distributor and the first subscriber, other subscribers will be added later. There are multiple databases on the production server; selected tables from each will be replicated to corresponding databases on the other servers. Both are SQL7, no SP (yet). I have set up the replication on two databases so far. The initial snapshot replication runs fine; the tables and data are duplicated on the reports server. For one DB the transaction replication fail the other still reports “No replicated transactions are available”, which is probably true. The failing log reader agent history reports: “The process could not execute ‘sp_replcmds’ on SQL1“ (five time, 10 minutes apart). The session details shows 2 items, “Initializing” and the same error message. The error details show it as an ODBC error timeout. After the fifth time the log agent history reports “The process could not execute ‘sp_Msadd_repl_commands27hp’ on SQL2 ” The session details show 3 items , “Initializing”, “No replicated transactions are available” and the same error message. The error details show the following (Category, Source, Number, Message): Agent, blank, 2000, “IDistPut Interface has been shut down.”; Agent, SQL1-JCL1, 20007,” No replicated transactions are available.” ( 26 times); Data Source, SQL2, 1007 “Cannot insert duplicate key row in object 'MSrepl_transactions' with unique index 'ucMSrepl_transactions'.Cannot insert duplicate key row in object 'MSrepl_transactions' with unique index 'ucMSrepl_transactions'.” I can restart the agent, but after two hours the same thing appears. These were set up with EM using the wizzards Any suggestions would be greatly appreciated.
View Replies !
Change Field Name To User Friendly Data
I have a data model based upon (views and tables) in a database. The field names of the views and tables are not user friendly and carry no meaning for the end user (AS400). I need to change those attributes to show user friendly information. I would like to know if there is a way to change the (name) for each of the fields other than manual entry. I am trying to have the model (and there fore the entity) show user friendly names for each field in the model. The names and descriptions can typed in and this method will change the name and you can even add a description. Can the name field be bound to a field in the view or table that shows a friendly name? How can this process be scripted or automated so that each of the fields shows a user friendly description that is available. We have a number of tables and views that require this information and its way too much data to enter by hand. Thanks for any help!
View Replies !
Transaction Context In User By Another Session
Im using SQL Server 2000 sp2 for ASP application on windows server 2003 sp2. Im getting an error 'Transaction context in user by another session'. I have not at all used BEGINTRANS / committrans , No Insert / Update statements. the kind of SQL statements that i was using in that page load is only "SELECT statements" Can any one give me answer how to get rid of this. I have used only one connection object. and result set as a loop containing few more result sets. But I m sure they are so complex statements to block the SQL server transactions. Awaiting for reply..................
View Replies !
Merge Or Transaction Replication?
I need multidirectional replication i.e. Publisher will update subscribers and vice versa. Is merge replication the only way or can multidirectional replication be done at all with transaction replication. MSDN says "Transactional replication maintains transactional consistency, but Subscriber sites are not as autonomous as they are in merge replication because Publishers and Subscribers generally should be connected continuously for updates to be propagated to Subscribers." . Does "not as autonomous" mean it can still do it with some trade-off? This is for an OLTP kind of app. Which is the most efficient way to distribute data in real time across multiple databases/servers. Pls let me know if any other inputs are required.
View Replies !
Bi-directional Transaction Replication
Two Servers,1st 2nddatabase a -------> after replication a'b' <------- after replication bon 1st server a is replicated to 2ndon 2nd server b is replicated to 1stI tried with merge replication it is working fine.But if i tried to usetransactional replication it give some error like "accessviolation".Using tansactional replication is it possible.plz help me toslove this problem.Regards,Senthil prabu R
View Replies !
Bi-directional Transaction Replication
Besides loopback detection, is there anything else that is needed to prevent the replication command from sending back to the source. Cos i am having problem with the subscriber B sending back the replication to A in the form of B as the publisher and A as the subscriber. :confused:
View Replies !
Problem With Transaction Replication
Dear Experts, I have a very strange problem on the sql server 2000 SP3. The problem has to do with the replication. The replication is running between two instance in both directions. Both intances are set on the same server. I'm using linked server. Problem is: the replication get an error after the call of the replication procedure (pRepl1) 1. The call on the subscriber is ok. The changes are done. No errors occurs 2. then shows the replication agent an error an stop the replication. The error is: 'prepl1' on the publication side could be invoked only within an transaction. This error is implemented in the 'prepl1'-procedure and occure if somebody call the procedur without to set "begin tran"-Statement. The procedure check the @@trancount value. But as I know the replication of the procedure call is a transaction and the procedure should find a @@trancount with the value > 0. Some calls where replicated very good. But after a while the replication began to throw this error. After that i could not start the rep. agent. 3. If i get the procedure call from the distribution-TA and execute it on the subscriber (within a "begin tran" and "commit tran" statement) i get no errors and all the changes are done. I could solve the problem only after uninstall the replication (uninstall all the subscriber , publisher etc.) an installing the replication from the beginning I can't understand when the problem occur. Can somebody help me ? Is the problem a false administration of the replication ? or pephaps a correct SP ? Thank you for any advice. Kolia
View Replies !
Transaction Replication - Identity Col
Working with Replication wizard. Can't manage transaction repl with identity cols on Publisher and Subscriber. Get msg: "proceure sp_MSIns_tablename not found". Followed instructions to set up create table script with 'Not for replication' statement and set the article properties override to 'Not Drop Table'. No other help available in tool - what is the proper way to get replication working with Identity cols?
View Replies !
Transaction Log Backup And Replication
Hi, I'm new to SQL Server 7.0. I wonder if anybody could help me answer the following two questions - 1)Is there any replication bench marks (speed/memory usage, etc) that we can use? So that we can better configure SQL Server accordingly. 2) As for disaster recovery side of things, I was told the "NO_TRUNCATE" option when backing up a transaction log doesn't actually work in SQL Server 7.0, although it worked in 6.5, is this true? This will mean a full data recovery can not be achieved in the event of a system failure. Help on this would be greatly appreciated. Thanks, Shaunak
View Replies !
Transaction-Replication----Urgent
Hi, Every one I have added new column in my Publication - Article Table , Same column I have added in my Subscriber Table , I am doing Transaction replication , but now its giving me error Insert Error - Column Name or Supplied Value doesn't Match, If any one has any idea please suggest me Thanks Nirmal
View Replies !
Transaction Replication Failure
Morning, I have set up Transactional Replication with the Distributor and the Subscriber on the OLAP server, and the Publisher on the OLTP Server. The databases were synchronized manually by backup/restore, and a simple insert and update on OLTP was successfully replicated. OLTP resides in a clustered environment. However, inserting into our Permits table (either through the application, or through the Enterprise Manager) causes the Distribution Agent to fail. The reported reason is that sp_MSdel_Logins (Logins is just another user table) is invoked on OLAP and due to referential integrity constraints, the delete fails. Permits table contains just under 2 million records. First, if one tries to delete a row on the Publisher with referential integrity failure then that transaction should not get replicated. Hence, the Distribution Agent should never see this transaction. Second, deleting from the Permits table should not invoke the deletion of a record in the Logins table. I verified the article properties for table Permits and the proper commands are specified (CALL sp_MSins_Permits, MCALL sp_MSupd_Permits, CALL sp_MSdel_Permits). Third, it is always the same row in Logins that sp_MSdel_Logins attempts to delete. And sure enough that record can not be deleted due to referential integrity. I can't figure out why SQL Server has a fixation with record 212. Does anyone have any thoughts on how to resolve this? I have disabled replication and re-installed it without resolving the problem. All I can think of is to disable replication, remove all references to 212, and re-install. I'm just afraid that this may resolve the issue with regards to this particular instance, without resolving the fundamental problem. Thanks, Sergio
View Replies !
Transaction Replication(2nd Post)
Hi all, I am using SQL 2k on two seperate servers on win 2k advance servers without any service packs. My problem is i am using transactional replication between two servers (SML1 and SML2), SML1 is publisher/Distributor and SML2 is subscriber. My db name on both the server is same. when i am inserting data in db on SML1 it replicates immediately on SML2 but vice versa in not happening. Any help in setting up two way replication is highly appreciated. (There is no timestamp column on table which is used as article and there is PK). thanks in advance. Minesh.
View Replies !
Transaction Log & Replication -- URGENT
Hi All, I have been asked to take over the maintenance on dB servers that I have not originally 'set up'. The request came as a result of the database log files growing at such a rapid pace its taking up hard drive space. I ran sp_dboption and noticed that the log files are NOT set up to truncate on checkpoint. As in SQL 2000 I wanted to determine if the dB was set up in 'simple' or 'full' recovery mode. It appears they are set up in 'full' recovery mode (which we can modify, since this is not a transactional dB and we rarely if ever have to restore from the transaction log). Hence my question, Is there anything I need to worry about (regarding replication)if I change the dboption to truncate on checkpoint and set dB in simple recovery mode? And after I backup the transaction log and try to shrink it will that have any adverse affects? I feel like this is all I need to do but replication is new ground for me, any information regarding it would be greatly appreciated. Also, any advice on how to maintain the transaction logs would be helpful. Christine
View Replies !
Transaction Replication Problem
How terrible my life! save me! Do you know the function of system store procedures for replication in publish database and distrubtion database? please tell me. I'd established 'transaction log' replication, it's like something is full or can't active,i don't know what process is unvalid. How i do!
View Replies !
Transforming Transaction Replication
Hi all, using transactional replication in SQL 05, I have a schema that is different at the subscriber and wanted to know the best approach for transforming the published data. For example, the subscriber is more normalized than the data being published causing 1 published record broken out into to many tables at the subscriber. Also need to applying scalar funtions to a few published columns before committing at subscriber. I've read DTS, although backwards compatible, is being deprecated in a future release. I suppose I can use either SSIS or custom procedures. I didn't see any tie-in to SSIS in the system procedures to setup replication (only DTS). Any advice on which would work in this type of scenario? Much appreciated, Pat
View Replies !
Usage Of Begin Transaction.... Commit Transaction??
Hi, anyone can help,I am trying to learn how to use begin tran..commit tran .. rollback tran by doing this exercise... unfortunatelly,it did not work...anyone can help... I have a table named tbl_balance(investor_id int,amount money) I want to insert/update balance for each investor. I wrote a procedure but it didnot work::: declare @investorid int, @amount money,@error_status int select @investorid=1 select @amount = 500 /* to check if investor exist in the tbl_balance table, if so execute the codes */ IF exists(select investorid from tbl_bal where investorid=@investorid ) begin tran begin update tbl_balance set amount=amount+@amount where investorid =@investorid if (@error_status<> 0) begin ROLLBACK tran select 'you did not update' end else begin COMMIT tran select ' YOu did update ' end end IF not exists(select investorid from tbl_bal where investorid=@investorid ) begin tran begin insert into tbl_balance values(@investorid,@amount) if (@error_status<> 0) begin ROLLBACK tran select 'you did not update' end else begin COMMIT tran select ' YOu did update ' end end
View Replies !
Problem With SSIS Transaction...Transaction Scope
Hi, I am having some problem with SSIS transaction. Eventhought I tried to imitate the concept that Jamie presented at http://www.sqlservercentral.com/columnists/jthomson/transactionsinsqlserver2005integrationservices.asp . My workflow is as followed ********************************* For Each ADO.Record in Oracle (transaction=not supported) If (Certain_Field_Value = 'A') Lookup Data in SQL DB with values from Oracle (transaction=not supported) DO Sequence A (Start a Transaction , transaction=required) INSERT/UPDATE some records in SQLDB(transaction=supported) Finish Sequence A ( transaction should stop here) UPDATE Oracle DB ( Execute SQLTask, transaction=not supported) If (Certain_Field_Value = 'B') Lookup Data in SQL DB with values from Oracle (transaction=not supported) DO Sequence B (Start a Transaction , transaction = required) INSERT/UPDATE some records in SQLDB (transaction=supported) Finish Sequence A ( transaction should stop here) UPDATE Oracle DB ( Execute SQLTask, transaction=not supported) If (Certain_Field_Value = 'C') ------------ ------------ End ForEach Loop ************************************* My requirements are that I want separate transaction for each Sequence A, B, C, etc... If Sequence A transaction fails, the other should still be continuing with another transaction. But I am getting an error regarding the OLEDB Error in next Task (e.g in Certain_Field_Value = 'B') "Lookup Data in SQL DB with values from Oracle ", the error message is ".......Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction. ". What is it that I am doing wrong? Regards KyawAM
View Replies !
Raising User Friendly Message In Case Of DB Error
Hi,I am using sql server 2005 and i am having table with a column which has unique key constraint.So if the user tries to enter the duplicate value for that particular column and try to save it ,it errors out in runtime..how do i raise the user friendly error message in this DB error, when user tries to save it.Thanks
View Replies !
Controlling A Transaction By User In SQL Server 2000
Hey Folks! I have a typical requirement by my client. On submitting a Update (Bulk) button a huge database operation starts. A huge bulk update operation need to be performed. This would take 2-3 minutes some times. Client wants a cancel button in this case where he can be given a way to cancel the database Transaction. Please let me know in case if there is a way out. Thanks, in advance. Regards, Uday.D
View Replies !
Error Ms Sql 2000 Transaction Log Replication
My sql server install sp3a. This error transaction log replication When Subscrib run it error Category:COMMAND Source: Failed Command Number: Message: {CALL sp_MSupd_Company Profile (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NU LL,'02-3229535',NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'CB01',0x00080 000)} Category:ODBC Source: ODBC SQL Server Driver Number: 37000 Message: Syntax error or access violation Syntax error or access violation. The step failed. When I test this Command in SQL Query Analyzer with Thsi Command {CALL "sp_MSupd_Company Profile" (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NU LL,'02-3229535',NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'CB01',0x00080 000)} can run Complete I add "sp_MSupd_Company Profile" in command. Please help me how I can do for this problem.
View Replies !
Transaction Replication Multiple Publication
:mad: I have one Publisher! (SQL 2005) Two Subscribers. (SQL 2005) Each subscriber requires different row filter from the published tables. So, i need to create Two publications for each subscriber for the same db. When i setup first publication, and apply filters to published tables. All goes fine. As soon i create an other publication with same published tables but DIFFERENT row filter; the log reader agent fails! The process could not execute 'sp_replcmds' on 'DB1'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011) Get help: http://help/MSSQL_REPL20011 Execution of filter stored procedure 2076702271 failed. See the SQL Server errorlog for more information. (Source: MSSQLServer, Error number: 18764) Get help: http://help/18764 The Log Reader Agent failed to construct a replicated command from log sequence number (LSN) {0008443a:000003b5:003e}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805) :confused: I have tried to re-initialze the subscriptions and re-run snapshots but no luck. As i remove the row filter from the second publication; log reader starts working fine. Previously i was using Merge replication and all was going BUT, on very large DML on publisher, we use to receive deadlock errors on application. I decided to use Tran repl instead because log reader reads the changes from tran-log instead of locking the published tables; NOW I AM STUCK! Ideas please!!!!
View Replies !
|
TRACKER
