Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Unanswered

I got SQL Server Message 229 when do Closing Period

Posted on by Microsoft Employee

 

 

Dear all,

I got problem when Closing Period.

 

 

[URL=http://imageshack.us/photo/my-images/824/image2cye.png/][IMG]http://img824.imageshack.us/img824/43/image2cye.png[/IMG][/URL]

 

[URL=http://imageshack.us/photo/my-images/152/image3tf.png/][IMG]http://img152.imageshack.us/img152/7817/image3tf.png[/IMG][/URL]

After i got SQL Server Message 229, proses closing cannot be cancel (I wait for  3 hours) and i must do end task

 

Please kindly suggest or help me for this issue

 

thanks before,

 

E. Senjaya

  • HolliV Profile Picture
    HolliV 235 on at
    RE: I got SQL Server Message 229 when do Closing Period

    Thank you.  You always have the best solutions.  I ended up just dropping the triggers for a company that I deleted and that worked for me but without you pointing me to the triggers I wouldn't have figured it out.  In my situation I deleted an empty company but the SYSTEM db still held the triggers for that company.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: I got SQL Server Message 229 when do Closing Period

    Hi Carolyn,

    thanks for your guide.

    I solve our issue by restore system database

    regards

    E.Senjaya

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: I got SQL Server Message 229 when do Closing Period

    Hi Carolyn,

    thanks for your information. It's \ery usefull

    but does any your script is complete or some part are hide ?

    because i think it's not a complete script (usually SQL hide some text if there is a sign "+")

    [URL=imageshack.us/.../image1ae.png][IMG][View:http://img441.imageshack.us/img441/2730/image1ae.png][/IMG][/URL]

    Uploaded with [URL=http://imageshack.us]ImageShack.us[/URL]

     

     

    thanks before

    E.Senjaya

  • Re: I got SQL Server Message 229 when do Closing Period

    Tony,

    Here is the script file contents:

    -- Drop all windows authentication triggers.  

    -- If using windows authentication, it will also recreate the triggers

    --  and cleans up any stray vs_acctsub or vs_acctxref records

    --

    -- 1 - Make a good database backup

    -- 2 - Run as is against your SL System database

    --

    -- last updated: 5/13/2008

    -- Step 1: Drop all ACCTSUB and ACCTXREF triggers

    declare @triggername as char(100)

    declare @execString as char(200)

    DECLARE trigger_cursor CURSOR FOR

      select name from sysobjects where type='TR' and (

    name like 'sDeleteAcctSub_%' or

    name like 'sInsertAcctSub_%' or

    name like 'sUpdateAcctSub_%' or

    name like 'sDeleteAcctXref_%' or

    name like 'sInsertAcctXref_%' or

    name like 'sUpdateAcctXref_%')

    OPEN trigger_cursor

    FETCH NEXT FROM trigger_cursor INTO @triggername

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @execString = 'drop trigger ' + @triggername

    print @execString

    exec (@execString)

    print 'Done'

    FETCH NEXT FROM trigger_cursor INTO @triggername

    END

    CLOSE trigger_cursor

    DEALLOCATE trigger_cursor

    -- only do step 2 and 3 if windows auth

    if (select top 1 text from syscomments where ID in (select ID from sysobjects where name='getauthenticationtype' and type='P'))

    like '%Windows%'

    begin

    -- Step 2: Recreate a new set of 6 triggers for each app database listed in the company table

    declare @dbname as char(100)

    declare @execString2 as char(1000)

    DECLARE db_cursor CURSOR FOR

      select distinct databasename from company  where databasename<>''

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

            set @execString2 = 'CREATE TRIGGER sDeleteAcctSub_' + rtrim(@dbname)

    +' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)

    +' AFTER DELETE '

    +' AS Delete '+rtrim(@dbname)+'..vs_AcctSub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'

    print @execString2

    exec (@execString2)

    print 'Done'

            set @execString2 = 'CREATE TRIGGER sInsertAcctSub_' + rtrim(@dbname)

    +' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)

    +' AFTER INSERT '

    +' AS Insert into '+rtrim(@dbname)+'..vs_AcctSub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'

    +'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'

    print @execString2

    exec (@execString2)

    print 'Done'

            set @execString2 = 'CREATE TRIGGER sUpdateAcctSub_' + rtrim(@dbname)

    +' ON AcctSub WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)

    +' AFTER UPDATE '

    +' AS Delete '+rtrim(@dbname)+'..vs_acctsub from '+rtrim(@dbname)+'..vs_acctsub v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid and v.sub = deleted.sub'

    +' Insert into '+rtrim(@dbname)+'..vs_acctsub select acct,active,cpnyid,crtd_datetime,crtd_prog,crtd_user,descr,lupd_datetime,lupd_prog,lupd_user,noteid,s4future01,s4future02,s4future03,s4future04,s4future05,s4future06,s4future07,s4future08,s4future09,s4future10,'

    +'s4future11,s4future12,sub,user1,user2,user3,user4,user5,user6,user7,user8,null from inserted'

    print @execString2

    exec (@execString2)

    print 'Done'

            set @execString2 = 'CREATE TRIGGER sDeleteAcctXref_' + rtrim(@dbname)

    +' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)

    +' AFTER DELETE '

    +' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'

    print @execString2

    exec (@execString2)

    print 'Done'

            set @execString2 = 'CREATE TRIGGER sInsertAcctXref_' + rtrim(@dbname)

    +' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)

    +' AFTER INSERT '

    +' AS Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'

    print @execString2

    exec (@execString2)

    print 'Done'

            set @execString2 = 'CREATE TRIGGER sUpdateAcctXref_' + rtrim(@dbname)

    +' ON AcctXref WITH EXECUTE AS '+char(39)+'07718158D19D4f5f9D23B55DBF5DF1'+char(39)

    +' AFTER UPDATE '

    +' AS Delete '+rtrim(@dbname)+'..vs_acctxref from '+rtrim(@dbname)+'..vs_acctxref v join deleted on v.acct = deleted.acct and v.cpnyid = deleted.cpnyid'

    +' Insert into '+rtrim(@dbname)+'..vs_acctXref select acct,accttype,active,cpnyid,descr,user1,user2,user3,user4,null from inserted'

    print @execString2

    exec (@execString2)

    print 'Done'

    FETCH NEXT FROM db_cursor INTO @dbname

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

    -- Step 3: Cleanup any stray vs_acctxref or vs_acctsub records

    declare @dbName3 as char(85)

    declare @execString3 as char(200)

    DECLARE db_cursor3 CURSOR FOR

      select distinct databasename from company where databasename<>''

    OPEN db_cursor3

    FETCH NEXT FROM db_cursor3 INTO @dbName3

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctxref a left join acctxref b on a.acct=b.acct and a.cpnyid=b.cpnyid where b.acct is null'

    print @execString3

    exec (@execString3)

    set @execString3 = 'delete a from ' + QUOTENAME(rtrim(@dbName3)) + '..vs_acctsub a left join acctsub b on a.acct=b.acct and a.sub=b.sub and a.cpnyid=b.cpnyid where b.acct is null'

    print @execString3

    exec (@execString3)

    FETCH NEXT FROM db_cursor3 INTO @dbName3

    END

    CLOSE db_cursor3

    DEALLOCATE db_cursor3

    END

    -- END

  • Re: I got SQL Server Message 229 when do Closing Period

    Tony,

    Here is the content of the article:

    Various SQL Server error messages occur in multiple screens in Microsoft Dynamics SL

    Note Before you follow the instructions in this article, make sure that you have a complete backup copy of the database that you can restore if a problem occurs.

    SYMPTOMS

    You may receive one of the following error messages in the various screens in Microsoft Dynamics SL:

    Error message 1

    SQL Server Message 10215 - Invalid object name '<xxxx>..vs_AcctSub'

    Error message 2

    SQL Server Message 10215 - Invalid object name '<xxxx>..vs_AcctXRef'

    Error message 3

    System Message 6909 Another process has already added the vs_acctxref item. The program must be terminated

    Error message 4

    SQL error 229 – INSERT permission was denied on object vs_acctsub

    Error message 5

    System Message 6908 Another process has already added the vs_acctsub item. The system will automatically Cancel your changes.

    Note In these messages, <xxxx> represents the name of the Microsoft Dynamics SL application database.

    This problem occurs if the following conditions are true:• The databases are configured to use Windows authentication.

    • You deleted or renamed an application database.

    CAUSE

    When the databases are configured to use Windows authentication, six triggers are created in the system database that contain the name of the application database. If an application database is deleted, renamed, or the authentication type is changed is changed from Windows Authentication to SQL Authentication, the triggers that contain the database name remain in the system database.

    RESOLUTION

    To resolve this problem, download and then run the rebuild_triggers.sql statement against the system database. To do this, follow these steps:

    1.  Download the rebuild_triggers.sql file.

    The following file is available for download from the Microsoft Dynamics File Exchange Server:

    rebuild_triggers.sql

    Release Date: May 21, 2008

    Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help prevent any unauthorized changes to the file.  

    2.  Double-click the rebuild_triggers.sql file, and then connect to the computer that is running SQL Server and that hosts the Microsoft Dynamics SL databases.  

    3.  Select the system database that you want, and then click Execute.  

    4.  Exit SQL Server Management Studio.  

    I'll send the script in the next post.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: I got SQL Server Message 229 when do Closing Period

    No

  • Re: I got SQL Server Message 229 when do Closing Period

    Hi Tony,

    Does this link work for you?  mbs.microsoft.com/.../KBDisplay.aspx

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: I got SQL Server Message 229 when do Closing Period

    Carolyn,

    I attempted to find this articl on partnersource and came up empty.  I did find a link in an article to it but nothing came up when I clicked on it.

  • Re: I got SQL Server Message 229 when do Closing Period

    Hello Ekos,

    For SQL 229 error message "insert permission was denied on the object vs_acctsubb" you will need to follow the instructions on KB article 942095.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: I got SQL Server Message 229 when do Closing Period

    Hi Carolyn,

    i have already give print screen on my issue

    May you kindly advise me ?

    thanks before

    E.Senjaya

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,995 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,610 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans