Showing posts with label generated. Show all posts
Showing posts with label generated. Show all posts

Monday, March 26, 2012

how disable MSX server generated job

I have some jobs that when I want to delete or disable it I get bellow error
Msg:
"Error 14274 : cannot add, update, or delete a job that originated from an
MSX server"
what it means? how can I delete this job?
Thanks
TarvirdiIf memory servers, go into the MSDB.dbo.SysJobs table and update the
originating_server column to be that of the name the job currently resides
on. You should then be able to do whatever you want.
"Tarvirdi" wrote:

> I have some jobs that when I want to delete or disable it I get bellow err
or
> Msg:
> "Error 14274 : cannot add, update, or delete a job that originated from an
> MSX server"
> what it means? how can I delete this job?
> Thanks
> Tarvirdi
>
>

how disable MSX server generated job

I have some jobs that when I want to delete or disable it I get bellow error
Msg:
"Error 14274 : cannot add, update, or delete a job that originated from an
MSX server"
what it means? how can I delete this job?
Thanks
TarvirdiIf memory servers, go into the MSDB.dbo.SysJobs table and update the
originating_server column to be that of the name the job currently resides
on. You should then be able to do whatever you want.
"Tarvirdi" wrote:
> I have some jobs that when I want to delete or disable it I get bellow error
> Msg:
> "Error 14274 : cannot add, update, or delete a job that originated from an
> MSX server"
> what it means? how can I delete this job?
> Thanks
> Tarvirdi
>
>

Monday, March 19, 2012

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.
I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegro ups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>
|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> I can't make head or tail of what you are trying to do here (mostly because
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, it
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired results.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegro ups.com...

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> I can't make head or tail of what you are trying to do here (mostly becaus
e
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, i
t
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired result
s.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...|||On 3 Jan 2007 06:50:11 -0800, Loganx80 wrote:

>OK,
>Sorry that my example isn't the easiest to understand. I was actually
>just trying to make it more readable. If it's true that the CASE
>expression cannot return more than one value (such as an IN list), then
>I may need to take another approach, but let me at least give you a
>better example.
>Let's say I have a table with products in it. Each product has a
>category #.
>Categories:
>1 Shoes
>2 Pants
>3 Shirts
>4 TVs
>5 DVD Players
>6 Xboxes
>Now I want to do a select statement based upon a category type passed
>into the procedure @.CategoryType. When @.CategoryType is "electronics",
>I want to return all products with category numbers 4, 5, or 6.
>Likewise, if "clothing" is passed in, I want to return all products
>with 1, 2, 3.
>SELECT * FROM Products WHERE CategoryID IN
> (CASE @.CategoryType
> WHEN "electronics" THEN (4,5,6)
> WHEN "clothing" THEN (1,2,3)
> END)
>
>Hopefully, this makes more sense.
Hi Matthew,
Best way to do this is to add a categories table with a CategoryID and a
CategoryType column. That way, you can add products (and categories!)
without having to change any queries. I have always hated hard-coded
magic values.
But if you do want to hard-code the values, try something like this
SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM Products
WHERE (@.CategoryType = 'electronics' AND CategoryID IN (4, 5, 6))
OR (@.CategoryType = 'clothing' AND CategoryID IN (1, 2, 3));
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:
> I can't make head or tail of what you are trying to do here (mostly because
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, it
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired results.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
> >I am trying to run a query that filters using an IN statment, but the
> > IN statement needs to be dynamically generated based upon some
> > criteria. Here is my example:
> >
> > DECLARE @.tempTable TABLE
> > (
> > letter char(1),
> > id int
> > )
> >
> > INSERT INTO @.tempTable VALUES ('A', 1)
> > INSERT INTO @.tempTable VALUES ('B', 2)
> > INSERT INTO @.tempTable VALUES ('C', 3)
> >
> > SELECT * FROM @.tempTable WHERE letter IN
> > (Case 2
> > WHEN 1 THEN ('A')
> > WHEN 2 THEN ('B', 'C')
> > END)
> >
> >
> > If you try to run this code, it gives an error on the line that
> > contains the 'B', 'C' (incorrect syntax near ',').
> >
> > If you remove the C and just leave the two lines, one with A, one with
> > B, then it will work as expected.
> >|||On 3 Jan 2007 06:50:11 -0800, Loganx80 wrote:
>OK,
>Sorry that my example isn't the easiest to understand. I was actually
>just trying to make it more readable. If it's true that the CASE
>expression cannot return more than one value (such as an IN list), then
>I may need to take another approach, but let me at least give you a
>better example.
>Let's say I have a table with products in it. Each product has a
>category #.
>Categories:
>1 Shoes
>2 Pants
>3 Shirts
>4 TVs
>5 DVD Players
>6 Xboxes
>Now I want to do a select statement based upon a category type passed
>into the procedure @.CategoryType. When @.CategoryType is "electronics",
>I want to return all products with category numbers 4, 5, or 6.
>Likewise, if "clothing" is passed in, I want to return all products
>with 1, 2, 3.
>SELECT * FROM Products WHERE CategoryID IN
> (CASE @.CategoryType
> WHEN "electronics" THEN (4,5,6)
> WHEN "clothing" THEN (1,2,3)
> END)
>
>Hopefully, this makes more sense.
Hi Matthew,
Best way to do this is to add a categories table with a CategoryID and a
CategoryType column. That way, you can add products (and categories!)
without having to change any queries. I have always hated hard-coded
magic values.
But if you do want to hard-code the values, try something like this
SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM Products
WHERE (@.CategoryType = 'electronics' AND CategoryID IN (4, 5, 6))
OR (@.CategoryType = 'clothing' AND CategoryID IN (1, 2, 3));
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Monday, March 12, 2012

How can we control the recursion in CTE?

Hi,

I would like to have some sort of control to recursive CTE, i.e. the query result should be generated level by level.

By default, it looks like this:

levels parentid, categoryid category name

--

1 0 1 aaa

2 1 100 bbb

2 1 101 ccc

2 1 102 ddd

3 102 200 xxx

3 102 201 yyy

4 201 300 zzz

5 300 400 qqq

3 101 210 ppp

....

How can I control the JOIN so that sql server query engine will process join level by level? the CTE I used as following:

with cte(levels, parentid, categoryid, title) as (

select 1,

parentid,

categoryid,

title

from categories

where parentid = 0

union all

select p.levels+1,

c.parentid,

c.categoryid,

c.title

from cte p join categories c on p.categoryid = c.parentid

)

select *

from cte

Thanks

Bill:

If what you want is just to sort the data by level, a simple order by will do -- something like:

declare @.categories table
( levels int,
parentId int,
categoryId int,
title varchar(10)
)
insert into @.categories
select 1,0,1,'aaa' union all
select 2,1,100,'bbb' union all
select 2,1,101,'ccc' union all
select 2,1,102,'ddd' union all
select 3,102,200,'xxx' union all
select 3,102,201,'yyy' union all
select 4,201,300,'zzz' union all
select 5,300,400,'qqq' union all
select 3,101,210,'ppp'

;with cte(levels, parentid, categoryid, title) as (
select 1,
parentid,
categoryid,
title
from @.categories
where parentid = 0
union all
select p.levels+1,
c.parentid,
c.categoryid,
c.title
from cte p join @.categories c on p.categoryid = c.parentid
)
select *
from cte order by levels, categoryId

/*
levels parentid categoryid title
-- -- -- -
1 0 1 aaa
2 1 100 bbb
2 1 101 ccc
2 1 102 ddd
3 102 200 xxx
3 102 201 yyy
3 101 210 ppp
4 201 300 zzz
5 300 400 qqq
*/

However, now that I think about it, I think you are asking a different question; hold on and I will get a second example.

|||

What do you mean "the query result should be generated level by level." There is never any guarantee what the result will look like unless you order it. What order do you want to achieve? Based on your generated level by level question, I would guess:

...

select *

from cte

order by levels

|||

My knee-jerk reaction to the question was the same as Louis' reaction. What I often find is that this question is intended to ask how do you display the data in tree order. That is a very different proposition. Is this more like what you are looking for:


;with cte(levels, parentid, categoryid, tree, title) as (
select 1,
parentid,
categoryid,
cast(str(categoryId, 11) as varchar(120)),
title
from @.categories
where parentid = 0
union all
select p.levels+1,
c.parentid,
c.categoryid,
cast(tree + '/' + str(c.categoryId, 11) as varchar(120)),
c.title
from cte p join @.categories c on p.categoryid = c.parentid
)
select levels,
parentId,
categoryId,
title,
replace(tree, ' ', '') as tree
from cte order by tree

/*
levels parentId categoryId title tree
-- -- -- - -
1 0 1 aaa 1
2 1 100 bbb 1/100
2 1 101 ccc 1/101
3 101 210 ppp 1/101/210
2 1 102 ddd 1/102
3 102 200 xxx 1/102/200
3 102 201 yyy 1/102/201
4 201 300 zzz 1/102/201/300
5 300 400 qqq 1/102/201/300/400
*/

|||

Thanks Kent/Louis,

Please refer to this thread: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=473895&SiteID=17.
Euan's codes work perfect for a small table, but mine is pretty large, I want the CTE returning me maximum 20 levels of result, I did use [OPTION (MAXRECURSION 20)] in my query, but because the statement will terminate first when it reaches maximum levels, in this case, ORDER BY won't work.

My result set looks like this:

Levels Pathid Paths

1 96 40885>96
1 98 40885>98
....
1 60532 40885>60532
2 193 40885>60532>193
2 194 40885>60532>194
....
2 60524 40885>60532>60524
3 698 40885>60532>60524>698
3 915 40885>60532>60524>915
...
3 60527 40885>60532>60524>60527
4 3068 40885>60532>60524>60527>3068
4 4022 40885>60532>60524>60527>4022
...
4 60530 40885>60532>60524>60527>60530
5 10760 40885>60532>60524>60527>60530>10760
5 20365 40885>60532>60524>60527>60530>20365
...

please note, above pattern is very interesting, what I really want here is: I'd like to have the next level results are all from previous level, NOT just last record.

Thanks

|||

Did Kent's query answer you with the path? It seems to be the same (other than the fact that you are not returning the root node). A query like that was going to be my next suggeston.

Instead of MAXRECURSION, you can also limit the level value in the where clause to make it stop too:

;with cte(levels, parentid, categoryid, tree, title) as (
select 1,
parentid,
categoryid,
cast(str(categoryId, 11) as varchar(120)),
title
from @.categories
where parentid = 0
union all
select p.levels+1,
c.parentid,
c.categoryid,
cast(tree + '/' + str(c.categoryId, 11) as varchar(120)),
c.title
from cte p join @.categories c on p.categoryid = c.parentid
where p.levels < 2 --this will get level one and two only (because p.levels + 1)
)
select levels,
parentId,
categoryId,
title,
replace(tree, ' ', '') as tree
from cte
order by tree

levels parentId categoryId title tree

-- -- -- - --

1 0 1 aaa 1

2 1 100 bbb 1/100

2 1 101 ccc 1/101

2 1 102 ddd 1/102

|||

Thanks Louis , that works

Bill