Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I know this is the Access board, but seeing as there isn't a SQL Server Forum, here goes...

    i have a somewhat complex INSERT INTO that writes a BLOB to SQL Server:

    'INSERT into dbo.ArchivedAccessDBs(AccessDB) SELECT * FROM OPENROWSET(BULK N''\\<remote machine>\DB\sskelton\' + @AccessDB + ''', SINGLE_BLOB) as Document;'

    I tried all morning to change this to a two-part INSERT INTO as in

    INSERT INTO Table (Field1, Field2) VALUES (Field1Value, Field2Value) and it won't work, perhaps because of the sub-SELECT statement. Anyhoo, I gave up on that and thought, "Just get the ID on the last insert and update the column to the required value". so I have this:

    'INSERT into dbo.ArchivedAccessDBs(AccessDB) SELECT * FROM OPENROWSET(BULK N''\\<remote machine>\DB\sskelton\' + @AccessDB + ''', SINGLE_BLOB) as Document SELECT SCOPE_IDENTITY() AS [ID];'

    Which returns the ID value in the results pane as [ID] (I guess it's a virtual column) but I can't figure out how to load that value to the variable (@ID, say) and use it in another SQL call.

    Any ideas?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I can't help you with your question, but this is indeed the correct forum for it - we don't get enough SQL Server questions to merit a separate forum, and Access is the nearest one.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I'm afraid you are over my head on this one. I try to avoid T-SQL like the plague when I can. (Which is another reason that I avoid ADPs.) In theory, you should be able to do what you want in a SPROC, but I'm not proficient enough in the arcane syntax of doing things that I can help. I would suggest you try one of the sites dedicated to SQL Server - the ones I usually try when I do get stuck are www.sqlteam.com, www.sqlcentral.com and www.sqlmonster.com.
    Wendell

Posting Permissions

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