How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Hi

I have Try to Create Stored Procedure in C# with the following structure

[Microsoft.SqlServer.Server.SqlProcedure]

public static void sp_AddImage(Guid ImageID, string ImageFileName, byte[] Image)

{

}

But when I try to deploy that SP to SQL Server Express , The SP Parameters become in the following Stature

@.ImageID uniqueidentifier

@.ImageFileName nvarchar(4000)

@.Image varbinary(8000)

But I don’t want that Data types .. I want it to be in the following format

@.ImageID uniqueidentifier

@.ImageFileName nText

@.Image Image

How Can I Control the data type for each parameter ?

Or

How Can I Change the data type of the parameter for the Deployed Stored Procedure ?

Or

How Can I defined the new Data type ?

Or

What's the solution to this problem ?

Note : I get Error when I try to use Alert Statement to change the parameter Data type for the SP

ALTER PROCEDURE [dbo].[sp_AddImage]

@.ImageID [uniqueidentifier],

@.ImageFileName nText,

@.Image Image

WITH EXECUTE AS CALLER

AS

EXTERNAL NAME [DatabaseAndImages].[StoredProcedures].[sp_AddImage]

GO

And thanks with my best regarding

Fraas

Hi Fraas

You may change parameter types (from nvarchar(4000) to nvarchar(max), for example), but you can't pass values bigger than 8000 bytes directly...

If you need nText or Image handling inside your .NET sp's, you should use .NET wrappers for this SQL types. Consider following procedure definition:

public static void AddImage(Guid ImageID, SqlChars FileName, SqlBytes image)

{

SqlContext.Pipe.Send(String.Format("Image: id {0}, filename length: {1}, image size: {2}", ImageID.ToString(), FileName.Value.Length, image.Length));

}

After deploying this proc, parameter types are uniqueidentifier, nvarchar(max) and varbinary(max).
nvarchar(max) should be used instead of ntext, and varbinary(max) instead of image types accordingly. Ntext and image are deprecated.

simple test (values bigger than 8000 can pass! ;-)):

declare @.id uniqueidentifier, @.image varbinary(max), @.filename nvarchar(max)

select @.filename = (select * from AdventureWorks.Person.Contact for xml auto, elements)

--or select @.filename = (select convert(nvarchar(max), replicate(N'1', 8000)) + convert(nvarchar(max), replicate(N'2', 8000)) )

select @.image = convert(varbinary(max), @.filename)

select @.id = newid()

exec AddImage @.id, @.filename, @.image

WBR, Evergray

--

Words mean nothing...

P.S. Do you really need ntext for storing file name?

|||What you are seeing is Visual Studio's mapping of CLR types to SQL

types, where string/SqlString maps to nvarchar(4000) and byte[] maps to

varbinary(8000).

The ntext and image datatypes in SQL 2005 are now deprecated and you

should use nvarchar(max) and varbinary(max) instead. To automatically

get those types from your CLR code, you should use the SqlChars and SqlBinary types from the SqlTypes namespace instead.

Niels|||

Hi

Thanks for the replies

Surly I will not save the File Name in (nText) Data Type but this is only as example .. I need to use the nText Type to store large text Date in the filed

And I need the Image Data Type to store Large File In the Database

|||Let's just make sure we're all on the same page here. From your response above I'm not usre if you realize (if you do I apologize) that ntext/text and image in SQL 2005 are being deprecated and eventually will go away (they are there now only for backward compatibility). They are being replaced with the nvarchar(max)/varchar(max) and varbinary(max) datatypes. These new datatypes have the same storage capabilities as the old ntext and image, but are much easier to work with

So in SQL 2005 you would use the nvarchar(max) type to store larger text date and varbinary(max) to store a large file. Subsequently, when you use VS to create SQCLR assemblies and stored procs you can use the SqlChars and SqlBinary datatypes in order to get automatic mapping to ntext(max) and varbinary(max) during deployment.

Niels|||

Thanks for all replies .. as I can see it solve my problem when the nVarchar(Max) replace for nText

With my regarding

Fraas