SQL 2008 Help please URGENT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

SQL 2008 Help please URGENT

Author

Topic

Tyson
Starting Member

10 Posts

Posted - 2014-11-17 : 15:30:59

Evening all

I have two questions please,,

1. Please can you help me out I am in the process of creating a private server for the gaming community I am using SQL 2008 R2 and I have the game working on my computer however external users are unable to join can anyone think as to why no one can join the server externally only I using my computer can access the server

2. I am trying to run a query which I have run already and has worked successfully however i'm trying to do the query again to make another game master account however im being introduced to an error which says the following:
MSG 102 LEVEL 15 STATE 1 LINE 2
INCORRECT SYNTAX NEAR "="
can anyone please shed any light on this it worked once fine now its coming up with that.

this is the quesry I am putting in.

sLEVEL = '25', iSTR = '200000000', iDEX = '2000000000', iINT = '200000000', iCHA = '2000000000', iWIS = '2000000000', iCON = '200000000', sHP = '2500', sMAXHP = '2500', sMP = '2500', sMAXMP = '2500', sSTM = '2500', sMAXSTM = '2500', sMAXWGT = '2500', iMORAL = '7400000', iMONEY = '500000', iBANK = '50000000', sRANK = '1', iSwordEXP = '20000000', iSpearEXP = '20000000', iBowEXP = '20000000', iAxeEXP = '20000000', iKnuckleEXP = '20000000', iStaffEXP = '20000000', iWeaponMakeEXP = '20000000', iArmorMakeEXP = '20000000', iAccMakeEXP = '20000000', iPotionMakeEXP = '20000000', iCookingEXP = '20000000', iWMagicEXP = '10000000', iBMagicEXP = '10000000', iDMagicEXP = '0'
WHERE strUserId = 'CharNameHere'

regards
any help would be appreciated and you would be a life saver thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-17 : 15:36:49

1. We need more info, like an error.
2. That's not a complete query. Where's the rest of it? It should start with SELECT/INSERT/UPDATE/DELETE.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 15:37:36

1. Does your server have a public IP address and advertized DNS name?

2. Did you forget the command 'SELECT'? Also, where is the FROM clause pointing to the table with the column strUserId?

Tyson
Starting Member

10 Posts

Posted - 2014-11-17 : 15:40:32

Chaps you will need to bear with me as its new to me aswell but I have a computer literate friend who is assisting me on this project let me upload the server guide for you and see if that helps you. What other info would you need to assist you further?

the server is done through my LAN IP address and that is the full query as it worked earlier when I done it earlier

Tyson
Starting Member

10 Posts

Posted - 2014-11-17 : 15:43:44

this is the server set up guide that I have followed and has worked and I have successfully made a server - However no external users can join and this is the problem i am having

here is the guide -

Server setup guide

Software:

1. MSSQL 2008 R2 Express (235MB x86 / 247MB x64)
2. The Myth of Soma server files (7.3MB approx)( Extract them to c:\SERVER)
3. XVI32 (558KB)
4. Xampp 1.7.3 (53.7 MB)
5. An installed Myth of Soma client (366MB)

Step One: Downloading MSSQL
1. Download SQL Server 2008 R2 express from:
http://www.microsoft.com/express/Database/
get the installer for the Database and the Management Tools
2. It will ask you whether you want x86 or x64 if you are not sure what the difference is download the x86 version of the database installer.
3. Wait until your download is complete, and then open the installer.

Step Two: Installing MSSQL
1. Click New Installation or add features to an existing installation.
2. Tick I accept the license terms and press next.
3. Ensure all tick boxes are selected and press next.
4. Click Named instance, and press next.
5. Press next.
6. Press next.
7. Select Mixed mode and enter a strong and long password and press next.
8. Press next.
9. After install has completed, press close.

Step Three: Configuring MSSQL
1. Open SQL Server Management Studio, you'll find it in the start menu under 'Microsoft SQL Server 2008 R2'.
2. Select Windows Authentication press Connect.
3. Right click Databases and press restore Database.
4. Select From device and click the button that says '...' to the right.
5. Press Add, go to 'C:\SERVER\oneperone\db and select 'soma.bak' press okay.
6. Press okay tick the tick box and type soma in the top drop down box press okay.
7. Press okay, expand Security right click Logins and press New Login.
8. Type soma in the login name box and select SQL Server authentication type soma into the password field, untick Enforce password policy.
9. Change the default database to soma, press User Mapping tick soma and tick db_owner press okay.
10. Right click the instance at the top of the object explorer and click properties and click connections and untick Allow remote connections to this server, press okay

Step Four: ODBC connector
1. Press windows key and r together, type Odbcad32.exe and press okay. (if you are running a 64bit system type %systemdrive%\Windows\SysWoW64\Odbcad32.exe instead)
2. Select System DSN and press Add...
3. Scroll down the list and select SQL Server press finish.
4. Type soma into name, a description into description and type localhost\SQLExpress
5. Press next, select with SQL Server authentication, enter soma under login ID and enter soma under password press next.
6. Select Change the default database to and select soma from the drop down box press next and finish.
7. Press test data source, if you do not see TESTS COMPLETED SUCCESSFULLY! review your installation up until this point and try and figure out what is preventing you from connecting to your database.
8. Press okay three times until the ODBC is not in view.

Step Five: Services Setup
1. Go to C:\SERVER\services\Service Management\Service Creator
2. Open config.ini in a text editor
3. Change the IP line to your current LAN IP
4. Save and close changes
5. Open Soma Services.exe and press Create Services
6. Check the Services by pressing View Services make sure SOMA user, SOMA game, SOMA session are running
if not start them in the order above by selecting them and pressing start

Step Six: Start the server
1. Go to c:\SERVER\oneperone and open sharedmem.exe
2. Open oneperone.exe in the same folder, if you are running the latest server files you should see no errors
Close the server by opening task manager and ending the oneperone.exe process and then closing sharedmem.exe

Step Seven: Setting up your register page
1. Go to http://sourceforge.net/projects/xampp/f ... e/download and download the .exe 53.7mb self extracting RAR archive
2. Run the xampp install click install, follow the command line installer
3. When the installer has finished go to c:\SERVER\Tools\Web tools select all the files in that directory and right click them and press copy. Go to c:\xampp\htdocs and press paste.
4. Go to c:\xampp\php\php.ini open it in a text editor, replace
Code: Select all
;extension=php_mssql.dll
with the line
Code: Select all
extension=php_mssql.dll
save the file.
5. Go to Start > Programs > Microsoft SQL Server 2008R2 > Configuration Tools > and open SQL Server Configuration Manager.
6. Expand SQL Server Network Configuration, press Protocols for MSSQLSERVER Double-click the protocol name TCP/IP and set the property ENABLED to Yes. Double-click the protocol name Named Pipes and set the property ENABLED to Yes.
7. Press start click run type services.msc and press enter and scroll down until you find the service called "SQL Server" select this service and press restart.
8. Open XAMPP control panel from the start menu and click Start Apache
9. Check the register page by opening your web browser and navigating to http://localhost/register.php

Step Eight: Port forwarding
1. If you are not familiar with your own router's forward porting functionality check http://portforward.com/ for your router's model which should give you an idea of how to perform port forwarding on your particular router
2. Forward the ports 12000, 4110 and 80 from your router to your PC, ensure windows firewall has an exception for those ports and that any third party firewalls you may have also have exceptions for those ports
3. Go to https://www.grc.com/x/ne.dll?bh0bkyd2 press proceed and type 12000, 4110, 80 into the text field and press "User Specified Custom Port Probe" this is the result you want:

4. If you do not get "failed", check your router forwarding settings, firewall settings and check to see whether your services, xampp and oneperone are running during the scan

Step Nine: Connecting
1. Go to c:\SERVER\services and open dir.ini in a text editor replace the line
Code: Select all
SVR01_ADDR=
with
Code: Select all
SVR01_ADDR=WANIPHERE
2. type services.msc into your start menu and press enter and stop the user, game and session service, then start them in order: User, Game, Session
3. Right click on your desktop press new > shortcut and type
Code: Select all
C:\Soma\Soma.exe LANIPHERE
and press next then finish, double click the newly created shortcut

Step Ten: Gm creation
1. Go to http://localhost/register.php make yourself an account, and log into your game, create a character and remember the character's username, once your character has been created, quit the game
2. Open Microsoft SQL Server Management Studio and connect to your database, press new query and paste
Code: Select all
UPDATE GAMEUSER SET
sLEVEL = '25', iSTR = '200000000', iDEX = '2000000000', iINT = '200000000', iCHA = '2000000000', iWIS = '2000000000', iCON = '200000000', sHP = '2500', sMAXHP = '2500', sMP = '2500', sMAXMP = '2500', sSTM = '2500', sMAXSTM = '2500', sMAXWGT = '2500', iMORAL = '7400000', iMONEY = '500000', iBANK = '50000000', sRANK = '1', iSwordEXP = '20000000', iSpearEXP = '20000000', iBowEXP = '20000000', iAxeEXP = '20000000', iKnuckleEXP = '20000000', iStaffEXP = '20000000', iWeaponMakeEXP = '20000000', iArmorMakeEXP = '20000000', iAccMakeEXP = '20000000', iPotionMakeEXP = '20000000', iCookingEXP = '20000000', iWMagicEXP = '10000000', iBMagicEXP = '10000000', iDMagicEXP = '0'
WHERE strUserId = 'CharNameHere'
substituting 'CharNameHere' for your character name for example = 'Arcanine'
3. Press the green play button at the top and ensure it says one row has been affected then log back into your game to test your gm character, you can find gm commands here all gm commands are executed by typing forward slash in game and then the gm command for example:
Code: Select all
/rain 1
confirm by pressing enter

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-17 : 15:45:08

The server guide has the correct command:
UPDATE GAMEUSER SET
sLEVEL = '25', iSTR = '200000000', iDEX = '2000000000', iINT = '200000000', iCHA = '2000000000', iWIS = '2000000000', iCON = '200000000', sHP = '2500', sMAXHP = '2500', sMP = '2500', sMAXMP = '2500', sSTM = '2500', sMAXSTM = '2500', sMAXWGT = '2500', iMORAL = '7400000', iMONEY = '500000', iBANK = '50000000', sRANK = '1', iSwordEXP = '20000000', iSpearEXP = '20000000', iBowEXP = '20000000', iAxeEXP = '20000000', iKnuckleEXP = '20000000', iStaffEXP = '20000000', iWeaponMakeEXP = '20000000', iArmorMakeEXP = '20000000', iAccMakeEXP = '20000000', iPotionMakeEXP = '20000000', iCookingEXP = '20000000', iWMagicEXP = '10000000', iBMagicEXP = '10000000', iDMagicEXP = '0'
WHERE strUserId = 'CharNameHere'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Tyson
Starting Member

10 Posts

Posted - 2014-11-17 : 15:48:03

quote:
Originally posted by tkizer

1. We need more info, like an error.
2. That's not a complete query. Where's the rest of it? It should start with SELECT/INSERT/UPDATE/DELETE.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

reference question one there is no error it just says failed to connect to server no error code just continues to state the above message

Tyson
Starting Member

10 Posts

Posted - 2014-11-17 : 15:50:20

quote:
Originally posted by tkizer

The server guide has the correct command:
UPDATE GAMEUSER SET
sLEVEL = '25', iSTR = '200000000', iDEX = '2000000000', iINT = '200000000', iCHA = '2000000000', iWIS = '2000000000', iCON = '200000000', sHP = '2500', sMAXHP = '2500', sMP = '2500', sMAXMP = '2500', sSTM = '2500', sMAXSTM = '2500', sMAXWGT = '2500', iMORAL = '7400000', iMONEY = '500000', iBANK = '50000000', sRANK = '1', iSwordEXP = '20000000', iSpearEXP = '20000000', iBowEXP = '20000000', iAxeEXP = '20000000', iKnuckleEXP = '20000000', iStaffEXP = '20000000', iWeaponMakeEXP = '20000000', iArmorMakeEXP = '20000000', iAccMakeEXP = '20000000', iPotionMakeEXP = '20000000', iCookingEXP = '20000000', iWMagicEXP = '10000000', iBMagicEXP = '10000000', iDMagicEXP = '0'
WHERE strUserId = 'CharNameHere'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sorry I should of been more accurate the error says the following

Msg 208, Level 16, State 1, Line 2
Invalid object name 'GAMEUSER'.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-17 : 15:51:53

quote:
Originally posted by Tyson

quote:
Originally posted by tkizer

The server guide has the correct command:
UPDATE GAMEUSER SET
sLEVEL = '25', iSTR = '200000000', iDEX = '2000000000', iINT = '200000000', iCHA = '2000000000', iWIS = '2000000000', iCON = '200000000', sHP = '2500', sMAXHP = '2500', sMP = '2500', sMAXMP = '2500', sSTM = '2500', sMAXSTM = '2500', sMAXWGT = '2500', iMORAL = '7400000', iMONEY = '500000', iBANK = '50000000', sRANK = '1', iSwordEXP = '20000000', iSpearEXP = '20000000', iBowEXP = '20000000', iAxeEXP = '20000000', iKnuckleEXP = '20000000', iStaffEXP = '20000000', iWeaponMakeEXP = '20000000', iArmorMakeEXP = '20000000', iAccMakeEXP = '20000000', iPotionMakeEXP = '20000000', iCookingEXP = '20000000', iWMagicEXP = '10000000', iBMagicEXP = '10000000', iDMagicEXP = '0'
WHERE strUserId = 'CharNameHere'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sorry I should of been more accurate the error says the following

Msg 208, Level 16, State 1, Line 2
Invalid object name 'GAMEUSER'.

Perhaps you are executing the query in the wrong database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Tyson
Starting Member

10 Posts

Posted - 2014-11-17 : 15:54:24

quote:
Originally posted by tkizer

quote:
Originally posted by Tyson

quote:
Originally posted by tkizer

The server guide has the correct command:
UPDATE GAMEUSER SET
sLEVEL = '25', iSTR = '200000000', iDEX = '2000000000', iINT = '200000000', iCHA = '2000000000', iWIS = '2000000000', iCON = '200000000', sHP = '2500', sMAXHP = '2500', sMP = '2500', sMAXMP = '2500', sSTM = '2500', sMAXSTM = '2500', sMAXWGT = '2500', iMORAL = '7400000', iMONEY = '500000', iBANK = '50000000', sRANK = '1', iSwordEXP = '20000000', iSpearEXP = '20000000', iBowEXP = '20000000', iAxeEXP = '20000000', iKnuckleEXP = '20000000', iStaffEXP = '20000000', iWeaponMakeEXP = '20000000', iArmorMakeEXP = '20000000', iAccMakeEXP = '20000000', iPotionMakeEXP = '20000000', iCookingEXP = '20000000', iWMagicEXP = '10000000', iBMagicEXP = '10000000', iDMagicEXP = '0'
WHERE strUserId = 'CharNameHere'

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sorry I should of been more accurate the error says the following

Msg 208, Level 16, State 1, Line 2
Invalid object name 'GAMEUSER'.

Perhaps you are executing the query in the wrong database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

its the correct DB as I done it earlier on today and it worked fine now it is telling me that GAMEUSER is incorrect. any ideas on question one? reference external users not being able to connect?

Tyson
Starting Member

10 Posts

Posted - 2014-11-17 : 15:55:45

quote:
Originally posted by gbritton

1. Does your server have a public IP address and advertized DNS name?

2. Did you forget the command 'SELECT'? Also, where is the FROM clause pointing to the table with the column strUserId?

1. the game is using my LAN IP address in the target box of the properties menu

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-11-17 : 15:57:30

1. We don't have enough information. We are a SQL Server community. We aren't a gaming community. We don't have experience with whatever system you are setting up. Without a SQL Server error or more information, it is hard for us to help. We'd love to help, but the information given thus far isn't enough for us to help.
2. Check that the object still exists in the database. You are either in the wrong database or the object doesn't exist. Perhaps you accidentally dropped it?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Tyson
Starting Member

10 Posts

Posted - 2014-11-17 : 16:01:40

Just to let you know I have fixed the query issue thanks for your help on that one :D

Tyson
Starting Member

10 Posts

Posted - 2014-11-17 : 16:03:51

quote:
Originally posted by tkizer

1. We don't have enough information. We are a SQL Server community. We aren't a gaming community. We don't have experience with whatever system you are setting up. Without a SQL Server error or more information, it is hard for us to help. We'd love to help, but the information given thus far isn't enough for us to help.
2. Check that the object still exists in the database. You are either in the wrong database or the object doesn't exist. Perhaps you accidentally dropped it?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

I understand your are a SQL community and The server has been created through SQL however there are no errors apparent that pop up otherwise I would state them here. the only issue is connection from external users. which is connected through an IP address and all synced into SQL

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-17 : 20:58:35

If your server is using your lan IP then no one can connect to it from outside your lan without some kind of redirection in your router

Tyson
Starting Member

10 Posts

Posted - 2014-11-18 : 07:38:40

quote:
Originally posted by gbritton

If your server is using your lan IP then no one can connect to it from outside your lan without some kind of redirection in your router

Hi mate you seem to know about this please could you send me an e Mail on Gaz.slater@yahoo.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources