Increase one month

Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
MichaelTry with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> > Hi,
> > I have a date value of 4/1/2005, I wanted to increase just one month
> > to 5/1/2005. I have millions of records like that to increase just one
> > month. Is there a function I can use?
> > Thanks a lot!!
> > Michael
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
thanks a lot all!!!

Increase one month

Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
Michael
Try with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegr oups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegr oups.com...
>
>
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
thanks a lot all!!!

Increase one month

Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
Michael"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
>
>
>
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,
SQL.90).aspx
> --
thanks a lot all!!!

increase aggregations (design storage) programmatically (was "Please help thank

Fact table gets update every day with thousands of records, I been increase the aggregations manually. Is there a way I can increase aggregations (design storage) programmatically . My cub gets process ones in day with VB program. I want to increase aggregations before processing cub through VB.

It's argent

please help me

thanksCan you do a full process? If you're not running into time constraints, then I'd do a full process.|||As fact table's records increases, dont I have to increase the aggregation in the cub?|||No. Aggregations are dimension related, so as long as your not creating new dimensions then you don't have to worry about increasing aggregations. However, if processing time is not an issue, I'd do a full process.|||I am confused according to your statement
When I create cub with single records fact table the aggregations are zero.
When I create cub with thousands of records in fact table the aggregations are 850 or more.|||Here's what I'm trying to say:

The theoretical maximum number of possible aggregations in a cube is the product of the number of levels in each cube dimension. As you add levels and dimensions to a cube, the number of possible aggregations increases exponentially. The higher the number of dimensions and levels in a cube, the greater its complexity. In the example in Figure 2, the Time dimension has four levels, the Customers dimension has five levels, and the Products dimension has five levels. This yields a theoretical maximum number of aggregations of 100 (5 customer levels x 5 product levels x 4 time levels). However, this number increases exponentially as you add dimensions or levels. For example, if you add the Day level to the Time dimension, the theoretical maximum number of aggregations increases to 125 (5 x 5 x 5). Now, suppose you add two more dimensions to this cube, each with three levels. The theoretical maximum number of aggregations increases to 1125 (5 x 5 x 5 x 3 x 3). A cube with nine dimensions containing five levels each yields theoretical maximum number of aggregations of 1,953,125. A cube of this complexity is considered a cube of medium complexity. A cube of high complexity might have 20 dimensions with five levels each and yield a theoretical maximum number of aggregations of approximately 95 trillion. As you can see, you can directly affect the theoretical maximum number of aggregations in a cube by changing the number of dimensions or the number of levels. Having multiple dimensions with deep hierarchies improves the ability of users to perform analysis, but having too many of either can lead to resource problems during querying and processing.

Follow this link for more info...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ansvcspg.mspx|||Yes I understand that

What I did is, my fact table was MT (no records) I added one dummy record and created cub out of it, basically dimensions contains single level. I have 40 dimensions and 35 measures. I created a script out of it and sending the script to user to create the cub in their analysis server. In the script I had prompt for users data source name. Users fact table contains millions of records. When user processes the cub with millions of records the dimensions will have multiple levels. So user has to recreate the aggregations?

Sorry, I know you are trying to clarify my doubts but I am still confused.

Thank you so much.|||I think I remember now. You're sharing an identical cube structure with someone, but you're each pointing to different data sources, correct?

If that is the case, the user would have to re-process. Especially since the dimensions will have multiple levels once your script has run against their data. With the numbers you've provided, I wouldn't do a full process. However, again, aggregations are based on dimensions, so you don't need to programatically increase the number of aggregations as new records are added to the fact. Even if new dimension levels are being created, I wouldn't increase the number of aggregations. Just select a particular "performance gain level" and let AS do the rest (i.e. 30%). Having said that, you should probably look into usage based optimization. I have a similar sized cube as you've described and I know that there are a lot of wasted aggregations in the cube (fully processed). It only gets updated once a month, so it's not a big deal to do a full process. Remember, as your cube gets more complex, it's unlikely that your users are making the most of that complexity. That's why usage base optimization makes sense. I haven't done usage based optimization "programatically", so I'm no help there.

I hope that makes sense.|||Thanks so much

User doesnt know about analysis server.
I want to provide user to click option to design storage.

After crating the cub on user server,

I am looking for VB code to
1. Count dimension members
2. Design storage

So user doesnt have to do it manually .sql

Incorrect syntax near the keyword ELSE.

Hi,

I have written a stored procedure to add the records to the table in DB from the report I generate, but the sored procedure gives me this error:

Incorrect syntax near the keyword 'ELSE'.

I am using Sql Server 2005.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
DECLARE @.PKID INT
DECLARE @.TagID INT

SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode

SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
END

DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT

SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens=@.UniqueUserOpens-UniqueUserOpens,@.UniqueUserMessageClicks=@.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks=@.UniqueUserLinkClicks-UniqueUserLinkClicks,@.TotalLinkClicks=@.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes=@.Unsubscribes-Unsubscribes,@.Bounces=@.Bounces-Bounces,@.SpamComplaints=@.SpamComplaints-SpamComplaints,
@.Cost=@.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

ELSE
SET NOCOUNT ON

INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)

SELECT
@.ReportDate,
@.SiteID,
@.PKID,
@.Sent,
@.Delivered,
@.TotalOpens,
@.UniqueUserOpens,
@.UniqueUserMessageClicks,
@.UniqueUserLinkClicks,
@.TotalLinkClicks,
@.Unsubscribes,
@.Bounces,
@.SpamComplaints,
@.Cost,
@.DataFeedID,
@.TagID

SET NOCOUNT OFF

Hi,

I think you find that the End Statement must be immediately before the Else (IF... BEGIN...END ELSE). Certainly if you move the END to just before the ELSE the SP compiles OK.

Hope this helps,

Paul

|||

How could I miss that.

Thanks a lot!!!

|||

No problems, sometimes these things just need a fresh pair of eyes.

Cheers

|||Try
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
SET NOCOUNT ON -- moved this
DECLARE @.PKID INT
DECLARE @.TagID INT
SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode
SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID
DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT
SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens = @.UniqueUserOpens-UniqueUserOpens,
@.UniqueUserMessageClicks = @.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks = @.UniqueUserLinkClicks-UniqueUserLinkClicks,
@.TotalLinkClicks = @.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes = @.Unsubscribes-Unsubscribes,
@.Bounces = @.Bounces-Bounces,
@.SpamComplaints = @.SpamComplaints-SpamComplaints,
@.Cost = @.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost
WHERE ReportDate=@.ReportDate AND SiteID=@.SiteID AND TagPKID=@.PKID
END
ELSE
INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,
UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)
VALUES ( -- Should be values
@.ReportDate, @.SiteID, @.PKID, @.Sent, @.Delivered, @.TotalOpens, @.UniqueUserOpens,
@.UniqueUserMessageClicks, @.UniqueUserLinkClicks, @.TotalLinkClicks, @.Unsubscribes,
@.Bounces, @.SpamComplaints, @.Cost, @.DataFeedID, @.TagID)
SET NOCOUNT OFF|||You have an ELSE with no matching IF.

Incorrect Identity Range

I'm trying to replicate a contacts table that has about
80000 records in it. When I set an identity range of 1000
but when the publication is created it keeps setting the
next identity range to start at 49000. This creates an
error that means that data cannot be entered either at the
publishing end or the subscribing end.
I did previously have this table replicating to two
subscribers and this is when the error first occurred. I
removed the subscriptions and then the publication and
tried to clean up any meta data that may have remained. I
then redid the publication but had the same problem.
is the datatype for the identity column int or smallint, or something else
altogether?
What happens if you script out your publication and make the changes to the
underlying script and deploy it?
Also do the underlying tables have any check constraints remaining on them
for the identity column? These are not removed when you recreate a nosync
subscription.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mburns" <anonymous@.discussions.microsoft.com> wrote in message
news:814f01c484dc$23bfcb20$a401280a@.phx.gbl...
> I'm trying to replicate a contacts table that has about
> 80000 records in it. When I set an identity range of 1000
> but when the publication is created it keeps setting the
> next identity range to start at 49000. This creates an
> error that means that data cannot be entered either at the
> publishing end or the subscribing end.
> I did previously have this table replicating to two
> subscribers and this is when the error first occurred. I
> removed the subscriptions and then the publication and
> tried to clean up any meta data that may have remained. I
> then redid the publication but had the same problem.
|||Thanks for your reply Hilary. My answers to your questions:
The datatype for the identity column is int.
I haven't tried scripting it. I did look for a script but
I couldn't see where to define the starting identity
range. Could you point me somewhere?
I couldn't find any constraints in the underlying tables.
I am replicating this table by itself (own publication) as
it is so large and replicating the other tables from this
db in a separate publication. Is this a problem?
Thanks
>--Original Message--
>is the datatype for the identity column int or smallint,
or something else
>altogether?
>What happens if you script out your publication and make
the changes to the
>underlying script and deploy it?
>Also do the underlying tables have any check constraints
remaining on them
>for the identity column? These are not removed when you
recreate a nosync
>subscription.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Mburns" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:814f01c484dc$23bfcb20$a401280a@.phx.gbl...
1000[vbcol=seagreen]
the[vbcol=seagreen]
I
>
>.
>

Inconsistent sp_spaceused

Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken space.
"Tibor Karaszi" wrote:

> Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
> news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
>
>
Leon Shargorodsky,
if you dropped a variable length column, you can reclaim the space using
"dbcc cleantable".
If it is a heap (table without clustered index), create a clustered index
and if you do not want to keep it then drop it.
If it is not a heap, use "dbcc dbreindex" or "alter index ... rebuild" if
you are using 2005.
AMB
"Leon Shargorodsky" wrote:
[vbcol=seagreen]
> Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken space.
> "Tibor Karaszi" wrote:

Include ID field in GROUP BY statement

I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.

The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':

ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83

And run the query:

select max(Cost),FruitName From Fruits
group by FruitName

It'll correctly return:

FruitName Cost
Apple 0.63
Pear 0.89

Now i need the ID also returned by my query so i go:

select max(Cost),FruitName,ID From Fruits
group by FruitName,ID

This doesnt return the above results with the ID appended to it, it instead returns:

ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83

As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:

ID FruitName Cost
2 Apple 0.63
4 Pear 0.89

Thanks.

I don't think you can do that with the ID field because there are multiple Ids for each item while using the group by. Can you use a Having clause? ie

select Cost,FruitName From Fruits
group by FruitName
having max(Cost)

This is a shot in the dark as i did not actually try your data. Hope it works though :)

|||

Thanks but dont think that would work as the 'HAVING' as i see it is just used to filter the results of the query so you'd use it if for example you wanted to only show max fuit more than ￡0.10

select Cost,FruitName From Fruit
group by FruitName,Cost
having max(Cost) > 0.1

Thats how i see it (could be wrong). But it still doesnt handle the problem of the ID.

|||

Hi,

This is a round about way, but it works

Select

FruitName,cost,(Select IDfrom Fruits fWhere f.Cost=frt.cost)as IDfrom

(

select FruitName,max(cost)as costFrom Fruits

group

by FruitName) frt|||

Hi TheGrox,

The below query may help you.

select id,fruitname,costfrom (select row_number()over (partitionby fruitnameorder by costdesc)as rownum,id,fruitname,costfrom Fruits) Fwhere rownum = 1

The main trick here is using the ROW_NUMBER() OVER (PARTITION BY...) clause

This is very like getting top n records from a table belonging to each category. You can follow the article athttp://www.kodyaz.com/articles/top-n-random-rows-foreach-category-or-group.aspx for more details and a sample for ROW_NUMBER() OVER (PARTITION BY...)

Eralper

http://www.kodyaz.com

|||

If you are using SQL 2000 you can try this:

Select t1.FruitName,t2.cost, Idfrom Fruits t1join (select FruitName,max(cost)as costFrom Fruitsgroup by FruitName ) t2on t1.FruitName = t2.fruitnameand t1.Cost = t2.cost

|||

Here is another one:

select t1.*from fruitsas t1where t1.ID= (select top 1 t2.IDfrom fruitsas t2where t2.FruitName = t1.FruitNameorder by t2.costDESC)

|||

Limno

I like your query better than mine. I am joining on name and cost. If there are multiple records with same name and costs, the join could return multiple records for same "Fruit" which might be incorrect.

|||I learned that solution from Umachandar Jayachandran. He is super.

Inactive record

Hi just wondering if there is a way to have one record in a table be read
only, were an application can not remove it and have all other records act
normally? I am currently handling this in .net code but would prefer to
have it built into the table.
thanks.
--
Paul G
Software engineer.No there is now row level security based on SQL Server 2000.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
news:BD1A992D-4723-48EA-82C1-7CF94A102258@.microsoft.com...
> Hi just wondering if there is a way to have one record in a table be read
> only, were an application can not remove it and have all other records act
> normally? I am currently handling this in .net code but would prefer to
> have it built into the table.
> thanks.
> --
> Paul G
> Software engineer.|||Sorry, i mean : No there is no row level security based on SQL Server 2000.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:eNTxiwzSFHA.612@.TK2MSFTNGP12.phx.gbl...
> No there is now row level security based on SQL Server 2000.
> HTH, Jens Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
> "Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BD1A992D-4723-48EA-82C1-7CF94A102258@.microsoft.com...
>|||ok thanks for the information.
"Jens Sü?meyer" wrote:

> No there is now row level security based on SQL Server 2000.
> HTH, Jens Suessmeyer.
>
> --
> http://www.sqlserver2005.de
> --
> "Paul" <Paul@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:BD1A992D-4723-48EA-82C1-7CF94A102258@.microsoft.com...
>
>|||On Wed, 27 Apr 2005 07:54:06 -0700, Paul wrote:

>Hi just wondering if there is a way to have one record in a table be read
>only, were an application can not remove it and have all other records act
>normally? I am currently handling this in .net code but would prefer to
>have it built into the table.
>thanks.
Hi Paul,
You can use a trigger:
CREATE TRIGGER DontTouchThisRow
ON MyTable AFTER UPDATE, DELETE
AS
IF EXISTS (SELECT *
FROM deleted
WHERE KeyCol = 1) -- Column to be protected
BEGIN
RAISERROR ('Row 1 may not be changed or removed', 16, 1)
ROLLBACK TRANSACTION
END
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

IN() operator problem

i have a list of ID's (indexed) more than 2500 items.
first i have to select these records from one and then Update in
another table. when i pass the list to sql with IN() operator in
select query it takes too long (about 2 mins) and when i run update
query with same criteria it takes too much time.
i am using VB6 for development.
the field is indexed.

what i should do to improve performance?

Khurram Rao[posted and mailed, please reply in news]

Khurram (khurramanis@.gmail.com) writes:
> i have a list of ID's (indexed) more than 2500 items.
> first i have to select these records from one and then Update in
> another table. when i pass the list to sql with IN() operator in
> select query it takes too long (about 2 mins) and when i run update
> query with same criteria it takes too much time.
> i am using VB6 for development.
> the field is indexed.

Yes, the IN operator with many elements performs badly.

This article discusses different alternatives:
http://www.sommarskog.se/arrays-in-sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I have a list of ID's (indexed) more than 2500 items. First I have
to select these records [sic] from one and then Update in another
table. When I pass the list to SQL with IN() operator in select query
it takes too long (about 2 mins) and when I run update query with same
criteria it takes too much time.

Rows are not like records at all. Please post your code and DDL.
You are describing procedural steps, not a relational approach.|||um - create a temp table with your list, and join to it in the update
statement?

"Khurram" <khurramanis@.gmail.com> wrote in message
news:60ff3d6c.0412040236.6e0466ea@.posting.google.c om...
>i have a list of ID's (indexed) more than 2500 items.
> first i have to select these records from one and then Update in
> another table. when i pass the list to sql with IN() operator in
> select query it takes too long (about 2 mins) and when i run update
> query with same criteria it takes too much time.
> i am using VB6 for development.
> the field is indexed.
> what i should do to improve performance?
> Khurram Rao

In SQL Server,I need to pull the records that have improper international phone number of format

Hi can you please help me to come up with a solution where i can pull records that have improper international telephone number format. like for example: These are set of records that have length 16 characters.

TelNumber.

091-3 4-43 -5678 ->here including numbers it even counts the spaces ,'-' between numbers and gives length as 16.

509--66-4 3-8887

670- 67--077-546.

908-898-654-3421 ->only 4th and 5th records are valid records that match the standard format we follow:

972-567-553-7689 ccc-aaa-nnn-nnnn where ccc =country code;aaa =area code;nnn-nnnn =phone number

I need a query that can pull first 3 record types...

thanks,

Comalkatar

I think that this will work for you:

Code Snippet

DECLARE @.Phones table
( TelNumber varchar(20) )

SET NOCOUNT ON

INSERT INTO @.Phones VALUES ( '091-3 4-43 -5678' )
INSERT INTO @.Phones VALUES ( '509--66-4 3-8887' )
INSERT INTO @.Phones VALUES ( '670- 67--077-546.' )
INSERT INTO @.Phones VALUES ( '908-898-654-3421' )
INSERT INTO @.Phones VALUES ( '972-567-553-7689' )

SELECT TelNumber
FROM @.Phones
WHERE TelNumber NOT LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

TelNumber
--
091-3 4-43 -5678
509--66-4 3-8887
670- 67--077-546.

|||

Here You go...(using Regular Expression)

Code Snippet

Create Table #telephones (

TelNumber Varchar(100)

);

Insert Into #telephones Values('091-3 4-43 -5678');

Insert Into #telephones Values('509--66-4 3-8887');

Insert Into #telephones Values('70- 67--077-546.');

Insert Into #telephones Values('908-898-654-3421');

Insert Into #telephones Values('972-567-553-7689');

Select

*

From

#telephones

Where

TelNumber NOT Like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'

In SQL Server XYZ & xyz are treated as same value

Dear All,

In ORACLE, the values 'XYZ' and 'xyz' are treated as different values and I can enter two records with the primary key field having values 'XYZ' and 'xyz'.

But In SQL Server, 'XYZ' and 'xyz' or 'xYz' or 'Xyz' etc are treated as same value. So, when I try to insert 'xyz' after inserting a record with 'XYZ' as value of the primary key field, it gives me the duplicate record error. How to solve this problem?
I want it to work the SQL Server same as Oracle according to our requirement.

Can any one of you Please help me out in this regard?

Regards,
Sat.oracle is case sensitive, by default sql is not...
you can either change your whole installation to be case sensitive
or just make that primary key column case sensitive by running the command below...

alter table tablename
alter column columnname datatype
collate SQL_Latin1_General_Cp1_CS_AS