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)

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\

compare 2 tables with different names or schema

Problem:

You have 2 tables that you would like to compare at the column level (retrieve a list of the columns with different data types and/or names).
It happened to me today and the tables had 100+ columns so...

Alternative 1:

if the tables have the same names and a primary key , you can use one hidden gem of SQL Server (since version 90) called tablediff.exe .

Alternative 2:

My problem is the following , both tables belong to the same schema but hold different names. One of the tables is a simple import table (used to import text files) and has no primary key. Archiving the data from the import table to the destination table fails due a potential data truncation.

For the sake  of the example I will a use a database called TestDb that have 2 tables : books and books 1.

As you can see here the only difference is the BookTitle field that has a different length.
Run the following query :

The except statement will select (without duplicates) the elements contained in the left table that are not present in the right table (here books1)
here is the result:

Rgds.

sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)

We still have an SQL 2000 production server from a third party supplier. The backups failed for several days and looking on serveral blogs I found that some had disk space issues.

I did not have a disk space issue BUT a consultant from the third party had restricted the rights of the DATA folder to administrators only !

so if you run into this error :

1. check that you have sufficient disk space
2. check the data folder security :)

SQL setup fails to update - installer cache is missing files

installing the service pack you are receiving the following error message:

a link to correct this problem can be found here but still it is quite tricky.

You can download a file, FindSQLInstalls.vbs to gather the details of your SQL Server  installation. I had errors running the file at first , removing the comments at the top of the file solved the issue.

Mostly I found errors relating to missing path, both the original cd that was copied in a directory, and directory created  by the SQL server setup that were missing like d:\6e712df430a776566d80fe027ca507.

recreating the file to the original setup cd on the disk was mandatory, I tried to recreate the setup directory above (d:\6e712df430a776566d80fe027ca507) as a shortcut for the setup to relink source and destination files within  the c:\windows\installer directory, but the latter did not work.

I didn't have to copy all the files listed in the one created by the vbs script - I relaunched the Setup 4-5 times, looked for the missing files in the .txt file and finally went through the SP3 setup normally.

Steps:

1. Go to http://support.microsoft.com/kb/969052 and download the FindSQLInstall.vbs script
2. Using a command prompt (elevated privileges), run  Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt
3. open the text file to view the details.
4. Recreate the path to the original setup cd if required
5. Try to rerun setup and look in the text file for the missing file, copy it in the %windir%\installer directory (serveral trials may be required if serveral files are missing as in my case)