Bài tập 2 SQL Server 2000
Manipulate Data and Stored Procedure Please follow those steps to practise: Use bcp to export all data from Orders table of PracticeDB to c:Orders.txt (or to c:Orders.csv) Change some data in the c:Orders.txt and save. Then import to ...
Manipulate Data and Stored Procedure
Please follow those steps to practise:
- Use bcp to export all data from Orders table of PracticeDB to c:Orders.txt (or to c:Orders.csv)
- Change some data in the c:Orders.txt and save. Then import to Orders table from the text file using bcp
- Import Orders.txt to Orders table using BULK INSERT
- 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
- Using ad hoc computer name with OPENROWSET and OPENDATASOURCE functions to select data from ‘PracticeDB.mdb’
- 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
- 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’)
