connecting to sql server (istance)

cirollo

cirollo

Active Member
Licensed User
Longtime User
Hi guys!

I'm using b4ppc to connect and read/write data on a sql 2005-2008 db (express edition)

to connect to the db, I use this string:

B4X: 
stringaconn="Persist Security Info=False;Integrated Security=False;Server="&SVR &",1433;initial catalog=" &DBN &";user id="&USR &";password="&PWD&";"
      Sql1.New1
      If Sql1.Open(stringaconn) Then
         LblDb.Visible = True
         LblDb.Width = 230
         LblDb.Text = "Server: "&SVR&":1433 - DB: "&DBN&" ("&AZI&")"
   '      Msgbox("Conessione al DB Stabilita con Successo!!!","My First Program",cMsgboxYesNo,cMsgboxHand)
      Else    
             Msgbox("Impossibile Connettersi al DB!!!",progname,cMsgboxYesNo,cMsgboxHand)
         Msgbox(sql1.LastError,"")
      End If

it works if i connect to the main istance (for example: SVR=SERVER), but if i try to connect to:

SVR=SERVER\SQL2008

it doesn't work!!! why???
 
S

StephanH

Member
Licensed User
Longtime User
Hi,
Even there is a difference between a default instance and a 'named instance'.
You have to go into SQL Servers Connection Manager and set the TCP PORT manally different for your named instance.
The free express version dosn't support automatic PORT mapping in fact there is no SQL Browser Service is running.

You can also connect via &IP&:&Port&.

I hope this Information is helpful for you.

Regards,
Stephan


Sent with Tapatalk 2.
 
cirollo

cirollo

Active Member
Licensed User
Longtime User
???

tried also with 192.168.1.10\SQL2008

but doesn't work!

can you provide an example?
I think that on express version you could also install sql browser....
 
S

StephanH

Member
Licensed User
Longtime User
cirollo said:
tried also with 192.168.1.10\SQL2008

but doesn't work!

can you provide an example?
I think that on express version you could also install sql browser....
Click to expand...

192.168.1.10:12500
if your port is 12500.
Without the Name.
Express Edition havn't the Browser to resolve the named instance and bind it to the IP and port.

If the instance is running you can connect.



Sent with Tapatalk 2.
 
cirollo

cirollo

Active Member
Licensed User
Longtime User
???

tried also with 192.168.1.10\SQL2008

but doesn't work!

can you provide an example?
I think that on express version you could also install sql browser....
 
S

StephanH

Member
Licensed User
Longtime User
You can read in MSDN Browser wont work with Express Edition and named instance.

You tried the IP without \SQL2008?
What error returnd?

Sorry, I cannot provide a example.
I'm mobile and on the way.

Maybe it is a error in your credentials or somthing different.
I need the returncode from SQL Server to help you at this way.

Forget the instance name.
Connect only with IP and port.

Belive it.

Sent with Tapatalk 2.
 
cirollo

cirollo

Active Member
Licensed User
Longtime User
let me explain better

so stephan, thanks for the help, this is my situation

server with winserver2008 std and sql2008r2 (licensed edition)

on the sql there's only one istance, called SQL2008.

on the server is the ERP database that other users access via client using, for example,

2008SVR\SQL2008 as host and port 1433 in their connection string.

I cannot change the port of the sql, they've roughly 25 clients and they'll not linke that! Rather, I cannot reinstall SQL withous istance.

I need to access the db from a mobile application, but if i try at home/office with predefined istance, everything works fine, on the customer site I get the problem.
If you have any hint, otherwise next monday I can give the correct error because I'm at the customer site.

regards,
 
S

StephanH

Member
Licensed User
Longtime User
yes, give me the error next monday.

I think an erp system isn't working in the basic authenticaton mode.
It is also not recommended that the default port 1433 is used.
This is poor installed against all known security features.

I cannot belive this.
At me, I have administered three generations of MS SQL Server.
Major version 2000 up to 2008.
Over six years.
Unbelivable how risky some people lives.

Cheers,
Stephan

Sent with Tapatalk 2.
 
S

StephanH

Member
Licensed User
Longtime User
additional you can phone the admin he should trace the logon process with SQL Profiler tool.
Also the error could be found in the Windows security log.

Regards,
Stephan

Sent with Tapatalk 2.
 
cirollo

cirollo

Active Member
Licensed User
Longtime User
this is the error message

Hi Stephan, in attached the error msg from b4ppc
 

Attachments

  • error_sql.jpg
    error_sql.jpg
    91.5 KB · Views: 258
S

StephanH

Member
Licensed User
Longtime User
I cannot read italiano :)
But it seems the connection to the instance is done.
There are no rights on the database?
The timeout expires message can have some reasons.

What say the Admin of the SQL Server?
Did he traced your logon with SQL Profiler?
He can filter your connection.

The message let me think the instance is driven in mixed mode authenticaton.

Please let check the userrights on the database.
You need minimal read Access.
If there are stored procedures reffering to System database you need also read on System database.
Let me know what error show the Profiler trace.

Regards,
Stephan

Sent with Tapatalk 2.
 
S

StephanH

Member
Licensed User
Longtime User
Again, I saw in my recommentation above I used the wrong delimiter.

Try IP, port in the connection string if the port isn't 1344 for the named instance. Sorry for this.

This can refuse the server connection also.

Right:
IP,Port

Wrong:
IP\Name, Port

You have to check database rights again and trace the try to connect.

Stephan

Sent with Tapatalk 2.
 
cirollo

cirollo

Active Member
Licensed User
Longtime User
connection successfull but...

Hi

finally I made the connection from a winCE PDA using in SQL 2008 the main instance and installing on the device the proper SQLClient CAB.

But, after doing the last operation, now I see the connection, but I get this error message:

An error occured on sub _mainmodule_app_start.

The database file is locked
database is locked
Continue?

This is my sub app_start, I think is related to the table rilevazioni that is onboard the device (is a sqllite table)
Infact, my program uses a local table to store data, that are transferred to the remote (network) sql at the user wish.
Any idea about this error?

B4X: 
Sub App_Start
   CreateConnection      
   Main.Show
   rigasel=""
   gestlotti="NO"
   svart="NO"
   gestubi="NO"
   ' leggiamo il numero del terminalino
   ' leggo dal file di testo i parametri per comporre la stringa di connessione al db remoto
   If FileExist ("param.txt") = True Then
      FileOpen (c1,"Param.txt",cRead ,, cASCII)
      r = FileRead (c1)   
      Do Until r = EOF
         Select SubString (r,0,3)
         Case "NUM"
            numterm = SubString (r,3,StrLength (r)-3)
         Case "LOT"
            gestlotti = SubString (r,3,StrLength (r)-3)
         Case "SVA"
            svart = SubString (r,3,StrLength (r)-3)
         Case "UBI"
            gestubi = SubString (r,3,StrLength (r)-3)
         Case "SVR"
            SVR = SubString (r,3,StrLength (r)-3)
         Case "USR"
            USR = SubString (r,3,StrLength (r)-3)
         Case "PWD"
            PWD = SubString (""&r,3,StrLength (r)-3)
         Case "DBN"
            DBN = SubString (""&r,3,StrLength (r)-3)
         Case "AZI"
            AZI = SubString (""&r,3,StrLength (r)-3)
         Case "MAG"
            MAG = SubString (""&r,3,StrLength (r)-3)
         Case "SER"
            SER = SubString (""&r,3,StrLength (r)-3)
         End Select
         r = FileRead (c1)
      Loop
      FileClose (c1)
   End If
   If SVR <> "NO" Then
      'apro la connessione con il db remoto
      stringaconn="Persist Security Info=False;Integrated Security=False;Server="&SVR &",1433;initial catalog=" &DBN &";user id="&USR &";password="&PWD&";"
   '   stringaconn="Data Source=192.168.7.201,1433;Network Library=DBMSSOCN;initial catalog=" &DBN &";user id="&USR &";password="&PWD&";"
   '   Msgbox(stringaconn)
      Sql1.New1
      If Sql1.Open(stringaconn) Then
         LblDb.Visible = True
         LblDb.Width = 230
         LblDb.Text = "Server: "&SVR&":1433 - DB: "&DBN&" ("&AZI&")"
   '      Msgbox("Conessione al DB Stabilita con Successo!!!","My First Program",cMsgboxYesNo,cMsgboxHand)
      Else    
   '         Msgbox("Impossibile Connettersi al DB!!!",progname,cMsgboxYesNo,cMsgboxHand)
   '      Msgbox(sql1.LastError,"")
         Msgbox("Impossibile Connettersi al DB!!!")
         main.Close
      End If
   End If
   label15.Text=label15.Text&" "&numterm
   ' riempiamo il combobox dell'operatore
   CmbOpe.Add ("...")
   CmbOpe.Add ("1")
   CmbOpe.Add ("2")
   CmbOpe.Add ("3")
   CmbOpe.Add ("4")
   CmbOpe.Add ("5")
   CmbOpe.Add ("6")
   CmbOpe.Add ("7")
   CmbOpe.Add ("8")
   CmbOpe.Add ("9")
   CmbOpe.SelectedIndex=1
   'nascondiamo il combo operatore
   label14.Visible=False
   cmbope.Visible=False
   'nascondiamo la label export
   label18.Visible=False
   ' verifichiamo se esiste la tabella
   Command1.CommandText = "SELECT name FROM sqlite_master WHERE type = 'table' AND name='t_rilevazioni'"
   Reader1.New1
   Reader1.Value = Command1.ExecuteReader
   If Reader1.ReadNextRow = False Then
      ' No table with this name in the database.
      ' Create one.
       Reader1.Close
      If gestlotti="NO" Then
         Command1.CommandText = "CREATE TABLE t_rilevazioni (Id STRING PRIMARY KEY,Barcode STRING,Qta STRING,Ubi STRING,Seriale STRING)"
      Else
         Command1.CommandText = "CREATE TABLE t_rilevazioni (Id STRING PRIMARY KEY,Barcode STRING,Lotto STRING,Qta STRING,Ubi STRING,Seriale STRING)"
      End If
      Command1.ExecuteNonQuery
   Else
      Reader1.Close
'      Command1.CommandText = "DROP TABLE t_rilevazioni"
'        Command1.ExecuteNonQuery
   End If
End Sub
 
You must log in or register to reply here.
Top