Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
|
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 22nd, 2005, 03:37 PM
Authorized User
 
Join Date: Nov 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dumping StoredProc output to text File

I have a problem getting SP output into the flat file. The output is not uniform. There are a lot of Print statements and differently formatted sets of results (multiple SELECT-s)

What would be the quickest way to load that into TXT file?

I tried thru ActiveX

- Code makes Connection & RecordSet (Works)
- Creates and Opens file (Works)
- But getting data from the RecordSet into the file doesn’t work.



 
Old December 22nd, 2005, 08:22 PM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

You can use osql.exe to execute a query and store the results in a file.
Eg
osql -E -dTempDB -h-1 -w100 -n -i "C:\SQL_BagOTricks\ExportXML.sql" -o "C:\SQL_BagOTricks\ExportXML.XML"

OR
osql -E -dTempDB -h-1 -w100 -n -Q "SELECT CustomerID FROM Customer" -o "C:\SQL_BagOTricks\ExportXML.XML"

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
 
Old December 23rd, 2005, 01:05 PM
Authorized User
 
Join Date: Nov 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks David,

That seems as an excellent idea - I see the point.

How would you put that in a package? I need to schedule it.


 
Old December 23rd, 2005, 05:00 PM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

You could put it in a SQL Server Agent job step of type cmdExec or if it needs to be in a package put it in an Execute Process task

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
 
Old December 29th, 2005, 11:13 AM
Authorized User
 
Join Date: Nov 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks David!

 
Old January 10th, 2006, 11:23 AM
Authorized User
 
Join Date: Nov 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi David and others,

Osql.exe works like a charm in package using exec process task. Thanks a lot. This is a syntax I’m using:

Win32
Osql.exe

Param:
-E -S "MYSQLSERVER" -dMyDB -h0 -w220 -n -i "\\fileserver\GroupDir\Dirs\MyStoredProcCall.s ql" -o "\\fileserver\GroupDir\Dirs\Rpt.txt"

Now I have different issue!

Package runs with no problems. It consist of 2 steps:
- osql.exe command producing file
- ActiveX script changing name of the file

Problem comes when I try to schedule package - it fails with error: ‘No such a file or directory’.

As it may seem straight forward - it is not.

- It shouldn’t be path issue since I am using UNC path identification: eg. \\server\dir\somedir\myfile.sql

- Shouldn’t be either server permission since I’m running other scheduled jobs from the same location.

I created that scheduled job by right clicking package --> Schedule package

Any thoughts would be very appreciated?

Thanks


 
Old January 10th, 2006, 12:31 PM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

It is probably an issue with the permissions assigned to the SQL Server Agent Service Account. When you look at the SQL Server Agent Service I'll wager that it is running as LocalSystem or LocalBox\LocalUser instead of domain\DomainUser. If it is not a domain user it can not even be assigned permissions to view files across the network. Localsystem won't even try. A LocalUser tries to login as a user of the same name and password on the remote box, if such a user exists and has the same password then it can check permissions.

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com
 
Old January 10th, 2006, 12:48 PM
Authorized User
 
Join Date: Nov 2005
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks David,

Problem solved and you were bang-on again; it was permission issue.

Although appropriate permissions have been assign to specific directory – subdirectories that should have carried permission rights from that parent directory are not there.

It runs fine in the main directory with clearly assigned permissions to SQL server proxy a/c.

Again - thanks a lot!


 
Old January 12th, 2006, 04:41 PM
Friend of Wrox
 
Join Date: Dec 2005
Posts: 146
Thanks: 0
Thanked 1 Time in 1 Post
Default

Glad I could be of service

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Trailing blanks for 'text' output Neal XSLT 5 May 29th, 2008 06:25 AM
TEXT OUTPUT IN WINDOWS suman_16 XSLT 1 January 24th, 2008 06:09 AM
Output to text bonekrusher XSLT 4 November 25th, 2007 01:06 PM
text output problems dextermagnific XSLT 1 August 10th, 2006 12:31 PM
retrieve varbinary using StoredProc with OUTPUT ag19702004 ADO.NET 0 November 30th, 2005 01:37 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.