Anyone has any idea on how to insert 2 strings into a row with 2 lines created?

<p>Hi ,

I would like to know anyway to insert two strings into a row with 2 lines created?
My codes are as below

If NodeName = "subProductPrice" Then
If xmlrder.NodeType = Xml.XmlNodeType.Text Then
SubPrPriceList.Add(xmlrder.Value)

For i = 0 To SubPrPriceList.Count - 1
SubPrPrice = CStr(SubPrPriceList.Item(i))
PriceBrkDownStr &= SubPrPrice

Next

PricePerDay = SvcDate & PriceBrkDownStr
dr("dailyPrice") = PricePerDay
End If
End If

Although both the SvcDate and PriceBrkDownStr are inserted into the same row but they are displayed into one line such as below:

<u>dailyPrice </u>
02/03/2007 03/03/2007 120 230

Any idea how to make the date and price separate into two rows in the same table grid row? thanks =)
</p>

store <br> between the lines and let me know if it worked...

|||

Hi, sorry I dont get what you mean. Insert <br> between 2 lines? I am inserting the string into the table grid row. How can I insert the tag <br> ? =P

|||

I mean store them as a single line with <br> between them and when you will diplay them they will be shown like two lines... i understood thats what you want to do... if it is not the case then my mistake :)

any1 please really stuck! sql statement insert

Hi

I have the following insert statement

However it wont insert into the database

can any1 help or suggest tried everything

thanx

SqlCommand cmd2 = new SqlCommand("INSERT INTO room_type_temp (Room_code, Room_description, Room_notes, area, height, spanotes, pladescription, perdescription, floor, walls, ceiling, doorsets, glazing, windows, chanotes, hatch, air, lighting, noise, safety, awt, ast, amvs, amve, amvsu, ap, af, ah, lsi, lsn, lli, lcr, lslg, nasl, nsp, nt, sahs, AF2, sdhw, atn, amn, apn, lsin, lsnn, llin, lslgg, lcrn, nn, sn, fn, npf, nms, nin, fe, fad, Room_Sheet) SELECT Room_code, Room_description, Room_notes, area, height, spanotes, pladescription, perdescription, floor, walls, ceiling, doorsets, glazing, windows, chanotes, hatch, air, lighting, noise, safety, awt, ast, amvs, amve, amvsu, ap, af, ah, lsi, lsn, lli, lcr, lslg, nasl, nsp, nt, sahs, AF2, sdhw, atn, amn, apn, lsin, lsnn, llin, lslgg, lcrn, nn, sn, fn, npf, nms, nin, fe, fad, Room_Sheet FROM room_types where room_code = " + (Session["room_code"].ToString()), con);

Did you get error message? It seems that you missed single quote around session.

FROM room_types where room_code = " + (Session["room_code"].ToString()), con);

Should be

FROM room_types where room_code = '" + (Session["room_code"].ToString()) + "'", con);

|||

Hi

found it as well

used '" + room_code + "'

cheers!!!

:-)

Any way to suppress ADO error from stored proc?

Hi,
I have a stored procedure that can insert multiple rows but it does them in
a loop so the inserts happen one at a time. There is a unique constraint on
the table and sometimes the insert violates this and the constraint
violation gets returned as an error. What I want to do is to trap the error
report it in another table and continue processing all of the inserts and
not have the unique constraint error returned to the calling application.
The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
procedure processing all the inserts and then I just do a RETURN(0) at the
end. Unfortunately this still returns the unique constraint violation even
though I am doing the RETURN(0). There doesn't seem to be any way to keep
the unique constraint from going through to ADO and reporting back an error.
The procedure has now been changed to check for the unique problem before
performing the insert but that seems ineffecient so I would rather just
suppress the error if possible. Thanks in advance for any ideas.
Wayne AntinoreHi Wayne.
Unfortunately, T-SQL offers no way to either suppress errors or provide
run-time inspection of full error messages.
You will have to implement exception management in the client to ignore
error message passed back up to ADO if you really want to take the approach
you've described.
Many people take the approach you've described & perform the distinct query
before performing the insert, but even this is not fool-proof in a high
concurrency environment.
fwiw - this has been bitched about for years & we've got some better error
handling tools coming in the next version of SQL Server. For now though,
you're stuck with this problem as you've described.
Regards,
Greg Linwood
SQL Server MVP
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Wayne
Look at SET XACT_ABORT on BOL
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Hi Uri.
I think this won't help Wayne.
SET XACT_ABORT ON will attempt to rollback an entire transaction on any
error. The way I read Wayne's post, he wants his transaction to continue
processing other rows, even after a constraint violation, so it would have
the opposite effect from what he's after.
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> Wayne
> Look at SET XACT_ABORT on BOL
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I have a stored procedure that can insert multiple rows but it does them
> in
> > a loop so the inserts happen one at a time. There is a unique
constraint
> on
> > the table and sometimes the insert violates this and the constraint
> > violation gets returned as an error. What I want to do is to trap the
> error
> > report it in another table and continue processing all of the inserts
and
> > not have the unique constraint error returned to the calling
application.
> > The procedure was changed to check @.@.ERROR and use a CONTINUE to keep
the
> > procedure processing all the inserts and then I just do a RETURN(0) at
the
> > end. Unfortunately this still returns the unique constraint violation
> even
> > though I am doing the RETURN(0). There doesn't seem to be any way to
keep
> > the unique constraint from going through to ADO and reporting back an
> error.
> > The procedure has now been changed to check for the unique problem
before
> > performing the insert but that seems ineffecient so I would rather just
> > suppress the error if possible. Thanks in advance for any ideas.
> >
> > Wayne Antinore
> >
> >
>|||Hi,Greg
>The way I read Wayne's post, he wants his transaction to continue
>processing other rows, even after a constraint violation, so it would have
>the opposite effect from what he's after.
So ,he can use
This is an example from BOL
CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO
/* Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back, but
XACT_ABORT was OFF and rest of transaction
succeeded.
*/
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Hi Uri.
> I think this won't help Wayne.
> SET XACT_ABORT ON will attempt to rollback an entire transaction on any
> error. The way I read Wayne's post, he wants his transaction to continue
> processing other rows, even after a constraint violation, so it would have
> the opposite effect from what he's after.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > Wayne
> > Look at SET XACT_ABORT on BOL
> >
> > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > Hi,
> > > I have a stored procedure that can insert multiple rows but it does
them
> > in
> > > a loop so the inserts happen one at a time. There is a unique
> constraint
> > on
> > > the table and sometimes the insert violates this and the constraint
> > > violation gets returned as an error. What I want to do is to trap the
> > error
> > > report it in another table and continue processing all of the inserts
> and
> > > not have the unique constraint error returned to the calling
> application.
> > > The procedure was changed to check @.@.ERROR and use a CONTINUE to keep
> the
> > > procedure processing all the inserts and then I just do a RETURN(0) at
> the
> > > end. Unfortunately this still returns the unique constraint violation
> > even
> > > though I am doing the RETURN(0). There doesn't seem to be any way to
> keep
> > > the unique constraint from going through to ADO and reporting back an
> > error.
> > > The procedure has now been changed to check for the unique problem
> before
> > > performing the insert but that seems ineffecient so I would rather
just
> > > suppress the error if possible. Thanks in advance for any ideas.
> > >
> > > Wayne Antinore
> > >
> > >
> >
> >
>|||Hi Uri.
I now see you intended to set it OFF rather than ON.
However - Wayne's problem still remains that errors will be thrown through
ADO to his client, regardless of how XACT_ABORT is set.
Whether you set XACT_ABORT either ON / OFF won't solve his problem..
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> Hi,Greg
> >The way I read Wayne's post, he wants his transaction to continue
> >processing other rows, even after a constraint violation, so it would
have
> >the opposite effect from what he's after.
>
> So ,he can use
> This is an example from BOL
> CREATE TABLE t1 (a int PRIMARY KEY)
> CREATE TABLE t2 (a int REFERENCES t1(a))
> GO
> INSERT INTO t1 VALUES (1)
> INSERT INTO t1 VALUES (3)
> INSERT INTO t1 VALUES (4)
> INSERT INTO t1 VALUES (6)
> GO
> SET XACT_ABORT OFF
> GO
> BEGIN TRAN
> INSERT INTO t2 VALUES (1)
> INSERT INTO t2 VALUES (2) /* Foreign key error */
> INSERT INTO t2 VALUES (3)
> COMMIT TRAN
> GO
> /* Select shows only keys 1 and 3 added.
> Key 2 insert failed and was rolled back, but
> XACT_ABORT was OFF and rest of transaction
> succeeded.
> */
> "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
> message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Hi Uri.
> >
> > I think this won't help Wayne.
> >
> > SET XACT_ABORT ON will attempt to rollback an entire transaction on any
> > error. The way I read Wayne's post, he wants his transaction to continue
> > processing other rows, even after a constraint violation, so it would
have
> > the opposite effect from what he's after.
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > Wayne
> > > Look at SET XACT_ABORT on BOL
> > >
> > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > Hi,
> > > > I have a stored procedure that can insert multiple rows but it does
> them
> > > in
> > > > a loop so the inserts happen one at a time. There is a unique
> > constraint
> > > on
> > > > the table and sometimes the insert violates this and the constraint
> > > > violation gets returned as an error. What I want to do is to trap
the
> > > error
> > > > report it in another table and continue processing all of the
inserts
> > and
> > > > not have the unique constraint error returned to the calling
> > application.
> > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
keep
> > the
> > > > procedure processing all the inserts and then I just do a RETURN(0)
at
> > the
> > > > end. Unfortunately this still returns the unique constraint
violation
> > > even
> > > > though I am doing the RETURN(0). There doesn't seem to be any way
to
> > keep
> > > > the unique constraint from going through to ADO and reporting back
an
> > > error.
> > > > The procedure has now been changed to check for the unique problem
> > before
> > > > performing the insert but that seems ineffecient so I would rather
> just
> > > > suppress the error if possible. Thanks in advance for any ideas.
> > > >
> > > > Wayne Antinore
> > > >
> > > >
> > >
> > >
> >
> >
>|||THere is no way to suppress the Constraint violation error from the stored
procedure ( on the back end.).
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>|||Greg
I hope it will be possible with "Yukon" to use begin try catch ( I don't
remember exactly what is a syntax)
"Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
message news:#2qpMoNuDHA.2508@.TK2MSFTNGP12.phx.gbl...
> Hi Uri.
> I now see you intended to set it OFF rather than ON.
> However - Wayne's problem still remains that errors will be thrown through
> ADO to his client, regardless of how XACT_ABORT is set.
> Whether you set XACT_ABORT either ON / OFF won't solve his problem..
> Regards,
> Greg Linwood
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > Hi,Greg
> > >The way I read Wayne's post, he wants his transaction to continue
> > >processing other rows, even after a constraint violation, so it would
> have
> > >the opposite effect from what he's after.
> >
> >
> > So ,he can use
> > This is an example from BOL
> >
> > CREATE TABLE t1 (a int PRIMARY KEY)
> > CREATE TABLE t2 (a int REFERENCES t1(a))
> > GO
> > INSERT INTO t1 VALUES (1)
> > INSERT INTO t1 VALUES (3)
> > INSERT INTO t1 VALUES (4)
> > INSERT INTO t1 VALUES (6)
> > GO
> > SET XACT_ABORT OFF
> > GO
> > BEGIN TRAN
> > INSERT INTO t2 VALUES (1)
> > INSERT INTO t2 VALUES (2) /* Foreign key error */
> > INSERT INTO t2 VALUES (3)
> > COMMIT TRAN
> > GO
> >
> > /* Select shows only keys 1 and 3 added.
> > Key 2 insert failed and was rolled back, but
> > XACT_ABORT was OFF and rest of transaction
> > succeeded.
> > */
> >
> > "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote
in
> > message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Hi Uri.
> > >
> > > I think this won't help Wayne.
> > >
> > > SET XACT_ABORT ON will attempt to rollback an entire transaction on
any
> > > error. The way I read Wayne's post, he wants his transaction to
continue
> > > processing other rows, even after a constraint violation, so it would
> have
> > > the opposite effect from what he's after.
> > >
> > > Regards,
> > > Greg Linwood
> > > SQL Server MVP
> > >
> > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > > Wayne
> > > > Look at SET XACT_ABORT on BOL
> > > >
> > > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > Hi,
> > > > > I have a stored procedure that can insert multiple rows but it
does
> > them
> > > > in
> > > > > a loop so the inserts happen one at a time. There is a unique
> > > constraint
> > > > on
> > > > > the table and sometimes the insert violates this and the
constraint
> > > > > violation gets returned as an error. What I want to do is to trap
> the
> > > > error
> > > > > report it in another table and continue processing all of the
> inserts
> > > and
> > > > > not have the unique constraint error returned to the calling
> > > application.
> > > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
> keep
> > > the
> > > > > procedure processing all the inserts and then I just do a
RETURN(0)
> at
> > > the
> > > > > end. Unfortunately this still returns the unique constraint
> violation
> > > > even
> > > > > though I am doing the RETURN(0). There doesn't seem to be any way
> to
> > > keep
> > > > > the unique constraint from going through to ADO and reporting back
> an
> > > > error.
> > > > > The procedure has now been changed to check for the unique problem
> > > before
> > > > > performing the insert but that seems ineffecient so I would rather
> > just
> > > > > suppress the error if possible. Thanks in advance for any ideas.
> > > > >
> > > > > Wayne Antinore
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yep - Try / Catch will be good in Yukon.
I'm not sure if it allows error suppression, but I sure hope so!
Regards,
Greg Linwood
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:#yR3yTOuDHA.1596@.TK2MSFTNGP10.phx.gbl...
> Greg
> I hope it will be possible with "Yukon" to use begin try catch ( I don't
> remember exactly what is a syntax)
>
>
> "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote in
> message news:#2qpMoNuDHA.2508@.TK2MSFTNGP12.phx.gbl...
> > Hi Uri.
> >
> > I now see you intended to set it OFF rather than ON.
> >
> > However - Wayne's problem still remains that errors will be thrown
through
> > ADO to his client, regardless of how XACT_ABORT is set.
> >
> > Whether you set XACT_ABORT either ON / OFF won't solve his problem..
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:OtZIISNuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > Hi,Greg
> > > >The way I read Wayne's post, he wants his transaction to continue
> > > >processing other rows, even after a constraint violation, so it would
> > have
> > > >the opposite effect from what he's after.
> > >
> > >
> > > So ,he can use
> > > This is an example from BOL
> > >
> > > CREATE TABLE t1 (a int PRIMARY KEY)
> > > CREATE TABLE t2 (a int REFERENCES t1(a))
> > > GO
> > > INSERT INTO t1 VALUES (1)
> > > INSERT INTO t1 VALUES (3)
> > > INSERT INTO t1 VALUES (4)
> > > INSERT INTO t1 VALUES (6)
> > > GO
> > > SET XACT_ABORT OFF
> > > GO
> > > BEGIN TRAN
> > > INSERT INTO t2 VALUES (1)
> > > INSERT INTO t2 VALUES (2) /* Foreign key error */
> > > INSERT INTO t2 VALUES (3)
> > > COMMIT TRAN
> > > GO
> > >
> > > /* Select shows only keys 1 and 3 added.
> > > Key 2 insert failed and was rolled back, but
> > > XACT_ABORT was OFF and rest of transaction
> > > succeeded.
> > > */
> > >
> > > "Greg Linwood" <g_linwoodremovethisbeforeemailingme@.hotmail.com> wrote
> in
> > > message news:OdtgRONuDHA.3744@.TK2MSFTNGP11.phx.gbl...
> > > > Hi Uri.
> > > >
> > > > I think this won't help Wayne.
> > > >
> > > > SET XACT_ABORT ON will attempt to rollback an entire transaction on
> any
> > > > error. The way I read Wayne's post, he wants his transaction to
> continue
> > > > processing other rows, even after a constraint violation, so it
would
> > have
> > > > the opposite effect from what he's after.
> > > >
> > > > Regards,
> > > > Greg Linwood
> > > > SQL Server MVP
> > > >
> > > > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > > > news:u8IqSJNuDHA.2304@.tk2msftngp13.phx.gbl...
> > > > > Wayne
> > > > > Look at SET XACT_ABORT on BOL
> > > > >
> > > > > "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> > > > > news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> > > > > > Hi,
> > > > > > I have a stored procedure that can insert multiple rows but it
> does
> > > them
> > > > > in
> > > > > > a loop so the inserts happen one at a time. There is a unique
> > > > constraint
> > > > > on
> > > > > > the table and sometimes the insert violates this and the
> constraint
> > > > > > violation gets returned as an error. What I want to do is to
trap
> > the
> > > > > error
> > > > > > report it in another table and continue processing all of the
> > inserts
> > > > and
> > > > > > not have the unique constraint error returned to the calling
> > > > application.
> > > > > > The procedure was changed to check @.@.ERROR and use a CONTINUE to
> > keep
> > > > the
> > > > > > procedure processing all the inserts and then I just do a
> RETURN(0)
> > at
> > > > the
> > > > > > end. Unfortunately this still returns the unique constraint
> > violation
> > > > > even
> > > > > > though I am doing the RETURN(0). There doesn't seem to be any
way
> > to
> > > > keep
> > > > > > the unique constraint from going through to ADO and reporting
back
> > an
> > > > > error.
> > > > > > The procedure has now been changed to check for the unique
problem
> > > > before
> > > > > > performing the insert but that seems ineffecient so I would
rather
> > > just
> > > > > > suppress the error if possible. Thanks in advance for any
ideas.
> > > > > >
> > > > > > Wayne Antinore
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks Uri, Greg, and Wayne for your replies. I didn't think there was any
way to do this currently but I'm glad you confirmed it for me. This will be
a very useful feature in Yukon
Thanks again,
Wayne
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:eIp7jDNuDHA.2360@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a stored procedure that can insert multiple rows but it does them
in
> a loop so the inserts happen one at a time. There is a unique constraint
on
> the table and sometimes the insert violates this and the constraint
> violation gets returned as an error. What I want to do is to trap the
error
> report it in another table and continue processing all of the inserts and
> not have the unique constraint error returned to the calling application.
> The procedure was changed to check @.@.ERROR and use a CONTINUE to keep the
> procedure processing all the inserts and then I just do a RETURN(0) at the
> end. Unfortunately this still returns the unique constraint violation
even
> though I am doing the RETURN(0). There doesn't seem to be any way to keep
> the unique constraint from going through to ADO and reporting back an
error.
> The procedure has now been changed to check for the unique problem before
> performing the insert but that seems ineffecient so I would rather just
> suppress the error if possible. Thanks in advance for any ideas.
> Wayne Antinore
>

Any way to pass command line variables to osql

I want to pass a command line values into an osql run stored procedure.
The commandline values should be are the values to put into the insert stored procedure that writes them to a table.
Is it possible to do this.if ur parameter is positioned 2nd then add this code in your command file.

<varname> is some variable name that you want to use to trap the parameter. lets say empid for instance

SET empid=%2

after this, the place where u wud be calling ur stored procedure use this syntax.

osql -S servername -l 60 -n -E -d dbname
-Q"EXEC sp123 @.Var1 = '%empid%'"

Any way to insert page breaks between tables?

I have about 5 tables on a report.
Is there a way to insert a break between the tables to prevent tables
from being chopped on a page break?
Thank you!Pull up table properties dialog and check "insert page break after this
table" checkbox and also "fit this table on one page if possible" checkbox.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"The Whistler" <sharris@.SLeasynews.com> wrote in message
news:bg48i09pocfef9h7ot6rmklf02uas8dl49@.4ax.com...
> I have about 5 tables on a report.
> Is there a way to insert a break between the tables to prevent tables
> from being chopped on a page break?
> Thank you!
>sql

Any SQL wizard can help? Reformat the input file and transfer into SQL server

I am trying to transfer 200 txt files into SQL server by using query analyzer.
The command is 'Bulk insert [tableName] from 'path\filename.txt'
However, I need to read and modifiy the txt file.
I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily.

Thank you for the help in advance!

Here is the raw data layout, which is comma delimited.
BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990
Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005
Fq D Fq D Fq D Fq D
Date R P M E D Date R P M E D Date R P M E D Date R P M E D
1/1/90 1 2 3 4 5 1/1/90 2 3 4 5 6 1/1/90 3 4 5 6 7 1/1/90 4 5 6 7 8
2 3 4 5 6 1 2 3 4 5 3 4 5 6 7 6 7 8 9 1
1/1/05 ..... 1/1/05 ... 1/1/05 .... 1/1/05 ....

This is the desired output after load into the table, which is tacking each repeating block on top of each other.
Date R P M E D
1/1/90 1 2 3 4 5
2 3 4 5 6
1/1/05 .....
1/1/90 2 3 4 5 6
2 3 4 5 6
1/1/05 .....
1/1/90 3 4 5 6 7
3 4 5 6 7
1/1/05 .....
1/1/90 4 5 6 7 8
6 7 8 9 1
1/1/05 ....."I am trying to transfer 200 txt files into SQL server by using query analyzer."
--DTS might be more appropriate.

"I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily."
--Faith is a powerful thing.

"Here is the raw data layout, which is comma delimited."
--What you posted is not comma delimited.

"This is the desired output after load into the table, which is tacking each repeating block on top of each other."
--You are going to need to load this data into a staging table and normalize it before loading into your production tables. The process will be complex, involving several passes through the data.

If at all possible, try to get your source data in a better format. Practically any other format would be preferable to what you posted.|||Blindman,
Thank you for your reply.
You are right... I forgot to put "," in my sample file layout.
I am using another source provider to request time series in excel. This is the most efficient way I can utilize excel ability (256 columns and over 65,000 rows). That's why the raw data layout looks wired. However, I have to stick to it.

I was thinking to load these files into a table to normalize but I am not sure if I know SQL well enough to say this is the best solution. I think I got the answer from you.

What is staging db. I assume it is one of defualt DB in in enterprise manager, however, I did not see it. Or this is the name you gave?

Thank you again for the help.
Shiparsons|||Not "Staging DB". "Staging TABLE."

A staging table is basically an table that has the same structure as your input data, with additional columns added as needed to keep track of records as they are being processed. I always add an "Imported" column that defaults to getdate(), and an ImportErrors column that I populate as necessary during processing.

Your staging table should have no Primary Keys or constraints (unless you add a surrogate PKey for processing...), so that your import process never fails because the data does not match what is expected.

Once the data is in the staging table you cleans it and make sure it satisfies all the business rules required by your production tables. Then you make as many passes through the staging table as necessary to update the various production tables it feeds, starting with the top-level tables.|||Thank you for the explanation.
What datatype I should use when I create my staging table? I assume this is nonconstraints type since my raw data contains text, datetime, and float.

Thank you,
Qing|||You should try to match the datatype to the type of the data being entered, though some people just make all staging table columns varchar by default. I don't do this, as a rule, but you may have no other choice since your import file is actually a mix of different layouts. String fields are the only column types that will accept any input type.|||Blindman,
Thank you for the help.

I will try.

shiparsons

Any quick way to find middle value out of 3?

Please help
declare @.t table (id1 int not null, id2 int not null, id3 int null)
insert @.t values (1,2,null) -- middle null
insert @.t values (10,20,1) -- middle 10
insert @.t values (11,23,12) -- middle 12
insert @.t values (20,100,123) -- middle 100
select *, middle(ID1,id2,id3) --< middle out of 3
FROM @.t
Thank you!There are probably better ways, but this should work.
You could do the same thing using BETWEEN but I prefer to avoid using
BETWEEN.
I assumed that the values could be the same between 2 or 3 of the columns
SELECT ID1, ID2, ID3,
CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
ELSE ID3 END as Middle
FROM @.t
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Farmer" wrote:

> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>
>|||One additional note, this would get a bit uglier if id1 or id2 could also be
null. I was able to cheat a bit since only id3 was allowed to be null, and
you seemed to want the null value for your middle value.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Ryan Powers" wrote:
> There are probably better ways, but this should work.
> You could do the same thing using BETWEEN but I prefer to avoid using
> BETWEEN.
> I assumed that the values could be the same between 2 or 3 of the columns
> SELECT ID1, ID2, ID3,
> CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
> WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
> WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
> WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
> ELSE ID3 END as Middle
> FROM @.t
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Farmer" wrote:
>|||using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 Middle(SqlInt32 i1, SqlInt32 i2, SqlInt32 i3)
{
if (i1 < i2)
{
if (i3 < i1)
return i1;
if (i3 > i2)
return i2;
return i3;
}
if (i1 > i2)
{
if (i3 > i1)
return i1;
if (i3 < i2)
return i2;
return i3;
}
if (i3 <= i1)
return i1;
return i2;
}
};
William Stacey [MVP]
"Farmer" <someone@.somewhere.com> wrote in message
news:eFURSHkFGHA.1816@.TK2MSFTNGP11.phx.gbl...
> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>

Any easy way of getting a columnlist for use with an INSERT statement

Hi
I'm having some data that I need to copy from one table to another using an
INSERT statement. I'm wondering if there's an easier way to enter the column
list than having to type it in manually? In this case the table has about 20
columns (..of course with a lot of odd names...) and I find a bit
cumbersome to type all these in by hand, so hopefully some of you knows an
easier way of doing it?
Regards
SteenIn Query Analyzer, right-click on the table in the Object Browser. Mouse
over "Script Object to New Window as" and click "Insert".
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:erbYBVZmEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi
> I'm having some data that I need to copy from one table to another using
an
> INSERT statement. I'm wondering if there's an easier way to enter the
column
> list than having to type it in manually? In this case the table has about
20
> columns (..of course with a lot of odd names...) and I find a bit
> cumbersome to type all these in by hand, so hopefully some of you knows an
> easier way of doing it?
> Regards
> Steen
>|||Enjoy this :
select ' ' + T2.name + ','
from sysobjects T1, syscolumns T2
where
T1.type ='U'
and T1.Id = T2.Id
And T1.name = 'YourTable'
order by colid
Don
>--Original Message--
>Hi
>I'm having some data that I need to copy from one table
to another using an
>INSERT statement. I'm wondering if there's an easier way
to enter the column
>list than having to type it in manually? In this case the
table has about 20
>columns (..of course with a lot of odd names...) and I
find a bit
>cumbersome to type all these in by hand, so hopefully
some of you knows an
>easier way of doing it?
>Regards
>Steen
>
>.
>|||Thanks...that was just what I wanted.
Regards
Steen
David Portas wrote:
> If you are using SQL2000 you can drag the list of column names from
> the Object Browser in Query Analyzer. Just click on the word
> "columns" in the tree and drag it into the editing window.
> --
> David Portas
> SQL Server MVP

Any easy way of getting a columnlist for use with an INSERT statement

Hi
I'm having some data that I need to copy from one table to another using an
INSERT statement. I'm wondering if there's an easier way to enter the column
list than having to type it in manually? In this case the table has about 20
columns (..of course with a lot of odd names...) and I find a bit
cumbersome to type all these in by hand, so hopefully some of you knows an
easier way of doing it?
Regards
Steen
In Query Analyzer, right-click on the table in the Object Browser. Mouse
over "Script Object to New Window as" and click "Insert".
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:erbYBVZmEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Hi
> I'm having some data that I need to copy from one table to another using
an
> INSERT statement. I'm wondering if there's an easier way to enter the
column
> list than having to type it in manually? In this case the table has about
20
> columns (..of course with a lot of odd names...) and I find a bit
> cumbersome to type all these in by hand, so hopefully some of you knows an
> easier way of doing it?
> Regards
> Steen
>

ANTI-JOIN with two or more columns.

Consider the following schema:
CREATE TABLE A(i int, j int)
CREATE TABLE B(i int, j int)
INSERT A VALUES(1,1)
INSERT A VALUES(1,2)
INSERT A VALUES(2,1)
INSERT A VALUES(2,2)
INSERT A VALUES(2,3)
INSERT B VALUES(3,1)
INSERT B VALUES(3,2)
INSERT B VALUES(1,1)
INSERT B VALUES(1,2)
INSERT B VALUES(2,2)
INSERT B VALUES(4,5)
INSERT B VALUES(5,4)
How do I compose a TSQL Query that will list the records in Table B, that DO
NOT exist in Table A.?
I would like a record set that looks like this:
i j
-- --
3 1
3 2
4 5
5 4
--
Bob MarleyBob,
I don't know if column "i" is supposed to represent the PK or the combination of "i" and
"j". There isn't enough data here to make that determination, so I'll assume the latter:
select *
from B as b
where not exists (select * from A as a where a.i = b.i and a.j = b.j)
Hope this helps!
"Big Bob" <Bob@.hotmail.com> wrote in message
news:%23Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||Try:
select * from b
where not exists
(select * from a where a.i = b.i and a.j=b.j)
--
- Vishal
"Big Bob" <Bob@.hotmail.com> wrote in message news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||Hi Bob
This is a perfect example of what correlated subqueries were invented for.
SELECT i,j
FROM B
WHERE NOT EXISTS (SELECT i, j FROM A
WHERE i = B.i AND j = B.j)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Big Bob" <Bob@.hotmail.com> wrote in message
news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that
DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||Thanks Kalen!
Is there a way to do this with the new style SQL-92 joins rather than the
old SQL-89 join style. This is what I have been beating my head against the
wall with; the new join style.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> Hi Bob
> This is a perfect example of what correlated subqueries were invented for.
>
> SELECT i,j
> FROM B
> WHERE NOT EXISTS (SELECT i, j FROM A
> WHERE i = B.i AND j = B.j)
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Big Bob" <Bob@.hotmail.com> wrote in message
> news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > Consider the following schema:
> >
> > CREATE TABLE A(i int, j int)
> > CREATE TABLE B(i int, j int)
> >
> > INSERT A VALUES(1,1)
> > INSERT A VALUES(1,2)
> > INSERT A VALUES(2,1)
> > INSERT A VALUES(2,2)
> > INSERT A VALUES(2,3)
> >
> > INSERT B VALUES(3,1)
> > INSERT B VALUES(3,2)
> > INSERT B VALUES(1,1)
> > INSERT B VALUES(1,2)
> > INSERT B VALUES(2,2)
> > INSERT B VALUES(4,5)
> > INSERT B VALUES(5,4)
> >
> >
> > How do I compose a TSQL Query that will list the records in Table B,
that
> DO
> > NOT exist in Table A.?
> >
> > I would like a record set that looks like this:
> >
> > i j
> > -- --
> > 3 1
> > 3 2
> > 4 5
> > 5 4
> >
> >
> >
> > --
> > Bob Marley
> >
> >
>|||use NOT EXISTS
e.g.
select * from B
where NOT EXISTS
(select * from A where A.i = B.i and A.j = B.j)
"Big Bob" <Bob@.hotmail.com> wrote in message
news:%23Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that
DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||I don't think this can be done with any kind of join.
If you have found a way to do it with the old style, post it and I'm sure
someone can easily convert it to new style.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Big Bob" <Bob@.hotmail.com> wrote in message
news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
> Thanks Kalen!
> Is there a way to do this with the new style SQL-92 joins rather than the
> old SQL-89 join style. This is what I have been beating my head against
the
> wall with; the new join style.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > Hi Bob
> >
> > This is a perfect example of what correlated subqueries were invented
for.
> >
> >
> > SELECT i,j
> > FROM B
> > WHERE NOT EXISTS (SELECT i, j FROM A
> > WHERE i = B.i AND j = B.j)
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Big Bob" <Bob@.hotmail.com> wrote in message
> > news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > > Consider the following schema:
> > >
> > > CREATE TABLE A(i int, j int)
> > > CREATE TABLE B(i int, j int)
> > >
> > > INSERT A VALUES(1,1)
> > > INSERT A VALUES(1,2)
> > > INSERT A VALUES(2,1)
> > > INSERT A VALUES(2,2)
> > > INSERT A VALUES(2,3)
> > >
> > > INSERT B VALUES(3,1)
> > > INSERT B VALUES(3,2)
> > > INSERT B VALUES(1,1)
> > > INSERT B VALUES(1,2)
> > > INSERT B VALUES(2,2)
> > > INSERT B VALUES(4,5)
> > > INSERT B VALUES(5,4)
> > >
> > >
> > > How do I compose a TSQL Query that will list the records in Table B,
> that
> > DO
> > > NOT exist in Table A.?
> > >
> > > I would like a record set that looks like this:
> > >
> > > i j
> > > -- --
> > > 3 1
> > > 3 2
> > > 4 5
> > > 5 4
> > >
> > >
> > >
> > > --
> > > Bob Marley
> > >
> > >
> >
> >
>|||if you really want to use it, you can use a left join
select b.*
from b
left join a on b.i=a.i and b.j=a.j
where a.i is null
the where eliminates the joined rows, however
1. it defeats the purpose of a left join somewhat, which is to get all from
left, and matching from right
2. afaik, using exists is faster
"Big Bob" <Bob@.hotmail.com> wrote in message
news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
> Thanks Kalen!
> Is there a way to do this with the new style SQL-92 joins rather than the
> old SQL-89 join style. This is what I have been beating my head against
the
> wall with; the new join style.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > Hi Bob
> >
> > This is a perfect example of what correlated subqueries were invented
for.
> >
> >
> > SELECT i,j
> > FROM B
> > WHERE NOT EXISTS (SELECT i, j FROM A
> > WHERE i = B.i AND j = B.j)
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Big Bob" <Bob@.hotmail.com> wrote in message
> > news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > > Consider the following schema:
> > >
> > > CREATE TABLE A(i int, j int)
> > > CREATE TABLE B(i int, j int)
> > >
> > > INSERT A VALUES(1,1)
> > > INSERT A VALUES(1,2)
> > > INSERT A VALUES(2,1)
> > > INSERT A VALUES(2,2)
> > > INSERT A VALUES(2,3)
> > >
> > > INSERT B VALUES(3,1)
> > > INSERT B VALUES(3,2)
> > > INSERT B VALUES(1,1)
> > > INSERT B VALUES(1,2)
> > > INSERT B VALUES(2,2)
> > > INSERT B VALUES(4,5)
> > > INSERT B VALUES(5,4)
> > >
> > >
> > > How do I compose a TSQL Query that will list the records in Table B,
> that
> > DO
> > > NOT exist in Table A.?
> > >
> > > I would like a record set that looks like this:
> > >
> > > i j
> > > -- --
> > > 3 1
> > > 3 2
> > > 4 5
> > > 5 4
> > >
> > >
> > >
> > > --
> > > Bob Marley
> > >
> > >
> >
> >
>|||Cool. Yes, it sort of defeats the purpose of LEFT JOIN, so maybe that's why
I didn't think of it. Also, it is prime example of why not all old style
joins can be converted easily to new style joins, or visa versa, as in this
case.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Trey Walpole" <treyNOpole@.SPcomcastAM.net> wrote in message
news:#Vf18JkrDHA.1692@.TK2MSFTNGP12.phx.gbl...
> if you really want to use it, you can use a left join
> select b.*
> from b
> left join a on b.i=a.i and b.j=a.j
> where a.i is null
> the where eliminates the joined rows, however
> 1. it defeats the purpose of a left join somewhat, which is to get all
from
> left, and matching from right
> 2. afaik, using exists is faster
>
> "Big Bob" <Bob@.hotmail.com> wrote in message
> news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
> > Thanks Kalen!
> >
> > Is there a way to do this with the new style SQL-92 joins rather than
the
> > old SQL-89 join style. This is what I have been beating my head against
> the
> > wall with; the new join style.
> >
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > > Hi Bob
> > >
> > > This is a perfect example of what correlated subqueries were invented
> for.
> > >
> > >
> > > SELECT i,j
> > > FROM B
> > > WHERE NOT EXISTS (SELECT i, j FROM A
> > > WHERE i = B.i AND j = B.j)
> > >
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Big Bob" <Bob@.hotmail.com> wrote in message
> > > news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > > > Consider the following schema:
> > > >
> > > > CREATE TABLE A(i int, j int)
> > > > CREATE TABLE B(i int, j int)
> > > >
> > > > INSERT A VALUES(1,1)
> > > > INSERT A VALUES(1,2)
> > > > INSERT A VALUES(2,1)
> > > > INSERT A VALUES(2,2)
> > > > INSERT A VALUES(2,3)
> > > >
> > > > INSERT B VALUES(3,1)
> > > > INSERT B VALUES(3,2)
> > > > INSERT B VALUES(1,1)
> > > > INSERT B VALUES(1,2)
> > > > INSERT B VALUES(2,2)
> > > > INSERT B VALUES(4,5)
> > > > INSERT B VALUES(5,4)
> > > >
> > > >
> > > > How do I compose a TSQL Query that will list the records in Table B,
> > that
> > > DO
> > > > NOT exist in Table A.?
> > > >
> > > > I would like a record set that looks like this:
> > > >
> > > > i j
> > > > -- --
> > > > 3 1
> > > > 3 2
> > > > 4 5
> > > > 5 4
> > > >
> > > >
> > > >
> > > > --
> > > > Bob Marley
> > > >
> > > >
> > >
> > >
> >
> >
>|||Here's a solution that uses an INNER JOIN and grouping:
select B.i, B.j
from B join A
on A.i < B.i and A.j < B.j
group by B.i, B.j
having max(A.i) + max(A.j) < B.i + B.j
And another, with T-SQL proprietary TOP .. WITH
TIES. It's not as efficient, but it's, well, it's another query...
select top 1 with ties B.i, B.j
from B join A
on A.i <> B.i or A.j <> B.j
group by B.i, B.j
order by count(*) desc
-- Steve Kass
-- Drew University
-- Ref: E3D3398B-1749-4F42-B5B0-6F7B71BA2AC5
Trey Walpole wrote:
>if you really want to use it, you can use a left join
>select b.*
> from b
> left join a on b.i=a.i and b.j=a.j
> where a.i is null
>the where eliminates the joined rows, however
>1. it defeats the purpose of a left join somewhat, which is to get all from
>left, and matching from right
>2. afaik, using exists is faster
>
>"Big Bob" <Bob@.hotmail.com> wrote in message
>news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
>
>>Thanks Kalen!
>>Is there a way to do this with the new style SQL-92 joins rather than the
>>old SQL-89 join style. This is what I have been beating my head against
>>
>the
>
>>wall with; the new join style.
>>
>>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>>news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
>>
>>Hi Bob
>>This is a perfect example of what correlated subqueries were invented
>>
>for.
>
>>SELECT i,j
>>FROM B
>>WHERE NOT EXISTS (SELECT i, j FROM A
>> WHERE i = B.i AND j = B.j)
>>
>>--
>>HTH
>>--
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>"Big Bob" <Bob@.hotmail.com> wrote in message
>>news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
>>
>>Consider the following schema:
>>CREATE TABLE A(i int, j int)
>>CREATE TABLE B(i int, j int)
>>INSERT A VALUES(1,1)
>>INSERT A VALUES(1,2)
>>INSERT A VALUES(2,1)
>>INSERT A VALUES(2,2)
>>INSERT A VALUES(2,3)
>>INSERT B VALUES(3,1)
>>INSERT B VALUES(3,2)
>>INSERT B VALUES(1,1)
>>INSERT B VALUES(1,2)
>>INSERT B VALUES(2,2)
>>INSERT B VALUES(4,5)
>>INSERT B VALUES(5,4)
>>
>>How do I compose a TSQL Query that will list the records in Table B,
>>
>>that
>>
>>DO
>>
>>NOT exist in Table A.?
>>I would like a record set that looks like this:
>>i j
>>-- --
>>3 1
>>3 2
>>4 5
>>5 4
>>
>>--
>>Bob Marley
>>
>>
>>
>>
>
>|||Oops - the first one should be
select B.i, B.j
from B join A
on A.i <= B.i and A.j <= B.j
group by B.i, B.j
having max(A.i) + max(A.j) < B.i + B.j
SK
Steve Kass wrote:
> Here's a solution that uses an INNER JOIN and grouping:
> select B.i, B.j
> from B join A
> on A.i < B.i and A.j < B.j
> group by B.i, B.j
> having max(A.i) + max(A.j) < B.i + B.j
>
> And another, with T-SQL proprietary TOP .. WITH
> TIES. It's not as efficient, but it's, well, it's another query...
> select top 1 with ties B.i, B.j
> from B join A
> on A.i <> B.i or A.j <> B.j
> group by B.i, B.j
> order by count(*) desc
>
> -- Steve Kass
> -- Drew University
> -- Ref: E3D3398B-1749-4F42-B5B0-6F7B71BA2AC5
> Trey Walpole wrote:
>> if you really want to use it, you can use a left join
>> select b.*
>> from b
>> left join a on b.i=a.i and b.j=a.j
>> where a.i is null
>> the where eliminates the joined rows, however
>> 1. it defeats the purpose of a left join somewhat, which is to get
>> all from
>> left, and matching from right
>> 2. afaik, using exists is faster
>>
>> "Big Bob" <Bob@.hotmail.com> wrote in message
>> news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Kalen!
>> Is there a way to do this with the new style SQL-92 joins rather
>> than the
>> old SQL-89 join style. This is what I have been beating my head
>> against
>>
>> the
>>
>> wall with; the new join style.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
>>
>> Hi Bob
>> This is a perfect example of what correlated subqueries were invented
>>
>> for.
>>
>> SELECT i,j
>> FROM B
>> WHERE NOT EXISTS (SELECT i, j FROM A
>> WHERE i = B.i AND j = B.j)
>>
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Big Bob" <Bob@.hotmail.com> wrote in message
>> news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
>>
>> Consider the following schema:
>> CREATE TABLE A(i int, j int)
>> CREATE TABLE B(i int, j int)
>> INSERT A VALUES(1,1)
>> INSERT A VALUES(1,2)
>> INSERT A VALUES(2,1)
>> INSERT A VALUES(2,2)
>> INSERT A VALUES(2,3)
>> INSERT B VALUES(3,1)
>> INSERT B VALUES(3,2)
>> INSERT B VALUES(1,1)
>> INSERT B VALUES(1,2)
>> INSERT B VALUES(2,2)
>> INSERT B VALUES(4,5)
>> INSERT B VALUES(5,4)
>>
>> How do I compose a TSQL Query that will list the records in Table B,
>>
>> that
>>
>> DO
>>
>> NOT exist in Table A.?
>> I would like a record set that looks like this:
>> i j
>> -- --
>> 3 1
>> 3 2
>> 4 5
>> 5 4
>>
>> --
>> Bob Marley
>>
>>
>>
>>
>>
>>
>|||For those that are interested,Sql99 would allow this problem
to be easily solved without any correlated subquery,join or
grouping.Only a rank/counter based on the i,j combination and
table identification is necessary.Based on the rank, a simple
select would return the rows in table B not in A.
Here's the basic idea using the RAC utility to easily simulate
the sql99 rank function.
We create a union query where we assign a 1 (tableid) if the
i,j combination is from table A and a 2 if from table B.
We sort the data by i,j and tableid and obtain a rank
of each i,j combination.
Exec Rac
@.transform='_dummy_',
@.rows='i & j & tableid',
@.pvtcol='Sql*Plus',
@.from='(select i,j,2 as tableid from B
union all
select i,j,1 as tableid from A) as alldata',
@.grand_totals='n',@.rowbreak='n',
-- Rank i,j combinations based on the sort of i,j,tableid.
-- The rank column is named tablecnter.
@.rowcounters='j{tablecnter}'
i j tableid tablecnter
-- -- -- --
1 1 1 1
1 1 2 2
1 2 1 1
1 2 2 2
2 1 1 1
2 2 1 1
2 2 2 2
2 3 1 1
3 1 2 1
3 2 2 1
4 5 2 1
5 4 2 1
As you can see only when the tableid is 2 (table B) and
the rank (tablecnter) is 1 is it the case that the i,j
combination is in table B and not in table A.Now the
solution is simple.
Exec Rac
@.transform='_dummy_',
@.rows='i & j & tableid',
@.pvtcol='Sql*Plus',
@.from='(select i,j,2 as tableid from B
union all
select i,j,1 as tableid from A) as alldata',
@.grand_totals='n',@.rowbreak='n',
@.rowcounters='j{tablecnter}',
-- Check for tableid=2 and tablecnter=1 (data only in B).
@.select='select i,j
from rac
where tableid=2 and tablecnter=1
order by rd'
i j
-- --
3 1
3 2
4 5
5 4
RAC v2.2 and QALite @.
www.rac4sql.net

Anticipated PK Violation

I am maintaining a stored proc that does the following (pseudo code for simplicity)

INSERT INTO MyTable (CountColumn, ...) VALUES (1, ...)

-- 2627 = Primary key violation
IF (@.@.ERROR = 2627)
UPDATE MyTable SET CountColumn = CountColumn + 1
WHERE ...

Basically, try to create a new record with a PK and a count of 1. If a PK violation occurs, UPDATE the count of existing record.

Pretty simple logic. On some servers this works exactly as expected; the PK violation is caught and handled internally. On other servers, this error percolates out of the stored proc and causes the calling code to receive an error and fail. All servers are SQL Server 2000 SP3a Standard Edition. Any ideas why this happens?

Is there a better (and ideally as fast or faster) way to handle the INSERT/UPDATE issue? I can put the UPDATE first and INSERT if @.@.ROWCOUNT is 0, but that has the slight potential of a race condition where two processes try to INSERT the same PK at the same time.you could try a conditional flow control block like this:

if not exists (select * from mytable where PK = x)
insert ...
else
update ...

the subquery will always be executed, but it'll be quite fast with the search condition on the primary key

hth,
Cristian Babu|||I'd reverse the if...else:

if exists (...) update... else insert...|||I'd reverse the if...else:

if exists (...) update... else insert...

What I did is:

UPDATE
IF @.@.ROWCOUNT = 0 INSERT

I would think that is slightly more efficient.

What is really odd that the original approach (INSERT, IF ERROR THEN UPDATE) worked fine in Query Analyzer on two servers running SQL Server 2000 Personal edition but produces errors on a new installation of SQL Server 2000 Standard edition. Ideally, as a maintenance programmer, I wouldn't have had to touch the stored proc.|||Are the service pack levels identical on all 3 machines?|||Are the service pack levels identical on all 3 machines?

There is:
SQL Server Personal (no SP): The original code doesn't percolate an error
SQL Server Personal (SP3): The original code doesn't percolate an error
SQL Server Standard (SP3): The original code DOES percolate an error

I'm trying to get coworkers to patch the one unpatched server (we should ALWAYS patch our servers). But either way, it doesn't seem to be related to service patck since two systems with SP3 exhibit different behavior.

ANSI_PADDING woes !

I've got a table that I've added a computed column to. That column is part
of an index. As soon as I attempt to insert a row I get the infamous:
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods
I've tried creating the table with ansi_padding on and off... and with the
session doing the insert on and off...nothing seems to work...what is
causing this?
Thanks for any insight...really stuck and need to get through this to lock
down some input issues...
Hi Tim
I think that the connection that you are trying to insert does not have the
correct ANSI_PADDING setting rather than anything else.
If Ansi_Padding is on when you create the index and insert everything is ok.
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING ON
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 1, 3
go
select * from TST1
go
If it is off when you try to insert but on otherwise, you get your error
message
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 2, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
If it was off when you create the table and index it will give an error
creating the index
use master
go
drop database TESTDB
go
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING Off
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
/*
Msg 1934, Level 16, State 1, Line 1
CREATE INDEX failed because the following SET options have incorrect
settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
indexed views and/or indexes on computed columns and/or query notifications
and/or xml data type methods.
*/
If it is on when creating the index you can insert if ANSI_PADDING is on:
SET ANSI_PADDING On
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 3, 3
go
But not if it is off
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 4, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
-- Checking the user options when off and on
-- When off
SELECT @.@.OPTIONS
-- 5480
SET ANSI_PADDING ON
go
SELECT @.@.OPTIONS
-- 5496
-- 16 is the USER_OPTION value to set!!
HTH
John
"Tim Greenwood" wrote:

> I've got a table that I've added a computed column to. That column is part
> of an index. As soon as I attempt to insert a row I get the infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
>
>
|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> I've got a table that I've added a computed column to. That column is
> part of an index. As soon as I attempt to insert a row I get the
> infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
What was the ANSI_PADDING setting when you created the table and when you
later created the computed column?
|||I stated in the original message I have created the table both ways...and
in each instance tried with it on /off for the session doing the insert.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> What was the ANSI_PADDING setting when you created the table and when you
> later created the computed column?
>
|||Hi
If you check my script out in the other reply it seems to be the session
where you do the insert.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
>
|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
Ahh missed that part. So change it in your session/connection where you're
trying to perform the INSERT.
|||Yes the connection was it...thank you!! I wasn't aware that the connection
had properties different from the session itself...I mean if my connection
did not have ANSI_PADDING enabled and I entered SET ANSI_PADDING ON I would
have thought that would change everything for the existing
session/connection both.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...[vbcol=seagreen]
> Hi Tim
> I think that the connection that you are trying to insert does not have
> the
> correct ANSI_PADDING setting rather than anything else.
> If Ansi_Padding is on when you create the index and insert everything is
> ok.
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING ON
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 1, 3
> go
> select * from TST1
> go
> If it is off when you try to insert but on otherwise, you get your error
> message
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 2, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> If it was off when you create the table and index it will give an error
> creating the index
> use master
> go
> drop database TESTDB
> go
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING Off
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> CREATE INDEX failed because the following SET options have incorrect
> settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
> indexed views and/or indexes on computed columns and/or query
> notifications
> and/or xml data type methods.
> */
> If it is on when creating the index you can insert if ANSI_PADDING is on:
> SET ANSI_PADDING On
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 3, 3
> go
> But not if it is off
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 4, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> -- Checking the user options when off and on
> -- When off
> SELECT @.@.OPTIONS
> -- 5480
> SET ANSI_PADDING ON
> go
> SELECT @.@.OPTIONS
> -- 5496
> -- 16 is the USER_OPTION value to set!!
> HTH
> John
> "Tim Greenwood" wrote:
|||Hi Tim
The SET command would only set the ANSI_PADDING for the current connection
you would need to use sp_configure to set the USER_OPTIONS value, which will
change the properties for any subsequent new connection.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u7mcB1HiIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Yes the connection was it...thank you!! I wasn't aware that the
> connection had properties different from the session itself...I mean if my
> connection did not have ANSI_PADDING enabled and I entered SET
> ANSI_PADDING ON I would have thought that would change everything for the
> existing session/connection both.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
>

ANSI_PADDING woes !

I've got a table that I've added a computed column to. That column is part
of an index. As soon as I attempt to insert a row I get the infamous:
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods
I've tried creating the table with ansi_padding on and off... and with the
session doing the insert on and off...nothing seems to work...what is
causing this?
Thanks for any insight...really stuck and need to get through this to lock
down some input issues...Hi Tim
I think that the connection that you are trying to insert does not have the
correct ANSI_PADDING setting rather than anything else.
If Ansi_Padding is on when you create the index and insert everything is ok.
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING ON
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 1, 3
go
select * from TST1
go
If it is off when you try to insert but on otherwise, you get your error
message
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 2, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
If it was off when you create the table and index it will give an error
creating the index
use master
go
drop database TESTDB
go
create database TESTDB
go
use TESTDB
gO
SET ANSI_PADDING Off
go
create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
ID2 )
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
/*
Msg 1934, Level 16, State 1, Line 1
CREATE INDEX failed because the following SET options have incorrect
settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
indexed views and/or indexes on computed columns and/or query notifications
and/or xml data type methods.
*/
If it is on when creating the index you can insert if ANSI_PADDING is on:
SET ANSI_PADDING On
go
crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
go
insert into tST1 ( ID1, ID2 )
select 3, 3
go
But not if it is off
SET ANSI_PADDING Off
go
insert into tST1 ( ID1, ID2 )
select 4, 3
go
/*
Msg 1934, Level 16, State 1, Line 1
INSERT failed because the following SET options have incorrect settings:
'ANSI_PADDING'. Verify that SET options are correct for use with indexed
views and/or indexes on computed columns and/or query notifications and/or
xml data type methods.
*/
-- Checking the user options when off and on
-- When off
SELECT @.@.OPTIONS
-- 5480
SET ANSI_PADDING ON
go
SELECT @.@.OPTIONS
-- 5496
-- 16 is the USER_OPTION value to set!!
HTH
John
"Tim Greenwood" wrote:
> I've got a table that I've added a computed column to. That column is part
> of an index. As soon as I attempt to insert a row I get the infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
>
>|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
> I've got a table that I've added a computed column to. That column is
> part of an index. As soon as I attempt to insert a row I get the
> infamous:
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods
> I've tried creating the table with ansi_padding on and off... and with the
> session doing the insert on and off...nothing seems to work...what is
> causing this?
> Thanks for any insight...really stuck and need to get through this to lock
> down some input issues...
What was the ANSI_PADDING setting when you created the table and when you
later created the computed column?|||I stated in the original message I have created the table both ways...and
in each instance tried with it on /off for the session doing the insert.
"Mike C#" <xyz@.xyz.com> wrote in message
news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
>> I've got a table that I've added a computed column to. That column is
>> part of an index. As soon as I attempt to insert a row I get the
>> infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the session doing the insert on and off...nothing seems to work...what
>> is causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock down some input issues...
> What was the ANSI_PADDING setting when you created the table and when you
> later created the computed column?
>|||Hi
If you check my script out in the other reply it seems to be the session
where you do the insert.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:Ozq7VKxhIHA.5900@.TK2MSFTNGP02.phx.gbl...
>> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
>> news:u2M3gnihIHA.3788@.TK2MSFTNGP04.phx.gbl...
>> I've got a table that I've added a computed column to. That column is
>> part of an index. As soon as I attempt to insert a row I get the
>> infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the session doing the insert on and off...nothing seems to work...what
>> is causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock down some input issues...
>> What was the ANSI_PADDING setting when you created the table and when you
>> later created the computed column?
>|||"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%239lAvkDiIHA.2416@.TK2MSFTNGP04.phx.gbl...
>I stated in the original message I have created the table both ways...and
>in each instance tried with it on /off for the session doing the insert.
Ahh missed that part. So change it in your session/connection where you're
trying to perform the INSERT.|||Yes the connection was it...thank you!! I wasn't aware that the connection
had properties different from the session itself...I mean if my connection
did not have ANSI_PADDING enabled and I entered SET ANSI_PADDING ON I would
have thought that would change everything for the existing
session/connection both.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
> Hi Tim
> I think that the connection that you are trying to insert does not have
> the
> correct ANSI_PADDING setting rather than anything else.
> If Ansi_Padding is on when you create the index and insert everything is
> ok.
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING ON
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 1, 3
> go
> select * from TST1
> go
> If it is off when you try to insert but on otherwise, you get your error
> message
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 2, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> If it was off when you create the table and index it will give an error
> creating the index
> use master
> go
> drop database TESTDB
> go
> create database TESTDB
> go
> use TESTDB
> gO
> SET ANSI_PADDING Off
> go
> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1 *
> ID2 )
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> CREATE INDEX failed because the following SET options have incorrect
> settings: 'ANSI_PADDING'. Verify that SET options are correct for use with
> indexed views and/or indexes on computed columns and/or query
> notifications
> and/or xml data type methods.
> */
> If it is on when creating the index you can insert if ANSI_PADDING is on:
> SET ANSI_PADDING On
> go
> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
> go
> insert into tST1 ( ID1, ID2 )
> select 3, 3
> go
> But not if it is off
> SET ANSI_PADDING Off
> go
> insert into tST1 ( ID1, ID2 )
> select 4, 3
> go
> /*
> Msg 1934, Level 16, State 1, Line 1
> INSERT failed because the following SET options have incorrect settings:
> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
> views and/or indexes on computed columns and/or query notifications and/or
> xml data type methods.
> */
> -- Checking the user options when off and on
> -- When off
> SELECT @.@.OPTIONS
> -- 5480
> SET ANSI_PADDING ON
> go
> SELECT @.@.OPTIONS
> -- 5496
> -- 16 is the USER_OPTION value to set!!
> HTH
> John
> "Tim Greenwood" wrote:
>> I've got a table that I've added a computed column to. That column is
>> part
>> of an index. As soon as I attempt to insert a row I get the infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the
>> session doing the insert on and off...nothing seems to work...what is
>> causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock
>> down some input issues...
>>|||Hi Tim
The SET command would only set the ANSI_PADDING for the current connection
you would need to use sp_configure to set the USER_OPTIONS value, which will
change the properties for any subsequent new connection.
John
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:u7mcB1HiIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Yes the connection was it...thank you!! I wasn't aware that the
> connection had properties different from the session itself...I mean if my
> connection did not have ANSI_PADDING enabled and I entered SET
> ANSI_PADDING ON I would have thought that would change everything for the
> existing session/connection both.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:5B951B45-CAEE-4FAB-90AB-4CC90BB024DB@.microsoft.com...
>> Hi Tim
>> I think that the connection that you are trying to insert does not have
>> the
>> correct ANSI_PADDING setting rather than anything else.
>> If Ansi_Padding is on when you create the index and insert everything is
>> ok.
>> create database TESTDB
>> go
>> use TESTDB
>> gO
>> SET ANSI_PADDING ON
>> go
>> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1
>> *
>> ID2 )
>> go
>> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 1, 3
>> go
>> select * from TST1
>> go
>> If it is off when you try to insert but on otherwise, you get your error
>> message
>> SET ANSI_PADDING Off
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 2, 3
>> go
>> /*
>> Msg 1934, Level 16, State 1, Line 1
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods.
>> */
>> If it was off when you create the table and index it will give an error
>> creating the index
>> use master
>> go
>> drop database TESTDB
>> go
>> create database TESTDB
>> go
>> use TESTDB
>> gO
>> SET ANSI_PADDING Off
>> go
>> create table TST1 ( ID1 INT NOT NULL, ID2 INT NOT NULL, IDPRODUCT as id1
>> *
>> ID2 )
>> go
>> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
>> go
>> /*
>> Msg 1934, Level 16, State 1, Line 1
>> CREATE INDEX failed because the following SET options have incorrect
>> settings: 'ANSI_PADDING'. Verify that SET options are correct for use
>> with
>> indexed views and/or indexes on computed columns and/or query
>> notifications
>> and/or xml data type methods.
>> */
>> If it is on when creating the index you can insert if ANSI_PADDING is on:
>> SET ANSI_PADDING On
>> go
>> crEate index TST1_iDpRODUCT ON tst1 ( IDPRODUCT )
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 3, 3
>> go
>> But not if it is off
>> SET ANSI_PADDING Off
>> go
>> insert into tST1 ( ID1, ID2 )
>> select 4, 3
>> go
>> /*
>> Msg 1934, Level 16, State 1, Line 1
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods.
>> */
>> -- Checking the user options when off and on
>> -- When off
>> SELECT @.@.OPTIONS
>> -- 5480
>> SET ANSI_PADDING ON
>> go
>> SELECT @.@.OPTIONS
>> -- 5496
>> -- 16 is the USER_OPTION value to set!!
>> HTH
>> John
>> "Tim Greenwood" wrote:
>>
>> I've got a table that I've added a computed column to. That column is
>> part
>> of an index. As soon as I attempt to insert a row I get the infamous:
>> INSERT failed because the following SET options have incorrect settings:
>> 'ANSI_PADDING'. Verify that SET options are correct for use with indexed
>> views and/or indexes on computed columns and/or query notifications
>> and/or
>> xml data type methods
>> I've tried creating the table with ansi_padding on and off... and with
>> the
>> session doing the insert on and off...nothing seems to work...what is
>> causing this?
>> Thanks for any insight...really stuck and need to get through this to
>> lock
>> down some input issues...
>>
>