If you’ve encountered error Msg 7411, Level 16 in SQL Server, it’s because you need to enable “RPC out” on the linked server that you’re trying to execute code on.
Continue readingTag: t-sql
Find Out the Data Type of the Columns Returned in a Result Set in SQL Server
In SQL Server, there are a few ways to get metadata of the result set from a query. This includes the data type of the columns returned by a T-SQL query.
In particular, the sys.dm_exec_describe_first_result_set system dynamic management function is a good choice for such a task.
How to Fix “Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.” Error in SQL Server
It’s quite easy to encounter error Msg 214, Level 16 when executing stored procedures such as sp_executesql or sp_describe_first_result_set.
Fortunately it’s easy to fix too!
The most common reason for getting this error is that you forgot to prefix your string with N.
Therefore, to fix this issue, try prefixing your string with N.
3 Ways to Get the Job Steps of a SQL Server Agent Job (T-SQL)
In this article, I present three options for returning the steps of a SQL Server Agent job when using T-SQL.
Continue reading3 Ways to Extract the Month from a Date in SQL Server (T-SQL)
Here are three T-SQL functions that you can use to extract the month from a date in SQL Server.
Continue readingGet the Underlying Columns of a View Based on its Result Set
One of the great things about database views, is that they allow you to run complex queries without needing to know the underlying database schema.
Yes, it’s true that you need to know the underlying schema when you create the view, but you only need to do that once. Once you’ve created it, you can query that view all day long without needing to remember all the table and column names, etc.
Views typically combine data from multiple tables into a single, virtual table, which makes it kind of like a “black box”. As long as it works as designed, you don’t need to concern yourself with the hidden details.
But what if you do want to check a view for its underlying tables and columns?
Continue readingOverride the Query Optimizer for your T-SQL Joins with FORCEPLAN
The SET FORCEPLAN statement overrides the logic used by the SQL Server query optimizer to process a T-SQL SELECT statement.
More specifically, when FORCEPLAN is set to ON, the query optimizer processes a join in the same order as the tables appear in the FROM clause of a query.
This also forces the use of a nested loop join unless other types of joins are required to construct a plan for the query, or they are requested with join hints or query hints.
Continue readingAbout the GO Command in SQL Server
If you’ve used T-SQL you will probably be familiar with the GO command. This command is often placed at the end of a batch of statements.
Extract the Week Number from a Date in SQL Server (T-SQL)
You can use the T-SQL function DATEPART() to return the week number from a date in SQL Server.
By “week number” I mean the week’s number within the year of the specified date.
Continue readingReturn the ISO Week Number from a Date in SQL Server (T-SQL)
If you need to extract the ISO week number from a date in SQL Server, you can use the iso_week argument when calling the DATEPART() function. You can alternatively use the isowk or isoww arguments to do the same thing.
By “ISO week”, I’m referring to the ISO 8601 date and time standard.
Continue reading