Error = Arithmetic Overflow Error Converting Expression To Data Type Smalldatetim

Mar 22, 2007

  $exception {"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}

occurs

here is my code

protected void EmailSubmitBtn_Click(object sender, EventArgs e)

{

SqlDataSource NewsletterSqlDataSource = new SqlDataSource();

NewsletterSqlDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();

 

//Text version

NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text;

NewsletterSqlDataSource.InsertCommand = "INSERT INTO NewsLetter (EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";

 

//storeprocedure version

//NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;

//NewsletterSqlDataSource.InsertCommand = "EmailInsert";

NewsletterSqlDataSource.InsertParameters.Add("EmailAddress", EmailTb.Text);

NewsletterSqlDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());

NewsletterSqlDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());

int rowsAffected = 0;

try

{

rowsAffected = NewsletterSqlDataSource.Insert();

}

catch (Exception ex)

{

Server.Transfer("NewsletterProblem.aspx");

}

finally

{

NewsletterSqlDataSource = null;

}

if (rowsAffected != 1)

{

Server.Transfer("NewsletterProblem.aspx");

}

else

{

Server.Transfer("NewsletterSuccess.aspx");

}


 

View 3 Replies


ADVERTISEMENT

Arithmetic Overflow Error Converting Expression To Data Type Int.

Aug 28, 2002

l'm running this procedure and l get this error. All l'm trying to do is to get the size of the database and its objects and what the size should be so that its sized right. Is there a better way of doing this ?

CREATE PROCEDURE sp_totalsize as

SELECT o.name 'Table', SUM(c.length) 'Record size',
MAX (i.rows) '#of rows',
CONVERT (decimal (10, 4), SUM (c.length * i.rows)/(1024.00 * 1024.00)) 'Approx. size (MB)'

FROM sysobjects o, syscolumns c, sysindexes i
WHERE o.id = c.id
AND o.id = i.id
AND (i.indid = 1 or i.indid = 0)
AND o.type = 'U'
GROUP BY o.name
COMPUTE SUM (CONVERT (decimal (10,4), SUM (c.length * i.rows)/(1024.00 * 1024.00)))


GO




(17 row(s) affected)

Server: Msg 8115, Level 16, State 2, Procedure sp_totalsize, Line 3
Arithmetic overflow error converting expression to data type int.

View 1 Replies View Related

Arithmetic Overflow Error Converting Expression To Data Type Int

Sep 27, 2004

Under certain circumstances I am getting the following error

"Arithmetic overflow error converting expression to data type int"

when running the following code:

SELECT Count(*), Sum(GrossWinAmount)
FROM LGSLog
WHERE
(CurrentDate >= '9/1/2004 8:00:00 AM') And (CurrentDate <= '9/27/2004 7:59:59 AM')

If I remove the "Sum(GrossWinAmount)" from the select, it works fine. I therefore believe that Sum is causing the error. Is there a version of Sum that works with larger variables, such as a BigInt? If not, is there some way to do the equivalent using larger numbers? I need to allow for the possibility of obtaining one month's summary, and sometimes the summary value is apparently too large for Sum to handle.

View 10 Replies View Related

Arithmetic Overflow Error Converting Expression To Data Type Int.

Sep 8, 2006

I've got this error message while generate the output with ASP:

"Microsoft OLE DB Provider for SQL Server (0x80004005)
Arithmetic overflow error converting expression to data type int."

it indicate that the error is related to this line:
"rc1.Movenext"

where rc1 is set as objconn.Execute(sql).

Not all outputs result like this, it happens when it has many relationships with other records, especially with those records which also have many relationships with other records.

Can anyone suggest a solution?
I've tried to increase the size of the database file, but it doesn't work.

View 4 Replies View Related

Arithmetic Overflow Error Converting Expression To Data Type Datetime

Sep 20, 2006

Hi all,In the beginning of this month I've build a website with a file-upload-control. When uploading a file, a record (filename, comment, datetime) gets written to a SQLExpress database, and in a gridview a list of the files is shown. On the 7th of September I uploaded some files to my website, and it worked fine. In the database, the datetime-record shows "07/09/2006 11:45". When I try to upload a file today, it gives me the following error: Error: Arithmetic overflow error converting expression to data type datetime. The statement has been terminated.While searching in google, i found it might have something to do with the language settings of my SQLExpress, I've tried changing this, but it didn't help. What I find weird is that it worked fine, and now it doesn't anymore. Here is my code of how I get the current date to put it into the database:1 SqlDataSource2.InsertParameters.Add("DateInput", DateTime.Now.ToString());
Am I doing something wrong, or am I searching for a solution in the wrong place? best regards, Dimitri

View 3 Replies View Related

Arithmetic Overflow Error Converting Expression To Data Type Datetime

Jan 7, 2008

Hi,
I'm having this error with my page, user picks the date -using the AJAX Control Toolkit Calender with the format of ( dd/MM/yyyy ).
It looks like the application current format is MM/dd/yyyy, because it shows the error page if the day is greater than 12,  like: 25/03/2007
What is wrong?
Here is the error page:
Server Error in '/' Application.


Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.
Any help will be appreciated!

View 3 Replies View Related

T-SQL (SS2K8) :: Arithmetic Overflow Error Converting Expression To Data Type Int

Mar 25, 2015

In my sql statement, I don't have any datatype as INT, when I run it, give me error as 'Arithmetic overflow error converting expression to data type int'.

example :
select column1, 2, 3 .....
from (select sum(float) as column1 , ....)

When I hop my cursor on top of column1, it shows (int,null)

View 4 Replies View Related

Arithmetic Overflow Error Converting Expression To Data Type Datetime

Jun 12, 2014

The following codes give me the error "arithmetic overflow error converting expression to data type datetime" Unfortunately, the datatype of date of this database is still varchar(50)

select date from tbltransaction
where datepart(wk,convert(datetime,date,103)) = 15

View 3 Replies View Related

Arithmetic Overflow Error Converting Expression To Data Type Bigint

Jan 9, 2007

I am attempting to setup a replication from SQL Server 2005 that will be read by SQL Server Compact Edition (beta). I'm having trouble getting the Publication Wizard to create the Publication. Sample table definition that I'm replicating:

USE dbPSMAssist_Development;
CREATE TABLE corporations (
id NUMERIC(19,0) IDENTITY(1964,1) NOT NULL PRIMARY KEY,
idWas NUMERIC(19,0) DEFAULT 0,
logIsActive BIT DEFAULT 1,
vchNmCorp VARCHAR(75) NOT NULL,

vchStrtAddr1 VARCHAR(60) NOT NULL,
vchNmCity VARCHAR(50) NOT NULL,
vchNmState VARCHAR(2) NOT NULL,
vchPostalCode VARCHAR(10) NOT NULL,
vchPhnPrimary VARCHAR(16) NOT NULL,
);
CREATE INDEX ix_corporations_nm ON corporations(vchNmCorp, id);
GO


When the wizard gets to the step where it is creating the publication, I get the following error message:


Arithmetic overflow error converting expression to data type bigint. Changed database context to 'dbPSMAssist_Development'. (Microsoft SQL Server, Error: 8115).

I can find no information on what this error is or why I am receiving the error. Any ideas on how to fix would be appreciated.

Thanks in advance ...

David L. Collison

Any day above ground is a good day.

View 3 Replies View Related

Transact SQL :: Arithmetic Overflow Error Converting Expression To Data Type Int

Oct 16, 2013

this query is running fine in 2008 , but its not working in 2005 below is the error Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.there is some problem in converting date in cte

with a 
as 
(
SELECT dbname = DB_NAME(database_id) ,
       [DBSize] = CAST( ((SUM(ms.size)* 8) / 1024.0) AS DECIMAL(18,2) ) 
       ,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'01/01/1900') AS LastBackUpTime
FROM   sys.master_files ms
inner join msdb.dbo.backupset bus ON bus.database_name = DB_NAME()

[code]....

View 9 Replies View Related

Arithmetic Overflow Error Converting Expression To Data Type Int When Using Fill From A SqlDataAdapter

Mar 7, 2007

I thought I'd post this quick problem and answer, as I couldn't find the answer when searching for it. 
I tried to call a stored procedure on SQL Server 2000 using the System.Data.SqlClient objects, and was not expecting any unusual issues.  However when I came to call the Fill method I received the error "Arithmetic overflow error converting expression to data type int."
My first checks were the obvious ones of verifying that I'd provided all the correct datatypes and had no unexpected null values, but I found nothing out of order.  The problem turns out to be a difference on the maximum values for integers between C# and SQL Server 2000.  Previously having hit issues with SQL Server integers requiring Long Integer types in VB6, I was aware that these are 32-bit integers, so I was passing in Int32 variables.  The problem was that Int32.MaxValue is not a valid integer for SQL Server.  Seeing as I was providing an abitrary upper value for records-per-page (to fetch all in one page), I was simply able to change this to Int16.MaxValue and will hit no further problems as this is also well beyond any expected range for this parameter.
 If anyone can name off the top of their heads what value should be provided as a maximum integer for SQL Server 2000, this might be a useful addition, but otherwise I hope this spares others some hunting if they also experience this problem.
James Burton

View 1 Replies View Related

Data Access :: Arithmetic Overflow Error Converting Expression To Data Type Int

Jul 24, 2015

When I execute the below stored procedure I get the error that "Arithmetic overflow error converting expression to data type int".

USE [FileSharing]
GO
/****** Object: StoredProcedure [dbo].[xlaAFSsp_reports] Script Date: 24.07.2015 17:04:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[Code] .....

Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
(1 row(s) affected)

View 10 Replies View Related

Arithmetic Overflow Error Converting Numeric To Data Type

Sep 4, 2012

This is the error in my query:

[Err] 22003 - [SQL Server] Arithmetic overflow error converting numeric to data type numeric.

If tried [%TAX] DECIMAL (10, 2) I have the correct output, but I need 4 decimal in my output.

Code:
MATNUMB-VER-EXENUMB-VER-RIC%TAX
a476427228.1515,17%
b9241314323.1515,50%
c7781510878.1413,98%
d7480019601.2626,20%
e877618297.099,45%

[Code] .....

View 2 Replies View Related

SQL Server 2008 :: Arithmetic Overflow Error Converting Nvarchar To Data Type Numeric

Oct 13, 2012

When I run the following sql query:

"update table set price = price * 1.1 "..

I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."

The table is set to nvarchar, and i am just trying to make the prices go up 10%.

View 9 Replies View Related

SQL Server 2012 :: Arithmetic Overflow Error Converting Varchar To Data Type Numeric

Oct 30, 2014

OK, so I have this query where I'm trying to subtract values like this, when I do this I am getting (Arithmetic overflow error converting varchar to data type numeric.) I have tried many different things, and now of these work, it'll either return 0 because it loses the .XXXXX.

Convert(DECIMAL(10,7),CAST([TIME_OF_COMPLETION] as DECIMAL(10,7)) - Convert(DECIMAL(10,7),CAST([OPR_DELIVERED_TIME] as DECIMAL(10,7)) round(cast(cast(hist.[TIME_OF_COMPLETION] AS float) as DECIMAL(15, 5)) - CAST(hist.[OPR_DELIVERED_TIME] AS FLOAT),1 SELECT convert(FLOAT,CAST('735509.00053' AS DECIMAL(10,5))) - convert(FLOAT,CAST('735509.00047' AS DECIMAL(10,5)))

View 1 Replies View Related

Arithmetic Overflow Error Converting Expression To

Mar 28, 2008

insert into----
select ID_NO,cast(row_number() over(partition by ID_NO order by ID_NO)as varchar(2))
from
test_222

I am trying to insert into test222 table .The id_no column is varchar field
error:
Arithmetic overflow error converting expression to data type varchar.
The statement has been terminated.

View 7 Replies View Related

T-SQL (SS2K8) :: Arithmetic Overflow Error Converting Varchar To Data Type Numeric Message When Field Is Decimal

Apr 3, 2014

I am trying to setup an indicator value for an SSRS report to show green and red values on a report, based on the NRESULT value. The problem I am facing is that I have several different CASE statements that have the same logic, and they are processing just fine. NRESULT is a decimal field, so no conversion should be necessary. I do not know why I am getting the "Arithmetic overflow error converting varchar to data type numeric." error message.

Below is the CASE statement where the error is occurring. It is in the part of the ELSE CASE. The first CASE works just fine when the ELSE CASE is commented out. If I also change the ELSE CASE statement to say "else case when LEFT(NRESULT,1) = '-' then '0'", then it processes fine, too, so it has to be something I am missing something in the check on negative values. I do need the two checks, one for positive and one for negative values, to take place.

case when LEFT(NRESULT,1) <> '-' then --This portion, for checking positive values, of the CASE statement works fine.
CASE WHEN LEFT(ROUND(NRESULT,2),4) between 0.00 and 0.49 THEN '2' --Green
ELSE CASE WHEN LEFT(ROUND(NRESULT,2),4) > 0.49 THEN '0' --Red
ELSE '3' --White
END
END
else case when LEFT(NRESULT,1) = '-' then --This portion, for checking negative values, of the CASE statement is producing the conversion error message.

[code]....

I checked the NRESULT field, and there are not any NULL values in there, either.

View 1 Replies View Related

T-SQL (SS2K8) :: Arithmetic Overflow Error Converting Numeric To Data Type Numeric

Jun 10, 2014

when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)

set @a=99362600999900.0000

The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.

View 2 Replies View Related

Arithmetic Overflow Error Converting Numeric To Data Type Numeric

Mar 21, 2006

Guys

I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000

When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)



Any ideas ??

View 6 Replies View Related

Arithmetic Overflow Error Converting Numeric

Jan 13, 2015

The following function is throwing the error:
1) "Arithmetic overflow error converting numeric to data type numeric."

2) The variable @x should only be set when if condition is equal to 1. For any other values, it should not go inside if condition. Does the following if condition code meet that criteria?

ALTER FUNCTION [dbo].[fn_Calculator]
(
@abc bit
)
returns decimal(14,10)
as begin

[code]...

View 3 Replies View Related

Arithmetic Overflow Error For Type Int

Sep 16, 2006

i have to calculate
power(2,32) in SQL Server Enterprise Manager.
here 32 is obtained through a series of calculations. no problem there. Error is in calculating power.
I am getting the foll error
Arithmetic overflow error for type int value = 4294927696.00000000
i tried convert(bigint,power(2,32)) but no use since conversion will anyway take place only after power is calculated which again gives the same error.

View 3 Replies View Related

Arithmetic Overflow Error For Type Int, Value=????

Jun 11, 2007

Hi all
Today I'm so incredibly annoyed.
I have a simple Table which has one Identity column plus an integer Column with Unique constraint and some varchar fields.When i change the value of that Unique Constraint Column , the value will change successfully But today i find a record (Just By Chance) that when i change the value of its filed i recieve the following Error: its filed has the value of 2519 and i wanted to update it to 2520 !!!

---------------------------
SQL Server Enterprise Manager
---------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for type int, value = 6262227669.000000.

[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
---------------------------
OK Help
---------------------------

As i said earlier i have no problem with other records , that's strange.

Could anyone help me Please?

Thanks in advance.
Kind Regards.

View 5 Replies View Related

Arithmetic Overflow Error For Type Int, Value = 6262227669.000000

Jun 11, 2007

Hi allToday I'm so incredibly annoyed I have a simple Table which has one Identity column plus an integer Column with Unique constraint and some varchar fields.When i change the value of that Unique Constraint Column , the value will change successfully But today i find a record (Just By Chance) that when i change the value of its filed i recieve the following Error: its filed has the value of 2519 and i wanted to update it to 2520 !!!
---------------------------SQL Server Enterprise Manager---------------------------[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error for type int, value = 6262227669.000000.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. ---------------------------OK   Help   ---------------------------
As i said earlier i have no problem with other records , that's strange.
Could anyone help me Please?
Thanks in advance.Kind Regards.

View 7 Replies View Related

SQL Server 2012 :: Arithmetic Overflow Error For Type Varchar

Mar 11, 2014

I have the following code in a SP. The 2 if statements for the @@ Error I added only for testing as I suddenly started get the following error at the 1st @@Error statement

Msg 232, Level 16, State 2, Line
Arithmetic overflow error for type varchar, value = 10000.00000.

I cannot have this SP running minutes it must be done in seconds.

I have tried the folloiwng changes but then it takes to long.

-- set @new = 'x' + right('0000' + convert(varchar(5), round(rand() * 10000, 0)), 4)
-- set @new = 'x' + right('0000' + convert(varchar(4), right(round(rand() * 10000, 0),4)), 4)

declare @newvarchar(5),
@xstsint,
@new_retvarchar(5)
,@Cnt as int /* just for my testing */
set @cnt = 0 /* just for my testing */

[Code] ......

View 6 Replies View Related

Arithmetic Operation Resulted In An Overflow. (System.Data) - Connection Error With SQL Server 2005

Oct 20, 2007

 We've been using SQL Server 2005 for a while as the db for our web app. Everything has been working fine, until yesterday when we started getting a "Arithmetic operation resulted in an overflow. (System.Data)" error message when trying to connect from SQL Server Management Studio or from our web app.  This only happens when trying to connect remotely, although remote connections have worked for us perfectly in the past. The full error message is reproduced below. Thanks ahead of time for any help.  ===================================Cannot connect to serverName===================================Arithmetic operation resulted in an overflow. (System.Data)------------------------------Program Location:   at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)   at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)   at System.Data.SqlClient.SqlConnection.Open()   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser() 

View 1 Replies View Related

Arithmetic Overflow Error

Feb 16, 2008

hi, can someone please tell me what this error is, i am trying to create a quiz engine but i keep getting this error when i try to save the results of me quiz in the results page. i have been following the tutorial from this website. Please can someone help me, thanks 



Arithmetic overflow error converting expression to data type smalldatetime.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type smalldatetime.The statement has been terminated.Source Error:



Line 46: userQuizDataSource.InsertParameters.Add("UserName", User.Identity.Name)
Line 47:
Line 48: Dim rowsAffected As Integer = userQuizDataSource.Insert()
Line 49: If rowsAffected = 0 Then
Line 50: ' Let's just notify that the insertion didn't

View 8 Replies View Related

Arithmetic Overflow Error

Jul 21, 2004

I got the following error when running a SP:

Server: Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
The statement has been terminated.

The stupid thing is, that there is no data conversion at all. It's an insert into SLQ server table where data is retrieved from an Oracle View (using ADO DB link). I got 4 other SP's, doing the same thing for resp 4 other tables, which works fine. Those :mad: SP won't work. I don't know why. Below I put the table structure, view structure and SP I used:

Table:
Contract_No varchar (20) NOT NULL
Registration_Date_Time datetime NOT NULL
AGC varchar (4) NOT NULL
Salesgroup varchar (4) NOT NULL
Group_ varchar (8) NOT NULL
Activity_Type varchar (4) NULL
Type char (1) NULL
Group_Description varchar (50) NULL
Stock_Um varchar (4) NULL
B_Qty numeric(11, 4) NULL
B_Cost numeric(23, 4) NULL
C_Qty numeric(11, 4) NULL
C_Cost numeric(24, 4) NULL
D_Qty numeric(11, 4) NULL
D_Cost numeric(24, 4) NULL

Oracele view:
CONTRACT_NO VARCHAR2(20)
AGC VARCHAR2(4)
SALESGROUP VARCHAR2(4)
GROUP_ VARCHAR2(8)
ACTIVITY_TYPE VARCHAR2(4)
TYPE CHAR(1)
GROUP_DESCRIPTION VARCHAR2(50)
STOCK_UM VARCHAR2(4)
B_QTY NUMBER
B_COST NUMBER
C_QTY NUMBER
C_COST NUMBER
D_QTY NUMBER
D_COST NUMBER

Stored procedure:
CREATE PROCEDURE mis_Upload_Contract_Kosten
@strType varchar(10),
@strDate varchar(19)
AS
declare @strInsert as varchar(1000);
declare @strSelect as varchar(1000);
declare @strWhere as varchar(1000);
declare @strSql as varchar(3019);

SET @strWhere = ''

SET @strInsert = 'INSERT C_Contract_Kosten (
Contract_No
, AGC
, Salesgroup
, Group_
, Activity_Type
, Type
, Group_Description
, Stock_Um
, B_Qty
, B_Cost
, C_Qty
, C_Cost
, D_Qty
, D_Cost
, Registration_Date_Time)'

SET @strSelect = ' SELECT gLCK.Contract_No
, gLCK.AGC
, gLCK.Salesgroup
, gLCK.Group_
, gLCK.Activity_Type
, gLCK.Type
, gLCK.Group_Description
, gLCK.Stock_Um
, gLCK.B_Qty
, gLCK.B_Cost
, gLCK.C_Qty
, gLCK.C_Cost
, gLCK.D_Qty
, gLCK.D_Cost
, ' + char(39) + @strDate + char(39) + '
FROM Glovia..LIVE.C_CONTRACT_KOSTEN as gLCK
WHERE gLCK.Contract_No NOT LIKE '' IND*''
AND NOT EXISTS
( SELECT vCC.Contract_No
FROM V_Contracts_Closed as vCC
WHERE vCC.Contract_No = gLCK.Contract_No)
AND EXISTS
( SELECT cc.Contract_No
FROM C_Contracten as cc
WHERE cc.Registration_Date_Time = ' + char(39) + @strDate + char(39) + '
AND cc.Contract_No = gLCK.Contract_No)'


IF @strType = 'closed'
BEGIN
SET @strWhere = ' AND NOT(gLCK.Contract_Close_Date IS NULL)'
END

IF @strType = 'open'
BEGIN
SET @strWhere = ' AND gLCK.Contract_Close_Date IS NULL'
END

SET @strSql = @strInsert + @strSelect + @strWhere

EXEC (@strSql)
GO

View 1 Replies View Related

Arithmetic Overflow Error

Jun 13, 2008

I am trying to run this query:

SELECT 'D', property.parcel_number, ROUND(.23 + property_char.value * .02731, 2) AS nwcharge
FROM
property INNER JOIN
property_char ON property.id = property_char.property_id INNER JOIN property_char AS pc ON property.id = pc.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id INNER JOIN
val_component AS vc ON property.id = vc.property_id


but I am getting the error:

"Arithmetic overflow error converting varchar to data type numeric".

It seems to be cause by the line: ROUND(.23 + property_char.value * .02731, 2) AS nwcharge.

Please help.

Thanks

View 5 Replies View Related

Arithmetic Overflow Error

Sep 18, 2006

i have an sql query that goes like this:

select CategoryID & power(2,x) from CategoryDetails

where CategoryID can range upto 15 digits eg: 137652435487090
x is the result of a formulae and can range upto 2 digits eg:95

CategoryID is the current category ID.
Number resulting from the formulae is the subcategory ID of the current categoryID.

I am getting error Arithmetic overflow error for datatype bigint.
what datatype shd I use to resolve the error or is there any way out.

View 9 Replies View Related

Arithmetic Overflow Error

Mar 27, 2008

Dear gurus

I want to have this query for some calculation

SELECT DATEADD(day, 1, CONVERT(datetime, @ddate, 101)) AS Expr1

@ddate is a parameter which will be passed during runtime.

when i try this is in the query window i get arithmetic overflow error

Thanks and Regards
Arun

View 5 Replies View Related

Arithmetic Overflow Error

Jan 7, 2008

Hi,
Below query failed which executing in sql 2005 64Bit and large memory

select count(*) AS Buffered_Page_Count
,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type int.

but it work fine in my test server which running 2GB of ram with sql 2005

View 7 Replies View Related

Arithmetic Overflow Error

Oct 18, 2007

I get an arithmetic overflow error when trying to insert DateTime.Now() as a SmallDateTime. When I set up my table definition I entered SmallDateTime and the program automatically allowed a length of 4. Any suggestions?

View 6 Replies View Related

Error :Arithmetic Overflow Occurred

Feb 7, 2000

I have two tables which have dates in varchar fields on both. I have to
join them to get the data . I wrote the following syntax in where clause. In
one of the fields includes time and the other does not. But the thing is, I
got the Arithmetic overflow occurred error message after '01/01/2000'. Isn't
it strange?

convert(varchar(30),convert(datetime,R.request_dat e),101)=convert(varchar(30
),convert(datetime,B.request_date),101)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved