Posts written by Nate Simmons

A Non-Trivial SQL CLR Table Value Function Example

Reprinted with permission from owner. Original at http://nlsimmons.com/SQLScraps/

Overview

In this post I would like to demonstrate how to create a simple yet non-trivial CLR table-valued function (TVF). There are several examples of CLR TVFs on the net but the majority of them show only a trivial example. It is very difficult, in some cases impossible, to apply the principles and code in those examples to your own CLR TVF. The TVF outlined in this post is used to return the IP address for a given host name. Keep reading…

Msg 14274, Level 16, State 1, Procedure sp_add_job

Reprinted with permission from owner. Original at http://nlsimmons.com/SQLScraps/

Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

Recently I ran into this error on an instance of SQL Server 2005. There are several articles and blog posts concerning this error on SQL Server 2000 systems, but next to none that discuss the error in SQL Server 2005. For a list of all of the procedures in MSDB that can throw this error, execute the following query:

Keep reading…

Scripting Stored Procedure Permissions

Reprinted with permission from owner. Original at http://nlsimmons.com/SQLScraps/

Overview

When scripting stored procedure code for deployment to several servers, there are a few things to keep in mind.  One of those things is the permissions associated with the procedure you’re deploying.  When deploying a brand new stored procedure, the process of assigning permissions is fairly straightforward.  If you need to deploy an update to an existing stored procedure, things get a bit more complicated.  If you simply issue a DROP PROCEDURE call followed by the CREATE PROCEDURE code, you will lose the permissions that are associated with the stored procedure.  Issuing an ALTER PROCEDURE command will preserve the permissions but will cause you trouble if there are places where the procedure doesn’t yet exist.  To solve this issue, you should script out the permissions on a procedure to a temp table before dropping the procedure.  Keep reading…

We're Here To Help.

Database problem? Tell us a little bit about it.

Name*

Email*

Subject

Message*

captcha

* Required