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.
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.
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 readingOne 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.
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.
In SQL Server, you can use SET ROWCOUNT to limit the rows returned by a query.
The way it works is that it causes SQL Server to stop processing the query after the specified number of rows are returned.
It’s similar to the TOP() clause, but with the difference that SET ROWCOUNT is set outside of the query, and will affect all subsequent queries.
If you get error message 7707 in SQL Server, it’s because you’re trying to create a partition scheme that doesn’t specify enough filegroups to match the partition function.
Fortunately, this is easy to fix.
Continue readingSometimes 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 readingIn SQL Server, you can use the SOME logical operator to compare a scalar value with a single-column set of values returned by a subquery.
It can be used with subqueries that have a result set of one column.
The SOME operator is the equivalent of the ANY logical operator.
In SQL Server, you can use the ANY logical operator to compare a scalar value with a single-column set of values returned by a subquery.
It can be used with subqueries that have a result set of one column.
The ANY operator is the equivalent of the SOME logical operator.