Hi to all,
there is an anoying bug in the Query Analyser (german) version 8.00.760:
In the graphical execution plan costs, percentage, ... are wrong!
If I use set showplan_all on, I will get the right values.
I guess it might be the german decimal character!
If I change the country settings to english(usa) everything ist ok.
Couldn't find this on the msdn side.
Is there any bugfix.
markusi think this applies to all europeans versions that
use "," in place of the english "."
it is possible the numerical values are generated by sql
server, and the graphical display by QA, which may be
handling the european convention
-sorry don't know of fix, i mostly work with US settings
>--Original Message--
>Hi to all,
>there is an anoying bug in the Query Analyser (german)
version 8.00.760:
>In the graphical execution plan costs, percentage, ...
are wrong!
>If I use set showplan_all on, I will get the right values.
>I guess it might be the german decimal character!
>If I change the country settings to english(usa)
everything ist ok.
>Couldn't find this on the msdn side.
>Is there any bugfix.
>markus
>
>.
>|||I reported this as a bug back in April but I don't believe there is a fix
available.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Markus Wimplinger" <markus.wimplinger@.cds.co.at> wrote in message
news:%23jZ5F6JmDHA.2488@.TK2MSFTNGP12.phx.gbl...
Hi to all,
there is an anoying bug in the Query Analyser (german) version 8.00.760:
In the graphical execution plan costs, percentage, ... are wrong!
If I use set showplan_all on, I will get the right values.
I guess it might be the german decimal character!
If I change the country settings to english(usa) everything ist ok.
Couldn't find this on the msdn side.
Is there any bugfix.
markus
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Tuesday, March 20, 2012
Monday, March 19, 2012
BUG or not?
I have a case when the same query returns two different results, depending
on the generated exec plan. I managed to reproduce it on different servers
and databases.
Heres the code, the trigger should generate new number max(Broj)+1 but the
result here is 1 because join in the selec max() part returns empty result
set.
If you substitute part in the join on n.prefix = i.prefix with n.prefix =
convert(varchar(10), i.prefix) then the trigger works.
Notice that the convert function doesnt actually do anything, its there as
an example, feel free to use any method to force different exec plan.
if exists(select * from sysobjects where xtype='U' and name='tbl')
drop table tbl
go
create table tbl (
[id] int identity(1,1) not null primary key clustered,
prefix varchar(10) not null,
broj int null
)
go
insert tbl (prefix, broj) values( '2006/01-', 54)
insert tbl (prefix, broj) values( '2006/01-', 53)
insert tbl (prefix, broj) values( '2006/01-', 52)
insert tbl (prefix, broj) values( '2006/01-', 51)
insert tbl (prefix, broj) values( '2006/01-', 50)
insert tbl (prefix, broj) values( '2006/01-', 49)
insert tbl (prefix, broj) values( '2006/01-', 48)
insert tbl (prefix, broj) values( '2006/01-', 47)
insert tbl (prefix, broj) values( '2006/01-', 46)
insert tbl (prefix, broj) values( '2006/01-', 45)
insert tbl (prefix, broj) values( '2006/01-', 44)
insert tbl (prefix, broj) values( '2006/01-', 43)
insert tbl (prefix, broj) values( '2006/01-', 42)
insert tbl (prefix, broj) values( '2006/01-', 41)
insert tbl (prefix, broj) values( '2006/01-', 40)
insert tbl (prefix, broj) values( '2006/01-', 39)
insert tbl (prefix, broj) values( '2006/01-', 38)
insert tbl (prefix, broj) values( '2006/01-', 37)
insert tbl (prefix, broj) values( '2006/01-', 36)
go
if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
drop trigger tr_tbl_ins
go
create trigger tr_tbl_ins on tbl
for insert
as
update tbl
set
broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
n.prefix = i.prefix),0)+1
where
[id] = (select [id] from inserted)
go
insert into tbl (prefix)
values ('2006/01-')
select * from tbl order by broj
-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
returns NULL valueForgot to add, its for SQL Server 2005, SQL Server 2000 works as
expected...
Marko
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uqJ6IuJVGHA.736@.TK2MSFTNGP12.phx.gbl...
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
>
> if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
> go
> create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
> go
> insert tbl (prefix, broj) values( '2006/01-', 54)
> insert tbl (prefix, broj) values( '2006/01-', 53)
> insert tbl (prefix, broj) values( '2006/01-', 52)
> insert tbl (prefix, broj) values( '2006/01-', 51)
> insert tbl (prefix, broj) values( '2006/01-', 50)
> insert tbl (prefix, broj) values( '2006/01-', 49)
> insert tbl (prefix, broj) values( '2006/01-', 48)
> insert tbl (prefix, broj) values( '2006/01-', 47)
> insert tbl (prefix, broj) values( '2006/01-', 46)
> insert tbl (prefix, broj) values( '2006/01-', 45)
> insert tbl (prefix, broj) values( '2006/01-', 44)
> insert tbl (prefix, broj) values( '2006/01-', 43)
> insert tbl (prefix, broj) values( '2006/01-', 42)
> insert tbl (prefix, broj) values( '2006/01-', 41)
> insert tbl (prefix, broj) values( '2006/01-', 40)
> insert tbl (prefix, broj) values( '2006/01-', 39)
> insert tbl (prefix, broj) values( '2006/01-', 38)
> insert tbl (prefix, broj) values( '2006/01-', 37)
> insert tbl (prefix, broj) values( '2006/01-', 36)
> go
> if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
> drop trigger tr_tbl_ins
> go
> create trigger tr_tbl_ins on tbl
> for insert
> as
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
> n.prefix = i.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> go
>
> insert into tbl (prefix)
> values ('2006/01-')
> select * from tbl order by broj
> -- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
> returns NULL value
>|||It sure looks like a bug to me.
The UPDATE in the trigger can be simplified somewhat. There is no
need to include another instance of tbl in the subquery, it can be
written as a corellated subquery. This version did not have the
problem:
update tbl
set
broj = coalesce((select max(n.broj) from tbl n
where n.prefix = tbl.prefix),0)+1
where
[id] = (select [id] from inserted)
Of course you must already realize that the trigger will provide
duplicate values if more than one row is INSERTED at a time. This is
considered a vulnerability, and I think the trigger should have a test
that enforces that there is only one row in INSERTED.
Roy Harvey
Beacon Falls, CT
On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
>Heres the code, the trigger should generate new number max(Broj)+1 but the
>result here is 1 because join in the selec max() part returns empty result
>set.
>If you substitute part in the join on n.prefix = i.prefix with n.prefix =
>convert(varchar(10), i.prefix) then the trigger works.
>Notice that the convert function doesnt actually do anything, its there as
>an example, feel free to use any method to force different exec plan.
>
>if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
>go
>create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
>go
>insert tbl (prefix, broj) values( '2006/01-', 54)
>insert tbl (prefix, broj) values( '2006/01-', 53)
>insert tbl (prefix, broj) values( '2006/01-', 52)
>insert tbl (prefix, broj) values( '2006/01-', 51)
>insert tbl (prefix, broj) values( '2006/01-', 50)
>insert tbl (prefix, broj) values( '2006/01-', 49)
>insert tbl (prefix, broj) values( '2006/01-', 48)
>insert tbl (prefix, broj) values( '2006/01-', 47)
>insert tbl (prefix, broj) values( '2006/01-', 46)
>insert tbl (prefix, broj) values( '2006/01-', 45)
>insert tbl (prefix, broj) values( '2006/01-', 44)
>insert tbl (prefix, broj) values( '2006/01-', 43)
>insert tbl (prefix, broj) values( '2006/01-', 42)
>insert tbl (prefix, broj) values( '2006/01-', 41)
>insert tbl (prefix, broj) values( '2006/01-', 40)
>insert tbl (prefix, broj) values( '2006/01-', 39)
>insert tbl (prefix, broj) values( '2006/01-', 38)
>insert tbl (prefix, broj) values( '2006/01-', 37)
>insert tbl (prefix, broj) values( '2006/01-', 36)
>go
>if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
>drop trigger tr_tbl_ins
>go
>create trigger tr_tbl_ins on tbl
>for insert
>as
>update tbl
>set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
>n.prefix = i.prefix),0)+1
>where
> [id] = (select [id] from inserted)
>go
>
>insert into tbl (prefix)
>values ('2006/01-')
>select * from tbl order by broj
>-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
>returns NULL value
>|||This is not the real code, it is simplified as musch as possible to show a
point. And, yes, there are ways to fix this but thats not the point. Now,
where should I report this?
MC
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
> It sure looks like a bug to me.
> The UPDATE in the trigger can be simplified somewhat. There is no
> need to include another instance of tbl in the subquery, it can be
> written as a corellated subquery. This version did not have the
> problem:
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n
> where n.prefix = tbl.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> Of course you must already realize that the trigger will provide
> duplicate values if more than one row is INSERTED at a time. This is
> considered a vulnerability, and I think the trigger should have a test
> that enforces that there is only one row in INSERTED.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
> wrote:
>|||I could see that it was a simplified example.
The way I always did this was to open a case with Microsoft. This
cost some $$$ up front, but it was always refunded in the long run
when it proved to be a bug. Haven't had to do that in some years
though.
Roy
On Fri, 31 Mar 2006 14:21:39 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:
>This is not the real code, it is simplified as musch as possible to show a
>point. And, yes, there are ways to fix this but thats not the point. Now,
>where should I report this?
>
>MC|||> Now,
> where should I report this?
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:uiyBBvLVGHA.2156@.tk2msftngp13.phx.gb
l...
> This is not the real code, it is simplified as musch as possible to show a
> point. And, yes, there are ways to fix this but thats not the point. Now,
> where should I report this?
>
> MC
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
>|||Maybe you should report a bug:
http://lab.msdn.microsoft.com/produ...ck/Default.aspx
ML
http://milambda.blogspot.com/|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I have a case when the same query returns two different results, depending
> on the generated exec plan. I managed to reproduce it on different servers
> and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
Definitely a bug, and you should report it on
http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
exists there as well.
The bug appears to related to the virtual inserted table. I rewrote
the MAX query in a way which is more palatable to me:
select max(n.broj)
from tbl n
where exists(select * from inserted i where n.prefix = i.prefix)
However, it still returns NULL.
If I do:
SELECT * INTO #inserted FROM tbl
and then use #inserted in the trigger, I get the correct result.
Note: while the use if SELECT INTO is palatable, since you don't
have to copy the table definition in the trigger, my recommendation
is to use a table variable instead (which only should the columns
actually referenced the trigger). I did use SELECT INTO for a while,
but I found that it gave us performance problems.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've reported the bug, however I cant seem to check the status. It seems
that part of the site is down at the moment. If you want to look at it, i
created it under 'mculo'
MC
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9797F2B151D23Yazorman@.127.0.0.1...
> MC (marko_culo#@.#yahoo#.#com#) writes:
> Definitely a bug, and you should report it on
> http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
> done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
> exists there as well.
> The bug appears to related to the virtual inserted table. I rewrote
> the MAX query in a way which is more palatable to me:
> select max(n.broj)
> from tbl n
> where exists(select * from inserted i where n.prefix = i.prefix)
> However, it still returns NULL.
> If I do:
> SELECT * INTO #inserted FROM tbl
> and then use #inserted in the trigger, I get the correct result.
> Note: while the use if SELECT INTO is palatable, since you don't
> have to copy the table definition in the trigger, my recommendation
> is to use a table variable instead (which only should the columns
> actually referenced the trigger). I did use SELECT INTO for a while,
> but I found that it gave us performance problems.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I've reported the bug, however I cant seem to check the status. It seems
> that part of the site is down at the moment. If you want to look at it, i
> created it under 'mculo'
Yeah, I also had problems with the site yesterday. I guess it will come back
later today. Keep in mind that it is still only six o'clock in the morning
in Redmond.
But I did see your bug Saturday. As you had failed to attach(*) the repro,
I augmented the bug with the information from the newsgroup.
(*) It's very easy to go wrong, due to the poor inteface on the site. First
browse the file path, and then press Add Attachment. Took me some time to
figure that out the first time.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
on the generated exec plan. I managed to reproduce it on different servers
and databases.
Heres the code, the trigger should generate new number max(Broj)+1 but the
result here is 1 because join in the selec max() part returns empty result
set.
If you substitute part in the join on n.prefix = i.prefix with n.prefix =
convert(varchar(10), i.prefix) then the trigger works.
Notice that the convert function doesnt actually do anything, its there as
an example, feel free to use any method to force different exec plan.
if exists(select * from sysobjects where xtype='U' and name='tbl')
drop table tbl
go
create table tbl (
[id] int identity(1,1) not null primary key clustered,
prefix varchar(10) not null,
broj int null
)
go
insert tbl (prefix, broj) values( '2006/01-', 54)
insert tbl (prefix, broj) values( '2006/01-', 53)
insert tbl (prefix, broj) values( '2006/01-', 52)
insert tbl (prefix, broj) values( '2006/01-', 51)
insert tbl (prefix, broj) values( '2006/01-', 50)
insert tbl (prefix, broj) values( '2006/01-', 49)
insert tbl (prefix, broj) values( '2006/01-', 48)
insert tbl (prefix, broj) values( '2006/01-', 47)
insert tbl (prefix, broj) values( '2006/01-', 46)
insert tbl (prefix, broj) values( '2006/01-', 45)
insert tbl (prefix, broj) values( '2006/01-', 44)
insert tbl (prefix, broj) values( '2006/01-', 43)
insert tbl (prefix, broj) values( '2006/01-', 42)
insert tbl (prefix, broj) values( '2006/01-', 41)
insert tbl (prefix, broj) values( '2006/01-', 40)
insert tbl (prefix, broj) values( '2006/01-', 39)
insert tbl (prefix, broj) values( '2006/01-', 38)
insert tbl (prefix, broj) values( '2006/01-', 37)
insert tbl (prefix, broj) values( '2006/01-', 36)
go
if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
drop trigger tr_tbl_ins
go
create trigger tr_tbl_ins on tbl
for insert
as
update tbl
set
broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
n.prefix = i.prefix),0)+1
where
[id] = (select [id] from inserted)
go
insert into tbl (prefix)
values ('2006/01-')
select * from tbl order by broj
-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
returns NULL valueForgot to add, its for SQL Server 2005, SQL Server 2000 works as
expected...
Marko
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message
news:uqJ6IuJVGHA.736@.TK2MSFTNGP12.phx.gbl...
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
>
> if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
> go
> create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
> go
> insert tbl (prefix, broj) values( '2006/01-', 54)
> insert tbl (prefix, broj) values( '2006/01-', 53)
> insert tbl (prefix, broj) values( '2006/01-', 52)
> insert tbl (prefix, broj) values( '2006/01-', 51)
> insert tbl (prefix, broj) values( '2006/01-', 50)
> insert tbl (prefix, broj) values( '2006/01-', 49)
> insert tbl (prefix, broj) values( '2006/01-', 48)
> insert tbl (prefix, broj) values( '2006/01-', 47)
> insert tbl (prefix, broj) values( '2006/01-', 46)
> insert tbl (prefix, broj) values( '2006/01-', 45)
> insert tbl (prefix, broj) values( '2006/01-', 44)
> insert tbl (prefix, broj) values( '2006/01-', 43)
> insert tbl (prefix, broj) values( '2006/01-', 42)
> insert tbl (prefix, broj) values( '2006/01-', 41)
> insert tbl (prefix, broj) values( '2006/01-', 40)
> insert tbl (prefix, broj) values( '2006/01-', 39)
> insert tbl (prefix, broj) values( '2006/01-', 38)
> insert tbl (prefix, broj) values( '2006/01-', 37)
> insert tbl (prefix, broj) values( '2006/01-', 36)
> go
> if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
> drop trigger tr_tbl_ins
> go
> create trigger tr_tbl_ins on tbl
> for insert
> as
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
> n.prefix = i.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> go
>
> insert into tbl (prefix)
> values ('2006/01-')
> select * from tbl order by broj
> -- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
> returns NULL value
>|||It sure looks like a bug to me.
The UPDATE in the trigger can be simplified somewhat. There is no
need to include another instance of tbl in the subquery, it can be
written as a corellated subquery. This version did not have the
problem:
update tbl
set
broj = coalesce((select max(n.broj) from tbl n
where n.prefix = tbl.prefix),0)+1
where
[id] = (select [id] from inserted)
Of course you must already realize that the trigger will provide
duplicate values if more than one row is INSERTED at a time. This is
considered a vulnerability, and I think the trigger should have a test
that enforces that there is only one row in INSERTED.
Roy Harvey
Beacon Falls, CT
On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:
>I have a case when the same query returns two different results, depending
>on the generated exec plan. I managed to reproduce it on different servers
>and databases.
>Heres the code, the trigger should generate new number max(Broj)+1 but the
>result here is 1 because join in the selec max() part returns empty result
>set.
>If you substitute part in the join on n.prefix = i.prefix with n.prefix =
>convert(varchar(10), i.prefix) then the trigger works.
>Notice that the convert function doesnt actually do anything, its there as
>an example, feel free to use any method to force different exec plan.
>
>if exists(select * from sysobjects where xtype='U' and name='tbl')
> drop table tbl
>go
>create table tbl (
> [id] int identity(1,1) not null primary key clustered,
> prefix varchar(10) not null,
> broj int null
> )
>go
>insert tbl (prefix, broj) values( '2006/01-', 54)
>insert tbl (prefix, broj) values( '2006/01-', 53)
>insert tbl (prefix, broj) values( '2006/01-', 52)
>insert tbl (prefix, broj) values( '2006/01-', 51)
>insert tbl (prefix, broj) values( '2006/01-', 50)
>insert tbl (prefix, broj) values( '2006/01-', 49)
>insert tbl (prefix, broj) values( '2006/01-', 48)
>insert tbl (prefix, broj) values( '2006/01-', 47)
>insert tbl (prefix, broj) values( '2006/01-', 46)
>insert tbl (prefix, broj) values( '2006/01-', 45)
>insert tbl (prefix, broj) values( '2006/01-', 44)
>insert tbl (prefix, broj) values( '2006/01-', 43)
>insert tbl (prefix, broj) values( '2006/01-', 42)
>insert tbl (prefix, broj) values( '2006/01-', 41)
>insert tbl (prefix, broj) values( '2006/01-', 40)
>insert tbl (prefix, broj) values( '2006/01-', 39)
>insert tbl (prefix, broj) values( '2006/01-', 38)
>insert tbl (prefix, broj) values( '2006/01-', 37)
>insert tbl (prefix, broj) values( '2006/01-', 36)
>go
>if exists(select * from sysobjects where xtype='TR' and name='tr_tbl_ins')
>drop trigger tr_tbl_ins
>go
>create trigger tr_tbl_ins on tbl
>for insert
>as
>update tbl
>set
> broj = coalesce((select max(n.broj) from tbl n inner join inserted i on
>n.prefix = i.prefix),0)+1
>where
> [id] = (select [id] from inserted)
>go
>
>insert into tbl (prefix)
>values ('2006/01-')
>select * from tbl order by broj
>-- New broj should be max(Broj)+1 but its 1 since the select max(n.broj)
>returns NULL value
>|||This is not the real code, it is simplified as musch as possible to show a
point. And, yes, there are ways to fix this but thats not the point. Now,
where should I report this?
MC
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
> It sure looks like a bug to me.
> The UPDATE in the trigger can be simplified somewhat. There is no
> need to include another instance of tbl in the subquery, it can be
> written as a corellated subquery. This version did not have the
> problem:
> update tbl
> set
> broj = coalesce((select max(n.broj) from tbl n
> where n.prefix = tbl.prefix),0)+1
> where
> [id] = (select [id] from inserted)
> Of course you must already realize that the trigger will provide
> duplicate values if more than one row is INSERTED at a time. This is
> considered a vulnerability, and I think the trigger should have a test
> that enforces that there is only one row in INSERTED.
> Roy Harvey
> Beacon Falls, CT
> On Fri, 31 Mar 2006 10:31:01 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
> wrote:
>|||I could see that it was a simplified example.
The way I always did this was to open a case with Microsoft. This
cost some $$$ up front, but it was always refunded in the long run
when it proved to be a bug. Haven't had to do that in some years
though.
Roy
On Fri, 31 Mar 2006 14:21:39 +0200, "MC" <marko_culo#@.#yahoo#.#com#>
wrote:
>This is not the real code, it is simplified as musch as possible to show a
>point. And, yes, there are ways to fix this but thats not the point. Now,
>where should I report this?
>
>MC|||> Now,
> where should I report this?
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MC" <marko_culo#@.#yahoo#.#com#> wrote in message news:uiyBBvLVGHA.2156@.tk2msftngp13.phx.gb
l...
> This is not the real code, it is simplified as musch as possible to show a
> point. And, yes, there are ways to fix this but thats not the point. Now,
> where should I report this?
>
> MC
>
> "Roy Harvey" <roy_harvey@.snet.net> wrote in message
> news:db6q22529ujosandv3442g81j72e4i2j6q@.
4ax.com...
>|||Maybe you should report a bug:
http://lab.msdn.microsoft.com/produ...ck/Default.aspx
ML
http://milambda.blogspot.com/|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I have a case when the same query returns two different results, depending
> on the generated exec plan. I managed to reproduce it on different servers
> and databases.
> Heres the code, the trigger should generate new number max(Broj)+1 but the
> result here is 1 because join in the selec max() part returns empty result
> set.
> If you substitute part in the join on n.prefix = i.prefix with n.prefix =
> convert(varchar(10), i.prefix) then the trigger works.
> Notice that the convert function doesnt actually do anything, its there as
> an example, feel free to use any method to force different exec plan.
Definitely a bug, and you should report it on
http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
exists there as well.
The bug appears to related to the virtual inserted table. I rewrote
the MAX query in a way which is more palatable to me:
select max(n.broj)
from tbl n
where exists(select * from inserted i where n.prefix = i.prefix)
However, it still returns NULL.
If I do:
SELECT * INTO #inserted FROM tbl
and then use #inserted in the trigger, I get the correct result.
Note: while the use if SELECT INTO is palatable, since you don't
have to copy the table definition in the trigger, my recommendation
is to use a table variable instead (which only should the columns
actually referenced the trigger). I did use SELECT INTO for a while,
but I found that it gave us performance problems.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I've reported the bug, however I cant seem to check the status. It seems
that part of the site is down at the moment. If you want to look at it, i
created it under 'mculo'
MC
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9797F2B151D23Yazorman@.127.0.0.1...
> MC (marko_culo#@.#yahoo#.#com#) writes:
> Definitely a bug, and you should report it on
> http://lab.msdn.microsoft.com/ProductFeedback/ if you haven't already
> done so. I tested it on the CTP of SP1 for SQL 2005, and the bug
> exists there as well.
> The bug appears to related to the virtual inserted table. I rewrote
> the MAX query in a way which is more palatable to me:
> select max(n.broj)
> from tbl n
> where exists(select * from inserted i where n.prefix = i.prefix)
> However, it still returns NULL.
> If I do:
> SELECT * INTO #inserted FROM tbl
> and then use #inserted in the trigger, I get the correct result.
> Note: while the use if SELECT INTO is palatable, since you don't
> have to copy the table definition in the trigger, my recommendation
> is to use a table variable instead (which only should the columns
> actually referenced the trigger). I did use SELECT INTO for a while,
> but I found that it gave us performance problems.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||MC (marko_culo#@.#yahoo#.#com#) writes:
> I've reported the bug, however I cant seem to check the status. It seems
> that part of the site is down at the moment. If you want to look at it, i
> created it under 'mculo'
Yeah, I also had problems with the site yesterday. I guess it will come back
later today. Keep in mind that it is still only six o'clock in the morning
in Redmond.
But I did see your bug Saturday. As you had failed to attach(*) the repro,
I augmented the bug with the information from the newsgroup.
(*) It's very easy to go wrong, due to the poor inteface on the site. First
browse the file path, and then press Add Attachment. Took me some time to
figure that out the first time.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Thursday, March 8, 2012
Bug in Ent Mgr Maintenance Plan Wizard if MSDE vs SQL
I have seen this "bug" many times personally, but cannot seem to find any posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT include licence for Enterprize Manager (EM)... but any technician who does have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access Licence) does have Enterprize Manag
er and can connect and manage MSDE and SQL engines/servers equally well.
Consider http://support.microsoft.com/default...22&Product=sql Here we see substantiation that one "can" use EM and Maintenance Plans on MSDE, albeit that you had better be the latest Service Pack since the sqlmaint feature was over
looked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for Database Backup, Transaction Backup (and Log text files). This is important because unless you can also choose to do so, one will end up filling the hard disk with far too many SQL
dumps.
If connected to an SQL server... the option works fine. If connected to an MSDE, the option fails to provie the drop-down for days/weeks and the resulting job created has bugs in the command line options of the job step which one must then go and manuall
y tweak to be correct (never report a bug without a workaround eh? ;-P)
John,
As far as im aware this happens because the maintenance wizard
(sqlmaint.exe) is looking for a backupdirectory string valuein the MSDE
instance registry location.
(I would maybe do this on a test machine first if possible or make sure you
have backups)
So firstly make sure the following backup folder path exists , if not create
it :
c:\program files\microsoft sql server\<MSDE instance>\Backup.
Then in regedit:
HKLM\Software\Microsoft\Microsoft SQL Server\<MSDE instance>\MSSQLserver
there should be a BackupDirectory string value , the value data should be
the physical path to the backup folder in our case c:\program
files\microsoft sql server\<MSDE instance>\Backup.
HTH
Dylan
"John D. Sullivan" <John D. Sullivan@.discussions.microsoft.com> wrote in
message news:835E25B5-6EE5-4533-A1D5-43D3873BC0B1@.microsoft.com...
> I have seen this "bug" many times personally, but cannot seem to find any
posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
> Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
|||Thanks for the thought. No that's not what I mean (see my additional post
in next thread). About the backup folder here is what I did discover the
hard way..
When creating the Maintenance Plan...if one chooses "use the default backup
directory" then it seems to work, and the (first) backup will create the
folder path even if it did not yet exist. However if you specify a "use
this directory" to backup to... then I have found that folder you specify
had better exist already because the backup job will fail rather than create
it. So there's a little idiosyncracy for ya. (I have not experienced the
issue you mention, but perhaps that is an issue if the SQL is installed as a
"named" instance of SQL instead of the default/single instance of SQL? in
which case thank you for the additional tweaks)
John D. Sullivan
ABELSoft Corporation
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:OQXmdsjaEHA.3596@.tk2msftngp13.phx.gbl...
> John,
> As far as im aware this happens because the maintenance wizard
> (sqlmaint.exe) is looking for a backupdirectory string valuein the MSDE
> instance registry location.
> (I would maybe do this on a test machine first if possible or make sure
you
> have backups)
> So firstly make sure the following backup folder path exists , if not
create[vbcol=seagreen]
> it :
> c:\program files\microsoft sql server\<MSDE instance>\Backup.
> Then in regedit:
> HKLM\Software\Microsoft\Microsoft SQL Server\<MSDE instance>\MSSQLserver
> there should be a BackupDirectory string value , the value data should be
> the physical path to the backup folder in our case c:\program
> files\microsoft sql server\<MSDE instance>\Backup.
> HTH
> Dylan
>
>
> "John D. Sullivan" <John D. Sullivan@.discussions.microsoft.com> wrote in
> message news:835E25B5-6EE5-4533-A1D5-43D3873BC0B1@.microsoft.com...
any[vbcol=seagreen]
> posting on the Internet about it. I have many customers using MSDE
Enterprise[vbcol=seagreen]
> Manager when connecting to MSDE.
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> an MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
>
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT include licence for Enterprize Manager (EM)... but any technician who does have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access Licence) does have Enterprize Manag
er and can connect and manage MSDE and SQL engines/servers equally well.
Consider http://support.microsoft.com/default...22&Product=sql Here we see substantiation that one "can" use EM and Maintenance Plans on MSDE, albeit that you had better be the latest Service Pack since the sqlmaint feature was over
looked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for Database Backup, Transaction Backup (and Log text files). This is important because unless you can also choose to do so, one will end up filling the hard disk with far too many SQL
dumps.
If connected to an SQL server... the option works fine. If connected to an MSDE, the option fails to provie the drop-down for days/weeks and the resulting job created has bugs in the command line options of the job step which one must then go and manuall
y tweak to be correct (never report a bug without a workaround eh? ;-P)
John,
As far as im aware this happens because the maintenance wizard
(sqlmaint.exe) is looking for a backupdirectory string valuein the MSDE
instance registry location.
(I would maybe do this on a test machine first if possible or make sure you
have backups)
So firstly make sure the following backup folder path exists , if not create
it :
c:\program files\microsoft sql server\<MSDE instance>\Backup.
Then in regedit:
HKLM\Software\Microsoft\Microsoft SQL Server\<MSDE instance>\MSSQLserver
there should be a BackupDirectory string value , the value data should be
the physical path to the backup folder in our case c:\program
files\microsoft sql server\<MSDE instance>\Backup.
HTH
Dylan
"John D. Sullivan" <John D. Sullivan@.discussions.microsoft.com> wrote in
message news:835E25B5-6EE5-4533-A1D5-43D3873BC0B1@.microsoft.com...
> I have seen this "bug" many times personally, but cannot seem to find any
posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
> Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
|||Thanks for the thought. No that's not what I mean (see my additional post
in next thread). About the backup folder here is what I did discover the
hard way..
When creating the Maintenance Plan...if one chooses "use the default backup
directory" then it seems to work, and the (first) backup will create the
folder path even if it did not yet exist. However if you specify a "use
this directory" to backup to... then I have found that folder you specify
had better exist already because the backup job will fail rather than create
it. So there's a little idiosyncracy for ya. (I have not experienced the
issue you mention, but perhaps that is an issue if the SQL is installed as a
"named" instance of SQL instead of the default/single instance of SQL? in
which case thank you for the additional tweaks)
John D. Sullivan
ABELSoft Corporation
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:OQXmdsjaEHA.3596@.tk2msftngp13.phx.gbl...
> John,
> As far as im aware this happens because the maintenance wizard
> (sqlmaint.exe) is looking for a backupdirectory string valuein the MSDE
> instance registry location.
> (I would maybe do this on a test machine first if possible or make sure
you
> have backups)
> So firstly make sure the following backup folder path exists , if not
create[vbcol=seagreen]
> it :
> c:\program files\microsoft sql server\<MSDE instance>\Backup.
> Then in regedit:
> HKLM\Software\Microsoft\Microsoft SQL Server\<MSDE instance>\MSSQLserver
> there should be a BackupDirectory string value , the value data should be
> the physical path to the backup folder in our case c:\program
> files\microsoft sql server\<MSDE instance>\Backup.
> HTH
> Dylan
>
>
> "John D. Sullivan" <John D. Sullivan@.discussions.microsoft.com> wrote in
> message news:835E25B5-6EE5-4533-A1D5-43D3873BC0B1@.microsoft.com...
any[vbcol=seagreen]
> posting on the Internet about it. I have many customers using MSDE
Enterprise[vbcol=seagreen]
> Manager when connecting to MSDE.
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> an MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
>
Bug in Ent Mgr Maintenance Plan Wizard if MSDE vs SQL
I have seen this "bug" many times personally, but cannot seem to find any posting on the Internet about it. I have many customers using MSDE
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT include licence for Enterprize Manager (EM)... but any technician who does have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access Licence) does have Enterprize Manag
er and can connect and manage MSDE and SQL engines/servers equally well.
Consider http://support.microsoft.com/default...22&Product=sql Here we see substantiation that one "can" use EM and Maintenance Plans on MSDE, albeit that you had better be the latest Service Pack since the sqlmaint feature was over
looked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for Database Backup, Transaction Backup (and Log text files). This is important because unless you can also choose to do so, one will end up filling the hard disk with far too many SQL
dumps.
If connected to an SQL server... the option works fine. If connected to an MSDE, the option fails to provie the drop-down for days/weeks and the resulting job created has bugs in the command line options of the job step which one must then go and manuall
y tweak to be correct (never report a bug without a workaround eh? ;-P)
John,
As far as im aware this happens because the maintenance wizard
(sqlmaint.exe) is looking for a backupdirectory string valuein the MSDE
instance registry location.
(I would maybe do this on a test machine first if possible or make sure you
have backups)
So firstly make sure the following backup folder path exists , if not create
it :
c:\program files\microsoft sql server\<MSDE instance>\Backup.
Then in regedit:
HKLM\Software\Microsoft\Microsoft SQL Server\<MSDE instance>\MSSQLserver
there should be a BackupDirectory string value , the value data should be
the physical path to the backup folder in our case c:\program
files\microsoft sql server\<MSDE instance>\Backup.
HTH
Dylan
"John D. Sullivan" <John D. Sullivan@.discussions.microsoft.com> wrote in
message news:835E25B5-6EE5-4533-A1D5-43D3873BC0B1@.microsoft.com...
> I have seen this "bug" many times personally, but cannot seem to find any
posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
> Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
|||Thanks for the thought. No that's not what I mean (see my additional post
in next thread). About the backup folder here is what I did discover the
hard way..
When creating the Maintenance Plan...if one chooses "use the default backup
directory" then it seems to work, and the (first) backup will create the
folder path even if it did not yet exist. However if you specify a "use
this directory" to backup to... then I have found that folder you specify
had better exist already because the backup job will fail rather than create
it. So there's a little idiosyncracy for ya. (I have not experienced the
issue you mention, but perhaps that is an issue if the SQL is installed as a
"named" instance of SQL instead of the default/single instance of SQL? in
which case thank you for the additional tweaks)
John D. Sullivan
ABELSoft Corporation
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:OQXmdsjaEHA.3596@.tk2msftngp13.phx.gbl...
> John,
> As far as im aware this happens because the maintenance wizard
> (sqlmaint.exe) is looking for a backupdirectory string valuein the MSDE
> instance registry location.
> (I would maybe do this on a test machine first if possible or make sure
you
> have backups)
> So firstly make sure the following backup folder path exists , if not
create[vbcol=seagreen]
> it :
> c:\program files\microsoft sql server\<MSDE instance>\Backup.
> Then in regedit:
> HKLM\Software\Microsoft\Microsoft SQL Server\<MSDE instance>\MSSQLserver
> there should be a BackupDirectory string value , the value data should be
> the physical path to the backup folder in our case c:\program
> files\microsoft sql server\<MSDE instance>\Backup.
> HTH
> Dylan
>
>
> "John D. Sullivan" <John D. Sullivan@.discussions.microsoft.com> wrote in
> message news:835E25B5-6EE5-4533-A1D5-43D3873BC0B1@.microsoft.com...
any[vbcol=seagreen]
> posting on the Internet about it. I have many customers using MSDE
Enterprise[vbcol=seagreen]
> Manager when connecting to MSDE.
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> an MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
>
The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise Manager when connecting to MSDE.
Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT include licence for Enterprize Manager (EM)... but any technician who does have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access Licence) does have Enterprize Manag
er and can connect and manage MSDE and SQL engines/servers equally well.
Consider http://support.microsoft.com/default...22&Product=sql Here we see substantiation that one "can" use EM and Maintenance Plans on MSDE, albeit that you had better be the latest Service Pack since the sqlmaint feature was over
looked in the original release to market.
The bug I am reporting is with the "Remove after X Weeks/Days" option for Database Backup, Transaction Backup (and Log text files). This is important because unless you can also choose to do so, one will end up filling the hard disk with far too many SQL
dumps.
If connected to an SQL server... the option works fine. If connected to an MSDE, the option fails to provie the drop-down for days/weeks and the resulting job created has bugs in the command line options of the job step which one must then go and manuall
y tweak to be correct (never report a bug without a workaround eh? ;-P)
John,
As far as im aware this happens because the maintenance wizard
(sqlmaint.exe) is looking for a backupdirectory string valuein the MSDE
instance registry location.
(I would maybe do this on a test machine first if possible or make sure you
have backups)
So firstly make sure the following backup folder path exists , if not create
it :
c:\program files\microsoft sql server\<MSDE instance>\Backup.
Then in regedit:
HKLM\Software\Microsoft\Microsoft SQL Server\<MSDE instance>\MSSQLserver
there should be a BackupDirectory string value , the value data should be
the physical path to the backup folder in our case c:\program
files\microsoft sql server\<MSDE instance>\Backup.
HTH
Dylan
"John D. Sullivan" <John D. Sullivan@.discussions.microsoft.com> wrote in
message news:835E25B5-6EE5-4533-A1D5-43D3873BC0B1@.microsoft.com...
> I have seen this "bug" many times personally, but cannot seem to find any
posting on the Internet about it. I have many customers using MSDE
> The bug is with the Database Maintenance Plans of Microsoft SQL Enterprise
Manager when connecting to MSDE.
> Yes, yes, I realize that MSDE (Microsoft SQL Desktop Edition) does NOT
include licence for Enterprize Manager (EM)... but any technician who does
have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
Licence) does have Enterprize Manager and can connect and manage MSDE and
SQL engines/servers equally well.
> Consider
http://support.microsoft.com/default...22&Product=sql
Here we see substantiation that one "can" use EM and Maintenance Plans on
MSDE, albeit that you had better be the latest Service Pack since the
sqlmaint feature was overlooked in the original release to market.
> The bug I am reporting is with the "Remove after X Weeks/Days" option for
Database Backup, Transaction Backup (and Log text files). This is important
because unless you can also choose to do so, one will end up filling the
hard disk with far too many SQL dumps.
> If connected to an SQL server... the option works fine. If connected to
an MSDE, the option fails to provie the drop-down for days/weeks and the
resulting job created has bugs in the command line options of the job step
which one must then go and manually tweak to be correct (never report a bug
without a workaround eh? ;-P)
|||Thanks for the thought. No that's not what I mean (see my additional post
in next thread). About the backup folder here is what I did discover the
hard way..
When creating the Maintenance Plan...if one chooses "use the default backup
directory" then it seems to work, and the (first) backup will create the
folder path even if it did not yet exist. However if you specify a "use
this directory" to backup to... then I have found that folder you specify
had better exist already because the backup job will fail rather than create
it. So there's a little idiosyncracy for ya. (I have not experienced the
issue you mention, but perhaps that is an issue if the SQL is installed as a
"named" instance of SQL instead of the default/single instance of SQL? in
which case thank you for the additional tweaks)
John D. Sullivan
ABELSoft Corporation
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:OQXmdsjaEHA.3596@.tk2msftngp13.phx.gbl...
> John,
> As far as im aware this happens because the maintenance wizard
> (sqlmaint.exe) is looking for a backupdirectory string valuein the MSDE
> instance registry location.
> (I would maybe do this on a test machine first if possible or make sure
you
> have backups)
> So firstly make sure the following backup folder path exists , if not
create[vbcol=seagreen]
> it :
> c:\program files\microsoft sql server\<MSDE instance>\Backup.
> Then in regedit:
> HKLM\Software\Microsoft\Microsoft SQL Server\<MSDE instance>\MSSQLserver
> there should be a BackupDirectory string value , the value data should be
> the physical path to the backup folder in our case c:\program
> files\microsoft sql server\<MSDE instance>\Backup.
> HTH
> Dylan
>
>
> "John D. Sullivan" <John D. Sullivan@.discussions.microsoft.com> wrote in
> message news:835E25B5-6EE5-4533-A1D5-43D3873BC0B1@.microsoft.com...
any[vbcol=seagreen]
> posting on the Internet about it. I have many customers using MSDE
Enterprise[vbcol=seagreen]
> Manager when connecting to MSDE.
> include licence for Enterprize Manager (EM)... but any technician who does
> have a licenced copy of SQL (Standard, Enterprize etc) CAL (Client Access
> Licence) does have Enterprize Manager and can connect and manage MSDE and
> SQL engines/servers equally well.
> http://support.microsoft.com/default...22&Product=sql
> Here we see substantiation that one "can" use EM and Maintenance Plans on
> MSDE, albeit that you had better be the latest Service Pack since the
> sqlmaint feature was overlooked in the original release to market.
for
> Database Backup, Transaction Backup (and Log text files). This is
important
> because unless you can also choose to do so, one will end up filling the
> hard disk with far too many SQL dumps.
> an MSDE, the option fails to provie the drop-down for days/weeks and the
> resulting job created has bugs in the command line options of the job step
> which one must then go and manually tweak to be correct (never report a
bug
> without a workaround eh? ;-P)
>
Subscribe to:
Posts (Atom)