Problem With SQL Server

Hello,
Do any body knows about following errors
2006-05-09 11:51:51.77 spid99 Error: 4014, Severity: 20, State: 2.
2006-05-09 11:51:51.77 spid99 A fatal error occurred while reading the
input stream from the network. The session will be terminated.
2006-05-09 11:51:52.44 Server Error: 4014, Severity: 20, State: 2.
2006-05-09 11:51:52.44 Server A fatal error occurred while reading the
input stream from the network. The session will be terminated.
I am getting this error frequently, especially under mediam load. Pls help"input stream from the network" suggests you have networking issue. start
with network monitor to see if there is any problem.
http://support.microsoft.com/kb/812953/
-oj
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:CFDE6666-841F-4FFE-AC07-E4D60AB6D464@.microsoft.com...
> Hello,
> Do any body knows about following errors
> 2006-05-09 11:51:51.77 spid99 Error: 4014, Severity: 20, State: 2.
> 2006-05-09 11:51:51.77 spid99 A fatal error occurred while reading
> the
> input stream from the network. The session will be terminated.
> 2006-05-09 11:51:52.44 Server Error: 4014, Severity: 20, State: 2.
> 2006-05-09 11:51:52.44 Server A fatal error occurred while reading
> the
> input stream from the network. The session will be terminated.
> I am getting this error frequently, especially under mediam load. Pls help
>

Problem With SQL Server

Hello,
Do any body knows about following errors
2006-05-09 11:51:51.77 spid99 Error: 4014, Severity: 20, State: 2.
2006-05-09 11:51:51.77 spid99 A fatal error occurred while reading the
input stream from the network. The session will be terminated.
2006-05-09 11:51:52.44 Server Error: 4014, Severity: 20, State: 2.
2006-05-09 11:51:52.44 Server A fatal error occurred while reading the
input stream from the network. The session will be terminated.
I am getting this error frequently, especially under mediam load. Pls help"input stream from the network" suggests you have networking issue. start
with network monitor to see if there is any problem.
http://support.microsoft.com/kb/812953/
-oj
"kailux4" <kailux4@.discussions.microsoft.com> wrote in message
news:CFDE6666-841F-4FFE-AC07-E4D60AB6D464@.microsoft.com...
> Hello,
> Do any body knows about following errors
> 2006-05-09 11:51:51.77 spid99 Error: 4014, Severity: 20, State: 2.
> 2006-05-09 11:51:51.77 spid99 A fatal error occurred while reading
> the
> input stream from the network. The session will be terminated.
> 2006-05-09 11:51:52.44 Server Error: 4014, Severity: 20, State: 2.
> 2006-05-09 11:51:52.44 Server A fatal error occurred while reading
> the
> input stream from the network. The session will be terminated.
> I am getting this error frequently, especially under mediam load. Pls help
>

Problem with SELECT...WHERE IN procedure

This works!
SELECT au_lname, state
FROM authors
WHERE state IN ('CA', 'IN', 'MD')
--
But when I do the same in a proc it doesn't!
create procedure mytest
@.test varchar(1000)
as
SELECT au_lname, state
FROM authors
WHERE state IN (@.test)
go
Call proc:
exec test 'CA, IN, MD'
I get no error messages, just no results!
Whats wrong?
Regards
/AndersHave a look at
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/arrays-in-sql.html
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Anders" <it99ama@.du.se> wrote in message
news:O5GLses2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> This works!
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> --
> But when I do the same in a proc it doesn't!
> create procedure mytest
> @.test varchar(1000)
> as
> SELECT au_lname, state
> FROM authors
> WHERE state IN (@.test)
> go
> Call proc:
> exec test 'CA, IN, MD'
> I get no error messages, just no results!
> Whats wrong?
> Regards
> /Anders
>
>
>|||Anders (it99ama@.du.se) writes:
> This works!
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> --
> But when I do the same in a proc it doesn't!
> create procedure mytest
> @.test varchar(1000)
> as
> SELECT au_lname, state
> FROM authors
> WHERE state IN (@.test)
> go
> Call proc:
> exec test 'CA, IN, MD'
> I get no error messages, just no results!
> Whats wrong?
Had there been a row with where state had had the value "CA, IN, MD",
you would have found that row.
Have a look at
http://www.sommarskog.se/arrays-in-...list-of-strings
for way to handle a list of values.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||http://www.aspfaq.com/2248
"Anders" <it99ama@.du.se> wrote in message
news:O5GLses2FHA.1188@.TK2MSFTNGP12.phx.gbl...
> This works!
> SELECT au_lname, state
> FROM authors
> WHERE state IN ('CA', 'IN', 'MD')
> --
> But when I do the same in a proc it doesn't!
> create procedure mytest
> @.test varchar(1000)
> as
> SELECT au_lname, state
> FROM authors
> WHERE state IN (@.test)
> go
> Call proc:
> exec test 'CA, IN, MD'
> I get no error messages, just no results!
> Whats wrong?
> Regards
> /Anders
>
>
>

Problem with Restore (SQL 2005)

Hello,
Yesterday at 2pm I started a restore of a very small database. It is now 0745 on the next day and it is still in the RESTORING state. It can't take this long. Does anyone know what I can do? I need to get this database back up, even if it is the version before the restore.

Any help ASAP would be appreciated!!

Thanks so much.

Quote:

Originally Posted by sawtooths

Hello,
Yesterday at 2pm I started a restore of a very small database. It is now 0745 on the next day and it is still in the RESTORING state. It can't take this long. Does anyone know what I can do? I need to get this database back up, even if it is the version before the restore.

Any help ASAP would be appreciated!!

Thanks so much.

I had a restore lock up on me once.
I had to restart SQL Server.
I know this is a horrible solution, but in my case it was on a demo box, so it was bearable.

Problem with query and date ranges

I'm trying to create a query that will tell me which requests
took longer than 10 days to move one from particular state to another
state. The query I've created returns the correct requests,
but not always the correct 'NextActionDate'/'NextStatus'/'NextState'.

I'm sure I'm missing something easy, but I can't figure out what it
might be. Any help is appreciated! Thanks,
Myron
-- remove SPAM-KILL from address to reply by email --

DDL for table creation and data population:

CREATE TABLE [dbo].[ReqHistory] (
[Id] [int] NOT NULL ,
[ReqId] [int] NOT NULL ,
[ReqIDStateId] [tinyint] NOT NULL ,
[ActionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RequestStates] (
[ID] [tinyint] NOT NULL ,
[StateText] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Requests] (
[ID] [int] NOT NULL ,
[ShortDescription] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StatusChangeDate] [datetime] NULL ,
[Status] [tinyint] NULL
) ON [PRIMARY]
GO

insert into Requests values(361, 'Test ID: 361', cast('2004-06-03 08:52:03.013' as datetime),98)
insert into Requests values(1400, 'Test ID: 1400', cast('2004-05-13 04:01:55.250' as datetime),97)
insert into Requests values(30051,'Test ID: 30051', cast('2004-09-15 10:10:25.093' as datetime), 96)

insert into ReqHistory values(904,361,1,cast('2004-05-03 00:20:55.983' as datetime))
insert into ReqHistory values(931,361,2,cast('2004-05-03 01:07:14.157' as datetime))
insert into ReqHistory values(959,361,20,cast('2004-05-03 01:29:20.157' as datetime))
insert into ReqHistory values(20250,361,31,cast('2004-06-03 08:51:58.950' as datetime))
insert into ReqHistory values(20251,361,98,cast('2004-06-03 08:52:03.013' as datetime))
insert into ReqHistory values(20249,361,30,cast('2004-06-03 08:51:51.107' as datetime))
insert into ReqHistory values(939,361,10,cast('2004-05-03 01:10:36.093' as datetime))
insert into ReqHistory values(7318,1400,1,cast('2004-05-13 03:48:01.420' as datetime))
insert into ReqHistory values(7346,1400,2,cast('2004-05-13 03:56:37.857' as datetime))
insert into ReqHistory values(7347,1400,12,cast('2004-05-13 03:57:03.293' as datetime))
insert into ReqHistory values(7356,1400,22,cast('2004-05-13 04:00:58.497' as datetime))
insert into ReqHistory values(7357,1400,97,cast('2004-05-13 04:01:55.250' as datetime))
insert into ReqHistory values(53218,30051,1,cast('2004-08-06 10:12:33.050' as datetime))
insert into ReqHistory values(53223,30051,2,cast('2004-08-06 10:15:32.500' as datetime))
insert into ReqHistory values(53246,30051,13,cast('2004-08-06 10:26:34.850' as datetime))
insert into ReqHistory values(53264,30051,23,cast('2004-08-06 10:47:38.993' as datetime))
insert into ReqHistory values(70138,30051,3,cast('2004-09-15 09:21:18.230' as datetime))
insert into ReqHistory values(70257,30051,96,cast('2004-09-15 10:10:25.093' as datetime))

insert into RequestStates values(1,'Awaiting CSMC')
insert into RequestStates values(2,'CSMC Review')
insert into RequestStates values(3,'Reject Awaiting CSMC')
insert into RequestStates values(10,'Awaiting MA Review')
insert into RequestStates values(12,'Awaiting FO Review')
insert into RequestStates values(13,'Awaiting IS Review')
insert into RequestStates values(20,'MA Review')
insert into RequestStates values(22,'FO Review')
insert into RequestStates values(23,'IS Review')
insert into RequestStates values(30,'Func Approval')
insert into RequestStates values(31,'Func Approval Complete')
insert into RequestStates values(96,'Resolved')
insert into RequestStates values(97,'Planning')
insert into RequestStates values(98,'Open')
insert into RequestStates values(99,'Closed')

The query that almost works:

select irh.ReqID, irh.MAactiondate, irh.reviewstate,
irh2.Nextactiondate, irh2.irh2state as NextStatus, irh2.statetext as NextState
from (select distinct irh.ReqID, max(irh.actiondate) as MAactiondate,
irh.ReqIDStateID As IRHState, irs.statetext as ReviewState
from ReqHistory IRH
join requeststates irs on irs.id = irh.ReqIDStateID
where irh.ReqIDStateID in (20, 23)
group by irh.ReqID, irs.statetext, irh.ReqIDStateID) as irh
join (select irh2.actiondate as NextActiondate, irh2.ReqID, irh2.IRH2State, irs.statetext
from (select min(actiondate) as actiondate, ReqID,
min(ReqIDStateID) as IRH2State
from ReqHistory
--the WHERE is wrong, and I believe should be irh2.Nextactiondate > irh.maactiondate,
--but I couldn't make it work
where ReqIDStateID > 23
group by ReqID) as irh2
join RequestStates irs on irs.id = irh2.irh2state ) as irh2 on irh.ReqID = irh2.ReqID
join requests ir on ir.id = irh.ReqID
where irh.MAactiondate + 10 < irh2.Nextactiondate
order by irh.ReqID

The data being returned is:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 96 Resolved

The data that should have been returned:
(the 'time' portion of the dates edited out for space)

ReqID MAActionDate Review State NextActiondate NextStatus NextState
361 2004-05-03 MA Review 2004-06-03 30 Functional Approval
30051 2004-08-06 IS Review 2004-09-15 3 Reject Awaiting CSMCHoly subqueries batman!

Thanks for including the DDL... it made finding an answer to this much
easier. A couple quick notes though... you didn't include and foreign
or primary keys. That combined with the inconsistent naming standards
made it a little difficult to understand the code.

The following code returned what you were expecting. The code assumes
that no two actions can occur at the same exact time. If that
assumption is incorrect then the code will not work correctly in those
situations.

Also, instead of the NOT EXISTS you could of course opt to use a LEFT
OUTER JOIN along with checking for IS NULL on one of the PK columns for
the table (RH). That often gives better performance than NOT EXISTS in
my experience.

HTH,
-Tom.

SELECT MAH.ReqID, MAH.ActionDate, RS.StateText, NA.ActionDate,
NA.ReqIDStateID, NS.StateText
FROM ReqHistory MAH
INNER JOIN ReqHistory NA ON NA.ReqID = MAH.ReqID
AND NA.ActionDate > DATEADD(dy, 10,
MAH.ActionDate)
INNER JOIN RequestStates RS ON RS.ID = MAH.ReqIDStateID
INNER JOIN RequestStates NS ON NS.ID = NA.ReqIDStateID
WHERE MAH.ReqIDStateID IN (20, 23)
AND NOT EXISTS (SELECT *
FROM ReqHistory RH
WHERE RH.ReqID = MAH.ReqID
AND RH.ActionDate > MAH.ActionDate
AND RH.ActionDate < NA.ActionDate)|||"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote:
>Holy subqueries batman!
>Thanks for including the DDL... it made finding an answer to this much
>easier. A couple quick notes though... you didn't include and foreign
>or primary keys. That combined with the inconsistent naming standards
>made it a little difficult to understand the code.
<some snippage
Thanks for the speedy and accurate answer, Tom! Your query looks a
lot more elegant than my monster, and it found rows that my original
was dropping. :)
Myron|||Your basic design is wrong. Time comes in durations and not points --
look at everyone from Zeno to the ISO standards. Also, your data
element names make no sense -- "ReqIDStateId"? if something is a
state, then it is a value and not an indentifier. What is the vague
"ID": floating around? Surely you do not blindly use sequentail
numbering in an RDBMS to mimic a sequential file physical record
number!

CREATE TABLE RequestHistory
( request_nbr INTEGER NOT NULL ,
request_status INTEGER NOT NULL
REFERENCES RequestStatusCodes(request_status)
ON UPDATE CASCADE,
start_date DATETIME DEFAULT CURRENT TIMESTAMP NOT NULL,
end_date DATETIME, -- null means current
PRIMARY KEY (request_nbr, start_date ));

>>I'm trying to create a query that will tell me which requests took
longer than 10 days to move one from particular state to another state.
<<

Trival with the right design, isn't it?