jmpayaalvare
03-08-2013, 05:04 AM
Hi all, here comes my tough one. I think is pretty difficult to solve this via Forum, but I'm fed up with this problem, really.
I'm running a stored procedure asynchronously from a vba piece of code in a SQL Server 2005 Server. I know that adAsyncExecute option gives you the opportunity to call a stored procedure and don't lose the control over your form or whatever the code has to do.
I've got two different SP's wich can be executed. Wether is one or the other is up to the main stored procedure which is really the stored procedure called from VBA. It depends on some parameter data stored in a concrete SQL Server table.
Both sp's are quite long procedures and take their time to get finished. One of them finishes even if it's called many times during a execution. The other doesn't. It seems to miss the connection but i don't have de possibility to find out. The log table I've designed seems to stop recieving data when this SP's is executed.
It doesn't seem to be a problem with the stored procedure, because if I run it from SQL Server Management Studio, it works fine!!! The way I'm doing it from the VBA is creating two global variables (ADO Connection and ADO Command). They aren't set to nothing at any time, so the connection shouldn't be lost and it should end the execution, as it does with the other stored procedure.
This is the situation:
Public cntGlobal As ADODB.connection
Public cmdGlobal As ADODB.Command
Public Sub execRegistroCurrParam(ByVal currParam As CMedida)
'This procedure starts the data register SQL sp_TME_ExecRegistroManager
Set cntGlobal = New ADODB.connection
Set cmdGlobal = New ADODB.Command
With cntGlobal
.ConnectionString = stCon
.CursorLocation = adUseClient
.Open
End With
'Ejecutamos el registro de la medida de TME_TblCurrParam
cmdGlobal.ActiveConnection = cntGlobal
cmdGlobal.CommandText = "exec SWQ_Servicios.dbo.sp_TME_ExecRegistroManager " & currParam.ParamId
cmdGlobal.Execute Options:=adAsyncExecute
' Debug.Print "Registrando " & currParam.Base & " - " & currParam.TipoMedida & " - " & currParam.CollectionName & "..."
frmGestionReg.lblCurrRegister.Caption = currParam.Base & " --> " & currParam.CollectionName
End Sub
What could be the reason?? is there any known causes that could cut off this connection in some concrete cases?? Why would I be able to execute with no problems in SQL Server 2005 Management Studio?? This is one piece of the body of the sp_TME_ExecRegistroManager
IF NOT EXISTS (select * from SWQ_Servicios.dbo.TME_ImporState)
BEGIN
INSERT INTO SWQ_Servicios.dbo.TME_LogRegister (MsgTime, [Message]) VALUES (convert(varchar,getdate(),121), 'Registrando ' + @base + ' - ' + @tipoMedida + ' - ' + @collectionName + '...')
-- Comenzamos el registro de la medida
IF (@tipoMedida='DUAL')
EXECUTE sp_TME_ExecRegistroVozManager;
ELSE IF (@tipoMedida='DATOS')
EXECUTE sp_TME_ExecRegistroDatosManager;
-- No cambiar el mensaje de información sin cambiar la programación del TimerControl de VBA
insert into SWQ_Servicios.dbo.TME_LogRegister (MsgTime, [Message]) values (convert(varchar,getdate(),121), 'Medida registrada.')
END
ELSE
BEGIN
declare @importType varchar(100)
select @importType=Description from SWQ_Servicios.dbo.TME_ImporState
INSERT INTO SWQ_Servicios.dbo.TME_LogRegister (MsgTime, [Message]) VALUES (convert(varchar,getdate(),121), 'No se registraron datos en ' + @base + ' debido a tareas en curso: ' + @importType);
INSERT INTO SWQ_Servicios.dbo.TME_LogRegister (MsgTime, [Message]) VALUES (convert(varchar,getdate(),121), 'Medida no registrada.');
END
sp_TME_ExecRegistroVozManager runs well and doen't get cut off. sp_TME_ExecRegistroDatosManager seems to lose the connection, and never finish the execution if I call it from VBA, but it has no problem form SQL Server Management Studio.
I know is hard to explain better and even more to understand properly. Please ask for as many extra information as you need in order to help me out. I know is going to be hard.
By the way, I'm deeply sorry for the spanish-coding posted
Help meeeeee!!! :banghead:
I'm running a stored procedure asynchronously from a vba piece of code in a SQL Server 2005 Server. I know that adAsyncExecute option gives you the opportunity to call a stored procedure and don't lose the control over your form or whatever the code has to do.
I've got two different SP's wich can be executed. Wether is one or the other is up to the main stored procedure which is really the stored procedure called from VBA. It depends on some parameter data stored in a concrete SQL Server table.
Both sp's are quite long procedures and take their time to get finished. One of them finishes even if it's called many times during a execution. The other doesn't. It seems to miss the connection but i don't have de possibility to find out. The log table I've designed seems to stop recieving data when this SP's is executed.
It doesn't seem to be a problem with the stored procedure, because if I run it from SQL Server Management Studio, it works fine!!! The way I'm doing it from the VBA is creating two global variables (ADO Connection and ADO Command). They aren't set to nothing at any time, so the connection shouldn't be lost and it should end the execution, as it does with the other stored procedure.
This is the situation:
Public cntGlobal As ADODB.connection
Public cmdGlobal As ADODB.Command
Public Sub execRegistroCurrParam(ByVal currParam As CMedida)
'This procedure starts the data register SQL sp_TME_ExecRegistroManager
Set cntGlobal = New ADODB.connection
Set cmdGlobal = New ADODB.Command
With cntGlobal
.ConnectionString = stCon
.CursorLocation = adUseClient
.Open
End With
'Ejecutamos el registro de la medida de TME_TblCurrParam
cmdGlobal.ActiveConnection = cntGlobal
cmdGlobal.CommandText = "exec SWQ_Servicios.dbo.sp_TME_ExecRegistroManager " & currParam.ParamId
cmdGlobal.Execute Options:=adAsyncExecute
' Debug.Print "Registrando " & currParam.Base & " - " & currParam.TipoMedida & " - " & currParam.CollectionName & "..."
frmGestionReg.lblCurrRegister.Caption = currParam.Base & " --> " & currParam.CollectionName
End Sub
What could be the reason?? is there any known causes that could cut off this connection in some concrete cases?? Why would I be able to execute with no problems in SQL Server 2005 Management Studio?? This is one piece of the body of the sp_TME_ExecRegistroManager
IF NOT EXISTS (select * from SWQ_Servicios.dbo.TME_ImporState)
BEGIN
INSERT INTO SWQ_Servicios.dbo.TME_LogRegister (MsgTime, [Message]) VALUES (convert(varchar,getdate(),121), 'Registrando ' + @base + ' - ' + @tipoMedida + ' - ' + @collectionName + '...')
-- Comenzamos el registro de la medida
IF (@tipoMedida='DUAL')
EXECUTE sp_TME_ExecRegistroVozManager;
ELSE IF (@tipoMedida='DATOS')
EXECUTE sp_TME_ExecRegistroDatosManager;
-- No cambiar el mensaje de información sin cambiar la programación del TimerControl de VBA
insert into SWQ_Servicios.dbo.TME_LogRegister (MsgTime, [Message]) values (convert(varchar,getdate(),121), 'Medida registrada.')
END
ELSE
BEGIN
declare @importType varchar(100)
select @importType=Description from SWQ_Servicios.dbo.TME_ImporState
INSERT INTO SWQ_Servicios.dbo.TME_LogRegister (MsgTime, [Message]) VALUES (convert(varchar,getdate(),121), 'No se registraron datos en ' + @base + ' debido a tareas en curso: ' + @importType);
INSERT INTO SWQ_Servicios.dbo.TME_LogRegister (MsgTime, [Message]) VALUES (convert(varchar,getdate(),121), 'Medida no registrada.');
END
sp_TME_ExecRegistroVozManager runs well and doen't get cut off. sp_TME_ExecRegistroDatosManager seems to lose the connection, and never finish the execution if I call it from VBA, but it has no problem form SQL Server Management Studio.
I know is hard to explain better and even more to understand properly. Please ask for as many extra information as you need in order to help me out. I know is going to be hard.
By the way, I'm deeply sorry for the spanish-coding posted
Help meeeeee!!! :banghead: