Error While Get Data From Stored Procedures (Mssql) 关注

0
Avatar
Legacy Poster

Error while get data from stored procedures (MSSQL). Unable to use "insert into" statement in stored procedure (MSSQL). Created a temp table to get data from multiple table to process data. While using this procedure in BarTender we are facing error "connection closed". After doing some trail and error method, we came to know that "insert into" syntax is causing the error. When commented out this line it is working fine. Please suggest the way to fix this issue. Thanks in advance.

3 评论

0
Avatar
Domingo Rodriguez
版主
评论操作 固定链接

Could you paste the entire stored procedure to this post?
0
Avatar
Legacy Poster
评论操作 固定链接

[quote name='Domingo - Seagull Support' timestamp='1343126416' post='2929']
Could you paste the entire stored procedure to this post?
[/quote]

Hi,

I am apologize for delay posting my procedure, actually we are in busy to deliver our project at that time, We have integrated BarTender in application successfully (thanks, this forum helped us a lot). Now we have little bit free time to work on pending issues. We want a solution to fix it as we will use this type of syntax more frequently. Please review below procedure and suggest the solution, thanks in advance.

if Object_Id('dbo.pr_Shp_GetDataXML') is null
Exec('Create Procedure pr_Shp_GetDataXMLas begin return; end')
Go
Alter Procedure pr_Shp_GetDataXML
(@LPNs XML)
as
declare @ReturnCode TInteger,
@MessageName TMessageName,
@UserId TUserId,
@vLPN TLPN;

declare @ttLPNs TEntityKeysTable;

declare @lpndata table
(ShipFromName TName,
ShipFromAddress1 TAddressLine,
ShipFromCity TCity,
ShipFromState TState,
ShipFromZip TZip,
ShipFromCountry TCountry,
ShipFromCSZ TVarchar);

begin
set NOCOUNT ON;
select @ReturnCode = 0,
@Messagename = null,
@UserId = System_User;

insert into @ttLPNs (EntityKey)
select Record.Col.value('(./text())[1]', 'varchar(50)')
from @LPNs.nodes('/root/EntityKey') as Record(Col)

select top 1 @vLPN = EntityKey
from @ttLPNs;

while (@vLPN is not null)
begin

insert into @lpndata
exec pr_Shipping_GetLPNDate @vLPN

delete @ttLPNs
where EntityKey = @vLPN

/* Set @vLabelType as null, this will set to break the loop in case next line has no top record */
select @vLPN = null;

/* Fetch the selected label need to be print one by one to process*/
select top 1 @vLPN = EntityKey
from @ttLPNs;
end

select * from @ttLPNs

ErrorHandler:
if (@MessageName is not Null)
Exec @ReturnCode = pr_Messages_ErrorHandler @MessageName;

ExitHandler:
return(@ReturnCode);
end /* pr_Shp_GetDataXML*/
0
Avatar
Gene Henson
版主
评论操作 固定链接

Hello,

I'd like to have you do two things:

First, add the following to your Stored Procedure: "SET NOCOUNT ON". You'll need to turn that on before creating the first temp table, and then you can turn it back off after you're finished inserting to the last temp table using "SET NOCOUNT OFF".

Second, in BarTender's database connection setup, turn "use client cursor" on.

Let me know if that helps.

请先登录再写评论。