SQL Server Update table with inner join : update one table based on the contents of another table

Many times we need to update the contents of one table based on the contents of another table.

Have you ever found yourself in such situation ?

Well the simplest approach for that is by using the aliases.

For example if we wish to update a table tableA based on the contents of the table tableB then by using aliases we can do it in the following way:

Update a set a.field = b.field from tableA a inner join tableB b on a.primaryKey=b.foreignkey

And that’s it.

What is equivalent for regsvr32 exe in .NET?

Regasm

What are types of compatibility inVB6?

There are three possible project compatibility settings: - No compatibility - Project compatibility - Binary compatibility No Compatibility With this setting, new class ID’s, new interface ID’s and a new type library ID will be generated by VB each time the ActiveX component project is compiled. This will cause any compiled client components to fail(with error 429!) and report a missing reference to the `VB ActiveX Test component’ when a client project is loaded in the VB IDE. Note: Use this setting to compile the initial release of a component to other developers. Project Compatibility With this setting, VB will generate new interface ID’s for classes whose interfaces have changed, but will not change the class ID’s or the type library ID. This will still cause any compiled client components to fail(with error 429!)but will not report a missing reference to the `VB ActiveX Test component’ when a client project is loaded in the VB IDE. Recompilation of client components will restore them to working order again. Note: Use this setting during the initial development and testing of a component within the IDE and before the component is released to other developers. Binary Compatibility VB makes it possible to extend an existing class or interface by adding new methods and properties etc. And yet still retain binary compatibility. It can do this, because it silently creates a new interface ID for the extended interface and adds registration code to register the original interface ID but with a new forward key containing the value of the new interface ID.COM will then substitute calls having the old ID with the new ID and hence applications built against the old interface will continue to work(assuming the inner workings of the component remain backward compatible!). With this settings, VB will not change any of the existing class, interface or type library ID’s, however in order that it can do so, VB requires the project to specify an existing compiled version that it can compare against to ensure that existing interfaces have not been broken.

How do you do object pooling in .NET?

COM+ reduces overhead by creating object from scratch. So in COM+ when object is activated from its pool and when it’s deactivated it’s pushed back to the pool. Objects pooling is configured by using the “ObjectPoolingAttribute” to the class.

How many types of Transaction are there in COM+ .NET?

There are 5 transaction types that can be used with COM+. Whenever an object is registered with COM+ it has no abide either to these 5 transaction types. Disabled: There is no transaction. COM+ does not provide transaction support for this component. Not Supported: Components does not support transactions. Hence even if the calling component in the hierarchy is transaction enabled this component will not participate in the transaction. Supported: Components with transaction type support will be a part of the transaction. This will be only if the calling component has an active transaction. If the calling component is not transaction enabled this component will not start a new transaction. Required: Components with this attribute require a transaction i.e. either the calling should have a transaction in place else this component will start a new transaction. Required New: Components enabled with this transaction type always require a new transaction. Components with required new transaction type instantiate a new transaction for themselves every time.

How to implement DTC in .NET?

DTC is implemented using COM+. Following are the steps to implement COM+ in .NET: - “EnterpriseService” namespace has all the classes by which we can implement DTC in .NET. You have to add reference “EnterpriseService” namespace. - Your class must derive from “Service Component” object. - Then you have to define your class with the transaction attribute - After the class level transaction type is defined, its time to define at the method level the AutoComplete attribute. AutoComplete attribute says that if no exception is thrown then mark its part of the transaction as being okay. This helps cut down on the amount of code required. If the implementation sets AutoComplete to false, or omits it all together, then we would need to manage the transaction manually. To manually control the transaction you will need to use the ContextUtil class and its static members.

How do we create DCOM objects in VB6?

Using the Create Object method you can create a DCOM object. You have to put the server name in the registry.

Can you explain what DCOM is?

DCOM differs from COM in that it allows for creating objects distributed across a network, a protocol for invoking that object’s methods, and secures access to the object. DCOM provides a wrapper around COM, hence it is a backwards compatible extension. DCOM uses Remote Procedural calls (RPC) using Open Software Foundation’s Distributed Computing Environment. These RPC are implemented over TCP/IP and names pipes. The protocol which is actually being used is registered just prior to use, as opposed to being registered at initialization time. The reason for this is that is that if a protocol is not being used, it will not be loaded. In order to inform an object that the client is still alive, periodic pinging is used. Hence, when the client has died and no ping has been received (to refresh it) before the expiration time, the serverobject will perform some clean up tasks (including decrementing its reference count). Since RPC across a network are typically slow (compared to processes residing on the same machine), DCOM sends multiple requests in the same call.

Can you describe IUKNOWN interface in short?

Every COM object supports at least one interface, the IUnknown interface. All interfaces are classes derived from the base class IUnknown. Each interface supports methods access data and performs operations transparently to the programmer. For example, IUnknown supports three methods, AddRef, Release (), and QueryInterface(). Suppose that printerf is a pointer to an IUnknown. Printerf->;AddRef() increments the reference count. Printerf->;Release() decrements the reference count, deleting the object when the reference count reaches zero. Printerf->;QueryInterface(IDesired, pDesired) checks to see if the current interface (IUnknown) supports snother interface, IDesired, creates an instance (via a call to CoCreateInstance()) of the object if the reference count is zero (the object does not yet exist), and then calls pDesired->;AddRef() to increment the reference count (where pDesired is a pointer to IDesired) and returns the pointer to the caller.

What is Reference counting in COM?

Reference counting is a memory management technique used to count how many times an object has a pointer referring to it. The first time is it created; the reference count is set to one. When the last reference to the object is nulled, the reference count is set to zero and the object is deleted. Care must be exercised to prevent a context switch from changing the reference count at the time of deletion.

How can we use .NET components in COM?Twist: What is CCW (COM callable wrapper)? Twist: How do we ensure that .NET component is compatible with COM?

.NET components can not be used in straight forward way with COM. You will need to create CCW in order that COM components communicate with .NET assemblies. Other way to create CCW is by using InteropServices attributes. Here interfaces are created automatically. Following are different types of attributes: -None: No class interface is generated for the class. This is default setting when you do not specify anything. -AutoDispatch: Interface that supports IDispatch is created for the class. However, no type information is produced. -AutoDual: A dual interface is created for the class. Type information is produced and made available in the type library. Below in the source code we have used the third attribute. Imports System.Runtime.InteropServices _ Public Class ClsCompliant End Class Other than class attributes defined up there are other attributes with which you can govern other part of assembly. Example “GuidAttribute” allows you to specify the GUID, “ComVisibleAttribute” can be used to hide .NET types from COM etc. -Once .NET assembly is created using either interface or using Interopservices method we need to create a COM type library using Type library export tool [Tlbexp (AssemblyName)] -The final thing is registering the CCW in registry using regasm tool. Regasm AssemblyName[Options] and that's it.

How can we use COM Components in .NET? Twist: What is RCW?

Dot NET components communicate with COM using RCW (Runtime Callable Wrapper). Following are the ways with which you can generate RCW: -Adding reference in Visual Studio.net -Using Type library import tool. Tlbimp.exe yourname.dll -Using interopservices.System.runtime. Interopservices namespace contains class TypeLib Converter which provides methods to convert COM classes and interface in to assembly metadata. -Make your custom wrappers. If your COM component does not have type library then the only way to communicate is writing custom wrappers. That means communicating directly with COM components.

What is CODE Access security?

To help protect computer systems from malicious mobile code, to allow code from unknown origins to run with protection, and to help prevent trusted code from intentionally or accidentally compromising security, the .NET Framework provides a security mechanism called code access security. Code access security allows code to be trusted to varying degrees depending on where the code originates and on other aspects of the code's identity. Code access security also enforces the varying levels of trust on code, which minimizes the amount of code that must be fully trusted in order to run. Using code access security can reduce the likelihood that your code can be misused by malicious or error-filled code. It can reduce your liability because you can specify the set of operations your code should be allowed to perform as well as the operations your code should never be allowed to perform. Code access security can also help minimize the damage that can result from security vulnerabilities in your code.

All managed code that targets the common language runtime receives the benefits of code access security, even if that code does not make a single code access security call.

What'is difference between System exceptions and Application exceptions?

From the System.SystemException documentation:

This class is provided as a means to differentiate between exceptions defined by the system versus exceptions defined by applications.

So, consider it the opposite of ApplicationException. All Exceptions that are defined by the .NET Framework inherit from SystemException. All Exceptions that are defined by code that you write should derive from ApplicationException. Exception is the root class of ALL exceptions.