Microsoft KB Archive/324506

= INFO: CS2K: How to create a Profile Data Population Script =

Article ID: 324506

Article Last Modified on 12/27/2002

-

APPLIES TO


 * Microsoft Commerce Server 2000 Standard Edition

-



This article was previously published under Q324506



SUMMARY
When you package a Commerce Server 2000 Web site, many settings are included in the package (PuP file). However, User Profile data is not included in the package. There are many ways to migrate Profile data from a Development environment to a Production environment or to a Staging environment. This Knowledge Base article outlines some of those options.

Some of the Profile Migration options are the following:
 * Use the SQL Server Import and Export Data wizard.
 * Create a SQL script for PuP.exe to use that is based on C:\Program Files\Microsoft Commerce Server\PopulateProfileSQL.sql.
 * Use the SQL Server command line utility BCP to export and import profile data.

Note The first option and the third option assume that you have already unpacked a Commerce Web site on the destination server, and that you only have to migrate Profile data.



MORE INFORMATION
 Use the SQL Server Import and Export Data wizard.  Click Start, point to Program Files, point to Microsoft SQL Server, and then click Import and Export Data. The introduction screen for the DTS Import/Export Wizard appears. Click Next. For Data Source, verify that Microsoft OLE DB provider for SQL Server is selected. Configure the source Server name and the Authentication information appropriately, and then select the correct source Database name (such as Retail_Commerce). Click Next. For Destination, verify that Microsoft OLE DB provider for SQL Server is selected. Configure the destination Server name and the Authentication information appropriately, and then select the correct destination Database name. (A database that has empty tables already exists; it is created when you unpack your Commerce Site from a PuP file.) Click Next. Leave the default option Copy table(s) and view(s) from the source database selected. Click Next. Click to select the check boxes for Addresses, UserObject, BlanketPOs, OrganizationObject, and any custom tables that were included in the Profile Definition Schema Script when you unpacked the Commerce Web site to the destination server. Click Next.</li> Verify that Run immediately is selected, and then click Next.</li> A summary of what is about to occur appears. Verify that the information is correct, and then click Finished.</li> The Executing Package window appears, which shows the progress for each step in the DTS package. When the dialog box appears that says Successfully copied table(s) from Microsoft SQL Server to Microsoft SQL Server, the Profile Data Migration is completed. Click Done.</li></ol> </li> Create a SQL script for PuP.exe to use that is based on C:\Program Files\Microsoft Commerce Server\PopulateProfileSQL.sql.

The following is a sample script for the UserObject table that writes a SQL script that you can use with PuP.exe: <ol>  Create a file named ExportDataToSQL.vbs (or any other name that has a VBS file name extension), and then paste the following sample code in this file.

Note This script exports only the UserObject table. To export other tables, change the SELECT statement accordingly. When you have exported all the tables that you want by using this method, you can combine them in a single SQL script. Option Explicit Dim SqlUserName: SqlUserName = &quot;sa&quot; Dim SqlPassword: SqlPassword = &quot;password&quot; Dim SqlServer: SqlServer = &quot;NetBIOS Computer name or IP&quot; Dim SqlDatabase: SqlDatabase = &quot;Retail_Commerce&quot;

' CursorTypeEnum Values Const adOpenForwardOnly = 0 Const adOpenKeyset = 1 Const adOpenDynamic = 2 Const adOpenStatic = 3

' LockTypeEnum Values Const adLockReadOnly = 1 Const adLockPessimistic = 2 Const adLockOptimistic = 3 Const adLockBatchOptimistic = 4

Dim fso, cn, rs Set fso = CreateObject(&quot;Scripting.FileSystemObject&quot;) Set cn = CreateObject(&quot;ADODB.Connection&quot;) Set rs = CreateObject(&quot;ADODB.Recordset&quot;)

'This example SQL statement specifies each field. 'Dim Sql: Sql = &quot;SELECT g_user_id, g_org_id, u_user_title, i_user_type, u_first_name, u_last_name, u_email_address, &quot; & _ '              &quot;u_tel_number, u_tel_extension, u_fax_number, u_fax_extension, u_logon_name, u_user_security_password, &quot; & _ '              &quot;g_user_id_changed_by, i_account_status, u_user_catalog_set, d_date_registered, u_campaign_history, &quot; & _ '              &quot;i_partner_desk_role, d_date_last_changed, d_date_created, u_Pref1, u_Pref2, u_Pref3, u_Pref4, u_Pref5 &quot; & _ '              &quot;FROM UserObject&quot;

'These sample SQL statements SELECT everything in the table. 'Dim Sql: Sql = &quot;SELECT * FROM Addresses&quot; 'Dim Sql: Sql = &quot;SELECT * FROM Organizations&quot; 'Dim Sql: Sql = &quot;SELECT * FROM UserObject&quot; Dim Sql: Sql = &quot;SELECT * FROM UserObject&quot;

On Error Resume Next cn.Open &quot;Provider=SQLOLEDB.1;User ID=&quot; & SqlUserName & &quot;;Password=&quot; & SqlPassword & &quot;;Persist Security Info=True;Initial Catalog=&quot; & SqlDatabase & &quot;;Data Source=&quot; & SqlServer If Err.number <> 0 Then MsgBox &quot;Failed to open Database&quot; WScript.Quit End If

rs.Open Sql, cn, adOpenForwardOnly, adLockReadOnly If Err.number <> 0 Then MsgBox &quot;Failed to open Recordset&quot; WScript.Quit End If

Dim Flds, Fld Set Flds = rs.Fields Dim SqlInsert: SqlInsert = &quot; INSERT INTO [UserObject] (&quot; For Each fld In Flds    SqlInsert = SqlInsert & &quot;[&quot; & fld.Name & &quot;],&quot; Next SqlInsert = Left(SqlInsert, Len(SqlInsert) - 1) & &quot;)&quot; If Err.number <> 0 Then MsgBox &quot;Failed to read Field Names&quot; WScript.Quit End If

Dim SqlValues If Not rs.EOF Then Dim SqlScript, Field Set SqlScript = fso.CreateTextFile(&quot;CustomPopulateProfileSQL.sql&quot;) If Err.number <> 0 Then MsgBox &quot;Failed to write output file&quot; WScript.Quit End If

Do While Not rs.EOF SqlScript.WriteLine SqlInsert SqlValues = &quot; VALUES (&quot;        For Each Field in rs.Fields            If IsNull(Field.Value) Then                SqlValues = SqlValues & &quot;NULL, &quot;            Else                Select Case Field.Type                    Case 130, 202, 203                        SqlValues = SqlValues & &quot;N'&quot; & Field.Value & &quot;', &quot;                    Case 129, 135, 200, 201                        SqlValues = SqlValues & &quot;'&quot; & Field.Value & &quot;', &quot;                    Case Else                        SqlValues = SqlValues & Field.Value & &quot;, &quot;                                  End Select            End If        Next        SqlValues = Left(SqlValues, Len(SqlValues) - 2) & &quot;)&quot; SqlScript.WriteLine SqlValues SqlScript.WriteLine &quot;GO&quot; & vbCrLf rs.MoveNext Loop SqlScript.Close Set SqlScript = Nothing Else MsgBox &quot;There is no data in the database and/or table you specified. Please check connection string to ensure it is correct or check the database to ensure there is data.&quot; WScript.Quit End If

rs.Close cn.Close Set rs = Nothing Set cn = Nothing Set fso = Nothing

MsgBox &quot;Script Generation Complete!!&quot; </li> Change the values in the script for SqlUserName, SqlPassword, SqlServer, and SqlDatabase, and then save the file.</li> When you run this script, you create a file named CustomPopulateProfileSQL.sql. Point to this file when you package the Commerce Web site.

Note Most administrators do not include the Profile Schema Script when they package a site. It is a good idea to also select this file (ProfileSQL.sql, if the site is based on the Retail site) when you pack your site.</li></ol> </li> Use the SQL Server command line utility BCP to export and import profile data.

For more information about BCP, see the Books Online topic &quot;bcp Utility.&quot; <ol> Run the following command line utility from the source computer running SQL Server:

bcp &quot;Retail_commerce.dbo.UserObject&quot; out &quot;userObject.txt&quot; -c -q -U&quot;sa&quot; -P&quot;thePassword&quot;

</li> Bring the newly created UserObject.txt file to the destination computer running SQL Server, and then run the following command:

bcp &quot;Retail_commerce.dbo.UserObject&quot; in &quot;userObject.txt&quot; -c -q -U&quot;sa&quot; -P&quot;thePassword&quot;

</li></ol> </li></ul>

Keywords: kbinfo KB324506

-

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

© Microsoft Corporation. All rights reserved.