Showing posts with label nvarchar. Show all posts
Showing posts with label nvarchar. Show all posts

Friday, March 30, 2012

problem in instead of Trigger

hi,
i am using trigger as below:
alter TRIGGER Docsdelete
ON Docs
INSTEAD OF DELETE
AS
Declare @.DeletedDocLibRowId int
declare @.Dirname nvarchar(256)
declare @.LeafName nvarchar(128)
set @.Dirname = (SELECT dirname FROM DELETED)
set @.LeafName = (SELECT leafname FROM DELETED)
set @.DeletedDocLibRowId = (SELECT doclibrowid FROM DELETED)
if(@.DeletedDocLibRowId IS NULL)
BEGIN
DELETE docs from deleted where docs.dirname = @.Dirname and
docs.leafname = @.LeafName
END
my problem is that i want to delete row in instead of trigger method,
but when i use this method, i cannot delete my row , the row seems to
be still in table even when i apply my trigger.
please anyone help me in this regard.
sathya naryanan v
narayanan@.gsdindia.comThat a often misunderstanding of triggers.
Trigers are fired per DML Operation rather than per row, so the statement
> DELETE docs from deleted where docs.dirname = @.Dirname and
> docs.leafname = @.LeafName
wont work because if you delete more than one row it will Rollback end with
an error.

> alter TRIGGER Docsdelete
> ON Docs
> INSTEAD OF DELETE
> AS
> Declare @.DeletedDocLibRowId int
> declare @.Dirname nvarchar(256)
> declare @.LeafName nvarchar(128)
> set @.Dirname = (SELECT dirname FROM DELETED)
> set @.LeafName = (SELECT leafname FROM DELETED)
> set @.DeletedDocLibRowId = (SELECT doclibrowid FROM DELETED)

> if (Select COUNT(*) from deleted where DeletedDocLibRowId IS NOT NULL) > 0
> BEGIN
Delete From sometable s--That table you want to delete from
inner join deleted d
on
s.dirname = d.dirname AND
d.leafname = d.leafname
> END
HTH, Jens Suessmeyer.
"sathya" <sathyanarayananit@.gmail.com> wrote in message
news:1122111997.898569.102710@.g43g2000cwa.googlegroups.com...
> hi,
> i am using trigger as below:
> alter TRIGGER Docsdelete
> ON Docs
> INSTEAD OF DELETE
> AS
> Declare @.DeletedDocLibRowId int
> declare @.Dirname nvarchar(256)
> declare @.LeafName nvarchar(128)
> set @.Dirname = (SELECT dirname FROM DELETED)
> set @.LeafName = (SELECT leafname FROM DELETED)
> set @.DeletedDocLibRowId = (SELECT doclibrowid FROM DELETED)
> if(@.DeletedDocLibRowId IS NULL)
> BEGIN
> DELETE docs from deleted where docs.dirname = @.Dirname and
> docs.leafname = @.LeafName
> END
> my problem is that i want to delete row in instead of trigger method,
> but when i use this method, i cannot delete my row , the row seems to
> be still in table even when i apply my trigger.
> please anyone help me in this regard.
>
> sathya naryanan v
> narayanan@.gsdindia.com
>|||On 23 Jul 2005 02:46:37 -0700, sathya wrote:

>hi,
>i am using trigger as below:
>alter TRIGGER Docsdelete
>ON Docs
>INSTEAD OF DELETE
>AS
>Declare @.DeletedDocLibRowId int
>declare @.Dirname nvarchar(256)
>declare @.LeafName nvarchar(128)
>set @.Dirname = (SELECT dirname FROM DELETED)
>set @.LeafName = (SELECT leafname FROM DELETED)
>set @.DeletedDocLibRowId = (SELECT doclibrowid FROM DELETED)
>if(@.DeletedDocLibRowId IS NULL)
>BEGIN
>DELETE docs from deleted where docs.dirname = @.Dirname and
>docs.leafname = @.LeafName
>END
>my problem is that i want to delete row in instead of trigger method,
>but when i use this method, i cannot delete my row , the row seems to
>be still in table even when i apply my trigger.
>please anyone help me in this regard.
Hi sathya,
Jens is correct that this trigger will only work for single-row deletes.
However, if you tested it with single-row deletes, I see no reason why
it would not work. Visual review of the code leads me to believe that
the row will actually be deleted if doclibrowid in the row is NULL, and
it will not be deleted if doclibrowid is anything but NULL.
That being said, it is unneeded to use the "from deleted" in the final
DELETE statement. Remove it, to save SQL Server some extra work.
And when you're busy changing things, why not go ahead and make it
multi-row proof as well. I believe that Jens missed the requirement for
doclibrowid to be NULL, so I'll show you another version. It differs
from Jens' version in two regards: it tests for doclibrowid to be NULL,
and it uses the more standard ANSI-standard DELETE FROM syntax instead
of the proprietary Transact-SQL DELETE FROM FROM syntax.
ALTER TRIGGER Docsdelete
ON docs
INSTEAD OF DELETE
AS
DELETE FROM docs
WHERE EXISTS (SELECT *
FROM deleted
WHERE deleted.dirname = docs.dirname
AND deleted.leafname = docs.leafname
AND deleted.doclibrowid IS NULL)
(untested)
If this still doesn't work, then consider:
a) using an AFTER trigger to rollback unwanted changes instead of using
an INSTEAD OF trigger to execute only the wanted changes - in my
experience, AFTER triggers often prove easier to understand and to
handle.
or
b) post a script that includes CREATE TABLE statements and INSERT
statements so that we can reproduce what you are experiencing. Add the
expected output to your script. See www.aspfaq.com/5006 for more
details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, March 23, 2012

Problem in a QUERY with COUNT

CREATE PROCEDURE [dbo].[GD_SP_HARDWARE_MONITOR_COUNT]

-- Add the parameters for the stored procedure here

@.Direccao nvarchar(10)

AS

DECLARE @.NrMon int

BEGIN

SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS Total

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID

WHERE (dbo.HARDWARE.MONITOR_ID <> 5)

GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario

END

DEAR FRIENDS,

HOW CAN I MULTIPLICATE THE VALUE FROM UNIDADES AND TOTAL?

Current output:

Unidades

/Total

TFT 17

417

/24,35

TFT 15

3254

/22,08

GOAL:

Unidades

/Total

TFT 17

417

/24,35

/10153,95

TFT 15

3254

/22,08

/71848,32

THANKS

Wrap your query in an outer query:

SELECT Item, Unidades, Total, Unidades*Total AS SomethingNew
FROM
(
Here comes your existing query
) SubQUery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

THANKS!!!!!!

FANTASTIC!!!!

|||

Another question:
I made a UNION with 2 querys :

ALTER PROCEDURE [dbo].[GD_SP_FACTURA]

-- Add the parameters for the stored procedure here

@.Direccao nvarchar(10)

AS

BEGIN

SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total

FROM

(

SELECT dbo.ModeloPC_Tipo.MOD_Nome AS Item, COUNT(*) AS Unidades, dbo.ModeloPC_Tipo.MOD_CustoUnit AS CustoUnitario

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.ModeloPC ON dbo.HARDWARE.MODELO_ID = dbo.ModeloPC.MODELO_ID INNER JOIN

dbo.ModeloPC_Tipo ON dbo.ModeloPC.MOD_Tipo = dbo.ModeloPC_Tipo.MOD_ID

WHERE (dbo.HARDWARE.MONITOR_ID <> 5)

GROUP BY dbo.SERVICO.S_NomeDir, dbo.ModeloPC_Tipo.MOD_CustoUnit, dbo.ModeloPC_Tipo.MOD_Nome

HAVING (dbo.SERVICO.S_NomeDir = @.Direccao)

) SubQUery

UNION

SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total

FROM

(

SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS CustoUnitario

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID

WHERE (dbo.HARDWARE.MONITOR_ID =1 OR dbo.HARDWARE.MONITOR_ID=2) AND dbo.SERVICO.S_NomeDir=@.Direccao

GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario

) SubQUery

END

OUTPUT:
Desktops 166 433,09 71892,94
Portáteis 3 675,84 2027,52
TFT 15 166 22,08 3665,28
TFT 17 3 24,35 73,05

How can I SUM the last Column of the 2 queries? How can I SUM (Total)?

Thanks!!!

|||

--1.SUM the last two columns

SELECT t.Item, t.Unidades, (t.CustoUnitario+t.Total) AS LAST2Sum FROM (Your UNION result) t

--2.SUM your TOTAL

SELECT SUM(t.Total) AS SumTotal FROM (Your UNION result) t

Wednesday, March 21, 2012

problem getting "LIKE @parameter%" to work

Hello,

I need a text box that the user puts in part of a name and hits find and it returns the values that contain the words. so i want the nvarchar value to go into the standard SQL statement below.

SELECT *
FROM table
WHERE column_name LIKE 'nvarchar%'

It works fine in when i type it in manually.

But im using a stored procedure from VS and it will not work with the '%' part

SELECT *
FROM table
WHERE column_name LIKE @.parameter%

Any help or ideas would be greatly appreciated.Hi, my similar line looks like ...


Dim myCommand = New SqlCommand("exec search_telephone '%" & filterValue1 & "%'", myConnection)

... where search_telephone is a stored procedure expecting an input of part of a surname.

NOTE the 2 percentage characters.

Richard|||Like uses a string as its input. So you'd need to use '%' + @.param + '%'|||Is your SQL running inside a stored proc? That's what it sounds like to me.

If that's the case, do something like this:


declare @.strSQL varchar(8000)
select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''' + @.parameter + ''''

EXEC ( @.strSQL )

I do this all the time in my stored procs for searches. I haven't found another way to do this. The trick is getting the number of single quotes right.|||It should be:


declare @.strSQL varchar(8000)

select @.strSQL = 'SELECT * FROM table WHERE column_name LIKE ''%' + @.parameter + '%'''

EXEC ( @.strSQL )

but you get the point.

For help debugging these types of "dynamically generated" SQL statements, use PRINT ( @.strSQL ) and run it in query analyzer.|||in your stored procedure this should work and will not require SELECT permissions on the table like an EXEC(@.sql) would.

CREATE PROC [some_search]
@.Search nvarchar(50)
AS

Declare @.LikeSearch nvarchar(52)

-- you could also add do '%' + @.Search + '%' depending on how you want the search to work
SET @.LikeSearch = @.Search + '%'

SELECT *
FROM table
WHERE column_name LIKE @.LikeSearch

sql

Monday, February 20, 2012

problem converting nvarchar column to datetime

ive a column that needs changing to datetime from nvarchar but im getting
this error message
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
ive checked the data and it seems fine to me - any ideas ?
thanks
mark
It definitely seems like you have invalid data in there. How does the strings look like? What format? Also,
you might want to check out: http://www.karaszi.com/sqlserver/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>
|||Hi Mark
The data may seem fine when you look at it, but SQL Server might disagree.
You can run have SQL Server inspect the data with the ISDATE( ) function to
see what values it is not happy with.
SELECT <nvarchar column>
FROM <mytable>
WHERE ISDATE(<nvarchar column>) = 0
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> It definitely seems like you have invalid data in there. How does the
strings look like? What format? Also,
> you might want to check out:
http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
the strings look like
01/01/1900
01/17/2003
etc (ie they are basically dates) - ive checked the data and its fine
|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> Hi Mark
> The data may seem fine when you look at it, but SQL Server might disagree.
> You can run have SQL Server inspect the data with the ISDATE( ) function
to
> see what values it is not happy with.
> SELECT <nvarchar column>
> FROM <mytable>
> WHERE ISDATE(<nvarchar column>) = 0
i tried that and it returned 0 results
mark
|||How far in do you get before this error is thrown? Have you check ALL the
rows for the data type. If one row is wrong then the whole process is rolled
back. What are you using to do the conversion?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>
|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> How far in do you get before this error is thrown? Have you check ALL the
> rows for the data type. If one row is wrong then the whole process is
rolled
> back. What are you using to do the conversion?
>
im not sure how far its getting, im basically using the enterprise manager
and design table and making the changes there, and its throwing that error!
|||How many rows do you have?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:rhpsc.61$Vp5.49@.newsfe2-win...[vbcol=seagreen]
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
the
> rolled
> im not sure how far its getting, im basically using the enterprise manager
> and design table and making the changes there, and its throwing that
error!
>
|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> How many rows do you have?
>
theres 1144 rows in total
|||Can you post a zipped CSV of the data in the column?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:JRpsc.293$ge6.16@.newsfe6-gui.server.ntli.net...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> theres 1144 rows in total
>

problem converting nvarchar column to datetime

ive a column that needs changing to datetime from nvarchar but im getting
this error message
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arith
metic
overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has
been
terminated.
ive checked the data and it seems fine to me - any ideas ?
thanks
markIt definitely seems like you have invalid data in there. How does the string
s look like? What format? Also,
you might want to check out: rl]
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/sqlserver/in...ver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:khnsc.33$Vp5.26@.newsfe2-win...">
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Ari
thmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||Hi Mark
The data may seem fine when you look at it, but SQL Server might disagree.
You can run have SQL Server inspect the data with the ISDATE( ) function to
see what values it is not happy with.
SELECT <nvarchar column>
FROM <mytable>
WHERE ISDATE(<nvarchar column> ) = 0
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Ari
thmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> It definitely seems like you have invalid data in there. How does the
strings look like? What format? Also,
> you might want to check out:
http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
the strings look like
01/01/1900
01/17/2003
etc (ie they are basically dates) - ive checked the data and its fine|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> Hi Mark
> The data may seem fine when you look at it, but SQL Server might disagree.
> You can run have SQL Server inspect the data with the ISDATE( ) function
to
> see what values it is not happy with.
> SELECT <nvarchar column>
> FROM <mytable>
> WHERE ISDATE(<nvarchar column> ) = 0
i tried that and it returned 0 results
mark|||How far in do you get before this error is thrown? Have you check ALL the
rows for the data type. If one row is wrong then the whole process is rolled
back. What are you using to do the conversion?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Ari
thmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement h
as been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> How far in do you get before this error is thrown? Have you check ALL the
> rows for the data type. If one row is wrong then the whole process is
rolled
> back. What are you using to do the conversion?
>
im not sure how far its getting, im basically using the enterprise manager
and design table and making the changes there, and its throwing that error!|||How many rows do you have?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:rhpsc.61$Vp5.49@.newsfe2-win...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
> rolled
> im not sure how far its getting, im basically using the enterprise manager
> and design table and making the changes there, and its throwing that
error!
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> How many rows do you have?
>
theres 1144 rows in total|||Can you post a zipped CSV of the data in the column?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:JRpsc.293$ge6.16@.newsfe6-gui.server.ntli.net...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> theres 1144 rows in total
>

problem converting nvarchar column to datetime

ive a column that needs changing to datetime from nvarchar but im getting
this error message
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
overflow error converting expression to data type datetime.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.
ive checked the data and it seems fine to me - any ideas ?
thanks
markIt definitely seems like you have invalid data in there. How does the strings look like? What format? Also,
you might want to check out: http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mark" <mark@.remove.com> wrote in message news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||Hi Mark
The data may seem fine when you look at it, but SQL Server might disagree.
You can run have SQL Server inspect the data with the ISDATE( ) function to
see what values it is not happy with.
SELECT <nvarchar column>
FROM <mytable>
WHERE ISDATE(<nvarchar column>) = 0
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> It definitely seems like you have invalid data in there. How does the
strings look like? What format? Also,
> you might want to check out:
http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
the strings look like
01/01/1900
01/17/2003
etc (ie they are basically dates) - ive checked the data and its fine|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> Hi Mark
> The data may seem fine when you look at it, but SQL Server might disagree.
> You can run have SQL Server inspect the data with the ISDATE( ) function
to
> see what values it is not happy with.
> SELECT <nvarchar column>
> FROM <mytable>
> WHERE ISDATE(<nvarchar column>) = 0
i tried that and it returned 0 results
mark|||How far in do you get before this error is thrown? Have you check ALL the
rows for the data type. If one row is wrong then the whole process is rolled
back. What are you using to do the conversion?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:khnsc.33$Vp5.26@.newsfe2-win...
> ive a column that needs changing to datetime from nvarchar but im getting
> this error message
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic
> overflow error converting expression to data type datetime.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ive checked the data and it seems fine to me - any ideas ?
> thanks
> mark
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> How far in do you get before this error is thrown? Have you check ALL the
> rows for the data type. If one row is wrong then the whole process is
rolled
> back. What are you using to do the conversion?
>
im not sure how far its getting, im basically using the enterprise manager
and design table and making the changes there, and its throwing that error!|||How many rows do you have?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:rhpsc.61$Vp5.49@.newsfe2-win...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:Ojd7qEaQEHA.3944@.tk2msftngp13.phx.gbl...
> > How far in do you get before this error is thrown? Have you check ALL
the
> > rows for the data type. If one row is wrong then the whole process is
> rolled
> > back. What are you using to do the conversion?
> >
> im not sure how far its getting, im basically using the enterprise manager
> and design table and making the changes there, and its throwing that
error!
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> How many rows do you have?
>
theres 1144 rows in total|||Can you post a zipped CSV of the data in the column?
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"mark" <mark@.remove.com> wrote in message
news:JRpsc.293$ge6.16@.newsfe6-gui.server.ntli.net...
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%233a5$RaQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> > How many rows do you have?
> >
> theres 1144 rows in total
>|||select rowid, convert(datetime, column1) from table1 order by rowid
something similar to that should get you the row or rows that have bad data.
mark wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > It definitely seems like you have invalid data in there. How does the
> strings look like? What format? Also,
> > you might want to check out:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> the strings look like
> 01/01/1900
> 01/17/2003
> etc (ie they are basically dates) - ive checked the data and its fine|||mark wrote:
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > It definitely seems like you have invalid data in there. How does the
> strings look like? What format? Also,
> > you might want to check out:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> the strings look like
> 01/01/1900
> 01/17/2003
> etc (ie they are basically dates) - ive checked the data and its fine
select rowid, convert(datetime, column1) from table1 order by rowid
if you have bad data you'll get an error with that select and then you can
easily track down the row with the bad data. "basically dates" is not good
enough for a data type conversion. sorry if i double posted this.|||This only works if you have a column called "ROWID" I do not believe SQL
server has a rowid column inherent to a table there is a ROWGUIDCOL type but
only if you build your table with it originally.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"ch" <ch@.dontemailme.com> wrote in message
news:40B251FC.A1AC90BF@.dontemailme.com...
> mark wrote:
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > It definitely seems like you have invalid data in there. How does the
> > strings look like? What format? Also,
> > > you might want to check out:
> > http://www.karaszi.com/sqlserver/info_datetime.asp
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> >
> > the strings look like
> > 01/01/1900
> > 01/17/2003
> > etc (ie they are basically dates) - ive checked the data and its fine
> select rowid, convert(datetime, column1) from table1 order by rowid
> if you have bad data you'll get an error with that select and then you can
> easily track down the row with the bad data. "basically dates" is not
good
> enough for a data type conversion. sorry if i double posted this.
>
>|||If Query Analyzer says your data is good, but you're having problems in
Enterprise Manager, try using Query Analyzer to change the datatype using
ALTER TABLE. EM might be doing something else behind the scene.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"mark" <mark@.remove.com> wrote in message
news:BXosc.56$Vp5.20@.newsfe2-win...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eKKCnEZQEHA.640@.TK2MSFTNGP09.phx.gbl...
> > Hi Mark
> >
> > The data may seem fine when you look at it, but SQL Server might
disagree.
> > You can run have SQL Server inspect the data with the ISDATE( ) function
> to
> > see what values it is not happy with.
> >
> > SELECT <nvarchar column>
> > FROM <mytable>
> > WHERE ISDATE(<nvarchar column>) = 0
> i tried that and it returned 0 results
> mark
>
>|||"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:O$5HGtaQEHA.2100@.TK2MSFTNGP11.phx.gbl...
> Can you post a zipped CSV of the data in the column?
>
hi thanks for the help, in the end i exported the data to access (no
problems) converted the field types in access
dropped the data from the sql table - and changed the field types and
re-imported back in - without any hangups
or errors - odd but still its done now!
mark|||select rowid, convert(datetime, column1) from table1 order by rowid
it also only works if you have a table named table1 and that table contains at
least two columns, one column named rowid and another column named column1.
Andrew Madsen wrote:
> This only works if you have a column called "ROWID" I do not believe SQL
> server has a rowid column inherent to a table there is a ROWGUIDCOL type but
> only if you build your table with it originally.
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B251FC.A1AC90BF@.dontemailme.com...
> > mark wrote:
> >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > > message news:eM%23FfDZQEHA.1160@.TK2MSFTNGP09.phx.gbl...
> > > > It definitely seems like you have invalid data in there. How does the
> > > strings look like? What format? Also,
> > > > you might want to check out:
> > > http://www.karaszi.com/sqlserver/info_datetime.asp
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > >
> > > the strings look like
> > > 01/01/1900
> > > 01/17/2003
> > > etc (ie they are basically dates) - ive checked the data and its fine
> >
> > select rowid, convert(datetime, column1) from table1 order by rowid
> > if you have bad data you'll get an error with that select and then you can
> > easily track down the row with the bad data. "basically dates" is not
> good
> > enough for a data type conversion. sorry if i double posted this.
> >
> >
> >|||h
I been in same position once, my date format in string was dd/MM/yyyy
to change the date format you must use convert(datetime,'mm/dd/yyyy') if you change the column format from nvarchar to datetime it will not work. what you should to is create another column with datetime format while your current column is in use. then run a procedure to pick date from the nvarchar date column, use convert and save it into column with datetime format.
Test your data
If everything is fine, backup your table and delete column with nvarchar format
Test your application or modules using that table

problem converting float into nvarchar

I have a problem converting a float filed into nvarchar.
select cast(sold as varchar(50))
where sold=431597.15 results in 431597 and is ignoring always my decimals.
Do you have any idea how to fix this?
tyHi

The below works for me:
DECLARE @.Sold AS Float
SELECT @.Sold = 431597.15
SELECT CAST(@.Sold AS VarChar(50)) --Wrong
SELECT CAST(CAST(@.Sold AS Decimal(8, 2)) AS VarChar(50))--Correct

HTH