Create stored procedure error
-
Hello,
I've created a stored procedure in sql 2005 and when i compiled and create the stored procedure it returns an error like this:
Location: tmpilb.cpp:2565
Expression: fFalse
SPID: 55
Process ID: 392
Description: Attempt to access expired blob handle (3)
Msg 3624, Level 20, State 1, Procedure usp_QuickSearch, Line 803
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
But when I re-create or press F5 it says: command(s) completed successfully. Is there something wrong with our Database (SQL Server 2005)? it is already patched with service pack 2.
And when executed using the UI it returns an error like this:
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
A severe error occurred on the current command. The results, if any, should be discarded.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
A severe error occurred on the current command. The results, if any, should be discarded.
Thanks in advance.
-
Have you runa a DBCC CHECKDB against the database as it suggests?
Other than that, I'd want to see the code to try to figure out where the error lies.
You might be running into this:
https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=125497&wa=wsignin1.0&siteid=68
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
When I execute the dbcc checkdb command in sql query this is the result:
DBCC results for 'OSM_Etech_13_1'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
DBCC results for 'sys.sysrowsetcolumns'.
There are 747 rows in 7 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 95 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 106 rows in 1 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 747 rows in 8 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 95 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 106 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 14 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 120 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 175 rows in 4 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 1762 rows in 41 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
DBCC results for 'sys.sysxprops'.
There are 0 rows in 0 pages for object "sys.sysxprops".
DBCC results for 'sys.sysscalartypes'.
There are 27 rows in 1 pages for object "sys.sysscalartypes".
DBCC results for 'sys.systypedsubobjs'.
There are 0 rows in 0 pages for object "sys.systypedsubobjs".
DBCC results for 'sys.sysidxstats'.
There are 199 rows in 5 pages for object "sys.sysidxstats".
DBCC results for 'sys.sysiscols'.
There are 296 rows in 2 pages for object "sys.sysiscols".
DBCC results for 'sys.sysbinobjs'.
There are 23 rows in 1 pages for object "sys.sysbinobjs".
DBCC results for 'sys.sysobjvalues'.
There are 292 rows in 94 pages for object "sys.sysobjvalues".
DBCC results for 'sys.sysclsobjs'.
There are 14 rows in 1 pages for object "sys.sysclsobjs".
DBCC results for 'sys.sysrowsetrefs'.
There are 0 rows in 0 pages for object "sys.sysrowsetrefs".
DBCC results for 'sys.sysremsvcbinds'.
There are 0 rows in 0 pages for object "sys.sysremsvcbinds".
DBCC results for 'sys.sysxmitqueue'.
There are 0 rows in 0 pages for object "sys.sysxmitqueue".
DBCC results for 'sys.sysrts'.
There are 1 rows in 1 pages for object "sys.sysrts".
DBCC results for 'sys.sysconvgroup'.
There are 0 rows in 0 pages for object "sys.sysconvgroup".
DBCC results for 'sys.sysdesend'.
There are 0 rows in 0 pages for object "sys.sysdesend".
DBCC results for 'sys.sysdercv'.
There are 0 rows in 0 pages for object "sys.sysdercv".
DBCC results for 'sys.syssingleobjrefs'.
There are 133 rows in 1 pages for object "sys.syssingleobjrefs".
DBCC results for 'sys.sysmultiobjrefs'.
There are 852 rows in 8 pages for object "sys.sysmultiobjrefs".
DBCC results for 'sys.sysdbfiles'.
There are 2 rows in 1 pages for object "sys.sysdbfiles".
DBCC results for 'sys.sysguidrefs'.
There are 0 rows in 0 pages for object "sys.sysguidrefs".
DBCC results for 'sys.sysqnames'.
There are 91 rows in 1 pages for object "sys.sysqnames".
DBCC results for 'sys.sysxmlcomponent'.
There are 93 rows in 1 pages for object "sys.sysxmlcomponent".
DBCC results for 'sys.sysxmlfacet'.
There are 97 rows in 1 pages for object "sys.sysxmlfacet".
DBCC results for 'sys.sysxmlplacement'.
There are 17 rows in 1 pages for object "sys.sysxmlplacement".
DBCC results for 'sys.sysobjkeycrypts'.
There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".
DBCC results for 'sys.sysasymkeys'.
There are 0 rows in 0 pages for object "sys.sysasymkeys".
DBCC results for 'sys.syssqlguides'.
There are 0 rows in 0 pages for object "sys.syssqlguides".
DBCC results for 'sys.sysbinsubobjs'.
There are 0 rows in 0 pages for object "sys.sysbinsubobjs".
DBCC results for 'BudgetComments'.
There are 0 rows in 0 pages for object "BudgetComments".
DBCC results for 'budgetfinalize'.
There are 0 rows in 0 pages for object "budgetfinalize".
DBCC results for 'BudgetTrans'.
There are 0 rows in 0 pages for object "BudgetTrans".
DBCC results for 'StopLight'.
There are 0 rows in 0 pages for object "StopLight".
DBCC results for 'tbl_rptTrialBalanceDefault'.
There are 0 rows in 0 pages for object "tbl_rptTrialBalanceDefault".
DBCC results for 'currency'.
There are 0 rows in 0 pages for object "currency".
DBCC results for 'DimItemAccess'.
There are 104 rows in 1 pages for object "DimItemAccess".
DBCC results for 'chartofaccounts'.
There are 624 rows in 11 pages for object "chartofaccounts".
DBCC results for 'Dimensions'.
There are 1339 rows in 23 pages for object "Dimensions".
DBCC results for 'Lti'.
There are 29 rows in 1 pages for object "Lti".
DBCC results for 'ReportLayoutDetail'.
There are 828 rows in 3 pages for object "ReportLayoutDetail".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'SummaryTransBudget'.
There are 1250 rows in 21 pages for object "SummaryTransBudget".
DBCC results for 'Trans'.
There are 3708 rows in 112 pages for object "Trans".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'TransSup'.
There are 1136 rows in 38 pages for object "TransSup".
DBCC results for 'VesselInfo'.
There are 6 rows in 1 pages for object "VesselInfo".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'ExchangeRate'.
There are 0 rows in 0 pages for object "ExchangeRate".
DBCC results for 'ReportDefinition'.
There are 154 rows in 2 pages for object "ReportDefinition".
CHECKDB found 0 allocation errors and 0 consistency errors in database 'OSM_Etech_13_1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
This is the result of the execution of my sp when changes the parameters made to the stored procedure:
Location: tmpilb.cpp:2565
Expression: fFalse
SPID: 53
Process ID: 392
Description: Attempt to access expired blob handle (3)
Msg 3624, Level 20, State 1, Procedure usp_QuickSearch, Line 558
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The stored procedure that I created is :
IF
OBJECT_ID ('dbo.usp_QuickSearch', 'P') IS NOT NULL
DROP PROCEDURE dbo.usp_QuickSearch
GO
/*---------------------------------------------------------------------------------
Description : Quick Search
Dependency : udf_TransactionPerRange2, udf_GetDataVoucher -- User Defined Functions
reportDefinition,trans,Dimensions,VesselInfo -- Tables
Parameters :
@dim15 VARCHAR(2500) -- Client
@dim14 VARCHAR(2500) -- Vessel Group
@dim13 VARCHAR(2500) -- Vessels
@CompanyID INT -- Company
@fromyear INT -- From Year
@toyear INT -- To Year
@frommonth INT -- From Year
@tomonth int -- To Month
@report_layoutid INT -- Report Layout ID
@BaseLanguage VARCHAR(5) -- Language
@QuickSearchValue VARCHAR(200) -- Quick Search Value
SAMPLE Parameters:
SET @companyid = 5
SET @fromyear = 2006
SET @frommonth = 9
SET @toYear = 2006
SET @tomonth = 9
SET @dim15 = '355|'
SET @dim14 = '356|'
SET @dim13 = '361|358|357|359|360|362|'
SET @report_layoutid = 22
SET @BaseLanguage = 'ENG'
SET @QuickSearchValue = ''
created by : MCV - 2006-09-13
change History : MCV -2006-09-18 --add three columns such as dim15,dim14,dim13
JMT -2006-09-20 -- Remove DIM 15 and 14 for displaying and search items
JMT -2006-09-25 -- Added dynamic columns dim 1 to 12
MCV -2006-09-25 -- Added temp table for voucher and change the whole queries to add dynamic columns dim 1 to 12.(3:00PM - 6:15PM)
JMT -2006-10-09 -- Change way #Voucher to return no voucher
JMT -2006-10-09 -- ADDED SUP Trans on Result and Search Criteria
MCV -2006-10-17 -- Tuning up the and change the specific search into like search.
MCV -2006-10-25 -- Add VoNoCtr in Query Search.
JMT -2006-11-09 -- ADDED SUP Trans Order TXT on Result and Search Criteria
JMT -2006-11-10 -- ADDED Quick search value space trim
JMT -2006-11-20 -- ADDED Cur AM as a display and searchable value
MCV -2007-03-12 -- Delete Dim14,Dim15 for the segreggation of DB
----------------------------------------------------------------------------------------*/
CREATE
PROCEDURE dbo.usp_QuickSearch
--DECLARE
@companyid
INT,
@fromyear
INT = null,
@frommonth
INT = null,
@toYear
INT = null,
@tomonth
INT = null,
@dim13
VARCHAR(max),
@report_layoutid
INT,
@BaseLanguage
VARCHAR(5),
@QuickSearchValue
NVARCHAR(200)
AS
--SET @companyid = 5
--SET @fromyear = 2006
--SET @frommonth = 9
--SET @toYear = 2006
--SET @tomonth = 9
------SET @dim15 = '355|'
------SET @dim14 = '356|'
--SET @dim13 = '361|358|357|359|360|362|'
--SET @report_layoutid = 22
--SET @BaseLanguage = 'ENG'
--SET @QuickSearchValue = 'EK*'
SET
NOCOUNT ON
--CREATE TABLE #Temp_Logs
--(DbId int
--,FileId int
--,CurrentSize int
--,MinimumSize int
--,UsedPages int
--,EstimatedPages int
--)
--
--INSERT INTO #Temp_logs
EXEC
usp_TruncateLogs
--Clean Quicksearch Value
SET
@QuickSearchValue = (SELECT RTRIM(@QuickSearchValue))
SET
@QuickSearchValue = (SELECT LTRIM(@QuickSearchValue))
DECLARE
@chvwildcard varchar(50)
,@chvChar varchar(20)
,@chvCharSearch varchar(50)
,@intCount int
,@intCtr int
,@intCountChar int
,@intCheck int
,@intCountVoucher int
,@VoNo nvarchar(20)
,@CompelloClient nvarchar(100)
,@cl_id varchar(10)
,@sql nvarchar(MAX)
,@lenCount int
,@ShowDim1 bit
,@ShowDim2 bit
,@ShowDim3 Bit
,@showDim4 bit
,@showDim5 bit
,@showDim6 bit
,@showDim7 bit
,@showDim8 bit
,@showDim9 bit
,@showDim10 bit
,@showdim11 bit
,@showdim12 bit
CREATE
TABLE #Dim
(
companyid int
,
accountid int
,
SupName nvarchar(250)
,
vono nvarchar(10)
,
dim1 int
,
dim2 int
,
dim3 int
,
dim4 int
,
dim5 int
,
dim6 int
,
dim7 int
,
dim8 int
,
dim9 int ,dim10 int
,
dim11 int
,
dim12 int
--,dim15 int
--,dim14 int
,
dim13 int
,
TrType nvarchar(10)
,
OrderNo nvarchar(80)
)
CREATE
TABLE #Dim_Final
(
companyid int --1
,
accountid int --2
,
SupName nvarchar(250)
,
vono nvarchar(10) --3
,
dim1_desc nvarchar(200) --4
,
dim2_desc nvarchar(200) --5
,
dim3_desc nvarchar(200) --6
,
dim4_desc nvarchar(200) --7
,
dim5_desc nvarchar(200) --8
,
dim6_desc nvarchar(200) --9
,
dim7_desc nvarchar(200) --10
,
dim8_desc nvarchar(200) --11
,
dim9_desc nvarchar(200) --12
,
dim10_desc nvarchar(200) --13
,
dim11_desc nvarchar(200) --14
,
dim12_desc nvarchar(200) --15
--,dim15_desc nvarchar(200)
--,dim14_desc nvarchar(200)
,
dim13_desc nvarchar(200)
,
TrType nvarchar(10)
,
OrderNo nvarchar(80)
)
CREATE
TABLE #Voucher
(
vonoctr int
,
CompelloClient nvarchar(100)
,
ClientAcct nvarchar(610)
,
AcAm decimal(38,17)
,
CurAm decimal(38,17)
,
TrYear smallint
,
TrMonth tinyint
,
VoNo nvarchar(20)
,
TrDate varchar(12)
,
CompelloURL nvarchar(200)
,
Cur nvarchar(10)
,
trtext nvarchar(200)
,
companyid int
,
ClientAcctsID int
,
dim13 int
,
TrType nvarchar(10)
,
OrderNo nvarchar(80)
)
CREATE
TABLE #SearchCriteria
(TransID int IDENTITY (1, 1) NOT NULL
,VonoCtr int
,CompelloClient nvarchar(100)
,ClientAcct nvarchar(610)
,AcAm decimal(38,17)
,CurAm decimal(38,17)
,TrYear smallint
,TrMonth tinyint
,VoNo nvarchar(20)
,TrDate varchar(12)
,CompelloURL nvarchar(200)
,Cur nvarchar(10)
,trtext nvarchar(200)
,dim1_desc nvarchar(200)
,dim2_desc nvarchar(200)
,dim3_desc nvarchar(200)
,dim4_desc nvarchar(200)
,dim5_desc nvarchar(200)
,dim6_desc nvarchar(200)
,dim7_desc nvarchar(200)
,dim8_desc nvarchar(200)
,dim9_desc nvarchar(200)
,dim10_desc nvarchar(200)
,dim11_desc nvarchar(200)
,dim12_desc nvarchar(200)
,dim13_desc nvarchar(200)
,TrType nvarchar(10)
,OrderNo nvarchar(80)
)
SET
@intCount = 0
SET
@chvwildcard = @QuickSearchValue
SELECT
@ShowDim1 = Dim1Show
,@ShowDim2 = Dim2Show
,@ShowDim3 = Dim3Show
,@showDim4 = Dim4Show
,@showdim5 =Dim5Show
,@showdim6 =Dim6Show
,@showdim7 =Dim7Show
,@showdim8 =Dim8Show
,@showdim9 =Dim9Show
,@showdim10 =Dim10Show
,@showdim11 =Dim11Show
,@showdim12 =Dim12Show
From
OSM_Etech_Main.dbo.Company where CompanyID=@companyid
SET
@intCtr =1
SET
@lenCount = LEN(@chvwildcard)
SET
@chvCharSearch = ''
SET
@intCountChar = LEN(@chvwildcard)
WHILE
@lenCount > 0
BEGIN
SET @chvChar = SUBSTRING(@chvwildcard,@lenCount,@intCtr)
SET @intCheck = (SELECT PATINDEX('[*]',@chvChar))
IF (@intCheck > 0)
SET @intCount = @intCount + 1
SET @lenCount = @lenCount - 1
END
SET
@intCheck =0
SET
@chvChar =''
print
@intCount
INSERT
INTO #Dim
SELECT
t1.companyid,
ClientAcctsID
,
null,
vono
,
t1
.dim1,
t1
.dim2,
t1
.dim3,
t1
.dim4,
t1
.dim5,
t1
.dim6,
t1
.dim7,
t1
.dim8,
t1
.dim9,
t1
.dim10,
t1
.dim11,
t1
.dim12,
-- t1.dim15,
-- t1.dim14,
t1
.dim13
,
'GL',null
FROM
trans t1
where
@companyid = companyid
and
(@dim13='all' or charindex('|'+cast (t1.dim13 as varchar(2500))+'|','|'+@dim13 )>0)
INSERT
INTO #Dim
SELECT
sup.companyid,
sup
.Supno,
sup
.SupNM,
vono
,
null as dim1,
null as dim2,
null as dim3,
null as dim4,
null as dim5,
null as dim6,
null as dim7,
null as dim8,
null as dim9,
null as dim10,
null as dim11,
null as dim12,
-- t1.dim15,
-- t1.dim14,
sup
.dim13
,
'SUP',sup.OrderTxt
FROM
transsup sup
where
@companyid = companyid
and
(@dim13='all' or charindex('|'+cast (sup.dim13 as varchar(2500))+'|','|'+@dim13 )>0)
INSERT
INTO #Dim_Final
(companyid
,accountid
,SupName
,vono
,dim1_desc
,dim2_desc
,dim3_desc
,dim4_desc
,dim5_desc
,dim6_desc
,dim7_desc
,dim8_desc
,dim9_desc
,dim10_desc
,dim11_desc
,dim12_desc
-- ,dim15_desc
-- ,dim14_desc
,dim13_desc,trtype,orderno)
SELECT
d1.companyid,
d1
.accountid,
d1
.SupName,
d1
.vono,
(CASE WHEN d1.dim1 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim1)
ELSE
''
END) AS dim1_desc,
(CASE WHEN d1.dim2 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim2)
ELSE
''
END) AS dim2_desc,
(CASE WHEN d1.dim3 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim3)
ELSE
''
END) AS dim3_desc,
(CASE WHEN d1.dim4 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim4)
ELSE
''
END) AS dim4_desc,
(CASE WHEN d1.dim5 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim5)
ELSE
''
END) AS dim5_desc,
(CASE WHEN d1.dim6 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim6)
ELSE
''
END) AS dim6_desc,
(CASE WHEN d1.dim7 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim7)
ELSE
''
END) AS dim7_desc,
(CASE WHEN d1.dim8 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim8)
ELSE
''
END) AS dim8_desc,
(CASE WHEN d1.dim9 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim9)
ELSE
''
END) AS dim9_desc,
(CASE WHEN d1.dim10 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim10)
ELSE
''
END) AS dim10_desc,
(CASE WHEN d1.dim11 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim11)
ELSE
''
END) AS dim11_desc,
(CASE WHEN d1.dim12 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim12)
ELSE
''
END) AS dim12_desc,
-- (CASE WHEN d1.dim15 IS NOT NULL THEN
-- (SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim15)
-- ELSE
-- ''
-- END) AS dim15_desc,
-- (CASE WHEN d1.dim14 IS NOT NULL THEN
-- (SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim14)
-- ELSE
-- ''
-- END) AS dim14_desc,
(CASE WHEN d1.dim13 IS NOT NULL THEN
(SELECT d3.[desc] FROM dimensions d3 WHERE d3.dimid = d1.dim13)
ELSE
''
END) AS dim13_desc ,
TrType
,OrderNo
FROM
#dim d1
INSERT
INTO #Voucher
select
distinct
CASE
WHEN t1.vonoctr IS NULL THEN 0
ELSE
t1.vonoctr END
,
VI.CompelloClient
-- , vo.cl_name
-- ,vo.vo_stamp_no
,Cast(c.ClientAccountNo as varchar(50)) + ' - ' + ISNULL(Cast(c.accountname as varchar(300)), '') as ClientAcct
,CAST(ISNULL(t1.acam,0.0) as varchar(20)) as AcAm
,CAST(ISNULL(t1.curam,0.0) as varchar(20)) as CurAm
, t1.TRYear
,t1.TrMonth
,t1.VoNo
, Right('00'+Cast(Day(t1.TrDate) as Varchar(5)),2)+'-'+Right('00'+Cast(MONTH(t1.TrDate) as Varchar(5)),2)+'-'+Cast(YEAR(t1.TrDate) as Varchar(4)) as TrDate
, VI.CompelloURL
,t1.Cur
,t1.trtext
,t1.companyid
,t1.ClientAcctsID
,t1.dim13
,'GL',null
from
trans t1
INNER JOIN udf_chartofaccounts(@companyid,@report_layoutid,@BaseLanguage) c on c.accountID = t1.clientacctsid
and c.dim13=t1.dim13
and (@dim13='all' or charindex('|'+cast (t1.dim13 as varchar(2500))+'|','|'+ @dim13 )>0)
INNER
JOIN reportDefinition grp on c.accountgroupid=grp.accountgroupid
INNER
JOIN Dimensions d on c.dim13=d.dimid
INNER
JOIN VesselInfo VI on d.DimID=VI.DimID
--LEFT join vwVoNumber vo
--on vo.vo_stamp_no =t1.vono
-- and vo.cl_name=VI.CompelloClient
INSERT
INTO #Voucher
select
distinct
CASE
WHEN sup.vonoctr IS NULL THEN 0
ELSE
sup.vonoctr END
,
VI.CompelloClient
--, vo.cl_name
--,vo.vo_stamp_no
,Cast(sup.SupNo as varchar(50)) + ' - ' + ISNULL(Cast(sup.SUpNM as varchar(300)), '') as ClientAcct
,CAST(ISNULL(sup.acam,0.0) as varchar(20)) as AcAm
,CAST(ISNULL(sup.curam,0.0) as varchar(20)) as CurAm
, sup.TRYear
,sup.TrMonth
,sup.VoNo
, Right('00'+Cast(Day(sup.TrDate) as Varchar(5)),2)+'-'+Right('00'+Cast(MONTH(sup.TrDate) as Varchar(5)),2)+'-'+Cast(YEAR(sup.TrDate) as Varchar(4)) as TrDate
, VI.CompelloURL
,sup.Cur
,sup.trtext
,sup.companyid
,sup.SupNo
,sup.dim13
,'SUP',sup.OrderTxt
from
transsup sup
INNER
JOIN Dimensions d on sup.dim13=d.dimid and (@dim13='all' or charindex('|'+cast (sup.dim13 as varchar(2500))+'|','|'+ @dim13 )>0)
INNER
JOIN VesselInfo VI on d.DimID=VI.DimID
--LEFT join vwVoNumber vo
--on vo.vo_stamp_no =sup.ref
-- and vo.cl_name=VI.CompelloClient
CREATE
NONCLUSTERED INDEX idx_Temp1
ON #voucher(vono,companyid,ClientAcctsID,TrDate)
CREATE
NONCLUSTERED INDEX idx_temp2
ON #Dim_Final(companyid,accountid,vono)
IF
(@intCount=0 AND @QuickSearchValue <> '')
BEGIN
SET @sql = 'INSERT INTO #SearchCriteria ' +
'SELECT DISTINCT vo.vonoctr ' +
',vo.CompelloClient ' +
--, '(CASE WHEN vo.cl_id IS NOT NULL THEN 1 ELSE 0 END) as VoCtr ' +
',vo.ClientAcct ' +
',vo.AcAm ' +
',vo.CurAm ' +
',vo.TrYear ' +
',vo.TrMonth ' +
',vo.VoNo ' +
',vo.TrDate ' +
',vo.CompelloURL ' +
',vo.Cur ' +
',vo.trtext ' +
',d1.dim1_desc ' +
',d1.dim2_desc ' +
',d1.dim3_desc ' +
',d1.dim4_desc ' +
',d1.dim5_desc ' +
',d1.dim6_desc ' +
',d1.dim7_desc ' +
',d1.dim8_desc ' +
',d1.dim9_desc ' +
',d1.dim10_desc ' +
',d1.dim11_desc ' +
',d1.dim12_desc ' +
-- d1.dim15_desc,
-- d1.dim14_desc,
',d1.dim13_desc ' +
',vo.TrType,vo.OrderNo ' +
'FROM #VOUCHER vo ' +
'INNER JOIN #Dim_Final d1 ON d1.companyid = vo.companyid ' +
'AND d1.accountid = vo.ClientAcctsID ' +
'AND d1.vono = vo.vono ' +
'WHERE vo.companyid = ' + LTRIM(RTRIM(CAST(@companyid as char))) +
'and ('+ '''' + @dim13 + '''' + '=' + '''' + 'all' + '''' + 'or charindex(' + '''' + '|'+ '''' + '+ cast (vo.dim13 as varchar(2500))+ ' + '''' + '|' + '''' + ',' + '''' + '|' + '''' + '+' + '''' + @dim13 + '''' + ')>0) ' +
'and (vo.acam is not null and vo.acam <> 0) ' +
'and ((PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' + '''' + ',vo.cur )>0)' +
' OR (PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' +'''' + ',ltrim(rtrim(vo.trtext)) )>0)' +
' OR (PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' + '''' + ',vo.TrDate)>0)' +
' OR (PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' + '''' + ',vo.VoNo )>0)' +
' OR (PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' + '''' + ',CAST(vo.trYear as char) )>0)' +
' OR (PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' + '''' + ',CAST(ISNULL(vo.acam,0.0) as varchar(100)))>0)' +
' OR (PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' + '''' + ',CAST(ISNULL(vo.curam,0.0) as varchar(100)))>0)' +
' OR (PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' + '''' + ',vo.ClientAcct )>0)' +
' OR (PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' + '''' + ',vo.TrType)>0) ' +
' OR (PATINDEX(' + '''' + '%' + @QuickSearchValue + '%' + '''' + ',vo.OrderNo)>0) '
IF @ShowDim1 =1
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim1_desc )>0) '
IF @ShowDim2 =1
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim2_desc )>0) '
IF @ShowDim3 =1
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim3_desc )>0) '
IF @showdim4 =1
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim4_desc )>0) '
IF @showdim5 =1
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim5_desc )>0) '
IF @showdim6 =1
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim6_desc )>0) '
IF @showdim7 =1
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim7_desc )>0) '
IF @showdim8 =1
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim8_desc )>0) '
IF @showdim9 =1
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim9_desc )>0) '
IF @showdim10 =10
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim10_desc )>0) '
IF @showDim11 =11
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim11_desc )>0) '
IF @showDim12 =12
SET @sql = @sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim12_desc )>0 '
SET @sql =@sql + ' OR (PATINDEX(' + '''' +'%'+ @QuickSearchValue + '%' +'''' + ',d1.dim13_desc )>0))'
EXEC sp_executesql @sql
SELECT * FROM #SearchCriteria
END
ELSE
IF (@intCount = 0 AND @QuickSearchValue = '')
BEGIN
INSERT INTO #SearchCriteria
SELECT DISTINCT vo.vonoctr,
vo
.CompelloClient,
-- (CASE WHEN vo.cl_id IS NOT NULL THEN 1 ELSE 0 END) as VoCtr,
vo
.ClientAcct ,
vo
.AcAm,
vo
.CurAm,
vo
.TrYear,
vo
.TrMonth,
vo
.VoNo,
vo
.TrDate,
vo
.CompelloURL
,vo.Cur
,vo.trtext
,d1.dim1_desc
,d1.dim2_desc
,d1.dim3_desc
,d1.dim4_desc
,d1.dim5_desc
,d1.dim6_desc
,d1.dim7_desc
,d1.dim8_desc
,d1.dim9_desc
,d1.dim10_desc
,d1.dim11_desc
,d1.dim12_desc
,d1.dim13_desc
,vo.TrType,vo.OrderNo
FROM #VOUCHER vo
INNER JOIN #Dim_Final d1 ON d1.companyid = vo.companyid
and d1.accountid =vo.ClientAcctsID
and d1.vono = vo.vono
WHERE @companyid = vo.companyid
and (@dim13='all' or charindex('|'+cast (vo.dim13 as varchar(2500))+'|','|'+@dim13 )>0)
and (vo.acam is not null and vo.acam <> 0)
SELECT * FROM #SearchCriteria
END
ELSE
IF (@intCount>1)
BEGIN
SET @chvCharSearch = ''
SET @intCountChar = LEN(@chvwildcard)
WHILE @intCountChar > 0
BEGIN
SET @chvChar = SUBSTRING(@chvwildcard,@intCountChar,@intCtr)
SET @intCheck = (SELECT PATINDEX('[*]',@chvChar))
IF @intCheck = 0
BEGIN
IF @chvChar = '' OR @chvChar <> ''
SET @chvCharSearch = rtrim(ltrim(@chvChar)) + @chvCharSearch
END
SET @intCountChar = @intCountChar - 1
END
SET @chvCharSearch = '''' + '%' + rtrim(ltrim(@chvCharSearch)) + '%' + ''''
SET @sql = 'INSERT INTO #SearchCriteria ' +
'SELECT DISTINCT vo.vonoctr' +
',vo.CompelloClient ' +
-- '(CASE WHEN vo.cl_id IS NOT NULL THEN 1 ELSE 0 END) as VoCtr ' +
',vo.ClientAcct ' +
',vo.AcAm ' +
',vo.CurAm ' +
',vo.TrYear ' +
',vo.TrMonth ' +
',vo.VoNo ' +
',vo.TrDate ' +
',vo.CompelloURL ' +
',vo.Cur ' +
',vo.trtext ' +
',d1.dim1_desc ' +
',d1.dim2_desc ' +
',d1.dim3_desc ' +
',d1.dim4_desc ' +
',d1.dim5_desc ' +
',d1.dim6_desc ' +
',d1.dim7_desc ' +
',d1.dim8_desc ' +
',d1.dim9_desc ' +
',d1.dim10_desc ' +
',d1.dim11_desc '
+
',d1.dim12_desc ' +
',d1.dim13_desc ' +
',vo.TrType,vo.OrderNo ' +
'FROM #VOUCHER vo ' +
'INNER JOIN #Dim_Final d1 ON d1.companyid = vo.companyid ' +
'AND d1.accountid = vo.ClientAcctsID ' +
'AND d1.vono = vo.vono ' +
'WHERE vo.companyid = ' + LTRIM(RTRIM(CAST(@companyid as char))) +
'and ('+ '''' + @dim13 + '''' + '=' + '''' + 'all' + '''' + 'or charindex(' + '''' + '|'+ '''' + '+ cast (vo.dim13 as varchar(2500))+ ' + '''' + '|' + '''' + ',' + '''' + '|' + '''' + '+' + '''' + @dim13 + '''' + ')>0) ' +
'and (vo.acam is not null and vo.acam <> 0) AND ' +
'((PATINDEX(' + @chvCharSearch + ',CAST(ISNULL(vo.acam,0.0) as varchar))>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',CAST(ISNULL(vo.curam,0.0) as varchar))>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',CAST(vo.TrMonth as char))>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.VoNo)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.ClientAcct)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.TrDate)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',ltrim(rtrim(vo.trtext)))>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.cur)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',CAST(vo.TrYear as char))>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.TrType)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.OrderNo)>0) '
IF @ShowDim1 =1
SET @sql = @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim1_desc)>0)'
IF @ShowDim2 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim2_desc)>0) '
IF @showDim3 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim3_desc)>0) '
IF @showDim4 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim4_desc)>0) '
IF @showDim5 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim5_desc)>0) '
IF @showDim6 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim6_desc)>0) '
IF @showDim7 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim7_desc)>0) '
IF @showDim8 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim8_desc)>0) '
IF @showDim9 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim9_desc)>0) '
IF @showDim10 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim10_desc)>0) '
IF @showDim11=1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim11_desc)>0) '
IF @showDim12 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim12_desc)>0) '
SET @sql=@sql+ 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim13_desc)>0))'
--PRINT @sql
EXEC sp_executesql @sql
SELECT * FROM #SearchCriteria
END
ELSE
IF @intCount=1
BEGIN
PRINT @intCount
SET @chvCharSearch = ''
SET @intCountChar = LEN(@chvwildcard)
WHILE @intCountChar > 0
BEGIN
SET @chvChar = SUBSTRING(@chvwildcard,@intCountChar,@intCtr)
SET @intCheck = (SELECT PATINDEX('[*]',@chvChar))
IF @intCheck = 0
BEGIN
IF @chvChar = '' OR @chvChar <> ''
SET @chvCharSearch = rtrim(ltrim(@chvChar)) + @chvCharSearch
END
SET @intCountChar = @intCountChar - 1
END
SET @intCheck =0
SET @intCheck = (SELECT PATINDEX('%[*]%',@QuickSearchValue))
IF @intCheck = 1
SET @chvCharSearch = '''' + '%' + rtrim(ltrim(@chvCharSearch)) + ''''
ELSE IF @intCheck > 1
SET @chvCharSearch = '''' + rtrim(ltrim(@chvCharSearch)) + '%' + ''''
SET @sql = 'INSERT INTO #SearchCriteria ' +
'SELECT DISTINCT vo.vonoctr' +
',vo.CompelloClient ' +
-- '(CASE WHEN vo.cl_id IS NOT NULL THEN 1 ELSE 0 END) as VoCtr ' +
',vo.ClientAcct ' +
',vo.AcAm ' +
',vo.CurAm ' +
',vo.TrYear ' +
',vo.TrMonth ' +
',vo.VoNo ' +
',vo.TrDate ' +
',vo.CompelloURL ' +
',vo.Cur ' +
',vo.trtext ' +
',d1.dim1_desc ' +
',d1.dim2_desc ' +
',d1.dim3_desc ' +
',d1.dim4_desc ' +
',d1.dim5_desc ' +
',d1.dim6_desc ' +
',d1.dim7_desc ' +
',d1.dim8_desc ' +
',d1.dim9_desc ' +
',d1.dim10_desc ' +
',d1.dim11_desc ' +
',d1.dim12_desc ' +
',d1.dim13_desc ' +
',vo.TrType,vo.OrderNo ' +
'FROM #VOUCHER vo ' +
'INNER JOIN #Dim_Final d1 ON d1.companyid = vo.companyid ' +
'AND d1.accountid = vo.ClientAcctsID ' +
'AND d1.vono = vo.vono ' +
'WHERE vo.companyid = ' + LTRIM(RTRIM(CAST(@companyid as char))) +
' and ('+ '''' + @dim13 + '''' + '=' + '''' + 'all' + '''' + 'or charindex(' + '''' + '|'+ '''' + '+ cast (vo.dim13 as varchar(2500))+ ' + '''' + '|' + '''' + ',' + '''' + '|' + '''' + '+' + '''' + @dim13 + '''' + ')>0) ' +
'and (vo.acam is not null and vo.acam <> 0) AND ' +
'((PATINDEX(' + @chvCharSearch + ',CAST(ISNULL(vo.acam,0.0) as varchar))>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',CAST(ISNULL(vo.curam,0.0) as varchar))>0) OR ' +
'(PATINDEX('
+ @chvCharSearch + ',CAST(vo.TrMonth as char))>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.VoNo)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.ClientAcct)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.TrDate)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',ltrim(rtrim(vo.trtext)))>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.cur)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',CAST(vo.TrYear as char))>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.TrType)>0) OR ' +
'(PATINDEX(' + @chvCharSearch + ',vo.OrderNo)>0) '
IF @ShowDim1 =1
SET @sql = @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim1_desc)>0)'
IF @ShowDim2 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim2_desc)>0) '
IF @showDim3 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim3_desc)>0) '
IF @showDim4 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim4_desc)>0) '
IF @showDim5 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim5_desc)>0) '
IF @showDim6 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim6_desc)>0) '
IF @showDim7 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim7_desc)>0) '
IF @showDim8 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim8_desc)>0) '
IF @showDim9 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim9_desc)>0) '
IF @showDim10 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim10_desc)>0) '
IF @showDim11=1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim11_desc)>0) '
IF @showDim12 =1
SET @sql= @sql + 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim12_desc)>0) '
SET @sql=@sql+ 'OR (PATINDEX(' + @chvCharSearch + ',d1.dim13_desc)>0))'
Print
@sql
EXEC sp_executesql @sql
SELECT * FROM #SearchCriteria
END
DROP
TABLE #SearchCriteria
DROP
TABLE #Dim
DROP
TABLE #Dim_Final
DROP
TABLE #voucher
--DROP TABLE #Temp_Logs
GO
-
It looks like you may be hitting a bug. I found this kb article that seems to describe your situation:
http://support.microsoft.com/kb/935356
"In SQL Server 2005, assertion error 3624 may occur. The error message for assertion error 3624 is as follows: "Attempt to access expired blob handle (3)."
When you run a query that uses the FOR XML clause together with the TYPE directive, the query may fail:"
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
But I did not used any query "FOR XML". It seems that there is something wrong with SQL 2005. It sucks me a lot, because when I restore a db backup in our production environment, same error occured when executing the stored procedure in production. But I have to log this kind of error in Backup forum. Anyway...Thanks a lot.
-
You really ought to report this to MS.
---------------------------------------
elsasoft.org -
As suggested open the case with PSS, generally you won't be charged if it is a bug...
MohammedU
Microsoft SQL Server MVP -
Hello Mohammed,
Can you give me the link to PSS?
Thanks a lot!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply