ISQL inserting spaces

[ringworm24]

I asked this a few days ago and some replied RTrim everything, thanks for the suggestion but, thats not the answer, I know what the problem is I am excuting the stored procedure using ISQL so i can run it through the sql server agent, and for some reason it is that engine which is inserting the spaces.

Please help

''I have an automated report which has lots of unwanted spaces in in it and a few empty rows at the EOF. When I run the report from Query analyzer and save as csv it has no spaces, where is the auotmated report getting the spaces from and how can I stop this? Many Thanks''

 

[mrdenny]

Are you trying to use isql to export the data to a text file?

Use BCP instead. isql isn't a data export tool. It's an interactive command tool. BCP is a data export tool.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ringworm24]

Still dosnt help the csv file has huge spaces it in ie.

xxxx ,aaaa ,bb ,etc

why are these spaces being inserted? If a run the SP from query analzer and save it then the are no spaces!!!

please help

 

[mrdenny]

What data type is your data?

When using Query Analyzer are you in Grid mode or text mode?

What is the command line you are using to export the data?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ringworm24]

Ojay I am struggling here to use BCP as well

Hi I am trying to use BCP to save the results of an SP to a file here is my code

DECLARE @Cmd VARCHAR(1000)

SELECT @Cmd = 'bcp "exec mydatabase..my_sp" QUERYOUT "d:\reports\bob.csv" -w -t"," -T'

EXEC master..xp_cmdshell @Cmd

I get invaild object errors on temp tables used within the SP

any ideas?

DENNY: In Query Analyzer it works fine i use grid mode and the save it as as a CSV, but when i do the same thing useing isql I have loads of spaces

the ISQL I use is the following

SELECT @Cmd = 'isql -U myuser -P mypass-i ' + @DriveLetter + ':\InputFiles\test.sql -o ' + @OutputFilename + ' -s ' + @Separator + ' -w 8000 -n -h-1 -m-99999'

Any help please

 

[mrdenny]

In isql you'll have that problem.

That's very strange that you are getting temp table errors when running the stored proc via BCP. I'd fire up profiler, and turn on a bunch of the events and see what it's doing. I'd also add the -S switch to the BCP as well as the -B switch.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com