October 8, 2010 at 1:21 pm
I have only recently begun my quest to automate Excel via my T-SQL. Although I am on a Network SQL Server, I have SQLExpress installed so I may test locally. I do not have admin rights on my Network SQL Server -is there a way for me to Set my privileges on my SQLExpress edition? I have tried all that I have read -and still cannot get sp_OACreate, sp_OAMethod, etc to work. There are a number of hits out there with detailed instruction on using the sp_OA utilities... I cannot get them to work locally.
Help!!!
October 8, 2010 at 2:07 pm
Why would you use sp_OACreate, et al, on SQL 2005? Build a .NET object, and call it via the whole CLR Stored Procedure functionality. It'll work better, and it cleans up after itself MUCH better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 8, 2010 at 2:24 pm
I'm pasting a tried and true snippet of code that uses sp_OaCreate.
All it is doing is witing to files in the root of the harddrive: c:\header.txt + c:\body.txt c:\results.txt'
etc.
this can help you resolve things like whetherh sp_oacreate is enabled on your instance, and whether the login that SQL runs under has permissions to the folders in question(the root of C in my example)
CREATE function dbo.Ufn_WriteToFile
(
@FileName varchar(1000), @Text1 varchar(1000)
)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @status VARCHAR(100), @eof VARCHAR(10)
SET @status = 'SUCCESS'
DECLARE @FS int, @OLEResult int, @FileID int
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0
SET @status= 'Error: Scripting.FileSystemObject'
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 8, 1 -- Append if required (8)
--execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@FileName, 1
IF @OLEResult <>0
SET @status ='Error: OpenTextFile'
--Write Text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0
SET @status= 'Error : WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
RETURN @status
END
GO
--table to capture xp_cmdshell output
CREATE TABLE #results(resultstext varchar(1000) )
declare @sql varchar(4000),
@rowcount int
--sample query: you would do the same to your existing bcp
--note i erased the password for the -P flag...make sure to fix
set @sql = 'bcp "SELECT TOP 5 * FROM SYSOBJECTS" queryout "c:\body.txt" -c -U"sa" -P""'
--export via bcp
insert into #results
EXEC master..xp_cmdshell @sql
SET @rowcount=@@rowcount --used only as example that that is NOT the rows you wanted to count
print @rowcount -- this is the rows from xp_cmdshells work, NOT the bcp!!!!
--you have to get it from the output of xp_cmdshell
--select * from #results where resultstext like '%rows copied.'
select @rowcount = convert(int,substring(resultstext,1,charindex(' ',resultstext) ) ) from #results where resultstext like '%rows copied.'
--header preamble
SET @sql = 'HEADER1:53910072007000000000086456'
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql)
--header date
SET @sql = 'Date: ' + convert(varchar,getdate(),101)
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql )
--header time
SET @sql ='Time: ' + convert(varchar,getdate(),108)
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql )
--header rowcount
SET @sql ='Total rows: ' + convert(varchar,@rowcount)
SELECT DBO.Ufn_WriteToFile('c:\header.txt',@sql)
--combine the header and query together into a final file.
set @sql ='copy c:\header.txt + c:\body.txt c:\results.txt'
EXEC master..xp_cmdshell @sql
drop table #results
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply