Hi all,
I tried to use dbo.tables of Northwind database in SQL Server Express to do SqlCommand to populate a DataTable in DataSet. The SqlCommand failed in the project execution and I found that Northwind database in SQL Server Express is busted and gone (just the name "Northwind" remained in the SQL Server Management Studio Express). How can I prevent it from happening in my "SqlCommand-SqlConnection" project? Please help and advise.
I tried to repair my "Northwind" database by using the SQL2000SampleDb.msi of Northwind and pubs Sample Databases for SQL Server 2000 downloaded from the http://www.microsoft.com/downloads. My "pubs" database is still in my SQL Server Management Studio Express. How can I just repair my "Northwind" database by using the Microsoft SQL2000SampleDb.msi program? Please help and advise this matter too.
Thanks in advance,
Scott Chang
You should try to Drop or Delete the Northwind database and then recreate it from script or attach a fresh copy of the mdf file that you've download.
As to how to prevent the initial problem, you'd have to tell us what you did in the first place before anyone could offer a suggestion as to what might have gone wrong.
Mike
|||Hi Mike, Thanks for your response.
(1). I deleted the name of "Northwind" in the SQL Server Express. Then I tried to recreate it from script by executing the following two things at the command prompt:
cd C:\SQL Server 2000 Sample Databases
sqlcmd -S .\SQLExpress -i instnwnd.sql
I got the following errors:
Changed database context to 'master'.
Msg 1802, Level 16, State 4, Server NAB-WK-EN\SQLEXPRESS, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5170, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1
Cannot create file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\northwnd.ldf' because it already exists. Change the file path or the file name, and retry the operation.
Msg 15010, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Procedure sp_dboption, Line 64
The database 'Northwind' does not exist. Use sp_helpdb to show available databases.
Available databases:
--
master
tempdb
model
msdb
ChemDatabase
ssmsExpressDB
newDB
pubs
Halloween
AdventureWorks
AdventureWorksDW
CamReynoldGISKeyR1
shcDB
C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\NORTHWND.MDF
Msg 15010, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Procedure sp_dboption, Line 64
The database 'Northwind' does not exist. Use sp_helpdb to show available databases.
Available databases:
--
master
tempdb
model
msdb
ChemDatabase
ssmsExpressDB
newDB
pubs
Halloween
AdventureWorks
AdventureWorksDW
CamReynoldGISKeyR1
shcDB
C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\NORTHWND.MDF
Msg 911, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1
Could not locate entry in sysdatabases for database 'Northwind'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3726, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not drop object 'dbo.Customers' because it is referenced by a FOREIGN KEY constraint.
Msg 3726, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not drop object 'dbo.Employees' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Server NAB-WK-EN\SQLEXPRESS, Line 1
There is already an object named 'Employees' in the database.
Msg 1913, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1
The operation failed because an index or statistics with name 'LastName' already exists on table 'dbo.Employees'.
Msg 1913, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1
The operation failed because an index or statistics with name 'PostalCode' already exists on table 'dbo.Employees'.
Msg 2714, Level 16, State 6, Server NAB-WK-EN\SQLEXPRESS, Line 2
There is already an object named 'Customers' in the database.
Msg 1913, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 1
..........................................................................................................
..........................................................................................................
Msg 1779, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Table 'Territories' already has a primary key defined on it.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Server NAB-WK-EN\SQLEXPRESS, Line 2
There is already an object named 'FK_Territories_Region' in the database.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not create constraint. See previous errors.
Msg 1779, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Table 'EmployeeTerritories' already has a primary key defined on it.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Server NAB-WK-EN\SQLEXPRESS, Line 2
There is already an object named 'FK_EmployeeTerritories_Employees' in the database.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 2
Could not create constraint. See previous errors.
Msg 2714, Level 16, State 4, Server NAB-WK-EN\SQLEXPRESS, Line 3
There is already an object named 'FK_EmployeeTerritories_Territories' in the database.
Msg 1750, Level 16, State 1, Server NAB-WK-EN\SQLEXPRESS, Line 3
Could not create constraint. See previous errors.
////////////////////////////////////////////////////////////////////////////////////////////////
I think I did not delete the busted "Northwind" database in SQLExpress completely. I do not know how to attach a fresh copy of the mdf file that I have downloaded either. Please help and advise me how to resolve this problem.
(2). I feel that my "Northwind" database in SQL Server Express was busted, when I tried to run the following code statements (of my SqlBulkCopy project):
using System;
using System.Data;
using System.Data.SqlClient;
namespace SqlBulkCopySample
.........................................
.........................................
private void btnStart_Click(object sender, EventArgs e)
{
String sourceConnectionString=
"Data Source=.SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
String destinationConnectionString=
"Data Source=.SQLEXPRESS;Initial Catalog=SqlBulkCopySample;Integrated Security=True";
DataTable data=SelectDataFromSource(sourceConnectionString);
CopyDataToDestination(destinationConnectionString, data);
}
private DataTable SelectDataFromSource(String connectionString)
{
DataTable data=new DataTable();
using (SqlConnection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("SelectOrders", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
SqlDataReader reader = command.ExecuteReader();
data Load(reader);
}
return data;
}
................................
...............................
/////////////////////////////////////////////////////////////////////////////////
I did not know how to create the stored procedure 'SelectOrders' in SQL Server Express yet. When I ran the SqlBulkCopySample, I got the following error:
SqlException was unhandled
Could not find stored procedure 'SelectOrders'
that were pointing to the secend staement of the following code statements:
connection.Open()
SqlDataReader reader = command.ExecuteReader();
Mike, please help again and tell me how I can prevent the "Northwind" database from being busted, if there is a mistake or wrong logic in the code statements of my ADO.NET 2.0-Visual C# 2005 Express (or VB 2005 Express) programming.
Thanks in advance,
Scott Chang
|||OK, from the list of available database it seems that you already have the Northwind database copied to your SQL directory and attached it as a user instance. In order to recreate it from scratch, you will have to deatch the database from the user instance (by closing the application which is using it), drop the files of the Northwind database in the mentioned directory and then afterwards create the database from scratch using the script.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Hi Jens, Thanks for your response and instructions.
I have a hard time to follow your instructions because I had deleted the name "Northwind" in my SQLEXPRESS already and I have the files of the Northwind database in my C:\SQL Server 2000 Sample Databases folder. I tried the two above-mentioned commands at the command prompt again and I got the same error output. Could you please help me again and give me more clearer instructions for re-installing the Northwind database?
Thanks again,
Scott Chang
|||Scott,Kindly check whether the physical files(mdf & ldf of northwind) for Northwind db is available at "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA" path. If yes try attaching the db in SQL Express. If this option fails, then delete those files and create the db as mentioned by jens.
Hi,
restart the service to make sure that no user instances are hanging around. Then see the sys.database_files to investigate if there are any database using the physical files. If not, try to attach them using a TSQL command or the SSMS Gui. if you have any further questions please come back.
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Hi Jens and Vidhya, Thanks for your responses and instructions.
(i) I deleted the Northwind.mdf and Northwind_log.LDF files in my C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data folder. Then I executed the 2 commands to re-install the Northwind database. It re-installed successfully.
(ii) This morning, I ran the following project:
///////////////-Form1.vb-///////////////////
Imports System
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Text
Imports System.Collections.Generic
Imports System.Drawing
Imports System.Windows.Forms
'
Public Class Form1
Dim bcp As SqlBulkCopy
Dim strConn As String = "Data Source=.\SQLExpress;" & _
"Initial Catalog=Northwind;Integrated Security=True"
Dim cn As New SqlConnection(strConn)
Dim options As SqlBulkCopyOptions = SqlBulkCopyOptions.Default
Dim txn As SqlTransaction = Nothing
'Supply a SqlConnction object, a value from SqlBulkCopyOptions
'and a SqlTransction object
bcp = New SqlBulkCopy(cn, options, txn)
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
End Sub
End Class
//////////////////////////////////////////////////////////////////////
It did not work and I got an error "Declaration Expected" on "bcp" of
bcp = New SqlBulkCopy(cn, options, txn).
I changed that code statement as a comment {
'bcp = New SqlBulkCopy(cn, options, txn)} and then I ran it again - it ran successfully without any error mesages.
When I checked the Databases of SQL Server Management Studio Express, I found that the name "Northwind" was there only and nothing was attached to the "Northwind" database!!!! When I clicked on "Northwind", I got the following error message:
Microsoft SQL Server Management Studio Express
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
Additional information:
One or more files do not match the primary file of the database. If you are attempting to attach a database, retry the operation with the correct files. If this is an existing database, the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error: 5173)
Now, my "Northwind" database in SQL Server Management Studio Express is busted again!!! I am very surprised to see this happened again. Could you please tell me why it happens and how to prevent it from happening?
Thanks,
Scott Chang
No comments:
Post a Comment