asked on

BCP error

Hello,

I have written some script that runs the BCp to execute a stored procedure and output the results to a file.

DECLARE	@return_value int
DECLARE @strbcpcmd NVARCHAR(2000)


set @strbcpcmd = 'bcp "exec [BPSD].[dbo].[SKL_XML_Export_InitialLoad] @StartDate = ''2013-11-01'', @EndDate = ''2014-09-30''" queryout "d:\nasher\bpsd\2013.xml" -w -C OEM -t"$" -T -S -U xxx -P xxxxx'+@@servername
select @strbcpcmd
EXEC master..xp_cmdshell @strbcpcmd

Open in new window

It works fine on my local SQL Server 2012 machine but when i deploy it to the live server (which is SQL Server 2008) i get the following error:

SQLState = 37000, NativeError = 5302
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Mutator 'modify()' on '@Aviseringsinformation' cannot be called on a null value.
NULL

Open in new window

I have looked for this error but can not find any reference to it. I suspect that it could be a permission error because i can run the sp manually. Also the second line refers to update a node that does not exist but i know that that it should.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

soozh

ASKER

ok. good idea. how would you write log statements? I normally just use a select statement... this ok here as well when using the BCP?

ste5an

It is not BCP what I had in mind. You need some logging in your stored procedure. You may use xp_logevent (Transact-SQL) or use your own table and insert logging rows into it. When you use your own table, then you need and table variable and a TRY..CATCH block. Otherwise your log table would be affected by rollbacks. E.g.

USE Test;
GO

CREATE TABLE dbo.Log
(
	LogID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	LogTimeStamp DATETIME NOT NULL,
	LogMessage NVARCHAR(MAX)
);
GO

CREATE PROCEDURE dbo.MySproc
AS
	SET NOCOUNT ON;
	SET XACT_ABORT ON;

	DECLARE @Aviseringsinformation XML; -- What type ever.

	DECLARE @Log TABLE 
	(
		LogTimeStamp DATETIME DEFAULT ( GETDATE() ),
		LogMessage NVARCHAR(MAX)
	);

	INSERT INTO @Log ( LogMessage ) VALUES ( 'Begin..' );

	BEGIN TRY
	    
		-- Your code here. 
		-- Place one or more log inserts around the your statemten containing the modify().
		-- Output also the value of your variable @Aviseringsinformation.
		INSERT INTO @Log ( LogMessage ) VALUES ( '@Aviseringsinformation is ' + CAST( @Aviseringsinformation AS NVARCHAR(MAX)) );
		-- Run here the modify() statement.
		
		INSERT INTO @Log ( LogMessage ) VALUES ( 'Done.' );
	END TRY
	BEGIN CATCH
		-- You may use the ERROR_xxx() functions to get additional information.
		INSERT INTO @Log ( LogMessage ) VALUES ( 'Error.' );
	END CATCH;

	INSERT INTO dbo.Log ( LogTimeStamp, LogMessage)
		SELECT	LogTimeStamp, 
			LogMessage
		FROM	@Log;

Open in new window

soozh

ASKER

You are correct... it did not exist... fixed now!