error when creating procedure - 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

General SQL Server Forums

New to SQL Server Programming

error when creating procedure

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-07-29 : 10:14:45

When attempting to create the procedure below I receive the
following message,
many thanks in advance for your guidance
:
Msg 102, Level 15, State 1, Procedure ETL_Extract_Oracle_F590690, Line 29
Incorrect syntax near 'SELECT * FROM OpenQuery(@SERVER, ' SELECT JCMCU ,JCCOMNTS FROM PRODDTA.F590690 WHERE UAUPMJ >= @Start')'.

create procedure dbo.ETL_Extract_Oracle_F590690 @Server VARCHAR(30), @Schema VARCHAR(10) , @RunDate SMALLDATETIME, @LEID VARCHAR(5) = NULL

AS
declare @ErrorStatus int

set @ErrorStatus = 0

--Log info
declare @ProcName varchar(100), @Object varchar(100), @RowCount int, @RC int
Set @ProcName = '[dbo].[ETL_Extract_Oracle_F590690] ' + Cast(@RunDate as varchar(12))

declare @Start int, @SQL nvarchar(max)
set @Start = dbo.fn_GetJDate(@RunDate)

--EXTRACT CHANGED RECORDS FROM JDE
----F590690 JobBook Custom Table
Begin try
Set @Object = 'F590690'
Exec bi_InsertRefreshLog @Object, @ProcName, NULL, 1 --Start the Log

set @SQL = 'SELECT * FROM OpenQuery(@SERVER, '' SELECT JCMCU ,JCCOMNTS FROM PRODDTA.F590690 WHERE UAUPMJ >= @Start'')'

GO

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-29 : 10:24:27

You need to add matching END TRY block and (optionally but highly recommended CATCH block) for your BEGIN TRY block.


                ......
		SET @SQL = 
		    'SELECT * FROM OpenQuery(@SERVER, '' SELECT JCMCU ,JCCOMNTS FROM PRODDTA.F590690 WHERE UAUPMJ >= @Start'')'
		
	END TRY
	BEGIN CATCH
	-- do catch stuff here
	END CATCH

Edit: The catch block itself is required, but the stuff within it is optional, but highly recommended.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 11:41:02

shouldnt

set @SQL = 'SELECT * FROM OpenQuery(@SERVER, '' SELECT JCMCU ,JCCOMNTS FROM PRODDTA.F590690 WHERE UAUPMJ >= @Start'')'
be

set @SQL = 'SELECT * FROM OpenQuery(' + @SERVER + ', '' SELECT JCMCU ,JCCOMNTS FROM PRODDTA.F590690 WHERE UAUPMJ >= ' + @Start + '')'

instead?

also are you using EXEC or sp_executesql to execute this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources