A connection could not be made with report server at https://webse

Hi,
My report server and report manager was working fine till yesterday. But
suddenly today when i tried to deploy a report i got error: "A connection
could not be made with report server at https://webserver1/ReportServer "
So when i tried to browse the report manager from IIS, I get error:"Could
not establish trust relationship with remote server."
I think its someting to do with SSL. Please help me with that 'czo out of
the blue today I have started getting this error.
--
pmudLook in the event log at the server machine for SSL errors.
Regards,
Martin

a complex query needs a solution

I need help with determining which salespeople should be
assigned bonuses based off a relationship between the
salesperson and their manager and also the relationship
between the salesperson and the customer. Below are 3
tables, the salesperson table which contains the
salesperson's number and a joblevel column. The joblevel
you can think of 10 = manager and 5 = worker. There are
more levels but just assume the higher the number the
higher the level of the person. Next is the customer
table. After that is the customer to salesrep assignment
table. If you read the first insert, we insert for
customer 779 the rep 5000 who happens to be a manager, a
date of 1/1/04, and an ownership of 1 (100%). That means
that the rep 5000 would get 100% of his bonus based on the
revenue that customer 779 generates. Next you see for the
same customer 5002 (a subordinate) is inserted. After
that you see at 3/1/04 a new subordinate level person
(level = 5) gets inserted. This means that at 1/1/04
subordinate 5002 is assigned to cusomter 779 but at 3/1/04
subordinate 5003 is assigned to customer 779. Since no
subordinate is assigned at 7/1/04, then we conclude that
5003 is still assigned to 779 at that date.
Skipping down we can se for customer 716 that 2 level=5
subordinates are assigned but one's ownership is 40% and
the other's is 60%. the next month 2 new subordinates are
rotated in. Don't worry about the insert logic to this
table. If 2 subordinates are split on a customer and one
gets replaced the next month, the other gets replaced too
so that in one month the total for each joblevel = 1 (100%)
create table salesrep (salesrepno int not null primary
key, joblevel int not null)
insert salesrep values (5000, 10)
insert salesrep values (5001, 10)
insert salesrep values (5002, 5)
insert salesrep values (5003, 5)
insert salesrep values (5004, 5)
insert salesrep values (5005, 5)
insert salesrep values (5006, 5)
insert salesrep values (5007, 5)
insert salesrep values (5008, 5)
insert salesrep values (5009, 10)
insert salesrep values (5010, 5)
create table customer (custno int not null primary key)
insert customer values (234)
insert customer values (332)
insert customer values (213)
insert customer values (716)
insert customer values (879)
insert customer values (267)
create table customer_salesrep (
custno int not null foreign key references customer,
salesrepno int not null foreign key references salesrep,
dateassigned datetime not null,
ownership int check (ownership between 0 and 1),
primary key (custno, salesrepno, dateassigned))
insert customer_salesrep values (779, 5000, '1/1/2004', 1)
insert customer_salesrep values (779, 5002, '1/1/2004', 1)
insert customer_salesrep values (779, 5003, '3/1/2004', 1)
insert customer_salesrep values (332, 5000, '1/1/2004', 1)
insert customer_salesrep values (213, 5000, '7/1/2004', 1)
insert customer_salesrep values (213, 5004, '7/1/2004', 1)
insert customer_salesrep values (716, 5000, '1/1/2004', 1)
insert customer_salesrep values (716, 5005, '1/1/2004', .4)
insert customer_salesrep values (716, 5006, '1/1/2004', .6)
insert customer_salesrep values (716, 5007, '3/1/2004', .4)
insert customer_salesrep values (716, 5008, '3/1/2004', .6)
insert customer_salesrep values (879, 5000, '1/1/2004')
insert customer_salesrep values (879, 5002, '1/1/2004')
insert customer_salesrep values (879, 5001, '7/1/2004')
insert customer_salesrep values (267, 5009, '1/1/2004', 1)
insert customer_salesrep values (267, 5010, '1/1/2004', 1)
I want to run a query where I pass in the manager level
number (in this case 5000) and a date. I want to know who
(the subordinate) is assigned to the customer at any date
I pass in. If another manager is assigned to a customer
(like 5009) then I don't want to see the customer
information for him.
If I run the query at 1/1/2004 for the manager 5000, I
should get in the results
779 5002 1
716 5005 .4
716 5006 .6
879 5002 1
If I run the query at 3/1/2004 for the manager 5000, I
should get in the results
779 5003 1
716 5007 .4
716 5008 .6
879 5002 1
If I run the query at 7/1/2004 for the manager 5000, I
should get in the results (notice customer 879 drops off
because another manager takes over the relationship)
779 5003 1
213 5004 1
716 5007 .4
716 5008 .6
Please let me know if you need any more information"Jerry Fortaine" <anonymous@.discussions.microsoft.com> wrote in message
news:26b001c50d4d$8823af10$a401280a@.phx.gbl...
>I need help with determining which salespeople should be
> assigned bonuses based off a relationship between the
> salesperson and their manager and also the relationship
> between the salesperson and the customer. Below are 3
> tables, the salesperson table which contains the
> salesperson's number and a joblevel column. The joblevel
> you can think of 10 = manager and 5 = worker. There are
> more levels but just assume the higher the number the
> higher the level of the person. Next is the customer
> table. After that is the customer to salesrep assignment
> table. If you read the first insert, we insert for
> customer 779 the rep 5000 who happens to be a manager, a
> date of 1/1/04, and an ownership of 1 (100%). That means
> that the rep 5000 would get 100% of his bonus based on the
> revenue that customer 779 generates. Next you see for the
> same customer 5002 (a subordinate) is inserted. After
> that you see at 3/1/04 a new subordinate level person
> (level = 5) gets inserted. This means that at 1/1/04
> subordinate 5002 is assigned to cusomter 779 but at 3/1/04
> subordinate 5003 is assigned to customer 779. Since no
> subordinate is assigned at 7/1/04, then we conclude that
> 5003 is still assigned to 779 at that date.
> Skipping down we can se for customer 716 that 2 level=5
> subordinates are assigned but one's ownership is 40% and
> the other's is 60%. the next month 2 new subordinates are
> rotated in. Don't worry about the insert logic to this
> table. If 2 subordinates are split on a customer and one
> gets replaced the next month, the other gets replaced too
> so that in one month the total for each joblevel = 1 (100%)
> create table salesrep (salesrepno int not null primary
> key, joblevel int not null)
> insert salesrep values (5000, 10)
> insert salesrep values (5001, 10)
> insert salesrep values (5002, 5)
> insert salesrep values (5003, 5)
> insert salesrep values (5004, 5)
> insert salesrep values (5005, 5)
> insert salesrep values (5006, 5)
> insert salesrep values (5007, 5)
> insert salesrep values (5008, 5)
> insert salesrep values (5009, 10)
> insert salesrep values (5010, 5)
> create table customer (custno int not null primary key)
> insert customer values (234)
> insert customer values (332)
> insert customer values (213)
> insert customer values (716)
> insert customer values (879)
> insert customer values (267)
> create table customer_salesrep (
> custno int not null foreign key references customer,
> salesrepno int not null foreign key references salesrep,
> dateassigned datetime not null,
> ownership int check (ownership between 0 and 1),
> primary key (custno, salesrepno, dateassigned))
> insert customer_salesrep values (779, 5000, '1/1/2004', 1)
> insert customer_salesrep values (779, 5002, '1/1/2004', 1)
> insert customer_salesrep values (779, 5003, '3/1/2004', 1)
> insert customer_salesrep values (332, 5000, '1/1/2004', 1)
> insert customer_salesrep values (213, 5000, '7/1/2004', 1)
> insert customer_salesrep values (213, 5004, '7/1/2004', 1)
> insert customer_salesrep values (716, 5000, '1/1/2004', 1)
> insert customer_salesrep values (716, 5005, '1/1/2004', .4)
> insert customer_salesrep values (716, 5006, '1/1/2004', .6)
> insert customer_salesrep values (716, 5007, '3/1/2004', .4)
> insert customer_salesrep values (716, 5008, '3/1/2004', .6)
> insert customer_salesrep values (879, 5000, '1/1/2004')
> insert customer_salesrep values (879, 5002, '1/1/2004')
> insert customer_salesrep values (879, 5001, '7/1/2004')
> insert customer_salesrep values (267, 5009, '1/1/2004', 1)
> insert customer_salesrep values (267, 5010, '1/1/2004', 1)
> I want to run a query where I pass in the manager level
> number (in this case 5000) and a date. I want to know who
> (the subordinate) is assigned to the customer at any date
> I pass in. If another manager is assigned to a customer
> (like 5009) then I don't want to see the customer
> information for him.
>
> If I run the query at 1/1/2004 for the manager 5000, I
> should get in the results
> 779 5002 1
> 716 5005 .4
> 716 5006 .6
> 879 5002 1
>
> If I run the query at 3/1/2004 for the manager 5000, I
> should get in the results
> 779 5003 1
> 716 5007 .4
> 716 5008 .6
> 879 5002 1
> If I run the query at 7/1/2004 for the manager 5000, I
> should get in the results (notice customer 879 drops off
> because another manager takes over the relationship)
> 779 5003 1
> 213 5004 1
> 716 5007 .4
> 716 5008 .6
> Please let me know if you need any more information
-- Customer and sales rep along with job level of rep
CREATE VIEW AccountReps (custno, salesrepno, joblevel, dateassigned, ownersh
ip)
AS
SELECT CSR.custno, CSR.salesrepno, SR.joblevel, CSR.dateassigned, CSR.owners
hip
FROM customer_salesrep AS CSR
INNER JOIN
salesrep AS SR
ON CSR.salesrepno = SR.salesrepno
-- For each customer account, all overlaps between a manager's and
-- worker's tenure
CREATE VIEW AccountAssignments
(custno, manager, manager_ownership,
manager_dateassigned, manager_dateunassigned,
worker, worker_ownership, worker_dateassigned, worker_dateunassigned)
AS
SELECT M.custno, M.salesrepno, M.ownership,
M.dateassigned, M.dateunassigned,
W.salesrepno, W.ownership, W.dateassigned, W.dateunassigned
FROM (SELECT R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership,
COALESCE(MIN(R2.dateassigned), '99991231')
AS dateunassigned
FROM AccountReps AS R1
LEFT OUTER JOIN
AccountReps AS R2
ON R1.custno = R2.custno AND
R1.joblevel = R2.joblevel AND
R1.salesrepno <> R2.salesrepno AND
R1.dateassigned < R2.dateassigned
WHERE R1.joblevel = 10
GROUP BY R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership)
AS M
INNER JOIN
(SELECT R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership,
COALESCE(MIN(R2.dateassigned), '99991231')
AS dateunassigned
FROM AccountReps AS R1
LEFT OUTER JOIN
AccountReps AS R2
ON R1.custno = R2.custno AND
R2.joblevel < 10 AND
R1.salesrepno <> R2.salesrepno AND
R1.dateassigned < R2.dateassigned
WHERE R1.joblevel < 10
GROUP BY R1.custno, R1.salesrepno, R1.dateassigned, R1.ownership)
AS W
ON W.dateassigned < M.dateunassigned AND
W.dateunassigned > M.dateassigned AND
W.custno = M.custno
-- For manager 5000 on 20040101
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040101' AND
worker_dateunassigned > '20040101' AND
manager_dateassigned <= '20040101' AND
manager_dateunassigned > '20040101'
ORDER BY custno, worker
-- For manager 5000 on 20040301
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040301' AND
worker_dateunassigned > '20040301' AND
manager_dateassigned <= '20040301' AND
manager_dateunassigned > '20040301'
ORDER BY custno, worker
-- For manager 5000 on 20040701
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040701' AND
worker_dateunassigned > '20040701' AND
manager_dateassigned <= '20040701' AND
manager_dateunassigned > '20040701'
ORDER BY custno, worker
-- Note that we can check assignments on an arbitrary date
-- For manager 5000 on 20040515
SELECT custno, worker, worker_ownership
FROM AccountAssignments
WHERE manager = 5000 AND
worker_dateassigned <= '20040515' AND
worker_dateunassigned > '20040515' AND
manager_dateassigned <= '20040515' AND
manager_dateunassigned > '20040515'
ORDER BY custno, worker
JAG|||Hi Jerry. I see that John already posted an answer to your question. I
took a look and came up with something a bit different. I think that the
problem lies in that you have relationships and data that are implicit in
your table schema. It may make for faster queries if you explicitly set an
owning manager to each line item, rather than calculate it each time you ran
your query. Right now this ownership is burried in the ordering of the
rows which is a bit tricky. It may be possible to create an indexed view
with this information in it. Maintaining "reportsto" relationships would
also make the queries a bit cleaner.
Here is my attempt:
--use master
--go
--DROP DATABASE complexquery
--go
create database complexquery
go
use complexquery
go
create table salesrep (salesrepno int not null primary
key, joblevel int not null)
go
insert salesrep values (5000, 10)
insert salesrep values (5001, 10)
insert salesrep values (5002, 5)
insert salesrep values (5003, 5)
insert salesrep values (5004, 5)
insert salesrep values (5005, 5)
insert salesrep values (5006, 5)
insert salesrep values (5007, 5)
insert salesrep values (5008, 5)
insert salesrep values (5009, 10)
insert salesrep values (5010, 5)
go
--delete from salesrep
create table customer (custno int not null primary key)
go
insert customer values (234)
insert customer values (332)
insert customer values (213)
insert customer values (716)
insert customer values (879)
insert customer values (267)
insert customer values (779) -- bug!bug! added value
go
--drop table customer_salesrep
create table customer_salesrep (
custno int not null foreign key references customer,
salesrepno int not null foreign key references salesrep,
dateassigned datetime not null,
ownership decimal(5,2) check (ownership between 0 and 1), -- bug!bug! int
truncation, changed to decimal
primary key (custno, salesrepno, dateassigned))
go
delete from customer_salesrep
go
insert customer_salesrep values (779, 5000, '1/1/2004', 1)
insert customer_salesrep values (779, 5002, '1/1/2004', 1)
insert customer_salesrep values (779, 5003, '3/1/2004', 1)
insert customer_salesrep values (332, 5000, '1/1/2004', 1)
insert customer_salesrep values (213, 5000, '7/1/2004', 1)
insert customer_salesrep values (213, 5004, '7/1/2004', 1)
insert customer_salesrep values (716, 5000, '1/1/2004', 1)
insert customer_salesrep values (716, 5005, '1/1/2004', .4)
insert customer_salesrep values (716, 5006, '1/1/2004', .6)
insert customer_salesrep values (716, 5007, '3/1/2004', .4)
insert customer_salesrep values (716, 5008, '3/1/2004', .6)
insert customer_salesrep values (879, 5000, '1/1/2004', 1)
insert customer_salesrep values (879, 5002, '1/1/2004', 1)
insert customer_salesrep values (879, 5001, '7/1/2004', 1)
insert customer_salesrep values (267, 5009, '1/1/2004', 1)
insert customer_salesrep values (267, 5010, '1/1/2004', 1)
go
--drop function utvf_whichboss
CREATE FUNCTION utvf_whichboss(@.lookupdate datetime) returns table as return
--
-- This function returns the list of customer accounts
-- that are owned by a given manager at any time. Caller
-- still needs to find the maximum dateassigned since all
-- previous owning managers will also show up.
SELECT
csr.salesrepno AS mgrno, csr.dateassigned, csr.custno
FROM
customer_salesrep csr
JOIN salesrep sr ON sr.salesrepno = csr.salesrepno
WHERE
sr.joblevel = 10
AND dateassigned <= @.lookupdate
GO
--drop function utvf_subs
CREATE FUNCTION utvf_subs( @.mgrno int, @.lookupdate datetime) RETURNS TABLE
AS RETURN
--
-- this function returns all of the rows that match a given mgrno and within
-- the lookupdate range. Caller still must find the max(dateassigned) since
-- it does not track duplicates (change of ownership)
SELECT csr2.custno, csr2.salesrepno, csr2.ownership, csr2.dateassigned
FROM
customer_salesrep csr2
join salesrep sr2 on sr2.salesrepno = csr2.salesrepno
WHERE csr2.custno IN
(
SELECT b.custno
FROM utvf_whichboss(@.lookupdate) b
WHERE b.dateassigned = (
SELECT max(dateassigned)
FROM utvf_whichboss(@.lookupdate)
WHERE custno = b.custno
)
AND b.mgrno = @.mgrno
)
AND sr2.joblevel = 5
AND csr2.dateassigned <= @.lookupdate
GO
--drop procedure usp_getsubs
CREATE PROCEDURE usp_getsubs
@.mgrno INT,
@.lookupdate DATETIME
AS
--
-- This sproc takes all of the rows that belong to
-- a given manager at a certain time and prunes out the
-- duplicates (i.e. customers who's owning subordinate have
-- changed.) It should only return the current subordinate
SELECT s.custno, s.salesrepno, s.ownership
FROM utvf_subs(@.mgrno, @.lookupdate) s
WHERE
s.dateassigned = (
SELECT MAX(dateassigned)
FROM utvf_subs(@.mgrno, @.lookupdate)
WHERE custno = s.custno
)
ORDER BY s.dateassigned
GO
-- Sample tvf output: note that all of the
-- rows are present, even though the ownership
-- has changed to a new subordinate.
SELECT * FROM utvf_subs(5000, '3/1/2004')
GO
-- sample sproc output: these are the samples in your
-- original mail. They all output as you described
-- but I could not preserve your ordering since it does
-- not appear to be encoded in your data anywhere
EXEC usp_getsubs 5000, '1/1/2004'
GO
EXEC usp_getsubs 5000, '3/1/2004'
GO
EXEC usp_getsubs 5000, '7/1/2004'
GO

a bug in Enterprise Manager ?

if I use enterprise manager to change Login permissions, I
am prompted to confirm the password even if the password
is not changed. Worse thing is that most of the time I
don't know the login password , so I am not able use EM to
assign permissions. I have access to other computers with
SQL EM working, and if I need to I can still use QA. I use
my pc for most of the sql admin work and would like if I
can resolve this.
What should I do ?
Thanks in advanceSee if this helps:
FIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/default.aspx?scid=kb;en-us;826161&Product=sql2k
Rand
This posting is provided "as is" with no warranties and confers no rights.

a bug in Enterprise Manager ?

if I use enterprise manager to change Login permissions, I
am prompted to confirm the password even if the password
is not changed. Worse thing is that most of the time I
don't know the login password , so I am not able use EM to
assign permissions. I have access to other computers with
SQL EM working, and if I need to I can still use QA. I use
my pc for most of the sql admin work and would like if I
can resolve this.
What should I do ?
Thanks in advance
See if this helps:
FIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/default...&Product=sql2k
Rand
This posting is provided "as is" with no warranties and confers no rights.

a bug in Enterprise Manager ?

if I use enterprise manager to change Login permissions, I
am prompted to confirm the password even if the password
is not changed. Worse thing is that most of the time I
don't know the login password , so I am not able use EM to
assign permissions. I have access to other computers with
SQL EM working, and if I need to I can still use QA. I use
my pc for most of the sql admin work and would like if I
can resolve this.
What should I do ?
Thanks in advanceSee if this helps:
FIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/defaul...1&Product=sql2k
Rand
This posting is provided "as is" with no warranties and confers no rights.

a better Enterprise Manager plz

hi,
do u know some nice (preferably commersial) tool for SQL Server 2k
administration
Embarcadero DBArtisan 8.0 (current).
"Coldman" wrote:

> hi,
> do u know some nice (preferably commersial) tool for SQL Server 2k
> administration
>
>
|||tnx, Julia, i`ll try it
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:01FAB6BB-7431-4F0D-9CE4-FFCD62BB2BD5@.microsoft.com...[vbcol=seagreen]
> Embarcadero DBArtisan 8.0 (current).
> "Coldman" wrote:
|||Hi,
DBArtsan is a good tool for Oracle/DB2 and Sybase. But as far as SQL Server
is concerned Enterprise manager is the best for
doing basic administration.
Thanks
Hari
SQL Server MVP
"Coldman" <nomorespam@.mail.com> wrote in message
news:OJbRR$KrFHA.2604@.TK2MSFTNGP14.phx.gbl...
> hi,
> do u know some nice (preferably commersial) tool for SQL Server 2k
> administration
>
sql

a better Enterprise Manager plz

hi,
do u know some nice (preferably commersial) tool for SQL Server 2k
administrationEmbarcadero DBArtisan 8.0 (current).
"Coldman" wrote:

> hi,
> do u know some nice (preferably commersial) tool for SQL Server 2k
> administration
>
>|||tnx, Julia, i`ll try it
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:01FAB6BB-7431-4F0D-9CE4-FFCD62BB2BD5@.microsoft.com...[vbcol=seagreen]
> Embarcadero DBArtisan 8.0 (current).
> "Coldman" wrote:
>|||Hi,
DBArtsan is a good tool for Oracle/DB2 and Sybase. But as far as SQL Server
is concerned Enterprise manager is the best for
doing basic administration.
Thanks
Hari
SQL Server MVP
"Coldman" <nomorespam@.mail.com> wrote in message
news:OJbRR$KrFHA.2604@.TK2MSFTNGP14.phx.gbl...
> hi,
> do u know some nice (preferably commersial) tool for SQL Server 2k
> administration
>

a better Enterprise Manager plz

hi,
do u know some nice (preferably commersial) tool for SQL Server 2k
administrationEmbarcadero DBArtisan 8.0 (current).
"Coldman" wrote:
> hi,
> do u know some nice (preferably commersial) tool for SQL Server 2k
> administration
>
>|||tnx, Julia, i`ll try it
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:01FAB6BB-7431-4F0D-9CE4-FFCD62BB2BD5@.microsoft.com...
> Embarcadero DBArtisan 8.0 (current).
> "Coldman" wrote:
>> hi,
>> do u know some nice (preferably commersial) tool for SQL Server 2k
>> administration
>>|||Hi,
DBArtsan is a good tool for Oracle/DB2 and Sybase. But as far as SQL Server
is concerned Enterprise manager is the best for
doing basic administration.
Thanks
Hari
SQL Server MVP
"Coldman" <nomorespam@.mail.com> wrote in message
news:OJbRR$KrFHA.2604@.TK2MSFTNGP14.phx.gbl...
> hi,
> do u know some nice (preferably commersial) tool for SQL Server 2k
> administration
>

'8007007F' error

Hi,
I have a problem with my SQL Server 2000.
When I try to display the rows from a table with enterprise manager, I get a
'8007007F' error
I don't have this problem if I read the table from a Query using the Query
Anlyzer.
I use Windows 2003 Server and I installed the MDAC 8.x
This problem also occurs when I try to open databases from Active Server
pages
Any ideas ?
Stan.I'm experiencing the same issues after upgrading from Windows Server 2000 to
Windows Server 2003. I receive error '8007007F' when i try to query an ASP
page|||Hi Shaheen,
My problem was not a ASP problem but a DLL problem
I was not able to view my datatable with Enterprise Manager.
If you have the same problem in this case, check this url out.
http://groups.google.com/groups?q=%...0phx.gbl&rnum=1
Hope that helps.
Stan
"Shaheen" <anonymous@.discussions.microsoft.com> a crit dans le message de
news:AE4ADF75-0850-4D7D-9488-EF484AFC77A3@.microsoft.com...
> I'm experiencing the same issues after upgrading from Windows Server 2000
to Windows Server 2003. I receive error '8007007F' when i try to query an
ASP page|||I had the same problem after upgrading to Windows 2003 Server. I called Mic
rosoft and here is the fix:
Symptoms:
After upgrading from Windows 2000 to Windows 2003 attempting to access a dat
abase or data component will result in a '8007007f' or "The specified proced
ure could not be found" error.
Status:
This is a known issue with some installations of Windows 2003
Workaround:
Extract oledb32.dll from the zip file into these two directories. It's impo
rtant that it be done in this order:
1) C:\WINNT\system32\dllCache
2) C:\Program Files\Common Files\System\OLE DB
3) Reboot the server
Cause:
This issue is caused when the Windows 2003 installer did not update the oled
b32.dll file.
You can dowload the oledb32.dll file here: [url]http://www.promiseweb.com/oledb32
.zip[/
url]
This is per Malcolm Stewart at Microsoft Developer Support

'8007007F' error

Hi,
I have a problem with my SQL Server 2000.
When I try to display the rows from a table with enterprise manager, I get a
'8007007F' error
I don't have this problem if I read the table from a Query using the Query
Anlyzer.
I use Windows 2003 Server and I installed the MDAC 8.x
This problem also occurs when I try to open databases from Active Server
pages
Any ideas ?
Stan.I'm experiencing the same issues after upgrading from Windows Server 2000 to Windows Server 2003. I receive error '8007007F' when i try to query an ASP page|||Hi Shaheen,
My problem was not a ASP problem but a DLL problem
I was not able to view my datatable with Enterprise Manager.
If you have the same problem in this case, check this url out.
http://groups.google.com/groups?q=%228007007F+the+fix%22&hl=fr&lr=&ie=UTF-8&selm=081601c309fd%242ac4c3e0%24a001280a%40phx.gbl&rnum=1
Hope that helps.
Stan
"Shaheen" <anonymous@.discussions.microsoft.com> a écrit dans le message de
news:AE4ADF75-0850-4D7D-9488-EF484AFC77A3@.microsoft.com...
> I'm experiencing the same issues after upgrading from Windows Server 2000
to Windows Server 2003. I receive error '8007007F' when i try to query an
ASP page

4GB Ram /3GB and the memory slider in Ent Manager

If you use the /3GB switch should the memory slider in SQL Enterprise
Manager display up to 3GB or do you set manually and just assume it's
working?
2000 Advanced server and SQL 2000 Enterprise Edition.
Thanks
Paul
Paul,
The maximum value of the slider should show a value of 3-something . (in my
environments it's 3455).
I would suggest not setting a fixed size, always let SQL use as much as
possible.
JP
"Paul Cahill" wrote:

> If you use the /3GB switch should the memory slider in SQL Enterprise
> Manager display up to 3GB or do you set manually and just assume it's
> working?
> 2000 Advanced server and SQL 2000 Enterprise Edition.
> Thanks
> Paul
>
>
|||Thanks JP.
Have you got 4GB Ram and if so are you just using /3GB and not PAE or AWE.
I have exactly 4GB ram. If I use PAE the slider in enterprise manager shows
4GB max. With /3GB I only see 2GB.
I don't want to have the overhead of /PAE and it should not be necessary. Is
there any way to see if /3GB is working (from an NT or SQL)?
Paul
"JP de Jong" <JP de Jong@.discussions.microsoft.com> wrote in message
news:1447D55D-C78C-41E8-87EB-FDF4C0E082D8@.microsoft.com...[vbcol=seagreen]
> Paul,
> The maximum value of the slider should show a value of 3-something . (in
> my
> environments it's 3455).
> I would suggest not setting a fixed size, always let SQL use as much as
> possible.
> JP
> "Paul Cahill" wrote:
|||Paul
Just checked it for you.
We have 4GB RAM in server (Windows 2003Server). In BoOT.INI we have "/3GB
/PAE". (so we do have PAE enabled).
In properties of system (my computer) it says. Physical Address Extensions
enabled. Not sure how to check if /3GB is enabled. You're absolutelt right
that /PAE only is effective when you have more than 4 GB.
/3GB only enables applications to use more virtual memory. I'm not quite
sure if you can find that back in the gui of SQL.
JP
"Paul Cahill" wrote:

> Thanks JP.
> Have you got 4GB Ram and if so are you just using /3GB and not PAE or AWE.
> I have exactly 4GB ram. If I use PAE the slider in enterprise manager shows
> 4GB max. With /3GB I only see 2GB.
> I don't want to have the overhead of /PAE and it should not be necessary. Is
> there any way to see if /3GB is working (from an NT or SQL)?
> Paul
>
> "JP de Jong" <JP de Jong@.discussions.microsoft.com> wrote in message
> news:1447D55D-C78C-41E8-87EB-FDF4C0E082D8@.microsoft.com...
>
>
|||Thanks JP.
Any idea why you have /PAE? I thought there's got to be an overhead for
address translation.
You don't feel like trying something for me......
Paul
"JP de Jong" <JPdeJong@.discussions.microsoft.com> wrote in message
news:45DC15E7-3C0D-4DD0-ACEE-732E9A8504E0@.microsoft.com...[vbcol=seagreen]
> Paul
> Just checked it for you.
> We have 4GB RAM in server (Windows 2003Server). In BoOT.INI we have "/3GB
> /PAE". (so we do have PAE enabled).
> In properties of system (my computer) it says. Physical Address Extensions
> enabled. Not sure how to check if /3GB is enabled. You're absolutelt right
> that /PAE only is effective when you have more than 4 GB.
> /3GB only enables applications to use more virtual memory. I'm not quite
> sure if you can find that back in the gui of SQL.
> JP
> "Paul Cahill" wrote:

4GB Ram /3GB and the memory slider in Ent Manager

If you use the /3GB switch should the memory slider in SQL Enterprise
Manager display up to 3GB or do you set manually and just assume it's
working?
2000 Advanced server and SQL 2000 Enterprise Edition.
Thanks
PaulPaul,
The maximum value of the slider should show a value of 3-something . (in my
environments it's 3455).
I would suggest not setting a fixed size, always let SQL use as much as
possible.
JP
"Paul Cahill" wrote:

> If you use the /3GB switch should the memory slider in SQL Enterprise
> Manager display up to 3GB or do you set manually and just assume it's
> working?
> 2000 Advanced server and SQL 2000 Enterprise Edition.
> Thanks
> Paul
>
>|||Thanks JP.
Have you got 4GB Ram and if so are you just using /3GB and not PAE or AWE.
I have exactly 4GB ram. If I use PAE the slider in enterprise manager shows
4GB max. With /3GB I only see 2GB.
I don't want to have the overhead of /PAE and it should not be necessary. Is
there any way to see if /3GB is working (from an NT or SQL)?
Paul
"JP de Jong" <JP de Jong@.discussions.microsoft.com> wrote in message
news:1447D55D-C78C-41E8-87EB-FDF4C0E082D8@.microsoft.com...[vbcol=seagreen]
> Paul,
> The maximum value of the slider should show a value of 3-something . (in
> my
> environments it's 3455).
> I would suggest not setting a fixed size, always let SQL use as much as
> possible.
> JP
> "Paul Cahill" wrote:
>|||Paul
Just checked it for you.
We have 4GB RAM in server (Windows 2003Server). In BoOT.INI we have "/3GB
/PAE". (so we do have PAE enabled).
In properties of system (my computer) it says. Physical Address Extensions
enabled. Not sure how to check if /3GB is enabled. You're absolutelt right
that /PAE only is effective when you have more than 4 GB.
/3GB only enables applications to use more virtual memory. I'm not quite
sure if you can find that back in the gui of SQL.
JP
"Paul Cahill" wrote:

> Thanks JP.
> Have you got 4GB Ram and if so are you just using /3GB and not PAE or AWE.
> I have exactly 4GB ram. If I use PAE the slider in enterprise manager show
s
> 4GB max. With /3GB I only see 2GB.
> I don't want to have the overhead of /PAE and it should not be necessary.
Is
> there any way to see if /3GB is working (from an NT or SQL)?
> Paul
>
> "JP de Jong" <JP de Jong@.discussions.microsoft.com> wrote in message
> news:1447D55D-C78C-41E8-87EB-FDF4C0E082D8@.microsoft.com...
>
>|||Thanks JP.
Any idea why you have /PAE? I thought there's got to be an overhead for
address translation.
You don't feel like trying something for me......
Paul
"JP de Jong" <JPdeJong@.discussions.microsoft.com> wrote in message
news:45DC15E7-3C0D-4DD0-ACEE-732E9A8504E0@.microsoft.com...[vbcol=seagreen]
> Paul
> Just checked it for you.
> We have 4GB RAM in server (Windows 2003Server). In BoOT.INI we have "/3GB
> /PAE". (so we do have PAE enabled).
> In properties of system (my computer) it says. Physical Address Extensions
> enabled. Not sure how to check if /3GB is enabled. You're absolutelt right
> that /PAE only is effective when you have more than 4 GB.
> /3GB only enables applications to use more virtual memory. I'm not quite
> sure if you can find that back in the gui of SQL.
> JP
> "Paul Cahill" wrote:
>

4GB Ram /3GB and the memory slider in Ent Manager

If you use the /3GB switch should the memory slider in SQL Enterprise
Manager display up to 3GB or do you set manually and just assume it's
working?
2000 Advanced server and SQL 2000 Enterprise Edition.
Thanks
PaulPaul,
The maximum value of the slider should show a value of 3-something . (in my
environments it's 3455).
I would suggest not setting a fixed size, always let SQL use as much as
possible.
JP
"Paul Cahill" wrote:
> If you use the /3GB switch should the memory slider in SQL Enterprise
> Manager display up to 3GB or do you set manually and just assume it's
> working?
> 2000 Advanced server and SQL 2000 Enterprise Edition.
> Thanks
> Paul
>
>|||Thanks JP.
Have you got 4GB Ram and if so are you just using /3GB and not PAE or AWE.
I have exactly 4GB ram. If I use PAE the slider in enterprise manager shows
4GB max. With /3GB I only see 2GB.
I don't want to have the overhead of /PAE and it should not be necessary. Is
there any way to see if /3GB is working (from an NT or SQL)?
Paul
"JP de Jong" <JP de Jong@.discussions.microsoft.com> wrote in message
news:1447D55D-C78C-41E8-87EB-FDF4C0E082D8@.microsoft.com...
> Paul,
> The maximum value of the slider should show a value of 3-something . (in
> my
> environments it's 3455).
> I would suggest not setting a fixed size, always let SQL use as much as
> possible.
> JP
> "Paul Cahill" wrote:
>> If you use the /3GB switch should the memory slider in SQL Enterprise
>> Manager display up to 3GB or do you set manually and just assume it's
>> working?
>> 2000 Advanced server and SQL 2000 Enterprise Edition.
>> Thanks
>> Paul
>>|||Paul
Just checked it for you.
We have 4GB RAM in server (Windows 2003Server). In BoOT.INI we have "/3GB
/PAE". (so we do have PAE enabled).
In properties of system (my computer) it says. Physical Address Extensions
enabled. Not sure how to check if /3GB is enabled. You're absolutelt right
that /PAE only is effective when you have more than 4 GB.
/3GB only enables applications to use more virtual memory. I'm not quite
sure if you can find that back in the gui of SQL.
JP
"Paul Cahill" wrote:
> Thanks JP.
> Have you got 4GB Ram and if so are you just using /3GB and not PAE or AWE.
> I have exactly 4GB ram. If I use PAE the slider in enterprise manager shows
> 4GB max. With /3GB I only see 2GB.
> I don't want to have the overhead of /PAE and it should not be necessary. Is
> there any way to see if /3GB is working (from an NT or SQL)?
> Paul
>
> "JP de Jong" <JP de Jong@.discussions.microsoft.com> wrote in message
> news:1447D55D-C78C-41E8-87EB-FDF4C0E082D8@.microsoft.com...
> > Paul,
> >
> > The maximum value of the slider should show a value of 3-something . (in
> > my
> > environments it's 3455).
> >
> > I would suggest not setting a fixed size, always let SQL use as much as
> > possible.
> >
> > JP
> >
> > "Paul Cahill" wrote:
> >
> >> If you use the /3GB switch should the memory slider in SQL Enterprise
> >> Manager display up to 3GB or do you set manually and just assume it's
> >> working?
> >> 2000 Advanced server and SQL 2000 Enterprise Edition.
> >>
> >> Thanks
> >> Paul
> >>
> >>
> >>
>
>|||Thanks JP.
Any idea why you have /PAE? I thought there's got to be an overhead for
address translation.
You don't feel like trying something for me......
Paul
"JP de Jong" <JPdeJong@.discussions.microsoft.com> wrote in message
news:45DC15E7-3C0D-4DD0-ACEE-732E9A8504E0@.microsoft.com...
> Paul
> Just checked it for you.
> We have 4GB RAM in server (Windows 2003Server). In BoOT.INI we have "/3GB
> /PAE". (so we do have PAE enabled).
> In properties of system (my computer) it says. Physical Address Extensions
> enabled. Not sure how to check if /3GB is enabled. You're absolutelt right
> that /PAE only is effective when you have more than 4 GB.
> /3GB only enables applications to use more virtual memory. I'm not quite
> sure if you can find that back in the gui of SQL.
> JP
> "Paul Cahill" wrote:
>> Thanks JP.
>> Have you got 4GB Ram and if so are you just using /3GB and not PAE or
>> AWE.
>> I have exactly 4GB ram. If I use PAE the slider in enterprise manager
>> shows
>> 4GB max. With /3GB I only see 2GB.
>> I don't want to have the overhead of /PAE and it should not be necessary.
>> Is
>> there any way to see if /3GB is working (from an NT or SQL)?
>> Paul
>>
>> "JP de Jong" <JP de Jong@.discussions.microsoft.com> wrote in message
>> news:1447D55D-C78C-41E8-87EB-FDF4C0E082D8@.microsoft.com...
>> > Paul,
>> >
>> > The maximum value of the slider should show a value of 3-something .
>> > (in
>> > my
>> > environments it's 3455).
>> >
>> > I would suggest not setting a fixed size, always let SQL use as much as
>> > possible.
>> >
>> > JP
>> >
>> > "Paul Cahill" wrote:
>> >
>> >> If you use the /3GB switch should the memory slider in SQL Enterprise
>> >> Manager display up to 3GB or do you set manually and just assume it's
>> >> working?
>> >> 2000 Advanced server and SQL 2000 Enterprise Edition.
>> >>
>> >> Thanks
>> >> Paul
>> >>
>> >>
>> >>
>>

48 Second Query Takes 40+ minutes To Display in Report Manager

I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
limitation, but here's the situation.
I have a report based on a single query that accepts 3 parameters:
1) Company (0/1)
2) Month (Date)
3) Retrieve either YTD or Monthly values (0/1)
Executing the query in Query Analyzer using parameters that would be the
worst case scenario from a processing stand point, it takes 48 seconds to
execute, and returns 2,054 rows (by 15 columns). Using parameters for the
best case scenario, it takes about 2-4 seconds, and returns 38 rows.
I can easily execute the best case scenario in SRS to display the report,
and it takes about 5-7 seconds to render as a web page. I continue to
time-out on the worst case scenario report, even after setting the timeout
value to 40 minutes in Site Settings in Report Manager.
Help! We've recommended the SRS architecture to the customer as a solution
over Crystal Reports. It now appears that large (even though it's only 2000
rows) result sets using 2+ groupings is causing SRS to seize in rendering the
report.
Any ideas or suggestions as to how to troubleshoot this?
Thanks!please post ddl, your query etc to give us an idea on how to replicate your
problem. you don't give us much to go on. returning 2,000 + rows is not an
issue in reporting services.
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!|||OK, thanks Mike. I'll include the RDL file, the script for the source tables,
and the query. If you offer any tips for trouble shooting this, that would be
great. I can't believe that a 48 second query takes 40 minutes with SRS.
Link To Download Here (very small file):
http://home.comcast.net/~wcsmith/KM/SRS.zip
Thanks!!!
"mike" wrote:
> please post ddl, your query etc to give us an idea on how to replicate your
> problem. you don't give us much to go on. returning 2,000 + rows is not an
> issue in reporting services.
> "Smit-Dog" wrote:
> > I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> > limitation, but here's the situation.
> >
> > I have a report based on a single query that accepts 3 parameters:
> >
> > 1) Company (0/1)
> > 2) Month (Date)
> > 3) Retrieve either YTD or Monthly values (0/1)
> >
> > Executing the query in Query Analyzer using parameters that would be the
> > worst case scenario from a processing stand point, it takes 48 seconds to
> > execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> > best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> >
> > I can easily execute the best case scenario in SRS to display the report,
> > and it takes about 5-7 seconds to render as a web page. I continue to
> > time-out on the worst case scenario report, even after setting the timeout
> > value to 40 minutes in Site Settings in Report Manager.
> >
> > Help! We've recommended the SRS architecture to the customer as a solution
> > over Crystal Reports. It now appears that large (even though it's only 2000
> > rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> > report.
> >
> > Any ideas or suggestions as to how to troubleshoot this?
> >
> > Thanks!|||Hi SmitDog,
Could you please try something? Hard code the values of the parameters in
your query. See if this improves the performance. If it does, please send
me a good repro and I will find out why this is happening. I tried to repro
in house but not been able to yet.
--
| Thread-Topic: 48 Second Query Takes 40+ minutes To Display in Report
Manager
| thread-index: AcUFWClju0gpbyR3SsGjzN0z7PcJMQ==| X-WBNR-Posting-Host: 68.61.30.192
| From: "=?Utf-8?B?U21pdC1Eb2c=?=" <SmitDog@.discussions.microsoft.com>
| Subject: 48 Second Query Takes 40+ minutes To Display in Report Manager
| Date: Fri, 28 Jan 2005 08:41:05 -0800
| Lines: 27
| Message-ID: <EABFFAF9-25FC-4F39-8EEB-DAF43A60C0F4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41210
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I don't know if I've quickly hit a SQL Reporting Services (SRS)
architecture
| limitation, but here's the situation.
|
| I have a report based on a single query that accepts 3 parameters:
|
| 1) Company (0/1)
| 2) Month (Date)
| 3) Retrieve either YTD or Monthly values (0/1)
|
| Executing the query in Query Analyzer using parameters that would be the
| worst case scenario from a processing stand point, it takes 48 seconds to
| execute, and returns 2,054 rows (by 15 columns). Using parameters for the
| best case scenario, it takes about 2-4 seconds, and returns 38 rows.
|
| I can easily execute the best case scenario in SRS to display the report,
| and it takes about 5-7 seconds to render as a web page. I continue to
| time-out on the worst case scenario report, even after setting the
timeout
| value to 40 minutes in Site Settings in Report Manager.
|
| Help! We've recommended the SRS architecture to the customer as a
solution
| over Crystal Reports. It now appears that large (even though it's only
2000
| rows) result sets using 2+ groupings is causing SRS to seize in rendering
the
| report.
|
| Any ideas or suggestions as to how to troubleshoot this?
|
| Thanks!
||||BTW, here is the query I'm using as a benchmark. You won't have the data, but
it basically takes 1-3 seconds when using Company = 1 (returning about 40
rows), and about 50 seconds when going against Company = 0 (returning about
2000 rows) in Query Analyzer.
Company = 1 in Report Manager renders in about 5-7 seconds. Company = 0
takes about 45 minutes. FWIW, this is on a 1.3GHz laptop with 512MB RAM,
using SQL Server 2000 (SP3).
Also, the big difference is that Company = 1 only has 59,000 records it's
going against, whereas Company=0 has 2,000,000 records it has to query
against.
exec sp_GetKeyJobSiteIndicatorsData 0, '2004-06-01', 0
"Smit-Dog" wrote:
> OK, thanks Mike. I'll include the RDL file, the script for the source tables,
> and the query. If you offer any tips for trouble shooting this, that would be
> great. I can't believe that a 48 second query takes 40 minutes with SRS.
> Link To Download Here (very small file):
> http://home.comcast.net/~wcsmith/KM/SRS.zip
> Thanks!!!
> "mike" wrote:
> > please post ddl, your query etc to give us an idea on how to replicate your
> > problem. you don't give us much to go on. returning 2,000 + rows is not an
> > issue in reporting services.
> >
> > "Smit-Dog" wrote:
> >
> > > I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> > > limitation, but here's the situation.
> > >
> > > I have a report based on a single query that accepts 3 parameters:
> > >
> > > 1) Company (0/1)
> > > 2) Month (Date)
> > > 3) Retrieve either YTD or Monthly values (0/1)
> > >
> > > Executing the query in Query Analyzer using parameters that would be the
> > > worst case scenario from a processing stand point, it takes 48 seconds to
> > > execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> > > best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> > >
> > > I can easily execute the best case scenario in SRS to display the report,
> > > and it takes about 5-7 seconds to render as a web page. I continue to
> > > time-out on the worst case scenario report, even after setting the timeout
> > > value to 40 minutes in Site Settings in Report Manager.
> > >
> > > Help! We've recommended the SRS architecture to the customer as a solution
> > > over Crystal Reports. It now appears that large (even though it's only 2000
> > > rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> > > report.
> > >
> > > Any ideas or suggestions as to how to troubleshoot this?
> > >
> > > Thanks!|||Hey Brad,
I posted a link to the basic files involved a couple of posts up. It's
missing the data, and that may be the deal-breaker in reproducing the
problem. I can supply a link to a DB backup if that would help, or perhaps
you can see something in my query, table structure, or RDL that would cause
this severe degradation in performance.
I'll try hardcoding the parameters and see if that helps.
""Brad Syputa - MS"" wrote:
> Hi SmitDog,
> Could you please try something? Hard code the values of the parameters in
> your query. See if this improves the performance. If it does, please send
> me a good repro and I will find out why this is happening. I tried to repro
> in house but not been able to yet.
> --
> | Thread-Topic: 48 Second Query Takes 40+ minutes To Display in Report
> Manager
> | thread-index: AcUFWClju0gpbyR3SsGjzN0z7PcJMQ==> | X-WBNR-Posting-Host: 68.61.30.192
> | From: "=?Utf-8?B?U21pdC1Eb2c=?=" <SmitDog@.discussions.microsoft.com>
> | Subject: 48 Second Query Takes 40+ minutes To Display in Report Manager
> | Date: Fri, 28 Jan 2005 08:41:05 -0800
> | Lines: 27
> | Message-ID: <EABFFAF9-25FC-4F39-8EEB-DAF43A60C0F4@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:41210
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I don't know if I've quickly hit a SQL Reporting Services (SRS)
> architecture
> | limitation, but here's the situation.
> |
> | I have a report based on a single query that accepts 3 parameters:
> |
> | 1) Company (0/1)
> | 2) Month (Date)
> | 3) Retrieve either YTD or Monthly values (0/1)
> |
> | Executing the query in Query Analyzer using parameters that would be the
> | worst case scenario from a processing stand point, it takes 48 seconds to
> | execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> | best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> |
> | I can easily execute the best case scenario in SRS to display the report,
> | and it takes about 5-7 seconds to render as a web page. I continue to
> | time-out on the worst case scenario report, even after setting the
> timeout
> | value to 40 minutes in Site Settings in Report Manager.
> |
> | Help! We've recommended the SRS architecture to the customer as a
> solution
> | over Crystal Reports. It now appears that large (even though it's only
> 2000
> | rows) result sets using 2+ groupings is causing SRS to seize in rendering
> the
> | report.
> |
> | Any ideas or suggestions as to how to troubleshoot this?
> |
> | Thanks!
> |
>|||One other weird thing that may be related...
Every few days, when running the query in QA using parameters for a worst
case scenerio, it would take 30-35 mintues to run the query. For some reason
it went from under a minute to 30x longer. To find the bottleneck, I
commented out the grouping of CASE statements that would SUM values based on
the code, and used hardcoded values instead. This immediately took the query
(again, in QA) from 30 minutes back down to 20 seconds or so. I slowly went
back to the SP and began to add back the CASE statements one by one, trying
to determine which one was causing the bottleneck. As I added them back, the
query time jumped back up to 48 seconds, very acceptable and suddendly back
to where it was before. Even after adding back all CASE statements, the query
was back down to 48 seconds.
I have no idea as to why this "fixes" the query, and the resulting code is
exactly the same. Perhaps when it "recompiles" it, it's internal algorithm is
fixed? Sorry this is so weird, but it is outside my area of expertise.
FWIW, these are the CASE statements and hardcoded values I toggle between to
"fix" the query.
**************************
sum ( case when ( seg1_code >= 6100 and seg1_code <=6145 ) then Balance end)
as BalanceAdditionalLabor,
sum ( case when ( seg1_code >= 6310 and seg1_code <=6400 ) then Balance end)
as BalanceControllables,
sum ( case when ( seg1_code >= 6001 and seg1_code <= 6008) then Balance end)
as BalanceDirectLabor,
sum ( case when ( ( ( seg1_code >= 6701 and seg1_code <=6785 ) or seg1_code
in ( 6002, 8000, 8005 ) ) ) then Balance end) as BalanceFixed,
sum ( case when ( seg1_code =6201 ) then Balance end) as BalanceMaterials,
sum ( case when ( seg1_code = 6204 ) then Balance end) as BalanceRepairs,
sum ( case when ( seg1_code >= 5001 and seg1_code <= 5999 ) then Balance
end) as BalanceSales,
sum ( case when ( seg1_code =5002 ) then Balance end) as BalanceSpecial,
sum ( case when ( seg1_code = 5100 ) then Balance end) as BalanceSubContractor
--BalanceAdditionalLabor=1,
--BalanceControllables=1,
--BalanceDirectLabor=1,
--BalanceFixed=1,
--BalanceMaterials=1,
--BalanceRepairs=1,
--BalanceSales=1,
--BalanceSpecial=1,
--BalanceSubContractor=1
****************************
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!|||OK... I've done some more testing, and this may not be related to SRS, but
rather some weirdness going on with the query itself.
It appears that I need to comment out a set of CASE statements, substitute
them with hardcoded values, execute the query a few times, un-comment the
CASE statements, then the query takes 48 seconds to run instead of 45 minutes.
I have gone through this little exercise dozens of times, and it magically
"fixes" the query everytime. Again, no perceived performance problem when the
query is run against the smaller company (much smaller result set - 40
records), just when going against the company that returns a large record set
(2400 records).
It's almost as if the query processor gets tangled in a funk, and going
through the comment/un-commenting of the CASE statments allows it to
recompile cleanly/correctly.
Unless someone has a clue as to what would be causing this weirdness, or can
see something glaringly wrong with the query, I'm to the point where I
re-write the query to get rid of the case statements altogether.
Any ideas or suggestions?
Thanks!
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!|||... And I've found out that once I execute the query using Company = 1, then
go back and execute it using Company = 2, the stored procedure is "broke"
again, and takes 40+ minutes to execute. I have to edit the SP, comment out
the CASE statetments, execute it using hardcoded values instead, go back and
un-comment the CASE statements, then using Company = 0 takes 45 seconds.
What the heck is going on here?
"Smit-Dog" wrote:
> I don't know if I've quickly hit a SQL Reporting Services (SRS) architecture
> limitation, but here's the situation.
> I have a report based on a single query that accepts 3 parameters:
> 1) Company (0/1)
> 2) Month (Date)
> 3) Retrieve either YTD or Monthly values (0/1)
> Executing the query in Query Analyzer using parameters that would be the
> worst case scenario from a processing stand point, it takes 48 seconds to
> execute, and returns 2,054 rows (by 15 columns). Using parameters for the
> best case scenario, it takes about 2-4 seconds, and returns 38 rows.
> I can easily execute the best case scenario in SRS to display the report,
> and it takes about 5-7 seconds to render as a web page. I continue to
> time-out on the worst case scenario report, even after setting the timeout
> value to 40 minutes in Site Settings in Report Manager.
> Help! We've recommended the SRS architecture to the customer as a solution
> over Crystal Reports. It now appears that large (even though it's only 2000
> rows) result sets using 2+ groupings is causing SRS to seize in rendering the
> report.
> Any ideas or suggestions as to how to troubleshoot this?
> Thanks!

404 on Report Manager

My report server has been in operation for 2 months now and my report
distribution portion works fine. But yesterday I started getting this
message "The request failed with HTTP status 404: Object Not Found." on my
report manager.
1. Any ideas how to fix it.
2. If it is a reinstall how do I save the RDL files.
Windows 2000 sp4
SQL Server 2000 sp3are you using SSL? If yes, make sure your Certificate haven't expired yet.
I had a similar issue & my cert had expired.
Also, try removing & enabling SSL.
"s" wrote:
> My report server has been in operation for 2 months now and my report
> distribution portion works fine. But yesterday I started getting this
> message "The request failed with HTTP status 404: Object Not Found." on my
> report manager.
> 1. Any ideas how to fix it.
> 2. If it is a reinstall how do I save the RDL files.
>
> Windows 2000 sp4
> SQL Server 2000 sp3
>
>|||No we are not using SSL
"mresanchez" wrote:
> are you using SSL? If yes, make sure your Certificate haven't expired yet.
> I had a similar issue & my cert had expired.
> Also, try removing & enabling SSL.
> "s" wrote:
> > My report server has been in operation for 2 months now and my report
> > distribution portion works fine. But yesterday I started getting this
> > message "The request failed with HTTP status 404: Object Not Found." on my
> > report manager.
> >
> > 1. Any ideas how to fix it.
> > 2. If it is a reinstall how do I save the RDL files.
> >
> >
> > Windows 2000 sp4
> > SQL Server 2000 sp3
> >
> >
> >

404 error in the report manager

hi,
I have installed the reporting serivces, the report server is working
fine,
But when i try to view the report manager, http://<servername>/reports
it gives a error message
"The request failed with HTTP status 404: Object Not Found. "
I have seen the virtual directories and the rswebapplication.config
also but i was not able to trace the error so any one please help me..
Thanks and Regards
JaganJagan,
How did you fix this problem? I am getting same error.
Thanks
--
Thanks
"Jagan" wrote:
> hi,
> I have installed the reporting serivces, the report server is working
> fine,
> But when i try to view the report manager, http://<servername>/reports
> it gives a error message
> "The request failed with HTTP status 404: Object Not Found. "
> I have seen the virtual directories and the rswebapplication.config
> also but i was not able to trace the error so any one please help me..
> Thanks and Regards
> Jagan
>

400: Bad Request

I am getting an "The request failed with HTTP status 400: Bad Request " Error in Report Manager Webpage. http://servername/Reports
The Reportserver itself and reports works fine (http://servername/reportserver).
Reports render OK and i am still able to connect to reportserver by management studio.

The environment is a W2K3 Standard with SQL 2005 (Build 2153) - and the server is a domain controller.

Reporting Service Configuration does not mention any issues - I have reapplied default settings - no change.
There are no errors in the eventlog.

I need some help - please

HANNESI have already checked the following posts

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=284929&SiteID=1
(I did not understend the "RSWebApplication.config" file - as soon as i write in the a reportserver url the website returns an execption - so this config must be empty I guess)

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=479636&SiteID=1|||This post gave me the finale hint (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=165851&SiteID=1) - but I do not habe a specific IP address bound to the website - but I had specific hostheaders and it seems the reportmanager needs at least one binding with no hosthader in my environment - therefor I added a fifth binding with no hosthander, no specific IP and default Port

Hannes

3rd party Data Administration Tools

I'm looking for a 3rd party tool to manage
disabling/enabling triggers. I evaluated Teratraw
Database Manager and it easily allows disabling/enabling
triggers via the gui but only 1 trigger at a time. I need
a tool that can disable/enable ALL triggers in a database
easily. (Similar to TOAD for Oracle).
Anyone know of such a management tool?
Thanks Rich
SQLExecMS from www.laplas-soft.com will help you
"Rich" <rstoll@.cadencenet.com> wrote in message
news:1c5701c5317f$dc1023a0$a601280a@.phx.gbl...
> I'm looking for a 3rd party tool to manage
> disabling/enabling triggers. I evaluated Teratraw
> Database Manager and it easily allows disabling/enabling
> triggers via the gui but only 1 trigger at a time. I need
> a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
> Thanks Rich
|||Would a stored procedure work for you?
"Rich" wrote:

> I'm looking for a 3rd party tool to manage
> disabling/enabling triggers. I evaluated Teratraw
> Database Manager and it easily allows disabling/enabling
> triggers via the gui but only 1 trigger at a time. I need
> a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
> Thanks Rich
>
|||
> I'm looking for a 3rd party tool to manage
> disabling/enabling triggers. I evaluated Teratraw
> Database Manager and it easily allows disabling/enabling
> triggers via the gui but only 1 trigger at a time. I need
> a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
Database Workbench - www.upscene.com
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
|||Hello Rich,
TOAD for SQL Server is currently in freeware (http://www.toadsoft.com/toadsqlserve..._sqlserver.htm), but a -much- more feature-laden version will go into beta in June. Take a look at the current version and stay on the lookout for the beta announceme
nt for the big summer release.
Hope this helps,
-Kev
~~~
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

> I'm looking for a 3rd party tool to manage disabling/enabling
> triggers. I evaluated Teratraw Database Manager and it easily allows
> disabling/enabling triggers via the gui but only 1 trigger at a time.
> I need a tool that can disable/enable ALL triggers in a database
> easily. (Similar to TOAD for Oracle).
> Anyone know of such a management tool?
> Thanks Rich
>