Hi all!
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
--
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Roman S. Golubin" wrote:
> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not nul
l
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>|||Hi, Chandra!
> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username
Showing posts with label identity. Show all posts
Showing posts with label identity. Show all posts
Tuesday, March 20, 2012
BUG: Internal Server Error
Hi all!
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?
hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Roman S. Golubin" wrote:
> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not null
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>
|||Hi, Chandra!
> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username
MSSQL 7.0 and MSSQL2000 Server.
When running query:
create table t (id int primary key identity(1,1), f int, u varchar not null
default USER)
GO
create view v
as
select id, f
from t
where u = USER
GO
insert v (f)
select f
from v
group by f
GO
then get error:
Server: Msg 8624, Level 16, State 9, Line 1
Internal SQL Server error.
It's a bug?
hi
just try this way
create table t
(id int primary key identity(1,1),
f int, u varchar not null
default 'USER')
GO
create view v
as
select id, f
from t
where u = 'USER'
GO
insert v (f) select f from t group by f
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Roman S. Golubin" wrote:
> Hi all!
> MSSQL 7.0 and MSSQL2000 Server.
> When running query:
> --
> create table t (id int primary key identity(1,1), f int, u varchar not null
> default USER)
> GO
> create view v
> as
> select id, f
> from t
> where u = USER
> GO
> insert v (f)
> select f
> from v
> group by f
> GO
> --
> then get error:
> --
> Server: Msg 8624, Level 16, State 9, Line 1
> Internal SQL Server error.
>
> It's a bug?
>
>
|||Hi, Chandra!
> just try this way
> create table t
> (id int primary key identity(1,1),
> f int, u varchar not null
> default 'USER')
> GO
BOL. USER.
-> Use USER to return the current user's database username
Monday, March 19, 2012
bug with ident_current
Hello,
I have tried developped a stored proc that is using the ident_current('tablename') function to get the last Identity ID.
It was working fine. I drop and recreate the table (which reset the ID) and found that the ident_current said that the last Identity Id used was 1. The problem was that the table was empty. I insert a record and try it again. It said again that the last Identity ID was 1. After the insertion if the first record, everything is fine...
I would like to know if you know a way get 0 when the table is empty and 1 when there is only one identity id that have been used.
Thank
Felix Pageau
fpageau@.str.ca
You can test the functionnality with the following code:
create table identCurrentBugExeample(IDnumber int IDENTITY(1,1), aChar char(1))
Declare @.count as int
set @.count = (select count(IDnumber) from identCurrentBugExeample)
--Print the number of record in the table
print @.count
--Supposed to print 0 because there isn't any identity values that has been used
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')
--Print 1 because the last identity used is 1
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')
--Print 2 because the last identity used is 2
print ident_current('identCurrentBugExeample')
drop table identCurrentBugExeampleI'm sorry...is there a question here?|||Yes,
is there an option I can use with the fonction to know if there has been a record inserted. I mean, I would like to know if I can get "0" instead of 1 when there hasn't been any record inserted.
It is not because the count of the table is 0 that hasn't been any record inserted and deleted (and the next identity won't be 1...)|||Are you looking for SCOPE_IDENTITY?|||i'm not looking for SCOPE_IDENTITY. I need the to know what was the last value inserted into the IDENTITY column.
If there wasn't any record ever added, I want something like NULL or -1 to show that the table is still intact.
When there was at least one record added, I want the value of IDENTITY coloumn of the last record|||Ok...I admit it...I have NO idea what you're getting at...
anything like...
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), col2 char(1))
GO
SELECT MAX(Col1) FROM myTable99
INSERT INTO myTable99(Col2) SELECT 'A'
SELECT MAX(Col1) FROM myTable99
DROP TABLE myTable99
GO|||Hello,
the code you have provided works great if the record hasn't been deleted.
When you delete a record (or all the records), the next time you insert one, the value of the IDENTITY column will be x+1
Like:
ID Name GUID
1 Felix AAAAAjsdhksdkjk
2 Bob AAAASDFfsdjkdsj
... ...
7 Alycia ASDADnenefnefn
8 Roger AAAAAdfshdsfhjsdf
If I delete the record #8, and I insert a new one it will look like :
ID Name GUID
1 Felix AAAAAjsdhksdkjk
...
7 Alycia ASDADnenefnefn
9 NewName AAAACCCCddddd
Then I delete all the records
ID Name GUID
Now I must know what is the ID to add a new one (I need to know that before adding it because I'm saving some parts of the record on a Exchnage 2000 linked server (where I must put the ID), get the Global Unique ID of the record in Exchange 2k after saving and putting the guID back in the SQL server in the right record.)
If I do ident_current('tablename') and the value returned is 1. How can I know if the new record I will put in the table will have the ID 1 or 2 if when I do ident_current and there hasn't been anyrecord in the table I got 1 and when there is (or there was) only one record, I also got 1 ?
Do you have a clue on how to do this ?|||By the way, I can't get the GUID (global unique Identifier) directly from Exchange but only while I'm using the linked server to exchange from SQL Server. But I can get the ID from the exchange server while using the linked server and I can set it directly in Exchange...
I need a cross reference between the record (object because it is a OO databse) and the record in SQL server.
That is why I must get the next ID.
Create a dummy record with no informations
Send the ID back to exchange
Save the object in exchange with the ID
Send the GUID of exchange to SQL
Put the GUID in the right "dummy" record with the ID
Get the needed informations from the linked server
And that should work (that is working when I already have a record in the table in sql prior to adding the dummy record. But when I haven't put any, I got a bad ID for the first record that I had. Every other records added later will work fine).
It is why I need to know what is the last value of the IDENTITY column and get 0 when there wasn't any ever added to the table. But the function is giving 1 when there wasn't any record added and is also giving 1 when only one record has been added.
Thank a lot|||I think you will have to set Identity_SID starts from 0 then only..you will get '0' for Select IDENT_CURRENT('table1') when no reords inserted...
try playing with Identity_SID ...you will get the correct value...
see below For SQL help ---
IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.
Syntax
IDENT_CURRENT('table_name')
Arguments
table_name
Is the name of the table whose identity value will be returned. table_name is varchar, with no default.
Return Types
sql_variant
Remarks
IDENT_CURRENT is similar to the Microsoft SQL Server 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@.@.IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
I have tried developped a stored proc that is using the ident_current('tablename') function to get the last Identity ID.
It was working fine. I drop and recreate the table (which reset the ID) and found that the ident_current said that the last Identity Id used was 1. The problem was that the table was empty. I insert a record and try it again. It said again that the last Identity ID was 1. After the insertion if the first record, everything is fine...
I would like to know if you know a way get 0 when the table is empty and 1 when there is only one identity id that have been used.
Thank
Felix Pageau
fpageau@.str.ca
You can test the functionnality with the following code:
create table identCurrentBugExeample(IDnumber int IDENTITY(1,1), aChar char(1))
Declare @.count as int
set @.count = (select count(IDnumber) from identCurrentBugExeample)
--Print the number of record in the table
print @.count
--Supposed to print 0 because there isn't any identity values that has been used
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')
--Print 1 because the last identity used is 1
print ident_current('identCurrentBugExeample')
insert identCurrentBugExeample(aChar) values('a')
--Print 2 because the last identity used is 2
print ident_current('identCurrentBugExeample')
drop table identCurrentBugExeampleI'm sorry...is there a question here?|||Yes,
is there an option I can use with the fonction to know if there has been a record inserted. I mean, I would like to know if I can get "0" instead of 1 when there hasn't been any record inserted.
It is not because the count of the table is 0 that hasn't been any record inserted and deleted (and the next identity won't be 1...)|||Are you looking for SCOPE_IDENTITY?|||i'm not looking for SCOPE_IDENTITY. I need the to know what was the last value inserted into the IDENTITY column.
If there wasn't any record ever added, I want something like NULL or -1 to show that the table is still intact.
When there was at least one record added, I want the value of IDENTITY coloumn of the last record|||Ok...I admit it...I have NO idea what you're getting at...
anything like...
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), col2 char(1))
GO
SELECT MAX(Col1) FROM myTable99
INSERT INTO myTable99(Col2) SELECT 'A'
SELECT MAX(Col1) FROM myTable99
DROP TABLE myTable99
GO|||Hello,
the code you have provided works great if the record hasn't been deleted.
When you delete a record (or all the records), the next time you insert one, the value of the IDENTITY column will be x+1
Like:
ID Name GUID
1 Felix AAAAAjsdhksdkjk
2 Bob AAAASDFfsdjkdsj
... ...
7 Alycia ASDADnenefnefn
8 Roger AAAAAdfshdsfhjsdf
If I delete the record #8, and I insert a new one it will look like :
ID Name GUID
1 Felix AAAAAjsdhksdkjk
...
7 Alycia ASDADnenefnefn
9 NewName AAAACCCCddddd
Then I delete all the records
ID Name GUID
Now I must know what is the ID to add a new one (I need to know that before adding it because I'm saving some parts of the record on a Exchnage 2000 linked server (where I must put the ID), get the Global Unique ID of the record in Exchange 2k after saving and putting the guID back in the SQL server in the right record.)
If I do ident_current('tablename') and the value returned is 1. How can I know if the new record I will put in the table will have the ID 1 or 2 if when I do ident_current and there hasn't been anyrecord in the table I got 1 and when there is (or there was) only one record, I also got 1 ?
Do you have a clue on how to do this ?|||By the way, I can't get the GUID (global unique Identifier) directly from Exchange but only while I'm using the linked server to exchange from SQL Server. But I can get the ID from the exchange server while using the linked server and I can set it directly in Exchange...
I need a cross reference between the record (object because it is a OO databse) and the record in SQL server.
That is why I must get the next ID.
Create a dummy record with no informations
Send the ID back to exchange
Save the object in exchange with the ID
Send the GUID of exchange to SQL
Put the GUID in the right "dummy" record with the ID
Get the needed informations from the linked server
And that should work (that is working when I already have a record in the table in sql prior to adding the dummy record. But when I haven't put any, I got a bad ID for the first record that I had. Every other records added later will work fine).
It is why I need to know what is the last value of the IDENTITY column and get 0 when there wasn't any ever added to the table. But the function is giving 1 when there wasn't any record added and is also giving 1 when only one record has been added.
Thank a lot|||I think you will have to set Identity_SID starts from 0 then only..you will get '0' for Select IDENT_CURRENT('table1') when no reords inserted...
try playing with Identity_SID ...you will get the correct value...
see below For SQL help ---
IDENT_CURRENT
Returns the last identity value generated for a specified table in any session and any scope.
Syntax
IDENT_CURRENT('table_name')
Arguments
table_name
Is the name of the table whose identity value will be returned. table_name is varchar, with no default.
Return Types
sql_variant
Remarks
IDENT_CURRENT is similar to the Microsoft SQL Server 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three functions return last-generated identity values. However, the scope and session on which 'last' is defined in each of these functions differ.
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
@.@.IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
Bug with 'enable identity insert on'
I'm using sp1, tryng to copy from 2005 db to 2005 db, selecting many tables at once, delete target rows on, identity insert ON.
choosing not to optimize for many tables:
Error: 0xC0202009 at Data Flow Task, Destination - table1 [22]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'dbo.table1.".
Error: 0xC0202009 at Data Flow Task, Destination 3 - table2 [226]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
Etc., for each table
Optimizing for many tables:
Error: 0xC0202009 at {AB2253E6-28B4-4D5A-B843-236F8E53E075}, OLE DB Destination [2]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The statement has been terminated.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'dbo.table1.".
Information: 0x402090E0 at {AB2253E6-28B4-4D5A-B843-236F8E53E075}, OLE DB Destination [2]: The final commit for the data insertion has ended.
When I drop and recreate the tables manually and set 'identity insert on' it doesn't bring the identity field over AT ALL.
I like that MS has tried to make it easier to move many tables at once by allowing us to set 'enable identity insert' to everything at once, but it doesn't appear to work. ( I've been puzzling over how it could work, anyway...)
Before sp 1, I had to go into each table by clicking the 'edit' button, and it would bring the correct identity value over, but the tables would no longer have the identity property on that field.
I have also tried 'run as a transaction' to no avail...
Well, the error is about PK violation, not about the Identity column definition.
|||Unless the identity column is the PK, in which case you should not have any columns mapped to the identity field in the destination OLE DB component. Leave it blank.|||The identity column is indeed the PK - NOTE: I didn't design this.
Because the identity column is the PK, and various tables reference the PK, I need to be able to import the actual value.
I did tell it to delete the data first.
If I drop the tables and re-create them with no data, it doesn't bring the identity value over at all, the field auto-numbers.
I seem to be in a no-win situation. Even if I drop all the keys, i still have the problem with not getting the actual identity value inserted.
If the tool is going to allow you to indicate the data should be cleared in the tables first, I think there should be some indication regarding the keys.
I suppose my problem actually lies with the identity value not coming over. I have searched in this forum and found that several others seem to be having the same problem, but have not seen a resolution.
?
|||Are you using "fast load" in the OLE DB destination with "Keep identity" checked?|||Perhaps I'm misunderstanding the problem but I have just managed to populate a table with an identity column as the PK by loading values into that column.
Here's the code to set it up:
Code Snippet
CREATE DATABASE tmp
GO
USE tmp
GO
CREATE TABLE source (id INT IDENTITY(1,1) PRIMARY key, chr CHAR(1))
GO
INSERT source VALUES ('a')
INSERT source VALUES ('b')
INSERT source VALUES ('c')
CREATE TABLE destination (id INT IDENTITY(1,1) PRIMARY key, chr CHAR(1))
GO
and below is the package that realised it. Let me know what is different in your scenario.
-Jamie
Code Snippet
<?xml version="1.0"?><DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="MSDTS.Package.1"><DTS:Property DTS:Name="PackageFormatVersion">2</< FONT>DTS:Property><DTS:Property DTS:Name="VersionComments"></< FONT>DTS:Property><DTS:Property DTS:Name="CreatorName">CT\jtbx</< FONT>DTS:Property><DTS:Property DTS:Name="CreatorComputerName">2UA63600NJ</< FONT>DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">3/29/2007 3:25:55 PM</< FONT>DTS:Property><DTS:Property DTS:Name="PackageType">5</< FONT>DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</< FONT>DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</< FONT>DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</< FONT>DTS:Property><DTS:Property DTS:Name="VersionMajor">1</< FONT>DTS:Property><DTS:Property DTS:Name="VersionMinor">0</< FONT>DTS:Property><DTS:Property DTS:Name="VersionBuild">1</< FONT>DTS:Property><DTS:Property DTS:Name="VersionGUID">{2B4A9733-B18D-47EE-A743-7219941EEEB7}</< FONT>DTS:Property><DTS:Property DTS:Name="EnableConfig">0</< FONT>DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></< FONT>DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</< FONT>DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</< FONT>DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</< FONT>DTS:Property>
<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</< FONT>DTS:Property><DTS:Property DTS:Name="ObjectName">LocalHost</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{711EB6A5-1873-451F-AF3A-8453EC797462}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</< FONT>DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</< FONT>DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=.;Initial Catalog=tmp;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</< FONT>DTS:Property></< FONT>DTS:ConnectionManager></< FONT>DTS:ObjectData></< FONT>DTS:ConnectionManager>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><Package xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsControlFlowDiagram><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="4" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="20717" y="17568" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="1" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="Validation warnings occurred." left="0" top="0" logicalid="3" controlid="3" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{D1234F40-926A-4975-86F0-EFC71316CEB6}" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></< FONT>DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</< FONT>DTS:Property><DTS:Property DTS:Name="ObjectName">{CBA6818F-FBB0-4EE8-A1BF-AD304660C52A}</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{F740740D-718C-47DB-9D17-E2071E8A5DF3}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName"></< FONT>DTS:Property></< FONT>DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><TaskHost xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DtsDataFlowDiagram><dwd:BoundingTop>3863</dwd:BoundingTop><dwd:Layout><dds>
<diagram fontclsid="{0BE35203-8F91-11CE-9DE3-00AA004BB851}" mouseiconclsid="{0BE35204-8F91-11CE-9DE3-00AA004BB851}" defaultlayout="Microsoft.DataWarehouse.Layout.GraphLayout" defaultlineroute="Microsoft.DataWarehouse.Layout.GraphLayout" version="7" nextobject="7" scale="100" pagebreakanchorx="0" pagebreakanchory="0" pagebreaksizex="0" pagebreaksizey="0" scrollleft="0" scrolltop="0" gridx="150" gridy="150" marginx="1000" marginy="1000" zoom="100" x="18071" y="16642" backcolor="15334399" defaultpersistence="2" PrintPageNumbersMode="3" PrintMarginTop="0" PrintMarginBottom="635" PrintMarginLeft="0" PrintMarginRight="0" marqueeselectionmode="1" mousepointer="0" snaptogrid="0" autotypeannotation="1" showscrollbars="0" viewpagebreaks="0" donotforceconnectorsbehindshapes="0" backpictureclsid="{00000000-0000-0000-0000-000000000000}">
<font>
<ddsxmlobjectstreamwrapper binary="01010000900180380100065461686f6d61" />
</font>
<mouseicon>
<ddsxmlobjectstreamwrapper binary="6c74000000000000" />
</mouseicon>
</diagram>
<layoutmanager>
<ddsxmlobj />
</layoutmanager>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Source" left="11298" top="3863" logicalid="4" controlid="4" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{D1234F40-926A-4975-86F0-EFC71316CEB6}/components/1" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="DdsShapes.DdsObjectManagedBridge.1" tooltip="OLE DB Destination" left="12700" top="6509" logicalid="5" controlid="5" masterid="0" hint1="0" hint2="0" width="3598" height="1164" noresize="0" nomove="0" nodefaultattachpoints="0" autodrag="1" usedefaultiddshape="1" selectable="1" showselectionhandles="1" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobjectstreaminitwrapper binary="000800000e0e00008c040000" />
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{D1234F40-926A-4975-86F0-EFC71316CEB6}/components/52" vartype="8" />
<property name="ShowConnectorSource" value="0" vartype="2" />
</ddsxmlobj>
</layoutobject>
<shape groupshapeid="0" groupnode="0" />
</ddscontrol>
<ddscontrol controlprogid="MSDDS.Polyline" left="12698" top="4628" logicalid="6" controlid="6" masterid="0" hint1="0" hint2="0" width="2201" height="2381" noresize="0" nomove="0" nodefaultattachpoints="1" autodrag="0" usedefaultiddshape="0" selectable="1" showselectionhandles="0" allownudging="1" isannotation="0" dontautolayout="0" groupcollapsed="0" tabstop="1" visible="1" snaptogrid="0">
<control>
<ddsxmlobj>
<polyline endtypedst="3" endtypesrc="1" usercolor="32768" linestyle="0" linerender="1" customendtypedstid="0" customendtypesrcid="0" adornsvisible="1" />
</ddsxmlobj>
</control>
<layoutobject>
<ddsxmlobj>
<property name="LogicalObject" value="{D1234F40-926A-4975-86F0-EFC71316CEB6}/paths/69" vartype="8" />
<property name="Virtual" value="0" vartype="11" />
<property name="VisibleAP" value="0" vartype="3" />
</ddsxmlobj>
</layoutobject>
<connector lineroutestyle="Microsoft.DataWarehouse.Layout.GraphLayout" sourceid="4" destid="5" sourceattachpoint="7" destattachpoint="6" segmenteditmode="0" bendpointeditmode="0" bendpointvisibility="2" relatedid="0" virtual="0">
<point x="13097" y="5027" />
<point x="13097" y="5768" />
<point x="14499" y="5768" />
<point x="14499" y="6509" />
</connector>
</ddscontrol>
</dds></dwd:Layout></dwd:DtsDataFlowDiagram><dwd:DtsComponentDesignerPropertiesList><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">52 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 DataSourceViewID</dwd:key></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">52 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty><dwd:DtsComponentDesignTimeProperty><dwd:key xsi:type="xsd:string">1 TableInfoObjectType</dwd:key><dwd:value xsi:type="xsd:string">Table</dwd:value></dwd:DtsComponentDesignTimeProperty></dwd:DtsComponentDesignerPropertiesList></TaskHost></< FONT>DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</< FONT>DTS:Property><DTS:Property DTS:Name="ObjectName">{D1234F40-926A-4975-86F0-EFC71316CEB6}</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{59998195-8E74-4AB7-9A98-206CCB534639}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName"></< FONT>DTS:Property></< FONT>DTS:PackageVariable>
<DTS:PackageVariable><DTS:Property DTS:Name="PackageVariableValue" DTS:DataType="8"><PipelinePath xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:dwd="http://schemas.microsoft.com/DataWarehouse/Designer/1.0"><dwd:DestinationName>OLE DB Destination Input</dwd:DestinationName><dwd:SourceName>OLE DB Source Output</dwd:SourceName></PipelinePath></< FONT>DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</< FONT>DTS:Property><DTS:Property DTS:Name="ObjectName">{D1234F40-926A-4975-86F0-EFC71316CEB6}-69</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{CF4C1E95-3336-4ED4-A60C-F4219105D5DE}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName"></< FONT>DTS:Property></< FONT>DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</< FONT>DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</< FONT>DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</< FONT>DTS:Property><DTS:Property DTS:Name="Disabled">0</< FONT>DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</< FONT>DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</< FONT>DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</< FONT>DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</< FONT>DTS:Property><DTS:Property DTS:Name="LocaleID">1033</< FONT>DTS:Property><DTS:Property DTS:Name="TransactionOption">1</< FONT>DTS:Property><DTS:Property DTS:Name="DelayValidation">0</< FONT>DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</< FONT>DTS:Property><DTS:Property DTS:Name="FilterKind">1</< FONT>DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></< FONT>DTS:Property></< FONT>DTS:LoggingOptions>
<DTS:Executable DTS:ExecutableType="{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"><DTS:Property DTS:Name="ExecutionLocation">0</< FONT>DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></< FONT>DTS:Property><DTS:Property DTS:Name="TaskContact"></< FONT>DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</< FONT>DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</< FONT>DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</< FONT>DTS:Property><DTS:Property DTS:Name="Disabled">0</< FONT>DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</< FONT>DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</< FONT>DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</< FONT>DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</< FONT>DTS:Property><DTS:Property DTS:Name="LocaleID">-1</< FONT>DTS:Property><DTS:Property DTS:Name="TransactionOption">1</< FONT>DTS:Property><DTS:Property DTS:Name="DelayValidation">0</< FONT>DTS:Property>
<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</< FONT>DTS:Property><DTS:Property DTS:Name="FilterKind">1</< FONT>DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></< FONT>DTS:Property></< FONT>DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{D1234F40-926A-4975-86F0-EFC71316CEB6}</< FONT>DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</< FONT>DTS:Property><DTS:Property DTS:Name="CreationName">{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}</< FONT>DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</< FONT>DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">
<components>
<component id="1" name="OLE DB Source" componentClassID="{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}" description="OLE DB Source" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="7" pipelineVersion="0" contactInfo="OLE DB Source;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;7">
<properties>
<property id="2" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</< FONT>property>
<property id="3" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[dbo].[source]</< FONT>property>
<property id="4" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></< FONT>property>
<property id="5" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></< FONT>property>
<property id="6" name="SqlCommandVariable" dataType="System.String" state="default" isArray="false" description="The variable that contains the SQL command to be executed." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></< FONT>property>
<property id="7" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</< FONT>property>
<property id="8" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</< FONT>property>
<property id="9" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">0</< FONT>property>
<property id="15" name="ParameterMapping" dataType="System.String" state="default" isArray="false" description="The mappings between the parameters in the SQL command and variables." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></< FONT>property></< FONT>properties>
<connections>
<connection id="10" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{711EB6A5-1873-451F-AF3A-8453EC797462}"/></< FONT>connections>
<outputs>
<output id="11" name="OLE DB Source Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="false" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="47" name="id" description="" lineageId="47" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="46"/>
<outputColumn id="50" name="chr" description="" lineageId="50" precision="0" scale="0" length="1" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Conversion" errorRowDisposition="FailComponent" truncationRowDisposition="FailComponent" externalMetadataColumnId="49"/></< FONT>outputColumns><externalMetadataColumns isUsed="True">
<externalMetadataColumn id="46" name="id" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0"/>
<externalMetadataColumn id="49" name="chr" description="" precision="0" scale="0" length="1" dataType="str" codePage="1252"/></< FONT>externalMetadataColumns></< FONT>output>
<output id="12" name="OLE DB Source Error Output" description="" exclusionGroup="0" synchronousInputId="0" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="48" name="id" description="" lineageId="48" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="51" name="chr" description="" lineageId="51" precision="0" scale="0" length="1" dataType="str" codePage="1252" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="13" name="ErrorCode" description="" lineageId="13" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="14" name="ErrorColumn" description="" lineageId="14" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></< FONT>outputColumns><externalMetadataColumns isUsed="False"/></< FONT>output>
</< FONT>outputs>
</< FONT>component>
<component id="52" name="OLE DB Destination" componentClassID="{E2568105-9550-4F71-A638-B7FE42E66922}" description="OLE DB Destination" localeId="-1" usesDispositions="true" validateExternalMetadata="True" version="4" pipelineVersion="0" contactInfo="OLE DB Destination;Microsoft Corporation;Microsoft SqlServer v9; (C) 2005 Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4">
<properties>
<property id="53" name="CommandTimeout" dataType="System.Int32" state="default" isArray="false" description="The number of seconds before a command times out. A value of 0 indicates an infinite time-out." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</< FONT>property>
<property id="54" name="OpenRowset" dataType="System.String" state="default" isArray="false" description="Specifies the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">[dbo].[destination]</< FONT>property>
<property id="55" name="OpenRowsetVariable" dataType="System.String" state="default" isArray="false" description="Specifies the variable that contains the name of the database object used to open a rowset." typeConverter="" UITypeEditor="" containsID="false" expressionType="None"></< FONT>property>
<property id="56" name="SqlCommand" dataType="System.String" state="default" isArray="false" description="The SQL command to be executed." typeConverter="" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" containsID="false" expressionType="None"></< FONT>property>
<property id="57" name="DefaultCodePage" dataType="System.Int32" state="default" isArray="false" description="Specifies the column code page to use when code page information is unavailable from the data source." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">1252</< FONT>property>
<property id="58" name="AlwaysUseDefaultCodePage" dataType="System.Boolean" state="default" isArray="false" description="Forces the use of the DefaultCodePage property value when describing character data." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</< FONT>property>
<property id="59" name="AccessMode" dataType="System.Int32" state="default" isArray="false" description="Specifies the mode used to access the database." typeConverter="AccessMode" UITypeEditor="" containsID="false" expressionType="None">3</< FONT>property>
<property id="61" name="FastLoadKeepIdentity" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the values supplied for identity columns will be copied to the destination. If false, values for identity columns will be auto-generated at the destination. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">true</< FONT>property>
<property id="62" name="FastLoadKeepNulls" dataType="System.Boolean" state="default" isArray="false" description="Indicates whether the columns containing null will have null inserted in the destination. If false, columns containing null will have their default values inserted at the destinaton. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">false</< FONT>property>
<property id="63" name="FastLoadOptions" dataType="System.String" state="default" isArray="false" description="Specifies options to be used with fast load. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">TABLOCK,CHECK_CONSTRAINTS</< FONT>property>
<property id="64" name="FastLoadMaxInsertCommitSize" dataType="System.Int32" state="default" isArray="false" description="Specifies when commits are issued during data insertion. A value of 0 specifies that one commit will be issued at the end of data insertion. Applies only if fast load is turned on." typeConverter="" UITypeEditor="" containsID="false" expressionType="None">0</< FONT>property></< FONT>properties>
<connections>
<connection id="60" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{711EB6A5-1873-451F-AF3A-8453EC797462}"/></< FONT>connections>
<inputs>
<input id="65" name="OLE DB Destination Input" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="Insert" errorRowDisposition="FailComponent" truncationRowDisposition="NotUsed"><inputColumns>
<inputColumn id="89" name="" description="" lineageId="47" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="87"/>
<inputColumn id="90" name="" description="" lineageId="50" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="88"/>
</< FONT>inputColumns><externalMetadataColumns isUsed="True">
<externalMetadataColumn id="87" name="id" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0"/>
<externalMetadataColumn id="88" name="chr" description="" precision="0" scale="0" length="1" dataType="str" codePage="1252"/></< FONT>externalMetadataColumns></< FONT>input>
</< FONT>inputs>
<outputs>
<output id="66" name="OLE DB Destination Error Output" description="" exclusionGroup="1" synchronousInputId="65" deleteOutputOnPathDetached="false" hasSideEffects="false" dangling="false" isErrorOut="true" isSorted="false" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed"><outputColumns>
<outputColumn id="67" name="ErrorCode" description="" lineageId="67" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="1" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/>
<outputColumn id="68" name="ErrorColumn" description="" lineageId="68" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="2" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="0"/></< FONT>outputColumns><externalMetadataColumns isUsed="False"/></< FONT>output>
</< FONT>outputs>
</< FONT>component>
</< FONT>components>
<paths>
<path id="69" name="OLE DB Source Output" description="" startId="11" endId="65"/>
</< FONT>paths></< FONT>pipeline></< FONT>DTS:ObjectData></< FONT>DTS:Executable><DTS:Property DTS:Name="ObjectName">Package9</< FONT>DTS:Property><DTS:Property DTS:Name="DTSID">{CBA6818F-FBB0-4EE8-A1BF-AD304660C52A}</< FONT>DTS:Property><DTS:Property DTS:Name="Description"></< FONT>DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</< FONT>DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</< FONT>DTS:Property></< FONT>DTS:Executable>
Bug regarding identity columns?
I'm seeing some weird behavior regarding identity columns in MSSQL 2000.
In a specific client database we have this table:
ID Name SecLevDG Flags
-- -- -- --
1029528 xxx 0 0
1029529 xxx 0 0
1049676 xxx 0 0
While upgrading this database to a later version of our product, some schema changes are necessary. For this particular table, the changes are
In a specific client database we have this table:
ID Name SecLevDG Flags
-- -- -- --
1029528 xxx 0 0
1029529 xxx 0 0
1049676 xxx 0 0
While upgrading this database to a later version of our product, some schema changes are necessary. For this particular table, the changes are
alter table Authority drop constraint apkAuthorityId
alter table Authority drop column SecLevDg
alter table Authority add new_id integer identity
This code has worked perfectly for years, and even in this particular database, there are no error messages. However, the result isn't quite the expected:
ID Name Flags new_id
-- - -- --
1029528 xxx 0 16777220
1029529 xxx 0 16777220
1049676 xxx 0 16777220
Notice that the new column did NOT get unique values 1, 2, 3, etc... In other tests I manage to get different values, but still not the expected ones. Is this a bug in MSSQL 2000?
DBCC CHECKIDENT returns:
Checking identity information: current identity value '1', current column value '1'.
DBCC CHECKDB returns no errors before running the above statement. Afterwards it returns this (only relevant messages included):
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row.
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 1. Column 'new_id' was created NOT NULL, but is NULL in the row.
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:1145), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row.
[...]
CHECKDB found 0 allocation errors and 3 consistency errors in table 'Authority' (object ID 293576084).
Regards,
Oskar Berggren
WHen I'm creating a new identity column, I use this:
columnName int IDENTITY (1,1)
Perhaps it is balking because you aren't providing a seed value?
Or, if there are consistency errors, there's an issue with the table itself...
|||Also, although I can not find it now, I seem to remember reading that 2005 likes things to be specifically defined as NULL or NOT NULL.|||I encountered an issue like this in SQL Server 2005 RTM (I think SP1 fixed it). I was able to work around it by setting Max Degree of Parallelism (MAXDOP) to 1 for the transaction and then back to 0 afterwards.
My theory on this is that multiple processors are working to set the identity and the processors somehow end up with the same value. By having it done with a single processor it might run a bit slower but it resolved the issue for me.
I've never seen this issue in 2000, though. I assume you have the latest service packs?
Regards,
Jared
Bug regarding identity columns?
I'm seeing some weird behavior regarding identity columns in MSSQL 2000.
In a specific client database we have this table:
ID Name SecLevDG Flags
-- -- -- --
1029528 xxx 0 0
1029529 xxx 0 0
1049676 xxx 0 0
While upgrading this database to a later version of our product, some schema changes are necessary. For this particular table, the changes are
In a specific client database we have this table:
ID Name SecLevDG Flags
-- -- -- --
1029528 xxx 0 0
1029529 xxx 0 0
1049676 xxx 0 0
While upgrading this database to a later version of our product, some schema changes are necessary. For this particular table, the changes are
alter table Authority drop constraint apkAuthorityId
alter table Authority drop column SecLevDg
alter table Authority add new_id integer identity
This code has worked perfectly for years, and even in this particular database, there are no error messages. However, the result isn't quite the expected:
ID Name Flags new_id
-- - -- --
1029528 xxx 0 16777220
1029529 xxx 0 16777220
1049676 xxx 0 16777220
Notice that the new column did NOT get unique values 1, 2, 3, etc... In other tests I manage to get different values, but still not the expected ones. Is this a bug in MSSQL 2000?
DBCC CHECKIDENT returns:
Checking identity information: current identity value '1', current column value '1'.
DBCC CHECKDB returns no errors before running the above statement. Afterwards it returns this (only relevant messages included):
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row.
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:277), row ID 1. Column 'new_id' was created NOT NULL, but is NULL in the row.
Server: Msg 8970, Level 16, State 1, Line 1
Row error: Object ID 293576084, index ID 0, page ID (1:1145), row ID 0. Column 'new_id' was created NOT NULL, but is NULL in the row.
[...]
CHECKDB found 0 allocation errors and 3 consistency errors in table 'Authority' (object ID 293576084).
Regards,
Oskar Berggren
WHen I'm creating a new identity column, I use this:
columnName int IDENTITY (1,1)
Perhaps it is balking because you aren't providing a seed value?
Or, if there are consistency errors, there's an issue with the table itself...
|||Also, although I can not find it now, I seem to remember reading that 2005 likes things to be specifically defined as NULL or NOT NULL.|||I encountered an issue like this in SQL Server 2005 RTM (I think SP1 fixed it). I was able to work around it by setting Max Degree of Parallelism (MAXDOP) to 1 for the transaction and then back to 0 afterwards.
My theory on this is that multiple processors are working to set the identity and the processors somehow end up with the same value. By having it done with a single processor it might run a bit slower but it resolved the issue for me.
I've never seen this issue in 2000, though. I assume you have the latest service packs?
Regards,
Jared
Wednesday, March 7, 2012
Bug in Copy database routine
I copied some databases from a SQL 2000 server to a SQL 2005 server. All the tables in the databases with auto incrementing identity columns (not for replication) had the identity information removed from the table definition. I used the SQL Management Object method.This is a known issue in SMO that is expected to be fixed in Server Pack 1.
Peter|||So what is the recommended way to copy databases from SQL 2000 to SQL 2005?
Bug
I have a client that I wrote a script for them to change the clustered index
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.
Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/default...09&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>
|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/default...09&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> index
new[vbcol=seagreen]
> query
> to
> went
> able
of[vbcol=seagreen]
> my
others[vbcol=seagreen]
> it
found[vbcol=seagreen]
> SP3a.
> on
> problem
and[vbcol=seagreen]
90[vbcol=seagreen]
ahve[vbcol=seagreen]
all
>
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.
Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/default...09&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>
|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/default...09&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> index
new[vbcol=seagreen]
> query
> to
> went
> able
of[vbcol=seagreen]
> my
others[vbcol=seagreen]
> it
found[vbcol=seagreen]
> SP3a.
> on
> problem
and[vbcol=seagreen]
90[vbcol=seagreen]
ahve[vbcol=seagreen]
all
>
Bug
I have a client that I wrote a script for them to change the clustered index
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/defaul...509&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/defaul...509&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> index
new[vbcol=seagreen]
> query
> to
> went
> able
of[vbcol=seagreen]
> my
others[vbcol=seagreen]
> it
found[vbcol=seagreen]
> SP3a.
> on
> problem
and[vbcol=seagreen]
90[vbcol=seagreen]
ahve[vbcol=seagreen]
all[vbcol=seagreen]
>
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/defaul...509&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/defaul...509&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> index
new[vbcol=seagreen]
> query
> to
> went
> able
of[vbcol=seagreen]
> my
others[vbcol=seagreen]
> it
found[vbcol=seagreen]
> SP3a.
> on
> problem
and[vbcol=seagreen]
90[vbcol=seagreen]
ahve[vbcol=seagreen]
all[vbcol=seagreen]
>
Bug
I have a client that I wrote a script for them to change the clustered index
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> >
> > I have a client that I wrote a script for them to change the clustered
> index
> > from the PK field (which is an identity) to another field. The new
> > clustering field happens to contain some nulls (which is not supposed to
> > happen but..). Anyway, they ran the script and the new table with the
new
> > clustered index was created without apparent errors. Then they ran a
> query
> > that looks like this:
> >
> > SELECT *
> > FROM myTable
> > WHERE clusterField is not null and someOtherField is null
> >
> > The return set returned records where the clusterField is null. I tried
> to
> > duplicate this on one of my servers and didn't have the problem. So I
> went
> > to their office and ran it and the problem persisted. After a couple of
> > hours of testing I found that the problem only existed above a certain
> > threshold of records in the table (about 1.1 million). After not being
> able
> > to find a solution I went home to do some more troubleshooting on some
of
> my
> > servers and found that on one server it had the problem but on two
others
> it
> > didn't have the problem. the one server that had the problem was using
> > service pack 3 while the two that worked were on SP3a. I thought I
found
> > the problem and told my client to make sure that their servers were on
> SP3a.
> > They had on that was on SP3 and one on 3a. They tested the script again
> on
> > the server with 3a and still had the problem. I don't know where to go
> > next. I'm going to post the exact script that I used to exhibit the
> problem
> > in hopes that someone can find a solution or verify that this is a bug
and
> > under what circumstances it occurs.
> >
> >
> >
> > CREATE TABLE [HIST_NEW] (
> > [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> > [LOANOID] [bigint] NULL ,
> > [USERFILEOID] [bigint] NULL ,
> > [LOANPURGEOID] [bigint] NULL ,
> > [SystemDate] [varchar] (8) DEFAULT (''),
> > [SystemDateSQL] [datetime] default getdate(),
> > [SystemTime] [varchar] (6) DEFAULT (''),
> > [SystemTImeSQL] [datetime] default getdate(),
> > [TransactionID] [varchar] (5) DEFAULT (''),
> > [ActionCode] [varchar] (1) DEFAULT (''),
> > [BatchDate] [varchar] (8) DEFAULT (''),
> > [BatchDateSQL] [datetime] default getdate(),
> > [CompanyAgent] [varchar] (20) DEFAULT (''),
> > [DocumentID] [varchar] (20) DEFAULT (''),
> > [EffDate] [varchar] (8) DEFAULT (''),
> > [EffDateSQL] [datetime] default getdate(),
> > [Entry] [int] NULL ,
> > [ExpDate] [varchar] (8) DEFAULT (''),
> > [ExpDateSQL] [datetime] default getdate(),
> > [HistoryNumber] [int] NULL ,
> > [LNUM] [varchar] (18) DEFAULT (''),
> > [MailDate] [varchar] (8) DEFAULT (''),
> > [MailDateSQL] [datetime] default getdate(),
> > [ModCount] [smallint] NULL ,
> > [Policy] [varchar] (15) DEFAULT (''),
> > [UserID] [varchar] (10) DEFAULT (''),
> > [lenderNumber] [char] (4) DEFAULT (''),
> > CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> > ) ON [PRIMARY]
> > GO
> >
> > --create a new clustered index on the LOANOID column
> > create clustered index cix_hist on hist_new (LOANOID) with fillfactor =90
> >
> >
> > declare @.counter
> > set @.counter = 1
> >
> > while @.counter < 3500000
> > BEGIN
> > INSERT HIST_NEW (LOANOID, LNUM)
> > SELECT case when @.counter %6 = 0 then null else @.counter end,
> > case when @.counter %500 = 0 then null else @.counter end,
> >
> > SET @.counter = @.counter + 1
> > END
> >
> >
> > --This query should not return nulls in the LOANOID field but is does in
> > some cases.
> > select lnum, LOANOID
> > from HIST_new
> > where LOANOID is not null
> > and lnum is null
> >
> >
> >
> > I appreciate any insight that can be provided on this issue. Also, I
ahve
> > been running this on SQL Server Standard Edition on Win 2K with SP4 in
all
> > cases and on various types of hardware.
> >
> >
>
from the PK field (which is an identity) to another field. The new
clustering field happens to contain some nulls (which is not supposed to
happen but..). Anyway, they ran the script and the new table with the new
clustered index was created without apparent errors. Then they ran a query
that looks like this:
SELECT *
FROM myTable
WHERE clusterField is not null and someOtherField is null
The return set returned records where the clusterField is null. I tried to
duplicate this on one of my servers and didn't have the problem. So I went
to their office and ran it and the problem persisted. After a couple of
hours of testing I found that the problem only existed above a certain
threshold of records in the table (about 1.1 million). After not being able
to find a solution I went home to do some more troubleshooting on some of my
servers and found that on one server it had the problem but on two others it
didn't have the problem. the one server that had the problem was using
service pack 3 while the two that worked were on SP3a. I thought I found
the problem and told my client to make sure that their servers were on SP3a.
They had on that was on SP3 and one on 3a. They tested the script again on
the server with 3a and still had the problem. I don't know where to go
next. I'm going to post the exact script that I used to exhibit the problem
in hopes that someone can find a solution or verify that this is a bug and
under what circumstances it occurs.
CREATE TABLE [HIST_NEW] (
[HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
[LOANOID] [bigint] NULL ,
[USERFILEOID] [bigint] NULL ,
[LOANPURGEOID] [bigint] NULL ,
[SystemDate] [varchar] (8) DEFAULT (''),
[SystemDateSQL] [datetime] default getdate(),
[SystemTime] [varchar] (6) DEFAULT (''),
[SystemTImeSQL] [datetime] default getdate(),
[TransactionID] [varchar] (5) DEFAULT (''),
[ActionCode] [varchar] (1) DEFAULT (''),
[BatchDate] [varchar] (8) DEFAULT (''),
[BatchDateSQL] [datetime] default getdate(),
[CompanyAgent] [varchar] (20) DEFAULT (''),
[DocumentID] [varchar] (20) DEFAULT (''),
[EffDate] [varchar] (8) DEFAULT (''),
[EffDateSQL] [datetime] default getdate(),
[Entry] [int] NULL ,
[ExpDate] [varchar] (8) DEFAULT (''),
[ExpDateSQL] [datetime] default getdate(),
[HistoryNumber] [int] NULL ,
[LNUM] [varchar] (18) DEFAULT (''),
[MailDate] [varchar] (8) DEFAULT (''),
[MailDateSQL] [datetime] default getdate(),
[ModCount] [smallint] NULL ,
[Policy] [varchar] (15) DEFAULT (''),
[UserID] [varchar] (10) DEFAULT (''),
[lenderNumber] [char] (4) DEFAULT (''),
CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
) ON [PRIMARY]
GO
--create a new clustered index on the LOANOID column
create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
declare @.counter
set @.counter = 1
while @.counter < 3500000
BEGIN
INSERT HIST_NEW (LOANOID, LNUM)
SELECT case when @.counter %6 = 0 then null else @.counter end,
case when @.counter %500 = 0 then null else @.counter end,
SET @.counter = @.counter + 1
END
--This query should not return nulls in the LOANOID field but is does in
some cases.
select lnum, LOANOID
from HIST_new
where LOANOID is not null
and lnum is null
I appreciate any insight that can be provided on this issue. Also, I ahve
been running this on SQL Server Standard Edition on Win 2K with SP4 in all
cases and on various types of hardware.Is this on a multi-processor computer? If so, perhaps this is the bug
documented in 814509:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
set the max degree of parallelism to 1.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> I have a client that I wrote a script for them to change the clustered
index
> from the PK field (which is an identity) to another field. The new
> clustering field happens to contain some nulls (which is not supposed to
> happen but..). Anyway, they ran the script and the new table with the new
> clustered index was created without apparent errors. Then they ran a
query
> that looks like this:
> SELECT *
> FROM myTable
> WHERE clusterField is not null and someOtherField is null
> The return set returned records where the clusterField is null. I tried
to
> duplicate this on one of my servers and didn't have the problem. So I
went
> to their office and ran it and the problem persisted. After a couple of
> hours of testing I found that the problem only existed above a certain
> threshold of records in the table (about 1.1 million). After not being
able
> to find a solution I went home to do some more troubleshooting on some of
my
> servers and found that on one server it had the problem but on two others
it
> didn't have the problem. the one server that had the problem was using
> service pack 3 while the two that worked were on SP3a. I thought I found
> the problem and told my client to make sure that their servers were on
SP3a.
> They had on that was on SP3 and one on 3a. They tested the script again
on
> the server with 3a and still had the problem. I don't know where to go
> next. I'm going to post the exact script that I used to exhibit the
problem
> in hopes that someone can find a solution or verify that this is a bug and
> under what circumstances it occurs.
>
> CREATE TABLE [HIST_NEW] (
> [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> [LOANOID] [bigint] NULL ,
> [USERFILEOID] [bigint] NULL ,
> [LOANPURGEOID] [bigint] NULL ,
> [SystemDate] [varchar] (8) DEFAULT (''),
> [SystemDateSQL] [datetime] default getdate(),
> [SystemTime] [varchar] (6) DEFAULT (''),
> [SystemTImeSQL] [datetime] default getdate(),
> [TransactionID] [varchar] (5) DEFAULT (''),
> [ActionCode] [varchar] (1) DEFAULT (''),
> [BatchDate] [varchar] (8) DEFAULT (''),
> [BatchDateSQL] [datetime] default getdate(),
> [CompanyAgent] [varchar] (20) DEFAULT (''),
> [DocumentID] [varchar] (20) DEFAULT (''),
> [EffDate] [varchar] (8) DEFAULT (''),
> [EffDateSQL] [datetime] default getdate(),
> [Entry] [int] NULL ,
> [ExpDate] [varchar] (8) DEFAULT (''),
> [ExpDateSQL] [datetime] default getdate(),
> [HistoryNumber] [int] NULL ,
> [LNUM] [varchar] (18) DEFAULT (''),
> [MailDate] [varchar] (8) DEFAULT (''),
> [MailDateSQL] [datetime] default getdate(),
> [ModCount] [smallint] NULL ,
> [Policy] [varchar] (15) DEFAULT (''),
> [UserID] [varchar] (10) DEFAULT (''),
> [lenderNumber] [char] (4) DEFAULT (''),
> CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> ) ON [PRIMARY]
> GO
> --create a new clustered index on the LOANOID column
> create clustered index cix_hist on hist_new (LOANOID) with fillfactor = 90
>
> declare @.counter
> set @.counter = 1
> while @.counter < 3500000
> BEGIN
> INSERT HIST_NEW (LOANOID, LNUM)
> SELECT case when @.counter %6 = 0 then null else @.counter end,
> case when @.counter %500 = 0 then null else @.counter end,
> SET @.counter = @.counter + 1
> END
>
> --This query should not return nulls in the LOANOID field but is does in
> some cases.
> select lnum, LOANOID
> from HIST_new
> where LOANOID is not null
> and lnum is null
>
> I appreciate any insight that can be provided on this issue. Also, I ahve
> been running this on SQL Server Standard Edition on Win 2K with SP4 in all
> cases and on various types of hardware.
>|||Thanks Dan, that appears to be the problem. I've advised my client to turn
off parallelism until a service pack with that fix becomes available.
--Buddy
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23%237dZTBKEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Is this on a multi-processor computer? If so, perhaps this is the bug
> documented in 814509:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;814509&Product=sql
> A workaround not mentioned in the article is to specify a MAXDOP 1 hint or
> set the max degree of parallelism to 1.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Buddy Ackerman" <buddy_nospam@.buddyackerman.com> wrote in message
> news:eTqRkj7JEHA.1892@.TK2MSFTNGP09.phx.gbl...
> >
> > I have a client that I wrote a script for them to change the clustered
> index
> > from the PK field (which is an identity) to another field. The new
> > clustering field happens to contain some nulls (which is not supposed to
> > happen but..). Anyway, they ran the script and the new table with the
new
> > clustered index was created without apparent errors. Then they ran a
> query
> > that looks like this:
> >
> > SELECT *
> > FROM myTable
> > WHERE clusterField is not null and someOtherField is null
> >
> > The return set returned records where the clusterField is null. I tried
> to
> > duplicate this on one of my servers and didn't have the problem. So I
> went
> > to their office and ran it and the problem persisted. After a couple of
> > hours of testing I found that the problem only existed above a certain
> > threshold of records in the table (about 1.1 million). After not being
> able
> > to find a solution I went home to do some more troubleshooting on some
of
> my
> > servers and found that on one server it had the problem but on two
others
> it
> > didn't have the problem. the one server that had the problem was using
> > service pack 3 while the two that worked were on SP3a. I thought I
found
> > the problem and told my client to make sure that their servers were on
> SP3a.
> > They had on that was on SP3 and one on 3a. They tested the script again
> on
> > the server with 3a and still had the problem. I don't know where to go
> > next. I'm going to post the exact script that I used to exhibit the
> problem
> > in hopes that someone can find a solution or verify that this is a bug
and
> > under what circumstances it occurs.
> >
> >
> >
> > CREATE TABLE [HIST_NEW] (
> > [HISTOID] [bigint] IDENTITY(1,1) NOT NULL ,
> > [LOANOID] [bigint] NULL ,
> > [USERFILEOID] [bigint] NULL ,
> > [LOANPURGEOID] [bigint] NULL ,
> > [SystemDate] [varchar] (8) DEFAULT (''),
> > [SystemDateSQL] [datetime] default getdate(),
> > [SystemTime] [varchar] (6) DEFAULT (''),
> > [SystemTImeSQL] [datetime] default getdate(),
> > [TransactionID] [varchar] (5) DEFAULT (''),
> > [ActionCode] [varchar] (1) DEFAULT (''),
> > [BatchDate] [varchar] (8) DEFAULT (''),
> > [BatchDateSQL] [datetime] default getdate(),
> > [CompanyAgent] [varchar] (20) DEFAULT (''),
> > [DocumentID] [varchar] (20) DEFAULT (''),
> > [EffDate] [varchar] (8) DEFAULT (''),
> > [EffDateSQL] [datetime] default getdate(),
> > [Entry] [int] NULL ,
> > [ExpDate] [varchar] (8) DEFAULT (''),
> > [ExpDateSQL] [datetime] default getdate(),
> > [HistoryNumber] [int] NULL ,
> > [LNUM] [varchar] (18) DEFAULT (''),
> > [MailDate] [varchar] (8) DEFAULT (''),
> > [MailDateSQL] [datetime] default getdate(),
> > [ModCount] [smallint] NULL ,
> > [Policy] [varchar] (15) DEFAULT (''),
> > [UserID] [varchar] (10) DEFAULT (''),
> > [lenderNumber] [char] (4) DEFAULT (''),
> > CONSTRAINT [PK__HIST_] PRIMARY KEY NONCLUSTERED (HISTOID)
> > ) ON [PRIMARY]
> > GO
> >
> > --create a new clustered index on the LOANOID column
> > create clustered index cix_hist on hist_new (LOANOID) with fillfactor =90
> >
> >
> > declare @.counter
> > set @.counter = 1
> >
> > while @.counter < 3500000
> > BEGIN
> > INSERT HIST_NEW (LOANOID, LNUM)
> > SELECT case when @.counter %6 = 0 then null else @.counter end,
> > case when @.counter %500 = 0 then null else @.counter end,
> >
> > SET @.counter = @.counter + 1
> > END
> >
> >
> > --This query should not return nulls in the LOANOID field but is does in
> > some cases.
> > select lnum, LOANOID
> > from HIST_new
> > where LOANOID is not null
> > and lnum is null
> >
> >
> >
> > I appreciate any insight that can be provided on this issue. Also, I
ahve
> > been running this on SQL Server Standard Edition on Win 2K with SP4 in
all
> > cases and on various types of hardware.
> >
> >
>
Subscribe to:
Posts (Atom)