stored procedures

Hola, desde donde lo estas ejecutando?


Salu2

Microsoft MVP SQL Server
Culminis Speaker

"Norman" escribió en el mensaje
news:

Hola.
Tengo un Scrip.SQL, que me crea stored procedures...
al ejecutar el Scrip me da el error:

Msg 10060, Level 16, State 1, Line 0
TCP Provider: A connection attempt failed because the connected party
did not properly respond after a period of time, or established
connection failed because connected host has failed to respond.

(he parado y vuelto a arrancas el SQL (2005))

Este mismo Scrip lo he ejecutado en otros servidores y me crea los Stored
procedures sin ningun problema.

Alguna idea??

Nocreo qu4e sea el codigo, puesto que en otros servidores, misma version, que
tienen identica BBDD funciona

de todas formas:

USE [bbddrepli]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_Gupdates]
( @v_plaza char(2),
@v_job_id int,
@v_table_name varchar(50),
@v_result_code int output)

AS
Declare @v_rec_id int
Declare @v_rec_status tinyint
Declare @v_last_update datetime
Declare @v_update_interval int
Declare @v_sql_string nvarchar(4000)
Declare @v_sql_delete nvarchar(4000)
Declare @v_sql_insert nvarchar(4000)
Declare @v_identity_on nvarchar(100)
Declare @v_field_list varchar(4000)
Declare @v_field_name varchar(50)
Declare @v_key_field_name varchar(50)
Declare @v_update_type tinyint
Declare @v_server_name varchar(40)
Declare @v_rec_count int
Declare @v_msg varchar(255)
Declare @v_del_error int
Declare @v_ins_error int
Declare @v_error_count int
Declare @v_HostedDel bit
Declare @v_db_name varchar(20)

BEGIN

SET XACT_ABORT OFF

Select @v_HostedDel = bbddrepli.dbo.fn_HostedDel(@v_plaza)
If @v_HostedDel = 1
Set @v_db_name = 'alertran_'+@v_plaza
Else
Set @v_db_name = 'alertran'

Set @v_result_code = 0
Set @v_rec_count = 0
Set @v_error_count = 0


Select @v_last_update = isnull(last_update,CONVERT(datetime, '01/01/1900',
103)),
@v_update_interval = update_interval,
@v_update_type = update_type,
@v_key_field_name = key_field_name
from Update_Jobs
Where table_name = @v_table_name

If @v_HostedDel = 1
Declare cUpdates cursor for
Select Rec_ID,rec_status from bbddrepli.dbo.rec_updates
Where table_name = @v_table_name
and last_updated > @v_last_update
and central = 1
Else
Declare cUpdates cursor for
Select Rec_ID,rec_status from CENTRAL_TNT.bbddrepli.dbo.rec_updates
Where table_name = @v_table_name
and last_updated > @v_last_update
and central = 1

Set @v_sql_delete = 'Delete from
'+rtrim(@v_db_name)+'.dbo.'+@v_Table_name+' where _recno=@p_rec_id'
Set @v_sql_delete = 'SET LOCK_TIMEOUT 0; '+ @v_sql_delete

If @v_HostedDel = 1
Set @v_sql_string = 'Select @p_field_list =
rtrim(Alertran_TNT.dbo.fn_getFieldList(@p_table_name))'
else
Set @v_sql_string = 'Select @p_field_list =
rtrim(Alertran.dbo.fn_getFieldList(@p_table_name))'

exec sp_executesql @v_sql_string, N'@p_table_name
varchar(50),@p_field_list nvarchar(4000)
output',@v_table_name,@p_field_list=@v_field_list OUTPUT

Set @v_sql_insert = 'Insert into '+rtrim(@v_db_name)+'.dbo.'+
@v_table_name+ '('+@v_field_list+') '

If @v_HostedDel = 1
Set @v_sql_insert = @v_sql_insert + ' Select * from
alertran_tnt.dbo.'+rtrim(@v_table_name)
Else
Set @v_sql_insert = @v_sql_insert + ' Select * from
Central_TNT.alertran_tnt.dbo.'+rtrim(@v_table_name)


Set @v_sql_insert = @v_sql_insert + ' where _recno=@p_rec_id'
If @v_update_type = 3
Set @v_sql_insert = @v_sql_insert + ' and del_id= '''+ @v_plaza +''' '

Set @v_identity_on = N'SET IDENTITY_INSERT '+rtrim(@v_table_name)+' ON;'
Set @v_sql_insert = @v_identity_on + @v_sql_insert

Open cUpdates
Fetch cUpdates into @v_rec_id,@v_rec_status
While @@fetch_status=0
Begin
Set @v_ins_error = 0
Begin transaction
Set @v_sql_string = 'exec '+rtrim(@v_db_name)+'.dbo.sp_executesql
@p_sql_delete, N''@p_rec_id int'',@pv_rec_id'
exec sp_executesql @v_sql_string, N'@p_sql_delete
nvarchar(4000),@pv_rec_id int',@v_sql_delete,@v_rec_id

Set @v_del_error = @@error
If @v_del_error= 0 and @v_rec_status < 3
Begin
Set @v_sql_string = 'exec '+rtrim(@v_db_name)+'.dbo.sp_executesql
@p_sql_insert, N''@p_rec_id int'',@pv_rec_id'
exec sp_executesql @v_sql_string, N'@p_sql_insert
nvarchar(4000),@pv_rec_id int',@v_sql_insert,@v_rec_id
Set @v_ins_error = @@error
End

Set @v_rec_count = @v_rec_count + 1
If @v_del_error=0 and @v_ins_error=0
Begin
Commit
End
Else
Begin
RollBack
If @v_del_error = 1222
Set @v_msg = 'Proc: sp_Gupdates - Error updating
'+rtrim(@v_table_name)+'. Recno:'+rtrim(ltrim(str(@v_rec_id)))+' locked'
else
Set @v_msg = 'Proc: sp_Gupdates - Error
'+ltrim(rtrim(str(@v_del_error)))+' updating '+@v_table_name+'.
Recno:'+rtrim(ltrim(str(@v_rec_id)))

Exec sp_WriteErrorLog @v_plaza,@v_job_id ,@v_table_name,-1,@v_msg
Set @v_error_count = @v_error_count + 1
End
Fetch cUpdates into @v_rec_id,@v_rec_status
End

If @v_error_count = 0
Begin
Exec sp_WriteMsgLog @v_plaza,@v_job_id ,@v_table_name,0,'Proc: sp_Gupdates
- Execution: OK',@v_rec_count
End
Else
Begin
Set @v_result_code= -1
Set @v_msg = 'Proc: sp_Gupdates - Execution: Failed
-'+ltrim(rtrim(str(@v_error_count)))+' rec of
'+ltrim(rtrim(str(@v_rec_count)))+' with error'
Exec sp_WriteErrorLog @v_plaza,@v_job_id ,@v_table_name,-1,@v_msg
End

Close cUpdates
Deallocate cUpdates

END





"Alejandro Mesa" wrote:

Norman,

Pudieras postear el codigo?


AMB


"Norman" wrote:

> Hola.
> Tengo un Scrip.SQL, que me crea stored procedures...
> al ejecutar el Scrip me da el error:
>
> Msg 10060, Level 16, State 1, Line 0
> TCP Provider: A connection attempt failed because the connected party
> did not properly respond after a period of time, or established
> connection failed because connected host has failed to respond.
>
> (he parado y vuelto a arrancas el SQL (2005))
>
> Este mismo Scrip lo he ejecutado en otros servidores y me crea los Stored
> procedures sin ningun problema.
>
> Alguna idea??
>

Desde el:
SQL Server Management studio / "Open file" (habro el fichero script.SQL" y
lo ejecuto como una Query dando a: "! Execute"




"Maxi" wrote:

Hola, desde donde lo estas ejecutando?


Salu2

Microsoft MVP SQL Server
Culminis Speaker

"Norman" escribió en el mensaje
news:
> Hola.
> Tengo un Scrip.SQL, que me crea stored procedures...
> al ejecutar el Scrip me da el error:
>
> Msg 10060, Level 16, State 1, Line 0
> TCP Provider: A connection attempt failed because the connected party
> did not properly respond after a period of time, or established
> connection failed because connected host has failed to respond.
>
> (he parado y vuelto a arrancas el SQL (2005))
>
> Este mismo Scrip lo he ejecutado en otros servidores y me crea los Stored
> procedures sin ningun problema.
>
> Alguna idea??
>

Norman,

Pudieras postear el codigo?


AMB


"Norman" wrote:

Hola.
Tengo un Scrip.SQL, que me crea stored procedures...
al ejecutar el Scrip me da el error:

Msg 10060, Level 16, State 1, Line 0
TCP Provider: A connection attempt failed because the connected party
did not properly respond after a period of time, or established
connection failed because connected host has failed to respond.

(he parado y vuelto a arrancas el SQL (2005))

Este mismo Scrip lo he ejecutado en otros servidores y me crea los Stored
procedures sin ningun problema.

Alguna idea??

Es un error de conectividad.

Revisa los protocolos de red SQL Server que tienes en la máquina cliente y
en el servidor, usando la herramienta SQL Server Configuration Manager.


Gustavo Larriera, MVP
http://aspnet2.com/mvp.ashx?GustavoLarriera
http://solidqualitylearning.com/blogs/glarriera/
Este mensaje se proporciona tal como es, sin garantías de ninguna clase.



"Norman" wrote:

Hola.
Tengo un Scrip.SQL, que me crea stored procedures...
al ejecutar el Scrip me da el error:

Msg 10060, Level 16, State 1, Line 0
TCP Provider: A connection attempt failed because the connected party
did not properly respond after a period of time, or established
connection failed because connected host has failed to respond.

(he parado y vuelto a arrancas el SQL (2005))

Este mismo Scrip lo he ejecutado en otros servidores y me crea los Stored
procedures sin ningun problema.

Alguna idea??