Post reply

linked server problem

  • March 7, 2009 at 3:57 am

    #97354

    i am trying to create a linked server that i can use to link to a remote database in my web application i did the following command

    command.CommandText = "EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,@location,@provstr,@catalog"

    command.Parameters.Clear()

    command.Parameters.Add("@srvproduct", SqlDbType.Char, 20).Value = ""

    command.Parameters.Add("@server", SqlDbType.Char, 20).Value = "ERP1"

    command.Parameters.Add("@provider", SqlDbType.Char, 20).Value = "SQLNCLI"

    command.Parameters.Add("@datasrc", SqlDbType.Char, 20).Value = "ERP"

    command.Parameters.Add("@location", SqlDbType.Char, 20).Value = ""

    command.Parameters.Add("@provstr", SqlDbType.Char, 20).Value = ""

    command.Parameters.Add("@catalog", SqlDbType.Char, 20).Value = ""

    when i linked it to a sqldatasource for testing purposes i got the following error:

    out-of-process use of old db provider SQLNCLI with sql server is not supported

    what is the issue

  • March 7, 2009 at 4:32 am

    #955437

    Hi

    Change "SQLNCLI" to "SQLOLEDB" than it should work.

    Greets

    Flo

  • March 7, 2009 at 5:13 am

    #955443

    ok i did it but i got this error

    "cannot initialize the data source object of the ole db provider sqlncli for linked server ERP1"

    i searched for this error and i got that i should add a local server log in so i did and typed my user name and password but i got an error:

    TITLE: Microsoft SQL Server Management Studio

    ------------------------------

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    ADDITIONAL INFORMATION:

    Ad hoc updates to system catalogs are not allowed. (Microsoft SQL Server, Error: 259)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=259&LinkId=20476

    to you think it is a authorization issue?

  • March 7, 2009 at 5:23 am

    #955444

    You need the linked server and the authentication information for the remote server.

    Here is a working SQL example. You only have to put the information into your VB code.

    [font="Courier New"]-- The name of the link to be created

    DECLARE @LinkName     SYSNAME   SET @LinkName    = 'MYLINK'

    -- The name or IP of the remote server

    DECLARE @SrvName      SYSNAME   SET @SrvName     = 'RemoteServer'

    -- The name of the local login to be mapped to the linked server or NULL to allow to all local logins

    DECLARE @LocalLogin   SYSNAME   SET @LocalLogin  = NULL -- 'sa'

    -- The login for the remote server

    DECLARE @RmtLogin     SYSNAME   SET @RmtLogin    = 'rmt_browse'

    -- The password for the remote login

    DECLARE @RmtPwd       SYSNAME   SET @RmtPwd      = 'readonly'

    IF NOT EXISTS (SELECT * FROM MASTER..Sysservers WHERE IsRemote = 1 AND SrvName = @LinkName)

    BEGIN

       -- Create the linked server

       EXECUTE sp_addlinkedserver @server = @LinkName,

                                  @srvproduct = '',

                                  @provider = 'SQLOLEDB',

                                  @datasrc = @SrvName

    -- ,

    --                               @catalog = @DbName

       -- Create the remote login information

       EXECUTE sp_addlinkedsrvlogin @rmtsrvname = @LinkName,

                                  @useself = 'false',

                                  @locallogin = @LocalLogin,

                                  @rmtuser = @RmtLogin,

                                  @rmtpassword = @RmtPwd

    END

    GO

    [/font]

    Greets

    Flo

  • March 7, 2009 at 5:47 am

    #955446

    ok thanks man really it worked

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

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