remove cursor

hi below is my procedure..i don't want to use cursor....than what should be my query?

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

DECLARE @.Bank_Name VARCHAR(50)
DECLARE @.MICR_CMSCode varchar(50),@.MICR_SchemeGroup varchar(50)

--BANK CURSOR
DECLARE CUR_BANK CURSOR FOR
SELECT Bank_Name FROM Tbl_BankMst where Bank_isactive=1

OPEN CUR_BANK

FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
WHILE @.@.FETCH_STATUS=0
BEGIN

--print(@.Bank_Name)
--MICR CURSOR
DECLARE CUR_MICR cursor for
--Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_BankName='ICICI BANK LTD'
Select MICR_CMSCode,MICR_SchemeGroup From Tbl_MICRMst WHERE MICR_AuthStatus =2 and MICR_Optype =0 and MICR_BankName=rtrim(ltrim(@.Bank_Name))

Open CUR_MICR

Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup
while @.@.fetch_status = 0
begin
update Tbl_CMS_UploadDetails set CMS_SchemeGroup =@.MICR_SchemeGroup Where Scheme_Code=rtrim(ltrim(@.MICR_CMSCode))

--print(@.MICR_SchemeGroup)--@.MICR_CMSCode
Fetch Next from CUR_MICR into @.MICR_CMSCode,@.MICR_SchemeGroup

end
close CUR_MICR
deallocate CUR_MICR

- update compare status and maker-

declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName
--select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name='ICICI BANK LTD'

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID
-
FETCH NEXT FROM CUR_BANK INTO @.Bank_Name
END

close CUR_BANK
deallocate CUR_BANK

hi try this..

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1
--and a.MICR_BankName = rtrim(ltrim(@.Bank_Name))
) d
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

END
GO|||oops, i forgot to add the on clause..

ALTER PROCEDURE Usp_CMSUpdateSchemGroup
(
@.CMS_Upload_Master_ID numeric =null,
@.Maker numeric =null,
@.BnkName varchar(50)=null
)
AS
BEGIN

update Tbl_CMS_UploadDetails
set CMS_SchemeGroup = d.MICR_SchemeGroup
from Tbl_CMS_UploadDetails INNER JOIN
(
Select a.MICR_CMSCode
, a.MICR_SchemeGroup
From Tbl_MICRMst a INNER JOIN
Tbl_BankMst b ON a.MICR_BankName = rtrim(ltrim(b.Bank_Name))
WHERE a.MICR_AuthStatus = 2
and a.MICR_Optype = 0
and b.Bank_isactive=1
--and a.MICR_BankName = rtrim(ltrim(@.Bank_Name))
) d ON Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
where Tbl_CMS_UploadDetails.Scheme_Code = rtrim(ltrim(d.MICR_CMSCode))
declare @.Format_ID numeric
select @.Format_ID=DataFormat_ID from tbl_bankmst where Bank_Name=@.BnkName

print @.Format_ID --+ @.Bank_Name

update Tbl_CMS_UploadDetails
set Maker=@.Maker,
Make_Date=getdate(),
AuthStatus=2,
Optype=0,
Compare_Status ='Pending',
Format_ID=@.Format_ID
Where CMS_Upload_Master_ID=@.CMS_Upload_Master_ID

END
GO|||thanx let me try i will be back.sql

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.
You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>
|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0-9]%',SUBSTRING(@.InputString,@.i+1,len(@.InputString))) ,0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,' (',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]
|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,'(',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0-9]%',SUBSTRING(@.InputString,@.i+1,len(@.InputString))),0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHONENUMBER,'(',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> > Hello.
> >
> > How can I write some stored procedure or function to which I will pass =the
> > string and it will return me only numbers from my string?
> > Exactly I need to remove all non digit characters from some string.
> >
> > Tank you.
> >|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove all non digit characters from some string

Hello.
How can I write some stored procedure or function to which I will pass the
string and it will return me only numbers from my string?
Exactly I need to remove all non digit characters from some string.
Tank you.You could write an extended stored proc in a dll...dont know if this best
solution though
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||David,
If you have a fixed set of characters to remove i.e., like with a phone
number you could do something like this:
DECLARE @.TELEPHONENUMBER VARCHAR(25)
SET @.TELEPHONENUMBER = '(503)999-1851'
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
',''),'-','')
HTH
Jerry
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello.
> How can I write some stored procedure or function to which I will pass the
> string and it will return me only numbers from my string?
> Exactly I need to remove all non digit characters from some string.
> Tank you.
>|||Hi,
This might not be the most efficient way of doing it but it works :-)
DECLARE @.InputString varchar(32)
DECLARE @.OutputString varchar(32)
DECLARE @.i int
SET @.InputString =3D 'ABC123!=A3$456'
SET @.i =3D NULLIF(PATINDEX('%[0-9]%',@.InputString),0)
WHILE @.i IS NOT NULL
BEGIN
SET @.OutputString =3D isnull(@.OutputString,'') +
SUBSTRING(@.Inputstring, @.i, 1)
SET @.i =3D @.i +
NULLIF(PATINDEX('%[0- 9]%',SUBSTRING(@.InputString,@.i+1,len(@.In
putString))
),0)
END
SELECT @.OutputString -- This gives '123456'
I would normally use a auxilary numbers table instead of the loop, but
didn't have time to go through that in this example.
Jerry Spivey wrote:[vbcol=seagreen]
> David,
> If you have a fixed set of characters to remove i.e., like with a phone
> number you could do something like this:
> DECLARE @.TELEPHONENUMBER VARCHAR(25)
> SET @.TELEPHONENUMBER =3D '(503)999-1851'
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(@.TELEPHO
NENUMBER,'(',''),')',''),'
> ',''),'-','')
> HTH
> Jerry
> "David Dvali" <david_dvali@.hotmail.com> wrote in message
> news:%23iSW2D$0FHA.2348@.TK2MSFTNGP15.phx.gbl...
the[vbcol=seagreen]|||This should do the trick...
DECLARE @.Index smallint
DECLARE @.SearchString varchar(25)
DECLARE @.StringLength smallint
DECLARE @.CurrentChar Char(1)
SET @.searchstring = 'ab35d0l2rle.p1C9,:$#47)('
SET @.StringLength = LEN(@.SearchString)
SET @.Index = 1
WHILE @.Index <= @.StringLength
BEGIN
SET @.CurrentChar = SUBSTRING(@.SearchString, @.index, 1)
if @.CurrentChar NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
BEGIN
SET @.SearchString = REPLACE(@.SearchString, @.CurrentChar, 'X')
END
SET @.Index = @.Index + 1
END
SELECT REPLACE(@.SearchString, 'X','')
Returns 35021947
Replace all the non-numeric values with 'X' (so as to not screw up the WHILE
index,) then replace all Xs with an empty string. In a stored proc,
@.SearchString would be the parameter...
HTH,
Mike

Remove a constraint

I executed the following sp several times

ALTER PROCEDURE [dbo].[sp_GetItemsByCategoryOnPage]

AS

BEGIN

CREATE TABLE #TemporaryItems

(

ID int IDENTITY PRIMARY KEY,

...

[auto] [bit] NULL CONSTRAINT [DF_CostupdateItems_auto] DEFAULT ((1)),

...

)

...

END

Suddlenly I got a message:

The constraint [DF_CostupdateItems_auto] already exists. How can I remove it from DB?

Hi there,

You can use such a statement :

alter table YourTableNameWithThisConstraint drop constraint [DF_CostupdateItems_auto]

The constraints store in each Table's structures not in global.

__
May the God blessings be.

LotraSoft Ltd.

|||You will get the error message if multiple connections call the SP simultaneously. Even though the temporary table name is uniquefied automatically by SQL Server since you have a named constraint for the default you can't only execute the SP serially. So either remove the constraint name from the column definition so that SQL Server can automatically generate a unique name or don't call the SP concurrently.

Remote-server execution of a global temp stored procedure

I have the following execution of a global temporary stored procedure on a remote SQL 2000 server:

insert into targetTable
exec remoteServer.master.dbo.sp_MSforeachdb ' ', @.precommand = 'exec ##up_fetchQuery'

This is an ugly duck query but it seems to work fine. when I try to directly execute the remote stored procedure such as with

insert into query_log exec remoteServer.master.dbo.##up_fetchQuery

I get execution error

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'master' ignored, referencing object in tempdb.

When I try

insert into query_log exec remoteServer.tempdb.dbo.##up_fetchQuery

I get

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.
Database name 'tempdb' ignored, referencing object in tempdb.

with

insert into query_log exec remoteServer..dbo.##up_fetchQuery

or

insert into query_log exec remoteServer...##up_fetchQuery

I get

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure '##up_xportQueryLog'.

I guess the remote server has trouble resolving the name of the global temp stored procedure when its reference comes in as a remote stored procedure calls. Is there any way to directly call a global temp stored procedure from a remote server or do I need to stick with this goofy-looking work-around?

Dave

You can do below instead of using the undocumented system SP "sp_MSforeachdb".

insert into targetTable
exec remoteServer.master.dbo.sp_executesql N'exec ##up_fetchQuery'

|||

Thank you. (Laughing at myself; I thought of everything except the obvious; oh, brother!)

It definitely helps to have another set of eyes look at something.

Dave

Remotely Executing a Stored Proc

I have an ASP page that executes a stored procedure on the local SQL Server instance, but I need that stored proc to execute another stored proc on a remote server. Basically, proc A on server 1 executes and passes a parameter to proc B on server 2. Is there a way to do this without linking server 2?
TIAPlease take a look at the linked server functionality. This allows you to execute a distributed query or call a remote stored procedure.

remote system call?

Hi all,
I want to be able to have a stored procedure on server A to call a
stored procedure on Server B. I have my stored procedures in place. I have
used Linked servers on server A to define Server B so, that server A can
talk to server B. However I seem to have missed something. This is the
message that I get:
Could not find stored procedure 'sp_tt_load'.(42000,2812)
Procedure(sp_tt_dumpandload_for_standby).
sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
sp_tt_load ( server B). Server B will load the dump onto it's standby
database.
TIA for all the help.
Red
make sure you are using the full name to call the proc including the owner
name eg:
ServerB.databasename.dbo.sp_tt_load
"Red" <RedWolf_56@.yahoo.com> wrote in message
news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I
have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then
calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>
|||Thanks Mary, for the advise.
Actually, I found the error in my linked server configuration.
I had defined it using 'other data source' of Microsoft OLE DB provider for
SQL Server.
I should have used 'SQL Server' and then name the server.
Once I changed this, then I was able to make the stored procedure call,
which looks like this:
exec SERVERB...sp_tt_db_load
"Mary Bray" <reply@.tonewsgroup.com.NOSPAMPLEASE> wrote in message
news:eVuQWIDnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> make sure you are using the full name to call the proc including the owner
> name eg:
> ServerB.databasename.dbo.sp_tt_load
> "Red" <RedWolf_56@.yahoo.com> wrote in message
> news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> have
> calls
>
|||Lookup four part naming conventions which should help.
[server].[catalog].[owner].[object]
Adrian
Red wrote:

> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>
sql

remote system call?

Hi all,
I want to be able to have a stored procedure on server A to call a
stored procedure on Server B. I have my stored procedures in place. I have
used Linked servers on server A to define Server B so, that server A can
talk to server B. However I seem to have missed something. This is the
message that I get:
Could not find stored procedure 'sp_tt_load'.(42000,2812)
Procedure(sp_tt_dumpandload_for_standby).
sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
sp_tt_load ( server B). Server B will load the dump onto it's standby
database.
TIA for all the help.
Redmake sure you are using the full name to call the proc including the owner
name eg:
ServerB.databasename.dbo.sp_tt_load
"Red" <RedWolf_56@.yahoo.com> wrote in message
news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I
have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then
calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>|||Thanks Mary, for the advise.
Actually, I found the error in my linked server configuration.
I had defined it using 'other data source' of Microsoft OLE DB provider for
SQL Server.
I should have used 'SQL Server' and then name the server.
Once I changed this, then I was able to make the stored procedure call,
which looks like this:
exec SERVERB...sp_tt_db_load
"Mary Bray" <reply@.tonewsgroup.com.NOSPAMPLEASE> wrote in message
news:eVuQWIDnEHA.2680@.TK2MSFTNGP15.phx.gbl...
> make sure you are using the full name to call the proc including the owner
> name eg:
> ServerB.databasename.dbo.sp_tt_load
> "Red" <RedWolf_56@.yahoo.com> wrote in message
> news:%23D2fn0CnEHA.2140@.TK2MSFTNGP11.phx.gbl...
> > Hi all,
> > I want to be able to have a stored procedure on server A to call a
> > stored procedure on Server B. I have my stored procedures in place. I
> have
> > used Linked servers on server A to define Server B so, that server A can
> > talk to server B. However I seem to have missed something. This is the
> > message that I get:
> > Could not find stored procedure 'sp_tt_load'.(42000,2812)
> > Procedure(sp_tt_dumpandload_for_standby).
> >
> >
> > sp_tt_dumpandload_for_standby (server A) does a database dump and then
> calls
> > sp_tt_load ( server B). Server B will load the dump onto it's standby
> > database.
> >
> > TIA for all the help.
> >
> > Red
> >
> >
>|||Lookup four part naming conventions which should help.
[server].[catalog].[owner].[object]
Adrian
Red wrote:
> Hi all,
> I want to be able to have a stored procedure on server A to call a
> stored procedure on Server B. I have my stored procedures in place. I have
> used Linked servers on server A to define Server B so, that server A can
> talk to server B. However I seem to have missed something. This is the
> message that I get:
> Could not find stored procedure 'sp_tt_load'.(42000,2812)
> Procedure(sp_tt_dumpandload_for_standby).
>
> sp_tt_dumpandload_for_standby (server A) does a database dump and then calls
> sp_tt_load ( server B). Server B will load the dump onto it's standby
> database.
> TIA for all the help.
> Red
>

Remote Stored Procedure exec from variable

Hello Folks,

I have a server that is linked to several other servers. I have been able to successfully execute a procedure manually to each from the common server by executing this:

exec server1.dbname.owner.procedure
go
exec server2.dbname.owner.procedure
go
exec server3.dbname.owner.procedure
go

While this is ok, I'd like to wrap this in a loop to execute the procedure but switch out the server name in each iteration (something like this):

while @.variable is not null
begin
select @.server = 'change name in loop'
select @.str = @.server+'.dbname.owner.procedure'
exec @.str
end

This unfortunately does not work. The execute is acting like the server portion of the name does not exist (defaulting to local). I have attempted to use the AT SERVERNAME syntax in a similar fashion and been unsuccessful.

Is there some way I could dynamically create the four part name and execute it?

Any assistance would be greatly appreciated.

Thanks, Mark

DECLARE @.server nvarchar(128)

DECLARE @.cmd nvarchar(1000)

SET @.server = 'MyServer'

SET @.cmd = 'EXEC ' + @.server + '.MyDatabase.MySchema.MySproc'

EXEC (@.cmd)

Remote stored procedure

I am trying to run a stored procedure on a remote server and insert the
output into a table on the local machine.
The two servers are linked and I am using the code as follows
BEGIN DISTRIBUTED TRAN
insert into ServerA.Database.dbo.drv_total
exec ServerB.master.dbo.xp_fixeddrives
COMMIT TRAN
The MSDTC is started on both machines.
I am getting this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
Any thoughts?
TIA,
nivek
Hi Nivek,
Are you running on Windows 2003? If so, the following link may apply to you:
http://support.microsoft.com/default...b;en-us;329332
"The problem occurs because Microsoft Distributed Transaction Coordinator
(MS DTC) is not configured for network access. By default, the network access
settings of MS DTC are disabled on new installations of SQL Server 2000 on
computers running Windows Server 2003, Enterprise Edition. "
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"nivek" wrote:

> I am trying to run a stored procedure on a remote server and insert the
> output into a table on the local machine.
> The two servers are linked and I am using the code as follows
> BEGIN DISTRIBUTED TRAN
> insert into ServerA.Database.dbo.drv_total
> exec ServerB.master.dbo.xp_fixeddrives
> COMMIT TRAN
> The MSDTC is started on both machines.
> I am getting this error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
>
> Any thoughts?
>
> TIA,
> nivek
>
>
sql

Remote stored procedure

I am trying to run a stored procedure on a remote server and insert the
output into a table on the local machine.
The two servers are linked and I am using the code as follows
BEGIN DISTRIBUTED TRAN
insert into ServerA.Database.dbo.drv_total
exec ServerB.master.dbo.xp_fixeddrives
COMMIT TRAN
The MSDTC is started on both machines.
I am getting this error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
Any thoughts?
TIA,
nivekHi Nivek,
Are you running on Windows 2003? If so, the following link may apply to you:
http://support.microsoft.com/default.aspx?scid=kb;en-us;329332
"The problem occurs because Microsoft Distributed Transaction Coordinator
(MS DTC) is not configured for network access. By default, the network access
settings of MS DTC are disabled on new installations of SQL Server 2000 on
computers running Windows Server 2003, Enterprise Edition. "
Best Regards,
Joe Sack
Author of "SQL Server 2000 Fast Answers..."
http://www.JoeSack.com
"nivek" wrote:
> I am trying to run a stored procedure on a remote server and insert the
> output into a table on the local machine.
> The two servers are linked and I am using the code as follows
> BEGIN DISTRIBUTED TRAN
> insert into ServerA.Database.dbo.drv_total
> exec ServerB.master.dbo.xp_fixeddrives
> COMMIT TRAN
> The MSDTC is started on both machines.
> I am getting this error:
> Server: Msg 7391, Level 16, State 1, Line 2
> The operation could not be performed because the OLE DB provider 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
>
> Any thoughts?
>
> TIA,
> nivek
>
>

Remote SQL Debug: Server cannot connect to the debugger on my mach

Hello,
I am trying to debug a stored procedure on remote server. The debugger
starts and passes through without stopping.
No error messages are generated on the client.
On the server the following error appears in the Application Log:
Event Type: Error
Event Source: SQLDebugging98
Event Category: None
Event ID: 1
Date: 10/7/2005
Time: 4:41:47 PM
User: N/A
Computer: <server>
Description:
SQL Server is running as '<domain account>' and cannot connect to the
debugger on machine '<my machine>' (error = 0x80070005 Access is denied. ).
Use one of the following options to fix this error. 1) Run SQL Server as
"Local System", as a domain account, or as a local account with identical
usernames and passwords on both machine '<server>' and '<my machine>'. 2)
Verify that machine '<server>' can open files on machine '<my machine>'.
Debugging disabled for connection 55.
Server is Windows 2003 Standard running SQL 2000 SP4 with .NET remote
debugging support.
Client is Windows XP Pro SP2 running SQL 2000 SP4 and .NET 1.1 SP1.
Windows Firewall is disabled on both machines.
All DCOM permissions seems to be set OK on the server. I am a local admin on
the server and sys admin on SQL server and I can run sp_sdidebug without any
problems.
I can debug SQL on my local machine using Query Analyzer and VS.NET without
any problems. I can also debug SQL using Query Analyzer on the server if I
log there with my account.
What could prevent SQL debugger from connecting to my workstation? I tried
to add that SQL service account to my local admin group, but it did not help
.
I am not sure what is required to "2) Verify that machine '<server>' can ope
n
files on machine '<my machine>'." Does it mean that I have to trust my serve
r
machine? How can I do that?
What else could be wrong?Hello Serge,
You wrote in conference microsoft.public.sqlserver.programming on Fri, 7
Oct 2005 17:25:03 -0700:
SM> I am trying to debug a stored procedure on remote server. The debugger
SM> starts and passes through without stopping.
http://support.microsoft.com/defaul...kb;en-us;841249
and especially
http://support.microsoft.com/defaul...kb;en-us;839280
Vadm Rapp|||After spending few hours troubleshooting my local COM+ security and killing
my registry :) I found the settings that make remote SQL debugging work for
SQL Analyzer and VS.NET.
All these settings apply to the Default COM Security for Windows XP
Workstation from which debugging is initiated.
Access Permissions:
-- Limits:
* ANONYMOUS LOGON - Allow All (Required to debug from SQL Analyzer)
* Everyone - Allow All (Required to debug from VS.NET)
Launch and Activation Permissions:
Limits (add)
* ANONYMOUS LOGON - Allow All (Required to debug from VS.NET)
Default (add)
* ANONYMOUS LOGON - Allow All (Required to debug from VS.NET)
Huh!
Of course all settings on the server must be done first as described in
several knowlege articles. If firewall is anabled, then it should be adresse
d
as well.
"Serge Matsevilo" wrote:

> Hello,
> I am trying to debug a stored procedure on remote server. The debugger
> starts and passes through without stopping.
> No error messages are generated on the client.
> On the server the following error appears in the Application Log:
> Event Type: Error
> Event Source: SQLDebugging98
> Event Category: None
> Event ID: 1
> Date: 10/7/2005
> Time: 4:41:47 PM
> User: N/A
> Computer: <server>
> Description:
> SQL Server is running as '<domain account>' and cannot connect to the
> debugger on machine '<my machine>' (error = 0x80070005 Access is denied. )
.
> Use one of the following options to fix this error. 1) Run SQL Server as
> "Local System", as a domain account, or as a local account with identical
> usernames and passwords on both machine '<server>' and '<my machine>'. 2)
> Verify that machine '<server>' can open files on machine '<my machine>'.
> Debugging disabled for connection 55.
> Server is Windows 2003 Standard running SQL 2000 SP4 with .NET remote
> debugging support.
> Client is Windows XP Pro SP2 running SQL 2000 SP4 and .NET 1.1 SP1.
> Windows Firewall is disabled on both machines.
> All DCOM permissions seems to be set OK on the server. I am a local admin
on
> the server and sys admin on SQL server and I can run sp_sdidebug without a
ny
> problems.
> I can debug SQL on my local machine using Query Analyzer and VS.NET withou
t
> any problems. I can also debug SQL using Query Analyzer on the server if I
> log there with my account.
> What could prevent SQL debugger from connecting to my workstation? I tried
> to add that SQL service account to my local admin group, but it did not he
lp.
> I am not sure what is required to "2) Verify that machine '<server>' can o
pen
> files on machine '<my machine>'." Does it mean that I have to trust my ser
ver
> machine? How can I do that?
> What else could be wrong?

Remote Procedure Call Failed

Hi,
We are running IIS 4 under NT Server 4.0 Server, Serice pack 3; SQL
Server 6.5, Service Pack 3 and ASP 2.0, for our development databases.
We recently upgraded to IIS 4 and since then we keep getting thid
Remote Procedure error.
The exact error reads:
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.
The next message that comes up is:
Server Application Error
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.HTTP/1.1 500 Server Error Server:
Microsoft-IIS/4.0 Date: Mon, 29 Mar 1999 22:09:57 GMT Connection:
close Content-Type: text/html Content-Length: 93 The remote procedure
call failed.
We keep rebooting the server but this error persists. I can't find
anything that points to what can be causing this.
Has anyone else come across this problem?
Thanx in advance,
-=lata=-Lata,
1. Does this error happen only on the IIS? At the time of error, are you
able to use Query analyser and enterpirse manager and other client
connections to connect to sql server?
2. When you say you rebooted the server, is it the IIS server or the sQL
Server or the Windows server?
If other sql server applications and client connections are running fine,
then it could be an IIS issue.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Remote Procedure Call Failed

Hi,
We are running IIS 4 under NT Server 4.0 Server, Serice pack 3; SQL
Server 6.5, Service Pack 3 and ASP 2.0, for our development databases.
We recently upgraded to IIS 4 and since then we keep getting thid
Remote Procedure error.
The exact error reads:
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.
The next message that comes up is:
Server Application Error
The server has reached the maximum recovery limit for the application
during the processing of your request. Please contact the server
administrator for assistance.HTTP/1.1 500 Server Error Server:
Microsoft-IIS/4.0 Date: Mon, 29 Mar 1999 22:09:57 GMT Connection:
close Content-Type: text/html Content-Length: 93 The remote procedure
call failed.
We keep rebooting the server but this error persists. I can't find
anything that points to what can be causing this.
Has anyone else come across this problem?
Thanx in advance,
-=lata=-
Lata,
1. Does this error happen only on the IIS? At the time of error, are you
able to use Query analyser and enterpirse manager and other client
connections to connect to sql server?
2. When you say you rebooted the server, is it the IIS server or the sQL
Server or the Windows server?
If other sql server applications and client connections are running fine,
then it could be an IIS issue.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Remote procedure Call

The duplicacion is MErge.
when sinchronize this error is show:
Error calling rempote procedure
Please Help!
Gracias
Gabriel
Pizarro ,
please could you post up the complete error message.
Thanks,
Paul Ibison