How to determine EXEC permission to an extended stored procedure?

The following proc indicates whether you have EXEC permission to a proc -
however it fails for extended procs. I'd be grateful for a fix!
A good test is @.SPNM = 'xp_sprintf'. Note that the proc is getting a valid
object id for the extended procs.
PROCEDURE procHasExecutePermission
( @.SPNM sysname,
@.HAS bit OUTPUT
) AS
BEGIN
SET NOCOUNT ON
DECLARE @.OID int
SET @.OID = OBJECT_ID(@.SPNM)
IF @.OID IS NULL
IF SUBSTRING(@.SPNM, 1, 3) = 'sp_' OR SUBSTRING(@.SPNM, 1, 3) = 'xp_'
SET @.OID = OBJECT_ID('master..' + @.SPNM)
IF @.OID IS NULL
SET @.HAS = 0
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
END
Thanks in advance for your help,
Hal Heinrich
VP Technology
Aralan Solutions Inc.It doesn't fail (only) for extended procs, it fails for the objects
that begin with 'sp_' or 'xp_', because you are getting the OBJECT_ID
for the object from the master database, but the PERMISSIONS function
accepts only object id-s for objects from the current database. In some
cases, it may look like it's working for some objects from master, but
that's only because the same object id is allocated in the current
database for another object.
If you need to check for objects that may be in master, I would use
another procedure like this:
USE master
GO
CREATE PROCEDURE procHasExecutePermission1
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
USE YourDatabase
GO
CREATE PROCEDURE procHasExecutePermission2
(@.SPNM sysname, @.HAS int OUTPUT) AS
SET NOCOUNT ON
DECLARE @.OID int
SELECT @.OID = id FROM sysobjects
WHERE name=@.SPNM AND xtype IN ('P','X','FN')
IF @.OID IS NULL
SET @.HAS = NULL
ELSE
IF PERMISSIONS(@.OID) & 0x20 = 0x20
SET @.HAS = 1
ELSE
SET @.HAS = 0
GO
CREATE PROCEDURE procHasExecutePermission3
(@.SPNM sysname, @.HAS int OUTPUT) AS
IF LEFT(@.SPNM,3)='sp_'
EXEC master..procHasExecutePermission1 @.SPNM, @.HAS OUTPUT
IF @.HAS IS NOT NULL RETURN
EXEC procHasExecutePermission2 @.SPNM, @.HAS OUTPUT
I have used sysobjects to check the object type, so if you pass a table
name it will return NULL instead of 0.
You may want to improve these procedures using PARSENAME if you need to
allow procedure names prefixed with the database name. If the database
name may also be other than the current database or the master
database, then it gets complicated... there may be a solution by
calling the PERMISSIONS function from Dynamic SQL.
Razvan

How to determine Count of ties in MDX?

I need to determine the number of values in a set that are non-unique in the set.

In SQL, I would do the following:

select sum(value_count) as Ties

from (select value, count(*) as value_count

from source

group by value

having count(*) >1

)

In MDX, I have a set defined in a query that is passed into several custom statistical functions. I would like to be able to accomplish this without writing another external function, as it seems the sort of thing that should be easy...

Here is the query I'm trying to plug this into. Suggestions welcome.

with

set [data] as ( nonempty({

[Sample Date].[Time].[Day] }) *{ [Measures].[Parm Value] } )

MEMBER [Sen's Slope] as 'Statistics.Sen_Slope({[data]})'

MEMBER [Kendall's Tau] as 'Statistics.KENDALLS_TAU({[data]})'

MEMBER [Significant] as 'Statistics.Significance({[data]})'

MEMBER [MK_Z] as 'Statistics.Mann_Kendall_Z({[data]})'

member [measures].[Samples] as [data].count

member [measures].[Average] as avg([data])

member [measures].[Median] as Median([data])

member [measures].[Last Sample] as tail([data],1).item(0)

MEMBER [Ties] as '0'

select {[Samples],[Average],[Median],[Last Sample], [Kendall's Tau], [Sen's Slope], [MK_Z], [Significant], [Ties] } on 0

FROM ( SELECT ( [Sample Date].[Time].[2002 Q4]:[Sample Date].[Time].[2006 Q3] ) on 0,

{([Site Hierarchy].[Site Hierarchy].[Site ID].[0829SD6001] ,

[Analysis Parameter].[Analysis Parameter].[Acidity (ACD)])} ON 1

FROM [Sample Data])

;

Hi Clayton,

This sample from Adventure Works, where value is [Reseller Order Quantity], seems to work:

SQL query:

>>

select sum(order_count) as Ties
from
(select OrderQuantity, count(*) as order_count
from
(select ProductKey, sum(OrderQuantity) as OrderQuantity
from dbo.FactResellerSales
group by ProductKey) ps
group by OrderQuantity
having count(*) > 1) oc

87

>>

MDX query:

>>

With

Member [Measures].[PrdTies] as

Count(Filter(Order(NonEmpty(

[Product].[Product].[Product].Members,

{[Measures].[Reseller Order Quantity]}),

[Measures].[Reseller Order Quantity], BDESC) as OrdPrds,

(OrdPrds.CurrentOrdinal < OrdPrds.Count

And [Measures].[Reseller Order Quantity] =

([Measures].[Reseller Order Quantity],

OrdPrds.Item(OrdPrds.CurrentOrdinal)))

OR (OrdPrds.CurrentOrdinal > 1

And [Measures].[Reseller Order Quantity] =

([Measures].[Reseller Order Quantity],

OrdPrds.Item(OrdPrds.CurrentOrdinal-2)))))

select {[Measures].[PrdTies]} on 0

from [Adventure Works]

PrdTies
87

>>

|||

Deepak,

Thanks for the thourough response. I've done some testing, comparing results from the MDX you wrote to the ADOMD CLR proc I wrote, and I was getting different results. I've traced through the CLR version and manually counted the values in the set, and the CLR proc was correct. I've clipped out the relevant code in case you want to compare results. It may be the differences in how I'm calling the CLR functions by passing in a set versus the Measure method, which is working over the complete set of data from the subcube query.

Here is your test MDX query, with a measure added for the CLR function. In this case, both methods return the same count, as expected. I'm just curious as to what might be causing me to get dfferent counts between the two methods when used in my cube...

With

set [data] as ( NonEmpty([Product].[Product].[Product].Members)

*([Measures].[Reseller Order Quantity]) )

member [CountOfTies] as 'Statistics.CountOfTies({[data]})'

Member [Measures].[PrdTies] as

Count(Filter(Order(NonEmpty(

[Product].[Product].[Product].Members,

{[Measures].[Reseller Order Quantity]}),

[Measures].[Reseller Order Quantity], BDESC) as OrdPrds,

(OrdPrds.CurrentOrdinal < OrdPrds.Count

And [Measures].[Reseller Order Quantity] =

([Measures].[Reseller Order Quantity],

OrdPrds.Item(OrdPrds.CurrentOrdinal)))

OR (OrdPrds.CurrentOrdinal > 1

And [Measures].[Reseller Order Quantity] =

([Measures].[Reseller Order Quantity],

OrdPrds.Item(OrdPrds.CurrentOrdinal-2)))))

select {[Measures].[PrdTies], [CountOfTies]} on 0

from [Adventure Works];

Assembly code:

using System;

using System.Collections;

using Microsoft.AnalysisServices;

using Microsoft.AnalysisServices.AdomdServer;

namespace Statistics

{

public sealed class Statistics

{

private Statistics()

{

}

[CLSCompliant(false)]

// function to return a sum of the counts for tied values

public static Object CountOfTies(Set data1)

{

try

{

//number of Tuples in the set

Int32 v = data1.Tuples.Count;

if (v > 1)

{

//dimension a new array to hold values for passing into GetCountOfTies()

Double[] ValueArray;

ValueArray = new Double[v];

MDXValue mdxval;

Int32 i = 0;

// convert tuple set into an array

foreach (Tuple t in data1.Tuples)

{

mdxval = t;

ValueArray[i++] = mdxval.ToDouble();

}

// get array of any ties

Int32[] ResultArray = GetCountOfTies(ValueArray);

//sum counts to get results to return

i = 0;

foreach (Int32 j in ResultArray)

{i = i + j;}

return i;

}

else {return 0;}

}

catch (Exception e)

{

Console.WriteLine(e);

return "ERROR";

}

}

private static Int32[] GetCountOfTies(double[] doubleArray)

{

//create an array of counts of unique values, where there are ties in values.

//the resulting array will contain one element for each value with ties.

Array.Sort(doubleArray);

ArrayList numbers = new ArrayList();

double? lastVal = null;

int currentCount = 0;

foreach (double val in doubleArray)

{

if (lastVal != null && lastVal != val)

{

if (currentCount > 1)

numbers.Add(currentCount);

currentCount = 1;

}

else

{currentCount++;}

lastVal = val;

}

return (Int32[])numbers.ToArray(typeof(Int32));

}

}

}

How to Deploy Report on Remote Server..

Hi,

I m getting following error while deploying report on remote server...

Error 3 The permissions granted to user 'ZENITH\BalwantPatel' are insufficient for performing this operation. 0 0

The following setting i have on my remote server.

Configure Report Server:

Report Server Virtual Directory: Default

Report Manager Virtual Directory: Default

Windows Service Identity:
Service Name: Report Server
Service Account: Domain\Administrator

Windows Account: Domain\Administrator.

Web Service Identity : ASP.NET Service Account: ServerName\ASPNET

I m creating Report on my local machine as Domain\Balwant Patel. and Trying to deploy on other server on the same domain....

I dont know What settings do need to change on webserver or on my local machine.

Thank you,

Ballu.

hello,

I see u have not configured ur report server. Go to "start" -- "sql server 2005" -- "configuration tools" -- "reporting services configuration". Go thru it step by step and configure it. Once you have done it, you should have "content manager" permission to deploy on the remote server. Needlesss to say, I am assuming you have given the correct target URL for deploment. For "content manager" persmission settings, read http://msdn2.microsoft.com/en-us/library/aa337491.aspx.

I hope it helps...

Regards..

|||

Hi AsianIndian,

Thank you very much for your response....The Link That u give me is very useful...Now I get The Idea How to setup permission on SQL Server Reporting Service...I configured Report Server fine...but I don't know how to setup permission through SQL Server Management Studio...I have one problem when I m trying to connection to my Report Server Through SQL Server Management Studio... Initially when i m trying to connection Report Server on my local machine...i m getting the error that there is not enough permission on C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files this folder to machine\ASP.NET...So I give full permission to ASP.NET user....so I thought the same problem may occure on my deplyoment server...but I get different error...the error was internal server and the error and even the error was not shown correctly...but I found somewhere on the forums that I have to remove <configuration xmlns> attribute from the configuration node from Drive:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\web.config.

Regards,

Balwant Patel.

How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

How to delete the role by using AMO(Analysis Management Object)?

I want to delete the role by using AMO, but what I find is only a way that how to create it.

following sample is deleting the members of the role :

Code Snippet

Dim ServerName As Server 'Connect OLAP Server
Dim db As Database 'Database of OLAP Server

Dim role As Role

role = db.Roles.Item(0)
role.Members.Clear()
role.Update()

So I think that deleting the roles of database is like following :

Code Snippet

db.Roles.Clear()

db.Update()

It's wrong. It can't delete roles of a database after executing.

Please tell me what I shall do or where I can find these infoemation, thanks!!

Here's a code sample from one of our developers that does this. (Thanks for the code, Jason.) Keep in mind that in this code, we were looking for Roles that met a naming standard. It's a long story, but you may want to skip that step. Still, I kept it in here (and only changed the naming pattern) so I can insure the code still works with minimal effort. Also, keep in mind this code was written for an SSIS package so you will see some odd ball references in there.

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

Dim roleCollection As New ArrayList()

' FIND ALL OF THE ROLES IN THE ANALYSIS SERVICES DB THAT MATCH THE NAMING PATTERN
For Each currentRole As Role In database.Roles
If (currentRole.Name.StartsWith("XYZ")) Then
' BECAUSE YOU CAN'T DELETE AN ITEM IN A COLLECTION, ADD TO THE roleCollection ARRAY LIST
roleCollection.Add(currentRole)
End If
Next

' DELETE ALL ROLES IN THE roleCollection
For Each currentObj As Role In roleCollection

'DROP OPTION OF AlterOrDeleteDependents SHOULD REMOVE ANY ASSOCIATED PERMISSIONS WITH THIS ROLE
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()
Next

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

|||

Thanks for your answer~~

I can delete the role now.But I find that I can skip the step of ArrayList() and delete the role.

I don't know what a risk has in these statement, like following code :

If you know that. Could you tell me,please? Thanks!!

Code Snippet

Public Sub Main()
' SSAS 2005 Server
Dim server As New Microsoft.AnalysisServices.Server
Try
' CONNECT TO THE SERVER
server.Connect("localhost")

' THE ANALYSIS SERVICES DATABASE TO CONNECT TO
Dim database As New Microsoft.AnalysisServices.Database
database = server.Databases.FindByName(Dts.Variables("AnalysisServicesDatabaseName").Value.ToString)

' DELETE A ROLES
Dim currentObj As Role

currentObj.database.Roles.FinByName("XYZ")
currentObj.Drop(DropOptions.AlterOrDeleteDependents)
database.Update()

Dts.TaskResult = Dts.Results.Success

Catch ex As Exception
Dts.Events.FireError(1, ex.TargetSite.ToString, ex.Message, "", 0)
Finally
server.Disconnect()
End Try

End Sub

How to delete records on dependent tables? Thank You.

Hello,
I am creating my first procedures in SQL using SQL 2005.
I have 3 tables, with the following columns:
Surveys - [SurveyId](PK) and [SurveyName]
Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
[SurveyAnswer]
Each survay can include various questions and each question can include
several answers.
This is way I am using the Foreign Keys in both Questions and Answers
tables. To relate the tables.
I created a procedure which deletes a Survey given its SurveyId. This is
part is done.
I also need to delete all the questions dependent on that survey and all
the answers dependent on those questions.
How can I delete survey, its questions and their answers when receiving
the SurveyId?
Thank You Very Much,
Miguel
Here is the code of the procedure that I created which in this moment
only deletes the survey from the Surveys table:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
-- Procedure Parameters
@.SurveyId As uniqueidentifier
AS
BEGIN
-- Check if SurveyId is null
IF( @.SurveyId IS NULL )
RETURN -1
ELSE
BEGIN
-- Return '-1' if a survey with SurveyId given value is not found
IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
SurveyId ) )
RETURN -1
END
-- Delete the survey with SurveyId given value
DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
-- Return '0' when successful
RETURN 0
ENDYou just need to add cascade delete to your foreign key constraints and the
database will do this automatically.
This assumes that you always want to delete the related records.
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am creating my first procedures in SQL using SQL 2005.
> I have 3 tables, with the following columns:
> Surveys - [SurveyId](PK) and [SurveyName]
> Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
> Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
> [SurveyAnswer]
> Each survay can include various questions and each question can include
> several answers.
> This is way I am using the Foreign Keys in both Questions and Answers
> tables. To relate the tables.
> I created a procedure which deletes a Survey given its SurveyId. This is
> part is done.
> I also need to delete all the questions dependent on that survey and all
> the answers dependent on those questions.
> How can I delete survey, its questions and their answers when receiving
> the SurveyId?
> Thank You Very Much,
> Miguel
> Here is the code of the procedure that I created which in this moment
> only deletes the survey from the Surveys table:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
> -- Procedure Parameters
> @.SurveyId As uniqueidentifier
> AS
> BEGIN
> -- Check if SurveyId is null
> IF( @.SurveyId IS NULL )
> RETURN -1
> ELSE
> BEGIN
> -- Return '-1' if a survey with SurveyId given value is not found
> IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
> SurveyId ) )
> RETURN -1
> END
> -- Delete the survey with SurveyId given value
> DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
> -- Return '0' when successful
> RETURN 0
> END
>|||Hi,
Could you, please, explain how to add cascade delete to my foreign key
constraints.
I am starting with SQL and I have no idea how to do that.
Thanks,
Miguel
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
> You just need to add cascade delete to your foreign key constraints and th
e
> database will do this automatically.
> This assumes that you always want to delete the related records.
> "Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
> news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...|||It is best to look it up in Books OnLine, or check with your DBA.
Here is an example of the syntax, however.
ALTER TABLE [owner].[tablename] ADD CONSTRAINT
[constraintname] Foreign KEY
(
[Columnname]
) REFERENCES [owner].[OtherTablename] (
[Columnname]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:ujxYNCOVGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Could you, please, explain how to add cascade delete to my foreign key
> constraints.
> I am starting with SQL and I have no idea how to do that.
> Thanks,
> Miguel
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
>
the
[SurveyQuestion]
include
is
all
receiving
>

How to delete data older than X days, without considering time

When running the following SQL statements, I get the same results.
Though I need to count only -30 days. Both statements below also
consider the time of the day as well, which is not desired

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < GETDATE()- 30

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())

Here is the format of the values in column
MNT_R.TIMESTAMP
2005-08-09 06:06:44.577
2005-08-09 06:06:46.810
2005-08-09 06:06:49.060

So, since data are inserted into the MNT_R table every few seconds, my
delete statement will delete different number of rows, according to the
time of the day it runs.

Can you please post a SQL query that will not give me this headache?

thanx a lot allHi there,

You have to convert the source column to a non-using time format like
ISO:

DELETE FROM MNT_R
WHERE VARCHAR(10),MNT_R.TIMESTAMP < CONVERT(VARCHAR(10),GETDATE()-
30,112)

HTH, Jens Suessmeyer.|||nai (nioannides@.laiki.com) writes:
> When running the following SQL statements, I get the same results.
> Though I need to count only -30 days. Both statements below also
> consider the time of the day as well, which is not desired
>
> DELETE FROM MNT_R
> WHERE MNT_R.TIMESTAMP < GETDATE()- 30
> DELETE FROM MNT_R
> WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())
>
> Here is the format of the values in column
> MNT_R.TIMESTAMP
> 2005-08-09 06:06:44.577
> 2005-08-09 06:06:46.810
> 2005-08-09 06:06:49.060
> So, since data are inserted into the MNT_R table every few seconds, my
> delete statement will delete different number of rows, according to the
> time of the day it runs.
> Can you please post a SQL query that will not give me this headache?

Instead of getdate() used convert(char(8), getdate(), 112) to strip
of the time portion.

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

How to Delete ?

hi,

How to delete the following repeated data ?

my table :
Name Salary
--
Abc 20000
Abc 10000
CDE 01000
XYZ 12000
VID 30233
XYZ 40000

from the above table I want to delete repeated record 'Abc' and XYZ so that I should have the following records

Name Salary
--
Abc 20000
CDE 01000
XYZ 12000
VID 30233

Is it possible ?

Adv. thanks
bye
muralidharan T RYou need to be specific about which rows you wish to delete. If, for example, you want to delete all but the highest salary for each name:

delete from myTable
where Salary < (
select max(Salary) from myTable as T2
where T2.Salary > myTable.Salary
)

Steve Kass
Drew University
SQL Server MVP