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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

MERGE command w/ linked servers & udfs

Posted By:      Posted Date: September 12, 2010    Points: 0   Category :Sql Server
Hi guys - I have a MERGE statement I'm trying to fight with and losing. I'm  calling data from 2 linked servers and one of them has a user defined functions on it.. I have database A on one server (2008 R2) - the one with the UDFs and the source of the MERGE command. And database B on the other server (2008 sp1) - the target. The source of the MERGE does contain a call to a table function in server A. When both databases are on the same server, it runs fine and I'm not entirely sure how to fix it so I can get it to run between linked servers. I've tried both using OPENQUERY and just a normal statement...on the "normal" statement (Query #1) I get "Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database" when I run it "normally" . Which the compatibility of both 2008 and 2008R2 are set to 100. When i try with OPENQUERY (#2) I get "Incorrect syntax near the keyword 'AS" . It is a dynamic query because I need to pass in my database names. @A and @B are the linked server names. Query #1 (the "normal" one just using MERGE) DECLARE @A VARCHAR(50) DECLARE @B VARCHAR(50) DECLARE @LastUpdated DATETIME DECLARE @query VARCHAR(8000) SET @A = ? SET @B = ? SET @LastUpdated = ? SET @query = ' MERGE '+@B+'.database.dbo.tblFieldVersion as target USING ( SELECT c.x, st

View Complete Post

More Related Resource Links

Data Points: Updating Data in Linked Servers, Information Schema Views, and More


Every day a developer somewhere needs to write code to iterate through SQL ServerT system objects, query and update tables in linked servers, handle optimistic concurrency, and retrieve column and stored procedure metadata.

John Papa

MSDN Magazine November 2004

Using Impersonation or Certificates Across Linked Servers

We are trying to isolate the custom reporting stored procedures from our third-party SQL Server database ("SourceData" database) by placing all of those procedures in a separate database ("MyReporting" database) on a separate server. In addition, and most critically, I am trying to prevent all ad-hoc reporting (MS Access, Excel, developers) against the third-party database. To accomplish this, I grant permission to a specific user to execute a stored proc in the reporting database and then the stored proc accesses the data in the third-party database either by impersonation using EXECUTE AS or by certificates (following the model described in this MSDN article: http://msdn.microsoft.com/en-us/library/ms188304(SQL.90).aspx). However, as soon as I try to implement this model across multiple servers using Linked Servers, I am unsuccessful (with one unsatisfactory exception). As far as I can tell, I've tried every combination of option in the Linked Server setup. The unsatisfactory option it to map the reporting user to the "SourceDataID" user in the SourceData database. That works, however, that simply opens up the ability to perform ad-hoc reporting if a user connects to the reporting database since the Linked Server exposes all of the tables that the SourceDataID has access to.Below are diagrams describing how these models are setup:In this Imperso

Merge command


I have SQL Server 2008 and 2000 on one machine.

Can I use SQL server 2008s merge comand to Merge data into a table in SQL server 2000?

Can the source table be in 2000 or 2008 as long as I execute it from 2008?




Problem with Linked servers from sql 2005 to Excel file


I am probably doing something stupid BUT I am creating a linked server to an excel file, I have done this on sql2005sp2x64 using the ace ole 12 provder, and replicated on another machine running sql2005sp2x32 using the jet4 provider and I am having the same problem on both.  I have turned allow inprocess option on on both providers.  I as an administrator on the machine where the database and datafile are residing have no problem accessing the linked server and the file underneath however any enduser that is not in the local administrators group gets the error   Cannot initialize the data source object of OLE DB provier "Either provider I have tried" for linked server    SQL Server Error 7303.  I have checked file permissions, and they are open to the world, I have tried both use existing authentication and use no authentication when accessing the linked server.  and I have tried endless different combinations of providers and provider strings.  I have went as far as having the enduser create a linked server to an excel file on his local machine (which he is admin on and I am not)   and he can access the file .. and I can not.  so I am just assuming there is some sort of security setting somewhere .. that I am just missing.



Get Value from table in a Case statement with Linked Servers


I have 2 SQL Server instances that I need to query from.  I have figured out how link the 2 servers, I just don't know how to get the value from the other instance/table and put it in the case statement. 

Here are the tables:

Instance: Default     Database:GAD

Table Sample1

ID          Integer
V301054     Float
V301060     Float

Instance: MSSQL     Database:SCA

Table DESI
ID          Integer
Unit_Code    smallint
Unit_Name    Varchar(50)
Amount      Float

Here is the Select Statement with the when clause in it.

Select top (1)V301054A as MW, Availability = case when V301054A <= '.1' then 'Deployed' else '0' end FROM [Default].[SCA].[dbo].[SAMPLES1] AS SAMP1
Group by V301054A
order by MW desc

What I need to do is get the value from this Select Statement and put it where the 'Deployed' is in the above When Clause.

Cannot use Serializable Transaction Level with Linked Servers


I am testing an upgrade from SQL Server 2005 to SQL Server 2008 R2.  I have a stored procedure which attempts to use the serializable transaction isolation level on a distributed transaction (linked server). 

The query fails on SQL Server 2008 R2 with 


OLE DB provider "SQLNCLI10" for linked server "SERVER1" returned message "No transaction is active.".

Msg 7391, Level 16, State 2

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVER1" was unable to begin a distributed transaction.


If I change the isolation level to read committed, the transaction succeeds.  This same procedure worked on SQL Server 2005 but now fails on SQL Server 2008 R2 using the same isolation level. 

Is there a way to get this isolation level to work with linked servers on SQL Server 2008 R2?

Cann't Merge the database between the servers




I was created a database in sql server2000, which was manipulated with c#.net(front end). acutally i would like install 2 sql servers in 2 different locations(2 different time zone) to manage the same database. both  the servers should automatically update the database in the speific time duration (between them).


pls help me to set this.

Linked Servers Hangs


Hello all,

I've been having a very tough time trying to get the simplest of linked server to work. I've managed to create similar linked servers on my home PC, but the Management Studios on my office PC is hanging whenever I try to do anything with a linked server.

I have both SQL Server 2005 and 2008 express editions installed on my work PC. I also have a number of ODBC DSNs to a variety of data sources (ACCESS DBs, another MS SQL server (2005), an Oracle server, EXCEL files, .gdb databases, etc.). These DSNs all work properly to create connections, all have been tested outside of the Management Studio without any problem. 

I can create the Linked Servers in both 2005 and 2008 Servers, but any time I try to interact with them, the Management Studio hangs.

For example, if I:

     - Test Connection in 2008

     - Expand the Linked Server catalog in 2008

     - Run any form of query against the Linked server (This includes both direct queries and OpenQuery() queries)

     - Alter any of the Linked Server Properties.

Pretty much the only thing that I can do is create the linked server. Any other action on the linked server causes the Management Studio the hang, regardless of any timeout that I specify.

This has m

Data transfer between two linked SQL servers



I need to build a service that copies new rows from SqlServerA.DB1.TABLE_X to SqlServerB.DB2.TABLE_X. Direction is from SqlServerA to SqlServerB

Source and destination tables are the same structure. SqlServerA and SqlServerB are located in two different cities and there is VPN connectivity between them. The VPN is based on Internet and reasonably fast.

The service need to be able to recover when VPN is down and try again later when VPN is running again.

I wonder if linked servers are suitable for this scenario? I can avoid joining distributed tables together and leverage linked servers by using INSERT statements.

I am aware of MERGE replication and I wonder if MERGE replication is a simpler alternative?

SQL Standard: Can we compress the data passed between linked servers?



I have to connect two sql server 2008 standard edition as linked servers. Is there any possible way to compress the data passed between two linked servers?

Thank you.

Linked Servers Between Enterprise and Web Edition



I'm having a strange time trying to get an instance of Enterprise Edition SQL 2008 R2 to link with a Web Edition. We've been able to link to other instances without issues, and whenever we go to link with the Web Edition, we get the error:


Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

The two VMs are on the same domain, with the same permissions, and this error does not appear when we link two Enterprise servers. I've googled around and I couldn't see anything that says we can't link the two versions up, and the error implies some kind of permissions thing, but both servers share the same Active Directory, and have the same logins.


I'm stumped. Any ideas on how to start solving this?

Many thanks



Edit: to clarify, the users are in AD Groups, "user_Admin". The group is added as a login and mapped to the various DBs on both servers. We can only use SSPI authentication - no SQL Server Authentication.

Using Linked Servers within SqlCommand

Anyone have a clue why I can run "SELECT TOP 10 * FROM [myServer].[myDatabase].[dbo].[myTable]" from my SQL server but I can't run the following from a page hos

Are linked servers an issue on SQL Server 2008 R2 64 bit platform?

Recently a Microsoft employee strongly recommended not using linked servers at all.   I have a different thread on this topic which died.  However, I'd like to get feedback regarding linked servers on SQL Server 2008 R2 64 bit platforms.
michelle jenks

Row command working on all rows not selected one?



I have this code that hides a column in a gridview.  (The column houses another nested gridview).

    Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles GridView1.RowCommand
        If e.CommandName = "Select" Then
            If GridView1.Columns(0).Visible = False Then
                GridView1.Columns(0).Visible = True
                GridView1.Columns(0).Visible = False
            End If
        End If
    End Sub

The trouble is that if I click the select link on one row that action to hide is carried out on all rows.  How can I set this up so that it only hides the row that is selected?


Print command



Is there a way of setting up a print area for a part of my webpage, I only want to print a certain part of the page, when I select print at the moment it will print the whole page and the layout is rubbish!!

command object error


Could someone please tell me what is wrong with my syntax here?  I'd be much appreciative.


objCmd = New SQLCommand("SELECT CASE WHEN objDR('Item') = 'Enter Bill of Lading' then enterbol " _  

& "WHEN objDR('Item') = 'Bill of Lading History' THEN bolhistory " _  

& "WHEN objDR('Item') = 'Request Transfer' or objDR('Item') = 'View Transfer in Progress' THEN transfers " _  

& "END  from plantGroups where plantgroupid=@plantgroupid", objConn)

how to make a css style background image be linked to an image in the resource file


hey guy, i need help in a very troubling matter. I want to make a textbox with a specific style, within this style a set of images as background parts, how can i attach these images to the custom control as whenever i take the .dll file and add it to the toolbar the images do not appear. So i tried to make them as resource files and their property as embedded in .resx file, so how to make the css style background image url to be linked to any of those image.

thanks in advance

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