2 osql questions:

[entrekken]

1.
I am running the following osql command from within a stored procedure (I took out all sensitive info ). I get the following error message: '[ODBC Driver Manager] Data source name not found and no default driver
specified' I checked my ODBC connection in the ODBC connection manager and it connects just fine. What else could be causing this?

2.
When I run just the command (the @cmd variable from below) from the command prompt, the output file spools fine. However, the data isn't aligned properly like it would if I was trying to save to file (txt or rpt) from Query Analyzer. I have tried saving to txt and rpt and both return the same results. Is there any way to improve this?


I would like to do the osql command within a stored procedure b/c the query within the input.sql needs to run daily. Any advice is welcome! Thanks



DECLARE @BaseLocation VARCHAR(80)
DECLARE @BaseFileName VARCHAR(80)
DECLARE @cmd VARCHAR(200)
DECLARE @status INT
DECLARE @date VARCHAR(8)

BEGIN
SET @date = convert(char(8),getdate(),112)
WAITFOR DELAY '00:00:1'

SET @cmd = 'isql -S<servername> -D<database_name> -U<username> -P<pwd> -iC:\input.sql -oC:\output.txt'
exec master..xp_cmdshell @cmd
END

 

[entrekken]

One Correction/Clarification: I noticed in the code I sent that the command reads isql. It should be osql. I tried both commands trying to see which one would be better and osql won!

 

[entrekken]

I figured some stuff out and thought I'd post it for the community:

To resolve issue #1: I switched to using an isql command stored in a bat file. I call the bat file in a DTS package using the execute process task. I create a stored procedure to run the DTS package. Then I schedule the DTS stored procedure as a job.

To resolve issue #2: This is a really silly one but I think some people might still find it useful. In order to separate my datasets present in the same output file, I issued a print statement which printed a row of astericks (PRINT '*************...' This row was too long and then caused the entire file to misalign. GO figure