alerts

How can I set an alert to email me when a Login fails? I have been getting
several "Login failed for user '(null)'. Reason : Not associated witha
trusted SQL Server Connection." I need to find out what web site is doing
this.You can create alerts for specific error numbers - this one
I think is error number 18452
I don't think that will help you find out what web site is
failing causing the error. You'd probably have to monitor at
the network level.
-Sue
On Thu, 25 Aug 2005 11:01:06 -0700, "Rich"
<Rich@.discussions.microsoft.com> wrote:
>How can I set an alert to email me when a Login fails? I have been getting
>several "Login failed for user '(null)'. Reason : Not associated witha
>trusted SQL Server Connection." I need to find out what web site is doing
>this.

alerts

How can I set an alert to email me when a Login fails? I have been getting
several "Login failed for user '(null)'. Reason : Not associated witha
trusted SQL Server Connection." I need to find out what web site is doing
this.
You can create alerts for specific error numbers - this one
I think is error number 18452
I don't think that will help you find out what web site is
failing causing the error. You'd probably have to monitor at
the network level.
-Sue
On Thu, 25 Aug 2005 11:01:06 -0700, "Rich"
<Rich@.discussions.microsoft.com> wrote:

>How can I set an alert to email me when a Login fails? I have been getting
>several "Login failed for user '(null)'. Reason : Not associated witha
>trusted SQL Server Connection." I need to find out what web site is doing
>this.
sql

alerts

How can I set an alert to email me when a Login fails? I have been getting
several "Login failed for user '(null)'. Reason : Not associated witha
trusted SQL Server Connection." I need to find out what web site is doing
this.You can create alerts for specific error numbers - this one
I think is error number 18452
I don't think that will help you find out what web site is
failing causing the error. You'd probably have to monitor at
the network level.
-Sue
On Thu, 25 Aug 2005 11:01:06 -0700, "Rich"
<Rich@.discussions.microsoft.com> wrote:

>How can I set an alert to email me when a Login fails? I have been getting
>several "Login failed for user '(null)'. Reason : Not associated witha
>trusted SQL Server Connection." I need to find out what web site is doing
>this.

Aggregete() function doesn't work for SQL query results!

Reporting Services translates null value into blank on SQL query results, but not on MDX query results. But Aggregate() functions is triggered only by null value.

So Aggregate() function only works for MDX query results, not for SQL query results.

MDX example:

select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube

SQL example:

SELECT * FROM OPENQUERY(Linked_Cube, '
select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube')

Now you build a report with a table, then add a grouping and use "=Aggregate(Fields!Sales.Value)" for the group level cell. If you bind MDX query to this table, then aggregates show up correctly. But if you bind SQL query to this table, there are no aggregates at all.

I need to use SQL query to drive my reports, because MDX query results need to be merged with results from other calculations.

How can I make Aggregate() function to work for SQL query results?

Thanks,

Bo Dong

bo_dong@.yahoo.com

How can I make it to work for SQL as well?

Please read my answer on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=668524&SiteID=1

-- Robert

aggregates and type conversion

Hello,
I have a field of type string which I know will either hold a double value or null. I wish to sum the value of this field and mulitply the result by -1 to give a negative number, but it's proving really difficult.
My first problem is that if I modify my field to use CDbl I get an error when it runs, along the lines of "Field contains a direct or indirect reference to itself..."
So I have to get around it by creating a separate field with a different name, like
myFieldDbl and setting it's formula to = Cdbl(Fields!MyField.Value).
Why is that then ?
My next problem is that when I multiply by -1
e.g. =Sum(Fields!MyFieldDbl.Value * -1),
it gives me this error :
"The value expression for the textbox â'textbox19â' uses an aggregate function on data of varying data types".
To get around this I have to multiply by -1.0 instead ! As if there's a difference !!!
Type conversion in Reporting services is diabolical. I can't even use the format property unless I explicitly declare my field to be a particluar type first, even though I could happily use format$(myField,"C") in the Value property with any data type.
Can I expect MS might have a look at thses issues in time for the next service pack?Note: It is up to the data provider (e.g. managed SQL provider) how it
translates database types into .NET datatypes. Generally, you can determine
the runtime datatype of fields by temporarily adding a textbox to the report
which shows the runtime datatype:
=First(Fields!SomeFieldName.Value, "DataSetName").GetType.ToString
I assume that your field is actually a System.Decimal rather than a
System.Double as you indicated. An expression like this should work for you:
= -1 * Sum( iif(IsNothing(Fields!MyFieldDbl.Value), 0.0,
CDbl(Fields!MyFieldDbl.Value)))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike Salway" <MikeSalway@.discussions.microsoft.com> wrote in message
news:B90D6631-A0D2-4F53-9C69-A06366ADA6C7@.microsoft.com...
> Hello,
> I have a field of type string which I know will either hold a double value
or null. I wish to sum the value of this field and mulitply the result
by -1 to give a negative number, but it's proving really difficult.
> My first problem is that if I modify my field to use CDbl I get an error
when it runs, along the lines of "Field contains a direct or indirect
reference to itself..."
> So I have to get around it by creating a separate field with a different
name, like
> myFieldDbl and setting it's formula to = Cdbl(Fields!MyField.Value).
> Why is that then ?
> My next problem is that when I multiply by -1
> e.g. =Sum(Fields!MyFieldDbl.Value * -1),
> it gives me this error :
> "The value expression for the textbox 'textbox19' uses an aggregate
function on data of varying data types".
> To get around this I have to multiply by -1.0 instead ! As if there's a
difference !!!
> Type conversion in Reporting services is diabolical. I can't even use the
format property unless I explicitly declare my field to be a particluar type
first, even though I could happily use format$(myField,"C") in the Value
property with any data type.
> Can I expect MS might have a look at thses issues in time for the next
service pack?

AggregateFunction = None

Hi everibody.

I use a AggregateFunction = None for a measure (price for article). When I go in a client Olap, every data is Null.

Where I wrong?

Tank you

Nothing is wrong. AggregationFunction = None means the data is not aggregated and only exists on leaves of measure group. Therefore all aggregated cells are NULL. This is how None is supposed to work.|||

Sorry Mosha. For first tank you, but.....

I read that leaf of a hierarchy take value from Fact table, then the total or subtotal don't exist, but value for leaf must me exist. Instead, I never see data

Bye from Florence

Marco

|||

There is important difference between hierarchy leaves and measure group leaves. The data is loaded at measure group leaves, not at hierarchy leaves. For more details about difference between the two you can read the following: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

|||

Tahk you Mosha. I try to understad......

Bye Bye

Marco

AggregateFunction = None

Hi everibody.

I use a AggregateFunction = None for a measure (price for article). When I go in a client Olap, every data is Null.

Where I wrong?

Tank you

Nothing is wrong. AggregationFunction = None means the data is not aggregated and only exists on leaves of measure group. Therefore all aggregated cells are NULL. This is how None is supposed to work.|||

Sorry Mosha. For first tank you, but.....

I read that leaf of a hierarchy take value from Fact table, then the total or subtotal don't exist, but value for leaf must me exist. Instead, I never see data

Bye from Florence

Marco

|||

There is important difference between hierarchy leaves and measure group leaves. The data is loaded at measure group leaves, not at hierarchy leaves. For more details about difference between the two you can read the following: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

|||

Tahk you Mosha. I try to understad......

Bye Bye

Marco

Aggregate returns null - sub value

I have something like the following:
select count(mycolumn) from mytable where...
This works great. The problem is that sometimes, count(mycolumn) returns
null because mycolumn contains no values. In this case, I'd really rather
show the user a '0' rather than the word null.
Any suggestions on how to show the user a 0 (zero) when count returns a null
?
Thanks
--
RandyTry:
select isnull (count(mycolumn), 0)
from mytable where...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:E73AE804-52AF-4C79-ACEE-59385838204D@.microsoft.com...
I have something like the following:
select count(mycolumn) from mytable where...
This works great. The problem is that sometimes, count(mycolumn) returns
null because mycolumn contains no values. In this case, I'd really rather
show the user a '0' rather than the word null.
Any suggestions on how to show the user a 0 (zero) when count returns a
null?
Thanks
--
Randy|||Try,
select isnull(count(mycolumn), 0) from mytable where ...
go
AMB
"randy1200" wrote:

> I have something like the following:
> select count(mycolumn) from mytable where...
> This works great. The problem is that sometimes, count(mycolumn) returns
> null because mycolumn contains no values. In this case, I'd really rather
> show the user a '0' rather than the word null.
> Any suggestions on how to show the user a 0 (zero) when count returns a nu
ll?
> Thanks
> --
> Randy|||Exactly what I needed. Many thanks
--
Randy
"Tom Moreau" wrote:

> Try:
> select isnull (count(mycolumn), 0)
> from mytable where...
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "randy1200" <randy1200@.newsgroups.nospam> wrote in message
> news:E73AE804-52AF-4C79-ACEE-59385838204D@.microsoft.com...
> I have something like the following:
> select count(mycolumn) from mytable where...
> This works great. The problem is that sometimes, count(mycolumn) returns
> null because mycolumn contains no values. In this case, I'd really rather
> show the user a '0' rather than the word null.
> Any suggestions on how to show the user a 0 (zero) when count returns a
> null?
> Thanks
> --
> Randy
>|||select count(mycolumn) from mytable where mycolumn is not null
?
"randy1200" <randy1200@.newsgroups.nospam> wrote in message
news:E73AE804-52AF-4C79-ACEE-59385838204D@.microsoft.com...
> I have something like the following:
> select count(mycolumn) from mytable where...
> This works great. The problem is that sometimes, count(mycolumn) returns
> null because mycolumn contains no values. In this case, I'd really rather
> show the user a '0' rather than the word null.
> Any suggestions on how to show the user a 0 (zero) when count returns a
null?
> Thanks
> --
> Randy

aggregate query help

--drop table bldg
--drop table room
create table bldg
(
bldg_id int not null primary key,
bldg_nm varchar(20)
)
go
create table room
(
bldg_id int not null,
room_num varchar(10) not null,
area numeric,
org varchar(10)
)
go
alter table room add constraint PKroom primary key (bldg_id, room_num)
go
insert into bldg values (1, 'B1')
insert into bldg values (2, 'B2')
insert into room values (1, '1RM1', 100.00, 'Org1')
insert into room values (1, '1RM2', 50.00, 'Org2')
insert into room values (1, '1RM3', 250.00, 'Org2')
insert into room values (2, '2RM1', 400.00, 'Org1')
insert into room values (2, '2RM2', 600.00, 'Org2')
go
select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org
order by t1.bldg_nm, t2.org
go
bldg org Area
-- -- ---
B1 Org1 100
B1 Org2 300
B2 Org1 400
B2 Org2 600
that works fine. however, i need two additional columns in the output.
one column is the total area of the building.
the other is the percentage of usage by the org of each building.
the output i need would look like this.
bldg org
Area Bldg Area Org Pct
-- -- ---
B1 Org1
100 400 25.00
B1 Org2
300 400 75.00
B2 Org1
400 1000 40.00
B2 Org2
600 1000 60.00
any way to do this in a select statement, i.e. no stored procedures or
cursors?Try something like this:
set nocount on
create table bldg
(
bldg_id int not null primary key,
bldg_nm varchar(20)
)
go
create table room
(
bldg_id int not null,
room_num varchar(10) not null,
area numeric,
org varchar(10)
)
go
alter table room add constraint PKroom primary key (bldg_id, room_num)
go
insert into bldg values (1, 'B1')
insert into bldg values (2, 'B2')
insert into room values (1, '1RM1', 100.00, 'Org1')
insert into room values (1, '1RM2', 50.00, 'Org2')
insert into room values (1, '1RM3', 250.00, 'Org2')
insert into room values (2, '2RM1', 400.00, 'Org1')
insert into room values (2, '2RM2', 600.00, 'Org2')
go
select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org
order by t1.bldg_nm, t2.org
go
select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
on t1.bldg_id = t2.bldg_id
group by t1.bldg_nm
go
select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
100.00 PCT
from
(select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
on t1.bldg_id = t2.bldg_id
group by t1.bldg_nm, t1.bldg_id) t3
join
(select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
group by t1.bldg_nm, t2.org, t1.bldg_id) t4
on t3.bldg_nm = t4.bldg_nm
order by t3.bldg_nm, t4.org
drop table bldg
drop table room
--
----
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"ch" <ch@.dontemailme.com> wrote in message
news:4152E589.90B4DB41@.dontemailme.com...
> --drop table bldg
> --drop table room
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
>
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> bldg org Area
> -- -- ---
> B1 Org1 100
> B1 Org2 300
> B2 Org1 400
> B2 Org2 600
>
> that works fine. however, i need two additional columns in the output.
> one column is the total area of the building.
> the other is the percentage of usage by the org of each building.
> the output i need would look like this.
> bldg org
> Area Bldg Area Org Pct
> -- -- ---
> B1 Org1
> 100 400 25.00
> B1 Org2
> 300 400 75.00
> B2 Org1
> 400 1000 40.00
> B2 Org2
> 600 1000 60.00
> any way to do this in a select statement, i.e. no stored procedures or
> cursors?|||thanks, although i had to change you inner join syntax to where clauses
because i don't care for the inner join syntax ;)
"Gregory A. Larsen" wrote:
> Try something like this:
> set nocount on
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm
> go
> select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> 100.00 PCT
> from
> (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm, t1.bldg_id) t3
> join
> (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> on t3.bldg_nm = t4.bldg_nm
> order by t3.bldg_nm, t4.org
> drop table bldg
> drop table room
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
> "ch" <ch@.dontemailme.com> wrote in message
> news:4152E589.90B4DB41@.dontemailme.com...
> >
> > --drop table bldg
> > --drop table room
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > bldg org Area
> > -- -- ---
> > B1 Org1 100
> > B1 Org2 300
> > B2 Org1 400
> > B2 Org2 600
> >
> >
> > that works fine. however, i need two additional columns in the output.
> > one column is the total area of the building.
> > the other is the percentage of usage by the org of each building.
> > the output i need would look like this.
> >
> > bldg org
> > Area Bldg Area Org Pct
> > -- -- ---
> > B1 Org1
> > 100 400 25.00
> > B1 Org2
> > 300 400 75.00
> > B2 Org1
> > 400 1000 40.00
> > B2 Org2
> > 600 1000 60.00
> >
> > any way to do this in a select statement, i.e. no stored procedures or
> > cursors?|||thanks, although i had to change you inner join syntax to where clauses
because i don't care for the inner join syntax ;)
"Gregory A. Larsen" wrote:
> Try something like this:
> set nocount on
> create table bldg
> (
> bldg_id int not null primary key,
> bldg_nm varchar(20)
> )
> go
> create table room
> (
> bldg_id int not null,
> room_num varchar(10) not null,
> area numeric,
> org varchar(10)
> )
> go
> alter table room add constraint PKroom primary key (bldg_id, room_num)
> go
> insert into bldg values (1, 'B1')
> insert into bldg values (2, 'B2')
> insert into room values (1, '1RM1', 100.00, 'Org1')
> insert into room values (1, '1RM2', 50.00, 'Org2')
> insert into room values (1, '1RM3', 250.00, 'Org2')
> insert into room values (2, '2RM1', 400.00, 'Org1')
> insert into room values (2, '2RM2', 600.00, 'Org2')
> go
> select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org
> order by t1.bldg_nm, t2.org
> go
> select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm
> go
> select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> 100.00 PCT
> from
> (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> on t1.bldg_id = t2.bldg_id
> group by t1.bldg_nm, t1.bldg_id) t3
> join
> (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> on t3.bldg_nm = t4.bldg_nm
> order by t3.bldg_nm, t4.org
> drop table bldg
> drop table room
> --
> ----
> ----
> -
> Need SQL Server Examples check out my website
> http://www.geocities.com/sqlserverexamples
> "ch" <ch@.dontemailme.com> wrote in message
> news:4152E589.90B4DB41@.dontemailme.com...
> >
> > --drop table bldg
> > --drop table room
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > bldg org Area
> > -- -- ---
> > B1 Org1 100
> > B1 Org2 300
> > B2 Org1 400
> > B2 Org2 600
> >
> >
> > that works fine. however, i need two additional columns in the output.
> > one column is the total area of the building.
> > the other is the percentage of usage by the org of each building.
> > the output i need would look like this.
> >
> > bldg org
> > Area Bldg Area Org Pct
> > -- -- ---
> > B1 Org1
> > 100 400 25.00
> > B1 Org2
> > 300 400 75.00
> > B2 Org1
> > 400 1000 40.00
> > B2 Org2
> > 600 1000 60.00
> >
> > any way to do this in a select statement, i.e. no stored procedures or
> > cursors?|||> because i don't care for the inner join syntax ;)
That is a mistake IMO. The whole database world is moving away from the old join syntax. Of course
it is up to you, but perhaps time will come when you work in a project/organization that don't like
the old join syntax... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ch" <ch@.dontemailme.com> wrote in message news:41530D1C.953CF4F1@.dontemailme.com...
> thanks, although i had to change you inner join syntax to where clauses
> because i don't care for the inner join syntax ;)
>
> "Gregory A. Larsen" wrote:
> >
> > Try something like this:
> >
> > set nocount on
> >
> > create table bldg
> > (
> > bldg_id int not null primary key,
> > bldg_nm varchar(20)
> > )
> > go
> >
> > create table room
> > (
> > bldg_id int not null,
> > room_num varchar(10) not null,
> > area numeric,
> > org varchar(10)
> > )
> > go
> > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > go
> >
> > insert into bldg values (1, 'B1')
> > insert into bldg values (2, 'B2')
> > insert into room values (1, '1RM1', 100.00, 'Org1')
> > insert into room values (1, '1RM2', 50.00, 'Org2')
> > insert into room values (1, '1RM3', 250.00, 'Org2')
> > insert into room values (2, '2RM1', 400.00, 'Org1')
> > insert into room values (2, '2RM2', 600.00, 'Org2')
> > go
> >
> > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org
> > order by t1.bldg_nm, t2.org
> > go
> >
> > select t1.bldg_nm, sum(t2.area) from bldg t1 join room t2
> > on t1.bldg_id = t2.bldg_id
> > group by t1.bldg_nm
> > go
> >
> > select t3.bldg_nm as 'bldg', t4.org, Area, bldg_area , area / bldg_area *
> > 100.00 PCT
> > from
> >
> > (select t1.bldg_nm, sum(t2.area) bldg_area from bldg t1 join room t2
> > on t1.bldg_id = t2.bldg_id
> > group by t1.bldg_nm, t1.bldg_id) t3
> >
> > join
> >
> > (select t1.bldg_nm, t2.org, sum(t2.area) as 'Area'
> > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > group by t1.bldg_nm, t2.org, t1.bldg_id) t4
> >
> > on t3.bldg_nm = t4.bldg_nm
> >
> > order by t3.bldg_nm, t4.org
> >
> > drop table bldg
> > drop table room
> >
> > --
> >
> > ----
> > ----
> > -
> >
> > Need SQL Server Examples check out my website
> > http://www.geocities.com/sqlserverexamples
> >
> > "ch" <ch@.dontemailme.com> wrote in message
> > news:4152E589.90B4DB41@.dontemailme.com...
> > >
> > > --drop table bldg
> > > --drop table room
> > >
> > > create table bldg
> > > (
> > > bldg_id int not null primary key,
> > > bldg_nm varchar(20)
> > > )
> > > go
> > >
> > > create table room
> > > (
> > > bldg_id int not null,
> > > room_num varchar(10) not null,
> > > area numeric,
> > > org varchar(10)
> > > )
> > > go
> > > alter table room add constraint PKroom primary key (bldg_id, room_num)
> > > go
> > >
> > > insert into bldg values (1, 'B1')
> > > insert into bldg values (2, 'B2')
> > > insert into room values (1, '1RM1', 100.00, 'Org1')
> > > insert into room values (1, '1RM2', 50.00, 'Org2')
> > > insert into room values (1, '1RM3', 250.00, 'Org2')
> > > insert into room values (2, '2RM1', 400.00, 'Org1')
> > > insert into room values (2, '2RM2', 600.00, 'Org2')
> > > go
> > >
> > >
> > > select t1.bldg_nm as 'bldg', t2.org, sum(t2.area) as 'Area'
> > > from bldg t1, room t2 where t2.bldg_id = t1.bldg_id
> > > group by t1.bldg_nm, t2.org
> > > order by t1.bldg_nm, t2.org
> > > go
> > >
> > > bldg org Area
> > > -- -- ---
> > > B1 Org1 100
> > > B1 Org2 300
> > > B2 Org1 400
> > > B2 Org2 600
> > >
> > >
> > > that works fine. however, i need two additional columns in the output.
> > > one column is the total area of the building.
> > > the other is the percentage of usage by the org of each building.
> > > the output i need would look like this.
> > >
> > > bldg org
> > > Area Bldg Area Org Pct
> > > -- -- ---
> > > B1 Org1
> > > 100 400 25.00
> > > B1 Org2
> > > 300 400 75.00
> > > B2 Org1
> > > 400 1000 40.00
> > > B2 Org2
> > > 600 1000 60.00
> > >
> > > any way to do this in a select statement, i.e. no stored procedures or
> > > cursors?

Again a "Login failed for user 'null"

Hi,
I'm trying to setup replication between 2 SQL server on 2 different sites.
SQL server A is a W2k SP4 server with SQL7.0 with SQL2000 client tools
SQL server B is a W2k SP4 server with SQL2000
Servers are in mixed mode.
Servers both have MDAC version 2.6 sp2
Username used to login to the server is the administrator and for connection
to SQL is use the sa account.
When I try to start a snapshot agent I get the message "Login failed for
user 'null'. Reason: Not associated with a trusted SQL Server connection"
How can I solve this problem.
kind regards
Michiel BoerHow are you launching the snapshot agent? through command line or sql server
agent?
What is the exact command that you are using?
What account is the SQL agent service running under?
Thanks,
Bala.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michiel" <meme@.ikkus.com> wrote in message
news:403b5fc8$0$32978$e4fe514c@.dreader15
.news.xs4all.nl...
> Hi,
> I'm trying to setup replication between 2 SQL server on 2 different sites.
> SQL server A is a W2k SP4 server with SQL7.0 with SQL2000 client tools
> SQL server B is a W2k SP4 server with SQL2000
> Servers are in mixed mode.
> Servers both have MDAC version 2.6 sp2
> Username used to login to the server is the administrator and for
connection
> to SQL is use the sa account.
> When I try to start a snapshot agent I get the message "Login failed for
> user 'null'. Reason: Not associated with a trusted SQL Server connection"
> How can I solve this problem.
> kind regards
> Michiel Boer
>
>

After SSIS package runs All rows, All Fields are NULL in destination table ?

I am copying a simple table from a Sql Server 2005 database to an *.sdf mobile database.

I am brand new to SSIS and I am probably doing something wrong. But after executing the SSIS package all the rows and all the fields are NULL in the destination database. I put a datagrid viewer between the OLE DB Source and the Sql Server compact edition destination and I can see the real data which is obviously not ALL NULL.

Does anyone have a clue as to why it would be doing this?

Any help would be much appreciated.

Thanks...

I do't have a cue why this would be happening but if I were investigating I would start at the SQL End. That means SQL profiler to find out what insert statements are being issued. Admittedly I'm assuming that Profiler will work with SQL CE.

-Jamie