Hi Gang,
Not sure if this is the "right" sql server group to post this question in, but thought I'd give it the "ol'college try". If you feel that I may benefit by posting same question in a different group, please don't hesitate to let me know. Anyhow, below is my question:
Working with a client on developing an Application Service Provider system where users of the system will be affiliated with a parent company, the company will have the contract with my client and each company's data must be separated from all other companies. salesforce.com is a great example of such a structure, but has nothing to do with our application (meaning, we're not building a sales app).
What it comes down to is that there are 2 choices (unless someone knows of a others...)
1. Having the system create a separate database for each company or
2. Managing individual user access rights either through database roles or programatically.
We're looking at other DB providers to see what options we have, and for example, have found that Oracle 9i (which salesforce.com runs on) has a "Virtual Private Database Option". This feature enables developers to build 1 DB to support the ASP model and have Oracle logically (behind the scenes) manage access to the data. There are some disadvantages to doing this, but it is interesting.
I'm looking for information about / examples of SQL Server being used in an ASP environment and what methodology was used and why. Any assistance is greatly appreciated.
Thanks!I prefer separated databases... Each database can be modified, or
recovered, or backed up as the user sees fit... If they are all in a single
database and one user does something nasty and needs to rollback, it would
be a much more difficult task.. Security would be cleaner also. But you
would have lots of backup jobs to deal with...or maybe more complicated
jobs to backup everything.
However if there are going to be lots of customers, which would mean
hundreds or perhaps more databases, then the SQL tools do not work well...
It would take lots of time to open SQL Enterprise Manager for instance, so
you'd have to go command line probably...
The benefit of using a single database, is easier maintenance... But mixing
customer data might be a problem... would you have a separate set of tables
for each customer or simply add a companyid field to each table... IF you
have a companyID field added, you'd need to make sure performance wouldn't
suffer... If I have a small number of rows for my company in one of the
tables, where other companies have millions of rows, if I needed to do a
table scan, I'd have to look through everyone elses rows as well...
Tough choice,
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"appsprov" <anonymous@.discussions.microsoft.com> wrote in message
news:B973EF65-7175-4208-9657-3B8AAAADB2D5@.microsoft.com...
> Hi Gang,
> Not sure if this is the "right" sql server group to post this question in,
but thought I'd give it the "ol'college try". If you feel that I may
benefit by posting same question in a different group, please don't hesitate
to let me know. Anyhow, below is my question:
> Working with a client on developing an Application Service Provider system
where users of the system will be affiliated with a parent company, the
company will have the contract with my client and each company's data must
be separated from all other companies. salesforce.com is a great example of
such a structure, but has nothing to do with our application (meaning, we're
not building a sales app).
> What it comes down to is that there are 2 choices (unless someone knows of
a others...)
> 1. Having the system create a separate database for each company or
> 2. Managing individual user access rights either through database roles
or programatically.
> We're looking at other DB providers to see what options we have, and for
example, have found that Oracle 9i (which salesforce.com runs on) has a
"Virtual Private Database Option". This feature enables developers to build
1 DB to support the ASP model and have Oracle logically (behind the scenes)
manage access to the data. There are some disadvantages to doing this, but
it is interesting.
> I'm looking for information about / examples of SQL Server being used in
an ASP environment and what methodology was used and why. Any assistance is
greatly appreciated.
> Thanks!
Showing posts with label provider. Show all posts
Showing posts with label provider. Show all posts
Thursday, March 8, 2012
Monday, February 13, 2012
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
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
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
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...
Subscribe to:
Posts (Atom)