Archive for 2013

Who’s in those AD Windows Groups setup on SQL Server?

I wanted to be able to check which windows users had been placed in the Windows AD Groups we use to control access to SQL Server. Here’s what I came up with to make checking this easy; ?View Code POWERSHELLImport-Module SQLPS -DisableNameChecking -ErrorAction Ignore; Import-Module ActiveDirectory -DisableNameChecking -ErrorAction Ignore;   $sql_server = "sql_instance"; $srv = […]

SSIS: Failed to load XML from package file

I recently came across this error in one of our SSIS packages. Description: Failed to load XML from package file “C:\Path\To\Package.dtsx” due to error 0xC00CE508 “An invalid character was found in text content. Line 426, Column 104”. This happens when loading a package and the file cannot be opened or loaded correctly into an XML […]

TSQL to help with mirroring failover

We ran a mock fail-over from our staging cluster to an offsite mirror server today. Here’s a few queries I used to help me get this done. I first took a backup of the SQL Agent jobs status on each server so I could easily return the jobs to their previous status on each server. […]

Use sys.sql_modules not INFORMATION_SCHEMA.ROUTINES

Have you ever tried using INFORMATION_SCHEMA.ROUTINES in SQL Server for searching for all procs referencing a specific object? Perhaps something like this; ?View Code TSQLSELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE ‘%table_name%’; Has this ever bitten you? If not then you’ve been lucky so far. The ROUTINE_DEFINITION column is limited to 4000 characters so you […]

Twitter lists with Tweet-SQL 3.6

Here’s a quick post about viewing Twitter lists with Tweet-SQL 3.6. First you can view your lists by running; ?View Code TSQLEXEC dbo.tweet_lists_list null, null, null; This will show the lists followed by the authenticating user. To work with specific list tweets copy the appropriate @list_id into the script below and execute it. ?View Code […]