SitePoint Sponsor

User Tag List

Page 2 of 4 FirstFirst 1234 LastLast
Results 26 to 50 of 76
  1. #26
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi cpradio,

    Got so much going on at the mo, thought i best come back to this one.

    I understand I need to use:

    ec xp_cmdshell 'bcp "your query goes here" queryout "<path to web server>\name of file.csv" -T -c -t","'

    and will have to work out how, but do I included it in the download.php file that the user goes to when they click the 'Export' button.

    As below, or do I use it on its own in download.php

    PHP Code:
    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'); 
    echo 
    readfile('contractsCSF.csv'); //Reading the contents of the file
    ec xp_cmdshell 'bcp "your query goes here" queryout "<path to web server>\name of file.csv" -T -c -t","' 

  2. #27
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Yes, you would.

    However, you have a few lines in the wrong order.
    PHP Code:
    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
    // Build the Query you want to Export to CSV (may need to send the 
    // 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 
    Same way we build the query in http://www.sitepoint.com/forums/show...mp-MSSQL/page3
    So you will need to pass the appropriate variables to the download page and use them to build your $sqlQueryToExport

  3. #28
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Right, this is going to be another step up by the looks, but thanks again cpradio, probably keep me busy for a year or two!

    Cheers

  4. #29
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    OK Im back on this now, as didnt properly finish off the other stuff as you know.

    I am starting off by trying to work out how to pass the query over to download.php.

    This is where I'm at at this point, but am workign on things:

    Code:
    <?
    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
    session_start();
    
    require_once('config.php');
    $connectionInfo = array("Database"=>$databaseName, "UID"=>$username, "PWD"=>$password);
    $conn = sqlsrv_connect($hostName , $connectionInfo);
    
    
    // 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  
    
    ?>
    It seems I'm having trouble at the start when I click the Export button. It all kicks in so to speak and an IE pop up error box appears and isnt allowing me to continue.

    Internet Explorer cannt download download.php.

  5. #30
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    I would write the query to a session variable such as $_SESSION['currentQuery'] = $query; Then in your download page read the query using $_SESSION['currentQuery']

    And so you don't make a common mistake, make sure session_start(); is at the top of all scripts involved with assigning and reading your $_SESSION variables.

  6. #31
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah ye thank you. I echoed it out and realised that there are bits in there that are not needed to create the .csv file:

    Code:
    WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%') ) select * from LIMIT WHERE {limit clause} order by ID DESC
    Such as WITH LIMIT AS( and RowNumber and all, So should I create another query just for export minus the add ons, so in effect trimming it down.

    No just thinking I cant do that, will have to think this through.

  7. #32
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Well you have two options there
    1) Try to pull out the data you need from that query.
    2) Create a 3rd variable for each query you have and store that in the $_SESSION variable

    The biggest challenge you really have is removing ROW_NUMBER and the WHERE {limit clause} with your existing query.

    So doing the following "might" work
    PHP Code:
    $query $_SESSION['currentQuery'];
    $query str_replace(array(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'"'WHERE {limit clause}'), ''$query);
    var_dump($query); 

  8. #33
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ye that did it, well nearly:

    Before:
    PHP Code:

    WITH LIMIT 
    AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%24%' or Contract_Status like '%24%' or Contract_Start like '%24%' or Contract_End like '%24%' or Contract_Length like '%24%' or Renewal_Date like '%24%' or Site_Name like '%24%' or Street like '%24%' or City like '%24%' or Postcode like '%24%' or Country like '%24%' or Region like '%24%' or Group_Member like '%24%' or Group_Name like '%24%' or No_of_Rooms like '%24%' or Type_of_Establishment like '%24%' or CSF like '%24%' or Food_Safety like '%24%' or Swimming_Pool_Safety like '%24%' or Legionella_Safety like '%24%' or Fire_Safety like '%24%' or Hurricane like '%24%' or Green like '%24%' or Guest like '%24%' or Room_Safety like '%24%' or Quality_Safety like '%24%' or Supply_Safety like '%24%' or Tourcheck like '%24%' or No_of_Modules like '%24%' or Currency_of_Invoice like '%24%' or Annual_Contract_Value like '%24%' or No_of_audits_per_annum like '%24%' or No_of_visits_per_annum like '%24%' or Seasonal_or_Full like '%24%' or Labs like '%24%' or Month_Opens like '%24%' or Month_Closes like '%24%' or Invoicing_Profile like '%24%') ) select from LIMIT WHERE {limit clauseorder by ID DESC 
    After:
    PHP Code:
    SELECT FROM Intranet WHERE (Contract_Number like '%24%' or Contract_Status like '%24%' or Contract_Start like '%24%' or Contract_End like '%24%' or Contract_Length like '%24%' or Renewal_Date like '%24%' or Site_Name like '%24%' or Street like '%24%' or City like '%24%' or Postcode like '%24%' or Country like '%24%' or Region like '%24%' or Group_Member like '%24%' or Group_Name like '%24%' or No_of_Rooms like '%24%' or Type_of_Establishment like '%24%' or CSF like '%24%' or Food_Safety like '%24%' or Swimming_Pool_Safety like '%24%' or Legionella_Safety like '%24%' or Fire_Safety like '%24%' or Hurricane like '%24%' or Green like '%24%' or Guest like '%24%' or Room_Safety like '%24%' or Quality_Safety like '%24%' or Supply_Safety like '%24%' or Tourcheck like '%24%' or No_of_Modules like '%24%' or Currency_of_Invoice like '%24%' or Annual_Contract_Value like '%24%' or No_of_audits_per_annum like '%24%' or No_of_visits_per_annum like '%24%' or Seasonal_or_Full like '%24%' or Labs like '%24%' or Month_Opens like '%24%' or Month_Closes like '%24%' or Invoicing_Profile like '%24%') ) order by ID DESC 
    Using:
    PHP Code:
    $query str_replace(array("select * from LIMIT order by ID DESC"", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'""WHERE {limit clause}""WITH LIMIT AS(","select * from LIMIT"), ''$query); 
    If you notice at the end of the query you have a double )), the last ')' I've tried to remove by adding it as below and it wont allow it, and makes the rest of it appear.

    PHP Code:
    $query str_replace(array("select * from LIMIT order by ID DESC"", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'""WHERE {limit clause}""WITH LIMIT AS(",") select * from LIMIT"), ''$query); 

  9. #34
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Hmm... I didn't end up with a double ) ) problem... I ended up with
    Code:
    string(1304) "WITH LIMIT AS( SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%') ) select * from LIMIT  order by ID DESC"
    Using
    PHP Code:
    <?php
        session_start
    ();
        
    //$query = $_SESSION['currentQuery'];
        
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%') ) select * from LIMIT WHERE {limit clause} order by ID DESC";
        
    $query str_replace(array(", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'"'WHERE {limit clause}'), ''$query);
        
    var_dump($query);
    ?>
    Yes, you could argume it is using a CTE unnecessarily, but I think it would work. Otherwise, you could use:
    PHP Code:
    <?php
        session_start
    ();
        
    //$query = $_SESSION['currentQuery'];
        
    $query "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%') ) select * from LIMIT WHERE {limit clause} order by ID DESC";
        
    $query str_replace(array('WITH LIMIT AS( '", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'"') ) select * from LIMIT WHERE {limit clause}'), ''$query);
        
    var_dump($query);
    ?>
    Which gives you:
    Code:
    string(1265) "SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%' order by ID DESC"

  10. #35
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is typical, that I'm getting different from you.

    This is the query:

    Code:
    string(1310) "SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%' )) select * from LIMIT WHERE {limit clause} order by ID DESC"
    Using this:

    PHP Code:
    $query str_replace(array('WITH LIMIT AS( '", ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber'"')) select * from LIMIT WHERE {limit clause}'), ''$query);
    var_dump($query); 
    I get this:
    Code:
    string(1310) "SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%' )) select * from LIMIT WHERE {limit clause} order by ID DESC"
    It wont let ')) select * from LIMIT WHERE {limit clause}' be removed.

  11. #36
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    What version of PHP are you running? Maybe it has something to do with that.

    Does doing this provide the same end result:
    PHP Code:
    $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); 
    var_dump($query); 
    Edit:

    fixed typo in code

  12. #37
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes by the look of it it does.

    I took the )) out and it allowed the rest of the code behind to go, its the double )) thats causing the problem.

  13. #38
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Ah, I found the issue, it seems the formatting is removing the space between ) and ) in my last str_replace, make sure there is a space between the parenthesizes

  14. #39
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    LOL, ahhhhh.

    Nope its not budging...

    PHP Code:
    $query str_replace(') ) select * from LIMIT WHERE {limit clause}'''$query); 

  15. #40
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I changed it to this and it seems to have worked.

    PHP Code:
    $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); 
    Code:
    string(1269) "SELECT * FROM Intranet WHERE (Contract_Number like '%Active%' or Contract_Status like '%Active%' or Contract_Start like '%Active%' or Contract_End like '%Active%' or Contract_Length like '%Active%' or Renewal_Date like '%Active%' or Site_Name like '%Active%' or Street like '%Active%' or City like '%Active%' or Postcode like '%Active%' or Country like '%Active%' or Region like '%Active%' or Group_Member like '%Active%' or Group_Name like '%Active%' or No_of_Rooms like '%Active%' or Type_of_Establishment like '%Active%' or CSF like '%Active%' or Food_Safety like '%Active%' or Swimming_Pool_Safety like '%Active%' or Legionella_Safety like '%Active%' or Fire_Safety like '%Active%' or Hurricane like '%Active%' or Green like '%Active%' or Guest like '%Active%' or Room_Safety like '%Active%' or Quality_Safety like '%Active%' or Supply_Safety like '%Active%' or Tourcheck like '%Active%' or No_of_Modules like '%Active%' or Currency_of_Invoice like '%Active%' or Annual_Contract_Value like '%Active%' or No_of_audits_per_annum like '%Active%' or No_of_visits_per_annum like '%Active%' or Seasonal_or_Full like '%Active%' or Labs like '%Active%' or Month_Opens like '%Active%' or Month_Closes like '%Active%' or Invoicing_Profile like '%Active%' order by ID DESC"
    There no theory behind it, just tried it!

  16. #41
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Yeah, that would work too Though, keep in mind if you have ORs and AND conditions properly separated with (), your last replacement will cause issues.

  17. #42
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The next stage on from here is it to create the table from the data as below:

    Code:
    <?
    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.

  18. #43
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  19. #44
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Morning cpradio,

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

    PHP Code:
    $stmt sqlsrv_query($conn$query);
    while(
    $data sqlsrv_fetch_array($stmtSQLSRV_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:

    PHP Code:
    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.

    PHP Code:
    //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.

  20. #45
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    <?
    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.

  21. #46
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    '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.

  22. #47
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  23. #48
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:

    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.

  24. #49
    SitePoint Guru
    Join Date
    Feb 2006
    Location
    Chepstow, South Wales
    Posts
    907
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have got in touch with the hosts and the path they gave is below:

    Code:
    '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.

  25. #50
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,122
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by multichild View Post
    Have got in touch with the hosts and the path they gave is below:

    Code:
    '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:



    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.
    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)
    Code:
    \\DATASTORE101\CSFWEBDATA$\checksafetyfirst\en\csfintranet\
    With that thought, the first thing I would try is
    Code:
    '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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •