Friday, May 29, 2009
1) Download Devcon from here > http://support.microsoft.com/?kbid=311272
2 ) Open an Administrator command prompt
3) Use Devcon!
To see the classes of devices installed >
Listing 58 setup class(es).
WCEUSBS : Mobile devices
USB : Universal Serial Bus controllers
Media Center Extender: Media Center Extender
PnpPrinters : IEEE 1394 and SCSI printers
Dot4 : IEEE 1284.4 devices
Dot4Print : IEEE 1284.4 compatible printer
CDROM : DVD/CD-ROM drives
Computer : Computer
DiskDrive : Disk drives
Display : Display adapters
fdc : Floppy drive controllers
hdc : IDE ATA/ATAPI controllers
Keyboard : Keyboards
MEDIA : Sound, video and game controllers
Modem : Modems
Monitor : Monitors
Mouse : Mice and other pointing devices
MTD : Memory technology driver
MultiFunction : Multifunction adapters
Net : Network adapters
NetClient : Network Client
NetService : Network Service
NetTrans : Network Protocol
PCMCIA : PCMCIA adapters
Ports : Ports (COM & LPT)
Printer : Printers
SCSIAdapter : Storage controllers
System : System devices
Unknown : Other devices
FloppyDisk : Floppy disk drives
Processor : Processors
MultiPortSerial : Multi-port serial adapters
Memory : Memory devices
SmartCardReader : Smart card readers
Sensor : Sensors
VolumeSnapshot : Storage volume shadow copies
BiometricDevice : Biometric Devices
1394 : IEEE 1394 Bus host controllers
Infrared : Infrared devices
Image : Imaging devices
TapeDrive : Tape drives
Volume : Storage Volumes
Battery : Batteries
HIDClass : Human Interface Devices
61883 : 61883 Device Class
LegacyDriver : Non-Plug and Play Drivers
SmartCard : Smart cards
SideShow : Windows SideShow
SDHost : SD host adapters
Transfer Cable : Transfer Cable Devices
AVC : AVC Devices
MediumChanger : Medium Changer devices
SBP2 : SBP2 IEEE 1394 Devices
XnaComposite : Microsoft Common Controller For Windows Class
SecurityDevices : Security Devices
SmartCardFilter : Smart Card Filter
Bluetooth : Bluetooth Radios
WPD : Portable Devices
The class for Portable Devices is 'WPD'
List all Portable devices currently plugged in >
D:\devcon\i386>devcon listclass wpd
Listing 1 device(s) for setup class "WPD" (Portable Devices).
Yes, my current USB drive is made by Corsair!
If we'd already have known part of the name, we could have found like this >
D:\devcon\i386>devcon find *corsair*
USBSTOR\DISK&VEN_CORSAIR&PROD_UFD&REV_1100\AA23000000001534&0: Corsair UFD USB D
1 matching device(s) found.
Finally, we can issue a command to safely remove the device >
D:\devcon\i386>devcon remove *corsair*
1 device(s) removed.
Wednesday, May 27, 2009
RSAT for Windows 7 Release Candidate (RC)
RSAT for Vista / AdminPak for XP >
Tuesday, May 26, 2009
This version includes table schema and uses information_schema.routines rather than sysobjects.
select 'GRANT EXECUTE ON [' + specific_schema + '].['+routine_name+'] TO [LoginName]' from information_schema.routines where routine_type = 'procedure'
Saturday, May 23, 2009
On attempting to open Management Studio (after successful SQL install), I was greeted with this...
" Unhandled exception has occurred in a component in your application. If you
click Continue, the application will ignore this error and attempt to
continue. Key not valid for use in specified state. "
Clicking 'Details' was not much more help...
" ************** Exception Text **************
System.Security.Cryptography.CryptographicException: Key not valid for use
in specified state.
at System.Security.Cryptography.ProtectedData.Unprotect(Byte encryptedData, Byte optionalEntropy, DataProtectionScope scope)
When Management Studio opened, 'Registered Servers' was empty. I mean totally empty.
Database engine icon was missing and i could not even add servers >
Basically, my roaming profile had bought with it an old Management Studio config file that could not be opened.
To solve this,
- Navigate to C:\Documents and Settings\ [username] \Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell .
- Rename the old RegSrvr.xml to something else e.g. RegSrvr.bak
- Start Management Studio again (it will recreate the file and you'll be able to add your servers again)
Link : http://www.dbtalk.net/microsoft-public-sqlserver-tools/ssms-2008-a-512328.html
Friday, May 22, 2009
" The oledb connection manager could not be edited "
The solution was yet another DLL needing re-registering following recent updates!
From an Administrator command prompt, type >
SQL Server 2008 Developer Training Kit
Thursday, May 21, 2009
(NB : Terminal Services is to be renamed 'Remote Desktop Services' in Windows 2008 R2)
Remote Administration Mode only allows 2 connections. Try and increase it (you can't) >
This is less than Windows 2003, where 3 admins could log in at the same time (1 using the /console switch for session 0). Windows 2008 does at least allow you to easily see who has the open sessions so you can disconnect them (or ask politely if the mood takes you! )
From (http://blogs.msdn.com/rds/archive/2007/12/17/changes-to-remote-administration-in-windows-server-2008.aspx) >
" Behavior when you connect to a server that does not have Terminal Server installed >
If you (as a member of the Administrators group on the destination server) start a Remote Desktop session to a Windows Server 2008-based server that does not have the Terminal Server role service installed, the following behavior is true for the remote administration session:
• Time zone redirection is disabled.
• Terminal Services Session Broker (TS Session Broker) redirection is disabled.
• Plug and Play device redirection is disabled.
• The remote session theme is changed to Windows Classic.
• Terminal Services Easy Print is disabled. "
Sunday, May 17, 2009
"Object reference not set to an instance of an object. (SQLEditors)"
The databases concerned are fine, and can be accessed remotely from Management Studio.
After a lot of googling, all anyone could come up with was to reinstall / repair SQL using the installation program.
The actual solution wasn't actually that bad however.
I attempted to re-apply service pack 3 , this time downloading it fully.
What had happened was that the SP3 download I allowed Windows Update to install had failed to apply SQL 2005 SP3 to SQL Client Compentents.
Therefore SP3 saw this was the only element it needed to install, did so, and Management Studio worked again!
Friday, May 15, 2009
Thursday, May 14, 2009
If you run SQL Server on a laptop or local desktop for development / research purposes, then you'll want to turn SQL Services on and off when needed so they don't interfere with other work.
To do so, create batch files for starting and stopping the services.
The commands you want take the form >
NET START SERVICENAME
NET STOP SERVICENAME
service names are >
- MSSQLSERVER - Database Engine
- MSDTSSERVER100 - SSIS / Integration Services
- REPORTSERVER - SSRS / Reporting Services
- MSSQLSERVEROLAPSERVICE -- SSAS / Analysis Services
so , if you're really lazy, cut & paste the following into batch files >
NET START MSSQLSERVER
NET START MSDTSSERVER100
NET START REPORTSERVER
NET START MSSQLSERVEROLAPSERVICE
NET STOP MSSQLSERVER
NET STOP MSDTSSERVER100
NET STOP REPORTSERVER
NET STOP MSSQLSERVEROLAPSERVICE
* SQLServerCentral.com requires registration, but I cannot stress enough how valuable it is.
Wednesday, May 13, 2009
The solution ?
1) Launch a command prompt as Administrator
2) Run regsvr32 actxprxy.dll
It's an ActiveX Interface Marshalling Library (whatever that means!) that needs to be re-registered.
Link : http://msmvps.com/blogs/martinpoon/archive/2009/03/20/ssms-unable-to-cast-com-object-of-type-system-comobject-to-interface-type-microsoft-visualstudio-ole-interop-iserviceprovider-exception-from-hresult-0x80004002-e-nointerface.aspx
Monday, May 11, 2009
SSIS Error : Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state."
Got this when scheduling a SSIS package via SQL Server Agent
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state."
The developer had a saved the package with a stricter security level.
I solved by changing the ProtectionLevel property to DontSaveSensitive
(The solution was using SSIS Package configuration files)
The message here is deceptive, and sent me down the route of checking permissions.
I knew I hadn't changed any permissions but I had to verify that a colleague hadn't either.
The job was owned at the time by a Windows account (I've since changed this).
There was an annoyingly simple solution to this problem.
Restart SQL Server Agent!
Basically, for reasons unknown (possibly the SQL VM being paused rater than restarted), the network service account that jobs were running under could no longer be verified by the SQL 2005 instance (lost contact with domain controller on restart or similar).
Date 11/05/2009 09:32:46
Log Job History (import jobs | jobname)
Step ID 0
Job Name import jobs | jobname
Step Name (Job outcome)
Sql Severity 0
Sql Message ID 0
Operator Net sent
Retries Attempted 0
The job failed. The owner () of job import jobs | jobname does not have server access.
Saturday, May 9, 2009
Friday, May 8, 2009
Customising Report Manager in SQL Reporting Services 2008
Here's how to add your company logo to 'Report Manager'
1) Copy image file to >
c:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager\images\companylogo.jpg
2) Edit Style Sheet at
c:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\ReportingServices\ReportManager\Styles\ReportingServices.css
3) Find this code>
4) Change it to >
BACKGROUND: url(http://ServerName/Reports/Images/CompanyLogo.JPG) no-repeat;
Here's where I found out >
Thursday, May 7, 2009
1) Locate 'Policy Management' in object explorer >
2) Right click 'Conditions' and select 'New Condition' >
3) Fill in the wizard!
Enter a name, select a Facet from the dropdown and enter expressions to be met for the condition to be met. The conditions available in the expressions box change depending on the Facet chosen >
4) Enter something useful in the description so any fellow administrators know what you've done! >
5) Create a policy to accompany (and audit/enforce) the condition >
6) Complete the New Policy wizard by
i. providing a policy name
ii. linking the policy to the condition you just defined
iii. specify the targets of the policy
iv. set the evaluation mode >
7) Again, something helpful in the description is nice >
8) Test the policy using 'Evaluate' >
9) The server has passed the policy :) >
10) Clicking 'view' under the details column reveals the specifics about the tests performed >
Wednesday, May 6, 2009
3 new terms are associated with Policy Management.
- Policy - A group of conditions to be checked/enforced
- Conditions - State of Facets (true/false).
- Facet - Manageable properties of SQL object.
Policy Based Mamagement (PBM) is found under 'Management on a SQL 2008 instance >
There are currently 84 'Facets' you can define policies for -
It's easy to list them via TSQL -
use msdb select name from dbo.syspolicy_management_facets go
or you can list all the facets and the target types and they can be applied to >
use msdb select f.name as facet_name ,fe.event_name ,fe.target_type ,fe.target_type_alias from dbo.syspolicy_management_facets f inner join dbo.syspolicy_facet_events fe on f.management_facet_id = fe.management_facet_id go
Useful PBM Links:
Getting a list of all Facets and their properties
Tuesday, May 5, 2009
Resource Governor consists of 3 factors -
1. Resource Pools
A resource pool is a set configuration settings.
You can adjust 4 factors per pool -
- Maximum Memory
- Minimum Memory
- Maximum CPU
- Minimum CPU
There are 2 standard resource pools, 'internal' and 'default'.
'Internal' controls system resources used by SQL Server.
It is not affected by the configuration of other user pools.
'Default' is the first user pool, initially defined to use all available resources.
Further resource pools are user defined. The effect of adding further pools is shown below -
Eff Max% = Max% - SUM (Min% of other pools)
Shared% = Eff Max% - Min%
|Resource Pool||Min %||Max %||Effective Max %||Shared %|
The same calculations apply equally to memory or cpu....
Tsql for creation of a resource pool -
CREATE RESOURCE POOL SQL_Reports WITH ( MAX_CPU_PERCENT = 25, MAX_MEMORY_PERCENT = 50 ); GO2. Workload Groups
Workload Groups are containers for sql server sessions.
They get applied to resource pools and can be moved freely between them.
CREATE WORKLOAD GROUP Adhoc_Reports USING SQL_Reports GO CREATE WORKLOAD GROUP Application_Reports USING SQL_Reports GOTo put sql sessions into groups, the sessions need to be classified.
3. Classification Functions
To allocate sql sessions to workgroups, they need to be classified.
To do this, write a Classifier Function.
This is really a SQL scaler UDF (user defined function) which looks at connection/session properties to determine session properties.
You could advocate different behaviour based on -
What is running the session - APP_NAME()
What the time is - GETDATE()
Who is executing the SQL - SUSER_SNAME()
CREATE FUNCTION ResourceGovClassifier() RETURNS SYSNAME WITH SCHEMABINDING BEGIN DECLARE @classification VARCHAR(32) IF SUSER_SNAME() = 'ReportingUser' SET @classification = ‘Application_Reports’ IF (APP_NAME() LIKE '%REPORT SERVER%') SET @classification = ‘Adhoc_Reports’ RETURN @classification END GO
Setting up Resource Governor to use the function -
ALTER RESOURCE GOVERNOR WITH ( CLASSIFIER_FUNCTION = dbo.ResourceGovClassifier) GO
Enable Resource Governor -
ALTER RESOURCE GOVERNOR RECONFIGURE GO
When defining the IMPORTANCE of a sesion, be aware it only applies to active workers of groups assigned to the same pool.
Not sure if all this is working?
Query Resource Governor sessions like this -
SELECT session_id, [host_name], [program_name], nt_user_name, groups.name as 'ResGov_Group', pools.name as 'ResGov_Pool' FROM sys.dm_exec_sessions usersessions INNER JOIN sys.dm_resource_governor_workload_groups groups ON usersessions.group_id = groups.group_id INNER JOIN sys.dm_resource_governor_resource_pools pools ON groups.pool_id = pools.pool_id