Please help lost connection urgent!

Hi,
After I install Service Pack3 on my local PC machine, I cannot connect to
the Development Server by using Query Analyzer and SQL Profiler. but the
strangest thing is i can connect to that server by using Enterprise Manager.
The error message showing on QA is
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied. -- it really doesn't make sense to me to only QA but not EM
.
Please help and thanks
EdThat may help:
http://groups.google.de/groups? q=S...ftngxa06&rnum=1
HTH, Jens Smeyer.
"Ed" <Ed@.discussions.microsoft.com> schrieb im Newsbeitrag
news:C80F022D-B11F-432A-9B86-5A9F86232244@.microsoft.com...
> Hi,
> After I install Service Pack3 on my local PC machine, I cannot connect
> to
> the Development Server by using Query Analyzer and SQL Profiler. but the
> strangest thing is i can connect to that server by using Enterprise
> Manager.
> The error message showing on QA is
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied. -- it really doesn't make sense to me to only QA but not
> EM.
> Please help and thanks
> Ed|||I'll quote you some possible solutions from sswug.org mailing list:
I recall that changing the port at which a named instance exists requires
restart of the default instance (or perhaps just SQL Agent?). If you can
easily restart the default instance and agent on the DEV box, I would do
so.
--
Fixed the problem on the DEV server. When the instance came back up, it
was for some reason, listening on 1433.... same as the default instance.
Changed the port number in SNU, restarted the services, and can connect
fine now.
--
Did you check the local Windows firewall settings on the MSDE machine?
You have to explicitly enable TCP and/or named pipes (globally or for a
single instance of SQL).
--
Is this a clustered instance? If so, disabling named pipes messes up the
named pipes reg key and this messes up both TCP and named pipes
connectivity.
Can you connect locally or does that fail as well? What about if you
force a specific protocol or port?
Np:Server\Instance
TCP:server\instance
LPC:server\instance
TCP:server\instance,port
Maybe some of these q&a's may give an idea.
Regards,
Marko Simic
"Ed" wrote:

> Hi,
> After I install Service Pack3 on my local PC machine, I cannot connect
to
> the Development Server by using Query Analyzer and SQL Profiler. but the
> strangest thing is i can connect to that server by using Enterprise Manage
r.
> The error message showing on QA is
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied. -- it really doesn't make sense to me to only QA but not
EM.
> Please help and thanks
> Ed|||Thanks for the answer from both.
I spent some much time looking at MS website to find the solution.
Finally, after I downloaded the MDAC 2.8 (newest version), everything is
back to normal now.
Ed
"Simic Marko" wrote:
> I'll quote you some possible solutions from sswug.org mailing list:
> --
> I recall that changing the port at which a named instance exists requires
> restart of the default instance (or perhaps just SQL Agent?). If you can
> easily restart the default instance and agent on the DEV box, I would do
> so.
> --
> Fixed the problem on the DEV server. When the instance came back up, it
> was for some reason, listening on 1433.... same as the default instance.
> Changed the port number in SNU, restarted the services, and can connect
> fine now.
> --
> Did you check the local Windows firewall settings on the MSDE machine?
> You have to explicitly enable TCP and/or named pipes (globally or for a
> single instance of SQL).
> --
> Is this a clustered instance? If so, disabling named pipes messes up the
> named pipes reg key and this messes up both TCP and named pipes
> connectivity.
> Can you connect locally or does that fail as well? What about if you
> force a specific protocol or port?
> Np:Server\Instance
> TCP:server\instance
> LPC:server\instance
> TCP:server\instance,port
> Maybe some of these q&a's may give an idea.
> Regards,
> Marko Simic
> "Ed" wrote:
>

Please help its urgent

i have a data which will be updated daily in database.. my crystal report will show all that data..

Now here is da query on which i need help..

Take an example i have values shown on report.. eg

100

300

500

900

1000

.......

note above values will keep on increasing every day..

OUTPUT should be

300 - 100 = 200

500 - 300= 200 .......

so its like i want difference by subracting second value (D2) minus (D1) and then (D3) minus (D2) and so on till it encounters last row.

ACTUAL VALUES ........ how can i get
FROM DB ....... DIFFERENCE OUTPUT ...... OUTPUT

100 ............... ( 300 minus 100 ) ....... 200

300 ............... ( 500 minus 300 ) ....... 200

500 ............... ( 900 minus 500 ) ....... 400

900 ............... ( 1000 minus 900 ) ....... 100

1000 ............... last row will be blank as below this der is no row

i hope u ppl get ma query and plz help me.. its urgentNext({field}) - {field}

Please help - urgent!

I have a matrix that displays data for tags per day on each page. The
matrix
has a grouped column that does the grouping by day (this column is returned
from a stored procedure) and page breaks at the end of the day.
This is what I need - before I page break, I need to also display the
statistics per day like avg, min and max values for that day for all the
tags.
Date Tag1 Tag2 Tag3 Tag4
======================================== 10/01/2003 00:00 2 4 6 7
10/01/2003 01:00 2 4 6 7
10/01/2003 02:00 2 4 6 7
10/01/2003 03:00 2 4 6 7
......
10/01/2003 21:00 2 4 6 7
10/01/2003 22:00 2 4 6 7
10/01/2003 23:00 2 4 6 7
========================================= Daily Statistics
--
Sum: 48 96 144 168
Average: 2 4 6
7
Min: 2 4 6
7
Max: 2 4 6
7
==========================================
======> the first one is the matrix (grouped by day and page breaks after
each day)
=======> The Daily Statistics part (summary) I am not able to do. Since the
matrix has a page break per
day, I am not able to get the statistics also on the same page for that day.
Any help will be highly appreciated. Thanks.Try putting your matrix inside a table. Your outer group goes in the
table -- with summaries in the table group footer -- and your inner group is
handled in the matrix.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"KMP" <KMP@.msn.com> wrote in message
news:%23dnvGhcFFHA.1936@.TK2MSFTNGP14.phx.gbl...
>I have a matrix that displays data for tags per day on each page. The
> matrix
> has a grouped column that does the grouping by day (this column is
> returned
> from a stored procedure) and page breaks at the end of the day.
> This is what I need - before I page break, I need to also display the
> statistics per day like avg, min and max values for that day for all the
> tags.
> Date Tag1 Tag2 Tag3 Tag4
> ========================================> 10/01/2003 00:00 2 4 6 7
> 10/01/2003 01:00 2 4 6 7
> 10/01/2003 02:00 2 4 6 7
> 10/01/2003 03:00 2 4 6 7
> ......
> 10/01/2003 21:00 2 4 6 7
> 10/01/2003 22:00 2 4 6 7
> 10/01/2003 23:00 2 4 6 7
> =========================================> Daily Statistics
> --
> Sum: 48 96 144
> 168
> Average: 2 4 6
> 7
> Min: 2 4 6
> 7
> Max: 2 4 6
> 7
> ==========================================> ======> the first one is the matrix (grouped by day and page breaks after
> each day)
> =======> The Daily Statistics part (summary) I am not able to do. Since
> the
> matrix has a page break per
> day, I am not able to get the statistics also on the same page for that
> day.
> Any help will be highly appreciated. Thanks.
>
>
>|||Thank you very much. It worked.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:uyFiy2cFFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Try putting your matrix inside a table. Your outer group goes in the
> table -- with summaries in the table group footer -- and your inner group
is
> handled in the matrix.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "KMP" <KMP@.msn.com> wrote in message
> news:%23dnvGhcFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> >I have a matrix that displays data for tags per day on each page. The
> > matrix
> > has a grouped column that does the grouping by day (this column is
> > returned
> > from a stored procedure) and page breaks at the end of the day.
> >
> > This is what I need - before I page break, I need to also display the
> > statistics per day like avg, min and max values for that day for all the
> > tags.
> >
> > Date Tag1 Tag2 Tag3 Tag4
> > ========================================> > 10/01/2003 00:00 2 4 6 7
> > 10/01/2003 01:00 2 4 6 7
> > 10/01/2003 02:00 2 4 6 7
> > 10/01/2003 03:00 2 4 6 7
> > ......
> > 10/01/2003 21:00 2 4 6 7
> > 10/01/2003 22:00 2 4 6 7
> > 10/01/2003 23:00 2 4 6 7
> > =========================================> > Daily Statistics
> > --
> > Sum: 48 96 144
> > 168
> > Average: 2 4 6
> > 7
> > Min: 2 4 6
> > 7
> > Max: 2 4 6
> > 7
> > ==========================================> >
> > ======> the first one is the matrix (grouped by day and page breaks
after
> > each day)
> > =======> The Daily Statistics part (summary) I am not able to do. Since
> > the
> > matrix has a page break per
> > day, I am not able to get the statistics also on the same page for that
> > day.
> >
> > Any help will be highly appreciated. Thanks.
> >
> >
> >
> >
> >
>sql

Please give me your suggestions on Report Query - Very Urgent

I need to disaplay number of Active Agencies on monthwise in one of my report. I have tbl_Agency table with ActiveDate and ActiveFlag. ActiveDate column contains always first Activation Date. If any chances in the agencies(update/delete) the same record will move to tbl_AgencyHistory table.

"If an agency is inactivated in September 10th, inactivated all of October, and then reactivated November 10th - the agency would be counted in September, not in October and counted in November"

ActiveDate column has always first activation date, I could not meet this requirement. This is very urgent issue, Could you please help me on this.

Thanks,

Malar

Malar:

You want a monthly summary such as:

Month ActiveCt
-- -
2006 January 27
2006 February 30
2006 March 45
2006 April 55
2006 May 61
2006 August 27
2006 September 29
2006 October 35
2006 November 60
2006 December 63

The source tables need to include both the home table plus the history table?

Dave

|||

Malar:

Please review the following scenario and verify if I understand your update process correctly:

Acme Coyote Co gets first activitated on 8/15:

tbl_agency: agencyName='Acme Coyote Co', activeFlag='Y', activenDate='8/15/6'
( there are no history records for 'Acme Coyote Co'

Acme Coyote Co goes inactive on 9/10:

tbl_agency: agencyName='Acme Coyote Co', activeFlag='N', activeDate='9/10/6'
tbl_agencyHist: seq=1, agencyName='Acme Coyote Co', activeFlag='Y', activeDate='8/15/6'

Acme Coyote Co re-activates on 11/07:

tbl_agency: agencyName='Acme Coyote Co', activeFlag='Y', activeDate='11/7/6'
tbl_agencyHist: seq=1, agencyName='Acme Coyote Co', activeFlag='Y', activeDate='8/15/6'
tbl_agencyHist: seq=2, agencyName='Acme Coyote Co', activeFlag='N', activeDate='9/10/6'

|||

Hi Mugambo,

Thanks for your reply. I need to display out put like this.

Year Month ExistingActiveCt NewActiveCt
-- -
2006 January 27 3
2006 February 30 15
2006 March 45 10
2006 April 55 6
2006 May 61 0
2006 August 27 2
2006 September 29 6
2006 October 35 25
2006 November 60 3
2006 December 63 0

Above your query explanation is correct. We have ModifiedOn column in both the tables. If any of Agency is inactivated, tbl_Agency table in ModifiedOn is updated with current date. The previous record is in historty table ModifiedOn also updated with current date.

Thanks,

Malar

|||

Malar:

Please forgive the long-winded jazz that follows. This went much more over-the-top for me that I had planned. First, I use my iterator table -- a table of numbers -- as part of the routine. The iterator table can implemented as:

-- -
-- I use my iterator table as a read-only untility table from time to time.
-- The best write-up for this type of thing is given as a table of numbers
-- at the website:
--
-- http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
-- -
if exists
( select 0 from sysobjects
where type = 'U'
and id = object_id ('dbo.SMALL_ITERATOR')
)
drop table dbo.SMALL_ITERATOR
go

create table dbo.SMALL_ITERATOR
( iter smallint not null
constraint PK_SMALL_ITERATOR primary key
)
go

-- -
-- Here, we use the master "spt_values" table to load initial values into
-- the small iterator table.
--
-- NOTE:
--
-- Use of the spt_values in this situation is an undocumented SQL Server
-- feature. In fact, SQL Server 2000 has values 0-255 while SQL Server
-- 2005 has values 0-2047 so beware! This "built-in table of numbers" can
-- be dangerous!
--
-- I restrict the domain of numbers here 0-255 for the sake of providing a
-- little more safety.
-- -
truncate table SMALL_ITERATOR

insert into small_iterator
select number from master.dbo.spt_values
where name is null
and number <= 255

insert into small_iterator
select 256 * j.iter + i.iter
from small_iterator i
inner join small_iterator j
on j.iter > 0
and j.iter <= 128
and 256 * j.iter + i.iter <= 32767

delete from small_iterator where iter = 0

select count(*) [count],
min (iter) [min iterator],
max (iter) [max iterator]
from SMALL_ITERATOR

go

dbcc dbreindex (small_iterator, '', 100)
go

update statistics small_iterator
go

exec sp_recompile small_iterator
go

The actual report code:

-- -
-- Hang on to your hat and holy moly this is nasty.
--
-- I guess what bugs me most about all of this has to do with the UNIONs
-- that are necessary because not all of the records are retained in the
-- history table.
--
-- Notice that there are at least a couple of benefits to be derived by
-- keeping all records -- even a copy of the current record -- in the
-- history table:
--
-- (1) The UNIONS for this report and for any other report that must
-- account for history are eliminated
-- (2) Elimination the UNIONS means that these types of report processes,
-- which likely process by table scan, now no longer have to impact
-- the current record table. This can eliminate a heavy kind of
-- contention away from the primary table
-- -
set nocount on
set ansi_warnings off

declare @.matrixYr char (4) set @.matrixYr = '2006'
declare @.baseDt datetime set @.baseDt = '1/1/'+@.matrixYr

--select @.matrixYr as [@.matrixYr],
-- @.baseDt as [@.baseDt]

-- -
-- Generate mockup table for testing. uncomment the other insert
-- statements to test with more data.
-- -
declare @.agency table
( agency_id integer not null primary key,
agencyName varchar (30) not null,
activeFlag char (1) not null,
activeDate datetime not null
)

declare @.agencyHist table
( agency_id integer not null,
seq integer not null,
agencyName varchar (30) not null,
activeFlag char (1) not null,
activeDate datetime not null
)

insert into @.agency values (1, 'Acme Coyote Co', 'Y', '11/7/6')
insert into @.AgencyHist values (1, 1, 'Acme Coyote Co', 'Y', '1/31/6')
insert into @.agencyHist values (1, 2, 'Acme Coyote Co', 'N', '2/14/6')
insert into @.agencyHist values (1, 3, 'Acme Coyote Co', 'Y', '4/17/6')
insert into @.agencyHist values (1, 4, 'Acme Coyote Co', 'N', '4/19/6')
insert into @.AgencyHist values (1, 5, 'Acme Coyote Co', 'Y', '8/15/6')
insert into @.agencyHist values (1, 6, 'Acme Coyote Co', 'N', '9/10/6')

/*
insert into @.agency values (2, 'Ralph''s Plumbing', 'N', '5/7/6')
insert into @.AgencyHist values (2, 1, 'Ralph''s Plumbing', 'Y', '12/7/5')

insert into @.agency values (3, 'Tricky Ricky Taffy', 'Y', '9/7/6')
insert into @.AgencyHist values (3, 1, 'Tricky Ricky Taffy', 'Y', '9/9/5')
insert into @.agencyHist values (3, 2, 'Tricky Ricky Taffy', 'N', '4/19/6')

insert into @.agency values (4, 'Gopher Tunnelling', 'Y', '7/14/6')

insert into @.agency values (5, 'Random Certainty Co', 'Y', '12/1/5')

insert into @.agency values (11, 'Bad Records 1', 'N', '4/21/6')
insert into @.AgencyHist values (11, 1, 'Bad Records 1', 'Y', '2/14/6')
insert into @.agencyHist values (11, 2, 'Bad Records 1', 'Y', '2/15/6')

insert into @.agency values (12, 'Bad Records 2', 'N', '7/7/6')
insert into @.AgencyHist values (12, 1, 'Bad Records 2', 'Y', '6/1/6')
insert into @.agencyHist values (12, 2, 'Bad Records 2', 'N', '6/1/6')

insert into @.agency values (13, 'Bad Records 3', 'N', '11/24/6')
insert into @.AgencyHist values (13, 1, 'Bad Records 3', 'Y', '9/14/6')
insert into @.agencyHist values (13, 2, 'Bad Records 3', 'Y', '10/21/6')
insert into @.agencyHist values (13, 3, 'Bad Records 3', 'N', '10/29/6')
*/

--print '-- @.AGENCY --'
--select * from @.agency
--select * from @.agencyHist

-- -
-- This is what scalds me the most. I don't care for the union
-- in combination with the self left join of the complement of the union.
--
-- It might be a good idea to use NOLOCK hints on each of these tables
-- since this is a report.
--
-- If you always get ZERO scrubbed records, the scrubbing routine can be
-- eliminated. To elimnate the scrub routine change the target table
-- of the next insert from @.rawInterval to @.interval and remove the code
-- that moves scrubbed records from the @.rawInterval table into the
-- @.interval table.
-- -
declare @.rawInterval table
( agency_id integer not null,
startDate datetime not null,
endDate datetime not null,
primary key (endDate, startDate, agency_id)
)

--insert into @.interval -- ONLY if the data is CLEAN!
insert into @.rawInterval
select a.agency_id,
a.activeDate as startDate,
isnull (min (b.activeDate), '12/31/3999 23:59:59.997') as endDate
from ( select agency_id,
agencyName,
activeDate
from @.agency
where activeFlag = 'Y'
union
select agency_id,
agencyName,
activeDate
from @.agencyHist
where activeFlag = 'Y'
) a

left join
( select agency_id,
agencyName,
activeDate
from @.agency
where activeFlag <> 'Y'
union
select agency_id,
agencyName,
activeDate
from @.agencyHist
where activeFlag <> 'Y'
) b
on a.agency_id = b.agency_id
and a.activeDate < b.activeDate
group by a.agency_id, a.activeDate

-- print '- RAW INTERVAL -'
-- select * from @.rawInterval

-- -
-- Scrub out the records that cause the problems
--
-- This section can be eliminated if the data is always clean.
-- -

declare @.interval table
( agency_id integer not null,
startDate datetime not null,
endDate datetime
)

insert into @.interval
select a.agency_id,
a.startDate,
a.endDate
from @.rawInterval a
inner join
( select s.agency_id,
min (s.startDate) as min_startDate,
endDate
from @.rawInterval s
group by s.agency_id, s.endDate
) b
on a.agency_id = b.agency_id
and a.startDate = b.min_startDate

--print '-- SCRUBBING: --'
--select * from @.interval

select ( select count(*) from @.rawInterval ) -
( select count(*) from @.interval ) as [Scrubbed Records]

-- -
-- After we do all that hand waving we now write this to an output summary
-- to hold the information. This information will be mergede with the
-- "first time" agencies after the "first time" agency information is
-- compiled.
--
-- Note that the select for this insert uses the "small_iterator" table
-- -
declare @.output table
( period varchar (15) not null,
monthBasis datetime not null,
ExistingActiveCt integer not null
)

insert into @.output
select period,
monthBasis,
sum ( case when i.agency_id is null then 0 else 1 end ) as existingActiveCt
from ( select left (@.matrixYr + ' ' + datename (month,
dateadd (month, iter - 1, @.baseDt)), 15) as period,
dateadd (month, iter - 1, @.baseDt) as monthBasis
from small_iterator (nolock)
where iter <= 12
) m
left join @.interval i
on dateadd (month, 1, m.monthBasis) > i.startDate
and ( m.monthBasis <= i.endDate or i.endDate is null )
group by monthBasis, period
order by monthBasis, period

-- -
-- Picking up the first time agencies looks a little better. But we are
-- still dealing with the same union.
-- -
declare @.firstTime table
( monthBasis datetime not null,
newActiveCt integer not null
)

insert into @.firstTime
select convert (datetime, activationMonth + '/1/' + activationYear),
newActiveCt
from ( select convert (varchar(2), month (activeDate)) as activationMonth,
convert (varchar(4), year (activeDate)) as activationYear,
count (*) as newActiveCt
from ( select agency_id,
min (activeDate) as activeDate
from ( select agency_id,
min(activeDate) as activeDate
from @.agency
where activeFlag = 'Y'
group by agency_id
union
select agency_id,
min (activeDate)
from @.agencyHist
where activeFlag = 'Y'
group by agency_id
) j
group by agency_id
having min (activeDate) >= @.baseDt
) x
group by convert (varchar(2), month (activeDate)),
convert (varchar(4), year (activeDate))
) act

--select * from @.firstTime

-- -
-- And after all of that work we get a small report.
--
-- What a way to run a railroad.
-- -
select a.period,
a.ExistingActiveCt,
isnull (b.NewActiveCt, 0) as NewActiveCt
from @.output a
left outer join @.firstTime b
on a.monthBasis = b.monthBasis
order by a.monthBasis

set ansi_warnings on

-- -
-- Output with "Acme Coyote Co" only
-- -

-- Scrubbed Records
-- -
-- 0

-- period ExistingActiveCt NewActiveCt
-- - --
-- 2006 January 1 1
-- 2006 February 1 0
-- 2006 March 0 0
-- 2006 April 1 0
-- 2006 May 0 0
-- 2006 June 0 0
-- 2006 July 0 0
-- 2006 August 1 0
-- 2006 September 1 0
-- 2006 October 0 0
-- 2006 November 1 0
-- 2006 December 1 0

-- -
-- Output with all records
-- -

-- Scrubbed Records
-- -
-- 2

-- period ExistingActiveCt NewActiveCt
-- - --
-- 2006 January 4 1
-- 2006 February 5 1
-- 2006 March 4 0
-- 2006 April 5 0
-- 2006 May 2 0
-- 2006 June 2 1
-- 2006 July 3 1
-- 2006 August 3 0
-- 2006 September 5 1
-- 2006 October 4 0
-- 2006 November 4 0
-- 2006 December 4 0

|||

Thanks Mugambo,

I have not used iteration. Because I need only 12 rows. Remaining logic is the same.

Thanks,

Malar

set nocount on
set ansi_warnings off

-- -
-- Generate mockup table for testing. uncomment the other insert
-- statements to test with more data.
-- -

declare @.agency table
( AgencyID integer not null primary key,
AgencyName varchar (30) not null,
ActiveInd BIT not null,
ActivationDt datetime not null
)
declare @.agencyHist table
( AgencyID integer not null,
seq integer not null, --primary key
AgencyName varchar (30) not null,
ActiveInd char (1) not null,
ActivationDt datetime not null
)

insert into @.agency values (1, 'Acme Coyote Co', 1, '9/10/6')
insert into @.AgencyHist values (1, 1, 'Acme Coyote Co', 0, '1/15/6')
insert into @.AgencyHist values (1, 2, 'Acme Coyote Co', 1, '1/31/6')
insert into @.agencyHist values (1, 3, 'Acme Coyote Co', 0, '2/14/6')
insert into @.agencyHist values (1, 4, 'Acme Coyote Co', 1, '4/17/6')
insert into @.agencyHist values (1, 5, 'Acme Coyote Co', 0, '4/19/6')
insert into @.AgencyHist values (1, 6, 'Acme Coyote Co', 1, '8/15/6')
insert into @.AgencyHist values (1, 7, 'Acme Coyote Co', 0, '9/10/6')
insert into @.agency values (2, 'Ralph''s Plumbing', 1, '7/7/6')
insert into @.AgencyHist values (2, 8, 'Ralph''s Plumbing', 0, '5/7/5')
insert into @.AgencyHist values (2, 9, 'Ralph''s Plumbing', 1, '5/7/6')
insert into @.AgencyHist values (2, 10, 'Ralph''s Plumbing', 0, '7/7/6')
insert into @.agency values (3, 'Tricky Ricky Taffy', 1, '4/19/6')
insert into @.AgencyHist values (3, 11, 'Tricky Ricky Taffy', 0, '9/9/5')
insert into @.AgencyHist values (3, 12, 'Tricky Ricky Taffy', 1, '10/9/5')
insert into @.agencyHist values (3, 13, 'Tricky Ricky Taffy', 0, '4/19/6')
insert into @.agency values (4, 'Gopher Tunnelling', 1, '7/14/6')
insert into @.agency values (5, 'Random Certainty Co', 1, '12/1/5')

/*insert into @.agency values (11, 'Bad Records 1', 0, '4/21/6')
insert into @.AgencyHist values (11, 14, 'Bad Records 1', 0, '2/14/6')
insert into @.agencyHist values (11, 15, 'Bad Records 1', 1, '4/21/6')

insert into @.agency values (12, 'Bad Records 2', 0, '7/7/6')
insert into @.AgencyHist values (12, 16, 'Bad Records 2', 1, '6/1/6')
insert into @.agencyHist values (12, 17, 'Bad Records 2', 1, '7/7/6')
insert into @.agency values (13, 'Bad Records 3', 0, '11/24/6')
insert into @.AgencyHist values (13, 18, 'Bad Records 3', 1, '9/14/6')
insert into @.agencyHist values (13, 19, 'Bad Records 3', 0, '10/21/6')
insert into @.agencyHist values (13, 20, 'Bad Records 3', 1, '11/24/6')
*/
--print '-- @.AGENCY --'
-- select * from @.agency
-- select * from @.agencyHist
DECLARE
@.StartDate DATETIME,
@.EndDate DATETIME,
@.TempFromDate DATETIME,
@.TempToDate DATETIME,
@.TotalRows INT,
@.LoopCount INT,
@.FromYear INT,
@.ToYear INT,
@.FromMonth INT,
@.ToMonth INT

DECLARE @.tempOutput TABLE
(
CountId INT IDENTITY(1,1) PRIMARY KEY,
Mnth CHAR(3),-- 1 to 12
Yr INT, -- 4 digit
StartDate DATETIME,
EndDate DATETIME
)

SELECT @.FromYear =2006,
@.ToYear =2006,
@.FromMonth =1,
@.ToMonth =12

SELECT @.StartDate = CAST(LTRIM(RTRIM(CAST(@.FromMonth AS CHAR(2)))) + '-01-' + CAST(@.FromYear AS CHAR(4)) AS DATETIME)

SELECT @.EndDate = CAST(LTRIM(RTRIM(CAST(@.ToMonth AS CHAR(2)))) + '-'
+ CAST(DAY(DATEADD(d, -DAY(DATEADD(m,1,CAST(CAST(@.ToMonth AS CHAR(2)) + '-01-'
+ CAST(@.ToYear AS CHAR(4)) AS DATETIME))),DATEADD(m,1,CAST(CAST(@.ToMonth AS CHAR(2))
+ '-01-' + CAST(@.ToYear AS CHAR(4)) AS DATETIME)))) AS CHAR(2)) + '-'
+ CAST(@.ToYear AS CHAR(4)) + ' 23:59:59.000' AS DATETIME)

SELECT @.LoopCount = 1
,@.TotalRows = DATEDIFF(Month, @.StartDate, @.EndDate) + 1
,@.TempFromDate = @.StartDate

SELECT @.TempToDate = CAST(LTRIM(RTRIM(CAST(DATEPART(Month,@.StartDate) AS CHAR(2)))) + '-'
+ CAST(DAY(DATEADD(d, -DAY(DATEADD(m,1,@.StartDate)),DATEADD(m,1,@.StartDate))) AS CHAR(2)) + '-'
+ CAST(LTRIM(RTRIM(DATEPART(Year,@.StartDate))) AS CHAR(4)) + ' 23:59:59.000' AS DATETIME)

-- Insert required number of rows
WHILE @.LoopCount <= @.TotalRows
BEGIN
INSERT INTO @.tempOutput
SELECT LEFT(CONVERT(CHAR(10),@.TempFromDate,107),3), YEAR(@.TempFromDate), @.TempFromDate, @.TempToDate
SET @.LoopCount = @.LoopCount + 1
SET @.TempFromDate = DATEADD(Month,1,@.TempFromDate)
SET @.TempToDate = CAST(LTRIM(RTRIM(CAST(DATEPART(Month,@.TempFromDate) AS CHAR(2)))) + '-' + CAST(DAY(DATEADD(d, -DAY(DATEADD(m,1,@.TempFromDate)),DATEADD(m,1,@.TempFromDate))) AS CHAR(2)) + '-' + CAST(LTRIM(RTRIM(DATEPART(Year,@.TempFromDate))) AS CHAR(4))
+ ' 23:59:59.000' AS DATETIME)

END
-- print temporary table with 12 records with start date and end date
-- select * from @.tempOutput

declare @.rawInterval table
( AgencyID integer not null,
startDate datetime not null,
endDate datetime not null,
primary key (endDate, startDate, AgencyID)
)
--insert into @.interval -- ONLY if the data is CLEAN!
insert into @.rawInterval
select a.AgencyID,
a.ActivationDt as startDate,
isnull (min (b.ActivationDt), '12/31/3999 23:59:59.997') as endDate
from ( select AgencyID,
AgencyName,
ActivationDt
from @.agency
where ActiveInd = 1
union
select AgencyID,
AgencyName,
ActivationDt
from @.agencyHist
where ActiveInd <> 1
) a
left join
( select AgencyID,
AgencyName,
ActivationDt
from @.agency
where ActiveInd <> 1
union
select AgencyID,
AgencyName,
ActivationDt
from @.agencyHist
where ActiveInd = 1
) b
on a.AgencyID = b.AgencyID
and a.ActivationDt < b.ActivationDt
group by a.AgencyID, a.ActivationDt
-- print '- RAW INTERVAL -'
--select * from @.rawInterval

-- New Agency Code
declare @.firstTime table
( monthBasis datetime not null,
newActiveCt integer not null
)

insert into @.firstTime
select convert (datetime, activationMonth + '/1/' + activationYear),
newActiveCt
from ( select convert (varchar(2), month (ActivationDt)) as activationMonth,
convert (varchar(4), year (ActivationDt)) as activationYear,
count (AgencyID) as newActiveCt
from ( select AgencyID,
min (ActivationDt) as ActivationDt
from ( select AgencyID,
min(ActivationDt) as ActivationDt
from @.agency
where ActiveInd = 1
group by AgencyID
union
select AgencyID,
min (ActivationDt)
from @.agencyHist
where ActiveInd = 1
group by AgencyID
) j
group by AgencyID
having min (ActivationDt) >= @.StartDate
) x
group by convert (varchar(2), month (ActivationDt)),
convert (varchar(4), year (ActivationDt))
) act
-- print new agencies
-- select * from @.firstTime

-- exisitng agency code
declare @.output table
( period varchar (15) not null,
monthBasis datetime not null,
ExistingActiveCt integer not null,
Mnth char(3) not null
)

insert into @.output
select period,
monthBasis,
sum ( case when i.AgencyID is null then 0 else 1 end ) as existingActiveCt,
Mnth
from (
select Mnth,Yr as period ,StartDate as monthBasis from @.tempOutput
) m
left join @.rawInterval i
on dateadd (month, 1, m.monthBasis) > i.startDate
and ( m.monthBasis <= i.endDate or i.endDate is null )
group by monthBasis, period, Mnth
order by monthBasis, period, Mnth

-- print existing agencies
-- select * from @.output

-- existing agencies and new agencies
select a.period,
a.ExistingActiveCt,
isnull (b.NewActiveCt, 0) as NewActiveCt,
a.monthBasis,
a.Mnth
from @.output a
left outer join @.firstTime b
on a.monthBasis = b.monthBasis
order by a.monthBasis

Please give kind attension to my queries very urgent

Please give kind attension to my queries

When I am giving like http://localhost/ReportServer the following message is coming in explorer. What I have to do please help

Reporting Services Error

---

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase) Get Online Help

---

SQL Server Reporting Services

Moving to RS forum.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||What version of reporting services are you using and what version of sql server did you point it to?|||

Thanku for attension

I am using version SQL server 2005

|||

This thread may help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=237706&SiteID=1

cheers,

Andrew

sql