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) 

RETURNS VARCHAR(8000)

AS

BEGIN

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

END

Friday, August 5, 2011

MCTS 70-515 Book - Dumps

Hey Guys, hope everyone is doing well.

I am writing here about some information regarding MCTS Self-Paced Training Kit (Exam 70-515): Web Applications Development with Microsoft® .NET Framework 4.


The exam comprises of 50 questions and major focus was on new stuff such as ASP.NET MVC, jQuery, Dynamic Data Websites, ASP.NET web forms 4 etc. Click here to see the overall skills measured. 


I have just got the EBook of it. You can view E-Book here

Also, you can buy it your personal copy from Amazon.


MCTS Self-Paced Training Kit (Exam 70-515): Web Applications Development with Microsoft .NET Framework 4 (Mcts 70-515 Exam Exam Prep)

 Page (iii) of the book describes the overall exam's topics structure has been distributed in this book.





I must also thanks Mr. Adil Mughal for his post.

Wednesday, July 13, 2011

Installing Android with Eclipse

The latest Android SDK for Windows, Mac and Linux can always be obtained from the following URI:


It only needs to be downloaded and unzipped to your preferred hdd-location.

The Android Development Tools (ADT)

Android provides an Eclipse plugin called ‘ADT’ to make programming and debugging easier.

The ADT provides easy access to the LogCat, the Android-Manifest/Resource-Editor, File, Thread, and Heap Control, incoming call/sms simulation, etc… – since SDK-version m5 all for multiple emulator instances at the same time.

Installing the Eclipse Plugin (ADT)

To download and install the ADT plugin, follow the steps Google provides to developers:

1. Start Eclipse, then select Help > Software Updates > Find and Install....

2. In the dialog that appears, select Search for new features to install and press Next.

3. Press New Remote Site.

4. In the resulting dialog box, enter a name for the remote site (e.g. Android Plugin) and enter this as its URL:


Press OK.

5. You should now see the new site added to the search list (and checked). Press Finish.

6. In the subsequent Search Results dialog box, select the checkbox for Android Plugin > Developer Tools. This will check both features: "Android Developer Tools", and "Android Editors". The Android Editors feature is optional, but recommended. If you choose to install it, you need the WST plugin mentioned earlier in this page.
Now press Next.

7. Read the license agreement and then select Accept terms of the license agreement, if appropriate. Press Next.

8. Press Finish.

9. The ADT plugin is not signed; you can accept the installation anyway by pressing Install All.

10. Restart Eclipse.

11. After restart, update your Eclipse preferences to point to the SDK directory:

a. Select Window > Preferences... to open the Preferences panel. (Mac OS X: Eclipse > Preferences)
b. Select Android from the left panel.
c. For the SDK Location in the main panel, press Browse... and locate the SDK directory.
d. Press Apply, then OK.

Updating the ADT Plugin

Updating the ADT Plugin follows the standard procedure of upgrading a common Eclipse plugin:

1. Select Help > Software Updates > Find and Install....

2. Select Search for updates of the currently installed features and press Finish.

3. If any update for ADT is available, select and Install.

That's it, you are done.

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.
DELETE TOP (1)
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 
GROUP BY CustomerID
HAVING COUNT(CustomerID) > 1

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

END 
 



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

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

OPEN dupCursor 

FETCH NEXT FROM dupCursor 
INTO @custID, @cnt

WHILE @@FETCH_STATUS = 0
BEGIN
    DELETE Top (@cnt-1)
    FROM tmpCustomer
    WHERE CustomerID = @custID
    
    FETCH NEXT FROM dupCursor 
    INTO @custID, @cnt
END

CLOSE dupCursor
DEALLOCATE dupCursor 



This ran much better at 18 seconds. Enjoy.