| Author |
Topic |
|
Cowski
Starting Member
30 Posts |
 Posted - 2010-08-03 : 15:09:30
|
| We're looking for a way to set up some SQL code that will export table data to a csv file without using BCP or xp_cmdshell due to security issues. There has to be a way to do this...may need a nudge in the right diretions.Thanks! |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Cowski
Starting Member
30 Posts |
Posted - 2010-08-03 : 15:18:06
|
Okay...I have an update. Evidently I misunderstood my lead' concern.  BCP is okay...it's just the shell he wants to stay away from.Thanks! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Cowski
Starting Member
30 Posts |
Posted - 2010-08-03 : 16:04:42
|
quote:
Originally posted by tkizer
Who or what is going to do the export? A SQL job, a person, a stored procedure, a program, ...?
My bad...(got the 3:30pm I can't stay awake & think right fever)...This would be run, preferably, from a stored proc that could then be run from the SQL Server Agent if at all possible. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-08-03 : 16:07:11
|
| If it's from a stored procedure, then you'd need to call bcp via xp_cmdshell. But a SQL Agent job allows an executable to run directly from a CmdExec job step, so you can directly call bcp.exe from there.Here's some bcp/csv examples:Export a table:bcp db1.dbo.tbl1 out C:\folder1\file1.csv -Sserver1\instance1 -T -c -t, -r\r\nExport the results of a query:bcp "select c1, c4 from db1.dbo.tbl1 where c2 = 2" queryout C:\folder1\file1.csv -Sserver1\instance1 -T -c -t, -r\r\nTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-03 : 16:09:01
|
You can also use SQLCLR to do this, but you cannot use SAFE mode.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Cowski
Starting Member
30 Posts |
Posted - 2010-08-04 : 11:17:26
|
quote:
Originally posted by tkizer
Here's some bcp/csv examples:Export a table:bcp db1.dbo.tbl1 out C:\folder1\file1.csv -Sserver1\instance1 -T -c -t, -r\r\n
I've run this string with just about every mixture of switches I can think of. Now for your example here my syntax is as follows:BCP master..sysobjects out c:\hold\sysobjects.csv -SServername\Instance -T -c -t, -r (I'm just using the sysobjects as an example to get it running)The results I'm getting back from this line are:"Executed as user: SERVERNAME\SYSTEM. SQLState = S1000<c/> NativeError = 0 Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file. Process Exit Code 1. The step failed."Note:I'm using the Operating System (CmdExec) Type when building my job.When creating the job, sqladmin is the owner.Also...when I run the same BCP code line as above, insert the correct servername & put it in a DOS prompt...IT WORKS FINE!!  |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-04 : 11:34:38
|
One thing. The path when using SQLJob is reletive the server, not your local workstation.Where do you open your command window (dos prompt)? On your local machine?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Cowski
Starting Member
30 Posts |
Posted - 2010-08-04 : 13:21:30
|
Okay...very much embarrassed  on some of my questions but got the issue totally resolved!!Thank you very much Tara & Peso!! This is exactly what we needed!! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Export to CSV WITHOUT using BCP or xp_cmdshell