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
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Friday, March 30, 2012
profiler
Hi
I need to gather user activities on our SQL2K server on
weekdays between 8-5 using the profiler. Is there a way to
schedule this to start and stop at these times?
Thanks,
Mark
You can create and start traces using TSQL scripts. You get a handle back with which you can stop
and close a trace. Easiest way to get started is to define the trace in Profiler, and from the
profiler menu save the trace definition as a TSQL script.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"mark" <anonymous@.discussions.microsoft.com> wrote in message
news:067d01c51055$dd11e810$a501280a@.phx.gbl...
> Hi
> I need to gather user activities on our SQL2K server on
> weekdays between 8-5 using the profiler. Is there a way to
> schedule this to start and stop at these times?
> Thanks,
> Mark
I need to gather user activities on our SQL2K server on
weekdays between 8-5 using the profiler. Is there a way to
schedule this to start and stop at these times?
Thanks,
Mark
You can create and start traces using TSQL scripts. You get a handle back with which you can stop
and close a trace. Easiest way to get started is to define the trace in Profiler, and from the
profiler menu save the trace definition as a TSQL script.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"mark" <anonymous@.discussions.microsoft.com> wrote in message
news:067d01c51055$dd11e810$a501280a@.phx.gbl...
> Hi
> I need to gather user activities on our SQL2K server on
> weekdays between 8-5 using the profiler. Is there a way to
> schedule this to start and stop at these times?
> Thanks,
> Mark
Wednesday, March 28, 2012
profiler
Hi
I need to gather user activities on our SQL2K server on
weekdays between 8-5 using the profiler. Is there a way to
schedule this to start and stop at these times?
Thanks,
MarkYou can create and start traces using TSQL scripts. You get a handle back with which you can stop
and close a trace. Easiest way to get started is to define the trace in Profiler, and from the
profiler menu save the trace definition as a TSQL script.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"mark" <anonymous@.discussions.microsoft.com> wrote in message
news:067d01c51055$dd11e810$a501280a@.phx.gbl...
> Hi
> I need to gather user activities on our SQL2K server on
> weekdays between 8-5 using the profiler. Is there a way to
> schedule this to start and stop at these times?
> Thanks,
> Mark
I need to gather user activities on our SQL2K server on
weekdays between 8-5 using the profiler. Is there a way to
schedule this to start and stop at these times?
Thanks,
MarkYou can create and start traces using TSQL scripts. You get a handle back with which you can stop
and close a trace. Easiest way to get started is to define the trace in Profiler, and from the
profiler menu save the trace definition as a TSQL script.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"mark" <anonymous@.discussions.microsoft.com> wrote in message
news:067d01c51055$dd11e810$a501280a@.phx.gbl...
> Hi
> I need to gather user activities on our SQL2K server on
> weekdays between 8-5 using the profiler. Is there a way to
> schedule this to start and stop at these times?
> Thanks,
> Mark
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
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
Hi
I need to gather user activities on our SQL2K server on
weekdays between 8-5 using the profiler. Is there a way to
schedule this to start and stop at these times?
Thanks,
MarkYou can create and start traces using TSQL scripts. You get a handle back wi
th which you can stop
and close a trace. Easiest way to get started is to define the trace in Prof
iler, and from the
profiler menu save the trace definition as a TSQL script.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"mark" <anonymous@.discussions.microsoft.com> wrote in message
news:067d01c51055$dd11e810$a501280a@.phx.gbl...
> Hi
> I need to gather user activities on our SQL2K server on
> weekdays between 8-5 using the profiler. Is there a way to
> schedule this to start and stop at these times?
> Thanks,
> Marksql
I need to gather user activities on our SQL2K server on
weekdays between 8-5 using the profiler. Is there a way to
schedule this to start and stop at these times?
Thanks,
MarkYou can create and start traces using TSQL scripts. You get a handle back wi
th which you can stop
and close a trace. Easiest way to get started is to define the trace in Prof
iler, and from the
profiler menu save the trace definition as a TSQL script.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"mark" <anonymous@.discussions.microsoft.com> wrote in message
news:067d01c51055$dd11e810$a501280a@.phx.gbl...
> Hi
> I need to gather user activities on our SQL2K server on
> weekdays between 8-5 using the profiler. Is there a way to
> schedule this to start and stop at these times?
> Thanks,
> Marksql
Profile, SQLDataSource and GridView
Hello.
When I create a user at the ASP.NET database, I need to insert more fields than the defaults, and I do it like this:
Dim customProfile As ProfileCommon = ProfileCommon.Create(CreateUserWizard1.UserName, True)
customProfile.telephone =(CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("TelephoneText"),TextBox)).Text
(telephone example)
Those data are inserted at the DB at the aspnet_Profile table, in the fields PropertyNames & PropertyValuesString, but they are saved together (see image above)
I want to separate those properties in the GridView as long as each property appears in a column, is it possible?
Thank you very much, i'm expecting your answers.
If you want an additional column you could change existing TSQL
SELECT A, B, C FROM TABLE
to
SELECT A, B, C, '' AS D FROM TABLE
|||
I want to separate these properties in columns, not to create a new empty column.
GridView shows me this:
and I need this:
The text is the following:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" CellPadding="4" CssClass="gridview" DataSourceID="SqlDataSource1"
ForeColor="#333333" GridLines="None">
<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:BoundField DataField="UserName" HeaderText="ID" SortExpression="UserName" />
<asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
<asp:BoundField DataField="PropertyNames" HeaderText="Propiedades" SortExpression="PropertyNames" />
<asp:BoundField DataField="PropertyValuesString" HeaderText="Valores" SortExpression="PropertyValuesString" />
</Columns>
<RowStyle BackColor="#EFF3FB" />
<EditRowStyle BackColor="#2461BF" />
<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\webfax.mdf;Integrated Security=True;User Instance=True"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT aspnet_Membership.Email, aspnet_Profile.PropertyNames, aspnet_Profile.PropertyValuesString, aspnet_Users.UserName FROM aspnet_Membership INNER JOIN aspnet_Profile ON aspnet_Membership.UserId = aspnet_Profile.UserId INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId">
</asp:SqlDataSource>
ok, both texts are:
empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:
and
UBuLa cartera45678que yo tengo
these are for the first case, for the second case:
empresa:S:0:3:nombre:S:3:8:telefono:S:11:5:apellidos:S:16:13
and
453el huevo76534de la gallina
thanks
Presumably you need
empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:
broken down to
empresa:S:0:3:
nombre:S:3:10:
telefono:s:13:5:
apellidos:S:18:12:
using the delimiters I have marked in bold
|||Based on the presumption in my previous post
DECLARE @.VAR VARCHAR(100)
DECLARE @.EMPRESSA VARCHAR(100)
DECLARE @.NOMBRE VARCHAR(100)
DECLARE @.telefono VARCHAR(100)
DECLARE @.apellidos VARCHAR(100)
SET @.VAR = 'empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:'
PRINT @.VAR
--empresa:S:0:3:
--nombre:S:3:10:
--telefono:s:13:5:
--apellidos:S:18:12:
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('nombre:', @.VAR)
PRINT @.IMARK1
IF @.IMARK1 > 0 BEGIN
SET @.EMPRESSA = SUBSTRING(@.VAR, 1, @.IMARK1-1)
PRINT '@.EMPRESSA [' + @.EMPRESSA + ']'
END
SET @.IMARK1 = CHARINDEX('apellidos:', @.VAR)
IF @.IMARK1 > 0 BEGIN
SET @.apellidos = SUBSTRING(@.VAR, @.IMARK1,DATALENGTH(@.VAR))
PRINT '@.apellidos [' + @.apellidos + ']'
END
SET @.IMARK1 = CHARINDEX('apellidos:', @.VAR)
SET @.IMARK2 = CHARINDEX('telefono:', @.VAR)
IF @.IMARK1 > 0 AND @.IMARK2 > 0 AND @.IMARK1 > @.IMARK2 BEGIN
SET @.telefono = SUBSTRING(@.VAR, @.IMARK2,@.IMARK1 - @.IMARK2)
PRINT '@.telefono [' + @.telefono + ']'
END
SET @.IMARK1 = CHARINDEX('telefono:', @.VAR)
SET @.IMARK2 = CHARINDEX('nombre:', @.VAR)
IF @.IMARK1 > 0 AND @.IMARK2 > 0 AND @.IMARK1 > @.IMARK2 BEGIN
SET @.nombre = SUBSTRING(@.VAR, @.IMARK2,@.IMARK1 - @.IMARK2)
PRINT '@.nombre [' + @.nombre + ']'
END
gives
empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:
15
@.EMPRESSA [empresa:S:0:3:]
@.apellidos [apellidos:S:18:12:]
@.telefono [telefono:s:13:5:]
@.nombre [nombre:S:3:10:]
The TSQL will need to be wrapped in in some scalar functions
|||The functions
CREATE FUNCTION dbo.fnGetApellidos (@.VAR VARCHAR(250))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.RETURN VARCHAR(50)
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('apellidos:', @.VAR)
IF @.IMARK1 > 0 BEGIN
SET @.RETURN = SUBSTRING(@.VAR, @.IMARK1,DATALENGTH(@.VAR))
--PRINT '@.apellidos [' + @.RETURN + ']'
END
RETURN @.RETURN
END
GO
CREATE FUNCTION dbo.fnGetEMPRESSA (@.VAR VARCHAR(250))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.RETURN VARCHAR(50)
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('nombre:', @.VAR)
--PRINT @.IMARK1
IF @.IMARK1 > 0 BEGIN
SET @.RETURN = SUBSTRING(@.VAR, 1, @.IMARK1-1)
--PRINT '@.EMPRESSA [' + @.RETURN + ']'
END
RETURN @.RETURN
END
GO
CREATE FUNCTION dbo.fnGetTelefono (@.VAR VARCHAR(250))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.RETURN VARCHAR(50)
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('apellidos:', @.VAR)
SET @.IMARK2 = CHARINDEX('telefono:', @.VAR)
IF @.IMARK1 > 0 AND @.IMARK2 > 0 AND @.IMARK1 > @.IMARK2 BEGIN
SET @.RETURN = SUBSTRING(@.VAR, @.IMARK2,@.IMARK1 - @.IMARK2)
--PRINT '@.telefono [' + @.RETURN + ']'
END
RETURN @.RETURN
END
GO
CREATE FUNCTION dbo.fnGetNombre (@.VAR VARCHAR(250))
RETURNS VARCHAR(50) AS
BEGIN
DECLARE @.RETURN VARCHAR(50)
DECLARE @.IMARK1 INT
DECLARE @.IMARK2 INT
SET @.IMARK1 = CHARINDEX('telefono:', @.VAR)
SET @.IMARK2 = CHARINDEX('nombre:', @.VAR)
IF @.IMARK1 > 0 AND @.IMARK2 > 0 AND @.IMARK1 > @.IMARK2 BEGIN
SET @.RETURN = SUBSTRING(@.VAR, @.IMARK2,@.IMARK1 - @.IMARK2)
--PRINT '@.nombre [' + @.RETURN + ']'
END
RETURN @.RETURN
END
GO
When run by
DECLARE @.VAR VARCHAR(100)
SET @.VAR = 'empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:'
PRINT @.VAR
PRINT dbo.fnGetEmpressa(@.VAR)
PRINT dbo.fnGetNombre(@.VAR)
PRINT dbo.fnGetTelefono(@.VAR)
PRINT dbo.fnGetApellidos(@.VAR)
give
empresa:S:0:3:nombre:S:3:10:telefono:s:13:5:apellidos:S:18:12:
empresa:S:0:3:
nombre:S:3:10:
telefono:s:13:5:
apellidos:S:18:12:
Thus instead of
SELECT VAR FROM TABLENAME
you can have
SELECT dbo.fnGetEmpressa(VAR) ASEmpressa,dbo.fnGetNombre(VAR) ASNombre,
dbo.fnGetTelefono(VAR) ASTelefono,dbo.fnGetApellidos(VAR) ASApellidos FROM TABLENAME
this giving you the required split on columns
sqlProfile Trace: how to tell which database when filtering on user
Let's say there are two databases, DB#1 and DB#2, and a user JOE. DB#1 and
DB#2 have stored procedures identically named (.e.g. "uspGetOrders") with
the same signature. Is it possible when running a trace that filters on
user like JOE, to determine which database the procedure belongs to which
JOE happens to be executing? How can I tell which it is? When I double
click on the trace line to view the details, the sp name is displayed
without a db or owner prefix. And if I filter on database name, I see this:
exec uspGetOrders @.p1 no matter which database I happen to choose. The
trace doesn't seem to tell me where the procedure "originates" so to speak.
I'm trying to figure out why the procedure is returning no data when it is
moved to DB#2 and points back to tables in DB1. The procedure worked fine
when it was in DB#1. The stored procedure refers to the table as
DB1.dbo.Orders and JOE has been granted execute privileges (via a role) on
DB2.dbo.uspGetOrders. JOE had no problem working with the procedure when
it was in the same database as the table. JOE belongs to a role that has
select privileges on Orders.
The only other thing that has changed is the Initial Catalog when JOE
connects. Now it is DB2.
Thanks!
TimoIf a user is in DB#1 and what you see executed is
uspGetOrders, it's executing the stored procedure in DB#1.
It would have to be qualified by the database name to
execute the stored procedure in DB#2.
If you are on SQL Server 2000 and if you are on SP3, then
the issue with the stored procedure could be due to not
having cross database ownership chaining enabled. If you are
running under this configuration and have updated your books
online, you can find more information in books online under
the topic:
Cross DB Ownership Chaining
-Sue
On Mon, 12 Apr 2004 12:25:28 -0400, "Timo"
<timo@.anonymous.com> wrote:
>Let's say there are two databases, DB#1 and DB#2, and a user JOE. DB#1 and
>DB#2 have stored procedures identically named (.e.g. "uspGetOrders") with
>the same signature. Is it possible when running a trace that filters on
>user like JOE, to determine which database the procedure belongs to which
>JOE happens to be executing? How can I tell which it is? When I double
>click on the trace line to view the details, the sp name is displayed
>without a db or owner prefix. And if I filter on database name, I see this
:
>exec uspGetOrders @.p1 no matter which database I happen to choose. The
>trace doesn't seem to tell me where the procedure "originates" so to speak.
>I'm trying to figure out why the procedure is returning no data when it is
>moved to DB#2 and points back to tables in DB1. The procedure worked fine
>when it was in DB#1. The stored procedure refers to the table as
>DB1.dbo.Orders and JOE has been granted execute privileges (via a role) on
>DB2.dbo.uspGetOrders. JOE had no problem working with the procedure when
>it was in the same database as the table. JOE belongs to a role that has
>select privileges on Orders.
>The only other thing that has changed is the Initial Catalog when JOE
>connects. Now it is DB2.
>Thanks!
>Timo
>
>
>|||Sue,
Thanks for the reply. It doesn't appear to be Cross DB Ownership chaining;
same issue after this:
EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
EXEC sp_dboption 'DB1', 'db chaining', 'ON'
EXEC sp_dboption 'DB2', 'db chaining', 'ON'
Trace shows the procedure being invoked; the IN parameter is there, but all
the OUT parameters are null. When I've encountered similar symptoms in Trace
before, it was because the user lacked execute privileges on the procedure.
But JOE has these privileges by virtue of membership in a db role.
Any other way to troubleshoot this scenario using Trace?
Thanks again.
Timo
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:vcil70hj0j37239l9ds5tevbtfotc6n506@.
4ax.com...
> If a user is in DB#1 and what you see executed is
> uspGetOrders, it's executing the stored procedure in DB#1.
> It would have to be qualified by the database name to
> execute the stored procedure in DB#2.
> If you are on SQL Server 2000 and if you are on SP3, then
> the issue with the stored procedure could be due to not
> having cross database ownership chaining enabled. If you are
> running under this configuration and have updated your books
> online, you can find more information in books online under
> the topic:
> Cross DB Ownership Chaining
> -Sue
> On Mon, 12 Apr 2004 12:25:28 -0400, "Timo"
> <timo@.anonymous.com> wrote:
>
and
which
this:
speak.
is
on
when
>|||In Profiler, you'd want to select exceptions from the Errors
and Warnings event class. That will give you any errors
being raised. You should also make sure you are capturing
the completion of the stored procedure, not just the
starting event.
Did you check the owners of the objects (the stored
procedure and the underlying tables) and databases involved?
The databases need to be owned by the same login. The
objects need to have the same object owner.
-Sue
On Mon, 12 Apr 2004 13:40:39 -0400, "Timo"
<timo@.anonymous.com> wrote:
>Sue,
>Thanks for the reply. It doesn't appear to be Cross DB Ownership chaining;
>same issue after this:
>EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
>EXEC sp_dboption 'DB1', 'db chaining', 'ON'
>EXEC sp_dboption 'DB2', 'db chaining', 'ON'
>Trace shows the procedure being invoked; the IN parameter is there, but all
>the OUT parameters are null. When I've encountered similar symptoms in Trac
e
>before, it was because the user lacked execute privileges on the procedure.
>But JOE has these privileges by virtue of membership in a db role.
>Any other way to troubleshoot this scenario using Trace?
>Thanks again.
>Timo
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:vcil70hj0j37239l9ds5tevbtfotc6n506@.
4ax.com...
>and
>which
>this:
>speak.
>is
>on
>when
>|||To add to Sue's response, if your objects are owned by 'dbo', the database
owners need to be the same in order to maintain an unbroken ownership chain
for dbo-owned objects. If needed, you can use sp_changedbowner to ensure
the databases are owned by the same login:
USE DB1
EXEC sp_changedbowner 'MyOwnerLogin'
USE DB2
EXEC sp_changedbowner 'MyOwnerLogin'
Users will need access to both databases but permissions on indirectly
referenced objects are not needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Timo" <timo@.anonymous.com> wrote in message
news:%23t2AiVLIEHA.580@.TK2MSFTNGP12.phx.gbl...
> Sue,
> Thanks for the reply. It doesn't appear to be Cross DB Ownership
chaining;
> same issue after this:
> EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
> EXEC sp_dboption 'DB1', 'db chaining', 'ON'
> EXEC sp_dboption 'DB2', 'db chaining', 'ON'
> Trace shows the procedure being invoked; the IN parameter is there, but
all
> the OUT parameters are null. When I've encountered similar symptoms in
Trace
> before, it was because the user lacked execute privileges on the
procedure.
> But JOE has these privileges by virtue of membership in a db role.
> Any other way to troubleshoot this scenario using Trace?
> Thanks again.
> Timo
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:vcil70hj0j37239l9ds5tevbtfotc6n506@.
4ax.com...
> and
with
on
> which
> this:
The
> speak.
> is
fine
> on
> when
has
>|||Thank you Dan for the dbchangeowner tip, and Sue for the Errors and Warnings
event class tip. I will use them to advantage. In Trace, I was not seeing
the completion of the sp, only the starting event, and so was unable to
determine what database the client was pointing at. After having the
developer re-check the connection string in the client-side code, we found
that the wrong database was being specified as the initial catalog.
Timo
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ukvyQdVIEHA.2972@.TK2MSFTNGP12.phx.gbl...
> To add to Sue's response, if your objects are owned by 'dbo', the database
> owners need to be the same in order to maintain an unbroken ownership
chain
> for dbo-owned objects. If needed, you can use sp_changedbowner to ensure
> the databases are owned by the same login:
> USE DB1
> EXEC sp_changedbowner 'MyOwnerLogin'
> USE DB2
> EXEC sp_changedbowner 'MyOwnerLogin'
> Users will need access to both databases but permissions on indirectly
> referenced objects are not needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Timo" <timo@.anonymous.com> wrote in message
> news:%23t2AiVLIEHA.580@.TK2MSFTNGP12.phx.gbl...
> chaining;
> all
> Trace
> procedure.
DB#1
> with
> on
double
see
> The
it
> fine
role)
> has
>
DB#2 have stored procedures identically named (.e.g. "uspGetOrders") with
the same signature. Is it possible when running a trace that filters on
user like JOE, to determine which database the procedure belongs to which
JOE happens to be executing? How can I tell which it is? When I double
click on the trace line to view the details, the sp name is displayed
without a db or owner prefix. And if I filter on database name, I see this:
exec uspGetOrders @.p1 no matter which database I happen to choose. The
trace doesn't seem to tell me where the procedure "originates" so to speak.
I'm trying to figure out why the procedure is returning no data when it is
moved to DB#2 and points back to tables in DB1. The procedure worked fine
when it was in DB#1. The stored procedure refers to the table as
DB1.dbo.Orders and JOE has been granted execute privileges (via a role) on
DB2.dbo.uspGetOrders. JOE had no problem working with the procedure when
it was in the same database as the table. JOE belongs to a role that has
select privileges on Orders.
The only other thing that has changed is the Initial Catalog when JOE
connects. Now it is DB2.
Thanks!
TimoIf a user is in DB#1 and what you see executed is
uspGetOrders, it's executing the stored procedure in DB#1.
It would have to be qualified by the database name to
execute the stored procedure in DB#2.
If you are on SQL Server 2000 and if you are on SP3, then
the issue with the stored procedure could be due to not
having cross database ownership chaining enabled. If you are
running under this configuration and have updated your books
online, you can find more information in books online under
the topic:
Cross DB Ownership Chaining
-Sue
On Mon, 12 Apr 2004 12:25:28 -0400, "Timo"
<timo@.anonymous.com> wrote:
>Let's say there are two databases, DB#1 and DB#2, and a user JOE. DB#1 and
>DB#2 have stored procedures identically named (.e.g. "uspGetOrders") with
>the same signature. Is it possible when running a trace that filters on
>user like JOE, to determine which database the procedure belongs to which
>JOE happens to be executing? How can I tell which it is? When I double
>click on the trace line to view the details, the sp name is displayed
>without a db or owner prefix. And if I filter on database name, I see this
:
>exec uspGetOrders @.p1 no matter which database I happen to choose. The
>trace doesn't seem to tell me where the procedure "originates" so to speak.
>I'm trying to figure out why the procedure is returning no data when it is
>moved to DB#2 and points back to tables in DB1. The procedure worked fine
>when it was in DB#1. The stored procedure refers to the table as
>DB1.dbo.Orders and JOE has been granted execute privileges (via a role) on
>DB2.dbo.uspGetOrders. JOE had no problem working with the procedure when
>it was in the same database as the table. JOE belongs to a role that has
>select privileges on Orders.
>The only other thing that has changed is the Initial Catalog when JOE
>connects. Now it is DB2.
>Thanks!
>Timo
>
>
>|||Sue,
Thanks for the reply. It doesn't appear to be Cross DB Ownership chaining;
same issue after this:
EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
EXEC sp_dboption 'DB1', 'db chaining', 'ON'
EXEC sp_dboption 'DB2', 'db chaining', 'ON'
Trace shows the procedure being invoked; the IN parameter is there, but all
the OUT parameters are null. When I've encountered similar symptoms in Trace
before, it was because the user lacked execute privileges on the procedure.
But JOE has these privileges by virtue of membership in a db role.
Any other way to troubleshoot this scenario using Trace?
Thanks again.
Timo
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:vcil70hj0j37239l9ds5tevbtfotc6n506@.
4ax.com...
> If a user is in DB#1 and what you see executed is
> uspGetOrders, it's executing the stored procedure in DB#1.
> It would have to be qualified by the database name to
> execute the stored procedure in DB#2.
> If you are on SQL Server 2000 and if you are on SP3, then
> the issue with the stored procedure could be due to not
> having cross database ownership chaining enabled. If you are
> running under this configuration and have updated your books
> online, you can find more information in books online under
> the topic:
> Cross DB Ownership Chaining
> -Sue
> On Mon, 12 Apr 2004 12:25:28 -0400, "Timo"
> <timo@.anonymous.com> wrote:
>
and
which
this:
speak.
is
on
when
>|||In Profiler, you'd want to select exceptions from the Errors
and Warnings event class. That will give you any errors
being raised. You should also make sure you are capturing
the completion of the stored procedure, not just the
starting event.
Did you check the owners of the objects (the stored
procedure and the underlying tables) and databases involved?
The databases need to be owned by the same login. The
objects need to have the same object owner.
-Sue
On Mon, 12 Apr 2004 13:40:39 -0400, "Timo"
<timo@.anonymous.com> wrote:
>Sue,
>Thanks for the reply. It doesn't appear to be Cross DB Ownership chaining;
>same issue after this:
>EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
>EXEC sp_dboption 'DB1', 'db chaining', 'ON'
>EXEC sp_dboption 'DB2', 'db chaining', 'ON'
>Trace shows the procedure being invoked; the IN parameter is there, but all
>the OUT parameters are null. When I've encountered similar symptoms in Trac
e
>before, it was because the user lacked execute privileges on the procedure.
>But JOE has these privileges by virtue of membership in a db role.
>Any other way to troubleshoot this scenario using Trace?
>Thanks again.
>Timo
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:vcil70hj0j37239l9ds5tevbtfotc6n506@.
4ax.com...
>and
>which
>this:
>speak.
>is
>on
>when
>|||To add to Sue's response, if your objects are owned by 'dbo', the database
owners need to be the same in order to maintain an unbroken ownership chain
for dbo-owned objects. If needed, you can use sp_changedbowner to ensure
the databases are owned by the same login:
USE DB1
EXEC sp_changedbowner 'MyOwnerLogin'
USE DB2
EXEC sp_changedbowner 'MyOwnerLogin'
Users will need access to both databases but permissions on indirectly
referenced objects are not needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Timo" <timo@.anonymous.com> wrote in message
news:%23t2AiVLIEHA.580@.TK2MSFTNGP12.phx.gbl...
> Sue,
> Thanks for the reply. It doesn't appear to be Cross DB Ownership
chaining;
> same issue after this:
> EXEC sp_configure 'Cross DB Ownership Chaining', '0'; RECONFIGURE
> EXEC sp_dboption 'DB1', 'db chaining', 'ON'
> EXEC sp_dboption 'DB2', 'db chaining', 'ON'
> Trace shows the procedure being invoked; the IN parameter is there, but
all
> the OUT parameters are null. When I've encountered similar symptoms in
Trace
> before, it was because the user lacked execute privileges on the
procedure.
> But JOE has these privileges by virtue of membership in a db role.
> Any other way to troubleshoot this scenario using Trace?
> Thanks again.
> Timo
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:vcil70hj0j37239l9ds5tevbtfotc6n506@.
4ax.com...
> and
with
on
> which
> this:
The
> speak.
> is
fine
> on
> when
has
>|||Thank you Dan for the dbchangeowner tip, and Sue for the Errors and Warnings
event class tip. I will use them to advantage. In Trace, I was not seeing
the completion of the sp, only the starting event, and so was unable to
determine what database the client was pointing at. After having the
developer re-check the connection string in the client-side code, we found
that the wrong database was being specified as the initial catalog.
Timo
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:ukvyQdVIEHA.2972@.TK2MSFTNGP12.phx.gbl...
> To add to Sue's response, if your objects are owned by 'dbo', the database
> owners need to be the same in order to maintain an unbroken ownership
chain
> for dbo-owned objects. If needed, you can use sp_changedbowner to ensure
> the databases are owned by the same login:
> USE DB1
> EXEC sp_changedbowner 'MyOwnerLogin'
> USE DB2
> EXEC sp_changedbowner 'MyOwnerLogin'
> Users will need access to both databases but permissions on indirectly
> referenced objects are not needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Timo" <timo@.anonymous.com> wrote in message
> news:%23t2AiVLIEHA.580@.TK2MSFTNGP12.phx.gbl...
> chaining;
> all
> Trace
> procedure.
DB#1
> with
> on
double
see
> The
it
> fine
role)
> has
>
Profile GUID
I am using a SqlDatasource and need to set a SelectParamter to the ProviderUserKey (The GUID of the user when Profiles are enabled)
Can anyone tell me whether it is possible and How?
I am currently using the session state to store it in and then using the session=... to get the value into the parameter.
Is there a direct way of passing this value into a SelectParameter when using a SqlDataSource?
Thanks in advance.
Hi,
As far as I know, there isn't any direct way to use the user GUID as parameter.
The only way I can think is to use custom code to query the GUID and put it in the querystring or session.
HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!
|||Thanks|||Thanks for taking the time.
I am already using the session state.(querystring option would raise serious security issues).
Just in case anyone else is interested, you can also use the inserting/updating events on the SqlDatasource to assign the GUID to the specific parameter.
I just thought there might be a way of doing it directly.
Friday, March 23, 2012
ProClarity Dashboard server user name and password modification
Hi, all experts here,
Does anyone know how can we change the password for a particular user of ProClarity dashboard server? I changed it from the ProClarity dashboard server database's 'users' table, but it does not work. Any one know how can we do that.
Thanks and I am looking forward to hearing from you.
With kind regards,
Yours sincerely,
Hello Helen. Dashboard Server is integrated with Active Directory. You map users into groups in AD and map that groups in Dashboard Server.
Normally you do not handle accounts and passwords in Dashboard Server with the exception of the dashboard administrator.
HTH
Thomas Ivarsson
|||Hi, Thomas,
Thanks for your kind advices.
Yes, I logged on as the administrator of the Server. What happened was the I logged on as administrator of the dashboard viewer successfully. But it did not let me log on to the dashboard studio with exactly the same administrator account. What I did was going to the database of the dashboard server and changed the password for this administrator account. But it did not let me log on with the reset password again. I am still stuck in it.
Thanks for your help anyway.
With kind regards,
Yours sincerely,
|||OK. You can always reinstall Dashboard Server once again and get the option to change the password.
I suspect that the integration with Active Directory is not done correctly. I think you will need the help of an it-support specialist. The installation of this product is a little bit tricky.
Changing password directly in a table will usually not work.
Edit: It is possible to change the password for the PDS administrator by changing authentication back to SQL Server /forms authentication and use the default admin id and password. You will have to configure some xml files to do that and make an authentication change in Internet Information Server(IIS).
This is in the documentation.
HTH
Thomas Ivarsson
|||Hi, Thomas,
Thanks for your help.
With kind regards,
Yours sincerely,
ProClarity Dashboard server user name and password modification
Hi, all experts here,
Does anyone know how can we change the password for a particular user of ProClarity dashboard server? I changed it from the ProClarity dashboard server database's 'users' table, but it does not work. Any one know how can we do that.
Thanks and I am looking forward to hearing from you.
With kind regards,
Yours sincerely,
Hello Helen. Dashboard Server is integrated with Active Directory. You map users into groups in AD and map that groups in Dashboard Server.
Normally you do not handle accounts and passwords in Dashboard Server with the exception of the dashboard administrator.
HTH
Thomas Ivarsson
|||Hi, Thomas,
Thanks for your kind advices.
Yes, I logged on as the administrator of the Server. What happened was the I logged on as administrator of the dashboard viewer successfully. But it did not let me log on to the dashboard studio with exactly the same administrator account. What I did was going to the database of the dashboard server and changed the password for this administrator account. But it did not let me log on with the reset password again. I am still stuck in it.
Thanks for your help anyway.
With kind regards,
Yours sincerely,
|||OK. You can always reinstall Dashboard Server once again and get the option to change the password.
I suspect that the integration with Active Directory is not done correctly. I think you will need the help of an it-support specialist. The installation of this product is a little bit tricky.
Changing password directly in a table will usually not work.
Edit: It is possible to change the password for the PDS administrator by changing authentication back to SQL Server /forms authentication and use the default admin id and password. You will have to configure some xml files to do that and make an authentication change in Internet Information Server(IIS).
This is in the documentation.
HTH
Thomas Ivarsson
|||Hi, Thomas,
Thanks for your help.
With kind regards,
Yours sincerely,
ProClarity Dashboard server user name and password modification
Hi, all experts here,
Does anyone know how can we change the password for a particular user of ProClarity dashboard server? I changed it from the ProClarity dashboard server database's 'users' table, but it does not work. Any one know how can we do that.
Thanks and I am looking forward to hearing from you.
With kind regards,
Yours sincerely,
Hello Helen. Dashboard Server is integrated with Active Directory. You map users into groups in AD and map that groups in Dashboard Server.
Normally you do not handle accounts and passwords in Dashboard Server with the exception of the dashboard administrator.
HTH
Thomas Ivarsson
|||Hi, Thomas,
Thanks for your kind advices.
Yes, I logged on as the administrator of the Server. What happened was the I logged on as administrator of the dashboard viewer successfully. But it did not let me log on to the dashboard studio with exactly the same administrator account. What I did was going to the database of the dashboard server and changed the password for this administrator account. But it did not let me log on with the reset password again. I am still stuck in it.
Thanks for your help anyway.
With kind regards,
Yours sincerely,
|||OK. You can always reinstall Dashboard Server once again and get the option to change the password.
I suspect that the integration with Active Directory is not done correctly. I think you will need the help of an it-support specialist. The installation of this product is a little bit tricky.
Changing password directly in a table will usually not work.
Edit: It is possible to change the password for the PDS administrator by changing authentication back to SQL Server /forms authentication and use the default admin id and password. You will have to configure some xml files to do that and make an authentication change in Internet Information Server(IIS).
This is in the documentation.
HTH
Thomas Ivarsson
|||Hi, Thomas,
Thanks for your help.
With kind regards,
Yours sincerely,
Wednesday, March 21, 2012
processor maxed out block other process...
Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.
There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting.
Of course indexing well is almost always the best path to follow.
|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!
> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.
There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting.
Of course indexing well is almost always the best path to follow.
|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!
> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe
processor maxed out block other process...
Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a écrit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!
> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time... :)
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a écrit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!
> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time... :)
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe
processor maxed out block other process...
Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure
will show you the setting.
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!
> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophesql
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure
will show you the setting.
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!
> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophesql
Tuesday, March 20, 2012
Processing User Activity Table
Hello,
I have an application that will be logging to a SQL Server 2000
database user user activity from several Windows 2003 terminal
servers. This information will be retrieved by monitoring the
Security logs of these servers (this part I know how to accomplish
already).
A table in the database, tblLogEntries, will contain the following
fields:
- ID = autoincrementing int
- LogTime = Date/Time the user activity was recorded in the security
log
- Username = User's login ID that the activity was recorded with
- Type = int, referencing a lookup table with the values of Logon,
Logoff, and possible other future items
- Server = The name of the server the activity was recorded on.
The only question I have is, can you offer a way to process the total
user login time during a given range using T-SQL.
For Example...
Given the table data:
ID LogTime Username Type Server
1 10-10-2003 8:30:00 Tom Logon SERVER-A
2 10-10-2003 8:45:00 Sarah Logon SERVER-A
3 10-10-2003 16:45:00 Tom Logoff SERVER-A
4 10-10-2003 17:00:00 Sarah Logoff SERVER-A
5 10-11-2003 8:30:00 Tom Logon SERVER-A
6 10-11-2003 8:45:00 Sarah Logon SERVER-A
7 10-11-2003 16:30:00 Sarah Logoff SERVER-A
8 10-11-2003 17:15:00 Tom Logoff SERVER-A
How would you receive the output:
User Logon Total Time for SERVER-A
Tom 17.0 hrs
Sarah 16.0 hrs
I know I can handle this type of processing on my ASP.NET front-end,
but I'm curious as to how easily it can be done by the database,
itself.
Thanks in advance for your assistance.Do:
SELECT UserName,
SUM( DATEDIFF( hour, Login, COALESCE( Logoff, Login ) ) )
FROM ( SELECT t1.Username, t1.LogTime,
( SELECT MIN( t2.LogTime )
FROM tbl t2
WHERE t2.Server = t1.Server
AND t2.UserName = t1.Username
AND t2.Type = 'LogOff'
AND t2.LogTime > t1.LogTime )
FROM tbl t1
WHERE t1.Type = 'Logon'
AND t1.Server = 'SERVER-A' ) D ( UserName, Login, Logoff )
GROUP BY UserName ;
--
- Anith
( Please reply to newsgroups only )|||Thanks for the reply.. the query looks good, but I'm having a little
trouble following it, and, as a result, cannot get it to work.
Would you mind explaining it a bit or point me to a reference for this
type of processing?
I'm not sure what that "D" operator is for, and I'll read up on the
COALESCE in BOL.
------------
http://members.tripod.com/kcourville0/
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||D is an alias used for a derived table used in the query; when you use a
subquery construct directly in the FROM clause of an SQL statement with an
alias, in t-SQL, it is called a derived table. Pl. refer to SQL Server Books
Online for syntax and more details on this construct.
The logic is simple:
1. Retrieve the list of users with their login times and the subsequent
logout time (this is achieved using a subquery with MIN function). You can
run the derived table by itself to further understand how it is evaluated.
2. With the data in the derived table, the outer query, get the time
difference between the login time and the logout times and then find the
total using the SUM function.
--
- Anith
( Please reply to newsgroups only )|||Cool... I'll have to learn more of derived tables.
Thanks again for your response.
------------
http://members.tripod.com/kcourville0/
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
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.
Monday, March 12, 2012
processing has been canceled by the user ?
hi all,
sometimes i am seeing a error , when i am trying to gegerate a report
please help .........
---
Reporting Services Error
----
Report processing has been canceled by the user. (rsProcessingAborted) Get
Online HelpOn Jan 18, 8:53 am, "jelzi" <je...@.discussions.microsoft.com> wrote:
> hi all,
> sometimes i am seeing a error , when i am trying to gegerate a report
> please help .........
> ---
> Reporting Services Error
> ----
> Report processing has been canceled by the user. (rsProcessingAborted) Get
> Online Help
You will most likely want to increase your timeout value in the site
settings. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||hi Enrique,
where is this setting ?
regards
jelzi
>> hi all,
>> sometimes i am seeing a error , when i am trying to gegerate a report
>> please help .........
>> ---
>> Reporting Services Error
>> ----
>> Report processing has been canceled by the user. (rsProcessingAborted)
>> Get
>> Online Help
>
> You will most likely want to increase your timeout value in the site
> settings. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
sometimes i am seeing a error , when i am trying to gegerate a report
please help .........
---
Reporting Services Error
----
Report processing has been canceled by the user. (rsProcessingAborted) Get
Online HelpOn Jan 18, 8:53 am, "jelzi" <je...@.discussions.microsoft.com> wrote:
> hi all,
> sometimes i am seeing a error , when i am trying to gegerate a report
> please help .........
> ---
> Reporting Services Error
> ----
> Report processing has been canceled by the user. (rsProcessingAborted) Get
> Online Help
You will most likely want to increase your timeout value in the site
settings. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||hi Enrique,
where is this setting ?
regards
jelzi
>> hi all,
>> sometimes i am seeing a error , when i am trying to gegerate a report
>> please help .........
>> ---
>> Reporting Services Error
>> ----
>> Report processing has been canceled by the user. (rsProcessingAborted)
>> Get
>> Online Help
>
> You will most likely want to increase your timeout value in the site
> settings. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
Wednesday, March 7, 2012
Process in SQL Server 2005
Hi, I am developing an application with vb.net and sql server 2005 express edition. If a user logs in (through the application), I want to query the computer host from sql server 2005 in which the user logs on. Is there any special table or procedure that I can use to achieve this? Thanks.
hi,
ghozan wrote:
Hi, I am developing an application with vb.net and sql server 2005 express edition. If a user logs in (through the application), I want to query the computer host from sql server 2005 in which the user logs on. Is there any special table or procedure that I can use to achieve this? Thanks.
can you please expand on your requirement? I did really not understand what you are looking for..
regards
|||check system sp ... sp_who or
select *from sys.dm_exec_sessions
Madhu
Subscribe to:
Posts (Atom)