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

  • 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

  • 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

  • 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.

    🙂

  • 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

  • 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.


  • 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.

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

  • 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

  • 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

  • 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

  • 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.

  • 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


  • 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?

  • 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

  • Does this mean it works?

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

    John

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

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