The SQL code below works great in MSSQL but when called via php it only outputs roughly half the results. I have tried placing this in a stored procedure and executing from php but that produces the same result as well.
It should be inserting 150 rows, but I am getting only 77, sometimes 78, but it is always one of those two numbers, which leads me to believe its stopping close to half way every time…
error_reporting(0); //turn off error reporting session_start(); if ($_SESSION['id']) { require('../config.php'); $connectionInfo = array( "Database"=>$database, "UID"=>$user, "PWD"=>$password); $conn = sqlsrv_connect( $host, $connectionInfo); $sql = "INSERT INTO [RP].[dbo].[RP_UniqueOffers] (offertype) Select distinct(offertype) from [RP].[dbo].[Offers] t2 where convert(date,[RedeemedDate]) >= convert(date,getdate()-30) and not exists (Select distinct(offertype) from [RP].[dbo].[RP_UniqueOffers] t1 where t1.OfferType = t2.offertype ) truncate table [RP].[dbo].[RP_Last30days] DECLARE @id INT DECLARE @OfferType NVARCHAR(100) DECLARE @getid CURSOR Declare @counter int Declare @nextDate as Date SET @getid = CURSOR FOR SELECT [RP].[dbo].[RP_UniqueOffers].ID, [RP].[dbo].[RP_UniqueOffers].OfferType FROM [RP].[dbo].[RP_UniqueOffers] OPEN @getid FETCH NEXT FROM @getid INTO @id, @OfferType WHILE @@FETCH_STATUS = 0 BEGIN --print @offertype Set @counter = 1 WHILE (@counter < 31 ) begin Set @nextDate = convert(varchar,getdate() -30 + @counter,101) insert into [RP].[dbo].[Last30Days] ([Date],[OfferType]) Select Convert(varchar,@nextDate,101), @OfferType Set @counter = @counter + 1 END FETCH NEXT FROM @getid INTO @id, @OfferType END CLOSE @getid DEALLOCATE @getid"; $params = array(); $options = array( "Scrollable" => SQLSRV_CURSOR_KEYSET ); $stmt = sqlsrv_query( $conn, $sql , $params, $options ); if ($stmt === False) { echo "Faild to worked!"; } else { // echo "Should have worked... "; } sqlsrv_free_stmt($stmt); } ?>```
Advertisement
Answer
Credit to @SalmanA
I needed to add SET NOCOUNT ON to the top of the script as well as using sqlsrv_prepare and sqlsrv_execute in place of sqlsrv_query.