Using SQLCMD to execute SQL scripts

Do you have a set of SQL scripts that you run all the time? Have you thought about creating a batch file to call these scripts whenever you need them to run? Here is how you would do such a thing.

First, create a folder on your computer and call it something such as scripts and then drop your scripts into the folder (Figure A).

Figure A.

Next, create a batch file that points to the script or scripts that you created (Figure B).

Figure B.

Your script or scripts can be as easy or as complex as you want. Create a set of scripts to help you in your daily life.

Let’s now go ahead and run our batch file (Figure C).

Figure C.

Another script I tend to run a lot is a reset permissions script. Figure D. shows the output.

Note: You can run a sqlcmd /? to get a listing of all your switches (Figure E).

Figure E.


Sample Command:

C:\Documents and Settings\minds>sqlcmd -SMinds8\Minds8 -Usa -P123456 -dSamy -q"s

elect a.LastName,a.Firstname,b.OrderNo,Convert(varchar(10),b.orderdate,103)Order

date,b.Orderprice From dbo.persons as a inner join orders as b on a.p_id=b.p_id"

 -h10 -Y15

LastName        Firstname       OrderNo     Orderdate  Orderprice

--------------- --------------- ----------- ---------- ---------------------

Pettersen       Kari                  77895 02/12/2009             1000.0000

Pettersen       Kari                  44678 02/12/2009             1600.0000

Hansen          Ola                   22456 02/12/2009              700.0000

Hansen          Ola                   24562 02/12/2009              300.0000

Hansen          Ola                   34764 02/12/2009             2000.0000

Nilsen          Johan                 12345 02/12/2009              100.0000

Nilsen          Johan                 12345 02/12/2010              100.0000

Nilsen          Johan                 12345 02/12/2008              100.0000

Hansen          Ola                   12345 02/12/2010              100.0000

(9 rows affected)

1> quit

C:\Documents and Settings\minds>

Export Data From SQL Server 2005 to Microsoft Excel Datasheet

Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. 

Run following code in SQL Server Management Studio – Query Editor.

USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO


Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.

Make sure your spreadsheet is closed during this operation. If it is open it may thrown an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.

How to send mail from sql server 2005

Database Mail in SQL Server 2005

The SQL Mail problems, that we faced in SQL Server 7.0 and 2000, are no more. SQL Server 2005 supports and uses SMTP email now and there is no longer a need to MAPI client to send email. In SQL Server 2005, the mail feature is called Database Mail. In this article, I am going to demonstrate step-by-step, with illustrations, how to configure Database Mail and send email from SQL Server.
Database Mail has four components.

1.     Configuration Component
Configuration component has two sub components. One is the Database Mail account, which contains information such as the SMTP server login, Email account, Login and password for SMTP mail.
The Second sub component is Database Mail Profile. Mail profile can be Public, meaning members ofDatabaseMailUserRole in MSDB database can send email. For private profile, a set of users should be defined.
2.     Messaging Component
Messaging component is basically all of the objects related to sending email stored in the MSDB database.
3.     Database Mail Executable
Database Mail uses the DatabaseMail90.exe executable to send email.
4.     Logging and Auditing component
Database Mail stores the log information on MSDB database and it can be queried using sysmail_event_log.

Step 1
Before setting up the Database Mail profile and accounts, we have to enable the Database Mail feature on the server. This can be done in two ways. The first method is to use Transact SQL to enable Database Mail. The second method is to use a GUI.
In the SQL Server Management Studio, execute the following statement.

use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure 
go

Alternatively, you could use the SQL Server Surface area configuration. Refer Fig 1.0.

Fig 1.0

Step 2
The Configuration Component Database account can be enabled by using the sysmail_add_account procedure. In this article, we are going create the account, "MyMailAccount," using mail.optonline.net as the mail server and
makclaire@optimumonline.net as the e-mail account.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'MyMailAccount',
    @description = 'Mail account for Database Mail',
    @email_address = 'makclaire@optonline.net',
    @display_name = 'MyAccount',
 @username='makclaire@optonline.net',
 @password='abc123',
    @mailserver_name = 'mail.optonline.net'

Step 3
The second sub component of the configuration requires us to create a Mail profile.
In this article, we are going to create "MyMailProfile" using the sysmail_add_profile procedure to create a Database Mail profile.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'MyMailProfile',
       @description = 'Profile used for database mail'

Step 4
Now execute the sysmail_add_profileaccount procedure, to add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MyMailProfile',
    @account_name = 'MyMailAccount',
    @sequence_number = 1

Step 5
Use the sysmail_add_principalprofile procedure to grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile.
Please execute the statement below.

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MyMailProfile',
    @principal_name = 'public',
    @is_default = 1 ;

Step 6
Now let us send a test email from SQL Server.
Please execute the statement below.

declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='mak_999@yahoo.com',
    @subject = 'My Mail Test',
    @body = @body1,
    @body_format = 'HTML' ;

You will get the message shown in Fig 1.1.

 Fig 1.1

Moreover, in a few moments you will receive the email message shown in Fig 1.2.

 Fig 1.2

You may get the error message below, if you haven't run the SQL statements from step 1.

Msg 15281, Level 16, State 1, Procedure sp_send_dbmail, Line 0
SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of
component 'Database Mail XPs' because this component is turned off as part of
the security configuration for this server. A system administrator can enable
the use of 'Database Mail XPs' by using sp_configure. For more information
about enabling 'Database Mail XPs', see "Surface Area Configuration"
in SQL Server Books Online. 

You may see this in the database mail log if port 25 is blocked. Refer Fig 1.3.

 Fig 1.3

Please make sure port 25 is not blocked by a firewall or anti virus software etc. Refer Fig 1.4.

 Fig 1.4

Step 7
You can check the configuration of the Database Mail profile and account using SQL Server Management Studio by right clicking Database Mail [Refer Fig 1.5] and clicking the Configuration. [Refer Fig 1.6]

 Fig 1.5

 Fig 1.6

Step 8
The log related to Database Mail can be viewed by executing the statement below. Refer Fig 1.7.

SELECT * FROM msdb.dbo.sysmail_event_log

 Fig 1.7

Conclusion

This article has demonstrated step-by-step instructions, with illustrations, how to configure Database Mail and send email from SQL Server.

Insert Picture into SQL Server 2005 Image Field using only SQL

USE AdventureWorks

-- export
DECLARE @SQLcommand nvarchar(4000)
SET @SQLcommand = 'bcp "select LargePhoto from AdventureWorks.Production.ProductPhoto where ProductPhotoID = 111" queryout "C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Blue hills.jpg" -T -n '

EXEC xp_cmdshell @SQLcommand

GO


-- import image
INSERT Production.ProductPhoto (
ThumbNailPhoto,
ThumbnailPhotoFileName,
LargePhoto,
LargePhotoFileName)
SELECT null, null, LargePhoto.*, N'Blue hills.jpg'
FROM OPENROWSET
(BULK 'C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Blue hills.jpg', SINGLE_BLOB) LargePhoto
GO

select * from Production.ProductPhoto order by ModifiedDate desc
GO

Enable xp_cmdshell using sp_configure

Use Master

---- To allow advanced options to be changed.

EXEC sp_configure ‘show advanced options’, 1

GO

---- To update the currently configured value for advanced options.

RECONFIGURE WITH OVERRIDE

GO

----- To enable the feature.

EXEC sp_configure ‘xp_cmdshell’, 1

GO

---- To update the currently configured value for this feature.

RECONFIGURE WITH OVERRIDE

GO

EXEC sp_configure ‘show advanced options’, 0

RECONFIGURE WITH OVERRIDE

GO

How to import/export data to Excel?

Execute the following script to export table data into an Excel worksheet and import the data back into a table:
use AdventureWorks

-- export
-- the target empty worksheet should exist with header line only

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=F:\data\test\NameAddress.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
select FirstName,
LastName,
EmailAddress,
Phone
from Person.Contact
go
-- import
select * into tempdb.dbo.NameAddress
from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=F:\data\test\NameAddress.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
go
use tempdb
select * from dbo.NameAddress
go

How to find all tables where a column occurs?

Execute the following script in Query Editor to find all occurances of the "addressid" column:

use AdventureWorks;
select
[Schema]=s.name,
[Table]=o.name,
[Column]=c.name
from sys.columns c
join sys.objects o
on c.object_id=o.object_id
join sys.schemas s
on s.schema_id=o.schema_id
where c.name='addressid'
order by [Table]