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 data type, doesn't return the data on selected

I don't know much about the Image data type. When I query a record and get
only 16bits of the an Imaeg field, instead of the actual data?
Any documentation and guidance for this problem? It seems not on the Books
Online.
Thanks very much.Check out the 'Retrieving ntext, text, or image Values' topic in the SQL
Server Books Online.
HTH
Jerry
"zhaounknown" <zhaounknown@.discussions.microsoft.com> wrote in message
news:686F627B-885B-4C6E-9134-4148CFFD824F@.microsoft.com...
>I don't know much about the Image data type. When I query a record and get
> only 16bits of the an Imaeg field, instead of the actual data?
> Any documentation and guidance for this problem? It seems not on the Books
> Online.
> Thanks very much.|||Thanks for your reply.
I checked the topic of "retrieving ntext, ...".
What it says is :
The full amount of data is returned if the length is less than TEXTSIZE.
The DB-Library API also supports a dbtextsize parameter that controls the
length of ntext, text, and image data that can be selected. The Microsoft OL
E
DB Provider for SQL Server and the SQL Server ODBC driver automatically set
@.@.TEXTSIZE to its maximum of 2 GB.
I am using MSDE, and @.@.TextSize is 64512. However, my object's length counts
56424, which is less than 64512 and is supposed to return the data in full.
But it seems doesn't.
"Jerry Spivey" wrote:

> Check out the 'Retrieving ntext, text, or image Values' topic in the SQL
> Server Books Online.
> HTH
> Jerry
> "zhaounknown" <zhaounknown@.discussions.microsoft.com> wrote in message
> news:686F627B-885B-4C6E-9134-4148CFFD824F@.microsoft.com...
>
>|||I found the problem is the data has not been stored into the SQL Server.
The reason is:
I create a update command using ADO.Net wizard by entering the command text
manually in the wizard, which will generate the Image field to have size at
16, instead of 2147483647, which causes the problem.
Hope, this may help anyone.
"zhaounknown" wrote:
> Thanks for your reply.
> I checked the topic of "retrieving ntext, ...".
> What it says is :
> The full amount of data is returned if the length is less than TEXTSIZE.
> The DB-Library API also supports a dbtextsize parameter that controls the
> length of ntext, text, and image data that can be selected. The Microsoft
OLE
> DB Provider for SQL Server and the SQL Server ODBC driver automatically se
t
> @.@.TEXTSIZE to its maximum of 2 GB.
> I am using MSDE, and @.@.TextSize is 64512. However, my object's length coun
ts
> 56424, which is less than 64512 and is supposed to return the data in full
.
> But it seems doesn't.
>
> "Jerry Spivey" wrote:
>

Im flooding my SQL Server with INSERT / UPDATE requests. How do I optimize?

I have an application that calculates a bunch of numbers and then inserts them into a table (or updates a record in the table if it exists). In my test environment it is issuing 100 insert or update requests to the server per second and it could run for several hours.
After a the first several hundred requests, the SQL server is bogging down (processor at 90-100%) and the application slows down while it waits on SQL to update the database.
What would be the best way to optimize this app? Would it help to loop through all my insert/update requests and then send them as one big batch of statements to the server (per 1000 requests or something)? Is there a better way of doing this?
Thanks!

Here are two approachs we did:

1. Queue up all insert/update, and run batch job as one transaction in one connection, next release of our Lattice.DataMapper will support run batch job in a seperate thread at certain time you define, see midnight tonight.
2. You can send a xml doc with all your insert/update data over to SQL server and call stored procedures using SQL server xml support. So only one stored procedure call you are done.