Cannot process the object "SET FMTONLY ON EXEC DBName..StoredProc". The OLE DB provide

I am getting an error when creating MyView:
Cannot process the object "SET FMTONLY ON EXEC DBName..StoredProc". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

MyView is accessing a stored procedure as follows:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create VIEW [dbo].[MyView]
AS
SELECT * FROM
OPENROWSET ( 'SQLOLEDB', 'SERVER=.;Trusted_Connection=Yes',
'SET FMTONLY ON EXEC DBName..StoredProc' )
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

StoredProc has a dynamically created synonym to avoid conflicts as follows:

declare @.DB as varchar(25)
SET @.DB = db_name()
DECLARE @.TblName as varchar(50)
DECLARE @.cx as varchar(100)
SET @.TblName = 'MyFunction'
declare @.DySynName as varchar(200)
declare @.intI as int

SELECT @.DySynName = 'dsicx' + @.TableName + '_' + system_user + '_' + CONVERT(char(12), GETDATE(), 14)

- The following while loop strips off the colon
SELECT @.intI = charindex(':',@.DySynName)
WHILE @.intI > 1
BEGIN
SELECT @.DySynName = substring(@.DySynName,1,@.intI - 1) +
substring(@.DySynName,@.intI + 1, LEN(@.DySynName)-@.intI )
SELECT @.intI = charindex(':',@.DySynName)
END

SET @.cx= substring(db_name(), dbo.instrrev('_', db_name()) + 1, datalength(db_name()) - dbo.instrrev('_', db_name()))

EXEC ('CREATE SYNONYM '+@.DySynName +' FOR '+@.cx+'..'+@.TblName+'

select *
from '+@.DySynName +'('''+@.DB+''', 5, 0)

drop synonym '+@.DySynName )

The StoredProc procedure works fine on it's own, and the View works fine if I use a static name instead of @.DySynName when creating, using and dropping the synonym.
MyView will work with the StoredProc procedure if I use @.DySynName for the syonym but I have to hard code the login and password in the OPENROWSET function instead of using Trusted_Connection=Yes, since multiple users will be accessing MyView a hard coded login and password will not suffice.

Is there something I can use other than Trusted_Connection=Yes that will allow me to use the @.DySynName variable for my synonym? Any ideas would be greatly appreciated, please keep in mind that I cannot exclude the dynamic synonym name and I cannot use temporary tables or create and delete the view upon use because of the load on the system.

Use following statements, when you create View:

Code Snippet

CREATE VIEW [dbo].[MyView]

AS

SELECT * FROM

OPENROWSET ( 'SQLOLEDB', 'SERVER=.;Trusted_Connection=Yes',

'SET FMTONLY ON; EXEC DBName..StoredProc' )

GO

I just add semicolon and SQL Server execute "SET FMTONLY ON; EXEC DBName..StoredProc" as two statements

|||Still no luck, thanks for trying though.

Cannot log conflict data

Hi,
I'm having problme when performing the Merge replication and the error as
belows:
1) The process could not log conflict information
follow by
2) Too many arguments were supplied for procedure sp_cft_xxxxxxx
Pls help, it is urgent!!!
Thanks.
Ringo
Message posted via http://www.sqlmonster.com
does this help?
http://support.microsoft.com/default...;en-us;Q241658
Hilary Cotter
Looking for a SQL Server replication book?
<http://www.nwsu.com/0974973602.html>
Looking for a FAQ on Indexing Services/SQL FTS
<http://www.indexserverfaq.com>
|||Hi,
This doesn't really help. Maybe I should address more on my problem. The
problem happened when there;s conflict during replication and try to record
info into the conflict table through sp_cft_xxxxx stored procedure. The
error shows that the parameters passing to the sp_cft_xxxxx SP is more (one
extra parameter which I think is "0x00" as the last parameter).
The above replication is between the SQL CE (PDA) and MS SQL 2000 server
where I'm using the "Synchronize()" method.
BTW, thanks for your help
Message posted via http://www.sqlmonster.com
|||where are you calling this proc? Its on the publisher correct?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ringo law via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:daf883cd95a9432fba48b2a26852239f@.SQLMonster.c om...
> Hi,
> This doesn't really help. Maybe I should address more on my problem. The
> problem happened when there;s conflict during replication and try to
> record
> info into the conflict table through sp_cft_xxxxx stored procedure. The
> error shows that the parameters passing to the sp_cft_xxxxx SP is more
> (one
> extra parameter which I think is "0x00" as the last parameter).
> The above replication is between the SQL CE (PDA) and MS SQL 2000 server
> where I'm using the "Synchronize()" method.
> BTW, thanks for your help
> --
> Message posted via http://www.sqlmonster.com
|||Hi,
No, I didn't call the stored proc. It's a system stored proc to log
conflict info in publisher table (conflict_tablename) when there's conflict
occurred.
If I try this Merge Replication between SQL Server (2000 to 2000), it can
work and the conflict record is created. But, if between SQL CE and 2000,
then this problem happened.
Thanks.
Ringo
Message posted via http://www.sqlmonster.com

Cannot kill process with EC wait type

Hi
I had tired to create index on large databse (>200GB, engine ss2k) but
I got errors, so I executed DBCC CHECKDB. DBCC told me that I have to
execute it with repair options. But I noticed in Activity Monitor that
I had still running creating index process with EC wait type. Then I
started to kill it but without success.
Is it possible to kill such process, without restarting sql server?
Best regards,
BartekHi Bartek
The process may be rolling back try KILL WITH STATUSONLY to see what it is
doing.
John
"Bartolo" wrote:
> Hi
> I had tired to create index on large databse (>200GB, engine ss2k) but
> I got errors, so I executed DBCC CHECKDB. DBCC told me that I have to
> execute it with repair options. But I noticed in Activity Monitor that
> I had still running creating index process with EC wait type. Then I
> started to kill it but without success.
> Is it possible to kill such process, without restarting sql server?
> Best regards,
> Bartek
>|||Hi John
Thanks for reponse. I tried this one, it stuck to 100% for few days.
I got reply on another forums that I can't even kill this process with
server restart.
It is really confusing that there is no way to get rid off such
process.
Bartek
On 16 Mar, 08:22, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Bartek
> The process may be rolling back try KILL WITH STATUSONLY to see what it is
> doing.
> John
> "Bartolo" wrote:
> > Hi
> > I had tired to create index on large databse (>200GB, engine ss2k) but
> > I got errors, so I executed DBCC CHECKDB. DBCC told me that I have to
> > execute it with repair options. But I noticed in Activity Monitor that
> > I had still running creating index process with EC wait type. Then I
> > started to kill it but without success.
> > Is it possible to kill such process, without restarting sql server?
> > Best regards,
> > Bartek|||I succeeded.
It seems that restarting SQL Server is the only opition to get rid of
such process.
Cheers,
Bartek
On 17 Mar, 12:51, Bartolo <andrew...@.gmail.com> wrote:
> Hi John
> Thanks for reponse. I tried this one, it stuck to 100% for few days.
> I got reply on another forums that I can't even kill this process with
> server restart.
> It is really confusing that there is no way to get rid off such
> process.
> Bartek
> On 16 Mar, 08:22, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi Bartek
> > The process may be rolling back try KILL WITH STATUSONLY to see what it is
> > doing.
> > John
> > "Bartolo" wrote:
> > > Hi
> > > I had tired to create index on large databse (>200GB, engine ss2k) but
> > > I got errors, so I executed DBCC CHECKDB. DBCC told me that I have to
> > > execute it with repair options. But I noticed in Activity Monitor that
> > > I had still running creating index process with EC wait type. Then I
> > > started to kill it but without success.
> > > Is it possible to kill such process, without restarting sql server?
> > > Best regards,
> > > Bartek

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Kill Process

SPID 84: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I've tried to kill this several times. It appears as though it should be
completed but it's hanging for some reason.
Any help would be appreciated.
Thanks!I found the process in windows task manager. Killing the process through
there allowed me to then kill it through the KILL (SPID) command.
"FK" wrote:
> SPID 84: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I've tried to kill this several times. It appears as though it should be
> completed but it's hanging for some reason.
> Any help would be appreciated.
> Thanks!

Cannot kill process

process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
Thanks a lot in advance for help!Urban,
Try:
KILL <SPID> WITH STATUSONLY.
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
SORRY, forgot to mention, that I tryed KILL 51, of course.
Thanks a lot in advance for help!|||Urban,
STATUSONLY will show you the estimated rollback completion. Is this a test
box, you could try stopping the SQL Server service and then starting the SQL
Server service then the SQL Server Agent service. Any errors reported in
either the SQL logs or the app event logs?
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> SORRY, forgot to mention, that I tryed KILL 51, of course.
>
> Thanks a lot in advance for help!
>|||Query analyser returns:
Status report cannot be obtained. Rollback operation for Process ID 51 is
not in progress.
Kill 51 is making the entries in tempd, but not killing the process.
"Jerry Spivey" wrote:
> Urban,
> Try:
> KILL <SPID> WITH STATUSONLY.
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> > process with status runnable in MASTER db (waiting for lock shown in mmc).
> > as consequence, SQL Server agent is not starting anymore!
> > how can I kill this process?
> > master..sysobjects shows this process as non-blocked (blocked=0).
> > in tempdb the entry is shown with SPID #51 correponding to the running
> > process
> > but it is not terminating since several days.
> >
> > Thanks a lot in advance for help!
> >
>
>|||Restart tried several times, even restatred machine.
No errors reported, Just that Process 51 has been killed, but is a true lie;-)
the process is still alive!
"Jerry Spivey" wrote:
> Urban,
> STATUSONLY will show you the estimated rollback completion. Is this a test
> box, you could try stopping the SQL Server service and then starting the SQL
> Server service then the SQL Server Agent service. Any errors reported in
> either the SQL logs or the app event logs?
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> > process with status runnable in MASTER db (waiting for lock shown in mmc).
> > as consequence, SQL Server agent is not starting anymore!
> > how can I kill this process?
> > master..sysobjects shows this process as non-blocked (blocked=0).
> > in tempdb the entry is shown with SPID #51 correponding to the running
> > process
> > but it is not terminating since several days.
> > SORRY, forgot to mention, that I tryed KILL 51, of course.
> >
> >
> > Thanks a lot in advance for help!
> >
>
>|||How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
using to show you the information?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||Thanks a lot! I'm ashamed, you're right!!!!
It is indeed my own process. I didn't recognize, because I used the run as
command for mmc with the same user that is running SQL-Server, and not my
common user.
I understand, that the prozess is not allowed to kill itself. - Strange,
that it was alive for more than 10 days.
Meanwile, SQL Server Agent was able to start, after about 10 days. This I
don't understand. But even after restarting the databse it works fine.
Propably a problem when patching the server.
Anyway, you have solved my Problem, Tibor. Thanks a lot!
"Tibor Karaszi" wrote:
> How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
> using to show you the information?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> > process with status runnable in MASTER db (waiting for lock shown in mmc).
> > as consequence, SQL Server agent is not starting anymore!
> > how can I kill this process?
> > master..sysobjects shows this process as non-blocked (blocked=0).
> > in tempdb the entry is shown with SPID #51 correponding to the running process
> > but it is not terminating since several days.
> >
> > Thanks a lot in advance for help!
> >
>

Cannot Kill Process

SPID 84: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I've tried to kill this several times. It appears as though it should be
completed but it's hanging for some reason.
Any help would be appreciated.
Thanks!
I found the process in windows task manager. Killing the process through
there allowed me to then kill it through the KILL (SPID) command.
"FK" wrote:

> SPID 84: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I've tried to kill this several times. It appears as though it should be
> completed but it's hanging for some reason.
> Any help would be appreciated.
> Thanks!

Cannot kill process

process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
Thanks a lot in advance for help!
Urban,
Try:
KILL <SPID> WITH STATUSONLY.
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>
|||process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running process
but it is not terminating since several days.
SORRY, forgot to mention, that I tryed KILL 51, of course.
Thanks a lot in advance for help!
|||Urban,
STATUSONLY will show you the estimated rollback completion. Is this a test
box, you could try stopping the SQL Server service and then starting the SQL
Server service then the SQL Server Agent service. Any errors reported in
either the SQL logs or the app event logs?
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> SORRY, forgot to mention, that I tryed KILL 51, of course.
>
> Thanks a lot in advance for help!
>
|||Query analyser returns:
Status report cannot be obtained. Rollback operation for Process ID 51 is
not in progress.
Kill 51 is making the entries in tempd, but not killing the process.
"Jerry Spivey" wrote:

> Urban,
> Try:
> KILL <SPID> WITH STATUSONLY.
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
>
|||Restart tried several times, even restatred machine.
No errors reported, Just that Process 51 has been killed, but is a true lie;-)
the process is still alive!
"Jerry Spivey" wrote:

> Urban,
> STATUSONLY will show you the estimated rollback completion. Is this a test
> box, you could try stopping the SQL Server service and then starting the SQL
> Server service then the SQL Server Agent service. Any errors reported in
> either the SQL logs or the app event logs?
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
>
>
|||How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
using to show you the information?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>
|||Thanks a lot! I'm ashamed, you're right!!!!
It is indeed my own process. I didn't recognize, because I used the run as
command for mmc with the same user that is running SQL-Server, and not my
common user.
I understand, that the prozess is not allowed to kill itself. - Strange,
that it was alive for more than 10 days.
Meanwile, SQL Server Agent was able to start, after about 10 days. This I
don't understand. But even after restarting the databse it works fine.
Propably a problem when patching the server.
Anyway, you have solved my Problem, Tibor. Thanks a lot!
"Tibor Karaszi" wrote:

> How are you looking that this? EM, current activity? Perhaps this is your connection that EM is
> using to show you the information?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>

Cannot kill process

process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running proce
ss
but it is not terminating since several days.
Thanks a lot in advance for help!Urban,
Try:
KILL <SPID> WITH STATUSONLY.
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||process with status runnable in MASTER db (waiting for lock shown in mmc).
as consequence, SQL Server agent is not starting anymore!
how can I kill this process?
master..sysobjects shows this process as non-blocked (blocked=0).
in tempdb the entry is shown with SPID #51 correponding to the running proce
ss
but it is not terminating since several days.
SORRY, forgot to mention, that I tryed KILL 51, of course.
Thanks a lot in advance for help!|||Urban,
STATUSONLY will show you the estimated rollback completion. Is this a test
box, you could try stopping the SQL Server service and then starting the SQL
Server service then the SQL Server Agent service. Any errors reported in
either the SQL logs or the app event logs?
HTH
Jerry
"Urban" <urban@.nospamplease> wrote in message
news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running
> process
> but it is not terminating since several days.
> SORRY, forgot to mention, that I tryed KILL 51, of course.
>
> Thanks a lot in advance for help!
>|||Query analyser returns:
Status report cannot be obtained. Rollback operation for Process ID 51 is
not in progress.
Kill 51 is making the entries in tempd, but not killing the process.
"Jerry Spivey" wrote:

> Urban,
> Try:
> KILL <SPID> WITH STATUSONLY.
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>
>|||Restart tried several times, even restatred machine.
No errors reported, Just that Process 51 has been killed, but is a true lie;
-)
the process is still alive!
"Jerry Spivey" wrote:

> Urban,
> STATUSONLY will show you the estimated rollback completion. Is this a tes
t
> box, you could try stopping the SQL Server service and then starting the S
QL
> Server service then the SQL Server Agent service. Any errors reported in
> either the SQL logs or the app event logs?
> HTH
> Jerry
> "Urban" <urban@.nospamplease> wrote in message
> news:CDE764AB-E274-4480-A61B-3A31C9BC0426@.microsoft.com...
>
>|||How are you looking that this? EM, current activity? Perhaps this is your co
nnection that EM is
using to show you the information?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Urban" <urban@.nospamplease> wrote in message
news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
> process with status runnable in MASTER db (waiting for lock shown in mmc).
> as consequence, SQL Server agent is not starting anymore!
> how can I kill this process?
> master..sysobjects shows this process as non-blocked (blocked=0).
> in tempdb the entry is shown with SPID #51 correponding to the running pro
cess
> but it is not terminating since several days.
> Thanks a lot in advance for help!
>|||Thanks a lot! I'm ashamed, you're right!!!!
It is indeed my own process. I didn't recognize, because I used the run as
command for mmc with the same user that is running SQL-Server, and not my
common user.
I understand, that the prozess is not allowed to kill itself. - Strange,
that it was alive for more than 10 days.
Meanwile, SQL Server Agent was able to start, after about 10 days. This I
don't understand. But even after restarting the databse it works fine.
Propably a problem when patching the server.
Anyway, you have solved my Problem, Tibor. Thanks a lot!
"Tibor Karaszi" wrote:

> How are you looking that this? EM, current activity? Perhaps this is your
connection that EM is
> using to show you the information?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Urban" <urban@.nospamplease> wrote in message
> news:7D887B89-D4DC-45E3-A2F3-2994CD423DA8@.microsoft.com...
>

Cannot Kill Process

SPID 84: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I've tried to kill this several times. It appears as though it should be
completed but it's hanging for some reason.
Any help would be appreciated.
Thanks!I found the process in windows task manager. Killing the process through
there allowed me to then kill it through the KILL (SPID) command.
"FK" wrote:

> SPID 84: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I've tried to kill this several times. It appears as though it should be
> completed but it's hanging for some reason.
> Any help would be appreciated.
> Thanks!

Cannot install SQL Server 2000 Developer edition on Windows XP Pro

Hi, I tried to install the SQL Server 2000 on
Windows XP Pro without success. There is
no error message either. The installation process
stalked on the server configuration step for
more then 30 minutes. I restarted the computer
and tried it again and it is still stalked there.
The server is Dell PowerEdge 400SC, P4 with
hyper threading.
Any ideas?
Thanks!
-Y.C.Yi,
have a look at this installation troubleshooting checklist, particularly the
reference to the 3 logfiles:
http://www.mssqlcity.com/Articles/Adm/installation_troubleshooting.htm
Regards,
Paul Ibison

Cannot insert explicit value for identity...

I am developing an integration process between two databases. One of
them is a SQL Server 2000 and the other is using MSDE 2000. The
integration process is done in C# (VS2003).

The main database is the SQL Server, the MSDE will contain a really
small subset of the data found on the main. To help diminish the amount
of time taken to develop an integration process between those
databases, the same structure are found on both side. The only
difference, when I insert data in the MSDE from the SQL Server, I set
the IDENTITY_INSERT to ON and use the same IDs found on the SQL Server.

I can insert one set of data without problem, but from there, if I try
again, I will always receive the "Cannot insert explicit value for
identity column in table ... when IDENTITY_INSERT is set to OFF." I
saw on Microsoft website the article ID 878501; I noticed I was using
MSDE sp3, I upgraded to SP4... and I still have the problem.

I know, when I call the update function on the sqldataadapter, the
adapters contain the IDENTITY_INSERT ON and it's set to OFF after the
insert. The "Cannot insert..." error is the only one I received.

Can anyone help me on that issue? Take note that this approach was
used because of customer requirements; the size of the database also
causes some problem (over 200 tables) and we decided to use the same
structure on both side to minimize the support time.Just thought about posting the SQL command in the adapter, it will look
something like:

SET IDENTITY_INSERT myTable ON; INSERT INTO myTable (myId, myCol1)
VALUES (@.myId, @.myCol1); SELECT myId, myCol1 FROM myTable WHERE (myId =
@.@.IDENTITY); SET IDENTITY_INSERT myTable OFF;

I hope that will help !|||(nicolas.bouchard@.gmail.com) writes:
> I am developing an integration process between two databases. One of
> them is a SQL Server 2000 and the other is using MSDE 2000. The
> integration process is done in C# (VS2003).
> The main database is the SQL Server, the MSDE will contain a really
> small subset of the data found on the main. To help diminish the amount
> of time taken to develop an integration process between those
> databases, the same structure are found on both side. The only
> difference, when I insert data in the MSDE from the SQL Server, I set
> the IDENTITY_INSERT to ON and use the same IDs found on the SQL Server.

The first reaction is: have you considered replication?

The second reaction is: skip IDENTITY, and generate the IDs on your
own. That is a trivial business. It's only if you have a high INSERT
rate from simultaneous processes that you need IDENTITY, as rolling
your own key can result in contention issues.

IDENTITY is convenient at times, but not in a situation like this. It
causes more problems than it solves.

Yet, an alternative is to use bulk load to extract and load data.
Moving one at time as you do not is not terribly effecient. Which may
not matter if the data size is moderate, but for something like
10-20 MB it could. With BCP it's easy to insert explicit IDENTITY
values with the -E option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,

thanks for the reply!

We did considered replication but for a reason unknown to me, we didn't
choose that solution; I can still imagine why, due to the fact we had
to develop an ownership process on certain tables, some tables are
owned by the main and others by the MSDE system and again, depending on
the state of the system.

The system (the main one) will be used by 200+ persons during the day,
the insert rate may be quite a problem with our own generated ids.

Until few days ago, I didn't know of bulk load, I will definitely look
into that for future projects. For this project, the amount of data
transfered between databases are not that big (talking less than 1MB
for the first transfer, then it will be almost nothing).

About my problem, I fixed it and I had few more tests to do; I was
planning to post the fix to my problem here, hoping it will help
someone else. I tried to do too much instructions in one command (my
guess is). Instead of doing everything in the same SQLCommand, I
modified the command to only manage the insert and the select to get
the new values; before calling the update command of the DataAdapter, I
disable the IDENTITY_INSERT then after the comand I enable it.

As I said, thanks for the quick answer !

Nicolas Bouchard

cannot insert data sometimes

The database sometimes cannot insert data through the visual basic 6.0 application. I don't know why it happens.
I watch the process id in the following path and find out many processes are in sleeping status.
In Enterprise Manager, ->SQL Server Group -> server name -> Management -> Current Activity -> Process Info

I guess the problem come from the maximum session of the process, but I am not familiar with the database infrastructure.
Whether has the maximum session of the procees? If have, What is the maximum session of the procees?

My environment is SQL Server 2000 Standard Edition on the Windows 2000 Server.

Thanks a lot.

Alex

>>The database sometimes cannot insert data through the visual basic 6.0 application

So what you get instead, then?

|||

gavrilenko_s

Thank you for your attention

I do nothing. The problem will restore automatically after a night or a few hours.

I make sure this is not the network problem because I verify the good status of network using "My Network Places".

addition informaiton:

MS SQL installed Service Pack 3

|||

Sounds like a blocking scenario. The current activity window isn't always a good indicator of blocking - you'll want to take a look at this support article for tips on tracking down the offending processes.

http://support.microsoft.com/kb/271509/en-us

|||I'd recommend you to use the Profiler to trace what client sends to the server and what's going on then while your application is unable to insert data. sp_who() and sp_lock() will help you to identify if there are any locks and blocks or not.|||

thanks you gavrilenko_s and JHalmans

I will use your suggestions to track the problem.

alex

Cannot fine the object “MSmerge_ctsv_... Because it does not exists or you do not have permissio

I keep getting the following error message during the initial merge agent.

· “The merge process was unable to deliver the snapshot to the subscriber.If using Web synchronization, the merge process may have been unable to create or write the message file, when troubleshooting, restart the sychronzation with verbose history loggin and specify an output file to write.

· Cannot fine the object “MSmerge_ctsv_B5ECD41086F24A64B8040A41A75B2DC4”, Because it does not exists or you do not have permission.(source: MSSQLServer, Error number 15151)

I do see this view in the publication and if I run the following command on the publisher I get…

select * from Msmerge_ctsv_B5ECD41086F24A64B8040A41A75B2DC4

I get

tablenick rowguid generation partchangegen lineage colv1 marker logical_record_parent_rowguid logical_record_lineage

(0 row(s) affected)

I have another publication working on this same server that is working, both are from a copy from a template database.Please advise to what this problem could be and what I can do to resolve this.

Thank you,

Pauly C

The issue was the mobile server name was changed but not dropped and re-added back so the values of@.@.servername and serverproperty(‘servername’) did not match. Once I fixed this everything replicated fine. Not a very intuitive error but if the error returns somethign like "Cannot fine the object “MSmerge_ctsv_..." check the subscriber server name by running

select @.@.servername as oldservername, serverproperty('servername') as newservername, case when @.@.servername <> serverproperty('servername') then '*** Error - Server name changed, replication will not work' else 'Replication friendly' end as cMessage

also this is a script I use to run the procs to drop and add the server...

Use Master

IF (select SERVERPROPERTY('ServerName')) <> (SELECT @.@.Servername )

begin

DECLARE @.SQL VARCHAR(256), @.oldsvrname varchar(128), @.newsvrname varchar(128)

select @.oldsvrname = cast(@.@.servername as varchar(128)), @.newsvrname = cast(SERVERPROPERTY('ServerName') as varchar(128))

set @.SQL = 'Sp_DropServer @.server = ''' + @.oldsvrname + ''', @.droplogins =''droplogins'''

exec( @.SQL)

SET @.SQL = 'Sp_Addserver @.server =''' + @.newsvrname + ''', @.local= ''LOCAL'' , @.duplicate_ok = ''duplicate_OK'' '

exec(@.SQL )

end

** must restart services at this point.

Thank you,

Pauly C

|||Maybe if you would run the agent with a higher OutputVerboseLevel, perhaps it may have given you clues.|||Actually I did that but it gave the same error message.