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:
- Enable “Ad Hoc Distribute” in the MSSQL Server
Enable “Ad Hoc Distribute” in the MSSQL ServerMySQL12345sp_configure 'show advanced options', 1;RECONFIGURE;sp_configure 'Ad Hoc Distributed Queries', 1;RECONFIGURE;GO
- Execute this query by filling up the parameters:
Setup connectionMySQL12SELECT a.*FROM OPENROWSET('provider_name', 'datasource';'user_id';'password','query') AS a;
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
- A quick example would be:
Connection ExampleMySQL1234SELECT a.*FROM OPENROWSET('SQLNCLI', 'MyServer\Instance01';'MyUser';'MyPassword','SELECT *FROM [dbo].[MyTable]') AS a;
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:Query exampleMySQL12345678910DECLARE @providerName VARCHAR(50) = 'SQLNCLI'DECLARE @dataSource VARCHAR(50) = 'MyServer\Instance01'DECLARE @username VARCHAR(50) = 'MyUser'DECLARE @password VARCHAR(50) = 'MyPassword'DECLARE @myQuery VARCHAR(MAX) ='SELECT a.*FROM OPENROWSET('''+@providerName+''','''+@dataSource+''';'''+@username+''';'''+@password+''',''SELECT *FROM [dbo].[MyTable] TWHERE T.foo = ''Have a nice day!'')AS a'EXEC sp_executesql @myQuery
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 🙂