.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Post New Web Links

a complex sql: is it a distribute transaction?

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server
 
remote: a table from linked server, such as remotelink.testdb.dbo.test
local: a local table

I need to insert the data which sit on local but doesn't sit on remove, the sql is like the following, is it a distribute transaction?
with missing_in_remote
as (
   select period_key,value,id from local where id not in (select id from remote)
   ),
data_to_insert as 
(
  select  period_key,value,id from
  (select *, rownum_over (partition_by period_key order by value) rownum from missing_in_remote where rownum=1
)   
insert into remote select * from missing_in_remote



View Complete Post


More Related Resource Links

about distribute transaction

  

I need to insert some data into a remote sql server (linked server), and also lock that table before the insert.

 

   BEGIN TRAN store_tran

    --join a remote table and a local table, then insert the result into the remote server.

    INSERT INTO #test select ... linkserver.db.dbo.rest with (tablockx) join localtable on ...

    insert into linkserver.db.dbo.rest select * from #test;

  COMMIT TRAN store_tran   

 

1: is that a distribute transaction?  If I use normal table, it is abosultely distribute transaction, but if use temporary table, is it still a  distribute transaction?

2: if use temporary variable instead of temporary table (#test) to save the intermediate result, is it distribute transaction?

3: if don't use a temporary place (temp variable, temp table) to store the intermediate, is it a distribute transaction? If there is no tablockx, then it will be a remote transaction but not a distribute transaction, but if use the tablockx, will it change the nature of the transaction (from non-distribute to distribute). In oracle database, if lock a table using select for update, then there is a bit changed in the table, and if changes some data on remote server, then it problem will be distributed tra

about distribute transaction

  

I need to insert some data into a remote sql server (linked server), and also lock that table before the insert.

 

   BEGIN TRAN store_tran

    --join a remote table and a local table, then insert the result into the remote server.

    INSERT INTO #test select ... linkserver.db.dbo.rest with (tablockx) join localtable on ...

    insert into linkserver.db.dbo.rest select * from #test;

  COMMIT TRAN store_tran   

 

1: is that a distribute transaction?  If I use normal table, it is abosultely distribute transaction, but if use temporary table, is it still a  distribute transaction?

2: if use temporary variable instead of temporary table (#test) to save the intermediate result, is it distribute transaction?

3: if don't use a temporary place (temp variable, temp table) to store the intermediate, is it a distribute transaction? If there is no tablockx, then it will be a remote transaction but not a distribute transaction, but if use the tablockx, will it change the nature of the transaction (from non-distribute to distribute). In oracle database, if lock a table using select for update, then there is a bit changed in the table, and if changes some data on remote server, then it problem will be distributed tra

SQL Server Database Transaction

  
A transaction is a group of SQL commands executed together as a single component of work to be accomplished. If all of these commands succeed, then a transaction is committed - the changes to the data are made final. If any of the commands within a transaction fail, then the entire transaction is cancelled, or in SQL Server terms, the transaction will be rolled back.

Transactions are one of the things that keep your data safe, but they're not necessarily easy to understand. Here's some help:

MVC too complex?

  

I believe lot of people will disagree with me on this one. But the MVC model sucks. The reason:

1.       Over complicated;

2.       Cannot handle complex UI;

3.       Hard to debugging as code scattered all over the place.

How do I persist custom complex types in design-time

  


Let's say that I've got one custom webcontrol called MyControl.



[ParseChildren(true)]
public partial class MyControl: WebControl {
[DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
[PersistenceMode(PersistenceMode.InnerProperty)]
public IComplex Complex { get; set; }
}


The control is a very simple control but it holds an instance of a complex type that is accessable through a property. The property has the propertytype IComplex


public interface IComplex {
string Name { get; set; }
List<IComplex> Children {get;}
}


Let's say that I am able to pick one of several different types of object that derives from IComplex in design-time and assign it to the controls property "Complex" property.

Naturally I would like to persist this information to the HTML-View in a user-friendly way.

Because we don't know what type the user has picked there is no way to instansiate the type if we don't persist the name of that type. The Type could also contain more properties than the Interface the controls property is refering.


<cc1:MyControl id="MyControl1" runat="server">
<Complex> <!-

Multiple web methods in one transaction

  

Hi everyone

Sorry I am kinda new in web services, so it may sound a bit newbish question.

We have a web service that comunicates with the database and exposes web methods that enables the application to access the database. Each method does only one operation (Insert, Update or Delete). What I need to do is create a transaction that will be consisted of multiple calls to the database, meaning multiple calls of more then one web methods. Can I do that? (I read that if the web method does not throw exception - or ContextUtil.SetAbort() is not called - the transaction will be commited automatically when the web method finishes executing, which I don't want to happen because I want to call another web method in the same transaction, and then manually commit the transaction)

Thank you in advance.


Input Validation: Enforcing Complex Business Data Rules with WPF

  

Windows Presentation Foundation has a rich data binding system that includes flexible support for business data validation. We take a look at implementing some complex data input validation scenarios that include customized data errors for users.

Brian Noyes

MSDN Magazine June 2010


Text Rendering: Build World-Ready Apps Using Complex Scripts In Windows Forms Controls

  

The System.Windows.Forms.TextRenderer class provides support for complex scripts in Windows Forms controls so you can render text the way you want and support international locales.

Miguel A. Lacouture

MSDN Magazine March 2006


Web Services: Capturing and Analyzing Client Transaction Metrics for .NET-Based Web Services

  

This article presents a general-purpose client quality reporting mechanism that can be used in any .NET-based transaction system that employs HTTP/SOAP. The design uses client response time and quality recording, upload of logs as SOAP headers attached to new transaction requests, and server handoff of these headers to a low priority queue for logging and analysis. This technique gives an enterprise near real-time information on actual end-user response times. These response times reflect network delays, client application overhead and server delays. By using this technique, enterprises can avoid the need to develop custom software to mine HTTP logs.

Brian Connolly

MSDN Magazine July 2004


Super Complex Table/Matrix

  

Hi

I'm in a situation where I have to build a super complex table or rather matrix

This is how the table/matrix should look like: 

 

http://yfrog.com/f/0seq7j/

 

The complex thing is that everything is taken from a database.

 

I will try to explain the color sections one by one.

 

SERIE (Green area)

If we start with the top columns; SERIE 1, SERIE 2, SERIE 3

These values are coming from a table column value. If this column has the value 3 then I will need to print 3 table cells, just like in the image.

 

 

MEDICINE NAMES (Blue area)

Then there is the outer left columns, that goes like this;

Behandlingsdag (meaning day of treatment)

Osplatin (100 mg/m2)

Leucovorin (x)

And so forth.

 

All these values are also coming from 3 different tables

 

 

One Column (Yellow area)

These cells are getting their values from a table. According to some true og false values.

 

 

1, 2, 3, 4 (Red area)

Then there is the table cells below the SERIES. These table cells goes like this

First there is a 1, then there are the tables cells 1 to 18 all below

Binding of complex entity turns into validation error

  
consider classic example: Product and Category editing Product in dropdown Id of Category is selected - Category mapped as object with all fields empty except Id when submitting Product edit form - validation gives an error: "Category name is required" (I have Required attribute on Category Name property) How deal with such errors if I want to use built-in validation (if (!ModelState.IsValid))? Writing custom data binder which would fill all such id-only-objects with values from database comes to mind.upd: I did small research of mvc code and found out that validation happens before binding - so this solution would't work. Do you have any other solutions for this problem?

SSIS Package Transaction locks entire table

  
Hello, I have SSIS package that using transactions and working as expected. But this locks all the tables involved in the data flow that are get inserted/loaded. If I query those tables during package execution the query waits till SSIS releases the lock. But I can still query those tables using NOLOCK hint. Is this possible SSIS will lock only the rows that it insert/update/delete etc? So that other user can still use that table or another instance of same SSIS Package can load data into those tables? My Package: In the package level I have TransactionOption = Required. The Sequence (Data Flow is inside this sequence) TransactionOption = Supported. I had some issue in my package that I posted here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97 and also resolved. Now I descovered this issue and trying to resolve. Thanks, Prabhat

CLR Trigger with stored procedures - transaction error

  
Hi, I've developed sample CLR trigger which call stored procedure but if execution of stored procedure fail I got following exception The context transaction which was active before entering user defined routine, trigger or aggregate "CskTest" has been ended inside of it, which is not allowed. Change application logic to enforce strict transaction nesting. The statement has been terminated.  My trigger [SqlTrigger(Event = "FOR INSERT", Name = "CskTest", Target = "Requirement")] public static void HandleRequirement() { using ( SqlConnection conn = new SqlConnection("Context Connection=true") ) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Test"; conn.Open(); try { cmd.ExecuteNonQuery(); } catch ( Exception ex ) { SqlContext.Pipe.Send(ex.Message); } } } I wanna handle this exception in my catch block. How can I do this?

Joining complex SQL tables

  
Hi, I am new to SQL query. I would like to join two SQL queries (MS Office Query and Computer Details Quer) into one. The tables output can be found at http://rapidshare.com/files/412281201/SQL_query.zip Or, from lsoon3@gmail.com Thanks for your guidance, below are the query:  1) MS Office Query: ================ select v_Add_Remove_Programs.DisplayName0 AS [MS Office Edition] from v_Add_Remove_Programs where DisplayName0 like 'Microsoft Office Project Professional%' OR DisplayName0 like 'Microsoft Office Visio Standard%' OR DisplayName0 like'Microsoft Office Visio Professional%' OR DisplayName0 like 'Microsoft Office Enterprise%'OR DisplayName0 like 'Microsoft Office Standard%' OR DisplayName0 like 'Microsoft Office Professional%' 2) Computer Details Query: ======================= select  distinct  v_R_System.ResourceID,  v_R_System.Netbios_Name0 AS [Computer Name],  v_R_System.Resource_Domain_OR_Workgr0 AS [Domain/Workgroup],  v_Site.SiteName as [SMS Site Name],  [Top Console User] = CASE  when (v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 is NULL or v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = '-1')  then 'Unknown'  Else v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0  End,  v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System],  v_GS_OPERATING_SYSTEM.CSDVers

WF4 for High Volume Transaction Apps

  
Hi! I am in the middle of redesigning my application and I would like to consider WF4 as the center piece for my application logic. My application is an sms gateway service and it can reach up to millions of hits within a short period of time. I would like to know whether WF4 is suitable to be used for millions of hits type of application. Thank You :) Best Regards, Serena

How to debug a complex function?

  
Hi everybody, I got a few complex functions written by my colleague to review. These functions calculate cost of goods using one of the methods (passed as a parameter) (FIFO, RWAC, LAST cost). I've been looking at these functions or running some queries based on these functions, but I'm yet not sure I understand them fully and their logic. What is the best approach for me to test / understand these functions? Thanks in advance.Premature optimization is the root of all evil in programming. (c) by Donald Knuth Naomi Nosonovsky, Sr. Programmer-Analyst My blog
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend