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
Tuesday, March 20, 2012
Problems with merge replication (MSmerge_genhistory)
Please anybody can you help me, I have a problems with merge replication.
Appear in my error log this message.
The process could not make a generation at the 'Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147200994)
----
--
Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique
index 'unc1MSmerge_genhistory'.
(Source: BDF (Data source); Error number: 2601)
Thanks a lot.
Almeida brito.Almeida,
Might try here:
Replication NG
http://www.microsoft.com/technet/co...ver.replication
HTH
Jerry
"Almeida brito." <Almeida brito.@.discussions.microsoft.com> wrote in message
news:CBECC476-8B85-4067-BADC-F2BBAA5CC387@.microsoft.com...
> Dear All,
> Please anybody can you help me, I have a problems with merge replication.
> Appear in my error log this message.
> The process could not make a generation at the 'Publisher'.
> (Source: Merge Replication Provider (Agent); Error number: -2147200994)
> ----
--
> Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique
> index 'unc1MSmerge_genhistory'.
> (Source: BDF (Data source); Error number: 2601)
> Thanks a lot.
> Almeida brito.
Problems with merge replication (MSmerge_genhistory)
Please anybody can you help me, I have a problems with merge replication.
Appear in my error log this message.
The process could not make a generation at the 'Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147200994)
------
Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique
index 'unc1MSmerge_genhistory'.
(Source: BDF (Data source); Error number: 2601)
Thanks a lot.
Almeida brito.Almeida,
Might try here:
Replication NG
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.replication
HTH
Jerry
"Almeida brito." <Almeida brito.@.discussions.microsoft.com> wrote in message
news:CBECC476-8B85-4067-BADC-F2BBAA5CC387@.microsoft.com...
> Dear All,
> Please anybody can you help me, I have a problems with merge replication.
> Appear in my error log this message.
> The process could not make a generation at the 'Publisher'.
> (Source: Merge Replication Provider (Agent); Error number: -2147200994)
> ------
> Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique
> index 'unc1MSmerge_genhistory'.
> (Source: BDF (Data source); Error number: 2601)
> Thanks a lot.
> Almeida brito.
Problems with merge replication (MSmerge_genhistory)
Please anybody can you help me, I have a problems with merge replication.
Appear in my error log this message.
The process could not make a generation at the 'Publisher'.
(Source: Merge Replication Provider (Agent); Error number: -2147200994)
------
Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique
index 'unc1MSmerge_genhistory'.
(Source: BDF (Data source); Error number: 2601)
Thanks a lot.
Almeida brito.
Almeida,
Might try here:
Replication NG
http://www.microsoft.com/technet/com...replica tion
HTH
Jerry
"Almeida brito." <Almeida brito.@.discussions.microsoft.com> wrote in message
news:CBECC476-8B85-4067-BADC-F2BBAA5CC387@.microsoft.com...
> Dear All,
> Please anybody can you help me, I have a problems with merge replication.
> Appear in my error log this message.
> The process could not make a generation at the 'Publisher'.
> (Source: Merge Replication Provider (Agent); Error number: -2147200994)
> ------
> Cannot insert duplicate key row in object 'MSmerge_genhistory' with unique
> index 'unc1MSmerge_genhistory'.
> (Source: BDF (Data source); Error number: 2601)
> Thanks a lot.
> Almeida brito.
Problems with Linked servers between sql 2000 and sql 2005
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a sql statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
What is the SQL Statement that works/doesn't work?
Do any other queries work on the SQL 2000 server when executed from the SQL 2005 server?
|||Also, if you can you post:
1 - Any errors that you see in the error log
2 - The commands that you used to create the linked server
3 - What type of authentication are you using? Integrated auth?
You may also want to take a look at this blog posting: http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx. It's describes the scenario using sql2k5 servers but there is a lot of info that applies to sql2k as well.
Thanks,
Il-Sung.
|||The select statement i used is just a simple one:
Select * from MyLink.MyDatabase.dbo.Table1
This is working now. It had to do with the way sql server 05 was setup.
But i have a different problem.
When i place the select statement in a simple insert trigger that has nothing in except for the select statement the trigger just blocks and does not execute. It stalls to a lock. The trigger is placed on a table at the sql 2000 server.
Any suggestions as to why this might not work?
Sincerely
Dan
|||Hi, Dan
This indicates your insert trigger fails to execute, normally, due to the rwo to be inserted did not pass all constraints check such as primary keys, unique indeex, foregin keys, etc...
Hence, I suggest you take a look at your table schema and double check. If you still face problem, please post your question to SQL Engine forumn where experts can help you very quick.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1
Good Luck!
Ming.
Problems with Linked servers between sql 2000 and sql 2005
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a sql statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
What is the SQL Statement that works/doesn't work?
Do any other queries work on the SQL 2000 server when executed from the SQL 2005 server?
|||Also, if you can you post:
1 - Any errors that you see in the error log
2 - The commands that you used to create the linked server
3 - What type of authentication are you using? Integrated auth?
You may also want to take a look at this blog posting: http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx. It's describes the scenario using sql2k5 servers but there is a lot of info that applies to sql2k as well.
Thanks,
Il-Sung.
|||The select statement i used is just a simple one:
Select * from MyLink.MyDatabase.dbo.Table1
This is working now. It had to do with the way sql server 05 was setup.
But i have a different problem.
When i place the select statement in a simple insert trigger that has nothing in except for the select statement the trigger just blocks and does not execute. It stalls to a lock. The trigger is placed on a table at the sql 2000 server.
Any suggestions as to why this might not work?
Sincerely
Dan
|||Hi, Dan
This indicates your insert trigger fails to execute, normally, due to the rwo to be inserted did not pass all constraints check such as primary keys, unique indeex, foregin keys, etc...
Hence, I suggest you take a look at your table schema and double check. If you still face problem, please post your question to SQL Engine forumn where experts can help you very quick.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1
Good Luck!
Ming.
Problems with Linked servers between sql 2000 and sql 2005
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a sql statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
What is the SQL Statement that works/doesn't work?
Do any other queries work on the SQL 2000 server when executed from the SQL 2005 server?
|||Also, if you can you post:
1 - Any errors that you see in the error log
2 - The commands that you used to create the linked server
3 - What type of authentication are you using? Integrated auth?
You may also want to take a look at this blog posting: http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx. It's describes the scenario using sql2k5 servers but there is a lot of info that applies to sql2k as well.
Thanks,
Il-Sung.
|||The select statement i used is just a simple one:
Select * from MyLink.MyDatabase.dbo.Table1
This is working now. It had to do with the way sql server 05 was setup.
But i have a different problem.
When i place the select statement in a simple insert trigger that has nothing in except for the select statement the trigger just blocks and does not execute. It stalls to a lock. The trigger is placed on a table at the sql 2000 server.
Any suggestions as to why this might not work?
Sincerely
Dan
|||Hi, Dan
This indicates your insert trigger fails to execute, normally, due to the rwo to be inserted did not pass all constraints check such as primary keys, unique indeex, foregin keys, etc...
Hence, I suggest you take a look at your table schema and double check. If you still face problem, please post your question to SQL Engine forumn where experts can help you very quick.
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1
Good Luck!
Ming.
Problems with Linked servers between sql 2000 and sql 2005
Dear all
I have created linked server object between a sql 2000 and sql 2005 database. i have the latest mdacs installed and all the services enabled on both sides.
But when i try and run a sql statement on the sql 2000 side i get this error:
[DBNETLIB][ConnectionOpen (PreLoginHandshake()).]General network error. Check your network documentation.
If i try and run a swl statment on the sql server 2005 side the procedure works.
Has anyone experienced the same problem?Any advice?
Thank you so much for the help.
Dan
Try also posting to the SQL Server forum at http://forums.microsoft.com/msdn/showforum.aspx?forumid=87&siteid=1
Thnax for the advice.I am just about to copy this post on there.
Much apreciated
Monday, March 12, 2012
Problems with groupings on Reporting Services 2005
I have a problem with a report where users want to see transacctions grouped by Account number and type(e.g: incomes and payments).
And so I use a table object where I put 2 groups:
1) Group by Account Number: Field!Account.Value
2) Group by Transaction Type: Field!Type.Value
I put in the detaill row group by: Field!Type.Value and Field!Account.Value.
When I execute the report only show the first row per transaction type, but the dataset has some rows per transaction type.
Please help me with this problem, thanks...
Sounds like you don't need a detail row group.
Saturday, February 25, 2012
Problems while attaching a database in SQLServer 2000
Dear all,
I recive a error when i attaching a database in sqlserver 2000. The error is :-
An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)
Are you attaching a database in SQL Server 2000 from a SQL Server 2005 Express? This won't work.|||Not in Express edition. I am trying it on Sql Server 2005 Enterprise Edition..|||
Hi Deepak,
could you post the t-sql used !!! and post Errors from SQL Error Logs too!!
Hemantgiri S. Gowami
|||I recive a error when i attaching a database in sqlserver 2000. The error is :-
An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)
|||How I can solve the error when attaching a database in sqlserver 2000.
Could not find row in sysindex for databases ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)
Problems while attaching a database in SQLServer 2000
Dear all,
I recive a error when i attaching a database in sqlserver 2000. The error is :-
An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)
Are you attaching a database in SQL Server 2000 from a SQL Server 2005 Express? This won't work.|||Not in Express edition. I am trying it on Sql Server 2005 Enterprise Edition..|||
Hi Deepak,
could you post the t-sql used !!! and post Errors from SQL Error Logs too!!
Hemantgiri S. Gowami
|||I recive a error when i attaching a database in sqlserver 2000. The error is :-
An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)
|||How I can solve the error when attaching a database in sqlserver 2000.
Could not find row in sysindex for databases ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)
Problems while attaching a database in SQLServer 2000
Dear all,
I recive a error when i attaching a database in sqlserver 2000. The error is :-
An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)
Are you attaching a database in SQL Server 2000 from a SQL Server 2005 Express? This won't work.|||Not in Express edition. I am trying it on Sql Server 2005 Enterprise Edition..|||
Hi Deepak,
could you post the t-sql used !!! and post Errors from SQL Error Logs too!!
Hemantgiri S. Gowami
|||I recive a error when i attaching a database in sqlserver 2000. The error is :-
An exception occurred while executing a T-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Could not find row in sysindex for databases ID 12, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)
|||How I can solve the error when attaching a database in sqlserver 2000.
Could not find row in sysindex for databases ID 7, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexs.
Could not open new database 'ABC'. Create Database is aborted. (Microsoft SQL Server, Error: 602)