Server: Msg 170, Level 15, State 1, Line 1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Server: Msg 170, Level 15, State 1, Line 1

Author

Topic

baaul
Starting Member

16 Posts

Posted - 2006-10-31 : 11:30:36

Hi,
I'm getting a server msg when I execute the Sproc. My Sproc returns a dataset in the "Grids" window but I find Server msg in the "messages" window. I'm running this in Query Analyzer SQL Server 2000. I'd appreciate any help....can't seem to locate the issue. Thanks! ------>

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '#final'.

=====================================

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ReportBapByKeyField]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ReportBapByKeyField]
GO

--EXEC ReportBapByKeyField '001704', '0','-1','-1:-1','-1','-1:-1:-1','-1','-1',null
-----------------------------------------------------------------------------------------------------------------------
-- Generated By:
-- Template: N/A
-- Template Last Updated: N/A
-- Procedure Name:
-- Date Generated: N/A
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[ReportBapByKeyField]
@orgID varchar(2500),
@quarterId INT,
@BusinessUnitOwners varchar(100),
@regulatorRegulations varchar(100),
@Geographies varchar(100),
@ProdClassNameActivities varchar(100),
@VendorRelationships varchar(100),
@Dependencies varchar(100),
@RABUOrgID varchar(20)

AS

Create Table #tmp (
rowCnt int,
sort_id int default 0,
grp_Code int default 1,
business_activity_profile_id INT,
business_activity_product_id int default 0,
value1_id int default 0,
value1_tx varchar(500) default NULL,
value2_id int default 0,
value2_tx varchar(500) default NULL,
value3_id int default 0,
value3_tx varchar(500) default NULL
)
Create Table #filteredBaps(
business_activity_profile_id int,
org_id varchar(20)

)

Create Table #rabuList(
recID int IDENTITY(1,1),
business_activity_profile_id int
)

Create Table #tmpTable(
rowCnt int IDENTITY(1,1),
sort_id int default 0,
grp_Code int default 1,
business_activity_profile_id INT,
business_activity_product_id int default 0,
value1_id int default 0,
value1_tx varchar(500) default NULL,
value2_id int default 0,
value2_tx varchar(500) default NULL,
value3_id int default 0,
value3_tx varchar(500)
)
Create Table #final (
sort_id INT,
grp_code INT,
business_activity_profile_id INT,
business_activity_product_id int default 0,
business_activity_ds varchar(500), -- added COLUMN values
org_nm varchar(60), -- added COLUMN values
regulator varchar(100) NULL,
regulation varchar(250) NULL,
geography varchar(120) NULL,
key_risk_indicator_id INT, -- added COLUMN value
product_class_tx varchar(50),
product_nm varchar(50),
activity_tx varchar(50),
vendor_nm varchar(250) NULL,
application_nm varchar(250) NULL, -- added COLUMN value
dependency_code_value varchar(120),
modified_dt varchar(50) -- added COLUMN values

)
Create Table #addRowColumns (
grp_code int,
sort_id INT default 0,
rowCnt int default 0,
business_activity_profile_id INT,
business_activity_product_id int default 0,
useValue_1 varchar(500),
useValue_2 varchar(500),
useValue_3 varchar(500)
)

Declare @iBap int, @iGrpCnt int
Declare @iCount int, @iRow int
Declare @iAdd int, @currRowCnt int
Declare @sSelect varchar(2500)

Begin
EXEC dbo.ReportKeyFieldRtn @orgID, @quarterId, @BusinessUnitOwners, @regulatorRegulations, @Geographies,
@ProdClassNameActivities,@VendorRelationships, @Dependencies, @RABU=@RABUOrgID

INSERT INTO #filteredBaps(org_id)
Select ra.org_id from dbo.ReportRabuOrgIDs(@orgID, @quarterID) ra JOIN business_activity_profile bap ON
ra.org_id=bap.org_id AND bap.is_draft_in=0

INSERT INTO #rabuList
Select DISTINCT business_activity_profile_id From #tmp ORDER BY business_activity_profile_id

IF @quarterID=0
Begin
UPDATE #filteredBaps Set business_activity_profile_id = bap.business_activity_profile_id
from #filteredBaps f JOIN business_activity_profile bap ON f.org_id=bap.org_id
Where bap.is_draft_in=0 and bap.business_activity_profile_id IN (Select r.business_activity_profile_id FROM #rabuList r)
End
Else
Begin
UPDATE #filteredBaps Set business_activity_profile_id = bap.business_activity_profile_id
from #filteredBaps f JOIN business_activity_profile_th bap ON f.org_id=bap.org_id
Where bap.time_dimension_id=@quarterID
End

Select @iCount=(Select COUNT(business_activity_profile_id) as bapId FROM #filteredBaps)
While @iCount<>0
Begin

Select @iBap=(SELECT MIN(t.business_activity_profile_id)
FROM #rabuList t Where t.business_activity_profile_id
NOT IN (Select f.business_activity_profile_id from #final f))

INSERT INTO #tmpTable
Select 0 as sort_id, t.grp_Code, t.business_activity_profile_id,0 as business_activity_product_id,
t.value1_id, t.value1_tx, t.value2_id, t.value2_tx, t.value3_id, t.value3_tx
from #tmp t
Where t.business_activity_profile_id=@iBap

--Count from 1 to [n] for each BAP/RABU
UPDATE #tmpTable Set sort_id=(select COUNT(1)+1 From #tmpTable tt
Where tt.rowCnt<#tmpTable.rowCnt and tt.grp_code=#tmpTable.grp_code)

--how many rows to insert for this Bap; Represents the max number of records for this BAP/RABU and is the 'control' - counts for all other Group Codes are
--compared to this value - If the count is less, then create repeating records for the difference
Select @iRow=(Select Max(sort_id) from #tmpTable)

--Column with the greatest # of rows; if more than one Group Code has an equal # of records, choose the lowest Group code to start
Select @iGrpCnt = (Select MIN(grp_code) from #tmpTable Where
sort_id=@iRow)

Begin
--INSERT Rows for the value with the greatest # of rows - creates the total number of rows for this RABU; all other group codes are less
Select @sSelect='INSERT INTO #final(sort_id, grp_code, business_activity_profile_id,
business_activity_product_id, org_nm, business_activity_ds, modified_dt,
regulator,regulation, geography, key_risk_indicator_id,product_class_tx,
product_nm, activity_tx, vendor_nm, application_nm,dependency_code_value)
Select sort_id, ' + CAST(@iGrpCnt as varchar(4)) + ' as grp_code, business_activity_profile_id,
0 as business_activity_product_id, '
Select @sSelect = CASE @iGrpCnt
WHEN 1 Then @sSelect + 'value1_tx as org_nm, value2_tx as business_activity_ds,
value3_tx as modified_dt, NULL as regulator, NULL as regulation,
NULL as geography, NULL as key_risk_indicator_id,
NULL as product_class_tx,NULL as product_nm, NULL as activity_tx,
NULL as vendor_nm, NULL as application_nm, NULL as dependency_code_value'
When 2 Then @sSelect + 'NULL as org_nm, NULL as business_activity_ds, NULL as modified_dt,
value1_tx as regulator, value2_tx as regulation,
NULL as geography, NULL as key_risk_indicator_id,NULL as product_class_tx,
NULL as product_nm, NULL as activity_tx, NULL as vendor_nm,
NULL as application_nm,NULL as dependency_code_value '
WHEN 3 THEN @sSelect + 'NULL as org_nm, NULL as business_activity_ds, NULL as modified_dt,
NULL as regulator, NULL as regulation, value1_tx as geography,
NULL as key_risk_indicator_id, NULL as product_class_tx,
NULL as product_nm, NULL as activity_tx, NULL as vendor_nm,
NULL as application_nm,NULL as dependency_code_value'
WHEN 4 THEN @sSelect + 'NULL as org_nm, NULL as business_activity_ds, NULL as modified_dt,
NULL as regulator, NULL as regulation, NULL as geography,NULL as key_risk_indicator_id,
value1_tx as product_class_tx, value2_tx as product_nm, value3_tx as activity_tx,
NULL as vendor_nm, NULL as application_nm,NULL as dependency_code_value'
WHEN 5 Then @sSelect + 'NULL as org_nm, NULL as business_activity_ds, NULL as modified_dt,
NULL as regulator, NULL as regulation, NULL as geography,NULL as key_risk_indicator_id,
NULL as product_class_tx, NULL as product_nm, NULL as activity_tx,
value1_tx as vendor_nm, NULL as application_nm,NULL as dependency_code_value'

WHEN 6 Then @sSelect + 'NULL as org_nm, NULL as business_activity_ds, NULL as modified_dt,
NULL as regulator, NULL as regulation, NULL as geography,NULL as key_risk_indicator_id,
NULL as product_class_tx, NULL as product_nm, NULL as activity_tx,
NULL as vendor_nm, NULL as application_nm,value1_tx as dependency_code_value'
WHEN 7 Then @sSelect + 'NULL as org_nm, NULL as business_activity_ds, NULL as modified_dt,
NULL as regulator, NULL as regulation, NULL as geography,NULL as key_risk_indicator_id,
NULL as product_class_tx, NULL as product_nm, NULL as activity_tx,
NULL as vendor_nm, value1_tx as application_nm, NULL as dependency_code_value'

WHEN 8 Then @sSelect + 'NULL as org_nm, NULL as business_activity_ds, NULL as modified_dt,
NULL as regulator, NULL as regulation, NULL as geography,value1_tx as key_risk_indicator_id,
NULL as product_class_tx, NULL as product_nm, NULL as activity_tx,
NULL as vendor_nm, NULL as application_nm, NULL as dependency_code_value'

ELSE @sSelect + 'NULL as org_nm, NULL as business_activity_ds, NULL as modified_dt,
NULL as regulator, NULL as regulation, NULL as geography,NULL as key_risk_indicator_id,
NULL as product_class_tx, NULL as product_nm, NULL as activity_tx,
NULL as vendor_nm, NULL as application_nm,NULL as dependency_code_value'
END
select @sSelect=@sselect + ' FROM #tmpTable Where grp_Code= ' + CAST(@iGrpCnt as varchar(4)) + '
ORDER BY sort_id, business_activity_profile_id, grp_code '
EXEC(@sSelect)
End

Delete From #tmpTable Where grp_code=@igrpCnt

if (Select Count(business_activity_profile_id) from #tmpTable)>0
Begin
While (Select COUNT(business_activity_profile_id) From #tmpTable) > 0
Begin
Select @iGrpCnt=(Select MIN(grp_code) From #tmpTable Where business_activity_profile_id=@iBap)
Select @iAdd = (Select MAX(sort_id) from #tmpTable Where business_activity_profile_id=@iBap and grp_code=@iGrpCnt)
Select @currRowCnt=(Select Count(sort_id) from #tmpTable Where business_activity_profile_id=@iBap and grp_code=@iGrpCnt)
--Set up the repeating data values; group code, sortID = the last record that is not null, rowcnt = the number of rows that are NULL
IF @currRowCnt<@iRow
Begin
INSERT INTO #addRowColumns(grp_code, sort_id, rowCnt, business_activity_profile_id, useValue_1)
Select DISTINCT @iGrpCnt as grp_code, t.sort_id, (@iRow-@currRowCnt) as rowCnt,
@iBap as business_activity_profile_id, t.value1_tx as useValue_1
From #tmpTable t
Where t.business_activity_profile_id=@iBap AND t.grp_code = @iGrpCnt AND t.sort_id = @iAdd
IF @iGrpCnt=2
Begin
UPDATE #addRowColumns set useValue_2 = t.value2_tx
FROM #addRowColumns a JOIN #tmpTable t ON a.sort_id=t.sort_id
AND a.business_activity_profile_id=t.business_activity_profile_id
Where a.business_activity_profile_id=@iBap AND a.grp_code=t.grp_code and a.grp_code=@iGrpCnt
AND t.sort_id=@iAdd
End
Else
If @iGrpCnt=1 or @iGrpCnt=4
Begin
UPDATE #addRowColumns set useValue_2 = t.value2_tx, useValue_3=t.value3_tx
FROM #addRowColumns a JOIN #tmpTable t ON a.sort_id=t.sort_id
AND a.business_activity_profile_id=t.business_activity_profile_id
Where a.business_activity_profile_id=@iBap AND a.grp_code=t.grp_code
and a.grp_code=@iGrpCnt and t.sort_id=@iAdd
End
Select @currRowCnt=(Select rowCnt from #addRowColumns
Where sort_id=@iGrpCnt and business_activity_profile_id=@iBap AND sort_id=@iAdd)
End

--Add the existing values for the group - could remove the group code value as it isn't completely accurate; thought I needed it for join purposes,
--but turned out to be useless!....not causing a problem so left .
Select @sSelect='UPDATE #final Set grp_code = ' + CAST(@iGrpCnt as varchar(4)) + ', ' +
CASE @iGrpCnt
When 1 Then ' org_nm = t.value1_tx, business_activity_ds = t.value2_tx, modified_dt = t.value3_tx '
When 2 Then ' regulator = t.value1_tx, regulation=t.value2_tx '
WHEN 3 THEN ' geography = t.value1_tx '
WHEN 4 THEN ' product_class_tx=t.value1_tx, product_nm=t.value2_tx, activity_tx=t.value3_tx '
WHEN 5 THEN ' vendor_nm=t.value1_tx '
WHEN 6 THEN ' dependency_code_value = t.value1_tx'
WHEN 7 THEN 'application_nm =t.value1_tx'
WHEN 8 THEN 'key_risk_indicator_id = t.value1_tx'
ELSE 'org_nm=f.org_nm, business_activity_ds=f.business_activity_ds, modified_dt=f.modified_dt,
regulator = f.regulator, regulation=f.regulation, geography=f.geography, key_risk_indicator_id=f.key_risk_indicator_id,
product_class_tx=f.product_class_tx, product_nm=f.product_nm, activity_tx=f.activity_tx,
vendor_nm=f.vendor_nm, dependency_code_value=f.dependency_code_value, application_nm=f.application_nm'
END
Select @sSelect = @sSelect + 'From #final f JOIN #tmpTable t ON t.sort_id=f.sort_id
WHERE f.business_activity_profile_id=t.business_activity_profile_id AND
f.business_activity_profile_id = ' + CAST(@iBap as varchar(4)) + ' AND t.grp_code = ' + CAST(@iGrpCnt as varChar(4)) + ''
EXEC(@sSelect)

Select @currRowCnt=@iAdd+1
While (@currRowCnt < (@iRow+1))
Begin
INSERT INTO #addRowColumns
Select @iGrpCnt as grp_code, @currRowCnt as sort_id, 0 as rowCnt, ac.business_activity_profile_id,
0 as business_activity_product_id, ISNULL(ac.useValue_1, 'NODATA'), ISNULL(ac.useValue_2, 'NODATA'),
ISNULL(ac.useValue_3, 'NODATA')
FROM #addRowcolumns ac Where ac.sort_id=@iAdd
Select @currRowCnt=@currRowCnt+1
End
Delete from #addRowColumns Where rowCnt <> 0
IF (SELECT COUNT(grp_code) From #addRowColumns where grp_code=@iGrpCnt) > 0
Begin
Select @sSelect='UPDATE #final Set grp_code = ' + CAST(@iGrpCnt as varchar(4)) + ', ' +
CASE @iGrpCnt
WHEN 1 Then ' org_nm=ac.useValue_1, business_activity_ds=ac.useValue_2, modified_dt=ac.useValue_3 '
When 2 Then ' regulator = ac.useValue_1, regulation=ac.useValue_2 '
WHEN 3 THEN ' geography = ac.useValue_1 '
WHEN 4 THEN ' product_class_tx=ac.useValue_1, product_nm=ac.useValue_2, activity_tx=ac.useValue_3 '
WHEN 5 THEN ' vendor_nm=ac.useValue_1 '
WHEN 6 Then ' dependency_code_value = ac.useValue_1'
WHEN 7 THEN 'application_nm = ac.useValue_1'
WHEN 8 THEN 'key_risk_indicator_id = ac.useValue_1'
ELSE 'org_nm=f.org_nm, business_activity_ds=f.business_activity_ds, modified_dt=f.modified_dt,
regulator = f.regulator, regulation=f.regulation, geography=f.geography, key_risk_indicator_id=f.key_risk_indicator_id,
product_class_tx=f.product_class_tx, product_nm=f.product_nm, activity_tx=f.activity_tx,
vendor_nm=f.vendor_nm, dependency_code_value=f.dependency_code_value,application_nm=f.application_nm'
END
Select @sSelect = @sSelect + 'From #final f JOIN #addRowColumns ac ON ac.sort_id=f.sort_id
WHERE f.business_activity_profile_id=ac.business_activity_profile_id AND
f.business_activity_profile_id = ' + CAST(@iBap as varchar(4)) + ' AND ac.grp_code = ' + CAST(@iGrpCnt as varChar(4)) + ''
EXEC(@sSelect)

End

Delete from #tmpTable Where grp_code=@iGrpCnt
DELETE From #addRowColumns
End
End
Set @iCount=@iCount-1
Delete From #rabuList Where business_activity_profile_id=@iBap

End
Select * from #final order by business_activity_profile_id, sort_id

--Select * from @addRowColumns
DROP TABLE #addRowColumns
DROP TABLE #filteredBaps
DROP TABLE #rabuList
DROP TABLE #tmpTable
DROP TABLE #tmp
DROP TABLE #final

End
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 11:37:22

well this looks wrong:
Select sort_id, ' + CAST(@iGrpCnt as varchar(4)) + ' as grp_code, business_activity_profile_id,
should be
Select sort_id, ''' + CAST(@iGrpCnt as varchar(4)) + ''' as grp_code, business_activity_profile_id,

since you're casting it to varchar. i haven't looked but there are probably more of the same error in your query.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources