Post reply

update database I don''t know its name till runtime

  • May 14, 2005 at 3:38 am

    #163582

    Hi all,

    I want to insert a row in a table that resides in DB on another server.

    I don't know the database, server, username and password till runtime.

    The stored procedure will take 4 arguments represent the connectivity settings:

    @server,@DB,@UserName,@PWD

    1. HOw can I connect to the server?

    2. How to update the table?

     

  • May 15, 2005 at 10:01 am

    #558927

    Can't be done.  The purpose of having a username and password is to keep people out unless they know the username and password.  Sorry...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 15, 2005 at 10:48 pm

    #558951

    You should look at OPENROWSET in SQL Server Books Online or OPENDATASOURCE.

    OPENROWSET returns a table, so you could issue something like

    insert into OPENROWSET(....)

    values(....)

    OPENDATASOURCE provides you with a connection to a database server, so you could do something like

    insert into OPENDATASOURCE(...).database.dbo.table([..., ..., ...])

    values(...)

    I don't think either of them take variables as parameters (not sure why - annoying though!) - so you will have to use some dynamic SQL to build up the entire SQL statement including the username, password, database & server and then execute the SQL string.

    Look at the EXEC command or the sp_executesql stored proc in BOL.

    Note that I am using BOL for SP3 - you should update your BOL from the microsoft/sql website as there is more up to date info on the above matters in there (not sure what has changed?)

    Good luck!

    Ian

  • May 16, 2005 at 5:24 am

    #558978

    thank you Ian

    this's exactly what I was looking for

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply