Server Upgrade from Windows 2003 to Windows 2008 2

[kas321]

I am working on a project for an organization which uses a web-based software program that works with sql 2005.

Excel forms were created to access data from the database/tables using visual basic. After upgrading the server from Windows 2003 to Windows 2008, the excel forms no longer work, even though they changed IP address in the module/code. Is this a matter of changing from ODBC to something else? I'm not sure this is a programming problem or a connection problem. any advice would be appreciated.

here is a sample of the code:
Execute_SQL_Command = 0

' Connect using the SQLOLEDB provider.
cn.Provider = "sqloledb"

' Specify connection string on Open method.
provStr = "Server=xx.xxx.xxx.xxx;xxxdatabase=" & SQLDatabase & ";uid=xxx;pwd=xxx

 

[gmmastros]

What is the error message (if any)?
Do you have the sqloledb provider installed?
Can you connect to it using the data link properties?

Do this:

[tt][blue]
Right click the desktop -> new -> Text Document
rename it to "TestConnection.udl"
double click the icon
click the provider tab.[/blue][/tt]

If you have "Microsoft OLE DB Provider for SQL Server" in the list, then you have the correct provider installed.

[tt][blue]
Click "Microsoft OLE DB Provider for SQL Server"
Click Next>>
Put the IP address in for the server.
Enter the User name and Password
Click the drop down for the database
[/blue][/tt]

If everything is configured properly, you should see a list of databases. One of the databases should be the same the database shown in your provStr variable.

web-based software program that works with sql 2005

even though they changed IP address in the module/code

Just to be clear here... is the database somewhere on the internet (not hosted on your server)? If so, you probably shouldn't have changed the IP Address.

Also, take another look the provStr.

[tt][blue]
provStr = "Server=[!]xx.xxx.xxx.xxx;xxx[/!]database=" & SQLDatabase & ";uid=xxx;pwd=xxx
[/blue][/tt]

When an IP Address is listed for the server, it is sometimes followed by a comma. The semi-colon should appear in front of database. When there is an IP Address followed by a comma and some more numbers, that means SQL is using a non-standard port. The standard port is 1433, but any port number can be used.

So, it usually looks like this...

[tt][blue]
provStr = "Server=xx.xxx.xxx.xxx[!],[/!]xxx[!];[/!]database=" & SQLDatabase & ";uid=xxx;pwd=xxx
[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kas321]

Thank you -- I will try this and by the way the database is on the server, not the internet. I will let you know what happens and thanks again.

 

[kas321]

thank you for the valuable information -- it is indeed a connection problem. Can you tell me if there are rules for naming the database? the the name of the database was changed for the new Windows 2008 Server. In other words, the name is "xxxdatabase" for old server and "dbserver" for new server. could this cause the connection problem? thanks again for your help.

 

[Snakeroot]

Don't forget that Windows 2008 has a firewall and it defaulted to on. That's usually the first thing I check when I "used to be able to do it on 2003."

You can get to the firewall settings from the Server Manager under "Configuration".

 

[kas321]

We've checked firewall. still no connection/excel reports do not run. any other suggestions...anyone?