Running TSQL job for SQL server agent is failing, giving me ANSI errors?

Does anybody know why this statement wont run on a schedule for the
server agent job. I ran it as a TSQL statement and tried it as a
stored procedure and both failed. this is what I'm trying to run
below...
----
--
-- 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%' --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)
----
select * from #tLinkedServerTables
drop table #tLinkedServerTables
Close crsLinkedServerTables
Deallocate crsLinkedServerTables
This is what it shows in the job history when I run it as a TSQL
statement and it fails.
... [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work
Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID],
[Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where
Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'
[SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action
DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.CFAC1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
(Message 0) Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
[Next_Work_Date] FROM public.CFAD1') as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
(Message 0) Update WorkList SET [Tickler Last Action DT] = Data.[Last_... The step failed.
I also tried to make a stored procedure and I get an error when I
execute it in a job as well. My job run statement exec [SCH CF
UPDATE]. This is the error I get below.
Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date],
[Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT
[Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1')
as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon
= 'SCH' [SQLSTATE 01000] (Message 0) Heterogeneous queries require the
ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This
ensures consistent query semantics. Enable these options and then
reissue your query. [SQLSTATE 42000] (Error 7405). The step failed.Hi Mike
"mike11d11" wrote:
> Does anybody know why this statement wont run on a schedule for the
> server agent job. I ran it as a TSQL statement and tried it as a
> stored procedure and both failed. this is what I'm trying to run
> below...
> ----
> --
> -- 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%' --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)
> ----
> select * from #tLinkedServerTables
> drop table #tLinkedServerTables
> Close crsLinkedServerTables
> Deallocate crsLinkedServerTables
>
>
>
>
>
> This is what it shows in the job history when I run it as a TSQL
> statement and it fails.
>
> ... [Tickler Last Action DT] = Data.[Last_Action_Date], [Next Work
> Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT [Account_ID],
> [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1') as Data Where
> Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH'
> [SQLSTATE 01000] (Message 0) Update WorkList SET [Tickler Last Action
> DT] = Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date]
> FROM OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.CFAC1') as Data Where Data.Account_ID => WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
> (Message 0) Update WorkList SET [Tickler Last Action DT] => Data.[Last_Action_Date], [Next Work Date] = Data.[Next_Work_Date] FROM
> OPENQUERY(SCH,'SELECT [Account_ID], [Last_Action_Date],
> [Next_Work_Date] FROM public.CFAD1') as Data Where Data.Account_ID => WorkList.[ACCOUNT#] AND WorkList.Logon = 'SCH' [SQLSTATE 01000]
> (Message 0) Update WorkList SET [Tickler Last Action DT] => Data.[Last_... The step failed.
>
> I also tried to make a stored procedure and I get an error when I
> execute it in a job as well. My job run statement exec [SCH CF
> UPDATE]. This is the error I get below.
> Update WorkList SET [Tickler Last Action DT] = Data.[Last_Action_Date],
> [Next Work Date] = Data.[Next_Work_Date] FROM OPENQUERY(SCH,'SELECT
> [Account_ID], [Last_Action_Date], [Next_Work_Date] FROM public.CFAB1')
> as Data Where Data.Account_ID = WorkList.[ACCOUNT#] AND WorkList.Logon
> = 'SCH' [SQLSTATE 01000] (Message 0) Heterogeneous queries require the
> ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This
> ensures consistent query semantics. Enable these options and then
> reissue your query. [SQLSTATE 42000] (Error 7405). The step failed.
>
Have you tried SELECT @.@.OPTIONS in the script to see if ANSI_NULLS and
ANSI_WARNINGS are set? If not try setting them.
John|||It's in the error message you got:
Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This
ensures consistent query semantics. Enable these options and
then reissue your query.
Set the ansi settings in the job script, something like:
SET ANSI_WARNINGS ON
GO
SET ANSI_NULLS ON
GO
<your job stuff here>
Or try recreating your stored procedure using:
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE YourSP...etc.
-Sue
On 26 Jan 2007 11:48:53 -0800, "mike11d11"
<mike11d11@.yahoo.com> wrote:
>Does anybody know why this statement wont run on a schedule for the
>server agent job. I ran it as a TSQL statement and tried it as a
>stored procedure and both failed. this is what I'm trying to run
>below...
>----
>--
>-- 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.