Category: SQL Server

How to Enable RPC Out using T-SQL

Posted on by Ian

You may occasionally need to enable the “RPC Out” option on a linked server. This option enables RPC to the given server.

RPC stands for Remote Procedure Calls. RPC is basically a stored procedure being run remotely from Server 1 to linked Server 2.

If you don’t enable this and you try to execute a stored procedure on the linked server, you’ll probably get error Msg 7411 telling you that the server is not configured for RPC.

Anyway, you can enable/disable this option either using SQL Server Management Studio (SSMS) or with T-SQL.

Continue reading →

2 Ways to Create a Database on a Linked Server using T-SQL

Posted on by Ian

One way of creating a database on a linked server, is to simply jump over to that server and create it locally.

But you’d probably feel a bit cheated if I included that as one of the “2 ways” to create a database on a linked server.

Also, while that option is fine if you’re able and willing to do it, this article shows you how to do it remotely using T-SQL, without having to jump over to the local server. Plus you might find this technique quicker than jumping over to the other server.

Both of the “2 ways” involve the EXECUTE statement (which can also be shortened to EXEC). We can use this statement to execute code on the linked server, and that includes creating a database on it.

Continue reading →

2 Ways to Create a Table on a Linked Server using T-SQL

Posted on by Ian

If you need to create a table on a linked server, you can do this either directly on the remote server, or you can do it by running a script from your local server.

Here are two ways of using T-SQL to create a table on a linked server.

Both methods use the EXECUTE/EXEC statement. But each uses a different approach.

Continue reading →

3 Ways to Get the Schema of a Result Set in SQL Server

Posted on by Ian

Sometimes when you run a query in SQL Server, you might want to know what the underlying data type of each column is, its precision, length, whether or not its nullable, etc.

If you’re querying just one table, you can get this sort of data with procedures such as sp_columns. But if your query runs across many tables, this could get unwieldy very quickly.

Fortunately there are several ways you can go about getting such metadata for a result set in SQL Server.

Continue reading →

How sys.dm_exec_describe_first_result_set_for_object Works in SQL Server

Posted on by Ian

In SQL Server, the sys.dm_exec_describe_first_result_set_for_object dynamic management function returns the metadata of the first result set for a given module.

It takes an @object_id as a parameter and describes the first result metadata for the module with that ID.

It uses the same algorithm as the sp_describe_first_result_set system stored procedure and the sys.dm_exec_describe_first_result_set function, and does pretty much the same thing, except that it’s limited to just stored procedures and triggers.

If you pass the ID of a different object type (such as a view, function, table, etc) then it will return an error.

Continue reading →

How sys.dm_exec_describe_first_result_set Works in SQL Server

Posted on by Ian

In SQL Server, the sys.dm_exec_describe_first_result_set dynamic management function returns the metadata of the first result set for a given T-SQL statement or statements.

This function uses the same algorithm as the sp_describe_first_result_set system stored procedure, and does pretty much the same thing.

It accepts three parameters, the first of which is the T-SQL statement/s you’re analysing.

Continue reading →