Hi
I'm working on a Matrix report in which i have a column grouping. I
have added a subtotal for the column group. so this total comes as he
last column i.e. after all the values in that column grouping. I want
to have a subtotal after lets say 10 columns and then again a total at
the end.
Can somebody tell me how to have a subtotal in the middle of a group as
well as in the end of the group?
Ankur MehtaAnkur,
The only way I know how to do this is to add another group which breaks down
your columns into sets of X number, depending on what other factor you use to
group on. Example using the Adventure Works sample database: Group on the
main Products, then do a group on Accessories under the products. Then you
can have a group2 footer which subtotals on the second group and a group1
footer which subtotals on the entire Products line.
Sorry I don't know of another way.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Ankur Mehta" wrote:
> Hi
> I'm working on a Matrix report in which i have a column grouping. I
> have added a subtotal for the column group. so this total comes as he
> last column i.e. after all the values in that column grouping. I want
> to have a subtotal after lets say 10 columns and then again a total at
> the end.
> Can somebody tell me how to have a subtotal in the middle of a group as
> well as in the end of the group?
> Ankur Mehta
>
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Having clause without GROUP BY clause?
Hi,
What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?
eg :
SELECT SUM(col1) from test HAVING col2 < 5
SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5
I want the equivalent query in MSSQLServer for the above Oracle query.
Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.
Thanks,
Gopi.those queries actually run in oracle? i rather doubt it
without a GROUP BY, the entire table is considered a single group
the individual col2 values would not necessarily all be the same, therefore the HAVING condition in the first query would not necessarily give you the results you want, assuming it even runs, which i doubt
in the second query you will surely get a syntax error even in oracle
perhaps what you want for the two queries is:
SELECT SUM(col1) from test where col2 < 5
SELECT SUM(col1) from test WHERE x=y AND col2 < 5|||Sorry for the typos. Actually the queries are as follows.
SELECT SUM(col1) from test HAVING SUM(col2) < 5
SELECT SUM(col1) from test WHERE x=y HAVING SUM(col2) < 5
Thanks,
Gopi.|||do you have sql server? if so, why don't you test those queries and see what you get
What is HAVING clause equivalent in the following oracle query, without the combination of "GROUP BY" clause ?
eg :
SELECT SUM(col1) from test HAVING col2 < 5
SELECT SUM(col1) from test WHERE x=y AND HAVING col2 < 5
I want the equivalent query in MSSQLServer for the above Oracle query.
Also, does the aggregate function in Select column(here the SUM(col1)) affect in anyway the presence of HAVING clause?.
Thanks,
Gopi.those queries actually run in oracle? i rather doubt it
without a GROUP BY, the entire table is considered a single group
the individual col2 values would not necessarily all be the same, therefore the HAVING condition in the first query would not necessarily give you the results you want, assuming it even runs, which i doubt
in the second query you will surely get a syntax error even in oracle
perhaps what you want for the two queries is:
SELECT SUM(col1) from test where col2 < 5
SELECT SUM(col1) from test WHERE x=y AND col2 < 5|||Sorry for the typos. Actually the queries are as follows.
SELECT SUM(col1) from test HAVING SUM(col2) < 5
SELECT SUM(col1) from test WHERE x=y HAVING SUM(col2) < 5
Thanks,
Gopi.|||do you have sql server? if so, why don't you test those queries and see what you get
having clause and where clause
for the following SQL statement
select ...
group by col1
having col1 is not null
Does it always return the same result as
select ...
where col1 is not null
group by col1
?
The "where" one should have better performance, is it always true?Having gets evaluated after all the results have been returned and
aggregates calculated.
Where gets evaluated as the results are returned. At which point the filter
happens depends on the execution plan, but it will always be prior to
aggregation.
As a rule, only criteria on aggregate columns (sum, count, etc.) should be
in the having clause. Everything else belongs in the where.
Where SHOULD always be more efficient than having. HOWEVER, it is possible
(but highly unlikely) that the difference could cause SQL Server to use a
different execution plan that by some freak coincidence would perform better
with the having than the where. This would be the exception, and I would be
rather surprised if anyone could come up with such a case.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||They should return the same result. However, the version with the where
clause is (IMHO) better code. Where should be used when you want to compare
on the values in each row, use haveing when you want to compare on
aggregated values. So the SQL to retrive all customers with orders totaling
more than $1000 from New York would be something like:
Select ...
Where State = 'NY'
Group By CustomerID
Having Amount > 1000
Tom
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||Use having ONLY if you cannot achieve the functionality through the where
clause.
In case of having the result set is already generated and the filter is
applied.
And in this particular case that you have mentioned there won't be any
difference in the result (or so I think).
select ...
group by col1
having col1 is not null
Does it always return the same result as
select ...
where col1 is not null
group by col1
?
The "where" one should have better performance, is it always true?Having gets evaluated after all the results have been returned and
aggregates calculated.
Where gets evaluated as the results are returned. At which point the filter
happens depends on the execution plan, but it will always be prior to
aggregation.
As a rule, only criteria on aggregate columns (sum, count, etc.) should be
in the having clause. Everything else belongs in the where.
Where SHOULD always be more efficient than having. HOWEVER, it is possible
(but highly unlikely) that the difference could cause SQL Server to use a
different execution plan that by some freak coincidence would perform better
with the having than the where. This would be the exception, and I would be
rather surprised if anyone could come up with such a case.
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||They should return the same result. However, the version with the where
clause is (IMHO) better code. Where should be used when you want to compare
on the values in each row, use haveing when you want to compare on
aggregated values. So the SQL to retrive all customers with orders totaling
more than $1000 from New York would be something like:
Select ...
Where State = 'NY'
Group By CustomerID
Having Amount > 1000
Tom
"nick" <nick@.discussions.microsoft.com> wrote in message
news:D3E95AF6-9550-4842-A62E-9B78A93AFC19@.microsoft.com...
> for the following SQL statement
> select ...
> group by col1
> having col1 is not null
> Does it always return the same result as
> select ...
> where col1 is not null
> group by col1
> ?
> The "where" one should have better performance, is it always true?|||Use having ONLY if you cannot achieve the functionality through the where
clause.
In case of having the result set is already generated and the filter is
applied.
And in this particular case that you have mentioned there won't be any
difference in the result (or so I think).
HAVING and GROUP BY
Hi All
I want to have all ideas about the use
of HAVING and GROUP BY
When either of them can be used?
Can HAVING be used without GROUP BY? HOW? WHY?
Can HAVING be used without an aggregate function? HOW? WHY?
Guys, you will certainly be doing me a favour and all other
newbie who access this group but find advanced discussions
going over the head due to less clear basics.
Thanks in Advance
Regards
Suresh BeniwalRefer Books online
"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message
news:1128424024.788067.225630@.g44g2000cwa.googlegroups.com...
> Hi All
> I want to have all ideas about the use
> of HAVING and GROUP BY
> When either of them can be used?
> Can HAVING be used without GROUP BY? HOW? WHY?
> Can HAVING be used without an aggregate function? HOW? WHY?
> Guys, you will certainly be doing me a favour and all other
> newbie who access this group but find advanced discussions
> going over the head due to less clear basics.
> Thanks in Advance
> Regards
> Suresh Beniwal
>|||Look in the BOl for further details about GROUP BY and HAVING. In
Common using Group by groups the resultset for the specified columns:
Select companyName
>From Customer
Group by companyName
HAVING is used to filter down the grouped result by the expression you
issue within the HAVING clause (e.g. HAVING COUNT(*) > 1, showing only
these values that appear more than one time within
Select companyName
>From Customer
Group by companyName
Having COUNT(*) > 1
Samples out there, e.g.
http://msdn.microsoft.com/library/d...r />
_1l4j.asp
HTH, Jens Suessmeyer.|||SureshBeniwal wrote:
> Hi All
> I want to have all ideas about the use
> of HAVING and GROUP BY
Check out BOL, and posts like this:
http://groups.google.nl/group/micro...de=source&hl=nl
> When either of them can be used?
GROUP BY if you want to aggregate information on groups of data. HAVING
if you want to filter the result based on the aggregated information. If
you want to filter the result based on base columns, then I suggest you
use the WHERE clause
> Can HAVING be used without GROUP BY?
Yes
> HOW?
By omitting the GROUP BY clause, and by only specifying aggregate
functions
> WHY?
Because the entire selection will be interpreted as a single group. This
is also why non-aggregate functions are not allowed when specifying a
HAVING clause without GROUP BY clause
> Can HAVING be used without an aggregate function?
Yes
> HOW?
By using selection list items in the predicates. But that is not very
useful, because in these cases the HAVING clause will act as a WHERE
clause, so you could just as well move the predicates from the HAVING to
the WHERE clause
> WHY?
Because the SQL standard allows it. I can see no reason why it should
not be allowed
HTH,
Gert-Jan
> Guys, you will certainly be doing me a favour and all other
> newbie who access this group but find advanced discussions
> going over the head due to less clear basics.
> Thanks in Advance
> Regards
> Suresh Beniwal|||Thanks all of you
that was sure an insight
but only i missed some practical examples
anyways...thanks
bye
I want to have all ideas about the use
of HAVING and GROUP BY
When either of them can be used?
Can HAVING be used without GROUP BY? HOW? WHY?
Can HAVING be used without an aggregate function? HOW? WHY?
Guys, you will certainly be doing me a favour and all other
newbie who access this group but find advanced discussions
going over the head due to less clear basics.
Thanks in Advance
Regards
Suresh BeniwalRefer Books online
"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message
news:1128424024.788067.225630@.g44g2000cwa.googlegroups.com...
> Hi All
> I want to have all ideas about the use
> of HAVING and GROUP BY
> When either of them can be used?
> Can HAVING be used without GROUP BY? HOW? WHY?
> Can HAVING be used without an aggregate function? HOW? WHY?
> Guys, you will certainly be doing me a favour and all other
> newbie who access this group but find advanced discussions
> going over the head due to less clear basics.
> Thanks in Advance
> Regards
> Suresh Beniwal
>|||Look in the BOl for further details about GROUP BY and HAVING. In
Common using Group by groups the resultset for the specified columns:
Select companyName
>From Customer
Group by companyName
HAVING is used to filter down the grouped result by the expression you
issue within the HAVING clause (e.g. HAVING COUNT(*) > 1, showing only
these values that appear more than one time within
Select companyName
>From Customer
Group by companyName
Having COUNT(*) > 1
Samples out there, e.g.
http://msdn.microsoft.com/library/d...r />
_1l4j.asp
HTH, Jens Suessmeyer.|||SureshBeniwal wrote:
> Hi All
> I want to have all ideas about the use
> of HAVING and GROUP BY
Check out BOL, and posts like this:
http://groups.google.nl/group/micro...de=source&hl=nl
> When either of them can be used?
GROUP BY if you want to aggregate information on groups of data. HAVING
if you want to filter the result based on the aggregated information. If
you want to filter the result based on base columns, then I suggest you
use the WHERE clause
> Can HAVING be used without GROUP BY?
Yes
> HOW?
By omitting the GROUP BY clause, and by only specifying aggregate
functions
> WHY?
Because the entire selection will be interpreted as a single group. This
is also why non-aggregate functions are not allowed when specifying a
HAVING clause without GROUP BY clause
> Can HAVING be used without an aggregate function?
Yes
> HOW?
By using selection list items in the predicates. But that is not very
useful, because in these cases the HAVING clause will act as a WHERE
clause, so you could just as well move the predicates from the HAVING to
the WHERE clause
> WHY?
Because the SQL standard allows it. I can see no reason why it should
not be allowed
HTH,
Gert-Jan
> Guys, you will certainly be doing me a favour and all other
> newbie who access this group but find advanced discussions
> going over the head due to less clear basics.
> Thanks in Advance
> Regards
> Suresh Beniwal|||Thanks all of you
that was sure an insight
but only i missed some practical examples
anyways...thanks
bye
Having 1 user in 2 NT groups causing problems
Hopefully this isnt by design...
I have a user who is a member of 2 NT groups. Both these
groups have access to one of our SQL 2k boxes.
Group A only has access to DB1 as dbo.
Group B only has access to DB2 as dbo.
When this user connects and logs onto DB2 it doesnt work
as it says he doesnt have enough permissions. I'm guessing
its because its only checked Group A?
Short of rejigging our NT groups is there a way round
this? Or have I done something stupid?
SQL Server 2000 sp3a, NT4. User connecting from NT4 client.
Thanks in advance.
Mike.Has Group A been specifically denied any permissions in DB2?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Watson" <mike@.REMOVE.prog99.com> wrote in message
news:ac7b01c43696$7e46f3f0$a501280a@.phx.gbl...
Hopefully this isnt by design...
I have a user who is a member of 2 NT groups. Both these
groups have access to one of our SQL 2k boxes.
Group A only has access to DB1 as dbo.
Group B only has access to DB2 as dbo.
When this user connects and logs onto DB2 it doesnt work
as it says he doesnt have enough permissions. I'm guessing
its because its only checked Group A?
Short of rejigging our NT groups is there a way round
this? Or have I done something stupid?
SQL Server 2000 sp3a, NT4. User connecting from NT4 client.
Thanks in advance.
Mike.|||>--Original Message--
>Has Group A been specifically denied any permissions in
DB2?
>--
>Tom
Thanks for the swift response Tom. No group A has been
neither denied or granted any access to DB2.
I'm beginning to wonder if group2 has had something
revoked further up the chain somewhere in the domain.|||Does this particular user have any denied permissions in DB2?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Watson" <mike@.remove.prog99.som> wrote in message
news:a9a001c43699$b28eb690$a401280a@.phx.gbl...
>--Original Message--
>Has Group A been specifically denied any permissions in
DB2?
>--
>Tom
Thanks for the swift response Tom. No group A has been
neither denied or granted any access to DB2.
I'm beginning to wonder if group2 has had something
revoked further up the chain somewhere in the domain.|||
>--Original Message--
>Does this particular user have any denied permissions in
DB2?
>--
>Tom
Nothing immediately jumps out at me. As far as I can see
they are just dbo with no other extra bits.|||What sort of message are you getting?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<anonymous@.discussions.microsoft.com> wrote in message
news:ada101c4369c$64a21b40$a001280a@.phx.gbl...
>--Original Message--
>Does this particular user have any denied permissions in
DB2?
>--
>Tom
Nothing immediately jumps out at me. As far as I can see
they are just dbo with no other extra bits.|||
>--Original Message--
>What sort of message are you getting?
>--
>Tom
Unfortunately its going home time here in Scotland and the
weather is surprisingly good so they have gone home. I'll
liase with them tomorrow and see exactly what message they
are getting.|||Glad it worked out. In that case, Group B would not have been able to
access its own database either.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Mike Watson" <mike@.remove.prog99.com> wrote in message
news:5F75D6D5-76F5-47B3-8858-83FAC6FABFF5@.microsoft.com...
Sorted!
NT group B hadnt been granted access to the NT server. Did that and it all
worked smoothly.
thanks for your assistance.
I have a user who is a member of 2 NT groups. Both these
groups have access to one of our SQL 2k boxes.
Group A only has access to DB1 as dbo.
Group B only has access to DB2 as dbo.
When this user connects and logs onto DB2 it doesnt work
as it says he doesnt have enough permissions. I'm guessing
its because its only checked Group A?
Short of rejigging our NT groups is there a way round
this? Or have I done something stupid?
SQL Server 2000 sp3a, NT4. User connecting from NT4 client.
Thanks in advance.
Mike.Has Group A been specifically denied any permissions in DB2?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Watson" <mike@.REMOVE.prog99.com> wrote in message
news:ac7b01c43696$7e46f3f0$a501280a@.phx.gbl...
Hopefully this isnt by design...
I have a user who is a member of 2 NT groups. Both these
groups have access to one of our SQL 2k boxes.
Group A only has access to DB1 as dbo.
Group B only has access to DB2 as dbo.
When this user connects and logs onto DB2 it doesnt work
as it says he doesnt have enough permissions. I'm guessing
its because its only checked Group A?
Short of rejigging our NT groups is there a way round
this? Or have I done something stupid?
SQL Server 2000 sp3a, NT4. User connecting from NT4 client.
Thanks in advance.
Mike.|||>--Original Message--
>Has Group A been specifically denied any permissions in
DB2?
>--
>Tom
Thanks for the swift response Tom. No group A has been
neither denied or granted any access to DB2.
I'm beginning to wonder if group2 has had something
revoked further up the chain somewhere in the domain.|||Does this particular user have any denied permissions in DB2?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Watson" <mike@.remove.prog99.som> wrote in message
news:a9a001c43699$b28eb690$a401280a@.phx.gbl...
>--Original Message--
>Has Group A been specifically denied any permissions in
DB2?
>--
>Tom
Thanks for the swift response Tom. No group A has been
neither denied or granted any access to DB2.
I'm beginning to wonder if group2 has had something
revoked further up the chain somewhere in the domain.|||
>--Original Message--
>Does this particular user have any denied permissions in
DB2?
>--
>Tom
Nothing immediately jumps out at me. As far as I can see
they are just dbo with no other extra bits.|||What sort of message are you getting?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
<anonymous@.discussions.microsoft.com> wrote in message
news:ada101c4369c$64a21b40$a001280a@.phx.gbl...
>--Original Message--
>Does this particular user have any denied permissions in
DB2?
>--
>Tom
Nothing immediately jumps out at me. As far as I can see
they are just dbo with no other extra bits.|||
>--Original Message--
>What sort of message are you getting?
>--
>Tom
Unfortunately its going home time here in Scotland and the
weather is surprisingly good so they have gone home. I'll
liase with them tomorrow and see exactly what message they
are getting.|||Glad it worked out. In that case, Group B would not have been able to
access its own database either.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Mike Watson" <mike@.remove.prog99.com> wrote in message
news:5F75D6D5-76F5-47B3-8858-83FAC6FABFF5@.microsoft.com...
Sorted!
NT group B hadnt been granted access to the NT server. Did that and it all
worked smoothly.
thanks for your assistance.
Friday, March 23, 2012
have lost the ability to get in
I never, ever cross-post, but I am desparate. This was placed in the
sqlserver.server group earlier today.
I installed SQL 2005 on a new machine last week. Created a database using
scripts. Worked fine for a few days. It's not on my machine, but on a
Win2003 Vitrual Server instance which I get to remotely.
Yesterday, I attached a database that had been created on another machine,
with SQL 2000. Seemed to work fine. I closed SQL Server to work on something
else, then opened it back up. It won't let me in now. It says that it
doesn't allow remote connections, even though I am doing it from the same
machine that it resides on. (Well, it's remote in that I am using Terminal
Services, but you know what I mean).
Text of the message is below the line at the bottom of this.
I tried changing the way I log in, including Windows Authentication, SQL
Server authentication, using my network name, and then using the user name I
created just for this thing when I installed SQL Server. I alwasy get the
same message. I also tried connecting remotely from another machine.
I was able to use this same machine to connect to another SQL Server
instance on the network, so it's apparent that the installation of SQL
Server isn't messed up.
I know that I will probably get asked about an admin username and password,
but I don't know what mine is. I tried sa with no password, but it didn't
work.
This is really causing some problems and I have something due at noon today.
Any help appreciated.
If I uninstalled SQL Server and re-install, will that let me in then?
-----
TITLE: Connect to Server
Cannot connect to SERV423.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (Microsoft SQL Server, Error: 2)
For help, click:
http://go.microsoft.com/fwlink?ProdN...2&LinkId=20476
BUTTONS:
OK
I stumbled across the fact that two of the services weren't running, despite
their being set to automatic. Not sure why, but at any rate, I started them
and I can get in now.
While I am happy that I am in, I sure wish MS would have given a more
intuitive error message; would have saved me a few hours.
|||Hi middletree,
Can you please tell use the exact services that needed to be started in
order for you to get this to work. I am having the exact same issue and
i've googled hi and lo with no apparent solution.
Thanks,
Brendan
middletree wrote:
> I stumbled across the fact that two of the services weren't running, despite
> their being set to automatic. Not sure why, but at any rate, I started them
> and I can get in now.
> While I am happy that I am in, I sure wish MS would have given a more
> intuitive error message; would have saved me a few hours.
sqlserver.server group earlier today.
I installed SQL 2005 on a new machine last week. Created a database using
scripts. Worked fine for a few days. It's not on my machine, but on a
Win2003 Vitrual Server instance which I get to remotely.
Yesterday, I attached a database that had been created on another machine,
with SQL 2000. Seemed to work fine. I closed SQL Server to work on something
else, then opened it back up. It won't let me in now. It says that it
doesn't allow remote connections, even though I am doing it from the same
machine that it resides on. (Well, it's remote in that I am using Terminal
Services, but you know what I mean).
Text of the message is below the line at the bottom of this.
I tried changing the way I log in, including Windows Authentication, SQL
Server authentication, using my network name, and then using the user name I
created just for this thing when I installed SQL Server. I alwasy get the
same message. I also tried connecting remotely from another machine.
I was able to use this same machine to connect to another SQL Server
instance on the network, so it's apparent that the installation of SQL
Server isn't messed up.
I know that I will probably get asked about an admin username and password,
but I don't know what mine is. I tried sa with no password, but it didn't
work.
This is really causing some problems and I have something due at noon today.
Any help appreciated.
If I uninstalled SQL Server and re-install, will that let me in then?
-----
TITLE: Connect to Server
Cannot connect to SERV423.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (Microsoft SQL Server, Error: 2)
For help, click:
http://go.microsoft.com/fwlink?ProdN...2&LinkId=20476
BUTTONS:
OK
I stumbled across the fact that two of the services weren't running, despite
their being set to automatic. Not sure why, but at any rate, I started them
and I can get in now.
While I am happy that I am in, I sure wish MS would have given a more
intuitive error message; would have saved me a few hours.
|||Hi middletree,
Can you please tell use the exact services that needed to be started in
order for you to get this to work. I am having the exact same issue and
i've googled hi and lo with no apparent solution.
Thanks,
Brendan
middletree wrote:
> I stumbled across the fact that two of the services weren't running, despite
> their being set to automatic. Not sure why, but at any rate, I started them
> and I can get in now.
> While I am happy that I am in, I sure wish MS would have given a more
> intuitive error message; would have saved me a few hours.
have lost the ability to get in
I never, ever cross-post, but I am desparate. This was placed in the
sqlserver.server group earlier today.
I installed SQL 2005 on a new machine last week. Created a database using
scripts. Worked fine for a few days. It's not on my machine, but on a
Win2003 Vitrual Server instance which I get to remotely.
Yesterday, I attached a database that had been created on another machine,
with SQL 2000. Seemed to work fine. I closed SQL Server to work on something
else, then opened it back up. It won't let me in now. It says that it
doesn't allow remote connections, even though I am doing it from the same
machine that it resides on. (Well, it's remote in that I am using Terminal
Services, but you know what I mean).
Text of the message is below the line at the bottom of this.
I tried changing the way I log in, including Windows Authentication, SQL
Server authentication, using my network name, and then using the user name I
created just for this thing when I installed SQL Server. I alwasy get the
same message. I also tried connecting remotely from another machine.
I was able to use this same machine to connect to another SQL Server
instance on the network, so it's apparent that the installation of SQL
Server isn't messed up.
I know that I will probably get asked about an admin username and password,
but I don't know what mine is. I tried sa with no password, but it didn't
work.
This is really causing some problems and I have something due at noon today.
Any help appreciated.
If I uninstalled SQL Server and re-install, will that let me in then?
----
--
TITLE: Connect to Server
--
Cannot connect to SERV423.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (Microsoft SQL Server, Error: 2)
For help, click:
tID=2&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...=2&LinkId=20476
BUTTONS:
OK
--I stumbled across the fact that two of the services weren't running, despite
their being set to automatic. Not sure why, but at any rate, I started them
and I can get in now.
While I am happy that I am in, I sure wish MS would have given a more
intuitive error message; would have saved me a few hours.|||Hi middletree,
Can you please tell use the exact services that needed to be started in
order for you to get this to work. I am having the exact same issue and
i've googled hi and lo with no apparent solution.
Thanks,
Brendan
middletree wrote:
> I stumbled across the fact that two of the services weren't running, despi
te
> their being set to automatic. Not sure why, but at any rate, I started the
m
> and I can get in now.
> While I am happy that I am in, I sure wish MS would have given a more
> intuitive error message; would have saved me a few hours.
sqlserver.server group earlier today.
I installed SQL 2005 on a new machine last week. Created a database using
scripts. Worked fine for a few days. It's not on my machine, but on a
Win2003 Vitrual Server instance which I get to remotely.
Yesterday, I attached a database that had been created on another machine,
with SQL 2000. Seemed to work fine. I closed SQL Server to work on something
else, then opened it back up. It won't let me in now. It says that it
doesn't allow remote connections, even though I am doing it from the same
machine that it resides on. (Well, it's remote in that I am using Terminal
Services, but you know what I mean).
Text of the message is below the line at the bottom of this.
I tried changing the way I log in, including Windows Authentication, SQL
Server authentication, using my network name, and then using the user name I
created just for this thing when I installed SQL Server. I alwasy get the
same message. I also tried connecting remotely from another machine.
I was able to use this same machine to connect to another SQL Server
instance on the network, so it's apparent that the installation of SQL
Server isn't messed up.
I know that I will probably get asked about an admin username and password,
but I don't know what mine is. I tried sa with no password, but it didn't
work.
This is really causing some problems and I have something due at noon today.
Any help appreciated.
If I uninstalled SQL Server and re-install, will that let me in then?
----
--
TITLE: Connect to Server
--
Cannot connect to SERV423.
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server) (Microsoft SQL Server, Error: 2)
For help, click:
tID=2&LinkId=20476" target="_blank">http://go.microsoft.com/fwlink?Prod...=2&LinkId=20476
BUTTONS:
OK
--I stumbled across the fact that two of the services weren't running, despite
their being set to automatic. Not sure why, but at any rate, I started them
and I can get in now.
While I am happy that I am in, I sure wish MS would have given a more
intuitive error message; would have saved me a few hours.|||Hi middletree,
Can you please tell use the exact services that needed to be started in
order for you to get this to work. I am having the exact same issue and
i've googled hi and lo with no apparent solution.
Thanks,
Brendan
middletree wrote:
> I stumbled across the fact that two of the services weren't running, despi
te
> their being set to automatic. Not sure why, but at any rate, I started the
m
> and I can get in now.
> While I am happy that I am in, I sure wish MS would have given a more
> intuitive error message; would have saved me a few hours.
Wednesday, March 21, 2012
Hash join
Can any one explain me this "Option [hash join]"...
what that Option [hash join] will effect in this query.
select * from [group] inner join patientgroup on PA_PatientID = PG_PatientID
Option [hash join]
Thanks
Noor
Noor wrote:
> Can any one explain me this "Option [hash join]"...
> what that Option [hash join] will effect in this query.
> select * from [group] inner join patientgroup on PA_PatientID =
> PG_PatientID Option [hash join]
>
> Thanks
> Noor
It will force SQL Server to use a hash join on the tables instead of a
nested loop join or a merge join. Generally, you want to let SQL Server
make up its own mind about what the best join operation is. Essentially,
a hash join forces SQL Server to create hash values on both tables in
order to determine the matching values.
To see how it affects your query test it in Query Analyzer with and
without the hint.
See Understanding Hash Joins in the BOL.
David G.
what that Option [hash join] will effect in this query.
select * from [group] inner join patientgroup on PA_PatientID = PG_PatientID
Option [hash join]
Thanks
Noor
Noor wrote:
> Can any one explain me this "Option [hash join]"...
> what that Option [hash join] will effect in this query.
> select * from [group] inner join patientgroup on PA_PatientID =
> PG_PatientID Option [hash join]
>
> Thanks
> Noor
It will force SQL Server to use a hash join on the tables instead of a
nested loop join or a merge join. Generally, you want to let SQL Server
make up its own mind about what the best join operation is. Essentially,
a hash join forces SQL Server to create hash values on both tables in
order to determine the matching values.
To see how it affects your query test it in Query Analyzer with and
without the hint.
See Understanding Hash Joins in the BOL.
David G.
Monday, February 27, 2012
Handling nulls in matrix sub totals
I have a matrix where I am using the inscope function to return different
calculations depending on which group it is in (ie the detail is just the
value and the sub totals are sum(values)).
In for the subtotal group I have used:
iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
sum(Fields!CurrentCount.Value)
But this is still returning blanks where there are no values in the group to
sum together.
Can anyone suggest how to get around this issue as it ?
TIA
Andrewyou might try iif(sum(Fields!CurrentCount.Value = nothing)
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||also if this formula you put on here looks just like the one you have in the
expression then you need to add a perenthesis onto the end. I tried to
recreate your problem and I get a 0. Weird
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||and aslo maybe you should try to set the null value to 0 before you sum it.
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>
calculations depending on which group it is in (ie the detail is just the
value and the sub totals are sum(values)).
In for the subtotal group I have used:
iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
sum(Fields!CurrentCount.Value)
But this is still returning blanks where there are no values in the group to
sum together.
Can anyone suggest how to get around this issue as it ?
TIA
Andrewyou might try iif(sum(Fields!CurrentCount.Value = nothing)
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||also if this formula you put on here looks just like the one you have in the
expression then you need to add a perenthesis onto the end. I tried to
recreate your problem and I get a 0. Weird
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>|||and aslo maybe you should try to set the null value to 0 before you sum it.
"Andrew Murphy" <AndrewMurphy@.discussions.microsoft.com> wrote in message
news:56B0AB62-6CDE-4F41-BF39-AC72FABAC2C0@.microsoft.com...
>I have a matrix where I am using the inscope function to return different
> calculations depending on which group it is in (ie the detail is just the
> value and the sub totals are sum(values)).
> In for the subtotal group I have used:
> iif(isnothing(sum(Fields!CurrentCount.Value)), 0,
> sum(Fields!CurrentCount.Value)
> But this is still returning blanks where there are no values in the group
> to
> sum together.
> Can anyone suggest how to get around this issue as it ?
> TIA
> Andrew
>
Subscribe to:
Posts (Atom)