ERROR: Subreport: query

Showing posts with label query. Show all posts

Thursday, March 29, 2012

estimating tempdb usage

Is there any way to estimate how much space will be needed by tempdb?
I have a user executing a simple query similar to:
select orders.customerid, sum(quantity*unitprice) as amount
from orders inner join [order details]
on orders.orderid=[order details].orderid
group by orders.customerid
In other words, two thin (not too many columns) tables, equi-join,
summarizing and a group by. The only trouble is her two tables have
28000 rows and nearly 800 million rows. Her query dies after a few
hours when tempdb autogrows and runs out of disk space at 17 gig. I
know tempdb is used for work tables, joins, sorts, group bys, etc.
But knowing the columns sizes and number of rows, is there a way to
estimate how much tempdb will be needed? She's basically the only
user on the system.
By the way, this is SQL Server 2000.
Thanks,
ScottI guess it would be better to Grow the tempdb first, then run oyur query.
I had the same problem on SQL Server 6.5 to 2000 migration with the log
files (we had big tables as well) , the Autogrowth didn´t work. So we had to
create the database and transaction logs big enough to support migration
process before initiate it.
HTH
"scott parmelee" <s_parmelee@.hotmail.com> escreveu na mensagem
news:e14a8116.0311130819.725c0a4@.posting.google.com...
> Is there any way to estimate how much space will be needed by tempdb?
> I have a user executing a simple query similar to:
> select orders.customerid, sum(quantity*unitprice) as amount
> from orders inner join [order details]
> on orders.orderid=[order details].orderid
> group by orders.customerid
> In other words, two thin (not too many columns) tables, equi-join,
> summarizing and a group by. The only trouble is her two tables have
> 28000 rows and nearly 800 million rows. Her query dies after a few
> hours when tempdb autogrows and runs out of disk space at 17 gig. I
> know tempdb is used for work tables, joins, sorts, group bys, etc.
> But knowing the columns sizes and number of rows, is there a way to
> estimate how much tempdb will be needed? She's basically the only
> user on the system.
> By the way, this is SQL Server 2000.
> Thanks,
> Scott

Estimating Table Sizes

Is it possible to write a query that can estimate the space a table is
using in the database? I would like to be able to generate a report
that can display the amount of disk space a table is consuming.How about using sp_spaceused?
RLF
<marcusq71@.gmail.com> wrote in message
news:1173901596.189278.188470@.l75g2000hse.googlegroups.com...
> Is it possible to write a query that can estimate the space a table is
> using in the database? I would like to be able to generate a report
> that can display the amount of disk space a table is consuming.
>|||Hello,
Go to the specific database and execute the below command to get the space
usage for all tables individually.
EXEC sp_MSForEachTable 'EXEC sp_spaceused [?]';
Thanks
Hari
<marcusq71@.gmail.com> wrote in message
news:1173901596.189278.188470@.l75g2000hse.googlegroups.com...
> Is it possible to write a query that can estimate the space a table is
> using in the database? I would like to be able to generate a report
> that can display the amount of disk space a table is consuming.
>|||On Mar 15, 12:46 am, "marcus...@.gmail.com" <marcus...@.gmail.com>
wrote:
> Is it possible to write a query that can estimate the space a table is
> using in the database? I would like to be able to generate a report
> that can display the amount of disk space a table is consuming.
Make sure you run DBCC UPDATEUSAGE on the database before you run
sp_spaceused
Reports and corrects inaccuracies in the sysindexes table, which may
result in incorrect space usage reports by the sp_spaceused system
stored procedure.
M A Srinivas|||On Mar 15, 2:07 am, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Mar 15, 12:46 am, "marcus...@.gmail.com" <marcus...@.gmail.com>
> wrote:
>
> Make sure you run DBCC UPDATEUSAGE on the database before you run
> sp_spaceused
> Reports and corrects inaccuracies in the sysindexes table, which may
> result in incorrect space usage reports by the sp_spaceused system
> stored procedure.
> M A Srin
Thank you all for these suggestions. This is exactly what I am
looking for. I was not clear on my initial request but the
MSforeachtable stored procedure was exactly what I was looking for.

Estimating Table Sizes

Is it possible to write a query that can estimate the space a table is
using in the database? I would like to be able to generate a report
that can display the amount of disk space a table is consuming.
How about using sp_spaceused?
RLF
<marcusq71@.gmail.com> wrote in message
news:1173901596.189278.188470@.l75g2000hse.googlegr oups.com...
> Is it possible to write a query that can estimate the space a table is
> using in the database? I would like to be able to generate a report
> that can display the amount of disk space a table is consuming.
>
|||Hello,
Go to the specific database and execute the below command to get the space
usage for all tables individually.
EXEC sp_MSForEachTable 'EXEC sp_spaceused [?]';
Thanks
Hari
<marcusq71@.gmail.com> wrote in message
news:1173901596.189278.188470@.l75g2000hse.googlegr oups.com...
> Is it possible to write a query that can estimate the space a table is
> using in the database? I would like to be able to generate a report
> that can display the amount of disk space a table is consuming.
>
|||On Mar 15, 12:46 am, "marcus...@.gmail.com" <marcus...@.gmail.com>
wrote:
> Is it possible to write a query that can estimate the space a table is
> using in the database? I would like to be able to generate a report
> that can display the amount of disk space a table is consuming.
Make sure you run DBCC UPDATEUSAGE on the database before you run
sp_spaceused
Reports and corrects inaccuracies in the sysindexes table, which may
result in incorrect space usage reports by the sp_spaceused system
stored procedure.
M A Srinivas
|||On Mar 15, 2:07 am, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Mar 15, 12:46 am, "marcus...@.gmail.com" <marcus...@.gmail.com>
> wrote:
>
> Make sure you run DBCC UPDATEUSAGE on the database before you run
> sp_spaceused
> Reports and corrects inaccuracies in the sysindexes table, which may
> result in incorrect space usage reports by the sp_spaceused system
> stored procedure.
> M A Srin
Thank you all for these suggestions. This is exactly what I am
looking for. I was not clear on my initial request but the
MSforeachtable stored procedure was exactly what I was looking for.
sql

Estimating Table Sizes

Is it possible to write a query that can estimate the space a table is
using in the database? I would like to be able to generate a report
that can display the amount of disk space a table is consuming.How about using sp_spaceused?
RLF
<marcusq71@.gmail.com> wrote in message
news:1173901596.189278.188470@.l75g2000hse.googlegroups.com...
> Is it possible to write a query that can estimate the space a table is
> using in the database? I would like to be able to generate a report
> that can display the amount of disk space a table is consuming.
>|||Hello,
Go to the specific database and execute the below command to get the space
usage for all tables individually.
EXEC sp_MSForEachTable 'EXEC sp_spaceused [?]';
Thanks
Hari
<marcusq71@.gmail.com> wrote in message
news:1173901596.189278.188470@.l75g2000hse.googlegroups.com...
> Is it possible to write a query that can estimate the space a table is
> using in the database? I would like to be able to generate a report
> that can display the amount of disk space a table is consuming.
>|||On Mar 15, 12:46 am, "marcus...@.gmail.com" <marcus...@.gmail.com>
wrote:
> Is it possible to write a query that can estimate the space a table is
> using in the database? I would like to be able to generate a report
> that can display the amount of disk space a table is consuming.
Make sure you run DBCC UPDATEUSAGE on the database before you run
sp_spaceused
Reports and corrects inaccuracies in the sysindexes table, which may
result in incorrect space usage reports by the sp_spaceused system
stored procedure.
M A Srinivas|||On Mar 15, 2:07 am, "M A Srinivas" <masri...@.gmail.com> wrote:
> On Mar 15, 12:46 am, "marcus...@.gmail.com" <marcus...@.gmail.com>
> wrote:
> > Is it possible to write a query that can estimate the space a table is
> > using in the database? I would like to be able to generate a report
> > that can display the amount of disk space a table is consuming.
> Make sure you run DBCC UPDATEUSAGE on the database before you run
> sp_spaceused
> Reports and corrects inaccuracies in the sysindexes table, which may
> result in incorrect space usage reports by the sp_spaceused system
> stored procedure.
> M A Srin
Thank you all for these suggestions. This is exactly what I am
looking for. I was not clear on my initial request but the
MSforeachtable stored procedure was exactly what I was looking for.

Tuesday, March 27, 2012

Estimated row count vs. actual row count

Hello!
I was trying to troubleshoot a query. Very last step show estimated
rowcount=142386. When query is executed, it returns only 7234 rows. I
executed UPDATE STATISTICS ... WITH FULLSCAN against all tables involved in
this query but this didn't make a difference. I am not sure what else I can
do at this point.
Any advice is a greatly appreciated,
IgorIf you're referring to "estimated rowcount" in the show execution plan
screen of Query Analyzer, that just refers to how many rows were processed
by that specific step in the processing... The number of rows the query
eventually produces can be and most often is very different..
This is Nothing to be concerned about.
"Igor Marchenko" wrote:

> Hello!
> I was trying to troubleshoot a query. Very last step show estimated
> rowcount=142386. When query is executed, it returns only 7234 rows. I
> executed UPDATE STATISTICS ... WITH FULLSCAN against all tables involved i
n
> this query but this didn't make a difference. I am not sure what else I ca
n
> do at this point.
> Any advice is a greatly appreciated,
> Igor
>
>|||Yes, I am referring to execution plan in Query Analyzer. I got concerned
after reading following article:
http://www.windowsitpro.com/sqlserv...61.html

For other queries there is no discrepancy between estimated and actual
number of rows.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:0DF221BF-921A-4D4A-9963-6FD335621588@.microsoft.com...
> If you're referring to "estimated rowcount" in the show execution plan
> screen of Query Analyzer, that just refers to how many rows were
> processed
> by that specific step in the processing... The number of rows the query
> eventually produces can be and most often is very different..
> This is Nothing to be concerned about.
>
> "Igor Marchenko" wrote:
>|||OK, read the article... The author's first comment, "Yes go ahead and
worry..." is a bit overkill - in that he is talking to a point extraneous to
the question he was asked...
He was asked about whether there's an issue simply because the numbers are
different. But he answered based on the fact the "estimated" Number is HIGH
.
Any time you see an "estimated rowCount" number much higher than the actual
number that were generated, then the Query Optimizer might not be choosing
the "fastest" most efficient option in processing the query... the accuracy
or completeness of the data results will not be affected, only the speed at
which it is generated.
"Igor Marchenko" wrote:

> Yes, I am referring to execution plan in Query Analyzer. I got concerned
> after reading following article:
> http://www.windowsitpro.com/sqlserv...61.ht
ml
> For other queries there is no discrepancy between estimated and actual
> number of rows.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:0DF221BF-921A-4D4A-9963-6FD335621588@.microsoft.com...
>
>|||That's exactly why I am worried : 'Query Optimizer might not be choosing
the "fastest" most efficient option in processing the query'. I do know
accuracy won't be affected.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:2BE6C2AB-8D39-4D79-ACC6-D0DBB427F651@.microsoft.com...
> OK, read the article... The author's first comment, "Yes go ahead and
> worry..." is a bit overkill - in that he is talking to a point extraneous
> to
> the question he was asked...
> He was asked about whether there's an issue simply because the numbers are
> different. But he answered based on the fact the "estimated" Number is
> HIGH.
> Any time you see an "estimated rowCount" number much higher than the
> actual
> number that were generated, then the Query Optimizer might not be choosing
> the "fastest" most efficient option in processing the query... the
> accuracy
> or completeness of the data results will not be affected, only the speed
> at
> which it is generated.
> "Igor Marchenko" wrote:
>|||Igor, I found this link:
http://msdn.microsoft.com/library/d...
etchapt14.asp
On it I extracted this bit if text...
Compare Actual vs. Estimated Rows and Executions
When you read the output from a SHOWPLAN statement, start from the
most-indented row that has the highest incremental change in the
TotalSubtreeCost column. Carefully evaluate both the index selection and the
optimizer's estimate by using the SET STATISTICS PROFILE ON command. This
command runs the statement, so only use it on SELECT statements or T-SQL cod
e
that does not modify data, or you can preface the command with a BEGIN TRAN
/ROLLBACK statement.
As an alternative, use the new profiler Performance:Showplan Statistics
event in SQL 2000. This event belongs to event class 98. This event reports
four columns that show estimated and actual rows and executions. You must
select the Binary Data column before the profiler event adds data to the
T-SQL or SP:stmtcompleted events.
Substantial differences in the estimated row count may indicate the
optimizer had out-of-date statistics or skewed statistics. For example, if
the estimated row count is 2 rows, and the actual row count is 50,000, the
optimizer may have had out-of-date statistics or skewed statistics. Try usin
g
the UPDATE STATISTICS WITH FULLSCAN command.
Hth,
Charly
"Igor Marchenko" wrote:

> That's exactly why I am worried : 'Query Optimizer might not be choosing
> the "fastest" most efficient option in processing the query'. I do know
> accuracy won't be affected.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:2BE6C2AB-8D39-4D79-ACC6-D0DBB427F651@.microsoft.com...
>
>|||Charly,
I did execute UPDATE STATISTICS WITH FULLSCAN but it didn't correct
discrepancies between estimated and actual number of rows.
Regards,
Igor
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:A427092E-9686-4F08-B9F3-115B8C313DA7@.microsoft.com...
> Igor, I found this link:
> http://msdn.microsoft.com/library/d...enetchapt14.asp
> On it I extracted this bit if text...
> Compare Actual vs. Estimated Rows and Executions
> When you read the output from a SHOWPLAN statement, start from the
> most-indented row that has the highest incremental change in the
> TotalSubtreeCost column. Carefully evaluate both the index selection and
> the
> optimizer's estimate by using the SET STATISTICS PROFILE ON command. This
> command runs the statement, so only use it on SELECT statements or T-SQL
> code
> that does not modify data, or you can preface the command with a BEGIN
> TRAN
> /ROLLBACK statement.
> As an alternative, use the new profiler Performance:Showplan Statistics
> event in SQL 2000. This event belongs to event class 98. This event
> reports
> four columns that show estimated and actual rows and executions. You must
> select the Binary Data column before the profiler event adds data to the
> T-SQL or SP:stmtcompleted events.
> Substantial differences in the estimated row count may indicate the
> optimizer had out-of-date statistics or skewed statistics. For example, if
> the estimated row count is 2 rows, and the actual row count is 50,000, the
> optimizer may have had out-of-date statistics or skewed statistics. Try
> using
> the UPDATE STATISTICS WITH FULLSCAN command.
> Hth,
> Charly
> "Igor Marchenko" wrote:
>|||When the optimizer's rowcount estimates are incorrect, often
statistics cannot remedy the problem. For many query plan
operators, the actual rowcount depends on the distribution
of values in two separate columns (perhaps in two separate
tables), and as far as I know, SQL Server keeps no statistics
on how the values in multiple columns are correlated. In
addition, there is no feedback mechanism to do anything with
the information you're seeing - that the actual and estimated
counts are very different.
I have often seen suboptimal query plans be chosen as a
result of this situation, but more often than not, the best
query plan is still being chosen, even though its cost is not
estimated correctly. When there is a better plan the optimizer
is missing, it's my experience that the culprit is less often the
bad rowcounts than that the indexes available are not ideal.
For instance, many queries benefit from nonclustered indexes
on more than a single column, but many designers never define
indexes on more than one column at a time.
Without seeing the query and actual query plan, and assuming
that some basic tuning has already been done to make sure
no more filters can be made SARGable than already are, I can
suggest a couple of things to look for - beyond that, if you post the
query and plan, along with the create table and create index
statements, we can take a further look.
First, if the bad rowcounts appear in parallel operators, you
might try adding the hint OPTION (MAXDOP 1) and see if
performance improves. Bad rowcounts can cause parallelism
to be used in countereffective ways. Second, if you can
identify the particular WHERE conditions that the optimizer
is costing poorly (the optimizer expects many more rows to
satisfy the conditions that actually do), try adding one or more
non-SARGable dummy conditions at the same point in the
query and see if the estimates are more accurate. For example,
if the condition A.col1 = B.col1 is estimated poorly, where
these two columns are integers, try
(A.col1 = B.col1 AND -A.col1 = -B.col1), or try (A.col1 =
B.col1 AND A.col1%2 < 2 AND B.col1%2 < 2). If date
columns are involved, add something like ...
AND MONTH(A.datecolumn) > 0. Be sure to use parentheses
so you don't change the meaning of your query due to the way
AND and OR are associated. The query optimizer handles
non-SARGable predicates generically and this may help out.
A simpler situation where the rowcounts can be
wrong is for non-SARGable conditions
like MONTH(datecolumn) = 1 or like
DATEADD(d,30,datecolumn) > GETDATE(). Here
the optimizer will generally use generic estimates (perhaps
something like "gee, I think about a third of the rows will
satisfy a > condition, no matter what the condition is").
Sometimes the condition can be rewritten so the optimizer
can use statistics instead of a generic estimate.
The second of these examples can better be written as
datecolumn > DATEADD(d,-30,GETDATE()), which
is SARGable. The first can't be SARGed on its own, but
if it is there for something like MONTH(datecolumn) = 1
AND YEAR(datecolumn) = 2005, it can be rewritten as
(datecolumn >= '20050101' and datecolumn < '20050201').
Feel free to post more details.
Steve Kass
Drew University
Igor Marchenko wrote:

>Charly,
>I did execute UPDATE STATISTICS WITH FULLSCAN but it didn't correct
>discrepancies between estimated and actual number of rows.
>Regards,
>Igor
>
>"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
>news:A427092E-9686-4F08-B9F3-115B8C313DA7@.microsoft.com...
>
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:ev8gWnZNFHA.3380@.TK2MSFTNGP15.phx.gbl...

> (A.col1 = B.col1 AND -A.col1 = -B.col1), or try (A.col1 =
> B.col1 AND A.col1%2 < 2 AND B.col1%2 < 2). If date
> columns are involved, add something like ...
> AND MONTH(A.datecolumn) > 0. Be sure to use parentheses
> so you don't change the meaning of your query due to the way
> AND and OR are associated.
Since AND has a higher precedence than OR, I think this caveat would only
apply if NOTs were involved. It never hurts to include extra parentheses, of
course, but in this case, I don't see that they're necessary.
Excellent post, as usual.|||Thank you Steve for your more than elaborate answer! I was able to
speed the query up by implementing clustered index on different set of
columns. Query plan has changed completely. Instead of using hash and merge
joins, SQL server is using inner loop joins.Overall query cost went down
from 137 to 56.1. I am currently verifying all queries accessing table in
question to make sure there is no side affect as a result of different
clustered index. I was wondering if you could provide a reference on where
to read more about query optimization techniques (statistics, better index
selection, etc.)
Thanks,
Igor

Estimated Query Plan

Hi,

I am writing a client application that shows estimated queries plans and statistics. I know how to obtain estimated plans by using SQL Server Management Studio. But is it possible to obtain by using database functions?

I have found sys.dm_exec_query_plan, but it seems that this function can only be used for executed (or executing) queries...

Thanks

SET SHOWPLAN_XML ON

exec YourSP

SET SHOWPLAN_XML OFF

|||

I do not want to obtain the execution plan for a stored procedure. I do want to use a stored procedure (that may be invoked by a client application) to obtain the execution plan for a user-submmitted query. (It is just like SQL Manager Studio does).

Thanks

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A'--Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?
Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
..
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?
|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?
sql

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicate
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the differences
> between Seek Predicate and Predicate?

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Hi experts,
I am looking at an estimated execution plan for a query that joins between 2
significantly big tables. One of the Clustered Index Seek uses has both
Predicate and Seek Predicate. What are the differences between Seek Predicat
e
and Predicate?
In detail, this is what I have done:
...
INNER JOIN BonusPromotion BP ON
...
AND BP.status = 'A' --Active
AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
...
The index used is the primary key along fields.
endDate,
validStartDate,
storeID,
emEAN,
bpID
Seek predicate is along field endDate, while Predicate is along field
validStartDate. My impression of Seek predicate is very fast search
performance. So why can't the Seek predicate include BOTH endDate and
validStartDate? Therefore back to my 1st question: What are the differences
between Seek Predicate and Predicate?Hi
What are the differences between Seek Predicate
> and Predicate?
http://blogs.msdn.com/craigfr/archi.../07/652668.aspx
.
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:4AC93D3A-69C0-4BD2-9CBD-2EEB86714040@.microsoft.com...
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
> 2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek
> Predicate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the
> differences
> between Seek Predicate and Predicate?|||HardKhor,
BP.endDate will be the only seek predicate, because you are not
searching for an exact match for that column, but for a range of values.
This means the other predicates will be evaluated by scanning the index
pages in this range of endDate.
Theoretically, it could still seek the next index column (in your case
validStartDate), but SQL Server does not support this. And even if it
did, the multiple (looped) seeks would only be useful if the preceding
column (in your case endDate) has a very low selectivity, and the index
is very shallow. Otherwise the cost of the multiple seeks would exceed
the cost of scanning the index pages.
HTH,
Gert-Jan
HardKhor wrote:
> Hi experts,
> I am looking at an estimated execution plan for a query that joins between
2
> significantly big tables. One of the Clustered Index Seek uses has both
> Predicate and Seek Predicate. What are the differences between Seek Predic
ate
> and Predicate?
> In detail, this is what I have done:
> ...
> INNER JOIN BonusPromotion BP ON
> ...
> AND BP.status = 'A' --Active
> AND BP.validStartDate >= @.tlBeginDate AND BP.endDate <= @.tlEndDate
> ...
> The index used is the primary key along fields.
> endDate,
> validStartDate,
> storeID,
> emEAN,
> bpID
> Seek predicate is along field endDate, while Predicate is along field
> validStartDate. My impression of Seek predicate is very fast search
> performance. So why can't the Seek predicate include BOTH endDate and
> validStartDate? Therefore back to my 1st question: What are the difference
s
> between Seek Predicate and Predicate?

Estimated Execution Plan Fails, XML Error?

Good afternoon, all. Whenever I try to use Query | Display Estimated
Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
message:
Error processing execution plan results. The error message is:
There is an error in XML document (1, 501).
There is an unclosed literal string. Line 1, position 501.
The only suggestion I've heard of is to instal SQL SPs, but that didn't
help. This is SQL 2K5 running on Win2K3. I don't believe there's any
strangeness like odd characters in instance names, and all language settings
are default. I've seen this before on another server, but Google has nothing.
Any ideas? Thanks!
I think I heard that the XML used for this can not handle some unusual
object names, but at best that is a long shot.
Roy Harvey
Beacon Falls, CT
On Mon, 6 Aug 2007 13:32:02 -0700, JonOfAllTrades
<JonOfAllTrades@.discussions.microsoft.com> wrote:

> Good afternoon, all. Whenever I try to use Query | Display Estimated
>Execution Plan in SQL Studio on any non-trivial query, I get this unhelpful
>message:
>Error processing execution plan results. The error message is:
>There is an error in XML document (1, 501).
>There is an unclosed literal string. Line 1, position 501.
> The only suggestion I've heard of is to instal SQL SPs, but that didn't
>help. This is SQL 2K5 running on Win2K3. I don't believe there's any
>strangeness like odd characters in instance names, and all language settings
>are default. I've seen this before on another server, but Google has nothing.
> Any ideas? Thanks!

Estimated Execution Plan

How do I display this in Query Analyzer ?Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.sql

Estimated Execution Plan

How do I display this in Query Analyzer ?
Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>
|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>
|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.

Estimated Execution Plan

How do I display this in Query Analyzer ?Hi,
Go to Query menu -- Click Show Execution plan.
After that execute the Query.
Thanks
Hari
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||To display the estimated execution plan, select Query --> Display Estimated
Execution Plan on the menu or press Ctrl-L.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alan" <NOSPAMalan_pltse@.yahoo.com.au> wrote in message
news:%23blIBj5yEHA.3236@.TK2MSFTNGP15.phx.gbl...
> How do I display this in Query Analyzer ?
>|||On Tue, 16 Nov 2004 08:10:29 -0600, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:
>To display the estimated execution plan, select Query --> Display Estimated
>Execution Plan on the menu or press Ctrl-L.
Or click the little icon.
Or SET SHOWPLAN_ALL ON or SET SHOWPLAN_TEXT ON.
J.

Estimated cost shows 300% in query plan

Hello all,
On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
(Windows Server 2003), most of the query plans I see are showing, on the
estimated cost of each step of the plan, more than 100% (e.g. 300% for a
seek).
Does anyone have an explanation or has already seen that ?
tia,
Rudi Bruchez
MCDBAIt's a bug in the graphical query plans when dealing with parallel query
plans. I have seen percentage reaching 4 figures even. I don't think it will
be fixed, considering it wasn't fixed in SP4.
--
Jacco Schalkwijk
SQL Server MVP
"Rudi Bruchez" <rudi#no-spam#at.babaluga.com> wrote in message
news:138syoixibdyn$.14zc2fkwrr1j5.dlg@.40tude.net...
> Hello all,
> On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
> (Windows Server 2003), most of the query plans I see are showing, on the
> estimated cost of each step of the plan, more than 100% (e.g. 300% for a
> seek).
> Does anyone have an explanation or has already seen that ?
> tia,
> Rudi Bruchez
> MCDBA|||On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
> It's a bug in the graphical query plans when dealing with parallel query
> plans. I have seen percentage reaching 4 figures even. I don't think it will
> be fixed, considering it wasn't fixed in SP4.
Hi,
Thanks for the feedback. My problem is that I've set the maximum degree of
parallelism to 1 at the server level, and there's no mention of parallelism
on the graphical plan. Should this still appear ?
Btw, even with a maxdop to 1, I still see some "degree of parallelism"
event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
Is it explanable ?
thanks again,
Rudi Bruchez|||On Thu, 27 Oct 2005 17:05:35 +0200, Rudi Bruchez
<rudi#no-spam#at.babaluga.com> wrote:
>On a SQL Server 2000 sp4, on a 4 Xeon cpus (with hyperthreading) machine
>(Windows Server 2003), most of the query plans I see are showing, on the
>estimated cost of each step of the plan, more than 100% (e.g. 300% for a
>seek).
>Does anyone have an explanation or has already seen that ?
It happens.
:)
How about the after-query plan?
J.|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>> It's a bug in the graphical query plans when dealing with parallel
>> query plans. I have seen percentage reaching 4 figures even. I don't
>> think it will be fixed, considering it wasn't fixed in SP4.
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:
> Rudi Bruchez wrote:
>> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>> It's a bug in the graphical query plans when dealing with parallel
>> query plans. I have seen percentage reaching 4 figures even. I don't
>> think it will be fixed, considering it wasn't fixed in SP4.
>> Hi,
>> Thanks for the feedback. My problem is that I've set the maximum
>> degree of parallelism to 1 at the server level, and there's no
>> mention of parallelism on the graphical plan. Should this still
>> appear ?
>> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
>> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
>> Is it explanable ?
>> thanks again,
>> Rudi Bruchez
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.

Estimated cost shows 300% in query plan

How about the after-query plan?
J.
|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:

> Rudi Bruchez wrote:
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.

Estimated cost shows 300% in query plan

How about the after-query plan?
J.|||Rudi Bruchez wrote:
> On Thu, 27 Oct 2005 16:13:39 +0100, Jacco Schalkwijk wrote:
>
> Hi,
> Thanks for the feedback. My problem is that I've set the maximum
> degree of parallelism to 1 at the server level, and there's no
> mention of parallelism on the graphical plan. Should this still
> appear ?
> Btw, even with a maxdop to 1, I still see some "degree of parallelism"
> event in profiler, with a BinaryData (CPUs involved) at 0X00000000.
> Is it explanable ?
> thanks again,
> Rudi Bruchez
What happens with the actual plan, not the estimated one? Does it
display correctly? What happens if you add a MAXDOP (1) to the query?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 27 Oct 2005 18:33:59 -0400, David Gugick wrote:

> Rudi Bruchez wrote:
> What happens with the actual plan, not the estimated one? Does it
> display correctly? What happens if you add a MAXDOP (1) to the query?
Hello,
I was talking about the actual plan, no difference there with the
estimated, on both I see 300% on several seeks. Same if I put the MAXDOP
(1) option on the query.
I'm interested in this also because it is a server hosted by an ISP, and
there are sometimes performances problems I've difficulties to explain form
the SQL server perspective only. I'm tracing peculiarities which could be
signs for problems.sql

Monday, March 26, 2012

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();
On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStrXing].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).
|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegro ups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=tr=ADing].ToString());
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnection
String].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=[/vb
col]
tr=ADing].ToString());[vbcol=seagreen]
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnecti
onString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I'm not using any fancy ado.net objects:

string sql= [whatever the user passes in]

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

try
{
int result = command.ExecuteNonQuery();

if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}

connection.Close();If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"eggie5" <eggie5@.gmail.comwrote in message
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...

Quote:

Originally Posted by

>I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
>
I'm not using any fancy ado.net objects:
>
string sql= [whatever the user passes in]
>
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
>
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
>
>
try
{
int result = command.ExecuteNonQuery();
>
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
>
connection.Close();
>

|||oops sorry!

On Jun 3, 8:18 pm, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
>
This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"eggie5" <egg...@.gmail.comwrote in message
>
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...
>

Quote:

Originally Posted by

Quote:

Originally Posted by

I'm not using any fancy ado.net objects:

Quote:

Originally Posted by

string sql= [whatever the user passes in]

Quote:

Originally Posted by

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStr ing].ToString());
connection.Open();

Quote:

Originally Posted by

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

Quote:

Originally Posted by

try
{
int result = command.ExecuteNonQuery();

Quote:

Originally Posted by

Quote:

Originally Posted by

connection.Close();

Thursday, March 29, 2012

estimating tempdb usage

Estimating Table Sizes

Estimating Table Sizes

Estimating Table Sizes

Tuesday, March 27, 2012

Estimated row count vs. actual row count

Estimated Query Plan

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Estimated Execution Plan-Diff betw Seek Predicate and Predicate?

Estimated Execution Plan Fails, XML Error?

Estimated Execution Plan

Estimated Execution Plan

Estimated Execution Plan

Estimated cost shows 300% in query plan

Estimated cost shows 300% in query plan

Estimated cost shows 300% in query plan

Monday, March 26, 2012

escaping data for update query

escaping data for update query

escaping data for update query

escaping data for update query

ERROR: Subreport

Blog Archive

About Me