Error loading file to SQL Server 2005

I am trying to load a text file into SQL Server 2005 and I get the following
error messages at the validation stage:
Messages
?Error 0xc00470fe: Data Flow Task: The product level is insufficient for
component "Source - A00U3UNN_Y2006014_FAD_1_VDF" (1).
(SQL Server Import and Export Wizard)
?Error 0xc00470fe: Data Flow Task: The product level is insufficient for
component "Data Conversion 1" (31).
(SQL Server Import and Export Wizard)
Does anyone know what product level is insufficient?
Is the compatibility level of the database you are inserting into not at 90?
Are you using DTS or SSIS?
Andrew J. Kelly SQL MVP
"Robin" <Robin@.discussions.microsoft.com> wrote in message
news:E4E163EE-8A30-418B-8255-26A2EA155C1C@.microsoft.com...
>I am trying to load a text file into SQL Server 2005 and I get the
>following
> error messages at the validation stage:
> Messages
> . Error 0xc00470fe: Data Flow Task: The product level is insufficient for
> component "Source - A00U3UNN_Y2006014_FAD_1_VDF" (1).
> (SQL Server Import and Export Wizard)
> . Error 0xc00470fe: Data Flow Task: The product level is insufficient for
> component "Data Conversion 1" (31).
> (SQL Server Import and Export Wizard)
>
> Does anyone know what product level is insufficient?
>

Error in datetime validation

xml including a datetime attribute is failing to validate against a simple schema held in an XML schema collection

The same xml validates correctly against the same schema in .Net

Is this a bug? (same behaviour is seen with dates)

Repro follows:

CREATE XML SCHEMA COLLECTION MyCollection AS '
<xs:schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://ns" xmlns:ns="http://ns" xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
<xs:complexType name="TestType">
<xs:attribute name="id" type="xs:string" use="required"> </xs:attribute>
<xs:attribute name="startDate" type="xs:dateTime" ></xs:attribute>
</xs:complexType>
<xs:element name="TestList" type="ns:TestType">
</xs:element>
</xs:schema>';
go
declare @.xmlvar xml(MyCollection)
set @.xmlvar=
'<TestList xmlns="http://ns" id="D882BA19-81FA-4F99-845D-D8AE57BD0699" startDate="2006-01-02T00:00:00" ></TestList>'

Msg 6926, Level 16, State 1, Line 2
XML Validation: Invalid simple type value: '2006-01-02T00:00:00'. Location: /*:TestList[1]/@.*:startDate

I have just found that the SQL implementation of XML requires the time zone - e.g. 'Z' suffix - but that the .Net implementation does not need the zone.

I now no longer have a problem as I will use the Z suffix - but it would be useful to know whether system.xml or SQL2005 are currently exhibiting the intended behaviour (assuming a target of consistency between the two)

|||

Your observation is correct that the server's schema processor requires the timezone specification - it normalizes the value to UTC and does not preserve the timezone.

The server uses its own XML schema validator, which is different from those in System.Xml and MSXML. As such, any server-side error is generated bu the server's XML schema processor. To isolate whether the error is given by the client or the server, invoke the validation at the server.

Hope this helps.

Thank you,

Shankar

Program Manager

Microsoft SQL Server

|||

Thanks Shankar - As I said in my last post, this is not a problem for me now. However, this is the second time that I have encountered tighter validation in the SQL engine than in system.xml (Previous thread was "XML Schema extension behaving differently between SQL2005 and VB.Net 2.0 ")

This can cause problems when developers do early development and testing saving to XML files. It means that when database integration takes place a different set of problems can occur - which would be completely avoidable if the validation was consistent between system.xml and the SQL engine.

Is there a goal to achieve consistency soon?

If not then it would certainly be useful to have sight of the known differences - I understand from Denis Ruckebusch in the previous thread that a list is being compiled

Thanks

|||

We are looking at relaxing some of the implementation restrictions in the server side validation based on feedback like yours and by removing some of the obstacles that required us to introduce the restrictions in the first place.

Best regards

Michael

|||

Hi Michael

That's encouraging - but for current development it would be very useful to have visibility of the known list of differences

Thanks

Error handling problem

I am running SQL 2000.
I have several procedures where I do general validation on entered values.
User procedures typically call these "checkprocedures" to verify all entered
values.
I thought I grasped the idea of error handling but must have done something
wrong.
When a wrong value is found I throw an exception in the checking procedure
and return an arbitrary return code other than 0, typically 1.
In the calling procedure I check the @.@.ERROR value and then take action
based on that. The problem is that excution does not stop after the exceptio
n
is thrown even if I explicitly end with a Return in the calling procedure.
What is wrong?
Why does not the execution in the main procedure stop?
Maybe it is overkill to use both exceptions and return values:
Should I skip the exception throwing and rely on the Return values?
Should I skip the Return values and rely on the exception throwing?
My idea was to avoid defining the validation and exception messages on many
places.
Code example enclosed below:
CREATE PROCEDURE dbo.CheckLanguage
@.LanguageKey char(5),
@.LanguageID int OUT
AS
SET @.LanguageID = NULL
-- Check if given language exists
SELECT @.LanguageID = LanguageID
FROM Language
WHERE LanguageKey = @.LanguageKey
IF @.LanguageID IS NULL
BEGIN
RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
16, 1, @.LanguageKey)
RETURN 1
END
GO
Calling procedure contains this code:
....
-- Make sure the given language key is valid
EXEC CheckLanguage @.LanguageKey, @.LanguageID OUT
SET @.ErrorNumber = @.@.ERROR
IF @.ErrorNumber <> 0
BEGIN
SET @.Response = 'Error_LanguageKey'
RETURN 0
END
...It seems it is the RETURN statement after the RAISERROR that resets the
@.@.ERROR to 0 again, making the calling procedure ....
What would you recommend?
Should I use RAISERROR or RETURN in the check procedure to inform the
calling procedure about the error?
I tend to the RAISERROR ....|||Since the procedure executes OK even if @.LanguageID is NULL, @.@.ERROR in your
calling statement will always be 0. @.@.ERROR traps the error number for the
most recently executed statement.
You need something like this
CREATE PROCEDURE dbo.CheckLanguage
@.LanguageKey char(5),
@.LanguageID int OUT
AS
SET @.LanguageID = NULL
-- Check if given language exists
SELECT @.LanguageID = LanguageID
FROM Language
WHERE LanguageKey = @.LanguageKey
IF @.LanguageID IS NULL
BEGIN
RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
16, 1, @.LanguageKey)
RETURN 1
END
Calling procedure contains this code:
....
-- Make sure the given language key is valid
DECLARE @.return_status int
EXEC @.return_status = CheckLanguage @.LanguageKey, @.LanguageID OUT
IF @.return_status = 1
BEGIN
SET @.Response = 'Error_LanguageKey'
RETURN 0
END
...
"Jakob Lithner" wrote:

> I am running SQL 2000.
> I have several procedures where I do general validation on entered values.
> User procedures typically call these "checkprocedures" to verify all enter
ed
> values.
> I thought I grasped the idea of error handling but must have done somethin
g
> wrong.
> When a wrong value is found I throw an exception in the checking procedure
> and return an arbitrary return code other than 0, typically 1.
> In the calling procedure I check the @.@.ERROR value and then take action
> based on that. The problem is that excution does not stop after the except
ion
> is thrown even if I explicitly end with a Return in the calling procedure.
> What is wrong?
> Why does not the execution in the main procedure stop?
> Maybe it is overkill to use both exceptions and return values:
> Should I skip the exception throwing and rely on the Return values?
> Should I skip the Return values and rely on the exception throwing?
> My idea was to avoid defining the validation and exception messages on man
y
> places.
>
> Code example enclosed below:
> CREATE PROCEDURE dbo.CheckLanguage
> @.LanguageKey char(5),
> @.LanguageID int OUT
> AS
> SET @.LanguageID = NULL
> -- Check if given language exists
> SELECT @.LanguageID = LanguageID
> FROM Language
> WHERE LanguageKey = @.LanguageKey
>
> IF @.LanguageID IS NULL
> BEGIN
> RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
> 16, 1, @.LanguageKey)
> RETURN 1
> END
> GO
>
> Calling procedure contains this code:
> ....
> -- Make sure the given language key is valid
> EXEC CheckLanguage @.LanguageKey, @.LanguageID OUT
> SET @.ErrorNumber = @.@.ERROR
> IF @.ErrorNumber <> 0
> BEGIN
> SET @.Response = 'Error_LanguageKey'
> RETURN 0
> END
> ...|||Thanks, I guess that is the best solution!