I was trying to run a stored procedure against another server earlier, and therefore, I found that Microsoft provided a method called “OPENROWSET” to run a query from a server against another one. One important thing I learned is that this method will not support for query that returns an XML. I learned it the hard way, hope you won’t make the same mistake.

Here’s are the steps of using it:

  1. Enable “Ad Hoc Distribute” in the MSSQL Server
  2. Execute this query by filling up the parameters:

    provider_name has no default value
    datasource is basically the server name
    user_id is the login username of the MSSQL Server password is the login
    password of the MSSQL Server
    query is the query you want to run
  3. A quick example would be:

    Note that if you want to parameterize the provider_name, username and password, you need to use dynamic sql. That means you need to make the whole query as a string and execute it.

    like this:

Note that you have to replace each single quote sign (‘) with two single quote signs to make it work.

Another useful note is that, if your query is longer than 4000 characters and you might want to use NVARCHAR(MAX) or VARCHAR(MAX). MSSQL may or may not truncate your query base on the following combination:

  • varchar(n) + varchar(n) will truncate at 8,000 characters.
  • nvarchar(n) + nvarchar(n) will truncate at 4,000 characters.
  • varchar(n) + nvarchar(n) will truncate at 4,000 characters.
  • nvarchar has higher precedence so the result is nvarchar(4,000) [n]varchar(max) + [n]varchar(max) won’t truncate.
  • varchar(max) + nvarchar(n) won’t truncate and the result will be typed as nvarchar(max).
  • nvarchar(max) + varchar(n) will first convert the varchar(n) input to nvarchar(n) and then do the concatenation.
  • If the length of the varchar(n) string is greater than 4,000 characters the cast will be to nvarchar(4000) and truncation will occur.

Credit to Martin Smith from StackOverflow 🙂