Numeric to real type

Hi Everyone,

I hope this is not a silly one. Well, I have two tables - table 1 destination, table 2 source. Data is being imported from table 1 to table 2. For ages this has been working fine but now we get an error which just says can not insert value 1 (.i.e. user defined error message from error handling). I have been trying to run this whole thing bit by bit and detect the actual error but for some reason it doesn't show any error but only on live.

I could detect just one potential issue that value 1 in table1 is numeric defined with size 13 precision (20,10) and in table 2 it is defined as real. But I am not sure if conversion from numeric to real can be any problem as the SP didn't show me when I tried to run manually.

Can anyone double on my assumption that this might be a potential problem which doesn't seem to occur on test server as it might be because the data are different in test and live.

Thanks a ton in advance,
Sree

Quote:

Originally Posted by sreemati

Hi Everyone,

I hope this is not a silly one. Well, I have two tables - table 1 destination, table 2 source. Data is being imported from table 1 to table 2. For ages this has been working fine but now we get an error which just says can not insert value 1 (.i.e. user defined error message from error handling). I have been trying to run this whole thing bit by bit and detect the actual error but for some reason it doesn't show any error but only on live.

I could detect just one potential issue that value 1 in table1 is numeric defined with size 13 precision (20,10) and in table 2 it is defined as real. But I am not sure if conversion from numeric to real can be any problem as the SP didn't show me when I tried to run manually.

Can anyone double on my assumption that this might be a potential problem which doesn't seem to occur on test server as it might be because the data are different in test and live.

Thanks a ton in advance,
Sree

Well that should not be a problem because 1 is just a single digit number.
We would not be able to find the real reason with the help of user defined message.
Is it possible to find the original error message and POST it for my reference?|||

Quote:

Originally Posted by amitpatel66

Well that should not be a problem because 1 is just a single digit number.
We would not be able to find the real reason with the help of user defined message.
Is it possible to find the original error message and POST it for my reference?

Hi

I did not mean the Number 1 by value1, I just meant it in reference to a field value1. Yes, I know that I am trying to re-create the error and get the error message, hopefully I get lucky soon and than I will post it.

Thanks,
Sree|||

Quote:

Originally Posted by sreemati

Hi

I did not mean the Number 1 by value1, I just meant it in reference to a field value1. Yes, I know that I am trying to re-create the error and get the error message, hopefully I get lucky soon and than I will post it.

Thanks,
Sree

Then I assume there should be some problem with the data.
Ok, find out the exact error message and POST here for my reference!!

NULL Values

I imported an excel spreadsheet and it loaded a ton of records with NULL
values. When I try to delete them, it tells me To many rows will be
affected.
what is a SQL statement that I can type that will delete all the records
that has NULL in a particular field?DELETE FROM YourTable
WHERE YourColumn IS NULL
... and quit using EM to edit your data... it's not designed for that and
will definitely cause you problems in the long run (like this one)
"johnfli" <john@.here.com> wrote in message
news:OuGGCt7qEHA.3712@.TK2MSFTNGP15.phx.gbl...
> I imported an excel spreadsheet and it loaded a ton of records with NULL
> values. When I try to delete them, it tells me To many rows will be
> affected.
> what is a SQL statement that I can type that will delete all the records
> that has NULL in a particular field?
>
>|||what would you propose instead?
Thank you for your assistance.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OR333v7qEHA.3700@.TK2MSFTNGP15.phx.gbl...
> DELETE FROM YourTable
> WHERE YourColumn IS NULL
> ... and quit using EM to edit your data... it's not designed for that and
> will definitely cause you problems in the long run (like this one)
>
> "johnfli" <john@.here.com> wrote in message
> news:OuGGCt7qEHA.3712@.TK2MSFTNGP15.phx.gbl...
> > I imported an excel spreadsheet and it loaded a ton of records with NULL
> > values. When I try to delete them, it tells me To many rows will be
> > affected.
> > what is a SQL statement that I can type that will delete all the records
> > that has NULL in a particular field?
> >
> >
> >
>|||"johnfli" <john@.here.com> wrote in message
news:OgX4Ly7qEHA.3868@.TK2MSFTNGP15.phx.gbl...
> what would you propose instead?
> Thank you for your assistance.
Script your data modifications and run them from QA.|||I don't use sql that much, so I have no idea on how to write scripts to
import data from other files.
if you could assist me with that, that would be great.
I have a workbook with several sheets.
All sheets are the same format.
what would the script be? I have the table in sql all setup (there are
only 10 fields)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eVf3az7qEHA.556@.tk2msftngp13.phx.gbl...
> "johnfli" <john@.here.com> wrote in message
> news:OgX4Ly7qEHA.3868@.TK2MSFTNGP15.phx.gbl...
> > what would you propose instead?
> >
> > Thank you for your assistance.
> Script your data modifications and run them from QA.
>|||Try this article:
http://support.microsoft.com/?id=321686
"johnfli" <john@.here.com> wrote in message
news:uFZ04B8qEHA.348@.TK2MSFTNGP15.phx.gbl...
> I don't use sql that much, so I have no idea on how to write scripts to
> import data from other files.
> if you could assist me with that, that would be great.
> I have a workbook with several sheets.
> All sheets are the same format.
> what would the script be? I have the table in sql all setup (there are
> only 10 fields)
>

NULL Values

I imported an excel spreadsheet and it loaded a ton of records with NULL
values. When I try to delete them, it tells me To many rows will be
affected.
what is a SQL statement that I can type that will delete all the records
that has NULL in a particular field?
DELETE FROM YourTable
WHERE YourColumn IS NULL
... and quit using EM to edit your data... it's not designed for that and
will definitely cause you problems in the long run (like this one)
"johnfli" <john@.here.com> wrote in message
news:OuGGCt7qEHA.3712@.TK2MSFTNGP15.phx.gbl...
> I imported an excel spreadsheet and it loaded a ton of records with NULL
> values. When I try to delete them, it tells me To many rows will be
> affected.
> what is a SQL statement that I can type that will delete all the records
> that has NULL in a particular field?
>
>
|||what would you propose instead?
Thank you for your assistance.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OR333v7qEHA.3700@.TK2MSFTNGP15.phx.gbl...
> DELETE FROM YourTable
> WHERE YourColumn IS NULL
> ... and quit using EM to edit your data... it's not designed for that and
> will definitely cause you problems in the long run (like this one)
>
> "johnfli" <john@.here.com> wrote in message
> news:OuGGCt7qEHA.3712@.TK2MSFTNGP15.phx.gbl...
>
|||"johnfli" <john@.here.com> wrote in message
news:OgX4Ly7qEHA.3868@.TK2MSFTNGP15.phx.gbl...
> what would you propose instead?
> Thank you for your assistance.
Script your data modifications and run them from QA.
|||I don't use sql that much, so I have no idea on how to write scripts to
import data from other files.
if you could assist me with that, that would be great.
I have a workbook with several sheets.
All sheets are the same format.
what would the script be? I have the table in sql all setup (there are
only 10 fields)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eVf3az7qEHA.556@.tk2msftngp13.phx.gbl...
> "johnfli" <john@.here.com> wrote in message
> news:OgX4Ly7qEHA.3868@.TK2MSFTNGP15.phx.gbl...
> Script your data modifications and run them from QA.
>
|||Try this article:
http://support.microsoft.com/?id=321686
"johnfli" <john@.here.com> wrote in message
news:uFZ04B8qEHA.348@.TK2MSFTNGP15.phx.gbl...
> I don't use sql that much, so I have no idea on how to write scripts to
> import data from other files.
> if you could assist me with that, that would be great.
> I have a workbook with several sheets.
> All sheets are the same format.
> what would the script be? I have the table in sql all setup (there are
> only 10 fields)
>