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.

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

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.

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

Thanks David!

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

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

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!

Glad I could be of service

David Lundell
Principal Consultant and Trainer
www.mutuallybeneficial.com