Post reply

output to text file

  • July 8, 2005 at 9:22 am

    #88099

    quick (and propably) easy question :

    how do I export the output of a stored proc into a text file? I need the output of sp_help_revlogin to be saved in a text file on a different server to be executed later on a backup server.

    (DTS transfer login does not work...)

    thanks,

    Erik

  • July 8, 2005 at 9:26 am

    #572424

    You can create a text file as linked server. Then I assume you can just do insert into linkedFileName[.?.?] (col1, col2) exec myproc.

    I know you can insert into... but I'm not 100% certain it'll work with an exec statement. At worst you'll have to use some sort of staging table.

  • July 8, 2005 at 9:30 am

    #572430

    Yup, for instance, if the linked server is MyTextFile and the text file is MyLogFile.txt you'd do something like:

    INSERT INTO MyTextFile...[MyLogFile#txt]
(Col1, Col2)
EXEC dbo.MyProc

    Do note the period becomes a # sign. However, there is the setup of the schema.ini file, the creation of the text file itself, and the linked server connection that all must be done.

    K. Brian Kelley
    @kbriankelley

  • July 8, 2005 at 9:44 am

    #572449

    can't you just use query analyser and use the output to file function ?

    MVDBA

  • July 8, 2005 at 9:57 am

    #572464

    Yes... but I want to make this task automatic. If my server fails, I need to rebuild the databases with the logins in order to make my software work again (windows logins). And I want all this to be automated! So everynight, a DTS will backup the logins in a text file to be ready to be executed on the standby server.

    Erik

  • July 8, 2005 at 10:08 am

    #572473

    i've seen it done this way as the text of a job in SQL agent

    xp_cmdshell 'isqlw.exe -S myserver -d master -U sa -P password -i c:\script.sql -o results.csv'

    although surely doinc a bcp of your master.sysxlogins table would be easier

    MVDBA

  • July 8, 2005 at 10:17 am

    #572485

    Does anyone have an idea why I can't DTS my logins? always fails with unspecified error!?!

  • July 8, 2005 at 10:19 am

    #572486

    Have you turned on the logging to file of the steps? Maybe the error could be more relevant in there.

  • July 8, 2005 at 10:23 am

    #572490

    sorry remi... I'm not I understand your sentence...

  • July 8, 2005 at 10:26 am

    #572495

    i think he means profiler trace

    MVDBA

  • July 8, 2005 at 10:27 am

    #572500

    Let's try it in english this time .

    Open the package, File/properties/3rd tab (logging or saving), then file out the err handling section. This may give you some more pointers.

  • July 8, 2005 at 10:27 am

    #572499

    No, but that could work too .

  • July 8, 2005 at 11:18 am

    #572534

    Remi... I only understand french and english... not some language you just made up

  • July 8, 2005 at 11:20 am

    #572535

    Anyways, try it and see what comes up.

  • July 8, 2005 at 11:31 am

    #572541

    Here's the output... I'm not sure how to find a meaningful error in this file...

    Thanks for your help... it's appreciated!

    Erik

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

    The execution of the following DTS Package succeeded:

    Package Name: lgoin

    Package Description: (null)

    Package ID: {1AA6F07F-6B92-44C2-82F3-4F760783E895}

    Package Version: {1D1CBA18-3103-447A-8ADF-03A854602585}

    Package Execution Lineage: {38C87143-96B9-4F19-A13C-7D2E985CA032}

    Executed On: ERIK

    Executed By: e-lali

    Execution Started: 2005-07-08 13:11:38

    Execution Completed: 2005-07-08 13:11:47

    Total Execution Time: 9,534 seconds

    Package Steps execution information:

    Step 'DTSStep_OMWCustomTasks.OMWTransferLogins_1' failed

    Step Error Source: Microsoft Data Transformation Services (DTS) Package

    Step Error Description:Erreur non spécifiée

    Step Error code: 80004005

    Step Error Help File:sqldts80.hlp

    Step Error Help Context ID:700

    Step Execution Started: 2005-07-08 13:11:38

    Step Execution Completed: 2005-07-08 13:11:47

    Total Step Execution Time: 9,534 seconds

    Progress count in Step: 0

    Task Log for this step:

    -----Start Task Log-----

    Error = 1 (00000001), Description = Script    Login <*LOGIN NAME*>

    (a line for each login)

    Error = 0 (00000000), Description = Transfer    Login <*LOGIN NAME*>

    and some

    Error = 1 (00000001), Description = Transfer    Login <*LOGIN NAME*>

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply