1. 

   karl lauritzen, Member

   I have installed mydbr on my laptop, SQL server express, linked servers to local MySql and to SQL server remote.

   PHP 7.2
   IIS 7.0
   mydbr 5.0.8

   Connected to Sql server express fine. I can write sp as below and connect to linked servers and get data fine when editing sp and htting execute.

   But if I hit execute on query tab to run sp I get nothing. Also once I exit edit of sp and go to run report, get nothing. Once I go back to edit my code was not saved for the report.

   if object_id('sp_DBR_AgentInquiry','P') is not null
   drop procedure sp_DBR_AgentInquiry
   go
   create procedure sp_DBR_AgentInquiry
   as

   set Ansi_Nulls on
   go
   set ansi_warnings on
   go

   begin

   set ansi_nulls on
   set ansi_warnings on

   select * from openquery(linkedserver,'select code,city from RHP_Report.dbo.producer')

   end
   go

   I have searched and see nothinng wrong on install nor can fnd any menton of ths by anyone else. Shuld I do an update or all new install.
2. 

   myDBR Team, Key Master

   MyDBR uses the standard GO-statement to signal the end of a batch of Transact-SQL statements. In your procedure definition you have extra GO-statements for the ANSI_NULL's and ANSI_WARNINGS causing your procedure code to terminate early.

   Remove them and your procedure should work as expected.:

   if object_id('sp_DBR_AgentInquiry','P') is not null
   
   drop procedure sp_DBR_AgentInquiry
   
   go
   
   create procedure sp_DBR_AgentInquiry
   
   as
   
   begin
   
   set ansi_nulls on
   
   set ansi_warnings on
   
   select * from openquery(linkedserver,'select code,city from RHP_Report.dbo.producer')
   
   end
   
   go
   
   

   You usually do not need to change ANSI-settings within the procedure. The default ANSI-settings for myDBR are:

   SET ANSI_WARNINGS OFF
   SET ARITHABORT OFF
   SET ANSI_NULLS ON

   If you want to use different defaults, you can override it in mydbr/user/defaults.php.

   Also, no need to use OPENQUERY, you can also reference linked server tables as "linkedservername.databasename.schemaname.tablename".

   --
   myDBR Team

Reply

You must log in to post.