FK Constraint question

I have two tables
GroupUsers and Alias

GroupUsers
-GroupId
-AliasId
-UserId

Alias
-AliasId
-UserId

When a user joins a Group he selects a default Alias for his group, so I have put a foreign key constraint from the groupusers table to the alias table.

Now, if an alias is delete, is there any way through a foreign key constraint to set the value to null because all I've seen is Cascade and No Action

A better solution I'm looking for would be a way to run a script that would use the value in the UserId column from the GroupUsers table to select the top alias from the Alias table with a corresponding UserId (if exists) and set it to that. I'm unsure as to if this solution is even possible.
SQL Server doesn't support the SQL standard to set a FK to NULL, you would need to implement this, or your other solution, using a DELETE trigger on the Alias table.|||We support the SET DEFAULT and SET NULL options in SQL Server 2005. So for now, you will have to implement the logic using SPs or trigger code.

FK and replication

(previously posted to another SQL group; no answer so far)
We are looking into how to do replication on a SQL Server 2000 db, for
disaster recovery. One thing we found was that FK contraints won't replicate
well. So we decided to drop the FK constraint, then add it back with a bit
of code that says it is not for replication.
We got this idea from someone who is using DB2 or Oracle (I can't remember
which). Will this work on SQL Server as well?
Here's my code (with some table and field names replaced for this post):
ALTER TABLE tablename DROP CONSTRAINT FKwhatever
ALTER TABLE tablename
ADD CONSTRAINT FKwhatever
FOREIGN KEY (REGIONID)
REFERENCES whatever NOT FOR REPLICATION
The article properties, snapshot tab gives you the option to take DRI. This only works if the related articles are in the same publication. If you use this, there will be no need to run other scripts. If you don't use this, then you could do the same thin
g in postscripts or sp_addscriptexec. "Yes, Not For Replication" has specific uses, and you may or may not use it for DR (I wouldn't as then it's fewer things to change afterwards). Note that there are many differences to log shipping which is a truer DR
technology: you'll loose defaults, users, permissions and user defined datatypes. All these will need taking into account.
Regards,
Paul Ibison
|||Thanks, but to be honest, I didn't understand much of what you said. Do you
have a link to some basic tutorial on this? I am not a DBA, BTW.
I should add that I just found out that the customer who's doing this is
doing it in real time.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:D89471D5-134D-4DBD-84F7-B0EE73400E8C@.microsoft.com...
> The article properties, snapshot tab gives you the option to take DRI.
This only works if the related articles are in the same publication. If you
use this, there will be no need to run other scripts. If you don't use this,
then you could do the same thing in postscripts or sp_addscriptexec. "Yes,
Not For Replication" has specific uses, and you may or may not use it for DR
(I wouldn't as then it's fewer things to change afterwards). Note that there
are many differences to log shipping which is a truer DR technology: you'll
loose defaults, users, permissions and user defined datatypes. All these
will need taking into account.
> Regards,
> Paul Ibison
|||The Not for Replication (NFR) option is well explained in Books Online
(BOL), as well as the whole of replication stuff.
Briefly:
1. If you have a DRI, you can't insert a FK row where the PK row does not
exist yet.
2. The NFR option means that the DRI is not enforced for replication only.
In other words, if Foreign Key data goes to susbscriber before the Primary
Key, then the FK data is allow to be inserted at the subscriber, even though
the PK row is not there yet. If you do not specify this option then you end
up with a conflict.
If you want your DRIs to be replicated, you will have to specify it in the
article properties. Again, please have a look in BOL.
Raj Moloye
|||What's DRI?
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:u8TOJRcEEHA.3568@.tk2msftngp13.phx.gbl...
> The Not for Replication (NFR) option is well explained in Books Online
> (BOL), as well as the whole of replication stuff.
> Briefly:
> 1. If you have a DRI, you can't insert a FK row where the PK row does not
> exist yet.
> 2. The NFR option means that the DRI is not enforced for replication only.
> In other words, if Foreign Key data goes to susbscriber before the Primary
> Key, then the FK data is allow to be inserted at the subscriber, even
though
> the PK row is not there yet. If you do not specify this option then you
end
> up with a conflict.
> If you want your DRIs to be replicated, you will have to specify it in the
> article properties. Again, please have a look in BOL.
> Raj Moloye
>
|||Middletree,
there's loads of good info in Books On Line (BOL). Look for the "replication, overview" section.
As for tutorials, have a look at the links in www.replicationanswers.com. At the top I've listed some tutorials I've come across. There are no specialist replication books as yet, but you'll find simple tutorials in most Administration books.
Ask as many questions on this newsgroup as you like - understanding this technology is important to making the correct business choice and there's plenty of experienced people willing to help.
Regards,
Paul Ibison
ps you can't get real-time synchronization, but using transactional replication you'll get pretty close to it, if you minimise the POLLINGINTERVAL parameter (perhaps add another post about optimization later).
|||Sorry, I was a bit lazy to write the longer definition.
DRI=Data Referential Integrity (this is the equivalent for FK Constraint)
Raj Moloye
"middletree" <middletree@.htomail.com> wrote in message
news:ea0UQTcEEHA.2576@.TK2MSFTNGP11.phx.gbl...
What's DRI?
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:u8TOJRcEEHA.3568@.tk2msftngp13.phx.gbl...
> The Not for Replication (NFR) option is well explained in Books Online
> (BOL), as well as the whole of replication stuff.
> Briefly:
> 1. If you have a DRI, you can't insert a FK row where the PK row does not
> exist yet.
> 2. The NFR option means that the DRI is not enforced for replication only.
> In other words, if Foreign Key data goes to susbscriber before the Primary
> Key, then the FK data is allow to be inserted at the subscriber, even
though
> the PK row is not there yet. If you do not specify this option then you
end
> up with a conflict.
> If you want your DRIs to be replicated, you will have to specify it in the
> article properties. Again, please have a look in BOL.
> Raj Moloye
>
|||thanks!
"Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
news:OoZe$YcEEHA.3672@.TK2MSFTNGP09.phx.gbl...
> Sorry, I was a bit lazy to write the longer definition.
> DRI=Data Referential Integrity (this is the equivalent for FK Constraint)
> Raj Moloye
>
> "middletree" <middletree@.htomail.com> wrote in message
> news:ea0UQTcEEHA.2576@.TK2MSFTNGP11.phx.gbl...
> What's DRI?
>
> "Raj Moloye" <rkmoloye@.hotmail.com> wrote in message
> news:u8TOJRcEEHA.3568@.tk2msftngp13.phx.gbl...
not
only.
Primary
> though
> end
the
>
>
|||This looks like a great resource; Thanks!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:15C068AB-E0A3-4DD1-A663-D8CB65EF0AC9@.microsoft.com...
> Middletree,
> there's loads of good info in Books On Line (BOL). Look for the
"replication, overview" section.
> As for tutorials, have a look at the links in www.replicationanswers.com.
At the top I've listed some tutorials I've come across. There are no
specialist replication books as yet, but you'll find simple tutorials in
most Administration books.
> Ask as many questions on this newsgroup as you like - understanding this
technology is important to making the correct business choice and there's
plenty of experienced people willing to help.
> Regards,
> Paul Ibison
> ps you can't get real-time synchronization, but using transactional
replication you'll get pretty close to it, if you minimise the
POLLINGINTERVAL parameter (perhaps add another post about optimization
later).
>

Fixing Table Headers

Hi,
the problem in one of our reports is, that there are a lot of detail lines
for each group, so if you scroll down to see them, the table header is not
visible. Our customers are complaining about that, because they have to
scroll up and down to see to which field the detail belongs.
Moreover it is not possible to split the table, because all details are a
logical group.
My question: Is it possible to fix a table header like the fixing function
in excel, so it can always be seen when you scroll down the details?
Thanks,
BjörnTable headers can be repeated on each page, but you can not do the excel
like thing... You can make your page smaller, but that's about it I think.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Björn Grünberg" <BjrnGrnberg@.discussions.microsoft.com> wrote in message
news:DE7380C6-C5A0-47C0-9C0E-F267976BCC4A@.microsoft.com...
> Hi,
> the problem in one of our reports is, that there are a lot of detail lines
> for each group, so if you scroll down to see them, the table header is not
> visible. Our customers are complaining about that, because they have to
> scroll up and down to see to which field the detail belongs.
> Moreover it is not possible to split the table, because all details are a
> logical group.
> My question: Is it possible to fix a table header like the fixing function
> in excel, so it can always be seen when you scroll down the details?
> Thanks,
> Björn|||Wayne is right, Reporting Services 2000 does not this particular feature.
Look for this in Reporting Services 2005 (shipping as part of Yukon),
though.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhCnpeBRFHA.3020@.TK2MSFTNGP12.phx.gbl...
> Table headers can be repeated on each page, but you can not do the excel
> like thing... You can make your page smaller, but that's about it I think.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Björn Grünberg" <BjrnGrnberg@.discussions.microsoft.com> wrote in message
> news:DE7380C6-C5A0-47C0-9C0E-F267976BCC4A@.microsoft.com...
>> Hi,
>> the problem in one of our reports is, that there are a lot of detail
>> lines
>> for each group, so if you scroll down to see them, the table header is
>> not
>> visible. Our customers are complaining about that, because they have to
>> scroll up and down to see to which field the detail belongs.
>> Moreover it is not possible to split the table, because all details are a
>> logical group.
>> My question: Is it possible to fix a table header like the fixing
>> function
>> in excel, so it can always be seen when you scroll down the details?
>> Thanks,
>> Björn
>|||On Mon, 18 Apr 2005 03:57:19 -0700, "Björn Grünberg"
<BjrnGrnberg@.discussions.microsoft.com> wrote:
>Hi,
>the problem in one of our reports is, that there are a lot of detail lines
>for each group, so if you scroll down to see them, the table header is not
>visible. Our customers are complaining about that, because they have to
>scroll up and down to see to which field the detail belongs.
>Moreover it is not possible to split the table, because all details are a
>logical group.
>My question: Is it possible to fix a table header like the fixing function
>in excel, so it can always be seen when you scroll down the details?
>Thanks,
>Björn
Bjorn,
It's a little bit of a kludge but why not add a table footer and
simply repeat the information that is in the table header? That should
make the relevant information visible in a larger part of the page.
Andrew Watt
MVP - InfoPath

Fixed number of Detail Lines

I have to create a report that will always show 12 detail lines per group, no matter how many records are in the group. For example, if there are only 8 records in the group I need to print 4 blank detail lines. I have it printing and breaking on the group just fine. How Do I get it to force the needed blank lines?
There will never be more than 12 per group.
I already have the report doing the new page on new group.
At this point the report works other than filling in missing detail with blank lines.
The report is a class roster which shows the students that have signed up for the class in a grid type layout. I just need the extra blank detail lines as a nice formating step. Some classes only have 4-5 students some have a full enrollment of 12Place a formula in the group footer to print 'n' blank lines.
e.g.

whileprintingrecords;
local numbervar lines := 12 - count({table.field}, {table.group});
local stringvar text;

while lines > 0 do
(
lines := lines - 1;
text := text & chr(13); //you may need chr(10) too / instead.
);

text

Tick the 'Can Grow' property of the object (right click, format field, common tab) and use the section expert to make the footer 'Suppress Blank Section'.

Fixed header rowin Reporting Services

Hi,
I've been devloping reports using Reporting Services. It's useful and fast.
However, I've this group of users who would like to fix the header row while
the data is able to scoll down and up without moving the header row. Is
there any configuration that can solve my problem? Below is a simple
illustration, hope anyone can help me in this.
| X header | Y header | Z header | ... | => This row must be fixed
| X data1 | Y data1 | Z data1 |... | ^
| X data2 | Y data2 | Z data2 |... | |
| X data3 | Y data3 | Z data3 |... | |
| X data4 | Y data4 | Z data4 |... | | These data rows
should be able to
| X data5 | Y data5 | Z data5 |... | | scroll up and down
| ........ | ........ | ........ |... | |
| X dataN | Y dataN | Z dataN |... | v
Best Regards,
Samie
I don't know how to do this.
I suspect that you will have a better response within the Reporting Services
newsgroup. I am including that group in this reply. Hopefully someone in
that group will be able to help.
Keith
"Samie" <Samie@.discussions.microsoft.com> wrote in message
news:D12C8438-E59D-49D5-9E2D-31C4D3A6B17C@.microsoft.com...
> Hi,
> I've been devloping reports using Reporting Services. It's useful and
fast.
> However, I've this group of users who would like to fix the header row
while
> the data is able to scoll down and up without moving the header row. Is
> there any configuration that can solve my problem? Below is a simple
> illustration, hope anyone can help me in this.
> | X header | Y header | Z header | ... | => This row must be fixed
> | X data1 | Y data1 | Z data1 |... | ^
> | X data2 | Y data2 | Z data2 |... | |
> | X data3 | Y data3 | Z data3 |... | |
> | X data4 | Y data4 | Z data4 |... | | These data rows
> should be able to
> | X data5 | Y data5 | Z data5 |... | | scroll up and down
> | ........ | ........ | ........ |... | |
> | X dataN | Y dataN | Z dataN |... | v
>
> Best Regards,
> Samie
>
|||Thanks Keith...
I'm hoping someone can help me too.
Regards,
samie
"Keith Kratochvil" wrote:

> I don't know how to do this.
> I suspect that you will have a better response within the Reporting Services
> newsgroup. I am including that group in this reply. Hopefully someone in
> that group will be able to help.
> --
> Keith
>
> "Samie" <Samie@.discussions.microsoft.com> wrote in message
> news:D12C8438-E59D-49D5-9E2D-31C4D3A6B17C@.microsoft.com...
> fast.
> while
>
|||Samie,
Fixed headers are not supported with version 1.0 but on the wish list for
2005.
Hope this helps.
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
"Samie" <Samie@.discussions.microsoft.com> wrote in message
news:119F060E-C479-4AF6-97E2-B59600B8D383@.microsoft.com...[vbcol=seagreen]
> Thanks Keith...
> I'm hoping someone can help me too.
> Regards,
> samie
>
> "Keith Kratochvil" wrote:
Services[vbcol=seagreen]
in[vbcol=seagreen]
row[vbcol=seagreen]
Is[vbcol=seagreen]
fixed[vbcol=seagreen]
rows[vbcol=seagreen]
down[vbcol=seagreen]

Fix matrix column and IF-ELSE statement

Hello.
I have to questions -
1) I have a matrix in my report and I want The group column to be fix so if I have a lot of columns and I scroll left the Header column will stay in the left of the screen and only the result column will be scrolled.
How can I do it?
2) How can I use a IF-ELSE statment or a CASE in the expression?
I want one of my column to be red if the value is "1", green if the value is "2" and blue if the value is "3".
Is there a way to do it?

Thanks in advance.Just to let you know that I found a replacment for the if.

I found that there is a switch-case statment in SSRS :)

I still be happy to hear if there is a way to make the first column of a matrix or table fix (like in excel) so they will stay visible even if I scroll left or right.

Thanks.|||

You can do the fix column using the fixedHeader property set to true on the cell of the table you are trying to lock.

Hope this helps

Andy

|||Hi Andy.

Thanks for your reply.

The FixedHeader is good only for table but I'm more intresting in Matrix.
In matrix you also have column that you define and I want those column to stay fixed on the left while I'm scrolling to the right with the rest of the data.

I didn't found any attribute like the "fixheader" for the matrix column.

Thank again.

Roy.|||

I would assume the field you want to remain visible is on a header as this is usually the case.

For this if you Edit the Column Group (or add the group!) then you will have an option asking if "Group header should remain visible when scrolling"

|||Andy - you are the man :)

Thanks a lot pal, I looked for ages for this solution!

Its exactly what I needed and now I can sleep well again :)

Thanks and take care,
Roy.

Fix matrix column and IF-ELSE statement

Hello.
I have to questions -
1) I have a matrix in my report and I want The group column to be fix
so if I have a lot of columns and I scroll left the Header column will
stay in the left of the screen and only the result column will be
scrolled.
How can I do it?
2) How can I use a IF-ELSE statment or a CASE in the expression?
I want one of my column to be red if the value is "1", green if the
value is "2" and blue if the value is "3".
Is there a way to do it?
Thanks in advance.Just to let you know that I found a replacment for the if.
I found that there is a switch-case statment in SSRS :)
I still be happy to hear if there is a way to make the first column of
a matrix or table fix (like in excel) so they will stay visible even if
I scroll left or right.
Thanks.
nicknack =EB=FA=E1:
> Hello.
> I have to questions -
> 1) I have a matrix in my report and I want The group column to be fix
> so if I have a lot of columns and I scroll left the Header column will
> stay in the left of the screen and only the result column will be
> scrolled.
> How can I do it?
> 2) How can I use a IF-ELSE statment or a CASE in the expression?
> I want one of my column to be red if the value is "1", green if the
> value is "2" and blue if the value is "3".
> Is there a way to do it?
> > Thanks in advance.|||O=2Ek so I got the answer to this matrix thing too.
After creating a column group you can use (in the "edit group" option)
the "Group header should remain visible when scrolling" and thats what
make the magic happen.
Thanks to andy for the solution :)
take care,
Roy
nicknack =D7=9B=D7=AA=D7=91:
> Just to let you know that I found a replacment for the if.
> I found that there is a switch-case statment in SSRS :)
> I still be happy to hear if there is a way to make the first column of
> a matrix or table fix (like in excel) so they will stay visible even if
> I scroll left or right.
> Thanks.
> nicknack =C3=AB=C3=BA=C3=A1:
> > Hello.
> > I have to questions -
> > 1) I have a matrix in my report and I want The group column to be fix
> > so if I have a lot of columns and I scroll left the Header column will
> > stay in the left of the screen and only the result column will be
> > scrolled.
> > How can I do it?
> > 2) How can I use a IF-ELSE statment or a CASE in the expression?
> > I want one of my column to be red if the value is "1", green if the
> > value is "2" and blue if the value is "3".
> > Is there a way to do it?
> > > > Thanks in advance.

Fix matrix column and IF-ELSE statement

Hello.
I have to questions -
1) I have a matrix in my report and I want The group column to be fixso if I have a lot of columns and I scroll left the Header column willstay in the left of the screen and only the result column will bescrolled.
How can I do it?
2) How can I use a IF-ELSE statment or a CASE in the expression?
I want one of my column to be red if the value is "1", green if the value is "2" and blue if the value is "3".
Is there a way to do it?

Thanks in advance.Just to let you know that I found a replacment for the if.

I found that there is a switch-case statment in SSRS :)

I still be happy to hear if there is a way to make the first column ofa matrix or table fix (like in excel) so they will stay visible even ifI scroll left or right.

Thanks.|||

O.k so I got the answer to this matrix thing too.

After creating a column group you can use (in the "edit group" option) the "Group header should remain visible when scrolling" and thats what make the magic happen.

Thanks to andy for the solution :)

take care,

Roy z.

First time user that can't find answer. Need online website.

HI! Thank you for even looking at this question.

I need to get a website up and running for a small group of people. I've had SQL Server for a while, but didn't have any use for it. It came with Visual Studio 2005, which gave me a little know how on scripting. Now, I have Visual Web Developer and what I need to make a web page. I just need to know how to configure all my settings to allow my website to be viewed online by anyone with an internet connection. I'm a person that understands detailed or simple directions, but I can not seem to find directions online. Now, I know what your thinking, but I've never attemped anything like this before. So, please, HELP! I need and want to learn as much as possible.

Thank you, tecfreak213

P.S. also, how do I change or get a web address? Thanks.

Hi,

Welcome to Forum.

You will require a Web Server (Internet Information Server ), A dedicated Internet connection, SQL Server, IP Address to bind to web server, Domain Name

Setup SQL Server with Database

Setup IIS Server (with Internet Connection and dedicated IP address bind to that, refer below article on how to setup IIS)

http://www.no-ip.com/support/guides/web_servers/setting_up_iis.html

http://www.webwizguide.com/asp/tutorials/installing_iis_winXP_pro.asp

http://www.webmasterstop.com/122.html

Create Directory on the IIS Server and deploy you web application in it

Bind you Directory to IIS

HTH

Hemantgiri S. Goswami

First or last row in T-SQL

Hi every body

I want to know what is similar to Last and First in Group By cluse like Access 2000.

I want to get last row of a column at the same time as get sum of another column

hi koosha,

i'm not sure if this is what you want

select max(orderid),customerid from
dbo.Orders
group by customerid

select min(orderid),customerid from
dbo.Orders
group by customerid

|||On Sat, 7 Jan 2006 05:33:00 -0800,

wrote:

>Hi every body

>I want to know what is similar to Last and First in Group By cluse like

>Access 2000.

>I want to get last row of a column at the same time as get sum of

>another column

>

Hi Koosha,

No, there is no such thing. Tables in a relational database have no

implied ordering, by definition. There can only be an order if you

specify it (e.g. using ORDER BY).

If you want the lowest or highest value of a column, use MIN(column) or

MAX(column). If you want the "first" or "last" row as defined by some

other order, use TOP 1, a subquery or a join to a derived table. Here's

an example using a subquery:

SELECT SUM(Column1) AS SumOfColumn1,

(SELECT TOP 1 Column2

FROM TheTable

ORDER BY SortColumn) AS FirstOfColumn2

FROM TheTable

--

Hugo Kornelis, SQL Server MVP|||

Jose is correct. There is no direct equivalent to "First" and "Last". You can sometimes use "Min" and "Max" as substitutes if that makes sense for the underlying data type.

|||hi

thanks for ur message.but i need to use for like this :

select sum(Orderprice) as exp1 , last(orderprice) as exp2 from table group by customerId

in that way u mentioned , it 's not posible to use with sum and other agregate function

I found 2 way to simulate but I don't know which one is better .

1- to design a class and add to assembly for sql 2005 .(I tried and was unsuccess)

2- to use another function inside my select (it answered)

First or last row in T-SQL

Hi every body

I want to know what is similar to Last and First in Group By cluse like Access 2000.

I want to get last row of a column at the same time as get sum of another column

hi koosha,

i'm not sure if this is what you want

select max(orderid),customerid from
dbo.Orders
group by customerid

select min(orderid),customerid from
dbo.Orders
group by customerid

|||On Sat, 7 Jan 2006 05:33:00 -0800,

wrote:

>Hi every body

>I want to know what is similar to Last and First in Group By cluse like

>Access 2000.

>I want to get last row of a column at the same time as get sum of

>another column

>

Hi Koosha,

No, there is no such thing. Tables in a relational database have no

implied ordering, by definition. There can only be an order if you

specify it (e.g. using ORDER BY).

If you want the lowest or highest value of a column, use MIN(column) or

MAX(column). If you want the "first" or "last" row as defined by some

other order, use TOP 1, a subquery or a join to a derived table. Here's

an example using a subquery:

SELECT SUM(Column1) AS SumOfColumn1,

(SELECT TOP 1 Column2

FROM TheTable

ORDER BY SortColumn) AS FirstOfColumn2

FROM TheTable

--

Hugo Kornelis, SQL Server MVP|||

Jose is correct. There is no direct equivalent to "First" and "Last". You can sometimes use "Min" and "Max" as substitutes if that makes sense for the underlying data type.

|||hi

thanks for ur message.but i need to use for like this :

select sum(Orderprice) as exp1 , last(orderprice) as exp2 from table group by customerId

in that way u mentioned , it 's not posible to use with sum and other agregate function

I found 2 way to simulate but I don't know which one is better .

1- to design a class and add to assembly for sql 2005 .(I tried and was unsuccess)

2- to use another function inside my select (it answered)

First in sequence

A java group put this out. Is there a way to make it work in SQL 2000 (works
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
--
Regards,
JamieTry:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:
> A java group put this out. Is there a way to make it work in SQL 2000 (works
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie

First in sequence

A java group put this out. Is there a way to make it work in SQL 2000 (works
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
Regards,
Jamie
Try:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:

> A java group put this out. Is there a way to make it work in SQL 2000 (works
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie

First in sequence

A Java group put this out. Is there a way to make it work in SQL 2000 (work
s
fine in SQL 2005 using a partition by statement - only looking for a 2000
method)
create table #tt(id int,name varchar(3),salary int)
insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
3000 as salary
insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
insert into #tt(id , name ,salary)select 20 , 'are', 4000
insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
select a.id, a.name, a.salary
from #tt a
where (a.id, a.salary) in
(select b.id, min(b.salary)
from #tt b
group by b.id
)
Regards,
JamieTry:
select
a.id, a.name, a.salary
from
#tt a
inner join
(
select [id], min(salary) as min_salary
from #tt
group by [id]
) as b
on a.[id] = b.[id] and a.salary = b.min_salary
go
AMB
"thejamie" wrote:

> A Java group put this out. Is there a way to make it work in SQL 2000 (wo
rks
> fine in SQL 2005 using a partition by statement - only looking for a 2000
> method)
> create table #tt(id int,name varchar(3),salary int)
> insert into #tt(id , name ,salary)select 10 as id, 'aaa'as name ,
> 3000 as salary
> insert into #tt(id , name ,salary)select 10 , 'abc' , 3890
> insert into #tt(id , name ,salary)select 10 , 'bbb', 2000
> insert into #tt(id , name ,salary)select 20 , 'xyz' , 10000
> insert into #tt(id , name ,salary)select 20 , 'are', 4000
> insert into #tt(id , name ,salary)select 50 , 'yyy' , 5000
> select a.id, a.name, a.salary
> from #tt a
> where (a.id, a.salary) in
> (select b.id, min(b.salary)
> from #tt b
> group by b.id
> )
> --
> Regards,
> Jamie

First 5 of a each group

I have a field that has different entries. Is there a way I can pull the first 5 of each one?

I can get the count of each different one, I just don't see how to get the first 5 of each.

SELECT PostType, COUNT(*) AS NumberOfEntires
FROM ECNADetail GROUP BY PostType ORDER BY PostType

That returns

PostType

NumberOfEntires

1

4924

2

181

3

3621

4

695

How many distinct post types are there? You could do:

Select Top 5 * from ECNADetail Where PostType=1

Union

Select Top 5 * from ECNADetail Where PostType=2

Union

....

|||

Hi Tealc,

Try this script:

select * from ECNADetail as t
where (select count(*) from ECNADetail where PostType=t.PostType and entries>t.entries)<5 order by PostType,entries desc

|||

Your definition of first 5 entries of PostType is not clear.

The following solution is based a datetime column (if you have one)

For Sql Server 2005, you can try:

SELECT
*FROM(SELECT*, Row_Number()OVER(PartitionBy PostTypeOrderBy PostType, YourdatetimeColumnDESC)as seq
FROM
ECNADetail)t1
WHERE
 t1.seq<6
 
If you don't have the datetimecolumn, you can choose other column to decide the order to choose the top 5 for each group.

|||I ended up doing the union with each type selected as the top 5. This was a one time run thing.

Firewall between IIS and SQL Server

Hi:
(Sorry for double post, I already post it in sqlserver.setup group but
i did't get any reply.)
I plan to deploying an ASP.Net web app into the following scenario:
Internet --> Firewall1 --> WebServer (IIS+SQL Server 2000) -->
firewall2 --> SQL Server 2000
The WebServer and the SQL Server behind the firewall2 is sit on
different LAN (different sub net),
and a two way replication will be create to sync these two SQL server
through firewall2.
Is it possible ? Is the firewall2 redundant ?
Separate the Web server and SQL server to different sub net and add
firewall2 between Web Server and SQL Server, does this configuration
provide better security to secure the data use by the LAN user ?
Thanks
JCVoon
having your servers behind the firewall does provide greater security.
however, you might want to read up on configuration to ensure connectivity
between hubs.
http://support.microsoft.com/kb/287932
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143602187.916531.187150@.i39g2000cwa.googlegr oups.com...
> Hi:
> (Sorry for double post, I already post it in sqlserver.setup group but
> i did't get any reply.)
> I plan to deploying an ASP.Net web app into the following scenario:
>
> Internet --> Firewall1 --> WebServer (IIS+SQL Server 2000) -->
> firewall2 --> SQL Server 2000
>
> The WebServer and the SQL Server behind the firewall2 is sit on
> different LAN (different sub net),
> and a two way replication will be create to sync these two SQL server
> through firewall2.
>
> Is it possible ? Is the firewall2 redundant ?
> Separate the Web server and SQL server to different sub net and add
> firewall2 between Web Server and SQL Server, does this configuration
> provide better security to secure the data use by the LAN user ?
>
> Thanks
> JCVoon
>
|||oj:
Thanks for the info.
Regards
JCVoon

Firewall between IIS and SQL Server

Hi:
(Sorry for double post, I already post it in sqlserver.setup group but
i did't get any reply.)
I plan to deploying an ASP.Net web app into the following scenario:
Internet --> Firewall1 --> WebServer (IIS+SQL Server 2000) -->
firewall2 --> SQL Server 2000
The WebServer and the SQL Server behind the firewall2 is sit on
different LAN (different sub net),
and a two way replication will be create to sync these two SQL server
through firewall2.
Is it possible ? Is the firewall2 redundant ?
Separate the Web server and SQL server to different sub net and add
firewall2 between Web Server and SQL Server, does this configuration
provide better security to secure the data use by the LAN user ?
Thanks
JCVoonhaving your servers behind the firewall does provide greater security.
however, you might want to read up on configuration to ensure connectivity
between hubs.
http://support.microsoft.com/kb/287932
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143602187.916531.187150@.i39g2000cwa.googlegroups.com...
> Hi:
> (Sorry for double post, I already post it in sqlserver.setup group but
> i did't get any reply.)
> I plan to deploying an ASP.Net web app into the following scenario:
>
> Internet --> Firewall1 --> WebServer (IIS+SQL Server 2000) -->
> firewall2 --> SQL Server 2000
>
> The WebServer and the SQL Server behind the firewall2 is sit on
> different LAN (different sub net),
> and a two way replication will be create to sync these two SQL server
> through firewall2.
>
> Is it possible ? Is the firewall2 redundant ?
> Separate the Web server and SQL server to different sub net and add
> firewall2 between Web Server and SQL Server, does this configuration
> provide better security to secure the data use by the LAN user ?
>
> Thanks
> JCVoon
>|||oj:
Thanks for the info.
Regards
JCVoon

Firewall between IIS and SQL Server

Hi:
(Sorry for double post, I already post it in sqlserver.setup group but
i did't get any reply.)
I plan to deploying an ASP.Net web app into the following scenario:
Internet --> Firewall1 --> WebServer (IIS+SQL Server 2000) -->
firewall2 --> SQL Server 2000
The WebServer and the SQL Server behind the firewall2 is sit on
different LAN (different sub net),
and a two way replication will be create to sync these two SQL server
through firewall2.
Is it possible ? Is the firewall2 redundant ?
Separate the Web server and SQL server to different sub net and add
firewall2 between Web Server and SQL Server, does this configuration
provide better security to secure the data use by the LAN user ?
Thanks
JCVoonhaving your servers behind the firewall does provide greater security.
however, you might want to read up on configuration to ensure connectivity
between hubs.
http://support.microsoft.com/kb/287932
-oj
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143602187.916531.187150@.i39g2000cwa.googlegroups.com...
> Hi:
> (Sorry for double post, I already post it in sqlserver.setup group but
> i did't get any reply.)
> I plan to deploying an ASP.Net web app into the following scenario:
>
> Internet --> Firewall1 --> WebServer (IIS+SQL Server 2000) -->
> firewall2 --> SQL Server 2000
>
> The WebServer and the SQL Server behind the firewall2 is sit on
> different LAN (different sub net),
> and a two way replication will be create to sync these two SQL server
> through firewall2.
>
> Is it possible ? Is the firewall2 redundant ?
> Separate the Web server and SQL server to different sub net and add
> firewall2 between Web Server and SQL Server, does this configuration
> provide better security to secure the data use by the LAN user ?
>
> Thanks
> JCVoon
>|||oj:
Thanks for the info.
Regards
JCVoon

firehose

Good day to all

How could I dis-able the read-only in a table when it generate an error message "Firehose mode"?, when I delete a record or a group of records?. What is that mean?

Thank you for any help

Madix

Hi Madix,

Could you let me know the exact error message you're getting, which product/language/API you are using and what you do that causes the error please.

Thanks,

Chris

|||Hi,
guess your problem is related to:
http://support.microsoft.com/kb/286199/en-us
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q237398&
HTH, Jens Suessmeyer.

firehose

Good day to all

How could I dis-able the read-only in a table when it generate an error message "Firehose mode"?, when I delete a record or a group of records?. What is that mean?

Thank you for any help

Madix

Hi Madix,

Could you let me know the exact error message you're getting, which product/language/API you are using and what you do that causes the error please.

Thanks,

Chris

|||Hi,
guess your problem is related to:
http://support.microsoft.com/kb/286199/en-us
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q237398&
HTH, Jens Suessmeyer.