Sql Case sensitivity: contains

Showing posts with label contains. Show all posts

Tuesday, March 20, 2012

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
> > I have a table with a column of datatype var and it has to be changed to nvar.
> > The table contains millions of records and with an alter table my log get's
> > full, starts growing and in the end I my query stops with no result.
> > What is the best way to accomplish this change while keeping the data and
> > having not to much logging?
> >
> > Any help is appreciated.
> > I was thinking of an insert into a new table...|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
> > Make sure you have good backup firstly before doing what I can think out:
> > 1) If there are index on the column, drop them before changing its datatype.
> > 2) change the db recovery mode to 'simple'.
> > 3) shrink the db size and some other db size (including tempdb, or
> > considering change their recovery mode to 'simple' before shrinking) to free
> > more disk space.
> > 4) do the altering
> > 5) re-create the index.
> >
> > James
> >
> > "Zekske" wrote:
> >
> > > I have a table with a column of datatype var and it has to be changed to nvar.
> > > The table contains millions of records and with an alter table my log get's
> > > full, starts growing and in the end I my query stops with no result.
> > > What is the best way to accomplish this change while keeping the data and
> > > having not to much logging?
> > >
> > > Any help is appreciated.
> > > I was thinking of an insert into a new table...|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>> Notice my suggestion might still fail finally, and you should be sure of the
>> implication of changing recovery model.
>> James
>> "James Ma" wrote:
>> > Make sure you have good backup firstly before doing what I can think out:
>> > 1) If there are index on the column, drop them before changing its datatype.
>> > 2) change the db recovery mode to 'simple'.
>> > 3) shrink the db size and some other db size (including tempdb, or
>> > considering change their recovery mode to 'simple' before shrinking) to free
>> > more disk space.
>> > 4) do the altering
>> > 5) re-create the index.
>> >
>> > James
>> >
>> > "Zekske" wrote:
>> >
>> > > I have a table with a column of datatype var and it has to be changed to nvar.
>> > > The table contains millions of records and with an alter table my log get's
>> > > full, starts growing and in the end I my query stops with no result.
>> > > What is the best way to accomplish this change while keeping the data and
>> > > having not to much logging?
>> > >
>> > > Any help is appreciated.
>> > > I was thinking of an insert into a new table...|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
> >I did change the recovery model to simple so that doesn't help.
> > That's why I was thinking about doing a bulk import or something like that
> > but I'm not sure it's possible.
> >
> > Thanks anyway
> >
> > "James Ma" wrote:
> >
> >> Notice my suggestion might still fail finally, and you should be sure of the
> >> implication of changing recovery model.
> >>
> >> James
> >>
> >> "James Ma" wrote:
> >>
> >> > Make sure you have good backup firstly before doing what I can think out:
> >> > 1) If there are index on the column, drop them before changing its datatype.
> >> > 2) change the db recovery mode to 'simple'.
> >> > 3) shrink the db size and some other db size (including tempdb, or
> >> > considering change their recovery mode to 'simple' before shrinking) to free
> >> > more disk space.
> >> > 4) do the altering
> >> > 5) re-create the index.
> >> >
> >> > James
> >> >
> >> > "Zekske" wrote:
> >> >
> >> > > I have a table with a column of datatype var and it has to be changed to nvar.
> >> > > The table contains millions of records and with an alter table my log get's
> >> > > full, starts growing and in the end I my query stops with no result.
> >> > > What is the best way to accomplish this change while keeping the data and
> >> > > having not to much logging?
> >> > >
> >> > > Any help is appreciated.
> >> > > I was thinking of an insert into a new table...
>
>

Change datatype var to nvar but log gets full

Thursday, February 16, 2012

categories...

Hi,

i'm devoloping a web application that contains categories for example

Category1

Category11

Category111

Category1111

.........

Category112

Category12

Category2

categories with categories with categories... someone can help me about designing an elegant table for this situation.

Thanks

Fdo.

ive successfully used a technique very much like this:http://www.developerfusion.co.uk/show/4633/2/

this articel also offer a somewhat different approach that i've not tried:http://www.sitepoint.com/article/hierarchical-data-database/2

|||

excelent.

thanks Mike

Tuesday, February 14, 2012

Catalog size/ratio?

I want to get a estimate of size of the catalog based on the size of the
data it is indexing.
for example, for 10G of Table size(table contains just a column of
varchar(255)), what is the size of its catalog?
I know that it depends on its content, but just want to get a idea of how
big catalog will be since I am thinking using 64 bit platform and want to
load the whole catalog inside the memory. Knowing the size of catalog help
me determine how many Gigs of memory I have to buy...
Thanks
--Xin Chen
That's really quite unpredictable. It not only depends on content, but then
on compressibility as the catalog files are compressed.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Xin Chen" <xchen@.xtremework.com> wrote in message
news:%23uyFxuLJFHA.2640@.TK2MSFTNGP09.phx.gbl...
> I want to get a estimate of size of the catalog based on the size of the
> data it is indexing.
> for example, for 10G of Table size(table contains just a column of
> varchar(255)), what is the size of its catalog?
> I know that it depends on its content, but just want to get a idea of how
> big catalog will be since I am thinking using 64 bit platform and want to
> load the whole catalog inside the memory. Knowing the size of catalog
help
> me determine how many Gigs of memory I have to buy...
> Thanks
> --Xin Chen
>

Friday, February 10, 2012

Cast as Integer problem.

I have DB that contains a field named GarmentSize. The field type is TEXT as it can contain for example

8
10
12
14

or

L
XL
XXL

The problem is when I run a query and order by GarmentSize the results displayed are as follows if it contains numbers

10
12
14
8

Instead of

8
10
12
14

If I use order by CAST(GarmentSize as Integer) this works fine, unless the field contains text then is throws the following error.

Syntax error converting the varchar value 'XL' to a column of data type int

Is there a way of determining if the field contains characters that can be casted before doing the CAST?I'd convert the single digit numbers to a more managable form, something like: ORDER BY CASE WHEN GarmentSize LIKE '[0-9]'
THEN ' ' + GarmentSize ELSE GarmentSize END-PatP|||yeah, but pat, that still doesn't solve the sequencing problem

appending '42' to a space is still gonna come after appending '105' to a space

gosman, i urge you most strenuously, downsize your TEXT field to at least VARCHAR(8000)

in fact, i would take bets that you could probably downsize it to VARCHAR(100) and still accommodate all the GarmentSizes in your database|||Wouldn't a size 42 have to be about five meters tall, and weigh 1000 Kg or more? ...and I thought that Americans were big! I'm not sure I'm prepared to explore the size 105.

To futher address this problem so it can handle more cases, how about:CASE WHEN IsNumeric(GarmentSize)
THEN Cast(Cast(GarmentSize AS MONEY)) AS CHAR(50))
ELSE GarmentSize END-PatP ;)|||varchar?...if we're changing datatypes, why not santize the data and make it int?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 text)
GO

INSERT INTO myTable99(Col1)
SELECT 'XL' UNION ALL
SELECT '1'
GO

SELECT CASE WHEN ISNUMERIC(CONVERT(varchar(8000),Col1)) = 0 THEN 'NO' ELSE 'YES' END AS [A Number?], Col1
FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||sanitize the data and make it integer? hitting the 'ritas at lunch again, were we?

what do you propose as integer equivalents of XS, S, M, L, XL?

oh, oh, oh, please say "use foreign keys to look up the size name"

:) :) :)|||Thanks Guys.

Pat the field type is actually VARCHAR 50

I've tried the following

Select GarmentSize from PHOOLRATIOS order by CASE WHEN IsNumeric(GarmentSize)THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))ELSE GarmentSize END

But I get this message. Incorrect syntax near the keyword 'THEN'.

Forgive my ignorance but I'm a complete SQL novice.|||sanitize the data and make it integer? hitting the 'ritas at lunch again, were we?

what do you propose as integer equivalents of XS, S, M, L, XL?

oh, oh, oh, please say "use foreign keys to look up the size name"

:) :) :)

I would say that the data is not normalized, since XL is a representation of a size range, not the actuall size...

And the Foreign key bit for a surrogate

puuuuleeeze

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

You might want a "size fits from" and a "size fits to" columns

Now aint that nitpicky...|||gosman, you lied, your first post distinctly says TEXT :)

try WHEN IsNumeric(GarmentSize) = 1 THEN ...

sorry, brett, garmentsize is normalized

if a range is your criterion of not normalized, then any of your tables that uses a datetime column is not normalized, since we all know that a datetime value is not a single value but rather represents a range

;)|||SELECT GarmentSize
FROM PHOOLRATIOS
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize|||gosman, you lied, your first post distinctly says TEXT :)

try WHEN IsNumeric(GarmentSize) = 1 THEN ...

sorry, brett, garmentsize is normalized

if a range is your criterion of not normalized, then any of your tables that uses a datetime column is not normalized, since we all know that a datetime value is not a single value but rather represents a range

;)

Who's been drinking at lunch?|||Sorry, that was my bad! How about:Select GarmentSize
from PHOOLRATIOS
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))
ELSE GarmentSize
END-PatP|||Hi Pat

Select GarmentSize
from PHOOLRATIOS
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))
ELSE GarmentSize
END

Server: Msg 1035, Level 15, State 10, Line 4
Incorrect syntax near 'Cast', expected 'AS'.|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(GarmentSize char(50))
GO

INSERT INTO myTable99(GarmentSize)
SELECT 'XL' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '10' UNION ALL
SELECT '11' UNION ALL
SELECT '112'
GO

SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize

--Pat's off his meds..forgive him

Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
END

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||brett obviously is dropping his myTable99 before looking at the results

this --Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
ENDdoes not sort the "numeric" values into numeric sequence!!!

insert the value '42' and you'll see what i mean

and yes, every datetime value represents a range

and i don't start drinking until after 5:00 p.m.

:) :) :)|||Well I'll be a Hunkey's Monkle! Brett is correct.

It appears that CAST does a right fill with spaces. My example ought to read:SELECT GarmentSize
FROM PHOOLRATIOS
ORDER BY CASE WHEN 1 = IsNumeric(GarmentSize)
THEN Str(GarmentSize, 50)
ELSE GarmentSize
ENDThe alternative that he posted would also be good, although I'd reverse the order to put the numeric values first, something like:SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN 1
ELSE 2
END, GarmentSize-PatP|||brett obviously is dropping his myTable99 before looking at the results

this --Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
ENDdoes not sort the "numeric" values into numeric sequence!!!

insert the value '42' and you'll see what i mean

and yes, every datetime value represents a range

and i don't start drinking until after 5:00 p.m.

:) :) :)

Rudy...ya gotta stop smokin crack...it's not good for you...

Yes, I know..that's Pat's query...did you see the first query?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(GarmentSize char(50))
GO

INSERT INTO myTable99(GarmentSize)
SELECT 'XL' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '10' UNION ALL
SELECT '40' UNION ALL
SELECT '11' UNION ALL
SELECT '112'
GO

SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize

SET NOCOUNT OFF
DROP TABLE myTable99
GO

And what's with the very specific point in time being a range?|||sorry, pal, i don't do crack either

keep fishing, you're sure to hit one of my vices eventually

there is no such thing as a point in time

what i was trying to get you to see goes something like this --

if A implies B, and C is A, then C implies B

A = a range
B = not normalized
C = some value

you claimed a range was not normalized

i'm saying that by this very same logic, since a datetime value is a range, therefore any of your tables using a datetime value is not normalized

actually, i have no problem with datetime values, and i know exactly how to handle them

what i was trying to point out is that your claim that a size is actually a range and therefore not nomalized is nonsense

no offence, good buddy, but it was

and if you were only joking, then you simply must learn how to use smileys correctly|||It appears that CAST does a right fill with spaces. My example ought to read...It's not the CAST, it's the datatype that you use within the CAST, - CHAR(50). If you used VARCHAR(50) there won't be any "right fill"-ing ;)|||OK...attempt to be clear.

Our friends Column, GarmentSize, is retaining 2 types of data in the same column.

One is the very specific size of a garmet, let's say a size 10.

Recording the size of the garment as M or medium presents no specific size. M is a range of sizes, like 10-12. Not 1 size.

Apples and Oranges. They're both fruit, but they are essentially different.

And I did say I was being picky, but I'd still say they'd need another table that addresses this and descibes what each (M, L, XL, ect) actually means.

I still don't get what you mean by that a datetime column represents a range.

I never said a "range was not normalized". It's the combination of a specific size and a range of sizes (XL, for example) is not normalized. The size range values in their own column would be fine.

OK, Vices, how about Fishing?|||yes, XL is a range, it is the range between where L leaves off and XXL begins

just like size 8 is a range, between where 7 leaves off and 9 begins!!

nope, i don't fish, nor do i hunt

i do like baiting gullible DBAs, though, and you are providing a world of entertainment for us today!!

have you ever gone into the Senior VP of Marketing's office and told her she cannot use XL as a size, and should use a numeric range instead?

what utter nonsense

"The size range values in their own column would be fine" -- they already are in their own column, it's called GarmentSize, and it has a perfectly valid domain consisting of sizes which happen to be either numbers or letters

but you DBAs don't know what a domain is, so let's leave the modelling nuances alone and go back to talking about other stuff like raid stripes and scuzzies and other important stuff

:)|||Then it's got to be some sort of hallucinogen

Is an apple a range, is an airplane a range, is a shirt a range?

Isn't our freinds problem proof enough that their data is not normalized?|||Is an apple a range, is an airplane a range, is a shirt a range?no, they are not

Isn't our freinds problem proof enough that their data is not normalized?no, the fact that numerics in a VARCHAR column sort as characters is certainly not proof that the data is not normalized

i hesitate to ask, but do you even know what normalized means?

like i suggested in my last post, perhaps (for your sake) we should just drop the subject|||Scrapped! Looked good on paper...|||OK, try again:

ORDER BY right('000000000...50 of these...0000000' + GarmentSize, 50)|||Since the data itself is not going to be sorted very well, dare I suggest a sortorder column? This may curdle Rudy's blood, but hey, why should he have all the fun, eh? ;-)|||fantastic idea!! a sortorder column!!

okay, now, let's see, how should we populate this column

I KNOW!! let's make it numberic, and for "numeric" sizes, we'll use the actual numbers!!

now, what about those pesky alpha sizes, like S, M, XL, and so on...

HEY!! BRILLIANT IDEA!! let's assign S to 7, M to 9, XL to 14, ...

uh oh, wait a sec

that would make a man's XL shirt, which is approximately a chest 42" the same as a woman's XL dress, which is a size 6...

brett? where are you, brett? could you please normalize this sortorder column for us?

pretty please?|||You just aren't seeing it Rudy..

XL is not 1 size.

And I gotta admit, you're a little edgy today...

In pants a men's XL would be 38-40...

woops, gotta run...

Don't forget your midol

Cast as Decimal

myTable in the below code examples resides in a linked Visual FoxPro
database. myTable contains a field called myDecimalField as well as several
others exactly like it and are of Decimal (9,1) not null type.
All the other fields select fine in SQL, but myDecimalField gives the ERROR
below when I SELECT it.
Just for a test, I CASTed myDecimalField in CODE 1 below as a VarChar type
and SQL returns it fine. So, I tried CODE 2 below and tried to CONVERT the
VarChar CAST and I get the same ERROR below.
Can someone help me with syntax in CODE 2 and convert myDecimalField into a
DECIMAL format so I can retain the fields decimals and numberic type?
**********************
CODE 1 (works):
SELECT myIdField, CAST(myDecimalField AS
VARCHAR(55)) AS myDecimalField FROM myTable
CODE 2 ( doesn't work):
SELECT myIdField, CONVERT(DECIMAL(18, 4),
CAST(myDecimalField AS VARCHAR(55))) AS myDecimalField FROM myTable
ERROR:
OLE DB error trace [OLE/DB Provider 'VFPOLEDB'
IRowset::GetData returned 0x80040e21: Data status returned from the
provider: [COLUMN_NAME=myDecimalField
STATUS=DBSTATUS_E_UNAVAILABLE]].
Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column
'[VFPOLEDB].myDecimalField' from the OLE DB provider 'VFPOLEDB'. The
provider cannot determine the value for this column.Scott Bailey (sbailey@.mileslumber.com) writes:
> myTable in the below code examples resides in a linked Visual FoxPro
> database. myTable contains a field called myDecimalField as well as
> several others exactly like it and are of Decimal (9,1) not null type.
> All the other fields select fine in SQL, but myDecimalField gives the
> ERROR below when I SELECT it.
> Just for a test, I CASTed myDecimalField in CODE 1 below as a VarChar type
> and SQL returns it fine. So, I tried CODE 2 below and tried to CONVERT the
> VarChar CAST and I get the same ERROR below.
> Can someone help me with syntax in CODE 2 and convert myDecimalField
> into a DECIMAL format so I can retain the fields decimals and numberic
> type?
The situation certainly looks spooky, but the root problem is obviously
a problem with FoxPro, or the FoxPro provider. I would guess that there
some rows where myDecimalField has some illegal value.
Assuming that myTable has an column called id, of which the lowest value
is 1, and the highest is 100, you could do
SELECT ... FROM myTable WHERE id BETWEEN 1 AND 50
If that gives the error, narrow down the interval to 1 AND 25 and so on.
Of course it's a good idea to look at the data from FoxPro as well.
If you want an SQL Server solution, you would have to bounce the data
over a temp table, so the conversion from varchar takes place in
SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Couple of things:
1. The error occurs on all records so i know it's not bad data, plus there's
another field with same problem.
2. Could you provide some syntax example of creating a temp table with the
varchar conversion and transferring it as you suggested? I've never used a
temp table before.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A9BCADA85Yazorman@.127.0.0.1...
> Scott Bailey (sbailey@.mileslumber.com) writes:
> The situation certainly looks spooky, but the root problem is obviously
> a problem with FoxPro, or the FoxPro provider. I would guess that there
> some rows where myDecimalField has some illegal value.
> Assuming that myTable has an column called id, of which the lowest value
> is 1, and the highest is 100, you could do
> SELECT ... FROM myTable WHERE id BETWEEN 1 AND 50
> If that gives the error, narrow down the interval to 1 AND 25 and so on.
> Of course it's a good idea to look at the data from FoxPro as well.
> If you want an SQL Server solution, you would have to bounce the data
> over a temp table, so the conversion from varchar takes place in
> SQL Server.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||scott (sbailey@.mileslumber.com) writes:
> 1. The error occurs on all records so i know it's not bad data, plus
> there's another field with same problem.
Weird. But I'm not a Foxpro person, so I have no idea of what could
be going on.

> 2. Could you provide some syntax example of creating a temp table with the
> varchar conversion and transferring it as you suggested? I've never used a
> temp table before.
CREATE TABLE #spookydecimal (id int NOT NULL,
decvalue varchar(55) NULL)
-- Add other columns as needed.
INSERT #spookydecimal(id, decvalue)
SELECT myIdField, CAST(myDecimalField AS
VARCHAR(55)) AS myDecimalField
FROM myTable
SELECT id, decvalue, case(decvalue as decimal(18,4))
FROM #spoookydecimal
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97A9DB71F9EE0Yazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Weird. But I'm not a Foxpro person, so I have no idea of what could
> be going on.
>
> CREATE TABLE #spookydecimal (id int NOT NULL,
> decvalue varchar(55) NULL)
> -- Add other columns as needed.
> INSERT #spookydecimal(id, decvalue)
> SELECT myIdField, CAST(myDecimalField AS
> VARCHAR(55)) AS myDecimalField
> FROM myTable
> SELECT id, decvalue, case(decvalue as decimal(18,4))
> FROM #spoookydecimal
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

Tuesday, March 20, 2012

Change datatype var to nvar but log gets full

Change datatype var to nvar but log gets full

Change datatype var to nvar but log gets full

Thursday, February 16, 2012

categories...

Tuesday, February 14, 2012

Catalog size/ratio?

Friday, February 10, 2012

Cast as Integer problem.

Cast as Decimal

Sql Case sensitivity

Blog Archive

About Me