Modifying SQL Query

Question

Post reply

Modifying SQL Query

Umang Bhatt

Newbie

Points: 7
More actions
March 13, 2004 at 8:59 am

#107786

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.

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

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

jarretg SSCommitted Points: 1706 More actions · Answer 1

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

Peter Evans Ten Centuries Points: 1187 More actions · Answer 2

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_)

SueB SSCommitted Points: 1668 More actions · Answer 3

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

Peter Evans Ten Centuries Points: 1187 More actions · Answer 4

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_)