SQL Server provides the STUFF() function that enables you to insert a string inside another string.
The function also allows you to specify the number of characters (if any) to delete from the original string in order to fit the new string.
Continue readingThere are several ways to return the year from a date in SQL Server. Here are three (or is it four?).
Continue readingThere 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:
LOGINPROPERTY() function. sys.server_principals system catalog view in the master database.Examples of these are below.
Continue readingIn 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.
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.
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 readingYou 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 readingIf 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.
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.
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