Error 7202 Could not find server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error 7202 Could not find server

Author

Topic

jabeam
Starting Member

4 Posts

Posted - 2011-08-18 : 10:13:46

Hi all, I am currently running into the following error when run a stored procedure with BEGIN TRANS:

"Could not find server 'VS-DEVELOPER\DEVELOPER_SQL' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

The server shown above is the a local sql server instance that is running the stored procedure and therefore shouldn't need to be linked. Am I misunderstanding the linked server definition or is there another underlying issue with the stored procedure?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-18 : 10:20:07

If the query is executing against that instance, remove the server name from the query.

If you're querying a different instance on the same server, you still need to create a linked server.

Also, might need to wrap the name in brackets, with that hyphen in there

[VS-DEVELOPER\DEVELOPER_SQL]

jabeam
Starting Member

4 Posts

Posted - 2011-08-18 : 10:28:50

Thanks for the reply. As far as I can tell, I'm not referencing the server name directly in any of the queries ran by the stored procedure. Here's the stored procedure if it will help:

declare @trans_type nvarchar(15)
declare @pending_flag char(1)
declare @userid nvarchar(20)
declare @from_warehouseid nvarchar(15)
declare @from_locationid nvarchar(15)
declare @transaction_descrip nvarchar(80)
declare @max_pendingid int
declare @rec_count int

/*
Synonyms used:
ASAP_EXP_PK_LINE
VISUAL_AMTS_PENDING_TRANS
*/

if @trans_type='' or @trans_type is null
begin
select @trans_type='I' --I=Issue to WorkOrder, M=Inventory Material Movement; W=Receipt from work order
end

if @from_warehouseid = '' or @from_warehouseid is null
begin
select @from_warehouseid = min(dflt_asap_exp_wo_from_warehouseid) from tblSystem
end

if @from_locationid = '' or @from_locationid is null
begin
select @from_locationid = min(dflt_asap_exp_wo_from_locationid) from tblSystem
end

if @transaction_descrip = '' or @transaction_descrip is null
begin
select @transaction_descrip = min(dflt_asap_exp_wo_transaction_descrip) from tblSystem
end

if @userid = '' or @userid is null
begin
select @userid= min(dflt_global_userid) from tblSystem
end

if @pending_flag='' or @pending_flag is null
begin
select @pending_flag= min(dflt_asap_exp_pending_flag) from tblSystem
end

BEGIN TRAN

--**(The next line is where the error happens)
select pxx.ORDER_NUM, ltrim(rtrim(substring(substring(pxx.ORDER_NUM,1, len(pxx.ORDER_NUM)-2), charindex('-b', pxx.ORDER_NUM) +2 , len(pxx.ORDER_NUM)))) + '/' + ltrim(rtrim(pxx.HOST_ORDER_NUM))
as DERIVED_REQID, pxx.PROD_ID, pxx.QTY_REQUESTED, pxx.QTY_PICKED,
pxx.DATE_STAMP, pxx.TIME_STAMP into tmpbo from ASAP_EXP_PK_LINE as pxx
where pxx.ORDER_NUM in (select ORDER_NUM from ASAP_EXP_PK_LINE as px where charindex('-b', px.ORDER_NUM) > 0)

update tmpbo set ORDER_NUM = substring(ORDER_NUM, 1, len(ltrim(rtrim(ORDER_NUM)))-2)

select * from tmpbo

-- remove duplicates based on last date/time stamp in export table ..

delete from tmpbo where ltrim(rtrim(DERIVED_REQID)) + ltrim(rtrim(DATE_STAMP)) + ltrim(rtrim(TIME_STAMP)) in(
select ltrim(rtrim(DERIVED_REQID)) + ltrim(rtrim(bkx.LAST_EXP_DATE_STAMP)) + ltrim(rtrim(bkx.LAST_EXP_TIME_STAMP)) from ProcessTableWOReqBKO as bkx)

select * from tmpbo

-- note: substring value 4 includes offset for -b prefix that will be removed..

select substring(px.ORDER_NUM, charindex('-b', px.ORDER_NUM) +2 , len(px.ORDER_NUM)) as ORDER_NUM,
px.HOST_ORDER_NUM, dbo.fn_Split_HostID_String(px.HOST_ORDER_NUM,'sub') as SUB,
dbo.fn_Split_HostID_String(px.HOST_ORDER_NUM, 'seq') as SEQ,
dbo.fn_Split_HostID_String(px.HOST_ORDER_NUM, 'piece') as PIECE,
px.PROD_ID, px.QTY_PICKED,
substring( ltrim(rtrim(substring(px.ORDER_NUM, charindex('-b', px.ORDER_NUM) +2 , len(px.ORDER_NUM)))), 1,(len(ltrim(rtrim(px.ORDER_NUM)))-4)) + '/' + ltrim(rtrim(px.HOST_ORDER_NUM)) as DERIVED_REQID
into tmp0 from ASAP_EXP_PK_LINE as px
where ltrim(rtrim(substring(px.ORDER_NUM,1,len(px.ORDER_NUM)-2))) + '/' + ltrim(rtrim(px.HOST_ORDER_NUM)) + ltrim(rtrim(px.DATE_STAMP)) + ltrim(rtrim(px.TIME_STAMP)) in
(select '-b' + ltrim(rtrim(wo.DERIVED_REQID)) + ltrim(rtrim(wo.DATE_STAMP)) + ltrim(rtrim(wo.TIME_STAMP)) from tmpbo as wo)

delete from tmp0 where QTY_PICKED=0

update tmp0 set ORDER_NUM = substring(ORDER_NUM, 1, len(ORDER_NUM)-4)

-- select * from tmp0

select t.DERIVED_REQID,@trans_type as TRANS_TYPE, t.SUB, t.SEQ, t.PIECE, wo.WORKORDER_BASE_ID, wo.WORKORDER_LOT_ID, wo.WORKORDER_SPLIT_ID,
wo.PART_ID, @from_warehouseid as FROM_WAREHOUSE_ID, @from_locationid as FROM_LOCATION_ID,
@transaction_descrip as DESCRIP, @userid as USERID, t.QTY_PICKED as QTY into tmp1
from tmp0 as t
join ProcessTableWO as wo on wo.DERIVED_REQID = t.DERIVED_REQID

select * from tmp1

select @max_pendingid= max(PENDING_ID)+1 from VISUAL_AMTS_PENDING_TRANS

if @max_pendingid is null
begin
select @max_pendingid=1
end

create table sequencer(ROWID int identity,
DERIVED_REQID nvarchar(50),
PENDING_ID int)

insert into sequencer(DERIVED_REQID, PENDING_ID)
select min(DERIVED_REQID), (ROW_NUMBER() over (order by DERIVED_REQID) + @max_pendingid) from tmp0 group by DERIVED_REQID

----select * from sequencer
----select * from tmp1

insert into VISUAL_AMTS_PENDING_TRANS(PENDING_ID, TRANS_TYPE, [USER_ID], PENDING_FLAG, TRANSACTION_DATE, TRANS_DATETIME,
PART_ID, QTY, WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID,
OPERATION_SEQ_NO, REQ_PIECE_NO, FROM_WAREHOUSE_ID, FROM_LOCATION_ID, [DESCRIPTION])
select distinct min(s.PENDING_ID), min(@trans_type), min(@userid), min(@pending_flag) , convert(char(10),getdate(),101), getdate(),
min(t.PART_ID), sum(t.QTY), min(t.WORKORDER_BASE_ID), min(t.WORKORDER_LOT_ID), min(t.WORKORDER_SPLIT_ID), min(cast(t.SUB as nvarchar(3))),
min(t.SEQ), min(t.PIECE), @from_warehouseid, @from_locationid, @transaction_descrip
from sequencer as s
join tmp1 as t on t.DERIVED_REQID=s.DERIVED_REQID
group by t.DERIVED_REQID

-- update quantity picked in totals table..
update wto set wto.QTY_TOTAL_PICKED = isnull(wto.QTY_TOTAL_PICKED,0)+t.QTY_PICKED,
wto.LAST_EXP_DATE_STAMP=t.DATE_STAMP, wto.LAST_EXP_TIME_STAMP=t.TIME_STAMP
from ProcessTableWOTotals as wto join tmpbo as t on ltrim(rtrim(t.DERIVED_REQID))=ltrim(rtrim(wto.DERIVED_REQID))

-- insert into backorder log table
insert into ProcessTableWOReqBKO(DERIVED_REQID, PART_ID, QTY_TOTAL_PICKED, LAST_EXP_DATE_STAMP, LAST_EXP_TIME_STAMP)
select t.DERIVED_REQID, t.PROD_ID, t.QTY_PICKED, t.DATE_STAMP, t.TIME_STAMP from tmpbo as t

----log
insert into tblTransactionLog(MODULE, LOG_TRANS_TYPE, LOG_TEXT, QTY_AFFECTED)
select 'EXP_REQ', 'INF', 'Received issue of [backordered] materials confirmation.. Qty: ' + ltrim(rtrim(cast(t.QTY as nvarchar(25)))) +
' Part: ' + ltrim(rtrim(t.PART_ID)) +
' from ASAP for WO Request ' + ltrim(rtrim(t.DERIVED_REQID)) + '.', t.QTY
from tmp1 as t
--
select @rec_count=@@rowcount

drop table tmp0
drop table tmp1
drop table sequencer
drop table tmpbo

COMMIT TRAN

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-18 : 10:53:54

one of these synonyms:

ASAP_EXP_PK_LINE
VISUAL_AMTS_PENDING_TRANS

is referencing it.

jabeam
Starting Member

4 Posts

Posted - 2011-08-18 : 11:03:34

Thanks for the help. I've added the VS-DEVELOPER\DEVELOPER_SQL as a linked server and now when I run the stored proc, I receive the following error:

Msg 3910, Level 16, State 2, Line 1
Transaction context in use by another session.

Any ideas? Again, I really appreciate the help on this issue.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-08-18 : 13:34:35

Saw this? http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/2da5983c-2256-4394-82e0-c0c26c663a7e/

jabeam
Starting Member

4 Posts

Posted - 2011-08-18 : 14:44:11

Thanks Russell. That link helped me resolve the issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources