Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bobbles22
Starting Member
18 Posts |
 Posted - 2009-11-27 : 05:23:17
|
| I have a query I am trying to run through the SQL Agent hourly to remove the system user for our accounts software which fails to remove itself. Very simple query. Should delete the user 'AIS' for any accounts created that day. When I run the query as a normal manual query within the database, it runs fine and removes any occasions of the AIS user being logged in. When runs as a step within the Agent, I get an error. I am fairly new to this level of SQL admin, so any help would be greatly appreciated. The query is below and the error below that. DELETE FROM swrlive.dbo.SYSUserLogin WHERE UserName = 'AIS' AND DateTimeCreated < DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))However, I get the error:MessageExecuted as user: SWRDOMAIN\Administrator. ...(1, 21) Parser: The syntax for 'dbo' is incorrect. at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForSoapFault(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.CheckForError(XmlReader reader, XmlaResult xmlaResult, Boolean throwIfError) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessage(Boolean endReceivalIfException, Boolean readSession, Boolean readNamespaceCompatibility) at Microsoft.AnalysisServices.Xmla.XmlaClient.SendMessageAndReturnResult(String& result, Boolean skipResult) at Microsoft.AnalysisServices.Xmla.XmlaClient.ExecuteStatement(String statement, String& result, Boolean skipResult, String properties, String parameters) at Microsoft.SqlServer.Management.Smo.Olap.SoapClient.ExecuteStatement(String stmt, StatementType stmtType, Boolean withResults, String properties, String parameters, Boolean restrictionListElement, String discoverType, String catalo. The step failed.Many thanks. Bob |
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-11-27 : 08:58:39
|
| On your sql agent job, is this just a step which executes this sql statement above |
 |
|
|
bobbles22
Starting Member
18 Posts |
Posted - 2009-11-27 : 10:00:50
|
| Forgive my ignorance, but why does it say that the syntax is incorrect and error out not running the query? |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2009-11-27 : 10:30:56
|
| Is Agent hitting the right database?Does the Agent have the proper access? Can Agent query the database? Is it a permissions issue?Have you got the right "compatability" level? what is it set at? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-11-28 : 04:06:52
|
| Change the step TYPE in the job step to T-SQL Command. You have it set to Analysis Services Query |
|
|
|
LarryC74
Yak Posting Veteran
94 Posts |
Posted - 2009-12-02 : 12:05:23
|
| Have you parsed the query in SQL server agent to make sure it works? |
|
|
|
|
|
|
|
|
SQL Agent Query Problem