General Network error even good connection and small amount of data

Hy everyone,

I have a problem at pulling data on PDA using Sql Mobile 5 from Sql Server 2000.

It work s for most tables, except for two . Every table of those two has about 1000 records and every record about 150 bytes, so all data is about 150 kbytes.

When I connect to the Sql Server 2000 through LAN, no problem. But when I connect through GPRS - VPN, the error occurs. But, the PDA- phone connection is via Bluetooth and at ping no TimeOut appears, the replies are about 600-700 ms. When connecting with a desktop computer through GPRS no such problem occurs.

So , I don't know what could be the cause: the connection or the Sql Mobile which doesn't retry enough in case of error to transfer all data .

A specialist from the GPRS provider told me to use 3G phone , but , because the GPRS phones have already been bought, this would be a too big investment. So, I don't think this is a good idea in this moment .

I found similar problem on the net and a solution was to use Pooling = False. But , in Sql Mobile there is no pulling parameter available when making the connection string .

Any help is very precious to me ,

Mihai .

Hi Mihai,

Seems like you are using a GPRS phone to act like a bluetooth modem for a PDA. This is a tested scenario, and would work :) A couple of issues i can imagine right-away:

1. From the ping round trip time of 600 ms, i guess the synchronization must be timing-out at the TCP/IP level. Below are some time-out properties (HTTP &TCP/IP) of the merge replication and RDA programming objects, which can be set to optimize synchronization over various network bandwidths.

(HTTP)

ConnectionRetryTimeout: Specifies the time, in seconds, to continue to retry sending requests after an established connection has failed.

(TCP/IP)

ConnectTimeout: Specifies the time, in milliseconds, to wait for a connection to the server.

ReceiveTimeout: Specifies the time, in milliseconds, to wait for the response to a server request.

SendTimeout: Specifies the time, in milliseconds, to wait to send a request to the server.

When data is synchronized over a cellular or mobile network, requests sent to the server, and responses from the server can time out. This occurs when a low-bandwidth connection requires too much time to transmit the data. In your case, you have an overhead of the bluetooth transport as well! These time-outs would cause synchronization to fail, which you can solve by specifying a longer time-out value. Try setting a huge value (60000 ms) in your code for the TCP/IP Connect and Send/Recieve Timeouts on the merge replication/RDA object. This mostly should solve your problem.

2. Bluetooth DUN Profile - I assume you are using the Dial-Up Networking profile for the phone to act as a GPRS modem. Are you sure there are no pairing issues? During our tests, we faced a lot of issues with the bluetooth pairing - especially between WM 5.0 PDA's and third party mobile devices.

Let me know if this works out!

Regards,

Sravanth Aluru

|||

When inserting the parameters you specify for the connection string I get an error that says "Unknown connection option ..." .

And for the SqlConnection object there are no properties like those. Only PacketSize, ConnectionTimeout .

I forgot to say previously that I put in connection string "Packet Size=512" and "ConnectionTimeout=90". I tried even with ConnectionTimeout=180. No change.

Do I miss anything, any assembly declaration ?

Could give me another advice ?

Mihai.

|||

The parameters are available in SQL Mobile 3.1 Beta which shipped with Visual Studio 2005 SP1 and onwards.

Sorry for missing this information out in my earlier response!

Can you specify which version you are on?

/Sravanth

|||

I have Sql Mobile 3.00. So , now I am downloading it . I'll reply as soon as I have news.

Thanks a lot,

Mihai.

|||

I can't get sql mobile 3.1

I just installed VS Professional, then SP1. Only Sql mobile 3.0. Anyway, in VS when I try to add reference to System.Data.Sqlclient it shows 3.0 version .

I tried to connect with SendTimeout parameter in the connection string . But I get error which says it is unknown parameter.

I even installed Sql everywhere because I saw it was 3.1 version. Nothing .

I also installed 3_SQL Server 2005 Mobile Edition Server Tools ...

I don't know what else to do.

Any suggestion ?

Thank you,

Mihai .

|||

Yes, I see. It's TCP/IP level ... sorry .

Of course I won't have those parameters in connection string. But how can I work at stream level and use the SQL commands to get data ? Everywhere in docs, when using TcpClient class, the stream is used for sending/receiving data .

But, how do I do to use these TCP/IP parameters if I work at IP level, with sql connection and then sql commands ?

Please give me some advices , I'm a rookie in working with these protocols.

Thanks a lot,

Mihai.

|||

This is a sample of how I get data:

SqlConnection s2000conn = new SqlConnection("Server=10.1.1.10 ; Connection Timeout=60 ; Packet Size = 512 ; UId=user;Pwd=pwd;Database=GestSC");

try

{

s2000conn.Open();

}

catch (SqlException eroare)

{

MessageBox.Show("Connection error", "Eroare Sql");

return;

}

SqlCommand sqlcoms = new SqlCommand();

sqlcoms.CommandText = "SELECT * FROM part ";

SqlDataAdapter da_part = new SqlDataAdapter(sqlcoms.CommandText, s2000conn);

DataSet ds_part = new DataSet();

DataTable dt_part = ds_part.Tables.Add("downpart");

da_part.Fill(ds_part, "downpart");

.....

Then I insert this data in Sql Mobile database .

But how would I use TcpClient ?

TcpClient tc = new TcpClient();

tc.SendTimeout = 90;

And then ?

Thank you ,

Mihai .

|||

Man ,

Is it that simple that it doesn't really worth the effort or you have no answer ?

Can you say smth ?

Thanks anyway,

Mihaila.

|||

Hy all,

I solved the problem by breaking the results into many smaller packets.

First I counted the total number of records and then Fill-ed the cursor with a for-end loop. Simple, but initially I wanted to work in the default mode, not to write code.

And it works.

Finally I heared from the GPRS provider that the GPRS allows small packets of data (maximum 2048 for Packet Size preffered) and the transport of data is not very reliable, so small amount of data should be transfered at one time.

Thank you anyway for all your replies,

Mihaila.

General Network error even good connection and small amount of data

Hy everyone,

I have a problem at pulling data on PDA using Sql Mobile 5 from Sql Server 2000.

It work s for most tables, except for two . Every table of those two has about 1000 records and every record about 150 bytes, so all data is about 150 kbytes.

When I connect to the Sql Server 2000 through LAN, no problem. But when I connect through GPRS - VPN, the error occurs. But, the PDA- phone connection is via Bluetooth and at ping no TimeOut appears, the replies are about 600-700 ms. When connecting with a desktop computer through GPRS no such problem occurs.

So , I don't know what could be the cause: the connection or the Sql Mobile which doesn't retry enough in case of error to transfer all data .

A specialist from the GPRS provider told me to use 3G phone , but , because the GPRS phones have already been bought, this would be a too big investment. So, I don't think this is a good idea in this moment .

I found similar problem on the net and a solution was to use Pooling = False. But , in Sql Mobile there is no pulling parameter available when making the connection string .

Any help is very precious to me ,

Mihai .

Hi Mihai,

Seems like you are using a GPRS phone to act like a bluetooth modem for a PDA. This is a tested scenario, and would work :) A couple of issues i can imagine right-away:

1. From the ping round trip time of 600 ms, i guess the synchronization must be timing-out at the TCP/IP level. Below are some time-out properties (HTTP &TCP/IP) of the merge replication and RDA programming objects, which can be set to optimize synchronization over various network bandwidths.

(HTTP)

ConnectionRetryTimeout: Specifies the time, in seconds, to continue to retry sending requests after an established connection has failed.

(TCP/IP)

ConnectTimeout: Specifies the time, in milliseconds, to wait for a connection to the server.

ReceiveTimeout: Specifies the time, in milliseconds, to wait for the response to a server request.

SendTimeout: Specifies the time, in milliseconds, to wait to send a request to the server.

When data is synchronized over a cellular or mobile network, requests sent to the server, and responses from the server can time out. This occurs when a low-bandwidth connection requires too much time to transmit the data. In your case, you have an overhead of the bluetooth transport as well! These time-outs would cause synchronization to fail, which you can solve by specifying a longer time-out value. Try setting a huge value (60000 ms) in your code for the TCP/IP Connect and Send/Recieve Timeouts on the merge replication/RDA object. This mostly should solve your problem.

2. Bluetooth DUN Profile - I assume you are using the Dial-Up Networking profile for the phone to act as a GPRS modem. Are you sure there are no pairing issues? During our tests, we faced a lot of issues with the bluetooth pairing - especially between WM 5.0 PDA's and third party mobile devices.

Let me know if this works out!

Regards,

Sravanth Aluru

|||

When inserting the parameters you specify for the connection string I get an error that says "Unknown connection option ..." .

And for the SqlConnection object there are no properties like those. Only PacketSize, ConnectionTimeout .

I forgot to say previously that I put in connection string "Packet Size=512" and "ConnectionTimeout=90". I tried even with ConnectionTimeout=180. No change.

Do I miss anything, any assembly declaration ?

Could give me another advice ?

Mihai.

|||

The parameters are available in SQL Mobile 3.1 Beta which shipped with Visual Studio 2005 SP1 and onwards.

Sorry for missing this information out in my earlier response!

Can you specify which version you are on?

/Sravanth

|||

I have Sql Mobile 3.00. So , now I am downloading it . I'll reply as soon as I have news.

Thanks a lot,

Mihai.

|||

I can't get sql mobile 3.1

I just installed VS Professional, then SP1. Only Sql mobile 3.0. Anyway, in VS when I try to add reference to System.Data.Sqlclient it shows 3.0 version .

I tried to connect with SendTimeout parameter in the connection string . But I get error which says it is unknown parameter.

I even installed Sql everywhere because I saw it was 3.1 version. Nothing .

I also installed 3_SQL Server 2005 Mobile Edition Server Tools ...

I don't know what else to do.

Any suggestion ?

Thank you,

Mihai .

|||

Yes, I see. It's TCP/IP level ... sorry .

Of course I won't have those parameters in connection string. But how can I work at stream level and use the SQL commands to get data ? Everywhere in docs, when using TcpClient class, the stream is used for sending/receiving data .

But, how do I do to use these TCP/IP parameters if I work at IP level, with sql connection and then sql commands ?

Please give me some advices , I'm a rookie in working with these protocols.

Thanks a lot,

Mihai.

|||

This is a sample of how I get data:

SqlConnection s2000conn = new SqlConnection("Server=10.1.1.10 ; Connection Timeout=60 ; Packet Size = 512 ; UId=user;Pwd=pwd;Database=GestSC");

try

{

s2000conn.Open();

}

catch (SqlException eroare)

{

MessageBox.Show("Connection error", "Eroare Sql");

return;

}

SqlCommand sqlcoms = new SqlCommand();

sqlcoms.CommandText = "SELECT * FROM part ";

SqlDataAdapter da_part = new SqlDataAdapter(sqlcoms.CommandText, s2000conn);

DataSet ds_part = new DataSet();

DataTable dt_part = ds_part.Tables.Add("downpart");

da_part.Fill(ds_part, "downpart");

.....

Then I insert this data in Sql Mobile database .

But how would I use TcpClient ?

TcpClient tc = new TcpClient();

tc.SendTimeout = 90;

And then ?

Thank you ,

Mihai .

|||

Man ,

Is it that simple that it doesn't really worth the effort or you have no answer ?

Can you say smth ?

Thanks anyway,

Mihaila.

|||

Hy all,

I solved the problem by breaking the results into many smaller packets.

First I counted the total number of records and then Fill-ed the cursor with a for-end loop. Simple, but initially I wanted to work in the default mode, not to write code.

And it works.

Finally I heared from the GPRS provider that the GPRS allows small packets of data (maximum 2048 for Packet Size preffered) and the transport of data is not very reliable, so small amount of data should be transfered at one time.

Thank you anyway for all your replies,

Mihaila.

sql

Function to Convert Amount to Words for Check Printing

I am thinking someone out there must have already developed this....

I am using Report Services to generate checks and need to convert the $ amount into words on the "Pay To" line.

I searched Google and found a couple of VB/ASP routines that do this, but I would need to do it via a SQL Proc since the reporter does not provide the capability to reference and/or call custom VB functions from within.

Does anyone have / know of SQL function that may be publicly available or are willing to share that does this or perhaps can suggest a better way to handle?

Thanks.

If you use SQL Server Reporting Services, then you could add VB Code functions and then use it in your Expressions.

Also you could create .NET Stored procedures/user defined functions and use it in you Transact-SQL code.

|||

It is absolutely amazing. Just by typing in 'sql function amount to words' into the Google? search bar, I got back dozens of hits, these look like good possibilities.

http://www.novicksoftware.com/udfofweek/Vol1/T-SQL-UDF-Volume-1-Number-46-NumberToWords.htm
http://www.devx.com/vb2themax/Tip/19053
http://code.filehungry.com/product/languages/sql/miscellaneous/123_tsql_functions

Google? is your friend...|||

Thanks. I figured out that even though the project explorer didn't list class as a new type of item which could be added, I could add a class to the reports project via the menu.

Appreciate your help,

Annette

|||

Funny how I did exactly the same Google search and didn't get the same results (or perhaps they were not listed on the first few pages I checked (there were 10 pages of results).

Google is definitely our friend and so are people like yourself who take the time to answer these posts.

Thank you,

Annette

Function Help

I am using SQL 2000.
I have a table with Shipping Transactions in it (below).
Each record listed is a package, but the Amount shown is the total shipping
cost for all packages within a Shipment (Tracking)
I am trying to write a FUNCTION that will return the Total Shipment Amount
for a given PT (462714). Any Amount associated with a record that is voided
would not be included.
So, using the data below the function should return:
61.73
(61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
354925898 was voided so the 46.04 associated with it is excluded.
Recorded PT Order Tracking Total Weight Amount Void
1 462714 1589651 352414201 247 46.04 NO
2 462714 1589651 352414201 247 46.04 NO
3 462714 1589651 352414201 247 46.04 NO
19 462714 1589651 354925898 247 46.04 NO
20 462714 1589651 354925898 247 46.04 NO
21 462714 1589651 354925898 247 46.04 NO
22 462714 1589651 354925898 247 46.04 NO
23 462714 1589651 354925898 247 46.04 NO
24 462714 1589651 354925898 247 46.04 NO
25 462714 1589651 354925898 247 46.04 NO
26 462714 1589651 354925898 247 46.04 NO
27 462714 1589651 354925898 247 46.04 NO
28 462714 1589651 354925898 247 46.04 Y
29 462714 1589651 354925898 247 46.04 Y
30 462714 1589651 354925898 247 46.04 Y
31 462714 1589651 354925898 247 46.04 Y
32 462714 1589651 354925898 247 46.04 Y
33 462714 1589651 354925898 247 46.04 Y
34 462714 1589651 354925898 247 46.04 Y
35 462714 1589651 354925898 247 46.04 Y
36 462714 1589651 354925898 247 46.04 Y
37 462714 1589651 354111785 56 15.69 N
38 462714 1589651 354111785 56 15.69 NO
Any help would be greatly appreciated.
Thank youA number of ways, here is one (untested):
SELECT SUM(amount)
FROM
(
SELECT order,amount = MIN(amount)
FROM tablename
WHERE Void = 'N'
AND PT = 462714
GROUP BY order
) x
(Try and get the query itself working before incorporating it into a
function. Much easier to debug in QA that way, imho.)
A
"Kevin L" <no_spam@.not_real_email.com> wrote in message
news:%23yxDhihPGHA.720@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000.
> I have a table with Shipping Transactions in it (below).
> Each record listed is a package, but the Amount shown is the total
> shipping cost for all packages within a Shipment (Tracking)
> I am trying to write a FUNCTION that will return the Total Shipment Amount
> for a given PT (462714). Any Amount associated with a record that is
> voided would not be included.
> So, using the data below the function should return:
> 61.73
> (61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
> 354925898 was voided so the 46.04 associated with it is excluded.
>
> Recorded PT Order Tracking Total Weight Amount Void
> 1 462714 1589651 352414201 247 46.04 NO
> 2 462714 1589651 352414201 247 46.04 NO
> 3 462714 1589651 352414201 247 46.04 NO
> 19 462714 1589651 354925898 247 46.04 NO
> 20 462714 1589651 354925898 247 46.04 NO
> 21 462714 1589651 354925898 247 46.04 NO
> 22 462714 1589651 354925898 247 46.04 NO
> 23 462714 1589651 354925898 247 46.04 NO
> 24 462714 1589651 354925898 247 46.04 NO
> 25 462714 1589651 354925898 247 46.04 NO
> 26 462714 1589651 354925898 247 46.04 NO
> 27 462714 1589651 354925898 247 46.04 NO
> 28 462714 1589651 354925898 247 46.04 Y
> 29 462714 1589651 354925898 247 46.04 Y
> 30 462714 1589651 354925898 247 46.04 Y
> 31 462714 1589651 354925898 247 46.04 Y
> 32 462714 1589651 354925898 247 46.04 Y
> 33 462714 1589651 354925898 247 46.04 Y
> 34 462714 1589651 354925898 247 46.04 Y
> 35 462714 1589651 354925898 247 46.04 Y
> 36 462714 1589651 354925898 247 46.04 Y
> 37 462714 1589651 354111785 56 15.69 N
> 38 462714 1589651 354111785 56 15.69 NO
>
> Any help would be greatly appreciated.
> Thank you
>|||There is some duplicate data (as far as the fields you are looking at for
returning the total) - the following function weeds through them to return
the sum. I might suggest finding some relation to this table to filter
rather than having to use the distincts in the function. This should get yo
u
started:
CREATE FUNCTION [dbo].[GetPTTotal] (
@.PT int
)
RETURNS money AS
BEGIN
declare @.amount money
declare @.trackingamounts table (tracking int, amount money, void bit default
0)
--grab all the records which might indicate a valid amount
insert @.trackingamounts (tracking, amount)
select distinct tracking, amount
from dbo.shippingtransaction
where PT = @.pt
and void like 'n%'
--go back and void any tracking number which has at least one void
update ta
set void = 1
FROM @.trackingamounts ta
join ( select distinct tracking, void
from dbo.shippingtransaction
where pt = @.pt
and void = 'Y'
) tab on ta.tracking = tab.tracking
-- output the sum of any remaining valid amounts
select @.amount = sum(amount) from @.trackingamounts where void = 0
RETURN @.amount
END
GO
"Kevin L" wrote:

> I am using SQL 2000.
> I have a table with Shipping Transactions in it (below).
> Each record listed is a package, but the Amount shown is the total shippin
g
> cost for all packages within a Shipment (Tracking)
> I am trying to write a FUNCTION that will return the Total Shipment Amount
> for a given PT (462714). Any Amount associated with a record that is voide
d
> would not be included.
> So, using the data below the function should return:
> 61.73
> (61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
> 354925898 was voided so the 46.04 associated with it is excluded.
>
> Recorded PT Order Tracking Total Weight Amount Void
> 1 462714 1589651 352414201 247 46.04 NO
> 2 462714 1589651 352414201 247 46.04 NO
> 3 462714 1589651 352414201 247 46.04 NO
> 19 462714 1589651 354925898 247 46.04 NO
> 20 462714 1589651 354925898 247 46.04 NO
> 21 462714 1589651 354925898 247 46.04 NO
> 22 462714 1589651 354925898 247 46.04 NO
> 23 462714 1589651 354925898 247 46.04 NO
> 24 462714 1589651 354925898 247 46.04 NO
> 25 462714 1589651 354925898 247 46.04 NO
> 26 462714 1589651 354925898 247 46.04 NO
> 27 462714 1589651 354925898 247 46.04 NO
> 28 462714 1589651 354925898 247 46.04 Y
> 29 462714 1589651 354925898 247 46.04 Y
> 30 462714 1589651 354925898 247 46.04 Y
> 31 462714 1589651 354925898 247 46.04 Y
> 32 462714 1589651 354925898 247 46.04 Y
> 33 462714 1589651 354925898 247 46.04 Y
> 34 462714 1589651 354925898 247 46.04 Y
> 35 462714 1589651 354925898 247 46.04 Y
> 36 462714 1589651 354925898 247 46.04 Y
> 37 462714 1589651 354111785 56 15.69 N
> 38 462714 1589651 354111785 56 15.69 NO
>
> Any help would be greatly appreciated.
> Thank you
>
>|||On Thu, 2 Mar 2006 12:51:27 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>A number of ways, here is one (untested):
>SELECT SUM(amount)
>FROM
>(
> SELECT order,amount = MIN(amount)
> FROM tablename
> WHERE Void = 'N'
> AND PT = 462714
> GROUP BY order
> ) x
>(Try and get the query itself working before incorporating it into a
>function. Much easier to debug in QA that way, imho.)
Hi Aaron,
Looking at the sample data Kevin posted, I suspect that it should be
SELECT SUM(amount)
FROM
(
SELECT order,amount = MIN(amount)
FROM tablename
AND PT = 462714
GROUP BY order
HAVING MAX(Void) < 'Y'
) x
One of the orders had 'NO' for voided in some rows and 'Y' in some
others, and he wanted to exclude the whole order.
BTW, Kevin: Please add a CHCEK constraint to the Void column so that
you'll have to deal with only one value for yes and one for no - this
mess with NO, N and Y (end maybe YES as well) all interspersed just
gives you unneeded problems.
Hugo Kornelis, SQL Server MVP|||> One of the orders had 'NO' for voided in some rows and 'Y' in some
> others, and he wanted to exclude the whole order.
Ah, good catch.