Room of a pleinolijf

Ask yourself this: how do I want to be remembered ?


4 Comments

MS SQL Server: Error 14724 – MSX server

I wanted to change several jobs on our MS SQL Server just now. I got the following error when I tried saving a job after deleting a step:

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

After using my friend (Google that is), I found out that the reason for the error is that a job remembers the name of the server on which it was originally created on. If the name of the server changes after the job has been created, and you want to edit that job, it will not be possible to do so.
The only solution is to change the originating_server attribute of the job to the current servername.

You can do this by executing the following transact-sql command:

UPDATE msdb.dbo.sysjobs
SET originating_server = CONVERT(nvarchar, SERVERPROPERTY('servername'))
WHERE originating_server <> CONVERT(nvarchar, SERVERPROPERTY('servername'))

Do note that some solutions online will offer the following answer:
UPDATE msdb.dbo.sysjobs
SET originating_server = @@SERVERNAME
WHERE originating_server <> @@SERVERNAME

Which is not entirely correct, as @@SERVERNAME doesn’t automatically update itself, as explained here [ msdn.microsoft.com ]:

Although the @@SERVERNAME function and the SERVERNAME property of SERVERPROPERTY function may return strings with similar formats, the information can be different. The SERVERNAME property automatically reports changes in the network name of the computer.

In contrast, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.

Make sure you first do a select * from msdb.dbo.sysjobs to make sure you don’t mess up your system 😉

Technorati Tags: , , , ,


11 Comments

SQL Split String into Table

If you ever needed to split a delimited string into a table of rows, here’s what you need.

In the example I needed to join all the values (which were actually keyfields) in the string with an existing table. This function was exactly what I needed.

[ do pardon the crappy layout, this is the best I could do in a short time ]

CREATE FUNCTION iter_charlist_to_table

(@list      ntext,

@delimiter nchar(1) = N',')

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

str     varchar(4000),

nstr    nvarchar(2000)) AS BEGIN

DECLARE @pos      int,

@textpos  int,

@chunklen smallint,

@tmpstr   nvarchar(4000),

@leftover nvarchar(4000),

@tmpval   nvarchar(4000)

SET @textpos = 1

SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2

SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)

SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0

BEGIN

SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))

INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)

SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))

SET @pos = charindex(@delimiter, @tmpstr)

END

SET @leftover = @tmpstr

END

INSERT @tbl(str, nstr)

VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))

RETURN

END

And a version for a list of integers (faster; space-delimited):

CREATE FUNCTION iter_intlist_to_table

(@list ntext)

RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,

number  int NOT NULL) AS

BEGIN

DECLARE @pos      int,

@textpos  int,

@chunklen smallint,

@str      nvarchar(4000),

@tmpstr   nvarchar(4000),

@leftover nvarchar(4000)  SET @textpos = 1

SET @leftover = ''

WHILE @textpos <= datalength(@list) / 2

BEGIN

SET @chunklen = 4000 - datalength(@leftover) / 2

SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))

SET @textpos = @textpos + @chunklen

SET @pos = charindex(' ', @tmpstr)

WHILE @pos > 0

BEGIN

SET @str = substring(@tmpstr, 1, @pos - 1)

INSERT @tbl (number) VALUES(convert(int, @str))

SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))

SET @pos = charindex(' ', @tmpstr)

END

SET @leftover = @tmpstr

END

IF ltrim(rtrim(@leftover)) <> ''

INSERT @tbl (number) VALUES(convert(int, @leftover))

RETURN

END

You simply create these two user defined functions in SQL Server, and they are ready to use like this:

select @total = sum(t.invoiced)
from invoices t
-- function to split a ";"-delimited string
inner join iter_charlist_to_table(@budgetid, ';') i on i.[str] = t.budgetid

The resulting table of the charlist function will contain two columns: “str” -the value itself-, and a “number” column -just an autonumber-.
The intlist result will be a table with “listpos”, and “number”, the latter being the int value in the list. A bit confusing between the two functions, I know. But it’s easy to rename them yourself 😉

My source was this site (Erland Sommarskog).