VERIFIED SOLUTION i

Resolve Error when calling SQL Server Stored Procedure in MapInfo Pro

Product affected: MapInfo Pro™
Example of Stored Procedure in Sql Server and Connecting to MiPro via Sql Server:

1. In SQL Server create a simple stored procedure to create a random number:
 
CREATE procedure dbo.GenerateRandomID 
As
Begin
 
---- Create the variables for the random number generation
DECLARE @GenerateRandomID INT;
DECLARE @Upper INT;
DECLARE @Lower INT
 
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @GenerateRandomID = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @GenerateRandomID
 
end
GO

2. In MapBasic create a simple test program to call this and return the resulting value, for example:
 
Include "mapbasic.def"
Include "menu.def"
 
Declare Sub Main
 
Sub Main
 
Dim hdbc, hstmt As Integer
Dim Stmt As Integer
Dim Col As Integer 
 
'1. connect to Server
hdbc=server_connect("QELIB","DSN=MaxA_VM;DB=SoftwareSupport;UID=MAPINFO;pwd=MAPINFO")
 
'2. call SP
Stmt = Server_Execute(hdbc, "GenerateRandomID") 
 
Server Stmt Fetch NEXT
For Col = 1 To Server_NumCols(Stmt)
Print Server_ColumnInfo(Stmt, Col, SRV_COL_INFO_NAME) + " = " + Server_ColumnInfo(Stmt, Col, SRV_COL_INFO_VALUE)
Next
 
'4.
Server hdbc Disconnect
 
End Sub
 
UPDATED:  September 14, 2017