Conversion of Varchar into float

We convert a varchar column into float so that the data is ordered logically
like (1,2,10,11) instead of (1,10,11,2).
We have noticed one peculiar issue. When this query runs for a specific
range of inputs for the float values, it selects records which are outide
the inputs ranges
For example, if the query is run for float values between 1 and 10 then
values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
We tried using the float conversion part of the where clause error, but it
gives a data type conversion error . Is there any other method to order the
varchar values logically besides converting into float.
Need forum members help on this
Soura.Could you perhaps post the query so that we know what steps you are
taking to accomplish your goal?|||Hi
Check out http://www.sommarskog.se/arrays-in-sql.html to convert it into an
orderable format, you would then need to reconstitute the string.
John
"SouRa" wrote:
> We convert a varchar column into float so that the data is ordered logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
> We tried using the float conversion part of the where clause error, but it
> gives a data type conversion error . Is there any other method to order the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||Do your numbers have decimals? If not, then you should be using int and not
float.
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> We convert a varchar column into float so that the data is ordered
> logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> ?
> We tried using the float conversion part of the where clause error, but
> it
> gives a data type conversion error . Is there any other method to order
> the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||We do have decmials .I forgot to mention about this in my original post
"Michael D'Angelo" wrote:
> Do your numbers have decimals? If not, then you should be using int and not
> float.
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> > We convert a varchar column into float so that the data is ordered
> > logically
> > like (1,2,10,11) instead of (1,10,11,2).
> >
> > We have noticed one peculiar issue. When this query runs for a specific
> > range of inputs for the float values, it selects records which are outide
> > the inputs ranges
> >
> > For example, if the query is run for float values between 1 and 10 then
> > values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> > ?
> >
> > We tried using the float conversion part of the where clause error, but
> > it
> > gives a data type conversion error . Is there any other method to order
> > the
> > varchar values logically besides converting into float.
> >
> > Need forum members help on this
> >
> > Soura.
> >
> >
> >
>
>|||We have solved this issue by using money instead of float
"nate.vu@.gmail.com" wrote:
> Could you perhaps post the query so that we know what steps you are
> taking to accomplish your goal?
>|||Hi
You may want to use decimal or numeric instead of money.
John
"SouRa" wrote:
> We have solved this issue by using money instead of float
> "nate.vu@.gmail.com" wrote:
> > Could you perhaps post the query so that we know what steps you are
> > taking to accomplish your goal?
> >
> >sqlsql

Conversion of Varchar into float

We convert a varchar column into float so that the data is ordered logically
like (1,2,10,11) instead of (1,10,11,2).
We have noticed one peculiar issue. When this query runs for a specific
range of inputs for the float values, it selects records which are outide
the inputs ranges
For example, if the query is run for float values between 1 and 10 then
values 11 is also getting pickedup besides 1 to 10. How to prevent this. ?
We tried using the float conversion part of the where clause error, but it
gives a data type conversion error . Is there any other method to order the
varchar values logically besides converting into float.
Need forum members help on this
Soura.Could you perhaps post the query so that we know what steps you are
taking to accomplish your goal?|||Hi
Check out http://www.sommarskog.se/arrays-in-sql.html to convert it into an
orderable format, you would then need to reconstitute the string.
John
"SouRa" wrote:

> We convert a varchar column into float so that the data is ordered logical
ly
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
?
> We tried using the float conversion part of the where clause error, but
it
> gives a data type conversion error . Is there any other method to order th
e
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||Do your numbers have decimals? If not, then you should be using int and not
float.
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
> We convert a varchar column into float so that the data is ordered
> logically
> like (1,2,10,11) instead of (1,10,11,2).
> We have noticed one peculiar issue. When this query runs for a specific
> range of inputs for the float values, it selects records which are outide
> the inputs ranges
> For example, if the query is run for float values between 1 and 10 then
> values 11 is also getting pickedup besides 1 to 10. How to prevent this.
> ?
> We tried using the float conversion part of the where clause error, but
> it
> gives a data type conversion error . Is there any other method to order
> the
> varchar values logically besides converting into float.
> Need forum members help on this
> Soura.
>
>|||We do have decmials .I forgot to mention about this in my original post
"Michael D'Angelo" wrote:

> Do your numbers have decimals? If not, then you should be using int and n
ot
> float.
> "SouRa" <SouRa@.discussions.microsoft.com> wrote in message
> news:885E2A2F-6FD6-417E-A20F-470465D574DC@.microsoft.com...
>
>|||We have solved this issue by using money instead of float
"nate.vu@.gmail.com" wrote:

> Could you perhaps post the query so that we know what steps you are
> taking to accomplish your goal?
>|||Hi
You may want to use decimal or numeric instead of money.
John
"SouRa" wrote:
[vbcol=seagreen]
> We have solved this issue by using money instead of float
> "nate.vu@.gmail.com" wrote:
>

Control jobs using SQL code?

Is there a way (system stored proc) to schedule/reschedule jobs through
SQL code (stored procedure) instead of GUI? We have a job set up on SQL
server and we are trying to control scheduling piece of it through
stored proc. Any help would be appreciated. Thanx!
*** Sent via Developersdex http://www.examnotes.net ***Have a look at sp_update_jobschedule in Books Online. This procedure is in t
he
msdb database, so it is used like
EXEC msdb.dbo.sp_update_jobschedule
"Test Test" wrote:

> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||have a look at following system stored procedures in BOL, there are few more
as well which are related to scheduling the job, you can have a look at them
in BOL.
sp_add_job
sp_add_jobstep
sp_add_jobschedule
sp_delete_job
sp_help_job
sp_help_jobstep
sp_update_job
"Test Test" wrote:

> Is there a way (system stored proc) to schedule/reschedule jobs through
> SQL code (stored procedure) instead of GUI? We have a job set up on SQL
> server and we are trying to control scheduling piece of it through
> stored proc. Any help would be appreciated. Thanx!
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Thanks, Mark. It really helps!
*** Sent via Developersdex http://www.examnotes.net ***

Contolling X axis labels in Chart

I have chart with data grouped by years and months. My data stops in Jan
2010 and the chart is showing this data with a year grouping of [2] instead
of [2010]. It appears to be truncating the year because it there is data for
only one month and it is the right most value in the chart. Any ideas on how
to force some extra space so that the entire year shows up? this currently
does not look very professional.
TIA
DeanI'm having a similar issue. The user is able to select a date range,
and a time frame (monthly, quarterly, yearly) and pass those
parameters into the report. When the report is generated, the monthly
grouping is fine. But quarterly and yearly, the brackets extend beyond
the range of the graph and are cut off. Sizing the chart area doesn't
do anything, because the brackets grow with the chart area. I'd either
like to turn off the brackets and just have the Labels or force more
space into the chart that doesn't extend the brackets.
Tim L

CONTAINS to behave as LIKE %abc%

Hello,
I want to use CONTAINS to search for charater patterns instead of using LIKE
. Here is an example:
Instead of,
Select *
from Northwind.dbo.Categories
Where Description LIKE '%ee%'
I want to use something like,
Select *
from Northwind.dbo.Categories
Where CONTAINS(Description, ' "*ee*" ')
But this doesn't seem to work. I know that the same syntax without the first
'*' works equivalent to LIKE 'ee%'.
Any suggestions?
FarhanContains is used for Full Text Search. Do you have a full text index on
that table?
David Gugick
Imceda Software
www.imceda.com|||Hi,
Thanks for your reply. Every thing is set up correctly, its just that I cant
get CONTAINS to behave as LIKE '%abc%' sort of syntax.
Farhan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23D23q$YKFHA.2800@.TK2MSFTNGP10.phx.gbl...
> Contains is used for Full Text Search. Do you have a full text index on
> that table?
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Farhan Noor Qureshi wrote:
> Hi,
> Thanks for your reply. Every thing is set up correctly, its just that
> I cant get CONTAINS to behave as LIKE '%abc%' sort of syntax.
> Farhan
>
I think without the first "*" is what you want.
David Gugick
Imceda Software
www.imceda.com|||If I remove the first *, it behaves as LIKE 'abc%' which is a good feature.
But I need to run an equivalent of LIKE '%abc%'.
Farhan
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OZv5jKbKFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Farhan Noor Qureshi wrote:
>
> I think without the first "*" is what you want.
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Farhan & Dave,
Farhan, the leading "*" (asterisk) wildcard in the search condition is
ignored and this is by design for SQL Server 7.0, 2000 and 2005.
SQL FTS only supports a trailing "*" (asterisk) wildcard, i.e.. a wildcard
word-based suffix search, for example, a search for "book*" will find book,
books, booking & booked. A leading "*" (asterisk) wildcard is not supported
because unlike T-SQL LIKE, SQL FTS is a language-specific linguistic search
method, while LIKE is a grep or pattern search method. Would you want to
search on "*og" and find God and Log in the same results?
Regards,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Farhan Noor Qureshi" <fqureshi@.N-O-S-P-A-MAiroom.com> wrote in message
news:ep1QTbcKFHA.3640@.TK2MSFTNGP12.phx.gbl...
> If I remove the first *, it behaves as LIKE 'abc%' which is a good
feature.
> But I need to run an equivalent of LIKE '%abc%'.
> Farhan
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:OZv5jKbKFHA.3340@.TK2MSFTNGP14.phx.gbl...
>

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas

"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>
|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaff
choppertoo
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message555779.html

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:
> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:
> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaf
-
chopperto
----
Posted via http://www.webservertalk.co
----
View this thread: http://www.webservertalk.com/message555779.htm

Constant scan when querying an identity field over linked server

When I perform a simple query over a linked server that is looking for a
specific identity value, SQL Server performs a constant scan instead of a
remote query. Running the same query locally gives expected results. Also,
forcing a data type conversion in the criteria gives expected results.
Example problematic query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
Any ideas as to why this is happening and how to permanently prevent it?
Thanks!!In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Look at the provider properties for the OLE DB provider you are using for
this linked server; they are sensitive to this attributes and they are
global, meaning they will affect all linked servers and remote queries using
this provider.
Sincerely,
Anthony Thomas
"Robert Davis" <RobertDavis@.discussions.microsoft.com> wrote in message
news:4B3A73A3-D24B-479B-9799-ADBC9054BAAB@.microsoft.com...
In case i wasn't clear on this point, it only happens when the criteria is
checking an identity field. Another other field works as expected.
"Robert Davis" wrote:

> When I perform a simple query over a linked server that is looking for a
> specific identity value, SQL Server performs a constant scan instead of a
> remote query. Running the same query locally gives expected results. Also,
> forcing a data type conversion in the criteria gives expected results.
> Example problematic query:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID = 1
> 0 records are returned, but a record with a MyID = 1 does exist.
> The following works as expected:
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where MyID Like 1
> Select MyID
> From Server2.MyDB.dbo.MyTable
> Where Cast(MyID As varchar) = 1
> Any ideas as to why this is happening and how to permanently prevent it?
> Thanks!!
>|||Robert .. or anyone
Did you mange to resolve this issue and if so what was the solution?
Thanks
Chris Longstaff