Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: SQL Update (automation)

    Hi all.
    Is there a way to create a Batch file that will run an Update Statement and schedule it to run?
    I've used bcp to extract data to a txt file before, but i'm not sure if an Update can be performed.
    I'm using SQL Server 2008 R2 Express Edition so i don't have Server Agent available.
    Many thanks in advance
    Andy

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    SQLCMD.EXE can run a SQL query from within a Windows .BAT file.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    Thanks for the reply PatP.

    Do you have an example by any chance?

    Thank You
    Andy

  4. #4
    Join Date
    Feb 2008
    Posts
    120
    so far i have:

    sqlcmd -S MYPC\SQLEXPRESS
    Use mydatabase
    select * from loyalty_points
    WHERE (LastUpdated > { fn NOW() } - 15)
    go

    I made a .bat file but when i run it, the cmd windows sits at line 1 waiting for user input!
    Why doesn't it run the full script?

    Thanks
    Andy

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Because you're not telling it how to authenticate to the server. Throw in a couple more switches....

    Trusted connection: -E

    or

    Login name: -U
    Password: -P


    Lastly you can specify the database in the connection instead of using the "USE" statement

    -d mydatabase
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2008
    Posts
    120
    Thanks guys, i now have my auto update working based on certains timescales :-)

    Brill!

    Now I'm trying to send emails using 'msdb.dbo.sp_send_dbmail'
    I have followed the instructions below from another site post.

    1.sysmail_account -> create a default mail account
    2. sysmail_profile -> create a default profile(you will need this with sp_send_dbmail)
    3. sysmail_profileaccount -> add related data to this based on 2 profile id
    4. sysmail_server -> create a mail server from your email account you will be using to send emails.

    I use the code below to send a test email:
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'LoyaltyPoints',
    @recipients = 'myemail@mydomain.co.uk',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;

    But i'm getting this message in the sysmail_log:
    DatabaseMail process is started
    Object reference not set to an instance of an object.
    DatabaseMail process is shutting down

    So it looks like the send email is failing.

    I'm working on my local Windows XP machine.

    Any Ideas please?

    Andy

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Open SSMS and connect to your SQL Instance.
    Under the "Management" node you'll find "Database Mail".
    Right-click and chose "Send Test E-Mail"

    Prove that this part works first
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2008
    Posts
    120
    Hi Gvee

    I don't seem to have Database Mail under management Node.
    I have set DatabaseMail up on the 'Live' server and successfully send two test mails.
    I am working with SQL Express 2008 on both local and live servers.
    It would be useful to get it working on the local server too, if possible.

    I only see:
    Data-tier Applications
    Policy Management
    SQL Server Logs
    Legacy

    Thanks again
    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •