Accessing Database Resources (Standard 2009)

  • Article

4/24/2012

Microsoft Corporation

January 2009

Summary

This article contains detailed information about the component database and about how to use SQL commands to view and manipulate the contents of the database tables and resources.

Introduction

Visibility Settings in the Database

Finding Owning Components for Resources

Using osql and Scripts with Your Database

Conclusion

Introduction

This article contains some insight into the contents of the database tables and resources and addresses the following issues:

  • Have you ever wanted to delete a component from the database by using Component Database Manager (DBMGR) but its visibility was too low?
  • Have to know how to query for the component owner of a registry key?
  • Can you query the SQL Server database for the owning component of a file resource instead of by using Target Designer’s filter manager?

Visibility Settings in the Database

In Target Designer, the lowest visibility level is 100. You can set this on the Tools menu, by choosing Options, and then choosing Advanced. This means that any component that has a visibility lower than this is not viewable from Target Designer. A user can view the SQL table for the dependency database by using SQL Server Management Studio and change the visibility setting for all the components to 0 (or at least 100), so they can see every component in the database by using Target Designer.

Note

By default, some of low-level, base components are hidden, for example, the BASE COMPONENT. This is to prevent developers from accidentally deleting components that could corrupt the database and require a full reinstallation of the product.

There is a switch to view the hidden components. Although not supported, this method is a more effective way than manipulating the contents of the database directly.

To change the visibility setting of the component database

  1. Start the Component Database Manager

  2. Choose the Components tab.

  3. Note the number of visible components.

  4. Close the Component Database Manager.

  5. Open a Command Prompt window and browse to .\Program Files\Windows Embedded\Bin and type the following:

    DBMGR.exe /v

    On the Components tab, you can see 300 to 350 additional components. Your visibility is now 0. In this state, you can view or delete any component in the database.

    Note

    If you accidentally delete one of the low-level components, you can possibly corrupt your component database and have to reinstall it.

Finding Owning Components for Resources

Although Target Designer and Component Database Manager have a filter function that lets users search for the owning component for any file resource, many developers prefer to use programmatic methods to find this information. In this case, you can query the embedded database directly by using a SQL query.

The query shown here will return all components in the database that own a file.

In SQL Server Management Tools, connect to the server that hosts the embedded database. Select the MantisSQLDB in the database, choose New Query from the toolbar and then copy and paste this query directly into Query Analyzer, and then change it as required.

DECLARE @Filename varchar(255)
SET @Filename = 'iexplore.exe' 
select DISTINCT A.DisplayName
FROM ComponentObjects A, ExtendedProperties B
WHERE A.ComponentID = B.OwnerID AND B.ResourceTypeID = 1
AND (B.Name = 'SrcName' OR B.Name = 'DstName')
AND B.StringValue = @Filename

This example query, run on an XP Embedded Update Rollup 1 database, with some security updates installed, returns a list of components in the database that have Iexpore.exe as one of their file resources. As you can see, it finds all versions of the component in this reference database, the version from XP Embedded RTM, SP1, SP2, several security updates.

The results returned will be unique to the database version that you have installed. For example,

Internet Explorer

Internet Explorer - Hotfix Q319182

Primitive: iexplore.exe

Internet Explorer

Internet Explorer - Hotfix Q321232

Internet Explorer - Hotfix Q313675

Internet Explorer - Hotfix Q316059

Internet Explorer

Primitive: iexplore.exe

The query can also return the revision number of the component, so you know the latest version of the component to look for in Target Designer. The highest revision number is the most recent version of the component.

DECLARE @Filename varchar(255)
SET @Filename = 'iexplore.exe'
select DISTINCT A.DisplayName, A.Revision
FROM ComponentObjects A, ExtendedProperties B
WHERE A.ComponentID = B.OwnerID AND B.ResourceTypeID = 1
AND (B.Name = 'SrcName' OR B.Name = 'DstName')
AND B.StringValue = @Filename
ORDER BY DisplayName, Revision DESC

To find all file resources with the string 'wmi', change the exact value of 'iexplore.exe' to '%wmi%' in the query.

select DISTINCT A.DisplayName, A.Version, A.Revision, A.ComponentVIGUID, A.ComponentVSGUID
FROM ComponentObjects A, ExtendedProperties B
WHERE A.ComponentID = B.OwnerID AND B.ResourceTypeID = 1
AND (B.Name = 'SrcName' OR B.Name = 'DstName')
AND B.StringValue LIKE '%wmi%'
ORDER BY DisplayName, Revision DESC

You can provide a list of files or use the query in an ASP page or VBScript.

If you wanted to query for the component owner of a registry key, change the query as follows:

select DISTINCT A.DisplayName, A.Version, A.Revision, A.ComponentVIGUID, A.ComponentVSGUID
FROM ComponentObjects A, ExtendedProperties B
WHERE A.ComponentID = B.OwnerID AND B.ResourceTypeID = 2
And B.Name ='KeyPath'
AND B.StringValue LIKE '%SOFTWARE\Microsoft\Windows NT\CurrentVersion\GRE_Initialize%'
ORDER BY DisplayName, Revision DESC

The difference between this and the query for file resources, is in the last line for the 'WHERE' clause. The Name = KeyPath guarantees that you are looking at the correct part of data within the key. This ExtendedProperties.ResourceTypeID=2 is also different. The Resource ID for a file type of resource is 1 the Resource ID for a registry related resource is 2.

The query is only a key path. If you are looking for registry data, change KeyPath to RegValue in the WHERE clause. Here is an example of a query that returns most of the audio driver components:

select DISTINCT A.DisplayName, A.Version, A.Revision, A.ComponentVIGUID, A.ComponentVSGUID
FROM ComponentObjects A, ExtendedProperties B
WHERE A.ComponentID = B.OwnerID AND B.ResourceTypeID = 2
And B.Name ='RegValue'
AND B.StringValue LIKE '%Aux Mute%'
ORDER BY DisplayName, Revision DESC

If you know the exact string to query for, you can drop the '%' character. This can reduce the time for SQL to return the results.

Using osql and Scripts with Your Database

The osql utility is a command prompt utility that you can use to run Transact-SQL statements and script files to interact with your database or databases. It is applicable if you have Microsoft SQL Desktop Engine (MSDE) or SQL Express installed, but not the full SQL Server, and you have not installed the Management Tools.

It can also be used to interact with a SQL Express database on your run-time image, if you included the Microsoft SQL Express 2005 Macro or Microsoft SQL Server 2000 Desktop Edition components in your configuration, and then install MSDE or SQL Server Express 2005 on the run-time image after deployment.

The osql utility uses the ODBC database API to communicate with the server. It uses built-in SQL stored procedures. Most common actions can be performed by using osql commands.

To connect to the local server

  • Connect to a local server by typing the following at a command prompt

    osql -E

    This connects you to the local, default instance of SQL Server 2000 Desktop Engine (also known as MSDE 2000) or SQL Server Express 2005 by using Windows authentication.

To connect to a named instance of a server

  • Connect to a named instance of a server by typing the following at a command prompt

    osql -E -S servername\instancename

To change the system administrator password

  • If the system administrator password is NULL., change the password by typing the following at a command prompt

    osql.exe -U sa -P "" -Q "sp_password NULL, '<new_password>', 'sa'"

  • If the system administrator password is not NULL., change the password by typing the following at a command prompt

    osql.exe -U sa -P "<old_password>" -Q "sp_password ‘<old_password>’, '<new_password>', 'sa'"

To add new SQL Server accounts

  1. Add a new SQL Server account by executing the following stored procedure at the osql prompt:

    EXEC sp_addlogin 'login', 'password', ['default database']

  2. Add a server role to the new account by executing the following stored procedure at the osql prompt:

    EXEC sp_addsrvrolemember ‘login’, ‘sysadmin’

To add a trusted Windows NT user

  • Add a trusted Windows NT user by executing the following stored procedure at the osql prompt:

    EXEC sp_addlogin 'login'

    Note

    Only members of the sysadmin and the securityadmin fixed server roles can run this stored procedure.

To grant a user account access to a database

  • Grant a user account access to a database by executing the following stored procedure at the osql prompt:

    EXEC sp_grantdbaccess 'login', 'user account name'

    Note

    Only members of the sysadmin fixed server role, the db_accessadmin and the db_owner fixed database roles can run the sp_grantdbaccess stored procedure.

To back up and restore databases

  • Back up a database by executing the following command at the osql prompt.

    BACKUP DATABASE <database name> TO DISK = '<backup path>\<backup file>'

  • Restore a database by executing the following command at the osql prompt:

    RESTORE DATABASE <database name> FROM DISK = '<backup path>\<backup file>'

You can also use osql in a batch file. Here is an example of detaching and attaching a database, where the drive is the location that your Windows Embedded Data folder was created.

To detach or attach a database

  • To detach a database by using osql in a batch file:

    echo EXEC sp_detach_db 'MantisSQLDB', 'true' >temp01.txt
echo go >>temp01.txt
echo quit >>temp01.txt

@Rem Detach the current DB
osql -H local -E -i temp01.txt -e

  • To attach a database by using osql in a batch file:

    echo EXEC sp_attach_db @dbname = N'MantisSQLDB',
@filename1 = N'<Drive>:\Windows Embedded Data\MantisSQLDB_Data.mdf',
@filename2 = N'<Drive>:\Windows Embedded Data\MantisSQLDB_log.ldf' >temp02.txt
echo go >>temp02.txt
echo quit >>temp02.txt

@Rem Attach the DB
osql -H local -E -i temp02.txt -e
osql del /F temp01.txt

You can maintain different versions of the database for different product versions in different folders and then attach to the appropriate database as needed. Be aware that the name of the .ldf file may vary, depending on the database version that you are using (for example, Mantissqldb_log.ldf, Mantissqldbeval_log.ldf).

You do not have to save copies of the log file, depending on whether you want to continue logging to the same file, or if you want to start a new log when a database is attached.

The full osql syntax can be found at this Microsoft Web site.

For more information about osql, see this Microsoft Web site.

Conclusion

What you have learned

This technical article has provided you with information on advanced component database procedures by using DBMGR.exe and the osql utility.