Devcon - Controlling Devices from the command prompt.

Devcon - Controlling Devices from the command prompt.

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 >

D:\devcon\i386>devcon classes

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).
WPDBUSENUMROOT\UMB\2&37C186B&0&STORAGE#VOLUME#_??_USBSTOR#DISK&VEN_CORSAIR&PROD_
UFD&REV_1100#AA23000000001534&0#: G:\

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
evice
1 matching device(s) found.

Finally, we can issue a command to safely remove the device >

D:\devcon\i386>devcon remove *corsair*

USBSTOR\DISK&VEN_CORSAIR&PROD_UFD&REV_1100\AA23000000001534&0: Removed
1 device(s) removed.

Remote Desktops Tool for Windows 7

Remote Server Administration Tools has been updated for you lucky people running the Release Candidate of Windows 7.

RSAT for Windows 7 Release Candidate (RC)
http://www.microsoft.com/downloads/details.aspx?FamilyID=f6c62797-791c-48e3-b754-c7c0a09f32f3&displaylang=en


RSAT for Vista / AdminPak for XP >
http://sqlsolace.blogspot.com/2008/09/remote-desktops-tool-for-vista.html

Grant Permissions to run all stored procedures

An update on a previous post.

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'

Management Studio Error - .Net Framework

Following an epic HDD fail (dead drive) in my work desktop pc, I reinstalled SQL 2008 on my new OS (support gave me new drive and image of company OS).

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,

1. Navigate to C:\Documents and Settings\ [username] \Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell .
   
   
2. Rename the old RegSrvr.xml to something else e.g. RegSrvr.bak
   
   
3. 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

SSIS Error : The oledb connection manager could not be edited

Returned to a previously fine SSIS package and was presented with this error :

" 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 >

regsvr32 dtsconn.dll

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/1048197e-3769-4084-b9ed-208aef0f83fc/

SQL 2008 : New Downloads

SQL 2008 Books Online (May 2009)

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=765433f7-0983-4d7a-b628-0a98145bcb97


SQL Server 2008 Developer Training Kit

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=e9c68e1b-1e0e-4299-b498-6ab3ca72a6d7

Simultaneous RDP Connections in Windows 2008

By default, when you connect via RDP to a new Windows 2008 install, you are connected in "Remote Administration" mode. This is because Terminal Services is not installed.

(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. "

Management Studio Error : "Object reference not set to an instance of an object. (SQLEditors)"

Recieved this error in SQL 2005 Management Studio on a server when trying to open some tables.

"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!

Off Topic : Google Fail

Interesting plane analogy to describe yesterday's slowdown...

http://googleblog.blogspot.com/2009/05/this-is-your-pilot-speaking-now-about.html

Running SQL Server locally

An update on a previous post.
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

New SQL Whitepaper : Disk Allignment

Disk Partition Alignment Best Practices for SQL Server

http://msdn.microsoft.com/en-us/library/dd758814.aspx

SSIS : Package Configuration

Excellent article on package configuration

http://www.sqlservercentral.com/articles/SSIS/66500/ *

* SQLServerCentral.com requires registration, but I cannot stress enough how valuable it is.

SQL 2008 : Management Studio Error

SQL 2008 : Management Studio fails to launch with the following error following some Windows Updates >

" Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.VisualStudio.OLE.Interop.IServiceProvider'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{6D5140C1-7436-11CE-8034-00AA006009FA}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). (Microsoft.VisualStudio.OLE.Interop) "

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

SQL 2008 : R2 - Coming Soon!

Coming soon!

http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx

http://sqlserverpedia.com/blog/sql-server-bloggers/sql-server-2008-r2/

SSIS Error : Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state."

A SSIS Package Execution Error

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)



http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/749bb98a-5317-43bd-a548-e0cdef5a12d9
http://www.cubido.at/Blog/tabid/176/EntryID/155/Default.aspx

SQL Agent Job Failures

The job failed. The owner () of job import jobs | jobname does not have server access.

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
Server SQL05-UAT
Job Name import jobs | jobname
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
The job failed. The owner () of job import jobs | jobname does not have server access.

Link
http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/47e7bc08-efa6-4a80-b8cd-1df92f10e7bc/

SSIS : SSIS for beginners

Good post on this site >

http://movetothetop.com/ssisexamples/index.php/2009/01/8-simple-rules-for-ssis-beginners/

Reporting Services 2008 : Customising Report Manager

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>

.msrs-uppertitle
{
font-family:Verdana;
font-size:x-small;
}

4) Change it to >

.msrs-uppertitle
{
font-family:Verdana;
font-size:x-small;
    BACKGROUND: url(http://ServerName/Reports/Images/CompanyLogo.JPG) no-repeat;
    HEIGHT: 42px;
    WIDTH: 155px;
    TEXT-INDENT: -5000px;
POSITION:absolute;
TOP: 15px;
RIGHT:330px;
}

Here's where I found out >
http://weblogs.asp.net/jgalloway/archive/2006/12/12/reporting-services-add-a-logo-to-the-report-manager.aspx

SQL 2008 : Policy Based Management - Building and Testing a Policy

Step by step guide to build and test a policy in SQL 2008's Policy Based Management (PBM) >

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 >

SQL 2008 : Policy Based Management - Basics

Policy Based Management enables you to enforce and audit standards across multiple SQL 2008 instances through user defined rules (policies).

3 new terms are associated with Policy Management.

1. Policy - A group of conditions to be checked/enforced
2. Conditions - State of Facets (true/false).
3. 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
http://sql-articles.com/blogs/policy-based-management-pbm/
http://blogs.msdn.com/sqlpbm/
http://www.mssqltips.com/tip.asp?tip=1492

SQL 2008 : Resource Governor

Resource Governor enables dynamic (on the fly) allocation of SQL Server resources.

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

NB : CPU is controlled per Scheduler, not accross all schedulers.

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 %
Internal	0	100	100	100
Default	0	100	80	20
SQL_App	10	40	20	10
SQL_Reports	10	40	20	10
SQL_Admin	0	25	5	5

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
);
GO

2. 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
GO

To 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

IE8: The best thing

The best thing about Internet Explorer 8?

The brunette on the post install page....