BLOB

Hi all!
I want to save some .exe files in a database, but I dont get this BLOB thing

How do I declare a BLOB in the database table and as a variable in a stored
procedure?
Regards
MarreBLOB (Binary Large OBject) is a general term. You will find no data type in
SQL that is a blob per say. Depending on the version of SQL, you should be
using an "image" field (2000) or varbinary(MAX) in SQL 2005.
-Andrew
"Marre" <news@.supremelink.se> wrote in message
news:uPTPLqS4FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hi all!
> I want to save some .exe files in a database, but I dont get this BLOB
> thing
> How do I declare a BLOB in the database table and as a variable in a
> stored procedure?
>
> Regards
> Marre
>|||Hi Andrew!
Thanx for youre answer! As I understand it, i can use binary and varbinary
for files up to 8kb and if I want to work with larger files, I should use
Image.
Does this sound right to you?
Regards
Marre
"Andrew Robinson" <nemoby@.nospam.nospam> wrote in message
news:ugA$rmV4FHA.1276@.TK2MSFTNGP09.phx.gbl...
> BLOB (Binary Large OBject) is a general term. You will find no data type
> in SQL that is a blob per say. Depending on the version of SQL, you should
> be using an "image" field (2000) or varbinary(MAX) in SQL 2005.
> -Andrew
> "Marre" <news@.supremelink.se> wrote in message
> news:uPTPLqS4FHA.1864@.TK2MSFTNGP12.phx.gbl...
>|||Yes.
Image and VARCHAR(MAX) / VARBINARY(MAX) have a performace hit. Store it in a
varbinary(8000) if you can.
-A
"Marre" <news@.supremelink.se> wrote in message
news:%23awky%23f4FHA.3276@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew!
> Thanx for youre answer! As I understand it, i can use binary and varbinary
> for files up to 8kb and if I want to work with larger files, I should use
> Image.
> Does this sound right to you?
> Regards
> Marre
> "Andrew Robinson" <nemoby@.nospam.nospam> wrote in message
> news:ugA$rmV4FHA.1276@.TK2MSFTNGP09.phx.gbl...
>|||Thank you for helping me out here!
/Marre
"Andrew Robinson" <nemoby@.nospam.nospam> wrote in message
news:%23zaMkhn4FHA.472@.TK2MSFTNGP15.phx.gbl...
> Yes.
> Image and VARCHAR(MAX) / VARBINARY(MAX) have a performace hit. Store it in
> a varbinary(8000) if you can.
> -A
> "Marre" <news@.supremelink.se> wrote in message
> news:%23awky%23f4FHA.3276@.TK2MSFTNGP10.phx.gbl...
>|||Can you give more info on the performance hit?
Thanks.
"Andrew Robinson" wrote:

> Yes.
> Image and VARCHAR(MAX) / VARBINARY(MAX) have a performace hit. Store it in
a
> varbinary(8000) if you can.
> -A
> "Marre" <news@.supremelink.se> wrote in message
> news:%23awky%23f4FHA.3276@.TK2MSFTNGP10.phx.gbl...
>
>|||"yluo" <yluo@.plexus-group.net> wrote in message
news:0818E574-53A7-4936-9C36-CA8BC3F2B957@.microsoft.com...[vbcol=seagreen]
> Can you give more info on the performance hit?
> Thanks.
>
> "Andrew Robinson" wrote:
>
A varbinary(8000) column will live on the data page itself as part of the
row.
VARCHAR(MAX) and VARBINARY(MAX) will use BLOB objects. In the row itself,
a 16-byte pointer will now point at separate data pages which store the BLOB
object.
That = more reads, possibly more unused space etc.
Rick Sawtell
MCT, MCSD, MCDBA

BLOB

Hi all!
I want to save some .exe files in a database, but I dont get this BLOB thing

How do I declare a BLOB in the database table and as a variable in a stored
procedure?
Regards
Marre
BLOB (Binary Large OBject) is a general term. You will find no data type in
SQL that is a blob per say. Depending on the version of SQL, you should be
using an "image" field (2000) or varbinary(MAX) in SQL 2005.
-Andrew
"Marre" <news@.supremelink.se> wrote in message
news:uPTPLqS4FHA.1864@.TK2MSFTNGP12.phx.gbl...
> Hi all!
> I want to save some .exe files in a database, but I dont get this BLOB
> thing
> How do I declare a BLOB in the database table and as a variable in a
> stored procedure?
>
> Regards
> Marre
>
|||Hi Andrew!
Thanx for youre answer! As I understand it, i can use binary and varbinary
for files up to 8kb and if I want to work with larger files, I should use
Image.
Does this sound right to you?
Regards
Marre
"Andrew Robinson" <nemoby@.nospam.nospam> wrote in message
news:ugA$rmV4FHA.1276@.TK2MSFTNGP09.phx.gbl...
> BLOB (Binary Large OBject) is a general term. You will find no data type
> in SQL that is a blob per say. Depending on the version of SQL, you should
> be using an "image" field (2000) or varbinary(MAX) in SQL 2005.
> -Andrew
> "Marre" <news@.supremelink.se> wrote in message
> news:uPTPLqS4FHA.1864@.TK2MSFTNGP12.phx.gbl...
>
|||Yes.
Image and VARCHAR(MAX) / VARBINARY(MAX) have a performace hit. Store it in a
varbinary(8000) if you can.
-A
"Marre" <news@.supremelink.se> wrote in message
news:%23awky%23f4FHA.3276@.TK2MSFTNGP10.phx.gbl...
> Hi Andrew!
> Thanx for youre answer! As I understand it, i can use binary and varbinary
> for files up to 8kb and if I want to work with larger files, I should use
> Image.
> Does this sound right to you?
> Regards
> Marre
> "Andrew Robinson" <nemoby@.nospam.nospam> wrote in message
> news:ugA$rmV4FHA.1276@.TK2MSFTNGP09.phx.gbl...
>
|||Thank you for helping me out here!
/Marre
"Andrew Robinson" <nemoby@.nospam.nospam> wrote in message
news:%23zaMkhn4FHA.472@.TK2MSFTNGP15.phx.gbl...
> Yes.
> Image and VARCHAR(MAX) / VARBINARY(MAX) have a performace hit. Store it in
> a varbinary(8000) if you can.
> -A
> "Marre" <news@.supremelink.se> wrote in message
> news:%23awky%23f4FHA.3276@.TK2MSFTNGP10.phx.gbl...
>
|||Can you give more info on the performance hit?
Thanks.
"Andrew Robinson" wrote:

> Yes.
> Image and VARCHAR(MAX) / VARBINARY(MAX) have a performace hit. Store it in a
> varbinary(8000) if you can.
> -A
> "Marre" <news@.supremelink.se> wrote in message
> news:%23awky%23f4FHA.3276@.TK2MSFTNGP10.phx.gbl...
>
>
|||"yluo" <yluo@.plexus-group.net> wrote in message
news:0818E574-53A7-4936-9C36-CA8BC3F2B957@.microsoft.com...[vbcol=seagreen]
> Can you give more info on the performance hit?
> Thanks.
>
> "Andrew Robinson" wrote:
A varbinary(8000) column will live on the data page itself as part of the
row.
VARCHAR(MAX) and VARBINARY(MAX) will use BLOB objects. In the row itself,
a 16-byte pointer will now point at separate data pages which store the BLOB
object.
That = more reads, possibly more unused space etc.
Rick Sawtell
MCT, MCSD, MCDBA

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptest
If you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest
|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>
|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:
> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> >
> > Hi,
> >
> > I'm doing dynamic queries. When I try to insert blank or null date data
> > into a table, I'm getting the default value of '01-01-2000' or
> > '01-01'1900'
> > respectively. What I really want it to do is insert a null into the
> > table.
> >
> > I created some sample code below. Can anyone help?
> >
> > create table #mytemptest
> > (testdate smalldatetime null
> > )
> >
> > SET CONCAT_NULL_YIELDS_NULL OFF
> >
> > declare @.query as varchar(1000)
> > declare @.inputdate as smalldatetime
> >
> > select @.inputdate=''
> > select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select @.inputdate=null
> > select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> > select @.query
> > insert into #mytemptest(testdate)
> > exec(@.query)
> >
> > select * from #mytemptest
> > drop table #mytemptest
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10), 'NULL') + ''''

Blank or null datetime variable converts to an actual date

Hi,
I'm doing dynamic queries. When I try to insert blank or null date data
into a table, I'm getting the default value of '01-01-2000' or '01-01'1900'
respectively. What I really want it to do is insert a null into the table.
I created some sample code below. Can anyone help?
create table #mytemptest
(testdate smalldatetime null
)
SET CONCAT_NULL_YIELDS_NULL OFF
declare @.query as varchar(1000)
declare @.inputdate as smalldatetime
select @.inputdate=''
select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select @.inputdate=null
select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
select @.query
insert into #mytemptest(testdate)
exec(@.query)
select * from #mytemptest
drop table #mytemptestIf you insert a NULL it will remain as a NULL...
select @.inputdate=null
select @.query='select null'
select @.query
insert into #mytemptest(testdate)
exec(@.query)
HTH. Ryan
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
> Hi,
> I'm doing dynamic queries. When I try to insert blank or null date data
> into a table, I'm getting the default value of '01-01-2000' or
> '01-01'1900'
> respectively. What I really want it to do is insert a null into the
> table.
> I created some sample code below. Can anyone help?
> create table #mytemptest
> (testdate smalldatetime null
> )
> SET CONCAT_NULL_YIELDS_NULL OFF
> declare @.query as varchar(1000)
> declare @.inputdate as smalldatetime
> select @.inputdate=''
> select @.query='select ''' + convert(varchar(10),@.inputdate,10) + ''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select @.inputdate=null
> select @.query='select '''+ convert(varchar(10),@.inputdate,10)+''''
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
> select * from #mytemptest
> drop table #mytemptest|||Thanks Ryan,
I thought as much. I was trying to avoid that because I'm building queries
with lots of variables, so I'll have to do a bunch of IF statements. Thanks
for your time!
-Trish
"Ryan" wrote:

> If you insert a NULL it will remain as a NULL...
> select @.inputdate=null
> select @.query='select null'
> select @.query
> insert into #mytemptest(testdate)
> exec(@.query)
>
> --
> HTH. Ryan
> "Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
> news:69B2C58B-EA97-4721-9423-B88EC39527AD@.microsoft.com...
>
>|||Trish,
Instead of lots of IF statements, can you just wrap your convert
statement with isNULL()? This will put the value 'NULL' right into
your query when @.inputDate is null...
SELECT @.query = 'select ''' +
isNull(Convert(varchar(10),@.inputdate,10
), 'NULL') + ''''