DocumentID not returned in Adventureworks

The Large Binary Object example installed with SQL Server 2005 has a table with the DocumentID as the key. When the row is inserted it should return the DocumentID that was created. When you debug the example stored procedure you can see the documentID is created and correct. When you run the sample unmodified it always returns -1 as the documentID. It appears the stored procedure is catching an exception and assigning the documentID a value of -1 as seen here in the Adventureworks LOB sample sored procedure (below).

However, it appears that something with the datbase engine or proce is broken and Microsoft knew this becasue the assignment to documentID upon return from the stored proc is commented out (see below). In fact the whole sample is faked because when you follow it you realize that it is reading into the database one document and writing out one that already existed in the database..

Does anyone know how to get the DocumentID back from the stored procedure that inserted the binary object into the Documents table of Adventureworks?

Thanks in advance.

Portion of Adventure works usp_InsertDocument

RAISERROR ('Insert failed.', 16, 1);

END TRY

BEGIN CATCH

SET @.DocumentID = -1;

RETURN(1);

END CATCH;

END -- END of usp_InsertDocument

;

Portion of LargeBinaryObject.cs

using (SqlConnection sqlConn = new SqlConnection(connectionString))

{

using (SqlCommand sprocCommand = new SqlCommand("[Production].[usp_InsertDocument]", sqlConn))

{

sqlConn.Open();

sprocCommand.CommandType = CommandType.StoredProcedure;

// Add time to the title because there is an unique constraint on this column.

sprocCommand.Parameters.Add(new SqlParameter("@.Title", SqlDbType.NVarChar, 50));

sprocCommand.Parameters[0].Value = fileName + DateTime.Now.TimeOfDay.ToString();

sprocCommand.Parameters.Add(new SqlParameter("@.FileName", SqlDbType.NVarChar, 400));

sprocCommand.Parameters[1].Value = fullFileName;

sprocCommand.Parameters.Add(new SqlParameter("@.FileExtension", SqlDbType.NVarChar, 8));

sprocCommand.Parameters[2].Value = fileExtension;

sprocCommand.Parameters.Add(new SqlParameter("@.Status", SqlDbType.TinyInt));

sprocCommand.Parameters[3].Value = 1;

sprocCommand.Parameters.Add(new SqlParameter("@.Document", SqlDbType.Image));

sprocCommand.Parameters[4].Value = bytes;

sprocCommand.Parameters.Add(new SqlParameter("@.DocumentID", SqlDbType.Int));

sprocCommand.Parameters[5].Direction = ParameterDirection.Output;

sprocCommand.ExecuteNonQuery();

//int DocumentID = (int)sprocCommand.Parameters[5].Value;

}

This here is a pretty easy one:

SqlCommand cmd = new SqlCommand("usp_YourProc");

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = conn;

SqlParameter param_InputoutputTest = new SqlParameter("@.InputoutputTest", SqlDbType.Int);

param_InputoutputTest.Value = "1";

param_InputoutputTest.Direction = ParameterDirection.InputOutput;

SqlParameter param_ReturntTest = new SqlParameter("@.ReturntTest", SqlDbType.Int);

param_ReturntTest.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(param_InputoutputTest);

cmd.Parameters.Add(param_ReturntTest);

conn.Open();

cmd.ExecuteNonQuery();

Console.WriteLine(cmd.Parameters["@.ReturntTest"].Value.ToString());

Console.WriteLine(cmd.Parameters["@.InputoutputTest"].Value.ToString());

conn.Close();

ALTER PROCEDURE usp_YourProc

(

@.InputoutputTest INT OUTPUT

)

AS

SELECT @.InputoutputTest = -1

RETURN 2

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-