A question about export

[pzhou]

Hi sql server experts,
I'd like to get some help on this.
I need to export a table to a .csv file and email the file to somebody. This should happen once a month automatically.
How do I do this? Should I do this with T-SQL?
Is there a package or tool?
I tried DTS but there is no way to email.
Your help is appreciated.

Ping

 

[nomadicalloy]

Please look at the following link

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/DTS/Q_22547822.html

This is Signature

http://www.xpherion.com/

 

[mrdenny]

BCP is the recommended tool to use for exporting data. Then you can use xp_sendmail to email the file.

You can also to this with DTS. It has an email object that can be added to the package and used to send the file.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLSister]

Or you could write an ActiveX task to email.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()

	dim oMail
	set oMail = CreateObject("CDO.Message")
	dim oMailConf
	set oMailConf = CreateObject("CDO.Configuration")
	
	Dim Flds
	Set Flds = oMailConf.Fields
	
	' Use string constants for field names.
	Flds("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserver")[/URL]	= "testmail3.test.local"
	Flds("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/smtpserverport")[/URL]	= 25
	Flds("[URL unfurl="true"]http://schemas.microsoft.com/cdo/configuration/sendusing")[/URL]	    	= 2
	Flds.Update
	
	Set oMail.Configuration = oMailConf
	
	Set Flds = oMail.Fields
	With Flds
		.Item("urn:schemas:mailheader:to") = "test@test.com;test2@testdirect.com"
		.Item("urn:schemas:mailheader:from") = "test3@testdirect.com"
		.Item("urn:schemas:mailheader:subject") = "(DTS-Automated) Pre_process failed for client X"
		.Update
	End With
	
	oMail.TextBody = "What did they do to the file this time?"
	oMail.Send


Main = DTSTaskExecResult_Failure

End Function

Of course you'll need to fill in the real email addreses, your smt server name and port and what you want the subject and body of the message to say. But this should get you started. (And no this isnot the real message I send!)
If you are sending the messages to let you know about the status of the import, I highly recommend you use some specific words in all your packages that will enable you to set up rules for handling them in Outlook. I have mine set up so that all the automated messages are filed to a specific folder unless they contain the word fail. then they go to my inbox. Helps me weed out the messages I might need later as proof that something happened from the ones I need to act on now.

Questions about posting. See faq183-874

 

[mrdenny]

Do be careful relying on ActiveX in DTS packages. SSIS doesn't support Active X after SQL 2000.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[pzhou]

Thanks everybody for the responses.
when I used DTS designer, I got the following error when I use the Sendmail task.

Unable to get MAPI profile

So I'm going to try to use BCP to do it.

For the ActiveX script, I will save it for the last.

Cheers,


--Ping

 

[mrdenny]

When you are designing the DTS package are you designing it on your workstation or on your server?

If on your workstation what email program is installed and configured?

If on your server were you logged in with the same accout which the SQL Server logs in with? Is Outlook installed on the server, and configured for SQL Server to use?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[pzhou]

yes. I logged in using the same account as SQL Server.
Outlook is not installed on the SQL Server machine.

--Ping

 

[mrdenny]

In order to use the DTS sendmail object you have to have Outlook installed and configured.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[pzhou]

Thanks for your response.
I will use BCP with some other email utility.

--Ping