Solved

Export data from MS-SQL 8.0 to text without using bcp or xp_cmdshell

Posted on 2004-08-30
4
229 Views
Last Modified: 2008-01-09
How ?
I did succeed to import with bulk insert from ...
but I need to process that data and do the export by rewriting the text file as well.

Thanks.
Gregor
0
Comment
Question by:gregajesih
  • 2
  • 2
4 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11930890
You can always use DTS and the sp_OA stored procedures to call it...
0
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 11930915
(there is an example of SP_OA here--scroll down past the xp_cmdshell example http://www.databasejournal.com/features/mssql/article.php/1459181)

Brett
0
 

Author Comment

by:gregajesih
ID: 11939292
It seems there is nothing better (more elegant and simpler) then I accept the answer.
Thank you.
0
 

Author Comment

by:gregajesih
ID: 11942415
Well, I tried the approach and it seems that something is still not working.
I used this code with query analyzer being connected to the server and database where the source data is present and DTS package is stored in the same server under "ThisServer-DTS-Local packages".

MS-SQL Code:

-- create package
declare @hr int
declare @oPKG int
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr <> 0
BEGIN
    PRINT '***  Create Package object failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

--Loading the Package:
-- DTSSQLServerStorageFlags :
--- DTSSQLStgFlag_Default = 0
--- DTSSQLStgFlag_UseTrustedConnection = 256
--EXEC @hr = sp_OAMethod @oPKG,
--  'LoadFromSQLServer("localhost","","", 256, , , , "ExportBsnprof",)',
--  NULL
EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSqlServer', NULL,
  @ServerName='10.100.10.30\MPSCENTER', @ServerUsername='usernametext', @ServerPassword='passwordtext',@PackageName='ExportBsnprof', @Flags=256

IF @hr <> 0
BEGIN
    PRINT '***  Load Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

--Executing the Package:
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
    PRINT '***  Execute failed'
    EXEC sp_displayoaerrorinfo @oPKG , @hr
    RETURN
END
else
    print 'Executed!'

--Cleaning up:
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
    PRINT '***  Destroy Package failed'
    EXEC sp_displayoaerrorinfo @oPKG, @hr
    RETURN
END

..and here is the result grid text:

/*-----------------------------

-- create package
declare @hr int
-----------------------------*/
Executed!
 
What's wrong ?
Thanks.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now