Job generates login error

I have a package which will run in Management Studio. The package is saved in MSDB. When I try to set up a job, the step errors out with what appears to be a login error. I am specifying windows authentication in the job and also used that when creating the package.

Paul,

the reason is security, probably you created the package with a different account than the sql server agent is running.

Solution:

you can create the package with the agent account,

or in the package -> properties -> security -> protectionlevel -> choose encryptsensitivewithpassword en fill in a password.

greetz

http://www.ns-consulting.be

|||

the package was originally created in management studio using the administrator account. do I need to set up the SQL Agent as a user? Is there a book on SQL Server 2005 which walks you through creating a sample solution from start to finish? I have the Administrator's Consultant and 'Changing the Paradigm' books. SQL Server 2005 is entirely new to me; I was a dba for Sql Server 2000 before this.

Thanks for your help!

Paul

|||

the package was recreated - I changed the encription option to 'Rely on server . . .' and changed the source path from X:\Directory\FileName to \\HostServerName\Directory\FileName.

problem is solved.

Job generates login error

I have a package which will run in Management Studio. The package is saved in MSDB. When I try to set up a job, the step errors out with what appears to be a login error. I am specifying windows authentication in the job and also used that when creating the package.

Paul,

the reason is security, probably you created the package with a different account than the sql server agent is running.

Solution:

you can create the package with the agent account,

or in the package -> properties -> security -> protectionlevel -> choose encryptsensitivewithpassword en fill in a password.

greetz

http://www.ns-consulting.be

|||

the package was originally created in management studio using the administrator account. do I need to set up the SQL Agent as a user? Is there a book on SQL Server 2005 which walks you through creating a sample solution from start to finish? I have the Administrator's Consultant and 'Changing the Paradigm' books. SQL Server 2005 is entirely new to me; I was a dba for Sql Server 2000 before this.

Thanks for your help!

Paul

|||

the package was recreated - I changed the encription option to 'Rely on server . . .' and changed the source path from X:\Directory\FileName to \\HostServerName\Directory\FileName.

problem is solved.

Job generates login error

I have a package which will run in Management Studio. The package is saved in MSDB. When I try to set up a job, the step errors out with what appears to be a login error. I am specifying windows authentication in the job and also used that when creating the package.

Paul,

the reason is security, probably you created the package with a different account than the sql server agent is running.

Solution:

you can create the package with the agent account,

or in the package -> properties -> security -> protectionlevel -> choose encryptsensitivewithpassword en fill in a password.

greetz

http://www.ns-consulting.be

|||

the package was originally created in management studio using the administrator account. do I need to set up the SQL Agent as a user? Is there a book on SQL Server 2005 which walks you through creating a sample solution from start to finish? I have the Administrator's Consultant and 'Changing the Paradigm' books. SQL Server 2005 is entirely new to me; I was a dba for Sql Server 2000 before this.

Thanks for your help!

Paul

|||

the package was recreated - I changed the encription option to 'Rely on server . . .' and changed the source path from X:\Directory\FileName to \\HostServerName\Directory\FileName.

problem is solved.

Job execution logged as an error

I have a special necessity in which I need that the result of failed jobs in
SQL Server Agent be logged as errors in Windows Event Viewer.
Normally they are logged as Warnings.
We developed a solution that uses active scripting for writing the errors in
Event Viewer. However, the policy configured prevents the execution of active
scripting.
Is there any other way of writing the result of failed jobs as errors in
Event Viewer?
I was hoping that there was a configuration that could be done to change the
type of event written in Event Viewer.
ThanksMaybe something using a job step of raiserror with log as a
failure branch in the job might work for you.
-Sue
On Tue, 03 Oct 2006 15:51:32 GMT, "jaimefelipe" <u27450@.uwe>
wrote:
>I have a special necessity in which I need that the result of failed jobs in
>SQL Server Agent be logged as errors in Windows Event Viewer.
>Normally they are logged as Warnings.
>We developed a solution that uses active scripting for writing the errors in
>Event Viewer. However, the policy configured prevents the execution of active
>scripting.
>Is there any other way of writing the result of failed jobs as errors in
>Event Viewer?
>I was hoping that there was a configuration that could be done to change the
>type of event written in Event Viewer.
>Thanks|||Thanks for your reply Sue.
I tried the command sugested but, in order to run it, the job has to be
configured as sysadmin and this is not allowed in my company.
Is there other way?
Sue Hoegemeier wrote:
>Maybe something using a job step of raiserror with log as a
>failure branch in the job might work for you.
>-Sue
>>I have a special necessity in which I need that the result of failed jobs in
>>SQL Server Agent be logged as errors in Windows Event Viewer.
>[quoted text clipped - 12 lines]
>>Thanks|||xp_logevent?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jaimefelipe" <u27450@.uwe> wrote in message news:673a0cf792feb@.uwe...
> Thanks for your reply Sue.
> I tried the command sugested but, in order to run it, the job has to be
> configured as sysadmin and this is not allowed in my company.
> Is there other way?
>
> Sue Hoegemeier wrote:
>>Maybe something using a job step of raiserror with log as a
>>failure branch in the job might work for you.
>>-Sue
>>I have a special necessity in which I need that the result of failed jobs in
>>SQL Server Agent be logged as errors in Windows Event Viewer.
>>[quoted text clipped - 12 lines]
>>Thanks
>|||Tibor Karaszi wrote:
>xp_logevent?
>> Thanks for your reply Sue.
>[quoted text clipped - 13 lines]
>>Thanks
I order to run it, the user also has to be sysyadmin or db_owner of the
master database and there is a policy in my company that does not allow it.
So that does not solve my problem yet, but thanks.|||> I order to run it, the user also has to be sysyadmin or db_owner
Did you try it? Below executes just fine on my 2005 installation:
CREATE LOGIN x WITH PASSWORD = 'y'
CREATE USER x
GRANT EXECUTE ON xp_logevent TO x
EXECUTE AS LOGIN = 'X'
GO
EXEC xp_logevent 50001, 'My error', 'ERROR'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jaimefelipe" <u27450@.uwe> wrote in message news:673aaf26ce58d@.uwe...
> Tibor Karaszi wrote:
>>xp_logevent?
>> Thanks for your reply Sue.
>>[quoted text clipped - 13 lines]
>>Thanks
>
> I order to run it, the user also has to be sysyadmin or db_owner of the
> master database and there is a policy in my company that does not allow it.
> So that does not solve my problem yet, but thanks.
>

Job Agent - Syntax Errors

Hi All,
I'm having a problem with a job on an SQL 2000 Server that is linked to pull
data from an SQL 2005 server. When I enter my infromation into the Job Step
properties and parse the SQL, the system tells me it is fine. When I run
the code through query analyzer, it also works fine as well. However, when
the job is run, I am getting a syntax error on the file name because (I
think) it is in double quotes.
Here is the SQL I am trying to parse:
insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
(vin,productionDateDT,plantcode,UnitReferenceNo,ProductionLotNo,
KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Completed,GhostBody)
select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
+ CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
from bos_data a
where vin COLLATE DATABASE_DEFAULT not in
(select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging")
Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import Staging"
in both areas of the SQL.
Any ideas would be most appreciated.
Thanks!
Brian.My guess is that Agent is executing your code with below setting:
SET QUOTED_IDENTIFIER OFF
Above will interpret double-quotes as string delimiters instead of identifier delimiters. IMO, the
setting should be ON which is ANSI SQL compliant, what QA etc has and apparently even the "parse"
functionality seem to have it set differently.
Anyhow, you can try to use [square brackets] instead of double-quotes. Or add below in beginning of
your TSQL code:
SET QUOTED_IDENTIFIER ON
GO
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.microsoft.com...
> Hi All,
> I'm having a problem with a job on an SQL 2000 Server that is linked to pull
> data from an SQL 2005 server. When I enter my infromation into the Job Step
> properties and parse the SQL, the system tells me it is fine. When I run
> the code through query analyzer, it also works fine as well. However, when
> the job is run, I am getting a syntax error on the file name because (I
> think) it is in double quotes.
> Here is the SQL I am trying to parse:
> insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
> (vin,productionDateDT,plantcode,UnitReferenceNo,ProductionLotNo,
> KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Completed,GhostBody)
> select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
> + CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
> a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
> from bos_data a
> where vin COLLATE DATABASE_DEFAULT not in
> (select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging")
> Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import Staging"
> in both areas of the SQL.
> Any ideas would be most appreciated.
> Thanks!
> Brian.|||That worked.
Thank you very much, Tibor!
/b;
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OoRcLb8nIHA.1036@.TK2MSFTNGP03.phx.gbl...
> My guess is that Agent is executing your code with below setting:
> SET QUOTED_IDENTIFIER OFF
> Above will interpret double-quotes as string delimiters instead of
> identifier delimiters. IMO, the setting should be ON which is ANSI SQL
> compliant, what QA etc has and apparently even the "parse" functionality
> seem to have it set differently.
> Anyhow, you can try to use [square brackets] instead of double-quotes. Or
> add below in beginning of your TSQL code:
> SET QUOTED_IDENTIFIER ON
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
> news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.microsoft.com...
>> Hi All,
>> I'm having a problem with a job on an SQL 2000 Server that is linked to
>> pull
>> data from an SQL 2005 server. When I enter my infromation into the Job
>> Step
>> properties and parse the SQL, the system tells me it is fine. When I run
>> the code through query analyzer, it also works fine as well. However,
>> when
>> the job is run, I am getting a syntax error on the file name because (I
>> think) it is in double quotes.
>> Here is the SQL I am trying to parse:
>> insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
>> (vin,productionDateDT,plantcode,UnitReferenceNo,ProductionLotNo,
>> KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Completed,GhostBody)
>> select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
>> + CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
>> a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
>> from bos_data a
>> where vin COLLATE DATABASE_DEFAULT not in
>> (select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import
>> Staging")
>> Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import
>> Staging"
>> in both areas of the SQL.
>> Any ideas would be most appreciated.
>> Thanks!
>> Brian.
>

Job Agent - Syntax Errors

Hi All,
I'm having a problem with a job on an SQL 2000 Server that is linked to pull
data from an SQL 2005 server. When I enter my infromation into the Job Step
properties and parse the SQL, the system tells me it is fine. When I run
the code through query analyzer, it also works fine as well. However, when
the job is run, I am getting a syntax error on the file name because (I
think) it is in double quotes.
Here is the SQL I am trying to parse:
insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
(vin,productionDateDT,plantcode,UnitReferenceNo,Pr oductionLotNo,
KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Compl eted,GhostBody)
select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
+ CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
from bos_data a
where vin COLLATE DATABASE_DEFAULT not in
(select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging")
Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import Staging"
in both areas of the SQL.
Any ideas would be most appreciated.
Thanks!
Brian.
My guess is that Agent is executing your code with below setting:
SET QUOTED_IDENTIFIER OFF
Above will interpret double-quotes as string delimiters instead of identifier delimiters. IMO, the
setting should be ON which is ANSI SQL compliant, what QA etc has and apparently even the "parse"
functionality seem to have it set differently.
Anyhow, you can try to use [square brackets] instead of double-quotes. Or add below in beginning of
your TSQL code:
SET QUOTED_IDENTIFIER ON
GO
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.microsoft.com...
> Hi All,
> I'm having a problem with a job on an SQL 2000 Server that is linked to pull
> data from an SQL 2005 server. When I enter my infromation into the Job Step
> properties and parse the SQL, the system tells me it is fine. When I run
> the code through query analyzer, it also works fine as well. However, when
> the job is run, I am getting a syntax error on the file name because (I
> think) it is in double quotes.
> Here is the SQL I am trying to parse:
> insert into starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging"
> (vin,productionDateDT,plantcode,UnitReferenceNo,Pr oductionLotNo,
> KDLotNo,LotPosition,MTOC,ModelGrade,Returned,Compl eted,GhostBody)
> select a.vin, (CONVERT(varchar(10), a.pdate ,101) + ' '
> + CONVERT(varchar(10), a.ptime ,108)), a.plantcode, a.lrnum, a.prod_lot,
> a.kd_lot, a.lot_position, a.mtoc, 'N/A', 'N/A', 'N/A','N/A'
> from bos_data a
> where vin COLLATE DATABASE_DEFAULT not in
> (select vin from starscream.NAPROD_40.dbo."SPS 4_0$AFON DTS Import Staging")
> Again, it fails with a syntax error on the "SPS 4_0$AFON DTS Import Staging"
> in both areas of the SQL.
> Any ideas would be most appreciated.
> Thanks!
> Brian.
|||That worked.
Thank you very much, Tibor!
/b;
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OoRcLb8nIHA.1036@.TK2MSFTNGP03.phx.gbl...
> My guess is that Agent is executing your code with below setting:
> SET QUOTED_IDENTIFIER OFF
> Above will interpret double-quotes as string delimiters instead of
> identifier delimiters. IMO, the setting should be ON which is ANSI SQL
> compliant, what QA etc has and apparently even the "parse" functionality
> seem to have it set differently.
> Anyhow, you can try to use [square brackets] instead of double-quotes. Or
> add below in beginning of your TSQL code:
> SET QUOTED_IDENTIFIER ON
> GO
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Brian Piotrowski" <n0spam-bpiotrowski@.simcoeparts.com> wrote in message
> news:A83BACC9-B41F-470F-8F29-8B2B9A6BAB2B@.microsoft.com...
>