TaxAssist and SQL 2008

A recent issue has been discovered with TaxAssist running on SQL 2008. The TA_XP.SQL and AddUsers.sql SQL scripts do not function properly on SQL 2008 when trying to add DYNGRP user/database role to “master” and “vertex” databases.

Error:
Could not find stored procedure “sp_addgroup”
Scenario:
Trying to execute TA_XP.SQL or AddUsers.SQL in SQL 2008
Reason:
In SQL 2008, DYNGRP should be added as a Database Role and the existing scripts doesn’t have that logic for it.
Workaround:
Manually add the SQL Database role “DYNGRP” to the master and Vertex database in SQL 2008 before executing the AddUsers.sql or the TA_XP.sql
Resolution:
This has been addressed in the TaxAssist v10.0 scripts. This update will automatically add DYNGRP as a database role if the SQL version is 2008. The same scripts will work on SQL 2000, SQL 2005 and SQL 2008.
For SQL 2000, it will create DYNGRP as a “User Group” and in SQL 2005 and 2008 the TA scripts will add DYNGRP as a “Database Role”.

The next update of TaxAssist v10.0 will have these files.  However, for quick reference, I have included the revised scripts below:

AddUsers.sql

if not exists( select * from sysusers where name = ‘DYNGRP’ )
begin
if ((SELECT convert(numeric(19,0), convert(varchar(3),SERVERPROPERTY(’productversion’))) as MAJORVERSION) >= 10)
begin
create role DYNGRP
end
else
begin
exec sp_addgroup DYNGRP
end
end
go
declare @CMD varchar(128)
, @USER varchar(30)
declare USER_Cursor cursor for select ‘exec sp_adduser ”’ + rtrim(name) + ”’, ”’ + rtrim(name) + ”’, DYNGRP’,
rtrim(name) from DYNAMICS..sysusers where uid > 1
open USER_Cursor
fetch USER_Cursor into @CMD, @USER
while (@@FETCH_STATUS = 0)
BEGIN
if not exists(select * from sysusers where name = @USER)
BEGIN
exec (@CMD)
select @USER = @USER + ‘ added.’
print @USER
END
fetch next from USER_Cursor into @CMD, @USER
END
deallocate USER_Cursor
TA_XP.sql
use master
go
/*****************************************************************************
*
* TaxAssist for Vertex/TaxWare
*
*****************************************************************************/
if exists (select * from sysobjects where id = object_id(’dbo.vtx_lookup_geocode’) and sysstat & 0xf = 4)
exec sp_dropextendedproc ‘vtx_lookup_geocode’
go
if exists (select * from sysobjects where id = object_id(’dbo.vtx_calculate_tax’) and sysstat & 0xf = 4)
exec sp_dropextendedproc ‘vtx_calculate_tax’
GO
if exists (select * from sysobjects where id = object_id(’dbo.vtx_register_tax’) and sysstat & 0xf = 4)
exec sp_dropextendedproc ‘vtx_register_tax’
GO
if exists (select * from sysobjects where id = object_id(’dbo.vtx_lookup_version’) and sysstat & 0xf = 4)
exec sp_dropextendedproc ‘vtx_lookup_version’
GO
if exists (select * from sysobjects where id = object_id(’dbo.vtx_get’) and sysstat & 0xf = 4)
exec sp_dropextendedproc ‘vtx_get’
GO
if exists (select * from sysobjects where id = object_id(’dbo.vtx_set’) and sysstat & 0xf = 4)
exec sp_dropextendedproc ‘vtx_set’
GO
if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[vtx_calculate_tax_return]‘) and sysstat & 0xf = 4)
exec sp_dropextendedproc N’[dbo].[vtx_calculate_tax_return]‘
GO
exec sp_addextendedproc ‘vtx_lookup_geocode’, ‘xp100001.dll’
GO
exec sp_addextendedproc ‘vtx_calculate_tax’, ‘xp100001.dll’
GO
exec sp_addextendedproc ‘vtx_register_tax’, ‘xp100001.dll’
GO
exec sp_addextendedproc ‘vtx_lookup_version’, ‘xp100001.dll’
GO
exec sp_addextendedproc ‘vtx_get’, ‘xp100001.dll’
GO
exec sp_addextendedproc ‘vtx_set’, ‘xp100001.dll’
GO
exec sp_addextendedproc ‘vtx_calculate_tax_return’, ‘xp100001.dll’
GO
if not exists (select * from sysusers where name = ‘DYNGRP’)
begin
if ((SELECT convert(numeric(19,0), convert(varchar(3),SERVERPROPERTY(’productversion’))) as MAJORVERSION) >= 10)
begin
create role DYNGRP
end
else
begin
exec sp_addgroup DYNGRP
end
end
go
declare @cStatement varchar(255)
declare G_cursor CURSOR for select ‘grant execute on ‘ + name + ‘ to DYNGRP’ from sysobjects
where (type = ‘X’)
and (name = ‘vtx_lookup_geocode’
or name = ‘vtx_calculate_tax’
or name = ‘vtx_register_tax’
or name = ‘vtx_lookup_version’
or name = ‘vtx_get’
or name = ‘vtx_set’
or name = ‘vtx_calculate_tax_return’)
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
GO