Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
Message posted via http://www.sqlmonster.com
Robert Richards via SQLMonster.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com
|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via SQLMonster.com wrote:
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Friday, March 30, 2012
Profiler and alerts
Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via droptable.com wrote:
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
(say 30 seconds or greater) occurs in Profiler?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via droptable.com wrote:
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
Profiler and alerts
Is there a way to set up an alert in SQL Server when a long running query
(say 30 seconds or greater) occurs in Profiler?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via SQLMonster.com wrote:
>> Is there a way to set up an alert in SQL Server when a long running
>> query (say 30 seconds or greater) occurs in Profiler?
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
(say 30 seconds or greater) occurs in Profiler?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> Is there a way to set up an alert in SQL Server when a long running
> query (say 30 seconds or greater) occurs in Profiler?
Profiler is just a client application. What you can do is write a
server-side trace that monitors SQL:BatchCompleted and RPC:Completed
events with a Duration >= 30,000. Have the trace write to a local file
on the SQL Server box. You can then write a recurring SQL job that
pauses the trace, looks in the trace file for any rows (using
fn_trace_gettable) and if it finds them, inserts the rows into a real
table and sends an alert using xp_sendmail or xp_logevent and
sp_add_alert. Then start up the trace again.
But there might be an easier way... We'll see what the rest of the ng
recommends.
David Gugick
Imceda Software
www.imceda.com|||This is presently a bit of a pain to do, but the new
Microsoft.SqlServer.Management.Trace library in SQL05 lets you grab sql
trace events on the fly. It's backward compatible to SQL2K too, so you could
experiment with it now for SQL2K. I'm not recommending you stick this into
production (as SQL05's still in beta) but it will at least give you a view
of what's coming up & perhaps help you make a decision on how much time you
want to spend on this presently given the emerging technology.
Here's a vb demo of how it works.
Imports Microsoft.SqlServer.Management.Trace
Dim ts as TraceServer = new TraceServer
Dim c as ConnectionInfoBase = new SqlConnectionInfo("localhost")
c.SqlConnectionInfo.UseIntegratedSecurity = true
ts.InitializeAsReader(c, "c:\Standard.tdf")
do while reader.Read = true
'read trace info off the TraceServer's
'(reader) GetName() & GetValue() methods
loop
ts.Close
ts.Dispose
hth
Regards,
Greg Linwood
SQL Server MVPp
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O3J0rOiDFHA.4052@.TK2MSFTNGP15.phx.gbl...
> Robert Richards via SQLMonster.com wrote:
>> Is there a way to set up an alert in SQL Server when a long running
>> query (say 30 seconds or greater) occurs in Profiler?
> Profiler is just a client application. What you can do is write a
> server-side trace that monitors SQL:BatchCompleted and RPC:Completed
> events with a Duration >= 30,000. Have the trace write to a local file on
> the SQL Server box. You can then write a recurring SQL job that pauses the
> trace, looks in the trace file for any rows (using fn_trace_gettable) and
> if it finds them, inserts the rows into a real table and sends an alert
> using xp_sendmail or xp_logevent and sp_add_alert. Then start up the trace
> again.
> But there might be an easier way... We'll see what the rest of the ng
> recommends.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
Wednesday, March 28, 2012
Profiler
We usually monitor our production server via profiler from
our desktop. We had an issue over the weekend and would
usually have the profiler results to review and
troubleshoot. However, over the weekend our desktop's were
rebooted and all info lost.
So we were thinking of running the profiler directly on
the production server but have read/heard of issue's
related to this and a potential performance hit on the
server. When running it on my own desktop I sometimes run
into memory issues.
What are the pro's and con's of running profiler directly
on the server?
What about running the stored proc's in query analyzer
from our desktop compared to running the profiler directly?
Would it be better to use the stored procedures instead of
profiler?
Any input would be greatly appreciated.
ThanksI don't run Profiler on the server directly because of the way I store the
Profiler data. In order to really use the data I find that I need to store
the profile stuff in a table. I don't like the idea of writing to the
production server so I have a seperate server where I run Profiler and log
the data in an SQL table.
Christian Smith
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||If your server's not under heavy load, this is probably ok, but if the
server is under heavy load, using the Profiler on the server might hurt
performance because it consumes CPU, memory to collect data, display on the
screen etc.
If you are dealing with a server under heavy load, there is a better
alternative:
SQL Profiler is a client side tool that collects data from SQL Trace (server
side component) and displays it on the GUI. SQL Trace can be run on it's own
on the server without the overhead of Profiler by using it's stored procs -
sp_trace_create (& cousins). SQL 7 has a different set of procs for this,
but I'll assume you're on SQL 2K for now unless you say otherwise..
When you use SQL Trace, you can set the output to go to a .trc file on the
server and open the .trc file later with Profiler, save to a table, run
queries whatever..
To make life super-easy when doing this, you can even define your SQL Trace
in SQL Profiler, then use SQL Profiler's scripting tool to script the
sp_trace_create commands. You can even schedule starting & stopping SQL
Trace via these stored procs from the SQL Agent.
For more info, read up on sp_trace_create in SQL Books Online..
Then again, if you're just doing ad-hoc tracing on a server that's not under
heavy load, you're probably ok running small-ish SQL Profilers directly on
the server, but do be sensitive that it will add load & might affect users..
HTH
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||you can script the sp_trace calls directly per greg's
description or just run profiler on the server,
under almost no circumstances should profiler be saving
directly to a table on the production server
set it to save to a file, and place the file on lightly
loaded disk
also, an accumalation rate by profiler of <1000 line
items /sec should not be a significant load on the server,
but to be safe, try to keep it under 200/sec
>--Original Message--
>We usually monitor our production server via profiler
from
>our desktop. We had an issue over the weekend and would
>usually have the profiler results to review and
>troubleshoot. However, over the weekend our desktop's
were
>rebooted and all info lost.
>So we were thinking of running the profiler directly on
>the production server but have read/heard of issue's
>related to this and a potential performance hit on the
>server. When running it on my own desktop I sometimes run
>into memory issues.
>What are the pro's and con's of running profiler directly
>on the server?
>What about running the stored proc's in query analyzer
>from our desktop compared to running the profiler
directly?
>Would it be better to use the stored procedures instead
of
>profiler?
>Any input would be greatly appreciated.
>Thanks
>.
>
our desktop. We had an issue over the weekend and would
usually have the profiler results to review and
troubleshoot. However, over the weekend our desktop's were
rebooted and all info lost.
So we were thinking of running the profiler directly on
the production server but have read/heard of issue's
related to this and a potential performance hit on the
server. When running it on my own desktop I sometimes run
into memory issues.
What are the pro's and con's of running profiler directly
on the server?
What about running the stored proc's in query analyzer
from our desktop compared to running the profiler directly?
Would it be better to use the stored procedures instead of
profiler?
Any input would be greatly appreciated.
ThanksI don't run Profiler on the server directly because of the way I store the
Profiler data. In order to really use the data I find that I need to store
the profile stuff in a table. I don't like the idea of writing to the
production server so I have a seperate server where I run Profiler and log
the data in an SQL table.
Christian Smith
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||If your server's not under heavy load, this is probably ok, but if the
server is under heavy load, using the Profiler on the server might hurt
performance because it consumes CPU, memory to collect data, display on the
screen etc.
If you are dealing with a server under heavy load, there is a better
alternative:
SQL Profiler is a client side tool that collects data from SQL Trace (server
side component) and displays it on the GUI. SQL Trace can be run on it's own
on the server without the overhead of Profiler by using it's stored procs -
sp_trace_create (& cousins). SQL 7 has a different set of procs for this,
but I'll assume you're on SQL 2K for now unless you say otherwise..
When you use SQL Trace, you can set the output to go to a .trc file on the
server and open the .trc file later with Profiler, save to a table, run
queries whatever..
To make life super-easy when doing this, you can even define your SQL Trace
in SQL Profiler, then use SQL Profiler's scripting tool to script the
sp_trace_create commands. You can even schedule starting & stopping SQL
Trace via these stored procs from the SQL Agent.
For more info, read up on sp_trace_create in SQL Books Online..
Then again, if you're just doing ad-hoc tracing on a server that's not under
heavy load, you're probably ok running small-ish SQL Profilers directly on
the server, but do be sensitive that it will add load & might affect users..
HTH
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||you can script the sp_trace calls directly per greg's
description or just run profiler on the server,
under almost no circumstances should profiler be saving
directly to a table on the production server
set it to save to a file, and place the file on lightly
loaded disk
also, an accumalation rate by profiler of <1000 line
items /sec should not be a significant load on the server,
but to be safe, try to keep it under 200/sec
>--Original Message--
>We usually monitor our production server via profiler
from
>our desktop. We had an issue over the weekend and would
>usually have the profiler results to review and
>troubleshoot. However, over the weekend our desktop's
were
>rebooted and all info lost.
>So we were thinking of running the profiler directly on
>the production server but have read/heard of issue's
>related to this and a potential performance hit on the
>server. When running it on my own desktop I sometimes run
>into memory issues.
>What are the pro's and con's of running profiler directly
>on the server?
>What about running the stored proc's in query analyzer
>from our desktop compared to running the profiler
directly?
>Would it be better to use the stored procedures instead
of
>profiler?
>Any input would be greatly appreciated.
>Thanks
>.
>
Profiler
We usually monitor our production server via profiler from
our desktop. We had an issue over the weekend and would
usually have the profiler results to review and
troubleshoot. However, over the weekend our desktop's were
rebooted and all info lost.
So we were thinking of running the profiler directly on
the production server but have read/heard of issue's
related to this and a potential performance hit on the
server. When running it on my own desktop I sometimes run
into memory issues.
What are the pro's and con's of running profiler directly
on the server?
What about running the stored proc's in query analyzer
from our desktop compared to running the profiler directly?
Would it be better to use the stored procedures instead of
profiler?
Any input would be greatly appreciated.
ThanksI don't run Profiler on the server directly because of the way I store the
Profiler data. In order to really use the data I find that I need to store
the profile stuff in a table. I don't like the idea of writing to the
production server so I have a seperate server where I run Profiler and log
the data in an SQL table.
Christian Smith
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||If your server's not under heavy load, this is probably ok, but if the
server is under heavy load, using the Profiler on the server might hurt
performance because it consumes CPU, memory to collect data, display on the
screen etc.
If you are dealing with a server under heavy load, there is a better
alternative:
SQL Profiler is a client side tool that collects data from SQL Trace (server
side component) and displays it on the GUI. SQL Trace can be run on it's own
on the server without the overhead of Profiler by using it's stored procs -
sp_trace_create (& cousins). SQL 7 has a different set of procs for this,
but I'll assume you're on SQL 2K for now unless you say otherwise..
When you use SQL Trace, you can set the output to go to a .trc file on the
server and open the .trc file later with Profiler, save to a table, run
queries whatever..
To make life super-easy when doing this, you can even define your SQL Trace
in SQL Profiler, then use SQL Profiler's scripting tool to script the
sp_trace_create commands. You can even schedule starting & stopping SQL
Trace via these stored procs from the SQL Agent.
For more info, read up on sp_trace_create in SQL Books Online..
Then again, if you're just doing ad-hoc tracing on a server that's not under
heavy load, you're probably ok running small-ish SQL Profilers directly on
the server, but do be sensitive that it will add load & might affect users..
HTH
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>
our desktop. We had an issue over the weekend and would
usually have the profiler results to review and
troubleshoot. However, over the weekend our desktop's were
rebooted and all info lost.
So we were thinking of running the profiler directly on
the production server but have read/heard of issue's
related to this and a potential performance hit on the
server. When running it on my own desktop I sometimes run
into memory issues.
What are the pro's and con's of running profiler directly
on the server?
What about running the stored proc's in query analyzer
from our desktop compared to running the profiler directly?
Would it be better to use the stored procedures instead of
profiler?
Any input would be greatly appreciated.
ThanksI don't run Profiler on the server directly because of the way I store the
Profiler data. In order to really use the data I find that I need to store
the profile stuff in a table. I don't like the idea of writing to the
production server so I have a seperate server where I run Profiler and log
the data in an SQL table.
Christian Smith
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||If your server's not under heavy load, this is probably ok, but if the
server is under heavy load, using the Profiler on the server might hurt
performance because it consumes CPU, memory to collect data, display on the
screen etc.
If you are dealing with a server under heavy load, there is a better
alternative:
SQL Profiler is a client side tool that collects data from SQL Trace (server
side component) and displays it on the GUI. SQL Trace can be run on it's own
on the server without the overhead of Profiler by using it's stored procs -
sp_trace_create (& cousins). SQL 7 has a different set of procs for this,
but I'll assume you're on SQL 2K for now unless you say otherwise..
When you use SQL Trace, you can set the output to go to a .trc file on the
server and open the .trc file later with Profiler, save to a table, run
queries whatever..
To make life super-easy when doing this, you can even define your SQL Trace
in SQL Profiler, then use SQL Profiler's scripting tool to script the
sp_trace_create commands. You can even schedule starting & stopping SQL
Trace via these stored procs from the SQL Agent.
For more info, read up on sp_trace_create in SQL Books Online..
Then again, if you're just doing ad-hoc tracing on a server that's not under
heavy load, you're probably ok running small-ish SQL Profilers directly on
the server, but do be sensitive that it will add load & might affect users..
HTH
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>
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(PermissionState.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
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(PermissionState.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
Tuesday, March 20, 2012
Processing Queries via Email
In SQL 2000, we processed queries and return results to user. In SQL 2005, we
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.
Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.
Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.
Processing Queries via Email
In SQL 2000, we processed queries and return results to user. In SQL 2005, w
e
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibl
y
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005,
we
> have not been successful in doing so. I do not see any way using DB Mail t
o
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail unti
l
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incomi
ng
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I woul
d
> be very grateful.
e
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibl
y
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005,
we
> have not been successful in doing so. I do not see any way using DB Mail t
o
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail unti
l
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incomi
ng
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I woul
d
> be very grateful.
Processing Queries via Email
In SQL 2000, we processed queries and return results to user. In SQL 2005, we
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.
have not been successful in doing so. I do not see any way using DB Mail to
process incoming mail.
We have set up SQL Mail and are able to send mail with no problem. We had
planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
we could migrate to whatever process would provide the same functionality.
So, two questions:
1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
queries and returning results to user?
2. What provides the equivalent functionality in SQL 2005? Is it done via
Reporting Service?
If someone can point me in the right direction for either question, I would
be very grateful.Hi Roger
AFAIK there is no method of reading a mailbox with database mail, therefore
you would have to retain your current process of reading the mailbox or
possibly use the CLR to do this (for example see
http://support.microsoft.com/kb/813349 ). You could use a different (possibly
more reliable) method of delivery such as a web service instead which can be
hosted by the database server.
John
"RogerT" wrote:
> In SQL 2000, we processed queries and return results to user. In SQL 2005, we
> have not been successful in doing so. I do not see any way using DB Mail to
> process incoming mail.
> We have set up SQL Mail and are able to send mail with no problem. We had
> planned to upgrade servers from SQL 2000 to SQL 2005 and use SQL Mail until
> we could migrate to whatever process would provide the same functionality.
> So, two questions:
> 1. Has anyone been successful using SQL Mail on SQL 2005 to process incoming
> queries and returning results to user?
> 2. What provides the equivalent functionality in SQL 2005? Is it done via
> Reporting Service?
> If someone can point me in the right direction for either question, I would
> be very grateful.
Processing of cube created via DSO in AS 2005 fails
We are testing an application originaly written for AS 2000 with AS
2005.
This application uses DSO from VB to create and process cubes. I have
gotten
over the major hurdles of getting DSO to work with AS 2005. However,
upon
creating of the cubes, I get errors processing it (even from
Management
Studio). Here's the code (abridged to remove error handling) that
we use to
create a cube:
Dim dsoCube As DSO.MDStore
Set
dsoCube = m_dsoDatabase.MDStores.AddNew(cubeName)
' set the cube's
description
dsoCube.Description = sCubeDesc
' set the cube's
datasource
dsoCube.DataSources.Add
m_dsoDatabase.DataSources(mvarDBName)
Dim dboStr As String
dboStr = sLQuote & "dbo" & sRQuote & "."
' set the source
table (fact table) for the cube
dsoCube.SourceTable = dboStr &
sLQuote & FACT_TABLE & sRQuote
dsoCube.EstimatedRows =
164558
' specify access permissions to the cube by adding roles to the
cube
Dim dsoCubeRole As DSO.role
Set dsoCubeRole =
dsoCube.Roles.AddNew("BSA Role")
dsoCubeRole.SetPermissions "Access",
"RW"
' create cube's measures
'
Dim dsoMeasure As
DSO.Measure
Set dsoCube = m_dsoDatabase.MDStores.Item(cubeName)
Set dsoMeasure = dsoCube.Measures.AddNew("BaseAmt")
' set the
measure's source column, data type and the formatting
dsoMeasure.SourceColumn = dsoCube.SourceTable & "." &
_
sLQuote & "baseamt" & sRQuote
dsoMeasure.SourceColumnType = adDouble
dsoMeasure.FormatString =
"Currency"
' this measure will be aggregated by summation
dsoMeasure.AggregateFunction = aggSum
Set dsoMeasure =
dsoCube.Measures.AddNew("Count")
' set the measure's source column,
data type and the formatting
dsoMeasure.SourceColumn =
dsoCube.SourceTable & "." & _
sLQuote
& "baseamt" & sRQuote
dsoMeasure.SourceColumnType =
adInteger
' this measure will be aggregated by summation
dsoMeasure.AggregateFunction = aggCount
Set dsoMeasure =
dsoCube.Measures.AddNew("TranNo")
' set the measure's source column,
data type and the formatting
dsoMeasure.SourceColumn =
dsoCube.SourceTable & "." & _
sLQuote
& "TranNo" & sRQuote
dsoMeasure.SourceColumnType =
adInteger
' this measure will be aggregated by summation
dsoMeasure.AggregateFunction = aggMax
' Create Calculated
members
Dim dsoCalculatedMember As DSO.Command
Set
dsoCalculatedMember = dsoCube.Commands.AddNew("CustAvgAmt")
' set the
command type
dsoCalculatedMember.CommandType = cmdCreateMember
'
set the MDX statement that defines the calculated member
dsoCalculatedMember.Statement = _
"Create Member Measures.[CustAvgAmt] As
" & _
"'avg({LastPeriods(3 ,
[bookdate].&[2003].&[4].&[10])},
measures.[baseamt])'"
Set dsoCalculatedMember = dsoCube.Commands.AddNew("CustAvgCnt")
' set the
command type
dsoCalculatedMember.CommandType = cmdCreateMember
' set the MDX statement that defines the calculated member
dsoCalculatedMember.Statement = _
"Create Member Measures.[CustAvgCnt] As
" & _
"'avg({LastPeriods(3 ,
[bookdate].&[2003].&[4].&[10])},
measures.[Count])'"
'
add the BookDate dimension
Dim dsoBookDateDim As DSO.Dimension
Set
dsoBookDateDim = dsoCube.Dimensions.AddNew("BookDate")
' add the
RecvPay dimension
Dim dsoRecvPayDim As DSO.Dimension
Set
dsoRecvPayDim = dsoCube.Dimensions.AddNew("RecvPay")
' get the list
of all tables used in this cube
' this list includes the fact table and
the dimension tables
' Note: Make sure that you do not repeat the same
table name twice.
dsoCube.FromClause = dsoCube.SourceTable & ", "
&
dsoBookDateDim.FromClause
dsoCube.joinClause =
dsoCube.joinClause & _
"(" & _
dboStr
& sLQuote & FACT_TABLE & sRQuote & "." & sLQuote
&
"bookdate" & sRQuote & _
" = " &
_
dboStr & sLQuote & "TblBookDate" & sRQuote &
"." & sLQuote &
"bookdate" & sRQuote & _
")"
dsoCube.SourceTableFilter = mvarFactTableFilter
' save the
cube definition in the metadata repository
dsoCube.Update
This
code runs fine, but during processing of the selected cube from
Management
Studio, the following error is displayed:
The Measures cube either does
not exist or has not been processed
There is no Measures cube, nor was
there ever. This does not happen when
processing cubes in AS 2000 or even in
AS 2005 if the cube was migrated from
AS 2000.
Can anybody help with
this?
Thanks in advance,
Boris Zakharin, MCAD
Metavante Risk and
Compliance
Hi Boris,
the problem is in the following line, which creates a calculated member. In AS2005 (as well as in AS2000) the member created in the cube scope should be prefixed with the cube name or CurrentCube, since Measures is the first name detected AS2005 treats it as a cube name. Pehaps AS2000 did not detected this error during the prosessing, but the calc member was disabled pr may be I'm wrong and CurrentCube was optional, but anyway, if you add CurrentCube to all calculated members it should start to process.
"Create Member Measures.[CustAvgAmt] As " & _
"'avg({LastPeriods(3 , [bookdate].&[2003].&[4].&[10])},
measures.[baseamt])'"
Processing Mining Models & Structures via SSIS
Still new to DM and SSIS...anyand all help is greatly appreciated!
In SSIS they say that you can use the Analysis Services Processing Task to process a mining model/mining structure, however, I do not see where you can give it a relational table to work off of. I know that I can use a data flow to do this but I wanted to go a different route if I could to process my models as I don't really necessarily need the data flow as what I am tring to do is pretty simple.
That brings me to a more general question, what is the best method for training your models using SSIS? I am building a new model everytime the package runs using some variables and the DDL task, running a query on it, and destroying it at the end of the package but I am having logistical problems training it outside of the data flow. I tried using the DM Query task but it requires that you output a result set and I am not sure if I can use it to create and process models.
I would think that they would just give you a DMX task similar to the SQL task but that does not seem to be the case. Also, when I browse the AS objects via the processing task I can only see the mining structures and not the mining models.
Am I just missing something here?
Cheers,
Dan Meyers
If you just want to process the mining models/structure, you should use the Analysis Services processing task. If you wnat more flexibility, you can use the Analysis Services Execute DDL task (you can do pretty much anything there). There is a feature called "out of line bindings" that you can specify if you want to proecess against different data than the model was bound to in the DSV - I'm not sure of the documentation level on this feature though.
You are right that you can't use the DM Query task for the reason you stated - you can use the DDL task as in this tip http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1443.aspx to process using DMX, or execute any other non-rowset returning DMX statement.
On the point of what happens during the data flow execution, the model/structure is only created at design time. The processing happens at run time. This means that if you wanted to run this package against another server, you would first have to create the model on that server. The easiest method may be to use SSMS to script the structure to create and then use the DDL task again
HTH
-Jamie
|||Interesting...I did not realize that you could use the DDL task to execute DMX statments. Makes perfect sense since it all gets broken down to XMLA anyway. This is exactly the answer I was looking for. You are like the Yoda of SQL DM. Once again, thanks for the help.
-Dan Meyers
|||Jamie,
I have another more question on this...Do I have to build a new DSV for my mining model or can I just use the same one that I am using for my OLAP cubes?
|||You can use the same DSV - the DSV is simply a collection of tables/named queries that don't necessarily have to be relatedMonday, March 12, 2012
Processing Cubes via a SQL Server 2000 DTS
Hi,
I have a DTS which processes a set of AS 2000 cubes via a set of AS Processing Tasks.
I want to know how I can accurately trap and report errors once a cube fails to process?
Thanks in advance
Jon Derbyshire
The logging of processing errors in DTS ( SQL Server 2000 ) is bit limited.
You should try and look at migrating your application to SQL Server 2005 instead. I know it is bit a far fetched suggestion to migrate your whole solution to SQL Server 2005 just because of a single problem with accurately tracking processing errors. But the problem is: your will find increasingly harder to implement any new functionality with old version of the product. You will find it harder to find answers to your questions in the community that has moved to new ( different) technologies. At some point you should look at developing new solution using SQL Server 2005.
Hope that helps.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Saturday, February 25, 2012
process analysis server objects from web via ASP.NET + C#
Hi!
We are running several Analysis Services Cubes on a Database Server (SQL 2005) machine.
Another machine is our web server (IIS, ASPX) which displays cube
content and allows drill down to several levels. Everything works fine
and we are looking forward to optimizing the whole data management
process. At the moment we try to develop DTS packeges (SQL 2005).
Is it possible to initiate a cube processing (without DTS in the background) from eg an administration section in the web page?
TIA and kind regards from Vienna
JohnnyR
You can use AMO (Microsoft.AnalysisServices.dll from "%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies"). If IIS impersonates the current user, then AMO code from the administration aspx page would also connect to AS2005 as that user. The user needs read permission for the Database and read+process permissions for the Cube.
Sample code:
using Microsoft.AnalysisServices;
...
Server server = new Server();
server.Connect("localhost");
try
{
server.Databases["the ID of the database"].Cubes["the ID of the cube"].Process();
}
finally
{
server.Disconnect();
}
You can also get processing notifications with AMO, to display progress on the administration page; more details here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75148&SiteID=1
Adrian Dumitrascu
|||Hi,
I have created an analysis services project using SQL Server 2005, in which I have included a cube, dimension, data source and data view. Now using the Analysis services Browser tab of cube I can see the Pivot table exactly the way I want where I can drag & drop table fields as per my requirements. But I am struggling to display the same cube over the Web. Can any please tell me that how I can publish the contents of Cube i.e. Pivot table on a web application so that end user can use this. I want the same functionality, which I can see in the Browser tab of Cube in AS2005 i.e. user should have freedom to drag & drop the fields exactly like in Excel Pivot table.
Any help would be highly appreciated.
Thanks in advance.
|||The control used in the browser is the Office Web Components 11.0 PivotTable control.
You can use this control in your asp.net app by referencing this control in the COM tab.
As to how to use it.. documentation is sparse, so I'd just do a search on the web.
Alternatively, you could host thepivot table in an Excel spread sheet and publish that as a web page... use Excel 2007 for the best experience... again, do a search on Excel services.
Otherwise, there are true .NEt controls, such as those produced by RadarSoft
process analysis server objects from web via ASP.NET + C#
Hi!
We are running several Analysis Services Cubes on a Database Server (SQL 2005) machine.
Another machine is our web server (IIS, ASPX) which displays cube
content and allows drill down to several levels. Everything works fine
and we are looking forward to optimizing the whole data management
process. At the moment we try to develop DTS packeges (SQL 2005).
Is it possible to initiate a cube processing (without DTS in the background) from eg an administration section in the web page?
TIA and kind regards from Vienna
JohnnyR
You can use AMO (Microsoft.AnalysisServices.dll from "%ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies"). If IIS impersonates the current user, then AMO code from the administration aspx page would also connect to AS2005 as that user. The user needs read permission for the Database and read+process permissions for the Cube.
Sample code:
using Microsoft.AnalysisServices;
...
Server server = new Server();
server.Connect("localhost");
try
{
server.Databases["the ID of the database"].Cubes["the ID of the cube"].Process();
}
finally
{
server.Disconnect();
}
You can also get processing notifications with AMO, to display progress on the administration page; more details here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=75148&SiteID=1
Adrian Dumitrascu
|||Hi,
I have created an analysis services project using SQL Server 2005, in which I have included a cube, dimension, data source and data view. Now using the Analysis services Browser tab of cube I can see the Pivot table exactly the way I want where I can drag & drop table fields as per my requirements. But I am struggling to display the same cube over the Web. Can any please tell me that how I can publish the contents of Cube i.e. Pivot table on a web application so that end user can use this. I want the same functionality, which I can see in the Browser tab of Cube in AS2005 i.e. user should have freedom to drag & drop the fields exactly like in Excel Pivot table.
Any help would be highly appreciated.
Thanks in advance.
|||The control used in the browser is the Office Web Components 11.0 PivotTable control.
You can use this control in your asp.net app by referencing this control in the COM tab.
As to how to use it.. documentation is sparse, so I'd just do a search on the web.
Alternatively, you could host thepivot table in an Excel spread sheet and publish that as a web page... use Excel 2007 for the best experience... again, do a search on Excel services.
Otherwise, there are true .NEt controls, such as those produced by RadarSoft
Monday, February 20, 2012
Procedure x expects parameter @y which was not supplied.
I'm trying to update a member's record via stored procedure. The procedure is thus:
*********************************
CREATE PROCEDURE upd_MemberProfile(
@.MemberID VarChar(10),
@.FirstName VarChar(20),
@.LastName VarChar(20),
@.Address VarChar(50),
@.City VarChar(40),
@.StateID Char(2),
@.ZipCode Char(5),
@.Email VarChar(30),
@.PayPalSubscrID VarChar(22)
) AS
UPDATE Members
SET FirstName = @.FirstName, LastName = @.LastName, Address = @.Address, City = @.City, StateID = @.StateID, ZipCode = @.ZipCode, Email = @.Email, PayPalSubscrID = @.PayPalSubscrID
WHERE MemberID = @.MemberID
GO
*****************************
The call from the Webpage is like so:
*****************************
...
Dim Subscr_id As String = "123ABC-HDTV"
Try
cmd.CommandText = dbo() & "upd_MemberProfile"
cmd.CommandType = CommandType.StoredProcedure
With cmd.Parameters
.Add("@.MemberID", MemberID)
.Add("@.FirstName", FirstName)
.Add("@.LastName", LastName)
.Add("@.Address", Address)
.Add("@.City", City)
.Add("@.StateID", StateID)
.Add("@.ZipCode", ZipCode)
.Add("@.Email", Payer_email)
.Add("@.PayPalSubscrID", Subscr_id)
End With
If cnn.State.Open Then
cnn.Close()
End If
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
Catch ex As Exception
'Notify Admin of the Problem
MailUsTheOrder("ERROR on upd_MemberProfile: " & ex.Message)
'Close the SQL Connection
If cnn.State.Open Then
cnn.Close()
End If
End Try
******************************
This was working two weeks ago. I have changed NOTHING and now I get the error:
Procedure 'upd_MemberProfile' expects parameter '@.PayPalSubscrID', which was not supplied.
Any Ideas?yup. you might not be setting that parameter in another method or something. Do you have any overloads or anything? step through the application, and see if the right method is being called.|||Hi,
is your Subscr_id 's value nothing?
Subscribe to:
Posts (Atom)