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

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

Cookies on SQLServerCentral