Avatar of mathburg
mathburg
 asked on

macros in sql server

Is it possible to run a macro in sql server to automate a batch of queries?  If so, is it possible to automate the running of these macros?

Thanks in advance for you knowledge and expertise
Microsoft SQL ServerMicrosoft DevelopmentMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
SOLUTION
Anthony Perkins

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mathburg

ASKER
I have got all of my tables running on agent jobs, but some of my queries have temporary tables and won't run on jobs because of  an error.  If you run them manually,  they complete fine, but have some permission errors and agent jobs are unforgiving.  That's why I'm asking around to see if there is another way to automate my queries.
Anthony Perkins

Providing the startup account for the SQL Server Agent service has the right permissions then there is no reason you cannot execute your scripts from there.  Where we should start is for you to tell us the error you are getting.

Alternatively, if you do not want to use the SQL Server Agent, then you will have to use some other scheduling program such as the built in Windows Task Scheduler, so that you can execute you scripts using SQLCmd.exe.
EugeneZ

additionally to the above post about SQL Server Agent that can help you, you can use "home made" or 3 rd party tools :
e.g
JAMS Enterprise Job Scheduler
http://www.jamsscheduler.com/

The sql agent looks like the  best for you.

So, The answer is  "yes", you just need to be more specific about what would you like to do

more
SQL Server Agent Introduction
http://www.youtube.com/watch?v=9j91paAoSVk
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
mathburg

ASKER
Thanks for your help.  I appreciate it.  

The error in agent when I run the query says: Cannot drop the table 'Temp_Loan' because it does not exist or you do not have permission. [SQLSTATE 42S02] [ERROR 3701].  The Step failed.  SQL Severity 11.

Thanks again for your help
mathburg

ASKER
error in my last comment, should say agent job instead of query
Thanks for your help.  I appreciate it.  

The error in agent when I run the AGENT job says: Cannot drop the table 'Temp_Loan' because it does not exist or you do not have permission. [SQLSTATE 42S02] [ERROR 3701].  The Step failed.  SQL Severity 11.

Thanks again for your help
Anthony Perkins

That means that the account used by the SQL Server Agent service does not have the appropriate permissions.  So here are the steps you need to take:
1.  Identify the Windows account used for the SQL Server Agent.
2.  Identify the SQL Server Login using that account.
3.  Find the user in the database that is using this Login
4.  Make sure that if the Login does not have the correct permissions that the user does.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mathburg

ASKER
I don't get it.  I am set up as SysAdmin and Public under Roles, and I automate the transfer of the data from the core everyday through the agent.  Why wouldn't I be able to run these queries.  I can automate some queries, just not the ones with temporary tables.  Maybe I am super confused.  Any further help would be appreciated.

Thanks,
EugeneZ

<The error in agent when I run the query says: Cannot drop the table 'Temp_Loan' because it does not exist or you do not have permission. [SQLSTATE 42S02] [ERROR 3701].  The Step failed.  SQL Severity 11.>
2nd part
you may not run this "drop table" in the right DB.
try to use:

USE YOURDB

DROP TABLE Temp_Loan'


---
Also did you try to run all this sql agent code from SSMS ?
Anthony Perkins

I don't get it.  I am set up as SysAdmin and Public under Roles,
You could be God, that is not relevant, what matters is the Windows account used by the SQL Server Agent service.  Do you need help finding that?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
mathburg

ASKER
I am not sure how to find my set up in Server Agent Service.  Can you help me please.

Thanks again for you patience and help.
mathburg

ASKER
Also did you try to run all this sql agent code from SSMS ?

What is SSMS?  I guess I have a lot of learning to do.
Anthony Perkins

I am not sure how to find my set up in Server Agent Service.  Can you help me please.
1. Execute "Services.msc" from the Start menu or from the command line.
2. Find the SQL Server Agent (MSSQLSERVER) service and double click on it.
3. Click on the LogOn tab
4. Let us know if you are using the "Local System Account" or "This Account".
5. If it is this account that is the Login that requires the appropriate permissions.

What is SSMS?
SQL Server Management Studio
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
mathburg

ASKER
Thank you very much.  I followed the steps below, then went to Security Logins, then the Logon name for mssqlserver.  I right clicked went to Properties, then the User Mapping.  All of the tables are mapped.  The database role is Public.  Is that a problem?  If so, what other roles should it have?

Thanks again
mathburg

ASKER
Right now, I am set up as the db.owner, I am trying that for the mssqlserver logon.  So I set the roles to public and db.owner for user Mapping for mssqlsserver.  I'm  running one of the simple temporary tables as a job right now to see if it works.

Thanks
Anthony Perkins

So you have identified the account used by the SQL Server Agent service.  We will call it SQLServerAgentAccount (for short).

You then went into SSMS and in the Security object confirmed the Login and all the server roles that the SQLServerAgentAccount belongs to.

You then went to the database and found the user mapped to the SQLServerAgentAccount Login.  We will call it SQLServerAgentUser.

You then wen to the Database Security object and you identified that it only belonged to the Public database role.

Finally to triple check that you had not overlooked anything you logged on to SQL Server as the  SQLServerAgentAccount and attempted to run the scripts from the job.

Is that correct?  If so, and if you are still getting an error can you tell us the output to those steps.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
mathburg

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
mathburg

ASKER
I've requested that this question be closed as follows:

Accepted answer: 0 points for mathburg's comment #a39088399

for the following reason:

I appreaciate all of your help, and while I couldn't find a solution from your hlep, I learned a lot about how to look up permissions and find out who's logon is doing what. It works beautifully now.

Thanks again for all your help
Anthony Perkins

Moderator,

I believe if you review the question, I answered the original question in my first comment.  In fact the author ended up using exactly this solution to solve the problem.

Thanks,
Anthony
Anthony Perkins

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.