SQL Server 2008

July 1, 2010 9:59AM

Hi

I'm running SQL Server 2008 R2 on a Windows 7 64-bit machine on which I have Omniscope 2.6 b300 installed. I also have MS Office Pro 2010 on this machine. I add a Database table to the pallette in datamanager I select SQL Server and then select SQL Server 2008. Then I complete the fields as required and try to connect and receive an error (see attached). I have tried with all SQL Server options and still cannot connect (jTDS etc). I have turned the firewall off on the machine, checked all the ports are still at their default of 1433 and still get an error. The only way I can connect to SQL Server is by downloading the SQL Server JDBC drivers using the JDBC (Advanced) option. The preferred route is obviously to use the SQL Server option rather than to install JDBC.

Can you confirm whether this is an issue or I need to change my config?

Thanks

Andy

chris · July 1, 2010 10:21AM

Hi Andy,

It looks like your host name is incorrect. The host name should be the name or IP-Address of the PC on which SQL-Server is installed. Please try again with: 'Paddy-PC'.

Thanks

Chris

July 1, 2010 11:03AM

Hi Chris

The host name is OK. Paddy-PC is the machine and SQLEXPRESS is the server instance. When I connect using the SQL Server Management Studio I always connect to either Paddy-PC\SQLExpress or .\SQLExpress (. meaning local machine)-see attached. I'm using SQL Server 2008 R2 Express which is why the instance name is needed.

The JDBC connection string is below (which works) and you can see this uses PADDY_PC\sqlexpress

jdbc:sqlserver://PADDY-PC\sqlexpress;databaseName=master

Have tried connecing with just Paddy-PC or the IP address and still get the same error.

Thanks

Andy

chris · July 1, 2010 12:23PM

Hi Andy,

The host name is different to the server name you use in the SQL Server Management Studio. It should therefore only be the name of the PC or the IP address. Leaving the host name like this, could you try the following:

- Entering the Database name as: master and adelie_raw and no database name.

- Ensuring SQL server is running on port 1433. You can find this out in the SQL Server Configuration Manager by expanding the SQL Server Network Configuration and select Protocols for MSSQLSERVER (default instance). In the right hand panel you'll see the protocols. Double click on TCP/IP and a properties dialog box will appear. If you select the IP Addresses tab you should see your IP address and the TCP port SQL Server is running on.

Please let me know if you still can't connect.

Thanks

Chris

July 1, 2010 4:07PM

Hi Chris

Have tried all your suggestions but still no luck. My SQL server does not have a Default instance of MSSQLSERVER, just a named instance of SQLEXPRESS. Have seen today that there are a couple of bugs around this:

http://connect.microsoft.com/SQLServer/feedback/details/361809/selecting-the-default-instance-option-in-sql-server-2008-express-rtm-has-no-effect#

http://www.dedicatedsqlserver.com/HowTo/SQL2008_Express_Default_Instance.aspx

Should it not be possible to connect to a named instance? The JDBC connects to it just fine.

Thanks

Andy

chris · July 1, 2010 4:28PM

Have you tried entering 'SQLEXPRESS' as the Database name?

July 1, 2010 4:38PM

Just tried - no luck. If you want to call me I can share my screen and you can see what's going on. Am around today and tomorrow.

Andy

chris · July 1, 2010 4:57PM

Hi Andy,

I've sent you an email to arrange a Skype session so we can investigate this further.

Thanks

Chris

October 1, 2010 8:39AM

@aknotts

Did you get this working? The SQL connection now allows separate specification of server & host name - so it should all work...?

October 1, 2010 8:55AM

Hi Guy

Yes thanks, got it working a treat. How's things? Have you spoken to Paddy recently?

Andy

October 5, 2010 6:26PM

Andy - glad you got it working. No... must give Paddy a call :)

Welcome!

Categories

Ideas Parade

Tagged