RE : Stored procedure error - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

RE : Stored procedure error

Author

Topic

kond.mohan
Posting Yak Master

213 Posts

Posted - 2012-06-25 : 05:30:57

Dear all,
we have created ssis packages and stored procedures to load the data from ORACLE source to SQL SERVER destionion on daily basis

every day my stored procedure is executing fine

this is my sp
BEGIN

truncate table data_update_time_full

Declare @compCode varchar(50)
declare @ETLPattern nvarchar(500)
Declare @QueryText nvarchar(4000)
Declare @rowcnt int
Declare @MaxDate2 varchar(50)
Declare @queryvariable nvarchar(4000)
Declare @incOrFull varchar(50)
Declare @PK varchar(500)
Declare @Schema_name varchar(500)
Declare @Start_time datetime
Declare @end_time datetime
dECLARE @SOURCE_TABLENAME VARCHAR(500)

Declare @KeyMatrix Cursor
Set @KeyMatrix = Cursor for
SELECT [TABLE],incOrFull,PK, [Schema_name] FROM dbo.[Tables]
where incorfull = 'Full' -- and [Table] = 'DDC'
and [Table] not in (select [Table_name] from data_update_time_full)
--and [Table] not in ('Accounts') --('CLC','LAVS','LN_BGEN','FPC')
order by [table]

Open @KeyMatrix

Fetch NEXT from @KeyMatrix into @CompCode, @incorFull, @pk, @schema_name
While @@FETCH_STATUS = 0
BEGIN

print 'Started Table --------------------'+@CompCode

IF @incOrFull = 'Full'
BEGIN

set @Start_time = GETDATE()
set @QueryText =''
set @QueryText = 'Truncate Table '+ @compCode
print @QueryText
Exec SP_EXECUTESQL @QUERYTEXT

set @QueryText =''

set @QueryText = 'insert into '+@compcode+' select * from openquery(FINRPT,'+CHAR(39)+ 'select * from '+@Schema_name + '.'+@compcode+CHAR(39)+')'
print @QueryText
Exec SP_EXECUTESQL @QUERYTEXT

SET @SOURCE_TABLENAME = @SCHEMA_NAME +'.'+ @COMPCODE

--IF @compCode = 'FBM'
-- BEGIN
-- ALTER INDEX ALL ON FBM REBUILD
-- END

--IF @compCode = 'OCI'
-- BEGIN
-- ALTER INDEX ALL ON OCI REBUILD
-- END
--IF @compCode = 'FBH'
-- BEGIN
-- execute create_index_fbh
-- END
--IF @compCode = 'CXL'
-- BEGIN
-- CREATE NONCLUSTERED INDEX [indx03] ON [dbo].[CXL]
-- ( [ENTITY_CRE_FLG] ASC, [DEL_FLG] ASC, [TARGET_ACID] ASC
-- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON SECONDARY
-- END
END
set @QueryText = 'select count(*) from '+ @compCode
exec sp_executesql @querytext

set @end_time = GETDATE()

EXECUTE ETL_Control_Table @SOURCE_TABLENAME , @COMPCODE, @Start_time, @end_time, 'Y'

insert into Data_update_time
values(@compCode,@Start_time, @end_time)

print 'Completed Table ----------------------'+@Compcode+'------------------------'
Fetch NEXT from @KeyMatrix into @CompCode, @incorFull, @pk, @schema_name
end

CLOSE @KEYMATRIX
DEALLOCATE @KEYMATRIX
END

previosuly exected fine
i got this error on last 3 days onwards

Executed as user: ONEKEYBIDB\SYSTEM. Microsoft (R) SQL Server Execute Package Utility Version 10.0.5500.0 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:20:56 AM Error: 2012-06-24 18:19:20.49 Code: 0xC002F210 Source: FULLREAD PROCEDURE Execute SQL Task Description: Executing the query "execute DWH_AUTO_UPDATE_full" failed with the following error: "Unspecified error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:20:56 AM Finished: 8:09:52 PM Elapsed: 38934.8 seconds. The package execution failed. The step failed.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-25 : 06:04:59

Probably a permissions or network/firewall problem - or maybe someone has changed the structure of the Oracle database?

Maybe it[s just that something has lost the context and just needs a reboot?
Does the query work if yoou execute it on the Oracle database?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources