Need help with writing to text file, new to sql server

Question

Post reply

Need help with writing to text file, new to sql server

Viewing 8 posts - 16 through 23 (of 23 total)

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

ram_kamboz2k7 SSC-Addicted Points: 475 More actions · Answer 1

No I did, I linked the database name twice.

But now I havent, it has now outputted to a text file.

But, is there a simple way of extracting certain rows from the users table?

I.E. ID, LOGINNNAME, SURNAME, FORENAME?

Thanks Guys

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Sounds like you're being taught to run before you can walk. Those are columns, not rows.

SELECT col1, col2, col3 FROM MyDB.dbo.USERS

John

ram_kamboz2k7 SSC-Addicted Points: 475 More actions · Answer 3

ram_kamboz2k7

SSC-Addicted

Points: 475

February 14, 2012 at 3:34 am

#1446735

DONE IT!

This is great!

ram_kamboz2k7 SSC-Addicted Points: 475 More actions · Answer 4

Thanks Guys

Yes I meant columns, but I was just thinking about extracting rows of data, but just from the required columns

Sorry I did not comment it properly.

Just a few more things...Now I have the main text inserted within the text file. The task reuires me to produce a header and a footer.

Would I just need to produce a simple execution as I did before, but to the same file?

EXEC xp_cmdshell 'bcp "Select count (*).TM37_1_2_ONLINE.dbo.USERS" queryout "C:\BMW\tsqloutput\gradfive.txt" -T -c -t,'

?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

A header and a footer? I'm out of my depth now. Sounds like something you'd user Reporting Services for. Do you have a sample of what the output file should look like?

A hint for you: before you try to write a bcp command, check that the the SELECT statement works in SSMS. If it doesn't, it won't work in the bcp command.

John

ram_kamboz2k7 SSC-Addicted Points: 475 More actions · Answer 6

20120202

100000,RamTallyman1,Ram,Kamboz

0,TALLYMAN,,TALLYMAN

1,tmInstall,,

3

Above is an example of a notepad, just showing a simple output of what the file should actually look like.

20120202 - the header of the file

100000,RamTallyman1,Ram,Kamboz

0,TALLYMAN,,TALLYMAN - The left is the rows of data extracted from the selected columns

1,tmInstall,,

3 - A count of the number of rows within the table Users.

ram_kamboz2k7 SSC-Addicted Points: 475 More actions · Answer 7

20120202 - Sorry, this is the date of the day it was created, therefore, if possible, I require todays date within the header, which Im sure you would use the GetDate() function for.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

The only (easy) way I can think of for achieving anything like that is to use sqlcmd and pipe the output into your text file.

John