Calling Webservice: sp3a

Showing posts with label sp3a. Show all posts

Sunday, March 11, 2012

Can DTS read a file as one big blob?

(SQL Server 2000, SP3a)
Hello all!
I've got a pretty simple XML file that I'd like to process with DTS. I unde
rstand that
DTS doesn't have native capability for XML, so I was wondering if I could st
ill use DTS to
read in the entire source file and pass that into a stored procedure as a TE
XT argument?
Thanks for any help you can provide!
John PetersonHi John,
Not sure what you need to accomplish or what exactly process
the file means but with XML files, another option is to use
SQL Server 2000 Web Services Toolkit or just install SQLXML.
You can use the SQL XML Bulk Load object model to import XML
files. You could write this in an ActiveX script task in
DTS.
-Sue
On Sat, 13 Mar 2004 18:46:48 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>I've got a pretty simple XML file that I'd like to process with DTS. I und
erstand that
>DTS doesn't have native capability for XML, so I was wondering if I could s
till use DTS to
>read in the entire source file and pass that into a stored procedure as a T
EXT argument?
>Thanks for any help you can provide!
>John Peterson
>|||Thanks Sue!
Is the SQL XML Bulk Load object model part of the SQLXML package? I'm not w
holly familiar
with SQLXML -- my impression is that it's an add-on to SQL Server, and I kno
w the initial
reaction to our Hosting department will be to understand *why* it's necessar
y for us to
install that on the Production servers. But, if it's the right thing to do,
they'll
oblige.
I had thought to easily leverage some of the built-in XML features of SQL Se
rver, and I
think I could get DTS to invoke a SP that does some manipulation with OPENXM
L(), but I
wasn't sure how to get DTS to potentially squirt the contents of the XML fil
e into a NTEXT
parameter to the SP.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:8idb50lj18hs2hopcvr11c5inrfs0gns2d@.
4ax.com...
> Hi John,
> Not sure what you need to accomplish or what exactly process
> the file means but with XML files, another option is to use
> SQL Server 2000 Web Services Toolkit or just install SQLXML.
> You can use the SQL XML Bulk Load object model to import XML
> files. You could write this in an ActiveX script task in
> DTS.
> -Sue
> On Sat, 13 Mar 2004 18:46:48 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
to
argument?
>|||Yup...the SQLXML bulk load object is part of SQLXML. It's
an add-on or additional component to install. I've used
SQLXML Bulk Load in ActiveX scripts to import XML files into
the relational tables.
The SQLXML OLEDB provider that comes with it also supports
inserting XML data - but I haven't messed with it and don't
know much about it other than it exposes the functionality
through ADO. There is also a managed provider to work with
.Net
The help files that come with it are pretty good and have
examples, samples that can be used to test out
functionality. You may want to download and install it on
your PC to see if it works for you. Excuse the long link and
watch out for line wrap:
http://www.microsoft.com/downloads/...&displaylang=en
-Sue
On Mon, 15 Mar 2004 08:53:49 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>Thanks Sue!
>Is the SQL XML Bulk Load object model part of the SQLXML package? I'm not
wholly familiar
>with SQLXML -- my impression is that it's an add-on to SQL Server, and I kn
ow the initial
>reaction to our Hosting department will be to understand *why* it's necessa
ry for us to
>install that on the Production servers. But, if it's the right thing to do
, they'll
>oblige.
>I had thought to easily leverage some of the built-in XML features of SQL S
erver, and I
>think I could get DTS to invoke a SP that does some manipulation with OPENX
ML(), but I
>wasn't sure how to get DTS to potentially squirt the contents of the XML fi
le into a NTEXT
>parameter to the SP.
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:8idb50lj18hs2hopcvr11c5inrfs0gns2d@.
4ax.com...
>to
>argument?
>

can dts "invert" data

sql2k sp3a
Dont know if "invert" is the right word, but the only one I could come up
with. I have a flat file that displays data like:
GEN_PROREGCTCTXT CRZCODE S_GENERATION
1 CB 1
1 CS 1
That top row is data, not column names FYI. Anyways, what I need to do is
get that file into a table but I need it accross, not down. Meaning that the
rows 2 and 3 from column 1 will actually go into columns 2 and 3 in row 1.
It needs to look like:
GEN_PROREGCTCTXT 1 1
CRZCODE CB CS
S_GENERATION 1 1
Is there a way for DTS to handle something like this? Another good way to do
it?
TIA, ChrisRImport it into SQLServer the way it is, then look up how to code a
"pivot" query to turn it over.
J.
On Thu, 25 Aug 2005 13:28:50 -0700, "ChrisR" <noemail@.bla.com> wrote:
>sql2k sp3a
>Dont know if "invert" is the right word, but the only one I could come up
>with. I have a flat file that displays data like:
>GEN_PROREGCTCTXT CRZCODE S_GENERATION
>1 CB 1
>1 CS 1
>That top row is data, not column names FYI. Anyways, what I need to do is
>get that file into a table but I need it accross, not down. Meaning that the
>rows 2 and 3 from column 1 will actually go into columns 2 and 3 in row 1.
>It needs to look like:
>GEN_PROREGCTCTXT 1 1
>CRZCODE CB CS
>S_GENERATION 1 1
>Is there a way for DTS to handle something like this? Another good way to do
>it?
>TIA, ChrisR
>

Thursday, March 8, 2012

can dbcc showcontig slow things down/ lock tables?

sql2k sp3a
Not really any more to ask.
TIA, ChrisRYes. Shared locks are put up when you run it. Try using WITH FAST and do
only one table at a time. You get less info, but it's the stuff you need.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"ChrisR" <noemail@.bla.com> wrote in message
news:OJY7r9BqFHA.2588@.TK2MSFTNGP12.phx.gbl...
sql2k sp3a
Not really any more to ask.
TIA, ChrisR

Wednesday, March 7, 2012

Can BULK INSERT be used like TEXTCOPY?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct. BULK
INSERT looked close, but it also looks like the built-in analogue to BCP. That is, it
processes the contents of the file, and I really just want to squirt the file in to the
column verbatim.
Thanks for any help you can provide!
John PetersonYes. You can use bulk insert but you need a format file.
e.g.
1 SQLIMAGE 0 999999 "" 2 coln ""
1= entire file
SQLIMAGE= datatype
0= prefix length
999999= file length/size
""= no terminator
2= column ordinal
coln= column name
""= no collation
Thus, the bulk insert looks like this:
bulk insert tb
from 'file.img'
with(formatfile='fmt.fmt')
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of
files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I
could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL
construct. BULK
> INSERT looked close, but it also looks like the built-in analogue to BCP.
That is, it
> processes the contents of the file, and I really just want to squirt the
file in to the
> column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
See this thread for an example:
http://groups.google.com/groups?q=405F-B2C5-7256A4B9870A
Steve Kass
Drew University
John Peterson wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
>to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct. BULK
>INSERT looked close, but it also looks like the built-in analogue to BCP. That is, it
>processes the contents of the file, and I really just want to squirt the file in to the
>column verbatim.
>Thanks for any help you can provide!
>John Peterson
>
>|||Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
BTW: Do you need an *exact* binary file size in the format file, as Steve's link
suggests? Or will "oversizing" it suffice?
Thanks again!
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
> BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That is,
> it processes the contents of the file, and I really just want to squirt the file in to
> the column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
It's got to be exact. If you oversize it, you get an unexpected
end-of-file:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
SK
John Peterson wrote:
>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>suggests? Or will "oversizing" it suffice?
>Thanks again!
>
>"John Peterson" <j0hnp@.comcast.net> wrote in message
>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
>>to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
>>BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That is,
>>it processes the contents of the file, and I really just want to squirt the file in to
>>the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>
>|||Thanks, Steve!
One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
I have very little experiencing in using VBScript (this is going to be called from a Web
page) and ADO Parameter objects for an IMAGE data. I've been searching on the Web, and
I've seen a few snippets -- but not a comprehensive example. Do you have any
recommendations/links on that route, perchance?
"Steve Kass" <skass@.drew.edu> wrote in message
news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
> John,
> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any information
> about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
> The provider did not give any information about the error.].
> The statement has been terminated.
> SK
> John Peterson wrote:
>>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>>suggests? Or will "oversizing" it suffice?
>>Thanks again!
>>
>>"John Peterson" <j0hnp@.comcast.net> wrote in message
>>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell"
>>out to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
>>BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That
>>is, it processes the contents of the file, and I really just want to squirt the file in
>>to the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>>|||John,
I haven't done this myself, but you should be able to pass the image
data with a adLongVarBinary parameter.
You might need to create the parameter 1 byte longer than the length of
the data you're storing, according to
http://support.microsoft.com/default.aspx?scid=kb;en-us;190450
I also saw one suggestion that this meant you have to read back all but
the last byte of the stored image
value when retrieving the data, so you should be watchful:
http://groups.google.com/groups?hl=en&lr=&safe=off&threadm=uMwa0SoD%24GA.235%40cppssbbsa02.microsoft.com&rnum=6&prev=/groups%3Fhl%3Den%26lr%3D%26safe%3Doff%26q%3Dsqlserver%2Bstored%2Bprocedure%2Bimage%2Badlongvarbinary
SK
John Peterson wrote:
>Thanks, Steve!
>One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
>I have very little experiencing in using VBScript (this is going to be called from a Web
>page) and ADO Parameter objects for an IMAGE data. I've been searching on the Web, and
>I've seen a few snippets -- but not a comprehensive example. Do you have any
>recommendations/links on that route, perchance?
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>
>>John,
>> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
>>Server: Msg 4832, Level 16, State 1, Line 1
>>Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
>>Server: Msg 7399, Level 16, State 1, Line 1
>>OLE DB provider 'STREAM' reported an error. The provider did not give any information
>>about the error.
>>OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
>>The provider did not give any information about the error.].
>>The statement has been terminated.
>>SK
>>John Peterson wrote:
>>
>>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>>suggests? Or will "oversizing" it suffice?
>>Thanks again!
>>
>>"John Peterson" <j0hnp@.comcast.net> wrote in message
>>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>>
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell"
>>out to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
>>BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That
>>is, it processes the contents of the file, and I really just want to squirt the file in
>>to the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>>
>>
>
>|||Great stuff! Thanks again, Steve! :-)
"Steve Kass" <skass@.drew.edu> wrote in message
news:eXQUuyjvEHA.2804@.TK2MSFTNGP14.phx.gbl...
> John,
> I haven't done this myself, but you should be able to pass the image data with a
> adLongVarBinary parameter.
> You might need to create the parameter 1 byte longer than the length of the data you're
> storing, according to
> http://support.microsoft.com/default.aspx?scid=kb;en-us;190450
> I also saw one suggestion that this meant you have to read back all but the last byte of
> the stored image
> value when retrieving the data, so you should be watchful:
> http://groups.google.com/groups?hl=en&lr=&safe=off&threadm=uMwa0SoD%24GA.235%40cppssbbsa02.microsoft.com&rnum=6&prev=/groups%3Fhl%3Den%26lr%3D%26safe%3Doff%26q%3Dsqlserver%2Bstored%2Bprocedure%2Bimage%2Badlongvarbinary
> SK
>
> John Peterson wrote:
>>Thanks, Steve!
>>One other option I'm exploring is using a stored procedure with an IMAGE parameter.
>>But, I have very little experiencing in using VBScript (this is going to be called from
>>a Web page) and ADO Parameter objects for an IMAGE data. I've been searching on the
>>Web, and I've seen a few snippets -- but not a comprehensive example. Do you have any
>>recommendations/links on that route, perchance?
>>
>>"Steve Kass" <skass@.drew.edu> wrote in message
>>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>>John,
>> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
>>Server: Msg 4832, Level 16, State 1, Line 1
>>Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
>>Server: Msg 7399, Level 16, State 1, Line 1
>>OLE DB provider 'STREAM' reported an error. The provider did not give any information
>>about the error.
>>OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
>>The provider did not give any information about the error.].
>>The statement has been terminated.
>>SK
>>John Peterson wrote:
>>
>>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward.
>>:-)
>>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>>suggests? Or will "oversizing" it suffice?
>>Thanks again!
>>
>>"John Peterson" <j0hnp@.comcast.net> wrote in message
>>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>>
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on
>>disk), and I'd like to squirt them in to a table that has an IMAGE column. I could
>>"shell" out to use TEXTCOPY, but was wondering if I could leverage some built-in SQL
>>construct. BULK INSERT looked close, but it also looks like the built-in analogue to
>>BCP. That is, it processes the contents of the file, and I really just want to
>>squirt the file in to the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>>
>>

Can BULK INSERT be used like TEXTCOPY?

Sunday, February 19, 2012

Can a trigger determine INSERT/UPDATE context?

(SQL Server 2000, SP3a)
Hello all!
I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it determine
whether the action that fired the trigger was an INSERT versus an UPDATE? I've got a
little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I
need to have two separate triggers, and then potentially call out to a third SP that has
the "guts" of the current trigger?
Thanks!
John PetersonThis is a multi-part message in MIME format.
--=_NextPart_000_04DA_01C3B8E9.27FD2C60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
If your trigger handles only inserts and updates, then you will have rows in
inserted but not in deleted when there is an insert. However, if there is
an update, then both will be populated:
if @.@.ROWCOUNT = 0
return
if exists (select * from deleted)
begin
-- do the update processing
end
else
begin
-- do the insert processing
end
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
(SQL Server 2000, SP3a)
Hello all!
I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can
it determine
whether the action that fired the trigger was an INSERT versus an UPDATE?
I've got a
little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
need to have two separate triggers, and then potentially call out to a third
SP that has
the "guts" of the current trigger?
Thanks!
John Peterson
--=_NextPart_000_04DA_01C3B8E9.27FD2C60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

If your trigger handles only inserts =and updates, then you will have rows in inserted but not in deleted when there is an insert. However, if there is an update, then both will be populated:
if @.@.ROWCOUNT =3D =0
=return
if exists (select * from deleted)
begin
-- do the =update processing
end
else
begin
-- do the =insert processing
end
go
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...(SQL Server 2000, SP3a)Hello all!I was wondering if, within a =trigger that's defined FOR INSERT, UPDATE, can it determinewhether the =action that fired the trigger was an INSERT versus an UPDATE? I've got =alittle "extra" logic to do for an UPDATE that I want to avoid with an =INSERT. Or do Ineed to have two separate triggers, and then potentially call =out to a third SP that hasthe "guts" of the current trigger?Thanks!John Peterson

--=_NextPart_000_04DA_01C3B8E9.27FD2C60--|||Sure, compare (a) the count(*) from inserted with (b) the count(*) from
deleted.
if (a) = 0 and (b) > 0, it's a delete
if (a) > 0 and (b) = 0, it's an insert
if (a) > 0 and (b) > 0, it's an update
(Not sure if (a)=0 and (b)=0 is possible, but this would mean that the
trigger was fired for nothing, e.g. 0 row(s) affected.)
See http://www.aspfaq.com/2496 for an example of control flow in a trigger,
based on the event.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I was wondering if, within a trigger that's defined FOR INSERT, UPDATE,
can it determine
> whether the action that fired the trigger was an INSERT versus an UPDATE?
I've got a
> little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> need to have two separate triggers, and then potentially call out to a
third SP that has
> the "guts" of the current trigger?
> Thanks!
> John Peterson
>|||Ah, thanks guys -- I hadn't considered using those tables! I was trying to fiddle with
the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an INSERT is invoked,
that function will return TRUE. However, I'm getting a syntax error when I try to use it
like that:
...
if ((not update(MySpecificCol)) or (columns_updated())) begin
...
end
...
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it determine
> whether the action that fired the trigger was an INSERT versus an UPDATE? I've got a
> little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I
> need to have two separate triggers, and then potentially call out to a third SP that has
> the "guts" of the current trigger?
> Thanks!
> John Peterson
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0510_01C3B8EB.34932630
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
The columns_updated() function requires an argument.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
Ah, thanks guys -- I hadn't considered using those tables! I was trying to
fiddle with
the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an
INSERT is invoked,
that function will return TRUE. However, I'm getting a syntax error when I
try to use it
like that:
...
if ((not update(MySpecificCol)) or (columns_updated())) begin
...
end
...
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> I was wondering if, within a trigger that's defined FOR INSERT, UPDATE,
can it determine
> whether the action that fired the trigger was an INSERT versus an UPDATE?
I've got a
> little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> need to have two separate triggers, and then potentially call out to a
third SP that has
> the "guts" of the current trigger?
> Thanks!
> John Peterson
>
--=_NextPart_000_0510_01C3B8EB.34932630
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

The columns_updated() function =requires an argument.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle withthe COLUMNS_UPDATED() function, as BOL seemed to indicate =that when an INSERT is invoked,that function will return TRUE. =However, I'm getting a syntax error when I try to use itlike =that:...if ((not update(MySpecificCol)) or (columns_updated())) begin ...end..."John Peterson" wrote in =messagenews:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> (SQL Server 2000, SP3a)>> Hello all!>> I was =wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it =determine> whether the action that fired the trigger was an =INSERT versus an UPDATE? I've got a> little "extra" logic to do for an =UPDATE that I want to avoid with an INSERT. Or do I> need to have =two separate triggers, and then potentially call out to a third SP that =has> the "guts" of the current trigger?>> =Thanks!>> John Peterson>>

--=_NextPart_000_0510_01C3B8EB.34932630--|||I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the
columns have either explicit values or implicit (NULL) values inserted.
</Quote>
But, as I say, I can't quite get that function to work in this context. Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT context? I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was trying to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error when I try to use
it
> like that:
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus an UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I
> > need to have two separate triggers, and then potentially call out to a third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0537_01C3B8EC.A1EC5AC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
What's wrong with the method I posted?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions
because the
columns have either explicit values or implicit (NULL) values inserted.
</Quote>
But, as I say, I can't quite get that function to work in this context.
Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT context?
I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was trying
to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an
INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error when
I try to use
it
> like that:
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, UPDATE,
can it
determine
> > whether the action that fired the trigger was an INSERT versus an
UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> > need to have two separate triggers, and then potentially call out to a
third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
--=_NextPart_000_0537_01C3B8EC.A1EC5AC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

What's wrong with the method I posted?
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because =thecolumns have either explicit values or implicit (NULL) values inserted.But, as I say, I can't quite get that =function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I wonder ifthere's a quick/easy way to determine that?"John =Peterson" wrote =in messagenews:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with> the COLUMNS_UPDATED() function, as BOL seemed to =indicate that when an INSERT isinvoked,> that function will return =TRUE. However, I'm getting a syntax error when I try to useit> like =that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) begin> ...> =end> ...>>> "John Peterson" wrote in message> news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> > (SQL Server 2000, SP3a)> >> > Hello =all!> >> > I was wondering if, within a trigger that's defined =FOR INSERT, UPDATE, can itdetermine> > whether the action that =fired the trigger was an INSERT versus an UPDATE? I've got a> => little "extra" logic to do for an UPDATE that I want to avoid with an =INSERT. Or do I> > need to have two separate triggers, and then =potentially call out to a third SP thathas> > the "guts" of the current trigger?> >> > Thanks!> >> > =John Peterson> >> >>>

--=_NextPart_000_0537_01C3B8EC.A1EC5AC0--|||This is a multi-part message in MIME format.
--=_NextPart_000_00D6_01C3B8DC.C739DD80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom! There's nothing wrong with the method that you posted -- I =guess I was just using this as an exercise to learn more about the =COLUMNS_UPDATED() function, and whether using that might be more =performant. :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uBwQaaRuDHA.2360@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT =actions because the
columns have either explicit values or implicit (NULL) values =inserted.
</Quote>
But, as I say, I can't quite get that function to work in this =context. Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT =context? I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when =an INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error =when I try to use
it
> like that:
>
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, =UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus an =UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with =an INSERT. Or do I
> > need to have two separate triggers, and then potentially call out =to a third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
>
--=_NextPart_000_00D6_01C3B8DC.C739DD80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Thanks Tom! There's nothing =wrong with the method that you posted -- I guess I was just using this as an exercise =to learn more about the COLUMNS_UPDATED() function, and whether using that might =be more performant. :-)
"Tom Moreau" = wrote in message news:uBwQaaRuDHA.2360=@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because =thecolumns have either explicit values or implicit (NULL) values inserted.But, as I say, I can't quite get =that function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I =wonder ifthere's a quick/easy way to determine that?"John =Peterson" =wrote in messagenews:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with> the COLUMNS_UPDATED() function, as BOL seemed =to indicate that when an INSERT isinvoked,> that function will =return TRUE. However, I'm getting a syntax error when I try to useit> like that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) =begin> ...> end> ...>>> "John Peterson" = wrote in message> news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> > (SQL Server 2000, SP3a)> >> > Hello =all!> >> > I was wondering if, within a trigger that's defined =FOR INSERT, UPDATE, can itdetermine> > whether the action =that fired the trigger was an INSERT versus an UPDATE? I've got a> => little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I> > need to have two separate triggers, =and then potentially call out to a third SP thathas> > the ="guts" of the current trigger?> >> > Thanks!> =>> > John Peterson> >> >>>

--=_NextPart_000_00D6_01C3B8DC.C739DD80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0579_01C3B8EE.E25FEB10
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change the
ordinal position of various columns, then your trigger code will have to
change. Often, developers forget to update the trigger when that happens.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:uONX2dRuDHA.2448@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing wrong with the method that you posted -- I
guess I was just using this as an exercise to learn more about the
COLUMNS_UPDATED() function, and whether using that might be more performant.
:-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uBwQaaRuDHA.2360@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT
actions because the
columns have either explicit values or implicit (NULL) values inserted.
</Quote>
But, as I say, I can't quite get that function to work in this context.
Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT context?
I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was trying
to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that when an
INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax error
when I try to use
it
> like that:
>
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT,
UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus an
UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid with an
INSERT. Or do I
> > need to have two separate triggers, and then potentially call out to a
third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
>
--=_NextPart_000_0579_01C3B8EE.E25FEB10
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change the ordinal position of =various columns, then your trigger code will have to change. Often, =developers forget to update the trigger when that happens.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"John Peterson" wrote in =message news:uONX2dRuDHA.2448=@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing =wrong with the method that you posted -- I guess I was just using this as an exercise =to learn more about the COLUMNS_UPDATED() function, and whether using that might =be more performant. :-)
"Tom Moreau" = wrote in message news:uBwQaaRuDHA.2360=@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because =thecolumns have either explicit values or implicit (NULL) values inserted.But, as I say, I can't quite get =that function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I =wonder ifthere's a quick/easy way to determine that?"John =Peterson" =wrote in messagenews:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with> the COLUMNS_UPDATED() function, as BOL seemed =to indicate that when an INSERT isinvoked,> that function will =return TRUE. However, I'm getting a syntax error when I try to useit> like that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) =begin> ...> end> ...>>> "John Peterson" = wrote in message> news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> > (SQL Server 2000, SP3a)> >> > Hello =all!> >> > I was wondering if, within a trigger that's defined =FOR INSERT, UPDATE, can itdetermine> > whether the action =that fired the trigger was an INSERT versus an UPDATE? I've got a> => little "extra" logic to do for an UPDATE that I want to avoid with an INSERT. Or do I> > need to have two separate triggers, =and then potentially call out to a third SP thathas> > the ="guts" of the current trigger?> >> > Thanks!> =>> > John Peterson> >> >>>

--=_NextPart_000_0579_01C3B8EE.E25FEB10--|||This is a multi-part message in MIME format.
--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Understood -- I had hoped there would be a quick and easy mechanism to =essentially create a bitmask that was representative of all the columns =in the table (without regard to the position of the columns). Then, =simply compare this value with the COLUMNS_UPDATED() value. But, I =think something like that would be far more onerous than your (and =Aaron's) recommendation. :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:ebVwVjRuDHA.2308@.TK2MSFTNGP09.phx.gbl...
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change =the ordinal position of various columns, then your trigger code will =have to change. Often, developers forget to update the trigger when =that happens.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:uONX2dRuDHA.2448@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing wrong with the method that you posted -- =I guess I was just using this as an exercise to learn more about the =COLUMNS_UPDATED() function, and whether using that might be more =performant. :-)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uBwQaaRuDHA.2360@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"John Peterson" <j0hnp@.comcast.net> wrote in message =news:e8d2sWRuDHA.536@.tk2msftngp13.phx.gbl...
I meant to include the BOL snippet:
<Quote>
COLUMNS_UPDATED will return the TRUE value for all columns in INSERT =actions because the
columns have either explicit values or implicit (NULL) values =inserted.
</Quote>
But, as I say, I can't quite get that function to work in this =context. Unless I should
just be reading this as all "bits" will be on (TRUE) in an INSERT =context? I wonder if
there's a quick/easy way to determine that?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eP1juRRuDHA.1876@.TK2MSFTNGP09.phx.gbl...
> Ah, thanks guys -- I hadn't considered using those tables! I was =trying to fiddle with
> the COLUMNS_UPDATED() function, as BOL seemed to indicate that =when an INSERT is
invoked,
> that function will return TRUE. However, I'm getting a syntax =error when I try to use
it
> like that:
>
> ...
> if ((not update(MySpecificCol)) or (columns_updated())) begin
> ...
> end
> ...
>
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:uK1T8IRuDHA.3436@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > I was wondering if, within a trigger that's defined FOR INSERT, =UPDATE, can it
determine
> > whether the action that fired the trigger was an INSERT versus =an UPDATE? I've got a
> > little "extra" logic to do for an UPDATE that I want to avoid =with an INSERT. Or do I
> > need to have two separate triggers, and then potentially call =out to a third SP that
has
> > the "guts" of the current trigger?
> >
> > Thanks!
> >
> > John Peterson
> >
> >
>
>
--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Understood -- I had hoped there =would be a quick and easy mechanism to essentially create a bitmask that was =representative of all the columns in the table (without regard to the position of the columns). Then, simply compare this value with the =COLUMNS_UPDATED() value. But, I think something like that would be far more onerous =than your (and Aaron's) recommendation. :-)
"Tom Moreau" = wrote in message news:ebVwVjRuDHA.2308=@.TK2MSFTNGP09.phx.gbl...
Well, I'm not a big fan of COLUMNS_UPDATED(). If you decide to change the ordinal position =of various columns, then your trigger code will have to change. =Often, developers forget to update the trigger when that =happens.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:uONX2dRuDHA.2448=@.TK2MSFTNGP12.phx.gbl...
Thanks Tom! There's nothing =wrong with the method that you posted -- I guess I was just using this as an =exercise to learn more about the COLUMNS_UPDATED() function, and whether using =that might be more performant. :-)
"Tom Moreau" = wrote in message news:uBwQaaRuDHA.2360=@.TK2MSFTNGP10.phx.gbl...
What's wrong with the method I posted?
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql

"John Peterson" wrote in =message news:e8d2sWRuDHA.536@.t=k2msftngp13.phx.gbl...I meant to include the BOL =snippet:COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because = thecolumns have either explicit values or implicit (NULL) values = inserted.But, as I say, I can't quite get =that function to work in this context. Unless I shouldjust be =reading this as all "bits" will be on (TRUE) in an INSERT context? I =wonder ifthere's a quick/easy way to determine that?"John =Peterson" =wrote in messagenews:eP1juRRuDHA.1876=@.TK2MSFTNGP09.phx.gbl...> Ah, thanks guys -- I hadn't considered using those tables! I =was trying to fiddle with> the COLUMNS_UPDATED() function, as BOL =seemed to indicate that when an INSERT isinvoked,> that function =will return TRUE. However, I'm getting a syntax error when I try to = useit> like that:>> ...> if ((not update(MySpecificCol)) or (columns_updated())) begin> ...> end> ...>>> "John Peterson" wrote in = message> news:uK1T8IRuDHA.3436=@.tk2msftngp13.phx.gbl...> > (SQL Server 2000, SP3a)> >> > Hello =all!> >> > I was wondering if, within a trigger that's =defined FOR INSERT, UPDATE, can itdetermine> > whether the action =that fired the trigger was an INSERT versus an UPDATE? I've got =a> > little "extra" logic to do for an UPDATE that I want to avoid =with an INSERT. Or do I> > need to have two separate =triggers, and then potentially call out to a third SP thathas> > the ="guts" of the current trigger?> >> > Thanks!> >> > John Peterson> >> >>>

--=_NextPart_000_00F0_01C3B8E2.6B0B2CC0--

Tuesday, February 14, 2012

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure
A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name o
f the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it f
rom stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this
be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor =
@.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly some
thing with the
[sp_executesql] line. No amount of massaging will get this to work -- my gu
ess is that
the structure of [sp_executesql] won't permit a cursor variable to be handle
d. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in m
y case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the V
ARYING
keyword. From the stored procedure documentation (which I kind of assumed t
hat
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was nece
ssary when
using a cursor variable. But, it appears not to be the case, and that was t
he one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing dat
a
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the curso
r variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of th
e dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it compla
ins that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the o
riginal "By
Name" and the variable) -- and I need to essentially close both handles befo
re the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in
my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the
VARYING
> keyword. From the stored procedure documentation (which I kind of assumed
that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was ne
cessary when
> using a cursor variable. But, it appears not to be the case, and that was
the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor
output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could b
e found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cur
sor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dyna
mic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it comp
lains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the
original "By
> Name" and the variable) -- and I need to essentially close both handles before the

cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
I'm
>

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name of the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it from stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor = @.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly something with the
[sp_executesql] line. No amount of massaging will get this to work -- my guess is that
the structure of [sp_executesql] won't permit a cursor variable to be handled. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
--
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the VARYING
keyword. From the stored procedure documentation (which I kind of assumed that
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
using a cursor variable. But, it appears not to be the case, and that was the one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing data
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the cursor variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of the dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it complains that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the original "By
Name" and the variable) -- and I need to essentially close both handles before the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the VARYING
> keyword. From the stored procedure documentation (which I kind of assumed that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> using a cursor variable. But, it appears not to be the case, and that was the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > Cursors are usually best avoided because of their performance/resource
> > implications. Erland has an article on alternative methods for sharing data
> > between SPs:
> >
> > http://www.sommarskog.se/share_data.html
> >
> > For completeness, here's an amended version of your code:
> > ...
> > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > ...
> >
> > Now reference the cursor by variable (@.cursor).
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could be found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cursor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dynamic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it complains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the original "By
> Name" and the variable) -- and I need to essentially close both handles before the
cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> > David,
> >
> > You're right -- I appreciate that cursors aren't wholly performant, but in my case,
I'm
> > writing a management procedure that lends itself well to using cursors.
> >
> > Thanks for the link on other techniques for sharing data. :-)
> >
> > I think you solved my issue! From what I can tell, you merely removed the VARYING
> > keyword. From the stored procedure documentation (which I kind of assumed that
> > [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> > using a cursor variable. But, it appears not to be the case, and that was the one
> > combination I *didn't* try!
> >
> > Thanks so much! :-)
> >
> > John Peterson
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > > Cursors are usually best avoided because of their performance/resource
> > > implications. Erland has an article on alternative methods for sharing data
> > > between SPs:
> > >
> > > http://www.sommarskog.se/share_data.html
> > >
> > > For completeness, here's an amended version of your code:
> > > ...
> > > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > > ...
> > >
> > > Now reference the cursor by variable (@.cursor).
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> >
> >
>

Sunday, March 11, 2012

Can DTS read a file as one big blob?

can dts "invert" data

Thursday, March 8, 2012

can dbcc showcontig slow things down/ lock tables?

Wednesday, March 7, 2012

Can BULK INSERT be used like TEXTCOPY?

Can BULK INSERT be used like TEXTCOPY?

Can BULK INSERT be used like TEXTCOPY?

Sunday, February 19, 2012

Can a trigger determine INSERT/UPDATE context?

Tuesday, February 14, 2012

Can a cursor variable be assigned to a dynamically named cursor?

Can a cursor variable be assigned to a dynamically named cursor?

Calling Webservice

Blog Archive

About Me