Binary_double Oracle data type support

I'm using RS 2000 and I'm getting an "unsupported Oracle data type 101 encountered" error when attempting to retrieve data from a dataset based on a plsql stored procedure. I'm assuming this is related to a recent update on our Oracle 10g database to convert columns from number to binary_double.

Can anyone confirm if this is the case, and suggest a work-around? Is binary_double supported in RS 2005?

Thanks

RS uses MS .NET provider for Oracle which currently does not support binary_float/double.

The possible workaround is to cast binary_float columns to number in your SQL statement

select cast(my_bin_float_col as number) from ...

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
--
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+Isql

Binary Data Type

Hello Dears,

I was Maked Pictures table in sql server 2005 with Two Col pic_Id (int) and picture(binary)

i need now ro insert image into this table by Asp.net under VB.net Code i have in my form FileUpload and button control

and this is my Code

ProtectedSub btnLoad_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles btnLoad.Click

Dim content()AsByte = ReadBitmap2ByteArray(FileUpload1.PostedFile.ContentType)

StoreBlob2DataBase(content)

EndSub

Function ReadBitmap2ByteArray(ByVal FileNameAsString)AsByte()

Dim imageAs Drawing.Bitmap =New Drawing.Bitmap(FileName)

Dim streamAs IO.MemoryStream =New IO.MemoryStream()

image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp)

Return stream.ToArray

EndFunction

Sub StoreBlob2DataBase(ByVal contentAsByte())

con.Open()

Dim cmAsNew SqlCommand("Insert into Pictures(Pic_Id,picture) values(@.id,@.pic)", con)

cm.Parameters.AddWithValue("@.ID", 1)

Dim p1AsNew SqlParameter

p1.ParameterName ="@.pic"

p1.SqlDbType = Data.SqlDbType.Binary

p1.Value = content

p1.Size = content.Length

cm.Parameters.Add(p1)

cm.ExecuteNonQuery()

EndSub

when i make run to my website it give me an Exeption in this statement

Dim imageAs Drawing.Bitmap =New Drawing.Bitmap(FileName)

it tell me that Parameter is not valid

i need help about this please

with my Best regard

khalil

In this line:

Dim content()AsByte = ReadBitmap2ByteArray(FileUpload1.PostedFile.ContentType)

You are passing in the contentType rather than a filename.

|||

HI

its does not work also its give me the same error what can i do

|||

Of course it gives you the same error, I didn't fix it, I just pointed out what was wrong. Pass in the filename of the file, not the contenttype.

Binary Data Type

I am trying to store a byte array in a database. I want to use binary to store the data but I am confused about the type. The byte array I am trying to store is a password hash from SHA512.

I hash a plain text value then store the result in a byte array. I then want to store the byte array in the database as binary but I am confused when its asking for the size of the binary field. In nvarchar a size of 2 would mean 2 characters.

How should I choose the size of this binary field, and what does the size mean. If I choose a size of 6 does that mean 6 characters, like 010110. Or is it stored differently?

The maximum size of a plain text password is 30 characters, and the salt used to generate the SHA512 hash has a maximum size of 16, but I don't know the exact size of the salt because its randomly picked when the salt is generated.

I need to make sure the size of my binary field will hold the largest possible password hash, but I don't want it too large so its never completely used.

How is this data stored in the binary field, and what size binary field should I choose to make sure there are no problems with the password hash being truncated, yet making sure I'm not just wasting by creating a field thats too large.

Thanks!

The size is in bytes.|||

Use the varbinary datatype, since it expands to contain the content. That way you can overestimate the byte length without taking any (extra) space

passwordcolumn varbinary(2000) NOT NULL

/micke

|||

How should I choose the size of this binary field, and what does the size mean. If I choose a size of 6 does that mean 6 characters, like 010110. Or is it stored differently?

Ok, simple thing. What the size means, exactly, is written in the documentation - which you have and which you really should read. At one point. It is the MAXIMUM size of the data that can be stored. MAXIMUM - like, btw., with the nvarcher you mention as example.

The maximum size of a plain text password is 30 characters, and the salt used to generate the SHA512 hash has a maximum size of 16, but I don't know the exact size of the salt because its randomly picked when the salt is generated.

You DO know the maximum size of the salt. The salt should always have the SAME size. Maybe you add spaces to it. Or "0". Or whatever. And then, you basically only need the maximum size anyway.

How is this data stored in the binary field,

Did I mention the documentation? There is a nice section in that explains exactly how SQL Server stores binary data. But frankly, you really do not need to know HOW it is stored. All you need to know for the problem in hand is THAT it is stored. The storage details are an implementation detail of the engine.

and what size binary field should I choose to make sure there are no problems with the password hash being truncated

Choose a size that is lare enough to store the hash. The hash will normally be static in size anyway. So, the size of the vield chould really be clear. That said I would NOT store it as binary. I would store it as char(x) and encode the data in a human readable way (i.e. for example as HEX). Makes debugging a lot easier.

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
Mat
Convert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith
|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat
|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I
|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I

bigint or uniqueidentifer performance metrics?

If the use of the uniqueidentifier is now preferred, across how many
relations is it optimal to use as the type for primary keys? Is there some
point other than performance why one should not worry about reverting to the
use of bigint for related tables? I've only read a couple of stories about
it so far. What do you think?
<%= Clinton GallagherClinton
I would try to avoid using GUID as a Primary Key.
GUIDs take up 16-bytes of storage, more than an Identify column, which in
turn make the index larger, which increases I/O reads, which can hurt
performance. While the performance hit will be minimal if you do decide to
add a clustered index to a GUID column, every little bit adds up
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
> If the use of the uniqueidentifier is now preferred, across how many
> relations is it optimal to use as the type for primary keys? Is there some
> point other than performance why one should not worry about reverting to
> the use of bigint for related tables? I've only read a couple of stories
> about it so far. What do you think?
> <%= Clinton Gallagher
>|||That's what I read Uri but then on the next article GUIDs are praised not to
mention their use in the aspnet_Users table as well as other tables in the
ASP.NET 2.0 Membership, Roles and Profiles implementation Microsoft has
provided.
I think I'll just use their GUID as a foreign key and revert to bigint for
primary keys. It seems there are benefits for grabbing the last inserted
record using @.@.IDENTITY which GUID does not support.
I'm also trying to figure out how to use GUID as a primary key -- anyway --
and auto-generate the value using the newid( ) default binding. I've tried
learning to use newid( ) by entering data into the table but all I get to
see is red circles with ! and error messages or the red circles and no error
messages.
Thanks for your attention...
<%= Clinton Gallagher
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqYqhBqOGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Clinton
> I would try to avoid using GUID as a Primary Key.
> GUIDs take up 16-bytes of storage, more than an Identify column, which in
> turn make the index larger, which increases I/O reads, which can hurt
> performance. While the performance hit will be minimal if you do decide to
> add a clustered index to a GUID column, every little bit adds up
>
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
>|||This is kind of a confusing question, since you really need to give us more
information. Do you have tables that you need to store > 4 billion row? In
2005 there is a way to create an always increasing guid using
newsequentialid that can help a bit with the problems that using a GUID for
a key can create, but GUIDs make fine surrogate keys for tables, other than
performance and typability (the latter of which is just a programmer
convienience, so should not be really important to the question of whether
to choose them or not)
If you need the benefits of a GUID (like the UI can create them and be
guaranteed uniqueness,) then it might be worth it, otherwise use the correct
integer type. If you are going to have > 4 billion rows in many of your
tables, I hope you have spent a lot of money on a fast disk subsystem anyhow
because you will need it!
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
> If the use of the uniqueidentifier is now preferred, across how many
> relations is it optimal to use as the type for primary keys? Is there some
> point other than performance why one should not worry about reverting to
> the use of bigint for related tables? I've only read a couple of stories
> about it so far. What do you think?
> <%= Clinton Gallagher
>|||Well, good point. I should be comparing an int rather than bigint.
If its true that deleting a record can leave gaps in the sequence of the
key's
value when using an int which can impact sorting the same must be true
when using the uniqueidentifier.
Furthermore, as I understand it even when using newsequentialid( ) there
is no @.@.IDENTITY to get the value of the last inserted record. Do you
know if this is true?
Other than uniqueness, I see no reason to use uniqueidentifier yet..
That's the basis of this inquiry.
<%= Clinton Gallagher
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:OwfDDvxOGHA.2040@.TK2MSFTNGP14.phx.gbl...
> This is kind of a confusing question, since you really need to give us
> more information. Do you have tables that you need to store > 4 billion
> row? In 2005 there is a way to create an always increasing guid using
> newsequentialid that can help a bit with the problems that using a GUID
> for a key can create, but GUIDs make fine surrogate keys for tables, other
> than performance and typability (the latter of which is just a programmer
> convienience, so should not be really important to the question of whether
> to choose them or not)
> If you need the benefits of a GUID (like the UI can create them and be
> guaranteed uniqueness,) then it might be worth it, otherwise use the
> correct integer type. If you are going to have > 4 billion rows in many
> of your tables, I hope you have spent a lot of money on a fast disk
> subsystem anyhow because you will need it!
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2388qeVoOGHA.1832@.TK2MSFTNGP11.phx.gbl...
>|||> Furthermore, as I understand it even when using newsequentialid( ) there
> is no @.@.IDENTITY to get the value of the last inserted record. Do you
> know if this is true?
One of the differences between uniqueidentifier and identity is that a
uniqueidentifier value can be assigned by the application instead of SQL
Server. This eliminates the need to retrieve the surrogate key value after
insertion.
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%2313yEZzOGHA.2624@.TK2MSFTNGP12.phx.gbl...
> Well, good point. I should be comparing an int rather than bigint.
> If its true that deleting a record can leave gaps in the sequence of the
> key's
> value when using an int which can impact sorting the same must be true
> when using the uniqueidentifier.
> Furthermore, as I understand it even when using newsequentialid( ) there
> is no @.@.IDENTITY to get the value of the last inserted record. Do you
> know if this is true?
> Other than uniqueness, I see no reason to use uniqueidentifier yet..
> That's the basis of this inquiry.
> <%= Clinton Gallagher
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:OwfDDvxOGHA.2040@.TK2MSFTNGP14.phx.gbl...
>|||That was helpful to be reminded of as the DNF will indeed generate a GUID
consistent with the uniqueidentitifer.
<%= Clinton Gallagher
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OOChgd0OGHA.2696@.TK2MSFTNGP14.phx.gbl...
> One of the differences between uniqueidentifier and identity is that a
> uniqueidentifier value can be assigned by the application instead of SQL
> Server. This eliminates the need to retrieve the surrogate key value
> after insertion.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:%2313yEZzOGHA.2624@.TK2MSFTNGP12.phx.gbl...
>

bigint - Arithmetic overflow

Hi,

I am getting

Server: Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value =
1152921504606847000.000000.

when I do

select power(2,60) in query analyzer

then I tried

declare @.b bigint
select @.b = power(2,60)

same error.

I tried

select convert(bigint,power(2,60)) no luck

Is there any way to tell sql server to use bigint when it
calculate power(2,60)

Ram.This worked:

declare @.b bigint
select @.b = power(cast(2 as bigint),60)
select @.b

-------
1152921504606846976

(1 row(s) affected)|||Originally posted by rdjabarov
This worked:

declare @.b bigint
select @.b = power(cast(2 as bigint),60)
select @.b

-------
1152921504606846976

(1 row(s) affected)

much thanks !!!!!

bigint - Arithmetic overflow

Hi,
I am getting
Server: Msg 232, Level 16, State 3, Line 1
Arithmetic overflow error for type int, value = 1152921504606847000.000000.
when I do
select power(2,60) in query analyzer
then I tried
declare @.b bigint
select @.b = power(2,60)
same error.
I tried
select convert(bigint,power(2,60)) no luck
Is there any way to tell sql server to use bigint when it
calculate power(2,60)
Ram.declare @.t bigint
select @.t = 2
select power(@.t,60)
>--Original Message--
>Hi,
>I am getting
>Server: Msg 232, Level 16, State 3, Line 1
>Arithmetic overflow error for type int, value =>1152921504606847000.000000.
>when I do
>select power(2,60) in query analyzer
>then I tried
>declare @.b bigint
>select @.b = power(2,60)
>same error.
>I tried
>select convert(bigint,power(2,60)) no luck
>Is there any way to tell sql server to use bigint when it
>calculate power(2,60)
>Ram.
>
>
>.
>|||power function will return value of same type as input
type, so instead of constant 2 (which is integer) use a
variable defined as bigint, try following:
declare @.a bigint, @.b bigint
select @.a = 2
select @.b = power(@.a,60)
print @.b
hope this helps.
>--Original Message--
>Hi,
>I am getting
>Server: Msg 232, Level 16, State 3, Line 1
>Arithmetic overflow error for type int, value =>1152921504606847000.000000.
>when I do
>select power(2,60) in query analyzer
>then I tried
>declare @.b bigint
>select @.b = power(2,60)
>same error.
>I tried
>select convert(bigint,power(2,60)) no luck
>Is there any way to tell sql server to use bigint when it
>calculate power(2,60)
>Ram.
>
>
>.
>

big[er]int Datatype?

Hello,
I have a need to store a numeric value greater than the 2^63 allowed by the
bigint data type...any options?
TIADecimal?
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Steven Frank" <stevef@.relation.com> wrote in message
news:upMgP1rqDHA.1084@.tk2msftngp13.phx.gbl...
> Hello,
> I have a need to store a numeric value greater than the 2^63 allowed
by the
> bigint data type...any options?
> TIA
>|||How much bigger? E.g. did you try DECIMAL or NUMERIC?
If you don't need to perform calculations, you might be safer just storing
as a VARCHAR.
A
"Steven Frank" <stevef@.relation.com> wrote in message
news:upMgP1rqDHA.1084@.tk2msftngp13.phx.gbl...
> Hello,
> I have a need to store a numeric value greater than the 2^63 allowed by
the
> bigint data type...any options?
> TIA
>|||Well don't I feel like a dork... I though bigint was the King!
Thanks a lot!
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:O3zlX7rqDHA.1084@.tk2msftngp13.phx.gbl...
> Decimal?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Steven Frank" <stevef@.relation.com> wrote in message
> news:upMgP1rqDHA.1084@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a need to store a numeric value greater than the 2^63 allowed
> by the
> > bigint data type...any options?
> >
> > TIA
> >
> >
>|||Well, I've spoken too soon.
The decimal sorks great for storage, but I *do* have to do calculations,
specifically a logical AND (&) operation which does not allow the use of the
decimal data type. Any ideas for this one?
Thanks again!
Steve
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uoRd29rqDHA.2012@.TK2MSFTNGP12.phx.gbl...
> How much bigger? E.g. did you try DECIMAL or NUMERIC?
> If you don't need to perform calculations, you might be safer just storing
> as a VARCHAR.
> A
>
> "Steven Frank" <stevef@.relation.com> wrote in message
> news:upMgP1rqDHA.1084@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I have a need to store a numeric value greater than the 2^63 allowed by
> the
> > bigint data type...any options?
> >
> > TIA
> >
> >
>|||> The decimal sorks great for storage, but I *do* have to do calculations,
> specifically a logical AND (&) operation which does not allow the use of
the
> decimal data type. Any ideas for this one?
Use CAST/CONVERT when performing the calculations?|||I've tried that and it blows up because you can cast it to a bigint because
its too big...
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23LoVn0TrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > The decimal sorks great for storage, but I *do* have to do calculations,
> > specifically a logical AND (&) operation which does not allow the use of
> the
> > decimal data type. Any ideas for this one?
> Use CAST/CONVERT when performing the calculations?
>

BFILE on Microsoft SQL Server?

Hello!
Maybe you know the Type BFILE from Oracle Databases.
BFILE (External Binary File) is a binary file stored outside of the
database in the host operating system file system, but accessible from
database tables.
What is the BFILE equivalent on a Microsoft SQL Server? I could not
find one in the MSDN or Documentation - so maybe there is none?
SQL Server does not have any native construct that is explicitly equivalent
of the BFILE data type.
Linchi
"Deniz" wrote:

> Hello!
> Maybe you know the Type BFILE from Oracle Databases.
> BFILE (External Binary File) is a binary file stored outside of the
> database in the host operating system file system, but accessible from
> database tables.
> What is the BFILE equivalent on a Microsoft SQL Server? I could not
> find one in the MSDN or Documentation - so maybe there is none?
>

BFILE on Microsoft SQL Server?

Hello!
Maybe you know the Type BFILE from Oracle Databases.
BFILE (External Binary File) is a binary file stored outside of the
database in the host operating system file system, but accessible from
database tables.
What is the BFILE equivalent on a Microsoft SQL Server? I could not
find one in the MSDN or Documentation - so maybe there is none?SQL Server does not have any native construct that is explicitly equivalent
of the BFILE data type.
Linchi
"Deniz" wrote:

> Hello!
> Maybe you know the Type BFILE from Oracle Databases.
> BFILE (External Binary File) is a binary file stored outside of the
> database in the host operating system file system, but accessible from
> database tables.
> What is the BFILE equivalent on a Microsoft SQL Server? I could not
> find one in the MSDN or Documentation - so maybe there is none?
>

BFILE on Microsoft SQL Server?

Hello!
Maybe you know the Type BFILE from Oracle Databases.
BFILE (External Binary File) is a binary file stored outside of the
database in the host operating system file system, but accessible from
database tables.
What is the BFILE equivalent on a Microsoft SQL Server? I could not
find one in the MSDN or Documentation - so maybe there is none?SQL Server does not have any native construct that is explicitly equivalent
of the BFILE data type.
Linchi
"Deniz" wrote:
> Hello!
> Maybe you know the Type BFILE from Oracle Databases.
> BFILE (External Binary File) is a binary file stored outside of the
> database in the host operating system file system, but accessible from
> database tables.
> What is the BFILE equivalent on a Microsoft SQL Server? I could not
> find one in the MSDN or Documentation - so maybe there is none?
>

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.developersdex.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
--
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:
> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
> > Hai,
> >
> > I have a table with datetime type column. I stored the date with time in
> > this column.
> > Now i want to select the records between 10/01/2005 and 10/31/2005.
> >
> > I used 'select * from tablename where columnname between '10/01/2005' and
> > '10/31/2005'' query to select records.
> >
> > But the above query returns upto 10/30/2005.
> >
> > Please advise me.
> >
> > Rgds,
> > Soura
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:
> > select *
> > from tablename
> > where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>> Soura,
>> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
>> after midnight will not be included in the results.
>> Try:
>> select *
>> from tablename
>> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
>> Rob
>> SouRa wrote:
>> > Hai,
>> >
>> > I have a table with datetime type column. I stored the date with time in
>> > this column.
>> > Now i want to select the records between 10/01/2005 and 10/31/2005.
>> >
>> > I used 'select * from tablename where columnname between '10/01/2005' and
>> > '10/31/2005'' query to select records.
>> >
>> > But the above query returns upto 10/30/2005.
>> >
>> > Please advise me.
>> >
>> > Rgds,
>> > Soura

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
Soura
This works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***
|||This should contain all necessary information: http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura
|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]
|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>
|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>
|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be disastrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each time.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:

Between Clause

Hai,
I have a table with datetime type column. I stored the date with time in
this column.
Now i want to select the records between 10/01/2005 and 10/31/2005.
I used 'select * from tablename where columnname between '10/01/2005' and
'10/31/2005'' query to select records.
But the above query returns upto 10/30/2005.
Please advise me.
Rgds,
SouraThis works fine for me.
create table test(id int, testdate datetime)
insert into test values(1, '10/01/2005')
insert into test values(1, '10/10/2005')
insert into test values(1, '10/31/2005')
select * from test where testdate between '10/01/2005' and '10/31/2005'
Please post you data and ddl.
Thanks
Amish
*** Sent via Developersdex http://www.codecomments.com ***|||This should contain all necessary information: http://www.karaszi.com/SQLServer/in...fo_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:C2DF4191-4BD9-4A6B-AB94-2D16F715E493@.microsoft.com...
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||Soura,
'10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
after midnight will not be included in the results.
Try:
select *
from tablename
where columnname between '10/01/2005' and '10/31/2005 23:59:59'
Rob
SouRa wrote:
> Hai,
> I have a table with datetime type column. I stored the date with time in
> this column.
> Now i want to select the records between 10/01/2005 and 10/31/2005.
> I used 'select * from tablename where columnname between '10/01/2005' and
> '10/31/2005'' query to select records.
> But the above query returns upto 10/30/2005.
> Please advise me.
> Rgds,
> Soura|||> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
or between '10/01/2005' and '10/31/2005 23:59:59.997'
to capture everything in the last day.
William Stacey [MVP]|||Hi Rob,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"Rob" wrote:

> Soura,
> '10/31/2005' assumes 10/31/2005 at midnight. Any records with a time of
> after midnight will not be included in the results.
> Try:
> select *
> from tablename
> where columnname between '10/01/2005' and '10/31/2005 23:59:59'
> Rob
> SouRa wrote:
>|||Hi William ,
Thanks for your response, it is working fine. But i get the inputs only in
date format('10/01/2005'), So i want to concatenate the timestamp each time.
I have one method,
"select * from table_name where
convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
'10/31/2005'
it is working fine.
Can you tell me it is efficient one. Please advise me.
rgds,
Soura
"William Stacey [MVP]" wrote:

> or between '10/01/2005' and '10/31/2005 23:59:59.997'
> to capture everything in the last day.
> --
> William Stacey [MVP]
>
>|||Did you read my article?

> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
Above will negate the usage of indexes on the column. Can potentially be dis
astrous for performance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:CD781D5B-4FF2-49B5-B673-A47EC0A60E7E@.microsoft.com...[vbcol=seagreen]
> Hi Rob,
> Thanks for your response, it is working fine. But i get the inputs only in
> date format('10/01/2005'), So i want to concatenate the timestamp each tim
e.
> I have one method,
> "select * from table_name where
> convert(datetime,convert(varchar,column_
name) ) between '10/01/2005' and
> '10/31/2005'
> it is working fine.
> Can you tell me it is efficient one. Please advise me.
> rgds,
> Soura
> "Rob" wrote:
>

Best way to handle License issues

Here is the scenario:

We are developing an ETL type data load application and we're thinking building a SSIS package to assist with this.

The application and data files to be loaded would be on the client Windows XP Workstation. The SQL Server 2005 instance would be on a networked server elsewhere.

The XP Workstation would NOT have sql server 2005.

What is the best way to handle this? Can this be achieved fairly easily?

Kevin

You’ll have to be a bit more specific on how you want to do your licensing... on a per seat basis? Per server?

No matter the model, I would highly recommend taking a read of this article on the mechanisms provided by the .NET Framework to help you implement a licensing mechanism.

|||I'm sorry I wasn't clear. I do not want to CREATE licensing for my application. I want to find out what the best, cost effective solution for me would be.

Example:
Buy 1 SQL Server 2005 and call SSIS packages from the Client?
I think in this scenario I would only need 1 SQL Server Standard license?

Kevin
|||Ahh... that I’m afraid I cannot help you with much... instead I would suggest starting with the SQL Server 2005 Licensing: Frequently Asked Questions if you have not already found it.|||

If the ETL application is to be on the client then you will need to have a licensed SQL Server install there - although you need only install SSIS and tools, not the server itself.

A better - and more economical - scenario for you may be to have SSIS running on the SQL Server box. No additional license required. You can create a SQL Agent job to execute the package and invoke that job remotely from the client using T-SQL.

Either way, of course, you will have data moving over the network from client to server - and that may be a bottleneck for you.

Donald

Best way to do a free text search

HiI have a .net 2 website that works from a sql 2000 db. I am building a form that will allow the user to type in any search criteria and based on their input I need to produce some results (that's the simplest way to put it)I will need to search various parts of my db to find similar sounding information, I was just wondering what is the best way to do this. I had the following thoughts1) Search columns using Soundex tsql function (but not sure how good this is?)2) Remove all noise words from user input (eg, and, or, the etc...) and then use a regular expression to search the fields in the dbAlternatively are their some third party components to do can do this for me Many thanks in advance

You can full text search enable the database, and create a full text index on the column in the table you want to search. Then you will be able to search any word that appears inside that column. Here are a couple of links that might be helpful to you.

http://www.databasejournal.com/features/mssql/article.php/3441981

http://www.wherecanibuyit.co.uk/ASP/full-text-search.html

http://msdn2.microsoft.com/en-us/library/ms177652.aspx

|||

Hi

Thanks for that, it was a great help

I have noticed that their is a RowNumber method in SQL 2005, is their any way to do this functionality in SQL 2000

One method I thought of was to put the data in temp table and loop through each record and give it a number, and then pull out 10 records at a time as the user pages through.

Are their any other ways I could do this.

Many thanks in advance

|||

The short answer is yes. You may want to search for articles on Custom Paging with SQL Server 2000. There are ways to do it, but they are not as simple or clean as the ROW_NUMBER() function. Here is an article to get you started.

http://www.4guysfromrolla.com/webtech/042606-1.shtml