Microsoft KB Archive/329082

= How to Change or Reset the PIP Templates in the BTSKInstances Database =

Article ID: 329082

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft BizTalk Accelerator for RosettaNet 2.0 Enterprise Edition

-



This article was previously published under Q329082



SUMMARY
BizTalk Server Accelerator for RosettaNet 2.0 does not have a graphical user interface (GUI) tool that you can use to add or change Partner Interface Process (PIP) templates that are stored in the BTSKInstances database.

On a computer running BizTalk Server that has the BizTalk Accelerator for RosettaNet installed, you can use the following two stored procedures to update the PIP templates in the BTSKInstances database:
 * sp_GetPIDXML is used to retrieve the PIP templates.
 * sp_UpdatePIDXML is used to store the updated PIP templates.

The &quot;More Information&quot; section of this article contains a sample script that demonstrates how to retrieve or update the PIP templates by using these stored procedures.



Sample Code
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, see the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For additional information about the support options available from Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

  On a computer running Microsoft BizTalk Server that has the BizTalk Server Accelerator for RosettaNet 2.0 installed, paste the following sample code in a text editor (such as Notepad): ' This script will retrieve the existing Partner Interface Processes (PIPs) from the PartnerAgreementXML column of the ' tblGlobalChannels table in the BTSKInstances database and optionally update this column with a new XML payload. ' Typically, you would retrieve your existing PIPs into an XML file, modify the XML file to include any new PIPs, and then ' update the table with the modified XML file. ' ' NOTE: when you update the PartnerAgreementXML column with this script, it will overwrite all of the existing PIPs and ' replace them with the PIPs specified in the source XML file. It is recommended that you maintain a separate copy ' of your existing PIPs before you run the script to update your existing PIPs

Option Explicit

Dim retrievePIPxml Dim filesave Dim PIPxml Dim writePIPxml Dim fileretrieve Dim fso Dim f1 Dim updatefile Dim validate Dim continue Dim m_SQLServerName Dim m_DatabaseName Dim m_ConnectionString Dim conn

Call UpdatePIPS

Sub UpdatePIPS On Error Resume Next

m_SQLServerName = GetSQLServer m_DatabaseName = GetDatabase m_ConnectionString = &quot;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=&quot; & m_DatabaseName & &quot;;Data Source=&quot; & m_SQLServerName Set conn = CreateObject(&quot;ADODB.Connection&quot;) conn.Open m_ConnectionString Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot;)

If Err.Number <> 0 Then MsgBox &quot;Error occurred!&quot; + vbCRLF + &quot;Error Description is: &quot; + err.description + vbCRLF + &quot;Error Number is: &quot; + err.number, 16, &quot;Error&quot; Exit Sub End If

retrievePIPxml = MsgBox(&quot;Would you like to retrieve the existing Partner Agreement XML payload and save to a file?&quot;, 4, &quot;Retrieve existing Partner Agreement XML from BTSKInstances database&quot;) If retrievePIPxml = vbYes Then filesave = InputBox(&quot;Please enter file to save Partner Agreement XML file to:&quot;, &quot;Enter file to save Partner Agreement XML File&quot;, &quot;C:\Temp\PIPS.xml&quot;) PIPxml = GetPIPxml

If Err.Number <> 0 Then MsgBox &quot;Error occurred!&quot; + vbCRLF + &quot;Error Description is: &quot; + Err.Description + vbCRLF + &quot;Error Number is: &quot; + err.number, 16, &quot;Error&quot; Exit Sub End If

Set f1 = fso.CreateTextFile(filesave, True) f1.Write PIPxml fso.CopyFile filesave, filesave + &quot;.bak&quot;, False

If Err.Number = 58 Then Err.Number = 0 ElseIf Err.Number <> 0 Then MsgBox &quot;Error occurred!&quot; + vbCRLF + &quot;Error Description is: &quot; + Err.Description + vbCRLF + &quot;Error Number is: &quot; + err.number, 16, &quot;Error&quot; Exit Sub End If

End If

writePIPxml = MsgBox(&quot;Would you like to update the PIP database with a new XML file?&quot;, 4, &quot;Update PIP database with XML file&quot;) If writePIPxml = vbYes Then fileretrieve = Inputbox(&quot;Please Enter location of XML file with updated PIPs:&quot;, &quot;Enter location of XML File with updated PIPs&quot;, filesave) If fileretrieve <> &quot;&quot; Then Set f1 = fso.OpenTextFile(fileretrieve, 1) updatefile = f1.ReadAll PutPIPxml(updatefile) Else MsgBox &quot;You did not enter a valid location for the XML file.&quot; & vbCRLF & &quot;Update Cancelled.&quot;,0, &quot;Update Cancelled&quot; End If

Set f1 = Nothing If Err.Number <> 0 Then MsgBox &quot;Error occurred!&quot; + vbCRLF + &quot;Error Description is: &quot; + Err.Description + vbCRLF + &quot;Error Number is: &quot; + err.number, 16, &quot;Error&quot; Exit Sub End If

End If

Set fso = Nothing conn.Close Set conn = Nothing

End Sub

'Function for retrieving PIP XML Function GetPIPxml Dim rst Set rst = CreateObject(&quot;ADODB.Recordset&quot;) rst.Open &quot;Exec sp_GetPIDXML&quot;, conn GetPIPxml = rst.Fields(&quot;PartnerAgreementXML&quot;) Set rst = Nothing

If Err.Number <> 0 Then MsgBox &quot;Error occurred!&quot; + vbCRLF + &quot;Error Description is: &quot; + Err.Description + vbCRLF + &quot;Error Number is: &quot; + err.number, 16, &quot;Error&quot; Exit Function End If

End Function

'Function for storing PIP XML to PartnerAgreementXML column of the tblGlobalChannels table in the BTSKInstances database Function PutPIPxml(pidXML) On Error Resume Next continue = vbYes Dim testXML Set testXML = CreateObject(&quot;Msxml2.DOMDocument.3.0&quot;) validate = testXML.LoadXML(pidXML) set testXML = Nothing If validate = False Then continue = MsgBox(&quot;The file that you are updating the PartnerAgreementXML column with is not a valid XML file, Continue?&quot;, 4, &quot;Invalid XML file&quot;) If continue = vbYes Then conn.Execute &quot;Exec sp_UpdatePIDXML N'&quot; & pidXML & &quot;'&quot; Else MsgBox &quot;Update Cancelled&quot;,0, &quot;Update Cancelled&quot; End If

If Err.Number <> 0 Then MsgBox &quot;Error occurred!&quot; + vbCRLF + &quot;Error Description is: &quot; + Err.Description + vbCRLF + &quot;Error Number is: &quot; + err.number, 16, &quot;Error&quot; Exit Function End If

End Function

Function GetSQLServer Dim objShell Set objShell = CreateObject(&quot;WScript.Shell&quot;) GetSQLServer = objShell.RegRead(&quot;HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server Accelerator for RosettaNet\1.0\Administration\Server&quot;) Set objShell = Nothing

If Err.Number <> 0 Then MsgBox &quot;Error occurred!&quot; + vbCRLF + &quot;Error Description is: &quot; + Err.Description + vbCRLF + &quot;Error Number is: &quot; + err.number, 16, &quot;Error&quot; Exit Function End If

End Function

Function GetDatabase Dim objShell Set objShell = CreateObject(&quot;WScript.Shell&quot;) GetDatabase = objShell.RegRead(&quot;HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\BizTalk Server Accelerator for RosettaNet\1.0\Administration\Database&quot;) Set objShell = Nothing

If Err.Number <> 0 Then MsgBox &quot;Error occurred!&quot; + vbCRLF + &quot;Error Description is: &quot; + Err.Description + vbCRLF + &quot;Error Number is: &quot; + err.number, 16, &quot;Error&quot; Exit Function End If

End Function  Save the file. When you save the file, use a Visual Basic Script (.vbs) file name extension. Double-click the saved file to retrieve or update the PIP templates in your BTSKInstances database.

Keywords: kbinfo kbhowto kbpending KB329082

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.