myITforum and Windows IT Pro Forums

 How to Connect two different instances of SQL SERVER

Author Message
ashah

  • Total Posts : 1
  • Scores: 0
  • Reward points : 0
  • Joined: 4/29/2004
  • Status: offline
How to Connect two different instances of SQL SERVER Thursday, April 29, 2004 1:13 AM (permalink)
0
How to Connect two different instances of SQL SERVER for data migration or one thing is linked server is there is another way to do this? if not then how to create linked server through code(script)?
 
#1
    skuvshinkova

    • Total Posts : 560
    • Scores: 125
    • Reward points : 0
    • Joined: 8/29/2002
    • Location: Russia
    • Status: offline
    RE: How to Connect two different instances of SQL SERVER Thursday, April 29, 2004 1:58 AM (permalink)
    0

    How to Connect two different instances of SQL SERVER for data migration or one thing is linked server is there is another way to do this?


    1. Using BACKUP/RESTORE T-SQL commands;
    2. Using BCP and BULK INSERT
    3. Using Distributed Queries (Linked Servers)
    4. Using DTS Import/Export Wizard
    5. Using SQL Server Replication

    You decide, depending of your particular requirements ...


    if not then how to create linked server through code(script)?


    sp_addlinkedserver
    Creates a linked server, which allows access to distributed, heterogeneous queries against OLE DB data sources. After creating a linked server with sp_addlinkedserver, this server can then execute distributed queries. If the linked server is defined as Microsoft® SQL Server™, remote stored procedures can be executed.

    Syntax
    sp_addlinkedserver [ @server = ] ' server'
    [ , [ @srvproduct = ] ' product_name' ]
    [ , [ @provider = ] ' provider_name' ]
    [ , [ @datasrc = ] ' data_source' ]
    [ , [ @location = ] ' location' ]
    [ , [ @provstr = ] ' provider_string' ]
    [ , [ @catalog = ] ' catalog' ]

    Arguments
    [ @server = ] ' server'

    Is the local name of the linked server to create. server is sysname, with no default.

    With multiple instances of SQL Server, server may be servername\instancename. The linked server then may be referenced as the data source for

    SELECT *FROM [servername\instancename.]pubs.dbo.authors.

    If data_source is not specified, server is the actual name of the instance.

    [ @srvproduct = ] ' product_name'

    Is the product name of the OLE DB data source to add as a linked server. product_name is nvarchar(128), with a default of NULL. If SQL Server, provider_name, data_source, location, provider_string, and catalog do not need to be specified.

    [ @provider = ] ' provider_name'

    Is the unique programmatic identifier (PROGID) of the OLE DB provider corresponding to this data source. provider_name must be unique for the specified OLE DB provider installed on the current computer. provider_name is nvarchar(128), with a default of NULL. The OLE DB provider is expected to be registered with the given PROGID in the registry.

    [ @datasrc = ] ' data_source'

    Is the name of the data source as interpreted by the OLE DB provider. data_source is nvarchar(4000), with a default of NULL. data_source is passed as the DBPROP_INIT_DATASOURCE property to initialize the OLE DB provider.

    When the linked server is created against the SQL Server OLE DB provider, data_source can be specified in the form of servername\instancename, which can be used to connect to a specific instance of SQL Server running on the specified computer. servername is the name of the computer on which SQL Server is running, and instancename is the name of the specific SQL Server instance to which the user will be connected.

    [ @location = ] ' location'

    Is the location of the database as interpreted by the OLE DB provider. location is nvarchar(4000), with a default of NULL. location is passed as the DBPROP_INIT_LOCATION property to initialize the OLE DB provider.

    [ @provstr = ] ' provider_string'

    Is the OLE DB provider-specific connection string that identifies a unique data source. provider_string is nvarchar(4000), with a default of NULL. provstr is passed as the DBPROP_INIT_PROVIDERSTRING property to initialize the OLE DB provider.

    When the linked server is created against the SQL Server OLE DB provider, the instance can be specified using the SERVER keyword as SERVER=servername\instancename to specify a specific instance of SQL Server. servername is the name of the computer on which SQL Server is running, and instancename is the name of the specific SQL Server instance to which the user will be connected.

    [ @catalog = ] ' catalog'

    Is the catalog to be used when making a connection to the OLE DB provider. catalog is sysname, with a default of NULL. catalog is passed as the DBPROP_INIT_CATALOG property to initialize the OLE DB provider.

    Also, pay attention on this stored procedure:

    sp_addlinkedsrvlogin
    Creates or updates a mapping between logins on the local instance of Microsoft® SQL Server™ and remote logins on the linked server.

    Syntax
    sp_addlinkedsrvlogin [ @rmtsrvname = ] ' rmtsrvname'
    [ , [ @useself = ] ' useself' ]
    [ , [ @locallogin = ] ' locallogin' ]
    [ , [ @rmtuser = ] ' rmtuser' ]
    [ , [ @rmtpassword = ] ' rmtpassword' ]

    Arguments
    [@rmtsrvname =] ' rmtsrvname'

    Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

    [@useself =] ' useself'

    Determines the name of the login used to connect to the remote server. useself is varchar(8), with a default of TRUE. A value of true specifies that SQL Server authenticated logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. false specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server. true for useself is invalid for a Windows NT authenticated login unless the Microsoft Windows NT® environment supports security account delegation and the provider supports Windows Authentication (in which case creating a mapping with a value of true is no longer required but still valid).

    [@locallogin =] ' locallogin'

    Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows NT user. The Windows NT user must have been granted access to SQL Server either directly, or through its membership in a Windows NT group granted access.

    [@rmtuser =] ' rmtuser'

    Is the username used to connect to rmtsrvname when useself is false. rmtuser is sysname, with a default of NULL.

    [@rmtpassword =] ' rmtpassword'

    Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.
    < Message edited by skuvshinkova -- 4/29/2004 2:02:52 AM >
    Svetlana Kuvshinkova
     
    #2
      Guest
      RE: How to Connect two different instances of SQL SERVER Monday, May 17, 2004 12:19 AM (permalink)
      0
      from Microsoft TechNet:


      To create a linked server to access a SQL Server database

      Execute sp_addlinkedserver to create the linked server, specifying SQLOLEDB as provider_name, and the network name of the server running the remote instance of SQL Server as data_source.

      For example, to create a linked server named LinkSQLSrvr that operates against the instance of SQL Server running on the server whose network name is NetSQLSrvr, execute:

      sp_addlinkedserver N' LinkSQLSrvr' , ' ' , N' SQLOLEDB' , N' NetSQLSrvr'

      Map each local SQL Server login that needs access to the linked server to a SQL Server Authentication login on the linked server.

      This example maps access for the local login Joe to the SQL Server Authentication login Visitor on the linked server named LinkedSQLSrvr.

      sp_addlinkedsrvlogin N' LinkSQLSrvr' , false, N' Joe' , N' Visitor' , N' VisitorPwd'
      \ / /
      \\\' , / //
      \\\//, _/ //,
      \_-//' / //<,
      \ /// > \\\`__/_
      /,)-~>> _\` \\\
      (/ \\ //\\
      // // \\\
      (( ((
       
      #3
        Guest
        RE: How to Connect two different instances of SQL SERVER Monday, May 17, 2004 12:19 AM (permalink)
        0
        from Microsoft TechNet:


        To create a linked server to access a SQL Server database

        Execute sp_addlinkedserver to create the linked server, specifying SQLOLEDB as provider_name, and the network name of the server running the remote instance of SQL Server as data_source.

        For example, to create a linked server named LinkSQLSrvr that operates against the instance of SQL Server running on the server whose network name is NetSQLSrvr, execute:

        sp_addlinkedserver N' LinkSQLSrvr' , ' ' , N' SQLOLEDB' , N' NetSQLSrvr'

        Map each local SQL Server login that needs access to the linked server to a SQL Server Authentication login on the linked server.

        This example maps access for the local login Joe to the SQL Server Authentication login Visitor on the linked server named LinkedSQLSrvr.

        sp_addlinkedsrvlogin N' LinkSQLSrvr' , false, N' Joe' , N' Visitor' , N' VisitorPwd'
        \ / /
        \\\' , / //
        \\\//, _/ //,
        \_-//' / //<,
        \ /// > \\\`__/_
        /,)-~>> _\` \\\
        (/ \\ //\\
        // // \\\
        (( ((
         
        #4
          Online Bookmarks Sharing: Share/Bookmark

          Jump to:

          Current active users

          There are 0 members and 2 guests.

          Icon Legend and Permission

          • New Messages
          • No New Messages
          • Hot Topic w/ New Messages
          • Hot Topic w/o New Messages
          • Locked w/ New Messages
          • Locked w/o New Messages
          • Read Message
          • Post New Thread
          • Reply to message
          • Post New Poll
          • Submit Vote
          • Post reward post
          • Delete my own posts
          • Delete my own threads
          • Rate post

          2000-2014 ASPPlayground.NET Forum Version 3.9