Problems using SQL: fails

Showing posts with label fails. Show all posts

Friday, March 30, 2012

Problems with T-SQL in SQL Job

I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.You should be able to use the convert function directly:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
<stephan@.pathcom.com> wrote in message
news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
I have a strange problem. I have some T-SQL that functions properly in
Analyzer but fails when I write the same code into a SQL Job:
DECLARE @.DateTime datetime
DECLARE @.DateNoTime varchar(10)
SET @.DateTime = (GetDate())
SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
DECLARE @.Result datetime
SET @.Result = CONVERT(datetime, @.DateNoTime)
The last line, the CONVERT, fails in the SQL Job. I've tried using
CAST to see if that made a difference but I received the same error.
Here is the error SQL Agent generates:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
step failed.
Does anyone have any thoughts? I'm running SQL 2000.|||Thanks for the advice Tom but I still receive the same error when it
tries to convert the data. Like I said, this works well in Analyzer,
but not when it's set up as a step in a Job.
SR
Tom Moreau wrote:
> You should be able to use the convert function directly:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> <stephan@.pathcom.com> wrote in message
> news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> I have a strange problem. I have some T-SQL that functions properly
in
> Analyzer but fails when I write the same code into a SQL Job:
> DECLARE @.DateTime datetime
> DECLARE @.DateNoTime varchar(10)
> SET @.DateTime = (GetDate())
> SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
'/'
> + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> DECLARE @.Result datetime
> SET @.Result = CONVERT(datetime, @.DateNoTime)
> The last line, the CONVERT, fails in the SQL Job. I've tried using
> CAST to see if that made a difference but I received the same error.
> Here is the error SQL Agent generates:
> The conversion of a char data type to a datetime data type resulted
in
> an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> step failed.
> Does anyone have any thoughts? I'm running SQL 2000.|||Tom's advice should've worked. Your convert statement
is faulty.
What version of SQL Server are you running?
<stephan@.pathcom.com> wrote in message
news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> Thanks for the advice Tom but I still receive the same error when it
> tries to convert the data. Like I said, this works well in Analyzer,
> but not when it's set up as a step in a Job.
> SR
> Tom Moreau wrote:
> > You should be able to use the convert function directly:
> >
> > SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
> >
> > <stephan@.pathcom.com> wrote in message
> > news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> > I have a strange problem. I have some T-SQL that functions properly
> in
> > Analyzer but fails when I write the same code into a SQL Job:
> >
> > DECLARE @.DateTime datetime
> > DECLARE @.DateNoTime varchar(10)
> >
> > SET @.DateTime = (GetDate())
> > SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
> '/'
> > + rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> > rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> >
> > DECLARE @.Result datetime
> > SET @.Result = CONVERT(datetime, @.DateNoTime)
> >
> > The last line, the CONVERT, fails in the SQL Job. I've tried using
> > CAST to see if that made a difference but I received the same error.
> > Here is the error SQL Agent generates:
> >
> > The conversion of a char data type to a datetime data type resulted
> in
> > an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> > Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> > step failed.
> >
> > Does anyone have any thoughts? I'm running SQL 2000.
>|||Stephan,
Try replacing the final statement with
SET @.Result = CONVERT(datetime, @.DateNoTime,103)
For whatever reason, the context in which this runs as a job step
has different language or dateformat settings than your Query Analyzer,
and you are applying CONVERT without specifying the format of
the date string, hence leaving the interpretation dependent on the context.
By the way, you can remove the time from a date without anything
depending on string formats for datetime:
SET @.Result = DATEADD(d,DATEDIFF(d,0,@.DateTime),0)
Steve Kass
Drew University
stephan@.pathcom.com wrote:
>I have a strange problem. I have some T-SQL that functions properly in
>Analyzer but fails when I write the same code into a SQL Job:
>DECLARE @.DateTime datetime
>DECLARE @.DateNoTime varchar(10)
>SET @.DateTime = (GetDate())
>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) + '/'
>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>DECLARE @.Result datetime
>SET @.Result = CONVERT(datetime, @.DateNoTime)
>The last line, the CONVERT, fails in the SQL Job. I've tried using
>CAST to see if that made a difference but I received the same error.
>Here is the error SQL Agent generates:
>The conversion of a char data type to a datetime data type resulted in
>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>step failed.
>Does anyone have any thoughts? I'm running SQL 2000.
>
>|||Armando,
Tom's statement didn't work because it did not specify a format for
the conversion back to datetime. Tom's statement would have worked this
way:
SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
SK
Armando Prato wrote:
>Tom's advice should've worked. Your convert statement
>is faulty.
>What version of SQL Server are you running?
><stephan@.pathcom.com> wrote in message
>news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
>
>>Thanks for the advice Tom but I still receive the same error when it
>>tries to convert the data. Like I said, this works well in Analyzer,
>>but not when it's set up as a step in a Job.
>>SR
>>Tom Moreau wrote:
>>
>>You should be able to use the convert function directly:
>>SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
>>--
>>Tom
>>---
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com
>>
>><stephan@.pathcom.com> wrote in message
>>news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
>>I have a strange problem. I have some T-SQL that functions properly
>>
>>in
>>
>>Analyzer but fails when I write the same code into a SQL Job:
>>DECLARE @.DateTime datetime
>>DECLARE @.DateNoTime varchar(10)
>>SET @.DateTime = (GetDate())
>>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
>>
>>'/'
>>
>>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
>>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
>>DECLARE @.Result datetime
>>SET @.Result = CONVERT(datetime, @.DateNoTime)
>>The last line, the CONVERT, fails in the SQL Job. I've tried using
>>CAST to see if that made a difference but I received the same error.
>>Here is the error SQL Agent generates:
>>The conversion of a char data type to a datetime data type resulted
>>
>>in
>>
>>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
>>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
>>step failed.
>>Does anyone have any thoughts? I'm running SQL 2000.
>>
>
>|||Ah yes, I see.
Thanks.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23mhjopo7EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Armando,
> Tom's statement didn't work because it did not specify a format for
> the conversion back to datetime. Tom's statement would have worked this
> way:
> SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103), 103)
> SK
>
> Armando Prato wrote:
> >Tom's advice should've worked. Your convert statement
> >is faulty.
> >
> >What version of SQL Server are you running?
> >
> ><stephan@.pathcom.com> wrote in message
> >news:1104421071.721624.39040@.z14g2000cwz.googlegroups.com...
> >
> >
> >>Thanks for the advice Tom but I still receive the same error when it
> >>tries to convert the data. Like I said, this works well in Analyzer,
> >>but not when it's set up as a step in a Job.
> >>
> >>SR
> >>
> >>Tom Moreau wrote:
> >>
> >>
> >>You should be able to use the convert function directly:
> >>
> >>SET @.Result = CONVERT (datetime, CONVERT (char (10), getdate(), 103))
> >>
> >>--
> >>Tom
> >>
> >>---
> >>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> >>SQL Server MVP
> >>Columnist, SQL Server Professional
> >>Toronto, ON Canada
> >>www.pinnaclepublishing.com
> >>
> >>
> >><stephan@.pathcom.com> wrote in message
> >>news:1104419748.745035.54770@.c13g2000cwb.googlegroups.com...
> >>I have a strange problem. I have some T-SQL that functions properly
> >>
> >>
> >>in
> >>
> >>
> >>Analyzer but fails when I write the same code into a SQL Job:
> >>
> >>DECLARE @.DateTime datetime
> >>DECLARE @.DateNoTime varchar(10)
> >>
> >>SET @.DateTime = (GetDate())
> >>SET @.DateNoTime = rtrim(cast(datepart(dd,@.DateTime) as char(2))) +
> >>
> >>
> >>'/'
> >>
> >>
> >>+ rtrim(cast(datepart(mm,@.DateTime) as char(2))) + '/' +
> >>rtrim(cast(datepart(yyyy,@.DateTime) as char(4)))
> >>
> >>DECLARE @.Result datetime
> >>SET @.Result = CONVERT(datetime, @.DateNoTime)
> >>
> >>The last line, the CONVERT, fails in the SQL Job. I've tried using
> >>CAST to see if that made a difference but I received the same error.
> >>Here is the error SQL Agent generates:
> >>
> >>The conversion of a char data type to a datetime data type resulted
> >>
> >>
> >>in
> >>
> >>
> >>an out-of-range datetime value. [SQLSTATE 22007] (Error 242)
> >>Associated statement is not prepared [SQLSTATE HY007] (Error 0). The
> >>step failed.
> >>
> >>Does anyone have any thoughts? I'm running SQL 2000.
> >>
> >>
> >
> >
> >
> >|||Thanks guys. That worked like a charm.
Thanks Steve for the explanation why it didn't work. Strange that the
Agent and the Analyzer use a different engine...
SR|||Stephan,
you may find that the owner of the sql agent job(and/or the sqlserver agent
account) uses a different account to the one you have used to access QA and
as a result possibly has a different default language setting which has
resulted in a different date format.
HTH
<stephan@.pathcom.com> wrote in message
news:1104433299.670932.135410@.c13g2000cwb.googlegroups.com...
> Thanks guys. That worked like a charm.
> Thanks Steve for the explanation why it didn't work. Strange that the
> Agent and the Analyzer use a different engine...
> SR
>

Problems with T-SQL in SQL Job

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:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thr
ead/de66aea413263523/c28aea3b8f3ac1f0?lnk=st&q=%22An+attempt+was+made+to+send+an+e
mail+when+no+email+session+has+been+esta
blished%22&rnum=1&hl=en#c28aea3b8f3ac1f0[/
url]
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...
>
>|||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 say
s:
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 b
e
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...
>
>|||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:
[vbcol=seagreen]
> 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 s
ays:
> Note If this option is not available, enable SQL Server Agent Mail by usin
g
> 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 so
me
> other problem.
> Looking for help!
> Michelle
> "Michael D'Angelo" 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 say
s:
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 b
e
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...
>
>|||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:
[vbcol=seagreen]
> 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 s
ays:
> Note If this option is not available, enable SQL Server Agent Mail by usin
g
> 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 so
me
> other problem.
> Looking for help!
> Michelle
> "Michael D'Angelo" wrote:
>sql

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 DTS package. Access denied

Hi,

I made a DTS-package and it works when I execute it manually, but when it is
run by the SQL Server Agent, it fails.

I have use the guide to create a maintenance plan. That doesn't work so good
either. It runs Optimizations, but not integrity checks or backups (probably
because integrity checks failed).

The following from the log file:
Executed as user: HT-DOMAIN\INTRAB-SQL. ...:

DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnError:
DTSStep_DTSDataPumpTask_1,

Error = -2147024891 (80070005) Error string: Access is denied.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp Help context: 1100 Error Detail Records:
Error: -2147024891 (80070005); Provider Error: 0 (0) Error string:
Access is denied. Error source: Microsoft Data Transformation
Services (DTS) Package Help file: sqldts80.hlp Help context:
1100 Error: -2147024891 (80070005); Provider Error: 0 (0)
Error string: Cannot open a log file of specified name. Access is denied.
Error source: Micr... Process Exit Code 1. The step failed.

Log-file endeth here....

My "data-sources"/Connections is set up to use sql-server
authentication where I enter superuser name+password, not windows
authentication.

If I browse around using SQL-EM:
Under Security - Logins HT-DOMAIN\intrab-sql:
Tab General: Grant Access
Tab Server Roles: System Administrators
Tab Database Access: checkmark at Mbestil, user=dbo; database roles
for mbestil = public + db_owner

I also have som problems identifying where the package fails. I have given
the individual "steps"/"transformations" some pretty good names, but in the
log-file it still shows the "old" names.

The DTS package empties a table, then fills it by copying data from another
table in another database on another server.

Anyone with some useful tips ?

/jimIn the services management you will find SQL Agent is
configured with a user account on the operating system.
What OS are you on?

This OS account needs the appropriate DB access that is
equivalent to yours when running interactively.

Once this is fixed, because SQL Agent handles the automated
tasks, your backups, reorganisations, maintenance plans,
DTS tasks and anything else you need to automate in
the environment should have no problems in running
unattended.

Hope this helps.

Pete Brown
Falls Creek
Oz

"Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
news:btdvco$1ad$1@.sunsite.dk...
> Hi,
> I made a DTS-package and it works when I execute it manually, but when it
is
> run by the SQL Server Agent, it fails.
> I have use the guide to create a maintenance plan. That doesn't work so
good
> either. It runs Optimizations, but not integrity checks or backups
(probably
> because integrity checks failed).
> The following from the log file:

...[trim]...

> Log-file endeth here....
> My "data-sources"/Connections is set up to use sql-server
> authentication where I enter superuser name+password, not windows
> authentication.
> If I browse around using SQL-EM:
> Under Security - Logins HT-DOMAIN\intrab-sql:
> Tab General: Grant Access
> Tab Server Roles: System Administrators
> Tab Database Access: checkmark at Mbestil, user=dbo; database roles
> for mbestil = public + db_owner
> I also have som problems identifying where the package fails. I have given
> the individual "steps"/"transformations" some pretty good names, but in
the
> log-file it still shows the "old" names.
> The DTS package empties a table, then fills it by copying data from
another
> table in another database on another server.
> Anyone with some useful tips ?
> /jim|||mountain man wrote:
> In the services management you will find SQL Agent is
> configured with a user account on the operating system.

Found it.

> What OS are you on?

Will check (not at problem site right now)

> This OS account needs the appropriate DB access that is
> equivalent to yours when running interactively.

Why ? I can understand it, when it comes to the backup, etc. But I have
supplied the name and password in the Connection properties in the DTS
package. And I _think_ (will have to check) that it actually empties the
table (first step in the package).

> Once this is fixed, because SQL Agent handles the automated
> tasks, your backups, reorganisations, maintenance plans,
> DTS tasks and anything else you need to automate in
> the environment should have no problems in running
> unattended.

I sure hope it has some kind of domino-effect :-)

> Hope this helps.

Me too. Will get back.
thx
Jim

> "Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
> news:btdvco$1ad$1@.sunsite.dk...
>> Hi,
>>
>> I made a DTS-package and it works when I execute it manually, but
>> when it is run by the SQL Server Agent, it fails.
>>
>> I have use the guide to create a maintenance plan. That doesn't work
>> so good either. It runs Optimizations, but not integrity checks or
>> backups (probably because integrity checks failed).
>>
>> The following from the log file:
> ...[trim]...
>> Log-file endeth here....
>>
>> My "data-sources"/Connections is set up to use sql-server
>> authentication where I enter superuser name+password, not windows
>> authentication.
>>
>> If I browse around using SQL-EM:
>> Under Security - Logins HT-DOMAIN\intrab-sql:
>> Tab General: Grant Access
>> Tab Server Roles: System Administrators
>> Tab Database Access: checkmark at Mbestil, user=dbo; database roles
>> for mbestil = public + db_owner
>>
>> I also have som problems identifying where the package fails. I have
>> given the individual "steps"/"transformations" some pretty good
>> names, but in the log-file it still shows the "old" names.
>>
>> The DTS package empties a table, then fills it by copying data from
>> another table in another database on another server.
>>
>> Anyone with some useful tips ?
>>
>> /jim|||"Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
news:btgjk3$r7u$1@.sunsite.dk...
> mountain man wrote:

> > This OS account needs the appropriate DB access that is
> > equivalent to yours when running interactively.
> Why ? I can understand it, when it comes to the backup, etc.

I believe all tasks run via SQL Agent will try to use the SQL Agent
user account nominated in the services detail.

> But I have
> supplied the name and password in the Connection properties in the DTS
> package. And I _think_ (will have to check) that it actually empties the
> table (first step in the package).

Also check the name of the owner (you, admin?) of the automated package.
The SQL Agent account needs to have equivalent access on the database end.

> > Hope this helps.
> Me too. Will get back.

Good luck with it Jim. Sometimes it is a little fiddly getting SQL Agent
up and running for the first time. Also, there have been a multitude of
threads in here in the past concerning this very issue.

So if the MS doco is not conducive to the solution, try an advance google
through the mssql newsgroups only, for the term "SQL Agent". If all else
fails write back.

Pete Brown
Falls Creek
Oz|||mountain man wrote:
> "Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
> news:btgjk3$r7u$1@.sunsite.dk...
>> mountain man wrote:
>>> This OS account needs the appropriate DB access that is
>>> equivalent to yours when running interactively.
>>
>> Why ? I can understand it, when it comes to the backup, etc.
> I believe all tasks run via SQL Agent will try to use the SQL Agent
> user account nominated in the services detail.
>> But I have
>> supplied the name and password in the Connection properties in the
>> DTS package. And I _think_ (will have to check) that it actually
>> empties the table (first step in the package).
> Also check the name of the owner (you, admin?) of the automated
> package. The SQL Agent account needs to have equivalent access on the
> database end.

User JIM is now system administrator equivalent on the server. I split the
package in 3 parts to avoid any misunderstandings about workflow. All
packages are owned by JIM

Pack1 copies data from a remote server table to local table X.
Pack2 massages data from local table X and copies it to another local table
Y.
Pack3 copies some other data from remote server to another local table.

And then 3 jobs in sql server agent, 1 minute apart (each task takes a
second or 2).
It is Pack2 that fails. "Error string: Cannot open a log file of specified
name. Access is denied."
What does that mean ?

All packages run fine when I execute them manually.

I would have expected it to be either pack1 or 3. But now I think I'm gonna
change pack3 so it reads AND massages data from the remote server table to
local table Y.

It just puzzles me...

/jim|||"Jim Andersen" <jimVK@.officeconsult.dk> wrote in message news:<bu5svi$s7t$1@.sunsite.dk>...
> mountain man wrote:
> > "Jim Andersen" <jimVK@.officeconsult.dk> wrote in message
> > news:btgjk3$r7u$1@.sunsite.dk...
> >> mountain man wrote:
> >>> This OS account needs the appropriate DB access that is
> >>> equivalent to yours when running interactively.
> >>
> >> Why ? I can understand it, when it comes to the backup, etc.
> > I believe all tasks run via SQL Agent will try to use the SQL Agent
> > user account nominated in the services detail.
> >> But I have
> >> supplied the name and password in the Connection properties in the
> >> DTS package. And I _think_ (will have to check) that it actually
> >> empties the table (first step in the package).
> > Also check the name of the owner (you, admin?) of the automated
> > package. The SQL Agent account needs to have equivalent access on the
> > database end.
> User JIM is now system administrator equivalent on the server. I split the
> package in 3 parts to avoid any misunderstandings about workflow. All
> packages are owned by JIM
> Pack1 copies data from a remote server table to local table X.
> Pack2 massages data from local table X and copies it to another local table
> Y.
> Pack3 copies some other data from remote server to another local table.
> And then 3 jobs in sql server agent, 1 minute apart (each task takes a
> second or 2).
> It is Pack2 that fails. "Error string: Cannot open a log file of specified
> name. Access is denied."
> What does that mean ?
> All packages run fine when I execute them manually.
> I would have expected it to be either pack1 or 3. But now I think I'm gonna
> change pack3 so it reads AND massages data from the remote server table to
> local table Y.
> It just puzzles me...
> /jim

You might find this KB article useful - it gives quite a lot of detail
on how scheduled DTS packages are executed, and which security
contexts are relevant:

http://support.microsoft.com/defaul...4&Product=sql2k

Simon

Friday, March 30, 2012

Problems with T-SQL in SQL Job

Problems with T-SQL in SQL Job

Problems with T-SQL in SQL Job

Monday, March 26, 2012

Problems with SQL 2005 64bit sending emails

Problems with SQL 2005 64bit sending emails

Problems with SQL 2005 64bit sending emails

Friday, March 9, 2012

Problems with DTS package. Access denied

Problems using SQL

Blog Archive

About Me