In SQL Server, the ANSI_NULLS setting allows you to specify how NULL values are treated in queries.
You can use the SESSIONPROPERTY() function to check the ANSI_NULLS setting for the current session.
Tag: how to
How to Redefine the Columns Returned by a Stored Procedure in SQL Server
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.
How to Fix “EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set…” Msg 11537 in SQL Server
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.
How to Select a Subset of Columns from a Stored Procedure’s Result Set (T-SQL)
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.
How to Fix “profile name is not valid” When Updating a Database Mail Profile in SQL Server (T-SQL)
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.
Check if RPC Out is Enabled on a Linked Server
RPC stands for Remote Procedure Calls. It needs to be enabled before you can execute stored procedures on a linked server.
If you’re not sure whether it’s enabled on a linked server, you can check it’s setting by querying the sys.servers system catalog view.
2 Ways to Create a Database on a Linked Server using T-SQL
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.
2 Ways to Create a Table on a Linked Server using T-SQL
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.
3 Ways to Get the Schema of a Result Set in SQL Server
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 readingCompress a Specific Partition within a Table in SQL Server (T-SQL)
When you compress a partitioned table in SQL Server, you can compress all partitions, some, or just one partition.
To do this, use the REBUILD PARTITION syntax within the ALTER TABLE statement.
When doing this, you can rebuild just the specified partition or all partitions. Alternatively, you can rebuild all partitions, while compressing only a specific partition or list of partitions.
Continue reading