Image field in Trigger

Hi , i have a trigger that start on a record insert . . .This take de ID of the inserted record and start to fill other table in the database.In this triggher i have to update an image field of a table , getting the value from another table .I've casted this value in varbinary , but var binary can take only 8000 byte , and this fileis bigger than 8000 byte . . .How can i proced to solve the problem ?ThanksIf you are using SQL Server 2005 you can use the VARBINARY(MAX) type. if you are on a version prior to SQL Server 2k5 you can try to code your statement set based, eliminating the need for interim variables.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Hi , i'm using Sql Server 2000 . . . How can i eliminate the need for interim variables ?I've tryed to do this :UPDATA table1 SET img1 = (SELECT img2 FROM table2 where ...) where ...But the message is that i can use image field in trigger ...|||Hi,

did you have a look on:

http://msdn2.microsoft.com/en-us/library/ms189799.aspx

"In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is set to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.

If the compatibility level is 80 or higher, SQL Server allows for the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views. "

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

image datatype - get data out and create insert statement

I am needing to get the data out of a table that has an image datatype, and create an insert statement so that the data can be inserted into other databases(to be used as part of an upgrade script).

I have used the procedure InsertGenerator in the past to create insert statements out of tables that have data. This is the first time I have come across the image datatype. The image part of the procedure looks like this: (@.dataType, @.colName, and @.stringData are nvarchar)

IF @.dataType='image'
BEGIN
SET @.stringData=@.stringData+'''''''''+
isnull(cast(convert(varbinary,'+@.colName+')
as varchar(6)),''0'')+'''''',''+'
END

It successfully creates the insert statement. The insert statement runs successfully, but I am not sure if the image itself is ok. When I use the related application, it crashes and I cannot tell if it is because of the image. When I run a sql trace the last statement ran is pulling back the image as well as a few other columns. When I run the trace statement in query analyzer, it works, but in query analyzer it isnt displaying the image.

My question is will the above statement successfully convert an image to a value that can be inserted into another table and the image recreated when you run the application? Is there something else I need to do to get the image value in a table out so that it can be used in an insert statment.

BTW this is on SQL 2000.

Thank you so much,

Tracey

This thread (found by just looking down the message list without having to even change pages -WOW), may give you the information you desire.

Using a stored procedure to reinsert an image

|||

Perhaps I am not understanding, I am using a script to create and insert statement an example is below. The FileBinary column is the image. I cannot tell if this is correctly extracted as am image. The insert is successful when ran in query analyzer, but the image doesnt display in the application, the application crashes, so I cant tell if it is the image or not.
Creating an insert statement to the same database is not what Im trying to do. I am trying to create an insert statement that can be ran on other like databases not on the network, that are needing this piece of information for the latest version of our application.

insert into JTFile (FileID, Path, Name, Extension, Type, Description, HashValue, Seed, RecordActive,FileBinary) values('0588dbda3f4d483d84259626c251f072','','','.rpt','','test','','2055291722',1,'??')

iif problem brings back #Error

I'm pulling a "Number (8)" data type field for a date (formatted YYYYMMDD) from a Oracle 9i database. When the value is inserted into the database it is set a zero (A non-null database for the most part), otherwise it is for example 20061224. If the date field has a value the date is displayed correctly, but if it is zero then I get the "#Error" message.

Here is one of many iif expressions I've tried.

=iif(Len(CStr(Fields!DTE_MAILED.Value)) = 8,

((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4))

,

Nothing)

Now I've returned the value with out any formatting done to the string, and it will return "0"(zero) or a number. I've returned the lengths of the returning value and it comes back "1" or "8". I read a lot of previous posts and I thought at first that it was because I was trying to do a substring function on the zero value getting a index error. So I've changed the iif test condition many different ways with no prevail. I read a previous post where someone ended up doing his work in his SQL, but I would like to find out how to do this in the report. This is a simple expression, so I feel like there is something obvious I don't know maybe something with the format mask.

All help will be appreciated!

hi nwyork,

i don't have Oracle db so i can't try.

but as far as the error that i've encountered,

e.g. iif (condition, true exp, false exp)

you might want to put your true exp and false exp returning the same data types...

if your true exp has CStr then the false exp should put CStr

|||

Thanks for replying,

I thought that might be it so I tried returning (""), and after reading your post I tried using CStr function in the false part but I still get the error. I ran out of time so I used SubStr, Decode, and Length functions in the SQL.

|||

Hi nwyork

The first problem im seeing is in the boolean check.
You are using 2 expressions on the field.....(Cstr and Len)
If the field containes a null value there will be problems as
Len(Null) = #error

Why not try restructuring your iif statement as follows:

=iif( Fields!DTE_MAILED.Value = Nothing
,0
,((CStr(Fields!DTE_MAILED.Value)).Substring(4,2) + "/" + Right(CStr(Fields!DTE_MAILED.Value), 2) + "/" + Left(CStr(Fields!DTE_MAILED.Value), 4)))

I'm not sure if this is specific to your datasource as i've never used Oracle before but it should still do
the trick. You can even add a second iif in the false part of the expression to make sure that the length is 8
,because as soon as the expression hits false part you know that there is no
null's and you should not get an error when trying to use expressions on the the field value.

If I was unclear in any way, be sure to point it out.

It may be 2 late now but it may help when a similar problem pops up in the future ;P

G