Valid Identifier - 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 2000 Forums

Transact-SQL (2000)

Valid Identifier

Author

Topic

alunjg
Starting Member

15 Posts

Posted - 2005-09-06 : 22:35:13

Hi all

I have an SP that calls quite a few others, and I am getting the failure:
The name 'EXEC.....' is not a valid identifier.

In extracted out the syntax for that particular part of the SP & ran it & got the same error. The syntax is:
*********************************
declare @plan_id int, @sqltxt varchar(8000), @monitor_server varchar(30)
, @secondary_server varchar(30), @compressFilename varchar(255)
set @plan_id = 108
set @monitor_server = 'au-drp-monitor'
set @secondary_server = 'au-drp-ser01'
set @compressfilename = '\\AU-DRP-MONITOR\LogShip\GROUPSQL1\GROUPSQL1_SKMBackups_Log050907101358.rar'

SET @sqltxt= 'EXEC [' + @monitor_server + '].[LogShipping].dbo.LSProc_FTPFiles @secondary_server = '''+@secondary_server
SET @sqltxt = @sqltxt +''' ,@plan_id = '+cast(@plan_id as varchar(20))+' , @filename = '''+replace(@compressfilename,'v','')+''''

print @sqltxt
EXEC @sqltxt
***************************************

The command that is produced by this syntax is correct & actually works on it's own, i.e.:
***
EXEC [au-drp-monitor].[LogShipping].dbo.LSProc_FTPFiles
@secondary_server = 'au-drp-ser01'
,@plan_id = 108
,@filename = '\\AU-DRP-MONITOR\LogShip\GROUPSQL1\GROUPSQL1_SKMBackups_Log050907101358.rar'
***

Can anyone point me in the right direction as to why the syntax doesn't actually work when run from an SP? Any help would be appreciated.

Or, am I being a DUMB-ASS & the problem is staring me in the face?

Thanks in advance.

Regards

Alun

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-06 : 23:59:43

Did you try

 exec(@sqltxt)

Nathan Skerl

alunjg
Starting Member

15 Posts

Posted - 2005-09-07 : 18:58:47

Yes I did Nathan, thanks. However, if you have a cmd line

EXEC @return_value = @sqltxt

you still get the error, as you cannot bracket the @sqltxt, coz you get an invalid syntax error.

I really need the return value to ensure that further processing doesn't continue if there is a problem.

'What now?' he cries........

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-09-07 : 20:24:15

Remove "EXEC" from your @sqltext variable and try it.

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-09-09 : 10:56:02

Would the return parameter from the EXEC be in the scope of the execution of the dynamic sql or the execution of the stored procedure? Why not just use @@error following the exec ?

I think you'll have to use sp_executesql if you want to pass parameters in/out of the stored proc being executed in the dynamic sql, right?

declare	@sql nvarchar(4000),
        @error_code int

select @sql = N'exec @error_code = usp_test 10'
exec sp_executesql @sql, N'@error_code int output', @error_code output

select @error_code

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources