sql bit data type: tables

Showing posts with label tables. Show all posts

Thursday, March 22, 2012

Bookmark lookup cost factors (SQL Server 7.0)

Good day to you.
I am working with some large databases with several tables hosting
millions or tens of millions of records. The actual data is being
stored on a SAN, while the database host is a 4 CPU SMP machine. In
this case the SAN is often a bottleneck while the CPUs are idled.
A situation that I am consistently observing is extremely poor choices
for the uses of indexes -- or rather the lack of index use. What I am
constantly finding for these large tables is that highly unique
non-clustered indexes are being ignored, and instead a table scan is
being performed. The execution engine is (wrongly) assuming that the
bookmark lookups will be more costly than simply doing a tablescan.
The end result is that many queries run 10s or 100s of times longer
than they should without explicitly adding index hints to force it to
use the index.
In one example a table with 5,000,000 or so records has a
non-clustered index on a field, and the statistics believe that there
are around 141 records that match the requested specific criteria
(there are really less -- as a sidenote can one increase the number of
steps stored by statistics?). Instead of quickly pulling the presumed
141 index matches and then doing the corresponding bookmark lookups,
it's instead table scanning all 5,000,000 records. Indeed if I look at
the estimated execution plans, it's claiming that the forced use of
the non-clutered index, and hence forced bookmark lookups, will yield
5x the subtree cost. The end results tell the real story, though -
with the index hint it completes instantly, while without the hint it
takes hundreds of times longer.
Is there a server or database setting that governs the estimation of
the cost of bookmark lookups? I do not wish to resort to unnecessary
hardcoded index hints when it's such a basically incorrect assumption
by the query engine that affects the entire system wherever large
tables are accessed. As a sidenote I had previously heard the
statistic that a non-clustered index will only be used if the
estimated set yields less than 2% of the total set, due to the cost of
bookmark lookups, but in this case it isn't using it even when the
estimated set is ~ 0.003%... how is it choosing?
Thanks
Dennis ForbesHi Dennis,
I think that 285996 PRB: Cost of Using Non-Clustered Indexes May Be Incorrect if Data (
http://support.microsoft.com/?id=285996) is something that may be applicable
to your scenario. Unfortunately there are no 'knobs' that tune the cost es
timation for any operators inside SQL Server.
Thanks,
--R
"Dennis Forbes" <dennis_forbes@.hotmail.com> wrote in message news:32811a52.0
403190902.35fd2ef5@.posting.google.com...
Good day to you.
I am working with some large databases with several tables hosting
millions or tens of millions of records. The actual data is being
stored on a SAN, while the database host is a 4 CPU SMP machine. In
this case the SAN is often a bottleneck while the CPUs are idled.
A situation that I am consistently observing is extremely poor choices
for the uses of indexes -- or rather the lack of index use. What I am
constantly finding for these large tables is that highly unique
non-clustered indexes are being ignored, and instead a table scan is
being performed. The execution engine is (wrongly) assuming that the
bookmark lookups will be more costly than simply doing a tablescan.
The end result is that many queries run 10s or 100s of times longer
than they should without explicitly adding index hints to force it to
use the index.
In one example a table with 5,000,000 or so records has a
non-clustered index on a field, and the statistics believe that there
are around 141 records that match the requested specific criteria
(there are really less -- as a sidenote can one increase the number of
steps stored by statistics?). Instead of quickly pulling the presumed
141 index matches and then doing the corresponding bookmark lookups,
it's instead table scanning all 5,000,000 records. Indeed if I look at
the estimated execution plans, it's claiming that the forced use of
the non-clutered index, and hence forced bookmark lookups, will yield
5x the subtree cost. The end results tell the real story, though -
with the index hint it completes instantly, while without the hint it
takes hundreds of times longer.
Is there a server or database setting that governs the estimation of
the cost of bookmark lookups? I do not wish to resort to unnecessary
hardcoded index hints when it's such a basically incorrect assumption
by the query engine that affects the entire system wherever large
tables are accessed. As a sidenote I had previously heard the
statistic that a non-clustered index will only be used if the
estimated set yields less than 2% of the total set, due to the cost of
bookmark lookups, but in this case it isn't using it even when the
estimated set is ~ 0.003%... how is it choosing?
Thanks
Dennis Forbessql

Bookmark Lookup

Fellow Developers

i have a query that has joins of tables with huge data (more than 2G of records per table). the execution plan shows me the 80% of the execution is on a "Bookmark Lookup" on the biggest table. Does anyone have clue how can I optimize this query? other than using covering indexes...

best regards

Jeries Shahin wrote:
Other than using covering indexes...

Well, if you know the answer already....

Seriously, you need to understand how indexes in SQL Server work (you may already, so here is the short version.) The nonclustered index uses the key of the clustered index rather than keeping a pointer to the physical page. Of course, this is great almost all of the time, but can be a costly operation at times.

Are you using any hints? And what join operators are being used? That is a lot of data (assuming 2G = 2 GB and not 2 grand :) and I would have assumed it would do a hash join, unless this is doing a merge join. Posting the output of showplan_text would be a good place to start:

set showplan_text on
go

select ...
go

set showplan_text off
go

And what version of SQL Server are you on? The new INCLUDE clause on the CREATE INDEX statement could actually be the ticket. It gives you a covering index without the overhead of including data you aren't using for searching on in the B-Tree (only the leaf nodes are affected)

|||

Why don't you want to use a covering index. Thats like saying I want my car to go but I don't want to use gas. If you want performance from a relational DB system you need to use the right indexes.

I agree that the "include" option in SQL 2005 maybe an option.

|||

Using a covering index can be really costly if the index keys are really large, so it might not be a good idea in 2000 or earlier to cover an index. We don't know his usage pattern and that is a lot of data (again assuming that G means GB :) This query might only be executed once a day/week/month. There may be thousands of modifications a minute on the table.

It might even be that a reporting database/warehouse is in order.

|||

I agree but to some extent if the performance isn't what is required, then something has to be done and there are a number of options covering indexing being on of them, redesign being another.

One always has to balance out their performance needs. This gets much more complex when doing DSS stuff on an OLTP system. ideally they should be mutually exclusive.

Tuesday, March 20, 2012

BOL Package example Errors

Hi There

Ok i realize this may not be exactly the correct place to post this, but i seem to get good advice here.

I am trying to follow the SMO Tables DBCC Package Sample in BOL.

I have copied the Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.SmoEnum.dll to my latest .NET Framework folder as specified in BOL example.

Problem is when i try to run the package i get the following error:

An error occured while compiling the script for the Script Task

Error 30466: Namespace or type specified in the Imports 'Microsoft.SqlServer.Management.Common' cannot be found. Make sure the name space or the type is defined and it doesn't contain other aliases.
Line 9 Column 9 through 45

Imports Microsoft.SqlServer.Management.Common

Now i am guessing it has something to do with the SqlSmo dll's , i am not sure what to check as i have copied them to my .NET framework.

Someone suggested i should go to my .NET Framework 2.0 configuration and confirm that it has picked up the new smo dll's, but since installing the Beta .Net Framework 2.0 from the June CTP i get the following error when i try open the configuration:

Snap-in failed in to initialize
.NET Framework 2.0 Configuration

I have tried reinstalling the .Net Framework and i still get the same error, have not found any appropriate solutions on the net.

Are these issues related ? Any help would be greatly appreaciated.

Thanx
I'm not familiar with that sample. Where are you trying to use the SMO assemblies? In the script task?|||Hi Kirk

That is correct.

If you have installed the sample packages in the default directory.
You can open the project from the following path:

C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\SmoTablesDBCC\SmoTablesDBCC\SmoTablesDBCC.dtproj

If you have not installed tthe sample packages i can copy the script and post it here if you like.

Thanx|||Sounds like you haven't moved over the assembly.
I describe how to do that here:
http://sqljunkies.com/WebLog/knight_reign/archive/2005/07/07/16018.aspx

Thanks,|||Hi Kirk

Ok please bear with me but i have a few questions.

Firstly here are the assembly imports as they are in the script.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.Common ***
Imports Microsoft.SqlServer.Management.Smo

The one marked *** is the one with the compilation error.
Now for the example package i copied the Microsoft.SqlServer.SmoEnum.dll and Microsoft.SqlServer.Smo.dll assemblies to %windir%\Microsoft.net\framework\v2.0.xxxxx\ as specified. Now these refer to the import following the one giving me the error.So question number 1 what is the correct assembly for Imports Microsoft.SqlServer.Management.Common? As i can find no Management.Common assembly on the CTP assemblies folder? In other words i am not sure which assembly goes with that import.
Secondly I cannot find the option Project - Add References in BI studio from the project menu or an option like that in the script task editor, i have feeling i am being pretty stupid here, but i cannot, maybe it was another CTP release and it has another name in the June release?

Thanx Again Kirk|||Apologies, I assumed that the assemblies were correctly listed. The correct assembly for that namespace is actually: Microsoft.SQLServer.ConnectionInfo.dll
HTH|||Hi Kirk

Thank You it works now, i wonder why there is no mention of that assemly in the package examples in BOL? As it does nto work without it.
Lastly if you dont mind why did i not have to add a reference to it in BI, as stipulated in your link? As i mentioned above i cannot find this option in BI stdio?

Thanx Again|||Sean,
The documentation team are encouraging people to use the "Send Feedback" link in BOL if you've any problems with it. Drop them a line on that and it'll get actioned.

-Jamie|||

Lastly if you dont mind why did i not have to add a reference to it in BI?
Not clear on the question but will venture an answer.
You did add it in the VSA environment, right? The VSA environment is a sub-environment that is agnostic to and ignorant of the fact that it's running inside the BI environment. So, the VSA script can reference the assembly without the BI environment even knowing about it. I think that's the answer to the question...

|||

The information in the Readme for this sample explains some of the issues discussed in this thread.

The Script task project already contains the necessary References for all the managed namespaces that are also imported by using Imports statements. However certain DLLs need to be copied to the .NET directory as described in the Readme to be "visible" to the Script task.

You need to make the Project Explorer window visible to view and add References.

After copying the DLLs, you may need to close and reopen the script for the blue squigglies under the imported namespaces to disappear. If that doesn't work, try setting Precompile to False temporarily.

-Doug

|||Hi Kirk

That does clear things up, i found it in the VSA environment, bit unclear in the link i was trying to find it in BI, Thanxsql

Monday, March 19, 2012

blonde to write query or design fault

Dear All,

Im wondering if its the design that needs to be changed or I simply cant put this together.

I have 3 tables.

1. people (peopId, peopFName, peopSName etc.)
2. codes (codeId, codeName)
3. codedPeople(codePeopleId, peopId, codeId)

Codes represent different skills of people, example the sort of job functions theyve held in their employment. Like:

t-CEO,
t-CFO
t-Founder
etc.

people, clearly holds data about people.

CodedPeople holds data about which people are coded. So person1 can be coded as t-CEO as well t-Founder, and person2 coded as t-CFO

What I need is a query that returns all distinct people records and takes a number of codeNames as input. So if I throw in t-CEO OR t-Founder I get person1, again if I define t-CEO AND t-Founder I get person1.

However when I add t-CEO OR t-CFO I get person1 and person2 but when the query takes t-CEO AND t-CFO I get no result.

I cant seem to come up with anything that would give me a good starting point. Is there a design fault here? All opinions are much appreciated, thanks in advance!"the query takes t-CEO AND t-CFO I get no result."

Is that wrong? No person is t-CEO AND t-CFO in your example.

Please tell us what you want the result to be.|||Thanks for getting back!

Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)

I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CFO, t-Founder)

I also coded person2 as a technology-Chief Financial Officer (t-CFO)

I want to write a query that takes codes as parameters:

t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)

t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)

t-CFO or t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)

Am I describing it correctly?

Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder

I have:

SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName

But thats useless!|||Let me correct that, I have mixed up the similar codes of t-CEO and t-CFO, the correct one is:

Thanks for getting back!

Ok, so I have 3 codes and 2 people in the database. (In reality its about 250 different codes and about 10,000 people, growth is about 5000 / year)

I coded person1 as a technology-Chief Executive Officer and also as a technology-Founder (t-CEO, t-Founder)

I also coded person2 as a technology-Chief Financial Officer (t-CFO)

I want to write a query that takes codes as parameters:

t-CEO AND t-Founder = returns person1 (as hes coded as a t-CEO and t-Founder)
t-CEO OR t-CFO = returns person1 and person2 (as person1 is coded as t-CEO and person2 is coded as t-CFO)

t-CEO AND t-CFO = returns no result ( as no person in the db is coded as both a t-CEO and also a t-CFO)

t-CFO OR t-Founder = returns person1 and person2 (as person1 is coded as a t-Founder and person2 is coded as a t-CFO)

Am I describing it correctly?

Of course the query need to be flexible as Ill using it from ASP.NET dropping in the parameters so codeName Like t-CEO OR codeName Like t-Founder

I have:

SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' ) )
ORDER BY peopSName, peopFName

But thats useless!

blocks hangup database when inserting in 1 specific table

Hi,

We use a database with about 40 related tables. Some tables contain as
much as 30.000 records. We use Access97 as an interface to the
database. Now recently we have the problem that when we want to insert
a row in one specific table (alwasy the same) the database makes
blocks.

Details:
- about 10% of the data was inserted using copying from Excel, before
this action there was no problem, though there is no evidence that
this causes the problem.
- inserting rows via the Query Analyzer works fine, via Access causes
trouble.
- the tempdb lofile has grown to 48Mb.

Has anyone ideas about what is going on and what I can do to solve the
problem?

TAV,
Jan WillemsJan Willems (jwillems@.xs4all.nl) writes:
> We use a database with about 40 related tables. Some tables contain as
> much as 30.000 records. We use Access97 as an interface to the
> database. Now recently we have the problem that when we want to insert
> a row in one specific table (alwasy the same) the database makes
> blocks.

"Makes blocks"? You mean that the INSERT operation is blocked, and you
have to cancel the operation to continue?

When the situation occurs, use sp_who from Query Analyzer, and see if
any process has a non-zero value in the Blk column. In such case, the
process listed in Blk, blocks the spid of that row.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Blocking while getting Snapshot

We have a production database that is replicated(transactional).
Whenever we have to take a snapshot, it creates blocking on the
production tables and causes timeouts within the production
application. This is creating big problems and I am wondering if there
is away to avoid the blocking which causes the timeouts.
Please have a look at the option to allow concurrent snapshot generation
(snapshot tab - 'do not lock tables...').
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||That works very well for us. Thanks for the information.
|||I have the same problem and tried to use the 'Do Not Lock Table Option'.
However, the snapshot loading failed. Please help if you could post your
test result in this threat. Thanks a lot.
|||Please can you post up your complete error message.
Rgds,
Paul Ibison

Sunday, March 11, 2012

Blocking processes

In sql2005 database it happenings that 2 processes lock different tables and
each process wait for the other to finish - or something similar.
The result is that application waits and nothing works.
I heard that SQL2005 automatically handles this situations and kill process
who did less work. Is there some setting?
Regards,SAre you describing a deadlock scenario? SQL Server has had deadlock detectio
n and resolution since
version 1.0 (although the internal algorithms has changed over the versions)
. I suggest that you
start reading up on "deadlock" (Books Online, Google etc) to see if this is
what you refer to.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:uWwj27UWGHA.1084@.TK2MSFTNGP04.phx.gbl...
> In sql2005 database it happenings that 2 processes lock different tables a
nd each process wait for
> the other to finish - or something similar.
> The result is that application waits and nothing works.
> I heard that SQL2005 automatically handles this situations and kill proces
s who did less work. Is
> there some setting?
> Regards,S
>|||Well I have locked processes, which never finishes.
But as I heared, the SQL2005 now has automatic detection and kill the
process who has done less job until deadlock.
What can I do?
It's not mine application so I don't know exactly what is happening.
When I kill process manually, than everything works.
It's happening about once a w

.
Regards,Simon
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvNnuBVWGHA.2080@.TK2MSFTNGP05.phx.gbl...
> Are you describing a deadlock scenario? SQL Server has had deadlock
> detection and resolution since version 1.0 (although the internal
> algorithms has changed over the versions). I suggest that you start
> reading up on "deadlock" (Books Online, Google etc) to see if this is what
> you refer to.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "simonZ" <simon.zupan@.studio-moderna.com> wrote in message
> news:uWwj27UWGHA.1084@.TK2MSFTNGP04.phx.gbl...
>|||> Well I have locked processes, which never finishes.
Than you don't have a deadlock, you just have a blocking scenario. Unless yo
u have a deadlock that
haven't been detected by SQL Server, which would be considered a bug. If it
is a blocking scenario,
you have to find who is blocking the others, and why that transaction doesn'
t finish.

> It's not mine application so I don't know exactly what is happening.
Blocking and deadlock problems is an application problem, so you need to tal
k to the application
vendor about this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"simonZ" <simon.zupan@.studio-moderna.com> wrote in message
news:OO$MWAYWGHA.4212@.TK2MSFTNGP02.phx.gbl...
> Well I have locked processes, which never finishes.
> But as I heared, the SQL2005 now has automatic detection and kill the proc
ess who has done less
> job until deadlock.
> What can I do?
> It's not mine application so I don't know exactly what is happening.
> When I kill process manually, than everything works.
> It's happening about once a w

.
> Regards,Simon
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OvNnuBVWGHA.2080@.TK2MSFTNGP05.phx.gbl...
>

Blocking MS Access from linking tables...

Good morning...
I have an Access front end that uses SQL Server linked tables. SQL Server
uses Windows authentication. I have one Windows group that all Access users
are a member of. I added that group to SQL Server logins and gave it
public, datareader, and datawriter rights to the one database that's used.
My front end is locked down, but I want to stop users from creating a new
.mdb and linking SQL Server tables through DSNs or ADO connections or even
just importing the links from the actual front end.. I've tried setting the
"denydatareader" security policy - that keeps the SQL tables from being seen
in the import/link list- but also blocks read rights from the actual front
end database. I could set an Access database password on the front end to
block importing the links, but that only solves one of the three problems
and I want to stay away from Access security altogether.
Is there a way to stop users from creating their own DSNs or connection
objects or linking tables while still using Windows authentication?
Thanks.
Matthew Wells
MWells@.FirstByte.netNo. If you're using Windows authentication, and have granted
users/roles permissions on the base tables, then they can get at the
data no matter what tool they use. An alternative would be to revoke
permissions to public on the tables, and create an Access application
that does not use linked tables, but instead uses pass-through queries
to execute stored procedures. This is a lot more work since you'll
need to create an unbound FE, but it can be done. Only users who are
comfortable working with stored procedures would be able to get at the
data. Another option would be application roles, but they are a really
poor choice for linked table apps. see
http://support.microsoft.com/defaul...;EN-US;Q229564.
--Mary
On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>Good morning...
>I have an Access front end that uses SQL Server linked tables. SQL Server
>uses Windows authentication. I have one Windows group that all Access user
s
>are a member of. I added that group to SQL Server logins and gave it
>public, datareader, and datawriter rights to the one database that's used.
>My front end is locked down, but I want to stop users from creating a new
>.mdb and linking SQL Server tables through DSNs or ADO connections or even
>just importing the links from the actual front end.. I've tried setting th
e
>"denydatareader" security policy - that keeps the SQL tables from being see
n
>in the import/link list- but also blocks read rights from the actual front
>end database. I could set an Access database password on the front end to
>block importing the links, but that only solves one of the three problems
>and I want to stay away from Access security altogether.
>Is there a way to stop users from creating their own DSNs or connection
>objects or linking tables while still using Windows authentication?
>Thanks.
>Matthew Wells
>MWells@.FirstByte.net
>|||I read that article. It seems to apply only to ADO conenctions. Aren't
linked tables DAO? Does connection pooling work the same way? This is a
database that was converted from Access to SQL Server. We have to lock down
the data from any outside attempts to get it. I don't want to use SQL
authentication because I don't want to maintain two sets of security logins.
I know that using an Access form can create multiple SPIDs on SQL Server
(combo box rowsources et al). What is the downside of using Application
Roles?
Thanks.
Matthew Wells
MWells@.FirstFleet.com
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
> No. If you're using Windows authentication, and have granted
> users/roles permissions on the base tables, then they can get at the
> data no matter what tool they use. An alternative would be to revoke
> permissions to public on the tables, and create an Access application
> that does not use linked tables, but instead uses pass-through queries
> to execute stored procedures. This is a lot more work since you'll
> need to create an unbound FE, but it can be done. Only users who are
> comfortable working with stored procedures would be able to get at the
> data. Another option would be application roles, but they are a really
> poor choice for linked table apps. see
> http://support.microsoft.com/defaul...;EN-US;Q229564.
> --Mary
> On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
> <MWells@.FirstByte.net> wrote:
>
Server[vbcol=seagreen]
users[vbcol=seagreen]
used.[vbcol=seagreen]
even[vbcol=seagreen]
the[vbcol=seagreen]
seen[vbcol=seagreen]
front[vbcol=seagreen]
to[vbcol=seagreen]
>|||Yes, connection pooling works the same way. If you are serious about
locking down the SQL Server database, then DO NOT use Access as a
front-end unless you use it in an unbound scenario as I described
below. You will need to revoke or deny permissions on the tables and
create parameterized stored procedures for all DML operations so that
users can interact with the data only through your stored procedures,
which are executed using a least-priviledged account.
Application roles are intrinsically insecure because you must store
the password that activates them on the client, where it can be
discovered by a determined attacker. If you create an unbound
application that executes under least priviledges, then there likely
won't be much penalty for using them (other than performance) or much
harm if the password is uncovered, but then there's not much benefit,
either. Using Windows authentication is more secure than SQL logins,
and if all users belong to roles that have extremely restricted
permissions that only allow them to execute parameterized stored
procedures, then that's about the best you can do. You can also
provide additional verification and validation in your stored
procedure code (for example, only allowing users to access rows that
they "own").
There is code and discussion of the techniques involved in writing an
unbound Access application in the book described in my sig. It's a lot
of work, but if security is a priority then you have to do it. Access
was designed to be easy to use, not secure.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 01 Nov 2004 14:29:21 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>I read that article. It seems to apply only to ADO conenctions. Aren't
>linked tables DAO? Does connection pooling work the same way? This is a
>database that was converted from Access to SQL Server. We have to lock dow
n
>the data from any outside attempts to get it. I don't want to use SQL
>authentication because I don't want to maintain two sets of security logins
.
>I know that using an Access form can create multiple SPIDs on SQL Server
>(combo box rowsources et al). What is the downside of using Application
>Roles?
>Thanks.
>Matthew Wells
>MWells@.FirstFleet.com
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
>Server
>users
>used.
>even
>the
>seen
>front
>to
>

Thursday, March 8, 2012

blocking caused from SQL with (nolock) hint

I am seeing blocking in a database that is caused by a SQL statement that
joins two tables with (nolock) hints. How is that possible? I thought
nolock would perform a dirty read and would not block readers. Is that not
the case?
Thanks,
JayJay,
No more information (environment, SQL code, etc) than the question, means
this question is hard to answer.
(1) If your SQL is doing an update, then (of course) it will lock the
resources being updated.
(2) I have also seen a repeated instance of an older version of Access
causing a lock (a SCH-M lock) even though it had no rights to make any
schema change.
Post some more details if you have them.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> I am seeing blocking in a database that is caused by a SQL statement that
> joins two tables with (nolock) hints. How is that possible? I thought
> nolock would perform a dirty read and would not block readers. Is that
not
> the case?
> Thanks,
> Jay|||Jay P wrote:
> I am seeing blocking in a database that is caused by a SQL statement
> that joins two tables with (nolock) hints. How is that possible? I
> thought nolock would perform a dirty read and would not block
> readers. Is that not the case?
> Thanks,
> Jay
I've seen undesirable results with NOLOCK on temp tables. Is this the
case? Post your SQL please.
--
David Gugick
Imceda Software
www.imceda.com|||The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b WHERE
a.col1='x' and b.col2='y' ' I am using a script to check for blocking that
generates a SQL Profiler trace and also using the sp_pss80 script to show
locks and input buffer contents but I'm having problems interpreting the
output. I do know that when I run this statement, I get some blocking going
on and I'm confused by the fact that it's just a SELECT (dirty read) type
operation albeit on a rather large table of appx. 13 million rows and is
doing a index range scan... Thanks for the reply.
"Russell Fields" wrote:
> Jay,
> No more information (environment, SQL code, etc) than the question, means
> this question is hard to answer.
> (1) If your SQL is doing an update, then (of course) it will lock the
> resources being updated.
> (2) I have also seen a repeated instance of an older version of Access
> causing a lock (a SCH-M lock) even though it had no rights to make any
> schema change.
> Post some more details if you have them.
> RLF
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > I am seeing blocking in a database that is caused by a SQL statement that
> > joins two tables with (nolock) hints. How is that possible? I thought
> > nolock would perform a dirty read and would not block readers. Is that
> not
> > the case?
> >
> > Thanks,
> > Jay
>
>|||Jay,
Hmmmm....
If the join and select is big enough, SQL Server may need to create
worktables in order to handle the whole operation. It is possible that (if
worktables are being created) that you are doing some blocking on tempdb
system tables. Is that possible?
Beyond that, I have no brilliant ideas, because (as you say) you should be
getting dirty reads without locking.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
WHERE
> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
that
> generates a SQL Profiler trace and also using the sp_pss80 script to show
> locks and input buffer contents but I'm having problems interpreting the
> output. I do know that when I run this statement, I get some blocking
going
> on and I'm confused by the fact that it's just a SELECT (dirty read) type
> operation albeit on a rather large table of appx. 13 million rows and is
> doing a index range scan... Thanks for the reply.
> "Russell Fields" wrote:
> > Jay,
> >
> > No more information (environment, SQL code, etc) than the question,
means
> > this question is hard to answer.
> >
> > (1) If your SQL is doing an update, then (of course) it will lock the
> > resources being updated.
> >
> > (2) I have also seen a repeated instance of an older version of Access
> > causing a lock (a SCH-M lock) even though it had no rights to make any
> > schema change.
> >
> > Post some more details if you have them.
> >
> > RLF
> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > > I am seeing blocking in a database that is caused by a SQL statement
that
> > > joins two tables with (nolock) hints. How is that possible? I
thought
> > > nolock would perform a dirty read and would not block readers. Is
that
> > not
> > > the case?
> > >
> > > Thanks,
> > > Jay
> >
> >
> >|||When worktables are created there should not be blocking.
You could use sp_lock to find out if blocking is caused by any lock
resource, and use select * from sysprocesses to find out the wait type and
wait time. The Books Online has more details on how to use the two.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23ErXqoW3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Jay,
> Hmmmm....
> If the join and select is big enough, SQL Server may need to create
> worktables in order to handle the whole operation. It is possible that
> (if
> worktables are being created) that you are doing some blocking on tempdb
> system tables. Is that possible?
> Beyond that, I have no brilliant ideas, because (as you say) you should be
> getting dirty reads without locking.
> RLF
>
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
>> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
> WHERE
>> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
> that
>> generates a SQL Profiler trace and also using the sp_pss80 script to show
>> locks and input buffer contents but I'm having problems interpreting the
>> output. I do know that when I run this statement, I get some blocking
> going
>> on and I'm confused by the fact that it's just a SELECT (dirty read) type
>> operation albeit on a rather large table of appx. 13 million rows and is
>> doing a index range scan... Thanks for the reply.
>> "Russell Fields" wrote:
>> > Jay,
>> >
>> > No more information (environment, SQL code, etc) than the question,
> means
>> > this question is hard to answer.
>> >
>> > (1) If your SQL is doing an update, then (of course) it will lock the
>> > resources being updated.
>> >
>> > (2) I have also seen a repeated instance of an older version of Access
>> > causing a lock (a SCH-M lock) even though it had no rights to make any
>> > schema change.
>> >
>> > Post some more details if you have them.
>> >
>> > RLF
>> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
>> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
>> > > I am seeing blocking in a database that is caused by a SQL statement
> that
>> > > joins two tables with (nolock) hints. How is that possible? I
> thought
>> > > nolock would perform a dirty read and would not block readers. Is
> that
>> > not
>> > > the case?
>> > >
>> > > Thanks,
>> > > Jay
>> >
>> >
>> >
>

Wednesday, March 7, 2012

Blocked tables

From time to time I get blocked tables in my database and application stope
working.
So I try next example:
declare @.n int
set @.n=50
while @.n>0
begin
SELECT * FROM table1 INNER JOIN table2...
set @.n=@.n-1
end
While this selects are working I try
in other query analyzer window to create an update on table2:
UPDATE table2 set column1='test'
and I get blocked tables.
I guess something similar is happening in my application.
How can I prevent this blocking?
regards,SIf you are happy with dirty reads you can do this
SELECT * FROM table1 with (nolock) INNER JOIN table2 with (nolock) ...
http://sqlservercode.blogspot.com/
"simon" wrote:

> From time to time I get blocked tables in my database and application sto
pe
> working.
> So I try next example:
> declare @.n int
> set @.n=50
> while @.n>0
> begin
> SELECT * FROM table1 INNER JOIN table2...
> set @.n=@.n-1
> end
> While this selects are working I try
> in other query analyzer window to create an update on table2:
> UPDATE table2 set column1='test'
> and I get blocked tables.
> I guess something similar is happening in my application.
> How can I prevent this blocking?
> regards,S
>
>|||Why the tables are blocked until I restart the sql server ?
I can't write nolock in each query. Is there some other way?
"SQL" <SQL@.discussions.microsoft.com> wrote in message
news:E17B8CBD-CF39-4857-8FEF-7AEBEE25375C@.microsoft.com...
> If you are happy with dirty reads you can do this
> SELECT * FROM table1 with (nolock) INNER JOIN table2 with (nolock) ...
>
> http://sqlservercode.blogspot.com/
> "simon" wrote:
>|||On Tue, 25 Oct 2005 15:18:31 +0200, simon wrote:

>Why the tables are blocked until I restart the sql server ?
>I can't write nolock in each query. Is there some other way?
Hi Simon,
It appears to me that there are two things wrong:
1. You have somehow set your transactions to an isolation leven that is
higher than the standard "READ COMMITTED" level. With read committed,
locks for data being read are released when the statement finishes. With
REPEATABLE READ and SERIALIZABLE, locks are held until the end of the
transaction.
2. You are starting transactions that you don't finish. That might be
because not each BEGIN TRANSACTION in your app is matched by either a
COMMIT TRANSACTION or a ROLLBACK TRANSACTION, or because you don't use
explicit transactions, but have the autocommit transaction mode switched
off using SET IMPLICIT_TRANSACTIONS ON (that means that transactions are
automatically started by SQL Server, but they still have to be ended by
an explicit COMMIT or ROLLBACK statement).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo,
I don't set any transaction.
I just open sqlQueryAnalyzer and put this code:
declare @.n int
set @.n=50
while @.n>0
begin
SELECT * FROM table1 INNER JOIN table2...
set @.n=@.n-1
end
And open other window and put this code:
UPDATE table2 set column1='test'
If I execute each statement separately, than both works. The first statement
takes about minute, the update one less than second.
If I execute the update statement while select is working than I get blocked
tables for infinite time.
Any idea?
regards,S
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5cbtl1d40ntshsauftgqtjf2m4ucp3el1r@.
4ax.com...
> On Tue, 25 Oct 2005 15:18:31 +0200, simon wrote:
>
> Hi Simon,
> It appears to me that there are two things wrong:
> 1. You have somehow set your transactions to an isolation leven that is
> higher than the standard "READ COMMITTED" level. With read committed,
> locks for data being read are released when the statement finishes. With
> REPEATABLE READ and SERIALIZABLE, locks are held until the end of the
> transaction.
> 2. You are starting transactions that you don't finish. That might be
> because not each BEGIN TRANSACTION in your app is matched by either a
> COMMIT TRANSACTION or a ROLLBACK TRANSACTION, or because you don't use
> explicit transactions, but have the autocommit transaction mode switched
> off using SET IMPLICIT_TRANSACTIONS ON (that means that transactions are
> automatically started by SQL Server, but they still have to be ended by
> an explicit COMMIT or ROLLBACK statement).
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 26 Oct 2005 09:21:56 +0200, simon wrote:

>Hugo,
>I don't set any transaction.
>I just open sqlQueryAnalyzer and put this code:
>declare @.n int
>set @.n=50
>while @.n>0
>begin
> SELECT * FROM table1 INNER JOIN table2...
> set @.n=@.n-1
>end
>And open other window and put this code:
>UPDATE table2 set column1='test'
>
>If I execute each statement separately, than both works. The first statemen
t
>takes about minute, the update one less than second.
>If I execute the update statement while select is working than I get blocke
d
>tables for infinite time.
>Any idea?
Hi Simon,
Not now - I'll need more information.
Please post details about your tables: CREATE TABLE statements
(including all properties, constraints, indexes, etc) for the tables,
INSERT statements for the data in your tables. Also, post the complete
code that you are using, as the code above will only return a syntax
error.
Another thing to try: when both statements are running, open a third
window and execute
sp_lock
Post the results in a reply to this message.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Block to drop any object with ddl triger

Hi all,
I want block attempt to drop any objects(tables, sp, views...) of my data
base.
Have way to do this?
I did see same thing about DDL trigers, any one can sed-me one sample?
Thanks to allHi all,
Thanks,
I find one sample...
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;
Thanks again
"retf" <re.tf@.terra.com.br> escreveu na mensagem
news:%23cQ9yq6eGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I want block attempt to drop any objects(tables, sp, views...) of my data
> base.
> Have way to do this?
> I did see same thing about DDL trigers, any one can sed-me one sample?
> Thanks to all
>|||This should really be
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
RAISERROR('You must disable Trigger "safety" to drop or alter
tables!',16,1)
ROLLBACK ;
END
Without an error, a client may think the statement suceeded.
David
"retf" <re.tf@.terra.com.br> wrote in message
news:%23P2uRt6eGHA.3484@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> Thanks,
> I find one sample...
> CREATE TRIGGER safety
> ON DATABASE
> FOR DROP_TABLE, ALTER_TABLE
> AS
> PRINT 'You must disable Trigger "safety" to drop or alter tables!'
> ROLLBACK ;
>
> Thanks again
> "retf" <re.tf@.terra.com.br> escreveu na mensagem
> news:%23cQ9yq6eGHA.1272@.TK2MSFTNGP03.phx.gbl...
>|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> This should really be
>
> CREATE TRIGGER safety
> ON DATABASE
> FOR DROP_TABLE, ALTER_TABLE
> AS
> BEGIN
> RAISERROR('You must disable Trigger "safety" to drop or alter
> tables!',16,1)
> ROLLBACK ;
> END
> Without an error, a client may think the statement suceeded.
Quibble: there will be an error message without the RAISERROR. To
wit, in SQL 2005 a ROLLBACK in a trigger raises an error message.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thursday, February 16, 2012

blank pages

I have two tables in my report. The second table spans two pages, because of
this, in the pdf output, the first table has blank pages after each page.
Anyone could help me on this?
Thanks
AngeloI solved this myself by putting the second table into a list
"Mathi" wrote:
> I have two tables in my report. The second table spans two pages, because of
> this, in the pdf output, the first table has blank pages after each page.
> Anyone could help me on this?
> Thanks
> Angelo

Blank page for hidden table

Hello,
I have three tables all on separate pages, with a PageBreakAtEnd = true for
all of them to force the page breaks. However, if I hide the middle table, I
still get a blank page where it would be when I export to PDF. How can I not
get the blank page where the hidden table would normally be?
I need to have the page breaks at the end of all tables because sometimes
the middle table will not be hidden. The visibility of the middle table is an
expression toggled by a parameter value.
ThanksTry to play with the "list" or "Rectangle".here is my suggestion not
solution
On Apr 2, 9:08=A0pm, Lucky Horseshoe
<LuckyHorses...@.discussions.microsoft.com> wrote:
> Hello,
> I have three tables all on separate pages, with a PageBreakAtEnd =3D true =for
> all of them to force the page breaks. However, if I hide the middle table,= I
> still get a blank page where it would be when I export to PDF. How can I n=ot
> get the blank page where the hidden table would normally be?
> I need to have the page breaks at the end of all tables because sometimes
> the middle table will not be hidden. The visibility of the middle table is= an
> expression toggled by a parameter value.
> Thanks|||I tried putting the table to be hidden inside a list and then a rectangle but
I still get empty space on the page for it when I export to a PDF. Any other
ideas out there?
Thanks.
"RajDeep" wrote:
> Try to play with the "list" or "Rectangle".here is my suggestion not
> solution
> On Apr 2, 9:08 pm, Lucky Horseshoe
> <LuckyHorses...@.discussions.microsoft.com> wrote:
> > Hello,
> > I have three tables all on separate pages, with a PageBreakAtEnd = true for
> > all of them to force the page breaks. However, if I hide the middle table, I
> > still get a blank page where it would be when I export to PDF. How can I not
> > get the blank page where the hidden table would normally be?
> >
> > I need to have the page breaks at the end of all tables because sometimes
> > the middle table will not be hidden. The visibility of the middle table is an
> > expression toggled by a parameter value.
> >
> > Thanks
>|||I have same issue but the thing is i have blank page showing in the report it
self.
Seems like you do have only when you export but not in the report. Could you
please explain how to hide blank page in the Report manager?
Thank you.
"Lucky Horseshoe" wrote:
> I tried putting the table to be hidden inside a list and then a rectangle but
> I still get empty space on the page for it when I export to a PDF. Any other
> ideas out there?
> Thanks.
> "RajDeep" wrote:
> > Try to play with the "list" or "Rectangle".here is my suggestion not
> > solution
> >
> > On Apr 2, 9:08 pm, Lucky Horseshoe
> > <LuckyHorses...@.discussions.microsoft.com> wrote:
> > > Hello,
> > > I have three tables all on separate pages, with a PageBreakAtEnd = true for
> > > all of them to force the page breaks. However, if I hide the middle table, I
> > > still get a blank page where it would be when I export to PDF. How can I not
> > > get the blank page where the hidden table would normally be?
> > >
> > > I need to have the page breaks at the end of all tables because sometimes
> > > the middle table will not be hidden. The visibility of the middle table is an
> > > expression toggled by a parameter value.
> > >
> > > Thanks
> >
> >

Tuesday, February 14, 2012

Blank Database Name

Somehow a database got created that has no name...not even a space. If you
look in the diagrams, tables, views, and etc there is nothing there. How can
I get rid of this bad db? If I try to delete I get the following:
Error 21776: [SQL-DMO] The name '' was not found in the Databases
collection...
Thanks!"Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> Somehow a database got created that has no name...not even a space. If
you
> look in the diagrams, tables, views, and etc there is nothing there. How
can
> I get rid of this bad db? If I try to delete I get the following:
> Error 21776: [SQL-DMO] The name '' was not found in the Databases
> collection...
>
> Thanks!
Run the following query and see if you can find the database information in
the system tables.
If you can find it there, then you can probably update the sysdatabases
table and give the DB_ID in question a name that you can work with.
SELECT * FROM master.dbo.sysdatabases
Rick Sawtell
MCT, MCSD, MCDBA|||Rick...thanks for helping out. I was able to find the entry in the table you
specified. I updated the "Name" column, but the db still does not have a
name in the Database view. I would just delete the record, but I figured I
would check with you first to see what the possible consequences might be.
"Rick Sawtell" wrote:
> "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> > Somehow a database got created that has no name...not even a space. If
> you
> > look in the diagrams, tables, views, and etc there is nothing there. How
> can
> > I get rid of this bad db? If I try to delete I get the following:
> >
> > Error 21776: [SQL-DMO] The name '' was not found in the Databases
> > collection...
> >
> >
> > Thanks!
> Run the following query and see if you can find the database information in
> the system tables.
> If you can find it there, then you can probably update the sysdatabases
> table and give the DB_ID in question a name that you can work with.
> SELECT * FROM master.dbo.sysdatabases
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Rick...nevermind, your suggestion did work. I just didn't get it enough
time to refresh. I was able to delete the db using the menus.
Thanks again!!
"Rick Sawtell" wrote:
> "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> > Somehow a database got created that has no name...not even a space. If
> you
> > look in the diagrams, tables, views, and etc there is nothing there. How
> can
> > I get rid of this bad db? If I try to delete I get the following:
> >
> > Error 21776: [SQL-DMO] The name '' was not found in the Databases
> > collection...
> >
> >
> > Thanks!
> Run the following query and see if you can find the database information in
> the system tables.
> If you can find it there, then you can probably update the sysdatabases
> table and give the DB_ID in question a name that you can work with.
> SELECT * FROM master.dbo.sysdatabases
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||then try sp_rename '', 'New-Name', 'Database'
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Mat Powell" wrote:
> Rick...thanks for helping out. I was able to find the entry in the table you
> specified. I updated the "Name" column, but the db still does not have a
> name in the Database view. I would just delete the record, but I figured I
> would check with you first to see what the possible consequences might be.
> "Rick Sawtell" wrote:
> >
> > "Mat Powell" <MatPowell@.discussions.microsoft.com> wrote in message
> > news:52E877A4-B05C-4DF5-8A80-CFD7368B147C@.microsoft.com...
> > > Somehow a database got created that has no name...not even a space. If
> > you
> > > look in the diagrams, tables, views, and etc there is nothing there. How
> > can
> > > I get rid of this bad db? If I try to delete I get the following:
> > >
> > > Error 21776: [SQL-DMO] The name '' was not found in the Databases
> > > collection...
> > >
> > >
> > > Thanks!
> >
> > Run the following query and see if you can find the database information in
> > the system tables.
> >
> > If you can find it there, then you can probably update the sysdatabases
> > table and give the DB_ID in question a name that you can work with.
> >
> > SELECT * FROM master.dbo.sysdatabases
> >
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >

Blank Database Name

blank cells cause trouble on join

I have a few tables that I am joining together and one of the joins is on a
field that almost always has a code in it. Sometimes it is a blank (not a
null) if the clerk didn't type anything into it. The table that I am joining
to this one, has many cells in the join field thhat are blank and those rows
are used for commenting the other records in the table (I know it is stupid
but I didn't design it).
If I do a regular join whenever it comes upon one of these blanks it joins
to all of the comment records in the 2nd table.
What would be the easiest way to get around this problem?
Below is the statement as far as I got befor the last join messed me up.
cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
blank in all rows where they are being used as comments.
SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
codentry
from osmccsdb.cdocket
left outer join osmccsdb.cparty
on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccharge
on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccodes
on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
order by osmccsdb.cdocket.cdevtdt descLinda,
A LEFT JOIN will return all of the records from the table on left side of
the JOIN keyword and any matching records on the right. I don't have any
sample data to work with but you might look at the NULLIF function and see
if it might help you in your situation.
HTH
Jerry
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I see this with some tools and it is kind of a pain. First thing I would
note is that it is usually a bad idea to have user inputted values being
what you are joining on (but I know you said you didn't design it)
I would probaby just add a condition to your join that says something like:
AND ccodes.codevent <> '' AND the other column too
This will eliminate them from the join. Or if you want the '' rows from one
side, but not the other, then change the '' in the join:
case when ccodes.codevent = '' then 'NOT POSSIBLE' else ccodes.codevent end
Or you could use NULL instead of 'NOT POSSIBLE'
It might not be great for performance, so you might have to do some
trickiness if you have scads of data, but that is just the price you pay :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I would prefer updating those columns with some value,say string and
eliminate that in the where clause. and make it default so that problem wil
l
not come again
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||You are a freaking genius!!! I am not sure that I will be allowed to do tha
t
but if I can what a relief! These are court records and I am not sure how
weird they will be about me changing data.
"R.D" wrote:
> I would prefer updating those columns with some value,say string and
> eliminate that in the where clause. and make it default so that problem w
ill
> not come again
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Linda Ibarra" wrote:
>|||It was also pointed out to me that if I just created a view of this data
where the records with blank fields were not included and used that for the
join, that that would also eliminate this problem.
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||yup, you can always have a view. yet what if you want to use data in another
sproc or function or in another view. To solve this permanently have
amechanism where either you dont allow such values(?) or such blanks are
automatically replace with something like 'No Data'
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:
> It was also pointed out to me that if I just created a view of this data
> where the records with blank fields were not included and used that for th
e
> join, that that would also eliminate this problem.
> "Linda Ibarra" wrote:
>

Sunday, February 12, 2012

Bizarre Index on Table causing problems

Hello,
One of my sql tables has a bizarre index on it that looks like its name is
?8 (that's some weird small k, 8, and what appears to be a newline
character). The first character appears to be some sort of ascii code for
what I have no idea. The index appeared out of nowhere, and is a clustered,
hypothetical, auto create located on PRIMARY. It does not show in
Enterprise Manager's list of indexes. I believe it is the source of some
major problems on the table, and it cannot be dropped via DROP INDEX --
Cannot drop the index 'tblTable.?8', because it does not exist in the system
catalog.
Does anyone know of a way to remove this index, and where it came from? I
tried creating some sql to try and get around it
declare @.indexname nvarchar(2000)
select @.indexname = 'tblTable.' + name from sysindexes where id = 1110347070
and indid = 13
select @.indexname
declare @.sql nvarchar(3000)
set @.sql = 'DROP INDEX ' + @.indexname
exec (@.sql)
thinking that perhaps that might do the trick. No dice. If anyone can help
me out here I would really appreciate it. Thanks so much.I had something somewhat similar to this and I had to script a temp table out how I wanted, insert the data from the original to the temp, drop the original, rename the temp.
>--Original Message--
>Hello,
>One of my sql tables has a bizarre index on it that looks like its name is
>?8=04 (that's some weird small k, 8, and what appears to be a newline
>character). The first character appears to be some sort of ascii code for
>what I have no idea. The index appeared out of nowhere, and is a clustered,
>hypothetical, auto create located on PRIMARY. It does not show in
>Enterprise Manager's list of indexes. I believe it is the source of some
>major problems on the table, and it cannot be dropped via DROP INDEX --
>Cannot drop the index 'tblTable.?8', because it does not exist in the system
>catalog.
>Does anyone know of a way to remove this index, and where it came from? I
>tried creating some sql to try and get around it
>declare @.indexname nvarchar(2000)
>select @.indexname =3D 'tblTable.' + name from sysindexes where id =3D 1110347070
>and indid =3D 13
>select @.indexname
>declare @.sql nvarchar(3000)
>set @.sql =3D 'DROP INDEX ' + @.indexname
>exec (@.sql)
>thinking that perhaps that might do the trick. No dice. If anyone can help
>me out here I would really appreciate it. Thanks so much.
>
>
>.
>|||To reference names that don't conform to the rules for identifiers, try
enclosing the names in brackets (or double quotes):
DECLARE @.indexname nvarchar(261)
SELECT @.indexname = QUOTENAME(OBJECT_NAME(id)) +
'.' +
QUOTENAME(name)
FROM sysindexes
WHERE id = 1110347070 AND
indid = 13
--
Hope this helps.
Dan Guzman
SQL Server MVP
"tglenist" <tglenist@.hotmail.com> wrote in message
news:OT5jysNwDHA.2540@.TK2MSFTNGP10.phx.gbl...
> Hello,
> One of my sql tables has a bizarre index on it that looks like its name is
> ?8 (that's some weird small k, 8, and what appears to be a newline
> character). The first character appears to be some sort of ascii code for
> what I have no idea. The index appeared out of nowhere, and is a
clustered,
> hypothetical, auto create located on PRIMARY. It does not show in
> Enterprise Manager's list of indexes. I believe it is the source of some
> major problems on the table, and it cannot be dropped via DROP INDEX --
> Cannot drop the index 'tblTable.?8', because it does not exist in the
system
> catalog.
> Does anyone know of a way to remove this index, and where it came from? I
> tried creating some sql to try and get around it
> declare @.indexname nvarchar(2000)
> select @.indexname = 'tblTable.' + name from sysindexes where id =1110347070
> and indid = 13
> select @.indexname
> declare @.sql nvarchar(3000)
> set @.sql = 'DROP INDEX ' + @.indexname
> exec (@.sql)
> thinking that perhaps that might do the trick. No dice. If anyone can
help
> me out here I would really appreciate it. Thanks so much.
>
>|||"tglenist" <tglenist@.hotmail.com> wrote:
>One of my sql tables has a bizarre index on it that looks like its name is
>?8 (that's some weird small k, 8, and what appears to be a newline
>character). The first character appears to be some sort of ascii code for
>what I have no idea. The index appeared out of nowhere, and is a clustered,
>hypothetical, auto create located on PRIMARY. It does not show in
>Enterprise Manager's list of indexes. I believe it is the source of some
>major problems on the table, and it cannot be dropped via DROP INDEX --
>Cannot drop the index 'tblTable.?8', because it does not exist in the system
>catalog.
>Does anyone know of a way to remove this index, and where it came from? I
>tried creating some sql to try and get around it
>declare @.indexname nvarchar(2000)
>select @.indexname = 'tblTable.' + name from sysindexes where id = 1110347070
>and indid = 13
>select @.indexname
>declare @.sql nvarchar(3000)
>set @.sql = 'DROP INDEX ' + @.indexname
>exec (@.sql)
>thinking that perhaps that might do the trick. No dice. If anyone can help
>me out here I would really appreciate it. Thanks so much.
This sounds related to a recent thread entitled "Hypothetical indexes",
F8EE1587-3A79-4E3A-85B8-BE0B5B2E3A59@.microsoft.com
It refers to a KB article,
http://support.microsoft.com/support/kb/articles/Q293/1/77.ASP
HTH,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Words can only hurt if you try to read them. Don't play their game" - Zoolander

BIT-Wise Aggregation

Hi,

I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)

In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.

I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.

SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name

Thanks,
Loonysan

Here is code that shows a trick. The idea is to break each "rights mask" into a series of rows representing the separate bit values. These can be recombined with a Sum(distinct) across the roles owned by each account.

To make this work, you need a table that has one row for each bit position. My example shows a subset of bits built using a union. There is a system table called spt_values that holds a bunch of useful values used by system stored procedures. It contains rows for each bit position and would work with this application.

Drop Table #AccountRoles

Drop Table #Account

Drop Table #Role

Create Table #Account(

account_id int Not Null Identity( 1000, 100 ),

account_name varchar(100) Not Null

)

Create Table #Role(

role_id int Not Null Identity( 100, 1 ),

role_name varchar(100) Not Null,

rights int Not Null

)

Create Table #AccountRoles(

account_id int Not Null,

role_id int Not Null

)

Insert #Account values( 'Fred' )

Insert #Account values( 'Barney' )

Insert #Account values( 'Wilma' )

Insert #Account values( 'Betty' )

Select * from #Account

Insert #Role values ( 'Only 1', 1 )

Insert #Role values ( 'OneThree', 5 )

Insert #Role values ( 'JustTwo', 2 )

Insert #Role values ( 'All', 7 )

Select * from #Role

Insert #AccountRoles values ( 1100, 100 )

Insert #AccountRoles values ( 1100, 102 )

Insert #AccountRoles values ( 1200, 100 )

Insert #AccountRoles values ( 1200, 101 )

Insert #AccountRoles values ( 1300, 101 )

Insert #AccountRoles values ( 1300, 103 )

Select acc.account_id,

acc.account_name,

Sum(Distinct right_explode.single_right )

From #Account acc

Left Join #AccountRoles acr

On acr.account_id = acc.account_id

Join (

Select role_id,

bitposition,

rights & bitposition single_right

From #Roles

Cross Join

(

Select 1 as bitposition

Union

Select 2 as bitposition

Union

Select 4 as bitposition

Union

Select 8 as bitposition

Union

Select 16 as bitposition

) as bits

) right_explode

On right_explode.role_id = acr.role_id

Group

By acc.Account_Id,

acc.Account_Name

Order

By acc.Account_Name

Another approach would be to make a function that computed the bit-wise Or for a single account. This could be done using a local variable and a Select statement. This is not as good a solution.

Declare @.bitsum int

Select @.bitsum = bitsum | rights

From #AccountRoles ar

Join #Role r

On r.role_id = ar.role_id

Where ar.account_id = @.account

return @.bitsum|||

It works:

create table Account (Id int, AccountName nvarchar(25));

create table Role (id int, Rights int);

create table AccountRole (AccountID int, RoleID int);

insert into account values (1, 'DEMO');

insert into account values (2, 'TEST');

insert into role values (1, 125);

insert into role values (2, 225);

insert into AccountRole values (1, 1);

insert into AccountRole values (2, 1);

insert into AccountRole values (2, 2);

alter function f_or(@.acc_id int) returns int

begin

declare @.right int,

@.result int;

set @.result = 0

declare lcursor cursor for select rights from Role R, AccountRole AR where R.id = AR.roleid and AccountId = @.acc_id;

open lcursor;

fetch lcursor into @.right;

while @.@.FETCH_STATUS = 0 begin

set @.result = @.result | @.right

fetch lcursor into @.right

end

close lcursor;

return @.result;

end

select AccountName, dbo.f_or(id) 'Rights' from account

|||

Why do you think the second approach is not a good solution?

Thanks,
Loonysan

|||

If you only need to get the rights for a single Account, then the approach would be fine. However, in a set-wise report, my guess is that it would perform much worse as you are producing a new query for each Account instead of joining the tables in.

I only sketched what the function would look like. Can you complete the function or do you need more code? If you didn't simplify your tables or the problem, in a major way, for the sake of the post it should be easy enough to try both methods and weigh the advantages.

Thursday, March 22, 2012

Bookmark lookup cost factors (SQL Server 7.0)

Bookmark Lookup

Tuesday, March 20, 2012

BOL Package example Errors

Monday, March 19, 2012

blonde to write query or design fault

blocks hangup database when inserting in 1 specific table

Blocking while getting Snapshot

Sunday, March 11, 2012

Blocking processes

Blocking MS Access from linking tables...

Thursday, March 8, 2012

blocking caused from SQL with (nolock) hint

blocking caused from SQL with (nolock) hint

Wednesday, March 7, 2012

Blocked tables

Block to drop any object with ddl triger

Thursday, February 16, 2012

blank pages

Blank page for hidden table

Tuesday, February 14, 2012

Blank Database Name

Blank Database Name

Blank Database Name

blank cells cause trouble on join

Sunday, February 12, 2012

Bizarre Index on Table causing problems

BIT-Wise Aggregation

sql bit data type

Blog Archive

About Me