Cluster server connection

Good day,
I have recently installed a 2 node cluster server with SQL 2000 SP3 in my
network. and have found out that users connect to the Virtual cluster server
inorder to receive SQL services but they get the replay for the physical
node that holds the SQL.
I was wondering if this configuration can be changed so that the SQL servers
answers back with the IP address of the virtual server and not the physical
cluster.
the problem is that im working with a Firewall and we would like to restrict
the number of IP address that we expose to the world.
Thanks in advance
Oren Zippori
This is one of the wierd but unavoidable artifacts of clustering. You will
have to expose the host computer's IP addresses through the firewall.
Personally, I like to run my database servers on an restricted segment, just
to minimize the impact of stuff like this.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Oren Zippori" <orenzp@.hotmail.com> wrote in message
news:eu17pHbwFHA.3548@.tk2msftngp13.phx.gbl...
> Good day,
> I have recently installed a 2 node cluster server with SQL 2000 SP3 in my
> network. and have found out that users connect to the Virtual cluster
> server inorder to receive SQL services but they get the replay for the
> physical node that holds the SQL.
> I was wondering if this configuration can be changed so that the SQL
> servers answers back with the IP address of the virtual server and not the
> physical cluster.
> the problem is that im working with a Firewall and we would like to
> restrict the number of IP address that we expose to the world.
> Thanks in advance
> Oren Zippori
>
|||Thanks for the information on this Geoff.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uNRVXAgwFHA.664@.tk2msftngp13.phx.gbl...
> This is one of the wierd but unavoidable artifacts of clustering. You
> will have to expose the host computer's IP addresses through the firewall.
> Personally, I like to run my database servers on an restricted segment,
> just to minimize the impact of stuff like this.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Oren Zippori" <orenzp@.hotmail.com> wrote in message
> news:eu17pHbwFHA.3548@.tk2msftngp13.phx.gbl...
>

CLR Integration Security

Hi,
In BOL it is mentioned that we can control the access of users to file
system resources by using impersonation (WindowsIdentity.Impersonate).
Imagine I have a SP that creates a file on disk but the user sends the path
as a parameter. Although I can control the user to access only to particular
path, but how can I control the developer herself? As a DBA, I need to
control the developer while they can write their codes without using
"WindowsIdentity.Impersonate". If they don't do impersonation, they can work
under the security account of SQL Server.
I want to give permissions based on the user accounts that they log into
their Windows.
Thanks in advance,
LeilaLeila wrote:
> Hi,
> In BOL it is mentioned that we can control the access of users to file
> system resources by using impersonation (WindowsIdentity.Impersonate).
> Imagine I have a SP that creates a file on disk but the user sends the pat
h
> as a parameter. Although I can control the user to access only to particul
ar
> path, but how can I control the developer herself? As a DBA, I need to
> control the developer while they can write their codes without using
> "WindowsIdentity.Impersonate".
I do not really understand the question. What I mean is that; if the
developer writes the code without Impersonate, then everyone runs
without impersonation - so IMHO, it is not a question about dev vs.
regular users. If you are worried about dev doing things they shouldn't
do - well make sure they only have necessary rights on dev machines and
not production boxes.

> If they don't do impersonation, they can work
> under the security account of SQL Server.
> I want to give permissions based on the user accounts that they log into
> their Windows.
Well, I would assume that the account SQL runs under has very low
priviliges, and if you want to give rights according to the log-ins -
require that the code has Impersonate. Also, remember that running under
the account of SQL is only applied when you execute out of SQL Server,
i.e. file I/O etc.
Niels

ClR Function errror cant get to lcoal file system

My users complain that they cant run a CLR function. I am told that it cant get access to the local file system. I do not how to code these so from SSIS is there any way to let the users gain access to this. If this is a permission issue what is the lease privilege that I can configure for this to work?

Thanks
AdminAnupHow are they connecting to SQL Server ? If you are using SQL Server authenitcation, they will use the SQL Server account permissions to access the files, if this one does not have any permissions, they will get a Access denied. The same situation if you are using Windows authentication and the individual users do not have access to the files. Did you try to run the function individually ? Which error do you get there if you try to let it run at a user context (and not probably one with administrative priviledges :-) )

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||it is using sql authentication.

Can you let me know which permission does sql authentication need to execute

and also if i change it to be windows does it need any admin privileges|||If you are using SQL Server Authentication, the SQL Server service account SQL Server is running with, will need to have rights on the accessed files / shares. If you are using Windows authentication, the logged in User wil need to have those rights.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||Has the CLR assembly been granted EXTERNAL_ACCESS?|||Yes that was it as it was in Safe mode
Thanks

Client-side printing from NT with SP2

We have installed SP2 and client-side printing from the browser is working
fine for our users on XP/IE6. However, we have some users with NT4/IE6 on
their machines. They are able to view reports, but when they click the Print
icon, nothing happens (no error, no prompt, nothing).
Is the ActiveX control for printing included in SP2 supported for clients
using NT4?Ah, crap...
Same here. That's a show stopper!!!
"MELMEL" <MELMEL@.discussions.microsoft.com> wrote in message
news:DDFF5917-13B1-44D2-8138-EB44818B579D@.microsoft.com...
> We have installed SP2 and client-side printing from the browser is working
> fine for our users on XP/IE6. However, we have some users with NT4/IE6 on
> their machines. They are able to view reports, but when they click the
Print
> icon, nothing happens (no error, no prompt, nothing).
> Is the ActiveX control for printing included in SP2 supported for clients
> using NT4?|||When a product is given end of lifetime that really does mean that. It might
work or it might not but MS spends 0 time testing with it. It is not part of
their test suite. Extended support expired almost a year ago. I assume the
users can continute to export to PDF and print that way.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Fredrick Bartlett" <rick_bartlett@.WeDoNET.net> wrote in message
news:Os2pNyPUFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Ah, crap...
> Same here. That's a show stopper!!!
> "MELMEL" <MELMEL@.discussions.microsoft.com> wrote in message
> news:DDFF5917-13B1-44D2-8138-EB44818B579D@.microsoft.com...
> > We have installed SP2 and client-side printing from the browser is
working
> > fine for our users on XP/IE6. However, we have some users with NT4/IE6
on
> > their machines. They are able to view reports, but when they click the
> Print
> > icon, nothing happens (no error, no prompt, nothing).
> >
> > Is the ActiveX control for printing included in SP2 supported for
clients
> > using NT4?
>|||We didn't test on NT4. We tested on XP, Win 2003, Win 2000, Win 98 and
WinME.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O8w1aIYUFHA.580@.TK2MSFTNGP15.phx.gbl...
> When a product is given end of lifetime that really does mean that. It
> might
> work or it might not but MS spends 0 time testing with it. It is not part
> of
> their test suite. Extended support expired almost a year ago. I assume the
> users can continute to export to PDF and print that way.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Fredrick Bartlett" <rick_bartlett@.WeDoNET.net> wrote in message
> news:Os2pNyPUFHA.3184@.TK2MSFTNGP15.phx.gbl...
>> Ah, crap...
>> Same here. That's a show stopper!!!
>> "MELMEL" <MELMEL@.discussions.microsoft.com> wrote in message
>> news:DDFF5917-13B1-44D2-8138-EB44818B579D@.microsoft.com...
>> > We have installed SP2 and client-side printing from the browser is
> working
>> > fine for our users on XP/IE6. However, we have some users with NT4/IE6
> on
>> > their machines. They are able to view reports, but when they click the
>> Print
>> > icon, nothing happens (no error, no prompt, nothing).
>> >
>> > Is the ActiveX control for printing included in SP2 supported for
> clients
>> > using NT4?
>>
>

client/server performance problems

We have 1000 users on a client/server CRM application that we've written in
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
Doug
My guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
http://www.microsoft.com/sql/techinf...perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.co...ance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.co...mance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/de...rfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>

client/server performance problems

We have 1000 users on a client/server CRM application that we've written in
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
DougMy guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>

client/server performance problems

We have 1000 users on a client/server CRM application that we've written in
house. Recently we started experiencing performance problems at the top of
each hour, it lasts for about 30 seconds. I have run several traces using
these events:
lock: acquired,
lock: deadlock,
rpc: completed,
sql: stmtcompleted
Each trace runs from 10 till the hour until 10 after the hour. I've dumped
them out to tables so I could query them for the top 200 records by cpu,
duration, reads and writes. I then query them by each minute of starttime to
find the worst queries. I have fixed a lot of bad queries over the past
couple of weeks but still users are complaining. We are runnings Sql Server
2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for the
server but our I'm told it has a lot of cpu and at I think 8GB of memory. I
am not a dba, I am a developer but I cant understand why I'm not solving the
problem. The queries that I've fixed are running much faster, but still the
problem persists.
Am I not using the right events in SQL Profiler? Is it possible that we're
really having some sort of network issue at that time? I would use Perfmon
but that doesnt tell me much.
Any help is appreciated,
DougMy guess is you have a scheduled job that runs on the hour each hour. It
could also be related to checkpoints but they rarely follow such an exact
cycle. You can see these with the SQL counter CHeckpoin Pages per second.
You should monitor for CPU, Disks etc as well. See if these help:
http://www.microsoft.com/sql/techin.../perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.c...mance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.c...rmance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/d.../>
on_24u1.asp
Disk Monitoring
Andrew J. Kelly SQL MVP
"Doug Stiers" <doug@.nospam.com> wrote in message
news:%23t7lrXYCFHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have 1000 users on a client/server CRM application that we've written
> in
> house. Recently we started experiencing performance problems at the top of
> each hour, it lasts for about 30 seconds. I have run several traces using
> these events:
> lock: acquired,
> lock: deadlock,
> rpc: completed,
> sql: stmtcompleted
> Each trace runs from 10 till the hour until 10 after the hour. I've dumped
> them out to tables so I could query them for the top 200 records by cpu,
> duration, reads and writes. I then query them by each minute of starttime
> to
> find the worst queries. I have fixed a lot of bad queries over the past
> couple of weeks but still users are complaining. We are runnings Sql
> Server
> 2000 sp3a on a clustered Win2k SP4. I dont know the hardware config for
> the
> server but our I'm told it has a lot of cpu and at I think 8GB of memory.
> I
> am not a dba, I am a developer but I cant understand why I'm not solving
> the
> problem. The queries that I've fixed are running much faster, but still
> the
> problem persists.
> Am I not using the right events in SQL Profiler? Is it possible that we're
> really having some sort of network issue at that time? I would use Perfmon
> but that doesnt tell me much.
> Any help is appreciated,
> Doug
>

Client to Client communication

Hello,
I have a SQL Server 2005 Database. On some clients (I will call that input
clients) users will enter data and on some other clients these Data will be
used.
Well, I want to avoid, that the other clients have to poll the database all
the time for changes. I thought of somethig like an event which will be rised
at the input client and catched at the other clients.
Is there any possibility to do something like that direct with the SQL
Server (Express) where all the clients are connected to?
Or do I have to write my own server application where the clients have to
connect too and make my own network communication through sockets ect.
Note: The application is ought to work with the SQL Server 2005 Express by
default (for smaller databases), but will be abel to use the SQL Server 2005
also.
thanks, Robert
You could use Global ##Temp tables.
But then again, you will still be using a table on the server, and the
clients will have to poll that table for changes.
But if you were using Notification Services, the clients could be notified
that the changed data is available. (Of course, Notification Services is NOT
included with SQL Express.)
Basically, you are 'butting heads' with the design paradigm of SQL Server.
It is a data STORAGE and RETREIVAL service, NOT a COMMUNICATION service. You
may need to create your own communication service.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"rfw68" <rfw68@.nospam.nospam> wrote in message
news:6AE389B2-A7B3-45E7-974A-4E98AC97A7A8@.microsoft.com...
> Hello,
> I have a SQL Server 2005 Database. On some clients (I will call that input
> clients) users will enter data and on some other clients these Data will
> be
> used.
> Well, I want to avoid, that the other clients have to poll the database
> all
> the time for changes. I thought of somethig like an event which will be
> rised
> at the input client and catched at the other clients.
> Is there any possibility to do something like that direct with the SQL
> Server (Express) where all the clients are connected to?
> Or do I have to write my own server application where the clients have to
> connect too and make my own network communication through sockets ect.
> Note: The application is ought to work with the SQL Server 2005 Express by
> default (for smaller databases), but will be abel to use the SQL Server
> 2005
> also.
> thanks, Robert
>
|||Hello Robert,,
I agree with Arnie that SQL Server is not meant to do this job since it's
mainly for data storage and retrivial.
As Arnie mentioned, Notification service can be used to do this type of
jobs but it is not included in Express edition.
Notification Services
http://www.microsoft.com/sql/technologies/notification/default.mspx
An Introduction to SQL Server Notification Services
http://www.codeproject.com/dotnet/sqlns.asp
Usually you could add subscription to your application so that it could be
notified when data is changed on the server side, and client application
can retrival the newest data at this time.
If you don't want to use Notification Services, you may need to develop
your own service on server side to monitor the data change in SQL Server
and notfiy the clients. Since many users may change data concurrently, I
think it is usually not realistic for it to notifiy other clients by using
client-client communication. A central point might be a better option for
this situation.
If you have any comments or feedback, 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,
I think that our SocketPro can help you out easily at www.udaparts.com
See the article "
Notify your coworkers any messages anywhere
" at http://www.udaparts.com/document/articles/chatservice.htm
Also, see the tutorial 2 inside the package SocketPro for our chat
service.
Regards,
"rfw68" <rfw68@.nospam.nospam> wrote in message
news:6AE389B2-A7B3-45E7-974A-4E98AC97A7A8@.microsoft.com...
> Hello,
> I have a SQL Server 2005 Database. On some clients (I will call that input
> clients) users will enter data and on some other clients these Data will
> be
> used.
> Well, I want to avoid, that the other clients have to poll the database
> all
> the time for changes. I thought of somethig like an event which will be
> rised
> at the input client and catched at the other clients.
> Is there any possibility to do something like that direct with the SQL
> Server (Express) where all the clients are connected to?
> Or do I have to write my own server application where the clients have to
> connect too and make my own network communication through sockets ect.
> Note: The application is ought to work with the SQL Server 2005 Express by
> default (for smaller databases), but will be abel to use the SQL Server
> 2005
> also.
> thanks, Robert
>

Client Database Access/Opened Status

Is there a way to tell if a SQL database is currently being accessed by any
clients? Is it possible to see which clients/users are currently accessing a
SQL database?
Thanks,
Scott
You can query sysprocesses, execute sp_who2. On SQL Server
2005, you can query sys.dm_exec_sessions and
sys.dm_exec_connections.
-Sue
On Wed, 7 Feb 2007 13:32:26 -0800, Scott
<Scott@.discussions.microsoft.com> wrote:

>Is there a way to tell if a SQL database is currently being accessed by any
>clients? Is it possible to see which clients/users are currently accessing a
>SQL database?
>Thanks,
>Scott

Client access to sql database

sql 2000 on 2003 server standard, only 2 out of 10 users (xp pro clients)
trying to access sql program are asked to login to database, server name,
database, login id, password to TableName: datatable.
Is this an xp client issue or sql issue.
Can you give more details? How exactly are they logging in? Are you using
Windows authentication or SQL? Windows won't prompt you to login to SQL
Server since you already logged in to the domain.
Andrew J. Kelly SQL MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:FB02C0E3-2E67-4979-B809-C49BE0496BCA@.microsoft.com...
> sql 2000 on 2003 server standard, only 2 out of 10 users (xp pro clients)
> trying to access sql program are asked to login to database, server name,
> database, login id, password to TableName: datatable.
> Is this an xp client issue or sql issue.
|||Users logon to the domain, and from a shared drive the clients run an msi
file that installs a program on their local pc's, this uses a sql database.
They start the program and when they try to run a report they get a pop up
box asking for login info for "TableName datatable". I think it is Windows
Authentication, don't know how to check which one is being used.
Trying to determine if an xp client issue or sql issue.
"Andrew J. Kelly" wrote:

> Can you give more details? How exactly are they logging in? Are you using
> Windows authentication or SQL? Windows won't prompt you to login to SQL
> Server since you already logged in to the domain.
> --
> Andrew J. Kelly SQL MVP
>
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:FB02C0E3-2E67-4979-B809-C49BE0496BCA@.microsoft.com...
>
>
|||If it is windows authentication it should not need to ask. You really need
to see what the application is using for a connection string.
Andrew J. Kelly SQL MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:BEF206D6-AA2F-4949-9D5C-95B13AE16D42@.microsoft.com...[vbcol=seagreen]
> Users logon to the domain, and from a shared drive the clients run an msi
> file that installs a program on their local pc's, this uses a sql
> database.
> They start the program and when they try to run a report they get a pop up
> box asking for login info for "TableName datatable". I think it is Windows
> Authentication, don't know how to check which one is being used.
> Trying to determine if an xp client issue or sql issue.
> "Andrew J. Kelly" wrote:
|||Unfortunately I'm not a sql guru, just a network admin. This program that
points to the database was created by an outside company. Of course they say
that because only 2 of clients have the login issue it must be an xp client
issue and has nothing to do with the program. But the users are already in
the program, it is only when they try to run a report from within the program
that the login box appears?
Got any opinions one way or another?
"Andrew J. Kelly" wrote:

> If it is windows authentication it should not need to ask. You really need
> to see what the application is using for a connection string.
> --
> Andrew J. Kelly SQL MVP
>
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:BEF206D6-AA2F-4949-9D5C-95B13AE16D42@.microsoft.com...
>
>
|||What ever it is that is attempting to run the report must be making a new
connection. But without knowing what or how it is attempting to do this it
is anyone's guess. The vendor must have a clue as to how they connect and
why this is happening. After all they wrote it<g>. You can try using
profiler to see what they are sending to sql server at the time it happens.
Other than that I don't have a clue as to what the app is really doing.
Andrew J. Kelly SQL MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:A498DAFD-838E-466C-A03F-FC3102784162@.microsoft.com...[vbcol=seagreen]
> Unfortunately I'm not a sql guru, just a network admin. This program that
> points to the database was created by an outside company. Of course they
> say
> that because only 2 of clients have the login issue it must be an xp
> client
> issue and has nothing to do with the program. But the users are already in
> the program, it is only when they try to run a report from within the
> program
> that the login box appears?
> Got any opinions one way or another?
> "Andrew J. Kelly" wrote:

clearing execution plan cache

Hi,

We have an application which fetches data from a table which has approximately 1 million records.

1. Nearly 25 users will be using this application concurrently.

2. frequent updations will be done to the records in the geographyrolecurriculum table.

3. This table has 1 clustered index and 4 nonclustered index bounded to it.

Problem Statement:

1. Application runs smoothly for 15 - 20 days and after that all the screens throws timeout errors.

When i clear the sys.syscacheobjects its working fine again and screens get loaded quickly.

Please tell me how clearing the syscacheobjects makes the execution fast? and is this the correct way to solve the timeout issue or is there any other alternative?

2. Will the stored procs timeout if the tempdb is full ?

Thanks,

Arunprasad

Hi,

1. Not sure if you're doing this but you should NEVER manually make changes to the system tables, I am hoping that you are running something like DBCC FREEPROCCACHE to clear out the cache.

What I reckon is happening when you clear out the cache is you are basically forcing all the queries to generate new execution plans, and the new plans seem to be faster than the older ones.

What you need to do to ensure that the query optimiser has enough information to determine the correct query plan is to:

a. Maintain you indexes

DBCC INDEXDEFRAG

ALTER INDEX ….. REBUILD / DBCC DBREINDEX

b. Keep your statistics up-to-date

sp_createstats / CREATE STATISTICS

sp_updatestats / UPDATE STATISTICS

2. Managing you tempdb is a task in itself. This is a very interesting read:

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Basically if you sps are creating temp tables or you are using order by, group by, etc you will be using your tempdb. There are issues around the tempdb but they can be managed.

|||

Hi xrayb,

Thanks for the suggestion and we did what you have adviced us to do but the customer says its a hectic process.

So can you suggest me a workaround?

1. i have update STATISTICS ON for all the databases( DATABASE LEVEL) my server has will this have an effect on the table as well?

(Ex: Update statistics <tablename>)

2. Will the new execution plan be always better the older ones always? because this is happening very frequently in my application.

3. Do you have any other suggestion or findings why this timeout happens this frequently.

4. I appreaciate the effort you have put in to clear my doubt.

Thanks,

Arun

|||

1. I have a couple of issues with turning the create / update stats feature on at DB level, although they may not be an issues in your environment but I see them as:

a. You have no control over when it's run.

b. The update is triggered when they are already out of date, and I think this is based on a percentage (I’ve heard 10% and 20%), this is not a problem when you 1000 rows in your table but if you have 50,000,000 then 10% is 5,000,000 which is an awful long time to wait for the stats to be updated. BTW you can enable trace flag 205 to check how often this happens.

Also compare your estimated and an actual graphical execution plans, this will also let on if the stats are current.

Updating stats should be run each time there's a substantial change to the data / indexes, also the ALTER INDEX can be an online operation (although I would only run it during the working day under duress).

Use the DBCC SHOW_STATISTICS command to check individual tables and see what their current state is regarding stats.

2. The execution may be different and hopefully it will be quicker and more efficient (but not always). You may need to do some research into why this is happening, again, like managing your tempdb, performance tuning is an art in its self. Have a look at the link below it will give you enough information to query the DMVs and check how your query is being treated by the query optimiser (like the query plan).

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

I have slightly modified one of the queries and this returns the top 50 overview of which currently cached batches or procedures are using the most CPU and how many times they are being executed.

Code Snippet

select top 50

sum(qs.total_worker_time) as total_cpu_time,

sum(qs.execution_count) as total_execution_count,

count(*) as number_of_statements,

qs.plan_handle,

eqp.objectid,

OBJECT_NAME(eqp.objectid)

from

sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS eqp

group by qs.plan_handle,eqp.objectid

order by sum(qs.total_worker_time) desc, sum(qs.execution_count) desc

3. You basically need to identify what queries are performing badly, you can capture them (with the above mentioned script) or with server side traces or profiler. You have options to return long running queries. Once you have identified these queries you can start working on them individually.

From what you have described I reckon you have some poorly written queries and as soon as there's a load on the server or the stats are slightly outdated they just collapse in on themselves. I would try and capture the offending query and examine it, you won’t have to go through many before you realise if it’s poorly written.

Have a look at the DMVs too.

From BOL:

Find the 10 missing indexes with the highest anticipated improvement for user queries

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC;