About accessing data from sqlserver

Hello sir,
I have installed .net1.1version on windows2003 operating system.I have also installed sqlserver2000 on the same system.
My problem is that when i am trying to get data from sqlserver from asp.net program i am getting error as
'access denied to user NT AUTHORITY/NETWORK USER'.
I request you to kindly suggest me with an appropriate solution.
Thanking you.
Please email meon:-aanandkumar786@.yahoo.comeither the account that asp.net uses to run pages needs to be given access to the database, or you need to create your sql connection string with a specific sql server account to allow for the authentication.

Abnormal Timeout Issue on Production System

Hi all,
My production system is experiencing abnormal timeouts during posting of
transaction. The abnormal part is:
System usually works fine for about 6 ws; but one unlucky day, suddenly
for no apparent reason, system experiences massive timeout for about 2 - 3
hours; after that everything is fine again.
A background on the production system:
? It is a sales and stock control system. With a fair number (nothing
massive) of sales transactions every operation days
? Approx 50 client users
? Developed using VB .Net, VS 2003
? Running on .Net Framework 2.0
? Running on SQL Server database
? Supports multi-user, single database
It is also running on a pretty high-end server (from what I was told) with
spec:
? HP ML 530 G2 machine
? 2-processors, each 3 GHz, running Xeon HT
? 4 GB ECC RAM
? HDD running RAID 0 and RAID 1; Total disk space 200 GB, with 72 GB free
? SQL Server 2003 standard edition with sp3
? Windows 2003 Server standard edition
? Running other s/w, mainly Norton Anti-Virus 9.0 corp. version and Verita
s
9.1
USUALLY DURING THE FINE DAYS…
System works fine during both peak and off-peak hours with very rare
timeouts. Those timeouts are caused mainly by updating of "constant" tables,
such as stock when two customers are buying the same item at the same time.
That is acceptable as data integrity on those kinds of tables must be
maintained.
Database maintenance plan is in-placed to reorganized data and index pages,
scheduled to run once a w, during non-operation hours.
HDD defragmentation is scheduled to run twice a w, also during
non-operation hours.
The codes for the frequently used sales modules have been optimized to
ensure that transaction control is well handled, and unnecessary queries
removed.
HOWEVER WHEN THE UNLUCKY DAY OCCURRED…
Timeout occurs when user posts a sales transaction during the peak hours.
But the volume of transaction is similar to other peak hours during other
fine days!
I tried restarting the server and the client machines and applications but
the posting still timeout.
I even tried having only one user posting a transaction but that also timeou
t!
I checked the Profiler and it reveals that SQL Server is “stuck” while
attempting to insert some rows into a few tables at the start of the
transaction. These tables have small number of rows, from 10K to 200K each.
I
repeated the same test for only one user, and SQL Server remains “stuck”
as
well. BUT during fine days, SQL Server never has problem with those tables.
It can even insert data quickly into big tables with 2M+ rows!
I also checked the SQL EM Current Activities, and can find no tables being
blocked when only one user is posting. Tables are only blocked when more tha
n
one user is posting, and that is understandable coz SQL Server is “stuck
on
the 1st user’s transaction, thus blocking everyone else.
The SQL Server just become spastic, obsessed with the 1st (or only) user
transaction, never wanting to finish it off that I know it can.
And the weird thing is after 2-3 hours, everything goes back to normal, with
SQL Server at its best again.
PUZZLED…
The server specs is top-end; The program should be well optimized as far as
the frequently used sales modules are concerned; Maintenance Plans and
defragmentation scheduled tasks are in place; Heavy jobs such as Veritas
backup are only done during non-operation hours; It works fine for about
six-w, then it gets “stuck” on a few small tables, only to “unstuck
” after
2-3 hours break; The weirdest of all, it even timeouts when only one user
(having all the server and database resources to him /herself) is posting a
sales transaction!
Can anyone please kindly advice what are the possible causes and resolutions
for this anomalies be? Can the hardware spec affect it, even though it is
supposed to be high-end? Could other applications such as Veritas co-exist
with the SQL Server? Is SQL Server running any system jobs, e.g. Ghost
Cleanup, that can slow down its performance?
TQ in advance.Hi
The fact that it is stuck sounds like blocking issues.
If the application does not finish it's transaction on a specific set of
row(s) in a table, and another one comes in to work on the same row(s), the
2nd one has to wait.
When this happens, run sp_who2 and see what is blocking what.
Look up "blocks, avoiding" in books online.
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/
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:B1960834-C93C-458E-BC88-792045D88F2C@.microsoft.com...
> Hi all,
> My production system is experiencing abnormal timeouts during posting of
> transaction. The abnormal part is:
> System usually works fine for about 6 ws; but one unlucky day, suddenly
> for no apparent reason, system experiences massive timeout for about 2 - 3
> hours; after that everything is fine again.
>
> A background on the production system:
> . It is a sales and stock control system. With a fair number (nothing
> massive) of sales transactions every operation days
> . Approx 50 client users
> . Developed using VB .Net, VS 2003
> . Running on .Net Framework 2.0
> . Running on SQL Server database
> . Supports multi-user, single database
> It is also running on a pretty high-end server (from what I was told) with
> spec:
> . HP ML 530 G2 machine
> . 2-processors, each 3 GHz, running Xeon HT
> . 4 GB ECC RAM
> . HDD running RAID 0 and RAID 1; Total disk space 200 GB, with 72 GB free
> . SQL Server 2003 standard edition with sp3
> . Windows 2003 Server standard edition
> . Running other s/w, mainly Norton Anti-Virus 9.0 corp. version and
> Veritas
> 9.1
>
> USUALLY DURING THE FINE DAYS.
> System works fine during both peak and off-peak hours with very rare
> timeouts. Those timeouts are caused mainly by updating of "constant"
> tables,
> such as stock when two customers are buying the same item at the same
> time.
> That is acceptable as data integrity on those kinds of tables must be
> maintained.
> Database maintenance plan is in-placed to reorganized data and index
> pages,
> scheduled to run once a w, during non-operation hours.
> HDD defragmentation is scheduled to run twice a w, also during
> non-operation hours.
> The codes for the frequently used sales modules have been optimized to
> ensure that transaction control is well handled, and unnecessary queries
> removed.
>
> HOWEVER WHEN THE UNLUCKY DAY OCCURRED.
> Timeout occurs when user posts a sales transaction during the peak hours.
> But the volume of transaction is similar to other peak hours during other
> fine days!
> I tried restarting the server and the client machines and applications but
> the posting still timeout.
> I even tried having only one user posting a transaction but that also
> timeout!
> I checked the Profiler and it reveals that SQL Server is "stuck" while
> attempting to insert some rows into a few tables at the start of the
> transaction. These tables have small number of rows, from 10K to 200K
> each. I
> repeated the same test for only one user, and SQL Server remains "stuck"
> as
> well. BUT during fine days, SQL Server never has problem with those
> tables.
> It can even insert data quickly into big tables with 2M+ rows!
> I also checked the SQL EM Current Activities, and can find no tables being
> blocked when only one user is posting. Tables are only blocked when more
> than
> one user is posting, and that is understandable coz SQL Server is "stuck"
> on
> the 1st user's transaction, thus blocking everyone else.
> The SQL Server just become spastic, obsessed with the 1st (or only) user
> transaction, never wanting to finish it off that I know it can.
> And the weird thing is after 2-3 hours, everything goes back to normal,
> with
> SQL Server at its best again.
>
> PUZZLED.
> The server specs is top-end; The program should be well optimized as far
> as
> the frequently used sales modules are concerned; Maintenance Plans and
> defragmentation scheduled tasks are in place; Heavy jobs such as Veritas
> backup are only done during non-operation hours; It works fine for about
> six-w, then it gets "stuck" on a few small tables, only to "unstuck"
> after
> 2-3 hours break; The weirdest of all, it even timeouts when only one user
> (having all the server and database resources to him /herself) is posting
> a
> sales transaction!
> Can anyone please kindly advice what are the possible causes and
> resolutions
> for this anomalies be? Can the hardware spec affect it, even though it is
> supposed to be high-end? Could other applications such as Veritas co-exist
> with the SQL Server? Is SQL Server running any system jobs, e.g. Ghost
> Cleanup, that can slow down its performance?
> TQ in advance.|||Hi,
TQ for reply.
I don't think it is a blocking issue coz EM Current Activities shows no
blocks, no locks and whatsoever, and the codes have been optimized to handle
transaction properly. And as mentioned, weird things like why everything
works fine for about six ws, but fails for 3 hours; and even
one-and-only-one user gets timeout during posting. The system just degraded
for no apparent reason.
TQ for the sp_who2 thou. Will try it out (fingers crossed) in abt six ws
time! ;)
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> The fact that it is stuck sounds like blocking issues.
> If the application does not finish it's transaction on a specific set of
> row(s) in a table, and another one comes in to work on the same row(s), th
e
> 2nd one has to wait.
> When this happens, run sp_who2 and see what is blocking what.
> Look up "blocks, avoiding" in books online.
> 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/
> "HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
> news:B1960834-C93C-458E-BC88-792045D88F2C@.microsoft.com...
>
>

Ability to create and schedule jobs on shared system

I have a shared SQL Server, used by applications that donâ't need an
instance, just a DB. Users with DBO rights to their DB, no system roles, can
still create and schedule jobs under sql agent. I donâ't want to allow this,
how can I prevent it?Hello Jason,
You could use sqlagent proxy account to run the jobs if they are not in
sysadmin server role. If you want to prevent it, you could use the
following method:
1. Start SQL Server Enterprise Manager.
2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
expand SQL Server Group.
3. Expand the instance of SQL Server that you want to set up the proxy
account for.
4. Expand Management, right-click SQL Server Agent, and then click
Properties.
5. In the SQL Server Agent Properties dialog box, click the Job System
tab.
6. Under Non-SysAdmin job step proxy account, click to check the Only
users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
steps check box.
If you have any further questions on the issue, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
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/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.|||i looked at this setting, it currently is checked, however there is a user w/
only dbo rights to their db that can create, schedule jobs and run jobs.
i checked the roles, he has none.
this is a windows account, it if has some high level, domain admin type
right in AD, could AD be working behind the scenes to enable the features in
SQL Server for this user?
""Peter YangMSFT]"" wrote:
> Hello Jason,
> You could use sqlagent proxy account to run the jobs if they are not in
> sysadmin server role. If you want to prevent it, you could use the
> following method:
> 1. Start SQL Server Enterprise Manager.
> 2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
> expand SQL Server Group.
> 3. Expand the instance of SQL Server that you want to set up the proxy
> account for.
> 4. Expand Management, right-click SQL Server Agent, and then click
> Properties.
> 5. In the SQL Server Agent Properties dialog box, click the Job System
> tab.
> 6. Under Non-SysAdmin job step proxy account, click to check the Only
> users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
> steps check box.
> If you have any further questions on the issue, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> 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/subscriptions/support/default.aspx>.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hello Jason,
Yes. If it's domain admin, it's by default in the group local admin groups.
Local admin group is added in SQL server with sysadmin rights by default.
You may want to check the logins on the server to check this.
You may want to temporarily remove the login to test if the issue still
occurs. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
=====================================================Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607|||i have found another user who is NOT a domain admin, OR server admin. he is
DBO on his db and is able to create/run a job that runs a select from the
database.
i guess becuase these to meet the criteria of that checkbox.
that checkbox you mentioned, what actually is considered a cmdexec step?
assuming that is the case, is there any other way to block users from
creating jobs?
""Peter YangMSFT]"" wrote:
> Hello Jason,
> Yes. If it's domain admin, it's by default in the group local admin groups.
> Local admin group is added in SQL server with sysadmin rights by default.
> You may want to check the logins on the server to check this.
> You may want to temporarily remove the login to test if the issue still
> occurs. Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> 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.
> =====================================================> Please note that the newsgroups are staffed weekdays with a goal to provide
> ONE BUSINESS DAY RESPONSE to all posts.
> If this response time does not meet your needs, please contact CSS for more
> immediate assistance:
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
>|||Hello Jason,
I'd like to confirm if check the "Only users with SysAdmin priviledges can
execute CmdExec and ActiveScripting job
steps" check box in the sqlagent property dialog. If so, it's supposed that
a domain user without sysadmin rights shall be able to create job but the
job cannot run properly because there is no proxy agent account to run the
job.
If you have proxy account configured, you are not able to block only one
user/login using the proxy account because it's used as proxy for all users
without sysadmin rights.
I think cmdexec step in a job is actually run as the proxy account.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support|||that check box is checked.
upon further testing, this is what I see.
only some commands are blocked as indicated by the descriptive text.
e.g. a user can create and run a job that does a simple select. but user
cannot run a job that kicks off a DTS package as it requires a cmdexec cmd,
he can create it though. I was wanting to block the running off all jobs to
force users to schedule them through a DBA.
""Peter YangMSFT]"" wrote:
> Hello Jason,
> I'd like to confirm if check the "Only users with SysAdmin priviledges can
> execute CmdExec and ActiveScripting job
> steps" check box in the sqlagent property dialog. If so, it's supposed that
> a domain user without sysadmin rights shall be able to create job but the
> job cannot run properly because there is no proxy agent account to run the
> job.
> If you have proxy account configured, you are not able to block only one
> user/login using the proxy account because it's used as proxy for all users
> without sysadmin rights.
> I think cmdexec step in a job is actually run as the proxy account.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>|||Hello Jason,
Sorry for my fault and I spoke too quick based on my memory. In SQLServer
2000, the following table describes the user context under which a
scheduled SQLServer Agent job is run depending on whether the owner of the
job is sysadmin in SQLServer or not.
Owner of job Job Type Job executed as (user)
=========== ========= =============
non-sysadmin TSQL [owner of Job]
non-sysadmin CmdExec/Activex [SQL Agent proxy account]
sysadmin TSQL [owner of Job]
sysadmin CmdExec/Activex [SQLServer startup account]
Also, scheduled DTS packages fall under CmdExec job type.
You are correct that the checkbox only blocks CmdExec and ActiveScripting
job of non-sysadmin users. They could still schedule and run jobs that
don't use CmdExec and ActiveScripting job etc.
Since the SQLagent will "impersonate" the user only when the user runs the
job step, actually there is no option to "deny" the user to access the
database objects that he has permissions in the first place.
However, you could deny the user to create jobs by the following method:
1. Add the user as a database user of msdb database.
2. Run the following statement to deny its permission to
use msdb
deny execute on msdb.dbo.sp_add_job to username
If you have any further questions or concerns, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.|||Hello Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.

Ability to create and schedule jobs on shared system

I have a shared SQL Server, used by applications that don’t need an
instance, just a DB. Users with DBO rights to their DB, no system roles, can
still create and schedule jobs under sql agent. I don’t want to allow this,
how can I prevent it?
Hello Jason,
You could use sqlagent proxy account to run the jobs if they are not in
sysadmin server role. If you want to prevent it, you could use the
following method:
1. Start SQL Server Enterprise Manager.
2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
expand SQL Server Group.
3. Expand the instance of SQL Server that you want to set up the proxy
account for.
4. Expand Management, right-click SQL Server Agent, and then click
Properties.
5. In the SQL Server Agent Properties dialog box, click the Job System
tab.
6. Under Non-SysAdmin job step proxy account, click to check the Only
users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
steps check box.
If you have any further questions on the issue, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
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/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||i looked at this setting, it currently is checked, however there is a user w/
only dbo rights to their db that can create, schedule jobs and run jobs.
i checked the roles, he has none.
this is a windows account, it if has some high level, domain admin type
right in AD, could AD be working behind the scenes to enable the features in
SQL Server for this user?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> You could use sqlagent proxy account to run the jobs if they are not in
> sysadmin server role. If you want to prevent it, you could use the
> following method:
> 1. Start SQL Server Enterprise Manager.
> 2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
> expand SQL Server Group.
> 3. Expand the instance of SQL Server that you want to set up the proxy
> account for.
> 4. Expand Management, right-click SQL Server Agent, and then click
> Properties.
> 5. In the SQL Server Agent Properties dialog box, click the Job System
> tab.
> 6. Under Non-SysAdmin job step proxy account, click to check the Only
> users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
> steps check box.
> If you have any further questions on the issue, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications
> <http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
> 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/subscriptions/support/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
|||Hello Jason,
Yes. If it's domain admin, it's by default in the group local admin groups.
Local admin group is added in SQL server with sysadmin rights by default.
You may want to check the logins on the server to check this.
You may want to temporarily remove the login to test if the issue still
occurs. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
================================================== ===
Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
|||i have found another user who is NOT a domain admin, OR server admin. he is
DBO on his db and is able to create/run a job that runs a select from the
database.
i guess becuase these to meet the criteria of that checkbox.
that checkbox you mentioned, what actually is considered a cmdexec step?
assuming that is the case, is there any other way to block users from
creating jobs?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> Yes. If it's domain admin, it's by default in the group local admin groups.
> Local admin group is added in SQL server with sysadmin rights by default.
> You may want to check the logins on the server to check this.
> You may want to temporarily remove the login to test if the issue still
> occurs. Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> 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.
> ================================================== ===
> Please note that the newsgroups are staffed weekdays with a goal to provide
> ONE BUSINESS DAY RESPONSE to all posts.
> If this response time does not meet your needs, please contact CSS for more
> immediate assistance:
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607
>
|||Hello Jason,
I'd like to confirm if check the "Only users with SysAdmin priviledges can
execute CmdExec and ActiveScripting job
steps" check box in the sqlagent property dialog. If so, it's supposed that
a domain user without sysadmin rights shall be able to create job but the
job cannot run properly because there is no proxy agent account to run the
job.
If you have proxy account configured, you are not able to block only one
user/login using the proxy account because it's used as proxy for all users
without sysadmin rights.
I think cmdexec step in a job is actually run as the proxy account.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
|||that check box is checked.
upon further testing, this is what I see.
only some commands are blocked as indicated by the descriptive text.
e.g. a user can create and run a job that does a simple select. but user
cannot run a job that kicks off a DTS package as it requires a cmdexec cmd,
he can create it though. I was wanting to block the running off all jobs to
force users to schedule them through a DBA.
""Peter YangMSFT]"" wrote:

> Hello Jason,
> I'd like to confirm if check the "Only users with SysAdmin priviledges can
> execute CmdExec and ActiveScripting job
> steps" check box in the sqlagent property dialog. If so, it's supposed that
> a domain user without sysadmin rights shall be able to create job but the
> job cannot run properly because there is no proxy agent account to run the
> job.
> If you have proxy account configured, you are not able to block only one
> user/login using the proxy account because it's used as proxy for all users
> without sysadmin rights.
> I think cmdexec step in a job is actually run as the proxy account.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>

Ability to create and schedule jobs on shared system

I have a shared SQL Server, used by applications that don’t need an
instance, just a DB. Users with DBO rights to their DB, no system roles, ca
n
still create and schedule jobs under sql agent. I don’t want to allow thi
s,
how can I prevent it?Hello Jason,
You could use sqlagent proxy account to run the jobs if they are not in
sysadmin server role. If you want to prevent it, you could use the
following method:
1. Start SQL Server Enterprise Manager.
2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and then
expand SQL Server Group.
3. Expand the instance of SQL Server that you want to set up the proxy
account for.
4. Expand Management, right-click SQL Server Agent, and then click
Properties.
5. In the SQL Server Agent Properties dialog box, click the Job System
tab.
6. Under Non-SysAdmin job step proxy account, click to check the Only
users with SysAdmin priviledges can execute CmdExec and ActiveScripting job
steps check box.
If you have any further questions on the issue, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
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...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Jason,
I'm still interested in this issue. If you have any comments or questions,
please feel free to let's know. We look forward to hearing from you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.|||i looked at this setting, it currently is checked, however there is a user w
/
only dbo rights to their db that can create, schedule jobs and run jobs.
i checked the roles, he has none.
this is a windows account, it if has some high level, domain admin type
right in AD, could AD be working behind the scenes to enable the features in
SQL Server for this user?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> You could use sqlagent proxy account to run the jobs if they are not in
> sysadmin server role. If you want to prevent it, you could use the
> following method:
> 1. Start SQL Server Enterprise Manager.
> 2. In SQL Server Enterprise Manager, expand Microsoft SQL Server, and the
n
> expand SQL Server Group.
> 3. Expand the instance of SQL Server that you want to set up the proxy
> account for.
> 4. Expand Management, right-click SQL Server Agent, and then click
> Properties.
> 5. In the SQL Server Agent Properties dialog box, click the Job System
> tab.
> 6. Under Non-SysAdmin job step proxy account, click to check the Only
> users with SysAdmin priviledges can execute CmdExec and ActiveScripting jo
b
> steps check box.
> If you have any further questions on the issue, please feel free to let's
> know. Thank you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> 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...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello Jason,
Yes. If it's domain admin, it's by default in the group local admin groups.
Local admin group is added in SQL server with sysadmin rights by default.
You may want to check the logins on the server to check this.
You may want to temporarily remove the login to test if the issue still
occurs. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.
========================================
=============
Please note that the newsgroups are staffed weekdays with a goal to provide
ONE BUSINESS DAY RESPONSE to all posts.
If this response time does not meet your needs, please contact CSS for more
immediate assistance:
http://support.microsoft.com/defaul...ProPhone#faq607|||i have found another user who is NOT a domain admin, OR server admin. he is
DBO on his db and is able to create/run a job that runs a select from the
database.
i guess becuase these to meet the criteria of that checkbox.
that checkbox you mentioned, what actually is considered a cmdexec step?
assuming that is the case, is there any other way to block users from
creating jobs?
""Peter YangMSFT]"" wrote:

> Hello Jason,
> Yes. If it's domain admin, it's by default in the group local admin groups
.
> Local admin group is added in SQL server with sysadmin rights by default.
> You may want to check the logins on the server to check this.
> You may want to temporarily remove the login to test if the issue still
> occurs. Thanks.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> 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.
> ========================================
=============
> Please note that the newsgroups are staffed weekdays with a goal to provid
e
> ONE BUSINESS DAY RESPONSE to all posts.
> If this response time does not meet your needs, please contact CSS for mor
e
> immediate assistance:
> [url]http://support.microsoft.com/default.aspx?scid=fh;EN-US;OfferProPhone#faq607[/ur
l]
>|||Hello Jason,
I'd like to confirm if check the "Only users with SysAdmin priviledges can
execute CmdExec and ActiveScripting job
steps" check box in the sqlagent property dialog. If so, it's supposed that
a domain user without sysadmin rights shall be able to create job but the
job cannot run properly because there is no proxy agent account to run the
job.
If you have proxy account configured, you are not able to block only one
user/login using the proxy account because it's used as proxy for all users
without sysadmin rights.
I think cmdexec step in a job is actually run as the proxy account.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support|||that check box is checked.
upon further testing, this is what I see.
only some commands are blocked as indicated by the descriptive text.
e.g. a user can create and run a job that does a simple select. but user
cannot run a job that kicks off a DTS package as it requires a cmdexec cmd,
he can create it though. I was wanting to block the running off all jobs to
force users to schedule them through a DBA.
""Peter YangMSFT]"" wrote:

> Hello Jason,
> I'd like to confirm if check the "Only users with SysAdmin priviledges ca
n
> execute CmdExec and ActiveScripting job
> steps" check box in the sqlagent property dialog. If so, it's supposed tha
t
> a domain user without sysadmin rights shall be able to create job but the
> job cannot run properly because there is no proxy agent account to run the
> job.
> If you have proxy account configured, you are not able to block only one
> user/login using the proxy account because it's used as proxy for all user
s
> without sysadmin rights.
> I think cmdexec step in a job is actually run as the proxy account.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>|||Hello Jason,
Sorry for my fault and I spoke too quick based on my memory. In SQLServer
2000, the following table describes the user context under which a
scheduled SQLServer Agent job is run depending on whether the owner of the
job is sysadmin in SQLServer or not.
Owner of job Job Type Job executed as (user)
=========== ========= =============
non-sysadmin TSQL [owner of Job]
non-sysadmin CmdExec/Activex [SQL Agent proxy account]
sysadmin TSQL [owner of Job]
sysadmin CmdExec/Activex [SQLServer startup account]
Also, scheduled DTS packages fall under CmdExec job type.
You are correct that the checkbox only blocks CmdExec and ActiveScripting
job of non-sysadmin users. They could still schedule and run jobs that
don't use CmdExec and ActiveScripting job etc.
Since the SQLagent will "impersonate" the user only when the user runs the
job step, actually there is no option to "deny" the user to access the
database objects that he has permissions in the first place.
However, you could deny the user to create jobs by the following method:
1. Add the user as a database user of msdb database.
2. Run the following statement to deny its permission to
use msdb
deny execute on msdb.dbo.sp_add_job to username
If you have any further questions or concerns, please feel free to let's
know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
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.

A table owner to use xp_sendmail

how can i open the rights if
a table owner (not system administrator) to use xp_sendmail
Thanks a lot.
tonyCreate a user for that login in the master database and grant execute rights for that user in the
master database to xp_sendmail.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony WONG" <x34@.hknet.com> wrote in message news:OzaaNJ4hGHA.1508@.TK2MSFTNGP04.phx.gbl...
> how can i open the rights if
> a table owner (not system administrator) to use xp_sendmail
> Thanks a lot.
> tony
>

A table owner to use xp_sendmail

how can i open the rights if
a table owner (not system administrator) to use xp_sendmail
Thanks a lot.
tonyCreate a user for that login in the master database and grant execute rights
for that user in the
master database to xp_sendmail.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony WONG" <x34@.hknet.com> wrote in message news:OzaaNJ4hGHA.1508@.TK2MSFTNGP04.phx.gbl...[v
bcol=seagreen]
> how can i open the rights if
> a table owner (not system administrator) to use xp_sendmail
> Thanks a lot.
> tony
>[/vbcol]

A simple Update query using a date - conversion from msaccess

I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query

update
timecard
set
TcdDate = #3/18/05#

TcdDate is defined as a date/time type

It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?Converting a whole database into SQL Svr?? Why dont u try ur hand at writing a DTS package and running it. It does everything for u automatically though u need to spend some time writing it. It is absolutely reusable.|||Your sql stmt should be :

UPDATE
timecard
SET
TcdDate= '3/18/05'

In sql server the datetime datatype is treated as string hence you need to wrap it in quotes.|||Argh! I thought I had tried that already. But, in retrospect, I had coded TcdDate = 3/18/05, and thus my problem lay with a couple of single quotes.

Sometimes it takes a fresh set of eyes to look at a problem.

Thank you for your help.|||FWIW it would be safer to stick to ISO date format (YYYYMMDD). This will insure that the month and date portions are recognized correctly regardless of the language settings on the SQL Server:

UPDATE
 timecard
SET
 TcdDate = '20050318'

Terri

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all properties
that are available for a given period.
What I want to be able to say is show me all homes that are available between
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon
> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);
|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
[vbcol=seagreen]
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)
|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.
|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP
|||Hi everyone
[vbcol=seagreen]
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes things
faster but I don't really care. I just do it for clarity and to avoid conflicts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
[vbcol=seagreen]
Declarative Referential Integrity, data types, etc. in your schema are. Sample
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible columns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidified
it in my mind.
Thanks again
Simon

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following
.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all proper
ties
that are available for a given period.
What I want to be able to say is show me all homes that are available betwee
n
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP|||Hi everyone
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking
,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes thing
s
faster but I don't really care. I just do it for clarity and to avoid confli
cts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
Declarative Referential Integrity, data types, etc. in your schema are. Samp
le
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible colum
ns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidifi
ed
it in my mind.
Thanks again
Simon

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following
.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all proper
ties
that are available for a given period.
What I want to be able to say is show me all homes that are available betwee
n
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.

> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.

> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
[vbcol=seagreen]
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
Hugo Kornelis, SQL Server MVP|||Hi everyone
[vbcol=seagreen]
context.
Actually, the primary key in my tables is always ID, that way you never have
to think about it. I used to do it the other way, but then I got to thinking
,
why call it BookingID? It's obviously a BookingID by virtue of the fact that
its the ID of the Booking table. I always fully qualify column names anyway
so I use Bookings.ID. I've heard some people say that doing this makes thing
s
faster but I don't really care. I just do it for clarity and to avoid confli
cts.
Of course if I was dealing with a foregin key in a table then I'd call it
BookingID because that would be implicitly helpful and quite necessary.
[vbcol=seagreen]
Declarative Referential Integrity, data types, etc. in your schema are. Samp
le
data is also a good idea, along with clear specifications. It is very hard
to debug code when you do not let us see it."
Well that would be difficult considering I don't have any DDL to offer. I
was asking a general queston on how to achieve an end goal. I suggested a
bookings table which was remarkably simple and existed only in my head. I
thought it was simple enough to be clear to those who saw the possible colum
ns.
Thank you to everyone who has given some suggestions. I think I was pretty
much getting to the same conclusion as Aarons suggestion, but thats solidifi
ed
it in my mind.
Thanks again
Simon

A question on database design

Hi everyone,
I'm hoping someone could give me some advice on how to achieve the following.
I'm making a system that stores information on holiday homes. In particular
I need to store information on their bookings. When is a home occupied and
for how long.
I've considered making a Bookings table which would be somthing like:
ID
HomeID
StartDate
EndDate
What I can't see yet is how I'm going to query this table to find all properties
that are available for a given period.
What I want to be able to say is show me all homes that are available between
the 12th of april and the 27th of April.
Can anyone suggest how that query would work? Or is there perhaps a better
way to store this booking information?
Any advice and suggestions would be gratefully received.
Kindest Regards
Simon> I've considered making a Bookings table which would be somthing like:
> ID
How about BookingID? ID is meaningless and can easily be taken out of
context.
> What I can't see yet is how I'm going to query this table to find all
> properties that are available for a given period.
> What I want to be able to say is show me all homes that are available
> between the 12th of april and the 27th of April.
-- untested, due to lack of DDL, sample data, desired results;
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND
(
b.StartDate BETWEEN @.startDate AND @.endDate
OR
b.EndDate BETWEEN @.startDate AND @.endDate
)
);|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Don't you need a few more things in Bookings?
CREATE TABLE Bookings
(customer_id INTEGER NOT NULL
REFERENCES Customers(customer_id),
property_id INTEGER NOT NULL
REFERENCES Properties (property_id),
start_date DATETIME NOT NULL,
end_date DATETIME,
CHECK (start_date < end_date),
rental_rate DECIMAL (8,2) NOT NULL
CHECK (rental_rate > 0.00),
PRIMARY KEY (customer_id, property_id, start_date));
>> What I want to be able to say is show me all homes that are available between
the 12th of April and the 27th of April. <<
I would build a Calendar table which is discussed in a lot of old
postings.
SELECT @.my_arrival, @.my_depart, P.property_id
FROM Properties AS P
WHERE NOT EXISTS
(SELECT *
FROM Bookings AS B,
Calendar AS C
WHERE C.cal_date
BETWEEN B.start_date AND B.end_date
AND B.property_id = P.property_id
AND cal_date BETWEEN @.my_arrival AND @.my_depart);
Untested.
(SELECT cal_date
FROM Calendar
WHERE cal_date BETWEEN @.my_arrival AND @.my_depart) AS X(cal_date)|||You should consider recording dates for which a given unit is actually
available. One way is to create "fake" bookings for non-availability
dates - but that can get ugly.|||On Tue, 17 Jan 2006 07:28:49 -0800, Simon Harvey wrote:
(snip)
>Can anyone suggest how that query would work? Or is there perhaps a better
>way to store this booking information?
Hi Simon,
Depending on your indexes, the query below might run faster than the
query Aaron suggests:
DECLARE @.startDate SMALLDATETIME, @.endDate SMALLDATETIME;
SELECT @.startDate = '20060412', @.endDate = '20060427';
SELECT HomeID, Description
FROM dbo.Homes h
WHERE NOT EXISTS
(
SELECT 1
FROM dbo.Bookings b
WHERE b.HomeID = h.HomeID
AND b.StartDate <= @.endDate
AND b.EndDate >= @.startDate
);
NOTE: I've set the follow-up to just the SQL Server groups. I can't help
with Access syntax anyway.
--
Hugo Kornelis, SQL Server MVP