Showing posts with label script. Show all posts
Showing posts with label script. Show all posts

Friday, March 23, 2012

Hidden parameters + automatic hiding deployment script

Hi,
I have a number of reports which hidden parameters. The problem is
that when
I change the reports, or as the number of reports grows, I'm then
having to
manually go through the report in report manager and remove the prompt
string, making sure that 'Prompt user' is checked to put the
'hidden-ness'
back.
Is there any way I could do this automatically? All the report
parameters I
wish to hide will have consistent names so it would make my life a lot
easier
if I could do this.
Can I do it through the web service - or maybe through scripting?
Any help would be greatly appreciated.
Thanks,
Phil Aldis
PS I posted this before using the MS newsgropu thing but it appears
not to have made it to google's servers so I though more people might
see this.check this script: http://blogs.msdn.com/levs/archive/2004/07/20/189178.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Phil Aldis" <philaldis@.yahoo.com> wrote in message
news:64aba0db.0409200628.95bfa1a@.posting.google.com...
> Hi,
> I have a number of reports which hidden parameters. The problem is
> that when
> I change the reports, or as the number of reports grows, I'm then
> having to
> manually go through the report in report manager and remove the prompt
> string, making sure that 'Prompt user' is checked to put the
> 'hidden-ness'
> back.
> Is there any way I could do this automatically? All the report
> parameters I
> wish to hide will have consistent names so it would make my life a lot
> easier
> if I could do this.
> Can I do it through the web service - or maybe through scripting?
> Any help would be greatly appreciated.
> Thanks,
> Phil Aldis
> PS I posted this before using the MS newsgropu thing but it appears
> not to have made it to google's servers so I though more people might
> see this.

Monday, March 19, 2012

hi

how to run a vb script file in vb.netPlease, move your question to VB forum.

Monday, March 12, 2012

Heterogeneous queries and use of OLEDB providers are not supported

Hi all
I am trying to create linked server on 2005 64bit server for 2000 32 bit.
I ran instcat.sql script as microsoft article sugested.
Still, I am having problems.
I can browse db objects using management studio.
But when i try to run sql like:
select count(*) from botserver.master.dbo.sysobjects
I am getting:
Heterogeneous queries and use of OLEDB providers are not supported in fiber
mode.
I can neither run any stored procs.
My linked server is defined as server type: sql server
Any ideas?
Why are you running in fiber mode in the first place? Unless you have a
very good reason for turning it on you should turn it back off and solve two
problems.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> Hi all
> I am trying to create linked server on 2005 64bit server for 2000 32 bit.
> I ran instcat.sql script as microsoft article sugested.
> Still, I am having problems.
> I can browse db objects using management studio.
> But when i try to run sql like:
> select count(*) from botserver.master.dbo.sysobjects
> I am getting:
> Heterogeneous queries and use of OLEDB providers are not supported in
> fiber
> mode.
> I can neither run any stored procs.
> My linked server is defined as server type: sql server
> Any ideas?
|||Hi Kelly
I have 8 cpu host and content switching is pretty high.
I read that these are indications to use light weight pooling.
If you can please educate me on this point too.
Thank you, Gene.
"Andrew J. Kelly" wrote:

> Why are you running in fiber mode in the first place? Unless you have a
> very good reason for turning it on you should turn it back off and solve two
> problems.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
>
|||Try to use alias name as qualifier instead of full qualifier (complete schema).
Hope this will solve your problem.

Heterogeneous queries and use of OLEDB providers are not supported

Hi all
I am trying to create linked server on 2005 64bit server for 2000 32 bit.
I ran instcat.sql script as microsoft article sugested.
Still, I am having problems.
I can browse db objects using management studio.
But when i try to run sql like:
select count(*) from botserver.master.dbo.sysobjects
I am getting:
Heterogeneous queries and use of OLEDB providers are not supported in fiber
mode.
I can neither run any stored procs.
My linked server is defined as server type: sql server
Any ideas?Why are you running in fiber mode in the first place? Unless you have a
very good reason for turning it on you should turn it back off and solve two
problems.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> Hi all
> I am trying to create linked server on 2005 64bit server for 2000 32 bit.
> I ran instcat.sql script as microsoft article sugested.
> Still, I am having problems.
> I can browse db objects using management studio.
> But when i try to run sql like:
> select count(*) from botserver.master.dbo.sysobjects
> I am getting:
> Heterogeneous queries and use of OLEDB providers are not supported in
> fiber
> mode.
> I can neither run any stored procs.
> My linked server is defined as server type: sql server
> Any ideas?|||Hi Kelly
I have 8 cpu host and content switching is pretty high.
I read that these are indications to use light weight pooling.
If you can please educate me on this point too.
Thank you, Gene.
"Andrew J. Kelly" wrote:
> Why are you running in fiber mode in the first place? Unless you have a
> very good reason for turning it on you should turn it back off and solve two
> problems.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> > Hi all
> > I am trying to create linked server on 2005 64bit server for 2000 32 bit.
> > I ran instcat.sql script as microsoft article sugested.
> > Still, I am having problems.
> > I can browse db objects using management studio.
> > But when i try to run sql like:
> > select count(*) from botserver.master.dbo.sysobjects
> >
> > I am getting:
> > Heterogeneous queries and use of OLEDB providers are not supported in
> > fiber
> > mode.
> >
> > I can neither run any stored procs.
> > My linked server is defined as server type: sql server
> >
> > Any ideas?
>|||Context switching being high is relative but is also usually caused by other
factors that can be addressed to ease this issue. Things like poorly
optimized queries, lack of proper indexes, poorly configured I/O subsystems,
too little memory, excessive blocking etc. Even other apps running on the
same server can cause excessive context switching. Switching to fiber mode
without addressing the root cause will only cause more problems in most
cases. And fiber mode is usually only really effective on systems with more
than 8 CPU's that are running at or near 100% utilization. I suggest you
concentrate on finding the real bottle necks and think seriously about
turning fiber mode off.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:13BBB927-0B8C-4479-8B01-6A9050DA9BF8@.microsoft.com...
> Hi Kelly
> I have 8 cpu host and content switching is pretty high.
> I read that these are indications to use light weight pooling.
> If you can please educate me on this point too.
> Thank you, Gene.
> "Andrew J. Kelly" wrote:
>> Why are you running in fiber mode in the first place? Unless you have a
>> very good reason for turning it on you should turn it back off and solve
>> two
>> problems.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
>> > Hi all
>> > I am trying to create linked server on 2005 64bit server for 2000 32
>> > bit.
>> > I ran instcat.sql script as microsoft article sugested.
>> > Still, I am having problems.
>> > I can browse db objects using management studio.
>> > But when i try to run sql like:
>> > select count(*) from botserver.master.dbo.sysobjects
>> >
>> > I am getting:
>> > Heterogeneous queries and use of OLEDB providers are not supported in
>> > fiber
>> > mode.
>> >
>> > I can neither run any stored procs.
>> > My linked server is defined as server type: sql server
>> >
>> > Any ideas?
>>|||Hi Andrew
Thank you for your information.
Here I have few questions if you would be interested to answer:
1. Do you think that fiber pooling is the real reason for linked server
problem
2. I have 2 similar servers: 8 cpu, 8gb of memory running 64 bit 2003
advanced server. They both do not run anything but 64 bit 2005 sql server.
One is very busy but cpu is close to 3-5% - plenty of queries but all
optimized and lightweight - mostly inserts and single value indexed selects.
cashe is 99%, no paging. Many hundreds processes access it.
Another server is just idling.
Both have 800 - 5000 contest switching all the time. Which i think is too
high.
Do you think it's normal or typical for given platform?
Thank you, Gene.
"Andrew J. Kelly" wrote:
> Context switching being high is relative but is also usually caused by other
> factors that can be addressed to ease this issue. Things like poorly
> optimized queries, lack of proper indexes, poorly configured I/O subsystems,
> too little memory, excessive blocking etc. Even other apps running on the
> same server can cause excessive context switching. Switching to fiber mode
> without addressing the root cause will only cause more problems in most
> cases. And fiber mode is usually only really effective on systems with more
> than 8 CPU's that are running at or near 100% utilization. I suggest you
> concentrate on finding the real bottle necks and think seriously about
> turning fiber mode off.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:13BBB927-0B8C-4479-8B01-6A9050DA9BF8@.microsoft.com...
> > Hi Kelly
> >
> > I have 8 cpu host and content switching is pretty high.
> > I read that these are indications to use light weight pooling.
> > If you can please educate me on this point too.
> >
> > Thank you, Gene.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Why are you running in fiber mode in the first place? Unless you have a
> >> very good reason for turning it on you should turn it back off and solve
> >> two
> >> problems.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> >> > Hi all
> >> > I am trying to create linked server on 2005 64bit server for 2000 32
> >> > bit.
> >> > I ran instcat.sql script as microsoft article sugested.
> >> > Still, I am having problems.
> >> > I can browse db objects using management studio.
> >> > But when i try to run sql like:
> >> > select count(*) from botserver.master.dbo.sysobjects
> >> >
> >> > I am getting:
> >> > Heterogeneous queries and use of OLEDB providers are not supported in
> >> > fiber
> >> > mode.
> >> >
> >> > I can neither run any stored procs.
> >> > My linked server is defined as server type: sql server
> >> >
> >> > Any ideas?
> >>
> >>
>|||1. Yes Fiber mode has many restrictions and I am pretty sure that the
linked server issues you have are one of them.
2. 800 - 5000 context switching for an 8 processor server is not that much.
Especially if you have lots happening. That is no where even close to the
range that should be a consideration for fiber mode usage. Turn it off and
you will be much happier.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:4C7A457E-4176-49EC-8660-D7A95D689A04@.microsoft.com...
> Hi Andrew
> Thank you for your information.
> Here I have few questions if you would be interested to answer:
> 1. Do you think that fiber pooling is the real reason for linked server
> problem
> 2. I have 2 similar servers: 8 cpu, 8gb of memory running 64 bit 2003
> advanced server. They both do not run anything but 64 bit 2005 sql server.
> One is very busy but cpu is close to 3-5% - plenty of queries but all
> optimized and lightweight - mostly inserts and single value indexed
> selects.
> cashe is 99%, no paging. Many hundreds processes access it.
> Another server is just idling.
> Both have 800 - 5000 contest switching all the time. Which i think is too
> high.
> Do you think it's normal or typical for given platform?
> Thank you, Gene.
>
> "Andrew J. Kelly" wrote:
>> Context switching being high is relative but is also usually caused by
>> other
>> factors that can be addressed to ease this issue. Things like poorly
>> optimized queries, lack of proper indexes, poorly configured I/O
>> subsystems,
>> too little memory, excessive blocking etc. Even other apps running on
>> the
>> same server can cause excessive context switching. Switching to fiber
>> mode
>> without addressing the root cause will only cause more problems in most
>> cases. And fiber mode is usually only really effective on systems with
>> more
>> than 8 CPU's that are running at or near 100% utilization. I suggest you
>> concentrate on finding the real bottle necks and think seriously about
>> turning fiber mode off.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> news:13BBB927-0B8C-4479-8B01-6A9050DA9BF8@.microsoft.com...
>> > Hi Kelly
>> >
>> > I have 8 cpu host and content switching is pretty high.
>> > I read that these are indications to use light weight pooling.
>> > If you can please educate me on this point too.
>> >
>> > Thank you, Gene.
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Why are you running in fiber mode in the first place? Unless you have
>> >> a
>> >> very good reason for turning it on you should turn it back off and
>> >> solve
>> >> two
>> >> problems.
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
>> >> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
>> >> > Hi all
>> >> > I am trying to create linked server on 2005 64bit server for 2000 32
>> >> > bit.
>> >> > I ran instcat.sql script as microsoft article sugested.
>> >> > Still, I am having problems.
>> >> > I can browse db objects using management studio.
>> >> > But when i try to run sql like:
>> >> > select count(*) from botserver.master.dbo.sysobjects
>> >> >
>> >> > I am getting:
>> >> > Heterogeneous queries and use of OLEDB providers are not supported
>> >> > in
>> >> > fiber
>> >> > mode.
>> >> >
>> >> > I can neither run any stored procs.
>> >> > My linked server is defined as server type: sql server
>> >> >
>> >> > Any ideas?
>> >>
>> >>
>>|||Thank you Andrew, your advises were really helpfull.
"Andrew J. Kelly" wrote:
> 1. Yes Fiber mode has many restrictions and I am pretty sure that the
> linked server issues you have are one of them.
> 2. 800 - 5000 context switching for an 8 processor server is not that much.
> Especially if you have lots happening. That is no where even close to the
> range that should be a consideration for fiber mode usage. Turn it off and
> you will be much happier.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> news:4C7A457E-4176-49EC-8660-D7A95D689A04@.microsoft.com...
> > Hi Andrew
> >
> > Thank you for your information.
> > Here I have few questions if you would be interested to answer:
> > 1. Do you think that fiber pooling is the real reason for linked server
> > problem
> > 2. I have 2 similar servers: 8 cpu, 8gb of memory running 64 bit 2003
> > advanced server. They both do not run anything but 64 bit 2005 sql server.
> > One is very busy but cpu is close to 3-5% - plenty of queries but all
> > optimized and lightweight - mostly inserts and single value indexed
> > selects.
> > cashe is 99%, no paging. Many hundreds processes access it.
> > Another server is just idling.
> > Both have 800 - 5000 contest switching all the time. Which i think is too
> > high.
> > Do you think it's normal or typical for given platform?
> >
> > Thank you, Gene.
> >
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Context switching being high is relative but is also usually caused by
> >> other
> >> factors that can be addressed to ease this issue. Things like poorly
> >> optimized queries, lack of proper indexes, poorly configured I/O
> >> subsystems,
> >> too little memory, excessive blocking etc. Even other apps running on
> >> the
> >> same server can cause excessive context switching. Switching to fiber
> >> mode
> >> without addressing the root cause will only cause more problems in most
> >> cases. And fiber mode is usually only really effective on systems with
> >> more
> >> than 8 CPU's that are running at or near 100% utilization. I suggest you
> >> concentrate on finding the real bottle necks and think seriously about
> >> turning fiber mode off.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> news:13BBB927-0B8C-4479-8B01-6A9050DA9BF8@.microsoft.com...
> >> > Hi Kelly
> >> >
> >> > I have 8 cpu host and content switching is pretty high.
> >> > I read that these are indications to use light weight pooling.
> >> > If you can please educate me on this point too.
> >> >
> >> > Thank you, Gene.
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Why are you running in fiber mode in the first place? Unless you have
> >> >> a
> >> >> very good reason for turning it on you should turn it back off and
> >> >> solve
> >> >> two
> >> >> problems.
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >> Solid Quality Mentors
> >> >>
> >> >>
> >> >> "Gene." <Gene@.discussions.microsoft.com> wrote in message
> >> >> news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> >> >> > Hi all
> >> >> > I am trying to create linked server on 2005 64bit server for 2000 32
> >> >> > bit.
> >> >> > I ran instcat.sql script as microsoft article sugested.
> >> >> > Still, I am having problems.
> >> >> > I can browse db objects using management studio.
> >> >> > But when i try to run sql like:
> >> >> > select count(*) from botserver.master.dbo.sysobjects
> >> >> >
> >> >> > I am getting:
> >> >> > Heterogeneous queries and use of OLEDB providers are not supported
> >> >> > in
> >> >> > fiber
> >> >> > mode.
> >> >> >
> >> >> > I can neither run any stored procs.
> >> >> > My linked server is defined as server type: sql server
> >> >> >
> >> >> > Any ideas?
> >> >>
> >> >>
> >>
> >>
>

Heterogeneous queries and use of OLEDB providers are not supported

Hi all
I am trying to create linked server on 2005 64bit server for 2000 32 bit.
I ran instcat.sql script as microsoft article sugested.
Still, I am having problems.
I can browse db objects using management studio.
But when i try to run sql like:
select count(*) from botserver.master.dbo.sysobjects
I am getting:
Heterogeneous queries and use of OLEDB providers are not supported in fiber
mode.
I can neither run any stored procs.
My linked server is defined as server type: sql server
Any ideas?Why are you running in fiber mode in the first place? Unless you have a
very good reason for turning it on you should turn it back off and solve two
problems.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:DE0360F5-3A4C-47BB-9304-10D0FA789855@.microsoft.com...
> Hi all
> I am trying to create linked server on 2005 64bit server for 2000 32 bit.
> I ran instcat.sql script as microsoft article sugested.
> Still, I am having problems.
> I can browse db objects using management studio.
> But when i try to run sql like:
> select count(*) from botserver.master.dbo.sysobjects
> I am getting:
> Heterogeneous queries and use of OLEDB providers are not supported in
> fiber
> mode.
> I can neither run any stored procs.
> My linked server is defined as server type: sql server
> Any ideas?

Wednesday, March 7, 2012

Help: Why excute a stored procedure need to more 30 seconds, but direct excute the query o

Hello to all,

I have a stored procedure. If i give this commandexce ShortestPath 3418, '4125', 5 in a script and excute it. It takes more 30 seconds time to be excuted.

but i excute it with the same parameters direct in Microsoft SQL Server Management Studio , It takes only under 1 second time

I don't know why?

Maybe can somebody help me?

thanks in million

best Regards

Pinsha

My Procedure Codes are here:

set ANSI_NULLSON

set QUOTED_IDENTIFIERON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTERPROCEDURE [dbo].[ShortestPath](@.IDMemberint, @.IDOther varchar(1000),@.Levelint, @.Path varchar(100)=null output)

AS

BEGIN

if( @.Level= 1)

begin

select @.Path=convert(varchar(100),IDMember)

from wtcomValidRelationships

where wtcomValidRelationships.[IDMember]= @.IDMember

andPATINDEX('%'+@.IDOther+'%',(select RelationshipIDsfrom wtcomValidRelationshipswhere IDMember= @.IDMember))> 0

end

if(@.Level= 2)

begin

selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B

where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andPATINDEX('%'+@.IDOther+'%',B.RelationshipIDs)> 0

end

if(@.Level= 3)

begin

selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C

where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andPATINDEX('%'+@.IDOther+'%',C.RelationshipIDs)> 0

end

if( @.Level= 4)

begin

selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D

where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andPATINDEX('%'+@.IDOther+'%',D.RelationshipIDs)> 0

end

if(@.Level= 5)

begin

selecttop 1 @.Path=convert(varchar(100),A.IDMember)+'-'+convert(varchar(100),B.IDMember)+'-'+convert(varchar(100),C.IDMember)+'-'+convert(varchar(100),D.IDMember)+'-'+convert(varchar(100),E.IDMember)

from wtcomValidRelationshipsas A, wtcomValidRelationshipsas B, wtcomValidRelationshipsas C, wtcomValidRelationshipsas D, wtcomValidRelationshipsas E

where A.IDMember= @.IDMemberandcharindex(convert(varchar(100),B.IDMember),A.RelationshipIDS)> 0

andcharindex(convert(varchar(100),C.IDMember),B.RelationshipIDs)> 0andcharindex(convert(varchar(100),D.IDMember), C.RelationshipIDs)> 0

andcharindex(convert(varchar(100),E.IDMember),D.RelationshipIDs)> 0andPATINDEX('%'+@.IDOther+'%',E.RelationshipIDs)> 0

end

if(@.Level= 6)

begin

selecttop 1 @.Path=''from wtcomValidRelationships

end

END

Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?

If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.

You run it like this:

EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.

Where

a|||

Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?

If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.

You run it like this:

EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.

Where

|||

Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?

If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.

You run it like this:

EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.

Where

a stored|||

Was bothexce ShortestPath 3418, '4125', 5 and the run of the SQL within the sp done within MS SQL Management Studio with the same account on both sessions?

If yes then the problem may be due to the way the sp was initially run - it is likely to need recompiling look upsp_recompile in books on line.

You run it like this:

EXEC sp_recompile N'TABLENAME'; -- where TABLENAME is name of one of the tables the s.p. acts on.

This problem can happen , whereever a stored procedure has radically paths through it with reference to the most efficient way of processing.

|||

Hello,

I have tried this EXEC sp_recompile N'TABLENAME. But this problem can not be resolved.

Somebody tell me that thecharindex function is not good for searching. Better use in function. I tried to use in function. Another Problem comes: Member 3430 has Relationship with Member 3418, but i usedin function, Member 3430 can not be found that it has relationship with Member 3418. Here is my test code:

declare @.IDMint;

declare @.IDO varchar(100);

set @.IDM= 3418;

set @.IDO='3430'

selectconvert(varchar(100),IDMember)

from wtcomValidRelationships

where wtcomValidRelationships.[IDMember]= 3418

and(@.IDOin(select B.RelationshipIDsfrom wtcomValidRelationshipsas Bwhere B.IDMember= @.IDM))

Maybe somebody can help me? Thanks

Best Regards

Pinsha

|||

Try splittingdbo].[ShortestPath] intodbo].[ShortestPathLevel1],dbo].[ShortestPathLevel2] ect.so that each only has the logic to process one level. This will ensure that each is optimised correctly.

help: table view gone in EM sql 7.0

I seem to have screwed up EM (sql 7.0) and lost the table view for 1 DB. The
DB was giving me a script error so I tried redoing a DB diagram and killed
it when I found changing views of the DB would overcome the script error.
anyway sometime during that time I lost my table view it shows the count but
thats all no matter what view I select.Hi
Can you connect to the server from another PC and see if the view is
present? That would imply that it is the tools on the PC that are the issue
and you can then re-install them.
John
"evan b" <evan_at_cheapaschips.com.au_removethis> wrote in message
news:OK2RggD5EHA.2964@.TK2MSFTNGP15.phx.gbl...
>I seem to have screwed up EM (sql 7.0) and lost the table view for 1 DB.
>The
> DB was giving me a script error so I tried redoing a DB diagram and killed
> it when I found changing views of the DB would overcome the script error.
> anyway sometime during that time I lost my table view it shows the count
> but
> thats all no matter what view I select.
>|||John
Yes I can and yes it is - I have uninstalled and reinstalled locally
(laptop) and I still have the problem is there anywhere where I cleanup and
default views etc as it seems to remember past servers etc.
evan
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23s6TkbN5EHA.2664@.TK2MSFTNGP10.phx.gbl...
> Hi
> Can you connect to the server from another PC and see if the view is
> present? That would imply that it is the tools on the PC that are the
issue
> and you can then re-install them.
> John
> "evan b" <evan_at_cheapaschips.com.au_removethis> wrote in message
> news:OK2RggD5EHA.2964@.TK2MSFTNGP15.phx.gbl...
> >I seem to have screwed up EM (sql 7.0) and lost the table view for 1 DB.
> >The
> > DB was giving me a script error so I tried redoing a DB diagram and
killed
> > it when I found changing views of the DB would overcome the script
error.
> >
> > anyway sometime during that time I lost my table view it shows the count
> > but
> > thats all no matter what view I select.
> >
> >
>|||problem solved I deleted the MMC files within
profile\applications\micro...\ etc and then it defaulted to the normal
views.
"evan b" <evan_at_cheapaschips.com.au_removethis> wrote in message
news:u3mtub95EHA.3236@.TK2MSFTNGP15.phx.gbl...
> John
> Yes I can and yes it is - I have uninstalled and reinstalled locally
> (laptop) and I still have the problem is there anywhere where I cleanup
and
> default views etc as it seems to remember past servers etc.
> evan
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%23s6TkbN5EHA.2664@.TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > Can you connect to the server from another PC and see if the view is
> > present? That would imply that it is the tools on the PC that are the
> issue
> > and you can then re-install them.
> >
> > John
> >
> > "evan b" <evan_at_cheapaschips.com.au_removethis> wrote in message
> > news:OK2RggD5EHA.2964@.TK2MSFTNGP15.phx.gbl...
> > >I seem to have screwed up EM (sql 7.0) and lost the table view for 1
DB.
> > >The
> > > DB was giving me a script error so I tried redoing a DB diagram and
> killed
> > > it when I found changing views of the DB would overcome the script
> error.
> > >
> > > anyway sometime during that time I lost my table view it shows the
count
> > > but
> > > thats all no matter what view I select.
> > >
> > >
> >
> >
>

Monday, February 27, 2012

Help: Purge Table Stored Procedure

I have to create a stored procedure to purge "x" # of records from a
table. I have two tables (script below):

Schedule
ScheduleHistory

I need to purge records out of ScheduleHistory. The problem is that
the # of records that needs to be "kept" is dynamic, and stored in the
Schedule table. So all records in ScheduleHistory should be purged,
except for the most recent "x" number of records.

For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.

In the script below, I have two schedules ("test1" and "test2"). One
of them specifies that the most recent "5" records in ScheduleHistory
should be kept, and the other specifies that the most recent "3"
records in ScheduleHistory should be kept.

So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.

Dan

------
SCRIPT BELOW
------

CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[TimeLapse] [int] NOT NULL ,
[TimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetryTimeLapse] [int] NOT NULL ,
[RetryTimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetainHistoryNum] [int] NOT NULL ,
[ObjectDependencies] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ScheduleHistory] (
[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[Succeeded] [bit] NULL ,
[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NextStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')Assuming you want the keep the most recent rows based on StartDate, one
method:

DELETE FROM ScheduleHistory
FROM Schedule s
WHERE
(
SELECT COUNT(*)
FROM ScheduleHistory sh
WHERE
sh.ScheduleID = ScheduleHistory.ScheduleID AND
sh.ScheduleID = s.ScheduleID AND
sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> I have to create a stored procedure to purge "x" # of records from a
> table. I have two tables (script below):
> Schedule
> ScheduleHistory
> I need to purge records out of ScheduleHistory. The problem is that
> the # of records that needs to be "kept" is dynamic, and stored in the
> Schedule table. So all records in ScheduleHistory should be purged,
> except for the most recent "x" number of records.
> For instance, for each Schedule row, you can specify how many records
> to "keep" at all times. I want to keep the most recent "x" # of
> ScheduleHistory rows for each Schedule.
> In the script below, I have two schedules ("test1" and "test2"). One
> of them specifies that the most recent "5" records in ScheduleHistory
> should be kept, and the other specifies that the most recent "3"
> records in ScheduleHistory should be kept.
> So, I need a stored procedure that can clean up this table on demand.
> I can't figure this one out...it's over my head.
> Dan
> ------
> SCRIPT BELOW
> ------
>
> CREATE TABLE [dbo].[Schedule] (
> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [TimeLapse] [int] NOT NULL ,
> [TimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetryTimeLapse] [int] NOT NULL ,
> [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetainHistoryNum] [int] NOT NULL ,
> [ObjectDependencies] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ScheduleHistory] (
> [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [ScheduleID] [int] NOT NULL ,
> [StartDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NULL ,
> [Succeeded] [bit] NULL ,
> [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NextStart] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.

-Dan

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@.newsread2.news.pas.earthlink.n et>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> > Schedule
> > ScheduleHistory
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> > Dan
> > ------
> > SCRIPT BELOW
> > ------
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')|||That is exactly what I needed. Thank you very much Dan.

-Dan

"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<MLvic.10091$e4.6777@.newsread2.news.pas.earthlink.n et>...
> Assuming you want the keep the most recent rows based on StartDate, one
> method:
> DELETE FROM ScheduleHistory
> FROM Schedule s
> WHERE
> (
> SELECT COUNT(*)
> FROM ScheduleHistory sh
> WHERE
> sh.ScheduleID = ScheduleHistory.ScheduleID AND
> sh.ScheduleID = s.ScheduleID AND
> sh.StartDate >= ScheduleHistory.StartDate
> ) > s.RetainHistoryNum
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
> news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> > I have to create a stored procedure to purge "x" # of records from a
> > table. I have two tables (script below):
> > Schedule
> > ScheduleHistory
> > I need to purge records out of ScheduleHistory. The problem is that
> > the # of records that needs to be "kept" is dynamic, and stored in the
> > Schedule table. So all records in ScheduleHistory should be purged,
> > except for the most recent "x" number of records.
> > For instance, for each Schedule row, you can specify how many records
> > to "keep" at all times. I want to keep the most recent "x" # of
> > ScheduleHistory rows for each Schedule.
> > In the script below, I have two schedules ("test1" and "test2"). One
> > of them specifies that the most recent "5" records in ScheduleHistory
> > should be kept, and the other specifies that the most recent "3"
> > records in ScheduleHistory should be kept.
> > So, I need a stored procedure that can clean up this table on demand.
> > I can't figure this one out...it's over my head.
> > Dan
> > ------
> > SCRIPT BELOW
> > ------
> > CREATE TABLE [dbo].[Schedule] (
> > [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> > [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NOT NULL ,
> > [TimeLapse] [int] NOT NULL ,
> > [TimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetryTimeLapse] [int] NOT NULL ,
> > [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> > [RetainHistoryNum] [int] NOT NULL ,
> > [ObjectDependencies] [varchar] (300) COLLATE
> > SQL_Latin1_General_CP1_CI_AS NOT NULL
> > ) ON [PRIMARY]
> > GO
> > CREATE TABLE [dbo].[ScheduleHistory] (
> > [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> > [ScheduleID] [int] NOT NULL ,
> > [StartDate] [datetime] NOT NULL ,
> > [EndDate] [datetime] NULL ,
> > [Succeeded] [bit] NULL ,
> > [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [NextStart] [datetime] NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> > INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> > INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> > INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> > 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> > 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> > 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> > 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> > 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> > 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> > INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> > 1:01 PM', 1, '', '1/8/2000 1:00 PM')

Help: Purge Table Stored Procedure

I have to create a stored procedure to purge "x" # of records from a
table. I have two tables (script below):

Schedule
ScheduleHistory

I need to purge records out of ScheduleHistory. The problem is that
the # of records that needs to be "kept" is dynamic, and stored in the
Schedule table. So all records in ScheduleHistory should be purged,
except for the most recent "x" number of records.

For instance, for each Schedule row, you can specify how many records
to "keep" at all times. I want to keep the most recent "x" # of
ScheduleHistory rows for each Schedule.

In the script below, I have two schedules ("test1" and "test2"). One
of them specifies that the most recent "5" records in ScheduleHistory
should be kept, and the other specifies that the most recent "3"
records in ScheduleHistory should be kept.

So, I need a stored procedure that can clean up this table on demand.
I can't figure this one out...it's over my head.

Dan

------
SCRIPT BELOW
------

CREATE TABLE [dbo].[Schedule] (
[ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
[TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[TimeLapse] [int] NOT NULL ,
[TimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetryTimeLapse] [int] NOT NULL ,
[RetryTimeLapseMeasurement] [varchar] (2) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RetainHistoryNum] [int] NOT NULL ,
[ObjectDependencies] [varchar] (300) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ScheduleHistory] (
[ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
[ScheduleID] [int] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[Succeeded] [bit] NULL ,
[LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NextStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
1:01 PM', 1, '', '1/2/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
1:01 PM', 1, '', '1/3/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
1:01 PM', 1, '', '1/4/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
1:01 PM', 1, '', '1/5/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
1:01 PM', 1, '', '1/6/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
1:01 PM', 1, '', '1/7/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')
INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
1:01 PM', 1, '', '1/8/2000 1:00 PM')Assuming you want the keep the most recent rows based on StartDate, one
method:

DELETE FROM ScheduleHistory
FROM Schedule s
WHERE
(
SELECT COUNT(*)
FROM ScheduleHistory sh
WHERE
sh.ScheduleID = ScheduleHistory.ScheduleID AND
sh.ScheduleID = s.ScheduleID AND
sh.StartDate >= ScheduleHistory.StartDate
) > s.RetainHistoryNum

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dan Caron" <dancaron-listserv0687@.mailblocks.com> wrote in message
news:4d6e6a6c.0404231047.60cd40d0@.posting.google.c om...
> I have to create a stored procedure to purge "x" # of records from a
> table. I have two tables (script below):
> Schedule
> ScheduleHistory
> I need to purge records out of ScheduleHistory. The problem is that
> the # of records that needs to be "kept" is dynamic, and stored in the
> Schedule table. So all records in ScheduleHistory should be purged,
> except for the most recent "x" number of records.
> For instance, for each Schedule row, you can specify how many records
> to "keep" at all times. I want to keep the most recent "x" # of
> ScheduleHistory rows for each Schedule.
> In the script below, I have two schedules ("test1" and "test2"). One
> of them specifies that the most recent "5" records in ScheduleHistory
> should be kept, and the other specifies that the most recent "3"
> records in ScheduleHistory should be kept.
> So, I need a stored procedure that can clean up this table on demand.
> I can't figure this one out...it's over my head.
> Dan
> ------
> SCRIPT BELOW
> ------
>
> CREATE TABLE [dbo].[Schedule] (
> [ScheduleID] [int] IDENTITY (1, 1) NOT NULL ,
> [TypeFullName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [TimeLapse] [int] NOT NULL ,
> [TimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetryTimeLapse] [int] NOT NULL ,
> [RetryTimeLapseMeasurement] [varchar] (2) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RetainHistoryNum] [int] NOT NULL ,
> [ObjectDependencies] [varchar] (300) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ScheduleHistory] (
> [ScheduleHistoryID] [int] IDENTITY (1, 1) NOT NULL ,
> [ScheduleID] [int] NOT NULL ,
> [StartDate] [datetime] NOT NULL ,
> [EndDate] [datetime] NULL ,
> [Succeeded] [bit] NULL ,
> [LogNotes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [NextStart] [datetime] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> INSERT INTO Schedule VALUES ('Test1', 1, 'd', 1, 'm', 5, '')
> INSERT INTO Schedule VALUES ('Test2', 1, 'd', 1, 'm', 3, '')
> INSERT INTO ScheduleHistory VALUES (1,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/1/2000 1:00 PM', '1/1/2000
> 1:01 PM', 1, '', '1/2/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/2/2000 1:00 PM', '1/2/2000
> 1:01 PM', 1, '', '1/3/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/3/2000 1:00 PM', '1/3/2000
> 1:01 PM', 1, '', '1/4/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/4/2000 1:00 PM', '1/4/2000
> 1:01 PM', 1, '', '1/5/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/5/2000 1:00 PM', '1/5/2000
> 1:01 PM', 1, '', '1/6/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/6/2000 1:00 PM', '1/6/2000
> 1:01 PM', 1, '', '1/7/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (1,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')
> INSERT INTO ScheduleHistory VALUES (2,'1/7/2000 1:00 PM', '1/7/2000
> 1:01 PM', 1, '', '1/8/2000 1:00 PM')