2

I am new to both (Java) FitNesse and SQL Server, I want to have a basic SQL test working with DbFit,

I have the following in root:

!path c:\fitnesse\lib\dbfit.jar
!path c:\fitnesse\lib\fitlibrary.jar
!path c:\fitnesse\lib\sqljdbc4.jar
|Import|
|dbfit.SqlServerTest|

I have the following in my HelloWorldTest,

!contents -R2 -g -p -f -h

!|dbfit.SqlServerTest|

|Connect|LOCALSQLEXPRESS|sa|Password|somePassword:|

|eg.Division|
|numerator|denominator|quotient?|
|10       |2          |5        |
|12.6     |3          |4.2      |
|100      |4          |33       |

What I have tried:

  • I have double checked my SQLExpress username and password and the SqlServer instance is at localhost.I doubt this is a firewall issue. (My understaning of firewall is quite weak admittedly.)
  • Looking at Sql Server Configuration Manager (SSCM),
    • I see that "SQL Server (SQLEXPRESS) is running as Process with ID 2316,
    • Using the PowerShell command "nestat -a -no | Select-String 2316", I see that the process on port 55618.
  • In SSCM, I then create alias called "LOCALSQLEXPRESS" in both "SQL Native Client 11.0 Configuration (32bit)" and "SQL Native Client 11.0 Configuration" as I have no idea what my JDBC is using. (Apparently, JDBC for SQL Server does not support named instance, I got an exception telling me so when I was trying to connect to \localhost\SQLEXPRESS directly) I have also enabled "TCP/IP" and "Shared Memory" in "Protocols for SQLEXPRESS".

I am getting the following error when I run the test:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host LOCALSQLEXPRESS, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

(Note the port number of 1433, despite my alias point to port 55618.)

This is my first post at StackOverflow, please excuse any silliness. Can anyone shed any light?

benilov
  • 1,234
  • 11
  • 11
lingo_journey
  • 653
  • 1
  • 8
  • 22
  • Is that SQL Server actually configured to accept TCP/IP connections, and fixed on port 1433? By default TCP/IP is disabled, and the port is randomized. Also `LOCALSQLEXPRESS` is probably not the correct hostname... – Mark Rotteveel Nov 07 '12 at 12:19
  • @Mark is correct on the hostname issue. For a default installation you could use either `localhost\SQLEXPRESS` or `.\SQLEXPRESS`. – Michael Sorens Nov 07 '12 at 14:48
  • @MarkRotteveel & msorens Thanks! I have editted the question to clarify the points raised. Any other idea? – lingo_journey Nov 08 '12 at 14:38
  • JDBC does support named instances, but you do have to have the SQL Server Browser service running (and TCP/IP must be enabled in the SQL Server config). AFAIK both these things are disabled by default – Mark Rotteveel Nov 08 '12 at 14:47
  • @MarkRotteveel Thanks again! TCP/IP is running but my SQL Server Browser is indeed not running, how I do run it? In "Microsoft Management Console" documentation, I am told to run "\\sqlbrowser.exe -c", but I can't find sqlbrowser.exe anywhere under "C:\Program Files\Microsoft SQL Server". What do you think? – lingo_journey Nov 08 '12 at 14:57
  • Go to the SQL Server Configuration Manager, SQL Server Services and start the SQL Server Browser service. BTW: I don't think you can use aliases with the JDBC driver, so you would need to use localhost\NAMEDINSTANCE! – Mark Rotteveel Nov 08 '12 at 15:12
  • @MarkRotteveel Thanks a lot! I did try that before my last comment, but I noticed that "Start" was greyed out. I tried again just now, I right-clicked "SQL Server Browser" > Service > Change Start Mode from "Disabled" to "Manual", now I can start it! I'm happy now :) – lingo_journey Nov 08 '12 at 18:10
  • @MarkRotteveel I now have the following stack-trace, I have learnt something but in terms of my original problem, I feel that I am back to square one: `java.lang.UnsupportedOperationException: Java SQL Server Driver does not work with instance names. Create an alias for your SQL Server Instance. at dbfit.environment.SqlServerEnvironment.getInstanceString(SqlServerEnvironment.java:26) at dbfit.environment.SqlServerEnvironment.getConnectionString(SqlServerEnvironment.java:41) at dbfit.environment.AbstractDbEnvironment.connect`.... – lingo_journey Nov 08 '12 at 18:17
  • @Mark maybe I did not set up my alias correctly at the beginning? – lingo_journey Nov 08 '12 at 18:17
  • I don't know dbfit, but my first guess would be that they aren't talking about SQL Server aliases, but about aliases inside dbfit. – Mark Rotteveel Nov 08 '12 at 18:19

1 Answers1

0

I'm hoping lingo_journey already had worked out the issue by now. For others who may have same problem, I initially also couldn't make the named instance work with the multi-parameter Connect.

To solve the issue, I switched to connection string as parameter for Connect:

1) ensured that the SQL Server Browser service is running (as mentioned in the comments above)

2) changed the Connect parameter as below:

!| Connect | jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=DB_NAME;integratedSecurity=true |

The following worked as well using the SQL Server authentication (which is probably more applicable to lingo_journey:

!| Connect | jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=DB_NAME;user=user1;password=Welcome!|

Note than in some examples I saw on the web, the sqlserver: has been omitted from the connection string which did not work for me.

libran_6
  • 16
  • 6