Showing posts with label records. Show all posts
Showing posts with label records. Show all posts

Friday, March 30, 2012

Problems With Sum

Dear All
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

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
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

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 ?
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

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 ?
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

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!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