Tag: mssql

How to Redefine the Columns Returned by a Stored Procedure in SQL Server

Posted on by Ian

When you execute a stored procedure that returns a result set in SQL Server, the columns returned are defined in the stored procedure.

But did you know that you can redefine those columns?

What I mean is, you can change the names and/or the data type of the columns returned in the result set.

This could save you from having to fiddle with the column headers and data formats in the event you needed to use that result set in another setting.

For example, if a stored procedure returns a datetime2 column, but you only need the date part, you could specify date for that column, and your result set will only include the date part.

And the best part is that you can do it as part of the EXECUTE statement. No need to massage the data after executing the procedure. way to do this is by using the WITH RESULT SETS clause of the EXECUTE statement.

Continue reading →

How to Fix “EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s)…” in SQL Server

Posted on by Ian

If you encounter error Msg 11535, Level 16 while trying to execute a stored procedure, it’s because you didn’t define enough result sets in the WITH RESULT SETS clause.

Some stored procedures return multiple result sets. When using the WITH RESULT SETS clause, you need to define each expected result set. You need to do this even if you only want to change the definition of one or some of the result sets.

To fix this error, simply add the additional result sets to the WITH RESULT SETS clause, each separated by a comma.

You could also fix it by removing the WITH RESULT SETS clause, but I’ll assume you’re using it for a reason (i.e. you need to redefine the result set returned by the procedure).

Continue reading →

How to Fix “EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set…” Msg 11537 in SQL Server

Posted on by Ian

If you encounter error Msg 11537, Level 16 in SQL Server, chances are that you’re trying to execute a stored procedure by using the WITH RESULT SETS clause, but you haven’t included all the columns in your definition.

When you use the WITH RESULT SETS clause in the EXECUTE/EXEC statement, you must provide a definition for all columns returned by the stored procedure. If you don’t, you’ll get this error.

Continue reading →

How to Select a Subset of Columns from a Stored Procedure’s Result Set (T-SQL)

Posted on by Ian

Have you ever run a stored procedure, only to be overwhelmed at the number of columns returned? Maybe you only needed one or two columns, but it presented you with way too many columns for your needs on this particular occasion.

Fortunately, there’s a little trick you can use to retrieve selected columns from a stored procedure. This enables you to get just the columns you need.

And the best part is, it doesn’t involve having to create temporary tables and shuffling the data around.

All you need to do is pass your stored procedure to the OPENROWSET() function.

The same concept can be applied to the OPENQUERY() function.

Continue reading →

How to Fix “profile name is not valid” When Updating a Database Mail Profile in SQL Server (T-SQL)

Posted on by Ian

If you’re getting a “profile name is not valid” error when updating a Database Mail profile in SQL Server, it could be that you’ve forgotten to provide the profile ID.

When you update a Database Mail profile with the sysmail_update_profile_sp stored procedure, you need to include the profile ID if you want to update the profile name.

Continue reading →

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 →