/************************************
Chapter 15 Scripts
For MCITP SQL Server 2005 Database Developer All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.

Be aware that some of the scripts fail on purpose. For example, the book may be showing how a script works, and then after a constraint is added, the book may show how the same script fails.  The failure is not an error. In other words, just running the scripts without the benefit of the book won't be much help.

*************************************/

Dtexec /sql ssisexport /decrypt

/************************************/

Dtexec /sql ssisexport /decrypt

/************************************/

bcp AdventureWorks.Sales.Currency out Currency.dat -T -c

/************************************/

Bcp AdventureWorks.Production.Product out products.txt -T -c

/************************************/

Notepad products.txt

/************************************/

Bcp AdventureWorks.Production.Product out products.txt -T -c -t;

/************************************/

Notepad products.txt

/************************************/

bcp “SELECT Title, FirstName, LastName, EmailAddress FROM AdventureWorks.Person.Contact” queryout emaillist.txt -T -c

/************************************/

Notepad emaillist.txt

/************************************/

bcp “SELECT Title, FirstName, LastName, EmailAddress FROM AdventureWorks.Person.Contact” queryout emaillist.txt -T -c -t,

/************************************/

Notepad emaillist.txt

/************************************/

bcp “SELECT Title, FirstName, LastName, EmailAddress FROM AdventureWorks.Person.Contact ORDER BY LastName” queryout emaillist.txt -T -c -t,

/************************************/

Notepad emaillist.txt

/************************************/

USE Master;
GO
CREATE Database bcpImport

/************************************/

USE bcpImport;
GO
CREATE TABLE dbo.emailList
(
      Title nvarchar(6) NULL, 
      FirstName nvarchar(50)  NULL, 
      LastName nvarchar(50)  NULL, 
      email nvarchar(50)  NULL, 
);

/************************************/

Bcp bcpImport.dbo.emailList in emaillist.txt -T -c -t,

/************************************/

USE bcpImport;
GO
SELECT * FROM emailList

/************************************/

CREATE TABLE dbo.BulkInsert
(
      Title nvarchar(6) NULL, 
      FirstName nvarchar(50)  NULL, 
      LastName nvarchar(50)  NULL, 
      email nvarchar(50)  NULL, 
);

/************************************/

BULK INSERT bcpImport.dbo.BulkInsert
 FROM 'c:\emaillist.txt'
   WITH 
     ( FIELDTERMINATOR = ',',
       ROWTERMINATOR = '\n' )

/************************************/

SELECT *
   FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\data\Sales.mdb';
      'admin';'',Customers)
GO

/************************************/

SELECT * FROM 
   OPENDATASOURCE('SQLNCLI',
    'Data Source=MCITP1\MyNamedInstance;Integrated Security=SSPI')
    .AdventureWorks.Person.Contact

/************************************/