interface supported: tables

Showing posts with label tables. Show all posts

Friday, March 30, 2012

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

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

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Hi all,

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:

..........................................................................................................

////////////////////////////////////////////////////////////////////////////////////////////////

(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();

Thanks in advance,

Scott Chang

|||

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, Thanks for your response and instructions.

Thanks again,

Scott Chang

|||Scott,

|||

Hi,

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Vidhya, Thanks for your responses and instructions.

(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.

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional information:

Thanks,

Scott Chang

sql

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Hi all,

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:

..........................................................................................................

////////////////////////////////////////////////////////////////////////////////////////////////

(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();

Thanks in advance,

Scott Chang

|||

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, Thanks for your response and instructions.

Thanks again,

Scott Chang

|||Scott,

|||

Hi,

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Vidhya, Thanks for your responses and instructions.

(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.

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional information:

Thanks,

Scott Chang

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Hi all,

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:

..........................................................................................................

////////////////////////////////////////////////////////////////////////////////////////////////

(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();

Thanks in advance,

Scott Chang

|||

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, Thanks for your response and instructions.

Thanks again,

Scott Chang

|||Scott,

|||

Hi,

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Vidhya, Thanks for your responses and instructions.

(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.

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional information:

Thanks,

Scott Chang

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Hi all,

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:

..........................................................................................................

////////////////////////////////////////////////////////////////////////////////////////////////

(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();

Thanks in advance,

Scott Chang

|||

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, Thanks for your response and instructions.

Thanks again,

Scott Chang

|||Scott,

|||

Hi,

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens and Vidhya, Thanks for your responses and instructions.

(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.

Microsoft SQL Server Management Studio Express

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

Additional information:

Thanks,

Scott Chang

Wednesday, March 28, 2012

NoRows Property in RS2005

Is the NoRows property of a Data Region broken in Reporting Services 2005?
I have set the message to be displayed for both subreports and tables and
the message is never displayed when the data set is empty. It used to work
in RS2000.
Thanks,
TomHi Tom,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. The NoRows Property works fine in SQL Reporting
Service 2005. One thing to note is that, if you put a table in a
dataregion, you can only get the NoRows Message defined in the dataregion,
not the one in table.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
The NoRows property is available for both my table & included subreports,
however, in the case of the subreports, they still process even if the
NoRows property is set & the data set is empty.
As I said in my previous post:
I have tried setting the NoRows property for both the subreport and the
table but neither works. That's what prompted me to ask if NoRows is broken
in RS2005.
The documentation clearly states "When the dataset for a data region returns
no data, the data region is not rendered. Instead, a text box is rendered
that displays the value of the NoRows property." and just as clearly, it
doesn't work for me.
Tom
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:ZZf44cVTGHA.1952@.TK2MSFTNGXA03.phx.gbl...
> Hi Tom,
> Welcome to use MSDN Managed Newsgroup Support.
> I have tested on my side. The NoRows Property works fine in SQL Reporting
> Service 2005. One thing to note is that, if you put a table in a
> dataregion, you can only get the NoRows Message defined in the dataregion,
> not the one in table.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Tom,
I have tested in my side. It still works fine for subreport.
Here is my steps:
1. I create a report named Report1. Query data from a datasource, and put
the data in a table. I did not set the NoRows Property.
2. I create a report named Report2. Add a subreport control. And then I
set the ReportName as Report1 and NoRows Property to "Test".
3. I delete all the data in the database so that there will be no data in
the datasource.
4. I deploy the report to the report server and when I access the Report2,
it shows "Test" correctly.
Would you please check this in your side and tell me the result?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

NoRows Leaves Extra White Space

I have a report with multiple tables and datasets. The problem is if one of
the tables/datasets doesn't have any data I get too much white space between
the sections. I tried to set the visiblity property on the table but that
doesn't work if there is no data being returned via the dataset.
For example:
John Does (1st table)
Background Info (2nd table)
Education (3rd table)
This looks fine because there is data in all 3 tables/datasets
Now if there is no background info I get this:
John Doe (1st table)
Education (3rd table)
When I want this:
John Doe (1st table)
Education (3rd table)
I can't combine the info to one table for reasons I won't go into.
Thanks!Try putting each table inside a rectangle and setting the rectangle property
visiblility to Hidden = True if no rows are returned for the rectangles child
table. Additionally, put all 3 rectangles/tables inside a 'master'
rectangle. This will help control some of your whitespace issues.
--
Andy Potter
blog: http://sqlreportingservices.spaces.live.com/
"Anonymous" wrote:
> I have a report with multiple tables and datasets. The problem is if one of
> the tables/datasets doesn't have any data I get too much white space between
> the sections. I tried to set the visiblity property on the table but that
> doesn't work if there is no data being returned via the dataset.
> For example:
> John Does (1st table)
> Background Info (2nd table)
> Education (3rd table)
> This looks fine because there is data in all 3 tables/datasets
> Now if there is no background info I get this:
> John Doe (1st table)
>
> Education (3rd table)
> When I want this:
> John Doe (1st table)
> Education (3rd table)
> I can't combine the info to one table for reasons I won't go into.
> Thanks!
>

Normalizing my Database

Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table

Please help

I am thinking you need two more tables one for the inventory and another for the dates, delivery date, recieved date, purchase date and more. You want to separate out functional dependencies. Try the links below for sample data models and normalization tutorial. Hope this helps.

http://www.databaseanswers.org/data_models/

http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html

|||

Caddre is right, you probably need at the least, an inventory table (Tracks current stock), a receiving table (Tracks products received, date/time, etc), and a sales table (Tracks products sold, date/time) in addition to your products table.

Create triggers on the receiving table to update the quantities when records are added/updated/deleted.

Same for the sales table.

|||

Motley:
Caddre is right, you probably need at the least, an inventory table (Tracks current stock), a receiving table (Tracks products received, date/time, etc), and a sales table (Tracks products sold, date/time) in addition to your products table.
Create triggers on the receiving table to update the quantities when records are added/updated/deleted.
Same for the sales table.

hi thanks for ur response. The recieving table is my Delivaries Table (DelivaryId,DelivaryNo,Quantity,Date) if i understand u very well u mean i sholud create another table for keeping current stock record but if i do what then happens to the quantity column in the delivaries table

|||It would be the quantity delivered. You would then use that in the insert/update trigger to update the inventory table's quantity (Or what is commonly called "on hand" quantity).|||i think im begining to understand u but please try to expanciate more on what trigger does to a table or table column|||

CREATE TRIGGER (Transact-SQL)

Designing DDL Triggers

You can find more if you search on internet.

Normalizing Address Information...

THE LAYOUT:
I have two tables: "Applicant_T" and "StreetSuffix_T"

The "Applicant_T" table contains fields for the applicant's current address, previous address and employer address. Each address is broken up into parts (i.e., street number, street name, street suffix, etc.). For this discussion, I will focus on the street suffix. For each of the addresses, I have a street suffix field as follows:

[Applicant_T]
CurrSuffix
PrevSuffix
EmpSuffix

The "StreetSuffix_T" table contains the postal service approved street suffix names. There are two fields as follows:

[StreetSuffix_T]
SuffixID <--this is the primary key
Name

For each of the addresses in the Applicant_T table, I input the SuffixID of the StreetSuffix_T table.

THE PROBLEM:
I have never created a view that would require the primary key of one table to be associated with multiple fields of another table (i.e., SuffixID-->CurrSuffix, SuffixID-->PrevSuffix, SuffixID-->EmpSuffix). I want to create a view of the Applicant_T table that will show the suffix name from the StreetSuffix_T table for each of the suffix fields in the Applicant_T table. How is this done?I got the solution from another forum. It is as follows:


create view ApplicantAddresses
 ( currstreetnumber
 , currstreetname
 , ...
 , currsuffixname
 , prevsuffixname
 , empsuffixname
 )
as
select currstreetnumber
 , currstreetname
 , ...
 , c.name
 , p.name
 , e.name
 from Applicant_T
inner
 join StreetSuffix_T c
 on currsuffix = c.SuffixID
inner
 join StreetSuffix_T p
 on prevsuffix = p.SuffixID
inner
 join StreetSuffix_T e
 on empsuffix = e.SuffixID

|||Having the primary key of one table be associated with mulitple fields in another table is not unusual. However, it does bring a "Spock's raised eyebrow" indicating a table design that could be improved upon. To use the same lookup table more than once you just need to create table aliases so that the query can distinguish between the tables. You also need aliases in the Select clause so you can tell them apart.


SELECT dbo.StreetSuffix_T.Suffix AS CurrSuffix, StreetSuffix_T_1.Suffix AS PrevSuffix, StreetSuffix_T_2.Suffix AS EmpSuffix
FROM dbo.Applicant_T INNER JOIN
 dbo.StreetSuffix_T ON dbo.Applicant_T.CurrSuffix = dbo.StreetSuffix_T.SuffixID INNER JOIN
 dbo.StreetSuffix_T StreetSuffix_T_1 ON dbo.Applicant_T.PrevSuffix = StreetSuffix_T_1.SuffixID INNER JOIN
 dbo.StreetSuffix_T StreetSuffix_T_2 ON dbo.Applicant_T.EmpSuffix = StreetSuffix_T_2.SuffixID

However, the design of Applicant_T seems a bit suspect since you have multiple addresses. Maybe today you want current, former, and employer addresses but in the future you might need another one (spouses employer address, delivery address, second employer address, whatever). With your current design you'd need to add a bunch of new columns whenever you add a new address type.

It may be better to have a separate table that just handles addresses. It might have two keys, one to link back to the applicant_T table and another for the type of address held in that record. (0=current address, 1=previous address, 100=employer address, 1000=dog groomers address).|||McMurdoStation,

You are right. In my pursuit of a solution to a short-term issue, I neglected to consider the longer-term impact of my decision. I am going to change the table design.

Thanks for your input :)|||Yeah, you save less than a byte to normalize that, and lose tons of rotations in the joins.

Sacrifice space for efficiency in runtimes every once in awhile.|||The idea of normalization isn't to save space. If normalizing causes performance bottlenecks you can do things to address that. But that problem is easier to address when and if necessary than having a data model that can't handle change.|||In my particular case, scalability is of high importance. Normalizing the addresses in the manner McMurdoStation suggested, provides such scalability.

Monday, March 26, 2012

Normalization question

Hello,
I have a question about the benefits/gain or correctness
according to RDBMS convention of normalizing data tables.
My table in question is a single table that contains about
5,000,000 records that are queried to generate reports.
It does not feed any other tables. The un-normalized
table (tbl1) looks like this:
idNum Area Date Metric Value
1 A 1/1/2005 Q 3
1 A 1/1/2005 R 0
1 A 1/1/2005 S 17
1 A 1/1/2005 T 9
2 B 1/1/2005 Q 11
2 B 1/1/2005 R 8
2 B 1/1/2005 S 2
2 B 1/1/2005 T 30
...
10 A 1/2/2005 Q 3
10 A 1/2/2005 R 0
10 A 1/2/2005 S 17
10 A 1/2/2005 T 9
11 B 1/2/2005 Q 20
11 B 1/2/2005 R 22
11 B 1/2/2005 S 0
11 B 1/2/2005 T 17
...
So in this table I am capturing 4 metrics, Q, R, S, T for
each Area (A, B, ...) for each day. So for each Metric, I
am repeating the idNum, Area, and Date in each row.
In the normalized version, tbl1 would contain one
record/row for each idNum, Area, Date, and tbl2 would
contain the 4 rows of idNums, Metrics for each idNum,
Area, Date in tbl1
tbl1 tbl2
idNum Area Date idNum Metric Value
1 A 1/1/2005 1 Q 3
1 R 0
1 S 17
1 T 9
tbl1 would now shrink from 5,000,000 rows to about 50,000
(there's actually like 20 metrics for each area) but tbl2
would still have the 5,000,000 rows. My server has
300gigs drivespace, two 3gig cpu's, 8gigs ram. With the
normalized tables, I have 2 tables to manage now and have
to add a join for all the queries. I don't see the
benefit of normalizing my table for this scenario.
However, there may be concepts of RDBMS that I don't
understand where normalization is the correct way to go.
Any advice on this would be greatly appreciated.
Thanks,
Ron"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:1eec01c515d5$d62407b0$a601280a@.phx.gbl...
> idNum Area Date Metric Value
> 1 A 1/1/2005 Q 3
> 1 A 1/1/2005 R 0
> 1 A 1/1/2005 S 17
> 1 A 1/1/2005 T 9
> 2 B 1/1/2005 Q 11
> 2 B 1/1/2005 R 8
> 2 B 1/1/2005 S 2
> 2 B 1/1/2005 T 30
The main practical benefit of normalization (IMO) is elimination of
duplicate data. This has two sub-benefits: 1) reduction of storage space,
and 2) ease of updating. Depending on your circumstances, either or both of
these sub-benefits may not apply.|||Thanks for your reply. The main thing I was checking for
was if there were some concept I wasn't aware of. I can
see the reduced storage. And I suppose it is easier to
search through 50,000 rows vs 5,000,000 rows for stuff.
The conclusion I come to is that you can get more
performance with Normalization - or - it is easier to
write code for a single table but less performance. At my
place, the coding seems to change more than the data. I
guess the bottom line will be who is going to do more
work - me or the computer.

>--Original Message--
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1eec01c515d5$d62407b0$a601280a@.phx.gbl...
>The main practical benefit of normalization (IMO) is
elimination of
>duplicate data. This has two sub-benefits: 1) reduction
of storage space,
>and 2) ease of updating. Depending on your circumstances,
either or both of
>these sub-benefits may not apply.
>
>.
>|||Normalizing the table will prevent update anomalies and may also increase
performance, despite the joins. Querying a table with 50,000 records with a
n
index takes roughly 16 compares, whereas querying 5,000,000 records with an
index takes roughly 23 compares. In addition, smaller records mean more
records per data page, which should reduce disk reads. There is no doubt
that reading the entire table will perform slower with a join, but it has
been my experience that most queries retrieve only a small fraction of the
data, so an inner loop or merge join (with a clustered index on common) fiel
d
will actually perform better with two tables because of the reduced number o
f
disk reads required to retrieve the data.
I make it a habit to fully normalize a database and then denormalize only to
simplify joins of many tables, and then only when there is a perceived
performance problem that cannot be fixed with a judiciously placed index.
"Ron" wrote:

> Hello,
> I have a question about the benefits/gain or correctness
> according to RDBMS convention of normalizing data tables.
> My table in question is a single table that contains about
> 5,000,000 records that are queried to generate reports.
> It does not feed any other tables. The un-normalized
> table (tbl1) looks like this:
> idNum Area Date Metric Value
> 1 A 1/1/2005 Q 3
> 1 A 1/1/2005 R 0
> 1 A 1/1/2005 S 17
> 1 A 1/1/2005 T 9
> 2 B 1/1/2005 Q 11
> 2 B 1/1/2005 R 8
> 2 B 1/1/2005 S 2
> 2 B 1/1/2005 T 30
> ...
> 10 A 1/2/2005 Q 3
> 10 A 1/2/2005 R 0
> 10 A 1/2/2005 S 17
> 10 A 1/2/2005 T 9
> 11 B 1/2/2005 Q 20
> 11 B 1/2/2005 R 22
> 11 B 1/2/2005 S 0
> 11 B 1/2/2005 T 17
> ...
> So in this table I am capturing 4 metrics, Q, R, S, T for
> each Area (A, B, ...) for each day. So for each Metric, I
> am repeating the idNum, Area, and Date in each row.
> In the normalized version, tbl1 would contain one
> record/row for each idNum, Area, Date, and tbl2 would
> contain the 4 rows of idNums, Metrics for each idNum,
> Area, Date in tbl1
> tbl1 tbl2
> idNum Area Date idNum Metric Value
> 1 A 1/1/2005 1 Q 3
> 1 R 0
> 1 S 17
> 1 T 9
> tbl1 would now shrink from 5,000,000 rows to about 50,000
> (there's actually like 20 metrics for each area) but tbl2
> would still have the 5,000,000 rows. My server has
> 300gigs drivespace, two 3gig cpu's, 8gigs ram. With the
> normalized tables, I have 2 tables to manage now and have
> to add a join for all the queries. I don't see the
> benefit of normalizing my table for this scenario.
> However, there may be concepts of RDBMS that I don't
> understand where normalization is the correct way to go.
> Any advice on this would be greatly appreciated.
> Thanks,
> Ron
>|||>> So in this table I am capturing 4 metrics, Q, R, S, T for each Area
(A, B, ...) for each day. So for each Metric, I am repeating the
idNum, Area, and Date in each row. <<
1) id_num is redundant.
2) DATE is a reserved word.
3) Since the metrics are attribute of a reading, they need to be in
columns, not expressed as values. A normalized table would look like
this:
CREATE TABLE Readings
(area_code CHAR(1) NOT NULL,
collection_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
Q_value INTEGER DEFAULT 0 NOT NULL,
R_value INTEGER DEFAULT 0 NOT NULL,
S_value INTEGER DEFAULT 0 NOT NULL,
T_value INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY (area_code, collection_date ));
Obviously you will need to add CHECK() constraints and the right
DEFAULT values.

Normalisation

I'm trying to normalise a badly designed database. I have created new tables that split one huge table up, and I think it is now in 3NF.

My problem is that once i've created the new tables, How do I get the data out of the huge table into the smaller ones. Are there SQL commands to do this? I'm using Oracle 8i.

Any help would be greatly appreciated.

Many thanks,

Francis.Hello,

there are many ways to do this :
For my explanation I use BIG as the name for the bigtable and SMALL1, SMALL2 for the new designed tables :

1)
Create a SQL statement that give you back the dateiled result of table SMALL1 and SMALL2

After that use
INSERT INTO SMALL1 (field1, field2...)
SELECT field1, field2 ... FROM big WHERE ...

The Select part is your designed Select statement .. to this with both queries ... OK ?

2) Export the datas with SQLLOADER and import them ... (see SQLLOADER documentation)

3) Write a PL/SQL procedure to select the datas from the big table and put them into the new one ...

If you have problems writing the procedure - just let me know - I will help you ...

Hope this helps ?

Greetings
Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

*** did you ever use Oracle8i, MySQL, Interbase and Birdstep RDM Server databases at the same time ? No ? Use AlligatorSQL ***|||Thanks for you're prompt response.

I had thought of writing it as;

CREATE TABLE SMALL1(Column3, Column2, Column6)
AS
SELECT Column3, Column2, Column6
FROM BIGTABLE;

and do the same for all the other small tables. Then drop the big table.

Would this work?

Many thanks again.|||Hello,

thats perfect and very quick too ... I though, that you have already created the tables ...

Do it in this way :)

By the way ... are you interested in a new Oracle tool ... just send me an E-Mail to webmaster@.alligatorsql.com and I give you some information about AlligatorSQL.

Thanks

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Friday, March 23, 2012

noob question of how to combine two tables from two different servers.

I got two tables from 2 different server A and B.

I do a OLEDB source [server A] with "select ID, currencyNo, exchangerate from table A"

I do another OLEDB [ server B] source with "select currencyNo, currencyName from table B"

i want to combine these two OLEDB sources with a resultset

"select ID, currencyNo, currencyName, exchangerate from table A , B

where A.currencyNo = B.CurrencyNo"

how do i do this in SSIS? sorry if i m a noob. I dun want to use linked servers. can someone help?

Running that kind of query is not something that SSIS is going to do magically for you. You have to ask your DBA to set link servers (assuming they are SQL Server).

Other option is to use 2 OLE DB source and then use a Merge Join transformation to merge both sources in a single output.

Both solutions may impact negatively the performance; but that will be really an issue depending in other factors like volume of data and hardware configuration.

A third option is to break work in 2 pieces; first extract data from each table and load it in a common database and then once both tables are in the same database you can use a single ole db source component.

As you can see you have several options

Rafael Salas

|||Just remember that if using a Merge Join transformation, your inputs need to be sorted on the key. Also, it is best to do the sort in your source SQL if possible, and avoid using the sort transformation in SSIS.

Phil|||

i got problems using the merge join....it say i need to sort the data....

|||That's what I said you needed to do in my previous post.|||Thanks, i solved my problem!!

Wednesday, March 21, 2012

non-matching recrods 2 tables (Newbie)

Hi,

I am new to SQL and these forums, and have a quick simple question.

I am trying to view the records that do not match in 2 tables. I have tried a few different ways but keep getting results in the hundreds of thousands and the table only has 36 thousand reocrds.

Here is an example of what the two tables contain.

Table 1
customer_no eaddress_no name address (36000 records)

Table2
customer_no eaddress_no email (17000 records)

I need to find out which customers are not in table 2 by linking the eaddress_no numbers.

This is one of the scripts i ran that gives me the large results with a ton of dups:

SELECT *
FROM T_EADDRESS inner JOIN
T_CUST_LOGIN ON T_EADDRESS.eaddress_no
!= T_CUST_LOGIN.eaddress_no

Thanks in advance for any help with this.Try this:

SELECT Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.eaddress_no <> Table2.eaddress_no|||I got the same results:

Alot of dups.

I did just find this at SQLTeam after i posted this message. I am just getting ready to try it now.

SELECT A.No,A.Date
FROM A LEFT OUTER JOIN B ON A.No = B.No AND A.Date = B.Date WHERE B.No IS NOT NULL|||If your query does not work, try this:

SELECT *
FROM Table1
WHERE eaddress_no NOT IN
(SELECT eaddress_no FROM Table2)|||gyuan that worked.
yours looked a little easier for me to follow so i just used that one.
Thanks a million.|||Using a left join is generally preferable to NOT EXISTS.

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null

This can also be easily be modified to show records that are missing from either table:

SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no)
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||Thanks Blind Man,

Those make sense to me now . I helps to see the table names in the query.|||Blindman,

you have an extra ) where would the opening one go?|||Just remove it...you don't need it...|||Sorry. No Parenthesis is necessary. It was a copy/paste error.|||I tried that and it returns no records.|||If you run this

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null

and no records are returned, then there are no eaddress_no values in Table1 that are not also in Table2.

Run the second one with the FULL OUTER JOIN clause and see what you get:

SELECT Table1.*, Table2.*
FROM Table1 full outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table1.eaddress_no is null or Table2.eaddress_no is null|||OOPS!
WHERE clause was looking at wrong table! We want to see if Table2 is null...

SELECT Table1.*
FROM Table1
left outer join Table2 on Table1.eaddress_no = Table2.eaddress_no
WHERE Table2.eaddress_no is null|||Yep that did it. And it took about 2 seconds few to run.

Thanks blindman, now i see.

Tuesday, March 20, 2012

Non-Equi Joins

I am taking Microsoft Course 4329 on Joining Data from Multiple Tables in
SQL Server 2005, and I am stumped in the Lab, Task 2, Step 3.

How can I rewrite the following SQL Statement to produce the same results by
using a non-equality operator in the join?

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e join person.contact c on e.contactid=c.contactid
where gender='f' and vacationhours>50
order by hours desc

Thanks in advance for your help!

--Justin--

There may be mulitple answers,

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e CROSS JOIN person.contact c where e.contactid=c.contactid
and gender='f' and vacationhours>50
order by hours desc

use adventureworks
select firstname + ' ' + lastname as name, gender, vacationhours as hours
from humanresources.employee e JOIN person.contact c On vacationhours>50 where e.contactid=c.contactid
and gender='f'
order by hours desc

Non-Deterministic function in DPV

Hi! Using the new SQL 2005 functionality where the optimizer determines
where the data is based on the check constraints defined on the tables, can
you use a non-deterministic function such as getdate() in the check
constraints as long as you have another mechanism to make certain the data
gets moved when the date is no longer in the valid range.
For example: Table 1 has data that is 12 months old or less. Table 2 has
data that is 13 months old or more.
Thanks.alw (alw@.discussions.microsoft.com) writes:
> Hi! Using the new SQL 2005 functionality where the optimizer determines
> where the data is based on the check constraints defined on the tables,
> can you use a non-deterministic function such as getdate() in the check
> constraints as long as you have another mechanism to make certain the
> data gets moved when the date is no longer in the valid range.
> For example: Table 1 has data that is 12 months old or less. Table 2 has
> data that is 13 months old or more.
No. It simply does not make any sense. SQL Server cannot trust you to
actually use that mechanism. If you want to move data daily, then you
have to redefine the constraints daily. Not to talk about if your datetime
column also has minutes and milliseconds...
Note that in SQL 2005 you also have partitioned tables.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Monday, March 12, 2012

Non replicated tables and MS Access

What happens whey you try to edit the data in these
tables - any error messages etc? How are you editing
them - using Enterprise manager? (If so you must move off
the line for the edit to be committed).
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Manage to find the problem. An index was missing from the table which
prohibited MS Access from updating a row.
Howevere I was always able to edit a row using MS Query Analyser
Thanks
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:2b2201c49ee7$e565e230$a301280a@.phx.gbl...
> What happens whey you try to edit the data in these
> tables - any error messages etc? How are you editing
> them - using Enterprise manager? (If so you must move off
> the line for the edit to be committed).
> Regards,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Friday, March 9, 2012

NON DOMAIN AND DOMAIN CONNECTION

Hi all,

it happen to me a strange problem:

i have a mdb file (in Access 2K) with SQL Server 2K linked tables who
runs on a workstation which is on a different domain that the SQL
Server. It works.

If i create a mdb file from a workstation which is a the domain of the
SQL Server and then i run it a my non-domain workstation i have error
message:

But if i reattached my tables it works.

If someone have an idea...

PS: same ODBC on both machinesRavieR (principle@.caramail.com) writes:
> it happen to me a strange problem:
> i have a mdb file (in Access 2K) with SQL Server 2K linked tables who
> runs on a workstation which is on a different domain that the SQL
> Server. It works.
> If i create a mdb file from a workstation which is a the domain of the
> SQL Server and then i run it a my non-domain workstation i have error
> message:
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection

I'm nor sure that I followed the confiuguration exactly, but trusted
connections in workgroups is usually an iffy thing. For instance, at
home I have a non-domain workstation, on which I run a non-domain
virtual machine where I have the beta version of SQL 2005. From the
host machine I cannot conenct with trusted connection to SQL2005 on
the virtual machine, but not the other way round.

I think you need to be logged with the same username on both, and the
two users need to have the same password.

You may be better off with SQL authentication in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Nomore snapshot without locking tables?

HI There

After upgrading my publishers to 2005 i noticed that i cannot specify not to lock tables during snapshot during publication creation, also not on publication properties, and i see sp_addpublication has no such parameter, is there no longer an option not to lock publication tables during snapshot?

Thanx

Hi Dietz,

You can specify the @.sync_method parameter of sp_addpublication to be concurrent or concurrent_c.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl_4s32.asp

[ @.sync_method=] 'sync_method'

Is the synchronization mode. sync_method is nvarchar(13), and can be one of the following values.

Value

Description

native

Produces native-mode bulk copy program output of all tables. Not supported for Oracle Publishers.

character

Produces character-mode bulk copy program output of all tables. For an Oracle Publisher, character is valid only for snapshot replication.

concurrent

Produces native-mode bulk copy program output of all tables but does not lock tables during the snapshot. Only supported for transactional publications. Not supported for Oracle Publishers.

concurrent_c

Produces character-mode bulk copy program output of all tables but does not lock tables during the snapshot. Only supported for transactional publications.

NULL (default)

Defaults to native for Microsoft SQL Server Publishers. For non-SQL Server Publishers, defaults to character when the value of repl_freq is Snapshot and to concurrent_c for all other cases.

Regards,

Gary

|||

Thanx Gary

As far as i can see this option is not available though management studio when creating a publication or viewing publication properties after creation, correct ? Only through TSQL.

Friday, March 30, 2012

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Northwind database in SQL Server Express is busted and gone,after SqlCommand fails in VB2005Expr

Wednesday, March 28, 2012

NoRows Property in RS2005

NoRows Leaves Extra White Space

Normalizing my Database

Normalizing Address Information...

Monday, March 26, 2012

Normalization question

Normalisation

Friday, March 23, 2012

noob question of how to combine two tables from two different servers.

Wednesday, March 21, 2012

non-matching recrods 2 tables (Newbie)

Tuesday, March 20, 2012

Non-Equi Joins

Non-Deterministic function in DPV

Monday, March 12, 2012

Non replicated tables and MS Access

Friday, March 9, 2012

NON DOMAIN AND DOMAIN CONNECTION

Nomore snapshot without locking tables?

interface supported

Blog Archive

About Me