Please Help Just Upsized Access To Sql And Now Code Doesnt Work

here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim db As DAO.Database <--believe to be the problem
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set db = CurrentDb() <-- -problem
Set rs = db.OpenRecordset(SQL) <--problem

If IsNull([Forms]![fpatient]![chartnumber]) = False Then GoTo 400

If rs.EOF = False Then
If IsNull(rs!RecNum) = False Then
NewNum = rs!RecNum + 1
Else
NewNum = 1
End If
End If
'If NewNum = 1 Then

[Forms]![fpatient]![chartnumber] = UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & Format(NewNum, "000000")
'End If

400 End Function

basically if you typw in john smith this code would put smijo00001 into chartnumber field now i get a run-time error 91.What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.|||Change you dao to ado - using recordset and/or connection objects.|||Originally posted by Teddy
What error are you receiving?

Also be advised that UCase is not supported in MS SQL, you should use UPPER, nor is Cint.

You have a few access specific casts there that are going to cause trouble for you.

im receiving a run-time error 91 in the set rs= db.recordset(sql) statement but i know it coming from previous dim db as dao.database and dim rs as dao.recordset. i dont know how to convert this vb code to work with sql. maybe dim rs as sql.recordset and dim db as sql.database . please forgive my ignorance but i am new to sql and i dont know the syntax for sql. by the way thank you for the advice with ucase and cint not working . i tried a previous suggestion and turned dim rs as ado.recordset but the only option i get is adobe.recordset am i missing a reference .|||You need to use the ms ado 2.x library reference. Using adodb.recordset and adodb.connection - however, in your case you only need adodb.recordset.|||Hey
Who one saying that ODBC Connections not possible by DAO?

[QUOTE][SIZE=1]Originally posted by opcbriley
here is the code i was using from a command button which basically used the first and last name fields to make a primary key called chart number. now it doesnt work and i belive it is in the dim rs as dao.recordset statement because this data now resides on an sql server. here is the code snippet.

Public Function chartlookup()
Dim wrkODBC As Workspace
Dim conPubs As Connection
Dim rs As DAO.Recordset <-- belive to be the problem
Dim SQL As String
Dim NewNum As Integer
Dim NeWChartNum As String

SQL = "Select max(Cint(Right([chartnumber],6))) As RecNum From tblpatientinfo WHERE UCase(Left([chartnumber],5)) = '" & UCase(Left([Forms]![fpatient]![lname], 3)) & UCase(Left([Forms]![fpatient]![fname], 2)) & "'"

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
Set conPubs = wrkODBC.OpenConnection("costing", dbDriverNoPrompt)
.........

You may also open connection by using this code
remaining code will remain same
plz use it n reply me
thx|||You can use odbc - but don't - you are much better off using oledb (or use tds in .net). Better performance, resource usage and flexibility/functionality.|||yeah i know that OLEDB performance much better that dao but person asked abt DAO libraray n no one was there to reply by DAO libraray so I hv ginven reply by DAO library|||i appreciate all your help i was able to switch to ado connection i finally learned the syntax last night after about hour of reading. that code is working perfect now.

now on to find all my other problems that i have to change to be compatible with sql. i sure hope sql is worth all this trouble.

Please Help for Function

Hi
I am looking for freequently using fuction like Find number of characters in a word.

Ex:
[code]
Declare @.Str='AXG00023'
If I use the function like getnumberofChars(@.Str) Then it has to give the result is 3.
Because rest of all numeric valus.

Is we have any function like this in SqlServer

please Help me

:confused:Yes,

You can use the DATALENGTH(), which will return the number of characters in a string.

If you are using that on a char datatype, you may want to use a Rtrim() on the field first before getting that datalenght.

ie. datalength(rtrim(@.stringname))

Scooter Mcfly|||DECLARE
@.Str varchar(50),
@.i int,
@.x int

SET @.Str ='AXG00X023'
SET @.i = 0
SET @.x=1

WHILE @.x <= DATALENGTH(@.Str) BEGIN
IF ISNUMERIC(SUBSTRING(@.Str,@.x,1)) = 0
SET @.i=@.i+1

SET @.x=@.x+1
END

print @.i

If you are using SQL Server 2000 you could put this code into your own user defined function.

Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
.. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana M
Hi Diana,
I do not think that someone here can give a specific solution to that problem.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your sp
from vb. also take a look at sysprocesses able and try and see if you have an
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:

> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it does
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running from
> query analyzer
> I can assure you that it is not permission problem, it is not query problem,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored procedues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>

Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana MHi Diana,
I do not think that someone here can give a specific solution to that proble
m.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your s
p
from vb. also take a look at sysprocesses able and try and see if you have a
n
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:

> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If
I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it doe
s
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running fr
om
> query analyzer
> I can assure you that it is not permission problem, it is not query proble
m,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored proce
dues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are calle
d
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>

Please help

Hello everybody,
I have stored procedure that calls number of other stored procedures. If I
run stored procedure from query analyzer it never fails.
However it fails from VB. And to be more specific, it does not fail, it
stops at certain point and exits (there is no error msg in sql Profiler)
I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
It goes to SP6 up to the curtain point and without error leaves and it does
not go at all to SP7 and leaves sp SP1 immediately.
It leaves SP6 every time in the same place, however if I add waitfor
(delay) somewhere on the top, it would leave stored procedure earlier
VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
command or connection timeout, both values =0
The same stored procedure never fails (with the same data) when running from
query analyzer
I can assure you that it is not permission problem, it is not query problem,
because with added delay, it would leave stored procedure earlier
I mean without delay it fails after 8th query, with delay
it can leave stored procedure after 3rd query (for instance).
. It reference mdac2.5, also I tried to
reference 2.7 with service pack 1 without any luck
This stored procedure has few delete statements. If I would replace delete
with truncate -> stored procedure would not fail(100% successful). But I
can't do it, because the client who is running sp is not admin
Also the stored procedure sp6, that fails is using tempdb. If I increase
tempdb log file size, the failure rate decreases to around 50%.
I have solved the problem temporary by splitting sp1 into 2 stored procedues
Now sp1 calls sp2,sp3,sp4,sp5
sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
from vb.
Please help
Any suggestions would be appreciated
thanks,
Diana MHi Diana,
I do not think that someone here can give a specific solution to that problem.
i think you should open profiler and performance monitor and try to find the
problem.
may be you have a dead lock there? or an open transaction and this is
explains the wait for delay helps it. i would have focuced on the filters of
text data at your trace adding event of sp:start and at the perfmon add
counters of cpu and try to see if there is a jump there when you call your sp
from vb. also take a look at sysprocesses able and try and see if you have an
spid in waiting for a while.
hope it helps,
tomer
"Diana M" wrote:
> Hello everybody,
> I have stored procedure that calls number of other stored procedures. If I
> run stored procedure from query analyzer it never fails.
> However it fails from VB. And to be more specific, it does not fail, it
> stops at certain point and exits (there is no error msg in sql Profiler)
> I have stored procedure SP1 and it calls SP2, SP3, SP4, SP5, SP6, SP7
> It goes to SP6 up to the curtain point and without error leaves and it does
> not go at all to SP7 and leaves sp SP1 immediately.
> It leaves SP6 every time in the same place, however if I add waitfor
> (delay) somewhere on the top, it would leave stored procedure earlier
> VB6.0 use ODBC connection to SQL server(MDAC2.5), I do not have adodb
> command or connection timeout, both values =0
> The same stored procedure never fails (with the same data) when running from
> query analyzer
> I can assure you that it is not permission problem, it is not query problem,
> because with added delay, it would leave stored procedure earlier
> I mean without delay it fails after 8th query, with delay
> it can leave stored procedure after 3rd query (for instance).
> . It reference mdac2.5, also I tried to
> reference 2.7 with service pack 1 without any luck
>
> This stored procedure has few delete statements. If I would replace delete
> with truncate -> stored procedure would not fail(100% successful). But I
> can't do it, because the client who is running sp is not admin
> Also the stored procedure sp6, that fails is using tempdb. If I increase
> tempdb log file size, the failure rate decreases to around 50%.
> I have solved the problem temporary by splitting sp1 into 2 stored procedues
> Now sp1 calls sp2,sp3,sp4,sp5
> sp1_newOne calls sp6, sp7. Just to note that SP1 and sp1_NewOne are called
> from vb.
> Please help
> Any suggestions would be appreciated
> thanks,
> Diana M
>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 - limit on # of databases - large number of databases - man

I hear the limit of databases is over 37,000 but all over
Microsofts site they dont recommend going over 1500 databases per artical
316749.
This artical is close to our problem and we will be testing the -g option
and then sp4 very soon but i wanted to post some of our log file and see if
anyone has other ideas. we have sql2000 sp3.
after the server is running for hours this error will appear:
2006-01-11 15:31:48.68 serverSQL Server could not spawn process_loginread
thread.
then another hour or so goes by and we get this:
2006-01-11 15:54:32.20 spid941WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-01-11 15:54:32.21 spid941Query Memory Manager: Grants=4 Waiting=0
Maximum=126079 Available=125843
2006-01-11 15:54:32.21 spid941Global Memory Objects: Resource=8522
Locks=145 ...
2006-01-11 15:54:32.21 spid941Dynamic Memory Manager: Stolen=84484 OS
Reserved=3760 ...
2006-01-11 15:54:32.21 spid941Procedure Cache: TotalProcs=31346
TotalPages=70067 InUsePages=26148
2006-01-11 15:54:32.21 spid941Buffer Counts: Commited=208680
Target=208680 Hashed=123893...
2006-01-11 15:54:32.21 spid941Buffer Distribution: Stolen=14421 Free=299
Procedures=70067...
2006-01-11 15:55:05.93 spid941Query Memory Manager: Grants=0 Waiting=0
Maximum=127118 Available=127118
eventualy after getting these errors in the log for a hour or so we are
forced to reboot because our applications start giving connection error
messages.
Thank you in advance for the help
Changing the -g startup option can increase the MemToLeave area of memory
allocation and potentially alleviate that problem. But you might want to
seriously think about moving to a 64 bit platform to better utilize memory
allocations. Also rethink why you have 1500 db's<g>.
Andrew J. Kelly SQL MVP
"out of office" <outofoffice@.discussions.microsoft.com> wrote in message
news:A657C55D-ED97-49D3-B185-575D60F401A8@.microsoft.com...
> I hear the limit of databases is over 37,000 but all over
> Microsofts site they dont recommend going over 1500 databases per artical
> 316749.
> This artical is close to our problem and we will be testing the -g option
> and then sp4 very soon but i wanted to post some of our log file and see
> if
> anyone has other ideas. we have sql2000 sp3.
> after the server is running for hours this error will appear:
> 2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
> thread.
> then another hour or so goes by and we get this:
> 2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
> Maximum=126079 Available=125843
> 2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
> Locks=145 ...
> 2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
> Reserved=3760 ...
> 2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
> TotalPages=70067 InUsePages=26148
> 2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
> Target=208680 Hashed=123893...
> 2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
> Procedures=70067...
> 2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
> Maximum=127118 Available=127118
> eventualy after getting these errors in the log for a hour or so we are
> forced to reboot because our applications start giving connection error
> messages.
> Thank you in advance for the help
>

please - limit on # of databases - large number of databases - man

I hear the limit of databases is over 37,000 but all over
Microsofts site they dont recommend going over 1500 databases per artical
316749.
This artical is close to our problem and we will be testing the -g option
and then sp4 very soon but i wanted to post some of our log file and see if
anyone has other ideas. we have sql2000 sp3.
after the server is running for hours this error will appear:
2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
thread.
then another hour or so goes by and we get this:
2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
Maximum=126079 Available=125843
2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
Locks=145 ...
2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
Reserved=3760 ...
2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
TotalPages=70067 InUsePages=26148
2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
Target=208680 Hashed=123893...
2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
Procedures=70067...
2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
Maximum=127118 Available=127118
eventualy after getting these errors in the log for a hour or so we are
forced to reboot because our applications start giving connection error
messages.
Thank you in advance for the helpChanging the -g startup option can increase the MemToLeave area of memory
allocation and potentially alleviate that problem. But you might want to
seriously think about moving to a 64 bit platform to better utilize memory
allocations. Also rethink why you have 1500 db's<g>.
Andrew J. Kelly SQL MVP
"out of office" <outofoffice@.discussions.microsoft.com> wrote in message
news:A657C55D-ED97-49D3-B185-575D60F401A8@.microsoft.com...
> I hear the limit of databases is over 37,000 but all over
> Microsofts site they dont recommend going over 1500 databases per artical
> 316749.
> This artical is close to our problem and we will be testing the -g option
> and then sp4 very soon but i wanted to post some of our log file and see
> if
> anyone has other ideas. we have sql2000 sp3.
> after the server is running for hours this error will appear:
> 2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
> thread.
> then another hour or so goes by and we get this:
> 2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
> Maximum=126079 Available=125843
> 2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
> Locks=145 ...
> 2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
> Reserved=3760 ...
> 2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
> TotalPages=70067 InUsePages=26148
> 2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
> Target=208680 Hashed=123893...
> 2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
> Procedures=70067...
> 2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
> Maximum=127118 Available=127118
> eventualy after getting these errors in the log for a hour or so we are
> forced to reboot because our applications start giving connection error
> messages.
> Thank you in advance for the help
>

please - limit on # of databases - large number of databases - man

I hear the limit of databases is over 37,000 but all over
Microsofts site they dont recommend going over 1500 databases per artical
316749.
This artical is close to our problem and we will be testing the -g option
and then sp4 very soon but i wanted to post some of our log file and see if
anyone has other ideas. we have sql2000 sp3.
after the server is running for hours this error will appear:
2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
thread.
then another hour or so goes by and we get this:
2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
memory of Size= 65536.
2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
Maximum=126079 Available=125843
2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
Locks=145 ...
2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
Reserved=3760 ...
2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
TotalPages=70067 InUsePages=26148
2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
Target=208680 Hashed=123893...
2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
Procedures=70067...
2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
Maximum=127118 Available=127118
eventualy after getting these errors in the log for a hour or so we are
forced to reboot because our applications start giving connection error
messages.
Thank you in advance for the helpChanging the -g startup option can increase the MemToLeave area of memory
allocation and potentially alleviate that problem. But you might want to
seriously think about moving to a 64 bit platform to better utilize memory
allocations. Also rethink why you have 1500 db's<g>.
--
Andrew J. Kelly SQL MVP
"out of office" <outofoffice@.discussions.microsoft.com> wrote in message
news:A657C55D-ED97-49D3-B185-575D60F401A8@.microsoft.com...
> I hear the limit of databases is over 37,000 but all over
> Microsofts site they dont recommend going over 1500 databases per artical
> 316749.
> This artical is close to our problem and we will be testing the -g option
> and then sp4 very soon but i wanted to post some of our log file and see
> if
> anyone has other ideas. we have sql2000 sp3.
> after the server is running for hours this error will appear:
> 2006-01-11 15:31:48.68 server SQL Server could not spawn process_loginread
> thread.
> then another hour or so goes by and we get this:
> 2006-01-11 15:54:32.20 spid941 WARNING: Failed to reserve contiguous
> memory of Size= 65536.
> 2006-01-11 15:54:32.21 spid941 Query Memory Manager: Grants=4 Waiting=0
> Maximum=126079 Available=125843
> 2006-01-11 15:54:32.21 spid941 Global Memory Objects: Resource=8522
> Locks=145 ...
> 2006-01-11 15:54:32.21 spid941 Dynamic Memory Manager: Stolen=84484 OS
> Reserved=3760 ...
> 2006-01-11 15:54:32.21 spid941 Procedure Cache: TotalProcs=31346
> TotalPages=70067 InUsePages=26148
> 2006-01-11 15:54:32.21 spid941 Buffer Counts: Commited=208680
> Target=208680 Hashed=123893...
> 2006-01-11 15:54:32.21 spid941 Buffer Distribution: Stolen=14421 Free=299
> Procedures=70067...
> 2006-01-11 15:55:05.93 spid941 Query Memory Manager: Grants=0 Waiting=0
> Maximum=127118 Available=127118
> eventualy after getting these errors in the log for a hour or so we are
> forced to reboot because our applications start giving connection error
> messages.
> Thank you in advance for the help
>sql

PIVOT TABLE query !! @SNMSDN

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

fetch next from emp_cur into @.empid
while @.@.fetch_status = 0
begin
set @.sql1 = @.sql1 + ',' + cast(empid as varchar(10))
set @.sql2 = @.sql3 + cast(empid as varchar(10)) + ','
fetch next from emp_cur into @.empid
end

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

DECLARE @.pivotColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.sql = 'SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

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

|||

thanks a lot buddy...actually i have a bit more tweek in my problem....i need the resultant recordset in a temp table.... as i dont know many columns will be in it , select into has to be used..now when i use it in buliding my query string and then execute it (@.sql) , later select * from temp , it ives an error... invalid object name '#temp' ...

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

You could create a global temporary table (with two ## at the beginning of the name). The problem is that then the temp table will be available to all connections, so if it is possible that this code will ever run on two connections at the same time that won't work. So now you have to get tricky, you create the #temp table first (with the known VendorID column, but none of the other columns, because they are not known). Then you alter the table in the dynamic code before you insert.

DROP TABLE #temp
CREATE TABLE #temp(VendorID int)
DECLARE @.pivotColumns nvarchar(2000), @.alterColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SET @.alterColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],',
@.alterColumns = @.alterColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '] int,'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.alterColumns = LEFT(@.alterColumns, LEN(@.alterColumns) - 1)
SET @.sql = 'ALTER TABLE #temp
ADD ' + @.alterColumns
EXEC(@.sql)
SET @.sql = 'INSERT #temp
SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..

PIVOT TABLE query !!

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

fetch next from emp_cur into @.empid
while @.@.fetch_status = 0
begin
set @.sql1 = @.sql1 + ',' + cast(empid as varchar(10))
set @.sql2 = @.sql3 + cast(empid as varchar(10)) + ','
fetch next from emp_cur into @.empid
end

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

DECLARE @.pivotColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.sql = 'SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

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

|||

thanks a lot buddy...actually i have a bit more tweek in my problem....i need the resultant recordset in a temp table.... as i dont know many columns will be in it , select into has to be used..now when i use it in buliding my query string and then execute it (@.sql) , later select * from temp , it ives an error... invalid object name '#temp' ...

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

You could create a global temporary table (with two ## at the beginning of the name). The problem is that then the temp table will be available to all connections, so if it is possible that this code will ever run on two connections at the same time that won't work. So now you have to get tricky, you create the #temp table first (with the known VendorID column, but none of the other columns, because they are not known). Then you alter the table in the dynamic code before you insert.

DROP TABLE #temp
CREATE TABLE #temp(VendorID int)
DECLARE @.pivotColumns nvarchar(2000), @.alterColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SET @.alterColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],',
@.alterColumns = @.alterColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '] int,'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.alterColumns = LEFT(@.alterColumns, LEN(@.alterColumns) - 1)
SET @.sql = 'ALTER TABLE #temp
ADD ' + @.alterColumns
EXEC(@.sql)
SET @.sql = 'INSERT #temp
SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..

PIVOT TABLE query !!

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

fetch next from emp_cur into @.empid
while @.@.fetch_status = 0
begin
set @.sql1 = @.sql1 + ',' + cast(empid as varchar(10))
set @.sql2 = @.sql3 + cast(empid as varchar(10)) + ','
fetch next from emp_cur into @.empid
end

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

DECLARE @.pivotColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.sql = 'SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

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

|||

thanks a lot buddy...actually i have a bit more tweek in my problem....i need the resultant recordset in a temp table.... as i dont know many columns will be in it , select into has to be used..now when i use it in buliding my query string and then execute it (@.sql) , later select * from temp , it ives an error... invalid object name '#temp' ...

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

You could create a global temporary table (with two ## at the beginning of the name). The problem is that then the temp table will be available to all connections, so if it is possible that this code will ever run on two connections at the same time that won't work. So now you have to get tricky, you create the #temp table first (with the known VendorID column, but none of the other columns, because they are not known). Then you alter the table in the dynamic code before you insert.

DROP TABLE #temp
CREATE TABLE #temp(VendorID int)
DECLARE @.pivotColumns nvarchar(2000), @.alterColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SET @.alterColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],',
@.alterColumns = @.alterColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '] int,'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.alterColumns = LEFT(@.alterColumns, LEN(@.alterColumns) - 1)
SET @.sql = 'ALTER TABLE #temp
ADD ' + @.alterColumns
EXEC(@.sql)
SET @.sql = 'INSERT #temp
SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..

pivot table

I'm trying to extract some data from an ssas 2005 cube to build a report with ssrs 2005.

The report should have a variable number of columns and a fixed number of rows ... so I think I cannot use a table control but I must use a matrix control ...

So I would group the column for the fiscal month and the row for the measure name or measure caption ... and put the measure value inside the matrix.

Like the following

month 1 month 2 measure 1 xxx xxx measure 2 xxx xxx measure 3 xxx xxx

To do that I should run a query to extract data in the following form ...

fiscal month mesaure name measure value month 1 measure 1 xxx month 1 measure 2 xxx month 1 measure 3 xxx month 2 measure 1 xxx month 2 measure 2 xxx month 2 measure 3 xxx

The problem is ... when running an mdx query on reporting services I need to put the meausure only on the columns ...

so any idea on how can I extract data from ssas in that form ?

Cosimo

Can you place an upper bound on the number of months you need to report? Also, how do you want your months to display from left to right -- most recent to least recent?|||

thank

I solved ... using a static group and a matrix control

Cosimo

PIVOT operator for variable number of transformations

Hi, i'm trying to port a pivot query from access to sqlserver.
I'm trying this query:

SELECT IDMerce, [1] AS [Department-1], [2] AS [Department-2], [3] AS
[Department-3], [4] AS [Department-4]
FROM (SELECT IDMerce, Pezzi, IDMagazzino
FROM Disponibilita) p PIVOT (sum(Pezzi) FOR
IDMagazzino IN ([1], [2], [3], [4])) AS pvt

this works, but in my case i don't know in advance how many transformations
i need, so there is a solution?
Thanks2005 has support for pivot or "crosstab" queries, though SQL Server 2000 did not. In my opinion, they should have left it that way. Most application interfaces and reporting tools depend upon knowning in advance the layout of the recordsets they are going to receive, and certainly any sql views or procedures must be able to depend on getting consistent recordsets from the objects they call. Dynamic pivots and crosstabs by definition have variable record layouts.
Pivoting the data is arguable a matter of presentation, not data storage or retrieval or business rules. For this reason, you should pull your recordset as a flatfile and let your application or reporting engine handle the pivoting. Most reporting applications (Crystal, Access, Active Reports...) can easily create dynamic crosstabs from datasets, as this is designed as part of their functionality.

PIVOT is not case-sensitive

Hi there,

I am using a PIVOT to count the number of chunk for each block type:
ex.:
block_type, chunk
a, <data>
a, <data>
b, <data> ...

My problem is that the block_type is case-sensitive, 'a' should not be counted as a 'A'.
How can I take the case in consideration?

I've tried to plug a COLLATE SQL_Latin1_General_CP1_CS_AS statement but it doesn't seem to be supported... Something like:
SELECT *
FROM recv.test_Blocks
PIVOT (
COUNT(chunk)
FOR block_type COLLATE SQL_Latin1_General_CP1_CS_AS
IN ([9.], a, B, h, q)
) AS pvt

Also something like:
IN (a, A)
returns an error: The column 'A' was specified multiple times for 'pvt'.

Thanks

Collation of identifiers depends on the level at which there are defined. For tables and column names, the collation of the database is used. So if you are trying this on a database with case-insensitive collation it is expected behavior. You need to change the collation of the database to have column names that differ only by case. See Books Online topic on "Identifier Collation" for more details.