February 4, 2014 at 6:45 pm
I am using Data Compare which does the following:
UPDATE [dbo].[EmployeeImage]
SET [ImageData].WRITE(0xffd8ffe000104a46494600010001006000600000fffe001f4c45...,
NULL, NULL)
WHERE [EmployeeImageID] = 9
when I go back later and look at the data I have the following with 2 record:
EmployeeImageIDEmployeeIDImageData
900xFFD8FFE000104A46494600010200000100010000FFE0001...
279040xFFD8FFE000104A46494600010001006000600000FFFE001F...
I was trying to move the image from EmployeeImageID (27) to EmployeeImageID (9) but it doesn't move. The results are after running the update about 10 times. If I add in another field to get updated, it updates correctly (but not the image).
Thanks,
Tom
February 4, 2014 at 10:40 pm
tshad (2/4/2014)
I am using Data Compare which does the following:UPDATE [dbo].[EmployeeImage]
SET [ImageData].WRITE(0xffd8ffe000104a46494600010001006000600000fffe001f4c45...,
NULL, NULL)
WHERE [EmployeeImageID] = 9
when I go back later and look at the data I have the following with 2 record:
EmployeeImageIDEmployeeIDImageData
900xFFD8FFE000104A46494600010200000100010000FFE0001...
279040xFFD8FFE000104A46494600010001006000600000FFFE001F...
I was trying to move the image from EmployeeImageID (27) to EmployeeImageID (9) but it doesn't move. The results are after running the update about 10 times. If I add in another field to get updated, it updates correctly (but not the image).
Thanks,
Tom
Talk about sparse on details...
You might want to take a look at the documentation for using .WRITE in an update. http://technet.microsoft.com/en-us/library/ms177523.aspx
From that page:
If @Offset is NULL, the update operation appends expression at the end of the existing column_name value and @Length is ignored.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2014 at 6:05 am
The update statement you have is modifying the row where the ID = 9. If you want to move the value from 27 to 9, try writing an UPDATE FROM statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2014 at 10:42 am
Not sure what is sparse about my question.
I am just trying to take an image from one record and move it to another. I need to script it as the client is does not have these records in their system.
I took an empty database and ran Sql Data Compare to get their code (which doesn't do exactly what I want but the format and the image is there in the insert statement. So I used the Update statement (which is inside of an "EXEC") and replaced its image with the correct one but will not update it and gives me no error message.
As I said, I also added another field to the update statement and it was applied, so I know the update statement worked but the image wasn't replaced.
Here is the Data Compare code that I used (minus much of the image data as it is not necessary and way too long).
/*
Script created by SQL Data Compare version 7.1.0.230 from Red Gate Software Ltd at 2/5/2014 9:32:48 AM
Run this script on PDSA-DEVSQL2008.BNBTeamMemberqa
This script will make changes to PDSA-DEVSQL2008.BNBTeamMemberqa to make it the same as PDSA-DEVSQL2008.BNBTeamMemberDEV
Note that this script will carry out all DELETE commands for all tables first, then all the UPDATES and then all the INSERTS
It will disable foreign key constraints at the beginning of the script, and re-enable them at the end
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
BEGIN TRANSACTION
-- Add 2 rows to [dbo].[EmployeeImage]
SET IDENTITY_INSERT [dbo].[EmployeeImage] ON
EXEC(N'INSERT INTO [dbo].[EmployeeImage] ([EmployeeImageID], [EmployeeID], [ImageData], [ContentType], [InsertID], [InsertDate], [LastUpdateDate], [LastUpdateID], [ConcurrencyID]) VALUES (9, 0, 0xffd8ffe000104a46494600010200000100010000ffe0001c4f6361642452..., ''image/jpg'', ''admin'', ''2013-09-06 16:09:03.460'', NULL, NULL, NULL)')
EXEC(N'UPDATE [dbo].[EmployeeImage] SET [ImageData].WRITE(0xaaccb37381220ea09a91b72daa789c77f8648115cf384189711bcaa0b...,NULL,NULL) WHERE [EmployeeImageID]=9
')
EXEC(N'INSERT INTO [dbo].[EmployeeImage] ([EmployeeImageID], [EmployeeID], [ImageData], [ContentType], [InsertID], [InsertDate], [LastUpdateDate], [LastUpdateID], [ConcurrencyID]) VALUES (27, 904, 0xffd8ffe000104a46494600010001006000600000fffe001f4c45414420546563..., ''image/jpg'', ''admin'', ''2014-02-04 14:47:02.220'', NULL, NULL, NULL)')
SET IDENTITY_INSERT [dbo].[EmployeeImage] OFF
COMMIT TRANSACTION
GO
-- Reseed identity on [dbo].[EmployeeImage]
DBCC CHECKIDENT('[dbo].[EmployeeImage]', RESEED, 27)
GO
Tom
February 5, 2014 at 12:42 pm
Well as I quoted previously and tried to point out from BOL, if you pass NULL as the Offset it will APPEND the existing data.
Since we don't have table structures to work with I suspect that all you need to do is change the @Offset from NULL to 0.
EXEC(N'UPDATE [dbo].[EmployeeImage] SET [ImageData].WRITE(0xaaccb37381220ea09a91b72daa789c77f8648115cf384189711bcaa0b...,0,NULL) WHERE [EmployeeImageID]=9')
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply