Copy data for reports

I need to copy a significant amount of data (1-2gb) during work hours
to another database for some lengthy numeric calculations and a report.
Phantom rows inserted during the copy operation would be a problem but
a serializable transaction would impact availability. I was
considering using BCP to export the data with a query since I think it
would be faster than directly copying across databases with t-sql. The
BCP docs dont say anything about other users inserting during a bulk
export. Are inserts prevented during bulk export? Is BCP a good way
to go? Other options? I must support sql2k and cannot rely on new 2005
features.
Jim KaneYou could restore or re-attach a backup of the production database.
<jkane@.satx.rr.com> wrote in message
news:1134740711.836936.47380@.g47g2000cwa.googlegroups.com...
>I need to copy a significant amount of data (1-2gb) during work hours
> to another database for some lengthy numeric calculations and a report.
> Phantom rows inserted during the copy operation would be a problem but
> a serializable transaction would impact availability. I was
> considering using BCP to export the data with a query since I think it
> would be faster than directly copying across databases with t-sql. The
> BCP docs dont say anything about other users inserting during a bulk
> export. Are inserts prevented during bulk export? Is BCP a good way
> to go? Other options? I must support sql2k and cannot rely on new 2005
> features.
> Jim Kane
>

copy a table

Hello all!

How to copy a table in MS SQL Server 2000 without chaning a structure? I
mean, I have one table, which has autoincrement numeric field (ID). When
I copy this table by exporting this table into the same database folder
I loose the specification of the field ID. Now it is not autoincrement
field but usual int field. Is it possible to copy this table without
changing data and structure of a table?

Kindest regards
Thank you
Marcin from Poland

*** Sent via Developersdex http://www.developersdex.com ***Marcin Zmyslowski wrote:
> Hello all!
> How to copy a table in MS SQL Server 2000 without chaning a
> structure? I mean, I have one table, which has autoincrement numeric
> field (ID). When I copy this table by exporting this table into the
> same database folder I loose the specification of the field ID. Now
> it is not autoincrement field but usual int field. Is it possible to
> copy this table without changing data and structure of a table?

With DTS copy object might do the trick. Otherwise, create DDL with QA or
EM, create the new table and then copy the data with "SET IDENTITY_INSERT
your_table ON". HTH

robert|||Insert into table1(columns)
select columns from othertable

Madhivanan

Converting varchar to Money

I have a a Case statement which sometimes fails when converting a Varchar
column which contains numeric values to Money. I can understand why it fail
s
when "1.05E+07" is passed in, but other values appear to fail also. I have
not located the other offending values yet (500,000 rows to sift through)
but converting them to Float first avoids the errors. Any ideas?
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
"Server: Msg 235, Level 16, State 1, Line 1
Cannot convert a char value to money. The char value has incorrect syntax."
However the following code, which converts to Float, and then to Money, does
not fail.
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
CONVERT(FLOAT,c1_PriorSalePrice) )(a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
the contents can be converted to any numeric type. See
http://www.aspfaq.com/2390 for the long-winded version of this.
(b) if using convert(money, convert(float())) works, then what is wrong with
using that?
(c) STOP STORING NUMERIC VALUES AS STRINGS!
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>I have a a Case statement which sometimes fails when converting a Varchar
> column which contains numeric values to Money. I can understand why it
> fails
> when "1.05E+07" is passed in, but other values appear to fail also. I
> have
> not located the other offending values yet (500,000 rows to sift through)
> but converting them to Float first avoids the errors. Any ideas?
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
> "Server: Msg 235, Level 16, State 1, Line 1
> Cannot convert a char value to money. The char value has incorrect
> syntax."
> However the following code, which converts to Float, and then to Money,
> does
> not fail.
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
> CONVERT(FLOAT,c1_PriorSalePrice) )
>
>|||Brother AAron,
The data in question is being provided in bulk by an outside vendor, so I
have no choice in how the data is provided or in what format. I have never
seen this situation before and it may have implications for other procedures
.
I will go read the link you provided.
Thanks
Michael
"Aaron Bertrand [SQL Server MVP]" wrote:

> (a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
> the contents can be converted to any numeric type. See
> http://www.aspfaq.com/2390 for the long-winded version of this.
> (b) if using convert(money, convert(float())) works, then what is wrong wi
th
> using that?
> (c) STOP STORING NUMERIC VALUES AS STRINGS!
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>
>

Converting varchar to int (or numeric)

In one of my stored procedures, a varchar is input that is assumed to be an int, and I need to validate that it is an int before I CAST it as an int. I'm currently using ISNUMERIC() to eliminate non-numeric values, but how do I recover gracefully if values such as '7.5e3', '$334', or '45.9943' sneak through? They all pass the ISNUMERIC() test, but cause errors when being cast to int. Similar problems if casting to numeric, etc.You can use the case statement - for example:

declare @.test varchar(20)
select @.test='123.3'
select case when charindex('.',@.test) > 0 then cast(@.test as decimal(10,2))
when charindex('e',@.test) > 0 then cast...
else cast(@.test as int) end|||Thanks. That should work. On retrospect, it's obvious enough I should have come up with that myself!

converting to numeric (6,3)

Hey guys,

I am trying to achive the following:

Truncate the values 1680.390000000000000000

to numeric (6,3)

All works ok until i hit a value over 1000, (as above)

The code i was using is:

cos1 = 1680.390000000000000000000

CONVERT (NUMERIC(6 , 3), ROUND(COS1, 3))

I have tried a few others, but nothing seems to work..

I get the following error. "Arithmatic Overflow"

Has anyone got any suggestions?

Thanks guys,

Scotty

A numeric(6,3) would only accept a number as large as 999.999 -six digits total, three digits to the right of the decimal.

Your value, 1680.xxxx exceeds the maximum size and creates an overflow condition -resulting in an error.

Perhaps you need a larger number, like maybe, just guessing here, numeric(8,3)?

|||

Brilliant!!!!

thanks vry much Archie.. this has been confusing me all day,, the way they explain it looks to me as 6 to the left, and 3 to the right...

Thanks again

Scotty

Converting text to numeric

I have a database table with 2 numeric fields. The values to be inserted into these fields come from text boxes in an ASP.NET page. So in the parameters to add into these fields I simply use 'textbox1.text' & textbox2.text'.

One of the fields accepts the data within the text box and adds it into the appropriate numeric data field in the table. However on the other text box I get an error message 'Error converting data type nvarchar to numeric.'

Does anyone know why this occurs just on the one entry and not the other?, and if anyone has any suggestions on how to get around this please let me know.

Many ThanksWhat are the values of textbox1 and textbox2? If you have "fred" in textbox2, of course it cannot be converted to a numeric value.

Converting SQL numeric datatype to a mainframe (cobol) packed field

Hopefully I have the right forum for this questions...

I am trying to convert a field defined in a SQL database as NUMERIC (15,2) to something I can export to a mainframe. The field on the mainframe is a packed field that is 6 (alphanumeric)chars in length. Mainframe defines as PIC X(6) and redefines as PIC 9(10)V99 PACKED(uses each byte of the 2-byte char). Must be zero filled to the left. Should look like the following for an amount of $35.25: 000000003525

Pulling my hair out...can anyone help before I go bald?A lot depends on how you are trying to export the data from the SQL database to the mainframe. If you use one of the ETL or data connector solutions, you should be able to simply "push" the data across with zero conversion effort.

If you are converting to a flat file to import into a mainframe system that isn't supported by ETL, you'll have to do the conversion on the SQL side because very few mainframe packages deal very well with "human formatted" data, which is about all current generations of SQL engines produce. The major problem here is that you are trying to get the SQL engine to format things in a way that they rarely use (I can't imagine a business user requesting data formatted as 9(10)V99).

Depending on which SQL engine you are using, the syntax will be slightly different. On the chance that you mean SQL Server, I'd use:SELECT Replace(Replace(Str(35.25, 13, 2), '.', ''), ' ', '0')If this doesn't help, post again with more details, and I bet one of the bright folks here will be able to help.

-PatP|||The replace did not work any better than the replicate function.

Here is some more info...

If I declare a variable as binary and then fill it in the following manner I get the data as I want it...

declare @.amt binary(6)
select @.amt = 0x000000003525

but, I need to convert my field "disbursement_amt" (defined as numeric (15,2) )off table "d" to binary...and I can't figure out how to do something similar to:

select @.amt = 0x<d.disbursement_amt)>

...any more help?|||Ok, my code works just fine using SQL Server 2000 and Microsoft Query Analyzer (I just tested it), what are you using (engine and client)?

-PatP|||Your code does work!...but that's not the problem...see how I used it below

declare @.field numeric(15,2)
select @.field = '35.25'
select @.field
declare @.outnum char(12)
SELECT @.outnum = Replace(Replace(Str(@.field, 13, 2), '.', ''), ' ', '0')
select @.outnum

...Great if I need the output in character format, but I need the data output (@.outnum) in binary format. CONVERT() does not do it...|||Oh, BTW, I'm using SQL Server 2000 and Embarcadero DBArtisan

Converting numeric values to String in MDX

Is it possible to convert a numeric value to a string using MDX functions? I am defining a goal for my KPI and i need to display two values for the goal, (a percentage value like 75% and an actual value e.g. ￡200K) so i was thinking of converting both values to a string and then concatenate the two strings for the KPI goal. Is it possible to do this OR is there a better way of doing it?

Thanks in advance.

I'm not sure if this is the best way of handling the issue but it is technically possible. Here is a code sample illustrating one string assembly:

Code Snippet

with member [Measures].[Category Sales String] as

"*** " + CSTR([Measures].[Reseller Sales Amount].Value) + " ***"

select

[Measures].[Category Sales String] on 0,

[Product].[Category].Memberson 1

from [Adventure Works]

|||

Yes, certainly - just use the Cstr function (which is, by the way, native MDX although it behaves the same as the VBA function of the same name - see http://www.e-tservice.com/Files/vba_functions_in_as2005.doc) to cast the values to a string. For example:

Code Snippet

with member measures.test as

cstr([Measures].[Internet Sales Amount]) + " Hello!"

select measures.test on 0 from

[Adventure Works]

However, I'm not sure that concatenating these values is really a good idea. Why not create an extra dimension, or an attribute on a dimension, which has one real member and one calculated member and then allow the users to switch between slicing these members to display actual values and percentages? This is what's referred to as a shell dimension or a time utility dimension, and you can find out more about how to implement one of these here:

http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf

HTH,

Chris

|||Thanks a lot this is a real life saver for me. I want to concatenate because, I am displaying these values on a dashboard and i just want to display everything on the same screen.

Converting numeric to non-numeric?

I'm a beginner to Crystal. Honestly, I had no intention of learning it beyond a user level but our developer is MIA and we need to tweak our template. We have a field that use to be a number (a "MapID" in this case), it would be 1, 2, 3, 4, etc. Now we want it to reflect a different ID that is going to look like 1a, 1b, 1c, 2, 3a, etc. I've changed the RPT query in access to populate with the new ID, keeping the column the same, but of course nothing in life is that simple. When trying to export the Crystal Template to PDF I get this error almost immediately:

"The string is non-numeric"

The Formula Workshop pops up and says this:

CDbl({RPT_Detailed_Findings.MAPID})

By going to the subreport and right clicking on the specific field and going to Edit Formula the Formula Workshop says this:

If {?Pm-RPT_Detailed_Findings.source} = "mapped" then
{?Pm-RPT_Executive_Summary.MAPID}
else "Unmapped";

I am so new to this I can't really see why it would only accept a number, my only guess is that the restriction has to do with "CDbl" or "?PM". Am I even close?

I'm in a jam here as there is nobody else to go to in the company, I'm it. And the developer is non-responsive.

Thanks for the help.remove cDbl,

as 1a ur new id is already string, u cannot convert it to a double numeric value

Hope it helps|||Now they are all out of order and the letters still did not come in, also I believe only the records that did not have letters made it into the report. The report exported though.

Converting numeric data type to text data type

Hi,

I would like to convert a dollar amount ($1,500) to represent Fifteen
hundred dollars and 00/100 cents only for SQL reporting purposes. Is
this possible and can I incorporate the statement into an existing
left outer join query.

Thanks in advance,

Gavin[posted and mailed, please reply in news]

Gavin (gsegal@.mps.com) writes:
> I would like to convert a dollar amount ($1,500) to represent Fifteen
> hundred dollars and 00/100 cents only for SQL reporting purposes. Is
> this possible and can I incorporate the statement into an existing
> left outer join query.

If it is for reporting issues, it may be better to do this on client
level, but you could use a table to hold the various strings. I would
suggest that it will be simpler to implement, if you permit 1500 to be
rendered as "One thousand five hundred".

The table would look like this:

CREATE TABLE numberstrs (nr tinyint NOT NULL
CONSTRAINT ck_nr CHECK (nr BETWEEN 0 AND 99),
str varchar(23) NOT NULL,
CONSTRAINT pk_nr PRIMARY KEY (nr))
go
INSERT numberstrs (nr, str) VALUES (0, '')
INSERT numberstrs (nr, str) VALUES (1, 'one')
...
INSERT numberstrs (nr, str) VALUES (99, 'ninety-nine')

You would then use it as

SELECT CASE WHEN mil.str IS NOT NULL
THEN mil.str + ' millions and '
ELSE ''
END +
CASE WHEN hth.str IS NOT NULL
THEN mil.str + ' hundred '
ELSE ''
END +
CASE WHEN th.str IS NOT NULL
THEN th.str + ' thousand and '
ELSE ''
END +
CASE WHEN hun.str IS NOT NULL
THEN hun.str + ' hundred '
ELSE ''
END +
one.str + ' dollars ' +
ltrim(str((a.amt * 100) % 100)) + '/100 cents'
FROM tbl a
JOIN numberstrs one ON a.amt % 100 = nr
LEFT JOIN numberstrs hun ON (convert(int, a.amt) / 100) % 10 = nr
LEFT JOIN numberstrs th ON (convert(int, a.amt) / 1000) % 100 = nr
LEFT JOIN numberstrs hth ON (convert(int, a.amt) / 100000) % 10 = nr
LEFT JOIN numberstrs mil ON (convert(int, a.amt) / 1000000) % 100 = nr

This is something I made up, and I have not tested it. I don't think
the result will be that excellent for all numbers. For instance 101
would not come out pretty if you want it as "One hundred and one".
For even values like "One million" you need to add some logic.

Notice that there is a upper limit of 100 millions as I have written the
query. You would also have to arrange for the first letter in the
resulting string to be uppercase.

You mention how would incorporate into an existing query, but since I
don't see that query and don't where you want the value, I'm not taking
a stab at that.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

COnverting Numeric data type (Oracle) to Date Data type using SSIS

We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle.

I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination.

On Error, If I fail the component, then the error is :

There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.".

Regards

RH

If you are using a query to go against Oracle, you'll likely want to cast that as a varchar and then work with it in SSIS. Not sure that YYYYMMDD will cast to DT_DATE. You'll likely have to substring pieces of that to get it into a date. There are plenty of examples here on this forum for doing that. Just search for "YYYYMMDD."|||

Thanks for the reply. I used SQL on the OLEDB Source and added a TO_DATE expression in the SQL and use that column as input to my destination column.

This worked fine.

RH

Converting nchar to int (or numeric/decimal)

Hi ,

I have a column in my extract table as nchar(3) and in the destination (the same column with diff name ) it is decimal(3,0) .....i tried to use dataconversion transformation.....i even tried to use cast/convert fn's in the SQL Command (which i use in the "Source Transformation" to get the columns from the extract table).

I tried all the ways i can and still i get the same error..:

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80040E07. An OLE
DB record is available. Source: "Microsoft OLE DB Provider
for SQL Server" Hresult: 0x80040E07 Description: "Error
converting data type nvarchar to numeric.".

Can we actually do it...? any help would be appreciated.

thanks

ravi

you have non-numeric data in the source column that fails the conversion. Have you tried to configure the error output of the conversion transformation to re-direct error so the 'bad' rows are sent to a diffrent output where youc can inspect them?|||

Hi,

No ..No....Its Numeric in the source though the data type is nchar(3).

The thing is actually the column in the extract table has records as 110,150,160,170 and so on .....but its data type is nchar(3).The reason is this extract table comes from AS400 and while extracting As400 doesnt allow to send records if the column in the extract table on Sql Server is defined other than nchar...so have to go with nchar or nvarchar.

thanks

ravi

|||Try a derived column to cast the records. (Why not make them integers?)

(DT_I4)[Column]|||

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

|||

Adamus Turner wrote:

Works fine for me:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'211'

INSERTINTO #temp SELECT'311'

SELECTSUM(cast(test asint))FROM #temp

DROPTABLE #temp

Adamus

Yeah, but Ravi's source is AS400, not SQL Server. Though, the other option is to stage the data into SQL Server as it is in AS400, and then use a cast statement when pulling records from the staging table as Adamus has illustrated here.|||

Hey phil,

I extract the table from AS400 to extract_table on my extract database which is SQL Server DB , from there when i need to make some transformations then my column on the staging is decimal(3,0)....where as on the extract table it is nchar(3).

I tried similarly by declaring some variables and casting and converting ...it doensnt work..or am i doing some mistake...:

DECLARE @.X nchar(3)

DECLARE @.Y int

SET @.X ='GGG'

SELECT @.X

SET @.Y =Cast(@.X asint)

SELECT @.Y

thanks for all you views/immediate response's...

let me know if i am doing a mistake here..

thanks,

ravi

|||Then Adamus' example should apply.|||

Hi ,

I think i figured what it was i mean i have like around 200,000 records being pulled in to the extract there ....from which were few records which were nonnumeric in the nchar(3) column i.e apart from '100' , '510' ......there were records with 'MVR' which was causing all the problem... guess...

thanks for the quick responses...

thanks..!!

Ravi

|||

Then check isnumeric before the cast and set non-numerics to 0:

Code Snippet

CREATETABLE #temp(test nchar(3))

INSERTINTO #temp SELECT'111'

INSERTINTO #temp SELECT'AAA'

INSERTINTO #temp SELECT'311'

SELECTCASEISNUMERIC(test)WHEN 1 THENCAST(test asint)ELSE 0 END

FROM #temp

DROPTABLE #temp

Adamus

|||Just a tweak. You may want to set them to NULL, versus a 0. NULLs would be excluded from any math/aggregation totals you may be creating, such as an average. Using a 0 would allow that value to be used. So it's up to you.

In SSIS, you can use a conditional split to move numeric records to your destination table, and non-numerics to the trash, if you desire. Or, you can assign new values to the non-numerics. Up to you.