Mar 31, 2004
I have created a stored procedure in SQL Server. I found it very slow, so i putted "select getDate(), 'testposition 1'" at different places, so I could see what part of the code that takes time.
The problem is: Depending on where I put the select statements, the execution of the stored procedure seems to just stop. And depending on where i put the select statements, it stops at different places.
This is how I do (example):
1. I re-create the stored procedure with some "select getDate()"-statements
2. I run the stored procedure 15:00:00
3. I cancel the stored procedure after 20 seconds and look at the resultsets. All getDate-functions show a time between 15:00:00 and 15:00:02
4. I run the stored procedure 15:01:00
5. I cancel the stored procedure after 5 seconds and look at the resultsets. The same amount of resultsets are showed, so I can make the conclusion that the execution stopped at the same place as last time. All getDate-functions show a time between 15:01:00 and 15:01:02 this time too.
6. I re-create the stored procedure with some new "select getDate()"-statements
7. Now the execution stops at an other position. Somtimes even between two "select getDate()"-statements!
I pasted the whole stored procedure here:
drop PROCEDURE spUpdateASW
go
create PROCEDURE spUpdateASW
AS
DECLARE @DataBatchID int
DECLARE @DataHeaderID int
DECLARE @ASWTableID int
DECLARE @ASWTableName varchar(25)
DECLARE @ASWFieldName varchar(25)
DECLARE @AllowASWUpdate tinyint
DECLARE @IsPrimaryKey tinyint
DECLARE @DataTypeIsNumeric tinyint
DECLARE @Data varchar(100)
DECLARE @SQL_Where as varchar(400)
DECLARE @SQL_Insert as varchar(1000)
DECLARE @SQL_InsertValues as varchar(400)
DECLARE @SQL_Update as varchar(1000)
DECLARE @updateCounter int
DECLARE @whereCounter int
DECLARE @SQL_CheckIfAlreadyExist as varchar(1000)
DECLARE @ErrorMessage varchar(500)
DECLARE @RuleWhen as varchar(50)
DECLARE @RuleWhenToExec as varchar(500)
DECLARE @tempStr as varchar(700)
DECLARE @server varchar(50)
DECLARE @shortServer varchar(50)
SET @server = 'GIBSON_A3MFGF_T1.S44E5797.A3MFGFT1'
SET @shortServer = 'GIBSON_A3MFGF_T1'
DECLARE @SQL varchar(5000)
select getdate(), 'testposition 1'
CREATE Table #tmptblUpdateASW(
ASWRowAlreadyExists int,
RuleWhenIsValid int
)
INSERT INTO #tmptblUpdateASW(ASWRowAlreadyExists, RuleWhenIsValid) Values(-1, -1)
DECLARE Batch_Cursor CURSOR LOCAL FOR
SELECT DataBatchID from tblDataBatch
where DateConverted is not null and ASWUpdateStarted = 0
and DataBatchID not IN(
select fkDataBatchID from tblDataHeader where DataHeaderID IN(
select fkDataHeaderID from tblASWData where ConversionErrorMessage is not null
)
)
OPEN Batch_Cursor
FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
WHILE @@FETCH_STATUS = 0
BEGIN
Update tblDataBatch set ASWUpdateStarted = 1 where DataBatchID = @DataBatchID
DECLARE Header_Cursor CURSOR LOCAL FOR
SELECT DataHeaderID
from tblDataHeader
inner join tblAgileFieldType on tblDataHeader.fkAgileFieldTypeID = tblAgileFieldType.AgileFieldTypeID
where fkDataBatchID = @DataBatchID and isSentToASW = 0 order by tblAgileFieldType.InsertOrder
OPEN Header_Cursor
FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE ASWTable_Cursor CURSOR LOCAL FOR
SELECT ASWTableID, ASWTableName, RuleWhen
from tblASWTable
inner join tblASWField on tblASWTable.ASWTableID = tblASWField.fkASWTableID
inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
where fkDataHeaderID = @DataHeaderID
group by ASWTableID, ASWTableName, RuleWhen, InsertOrder
order by InsertOrder
OPEN ASWTable_Cursor
FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
WHILE @@FETCH_STATUS = 0
BEGIN
exec spBuildRuleString @DataHeaderID, @RuleWhen, @RuleWhenToExec output, 0
SET @tempStr = 'IF ' + @RuleWhenToExec + ' UPDATE #tmptblUpdateASW SET RuleWhenIsValid=1 ELSE UPDATE #tmptblUpdateASW SET RuleWhenIsValid=0'
EXEC (@tempStr)
IF (SELECT RuleWhenIsValid FROM #tmptblUpdateASW) = 1
BEGIN
set @ErrorMessage = null
exec spASWDataCheck_hardCoded @DataHeaderID, @ErrorMessage output
SET @SQL_Insert = 'INSERT INTO ' + @server + '.' + @ASWTableName + '('
SET @SQL_InsertValues = 'VALUES('
SET @SQL_Update = 'UPDATE ' + @server + '.' + @ASWTableName + ' set '
SET @updateCounter = 0
SET @SQL_Where = ' WHERE '
SET @whereCounter = 0
DECLARE ASWField_Cursor CURSOR LOCAL FOR
SELECT ASWFieldName, AllowASWUpdate, IsPrimaryKey, DataTypeIsNumeric, Data
from tblASWField
inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
where fkASWTableID = @ASWTableID and fkDataHeaderID = @DataHeaderID
OPEN ASWField_Cursor
FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
select getdate(), 'testposition 2'
WHILE @@FETCH_STATUS = 0
BEGIN
select getdate(), @ASWFieldName, 'testposition 3'
set @Data = replace(@Data, char(39), char(39) + char(39))
if @DataTypeIsNumeric = 0
set @Data = char(39) + @Data + char(39)
set @SQL_Insert = @SQL_Insert + @ASWFieldName + ', '
set @SQL_InsertValues = @SQL_InsertValues + @Data + ', '
IF @AllowASWUpdate = 1
BEGIN
set @SQL_Update = @SQL_Update + @ASWFieldName + ' = ' + @Data + ', '
set @updateCounter = @updateCounter + 1
END
IF @IsPrimaryKey = 1
BEGIN
set @SQL_Where = @SQL_Where + @ASWFieldName + ' = ' + @Data + ' and '
SET @whereCounter = @whereCounter + 1
END
FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
END
select getdate(), 'testposition 4'
CLOSE ASWField_Cursor
DEALLOCATE ASWField_Cursor
SET @SQL_Where = LEFT(@SQL_Where, LEN(@SQL_Where) - 4)
SET @SQL_Insert = LEFT(@SQL_Insert, LEN(@SQL_Insert) - 1) + ') ' + LEFT(@SQL_InsertValues, LEN(@SQL_InsertValues) - 1) + ')'
SET @SQL_Update = LEFT(@SQL_Update, LEN(@SQL_Update) - 1) + @SQL_Where
SET @SQL_CheckIfAlreadyExist = 'Update #tmptblUpdateASW set ASWRowAlreadyExists = ' +
'(SELECT * from OPENQUERY(' + @shortServer + ','' SELECT count(*) FROM ' + @ASWTableName + ' ' + replace(@SQL_Where,char(39), char(39) + char(39)) + ' ''))'
Exec(@SQL_CheckIfAlreadyExist)
select getdate(), 'testposition 4'
select getdate(), 'testposition 5'
select getdate(), 'testposition 6'
IF @whereCounter = 0
begin
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Inga primary keys var valda för denna tabellen!')
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) > 1
begin
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Kombinationen av primary keys genererade följande where-sats: ' + @SQL_Where)
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 1 and @updateCounter > 0
begin
EXEC(@SQL_Update)
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Update, @ErrorMessage)
update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 0
begin
EXEC(@SQL_Insert)
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Insert, @ErrorMessage)
update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
end
END
FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
END
CLOSE ASWTable_Cursor
DEALLOCATE ASWTable_Cursor
FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
END
CLOSE Header_Cursor
DEALLOCATE Header_Cursor
UPDATE tblDataBatch set DateToASW = getDate() where DataBatchID = @DataBatchID
FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
END
CLOSE Batch_Cursor
DEALLOCATE Batch_Cursor
DROP Table #tmptblUpdateASW
GO
View 4 Replies
View Related
Feb 28, 2001
I have a stored procedure that is calling a cursor to populate some variables it then uses those variable to get more information and then inserts that info into a final table. The estimated number of records that it should insert is around 2 million. The procedure stops after about 6000 records inserted. It still apears to be running but in fact is not. Can anyone help? I have also attached the code.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [dbo].[create_table1_tmp] AS
/* table1 variables */
declare@v_ARCKEY int
declare@v_INVKEY int
declare@v_ITEM char (15)
declare@v_BRAND char(15)
declare@v_PRICE decimal(12, 2)
declare@v_QTYORD decimal(12, 3)
declare@v_QTYSHP decimal(12, 3)
declare@v_CTCKEY int
declare@v_SOMKEY int
declare@v_ORDATE datetime
declare@v_FNAME char (15)
declare@v_CONTACT varchar (20)
declare@v_TITLE varchar (25)
declare@v_SALUT char (4)
declare@v_DEGREE char (6)
declare@v_SALESMN2char (3)
declare@v_TTL_CODEchar (4)
declare@v_SPECIALTYvarchar (30)
declare@v_COMPANYvarchar (35)
declare@v_ADDRESS1varchar (50)
declare@v_ADDRESS2varchar (50)
declare@v_CITYvarchar (20)
declare@v_STATEchar (10)
declare@v_KEYCODEchar (10)
declare@v_ZIPchar (10)
declare@v_COUNTRYchar (2)
declare@v_ARCSOURCEchar (5)
declare@v_SLSVOLchar (1)
declare@v_TYPEchar (8)
declare@v_NUMDRSdecimal(9,0)
declare@v_BEDSIZEdecimal(4,0)
declare@v_NUMLIVESdecimal(9,0)
declare@v_CODEchar (3)
declare@v_SUBKEYint
declare@v_SUBTYPEchar (1)
declare@v_STARTDATEdatetime
declare@v_TERMdecimal(2, 0)
declare@v_STATUSchar (1)
declare@v_STATDATEdatetime
declare@v_XRENEWEDdecimal(2, 0)
declare@v_EXPDATEdatetime
declare@v_SHPAMTdecimal(12, 2)
declare@v_SOMSOURCEchar (5)
declare@v_PMETHchar (1)
declare@v_EXTPRICEdecimal(12, 2)
declare@v_CTYPEchar (5)
declare@v_CTCKEY_SBSUBS int
declare@v_ACRONYMchar (8)
declare@v_PONUMvarchar (20)
declare@v_EMAILvarchar (50)
declare@v_ARCPHONEVARCHAR (20)
declare@v_ARCFAXNOVARCHAR (20)
declare@v_CMCPHONEVARCHAR (20)
declare@v_CMCFAXNOVARCHAR (20)
declare@v_DOFAXTINYINT
declare@v_DOPHONETINYINT
declare@v_DORENEWTINYINT
declare@v_DOMAILTINYINT
declare@v_DOUPDATETINYINT
declare@v_UPDONLYTINYINT
declare@v_EMAIL_INFOTINYINT
declare@v_EMAILPROMOTINYINT
declare@v_DISCdecimal(7,3)
declare @v_EUPDATETINYINT
/* processing Variables */
declare @v_tmpint
declare@v_amtdecimal(9,3)
declare@v_loopint
declare@v_arckey_tmp INT
declare@v_ctckey_tmp INT
declare@v_invkey_tmp INT
declare@v_subkey_tmp INT
declare@v_arcDORENEW TINYINT
declare@v_cmcDORENEW TINYINT
declare@v_sbsDORENEW TINYINT
declare@v_arcDOFAX TINYINT
declare@v_cmcDOFAX TINYINT
declare@v_arcDOMAIL TINYINT
declare@v_cmcDOMAIL TINYINT
declare@v_arcDOPHONE TINYINT
declare@v_cmcDOPHONE TINYINT
/* cursors for retrieving online data*/
declarev_mast cursor for
select /*+ INDEX_COMBINE(arc) */
som.arckey arckey,
sot.invkey invkey,
sot.item item,
sot.price price,
sot.qtyord qtyord,
sot.qtyshp qtyshp,
som.ctckey ctckey,
som.somkey somkey,
sot.ordate ordate,
arc.phone arcphone,
arc.faxno arcfaxno,
arc.salesmn2 salesmn2,
arc.country country,
arc.source arcsource,
arc.slsvol slsvol,
arc.type type,
arc.specialty specialty,
arc.numdrs numdrs,
arc.bedsize bedsize,
arc.numlives numlives,
arc.code code,
arc.dorenew arcdorenew,
arc.company company,
arc.address1 address1,
arc.address2 address2,
arc.city city,
arc.state state,
arc.zipzip,
sub.eupdate eupdate,
sub.doupdate doupdate,
sub.subkey subkey,
sub.subtype subtype,
sub.startdate startdate,
sub.term term,
sub.status status,
sub.statdate statdate,
sub.xrenewed xrenewed,
sub.dorenew sbsdorenew,
som.shpamt shpamt,
som.source somsource,
som.pmeth pmeth,
sub.ctckey ctckey_sbsubs,
sub.updonly updonly,
sot.disc disc,
arc.dofax arcdofax,
som.ponum,
arc.domail,
arc.dophone
from sotran sot,somast som, arcust arc,sbsubs sub
where (som.arckey != '121449' and som.arckey != '1364166')
and (som.sotype not in (
select value from table1_param
where name = 'sotype') or
som.sotype is null)
and (som.sostat not in (
select value from table1_param
where name = 'somstat') or
som.sostat is null)
and arc.arckey = som.arckey
and (arc.code not in (
select value from table1_param
where name = 'code') or
arc.code is null)
and (arc.slsvol not in (
select value from table1_param
where name = 'slsvol') or
arc.slsvol is null)
and arc.foreign_ = 0
and sot.somkey = som.somkey
and (sot.sostat not in (
select value from table1_param
where name = 'sotstat') or
sot.sostat is null)
and sot.item > '0100'
and sub.subkey =* sot.subkey
order by arc.arckey,som.ctckey,sot.invkey,sub.subkey,sot.rq date desc
-- BEGIN PROCESSING THE CURSOR DATA HERE
OPEN v_mast
--pull all somast records where custno isn't 121449 or 1364166
--and sotype filtered and sostat isnot filtered
set @v_loop = 0
fetch next from v_mast into @v_arckey, @v_invkey, @v_item, @v_price, @v_qtyord,
@v_qtyshp, @v_ctckey, @v_somkey, @v_ordate, @v_arcphone, @v_arcfaxno, @v_salesmn2, @v_country,
@v_arcsource, @v_slsvol, @v_type, @v_specialty, @v_numdrs, @v_bedsize, @v_numlives,
@v_code, @v_arcdorenew, @v_company, @v_address1, @v_address2, @v_city,
@v_state, @v_zip, @v_eupdate, @v_doupdate, @v_subkey, @v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_sbsdorenew, @v_shpamt, @v_somsource, @v_pmeth, @v_ctckey_sbsubs,
@v_updonly, @v_disc, @v_arcdofax, @v_ponum, @v_arcdomail, @v_arcdophone
while @@fetch_status = 0--for v_rec in v_mast loop
Begin
set @v_loop = @v_loop + 1
set @v_expdate = dateadd(month,@v_term,@v_startdate) - 1
set @v_keycode = ''
--select company, address1, address2, city, state, zip
--from cmcship
--where ctckey = @v_ctckey
--if (@@rowcount = 0)
--set @v_tmp = null
--else
--begin
begin tran t1
set @v_company = (select top 1 company from cmcship where ctckey = @v_ctckey)
set @v_address1 = (select top 1 address1 from cmcship where ctckey = @v_ctckey)
set @v_address2 = (select top 1 address2 from cmcship where ctckey = @v_ctckey)
set @v_city = (select top 1 city from cmcship where ctckey = @v_ctckey)
set @v_state = (select top 1 state from cmcship where ctckey = @v_ctckey)
set @v_zip = (select top 1 zip from cmcship where ctckey = @v_ctckey)
commit tran t1
--end
--select company, address1, address2, city, state, zip
--from cmcadd
--where ctckey = @v_ctckey
--if (@@rowcount = 0)
--set @v_tmp = null
--else
--begin
begin tran t2
set @v_company = (select top 1 company from cmcadd where ctckey = @v_ctckey)
set @v_address1 = (select top 1 address1 from cmcadd where ctckey = @v_ctckey)
set @v_address2 = (select top 1 address2 from cmcadd where ctckey = @v_ctckey)
set @v_city = (select top 1 city from cmcadd where ctckey = @v_ctckey)
set @v_state = (select top 1 state from cmcadd where ctckey = @v_ctckey)
set @v_zip = (select top 1 zip from cmcadd where ctckey = @v_ctckey)
commit tran t2
--end
--select fname, contact, title, salut, degree, phone, faxno, ttl_code, dorenew,
--ctype, email, dofax, domail, email_info, emailpromo, docall
--from cmctac
--where ctckey = @v_ctckey
--if (@@rowcount = 0)
--RAISERROR ('cmc not found', 16, 1)
--else
--Begin
begin tran t3
set @v_fname = (select top 1 fname from cmctac where ctckey = @v_ctckey)
set @v_contact = (select top 1 contact from cmctac where ctckey = @v_ctckey)
set @v_title = (select top 1 title from cmctac where ctckey = @v_ctckey)
set @v_salut = (select top 1 salut from cmctac where ctckey = @v_ctckey)
set @v_degree = (select top 1 degree from cmctac where ctckey = @v_ctckey)
set @v_cmcphone = (select top 1 phone from cmctac where ctckey = @v_ctckey)
set @v_cmcfaxno = (select top 1 faxno from cmctac where ctckey = @v_ctckey)
set @v_ttl_code = (select top 1 ttl_code from cmctac where ctckey = @v_ctckey)
set @v_cmcdorenew = (select top 1 dorenew from cmctac where ctckey = @v_ctckey)
set @v_ctype = (select top 1 ctype from cmctac where ctckey = @v_ctckey)
set @v_email = (select top 1 email from cmctac where ctckey = @v_ctckey)
set @v_cmcdofax = (select top 1 dofax from cmctac where ctckey = @v_ctckey)
set @v_cmcdomail = (select top 1 domail from cmctac where ctckey = @v_ctckey)
set @v_email_info = (select top 1 email_info from cmctac where ctckey = @v_ctckey)
set @v_emailpromo = (select top 1 emailpromo from cmctac where ctckey = @v_ctckey)
set @v_cmcdophone = (select top 1 docall from cmctac where ctckey = @v_ctckey)
commit tran t3
--end
--select acronym, brand from invhead
--where invkey = @v_invkey
--if (@@cursor_rows = 0)
--set @v_tmp = null
--else
--begin
begin tran t4
set @v_acronym = (select top 1 acronym from invhead where invkey = @v_invkey)
set @v_brand = (select top 1 brand from invhead where invkey = @v_invkey)
commit tran t4
--end
set @v_amt = ((@v_qtyshp+@v_qtyord)*@v_price *(100-@v_disc))*.01
if @v_amt < 0
set @v_extprice = round(@v_amt,2)
else
set @v_extprice = round(@v_amt,2)
if (@v_arcdomail = 0 or @v_cmcdomail = 0)
set @v_domail = 0
else
set @v_domail = 1
if (@v_arcdofax = 0 or @v_cmcdofax = 0)
set @v_dofax = 0
else
set @v_dofax = 1
if (@v_arcdophone = 0 or @v_cmcdophone = 0)
set @v_dophone = 0
else
set @v_dophone = 1
if ((@v_arcdorenew = 0 or @v_cmcdorenew = 0) or @v_sbsdorenew = 0)
set @v_dorenew = 0
else
set @v_dorenew = 1
-- remove all but the newest orders
if @v_arckey = @v_arckey_tmp and @v_ctckey = @v_ctckey_tmp and
@v_invkey = @v_invkey_tmp and ((@v_subkey is null and
@v_subkey_tmp is null) or (@v_subkey = @v_subkey_tmp))
set @v_tmp = null
else
Begin
begin tran t5
set @v_arckey_tmp = @v_arckey
set @v_ctckey_tmp = @v_ctckey
set @v_invkey_tmp = @v_invkey
set @v_subkey_tmp = @v_subkey
commit tran t5
begin tran t6
insert into table1_tmp
(
arckey, invkey, item, brand, price, qtyord, qtyshp, ctckey, somkey,
ordate, fname, contact, title, salut, degree, salesmn2, ttl_code,
specialty, company, address1, address2, city, state, keycode,
zip, country, arcsource, slsvol, type, numdrs, bedsize, numlives,
code, subkey, subtype, startdate, term, status,
statdate, xrenewed,expdate, shpamt, somsource,
pmeth, extprice, ctype,ctckey_sbsubs, acronym,
ponum, email, arcphone, arcfaxno, cmcphone, cmcfaxno,
dofax, dophone, dorenew, domail, doupdate, updonly,
email_info, emailpromo, disc)
values (
@v_arckey, @v_invkey, @v_item, @v_brand, @v_price, @v_qtyord, @v_qtyshp,
@v_ctckey, @v_somkey, @v_ordate, @v_fname, @v_contact, @v_title,
@v_salut, @v_degree, @v_salesmn2, @v_ttl_code, @v_specialty, @v_company,
@v_address1, @v_address2, @v_city, @v_state, @v_keycode,
@v_zip, @v_country, @v_arcsource, @v_slsvol,
@v_type, @v_numdrs, @v_bedsize, @v_numlives, @v_code,
@v_subkey,@v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_expdate, @v_shpamt, @v_somsource,
@v_pmeth, @v_extprice, @v_ctype, @v_ctckey_sbsubs, @v_acronym,
@v_ponum, @v_email, @v_arcphone, @v_arcfaxno, @v_cmcphone,
@v_cmcfaxno, @v_dofax, @v_dophone, @v_dorenew, @v_domail,
@v_doupdate, @v_updonly, @v_email_info, @v_emailpromo, @v_disc
)
commit tran t6
End
fetch next from v_mast into @v_arckey, @v_invkey, @v_item, @v_price, @v_qtyord,
@v_qtyshp, @v_ctckey, @v_somkey, @v_ordate, @v_arcphone, @v_arcfaxno, @v_salesmn2, @v_country,
@v_arcsource, @v_slsvol, @v_type, @v_specialty, @v_numdrs, @v_bedsize, @v_numlives,
@v_code, @v_arcdorenew, @v_company, @v_address1, @v_address2, @v_city,
@v_state, @v_zip, @v_eupdate, @v_doupdate, @v_subkey, @v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_sbsdorenew, @v_shpamt, @v_somsource, @v_pmeth, @v_ctckey_sbsubs,
@v_updonly, @v_disc, @v_arcdofax, @v_ponum, @v_arcdomail, @v_arcdophone
end --while loop
close v_mast
deallocate v_mast
View 1 Replies
View Related
Apr 9, 2002
Can anyone help me?
My brand new installation of SQL Server 6.5 running on Windows 2000 suddenly stops working for about 6 minutes, in this periods no one can use SQL 6.5 services because it stops responding. My logs is show below:
2002/04/08 15:50:22.60 ods Error : 17832, Severity: 18, State: 0
2002/04/08 15:50:22.60 ods Unable to read login packet(s).
2002/04/08 15:50:24.50 ods Error : 17832, Severity: 18, State: 0
2002/04/08 15:50:24.50 ods Unable to read login packet(s).
2002/04/08 15:50:26.79 ods Error : 17832, Severity: 18, State: 0
2002/04/08 15:50:26.79 ods Unable to read login packet(s).
2002/04/09 07:10:21.48 ods Error : 17832, Severity: 18, State: 0
2002/04/09 07:10:21.48 ods Unable to read login packet(s).
2002/04/09 07:53:33.76 ods Error : 17824, Severity: 10, State: 0
2002/04/09 07:53:33.76 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname '2M_ROSANNA'.
2002/04/09 07:53:33.76 ods OS Error : 232,
2002/04/09 07:53:33.78 spid75 Error : 1608, Severity: 21, State: 2
2002/04/09 07:53:33.78 spid75 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information.
2002/04/09 07:53:41.10 ods Error : 17824, Severity: 10, State: 0
2002/04/09 07:53:41.10 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'CE_SANDRA'.
2002/04/09 07:53:41.10 ods OS Error : 232,
2002/04/09 07:53:41.10 spid80 Error : 1608, Severity: 21, State: 2
2002/04/09 07:53:41.10 spid80 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information.
2002/04/09 07:55:42.20 ods Error : 17824, Severity: 10, State: 0
2002/04/09 07:55:42.20 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'CE_SANDRA'.
2002/04/09 07:55:42.20 ods OS Error : 232,
2002/04/09 07:55:42.20 spid53 Error : 1608, Severity: 21, State: 2
2002/04/09 07:55:42.20 spid53 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information.
2002/04/09 07:57:09.40 ods Error : 17824, Severity: 10, State: 0
2002/04/09 07:57:09.40 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'FE_FERTILIDAD'.
2002/04/09 07:57:09.40 ods OS Error : 232,
2002/04/09 07:57:09.40 spid91 Error : 1608, Severity: 21, State: 2
2002/04/09 07:57:09.40 spid91 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information.
2002/04/09 07:57:09.40 ods Error : 17824, Severity: 10, State: 0
2002/04/09 07:57:09.40 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'SF_ROSA'.
2002/04/09 07:57:09.40 ods OS Error : 232,
2002/04/09 07:57:09.40 spid11 Error : 1608, Severity: 21, State: 2
2002/04/09 07:57:09.40 spid11 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information.
2002/04/09 07:57:36.04 ods Error : 17824, Severity: 10, State: 0
2002/04/09 07:57:36.04 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'CE_MILAGROS'.
2002/04/09 07:57:36.04 ods OS Error : 232,
2002/04/09 07:57:36.04 spid60 Error : 1608, Severity: 21, State: 2
2002/04/09 07:57:36.04 spid60 A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information.
The problems begin on 2002/04/09 at 7:53am. and it respond again at 7:57, there's no need to restart the service.
I will appreciate some help here.
View 1 Replies
View Related
