Architecture question grouping different kinds of stuff...

Hi all,

I'm hoping somebody here might be able to point me in the right
direction.

Basically, I'm looking for a "good" way to build tables such that
different kinds of stuff can grouped.

For example, let's say I have "rooms" and they can contain "people"
and/or "books" and/or "furniture" -- each of those objects (rooms,
people, books, furniture, etc) may have their own attributes -- but the
idea is that a room may contain 0 or more of each of the others.

I'll need to do lots of selects to retrieve the contents of specific
rooms.

Is there a standard / good way to go about this sort of thing?

Somehow giving people, books, furniture a room_ID (foreign key) seems a
bit clumbsy -- ie, do I have to run a select for each?

thanks kindly, -ScottFollowing up on my own question above, let's say I have these tables:

Rooms
.. . RoomID

People
.. . PersonID
.. . Name

Books
.. . BookID
.. . Title

Furniture
.. . FurnitureID
.. . FurnitureType

I could group stuff into rooms with something like:

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID

(where ContentType could be person, book, furniture, etc)

But it seems like if there were 20 different kinds of things, I'd have
to run a select for each.

I'm hoping this is a standard SQL architecture problem, but I lack the
vocabulary to know what to search for.

thanks, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> Following up on my own question above, let's say I have these tables:
> Rooms
> . . RoomID
> People
> . . PersonID
> . . Name
> Books
> . . BookID
> . . Title
> Furniture
> . . FurnitureID
> . . FurnitureType
> I could group stuff into rooms with something like:
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> (where ContentType could be person, book, furniture, etc)
> But it seems like if there were 20 different kinds of things, I'd have
> to run a select for each.

I would look into gathering all these contents into one supertable.
If then there are specicic attributes for person, books etc you can
can have sub tables for this.

This contents table would have a type field specifying the type of object.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland, thanks -- are you suggesting something like:

Rooms
.. . RoomID

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . PersonName
.. . BookTitle
.. . FurnitureType

or even

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . VariableText1
.. . VariableText2

(where 'VariableText' would store different kinds of stuff, depending
on the ContentType)

Since some of these objects may have a few attributes, such a
supertable could have lots of nulls -- is that "ok"?

Thanks again for your help, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> Hi Erland, thanks -- are you suggesting something like:
> Rooms
> . . RoomID
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . PersonName
> . . BookTitle
> . . FurnitureType
> or even
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . VariableText1
> . . VariableText2
> (where 'VariableText' would store different kinds of stuff, depending
> on the ContentType)
>
> Since some of these objects may have a few attributes, such a
> supertable could have lots of nulls -- is that "ok"?

Nah, rather I was thinking you would keep the existing tables, and
just add this RoomContents table. Probably you should move some columns
into this table, for instance a string that represents a name of some
sort. (That is "sofa" for furnitures.)

This would make it easier to write a simple query that lists all the
contents in the room. If you need furniture-specific information you would
go to that table.

In the end it may be a trade-off where you place things. If some attributes
are common to most contents, it may be better to have it as a nullable
column in the main table.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The trouble is that each time I "load a room" I'll need at least a few
of the attributes of the things in that room.

So, if I understand correctly, I would keep all of those various
objects' attributes in one "supertable," and perhaps keep some
additional attributes (those not needed when the room loads) in the
extra object-specific tables.

Does that sound about right?

Thanks again, -Scott|||turnstyle (scott@.turnstyle.com) writes:
> The trouble is that each time I "load a room" I'll need at least a few
> of the attributes of the things in that room.
> So, if I understand correctly, I would keep all of those various
> objects' attributes in one "supertable," and perhaps keep some
> additional attributes (those not needed when the room loads) in the
> extra object-specific tables.
> Does that sound about right?

Yes, that was my thought.

In an object-oriented language, this is a little easier since you would
have "Contents" a base class (possibly virtual) and then have Furtinure
etc to inherit from that group. In SQL you cannot do this very well.
You could have a view that unites all tables, and then have an INSTEAD
OF trigger on the view, so when you inserts into the view, things fall
down in their respective places, although I am not sure that this would
be worth the effort.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Applying SP4 on an active/passive 2 way cluster

I have a 2 way cluster and I want to apply SP4 on it.
Basically, I have 2 servers. Server A runs a default instance and
another instance. While the default services is disabled and the other
instance is running, Server B runs the default instance and has the other
instance disabled. This basically acts like a 2 way cluster
(active/passive).
How can I apply SQL Server 2000 SP4 on the 2 servers and have all the
nodes come up the correct way? What are the steps to achieve this?http://download.microsoft.com/download/1/B/D/1BDF5B78-584E-4DE0-B36F-
C44E06B0D2A3/ReadmeSql2k32sp4.htm#_installing_on_a_failover_cluster
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||This is not going to help me. I have explicitly mentioned that I have a 2
way cluster in my original email. So a standard article like you have given
me is NOT going to help.
"Ole Kristian Bangås" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns981DB671F914Folekristianbangaas@.207.46.248.16...
> http://download.microsoft.com/download/1/B/D/1BDF5B78-584E-4DE0-B36F-
> C44E06B0D2A3/ReadmeSql2k32sp4.htm#_installing_on_a_failover_cluster
> --
> Ole Kristian Bangås
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||"Shiva" <arbitsquare@.hotmail.com> wrote in
news:OTKiIkivGHA.2120@.TK2MSFTNGP03.phx.gbl:
> This is not going to help me. I have explicitly mentioned that I have
> a 2 way cluster in my original email. So a standard article like you
> have given me is NOT going to help.
Yes, and I don't quite get the problem. Let me quote point two in the
article I gave a link to:
"Run the service pack from the node that owns the group containing the
virtual server that you plan to upgrade. This installs the service pack
files on all nodes in the failover cluster."
Can you please explain what your problem is? As I understand your scenario
you have a two-node two-instance active-passive SQL cluster. An upgrade of
that cluster should be rather straightforward.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||"Shiva" <arbitsquare@.hotmail.com> wrote in
news:OTKiIkivGHA.2120@.TK2MSFTNGP03.phx.gbl:
Correction to my previous post, You actually have an active/active
confuguration, with one instance on each server, but the upgrade procedure
is still valid.
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

Applying SP4 on an active/passive 2 way cluster

I have a 2 way cluster and I want to apply SP4 on it.

Basically, I have 2 servers. Server A runs a default instance and another instance. While the default services is disabled and the other instance is running, Server B runs the default instance and has the other instance disabled. This basically acts like a 2 way cluster (active/passive).

How can I apply SQL Server 2000 SP4 on the 2 servers and have all the nodes come up the correct way? What are the steps to achieve this?Since SQL Server is a shared resource on a clustered server, installing SP4 on one server is automatically done on the other. Everything is transparent to the user/admin.sql

Applying SP4 on an active/passive 2 way cluster

I have a 2 way cluster and I want to apply SP4 on it.
Basically, I have 2 servers. Server A runs a default instance and
another instance. While the default services is disabled and the other
instance is running, Server B runs the default instance and has the other
instance disabled. This basically acts like a 2 way cluster
(active/passive).
How can I apply SQL Server 2000 SP4 on the 2 servers and have all the
nodes come up the correct way? What are the steps to achieve this?http://download.microsoft.com/downl...584E-4DE0-B36F-
C44E06B0D2A3/ReadmeSql2k32sp4.htm#_installing_on_a_failover_cluster
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||This is not going to help me. I have explicitly mentioned that I have a 2
way cluster in my original email. So a standard article like you have given
me is NOT going to help.
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns981DB671F914Folekristianbangaas@.
207.46.248.16...
> http://download.microsoft.com/downl...584E-4DE0-B36F-
> C44E06B0D2A3/ReadmeSql2k32sp4.htm#_installing_on_a_failover_cluster
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||"Shiva" <arbitsquare@.hotmail.com> wrote in
news:OTKiIkivGHA.2120@.TK2MSFTNGP03.phx.gbl:

> This is not going to help me. I have explicitly mentioned that I have
> a 2 way cluster in my original email. So a standard article like you
> have given me is NOT going to help.
Yes, and I don't quite get the problem. Let me quote point two in the
article I gave a link to:
"Run the service pack from the node that owns the group containing the
virtual server that you plan to upgrade. This installs the service pack
files on all nodes in the failover cluster."
Can you please explain what your problem is? As I understand your scenario
you have a two-node two-instance active-passive SQL cluster. An upgrade of
that cluster should be rather straightforward.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP|||"Shiva" <arbitsquare@.hotmail.com> wrote in
news:OTKiIkivGHA.2120@.TK2MSFTNGP03.phx.gbl:
Correction to my previous post, You actually have an active/active
confuguration, with one instance on each server, but the upgrade procedure
is still valid.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

Applying SP3

Hi

I am basically a sybase guy but unfortunately I need to patch a SQL server with SP3. This is the first time I will be doing that. So I am not sure that after applying SP3, if I would need to rebuild all databases again.

Could anybody tell me what and where is the impact when we install SP3..??

I would appreciate any kinda help.

Rgds

WilsonMake a backup of any production database (including master and msdb) before you apply sp3 (just to be paranoid). Apply the service pack, reboot NT, and go merrily on your way. No rebuilding or other "care and feeding" needed unless something goes dreadfully wrong during the application of the service pack.

-PatP|||just to be paranoid

I thought that was part of the job...

Oh, and someone once told me...in response to

"Do you think everyones out to get you? Are you Paranoid?"

"You're only Paranoid if you're wrong"

So what's the downside?

Application roles

Can anybody tell, how to implement the application roles in SQL Server
2000.

Basically, I want to Implement the application roles in our
application, so that it can be application specific. Its' an clients
requirement from we people.

Thanks

Prashant Thakwanithakwani@.rediffmail.com (Prashant Thakwani) wrote in message news:<bf0d42bf.0403032120.588fb947@.posting.google.com>...
> Can anybody tell, how to implement the application roles in SQL Server
> 2000.
> Basically, I want to Implement the application roles in our
> application, so that it can be application specific. Its' an clients
> requirement from we people.
> Thanks
> Prashant Thakwani

1. Create the role with sp_addapprole
2. Grant permissions to the role with GRANT
3. Use sp_setapprole to activate the role - you now have the role's
permissions, not your own permissions
4. Code your application to use sp_setapprole

There are examples for these commands in Books Online - are you having
a specific problem implementing them? If so, perhaps you could give
more information about what commands you're using, what errors or
unexpected behaviour you see etc.

Simon

Append data to Sql Server 2000 table from DB2 table (linked server)

Hello,
I am trying to append new records from a DB2 database to a Sql Server
2000 database. Basically table A (Sql Server) has been uploaded with a
dump from table B (DB2) and from know on I would like to append any new
records added to table B to table A. I am running a DTS package to do
this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
primary keys.
Any ideas.
Thanks for your helpThe usual approach is to load the data into a staging table, which
matches the target table in layout but is truncated before the load.
Then you can INSERT to the target table from the staging table where
NOT EXISTS the key. You also have the option of doing an UPDATE to
the target table from the staging table, which would be done before
the INSERT.
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 10:57:21 -0800, mrdata1701@.gmail.com wrote:
>Hello,
>I am trying to append new records from a DB2 database to a Sql Server
>2000 database. Basically table A (Sql Server) has been uploaded with a
>dump from table B (DB2) and from know on I would like to append any new
>records added to table B to table A. I am running a DTS package to do
>this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
>NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
>primary keys.
>Any ideas.
>Thanks for your help|||Thanks Roy! The thing is the table has over 380K records so it would
take a while to update and this is something that will be run daily so
it could end up using more resources that I would like.
On Jan 15, 2:16 pm, Roy Harvey <roy_har...@.snet.net> wrote:
> The usual approach is to load the data into a staging table, which
> matches the target table in layout but is truncated before the load.
> Then you can INSERT to the target table from the staging table where
> NOT EXISTS the key. You also have the option of doing an UPDATE to
> the target table from the staging table, which would be done before
> the INSERT.
> Roy Harvey
> Beacon Falls, CT
> On 15 Jan 2007 10:57:21 -0800, mrdata1...@.gmail.com wrote:
>
> >Hello,
> >I am trying to append new records from a DB2 database to a Sql Server
> >2000 database. Basically table A (Sql Server) has been uploaded with a
> >dump from table B (DB2) and from know on I would like to append any new
> >records added to table B to table A. I am running a DTS package to do
> >this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
> >NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
> >primary keys.
> >Any ideas.
> >Thanks for your help- Hide quoted text -- Show quoted text -|||On 15 Jan 2007 11:26:59 -0800, mrdata1701@.gmail.com wrote:
>Thanks Roy! The thing is the table has over 380K records so it would
>take a while to update and this is something that will be run daily so
>it could end up using more resources that I would like.
The ideal would be to have the DB2 data marked in some indicating
which rows had been updated. Lacking that your alternatives are to
use a staging table as already described, or process today's DB2
extract against yesterday's DB2 extract in an old fashioned match-file
program written in whatever language you prefer. That assumes that
the files are already sorted on the key, of course. Personally I
would try the staging table approach first.
Roy Harvey
Beacon Falls, CT

Append data to Sql Server 2000 table from DB2 table (linked server)

Hello,
I am trying to append new records from a DB2 database to a Sql Server
2000 database. Basically table A (Sql Server) has been uploaded with a
dump from table B (DB2) and from know on I would like to append any new
records added to table B to table A. I am running a DTS package to do
this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
primary keys.
Any ideas.
Thanks for your help
The usual approach is to load the data into a staging table, which
matches the target table in layout but is truncated before the load.
Then you can INSERT to the target table from the staging table where
NOT EXISTS the key. You also have the option of doing an UPDATE to
the target table from the staging table, which would be done before
the INSERT.
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 10:57:21 -0800, mrdata1701@.gmail.com wrote:

>Hello,
>I am trying to append new records from a DB2 database to a Sql Server
>2000 database. Basically table A (Sql Server) has been uploaded with a
>dump from table B (DB2) and from know on I would like to append any new
>records added to table B to table A. I am running a DTS package to do
>this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
>NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
>primary keys.
>Any ideas.
>Thanks for your help
|||Thanks Roy! The thing is the table has over 380K records so it would
take a while to update and this is something that will be run daily so
it could end up using more resources that I would like.
On Jan 15, 2:16 pm, Roy Harvey <roy_har...@.snet.net> wrote:[vbcol=seagreen]
> The usual approach is to load the data into a staging table, which
> matches the target table in layout but is truncated before the load.
> Then you can INSERT to the target table from the staging table where
> NOT EXISTS the key. You also have the option of doing an UPDATE to
> the target table from the staging table, which would be done before
> the INSERT.
> Roy Harvey
> Beacon Falls, CT
> On 15 Jan 2007 10:57:21 -0800, mrdata1...@.gmail.com wrote:
>
>
>
|||On 15 Jan 2007 11:26:59 -0800, mrdata1701@.gmail.com wrote:

>Thanks Roy! The thing is the table has over 380K records so it would
>take a while to update and this is something that will be run daily so
>it could end up using more resources that I would like.
The ideal would be to have the DB2 data marked in some indicating
which rows had been updated. Lacking that your alternatives are to
use a staging table as already described, or process today's DB2
extract against yesterday's DB2 extract in an old fashioned match-file
program written in whatever language you prefer. That assumes that
the files are already sorted on the key, of course. Personally I
would try the staging table approach first.
Roy Harvey
Beacon Falls, CT

Append data to Sql Server 2000 table from DB2 table (linked server)

Hello,
I am trying to append new records from a DB2 database to a Sql Server
2000 database. Basically table A (Sql Server) has been uploaded with a
dump from table B (DB2) and from know on I would like to append any new
records added to table B to table A. I am running a DTS package to do
this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
primary keys.
Any ideas.
Thanks for your helpThe usual approach is to load the data into a staging table, which
matches the target table in layout but is truncated before the load.
Then you can INSERT to the target table from the staging table where
NOT EXISTS the key. You also have the option of doing an UPDATE to
the target table from the staging table, which would be done before
the INSERT.
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 10:57:21 -0800, mrdata1701@.gmail.com wrote:

>Hello,
>I am trying to append new records from a DB2 database to a Sql Server
>2000 database. Basically table A (Sql Server) has been uploaded with a
>dump from table B (DB2) and from know on I would like to append any new
>records added to table B to table A. I am running a DTS package to do
>this every night. The columns are CV_NBR, NOTE_OWNER, NOTE_DATE,
>NOTE_SEQ_NBR, NOTE_TEXT where CV_NBR, NOTE_DATE, NOTE_SEQ_NBR would be
>primary keys.
>Any ideas.
>Thanks for your help|||Thanks Roy! The thing is the table has over 380K records so it would
take a while to update and this is something that will be run daily so
it could end up using more resources that I would like.
On Jan 15, 2:16 pm, Roy Harvey <roy_har...@.snet.net> wrote:[vbcol=seagreen]
> The usual approach is to load the data into a staging table, which
> matches the target table in layout but is truncated before the load.
> Then you can INSERT to the target table from the staging table where
> NOT EXISTS the key. You also have the option of doing an UPDATE to
> the target table from the staging table, which would be done before
> the INSERT.
> Roy Harvey
> Beacon Falls, CT
> On 15 Jan 2007 10:57:21 -0800, mrdata1...@.gmail.com wrote:
>
>
>
>
>|||On 15 Jan 2007 11:26:59 -0800, mrdata1701@.gmail.com wrote:

>Thanks Roy! The thing is the table has over 380K records so it would
>take a while to update and this is something that will be run daily so
>it could end up using more resources that I would like.
The ideal would be to have the DB2 data marked in some indicating
which rows had been updated. Lacking that your alternatives are to
use a staging table as already described, or process today's DB2
extract against yesterday's DB2 extract in an old fashioned match-file
program written in whatever language you prefer. That assumes that
the files are already sorted on the key, of course. Personally I
would try the staging table approach first.
Roy Harvey
Beacon Falls, CT

Apostrophe problem

I'm stumped by what seems to be an apostrophe problem. Basically, I have a query that does this:

...

WHERE provider = @.Provider

...

When @.Provider has an apostrophe, no results are returned, even though an independent query of the database shows that there should be records returned. When I repace the apostrophy with double apostrophes, that doesn't improve things, either.

Just as a by-the-way, I would think that double apostrophe's are not needed in this situation, since I'm not building any strings with @.Provider. Regarless of that, though, it doesn't work either way. What am I missing?

Thanks!

As this example demonstrates, double apostrophies are required if the sought value contains a single apostrophy.

Code Snippet

DECLARE @.MyTable table
( Provider varchar(20) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 'Smith' )
INSERT INTO @.MyTable VALUES ( 'O''Donald' )
INSERT INTO @.MyTable VALUES ( 'Bill''s Wife' )

DECLARE @.Provider varchar(20)
SET @.Provider = 'O''Donald'

SELECT Provider
FROM @.MyTable
WHERE Provider = @.Provider

SET @.Provider = 'Bill''s Wife'

SELECT Provider
FROM @.MyTable
WHERE Provider = @.Provider

|||Not sure why the above keeps getting marked as the answer or by whom. It does not answer my question. Sorry.|||

Perhaps one of the moderators felt that it provided a clear and unambiguous response to the question as asked.

If a field contains an apostrophe, then the way to indicate that the criteria value also contains an apostrophe is to use double apostrophes. The examples I provided clearly demonstrate that is the case. Putting apostrophes into text boxes to terminate the string is one of the oldest forms of SQL Injection hacking.

If your @.Provider is set to a string containing an apostrophe, then every character past the apostrophe is ignored.

@.Provider = 'O'Connor becomes @.Provider = 'O'. Depending upon how the code is written, the error may be ignored.

If you feel that your question has not been answered, perhaps you should 'revise' the question and resubmit it.|||

Double apostrophe is needed... otherwise the server would never know you're referring to the special character.... and not the end of a string.

|||

...or perhaps people who read it should read it to the end and pay attention rather than jump to conclusions and effectively shut down the thread. After all, the original question states clearly that "When I repace the apostrophy with double apostrophes, that doesn't improve things, either." Clearly, I'm aware of the double-apostrophe solution! Anyway, no hard feelings. I've found a workaround at this point.

|||

When I repace the apostrophy with double apostrophes, that doesn't improve things, either."

Since you never gave any indication about what that statement meant, it could only be ignored.

Perhaps what is, is that the original question was not properly stated and/or did not have enough information to provide the solution you were seeking. I suggest that instead of quibbling with the responses, it would be more productive to provide more information about the problem -we can't read your mind. Revision and restatement is often more productive that accusations and retorts.

Not sure why the above keeps getting marked as the answer or by whom. It does not answer my question.

Embedded single quotes must be 'escaped' (doubled) when used as part of a query string criteria. As far as I am aware, under 'normal' circumstances, there is no 'work-around' for embedded single quotes.

However, there is the non-standard use of QUOTED_IDENTIFIER. By setting it off, you would use double quotes for the string delimiters and have embedded single quotes. That 'could' cause other unexpected issues with the database and/or applications. From Books Online:

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

|||

Hi Jararaca

Are you certain that your @.Provider variable contains a classic apostrophe (i.e. CHAR(39)) rather than CHAR(96), which looks similar but isn't treated as being an apostrophe by SQL Server?

If this turns out to be the problem you could (preferably) modify the code that sets the value of @.Provider, or, if that is beyond your control, use:

WHERE Provider = REPLACE(@.Provider, CHAR(96), CHAR(39))

Failing this, and assuming that your query is embeded in a stored procedure, I would set up a SQL Profiler trace in order to capture the values of the parameters passed in to the stored procedure - this way you should be able to immediately spot any problems with the value of @.Provider.

Chris

|||This is it! Thank you Chris. I went back and checked the data and this is exactly what's going on. Much appreciated!|||Arnie, if you change the correct answer in this thread again, I'll report you to the forum adminstration.|||

I don't have time to go back and 'change the correct answer'.

I would rather spend my time helping others rather than quibbling over the 'worth' of a suggestion. I put them out, some work, some don't. That's not my call. As was indicated earlier, there are a quite a few moderators, and perhaps someone considered the suggestion a good response to the question as asked.

But public accusations, especially from someone that hides behind an anonymous nom de plume -now that's getting downright 'unfriendly' and not worthy of further discourse ...

p.s., I'm glad that your issue was resolved.

|||

Ooo. Below the belt, dude... in so many ways. I challenge you to a duel, sir! (face slap with white gloves!)

Maybe we should just lighten up. I appreciate your gladness over the resolution of my issue. And thank you in return for your attempt to help.

|||Color me lighter...

Apostrophe problem

I'm stumped by what seems to be an apostrophe problem. Basically, I have a query that does this:

...

WHERE provider = @.Provider

...

When @.Provider has an apostrophe, no results are returned, even though an independent query of the database shows that there should be records returned. When I repace the apostrophy with double apostrophes, that doesn't improve things, either.

Just as a by-the-way, I would think that double apostrophe's are not needed in this situation, since I'm not building any strings with @.Provider. Regarless of that, though, it doesn't work either way. What am I missing?

Thanks!

As this example demonstrates, double apostrophies are required if the sought value contains a single apostrophy.

Code Snippet

DECLARE @.MyTable table
( Provider varchar(20) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 'Smith' )
INSERT INTO @.MyTable VALUES ( 'O''Donald' )
INSERT INTO @.MyTable VALUES ( 'Bill''s Wife' )

DECLARE @.Provider varchar(20)
SET @.Provider = 'O''Donald'

SELECT Provider
FROM @.MyTable
WHERE Provider = @.Provider

SET @.Provider = 'Bill''s Wife'

SELECT Provider
FROM @.MyTable
WHERE Provider = @.Provider

|||Not sure why the above keeps getting marked as the answer or by whom. It does not answer my question. Sorry.|||

Perhaps one of the moderators felt that it provided a clear and unambiguous response to the question as asked.

If a field contains an apostrophe, then the way to indicate that the criteria value also contains an apostrophe is to use double apostrophes. The examples I provided clearly demonstrate that is the case. Putting apostrophes into text boxes to terminate the string is one of the oldest forms of SQL Injection hacking.

If your @.Provider is set to a string containing an apostrophe, then every character past the apostrophe is ignored.

@.Provider = 'O'Connor becomes @.Provider = 'O'. Depending upon how the code is written, the error may be ignored.

If you feel that your question has not been answered, perhaps you should 'revise' the question and resubmit it.|||

Double apostrophe is needed... otherwise the server would never know you're referring to the special character.... and not the end of a string.

|||

...or perhaps people who read it should read it to the end and pay attention rather than jump to conclusions and effectively shut down the thread. After all, the original question states clearly that "When I repace the apostrophy with double apostrophes, that doesn't improve things, either." Clearly, I'm aware of the double-apostrophe solution! Anyway, no hard feelings. I've found a workaround at this point.

|||

When I repace the apostrophy with double apostrophes, that doesn't improve things, either."

Since you never gave any indication about what that statement meant, it could only be ignored.

Perhaps what is, is that the original question was not properly stated and/or did not have enough information to provide the solution you were seeking. I suggest that instead of quibbling with the responses, it would be more productive to provide more information about the problem -we can't read your mind. Revision and restatement is often more productive that accusations and retorts.

Not sure why the above keeps getting marked as the answer or by whom. It does not answer my question.

Embedded single quotes must be 'escaped' (doubled) when used as part of a query string criteria. As far as I am aware, under 'normal' circumstances, there is no 'work-around' for embedded single quotes.

However, there is the non-standard use of QUOTED_IDENTIFIER. By setting it off, you would use double quotes for the string delimiters and have embedded single quotes. That 'could' cause other unexpected issues with the database and/or applications. From Books Online:

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

|||

Hi Jararaca

Are you certain that your @.Provider variable contains a classic apostrophe (i.e. CHAR(39)) rather than CHAR(96), which looks similar but isn't treated as being an apostrophe by SQL Server?

If this turns out to be the problem you could (preferably) modify the code that sets the value of @.Provider, or, if that is beyond your control, use:

WHERE Provider = REPLACE(@.Provider, CHAR(96), CHAR(39))

Failing this, and assuming that your query is embeded in a stored procedure, I would set up a SQL Profiler trace in order to capture the values of the parameters passed in to the stored procedure - this way you should be able to immediately spot any problems with the value of @.Provider.

Chris

|||This is it! Thank you Chris. I went back and checked the data and this is exactly what's going on. Much appreciated!|||Arnie, if you change the correct answer in this thread again, I'll report you to the forum adminstration.|||

I don't have time to go back and 'change the correct answer'.

I would rather spend my time helping others rather than quibbling over the 'worth' of a suggestion. I put them out, some work, some don't. That's not my call. As was indicated earlier, there are a quite a few moderators, and perhaps someone considered the suggestion a good response to the question as asked.

But public accusations, especially from someone that hides behind an anonymous nom de plume -now that's getting downright 'unfriendly' and not worthy of further discourse ...

p.s., I'm glad that your issue was resolved.

|||

Ooo. Below the belt, dude... in so many ways. I challenge you to a duel, sir! (face slap with white gloves!)

Maybe we should just lighten up. I appreciate your gladness over the resolution of my issue. And thank you in return for your attempt to help.

|||Color me lighter...

Apostrophe problem

I'm stumped by what seems to be an apostrophe problem. Basically, I have a query that does this:

...

WHERE provider = @.Provider

...

When @.Provider has an apostrophe, no results are returned, even though an independent query of the database shows that there should be records returned. When I repace the apostrophy with double apostrophes, that doesn't improve things, either.

Just as a by-the-way, I would think that double apostrophe's are not needed in this situation, since I'm not building any strings with @.Provider. Regarless of that, though, it doesn't work either way. What am I missing?

Thanks!

As this example demonstrates, double apostrophies are required if the sought value contains a single apostrophy.

Code Snippet

DECLARE @.MyTable table
( Provider varchar(20) )

SET NOCOUNT ON

INSERT INTO @.MyTable VALUES ( 'Smith' )
INSERT INTO @.MyTable VALUES ( 'O''Donald' )
INSERT INTO @.MyTable VALUES ( 'Bill''s Wife' )

DECLARE @.Provider varchar(20)
SET @.Provider = 'O''Donald'

SELECT Provider
FROM @.MyTable
WHERE Provider = @.Provider

SET @.Provider = 'Bill''s Wife'

SELECT Provider
FROM @.MyTable
WHERE Provider = @.Provider

|||Not sure why the above keeps getting marked as the answer or by whom. It does not answer my question. Sorry.|||

Perhaps one of the moderators felt that it provided a clear and unambiguous response to the question as asked.

If a field contains an apostrophe, then the way to indicate that the criteria value also contains an apostrophe is to use double apostrophes. The examples I provided clearly demonstrate that is the case. Putting apostrophes into text boxes to terminate the string is one of the oldest forms of SQL Injection hacking.

If your @.Provider is set to a string containing an apostrophe, then every character past the apostrophe is ignored.

@.Provider = 'O'Connor becomes @.Provider = 'O'. Depending upon how the code is written, the error may be ignored.

If you feel that your question has not been answered, perhaps you should 'revise' the question and resubmit it.|||

Double apostrophe is needed... otherwise the server would never know you're referring to the special character.... and not the end of a string.

|||

...or perhaps people who read it should read it to the end and pay attention rather than jump to conclusions and effectively shut down the thread. After all, the original question states clearly that "When I repace the apostrophy with double apostrophes, that doesn't improve things, either." Clearly, I'm aware of the double-apostrophe solution! Anyway, no hard feelings. I've found a workaround at this point.

|||

When I repace the apostrophy with double apostrophes, that doesn't improve things, either."

Since you never gave any indication about what that statement meant, it could only be ignored.

Perhaps what is, is that the original question was not properly stated and/or did not have enough information to provide the solution you were seeking. I suggest that instead of quibbling with the responses, it would be more productive to provide more information about the problem -we can't read your mind. Revision and restatement is often more productive that accusations and retorts.

Not sure why the above keeps getting marked as the answer or by whom. It does not answer my question.

Embedded single quotes must be 'escaped' (doubled) when used as part of a query string criteria. As far as I am aware, under 'normal' circumstances, there is no 'work-around' for embedded single quotes.

However, there is the non-standard use of QUOTED_IDENTIFIER. By setting it off, you would use double quotes for the string delimiters and have embedded single quotes. That 'could' cause other unexpected issues with the database and/or applications. From Books Online:

When SET QUOTED_IDENTIFIER is OFF, literal strings in expressions can be delimited by single or double quotation marks. If a literal string is delimited by double quotation marks, the string can contain embedded single quotation marks, such as apostrophes.

SET QUOTED_IDENTIFIER must be ON when you are creating or changing indexes on computed columns or indexed views. If SET QUOTED_IDENTIFIER is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.

|||

Hi Jararaca

Are you certain that your @.Provider variable contains a classic apostrophe (i.e. CHAR(39)) rather than CHAR(96), which looks similar but isn't treated as being an apostrophe by SQL Server?

If this turns out to be the problem you could (preferably) modify the code that sets the value of @.Provider, or, if that is beyond your control, use:

WHERE Provider = REPLACE(@.Provider, CHAR(96), CHAR(39))

Failing this, and assuming that your query is embeded in a stored procedure, I would set up a SQL Profiler trace in order to capture the values of the parameters passed in to the stored procedure - this way you should be able to immediately spot any problems with the value of @.Provider.

Chris

|||This is it! Thank you Chris. I went back and checked the data and this is exactly what's going on. Much appreciated!|||Arnie, if you change the correct answer in this thread again, I'll report you to the forum adminstration.|||

I don't have time to go back and 'change the correct answer'.

I would rather spend my time helping others rather than quibbling over the 'worth' of a suggestion. I put them out, some work, some don't. That's not my call. As was indicated earlier, there are a quite a few moderators, and perhaps someone considered the suggestion a good response to the question as asked.

But public accusations, especially from someone that hides behind an anonymous nom de plume -now that's getting downright 'unfriendly' and not worthy of further discourse ...

p.s., I'm glad that your issue was resolved.

|||

Ooo. Below the belt, dude... in so many ways. I challenge you to a duel, sir! (face slap with white gloves!)

Maybe we should just lighten up. I appreciate your gladness over the resolution of my issue. And thank you in return for your attempt to help.

|||Color me lighter...