Tansact-sql Not Working From Server Agent

Mar 21, 2008



I will try to explain this the best I can and even include the code. I have the following:


DECLARE @x XML

SELECT @x =cast(bulkcolumn as XML)

FROM OPENROWSET(BULK 'C:databasexmldtdyahoostore3.xml', SINGLE_BLOB) AS x

USE yahoostore

-- create a table variable

CREATE TABLE PRODUCTS (ProductID VARCHAR(255), Code varchar(255), ProductDesc VARCHAR(255), Url VARCHAR(255),

Orderable varchar(255), Taxable varchar(255), HTMLPath varchar(255),

Caption varchar(255), Thumb varchar(255), Picture varchar(255),

BasePrice varchar(255), LocalizedBasePrice varchar(255), OriginalPrice varchar(255), LocalizedOriginalPrice varchar(255),

SalePrice varchar(255), LocalizedSalePrice varchar(255),

Availability varchar(255), Weight varchar(255), OptionLists varchar(255))



INSERT INTO Products (ProductID, Code, ProductDesc, Url, Orderable, Taxable, HTMLPath,

Caption, Thumb, Picture, BasePrice, LocalizedBasePrice, OriginalPrice, LocalizedOriginalPrice,

SalePrice, LocalizedSalePrice, Availability, Weight, OptionLists)



SELECT

x.value('@Id[1]','varchar(255)') AS id,

x.value('Code[1]', 'VARCHAR(255)') AS code,

x.value('Description[1]','VARCHAR(255)') as description,

x.value('Url[1]','VARCHAR(255)') as url,

x.value('Orderable[1]', 'VARCHAR(255)') AS orderable,

x.value('Taxable[1]', 'VARCHAR(255)') AS taxable,

x.value('Path[1]', 'VARCHAR(255)') as htmlpath,

x.value('Caption[1]', 'VARCHAR(255)') as caption,

x.value('Thumb[1]', 'VARCHAR(255)') AS thumb,

x.value('Picture[1]', 'VARCHAR(255)') AS picture,

x.value('(Pricing/BasePrice)[1]', 'MONEY') AS baseprice,

x.value('(Pricing/LocalizedBasePrice)[1]', 'MONEY') AS localizedbaseprice,

x.value('(Pricing/OriginalPrice)[1]', 'MONEY') AS originalprice,

x.value('(Pricing/LocalizedOrignalPrice)[1]', 'MONEY') AS localizedoriginalprice,

x.value('(Pricing/SalePrice)[1]', 'MONEY') AS saleprice,

x.value('(Pricing/LocalizedSalePrice)[1]', 'MONEY') AS localizedsaleprice,

x.value('Availability[1]', 'VARCHAR(255)') AS availability,

x.value('Weight[1]', 'VARCHAR(255)') AS weight,

x.value('OptionLists[1]', 'VARCHAR(255)') as optionlist





FROM @x.nodes('/StoreExport/Products/Product') s(x)

--SELECT * FROM Products


This workings fine, but when I add it to the server agent to run nightly it fails.

The only thing I see for the error is:
Message
Executed as user: NT AUTHORITYSYSTEM. INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.

Can some one explain what happened and how to fix this
Dee

View 4 Replies


ADVERTISEMENT

SQL Server Agent: Job Not Working

May 7, 2007

I created a job using the SQL Server Agent of type Operating System Command (CmdExec). The command was to open a web page that will execute a series of batch programs. I tried running the job I created several times and according to the history of the job, it was successful. However, I checked the supposed changes to some records in the database and no changes were done. I also checked the trace log of the web page but there was no information logged.

I'm convinced that the problem is not on the web page since when I tried manually opening the web page through the browser, the changes were done successfully.

For reference, below is the script of the job:

USE [msdb]
GO
/****** Object: Job [try] Script Date: 05/05/2007 11:06:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 05/05/2007 11:06:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'try',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=3,
@notify_level_netsend=3,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'AZEUSPHazuser',
@notify_email_operator_name=N'AZEUSPHazuser',
@notify_netsend_operator_name=N'AZEUSPHazuser', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [try] Script Date: 08/07/2006 11:06:59 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'try',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'CmdExec',
@command=N'start http://shikra/QSCO/pages/bat/scheduler1.aspx',
@flags=4,
@proxy_name=N'jco2'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Thanks in advance!

Regards.

View 6 Replies View Related

SQL Server Compact Server Agent Is Not Working In IIS6

Mar 28, 2008

SQL Server Compact Server Agent is not working in IIS6, when i put de link http://myserver/myweb/sqlcesa35.dll?diag i recive de message

Service UnavailableThe problem is in windows server 2003 IIS6

please a need help....

View 2 Replies View Related

Tansact SQL - Linefeed Characters In Varchar Columns

May 22, 2008



This is obviously a radical idea but some actually DO want to store linefeeds in varchar columns.

In MySQL I can escape difficult characters for example

INSERT INTO sometable(address) VALUES("23 SomeRoad
SomeTown
SomeCounty");

Does anyone know how to do this in Transact SQL?

View 7 Replies View Related

Working Query Refuses To Run In SQL Agent Job

Nov 16, 2007

Hi, I am in need of help,

I have a couple of queries that i run on my server but i need to automate them now. I have created a new job in the sql agent jobs and set up my steps accordingly.

My queries all run in a query window without trouble and they also parse in the command window of the sql agen job-step screen. When I run them manually, it fails with the first query moaning about my variables etc etc.


Is there some sort of limitation that i am not aware of or something?

View 4 Replies View Related

SQL Agent Not Working Correctly, Returns 14266

Jun 2, 2004

exec msdb..sp_help_jobhistory @mode = 'SUMMARY'
Server: Msg 14266, Level 16, State 1, Procedure sp_help_jobhistory, Line 79
The specified '@mode' is invalid (valid values are: SUMMARY, FULL, SEM).

When I double-click to open a job in EM, I get the same error message, but with a different content:

Error 14266: The specified '@class' is invalid (valid values are: JOB, ALERT, OPERATOR).

Any suggestions.
Thanks Lennart

View 1 Replies View Related

Execute Sql Server Agent Job Task - Job Immediately Returns Success... However Agent Job Is Still Running???

Nov 30, 2006

when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds



However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.



Thanks,

Larry

View 1 Replies View Related

SQL Server Agent Could Not Access Replication Agent

Feb 19, 2007

We just moved source server to newer, bigger box ... Windows 2003 and Active Directory ... Snapshot agent worked but distribution failed ... Same login as on older machine, login is sysadm, used DCOMCNFG to allow ability to launch process ... What are we missing?

View 4 Replies View Related

Snapshot Agent &&amp; Log Reader Agent Will Not Start.

Nov 30, 2006

Hi All,

I would appreciate any help here at all. I am pulling my hair out!

I am unable to start the snapshot agent or log reader agent from within SQL management studio. it fails on executing step 2 with unknown username or bad password.

I have checked all account details and they are correctly using my domain admin account. I can connect to SQL using teh same account and it also has sysadmin permissions.

If i copy the step 2 paramters and start from the cmd prompt (again logged in using the same domain account) they both start fine.



Any ideas would be gratefully received.

View 1 Replies View Related

Cannot Start Log Reader Agent And Snapshot Agent

May 18, 2007

I am testing peer to peer replication in our environment. I simulated a three node peer to peer topology and a local distributor.

For some wierd reason I cannot get the Log Reader Agent and snapshot agent to start. The domain account under which SQL Server Agent runs has administrator previlage on the box. I also use a domain account for SQL Server Service. (none of the passwords changed).

This is the error I am getting - "Executed as user: abc. A required privilege is not held by the client. The step failed"

Any ideas???





Also this domain account is a member of



SQLServer2005MSSQLUsers$ServerName$MSSQLSERVER

SQLServer2005MSAgentUser$ServerName$MSSQLSERVER


View 3 Replies View Related

Sql Agent Not Starting Can We Reinstall Just Agent ?

Feb 7, 2002

SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).

Is any has the same problem ?

View 6 Replies View Related

SQL Server 2000 SQL Agent Error [393] Waiting For SQL Server To Recover Databases

Jan 30, 2007

While I was out of the office the Lan Team moved one of my SQL Server2000 servers to a new network domain. Since then the maintenance jobhas not ran.The error log for the SQL Agents has the message listed in the subjectline. I have not found any useful articles on the MS SQL Serversite. Anyone know what might be wrong and how to fix it.HTH -- Mark D Powell --

View 4 Replies View Related

SQL Server,SQL Server Agent,SQL Analysis Resources Failing In SQL 2005 Cluster

Aug 17, 2007

Hi.
I have installed an SQL 2005 Failover Cluster on a Two Node Active Passive Windows 2003 Cluster.If i am trying to failover from the Active node to the passive Node,The Sql Server,Sql Server Agent and SQL Analysis resources fail,However if i reset the passowrd in the services tab of the above three services on the pasive node,the resources come online.
Below is the brief of my setup
1.I have two Active directory domain controllers running Windows 2003 R2 Standard edition with SP2.
2.i have installed a Windows 2003 Two Node Active Passive Cluster as NODE1 and NODE2.
3.The Domain account used to install WIndows 2003 A/P Cluster is Clusteradmin.This account is the member of Administartors on the Domain as well as the Local Admin on NODE1 and NODE2.
4.SQL 2005 with SP2 is installed on both the Nodes as SQL 2005 Failover Cluster.The account used to install SQL 2005 is sqadmin.This account is the member of Administartors on the domain and the member of Local Admins on NODE1 and NODE2.
5.SLQ 2005 has four domain groups for 4 SQL Services.The Services are SQL Server,SQL Server Agent,SLQ Anaylisis Server and Full text Search.
6.Each of these servcie has a seperate service account created for them.All these service accounts areb the members of domain admin and the member of Local Admin on NODE1 and NODE2.
7.Each of these servcies is running under these servcie accounts in the Servcies tab in NODE1 and NODE2.
8.If i fail the resources from NODE1 to NODE2 ,The SQL Server,SQL Server Agent and SQL Anaylisis resources are failing.on going to the service tab of NODE 2 I reset the password for these services,the services come online.
10.The Cluster resources and MSDTC Resources are Failing over successfully.They are coming online successfully.
11,I have a problem with the SQL Server Resources,even if i failback to NODE1 from NODE2,the same resources are failing again.

Plz Advice.
Regards
Khalid

View 1 Replies View Related

Some Things Not Working In 2005 And Working In 2000

Mar 3, 2006

hi

I had a view in which I did something like this
isnull(fld,val) as 'alias'

when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005.
When I change the query to fld as 'alias' then it works ok in sql 2005 .
why ?? I still have sql 2000 (8.0) compatability.

Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???

any clues or answers ?? it is some configuration issue ?

Thanks in advance.

View 5 Replies View Related

ReadWriteDeleteMessageFile FAILURE With SQL Server Mobile Server Agent Diagnostics

Jul 31, 2007

Hi all.

Is there a difference with sql replication between sql server 2005 64bit and sql server 2005 32bit?
Both are on a Windows 2003 server. One is 32 bit and the other one is 64 bit.
The first time I set up sql replication (test environment), it was on a 32 bit sql server. This worked fine.
The second time I wanted to set up replication (live environment), it was on a 64 bit sql server. This didn't worked fine.


I can't call the sqlcesa30.dll file on the 64bit server with IIS. So I called the dll via a remote IIS server.
This worked but gave me the following error when calling the following URL:
http://domain/PDASYNC/sqlcesa30.dll?diag

SQL Server Mobile Server Agent Diagnostics
2007/07/31 14:26:55


General Information


Item
Value

Server Name
domain

URL
/PDASYNC/sqlcesa30.dll

Authentication Type
Anonymous

Server Port
80

HTTPS
off

Server Software
Microsoft-IIS/6.0

Replication
Allowed

RDA
Allowed

Logging Level
0


Impersonation and Access Tests


Action
Status
ErrorCode

Impersonate User
SUCCESS
0x0

ReadWriteDeleteMessageFile
FAILURE
80070003


SQL Server Mobile Modules Test


Module
Status
ErrorCode
Version

SQLCERP30.DLL
SUCCESS
0x0
3.0.5207.0

SQLCESA30.DLL
SUCCESS
0x0
Unknown


Reconciler Test


Reconciler
Status
ErrorCode

9.0 Database Reconciler
SUCCESS
0x0

8.0 Database Reconciler
FAILURE
0x8007007E


SQL Server Module Versions


Module
Version

sqloledb.dll
2000.86.1830.0

9.0 replrec.dll
2005.90.2047.0

9.0 replprov.dll
2005.90.2047.0

9.0 msgprox.dll
2005.90.2047.0

8.0 replrec.dll
2000.80.760.0

8.0 replprov.dll
2000.80.760.0

8.0 msgprox.dll
2000.80.760.0


After seeing this I looked up what ReadWriteDeleteMessageFile could mean. It appeared to be some NTFS acces problem. But when I checked the security settings, it all seemed to be ok.

Has anyone any idea what else to check? Or is sure that I must have done something wrong?

Thanks in advance,
Arjan

View 3 Replies View Related

Ssis Package Can't Connect To Pop Server Over The Internet From Sql Server Agent

Dec 20, 2007

Sql server agent is running under a domain account that is a member of administrators and domain users amongst others, and the package is executed as the service account. Connecting to servers on the same domain works and when I run it from the msdb package store in ssis (ssis runs under the network service account ...) it will connect to the pop server as well. Permissions, fiddly proxies .. the answer's out there somewhere

View 3 Replies View Related

DB Engine :: Failure Of Server Agent Service In Server 2014

Oct 15, 2015

The SQL Server Agent (MSSQLSERVER) service on server started and then stopped. If i Manually start the services also SQL Server Agent services are stopping automatically.Though I selected Auto Restart SQL Server Agent if it stops unexpectedly, The services are stopping automatically.TCP/IP protocol was enabled for port 1433.Are there any setup I missed?

View 5 Replies View Related

What To Do If Do Not Have Sql Server Job Agent?

Dec 18, 2006

Hello everyone !!!
I need to do a process that run every day to update some data, but my sql server version (express 2005) does not have job agent service.  Does anyone can give me a suggestion of what to do?
 Thank you!
 

View 2 Replies View Related

SQL Server Agent

Mar 14, 2006

Hi there,
was wondering if any can tell me why the following jobs keeps failing:
     INSERT INTO TblActivityBookingArchive     SELECT     *     FROM         TblActivityBooking     WHERE     (expiryDate <= GETDATE())
it retuens that it is successful when there is no data to archive from TblActivityBooking, it's when there is data to archive that it returns that the job has failed.
Hope you can help
Thanks
Mel

View 4 Replies View Related

Sql Server Agent

Jul 29, 2002

I am unable to start SQL Server Agent. When I try, I get this message: "An Error 1053 - (The service did not respond to the stort or control request in a timely fashion) occurred while performing this service operation on the SqlServerAgent service."

View 1 Replies View Related

Using SQL Server Agent

Aug 7, 2002

Is it possible to schedule a job to automatically send e-mails based on criteria in a database table, maybe using CDONTS? I don't want to use SQLMail, since my administrator has not set that up. Also, I don't want to write the code in an ASP page for the following reason: If the page is not being viewed at the time I want the e-mail to be sent, it won't work. Besides, it's not efficient to have to read this code for no reason every time a page is loaded in the hopes someone loads it at the correct time. Any suggestions?

View 1 Replies View Related

SQL Server Agent

Mar 20, 2001

We received the error messages below (in the SQLAgent Log) when trying to execute a SQL Job manually. These errors do not appear if the job is executed automatically, and consequently runs smoothly. Only when we try to kick off the job manually does it fail and report these errors.


3/20/01 4:27:16 AM - ! [000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: A required privilege is not held by the client)
3/20/01 4:27:16 AM - ! [000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: A required privilege is not held by the client)

If anyone has any insight into this issue it would be greatly appreciated.

Thank you,

Nick

View 3 Replies View Related

Sql Server Agent

Aug 12, 2000

Hi

My sql server agent is not starting and whenever I try to start a job it gives me an error saying that job csnnot be started as sql server agent is not running...and it open a black window(with title bar d:mssql7insqlagent.exe)..can anyone tell me the reason for that and how do I get around this problem.

Thanks
VENU

View 1 Replies View Related

SQL Server Agent

Nov 15, 2000

Attempted to start the SQL Server Agent in Services and got this error message: 'Error 2140: An Internal Windows NT error occured'. Does anyone know what I need to do or fix? Any help would be greatly appreciated. Thanks.

View 1 Replies View Related

SQL Server Agent

Oct 19, 1999

Need immediate help trying to figure this message out. We are having immediate problems trying to start the SQLServer Agent, the service that runs the Scheduled Tasks and needs to be reset if NT connectivity has been interrupted to keep SQL Mail on-line, is not responding and generating the following message. All DBAs that might know something about this problem, would you please respond immediately. We can't have our SQL Server Agent down and not responding.

[000] Password verification of the 'SQLAgentCmdExec' proxy account failed (reason: Logon failure: the user has not been granted the requested logon type at this computer)

Thanks for all your help and solutions in getting to an answer.
Sincerely,
Daimon Russell

View 2 Replies View Related

SQL Server Agent

Oct 23, 2002

In SQL Server 2000, how can I set up SQL Server Agent to automatically start up when SQL Server starts up ? I do NOT want these to start up when the OS starts (so setting them to automatic in Control Panel Services is no good). When I shut down SQL Server the SQL Server Agent does automatically shut down too but I cannot get it to start up.

View 3 Replies View Related

SQL Server Agent

May 9, 2005

I am running 3 instances of SQL2000 SP3a on a windows 2000 server. Over the weekend, the server was restarted due to security patches being applied. All 3 instances are setup the same (Automatically start SQL Server Agent when restarting server). The 2 named instances started the agent fine but the default instance did not. There are no errors in the log. When I started it manually this morning, it started fine with no errors. Does anyone have any idea why the agent would not have started automatically?

Thanks,
Ken

View 1 Replies View Related

SQL Server Agent

Jun 10, 2005

Hi all,

I have a newly created database, and when I try to start SQL Server Agent I get the following error:

An error 5 - (Access is denied) occurred while performing this service operation on the SQLServerAgent service.

What causes this error?

View 1 Replies View Related

SQL Server Agent

Sep 22, 2006

Hi All,

I need to take the databases offline and stop the SQL Server Agent. If I stop the SQL Server Agent first, will I be able to take the databases offline?

Thanks.

View 6 Replies View Related

How To Use SQL Server Agent

Jul 17, 2004

I want to sendmail after finishing job by using SQl Server Agent.When I try to change SQL Server Agent start up account
from new account in windowsxp system , I give new account a right of
'Log oOn as a Service Right ',but I experience the error the
following.
Error 22042 xp_setsqlsecurity() returned error-
2147023564 'No mapping between account name and security
IDs was done'

Could you help to resolve this?

Thanks

View 1 Replies View Related

SQL Server Agent

Apr 15, 2008

Hi Everyone,

What permissions one should have to veiw SQL Server Agent on server to which he logged in ?

Can someone help me in this ?



Thanks,
ServerTeam

View 3 Replies View Related

SQL Server Agent

Apr 17, 2008

Hi,
I notice one of my database server icon different than others. The green arrow show empty. Does anyone know what it mean?

Kindly advice.


Regards,
Jimmy Liew

View 5 Replies View Related

SQL Server Job Agent !

Apr 21, 2008

Hi pals,

I am using sql server 2005.

Is there any means to check/see the log if i add a new job category has been added or deleted .

I need this for Auditing Purpose.

Hint:
-----

Management Studio-->Jobs-->Right Click-->Manage Job Categories-->Add/Delete.

Any Thoughts ???

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved