Exercise 2: Manipulate Data and Stored Procedure

SQL Server 2000

Exercise 2: Manipulate Data and Stored Procedure

-----Week 5-----

Please follow those steps to practise:

1.  Use bcp to export all data from Orders table of PracticeDB to c:\Orders.txt (or to c:\Orders.csv)

2.  Change some data in the c:\Orders.txt and save. Then import to Orders table from the text file using bcp

3.  Import Orders.txt to Orders table using BULK INSERT

4.  Create a Linked Server ‘LinkedPracticeDB’ which link to an Access database ‘PracticeDB.mdb’ (firstly you have to create an Access database similar to PracticeDB in SQL Server and input some data). Then do a select data using four-part name and OPENQUERY

5.  Using ad hoc computer name with OPENROWSET and OPENDATASOURCE functions to select data from ‘PracticeDB.mdb’

6.  Create the following Cursor

DECLARE @au_lname varchar(40), @au_fname varchar(20)

DECLARE Employee_Cursor CURSOR FOR

SELECT LastName, FirstName FROM Northwind.dbo.Employees

OPEN Employee_Cursor

FETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fname

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT 'Author:' + @au_fname + ' ' + @au_lname

FETCH NEXT FROM Employee_Cursor INTO @au_lname, @au_fname

END

CLOSE Employee_Cursor

DEALLOCATE Employee_Cursor

7.  Create the following stored procedure and try to execute with some values

CREATE PROCEDURE AddNewOrder

@OrderID smallint,

@ProductName varchar(50),

@CustomerName varchar(50),

@Result smallint=1 Output

AS

DECLARE @CustomerID smallint

BEGIN TRANSACTION

If not Exists(SELECT CustomerID FROM Customers WHERE [Name]=@CustomerName)

BEGIN

SET @CustomerID= (SELECT Max(CustomerID) FROM Customers)

SET @CustomerID=@CustomerID+1

INSERT INTO Customers VALUES(@CustomerID,@CustomerName)

If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID)

BEGIN

SELECT @Result=1

ROLLBACK TRANSACTION

END

Else

BEGIN

INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID)

SELECT @Result=0

COMMIT TRANSACTION

END

END

Else

BEGIN

If Exists(SELECT OrderID FROM [Orders] WHERE OrderID=@OrderID)

BEGIN

SELECT @Result=1

ROLLBACK TRANSACTION

END

Else

BEGIN

INSERT INTO [Orders](OrderID,ProductName,CustomerID) VALUES(@OrderID,@ProductName,@CustomerID)

SELECT @Result=0

COMMIT TRANSACTION

END

END

Print @Result

Return

9. Using VB 6 or VB.NET to execute the ‘AddNewOrder’ stored procedure

10. Using xp_cmdshell extended stored procedure to send a message (xp_cmdshell ‘net send Hello’)