Profile / Stored Procedure

Hi,

I have created a Stored Procedure, under Stored procedures section under Enterprise Manager on SQL server 2000.

Could anybody tell me, how and what are the steps to follow to TRACE the procedure , using SQL PROFILER ?

Please advice me !

NicolFrom Enterprise Manager:
-- choose Tools
-- SQL Profiler
-- File
-- New
-- Trace
-- select/enter connection informatin as appropriate (I was able to accept the defaults)
-- press OK
-- click the General tab of the Trace Properties window
-- from the "Template name:" dropdown choose SQLProfilerTSQL_SPs
-- choose RUN
-- do whatever it is you need to do to run your Stored Procedure (from within Query Analyzer or ASP.NET)
-- go back to Profiler and click the red square "stop selected trace" icon
-- review the results

Terri|||Thanks Tmorton,

I got started with checking SP using SQL profiler.. :)

Now I found, I think, I have problem with coding in VB.NET or problem in SP.

Even though, I have given valid Username/Password, RETURNVALUE is always showing zero .

Stored procedure
-------
CREATE PROCEDURE test
(@.username varchar(100),
@.userpwd varchar(200)
)
AS
set nocount on
return (select count(*) as s from employer
WHERE user_name like '%@.username%' and
password ='@.userpwd')

GO

VB.NET Code using SP
--------
CmdCoInfo = New SqlCommand("test", ConVM)
CmdCoInfo.CommandType = CommandType.StoredProcedure
CmdCoInfo.Parameters.Add("@.username", Trim(StrUser))
CmdCoInfo.Parameters.Add("@.userpwd", Trim(StrPass))
Dim paramOut As SqlParameter
paramOut = CmdCoInfo.Parameters.Add("Returnvalue", SqlDbType.Int)
paramOut.Direction = ParameterDirection.ReturnValue
paramOut.Size = 40
' Retrieve the record that matches the username/password
ConVM.Open()
CmdCoInfo.ExecuteNonQuery()

Dim retrr As String
If Not IsDBNull(CmdCoInfo.Parameters("Returnvalue").Value) Then
retrr = (CmdCoInfo.Parameters("Returnvalue").Value)
Else
retrr = "Not know"
End If
Response.Write(retrr)

--------------

Is there problem with SP or VB.net code ?

Please advice !|||The problem is in your stored procedure.

Instead of this:

return (select count(*) as s from employer
WHERE user_name like '%@.username%' and
password ='@.userpwd')
GO

This would be more correct:

DECLARE @.RecordCount integer
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.RecordCount
GO

HOWEVER, ReturnValue is meant to signal back to the calling program whether or not the stored procedure was successful. So you are misusing it to return a COUNT. You should use an Output parameter instead. Add an additional parameter called RecordCount with ParameterDirection of Output. Add the Output parameter to your stored procedure, which will look like this:

CREATE PROCEDURE test
(@.username varchar(100),
@.userpwd varchar(200) ,
@.RecordCount int OUTPUT
)
AS
set nocount on
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.@.ERROR
GO

I haven't tested this code but it should be about right.

Terri|||Terri,

ur 2nd option worked for me. But i didnt understand why I am still getting zero as ReturnValue , when I tried with 1st option(given below), eventhough record exists for that username/pwd.

DECLARE @.RecordCount integer
select @.RecordCount= count(*) from employer
WHERE user_name like '%'+@.username +'%' and
password =@.userpwd
RETURN @.RecordCount
GO

Please advice !|||I used that same stored procedure, and your code modified slightly which executes the procedure and puts the returnvalue into a label and it works as expected:

CmdCoInfo = New SqlCommand("returntest", SqlConnection)
CmdCoInfo.CommandType = CommandType.StoredProcedure
CmdCoInfo.Parameters.Add("@.test1", 1)
Dim paramOut As SqlParameter
paramOut = CmdCoInfo.Parameters.Add("Returnvalue", SqlDbType.Int)
paramOut.Direction = ParameterDirection.ReturnValue
paramOut.Size = 40
SqlConnection.Open()
CmdCoInfo.ExecuteNonQuery()
Label1.Text = CmdCoInfo.Parameters("ReturnValue").Value

Terrisql

Product Level Insufficient

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

Thanks,

Michael D. Fox

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

Thanks,

Matt

|||

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

Thanks,

Michael D. Fox

|||

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

Thanks,

Matt

|||

Thanks Matt,

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

Thanks again,

Michael D. Fox

sql

Producing several letters based on the results of a query

I have created a single page letter using a number of text boxes and one
table, which is populated with static text along with some data from a single
row results set. This works perfectly when the results set only contains one
row, but when there are multiple data rows, the report still only contains
one page, whereas I expected there to be a page for every row.
A simple way to reproduce this is to create a report with a single text box
containing the value of one column from a query that returns multiple rows.
This results in a one page report, rather than a page for each row returned.
I'd be grateful if someone can help with this - I'm hoping there's a simple
solution, like setting a 'repeating region' property.
By the way, I've tried creating a single column table in the hope I could
put the entire contents of the letter into a cell of the table and that the
table row (and therefore the letter) would then repeat for each row of data,
but it doesn't appear to be possible to fix the size of a table row and have
multiple text boxes (and a table) inside it.
Thanks in advance,
David.Hey David,
If I understand you correctly, your trying to make, basically, a form
letter, that populates the variables from a query. For example
This is my test letter to "DAVID".
Where "DAVID" is generated from column/row in your Dataset. Why can't you
make a single cell table with all the text as you want it, except insert the
Field references you need (instead of textboxes)? For Example, the cell
would look like:
= "This is my test letter to " & Fields!ContactName.Value & ". " &
Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
etc...
Once this is done, you choose the "Group" properties and place a page break
after each group (or row in this case). This creates a new letter, each on
its own pages, for each row returned.
Michael C
"David C" wrote:
> I have created a single page letter using a number of text boxes and one
> table, which is populated with static text along with some data from a single
> row results set. This works perfectly when the results set only contains one
> row, but when there are multiple data rows, the report still only contains
> one page, whereas I expected there to be a page for every row.
> A simple way to reproduce this is to create a report with a single text box
> containing the value of one column from a query that returns multiple rows.
> This results in a one page report, rather than a page for each row returned.
> I'd be grateful if someone can help with this - I'm hoping there's a simple
> solution, like setting a 'repeating region' property.
> By the way, I've tried creating a single column table in the hope I could
> put the entire contents of the letter into a cell of the table and that the
> table row (and therefore the letter) would then repeat for each row of data,
> but it doesn't appear to be possible to fix the size of a table row and have
> multiple text boxes (and a table) inside it.
> Thanks in advance,
> David.|||Thanks Michael - it was the grouping that I needed. I actually stumbled
across the same answer last night in a TechNet article
(http://technet.microsoft.com/en-us/library/ms155816.aspx).
I put a List in first and then added a single column Table to it. I then
created a group expression on the list in Properties - General - Edit Details
Group - General, as follows: -
=Ceiling(RowNumber(Nothing)/1)
I also set 'Page break at end'.
Is this what you meant?
My problem now is that I want to make some of the text bold and I thought
I'd be able to do this using the Format command - something like Format("some
text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
that?
Thanks again.
David.
"Michael C" wrote:
> Hey David,
> If I understand you correctly, your trying to make, basically, a form
> letter, that populates the variables from a query. For example
> This is my test letter to "DAVID".
> Where "DAVID" is generated from column/row in your Dataset. Why can't you
> make a single cell table with all the text as you want it, except insert the
> Field references you need (instead of textboxes)? For Example, the cell
> would look like:
> = "This is my test letter to " & Fields!ContactName.Value & ". " &
> Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> etc...
> Once this is done, you choose the "Group" properties and place a page break
> after each group (or row in this case). This creates a new letter, each on
> its own pages, for each row returned.
> Michael C
> "David C" wrote:
> > I have created a single page letter using a number of text boxes and one
> > table, which is populated with static text along with some data from a single
> > row results set. This works perfectly when the results set only contains one
> > row, but when there are multiple data rows, the report still only contains
> > one page, whereas I expected there to be a page for every row.
> >
> > A simple way to reproduce this is to create a report with a single text box
> > containing the value of one column from a query that returns multiple rows.
> > This results in a one page report, rather than a page for each row returned.
> >
> > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > solution, like setting a 'repeating region' property.
> >
> > By the way, I've tried creating a single column table in the hope I could
> > put the entire contents of the letter into a cell of the table and that the
> > table row (and therefore the letter) would then repeat for each row of data,
> > but it doesn't appear to be possible to fix the size of a table row and have
> > multiple text boxes (and a table) inside it.
> >
> > Thanks in advance,
> >
> > David.|||Hey David,
Unfortunately I do not think there is a way, and if there i do not know
it. I had asked the forum recently about 2 fonts, 1 control but got no
responses.
You have done basically what I was describing. Glad it (sort of) worked for
you.
Michael
"David C" wrote:
> Thanks Michael - it was the grouping that I needed. I actually stumbled
> across the same answer last night in a TechNet article
> (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> I put a List in first and then added a single column Table to it. I then
> created a group expression on the list in Properties - General - Edit Details
> Group - General, as follows: -
> =Ceiling(RowNumber(Nothing)/1)
> I also set 'Page break at end'.
> Is this what you meant?
> My problem now is that I want to make some of the text bold and I thought
> I'd be able to do this using the Format command - something like Format("some
> text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> that?
> Thanks again.
> David.
>
> "Michael C" wrote:
> > Hey David,
> > If I understand you correctly, your trying to make, basically, a form
> > letter, that populates the variables from a query. For example
> >
> > This is my test letter to "DAVID".
> >
> > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > make a single cell table with all the text as you want it, except insert the
> > Field references you need (instead of textboxes)? For Example, the cell
> > would look like:
> >
> > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > etc...
> >
> > Once this is done, you choose the "Group" properties and place a page break
> > after each group (or row in this case). This creates a new letter, each on
> > its own pages, for each row returned.
> >
> > Michael C
> >
> > "David C" wrote:
> >
> > > I have created a single page letter using a number of text boxes and one
> > > table, which is populated with static text along with some data from a single
> > > row results set. This works perfectly when the results set only contains one
> > > row, but when there are multiple data rows, the report still only contains
> > > one page, whereas I expected there to be a page for every row.
> > >
> > > A simple way to reproduce this is to create a report with a single text box
> > > containing the value of one column from a query that returns multiple rows.
> > > This results in a one page report, rather than a page for each row returned.
> > >
> > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > solution, like setting a 'repeating region' property.
> > >
> > > By the way, I've tried creating a single column table in the hope I could
> > > put the entire contents of the letter into a cell of the table and that the
> > > table row (and therefore the letter) would then repeat for each row of data,
> > > but it doesn't appear to be possible to fix the size of a table row and have
> > > multiple text boxes (and a table) inside it.
> > >
> > > Thanks in advance,
> > >
> > > David.|||Hi Michael,
The text I wanted to be in bold was on a separate line, so in the end I just
put in a separate row in the table and set the row to bold. This worked fine,
presumably because the List control was handling the grouping.
Thanks again for your help.
David.
"Michael C" wrote:
> Hey David,
> Unfortunately I do not think there is a way, and if there i do not know
> it. I had asked the forum recently about 2 fonts, 1 control but got no
> responses.
> You have done basically what I was describing. Glad it (sort of) worked for
> you.
> Michael
> "David C" wrote:
> > Thanks Michael - it was the grouping that I needed. I actually stumbled
> > across the same answer last night in a TechNet article
> > (http://technet.microsoft.com/en-us/library/ms155816.aspx).
> >
> > I put a List in first and then added a single column Table to it. I then
> > created a group expression on the list in Properties - General - Edit Details
> > Group - General, as follows: -
> >
> > =Ceiling(RowNumber(Nothing)/1)
> >
> > I also set 'Page break at end'.
> >
> > Is this what you meant?
> >
> > My problem now is that I want to make some of the text bold and I thought
> > I'd be able to do this using the Format command - something like Format("some
> > text", "Bold"), but that doesn't seem to work. Have you any idea how I can do
> > that?
> >
> > Thanks again.
> >
> > David.
> >
> >
> >
> > "Michael C" wrote:
> >
> > > Hey David,
> > > If I understand you correctly, your trying to make, basically, a form
> > > letter, that populates the variables from a query. For example
> > >
> > > This is my test letter to "DAVID".
> > >
> > > Where "DAVID" is generated from column/row in your Dataset. Why can't you
> > > make a single cell table with all the text as you want it, except insert the
> > > Field references you need (instead of textboxes)? For Example, the cell
> > > would look like:
> > >
> > > = "This is my test letter to " & Fields!ContactName.Value & ". " &
> > > Fields!ContactName.Value & " lives at " & Fields!ContactAddy.Value & "."
> > > etc...
> > >
> > > Once this is done, you choose the "Group" properties and place a page break
> > > after each group (or row in this case). This creates a new letter, each on
> > > its own pages, for each row returned.
> > >
> > > Michael C
> > >
> > > "David C" wrote:
> > >
> > > > I have created a single page letter using a number of text boxes and one
> > > > table, which is populated with static text along with some data from a single
> > > > row results set. This works perfectly when the results set only contains one
> > > > row, but when there are multiple data rows, the report still only contains
> > > > one page, whereas I expected there to be a page for every row.
> > > >
> > > > A simple way to reproduce this is to create a report with a single text box
> > > > containing the value of one column from a query that returns multiple rows.
> > > > This results in a one page report, rather than a page for each row returned.
> > > >
> > > > I'd be grateful if someone can help with this - I'm hoping there's a simple
> > > > solution, like setting a 'repeating region' property.
> > > >
> > > > By the way, I've tried creating a single column table in the hope I could
> > > > put the entire contents of the letter into a cell of the table and that the
> > > > table row (and therefore the letter) would then repeat for each row of data,
> > > > but it doesn't appear to be possible to fix the size of a table row and have
> > > > multiple text boxes (and a table) inside it.
> > > >
> > > > Thanks in advance,
> > > >
> > > > David.

processing time issue

Hi all,

I have a quick question regarding the processing time.

I created view in db for the count purposes with huge fact table. ( I'm using dimension table which

joins the 1 fact and 1 dimension to count the key for the fast data retrieval)

If I write the query for the count in management studio and it gives a results within 5 sec.

But, if I add this view in DSV and process the cubes than it takes around 50 minutes.

This causes due to the Fact table? or what else?

I don't know what to check and please give me some comments.

Thanks in advance.

Have you run a profiler trace against the server. You could probably trace either SQL or SSAS to capture the exact SQL statement that is being executed. If this view is being used as a fact table SSAS will do a full scan of it and do a look up for each row to each of the associated dimensions. Doing a trace against SSAS might give you some more hints on what is going on. There are also a number of SSAS specific counters in Performance Monitor that might give some insight into what is going on.|||

Hi Darren,

Thanks for your good tips.

As you mentioned above, the view looks up for each row of associated dimension (fact table). In the Fact, total number of rows is around 153,000,000 and it makes cube processing very slow now.

So to improve better performance and faster processing what would you recommend in this situation? I only need the count of store number not that huge fact table.

Please give me some comments.

Thanks.

|||

There would be a couple of possible approaches to improving the processing performance.

One would be to partition the fact table so that you only process recent records and not the full 153 million.

Another might be to look at doing incremental processing. If you can keep track of which records are new since you last processed, you can just load those.

Finally you could possibly do a "group by" in your view to reduce the granularity, but often reducing the granularity reduces the flexibilty of your design and would generally be a last resort.

Processing of cubes in a scheduled DTS

Hi everyone!

I created a DTS which does some data transformations before processing some cubes. It finished processing in abt 10mins when I run this DTS manually. However, when I schedule this DTS to run, it took around 3 over hours to run. Does anybody know where the problem lies? I have been looking for a solution for this for a long time and I'm hoping that somebody can help me...

Thank you!! :)

MichelleDo you have the latest SP on the server? I seem to recall something in sp3 that is supposed to help this|||My database is in a different server from the cube. The SQL server for this database is Server 2000, service pack 3a. The one for my cubes is version 7.00.623. Will the difference in version slow down the process?

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])'"

|||Thanks a lot, that worked

Processing AS 2005 dimension with Oracle data source

Hi everyone -

I have a problem with an AS 2005 Data Source View created with an Oracle 10g data source. The view creates fine, and the dimension also creates with no problems. I click "explore data" from the right-click menu on the dimension's source table and the table data is returned with no problem. However, when I process the dimension, I get an immediate error "Referenced account is locked out...". The Impersonation Information is the Oracle userid/password. I am not locked out of the Oracle database, so I can't figure this one out. Have found nothing about this error during my searches. Any help would be appreciated.

Thanks

In the Impersonation Information , pick 'Use the service account'.|||Thanks for the help. I always used "Use the service account" for SQL Server data sources, but figured it would need my Oracle login to connect to an Oracle source. How the service account knows my Oracle login is a mystery, but as long as it works, I won't complain....

process is blocking itself

I have a stored proc created for report and now suddenly the report is takin
g
lot of time to run. The store proc is having simple select statement with
multiple case statements.
I checked the sysprocesses table and noticed that the process is blocking
itself.
What could be reason and how to solve this issue'
ThanksSo you are saying that when you run sp_who the spid that appears in
the "blk" column is the SAME as the value in the "spid" column of the
same line?
Roy Harvey
Beacon Falls, CT
On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
<TSQL@.discussions.microsoft.com> wrote:

>I have a stored proc created for report and now suddenly the report is taki
ng
>lot of time to run. The store proc is having simple select statement with
>multiple case statements.
>I checked the sysprocesses table and noticed that the process is blocking
>itself.
>What could be reason and how to solve this issue'
>Thanks|||You're running SQL 2000 with SP4 installed:
http://support.microsoft.com/defaul...KB;EN-US;906344
TSQL wrote:
> I have a stored proc created for report and now suddenly the report is tak
ing
> lot of time to run. The store proc is having simple select statement with
> multiple case statements.
> I checked the sysprocesses table and noticed that the process is blocking
> itself.
> What could be reason and how to solve this issue'
> Thanks|||Yes Exactly.
I ran -
select * from sysprocesses
where physical_io>25 or cpu>15 or memusage>15
order by blocked desc
"Roy Harvey" wrote:

> So you are saying that when you run sp_who the spid that appears in
> the "blk" column is the SAME as the value in the "spid" column of the
> same line?
> Roy Harvey
> Beacon Falls, CT
>
> On Thu, 8 Jun 2006 12:54:02 -0700, TSQL
> <TSQL@.discussions.microsoft.com> wrote:
>
>|||seems wierd. Try it with
OPYION (MAXDOP 1) and
and recompile the stored procedure.
Can you post the script?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||SQL 2000 latches quite often block other latch request from the same
SPID by design (which Tracy also acknowledged; see
http://support.microsoft.com/default.aspx/kb/906344). With SP4
Microsoft have started displaying those latch blocks (in addition to the
lock blocks) in the blocked column of sysprocesses, so now it looks like
a SPID is blocking itself (which, I guess, technically, it is) but in
fact it's usually just waiting on a page to be read into memory due to
slow I/O.
*mike hodgson*
http://sqlnerd.blogspot.com
Omnibuzz wrote:

>seems wierd. Try it with
>OPYION (MAXDOP 1) and
>and recompile the stored procedure.
>Can you post the script?
>|||Thanks Mike, Tracy. Went through the article. Makes sense. Makes a lot of
sense.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

Process Cube using SQL DTS

Hi
I have created a package to do this which simply returns with the error
'1 task(s) failed during execution'. I can process the dimensions and
cubes manually in analysis Manager fine.
SQL and analysis Manager are both on the same server and the task
appears to be running with the domain administrator user.
Cany anyone help?
Cheers
Brettos.
brettos
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message542345.html
In workflow properties, set "Execute on main package thread", including all
packages that contains OLAP processing.
James Ma
"brettos" wrote:

> Hi
> I have created a package to do this which simply returns with the error
> '1 task(s) failed during execution'. I can process the dimensions and
> cubes manually in analysis Manager fine.
> SQL and analysis Manager are both on the same server and the task
> appears to be running with the domain administrator user.
> Cany anyone help?
> Cheers
> Brettos.
>
> --
> brettos
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message542345.html
>
|||To help in your debuging.
Open The package in design mode.
Right Click and Select "Package Properties"
Select The "Logging" tab.
Provide a log file location to use during processing of the package.
save and close the package.
Now run the package.
Open the log file and see what details you can gleam.
report back to us.
Hope this helps.
dlr
"brettos" <brettos.1gk5p2@.mail.webservertalk.com> wrote in message
news:brettos.1gk5p2@.mail.webservertalk.com...
> Hi
> I have created a package to do this which simply returns with the error
> '1 task(s) failed during execution'. I can process the dimensions and
> cubes manually in analysis Manager fine.
> SQL and analysis Manager are both on the same server and the task
> appears to be running with the domain administrator user.
> Cany anyone help?
> Cheers
> Brettos.
>
> --
> brettos
> Posted via http://www.webservertalk.com
> View this thread: http://www.webservertalk.com/message542345.html
>

Process Cube using SQL DTS

Hi
I have created a package to do this which simply returns with the error '1 t
ask(s) failed during execution'. I can process the dimensions and cubes man
ually in analysis Manager fine.
SQL and analysis Manager are both on the same server and the task appears to
be running with the domain administrator user.
Cany anyone help?
Cheers
Brettos.In workflow properties, set "Execute on main package thread", including all
packages that contains OLAP processing.
James Ma
"brettos" wrote:

> Hi
> I have created a package to do this which simply returns with the error
> '1 task(s) failed during execution'. I can process the dimensions and
> cubes manually in analysis Manager fine.
> SQL and analysis Manager are both on the same server and the task
> appears to be running with the domain administrator user.
> Cany anyone help?
> Cheers
> Brettos.
>
> --
> brettos
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message542345.html
>|||To help in your debuging.
Open The package in design mode.
Right Click and Select "Package Properties"
Select The "Logging" tab.
Provide a log file location to use during processing of the package.
save and close the package.
Now run the package.
Open the log file and see what details you can gleam.
report back to us.
Hope this helps.
dlr
"brettos" <brettos.1gk5p2@.mail.webservertalk.com> wrote in message
news:brettos.1gk5p2@.mail.webservertalk.com...
> Hi
> I have created a package to do this which simply returns with the error
> '1 task(s) failed during execution'. I can process the dimensions and
> cubes manually in analysis Manager fine.
> SQL and analysis Manager are both on the same server and the task
> appears to be running with the domain administrator user.
> Cany anyone help?
> Cheers
> Brettos.
>
> --
> brettos
> ---
> Posted via http://www.webservertalk.com
> ---
> View this thread: http://www.webservertalk.com/message542345.html
>

Process could note deliver updates (s) at the publisher

I have created a publisher with row filtering using merge replication .I configured 5-6 subscribers for it.It was working fine for 2 months.But now for two subscribers its showing an error Process could not deliver update (s) at the publisher

Ive checked the job history..its showing

Merge process encountered an unexpected network error. The connection to tublisher 'Publishername' is no longer available

can any one help me?

thanks and regards

Dhanya

Sounds like a connection issue. Does it happen intermittently or persistently? If persistently, you can try to use osql.exe to connect to server "Publishername" from the machine that merge process is launched.

Thanks,

Peng

|||

Ive increased the query time out both at the publisher and subscriber...

now i am getting another message "The process is running and is wainting for a response from one of the backend connections"

Process could note deliver updates (s) at the publisher

I have created a publisher with row filtering using merge replication .I configured 5-6 subscribers for it.It was working fine for 2 months.But now for two subscribers its showing an error Process could not deliver update (s) at the publisher

Ive checked the job history..its showing

Merge process encountered an unexpected network error. The connection to tublisher 'Publishername' is no longer available

can any one help me?

thanks and regards

Dhanya

Sounds like a connection issue. Does it happen intermittently or persistently? If persistently, you can try to use osql.exe to connect to server "Publishername" from the machine that merge process is launched.

Thanks,

Peng

|||

Ive increased the query time out both at the publisher and subscriber...

now i am getting another message "The process is running and is wainting for a response from one of the backend connections"

Process constantly polling the tmw_queue table looking for work to do

I have created a package that will backup prod. database and restore
dev. database.
The job failed because there is a process constantly polling for work
to do.
How can I have the job kill this process, put database in single user
mode and then restore database?
Isabel
Use ALTER DATABASE to set restricted user or single user and the ROLLBACK option of ALTER DATABASE
(see Books Online for syntax).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"confused" <isabelellis@.hotmail.com> wrote in message
news:1133543598.767838.12810@.o13g2000cwo.googlegro ups.com...
>I have created a package that will backup prod. database and restore
> dev. database.
> The job failed because there is a process constantly polling for work
> to do.
> How can I have the job kill this process, put database in single user
> mode and then restore database?
> Isabel
>

Process constantly polling the tmw_queue table looking for work to do

I have created a package that will backup prod. database and restore
dev. database.
The job failed because there is a process constantly polling for work
to do.
How can I have the job kill this process, put database in single user
mode and then restore database?
IsabelUse ALTER DATABASE to set restricted user or single user and the ROLLBACK op
tion of ALTER DATABASE
(see Books Online for syntax).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"confused" <isabelellis@.hotmail.com> wrote in message
news:1133543598.767838.12810@.o13g2000cwo.googlegroups.com...
>I have created a package that will backup prod. database and restore
> dev. database.
> The job failed because there is a process constantly polling for work
> to do.
> How can I have the job kill this process, put database in single user
> mode and then restore database?
> Isabel
>

Process constantly polling the tmw_queue table looking for work to do

I have created a package that will backup prod. database and restore
dev. database.
The job failed because there is a process constantly polling for work
to do.
How can I have the job kill this process, put database in single user
mode and then restore database?
IsabelUse ALTER DATABASE to set restricted user or single user and the ROLLBACK option of ALTER DATABASE
(see Books Online for syntax).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"confused" <isabelellis@.hotmail.com> wrote in message
news:1133543598.767838.12810@.o13g2000cwo.googlegroups.com...
>I have created a package that will backup prod. database and restore
> dev. database.
> The job failed because there is a process constantly polling for work
> to do.
> How can I have the job kill this process, put database in single user
> mode and then restore database?
> Isabel
>