There is some option in Sql Server 2000 to set of avoid errors when a text
larger than destination field is stored in it ?
For example i must do an INSERT INTO NAMES
and NAMES have a field of size 10
if i store a field of size 20 in it the server give me an error
can i avoid this error and store only the firsts 10 bytes in the destination
field?
thanks a lot.Look in BOL for SET ANSI_WARNINGS with the example
"Romano Benedetto" <RomanBe@.tin.it> schrieb im Newsbeitrag
news:UYtee.1336790$35.49871941@.news4.tin.it...
> There is some option in Sql Server 2000 to set of avoid errors when a text
> larger than destination field is stored in it ?
> For example i must do an INSERT INTO NAMES
> and NAMES have a field of size 10
> if i store a field of size 20 in it the server give me an error
> can i avoid this error and store only the firsts 10 bytes in the
> destination field?
> thanks a lot.
>|||Look in BOL for SET ANSI_WARNINGS with the example
PRINT 'Testing String Overflow in INSERT'
GO
INSERT INTO T1 VALUES (4, 4, 'Text string longer than 20 characters')
GO
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Romano Benedetto" <RomanBe@.tin.it> schrieb im Newsbeitrag
news:UYtee.1336790$35.49871941@.news4.tin.it...
> There is some option in Sql Server 2000 to set of avoid errors when a text
> larger than destination field is stored in it ?
> For example i must do an INSERT INTO NAMES
> and NAMES have a field of size 10
> if i store a field of size 20 in it the server give me an error
> can i avoid this error and store only the firsts 10 bytes in the
> destination field?
> thanks a lot.
>
Showing posts with label avoid. Show all posts
Showing posts with label avoid. Show all posts
Saturday, February 25, 2012
Avoiding Temporary Tables
Hello All,
I have a question about how I might avoid using a temporary table in a sql
query. In a query I am writing I am trying to extract data from a table o
n
an existing database via a set of identifier codes (These queries are writte
n
from the Matlab environment and the list of identifiers are easily accesible
in matlab).
The query I am currently using looks something like this...
select t.var1, t.code, t.var2
from pinf t
join
( SELECT distinct s.pcode as code
FROM prc s
WHERE s.code in (74156, 54471) ) -- identifiers 74156, 54471
as prctable on prctable.code = t.code
and '12/06/2005' >= t.dt1 and t.d2 <= '12/06/2005'
with the identifier being 74156, 54471. The problem is there is not a
convenient table with all of the identifiers in it. So the solution above
where I draw from an existing table is not sufficient.
I believe that I can create a temporary table with these identifiers in it,
but the performance of the query slows markedly.
Is there some other way to extract the data with these identifier from the
table pinf without creating a temporary table?
Any help would be greatly appreciated.
Cheers,
Lanny>> ... with the identifier being 74156, 54471.
Is this a set of two identifier values or a single comma separated
identifier value with two numbers in them?
I am not familiar with the matlab environment, but how exactly do you
extract the identifiers to the #temp table? The general alternatives ( which
in many cases may not be sufficient ) are using a view or even a base table
which can have all the required identifiers.
Anith|||It is a set of 2 distinct identifier, and conceptually I would want to look
for many more than 2 (perhaps on the order of thousands) of these distinct
identifiers.
I extract the identifiers via another query that I know functions properly
and will give me the desired identifier. I looked into using a base table
or a view, and did not believe they provided the functionality I needed.
Essentially, I have some list of numbers and I want to extract that list, bu
t
all of the elements of this list do not exist on any distinct table in the
database.
Thanks so much for the help.
"Anith Sen" wrote:
> Is this a set of two identifier values or a single comma separated
> identifier value with two numbers in them?
>
> I am not familiar with the matlab environment, but how exactly do you
> extract the identifiers to the #temp table? The general alternatives ( whi
ch
> in many cases may not be sufficient ) are using a view or even a base tabl
e
> which can have all the required identifiers.
> --
> Anith
>
>|||On Wed, 7 Dec 2005 10:18:03 -0800, Lanny wrote:
>It is a set of 2 distinct identifier, and conceptually I would want to look
>for many more than 2 (perhaps on the order of thousands) of these distinct
>identifiers.
>I extract the identifiers via another query that I know functions properly
>and will give me the desired identifier. I looked into using a base table
>or a view, and did not believe they provided the functionality I needed.
>Essentially, I have some list of numbers and I want to extract that list, b
ut
>all of the elements of this list do not exist on any distinct table in the
>database.
>Thanks so much for the help.
Hi Lanny,
I'm not sure if I understand your requirements completely, but based on
what I do understand, you might find what you need on Erlands site:
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I have a question about how I might avoid using a temporary table in a sql
query. In a query I am writing I am trying to extract data from a table o
n
an existing database via a set of identifier codes (These queries are writte
n
from the Matlab environment and the list of identifiers are easily accesible
in matlab).
The query I am currently using looks something like this...
select t.var1, t.code, t.var2
from pinf t
join
( SELECT distinct s.pcode as code
FROM prc s
WHERE s.code in (74156, 54471) ) -- identifiers 74156, 54471
as prctable on prctable.code = t.code
and '12/06/2005' >= t.dt1 and t.d2 <= '12/06/2005'
with the identifier being 74156, 54471. The problem is there is not a
convenient table with all of the identifiers in it. So the solution above
where I draw from an existing table is not sufficient.
I believe that I can create a temporary table with these identifiers in it,
but the performance of the query slows markedly.
Is there some other way to extract the data with these identifier from the
table pinf without creating a temporary table?
Any help would be greatly appreciated.
Cheers,
Lanny>> ... with the identifier being 74156, 54471.
Is this a set of two identifier values or a single comma separated
identifier value with two numbers in them?
I am not familiar with the matlab environment, but how exactly do you
extract the identifiers to the #temp table? The general alternatives ( which
in many cases may not be sufficient ) are using a view or even a base table
which can have all the required identifiers.
Anith|||It is a set of 2 distinct identifier, and conceptually I would want to look
for many more than 2 (perhaps on the order of thousands) of these distinct
identifiers.
I extract the identifiers via another query that I know functions properly
and will give me the desired identifier. I looked into using a base table
or a view, and did not believe they provided the functionality I needed.
Essentially, I have some list of numbers and I want to extract that list, bu
t
all of the elements of this list do not exist on any distinct table in the
database.
Thanks so much for the help.
"Anith Sen" wrote:
> Is this a set of two identifier values or a single comma separated
> identifier value with two numbers in them?
>
> I am not familiar with the matlab environment, but how exactly do you
> extract the identifiers to the #temp table? The general alternatives ( whi
ch
> in many cases may not be sufficient ) are using a view or even a base tabl
e
> which can have all the required identifiers.
> --
> Anith
>
>|||On Wed, 7 Dec 2005 10:18:03 -0800, Lanny wrote:
>It is a set of 2 distinct identifier, and conceptually I would want to look
>for many more than 2 (perhaps on the order of thousands) of these distinct
>identifiers.
>I extract the identifiers via another query that I know functions properly
>and will give me the desired identifier. I looked into using a base table
>or a view, and did not believe they provided the functionality I needed.
>Essentially, I have some list of numbers and I want to extract that list, b
ut
>all of the elements of this list do not exist on any distinct table in the
>database.
>Thanks so much for the help.
Hi Lanny,
I'm not sure if I understand your requirements completely, but based on
what I do understand, you might find what you need on Erlands site:
http://www.sommarskog.se/arrays-in-sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Avoiding NULLS how ?
Two examples where we use NULL fields, how to avoid them ?
For a datetime field, date's which are still unknown, for
example an appointment in the future or the ending of
a event still going on, were the date still
has to be set or the date when somebody died.
(And what to do with a birthdate, which is not completely
know, for example jan 1958 or born in 1958, sorry side track).
A integer field, for example a count field were there is no
actual count at the moment. (count can be positive and negative).
Offcourse the data is used by different applications and systems,
also by MIS/MSS/DSS/Olap/Datamining.
Ben BrugmanHi Ben,
What's the reason for avoiding NULL's, seem like a logical values to me in
these cases?
HTH
Karl Gram
http://www.gramonline.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:#88hzTODEHA.3016@.TK2MSFTNGP11.phx.gbl...
> Two examples where we use NULL fields, how to avoid them ?
> For a datetime field, date's which are still unknown, for
> example an appointment in the future or the ending of
> a event still going on, were the date still
> has to be set or the date when somebody died.
> (And what to do with a birthdate, which is not completely
> know, for example jan 1958 or born in 1958, sorry side track).
> A integer field, for example a count field were there is no
> actual count at the moment. (count can be positive and negative).
> Offcourse the data is used by different applications and systems,
> also by MIS/MSS/DSS/Olap/Datamining.
> Ben Brugman
>|||On Thu, 18 Mar 2004 13:31:32 +0100, ben brugman wrote:
>Two examples where we use NULL fields, how to avoid them ?
>For a datetime field, date's which are still unknown, for
>example an appointment in the future or the ending of
>a event still going on, were the date still
>has to be set or the date when somebody died.
>(And what to do with a birthdate, which is not completely
>know, for example jan 1958 or born in 1958, sorry side track).
>A integer field, for example a count field were there is no
>actual count at the moment. (count can be positive and negative).
>Offcourse the data is used by different applications and systems,
>also by MIS/MSS/DSS/Olap/Datamining.
>Ben Brugman
I know many people advise against using NULLs. I don't agree with
them. If a programmer doesn't know how to code proper SQL statements
with NULLable columns, don't forbid NULLs but fire the programmer and
hire a more capable replacement.
In the examples you provided (date unknown / no count present), NULL
is an excellent (the best, IMnotsoHO) solution.
Icomplete dates are another matter. If you foresee incomplete dates,
you'll have to store the parts of the date individually. So the
combination day/month/year would be NULL/NULL/1958 for someone born in
1958, or NULL/1/1958 for someone born in jan 1958. However, this will
require lots of extra work if you also have to do date calculations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
For a datetime field, date's which are still unknown, for
example an appointment in the future or the ending of
a event still going on, were the date still
has to be set or the date when somebody died.
(And what to do with a birthdate, which is not completely
know, for example jan 1958 or born in 1958, sorry side track).
A integer field, for example a count field were there is no
actual count at the moment. (count can be positive and negative).
Offcourse the data is used by different applications and systems,
also by MIS/MSS/DSS/Olap/Datamining.
Ben BrugmanHi Ben,
What's the reason for avoiding NULL's, seem like a logical values to me in
these cases?
HTH
Karl Gram
http://www.gramonline.com
"ben brugman" <ben@.niethier.nl> wrote in message
news:#88hzTODEHA.3016@.TK2MSFTNGP11.phx.gbl...
> Two examples where we use NULL fields, how to avoid them ?
> For a datetime field, date's which are still unknown, for
> example an appointment in the future or the ending of
> a event still going on, were the date still
> has to be set or the date when somebody died.
> (And what to do with a birthdate, which is not completely
> know, for example jan 1958 or born in 1958, sorry side track).
> A integer field, for example a count field were there is no
> actual count at the moment. (count can be positive and negative).
> Offcourse the data is used by different applications and systems,
> also by MIS/MSS/DSS/Olap/Datamining.
> Ben Brugman
>|||On Thu, 18 Mar 2004 13:31:32 +0100, ben brugman wrote:
>Two examples where we use NULL fields, how to avoid them ?
>For a datetime field, date's which are still unknown, for
>example an appointment in the future or the ending of
>a event still going on, were the date still
>has to be set or the date when somebody died.
>(And what to do with a birthdate, which is not completely
>know, for example jan 1958 or born in 1958, sorry side track).
>A integer field, for example a count field were there is no
>actual count at the moment. (count can be positive and negative).
>Offcourse the data is used by different applications and systems,
>also by MIS/MSS/DSS/Olap/Datamining.
>Ben Brugman
I know many people advise against using NULLs. I don't agree with
them. If a programmer doesn't know how to code proper SQL statements
with NULLable columns, don't forbid NULLs but fire the programmer and
hire a more capable replacement.
In the examples you provided (date unknown / no count present), NULL
is an excellent (the best, IMnotsoHO) solution.
Icomplete dates are another matter. If you foresee incomplete dates,
you'll have to store the parts of the date individually. So the
combination day/month/year would be NULL/NULL/1958 for someone born in
1958, or NULL/1/1958 for someone born in jan 1958. However, this will
require lots of extra work if you also have to do date calculations.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, February 24, 2012
avoiding "NaN" , "infinity" in reports
When I use calculation in generating reports, I get "NaN" and "Infinity" values, I wanted to avoid this and display "0" if the result of calculation is null or infinity..
I used the below calculation
=(Fields!approved_tier_cnt.Value)/(Fields!approved_cnt.Value)
Please let me know how to avoid "NaN" and "infinity", while displaying in reports
Regards
Durai
Hello
What I do is tackle those Nan's before they get into RS. On your SQL Server you could make a function something like this:
CREATE FUNCTION formula (
@.Numerator REAL,
@.Denominator REAL,
@.decimals INT = 2,
@.length INT = 10
)
RETURNS VARCHAR(50)
AS BEGIN IF (
@.Denominator IS NULL )
OR (@.Numerator IS NULL )
RETURN '' IF
(@.Denominator <> 0) BEGIN
RETURN str((@.Numerator ) / @.Denominator,@.length - 1, @.decimals)
END
RETURN ''
END
et voila...
put the results in a view
Worf
|||I would use some explicit type casting and conditional logic.
Firstly to avoid NaN I would make sure the fields are numeric by using one of the standard VB conversion functions e.g. CInt(), CDbl() etc. like this:
=CDbl(Fields!approved_tier_cnt.Value)/CDbl(Fields!approved_cnt.Value)
Then use the Iif() function to check for nulls or check that the divisor is not 0 to avoid infinity
=Iif( Fields!approved_tier_cnt.Value AND
Fields!approved_cnt.Value AND
CDbl(Fields!approved_cnt.Value) != 0
, CDbl(Fields!approved_tier_cnt.Value) / CDbl(Fields!approved_cnt.Value)
, 0
)
Avoid windows login prompt while accessing report server.
Hi,
We are using Microsoft Reporting Service 2005 to develop reports and we are accessing these reports through a J2EE application.
The front end is implemented using Tapestry and we using JBoss as our applicaiton server.
We are using Shared Data Sources for the reports and we set its data source type to SQL Server Analysis Services.
In the credential tab, by default "Use Windows authentication" is selected. All other options are disabled.
When I access my reportserver through my web application, I am always prompted for a windows login and password.
How can I avoid being shown the windows login prompt, since our web application will be used by several users and we do not want the users to type in a username/password everytime they want to access our reports.
Please suggest me solution for this scenario.
Thanks in advance!
Hi,
i think the core problem is, that you use windows integrated authentication. In this case the current login user on the client is used to authenticat the access to the database, if this user dont have access rights to the database, you get the login prompt.
Perhapse it is possible for you to set fixed credentials in the shared datasource?
Tibor Csizmadia
|||
How do I give the users access rights to the database? and there may be so many users invoking the reports through our web application.
As I mentioned in my earlier post when I create a new shared data source in my report project, in the Credentials tab "Use Windows Authentication (Integrated Security)" is selected by default. All other options are disabled so I cannot set any other credentials.
Why is it that I can select credential only as "Windows Authentication" is it because the data source type is selected as SQL Server Analysis Services?
Also one thing I forgot to mention is that we are using SQL Server 2005 Standard Edition
|||Hi,
if you want to use the integrated security, i would create a security-group the the users who have access to the database. Then asign in the SQL-Server-Management Studio this security group to the database (Security/Logins, Add new User, choose the security-group from the AD, assign the Database to access throu this group).
I doent know why you can only use the windows Authentication for "Microsoft SQL-Server Analyse Services" in VS.NET, but if you define the share datasource in the reporting-managment website (http://localhost/Reports) you can set outher authentications...Perhaps MS have for this an explanation.
I hope this help you.
Tibor Csizmadia
avoid warning
We use a script in our build process to create a database. A single
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.
See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>
|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegro ups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.
See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>
|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegro ups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>
avoid warning
We use a script in our build process to create a database. A single
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
> > We use a script in our build process to create a database. A single
> > script creates entire database, including stored procedures. We
> > occasionally
> > get a warning message "cannot create entry in sysdepends as .....
> > the procedure will still be created". I believe this is a harmless
> > warning
> > and happens because of the order of creation of stord procedure. Is
> > there a way to disable this warning.
> >
> >|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegroups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
> > We use a script in our build process to create a database. A single
> > script creates entire database, including stored procedures. We
> > occasionally
> > get a warning message "cannot create entry in sysdepends as .....
> > the procedure will still be created". I believe this is a harmless
> > warning
> > and happens because of the order of creation of stord procedure. Is
> > there a way to disable this warning.
> >
> >|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegroups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>
avoid warning
We use a script in our build process to create a database. A single
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
>|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegroups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>
script creates entire database, including stored procedures. We
occasionally
get a warning message "cannot create entry in sysdepends as .....
the procedure will still be created". I believe this is a harmless
warning
and happens because of the order of creation of stord procedure. Is
there a way to disable this warning.See "set ansi_warnings" in BOL.
use northwind
go
set ansi_warnings off
go
create procedure proc1
as
select * from t1
go
set ansi_warnings on
go
drop procedure proc1
go
AMB
"Data Cruncher" wrote:
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>|||Sorry,
It seems that this option does not help.
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> See "set ansi_warnings" in BOL.
> use northwind
> go
> set ansi_warnings off
> go
> create procedure proc1
> as
> select * from t1
> go
> set ansi_warnings on
> go
> drop procedure proc1
> go
>
> AMB
> "Data Cruncher" wrote:
>|||I don't think there's a way to shut off the warning outside of
creating your script with sub procs being compiled before the
calling procs.
For all intents and purposes, it's harmless for the reason
you stated. Of course, it's not harmless if the main procedure,
in fact, doesn't get created!
"Data Cruncher" <dcruncher4@.netscape.net> wrote in message
news:1116249702.395329.63480@.g47g2000cwa.googlegroups.com...
> We use a script in our build process to create a database. A single
> script creates entire database, including stored procedures. We
> occasionally
> get a warning message "cannot create entry in sysdepends as .....
> the procedure will still be created". I believe this is a harmless
> warning
> and happens because of the order of creation of stord procedure. Is
> there a way to disable this warning.
>
avoid using cursors....
I am trying to rewrite a sp that I created years ago to avoid using cursors
so there is no problems with mutiprocessor systems and parallelism.
Simplifying, we have an order table and task table for each order:
CREATE TABLE [TOrders] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NULL ,
CONSTRAINT [PK_TOrders] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [TTasks] (
[OrderID] [int] NOT NULL ,
[ID] [int] NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NOT NULL
CONSTRAINT [PK_TTasks] PRIMARY KEY NONCLUSTERED
(
[OrderID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_TTasks_TOrders] FOREIGN KEY
(
[OrderID]
) REFERENCES [TOrders] (
[ID]
)
) ON [PRIMARY]
GO
The stored procedure under analysis consists of copying (inserting) all the
tasks already existing for an order to another one. Since ID field in TTasks
is not identity, we should retrieve the current maximum ID for the
destination order and continue the insertions from that ID onwards. The SP
as it is now follows:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int,
@.Description varchar(64)
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
-- Cursor to iterate through source taks
DECLARE my_cursor CURSOR LOCAL FOR
SELECT TTasks.Description FROM TTasks
WHERE TTasks.OrderID= @.fromO
ORDER BY TTasks.ID
-- This is a simple iteration to insert tasks but starting at @.i instead
starting at 1
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @.Description
WHILE (@.@.FETCH_STATUS <> -1) BEGIN
IF (@.@.FETCH_STATUS <> -2) BEGIN
SET @.i = @.i + 1
INSERT INTO TTasks (OrderID, ID, Description)
VALUES (@.toO, @.i, @.Description)
END
FETCH NEXT FROM my_cursor INTO @.Description
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
What I am trying to do is replace the cursor used there by a single insert
statement such as:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ****, Description FROM TTasks
WHERE TTasks.OrderID = @.fromO
ORDER BY TTasks.ID
GO
What is driving me crazy is how to calculate the field marked with ****
Can someone help me? Is there any way to do it without having ID field in
TTasks being an identity? It cannot be an identity since it must start with
1 for every new order.
Thanks in advance.jagb (jagb@.NOSPAM.com) writes:
> What is driving me crazy is how to calculate the field marked with ****
> Can someone help me? Is there any way to do it without having ID field
> in TTasks being an identity? It cannot be an identity since it must
> start with 1 for every new order.
A very simple-minded solution is to bounce the data over a temp table
with an IDENTITY column.
A more "relational" solution is to add a table of numbers to the database.
This is a one-column table that holds all numbers from 1 up to some limit.
Personally, I sort of favour the temp-table solution, as it is more
robust. You don't risk to run out of numbers. Then again, there is a
performance cost for using an extra table, so for this case I might go
for a table of numbers.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could use a correlated subquery to generate the ranking value within a
SELECT statement like:
INSERT tasks ( ... )
SELECT @.to, ( SELECT COUNT(*)
FROM tasks t2
WHERE t2.orderID = t1.Orderid
AND t2.id <= t1.id )
FROM tasks t1
WHERE t1.orderid = @.p ;
The assumption is that the id values are unique for each Orderid. This is
untested, if you'd like a tested one, please post a few sample data along
with expected results.
Anith|||TOrders (ID, Desc)
1 First Order
2 Second Order
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
After having run the SP to copy tasks from order=1 to order=2 the tasks
table should contain the following values:
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
2 5 a <-- these are the inserted records
2 6 b <-- these are the inserted records
2 7 c <-- these are the inserted records
This is not achieved using your statement wich will incorrectly try to
insert the values:
2 1 a
2 2 b
2 9 c
And will raise a primary key conflict, since the keys (OrderID ID) valued
to (2 1), (2 2) and (2 3) already exist in TTasks.
Note that the (1 9 c) record should be converted to (2 7 c) when
copied along for order 2.
Mhh... it seems that your approach almost hit in the nail... after having
done some tests I have found the solution:
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO,
(SELECT ISNULL(COUNT(*), 0)
FROM dbo.TTasks t2
WHERE (OrderID = t1.OrderID) AND (ID <=
t1.ID)) +
(SELECT ISNULL(MAX(ID), 0)
FROM dbo.TTasks
WHERE (OrderID = @.toO)), Descripcion
FROM dbo.TTasks t1
WHERE (OrderID = @.fromO)
Thanks for your help, Anith.
"Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
> You could use a correlated subquery to generate the ranking value within a
> SELECT statement like:
> INSERT tasks ( ... )
> SELECT @.to, ( SELECT COUNT(*)
> FROM tasks t2
> WHERE t2.orderID = t1.Orderid
> AND t2.id <= t1.id )
> FROM tasks t1
> WHERE t1.orderid = @.p ;
> The assumption is that the id values are unique for each Orderid. This is
> untested, if you'd like a tested one, please post a few sample data along
> with expected results.
> --
> Anith
>|||jagb (jagb@.NOSPAM.com) writes:
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
Beware, though, that nested subqueries in the SELECT list often gives
poor performance. Certinly better than your cursor, it can be considerably
slower than bouncing over a temp table, or using a table of numbers.
Then again, it depends on how many rows you insert at time. If it is
< 100, the difference may not be measurable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You can also use a Table Variable with an IDENTITY column. I like them
better than temp tables for generating row numbers because unlike temp
tables, modifications to table variables don't share the same transaction
space as changes to permanent tables which can reduce the performance hit of
using the extra table. They also reduce recompiles and can reduce
contention on the system table indexes in tempdb.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97428F2528D48Yazorman@.127.0.0.1...
> jagb (jagb@.NOSPAM.com) writes:
> A very simple-minded solution is to bounce the data over a temp table
> with an IDENTITY column.
> A more "relational" solution is to add a table of numbers to the database.
> This is a one-column table that holds all numbers from 1 up to some limit.
> Personally, I sort of favour the temp-table solution, as it is more
> robust. You don't risk to run out of numbers. Then again, there is a
> performance cost for using an extra table, so for this case I might go
> for a table of numbers.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||I think bouncing the values off a temporary object will be faster than a
correlated subquery:
DECLARE @.T TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Description VARCHAR(64) NOT NULL
)
BEGIN TRAN
INSERT @.T (Description)
SELECT Description
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block updates to source order
WHERE OrderID = @.fromO
ORDER BY ID
IF @.@.ROWCOUNT > 0
BEGIN
SELECT @.i = ISNULL(MAX(ID), 0)
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block appending to destination
order
WHERE OrderID = @.to0
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ID + @.i, Description FROM @.T
END
COMMIT TRAN
"jagb" <jagb@.NOSPAM.com> wrote in message
news:esRjDsgEGHA.2704@.TK2MSFTNGP15.phx.gbl...
> TOrders (ID, Desc)
> 1 First Order
> 2 Second Order
>
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> After having run the SP to copy tasks from order=1 to order=2 the tasks
> table should contain the following values:
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> 2 5 a <-- these are the inserted records
> 2 6 b <-- these are the inserted records
> 2 7 c <-- these are the inserted records
> This is not achieved using your statement wich will incorrectly try to
> insert the values:
> 2 1 a
> 2 2 b
> 2 9 c
> And will raise a primary key conflict, since the keys (OrderID ID) valued
> to (2 1), (2 2) and (2 3) already exist in TTasks.
> Note that the (1 9 c) record should be converted to (2 7 c)
> when copied along for order 2.
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
> "Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
> news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>
so there is no problems with mutiprocessor systems and parallelism.
Simplifying, we have an order table and task table for each order:
CREATE TABLE [TOrders] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NULL ,
CONSTRAINT [PK_TOrders] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [TTasks] (
[OrderID] [int] NOT NULL ,
[ID] [int] NOT NULL ,
[Description] [varchar] (64) COLLATE Modern_Spanish_CI_AS NOT NULL
CONSTRAINT [PK_TTasks] PRIMARY KEY NONCLUSTERED
(
[OrderID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK_TTasks_TOrders] FOREIGN KEY
(
[OrderID]
) REFERENCES [TOrders] (
[ID]
)
) ON [PRIMARY]
GO
The stored procedure under analysis consists of copying (inserting) all the
tasks already existing for an order to another one. Since ID field in TTasks
is not identity, we should retrieve the current maximum ID for the
destination order and continue the insertions from that ID onwards. The SP
as it is now follows:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int,
@.Description varchar(64)
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
-- Cursor to iterate through source taks
DECLARE my_cursor CURSOR LOCAL FOR
SELECT TTasks.Description FROM TTasks
WHERE TTasks.OrderID= @.fromO
ORDER BY TTasks.ID
-- This is a simple iteration to insert tasks but starting at @.i instead
starting at 1
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @.Description
WHILE (@.@.FETCH_STATUS <> -1) BEGIN
IF (@.@.FETCH_STATUS <> -2) BEGIN
SET @.i = @.i + 1
INSERT INTO TTasks (OrderID, ID, Description)
VALUES (@.toO, @.i, @.Description)
END
FETCH NEXT FROM my_cursor INTO @.Description
END
CLOSE my_cursor
DEALLOCATE my_cursor
GO
What I am trying to do is replace the cursor used there by a single insert
statement such as:
CREATE PROCEDURE CopyTasksFromOrderToOrder (@.fromO int, @.toO int) AS
DECLARE
@.i int
-- Retrieve the currently maximum ID for the destination order
SELECT @.i = ISNULL(MAX(ID),0) FROM TTasks WHERE OrderID = @.toO
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ****, Description FROM TTasks
WHERE TTasks.OrderID = @.fromO
ORDER BY TTasks.ID
GO
What is driving me crazy is how to calculate the field marked with ****
Can someone help me? Is there any way to do it without having ID field in
TTasks being an identity? It cannot be an identity since it must start with
1 for every new order.
Thanks in advance.jagb (jagb@.NOSPAM.com) writes:
> What is driving me crazy is how to calculate the field marked with ****
> Can someone help me? Is there any way to do it without having ID field
> in TTasks being an identity? It cannot be an identity since it must
> start with 1 for every new order.
A very simple-minded solution is to bounce the data over a temp table
with an IDENTITY column.
A more "relational" solution is to add a table of numbers to the database.
This is a one-column table that holds all numbers from 1 up to some limit.
Personally, I sort of favour the temp-table solution, as it is more
robust. You don't risk to run out of numbers. Then again, there is a
performance cost for using an extra table, so for this case I might go
for a table of numbers.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You could use a correlated subquery to generate the ranking value within a
SELECT statement like:
INSERT tasks ( ... )
SELECT @.to, ( SELECT COUNT(*)
FROM tasks t2
WHERE t2.orderID = t1.Orderid
AND t2.id <= t1.id )
FROM tasks t1
WHERE t1.orderid = @.p ;
The assumption is that the id values are unique for each Orderid. This is
untested, if you'd like a tested one, please post a few sample data along
with expected results.
Anith|||TOrders (ID, Desc)
1 First Order
2 Second Order
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
After having run the SP to copy tasks from order=1 to order=2 the tasks
table should contain the following values:
TTasks (OrderID ID Descripcion)
1 1 a
1 2 b
1 9 c
2 1 WW
2 2 XX
2 3 YY
2 4 ZZ
2 5 a <-- these are the inserted records
2 6 b <-- these are the inserted records
2 7 c <-- these are the inserted records
This is not achieved using your statement wich will incorrectly try to
insert the values:
2 1 a
2 2 b
2 9 c
And will raise a primary key conflict, since the keys (OrderID ID) valued
to (2 1), (2 2) and (2 3) already exist in TTasks.
Note that the (1 9 c) record should be converted to (2 7 c) when
copied along for order 2.
Mhh... it seems that your approach almost hit in the nail... after having
done some tests I have found the solution:
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO,
(SELECT ISNULL(COUNT(*), 0)
FROM dbo.TTasks t2
WHERE (OrderID = t1.OrderID) AND (ID <=
t1.ID)) +
(SELECT ISNULL(MAX(ID), 0)
FROM dbo.TTasks
WHERE (OrderID = @.toO)), Descripcion
FROM dbo.TTasks t1
WHERE (OrderID = @.fromO)
Thanks for your help, Anith.
"Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
> You could use a correlated subquery to generate the ranking value within a
> SELECT statement like:
> INSERT tasks ( ... )
> SELECT @.to, ( SELECT COUNT(*)
> FROM tasks t2
> WHERE t2.orderID = t1.Orderid
> AND t2.id <= t1.id )
> FROM tasks t1
> WHERE t1.orderid = @.p ;
> The assumption is that the id values are unique for each Orderid. This is
> untested, if you'd like a tested one, please post a few sample data along
> with expected results.
> --
> Anith
>|||jagb (jagb@.NOSPAM.com) writes:
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
Beware, though, that nested subqueries in the SELECT list often gives
poor performance. Certinly better than your cursor, it can be considerably
slower than bouncing over a temp table, or using a table of numbers.
Then again, it depends on how many rows you insert at time. If it is
< 100, the difference may not be measurable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||You can also use a Table Variable with an IDENTITY column. I like them
better than temp tables for generating row numbers because unlike temp
tables, modifications to table variables don't share the same transaction
space as changes to permanent tables which can reduce the performance hit of
using the extra table. They also reduce recompiles and can reduce
contention on the system table indexes in tempdb.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97428F2528D48Yazorman@.127.0.0.1...
> jagb (jagb@.NOSPAM.com) writes:
> A very simple-minded solution is to bounce the data over a temp table
> with an IDENTITY column.
> A more "relational" solution is to add a table of numbers to the database.
> This is a one-column table that holds all numbers from 1 up to some limit.
> Personally, I sort of favour the temp-table solution, as it is more
> robust. You don't risk to run out of numbers. Then again, there is a
> performance cost for using an extra table, so for this case I might go
> for a table of numbers.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||I think bouncing the values off a temporary object will be faster than a
correlated subquery:
DECLARE @.T TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
Description VARCHAR(64) NOT NULL
)
BEGIN TRAN
INSERT @.T (Description)
SELECT Description
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block updates to source order
WHERE OrderID = @.fromO
ORDER BY ID
IF @.@.ROWCOUNT > 0
BEGIN
SELECT @.i = ISNULL(MAX(ID), 0)
FROM TTasks WITH(UPDLOCK, HOLDLOCK) --block appending to destination
order
WHERE OrderID = @.to0
INSERT INTO TTasks (OrderID, ID, Description)
SELECT @.toO, ID + @.i, Description FROM @.T
END
COMMIT TRAN
"jagb" <jagb@.NOSPAM.com> wrote in message
news:esRjDsgEGHA.2704@.TK2MSFTNGP15.phx.gbl...
> TOrders (ID, Desc)
> 1 First Order
> 2 Second Order
>
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> After having run the SP to copy tasks from order=1 to order=2 the tasks
> table should contain the following values:
> TTasks (OrderID ID Descripcion)
> 1 1 a
> 1 2 b
> 1 9 c
> 2 1 WW
> 2 2 XX
> 2 3 YY
> 2 4 ZZ
> 2 5 a <-- these are the inserted records
> 2 6 b <-- these are the inserted records
> 2 7 c <-- these are the inserted records
> This is not achieved using your statement wich will incorrectly try to
> insert the values:
> 2 1 a
> 2 2 b
> 2 9 c
> And will raise a primary key conflict, since the keys (OrderID ID) valued
> to (2 1), (2 2) and (2 3) already exist in TTasks.
> Note that the (1 9 c) record should be converted to (2 7 c)
> when copied along for order 2.
> Mhh... it seems that your approach almost hit in the nail... after having
> done some tests I have found the solution:
> INSERT INTO TTasks (OrderID, ID, Description)
> SELECT @.toO,
> (SELECT ISNULL(COUNT(*), 0)
> FROM dbo.TTasks t2
> WHERE (OrderID = t1.OrderID) AND (ID <=
> t1.ID)) +
> (SELECT ISNULL(MAX(ID), 0)
> FROM dbo.TTasks
> WHERE (OrderID = @.toO)), Descripcion
> FROM dbo.TTasks t1
> WHERE (OrderID = @.fromO)
>
> Thanks for your help, Anith.
> "Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
> news:eZf8nlfEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>
avoid using cursors
Hi All,
I want to avoid using cursors and loops in stored procedures.
Please suggest alternate solutions with example (if possible).
I want to avoid using cursors and loops in stored procedures.
Please suggest alternate solutions with example (if possible).
Any suggestion in these regards will be appreciated.
Thanks in advance,
T.S.NegiHere is a sample:
http://www.extremeexperts.com/SQL/A...TSQLResult.aspx
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"T.S.Negi" <tilak.negi@.mind-infotech.com> wrote in message
news:a1930058.0502132109.268ae0dd@.posting.google.c om...
> Hi All,
> I want to avoid using cursors and loops in stored procedures.
> Please suggest alternate solutions with example (if possible).
> Any suggestion in these regards will be appreciated.
>
> Thanks in advance,
> T.S.Negi|||Loops and cursors are just programming constructs, not actual problems
to be solved, so there is no generic solution or example to show how to
avoid them. You should try to avoid or minimize the use of loops and
cursors in SQL and this is done by writing standard set-based code:
SELECT, UPDATE, DELETE and INSERT statements that operate on sets of
rows rather than one row at a time. The actual details will depend on
exactly what you want to achieve.
If you require hlep with a specific problem then please post more
details as described in:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Unfortunately, all the examples in that article are loops!
--
David Portas
SQL Server MVP
--|||tilak.negi@.mind-infotech.com (T.S.Negi) wrote:
>Hi All,
>I want to avoid using cursors and loops in stored procedures.
>Please suggest alternate solutions with example (if possible).
Off at a tangent:
I see many times in these groups (and elsewhere) that cursors should
be avoided. As a comparative newbie can someone explain to me the
reasons for this.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||There are plenty of good reasons to use declarative, set-based SQL code
instead of cursors. The reason most usually given is performance. SQL
Server, like other SQL databases, is designed primarily for
set-at-a-time rather than row-at-a-time operations. Cursors are
typically very slow, although performance obviously varies considerably
depending on what you are doing. Paradoxically, it is also true to say
that there is a small class of problems for which cursors are faster
than any set-based solution. In a well-designed database those
situations are uncommon in my experience and you would be well-advised
to get a second opinion if you think you have come across such a case.
Performance and locking issues with cursors tend to mean they are far
less scalable than the set-based alternatives so even if they work for
you today they may not be a viable solution in future.
Besides performance there are other good reasons to use set-based code:
The declarative code is usually much more concise and therefore easier
to develop, inspect, test and maintain; It's more likely to be
portable to other database platforms; SQL professionals (good ones
using TSQL anyway) tend to write cursors seldom and so are likely to be
more comfortable and more productive writing set-based code; Set-based
code avoids or tends to show-up some of the logical anomalies and
design problems that can lie hidden and unnoticed in procedural cursor
code.
A legitimate place to use a cursor is for something inherently
procedural (typically admin tasks such as managing backups, sending
emails or importing/exporting files) but in general in an RDBMS you
should assume the solution to any data-manipulation problem will be
set-based unless expert analysis proves otherwise. That's why I would
class cursors as a feature for advanced users only. If you find
yourself writing cursors regularly then it's time to rethink what you
are doing or maybe go on a course to learn grown-up SQL (too many
cursors are written by programmers who don't know better techniques)
:-)
--
David Portas
SQL Server MVP
--|||On Tue, 15 Feb 2005 14:21:03 +0000, John A Fotheringham wrote:
>I see many times in these groups (and elsewhere) that cursors should
>be avoided. As a comparative newbie can someone explain to me the
>reasons for this.
Hi John,
SQL Server is heavily optimized towards set-based operations, where you
use one single query to specify what you want and let SQL Server work out
the best strategy to satisfy your request. That's why SQL is called a
declarative language (you declare the intended results, not the way to get
there, as opposed to procedural languages (where you specify the procedure
to get the intended results).
Using cursors is forcing a procedural approach on SQL. You still use a
query to specify the rows you want to fetch (the declarative part), but
then you fetch one, do something with it and fetch the next - that is
purely procedural.
It is my experience that at least 99% of all existing cursor-based code
can be replaced by set-based code. In most cases, the replacing set-based
code is shorter (less lines), easier to read, understand and maintain (at
least after you've mastered the learning curve to switch from procedural
thinking to declarative thinking) and -most important- performs much
faster.
For the remaining less than one percent, cursors are indeed the best
solution. I won't say that cursors should _always_ be avoided. But I will
say that they are to be used as a final resort only - and it's always wise
to get a second opinion first. Newsgroups are a great place to explain
your problem and ask if others agree that this particular problem can't be
solved with declarative code. In most cases, you'll get a surprising
answer - and if you take the trouble to not only copy and adapt the code
posted, but also to try to understand it, you'll get a great learning
experience thrown in for free!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||One trick I have started to use:
Whenever I need to iterate in a procedure I have a table that I have
created with only one column like so:
CREATE TABLE [Numbers] (
[PkNumber] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [Pk_Number] PRIMARY KEY CLUSTERED
(
[PkNumber]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Declare @.i as int
set @.i = 0
while @.i <= 10000
begin
Insert into dbo.Numbers Default values
set @.i = @.i + 1
end
GO
Now with this table I can query against it like it were a loop but
still remaining set based.
So for instance If I were trying to Schedule a date every 7 days for
the next year I could do something like
insert into dates(dates)
Select DateAdd(Day,pk_Number * 7,getdate())
from Numbers
where pk_number < 366
Just a little trick
Tal McMahon|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> It is my experience that at least 99% of all existing cursor-based code
> can be replaced by set-based code. In most cases, the replacing set-based
> code is shorter (less lines), easier to read, understand and maintain (at
> least after you've mastered the learning curve to switch from procedural
> thinking to declarative thinking) and -most important- performs much
> faster.
Our system has its fair share ot iterative processing, and maybe the
most common good reason to use a cursor is that you have a stored
procedure that performs an operation on a single set of values and
you want to reuse that logic.
If all the procedure performs is a simple update, or a plain insert,
there's little reason to keep the procedure.
But we have core procedures that performs a lot of updates and inserts
(including validations) for a bunch of in-parameters. Rewriting such
a procedure to operate set-based from an input table is a major task.
We've done it in one case. I seem to recall that the time estimate was
200 h, and I think we exceeded that. The result is a monster procedure
on 3000 lines that uses 43 table variables.
This particular rewrite was necessary given some of the volumes that can
occur in some of the iterations with its predecessor. But I can tell
you that I am not going to initiate more rewrites, just for the sake
of it.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 15 Feb 2005 22:57:01 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>This particular rewrite was necessary given some of the volumes that can
>occur in some of the iterations with its predecessor. But I can tell
>you that I am not going to initiate more rewrites, just for the sake
>of it.
Hi Erland,
I see what you mean and I totally agree: if it works and it is not "too"
slow, then there is abolutely no reason to change it.
The often prohibitive cost of rewrites is just another reason for me to
continue trying to convince everybody to write set-based code right from
the off.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have a book called SQL PROGRAMMING STYLE that should be published by
2005 April that has some chapters on how to think in Sets instead of
procedures. There is no single, magic answer.|||>> I see many times in these groups (and elsewhere) that cursors should
be avoided. As a comparative newbie can someone explain to me the
reasons for this. <<
All of the performance reasons that portas, Kornelis and Sommarskog
gave are the usual reasons. But don't for get the lack of portability!
In spite of the SQL standards, cursors are still VERY proprietary. But
even if they were all perfectly aligned, there are enough
"implementation dependent" things to screw you over. For example,
there is a warning that is raised when a GROUP BY has a NULL removed
from one of the groups. Sounds good, since I might not want to run a
report if I have missing data in one or more groups ("And where the
hell is Smith's sales figures??!")
But this warning can be raised at DECLARE CURSOR, OPEN cursor and/or
FETCH cursor. This is going to change application program logic quite
a bit.|||Except, of course, that this schedules the event every 7 days for the
next 7 years...
Your where clause should be:
where (pk_number * 7) < 366|||Erland Sommarskog <esquel@.sommarskog.se> wrote:
>Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
>> It is my experience that at least 99% of all existing cursor-based code
>> can be replaced by set-based code. In most cases, the replacing set-based
>> code is shorter (less lines), easier to read, understand and maintain (at
>> least after you've mastered the learning curve to switch from procedural
>> thinking to declarative thinking) and -most important- performs much
>> faster.
>Our system has its fair share ot iterative processing, and maybe the
>most common good reason to use a cursor is that you have a stored
>procedure that performs an operation on a single set of values and
>you want to reuse that logic.
This is the only situation in which I've used a cursor so far.
I have a trigger set on insert into one table, and for each inserted
record I want to use it's contents to create and/or update the
contents of a record in a second table.
I've written a procedure to do the fairly complex update from one
record to another, and I call that procedure from inside a fairly
simple cursor loop that forms the main body of the trigger procedure.
I'm not too worried about cursor overheads here, because in general
only one record at a time is being inserted into the first table.
At the time I wrote this cursors seemed the only (and natural) way to
iterate through the records in the "inserted" table, which in this
instance is what I need to do as each record has to be processed
separately.
If there's a better/more appropriate way of doing this I'd be
interested to hear it. A lot of the "set-based" solutions I see here
just seem to be implementing loops using a table and from a purely
programming point of view (my background) seem a counter-intuitive way
of doing things.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||I would recommend that you don't use cursors in triggers. As already
discussed, if you have a legacy of procedural code that works on one
row at a time then yes, you may be forced to call that code in a loop
just because of the cost of rewriting your procedure in set-based form.
That's a pity because some day you may want to update more than one row
at a time and anyway you really don't need the overhead of a cursor
declaration in a trigger, even for a single row.
> A lot of the "set-based" solutions I see here
> just seem to be implementing loops using a table and from a purely
> programming point of view (my background) seem a counter-intuitive
way
> of doing things.
I don't know what you are referring to. Maybe you have an example? If
you mean using WHILE loops and SELECT statements in place of cursors
then yes, that is just cursor in disguise and all my comments about
cursors apply equally to those other row-by-row constructs.
It is true that declarative SQL requires a slightly different mindset
and it is often noted that procedural programmers find these methods
counter-intuitive. However, the relational model and SQL are the
dominant industry database standards with good reason and that is
hopefully a good enough incentive for the programmer to learn the
standard techniques and best-practices, aside from the very practical
considerations already explained.
--
David Portas
SQL Server MVP
--|||> If there's a better/more appropriate way of doing this I'd be
> interested to hear it.
Very likely there is a better way but we'll need more details first:
http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
>I would recommend that you don't use cursors in triggers. As already
>discussed, if you have a legacy of procedural code that works on one
>row at a time then yes, you may be forced to call that code in a loop
>just because of the cost of rewriting your procedure in set-based form.
>That's a pity because some day you may want to update more than one row
>at a time and anyway you really don't need the overhead of a cursor
>declaration in a trigger, even for a single row.
So how would you advise to select a single row from the inserted table
without using a cursor?
>> A lot of the "set-based" solutions I see here
>> just seem to be implementing loops using a table and from a purely
>> programming point of view (my background) seem a counter-intuitive
>way
>> of doing things.
>I don't know what you are referring to. Maybe you have an example? If
>you mean using WHILE loops and SELECT statements in place of cursors
>then yes, that is just cursor in disguise and all my comments about
>cursors apply equally to those other row-by-row constructs.
Well that's what I thought. Most of these approaches seem to be a
loop through table, and it's not obvious (to the newbies) that this
would be more efficient than a cursor.
>It is true that declarative SQL requires a slightly different mindset
>and it is often noted that procedural programmers find these methods
>counter-intuitive. However, the relational model and SQL are the
>dominant industry database standards with good reason and that is
>hopefully a good enough incentive for the programmer to learn the
>standard techniques and best-practices, aside from the very practical
>considerations already explained.
I'm not disputing this, just asking questions so I can better get into
the correct mindset for SQL.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
>> If there's a better/more appropriate way of doing this I'd be
>> interested to hear it.
>Very likely there is a better way but we'll need more details first:
>http://www.aspfaq.com/etiquette.asp?id=5006
Well I was really just asking a generic question. The tables involved
are fairly lengthy (although as far as the logic goes only a few
fields apply) and I didn't think to fill my post with all the details.
I wasn't seeking a particular solution, just asking the general
question.
In essence I have
CREATE TABLE transaction
(
IDint IDENTITY(1,1),
tsdatetime
jobvarchar(20)
statuschar(1)
...
other transaction fields
...
)
CREATE TABLE jobs
(
jobvarchar(20)
statuschar(1)
last_updatedatetime
...
other job fields
...
)
Each time a transaction comes in I use the details in the transaction
to update the jobs table. If it's a new job I create a new record,
otherwise I perform an update. The nature of the update can depend
on the value of the new status, so that depending on the status
different values amongst the "other transaction fields" will cause
different updated for the "other job fields". Further processing may
occur for some status codes.
To achieve this I wrote a "processTrn" procedure which takes a single
transaction and executes all the (largely procedural) updates.
To call this procedure I created a trigger on the transactions table,
and it's there that I use a cursor to go through the "inserted" table
to extract each new record in turn and call the procedure on it.
To my mind this is a naturally loop+procedural process.
Note, because the transactions table is added to 1 record at a time by
an external process, the actual cursor loops in this case are usually
for a single record.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||> So how would you advise to select a single row from the inserted
table
> without using a cursor?
I *wouldn't* select a single row. The problem is precisely to AVOID
processing single rows of data and process the whole set of data at
once. This is what we mean by "set-based" code. By putting business
logic in a stored proc that opeartes only on one row at a time you have
forced yourself to call that proc once for each row. It likely doesn't
have to be that way but since you haven't explained what the proc does
I can't really advise on the alternatives.
--
David Portas
SQL Server MVP
--|||> If it's a new job I create a new record
INSERT INTO Jobs (job, ...)
SELECT job, ...
FROM Inserted
WHERE NOT EXISTS
(SELECT *
FROM Jobs
WHERE job = Inserted.job)
Note however that you should generally avoid duplicating data between
tables (except for key columns). Duplicated data is a problem in a
relational database and the goal of Normalization in db design is to
eliminate it. You should also aim to eliminate transitive dependencies
- i.e. columns that can always be derived from data in other (non-key
columns) - doing so reduces the need for triggers.
> otherwise I perform an update
UPDATE Jobs
SET ... ?
WHERE EXISTS
(SELECT *
FROM Inserted
WHERE job = Jobs.job AND ... ?)
> The nature of the update can depend
> on the value of the new status, so that depending on the status
> different values amongst the "other transaction fields" will cause
> different updated for the "other job fields".
That's not much information to go on but you could probably use CASE
expressions for this.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
>> If it's a new job I create a new record
>INSERT INTO Jobs (job, ...)
> SELECT job, ...
> FROM Inserted
> WHERE NOT EXISTS
> (SELECT *
> FROM Jobs
> WHERE job = Inserted.job)
Thanks. I begin to see how the procedural approach can be avoided.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||LOL,
yeah I guess you are right must have been late.|||John A Fotheringham (jafsoft@.gmail.com) writes:
> This is the only situation in which I've used a cursor so far.
> I have a trigger set on insert into one table, and for each inserted
> record I want to use it's contents to create and/or update the
> contents of a record in a second table.
> I've written a procedure to do the fairly complex update from one
> record to another, and I call that procedure from inside a fairly
> simple cursor loop that forms the main body of the trigger procedure.
> I'm not too worried about cursor overheads here, because in general
> only one record at a time is being inserted into the first table.
Normally, it is not a good idea ot have a cursor in a trigger, but if
you know your business well enough to be confident that one row-at-a-time
is the normal case, this sounds like a sound approach to me. From a
theoretical point of view, the trigger certainly could be improved. But as
long as the penalty for the cursor is low or non-existent, it seems very
difficult to justify spending time on a more complex solution.
That cannot be denied, if you want to encapsulate logic by putting
it in stored procedures, this is easier for scalar values than for
sets of values, since procedure parameters are scalar. It is possible
to work around this by sharing temp tables or similar, but only does
this increase complexity. You can also get recompilation issues that
are bad for performance.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
avoid using BCP for snapshot
sql2k sp3
Howdy kids. So, now that management has made up they're
minds(again, pulling my hair out now) Im back to part of
where I was yesterday. At least since then though Ive
learned a bit more about Replication and what will be
required to make it work in my environment.
Well you've all heard this first part a few times now. The
Subscriber has more columns than the Publisher. Im now
back to needing to do a snapshot to populate the
Subscriber. I can write Subscriber Insert and Update procs
to handle individual data modifications AFTER the initial
snapshot is done. But what about that initial snapshot? It
gets BCP'd into the Subscriber table which wont work here
beacuase of the different schemas. I know how to use
format files for normal BCP but it wont work for this
scenario. So, how to I do that initial snapshot without
using BCP? Im guessing DTS but am hoping to be able to use
the procs I have to create anyways to accomodate for the
different schema unless DTS is a whole lot faster?
TIA, ChrisR
post the schemas for the table on the publisher and subscriber here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:7f6c01c4848a$4c95b750$a601280a@.phx.gbl...
> sql2k sp3
> Howdy kids. So, now that management has made up they're
> minds(again, pulling my hair out now) Im back to part of
> where I was yesterday. At least since then though Ive
> learned a bit more about Replication and what will be
> required to make it work in my environment.
> Well you've all heard this first part a few times now. The
> Subscriber has more columns than the Publisher. Im now
> back to needing to do a snapshot to populate the
> Subscriber. I can write Subscriber Insert and Update procs
> to handle individual data modifications AFTER the initial
> snapshot is done. But what about that initial snapshot? It
> gets BCP'd into the Subscriber table which wont work here
> beacuase of the different schemas. I know how to use
> format files for normal BCP but it wont work for this
> scenario. So, how to I do that initial snapshot without
> using BCP? Im guessing DTS but am hoping to be able to use
> the procs I have to create anyways to accomodate for the
> different schema unless DTS is a whole lot faster?
> TIA, ChrisR
|||Chris,
the fastest way is BCP out the data, transfer it over to teh subscriber,
create the table there and then use BULK INSERT on the subscriber.
Alternatively, to make things easier, I would create the table on the
subscriber and use the view mentioned in your earlier post with a linked
server to insert the records. DTS would do the same thing if you used an
ExecuteSQL task. However if you are concerned about errors on the insert,
then I would use DTS - the transform data task with error logging.
HTH,
Paul Ibison
|||the fastest way is to dts it over. with bcp you have to bcp into the file
system and then bcp from the file system to the server.
for a single table with DTS is is blasted over as a rowset. For multiple
tables it is bcp'd into the file system and then bcp'd from the file system
to the server.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uZ9DqxIhEHA.1568@.TK2MSFTNGP09.phx.gbl...
> Chris,
> the fastest way is BCP out the data, transfer it over to teh subscriber,
> create the table there and then use BULK INSERT on the subscriber.
> Alternatively, to make things easier, I would create the table on the
> subscriber and use the view mentioned in your earlier post with a linked
> server to insert the records. DTS would do the same thing if you used an
> ExecuteSQL task. However if you are concerned about errors on the insert,
> then I would use DTS - the transform data task with error logging.
> HTH,
> Paul Ibison
>
|||Also included Insert proc for Subscriber. Is there a way I
can just use that IF its as quick as DTS?
--Publisher
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TransDate] [smalldatetime] NOT NULL ,
[TransAmt] [money] NOT NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--Subscriber
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--Insert proc on Subscriber
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure sp_msIns_TransDtl
@.TransDtlKey int ,
@.CustomerKey int ,
@.SerialNbr char (10) ,
@.TranCode char (4) ,
@.TransDate smalldatetime ,
@.TransAmt money ,
@.RefNbr char (23) ,
@.MerchName varchar (25) ,
@.City varchar (15) ,
@.State varchar (3) ,
@.RejectReason varchar (15) ,
@.PostDate datetime ,
@.CreateDate datetime ,
@.MerchSIC char (4)
as
declare
@.TransDateShort char (10) ,
@.TransDateMonth tinyint ,
@.TransDateYear smallint ,
@.PostDateShort char (10) ,
@.PostDateMonth tinyint ,
@.PostDateYear smallint
select @.TransDateShort = Convert(varchar(10), @.TransDate,
101)-- from TransDTL
select @.TransDateMonth = Month(@.TransDate)-- from TransDTL
select @.TransDateYear = Year(@.TransDate)-- from TransDTL
select @.PostDateShort = Convert(varchar(10), @.PostDate,
101)-- from TransDTL
select @.PostDateMonth = Month(@.PostDate)-- from TransDTL
select @.PostDateYear = Year(@.PostDate)-- from TransDTL
insert into TransDTL
(
TransDtlKey ,
CustomerKey ,
SerialNbr ,
TranCode ,
TransDate ,
TransDateShort ,
TransDateMonth ,
TransDateYear ,
TransAmt ,
RefNbr ,
MerchName ,
City ,
State ,
RejectReason ,
PostDate ,
PostDateShort ,
PostDateMonth ,
PostDateYear ,
CreateDate ,
MerchSIC
)
values
(
@.TransDtlKey ,
@.CustomerKey ,
@.SerialNbr ,
@.TranCode ,
@.TransDate ,
@.TransDateShort ,
@.TransDateMonth ,
@.TransDateYear ,
@.TransAmt ,
@.RefNbr ,
@.MerchName ,
@.City ,
@.State ,
@.RejectReason ,
@.PostDate ,
@.PostDateShort ,
@.PostDateMonth ,
@.PostDateYear ,
@.CreateDate ,
@.MerchSIC
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
>--Original Message--
>post the schemas for the table on the publisher and
subscriber here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7f6c01c4848a$4c95b750$a601280a@.phx.gbl...
The[vbcol=seagreen]
procs[vbcol=seagreen]
initial[vbcol=seagreen]
It[vbcol=seagreen]
here[vbcol=seagreen]
use
>
>.
>
|||Thanks Paul. I dont think Bulk Insert would work because
of the schema differences. Even with format files. Look @.
the insert proc in my reply to Hilary and it will make
more sense.
Thanks again.
>--Original Message--
>Chris,
>the fastest way is BCP out the data, transfer it over to
teh subscriber,
>create the table there and then use BULK INSERT on the
subscriber.
>Alternatively, to make things easier, I would create the
table on the
>subscriber and use the view mentioned in your earlier
post with a linked
>server to insert the records. DTS would do the same thing
if you used an
>ExecuteSQL task. However if you are concerned about
errors on the insert,
>then I would use DTS - the transform data task with error
logging.
>HTH,
>Paul Ibison
>
>.
>
|||Fair point - I'd done things like this because we also saved audit copies of
tables. The bcp files were zipped up before transferring, but I assume that
DTS is still faster in this case.
Finally, Insert Into... Select... using a linked server directly is
presumably faster?
Regards,
Paul Ibison
|||I am not sure about this. I'd have to test it. You can set up a DTS package
so that it will do a non logged insert which should be faster than the
insert select which is logged.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u3mklsJhEHA.1184@.TK2MSFTNGP12.phx.gbl...
> Fair point - I'd done things like this because we also saved audit copies
of
> tables. The bcp files were zipped up before transferring, but I assume
that
> DTS is still faster in this case.
> Finally, Insert Into... Select... using a linked server directly is
> presumably faster?
> Regards,
> Paul Ibison
>
|||you may be running into a bug. it looks like you have to use character mode
to handle the money data type correctly.
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
drop view CustomSyncObject
go
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
--inserting data into publisher
go
select * from transdtl
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--startup snapshot agent
select * from transdtl
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
select * from transdtl where transdtlkey=1
GO
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:7a2b01c4848f$8c8961e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Also included Insert proc for Subscriber. Is there a way I
> can just use that IF its as quick as DTS?
> --Publisher
> CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TranCode] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TransDate] [smalldatetime] NOT NULL ,
> [TransAmt] [money] NOT NULL ,
> [RefNbr] [char] (23) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MerchName] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [City] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL ,
> [MerchSIC] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> --Subscriber
> CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranCode] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> [TransAmt] [money] NULL ,
> [RefNbr] [char] (23) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MerchName] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NULL ,
> [PostDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDateMonth] [tinyint] NULL ,
> [PostDateYear] [smallint] NULL ,
> [CreateDate] [datetime] NULL ,
> [MerchSIC] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> --Insert proc on Subscriber
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER procedure sp_msIns_TransDtl
> @.TransDtlKey int ,
> @.CustomerKey int ,
> @.SerialNbr char (10) ,
> @.TranCode char (4) ,
> @.TransDate smalldatetime ,
> @.TransAmt money ,
> @.RefNbr char (23) ,
> @.MerchName varchar (25) ,
> @.City varchar (15) ,
> @.State varchar (3) ,
> @.RejectReason varchar (15) ,
> @.PostDate datetime ,
> @.CreateDate datetime ,
> @.MerchSIC char (4)
> as
> declare
> @.TransDateShort char (10) ,
> @.TransDateMonth tinyint ,
> @.TransDateYear smallint ,
> @.PostDateShort char (10) ,
> @.PostDateMonth tinyint ,
> @.PostDateYear smallint
>
> select @.TransDateShort = Convert(varchar(10), @.TransDate,
> 101)-- from TransDTL
> select @.TransDateMonth = Month(@.TransDate)-- from TransDTL
> select @.TransDateYear = Year(@.TransDate)-- from TransDTL
> select @.PostDateShort = Convert(varchar(10), @.PostDate,
> 101)-- from TransDTL
> select @.PostDateMonth = Month(@.PostDate)-- from TransDTL
> select @.PostDateYear = Year(@.PostDate)-- from TransDTL
> insert into TransDTL
> (
> TransDtlKey ,
> CustomerKey ,
> SerialNbr ,
> TranCode ,
> TransDate ,
> TransDateShort ,
> TransDateMonth ,
> TransDateYear ,
> TransAmt ,
> RefNbr ,
> MerchName ,
> City ,
> State ,
> RejectReason ,
> PostDate ,
> PostDateShort ,
> PostDateMonth ,
> PostDateYear ,
> CreateDate ,
> MerchSIC
> )
> values
> (
> @.TransDtlKey ,
> @.CustomerKey ,
> @.SerialNbr ,
> @.TranCode ,
> @.TransDate ,
> @.TransDateShort ,
> @.TransDateMonth ,
> @.TransDateYear ,
> @.TransAmt ,
> @.RefNbr ,
> @.MerchName ,
> @.City ,
> @.State ,
> @.RejectReason ,
> @.PostDate ,
> @.PostDateShort ,
> @.PostDateMonth ,
> @.PostDateYear ,
> @.CreateDate ,
> @.MerchSIC
> )
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
>
>
>
>
> subscriber here.
> message
> The
> procs
> initial
> It
> here
> use
Howdy kids. So, now that management has made up they're
minds(again, pulling my hair out now) Im back to part of
where I was yesterday. At least since then though Ive
learned a bit more about Replication and what will be
required to make it work in my environment.
Well you've all heard this first part a few times now. The
Subscriber has more columns than the Publisher. Im now
back to needing to do a snapshot to populate the
Subscriber. I can write Subscriber Insert and Update procs
to handle individual data modifications AFTER the initial
snapshot is done. But what about that initial snapshot? It
gets BCP'd into the Subscriber table which wont work here
beacuase of the different schemas. I know how to use
format files for normal BCP but it wont work for this
scenario. So, how to I do that initial snapshot without
using BCP? Im guessing DTS but am hoping to be able to use
the procs I have to create anyways to accomodate for the
different schema unless DTS is a whole lot faster?
TIA, ChrisR
post the schemas for the table on the publisher and subscriber here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:7f6c01c4848a$4c95b750$a601280a@.phx.gbl...
> sql2k sp3
> Howdy kids. So, now that management has made up they're
> minds(again, pulling my hair out now) Im back to part of
> where I was yesterday. At least since then though Ive
> learned a bit more about Replication and what will be
> required to make it work in my environment.
> Well you've all heard this first part a few times now. The
> Subscriber has more columns than the Publisher. Im now
> back to needing to do a snapshot to populate the
> Subscriber. I can write Subscriber Insert and Update procs
> to handle individual data modifications AFTER the initial
> snapshot is done. But what about that initial snapshot? It
> gets BCP'd into the Subscriber table which wont work here
> beacuase of the different schemas. I know how to use
> format files for normal BCP but it wont work for this
> scenario. So, how to I do that initial snapshot without
> using BCP? Im guessing DTS but am hoping to be able to use
> the procs I have to create anyways to accomodate for the
> different schema unless DTS is a whole lot faster?
> TIA, ChrisR
|||Chris,
the fastest way is BCP out the data, transfer it over to teh subscriber,
create the table there and then use BULK INSERT on the subscriber.
Alternatively, to make things easier, I would create the table on the
subscriber and use the view mentioned in your earlier post with a linked
server to insert the records. DTS would do the same thing if you used an
ExecuteSQL task. However if you are concerned about errors on the insert,
then I would use DTS - the transform data task with error logging.
HTH,
Paul Ibison
|||the fastest way is to dts it over. with bcp you have to bcp into the file
system and then bcp from the file system to the server.
for a single table with DTS is is blasted over as a rowset. For multiple
tables it is bcp'd into the file system and then bcp'd from the file system
to the server.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uZ9DqxIhEHA.1568@.TK2MSFTNGP09.phx.gbl...
> Chris,
> the fastest way is BCP out the data, transfer it over to teh subscriber,
> create the table there and then use BULK INSERT on the subscriber.
> Alternatively, to make things easier, I would create the table on the
> subscriber and use the view mentioned in your earlier post with a linked
> server to insert the records. DTS would do the same thing if you used an
> ExecuteSQL task. However if you are concerned about errors on the insert,
> then I would use DTS - the transform data task with error logging.
> HTH,
> Paul Ibison
>
|||Also included Insert proc for Subscriber. Is there a way I
can just use that IF its as quick as DTS?
--Publisher
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TransDate] [smalldatetime] NOT NULL ,
[TransAmt] [money] NOT NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NOT NULL ,
[CreateDate] [datetime] NOT NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--Subscriber
CREATE TABLE [dbo].[TransDtl] (
[TransDtlKey] [int] NOT NULL ,
[CustomerKey] [int] NULL ,
[SerialNbr] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TranCode] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDate] [smalldatetime] NULL ,
[TransDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[TransDateMonth] [tinyint] NULL ,
[TransDateYear] [smallint] NULL ,
[TransAmt] [money] NULL ,
[RefNbr] [char] (23) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MerchName] [varchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (3) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[RejectReason] [varchar] (15) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDate] [datetime] NULL ,
[PostDateShort] [char] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[PostDateMonth] [tinyint] NULL ,
[PostDateYear] [smallint] NULL ,
[CreateDate] [datetime] NULL ,
[MerchSIC] [char] (4) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--Insert proc on Subscriber
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER procedure sp_msIns_TransDtl
@.TransDtlKey int ,
@.CustomerKey int ,
@.SerialNbr char (10) ,
@.TranCode char (4) ,
@.TransDate smalldatetime ,
@.TransAmt money ,
@.RefNbr char (23) ,
@.MerchName varchar (25) ,
@.City varchar (15) ,
@.State varchar (3) ,
@.RejectReason varchar (15) ,
@.PostDate datetime ,
@.CreateDate datetime ,
@.MerchSIC char (4)
as
declare
@.TransDateShort char (10) ,
@.TransDateMonth tinyint ,
@.TransDateYear smallint ,
@.PostDateShort char (10) ,
@.PostDateMonth tinyint ,
@.PostDateYear smallint
select @.TransDateShort = Convert(varchar(10), @.TransDate,
101)-- from TransDTL
select @.TransDateMonth = Month(@.TransDate)-- from TransDTL
select @.TransDateYear = Year(@.TransDate)-- from TransDTL
select @.PostDateShort = Convert(varchar(10), @.PostDate,
101)-- from TransDTL
select @.PostDateMonth = Month(@.PostDate)-- from TransDTL
select @.PostDateYear = Year(@.PostDate)-- from TransDTL
insert into TransDTL
(
TransDtlKey ,
CustomerKey ,
SerialNbr ,
TranCode ,
TransDate ,
TransDateShort ,
TransDateMonth ,
TransDateYear ,
TransAmt ,
RefNbr ,
MerchName ,
City ,
State ,
RejectReason ,
PostDate ,
PostDateShort ,
PostDateMonth ,
PostDateYear ,
CreateDate ,
MerchSIC
)
values
(
@.TransDtlKey ,
@.CustomerKey ,
@.SerialNbr ,
@.TranCode ,
@.TransDate ,
@.TransDateShort ,
@.TransDateMonth ,
@.TransDateYear ,
@.TransAmt ,
@.RefNbr ,
@.MerchName ,
@.City ,
@.State ,
@.RejectReason ,
@.PostDate ,
@.PostDateShort ,
@.PostDateMonth ,
@.PostDateYear ,
@.CreateDate ,
@.MerchSIC
)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
>--Original Message--
>post the schemas for the table on the publisher and
subscriber here.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:7f6c01c4848a$4c95b750$a601280a@.phx.gbl...
The[vbcol=seagreen]
procs[vbcol=seagreen]
initial[vbcol=seagreen]
It[vbcol=seagreen]
here[vbcol=seagreen]
use
>
>.
>
|||Thanks Paul. I dont think Bulk Insert would work because
of the schema differences. Even with format files. Look @.
the insert proc in my reply to Hilary and it will make
more sense.
Thanks again.
>--Original Message--
>Chris,
>the fastest way is BCP out the data, transfer it over to
teh subscriber,
>create the table there and then use BULK INSERT on the
subscriber.
>Alternatively, to make things easier, I would create the
table on the
>subscriber and use the view mentioned in your earlier
post with a linked
>server to insert the records. DTS would do the same thing
if you used an
>ExecuteSQL task. However if you are concerned about
errors on the insert,
>then I would use DTS - the transform data task with error
logging.
>HTH,
>Paul Ibison
>
>.
>
|||Fair point - I'd done things like this because we also saved audit copies of
tables. The bcp files were zipped up before transferring, but I assume that
DTS is still faster in this case.
Finally, Insert Into... Select... using a linked server directly is
presumably faster?
Regards,
Paul Ibison
|||I am not sure about this. I'd have to test it. You can set up a DTS package
so that it will do a non logged insert which should be faster than the
insert select which is logged.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:u3mklsJhEHA.1184@.TK2MSFTNGP12.phx.gbl...
> Fair point - I'd done things like this because we also saved audit copies
of
> tables. The bcp files were zipped up before transferring, but I assume
that
> DTS is still faster in this case.
> Finally, Insert Into... Select... using a linked server directly is
> presumably faster?
> Regards,
> Paul Ibison
>
|||you may be running into a bug. it looks like you have to use character mode
to handle the money data type correctly.
create database ChrisR
go
create database ChrisRSub
go
use ChrisR
go
CREATE TABLE TransDtl
(
TransDtlKey int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
CustomerKey int NULL,
SerialNbr char(10),
TranCode char(4),
TransDate smalldatetime NOT NULL,
TransAmt money NOT NULL,
RefNbr char(23),
MerchName varchar(25),
City varchar(15),
State varchar(3),
RejectReason varchar(15),
PostDate datetime NOT NULL,
CreateDate datetime NOT NULL,
MerchSIC char(4)
)
GO
use chrisr
GO
drop view CustomSyncObject
go
Create View CustomSyncObject
as
select TransDtlKey=convert(int,TransDtlKey),
CustomerKey=convert(int,CustomerKey),
SerialNbr=convert(char(10),SerialNbr),
TranCode=convert(char(4),TranCode),
TransDate=convert(smalldatetime,TransDate),
TransDateShort = Convert(varchar(10),TransDate, 101),
TransDateMonth = convert(tinyint,Month(TransDate)),
TransDateYear = convert(smallint,Year(TransDate)),
TransAmt=convert(money, TransAmt),
RefNbr, MerchName, City, State, RejectReason, PostDate,
PostDateShort = Convert(varchar(10), PostDate, 101),
PostDateMonth = convert(tinyint, Month(PostDate)),
PostDateYear=convert(smallint, Year(PostDate)),
CreateDate,
MerchSIC from TransDtl
GO
sp_dboption 'ChrisR','published','true'
go
sp_addpublication 'ChrisR',@.status='active', @.sync_method = N'character'
go
sp_addpublication_snapshot 'ChrisR'
go
sp_addarticle @.publication = 'ChrisR',
@.article = 'TransDTL',
@.source_object = 'TransDTL',
@.destination_table = 'TransDTL',
@.type = 'logbased manualview',
@.sync_object='CustomSyncObject',
@.creation_script = 'c:\temp\TransDTL.sql',
@.pre_creation_cmd = 'delete',
@.schema_option = 0x0,
@.status = 8,
@.ins_cmd = 'CALL sp_MSins_TransDTL',
@.del_cmd = 'CALL sp_MSdel_TransDTL',
@.upd_cmd = 'MCALL sp_MSupd_TransDTL'
GO
use chrisRsub
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSins_TransDTL') drop proc [sp_MSins_TransDTL]
go
create procedure [sp_MSins_TransDTL] @.c1 int,@.c2 int,@.c3 char(10),@.c4
char(4),@.c5 smalldatetime,@.c6 money,@.c7 char(23),@.c8 varchar(25),@.c9
varchar(15),@.c10 varchar(3),@.c11 varchar(15),@.c12 datetime,@.c13
datetime,@.c14 char(4)
AS
BEGIN
insert into [TransDTL](
[TransDtlKey], [CustomerKey], [SerialNbr], [TranCode],
[TransDate],[TransDateShort],
[TransDateMonth], [TransDateYear], [TransAmt], [RefNbr], [MerchName],
[City], [State],
[RejectReason], [PostDate],[PostDateShort],[PostDateMonth],[PostDateYear],
[CreateDate], [MerchSIC]
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, Convert(varchar(10), @.c5, 101), Month(@.c5),
Year(@.c5),
@.c6, @.c7, @.c8, @.c9, @.c10, @.c11, @.c12, Convert(varchar(10), @.c12, 101),
Month(@.c12), Year(@.c12), @.c13, @.c14
)
END
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSupd_TransDTL') drop proc [sp_MSupd_TransDTL]
go
create procedure [sp_MSupd_TransDTL]
@.c1 int,@.c2 int,@.c3 char(10),@.c4 char(4),@.c5 smalldatetime,@.c6 money,@.c7
char(23),@.c8 varchar(25),@.c9 varchar(15),@.c10 varchar(3),@.c11
varchar(15),@.c12 datetime,@.c13 datetime,@.c14 char(4),@.pkc1 int
,@.bitmap binary(2)
as
if substring(@.bitmap,1,1) & 1 = 1
begin
update [TransDTL] set
[TransDtlKey] = case substring(@.bitmap,1,1) & 1 when 1 then @.c1 else
[TransDtlKey] end
,[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
else
begin
update [TransDTL] set
[CustomerKey] = case substring(@.bitmap,1,1) & 2 when 2 then @.c2 else
[CustomerKey] end
,[SerialNbr] = case substring(@.bitmap,1,1) & 4 when 4 then @.c3 else
[SerialNbr] end
,[TranCode] = case substring(@.bitmap,1,1) & 8 when 8 then @.c4 else
[TranCode] end
,[TransDate] = case substring(@.bitmap,1,1) & 16 when 16 then @.c5 else
[TransDate] end
,[TransDateShort]= case substring(@.bitmap,1,1) & 16 when 16 then
Convert(varchar(10), @.c5, 101) else [TransDateShort] end
,[TransDateMonth]= case substring(@.bitmap,1,1) & 16 when 16 then Month(@.c5)
else [TransDateMonth] end
,[TransDateYear]= case substring(@.bitmap,1,1) & 16 when 16 then Year(@.c5)
else [TransDateYear] end
,[TransAmt] = case substring(@.bitmap,1,1) & 32 when 32 then @.c6 else
[TransAmt] end
,[RefNbr] = case substring(@.bitmap,1,1) & 64 when 64 then @.c7 else [RefNbr]
end
,[MerchName] = case substring(@.bitmap,1,1) & 128 when 128 then @.c8 else
[MerchName] end
,[City] = case substring(@.bitmap,2,1) & 1 when 1 then @.c9 else [City] end
,[State] = case substring(@.bitmap,2,1) & 2 when 2 then @.c10 else [State] end
,[RejectReason] = case substring(@.bitmap,2,1) & 4 when 4 then @.c11 else
[RejectReason] end
,[PostDate] = case substring(@.bitmap,2,1) & 8 when 8 then @.c12 else
[PostDate] end
,[PostDateShort] = case substring(@.bitmap,2,1) & 8 when 8 then
Convert(varchar(10), @.c12, 101) else [PostDateShort] end
,[PostDateMonth] = case substring(@.bitmap,2,1) & 8 when 8 then Month(@.c12)
else [PostDateMonth] end
,[PostDateYear] = case substring(@.bitmap,2,1) & 8 when 8 then Year(@.c12)
else [PostDateYear] end
,[CreateDate] = case substring(@.bitmap,2,1) & 16 when 16 then @.c13 else
[CreateDate] end
,[MerchSIC] = case substring(@.bitmap,2,1) & 32 when 32 then @.c14 else
[MerchSIC] end
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
if exists (select * from sysobjects where type = 'P' and name =
'sp_MSdel_TransDTL') drop proc [sp_MSdel_TransDTL]
go
create procedure [sp_MSdel_TransDTL] @.pkc1 int
as
delete [TransDTL]
where [TransDtlKey] = @.pkc1
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
go
use ChrisR
--inserting data into publisher
go
select * from transdtl
declare @.counter int
set @.counter=1
while @.counter < 100
begin
insert into transdtl
(CustomerKey,SerialNbr,TranCode,TransDate,TransAmt ,RefNbr,MerchName,
City,State,RejectReason,PostDate,CreateDate,MerchS IC)
values
(@.counter,'t2','t3',getdate()-@.counter,@.counter+1,@.counter+2,'t4','t5','CA',
't6',getdate()-@.counter*10,getdate()-@.counter*100, 't7')
select @.counter=@.counter+1
end
go
--startup snapshot agent
select * from transdtl
use ChrisR
GO
update transdtl
set transdate=getdate()+365
where transdtlkey=1
go
select * from transdtl where transdtlkey=1
GO
use chrisRsub
select * from transdtl where transdtlkey=1
GO
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:7a2b01c4848f$8c8961e0$a501280a@.phx.gbl...[vbcol=seagreen]
> Also included Insert proc for Subscriber. Is there a way I
> can just use that IF its as quick as DTS?
> --Publisher
> CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] IDENTITY (1, 1) NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TranCode] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [TransDate] [smalldatetime] NOT NULL ,
> [TransAmt] [money] NOT NULL ,
> [RefNbr] [char] (23) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MerchName] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [City] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NOT NULL ,
> [CreateDate] [datetime] NOT NULL ,
> [MerchSIC] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
>
> --Subscriber
> CREATE TABLE [dbo].[TransDtl] (
> [TransDtlKey] [int] NOT NULL ,
> [CustomerKey] [int] NULL ,
> [SerialNbr] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TranCode] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDate] [smalldatetime] NULL ,
> [TransDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [TransDateMonth] [tinyint] NULL ,
> [TransDateYear] [smallint] NULL ,
> [TransAmt] [money] NULL ,
> [RefNbr] [char] (23) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [MerchName] [varchar] (25) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [City] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [State] [varchar] (3) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [RejectReason] [varchar] (15) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDate] [datetime] NULL ,
> [PostDateShort] [char] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [PostDateMonth] [tinyint] NULL ,
> [PostDateYear] [smallint] NULL ,
> [CreateDate] [datetime] NULL ,
> [MerchSIC] [char] (4) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> --Insert proc on Subscriber
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER procedure sp_msIns_TransDtl
> @.TransDtlKey int ,
> @.CustomerKey int ,
> @.SerialNbr char (10) ,
> @.TranCode char (4) ,
> @.TransDate smalldatetime ,
> @.TransAmt money ,
> @.RefNbr char (23) ,
> @.MerchName varchar (25) ,
> @.City varchar (15) ,
> @.State varchar (3) ,
> @.RejectReason varchar (15) ,
> @.PostDate datetime ,
> @.CreateDate datetime ,
> @.MerchSIC char (4)
> as
> declare
> @.TransDateShort char (10) ,
> @.TransDateMonth tinyint ,
> @.TransDateYear smallint ,
> @.PostDateShort char (10) ,
> @.PostDateMonth tinyint ,
> @.PostDateYear smallint
>
> select @.TransDateShort = Convert(varchar(10), @.TransDate,
> 101)-- from TransDTL
> select @.TransDateMonth = Month(@.TransDate)-- from TransDTL
> select @.TransDateYear = Year(@.TransDate)-- from TransDTL
> select @.PostDateShort = Convert(varchar(10), @.PostDate,
> 101)-- from TransDTL
> select @.PostDateMonth = Month(@.PostDate)-- from TransDTL
> select @.PostDateYear = Year(@.PostDate)-- from TransDTL
> insert into TransDTL
> (
> TransDtlKey ,
> CustomerKey ,
> SerialNbr ,
> TranCode ,
> TransDate ,
> TransDateShort ,
> TransDateMonth ,
> TransDateYear ,
> TransAmt ,
> RefNbr ,
> MerchName ,
> City ,
> State ,
> RejectReason ,
> PostDate ,
> PostDateShort ,
> PostDateMonth ,
> PostDateYear ,
> CreateDate ,
> MerchSIC
> )
> values
> (
> @.TransDtlKey ,
> @.CustomerKey ,
> @.SerialNbr ,
> @.TranCode ,
> @.TransDate ,
> @.TransDateShort ,
> @.TransDateMonth ,
> @.TransDateYear ,
> @.TransAmt ,
> @.RefNbr ,
> @.MerchName ,
> @.City ,
> @.State ,
> @.RejectReason ,
> @.PostDate ,
> @.PostDateShort ,
> @.PostDateMonth ,
> @.PostDateYear ,
> @.CreateDate ,
> @.MerchSIC
> )
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
>
>
>
>
> subscriber here.
> message
> The
> procs
> initial
> It
> here
> use
Avoid update without where clause
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
No, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =
at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference=20
=20
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =
transaction mode. When OFF, it returns the connection to autocommit =
transaction mode.
When a connection is in implicit transaction mode and the connection is =
not currently in a transaction, executing any of the following =
statements starts a transaction:
--=20
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
>=20
> Using SQL 7.0,
>=20
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where=20
> clause ? =20
>=20
> In order to avoid devastating lapse of memory :o\
>=20
> Thanks !
>=20
> Donald
|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sq...y_triggers.htm
but may not be completely safe.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
|||I agree with Mark completely.
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
Then the DELETE statement.
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
Then we use a "-- ROLLBACK COMMIT" as the final line.
This is commented out so that you have to DBLCLICK on the keyword that you want to use.
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened?". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...
|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>
>.
>
|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message[vbcol=seagreen]
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
on the live[vbcol=seagreen]
cocked it up,[vbcol=seagreen]
changes to[vbcol=seagreen]
void
>
>.
>
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
No, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =
at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference=20
=20
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =
transaction mode. When OFF, it returns the connection to autocommit =
transaction mode.
When a connection is in implicit transaction mode and the connection is =
not currently in a transaction, executing any of the following =
statements starts a transaction:
--=20
Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
>=20
> Using SQL 7.0,
>=20
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where=20
> clause ? =20
>=20
> In order to avoid devastating lapse of memory :o\
>=20
> Thanks !
>=20
> Donald
|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sq...y_triggers.htm
but may not be completely safe.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald
|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald
|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
|||I agree with Mark completely.
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
Then the DELETE statement.
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
Then we use a "-- ROLLBACK COMMIT" as the final line.
This is commented out so that you have to DBLCLICK on the keyword that you want to use.
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened?". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...
|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>
>.
>
|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message[vbcol=seagreen]
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
on the live[vbcol=seagreen]
cocked it up,[vbcol=seagreen]
changes to[vbcol=seagreen]
void
>
>.
>
Avoid update without where clause
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
DonaldNo, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =transaction mode. When OFF, it returns the connection to autocommit =transaction mode.
When a connection is in implicit transaction mode and the connection is =not currently in a transaction, executing any of the following =statements starts a transaction:
-- Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> > Using SQL 7.0,
> > Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where > clause ? > > In order to avoid devastating lapse of memory :o\
> > Thanks !
> > Donald|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
but may not be completely safe.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
> > Hi every one,
> >
> > Using SQL 7.0,
> >
> > Without using transaction, is there a way that SQL void
> > (refuse) any update or delete if there is no where
> > clause ?
> >
> > In order to avoid devastating lapse of memory :o\
> >
> > Thanks !
> >
> > Donald|||I agree with Mark completely
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*)
Then the DELETE statement
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted
Then we use a "-- ROLLBACK COMMIT" as the final line
This is commented out so that you have to DBLCLICK on the keyword that you want to use
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>> Hi every one,
>> Using SQL 7.0,
>> Without using transaction, is there a way that SQL void
>> (refuse) any update or delete if there is no where
>> clause ?
>> In order to avoid devastating lapse of memory :o\
>> Thanks !
>> Donald
>
>.
>|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
>> Donald,
>> You could get into the habit of always executing things
on the live
>> servers like this:
>> BEGIN TRAN
>> <your DML code>
>> <some select statements to verify your DML code>
>> When you are satisfied, issue a COMMIT TRAN, or if you
cocked it up,
>> issue a ROLLBACK TRAN. I always do this when making
changes to
>> production, even if they have been scripted and tested.
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Donald wrote:
>> > Hi every one,
>> >
>> > Using SQL 7.0,
>> >
>> > Without using transaction, is there a way that SQL
void
>> > (refuse) any update or delete if there is no where
>> > clause ?
>> >
>> > In order to avoid devastating lapse of memory :o\
>> >
>> > Thanks !
>> >
>> > Donald
>
>.
>|||One way I achieved this in the past was to create triggers
on the table(s) in question and make sure the rowcount
wasn't over a certain threshold. Some tables might only
have 1 row updated at a time and never more than that,
others maybe 5 or 10. So I made the trigger check the
rowcount for this.
Van
>--Original Message--
>Hi every one,
>Using SQL 7.0,
>Without using transaction, is there a way that SQL void
>(refuse) any update or delete if there is no where
>clause ?
>In order to avoid devastating lapse of memory :o\
>Thanks !
>Donald
>.
>|||Very good points Steve! I agree with you completely too, and you put it
much better than I did.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> I agree with Mark completely.
> We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
> Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
> Then the DELETE statement.
> Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
> Then we use a "-- ROLLBACK COMMIT" as the final line.
> This is commented out so that you have to DBLCLICK on the keyword that you want to use.
> Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
> We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
DonaldNo, this is a case where your developers must use common sense, and look
over their scripts before executing them.
If it's any consolation, SQL Server also won't prevent you from tripping
over the power cord or using a sledgehammer against the CPU. ;-)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Pretty much your only option is SET IMPLICIT_TRANSACTIONS. This is set =at the connection level
A snip from Books Online (within the SQL Server program group):
Transact-SQL Reference
SET IMPLICIT_TRANSACTIONS
Sets implicit transaction mode for the connection.
Syntax
SET IMPLICIT_TRANSACTIONS { ON | OFF }
Remarks
When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit =transaction mode. When OFF, it returns the connection to autocommit =transaction mode.
When a connection is in implicit transaction mode and the connection is =not currently in a transaction, executing any of the following =statements starts a transaction:
-- Keith
"Donald" <anonymous@.discussions.microsoft.com> wrote in message =news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> > Using SQL 7.0,
> > Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where > clause ? > > In order to avoid devastating lapse of memory :o\
> > Thanks !
> > Donald|||Actually, I guess you could enforce this by preventing direct access to the
table and forcing access via stored procedures. Depending on how flexible
the where clause can be, you may need to read these articles;
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||Would make a handy feature, but there's isn't anything like that build-in at
the moment.
You could achive this by using triggers, for example:
http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
but may not be completely safe.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:1383601c443ff$12727930$a001280a@.phx.gbl...
Hi every one,
Using SQL 7.0,
Without using transaction, is there a way that SQL void
(refuse) any update or delete if there is no where
clause ?
In order to avoid devastating lapse of memory :o\
Thanks !
Donald|||Donald,
You could get into the habit of always executing things on the live
servers like this:
BEGIN TRAN
<your DML code>
<some select statements to verify your DML code>
When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
issue a ROLLBACK TRAN. I always do this when making changes to
production, even if they have been scripted and tested.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Donald wrote:
> Hi every one,
> Using SQL 7.0,
> Without using transaction, is there a way that SQL void
> (refuse) any update or delete if there is no where
> clause ?
> In order to avoid devastating lapse of memory :o\
> Thanks !
> Donald|||It didn't sound like he was worried about having to roll back the update or
delete, I think he was worried about preventing users from locking up the
server by trying to act on the whole table?
Maybe I read it wrong...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
> Donald,
> You could get into the habit of always executing things on the live
> servers like this:
> BEGIN TRAN
> <your DML code>
> <some select statements to verify your DML code>
> When you are satisfied, issue a COMMIT TRAN, or if you cocked it up,
> issue a ROLLBACK TRAN. I always do this when making changes to
> production, even if they have been scripted and tested.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Donald wrote:
> > Hi every one,
> >
> > Using SQL 7.0,
> >
> > Without using transaction, is there a way that SQL void
> > (refuse) any update or delete if there is no where
> > clause ?
> >
> > In order to avoid devastating lapse of memory :o\
> >
> > Thanks !
> >
> > Donald|||I agree with Mark completely
We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action
Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*)
Then the DELETE statement
Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted
Then we use a "-- ROLLBACK COMMIT" as the final line
This is commented out so that you have to DBLCLICK on the keyword that you want to use
Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count
We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...|||Good answer, i like the sledgehammer example
Thank you
>--Original Message--
>No, this is a case where your developers must use common
sense, and look
>over their scripts before executing them.
>If it's any consolation, SQL Server also won't prevent
you from tripping
>over the power cord or using a sledgehammer against the
CPU. ;-)
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Donald" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1383601c443ff$12727930$a001280a@.phx.gbl...
>> Hi every one,
>> Using SQL 7.0,
>> Without using transaction, is there a way that SQL void
>> (refuse) any update or delete if there is no where
>> clause ?
>> In order to avoid devastating lapse of memory :o\
>> Thanks !
>> Donald
>
>.
>|||I just want to prevent massive update or delete from ME!
I often have to update tables in production to fix data
from stupid VB applications
I was just wondering if it was possible without using
transactions. But i know, i should use TRANS.
As Narayana said : Would make a handy feature
Thank you for your help
Donald
>--Original Message--
>It didn't sound like he was worried about having to roll
back the update or
>delete, I think he was worried about preventing users
from locking up the
>server by trying to act on the whole table?
>Maybe I read it wrong...
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in
message
>news:eaZ#8XAREHA.2032@.TK2MSFTNGP11.phx.gbl...
>> Donald,
>> You could get into the habit of always executing things
on the live
>> servers like this:
>> BEGIN TRAN
>> <your DML code>
>> <some select statements to verify your DML code>
>> When you are satisfied, issue a COMMIT TRAN, or if you
cocked it up,
>> issue a ROLLBACK TRAN. I always do this when making
changes to
>> production, even if they have been scripted and tested.
>> --
>> Mark Allison, SQL Server MVP
>> http://www.markallison.co.uk
>> Donald wrote:
>> > Hi every one,
>> >
>> > Using SQL 7.0,
>> >
>> > Without using transaction, is there a way that SQL
void
>> > (refuse) any update or delete if there is no where
>> > clause ?
>> >
>> > In order to avoid devastating lapse of memory :o\
>> >
>> > Thanks !
>> >
>> > Donald
>
>.
>|||One way I achieved this in the past was to create triggers
on the table(s) in question and make sure the rowcount
wasn't over a certain threshold. Some tables might only
have 1 row updated at a time and never more than that,
others maybe 5 or 10. So I made the trigger check the
rowcount for this.
Van
>--Original Message--
>Hi every one,
>Using SQL 7.0,
>Without using transaction, is there a way that SQL void
>(refuse) any update or delete if there is no where
>clause ?
>In order to avoid devastating lapse of memory :o\
>Thanks !
>Donald
>.
>|||Very good points Steve! I agree with you completely too, and you put it
much better than I did.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Steve Z wrote:
> I agree with Mark completely.
> We are in the habit of always starting a query for delete with a BEGIN TRAN. Especially when doing the work in PRODUCTION - but regardless - on the TEST BOX it's needed so you can be satisfied with your query action.
> Then a SELECT statement to get us happy with the recordcount. Either "details" or a COUNT(*).
> Then the DELETE statement.
> Then sometimes another SELECT statement to prove that all the records from the first SELECT really got deleted.
> Then we use a "-- ROLLBACK COMMIT" as the final line.
> This is commented out so that you have to DBLCLICK on the keyword that you want to use.
> Carefully review the RESULTS PANEL - check the counts. With the way "poor" joins can increase row presentation, this is extremely important - you might have more ROWS in the "FIRST SELECT" then in the actual DELETE row count.
> We even save these AD HOC queries just to CYA when the folks up top ask "what just happened'". Sometimes even save the QUERY DATA GRID to a NOTEPAD file for proof...
Subscribe to:
Posts (Atom)