Using Northwind Database To Create A View (was Can You Lead On The Right Track...)
Jan 27, 2006
I am using Northwind database to Create a view showing every order that was shipped to Spain. Name the destination column 'DestinationSpain'. Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Here is my script:
use Northwind
GO
/*STEP 2, #1*/
/* does it exist, if so drop it */
if exist (select * from dbo.sysobjects
where id = object_id(N'[dbo].[OrdersToSpain]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[OrdersToSpain]
GO
/* Create the View */
create view "OrdersToSpain" AS
SELECT
Orders.OrderID AS Order_ID,
Orders.CustomerID AS Customer_ID,
Orders.OrderDate AS Ordered_Date.
Orders.ShippedDate AS Shipped_Date,
Orders.ShipCountry AS DestinationSpain
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipCounty LIKE '%SPAIN%'
GO
Here are the errors I am getting:
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.
Server: Msg 170, Level 15, State 1, Procedure OrdersToSpain, Line 7
Line 7: Incorrect syntax near '.'.
From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code:
--UpdateSPforNWcustomersTable.sql--
USE NORTHWIND
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo')
DROP PROCEDURE dbo.[SelectCustomers]
GO
CREATE PROCEDURE dbo.[SelectCustomers]
AS
SET NOCOUNT ON;
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo')
SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)
GO
==================================================================================== I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages:
Msg 911, Level 16, State 1, 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.
=============================================================================================================== I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise.
I have Visual Studio 2005 Beta 2.00 install which have installed the SQLEXPRESS server. I have the Script for the Northwind database which I need to run some demos but I can figure out how to execute this script. Can someone tell me what todo or how to attatched the database to this server. I also have a copy of the database already created. But When I tried login in into the database I get an error login fail. Which is the default user amd password for the northwind database? Tia Charles
We're using SQL Server 2000 and we'd like to be able to track our viewusage to tell which of our views get used most often. This will helpus determine which views we need to concentrate on first foroptimizing, indexing, etc... Does anybody know if there's a way totell when a view is opened?Thanks,Gary
I'm building an application that tracks courses and equivalent courses. For example, say I have course A which a user should get credit for if they have taken equivalent courses. So if a user has taken (course B or course C) AND (course D or course E) he should get credit for Course A. What's the best way to setup a SQL table to create these type of equivalent relationships?
Our MS SQL (SQL Server 2000) DBA has database privileges locked downpretty tightly. We end users/developers do not have administratorprivileges for most databases. That arrangement has worked out OK forthe most part. However, it's a bit aggravating that we can't evencreate our own database diagrams. When we attempt to do so (inEnterprise Manager), we get a dialog that says "You do not havesufficient privilege to create a new database diagram."Our DBA is so busy that it's difficult to get them to create ones forus. And even when they do, it seems that we can't even view it online-- we rely on the DBA to give us printed copies or screenshots for ourdocumentation. How ridiculous is that? We'd also like to be able tomanipulate the diagrams online so that we can more easily study ourtable structures, indexes, foreign key constraints, etc. In fact,there doesn't even seem to be any other way to easily view currentforeign key constraints.I realize that this might have to do with the diagramming tool havingthe ability to actually manipulate the database in ways we aren'tsupposed to do (e.g., like creating new tables). However, isn't therea "read-only" version of this tool that will give us what I'm lookingfor? Short of that, are there any other free or cheap tools that willprovide this? Thanks!(Please forgive my ignorance if this has been hashed out before.However, I did search news groups and the web before posting, figuringthis had been covered before, but I didn't find anything thatspecifically addressed this issue.)
I am getting the following error when trying to create a view in a database.
If I have db_owner rights so I should not have an issue? Any thoughts
TITLE: Microsoft SQL Server Management Studio ------------------------------
Property DefaultSchema is not available for Database '[GiftTraq]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (SQLEditors)
I recently installed VB Studio Express 2008, which also installed SQL Server Compact Edition. I was going thro the online tutorial from Microsoft, which tells you to create a connection to the Northwind DB. But, when I tried to connect, I get an error message saying access is not allowed. Note that the is running under Vista Home Premium. I have tried searching for an answer to this question, but have not found an answer that solves my problem. The closest was a similar problem for a C# application running in Internet Explorer, and the answer for that one was to change security settings in IE. But, for this demo, IE was not involved.
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
) ================================================= In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code: --Form_Kimmel.vb-- Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
End Class ==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
I have to do an exercize where i am using Northwind sample database. I am normally accustomed to linking to a database on another server. Is there a way i can just include the Northwind database to my project. In general, I need to find out how to link to the Northwind db so I can start querying and processing data. thanks for your help
I want use the the Northwind database do some example! I had installed the Visual Web Deveplopment Express Edition and installed the SQL Server 2005 Express Edition .but I build one new Website and connect the Northwind database but i can't find the it from the Database Explorer.why?
sara writes "Dear Sir/Madam, I use Sql Server 2005.I want to use Northwind Database & I have it but I could not Execute That It is the error: Msg 911, Level 16, State 1, Line 1 Could not locate entry in sysdatabases for database 'NorthwindCS'. No entry found with that name. Make sure that the name is entered correctly.
While my application is running on the production server, I want to develop on the test server. After a few weeks, I want to update the application, and have to update the database structure on the production server also with the most recent one from the testserver, but without deleting the current data on the production server.
I create/modify all tables in SQL Server 2005 via Management Studio. The application is built in VS2008 Pro. I'm using SubVersion (SVN).
I can let Management Studio generate scripts for every change and store them manualy, but that's a little bit too much work. What I want is a sort off version control solution. A solution that a service running on the background tracks every change on a specific database on my test server, and stores them as T-SQL code in a repository. Then I can collect all those T-SQL scripts, and run them against the production server.
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
Hi I have just started using the SQL Server 2005 Express Edition and wondering how to install the InstNwnd.sql from the sql file. I can't find any way to insert the database
I'm installing Northwind Database onto Local Computer. The Computer's Name is (Localhost)The Installation Menu is asking to for the [Enter Name of the (Server)] "______________________" Am I correct in assuming to Enter Localhost - for the [Enter Name of the (Sever)]. "___Localhost_____" Can someone comfirm as to whether this is correct or not... Thank You.
I have asked for the following questions and I need your advises.Utilizing the Northwind database suppied with SQL Server, create SQL tosolve each of the exercises listed.1.I want to contact all customers who have received over $1,000 indiscounts on orders this year. Give me the name and phone number of theperson to contact at the customers site. Also, list the orders wherethe total discount was greater than $100. Remember, discount is apercentage of the price.2.Give me a list of suppliers and products where we do not have thestock on hand to fill the orders to be shipped. List out the customerand order information for each of the products involved.3.Give me a list of all orders that were shipped after the requireddate for the week of Jan 7, 2001. I want to know the name of theemployees that were responsible for the orders.4.We are having a golf tournament and I need some prizes. Give me alist of the top 5 shippers by dollar amount in the last year.5.Some customers are taking us for a ride on shipping. Give me a listof customers and the orders involved where more than ½ of their ordersare being shipped to a region other than their home region.Please advise ...thanks a lot
i am getting an error ... given below and my web.config is also given below
can any one help me is my connection string right ... i am using sql server 2005 .. my system name is soft18 ..
Server Error in '/prjLogin' Application.
Cannot open database "Northwind" requested by the login. The login failed. Login failed for user 'SOFT18Administrator'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database "Northwind" requested by the login. The login failed. Login failed for user 'SOFT18Administrator'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Cannot open database "Northwind" requested by the login. The login failed. Login failed for user 'SOFT18Administrator'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +115 System.Data.SqlClient.TdsParser.ThrowExcepti.................... ........................ ................ ...................
I have a requirement. I am having a database which is having views,procedures and tables. Many users are having access to the database. If i want to track all the schema changes happening in the database how to achieve that? As in i have used some tables in a procedure and someone might change the column or drop the column. So it is going to have impacts on my procedure. I need a trigger which tracks all the changes done in a database and the impacts those changes are going to make.
I am an advanced user of MS Access and other databases but relatively new to SQL Server. I am utilizing SQL Server Express to analyze a database that was created by another entity. I am interested in looking at any specific record in any table and seeing who created that record, what date/time the record was created, who edited the record, when that edit occurred, etc. I don't see any such fields in the database. Would the designer of the database have to have explicitly added such functionality, or is this information available but just hidden from the novice's view?
I just downloaded and installed MSDE2000 sp3a for winform and asp.net quickstart tutorial. But I found it has no sample database such as pubs and northwind. It used to have these databases. At least in last September when .Net web Matrix came out.
If this is the case, can someone tell me where to get the sql scripts to create Northwind database in MSDE? Becasue the winform tutorial uses this database.
Does anyone know where I can find a Northwind end to end database solutions (examples) written in ASP.NET (VB). I would like to reverse engineer this project to learn more about ASP.NET?
I am trying to connect to SQL 7.0 northwind database via visual studio2005. But I can't open the connection. I get an error saying remoteaccess is not allowed. But the remote access is ok. I can used the samelogin and connect to the sql server via enterprise manage.SqlConnection conn = new SqlConnection("DataSource=(sqlserver:1433);Initial Catalog=Northwind;UserID=me;Password=mypassword");The above is the string I am trying to use.Is there a way to do this in Visual studio thanks
I'm actually taking Microsoft's 2779 and just finished a lab where wekept track of our changes to the database.However, I'm not happy with the scripts interface because it does nottell me the chronological order of my changes to the database.Could someone share with me their technique for keeping track ofdatabase changes?I'm actually thinking a set of tables would be best, because sometimesyou want to know what database object you made a change to and othertimes you want to know when you did something...
Hi.I'm very new to this so I apologise in advance for asking the blindibly obvious. I have installed SQL Express and SQL Server Management Studio Express and I have downloaded and attached the Northwind sample database. I can see and edit the data in the tables but when I try to open the Database Diagram node I get the following message:Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.I have no idea what a valid logon would be. Can somebody help? Thanks
i try create this example:http://www.codeproject.com/KB/webforms/ReportViewer.aspxI have Northwind database added in the SQL server management and i select Northwind databse in drop box and I push Execute!ALTER PROCEDURE ShowProductByCategory(@CategoryName nvarchar(15) )ASSELECT Categories.CategoryName, Products.ProductName, Products.UnitPrice, Products.UnitsInStockFROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryIDWHERE CategoryName=@CategoryNameRETURNbut error is:Msg 208, Level 16, State 6, Procedure ShowProductByCategory, Line 11Invalid object name 'ShowProductByCategory'.on web not so clear what is issue pls. help
I am trying to execute the following query , in Management Studio. But it takes forever. Can someone tell me why is this happening? I am running the query in 'NorthWind' database.The windows account under which I am logged into WinXP (windows authentication is enabled for the SQL Server database) is the database owner for NorthWind database. alter database NorthWind SET ENABLE_BROKER
Hi,Good morning to All. While doing practice I did some modifications to some tables in Northwind database.Now, how can I get my original tables back again? Take Oracle for example:We can modify table data. But when we run demobld.sql script then all tables will replaces with original entries. Like this I want in SQL Server also. Is there any such facility available in SQL Server? Thanks in advance,Ashok kumar.