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

Question

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

ram_kamboz2k7

SSC-Addicted

Points: 475
More actions
February 14, 2012 at 2:44 am

#252863

Hi Guys
I am new to sql server and require a procedure which is able to output data to a text file from a specific table.
I have read the concepts of BCP and xp CMD SHELL. However I need help compiling a simple output.
(5)Write a stored procedure that creates a comma separated flat file called ‘GradChallenge’ followed by today’s date formatted ‘yyyymmdd’ and add the extension ‘.txt’.
The file will contain the ID, LOGINNAME, FORENAME and SURNAME of the USERS table. The header will consist of today’s date in the format ‘yyyymmdd’ and the footer will contain a row count.
I hope this task makes sense, I am new to sql server so any help would be appreciated.
Thank You

Viewing 15 posts - 1 through 15 (of 23 total)

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

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

Welcome to SQLServerCentral. We're all to help you, however most of us draw the line at doing your homework for you. Please have a go at doing it yourself, and post back if there's any particular aspect you need help with.

John

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

declare @path varchar(2000)

set @path = 'C:\'

EXEC master.dbo.xp_create_subdir @Path

I have created a directory first and foremost.

EXEC xp_cmdshell 'bcp "SELECT * FROM USERS" queryout "C:\gradfive.txt" -T -c -t,'

I want the above to just output all from table users in gradfive text file, but does not work.

I'd rather take it step by step.

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

ram_kamboz2k7 (2/14/2012)
but does not work.

Error message? File not created? Data in wrong format in file? Other problem? Please be more specific, and help us to help you.

John

Phil Parkin SSC Guru Points: 247158 More actions · Answer 4

ram_kamboz2k7 (2/14/2012)
declare @path varchar(2000)
set @path = 'C:\'
EXEC master.dbo.xp_create_subdir @Path
I have created a directory first and foremost.
EXEC xp_cmdshell 'bcp "SELECT * FROM USERS" queryout "C:\gradfive.txt" -T -c -t,'
I want the above to just output all from table users in gradfive text file, but does not work.
I'd rather take it step by step.

Why would you create a path c:\? This pretty much exists on every PC.

Saying that something "does not work" is unhelpful. Please post the text of the error you are receiving.

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

Sorry

declare @path varchar(2000)

set @path = 'C:\BMW\tsqloutput'

EXEC master.dbo.xp_create_subdir @Path

The directory creates successfully.

EXEC xp_cmdshell 'bcp "SELECT * FROM USERS" queryout "C:\bcptest.txt" -T -c -t,'

To extract all from table users produces the following errors.

SQLState = S0002, NativeError = 208

Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'USERS'.

SQLState = 37000, NativeError = 8180

Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.

The errors are above.

Sorry for not being specific before.

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

Also, users should not be an invalid object name...as it is created as a table already.

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

Unless the USERS table exists in the default database for your user, you will need to qualify it with the database (and maybe also schema) name:

SELECT * FROM MyDB.MySchema.USERS

By the way, you'll probably have learnt in a previous class that using "SELECT *" is not good practice. Best to provide an explicit column list.

John

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

EXEC xp_cmdshell 'bcp "SELECT * FROM dbo.TM37_1_2_ONLINE.USERS" queryout "C:\BMW\tsqloutput\gradfive.txt" -T -c -t,'

The database name is dbo, and the schema name is tm37_1_2_online.

I query it out to the file i want it to write to.

But still produces the same errors as before.

Is there something i need to do before this?

Directory has been created and read,write permissions have been granted.

Thanks

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

Are you sure tm37_1_2_online isn't the database name, because dbo sounds like the schema name. Just swap it round with dbo and it should work.

As a bit of background reading, I recommend that you search for "fully qualified object names in sql server".

John

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

EXEC xp_cmdshell 'bcp "SELECT * FROM TM37_1_2_ONLINE.TM37_1_2_ONLINE.dbo.USERS" queryout "C:\BMW\tsqloutput\gradfive.txt" -T -c -t,'

Well I have changed it around and seems to come up with the same errors.

The users table definitely does exist within the database.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 11

ram_kamboz2k7 (2/14/2012)
EXEC xp_cmdshell 'bcp "SELECT * FROM TM37_1_2_ONLINE.TM37_1_2_ONLINE.dbo.USERS" queryout "C:\BMW\tsqloutput\gradfive.txt" -T -c -t,'
Well I have changed it around and seems to come up with the same errors.
The users table definitely does exist within the database.

Try

TM37_1_2_ONLINE.dbo.USERS

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

EXEC xp_cmdshell 'bcp "SELECT * FROM.TM37_1_2_ONLINE.dbo.USERS" queryout "C:\BMW\tsqloutput\gradfive.txt" -T -c -t,'

Ah. I have something else.

NULL

Starting copy...

NULL

4 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 16 Average : (250.00 rows per sec.)

NULL

Does this mean it works?

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

You haven't read about fully qualified object names, have you? Nor have you read Phil's suggestion properly. The only way your code will work is if you have a linked server with the same name as the database. But that's academic - just get rid of one of the first database name.

John

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

Does this mean it works?

You tell me - you have access to the file; I don't.

John

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

Cookies on SQLServerCentral