This query seems to run particularly slowly, is there a way to rewrite
it without using IN?
id fruit_name
14 Apple
34 Apple
37 Banana
56 Banana
66 Banana
78 Pear
103 Orange
select * from my_table
where id in (
select max(id)
from my_table
group by fruit_name )
Result
==================
id fruit_name
34 Apple
66 Banana
78 Pear
103 Orange
Cheers!SELECT A.* FROM MyTable A
INNER JOIN
(SELECT max(id) as Id FROM MyTable) B
ON A.id = B.Id
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
<davidol@.hushmail.com> wrote in message
news:1109567370.806709.130280@.o13g2000cwo.googlegroups.com...
> This query seems to run particularly slowly, is there a way to rewrite
> it without using IN?
> id fruit_name
> 14 Apple
> 34 Apple
> 37 Banana
> 56 Banana
> 66 Banana
> 78 Pear
> 103 Orange
> select * from my_table
> where id in (
> select max(id)
> from my_table
> group by fruit_name )
> Result
> ==================
> id fruit_name
> 34 Apple
> 66 Banana
> 78 Pear
> 103 Orange
>
> Cheers!
>|||An index on (fruit_name) or perhaps (fruit_name, id) or (fruit_name, id
desc)
will help, I think.
You can also try some alternative was of writing this.
select fruit_name, max(id)
from my_table
group by fruit_name
select * from my_table
where not exists (
select * from my_table as mtcopy
where mtcopy.fruit_name = my_table.fruit_name
and mtcopy.id > my_table.id
)
select fruit_name, id
from my_table
where id = (
select top 1 id
from my_table as mtcopy
where mtcopy.fruit_name = my_table.fruit_name
order by id desc
)
Steve Kass
Drew University
davidol@.hushmail.com wrote:
>This query seems to run particularly slowly, is there a way to rewrite
>it without using IN?
>id fruit_name
>14 Apple
>34 Apple
>37 Banana
>56 Banana
>66 Banana
>78 Pear
>103 Orange
> select * from my_table
> where id in (
> select max(id)
> from my_table
> group by fruit_name )
>Result
>==================
>id fruit_name
>34 Apple
>66 Banana
>78 Pear
>103 Orange
>
>Cheers!
>
>|||SELECT MAX(id), fruit_name
FROM my_table
GROUP BY fruit_name
However, this probably won't do much to speed up the query. Can you give
actual DDL, including any indexes? (See http://www.aspfaq.com/5006
On 2/28/05 12:09 AM, in article
1109567370.806709.130280@.o13g2000cwo.googlegroups.com,
"davidol@.hushmail.com" <davidol@.hushmail.com> wrote:
> This query seems to run particularly slowly, is there a way to rewrite
> it without using IN?
> id fruit_name
> 14 Apple
> 34 Apple
> 37 Banana
> 56 Banana
> 66 Banana
> 78 Pear
> 103 Orange
> select * from my_table
> where id in (
> select max(id)
> from my_table
> group by fruit_name )
> Result
> ==================
> id fruit_name
> 34 Apple
> 66 Banana
> 78 Pear
> 103 Orange
>
> Cheers!
>
Showing posts with label avoiding. Show all posts
Showing posts with label avoiding. Show all posts
Saturday, February 25, 2012
Avoiding truncate error
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.
>
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.
>
Avoiding transaction logging - at least reducing it
I have an application that uses work tables within an SP to generate a recordset output to the caller as the result of a search operation. The SP does not update the main database, and the results, although sizeable, are not saved.
Clearly I do not need to log the updates to the work tables. They start out empty and the contents could be discarded.
Does SQL Server recognise this and not log the updates to work tables? Is there any way I can indicate that a table is not significant and need not be logged at all?
Would it help to put the work tables in a separate database?
Maybe it would not make much difference anyway?
Thanks in advance for any helpFortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.
A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !|||Originally posted by fadace
Fortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.
A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !
Huh? Well I'm glad you didn't become very specific about what you're talking about.
Veritant:
You say Search to return result set, then you say update to work table
SELECTS are not logged
Creating a result set on the fly, like
SELECT * INTO #temp FROM...is not logged
TRUNCATE TABLE or DROP TABLE is not logged..
If you have some examples of what you're doing and need help, post it and we'll look at it...
Clearly I do not need to log the updates to the work tables. They start out empty and the contents could be discarded.
Does SQL Server recognise this and not log the updates to work tables? Is there any way I can indicate that a table is not significant and need not be logged at all?
Would it help to put the work tables in a separate database?
Maybe it would not make much difference anyway?
Thanks in advance for any helpFortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.
A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !|||Originally posted by fadace
Fortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.
A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !
Huh? Well I'm glad you didn't become very specific about what you're talking about.
Veritant:
You say Search to return result set, then you say update to work table
SELECTS are not logged
Creating a result set on the fly, like
SELECT * INTO #temp FROM...is not logged
TRUNCATE TABLE or DROP TABLE is not logged..
If you have some examples of what you're doing and need help, post it and we'll look at it...
Avoiding time-outs
The C++ application calls the database to look up property data. One
troublesome query is a function that returns a table, finding data which
is assembled from four or five tables through a view that has a join,
and then updating the resulting @.table from some other tables. There
are several queries inside the function, which are selected according
to which parameters are supplied (house #, street, zip, or perhaps parcel
number, or house #, street, town, city,...etc.). If a lot of parameters
are provided, and the property is not in the database, then several queries
may be attempted -- it keeps going until it runs out of queries or finds
something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
some failure cases, depending on the distribution of data. (~100 mil
properties in the DB) Some queires operate on the assumption the input data
is slightly faulty, and take relatively a long time, e.g., if WHERE
ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
all this is going on the application may decide the DB is never going to
return, and time out; it also seems more likely to throw an exception the
longer it has to wait. Is there a way to cause the DB function to fail if
it takes more than a certain amount of time? I could also recast it as
a procedure, and check the time consumed after every query, and abandon
the search if a certain amount of time has elapsed.
troublesome query is a function that returns a table, finding data which
is assembled from four or five tables through a view that has a join,
and then updating the resulting @.table from some other tables. There
are several queries inside the function, which are selected according
to which parameters are supplied (house #, street, zip, or perhaps parcel
number, or house #, street, town, city,...etc.). If a lot of parameters
are provided, and the property is not in the database, then several queries
may be attempted -- it keeps going until it runs out of queries or finds
something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
some failure cases, depending on the distribution of data. (~100 mil
properties in the DB) Some queires operate on the assumption the input data
is slightly faulty, and take relatively a long time, e.g., if WHERE
ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
all this is going on the application may decide the DB is never going to
return, and time out; it also seems more likely to throw an exception the
longer it has to wait. Is there a way to cause the DB function to fail if
it takes more than a certain amount of time? I could also recast it as
a procedure, and check the time consumed after every query, and abandon
the search if a certain amount of time has elapsed.
Thanks in advance,
Jim Geissmanjim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman
You don't give any information about your version of MSSQL, and the
client library you're using, but you may be able to set a suitable
timeout period on the client side. Alternatively, look at the "query
governor cost limit Option" in Books Online - this terminates queries
that run for more than a given number of seconds.
Simon|||jim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman
See "remote query timeout Option" in the help text. However, relying
on this may cause inconsistent bahaviour.
This design pattern can also lead to heavy load on your database.
As a suggestion, have two separate sets of queries, one that assumes
good data (should be much quicker which you want to use most times?)
and one that may have incorrect data (will be slower, but not used
very often). In you screen have a checkbox to indicate what search
option to use. Alternatively perform better validation on the data
before submitting the form.|||That sounds interesting. I'll look into it.
> You don't give any information about your version of MSSQL, and the
> client library you're using, but you may be able to set a suitable
> timeout period on the client side. Alternatively, look at the "query
> governor cost limit Option" in Books Online - this terminates queries
> that run for more than a given number of seconds.
> Simon
Avoiding the user login prompt for a reporting services report
Most of the stuff I have read on this is from 2004. I am wondering if
there is a easier fix to this problem.do you use NT authentication or basic authentication?
using NTLM the users can add the web site to the intranet list then the
current login/password will be used. (IE only) so there is no prompt for the
user.
basic authentication allways ask the user.
anonymous access... its anonymous, so no login but no security.
Enterprise edition of RS allow you to create your own authentication system
(like a form based authentication)
"Tim" <TimGoard@.gmail.com> wrote in message
news:1135719831.735427.54260@.f14g2000cwb.googlegroups.com...
> Most of the stuff I have read on this is from 2004. I am wondering if
> there is a easier fix to this problem.
>|||Also, just in case you have a weird problem. I had a case where the issue
was that the server (it was a test server) was using DHCP (dynamic tcpip
address instead of fixed). When I went to a fixed IP address the probelm
went away. Obscure but thought I would mention it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:u7ALlbzCGHA.2596@.TK2MSFTNGP10.phx.gbl...
> do you use NT authentication or basic authentication?
> using NTLM the users can add the web site to the intranet list then the
> current login/password will be used. (IE only) so there is no prompt for
> the user.
> basic authentication allways ask the user.
> anonymous access... its anonymous, so no login but no security.
> Enterprise edition of RS allow you to create your own authentication
> system (like a form based authentication)
> "Tim" <TimGoard@.gmail.com> wrote in message
> news:1135719831.735427.54260@.f14g2000cwb.googlegroups.com...
>> Most of the stuff I have read on this is from 2004. I am wondering if
>> there is a easier fix to this problem.
>|||I am new to web programming. I thought I needed to say that.
In my ASP.net app authentication is set to Windows. Is that the
authentication you are talking about? I set it to none and it stills
comes up with the login prompt.|||Could you open the following file:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\rsreportserver.config
Check the tag <UrlRoot> to see if your servername is entered correctly (e.g.
not set to localhost)
Hth,
Pieter
"Tim" <TimGoard@.gmail.com> wrote in message
news:1135792805.115690.18910@.g44g2000cwa.googlegroups.com...
>I am new to web programming. I thought I needed to say that.
> In my ASP.net app authentication is set to Windows. Is that the
> authentication you are talking about? I set it to none and it stills
> comes up with the login prompt.
>|||The <UrlRoot> is entered correctly.
The authentication tag in this file is:
<Authentication>
<Extension Name="Windows"
Type="Microsoft.ReportingServices.Authentication.WindowsAuthentication,
Microsoft.ReportingServices.Authorization"/>
</Authentication>
I still have no idea how to overcome the windows login prompt for
showing the report in my asp.net app.|||Does your url to your reporting server include your server's ip address? If
it does, you'll have to enter the address into the end users local Intranet
security site.
From the end users machine, open IE.
Click on Tools>Internet Options
Click on the Security Tab then Local Intranet
Click on Sites
Click on Advanced
Enter in the url of your reporting server and click add.
Save your changes and try to access the site.
HTH
"Tim" wrote:
> The <UrlRoot> is entered correctly.
> The authentication tag in this file is:
> <Authentication>
> <Extension Name="Windows"
> Type="Microsoft.ReportingServices.Authentication.WindowsAuthentication,
> Microsoft.ReportingServices.Authorization"/>
> </Authentication>
> I still have no idea how to overcome the windows login prompt for
> showing the report in my asp.net app.
>|||Where are you running the report off from? Your local Machine? or some
other machine?
there is a easier fix to this problem.do you use NT authentication or basic authentication?
using NTLM the users can add the web site to the intranet list then the
current login/password will be used. (IE only) so there is no prompt for the
user.
basic authentication allways ask the user.
anonymous access... its anonymous, so no login but no security.
Enterprise edition of RS allow you to create your own authentication system
(like a form based authentication)
"Tim" <TimGoard@.gmail.com> wrote in message
news:1135719831.735427.54260@.f14g2000cwb.googlegroups.com...
> Most of the stuff I have read on this is from 2004. I am wondering if
> there is a easier fix to this problem.
>|||Also, just in case you have a weird problem. I had a case where the issue
was that the server (it was a test server) was using DHCP (dynamic tcpip
address instead of fixed). When I went to a fixed IP address the probelm
went away. Obscure but thought I would mention it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:u7ALlbzCGHA.2596@.TK2MSFTNGP10.phx.gbl...
> do you use NT authentication or basic authentication?
> using NTLM the users can add the web site to the intranet list then the
> current login/password will be used. (IE only) so there is no prompt for
> the user.
> basic authentication allways ask the user.
> anonymous access... its anonymous, so no login but no security.
> Enterprise edition of RS allow you to create your own authentication
> system (like a form based authentication)
> "Tim" <TimGoard@.gmail.com> wrote in message
> news:1135719831.735427.54260@.f14g2000cwb.googlegroups.com...
>> Most of the stuff I have read on this is from 2004. I am wondering if
>> there is a easier fix to this problem.
>|||I am new to web programming. I thought I needed to say that.
In my ASP.net app authentication is set to Windows. Is that the
authentication you are talking about? I set it to none and it stills
comes up with the login prompt.|||Could you open the following file:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\rsreportserver.config
Check the tag <UrlRoot> to see if your servername is entered correctly (e.g.
not set to localhost)
Hth,
Pieter
"Tim" <TimGoard@.gmail.com> wrote in message
news:1135792805.115690.18910@.g44g2000cwa.googlegroups.com...
>I am new to web programming. I thought I needed to say that.
> In my ASP.net app authentication is set to Windows. Is that the
> authentication you are talking about? I set it to none and it stills
> comes up with the login prompt.
>|||The <UrlRoot> is entered correctly.
The authentication tag in this file is:
<Authentication>
<Extension Name="Windows"
Type="Microsoft.ReportingServices.Authentication.WindowsAuthentication,
Microsoft.ReportingServices.Authorization"/>
</Authentication>
I still have no idea how to overcome the windows login prompt for
showing the report in my asp.net app.|||Does your url to your reporting server include your server's ip address? If
it does, you'll have to enter the address into the end users local Intranet
security site.
From the end users machine, open IE.
Click on Tools>Internet Options
Click on the Security Tab then Local Intranet
Click on Sites
Click on Advanced
Enter in the url of your reporting server and click add.
Save your changes and try to access the site.
HTH
"Tim" wrote:
> The <UrlRoot> is entered correctly.
> The authentication tag in this file is:
> <Authentication>
> <Extension Name="Windows"
> Type="Microsoft.ReportingServices.Authentication.WindowsAuthentication,
> Microsoft.ReportingServices.Authorization"/>
> </Authentication>
> I still have no idea how to overcome the windows login prompt for
> showing the report in my asp.net app.
>|||Where are you running the report off from? Your local Machine? or some
other machine?
Avoiding the usage of DTC
Hello
I am running an script and the following sentence throws and error because the DTC service is not running in the Remote Server:
insert into MyLocalTable
execute synonym_MyRemoteProcedure @.SomeParameter
Since a transaction is not declared within the script, why is the DTC required?
How can I avoid the usage of the DTC? Is there a way to say "this code is not within a distributed transaction"?
Thanks a lot.You are still in an explicit transaction. As far as I know, the requirement that the DTC service be running can not be bypassed. You can try using just a select statement to get the data from the remote server, as the "black box" nature of the stored procedure may be causing this.
I am running an script and the following sentence throws and error because the DTC service is not running in the Remote Server:
insert into MyLocalTable
execute synonym_MyRemoteProcedure @.SomeParameter
Since a transaction is not declared within the script, why is the DTC required?
How can I avoid the usage of the DTC? Is there a way to say "this code is not within a distributed transaction"?
Thanks a lot.You are still in an explicit transaction. As far as I know, the requirement that the DTC service be running can not be bypassed. You can try using just a select statement to get the data from the remote server, as the "black box" nature of the stored procedure may be causing this.
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 temp tables
Dear all,
I'd like to rewrite this update statement without using a temp table.
For each row with duplicate my_id's, the reference_no field should be
set to the number of duplicates for that id.
When I try rewriting this as a single statement I have problems getting
'at' the calculated duplicates field.
Cheers!
select my_id, count(*) as duplicates
into #tmp
from my_table
group by my_id
having count(*) > 1
update my_table
set my_table.reference_no = #tmp.duplicates
from #tmp, my_table
where #tmp.my_id = my_table.my_idOn 7 Mar 2005 23:53:24 -0800, davidol@.hushmail.com wrote:
>I'd like to rewrite this update statement without using a temp table.
>For each row with duplicate my_id's, the reference_no field should be
>set to the number of duplicates for that id.
Hi Davidol,
This version uses only ANSI-standard constructions. You need to use the
column(s) that make up the primary key of the table; I've assumed a
compound primary key on column PK01 and PK02 for my example:
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE EXISTS (SELECT *
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id
AND ( m2.PK01 <> my_table.PK01
OR m2.PK02 <> my_table.PK02))
If you don't have a primary key, you should change your design. In case
you can't do that right now, try the following query (still ANSI
compliant, but probably slower than the first query):
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id) > 1
Finally, if you don't care about portability, you could use the
proprietary UPDATE FROM syntax, as below. Performance might be better
than the ANSI-compliant version (but test it out to be sure). Don't
forget to document the use of a non-ANSI compliant construction (and
include a commented ANSI-compliant version in the code, or include it in
external documentation, so that you don't have to redo the thinking when
you do have to port your code).
UPDATE m
SET m.reference_no = a.cnt
FROM my_table AS m
INNER JOIN (SELECT my_id, COUNT(*) AS cnt
FROM my_table
GROUP BY my_id
HAVING COUNT(*) > 1) AS a
ON a.my_id = m.my_id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this also
update my_table set reference=T.Count from (
select my_id,count(*) as 'Count' from my_table group by my_id having
count(*)>1)
T , my_table A where A.my_id=T.my_id
Madhivanan
I'd like to rewrite this update statement without using a temp table.
For each row with duplicate my_id's, the reference_no field should be
set to the number of duplicates for that id.
When I try rewriting this as a single statement I have problems getting
'at' the calculated duplicates field.
Cheers!
select my_id, count(*) as duplicates
into #tmp
from my_table
group by my_id
having count(*) > 1
update my_table
set my_table.reference_no = #tmp.duplicates
from #tmp, my_table
where #tmp.my_id = my_table.my_idOn 7 Mar 2005 23:53:24 -0800, davidol@.hushmail.com wrote:
>I'd like to rewrite this update statement without using a temp table.
>For each row with duplicate my_id's, the reference_no field should be
>set to the number of duplicates for that id.
Hi Davidol,
This version uses only ANSI-standard constructions. You need to use the
column(s) that make up the primary key of the table; I've assumed a
compound primary key on column PK01 and PK02 for my example:
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE EXISTS (SELECT *
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id
AND ( m2.PK01 <> my_table.PK01
OR m2.PK02 <> my_table.PK02))
If you don't have a primary key, you should change your design. In case
you can't do that right now, try the following query (still ANSI
compliant, but probably slower than the first query):
UPDATE my_table
SET reference_no = (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id)
WHERE (SELECT COUNT(*)
FROM my_table AS m2
WHERE m2.my_id = my_table.my_id) > 1
Finally, if you don't care about portability, you could use the
proprietary UPDATE FROM syntax, as below. Performance might be better
than the ANSI-compliant version (but test it out to be sure). Don't
forget to document the use of a non-ANSI compliant construction (and
include a commented ANSI-compliant version in the code, or include it in
external documentation, so that you don't have to redo the thinking when
you do have to port your code).
UPDATE m
SET m.reference_no = a.cnt
FROM my_table AS m
INNER JOIN (SELECT my_id, COUNT(*) AS cnt
FROM my_table
GROUP BY my_id
HAVING COUNT(*) > 1) AS a
ON a.my_id = m.my_id
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Try this also
update my_table set reference=T.Count from (
select my_id,count(*) as 'Count' from my_table group by my_id having
count(*)>1)
T , my_table A where A.my_id=T.my_id
Madhivanan
Avoiding subreports
We have the need of constructing a report with a main data category and two
subcategories, meaning the following: One customer can have n phone numbers
(so it is a 1:n relation from customer to phone numbers), and one customer
can also have n orders (so there is again a 1:n relation from the customer
to orders, but the orders and the phone numbers have nothing to do with each
other). The presentation should be like a grouping: One customer, then a
block with the phone numbers, then a block with the orders, and then the
next customer and so on. We know that using subreports would be the ideal
solution (working perfectly), but it has 2 severe drawbacks: 1. The
performance in case of a lot of data is simply not acceptable, and 2.
Subreports do not export into Excel. We need therefore an other solution. Is
there a way of doing it with grouping? So far we did not succeed; we tried
to insert a table into a footer field of the main table, but even if we
assign there a dataset, we can't select the exact fields of this dataset
(e.g. phonenumber), but only with aggregat functions (e.g.
First(phonenumber), which is not what we want).
What else could be done, if grouping doesn't work? Is the only other
possibility to use the programming extensibility (e.g. building our own data
provider)?On Jun 27, 8:24 am, "Lorenz Ingold" <l.ing...@.winvs.ch> wrote:
> We have the need of constructing a report with a main data category and two
> subcategories, meaning the following: One customer can have n phone numbers
> (so it is a 1:n relation from customer to phone numbers), and one customer
> can also have n orders (so there is again a 1:n relation from the customer
> to orders, but the orders and the phone numbers have nothing to do with each
> other). The presentation should be like a grouping: One customer, then a
> block with the phone numbers, then a block with the orders, and then the
> next customer and so on. We know that using subreports would be the ideal
> solution (working perfectly), but it has 2 severe drawbacks: 1. The
> performance in case of a lot of data is simply not acceptable, and 2.
> Subreports do not export into Excel. We need therefore an other solution. Is
> there a way of doing it with grouping? So far we did not succeed; we tried
> to insert a table into a footer field of the main table, but even if we
> assign there a dataset, we can't select the exact fields of this dataset
> (e.g. phonenumber), but only with aggregat functions (e.g.
> First(phonenumber), which is not what we want).
> What else could be done, if grouping doesn't work? Is the only other
> possibility to use the programming extensibility (e.g. building our own data
> provider)?
Hi Lorenz,
I previously did something really similar by:
(1.) For each eventual group, I created a group on customer. (for
your example of orders and phone numbers, i would have two groups -
but both of them would be grouped by CustomerId).
(2.) I merged the group header row and inserted a table.
(3.) On the inserted table, I created a group (let's say phone number
from your example). I removed the inserted table's header and footer
and put the phone details into the detail line.
(4.) Repeat with your orders, addresses, whatever.
The only problem with this solution is that you will still lose the
details when rendering to Excel. While this solution did work for me,
I felt that I was probably hitting the problem with too complex a
hammer and would love to see someone else's more elegant solution.
Cheers,
Kathy
subcategories, meaning the following: One customer can have n phone numbers
(so it is a 1:n relation from customer to phone numbers), and one customer
can also have n orders (so there is again a 1:n relation from the customer
to orders, but the orders and the phone numbers have nothing to do with each
other). The presentation should be like a grouping: One customer, then a
block with the phone numbers, then a block with the orders, and then the
next customer and so on. We know that using subreports would be the ideal
solution (working perfectly), but it has 2 severe drawbacks: 1. The
performance in case of a lot of data is simply not acceptable, and 2.
Subreports do not export into Excel. We need therefore an other solution. Is
there a way of doing it with grouping? So far we did not succeed; we tried
to insert a table into a footer field of the main table, but even if we
assign there a dataset, we can't select the exact fields of this dataset
(e.g. phonenumber), but only with aggregat functions (e.g.
First(phonenumber), which is not what we want).
What else could be done, if grouping doesn't work? Is the only other
possibility to use the programming extensibility (e.g. building our own data
provider)?On Jun 27, 8:24 am, "Lorenz Ingold" <l.ing...@.winvs.ch> wrote:
> We have the need of constructing a report with a main data category and two
> subcategories, meaning the following: One customer can have n phone numbers
> (so it is a 1:n relation from customer to phone numbers), and one customer
> can also have n orders (so there is again a 1:n relation from the customer
> to orders, but the orders and the phone numbers have nothing to do with each
> other). The presentation should be like a grouping: One customer, then a
> block with the phone numbers, then a block with the orders, and then the
> next customer and so on. We know that using subreports would be the ideal
> solution (working perfectly), but it has 2 severe drawbacks: 1. The
> performance in case of a lot of data is simply not acceptable, and 2.
> Subreports do not export into Excel. We need therefore an other solution. Is
> there a way of doing it with grouping? So far we did not succeed; we tried
> to insert a table into a footer field of the main table, but even if we
> assign there a dataset, we can't select the exact fields of this dataset
> (e.g. phonenumber), but only with aggregat functions (e.g.
> First(phonenumber), which is not what we want).
> What else could be done, if grouping doesn't work? Is the only other
> possibility to use the programming extensibility (e.g. building our own data
> provider)?
Hi Lorenz,
I previously did something really similar by:
(1.) For each eventual group, I created a group on customer. (for
your example of orders and phone numbers, i would have two groups -
but both of them would be grouped by CustomerId).
(2.) I merged the group header row and inserted a table.
(3.) On the inserted table, I created a group (let's say phone number
from your example). I removed the inserted table's header and footer
and put the phone details into the detail line.
(4.) Repeat with your orders, addresses, whatever.
The only problem with this solution is that you will still lose the
details when rendering to Excel. While this solution did work for me,
I felt that I was probably hitting the problem with too complex a
hammer and would love to see someone else's more elegant solution.
Cheers,
Kathy
Avoiding SQL Injection with Dynamic SQL
I am exclusively using Stored Procedures to access the database, i.e. there are no Ad-Hoc SQL statements anywhere in the C# code. However, one thing I need to be able to do is to allow filtering for data grids on my ASP.NET page. I want to do the filtering in the Stored Procedure using Dynamic SQL to set the WHERE clause. However, one fear of mine is SQL injection from the client. How can I avoid arbitrary SQL injection, yet still allow for a dynamic WHERE clause to be passed into the stored procedure?
Jason PachecoFrom herehttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec03.mspx
Preventing SQL Injection
So long as injected SQL code is syntactically correct, it will be impossible to programmatically detect tampering on the server side. You must therefore validate all user input on the client side, and force server-side type checking by calling parameterized stored procedures. Always validate user input by testing type, length, format, and range. Untested input can cause program errors, and may be used by hackers as a point of entry into your system. When implementing precautions against malicious input, consider the architecture and deployment scenarios of your application. Remember that programs designed to run in a secure environment can be copied to an insecure environment.
Validate All Input
The following suggestions should be considered best practices:
- Make no assumptions about the size, type, or content of the data received by your application. For example, evaluate:
- How will your application behave if an errant, or malicious, user enters a 10-megabyte MPEG file where your application expects a postal code?
- How will your application behave if a DROP TABLE statement is embedded in a text field?
- Test the size and data type of input, and enforce appropriate limits. This can help prevent deliberate buffer overruns.
- Test the content of string variables and accept only expected values. Reject entries containing binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
- When working with XML documents, validate all data against its schema as it is entered.
- Never build Transact-SQL statements directly from user input.
- Use stored procedures to validate user input.
- In multi-tiered environments, all data should be validated before admission to the trusted zone. Data that does not pass the validation process should be rejected, and an error returned to the previous tier.
- Implement multiple layers of validation. Precautions you take against casually malicious users may be ineffective against expert hackers. The best practice is to validate input in the user interface, and then at all subsequent points at which it crosses a trust boundary.
For example, data validation in a client-side application may prevent simple script injection; however, if the next tier assumes that its input has already been validated, any hacker capable of bypassing your client can have unrestricted access to your system.
- Never concatenate user input that is not validated. String concatenation is the primary point of entry for script injection.
- Do not accept the following strings in fields from which file names may be constructed: AUX, CLOCK$, COM1 through COM8, CON, CONFIG$, LPT1 through LPT8, NUL, and PRN.
When possible, reject input that contains the following potentially dangerous characters.
Input characterMeaning in Transact-SQL
; Query delimiter
' Character data string delimiter
-- Comment delimiter
/* ... */ Comment delimiters. Text between /* and */ is not evaluated by the server.
Xp_ Begins the name of catalog extended stored procedures such as xp_cmdshell.
Use Type-Safe SQL Parameters
The Parameters collection in SQL Server provides type checking and length validation. If you use the Parameters collection, input is treated as a literal value rather than executable code. An additional benefit of using the Parameters collection is that you can enforce type and length checks. Values outside of the range will trigger an exception. The following code fragment illustrates using the Parameters collection:
SqlDataAdapter myCommand = new SqlDataAdapter("AuthorLogin", conn);
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter parm = myCommand.SelectCommand.Parameters.Add(
"@.au_id", SqlDbType.VarChar, 11);
parm.Value = Login.Text;
In this example, the @.au_id parameter is treated as a literal value rather than executable code. This value is checked for type and length. If the value of @.au_id does not conform to the specified type and length constraints, an exception will be thrown.
Use Parameterized Input with Stored Procedures
Stored procedures may be susceptible to SQL injection if they use unfiltered input. For example, the following code is vulnerable:
SqlDataAdapter myCommand =
new SqlDataAdapter("LoginStoredProcedure '" +
Login.Text + "'", conn);
If you use stored procedures, you should use parameters as their input.
Use the Parameters Collection with Dynamic SQL
If you cannot use stored procedures, you can still use parameters, as shown below.
SqlDataAdapter myCommand = new SqlDataAdapter(
"SELECT au_lname, au_fname FROM Authors WHERE au_id = @.au_id", conn);
SQLParameter parm = myCommand.SelectCommand.Parameters.Add("@.au_id",
SqlDbType.VarChar, 11);
Parm.Value = Login.Text;
Filtering Input
Filtering input may also be helpful in protecting against SQL injection by removing escape characters, but due to the large number of characters that may pose problems it is not a reliable defense. The following snippet searches for the character string delimiter.
private string SafeSqlLiteral(string inputSQL)
{
return inputSQL.Replace("'", "''");
}
LIKE Clauses
Note that if you are using a LIKE clause, wildcard characters still need to be escaped:
s = s.Replace("[", "[[]");
s = s.Replace("%", "[%]");
s = s.Replace("_", "[_]");|||
Many thanks to you DarrellNorton,
Very helpfull information.
this was my posthttp://forums.asp.net/926297/ShowPost.aspx
BR
Avoiding Setting xp_cmdshell in SQL 2005
Would anyone know, how to work around a potential problem of not using xp_cmdshell.
For our users, We are using xp_cmdshell to look up the xml files in a particular folder and process them into Database. xp_cmdshell is used to read the contents of the file and build up the string for the stored procedure parameter input.
In SQL 2000, xp_cmdshell was set by default. in SQL 2005 not set by default and some of my users doesn't want to set it suggesting security concerns.
Thanks.
You could use OPENROWSET with BULK option for upload xml file as sigle blob into one database field. For example:
Code Snippet
INSERT INTO myTable(Document)SELECT * FROM OPENROWSET(BULK N'C:\data.xml', SINGLE_BLOB) AS Document
Then you could use xml data type functions for split data. See xml.value, xml.nodes in BOL
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)
Avoiding Extra page in printing
Hi,
I have finally worked my way through the process of creating a semi complex
report. It looks good. I like the product and have found it rich in neat
features.
However, it offers a page break option on table groups of either at the
start of the group or at the end of a group. The end of the group choice
displays properly in HTML however when I use the print icon on the print
toolbar, it prints an extra page because it breaks at the end of the last
group. Is there a way of turning off that final break at the end of print?
Thanks,
hughHi Hugh,
I understood it wil be a blank page for your
Please set PageBreakAtStart to be True and set PageBreakAtEnd to be False
to see whether it could resolve this.
If above does not work, would you please generate a sample rdl file with
AdventureWorks for me to reproduce it on my side?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
Thanks it just worked as you said. I tried all different kind of
combinations. I am positive that I tried that one before and it only gave
me an extra page in the beginning instead of at the end. I must have had
something else wrong when I tried that combination.
Thank you very much.
hugh
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:rFgfb314FHA.1144@.TK2MSFTNGXA01.phx.gbl...
> Hi Hugh,
> I understood it wil be a blank page for your
> Please set PageBreakAtStart to be True and set PageBreakAtEnd to be False
> to see whether it could resolve this.
> If above does not work, would you please generate a sample rdl file with
> AdventureWorks for me to reproduce it on my side?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
I have finally worked my way through the process of creating a semi complex
report. It looks good. I like the product and have found it rich in neat
features.
However, it offers a page break option on table groups of either at the
start of the group or at the end of a group. The end of the group choice
displays properly in HTML however when I use the print icon on the print
toolbar, it prints an extra page because it breaks at the end of the last
group. Is there a way of turning off that final break at the end of print?
Thanks,
hughHi Hugh,
I understood it wil be a blank page for your
Please set PageBreakAtStart to be True and set PageBreakAtEnd to be False
to see whether it could resolve this.
If above does not work, would you please generate a sample rdl file with
AdventureWorks for me to reproduce it on my side?
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
Thanks it just worked as you said. I tried all different kind of
combinations. I am positive that I tried that one before and it only gave
me an extra page in the beginning instead of at the end. I must have had
something else wrong when I tried that combination.
Thank you very much.
hugh
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:rFgfb314FHA.1144@.TK2MSFTNGXA01.phx.gbl...
> Hi Hugh,
> I understood it wil be a blank page for your
> Please set PageBreakAtStart to be True and set PageBreakAtEnd to be False
> to see whether it could resolve this.
> If above does not work, would you please generate a sample rdl file with
> AdventureWorks for me to reproduce it on my side?
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
Avoiding EOF Error
I am querying a sql server db from asp/ado. I am trying to get data from a list of people but all of them may not have the data I am looking for. As a result, when the query gets to that person/record, I get the message:
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
How can I avoid this message? Here is my code:
sql="SELECT TrngDate, Effort, UnitMeas FROM TrainingData WHERE PartID = " & CLng(iPartID)
Set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 2
Do While Not rs.eof
.
.
.
rs.MoveNext
Loop
I also used the conn.Execute approach.
This a recurring issue for me. Any ideas?Check this KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;230101) and get the fix if required.
Also check whether that would be a closed recordset while error was returned. And also check whether second RS is opened/returned.|||Originally posted by Satya
Check this KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;230101) and get the fix if required.
Also check whether that would be a closed recordset while error was returned. And also check whether second RS is opened/returned.
But it doesn't appear that the problem referred to is my issue. My issue is that the record that I am looking for may in fact not exist. I am not sure how to determine that before 'looking' for it. Is there any way I can get a record count? In the past I have tried this and it returns a recordcount of -1.|||In that case while opening of RS just count from the table and use the variable to avoid it.|||Originally posted by Satya
In that case while opening of RS just count from the table and use the variable to avoid it.
but I don't understand how that would look syntactically. Could you show me the code?
Thanks!|||Dont' be sorry..
Rather I fee so, (Im no developer :( )
Still check this webpage (http://www.vbcode.com/asp/showsn.asp?theID=4816) for code example.
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
How can I avoid this message? Here is my code:
sql="SELECT TrngDate, Effort, UnitMeas FROM TrainingData WHERE PartID = " & CLng(iPartID)
Set rs=Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 1, 2
Do While Not rs.eof
.
.
.
rs.MoveNext
Loop
I also used the conn.Execute approach.
This a recurring issue for me. Any ideas?Check this KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;230101) and get the fix if required.
Also check whether that would be a closed recordset while error was returned. And also check whether second RS is opened/returned.|||Originally posted by Satya
Check this KBA (http://support.microsoft.com/default.aspx?scid=kb;en-us;230101) and get the fix if required.
Also check whether that would be a closed recordset while error was returned. And also check whether second RS is opened/returned.
But it doesn't appear that the problem referred to is my issue. My issue is that the record that I am looking for may in fact not exist. I am not sure how to determine that before 'looking' for it. Is there any way I can get a record count? In the past I have tried this and it returns a recordcount of -1.|||In that case while opening of RS just count from the table and use the variable to avoid it.|||Originally posted by Satya
In that case while opening of RS just count from the table and use the variable to avoid it.
but I don't understand how that would look syntactically. Could you show me the code?
Thanks!|||Dont' be sorry..
Rather I fee so, (Im no developer :( )
Still check this webpage (http://www.vbcode.com/asp/showsn.asp?theID=4816) for code example.
Friday, February 24, 2012
Avoiding Empty Tags using XML Explicit..
Hey all, a nice little challenge for you as I've searched all over..
Here is some very simple XML Explicit code:
SELECT 1 AS TAG,
NULL AS Parent,
product AS [ParentTag!1!AProduct!Element]
FROM tblTableWHERE X = Y
FOR XML EXPLICIT
When there is no results, I get an empty tag.
<ParentTag/>
This I do not want. Insted I would like nothing. Is there a simple way to achieve this?
Using SQL Server 2000
Looking forward to your responses!
Roqs
Dunno if its just my SQL Server but mine is working fine. I get a blank when no data is returned (Not the <ParentTag/>)Tried this with northwind.
SELECT 1 AS TAG,
NULL AS Parent,
CompanyName AS [ParentTag!1!AProduct!Element]
FROM Customers
WHERE CustomerID = 'LFKI'
FOR XML EXPLICIT
avoiding duplicate lines
I have a oracle database that includes the following tables:
game(gameID, season, week, stadium) -- gameID is PK
competesIn(gameID, team, score) -- gameID, teamName is PK
As you can probably infer, it is a database to track the results of football games.
competesIn has two records for every game, one for each team.
I would like to create a view that combines these tables into the following structure
gameView(season, week, location, team1, score1, team2, score2)
I have tried using the following SQL inside the view:
select g.season, g.week, g.stadium,
c1.team as team1, c1.score as Score1,
c2.team as team2, c2.score as Score2
from game g, competesIn c1, competesIn c2
where c1.gameID = g.gameID
and c2.gameID = g.gameID
and c1.team != c2.team
This does create the view I was looking for but it creates two rows for each game and I need it to return only one row per game.
Any thoughts?change != in last line to <
game(gameID, season, week, stadium) -- gameID is PK
competesIn(gameID, team, score) -- gameID, teamName is PK
As you can probably infer, it is a database to track the results of football games.
competesIn has two records for every game, one for each team.
I would like to create a view that combines these tables into the following structure
gameView(season, week, location, team1, score1, team2, score2)
I have tried using the following SQL inside the view:
select g.season, g.week, g.stadium,
c1.team as team1, c1.score as Score1,
c2.team as team2, c2.score as Score2
from game g, competesIn c1, competesIn c2
where c1.gameID = g.gameID
and c2.gameID = g.gameID
and c1.team != c2.team
This does create the view I was looking for but it creates two rows for each game and I need it to return only one row per game.
Any thoughts?change != in last line to <
Avoiding Divide by Zero in Report
What is the experession to evaluate if the result of a computation would be a divide by zero error for a text box in report?
IIF(divide by zero, display nothing, else display computed result)...?
you should actually use it in your query,but...
=iif(value1 = 0 or value2=0,0, value1/value2)
where the values are the values your dividing by.
it's much easier to do it in sql though...
|||I'd recommend either doing it in your Stored Procedure or creating a function to handle it. You don't want to be putting that expression in every sell in which you're doing division.You can create a function like the one below and place it in the Report code block or put it in an assembly and reference the assembly in the report.
Public Function CalcAvg(dblNum As Double, dblDen As Double) as Object
if (dblDen = nothing) or (dblDen = 0)
CalcAvg = 0
else
CalcAvg = dblNum / dblDen
end if
End Function|||
I have this code in my Code section
Code Snippet
Public Shared Function DivideXbyY(ByVal X As Decimal, ByVal Y As Decimal) As DecimalDivideXbyY = IIF(Y=0, 0, X / IIF(Y=0, 1, Y))
End Function then I call it in the Table/Matrix
Code Snippet
=Code.DivideXbyY(Fields!amt.Value,Fields!tix.Value)
|||Thanks guys, your responses are very helpful and much appreciated.Avoiding deadlock
I have a stored procedure spUpdateClient, which takes as params a number of properties of a client application that wants to register its existence with the database. The sp just needs to add a new row or update an existing row with this data.
I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)
IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
ENDELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END
But the client apps call this every second or so, so soon enough I started getting primary key violations. It looks like one client would make two calls nearly at the same time, both would get a 0 value on the SELECT line, so both would try to insert a new row with the same ClientId. No good.
So then I added
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.
Instead I'm now getting deadlock errors.
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.
I could catch the deadlock in my client app after SQL Server kills one of the transactions, but it seems to me there should be some way to set a lock at the top of the sp that says "nobody else can enter this sp until I exit it". Any such thing?
Thanks.
Nate Hekman
You can change your code to the following:
BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, XLOCK)WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
ENDELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
ENDCOMMIT
The XLOCK hint in the SELECT statement will ensure that if the rows exists you lock it exclusively so the update will work fine. And the SERIALIZABLE hint will ensure that if the row doesn't exist you lock the key range for the new row. This will prevent the deadlock from happening.
Another approach is to do the following:
|||BEGIN TRANSACTION
UPDATE Clients WITH(SERIALIZABLE)
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
IF @.@.ROWCOUNT = 0BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
ENDCOMMIT
This is a very good understanding of why you are getting a deadlock :) You can cause it to single thread by adding an LOCK hint to the existence check. Also, change from using a count to exists, it will be better as it just needs to see a single row, rather than counting them all. No need for serializable here now, but make sure ClientId is indexed right (and if it is the declare primary key that should be fine)
BEGIN TRANSACTION
IF EXISTS (SELECT 1
FROM Clients WITH (XLOCK)
WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END
COMMIT TRANSACTION
An alternative is to just ignore the deadlock as you know why it occurs, and since you are only keeping a single copy, it is just as good and will be fast enough...
Another alternative would be to switch to an insert only methodology and just pump rows into a table. It would give you counts of visits, actual times of visits, etc. You could also glean the same information as you have now with no locking problems at all.
--clientId, visitDate would be the likely UNIQUE constraint
--if they are > .003 seconds apart, which I don't know based on your needs
create table clientVisit
(
clientVisitId int identity primary key,
clientId guid,
visitDate datetime default (getdate()),
hostName varchar(?),
etc varchar(?)
)
then just insert... It will take more disk space, but it should be just as fast. You could then pull the data off periodically and get the same information, plus some.
|||I didn't think that:
BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, UPDLOCK)
WHERE ClientId=@.ClientId)
Would suffice since a SHARED lock compatible with an UPDATE lock? In this case, the second could still read there to be no rows.
(I did overlook that you need to increase the isolation level just in case READ_COMMITTED_SNAPSHOT is enabled. And I didn't realize you could put lock hints on UPDATE statements :)
Thanks!
|||You are right. This should be XLOCK instead.|||Wow, thanks for the excellent replies everyone! I'm very new at T-SQL so all this locking stuff is a lot to mull over. But you've given me several good approaches that I think will work just great.
Thanks again.
Nate
|||IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END
This problem's interesting and I'm sure lots of people have encountered before.
There are some things I still don't understand.
If we set the isolation level as Serializable and use XLOCK for the SELECT as follows:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
IF (SELECT COUNT(ClientId) FROM Clients (WITH XLOCK) WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END
COMMIT TRANSACTION
As Nate Hekman first wrote:
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.
Question 1>
If the row doesn't exist, does the process go like this:
process1 executes SELECT ... WITH XLOCK and holds an exclusive lock on Clients.
Because isolation level Serializable is used, other processes can't insert new rows or update rows within the defined range. Also, because an exclusive lock is held, other processes can't even read until process1 finishes?
Question 2>
How does it solve the deadlock problem?
Question 1:
When a transaction holds Exclusive locks on a range of rows, other transaction cannot read those rows, unless the second transaction's isolation level is not READ UNCOMMITTED. As Nate uses the same sp for inserts, yes, the select command blocks all readers.
Question2:
Deadlocks do not occur because a transaction that executes its select command is guaranteed to be granted all the locks it will needs further, that is, it cannot be blocked, so a deadlock can never occur. This is a sort of pre-declaration of locks, or pessimistic locking. When transaction A executes its select command, the other ones (say trans. B)cannot read. If transaction A takes a long time to commit, B might be eventually aborted. But this will not happen-the insert transaction is a short one, and it will need milliseconds to commit, after that transaction B(or other waiting transaction) will be granted the lock. In this scenario, aborts are very unlikely to occur unless the workload is high.
Avoiding deadlock
I have a stored procedure spUpdateClient, which takes as params a number of properties of a client application that wants to register its existence with the database. The sp just needs to add a new row or update an existing row with this data.
I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)
IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
ENDELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END
But the client apps call this every second or so, so soon enough I started getting primary key violations. It looks like one client would make two calls nearly at the same time, both would get a 0 value on the SELECT line, so both would try to insert a new row with the same ClientId. No good.
So then I added
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.
Instead I'm now getting deadlock errors.
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.
I could catch the deadlock in my client app after SQL Server kills one of the transactions, but it seems to me there should be some way to set a lock at the top of the sp that says "nobody else can enter this sp until I exit it". Any such thing?
Thanks.
Nate Hekman
You can change your code to the following:
BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, XLOCK)WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
ENDELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
ENDCOMMIT
The XLOCK hint in the SELECT statement will ensure that if the rows exists you lock it exclusively so the update will work fine. And the SERIALIZABLE hint will ensure that if the row doesn't exist you lock the key range for the new row. This will prevent the deadlock from happening.
Another approach is to do the following:
|||BEGIN TRANSACTION
UPDATE Clients WITH(SERIALIZABLE)
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
IF @.@.ROWCOUNT = 0BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
ENDCOMMIT
This is a very good understanding of why you are getting a deadlock :) You can cause it to single thread by adding an LOCK hint to the existence check. Also, change from using a count to exists, it will be better as it just needs to see a single row, rather than counting them all. No need for serializable here now, but make sure ClientId is indexed right (and if it is the declare primary key that should be fine)
BEGIN TRANSACTION
IF EXISTS (SELECT 1
FROM Clients WITH (XLOCK)
WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END
COMMIT TRANSACTION
An alternative is to just ignore the deadlock as you know why it occurs, and since you are only keeping a single copy, it is just as good and will be fast enough...
Another alternative would be to switch to an insert only methodology and just pump rows into a table. It would give you counts of visits, actual times of visits, etc. You could also glean the same information as you have now with no locking problems at all.
--clientId, visitDate would be the likely UNIQUE constraint
--if they are > .003 seconds apart, which I don't know based on your needs
create table clientVisit
(
clientVisitId int identity primary key,
clientId guid,
visitDate datetime default (getdate()),
hostName varchar(?),
etc varchar(?)
)
then just insert... It will take more disk space, but it should be just as fast. You could then pull the data off periodically and get the same information, plus some.
|||I didn't think that:
BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, UPDLOCK)
WHERE ClientId=@.ClientId)
Would suffice since a SHARED lock compatible with an UPDATE lock? In this case, the second could still read there to be no rows.
(I did overlook that you need to increase the isolation level just in case READ_COMMITTED_SNAPSHOT is enabled. And I didn't realize you could put lock hints on UPDATE statements :)
Thanks!
|||You are right. This should be XLOCK instead.|||Wow, thanks for the excellent replies everyone! I'm very new at T-SQL so all this locking stuff is a lot to mull over. But you've given me several good approaches that I think will work just great.
Thanks again.
Nate
|||IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END
This problem's interesting and I'm sure lots of people have encountered before.
There are some things I still don't understand.
If we set the isolation level as Serializable and use XLOCK for the SELECT as follows:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
IF (SELECT COUNT(ClientId) FROM Clients (WITH XLOCK) WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END
COMMIT TRANSACTION
As Nate Hekman first wrote:
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.
Question 1>
If the row doesn't exist, does the process go like this:
process1 executes SELECT ... WITH XLOCK and holds an exclusive lock on Clients.
Because isolation level Serializable is used, other processes can't insert new rows or update rows within the defined range. Also, because an exclusive lock is held, other processes can't even read until process1 finishes?
Question 2>
How does it solve the deadlock problem?
Question 1:
When a transaction holds Exclusive locks on a range of rows, other transaction cannot read those rows, unless the second transaction's isolation level is not READ UNCOMMITTED. As Nate uses the same sp for inserts, yes, the select command blocks all readers.
Question2:
Deadlocks do not occur because a transaction that executes its select command is guaranteed to be granted all the locks it will needs further, that is, it cannot be blocked, so a deadlock can never occur. This is a sort of pre-declaration of locks, or pessimistic locking. When transaction A executes its select command, the other ones (say trans. B)cannot read. If transaction A takes a long time to commit, B might be eventually aborted. But this will not happen-the insert transaction is a short one, and it will need milliseconds to commit, after that transaction B(or other waiting transaction) will be granted the lock. In this scenario, aborts are very unlikely to occur unless the workload is high.
avoiding cursors how to do this?
OK say I want to execute the following stored procedure for every order that
matches specific criteria
EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
If my set of orders to process is returned by this query:
select lngOrderID from tblOrders where intstatus=3
Is there any way to do this without resorting to a cursor?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>
No. And cursors aren't _that_ slow. Executing the procedure for each row
would not be noticably better without a cursor.
The potential performance problem is that you are executing the logic of the
procedure on a row-wise basis, instead of a set-wise basis. The only way to
avoid the row-wise processing would be to unwrap the guts of
dbo.sprPassUpline and create a version which operated over the entire set of
rows. Something like
EXECUTE @.RC = dbo.sprPassUplineByStatus 3
David|||declare @.loop int
declare @.rowcount int
declare @.lngOrder int
declare @.RC int
create table #OrderList (
ROW_ID int identity ,
lngOrder int not null )
insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
intStatus = 3
declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
while @.loop <= @.rowcount
begin
select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <>
begin
-- do whatever
end
select @.loop = @.loop + 1
-- or the below method if you delete any rows from the #OrderList table
for any reason
-- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
end
drop table #OrderList
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>|||"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:O8e%f.18404$ic1.16436@.newsfe5-win.ntli.net...
> declare @.loop int
> declare @.rowcount int
> declare @.lngOrder int
> declare @.RC int
> create table #OrderList (
> ROW_ID int identity ,
> lngOrder int not null )
> insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
> intStatus = 3
> declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
> while @.loop <= @.rowcount
> begin
> select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
> exec @.RC = dbo.sprPassUpline @.lngOrder
> if @.RC <>
> begin
> -- do whatever
> end
> select @.loop = @.loop + 1
> -- or the below method if you delete any rows from the #OrderList table
> for any reason
> -- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
> end
> drop table #OrderList
>
OK, that is tecnically what the OP asked for, but in what possible way is
that better than using a cursor?
declare cOrders cursor local static for
select lngOrderID from tblOrders
where intStatus = 3
declare @.lngOrder int,
@.RC int
open cOrders
fetch next from cOrders into @.lngOrder
while @.@.fetch_status = 0
begin
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <> 0
begin
raiserror('sprPassUpline failed returning %d',16,1,@.RC)
end
fetch next from cOrders into @.lngOrder
end
close cOrders
?
Davud|||> Is there any way to do this without resorting to a cursor?
Possibly, but since you haven't told us what the SP does we can't tell you.
The question is not "How do I do X once for each row without a cursor?"
The question is "How do I do X for the whole set INSTEAD OF once for each
row?"
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Well actually in normal day to day business this SP is only called for one
row at a time when that order enters a given state. But when initializing
new systems it must be called for everything on import. Guess that is a
good enough reason to redo the SP and just make it set based so it'll handle
one or more. We never had this requirement before so it was never an issue.
Thanks for jumping in everybody.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uvSpxznXGHA.5012@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to do this without resorting to a cursor?
> Possibly, but since you haven't told us what the SP does we can't tell
> you.
> The question is not "How do I do X once for each row without a cursor?"
> The question is "How do I do X for the whole set INSTEAD OF once for each
> row?"
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
matches specific criteria
EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
If my set of orders to process is returned by this query:
select lngOrderID from tblOrders where intstatus=3
Is there any way to do this without resorting to a cursor?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>
No. And cursors aren't _that_ slow. Executing the procedure for each row
would not be noticably better without a cursor.
The potential performance problem is that you are executing the logic of the
procedure on a row-wise basis, instead of a set-wise basis. The only way to
avoid the row-wise processing would be to unwrap the guts of
dbo.sprPassUpline and create a version which operated over the entire set of
rows. Something like
EXECUTE @.RC = dbo.sprPassUplineByStatus 3
David|||declare @.loop int
declare @.rowcount int
declare @.lngOrder int
declare @.RC int
create table #OrderList (
ROW_ID int identity ,
lngOrder int not null )
insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
intStatus = 3
declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
while @.loop <= @.rowcount
begin
select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <>
begin
-- do whatever
end
select @.loop = @.loop + 1
-- or the below method if you delete any rows from the #OrderList table
for any reason
-- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
end
drop table #OrderList
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>|||"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:O8e%f.18404$ic1.16436@.newsfe5-win.ntli.net...
> declare @.loop int
> declare @.rowcount int
> declare @.lngOrder int
> declare @.RC int
> create table #OrderList (
> ROW_ID int identity ,
> lngOrder int not null )
> insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
> intStatus = 3
> declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
> while @.loop <= @.rowcount
> begin
> select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
> exec @.RC = dbo.sprPassUpline @.lngOrder
> if @.RC <>
> begin
> -- do whatever
> end
> select @.loop = @.loop + 1
> -- or the below method if you delete any rows from the #OrderList table
> for any reason
> -- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
> end
> drop table #OrderList
>
OK, that is tecnically what the OP asked for, but in what possible way is
that better than using a cursor?
declare cOrders cursor local static for
select lngOrderID from tblOrders
where intStatus = 3
declare @.lngOrder int,
@.RC int
open cOrders
fetch next from cOrders into @.lngOrder
while @.@.fetch_status = 0
begin
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <> 0
begin
raiserror('sprPassUpline failed returning %d',16,1,@.RC)
end
fetch next from cOrders into @.lngOrder
end
close cOrders
?
Davud|||> Is there any way to do this without resorting to a cursor?
Possibly, but since you haven't told us what the SP does we can't tell you.
The question is not "How do I do X once for each row without a cursor?"
The question is "How do I do X for the whole set INSTEAD OF once for each
row?"
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Well actually in normal day to day business this SP is only called for one
row at a time when that order enters a given state. But when initializing
new systems it must be called for everything on import. Guess that is a
good enough reason to redo the SP and just make it set based so it'll handle
one or more. We never had this requirement before so it was never an issue.
Thanks for jumping in everybody.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uvSpxznXGHA.5012@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to do this without resorting to a cursor?
> Possibly, but since you haven't told us what the SP does we can't tell
> you.
> The question is not "How do I do X once for each row without a cursor?"
> The question is "How do I do X for the whole set INSTEAD OF once for each
> row?"
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
Subscribe to:
Posts (Atom)