Cant show anything in the AS's Cube Data Explorer

Hi, I was killed by one strange problem:

I made an OLAP Project in the BusinessIntelegent Studio, with right Dims, right cubes, right measures,and built it successfully. I put some data into the it's fact table and dimention tables in the SQL server, and ProcessFull the cube successfully.

From the Process Form's result record, it said that i have read 25000 rows into the AS cube.

I get to the BusinessIntelegent Studio's Cube Data Explorer, i mean the place just to explore the cube's data in a pivot table tool. I put any of the measures into the tool, but it always shows nothing(Empty). I sent a MDX to the server to select the same measure, and also got nothing (NULL).

Any friends have met this? Thanks!

dear friend
go to the "calculated measures" tab of ur analysis services where u create the calculated measures...
there z a default "calculated measure" that comes under which u make and create all new measures..
i think by mistake u wud have deleted that default one..just go and create one more default calculated measure and i think its goona solve ur problem..
do post me back if the problem persist

regards
sid|||

Do you mean the first node of the default,that is, "caculate" in the calculated measures tab?

No, I have NOT deleted that.

I found something. If I delete all dimentions and add one again, the problem will be solved. The problem is still there if I only delete one dimention and add it again. I need to delete All dimentions, but only need to add one again, the problem is gone.

Why?

Thank u.

|||

Other things to check :

Have you specified default member on any dimension and/or attribute?

Is there any attributes that have "IsAggregatable" property set to false?

If you loaded many records and each measure value is 0 or NULL, at the end your result will allways be 0/NULL.

Vidas Matelis

|||

1.What you mean default member?

2.Yes, I have some attributes that have "IsAggregatable" property set to false. That is because my dimentions are father-son dimentions, so I set the fatherKey attributes's "IsAggregatable" property to false.

3.Of cause, my records's measure value is not 0 or Null. In fact, when I drop the dimention and then add them again the problem will go.

Thanks.

|||

1. For each dimension attribute you can specify default member. Then queries that do not specify anything for that attribute will use default member.In dimension editor when you select attribute, you can see property "DefaultMember". Lets your have country dimension/attribute and your fact records are assigned to european countries. But if you selecte default member "USA", by default your queries will be executed for "USA" country.

2. I guess this is your problem. Try to set IsAggregatable property to true and check for results. The way it works, that if you set this to false, then in queries where this attribute is not specified SSAS uses member from DefaultMember property. If there is no defaultMember specified for attribute with IsAggregatable=False, then default member is choosed for you. More info here: http://msdn2.microsoft.com/en-us/library/ms175626.aspx.

You might also experience strange results if more than one attribute in the same dimension has different specified default memeber.

Most parent/child dimensions by nature can be aggregatable, so double check if there is business requirement to set IsAggregatable to false there.

3. I was just double checking.

Vidas Matelis

|||

Vidas，I found sothing.

Yes, my dimentions are all father-son type dimentions, and I set the fatherkey's attribute's IsAggregatable property to False. That's the cause. When I change it to True, I could explore the data now.

At this time, the father-son dimention's default member will be chosed as the top node. In fact that's why I could not see any data in the cube's explorer TAB. Because the records are bound to another node of the father-son dimention, which is not the son the the TOP node.

I checked the data records, and found an interesting thing.

I found that if I set the fatherkey's attribute's IsAggregatable property to False, and keep it's default member to Nothing, the father-son dimention will NOT have the ALL member. You could do this and check it in the dimention's explorer TAB, and you will find that the ALL member is gone. Why?

Thanks.

|||

Ivanchain,

When you set IsAggregatable property to false, it is expected that that attribute will not have ALL member. This is by design. When IsAggregatable = False, you are telling SSAS that there is no point aggregating all members. More info here: http://msdn2.microsoft.com/en-us/library/ms174497.aspx

I am glad you found source of your problem.

Vidas Matelis

|||Thanks dear friends!

can't see DB on SQL 2005 with SQL management studio

I am having a strange issue with SQL Management studio express. I can
log via the front end, but I do not see the DB for which this user is
"owner"
This is not the odd part. The odd part is that if I log in through a
3rd party front end such as EMS sql manager lite I do see the database
I own, and can manipulate it as I please. Can anyone suggest a reason
for this. Here are details of the setup just in case. The DB server
only has one DB other then the default system DB's. The owner of the db
I am having issue with has full permissions on the DB, but is not a
member of ANY server roles. There is a firewall on the machine but it
is allowing tcp connections on 1433. I have not set any connection
restrictions. The only major security change done on the server was to
use:
use master
Revoke View any database from public
go
AND
use *db I am working with*
Exec Sp_changedbowner ' *user that will own the db I am working with* '
Hi
Have you logged out/in since changing the owner of the database? Have you
tried to create a new database as that login, and can you see it? Have you
applied SP1?
John
"pr0f1t" wrote:

> I am having a strange issue with SQL Management studio express. I can
> log via the front end, but I do not see the DB for which this user is
> "owner"
> This is not the odd part. The odd part is that if I log in through a
> 3rd party front end such as EMS sql manager lite I do see the database
> I own, and can manipulate it as I please. Can anyone suggest a reason
> for this. Here are details of the setup just in case. The DB server
> only has one DB other then the default system DB's. The owner of the db
> I am having issue with has full permissions on the DB, but is not a
> member of ANY server roles. There is a firewall on the machine but it
> is allowing tcp connections on 1433. I have not set any connection
> restrictions. The only major security change done on the server was to
> use:
> use master
> Revoke View any database from public
> go
> AND
> use *db I am working with*
> Exec Sp_changedbowner ' *user that will own the db I am working with* '
>
|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts
John Bell wrote:[vbcol=seagreen]
> Hi
> Have you logged out/in since changing the owner of the database? Have you
> tried to create a new database as that login, and can you see it? Have you
> applied SP1?
> John
> "pr0f1t" wrote:
|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts
|||Hi
That sounds like the change of ownership has not been reflected in the other
database. For the new database did you create the database as the login or
change the database to be owned by the login?
If you log in as a sysadm you should be able to see that database?
Try changing the owner again to a different login and then change it back.
If you run SELECT * FROM master.sys.databases what does it return?
Try regranting the VIEW ALL DATABASES to public and then just deny it to
specific logins.
What version of SQL 2005 are you using?
John
"pr0f1t" wrote:

> that is a fantastic suggestion. I don't knwo why I did not try this
> first. Yes the new DB is visible if I make this user owner. So why is
> the origional DB not visible if this user is owner of that as well? Any
> thoughts
>
> John Bell wrote:
>
|||*dbname*5NULL0xC1F615C4866765479D8B0AD5FD9FDEAA2006-10-31
14:53:43.06080SQL_Latin1_General_CP1_CI_AS0MULTI_USER0000ONLINE0000OFF03SIMPLE1TORN_PAGE_DETECTION1100000000000000000000009063F0BE-B2B1-4A45-9036-8775AAB1B47A00NOTHING0
is the result of SELECT * FROM master.sys.databases
I am running SQL Server 9.0.2047. I tried to change the ownership of
the DB but it only results in errors pretaining to object and schema
ownershipo, but I cannot seem to change the ownership to another user.
Ifs there a way to do this with TSQL? I have one user that essentially
needs to hve ownership of the DB, and access via a front end
porefereably Microsoft SQL managment studio express.
John Bell wrote:
|||Hi
Can you post the exact error number and message that occurs when you try to
change the owner? I assume you are using sp_changedbowner to do this?
Try creating a new user and changing the schema ownership to them
John
"pr0f1t" wrote:

> *dbname*5NULL0xC1F615C4866765479D8B0AD5FD9FDEAA2006-10-31
> 14:53:43.06080SQL_Latin1_General_CP1_CI_AS0MULTI_USER0000ONLINE0000OFF03SIMPLE1TORN_PAGE_DETECTION1100000000000000000000009063F0BE-B2B1-4A45-9036-8775AAB1B47A00NOTHING0
>
> is the result of SELECT * FROM master.sys.databases
> I am running SQL Server 9.0.2047. I tried to change the ownership of
> the DB but it only results in errors pretaining to object and schema
> ownershipo, but I cannot seem to change the ownership to another user.
> Ifs there a way to do this with TSQL? I have one user that essentially
> needs to hve ownership of the DB, and access via a front end
> porefereably Microsoft SQL managment studio express.
>
> John Bell wrote:
>

can't see DB on SQL 2005 with SQL management studio

I am having a strange issue with SQL Management studio express. I can
log via the front end, but I do not see the DB for which this user is
"owner"
This is not the odd part. The odd part is that if I log in through a
3rd party front end such as EMS sql manager lite I do see the database
I own, and can manipulate it as I please. Can anyone suggest a reason
for this. Here are details of the setup just in case. The DB server
only has one DB other then the default system DB's. The owner of the db
I am having issue with has full permissions on the DB, but is not a
member of ANY server roles. There is a firewall on the machine but it
is allowing tcp connections on 1433. I have not set any connection
restrictions. The only major security change done on the server was to
use:
use master
Revoke View any database from public
go
AND
use *db I am working with*
Exec Sp_changedbowner ' *user that will own the db I am working with* 'Hi
Have you logged out/in since changing the owner of the database? Have you
tried to create a new database as that login, and can you see it? Have you
applied SP1?
John
"pr0f1t" wrote:

> I am having a strange issue with SQL Management studio express. I can
> log via the front end, but I do not see the DB for which this user is
> "owner"
> This is not the odd part. The odd part is that if I log in through a
> 3rd party front end such as EMS sql manager lite I do see the database
> I own, and can manipulate it as I please. Can anyone suggest a reason
> for this. Here are details of the setup just in case. The DB server
> only has one DB other then the default system DB's. The owner of the db
> I am having issue with has full permissions on the DB, but is not a
> member of ANY server roles. There is a firewall on the machine but it
> is allowing tcp connections on 1433. I have not set any connection
> restrictions. The only major security change done on the server was to
> use:
> use master
> Revoke View any database from public
> go
> AND
> use *db I am working with*
> Exec Sp_changedbowner ' *user that will own the db I am working with* '
>|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts
John Bell wrote:[vbcol=seagreen]
> Hi
> Have you logged out/in since changing the owner of the database? Have you
> tried to create a new database as that login, and can you see it? Have you
> applied SP1?
> John
> "pr0f1t" wrote:
>|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts|||Hi
That sounds like the change of ownership has not been reflected in the other
database. For the new database did you create the database as the login or
change the database to be owned by the login?
If you log in as a sysadm you should be able to see that database?
Try changing the owner again to a different login and then change it back.
If you run SELECT * FROM master.sys.databases what does it return?
Try regranting the VIEW ALL DATABASES to public and then just deny it to
specific logins.
What version of SQL 2005 are you using?
John
"pr0f1t" wrote:

> that is a fantastic suggestion. I don't knwo why I did not try this
> first. Yes the new DB is visible if I make this user owner. So why is
> the origional DB not visible if this user is owner of that as well? Any
> thoughts
>
> John Bell wrote:
>|||*dbname* 5 NULL 0xC1F615C4866765479D8B0A
D5FD9FDEAA 2006-10-31
14:53:43. 060 80 SQL_Latin1_General_CP1_CI_AS 0 MU
LTI_USER 0 0 0 0 ONLINE 0 0
0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECTIO
N 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
is the result of SELECT * FROM master.sys.databases
I am running SQL Server 9.0.2047. I tried to change the ownership of
the DB but it only results in errors pretaining to object and schema
ownershipo, but I cannot seem to change the ownership to another user.
Ifs there a way to do this with TSQL? I have one user that essentially
needs to hve ownership of the DB, and access via a front end
porefereably Microsoft SQL managment studio express.
John Bell wrote:|||Hi
Can you post the exact error number and message that occurs when you try to
change the owner? I assume you are using sp_changedbowner to do this?
Try creating a new user and changing the schema ownership to them
John
"pr0f1t" wrote:

> *dbname* 5 NULL 0xC1F615C4866765479D8B0A
D5FD9FDEAA 2006-10-31
> 14:53:43. 060 80 SQL_Latin1_General_CP1_CI_AS 0 MU
LTI_USER 0 0 0 0 ONLINE 0
0 0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECT
ION 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0
0 0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
>
> is the result of SELECT * FROM master.sys.databases
> I am running SQL Server 9.0.2047. I tried to change the ownership of
> the DB but it only results in errors pretaining to object and schema
> ownershipo, but I cannot seem to change the ownership to another user.
> Ifs there a way to do this with TSQL? I have one user that essentially
> needs to hve ownership of the DB, and access via a front end
> porefereably Microsoft SQL managment studio express.
>
> John Bell wrote:
>

can't see DB on SQL 2005 with SQL management studio

I am having a strange issue with SQL Management studio express. I can
log via the front end, but I do not see the DB for which this user is
"owner"
This is not the odd part. The odd part is that if I log in through a
3rd party front end such as EMS sql manager lite I do see the database
I own, and can manipulate it as I please. Can anyone suggest a reason
for this. Here are details of the setup just in case. The DB server
only has one DB other then the default system DB's. The owner of the db
I am having issue with has full permissions on the DB, but is not a
member of ANY server roles. There is a firewall on the machine but it
is allowing tcp connections on 1433. I have not set any connection
restrictions. The only major security change done on the server was to
use:
use master
Revoke View any database from public
go
AND
use *db I am working with*
Exec Sp_changedbowner ' *user that will own the db I am working with* 'Hi
Have you logged out/in since changing the owner of the database? Have you
tried to create a new database as that login, and can you see it? Have you
applied SP1?
John
"pr0f1t" wrote:
> I am having a strange issue with SQL Management studio express. I can
> log via the front end, but I do not see the DB for which this user is
> "owner"
> This is not the odd part. The odd part is that if I log in through a
> 3rd party front end such as EMS sql manager lite I do see the database
> I own, and can manipulate it as I please. Can anyone suggest a reason
> for this. Here are details of the setup just in case. The DB server
> only has one DB other then the default system DB's. The owner of the db
> I am having issue with has full permissions on the DB, but is not a
> member of ANY server roles. There is a firewall on the machine but it
> is allowing tcp connections on 1433. I have not set any connection
> restrictions. The only major security change done on the server was to
> use:
> use master
> Revoke View any database from public
> go
> AND
> use *db I am working with*
> Exec Sp_changedbowner ' *user that will own the db I am working with* '
>|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts
John Bell wrote:
> Hi
> Have you logged out/in since changing the owner of the database? Have you
> tried to create a new database as that login, and can you see it? Have you
> applied SP1?
> John
> "pr0f1t" wrote:
> > I am having a strange issue with SQL Management studio express. I can
> > log via the front end, but I do not see the DB for which this user is
> > "owner"
> > This is not the odd part. The odd part is that if I log in through a
> > 3rd party front end such as EMS sql manager lite I do see the database
> > I own, and can manipulate it as I please. Can anyone suggest a reason
> > for this. Here are details of the setup just in case. The DB server
> > only has one DB other then the default system DB's. The owner of the db
> > I am having issue with has full permissions on the DB, but is not a
> > member of ANY server roles. There is a firewall on the machine but it
> > is allowing tcp connections on 1433. I have not set any connection
> > restrictions. The only major security change done on the server was to
> > use:
> >
> > use master
> > Revoke View any database from public
> > go
> >
> > AND
> >
> > use *db I am working with*
> > Exec Sp_changedbowner ' *user that will own the db I am working with* '
> >
> >|||that is a fantastic suggestion. I don't knwo why I did not try this
first. Yes the new DB is visible if I make this user owner. So why is
the origional DB not visible if this user is owner of that as well? Any
thoughts|||Hi
That sounds like the change of ownership has not been reflected in the other
database. For the new database did you create the database as the login or
change the database to be owned by the login?
If you log in as a sysadm you should be able to see that database?
Try changing the owner again to a different login and then change it back.
If you run SELECT * FROM master.sys.databases what does it return?
Try regranting the VIEW ALL DATABASES to public and then just deny it to
specific logins.
What version of SQL 2005 are you using?
John
"pr0f1t" wrote:
> that is a fantastic suggestion. I don't knwo why I did not try this
> first. Yes the new DB is visible if I make this user owner. So why is
> the origional DB not visible if this user is owner of that as well? Any
> thoughts
>
> John Bell wrote:
> > Hi
> >
> > Have you logged out/in since changing the owner of the database? Have you
> > tried to create a new database as that login, and can you see it? Have you
> > applied SP1?
> >
> > John
> >
> > "pr0f1t" wrote:
> >
> > > I am having a strange issue with SQL Management studio express. I can
> > > log via the front end, but I do not see the DB for which this user is
> > > "owner"
> > > This is not the odd part. The odd part is that if I log in through a
> > > 3rd party front end such as EMS sql manager lite I do see the database
> > > I own, and can manipulate it as I please. Can anyone suggest a reason
> > > for this. Here are details of the setup just in case. The DB server
> > > only has one DB other then the default system DB's. The owner of the db
> > > I am having issue with has full permissions on the DB, but is not a
> > > member of ANY server roles. There is a firewall on the machine but it
> > > is allowing tcp connections on 1433. I have not set any connection
> > > restrictions. The only major security change done on the server was to
> > > use:
> > >
> > > use master
> > > Revoke View any database from public
> > > go
> > >
> > > AND
> > >
> > > use *db I am working with*
> > > Exec Sp_changedbowner ' *user that will own the db I am working with* '
> > >
> > >
>|||*dbname* 5 NULL 0xC1F615C4866765479D8B0AD5FD9FDEAA 2006-10-31
14:53:43.060 80 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECTION 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
is the result of SELECT * FROM master.sys.databases
I am running SQL Server 9.0.2047. I tried to change the ownership of
the DB but it only results in errors pretaining to object and schema
ownershipo, but I cannot seem to change the ownership to another user.
Ifs there a way to do this with TSQL? I have one user that essentially
needs to hve ownership of the DB, and access via a front end
porefereably Microsoft SQL managment studio express.
John Bell wrote:|||Hi
Can you post the exact error number and message that occurs when you try to
change the owner? I assume you are using sp_changedbowner to do this?
Try creating a new user and changing the schema ownership to them
John
"pr0f1t" wrote:
> *dbname* 5 NULL 0xC1F615C4866765479D8B0AD5FD9FDEAA 2006-10-31
> 14:53:43.060 80 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 1 TORN_PAGE_DETECTION 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 9063F0BE-B2B1-4A45-9036-8775AAB1B47A 0 0 NOTHING 0
>
> is the result of SELECT * FROM master.sys.databases
> I am running SQL Server 9.0.2047. I tried to change the ownership of
> the DB but it only results in errors pretaining to object and schema
> ownershipo, but I cannot seem to change the ownership to another user.
> Ifs there a way to do this with TSQL? I have one user that essentially
> needs to hve ownership of the DB, and access via a front end
> porefereably Microsoft SQL managment studio express.
>
> John Bell wrote:
>

CAN'T remove "Execute job" from alert in SS 2005?

This is kind of strange. On a SQL 2005 SP2 box, I've set up a few alerts to
monitor tran log growth. If they reach the threshold the alerts will fire
and send emails and kick off a tran log dump job. Now I want to remove the
job by unchecking the "Execute job" box from the "Response" node. after
clicking OK and going back to revisit it, the job is still checked. I've
tested this on a few other SQL instances - all are the same. From my
machine, I tested on a SQL 2000 server and it works as expected.
The alert is scripted below. I have also tried to change @.job_id to null in
a hope that the sproc will make it unchecked. But still the old job exists
in the alert.
EXEC msdb.dbo.sp_add_alert @.name=N'test',
@.message_id=0,
@.severity=0,
@.enabled=1,
@.delay_between_responses=0,
@.include_event_description_in=1,
@.category_name=N'[Uncategorized]',
@.performance_condition=N'SQLServer:Databases|Log File(s) Used Size
(KB)|tempdb|>|100000',
@.job_id=N'4d9d9fde-0fed-4d79-b894-0a04ae0d2b85'
I tend to believe this is a SQL 2005 bug. Has anyone else seen or can anyone
duplicate the same problem?
RickIt is not strange if it's a bug. :-) confirmed for ss2005. someone already
reported in April 2007.
"YPD" <y.ding@.neu.edu> wrote in message
news:C272B7CA-23BD-497A-AACC-C84E60A5030E@.microsoft.com...
> This is kind of strange. On a SQL 2005 SP2 box, I've set up a few alerts
> to monitor tran log growth. If they reach the threshold the alerts will
> fire and send emails and kick off a tran log dump job. Now I want to
> remove the job by unchecking the "Execute job" box from the "Response"
> node. after clicking OK and going back to revisit it, the job is still
> checked. I've tested this on a few other SQL instances - all are the same.
> From my machine, I tested on a SQL 2000 server and it works as expected.
> The alert is scripted below. I have also tried to change @.job_id to null
> in a hope that the sproc will make it unchecked. But still the old job
> exists in the alert.
> EXEC msdb.dbo.sp_add_alert @.name=N'test',
> @.message_id=0,
> @.severity=0,
> @.enabled=1,
> @.delay_between_responses=0,
> @.include_event_description_in=1,
> @.category_name=N'[Uncategorized]',
> @.performance_condition=N'SQLServer:Databases|Log File(s) Used Size
> (KB)|tempdb|>|100000',
> @.job_id=N'4d9d9fde-0fed-4d79-b894-0a04ae0d2b85'
> I tend to believe this is a SQL 2005 bug. Has anyone else seen or can
> anyone duplicate the same problem?
>
> Rick

can't kill process!

Strange thing. I have a spid that says the process is
"select * from user-table1". The spid is in "sleeping"
status. I can't kill the process and I have tried 20
times! It won't go away. I have stopped and started the
Sql Server services. I have rebooted the Sql Server box.
Its always there and it is causing probelms with my DB
Maint jobs. How can I get rid of the spid if Kill does
not work?
Thanks,
Brianbriang (anonymous@.discussions.microsoft.com) writes:
> Strange thing. I have a spid that says the process is
> "select * from user-table1". The spid is in "sleeping"
> status. I can't kill the process and I have tried 20
> times! It won't go away. I have stopped and started the
> Sql Server services. I have rebooted the Sql Server box.
> Its always there and it is causing probelms with my DB
> Maint jobs. How can I get rid of the spid if Kill does
> not work?
If you stop and start SQL Server, and the process is still there,
this indicates that the client program keeps reconnecting and
submits the query over and over again. This should be visible
in sysprocesses by the column last_batch always being a recent
point in time.
So you would somehow have to find that client and see what it is up to.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Run sp_who2 from Query Analyser. Located the spid in question and retrieve
the HostName, Login and ProgramName from the results. This will give you vi
tal clues as to what the culprit is. eg A service account on a particular wo
rkstation or server running
say a .NET application or component. If you can't locate the "application"
in question take this information to one of your network administrators (who
should be able to solve this one for you).

can't kill process!

Strange thing. I have a spid that says the process is
"select * from user-table1". The spid is in "sleeping"
status. I can't kill the process and I have tried 20
times! It won't go away. I have stopped and started the
Sql Server services. I have rebooted the Sql Server box.
Its always there and it is causing probelms with my DB
Maint jobs. How can I get rid of the spid if Kill does
not work?
Thanks,
Brianbriang (anonymous@.discussions.microsoft.com) writes:
> Strange thing. I have a spid that says the process is
> "select * from user-table1". The spid is in "sleeping"
> status. I can't kill the process and I have tried 20
> times! It won't go away. I have stopped and started the
> Sql Server services. I have rebooted the Sql Server box.
> Its always there and it is causing probelms with my DB
> Maint jobs. How can I get rid of the spid if Kill does
> not work?
If you stop and start SQL Server, and the process is still there,
this indicates that the client program keeps reconnecting and
submits the query over and over again. This should be visible
in sysprocesses by the column last_batch always being a recent
point in time.
So you would somehow have to find that client and see what it is up to.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Run sp_who2 from Query Analyser. Located the spid in question and retrieve the HostName, Login and ProgramName from the results. This will give you vital clues as to what the culprit is. eg A service account on a particular workstation or server running say a .NET application or component. If you can't locate the "application" in question take this information to one of your network administrators (who should be able to solve this one for you).