sql error 15138: bit

Showing posts with label bit. Show all posts

Tuesday, March 27, 2012

Error 17883

The SQL Server is 32 bit - 2000 and I have applied SP3a with hot patch 815495. So it is up to version 818 but I am still getting errors sporadically which are the Process 53:0 (19f8) UMS Context 0x0D2123E8 appears to be non-yielding on Scheduler 0. They
are server errors and appear to be happening in the early morning and trying to search the microsoft web site, indicates that these should be corrected.
Are there additional hot patches to correct this problem? Thanks.
Cynthia,
Here is an article which seems to outline your problem.
http://support.microsoft.com/default...b;en-us;810885
The circumstances for this are fairly specific and there is no hot-patch
according to the article. Do you fit the profile of this article?
Russell Fields
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:803D34D5-99BD-408F-B767-A29FE8FC112C@.microsoft.com...
> The SQL Server is 32 bit - 2000 and I have applied SP3a with hot patch
815495. So it is up to version 818 but I am still getting errors
sporadically which are the Process 53:0 (19f8) UMS Context 0x0D2123E8
appears to be non-yielding on Scheduler 0. They are server errors and
appear to be happening in the early morning and trying to search the
microsoft web site, indicates that these should be corrected.
> Are there additional hot patches to correct this problem? Thanks.
>
|||Hi,
Thanks for the reply. Yes I do have autogrow on all the servers and this one that is giving the error. But there is nothing exceptional about this server compared to the other servers. No high speed disk system or anything unusual. This is the only s
erver that has this error and the error 17883 and Q815056 seems to identify what the problem is but the hotfix 815495 didn't resolve the problem. I am wondering if the error is informational only?
|||Cynthia,
Although this error is "informational" in that there is no direct action
that you can take, I would view the message as the harbinger of problems
that may hurt you more in the future.
Inside 810885 there is a reference to:
http://support.microsoft.com/default.aspx?kbid=319892
That makes the following comment: If you cannot determine the root cause
immediately, consult the error log for problems and engage in extended
support efforts.
Sounds like Microsoft feels that you should be concerned.
Russell Fields
"cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:BA5E143F-A0EE-486B-97D7-756D71893984@.microsoft.com...
> Hi,
> Thanks for the reply. Yes I do have autogrow on all the servers and this
one that is giving the error. But there is nothing exceptional about this
server compared to the other servers. No high speed disk system or anything
unusual. This is the only server that has this error and the error 17883
and Q815056 seems to identify what the problem is but the hotfix 815495
didn't resolve the problem. I am wondering if the error is informational
only?
|||In the absence of a specific bug, this is likely going to be best-handled and troubleshot by Microsoft support. I'd recommend that you engage them as soon as possible.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message news:exv7IzyTEHA.2464@.TK2MSFTNGP10.phx.gbl...
Cynthia,
Although this error is "informational" in that there is no direct action
that you can take, I would view the message as the harbinger of problems
that may hurt you more in the future.
Inside 810885 there is a reference to:
http://support.microsoft.com/default.aspx?kbid=319892
That makes the following comment: If you cannot determine the root cause
immediately, consult the error log for problems and engage in extended
support efforts.
Sounds like Microsoft feels that you should be concerned.
Russell Fields
"cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:BA5E143F-A0EE-486B-97D7-756D71893984@.microsoft.com...
> Hi,
> Thanks for the reply. Yes I do have autogrow on all the servers and this
one that is giving the error. But there is nothing exceptional about this
server compared to the other servers. No high speed disk system or anything
unusual. This is the only server that has this error and the error 17883
and Q815056 seems to identify what the problem is but the hotfix 815495
didn't resolve the problem. I am wondering if the error is informational
only?
|||Thanks Ryan and Russell. You're both right and I have run out of troubleshooting tools for this.

Monday, March 26, 2012

Error 17883

The SQL Server is 32 bit - 2000 and I have applied SP3a with hot patch 81549
5. So it is up to version 818 but I am still getting errors sporadically wh
ich are the Process 53:0 (19f8) UMS Context 0x0D2123E8 appears to be non-yie
lding on Scheduler 0. They
are server errors and appear to be happening in the early morning and trying
to search the microsoft web site, indicates that these should be corrected.
Are there additional hot patches to correct this problem? Thanks.Cynthia,
Here is an article which seems to outline your problem.
http://support.microsoft.com/defaul...kb;en-us;810885
The circumstances for this are fairly specific and there is no hot-patch
according to the article. Do you fit the profile of this article?
Russell Fields
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:803D34D5-99BD-408F-B767-A29FE8FC112C@.microsoft.com...
> The SQL Server is 32 bit - 2000 and I have applied SP3a with hot patch
815495. So it is up to version 818 but I am still getting errors
sporadically which are the Process 53:0 (19f8) UMS Context 0x0D2123E8
appears to be non-yielding on Scheduler 0. They are server errors and
appear to be happening in the early morning and trying to search the
microsoft web site, indicates that these should be corrected.
> Are there additional hot patches to correct this problem? Thanks.
>|||Hi,
Thanks for the reply. Yes I do have autogrow on all the servers and this o
ne that is giving the error. But there is nothing exceptional about this se
rver compared to the other servers. No high speed disk system or anything u
nusual. This is the only s
erver that has this error and the error 17883 and Q815056 seems to identify
what the problem is but the hotfix 815495 didn't resolve the problem. I am
wondering if the error is informational only'|||Cynthia,
Although this error is "informational" in that there is no direct action
that you can take, I would view the message as the harbinger of problems
that may hurt you more in the future.
Inside 810885 there is a reference to:
http://support.microsoft.com/default.aspx?kbid=319892
That makes the following comment: If you cannot determine the root cause
immediately, consult the error log for problems and engage in extended
support efforts.
Sounds like Microsoft feels that you should be concerned.
Russell Fields
"cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:BA5E143F-A0EE-486B-97D7-756D71893984@.microsoft.com...
> Hi,
> Thanks for the reply. Yes I do have autogrow on all the servers and this
one that is giving the error. But there is nothing exceptional about this
server compared to the other servers. No high speed disk system or anything
unusual. This is the only server that has this error and the error 17883
and Q815056 seems to identify what the problem is but the hotfix 815495
didn't resolve the problem. I am wondering if the error is informational
only'|||In the absence of a specific bug, this is likely going to be best-handled an
d troubleshot by Microsoft support. I'd recommend that you engage them as s
oon as possible.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message news:exv7
IzyTEHA.2464@.TK2MSFTNGP10.phx.gbl...
Cynthia,
Although this error is "informational" in that there is no direct action
that you can take, I would view the message as the harbinger of problems
that may hurt you more in the future.
Inside 810885 there is a reference to:
http://support.microsoft.com/default.aspx?kbid=319892
That makes the following comment: If you cannot determine the root cause
immediately, consult the error log for problems and engage in extended
support efforts.
Sounds like Microsoft feels that you should be concerned.
Russell Fields
"cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:BA5E143F-A0EE-486B-97D7-756D71893984@.microsoft.com...
> Hi,
> Thanks for the reply. Yes I do have autogrow on all the servers and this
one that is giving the error. But there is nothing exceptional about this
server compared to the other servers. No high speed disk system or anything
unusual. This is the only server that has this error and the error 17883
and Q815056 seems to identify what the problem is but the hotfix 815495
didn't resolve the problem. I am wondering if the error is informational
only'|||Thanks Ryan and Russell. You're both right and I have run out of troubleshoo
ting tools for this.sql

Error 17883

The SQL Server is 32 bit - 2000 and I have applied SP3a with hot patch 815495. So it is up to version 818 but I am still getting errors sporadically which are the Process 53:0 (19f8) UMS Context 0x0D2123E8 appears to be non-yielding on Scheduler 0. They are server errors and appear to be happening in the early morning and trying to search the microsoft web site, indicates that these should be corrected.
Are there additional hot patches to correct this problem? Thanks.Cynthia,
Here is an article which seems to outline your problem.
http://support.microsoft.com/default.aspx?scid=kb;en-us;810885
The circumstances for this are fairly specific and there is no hot-patch
according to the article. Do you fit the profile of this article?
Russell Fields
"Cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:803D34D5-99BD-408F-B767-A29FE8FC112C@.microsoft.com...
> The SQL Server is 32 bit - 2000 and I have applied SP3a with hot patch
815495. So it is up to version 818 but I am still getting errors
sporadically which are the Process 53:0 (19f8) UMS Context 0x0D2123E8
appears to be non-yielding on Scheduler 0. They are server errors and
appear to be happening in the early morning and trying to search the
microsoft web site, indicates that these should be corrected.
> Are there additional hot patches to correct this problem? Thanks.
>|||Hi
Thanks for the reply. Yes I do have autogrow on all the servers and this one that is giving the error. But there is nothing exceptional about this server compared to the other servers. No high speed disk system or anything unusual. This is the only server that has this error and the error 17883 and Q815056 seems to identify what the problem is but the hotfix 815495 didn't resolve the problem. I am wondering if the error is informational only'|||Cynthia,
Although this error is "informational" in that there is no direct action
that you can take, I would view the message as the harbinger of problems
that may hurt you more in the future.
Inside 810885 there is a reference to:
http://support.microsoft.com/default.aspx?kbid=319892
That makes the following comment: If you cannot determine the root cause
immediately, consult the error log for problems and engage in extended
support efforts.
Sounds like Microsoft feels that you should be concerned.
Russell Fields
"cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:BA5E143F-A0EE-486B-97D7-756D71893984@.microsoft.com...
> Hi,
> Thanks for the reply. Yes I do have autogrow on all the servers and this
one that is giving the error. But there is nothing exceptional about this
server compared to the other servers. No high speed disk system or anything
unusual. This is the only server that has this error and the error 17883
and Q815056 seems to identify what the problem is but the hotfix 815495
didn't resolve the problem. I am wondering if the error is informational
only'|||This is a multi-part message in MIME format.
--=_NextPart_000_001C_01C44EFA.1D839650
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
In the absence of a specific bug, this is likely going to be =best-handled and troubleshot by Microsoft support. I'd recommend that =you engage them as soon as possible.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message =news:exv7IzyTEHA.2464@.TK2MSFTNGP10.phx.gbl...
Cynthia,
Although this error is "informational" in that there is no direct =action
that you can take, I would view the message as the harbinger of =problems
that may hurt you more in the future.
Inside 810885 there is a reference to:
http://support.microsoft.com/default.aspx?kbid=3D319892
That makes the following comment: If you cannot determine the root =cause
immediately, consult the error log for problems and engage in extended
support efforts.
Sounds like Microsoft feels that you should be concerned.
Russell Fields
"cynthia" <anonymous@.discussions.microsoft.com> wrote in message
news:BA5E143F-A0EE-486B-97D7-756D71893984@.microsoft.com...
> Hi,
>
> Thanks for the reply. Yes I do have autogrow on all the servers =and this
one that is giving the error. But there is nothing exceptional about =this
server compared to the other servers. No high speed disk system or =anything
unusual. This is the only server that has this error and the error =17883
and Q815056 seems to identify what the problem is but the hotfix =815495
didn't resolve the problem. I am wondering if the error is =informational
only'
--=_NextPart_000_001C_01C44EFA.1D839650
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

In the absence of a specific bug, this is likely =going to be best-handled and troubleshot by Microsoft support. I'd recommend =that you engage them as soon as possible.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine.
"Russell Fields" wrote in message news:exv7IzyTEHA.2464=@.TK2MSFTNGP10.phx.gbl...Cynthia,Although this error is "informational" in that there is no direct =actionthat you can take, I would view the message as the harbinger of =problemsthat may hurt you more in the future.Inside 810885 there is a reference = to:http://support.microsoft.com/default.aspx?kbid=3D319892">http://s=upport.microsoft.com/default.aspx?kbid=3D319892That makes the following comment: If you cannot determine the root causeimmediately, consult the error log for problems and engage in = extendedsupport efforts.Sounds like Microsoft feels that =you should be concerned.Russell Fields"cynthia" wrote in messagenews:BA5=E143F-A0EE-486B-97D7-756D71893984@.microsoft.com...> Hi,>> Thanks for the reply. Yes I do have =autogrow on all the servers and thisone that is giving the error. But =there is nothing exceptional about thisserver compared to the other servers. No high speed disk system or anythingunusual. =This is the only server that has this error and the error 17883and Q815056 =seems to identify what the problem is but the hotfix 815495didn't =resolve the problem. I am wondering if the error is informationalonly'

--=_NextPart_000_001C_01C44EFA.1D839650--|||Thanks Ryan and Russell. You're both right and I have run out of troubleshooting tools for this.

Thursday, March 22, 2012

Error 15185: There is no remote user 'sa' mapped to local user '(null)' from the remote serv

I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.

I can create a linked server using the option "Be made using the login's current security context" but can not when trying to specify the security context, i.e. sa and the sa password. When I try I get the following message:

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.

I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.

The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:

Executed as user: NT AUTHORITY\SYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.

I'm sure the two errors are related. Any ideas what is going on?

What is your context on the server when you get the first error message? How do your sp_addlinkedserver and sp_addlinkedsrvlogin commands look like?

For the second error message, the jobs probably execute with the SQL Agent service account credentials, which is LocalSystem and is unmapped. You should check the SQL Server Tools General forum for more information about SQL Agent.

Thanks
Laurentiu

|||I am also facing the same problem for linked servers. My server is in cluster environment.
i have both servers in the same domain however when i try to link it show the above mentioned error. when i try to link from ne other server i m able to do but not from the clustered one.
please reply asap.
|||

Iam trying to create a linked server(which is 32 bit SQL Server 2000) on 64 bit SLQ Server 2005 and iam receiving the following error.

Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91

An error occurred during decryption.

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98

There is no remote user 'user1' mapped to local user '(null)' from the remote server 'SERVER1'.

Kerry- were you able to fix the error that you received. Anyone who are aware of this, please help me.

Thanks

|||Sorry, no we were never able to completely solve this. We have created linked servers using windows integrated security and as long as the user exists on both machines it does allow some limited access.sql

Error 15185: There is no remote user 'sa' mapped to local user '(null)' from the remote serv

I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.

I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.

The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:

Executed as user: NT AUTHORITY\SYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.

I'm sure the two errors are related. Any ideas what is going on?

What is your context on the server when you get the first error message? How do your sp_addlinkedserver and sp_addlinkedsrvlogin commands look like?

Thanks
Laurentiu

Iam trying to create a linked server(which is 32 bit SQL Server 2000) on 64 bit SLQ Server 2005 and iam receiving the following error.

Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91

An error occurred during decryption.

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98

There is no remote user 'user1' mapped to local user '(null)' from the remote server 'SERVER1'.

Kerry- were you able to fix the error that you received. Anyone who are aware of this, please help me.

Thanks

Error 15185: There is no remote user 'sa' mapped to local user '(null)' from the remote

I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.

I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.

The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:

Executed as user: NT AUTHORITY\SYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.

I'm sure the two errors are related. Any ideas what is going on?

What is your context on the server when you get the first error message? How do your sp_addlinkedserver and sp_addlinkedsrvlogin commands look like?

Thanks
Laurentiu

Iam trying to create a linked server(which is 32 bit SQL Server 2000) on 64 bit SLQ Server 2005 and iam receiving the following error.

Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91

An error occurred during decryption.

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98

There is no remote user 'user1' mapped to local user '(null)' from the remote server 'SERVER1'.

Kerry- were you able to fix the error that you received. Anyone who are aware of this, please help me.

Thanks

Error 15185: There is no remote user 'sa' mapped to local user '(null)' from the remote

I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.

I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.

The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:

Executed as user: NT AUTHORITY\SYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.

I'm sure the two errors are related. Any ideas what is going on?

What is your context on the server when you get the first error message? How do your sp_addlinkedserver and sp_addlinkedsrvlogin commands look like?

Thanks
Laurentiu

Iam trying to create a linked server(which is 32 bit SQL Server 2000) on 64 bit SLQ Server 2005 and iam receiving the following error.

Msg 15466, Level 16, State 2, Procedure sp_addlinkedsrvlogin, Line 91

An error occurred during decryption.

Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98

There is no remote user 'user1' mapped to local user '(null)' from the remote server 'SERVER1'.

Kerry- were you able to fix the error that you received. Anyone who are aware of this, please help me.

Thanks

Wednesday, March 21, 2012

Error 1429: A server cursor cannot be opened...

Using SQL native client from VFP 9.0 to SQL Server 2005 64 bit SP1 (happened before SP1 too)..

We have a stored procedure that returns 6 result sets. This SP uses 2 cursors. It is rather lengthy - I'll post the code if needed.

This SP works fine when called from VFP 99 percent of the time. Normally takes 2 to 3 secunds to execute.

Once in a while we will get a return from SQL ..

"OLE IDispatch exception code 0 from Microsoft SQL Native Client: A server cursor cannot be opened on the given statement or statements. Use a default result set or client cursor..."

The OLE error code is 1429. An OLE Exception code 3604 is also returned.

When this happens the SP will return the same error when executed for the same parameters over and over when called from VFP. When called directly from SQL management console it will normally work for the same parameters, although once in a while it will just hang (and not timeout apparently). In that case it will also hang from SQLCMD command line utility as well.

Wait a few hours and the SP will run fine for the same parameters in VFP. This happens even in the middle of the night when there is no possibility that data is being changed.

Here's the really fun part...

Open the SP source for modification (ALTER PROCEDURE) in management console and execute it (no changes at all, just let it recompile). Immediately it will work fine when called with the same parameters called from VFP or anywhere else (even if it was one of the rare instances where it hung in management console). This works EVERY TIME.

Sooo... I edited and executed the SP with the WITH RECOMPILE option assuming that that should do the trick (same as alter procedure/executing from management console right?). NOPE. Same problems. In order to work around the problem when the error occurs, I HAVE TO alter procedure and execute the code from management console.

Help?

Bill Kuhn - MCSE

The Kuhn Group, Inc.

http://www.kuhngroup.com

Did you deallocate your cursors in your code ? Would be nice to see the skeleton code, not the whole one, but the pure cursor code you implemented.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Yes - both cursors are closed and deallocated

Following is the entire stored procedure. The cursors are curs_Exams_pkeys and curs_Raw_CompData ..

ALTER procedure [dbo].[Comp_Data_for_Person]

@.persons_pkey int,

@.Latest_Date datetime

with recompile

SET NOCOUNT ON

declare @.ExamCount smallint

declare @.TopExam_pkey int

declare @.Exam1pkey int, @.Exam2pkey int, @.Exam3pkey int, @.Exam4pkey int, @.Exam5pkey int, @.Exam6pkey int, @.This_Exam_pkey int

declare @.ExamIndex tinyint

declare @.CurrentClients_pkey smallint

declare @.CurrentPanel smallint

-- following vars are used when FETCHing data from curs_RAW_CompData

declare @.Session_date datetime, @.TestNumber int, @.DataType tinyint, @.Result varchar(65),@.Description varchar(200), @.Class varchar(100), @.PrintFlag char(1), @.Formatted varchar(200),@.NewFlag char(1),@.CheckText varchar(200),@.PanelTestGroup varchar(200),@.PrintLevel int, @.Exams_pkey int

declare @.Current_TestNumber int, @.Current_Session_date datetime

if @.Latest_Date is null

set @.Latest_Date = '12/31/2099'

set transaction isolation level read uncommitted -- added by wsk 8/14/2006 to see if this helps

-- make table of exams that will print on this report

create table #ExamTable (session_date datetime,van smallint,name varchar(60),exams_pkey int,persons_pkey int, clients_pkey int,Questionnaire_Title varchar(254),panel smallint)

set @.ExamCount =

(select count(*) from exams join testsession on exams.testsession_pkey=testsession.pkey where persons_pkey=@.persons_pkey and session_date<=@.Latest_Date)

if @.ExamCount <7

insert into #ExamTable

select testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

(select top 1 isnull(description,'') from translationcodes where testnumber=1000000000 and value=(select top 1 isnull(cast(result as int),0) from documentdata where exams_pkey=exams.pkey and testnumber=1000000000 and datatype=11)) as Questionnaire_Title, exams.panel

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date desc

else

insert into #ExamTable

select * from (select top 5 testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date desc) as d1

union

select * from (select top 1 testsession.session_date,testsession.van,dbo.fullnamenormalatexamdate(exams.pkey) as name,exams.pkey,exams.persons_pkey,exams.clients_pkey,

from exams join testsession on exams.testsession_pkey=testsession.pkey

where exams.persons_pkey = @.persons_pkey and session_date <= @.Latest_Date

order by testsession.session_date) as d2

order by session_date desc

-- output list of exams in this data

select * from #ExamTable

--@.TopExam_pkey is key to latest exam for this person - this exam governs what testnumbers are printed in compdata

set @.TopExam_pkey = (select top 1 exams_pkey from #ExamTable order by session_date desc)

set @.CurrentClients_pkey = (select top 1 clients_pkey from #ExamTable order by session_date desc)

set @.CurrentPanel = (select top 1 panel from #ExamTable order by session_date desc)

-- get session_dates for all exams that will be reported on

set @.ExamIndex = 1

declare curs_Exams_pkeys cursor STATIC for select exams_pkey from #ExamTable order by session_date

open curs_Exams_pkeys

fetch next from curs_Exams_pkeys into @.This_Exam_pkey

while @.@.FETCH_STATUS = 0

begin

if @.ExamIndex = 1

set @.Exam1pkey = @.This_Exam_pkey

else if @.ExamIndex = 2

set @.Exam2pkey = @.This_Exam_pkey

else if @.ExamIndex = 3

set @.Exam3pkey = @.This_Exam_pkey

else if @.ExamIndex = 4

set @.Exam4pkey = @.This_Exam_pkey

else if @.ExamIndex = 5

set @.Exam5pkey = @.This_Exam_pkey

else if @.ExamIndex = 6

set @.Exam6pkey = @.This_Exam_pkey

set @.ExamIndex = @.ExamIndex + 1

fetch next from curs_Exams_pkeys into @.This_Exam_pkey

end

close curs_Exams_pkeys

deallocate curs_Exams_pkeys

-- output report header info

select dbo.fullnamenormalatexamdate(ex.pkey) as completename,dbo.ssnatexamdate(ex.pkey) as ssn,dbo.justdate(ts.session_date) as testdate,ts.van,ex.pid,ex.panel,

dbo.addressatexamdate(ex.pkey) as address,dbo.citystatezipatexamdate(ex.pkey) as citystatezip,adm.telephone,dbo.justdate(p.dob) as DOB, left(isnull(adm.employment,' '),2) as employmentyears,substring(isnull(adm.employment,' '),3,2) as employmentmonths,

isnull(cladm.payrollnum,'') as payrollnum,isnull(cladm.payrollnumber,'') as payrollnumber,isnull(cladm.jobcode,'') as jobcode,cl.client,cl.clientname,

clloc.location,clloc.description as locationdescription,dbo.genderatexamdate(ex.pkey) as gender,dbo.ageatexamdate(ex.pkey) as age,

isnull(cladm.memberssn,'') as memberssn, isnull(cladm.employeeid,'') as employeeid,

dbo.lastnameatexamdate(ex.pkey) as lastname,

dbo.firstnameatexamdate(ex.pkey) as firstname,

dbo.middlenameatexamdate(ex.pkey) as middlename

from exams ex join testsession ts on ex.testsession_pkey=ts.pkey

join clients cl on cl.pkey=ex.clients_pkey

join clientlocations clloc on ts.clientlocations_pkey=clloc.pkey

join clientadmin cladm on cladm.exams_pkey=ex.pkey

join administrative adm on adm.exams_pkey = ex.pkey

join persons p on ex.persons_pkey=p.pkey

where ex.pkey=@.TopExam_Pkey

create table #ReportTests (testnumber int)

insert into #ReportTests (testnumber)

select distinct medicaldata.testnumber from medicaldata where medicaldata.exams_pkey=@.TopExam_pkey and dbo.get_printflag(medicaldata.testnumber,medicaldata.datatype,medicaldata.result) in ('P','S')

union

select distinct labdata.testnumber from labdata where labdata.exams_pkey=@.TopExam_pkey and dbo.get_printflag(labdata.testnumber,labdata.datatype,labdata.result) in ('P','S')

create table #Final_CompData (TestNumber int, DataType tinyint, Description varchar(200), Class varchar(100), PrintFlag char(1), NewFlag char(1), CheckText varchar(200), PanelTestGroup varchar(200), PrintLevel int, Exam1Result varchar(2000),Exam2Result varchar(2000), Exam3Result varchar(2000), Exam4Result varchar(2000), Exam5Result varchar(2000), Exam6Result varchar(2000))

create index [testnumber_datatype] on #Final_CompData (testnumber,datatype)

declare curs_Raw_CompData cursor STATIC FOR

-- output medical compdata

select ts.session_date,

d1.testnumber,d1.datatype,d1.result,

dbo.testdescription(testnumber) as description,d1.class,dbo.get_printflag(d1.testnumber,d1.datatype,d1.result) as printflag,

rtrim(dbo.formatresult_new(testnumber,datatype,result))+' '+rtrim(dbo.testunits_by_datatype(testnumber,datatype)) as formatted,

d1.checkflag as newflag, d1.checktext,

(select top 1 ptg.testgroup from paneltestgroups ptg join clientpanels cp on ptg.clientpanels_pkey=cp.pkey

join testgroups tg on ptg.testgroup = tg.testgroup_descr

where tg.testnumber=d1.testnumber and cp.clients_pkey=@.CurrentClients_pkey and cp.panel=@.CurrentPanel) as paneltestgroup,

d1.printlevel,d1.exams_pkey

from

((select 'MEDICALDATA' as datatable,m.pkey as datatable_pkey,exams_pkey,m.testnumber,m.datatype,result,

dbo.referencerangecheck(e.pkey,m.testnumber,m.datatype,m.result) as checkflag,

dbo.referencerangetext(e.pkey,m.testnumber,m.datatype,m.result) as checktext,m.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join medicaldata m on m.exams_pkey=e.pkey

join tests on m.testnumber=tests.testnumber and m.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber in (select testnumber from #ReportTests)

)

union

(select 'LABDATA' as datatable,l.pkey as datatable_pkey,exams_pkey,l.testnumber,l.datatype,result,

dbo.referencerangecheck(e.pkey,l.testnumber,l.datatype,l.result) as checkflag,

dbo.referencerangetext(e.pkey,l.testnumber,l.datatype,l.result) as checktext,l.flag as oldflag,

e.testsession_pkey,e.clients_pkey,e.panel,tests.printlevel,tests.class,tests.printflag

from exams e join testsession t on e.testsession_pkey=t.pkey

join labdata l on l.exams_pkey=e.pkey

join tests on l.testnumber=tests.testnumber and l.datatype=tests.datatype

where e.pkey in (select exams_pkey from #ExamTable) and tests.testnumber in (select testnumber from #ReportTests)

))

as d1

join testsession ts on ts.pkey=d1.testsession_pkey

order by printlevel,testnumber,session_date -- session_date,van,pid,name,testnumber

open curs_Raw_CompData

fetch next from curs_Raw_CompData into @.Session_date, @.TestNumber, @.DataType, @.Result, @.Description , @.Class, @.PrintFlag, @.Formatted, @.NewFlag,@.CheckText, @.PanelTestGroup, @.PrintLevel, @.Exams_pkey

while @.@.FETCH_STATUS = 0

BEGIN

if @.Exams_pkey = @.TopExam_pkey -- use class,description,checktext,printflag,etc from this one

begin

if exists (select testnumber from #Final_CompData where testnumber=@.TestNumber and datatype = @.Datatype)

begin

update #Final_CompData

set Description = @.Description,Class = @.Class,PrintFlag = @.PrintFlag,NewFlag = @.NewFlag,CheckText = @.CheckText,PanelTestGroup = @.PanelTestGroup,PrintLevel = @.PrintLevel

where testnumber = @.TestNumber and datatype=@.DataType

end

else

begin

insert into #Final_CompData (testnumber,datatype,description,class,printflag,newflag,checktext,paneltestgroup,printlevel)

values (@.testnumber,@.datatype,@.description,@.class,@.printflag,@.newflag,@.checktext,@.paneltestgroup,@.printlevel)

end

else -- @.Exams_pkey is not = @.TopExam_pkey - only carry testnumber, datatype, and result info

begin

if not exists (select testnumber from #Final_CompData where testnumber=@.TestNumber and datatype = @.Datatype)

begin

insert into #Final_CompData (testnumber,datatype) values (@.TestNumber,@.DataType)

end

-- update correct Exam?Result

if @.Exams_pkey = @.Exam1pkey

begin

update #Final_CompData set Exam1Result = (select rtrim(isnull(exam1result,'')) from #Final_CompData where testnumber = @.TestNumber and datatype=@.DataType) + char(10) + rtrim(@.Formatted)