I regularly run a complex script manually in MSSMSE. The results shown in
Grid I then right click on the top left corner of the grid and select "Save
results as" and then save it as a CSV file.

The above works just as I want it to and the saved CSV file is just as I
would expect to see it.

But I have to do this for a large number of queries on all databases on the
server which is very time consuming.

I have tried saving the script and running it with osql
(sorry should have mentioned earlier this is still an old SQL2000 server)
But unfortunately the resultant CSV file seems to have a lot of blank spaces
and wraps lines and just is not in the same CSV format as that exported in
first step.

I am therefore wondering if there is not a way in which I could add
something into the SELECT statement to save reults to CSV file and hope the
results come out in the same format,

I have also tried altering settings to save results to file instead of GRID
but this also comes out in a different format.

If anybody has tried to do this before and come accross a solution for this
I will really appreciate it.

Regards
C

Cathy (Cathy (AT) Nospam (DOT) com) writes:
You can use -h option to supress headers, -s to set the column separator and
-w to control the width of the output. However, there will still be a lot of
space.

However, a better option for you may be BCP, at least as long as you
don't want the header lines.

bcp "yourquerygoeshere" queryout outputfile.txt -c -t, -CRAW -T -S server

-T is for trusted connection. Use -U and -P if you use SQL Server
authentication.

Beware that if there is comma in the data, bcp will not quote the value.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

I was just looking at BCP, struggled a bit, got most of it working in the
end, but still not quite the same format.

I was in fact hoping there was a way I could specify outfile as can be done
in MySQL but I dont seem to get this option in MSSMSE

WIll keep looking, there must be a way

C

Cathy (Cathy (AT) Nospam (DOT) com) writes:
So what was wrong with the format?

I don't know about MySQL, but SSMS is just a client tool for interactive
queries.

There is also the Export/Import wizard which you find the program
group for SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Well as you said
">Beware that if there is comma in the data, bcp will not quote the value."

Try to open the file exported via MSSMSE and the once created by BCP after
doing this and you will note the difference

This is the bcp command I am using., try the same with

bcp "SELECT CategoryID, CategoryName, Description FROM
[Northwind].[dbo].[Categories]" queryout outputfile.csv -c -t, -CRAW -T -S
PCNAME\SQLEXPRESS

Results when opened up in Excel is very different and not acceptable.

I could use the wizard, but the idea is to get a script that I can run
against many databases, to export the results individually whilst running.
Saving file data manually is almost easier and quicker than using the
wizard, unless I am missing a trick.

***RESULT of BCP***
1,Beverages,Soft drinks, coffees, teas, beers, and ales
2,Condiments,Sweet and savory sauces, relishes, spreads, and seasonings
3,Confections,Desserts, candies, and sweet breads
4,Dairy Products,Cheeses
5,Grains/Cereals,Breads, crackers, pasta, and cereal
6,Meat/Poultry,Prepared meats
7,Produce,Dried fruit and bean curd
8,Seafood,Seaweed and fish

*** RESULT OF MSSMSE***
CategoryID,CategoryName,Description
1,Beverages,"Soft drinks, coffees, teas, beers, and ales"
2,Condiments,"Sweet and savory sauces, relishes, spreads, and seasonings"
3,Confections,"Desserts, candies, and sweet breads"
4,Dairy Products,Cheeses
5,Grains/Cereals,"Breads, crackers, pasta, and cereal"
6,Meat/Poultry,Prepared meats
7,Produce,Dried fruit and bean curd
8,Seafood,Seaweed and fish

Cathy (Cathy (AT) Nospam (DOT) com) writes:
OK. This can be arranged with a format file:

9.0
3
1 SQLCHAR 0 0 "," 1 Description ""
2 SQLCHAR 0 0 ",\"" 2 CategoryName Latin1_General_CI_AS
3 SQLCHAR 0 0 "\"\r\n" 3 Description Latin1_General_CI_AS

Then replace "-c -t," in the command with "-f format.fmt".

An alternative is to run the query as

SELECT CategoryID, CategoryName, quotename(Description, '"')
FROM [Northwind].[dbo].[Categories]

Actually, this is better, because this will also handle double quotes
in the data.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

This is brilliant
Only problem I have now is that bcp reads the quote as the end of the
command. Is there a way to escape this?

bcp "SELECT CategoryID, CategoryName, quotename(Description, '"')FROM
[Northwind].[dbo].[Categories]" queryout outputfile.csv

Thanks so much for your help to so far

C

Cathy (Cathy (AT) Nospam (DOT) com) writes:
Well, not BCP but the command-line parser. I should have thought about
that, sorry. I think that you can use \ to escape the quote:

bcp "SELECT CategoryID, CategoryName, quotename(Description, '\"')FROM
[Northwind].[dbo].[Categories]" queryout outputfile.csv

But it may be simpler to use

quotename(Description, char(34))

instead, and evading the problem that way.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thank you so much for you help. I did try \" but that did not work. Was not
aware of char(34)

Worked a treat.

Regards
C