Connect SQL Server Express from Pocket PC

Connect SQL Server Express from Pocket PC

• Mike Ogilvie

  DataSet would fetch all rows from the server. That's OK of you have couple thousands rows, but it might take a while if you have significantly more. Not to mention you can run out of memory. Also emulator is usually slower than even low end device, so you should test performance on actual hardware.

  You can limit number of rows in SQL query or you can use DataReader which reads one row at a time (and reading one row is faster than reading all rows).

  I'm not sure about HasRows support. Simply call DataReader.Read() and see if it returns true. If it does, you have a row which you can access. Note: DataReader needs reliable connection to the server.

  
  
  

• jjirra

  Hi Ilya

  Ok. I have try to use DataReader.Read() to check but still can't get what I want. This only return true and false but it did not check with the table whether the login id/password exist or not. So got any idea

  I have try DataReader.Item("login") = name but I get error "Invalid attempt to read when no data is present". may I know what wrong

  Thanks.

  
  

• Project Ebiru

  Hi...

  Now I can connect to the SQL server after I re-install my server.

  Now I got another question here;

  I use dataset for my program but when I run it, I wait more than 10 min it can't show the form in the emulator May I know what is the problem But when I change not to use dataset, it can run more faster tham using dataset.

  How I want ot get the return result from the sql query for the login dialog Now I use DataReader, but it seem like can't support instance.HasRows. Got other method can do it

  Thanks.

  
  

• Luis Simões

  I'm using private network. The Login/Password you mentioned is windows password or the SQL Server User ID/Password Can I just use windows authentication

  For my local PC, I did'nt use any password to login my PC. How about if I set the trusted connection to false in the connection string, will it able to access

  
  

• sang

  In order for emulator to get network connection you need to cradle it (only good to connect to SQL running on the same PC as emulator) or configure virtual NE2000 adapter as described above. There's no need to scan several IPs and ports, only server's IP and SQL Server port matters.

  As to SQL Express configuration, please post to SQL Express forum.

  
  
  

• burrowsUW

  It's whatever your server is configured to accept. As far as I remember SQL Express only works with windows authentication, so you have to use that and it would be windows login/password. Even though windows authentication protocol will be used, you have to provide credential in the connection string. There's no login procedure on device, so SQL Client can't grab credentials from the system like desktop would do.

  If you don't use any password, you still have user ID. Just use that in connection string.

  
  
  

• Arun Shrivats

  Is 10.1.1.5 correct server IP and 2301correct port SQL is running on If so, does VxUtil report "Port 2301is open" while scanning ports (from 2301 to 2301 for speed) on server's IP of 10.1.1.5

  There's no point experimenting with connection strings until you have confirmed physical connection, without it no connection string would ever work.

  
  
  

• ahorse

  Good, we're almost done.

  It appears you're using empty string as user ID. You'd need to provide valid user ID and password in connection string as follows:

  ...User ID=YourUserId;Password=YourPassword;Integrated Security=SSPI;...

  "YourUserId" should be valid user ID with rights to use SQL Express and
  "YourPassword" should be valid password for this user ID.

  If you're on corporate network, please contact your network administrator for that information.

  For private network you can create/manage user accounts in Control Panel/User Accounts applet.

  I would suggest trying your own login/password as a test.

  
  
  

• Brat000012001

  Ok, I have solve the Sql Express problem and I have also configure the virtual NE2000 adapter. But still get the same error.

  I have change the connection string to this:

  Dim strConnection As String = "Server=10.1.1.5,2301;Initial Catalog=DrinksOrder;User ID=;Password=;Integrated Security=SSPI;"

  still having any error with this connection string

  Here myu coding:

  Imports System.IO

  Imports System.Data

  Imports System.Data.SqlClient

  Imports System.Math

  Imports System.Drawing

  Imports System

  Public Class Form1

  I try this three connection string but failed.

  'Private strConnection As String = "Persist Security Info=False;Integrated Security=False;Server=10.1.1.5,2301;Initial Catalog=DrinksOrder;"

  'Dim strConnection As String = "Server=10.1.1.5,2301;Initial Catalog=DrinksOrder;User ID=;Password=;Integrated Security=SSPI;"

  This connection string will getting error :spcified server not found : Order

  Dim strConnection As String = "Server=10.1.1.5;Data Source = Order;Database = DrinksOrder;Integrated Security=SSPI;"

  Dim strQuery As String

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  ReadOrderData()

  End Sub

  Private Sub ReadOrderData()

  Using connection As New SqlConnection(strConnection)

  Dim i As Integer

  Try

  connection.Open()

  MsgBox("Successfuly Connected.", MsgBoxStyle.Information, "Success")

  Catch ex As SqlException

  For i = 0 To ex.Errors.Count - 1

  MsgBox("Index #" & i.ToString() & ControlChars.NewLine _

  & "Message: " & ex.Errors(i).Message & ControlChars.NewLine _

  & "LineNumber: " & ex.Errors(i).LineNumber & ControlChars.NewLine _

  & "Source: " & ex.Errors(i).Source & ControlChars.NewLine _

  & "Procedure: " & ex.Errors(i).Procedure & ControlChars.NewLine)

  Next i

  End Try

  End Using

  End Sub

  End Class

  Any idea with this code

  Thanks.

  
  

• LuckyCarl

  Hi Ilya,

  Thanks again for you reply.

  After I configure the Enable the SQL TCP/IP, I just discover that I can't connect to the server by using SQL Management Studio Express. Is it I configure in the wrong way I follow the SQL Express readme to enable it.

  I have installed activesync 4.1 and VxUtil. Do I need to run the activesync when running port scan I have try to scan a few IP with port rang 1000-1200 but no result come out. Is it I miss up any step when configure the SQL TCP/IP

  Please advise.

  
  

• thedewd

  OK.. Now I think I can connect to the server but getting message :

  Login failed for user ". The user in not associated with a trusted SQL Server connection.

  Any idea with this Where can I asscociated to the trusted SQL Server connection

  Thanks.

  
  

• APMaio

  No, it’s not. First of all, 127.0.0.1 is a loop back address also known as “localhost”. It's pointing to device itself and not to the server, so connection is impossible because SQL is not running on device. You should use actual server name or IP instead. Next, named instance should be removed, only port server is on should be used:

  Server=ActualServerNameOrIPHere,1026

  "ActualServerNameOrIPHere" can not be localhost or 127.x.x.x

  As you’re using emulator, you need to configure network adapter on it. Please see this on how to do that:

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=375028&SiteID=1

  Before even trying to connect, please make sure VxUtils port scan tool clearly states: “Port 1026 is opened” while scanning SQL Server host by name or IP assuming you have configured SQL Express to listen on port 1026. If you don’t see that, connection attempts are guaranteed to fail because you do not have network connection to the SQL Server.

  
  
  

• Dennis Vys

  Thank you for your reply. It hel me a lot. But after i try coonect to the SQL Server, it failed.

  My connection string :

  Dim strConnection As String = "Server=127.0.0.1\SQLEXPRESS,1026;Initial Catalog=DrinksOrder;User ID=;Password=;Integrated Security=SSPI;"

  Is it correct

  I have configure the SQL Server and enable the TCP/IP. I try to run it on Pocket PC 2003 Emulator. If I run it on emulator without bluetooth dongle, will it able to run

   

  The error get from the SQLException is

  Index #0

  Message: SQL Server Does not exist or access denied.

  Line Number: 0

  Source : .Net SqlClient Data Provider

  Procedure : ConnectionOpen (Connect())

   

  Any idea...

  Thanks for advance.

   

   

  
  

• A Casa do Java

  You need to use SQL Client just like on desktop. Physical media (Ethernet, Wi-Fi, BT) is irrelevant as long as you're running TCP/IP over it. Your server should be configured to use TCP/IP as well. SQL Express has this disabled by default, so you'd need to enable it.

  Please see this for general connection issues:

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=335660&SiteID=1

  
  
  
• Connect SQL Server Express from Pocket PC