Profiler and performance

I'm planning on running a profiler trace on my DW database. It will run all
night long.
My concern is how can profiler trace effect my databse performance?
Thanks.Sinke
http://www.sql-server-performance.com/sql_server_profiler_tips.asp
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
> all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>|||If you can :
1)Run the Profiler from another machine
2)Be careful in the data you caollect i.e specify events , and use filters,
particuarly the database(s) you wnat to capture data on. Also, you may not
want to capture data of every single event
3)Save the data to a file on your hard disk , rather than a db table
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>sql

Profiler and performance

I'm planning on running a profiler trace on my DW database. It will run all
night long.
My concern is how can profiler trace effect my databse performance?
Thanks.Sinke
http://www.sql-server-performance.c...ofiler_tips.asp
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
> all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>|||If you can :
1)Run the Profiler from another machine
2)Be careful in the data you caollect i.e specify events , and use filters,
particuarly the database(s) you wnat to capture data on. Also, you may not
want to capture data of every single event
3)Save the data to a file on your hard disk , rather than a db table
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"Sinke Pinke" <sinkepinke@.REM_OVEnet.hr> wrote in message
news:e8dchs$539$1@.ss408.t-com.hr...
> I'm planning on running a profiler trace on my DW database. It will run
all
> night long.
> My concern is how can profiler trace effect my databse performance?
> Thanks.
>

Profiler 2005 issue

Hi!

When I try to run profile 2005 ... as soon as i hit the connect button (authentication dialog) I get this error:

The procedure entry point lstrcpynI could not be located in the dynamic link library MSDART.DLL

any ideas?

Everything is up to date on my system. my configuration:

SQL Server 2000 enterprise (can't wait till next week to get EE for 2005)
WS 2003 w/SP1
.NET 1.1/2.0 (2.0 was installed with SQL 2005)
VS.NET 2003 (going to install 2005 later)

Office 2003 with SP2anyone?

Profiler / sql trace

There are several ways to capture/view sql activities. Can
you tell me what the best way is ?
Run Profiler from a client machine or
Use sql trace procs from a client machine using QA or
Run sql trace procs at the server (which I've seen MS PSS
has always used)
thanksFor a quick look at a server I'll use Profiler from my PC and if I want to
keep it I'll save to a file (which gives me the ability to save to a table
later if I want to do more analysis) For longer traces I'll run a server
side trace on the server and generally import the resulting files into a
table for analysis. I tend to use a rollover file size of 50 MB to keep them
manageable and generally define a stop time or set up a job to stop it at a
future date.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dba" <Dba@.Dba1.Com> wrote in message
news:021001c3d61e$e5e79bf0$a401280a@.phx.gbl...
> There are several ways to capture/view sql activities. Can
> you tell me what the best way is ?
> Run Profiler from a client machine or
> Use sql trace procs from a client machine using QA or
> Run sql trace procs at the server (which I've seen MS PSS
> has always used)
> thanks|||Hello,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Based on my experience, this kind of problem does not have an easy and
clear answer. It is hard for me to tell you which the best way is to
capture/view sql activities; what a good method is for you may not be a
good method for other people. The way in which we choose to monitor SQL
Server depends on specific and particular concerns. I can help you find
the most appropriate way for you to monitor SQL server based on your needs,
but, please understand that there is no "best way" to do this.
When people use stored procedures to monitor SQL Server, the information we
are able to get is limited. For example:
Sp_who stored procedure provides information about current Microsoft SQL
Server users and processes.
sp_lock stored procedure reports information about locks.
SQL Server Profiler (SQL Trace) is a graphical tool that allows system
administrators and database developers to monitor engine events on
computers running Microsoft SQL Server. Using SQL Server Profiler on the
client machine to monitor SQL Server will increase network traffic.
Monitoring on the server will increase the workload of the server machine.
For more information regarding this issue, please refer to the following
article.
325263 Support WebCast: SQL Server 2000 Profiler: What's New and How to
http://support.microsoft.com/?id=325263
I hope the above explanation is clear. Please let us know if you need
further assistance on this issue.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michael,
I'm very familiar about sp_lock/sp_who. My main confusion
is that MS PSS strongly is against running Profiler at the
client side everytime I've talked/worked with them. If the
Proiler is so bad, why MS ship it at all ! As I've said
they have always run sql trace procs at our sql server
box. Any comments ?
thanks
long
>--Original Message--
>Hello,
>My name is Michael and I would like to thank you for
using Microsoft
>newsgroup.
>Based on my experience, this kind of problem does not
have an easy and
>clear answer. It is hard for me to tell you which the
best way is to
>capture/view sql activities; what a good method is for
you may not be a
>good method for other people. The way in which we choose
to monitor SQL
>Server depends on specific and particular concerns. I
can help you find
>the most appropriate way for you to monitor SQL server
based on your needs,
>but, please understand that there is no "best way" to do
this.
>When people use stored procedures to monitor SQL Server,
the information we
>are able to get is limited. For example:
>Sp_who stored procedure provides information about
current Microsoft SQL
>Server users and processes.
>sp_lock stored procedure reports information about locks.
>SQL Server Profiler (SQL Trace) is a graphical tool that
allows system
>administrators and database developers to monitor engine
events on
>computers running Microsoft SQL Server. Using SQL Server
Profiler on the
>client machine to monitor SQL Server will increase
network traffic.
>Monitoring on the server will increase the workload of
the server machine.
>For more information regarding this issue, please refer
to the following
>article.
>325263 Support WebCast: SQL Server 2000 Profiler: What's
New and How to
>http://support.microsoft.com/?id=325263
>I hope the above explanation is clear. Please let us know
if you need
>further assistance on this issue.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Hi Long,
Thanks for your feedback. Generally, the way we choose to monitor SQL
Server depends on the specific objective. Based on the particular
situation, our engineers will choose the most appropriate method. As for
this issue, can you please give me a sample to help describe the specific
objective? That way, we can help you analyze and determine which the most
appropriate way is to monitor SQL Server.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Long,
Thank you for choosing Microsoft! Michael is on holiday and I'm his backup. My name is
Billy and it's my pleasure to assist you with this issue.
To start with, as we known, Microsoft sometimes recommends our customers to perform the
best practices for their benefits. However, we never prohibit customers from applying their
easier way to do anything. It all depends on customer's specific issues and detailed
scenarios.
Based on my experience, the main reason why you are strongly recommended to run the
Profiler at the server side is that it guarantees you can trace all the information.
In the GUI trace setup, you can click the checkbox for: "server processes sql server trace
data"
The stored procedures to create/exec profiler traces will guarantee events will not be
dropped unless you use: sp_create_trace @.options = 1 which instead of a file creates a
rowset.
Server side trace files guarantee events not to be dropped, however, result set (or rowset
cannot guarantee this. On the client side, you cannot ensure the trace information is
complete as SQL Profiler gets the trace as a result set to the client.
Another consideration is the trace performance. On the client side, the trace information is
received as a result set, in which case if there is a large volume of trace information, the
performance will definitely be poorer than tracing on the server side. That's why we
recommend you run the Profiler on the server side.
However, if trace information is complete and the performance is also ok on the client side. I
fully understand that running the Profiler is convenient and also efficient for you in some
situations (such as the server is not on hand). Do you agree with me Long?
If there is anything more we can still do to assist you, please don't hesitate to let us know.
We are here to be of assistance!
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Profiler "settings" are greyed out

Ive run a trace and Im trying to access the Settings option on the Replay
button but its greyed out. Any ideas why?
sql2k sp3
TIA, ChrisR
ChrisR wrote:
> Ive run a trace and Im trying to access the Settings option on the
> Replay button but its greyed out. Any ideas why?
Start the replay first against the server in question. When you start,
those are the settings. You can access them again from the menu once the
replay is started.
David Gugick
Imceda Software
www.imceda.com

Profiler "settings" are greyed out

Ive run a trace and Im trying to access the Settings option on the Replay
button but its greyed out. Any ideas why?
--
sql2k sp3
TIA, ChrisRChrisR wrote:
> Ive run a trace and Im trying to access the Settings option on the
> Replay button but its greyed out. Any ideas why?
Start the replay first against the server in question. When you start,
those are the settings. You can access them again from the menu once the
replay is started.
--
David Gugick
Imceda Software
www.imceda.com

Profiler "settings" are greyed out

Ive run a trace and Im trying to access the Settings option on the Replay
button but its greyed out. Any ideas why?
sql2k sp3
TIA, ChrisRChrisR wrote:
> Ive run a trace and Im trying to access the Settings option on the
> Replay button but its greyed out. Any ideas why?
Start the replay first against the server in question. When you start,
those are the settings. You can access them again from the menu once the
replay is started.
David Gugick
Imceda Software
www.imceda.comsql

Profiler

Is there any way I can give user permission to run SQL profiler witout giving
them sysadmin access
thx
stoney wrote:
> Is there any way I can give user permission to run SQL profiler
> witout giving them sysadmin access
> thx
No. It is a sysadmin function. SQL 2005 will have other options.
David Gugick
Imceda Software
www.imceda.com
sql

PROFILER

Hello,
What would be the best place to run profiler, on the production Server or on
another client Machine?
Thanks,
QA
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"OA" <omrana@.verizon.net> wrote in message
news:%23GQ7oPukIHA.1052@.TK2MSFTNGP05.phx.gbl...
> Hello,
> What would be the best place to run profiler, on the production Server or
> on another client Machine?
>
> Thanks,
>

Profiler

Hey,
I want to run the profiler in certain times during the
day. Instead of me opening and closing sessions of
profiler, i extracted the script and run it through query
analyser (then schedula it as a job and run at the times i
want). I am testing this now and there seems to be a
problem. I run the script i got and when i execute it in
query anaylser the profiler file is created in the
location i want but no data is being written to it. I
cannot delete it because "the file is being used"... does
anyone know why this happenes? any work arrounds?
Help please!!Hi Claudia
This is normal.
Data is only written to the trace file in 128K chunks. So as soon as there
is 128K worth of events, you'll see some size for the file. If you stop and
close the trace, it will also write all the remaining data to the trace
file, or if the server is stopped.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Claudia" <anonymous@.discussions.microsoft.com> wrote in message
news:03dd01c39954$e85764d0$a001280a@.phx.gbl...
> Hey,
> I want to run the profiler in certain times during the
> day. Instead of me opening and closing sessions of
> profiler, i extracted the script and run it through query
> analyser (then schedula it as a job and run at the times i
> want). I am testing this now and there seems to be a
> problem. I run the script i got and when i execute it in
> query anaylser the profiler file is created in the
> location i want but no data is being written to it. I
> cannot delete it because "the file is being used"... does
> anyone know why this happenes? any work arrounds?
> Help please!!sql

Profiler

Is there any way I can give user permission to run SQL profiler witout giving
them sysadmin access
thxstoney wrote:
> Is there any way I can give user permission to run SQL profiler
> witout giving them sysadmin access
> thx
No. It is a sysadmin function. SQL 2005 will have other options.
--
David Gugick
Imceda Software
www.imceda.com

PROFILER

Hello,
What would be the best place to run profiler, on the production Server or on
another client Machine?
Thanks,QA
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
"OA" <omrana@.verizon.net> wrote in message
news:%23GQ7oPukIHA.1052@.TK2MSFTNGP05.phx.gbl...
> Hello,
> What would be the best place to run profiler, on the production Server or
> on another client Machine?
>
> Thanks,
>sql

production trouble

Why would a copy of a database run faster than the original? We had copied
the production database, to our development server a month ago. We are
having major performance problems with production now. It takes 60 seconds
to retrieve one table in production; in development the same table takes 2
seconds. I am logged in to the production server through Terminal Server, so
network shouldn't be an issue. The servers are built to the same
specification (RAM, hard drives,...) The production server only has one
database, our dev server has many databases competing for resources. I have
defragmented the indexes, but that does not help. Any other clues?
Thanks,
Greg
Heck it could be lots of things. Maybe this can help narrow down the
bottlenecks.
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
"Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
news:%23QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> Why would a copy of a database run faster than the original? We had copied
> the production database, to our development server a month ago. We are
> having major performance problems with production now. It takes 60 seconds
> to retrieve one table in production; in development the same table takes 2
> seconds. I am logged in to the production server through Terminal Server,
> so
> network shouldn't be an issue. The servers are built to the same
> specification (RAM, hard drives,...) The production server only has one
> database, our dev server has many databases competing for resources. I
> have
> defragmented the indexes, but that does not help. Any other clues?
> Thanks,
> Greg
>
|||Is number of CPUs same as well? More can be worse, try maxdop :-) same
service packs, hotfixes, sp_configure?
Blocking on production? What is production query waiting for? (sysprocesses,
blocker script)
Same execution plans? If different, than why?
Should get you started ...
Cheers,
AD
"Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
news:#QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> Why would a copy of a database run faster than the original? We had copied
> the production database, to our development server a month ago. We are
> having major performance problems with production now. It takes 60 seconds
> to retrieve one table in production; in development the same table takes 2
> seconds. I am logged in to the production server through Terminal Server,
so
> network shouldn't be an issue. The servers are built to the same
> specification (RAM, hard drives,...) The production server only has one
> database, our dev server has many databases competing for resources. I
have
> defragmented the indexes, but that does not help. Any other clues?
> Thanks,
> Greg
>
|||one of the most common things is the statistics on the columns
- check the last time the stats was updated on production and what the
sample rate was dbcc show_statistics/select stats_date(function)
- check what the rowmodctr(in sysindexes table) on the particular columns
involved in the query
- check that there are no _WA_sys(in sysindexes table) statistics
auto-generated for certain columns within this table.
post the query on to the newsgroup.
can you identify what part of the query is running slow?
HTH
"Andrew" wrote:

> Is number of CPUs same as well? More can be worse, try maxdop :-) same
> service packs, hotfixes, sp_configure?
> Blocking on production? What is production query waiting for? (sysprocesses,
> blocker script)
> Same execution plans? If different, than why?
> Should get you started ...
> Cheers,
> AD
> "Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
> news:#QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> so
> have
>
>

production trouble

Why would a copy of a database run faster than the original? We had copied
the production database, to our development server a month ago. We are
having major performance problems with production now. It takes 60 seconds
to retrieve one table in production; in development the same table takes 2
seconds. I am logged in to the production server through Terminal Server, so
network shouldn't be an issue. The servers are built to the same
specification (RAM, hard drives,...) The production server only has one
database, our dev server has many databases competing for resources. I have
defragmented the indexes, but that does not help. Any other clues?
Thanks,
GregHeck it could be lots of things. Maybe this can help narrow down the
bottlenecks.
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
"Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
news:%23QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> Why would a copy of a database run faster than the original? We had copied
> the production database, to our development server a month ago. We are
> having major performance problems with production now. It takes 60 seconds
> to retrieve one table in production; in development the same table takes 2
> seconds. I am logged in to the production server through Terminal Server,
> so
> network shouldn't be an issue. The servers are built to the same
> specification (RAM, hard drives,...) The production server only has one
> database, our dev server has many databases competing for resources. I
> have
> defragmented the indexes, but that does not help. Any other clues?
> Thanks,
> Greg
>|||Is number of CPUs same as well? More can be worse, try maxdop :-) same
service packs, hotfixes, sp_configure?
Blocking on production? What is production query waiting for? (sysprocesses,
blocker script)
Same execution plans? If different, than why?
Should get you started ...
Cheers,
AD
"Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
news:#QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> Why would a copy of a database run faster than the original? We had copied
> the production database, to our development server a month ago. We are
> having major performance problems with production now. It takes 60 seconds
> to retrieve one table in production; in development the same table takes 2
> seconds. I am logged in to the production server through Terminal Server,
so
> network shouldn't be an issue. The servers are built to the same
> specification (RAM, hard drives,...) The production server only has one
> database, our dev server has many databases competing for resources. I
have
> defragmented the indexes, but that does not help. Any other clues?
> Thanks,
> Greg
>|||one of the most common things is the statistics on the columns
- check the last time the stats was updated on production and what the
sample rate was dbcc show_statistics/select stats_date(function)
- check what the rowmodctr(in sysindexes table) on the particular columns
involved in the query
- check that there are no _WA_sys(in sysindexes table) statistics
auto-generated for certain columns within this table.
post the query on to the newsgroup.
can you identify what part of the query is running slow?
HTH
"Andrew" wrote:
> Is number of CPUs same as well? More can be worse, try maxdop :-) same
> service packs, hotfixes, sp_configure?
> Blocking on production? What is production query waiting for? (sysprocesses,
> blocker script)
> Same execution plans? If different, than why?
> Should get you started ...
> Cheers,
> AD
> "Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
> news:#QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> > Why would a copy of a database run faster than the original? We had copied
> > the production database, to our development server a month ago. We are
> > having major performance problems with production now. It takes 60 seconds
> > to retrieve one table in production; in development the same table takes 2
> > seconds. I am logged in to the production server through Terminal Server,
> so
> > network shouldn't be an issue. The servers are built to the same
> > specification (RAM, hard drives,...) The production server only has one
> > database, our dev server has many databases competing for resources. I
> have
> > defragmented the indexes, but that does not help. Any other clues?
> >
> > Thanks,
> >
> > Greg
> >
> >
>
>

production trouble

Why would a copy of a database run faster than the original? We had copied
the production database, to our development server a month ago. We are
having major performance problems with production now. It takes 60 seconds
to retrieve one table in production; in development the same table takes 2
seconds. I am logged in to the production server through Terminal Server, so
network shouldn't be an issue. The servers are built to the same
specification (RAM, hard drives,...) The production server only has one
database, our dev server has many databases competing for resources. I have
defragmented the indexes, but that does not help. Any other clues?
Thanks,
GregHeck it could be lots of things. Maybe this can help narrow down the
bottlenecks.
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
"Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
news:%23QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> Why would a copy of a database run faster than the original? We had copied
> the production database, to our development server a month ago. We are
> having major performance problems with production now. It takes 60 seconds
> to retrieve one table in production; in development the same table takes 2
> seconds. I am logged in to the production server through Terminal Server,
> so
> network shouldn't be an issue. The servers are built to the same
> specification (RAM, hard drives,...) The production server only has one
> database, our dev server has many databases competing for resources. I
> have
> defragmented the indexes, but that does not help. Any other clues?
> Thanks,
> Greg
>|||Is number of CPUs same as well? More can be worse, try maxdop :-) same
service packs, hotfixes, sp_configure?
Blocking on production? What is production query waiting for? (sysprocesses,
blocker script)
Same execution plans? If different, than why?
Should get you started ...
Cheers,
AD
"Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
news:#QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> Why would a copy of a database run faster than the original? We had copied
> the production database, to our development server a month ago. We are
> having major performance problems with production now. It takes 60 seconds
> to retrieve one table in production; in development the same table takes 2
> seconds. I am logged in to the production server through Terminal Server,
so
> network shouldn't be an issue. The servers are built to the same
> specification (RAM, hard drives,...) The production server only has one
> database, our dev server has many databases competing for resources. I
have
> defragmented the indexes, but that does not help. Any other clues?
> Thanks,
> Greg
>|||one of the most common things is the statistics on the columns
- check the last time the stats was updated on production and what the
sample rate was dbcc show_statistics/select stats_date(function)
- check what the rowmodctr(in sysindexes table) on the particular columns
involved in the query
- check that there are no _WA_sys(in sysindexes table) statistics
auto-generated for certain columns within this table.
post the query on to the newsgroup.
can you identify what part of the query is running slow?
HTH
"Andrew" wrote:

> Is number of CPUs same as well? More can be worse, try maxdop :-) same
> service packs, hotfixes, sp_configure?
> Blocking on production? What is production query waiting for? (sysprocesse
s,
> blocker script)
> Same execution plans? If different, than why?
> Should get you started ...
> Cheers,
> AD
> "Greg" <greg.demieville@.nospambrinksinc.com> wrote in message
> news:#QMgXMRIFHA.3012@.TK2MSFTNGP10.phx.gbl...
> so
> have
>
>

Production server error

Hello,
I have reports that have embedded code from a custom assembly.
In design, they work fine. When I deploy them and run them via the
Report Manager on production I get the following error in the cells
where I have this code running:
"Error in method xxxxxx - Request for the permission of type
System.Data.SqlClient.SqlClientPermission, System.Data,
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
failed."
I have followed all previous articles, made changes to .config files,
etc., etc. Given the assembly full trust, etc. Still no luck.
Has ANYONE actually got this to work? Crystal reports was a pain, but
at least it worked!
Thanks for any help with this.
FabioIt sounds like you did not explicitly assert permissions to open a database
connection. Unless you assert (!) the permission explicitly, it will fail
with a security exception. Example for opening a connection to a SQL Server
in custom code / custom assemblies:
...
SqlClientPermission permission = new
SqlClientPermission(Permission­State.Unrestricted);
try
{
permission.Assert(); // Assert security permission !!!
SqlConnection conn = new SqlConnection("...");
conn.Open();
...
}
BTW: you don't need FullTrust for using the SqlClient in a custom assembly,
because the SqlClient is enabled for partial trust scenarios. Just check the
MSDN documentation on the SqlClientPermission class.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
<fassmann@.gmail.com> wrote in message
news:1124303487.138374.35130@.g43g2000cwa.googlegroups.com...
> Hello,
> I have reports that have embedded code from a custom assembly.
> In design, they work fine. When I deploy them and run them via the
> Report Manager on production I get the following error in the cells
> where I have this code running:
> "Error in method xxxxxx - Request for the permission of type
> System.Data.SqlClient.SqlClientPermission, System.Data,
> Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
> failed."
> I have followed all previous articles, made changes to .config files,
> etc., etc. Given the assembly full trust, etc. Still no luck.
> Has ANYONE actually got this to work? Crystal reports was a pain, but
> at least it worked!
> Thanks for any help with this.
> Fabio
>|||Thanks Robert.
I am explicity asserting permissions. Here's the code I'm using. Am I
missing something?
Try
Dim permission As New
SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
permission.Assert()
'Use the MS data access app block to get the data reader
dr = _myDBAccess.ExecuteReader(_myConn,
CommandType.StoredProcedure, "Trader_GetTransByAssetID", New
SqlParameter("@.AssetID", nAssetID))
'Loop thru all transactions to build arraylist of
transaction objects
While (dr.Read())
objTrans = New Transaction(dr.GetInt32(COL_TRANSID),
dr.GetDateTime(COL_TRADEDATE), dr.GetSqlMoney(COL_DOLLARS).ToDouble,
sShareClass, 0.0, dr.GetSqlDecimal(COL_SHARES).ToDouble,
dr.GetSqlDecimal(COL_PRICE).ToDouble,
CType(dr.GetSqlInt16(COL_CALCSHAREBALANCE).ToString, Integer),
CType(dr.GetSqlInt16(COL_CALCCOSTBASIS).ToString, Integer))
arlTrans.Add(objTrans)
End While
Catch ex As Exception
_myErrorMessage += " Error in method
CDSC.BuildTransArray - " & ex.Message
Finally
'Clean Up
If Not (IsNothing(dr)) Then
dr.Close()
End If
End Try
Thanks for your help.|||OK, got it fixed.
You have to explicitly open the connection everywhere you use this. So,
we've got it added to every function/procedure within our assembly.
So not oly are we using the ExecuteReader, but we're opening the
connection each time as well.|||I have a similar problem, could you please give some example on this. when
you say
> So not oly are we using the ExecuteReader, but we're opening the
> connection each time as well.
do you mean you declare explicitly
Dim cn as sqlconnection = new sqlconnection(connectionstring)
after sqlclientpermission
From your example,
Try
Dim permission As New
SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
permission.Assert()
-----
' Dim _myConn as sqlconnection = new sqlconnection(connectionstring)
(adding this solved your issue?)
----
'Use the MS data access app block to get the data reader
dr = _myDBAccess.ExecuteReader(_myConn,
CommandType.StoredProcedure, "Trader_GetTransByAssetID", New
SqlParameter("@.AssetID", nAssetID
Appreciate your help on this.
--
kvs
"fassmann@.gmail.com" wrote:
> OK, got it fixed.
> You have to explicitly open the connection everywhere you use this. So,
> we've got it added to every function/procedure within our assembly.
> So not oly are we using the ExecuteReader, but we're opening the
> connection each time as well.
>|||Ok, here is what I tried but still getting error. Could you please tell me
what am I doing wrong or missing?
Dim permission As New
SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
permission.Assert()
'open connection explicitly
Dim cn As SqlConnection = New SqlConnection(cns)
cn.Open()
Try
Return SqlDataAccess.ExecuteDataSet(cn, sql, arp)
Finally
cn.Dispose()
End Try
--
Thanks.
kvs
"kvs" wrote:
> I have a similar problem, could you please give some example on this. when
> you say
> > So not oly are we using the ExecuteReader, but we're opening the
> > connection each time as well.
> do you mean you declare explicitly
> Dim cn as sqlconnection = new sqlconnection(connectionstring)
> after sqlclientpermission
> From your example,
> Try
> Dim permission As New
> SqlClientPermission(Security.Permissions.PermissionState.Unrestricted)
> permission.Assert()
> -----
> ' Dim _myConn as sqlconnection = new sqlconnection(connectionstring)
> (adding this solved your issue?)
> ----
> 'Use the MS data access app block to get the data reader
> dr = _myDBAccess.ExecuteReader(_myConn,
> CommandType.StoredProcedure, "Trader_GetTransByAssetID", New
> SqlParameter("@.AssetID", nAssetID
>
> Appreciate your help on this.
> --
> kvs
>
> "fassmann@.gmail.com" wrote:
> > OK, got it fixed.
> >
> > You have to explicitly open the connection everywhere you use this. So,
> > we've got it added to every function/procedure within our assembly.
> >
> > So not oly are we using the ExecuteReader, but we're opening the
> > connection each time as well.
> >
> >sql

Product Level Insufficient

I am trying to run dtexec on some DTSC packages I created using SSIS. I keep getting the error "The product level is insufficient for the component". Anybody know what this means?

Thanks,

Michael D. Fox

If you do a search of this forum you will find many threads that discuss the usual cause and the usual solution to this problem.

Thanks,

Matt

|||

I did look through the other replies. But I DO have SSIS installed. I am using the evaluation version of SQL Server 2005. Does this have any bearing on it?

Thanks,

Michael D. Fox

|||

Did you actually install SSIS or just the tools (i.e. do you have the SSIS service installed on the box you are trying to run the package on)? If the service is not installed then you will get this message. Which edition is the evaluation version (std, enterprise, etc) and which component is giving you the message (different components are available in different editions)?

Thanks,

Matt

|||

Thanks Matt,

I just went back and reinstalled, checking everything in sight, and it works now.

Thanks again,

Michael D. Fox

sql

producing a date time report in SQL/DTS

I have need to produce a report (excel sheet actually) from SQL that
would run each Tuesday and each Friday of every w.
What needs to be on the Tuesday report is everything that came in from
the Friday midnight time, until the Monday midnight time. The friday
report(sheet) would have everything that came in from Midnight Monday
evening, thru midnight Thursday. The next Tuesday report would have
everything from Midnight Thursday thru midnight Monday, and so on.
I know I can schedule the jobs to run on that interval, but how do I
selectively pick the records I want? There is a datetime field on the
table, "submit date" and what I am basically doing is a select * from
tbl_literature_orders where date > x.
Any ideas?
Thanks,
BC"Blasting Cap" schrieb:
> I have need to produce a report (excel sheet actually) from SQL that
> would run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from
> the Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BC
Try it with two jobs, one for Tuesday, one for Friday, and set the execution
time of the job appropriately. Search for your data by difference:
select * from MyTable where datefield > dateadd(d, -3, GetDate()) -- Friday
and
select * from MyTable where datefield > dateadd(d, -4, GetDate()) -- Tuesday|||Just use the DATEPART() or DATENAME() functions to determine which day it
is. Then use DATEADD() with the appropriate days to get the from and to
that you need for your WHERE clause.
Andrew J. Kelly SQL MVP
"Blasting Cap" <goober@.christian.net> wrote in message
news:eiUP1w9CGHA.4080@.TK2MSFTNGP09.phx.gbl...
>I have need to produce a report (excel sheet actually) from SQL that would
>run each Tuesday and each Friday of every w.
> What needs to be on the Tuesday report is everything that came in from the
> Friday midnight time, until the Monday midnight time. The friday
> report(sheet) would have everything that came in from Midnight Monday
> evening, thru midnight Thursday. The next Tuesday report would have
> everything from Midnight Thursday thru midnight Monday, and so on.
> I know I can schedule the jobs to run on that interval, but how do I
> selectively pick the records I want? There is a datetime field on the
> table, "submit date" and what I am basically doing is a select * from
> tbl_literature_orders where date > x.
> Any ideas?
> Thanks,
> BCsql

Processor Utilization

Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
Thanks
Hi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:

> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks

Processor Utilization

Hi
I am running SQL Server 2000 on a 2 processor system (Windows 2003
Standard). I regularily run large queries. When I run the queries the CPU
utilization is consitantly around 11%.
It appears as if something is limiting SQL Server to 11% CPU per
process/query. I have verified that its not I/O bound.
The Processor properties are set as follows:
- All 2 processors are checked in Processor Control
- "Maximum worker threads" is set to 255
- "Boost SQL Server priority on Windows" is not checked
- "Use Windows NT fibres" is checked
- "Use all available processors" is checked
Why is the CPU not fully utilized? What does "Boost SQL Server priority on
Windows" do?
Any assistance would be greatly appreciated
ThanksHi
You may want to check the boost priority option and not use fibres (as
fibres can cause other problems). You could also try using the MAXDOP hint to
only use one processor.
Monitor context switches to see if there is an excessive number before
considering using fibres. Ken Hendersons book The Gurus Guide to SQL Server
Architecture and Internals ISBN 0201700476 has a good section on this.
John
"Macisu" wrote:
> Hi
> I am running SQL Server 2000 on a 2 processor system (Windows 2003
> Standard). I regularily run large queries. When I run the queries the CPU
> utilization is consitantly around 11%.
> It appears as if something is limiting SQL Server to 11% CPU per
> process/query. I have verified that its not I/O bound.
> The Processor properties are set as follows:
> - All 2 processors are checked in Processor Control
> - "Maximum worker threads" is set to 255
> - "Boost SQL Server priority on Windows" is not checked
> - "Use Windows NT fibres" is checked
> - "Use all available processors" is checked
> Why is the CPU not fully utilized? What does "Boost SQL Server priority on
> Windows" do?
> Any assistance would be greatly appreciated
> Thanks