Assistance developing Query

Good Day;

I would appreciate assistance developing a query that I haven't been
able to develop without using a second table. I wish to count the
number of records that are still open on the first of each month.
Each record has an open date and a close date or the close date is
null i.e., the record is not yet closed. I've previously beaten this
by building a table, simply a list of the dates for the first of each
month for the next ten years or so, and then selecting values based
upon a date selected from that table. However I'd be happier if I
could do it without the second table. I'd be prepared to accept the
Min(Date) for each month as being the first of the month.

I've included some DDL statements to build and populate the table if
that helps. Since the selection is rather small and all the open
dates are very close together I think the result will be simply a
decreasing count from the month the first record is opened till today.

A pseudo code select statement might look like

Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
FROM DetailT
Where DateReceived > DateOfInterest or DateReceived is Null and
DateOpened < DateOfInterest
Group by Min(DateOpened)
Order by Min(DateOpened)

I hope I've explained it sufficiently well.

CREATE TABLE [dbo].[DetailT] (
[Autonum] [int] IDENTITY (1, 1) NOT NULL ,
[QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOpened] [smalldatetime] NOT NULL ,
[DateReceived] [smalldatetime] NULL ,

)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C15788', '06/04/2005 9:35', 07/04/2005)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B16091', '06/04/2005 9:36', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B15001', '06/04/2005 9:51', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('M18696', '06/04/2005 9:56', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C14969', '06/04/2005 10:05', '10/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('O10091', '06/04/2005 10:08', '12/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('D01197', '06/04/2005 10:13')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('H15001', '06/04/2005 10:15', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('J15090', '06/04/2005 10:24', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('J01202', '06/04/2005 10:31')
Insert into DetailT (QDNumber, DateOpened)
VALUES('G01193', '06/04/2005 10:32')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01164', '06/04/2005 10:35')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01162', '06/04/2005 10:48')
Insert into DetailT (QDNumber, DateOpened)
VALUES('F01124', '06/04/2005 10:59')
Insert into DetailT (QDNumber, DateOpened)
VALUES('H01147', '06/04/2005 11:01')
Insert into DetailT (QDNumber, DateOpened)
VALUES('S15068', '06/04/2005 11:10')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('E12322', '06/04/2005 11:32', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('A12205', '06/04/2005 11:37', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('D12259', '06/04/2005 11:40', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('C03394', '06/04/2005 11:51')

If you made it this far thank you for your patience. Any help would be
appreciated.

Thank you.

BillBill wrote:

> Good Day;
> I would appreciate assistance developing a query that I haven't been
> able to develop without using a second table. I wish to count the
> number of records that are still open on the first of each month.
> Each record has an open date and a close date or the close date is
> null i.e., the record is not yet closed. I've previously beaten this
> by building a table, simply a list of the dates for the first of each
> month for the next ten years or so, and then selecting values based
> upon a date selected from that table. However I'd be happier if I
> could do it without the second table. I'd be prepared to accept the
> Min(Date) for each month as being the first of the month.
> I've included some DDL statements to build and populate the table if
> that helps. Since the selection is rather small and all the open
> dates are very close together I think the result will be simply a
> decreasing count from the month the first record is opened till today.
> A pseudo code select statement might look like
> Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
> FROM DetailT
> Where DateReceived > DateOfInterest or DateReceived is Null and
> DateOpened < DateOfInterest
> Group by Min(DateOpened)
> Order by Min(DateOpened)
> I hope I've explained it sufficiently well.
> CREATE TABLE [dbo].[DetailT] (
> [Autonum] [int] IDENTITY (1, 1) NOT NULL ,
> [QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateOpened] [smalldatetime] NOT NULL ,
> [DateReceived] [smalldatetime] NULL ,
> )
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C15788', '06/04/2005 9:35', 07/04/2005)
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B16091', '06/04/2005 9:36', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B15001', '06/04/2005 9:51', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('M18696', '06/04/2005 9:56', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C14969', '06/04/2005 10:05', '10/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('O10091', '06/04/2005 10:08', '12/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('D01197', '06/04/2005 10:13')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('H15001', '06/04/2005 10:15', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('J15090', '06/04/2005 10:24', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('J01202', '06/04/2005 10:31')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('G01193', '06/04/2005 10:32')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01164', '06/04/2005 10:35')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01162', '06/04/2005 10:48')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('F01124', '06/04/2005 10:59')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('H01147', '06/04/2005 11:01')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('S15068', '06/04/2005 11:10')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('E12322', '06/04/2005 11:32', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('A12205', '06/04/2005 11:37', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('D12259', '06/04/2005 11:40', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('C03394', '06/04/2005 11:51')
> If you made it this far thank you for your patience. Any help would be
> appreciated.
> Thank you.
> Bill

Where the SQL statement you would use to load the table?
Put parentheses around it.
Go from there.
There may be far more elegant solutions but your the one getting
the paycheck. ;-)
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||On 18 Apr 2005 16:36:38 -0700, Bill wrote:

>I would appreciate assistance developing a query that I haven't been
>able to develop without using a second table.
(snip)
>I've previously beaten this
>by building a table, simply a list of the dates for the first of each
>month for the next ten years or so, and then selecting values based
>upon a date selected from that table. However I'd be happier if I
>could do it without the second table.

Hi Bill,

Why do you want to do it wothout a second table? Having a permanent
auxiliary calendar table in your database is actually quite useful and I
think that no database should ever be without one.
Here's a link to an article that shows how to create a general
all-purpose calendar table, how to fill it with data and several
examples of how to use it: http://www.aspfaq.com/show.asp?id=2519.

And here's how I'd write your query, using the table described above:

SELECT c.dt,
COUNT(*) as "Qty Still Open"
FROM Calendar AS c
INNER JOIN DetailT
ON ( DateReceived > c.dt OR DateReceived IS NULL )
AND DateOpened < c.dt
WHERE c.D = 1
AND c.dt BETWEEN (SELECT MIN(DateOpened)
FROM DetailT)
AND DATEADD(month, 1, (SELECT MAX(DateReceived)
FROM DetailT))
GROUP BY c.dt
ORDER BY c.dt

This one cuts off the listing at the lowest number. If you increase the
number (1) in the DATEADD expression, you can see that the number of
open cases remains constant after the last month listed.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||DA Morgan <damorgan@.x.washington.edu> wrote in message news:<1113891730.819997@.yasure>...
> Bill wrote:
> > Good Day;
> > I would appreciate assistance developing a query that I haven't been
> > able to develop without using a second table.

> > Thank you.
> > Bill
> Where the SQL statement you would use to load the table?
> Put parentheses around it.
> Go from there.
> There may be far more elegant solutions but your the one getting
> the paycheck. ;-)

Dan;

I'm sorry but I don't understand what you're trying to tell me. If
your first question is "Where is the SQL statement you would use to
load the table?" I don't have one, the data base is interactively
updated through an ASP based HTML form over the web. I did go to the
work of providing insert statements to assist anyone who might try to
help.

I don't understand what putting parentheses around it would do?

I hope there is a more elegant solution but unfortuantely I haven't
figured it out and was simply asking for assistance. If you don't
wish to help thats ok with me. Yes, I'm getting paid for the work I
do, but developing this solution is not what I was trained or educated
to do an so I'm learning as I go. I appologize if I've bothered you
by asking for help.

I will continue to explore the solution.

Cheers;

Bill|||Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<8oqa61p4rgmhtcfh7rgsc8dup9pj1a9d1t@.4ax.com>...
> On 18 Apr 2005 16:36:38 -0700, Bill wrote:
> >I would appreciate assistance developing a query that I haven't been
> >able to develop without using a second table.
> (snip)
> >I've previously beaten this
> >by building a table, simply a list of the dates for the first of each
> >month for the next ten years or so, and then selecting values based
> >upon a date selected from that table. However I'd be happier if I
> >could do it without the second table.
> Hi Bill,
> Why do you want to do it wothout a second table? Having a permanent
> auxiliary calendar table in your database is actually quite useful and I
> think that no database should ever be without one.
...
> This one cuts off the listing at the lowest number. If you increase the
> number (1) in the DATEADD expression, you can see that the number of
> open cases remains constant after the last month listed.
> Best, Hugo

Hugo;

Thank you for your assistance. I've reviewed the article you pointed
me to and have spent my spare time today building a number table and a
date table. I was reluctant to duplicate my earlier date table, which
only contained the dates for the first of the month into the SQL
Server environment since I felt that it was both cheating and
confusing. However the article showed that this can be a very useful
table and since it's been published I don't feel too bad about
emulating someone's work who know more about application development
than I do.

I do have one question, based mainly on my lack of formal training in
SQL Server and my experience this morning building the calendar table.
I was copying the code from the article and pasting it into SQL Query
Analyzer and running it, as I'm not certain where or how this code
should be executed. Most things ran very quickly after I modified
them properly to meet my environment. However the adding of row's to
the Calendar table (4,096) took several hours (2-3) I was really
surprised by this and wondered if I was doing something wrong but
since it finished successfully and subsequent code samples executed
quickly I moved on. My question is am I using the right part of the
SQL Server environment for this sort of work?

Thank you once again for your time and your assistance. Now that I've
overcome my reluctance to using the calendar table I'm comfortable
enough to go on and using you sample query to get what I was looking
for.

Thank you.

Cheers;

Bill|||On 20 Apr 2005 16:06:36 -0700, Bill wrote:

(snip)
>the article showed that this can be a very useful
>table and since it's been published I don't feel too bad about
>emulating someone's work who know more about application development
>than I do.

Hi Bill,

You certainly should not feel bad about it - sharing the code is exactly
the reason why Aaron has published it on his site.

Copying work from others may be a sin in artistic creative work, but in
software development, it's a sin NOT to copy and adapt proven solutions.

(snip)
>Most things ran very quickly after I modified
>them properly to meet my environment. However the adding of row's to
>the Calendar table (4,096) took several hours (2-3) I was really
>surprised by this

And so am I. Okay, the server does have a bit of work to do when
populating the table, but under normal circumstances, I would not expect
it to run for so long! A couple of minutes, maybe. Not hours.

I'm not sure if you still care to investigate this (since you now have
the table, and it's a one-time job after all), but if you do, then could
you please post the exact code you used to create and popultae the
tables?

>My question is am I using the right part of the
>SQL Server environment for this sort of work?

Yes, Query Analyzer is exactly the tool to use for these jobs.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Assign name to the DB users.

I'm developing a vb 2005 application and I’m creating the users directly to the database. I want to assign them names.

I want to do something like this:

CREATE TABLE admin.db_users (

id INT CONSTRAINT db_user_pk PRIMARY KEY,

[name] VARCHAR(50) CONSTRAINT db_user_name_nn NOT NULL,

authentication VARCHAR(25) CONSTRAINT db_user_authentication_nn NOT NULL,

CONSTRAINT db_user_fk FOREIGN KEY(id)

REFERENCES sys.database_principals (principal_id)

ON UPDATE CASCADE

ON DELETE CASCADE

);

GO

This is the error that i'm getting:

Msg 1767, Level 16, State 0, Line 1

Foreign key 'db_user_fk' references invalid table 'sys.database_principals'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

How do I solve this problem or how can I do something similar.

You cannot create foreign key references to views. A different way to get functionality close to what you need would be to use insert/update trigger on the db_users table where the trigger action verifies the existance of the user in sys.database_principals. Then define a DDL trigger on the database for the DDL_USER_EVENTS (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb2a7bd0-2347-488c-bb75-734098050c7c.htm) to catch DROP/ALTER USER events and do the appropriate delete/cascade action.|||

Hi,

I’m new in these if you or some body could to help me I will appreciated. How can I create that trigger?

This is the script of the table that I want to use if this is possible:

CREATE TABLE admin.db_users (

idINT CONSTRAINT user_pk PRIMARY KEY,

[name]VARCHAR(50) CONSTRAINT user_name_nn NOT NULL,

authenticationVARCHAR(25) CONSTRAINT user_authentication_nn NOT NULL,

activeBIT

);

|||

Here's a sample that will give you a sense of what can be done and get you started. You will need to modify/build on the sample to achieve what you need. Please check the "CREATE TRIGGER" topic in Books Online for more detailed information on using triggers.

-- DML Trigger to verify users against sys.database_principals
--
create trigger check_users on db_users
for insert, update
as
-- Collect inserted users that don't exist in sys.database_principals
declare @.invalid_users table([name] sysname)
insert into @.invalid_users
select convert(sysname, name) from inserted except select [name] from sys.database_principals

-- If invalid users are found, rollback transaction
if exists (select [name] from @.invalid_users)
begin
print 'Operation was aborted because following users are invalid'
select [name] as invalid_users from @.invalid_users
if @.@.trancount > 0 rollback tran
end
go

-- DDL Trigger to catch create/drop/alter of new database principals
--
create trigger user_ddl_trig
on database
for ddl_user_events
as
declare @.user_name sysname,
@.event_type sysname

select @.event_type = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','sysname')
select @.user_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

-- Do your cascased action
if @.event_type = 'DROP_USER'
begin
print 'dropping user ' + @.user_name
delete from db_users where [name] = @.user_name
-- Or check against db_users and rollback this transaction, etc
end
--else if @.event_type = 'ALTER_USER'...
go

|||Thanks a lot.

ASPState database transaction log out control

I am developing an Asp.Net web site which is using SQL Server session state
being held in the ASPState database. I noticed recently that the ASPState
database transaction log had grown to 7.5GB despite the face that only a
small number of users were using the web site which is still under
development. Any reason for this runaway growth? Build as below
..Net Framework 1.1
Windows Server 2003 Standard Edition SP1
Sql Server 2000 Standard Edition SP4
Scott
Hi
Your are in Full recovery mode and are not backup up your log.
http://msdn.microsoft.com/library/de...kprst_565v.asp
http://www.dbazine.com/sql/sql-artic...lins-sqlserver
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scottrm" <scottrm@.newsgroup.nospam> wrote in message
news:36A7060E-C318-46AC-B128-DB491B883A0B@.microsoft.com...
>I am developing an Asp.Net web site which is using SQL Server session state
> being held in the ASPState database. I noticed recently that the ASPState
> database transaction log had grown to 7.5GB despite the face that only a
> small number of users were using the web site which is still under
> development. Any reason for this runaway growth? Build as below
> .Net Framework 1.1
> Windows Server 2003 Standard Edition SP1
> Sql Server 2000 Standard Edition SP4
> --
> Scott
|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVSUQoL$FHA.3096@.tk2msftngp13.phx.gbl...
> Hi
> Your are in Full recovery mode and are not backup up your log.
>
You should run the ASP.NET state database in simple recovery mode unless you
are trying to do log shipping or something with it. Better yet, use the
script to create all its objects in TempDB intead of their own database.
David
|||Hi Scott,
You may also refer the articles below for more information
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/kb/317375/en-us
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/kb/873235/en-us
Topic: DBCC SHRINKDATABASE / DBCC SHRINKFILE in BOL
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

ASPState database transaction log out control

I am developing an Asp.Net web site which is using SQL Server session state
being held in the ASPState database. I noticed recently that the ASPState
database transaction log had grown to 7.5GB despite the face that only a
small number of users were using the web site which is still under
development. Any reason for this runaway growth' Build as below
.Net Framework 1.1
Windows Server 2003 Standard Edition SP1
Sql Server 2000 Standard Edition SP4
--
ScottHi
Your are in Full recovery mode and are not backup up your log.
http://msdn.microsoft.com/library/d... />
t_565v.asp
http://www.dbazine.com/sql/sql-arti...llins-sqlserver
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scottrm" <scottrm@.newsgroup.nospam> wrote in message
news:36A7060E-C318-46AC-B128-DB491B883A0B@.microsoft.com...
>I am developing an Asp.Net web site which is using SQL Server session state
> being held in the ASPState database. I noticed recently that the ASPState
> database transaction log had grown to 7.5GB despite the face that only a
> small number of users were using the web site which is still under
> development. Any reason for this runaway growth' Build as below
> .Net Framework 1.1
> Windows Server 2003 Standard Edition SP1
> Sql Server 2000 Standard Edition SP4
> --
> Scott|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVSUQoL$FHA.3096@.tk2msftngp13.phx.gbl...
> Hi
> Your are in Full recovery mode and are not backup up your log.
>
You should run the ASP.NET state database in simple recovery mode unless you
are trying to do log shipping or something with it. Better yet, use the
script to create all its objects in TempDB intead of their own database.
David|||Hi Scott,
You may also refer the articles below for more information
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/kb/317375/en-us
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/kb/873235/en-us
Topic: DBCC SHRINKDATABASE / DBCC SHRINKFILE in BOL
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

ASPState database transaction log out control

I am developing an Asp.Net web site which is using SQL Server session state
being held in the ASPState database. I noticed recently that the ASPState
database transaction log had grown to 7.5GB despite the face that only a
small number of users were using the web site which is still under
development. Any reason for this runaway growth' Build as below
.Net Framework 1.1
Windows Server 2003 Standard Edition SP1
Sql Server 2000 Standard Edition SP4
--
ScottHi
Your are in Full recovery mode and are not backup up your log.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_565v.asp
http://www.dbazine.com/sql/sql-articles/mullins-sqlserver
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scottrm" <scottrm@.newsgroup.nospam> wrote in message
news:36A7060E-C318-46AC-B128-DB491B883A0B@.microsoft.com...
>I am developing an Asp.Net web site which is using SQL Server session state
> being held in the ASPState database. I noticed recently that the ASPState
> database transaction log had grown to 7.5GB despite the face that only a
> small number of users were using the web site which is still under
> development. Any reason for this runaway growth' Build as below
> .Net Framework 1.1
> Windows Server 2003 Standard Edition SP1
> Sql Server 2000 Standard Edition SP4
> --
> Scott|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVSUQoL$FHA.3096@.tk2msftngp13.phx.gbl...
> Hi
> Your are in Full recovery mode and are not backup up your log.
>
You should run the ASP.NET state database in simple recovery mode unless you
are trying to do log shipping or something with it. Better yet, use the
script to create all its objects in TempDB intead of their own database.
David|||Hi Scott,
You may also refer the articles below for more information
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/kb/317375/en-us
How to stop the transaction log of a SQL Server database from growing
unexpectedly
http://support.microsoft.com/kb/873235/en-us
Topic: DBCC SHRINKDATABASE / DBCC SHRINKFILE in BOL
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

asp.net Stored Procedures vs not

Hey,

I'm developing an asp.net page that will be looking at having approx 500 000 members.

I have be warned by my webhost admin not to use stored procedures. They say that they are much less efficient with large databases. Harder to manage, and will lock me down.

This struck me as odd, as everything I have read and done so far points to SP's being the way to go.

People that have experience with large databases, what advice / comments can you give me? Should I use stored procedures, or should I put all my sql in the asp.net pages.

My situation, asp.net, ms sql. I will be storing the users in user groups, each group will have its own table containing member information, each group will have between 20 and 500 members. Each group will also have 2-3 tables associated with it. (not sure if this information is relevant, but if it is, there ya go).

ThanksWhat?? The only way I could see it being harder to manage would be if they didn't know what the heck they were doing with stored procedures...

I've been developing web sites with database interaction for the last 6 years or so and IMO stored procs are a must.

Stored procedures are less efficient with large databases?? Um,... no,... especially not if you are comparing them to straight sql statements being executed... yes, you can get problems with execution paths not using the right indexes but if you know what you are doing it's not a big issue...|||Phew, thought I was going mad. Because I couldn't understand why not to use SP's, lol.

One question though. (here we see the n00b emerge) lol

-Quote
yes, you can get problems with execution paths not using the right indexes but if you know what you are doing it's not a big issue...

I don't follow what people mean when they say indexing etc. I've sort of learn sql by playing and reading forums. I've never actually found a good book I thought worth buying (or resource really) other than these forums, lol.

How can I avoid bad indexing? What is indexing? This may be big and hard to answer, so if you'd rather throw me at a resource (online or book), do it, lol. A little reading never hurt anybody. It's a lot of 'reading the wrong stuff' that hurts, lol.

Thanks again rokslide,
-Ashleigh|||In short an index is exactly that....

Think about a book,.. it has an index that tells you where to find what you are looking for...

Tables are like books and they can have indexes,...

The indexes help "organise" the data and mean that when you are in there looking for things you can find it alot faster... Indexes will make the database take up more space but generally the performance increase and the reduced table locking will be well worth it...

It's difficult to say exactly what is bad indexing. I guess it's indexes that are too large for the return they give...

The problem I was referring to is... stored procedures are compiled, when they compile the build and execution plan and decide what indexes they will use. Sometimes they will not pick the best index and sometimes the best index will change depending on the data in the table. When this helps you need to force the stored procedure to build a new execution plan. This is probably not technically right (eg I have the phrases wrong) but the gist of it is correct from my understanding...

Deciding what indexes you want to build really depends on what you want to search the table for and how the data in the table relates to other things... drop me a PM (do we have PM's here?) and I can help you if you want to provide specific details.

ASP.NET DataGrid Problem

Hi,

As a relative newbie to SQL Server/ASP.NET I'm hoping someone here
can help with my problem. I'm developing a timesheet application in
ASP.NET C# using Visual Studio 2003 with a database built in MSDE.
One of my forms needs to return a simple list of resources from my
database. I have followed the guide on the MSDN libraries, but for
some reason I continuously get the same error message.

What I've done so far is Create the database, tables, and populate
with some sample data using using Server Explorer in Visual Studio. I have
connected to the database (using integrated security) and I am
trying to get the contents of the Resource table to appear on my
form. I have then created a DataAdapter (tested the connection, set
the SQL as a simple SELECT * from Resource, etc), which also generates an sqlConnection for me. To test this I have previewed the generated data, and it returns what I want, so I
have chosen to generate a DataSet of this. I am then trying to get
this data into a simple DataGrid. On the properties of the DataGrid
I have changed the DataSource to point at my Dataset. As I
understand it, I then have to add the following to my Page Load
section of my code.

sqlConnection1.Open();
this.sqlDataAdapter1.Fill(this.dsResource);
DataGrid1.DataBind();
sqlConnection1.Close();

The form builds fine, but when I browse to the particular form I get
the following error for the sqlConnection1.Open(); line. If I remove this line the error simply moves to the line below.

Exception Details: System.Data.SqlClient.SqlException: Cannot open
database requested in login 'SCMS'. Login fails. Login failed for
user 'AL-NOTEPAD\ASPNET'.

To me this is an error with my connection string. My database
instance is actually 'AL-NOTEPAD\VSDOTNET'. However the properties
for sqlConnection1 are pointing to the correct datasource. I do not
know why the application is looking for user 'AL-NOTEPAD\ASPNET'. It does not exist, to my knowledge. Do I need to grant access to this user? If so, how would I do it? Bearing in mind I am using MSDE, and do not have Enterprise Manager.

Any help with this would be greatly appreciated, as I get the same
error with my code for forms-based login...

Thanks in advance..

Hi there, welcome to the ASP.NET Forums!

The root of the problem is that Visual Studio will use *your* credentials to log in to the SQL Server and access the database when you are using integrated security. However, when you run the site through a web server, it will use the ASPNET account. And, as you are experiencing, the ASPNET account does by default have those needed permissions.

To solve this problem, you have 2 options. You can use impersonation in your web.config, so that your web application will impersonate whatever user you desire (which presumably has permissions to your SQL Server and database). Or, you can grant the needed permissions to the ASPNET account.

Seethis post for instructions on how to grant the needed permissions to the ASPNET account.|||

Hi,

Thanks for all the help, I've sorted the problem in the short term by
downloading an MSDE alternative to Enterprise Manager
(http://www.asql.biz/DbaMgr.shtm) and assigned the relevant access to
the ASPNET user account. The DataGrid now works.

Thanks again

Al

Asp.net 2.0 Vista Sql Express Error

I am developing a web site in asp.net 2.0 using visual studio 2005 and SQL express.

I tried to add a new Sql database using right click on the App_Data folder and adding the DB.

I get an error that the VS could not find a sql Express installed.

I want to clarify that I have installed the SQL express SP2 for vista version and that I can connect to SQL express db using management studio.

I think it is a vista issue probably that UA.

What can I do?

pls do not recommend the next step I already tried itL

If I create the DB on XP and transfer the project to my vista machine the db is there but I cannot connect to it.

Hi,

There are many side-effects with Vista. SQL Server Express will be supported with SP2 (which is not RTM yet) for Vista and Visual Studio won′t be supported after applying SP1 and additional hotfixes, So I guess its hard to help you here, sorry. Using Vista in that case for testing purposes would be ok as of my opinion but using it for productional developing would be too early for me.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Itzik,

FYI - VS 2005 is not certified on Vista yet, so this may well be a VS issue.

When you installed SQL Express SP2 did you check the option to create yourself as an administrator? If you did not, you would not have permission to create a database from anywhere. Any user on the computer has the ability to connect to SQL Express, but not necesarily create objects. Have you confirmed if you can create database using Management Studio? If you can't, you don't have the right permissions set.

Information on getting Express SP2 working on Vista is avaliable here.

Mike

|||

OK GUYS

First of all thanks for trying to help.

I decided to fought with it until I solve it.

Yes I did check the administrator button (but even if I am not I could always add myself to the sysadmin group manually, but it is a blessing change in SP2).

This is what went wrong if anybody else asks:

1. I installed the Sql express SP2 as default. The visual studio when adding a file db using SQL express is looking for the instance SQLEXPRESS ([computer name]\Sqlexpress is the full name).

2. I went to Tools and changed the instance name to blank as they ask me to in case of default installation. But that did not work fully (I could create the DB file but couldn't access it through the asp .net configuration pages).

3. So on that point I uninstalled the SQL express and installed it again under the required Sqlexpress (Shooting in the dark assuming the default is not supported under vista or something like that).

Amazingly it worked great. (I also installed it under local system rather than Network service so maybe this helps too).

Hope I saved some lost souls

Itzik Katzav

ASP.NET 2.0 and SQL Server Express Backup

I′m developing an application that uses SQL Server Express as the Back-End, i want to provide to the user an inteface where he can make backup of the database or restore a backup file, i really don′t know where i can begin with this task or what′s the best approach for doing stuff like this, i need some guidance here because we finish with the application and we are just adding some usefull features to the user.

If somebody has any idea, i am willing to learn and listen carefully. thnaks to everybody.

Thanks to everybody, CesarIf you are running under Full Trust, you can make use of the SMO library. See, for example,Using SMO for Backup,Restore and Security Purposes orGetting Started with SMO in SQL 2005 - Backups|||I try with the"Getting Started with SMO in SQL 2005 - Backups" sample and it doesn't show me the SQL Express instances, i got installed a SQL Server Standard and a SQL Express instance and the sample app only shows me the Standard instance.

I dont know if this got something to do with the "full trust"(i'm sorry but i dont know what it is).Could you be more specific ?.

Cesar

ASP.NET 2.0 and SQL Server 2000 connection issue

Hi,
I am developing an ASP.NET 2.0 application and SQL Server 2000 as
back-end. The connection to DB works fine if I connect to my local or
our dev databases. It is giving me the error when I try to connect to
Test database. However, I am able to connect to the same database
through Enterprise Manager by using same credentials. I tried using
TCP/IP and named pipe connections.
I am using the following connection string.
Password=password;Persist Security Info=True;User ID=User;Initial
Catalog=db;Data Source=server\instance;Network Library=DBMSSOCN;"
I am getting the following errors.
When using TCP/IP connection:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: TCP Provider, error: 0 - No connection could be
made because the target machine actively refused it.)
When using Named Pipes:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)
Any help is appreciated.
Thanks,
SreedharIs this a 2000 instance with a SQL server 2005 instance running as well?
A SQL 2000 with .net connection should look like...
Standard Security:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User
Id=sa;Password=asdasd;"
a.. Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated
Security=SSPI;"
You might have some issues if you are running different instances so let me
know...
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
<sreedhardasi@.gmail.com> wrote in message
news:1158682323.098370.14170@.k70g2000cwa.googlegroups.com...
> Hi,
> I am developing an ASP.NET 2.0 application and SQL Server 2000 as
> back-end. The connection to DB works fine if I connect to my local or
> our dev databases. It is giving me the error when I try to connect to
> Test database. However, I am able to connect to the same database
> through Enterprise Manager by using same credentials. I tried using
> TCP/IP and named pipe connections.
> I am using the following connection string.
> Password=password;Persist Security Info=True;User ID=User;Initial
> Catalog=db;Data Source=server\instance;Network Library=DBMSSOCN;"
> I am getting the following errors.
> When using TCP/IP connection:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (provider: TCP Provider, error: 0 - No connection could be
> made because the target machine actively refused it.)
> When using Named Pipes:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (provider: Named Pipes Provider, error: 40 - Could not
> open a connection to SQL Server)
>
> Any help is appreciated.
> Thanks,
> Sreedhar
>|||No, only SQL Server 2000 is running on the server, but it has several
instances. I am using one of them.
I verified my connection string and it is similar to the string that
you provided, except that it has SQL server instance for Data Source.
Let me know if I can provide any additional information.
Thanks,
Sreedhar
Warren Brunk wrote:[vbcol=seagreen]
> Is this a 2000 instance with a SQL server 2005 instance running as well?
> A SQL 2000 with .net connection should look like...
> Standard Security:
> "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User
> Id=sa;Password=asdasd;"
> a.. Trusted Connection:
> "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated
> Security=SSPI;"
> You might have some issues if you are running different instances so let m
e
> know...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1158682323.098370.14170@.k70g2000cwa.googlegroups.com...|||Hopefully, it doesn't look like this: "Data Source=server\instance" because
that wouldn't work.
I noticed that was the connection string you originally submitted.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<sreedhardasi@.gmail.com> wrote in message
news:1158695873.697272.313370@.i42g2000cwa.googlegroups.com...
> No, only SQL Server 2000 is running on the server, but it has several
> instances. I am using one of them.
> I verified my connection string and it is similar to the string that
> you provided, except that it has SQL server instance for Data Source.
> Let me know if I can provide any additional information.
> Thanks,
> Sreedhar
> Warren Brunk wrote:
>|||Thanks for your reply. Then, what would be the correct format?
Arnie Rowland wrote:[vbcol=seagreen]
> Hopefully, it doesn't look like this: "Data Source=server\instance" becaus
e
> that wouldn't work.
> I noticed that was the connection string you originally submitted.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1158695873.697272.313370@.i42g2000cwa.googlegroups.com...|||Replace the word 'server' with the actual name of your server, and the word
'instance' with the actual instance name of your SQL Server instance. If you
don't have a 'named' instance, then leave the slash and instance name off.
For example: "Data Source=MyComputer", or "Data Source=MyComputer\MSDE"

Each 'instance' is a separate SQL Server.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<sreedhardasi@.gmail.com> wrote in message
news:1158698297.475884.293360@.k70g2000cwa.googlegroups.com...[vbcol=seagreen]
> Thanks for your reply. Then, what would be the correct format?
> Arnie Rowland wrote:
>

ASP.NET 2.0 and SQL Server 2000 connection issue

Hi,
I am developing an ASP.NET 2.0 application and SQL Server 2000 as
back-end. The connection to DB works fine if I connect to my local or
our dev databases. It is giving me the error when I try to connect to
Test database. However, I am able to connect to the same database
through Enterprise Manager by using same credentials. I tried using
TCP/IP and named pipe connections.
I am using the following connection string.
Password=password;Persist Security Info=True;User ID=User;Initial
Catalog=db;Data Source=server\instance;Network Library=DBMSSOCN;"
I am getting the following errors.
When using TCP/IP connection:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: TCP Provider, error: 0 - No connection could be
made because the target machine actively refused it.)
When using Named Pipes:
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not
open a connection to SQL Server)
Any help is appreciated.
Thanks,
Sreedhar
Is this a 2000 instance with a SQL server 2005 instance running as well?
A SQL 2000 with .net connection should look like...
Standard Security:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User
Id=sa;Password=asdasd;"
a.. Trusted Connection:
"Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated
Security=SSPI;"
You might have some issues if you are running different instances so let me
know...
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
<sreedhardasi@.gmail.com> wrote in message
news:1158682323.098370.14170@.k70g2000cwa.googlegro ups.com...
> Hi,
> I am developing an ASP.NET 2.0 application and SQL Server 2000 as
> back-end. The connection to DB works fine if I connect to my local or
> our dev databases. It is giving me the error when I try to connect to
> Test database. However, I am able to connect to the same database
> through Enterprise Manager by using same credentials. I tried using
> TCP/IP and named pipe connections.
> I am using the following connection string.
> Password=password;Persist Security Info=True;User ID=User;Initial
> Catalog=db;Data Source=server\instance;Network Library=DBMSSOCN;"
> I am getting the following errors.
> When using TCP/IP connection:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (provider: TCP Provider, error: 0 - No connection could be
> made because the target machine actively refused it.)
> When using Named Pipes:
> An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the
> fact that under the default settings SQL Server does not allow remote
> connections. (provider: Named Pipes Provider, error: 40 - Could not
> open a connection to SQL Server)
>
> Any help is appreciated.
> Thanks,
> Sreedhar
>
|||No, only SQL Server 2000 is running on the server, but it has several
instances. I am using one of them.
I verified my connection string and it is similar to the string that
you provided, except that it has SQL server instance for Data Source.
Let me know if I can provide any additional information.
Thanks,
Sreedhar
Warren Brunk wrote:[vbcol=seagreen]
> Is this a 2000 instance with a SQL server 2005 instance running as well?
> A SQL 2000 with .net connection should look like...
> Standard Security:
> "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User
> Id=sa;Password=asdasd;"
> a.. Trusted Connection:
> "Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;Integrated
> Security=SSPI;"
> You might have some issues if you are running different instances so let me
> know...
> thanks,
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> */
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1158682323.098370.14170@.k70g2000cwa.googlegro ups.com...
|||Hopefully, it doesn't look like this: "Data Source=server\instance" because
that wouldn't work.
I noticed that was the connection string you originally submitted.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<sreedhardasi@.gmail.com> wrote in message
news:1158695873.697272.313370@.i42g2000cwa.googlegr oups.com...
> No, only SQL Server 2000 is running on the server, but it has several
> instances. I am using one of them.
> I verified my connection string and it is similar to the string that
> you provided, except that it has SQL server instance for Data Source.
> Let me know if I can provide any additional information.
> Thanks,
> Sreedhar
> Warren Brunk wrote:
>
|||Thanks for your reply. Then, what would be the correct format?
Arnie Rowland wrote:[vbcol=seagreen]
> Hopefully, it doesn't look like this: "Data Source=server\instance" because
> that wouldn't work.
> I noticed that was the connection string you originally submitted.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1158695873.697272.313370@.i42g2000cwa.googlegr oups.com...
|||Replace the word 'server' with the actual name of your server, and the word
'instance' with the actual instance name of your SQL Server instance. If you
don't have a 'named' instance, then leave the slash and instance name off.
For example: "Data Source=MyComputer", or "Data Source=MyComputer\MSDE"
[vbcol=seagreen]
Each 'instance' is a separate SQL Server.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<sreedhardasi@.gmail.com> wrote in message
news:1158698297.475884.293360@.k70g2000cwa.googlegr oups.com...
> Thanks for your reply. Then, what would be the correct format?
> Arnie Rowland wrote:
>