Click Next to move past the intro screen and onto the next page, then select the objects you want to script. Note that you can script all SQL objects here, not just tables. This is because this wizard allows you to script schema and data. Importantly, schema only is the default, which you’ll need to change on the next page. Click Next.

Now you’ll be presented with a set of options mainly relating to how the script is going to be output, however there is an unassuming button labelled Advanced. Clicking this button will give you a dialog with a list of options. The last option under the General section is “Types of data to script”, which you should change from “Schema only” to “Data only”, or “Schema and data”.

Finally, decide where you want the script to do and you should be able to generate your INSERT statements.

Parameters or Arguments

TOP (top_value)

Returns the top number of rows in the result set based on top_value. For example, TOP(10) would return the top 10 rows from the full result set.

PERCENT

Optional. If PERCENT is specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set.

WITH TIES

Optional. If the WITH TIES clause is specified, then rows tied in last place within the limited result set are returned. This may result in more rows be returned than the TOP parameter permits.

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

ORDER BY expression

Optional. It is used in the SELECT TOP statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC is descending order.

Example – Using TOP keyword

Let’s look at a SQL Server example, where we use the TOP keyword in the SELECT statement.

This SQL Server SELECT TOP example would select the first 5 records from the employees table where the last_name is ‘Anderson’. If there are other records in the employees table that have a last_name of ‘Anderson’, they will not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

SELECT TOP(5) WITH TIES
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the 5th top record is a tie, then all records in this tie position would be returned by the SELECT TOP statement. This will result in more than 5 records being returned.

Example – Using TOP PERCENT keyword

Let’s look at a SQL Server example, where we use the TOP PERCENT keyword in the SELECT statement.

This SQL Server SELECT TOP example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the employees table where the last_name is ‘Anderson’. The other 90% of the result set would not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the last position in the SELECT TOP(10) PERCENT record set is a tie, then these tied records would be returned by the SELECT TOP statement. This will result in more than 10% of the full record set being returned.

Original Post: http://www.tech-recipes.com/rx/49959/finding-duplicate-records-using-group-by-in-sql-server/

There are various times when we need to find duplicate records in SQL Server. It is possible to find duplicates using DISTINCT, ROW NUMBER as well as the GROUP BY approach.

Duplicate records can create problems sometimes when displaying reports or performing a Multiple Insert update. Finding duplicate records in a database needs further investigation. In some cases, duplicate records are positive, but it all depends on the data and the database design as well.

For example, if a customer has ordered the same product twice on the same date with the the same shipping and billing address, then this may result in a duplicate record.

Let us create a table Customer with First Name, Last Name, and Mobile Number fields.

In this blog, I’m going to walk you through the process of converting the MySQL Sakila-DB sample database to SQL Server 2008 R2 Express using the SQL Server Migration Assistant for MySQL v1.0 [Updated: Please obtain the lastest SSMA for MySQL] (SSMA). The Sakila-DB database has tables, views, stored procedures, functions and triggers that make the conversion interesting. The sample is based on the Inno-DB example, but does have one MyISAM table. SSMA also allows you to migrate your MySQL databases to SQL Azure, but we’ll save that topic for another post.

Downloading SQL Server 2008 R2 Express and SSMA

The easiest way to download SQL Server 2008 R2 Express, SQL Server Management Studio and SSMA is through the Microsoft Web Platform Installer (WPI). Once you’ve downloaded WPI, you can select from a variety of tools and products that can get you up and running using IIS, PHP, and SQL Server in no time.

I’ll focus on the minimum set of tools you need to get SQL Server 2008 R2 Express and SSMA up and running. once you launch WPI, click on the Products tab at the top tool and then select Database in the navigation page. In the image above, I’ve already installed the tools, but for the new install, you’ll click on the Add buttons to the right of the circled products to get you up and running. If you are running your application under PHP, you might also want to select one of the two PHP drivers for SQL Server as well. Once you’ve selected your tools, just click on the install button to start the process.

Downloading the MySQL ODBC Driver

WPI is not without flaws. SSMA requires the “MySQL OSBC Driver 5.1 or above” download to connect to your MySQL instance that comes from the MySQL downloads site. Once at the Download Connector/ODBC page, your need to download either the x32 or x64 version of the driver based on the machine architecture for the system you are running the SSMA client. Just follow the installation instructions from the installer. The default installation settings will be good enough to get you going.

Licensing SSMA

SSMA is a free tool, but does require you to associate a Microsoft Live ID for identification purposes. You must download a registration key. To help you with the registration process, a License Key Required dialog box opens the first time that you start the SSMA program. Use the following instructions to download a license key and associate the key with SSMA.

To license SSMA

Click Start, point to All Programs, point to Microsoft SQL Server Migration Assistant 2008 for MySQL, and then select Microsoft SQL Server Migration Assistant 2008 for MySQL.

On the Sign In Web page, enter your Windows Live ID user name and password, and click Sign In.

A Windows Live ID is a Hotmail e-mail address, MSN e-mail address, or Microsoft Passport account. If you do not have one of these accounts, you will have to create a new account. To create a new account, click the Sign up now button.

On the SQL Server Migration Assistant for MySQL License Registration Web page, fill in at least the required fields, which are marked with a red asterisk, and then click Finish.

In the File Download dialog box, click Save.

In the Save As dialog box, locate the folder that is shown in the License Management dialog box, and then click Save.

SSMA for MySQL User Interface

After SSMA is installed and licensed, you can use SSMA to migrate MySQL databases to SQL Server 2008 or SQL Azure. It helps to become familiar with the SSMA user interface before you start. The following diagram shows the user interface for SSMA, including the metadata explorers, metadata, toolbars, output pane, and error list pane:

S

Basic Steps for Migration of MySQL to SQL Server

To start a migration, you’ll need to perform the following high level steps:

Create a new project.

Connect to a MySQL database.

After a successful connection, MySQL schemas will appear in MySQL Metadata Explorer. Right-click objects in MySQL Metadata Explorer to perform tasks such as create reports that assess conversions to SQL Server 2008 R2 Express. You can also perform these tasks by using the toolbars and menus.

You’ll then connect to your instance of SQL Server 2008 R2 Express. After a successful connection, a hierarchy of your existing databases will appear in SQL Server Metadata Explorer. After you convert MySQL schemas to SQL Server schemas, select those converted schemas in SQL Server Metadata Explorer, and then synchronize the schemas with SQL Server.

After you synchronize converted schemas with SQL Server 2008 R2 Express, you can return to MySQL Metadata Explorer and migrate data from MySQL schemas into target database.

Let’s walk through the specifics.

Create a MySQL Migration Project

To get started, you’ll create your new project using the File | New Project command.

You’ll enter in your project name and then confirm that you are migrating to SQL Server. The Migrate To dropdown also allows you to choose SQL Azure, but that’s for another post. Once you make your selection, you are locked into the target backend.

Connect to a MySQL Database

To Connect to your MySQL instance, you’ll issue the File | Connect to MySQL command or click on the tool bar button that launches the following dialog:

If you forgot to to install the MySQL ODBC driver mentioned at the beginning of this blog, simply go to the download site, install the driver, and then issue the Connect to MySQL command.

Create Report of Potential Conversion Issues

Once you are connected, you’ll see the MySQL instance in the MySQL Metadata Explorer. You’ll want to expand the Databases node along with the Sakila database node and then check the box next to Sakila. This selects the database you want to migrate. Next, right click on the Sakila database and select the Create Report command or press the Create Report command on the toolbar as shown below.

Here is an example of the Assessment Report for the Sakila database.

The Assessment Report window contains three panes:

The left pane contains the hierarchy of objects that are included in the assessment report. You can browse the hierarchy, and select objects and categories of objects to view conversion statistics and code.

The content of the right pane depends on the item that is selected in the left pane.

If a group of objects is selected, such as schema, the right pane contains a Conversion statistics pane and Objects by Categories pane. The Conversion Statistics pane shows the conversion statistics for the selected objects. The Objects by Categories pane shows the conversion statistics for the object or categories of objects.

If a function, procedure, table or view is selected, the right pane contains statistics, source code, and target code.

The top area shows the overall statistics for the object. You might have to expand Statistics to view this information.

The Source area shows the source code of the object that is selected in the left pane. The highlighted areas show problematic source code.

The bottom pane shows conversion messages, grouped by message number. You can click Errors, Warnings, or Info to view categories of messages, and then expand a group of messages. Click an individual message to select the object in the left pane and display the details in the right pane.

In future blog posts, we’ll work through the specific problems that are in this report. For now, we’ll ignore the problematic objects for the schema and data migration steps. For now, close the report and then uncheck Functions, Procedures and Views nodes to take them out of the conversion. Then uncheck the tables with errors as shown below.

Go ahead and click on the Create Reports command to verify that there are no errors.

Connect to SQL Server

It’s time to connect SSMA to your SQL Server 2008 R2 Express instance. For the Server name, you’ll need the server name and instance for the target server. Since we are using the WPI installation of SQL Server 2008 R2 Express, you’ll enter in the server name as .\SQLEXPRESS.

You can select an existing database to migrate to using the Database control. You can also type in the name of a new database. In this case, use Sakila as shown below.

Once you click connect, SSMA prompts you if you want to create the database. Choose Yes to create the new database. When connecting to SQL Server Express instances, you’ll receive the following warning indicating that you won’t be able to use the server-side data migration engine. This engine is used for larger migration projects.

You can Continue from this dialog to start the actual migration process.

Convert Schema

Now that you’ve connected to the target SQL Server instance, SSMA enables the Convert Schema command. Click the Convert Schema command. Once the conversion is finished, you should see the SQL Server Metadata Explorer populated with the tables listed in bold as shown below.

Synchronize with Database

To write the tables to the target, select the dbo node in the SQL Server Metadata Explorer and then issue the Tools | Synchronize with Database command. SSMA displays the Synchronize with Database dialog as shown below. In this example, the Tables node was manually expanded to show that no tables are actually on the database at this time.

When you click OK, SSMA issues the CREATE TABLE statements to create the objects on the SQL Server target. There are some errors in this example because many of the tables selected have foreign key relationships to some of the tables that we excluded earlier. These errors can be ignored for now.

Migrate Data

The last step is to migrate the data into the tables. To complete the migration, select the Tables node within the MySQL Metadata Explorer for the Sakila database. Then issue the Tools | Migrate Data command or press the command on the toolbar. The Data Migration process requires you to connect to the MySQL database and to the SQL Server database again. SSMA then proceeds with the data migration process and displays the Data Migration Reports as shown below.

Using SQL Server Management Studio

The migrated tables are now ready on the target SQL Server instance. To see the results, launch SQL Server Management Studio (SSMS) and connect using the server name as .\SQLEXPRESS. Expand out the Databases node to see the Sakila database. Expand the out the Sakila database tables and then right click on the actor table and issue the Select Top 1000 Rows command to view the data as shown below.

SQL Server Management Studio that is part of the WPI is a free rich Windows client tool from Microsoft that offers a rich development and management experience like SQLyog and MONyog.

Problem: I have got this problem in local instance of SQL Server 2008 R2 on my machine. There are several databases on this instance. But I am not able to see any of them from the object explorer.

I am able to query my databases from the new query window. But not able to see any of them.

Whenever I try to explore the databases I get this error :

Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926).

I have tried

Refreshing the connection

Reconnecting the connection

Restarting the service Sql Server (MSSQLSERVER).

Restarting the SQL Server Management Studio

Restarting my machine

I have also tried combinations of above, but nothing works.

My operating system is Windows 7 Ultimate (64 bit).

SQL Server Management Studio Version is 10.50.2500.0.

Solution 1

Open new query window

EXEC sp_resetstatus 'DB_Name'; (Explanation :sp_resetstatus turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in sys.databases. Also note that only logins having sysadmin privileges can perform this.)

ALTER DATABASE DB_Name SET EMERGENCY; (Explanation : Once the database is set to EMERGENCY mode it becomes a READ_ONLY copy and only members of sysadmin fixed server roles have privileges to access it.)

DBCC checkdb('DB_Name'); (Explanation : Check the integrity among all the objects.)

ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE; (Explanation : Set the database to single user mode.)

I am using SQL server 2008, i got suddenly today an error while i was accessing database from visual studio the error is database ‘msdb’ cannot be opened. it has been marked suspect by recovery 2008 error code 926

don’t know what to do to get out from this type of error. any one have any idea please let me know

There can be many reasons for a SQL Server database to go in a suspect mode when you connect to it – such as the device going offline, unavailability of database files, improper shutdown etc. Consider that you have a database named ‘test’ which is in suspect mode

You can bring it online using the following steps:

Reset the suspect flag

Set the database to emergency mode so that it becomes read only and not accessible to others

Check the integrity among all the objects

Set the database to single user mode

Repair the errors

Set the database to multi user mode, so that it can now be accessed by others

Here is the code to do the above tasks:

Here’s the same code for you to try out

EXEC sp_resetstatus ‘test’

ALTER DATABASE test SET EMERGENCY

DBCC CheckDB (‘test’)

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB (‘test’, REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE test SET MULTI_USER

The fastest way is to restore the MSDB database, but if it’s your first time doing that, here’s an easier shortcut.

Restore the MSDB backup onto an existing (working) database server, but use a different database name than MSDB. The restore should go quickly (because MSDB is typically very small), and you can then verify that your objects are in there.

Detach the database, and copy the mdf/ldf files over to the broken instance.

One gotcha – in step 1, make sure you’re restoring onto the same version of SQL Server. If the broken server is SQL Server 2005, don’t do the restore on SQL Server 2012, because the SQL 2005 instance won’t be able to attach databases that have been touched by a newer version of SQL Server.

One of our clients’ SQL Servers got corrupted. we were forced to uninstall all instances and all SQL Server programs in add/remove programs and then reinstall SQL Server because their backups were failing and useless.

I was able to re-attach the user databases but now I need to recover the SQL Server agent jobs, so first I tried creating a user database to hold the old msdb data called MSDBData_OLD, stopping the SQL Server service, replacing the .mdf file with the corrupted msdb’s .mdf file and deleteing the .ldf file, which would normally result in the database getting marked SUSPECT, and I could take it from there, but when I start the service again and click the database it says:

The database MSDBData_OLD is not accessible (ObjectExplorer)

Next I tried attaching with the log file and I got this:

Finally, I tried simply attaching the msdb database as a user database, but when I do, I get:

File activation failure. The physical file name “D:\SQL\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf” may be incorrect. The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.

D:\SQL\MSSQL10_50_.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf is the path of the old and restored SQL Server msdb database log file.

We are running SQL Server 2008 R2 64 bit SP1.

Is there any way to recover from this or am I forced to rebuild the jobs from scratch?

I have searched in google at least 2 hours to get a code to designed a dynamic slider but I have failed to fulfill my requirements. I have code like this to do this and want to share with you to save your valuable time. Just follow the steps…