Boot.ini file: insert

Showing posts with label insert. Show all posts

Thursday, March 22, 2012

Bug?: left outer join in combination with a view

Hi,
there is a small test case (SQL Server 2000 - 8.00.760):
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
If you perform this select, the isnull_jn col is always 'J',
although it should be 'N' in the second row:
select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn isnull_jn
-- -- --
1 J J
2 NULL J <<<=== error, should be N!
If you redefine the query to:
select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
from test1 left outer join testview on test1.nr = testview.nr
nr jn case_jn
-- -- --
1 J J
2 NULL N <<== ok!
everything works fine.
Any suggestions?
--
markusThat looks like a bug with ISNULL; I switched it to use COALESCE instead and
it fixed the problem...
Hopefully someone reading this knows how to submit a SQL bug report to MS?
"mark" <wimark@.smsNOSAPM.at> wrote in message
news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> Hi,
> there is a small test case (SQL Server 2000 - 8.00.760):
> -- create a small table
> create table test1 (nr int null)
> insert into test1 values (1)
> insert into test1 values (2)
> -- now create a view
> create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> If you perform this select, the isnull_jn col is always 'J',
> although it should be 'N' in the second row:
> select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn isnull_jn
> -- -- --
> 1 J J
> 2 NULL J <<<=== error, should be N!
>
> If you redefine the query to:
> select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> from test1 left outer join testview on test1.nr = testview.nr
> nr jn case_jn
> -- -- --
> 1 J J
> 2 NULL N <<== ok!
> everything works fine.
> Any suggestions?
> --
> markus
>|||ISNULL is kind of notoriously NOT the same thing as COALESCE and CASE, so I
was skeptical that this was really a bug. But below is a repro which uses
ISNULL with a view and an "inline view" or derived table.
Now despite any differences between INSULL and COALESCE, these 2 queries
should return the same data. They do not.
-- create a small table
create table test1 (nr int null)
insert into test1 values (1)
insert into test1 values (2)
-- now create a view
create view testview as select nr, 'J' jn from test1 where nr = 1
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join (select nr, 'J' jn from test1 where nr = 1) testview
on test1.nr = testview.nr
where test1.nr=2
select
test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
from test1
left outer join testview
on test1.nr = testview.nr
where test1.nr=2
--
nr jn isnull_jn
2 N
(1 row(s) affected)
nr jn isnull_jn
2 J
(1 row(s) affected)
David
Should definitely return the same data
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> wrote in message
news:ebxMLqvJEHA.3120@.TK2MSFTNGP11.phx.gbl...
> That looks like a bug with ISNULL; I switched it to use COALESCE instead
and
> it fixed the problem...
> Hopefully someone reading this knows how to submit a SQL bug report to MS?
>
> "mark" <wimark@.smsNOSAPM.at> wrote in message
> news:#EJS#RvJEHA.620@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > there is a small test case (SQL Server 2000 - 8.00.760):
> >
> > -- create a small table
> > create table test1 (nr int null)
> > insert into test1 values (1)
> > insert into test1 values (2)
> > -- now create a view
> > create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> > If you perform this select, the isnull_jn col is always 'J',
> > although it should be 'N' in the second row:
> > select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn isnull_jn
> > -- -- --
> > 1 J J
> > 2 NULL J <<<=== error, should be N!
> >
> >
> > If you redefine the query to:
> > select test1.nr, jn, case when jn is null then 'N' else jn end as
> 'case_jn'
> > from test1 left outer join testview on test1.nr = testview.nr
> > nr jn case_jn
> > -- -- --
> > 1 J J
> > 2 NULL N <<== ok!
> > everything works fine.
> >
> > Any suggestions?
> > --
> > markus
> >
> >
>|||On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
>-- create a small table
>create table test1 (nr int null)
>insert into test1 values (1)
>insert into test1 values (2)
>-- now create a view
>create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
>If you perform this select, the isnull_jn col is always 'J',
>although it should be 'N' in the second row:
>select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn isnull_jn
>-- -- --
>1 J J
>2 NULL J <<<=== error, should be N!
>
>If you redefine the query to:
>select test1.nr, jn, case when jn is null then 'N' else jn end as 'case_jn'
>from test1 left outer join testview on test1.nr = testview.nr
>nr jn case_jn
>-- -- --
>1 J J
>2 NULL N <<== ok!
>everything works fine.
Hi Markus,
Nice one :-)
Looks like a bug to me. You might want to report it to MS.
>Any suggestions?
Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
COALESCE more portable. Plus, the support for ISNULL might be
discontinued in a future version of SQL Server.
2. COALESCE is more versatile. With COALESCE, you can get the first
non-NULL of as many arguments as you want. With ISNULL, the same can
only be achieved by nesting.
3. ISNULL apparently produces erroneous results when used in a left
outer join in combination with a view. A guy named Markus recently
posted about this in a newsgroup :-))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo,
COALESCE has bug problems of it's own:
http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
If you want to play safe, use CASE for any non-trivial scenarios where you
would use ISNULL or COALESCE.
--
Jacco Schalkwijk
SQL Server MVP
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:e2sa80500of69g9q8e4dmijfcqjl9toenc@.4ax.com...
> On Tue, 20 Apr 2004 18:32:32 +0200, mark wrote:
> >-- create a small table
> >create table test1 (nr int null)
> >insert into test1 values (1)
> >insert into test1 values (2)
> >-- now create a view
> >create view testview (nr, jn ) as select nr, 'J' from test1 where nr = 1
> >
> >If you perform this select, the isnull_jn col is always 'J',
> >although it should be 'N' in the second row:
> >select test1.nr, jn , isnull( jn, 'N') as 'isnull_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn isnull_jn
> >-- -- --
> >1 J J
> >2 NULL J <<<=== error, should be N!
> >
> >
> >If you redefine the query to:
> >select test1.nr, jn, case when jn is null then 'N' else jn end as
'case_jn'
> >from test1 left outer join testview on test1.nr = testview.nr
> >nr jn case_jn
> >-- -- --
> >1 J J
> >2 NULL N <<== ok!
> >everything works fine.
> Hi Markus,
> Nice one :-)
> Looks like a bug to me. You might want to report it to MS.
> >
> >Any suggestions?
> Yes. Don't use ISNULL. Use COALESCE instead. Three reasons:
> 1. COALESCE is ANSI-standard, ISNULL is proprietary. That makes
> COALESCE more portable. Plus, the support for ISNULL might be
> discontinued in a future version of SQL Server.
> 2. COALESCE is more versatile. With COALESCE, you can get the first
> non-NULL of as many arguments as you want. With ISNULL, the same can
> only be achieved by nesting.
> 3. ISNULL apparently produces erroneous results when used in a left
> outer join in combination with a view. A guy named Markus recently
> posted about this in a newsgroup :-))
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 21 Apr 2004 15:26:17 +0100, Jacco Schalkwijk wrote:
>Hi Hugo,
>COALESCE has bug problems of it's own:
>http://support.microsoft.com/default.aspx?scid=kb;en-us;317527
>If you want to play safe, use CASE for any non-trivial scenarios where you
>would use ISNULL or COALESCE.
When I executed the repro query in that article, I didn't get the
error, but the (expected) value of 1. Apparantly, this bug is fixed in
Service Pack 3a.
But thanks for the pointer, anyway - it just shows that it pays to pay
attention to the results of your query. A complicated product like SQL
Server (or any other RDBMS) can never be completely bug-free.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Bug?: left outer join in combination with a view

Monday, March 19, 2012

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.".

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

USE tmp

CREATE TABLE source (id INT IDENTITY(1,1) PRIMARY key, chr CHAR(1))

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))

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</DTS:Property><DTS:Property DTS:Name="VersionComments"></DTS:Property><DTS:Property DTS:Name="CreatorName">CT\jtbx</DTS:Property><DTS:Property DTS:Name="CreatorComputerName">2UA63600NJ</DTS:Property><DTS:Property DTS:Name="CreationDate" DTS:DataType="7">3/29/2007 3:25:55 PM</DTS:Property><DTS:Property DTS:Name="PackageType">5</DTS:Property><DTS:Property DTS:Name="ProtectionLevel">1</DTS:Property><DTS:Property DTS:Name="MaxConcurrentExecutables">-1</DTS:Property><DTS:Property DTS:Name="PackagePriorityClass">0</DTS:Property><DTS:Property DTS:Name="VersionMajor">1</DTS:Property><DTS:Property DTS:Name="VersionMinor">0</DTS:Property><DTS:Property DTS:Name="VersionBuild">1</DTS:Property><DTS:Property DTS:Name="VersionGUID">{2B4A9733-B18D-47EE-A743-7219941EEEB7}</DTS:Property><DTS:Property DTS:Name="EnableConfig">0</DTS:Property><DTS:Property DTS:Name="CheckpointFileName"></DTS:Property><DTS:Property DTS:Name="SaveCheckpoints">0</DTS:Property><DTS:Property DTS:Name="CheckpointUsage">0</DTS:Property><DTS:Property DTS:Name="SuppressConfigurationWarnings">0</DTS:Property>

<DTS:ConnectionManager><DTS:Property DTS:Name="DelayValidation">0</DTS:Property><DTS:Property DTS:Name="ObjectName">LocalHost</DTS:Property><DTS:Property DTS:Name="DTSID">{711EB6A5-1873-451F-AF3A-8453EC797462}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">OLEDB</DTS:Property><DTS:ObjectData><DTS:ConnectionManager><DTS:Property DTS:Name="Retain">0</DTS:Property><DTS:Property DTS:Name="ConnectionString">Data Source=.;Initial Catalog=tmp;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</DTS:Property></DTS:ConnectionManager></DTS:ObjectData></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>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</dds></dwd:Layout></dwd:DtsControlFlowDiagram></Package></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{CBA6818F-FBB0-4EE8-A1BF-AD304660C52A}</DTS:Property><DTS:Property DTS:Name="DTSID">{F740740D-718C-47DB-9D17-E2071E8A5DF3}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></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>

</mouseicon>

</diagram>

</layoutmanager>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</control>

</ddsxmlobj>

</layoutobject>

</ddscontrol>

</ddsxmlobj>

</control>

</ddsxmlobj>

</layoutobject>

</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></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{D1234F40-926A-4975-86F0-EFC71316CEB6}</DTS:Property><DTS:Property DTS:Name="DTSID">{59998195-8E74-4AB7-9A98-206CCB534639}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></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></DTS:Property><DTS:Property DTS:Name="Namespace">dts-designer-1.0</DTS:Property><DTS:Property DTS:Name="ObjectName">{D1234F40-926A-4975-86F0-EFC71316CEB6}-69</DTS:Property><DTS:Property DTS:Name="DTSID">{CF4C1E95-3336-4ED4-A60C-F4219105D5DE}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:PackageVariable><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">1033</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:Executable DTS:ExecutableType="{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}"><DTS:Property DTS:Name="ExecutionLocation">0</DTS:Property><DTS:Property DTS:Name="ExecutionAddress"></DTS:Property><DTS:Property DTS:Name="TaskContact"></DTS:Property><DTS:Property DTS:Name="ForceExecValue">0</DTS:Property><DTS:Property DTS:Name="ExecValue" DTS:DataType="3">0</DTS:Property><DTS:Property DTS:Name="ForceExecutionResult">-1</DTS:Property><DTS:Property DTS:Name="Disabled">0</DTS:Property><DTS:Property DTS:Name="FailPackageOnFailure">0</DTS:Property><DTS:Property DTS:Name="FailParentOnFailure">0</DTS:Property><DTS:Property DTS:Name="MaxErrorCount">1</DTS:Property><DTS:Property DTS:Name="ISOLevel">1048576</DTS:Property><DTS:Property DTS:Name="LocaleID">-1</DTS:Property><DTS:Property DTS:Name="TransactionOption">1</DTS:Property><DTS:Property DTS:Name="DelayValidation">0</DTS:Property>

<DTS:LoggingOptions><DTS:Property DTS:Name="LoggingMode">0</DTS:Property><DTS:Property DTS:Name="FilterKind">1</DTS:Property><DTS:Property DTS:Name="EventFilter" DTS:DataType="8"></DTS:Property></DTS:LoggingOptions><DTS:Property DTS:Name="ObjectName">Data Flow Task</DTS:Property><DTS:Property DTS:Name="DTSID">{D1234F40-926A-4975-86F0-EFC71316CEB6}</DTS:Property><DTS:Property DTS:Name="Description">Data Flow Task</DTS:Property><DTS:Property DTS:Name="CreationName">{C3BF9DC1-4715-4694-936F-D3CFDA9E42C5}</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property><DTS:ObjectData><pipeline id="0" name="pipelineXml" description="pipelineXml" defaultBufferMaxRows="10000" engineThreads="5" defaultBufferSize="10485760" BLOBTempStoragePath="" bufferTempStoragePath="" runInOptimizedMode="true">

<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]</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</property>

</outputs>

</component>

<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]</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</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</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</property>

</inputColumns><externalMetadataColumns isUsed="True">

</inputs>

</outputs>

</component>

</components>

<paths>

</paths></pipeline></DTS:ObjectData></DTS:Executable><DTS:Property DTS:Name="ObjectName">Package9</DTS:Property><DTS:Property DTS:Name="DTSID">{CBA6818F-FBB0-4EE8-A1BF-AD304660C52A}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName">MSDTS.Package.1</DTS:Property><DTS:Property DTS:Name="DisableEventHandlers">0</DTS:Property></DTS:Executable>

Thursday, March 8, 2012

bug in exception handling (begin try... begin catch...)?

begin try
-- insert ...
select * from nonexistingtableorfunction;
end try
begin catch
print 'catch me if you can'; -- line will not be printed
end catch;
begin try
exec doNotExitingprocedure;
end try
begin catch
print 'this line is printed :-)';
end catch;
Why this different behaviour?
If you wrap 1. example in anthother proc with exception handling
(begin try... begin catch...),
the wrapping proc will catch the error.
The above behaviour is not what I would expect compared with other
languages such as Java, C#, PL/SQL...
Greetings
B. D. JensenI think this is one of numerous reasons I have noticed a distinct lack of
uptake on the new TRY mechanism. Until it really does catch errors I won't
use it, nor advise my clients to.
"B D Jensen" <bjorn.d.jensen@.gmail.com> wrote in message
news:1191585968.457460.319780@.o80g2000hse.googlegroups.com...
> begin try
> -- insert ...
> select * from nonexistingtableorfunction;
> end try
> begin catch
> print 'catch me if you can'; -- line will not be printed
> end catch;
>
> begin try
> exec doNotExitingprocedure;
> end try
> begin catch
> print 'this line is printed :-)';
> end catch;
>
> Why this different behaviour?
> If you wrap 1. example in anthother proc with exception handling
> (begin try... begin catch...),
> the wrapping proc will catch the error.
>
> The above behaviour is not what I would expect compared with other
> languages such as Java, C#, PL/SQL...
> Greetings
> B. D. Jensen
>|||B D,
I believe that what is happening here is that in your first example, the
batch is failing with a syntax error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'nonexistingtableorfunction'.
This means that it failed at compile time and never executed. Since it
never executed, the try / catch had no chance to do anything.
In your second example, the called routine got the same error as above and
never executed.
However, in this case the calling routine is running just fine and the try /
catch sees that the called routine got an error.
At least, that is what it looks like to me.
RLF
"B D Jensen" <bjorn.d.jensen@.gmail.com> wrote in message
news:1191585968.457460.319780@.o80g2000hse.googlegroups.com...
> begin try
> -- insert ...
> select * from nonexistingtableorfunction;
> end try
> begin catch
> print 'catch me if you can'; -- line will not be printed
> end catch;
>
> begin try
> exec doNotExitingprocedure;
> end try
> begin catch
> print 'this line is printed :-)';
> end catch;
>
> Why this different behaviour?
> If you wrap 1. example in anthother proc with exception handling
> (begin try... begin catch...),
> the wrapping proc will catch the error.
>
> The above behaviour is not what I would expect compared with other
> languages such as Java, C#, PL/SQL...
> Greetings
> B. D. Jensen
>|||Take a look at the BOL here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/3a5711f5-4f6d-49e8-b1eb-53645181bc40.htm
In your first case, the missing object caused the batch to terminate, and
TRY/CATCH won't help if the batch is terminated. In your second case, the
batch is not terminated and TRY/CATCH works.
Yuu can place a PRINT statement at the end of each batch to see whether the
batch is terminated before it reaches the end.
Linchi
"B D Jensen" wrote:
> begin try
> -- insert ...
> select * from nonexistingtableorfunction;
> end try
> begin catch
> print 'catch me if you can'; -- line will not be printed
> end catch;
>
> begin try
> exec doNotExitingprocedure;
> end try
> begin catch
> print 'this line is printed :-)';
> end catch;
>
> Why this different behaviour?
> If you wrap 1. example in anthother proc with exception handling
> (begin try... begin catch...),
> the wrapping proc will catch the error.
>
> The above behaviour is not what I would expect compared with other
> languages such as Java, C#, PL/SQL...
> Greetings
> B. D. Jensen
>

Wednesday, March 7, 2012

bug - insert miliseconds

create table #t (col1 int identity,col2 datetime)
insert into #t (col2)
SELECT '2006-03-21 18:59:50.985'
select * from #tHi Dave,

Thats curious, in SQL 2000 this script:

create table #t (col1 int identity,col2 datetime)
insert into #t (col2)
SELECT '2006-03-21 18:59:50.985'
select * from #t

Results in:
2006-03-21 18:59:50.987

If I look up "datetime and smalldatetime" in Books Online it says:
"Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table. (the table below not included here)"

Maybe this helps you somehow.

--
-Dick Christoph
"Dave" <daveg.01@.gmail.com> wrote in message
news:1143152560.420148.112820@.e56g2000cwe.googlegr oups.com...
>I noticed that my tables are not storing the milisecond values that I
> am inserting. Is this a bug? If not, can someone please explain what
> is going or or point me to a resource so I can research it further.
>
> create table #t (col1 int identity,col2 datetime)
> insert into #t (col2)
> SELECT '2006-03-21 18:59:50.985'
> select * from #t|||Yes, it does. I should have looked this up. It rounds to the nearest
..003 second.

Thanks!

bug - insert miliseconds

I noticed that my tables are not storing the milisecond values that I
am inserting. Is this a bug? If not, can someone please explain what
is going or or point me to a resource so I can research it further.
create table #t (col1 int identity,col2 datetime)
insert into #t (col2)
SELECT '2006-03-21 18:59:50.985'
select * from #tHi Dave,
Thats curious, in SQL 2000 this script:
create table #t (col1 int identity,col2 datetime)
insert into #t (col2)
SELECT '2006-03-21 18:59:50.985'
select * from #t
Results in:
2006-03-21 18:59:50.987
If I look up "datetime and smalldatetime" in Books Online it says:
"Date and time data from January 1, 1753 through December 31, 9999, to an
accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds
or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007
seconds, as shown in the table. (the table below not included here)"
Maybe this helps you somehow.
--
-Dick Christoph
"Dave" <daveg.01@.gmail.com> wrote in message
news:1143152560.420148.112820@.e56g2000cwe.googlegroups.com...
>I noticed that my tables are not storing the milisecond values that I
> am inserting. Is this a bug? If not, can someone please explain what
> is going or or point me to a resource so I can research it further.
>
> create table #t (col1 int identity,col2 datetime)
> insert into #t (col2)
> SELECT '2006-03-21 18:59:50.985'
> select * from #t
>|||Yes, it does. I should have looked this up. It rounds to the nearest
.003 second.
Thanks!

Thursday, February 16, 2012

Bring File In As Binary Field

I want to have an SSIS package that processes a file in the normal insert, update style. But at the end I want to store the file as a binary field to another table for archive purposes. I am having trouble finding a good way to do this. Any samples, ideas, or articles would be appreciated.I haven't tried this, but my first thought would be reading the file into a variable through a script task, and then using that variable to write the blob to the destination table.|||That is the only method I could come up with as well.|||

Import Column transform is supposed to do this. Could you give it a try? It is not easy to configure as it deos not have a custom UI, but see if the BOL has some info.

Thanks.

|||It sounds like this might do what I need but books on line (like many things) is worthless for explanation of how/what to setup and use this component. Are you aware of other resources for this component?|||A quick Google search turned this up. See if it helps:

http://mgopinath.blogspot.com/search/label/Integration%20Services|||Thanks Phil... my google skills were poor as I was unable to find an article like that.|||

1Dave wrote:
Thanks Phil... my google skills were poor as I was unable to find an article like that.

ssis "import column"

Thursday, March 22, 2012

Bug?: left outer join in combination with a view

Bug?: left outer join in combination with a view

Bug?: left outer join in combination with a view

Monday, March 19, 2012

Bug with 'enable identity insert on'

Thursday, March 8, 2012

bug in exception handling (begin try... begin catch...)?

Wednesday, March 7, 2012

bug - insert miliseconds

bug - insert miliseconds

Thursday, February 16, 2012

Bring File In As Binary Field

Boot.ini file

Blog Archive

About Me