February 14, 2012 at 2:44 am
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
February 14, 2012 at 2:53 am
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
February 14, 2012 at 2:56 am
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.
February 14, 2012 at 3:02 am
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
February 14, 2012 at 3:04 am
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.
February 14, 2012 at 3:07 am
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.
February 14, 2012 at 3:07 am
Also, users should not be an invalid object name...as it is created as a table already.
February 14, 2012 at 3:12 am
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
February 14, 2012 at 3:18 am
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
February 14, 2012 at 3:22 am
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
February 14, 2012 at 3:25 am
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.
February 14, 2012 at 3:28 am
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
February 14, 2012 at 3:28 am
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?
February 14, 2012 at 3:30 am
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
February 14, 2012 at 3:32 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy