I got SQL Server Message 229 when do Closing Period

Hello E. Senjaya,

The print screens of the error messages are not viewable so I don't know what error messages you are receiving.  Are you trying to close the General Ledger module and it is hanging?  If so, please see KB article 916100.  The closing of the GL module hung due to a bug in FRx.  The solution is to apply the latest service pack for FRx.

Hi Carolyn,

i have already give print screen on my issue

May you kindly advise me ?

thanks before

E.Senjaya

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.

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.

Hi Tony,

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

No

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.

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

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 "+")

Uploaded with ImageShack.us

thanks before

E.Senjaya

Hi Carolyn,

thanks for your guide.

I solve our issue by restore system database

regards

E.Senjaya