Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bmmpam
Starting Member
5 Posts |
 Posted - 2007-02-08 : 10:05:14
|
| I am using a stored procedure to query tables and format a record to write to a file. Below is the syntax I'm using to do the write.set @cmd = 'echo ' + rtrim(@patient_rec) + ' >> f:\output\recall.csv'exec master..xp_cmdshell @cmdThe procedure will write about 30,000 records or so and then quit writing to the file. Is there a setting I have to modify to write more records or is there a better way to do this? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-08 : 10:09:09
|
| Why don't you use BCP and output all the record at once?Peter LarssonHelsingborg, Sweden |
 |
|
|
bmmpam
Starting Member
5 Posts |
Posted - 2007-02-08 : 10:11:46
|
| Pretty new to sql server and am not sure how to do this. Would I write all records to a temp table and then use BCP? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-02-08 : 10:16:10
|
| No, you can output the result of a query too, to a file directly.declare @sqlcmd varchar(8000)set @sqlcmd = 'bcp "SELECT FirstName, LastName FROM AdventureWorks.Person.Contact ORDER BY LastName, Firstname" queryout X:\Contacts.txt -c -T'exec master..xp_cmdshell @sqlcmdFOr more information about BCP command, turn to Books Online and do a search for BCPPeter LarssonHelsingborg, Sweden |
|
|
|
bmmpam
Starting Member
5 Posts |
Posted - 2007-02-09 : 10:27:55
|
| Peso, Thanks for your help, but I am using a quite lengthy stored procedure to gather my data so it doesn't seem like bcp is the best approach. Any other suggestions? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
bmmpam
Starting Member
5 Posts |
Posted - 2007-02-09 : 11:17:30
|
| I can give this a try. Any idea why it stopped after writing 30,000+ records? |
|
|
|
|
|
|
stored procedure output