Hi,
Using SQL Server 2000
I have a table like this
Results
ResultID int primary key
ProductID char(10)
RetailerID int
RetailerPrice money
Some sample data might be
ResultID ProductID RetailerID RetailerPrice
1 1231231234 1 9.99
2 1231231234 2 19.99
3 1231231234 3 12.99
4 1231231235 1 11.99
5 1231231235 2 13.99
6 1231231235 3 3.99
I want to return the lowest price and it's resultid for a list of products.
I've got as far as
SELECT ProductID, MIN(RetailerPrice)
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID
which returns
ProductID RetailerPrice
1231231235 3.99
1231231234 9.99
What I want is
ProductID RetailerPrice ResultID
1231231235 3.99 6
1231231234 9.99 1
I've tried
SELECT ProductID, MIN(RetailerPrice), ResultID
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID, ResultID
but this returns every price for each product. I've tried various group by
clauses but have hit a brick wall. If anyone can point me in the right
direction I'd appreciate it.
Cheers,
JonLooks like a derived table:
SELECT
R.*
FROM
Results R
JOIN
(
SELECT ProductID, MIN(RetailerPrice) AS RetailerPrice
FROM Results
GROUP BY ProductID
) AS X ON X.ProductID = R.ProductID
AND X.RetailerPrice = R.RetailerPrice
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Jon Spivey" <jons@.mvps.org> wrote in message
news:Ow$0XWdgGHA.4712@.TK2MSFTNGP05.phx.gbl...
Hi,
Using SQL Server 2000
I have a table like this
Results
ResultID int primary key
ProductID char(10)
RetailerID int
RetailerPrice money
Some sample data might be
ResultID ProductID RetailerID RetailerPrice
1 1231231234 1 9.99
2 1231231234 2 19.99
3 1231231234 3 12.99
4 1231231235 1 11.99
5 1231231235 2 13.99
6 1231231235 3 3.99
I want to return the lowest price and it's resultid for a list of products.
I've got as far as
SELECT ProductID, MIN(RetailerPrice)
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID
which returns
ProductID RetailerPrice
1231231235 3.99
1231231234 9.99
What I want is
ProductID RetailerPrice ResultID
1231231235 3.99 6
1231231234 9.99 1
I've tried
SELECT ProductID, MIN(RetailerPrice), ResultID
FROM Results
WHERE ProductID IN('1231231234', '1231231235')
GROUP BY ProductID, ResultID
but this returns every price for each product. I've tried various group by
clauses but have hit a brick wall. If anyone can point me in the right
direction I'd appreciate it.
Cheers,
Jon|||Perfect. Thanks Tom.
Jon
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23WI8MbdgGHA.3900@.TK2MSFTNGP05.phx.gbl...
> Looks like a derived table:
> SELECT
> R.*
> FROM
> Results R
> JOIN
> (
> SELECT ProductID, MIN(RetailerPrice) AS RetailerPrice
> FROM Results
> GROUP BY ProductID
> ) AS X ON X.ProductID = R.ProductID
> AND X.RetailerPrice = R.RetailerPrice
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Jon Spivey" <jons@.mvps.org> wrote in message
> news:Ow$0XWdgGHA.4712@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Using SQL Server 2000
> I have a table like this
> Results
> ResultID int primary key
> ProductID char(10)
> RetailerID int
> RetailerPrice money
> Some sample data might be
> ResultID ProductID RetailerID RetailerPrice
> 1 1231231234 1 9.99
> 2 1231231234 2 19.99
> 3 1231231234 3 12.99
> 4 1231231235 1 11.99
> 5 1231231235 2 13.99
> 6 1231231235 3 3.99
> I want to return the lowest price and it's resultid for a list of
> products.
> I've got as far as
> SELECT ProductID, MIN(RetailerPrice)
> FROM Results
> WHERE ProductID IN('1231231234', '1231231235')
> GROUP BY ProductID
> which returns
> ProductID RetailerPrice
> 1231231235 3.99
> 1231231234 9.99
> What I want is
> ProductID RetailerPrice ResultID
> 1231231235 3.99 6
> 1231231234 9.99 1
> I've tried
> SELECT ProductID, MIN(RetailerPrice), ResultID
> FROM Results
> WHERE ProductID IN('1231231234', '1231231235')
> GROUP BY ProductID, ResultID
> but this returns every price for each product. I've tried various group by
> clauses but have hit a brick wall. If anyone can point me in the right
> direction I'd appreciate it.
> Cheers,
> Jon
>
Showing posts with label group. Show all posts
Showing posts with label group. Show all posts
Wednesday, March 28, 2012
Query
Greetings,
I have three queries that I have to put together and they are based on the SELECT and GROUP BY.
1st Query requirement:
group the Employees by Job Code. Use a SELECT query.and modify the SQL
My answer is (and I'm not able to see the actual names and job codes):
SELECT 'EmployeeID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'JobTitleCode' AS JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, JobTitleCode
ORDER BY JobTitleCode;
2nd Query requirement:
group the Employees by Salary. Use a SELECT Query and modify the SQL
My answer is (and very similar to the above, I'm not seeing the information):
SELECT 'Employee ID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, [Salary]
ORDER BY [Salary];
Last Query requirement:
group the Employees by Salary within their Job Code. Use a SELECT Query and modify the SQL
My rough draft answer (not really sure at this point :-( is:
SELECT 'First Name' AS FirstName, 'Last Name' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY 'Salary', 'Job Title Code';
Any words of wisdom would be greatly appreciated.
Thanks for your time!Looks like a homework...Are you sure the fieldnames are spelled correctly? I don't think your queries will run...
1st - ...will give an error on invalid field name
2nd - ...will do the same
3rd - ...same difference
But conceptually ... I don't think you are answering the questions. You need to loose GROUP BY and reverse the order of the fields in ORDER BY clause.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?
Any other words of advice?
Thanks for your time.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?
Any other words of advice?
Thanks for your time.
So, you're saying that 'EmployeeID' and 'Employee ID' are the same field or 2 different fields?|||You are correct and I've modified to the new statements below:
Query 1:
SELECT EmployeeID, FirstName, JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, JobTitleCode
ORDER BY JobTitleCode;
Query 2:
SELECT EmployeeID, FirstName, [Salary]
FROM Employees_Table
WHERE Salary>45000
GROUP BY EmployeeID, FirstName, [Salary]
ORDER BY [Salary];
I hope I'm going in the correct direction with my answers?
Thanks for your responses!|||See my first post (loose GROUP BY, because you're not doing any aggregation)sql
I have three queries that I have to put together and they are based on the SELECT and GROUP BY.
1st Query requirement:
group the Employees by Job Code. Use a SELECT query.and modify the SQL
My answer is (and I'm not able to see the actual names and job codes):
SELECT 'EmployeeID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'JobTitleCode' AS JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, JobTitleCode
ORDER BY JobTitleCode;
2nd Query requirement:
group the Employees by Salary. Use a SELECT Query and modify the SQL
My answer is (and very similar to the above, I'm not seeing the information):
SELECT 'Employee ID' AS EmployeeID, 'FirstName' AS FirstName, 'LastName' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY EmployeeID, FirstName, LastName, [Salary]
ORDER BY [Salary];
Last Query requirement:
group the Employees by Salary within their Job Code. Use a SELECT Query and modify the SQL
My rough draft answer (not really sure at this point :-( is:
SELECT 'First Name' AS FirstName, 'Last Name' AS LastName, 'Salary' AS Salary
FROM Employees_Table
GROUP BY 'Salary', 'Job Title Code';
Any words of wisdom would be greatly appreciated.
Thanks for your time!Looks like a homework...Are you sure the fieldnames are spelled correctly? I don't think your queries will run...
1st - ...will give an error on invalid field name
2nd - ...will do the same
3rd - ...same difference
But conceptually ... I don't think you are answering the questions. You need to loose GROUP BY and reverse the order of the fields in ORDER BY clause.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?
Any other words of advice?
Thanks for your time.|||Yes, the field names in my database are spelled the same exact way as I've listed them in the SQL statements?
Any other words of advice?
Thanks for your time.
So, you're saying that 'EmployeeID' and 'Employee ID' are the same field or 2 different fields?|||You are correct and I've modified to the new statements below:
Query 1:
SELECT EmployeeID, FirstName, JobTitleCode
FROM Employees_Table
GROUP BY EmployeeID, FirstName, JobTitleCode
ORDER BY JobTitleCode;
Query 2:
SELECT EmployeeID, FirstName, [Salary]
FROM Employees_Table
WHERE Salary>45000
GROUP BY EmployeeID, FirstName, [Salary]
ORDER BY [Salary];
I hope I'm going in the correct direction with my answers?
Thanks for your responses!|||See my first post (loose GROUP BY, because you're not doing any aggregation)sql
Monday, March 26, 2012
query
Hi, how to write a query can group customer sale by month? please help.js wrote:
> Hi, how to write a query can group customer sale by month? please
> help.
www.aspfaq.com/5006
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Try,
use northwind
go
select
c.customerid,
year(oh.orderdate) as col_year,
month(oh.orderdate) as col_month,
sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice))
from
customers as c
inner join
orders as oh
on c.customerid = oh.customerid
inner join
[order details] as od
on oh.orderid = od.orderid
group by
c.customerid,
year(oh.orderdate),
month(oh.orderdate)
order by
c.customerid,
col_year,
col_month
go
-- or
select
c.customerid,
convert(char(6), oh.orderdate, 112) as col_yyyymm,
sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice))
from
customers as c
inner join
orders as oh
on c.customerid = oh.customerid
inner join
[order details] as od
on oh.orderid = od.orderid
group by
c.customerid,
convert(char(6), oh.orderdate, 112)
order by
c.customerid,
col_yyyymm,
go
AMB
"js" wrote:
> Hi, how to write a query can group customer sale by month? please help.
>
>|||SELECT Count(*), SUM(st.SalesAmount), Month(st.SalesDateField),
Year(st.SalesDateField)
FROM SalesTable st
--WHERE st.CustomerID = 1234
GROUP BY Month(st.SalesDateField), Year(st.SalesDateField)
"js" <js@.someone@.hotmail.com> wrote in message
news:er7mNsxAFHA.3940@.TK2MSFTNGP09.phx.gbl...
> Hi, how to write a query can group customer sale by month? please help.
>
>|||Thanks all,
Can I force it to output as follow(even 1997, Feb doesn't have data):
1997 1 503.5000
1997 2
1997 3 340.0000
1997 4
1997 5 340.0000
1997 6
"David Buchanan" <dbuch328@.cox.net> wrote in message
news:RfyJd.23223$EG1.4463@.lakeread04...
> SELECT Count(*), SUM(st.SalesAmount), Month(st.SalesDateField),
> Year(st.SalesDateField)
> FROM SalesTable st
> --WHERE st.CustomerID = 1234
> GROUP BY Month(st.SalesDateField), Year(st.SalesDateField)
> "js" <js@.someone@.hotmail.com> wrote in message
> news:er7mNsxAFHA.3940@.TK2MSFTNGP09.phx.gbl...
>|||Try,
use northwind
go
select
c.customerid,
p.col_year,
p.col_month,
sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice)) as
sales
from
customers as c
cross join
(
select
*
from
(
select distinct year(orderdate) from orders
) as y(col_year)
cross join
(
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) as m(col_month)
) as p(col_year, col_month)
left join
orders as oh
on c.customerid = oh.customerid and year(oh.orderdate) = p.col_year and
month(orderdate) = p.col_month
left join
[order details] as od
on oh.orderid = od.orderid
where
c.customerid = 'alfki'
group by
c.customerid,
p.col_year,
p.col_month
order by
c.customerid,
p.col_year,
p.col_month
go
AMB
"js" wrote:
> Thanks all,
> Can I force it to output as follow(even 1997, Feb doesn't have data):
> 1997 1 503.5000
> 1997 2
> 1997 3 340.0000
> 1997 4
> 1997 5 340.0000
> 1997 6
>
> "David Buchanan" <dbuch328@.cox.net> wrote in message
> news:RfyJd.23223$EG1.4463@.lakeread04...
>
>|||Thanks Alejandro...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:706A502C-6657-4F00-BA5F-3B3A4B7E295C@.microsoft.com...
> Try,
> use northwind
> go
> select
> c.customerid,
> p.col_year,
> p.col_month,
> sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice)) as
> sales
> from
> customers as c
> cross join
> (
> select
> *
> from
> (
> select distinct year(orderdate) from orders
> ) as y(col_year)
> cross join
> (
> select 1
> union all
> select 2
> union all
> select 3
> union all
> select 4
> union all
> select 5
> union all
> select 6
> union all
> select 7
> union all
> select 8
> union all
> select 9
> union all
> select 10
> union all
> select 11
> union all
> select 12
> ) as m(col_month)
> ) as p(col_year, col_month)
> left join
> orders as oh
> on c.customerid = oh.customerid and year(oh.orderdate) = p.col_year and
> month(orderdate) = p.col_month
> left join
> [order details] as od
> on oh.orderid = od.orderid
> where
> c.customerid = 'alfki'
> group by
> c.customerid,
> p.col_year,
> p.col_month
> order by
> c.customerid,
> p.col_year,
> p.col_month
> go
>
> AMB
> Hi, how to write a query can group customer sale by month? please
> help.
www.aspfaq.com/5006
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Try,
use northwind
go
select
c.customerid,
year(oh.orderdate) as col_year,
month(oh.orderdate) as col_month,
sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice))
from
customers as c
inner join
orders as oh
on c.customerid = oh.customerid
inner join
[order details] as od
on oh.orderid = od.orderid
group by
c.customerid,
year(oh.orderdate),
month(oh.orderdate)
order by
c.customerid,
col_year,
col_month
go
-- or
select
c.customerid,
convert(char(6), oh.orderdate, 112) as col_yyyymm,
sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice))
from
customers as c
inner join
orders as oh
on c.customerid = oh.customerid
inner join
[order details] as od
on oh.orderid = od.orderid
group by
c.customerid,
convert(char(6), oh.orderdate, 112)
order by
c.customerid,
col_yyyymm,
go
AMB
"js" wrote:
> Hi, how to write a query can group customer sale by month? please help.
>
>|||SELECT Count(*), SUM(st.SalesAmount), Month(st.SalesDateField),
Year(st.SalesDateField)
FROM SalesTable st
--WHERE st.CustomerID = 1234
GROUP BY Month(st.SalesDateField), Year(st.SalesDateField)
"js" <js@.someone@.hotmail.com> wrote in message
news:er7mNsxAFHA.3940@.TK2MSFTNGP09.phx.gbl...
> Hi, how to write a query can group customer sale by month? please help.
>
>|||Thanks all,
Can I force it to output as follow(even 1997, Feb doesn't have data):
1997 1 503.5000
1997 2
1997 3 340.0000
1997 4
1997 5 340.0000
1997 6
"David Buchanan" <dbuch328@.cox.net> wrote in message
news:RfyJd.23223$EG1.4463@.lakeread04...
> SELECT Count(*), SUM(st.SalesAmount), Month(st.SalesDateField),
> Year(st.SalesDateField)
> FROM SalesTable st
> --WHERE st.CustomerID = 1234
> GROUP BY Month(st.SalesDateField), Year(st.SalesDateField)
> "js" <js@.someone@.hotmail.com> wrote in message
> news:er7mNsxAFHA.3940@.TK2MSFTNGP09.phx.gbl...
>|||Try,
use northwind
go
select
c.customerid,
p.col_year,
p.col_month,
sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice)) as
sales
from
customers as c
cross join
(
select
*
from
(
select distinct year(orderdate) from orders
) as y(col_year)
cross join
(
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
union all
select 12
) as m(col_month)
) as p(col_year, col_month)
left join
orders as oh
on c.customerid = oh.customerid and year(oh.orderdate) = p.col_year and
month(orderdate) = p.col_month
left join
[order details] as od
on oh.orderid = od.orderid
where
c.customerid = 'alfki'
group by
c.customerid,
p.col_year,
p.col_month
order by
c.customerid,
p.col_year,
p.col_month
go
AMB
"js" wrote:
> Thanks all,
> Can I force it to output as follow(even 1997, Feb doesn't have data):
> 1997 1 503.5000
> 1997 2
> 1997 3 340.0000
> 1997 4
> 1997 5 340.0000
> 1997 6
>
> "David Buchanan" <dbuch328@.cox.net> wrote in message
> news:RfyJd.23223$EG1.4463@.lakeread04...
>
>|||Thanks Alejandro...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:706A502C-6657-4F00-BA5F-3B3A4B7E295C@.microsoft.com...
> Try,
> use northwind
> go
> select
> c.customerid,
> p.col_year,
> p.col_month,
> sum(od.Quantity * (cast((1.00 - od.Discount) as money) * od.UnitPrice)) as
> sales
> from
> customers as c
> cross join
> (
> select
> *
> from
> (
> select distinct year(orderdate) from orders
> ) as y(col_year)
> cross join
> (
> select 1
> union all
> select 2
> union all
> select 3
> union all
> select 4
> union all
> select 5
> union all
> select 6
> union all
> select 7
> union all
> select 8
> union all
> select 9
> union all
> select 10
> union all
> select 11
> union all
> select 12
> ) as m(col_month)
> ) as p(col_year, col_month)
> left join
> orders as oh
> on c.customerid = oh.customerid and year(oh.orderdate) = p.col_year and
> month(orderdate) = p.col_month
> left join
> [order details] as od
> on oh.orderid = od.orderid
> where
> c.customerid = 'alfki'
> group by
> c.customerid,
> p.col_year,
> p.col_month
> order by
> c.customerid,
> p.col_year,
> p.col_month
> go
>
> AMB
Wednesday, March 21, 2012
queries longer than 5 mins
Hi I have set up a windows group for our Crystal reporters as they sometimes
have code that runs for way to long is there a cmd I can issue that will cut
of any queries going on longer then 5 minutes just for this particular group
.
Not sure if possible
Thanks
for any help
SammySammy
Use SQL Server Profiler to see what is going on. Look at Duration column.
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:8A577E59-8C2D-48B3-8E77-C410FC41C308@.microsoft.com...
> Hi I have set up a windows group for our Crystal reporters as they
> sometimes
> have code that runs for way to long is there a cmd I can issue that will
> cut
> of any queries going on longer then 5 minutes just for this particular
> group.
> Not sure if possible
>
> Thanks
> for any help
> Sammy
have code that runs for way to long is there a cmd I can issue that will cut
of any queries going on longer then 5 minutes just for this particular group
.
Not sure if possible
Thanks
for any help
SammySammy
Use SQL Server Profiler to see what is going on. Look at Duration column.
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:8A577E59-8C2D-48B3-8E77-C410FC41C308@.microsoft.com...
> Hi I have set up a windows group for our Crystal reporters as they
> sometimes
> have code that runs for way to long is there a cmd I can issue that will
> cut
> of any queries going on longer then 5 minutes just for this particular
> group.
> Not sure if possible
>
> Thanks
> for any help
> Sammy
Tuesday, March 20, 2012
Quarter in the group by
How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
> > How can I create stored procedure with data grouped by quarter?
> > I am converting database from Access to SQL and learn SQL in the
same
> > time.
> > In Access that is simple because there is function for that.
> >
> > I was looking in the different news groups and found few answers
like
> > truncating data, but cannot find it it T-SQL.
> >
> > Thank you
> >
> >
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
> > How can I create stored procedure with data grouped by quarter?
> > I am converting database from Access to SQL and learn SQL in the
same
> > time.
> > In Access that is simple because there is function for that.
> >
> > I was looking in the different news groups and found few answers
like
> > truncating data, but cannot find it it T-SQL.
> >
> > Thank you
> >
> >
Quarter in the group by
How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
>
same[vbcol=seagreen]
like[vbcol=seagreen]
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank youHi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegroups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
>
same[vbcol=seagreen]
like[vbcol=seagreen]
Quarter in the group by
How can I create stored procedure with data grouped by quarter?
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank you
Hi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegr oups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
same[vbcol=seagreen]
like[vbcol=seagreen]
I am converting database from Access to SQL and learn SQL in the same
time.
In Access that is simple because there is function for that.
I was looking in the different news groups and found few answers like
truncating data, but cannot find it it T-SQL.
Thank you
Hi
Have a look at
http://www.aspfaq.com/show.asp?id=2519
"schapopa" <areklubinski@.hotmail.com> wrote in message
news:1112871972.138425.259710@.l41g2000cwc.googlegr oups.com...
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Example:
use northwind
go
select
year(orderdate) as col_year,
datepart(quarter, orderdate) as col_quarter,
count(*)
from
dbo.orders
group by
year(orderdate),
datepart(quarter, orderdate)
order by
year(orderdate),
datepart(quarter, orderdate);
AMB
"schapopa" wrote:
> How can I create stored procedure with data grouped by quarter?
> I am converting database from Access to SQL and learn SQL in the same
> time.
> In Access that is simple because there is function for that.
> I was looking in the different news groups and found few answers like
> truncating data, but cannot find it it T-SQL.
> Thank you
>
|||Thank you very much.
Works perfect.
Alejandro Mesa wrote:[vbcol=seagreen]
> Example:
> use northwind
> go
> select
> year(orderdate) as col_year,
> datepart(quarter, orderdate) as col_quarter,
> count(*)
> from
> dbo.orders
> group by
> year(orderdate),
> datepart(quarter, orderdate)
> order by
> year(orderdate),
> datepart(quarter, orderdate);
>
> AMB
>
> "schapopa" wrote:
same[vbcol=seagreen]
like[vbcol=seagreen]
Monday, March 12, 2012
Qry Analyzer Debug broken: XP SP2
I read a post in this group that a hotfix (per KB839280) would take care of
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
TomHi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||This is the error in the event log of the SQL Server system:
Event Type: Error
Event Source: SQLDebugging98
Event Category: None
Event ID: 1
Date: 9/14/2004
Time: 9:45:19 PM
User: N/A
Computer: HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
> > Hi Tom,
> >
> > Thanks for your posting!
> >
> > From your descriptions, I understood that you could not use Query Analyzer
> > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > anything I misunderstood, please feel free to let me know.
> >
> > Based on my scope, as you said it is a remotely debugging ,does it used a
> > linked server? If so, here is another document address the probelm you may
> > meet.
> >
> > You may receive a 7391 error message in SQL Server 2000 when you run a
> > distributed transaction against a linked server after you install Microsoft
> > Windows XP Service Pack 2
> > http://support.microsoft.com/default.aspx?kbid=839279
> >
> > What's the error message when it fails to debugging? Could you tell me how
> > to reporduce it on my machine? I need more detailed information for your
> > scenario, which, I believe, will make us closer to the resolution.
> >
> > Thank you for your patience and cooperation. If you have any questions or
> > concerns, don't hesitate to let me know. We are here to be of assistance!
> >
> >
> > Sincerely yours,
> >
> > Mingqing Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > This posting is provided "as is" with no warranties and confers no rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:
> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> > I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> > this prior to installing XP sp2 on the WinXP system. The sql server is at
> > sp3, and runs on a Windows 2003 system.
> >
> > The message I get when running the debugger is:
> >
> > Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> > on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> > SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> > connection 58.
> >
> > ""Mingqing Cheng [MSFT]"" wrote:
> >
> > > Hi Tom,
> > >
> > > Thanks for your posting!
> > >
> > > From your descriptions, I understood that you could not use Query Analyzer
> > > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > > anything I misunderstood, please feel free to let me know.
> > >
> > > Based on my scope, as you said it is a remotely debugging ,does it used a
> > > linked server? If so, here is another document address the probelm you may
> > > meet.
> > >
> > > You may receive a 7391 error message in SQL Server 2000 when you run a
> > > distributed transaction against a linked server after you install Microsoft
> > > Windows XP Service Pack 2
> > > http://support.microsoft.com/default.aspx?kbid=839279
> > >
> > > What's the error message when it fails to debugging? Could you tell me how
> > > to reporduce it on my machine? I need more detailed information for your
> > > scenario, which, I believe, will make us closer to the resolution.
> > >
> > > Thank you for your patience and cooperation. If you have any questions or
> > > concerns, don't hesitate to let me know. We are here to be of assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Mingqing Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > > This posting is provided "as is" with no warranties and confers no rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
TomHi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||This is the error in the event log of the SQL Server system:
Event Type: Error
Event Source: SQLDebugging98
Event Category: None
Event ID: 1
Date: 9/14/2004
Time: 9:45:19 PM
User: N/A
Computer: HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
> > Hi Tom,
> >
> > Thanks for your posting!
> >
> > From your descriptions, I understood that you could not use Query Analyzer
> > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > anything I misunderstood, please feel free to let me know.
> >
> > Based on my scope, as you said it is a remotely debugging ,does it used a
> > linked server? If so, here is another document address the probelm you may
> > meet.
> >
> > You may receive a 7391 error message in SQL Server 2000 when you run a
> > distributed transaction against a linked server after you install Microsoft
> > Windows XP Service Pack 2
> > http://support.microsoft.com/default.aspx?kbid=839279
> >
> > What's the error message when it fails to debugging? Could you tell me how
> > to reporduce it on my machine? I need more detailed information for your
> > scenario, which, I believe, will make us closer to the resolution.
> >
> > Thank you for your patience and cooperation. If you have any questions or
> > concerns, don't hesitate to let me know. We are here to be of assistance!
> >
> >
> > Sincerely yours,
> >
> > Mingqing Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > This posting is provided "as is" with no warranties and confers no rights.
> > Please reply to newsgroups only, many thanks!
> >
> >|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:
> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
> > I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> > this prior to installing XP sp2 on the WinXP system. The sql server is at
> > sp3, and runs on a Windows 2003 system.
> >
> > The message I get when running the debugger is:
> >
> > Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> > [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> > on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> > SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> > connection 58.
> >
> > ""Mingqing Cheng [MSFT]"" wrote:
> >
> > > Hi Tom,
> > >
> > > Thanks for your posting!
> > >
> > > From your descriptions, I understood that you could not use Query Analyzer
> > > to debug after XP SP2 was upgraded. Have I understood you? If there is
> > > anything I misunderstood, please feel free to let me know.
> > >
> > > Based on my scope, as you said it is a remotely debugging ,does it used a
> > > linked server? If so, here is another document address the probelm you may
> > > meet.
> > >
> > > You may receive a 7391 error message in SQL Server 2000 when you run a
> > > distributed transaction against a linked server after you install Microsoft
> > > Windows XP Service Pack 2
> > > http://support.microsoft.com/default.aspx?kbid=839279
> > >
> > > What's the error message when it fails to debugging? Could you tell me how
> > > to reporduce it on my machine? I need more detailed information for your
> > > scenario, which, I believe, will make us closer to the resolution.
> > >
> > > Thank you for your patience and cooperation. If you have any questions or
> > > concerns, don't hesitate to let me know. We are here to be of assistance!
> > >
> > >
> > > Sincerely yours,
> > >
> > > Mingqing Cheng
> > >
> > > Online Partner Support Specialist
> > > Partner Support Group
> > > Microsoft Global Technical Support Center
> > > ---
> > > Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> > > This posting is provided "as is" with no warranties and confers no rights.
> > > Please reply to newsgroups only, many thanks!
> > >
> > >
>
Qry Analyzer Debug broken: XP SP2
I read a post in this group that a hotfix (per KB839280) would take care of
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
Tom
Hi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||This is the error in the event log of the SQL Server system:
Event Type:Error
Event Source:SQLDebugging98
Event Category:None
Event ID:1
Date:9/14/2004
Time:9:45:19 PM
User:N/A
Computer:HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I have tried all the suggestions but the behavior remains the same: the
debugger connects and then runs thru the procedure beginning to end without
stopping, providing variable info, etc.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...[vbcol=seagreen]
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:
> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
>
this (posted by an MS support person). The post indicated this fix would take
care of the Visual Studio issue, and well as Query Analyzer.
I got the hotfix and installed it on my development server, however my XP
sp2 system still cannot remotely debug the server. I have always been able to
do this, until I installed XP sp2.
Any suggestions? Has anyone gotten remote debugging to work post XP sp2
installation?
Thanks for your assistance,
Tom
Hi Tom,
Thanks for your posting!
From your descriptions, I understood that you could not use Query Analyzer
to debug after XP SP2 was upgraded. Have I understood you? If there is
anything I misunderstood, please feel free to let me know.
Based on my scope, as you said it is a remotely debugging ,does it used a
linked server? If so, here is another document address the probelm you may
meet.
You may receive a 7391 error message in SQL Server 2000 when you run a
distributed transaction against a linked server after you install Microsoft
Windows XP Service Pack 2
http://support.microsoft.com/default.aspx?kbid=839279
What's the error message when it fails to debugging? Could you tell me how
to reporduce it on my machine? I need more detailed information for your
scenario, which, I believe, will make us closer to the resolution.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
this prior to installing XP sp2 on the WinXP system. The sql server is at
sp3, and runs on a Windows 2003 system.
The message I get when running the debugger is:
Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
connection 58.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||This is the error in the event log of the SQL Server system:
Event Type:Error
Event Source:SQLDebugging98
Event Category:None
Event ID:1
Date:9/14/2004
Time:9:45:19 PM
User:N/A
Computer:HESPERUS
Description:
SQL Server is running as 'Force3\SQLAdmin' and cannot connect to the
debugger on machine 'TOMHOME2' (error = 0x80070005 Access is denied. ). Use
one of the following options to fix this error. 1) Run SQL Server as "Local
System", as a domain account, or as a local account with identical usernames
and passwords on both machine 'HESPERUS' and 'TOMHOME2'. 2) Verify that
machine 'HESPERUS' can open files on machine 'TOMHOME2'. Debugging disabled
for connection 56.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
This setup did work prior to XP sp2, the logon account for the sql server
service is a domain (admin) account.
The odd thing now is, when I run the debugger, it actually runs, but does
not "step" thru the procedure, it goes straight thru to the end, and does not
show any of the values for the variables, connections, etc. In the results
pane, I get a result, and at the very bottom it says completed.
Putting in break points makes no difference, it just runs on to the end
regardless...
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your posting!
> From your descriptions, I understood that you could not use Query Analyzer
> to debug after XP SP2 was upgraded. Have I understood you? If there is
> anything I misunderstood, please feel free to let me know.
> Based on my scope, as you said it is a remotely debugging ,does it used a
> linked server? If so, here is another document address the probelm you may
> meet.
> You may receive a 7391 error message in SQL Server 2000 when you run a
> distributed transaction against a linked server after you install Microsoft
> Windows XP Service Pack 2
> http://support.microsoft.com/default.aspx?kbid=839279
> What's the error message when it fails to debugging? Could you tell me how
> to reporduce it on my machine? I need more detailed information for your
> scenario, which, I believe, will make us closer to the resolution.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Tom,
Thanks for your detailed information and descriptions!
First of all, The reason for Error 508 and Error code 0x80070005 is
permission issue. As you said it works fine before upgrading to XP SP2,
have you tried disable the Firewall os XP SP2. Close the firewall and then
tell me whether it works. Checking the following documents to make sure
your SQL Server in XP XP2 is correctly configured
How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/?id=841249
Secondly, The error 0x800706ba generated is a Distributed COM communication
error when using RPC. The error code 0x800706ba translates to "RPC server
unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
allocation. By default, RPC dynamic port allocation randomly selects port
numbers above 1024, so the problem seems to be the firewall blocking the
rpc communication. Do the folloing things to make sure DCOM is running
fine.
* On the client machine
--run dcomcnfg.
--Go to the dcom config properties under default properties, there is a
check box stating "enable Distributed COM on this computer", if this was
unchecked on the client machine, this is a default setting that is checked.
make sure it is checked.
*Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
replcaed the old ones with these on the client machine and then ran the
regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
/RegServer on the client machine and rebooted the box and everything
started to work like a charm.Customer was able to debug the stored proc on
the server using SQL QA and t-sql debugger from his client machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I have tried all the suggestions but the behavior remains the same: the
debugger connects and then runs thru the procedure beginning to end without
stopping, providing variable info, etc.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I found the problem: after applying the hotfix to the server, the client side
tools were no longer the same version as the server. Applying the hotfix to
the client fixed the problem.
""Mingqing Cheng [MSFT]"" wrote:
> Hi Tom,
> Thanks for your detailed information and descriptions!
> First of all, The reason for Error 508 and Error code 0x80070005 is
> permission issue. As you said it works fine before upgrading to XP SP2,
> have you tried disable the Firewall os XP SP2. Close the firewall and then
> tell me whether it works. Checking the following documents to make sure
> your SQL Server in XP XP2 is correctly configured
> How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
> http://support.microsoft.com/?id=841249
> Secondly, The error 0x800706ba generated is a Distributed COM communication
> error when using RPC. The error code 0x800706ba translates to "RPC server
> unavailable". DCOM uses Remote Procedure Call (RPC) dynamic port
> allocation. By default, RPC dynamic port allocation randomly selects port
> numbers above 1024, so the problem seems to be the firewall blocking the
> rpc communication. Do the folloing things to make sure DCOM is running
> fine.
> * On the client machine
> --run dcomcnfg.
> --Go to the dcom config properties under default properties, there is a
> check box stating "enable Distributed COM on this computer", if this was
> unchecked on the client machine, this is a default setting that is checked.
> make sure it is checked.
> *Then copied the new sqldbg.dll and sqldbreg.exe from the SQL Server CD,
> replcaed the old ones with these on the client machine and then ran the
> regsvr32 sqldbg.dll for re-resgistering the dll and sqldbreg.exe
> /RegServer on the client machine and rebooted the box and everything
> started to work like a charm.Customer was able to debug the stored proc on
> the server using SQL QA and t-sql debugger from his client machine.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||I also have the same problem after installed Windows XP SP2,
the similar error message when running debugger from query analyzer.
Is there any solution for this?
"TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...[vbcol=seagreen]
> I am trying to debug a sql server, from a WinXP sp2 system. I was able to do
> this prior to installing XP sp2 on the WinXP system. The sql server is at
> sp3, and runs on a Windows 2003 system.
> The message I get when running the debugger is:
> Server: Msg 508, Level 16, State 1, Procedure sp_sdidebug, Line 1
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger
> on HESPERUS (Error = 0x800706ba). Ensure that client-side components, such as
> SQLLE.DLL, are installed and registered on TOMHOME2. Debugging disabled for
> connection 58.
> ""Mingqing Cheng [MSFT]"" wrote:
|||Check my last entry on this topic, dated 9/16/2004
"duandd" wrote:
> I also have the same problem after installed Windows XP SP2,
> the similar error message when running debugger from query analyzer.
> Is there any solution for this?
> "TomT" <tomt@.tomt.com> wrote in message news:<93363379-8AFA-45EA-BE8D-F53FCB3835FE@.microsoft.com>...
>
Monday, February 20, 2012
Q: sum of defined field
Hello,
I created a column and create a field: myField that does some calculation. I
was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
not sum details. How can I sum it?
Thanks,
Jim.Not sure what you mean by "does not sum details" but you will have to
duplicate at the group level any calculations you perform at the detail
level. For example, if at the detail level you have
"=ReportItems!myField.Vlaue*2" then at the group level, you would need
"=Sum(ReportItems!myField.Vlaue)*2"
"JIM.H." wrote:
> Hello,
> I created a column and create a field: myField that does some calculation. I
> was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> not sum details. How can I sum it?
> Thanks,
> Jim.
>|||I have this in one of the columns at the second group level. I named the
field DepTotal
=ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
not have problem at this level, I get my number.
I added this in the first group level in the same column.
=Sum(ReportItems!DepTotal.Value)
It does not compile and it says aggregate function can be used only on
reports items contained in page headers and footers. This column has value in
only second group level, there is no detail data, might it be the problem?
"CGW" wrote:
> Not sure what you mean by "does not sum details" but you will have to
> duplicate at the group level any calculations you perform at the detail
> level. For example, if at the detail level you have
> "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> "=Sum(ReportItems!myField.Vlaue)*2"
> "JIM.H." wrote:
> > Hello,
> > I created a column and create a field: myField that does some calculation. I
> > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > not sum details. How can I sum it?
> > Thanks,
> > Jim.
> >|||When you say you named the field Dep Total, do you mean you named the textbox
that?
I believe you'll get the figure you're wanting for the second group level if
you use
=Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
ReportItems!Dep3.Value), or, if the groups are different from what I
understand them to be...
=Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
Sum(ReportItems!Dep3.Value),
"JIM.H." wrote:
> I have this in one of the columns at the second group level. I named the
> field DepTotal
> =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> not have problem at this level, I get my number.
> I added this in the first group level in the same column.
> =Sum(ReportItems!DepTotal.Value)
> It does not compile and it says aggregate function can be used only on
> reports items contained in page headers and footers. This column has value in
> only second group level, there is no detail data, might it be the problem?
>
> "CGW" wrote:
> > Not sure what you mean by "does not sum details" but you will have to
> > duplicate at the group level any calculations you perform at the detail
> > level. For example, if at the detail level you have
> > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > "=Sum(ReportItems!myField.Vlaue)*2"
> >
> > "JIM.H." wrote:
> >
> > > Hello,
> > > I created a column and create a field: myField that does some calculation. I
> > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > not sum details. How can I sum it?
> > > Thanks,
> > > Jim.
> > >|||yes, when I go to properties of textbox I see DepTotal and thsi is group
level 2. No in group level 1, I need to sum all group level 2 DepTotal
values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
level 1 too. I still get the same message for Dep1,2,3.
"CGW" wrote:
> When you say you named the field Dep Total, do you mean you named the textbox
> that?
> I believe you'll get the figure you're wanting for the second group level if
> you use
> =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> ReportItems!Dep3.Value), or, if the groups are different from what I
> understand them to be...
> =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> Sum(ReportItems!Dep3.Value),
>
> "JIM.H." wrote:
> > I have this in one of the columns at the second group level. I named the
> > field DepTotal
> > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > not have problem at this level, I get my number.
> >
> > I added this in the first group level in the same column.
> > =Sum(ReportItems!DepTotal.Value)
> >
> > It does not compile and it says aggregate function can be used only on
> > reports items contained in page headers and footers. This column has value in
> > only second group level, there is no detail data, might it be the problem?
> >
> >
> >
> > "CGW" wrote:
> >
> > > Not sure what you mean by "does not sum details" but you will have to
> > > duplicate at the group level any calculations you perform at the detail
> > > level. For example, if at the detail level you have
> > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > "=Sum(ReportItems!myField.Vlaue)*2"
> > >
> > > "JIM.H." wrote:
> > >
> > > > Hello,
> > > > I created a column and create a field: myField that does some calculation. I
> > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > not sum details. How can I sum it?
> > > > Thanks,
> > > > Jim.
> > > >|||The same sum(whatever) in each group should yield sums appropriate for that
group. If you get the same total in each group, then the groups are
identical. You might check your group definitions in the properities of the
table to make sure you're grouping distinctly.
"JIM.H." wrote:
> yes, when I go to properties of textbox I see DepTotal and thsi is group
> level 2. No in group level 1, I need to sum all group level 2 DepTotal
> values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
> ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
> level 1 too. I still get the same message for Dep1,2,3.
>
> "CGW" wrote:
> > When you say you named the field Dep Total, do you mean you named the textbox
> > that?
> >
> > I believe you'll get the figure you're wanting for the second group level if
> > you use
> >
> > =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> > ReportItems!Dep3.Value), or, if the groups are different from what I
> > understand them to be...
> > =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> > Sum(ReportItems!Dep3.Value),
> >
> >
> > "JIM.H." wrote:
> >
> > > I have this in one of the columns at the second group level. I named the
> > > field DepTotal
> > > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > > not have problem at this level, I get my number.
> > >
> > > I added this in the first group level in the same column.
> > > =Sum(ReportItems!DepTotal.Value)
> > >
> > > It does not compile and it says aggregate function can be used only on
> > > reports items contained in page headers and footers. This column has value in
> > > only second group level, there is no detail data, might it be the problem?
> > >
> > >
> > >
> > > "CGW" wrote:
> > >
> > > > Not sure what you mean by "does not sum details" but you will have to
> > > > duplicate at the group level any calculations you perform at the detail
> > > > level. For example, if at the detail level you have
> > > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > > "=Sum(ReportItems!myField.Vlaue)*2"
> > > >
> > > > "JIM.H." wrote:
> > > >
> > > > > Hello,
> > > > > I created a column and create a field: myField that does some calculation. I
> > > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > > not sum details. How can I sum it?
> > > > > Thanks,
> > > > > Jim.
> > > > >
I created a column and create a field: myField that does some calculation. I
was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
not sum details. How can I sum it?
Thanks,
Jim.Not sure what you mean by "does not sum details" but you will have to
duplicate at the group level any calculations you perform at the detail
level. For example, if at the detail level you have
"=ReportItems!myField.Vlaue*2" then at the group level, you would need
"=Sum(ReportItems!myField.Vlaue)*2"
"JIM.H." wrote:
> Hello,
> I created a column and create a field: myField that does some calculation. I
> was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> not sum details. How can I sum it?
> Thanks,
> Jim.
>|||I have this in one of the columns at the second group level. I named the
field DepTotal
=ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
not have problem at this level, I get my number.
I added this in the first group level in the same column.
=Sum(ReportItems!DepTotal.Value)
It does not compile and it says aggregate function can be used only on
reports items contained in page headers and footers. This column has value in
only second group level, there is no detail data, might it be the problem?
"CGW" wrote:
> Not sure what you mean by "does not sum details" but you will have to
> duplicate at the group level any calculations you perform at the detail
> level. For example, if at the detail level you have
> "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> "=Sum(ReportItems!myField.Vlaue)*2"
> "JIM.H." wrote:
> > Hello,
> > I created a column and create a field: myField that does some calculation. I
> > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > not sum details. How can I sum it?
> > Thanks,
> > Jim.
> >|||When you say you named the field Dep Total, do you mean you named the textbox
that?
I believe you'll get the figure you're wanting for the second group level if
you use
=Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
ReportItems!Dep3.Value), or, if the groups are different from what I
understand them to be...
=Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
Sum(ReportItems!Dep3.Value),
"JIM.H." wrote:
> I have this in one of the columns at the second group level. I named the
> field DepTotal
> =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> not have problem at this level, I get my number.
> I added this in the first group level in the same column.
> =Sum(ReportItems!DepTotal.Value)
> It does not compile and it says aggregate function can be used only on
> reports items contained in page headers and footers. This column has value in
> only second group level, there is no detail data, might it be the problem?
>
> "CGW" wrote:
> > Not sure what you mean by "does not sum details" but you will have to
> > duplicate at the group level any calculations you perform at the detail
> > level. For example, if at the detail level you have
> > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > "=Sum(ReportItems!myField.Vlaue)*2"
> >
> > "JIM.H." wrote:
> >
> > > Hello,
> > > I created a column and create a field: myField that does some calculation. I
> > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > not sum details. How can I sum it?
> > > Thanks,
> > > Jim.
> > >|||yes, when I go to properties of textbox I see DepTotal and thsi is group
level 2. No in group level 1, I need to sum all group level 2 DepTotal
values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
level 1 too. I still get the same message for Dep1,2,3.
"CGW" wrote:
> When you say you named the field Dep Total, do you mean you named the textbox
> that?
> I believe you'll get the figure you're wanting for the second group level if
> you use
> =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> ReportItems!Dep3.Value), or, if the groups are different from what I
> understand them to be...
> =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> Sum(ReportItems!Dep3.Value),
>
> "JIM.H." wrote:
> > I have this in one of the columns at the second group level. I named the
> > field DepTotal
> > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > not have problem at this level, I get my number.
> >
> > I added this in the first group level in the same column.
> > =Sum(ReportItems!DepTotal.Value)
> >
> > It does not compile and it says aggregate function can be used only on
> > reports items contained in page headers and footers. This column has value in
> > only second group level, there is no detail data, might it be the problem?
> >
> >
> >
> > "CGW" wrote:
> >
> > > Not sure what you mean by "does not sum details" but you will have to
> > > duplicate at the group level any calculations you perform at the detail
> > > level. For example, if at the detail level you have
> > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > "=Sum(ReportItems!myField.Vlaue)*2"
> > >
> > > "JIM.H." wrote:
> > >
> > > > Hello,
> > > > I created a column and create a field: myField that does some calculation. I
> > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > not sum details. How can I sum it?
> > > > Thanks,
> > > > Jim.
> > > >|||The same sum(whatever) in each group should yield sums appropriate for that
group. If you get the same total in each group, then the groups are
identical. You might check your group definitions in the properities of the
table to make sure you're grouping distinctly.
"JIM.H." wrote:
> yes, when I go to properties of textbox I see DepTotal and thsi is group
> level 2. No in group level 1, I need to sum all group level 2 DepTotal
> values. I put Sum in group level 2 like =Sum(ReportItems!Dep1.Value +
> ReportItems!Dep2.Value + ReportItems!Dep3.Value) and put this into grpup
> level 1 too. I still get the same message for Dep1,2,3.
>
> "CGW" wrote:
> > When you say you named the field Dep Total, do you mean you named the textbox
> > that?
> >
> > I believe you'll get the figure you're wanting for the second group level if
> > you use
> >
> > =Sum(ReportItems!Dep1.Value + ReportItems!Dep2.Value +
> > ReportItems!Dep3.Value), or, if the groups are different from what I
> > understand them to be...
> > =Sum(ReportItems!Dep1.Value) + sum(ReportItems!Dep2.Value) +
> > Sum(ReportItems!Dep3.Value),
> >
> >
> > "JIM.H." wrote:
> >
> > > I have this in one of the columns at the second group level. I named the
> > > field DepTotal
> > > =ReportItems!Dep1.Value + ReportItems!Dep2.Value ReportItems!Dep3.Value I do
> > > not have problem at this level, I get my number.
> > >
> > > I added this in the first group level in the same column.
> > > =Sum(ReportItems!DepTotal.Value)
> > >
> > > It does not compile and it says aggregate function can be used only on
> > > reports items contained in page headers and footers. This column has value in
> > > only second group level, there is no detail data, might it be the problem?
> > >
> > >
> > >
> > > "CGW" wrote:
> > >
> > > > Not sure what you mean by "does not sum details" but you will have to
> > > > duplicate at the group level any calculations you perform at the detail
> > > > level. For example, if at the detail level you have
> > > > "=ReportItems!myField.Vlaue*2" then at the group level, you would need
> > > > "=Sum(ReportItems!myField.Vlaue)*2"
> > > >
> > > > "JIM.H." wrote:
> > > >
> > > > > Hello,
> > > > > I created a column and create a field: myField that does some calculation. I
> > > > > was trying to do Sum(ReportItems!myField.Vlaue) in the group line, this does
> > > > > not sum details. How can I sum it?
> > > > > Thanks,
> > > > > Jim.
> > > > >
Subscribe to:
Posts (Atom)