Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: Creating Stored procedures

    HI

    I am trying to create a stored procedure which inputs data from an access db to an SQL DB. My code:

    CREATE PROCEDURE s2_comms
    @DBpath varchar(50)
    AS
    EXEC sp_addlinkedserver 'TempS2commsServer', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', @DBpath
    CREATE DATABASE S2comms
    USE S2comms
    select * into Codes from OPENQUERY(TempS2commsServer, 'SELECT * FROM Codes')
    select * into comms from OPENQUERY(TempS2commsServer, 'SELECT * FROM comms')
    EXEC sp_dropserver 'TempS2commsServer'

    But it tells me:
    a USE database statement is not allowed in a procedure or trigger.

    i am new to sql and cant figure out how to get round this, any ideas?
    thanks for any help.

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    ....

    well if you are using SQL Server, just create a DTS package to do this, much easier. Then you can schedule it to run when ever.

Posting Permissions

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