nvarchar / varchar(MAX) question

Hi all,
I'm curious about the efficiency of the varchar datatype in general. I
understand the difference between varchar and nvarchar (unicode), and
I understand what the new nvarchar(max) is in sql 2005. What I don't
understand is exactly how varchar uses server resources. Here's an
example to explain my confusion.
Let's say I have a database with a column that is varchar(200). Let's
say that users cannot enter in data over 100 characters long due to a
restriction from a client web app. This means I could have used
varchar(100). Since varchar (unlike char) can grow and shrink as
needed, have I lost anything by using varchar(200) instead of
varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
performance/storage by using this data type if my data is not going to
be that large?
Thank you!
I would not use nvarchar(max) on an nvarchar(200) column. It adds 2 bytes
per row. If you have "large value types out of row" turned off, it should
act the same as normal nvarchar but there may be other internal factors.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<mattdaddym@.gmail.com> wrote in message
news:b61b9ed4-7e6d-48f0-a533-ae2d2879cb46@.e4g2000hsg.googlegroups.com...
> Hi all,
> I'm curious about the efficiency of the varchar datatype in general. I
> understand the difference between varchar and nvarchar (unicode), and
> I understand what the new nvarchar(max) is in sql 2005. What I don't
> understand is exactly how varchar uses server resources. Here's an
> example to explain my confusion.
> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
> Thank you!
|||> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)?
No. However, keep in mind that your client app is not the only way to get
data into that table! If you only want 100 characters, then enforce it by
using the correct data type (or at the very least a check constraint). You
can update both database and client app(s) later if you need to expand the
data type to accommodate more characters.

> Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
I would never use a MAX type unless I *knew* I was going to require capacity
beyond the 4K or 8K limit. By default these values are stored off-row, so
that can affect efficiency and I/O issues if the query has to go all over
the place to assemble rows. You can override this setting but, as Jason
points out, there may very well be other internal factors, in addition to
the fact that you can't index, etc.
|||MAX columns can be in indexes as INCLUDED columns.
Not that I'm recommending it, but I want to make sure we're clear here.
I do agree with what Aaron said.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl...
....
> I would never use a MAX type unless I *knew* I was going to require
> capacity beyond the 4K or 8K limit. By default these values are stored
> off-row, so that can affect efficiency and I/O issues if the query has to
> go all over the place to assemble rows. You can override this setting
> but, as Jason points out, there may very well be other internal factors,
> in addition to the fact that you can't index, etc.
>
|||Sounds good. Thank you everyone.
On Feb 4, 5:45Xpm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> MAX columns can be in indexes as INCLUDED columns.
> Not that I'm recommending it, but I want to make sure we're clear here.
> I do agree with what Aaron said.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelaney.com
> "Aaron Bertrand [SQL Server MVP]" <ten...@.dnartreb.noraa> wrote in messagenews:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl. ..
> ...
>
>
>
> - Show quoted text -
|||Sure, I should have been more explicit, can't index in the traditional way
(e.g. CREATE INDEX foo ON table.column).

> MAX columns can be in indexes as INCLUDED columns.

nvarchar / varchar(MAX) question

Hi all,
I'm curious about the efficiency of the varchar datatype in general. I
understand the difference between varchar and nvarchar (unicode), and
I understand what the new nvarchar(max) is in sql 2005. What I don't
understand is exactly how varchar uses server resources. Here's an
example to explain my confusion.
Let's say I have a database with a column that is varchar(200). Let's
say that users cannot enter in data over 100 characters long due to a
restriction from a client web app. This means I could have used
varchar(100). Since varchar (unlike char) can grow and shrink as
needed, have I lost anything by using varchar(200) instead of
varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
performance/storage by using this data type if my data is not going to
be that large?
Thank you!I would not use nvarchar(max) on an nvarchar(200) column. It adds 2 bytes
per row. If you have "large value types out of row" turned off, it should
act the same as normal nvarchar but there may be other internal factors.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
<mattdaddym@.gmail.com> wrote in message
news:b61b9ed4-7e6d-48f0-a533-ae2d2879cb46@.e4g2000hsg.googlegroups.com...
> Hi all,
> I'm curious about the efficiency of the varchar datatype in general. I
> understand the difference between varchar and nvarchar (unicode), and
> I understand what the new nvarchar(max) is in sql 2005. What I don't
> understand is exactly how varchar uses server resources. Here's an
> example to explain my confusion.
> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)? Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
> Thank you!|||> Let's say I have a database with a column that is varchar(200). Let's
> say that users cannot enter in data over 100 characters long due to a
> restriction from a client web app. This means I could have used
> varchar(100). Since varchar (unlike char) can grow and shrink as
> needed, have I lost anything by using varchar(200) instead of
> varchar(100)?
No. However, keep in mind that your client app is not the only way to get
data into that table! If you only want 100 characters, then enforce it by
using the correct data type (or at the very least a check constraint). You
can update both database and client app(s) later if you need to expand the
data type to accommodate more characters.
> Similarly, what about nvarchar(MAX). Am I loosing
> performance/storage by using this data type if my data is not going to
> be that large?
I would never use a MAX type unless I *knew* I was going to require capacity
beyond the 4K or 8K limit. By default these values are stored off-row, so
that can affect efficiency and I/O issues if the query has to go all over
the place to assemble rows. You can override this setting but, as Jason
points out, there may very well be other internal factors, in addition to
the fact that you can't index, etc.|||MAX columns can be in indexes as INCLUDED columns.
Not that I'm recommending it, but I want to make sure we're clear here.
I do agree with what Aaron said.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl...
...
> I would never use a MAX type unless I *knew* I was going to require
> capacity beyond the 4K or 8K limit. By default these values are stored
> off-row, so that can affect efficiency and I/O issues if the query has to
> go all over the place to assemble rows. You can override this setting
> but, as Jason points out, there may very well be other internal factors,
> in addition to the fact that you can't index, etc.
>|||Sounds good. Thank you everyone.
On Feb 4, 5:45=A0pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> MAX columns can be in indexes as INCLUDED columns.
> Not that I'm recommending it, but I want to make sure we're clear here.
> I do agree with what Aaron said.
> --
> HTH
> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://blog.kalendelan=
ey.com
> "Aaron Bertrand [SQL Server MVP]" <ten...@.dnartreb.noraa> wrote in messag=enews:eOdbHM3ZIHA.5208@.TK2MSFTNGP04.phx.gbl...
> ...
>
>
> > I would never use a MAX type unless I *knew* I was going to require
> > capacity beyond the 4K or 8K limit. =A0By default these values are store=d
> > off-row, so that can affect efficiency and I/O issues if the query has t=o
> > go all over the place to assemble rows. =A0You can override this setting=
> > but, as Jason points out, there may very well be other internal factors,=
> > in addition to the fact that you can't index, etc.- Hide quoted text -
> - Show quoted text -|||Sure, I should have been more explicit, can't index in the traditional way
(e.g. CREATE INDEX foo ON table.column).
> MAX columns can be in indexes as INCLUDED columns.sql

Number of users

I want to know what is the max number of users that the MSSQL Server 7.0 can allow without chash or be slowly.
I have a server with 950 users conected and it=B4s very slowly.see
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_ts_8dbn.asp
950 is no where near the max capacity.
Running slowly will depend on the design and
implementation rather than the product.
You can build something that couldn't cope withy 2 users
if you try.

Number of connections to a database

Is there any way to know the number of current connections to an SQL Server Database? Also how can one change the max pool size (for the number of connections allowed to the database)?
Thanks.FROM Books ONLINE
----------
@.@.CONNECTIONS
Returns the number of connections, or attempted connections, since Microsoft SQL Server was last started.

Syntax
@.@.CONNECTIONS

Return Types
integer

Remarks
Connections are different from users. Applications, for example, can open multiple connections to SQL Server without the user observing the connections.

To display a report containing several SQL Server statistics, including connection attempts, run sp_monitor.

Examples
This example shows the number of login attempts as of the current date and time.

SELECT GETDATE() AS 'Today's Date and Time',
@.@.CONNECTIONS AS 'Login Attempts'

Here is the result set:

Today's Date and Time Login Attempts
-------- -----
1998-04-09 14:28:46.940 18

See Also

Configuration Functions

sp_monitor

1988-2000 Microsoft Corporation. All Rights Reserved.

----------
Originally posted by Big Bubba
Is there any way to know the number of current connections to an SQL Server Database? Also how can one change the max pool size (for the number of connections allowed to the database)?

Thanks.

null varbinary(max) question

I'm doing inserts into a table where I pass a byte[] array to a
varbinary(max) column. The colums is NULLS OK.
when I have a jpg it works great. but if the byte[] is null I get an er
ror
saying
argument @.Photo expects a value.
Why won't it accept null?
Regards,
Gary BlakelyTry passing DbNull.Value instead of a .Net null.
Hope this helps.
Dan Guzman
SQL Server MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:OVG7$PU2HHA.4004@.TK2MSFTNGP05.phx.gbl...
> I'm doing inserts into a table where I pass a byte[] array to a
> varbinary(max) column. The colums is NULLS OK.
> when I have a jpg it works great. but if the byte[] is null I get an
> error saying
> argument @.Photo expects a value.
> Why won't it accept null?
> --
> Regards,
> Gary Blakely
>|||Yes, when sending a null value as a Parameter value in a command to the
database, you cannot use null (Nothing in Visual Basic.NET). Instead you
need to use DBNull.Value.
You may refer to the section "Passing Null as Parameter Value" in this
article:
Best Practices for Using ADO.NET
http://msdn2.microsoft.com/en-us/library/ms971481.aspx
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Sorry but I didn't do a very good job of stating my problem. Let me try
again...
This is my code where Photo is a byte[]...
if (photo == null)
{
cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
}
else
{
cmd.Parameters.Add(new SqlParameter("@.photo", photo));
}
I have successfully used this technique on DateTime types using
Convert.DBNull and that worked fine. But Convert.DBNull won't work for the
Varbinary. Your suggestion to use DBNull.Value yields the following error...
"Implicit conversion from data type nvarchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query."
?
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLwHVYV2HHA.600@.TK2MSFTNGP05.phx.gbl...
> Try passing DbNull.Value instead of a .Net null.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:OVG7$PU2HHA.4004@.TK2MSFTNGP05.phx.gbl...
>|||Sorry but I didn't do a very good job of stating my problem. Let me try
again...
This is my code where Photo is a byte[]...
if (photo == null)
{
cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
}
else
{
cmd.Parameters.Add(new SqlParameter("@.photo", photo));
}
I have successfully used this technique on DateTime types using
Convert.DBNull and that worked fine. But Convert.DBNull won't work for the
Varbinary. Your suggestion to use DBNull.Value yields the following error...
"Implicit conversion from data type nvarchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query."
?
Gary
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:qR1tCUb2HHA.4100@.TK2MSFTNGHUB02.phx.gbl...
> Yes, when sending a null value as a Parameter value in a command to the
> database, you cannot use null (Nothing in Visual Basic.NET). Instead you
> need to use DBNull.Value.
> You may refer to the section "Passing Null as Parameter Value" in this
> article:
> Best Practices for Using ADO.NET
> http://msdn2.microsoft.com/en-us/library/ms971481.aspx
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>
>|||Hi Tina,
Thanks for your detailed response.
For the error yielded by DBNull.Value, it should be caused by not
explicitly specifying the column type. I performed a test with the
following code and it worked fine:
#define _BYTES
....
#if _BYTES
byte[] bytes = { 0x01, 0x11, 0x10, 0x21, 0xFF };
#else
byte[] bytes = null;
#endif
SqlConnection cn = new
SqlConnection("server=sha-chlwang-2k3\\wow;database=broker;integrated
security=SSPI");
cn.Open();
try
{
SqlCommand cmd = new SqlCommand("INSERT INTO BinTest
VALUES(@.P1,@.P2)", cn);
cmd.Parameters.Add(new SqlParameter("@.P1",
SqlDbType.VarBinary, -1, "BImage"));
cmd.Parameters.Add(new SqlParameter("@.P2",
SqlDbType.NVarChar, 50, "DESC"));
if(bytes==null)
cmd.Parameters["@.P1"].Value = DBNull.Value;
else
cmd.Parameters["@.P1"].Value = bytes;
cmd.Parameters["@.P2"].Value = "Test";
int n = cmd.ExecuteNonQuery();
MessageBox.Show(n.ToString());
}
finally
{
cn.Close();
}
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||> "Implicit conversion from data type nvarchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query."
Charles already pointed out that you can specify the parameter data type in
the SqlParameter constructor to avoid implicit conversion. I want to add
that an important clue about the cause of the error is the reference to
nvarchar in the error message. IMHO, the Best Practice is to always specify
the parameter data type that matches the data type on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:%23vGjc9t2HHA.6128@.TK2MSFTNGP02.phx.gbl...
> Sorry but I didn't do a very good job of stating my problem. Let me try
> again...
> This is my code where Photo is a byte[]...
> if (photo == null)
> {
> cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
> }
> else
> {
> cmd.Parameters.Add(new SqlParameter("@.photo", photo));
> }
> I have successfully used this technique on DateTime types using
> Convert.DBNull and that worked fine. But Convert.DBNull won't work for
> the Varbinary. Your suggestion to use DBNull.Value yields the following
> error...
>
> "Implicit conversion from data type nvarchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query."
> ?
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eLwHVYV2HHA.600@.TK2MSFTNGP05.phx.gbl...
>|||Hi Gary,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||Charles,
(sorry for the confusion caused by my replying from another developer's
computer (tina) earlier in this thread)
the code pasted below, that I developed based on your suggestion, works.
Like I said I have done this before with datetime types and I have never had
to explicitly state the datatype before. But with varbinary it apparently
needs to be done. Thanks much for your help!
cmd.Parameters.Add(new SqlParameter("@.photo",
SqlDbType.VarBinary, -1, "Photo"));
if (photo == null)
{
cmd.Parameters["@.photo"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@.photo"].Value = photo;
}
--
Regards,
Gary Blakely
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YiiQ2ey3HHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Hi Gary,
> I am interested in this issue. Would you mind letting me know the result
> of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>

null varbinary(max) question

I'm doing inserts into a table where I pass a byte[] array to a
varbinary(max) column. The colums is NULLS OK.
when I have a jpg it works great. but if the byte[] is null I get an error
saying
argument @.Photo expects a value.
Why won't it accept null?
--
Regards,
Gary BlakelyTry passing DbNull.Value instead of a .Net null.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:OVG7$PU2HHA.4004@.TK2MSFTNGP05.phx.gbl...
> I'm doing inserts into a table where I pass a byte[] array to a
> varbinary(max) column. The colums is NULLS OK.
> when I have a jpg it works great. but if the byte[] is null I get an
> error saying
> argument @.Photo expects a value.
> Why won't it accept null?
> --
> Regards,
> Gary Blakely
>|||Yes, when sending a null value as a Parameter value in a command to the
database, you cannot use null (Nothing in Visual Basic.NET). Instead you
need to use DBNull.Value.
You may refer to the section "Passing Null as Parameter Value" in this
article:
Best Practices for Using ADO.NET
http://msdn2.microsoft.com/en-us/library/ms971481.aspx
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Sorry but I didn't do a very good job of stating my problem. Let me try
again...
This is my code where Photo is a byte[]...
if (photo == null)
{
cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
}
else
{
cmd.Parameters.Add(new SqlParameter("@.photo", photo));
}
I have successfully used this technique on DateTime types using
Convert.DBNull and that worked fine. But Convert.DBNull won't work for the
Varbinary. Your suggestion to use DBNull.Value yields the following error...
"Implicit conversion from data type nvarchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query."
?
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLwHVYV2HHA.600@.TK2MSFTNGP05.phx.gbl...
> Try passing DbNull.Value instead of a .Net null.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:OVG7$PU2HHA.4004@.TK2MSFTNGP05.phx.gbl...
>> I'm doing inserts into a table where I pass a byte[] array to a
>> varbinary(max) column. The colums is NULLS OK.
>> when I have a jpg it works great. but if the byte[] is null I get an
>> error saying
>> argument @.Photo expects a value.
>> Why won't it accept null?
>> --
>> Regards,
>> Gary Blakely
>>
>|||Sorry but I didn't do a very good job of stating my problem. Let me try
again...
This is my code where Photo is a byte[]...
if (photo == null)
{
cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
}
else
{
cmd.Parameters.Add(new SqlParameter("@.photo", photo));
}
I have successfully used this technique on DateTime types using
Convert.DBNull and that worked fine. But Convert.DBNull won't work for the
Varbinary. Your suggestion to use DBNull.Value yields the following error...
"Implicit conversion from data type nvarchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query."
?
Gary
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:qR1tCUb2HHA.4100@.TK2MSFTNGHUB02.phx.gbl...
> Yes, when sending a null value as a Parameter value in a command to the
> database, you cannot use null (Nothing in Visual Basic.NET). Instead you
> need to use DBNull.Value.
> You may refer to the section "Passing Null as Parameter Value" in this
> article:
> Best Practices for Using ADO.NET
> http://msdn2.microsoft.com/en-us/library/ms971481.aspx
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> =====================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>
>|||Hi Tina,
Thanks for your detailed response.
For the error yielded by DBNull.Value, it should be caused by not
explicitly specifying the column type. I performed a test with the
following code and it worked fine:
#define _BYTES
....
#if _BYTES
byte[] bytes = { 0x01, 0x11, 0x10, 0x21, 0xFF };
#else
byte[] bytes = null;
#endif
SqlConnection cn = new
SqlConnection("server=sha-chlwang-2k3\\wow;database=broker;integrated
security=SSPI");
cn.Open();
try
{
SqlCommand cmd = new SqlCommand("INSERT INTO BinTest
VALUES(@.P1,@.P2)", cn);
cmd.Parameters.Add(new SqlParameter("@.P1",
SqlDbType.VarBinary, -1, "BImage"));
cmd.Parameters.Add(new SqlParameter("@.P2",
SqlDbType.NVarChar, 50, "DESC"));
if(bytes==null)
cmd.Parameters["@.P1"].Value = DBNull.Value;
else
cmd.Parameters["@.P1"].Value = bytes;
cmd.Parameters["@.P2"].Value = "Test";
int n = cmd.ExecuteNonQuery();
MessageBox.Show(n.ToString());
}
finally
{
cn.Close();
}
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||> "Implicit conversion from data type nvarchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query."
Charles already pointed out that you can specify the parameter data type in
the SqlParameter constructor to avoid implicit conversion. I want to add
that an important clue about the cause of the error is the reference to
nvarchar in the error message. IMHO, the Best Practice is to always specify
the parameter data type that matches the data type on the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:%23vGjc9t2HHA.6128@.TK2MSFTNGP02.phx.gbl...
> Sorry but I didn't do a very good job of stating my problem. Let me try
> again...
> This is my code where Photo is a byte[]...
> if (photo == null)
> {
> cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
> }
> else
> {
> cmd.Parameters.Add(new SqlParameter("@.photo", photo));
> }
> I have successfully used this technique on DateTime types using
> Convert.DBNull and that worked fine. But Convert.DBNull won't work for
> the Varbinary. Your suggestion to use DBNull.Value yields the following
> error...
>
> "Implicit conversion from data type nvarchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query."
> ?
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eLwHVYV2HHA.600@.TK2MSFTNGP05.phx.gbl...
>> Try passing DbNull.Value instead of a .Net null.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
>> news:OVG7$PU2HHA.4004@.TK2MSFTNGP05.phx.gbl...
>> I'm doing inserts into a table where I pass a byte[] array to a
>> varbinary(max) column. The colums is NULLS OK.
>> when I have a jpg it works great. but if the byte[] is null I get an
>> error saying
>> argument @.Photo expects a value.
>> Why won't it accept null?
>> --
>> Regards,
>> Gary Blakely
>>
>|||Hi Gary,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Best regards,
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||Charles,
(sorry for the confusion caused by my replying from another developer's
computer (tina) earlier in this thread)
the code pasted below, that I developed based on your suggestion, works.
Like I said I have done this before with datetime types and I have never had
to explicitly state the datatype before. But with varbinary it apparently
needs to be done. Thanks much for your help!
cmd.Parameters.Add(new SqlParameter("@.photo",
SqlDbType.VarBinary, -1, "Photo"));
if (photo == null)
{
cmd.Parameters["@.photo"].Value = DBNull.Value;
}
else
{
cmd.Parameters["@.photo"].Value = photo;
}
--
Regards,
Gary Blakely
"Charles Wang[MSFT]" <changliw@.online.microsoft.com> wrote in message
news:YiiQ2ey3HHA.4200@.TK2MSFTNGHUB02.phx.gbl...
> Hi Gary,
> I am interested in this issue. Would you mind letting me know the result
> of
> the suggestions? If you need further assistance, feel free to let me know.
> I will be more than happy to be of assistance.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ======================================================> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hi Gary,
Appreciate your update and response. I am glad to hear that the problem has
been fixed. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

null varbinary(max) question

I'm doing inserts into a table where I pass a byte[] array to a
varbinary(max) column. The colums is NULLS OK.
when I have a jpg it works great. but if the byte[] is null I get an error
saying
argument @.Photo expects a value.
Why won't it accept null?
Regards,
Gary Blakely
Try passing DbNull.Value instead of a .Net null.
Hope this helps.
Dan Guzman
SQL Server MVP
"GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
news:OVG7$PU2HHA.4004@.TK2MSFTNGP05.phx.gbl...
> I'm doing inserts into a table where I pass a byte[] array to a
> varbinary(max) column. The colums is NULLS OK.
> when I have a jpg it works great. but if the byte[] is null I get an
> error saying
> argument @.Photo expects a value.
> Why won't it accept null?
> --
> Regards,
> Gary Blakely
>
|||Sorry but I didn't do a very good job of stating my problem. Let me try
again...
This is my code where Photo is a byte[]...
if (photo == null)
{
cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
}
else
{
cmd.Parameters.Add(new SqlParameter("@.photo", photo));
}
I have successfully used this technique on DateTime types using
Convert.DBNull and that worked fine. But Convert.DBNull won't work for the
Varbinary. Your suggestion to use DBNull.Value yields the following error...
"Implicit conversion from data type nvarchar to varbinary(max) is not
allowed. Use the CONVERT function to run this query."
?
Gary
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eLwHVYV2HHA.600@.TK2MSFTNGP05.phx.gbl...
> Try passing DbNull.Value instead of a .Net null.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "GaryDean" <GaryDean@.newsgroups.nospam> wrote in message
> news:OVG7$PU2HHA.4004@.TK2MSFTNGP05.phx.gbl...
>
|||> "Implicit conversion from data type nvarchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query."
Charles already pointed out that you can specify the parameter data type in
the SqlParameter constructor to avoid implicit conversion. I want to add
that an important clue about the cause of the error is the reference to
nvarchar in the error message. IMHO, the Best Practice is to always specify
the parameter data type that matches the data type on the server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <TinaMSeaburn@.nospamexcite.com> wrote in message
news:%23vGjc9t2HHA.6128@.TK2MSFTNGP02.phx.gbl...
> Sorry but I didn't do a very good job of stating my problem. Let me try
> again...
> This is my code where Photo is a byte[]...
> if (photo == null)
> {
> cmd.Parameters.Add(new SqlParameter("@.photo", Convert.DBNull));
> }
> else
> {
> cmd.Parameters.Add(new SqlParameter("@.photo", photo));
> }
> I have successfully used this technique on DateTime types using
> Convert.DBNull and that worked fine. But Convert.DBNull won't work for
> the Varbinary. Your suggestion to use DBNull.Value yields the following
> error...
>
> "Implicit conversion from data type nvarchar to varbinary(max) is not
> allowed. Use the CONVERT function to run this query."
> ?
> Gary
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eLwHVYV2HHA.600@.TK2MSFTNGP05.phx.gbl...
>