Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: SELECT a field from another database (on same server) from stored proc

    Hi

    Is it possible to SELECT a field from another database which is on the same server from within a stored procedure?

    thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * FROM OtherDB.dbo.myTable99
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    easy as that eh!

    cheers brett

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    yup, easy as that

    I don't know the internals, but I think that the only performance hit is that it has to figure out that stats on the other db...however, I don't know why in a compiled sproc couldn't get the plan from the catalog at the compilation time...unless the hit is that it won't know updated stsats...

    Then again, the same thing would happen anyway in the db your in

    I gotta look it up in Kalen's book
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    hmmm, i seem to have hit a problem lol! I'm getting the following error:

    "Msg 7202, Level 11, State 2, Procedure add_venue, Line 106
    Could not find server 'DB1' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
    "

    ....seems to be asking for my server name (which i think is DSVR002386\SQLEXPRESS). Tried putting that in front but no joy

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Are you sure the database is on the same server and in the same instance?

    If it's in a different instance you are going to need a link server and you are going to have to make sure the login in the other server is there for the user that is executing the sproc...then you need to use a 4 part name...and the performance then will be definetly worse

    SELECT * FROM linkedservername.otherdb.dbo.table
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sounds like you have a rogue "." in the definition. Can you post the complete query?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It does indeed. But just to elbow myself to the front of the queue in case things get to this point.... if it is on another server and the instance is not named after the server (a la your example) you need to enclose your instance name in square brackets comme ca.... un petit puh:

    Code:
    FROM [DSVR002386\SQLEXPRESS].myDb.mySchema.myTable
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Hi

    I have it setup on a dedicated server which i have full control over. There are two database which i can clearly see under the "Databases" tab in Sql management studio.

    Here is my mssql:

    SET @newAdminId = (
    SELECT admin_id FROM DB1.dbo.ADMIN_LIST AS t1, DB1.dbo.ADMIN_LIST.aspnet_Users As t2
    WHERE t1.aspnet_Users_UserID = t2.UserId AND t2.UserName = @admin_id)

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Your problem is in this table:

    DB1.dbo.ADMIN_LIST.aspnet_Users

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well what's the name of the 2nd table and what's ADMIN_LIST

    And start coding in ANSI

    Code:
    SET @newAdminId = (
    			SELECT admin_id 
    			  FROM DB1.dbo.ADMIN_LIST AS t1
    		    INNER JOIN DB1.dbo.ADMIN_LIST.aspnet_Users As t2 
    			    ON t1.aspnet_Users_UserID 	= t2.UserId 
    			   AND t2.UserName 		= @admin_id)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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