Advertisement

07.03.2008 at 08:24AM PDT, ID: 23537175
[x]
Attachment Details

What are the general causes for the above stored procedure error?

Asked by ipjyo in SQL Server 2005

Tags: Microsoft, SQL Server Management Studio Express, 2005, SQL Server 2005 / XML, Msg 208, Level 16, State 6, Procedure spPTS_LoadNarratives, Line 182, Invalid object name 'dbo.spPTS_LoadNarratives'.

I have the attached stored procedure. and I am trying to compile this procedure with  the proper database selected. But I am getting the error as:

Msg 208, Level 16, State 6, Procedure spPTS_LoadNarratives, Line 182
Invalid object name 'dbo.spPTS_LoadNarratives'.

Could you please guide me where I am going wrong.

Thanks in advance!
Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:

USE [PostTrade]
GO
 
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
 
ALTER Procedure [dbo].[spPTS_LoadNarratives]
    @XMLData	text	 
as
Begin
Set nocount on
        
Declare	 @BCRTID				varchar(20)
	,	@CallingProc			varchar(40)
	,	@TransactionId			uniqueidentifier
	,	@BCRTTranId				varchar(40)
	,	@NarrativeId 			uniqueidentifier
	,	@ActionCd				varchar(1)
	,	@DisplayTypeCd			varchar(7)  
	,	@SourceTS				datetime 
	,	@GMTOffset				varchar(5)
	,	@NarrativeSourceGMTTS	datetime
	,	@HostLEId				varchar(7)
	,	@NarrativeSeqNo			varchar(20)     
	,	@NarrativeTxt			varchar(500)
	,   @NarrativeUserId		varchar(40)
	,	@NarrativeSource		varchar(40)
	,	@ProcessDt				varchar(11)
	,	@ApplicationCd			varchar(40)
	,	@SourceSysRefNo			varchar(20)
	
	    
Declare	@tmpTransaction	table (	
	  ActionCd			varchar(1)		null
      ,   DisplayTypeCd	varchar(7)		null
      ,   GMTOffset		varchar(5)		null
      ,   HostLEId			varchar(7)		null
      ,   NarrativeSeqNo	varchar(20)		null
      ,   NarrativeTxt		varchar(500)	null
      ,   NarrativeUserId	varchar(40)		null
      ,   ProcessDt		varchar(11)		null
      ,   SendingSysCd		varchar(7)		null
      ,   SourceSysCd		varchar(40)		null
      ,   SourceSysRefNo	varchar(20)		null
      ,   SourceTS			varchar(26)		null )
 
Declare	@NarrativeGMTTS	datetime
	,	@SettlmntNarrativeGMTTS	datetime	
          		
Declare    @RecordExistsIn	char(1)
	   ,   @RecordExistsIn_EXT char(1)			
			
select  @RecordExistsIn	=	'N'
select	@RecordExistsIn_EXT	=	'N' 
 
Declare	@xmldoc int	
 
	--*****************Extract Message Tags from XML**************
	 EXEC sp_xml_preparedocument @xmldoc OUTPUT, @XMLData
   
     insert	@tmpTransaction (ActionCd, DisplayTypeCd, GMTOffset, HostLEId,
				 NarrativeSeqNo, NarrativeTxt, NarrativeUserId, ProcessDt,
				 SendingSysCd,	SourceSysCd,	SourceSysRefNo,	SourceTS)
	 select	 a.ActionCd, a.DisplayTypeCd, a.GMTOffset, a.HostLEId,
             a.NarrativeSeqNo, a.NarrativeTxt, a.NarrativeUserId,
             a.ProcessDt, a.SendingSysCd, a.SourceSysCd, 
             a.SourceSysRefNo, a.SourceTS
	 from	openxml (@xmldoc, '/MSG/DATA',2)
	   with (ActionCd	varchar(1),
			DisplayTypeCd	varchar(7),
            GMTOffset varchar(5),
            HostLEId  varchar(7),
			NarrativeSeqNo varchar(20),
			NarrativeTxt	varchar(500),
			NarrativeUserId	varchar(40),
			ProcessDt		varchar(11),
			SendingSysCd		varchar(7),
			SourceSysCd		varchar(40),
			SourceSysRefNo	varchar(20),
			SourceTS			varchar(26)) a
	
	 EXEC sp_xml_removedocument @xmldoc OUTPUT
	--*******************************************************************
 
select	@NarrativeId 	=	newid()
select	@CallingProc 	=	object_name(@@procid)
select	@ActionCd		=	ActionCd from @tmpTransaction  
select  @DisplayTypeCd	=	DisplayTypeCd from @tmpTransaction
select  @SourceTS		=	SourceTS from @tmpTransaction 
--calculate @NarrativeSourceGMTTS
select  @GMTOffset		=	GMTOffset from @tmpTransaction
select	@NarrativeSourceGMTTS = dateadd(minute, cast(isnull(@GMTOffset,0) as int) * -1, cast(@SourceTS as datetime))
select	@HostLEId		=	HostLEId from  @tmpTransaction 
select  @NarrativeSeqNo	=	NarrativeSeqNo from @tmpTransaction
select  @NarrativeTxt	=	NarrativeTxt from @tmpTransaction
select  @NarrativeUserId	=	NarrativeUserId from @tmpTransaction
select  @NarrativeSource	=	SendingSysCd from @tmpTransaction
select  @ProcessDt		=	ProcessDt from @tmpTransaction
select  @ApplicationCd	=	SourceSysCd from @tmpTransaction
select  @SourceSysRefNo	=	SourceSysRefNo from @tmpTransaction
	  
--****************************Validations******************************
 
--datetime validations
	if len(ltrim(@ProcessDt)) > 0 and isdate(@ProcessDt) = 0          
		 begin raiserror(60000,16,1,'ProcessDt',@CallingProc) return -1 end
 
-- Other Validations
	if @ActionCd not in ('A')
		begin raiserror(70129,16,1, @ActionCd, @CallingProc) return -1 end	
 
	if @HostLEId not in ('MLPFS')
		begin raiserror(70129,16,1, @HostLEId, @CallingProc) return -1 end
 
--numeric validation
	if len(ltrim(@GMTOffset)) > 0 and isnumeric(@GMTOffset) = 0 
		begin raiserror(60001,16,1,'GMTOffset',@CallingProc) return -1 end
 
--datetime conversion
	if len(ltrim(@ProcessDt)) = 0 select @ProcessDt = null
 
--numeric conversion
	if len(ltrim(@GMTOffset)) = 0 set @GMTOffset = null
 
	if @SourceTS is null
	select	@SourceTS = getdate()
 
 
	select	@BCRTID		= BCRTUniqueId
		,	@BCRTTranId	= TransactionId 
	from	tb_BCRTTransactionXRef(nolock) 
	where	BCRTUniqueId	=  @SourceSysRefNo
 
	if @BCRTID is null or len(ltrim(@BCRTID)) = 0
		begin raiserror(70111,16,1,'No Related transaction for Narrative',@CallingProc) return -1 end
 
	select 	@RecordExistsIn ='Y'
	,	@TransactionId		= TransactionId 
	from	tb_Transaction (nolock) 
	where	TransactionId = @BCRTTranId
 
	If @RecordExistsIn = 'N'	
	--if @TransactionId is null or len(ltrim(@TransactionId)) = 0
	begin raiserror(70111,16,1,'No Related transaction for Narrative',@CallingProc) return -1 end	
   
 
	If @DisplayTypeCd not in ('C','S')
		begin raiserror (70200,16,1,'DisplayTypeCd',@CallingProc) return -1 end
 
	If @DisplayTypeCd in ('C')
	begin
		select @NarrativeGMTTS	=	NarrativeGMTTS from tb_Transaction 
		where TransactionId = @TransactionId	
			If 	@NarrativeGMTTS > @NarrativeSourceGMTTS  
				begin
				Update PostTrade..tb_Transaction 
					set NarrativeId	=	@NarrativeId	
				,	NarrativeGMTTS	=	@NarrativeSourceGMTTS
	--			,	NarrativeSeqNo	=	@NarrativeSeqNo 
					where	TransactionId	=	@TransactionId 
				end
	end
 
    If @DisplayTypeCd in ('S') 
	begin
		select @SettlmntNarrativeGMTTS	=	SettlmntNarrativeGMTTS from tb_TransactionExt 
		where TransactionId = @TransactionId
		If 	@SettlmntNarrativeGMTTS > @NarrativeSourceGMTTS
			begin
				select	@RecordExistsIn_EXT	=	'Y' from tb_transactionext
				where	TransactionId 	= @TransactionId
				If @RecordExistsIn_EXT = 'Y'
					update 	PostTrade..tb_TransactionExt
						set SettlmntNarrativeId = @NarrativeId
					,	SettlmntNarrativeSeqNo	=	@NarrativeSeqNo
						where	TransactionId	=	@TransactionId  
				else
 					insert PostTrade..tb_TransactionExt (
						TransactionId,
						SettlmntNarrativeId,	SettlmntNarrativeSeqNo)
					values(
						@TransactionId,	
						@NarrativeId,	@NarrativeSeqNo)
			end			
	end
 
   insert PostTrade..tb_TransactionNarrative (
			NarrativeId,		TransactionId,		NarrativeUserId,	NarrativeSource,
			NarrativeTypeCd,	ApplicationCd,		SourceTS,			RowInsrtdTS,
			RowInsrtdByUserId,	NarrativeText,		DisplayTypeCd,		NarrativeSeqNo,
			SourceSysRefNo)	     		
			
	values(
		@NarrativeId,	@TransactionId,		@NarrativeUserId,	@NarrativeSource,
		'TEXT',		@ApplicationCd,		@SourceTS,		getdate(),
		@CallingProc,	@NarrativeTxt,		@DisplayTypeCd,		@NarrativeSeqNo,
		@SourceSysRefNo)
End
[+][-]07.03.2008 at 08:28AM PDT, ID: 21926428

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 08:41AM PDT, ID: 21926547

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 08:46AM PDT, ID: 21926599

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]07.03.2008 at 09:09AM PDT, ID: 21926826

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zone: SQL Server 2005
Tags: Microsoft, SQL Server Management Studio Express, 2005, SQL Server 2005 / XML, Msg 208, Level 16, State 6, Procedure spPTS_LoadNarratives, Line 182, Invalid object name 'dbo.spPTS_LoadNarratives'.
Sign Up Now!
Solution Provided By: aneeshattingal
Participating Experts: 3
Solution Grade: A
 
 
[+][-]07.03.2008 at 09:16AM PDT, ID: 21926901

Assisted solutions are selected by the member who asked the question as a comment that contributed to their question's solution.

Start your 7-day free trial to view this Assisted Solution or ask the Experts your question.

 
[+][-]07.03.2008 at 09:22AM PDT, ID: 21926971

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628