Create CSV export from Stored Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Create CSV export from Stored Procedure

Author

Topic

gelwood
Starting Member

8 Posts

Posted - 2011-06-29 : 10:35:55

Hello! I need help in creating a CSV file from a Stored Procedure.
(I'm new to this and not sure if I'm formatting this correctly?)
Have received some help but still having problems...
When i run the stored proc i get the following results?
My stored proc is included below.
Thanks for any help you can provide.

SERVER NAME: LSFTEST
DATABASE: TEST
STORED PROC: HR_LPMH_UPDATE
TEMP TABLE: HRIMPUT
DESTINATION FOR CSV FILE: e:\LAWSON_HR_OUTPUT\HR_Test.csv

-------------------------------------------------------------------
RESULTS:
Output
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL

MESSAGES
(685 row(s) affected)

(3 row(s) affected)

-------------------------------------------------------------
STORED PROC:

ALTER PROC [dbo].[HR_LPMH_UPDATE]
AS
SELECT
EM.EMPLOYEE,
EM.FIRST_NAME,
EM.LAST_NAME,
EM.EMAIL_ADDRESS,
GK.UserName AS [Login ID],
'Password$' AS [Password],
Convert(Char,DATE_HIRED,101) AS [Hire Date],
EM.JOB_CODE,
'Employee Role'= CASE
WHEN HS.USER1='LEV1' THEN 'VP'
WHEN HS.USER1='LEV2' THEN 'VP'
WHEN HS.USER1='LEV3' THEN 'DIR'
WHEN HS.USER1='LEV4' THEN 'MGR'
WHEN HS.USER1='LEV5' THEN 'MGR'
ELSE 'EMPLOYEE'
END,
'' AS [Pay Grade],
EM.DEPARTMENT As [Department Number],
'' AS [Initial Evaluation Due Date],
CASE
WHEN HS.USER1='LEV1' THEN ''
WHEN HS.USER1='LEV2' THEN ''
WHEN EM.DATE_HIRED >='09/01/2009' THEN '11/30/2010'
ELSE '11/30/2009'
END AS [Annual Evaluation Due Date],
'' AS [Initial Skill Due Date]
INTO HRIMPUT

FROM dbo.EMPLOYEE EM
LEFT JOIN dbo.GET_KRONOSUSERS GK ON EM.EMPLOYEE = GK.employeeNumber
LEFT JOIN dbo.HRSUPER HS ON EM.EMPLOYEE = HS.EMPLOYEE

WHERE EM.EMP_STATUS<>'TD'
AND EM.JOB_CODE<>'9001'

ORDER BY EM.JOB_CODE

DECLARE @sql varchar(8000)
SELECT @sql = 'bcp "select * from TEST..HRIMPUT" '
+ 'queryout e:\LAWSON_HR_OUTPUT\HR_Test.csv -c -t, -T -S'+ @@servername
EXEC master..xp_cmdshell @sql

Drop table HRIMPUT

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-29 : 14:50:40

If I understood your logic correctly:
a) You have a stored procedure named TEST.dbo.HR_LPMH_UPDATE
b) In the stored procedure you have a call to EXEC xp_cmdshell to invoke the same stored procedure.
c) You are trying to do a select * from the stored procedure.

There are a few things that need to be corrected here. You cannot do a select * from AStoredProcedure. You have to invoke it as "exec AStoredProcedure". Second, you don't want to invoke the stored procedure from within itself.

Can you describe what you are trying to accomplish? May be people on this forum can offer suggestions on the best way to accomplish that.

gelwood
Starting Member

8 Posts

Posted - 2011-06-29 : 15:31:52

OK… Sorry for the confusion.
I simply want to output selected records from database TEST and create a CSV file that I can place in a designated directory.
My main confusion is/was on how to use “bcp” and “xp_cmdshell” to create a CSV file.

Thanks for any suggestions/help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-29 : 19:50:58

For this, all you need to do is what you have already tried - which is simply this:


DECLARE @sql varchar(8000)
SELECT @sql = 'bcp "select * from TEST..YOURTABLENAME" '
+ 'queryout c:\LAWSON_HR_OUTPUT\HR_Test.csv -c -t, -T -S'
+ @@servername
EXEC master..xp_cmdshell @sql

Alternatively, instead of queryout you can use out with the table name. But you cannot use a stored procedure name in either case.

If you do want to use the stored procedure, then instead of "select * from TEST..YOURABLENAME", use "exec TEST..YOURSTOREDPROCNAME".

gelwood
Starting Member

8 Posts

Posted - 2011-06-30 : 07:44:48

When i run the stored proc i get the following:
RESULTS
Output=
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
------------------------------------------
MESSAGES
(685 row(s) affected)

(3 row(s) affected)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-30 : 07:52:45

c:\LAWSON_HR_OUTPUT\HR_Test.csv has to be on the server or a reachable drive...

No, you're never too old to Yak'n'Roll if you're too young to die.

gelwood
Starting Member

8 Posts

Posted - 2011-06-30 : 08:01:24

This is being executed on one of our remote servers but c:\LAWSON_HR_OUTPUT\HR_Test.csv exists on that server?
Do you have to include a reference to the server name?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-30 : 14:17:44

When you specify the path as c:\LAWSON_HR_OUTPUT\HR_Test.csv it is looking at the c:\LAWSON_HR_OUTPUT drive of the server. Also, it is trying to write in the security context of the SQL Service Account on the server. You can use UNC paths, but you will need to set up the permissions so the service account on the remote server has permissions to write to the UNC path you choose. The following links may be helpful:

http://social.msdn.microsoft.com/Forums/en/sqltools/thread/b95be88f-6a72-44fc-8980-70fd5d94a6c2
http://msdn.microsoft.com/en-us/library/ms175046.aspx

I must admit that I have never gone through all that trouble (I am assuming it is a lot of trouble, but I don't know). Instead, what I have done is to run the bcp commands from Windows using a login that has the required permissions on the database (select/bulkadmin?) and the required write privileges to the destination folder. Part of the reason for this is my paranoia about xp_cmdshell. Being allowed to run windows commands from SQL somehow seems a little scary to me. But if my impressions are right, I may be in the minority - I see lot of references to doing things using xp_cmdshell on this forum.

Hopefully one of them will offer more useful suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources