Exporting table to a .csv file

PHP
41

Yeah, that would work too :slight_smile: Though, keep in mind if you have ORs and AND conditions properly separated with (), your last replacement will cause issues.

42

The next stage on from here is it to create the table from the data as below:


<?
session_start();
//header('Content-Type: application/csv'); //Outputting the file as a csv file
//header('Content-Disposition: attachment; filename=example.csv'); //Defining the name of the file and suggesting the browser to offer a 'Save to disk '
//header('Pragma: no-cache');

// connect to your SQL Server

require_once('config.php');
$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);

$query = $_SESSION['currentQuery'];

$query = str_replace('WITH LIMIT AS( ', '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace(')', '', $query);
var_dump($query);

$stmt = sqlsrv_query($conn, $query);
while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
{
echo "<table>";
echo "<tr>";
echo "<td width='40'>".$data["ID"]."</td>";
echo "<td width='70'>".$data["Contract_Number"]."</td>";
echo "<td width='70'>".$data["Contract_Status"]."</td>";
echo "<td width='55'>".$data["Annual_Contract_Value"]."</td>";
echo "<td width='80'>".date_format($data['Contract_Start'], 'd/m/y')."</td>";
echo "<td width='80'>".date_format($data['Contract_End'], 'd/m/y')."</td>";
echo "<td width='60'>".$data["Contract_Length"]."</td>";
echo "<td width='80'>".date_format($data['Renewal_Date'], 'd/m/y')."</td>";
echo "<td width='110'>".$data["Site_Name"]."</td>";
echo "<td width='80'>".$data["Street"]."</td>";
echo "<td width='80'>".$data["City"]."</td>";
echo "<td width='100'>".$data["State"]."</td>";
echo "<td width='100'>".$data["Country"]."</td>";
echo "<td width='60'>".$data["Region"]."</td>";
echo "<td width='60'>";
if ($data["Group_Member"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='100'>".$data["Group_Name"]."</td>";
echo "<td width='45'>".$data["No_of_Rooms"]."</td>";
echo "<td width='90'>".$data["Type_of_Establishment"]."</td>";
echo "<td width='60'>";
if ($data["CSF"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Food_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Swimming_Pool_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Legionella_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Fire_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Hurricane"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Green"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Guest"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Room_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Quality_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Supply_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Tourcheck"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>".$data["No_of_Modules"]."</td>";
echo "<td width='60'>".$data["Currency_of_Invoice"]."</td>";
echo "<td width='60'>".$data["No_of_audits_per_annum"]."</td>";
echo "<td width='60'>";
if ($data["No_of_visits_per_annum"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Labs"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
//echo "<td width='60' >".$data["No_of_visits_per_annum"]."</td>";
echo "<td width='65'>";
if ($data["Seasonal_or_Full"]==1){
echo "Seasonal";
} else {
echo "Full Year";
}
echo "</td>";
//echo "<td width='65' >".$data["Month_Opens"]."</td>";
echo "<td width='60'>";
if ($data["Month_Opens"]=="A. January"){
  echo "January";
}
if ($data["Month_Opens"]=="B. February"){
  echo "February";
}
if ($data["Month_Opens"]=="C. March"){
  echo "March";
}
if ($data["Month_Opens"]=="D. April"){
  echo "April";
}
if ($data["Month_Opens"]=="E. May"){
  echo "May";
}
if ($data["Month_Opens"]=="F. June"){
  echo "June";
}
if ($data["Month_Opens"]=="G. July"){
  echo "July";
}
if ($data["Month_Opens"]=="H. August"){
  echo "August";
}
if ($data["Month_Opens"]=="I. September"){
  echo "September";
}
if ($data["Month_Opens"]=="J. October"){
  echo "October";
}
if ($data["Month_Opens"]=="K. November"){
  echo "November";
}
if ($data["Month_Opens"]=="L. December"){
  echo "December";
}
echo "</td>";
//echo "<td width='65' >".$data["Month_Closes"]."</td>";
echo "<td width='60'>";
if ($data["Month_Closes"]=="A. January"){
  echo "January";
}
if ($data["Month_Closes"]=="B. February"){
  echo "February";
}
if ($data["Month_Closes"]=="C. March"){
  echo "March";
}
if ($data["Month_Closes"]=="D. April"){
  echo "April";
}
if ($data["Month_Closes"]=="E. May"){
  echo "May";
}
if ($data["Month_Closes"]=="F. June"){
  echo "June";
}
if ($data["Month_Closes"]=="G. July"){
  echo "July";
}
if ($data["Month_Closes"]=="H. August"){
  echo "August";
}
if ($data["Month_Closes"]=="I. September"){
  echo "September";
}
if ($data["Month_Closes"]=="J. October"){
  echo "October";
}
if ($data["Month_Closes"]=="K. November"){
  echo "November";
}
if ($data["Month_Closes"]=="L. December"){
  echo "December";
}
echo "</td>";
echo "<td width='65'>".$data["Invoicing_Profile"]."</td>";
echo "<td width='80'>".date_format($data['date_Created'], 'd/m/Y')."</td>";
echo "<td width='80'>".date_format($data['date_Modified'], 'd/m/Y')."</td>";
echo "</tr>";
echo "</table>";
}

// Build the Query you want to Export to CSV
// Run a Query using 'exec xp_cmdshell \\'bcp "' . $sqlQueryToExport . '" queryout "<path to web server>\\contractsCSF.csv" -T -c -t","\\'' as your SQL Query
// Close SQL Server Connection

//echo readfile('contractsCSF.csv'); //Reading the contents of the file

?>

If not then will scrap it, but if so then I cant get it to appear at the moment, but working on it.

43

OK it does work with another query, so thats good.

“SELECT * FROM Intranet order by ID DESC”

Thats fine, but not when using search, so at least I know it works…

Is it the next thing to do though, or am I going down the wrong path here

44

Morning cpradio,

Do I need to output the contract details from the query to an actual table as below:


$stmt = sqlsrv_query($conn, $query);
while($data = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC))
{
echo "<table>";
echo "<tr>";
echo "<td width='40'>".$data["ID"]."</td>";
echo "<td width='70'>".$data["Contract_Number"]."</td>";
echo "<td width='70'>".$data["Contract_Status"]."</td>";
echo "<td width='55'>".$data["Annual_Contract_Value"]."</td>";
echo "<td width='80'>".date_format($data['Contract_Start'], 'd/m/y')."</td>";
echo "<td width='80'>".date_format($data['Contract_End'], 'd/m/y')."</td>";
echo "<td width='60'>".$data["Contract_Length"]."</td>";
echo "<td width='80'>".date_format($data['Renewal_Date'], 'd/m/y')."</td>";
echo "<td width='110'>".$data["Site_Name"]."</td>";
echo "<td width='80'>".$data["Street"]."</td>";
echo "<td width='80'>".$data["City"]."</td>";
echo "<td width='100'>".$data["State"]."</td>";
echo "<td width='100'>".$data["Country"]."</td>";
echo "<td width='60'>".$data["Region"]."</td>";
echo "<td width='60'>";
if ($data["Group_Member"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='100'>".$data["Group_Name"]."</td>";
echo "<td width='45'>".$data["No_of_Rooms"]."</td>";
echo "<td width='90'>".$data["Type_of_Establishment"]."</td>";
echo "<td width='60'>";
if ($data["CSF"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Food_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Swimming_Pool_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Legionella_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Fire_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Hurricane"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Green"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Guest"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Room_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Quality_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Supply_Safety"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Tourcheck"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>".$data["No_of_Modules"]."</td>";
echo "<td width='60'>".$data["Currency_of_Invoice"]."</td>";
echo "<td width='60'>".$data["No_of_audits_per_annum"]."</td>";
echo "<td width='60'>";
if ($data["No_of_visits_per_annum"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
echo "<td width='60'>";
if ($data["Labs"]==1){
  echo "Yes";
} else {
  echo "No";
}
echo "</td>";
//echo "<td width='60' >".$data["No_of_visits_per_annum"]."</td>";
echo "<td width='65'>";
if ($data["Seasonal_or_Full"]==1){
echo "Seasonal";
} else {
echo "Full Year";
}
echo "</td>";
//echo "<td width='65' >".$data["Month_Opens"]."</td>";
echo "<td width='60'>";
if ($data["Month_Opens"]=="A. January"){
  echo "January";
}
if ($data["Month_Opens"]=="B. February"){
  echo "February";
}
if ($data["Month_Opens"]=="C. March"){
  echo "March";
}
if ($data["Month_Opens"]=="D. April"){
  echo "April";
}
if ($data["Month_Opens"]=="E. May"){
  echo "May";
}
if ($data["Month_Opens"]=="F. June"){
  echo "June";
}
if ($data["Month_Opens"]=="G. July"){
  echo "July";
}
if ($data["Month_Opens"]=="H. August"){
  echo "August";
}
if ($data["Month_Opens"]=="I. September"){
  echo "September";
}
if ($data["Month_Opens"]=="J. October"){
  echo "October";
}
if ($data["Month_Opens"]=="K. November"){
  echo "November";
}
if ($data["Month_Opens"]=="L. December"){
  echo "December";
}
echo "</td>";
//echo "<td width='65' >".$data["Month_Closes"]."</td>";
echo "<td width='60'>";
if ($data["Month_Closes"]=="A. January"){
  echo "January";
}
if ($data["Month_Closes"]=="B. February"){
  echo "February";
}
if ($data["Month_Closes"]=="C. March"){
  echo "March";
}
if ($data["Month_Closes"]=="D. April"){
  echo "April";
}
if ($data["Month_Closes"]=="E. May"){
  echo "May";
}
if ($data["Month_Closes"]=="F. June"){
  echo "June";
}
if ($data["Month_Closes"]=="G. July"){
  echo "July";
}
if ($data["Month_Closes"]=="H. August"){
  echo "August";
}
if ($data["Month_Closes"]=="I. September"){
  echo "September";
}
if ($data["Month_Closes"]=="J. October"){
  echo "October";
}
if ($data["Month_Closes"]=="K. November"){
  echo "November";
}
if ($data["Month_Closes"]=="L. December"){
  echo "December";
}
echo "</td>";
echo "<td width='65'>".$data["Invoicing_Profile"]."</td>";
echo "<td width='80'>".date_format($data['date_Created'], 'd/m/Y')."</td>";
echo "<td width='80'>".date_format($data['date_Modified'], 'd/m/Y')."</td>";
echo "</tr>";
echo "</table>";
}

Or do I bypass that, and I’m looking at this line:


exec xp_cmdshell \\'bcp "' . $sqlQueryToExport . '" queryout "<path to web server>\\contractsCSF.csv" -T -c -t","\\'

As I see it I need to change $sqlQueryToExport to $query and then find the path to the server.

I have had to comment out some lines of code that you gave me to, otherwise download.php which is this page doesnt load, as I get a error pop up and have to close the connection down.


//header('Content-Type: application/csv'); //Outputting the file as a csv file
//header('Content-Disposition: attachment; filename=example.csv'); //Defining the name of the file and suggesting the browser to offer a 'Save to disk '
//header('Pragma: no-cache');

I understand your probably busy cpradio, so I am reading through and trying ot understand how to exprt this lot to a .csv file now.

45

Ye I can see it now, the bcp $query bit to putput the query is what outputs it.

So have taken away the table and have left myself with:


<?
error_reporting(E_ALL);
ini_set('display_errors','OFF');

session_start();
header('Content-Type: application/csv'); //Outputting the file as a csv file
header('Content-Disposition: attachment; filename=example.csv'); //Defining the name of the file and suggesting the browser to offer a 'Save to disk '
header('Pragma: no-cache');

// connect to your SQL Server

require_once('config.php');
$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);

$query = $_SESSION['currentQuery'];

$query = str_replace('WITH LIMIT AS( ', '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace(')', '', $query);
//var_dump($query);

exec xp_cmdshell \\'bcp "' . $query . '" queryout "\\contractsCSF.csv" -T -c -t","\\''
echo readfile('contractsCSF.csv');
echo readfile('contractsCSF.csv'); //Reading the contents of the file

But one problem I have is when i click the export button from the cms it blocks me as it says it cant find the file, but it is there.

Then when I comment them lines out and go to the page, and refresh it doesnt do anything.

Will keep looking, I’m getting there I think.

46

Right, not getting there error now when I click export, but just a Internal 500 error page.

so possibly some script error somewhere.

Ah, found the error:


'exec xp_cmdshell \\'bcp "' . $query . '" queryout "contractsCSF.csv" -T -c -t","\\'';

Now when I click export, it looks as though its beginnning to download download.php by exporting file, but its stopped in its tracks by a IE opo up telling me that IE cannot download download.php, its either unavailable or cannot be found.

47

Seems like a IE problem that above as tried it in firefox and the option to save as example.csv worked, so got excited and then an error appeared in excel with the following;

Warning: readfile(contractsCSF.csv): failed to open stream: No such file or directory in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\download.php on line 25

So do I need to create an empty contractsCSF.csv file at the root level.

48

Ah,

Got around the IE problem, by using the lines below:

header(“Pragma: public”); // required
header(“Expires: 0”);
header(“Cache-Control: must-revalidate, post-check=0, pre-check=0”);
header(“Cache-Control: private”,false); // required for certain browsers
header(“Content-Transfer-Encoding: binary”);
header(“Content-Type: application/csv”);
header(“Content-Disposition: attachment; filename=contractsCSF.csv” );

But not sure if its right…

It allows me to save a csv file to the desktop say, which I do, but the file doesnt contain any of the query data only the line below:

Warning: readfile(contractsCSF.csv): failed to open stream: No such file or directory in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\download.php on line 30

This is what I got so far:



error_reporting(E_ALL);
ini_set('display_errors','On');

session_start();
header("Pragma: public"); // required
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false); // required for certain browsers
header("Content-Transfer-Encoding: binary");
header("Content-Type: application/csv");
header("Content-Disposition: attachment; filename=contractsCSF.csv" );


// connect to your SQL Server

require_once('config.php');
$connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
$conn = sqlsrv_connect($hostName , $connectionInfo);

$query = $_SESSION['currentQuery'];

$query = str_replace('WITH LIMIT AS( ', '', $query);
$query = str_replace(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'", '', $query);
$query = str_replace('select * from LIMIT WHERE {limit clause}', '', $query);
$query = str_replace(')', '', $query);
//var_dump($query);

'exec xp_cmdshell \\'bcp "' . $query . '" queryout "contractsCSF.csv" -T -c -t","\\'';
echo readfile('contractsCSF.csv');

But then open it up to get the error:

Warning: readfile(contractsCSF.csv): failed to open stream: No such file or directory in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\download.php on line 34

So have gone to the hosts now to try and help me with the path to the server.

49

Have got in touch with the hosts and the path they gave is below:


'exec xp_cmdshell \\'bcp "' . $query . '" queryout "CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv" -T -c -t","\\'';
echo readfile('CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv');

So then tried it again as its all sorted and still had the same error, so then they got back to me about that and said:

Your users won’t be able to access that folder once they’ve downloaded it because it’s only available locally to the webserver. You’ll need to get the webserver script to extract the data, put it into the file and then supply the file with the data already inside it.

But isnt that what we are doing. Building the query which is being transfered to that csv file, then its going to the server, and bouncing back for the user to see and save.

Thats what I’m thinking anyway.

50

This is where it gets interesting because SQL Server is going to want to store the file locally, and you need to have it store it on a different server.

I am assuming this is your web directory (that contains your scripts)

\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\

With that thought, the first thing I would try is


'exec xp_cmdshell \\'bcp "' . $query . '" queryout "\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv" -T -c -t","\\'';
echo readfile('\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv');

If that still fails, check your permissions on your export folder and make sure you give the user the CSV export is running as has permission to write to that folder.

51

Hi cpradio,

Getting back to normal here now, finally!

OK back to it now, I changed the lines above to yours and it still came back with the same error, so have made sure also that the read/write permissions on the export folder and the csv file inside it are on and still it comes back with the same error.


Warning: readfile(\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv): failed to open stream: No such file or directory in \\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\download.php on line 37

The bit I dont get also is that its saying no such file or directory in \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\download.php, when the download.php file does exist there.

52

It isn’t complaining about download.php, but rather about contractsCSF.csv. download.php is the executing PHP file, on line 37 of that file, it should be trying to perform a readfile() which is failing because it can’t find the file.

So this means one of two things (most likely)

  1. The command used to generate the CSV is failing.
  2. The file isn’t being written due to incorrect permissions which is preventing SQL Server from successfully writing the file.

The first thing I would do, is if you are allowed to connect to your SQL Server via SSMS or other related tools, try running the command PHP is trying to execute within SSMS and see what error gets returned.
You might be able to do the following too within PHP

$stmt = sqlsrv_query($conn, $query);
if (!$stmt)
{
  die(mssql_get_last_message());
}

The second thing to try (assuming the first one succeeds), is to give it a very simple query to use for the CSV (SELECT * FROM Intranet), hard code it, and run it again to see if the CSV file is generated, if it isn’t, you likely still have a permission issue.

53

Yes I do have access to SSMS and pasted the above into it and it creates an error and stops running:

‘exec xp_cmdshell \‘bcp “SELECT * FROM Intranet” queryout “\\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\export\contractsCSF.csv” -T -c -t","\’’;
echo readfile(‘\\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\export\contractsCSF.csv’);

Comes back with:

The EXECUTE permission was denied on the object ‘sp_enable_sql_debug’, database ‘mssqlsystemresource’, schema ‘sys’

If its permission issues, i will have to get onto the IT guy here, who is insisting that the export folder and the csv file isnide it have read, write permissions enabled.

54

It is definitely a permission issue but not with the folder. Your user account does not have access to call sp_enable_sql_debug located in database myssqlsystemresource. You will need to talk with your host to see if they will grant you permission to it.

55

OK have emailed the host, so will see what they come back with.

I have been trying to send you that voucher but having trouble with anything alcoholic as they can only deliver it, probably for under age reasons.

So would a amazon.com gift card do you.

56

The hosting company has come back and said that all the permissions on the database are now open, its at its highest permission level now.

I’m wondering if I pasted in the code right in SSMS, so will try again.

Do I paste this one exactly as it is below:


'exec xp_cmdshell \\'bcp "SELECT * From Intranet" queryout "\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv" -T -c -t","\\'';
echo readfile('\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv');
57

No, SSMS only cares about the query, which is

exec xp_cmdshell \\'bcp "SELECT * From Intranet" queryout "\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv" -T -c -t","\\'

It won’t know what to do with the PHP :slight_smile:

If the query runs fine, you will want to verify the file was generated in the export folder.

58

Right I see yes…

OK I tried it and it seemed to be running and then an error popped up, as it did seem to be working.

The error is:


Unable to start T-SQL Debugging. Could not connect to computer 'IP address in here'. Logon Failure: unknown user name or bad password.

I’m guessing thats got nothing to do with me logging into SSMS correctly, as I have, but its a step forward isnt it from the last error.

59

Okay, we are getting somewhere now, so now its time to break out the bcp options

usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]

So now you could utilize the following command (replace ServerName with the actual ServerName, DATASTORE101)

exec xp_cmdshell \\'bcp "SELECT * From Intranet" queryout "\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv" -c -t"," -r"\
" -S ServerName -T\\'

If that still fails, you can try passing your Username and Password to it

exec xp_cmdshell \\'bcp "SELECT * From Intranet" queryout "\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv" -c -t"," -r"\
" -S ServerName -U"Username" -P"Password" -T\\'
60

Damn, neither of them worked…

I used the second option and that didnt work either.


exec xp_cmdshell \\'bcp "SELECT * From Intranet" queryout "\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv" -c -t"," -r"\
" -S DATASTORE101 -U"******" -P"******" -T\\'