connection memory

How much memory does each connection to SQL Server hold ? Is that stored in
the buffer pool area ?Connection memory is stored both in the buffer pool and the mem-to-leave
area. You can get an idea of how much memory each connection is using by
querying the sysprocesses table (see the memusage column), but keep in mind
that some of that memory is shared so it's impossible (as far as I know) to
get an exact number. You can also get some helpful information about
connection memory from DBCC MEMORYSTATUS.
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Hassan" <hassan@.hotmail.com> wrote in message
news:uingOA98HHA.4612@.TK2MSFTNGP03.phx.gbl...
> How much memory does each connection to SQL Server hold ? Is that stored
> in the buffer pool area ?
>
>|||It costs 28KB to only establish a connection to SQL Server 2005.
--
Ekrem Önsoy
MCBDA, MCTS: SQL Server 2005, MCSD.Net, MCSE, MCT
"Hassan" <hassan@.hotmail.com> wrote in message
news:uingOA98HHA.4612@.TK2MSFTNGP03.phx.gbl...
> How much memory does each connection to SQL Server hold ? Is that stored
> in the buffer pool area ?
>
>sqlsql

connection issues when running multiple instances of same application

I am running multiple instance of the same application. This application is connecting to the database and running stored procedures using ADODB (all code examples are taken from msado15).

The problem is that somehow these two applications are sharing something either with the connection or commands.

For instance if the two instances are in the following function at the same time then they both thow an SEH exception:

Code Snippet

inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {

struct _Recordset * _result = 0;

HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

return _RecordsetPtr(_result, false);

}

The exception that occurs is: "First-chance exception ...: 0xC0000005: Access violation reading location 0x00000068."

And afterwords when they go to release the command an error occurs on

First-chance exception at 0x4de4120c in IpsEngine.exe: 0xC0000005: Access violation reading location 0xcccccccc.

Code Snippet

inline void _bstr_t::Data_t::_Free() throw()

{

if (m_wstr != NULL) {

::SysFreeString(m_wstr);

}

if (m_str != NULL) {

delete [] m_str;

}

}

This is being called from.

Code Snippet

inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {

HRESULT _hr = putref_ActiveConnection(ppvObject);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

}

The exception that occurs: First-chance exception at ...: 0xC0000005: Access violation reading location 0xcccccccc.

Similarly when one instance releases a command using

Code Snippet

inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {

HRESULT _hr = putref_ActiveConnection(ppvObject);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

}

The second instance of the application fails when running the command at the exact same time.

Code Snippet

inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {

struct _Recordset * _result = 0;

HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

return _RecordsetPtr(_result, false);

}

The command and connections are not static and there should be completely seperate instances of these for each instance of the application. Does anybody know why this may be happening. Any help would be appreciated. Thanks in advance.

All of these access issues are with pointers being passed into these functions. As such, we don't have much context with which to help you with these issues. Any issue could be causing these problems including: double freeing, not allocating memory elsewhere or failure to properly initialize memory, failure to check return codes of functions that return memory, etc. In these cases, we need to see the context surrounding the variables that are causing the AV, in for example: for Command15::Execute, RecordsAffected and Parameters would be the variables of interest, we would need to see how they are allocated, initialized, and used prior to this function call in the execution context of a failure, and how they are allocated, initialized, and used prior to this function call in the execution context of success. The most likely cause of this AV is failure to handle error conditions coming back from functions that allocate memory on success.

Thanks,

John

|||

But when I run only one instance of the application at a time it works fine, I have been running it already for a long time and never had a problem. How is it that running two seperate instances of the application could cause this error. The memory allocation would not change just because there are two instances? would it?

connection issues when running multiple instances of same application

I am running multiple instance of the same application. This application is connecting to the database and running stored procedures using ADODB (all code examples are taken from msado15).

The problem is that somehow these two applications are sharing something either with the connection or commands.

For instance if the two instances are in the following function at the same time then they both thow an SEH exception:

Code Snippet

inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {

struct _Recordset * _result = 0;

HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

return _RecordsetPtr(_result, false);

}

The exception that occurs is: "First-chance exception ...: 0xC0000005: Access violation reading location 0x00000068."

And afterwords when they go to release the command an error occurs on

First-chance exception at 0x4de4120c in IpsEngine.exe: 0xC0000005: Access violation reading location 0xcccccccc.

Code Snippet

inline void _bstr_t::Data_t::_Free() throw()

{

if (m_wstr != NULL) {

::SysFreeString(m_wstr);

}

if (m_str != NULL) {

delete [] m_str;

}

}

This is being called from.

Code Snippet

inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {

HRESULT _hr = putref_ActiveConnection(ppvObject);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

}

The exception that occurs: First-chance exception at ...: 0xC0000005: Access violation reading location 0xcccccccc.

Similarly when one instance releases a command using

Code Snippet

inline void Command15::PutRefActiveConnection ( struct _Connection * ppvObject ) {

HRESULT _hr = putref_ActiveConnection(ppvObject);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

}

The second instance of the application fails when running the command at the exact same time.

Code Snippet

inline _RecordsetPtr Command15::Execute ( VARIANT * RecordsAffected, VARIANT * Parameters, long Options ) {

struct _Recordset * _result = 0;

HRESULT _hr = raw_Execute(RecordsAffected, Parameters, Options, &_result);

if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));

return _RecordsetPtr(_result, false);

}

The command and connections are not static and there should be completely seperate instances of these for each instance of the application. Does anybody know why this may be happening. Any help would be appreciated. Thanks in advance.

All of these access issues are with pointers being passed into these functions. As such, we don't have much context with which to help you with these issues. Any issue could be causing these problems including: double freeing, not allocating memory elsewhere or failure to properly initialize memory, failure to check return codes of functions that return memory, etc. In these cases, we need to see the context surrounding the variables that are causing the AV, in for example: for Command15::Execute, RecordsAffected and Parameters would be the variables of interest, we would need to see how they are allocated, initialized, and used prior to this function call in the execution context of a failure, and how they are allocated, initialized, and used prior to this function call in the execution context of success. The most likely cause of this AV is failure to handle error conditions coming back from functions that allocate memory on success.

Thanks,

John

|||

But when I run only one instance of the application at a time it works fine, I have been running it already for a long time and never had a problem. How is it that running two seperate instances of the application could cause this error. The memory allocation would not change just because there are two instances? would it?

Connection contexts and Lock Resources..

Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend to
the AWE if need be as well or is it just limited to non AWE memory.
Thank you..Hassan
Yes , locks 'mean' memory. Can you elaborate about AWE question?
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23wVbbIiMHHA.960@.TK2MSFTNGP04.phx.gbl...
> Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend
> to the AWE if need be as well or is it just limited to non AWE memory.
> Thank you..
>|||The ONLY thing that can live in memory above 2GB (or 3GB if /3GB is set) on
a 32 bit machine is data that is cached in the data buffer pool.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23wVbbIiMHHA.960@.TK2MSFTNGP04.phx.gbl...
> Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend
> to the AWE if need be as well or is it just limited to non AWE memory.
> Thank you..
>|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eMcjfBoMHHA.992@.TK2MSFTNGP06.phx.gbl...
> The ONLY thing that can live in memory above 2GB (or 3GB if /3GB is set)
> on a 32 bit machine is data that is cached in the data buffer pool.
> --
> Andrew J. Kelly SQL MVP
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23wVbbIiMHHA.960@.TK2MSFTNGP04.phx.gbl...
>

Connection contexts and Lock Resources..

Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend to
the AWE if need be as well or is it just limited to non AWE memory.
Thank you..
Hassan
Yes , locks 'mean' memory. Can you elaborate about AWE question?
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23wVbbIiMHHA.960@.TK2MSFTNGP04.phx.gbl...
> Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend
> to the AWE if need be as well or is it just limited to non AWE memory.
> Thank you..
>
|||The ONLY thing that can live in memory above 2GB (or 3GB if /3GB is set) on
a 32 bit machine is data that is cached in the data buffer pool.
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23wVbbIiMHHA.960@.TK2MSFTNGP04.phx.gbl...
> Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend
> to the AWE if need be as well or is it just limited to non AWE memory.
> Thank you..
>
|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eMcjfBoMHHA.992@.TK2MSFTNGP06.phx.gbl...
> The ONLY thing that can live in memory above 2GB (or 3GB if /3GB is set)
> on a 32 bit machine is data that is cached in the data buffer pool.
> --
> Andrew J. Kelly SQL MVP
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23wVbbIiMHHA.960@.TK2MSFTNGP04.phx.gbl...
>

Connection contexts and Lock Resources..

Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend to
the AWE if need be as well or is it just limited to non AWE memory.
Thank you..Hassan
Yes , locks 'mean' memory. Can you elaborate about AWE question?
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23wVbbIiMHHA.960@.TK2MSFTNGP04.phx.gbl...
> Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend
> to the AWE if need be as well or is it just limited to non AWE memory.
> Thank you..
>|||The ONLY thing that can live in memory above 2GB (or 3GB if /3GB is set) on
a 32 bit machine is data that is cached in the data buffer pool.
--
Andrew J. Kelly SQL MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23wVbbIiMHHA.960@.TK2MSFTNGP04.phx.gbl...
> Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend
> to the AWE if need be as well or is it just limited to non AWE memory.
> Thank you..
>|||Thanks Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eMcjfBoMHHA.992@.TK2MSFTNGP06.phx.gbl...
> The ONLY thing that can live in memory above 2GB (or 3GB if /3GB is set)
> on a 32 bit machine is data that is cached in the data buffer pool.
> --
> Andrew J. Kelly SQL MVP
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23wVbbIiMHHA.960@.TK2MSFTNGP04.phx.gbl...
>> Is this stored in the buffer pool in SQL 2000 ? And if so, does it extend
>> to the AWE if need be as well or is it just limited to non AWE memory.
>> Thank you..
>

Connection broken on localhost

Following error occurs when I execute stored procedure sp_updatestats to update
statistics:
...
Updating dbo.AttachmentFolderDefinition
Updating dbo.Branch
...
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead
(WrapperRead()). Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
I am running the procedure from the SQL Query Analyzer locally from the server.
Sometimes the same error occurs when I do a backup or when I run other stored
procedures.
The SQL Server log and Event Log didn't help.
Any ideas ?
Thanks
Radek
Radek,
You are probably running into this issue:
FIX: General network error when you try to back up or restore a SQL
Server database on a computer that is running Windows Server 2003
http://support.microsoft.com/?id=827452
Try using TCP/IP as your network library instead of Named Pipes.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Radek Pospisil wrote:
> Following error occurs when I execute stored procedure sp_updatestats to update
> statistics:
> ...
> Updating dbo.AttachmentFolderDefinition
> Updating dbo.Branch
> ...
> [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionRead
> (WrapperRead()). Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
>
> I am running the procedure from the SQL Query Analyzer locally from the server.
> Sometimes the same error occurs when I do a backup or when I run other stored
> procedures.
> The SQL Server log and Event Log didn't help.
> Any ideas ?
> Thanks
> Radek
|||You are right. Thanks a lot.
Radek
Mark Allison <marka@.no.tinned.meat.mvps.org> wrote in
news:OdUmcoc4EHA.4092@.TK2MSFTNGP14.phx.gbl:

> Radek,
> You are probably running into this issue:
> FIX: General network error when you try to back up or restore a SQL
> Server database on a computer that is running Windows Server 2003
> http://support.microsoft.com/?id=827452
> Try using TCP/IP as your network library instead of Named Pipes.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>

Connection Breaks on StProc text UPDATE

Hi,
We have a simple stored procedure that has a text parameter, among others.
Inside the procedure the text parameter is assigned to a text column in a
simple SET clause of a simple UPDATE statement. We pass a 15 character
string as an argument for the parameter, and even though the string is
stored to the table, we get this error message (running in Query
Analyzer)...
*-*-*-*
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
(CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
*-*-*-*
Any of the following make the error message go away...
1) Pass NULL as the argument
2) Comment out the assignment in the SET clause
3) Change the parameter to varchar(8000)
Anyone have any ideas?
--
Thank you,
Daniel Jameson
Children's Oncology Group
http://www.childrensoncologygroup.orgCan you show the CREATE TABLE statement and the CREATE PROCEDURE code?
Also, tell us the result of SELECT @.@.VERSION
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"danjam" <djameson@.childrensoncologygroup.org> wrote in message
news:e6y2#eMxDHA.3416@.tk2msftngp13.phx.gbl...
> Hi,
> We have a simple stored procedure that has a text parameter, among others.
> Inside the procedure the text parameter is assigned to a text column in a
> simple SET clause of a simple UPDATE statement. We pass a 15 character
> string as an argument for the parameter, and even though the string is
> stored to the table, we get this error message (running in Query
> Analyzer)...
> *-*-*-*
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> (CheckforData()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> *-*-*-*
> Any of the following make the error message go away...
> 1) Pass NULL as the argument
> 2) Comment out the assignment in the SET clause
> 3) Change the parameter to varchar(8000)
> Anyone have any ideas?
> --
> Thank you,
> Daniel Jameson
> Children's Oncology Group
> http://www.childrensoncologygroup.org
>|||There are some known bugs that cause this or similar errors - it would
help to see your stored procedure. It could be an issue with UNION,
with an INSTEAD OF trigger, certain query structures, or an unknown
bug... It does sound like a bug, though.
SK
danjam wrote:
>Hi,
>We have a simple stored procedure that has a text parameter, among others.
>Inside the procedure the text parameter is assigned to a text column in a
>simple SET clause of a simple UPDATE statement. We pass a 15 character
>string as an argument for the parameter, and even though the string is
>stored to the table, we get this error message (running in Query
>Analyzer)...
>*-*-*-*
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
>(CheckforData()).
>Server: Msg 11, Level 16, State 1, Line 0
>General network error. Check your network documentation.
>Connection Broken
>*-*-*-*
>Any of the following make the error message go away...
> 1) Pass NULL as the argument
> 2) Comment out the assignment in the SET clause
> 3) Change the parameter to varchar(8000)
>Anyone have any ideas?
>
>|||Steve,
Thank you for responding. I have posted additional info under Aaron's post.
I do have an INSTEAD of trigger, but there are no UNION's involved.
--
Thank you,
Daniel Jameson
Children's Oncology Group
http://www.childrensoncologygroup.org
"Steve Kass" <skass@.drew.edu> wrote in message
news:uIFJ5pMxDHA.2556@.TK2MSFTNGP10.phx.gbl...
> There are some known bugs that cause this or similar errors - it would
> help to see your stored procedure. It could be an issue with UNION,
> with an INSTEAD OF trigger, certain query structures, or an unknown
> bug... It does sound like a bug, though.
> SK
> danjam wrote:
> >Hi,
> >
> >We have a simple stored procedure that has a text parameter, among
others.
> >Inside the procedure the text parameter is assigned to a text column in a
> >simple SET clause of a simple UPDATE statement. We pass a 15 character
> >string as an argument for the parameter, and even though the string is
> >stored to the table, we get this error message (running in Query
> >Analyzer)...
> >
> >*-*-*-*
> >[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData
> >(CheckforData()).
> >Server: Msg 11, Level 16, State 1, Line 0
> >General network error. Check your network documentation.
> >
> >Connection Broken
> >*-*-*-*
> >
> >Any of the following make the error message go away...
> > 1) Pass NULL as the argument
> > 2) Comment out the assignment in the SET clause
> > 3) Change the parameter to varchar(8000)
> >
> >Anyone have any ideas?
> >
> >
> >
>

Connection between 2 databases in local sql server

Hi,

I have two different applications based on SQL server database.

In one of the applications I need to use the information stored in the second database, only for read purpose.

As I understand I need how some to make a link between the databases in order to share tables.

Can anybody advise me how to do that, if it is possible at all?

Thanks

Just use a fully qualified name i.e. Server.Database.Owner.Table

for example: Select * from MyServer.Northwind.dbo.Customers

Regards

Paul

|||

Hi Paul,

Thanks for your replay, but maybe I'm not explained myself as well.

Because I'm accessing the database through ODBC, and when I'm using the specific DSN only the tables which are belongs to the specific database are available to me.

That’s the reason I though to make a link between the databases.

There is an option to do that?

|||

The easy way would be to create a stored procedure and have it do the work for you.

The hard way would be to establish 2 connections and query the tables seperately. If you are using .NET you could them combine them in a DataSet as two DataTables and establish a DataRelation.

Regards

Paul

|||

Hi Paul

Unfortunately I'm not using .NET, I'm working with VB6.

I'll appreciate it if you can be more specific about the stored procedure, how can I access the tables in the other database.

Thanks again

|||

Something like:

Create Proc MyProcedure
AS
select * from Db1.dbo.Table1 as tb1

inner join Db2.dbo.OtherTable as tb2
on tb1.id = tb2.id

Set the command type in an ADO Command object to a Stored Procedure and the CommnandText to MyProcedure (the stored procedure name) and call the execute method.

Connectio to Remote SQL Server from another SQL Server Stored Proc

Hi,
I have a problem with a conection to Remote SQL Server Database wich have a
blank space in databese name.
Ex.
"Production ED"
I Can`t change the name of this data base.
I Need to connect this database with a Stored Procedure in another Server.
Both Databases are SQL Server 2000 Ent. Ed.
Would it help if you put the name in brackets?
[Production ED]
so you could do something like...
select * from
[Production ED].dbo.ProcedureName
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Esteban" <Esteban@.discussions.microsoft.com> wrote in message
news:7CA8D942-0B44-434C-BD4D-34E4231DC7E8@.microsoft.com...
> Hi,
> I have a problem with a conection to Remote SQL Server Database wich have
> a
> blank space in databese name.
> Ex.
> "Production ED"
> I Can`t change the name of this data base.
> I Need to connect this database with a Stored Procedure in another Server.
> Both Databases are SQL Server 2000 Ent. Ed.

Connectio to Remote SQL Server from another SQL Server Stored Proc

Hi,
I have a problem with a conection to Remote SQL Server Database wich have a
blank space in databese name.
Ex.
"Production ED"
I Can`t change the name of this data base.
I Need to connect this database with a Stored Procedure in another Server.
Both Databases are SQL Server 2000 Ent. Ed.Would it help if you put the name in brackets?
[Production ED]
so you could do something like...
select * from
[Production ED].dbo.ProcedureName
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Esteban" <Esteban@.discussions.microsoft.com> wrote in message
news:7CA8D942-0B44-434C-BD4D-34E4231DC7E8@.microsoft.com...
> Hi,
> I have a problem with a conection to Remote SQL Server Database wich have
> a
> blank space in databese name.
> Ex.
> "Production ED"
> I Can`t change the name of this data base.
> I Need to connect this database with a Stored Procedure in another Server.
> Both Databases are SQL Server 2000 Ent. Ed.

connecting to sql server from mobile device

How can I connect to sql server from a mobile device?
the server is on pc and my app is on the device.
I want to perform commands and stored procedures which will be performed online on the pc and get immediate results.
is there any way of achiving that?

You will need to add a reference to SQLClient in your project.
Once this is done its the same as if you were connecting to sqlserver on the full framework with ASP.net or VB.net
here is some sample code

Imports System.Data.Common
Imports System.Data.SqlClient
Private m_connString As String = "user id=sa;password=;initial catalog=Northwind;Server=xxx.xxx.xxx.xxx;"
Private m_ds As New DataSet
Dim l_sqlConn As SqlConnection
Dim l_da As DataAdapter
Dim l_ds As DataSet
Dim l_sqlStmt As String
Try
l_sqlConn = New SqlConnection(m_connString)
l_sqlConn.Open()
l_sqlStmt = "SELECT * FROM tbl_Users ORDER BY FullName"
l_da = New SqlDataAdapter(l_sqlStmt, l_sqlConn)
m_ds = New DataSet
l_da.Fill(m_ds)

Catch ex As SqlException
Dim l_sqlerr As SqlError
For Each l_sqlerr In ex.Errors
MsgBox(l_sqlerr.Message)
Next
Finally
If l_sqlConn.State = ConnectionState.Open Then
l_sqlConn.Close()
l_sqlConn = Nothing
End If
End Try
Dim l_cmd As SqlCommand = l_sqlConn.CreateCommand()
l_cmd.CommandType = CommandType.StoredProcedure
l_cmd.CommandText = "<Stored Procedure Name>"
l_cmd.Parameters.Add(New SqlParameter("UnitOutDate", SqlDbType.DateTime))
l_cmd.Parameters("UnitOutDate").Value = Date.Now
l_cmd.executeNonQuery()

|||It's all good and pretty except for one little thing:
when i try to open the connection i get a "PlatformNotSupportedException"
is there any reason that exception could appears?|||on the emulator or on the device|||

let me specify a little:
I have a device which has a microsoft windows ce .net version 4.2 system.
i try to connect it to sqlserver 2000 that is on a pc server(win 2000 for servers). I create SqlConnect object on the device with the connection string
"Persist Security Info=False;Integrated Security=false; database=northwind; server=xxx.xxx.xxx.xxx; Connect Timeout=30; User ID=testing; Password=test;"
so far so good, but when i perform the function Open(on the device itself), I get the "PlatformNotSupportedException".
and i just cannot figure out why.

|||hmmm it sounds as if something is missing on the device like a library.
Try testing it on the emulator and see if you get the same error|||

That usually means DB collation you're using is not supported on particular device. You can change collation of this DB or get localized device which supports locale you’re using.

|||You hit the correct spot, it really is DB collation.
I created another database on the same server and gave it's collation Latin1_General_CI_AS and tried again and it worked :-)

which brings me to this question:
is there any way to tell the connection object what the database collation is? because I dont think i'm allowed to convert the database to Latin1_General_CI_AS collation.
or any other way to solve this mess...|||you can specify the locale identifier in the connection string.
see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlceconnectionclassconnectionstringtopic.asp

-Darren Shaffer
.NET Compact Framework MVP|||

Darren Shaffer wrote:

you can specify the locale identifier in the connection string.
see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlceconnectionclassconnectionstringtopic.asp

-Darren Shaffer
.NET Compact Framework MVP

I have this same problem (platformnotsupported) and I don't understand your answer. Problem is with SqlConnection and you say that we must add locale identifier in connection string to SqlCeConnection?
|||

As you correctly noted this is not going to work with SQL Client as it’s about SQL CE. So you have two choices:

1. Change database collation to something which is supported on device.

2. Get device which supports your database collation.

|||

Ilya Tumanov wrote:

As you correctly noted this is not going to work with SQL Client as it’s about SQL CE. So you have two choices:

1. Change database collation to something which is supported on device.

2. Get device which supports your database collation.

I know
Ad 1. No, i can't change collation of my database.
Ad.2. I can't get device which support my database collation (it is Symbol MC9090 with Win CE 5.0 and my database have Polish_CI_AS collation). Works great when I change colllation to Latin1_general but i can't do this with my main database.

Is there an other option? I try RDA but problem with charsets exists, Polish chars ? ? ? ń etc. changes to strange symbols, its useless.

connecting to sql server from mobile device

How can I connect to sql server from a mobile device?
the server is on pc and my app is on the device.
I want to perform commands and stored procedures which will be performed online on the pc and get immediate results.
is there any way of achiving that?

You will need to add a reference to SQLClient in your project.
Once this is done its the same as if you were connecting to sqlserver on the full framework with ASP.net or VB.net
here is some sample code

Imports System.Data.Common
Imports System.Data.SqlClient
Private m_connString As String = "user id=sa;password=;initial catalog=Northwind;Server=xxx.xxx.xxx.xxx;"
Private m_ds As New DataSet
Dim l_sqlConn As SqlConnection
Dim l_da As DataAdapter
Dim l_ds As DataSet
Dim l_sqlStmt As String
Try
l_sqlConn = New SqlConnection(m_connString)
l_sqlConn.Open()
l_sqlStmt = "SELECT * FROM tbl_Users ORDER BY FullName"
l_da = New SqlDataAdapter(l_sqlStmt, l_sqlConn)
m_ds = New DataSet
l_da.Fill(m_ds)

Catch ex As SqlException
Dim l_sqlerr As SqlError
For Each l_sqlerr In ex.Errors
MsgBox(l_sqlerr.Message)
Next
Finally
If l_sqlConn.State = ConnectionState.Open Then
l_sqlConn.Close()
l_sqlConn = Nothing
End If
End Try
Dim l_cmd As SqlCommand = l_sqlConn.CreateCommand()
l_cmd.CommandType = CommandType.StoredProcedure
l_cmd.CommandText = "<Stored Procedure Name>"
l_cmd.Parameters.Add(New SqlParameter("UnitOutDate", SqlDbType.DateTime))
l_cmd.Parameters("UnitOutDate").Value = Date.Now
l_cmd.executeNonQuery()

|||It's all good and pretty except for one little thing:
when i try to open the connection i get a "PlatformNotSupportedException"
is there any reason that exception could appears?|||on the emulator or on the device|||

let me specify a little:
I have a device which has a microsoft windows ce .net version 4.2 system.
i try to connect it to sqlserver 2000 that is on a pc server(win 2000 for servers). I create SqlConnect object on the device with the connection string
"Persist Security Info=False;Integrated Security=false; database=northwind; server=xxx.xxx.xxx.xxx; Connect Timeout=30; User ID=testing; Password=test;"
so far so good, but when i perform the function Open(on the device itself), I get the "PlatformNotSupportedException".
and i just cannot figure out why.

|||hmmm it sounds as if something is missing on the device like a library.
Try testing it on the emulator and see if you get the same error

|||

That usually means DB collation you're using is not supported on particular device. You can change collation of this DB or get localized device which supports locale you’re using.

|||You hit the correct spot, it really is DB collation.
I created another database on the same server and gave it's collation Latin1_General_CI_AS and tried again and it worked :-)

which brings me to this question:
is there any way to tell the connection object what the database collation is? because I dont think i'm allowed to convert the database to Latin1_General_CI_AS collation.
or any other way to solve this mess...|||you can specify the locale identifier in the connection string.
see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlceconnectionclassconnectionstringtopic.asp

-Darren Shaffer
.NET Compact Framework MVP|||

Darren Shaffer wrote:

you can specify the locale identifier in the connection string.
see:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlservercesqlceconnectionclassconnectionstringtopic.asp

-Darren Shaffer
.NET Compact Framework MVP

I have this same problem (platformnotsupported) and I don't understand your answer. Problem is with SqlConnection and you say that we must add locale identifier in connection string to SqlCeConnection?|||

As you correctly noted this is not going to work with SQL Client as it’s about SQL CE. So you have two choices:

1. Change database collation to something which is supported on device.

2. Get device which supports your database collation.

|||

Ilya Tumanov wrote:

As you correctly noted this is not going to work with SQL Client as it’s about SQL CE. So you have two choices:

1. Change database collation to something which is supported on device.

2. Get device which supports your database collation.

I know
Ad 1. No, i can't change collation of my database.
Ad.2. I can't get device which support my database collation (it is Symbol MC9090 with Win CE 5.0 and my database have Polish_CI_AS collation). Works great when I change colllation to Latin1_general but i can't do this with my main database.

Is there an other option? I try RDA but problem with charsets exists, Polish chars ? ? ? ń etc. changes to strange symbols, its useless.