Asked  6 Months ago    Answers:  5   Viewed   36 times

I need to know how to make a SQL query run daily using a SQL Server Agent job, with minimum required configuration settings.

 Answers

33
  1. Expand the SQL Server Agent node and right click the Jobs node in SQL Server Agent and select 'New Job'

  2. In the 'New Job' window enter the name of the job and a description on the 'General' tab.

  3. Select 'Steps' on the left hand side of the window and click 'New' at the bottom.

  4. In the 'Steps' window enter a step name and select the database you want the query to run against.

  5. Paste in the T-SQL command you want to run into the Command window and click 'OK'.

  6. Click on the 'Schedule' menu on the left of the New Job window and enter the schedule information (e.g. daily and a time).

  7. Click 'OK' - and that should be it.

(There are of course other options you can add - but I would say that is the bare minimum you need to get a job set up and scheduled)

Tuesday, June 1, 2021
 
BartmanEH
answered 6 Months ago
59

That's the expected result: in SQL Server the = operator ignores trailing spaces when making the comparison.

SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2, , General rules #3) on how to compare strings with spaces. The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them. The padding directly affects the semantics of WHERE and HAVING clause predicates and other Transact-SQL string comparisons. For example, Transact-SQL considers the strings 'abc' and 'abc ' to be equivalent for most comparison operations.

The only exception to this rule is the LIKE predicate. When the right side of a LIKE predicate expression features a value with a trailing space, SQL Server does not pad the two values to the same length before the comparison occurs. Because the purpose of the LIKE predicate, by definition, is to facilitate pattern searches rather than simple string equality tests, this does not violate the section of the ANSI SQL-92 specification mentioned earlier.

Source

Wednesday, June 9, 2021
 
Indranil
answered 6 Months ago
40

While the java.util.Timer used to be a good way to schedule future tasks, it is now preferable1 to instead use the classes in the java.util.concurrent package.

There is a ScheduledExecutorService that is designed specifically to run a command after a delay (or to execute them periodically, but that's not relevant to this question).

It has a schedule(Runnable, long, TimeUnit) method that

Creates and executes a one-shot action that becomes enabled after the given delay.


Using a ScheduledExecutorService you could re-write your program like this:

import java.util.concurrent.*;

public class Scratch {
    private static final ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);
    public static void main(String[] args) {
        System.out.println("Starting one-minute countdown now...");
        ScheduledFuture<?> countdown = scheduler.schedule(new Runnable() {
            @Override
            public void run() {
                // do the thing
                System.out.println("Out of time!");
            }}, 1, TimeUnit.MINUTES);

        while (!countdown.isDone()) {
            try {
                Thread.sleep(1000);
                System.out.println("do other stuff here");
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        scheduler.shutdown();
    }
}

One of the nice things you get by doing things this way is the ScheduledFuture<?> object you get back from calling schedule().

This allows you to get rid of the extra boolean variable, and just check directly whether the job has run.

You can also cancel the scheduled task if you don't want to wait anymore by calling its cancel() method.


1See Java Timer vs ExecutorService? for reasons to avoid using a Timer in favor of an ExecutorService.

Sunday, July 18, 2021
 
Claudio
answered 5 Months ago
10

You can use a MS Access command line switch to run a macro. If you search for "commandline" in Access help, the topic "Startup command-line options" gives you all the commandline switches. The switch for running a macro is x macro.

So, if you write your macro to run whatever you want and have it exit Access when it finishes, you can then create a commandline that will do the trick and put it in a batch file that the Windows Task Scheduler can execute.

However, as I said in a comment above, if you are just running some queries, I'd say it makes more sense to bypass Access entirely and use DAO directly in a scheduled vbScript to execute the queries.

Friday, August 13, 2021
 
xilec
answered 4 Months ago
16

I figured it out thanks to group by clause is not working with Image all I had to do was add alter the image column wording by CASTing it to VARBINARY like this:

SELECT 
    "AM-Martin".dbo.CpCore_Site.Number,"AM-Martin".dbo.CpCore_Site.Latitude,"AM-Martin".dbo.CpCore_Site.Longitude,"AM-Martin".dbo.CpSm_Face.RetiredOn,**CAST("AM-Martin_bin".dbo.CpCore_Image.Bytes as Varbinary)**, "AM-Martin".dbo.CpCore_Site.Name, "AM-Martin".dbo.CpCore_Site.Zipcode
FROM
    "AM-Martin".dbo.CpCore_Site

INNER JOIN "AM-Martin".dbo.CpSm_Face on "AM-Martin".dbo.CpSm_Face.SiteId = "AM-Martin".dbo.CpCore_Site.Oid
INNER JOIN "AM-Martin_bin".dbo.CpCore_Image on "AM-Martin".dbo.CpSm_Face.Oid = "AM-Martin_bin".dbo.CpCore_Image.OwnerId

WHERE
    "AM-Martin".dbo.CpSm_Face.RetiredOn LIKE '%9999%'

     AND "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LA%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%LC%' OR "AM-Martin".dbo.CpCore_Site.Number LIKE N'%BH%'


     AND "AM-Martin".dbo.CpCore_Site.Latitude > 0.0

GROUP BY "AM-Martin".dbo.CpCore_Site.Number,"AM-Martin".dbo.CpCore_Site.Latitude,"AM-Martin".dbo.CpCore_Site.Longitude,"AM-Martin".dbo.CpSm_Face.RetiredOn,**CAST("AM-Martin_bin".dbo.CpCore_Image.Bytes as Varbinary)**, "AM-Martin".dbo.CpCore_Site.Name, "AM-Martin".dbo.CpCore_Site.Zipcode;

I also have to give @Myonara credit for getting me on the right track with the group by suggestion.

Tuesday, August 31, 2021
 
ariel
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share