Error While Get Data From Stored Procedures (Mssql)

Legacy Poster

• 16 de julio de 2012 13:57

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.

0

• 

  Domingo Rodriguez

  Moderador

  ★ BarTender Hero ★

  • 24 de julio de 2012 10:40
  Could you paste the entire stored procedure to this post?

  0
• 

  Legacy Poster

  ★ BarTender Hero ★

  • 8 de noviembre de 2012 14:35
  [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
• 

  Gene Henson

  ★ BarTender Hero ★

  • 8 de noviembre de 2012 20:33
  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.

  0

Iniciar sesión para dejar un comentario.