I am trying to execute a stored procedure from vba using integrated security. I can run the stored procedure form the sql management studio under the same user account that I am using in vba. I get the following error.
Run-Time error '-2147217900 (80040e14)':
OLE DB provider "PIOLEDB" for linked server "PILINK" returned message "[PI SDK] Unable to open a session on a server. Verify the local pinetmgr service and the remote PI system are running. LQRES020".
here is the vba:
Private Sub connect_to_sql()
'*****************************************************************************************************************
' Execute SQL Stored Procedure to update production calculation
'*****************************************************************************************************************
' Create a connection object.
Dim rstStringVal As String
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim cmd As ADODB.Command
' Provide the connection string.
Dim strConn2 As String
'Use the SQL Server OLE DB Provider.
strConn2 = "PROVIDER=SQLOLEDB;"
'Connect to the Pubs database on the local server.
strConn2 = strConn2 & "DATA SOURCE=LQRES021;INITIAL CATALOG=LQAFCustom;"
strConn2 = strConn2 & "INTEGRATED SECURITY=SSPI;"
'Now open the connection.
cn.Open strConn2
' Create a recordset object.
Dim rst As ADODB.Recordset
Dim ResultSet
Set rst = New ADODB.Recordset
Sql = "exec dbo.dboTest"
rst.Open Sql, cn
rst.Close
cn.Close
Set rst = Nothing
Set cn = Nothing
End Sub
And the stored procedure that I am executing:
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Create A Table to Put Data into
DECLARE @productionTable TABLE (ID INT IDENTITY(1,1),dataTime DATETIME,dataInput FLOAT)
INSERT INTO @productionTable (dataTime,dataInput)
--Get Data from AF View and Insert into the Production Table
SELECT convert(datetime,((Time)))as TimeStamp, round((Convert(float,(Value))),3) as Input_Production
FROM [AFLINK].[PIAFDB].[Data].[DigesterProdution]
select ID,dataTime,dataInput
From @productionTable
-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(dataInput) FROM @productionTable)
-- Declare an iterator
DECLARE @I INT
-- Initialize the iterator
SET @I = 1
-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
-- Declare variables to hold the data which we get after looping each record
DECLARE @iID VARCHAR(50), @iTimestamp datetime, @iInputProduction float
-- Get the data from table and set to variables
SELECT @iID = ID, @iTimestamp = dataTime, @iInputProduction = dataInput FROM @productionTable WHERE ID = @I
-- Display the looped data
--PRINT 'Row No = ' + CONVERT(VARCHAR(2), @I)
--PRINT 'ID = ' + @iID + ', TimeStamp = ' + @iTimestamp + ' Production = '+ @iInputProduction
--Update PILINK.PIARCHIVE..PICOMP Set value = round((Convert(float,(@iInputProduction))),3)
--where tag = 'testtag' and time = convert(datetime,(@iTimestamp))
--Write Values To PI
INSERT INTO PILINK.PIARCHIVE..PICOMP (tag,time,value)
VALUES ('testtag',@iTimestamp,round(@iInputProduction,3))
-- Increment the iterator
SET @I = @I + 1
END
END
GO
What am I missing? I would appreciate any help.
I can also run the stored procedure from vba if I don't use integrated security; however, that doesn't meet our security guidelines.
