Terry L@u's blog: Move the IIS default path on Windows Server 2008 o...

Terry L@u's blog: Move the IIS default path on Windows Server 2008 o...: For security reason, administrators might be needed to move the default IIS path, Inetpub, to non-system drive.  According to KB2752331 ,...



The only thing is I don't think running the xcopy command at the end is useful as it is contained in the microsoft script line 23.

Recursive Relationship in Visio data model

Drawing E-R Diagrams with Crow’s Foot Notation using Microsoft Visio Tutorial

Drawing E-R Diagrams with Crow’s Foot Notation using Microsoft Visio Tutorial

Clear Search History of Notepad on Windows :: ajibanda

Clear Search History of Notepad on Windows :: ajibanda

ORA-00439: feature not enabled: Deferred Segment Creation on datapump import

Life and Oracle: ORA-00439: feature not enabled: Deferred Segment C...: Short version: If you get this error on a data pump import: ORA-39083: Object type TABLE:"SCOTT"."EMP" failed to creat...

Find User SID using Powershell

These snippets allow to find a local or AD user SID or get the username from the SID

Rename all files in directory using powershell part 2

Using Powershell 4 :

Rename all files in a directory using Powershell

 

Open dtsconfig files in Notepad ++ (formatted as xml)

I often use Notepad++ as a quick way to open text files, and I want to open dtsconfig files formatted as xml :
in the settings Menu chose style configurator

then add the dtsconfig file extension to xml config:

SSRS 2008R2 refuses to start after upgrade from SSRS 2008

Scenario : After upgrading from SQL 2008 to SQL 2008 R2, reporting services refuses to start. All other SQL server services run normally after the upgrade. steps performed:

 If a AD Domain account is used, it has to be local admin for SSRS To start.
 Local services account do not work either.
uninstall/reinstall reporting services brought another issue : the setup finds - fails the IA64 OS check. The OS is not IA64.
Tried using the command line with the SKIPRULES switch without success.
New error Message : "SQL Path Element is missing" and the setup fails. 

I spent quit some time on Google looking for a solution for this, none of which works. I finally found out how to fix the issue:
 From the command line run
SETUP.EXE /ACTION=RunDiscovery.
On the report below we can notice 2 SQL2008 component for which the instance is highlighted : MSSQLSERVER.INACTIVE

Resolution Steps to remove the inactive instance:

 First search for files called Datastore_discovery.xml and open the most recent one. List of files left after uninstalling SQL Server Look for the places containing "MSSQLSERVER.INACTIVE" below is a sample

Urn="Machine[@ID='IWE']/Product[@ID='SQLVNEXT']/Instance[@ID='MSSQLSERVER.INACTIVE']" ID="MSSQLSERVER.INACTIVE" Name="MSSQLSERVER.INACTIVE" ........

Then look for the related lines containing ProductCode = "{B5153233-9AEE-4CD4-9D2C-4FAAC870DBE2}" (of course yours will be different)
The number of times you have to look for these product codes depend on how many components are listed as inactive. For each of the product codes that you found run: ** msiexec /x {B5153233-9AEE-4CD4-9D2C-4FAAC870DBE2} **
Do this for each product code related the MSSQLSERVER.INACTIVE that you found.

Then run setup again, click on Tools then "Installed SQL Server Features discovery report". If you can still see an instance MSSQLSERVER.INACTIVE you missed one of the Product Codes.

 After cleaning the server completely, I noticed : The existing SQL SERVER 2008R2 instance , SSIS etc.. were still running. I reinstalled SSRS and everything went fine. I did not perform any additional steps.

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)

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)

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 :

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


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

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'

 

Download Code

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

Nice Free Script Generator - SQL Script Generator

SQL Script Generator Features

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\

Perfmon Counter poster (cheat sheet)

 

I really like this one from Quest Software.

List disabled Jobs and their schedule

To List the disabled Jobs and their relative schedules , you can run the following query:

USE msdb
GO
SELECT
 ss.schedule_id ,
 J.name,
 ss.name,
 J.enabled [Job Enabled ?],
 ss.enabled [Schedule ENABLED ?]
FROM sysjobs J
JOIN sysjobschedules s
 ON J.job_id = s.job_id
JOIN sysschedules ss
 ON s.schedule_id = ss.schedule_id
WHERE J.enabled = 0

The schedule_id can be used directly with sp_update_schedule, as from SQL 2008 schedules can be managed independantly of jobs.

List all tables with their schema name in a database

SELECT T.name ,S.name 
FROM sys.tables T JOIN sys.schemas S ON T.schema_id = S.schema_id              
ORDER BY T.name ASC