I'm trying to use 24x7 to run a SQL Server stored procedure; however, every time the job in 24x7 has run, it has returned an error.
I can run this stored procedure via DTS or SQL Query Analyzer and does not return an error.
The error message that is being returned states that "cannot insert NULL value into field...column does not allow NULL. INSERT fails.".
I'm inserting data into a temp table and NULL values are valid.
Please help. Thanks.

The behavior is controlled by ANSI_NULLS database or session option

Try recompiling your procedure with this option set to on
SET ANSI_NULLS ON
go
CREATE PROC ...
AS
...
go

This option controls how the table is created, whether SQL Server uses NULL or NOT NULL for table columns.

: I'm trying to use 24x7 to run a SQL Server stored procedure; however, every
: time the job in 24x7 has run, it has returned an error.
: I can run this stored procedure via DTS or SQL Query Analyzer and does not
: return an error.
: The error message that is being returned states that "cannot insert NULL
: value into field...column does not allow NULL. INSERT fails.".
: I'm inserting data into a temp table and NULL values are valid.
: Please help. Thanks.

Also make sure your procedure is not created with "WITH RECOMPILE" option. If it is, then every time you run it from 24x7 you will need to call SET ANSI_NULLS ON in the same script.

: The behavior is controlled by ANSI_NULLS database or session option

: Try recompiling your procedure with this option set to on
: SET ANSI_NULLS ON
: go
: CREATE PROC ...
: AS
: ...
: go

: This option controls how the table is created, whether SQL Server uses NULL
: or NOT NULL for table columns.

Thank you for teh response, but the stored procedure is not created
with the "WITH RECOMPILE" option and the "SET ANSI_NULLS ON" option is also set.
Any other thoughts? Thanks again.

: Also make sure your procedure is not created with "WITH RECOMPILE"
: option. If it is, then every time you run it from 24x7 you will need to
: call SET ANSI_NULLS ON in the same script.

Can you post fragment of your stored procedure code where the temp table is created?
Do you set SET ANSI_NULLS ON outside or inside the stored procedure? PS. This must be done outside.

: Thank you for teh response, but the stored procedure is not created
: with the "WITH RECOMPILE" option and the "SET ANSI_NULLS
: ON" option is also set.
: Any other thoughts? Thanks again.

It is done outside the stored procedure. The stored proc is something like:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.p_CRAExtract AS

BEGIN

SET NOCOUNT ON

DELCARE variablename VARCHAR

CREATE TABLE ##CRAExtract
(

fieldname VARCHAR
)

...

END

: Can you post fragment of your stored procedure code where the temp table is
: created?
: Do you set SET ANSI_NULLS ON outside or inside the stored procedure? PS. This
: must be done outside.

Try creating a test procedure like below and see if that works when called from 24x7. Depending on the outcome of the testing I will provide a solution to the NULLs issue

SET QUOTED_IDENTIFIER OFF
go
SET ANSI_NULLS ON
go

CREATE PROCEDURE dbo.p_CRAExtractTest AS
BEGIN
CREATE TABLE #CRAExtractLocal
(

fieldname VARCHAR(30) NULL
)

CREATE TABLE ##CRAExtractGlobal
(

fieldname VARCHAR(30) NULL
)

INSERT INTO #CRAExtractLocal VALUES (NULL)
INSERT INTO ##CRAExtractGlobal VALUES (NULL)
END
go

: It is done outside the stored procedure. The stored proc is something like:
: SET QUOTED_IDENTIFIER OFF
: GO
: SET ANSI_NULLS ON
: GO

: CREATE PROCEDURE dbo.p_CRAExtract AS

: BEGIN

: SET NOCOUNT ON

: DELCARE variablename VARCHAR

: CREATE TABLE ##CRAExtract
: (

: fieldname VARCHAR
: )

: ...

: END

Was able to get the test stored proc to work thru 24x7; however, it error out when trying to create the export file.
Here is my JAL for the 24x7 job:

Dim ( varibalename Number )
Dim ( varibalename2 Number )
Dim ( varibalename3 Number )

DatabaseConnect( "profilename" )
DatabseExecute( "EXEC p_Test", variablename )
DatabaseExport( "##TestGlobal", "D:\Test\testfile.txt", variablename2 )
DatabaseExecute( "DROP TABLE ##TestGlobal", variablename3 )
DatabaseDisconnect

Thanks.

: Try creating a test procedure like below and see if that works when called
: from 24x7. Depending on the outcome of the testing I will provide a
: solution to the NULLs issue

: SET QUOTED_IDENTIFIER OFF
: go
: SET ANSI_NULLS ON
: go

: CREATE PROCEDURE dbo.p_CRAExtractTest AS
: BEGIN
: CREATE TABLE #CRAExtractLocal
: (

: fieldname VARCHAR(30) NULL
: )

: CREATE TABLE ##CRAExtractGlobal
: (

: fieldname VARCHAR(30) NULL
: )

: INSERT INTO #CRAExtractLocal VALUES (NULL)
: INSERT INTO ##CRAExtractGlobal VALUES (NULL)
: END
: go

1. The export error is caused by single back-slashes slashes (please see FAQ section in the on-line help for more)

DatabaseExport( "##TestGlobal", "D:\\Test\\testfile.txt"

2. The way to get your main procedure to work is below

Dim ( rowcount, number )
DatabaseConnect( "profilename" )
DatabseExecute( "SET ANSI_NULLS ON", rowcount )
DatabseExecute( "EXEC dbo.p_CRAExtract", rowcount )
DatabaseExport( "##CRAExtract", "D:\\Test\\testfile.txt", rowcount )
DatabaseExecute( "DROP TABLE ##CRAExtract", rowcount )
DatabaseDisconnect

: Was able to get the test stored proc to work thru 24x7; however, it error out
: when trying to create the export file.
: Here is my JAL for the 24x7 job: Dim ( varibalename Number )
: Dim ( varibalename2 Number )
: Dim ( varibalename3 Number )

: DatabaseConnect( "profilename" )
: DatabseExecute( "EXEC p_Test", variablename )
: DatabaseExport( "##TestGlobal", "D:\Test\testfile.txt",
: variablename2 )
: DatabaseExecute( "DROP TABLE ##TestGlobal", variablename3 )
: DatabaseDisconnect

: Thanks.

OK. That worked with the test stored procedure. So, when I made the change
to the JAL that is used to schedule the other stored procedure, same error was returned:

"cannot insert null value..."

My JAL looks just like you have below.

Of course teh difference between the two stored procs is that the p_CRAExtract stored proc
has a few Cursors and 500 - 600 more lines of code.

: 1. The export error is caused by single back-slashes slashes (please see FAQ
: section in the on-line help for more)

: DatabaseExport( "##TestGlobal", "D:\\Test\\testfile.txt"

: 2. The way to get your main procedure to work is below

: Dim ( rowcount, number )
: DatabaseConnect( "profilename" )
: DatabseExecute( "SET ANSI_NULLS ON", rowcount )
: DatabseExecute( "EXEC dbo.p_CRAExtract", rowcount )
: DatabaseExport( "##CRAExtract", "D:\\Test\\testfile.txt",
: rowcount )
: DatabaseExecute( "DROP TABLE ##CRAExtract", rowcount )
: DatabaseDisconnect

It's got to be something else. Why do you think the problem is in ##CRAExtract table?

Consider emailing the entire procedure to support@softtreetech.com so we can pintpoint the problematic place.

: OK. That worked with the test stored procedure. So, when I made the change
: to the JAL that is used to schedule the other stored procedure, same error
: was returned: "cannot insert null value..."

: My JAL looks just like you have below.

: Of course teh difference between the two stored procs is that the
: p_CRAExtract stored proc
: has a few Cursors and 500 - 600 more lines of code.

Since it continues to error out with the message of cannot insert null value into a field within the ##CRAExtract table.

Just sent the stored proc to the email address below.

Thanks again for your help.

: It's got to be something else. Why do you think the problem is in
: ##CRAExtract table?

: Consider emailing the entire procedure to support@softtreetech.com so we can
: pintpoint the problematic place.