server hangs on osql called from stored Procedure

Question

server hangs on osql called from stored Procedure

paul.vanhouse

SSC Enthusiast

Points: 151
More actions
April 13, 2011 at 2:52 pm

#150142

This series of commands worked on another clients SQL '05 installation but at this site the output file is created then SQL seems to just hang up and the rest of the stored procedure never runs. Is there a setting somewhere that allows osql to run against a ## temp table?
set @cmd =
'osql -E -Spkcusesbwss8sql -w1000"," /Q "SET NOCOUNT ON Select * from ##ErrD" -o "\\pkcusesbwss8utl\SL8Data\pkc_FixedAssetTaxSchedules\ErrD.txt"'
exec master..xp_cmdshell @Cmd
I have tried the same query but instead of temp table ##ErrD I choose a "real" table "pk_dev.dbo.item" and the osql works.
So following that logic I created a "real" table in which to store my results and run osql against it but the command hangs.

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

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

paul.vanhouse SSC Enthusiast Points: 151 More actions · Answer 1

I just disovered that if I dump the contents of my ##temp table into a real table the osql command still hangs when run from the stored procedure, but the osql command runs and finishes with no issues when run from the DOS command prompt:

osql -E -Spkcusesbwss8sql -w1000"," /Q "SET NOCOUNT ON Select * from PK_Dev.dbo.[38607SA]" -o "\\pkcusesbwss8utl\SL8Data\pkc_FixedAssetTaxSchedules\ErrD.txt"

paul.vanhouse SSC Enthusiast Points: 151 More actions · Answer 2

I figured out three things: 1-it was taking a long time because there was so much data. 2-it works faster when using a "real" table instead of the ##temp table. 3-it works even faster using sqlcmd.

This is what I'm using now

sqlcmd -E -t10 -w1000 -o"\\pkcusesbwss8utl\SL8Data\pkc_FixedAssetTaxSchedules\ErrD.txt" -Q"SET NOCOUNT ON ; Select * from PK_Dev.dbo.[16815SA]" &Exit

Additions: Added a semicolon between SQL commands. Added a &Exit to guarantee that the DOS command prompt used by osql will close. (when run from the command line it closes the DOS window)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Have you tried using bcp? bcp may prove to be faster than sqlcmd. bcp was designed to be fast at getting data from a file on disk into SQL Server, and vice-versa. sqlcmd is less specialized when it comes to the tasks bcp handles. sqlcmd is good for interactive command line and stored scripting functions.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

paul.vanhouse SSC Enthusiast Points: 151 More actions · Answer 4

bcp doesn't include column headers (unless there's an option I don't know about). For this output I need column headers and columnar data...similar to a text output from query analyzer.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

Oh well...that's not something bcp can do with built-in functionality. You would have to do something with UNION ALL in your query to make the first row of your resultset contain the column names....or you could output two files, one with the column names, one with the data, and then use the cmd shell COPY command to append the data file to the column-name file.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato