mike11d11
I tried recreating my stored procedure from the query analyzer with this below but still same results when I exec the stored procedure from the jobs.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE [DBO].[TLRA CF UPDATE] AS
---------------------------------------------------------------------------
--
-- Following is a simple procedure for creating a temporary table
-- that has the table_name of each of the user tables in the linked server.
--
---------------------------------------------------------------------------
Declare
@Table_Name varchar(255),
@Sql Nvarchar(500)
---------------------------------------------------------------------------
-- Create a temporary table for storing result of sp_tables_ex
---------------------------------------------------------------------------
Create Table #tLinkedServerTables
(
Table_Cat varchar(255),
Table_schem varchar(255),
Table_Name varchar(255),
Table_Type varchar(255),
Remarks varchar(255)
)
---------------------------------------------------------------------------
-- Populate Temporary table with the results of the sp_tables_ex command
-- NOTE: the paramater passed to sp_tables_ex MUST be the name of a
-- valid linked server (already defined)
---------------------------------------------------------------------------
Insert Into #tLinkedServerTables exec sp_tables_ex 'TLRA'
---------------------------------------------------------------------------
-- Create cursor for selecting the Table names from the linked server
---------------------------------------------------------------------------
Declare crsLinkedServerTables Cursor For
Select table_name
From #tLinkedServerTables
Where Table_type = 'TABLE'
and table_name like 'CF%'
and table_name not like 'CF*%'
and table_name not like 'CFDAILY%'
and table_name not like 'CF1' --test 'public.CF%'
-- Open the cursor defined above
Open crsLinkedServerTables
Fetch Next From crsLinkedServerTables Into @Table_Name
While @@Fetch_Status = 0 Begin -- 0 = more records to process
-- Your Update process goes here.... Need to use dynamic SQL to create the
-- Query.
Set @Sql = 'Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(TLRA,''SELECT [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.' + @table_name + ''') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = ''TLRA'''
print @Sql
exec (@Sql) /* Better to use sp_executesql, but this is here as an example and should work */
-- Move to the next record
Fetch Next From crsLinkedServerTables Into @Table_Name
End
---------------------------------------------------------------------------
-- Clean up (drop temp tables, remove cursors)
---------------------------------------------------------------------------
drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO