Friday, March 30, 2012
Problems With Sum
We have a table with around 100000 records, this table
contains an Int value that can be >= 1 <= 999999.
Whenever we use the command
Select sum(MYINT) from tblTEST
We get the error 'Arithmetic overflow error converting
expression to data type int.'
I know that the result of the sum is bigger than an int
however I was wondering if there is a trick I can do so I
don't need to create my own loop to add them together.
Thanks for your time
JTry this
Select convert(decimal(12,2),sum(MYINT)) from tblTEST
Madhivanan|||SELECT SUM(CAST(myint AS BIGINT))
FROM tblTest
David Portas
SQL Server MVP
--|||You need to put CONVERT inside the SUM otherwise the SUM will still
overflow.
David Portas
SQL Server MVP
--|||Madhivanan,
It will still give you an Arithmetic Overflow error!
Try the code below
SELECT IDENTITY(INT) AS Number
INTO #TMP
FROM sysobjects s1
CROSS JOIN sysobjects s2
CROSS JOIN sysobjects s3
Select convert(decimal(12,2),sum(Number)) from #TMP
If you dont feel offended, I strongly recommend you to test
your solutions before you post them
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<madhivanan2001@.gmail.com> wrote in message
news:1109256055.787979.288100@.z14g2000cwz.googlegroups.com...
> Try this
> Select convert(decimal(12,2),sum(MYINT)) from tblTEST
> Madhivanan
>|||Thanks Thomas
It should be
Select sum(convert(decimal(12,2),Number)) from tblTEST
Madhivanan
Wednesday, March 7, 2012
Problems with DB 70 GB
Im trying to update millions of records , to a table , and the SQL SERVER ,
go too slow ?
how can I do ?
I have only one datafile , can i split ? It will increase the speed ?
Thanks
You might be able to break the update into more manageable pieces with a
where clause. It's tough to know how to help you without more information.
Can you post the update query?
"Batzal via droptable.com" wrote:
> hello,
> Im trying to update millions of records , to a table , and the SQL SERVER ,
> go too slow ?
> how can I do ?
> I have only one datafile , can i split ? It will increase the speed ?
> Thanks
>
|||I Have a table with 30 Millions of rows , and I must to add, 12 Millions .
The query is :
Insert into Table1
Select *
from TableTemp
This query works good , when i put a where clause , but if i want to insert
all the rows , it dont work.
I Dont know what to do , Split the Database ? This Database , has 70 GB .
Thanks
fnguy wrote:[vbcol=seagreen]
>You might be able to break the update into more manageable pieces with a
>where clause. It's tough to know how to help you without more information.
>Can you post the update query?
>[quoted text clipped - 5 lines]
|||You might be running out of log space or something similar such as server
resources. I'd suggest you get a dba in their to look at and review the
process for you. He / She should be able to resolve this easily in a day.
But be forwarned that you may hear something like "You need to drop your
non-clustered indexes first", "You need to get users out of the database
first", etc.
"Batzal via droptable.com" wrote:
> I Have a table with 30 Millions of rows , and I must to add, 12 Millions .
> The query is :
> Insert into Table1
> Select *
> from TableTemp
> This query works good , when i put a where clause , but if i want to insert
> all the rows , it dont work.
> I Dont know what to do , Split the Database ? This Database , has 70 GB .
> Thanks
> fnguy wrote:
>
Problems with DB 70 GB
Im trying to update millions of records , to a table , and the SQL SERVER ,
go too slow ?
how can I do ?
I have only one datafile , can i split ? It will increase the speed ?
ThanksYou might be able to break the update into more manageable pieces with a
where clause. It's tough to know how to help you without more information.
Can you post the update query?
"Batzal via droptable.com" wrote:
> hello,
> Im trying to update millions of records , to a table , and the SQL SERV
ER ,
> go too slow ?
> how can I do ?
> I have only one datafile , can i split ? It will increase the speed ?
> Thanks
>|||I Have a table with 30 Millions of rows , and I must to add, 12 Millions .
The query is :
Insert into Table1
Select *
from TableTemp
This query works good , when i put a where clause , but if i want to insert
all the rows , it dont work.
I Dont know what to do , Split the Database ? This Database , has 70 GB .
Thanks
fnguy wrote:[vbcol=seagreen]
>You might be able to break the update into more manageable pieces with a
>where clause. It's tough to know how to help you without more information.
>Can you post the update query?
>
>[quoted text clipped - 5 lines]|||You might be running out of log space or something similar such as server
resources. I'd suggest you get a dba in their to look at and review the
process for you. He / She should be able to resolve this easily in a day.
But be forwarned that you may hear something like "You need to drop your
non-clustered indexes first", "You need to get users out of the database
first", etc.
"Batzal via droptable.com" wrote:
> I Have a table with 30 Millions of rows , and I must to add, 12 Millions .
> The query is :
> Insert into Table1
> Select *
> from TableTemp
> This query works good , when i put a where clause , but if i want to inser
t
> all the rows , it dont work.
> I Dont know what to do , Split the Database ? This Database , has 70 GB .
> Thanks
> fnguy wrote:
>
Problems with DB 70 GB
Im trying to update millions of records , to a table , and the SQL SERVER ,
go too slow ?
how can I do ?
I have only one datafile , can i split ? It will increase the speed ?
ThanksYou might be able to break the update into more manageable pieces with a
where clause. It's tough to know how to help you without more information.
Can you post the update query?
"Batzal via SQLMonster.com" wrote:
> hello,
> Im trying to update millions of records , to a table , and the SQL SERVER ,
> go too slow ?
> how can I do ?
> I have only one datafile , can i split ? It will increase the speed ?
> Thanks
>|||I Have a table with 30 Millions of rows , and I must to add, 12 Millions .
The query is :
Insert into Table1
Select *
from TableTemp
This query works good , when i put a where clause , but if i want to insert
all the rows , it dont work.
I Dont know what to do , Split the Database ? This Database , has 70 GB .
Thanks
fnguy wrote:
>You might be able to break the update into more manageable pieces with a
>where clause. It's tough to know how to help you without more information.
>Can you post the update query?
>> hello,
>[quoted text clipped - 5 lines]
>> Thanks|||You might be running out of log space or something similar such as server
resources. I'd suggest you get a dba in their to look at and review the
process for you. He / She should be able to resolve this easily in a day.
But be forwarned that you may hear something like "You need to drop your
non-clustered indexes first", "You need to get users out of the database
first", etc.
"Batzal via SQLMonster.com" wrote:
> I Have a table with 30 Millions of rows , and I must to add, 12 Millions .
> The query is :
> Insert into Table1
> Select *
> from TableTemp
> This query works good , when i put a where clause , but if i want to insert
> all the rows , it dont work.
> I Dont know what to do , Split the Database ? This Database , has 70 GB .
> Thanks
> fnguy wrote:
> >You might be able to break the update into more manageable pieces with a
> >where clause. It's tough to know how to help you without more information.
> >Can you post the update query?
> >
> >> hello,
> >>
> >[quoted text clipped - 5 lines]
> >>
> >> Thanks
>
Saturday, February 25, 2012
Problems with adding data to wider table
I want to insert a record with fields let say"ProductCode, Name" to a table with records like "ProductCode, Name, NumberofItems". I get mismatching errors "Conversion from string "INSERT INTO dbo.t_Shopping(Productcod" to type 'Double' is not valid.". Note the missing last letter. ProductCode and Name are both varChars, NumberofItems is int in table and double in my code.
This is my insertion script ".CommandType = CommandType.Text = "INSERT INTO dbo.t_Shopping(ProductCode, Name, NumberofItems)SELECT ProductCode, Name, @.NumberofItems FROM dbo.t_Product WHERE ProductCode=@.ProductCode"
I have asked this question previously in an other mail, but probably because that thread was marked solved, no one answered.
The whole code is like this: I translated some names. I hope its still valid.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim NumberofItems As Double = 1
Dim conn As New SqlConnection("Data Source=DV2\SQLEXPRESS;Initial Catalog=testaus;Integrated Security=True")
Dim row As DetailsViewRow = DetailsView1.Rows(0)
Dim ProductCode As String = row.Cells(1).Text
Dim cmd As New Data.SqlClient.SqlCommand
Dim param As New SqlParameter()
TextBox1.Text = ProductCode
With cmd
.Connection = conn
.CommandType = CommandType.Text = "INSERT INTO dbo.t_Shopping(ProductCode, Name, NumberofItems)SELECT ProductCode, Name, @.NumberofItems_
FROM dbo.t_Product WHERE ProductCode=@.ProductCode"
.Parameters.AddWithValue("@.ProductCode", ProductCode)
.Parameters.AddWithValue("@.NumberofItems", NumberofItems) '
End With
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch ex As Data.SqlClient.SqlException
Throw New ApplicationException("An error occurred while trying to insert the record")
Finally
conn.Dispose()
End Try
Regards
Leif
My Sql command code had problems, probably. I tried this (see below), but got still more unclearer error. "Incorrect syntax near ','.".I dont know if the problem is in sql query string or in code using 2 parameters,or in wrong variable types.
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim NumberofItems As Integer = 1
Dim luku As Integer
Dim conn As New SqlConnection("Data Source=DV2\SQLEXPRESS;Initial Catalog=testaus;Integrated Security=True")
Dim row As DetailsViewRow = DetailsView1.Rows(0)
Dim ProductCode As String = row.Cells(1).Text
TextBox1.Text = ProductCode
Dim cmd As New SqlCommand("insert dbo.t_Shopping (ProductCode, Name, NumberofItems) select (ProductCode, Name,@.NumberofItems) FROM dbo.t_Product_
WHERE ProductCode=@.ProductCode", conn)
Dim partk, paramaar As New SqlParameter()
partk.Value = ProductCode
partk.SqlDbType = SqlDbType.VarChar
partk.Direction = ParameterDirection.Input
partk.ParameterName = "@.ProductCode"
cmd.Parameters.Add(partk)
paramaar.Value = NumberofItems
paramaar.SqlDbType = SqlDbType.Int
paramaar.Direction = ParameterDirection.Input
paramaar.ParameterName = "@.NumberofItems"
cmd.Parameters.Add(paramaar)
cmd.Connection.Open()
luku = cmd.ExecuteNonQuery() 'This gives errors
cmd.Connection.Close()
End Sub
Leif
|||
LISM:
Dim cmd As New SqlCommand("insert dbo.t_Shopping (ProductCode, Name, NumberofItems) select (ProductCode, Name,@.NumberofItems) FROM dbo.t_Product_
WHERE ProductCode=@.ProductCode", conn)
First, remove the () from the select query.
Second, make sure all the parameters are actually having some values.
Hope this will help.
|||Thanks
Is there difference between NumberofItems and @.NumberofItems and so on. Variables without @. have a value, but with them don't. Besides, I now tried to @.NumberofItems =NumberofItems and it didn't help (got errors).
Whats next?
Leif
It depends on what you want to use. @.NumberofItems seems to be what user typed in and NumberofItems seems to be coming from the table.. so its your call on which should go into the table.
|||Thank you for an answer
It looks like I have misunderstood this.
NumberOfItem comes now from code. Its value is 1 now. And yes, someday it may come from the user. Then I try to put it in the Shopping table. I try to do it this way because in Shopping table there is an extra field called NumberofItems. Shopping is a shopping basket and it should contain amount field. The Product table is really a list of names and codes.
Query should work so that it looks for an iten whose code is in ProductCode or @.ProductCode. And then copies that some fields of that record plus NumberOfItems field to shopping basket.
I don't really know where the problem is, in code or in query.
Regards
Leif
|||Just change your query to
INSERTdbo.t_Shopping (ProductCode, Name, NumberofItems)
SELECT ProductCode, Name,@.NumberofItems
FROMdbo.t_Product
WHERE ProductCode=@.ProductCode
as dhimant suggested and it would work.
||| Sorry about the mess. But it works!
There was still some problem with code or query, and I wanted to ask more help. Anyway I tried the query in this code, and at first I got errors:"Incorrect syntax near ')'. This is my code without translation. I left out name field to get shorter lines. Tuotekoodi=Productcode, Osto~Shopping, Maara=numberOfItems and so on.
Dim cmd As New SqlCommand("INSERT dbo.t_Osto (Tuotekoodi, Maara) SELECT Tuotekoodi,@.Maara FROM dbo.t_Tuote WHERE Tuotekoodi=@.Tuotekoodi", conn)
cmd.Parameters.Add("@.Maara", SqlDbType.Int)
cmd.Parameters.Add("@.Tuotekoodi", SqlDbType.VarChar)
conn.Open()
cmd.Parameters("@.Tuotekoodi").Value = Tuotekoodi
cmd.Parameters("@.Maara").Value = Maara
luku = cmd.ExecuteNonQuery()
conn.Close()
The most difficult and interesting part was the fact that the IDE inserted some extra brackets () . I copy/pasted your code to an empty place in my code, and got many errors, thats ok. But at the same time IDE inserted couple of pairs of brackets there. And then I pasted the code with extra brackets to my code. That was quite difficult to see. I tend to trust Copy/paste. A nasty feature?
Probably that spoiled my earlier attempts, also.
And lets not forget, it works, thanks.
Leif
problems with a stored prc
through it and insert records into another table in the same db. What I
have written is causing SQL server to hang or some other performance
bottleneck. Here is the code. Anybody got any ideas? Thanks!
ALTER PROCEDURE initQuestionsAnswered
@.empid int,@.testid int
AS
declare @.ANSID VARCHAR(10),@.QID VARCHAR(10)
DECLARE MY_CURS1 CURSOR FOR
select ansid,answers.questionid from
answers inner join questions on
questions.questionid=answers.questionid
inner join concepts on
concepts.conceptid=questions.conceptid
where concepts.testid=@.testid
OPEN MY_CURS1
FETCH MY_CURS1 INTO @.ANSID,@.QID
WHILE (@.@.FETCH_STATUS=0)
BEGIN
if not exists(
select answerid from questionsanswered where questionid=@.QID and
empid=@.empid)
BEGIN
insert into questionsanswered
(empid,questionid,testid,answercorrect)
values (@.empid,@.QID,@.testid,0)
END
END
close my_curs1
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Yep, cursors usually destroy performance and scalability. Don't use
them. I think you want something like this (untested though):
CREATE PROCEDURE initQuestionsAnswered
@.empid INTEGER,
@.testid INTEGER
AS
INSERT INTO QuestionsAnswered (empid, questionid, testid,
answercorrect)
SELECT @.empid, A.questionid, @.testid, 0
FROM Answers
JOIN Questions AS Q
ON Q.questionid = A.questionid
JOIN Concepts AS C
ON C.conceptid = Q.conceptid
WHERE C.testid = @.testid
AND NOT EXISTS
(SELECT *
FROM QuestionsAnswered
WHERE questionid = A.questionid
AND empid = @.empid
AND testid = @.testid)
GO
--
David Portas
SQL Server MVP
--|||Phil Townsend wrote:
> I have to get a stored procedure to produce a result set then loop
> through it and insert records into another table in the same db. What
I
> have written is causing SQL server to hang or some other performance
> bottleneck. Here is the code. Anybody got any ideas? Thanks!
> ALTER PROCEDURE initQuestionsAnswered
> @.empid int,@.testid int
> AS
> declare @.ANSID VARCHAR(10),@.QID VARCHAR(10)
> DECLARE MY_CURS1 CURSOR FOR
> select ansid,answers.questionid from
> answers inner join questions on
> questions.questionid=answers.questionid
> inner join concepts on
> concepts.conceptid=questions.conceptid
> where concepts.testid=@.testid
> OPEN MY_CURS1
> FETCH MY_CURS1 INTO @.ANSID,@.QID
> WHILE (@.@.FETCH_STATUS=0)
> BEGIN
> if not exists(
> select answerid from questionsanswered where questionid=@.QID and
> empid=@.empid)
> BEGIN
> insert into questionsanswered
> (empid,questionid,testid,answercorrect)
> values (@.empid,@.QID,@.testid,0)
> END
> END
> close my_curs1
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
You don't have a second fetch inside your while loop.
--
David Rowland
http://dbmonitor.tripod.com