Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed
."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/defaul...kb;en-us;818899
http://support.microsoft.com/defaul...kb;en-us;829444
http://support.microsoft.com/defaul...kb;en-us;812995
http://support.microsoft.com/defaul...kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step fail
ed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>sql

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SC
SC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default...b;en-us;818899
http://support.microsoft.com/default...b;en-us;829444
http://support.microsoft.com/default...b;en-us;812995
http://support.microsoft.com/default...b;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

Problem running a SQL job

I have a SQL job that runs as part of a nightly load of data tables. Part
of the job is a DTS job (loading a table from a text file), and other parts
of it are either sql code, or calling of a stored procedure.
We have 2 production servers, both (I thought) set up identically. SQL
2000, most recent service packs.
However, 2 times in the last several weeks, (3 times overall) we've gotten
this error in the same spot:
"A floating point exception occurred in the user process. Current
transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
We went back & checked the source table for it, and saw no problems.
We took the same job (a collection of about 10 stored procedure calls) that
looked like this:
sp_build_table_1
go
sp_build_table_2
go
sp_build_table_3
go
and so on...
and pulled it into query analyzer.
When run in Query Analyzer from a desktop (not the server), the jobs run
fine - no floating point exceptions or anything.
Any ideas as to what the problem might be? Not sure if it is a dts problem,
a programming problem or just a server setup issue.
Thanks,
SCSC,
There are several reasons that this might manifest itself that have nothing
to do with the data and everything to do with the complexity of a query.
You might check out these KB articles and see if they help.
http://support.microsoft.com/default.aspx?scid=kb;en-us;818899
http://support.microsoft.com/default.aspx?scid=kb;en-us;829444
http://support.microsoft.com/default.aspx?scid=kb;en-us;812995
http://support.microsoft.com/default.aspx?scid=kb;en-us;816503
Russell Fields
"Goober" <me@.privacy.net> wrote in message
news:uVnH2pFhEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I have a SQL job that runs as part of a nightly load of data tables. Part
> of the job is a DTS job (loading a table from a text file), and other
parts
> of it are either sql code, or calling of a stored procedure.
> We have 2 production servers, both (I thought) set up identically. SQL
> 2000, most recent service packs.
> However, 2 times in the last several weeks, (3 times overall) we've gotten
> this error in the same spot:
> "A floating point exception occurred in the user process. Current
> transaction is canceled. [SQLSTATE 42000] (Error 3628). The step failed."
> We went back & checked the source table for it, and saw no problems.
> We took the same job (a collection of about 10 stored procedure calls)
that
> looked like this:
> sp_build_table_1
> go
> sp_build_table_2
> go
> sp_build_table_3
> go
> and so on...
> and pulled it into query analyzer.
> When run in Query Analyzer from a desktop (not the server), the jobs run
> fine - no floating point exceptions or anything.
> Any ideas as to what the problem might be? Not sure if it is a dts
problem,
> a programming problem or just a server setup issue.
> Thanks,
> SC
>

Problem loading XML Files

I have been trying unsuccessfully to load a directory full of XML files into SQL Server 2005 Standard. I have not been able to make it work, and I would be grateful for any advice.

A simple version of the files that I am trying to load is TEST.NWS just below. I have removed almost all of the content from the files, leaving as a file TEST.NWS. (Hopefully I have not cut out too much.) This version of the test file leaves me with the same error messages I have with the full file. I have a directory with many such files and each file is much too big to edit manually.

Start TEST.NWS--

<?xml version="1.0" encoding="UTF-8"?>

<NewsML Version="1.2">

<NewsComponent xml:lang="en-us" Duid="NC001">

</NewsComponent>

</NewsML>

<?xml version="1.0" encoding="UTF-8"?>

<NewsML Version="1.2">

<NewsComponent xml:lang="en-us" Duid="NC002">

</NewsComponent>

</NewsML>

END-

The schema I am using is here: http://www.newsml.org/dl.php?fn=NewsML/1.2/specification/NewsML_1.2.xsd

I also tried this schema: http://www.nitf.org/IPTC/NITF/3.3/specification/schema/nitf-3-3.xsd

One version of the steps that I follow are here: http://msdn2.microsoft.com/en-us/library/ms139886.aspx At stage 8 of the "how to" recipe, when I click on the Columns, I get an error. The wording of the error depends on which of the two schemas I use. If I instead click next I also get an error. Clearly I am doing something wrong. Any suggestion for how to fix this will be most welcome. Thanks.

mzfrank,

Please you please copy and paste the error messages you are getting and optional the T-SQL script you are running?

Thanks,

Galex

|||

I was not using a script. I was following the MSDN "How to: Extract Data Using the XML Source" it uses SSIS rather than a T-SQL script. (Is this a task better done using T-SQL rather than SSIS?)

If I use NewsML_1.2.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: There was an error setting up the mapping. The 'http://www.w3.org/XML/1998/namespace:lang' attribute is not declared.

If I use nitf-3-3.xsd as the schema I get:

Error at Data Flow Task [XML Source [1]]: The XML Source Adapter does not support mixed content model on Complex Types.

If I ask it to infer a schem I get:

Unable to infer the XSD from the XML file. The XML contains multiple namespaces. (Microsoft Visual Studio)

|||

I am having the same issue. I posted in the SSIS forum, but this is what I wrote there:

I can't import from XML files using a foreach loop. I load an XML file with a generated XSD. When I map the file to the table it has no errors. If I now go back and change to a different XML file, I get an error:

"Error 1 Validation error. Data Flow Task: DTS.Pipeline: input column "COLUMNNAME" (129) has lineage ID 2115 that was not previously used in the Data Flow task. Package.dtsx 0 0"

This is for testing purposes. When I run the foreach loop it does not work. Ironically, I do the exact same thing in another foreach loop with a completely different XML and it works fine.

Here is the broken XSD:

<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="ComputerStatus">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="computer">
<xs:complexType>
<xs:attribute name="GUID" type="xs:string" use="optional" />
<xs:attribute name="WSUSServer" type="xs:string" use="optional" />
<xs:attribute name="WSUSGroup" type="xs:string" use="optional" />
<xs:attribute name="computerName" type="xs:string" use="optional" />
<xs:attribute name="OSBuild" type="xs:unsignedShort" use="optional" />
<xs:attribute name="OSSP" type="xs:unsignedByte" use="optional" />
<xs:attribute name="Model" type="xs:string" use="optional" />
<xs:attribute name="Make" type="xs:string" use="optional" />
<xs:attribute name="BIOS" type="xs:string" use="optional" />
<xs:attribute name="Processor" type="xs:string" use="optional" />
<xs:attribute name="LastReportedStatus" type="xs:string" use="optional" />
<xs:attribute name="LastSyncTime" type="xs:string" use="optional" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Help. Please. What have I done wrong. I imagine there is a flaw in my XML, but I can't pinpoint it.

Here is a sample of the XML file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<ComputerStatus>
<computerCount QTY="1" />
<computer GUID="edc2b6a5-5d86-467c-8c89-43fa18ae5921" WSUSServer="WSUS" WSUSGroup="THIS" computerName="COMPUTER" OSBuild="3790" OSSP="1" Model="COMPUTERTYPE" Make="HP" BIOS="1" Processor="x86" LastReportedStatus="10/25/2006 12:00:49 PM" LastSyncTime="10/25/2006 11:57:09 AM" />
</ComputerStatus>

Problem loading satellite assemblies in RS2000

Hi,

I have a problem calling Assembly.GetSatelliteAssembly inside a custom assembly referenced by a report. This assembly is used only for localization, and all it does is load the appropriate localized satellite assembly and pull a translated string out of it. The problem doesn't manifest itself if the report is viewed by a user that has higher privileges, but for a user with only the Browser role the GetSatelliteAssembly method throws a System.IO.FileLoadException "Access is denied: Resources.resources" (the custom assembly is called Resources.dll).

The assembly has no strong key and has the AllowPartiallyTrustedCallers attribute in AssemblyInfo. I did set fullTrust in the rssrvpolicy.config, like this:

<CodeGroup
class="FirstMatchCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="TestCodeGroup"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin\Resources.dll"/>
</CodeGroup>

I even tried asserting a FileIOPermission on the satellite dll prior to calling GetSatelliteAssemblies: the assert succeeds but the problem remains.

I'm using RS2000 SP2 with the whatsit hotfix (build no. 1402, IIRC). Note that this exception doesn't seem to appear if I try the same report in RS2005, but migrating is currently not an option.

What should I do? If this is a bug in RS2000 is there a workaround? Any suggestions are welcome.

Thanks in advance!
The old rule has proven true again, the answer comes by itself once you post the question to others...

The fusion log showed that GetSatelliteAssembly searches for the satellite dll in the ReportServer/bin folder, but also in Temporary ASP.Net Files... So, one part of the solution was to give the user modify permission on it. The other part was to give him read permission on the satellite assembly - and this is strange because the main assembly doesn't require extra permissions, but the satellite assembly does.

I'm sure I'm not seeing the big picture here, so if someone can shed any amount of light () on this issue, it would be appreciated.

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob

Problem Loading Data From FlatFile Source Data For Column overflowed the disk I/O buffer

Hi i am trying to do a straight forward load from a Flatfile source , i have defined the columns according to the lenghts defined in the Data Dictionary Provided but when i am trying to run the Task i am encounterring this error

The column data for column "Column 20" overflowed the disk I/O buffer.

I tried to add another column 21 at the end and truncate or leave that column unmapped to destination but the same problem occurs for column 21 what should i do to over come this .

In case of Bad Data how to clean up the source.. Please help me with this

You can find a lot of posts in this forum about the way SSIS parses flat files. It does that column by column. It means if your column delimiter is missing at column 21 it will continue parsing until that delimiter is found and it will eat all the columns in between and eventually make the data oveflow the reserved buffer.

In this case I would try to find the row where your data is misconigured. You can do that by cutting your file in halfs until you find the place which breaks parsing.

HTH,

Bob

|||

Hi Bob,

I understand the internal working. But then what will be the solution to handle? Again the question is "In case of Bad Data how to clean up the source via SSIS" - already raised by Pradeep here. Kristen's reply is partial. It doesn't solve here as there exist no workaround for skipping these rows. The clue is to use the Ragged right, where the Column size cannot exceed length of 4000 (Limitation to be noted) and splitting hundreds of such columns (if do have) and checking if the length of these individual columns exceed max length and finally mapping them is all not only too laborious but also inefficient.

On the other I tried ignoring or redirecting these rows, but the Flat File Source OLEDB Adapter doesn't seem to handle these rows at all. How will I skip these rows. I am using ForEach Loop loads GBs of data. and probability is that any of the file might contain row corruption.

Thanks

Subhash Subramanyam

|||

Hi Subhash,

Unfortunately, there is no in-box SSIS solution for this. As the current flat file parser cannot identify the broken rows it does not have any way to skip or report errors about them.

You'll have to craft your own solution and there are examples of how other people did it in this forum. It certanly isn't pretty but I cannot offer better, for now.

Thanks,

Bob