I am trying to get the DTS Execute SQL task to run a simple sql server 2000
no-parameter stored procedure.

The procedure runs without error when using SQL Analyzer but DTS Execute SQL
reports "could not find stored procedure "[dbo].[test]"

The stored procedure is as follows:

create procedure test as

declare tnames_cursor CURSOR
for
select database_name, tmp_table_name from
[DW_Dimensions].[dbo].[vw_dimension_temporary_tables_active]

open tnames_cursor

declare @tablename sysname
declare @databasename sysname

while (@@FETCH_STATUS <> -1)
begin
select @databasename = rtrim(@databasename)
select @tablename = rtrim(@tablename)
exec ("delete from " + @databasename + ".dbo." + @tablename)
fetch next from tnames_cursor into @databasename, @tablename
end
close tnames_cursor
deallocate tnames_cursor
GO

Any ideas?

First thing is that DTS is not able to find stored
procedure.........two possibilities:

1. Stored Procedure don't exist in the SQL server
2. Stored procedures exists but u r not referring it correctly. May be
instead of [dbo].[test] it should be [<DB NAME>].[<DB USER>].[test]

Can you check this?

-Hayat
www.mysticssoft.com

"Bruce Lester" <bruce_lester@email.com> wrote in message news:<FQ3fb.203970$mp.125924@rwcrnsc51.ops.asp.att .net>...
> I am trying to get the DTS Execute SQL task to run a simple sql server 2000
> no-parameter stored procedure.
>
> The procedure runs without error when using SQL Analyzer but DTS Execute SQL
> reports "could not find stored procedure "[dbo].[test]"
>
> The stored procedure is as follows:
>
> create procedure test as
>
> declare tnames_cursor CURSOR
> for
> select database_name, tmp_table_name from
> [DW_Dimensions].[dbo].[vw_dimension_temporary_tables_active]
>
> open tnames_cursor
>
> declare @tablename sysname
> declare @databasename sysname
>
> while (@@FETCH_STATUS <> -1)
> begin
> select @databasename = rtrim(@databasename)
> select @tablename = rtrim(@tablename)
> exec ("delete from " + @databasename + ".dbo." + @tablename)
> fetch next from tnames_cursor into @databasename, @tablename
> end
> close tnames_cursor
> deallocate tnames_cursor
> GO
>
> Any ideas?