Different error, same error?
ye same error…
Unable to start T-SQL Debugging. Could not connect to computer 'IP address in here'. Logon Failure: unknown user name or bad password.
Remove the -T (ends up that overrides the -U and -P) - I also removed the \ infront of the ’ at the beginning and the end of the command
exec xp_cmdshell 'bcp "SELECT * From Intranet" queryout "\\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\export\\contractsCSF.csv" -c -t"," -r"\
" -S DATASTORE101 -U"******" -P"******"'
Nope, but I did get an email forwarded onto me as the IT guy is sort of trying too, this is what he said.
The SQL Server and the SQl Server Agent users (SV-CSFDB01-SVR and SV-CSFDB01-SVA) should have write permissions to that datastore101 folder but I’m not sure if that’s ever been tested.
I think your best bet is to simplify it and first get your SSMS script to write out the data to a local path on the SQL server and check that you actually get a proper CSV file, i.e. comma delimited plain text file. If you’re accessing the DB server via SSMS running on your workstation then you’ll need to check how SSMS interprets a path of c:\ emp for example , will it try your workstation where it’s running or get the SQL server to write to it’s c:\ emp.
If you get that then you know your permissions issue is with connecting to the datastore which I can then investigate.
So could still be permission trouble, do you understand what he asking me to do, as I think I change the path to c:\ emp\*.txt and see if it writes something there first.
What you think?
I think he is asking to run the following
exec xp_cmdshell 'bcp "SELECT * From Intranet" queryout "c:\ emp\\contractsCSF.csv" -c -t"," -r"\
" -S DATASTORE101 -U"******" -P"******"'
Or
exec xp_cmdshell 'bcp "SELECT * From Intranet" queryout "c:\ emp\\contractsCSF.csv" -c -t"," -r"\
" -S DATASTORE101 -T'
I’d also try without the -S
exec xp_cmdshell 'bcp "SELECT * From Intranet" queryout "c:\ emp\\contractsCSF.csv" -c -t"," -r"\
" -T'
Ok tried all those and still had the same error, so will pass this onto the hosting company, and see if they can sort the permission issue out.
Thanks for this week again cpradio, have a nice weekend and will more than likey catch up with you on Monday.
Cheers
Morning,
A guy at the hosting company got back to me this morning with below:
Datastore101 is the file server, but from the queries below it looks like you’re trying to connect to it as the MS SQL server (-S DATASTORE101 ). Surely that should be csfdb02 ? Also you’ll need to pass it in a database name to find the Intranet table in.
So then I tried below and it still didnt work:
exec xp_cmdshell 'bcp "SELECT * From Intranet" queryout "c:\ emp\\contractsCSF.csv" -c -t"," -r"\
" -U"***" -P"***"''
He then returned later with this below:
For example, we could dump the contents of the fruit table to a text file with the following command:
bcp inventory.dbo.fruits out “C:\fruit\inventory.txt” -c -T
Obviously you’ll need to append the login info for the SQL server to that command.
For some reason whatever I try I kepe getting the same logging error.
Try this:
exec xp_cmdshell 'bcp "SELECT * From Intranet" queryout "c:\ emp\\contractsCSF.csv" -c -t"," -r"\
" -Scsfdb02 -dDATABASE_NAME -U"***" -P"***"'
Replace DATABASE_NAME with your actual Database Name.
Hi cpradio,
OK I think we have found out the problem, I have just been told that the database server doesnt have internet access, so althought the code is correct it wont work as we can get access to the server.
On looking into it, I came across this on google:
http://www.the-art-of-web.com/php/dataexport/#comments
Does what I think is the problem seem likely no, seeing what ever we have tried we get that error
OK I might be going down the wrong path here, but want to show some effort.
I used the code from that site above and put it together as below:
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");
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);
function cleanData(&$str)
{
if($str == 't') $str = 'TRUE';
if($str == 'f') $str = 'FALSE';
if(preg_match("/^0/", $str) || preg_match("/^\\+?\\d{8,}$/", $str) || preg_match("/^\\d{4}.\\d{1,2}.\\d{1,2}/", $str)) {
$str = "'$str";
}
if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
$str = mb_convert_encoding($str, 'UTF-16LE', 'UTF-8');
}
// filename for download
$filename = "website_data_" . date('Ymd') . ".csv";
header("Content-Disposition: attachment; filename=\\"$filename\\"");
header("Content-Type: text/csv; charset=UTF-16LE");
$out = fopen("php://output", 'w');
$flag = false;
$result = $query or die('Query failed!');
while(false !== ($row = $result)) {
if(!$flag) {
// display field/column names as first row
fputcsv($out, array_keys($row), ',', '"');
$flag = true;
}
array_walk($row, 'cleanData');
fputcsv($out, array_values($row), ',', '"');
}
fclose($out);
exit;
The good news is I starts to download and create a csv file, which is great, but then when it attempts to load an error is created saying that:
The file contains more than 1,048,576 rows or 16,384 columns. To fix this problem, open the source file in a text editor such as Microsoft Office Word. Save the source file as several smaller files that conform to this row and column limit, and then open the smaller files in Microsoft Office Excel. If the source data cannot be opened in a text editor, try importing the data into Microsoft Office Access, and then exporting subsets of the data from Access to Excel.
The area that you are trying to paste the tab-delineated data into is too small. To fix this problem, select an area in the worksheet large enough to accommodate every delimited item.
Over 1 million rows, that cant be as all together there only just over 2k contracts in the database.
The two lines below where orginally like this:
$result = pg_query("SELECT * FROM table ORDER BY field") or die('Query failed!');
while(false !== ($row = pg_fetch_assoc($result))) {
But pg_Query and pg_fetch_assoc kept coming back with below:
atal error: Call to undefined function pg_query() in \\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\download_2.php on line 44
Am I getting anywhere with this you think, as it feels like it, but I suppose it needs a bit of tinkering, especially trying to create a 90Mb file for no reason.
You need to replace the pg_* with the equivalent sqlsrvr_* functions.
It seems I’m getting somewhere now, and thanks for that last post and sorry about that, I should have seen that.
This is where I’m at at the moment:
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");
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);
// filename for download
$filename = "website_data_" . date('Ymd') . ".csv";
header("Content-Disposition: attachment; filename=\\"$filename\\"");
header("Content-Type: text/csv; charset=UTF-16LE");
$out = fopen("php://output", 'w');
$flag = false;
$result = sqlsrv_query($conn, $query) or die('Query failed!');
while(false !== ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))) {
if(!$flag) {
// display field/column names as first row
fputcsv($out, array_keys($row), ',', '"');
$flag = true;
}
fputcsv($out, array_values($row), ',', '"');
}
fclose($out);
exit;
It seems that one fatal error Im getting is stopping it, but I expect once sorted there will be something else, but this is what I’m getting which I understand but dont know how to sort this out, but basically I have dates in the database and it seems they need to be converted.
Here is the error:
Catchable fatal error: Object of class DateTime could not be converted to string in \\\\DATASTORE101\\CSFWEBDATA$\\checksafetyfirst\\en\\csfintranet\\download_2.php on line 53
Line 53 is this line:
fputcsv($out, array_values($row), ',', '"');
I dont know where to put the conversion and also how to code it out. There are five date entries.
I have done it before:
date_format($data[‘Contract_Start’], ‘d/m/y’)
date_format($data[‘Contract_End’], ‘d/m/y’)
date_format($data[‘Renewal_Date’], ‘d/m/y’)
date_format($data[‘date_Created’], ‘d/m/Y’)
date_format($data[‘date_Modified’], ‘d/m/Y’)
I’m guessing the $data bit changes to $row
You will want to use instanceof
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");
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);
// filename for download
$filename = "website_data_" . date('Ymd') . ".csv";
header("Content-Disposition: attachment; filename=\\"$filename\\"");
header("Content-Type: text/csv; charset=UTF-16LE");
$out = fopen("php://output", 'w');
$flag = false;
$result = sqlsrv_query($conn, $query) or die('Query failed!');
while(false !== ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC))) {
if(!$flag) {
// display field/column names as first row
fputcsv($out, array_keys($row), ',', '"');
$flag = true;
}
foreach ($row as $key => $value)
{
if ($value instanceof DateTime)
{
$row[$key] = date_format($value, 'd/m/y');
}
}
fputcsv($out, array_values($row), ',', '"');
}
fclose($out);
exit;
Awsome, it works!
Had to do swap the $query = str_replace values around a little, but I eventually got there, and its exporting correctly to a .csv file, which I wanted to let you know, and say a big, big thank you for that.
There just one more thing I think, and again I have had a think but not sure how to resolve it.
It the Annual_Contract_Value field in some cases there are
,
for a (,) and
.
for a (.)
I think it needs to happen where you answered the datetime issue, but dont have the code for it.
Basically when it outputs the value, and value with those codes in need to be changed to the proper value.
Will go through it now with a fine tooth comb to see if there anything else, which I’m sure there is, but its working as such now which is great.
Ahh, got it lol.
I added
$row = str_replace(",", ",", $row);
And that worked, so will do the other
A better option would likely be using html_entity_decode() http://php.net/manual/en/function.html-entity-decode.php