I've written a query that takes a while to run because it's retrieving about 10 million records from the SQL table. Eventually, I'll put putting infromation for mssql into mysql. The script will begin to run and then end prematurely without completing the script. I'm assuming this is because it's timing out. The query does work. I've printed it to my screen, copied it, and pasted it in the query window for mssql and I get results, so I don't think it's a problem with that. I've set max_execution_time and max_input_time to 0 in my php.ini file and set Remote query timeout to 0 in the server properties area in SQL Server Management Studio. I also set the memory limit to -1, and mssql settings in my php.ini file is pasted below. I've restarted apache and everything. But, it continues to end prematurely. I've checked the logs and don't see any additional information...

Any idea how to fix this?

//Settings from my php file
; Allow or prevent persistent links.
mssql.allow_persistent = On

; Maximum number of persistent links.  -1 means no limit.
mssql.max_persistent = -1

; Maximum number of links (persistent+non persistent).  -1 means no limit.
mssql.max_links = -1

; Minimum error severity to display.
mssql.min_error_severity = 10

; Minimum message severity to display.
mssql.min_message_severity = 10

; Compatability mode with old versions of PHP 3.0.
mssql.compatability_mode = Off

; Connect timeout
mssql.connect_timeout = 0

; Query timeout
mssql.timeout = 0

; Valid range 0 - 2147483647.  Default = 4096.
;mssql.textlimit = 4096

; Valid range 0 - 2147483647.  Default = 4096.
;mssql.textsize = 4096

; Limits the number of records in each batch.  0 = all records in one batch.
mssql.batchsize = 0

; Specify how datetime and datetim4 columns are returned
; On => Returns data converted to SQL server settings
; Off => Returns values as YYYY-MM-DD hh:mm:ss
;mssql.datetimeconvert = On

; Use NT authentication when connecting to the server
mssql.secure_connection = Off

    Two questions:

    How long does it take to run in the query window?

    What are you doing with the ten million rows after you select them?

    Returning all ten million rows to php and processing them there is asking a lot. You can do a lot of processing in the database to process them before sending the results back to the web server, stored procedures, local application server, etc. If all you need to do is transfer the data to MySQL you can split it up and transfer 100K at a time.

      7 days later

      Use exec to run the database PHP script in a DOS command line.

      I am getting this off from my head so the syntax may not be accurate, but I hope you get the idea.

      exec('cmd /c php.exe query.php');

      I included "/c" so that it returns control back to your main PHP script immediately, therefore no time-out.

        Write a Reply...