Moving Servers

Support for Micosoft SQL Server in the context of Jiwa installations.

Moving Servers

Postby robin » Thu Feb 18, 2010 5:18 pm

I will start with saying I'm not a techspert with SQL, I don't usually tinker with something I don't fully know how to use.

We recently purchased a brand new server for our company and I'm in the process of moving everything from one to the other.

I'v become stuck with Jiwa and I'm hoping you might help fix up a few problems I am having.

I have done a fresh install of SQL2008, I left the install of SQL2005 on the server as like I said I I don't know a huge amount about SQL and I didn't fancy trying to move SBSmonitoring to sql2008

I backed up my database from my 2003 server and have created a new data base on my new server, and restored the my data to the new database overwriting all settings.

I recreated all the users (JiwaUser, JiwaLogin, JiwaReports)
made sure they match access levels asnd ran scripts to fix user access based on Case 00006142 .

also created the connections in the ODBC (for spnet)

Jiwa Runs Fine on the Server itself, i can create invoices/purchases etc no problem.

However this is where my problems begin, i have run the upgrade database script but it keeps stopping with the following error:

Code: Select all
Incorrect syntax near the keyword 'TRANSACTION'.


Secondly, I have found it it taking 20+ seconds to find the server in the new connections wizard, and loading any windows i.e sales orders takes 30+ seconds to load.

Thirdly, I cannot connect to Jiwa from any client PC's, When I create the connection It either does not find the server at all, or it if does find it, I get the following error:

Code: Select all
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.


It did connect on a single occasion after I spent a few hours tinkering with it (named pipes and ports), but in Jiwa no menus would load and when I created a invoice it ignored all my settings and made an invoice starting from 0000000001

I'm certain its something simple to fix, but I have only got so far with google.

I have attempted a repair on the sql and also reinstalled sql with the same result.

My next step will be to remove all traces of SQL including 2005 and see if it makes any difference.

Hopefully you might suggest a solution before I do that though.
P.S version 6.5.11
robin
Occasional Contributor
Occasional Contributor
 
Posts: 26
Joined: Tue May 19, 2009 1:11 pm

Re: Moving Servers

Postby Mike.Sheen » Mon Feb 22, 2010 3:31 pm

robin wrote:I backed up my database from my 2003 server


2003 ? SQL version went from 2000, 2005 and then 2008. If it was part of SBS2003, then it is still SQL 2000.

robin wrote:However this is where my problems begin, i have run the upgrade database script but it keeps stopping with the following error:
...
Code: Select all
Incorrect syntax near the keyword 'TRANSACTION'.



If you backup a SQL database on SQL 2000 and restore it onto SQL 2008, it automatically places it in a compatibility mode. In your case, It would seem your database is running in SQL 2000 compatibility mode, and there is a command in our upgrade scripts which is not compatible with the SQL 2000 syntax.

You should check and set the compatibility level of your database. See how here : http://www.jiwa.com.au/forums/viewtopic.php?f=8&t=93

robin wrote:Secondly, I have found it it taking 20+ seconds to find the server in the new connections wizard, and loading any windows i.e sales orders takes 30+ seconds to load.

Thirdly, I cannot connect to Jiwa from any client PC's, When I create the connection It either does not find the server at all, or it if does find it, I get the following error:

Code: Select all
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.



Sounds like you need to use the configuration manager on the SQL Server to use TCP/IP. Turn off named pipes in the configuration manager and make sure TCP/IP is enabled, and has top priority.

Also, windows firewall may be limiting access. Temporarily disable windows firewall to see if that is the cause. Installing SQL is supposed to add a rule to allow the correct port (1433) through, but sometimes it does not. You should also check this on the client machines.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 1 guest

cron