sql error 15138: client

Showing posts with label client. Show all posts

Thursday, March 29, 2012

Error 18456 - domain admins only users that can authenticate

Server 2003
SQL 2000 SP3a
I have my server set up and my ODBC client configured. When I log in as
myself, as a domain admin, I can set my authentication to Windows (in my
ODBC), when I log on as a Domain Users, I cannot.
I get and error 18456. My server properties show SQL Server and Windows
authentication is allowed and there is no semi-colon in my password. What
security setting am I missing to allow my domain users to authenticate via
Windows authentication?
TIAHi,
Did you gave domain OS user access to SQL Server?
If not; open Enterprise manager and use Security option to add the Domain
user/group to SQL Server.
Thanks
Hari
SQL Server MVP
"dsb" <dsb@.discussions.microsoft.com> wrote in message
news:F7A3CA7A-1012-4673-8CDF-9B4B66583A3A@.microsoft.com...
> Server 2003
> SQL 2000 SP3a
> I have my server set up and my ODBC client configured. When I log in as
> myself, as a domain admin, I can set my authentication to Windows (in my
> ODBC), when I log on as a Domain Users, I cannot.
> I get and error 18456. My server properties show SQL Server and Windows
> authentication is allowed and there is no semi-colon in my password. What
> security setting am I missing to allow my domain users to authenticate via
> Windows authentication?
> TIA

Monday, March 26, 2012

error 17805; invalid buffer received from client

Seen this ?
http://support.microsoft.com/default.aspx?scid=kb;en-
us;832977
Peter
"Action speaks louder than words but not nearly as often"
Mark Twain

>--Original Message--
>I am trying to capture this error at its source. It is
very random and when
>using sql profiler I get too many rows that overload the
system. I simply
>want to catch the stored proc. (or T-sql statement) that
is causing this
>error. When I run sql profiler to only capture this
error that is fine but I
>in the TextData column I only get the error message,
again not the stored
>proc or statement that is causing this.
>Is there another way I should be running sql profiler?
Is there an error
>table that I can put a trigger on? ANY suggestions would
be appreciated.
>Thanks.
>.
>yes, but I did not notice the link to the sp_blocker_pss80 before. This
still gives me too much information (and will quickly fill up my hard drive)
when I just want to capture the event that is causing this. I will double
check but I'm pretty sure we are not using the utilities that are mentioned
in those articles.
Thanks anyway for you help.
"Peter The Spate" wrote:

> Seen this ?
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;832977
> Peter
> "Action speaks louder than words but not nearly as often"
> Mark Twain
>
> very random and when
> system. I simply
> is causing this
> error that is fine but I
> again not the stored
> Is there an error
> be appreciated.
>

error 17805; invalid buffer received from client

Seen this ?
http://support.microsoft.com/default.aspx?scid=kb;en-
us;832977
Peter
"Action speaks louder than words but not nearly as often"
Mark Twain

>--Original Message--
>I am trying to capture this error at its source. It is
very random and when
>using sql profiler I get too many rows that overload the
system. I simply
>want to catch the stored proc. (or T-sql statement) that
is causing this
>error. When I run sql profiler to only capture this
error that is fine but I
>in the TextData column I only get the error message,
again not the stored
>proc or statement that is causing this.
>Is there another way I should be running sql profiler?
Is there an error
>table that I can put a trigger on? ANY suggestions would
be appreciated.
>Thanks.
>.
>
yes, but I did not notice the link to the sp_blocker_pss80 before. This
still gives me too much information (and will quickly fill up my hard drive)
when I just want to capture the event that is causing this. I will double
check but I'm pretty sure we are not using the utilities that are mentioned
in those articles.
Thanks anyway for you help.
"Peter The Spate" wrote:

> Seen this ?
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;832977
> Peter
> "Action speaks louder than words but not nearly as often"
> Mark Twain
>
> very random and when
> system. I simply
> is causing this
> error that is fine but I
> again not the stored
> Is there an error
> be appreciated.
>
sql

Error 17805 Invalid buffer received from Client when executing stored Procedure

Hello,
I am receiving this error when I try to execute a Stored Procedure in
SQL Server 2000 using SYBASE dblib.
I have been able to connect to connect to SQL Server from Unix, but
when I try to execute a Stored Procedure via dblib, I get the error:
"Error 17805 Invalid buffer received from Client"
My Stored Procedure does not receive parameters, so I don't know what
is happening.
If I execute a simple SQL Statement, it works fine
I have a SQL Server 2000 running SP 4.
anyone can help?
Thank you in advanced
Jose Manuel
Jose Manuel wrote:
> Hello,
> I am receiving this error when I try to execute a Stored Procedure in
> SQL Server 2000 using SYBASE dblib.
> I have been able to connect to connect to SQL Server from Unix, but
> when I try to execute a Stored Procedure via dblib, I get the error:
> "Error 17805 Invalid buffer received from Client"
> My Stored Procedure does not receive parameters, so I don't know what
> is happening.
> If I execute a simple SQL Statement, it works fine
> I have a SQL Server 2000 running SP 4.
> anyone can help?
> Thank you in advanced
> Jose Manuel
Just curious: Why are you executing SQL on SQL Server 2000 usin the
Sybase dbLib instead of a SQL Server client library?
David Gugick
Imceda Software
www.imceda.com
|||Jose Manuel,
I am having exactlly the same problem as you had.
Could you please tell me how did you solve it?
Thanks
creo que voy varios pasos atras de ti. ojal me puedas ayudar. tengo el
mismo problema.
te ha contactado alguien ?
lo pudiste resolver?
gracias.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Error 17805

Error 17805 Severity 20 State 3 Invalid Buffer received
from client, logged in SQL Server (2K) Log.
Please help.Hi Allan,
I have SQL Server 2K with Service Pack 3 running on WIN
2K Advanced Server Plus Service Pack 3. The server is
accessed by clients over the internet.(Database engine
for an e-commerce site.)
The Error 17805 Severity 20 State 3 invalid buffer
recieved from client suggest the client is sending a
corrupt packet to the SQL server(only a guess).
Please help.
Regards
>--Original Message--
>I have no idea when you are seeing this as you do not
say but if it is after
>trying to pass data to SQL Server through a client app
then make sure that
>things like parameter definitions to stored procs etc
are the correct type
>i.e
>varchar not nvarchar etc etc
>
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Olutimi" <Olutimi@.hotmail.com> wrote in message
>news:09c001c366f6$c95ed850$a401280a@.phx.gbl...
>> Error 17805 Severity 20 State 3 Invalid Buffer received
>> from client, logged in SQL Server (2K) Log.
>> Please help.
>
>.
>|||You could also try making sure the clients are communicating through the
same API. Where I have seen mention of this error before is as I said in
command parameter (ADO) datatype mismatches
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Olutimi" <olutimi@.hotmail.com> wrote in message
news:0a0801c366fc$f930e9f0$a401280a@.phx.gbl...
> Hi Allan,
> I have SQL Server 2K with Service Pack 3 running on WIN
> 2K Advanced Server Plus Service Pack 3. The server is
> accessed by clients over the internet.(Database engine
> for an e-commerce site.)
> The Error 17805 Severity 20 State 3 invalid buffer
> recieved from client suggest the client is sending a
> corrupt packet to the SQL server(only a guess).
> Please help.
> Regards
>
> >--Original Message--
> >I have no idea when you are seeing this as you do not
> say but if it is after
> >trying to pass data to SQL Server through a client app
> then make sure that
> >things like parameter definitions to stored procs etc
> are the correct type
> >i.e
> >
> >varchar not nvarchar etc etc
> >
> >
> >
> >
> >--
> >
> >--
> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >"Olutimi" <Olutimi@.hotmail.com> wrote in message
> >news:09c001c366f6$c95ed850$a401280a@.phx.gbl...
> >> Error 17805 Severity 20 State 3 Invalid Buffer received
> >> from client, logged in SQL Server (2K) Log.
> >> Please help.
> >
> >
> >.
> >

Error 17 SQL SERVER

Cannot enable TCP/IP on SQL Server 2000 when switching to a new server.
Dynamic port - Client Configuration error cannot connect.
Help
--
Regards,
JamieMore Info... DMZ server cannot be accessed from server inside DMZ. Called
for support on this one. Old DMZ was aliased in code using a name that was
different from the actual server name. Once the actual server name was used
as an alias the TCP/IP worked fine. To enable the alias, a second server
needed to be setup using an ole db alias.
--
Regards,
Jamie
"thejamie" wrote:
> Cannot enable TCP/IP on SQL Server 2000 when switching to a new server.
> Dynamic port - Client Configuration error cannot connect.
> Help
> --
> Regards,
> Jamiesql

Error 16954

Hello.
What maybe the reason of error 16954: "Executing SQL Directly; no
cursor". I have SQL 2000 SP3, application runs from client machine. What
elements can affect this error?
bye...
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +--+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+Hi
Does this werror occur every time, or only now and then?
What code is being executed?
Regards
Mike
"[BuKoX]" wrote:

> Hello.
> What maybe the reason of error 16954: "Executing SQL Directly; no
> cursor". I have SQL 2000 SP3, application runs from client machine. What
> elements can affect this error?
> bye...
> --
> __ __
> |__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
> |__// |__|| | \\ \__// / \\ +--+
> +[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+
>|||Mike Epprecht (SQL MVP) wrote:

> Does this werror occur every time, or only now and then?
> What code is being executed?
This error occured as long as application is running and queries are
executed (every queries). Application connect db through MDAC.
bye...
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +--+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+|||does your SQLServer crash (or very slow) after this?
any other application(s) able to connect and run a query on this server
after that?

Error 16954

Hello.
What maybe the reason of error 16954: "Executing SQL Directly; no
cursor". I have SQL 2000 SP3, application runs from client machine. What
elements can affect this error?
bye...
--
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +--+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+Hi
Does this werror occur every time, or only now and then?
What code is being executed?
Regards
Mike
"[BuKoX]" wrote:
> Hello.
> What maybe the reason of error 16954: "Executing SQL Directly; no
> cursor". I have SQL 2000 SP3, application runs from client machine. What
> elements can affect this error?
> bye...
> --
> __ __
> |__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
> |__// |__|| | \\ \__// / \\ +--+
> +[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+
>|||Mike Epprecht (SQL MVP) wrote:
> Does this werror occur every time, or only now and then?
> What code is being executed?
This error occured as long as application is running and queries are
executed (every queries). Application connect db through MDAC.
bye...
--
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +--+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+|||does your SQLServer crash (or very slow) after this?
any other application(s) able to connect and run a query on this server
after that?

Error 16954

Hello.
What maybe the reason of error 16954: "Executing SQL Directly; no
cursor". I have SQL 2000 SP3, application runs from client machine. What
elements can affect this error?
bye...
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +--+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+
Hi
Does this werror occur every time, or only now and then?
What code is being executed?
Regards
Mike
"[BuKoX]" wrote:

> Hello.
> What maybe the reason of error 16954: "Executing SQL Directly; no
> cursor". I have SQL 2000 SP3, application runs from client machine. What
> elements can affect this error?
> bye...
> --
> __ __
> |__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
> |__// |__|| | \\ \__// / \\ +--+
> +[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+
>
|||Mike Epprecht (SQL MVP) wrote:

> Does this werror occur every time, or only now and then?
> What code is being executed?
This error occured as long as application is running and queries are
executed (every queries). Application connect db through MDAC.
bye...
__ __
|__\\ | || |_// / \\ \_// FreeBSD: The Power To Serve
|__// |__|| | \\ \__// / \\ +--+
+[ http://bukox.prv.pl ][ http://total.bukox.dmkproject.pl ]+
|||does your SQLServer crash (or very slow) after this?
any other application(s) able to connect and run a query on this server
after that?

Thursday, March 22, 2012

error 1603 when installing sql server 2005 sept ctp

I tried to install SQL Server 2005 Sept CTP
and it gave error 1603 when installing MS SQL native client.

What should I do?
As a first step, I suggest you tell us more about your setup, which build you are attempting to install and whether you had a previous build of SQL Server 2005 or Visual Studio 2005 on the machine. That would help us understand a little more about your situation. Andrew Watt MVP - InfoPath wrote in message news:23577834-954b-43c3-8625-dbd6ffba2c00@.discussions.microsoft.com... >I tried to install SQL Server 2005 Sept CTP
> and it gave error 1603 when installing MS SQL native client. >
> What should I do? > > >
>

Error 1603 trying to install SRS on client machines

Hello all. I'm hoping I can get some insight as to what's going wrong
here. We have CRM 3.0 and want to give developers SRS Report Designer
on their workstations to design some reports. I'm trying to install SRS
2000 on the first developer's machine, and after running setup it gives
me three warnings indicating:
* IIS is not installed;
* ASP.NET is not installed or not registered with IIS;
* IIS is not installed. (This error is listed twice).
This is to be expected as it's a workstation, not a server. But it
should only prevent me from installing certain server components such
as the Report Manager, as far as I can tell. So I click Next and see it
preparing to install. Then comes the error:
SetDialogs
Error Code: 1603
I have VS.NET 2003 installed with Visual C++ and the .NET SDK. From my
reading, all that's necessary is the basic VS.NET framework, so this
should also be acceptable. So -- help. What can I do to get past this?
Is it expecting something particular? I'm running the SRS setup from
the CRM installation media; might that be a problem?Fixed it myself, fortunately. It turned out that all I had to do was
ensure the ASPNET account was present on the local machine. Just
creating this account made everything work quite happily. More
properly, I followed the instructions in KB 320112 to repair the 1.1
framework which had the effect of creating this account.

error 1603 installing microsoft sql native client

Hi
You will need to remove all references to the SQL Server 2005 before you
start the instalation. Did you have any prior to RTM vesrions on your
computer?
"Microsoft News Group" <mflynn@.cvmediaonline.com> wrote in message
news:%23Ip$rjMlGHA.3512@.TK2MSFTNGP03.phx.gbl...
>I am sql server 2005 installed, and I do not think I have sql server
>express. I tried to install express but I keep getting.
> Error 1603 installing Microsoft SQL Native Client.
> This worked before when reinstalling SQL Server 2005 but uninstalling it,
> but will this hurt SQL server 2005 if I do it again for SQL Express?
>I am sql server 2005 installed, and I do not think I have sql server
express. I tried to install express but I keep getting.
Error 1603 installing Microsoft SQL Native Client.
This worked before when reinstalling SQL Server 2005 but uninstalling it,
but will this hurt SQL server 2005 if I do it again for SQL Express?|||Hi
You will need to remove all references to the SQL Server 2005 before you
start the instalation. Did you have any prior to RTM vesrions on your
computer?
"Microsoft News Group" <mflynn@.cvmediaonline.com> wrote in message
news:%23Ip$rjMlGHA.3512@.TK2MSFTNGP03.phx.gbl...
>I am sql server 2005 installed, and I do not think I have sql server
>express. I tried to install express but I keep getting.
> Error 1603 installing Microsoft SQL Native Client.
> This worked before when reinstalling SQL Server 2005 but uninstalling it,
> but will this hurt SQL server 2005 if I do it again for SQL Express?
>|||When you mean references, do you mean all of SQL Server 2005? I would like
both on the computer, is that not possible. What is RTM versions? If I
uninstall Native Client from Add/Remove Programs, and install Express, will
that mess up SQL server 2005 in anyway, because I have no idea what the
native client is.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:evuRYJPlGHA.1340@.TK2MSFTNGP02.phx.gbl...
> Hi
> You will need to remove all references to the SQL Server 2005 before you
> start the instalation. Did you have any prior to RTM vesrions on your
> computer?
>
>
>
> "Microsoft News Group" <mflynn@.cvmediaonline.com> wrote in message
> news:%23Ip$rjMlGHA.3512@.TK2MSFTNGP03.phx.gbl...
>|||When you mean references, do you mean all of SQL Server 2005? I would like
both on the computer, is that not possible. What is RTM versions? If I
uninstall Native Client from Add/Remove Programs, and install Express, will
that mess up SQL server 2005 in anyway, because I have no idea what the
native client is.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:evuRYJPlGHA.1340@.TK2MSFTNGP02.phx.gbl...
> Hi
> You will need to remove all references to the SQL Server 2005 before you
> start the instalation. Did you have any prior to RTM vesrions on your
> computer?
>
>
>
> "Microsoft News Group" <mflynn@.cvmediaonline.com> wrote in message
> news:%23Ip$rjMlGHA.3512@.TK2MSFTNGP03.phx.gbl...
>

error 1603 installing microsoft sql native client

I am sql server 2005 installed, and I do not think I have sql server
express. I tried to install express but I keep getting.
Error 1603 installing Microsoft SQL Native Client.
This worked before when reinstalling SQL Server 2005 but uninstalling it,
but will this hurt SQL server 2005 if I do it again for SQL Express?Hi
You will need to remove all references to the SQL Server 2005 before you
start the instalation. Did you have any prior to RTM vesrions on your
computer?
"Microsoft News Group" <mflynn@.cvmediaonline.com> wrote in message
news:%23Ip$rjMlGHA.3512@.TK2MSFTNGP03.phx.gbl...
>I am sql server 2005 installed, and I do not think I have sql server
>express. I tried to install express but I keep getting.
> Error 1603 installing Microsoft SQL Native Client.
> This worked before when reinstalling SQL Server 2005 but uninstalling it,
> but will this hurt SQL server 2005 if I do it again for SQL Express?
>|||When you mean references, do you mean all of SQL Server 2005? I would like
both on the computer, is that not possible. What is RTM versions? If I
uninstall Native Client from Add/Remove Programs, and install Express, will
that mess up SQL server 2005 in anyway, because I have no idea what the
native client is.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:evuRYJPlGHA.1340@.TK2MSFTNGP02.phx.gbl...
> Hi
> You will need to remove all references to the SQL Server 2005 before you
> start the instalation. Did you have any prior to RTM vesrions on your
> computer?
>
>
>
> "Microsoft News Group" <mflynn@.cvmediaonline.com> wrote in message
> news:%23Ip$rjMlGHA.3512@.TK2MSFTNGP03.phx.gbl...
>>I am sql server 2005 installed, and I do not think I have sql server
>>express. I tried to install express but I keep getting.
>> Error 1603 installing Microsoft SQL Native Client.
>> This worked before when reinstalling SQL Server 2005 but uninstalling it,
>> but will this hurt SQL server 2005 if I do it again for SQL Express?
>

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)