SAP Business One Patch release uitgave schema

SAP Business One Patch datum vrijgave

Voor SAP Business One 9.2

  • Patchlevel 3 – week van 30-5 en 3 juni 2016
  • Patchlevel 2 – week van 28-3 en 5 april 2016
  • Patchlevel 1 – week van 1-5 februari 2016

Voor SAP Business One 9.1

  • Patchlevel 13 – week van 5-10 juni 2016
  • Patchlevel 12 – week van 4-8 april 2016
  • Patchlevel 11 – week van 25-29 januari 2016
  • Patchlevel 10 – week van 30 november en 5 december 2016

Note: Patch delivery dates are subject to change without prior announcement.

 

Procedure SQL 2005/2008/2012 to enable Xp_CmdShell extended

If you want to  enable Xp_CmdShell extended stored procedure in Sql in order to execute or write data from within OS.

Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure ‘sys.xp_cmdshell’ of component ‘xp_cmdshell’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘xp_cmdshell’ by using sp_configure. For more information about enabling ‘xp_cmdshell’, see “Surface Area Configuration” in SQL Server Books Online.

The following commands can be applied from within Management Studio:

EXEC sp_configure ‘show advanced options’, 1
GO
— To update the currently configured value for advanced options.
RECONFIGURE
GO
— To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
— To update the currently configured value for this feature.
RECONFIGURE
GO

SAP Business One dashboards en Adobe flash (14.x & 15.x) werken niet meer

met de laatste versie van Adobe Flash (14.x & 15.x) problemen te zijn i.c.m. de dashboards van SAP. Is sommige gevallen worden de dashboards leeg weergegeven. Aangezien Xcelsius en dashboards Adobe flash gebruiken moet er een downgrade van Flash verricht wanneer dashboards niet meer werkt.

De oplossing

Downgrade naar Flash Player 13, hiervoor moet je het volgende doen:

1. Uninstall de Adobe flash player welke op dit moment op het systeem van de gebruiker geïnstalleerd staat. Dit staat omschreven in de volgende link:

http://helpx.adobe.com/flash-player/kb/uninstall-flash-player-windows.html#main_Download_the_Adobe_Flash_Player_uninstaller

2. Download Adobe flash versie 13.0.0.231 van onderstaande link:

http://download.macromedia.com/pub/flashplayer/installers/archive/fp_13.0.0.231_archive.zip

3. installeer de flashplayer: flashplayer13_0r0_231_winax.exe uit de folder 13_0_r0_231

Niet werken van op HANA gebaseerde CR reports (B1A)

Er is een technisch conflict tussen Crystal Reports 13.0.8 en sommige SAP Business One Patches

Gebruik CR runtime 13.0.8 of hoger niet voor de volgende patches:

  • SAP Business One 8.82 PL16 of lager
  • SAP Business One 9.0 PL8 of lager

Gebruik altijd CR runtime 13.0.8 of hoger voor de volgende patches:

  • SAP Business one 8.82 PL17  of hoger
  • SAP Business One 9.0 PL9 of hoger
  • SAP Business One 9.1 of hoger

Hoe lees je de diskspace uit van SQL server

schakel command shell in wanneer dit nog niet gedaan is:

declare @chkCMDShell as sql_variant
select @chkCMDShell = value from sys.configurations where name = ‘xp_cmdshell’
if @chkCMDShell = 0
begin
EXEC sp_configure ‘xp_cmdshell’, 1
RECONFIGURE;
end
else
begin
Print ‘xp_cmdshell is already enabled’
end

daarna kan het volgende commando uitgevoerd worden. zorg er wel voor het account waarmee de query uitgevoerd wordt de “ALTER SETTINGS permission” actief is (sysadmin en serveradmin fixed server rol).

declare @svrName varchar(255)
declare @sql varchar(400)
–by default it will take the current server name, we can the set the server name as well
–set @svrName = @@SERVERNAME –> when no instance
set @svrName =CONVERT(VARCHAR(128),SERVERPROPERTY(‘machinename’) )– @@SERVERNAME –> with instance
set @sql = ‘powershell.exe -c “Get-WmiObject -ComputerName ‘ + QUOTENAME(@svrName,””) + ‘ -Class Win32_Volume -Filter ”DriveType = 3” | select name,capacity,freespace | foreach{$_.name+”|”+$_.capacity/1048576+”%”+$_.freespace/1048576+”*”}”‘
–creating a temporary table
CREATE TABLE #output
(line varchar(255))
–inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
–script to retrieve the values in MB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(‘|’,line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) as Float),0) as ‘capacity(MB)’
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) )) as Float),0) as ‘freespace(MB)’
from #output
where line like ‘[A-Z][:]%’
order by drivename
–script to retrieve the values in GB from PS Script output
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(‘|’,line) -1))) as drivename
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) as Float)/1024,0) as ‘capacity(GB)’
,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘*’,line) -1)-CHARINDEX(‘%’,line)) )) as Float) /1024 ,0)as ‘freespace(GB)’
from #output
where line like ‘[A-Z][:]%’
order by drivename
–script to drop the temporary table
drop table #output

controleren van consistentie op UDT (User Defined Tables)

het kan soms voorkomen dat de UDT’s van SAP corrupt raken. Om hiervan zeker te zijn, kunnen een aantal controle queries en ook “opruim queries” uitgevoerd worden:

/*1) UDT is defined but does not exist.*/

select TableName from OUTB

where not exists (

select 1 from sysobjects where xtype=’U’ and name=’@’+TableName

)

/*2) UDF is not registered.*/

select * from sysobjects where [name] like ‘@%’ and name not in

(select ‘@’+tablename from outb

union all

select ‘@’+logtable from outb where logtable is not null) and xtype=’U’

/*3) UDF is defined on an unregistered UDT.*/

select T0.TableID from CUFD T0 where left(T0.TableID,1)=’@’ and

not exists (select 1 from OUTB T1 where ‘@’ +T1.TableName =

T0.TableID or ‘@’+T1.LogTable= T0.TableID)

/*4) UDF is defined but does not exist.*/

select TableID, AliasID from CUFD where not exists (

select t0.name, t1.name

from sysobjects t0 inner join syscolumns t1

on t0.xtype=’U’ and t0.id=t1.id

where t0.name=TableID and t1.name=’U_’+AliasID)

and TableID not in (‘BTNT’, ‘OIBT’, ‘OSRI’, ‘SRNT’)

/*5) UDF valid values are defined for non-existing UDF.*/

select TableId, FieldID from UFD1 T

where not exists (

select 1 from CUFD where TableId=T.TableId and FieldID=T.FieldID

)

/*6) UDF definition does not match actuality.*/

select T1.UDF, T0.nvarchar_size as ‘act_size’, T1.nvarchar_size as ‘def_size’ from (

select T2.name + ‘.’ + T3.name as ‘UDF’, T3.length/2 as ‘nvarchar_size’ from sysobjects T2 inner join syscolumns T3 on T2.id=T3.id where T2.xtype=’U’ and T3.xtype in

(select xtype from systypes where name=’nvarchar’)

) T0

inner join (

select tableid + ‘.U_’ + aliasid as ‘UDF’, editsize as ‘nvarchar_size’

from cufd where typeid=’A’ and editsize>1

) T1

on T0.UDF=T1.UDF

where T0.nvarchar_size>T1.nvarchar_size

/*7a) UDF contains extra spaces in field TableID in User-fields description CUFD table.*/

select * from CUFD

where datalength(TableID)<>LEN(TableID)

and ascii(SUBSTRING (TableID, LEN(TableID)+1, 1)) =32

/*7b) UDF contains extra spaces in field AliasID in User-fields

description CUFD table*/

select * from CUFD

where datalength(AliasID)<>len(AliasID)

and ascii(SUBSTRING (AliasID, LEN(AliasID)+1, 1)) =32

/*8) UDF exists but is not defined*/

select T1.name, T0.name from sys.columns T0 join sys.objects T1 on T0.object_id = T1.object_id

left join CUFD T2 on T2.TableID = T1.name and (‘U_’ + T2.AliasID) = T0.name

where T1.type = ‘U’ and T0.name like ‘U/_%’ escape ‘/’

and (‘U_’ + T2.AliasID) is null

and (T0.name !=’U_NAME’ and T1.name not in (‘OUSR’, ‘AUSR’))

 

 

/*************************** Update Query with description: ***************************/

 

/*1)Below query will delete all users defined tables which are defined but not exist in SQL Server.*/

delete OUTB where ‘@’+TableName not in (

select name from sysobjects where xtype=’U’)

/* 2 ) We recommend to remove the tables displayed by the query from the database directly in the database interface. Please consider this solution according to your customization. */

/*3) Below query will delete all users defined fields which are defined on an unregistered UDT.*/

delete from CUFD where left(TableID,1)=’@’ and not exists (

select 1 from OUTB where ‘@’+TableName = TableID or

‘@’+LogTable = TableID)

/*4) Below query will delete all users defined fields which are defined but not exist in SQL Server.*/

delete CUFD where not exists (

select t0.name, t1.name

from sysobjects t0 inner join syscolumns t1

on t0.xtype=’U’ and t0.id=t1.id

where t0.name=TableID and t1.name=’U_’+AliasID)

and TableID not in (‘BTNT’, ‘OIBT’, ‘OSRI’, ‘SRNT’)

/*5) Below query will delete all users defined fields’ valid values which are defined for non-exist UDF.*/

delete UFD1 where not exists (

select 1 from CUFD where TableId=UFD1.TableId and FieldID=UFD1.FieldID

)

/*6) Update the size of the UDF in Application.*/

/*7)Below query will trim extra spaces in the table name. */

/*7a) extra spaces in TableID of CUFD:*/

update CUFD

set TableID = replace (TableID,’ ‘, ”) where datalength(TableID)<> LEN

(TableID) and ascii(SUBSTRING (TableID, LEN(TableID)+1, 1)) =32

/*7b) extra spaces in AliasID of CUFD:*/

update CUFD

set AliasID = replace (AliasID,’ ‘, ”) where datalength(AliasID)<> LEN

(AliasID) and ascii(SUBSTRING (AliasID, LEN(AliasID)+1, 1)) =32

/*8) We recommend to remove the columns displayed by the query from the database directly in the database interface. Please consider this solution according to your customization.*/

 

%d bloggers op de volgende wijze: