Modifying SQL Query

  • We are using SQL server based Application.  I needed to download some data from this application.  The data administrtor send us following SQL with advice to run the SQL and open the output in GRID then use option select ALL and then copy and paste data into the program I want to use. i.e. Excel.

    ____________________________________________

    SELECT      dbo.CustomerID.CustomerName, dbo.QRYBRANCH.BranchName,

                          dbo.mstActivity.Description AS Activity, SUM(dbo.AdvAcDetail.FacilityExposure) AS balance

    FROM         dbo.mstActivity INNER JOIN

                          dbo.AdvAcDetail INNER JOIN

                          dbo.QRYBRANCH ON dbo.AdvAcDetail.BranchCode = dbo.QRYBRANCH.BranchCode ON dbo.mstActivity.Code = dbo.AdvAcDetail.Activity INNER JOIN

                          dbo.CustomerID ON dbo.AdvAcDetail.BranchCode = dbo.CustomerID.BranchCode AND

                          dbo.AdvAcDetail.CustomerID = dbo.CustomerID.CustomerID

    WHERE     (dbo.AdvAcDetail.DelSta IS NULL OR

                          dbo.AdvAcDetail.DelSta = N'') AND (dbo.CustomerID.DelSta IS NULL OR

                          dbo.CustomerID.DelSta = N'') AND (dbo.AdvAcDetail.RegionCode = N'23')

    GROUP BY dbo.CustomerID.CustomerName, dbo.QRYBRANCH.BranchName, dbo.mstActivity.Description

    ORDER BY dbo.QRYBRANCH.BranchName, dbo.CustomerID.CustomerName

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

    Now My question is can We modify this SQL in such manner that the out put is generated in a delimited text file with "|" as text delimiter?  I am sure I am askingvery elementry question.   But I am totally unfamiliar with SQL server.

  • In Query Analyzer, from the Tools menu, select Options, then on the Results tab, change your options to these:

    Default results target: - Results to Text

    Results output format: (*) - Custom Delimiter

    Delimiter: (*) - |

    This should do the trick for you.

    Jarret

  • You also might want to have your DBA set you up a DTS (Data transfromation services) script that you can run independently with DTSRUN.exe tool and load the data directly into excel instead of having to muck with the munging of the delimiters each time and of course excels data import wizard  too.

    Peter Evans (__PETER Peter_)

  • Would someone who wasn't a sysadmin be able to run a DTS script using DTSRUN.exe?

  • Yes! The User does need a login with the SQL Server instance that has the permissions and roles to perform the steps and also the ability to view the Package Repository.  Now, I have seen some METADATA repositories that restrict acess to admins for security reasons.  But that is not the case in general. 

    For the typical data export tasks to Access, Excel, Text, or HTML this can be achieved with the right user expectaions and permissions.  However, the one caveat to this is that it can be a pain in the admin's rear to make sure every client PC has the correct version of the MDAC stack to avoid configuration errors.  

    Also, I keep forgetting to mention that there is also the DTSRUNUI.exe tool that is more "report user" friendly because it provides a basic dialog based user interface.

    Peter Evans (__PETER Peter_)

Viewing 5 posts - 1 through 4 (of 4 total)

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