I got SQL Server Message 229 when do Closing Period

(0) Share

Report

Posted on by Community Member 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

*This post is locked for comments

All responses (11)

Answers (0)

HolliV 235 on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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
Carolyn Kolpien on at

Like (1)

Report

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
Carolyn Kolpien on at

Like (1)

Report

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 Microsoft Employee on at

Like (0)

Report

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

No
Carolyn Kolpien on at

Like (1)

Report

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 Microsoft Employee on at

Like (0)

Report

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.
Carolyn Kolpien on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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