reader headers of table

I have a table that has :
date, leader_count, employee_count, manager_count
10/14/2005, 4 ,5 ,5
Any idea how I can almost transpose the table to create a table that looks
like:
date, type, count
10/14/2005, leader, 4
10/15/2005, employee, 5
Thanks for any suggestions.
what do you mean transpose, how about using a view ?
"AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
news:07EF85C2-5F94-4E60-86B6-A9713160D21C@.microsoft.com...
>I have a table that has :
> date, leader_count, employee_count, manager_count
> 10/14/2005, 4 ,5 ,5
> Any idea how I can almost transpose the table to create a table that looks
> like:
> date, type, count
> 10/14/2005, leader, 4
> 10/15/2005, employee, 5
> Thanks for any suggestions.
|||Or where you looking for something like this ?
--
DROP TABLE Table1
CREATE TABLE Table1
(
[date] datetime,
leader_count int,
employee_count int,
manager_count int
)
INSERT INTO Table1 VALUES('10/14/2005', 4 ,5 ,5)
SELECT * FROM Table1
DROP TABLE NewTable
CREATE TABLE NewTable
(
[date] datetime,
Type char(10),
[count] int
)
INSERT INTO NewTable ([Date],Type,[count]) SELECT
[date],'Leader',leader_count FROM Table1
INSERT INTO NewTable ([Date],Type,[count]) SELECT
[date],'Employee',employee_count FROM Table1
INSERT INTO NewTable ([Date],Type,[count]) SELECT
[date],'Manager',manager_count FROM Table1
Go
SELECT * FROM NewTable
but try not to use so many reserved words
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:eliQgmO$FHA.140@.TK2MSFTNGP12.phx.gbl...
> what do you mean transpose, how about using a view ?
> "AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
> news:07EF85C2-5F94-4E60-86B6-A9713160D21C@.microsoft.com...
>
|||PERFECT! THANK YOU VERY MUCH.
"David J. Cartwright" wrote:

> Or where you looking for something like this ?
> --
> --
> DROP TABLE Table1
> CREATE TABLE Table1
> (
> [date] datetime,
> leader_count int,
> employee_count int,
> manager_count int
> )
> INSERT INTO Table1 VALUES('10/14/2005', 4 ,5 ,5)
> SELECT * FROM Table1
> DROP TABLE NewTable
> CREATE TABLE NewTable
> (
> [date] datetime,
> Type char(10),
> [count] int
> )
> INSERT INTO NewTable ([Date],Type,[count]) SELECT
> [date],'Leader',leader_count FROM Table1
> INSERT INTO NewTable ([Date],Type,[count]) SELECT
> [date],'Employee',employee_count FROM Table1
> INSERT INTO NewTable ([Date],Type,[count]) SELECT
> [date],'Manager',manager_count FROM Table1
> Go
> SELECT * FROM NewTable
> --
> but try not to use so many reserved words
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:eliQgmO$FHA.140@.TK2MSFTNGP12.phx.gbl...
>
>

reader headers of table

I have a table that has :
date, leader_count, employee_count, manager_count
10/14/2005, 4 ,5 ,5
Any idea how I can almost transpose the table to create a table that looks
like:
date, type, count
10/14/2005, leader, 4
10/15/2005, employee, 5
Thanks for any suggestions.what do you mean transpose, how about using a view ?
"AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
news:07EF85C2-5F94-4E60-86B6-A9713160D21C@.microsoft.com...
>I have a table that has :
> date, leader_count, employee_count, manager_count
> 10/14/2005, 4 ,5 ,5
> Any idea how I can almost transpose the table to create a table that looks
> like:
> date, type, count
> 10/14/2005, leader, 4
> 10/15/2005, employee, 5
> Thanks for any suggestions.|||Or where you looking for something like this ?
--
DROP TABLE Table1
CREATE TABLE Table1
(
[date] datetime,
leader_count int,
employee_count int,
manager_count int
)
INSERT INTO Table1 VALUES('10/14/2005', 4 ,5 ,5)
SELECT * FROM Table1
DROP TABLE NewTable
CREATE TABLE NewTable
(
[date] datetime,
Type char(10),
[count] int
)
INSERT INTO NewTable ([Date],Type,[count]) SELECT
[date],'Leader',leader_count FROM Table1
INSERT INTO NewTable ([Date],Type,[count]) SELECT
[date],'Employee',employee_count FROM Table1
INSERT INTO NewTable ([Date],Type,[count]) SELECT
[date],'Manager',manager_count FROM Table1
Go
SELECT * FROM NewTable
--
but try not to use so many reserved words
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:eliQgmO$FHA.140@.TK2MSFTNGP12.phx.gbl...
> what do you mean transpose, how about using a view ?
> "AshleyT" <AshleyT@.discussions.microsoft.com> wrote in message
> news:07EF85C2-5F94-4E60-86B6-A9713160D21C@.microsoft.com...
>|||PERFECT! THANK YOU VERY MUCH.
"David J. Cartwright" wrote:

> Or where you looking for something like this ?
> --
> --
> DROP TABLE Table1
> CREATE TABLE Table1
> (
> [date] datetime,
> leader_count int,
> employee_count int,
> manager_count int
> )
> INSERT INTO Table1 VALUES('10/14/2005', 4 ,5 ,5)
> SELECT * FROM Table1
> DROP TABLE NewTable
> CREATE TABLE NewTable
> (
> [date] datetime,
> Type char(10),
> [count] int
> )
> INSERT INTO NewTable ([Date],Type,[count]) SELECT
> [date],'Leader',leader_count FROM Table1
> INSERT INTO NewTable ([Date],Type,[count]) SELECT
> [date],'Employee',employee_count FROM Table1
> INSERT INTO NewTable ([Date],Type,[count]) SELECT
> [date],'Manager',manager_count FROM Table1
> Go
> SELECT * FROM NewTable
> --
> but try not to use so many reserved words
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:eliQgmO$FHA.140@.TK2MSFTNGP12.phx.gbl...
>
>

Read/Only access to Northwind database

Hi;
This is one of those things I want to be 100% sure to get right because we
are opening this user to the world.
How do I create a user in SqlServer that has read/only access to Northwind.
No access to any other database and only R/O access to Northwind?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
see the "too much free time video" - http://www.windwardreports.com/film.htmTry:
1) Create a login
2) Add the login as a user to the Northwind database
3) Add the user to the db_datareader fixed database role
Be sure that you remove the guest user, if present.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
Hi;
This is one of those things I want to be 100% sure to get right because we
are opening this user to the world.
How do I create a user in SqlServer that has read/only access to Northwind.
No access to any other database and only R/O access to Northwind?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
see the "too much free time video" - http://www.windwardreports.com/film.htm|||Hi;
I had it set to public & db_datareader because it won't let me deselect
public. Is this correct or am I missing something?
Also, what do you mean by "fixed database"?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Tom Moreau" wrote:

> Try:
> 1) Create a login
> 2) Add the login as a user to the Northwind database
> 3) Add the user to the db_datareader fixed database role
> Be sure that you remove the guest user, if present.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
> Hi;
> This is one of those things I want to be 100% sure to get right because we
> are opening this user to the world.
> How do I create a user in SqlServer that has read/only access to Northwind
.
> No access to any other database and only R/O access to Northwind?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> see the "too much free time video" - [url]http://www.windwardreports.com/film.htm[/ur
l]
>
>|||Hi Dave,
I think you've done it right and haven't miseed anything. The public role
is a special database role to which every database
user belongs. The public role contain default access permissionsfor any
user who can access the database. This database role cannot
be dropped.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial
response from the community or a Microsoft Support Engineer within 1
business day is
acceptable. Please note that each follow up response may take approximately
2 business days
as the support professional working with you may need further investigation
to reach the
most efficient resolution. The offering is not appropriate for situations
that require
urgent, real-time or phone-based interactions or complex project analysis
and dump analysis
issues. Issues of this nature are best handled working with a dedicated
Microsoft Support
Engineer by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||A "fixed database role" is one that is there by default and cannot be
removed. There are also fixed server roles. You can create your own
database roles, too. However, you cannot create server roles.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:C32D630F-14E6-4A85-96CF-171DCD4D1A48@.microsoft.com...
Hi;
I had it set to public & db_datareader because it won't let me deselect
public. Is this correct or am I missing something?
Also, what do you mean by "fixed database"?
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm
"Tom Moreau" wrote:

> Try:
> 1) Create a login
> 2) Add the login as a user to the Northwind database
> 3) Add the user to the db_datareader fixed database role
> Be sure that you remove the guest user, if present.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> ..
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:6F943F7E-D5A6-495F-8EAD-EE3199777575@.microsoft.com...
> Hi;
> This is one of those things I want to be 100% sure to get right because we
> are opening this user to the world.
> How do I create a user in SqlServer that has read/only access to
> Northwind.
> No access to any other database and only R/O access to Northwind?
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> see the "too much free time video" -
> http://www.windwardreports.com/film.htm
>
>

Read only User on data base

I'm sure this is a really easy one but I want to create a user with read only access on a sql 2000 database using SQL authentication.

I'm building a little pilot DB system and I have a trainee developer who I want to built me some queries for crystal reports. But I dont want them to be able to amend/delete data or tables etc.

I've created the user but how to I set it to read only. Also will the user still have read only rights if the user has access to the database via enterprize manager/Query analysier.

thanks,Grant the user as db_datareader. He or she will can only read data and uses EM and QA.|||I've Granted the user the db_datareader permission and within EM everything is ok the user has read only access but within QA, logging in with the correct SQL Ath. user name and password he still has permission to Drop tables via the object browser -- very strange or have I done something wrong !|||Is the id in a particular role?

How many roles do you have?|||The User has been created within the Public and DB_datareader database role only.

Do I need to add him to another db role or server role?|||Nope. Nothing wrong. You just have to change the password on the account he is using in Query Analyzer. Basically, this user has access to two separate accounts. One with the permissions you want him to have (NT Authenticated), and a second (the SQL authenticated one) with permissions you do not want him to have.

Naturally, before you go changing passwords, you have to ask where the other (SQL authenticated) account is used (maybe in some application that needs to update data?). In this case, changing the password could be prohibitive, and you may have to resort to asking the user to stop using that account. Ahh, politics.

Read Only User - One Database

How do I create a user, that has rights to only one database, and only
select rights to some views?
I created a login in Enterprise Manager; checked the database access only on
the database the user needs to see; and checked the db_datareader role.
However, logging in as this user I can see and select from other databases.
Under the database I gave the user rights to, I checked only select rights
on the views I want the user to select from. I can still select from
tables, etc.
Obviously, I'm missing something here...Which other databases? Most likely those are databases with
the guest account enabled. If a user doesn't have an account
to log into the database and the guest account is enabled,
the user has access through the guest account and whatever
rights are granted to public and this account. The guest
account cannot be deleted from master or tempdb. It can be
added, delete from other databases.
When you added the user to the db_datareader role, you gave
that user permissions to select from all user tables.
Permissions are cumulative so the user obtains all
permissions through the combination of their individual
account and any groups, roles that they are members of. When
you also gave the individual account select permission on
views, the user ended up with those permissions as well as
select on all user tables.
-Sue
On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:

>How do I create a user, that has rights to only one database, and only
>select rights to some views?
>I created a login in Enterprise Manager; checked the database access only o
n
>the database the user needs to see; and checked the db_datareader role.
>However, logging in as this user I can see and select from other databases.
>Under the database I gave the user rights to, I checked only select rights
>on the views I want the user to select from. I can still select from
>tables, etc.
>Obviously, I'm missing something here...
>|||sue i can't see guest account except master database.. you meant somebody
created it ?
"Sue Hoegemeier" wrote:

> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue
> On Thu, 28 Oct 2004 17:39:38 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
>|||No...nothing about someone creating the guest account but I
don't know what databases the user is accessing. Which other
databases? System databases or user databases? Is the user a
member of other roles or groups that would give them access
to the other databases?
-Sue
On Thu, 28 Oct 2004 19:53:01 -0700, "Dave"
<Dave@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>sue i can't see guest account except master database.. you meant somebody
>created it ?
>"Sue Hoegemeier" wrote:
>|||I've deleted the guest account from other user DBs and the user I can not
see/connect to those DBs - good.
I've unchecked the db_datareader role and only gave select permissions to 2
views in the DB - that seems to work OK as well.
However, the user can still select from system tables in the database they
are assigned to. Any way to prevent that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
> Which other databases? Most likely those are databases with
> the guest account enabled. If a user doesn't have an account
> to log into the database and the guest account is enabled,
> the user has access through the guest account and whatever
> rights are granted to public and this account. The guest
> account cannot be deleted from master or tempdb. It can be
> added, delete from other databases.
> When you added the user to the db_datareader role, you gave
> that user permissions to select from all user tables.
> Permissions are cumulative so the user obtains all
> permissions through the combination of their individual
> account and any groups, roles that they are members of. When
> you also gave the individual account select permission on
> views, the user ended up with those permissions as well as
> select on all user tables.
> -Sue|||The public role is able to select from some of the system tables. All
database user are members of public. That's why the user can select
from some of the system tables.
-Sue
On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
<no_spam@.no_email.org> wrote:

>I've deleted the guest account from other user DBs and the user I can not
>see/connect to those DBs - good.
>I've unchecked the db_datareader role and only gave select permissions to 2
>views in the DB - that seems to work OK as well.
>However, the user can still select from system tables in the database they
>are assigned to. Any way to prevent that?
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>|||Adrian
In additon to Sue's advice ,if you don't want the users be able to select
from system tables , you can hide them by editing SQL Server registration
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
> The public role is able to select from some of the system tables. All
> database user are members of public. That's why the user can select
> from some of the system tables.
> -Sue
> On Fri, 29 Oct 2004 08:55:09 -0400, "Adrian Maull \(MCP\)"
> <no_spam@.no_email.org> wrote:
>
2[vbcol=seagreen]
they[vbcol=seagreen]
>|||That doesn't prevent users from being able to select from
system tables though. All that will do is prevent displaying
them in Enterprise Manager.
-Sue
On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
<urid@.iscar.co.il> wrote:

>Adrian
>In additon to Sue's advice ,if you don't want the users be able to select
>from system tables , you can hide them by editing SQL Server registration
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:m745o01h1ck4l9e3262931g9nktach4ru3@.
4ax.com...
>2
>they
>|||Yep, I assumed that all manipulations are done by users in EM.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:qi5bo0lo2223mjjdqcfkdlhvafhlg596i0@.
4ax.com...
> That doesn't prevent users from being able to select from
> system tables though. All that will do is prevent displaying
> them in Enterprise Manager.
> -Sue
> On Sun, 31 Oct 2004 11:20:42 +0200, "Uri Dimant"
> <urid@.iscar.co.il> wrote:
>
not[vbcol=seagreen]
to[vbcol=seagreen]
>|||I am trying to do the same thing. I have a user that needs access to one of
the databases on the server. I can prevent him from modifying or even seeing
the tables on the other databases with permissions, but I want to be able to
hide the other databases from Enterprise Manager. I am using SQL logins. Can
I do this? It sounds like you were able to do this. I have deleted the guest
account from a number of databases and added his login to only two databases
but the login displays all of the databases, including those with only sa as
the login.
"Adrian Maull (MCP)" wrote:

> I've deleted the guest account from other user DBs and the user I can not
> see/connect to those DBs - good.
> I've unchecked the db_datareader role and only gave select permissions to
2
> views in the DB - that seems to work OK as well.
> However, the user can still select from system tables in the database they
> are assigned to. Any way to prevent that?
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:og93o0loelonsc0tkhbhau449s6defp78l@.
4ax.com...
>
>

Read only permissions for report writer

I have an msde database (SQL Server 2000) with an web front end. I would
like to create an MS Access Project to allow certain users to create ad hoc
reports.
I have created a new login named Reporter and connected to the msde database
using this user. This user has dbreadonly permissions.
This works to a certain extent in that new reports can be created based on
existing tables. However, I would like the user to be able to create their
own select queries and cannot find a way to manage this. I recieve a
message saying the user needs 'Create Procedure' rights.
Can anyone tell me how I can let Reporter create their own queries but not
add/edit/delete any existing data?
Many thanks
June
hi June,
June Macleod wrote:
> I have an msde database (SQL Server 2000) with an web front end. I
> would like to create an MS Access Project to allow certain users to
> create ad hoc reports.
> I have created a new login named Reporter and connected to the msde
> database using this user. This user has dbreadonly permissions.
> This works to a certain extent in that new reports can be created
> based on existing tables. However, I would like the user to be able
> to create their own select queries and cannot find a way to manage
> this. I recieve a message saying the user needs 'Create Procedure'
> rights.
> Can anyone tell me how I can let Reporter create their own queries
> but not add/edit/delete any existing data?
> Many thanks
> June
if you are required to allow "CREATE PROC" statement you have to grant
membership to ddladmin database role but, with such a permission, Reporter
can even create new tables and of course access/modify their data..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||better,
if you only are required to create procedures, you can grant that specific
permission to Reporte user like
GRANT CREATE PROCEDURE TO [Reporter]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Read only DB when moving database (error 5105 "device activation...")

Windows 2003 server
SQL2000 installed and running
Create a database on C:\Data called Ctest
Create a database on F:\Data called Ftest
Detach both databases.
Move Ftest to c:\Data folder.
Move Ctest to F:\Data folder.
Reattach both databases from their new locations.
Here's the problem --
Both are read only now.
Un-checking the Read only properties causes an error 5105 "device
activation..."
I do the same thing on another system and they don't attach as read
only.
Any ideas on this one?
Mark GMark,
Check the file properties for the .mdf and .ldf files - are they read-only?
Also since this is a test box try restarting the server as well.
HTH
Jerry
"Mark G" <megriep@.gmail.com> wrote in message
news:1128185904.036026.202210@.o13g2000cwo.googlegroups.com...
> Windows 2003 server
> SQL2000 installed and running
> Create a database on C:\Data called Ctest
> Create a database on F:\Data called Ftest
> Detach both databases.
>
> Move Ftest to c:\Data folder.
> Move Ctest to F:\Data folder.
>
> Reattach both databases from their new locations.
>
> Here's the problem --
> Both are read only now.
> Un-checking the Read only properties causes an error 5105 "device
> activation..."
>
> I do the same thing on another system and they don't attach as read
> only.
>
> Any ideas on this one?
>
> Mark G
>|||And also verify that the SQL Server service account has proper permission on the files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:Onzf8DrxFHA.3400@.TK2MSFTNGP14.phx.gbl...
> Mark,
> Check the file properties for the .mdf and .ldf files - are they read-only? Also since this is a
> test box try restarting the server as well.
> HTH
> Jerry
> "Mark G" <megriep@.gmail.com> wrote in message
> news:1128185904.036026.202210@.o13g2000cwo.googlegroups.com...
>> Windows 2003 server
>> SQL2000 installed and running
>> Create a database on C:\Data called Ctest
>> Create a database on F:\Data called Ftest
>> Detach both databases.
>>
>> Move Ftest to c:\Data folder.
>> Move Ctest to F:\Data folder.
>>
>> Reattach both databases from their new locations.
>>
>> Here's the problem --
>> Both are read only now.
>> Un-checking the Read only properties causes an error 5105 "device
>> activation..."
>>
>> I do the same thing on another system and they don't attach as read
>> only.
>>
>> Any ideas on this one?
>>
>> Mark G
>|||Thanks,
Yes, it's a permissions thing--something to do with Windows. Not sure
why one system has the problem and the other doesn't. I'll have to
look into it.
Thanks for all your help.
Mark.

Read one field in every row during SELECT

Hello

I have a table that keeps track of every access to the system. We insert the UserID, TimeOfAccess and TypeOfAccess.

We want to create a report and due to the limitations of Reporting Services for programatically processing, we want to create a temporary table to have the columns already set up for the report with the info that we need.

ORIGINAL TABLE

USERID | TIMEOFACCESS | TYPE OF ACCESS

2323 | 12/15/2007 03:52:54 | CLOCKIN

2323 | 12/15/2007 04:32:54 | CLOCKOUT

2323 | 12/15/2007 05:42:54 | CLOCKIN

2323 | 12/15/2007 07:53:54 | CLOCKOUT

2323 | 12/15/2007 09:18:54 | CLOCKIN

2323 | 12/15/2007 10:24:54 | CLOCKOUT

TEMPORARY TABLE

USERID | CLOCKIN | CLOCKOUT | ELAPSEDTIME

2323 | 03:52:54 | 04:32:54 | 0:40:54

OK. the problem is that I need to read the fields one by one in the SELECT statement so i can insert (and Update) the fields of the temporary table.

I create my temporary table

CREATE #TempTable

(

UserID int,

ClockIN DateTime null,

ClockOUT DateTime null,

ElapsedTime DateTime null,

)

I want to be able to do this in my stored procedure. I can doit in a form but i want to return from my database the datatable already suitable for my report.

Does anyone know how to read in a SELECT statement one field as it's been read?

you can do that using a cursor in your stored procedure.

See: http://www.sqlteam.com/item.asp?ItemID=553

If you dont want to use a cursor, its possible as well:

http://www.sql-server-performance.com/dp_no_cursors.asp

|||

hi,

you can achieve this using a better solution by dong a self join on userid and midnight(dateaccess)

you just have to drop the timepart of the date on the join. you dont even need the temp table.

here's the pseudocode: you just need to imporve this.

select userid, convert( varchar(20),dateaccess,102) as dateaccess, convert( varchar (20),dateaccess,108) as clockin, t2.clockout from table1 t1

where [type of access]='clockin'

join

(select userid, convert( varchar(20),dateaccess,102) as dateaccess,

convert( varchar(20),dateaccess,108) as clockout, from table1

where [type of access]='clockout')

as t2

on t1.userid=t2.userid and t1.dateaccess=t2.dateaccess

regards,

joey

|||

Try selecting the minimum "clockin" and maximum "clockout" per each userid, then calculate the diff in seconds.

Code Snippet

;with cte

as

(

select

userid,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout,

datediff(

seconds,

min(case when [type of access] = 'clockin' then [time of access] end) as clockin,

max(case when [type of access] = 'clockout' then [time of access] end) as clockout

) as elapsed_time_sec

from

dbo.t1

group by

userid

)

select

userid,

clockin,

clockout,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte;

AMB

|||

None of this solutions actually work.

I don't get the desired result. I think this is something impossible to do in a Stored Procedure wich is very disappointing because is not so difficult to do in a form. But that's what i don't want. There has to be a way... i guess i need to study more the t-sql language. I just can't believe t-sql is so poor.

|||

Try:

Code Snippet

;with cte_1

as

(

select

userid,

[TIME OF ACCESS],

[TYPE OF ACCESS],

row_number() over(partition by userid order by [TIME OF ACCESS]) as rn

from

dbo.t1

),

cte_2

as

(

select

a.userid,

a.[TIME OF ACCESS] as CLOCKIN,

b.[TIME OF ACCESS] as CLOCKOUT,

datediff(second, a.[TIME OF ACCESS], b.[TIME OF ACCESS]) as elapsed_time_sec

from

cte_1 as a

inner join

cte_1 as b

on a.userid = b.userid

and a.rn = b.rn - 1

and a.[TYPE OF ACCESS] = 'CLOCKIN'

and b.[TYPE OF ACCESS] = 'CLOCKOUT'

)

select

userid,

CLOCKIN,

CLOCKOUT,

right('00' + ltrim(elapsed_time_sec / 3600), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) / 60), 2) + ':' +

right('00' + ltrim((elapsed_time_sec % 3600) % 60), 2)

from

cte_2

order by

userid,

CLOCKIN;

go

BTW, I wonder why elapsed time for the first two rows is "00:40:54", is they both have same number of seconds. I think it should be "00:40:00".

AMB

|||

This is it.

It works perfectly fine.

Definitely i am gonna buy a couple of T-SQL books. Any suggestion on the best title or publisher?

Jose

|||

I will suggest the serie "Inside SQL Server 2005".

Inside Microsoft SQL Server 2005: T-SQL Querying

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735623139/ref=pd_bbs_sr_3/002-2026708-7606405?ie=UTF8&s=books&qid=1179781170&sr=1-3

Inside Microsoft SQL Server 2005: T-SQL Programming

http://www.amazon.com/Inside-Microsoft-Server-2005-Pro-Developer/dp/0735621977/ref=pd_sim_b_1/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

Inside Microsoft (r) SQL Server (tm) 2005: The Storage Engine

http://www.amazon.com/Inside-Microsoft-SQL-Server-2005/dp/0735621055/ref=pd_sim_b_2/002-2026708-7606405?ie=UTF8&qid=1179781170&sr=1-3

AMB

P.S. Your alias exposed you.

PP --> Pepe (Jose)

CUBAN --> from Cuba

Oye chico, vinistes a bailar en casa del trompo.

|||

AMB

P.S. Your alias exposed you.

As one who belives in transparency, and thinks that a lot of folks are stuck in grade school with their childish and 'cutsy' nom de plumes, exactly how this is a problem in this venue?

Granted, anonymity has it purposes and places -but really, are these forums such place to fear exposure?

|||

good point. I am not affraid of exposure. As a matter of fact, most of the people online knows me by that name. ppcuban@.{most famous emails}.com are my address, the domain ppcuban.com also... hahaha. So, basically everybody call me ppcuban as a nickname even out of internet.

AMB? Tu eres cubano?

|||

Jose,

Nacido y criado en la Vibora. Emigre hacia los Estados Unidos de america hace 9 anios. Vivi por un tiempo en Miami y ahora radico en Carolina del Sur (No pastelitos de guayava y mucho menos masareal).

Saludos,

Alejandro Mesa

read file and exec contents

I have a text file that contains "create index" statements - thanks to those
of you here who helped me get past my hurdles with this step earlier in the
w. :)
Now I need to read the file and execute it - basically execute the contents
of the file. I'm looking for suggestions on how to do it. I thought about
using the code below to read the contents into a global var but it seems as
if it only reads so many chars into a global var. My thought was to read it
into a global var then use an execute sql task to execute the global var.
Here is what I have so far. Any suggestion on the best way to apply the
create index statements in the text file?
Thanks, Andre
Function Main()
Set fso = CreateObject("Scripting.FileSystemObject")
Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
' msgbox textStreamObject.ReadAll
DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
Set textStreamObject = Nothing
Set fso = Nothing
Main = DTSTaskExecResult_Success
End Function
Sample of my TableIndexes.txt file
ALTER TABLE [MYTABLE] ADD
CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
(
[RowID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GOWhy don't you just use OSQL or SQLCMD to do this?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andre" <no@.spam.com> wrote in message news:eufKUxwTGHA.1728@.TK2MSFTNGP11.phx.gbl...darkred">
>I have a text file that contains "create index" statements - thanks to thos
e of you here who helped
>me get past my hurdles with this step earlier in the
> w. :)
> Now I need to read the file and execute it - basically execute the content
s of the file. I'm
> looking for suggestions on how to do it. I thought about
> using the code below to read the contents into a global var but it seems a
s if it only reads so
> many chars into a global var. My thought was to read it
> into a global var then use an execute sql task to execute the global var.
> Here is what I have so far. Any suggestion on the best way to apply the c
reate index statements
> in the text file?
> Thanks, Andre
> Function Main()
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
> ' msgbox textStreamObject.ReadAll
> DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
> Set textStreamObject = Nothing
> Set fso = Nothing
> Main = DTSTaskExecResult_Success
> End Function
>
> Sample of my TableIndexes.txt file
> ALTER TABLE [MYTABLE] ADD
> CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
> (
> [RowID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>|||Since you are reading the file from VBScript, you could simply store the
string in a local variable and execute as a statement using
ADODB.Command.Execute method call from the same script:
http://msdn.microsoft.com/library/d.../>
xecutex.asp
It seems there is no need to store the string in a DTS variable, just a
local variable. For example:
Dim strIndexes
If this task does not need to be schedules, then perhaps instead of DTS, use
Windows Scripting Host or OSQL from the command line.
"Andre" <no@.spam.com> wrote in message
news:eufKUxwTGHA.1728@.TK2MSFTNGP11.phx.gbl...
>I have a text file that contains "create index" statements - thanks to
>those of you here who helped me get past my hurdles with this step earlier
>in the
> w. :)
> Now I need to read the file and execute it - basically execute the
> contents of the file. I'm looking for suggestions on how to do it. I
> thought about
> using the code below to read the contents into a global var but it seems
> as if it only reads so many chars into a global var. My thought was to
> read it
> into a global var then use an execute sql task to execute the global var.
> Here is what I have so far. Any suggestion on the best way to apply the
> create index statements in the text file?
> Thanks, Andre
> Function Main()
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set textStreamObject = fso.OpenTextFile("c:\TableIndexes.txt",1,false,0)
> ' msgbox textStreamObject.ReadAll
> DTSGlobalVariables("strIndexes").Value = textStreamObject.ReadAll
> Set textStreamObject = Nothing
> Set fso = Nothing
> Main = DTSTaskExecResult_Success
> End Function
>
> Sample of my TableIndexes.txt file
> ALTER TABLE [MYTABLE] ADD
> CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED
> (
> [RowID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [prov_id] ON [MYTABLE]([Col1]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
> CREATE INDEX [ref_id] ON [MYTABLE]([Col2]) WITH FILLFACTOR = 90 ON
> [PRIMARY]
> GO
>|||It does need to be scheduled, and is actually part of a large DTS package.
I did change it to be in a local var rather than a global var - thanks for
that tip. It's working great now, thanks for your help.
Andre

Read / Write Into .doc From SQL Stored Procedure

I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?

Thank you.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

RE: Creating A Duplicate Database Without Enterprise Manager

Hi There,

I was wondering if there was a simple way of create a duplicate of an entire database.

I often need to create a second copy of a database for development purposes. I have lost the use of Enterprise Manager which I can no longer simply script the database and then use dts to move the data across.

Is there a way of doing this in T-SQL?

Thanks

SteveTake a full backup and restore as a different name ... that should do it .. i think ... rather than going about it the other way round.|||You lost the use of Enterprise Manager?

HUH?|||I think they're an amputee|||Perhaps it was destroyed by a Klingon Manager?|||Or a Romulan cloaking device has been attached, so it's really there, he just can't see it ;-)|||Could have been an Organian illusion all along...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55463

Re: What is the syntax for isnull in the IIF function in the query statement

Hi all,

What is the syntax for isnull in the IIF function in the query statement for SQL Server2K?

I'm trying to create a view to get a total in a field. If the quantity is null I want to display 0 in that field.

Thanks!!

SELECT dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, IIf(dbo.tblBag_data.bag_quantity IS NULL, 0,
dbo.tblBag_data.bag_quantity) AS qty_on_hand, dbo.tblShipping_sched.cust_num, dbo.tblShipping_sched.cust_name,
dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_num
FROM dbo.tblShipping_sched LEFT OUTER JOIN
dbo.tblBag_data ON dbo.tblShipping_sched.work_ord_line_num = dbo.tblBag_data.work_ord_line_num AND
dbo.tblShipping_sched.work_ord_num = dbo.tblBag_data.work_ord_num
GROUP BY dbo.tblShipping_sched.work_ord_num, dbo.tblShipping_sched.work_ord_line_num, dbo.tblBag_data.bag_quantity, dbo.tblShipping_sched.cust_num,
dbo.tblShipping_sched.cust_name, dbo.tblShipping_sched.apple_part_num, dbo.tblShipping_sched.apple_catalog_numsql server doesn't support IIF, you have to use CASE

incorrect --

IIf(dbo.tblBag_data.bag_quantity IS NULL, 0, dbo.tblBag_data.bag_quantity)

correct --

case when dbo.tblBag_data.bag_quantity IS NULL then 0 else dbo.tblBag_data.bag_quantity end

Re : Enabling / Disabling Data Flow Tasks

Hello,

I have created around 10 seperate packages for our application data load. Now I am planning to create a master package (or a wrapper package) which will execute all the 10 packages (thru execute package task). Then I have a job which executes the master package at a given date and time.

Question : How can I enable / disable execution of each package within the master package depending upon a flag variable. The reason why I need this mechanism is if the flag = 0 then I don't want all 10 packages within master package to execute and if flag = 1 then master package execution should begin and subsequently execute all packages within that master package.

Thank you

Jatin Shah

I am sure you could find a way to do this using the enable/disable method, however you may find it easier to use precedence constraints. Create a dummy (null-op) task in the parent package that is the first object to run, and connect it to all 10 sub-package tasks using precedence contraints. Double-click each precedence constraint, set the option to 'success and expression' and set the expression to "@.var == 1" (or 0 as the case may be)

Pretty much the same as what you are proposing, except the precedence constraint is being 'disabled' through a supported mechanism, instead of the tasks itself.

|||

Hello Grant,

Thank You for suggesting a way out.

I tried doing what you have explained, but when I try to set the expression as below

"@.flg == 1" it raises an error.

Thank You

Jatin Shah

|||

What's the error message?

You should check this out: http://www.sqlis.com/default.aspx?306

-Jamie

RDL to RDLC Dataset Bind Question

We got some existing Reports that are set to RDL files but now they want to be able to create a web user interface so the user can interact through such method. As I am finding out, these reports need to be converted to RDLC so that they can all the proper information so they can be binded. My question is, the datasets are already set (I beleive it's the .rdlc.data files) on the RDL solution project. Now I want transfer those DataSets to my Website project and bind those datasets to my Report Viewer Control. Is that the right approach? Or do we have to re-create the Datasets and then bind? Can I use Web Services to bind to these reports? Thanks for the input and all help is appreciated.

there is no need to do this what you need to do is to just deploythose reports on the reporting server then create web page and addreport viewer control to it to enable the user view the reports youdeveloped you can see this example to know how to do this

http://www.c-sharpcorner.com/UploadFile/asif.blog/DynamicDataGrouping08042006115320AM/DynamicDataGrouping.aspx

http://www.gotreportviewer.com/

RDL Generation

Does anyone know of projects in development to create RDL files from HTML
and/or PDF files? I have to think that if Microsoft has created a render
from RDL to PDF that the reverse would be fairly simple to complete as well.I doubt there is such a thing currently...I certainly haven't seen it
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"rbowlin" <rbowlin@.discussions.microsoft.com> wrote in message
news:D9FFCD4B-845B-42DD-895D-7815E5E0828F@.microsoft.com...
> Does anyone know of projects in development to create RDL files from HTML
> and/or PDF files? I have to think that if Microsoft has created a render
> from RDL to PDF that the reverse would be fairly simple to complete as
> well.|||There is a *huge* difference in going from RDL to <format> versus <format>
to RDL. RDL is a data-driven template for producing a report, and is far
more succinct. Any <format> is the marriage of a template and a specific
set of data.
Reverse engineering an instance of a report into its template would require
a lot of pattern recognition. Some repeating groups like headings and such
could be easily determined, but depending on the flexibility of the source
report generator, some of the other patterns could be a bear to parse.
Also, depending on the set of data used to generate a report instance, you
may not get complete fidelity in the template generation as the data set may
not exercise all of the possible scenarios.
Teaching software to parse <format> would not be impossible, but it would be
difficult and would likely capture less than the full template in the end.
A better shot would be to convert a known product's template to RDL.
Hitachi Consulting (not my firm) has engagement software that takes Crystal
Reports and produces RDL, for example.
Cheers, Mark
"rbowlin" <rbowlin@.discussions.microsoft.com> wrote in message
news:D9FFCD4B-845B-42DD-895D-7815E5E0828F@.microsoft.com...
> Does anyone know of projects in development to create RDL files from HTML
> and/or PDF files? I have to think that if Microsoft has created a render
> from RDL to PDF that the reverse would be fairly simple to complete as
> well.

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.

rda.SubmitSql cause outofmemoryexception

My application create quite a lot log file to submit to the server under WiFi coverage to monitor the application. (log is just txt file). as time goes on, submitsql method throw OutOfMemoryException. I agree my log file is quite big, 80K each and 6 log files to submit.
My log submitter is an instance object. I also tried static object but it doesn't help either.

I do dispose my rda object when it is out.

Is any way I can fix this?Could you provide som sample code - I do not understand the connection between the log files and submitsql - are you submitting the log file text in an insert statement?|||

Sure.

public LogSubmitter()

{

//some local objects.

public void SubmitLog(string logName, SqlCeRemoteDataAccess rda)

{

try

{

//

//read text from log file and append it to StringBuilder: logBuilder

//

string logCmd = "EXEC InsertLog "+"'"+logBuilder.ToString()+"'";

rda.SubmitSql(logCmd ,remoteConnStr);

}

catch(Exception ex)

{

Log.WriteException(ex);

}

}

}

where, InsertLog is a stored procedure on the server.

Is there is problem with this code?

PS: I only submit log when device is under WiFi coverage. rda object is disposed in the caller.

|||Have a close look at your StringBuilder - have you set a useful initial capacity - otherwise you should try that.|||

Thank you very much.

You are right, I didn't set initial capacity. Another reason could be I submit 6 log files continously, each log about takes 80k (max).

But one thing I cannot understand is that I called logBuilder.Remove(0,logBuilder) before submitting each log, so each stringbuilder should be empty before appending new log.

|||Did we solve your outofmemory exeption problems, then?|||

Just got log from my test scanner.

It still has this exception. I will do more changes with "capacity" property.

By the way, does StringBuilder has maximum capacity?

Cheers

|||It does, but the MacCapacity property is not available in NETCF, (according to docs) so who knows what it may be?|||

Thanks Erik.

Is StringBuilder's memory reused if we remove contents from it and then append string again?

|||

Yes, ErikJ, I resolved this issue by checking the length before appending new chars.

The StringBuilder throws "OutofMemoryException" when it's length is greater than 2359294.

Thanks.

|||

Hi, ErikEJ;

Now I have this exception again. The "Length" property is 57341 when this happens.

And I call "Remove" method after I finish submitting a log. It looks like GC doesn't collect those memory.

Thanks.