There are several ways to return the year from a date in SQL Server. Here are three (or is it four?).
Continue readingTag: mssql
2 Ways to Get the Default Language of a Login in SQL Server (T-SQL)
There are (at least) a couple of ways you can use T-SQL to return the default language of a specified login in SQL Server.
In particular, you can use the following methods:
- Query the
LOGINPROPERTY()function. - Query the
sys.server_principalssystem catalog view in themasterdatabase.
Examples of these are below.
Continue readingRename a User Defined Data Type in SQL Server (T-SQL)
In SQL Server, you can use the sp_rename stored procedure to rename a user created object in the current database, including a user-defined data type.
How to do an UPDATE Pass-Through Query in SQL Server
In SQL Server, the OPENQUERY rowset function enables you to execute a pass-through query on a linked server.
OPENQUERY is commonly referenced in the FROM clause of a query as if it were a table, but it can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.
This article presents an example of using OPENQUERY to do an UPDATE pass-through query.
How to Reference JSON Keys that Contain Special Characters when using OPENJSON, JSON_QUERY, and JSON_VALUE (SQL Server)
If you’re using a T-SQL function such as OPENJSON(), JSON_QUERY(), or JSON_VALUE(), you might be wary of any non-alphanumeric characters that might be in the JSON document that you’re working with. Especially if those special characters are in the key names, and you need to reference those key names.
OPENROWSET does not accept variables for its arguments (SQL Server)
If you’re trying to use OPENROWSET to run an ad hoc distributed query in SQL Server, but you keep getting an “incorrect syntax” error, and you just can’t see anything wrong with your syntax, it could be that you’re trying to use a variable for one of the arguments.
As mentioned in the title of this post, OPENROWSET does not accept variables for its arguments.
If you’re using variables as arguments, try changing these to string literals.
Continue readingConvert a Month Number to the Month Name in SQL Server (T-SQL)
You can use the T-SQL code below to convert a month number to its corresponding name in SQL Server.
This is for times where you don’t actually have the full date – when all you have is the month number. If you do have the date, then here’s how to get the month name from a date.
Continue readingReturn the Partition Number for Each Row When Querying a Partitioned Table in SQL Server (T-SQL)
If you’ve got a partitioned table in SQL Server, and you now want to run a query that includes the partition number on each row returned by the query, you can use the $PARTITION system function to do just that.
The $PARTITION function returns the partition number into which a set of partitioning column values would be mapped for any specified partition function.
You can therefore use it in your SELECT statement to return the partition that each row belongs to.
How to Edit Linked Server Options using T-SQL
If you’ve previously created a linked server, but you now want to update one or more of its server options, you can do that either via the SQL Server Management Studio GUI or with T-SQL.
If you choose to do it with T-SQL, the sp_serveroption system stored procedure is what you need.
Get the Short Day Name in SQL Server (T-SQL)
In SQL Server, you can use the FORMAT() function to return the short day name from a date. FORMAT() is a T-SQL function that enables you to format dates and numbers to a specified format.
This function returns its result as a string. Specifically, it returns it as either a nvarchar or null as the case may be.
Continue reading