Showing posts with label whenever. Show all posts
Showing posts with label whenever. 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

Monday, March 26, 2012

Problems with SQL 2005 64bit sending emails

I have SQL 2005 on WS 2003 64 bit
Whenever SQL agent is trying to send an email, it fails and I get this
error message in logs:
[264] An attempt was made to send an email when no email session has
been established
If I run the test email manually is working correctly. If I try to
send email using msdb.dbo.sp_send_dbmail is working correctly. If I
run manually a service intregation package that is sending the email is
running correctly.
But if I try to run a service integration package thats sending email
through a SQL Agent Job, then it fails with previous error message.I have the same error when attempting to send a message on an alert with
SQLAgent, although test mails work fine. I found a reference to this on
Google Groups:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/de66aea413263523/c28aea3b8f3ac1f0?lnk=st&q=%22An+attempt+was+made+to+send+an+email+when+no+email+session+has+been+established%22&rnum=1&hl=en#c28aea3b8f3ac1f0
but no resolution...
"marian@.dumitrascu.net" <md@.dumitrascu.net> wrote in message
news:1143045307.345110.165550@.u72g2000cwu.googlegroups.com...
> I have SQL 2005 on WS 2003 64 bit
> Whenever SQL agent is trying to send an email, it fails and I get this
> error message in logs:
> [264] An attempt was made to send an email when no email session has
> been established
> If I run the test email manually is working correctly. If I try to
> send email using msdb.dbo.sp_send_dbmail is working correctly. If I
> run manually a service intregation package that is sending the email is
> running correctly.
> But if I try to run a service integration package thats sending email
> through a SQL Agent Job, then it fails with previous error message.
>|||Unfortunately SQLAgent cannot use dbmail as an alerting option in x64
editions. See http://support.microsoft.com/kb/908360/en-us
I do wish they will fix this, but it does not look like it made it in
SP1.
Kevin|||"Kevin Madsen" <kevin.madsen@.gmail.com> wrote in message
news:1143130284.972295.178130@.e56g2000cwe.googlegroups.com...
> Unfortunately SQLAgent cannot use dbmail as an alerting option in x64
> editions. See http://support.microsoft.com/kb/908360/en-us
> I do wish they will fix this, but it does not look like it made it in
> SP1.
> Kevin
>
*sigh* All these little things that seem to be missing when you want to use
64-bit are most annoying. (Not just talking about SQL Server)|||Well now that SP1 is out, it appears to be working on x64!
"Kevin Madsen" <kevin.madsen@.gmail.com> wrote in message
news:1143130284.972295.178130@.e56g2000cwe.googlegroups.com...
> Unfortunately SQLAgent cannot use dbmail as an alerting option in x64
> editions. See http://support.microsoft.com/kb/908360/en-us
> I do wish they will fix this, but it does not look like it made it in
> SP1.
> Kevin
>|||You know, its weird I am on 64 bit too, the maintenance plans send email, db
mail is config properly and can send a test email, but all sql jobs I set up
cannot send any notification with the same error listed here -
--
John F.
"Michael D'Angelo" wrote:
> Well now that SP1 is out, it appears to be working on x64!
> "Kevin Madsen" <kevin.madsen@.gmail.com> wrote in message
> news:1143130284.972295.178130@.e56g2000cwe.googlegroups.com...
> > Unfortunately SQLAgent cannot use dbmail as an alerting option in x64
> > editions. See http://support.microsoft.com/kb/908360/en-us
> >
> > I do wish they will fix this, but it does not look like it made it in
> > SP1.
> >
> > Kevin
> >
>
>|||What do you mean working? We have sp1 and I haven't been able to get this to
work using database mail OR SQL Mail. The KB article is messed up b/c it says:
Note If this option is not available, enable SQL Server Agent Mail by using
the Surface Area Configuration tool, and then use the Surface Area
Configuration tool to set up a mail profile .
Well, there is nothing that I see called SQL Server Agent Mail in SAC (I'm
assuming that they mean SQL Mail). PLUS, there's nothing that I see in SAC
for setting up a mail profile (I think that this is done using Outlook
Express). It also doesn't tell you that you have to be logged on to the
server as the service account when setting up the mail profile (I'm pretty
sure that's true). Anyway, this article has too many things that might not be
exactly correct that I'm not sure if I'm missing something or if I have some
other problem.
Looking for help!
Michelle
"Michael D'Angelo" wrote:
> Well now that SP1 is out, it appears to be working on x64!
> "Kevin Madsen" <kevin.madsen@.gmail.com> wrote in message
> news:1143130284.972295.178130@.e56g2000cwe.googlegroups.com...
> > Unfortunately SQLAgent cannot use dbmail as an alerting option in x64
> > editions. See http://support.microsoft.com/kb/908360/en-us
> >
> > I do wish they will fix this, but it does not look like it made it in
> > SP1.
> >
> > Kevin
> >
>
>|||The article also neglects to mention that you have to restart the sql server
agent service after assigning the profile for it to use. That's what worked
for me for BOTH SQL Mail and Database Mail. So, I guess that was my problem
all along. BOL doesn't mention restarting the agent service in the How to:
Configure SQL Server Agent Mail to Use Database Mail. I don't think that I
had to restart the service on the 32-bit machine. Who knows...Anyway,
database mail DOES seem to work on 64-bit machines with Agent and sp1. That
KB Article really needs to be cleaned up, though.
"Michelle" wrote:
> What do you mean working? We have sp1 and I haven't been able to get this to
> work using database mail OR SQL Mail. The KB article is messed up b/c it says:
> Note If this option is not available, enable SQL Server Agent Mail by using
> the Surface Area Configuration tool, and then use the Surface Area
> Configuration tool to set up a mail profile .
> Well, there is nothing that I see called SQL Server Agent Mail in SAC (I'm
> assuming that they mean SQL Mail). PLUS, there's nothing that I see in SAC
> for setting up a mail profile (I think that this is done using Outlook
> Express). It also doesn't tell you that you have to be logged on to the
> server as the service account when setting up the mail profile (I'm pretty
> sure that's true). Anyway, this article has too many things that might not be
> exactly correct that I'm not sure if I'm missing something or if I have some
> other problem.
> Looking for help!
> Michelle
> "Michael D'Angelo" wrote:
> > Well now that SP1 is out, it appears to be working on x64!
> >
> > "Kevin Madsen" <kevin.madsen@.gmail.com> wrote in message
> > news:1143130284.972295.178130@.e56g2000cwe.googlegroups.com...
> > > Unfortunately SQLAgent cannot use dbmail as an alerting option in x64
> > > editions. See http://support.microsoft.com/kb/908360/en-us
> > >
> > > I do wish they will fix this, but it does not look like it made it in
> > > SP1.
> > >
> > > Kevin
> > >
> >
> >
> >

Friday, March 9, 2012

Problems with Enterprise Manager and 2003 Server

I have 2 2003 Servers running SQL Server 2000, SP3. All are seviced packed up the the 9's and i get the same problem on both.

Whenever you try and access any GUI views of tables etc. within Enterprise Manager the following error message occurs.

"An unexpected error happened during this operation."

I have tried simple things like re-install of client tools, and also re-registering mdt2dd.dll. But to no avail.

Any idea's please?A couple of thoughts:

1. check your error logs (system and application). There may be something there (though based on recent experience, I wouldn't bet on it).

2. Check privileges/security/user rights. Are you the admin on the PC?

3. Fire up SQL Profiler (make sure to trap for all errors); go through the process that gives you the error and see if there are any results.

4. Check MS Technet and see if they have related articles.

5. Reinstall the client tools.

6. Introduce your PC to a Louisville Slugger (my preference is for the 34 oz. variety).

Regards,

hmscott|||Tried all, all came up zip.

MS Tech did come up with a very similar problem but sadly was refering to SQL Server 7, and would have been fixed with SP3.

Cheers anyway.|||Checked the windows event logs ? Maybe they will provide a clue ..

Monday, February 20, 2012

Problems Using the SQL Server Surface Area Configuration

I am currently running a Windows Vista Enterprise OS platform using SQL Express, whenever i add a user to the administrator group using the SQL Server Surface Area Configuration UI, each time i click on the link to add New Administrator, the previous administrator i just added still appears on the left - just as if i never added this user previously.

Is there a way around this issue? or is this a SQL Express problem, because i also tried doing it with SQL Server Enterprise edition on a Win2K3 SP2 box and it gives the same results.

Feedback is greatly appreciated.

Thanks

Did you used the SQL Server provisiong tool on the Vista machine ?

Jens Suessmeyer.


http://www.sqlserver2005.de