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!

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!