Connecting B1 client to two servers

[Former Member]

Hi experts,

I would like to ask for your advice for above matter as I am fairly new to SAP B1.

A client needed to install the B1 on two SQL servers, namely Server 1 (S1) for SBO common and License server, and Server 2 for company database. I would like to know if it is possible to get the B1 client to connect to both server.

Thanks,
Syawal

[former_member390407 · Accepted solution]

Hy Syawal,

I'm not sure if you can host SBO-COMMON and company databases separately. SAP B1 code contains direct references to the SBO-COMMON database without references to a server. For example below is the TmSp_DragOn SP text (you can find a reference to the common dataabase):

USE SBODemoGB

GO

SET ANSI_NULLS, QUOTED_IDENTIFIER ON

GO

CREATE  proc TmSp_DragOn

@onObj nvarchar (4),

@fromObj nvarchar (4),

@fldAias nvarchar (15) = NULL,

@srchVal nvarchar (255)= NULL,

@ColTypeI   int



WITH ENCRYPTION





As

begin



declare    @errCode    int

declare @SQLString    nvarchar (4000)

declare @SQLString1    nvarchar (4000)

declare @SQLString2    nvarchar (4000)

declare @SQLString3    nvarchar (4000)

declare @SQLString4    nvarchar (4000)

declare @JoinString    nvarchar (256)

declare @FilterString    nvarchar (256)

declare @whereString    nvarchar (512)

declare @groupString    nvarchar (2048)

declare @tableStr    nvarchar (8)

declare    @dbCol        nvarchar (128)

declare @prefix        nvarchar (32)

declare    @isNull        int

declare    @colType    int

declare    @keyCount    int

declare    @fldCount    int

declare    @stringCount    int



select @JoinString = JoinStr, @FilterString = FilterStr

    from [sbo-common].[dbo].[SDRQ]

    where objTo = @onObj and objFrom = @fromObj



Set @stringCount = 0

Set @SQLString1 = ''

Set @SQLString2 = ''

Set @SQLString3 = ''

Set @SQLString4 = ''



Set @SQLString = 'Select  '

Set @groupString = ' Group By '



select @keyCount = count(name) from syscolumns 

        where id = OBJECT_ID(@onObj) and isnullable = 0

select @fldCount = count(name) from syscolumns 

        where id = OBJECT_ID(@onObj) 



-- Build output part

declare dbColsList cursor scroll OPTIMISTIC for 

    select name, isnullable, cast(xtype as int) as type

    from syscolumns where id = OBJECT_ID(@onObj)  order by colId



if (@onObj = @fromObj or @JoinString=@onObj+ ' T0')

    set @tableStr = 'T0.'

else

    set @tableStr = 'T1.'



open dbColsList

fetch next from dbColsList into @dbCol, @isNull, @colType

while @@fetch_status = 0

    begin

    set @dbCol = ltrim (rtrim(@dbCol))



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

    --find who is the current string

    if len(@SQLString) > 3900

    begin

             if @stringCount = 0 set @SQLString1 = @SQLString

        else if @stringCount = 1 set @SQLString2 = @SQLString

        else if @stringCount = 2 set @SQLString3 = @SQLString        

        else if @stringCount = 3 set @SQLString4 = @SQLString        

    

        set @SQLString = ''

        set @stringCount = @stringCount + 1        

    end

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



    if (@isNull = 1)

    begin

        set @fldCount = @fldCount - 1



        if (@colType = 34 or @colType = 35 or @colType = 36 or @colType = 99)

        set @SQLString = @SQLString + 'CAST (''FFFF'' as varchar(10))'

        else

        set @SQLString = @SQLString + 'MIN(T0.[' + @dbCol + '])'



        if (@fldCount > 0)

        set @SQLString = @SQLString + ','



    end

    else

    begin

        set @fldCount = @fldCount - 1

        set @keyCount = @keyCount - 1

        

        if (left(@dbCol,2) != 'U_')

        begin

            if @fldCount > 0

                set @SQLString = @SQLString + 'T0.['+ @dbCol + '],'

            else

                set @SQLString = @SQLString + 'T0.['+ @dbCol + ']'



            if @keyCount > 0

                set @groupString = @groupString + 'T0.[' + @dbCol + '],'

            else

                set @groupString = @groupString + 'T0.['+ @dbCol + ']'

        end

    end

    

    fetch next from dbColsList into @dbCol, @isNull, @colType

   end

    if( right(@groupString,1) = ',')

        set @groupString = left( @groupString,len(@groupString) -1 )

    if( right(@SQLString,1) = ',')

        set @SQLString = left( @SQLString,len(@SQLString) -1 )

close dbColsList

deallocate dbColsList



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

-- copy from sqlsting to the last string and rtrim

set @stringCount = @stringCount + 1



     if @stringCount = 1 set @SQLString1 = @SQLString

else if @stringCount = 2 set @SQLString2 = @SQLString

else if @stringCount = 3 set @SQLString3 = @SQLString        

else if @stringCount = 4 set @SQLString4 = @SQLString        



set @SQLString1 = rtrim(@SQLString1)

set @SQLString2 = rtrim(@SQLString2)

set @SQLString3 = rtrim(@SQLString3)

set @SQLString4 = rtrim(@SQLString4)

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



-- Add filtering & conditions

Set @whereString = ' WHERE '+ @tableStr + @fldAias

if (substring(@srchVal,1,8) = 'CONVERT(') 

begin

    Set @whereString = @whereString  + ' = ' +  @srchVal

end

else

begin 

    if (substring(@srchVal,1,1) = '''') 

        Set @whereString = @whereString  + ' = N' +  @srchVal

    else

        Set @whereString = @whereString  + ' = N''' +  @srchVal    + ''''

end



-- Obtain the result set (Using @SQLString1-4 bypass the max length of 4000 to an nvarchar)

exec (@SQLString1 + @SQLString2 + @SQLString3 + @SQLString4 + ' FROM ' + @JoinString + @whereString + ' ' + @groupString)



--print (@SQLString1)

--print (@SQLString2)

--print (@SQLString3)

--print (@SQLString4)

--print (' FROM ' + @JoinString )

--print (@whereString ) 

--print (@groupString)



end

GO 

[Former Member · Accepted solution]

Hi Sergei,

you are right, both servers need to have SBO-COMMON. Thanks for the code you've given. I'll try to use this a reference for my tweaking at my end. Thank you.

Regards

[agustin_marcoscividanes]

Hi

I think you have to declare two database servers in your SLD, and the customer can select the right one.

Kind regards

Agustín

[Former Member]

Hi Agustin,

thanks for your reply, seems i forgot this step in my implementation.
you've been a great help, thanks.