Friday, November 2, 2012

Call User-defined Function on Linked Server :SQL Server

If you try to invoke a user-defined function (UDF) through a linked server in SQL Server by using a "four-part naming" convention (server.database.dbo.Function), you may receive error message. 

The reason is User-defined function calls inside a four-part linked server query are not supported in SQL Server. Thats why error message indicates that the syntax of a Transact-SQL statement is incorrect. 

To work around this problem, use the Openquery function instead of the four-part naming convention. For example, instead of the following query

Select * from Linked_Server.database.dbo.Function(10)

run a query with the Openquery function:

Select * from Openquery(Linked_Server,'select database.dbo.Function(10)')

If the user-defined function takes variable or scalar parameters, you can use the sp_executesql stored procedure to avoid this behavior. 
For example:

exec Linked_Server.database.dbo.sp_executesql N'SELECT 
database.dbo.Function(@input)',N'@input int',@input=10

but if you want to use it in a select statement (within some Stored Procedure), then this EXEC command will create some problems for you :)
However this could be resolve easily by creating a user-defined function in your database (not the linked server one)

CREATE FUNCTION [dbo].Function_Name(@Parameter INT) 




DECLARE @word sysname

EXEC LinkedServer.DatabaseName.dbo.sp_executesql

N'SELECT DatabaseName.dbo.Function_Name(@Parameter)' --dynamic sql query to execute

,N'@Parameter int' --parameter definitions

,@Parameter=@word OUTPUT --assigning the caller procs local variable to the dynamic parameter

RETURN @word


Sunday, January 30, 2011

Delete Duplicate Rows in SQL Server 2005

A new addition to the DELETE command in SQL Server 2005 is the TOP statement. The DELETE TOP does the same thing as a SELECT TOP WHERE only the TOP number of rows are deleted. This can be very helpful when there are duplicate rows of data present.
FROM Sales.Customer
WHERE CustomerID = 1 

This would delete one of the duplicate rows for Customer number 1 Suppose somehow the whole customer table got duplicated. I duplicated the Sales.Customer table into a tmpCustomer table.

SELECT Top 1 CustomerID, COUNT(CustomerID) AS Cnt
FROM tmpCustomer 
HAVING COUNT(CustomerID) > 1

WHILE @@RowCount > 0
    DELETE Top (1)
    FROM tmpCustomer
    WHERE CustomerID = (SELECT Top (1) CustomerID
                        FROM tmpCustomer 
                        GROUP BY CustomerID
                        HAVING COUNT(CustomerID) > 1)


While this worked just fine, it ran about 4 minutes for 38K rows. Let's try the dreaded CURSOR. Notice I can stick a variable in where the TOP () statement is. I subtracted -1 because we don't want to delete every row.

DECLARE @cnt int, @custID as int

FOR SELECT CustomerID, COUNT(CustomerID) AS Cnt
    FROM tmpCustomer 
    GROUP BY CustomerID
    HAVING COUNT(CustomerID) > 1

OPEN dupCursor 

INTO @custID, @cnt

    DELETE Top (@cnt-1)
    FROM tmpCustomer
    WHERE CustomerID = @custID
    FETCH NEXT FROM dupCursor 
    INTO @custID, @cnt

CLOSE dupCursor

This ran much better at 18 seconds. Enjoy.

Thursday, September 2, 2010

jQuery Image Swap with Effects

Swapping one image with another is probably one of the most used javascript techniques. Also Dreamweaver made “Image Replacement” even easier for non HTML/Javascript programmers by including this feature out of the box. One thing about Dreamweaver’s image swapping javascript is that it’s not the most beautiful javascript code. Well, as always with anything javascript related, jQuery is to the rescue. Query makes dynamic image swapping a peace of cake.
Firstly you have to copy the following javascript code in the HEAD section of your page. You can also save the following javascript functions in a seperate js file.

(function($) {

    $.fn.innerfade = function(options) {
        return this.each(function() {   
            $.innerfade(this, options);

    $.innerfade = function(container, options) {
        var settings = {
         'animationtype':    'fade',
            'speed':            'fast',
            'type':             'sequence',
            'timeout':          2000,
            'containerheight':  'auto',
            'runningclass':     'innerfade',
            'children':         null
        if (options)
            $.extend(settings, options);
        if (settings.children === null)
            var elements = $(container).children();
            var elements = $(container).children(settings.children);
        if (elements.length > 1) {
            $(container).css('position', 'relative').css('height', settings.containerheight).addClass(settings.runningclass);
            for (var i = 0; i < elements.length; i++) {
                $(elements[i]).css('z-index', String(elements.length-i)).css('position', 'absolute').hide();
            if (settings.type == "sequence") {
                setTimeout(function() {
                    $, settings, 1, 0);
                }, settings.timeout);
            } else if (settings.type == "random") {
              var last = Math.floor ( Math.random () * ( elements.length ) );
                setTimeout(function() {
                    do { 
            current = Math.floor ( Math.random ( ) * ( elements.length ) );
          } while (last == current );             
          $, settings, current, last);
                }, settings.timeout);
      } else if ( settings.type == 'random_start' ) {
        settings.type = 'sequence';
        var current = Math.floor ( Math.random () * ( elements.length ) );
         $, settings, (current + 1) %  elements.length, current);
        }, settings.timeout);
      } else {
       alert('Innerfade-Type must either be \'sequence\', \'random\' or \'random_start\'');

    $ = function(elements, settings, current, last) {
        if (settings.animationtype == 'slide') {
        } else if (settings.animationtype == 'fade') {
            $(elements[current]).fadeIn(settings.speed, function() {
        } else
            alert('Innerfade-animationtype must either be \'slide\' or \'fade\'');
        if (settings.type == "sequence") {
            if ((current + 1) < elements.length) {
                current = current + 1;
                last = current - 1;
            } else {
                current = 0;
                last = elements.length - 1;
        } else if (settings.type == "random") {
            last = current;
            while (current == last)
                current = Math.floor(Math.random() * elements.length);
        } else
            alert('Innerfade-Type must either be \'sequence\', \'random\' or \'random_start\'');
        setTimeout((function() {
            $, settings, current, last);
        }), settings.timeout);


// **** remove Opacity-Filter in ie ****
function removeFilter(element) {

      speed: 800,
      timeout: 4000,
      type: 'sequence',
      containerheight: '220px'

Now Inside Body Tag, Copy paste the following code. You can replace the image src with your own :)
Thats is! You are done. Thanks for viewing :)

Wednesday, July 14, 2010

SQL Bulk Copy with C#.Net

Bulk copying of data from one data source to another data source is a new feature added to ADO.NET 2.0. Bulk copy classes provides the fastest way to transfer set of data from once source to the other.
Each ADO.NET data provider provides bulk copy classes. For example, in SQL .NET data provider, the bulk copy operation is handled by SqlBulkCopy class, which is described in Figure 1. As you can see from Figure 1, data from a data source can be copied to one of the four types - DataReader, DataSet, DataTable, or XML.

Figure 1. Bulk Copy operation in ADO.NET 2.0

Using bulk copy operation, you can transfer data between two tables on the same SQL Server, between two different SQL Servers, or even two different types of database servers.

using System.Data.SqlClient;

public static void CopyData(DataTable sourceTable, SqlConnection destConnection, SqlTransaction destTrans, string destTableName)
          // new method: SQLBulkCopy:
          using (SqlBulkCopy s = new SqlBulkCopy(destConnection, SqlBulkCopyOptions.Default, destTrans))
                s.DestinationTableName = destTableName;
                s.NotifyAfter = 40000;
                //s.SqlRowsCopied += new SqlRowsCopiedEventHandler(s_SqlRowsCopied);

Calling the above function

         SqlConnection destinationConnection= new SqlConnection(sourceConnectionString);
         destinationTransaction =              destinationConnection.BeginTransaction(IsolationLevel.ReadUncommitted);

         DataTable SourceDataTable= new  DataTable();

         CopyData(SourceDataTable, destinationConnection, destinationTransaction, "DestinationTableName");