January 18, 2012 at 10:59 am
Hello,
I am passing column with image data type and have to assign it to variable and then pass it into stored procedure.
I am confused as to what data type i should use.
First, what should be data type of the variable that tis image column is assigned to?
Second, what should I type the parameter in SQL Execute task for this image?
Thanks,
Juls
January 18, 2012 at 11:18 am
Are you using SQL 2000 or something later (forum is 2005, but I don't like to assume on that)?
In SQL 2005 and later, you should Varbinary(max) instead of Image as the datatype.
You can have varbinary(max) datatype parameters in SQL Server stored procedures, but there are some limitations when it comes to passing those from server to server. You can't have image parameters (or variables) in SQL Server procs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2012 at 11:48 am
I am actually using SQL server 2008
Developing SSIS package in VS 2008
I didn't create the db or tables. The column has image datatype. I can't change that.
I I set my stored procedure param to varbinary(max) datatype, what should the type of my SSIS variable be that reads in this image clumn.
And What should I set parameter mapping on sql execute task whn I load this variable into parameter to execute stored proc?
January 19, 2012 at 7:30 am
It'll need to be image in SSIS, and varbinary(max) in the parameter list of the stored proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 19, 2012 at 7:34 am
I don't see an option of "image" (or varbinary) in SSIS.
What should I use?
January 20, 2012 at 6:21 am
It's called DT_IMAGE in SSIS. As per: http://msdn.microsoft.com/en-us/library/ms141036.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 20, 2012 at 8:14 am
In data Flow, yes. But it is not an option when creating a variable, or when assigning parameters in sql task.
January 20, 2012 at 8:20 am
Not sure what to do in that case. Maybe someone will stop by with experience in that.
I'm used to pulling images in via the file system, not via a parameter on a proc. Wish I could help more, but I'm not used to working it that way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply