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:
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:
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:
December 6, 2019
