Wednesday, March 19, 2014

Configure SQL user to Write to the Event log

The case scenario is : I'm using a SSIS Script task to zip a large number of files and I want to log the 7zip errors to the Event log using System.Diagnostics. Here is the procedure I use to allow the SQL user to write to the event log.

You will need wevtutil normally found in %system32%, if not available you will have to use the registry editor.
1. configure credential for the user
2. determine permissions for the application log : wevtutil gl application > D:\temp\out.txt
3. open the file out.txt :
           name: application    
           enabled: true    
           type: Admin    
           owningPublisher:    
           isolation: Application    

            channelAccess: O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0x7;;;BA)(A;;0x7;;;SO)(A;;0x3;;;IU)(A;;0x3;;;SU)(A;;0x3;;;S-1-5-3)(A;;0x3;;;S-1-5-33)(A;;0x1;;;S-1-5-32-573)
   
            logging:
  logFileName: %SystemRoot%\System32\Winevt\Logs\application.evtx
  retention: false
  autoBackup: false
  maxSize: 20971520
  publishing:
  fileMax: 1

4.          Using the Registry Editor (see original post  from Beth Massi)

You can allow any authenticated user write access to your log or you can allow the specific user SID write access. Open up the registry editor to your event log CustomSD key:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Eventlog\MyLogName\CustomSD

To allow all authenticated users write access add the following security permission:
(A;;0x0002;;;AU)
To allow a specific user write access you would specify their SID:
(A;;0x0002;;;SID-OF-USER-ACCOUNT) 
5.       Modify the registry using wevtutil 
 wevtutil sl Application /ca:<updated channel line>

 Example to give read/write access to authenticated users will will use the 0x3 value:
 wevtutil sl Application /ca: O:BAG:SYD:(A;;0xf0007;;;SY)(A;;0x7;;;BA)(A;;0x7;;;SO)(A;;0x3;;;IU)(A;;0x3;;;SU)(A;;0x3;;;S-1-5-3)(A;;0x3;;;S-1-5-33)(A;;0x1;;;S-1-5-32-573)(A;;0x3;;;AU)

Friday, February 21, 2014

Shrink TempDb Without Restart

USE tempdb
GO
--sp_helpfile
DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
go
DBCC FREESYSTEMCACHE ('ALL')
GO
DBCC FREESESSIONCACHE
GO
--second parameter is targe in percent
DBCC SHRINKDATABASE(tempdb,10)

Monday, January 27, 2014

SSIS Parameter cannot be derived from sub-select queries


If you’re trying to use a parameter in a sub query using the SSIS SQL task you may encounter this error : image

Edit the SSIS Execute SQL Task and check the ByPassPrepare option
Currently it is set to false, and we generate the error above.

image
Set the Set the ByPassPrepare option to True:
  image
The task will now execute successfully !!

Monday, September 30, 2013

List All columns of a Table with their data type

USE AdventureWorks2008
GO 
SELECT t.name, 
t1.name,
t.name,
t1.name,
c.name,
c.max_length,
c.precision,
c.collation_name
FROM sys.TABLES t JOIN sys.COLUMNS c ON t.object_id = c.object_id JOIN sys.types t1 
ON c.user_type_id = t1.user_type_id WHERE t.name = 'Employee'
 

How to check the installed version of powershell

 

The Easy Way, in the powershell prompt type:

PS D:\> $host.Version

Major  Minor  Build  Revision
-----  -----  -----  ------
2      0      -1     -1

Wednesday, August 21, 2013

system_health session incidentally dropped

The session can be recreated using the file u_tables.sql (the script is at the bottom of the file) that is located in :

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Install\