SQL Server Error: The ‘DbProviderFactories’ section can only appear once per file.

You ever get the error:

The ‘DbProviderFactories’ section can only appear once per file.

…after a fresh install of Microsoft SQL Server Express?

DbProviderFactores error

When trying to do something like create a new table with SQL Server Management Studio?

New Table

I don’t know why, but one (or more) of the many maching.config files on your computer does, indeed, have TWO DbProviderFactories sections in it.  TWO of my machine.config files were like that, but the one causing this error was the one located here:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\machine.config

TWO DbProviderFactores

[GARD]

I recommend doing a system wide search for all machine.config files that have <DbProviderFactories/> in them.  Open each one to see if it additionally has another DbProviderFactories section and delete the blank one.  Deleting it from the file mentioned above solved this problem for me.

NOTE!  You’ll have to open your text editor as Administrator or you’ll be forbidden from saving changes to the file(s)!

See these images?

image

You’ll find an actual working versions of them at the top and bottom of this article. Please click the appropriate buttons in it to let your friends know about this article.

Check back later for updates too!

Subversion 403 forbidden error

Are you getting a “403 forbidden” error when trying to create a folder or check code into subversion, but you can browse the repo?

[GARD]

There are many causes for this, but one of them that’s very difficult to find is caused by the casing of the URL.  When you check out from subversion, you can use any casing.  When you check IN, you’d better be using the ExAcT casing.  And don’t think that just because you’re using a GUI like TortoiseSVN that it does it for you.  IT DOES NOT!

See these images?

image

You’ll find an actual working versions of them at the top and bottom of this article. Please click the appropriate buttons in it to let your friends know about this article.

Check back later for updates too!

RegAsm.exe “failed to load” “because it is not a valid .net assembly”

Problem

Ever get this error when trying to register a .Net DLL as an ActiveX component using RegAsm.exe?

“failed to load MyAssembly.dll because it is not a valid .net assembly”

I’ll bet you’re using a version of .Net higher than 2.0, aren’t you?  Turns out, each version of .Net you have installed has it’s own, version specific version of RegAsm.exe and when you run the RegAsm.exe command for your .Net 4.0 or so DLL, it’s running the .Net 2.0 version of RegAsm.exe.

Windows Registry Forensics
Windows Registry Forensics

Why make an ActiveX control if you’re using .Net?  Sometimes you just have to for many reasons which are outside the scope of this article.

Solution

[GARD]

There are a couple of ways to resolve this.

Specify Specific Version

If you want to specifically state which version to run, make a batch file for each version and name the batch file appropriately, and have that batch file have the hard path to the right RegAsm.exe.  For example, for the .Net 4.0 version of RegAsm, I created a batch file called RegAsm_4_0.bat and put it in the same folder with the RegAsm.exe file for .Net 4.0 here:

C:\Windows\Microsoft.NET\Framework\v4.0.30319

The contents of my RegAsm_4_0.bat file looks like this:

C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm.exe %1 %2

Now, from any Visual Studio command prompt (regardless of what folder I’m in), I type RegAsm_4_0 (plus my parameters) and it’ll run it.

Override default version

If, on the other hand, you want to continue using the command RegAsm, you can create a batch file named RegAsm.bat and put it in each of the framework folders.  All copies of those batch files should run the same, explicit version of RegAsm.exe you want.

Unfortunately, you can’t simply rename the RegAsm.exe files without messing around with security settings.  But that’s an option too which I’m not getting into here.

See these images?

image

You’ll find an actual working versions of them at the top and bottom of this article. Please click the appropriate buttons in it to let your friends know about this article.

Check back later for updates too!

An error occurred while signing: SignTool.exe not found.

Ever get either of these two errors while trying to deploy a .Net app?  Specifically, while trying to publish a Click-Once deployed app (it can happen in other cases too)?

I’ve got a freshly built PC with Visual Studio 2013 installed and one of the first apps I created was a simple WinForms app with Click-Once deployment and I got this error.

After much frustrated Googling, I found no answers, so I figured this one out myself.

Here’s why this error happens

[GARD]

The signtool.exe file is actually missing.  But, that doesn’t mean you don’t have it on your PC.  There are multiple SDK versions likely installed on your machine, but the signtool.exe isn’t in all of them and where ever Visual Studio is looking for it is NOT one of the locations that has it.

Solution

I searched my hard drive for signtool.exe and found it here:

C:\Program Files (x86)\Microsoft SDKs\Windows\v7.1A\Bin

Then I noticed that in C:\Program Files (x86)\Microsoft SDKs\Windows

there were multiple version folders.  I placed a copy of signtool.exe in each one of them inside their bin folder.  If they didn’t have a bin folder, I created one.

Turns out, the v7.0A\bin folder is where it needed to be (at least, on MY machine).  After placing a copy of signtool.exe there, it solved the problem.

[GARD]

 

See these images?

image

You’ll find an actual working versions of them at the top and bottom of this article. Please click the appropriate buttons in it to let your friends know about this article.

Check back later for updates too!

CodeStock 2012: Adaptive and Responsive Web Design

imageBelow are my notes taken during CodeStock 2012’s “Adaptive and Responsive Web Design” hosted by:

Steve Bodnar ( @SteveBodnar )
www.geeksandgurus.com

 

 

Click here to follow me on Google+.

Follow me on Twitter @CSharpner.

In this session we learned how HTML5 lets us design our web apps to automatically adjust to all sorts of different sized browsers.  See links in notes below for sites with examples.  Resize your browser to large and small and watch the sites automatically adapt.

Raw notes, as I typed them in class below:


  • How to make your sites work on any HTML/Javascript device regardless of browser or device.
  • RELEVANT CONCEPTS
    • App vs. site
    • Graceful degradation
    • Progressive enhancement
    • Others…
  • GRACEFUL DEGRADATION
    • Downgrade gracefully when features aren’t available in browser. User should not notice.
    • Design on most advanced browser then adapt to lower browsers
  • PROGRESSIVE ENHANCEMENT
    • Focused on content, not browsers
    • Builds for least capable devices
  • STRAGEGIES
    • Start with lowest
    • Design for semantics and structure
    • Add features appropriate for baseline devices
    • Add features appropriate for accessibility
    • Add layout markup and style sheets for structural layout
    • Add baseline presentational style sheets using link
    • Add behavior (scripts, css hover, etc.)
    • Add workarounds/hacks for recalcitrant browsers
    • Define styles for modern graphical browsers.
  • ADAPTIVE WEB DESIGN
    • Not clear divide between this and response web design. Responsive is more specific
    • Creating interfaces that adapt
        • (use firefox user agent switcher to render as other devices)
        • Using progressive enhancement
        • Examples of adapting
          • If device can access location, enable location-based behavior
          • If device supports touch, enable touch-friendly ui
          • If device supports html5, use it
        • Mobile First
  • RESPONSIVE WEB DESIGN
    • Fluid Grids
    • Fluid Images and Media
    • Media Queries
    • (site takes shape of browser).
    • MediaQueries.es for examples of adaptive web design.
    • ASP.NET MVC 4 provides adaptive in default project.
    • @media keyword in CSS.
    • Resolution independence
  • MOBILE FIRST
    • Embrace constraints, don’t fight them.
    • Ony eyeball, one thumb
      • Forces you to focus
        • What content must be delivered?
        • Is chrome necessary?
        • Rich context aware applications.
  • ACCESSIBILITY
    • Design for all visitors
      • Screen readers
      • Color blindness
      • Hearing impaired
      • Navigation, images, forms
    • Not just about design for people with disabilities
  • BROWSER GRADES
    • C-Grade
      • Based level of support
      • Identified, incapable, antiquated and rare
    • A-Grade
      • Highest level of support
      • Identified, capable, modern and common
    • X-Grade
      • Unknown, fringe or rare browsers as well as browsers on which development has ceased.
  • TOOLS
    • CSS
      • Embrace CSS
        • CSS Zen Garden csszengarden.com
          • Links on right switch CSS to completely change the site with zero HTML change.
        • CSS1k.com
          • Examples of what 1KB of CSS can do to how a website looks.
  • HTML5
    • Clark Sell and Brandon Satrom
    • Microsofties
    • At CodeMash
    • HTML
    • CSS3
    • JavaScript
    • Semantic HTML
      • <div>
      • <div id = “header”>
      • <header>
      • <article>
      • <time>
    • Data attributes
      • Any attribute that starts with “data-” will be treated as a storage area for private data
      • <data-role>
      • <data-role = “header”>
      • <data-role=”footer”>
    • Viewport (browsers tell you what resolution they can handle – phones will lie)
      • Visual Viewport
        • The part of the page that’s currently on-screen
      • Layout Viewport
        • Doesn’t change in size or shape
        • Varies per browser
  • MEDIA QUERIES (w3 site has it all)
    • Width
    • Height
    • Device-width
    • Devbice-height
    • Orientation
    • Aspect-ratio
    • Device-aspect-ratio
    • Color
    • Color-index
    • Others.
  • FRAMEWORKS
    • MVC 4
    • Twitter bootstrap twitter.github.com/bootstrap
  • EVERYONE AGREES
  • Emulators
    • Shrinking your browser isn’t a perfect example.
    • Use real emulators if you have them.
    • Android-emulator.org
      • Has emulators for multiple mobiles including Ios
  • RESOURCES
    • Alistapart.com
    • Css3pie.com – to get IE 6-9 capable of rendering several of the most useful css3 decoration features.

CodeStock 2012: What’s New in .Net 4.5?

imageBelow are my raw notes during the “What’s New in .Net 4.5?” session at CodeStock 2012 on Friday, June 15th at 1:50 PM / 70 min.  This session was hosted by Layla Driscoll from Microsoft.  She’s on the Silverlight CLR team.

Click here to follow me on Google+.

Follow me on Twitter @CSharpner.

And below are my crude, raw notes taken during the session, only minimally cleaned up.


This sample will use:

  • Async & await
  • Asplnet web api
  • Entity framework

Creating a Metro app

  • Multiple tiles
  • Drill down into them for more stuff.
  • Using some sort of MVC type of development.  (Really?  For a DESKTOP app??!?!  I’d heard of this, but that’s the LAST way I want to develop a DESKTOP app… you get the worst of both worlds… no advantages of a native app and no advantages of web deployment and all of the frustrations of both.  Though, it’s cool you CAN do it… I’d just never do it that way.)
    • Looks like it’s a web app, but it’s a Metro app.
    • Controllers are deriving from ApiController
    • Type called Feature.
    • (Note, code snippet below is incomplete.  Remember, I typed this in “class” as fast as I could…)
    public class FeaturesController: ApiController
    {
        private Feature[] features - {{new Feature{Id=1,Name="LINQ"}, new Feature{Id-2,Name="OptionalParam"}};
        public Ienumerable<Feature> Get()
        {
            return features;
        }
    
        //Feature is her own plain old class with several fields in it.
        public Feaure Get(int id)
        {
            var feature = features.SingleOrDefault(f=>f.Id=id);
            If (feature == null)
            {
                var resp = new HttpResponseMessage(HttpStatusCode.NotFound);
                Response.Content = new StringContent("Feature not found");
                throw new HttpResponseException(resp);
            }
        return feature;
        }

Async keyword can be placed on methods like

 

private async void blah()
{
    using (var httpClient = new HttpClient()
    {
        httpClient.MaxResponseContentBufferSize = 1024*1024;
        var response = await httpClient.GetStringAsync("http://localhost blah blah");
        var list = JsonConvert.DeserializeObject<List<int>>(response);
        foreach(var I int in list)
        {
            response = await httpClient.GetStringAsync("http://localhost blah blah" + i);
            var f = JsonConvert.DeserializeObject<FeatureDataItem>
        }
  • Use nonvolatile for locking code. (or did she say non-locking?)
  • Win32 API (or whatever it’s called now) looks like managed code… no DLL import crap.
  • New large object heap. Anything 85,000 bytes.
  • Multi-core JIT
  • Prefetcher
  • ReadOnlyDictionary

Setting up SQLite on 64bit Windows 7

File:SQLite370.svgIf you try to install the 64 bit version of SQLite on your Windows 7 64 bit system, you’ll find that your code will break.  It simply won’t work.  You’ll also find that when you try to add a connection in “Server Explorer” inside of Visual Studio, that there’s no option for making a SQLite connection.

Click here to follow me on Google+.

Follow me on Twitter @CSharpner.

image

This is because, for some reason, none of the current versions have the install for a SQLite connector.  For that, you’ll have to install the really old version 1.0.66.0 executable first.  After that, you can install the latest 32bit version (the 64bit version doesn’t work).  Once 1.0.66.0 is installed, you’ll have SQLite available in Visual Studio:

image

You can get v 1.0.66.0 from here:

You can download the 32bit DLLs here sqlite-netFx35-setup-bundle-x64-2008-1.0.77.0.zip:.  It’s under the section titled “Precompiled binaries for Windows”.

See these images?

imageimage

You’ll find an actual working versions of them at the top and bottom of this article. Please click the appropriate buttons in it to let your friends know about this article.

Check back later for updates too!

Click here to follow me on Google+.

Follow me on Twitter @CSharpner.

[poll id=”3″]

That’s it.  Good luck!

Web.config and App.config variables can be fully type safe

I’m going to introduce a MUCH better way of accessing your variables that you have created in your web.config or app.config file.  Most likely, you’re probably storing your variables in your .config file like this:

Click here to follow me on Google+.

 <appSettings> <add key="EMailAddress" value="me@something.com"/> <add key="IsProduction" value="true"/> <add key="MaxWait" value="15"/> appSettings>

And your code to access them probably looks like this:

this.EMailAddressLabel.Text = ConfigurationManager.AppSettings["EMailAddress"];if (ConfigurationManager.AppSettings["IsProduction"] == "true") this.NotProdNotifyLabel.visible = false;else this.NotProdNotifyLabel.visible = true;this.MaxWaitLabel.Text = "Maximum wait time is " + ConfigurationManager.AppSettings["MaxWait"] + " seconds";

That’s an awful lot of typing, has the potential for typos that aren’t caught by the editor or the compiler, doesn’t have intellisense, and is not type safe.

Solution!

Visual Studio has a very easy to use and very powerful feature for creating and accessing config variables.

Now, depending on what version of Visual Studio you’re using, things could be different.  As of this writing, the latest version is Visual Studio 2010.  But, this feature is available going all the way back to the original version of Visual Studio .Net back in February 2003.  So, it’s supported in all versions, but there are differences, not in how you do it, but in what choices you’ve already made for existing apps.  First, I’ll discuss doing this the right way when creating a new app:

BTW, this works for ALL .Net apps, not just ASP.Net web apps.  It works for WinForms, ASP.Net MVC, ASP.Net, Console apps, Windows Service Apps…

I’ll be using an ASP.Net WebForms app as an example, because that’s where the differences lie between the different version of Visual Studio.Net.

First, we’ll work with making a New app.

    1. Create a web app app.  Depending on your version of Visual Studio, you might have an option for “Web Site”.  DON’T CHOOSE THAT!  This whole solution is unavailable for web sites.  It MUST be a web app!

 

  • Right-click your project and choose “Properties:

 

  1. image

 

  • Click on the “Settings” tab.

 

  1. image

 

  • Click on the link, “This project does not contain a default settings file.  Click here to create one.”

 

  1. image

Now, your app contains what’s needed to support your simple typed config variables.  Notice the new stuff added to your project?

image

Now, never look at it again.  I just wanted you to see that it’s there.  This adds no complication to your life because you’ll never use it directly.  It’s just generated code.  Put it out of your mind and never think of it again.

Notice the grid that showed up on the screen?

image

This is where you’ll create your config variables… not in the config file itself.  This simple tool will automatically add them to the config file for you.  You don’t have to putz around with the messy config file anymore for your config variables.  Now, let’s add the 3 config variables we used the old way from the top of this article:

For each variable we create, we can choose the type of that variable!  Check out what happens when I create the IsProduction variable:

image

I picked “bool” from the “Type” drop down list.  Look at the “Value” column.  Notice that it knows what is valid for a boolean?  I’ll pick “True”.

Now, when I add MaxWait, I’ve got some options.  I can make it an int to represent number of seconds, but in reality, it’s representing a time span.  Guess what?  .Net has a TimeSpan type!

image

image

It inserts a value for me, showing me the format I need.  That’s HH:MM:SS, BTW.  I can add a decimal after the seconds to get milliseconds, if I want.  There’s a way to do days too, but I’ll let you figure that out.

I’m going to enter 15 seconds:

image

Now, this is important (but simple).  I need to hit “save”.

image

Now, look at what it did to the config file:

image

image

Now, there’s one more thing I want to draw your attention to… and it’s not important that you know this because you’ll never need to mess with this, but I want to give you a glimpse under the hood of what else has happened:

image

Pop open the Settings.settings branch and open the Settings.Designer.cs file:

 

 1: //------------------------------------------------------------------------------
 2: // 
 3: // This code was generated by a tool.
 4: // Runtime Version:4.0.30319.237
 5: //
 6: // Changes to this file may cause incorrect behavior and will be lost if
 7: // the code is regenerated.
 8: // 
 9: //------------------------------------------------------------------------------
 10: 
 11: namespace ConfigVarsSample.Properties {
 12: 
 13: 
 14:     [global::System.Runtime.CompilerServices.CompilerGeneratedAttribute()]
 15:     [global::System.CodeDom.Compiler.GeneratedCodeAttribute("Microsoft.VisualStudio.Editors.SettingsDesigner.SettingsSingleFileGenerator", "10.0.0.0")]
 16:  internal sealed partial class Settings : global::System.Configuration.ApplicationSettingsBase {
 17: 
 18:  private static Settings defaultInstance = ((Settings)(global::System.Configuration.ApplicationSettingsBase.Synchronized(new Settings())));
 19: 
 20:  public static Settings Default {
 21:             get {
 22:  return defaultInstance;
 23:             }
 24:         }
 25: 
 26:         [global::System.Configuration.ApplicationScopedSettingAttribute()]
 27:         [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
 28:         [global::System.Configuration.DefaultSettingValueAttribute("me@something.com")]
 29:  public string EMailAddress {
 30:             get {
 31:  return ((string)(this["EMailAddress"]));
 32:             }
 33:         }
 34: 
 35:         [global::System.Configuration.ApplicationScopedSettingAttribute()]
 36:         [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
 37:         [global::System.Configuration.DefaultSettingValueAttribute("True")]
 38:  public bool IsProduction {
 39:             get {
 40:  return ((bool)(this["IsProduction"]));
 41:             }
 42:         }
 43: 
 44:         [global::System.Configuration.ApplicationScopedSettingAttribute()]
 45:         [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
 46:         [global::System.Configuration.DefaultSettingValueAttribute("00:00:15")]
 47:  public global::System.TimeSpan MaxWait {
 48:             get {
 49:  return ((global::System.TimeSpan)(this["MaxWait"]));
 50:             }
 51:         }
 52:     }
 53: }

This is the code that got generated for you.  Now, put it out of your mind (again), because you’ll never actually edit this file.

Now, remember the old way of accessing the config variables?

this.EMailAddressLabel.Text = ConfigurationManager.AppSettings["EMailAddress"];if (ConfigurationManager.AppSettings["IsProduction"] == "true") this.NotProdNotifyLabel.visible = false;else this.NotProdNotifyLabel.visible = true;this.MaxWaitLabel.Text = "Maximum wait time is " + ConfigurationManager.AppSettings["MaxWait"] + " seconds";

Here’s the new way:

Add this to your using section:

using

 

ConfigVarsSample.Properties;

And in your code, you do this:

image

Notice how you get intellisense?!?!?  Let’s continue:

var settings = new Settings();this.EMailAddressLabel.Text = settings.EMailAddress;this.NotProdNotifyLabel.visible = !settings.IsProduction;this.MaxWaitLabel.Text = "Maximum wait time is " + settings.MaxWait.ToString();

See how much cleaner this code is?  And, since it’s typed, you can use the IsProduction variable as a boolean rather than examining the text value of it.  The MaxWait variable can actually be used in time and date functions and directly in calculations!

What have you gained?

    1. Intellisense.

 

  • Edit time typo checking.

 

 

  • Compile time error checking.

 

 

  • Strongly typed variables.

 

 

  • Code that won’t fail at run time.

 

 

  • .config files that you don’t have to edit directly as often.

 

 

  • Increased productivity.

 

See this image?

image_thumb26

You’ll find an actual working version of it at the bottom of this article. Please click the appropriate buttons in it to let your friends know about this article.

Check back later for updates too!

Click here to follow me on Google+.

Assembly Language

Code

In computer programming, there are hundreds of languages to choose from to write software, but when it comes down to it, they all get converted down to machine code eventually, even high level scripting languages like JavaScript.

Assembly language is the human readable form of machine code.  People write Assembly Language programs using a source code editor (a glorified text editor, optimized for writing computer code).  They then feed their source code into a program called an assembler to convert it into true machine code so that the CPU can execute it.

Assembly Language should not be confused with Assembler Language, though even some of the most experienced developers make the mistake.

For more information about Assembly Language, please read this article about Machine Code.

Machine Language or Machine Code

 

Thank you for sharing this article.  See this image?

image

You’ll find actual working versions of them at the top and bottom of this article. Please click the appropriate buttons in it to let your friends know about this article.

Find a GUID (or anything) in ANY table in your database!

[Updated 2015-03-12]

Do you ever run across an ID, such as a GUID or an integer (or anything else, for that matter) while stepping through code and aren’t quite sure which table it’s supposed to be for or need to know where it’s referenced anywhere in your database? I use GUIDs like they’re going out of style for a plethora of reasons, especially in my tables. EVERYTHING in my tables has a GUID to identify it. I use them as primary keys and as foreign keys. They’re computer generated, can be generated either on the db server, the web server, or even on the client and can be guaranteed to be unique in that table, or in an entire database, or in the entire universe, for that matter (try doing that with auto-incrementing integers!!!).

[GARD]

Anyway… I found myself needing to find an arbitrary GUID anywhere in the database and was doing a lot of manual querying. Finally, I decided that this could be EASILY automated by querying the database meta data for tables with GUID columns, then automatically querying those tables and columns for my GUID and output the results of each table and column the GUID was found in.

Below, is my solution: A stored procedure called FindGuidInAnyTable that takes a single parameter (a GUID) and searches the entire database for all occurrences of it. You could adapt this for any column type… not just GUIDs, BTW.

[Update 2015-03-13: Using the newer, friendlier system table names, and including the schema name as well]

ALTER procedure [dbo].[FindGuidInAnyTable]
(
 @FindThisGUID uniqueidentifier
) as

begin

 declare @TableSchema varchar(100)
 declare @TableName varchar(100)
 declare @NextColumn varchar(200)
 declare @Select varchar(1000)

 create table #ResultsTable
 (
 SchemaName varchar(100),
 TableName varchar(100),
 ColumnName varchar(100)
 )

 declare TableNameCursor cursor for
 select
 table_schema,
 table_name
 from
 INFORMATION_SCHEMA.tables
 where
 table_type = 'BASE TABLE'

 OPEN TableNameCursor

 FETCH NEXT FROM TableNameCursor INTO @TableSchema, @TableName

 WHILE (@@FETCH_STATUS <> -1) BEGIN

 IF (@@FETCH_STATUS <> -2) BEGIN

 declare ColumnNameCursor cursor for
 select distinct
 COLUMN_NAME
 from
 INFORMATION_SCHEMA.COLUMNS
 where
 TABLE_SCHEMA = @TableSchema and
 TABLE_NAME = @TableName and
 DATA_TYPE = 'uniqueidentifier'

 OPEN ColumnNameCursor

 FETCH NEXT FROM ColumnNameCursor INTO @NextColumn

 WHILE (@@FETCH_STATUS <> -1) BEGIN
 
 IF (@@FETCH_STATUS <> -2) BEGIN

 set @select = 'insert into #ResultsTable select ''' + @TableSchema + ''' as TableSchema, ''' + @TableName + ''' as TableName, ''' + @NextColumn + ''' as ColumnName from [' + @TableSchema + '].[' + @TableName + '] where [' + @NextColumn + '] = ''' + cast(@FindThisGUID as varchar(50))+ ''''
 print @select
 exec(@select)

 end
 
 FETCH NEXT FROM ColumnNameCursor INTO @NextColumn
 
 END

 CLOSE ColumnNameCursor
 DEALLOCATE ColumnNameCursor

 end

 FETCH NEXT FROM TableNameCursor INTO @TableSchema, @TableName

 END

 CLOSE TableNameCursor
 DEALLOCATE TableNameCursor

 select
 count(*) as Instances,
 SchemaName,
 TableName,
 ColumnName,
 @FindThisGUID as GuidFound
 from
 #ResultsTable
 group by
 SchemaName,
 TableName,
 ColumnName
 order by
 SchemaName,
 TableName,
 ColumnName

end

For MS SQL Server versions prior to SQL Server 2005, do this:

[GARD]

CREATE procedure [dbo].[FindGuidInAnyTable]
(
   @FindThisGUID uniqueidentifier
) as

begin

declare @TableName varchar(100)
declare @NextColumn varchar(200)
declare @Select     varchar(1000)

create table #ResultsTable
(
 TableName varchar(100),
 ColumnName varchar(100)
)

declare TableNameCursor cursor for
  select distinct
     o.name
  from
 syscolumns c,
 sysobjects o
  where
     c.id    = o.id and
     c.xtype = 36   and
     o.xtype = 'U'

OPEN TableNameCursor

FETCH NEXT FROM TableNameCursor INTO @TableName

WHILE (@@FETCH_STATUS &lt;> -1) BEGIN

  IF (@@FETCH_STATUS &lt;> -2) BEGIN

     declare ColumnNameCursor cursor for
        select distinct
           c.name
        from
 syscolumns c,
 sysobjects o
        where
           c.id    = o.id       and
           c.xtype = 36         and
           o.name  = @TableName and
           o.xtype = 'U'

     OPEN ColumnNameCursor

     FETCH NEXT FROM ColumnNameCursor INTO @NextColumn

     WHILE (@@FETCH_STATUS &lt;> -1) BEGIN
 
        IF (@@FETCH_STATUS &lt;> -2) BEGIN

           set @select = 'insert into #ResultsTable select ''' + @TableName + ''' as TableName, ''' + @NextColumn + ''' as ColumnName from [' + @TableName + '] where [' + @NextColumn + '] = ''' + cast(@FindThisGUID as varchar(50))+ ''''
           print @select
           exec(@select)

        end
 
        FETCH NEXT FROM ColumnNameCursor INTO @NextColumn
 
     END

     CLOSE      ColumnNameCursor
 DEALLOCATE ColumnNameCursor

  end

  FETCH NEXT FROM TableNameCursor INTO @TableName

END

CLOSE      TableNameCursor
DEALLOCATE TableNameCursor

select
  count(*) as Instances,
 TableName,
 ColumnName,
  @FindThisGUID as GuidFound
from
  #ResultsTable
group by
 TableName,
 ColumnName
order by
 TableName,
 ColumnName

end

Here’s how you use it:


exec FindGuidInAnyTable '34fdbfa2-cdbd-4f34-bd2e-1423063fb707'

Here are what the results look like in my database:

I put this stored procedure in all of my databases. It’s a real time saver!