About a RS version and previous conditions of use

I own a Windows Small Bussiness 2003 license which includes SQL server 2000
Standard Edition and additionally came with a version of Reporting Services.
I want to learn and use it (Reporting Services) as a beginner but when I
try to install it a message appears indicating that I need to install or
configure previously two products:
a) Visual Studio .Net 2003
b) IIS 5.0
Do I need both of 'em just to begin doing simple reports?
I supposed a simple use like I could obtain through Crystal reports 7.0 or
so on.
Please, help me.
Probably next year we will migrate to a new version of Microsoft SBS
Is it worth to do efforts with the versions I own nowadays or not?
Thanks alot in advance.
--
sanpetusRS 2000 report designer require some copy of VS 2003 to be installed. In the
past VB.net 2003 was the cheapest way to do this (about $100). I don't know
now. Note that the VB 2005 will not work for this.
In RS 2005 it comes with a version of VS 2005 so no additional purchase is
necessary.
RS is a asp.net application and as such it needs IIS. IIS comes with all
servers. It might need to configured though.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sanpetus" <sanpetus@.discussions.microsoft.com> wrote in message
news:14FE57B4-A059-4B0F-8539-C9D64CBAA6B6@.microsoft.com...
>I own a Windows Small Bussiness 2003 license which includes SQL server 2000
> Standard Edition and additionally came with a version of Reporting
> Services.
> I want to learn and use it (Reporting Services) as a beginner but when I
> try to install it a message appears indicating that I need to install or
> configure previously two products:
> a) Visual Studio .Net 2003
> b) IIS 5.0
> Do I need both of 'em just to begin doing simple reports?
> I supposed a simple use like I could obtain through Crystal reports 7.0 or
> so on.
> Please, help me.
> Probably next year we will migrate to a new version of Microsoft SBS
> Is it worth to do efforts with the versions I own nowadays or not?
> Thanks alot in advance.
> --
> sanpetus

Ability to Manually Enter a Parameter in a Queried Parameter

I have a queried parameter getting it's parameters from its own dataset.
Works great. Now they want to have the ability to enter their own if they
know what value they want rather than scrolling through the list.
Any ideas on how to go about this?
ThanksAnybody?
"BillD" wrote:
> I have a queried parameter getting it's parameters from its own dataset.
> Works great. Now they want to have the ability to enter their own if they
> know what value they want rather than scrolling through the list.
> Any ideas on how to go about this?
> Thanks
>|||Chris,
Thank You for taking the time to reply. Looks like I'll be getting into some
ASP as I also like to have a calendar for date parameters...
"Chris Baldwin" wrote:
> > "BillD" wrote:
> >
> >> I have a queried parameter getting it's parameters from its own
> >> dataset. Works great. Now they want to have the ability to enter
> >> their own if they know what value they want rather than scrolling
> >> through the list.
> Hello BillD,
> I'm not sure there's a way to do this when rendering through Report Manager.
> If there is I'd love to hear about it. But, I know you could easily create
> a custom webform that accomplishes this goal.
> For example, you can use JavaScript to determine whether the text box has
> been typed into and adjust the form action that requests the report accordingly.
> Here's a snippet that I used in one of my apps that might help:
> <script>
> function process()
> {
> var actionBase = "http://localhost/reportserver/myreport?rs:Command=Render&ParamVal=";
> var theForm = document.getElementById("myForm");
> var txtBox = document.getElementById("myBox");
> var selectBox = document.getElementById("mySelect");
> // If the textbox is empty, use the select box value
> if(txtBox.value == "")
> theForm.action = actionBase + selectBox.value;
> else
> // otherwise, use the text box value
> theForm.action = actionBase + txtBox.value;
> theForm.submit();
> }
> </script>
> <form method="get" id="myForm">
> <select id="mySelect">
> <!-- would be populated from database -->
> <option>Val1</option>
> <option>Val2</option>
> <option>Val3</option>
> </select>
> <input id="myBox" type="textbox">
> <input type="button" value="Submit" onClick="process();">
> </form>
> -chris
>
>sql

A Very Disturbing Finding about SQL Server Performance

After some testing, we have found that, when a client
computer is connected to an MS SQL server, and they are
listening to a music cd, in their own computer, the
performance of the SQL server degrades badly, until the
client stops listening to the CD. We have used
performance monitor to check this and are incredulous at
the findings. I have gone out on the web and, after some
exhaustive searching, have come up with nothing written
on the subject. Has anyone else experienced this
behavior? Has anyone else tested this? Does anyone have
an idea as to why this may be happening and how we can
insulate the performance of the SQL server from the
casual activities of the clients?
Thanks in advance for your replies.
John MangioneI listen to CDs as well as many of our developers while
connected to SQL server. We've never experienced what you
mention here.
>--Original Message--
>After some testing, we have found that, when a client
>computer is connected to an MS SQL server, and they are
>listening to a music cd, in their own computer, the
>performance of the SQL server degrades badly, until the
>client stops listening to the CD. We have used
>performance monitor to check this and are incredulous at
>the findings. I have gone out on the web and, after some
>exhaustive searching, have come up with nothing written
>on the subject. Has anyone else experienced this
>behavior? Has anyone else tested this? Does anyone have
>an idea as to why this may be happening and how we can
>insulate the performance of the SQL server from the
>casual activities of the clients?
>Thanks in advance for your replies.
>John Mangione
>.
>|||When the application is designed, there are many ways of
implementing database access.
If its done through cursors or VB Recordsets, this could
well be the case.
A (I won't say better) perhaps more efficient way is to
use SET commands.
E.g.
Issue the command to UPDATE directly to the server, rather
than locking the cursor.
Microsoft do recognise this as being more efficient.
Good Luck.
>--Original Message--
>If listening to the CD slows down the client machine
while it talks to the
>SQL server (SELECTs, INSERTs, UPDATEs, etc) then maybe
the application and
>the SQL code locks too many resources so other clients
have to wait and that
>might slow down the server.
>"John Mangione" <jmangione@.adelphia.com> wrote in message
>news:015601c36298$8032f4c0$a601280a@.phx.gbl...
>> After some testing, we have found that, when a client
>> computer is connected to an MS SQL server, and they are
>> listening to a music cd, in their own computer, the
>> performance of the SQL server degrades badly, until the
>> client stops listening to the CD. We have used
>> performance monitor to check this and are incredulous at
>> the findings. I have gone out on the web and, after some
>> exhaustive searching, have come up with nothing written
>> on the subject. Has anyone else experienced this
>> behavior? Has anyone else tested this? Does anyone have
>> an idea as to why this may be happening and how we can
>> insulate the performance of the SQL server from the
>> casual activities of the clients?
>> Thanks in advance for your replies.
>> John Mangione
>
>.
>|||Are you using Windows Media Player to listen to the CD's
IF so do you have one of the 3D visualizations running in the back ground
Why, these 3D graphics can eat up between 30-50% of your CPU.
I do not have any idea about the spec's of the machine they are running on
but if it is a desktop with SQL running on it, it can slow them down
significantly
Hope this helps|||This problem may very well expose design flaws in your application.
Regardless of what a user does on his/her machine SQL server must never be
slown down. You do not open a cursor at the client side and keep it open for
a log time, actually you are better off if you never use client side
cursors.
"John Mangione" <jmangione@.adelphia.net> wrote in message
news:016401c36361$a74aa390$a501280a@.phx.gbl...
> Well, I found out some additional information that has
> lowered my anxiety about the issue: the only people that
> are experiencing the degradation of service are those who
> are listening to the music, not any other users of that
> SQL server or the application on it.
> The indicator that threw us off is that the user's
> instance of the process running at the SQL server was
> spiking every time the music was playing on their
> machine. We are still at a loss to explain that, although
> Beverly's response might be closest to what's going on;
> that is, perhaps the use of recordset and cursor locks
> are creating interdepedencies on the two machines that
> could be avoided by using SET commands.
> Thank you all for your replies.
>
> >--Original Message--
> >After some testing, we have found that, when a client
> >computer is connected to an MS SQL server, and they are
> >listening to a music cd, in their own computer, the
> >performance of the SQL server degrades badly, until the
> >client stops listening to the CD. We have used
> >performance monitor to check this and are incredulous at
> >the findings. I have gone out on the web and, after some
> >exhaustive searching, have come up with nothing written
> >on the subject. Has anyone else experienced this
> >behavior? Has anyone else tested this? Does anyone have
> >an idea as to why this may be happening and how we can
> >insulate the performance of the SQL server from the
> >casual activities of the clients?
> >
> >Thanks in advance for your replies.
> >John Mangione
> >.
> >

a slight improvement to a great solution...

This is a great solution! I've modified it a bit to make it a little more
manageable though for my own use. I decided to make a version where the row
colors could be centrally managed since you have to copy the expression to
every cell in the row... and in some reports that can be a lot of cells...
this way you can define the color scheme in one place.. and you can also use
row level formatting. Here is how i did this.
Custom Code as follows:
Dim Public bgColor1 As String = "White"
Dim Public bgColor2 As String = "WhiteSmoke"
Dim Public bgColor As String = bgColor2
Public Function getBgColor(switch As Boolean) As String
If switch
If bgColor = bgColor1
bgColor = bgColor2
else
bgColor = bgColor1
end if
end if
return bgColor
End Function
Highlight the ROW and put in the following expression for BackgroundColor
property:
=Code.getBgColor(false)
Then all you have to do is go into the FIRST cell of the row and change it to:
=Code.getBgColor(true)
and walla works great (just like the original) with centralize management of
the row colors...
just a little change on a great solution...And what happens with concurrent users generating the same report?
Since bgColor is a public shared variable your code will run into problems.
Have a look at: http://odetocode.com/Articles/130.aspx
[...]
While shared methods are recommended, shared fields are definitely not. For
instance, the following code will have problems.
Public Shared Function AddToCount(ByVal Value As Integer) As String
Count = Count + value
End Function
Shared Count As Integer = 0
First, we have no control over the lifetime of the variable Count. Secondly,
if multiple users are executing the report with this code at the same time,
both reports will be changing the same Count field (that is why it is a
shared field). You don't want to debug these sorts of interactions - stick
to shared functions using only local variables (variables passed ByVal or
declared in the function body).
[...]
"thejez" <thejez@.discussions.microsoft.com> escribió en el mensaje
news:48D9ED94-AE6B-47C3-B1A9-C35DE05D4E08@.microsoft.com...
> This is a great solution! I've modified it a bit to make it a little more
> manageable though for my own use. I decided to make a version where the
> row
> colors could be centrally managed since you have to copy the expression to
> every cell in the row... and in some reports that can be a lot of cells...
> this way you can define the color scheme in one place.. and you can also
> use
> row level formatting. Here is how i did this.
> Custom Code as follows:
> Dim Public bgColor1 As String = "White"
> Dim Public bgColor2 As String = "WhiteSmoke"
> Dim Public bgColor As String = bgColor2
> Public Function getBgColor(switch As Boolean) As String
> If switch
> If bgColor = bgColor1
> bgColor = bgColor2
> else
> bgColor = bgColor1
> end if
> end if
> return bgColor
> End Function
> Highlight the ROW and put in the following expression for BackgroundColor
> property:
> =Code.getBgColor(false)
> Then all you have to do is go into the FIRST cell of the row and change it
> to:
> =Code.getBgColor(true)
> and walla works great (just like the original) with centralize management
> of
> the row colors...
> just a little change on a great solution...|||hrmmm this was supposed to be a reply to a previous post... and now i cant
even find that original post anymore (think it was posted originally on
5/27/05)...
anyway here the original post:
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>

A Server My Own!

Hello,

I have just been told That I can receive my own Server for Test purposes. I was ask what I wanted on the machine. I wanted to find out your thoughts on what I should asked for.

SQL Server 2000
Window 2003 (I may get 2000)

Should I get mail, I normally use SMTP server for mail.

I would love to here you suggestion or I forget within reason of course.

Thanksthe coleco vision smurf game. just kidding.

replicate your production server as closely as financially possible.

A rewrite of the sp_help_revlogin procedure (use at own risk)

Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).

I have altered the sp_help_revlogin (from Microsoft article # 246133 )

PLEASE TEST/FIX before you use this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_help_revlogin_2005]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_help_revlogin_2005 @.login_name sysname = NULL AS

DECLARE @.name sysname

DECLARE @.logintype char(1)

DECLARE @.logindisabled int

DECLARE @.binpwd varbinary (256)

DECLARE @.txtpwd sysname

DECLARE @.tmpstr varchar (256)

DECLARE @.SID_varbinary varbinary(85)

DECLARE @.SID_string varchar(256)

IF (@.login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name <> 'sa' and type in ('S','U','G')

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name = @.login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.logintype, @.logindisabled

IF (@.@.fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @.tmpstr = '/* sp_help_revlogin_2005 script '

PRINT @.tmpstr

SET @.tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'

PRINT @.tmpstr

PRINT ''

PRINT 'DECLARE @.pwd sysname'

WHILE (@.@.fetch_status <> -1)

BEGIN

IF (@.@.fetch_status <> -2)

BEGIN

PRINT ''

SET @.tmpstr = '-- Login: ' + @.name

PRINT @.tmpstr

IF (@.logintype = 'G' OR @.logintype = 'U')

BEGIN -- NT authenticated account/group

IF @.logindisabled = 1

BEGIN -- NT login is denied access

SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''

PRINT @.tmpstr

END

ELSE BEGIN -- NT login has access

SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''

PRINT @.tmpstr

END

END

ELSE IF (@.logintype = 'S')

BEGIN -- SQL Server authentication

SELECT @.binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @.SID_varbinary

IF (@.binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT

SET @.tmpstr = 'SET @.pwd = CONVERT (nvarchar(128), ' + @.txtpwd + ')'

PRINT @.tmpstr

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'EXEC master..sp_addlogin @.loginame = ''' + @.name

+ ''', @.passwd = @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = ''skip_encryption'''

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'EXEC master..sp_addlogin @.loginame = ''' + @.name

+ ''', @.passwd = NULL, @.sid = ' + @.SID_string

END

PRINT @.tmpstr

END

END

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.logintype, @.logindisabled

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

I've split this from the thread to which it was posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169513&SiteID=1), as it does not help with decrypting passwords. I've added a more suggestive title as well.

Thanks for your contribution.

Laurentiu

|||

Here's another rewrite of the sp_help_revlogin, which uses the new DDL and also outputs the password policy options:

http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx

Thanks
Laurentiu

a quick question about subscription

when I create a subscription through report manager it work ,but if I create
the subscription through my application (I'm using my own domain credentials
to authenticate to the server).It dosen't work.
Any idea?
ThanksHow does it fail? Does the create fail or the subscription fail when it
runs?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"J-R" <RayAll@.microsft.com> wrote in message
news:uLFwfi1VFHA.2128@.TK2MSFTNGP15.phx.gbl...
> when I create a subscription through report manager it work ,but if I
> create the subscription through my application (I'm using my own domain
> credentials to authenticate to the server).It dosen't work.
> Any idea?
> Thanks
>|||No it create the subscription,but it necer run the subscrition.My ASP.NET
application is in different box than Reporting Services and they both using
NTLM.
Thanks
"Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
news:uAt65%232VFHA.1452@.TK2MSFTNGP14.phx.gbl...
> How does it fail? Does the create fail or the subscription fail when it
> runs?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "J-R" <RayAll@.microsft.com> wrote in message
> news:uLFwfi1VFHA.2128@.TK2MSFTNGP15.phx.gbl...
>> when I create a subscription through report manager it work ,but if I
>> create the subscription through my application (I'm using my own domain
>> credentials to authenticate to the server).It dosen't work.
>> Any idea?
>> Thanks
>|||Is there any status shown after the subscription fires? After you create
the subscription through your app, can you view it through Report Manager?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"J-R" <RayAll@.microsft.com> wrote in message
news:OtgYYP9VFHA.2960@.TK2MSFTNGP15.phx.gbl...
> No it create the subscription,but it necer run the subscrition.My ASP.NET
> application is in different box than Reporting Services and they both
> using NTLM.
> Thanks
> "Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
> news:uAt65%232VFHA.1452@.TK2MSFTNGP14.phx.gbl...
>> How does it fail? Does the create fail or the subscription fail when it
>> runs?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "J-R" <RayAll@.microsft.com> wrote in message
>> news:uLFwfi1VFHA.2128@.TK2MSFTNGP15.phx.gbl...
>> when I create a subscription through report manager it work ,but if I
>> create the subscription through my application (I'm using my own domain
>> credentials to authenticate to the server).It dosen't work.
>> Any idea?
>> Thanks
>>
>|||I think I solved the problem,it was the problem of the user account we
create the suscription uder.That guy did not have enough previlliage to
execute a subscription.
Thanks for your help
"Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
news:%230vs3rDWFHA.2124@.TK2MSFTNGP14.phx.gbl...
> Is there any status shown after the subscription fires? After you create
> the subscription through your app, can you view it through Report Manager?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "J-R" <RayAll@.microsft.com> wrote in message
> news:OtgYYP9VFHA.2960@.TK2MSFTNGP15.phx.gbl...
>> No it create the subscription,but it necer run the subscrition.My ASP.NET
>> application is in different box than Reporting Services and they both
>> using NTLM.
>> Thanks
>> "Daniel Reib (MSFT)" <danreib@.online.microsoft.com> wrote in message
>> news:uAt65%232VFHA.1452@.TK2MSFTNGP14.phx.gbl...
>> How does it fail? Does the create fail or the subscription fail when it
>> runs?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "J-R" <RayAll@.microsft.com> wrote in message
>> news:uLFwfi1VFHA.2128@.TK2MSFTNGP15.phx.gbl...
>> when I create a subscription through report manager it work ,but if I
>> create the subscription through my application (I'm using my own domain
>> credentials to authenticate to the server).It dosen't work.
>> Any idea?
>> Thanks
>>
>>
>

A question about SQL queries

Ok, I'm sorry if this has already been answered. I didn't know what to look for (or I would've solved my problem on my own)

So I have two tables, one called"users" and the other one called"posts". I'm retrieving everything from the posts-table and the userid and username from the users-table. The thing I can't get working is that I ONLY want toretrieve the username for thespecific userid that made the post. I'm sorry for the lack of explanation and the lack of code (since I actually don't know what to do I don't have any code) and the fact that english isn't my native language.

Thanks in advance, if there's anyone that understands me

standard SQL statement for this problem would be:

assume table user has following columns : userid,username table post has following column : userid, postreply

select a.userid,a.username,b.postreply

from post b,user a

where b.userid=a.userid

now you have to adapt the statement for you flavor of database.

if you use the sql query editor to attach to your database type, it should generate the correct style for your command

to ensure that you get all records from one table even if there is no matching record in the other table then you start playing with inner joins and outter joins.

|||

Fair enough :D I will try that next week, going away for the weekend but I will get back to you if this worked out for me :)

EDIT: Just wanted to say I worked out fine for me :)

"SELECT users.userid, users.username, posts.postid, posts.userid, posts.message FROM users, posts WHERE users.userid = posts.userid"

a query using GROUP BY or?

Is there a simple way to do the following?
The database table has many records, each record has its own unique RecordID
(PK, int), some of the records can have one text field like an intenrifier
(SomeID) with the same value. A simplified schema is looking like this:
RecordID SomeID Action
1 134 2
2 123 2
3 1243 2
4 134 1
5 1ytr 2
6 1fgh 2
7 1243 1
8 hgf 2
9 b4rfg 2
I need to assign the value '1' or '2' to the Action field so that if we
order the whole list by the RecordID and then group it by the SomeID field,
the first record in each group (with the same SomeID field) should have
Action=2, all next entries inside each group should have Action=1. All
records without duplicates should have Action=2.
I can set Action=2 to all records, it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records?
Thanks,
Just D.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
CREATE TABLE Foobar
(foo_id INTEGER NOT NULL PRIMARY KEY,
grp_id CHAR(5) NOT NULL,
action_code INTEGER DEFAULT 2 NOT NULL
CHECK (action_code IN (1,2)));
Action=2, all next entries inside each group should have Action=1. All
records [sic] without duplicates should have Action=2. I can set
Action=2 to all records [sic], it's fast and easy. How can I assign '1'
to all appropriate (duplicate) records [sic] ? <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. A normalized table should not have redundant duplicates.
I am going to guess that this is what you want:
UPDATE Foobar
SET action_code
= CASE WHEN foo_id
< (SELECT MAX(foo_id)
FROM Foobar AS F1
WHERE F1.grp_id = Foobar.grp_id)
THEN 1 ELSE 2 END;|||Try this one:
update tbl set
Action =
case when RecordID = (select min(RecordID) from tbl as t where
t.SomeID = tb.SomeID)
then 2
else 1
end|||HI,
Excellent! That's a very good idea! Thanks!
Just D.
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:1127474970.840245.308070@.g44g2000cwa.googlegroups.com...
> Try this one:
> update tbl set
> Action =
> case when RecordID = (select min(RecordID) from tbl as t where
> t.SomeID = tb.SomeID)
> then 2
> else 1
> end
>