登入
Microsoft.com
 
Microsoft
 
 
 

none
Accessing tables in a sqlserver 2005 database from another server in the network RRS feed

 > 

  • 問題

  • Question
    登入以投票
    0
    登入以投票

     

    hi..

    I have a database Db1 in one SQLServer -'server1'   and i want to  insert all data in the table table1 in the database Db1 into a table table2 located in  another database Db2 in another SQLServer  -'server2' .I have used LinkedServer Procedure to create a linked server of  server2 inorder to access table2, but i did not get access to table2 instead i got access to

    stored procedures in the database Db2.

     

    I have used the following code:

    EXEC sp_addlinkedserver
       'Db2',
       N'SQL Server'

    EXEC sp_addlinkedsrvlogin 'Db2','false','null','sa','passwd'

     

    select * from Db2..table2

     

    Somebody please help me .......

     

    2008年9月30日 上午 08:50
    |

解答

  • Question
    登入以投票
    0
    登入以投票
    Hi Bijithbal

    Have you try to run

    SELECT *
    FROM Db2.Databasename.dbo.table2 on your database server 1?

    Could you retrieve any data back?
    If not, have you receive any error message?
    If you have receive some error message could you post it here any let us know?

    I have not does it through LinkedServer Procedure

    I create linked server via SQL Server management Studio 2005

    Do you use SQL Server management Studio?
    If so, maybe you could try to create the linked server again by using Management Studio.

    Here it is the step:
    1) Click on the database server where you want to add Linked Server to.
    2) Click on the "+" sign of the "Server Object"
    3) Right click on "Linked Servers" and Select "New linked Server"
       Then fill in the details E.g. Provide a linked server name ...etc
    If your another database server is SQL Server
    -->On the Provider drop down list you can choose "Microsoft OLE DB Provider for SQL Server"
    -->On the DataSource enter the database server name
    then you can go to the Security tab to enter the security information
    After that you can go to the server option.
    -->Make sure data access option is true.
    4) Then click on "OK"

    5) You will be able to see the New Linked Server name under the "Linked Server" Folder
    6) You should be able to see the database catelog as well as the database table.

    E.G.
    If you create the Linked Server name as "LS_DB2"
    Then you can retrieve data by using

    SELECT *
    FROM
    LS_DB2.[DatabaseName].dbo.Table2

    You can then click on table2 and see if you can retrieve data from here.

    Hope this help.



    2008年9月30日 上午 10:13
    |