SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    ********* Addict DLG_1's Avatar
    Join Date
    Jun 2000
    Location
    Texas, USA
    Posts
    596
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL Server Stored Procedure to create a table

    Can someone help me in writing a sp that checks for an existing table, if it's there deletes it and regardless of present status inserts 203 records to it? should run at 2am.

  2. #2
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure on what you're platform is, but I'll assume Windows since you're using MS SQL.

    Your best and easiest bet would be to write script using VBScript and save it as '.vbs' file. Then use Windows schedular to run it at 2am.

    Not sure if MS SQL has any kind of built-in scheduling. And even if you wrote it as a SP, you would still need a vbs or bat file to call it that could be scheduled.
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Below is part of the script that was generated by scripting the Customers table on the Northwind db (Action>All tasks>Generate SQL). You'd probably be best following the same method for dropping your own table.

    You'd just need to create an SP with the amended script.

    Code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Customers]
    GO
    You can set the SP to run under as a Job (Management>SQL Server Agent>Jobs). Create a new Step for the Job and set it to type T-SQL and insert the name of the SP in the command box. Then add a new Schedule to run the Job at 2am. Then just start the Job to make sure it's running.

  4. #4
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face

    I stand corrected
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com


Bookmarks

Posting Permissions

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