April 18, 2013 at 2:22 am
Hello everybody.
I'm getting an error in a job that I can't explain myself.
I have two servers: ERPSERVER and STAGINGSERVER
on STAGINGSERVER I run scheduled jobs that get data from ERPSERVER so to elaborate and provide them to other databases.
on ERPSERVER I have defined a view that prepares the data to be provided to STAGINGSERVER
Now, on STAGINGSERVER I have a scheduled job that queries ERPSERVER and use those data to feed my ETL. ERPSERVER is defined as linked server on STAGINGSERVER.
What happens is that somewhen I got errors on the job.
This is part of the View's Code:
SELECT
Partite.CreditNote,
1 AS exchangerate,
CONVERT(NVARCHAR(100), Partite.Salesperson) AS owneridExt,
...
...
FROM dbo.MA_PyblsRcvbls AS Partite INNER JOIN
dbo.MA_PyblsRcvblsDetails AS PartiteRighe
...
...
the code inside my job is the following
SELECT [CreditNote]
,[exchangerate]
,[owneridExt]
...
...
...
INTO #_OPENITEMS
FROM [ERPSERVER].DBNAME.dbo.VW_CRM_OpenItem
this is the error I get:
Step ID1
ServerSTAGINGSERVER
Job NameImport_1D_19
Step NameETL OpenItem
Duration00:00:10
Sql Severity16
Sql Message ID3621
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: domain\crmsqlserver. Cannot insert the value NULL into column 'exchangerate', table 'tempdb.dbo.#_OPENITEMS_______________________________________________________________________________________________0000000045DB'; column does not allow nulls. UPDATE fails. [SQLSTATE 23000] (Error 515) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
I'm out of resources, because I can't understand how it can the column 'exchangerate' be NULL.
If I execute the ETL Query from SSMS, all runs without any problem. If I run the query then I run the job it runs without problems
May it be a network/authentication problem?
Thank you in advance.
April 18, 2013 at 5:46 am
Rather than selecting into your temporary table have you tried creating the temporary table and specifying NULL in the definition of the column that fails in the error message you provided?
Chris
April 18, 2013 at 7:12 am
Hello Chris, thank you for the reply.
No I didn't tried your suggestion. I will in a few minutes.
May you tell me which kind of effect should I see?
Why do I have a NULL value in a field that is created with a value?
thank you again
April 18, 2013 at 7:53 am
The query and job should run without error, as for why you have NULL values I don't know why you have them. If you run the SELECT without the insert and check the result set, you should see the NULL records.
Chris
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply