Comments (1)

Having recently upgraded from Windows7 to Windows8.  I encountered the 442 Error while attempting to connect to a client’s site via the Cisco VPN Client x64, Version 5.0.07.0290.

The fix I found that actually works requires editing the registry.  Run regedit and navigate to the following key: HKEY_LOCAL_MACHINE\\SYSTEM\\CurrentControlSet\\Services\\CVirtA and then select the DisplayName.

The value for the DisplayName that I originally had was: @oem11.inf,%CVirtA_Desc%;Cisco Systems VPN Adapter for 64-bit Windows

Changing it to: Cisco Systems VPN Adapter for 64-bit Windows resolved the 442 Error and allowed me to successfully connect to the client site

Comments (3)

I recently built a development VM where I installed both the SQL Server 2008R2 and the SQL Server 2012 Management Tools.  Everything seemed fine, until I opened an SSIS Package with a Script Task in BIDS.

While editing the script I was receiving the above error.  There is a Microsoft Connect bug report for the error which is marked active.

The workaround, did work for me.  Which consisted of moving the Microsoft.SQLServer.ManagedDTS.dll for the the C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies to a backup folder.

Right now I’m happy that I can finish working on the SSIS package.  I’m not sure of any issues that moving the file may cause with SSDT however.

Comments (0)

The SQL Server Error Log tracks activity as it is occurs in the database server.  This information is helpful in monitoring the server and in trouble shooting database problems. A new log file is automatically created each time that the SQL Server service is restarted.

The easiest way to view the error log is using the Log File Viewer in SQL Server Management Studio (SSMS).  The log files are simple text files so they can also be opened in Notepad or any text file editor.

Log File Viewer

To access the log file viewer, in the SSMS Object Explorer expand the Management folder under a connected server, then expand the SQL Server Logs folder right clicking on one of the archived logs gives you the option to select “View SQL Server Log”.  Selecting this opens the log file viewer

Using the log file viewer, you can change the log file that you are viewing by (de)selecting the check boxes in the upper left panel of the log file viewer

The buttons at the top of the viewer provide the additional capabilities to; load log files stored on disk, export the log file to disk, refresh the data being displayed, the ability to filter the log, as well as the ability to search the log.

Very large error logs

One of the issues that I encounter on occasion is the size of the current error log has gotten extremely large.  A production server that is rebooted infrequently continues writing to the current log which can cause it to grow significantly.  Attempting to open a very large file in the Log File Viewer or a text editor can take an inordinate amount of time.

Archives

By default, SQL Server keeps an archive  history of 6 error logs.  The current file named “ErrorLog” with no extension and each of the archives is named “ErrorLog.n” where .n is .1 for the most recent archive, .2 next archive and so on.

To verify the actual location you can use the “undocumented” property ErrorLogFileName

SELECT SERVERPROPERTY (‘ErrorLogFileName’);

Cycling the error file

In order to keep the log file at a manageable size the system stored procedure sp_cycle_errorlog can be called to cycle the log.  The log file is cycled just as with a service restart

EXEC msdb.dbo.sp_cycle_errorlog 

The ability to execute the system stored procedure is restricted to members of the sysadmin server role

Changing the number of error log archives

Depending on the frequency at which you choose to cycle the error log the default of 6 archive copies may not be enough to maintain an adequate amount of history.

To change the number of archive copies kept using SSMS is a simple task. Open the Management Folder, then select the SQL Server Logs folder and right click.  Select the Configure option.

In the resulting dialog, check the option “Limit the number of error log files before they are recycled” and specify the maximum number to keep, then simply click OK. I set the limit to 45 in the example below.

How many to keep?

The number of error logs to keep in the archive depends on how frequently the error log is cycled and any other specific business requirements concerning log file retention.  Generally, I will create a nightly job to cycle the error log and set the number of error logs to keep to 45 which give me the current error log along with history spanning the last 45 days.  Alternatively, in some environments, I’ve set up the job to cycle the log files to run weekly, keeping 8 to 10 archives.

Comments (0)

A frequent question on a number of forums is how to export data to an XML file with SSIS.  There is even a Connect request for an SSIS: XML Destination Adapter, which has been marked as Closed – won’t fix. For awhile I’ve been using a script task to generate XML output based on the example that  Denis Gobo (blog | twitter) blogged about.

Last fall/winter I took the free on-line Introduction to Databases course offered by Stanford University, where part of the course touched on XML, including a section on XSLT.  The course provided a good overview and pointed me in the direction of trying to utilize XSLT within SSIS.  According to the website the course may be formally offered later in 2012 – I would highly recommend taking it.  It was an awesome experience.

For this example, I create an XML output file using SSIS.  The formatted XML data is retrieved from a stored procedure. There are a few modifications that need to be made to the retrieved XML to create the desired output result:

• Add the XML Version <?xml version=”1.0″ ?>
• Rename  the <ROOT> tag to <CustomerData>
• Specify the name space for the XSD used for validation.

Getting all the pieces in place

First, I created the XML Schema that would be used to validate the XML.  It’s located online and is also show here:

<?xml version="1.0" encoding="utf-8" ?>
<!--Created with Liquid XML Studio 2012 Designer Edition 10.0.2.3955 (http://www.liquid-technologies.com)-->
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="CustomerData">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="Customer" minOccurs="0" maxOccurs="unbounded">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="CustomerID" type="xs:unsignedShort" />
                            <xs:element name="FirstName" type="xs:string" />
                            <xs:element name="MiddleName" type="xs:string" minOccurs="0" maxOccurs="1" />
                            <xs:element name="LastName" type="xs:string" />
                            <xs:element name="Phone" type="xs:string" minOccurs="0" />
                            <xs:element name="EmailAddress" type="xs:string" minOccurs="0" />
                            <xs:element name="CustomerAddresses" minOccurs="0" maxOccurs="unbounded">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element name="AddressType" type="xs:string" />
                                        <xs:element name="AddressLine1" type="xs:string" />
                                        <xs:element name="AddressLine2" minOccurs="0" maxOccurs="1" />
                                        <xs:element name="City" type="xs:string" />
                                        <xs:element name="StateProvinceName" type="xs:string" />
                                        <xs:element name="PostalCode" type="xs:unsignedInt" />
                                        <xs:element name="CountryRegionName" type="xs:string" />
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

Next, I created a simple stored procedure that is used to query the AdventureWorks database and produce a result set in XML with name and address information for a small subset of customers. The customer IDs for four customers are hardcoded in the query to reduce the size of the sample XML.

USE AdventureWorks
GO
/*
** Stored Procedure to create sample XML data from
** AdventureWorks
*/
CREATE PROCEDURE Sales.getCustomerAddresses
AS

BEGIN
       SELECT   i. CustomerID
              , c. Title
              , c. FirstName
              , c. MiddleName
              , c. LastName
              , c. Suffix
              , c. Phone
              , c. EmailAddress
              , (
                 SELECT at. Name AS AddressType
                      , a. AddressLine1
                      , a. AddressLine2
                      , a. City
                      , StateProvinceName = sp.Name
                      , a. PostalCode
                      , CountryRegionName = cr.Name
                 FROM   Sales. CustomerAddress ca
                        INNER JOIN Person.Address a
                            ON a. AddressID = ca .AddressID
                        INNER JOIN Person.StateProvince sp
                            ON sp. StateProvinceID = a.StateProvinceID
                        INNER JOIN Person.CountryRegion cr
                            ON cr. CountryRegionCode = sp.CountryRegionCode
                        INNER JOIN Person.AddressType at
                            ON ca. AddressTypeID = at.AddressTypeID
                 WHERE  ca. CustomerID = i .CustomerID
                FOR
                 XML PATH ('CustomerAddresses')
                   , TYPE
                )
       FROM     Sales. Individual i
                INNER JOIN Person.Contact c
                    ON c. ContactID = i .ContactID
       WHERE    i. CustomerID IN (11532, 11279, 13145 , 13311)
      FOR
       XML PATH ('Customer')
         , TYPE

END
GO

Executing the stored procedure in SSMS:

EXEC Sales.getCustomerAddresses

Returns this result set:

<Customer>
  <CustomerID>11279</CustomerID>
  <FirstName>Amanda</FirstName>
  <MiddleName>S</MiddleName>
  <LastName>Cook</LastName>
  <Phone>252-555-0177</Phone>
  <EmailAddress>amanda3@adventure-works.com</EmailAddress>
  <CustomerAddresses>
    <AddressType>Shipping</AddressType>
    <AddressLine1>4098 Woodcrest Dr.</AddressLine1>
    <City>Everett</City>
    <StateProvinceName>Washington</StateProvinceName>
    <PostalCode>98201</PostalCode>
    <CountryRegionName>United States</CountryRegionName>
  </CustomerAddresses>
  <CustomerAddresses>
    <AddressType>Home</AddressType>
    <AddressLine1>9187 Vista Del Sol</AddressLine1>
    <City>Everett</City>
    <StateProvinceName>Washington</StateProvinceName>
    <PostalCode>98201</PostalCode>
    <CountryRegionName>United States</CountryRegionName>
  </CustomerAddresses>
</Customer>
<Customer>
  <CustomerID>11532</CustomerID>
  <FirstName>Lauren</FirstName>
  <LastName>Miller</LastName>
  <Phone>827-555-0183</Phone>
  <EmailAddress>lauren24@adventure-works.com</EmailAddress>
  <CustomerAddresses>
    <AddressType>Shipping</AddressType>
    <AddressLine1>6202 Seeno St.</AddressLine1>
    <City>Sammamish</City>
    <StateProvinceName>Washington</StateProvinceName>
    <PostalCode>98074</PostalCode>
    <CountryRegionName>United States</CountryRegionName>
  </CustomerAddresses>
  <CustomerAddresses>
    <AddressType>Home</AddressType>
    <AddressLine1>6437 Brookview Dr.</AddressLine1>
    <City>Redmond</City>
    <StateProvinceName>Washington</StateProvinceName>
    <PostalCode>98052</PostalCode>
    <CountryRegionName>United States</CountryRegionName>
  </CustomerAddresses>
</Customer>
<Customer>
  <CustomerID>13145</CustomerID>
  <FirstName>Gilbert</FirstName>
  <LastName>Xu</LastName>
  <Phone>798-555-0118</Phone>
  <EmailAddress>gilbert9@adventure-works.com</EmailAddress>
  <CustomerAddresses>
    <AddressType>Shipping</AddressType>
    <AddressLine1>1010 Maple</AddressLine1>
    <City>Baltimore</City>
    <StateProvinceName>Maryland</StateProvinceName>
    <PostalCode>21201</PostalCode>
    <CountryRegionName>United States</CountryRegionName>
  </CustomerAddresses>
  <CustomerAddresses>
    <AddressType>Home</AddressType>
    <AddressLine1>7779 Merry Drive</AddressLine1>
    <City>Cheektowaga</City>
    <StateProvinceName>New York</StateProvinceName>
    <PostalCode>14227</PostalCode>
    <CountryRegionName>United States</CountryRegionName>
  </CustomerAddresses>
</Customer>
<Customer>
  <CustomerID>13311</CustomerID>
  <FirstName>Isabella</FirstName>
  <LastName>Reed</LastName>
  <Phone>754-555-0118</Phone>
  <EmailAddress>isabella85@adventure-works.com</EmailAddress>
  <CustomerAddresses>
    <AddressType>Shipping</AddressType>
    <AddressLine1>5297 Algiers Drive</AddressLine1>
    <City>Renton</City>
    <StateProvinceName>Washington</StateProvinceName>
    <PostalCode>98055</PostalCode>
    <CountryRegionName>United States</CountryRegionName>
  </CustomerAddresses>
  <CustomerAddresses>
    <AddressType>Home</AddressType>
    <AddressLine1>6351 22nd Ave.</AddressLine1>
    <City>Renton</City>
    <StateProvinceName>Washington</StateProvinceName>
    <PostalCode>98055</PostalCode>
    <CountryRegionName>United States</CountryRegionName>
  </CustomerAddresses>
</Customer>

Now, the we’ve got the XSD, and the stored procedure completed we can build the SSIS Package to execute the procedure and create the XML output file.

Building the SSIS Package:

First, create an ADO.NET Connection to the AdventureWorks database and name the connection AdventureWorks.

Declare a variable with a data type of sting.  This will be used to hold the returned result set from the stored procedures:

Next we add an Execute SQL Task to call our stored procedure

Setting up the Execute SQL Task.

On the General settings tab, specify the connection information for our ADO.NET connection, the stored procedure to be executed, and that the ResultSet is XML:

On the Result Set tab, we are going to assign the result set returned from the stored procedure to the User::XMLResult variable defined earlier. It’s also important that you change the Result Name from the default of “NewResultName” to Zero (0), otherwise when executing the package you’ll receive an error

Setting up the XML Task

Next add an XML Task to the Project

On the General settings tab this is how the default properties initially display:

What we need to do first is change the Input Operation Type from Diff to XSLT.  This will expose the properties needed for using an XSLT.

Now make the following changes.

• The SourceType, which is the XML we want to transform is a variable.  The variable, User::XMLResult, holds the output from the stored procedure executed in the previous Execute SQL Task
• The Output Specifies where we want to save the results of the transformation. In our case, we want to save the results to a file so we select a File Connection.
• When presented with the <New File connection…> option in the dropdown, specify a new file for the output:

And fill out the dialog to create a new file

The Second Operand, is where we specify the XSLT to be applied against the Input Source.  For this example, we’re using direct input.  If this package was to be deployed to a production system, I would store the XSLT in a file rather than using direct input.

When presented with the XML Source Editor, paste the XSLT shown below into the source editor:

The completed XML Task – General settings tab.

XSLT

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml"
            indent="yes"
            omit-xml-declaration="no" />

<xsl:template match="*|@*|text()">
   <xsl:copy>
      <xsl:apply-templates select="*|@*|text()" />
   </xsl:copy>
</xsl:template>

<xsl:template match="ROOT">
	<CustomerData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://www.jaybonk.com/schemas/CustomerData.xsd">
     <xsl:apply-templates/>
  </CustomerData>
</xsl:template>

</xsl:stylesheet>

What the XSLT does…

• The <xsl:stylesheet> tag specifies that our output should be rendered as XML
• The first <xsl:template> matches everything in the source document; all elements (*), attributes (@*) and text (text()).  The remainder of the tag specifies that the document is to be copied and any additionally defined templates are recursively applied
• The second <xsl:template>, looks for any elements matching “ROOT”, and rewrites the opening and closing tags as “CustomerData”, with the opening tag additionally containing the required namespaces.
• Within the second template, the <xsl:apply-templates/> signifies that all children of the current node are processed.  In our case copying them to the output document.

Full disclosure, I’m a novice when it comes to working with XSLT and I was able to get this example working with just some rudimentary knowledge.  I do see XML, XPATH, XQuery, and XSLT as very powerful, flexible, and extremely useful.  I am looking forward to learning and working with them much more.

The resulting output file, after the XSLT has been applied:

<?xml version="1.0" encoding="utf-8"?>
<CustomerData xsi:noNamespaceSchemaLocation="http://www.jaybonk.com/schemas/CustomerData.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Customer>
    <CustomerID>11279</CustomerID>
    <FirstName>Amanda</FirstName>
    <MiddleName>S</MiddleName>
    <LastName>Cook</LastName>
    <Phone>252-555-0177</Phone>
    <EmailAddress>amanda3@adventure-works.com</EmailAddress>
    <CustomerAddresses>
      <AddressType>Shipping</AddressType>
      <AddressLine1>4098 Woodcrest Dr.</AddressLine1>
      <City>Everett</City>
      <StateProvinceName>Washington</StateProvinceName>
      <PostalCode>98201</PostalCode>
      <CountryRegionName>United States</CountryRegionName>
    </CustomerAddresses>
    <CustomerAddresses>
      <AddressType>Home</AddressType>
      <AddressLine1>9187 Vista Del Sol</AddressLine1>
      <City>Everett</City>
      <StateProvinceName>Washington</StateProvinceName>
      <PostalCode>98201</PostalCode>
      <CountryRegionName>United States</CountryRegionName>
    </CustomerAddresses>
  </Customer>
  <Customer>
    <CustomerID>11532</CustomerID>
    <FirstName>Lauren</FirstName>
    <LastName>Miller</LastName>
    <Phone>827-555-0183</Phone>
    <EmailAddress>lauren24@adventure-works.com</EmailAddress>
    <CustomerAddresses>
      <AddressType>Shipping</AddressType>
      <AddressLine1>6202 Seeno St.</AddressLine1>
      <City>Sammamish</City>
      <StateProvinceName>Washington</StateProvinceName>
      <PostalCode>98074</PostalCode>
      <CountryRegionName>United States</CountryRegionName>
    </CustomerAddresses>
    <CustomerAddresses>
      <AddressType>Home</AddressType>
      <AddressLine1>6437 Brookview Dr.</AddressLine1>
      <City>Redmond</City>
      <StateProvinceName>Washington</StateProvinceName>
      <PostalCode>98052</PostalCode>
      <CountryRegionName>United States</CountryRegionName>
    </CustomerAddresses>
  </Customer>
  <Customer>
    <CustomerID>13145</CustomerID>
    <FirstName>Gilbert</FirstName>
    <LastName>Xu</LastName>
    <Phone>798-555-0118</Phone>
    <EmailAddress>gilbert9@adventure-works.com</EmailAddress>
    <CustomerAddresses>
      <AddressType>Shipping</AddressType>
      <AddressLine1>1010 Maple</AddressLine1>
      <City>Baltimore</City>
      <StateProvinceName>Maryland</StateProvinceName>
      <PostalCode>21201</PostalCode>
      <CountryRegionName>United States</CountryRegionName>
    </CustomerAddresses>
    <CustomerAddresses>
      <AddressType>Home</AddressType>
      <AddressLine1>7779 Merry Drive</AddressLine1>
      <City>Cheektowaga</City>
      <StateProvinceName>New York</StateProvinceName>
      <PostalCode>14227</PostalCode>
      <CountryRegionName>United States</CountryRegionName>
    </CustomerAddresses>
  </Customer>
  <Customer>
    <CustomerID>13311</CustomerID>
    <FirstName>Isabella</FirstName>
    <LastName>Reed</LastName>
    <Phone>754-555-0118</Phone>
    <EmailAddress>isabella85@adventure-works.com</EmailAddress>
    <CustomerAddresses>
      <AddressType>Shipping</AddressType>
      <AddressLine1>5297 Algiers Drive</AddressLine1>
      <City>Renton</City>
      <StateProvinceName>Washington</StateProvinceName>
      <PostalCode>98055</PostalCode>
      <CountryRegionName>United States</CountryRegionName>
    </CustomerAddresses>
    <CustomerAddresses>
      <AddressType>Home</AddressType>
      <AddressLine1>6351 22nd Ave.</AddressLine1>
      <City>Renton</City>
      <StateProvinceName>Washington</StateProvinceName>
      <PostalCode>98055</PostalCode>
      <CountryRegionName>United States</CountryRegionName>
    </CustomerAddresses>
  </Customer>
</CustomerData>

So where did the <ROOT> tag come from?

The XSLT, applies changes to the “ROOT” element.  But if you look at the XML as returned from the stored procedure there is no <ROOT> element, the first element returned is the first <Customer> element.  The Execute SQL task wraps the result set in a <ROOT></ROOT> node as shown below.  I added a script task to display the User::XMLResult variable in a message box.

Comments (4)

I was recently working with a client on migrating from one hosting provider to another.  This involved migrating the databases to the new hosting center, including all the SQL Agent Jobs.

A number of the SQL Agent Jobs execute SSIS packages. The job for one of the critical packages was particularly problematic.  For the migration, I used the deployment utility created for and deployed on the old server.  The data sources for the staging and production databases, and the location of the incoming XML file and associated XSD were all stored in a dtsconfig file. The only exception was the connection for the SMTP server.  For some reason, this connection was hardcoded in the package, rather than set in the configuration file.

Looking at the Data sources in SQL Agent, I tried to override the connection string with the address of the new SMTP Server.

This is what the data sources tab originally looked like:

And this is what the original command line looked like:

I updated the data source for the SMTP connection string to use the correct IP Address for the new SMTP Server:

And this was the resulting update to the command line that was made by SQL Agent:

I saw the update to the command line, with the new values for the SMTP connection and felt confident that the update was successful.  However, that was short lived.  The first execution of the job failed with the following error:

That was certainly not what was expected, and a bit disappointing to say the least. The updated command line as generated was:

/FILE "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Packages\ProviderLoad\ProviderLoad.dtsx"  /CONNECTION "SMTP Connection Manager";"\"SmtpServer=192.168.33.102;UseWindowsAuthentication=False;EnableSsl=False;\"" /CHECKPOINTING OFF /REPORTING E

I reset the connection a number of times each time generating the same error.  What I noticed, was that the connection string was quoted.  This was the escaped quotes \” prior to  SmtpServer and after EnableSsl=False;.  None of the connection strings, in the dtsconfig were quoted, so on a wing and a prayer I removed the escaped quotes from the command line. By going back to the command line tab and selecting Edit the command line manually and removing the escaped quotes:

And then manually running the job yielded

I’m glad that I was able to get the package working. However, I was a bit surprised that the update generated by SQL Agent for the change to the connection string produced an invalid argument.  The package has since been updated and the connection string for the SMTP Server is now set in the dtsconfig.