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: , , , ,


Leave a comment

SQL Server Enterprise Manager table view bug?

*sigh*
The next big issue has arrived, this time in SQL Server Enterprise Manager 😦

It’s like this… Today I was working on a certain database as usual, generating scripts. Nothing special, all worked fine. After lunch, I return to continue work -after surfing, e-mailing, you know- and suddenly I can’t see the contents of a table in EM !
First I’m thinking “Shit, I’ve overwritten the wrong database”, but this is not the case, as Query Analyzer tells me. This is really weird, you know: I’m a 100% sure that it is the same tables on the same database on the same server registration. QA neatly shows me the two rows of a given table, EM shows nothing, not even column headers. I can’t edit any cells and I’m also unable to get any life out of the buttons in the toolbar when in table view. And this problem persists on all registrations in my EM !!

Come to think of it, this actually happened after I executed the few create table/sproc scripts in QA, which I created earlier on. No unusual stuff showed up.

I really don’t know what caused this… I tried everything: restarting app, restarting service, restarting computer, played with network utility, … nothing! And each time QA lists the records like it should, while EM refuses to do anything.

[ edit: as I expected, the bug died automagically. Last Friday, I needed to view a table’s content, and figured “what the hell, let’s try it again, who knows”, and there it was: the table’s content. Go figure. ]


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).