Error 925 DB already open when i refresh current activity

I have 20 DB's on my server.
When i set a DB1 to single_user and Run DBCC ShrinkDatabase command.
Then start EM and refresh current activity I get an
Error 925: Database DB1 already open and can only have one user at a time.
I cannot see anything except (no items).
On other servers i set single user and i can view and refresh the current
activity
I don't get it why can't i view current activity on other databases?
Thanks for your helpAlex,
Try closing the connection via Query Analyzer and try again.
HTH
Jerry
"alex sadykov" <alexsadykov@.gmail.com> wrote in message
news:%239FX%23tMgGHA.5088@.TK2MSFTNGP02.phx.gbl...
>I have 20 DB's on my server.
> When i set a DB1 to single_user and Run DBCC ShrinkDatabase command.
> Then start EM and refresh current activity I get an
> Error 925: Database DB1 already open and can only have one user at a time.
>
> I cannot see anything except (no items).
> On other servers i set single user and i can view and refresh the current
> activity
> I don't get it why can't i view current activity on other databases?
> Thanks for your help
>|||Also you could try running sp_who to check the current users and processes
accessing the database.
Hope this helps
Vishal Gandhi

Error 925 DB already open when i refresh current activity

I have 20 DB's on my server.
When i set a DB1 to single_user and Run DBCC ShrinkDatabase command.
Then start EM and refresh current activity I get an
Error 925: Database DB1 already open and can only have one user at a time.
I cannot see anything except (no items).
On other servers i set single user and i can view and refresh the current
activity
I don't get it why can't i view current activity on other databases?
Thanks for your helpAlex,
Try closing the connection via Query Analyzer and try again.
HTH
Jerry
"alex sadykov" <alexsadykov@.gmail.com> wrote in message
news:%239FX%23tMgGHA.5088@.TK2MSFTNGP02.phx.gbl...
>I have 20 DB's on my server.
> When i set a DB1 to single_user and Run DBCC ShrinkDatabase command.
> Then start EM and refresh current activity I get an
> Error 925: Database DB1 already open and can only have one user at a time.
>
> I cannot see anything except (no items).
> On other servers i set single user and i can view and refresh the current
> activity
> I don't get it why can't i view current activity on other databases?
> Thanks for your help
>|||Also you could try running sp_who to check the current users and processes
accessing the database.
Hope this helps
Vishal Gandhi

Error 3930, The current transaction cannot be committed and cannot support

I am trying to exectue this store procedure and I am getting this error

Msg 3930, Level 16, State 1, Line 190

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

I am using a linked server "SODIM" and I am trying to execute a distributed transaction between my SQL Server 2005 Express Edition and this linked server SQL Server 2005 standar Edition.

I need to set the isolation level to serializable because I need to block tbl_GuiasDistribuidores table.

I`m executing this SP under sa user and I dont know if a need certanly permissions in the database or in the MS DTC.

I dont know what I`m missing, Could give me some help?

This is the sp code:

ALTERPROCEDURE [dbo].[PCN_ASIGNARGUIAS]

@.Planta varchar(15),

@.DB varchar(50)

AS

BEGIN

DECLARE @.lvaSentencia VARCHAR(8000)

set @.lvaSentencia ='

SET XACT_ABORT on

begin try

begin transaction

declare @.DisCodigo as varchar(15)

declare @.idProceso as int

declare @.guia as numeric

declare @.idDato as int

declare @.Estado as bit

declare @.GuiaFinal as numeric

declare @.AsignarGuia as varchar(2)

delete tbl_ErrorRangosDistribuidores

IF OBJECT_ID('+'''ErrorGuias'''+') IS NOT NULL

BEGIN

drop table ErrorGuias

END

insert into sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos

(Ars_Fecha,

Doc_Codigo,

Ars_NombreArchivo,

Ars_NombreArchivoSubido,

Ars_OrdenProduccion,

Ars_Requerimiento,

Ars_OrdenServicio,

Ars_Ciclo,

Ars_UsuarioUltimoCambioEfectuado,

Ars_FechaUltimoCambioEfectuado)

select Ars_Fecha,

Doc_Codigo,

Ars_NombreArchivo,

Ars_NombreArchivoSubido,

Ars_OrdenProduccion,

Ars_Requerimiento,

Ars_OrdenServicio,

Ars_Ciclo,

Ars_UsuarioUltimoCambioEfectuado,

Ars_FechaUltimoCambioEfectuado

from tbl_archivossubidos

SELECT @.idProceso=a.Ars_Codigo FROM sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos a, Tbl_ArchivosSubidos b

where a.Ars_FechaUltimoCambioEfectuado = b.Ars_FechaUltimoCambioEfectuado

and a.Ars_Fecha = b.Ars_Fecha

and a.Ars_NombreArchivo = b.Ars_NombreArchivo

and a.Ars_OrdenProduccion = b.Ars_OrdenProduccion

and a.Doc_Codigo = b.Doc_Codigo

declare CursorCodDistribuidor cursor for

select Dis_Codigo,IndiceOLlaveunica from VisOrdenamientoDatosFinalCiudades

order by Dis_Codigo

open CursorCodDistribuidor

fetch next from CursorCodDistribuidor

into @.DisCodigo,@.idDato

set @.Estado = 1

while @.@.fetch_status = 0

begin

SET TRANSACTION ISOLATION LEVEL Read COMMITTED

--SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

select @.Estado = Gdis_Estado from sodim.'+ @.DB +'.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)

where

Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'

while @.Estado = 1

begin

select @.Estado = Gdis_Estado from sodim.'+ @.DB +'.dbo.tbl_GuiasDistribuidores with(TABLOCKX SERIALIZABLE)

where

Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'

end

update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)

set GDis_Estado = 1

where

Dis_codigo = @.DisCodigo

and plan_Codigo = '+ @.Planta +'

select @.AsignarGuia = Dis_ImprimirGuia from sodim.'+ @.DB +'.dbo.tbl_Distribuidores --with(TABLOCKX SERIALIZABLE)

where Dis_Codigo = @.DisCodigo

if @.AsignarGuia = '+'''SI'''+'

begin

set @.guia = -1

--consulta si hay guias disponibles en la tabla Tbl_GuiasActivas

select @.guia = GAct_Guia from sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas

where dis_Codigo = @.DisCodigo

and plan_Codigo = '+ @.Planta +'

order by GAct_Guia desc

--si no hay guias disponibles en la tabla Tbl_GuiasActivas, la variable @.guia = -1

if @.guia = -1

begin

-- consulta el valor de GDis_RangoFinal y lo almacena en la variable @.GuiaFinal

select @.GuiaFinal = GDis_RangoFinal from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores

where Dis_codigo = @.DisCodigo

and Plan_Codigo = '+ @.Planta +'

-- consulta el valor de la siguiente guia disponible y lo almacena en la variable @.guia

select @.guia = convert(numeric,GDis_ultimaguia) + 1 from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores

where dis_Codigo = @.DisCodigo

and Plan_Codigo = '+ @.Planta +'

-- si @.guia > @.GuiaFinal no hay guias disponibles

if @.guia > @.GuiaFinal

BEGIN

--PRINT '+'''@.guia > @.GuiaFinal'''+'

--PRINT @.guia

--PRINT @.GuiaFinal

--consulta el valor de GDis_RangoFinalReserva y lo almacena en la variable @.GuiaFinal

select @.GuiaFinal = GDis_RangoFinalReserva from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores

where Dis_codigo = @.DisCodigo

and Plan_Codigo = '+ @.Planta +'

--si @.GuiaFinal = 0 no existen guias en el rango de reserva (no hay guias para asignar)

if @.GuiaFinal = 0

begin

--PRINT '+'''entro error guias'''+'

insert into Tbl_ErrorRangosDistribuidores

(Dis_Codigo,error)

values

(@.DisCodigo,'+'''Se agot el rango principal y de reserva para el Distribuidor'''+')

--PRINT '+'''despues del insert'''+'

RAISERROR (@.DisCodigo, 16, 1)

end

--no hay guias para asignar pero si hay guias en reserva

else

BEGIN

insert into sodim.'+ @.DB +'.dbo.tbl_HistoricoRangoGuias

(Dis_Codigo,HR_RangoInicial,HR_RangoFinal)

select Dis_Codigo,GDis_RangoInicial,GDis_RangoFinal from

sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores

where dis_Codigo = @.DisCodigo

and Plan_Codigo = '+ @.Planta +'

--asigna las guias de reserva al rango principal

update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores

set

GDis_RangoInicial = GDis_RangoInicialReserva

,GDis_RangoFinal = GDis_RangoFinalReserva

,GDis_UltimaGuia = GDis_RangoInicialReserva

,GDis_RangoInicialReserva = 0

,GDis_RangoFinalReserva = 0

where dis_Codigo = @.DisCodigo

and Plan_Codigo = '+ @.Planta +'

--selecciona la ultima guia disponible y la almacena en la variable @.guia

select @.guia = GDis_UltimaGuia from sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores

where dis_codigo = @.DisCodigo

and Plan_Codigo = '+ @.Planta +'

update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores

set

GDis_ultimaguia = @.guia

where dis_Codigo = @.DisCodigo

and Plan_Codigo = '+ @.Planta +'

insert into Tbl_ErrorRangosDistribuidores

(Dis_Codigo,error)

values

(@.DisCodigo,'+'''se agoto el rango principal para el Distribuidor'''+')

end

end

--no hay guias disponibles en la tabla tbl_guiasActivas y si hay guias disponibles del distribuidor

else

BEGIN

--PRINT '+'''graba ultima guia'''+'

--PRINT @.guia

update sodim.'+ @.DB +'.dbo.Tbl_GuiasDistribuidores

set

GDis_ultimaguia = @.guia

where dis_Codigo = @.DisCodigo

and Plan_Codigo = '+ @.Planta +'

end

end

else

BEGIN

--PRINT '+'''elimina Guia activa'''+'

--PRINT @.guia

delete sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas

where GAct_Guia = @.guia

and dis_Codigo = @.DisCodigo

END

--PRINT '+'''guarda la Guia en Guia asignada'''+'

insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas

(Ars_Codigo,Dis_Codigo,GA_Guia,Plan_Codigo)

values

(@.idProceso,@.DisCodigo,@.guia,'+ @.Planta +')

--PRINT '+'''guarda la Guia en datos subidos'''+'

--PRINT @.guia

update dbo.Tbl_datosSubidos

set Guia = @.guia

where IndiceOLlaveunica = @.idDato

END

update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)

set GDis_Estado = 0

where

Dis_codigo = @.DisCodigo and plan_Codigo = '+ @.Planta +'

--PRINT '+'''lee otro registro del cursor'''+'

fetch next from CursorCodDistribuidor

into @.DisCodigo,@.idDato

--PRINT '+'''despues del commit'''+'

end

commit transaction

end try

--manejo de errores

begin catch

insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas

(Dis_Codigo,GAct_Guia,Plan_Codigo)

select Dis_Codigo,GA_Guia,plan_Codigo from sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas

where Ars_Codigo = @.idProceso

update Tbl_DatosSubidos

set

guia = '+'''SIN DATOS'''+'

update sodim.'+ @.DB +'.dbo.tbl_guiasDistribuidores with(TABLOCKX SERIALIZABLE)

set GDis_Estado = 0

--PRINT '+'''mostrar error'''+'

select

ERROR_NUMBER() AS Numero,

ERROR_LINE() AS Linea,

ERROR_MESSAGE() AS Mensaje

into ErrorGuias

rollback transaction

end catch

close CursorCodDistribuidor

deallocate CursorCodDistribuidor

delete sodim.'+ @.DB +'.dbo.Tbl_GuiasAsignadas

where Ars_Codigo = @.idProceso

delete sodim.'+ @.DB +'.dbo.Tbl_ArchivosSubidos

where Ars_Codigo = @.idProceso

SET TRANSACTION ISOLATION LEVEL Read COMMITTED'

--PRINT @.lvaSentencia

EXEC(@.lvaSentencia)

end

try rolling back the failed transaction before you update data in your catch() block.

e.g.

begin catch

if xact_state()=-1 rollback

insert into sodim.'+ @.DB +'.dbo.Tbl_GuiasActivas

error 3073 problem

Hi I have a problem with inserting new data for a newly connected database. What i do is i have a current connection open and running. When the user picks another database source from an OpenFileDialog, I create a temporary variable to test the database connection (from the openfiledialog) and if it can connect then i close the previous connected database and connect to the new database. I close the previous database and set the new database into one and the same variable.

The problem is when i connect to the new database, and am going to insert some new values to a certain table. It shows the error something like:

error 3073. An updatable query is necessary.

I have checked the data source of the database and it contains the path where the new connection is. The sql query is also valid because i have checked it in the ms access query builder. The permission is also valid since it is in write permission.

Hope anyone has an idea for my problem. Thanks in advance!

Please post the query.

SQL Server and Access have syntax differences in updating.

For example:

Access

--

Update table1 t1, table2 t2

SET t1.ID = t2.ID

...this will not work in SQL

SQL Server

--

Update t1

SET t1.ID = t2.ID

FROM Table1 t1

LEFT JOIN Table2 t2 on t1.ID = t2.ID

So testing the query in Access might fail in SQL Server.

Adamus

|||Here is the sample sql:

INSERT INTO [table name](ID, DIVISION, USER_NAME, EXEC_TIME, [CONTENTS]) VALUES (1, 'Division', 'user', '2005/05/19 10:45:01', 'Comments.');

After I call the OleDbCommand to ExecuteNonQuery, this error occurs. I am using MS Access by the way. Thanks!
|||Could you please the code part which is relevant (including initialization for the command) ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Technically this is not a SQL Server issue (since you're using Access), but please post the full ADO coding.

Adamus

|||Sorry for the confusion I was also hoping someone in this forum could also help me in my problem. I was thinking that this error is not dependent on what type database you use. But thanks still for the effort. This is another thread I created that has the necessary codes involved.

-> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1833561&SiteID=1

Please feel free to take a look. If you are going to reply kindly reply to this thread (SQL Server) instead so the threads will be more organized (opinions of the people in a particular expertise) and i won't get more confused Thanks a lot!