none
SQL 7.0迁移到SQL 2005后遇到的问题 RRS feed

  • 问题

  • 这个帖子我在51CTO上面发过,不想重复发了,才会出现贴图错误,要看图可以到下面连接上看

    http://bbs.51cto.com/thread-716391-1.html 

     

    大家好,

    我是一个新人,有相关SQL 7.0迁移到SQL2005的相关问题想请教这里的高手.

    为了把公司已经使用了10多年的生产服务器更新掉,而且SQL 7.0已经无法满足公司的业务需求,所以想要把SQL 7.0迁移到SQL2005.但是现在碰到问题,从ERP(MICROSOFT DYNAMICS AX)的接口传递数据到SQL7.0一点问题都没有,但是自从SQL7.0迁移到2005后,这个接口就失效了,我考虑应该不是ERP接口的问题,问题应该是出现在这个接口调用的存储过程上.


    在新升级的SQL服务器上找到这个存储过程,与原来的SQL7.0上的没有一点改变[code]set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go


    /** 1.08.0 15/06/00 BRT Include **/
    /*************************************************************************/
    /** **/
    /** Project: COSMOS **/
    /** File name: sp_bc1_insert_item **/
    /** **/
    /** Date created: 12/03/99 **/
    /** Last modified: 15/06/00 **/
    /** **/
    /** Description: **/
    /** **/
    /** Inserts an item record. It also creates the BOM records. **/
    /** **/
    /*************************************************************************/
    /** Parameters: **/
    /** see list **/
    /*************************************************************************/
    /** Return value: **/
    /** void **/
    /*************************************************************************/
    /** Ver. Date By Description **/
    /** ------ -------- --- ---------------------------------------- **/
    /** 1.00.0 17/03/98 BRT Distr. version **/
    /** 1.01.0 12/03/99 BRT remove language **/
    /** 1.02.0 23/03/99 BRT Added link to CofA **/
    /** 1.03.0 07/09/99 BRT Made the plantcd come from parameter **/
    /** 1.04.0 02/11/99 BRT Fix for cofa (ink and artwork) **/
    /** 1.05.0 09/11/99 BRT Fix for cofa (effective date) **/
    /** 1.06.0 30/11/99 BRT Fix for cofa (OX calc for item_master) **/
    /** 1.07.0 31/05/00 BRT Vcaps + VC **/
    /** 1.08.0 15/06/00 BRT added nolock **/
    /** **/
    /*************************************************************************/
    ALTER procedure [dbo].[sp_bc1_insert_item]
    @item_descr char(30), /* description */
    @item_descr_du char(30),
    @item_descr_fr char(30),
    @capsule_size char(2), /* capsule size */
    @closure_type char(2), /* closure type */
    @print_type char(2), /* print type */
    @groove_depth char(2), /* groove depth */
    @body_color char(15), /* body color */
    @cap_color char(15), /* cap color */
    @ink1 char(15), /* ink 1 */
    @ink2 char(15), /* ink 2 */
    @artwork1 char(15), /* rollcode 1 */
    @artwork2 char(15), /* rollcode 2 */
    @gel_formula char(1) /* gel_formula */
    as
    /*************************************************************************/
    /** DECLARATIONS **/
    /*************************************************************************/
    DECLARE @nitem_cd char(15)

    DECLARE @newitem_cd char(15)
    DECLARE @nitem_sequence_nr int
    DECLARE @new_sequence_nr int
    DECLARE @plant char(1)

    /*************************************************************************/
    /** search the plant code **/
    /*************************************************************************/
    SELECT @plant=substring(parameter_value_string,1,1)
    FROM parameter (nolock)
    WHERE parameter_cd = 'PLANT_CD'
    IF (@plant is null)
    BEGIN
    SELECT @plant='X'
    END

    /*************************************************************************/
    /** search if the item exists **/
    /*************************************************************************/
    SELECT @nitem_cd=item_cd
    FROM item (NOLOCK)
    WHERE capsule_size = @capsule_size AND
    closure_type = @closure_type AND
    print_type = @print_type AND
    groove_depth = @groove_depth AND
    body_color = @body_color AND
    cap_color = @cap_color AND
    ink1 = @ink1 AND
    ink2 = @ink2 AND
    artwork1 = @artwork1 AND
    artwork2 = @artwork2 AND
    gel_formula = @gel_formula
    IF (@nitem_cd is null)
    /*************************************************************************/
    /** the item does not exist, so create a new one **/
    /*************************************************************************/
    BEGIN
    /*************************************************************************/
    /** find the last sequence number **/
    /*************************************************************************/
    SELECT @nitem_sequence_nr = max (item_sequence_nr)
    FROM item (NOLOCK)
    WHERE capsule_size = @capsule_size AND
    closure_type = @closure_type AND
    print_type = @print_type
    IF (@nitem_sequence_nr is null)
    BEGIN
    SELECT @new_sequence_nr = 1
    END
    ELSE
    BEGIN
    SELECT @new_sequence_nr = @nitem_sequence_nr+1
    END
    SELECT @newitem_cd = @plant + @capsule_size + @closure_type +
    @print_type + '000' + convert(char(4),@new_sequence_nr)
    IF (@new_sequence_nr >= 10)

    BEGIN

    SELECT @newitem_cd = @plant + @capsule_size + @closure_type +
    @print_type + '00' + convert(char(4),@new_sequence_nr)
    END
    IF (@new_sequence_nr >= 100)
    BEGIN
    SELECT @newitem_cd = @plant + @capsule_size + @closure_type +

    @print_type + '0' + convert(char(4),@new_sequence_nr)
    END
    IF (@new_sequence_nr >= 1000)
    BEGIN
    SELECT @newitem_cd = @plant + @capsule_size + @closure_type +

    @print_type + convert(char(4),@new_sequence_nr)

    END


    END
    /*************************************************************************/
    /** CofA Update of bill_of_materials table **/
    /*************************************************************************/

    DECLARE @color_descr varchar(50)
    IF (@nitem_cd is null)
    BEGIN
    IF (not exists (select parent_item
    from bill_of_material (nolock)
    where parent_item = @newitem_cd))
    BEGIN
    /*************************************************************************/
    /** body color **/
    /*************************************************************************/
    insert into bill_of_material

    values('BM',@newitem_cd,1,@body_color,
    'Jan 1 1960','jan 1 2030','ZY','ZY',1)

    /*************************************************************************/
    /** cap color **/
    /*************************************************************************/
    insert into bill_of_material
    values('BM',@newitem_cd,2,@cap_color,
    'Jan 1 1960','jan 1 2030','ZY','ZY',1)
    /*************************************************************************/

    /** color components **/
    /*************************************************************************/
    declare @color_cd char(15),
    @raw_cd char(15),
    @color_frml_qty_perc real,
    @current_color_cd char(15),
    @seq int

    declare crsr scroll cursor
    for
    select color_cd,
    raw_cd,
    color_frml_qty_perc
    from color_frml(nolock)
    where color_cd = @body_color or
    color_cd = @cap_color
    order by color_cd
    for read only

    open crsr

    fetch first from crsr into @color_cd,@raw_cd,@color_frml_qty_perc

    select @current_color_cd = @color_cd
    select @seq = 1

    while( @@fetch_status = 0)
    begin

    IF (not exists (select parent_item
    from bill_of_material (nolock)

    where parent_item = @color_cd and
    seq_number = @seq))
    BEGIN
    insert into bill_of_material
    values ('BM',@color_cd,@seq,@raw_cd,'Jan 1 1960','jan 1 2030','ZY','ZY',@color_frml_qty_perc)
    END

    fetch next from crsr into @color_cd,@raw_cd,@color_frml_qty_perc

    if (@current_color_cd = @color_cd)
    begin
    select @seq = @seq + 1
    end

    else
    begin
    select @seq = 1
    select @current_color_cd = @color_cd
    end
    end

    close crsr
    deallocate crsr
    /*************************************************************************/
    /** update the item_master **/
    /*************************************************************************/
    IF (not exists (select item_cd
    from item_master(nolock)

    where item_cd = @newitem_cd))
    BEGIN
    insert into item_master

    values('IM',@newitem_cd,
    @capsule_size+@closure_type+@print_type+' ('+substring(@body_color,1,6)+' / '+substring(@cap_color,1,6)+')',

    '',
    @capsule_size,@gel_formula,0,
    @capsule_size,@closure_type,@groove_depth,
    @body_color,@cap_color,@print_type,
    'ART-'+@artwork1,'ART-'+@artwork2,@ink1,@ink2,
    '','','S',@gel_formula,
    @capsule_size,
    '','','','','','','','','','M','M',1.00000,'1','',1.000,'','')

    /*************************************************************************/
    /** update the item_master with the correct item_type **/
    /** B = Bovine and no FeOX and TI **/
    /** C = Bovine and FeOX and TI both sides **/
    /** D = Bovine and FeOX and TI one sides only **/
    /** N = Normal and no FeOX and TI **/
    /** O = Normal and FeOX and TI both sides **/
    /** P = Normal and FeOX and TI one sides only **/
    /** V = vcaps trans **/
    /** W = vcaps color **/
    /*************************************************************************/
    /*************************************************************************/
    /** update the item_master with the correct item_class **/
    /** item_class = capsule_size for non vcaps **/
    /** item_class = 1M 10VC **/
    /** 1N 11VC **/
    /** 1O 12VC **/
    /** 1P 13VC **/
    /** 1Q 14VC **/
    /** 1R 15VC **/
    /** 1S 16VC **/
    /** 1T 17VC **/
    /** 1U 18VC **/
    /** 1V 19VC **/
    /*************************************************************************/
    update item_master
    set item_type = 'B'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'B' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    (select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0 and
    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0

    update item_master
    set item_type = 'C'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'B' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    (select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0 and
    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0

    update item_master
    set item_type = 'D'

    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'B' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    (select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0 and
    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0

    update item_master
    set item_type = 'D'

    from item_master im(nolock), item i(nolock)

    where i.gel_formula = 'B' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    (select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0 and
    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0

    update item_master
    set item_type = 'N'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'N' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    (select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0 and

    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0

    update item_master
    set item_type = 'O'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'N' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    (select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0 and
    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0

    update item_master
    set item_type = 'P'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'N' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    (select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0 and
    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0

    update item_master
    set item_type = 'P'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'N' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    (select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0 and
    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0


    update item_master
    set item_type = 'V'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    (select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0 and
    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) = 0

    update item_master

    set item_type = 'W'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    ((select count (raw_cd) from color_frml where color_cd = i.body_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0 or
    (select count (raw_cd) from color_frml where color_cd = i.cap_color and raw_cd in ('BOX','ROX','YOX','TI')) > 0)

    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'M'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '0'

    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'N'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '1'

    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'O'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and

    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '2'

    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'P'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '3'

    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'Q'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '4'

    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'R'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '5'

    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'S'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '6'


    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'T'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '7'

    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'U'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '8'

    update item_master
    set item_class = substring(i.capsule_size,1,1) + 'V'
    from item_master im(nolock), item i(nolock)
    where i.gel_formula = 'V' and
    im.item_cd = i.item_cd and
    i.item_cd = @newitem_cd and
    substring(i.capsule_size,2,1) = '9'

    END

    IF (not exists (select item_cd
    from item_master (nolock)
    where item_cd = @body_color))
    BEGIN

    select @color_descr = color_descr
    from color_lst (nolock)
    where color_cd = @body_color

    insert into item_master
    values('IM',@body_color,
    @color_descr,
    '',
    'ZY','0',0,
    '','','',
    '','','',
    '','','','',
    '','','S',@gel_formula,
    @capsule_size,
    '','','','','','','','C','','L','L',1.00000,'1','',1.000,'','')
    END
    IF (not exists (select item_cd
    from item_master (nolock)
    where item_cd = @cap_color))
    BEGIN
    select @color_descr = color_descr
    from color_lst (nolock)
    where color_cd = @cap_color

    insert into item_master
    values('IM',@cap_color,
    @color_descr,
    '',


    'ZY','0',0,
    '','','',
    '','','',
    '','','','',
    '','','S',@gel_formula,

    @capsule_size,
    '','','','','','','','C','','L','L',1.00000,'1','',1.000,'','')
    END
    IF (not exists (select item_cd
    from item_master (nolock)

    where item_cd = @ink1))
    BEGIN
    if (@ink1 <> '')
    BEGIN
    insert into item_master
    values('IM',@ink1,

    @ink1,
    '',

    'DA','D',0,
    '','','',
    '','','',
    '','','','',
    '','','','',
    '',
    '','','','','','','','','','KG','KG',1.00000,'2','',1.000,'','')
    END
    END
    IF (not exists (select item_cd
    from item_master (nolock)
    where item_cd = @artwork1))
    BEGIN
    if (@artwork1 <> '')
    BEGIN
    insert into item_master
    values('IM','ART-'+@artwork1,
    @artwork1,
    '',

    'DA','D',0,
    '','','',
    '','','',
    '','','','',
    '','','','',
    '',
    '','','','','','','','','','EA','EA',1.00000,'1','',1.000,'','')
    END
    END
    IF (not exists (select item_cd
    from item_master (nolock)
    where item_cd = @ink2))

    BEGIN
    if (@ink2 <> '')
    BEGIN

    insert into item_master
    values('IM',@ink2,
    @ink2,
    '',

    'DA','D',0,
    '','','',
    '','','',
    '','','','',
    '','','','',
    '',
    '','','','','','','','','','KG','KG',1.00000,'2','',1.000,'','')

    END
    END
    IF (not exists (select item_cd
    from item_master (nolock)
    where item_cd = @artwork2))

    BEGIN
    if (@artwork2 <> '')
    BEGIN
    insert into item_master
    values('IM','ART-'+@artwork2,
    @artwork2,

    '',

    'DA','D',0,
    '','','',
    '','','',
    '','','','',
    '','','','',
    '',
    '','','','','','','','','','EA','EA',1.00000,'1','',1.000,'','')
    END
    END
    END
    END
    /*************************************************************************/
    /** give the result back an error --> easy in VC++ **/
    /*************************************************************************/
    IF (@nitem_cd is null)
    BEGIN
    RAISERROR (@newitem_cd,11,2)
    END
    ELSE
    BEGIN
    RAISERROR (@nitem_cd,11,2)
    END




    [/code]






    下面分享下我的迁移经验,大部分也是在网上搜集的.
    其实主要还是一些孤立用户的问题,麻烦一点,其他都还是比较简单的.
    就是现在我碰到的一些ERP接口无法把数据TRANSFER到新的SQL 2005,请大家帮忙,谢谢![code]1. Run Microsoft SQL Server Upgrade Advisor and get the following report:
    2. The method of migration (we used Method 1 or Method 2)

    Method 1.Backup the database on SQL 7.0 and Restore the database on SQL 2005
    Method 2.Stop the SQL service on SQL 7.0 and copy data and log file to SQL 2005 and then attach them(I cannot find the detach option on SQL 7.0).
    Method 3. Use ManagementCopy Database Wizard. ----Failure


    So we use Method1 and 2 to migrate SQL.

    The steps for migration

    --1.restore or attach the database on the new server--SQL 2005


    --2.set the level for Global

    Use master
    Go

    Exec dbo.sp_dbcmptlevel @dbname='global', @new_cmptlevel=80

    --3.set the dbowner for Global

    Use global
    EXEC sp_changedbowner 'sa'

    --4.check the user if it is in [security--login]

    Use global
    Go

    EXEC SP_CHANGE_USERS_LOGIN 'REPORT'

    --5.use the SP_CHANGE_USERS_LOGIN to fix the issue (Need set the password for every user)
    --example:

    Use global
    Go

    Exec SP_CHANGE_USERS_LOGIN 'Auto_Fix', 'admsublot', NULL,'Pfizer2004'

    --6.Enabled ‘Cross database ownership chaining’
    Use global
    Go

    Exec SP_DBOPTION ‘Global’, ‘db chaining’, ‘True’

    --7.Run 3 SQL file which is in this folder on SQL2005(Modify as SID in SQL 2005)

    --8.restore the job on SQL 2005
    [/code]

    2010年6月18日 2:34

全部回复

  • Who wrote the code? Should check with author.
    2010年6月18日 3:27
  • 没看到错误信息
    2010年6月18日 4:04
  • 这需要耐心去调试。。。并跟踪前端是怎样调用的,有什么不对的结果
    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
    2010年6月18日 4:17
  • 我刚做7.0到2005的升级,在些期间对我自己现有程序发现的主要问题列出,供你参考

    1、在7.0中支持的"*="运算在2005中已不支持

    2、对NULL值的判断有所改变,此点我还没有总结出具体如何改变

     

    对于你的程序,建议你对列出过程中的NULL值改变一下判断条件试一下

    /*************************************************************************/
    /** search the plant code **/
    /*************************************************************************/
    SELECT @plant=substring(parameter_value_string,1,1)
    FROM parameter (nolock)
    WHERE parameter_cd = 'PLANT_CD'
    IF (@plant is null)
    BEGIN
    SELECT @plant='X'
    END

    改为:

    /*************************************************************************/
    /** search the plant code **/
    /*************************************************************************/
    SELECT @plant=substring(parameter_value_string,1,1)
    FROM parameter (nolock)
    WHERE parameter_cd = 'PLANT_CD'
    IF (LEN(ISNUL(RTRIM(@plant),'')) = 0)
    BEGIN
    SELECT @plant='X'
    END

    试一下,或有可以解决你的问题

    其它地方类似修改即可
    meerio
    2010年7月17日 3:13