Home > Articles

Getting to Know the SQL Server 2005 Compact Edition Tools

  • Aug 24, 2007
Get to know SQL Server 2005 CE's tools by managing and manipulating the SQL Server database with the SQL Server Management Studio and Query Analyzer, including SQL Server CE into your Visual Studio project, connecting with the SQL Server CE database, and finally deploying the application.
This chapter is from the book

This chapter is from the book

Microsoft SQL Server 2005 Compact EditionMicrosoft SQL Server 2005 Compact Edition

Learn More Buy

This chapter is from the book

This chapter is from the book

Microsoft SQL Server 2005 Compact EditionMicrosoft SQL Server 2005 Compact Edition

Learn More Buy

In this Chapter

  • Using SQL Server Management Studio
  • Using the Query Analyzer
  • Using Visual Studio
  • Summary

One main limitation of the SQL CE 2.0 database released in 2002 was the need to manage the database either using a query analyzer tool on mobile devices or by running a program to manipulate data on a device. To allow rapid application development, Microsoft has added tight integration between SQL Server Compact Edition 3.x, SQL Server 2005, and Visual Studio 2005.

Using SQL Server Management Studio, developers and administrators can manage the SQL Server Compact Edition database located on a device or on a desktop. You can design tables, columns, constraints, and indexes using SQL Server Management Studio.

Similarly you can use Visual Studio features to create, design, and manipulate the SQL Server Compact Edition database. Visual Studio also helps you to deploy a database on a device.

In this chapter you will be familiarized with the available tools. You will also learn how to use SQL Server Management Studio and Query Analyzer to manage and manipulate the SQL Server database. You will become familiar with the Visual Studio interface and will learn how to include SQL Server Compact Edition in your project. You will also learn how to set up a connection with the SQL Server Compact Edition database and finally how to deploy the application on platforms.

Using SQL Server Management Studio

SQL Server Management Studio provides an integrated environment to manage the SQL Server family of databases. Using SQL Server Management Studio, you can manage SQL Server 2005 database, SQL Server Compact Edition 3.x database, Integration Services, Analysis Services, and Reporting Services.

SQL Server Management Studio is installed on your computer along with the SQL Server 2005 installation. By default the SqlWb executable is installed at C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE.

NOTE

SQL Server Management Studio is also known as Management Studio, SSMS, or Work Bench. Many developers also use the term Work Bench for SQL Server Management Studio.

SQL Server Management Studio has replaced Enterprise Manager and Query Analyzer. Using SQL Server Management Studio, you can also manage SQL Server 7 and the SQL Server 2000 database.

The biggest advantage of SQL Server Management Studio is that you do not need to learn a new interface for managing the SQL Server Compact Edition database. If you are already using SSMS for managing SQL Server, you can use the same interface for managing the SQL Server Compact Edition 3.0 database.

Starting SQL Server Management Studio

You can start the SQL Server Management Studio by clicking on Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio. Using SSMS you can create a project and a solution. The project contains a connection to a database and the corresponding SQL scripts. A container solution contains related projects. A project provides a mechanism to organize files, not the database objects. To create a new project and solution use the File | New menu option and specify the project and the solution name.

The SSMS user interface provides two types of windows—component windows and document windows. Component windows display windows listed under the View options such as Object Explorer, Registered Server, Solution Explorer, and so on. Document windows are used for queries, scripts, and files. The SQL Server Management Studio windows are shown in Figure 3.1. You can use various options to customize SSMS windows by using the Tools | option.

Figure 3.1

Figure 3.1 SQL Server Management Studio Windows

This section will describe these windows. The Bookmark feature is a new feature that you can use to create a shortcut to a line in a project file. To create a bookmark, choose Toggle Bookmark from the Edit menu.

Getting Connected to SQL Server Compact Edition Database

When you start SQL Server Management Studio, it starts with the Connection dialog box. In this dialog box you will specify that you wish to connect to SQL Server Compact Edition database. You need to specify the path of the SQL Server Compact Edition database file and initiate the connection. To connect to the SQL Server Compact Edition database follow the steps given below:

  1. Start the SQL Server Management Studio. The Connect to Server dialog box will appear as shown in Figure 3.2.
    Figure 3.2

    Figure 3.2 Connect to SQL Server CE

  2. Select SQL Server Compact Edition Server Type.
  3. Specify the name of the SQL Server Compact Edition database file in order to open an existing database. You can choose Browse for More options to browse the database file.
  4. Click on the Connect button to connect to the database.

This example demonstrates a simple scenario—opening an SQL Server Compact Edition database. Using the dialog box shown in Figure 3.2, you can open a password-protected encrypted database and create a new SQL Server Compact Edition database.

Using Object Explorer

Object Explorer is an SQL Server Management Studio component. Using this component you can connect to SQL Server, Integration Services, Analysis Services, Reporting Services, and an SQL Server Compact Edition database.

Object Explorer is displayed as part of the SQL Server Management Studio user interface. If Object Explorer is not visible, click on the View | Object Explorer menu option.

As shown in Figure 3.3, the Object Explorer window displays a tree structure view for all the objects. For each server type, Object Explorer displays a different set of nodes. For an SQL Server Compact Edition database, it displays Tables, Views, Programmability, and Replication nodes.

Figure 3.3

Figure 3.3 Object Explorer

Figure 3.1 shows a connection to both SQL Server database and the SQL Server Compact Edition database. All available connections are shown in a tree view. You can expand the tree nodes to see underlying objects. Object Explorer can display up to 65,536 objects.

You can right click on any node revealing a list of operations that can be performed on the object. For SQL Server Objects, the Filter option is available to allow filtering of objects. For SQL Server Compact Edition, the Filter option is not available.

Disconnecting from the SQL Server Compact Edition Database

To disconnect from the SQL Server Compact Edition database, simply select the database. Right click and then click on the Disconnect option.

After disconnecting from the SQL Server Compact Edition database, Object Explorer is refreshed. You can use the Tools | Options menu option to customize a window layout of SSMS as shown in Figure 3.4.

Figure 3.4

Figure 3.4 Object Explorer

Connecting to SQL Server Compact Edition Database from Object Explorer

To reconnect to the SQL Server Compact Edition database, click on the Connect button in Object Explorer. You will be shown options to connect to SQL Server Database Engine, Analysis Services, Integration Services, Reporting Services, and the SQL Server Compact Edition database. Select the SQL Server Compact Edition database option.

You will be shown the Connect to Server dialog box. Specify the path of the SQL Server Compact Edition database as you did earlier, thereby connecting to the SQL Server Compact Edition database.

Registering a Database Server

Using the Register Server option you can store the connection information. While connecting to an SQL Server Compact Edition database that is already registered, you do not need to resupply all the connection parameters. The Register Server Option is useful for connecting to databases that you connect to frequently. Use the Register Server Option to store connection information for SQL Server Compact Edition, SQL Server, Analysis Services, Integration Services, and Reporting Services.

At the time of registration, you store the connection information in SQL Server Management Studio. To store this information, use the Register a Database Server option.

  1. To register a server, select the SQL Server Compact Edition database in SQL Server Management Studio Object Explorer.
  2. Right click on database and click the Register option as shown in Figure 3.5.
    Figure 3.5

    Figure 3.5 Register a Database Server

  3. In the Register Server dialog box, specify where you want to place the database in the Server Group box. By default the SQL Server Compact Edition database is selected in Figure 3.6.
    Figure 3.6

    Figure 3.6 Specify a Server Name

  4. To create a new group, click on the New Group button.
  5. In the New Server Group dialog box, enter a Group name and Group description as shown in Figure 3.7.
    Figure 3.7

    Figure 3.7 Specify a New Server Group

  6. Click Save.
  7. The new Group name is entered under SQL Server Compact Edition database.
  8. Click Save.

Connecting to a Registered Database

You can view all registered servers and connect to one of the registered databases. You can access all the Registered Servers by clicking on the View | Registered Servers option.

Clicking on Registered Server will start the Registered Server Explorer tab. Register Server Explorer has five tabs in the top menu. The five tabs correspond to five types of database server that you can connect using SQL Server Management Studio. Earlier in this chapter you learned that when using SQL Server Management Studio you can manage the SQL Server 2005 database engine and Integration Services. As shown in Figure 3.8, select the tab for SQL Server Compact Edition to access the Example Registered Server Group that you created earlier.

Figure 3.8

Figure 3.8 List of Registered Servers

Select your Database Group and Database and click to connect.

Using Tables Node

You will be using the Tables node to define the tables and columns structures. You can right click on the Tables node and use options to create new tables. You can expand the Tables node by right clicking on any existing table and view/modify the properties of the table.

You can also expand the table to see the columns. You can see the column properties by right clicking on all columns.

Using Views Node

SQL Server Compact Edition provides INFORMATION_SCHEMA views to help you obtain metadata information about the SQL Server Compact Edition database. The INFORMATION_SCHEMA view gets included in each SQL Server Compact Edition database. You can click on views folders to see INFORMATION_SCHEMA views available. When you click on Views, your result will look similar to one shown in Figure 3.9.

Figure 3.9

Figure 3.9 Information Schema Views

Tables

The Tables view contains one row for each table that is accessible to the current user.

Columns

The Columns view contains one row for each column that is accessible to the current user.

Indexes

The Indexes view contains one row for each index that is accessible to the current user.

Key_Column_Usage

The Key_Column_Usage view contains one row for each column that is defined as key in the current database.

Table_Constraint

The Table_Constraints view contains one row for each table constraint in a database.

Referential_Constraints

The Referential_Contraints view contains one row for each foreign key constraint in a database.

Provider_Types

The Provider_Types view contains the data type supported in SQL Server Compact Edition.

In the upcoming chapters you will learn how to utilize these views for problem solving.

Using the Programmability Node

The programmability node is more useful in SQL Server 2005. In SQL Server 2005 this node contains subnodes for Stored Procedures, User Defined Data Types, etc. In the SQL Server Compact Edition database, Programmability nodes display the data type available in SQL Server CE.

Using the Replication Node

Merge Replication is a mechanism to synchronize data between SQL Server and SQL Server Compact Edition. In Merge Replication, SQL Server acts as Publisher of data and SQL Server Compact Edition as Subscriber of data. In the Object Explorer of the SQL Server Compact Edition database you have a Replication node. The Replication node will have a Subscriptions subnode. This node will show the subscriptions available for SQL Server Compact Edition database. If you have set up the subscription, this node will display the subscription information as shown in Figure 3.10. You can check out the subscription properties by right clicking on the subscription.

Figure 3.10

Figure 3.10 Replication Node

If the SQL Server Compact Edition database has not been configured to act as Subscriber, the Subscription node under Replication node will be empty.

You will learn more about Merge Replication and Subscription in Chapter 12.

Using the Query Editor

SQL Server Management Studio has a query editor. To write queries for SQL Server CE, click on either the New Query button or the File | New Query menu option. Clicking on New Query will start the new query editor or ask you to specify a connection. Query Editor can be connected or disconnected to an SQL Server Compact Edition database. Query Editor color highlights query syntax and provides tracking indicators.

You can also open existing queries using the File | Open | File... option.

From Object Explorer you can select the SQL Server Compact Edition database, right click, and select New Query to open a query editor.

The Query Editor has an upper pane in which you can write queries. The lower pane displays the results of query execution. The results displayed are read-only. In the results pane there are two tabs: Results and Messages. The Result tab displays the data after successful execution. The Message tab displays an error message if the query has errors or it displays a message indicating the number of rows impacted as a result of a query. You can click on the Message tab to see the message. The track changed indicator in Query Editor shows which lines of an SQL script are changed.

NOTE

SQL Server Compact Edition supports a subset of Transact-SQL commands. SQL Server 2005 supports complete Transact-SQL commands. Chapter 5, Defining the Database Structure, and Chapter 6, Manipulating the Database, provide the syntax and examples of Transact-SQL that are supported in SQL Server Compact Edition.

The results are displayed in the Results section as shown in Figure 3.11.

Figure 3.11

Figure 3.11 Query Editor

NOTE

SQL Server Management Studio provides a query builder feature to design queries. For an SQL Server database, use the Design Query in the Editor menu option. You will not see this menu option when you write queries for an SQL Server Compact Edition database. The Query Builder feature is not available for an SQL Server Compact Edition database.

Using a Graphical Execution Plan

When you write an enterprise application, it is very likely that you will want to isolate and tune performance bottlenecks. SQL Server Management Studio fulfills this key objective and helps you to understand where the most time is used in query execution. SQL Server Management Studio provides a solution to seeing the execution plan for queries. The query execution plan gives you insight and enables you to tune queries.

Using the Estimated Execution Plan

The Estimated Execution Plan option parses the query and then generates the execution plan. In this option, SQL Server Compact Edition does not execute the query but generates the execution plan. You can see the graphical representation of the execution plan with Query Display Estimated Execution plan.

Using the Actual Execution Plan

The Actual Execution plan generates the query and then shows the plan used by SQL Server Compact Edition. You can see the graphical representation of execution plan with Query | Include Actual Execution plan.

In Chapter 15, SQL Server Compact Edition Performance Tuning, you will learn how to exploit this feature of SQL Server Management Studio in order to debug and tune SQL Server Compact Edition database queries.

Managing the Database

Using SSMS you can create a new SQL Server Compact Edition database. Not only can you modify the properties of the database, you can also drop the database. With Management Studio you can even do additional operations such as the following:

  • Verify the SQL Server Compact Edition database.
  • Repair the SQL Server Compact Edition database.
  • Shrink the SQL Server Compact Edition database.
  • Compact the SQL Server Compact Edition database.

In this section you will also use SSMS interface to complete these operations.

Using Replication Wizards

For enterprise applications you will be synchronizing the data between SQL Server and the SQL Server Compact Edition database. The SQL Server Compact Edition database provides a built-in mechanism Merge Replication and Remote Data Access to synchronize the data with SQL Server. To implement Merge Replication you need to configure a Web Server, set up the Distributor, set up the Publication, and set up the Subscription.

Before using a Merge Replication you need to provide configuration details—central database, remote database, Snapshot agent folder, security mechanism, etc. You can set up this configuration either programmatically or by using Wizards. SQL Server Management Studio provides tools and wizards to set up publication, subscription, and a snapshot folder. It even generates a template code that can be used for Data Synchronization. The set of wizards remains the same whether you are setting up Merge Replication between two SQL Server instances or between SQL Server and SQL Server CE database.

Using the Publication Wizard

For database synchronization between SQL Server and SQL Server CE database, SQL Server first needs to publish the data. The database instance publishing the data is called Publisher. By using the SQL Server 2005 Publication Wizard you can specify the Publisher, Publication type, snapshot folder, and articles to publish.

Using the Subscription Wizard

The SQL Server CE database synchronizing data with backend SQL Server is called Subscriber. The subscription wizard allows you to create and manage subscriptions for SQL Server and SQL Server CE databases. You can use the properties dialog box for modifying properties of existing subscriptions.

Using the Configure Web Synchronization Wizard

You need to set up a Web Server for both Merge Replication and Remote Data Access. SQL Server Management Studio provides a Configure Web synchronization wizard. This wizard helps you to specify the Web Server, virtual directory, and authentication mechanism.

To use Web Synchronization Wizard, Publication Wizard, and Synchronization Wizard, you first need to learn the Merge Replication and Remote Data access features. You will learn how to use these wizards in Chapter 12, Synchronizing Data with Merge Replication.

NOTE

You can run the Configure Web Synchronization Wizard from SSMS or by running the executable Connwiz30.exe.

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020