Creating Text file using FileSystemObject - and attaching it to email 1

[Deadline]

With Due apologies for Tlbroadbent and other members for not providing adequate information in an earlier post...

-----------------
SQL 2000
Windows 2000
Visual Studio 6 SP5.
-----------------

The idea is to send an email of the contents of a table. No, VB executable is not permitted. And No again. Exchange server may not be properly configured in the clients place. Their exchange server has limited access. Dirty situation, yeah.

I managed to write an SP(found in MSDN, "Sending mail without SQL Mail&quot to send email with the query results. Here is the catch. If the query yields more than 8000 characters, it would truncate. I thought I would create a text file containing the result of the query and send it as an attachement.

So I'm trying to use the following code to create a TextFile. But it won't work. Could you please tell me where the mistake is ?

Or if there is any other way to do this, please tell me. Much Thanks.

CREATE PROCEDURE [dbo].[sp_CreateTextFile] 
@FileName varchar(100),
@FileContent varchar(8000)

AS
Declare @FSO int
Declare @TextFile int
Declare @hr int


EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
EXEC @hr = sp_OAMethod @FSO, 'CreateTextFile', @FileName,NULL,  @TextFile OUT
EXEC @hr = sp_OAMethod @TextFile, 'WriteLine', @fileContent, NULL
EXEC @hr = sp_OADestroy @FSO
GO

Thank you,
RR.
__________________________________
The best is yet to come.

 

[tlbroadbent]

You have some syntax errors in the method calls. Here is the corrected code including error handling.

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

CREATE PROCEDURE [dbo].[sp_CreateTextFile]
@FileName varchar(100),
@FileContent varchar(8000)

AS

Declare @FSO int
Declare @TextFile int
Declare @writefile int
Declare @hr int

-- variables for error handling
DECLARE @source varchar(255)
DECLARE @description varchar(255)

-- Create file scripting object
EXEC @hr =
sp_OACreate 'Scripting.FileSystemObject',
@FSO OUT

-- Display return code and value
Select HR=@hr, FSOVal=@FSO

-- You should probably add some error
-- handling here also

-- Create the file
EXEC @hr =
sp_OAMethod @FSO, 'CreateTextFile',
@TextFile OUT, @FileName

-- Display return code and return value
SELECT
HR=@hr,
TextFileVal=@textfile

-- Check for error
If @hr<>0
BEGIN
-- Error occurred, find the description
EXEC @hr =
sp_OAGetErrorInfo @fso,
@source OUT, @description OUT
IF @hr = 0
BEGIN
--Display error source and description
SELECT
ErrorSource = left(@source, 60),
ErrorDesc = @description
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
END
RETURN
END
ELSE
BEGIN
EXEC @hr =
sp_OAMethod @TextFile,
'WriteLine', null, @FileContent

--Display return code and return value
Select HR=@hr, WriteFileVal=@writefile
If @hr<>0
BEGIN
PRINT 'OLE Automation Error Information'
EXEC @hr =
sp_OAGetErrorInfo @textfile,
@source OUT, @description OUT
IF @hr = 0
BEGIN
--Display error source and description
SELECT
ErrorSource = Left(@source,60),
ErrorDesc = @description
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
END
RETURN
END
END

EXEC @hr = sp_OADestroy @FSO
Select HR=@hr

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

There are other ways to output to a text file. See the SQL FAQ about writing to a text file.

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14379

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[Deadline]

Terry,

Too bad, Tek-tips allows me to give only one star for a Five Star Star like you. Much Much Thanks. I'm going to refine your code before implementing it. Thank you,
RR.
__________________________________
The best is yet to come.

 

[KSiva]

Hello there!

I have written a python script (a client GUI tool to access the SQL server database). When client uploads a file, it need to be created in server side. I used the above code:

CREATE PROCEDURE [dbo].[sp_CreateTextFile]
@FileName varchar(100),
@FileContent varchar(8000)

AS
Declare @FSO int
Declare @TextFile int
Declare @hr int

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
EXEC @hr = sp_OAMethod @FSO, 'CreateTextFile', @FileName,NULL, @TextFile OUT
EXEC @hr = sp_OAMethod @TextFile, 'WriteLine', @fileContent, NULL
EXEC @hr = sp_OADestroy @FSO
GO

and it works fine, but creates the file in the client side. I am passsing the filename to be created and content of the file. Is that what supposed to be happen? I have been reading the filesystemObject doc and it reads that the file must be created in server side.

I am bit lost here ... any of you can help me???

Thanks a lot! Sivakumar Kandaraj
System Administrator,Web Programmer
Melbourne
Australia.

 

[tlbroadbent]

Sivakumar,

You copied the original code that Deadline posted. That code has several syntax errors. It will not work. It can't be creating a file on the client side. The code runs on the server. Use the code that I posted. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[KSiva]

Woops,

Sorry mate, I have actually used yours. The code that posted earlier doesn't make any file at all. And i used your... works brilliantly... but just not sure to create the file in server which runs sql-server.

Sorry for the wrong info:: : Sivakumar Kandaraj
System Administrator,Web Programmer
Melbourne
Australia.

 

[tlbroadbent]

I'm unclear what you mean by &quot;not sure to create the file in server.&quot; Does the code create a file on the local PC or on the server? Because the code runs in SQL Server, it should create the files on the server that runs the SQL Server service. Are you connecting to SQL Server or to a local copy of SQL Server or MSDE running on your client PC? Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[KSiva]

Sorry again mate!

Your code works fine but it create the file in client PC.

The client tool which I wrote in python accessing sql server as database and at some point they need to create a file in server.

The client then passes the filename to create (filepath) and the contents to sql server stored procedure. (I used your code here) but weirdly it creates the file in client side. I am hoping that I make sense here.

Thanks for your email support!
Sivakumar Kandaraj
System Administrator,Web Programmer
Melbourne
Australia.

 

[tlbroadbent]

I'm befuddled. I don't know how the T-SQL code could create a file on the client if executed on a server. Perhaps someone else has a clue about what is happening. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[KSiva]

Exactly,

I have been reading thru FileSystemObject document and all it says that it should create the file on server side. It will not create on client side for the security reason!

Well... I am kinda givin it up! Sivakumar Kandaraj
System Administrator,Web Programmer
Melbourne
Australia.

 

[sguslan]

My apoligies for a supid posting, but i am trying to learn:

Terry, if the filename was passed to the procedure in a UNC format and it happens to point to a path on the client machine with proper authority/privileges by the SQL server user, would'nt that work and create the file on the client?