22 Desember 2011 20:14
I have two instances: one is built on SQL Server 2008, and another is upgraded to 2008 R2. From SQL Server management studio, I can connect both instances with computer name. Problem is: I can connect only old instance with IP address instead of computer name, but I can't connect new instance with IP address.
Thank you in advance.
23 Desember 2011 10:59
Each SQL Instance uses a different port on the IP address.
For PHP, when you create an instance, you must assign a fixed port number *.
If your instance already has a fixed port number, all you need to do is append it to the IP address...
E.g. if you had instance Computer\InstA, it might be assigned to use port 2000, on IP address 220.127.116.11. To connect to it, you use this syntax:
(The default SQL Server port is 1433, and this port does not need to be appended to the IP address)
* The port can be fixed or dynamic, and I think SQL Instances use dynamic port numbers by default. It uses port 1434 for a browser service which maps instance names to port numbers. You could connect to your instance in Management Studio, then run the command NETSTAT in a command window to see what port numbers have been used.
- Ditandai sebagai Jawaban oleh wachy 10 Januari 2012 14:27
25 Desember 2011 10:45
Thank you for your suggestion. I’ve found the port of SQL new instance using netstat command. I’ve used according to your suggestion, but still it generates error message.
On development, I use SQL Server 2008 R2 in where I have to use sqlsrv_connect function for establishing connection. But on the live database, we have SQL 2008 what I upgraded into 2008 R2. On live database, mssql_connect is used for connection setting, and still it is OK with old instance. Which connection function should I use for on the live SQL 2008 R2?
Thank you again.
27 Desember 2011 18:39Moderator
You should be able to use sqlsrv_connect. Can you post your PHP connection code and the error details you are receiving? That might help us understand what is going on.
This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
05 Januari 2012 11:02
Sorry for my late reply. Thank you for your reply.
The error message is:
"A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. "
10 Januari 2012 11:39
The error message means that either:
- Your SQL Server was not found, so maybe you have *not* specified the correct parameters to sqlsrv_connect().
- Your firewall or network configuration prevented your PHP server from communicating with the SQL Server instance.
- You have not set up your instance correctly, e.g. disabled remote connections, disabled TCP/IP connections, or specified a dynamic (therefore, unknown) port number for the instance, etc.
This is not a problem with the driver.
Here are some basic rules for server names in sqlsrv_connect(), using examples for a theoretical instance MYINSTANCE running on computer MYSERVER at address 10.20.30.40 on port 5555:
- With an IP address - to connect remotely over TCP/IP: sqlsrv_connect('10.20.30.40,5555', $params);
- With server and/or instance name * - to connect using named pipes or TCP/IP on a LAN: sqlsrv_connect('MYSERVER\\MYINSTANCE', $params);
* When using server\instance names, these 2 rules may help:
- Encapsulate the text in single-quotes (not: ")
- Replace backslash \ with \\
10 Januari 2012 14:27
I believe my firewall would prevent this. Using netstat, I found the port (for example: 55012). I added it to the exemption list of firewall, but it didn't work.
So, I solved it by applying default SQL port on new instance and added all databases of old instance to the new instance.
Thank you so much both Johnson and Brian .