AnsweredAssumed Answered

Update or Delete rows in VB through SQL Server and a Linked Server PIOLEDBENT PI AF

Question asked by Dubus on Jun 7, 2018
Latest reply on Jun 8, 2018 by gregor

Hello,

 

In MS SQL Server, I have created a Linked Server with a PIOLEDBENT (PI OLEDB Enterprise).

In a sheet Excel, I've created a VB code to do a select in EnumerationSet,EnumerationValue and it works (see code below)

Now, I would like to do an update and a delete of an enumeration value but I don't find the right syntax or parameters. I'm connected with admin rigths.

 

Exemple to do a select :

'VB Code :

Sub Get_Enum_Act_list()

'Déclaration des variables
Dim cnx As New ADODB.Connection
Dim rst As New ADODB.Recordset

cnx.ConnectionString = "UID=xxx;PWD=xxx;DRIVER={SQL Server};Server=xxx;Database=master;"

cnx.Open

'Exécution de la requête
Set rst = cnx.Execute("SELECT v.name FROM [PIAF].[Database1].[Asset].[EnumerationValue] as v, [PIAF].[Database1].[Asset].[EnumerationSet] as s where v.EnumerationSetID = s.ID and s.name = 'Activities' order by v.name")

Sheets("Activity").Range("A1").CopyFromRecordset rst

End Sub

 

I tried first to delete in MS SQL Server but without success:

 

- Select * from Openquery(PIAF, 'delete from [Database1].[Asset].[EnumerationValue] where value = 15')

- delete openquery(PIAF,'select * from  [Database1].[Asset].[EnumerationValue] where value = 15')

 

- delete from [PIAF].[Database1].[Asset].[EnumerationValue] where value = 15

- select * from openquery(PIAF, 'EXECSCRIPT([DELETE FROM Database1.Asset.EnumerationValue WHERE id = 15])')

 

 

 

 

With PIAF = the Linked Server

 

Any idea to update or delete rows with Linked Server in MS SQL Server ?

 

Thank you very much

Outcomes