Solved

SQL 2012 Intermittance

Posted on 2014-03-12
97
102 Views
Last Modified: 2014-11-29
I have PHP web page that calls a stored procedure in SQL 2012 as UserA. The procedure does not complete.

When I run the same stored procedure from SSMS with same values as web as UserA the Stored Procedure completes normally.

As I move test and test the PHP calls to stored procedure completes intermittently.

what should I do.
0
Comment
Question by:Leo Torres
  • 56
  • 24
  • 17
97 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39922990
>The procedure does not complete.
can you please clarify how exactly you can see that? what is the stored procedure doing?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39922991
if the task does not "complete" when run from php, but works from SSMS, the difference is in what the procedure is doing vs the permissions of the calling user. check which logins are being used in both cases.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39923166
I can provide a bit more info on this as I recommended the post here.

The stored procedure calls several other stored procedures to achieve the required result. In an attempt to debug the process insert statements were placed at various points in each procedure to dump trace entries into a table.

The process completes successfully up until calling the last stored procedure at which point it simply terminates.

Here is a mockup of the process
create stored procedure createUser
AS
-- Does some work successfully
INSERT into tracefile (msg) VALUES ('before last proc call')

exec last_stored_procedure @param1=1000, @param2=@email -- AAA

INSERT INTO tracefile (msg) VALUES ('after last proc call') -- DOES NOT GET HERE

Open in new window

create stored procedure as last_stored_procedure
@param1 varchar(255)
@param2 varchar(max)
AS
INSERT INTO tracefile (msg) VALUES ('last_procedure_called') -- OR HERE

Open in new window

The above fails even when there is just the one insert statement.

When the createUser process is called from SSMS it executes to the end without issue or error.

When the query dumped from the script is copied and pasted to SSMS it runs without fail.

When the call is made from code it gets as far as AAA above and stops - but reports that the procedure completed successfuly.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923200
what is the execution time of the whole procedure?
what is the commandtimeout you set when you call the stored procedure?

if the asp script times out before the procedure completes, or the (default) command timeout (of 30 seconds) elapses before the proc complete, the transation shall be "stopped". which would issue a "ROLLBACK" if the whole stuff is started with a BEGIN TRANSATION. otherwise, indeed all that is started is committed implicitly.

please clarify
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39923382
what is the execution time of the whole procedure?
Max 1 sec
what is the commandtimeout you set when you call the stored procedure?
Not sure - but can't see that it would come into play - execution is almost instantaneous

Not sure if process is inside a transaction but Rollback not firing as the inserts to the trace file remain.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923406
does the trace entry from the last procedure also "fail" to be done?
and the trace entry that should happen after that procedure also "fails"?

anything in the sql server error log?
anything in the event log of the sql server box around those times?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39923587
does the trace entry from the last procedure also "fail" to be done?
Everything from AAA (refer code post) onwards fails to execute. Almost as if the procedure is not being called and the script is terminating at that point.

Error Log / Event Log - we will need to wait for torres321 to come back on line
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923596
could be if for example the disk is full, and/or database size increase takes too long ...
let's wait for event/error logs ...
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39923685
could be if for example the disk is full, and/or database size increase takes too long
Possibly but wouldn't explain why it works 100% of the time when run from SSMS.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39923802
Thanks Julain, for chiming in.

The user used by the web and the user used in SSMS were the same. So security wise I believe we are OK.

I had Open and close Transactions, but what would happen is the page would finish and I would remain with an open transaction in the back end that I had to Kill.

At its current state I have removed all Open and closed transactions just to try and get it to work with out it. I was going to add back once this issue was resolved.



Angel to answer your Question the Profiler does not record error but in the middle of the procedure execution it does a "exec sp_reset_connection" and thats it.

This was posted on  my original question
http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28372112.html



My issue is that the webpage call to procedure connection gets reset by  command 'exec sp_reset_connection'.

To be specific Profiler3.txt is done from web page
Profiler4.txt done directly from SQL

The Webpage procedure call gets cut off before all the transactions can finish there are 2 more inserts that need to get done and that does not happen. Granted the primary procedure that the page calls a second procedure that ultimately calls a 3rd procedure that does the insert.

When I say I did this from SQL Server I did it using the same user account the webpage uses to enter data

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39923816
As Far as the Disk space Highly unlikely I have close to 500 Gigs of space available. and the data entered by all the procedures is about 40 lines of code where 21 lines are due to log tracking to see where procedure is failing.  

Angel, you said you want to see errors in event viewer correct? if that is the case I can provide but it will have to wait till at least 6pm EST as I don't have access to Computer right now. Please confirm that is the log you speak of in case you need another log. I want to get right log to not waste time.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39923852
I think with the sp_reset_connection we do have the culprit in the IIS/data pooling.
I presume your data pool size is too small ...
here what I found in some external web site:
    What does sp_reset_connection do?

    Data access API's layers like ODBC, OLE-DB and System.Data.SqlClient all call the (internal) stored procedure sp_reset_connection when re-using a connection from a connection pool. It does this to reset the state of the connection before it gets re-used, however nowhere is documented what things get reset. This article tries to document the parts of the connection that get reset.

    sp_reset_connection resets the following aspects of a connection:

        All error states and numbers (like @@error)

        Stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query

        Waits for any outstanding I/O operations that is outstanding

        Frees any held buffers on the server by the connection

        Unlocks any buffer resources that are used by the connection

        Releases all allocated memory owned by the connection

        Clears any work or temporary tables that are created by the connection

        Kills all global cursors owned by the connection

        Closes any open SQL-XML handles that are open

        Deletes any open SQL-XML related work tables

        Closes all system tables

        Closes all user tables

        Drops all temporary objects

        Aborts open transactions

        Defects from a distributed transaction when enlisted

        Decrements the reference count for users in current database which releases shared database locks

        Frees acquired locks

        Releases any acquired handles

        Resets all SET options to the default values

        Resets the @@rowcount value

        Resets the @@identity value

        Resets any session level trace options using dbcc traceon()

    sp_reset_connection will NOT reset:

        Security context, which is why connection pooling matches connections based on the exact connection string

        Application roles entered using sp_setapprole, since application roles can not be reverted

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39924018
OK looks like its clear that you need the Event Viewer logs for when this ran I will provide soon as I can.

From what you gathered then on your last post. What is your recommendation for IIS/data pooling? And how can we test this?
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39925045
I ran this query serverl times this morning between the hours of 2am to 430am EST time. I have created a log for that time frame and there is nothing interesting to so. No errors. Please advise on next steps.
ErrorLog-2014-03-12.csv
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39925727
there are some issues that should be addressed, but indeed apart from this question.

I think I will need the declaration of  [dbo].[sp_CreateUser] ...
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39926218
Procedure sent
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39928400
Any updates?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928682
first thing I see is this, continuing to look ...
IF @@TRANCOUNT > 0
			BEGIN
				Print 'RollBack'
				Insert into dbo.InsertLog([Description])
				Values ('Trouble Transaction ROLLBACK Create User') 
				ROLLBACK TRANSACTION 
			END 

Open in new window

should be:
IF @@TRANCOUNT > 0
			BEGIN
				Print 'RollBack'
				ROLLBACK TRANSACTION 
				Insert into dbo.InsertLog([Description])
				Values ('Trouble Transaction ROLLBACK Create User') 
			END 

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928712
please change this for the logging:
Insert into dbo.InsertLog([Description])
Values ('Insert Done and Commited')  

Open in new window

into
Insert into dbo.InsertLog([Description])
Values ('Insert Done and Commited (' + cast(isnull( @U_ID , 0 )  as varchar(100)) + ')' )  

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39928725
OK will do.. think this would make a diff?

Doing it now
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928726
not sure, I think the real reason behind is the colums splitter creating a temp table ...
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39928743
Yes, that could be an issue

Need that process or something similar. Again it works from SSMS but issues from Web.

would the issue cause the procedure to not even begin. I would expect it to stop just before ColumnSpliter but that not the case in image below. I have seen it end there when my Dev Guy made adjustments to PHP Code.

Log
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928745
so, let's avoid that temp table, and use a dynamic sql with table variable instead, which has also the advantage that it can split with more than 10 fields ...
CREATE procedure [dbo].[ColumnSpliter]
@UserID as int
,@cols nvarchar(max)
,@values nvarchar(max)
AS

declare @col_cnt int 
select @col_cnt = count(*) from dbo.MultiRow(@cols, ',')

declare @sql nvarchar(max)
set @sql = 'select '

declare @l int
declare @col_name varchar(100)

set @l = 1
while @l <= @col_cnt
begin
  select @col_name = one from dbo.MultiRow(@cols, ',') where id = @l
  set @sql = @sql + case when @l = 1 then '' else ',' end 
	+ ' max(case when cn = ' + CAST(@l as varchar(10)) + ' then value end) [' + @col_name + ']'

  set @l = @l + 1
end

set @sql = @sql  +' 
from (
select sq.*
 , ROW_NUMBER() over (partition by cn order by r ) rn
from (
select v.id r, one value
   ,((v.id - 1 ) % @col_cnt) + 1 cn
  from dbo.MultiRow(@valuelist, '','') v
) sq
) data 
group by rn'
print @sql 
exec sp_executesql @sql , N'@col_cnt int, @valuelist varchar(max)', @col_cnt, @values

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39928749
will do.. Let me test
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39928758
I went ahead and committed your procedure into the DB and ran the insert from web. I got the same log as the one pasted above.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39928760
If you want or need access to sql server let me know.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928791
can you please double-check which database compatibility level the 2 databases (eCRM and SportsLottery) are ?

can you try to create the procedure ColumnSpliter in the eCRM database?

can you try to comment out the following 3 lines:

SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_WARNINGS ON
SET ANSI_PADDING ON
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39928830
Those 3 lines have been commented out

Re-ran from web same as image log posted above

Compatibility Level: SQL Server 2012(110)

for both Servers.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39928871
I am running out of ideas ....
but I "guess" that somewhere a commit is missing, just that I cannot find it ...
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39929049
if a commit is missing won't it fail from the ssms window as well?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39929091
the SSMS window will see "it's own transaction", while when running from web, if you don't do the final commit, other users won't see the data until committed, and the transaction would implicitly be rolled back if the session is ended without doing the commit.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39929274
I have no problem removing that Commit Transaction just to see if it works. I will do so retest and let you know. at work now till 5pm EST so it will be a while but I will remove and test and post right away.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39929413
> removing that Commit Transaction
if you remove the commit, you also need to remove the begin transaction...
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39930973
Yes agreed when I said remove Commit I meant begin and end Transaction.

Well I did remove the begin and end Tran. Ran the process and I still got the 6 Rows in the log listed above.  So its the same.

What about the IIS/application pool theory you had? At this point I don't know were to go.
I have no problem walking you thru process on the server with Profiler. Or I can export table to CSV or something. I have 2 runs 1 with call from web and 1 from SSMS execution.

Please let me know how I can facilitate your research.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39934225
Any new suggestions?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39934818
I have no further ideas, unfortunately.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39935337
Would and output of SQL Profiler. Help you in any way?

Could it be a problem with the way PHP makes the call?

This question has the php code used to make Db call
http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28372112.html#a39885185

I think my friend was telling me there is an issue with the way the email is being passed in the page that be the issue.

I guess if this leads to no more ideas where do I go next this has been the best place for me to get answers.

Thank you both very much for your time and patience with me and my project.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39936091
"Could it be a problem with the way PHP makes the call? "
What about setting up a dummy script in another scripting language

.asp
VBScript

And seeing if you have the same problem?

.asp works through the web server vbscript can go directly to the database - might shed some more light.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39937166
Sounds great, except I am not a php or asp Writer. I can do some VBScript. You want me to create a script to insert these values into the DB correct? IF so I will work on that the next few nights, and test soon as possible. Thanks Julian for providing another avenue.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39941436
Gentlemen, I have new findings

I believe my original Conclusion was correct its a problem or issue with the PHP side.

The VBScript code below completes the entire transaction generating an email to my account. I have also included the log as well below.  Guys were do we go from here?

Option Explicit

dim sServer, sConn, oConn, sDatabaseName, sUser, sPassword
sDatabaseName="eCRM"
sServer="localhost"
sUser="UserX"
sPassword="XXXXXXXXXX"
sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=" & sDatabaseName
Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, sUser, sPassword

Dim userName, _
	password, _
	emailAddress, _
	firstName, _
	lastName, _
	country, _
	address1, _
	address2, _
	city, _
	stateProvince, _
	postalCode, _
	refferalID, _
	U_Question1ID, _
	U_Answer1, _
	U_Question2ID, _
	U_Answer2, _
	U_Question3, _
	U_Answer3, _
	IPAddress 
	
Dim sqlcmd	



	userName = "mkoluvock"
	password = "test123"
	emailAddress = "ltorres321@gmail.com"
	firstName = "Micheal"
	lastName = "mkoluvock"
	country = "US"
	address1 = "733 E 24 ST"
	address2 = ""
	city = "Hialeah"
	stateProvince = "FL"
	postalCode = "33013"
	refferalID = ""
	U_Question1ID = "2"
	U_Answer1 = "dsgg"
	U_Question2ID = "1"
	U_Answer2 = "hthtr"
	U_Question3 = "Color"
	U_Answer3 = "green"
	IPAddress = "166.99.2.037"


sqlcmd = "Exec [dbo].[sp_CreateUser] " &_
					  " @userName = '" & userName &_
					  "' ,@password = '" & password &_
					  "' ,@emailAddress = '" & emailAddress &_
					  "' ,@firstName = '" & firstName &_
					  "' ,@lastName = '" & lastName &_
					  "' ,@country = '" & country &_
					  "' ,@address1 = '" & address1 &_
					  "' ,@address2 = '" & address2 &_
					  "' ,@city = '" & city &_
					  "' ,@stateProvince = '" & stateProvince &_
					  "' ,@postalCode = '" & postalCode &_
					  "' ,@refferalID =  '" & refferalID &_
					  "' ,@U_Question1ID = '" & U_Question1ID &_
					  "' ,@U_Answer1 = '" & U_Answer1 &_
					  "' ,@U_Question2ID = '" & U_Question2ID &_
					  "' ,@U_Answer2 = '" & U_Answer2 &_
					  "' ,@U_Question3 = '" & U_Question3 &_
					  "' ,@U_Answer3 =  '" & U_Answer3 &_
					  "' ,@IPAddress =  '" & IPAddress & "'" 

Wscript.Echo sqlcmd

oConn.Execute sqlcmd
'oConn.Execute "exec sp_help"
WScript.Echo "executed"
oConn.Close
Set oConn = Nothing  



'ALTER procedure [dbo].[sp_CreateUser]
'   @userName varchar(255),
'   @password varchar(255),
'   @emailAddress nvarchar(255),
'   @firstName nvarchar(100),
'   @lastName nvarchar(100),
'   @country nvarchar(100),
'   @address1 nvarchar(100),
'   @address2 nvarchar(100),
'   @city nvarchar(100),
'   @stateProvince nvarchar(100),
'   @postalCode varchar(25),
'   @refferalID int  = NULL,
'   @U_Question1ID nvarchar(100),
'   @U_Answer1 nvarchar(100),
'   @U_Question2ID nvarchar(100),
'   @U_Answer2 nvarchar(100),
'   @U_Question3 nvarchar(100),
'   @U_Answer3 nvarchar(100),
'   @IPAddress varchar(100)

Open in new window



Log complete
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39941438
12AM here now will try to wake up early to see if you guys have posted.. Thanks!
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39941498
I am going to port the above code to a PHP script - lets see what that tells us.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39941593
See what this does
<?php
$sDatabaseName="eCRM";
$sServer="localhost";
$sUser="UserX";
$sPassword="XXXXXXXXXX";
$sConn="Driver={SQL Server Native Client 10.0};Server=$sServer;Database=$sDatabaseName;";
$oConn = odbc_connect($sConn, $sUser, $sPassword, SQL_CUR_USE_ODBC);
if (!$oConn) {
	die("connection failed");
}

$userName="dmkoluvock";
$password="dtest123";
$emailAddress="dltorres321@gmail.com";
$firstName="Micheal";
$lastName="mkoluvock";
$country="US";
$address1="733 E 24 ST";
$address2="";
$city="Hialeah";
$stateProvince="FL";
$postalCode="33013";
$refferalID="";
$U_Question1ID="2";
$U_Answer1="dsgg";
$U_Question2ID="1";
$U_Answer2="hthtr";
$U_Question3="Color";
$U_Answer3="green";
$IPAddress="166.99.2.037";


$sqlcmd = "Exec [dbo].[sp_CreateUser] " .
	  " @userName = '" . $userName .
	  "' ,@password = '" . $password .
	  "' ,@emailAddress = '" . $emailAddress .
	  "' ,@firstName = '" . $firstName .
	  "' ,@lastName = '" . $lastName .
	  "' ,@country = '" . $country .
	  "' ,@address1 = '" . $address1 .
	  "' ,@address2 = '" . $address2 .
	  "' ,@city = '" . $city .
	  "' ,@stateProvince = '" . $stateProvince .
	  "' ,@postalCode = '" . $postalCode .
	  "' ,@refferalID =  '" . $refferalID .
	  "' ,@U_Question1ID = '" . $U_Question1ID .
	  "' ,@U_Answer1 = '" . $U_Answer1 .
	  "' ,@U_Question2ID = '" . $U_Question2ID .
	  "' ,@U_Answer2 = '" . $U_Answer2 .
	  "' ,@U_Question3 = '" . $U_Question3 .
	  "' ,@U_Answer3 =  '" . $U_Answer3 .
	  "' ,@IPAddress =  '" . $IPAddress . "'";

echo $sqlcmd;
odbc_exec($oConn, $sqlcmd);
echo "executed";
?>

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39942035
Oh, This is PHP how do I run this code. I ran the VBScript from the command Line.

Sorry I just dont know any PHP.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39942044
Two ways

1. You can put it in your web root and call it with your web browser
2. You can run it on the command line like so

php name_off_ile.php

If php.exe is not in your path you will need to add it.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39942071
This would be my centOS box? Then that's where I have php installed.

Ok let me see if I can get it to run.. thanks
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39942120
Ok - so you ran the asp script on the windows box (obviously) that's another parameter in the equation ...
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39942130
yes, one sec getting ready to run on centos box
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 8

Author Comment

by:Leo Torres
ID: 39942136
File error
err
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39942143
Ok wait I did miss a semi-colon when I updated file.
sent you a file
err2
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39942281
You also have a missing '$' in front of the sUser and sPassword values.

Should be
$sUser="UserX";
$sPassword="XXXXXXXXXX";

Open in new window

Not
sUser="UserX";
sPassword="XXXXXXXXXX";

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39942543
OK shoot will try to run now else in the after noon here
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39942589
Still no Cigar..

err3
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39942622
Just so you know I am using the codeIgniter Framework. What version I am not sure I really didn't set up the CentOS box.

Seems like you may be looking or trying to reference something that may not exists in my current framework.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39942898
Not thinking (*) - odbc is not available on linux - sorry - do you have PHP installed on the Windows Box?

Can you save this to a file on your CentOS box and call it from your browser
<?php
phpinfo();
?>

Open in new window

And post the results
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39942933
No I do not have PHP on windows box.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39942941
Cant access box right now will do tonight

Where should I even put this file anyway. So I can call it from a web browser?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39943069
Anywhere in the web-root - I usually call the file pi.php (but you can call it whatever you want)
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39943182
I placed it in this directory. After I placed it there I restarted Apache

COnfig
cnfig2
page result
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39943437
I cant get the info from web to work, but i was able to do it from the command line and out put to text file.

Just ran this from command line.
echo “<?php phpinfo(); ?>” | php > phpinfo.txt

I feel more comfortable with the command line. Sorry the text version does not look as sexy.
phpinfo.txt
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39945559
Updates?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39949908
I am wondering if this has to do with a mess up by MS whereby the MSSQL drivers are now split between those that run on Windows machines and the rest.

PHP no longer natively supports MSSQL - you have to get the drivers separately - as you are on CentOS - that might be at the core of what is going wrong here.

The acid test is (if possible) to instal PHP on your windows machine and test the code there.
Otherwise - you can try this code - but I have no idea if this works - because the MSSQL driver on my machine expects a different interface.
<?php
$sDatabaseName="eCRM";
$sServer="localhost";
$sUser="UserX";
$sPassword="XXXXXXXXXX";
$oConn = mssql_connect($sServer, $sUser, $sPassword);
if (!$oConn || mssql_select_db('php', $oConn)) {
	die("connection failed");
}

$userName="dmkoluvock";
$password="dtest123";
$emailAddress="dltorres321@gmail.com";
$firstName="Micheal";
$lastName="mkoluvock";
$country="US";
$address1="733 E 24 ST";
$address2="";
$city="Hialeah";
$stateProvince="FL";
$postalCode="33013";
$refferalID="";
$U_Question1ID="2";
$U_Answer1="dsgg";
$U_Question2ID="1";
$U_Answer2="hthtr";
$U_Question3="Color";
$U_Answer3="green";
$IPAddress="166.99.2.037";


$sqlcmd = "Exec [dbo].[sp_CreateUser] " .
	  " @userName = '" . $userName .
	  "' ,@password = '" . $password .
	  "' ,@emailAddress = '" . $emailAddress .
	  "' ,@firstName = '" . $firstName .
	  "' ,@lastName = '" . $lastName .
	  "' ,@country = '" . $country .
	  "' ,@address1 = '" . $address1 .
	  "' ,@address2 = '" . $address2 .
	  "' ,@city = '" . $city .
	  "' ,@stateProvince = '" . $stateProvince .
	  "' ,@postalCode = '" . $postalCode .
	  "' ,@refferalID =  '" . $refferalID .
	  "' ,@U_Question1ID = '" . $U_Question1ID .
	  "' ,@U_Answer1 = '" . $U_Answer1 .
	  "' ,@U_Question2ID = '" . $U_Question2ID .
	  "' ,@U_Answer2 = '" . $U_Answer2 .
	  "' ,@U_Question3 = '" . $U_Question3 .
	  "' ,@U_Answer3 =  '" . $U_Answer3 .
	  "' ,@IPAddress =  '" . $IPAddress . "'";

echo $sqlcmd;
mssql_query($sqlcmd, $oConn);
echo "executed";
?>

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39949941
will do I test soon as I get home about 3 hours

Just so we are on the same page here could you provide me link of what I have to download and install on my windows 2012 Machine with SQL 2012. If you need any more specs on my windows machine let me know please
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39949950
Specs
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39949962
Basically you need the PHP distribution

http://windows.php.net/download/

And the MSSQL Distribution

http://www.microsoft.com/en-us/download/details.aspx?id=20098

(I am working off 5.3 - and if I remember correctly your CentOS version is also 5.3 - so probably a good idea to go with 5.3).

You can then follow directions here

http://php.iis.net/

and here
http://www.rackspace.com/knowledge_center/article/installing-php-on-windows-server-2012
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39949970
ok, I will get this down as soon as possible as I really need to get this resolved. Thanks again for your help I will post update here once I have environment set and tested
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39952327
OK, so I was able to install. php v5.3 as you recommended.(Check)

I also downloaded and Extracted the Microsoft Drivers 3.0 for PHP for SQL Server (Check)

I am assuming the i have to change the php.ini file in some way to use those .dll files. I have not done anything in this regard (NO Check)

I did try to run the php from the command line on Powershell and Dos and it did nothing.

After Install there were 3 Spotlight addons that came up. I did not install was I supposed to?

I did not do the IIS portion since I was running the command from the command line dont know is that makes a difference. I call the Php command with the file and nothing happened. It was not executed.

Addons
php Version
Procedure call
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39952394
No - does need to be run from IIS

Looks like it is not finding PHP in your path

Yes - you need to add the SQL driver to your php.ini - copy the

php_sqlsrv_53_ts.dll

To your ext folder

And add the line

[PHP_MSSQL]
extension=php_sqlsrv_53_ts.dll

To your php.ini
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39952766
OK, I add "php_sqlsrv_53_ts.dll" to my  "C:\Program Files (x86)\PHP\v5.3\ext" directory.

I modified the php.ini file
with lines

[PHP_MSSQL]
extension=php_sqlsrv_53_ts.dll

Must I place these lines in a specific place on the file?

I restarted IIS and ran this from the command line

.\php.exe C:\php3\TestFiles\User_m.php

call 2
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39952860
Did that do anything in the database?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39952863
Actually - hang on I need to modify the script. The driver from Microsoft uses different function names.

Will post an update later today.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39952877
OK no worries. Looks like the PHP engine works fine I ran a Hello World file and it was fine

I will wait on your update.

Hello World
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39953005
Ok one step ahead of you now. New Code ran

Here, Login info may be a bit redundant.

<?php
echo "Step 1\n";
$sDatabaseName="eCRM";
$sServer="localhost";
$sUser="UserX";
$sPassword="XXXXXXXXXX";
$connectionInfo = array( "Database"=>"eCRM", "UID"=>"UserX", "PWD"=>"XXXXXXXXXX");
$oConn = sqlsrv_connect( $sServer, $connectionInfo);

if( $oConn ) {
    echo "Connection established.<br />";
}else{
    echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true));
    }

echo "\nStep 2\n";

$userName="dmkoluvock";
$password="dtest123";
$emailAddress="ltorres321@gmail.com";
$firstName="Micheal";
$lastName="mkoluvock";
$country="US";
$address1="733 E 24 ST";
$address2="";
$city="Hialeah";
$stateProvince="FL";
$postalCode="33013";
$refferalID="";
$U_Question1ID="2";
$U_Answer1="dsgg";
$U_Question2ID="1";
$U_Answer2="hthtr";
$U_Question3="Color";
$U_Answer3="green";
$IPAddress="166.99.2.037";

echo "Step 3\n";

$sqlcmd = "Exec [dbo].[sp_CreateUser] " .
    " @userName = '" . $userName .
    "' ,@password = '" . $password .
    "' ,@emailAddress = '" . $emailAddress .
    "' ,@firstName = '" . $firstName .
    "' ,@lastName = '" . $lastName .
    "' ,@country = '" . $country .
    "' ,@address1 = '" . $address1 .
    "' ,@address2 = '" . $address2 .
    "' ,@city = '" . $city .
    "' ,@stateProvince = '" . $stateProvince .
    "' ,@postalCode = '" . $postalCode .
    "' ,@refferalID =  '" . $refferalID .
    "' ,@U_Question1ID = '" . $U_Question1ID .
    "' ,@U_Answer1 = '" . $U_Answer1 .
    "' ,@U_Question2ID = '" . $U_Question2ID .
    "' ,@U_Answer2 = '" . $U_Answer2 .
    "' ,@U_Question3 = '" . $U_Question3 .
    "' ,@U_Answer3 =  '" . $U_Answer3 .
    "' ,@IPAddress =  '" . $IPAddress . "'";

echo "Step 4\n";

echo $sqlcmd;
sqlsrv_query($oConn, $sqlcmd);
echo "\nexecuted\n";
echo "Step 5\n";
?>

Open in new window


Execution here
Procedure Call

Log Produced
Log
This log clearly shows process completing. I received the email also executing this call.

Now the the process just needs to work from CentOS box.

Let me know your thoughts.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39953101
OK, so did some digging and found this

http://www.microsoft.com/en-us/download/details.aspx?id=28160

I dont even know what I currently have installed. I do see this in the ini file
sql ini file
Could it be what I need. Please advise and tell me where to look to verify this stuff thanks.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39953140
Learning as I go here - last time I worked with MSSQL and PHP support was built in - having to figure out what is what with the new MS drivers.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39953179
First I would like to know what driver I even have i could not see in the ini file.

Then I will worry about installing this Driver.

To me, this looks like the correct driver. What are your thoughts?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39953556
Try the ODBC option again
<?php
$sDatabaseName="eCRM";
$sServer="localhost";
$sUser="USER";
$sPassword="XXXXXXXX";
$sConn="Driver={SQL Server Native Client 10.0};Server=$sServer;Database=$sDatabaseName;";
$oConn = odbc_connect($sConn, $sUser, $sPassword, SQL_CUR_USE_ODBC);
if (!$oConn) {
	die("connection failed");
}

$userName="dmkoluvock";
$password="dtest123";
$emailAddress="dltorres321@gmail.com";
$firstName="Micheal";
$lastName="mkoluvock";
$country="US";
$address1="733 E 24 ST";
$address2="";
$city="Hialeah";
$stateProvince="FL";
$postalCode="33013";
$refferalID="";
$U_Question1ID="2";
$U_Answer1="dsgg";
$U_Question2ID="1";
$U_Answer2="hthtr";
$U_Question3="Color";
$U_Answer3="green";
$IPAddress="166.99.2.037";


$sqlcmd = "Exec [dbo].[sp_CreateUser] " .
	  " @userName = '" . $userName .
	  "' ,@password = '" . $password .
	  "' ,@emailAddress = '" . $emailAddress .
	  "' ,@firstName = '" . $firstName .
	  "' ,@lastName = '" . $lastName .
	  "' ,@country = '" . $country .
	  "' ,@address1 = '" . $address1 .
	  "' ,@address2 = '" . $address2 .
	  "' ,@city = '" . $city .
	  "' ,@stateProvince = '" . $stateProvince .
	  "' ,@postalCode = '" . $postalCode .
	  "' ,@refferalID =  '" . $refferalID .
	  "' ,@U_Question1ID = '" . $U_Question1ID .
	  "' ,@U_Answer1 = '" . $U_Answer1 .
	  "' ,@U_Question2ID = '" . $U_Question2ID .
	  "' ,@U_Answer2 = '" . $U_Answer2 .
	  "' ,@U_Question3 = '" . $U_Question3 .
	  "' ,@U_Answer3 =  '" . $U_Answer3 .
	  "' ,@IPAddress =  '" . $IPAddress . "'";

echo $sqlcmd;
odbc_exec($oConn, $sqlcmd);
echo "executed";
?>

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39953625
You want me to try from linux box. Correct?
if so
err3
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39953751
No - it won't work on Linux - but as you have installed PHP on the Windows box the odbc code should work now.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39953781
Whats the difference the PHP code I posted already worked on Windows box. The issue now is successful run from Linux box
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39953783
I will run your code on windows as well now one sec
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39953799
So there is a difference.
Your code does not connect.
shot
Modified your code a bit to see up to where it runs. Looks like step 1 is all is done. Nothing after that

<?php
echo "Step 1\n";
$sDatabaseName="eCRM";
$sServer="localhost";
$sUser="sUser";
$sPassword="XXXXXXXX";
$sConn="Driver={SQL Server Native Client 10.0};Server=$sServer;Database=$sDatabaseName;";
$oConn = odbc_connect($sConn, $sUser, $sPassword, SQL_CUR_USE_ODBC);
if (!$oConn) {
	die("connection failed");
}

echo "\nStep 2\n";
$userName="dmkoluvock";
$password="dtest123";
$emailAddress="dltorres321@gmail.com";
$firstName="Micheal";
$lastName="mkoluvock";
$country="US";
$address1="733 E 24 ST";
$address2="";
$city="Hialeah";
$stateProvince="FL";
$postalCode="33013";
$refferalID="";
$U_Question1ID="2";
$U_Answer1="dsgg";
$U_Question2ID="1";
$U_Answer2="hthtr";
$U_Question3="Color";
$U_Answer3="green";
$IPAddress="166.99.2.037";

echo "Step 3\n";

$sqlcmd = "Exec [dbo].[sp_CreateUser] " .
	  " @userName = '" . $userName .
	  "' ,@password = '" . $password .
	  "' ,@emailAddress = '" . $emailAddress .
	  "' ,@firstName = '" . $firstName .
	  "' ,@lastName = '" . $lastName .
	  "' ,@country = '" . $country .
	  "' ,@address1 = '" . $address1 .
	  "' ,@address2 = '" . $address2 .
	  "' ,@city = '" . $city .
	  "' ,@stateProvince = '" . $stateProvince .
	  "' ,@postalCode = '" . $postalCode .
	  "' ,@refferalID =  '" . $refferalID .
	  "' ,@U_Question1ID = '" . $U_Question1ID .
	  "' ,@U_Answer1 = '" . $U_Answer1 .
	  "' ,@U_Question2ID = '" . $U_Question2ID .
	  "' ,@U_Answer2 = '" . $U_Answer2 .
	  "' ,@U_Question3 = '" . $U_Question3 .
	  "' ,@U_Answer3 =  '" . $U_Answer3 .
	  "' ,@IPAddress =  '" . $IPAddress . "'";

echo "Step 4\n";	  
echo $sqlcmd;
odbc_exec($oConn, $sqlcmd);
echo "executed";
echo "Step 5\n";
?>
                                            

Open in new window

0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39954126
That code runs fine on my windows workstation. Are you sure you have set the server correctly?

Is your SQL server running as a named instance or the default instance?
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39954203
Default instance.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39954218
Yes, the one I posted worked fine and it had the same parameters. Once we finish this question I have to edit the code snippet where I forgot to remove username and password.
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39954376
I have removed the user name and password from your post.

Not sure from your last post - did the script succeed?
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39954476
My ,mistake I thought I posted this.

This is the code I got to work on the SQL box. NOw I just need to get it to fully work on Linux CentOS box

<?php
echo "Step 1\n";
$sDatabaseName="eCRM";
$sServer="localhost";
$sUser="XXXXXXXXXX";
$sPassword="XXXXXXXXXX";
$connectionInfo = array( "Database"=>"eCRM", "UID"=>"XXXXXXXXXX", "PWD"=>"XXXXXXXXXX");
$oConn = sqlsrv_connect( $sServer, $connectionInfo);

if( $oConn ) {
    echo "Connection established.<br />";
}else{
    echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true));
    }

echo "\nStep 2\n";

$userName="dmkoluvock";
$password="dtest123";
$emailAddress="ltorres321@gmail.com";
$firstName="Micheal";
$lastName="mkoluvock";
$country="US";
$address1="733 E 24 ST";
$address2="";
$city="Hialeah";
$stateProvince="FL";
$postalCode="33013";
$refferalID="";
$U_Question1ID="2";
$U_Answer1="dsgg";
$U_Question2ID="1";
$U_Answer2="hthtr";
$U_Question3="Color";
$U_Answer3="green";
$IPAddress="166.99.2.037";

echo "Step 3\n";

$sqlcmd = "Exec [dbo].[sp_CreateUser] " .
    " @userName = '" . $userName .
    "' ,@password = '" . $password .
    "' ,@emailAddress = '" . $emailAddress .
    "' ,@firstName = '" . $firstName .
    "' ,@lastName = '" . $lastName .
    "' ,@country = '" . $country .
    "' ,@address1 = '" . $address1 .
    "' ,@address2 = '" . $address2 .
    "' ,@city = '" . $city .
    "' ,@stateProvince = '" . $stateProvince .
    "' ,@postalCode = '" . $postalCode .
    "' ,@refferalID =  '" . $refferalID .
    "' ,@U_Question1ID = '" . $U_Question1ID .
    "' ,@U_Answer1 = '" . $U_Answer1 .
    "' ,@U_Question2ID = '" . $U_Question2ID .
    "' ,@U_Answer2 = '" . $U_Answer2 .
    "' ,@U_Question3 = '" . $U_Question3 .
    "' ,@U_Answer3 =  '" . $U_Answer3 .
    "' ,@IPAddress =  '" . $IPAddress . "'";

echo "Step 4\n";

echo $sqlcmd;
sqlsrv_query($oConn, $sqlcmd);
echo "\nexecuted\n";
echo "Step 5\n";
?>

Open in new window

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39958722
Updates?

I must be losing my mind sorry.  This thread is so long I thought I also posted this link check it out tell me what you think. What and how to install if need be. Maybe I have installed and dont even know it. I did not see it in the php info log I provided to you. Thanks!!

http://www.microsoft.com/en-us/download/details.aspx?id=28160
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39960942
New Theories?
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39981237
Guys, any updates please?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39982188
sorry, I don't have any useful input no this one :(
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39983022
Julian are you out of gas here as well?
0
 
LVL 51

Expert Comment

by:Julian Hansen
ID: 39983171
Pretty much - if we can establish that the code works from a Microsoft Machine - my suggestion would be that it is a funny in the PHP / MSSQL library that is causing the problem. Nothing else really seems to make sense. Can't see how you can code your PHP differently to achieve the result you want - after all all you are doing is invoking a Stored Proc with parameters - so very simple code.  The fact that it is not working in the way that it is failing - suggests something at a lower level and I am going to suggest that is the PHP / MSSQL library level.

Options going forward

Do some PHP tests from a Windows PHP installation and determine if problem goes away.

If that works out then unless there is a specific reason you are tied to CentOS then move to Windows. If that is not an option then consider moving to MySQL as alternative - failing that - not sure what else to suggest.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39983337
I posted this link I don't belive I have this driver installed . Can you tell me how I can check for this install.  The site code is about 80% done and paid for would cost me took much to change technology and redone.

I guess my question is what driver am I using. If it's not this one I would like to install it and test. Please just walk me thru pop is not my expertise.

http://www.microsoft.com/en-us/download/details.aspx?id=28160
0
 
LVL 51

Accepted Solution

by:
Julian Hansen earned 500 total points
ID: 39983495
I have no experience with setting up ODBC on linux. I would just go with the instructions on that page - modifying for CentOS as needed (MS seems to only support Red Hat distributions)

In terms of changing technology - if you move to a windows platform - all that should be required is installing PHP on the windows box and then copying the code across.

All my dev is on Windows against both IIS and Apache - and target environments are predominently Linux - so I don't see any issues if you do use a Windows OS solution - unless there are bits outside of the web code that are Linux dependent.
0
 
LVL 8

Author Comment

by:Leo Torres
ID: 39994426
I have been trying to set up php on a Windows machine but have not been successful at it. Getting a strange errors.. will post here later
0
 
LVL 8

Author Closing Comment

by:Leo Torres
ID: 40471780
Moved to asp.net
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now