SQL Server Performance

Retrieving row data

Discussion in 'General Developer Questions' started by trifunk, Jun 2, 2003.

  1. trifunk New Member

    Hi all,

    Anyone aware of another method to retrieve an entire row's data into a single field without using the FOR XML clause?

    We have an audit log that we log to for certain applications. Usually we just log the user, application and action taken against the data.
    Now we need to save a copy of the data for certain actions so that sensitive information that has been edited can be audited and admin can view the changes made to a record, is there any easy generic way to select an entire row as a single field for storing?
    I'm not looking to concatenate values from a row but some sexy method for pulling the data out of the necessary table (a row) for each audit in one go and storing it as a single field.

    Is this possible or am I trying my luck?

    Cheers
    Shaun

    World Domination Through Superior Software
  2. satya Moderator

    Try to use BCP with -c -r -t parameters, take help of BOL.

    _________
    Satya SKJ
  3. rushmada New Member

    I hope this helps u

    drop table #Output
    go
    Create Table #Output (
    OutputLine varchar(600) null
    )
    set nocount on
    Insert #Output ( OutputLine )
    exec master..xp_cmdshell 'osql -n -SServername -Usa -Ppassword -d northwind /q "select * from emp
    "'

    Select *
    From #Output
    GO

    Note: Here u have to give server name and password in the osql utility.

    Thanks.



    Rushendra
  4. vbkenya New Member

    I modified Rushi's code to use Windows Authentication, remove the column headers, insert a column separator and improve the formatting like this:

    Insert #Output (OutputLine)
    exec master..xp_cmdshell 'osql -n -SServerName -E -d Northwind -s, -h-1 -w300 /q "select * from employees"'


    But I must admit that it has very poor performance.

    Nathan H.O.

Share This Page