I have a number which is very long and need to use it as a parameter in a SQL statement to be executed. I am trying to convert that 'testSessionID' string to double or Long, the function returns Err.description. but when I use testSessionID = "2784863" it works fine.
Here is my code:
testSessionID = "1030000000000000005"
Dim TSID
TSID = CDbl (testSessionID)
Dim script_testSessionStatus, testSessionStatus
'Getting the TestSession Status of TestSessionID
script_testSessionStatus = ("exec GetTestSession @TestSessionID ='" & TSID & "'")
testSessionStatus = ExecuteSQLStatement_String(script_testSessionStatus, "Value")
Public Function ExecuteSQLStatement_String(Sql_Statement, colname)
Err.Clear
On Error Resume Next
objRecordSet.open Sql_Statement,objconnection,1
If Err.Number <> 0 Then
ExecuteSQLStatement_String = Err.description
objRecordSet.Close
Err.Clear
Else
ExecuteSQLStatement_String = objRecordSet.Fields(colname).Value
objRecordSet.Close
End If
On Error GoTo 0
End Function
Here is my GetTestSession stored procedure:
--Called from GetTestsessions based on input param
go
--Use MfgDB
go
set nocount on
go
-- Begin Create Procedure Script for dbo.GetTestSession
print 'Run Script for Procedure - dbo.GetTestSession'
--go
-- Project :
-- SQL Objects : dbo.GetTestSession
-- References To : No Foreign Key References constraints
--
-- Referenced By : Not Referenced by other table Foreign Keys constraints
go
go
------------------------------------------------------------------------
--Create Procedure Script for dbo.GetTestSession
------------------------------------------------------------------------
go
if object_id('[dbo].[GetTestSession]') is not null
Drop Procedure [dbo].[GetTestSession]
go
CREATE PROCEDURE [dbo].[GetTestSession]
@TestSessionID bigint
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON --always have this on
--Begin generic part of the stored procedure
--Do not change this generic block
-- $Date$
-- $Author$
-- $Rev$
-- $URL$
--Procedure-specific Declaration
DECLARE @AttID bigint
DECLARE @ts TABLE (
SuiteID bigint,
TestSessionType varchar(50),
StartSession datetime,
Operator varchar(100),
DUTAttributeSetID bigint,
SerialNumber varchar(50),
Attribute varchar(50),
Value varchar(255),
Comment varchar(1000)
)
--END Procedure-specific Declaration
--Error/Auditing Declaration
DECLARE @retVals RaiseMessages, -- Error message container
@dtNow datetime, -- Now according to the SP
@nRes int, -- Standard "result" int variable
@sAudit nvarchar(max) -- Audit string; contains this procedures
-- parameters with their data
-- inspect and optionally change SET at execution time and
-- disable external started transaction. Check ODBC connection settings.
-- EXEC dbo.xCheckSetup @@ProcID
-------------------------------------
--set start time
-------------------------------------
SELECT @dtNow = GETDATE(),
@nRes = 0,
@sAudit = ''
-- Build an audit string for the logging system. This is required for every
-- externally facing stored procedure. This can be autogenerated by running
-- exec apGenerateAuditCode 'apTemplate'
EXEC dbo.xAuditGetTestSession
@TestSessionID,
@sAudit OUTPUT
-------------------------------------------------------------------------
--End generic part of the stored procedure
-------------------------------------------------------------------------
--Begin specific part of the stored procedure
-------------------------------------------------------------------------
--scrub data minimize sniffing issues by offsetting to local variables
-------------------------------------------------------------------------
--Initial input value trap
-------------------------------------
INSERT INTO @ts(SuiteID,TestSessionType,StartSession,Operator,DUTAttributeSetID,SerialNumber,Attribute,Value)
SELECT ts.TestSuiteID,
tst.TestSessionType,
ts.Created,
uc.UserName CreatedBy,
ts.DUTAttributeSetID,
d.SerialNumber,
a.Description Att_Name,
v.Value Att_Value
FROM dbo.DUTs d
INNER JOIN dbo.DUTAttributeSets das
ON d.DUTID = das.DUTID
INNER JOIN dbo.TestSessions ts
ON das.DUTAttributeSetID = ts.DUTAttributeSetID
INNER JOIN dbo.TestSessionTypes tst
ON ts.TestSessionTypeID = tst.TestSessionTypeID
INNER JOIN dbo.TSAttributes ta
ON ts.TestSessionID = ta.TestSessionID
INNER JOIN dbo.Attributes a
ON ta.AttributeID = a.AttributeID
INNER JOIN dbo.TSValues v
ON ta.TSValueID = v.TSValueID
INNER JOIN dbo.Statuses s1
ON ta.StatusID = s1.StatusID
INNER JOIN dbo.Users uc
ON ts.CreatedBy = uc.UserID
WHERE ts.TestSessionID = @TestSessionID
/*
INNER JOIN dbo.vTestSessions ts
ON das.DUTAttributeSetID = ts.DUTAttributeSetID
WHERE ts.TestSessionID = @TestSessionID
*/
IF @@ROWCOUNT = 0
BEGIN
--This is an Error(nISError=1),Implicit Default no email (AlertGroup=null), logs the error (LogSuppress=0), and does not return a simple resultset (HasResultSet=0).
INSERT INTO @retVals(
Started,
Audit,
nIsError,
Severity,
Operation,
Code,
InitialMessage,
Description
)
SELECT @dtNow,
@sAudit,
1,
10,
'Initialize',
'ParameterVal',
'Invalid Parameter Value',
'Parameter @TestSessionID is not valid'
EXEC @nRes = dbo.apRaiseMessage @retVals
RETURN @nRes
END
-- -----------------------------------
-- Main body
-- -----------------------------------
BEGIN TRY
SELECT @AttID = dbo.fn_GetAttributeID('dbo.Tests','Iteration')
-- Expection is that Iteration will only be supplied at EndTestSession, so if Active, there should be no Iteration in TestSession
IF EXISTS (SELECT * FROM @ts WHERE Attribute = 'Status' AND Value = 'ACTIVE')
AND @AttID IS NOT NULL
BEGIN
INSERT INTO @ts (SuiteID, TestSessionType, StartSession, Operator, DUTAttributeSetID, SerialNumber, Attribute, Value)
SELECT TOP 1 ts.SuiteID, ts.TestSessionType, ts.StartSession, ts.Operator, ts.DUTAttributeSetID, ts.SerialNumber, 'Iteration', ISNULL(x.Value, 1)
FROM @ts ts
CROSS APPLY ( SELECT MAX(CAST(tav.Value AS int)) Value
FROM dbo.Tests t
INNER JOIN dbo.TestAttributes ta
ON t.TestID = ta.TestID
INNER JOIN dbo.TAValues tav
ON ta.TAValueID = tav.TAValueID
WHERE t.TestSessionID = @TestSessionID
AND ta.AttributeID = @AttID
) x
END
SELECT SuiteID, TestSessionType, Operator, StartSession, DUTAttributeSetID, SerialNumber, Attribute, Value
FROM @ts
END TRY
BEGIN CATCH
--This is an Error(nISError=1), Explicit Default has email (AlertGroup!=null), logs the error (LogSuppress=0), and does not return a simple resultset (HasResultSet=0).
INSERT INTO @retVals(
Started,
Audit,
nIsError,
Severity,
Operation,
Code,
InitialMessage,
Description,
HasResultSet,
AlertGroup
)
SELECT @dtNow,
@sAudit,
1,
10,
'GetTestSession',
'SevereError',
'Unexpected Error',
'A severe error occurred : "' + CONVERT(varchar,ERROR_NUMBER()) + '" - ' + ERROR_MESSAGE(),
0,
'Monitor'
EXEC @nRes = dbo.apRaiseMessage @retVals
RETURN @nRes
END CATCH
--This is NOT an Error(nISError=0),
INSERT INTO @retVals(
Started,
Audit,
nIsError,
Operation,
Code,
InitialMessage,
Description
)
SELECT @dtNow,
@sAudit,
0,
'End',
'End',
'Completed',
'Normal Code Execution'
EXEC @nRes = dbo.apRaiseMessage @retVals
RETURN @nRes
END
go
------------------------------------------------------------------------
-- End Create Procedure Script
------------------------------------------------------------------------
go
go
