May 9, 2008 at 5:29 am
Hi,
I'm really having a hard time with the problem that I'm encountering, so this is my very first time to ask something in any forum.
I guess this is the best sql forum that I've been to. Anyway, here is my dilemma.
Inside my SP:
IF EXISTS (select topiccode from topic_status where topiccode = @topiccode and status = @status)
begin
delete from topic_status ...;
insert into topic_status ...;
update topic_status ...;
end
Above is actually a simple query if you notice, but now I was told that we need to add the schemaname and incorporate it in every command. The schemaname will be of course an input parameter.
with schema:
IF EXISTS (select topiccode from [companyA].[topic_status] where topiccode = @topiccode and status = @status)
@schemaname varchar(25)
So basically, to add the @schemaname, I need to put the command in string before executing the said command, easy? well, not really for me:
declare @sql varchar(max) -- I need this to run the if exists command
select @sql = 'IF EXISTS (select topiccode from [' + @schemaname + '].[topic_status] where topiccode = ''' + @topiccode + ''' and status = ''' + @status + ''') print 1 else print 0'
exec(@sql)
Of course this will run, but how can I get the value of 1 if it exists and 0 if it doesn't.
Using "print' will just write the value, I've tried using "return" but it doesn't work and even instead of using
the "print/return" I tried "set @returnval = 1 else set @returnval = 0"; which returns again an error.
I also tried this:
set @returnval = exec(@sql) -- of course it wont work 🙁
Is there any workaround this problem? Thank for your time and effort. Please help.
Thanks,
Richard C. Delavin
May 9, 2008 at 5:54 am
Maybe a silly question, but why on earth does schema need to be an input parameter?
This is exactly the same as saying the table name must be an input parameter..
..or the columnname.. etc..
The schemaname is part of the object's qualified name, and shouldn't be needed to be handled as a parameter. If you could get rid of this requirement, then the whole thing would become much easier for you. 🙂
/Kenneth
May 9, 2008 at 6:39 am
Because the database will be handling different companies, with their own schema. They actually wants a multi-tenant DB but the problem is that they don't want specific "logins".
Thats why I'm having this dilemma.
May 9, 2008 at 7:44 am
Well, there may be more problems down this path than perhaps is apparent.
What you're looking at is extensive use of dynamic SQL. This is a real Pandoras Box.
In order to get some insight on what's ahead, please do read this article.
http://sommarskog.se/dynamic_sql.html
/Kenneth
May 9, 2008 at 9:53 am
although you may no like them, seperate logins are a simple solution to this problem. give them all the same passwod and let your app connect as the company's user and implicitly access their schema-scoped objects. objects that are common for all companies would remain part of the dbo schema.
May 9, 2008 at 2:25 pm
The only way to do exactly what you're asking is dynamic SQL. That opens up all kinds of serious security and other issues. Might want to reconsider the whole separate logins thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 9, 2008 at 2:42 pm
"Because the database will be handling different companies, with their own schema."
That is the wrong solution to the problem. Have one database for each company and each login has access to only one database.
Be sure to setup a separate ODBC name and DNS aliases for each company and so that if you decide to move one of the database to a different server, only the IP of the DNS alias needs to change.
SQL = Scarcely Qualifies as a Language
May 9, 2008 at 5:46 pm
Would "context switching" work for you? Here's the example from Books Online...
-- Switch execution context to the dan1 user account.
EXECUTE AS USER = 'dan1';
-- Execute the DBCC statement.
DBCC CHECKDB ('AdventureWorksDW');
-- Revert to the previous execution context.
REVERT;
--Jeff Moden
Change is inevitable... Change for the better is not.
May 9, 2008 at 10:42 pm
This is not leading down a path of success and successful implementation. At minimum you should have logins that segregate schemas. This way you can maintain objects for each company. What happens if a customer wants a stored procedure to operate differently than the one everyone else is using? Then your dynamic solution will not work and you will have to create a new procedure anyway.
The best solution is to have different databases for each company, or as stated before a default schema set for each company login. This allows for seperation of objects and increased security, and allows for greater manageability.
May 10, 2008 at 9:28 am
Tried Jeff Moden's suggestion to use "context switching" but I cannot get this to work based on three schemas and a dbo schema stored procedure, where the schema of the table is not specified. Any suggestions?
EXECUTE AS USER = 'CarlFederl1'
select * from Foo
exec dbo.foo_list
revert
go
Output is:
name
------
ONE
(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure foo_list, Line 2
Invalid object name 'foo'.
-- Reproduction SQL statements
-- create database and logins not included.
USE CarlFederl
GO
create schema Schema1 authorization dbo;
go
create schema Schema2 authorization dbo;
go
create table Schema1.Foo
(namevarchar(255) not null );
create table Schema2.Foo
(namevarchar(255) not null );
CREATE USER CarlFederl1 FOR LOGIN CarlFederl1 WITH DEFAULT_SCHEMA=Schema1;
CREATE USER CarlFederl2 FOR LOGIN CarlFederl2 WITH DEFAULT_SCHEMA=Schema2;
insert into Schema1.Foo (name) values ('ONE');
insert into Schema2.Foo (name) values ('TWO');
go
create procedure dbo.foo_list as
select * from foo
go
grant execute on dbo.foo_list to CarlFederl1 , CarlFederl2;
grant select on schema1.foo to CarlFederl1 ;
grant select on schema2.foo to CarlFederl2;
go
SQL = Scarcely Qualifies as a Language
May 10, 2008 at 10:00 am
While you may be able to get this guy working with context switching, it will be extremely difficult to manage and troubleshoot. The easiest solution is to create each stored procedure in each company schema, therefore, when the company user logs into the database he will be executing the stored procedure against his default schema. This method is transparent to the user.
For example, say the stored procedures are in the database as shown below. When the user logs into the database and run the exec StoredProcedure command the schema will go to and execute the correct stored procedure. Also doing it this way it allows for customization of objects for specific companies.
Sample SP Listing:
MyDatabase.Company1.StoredProcedure
MyDatabase.Company2.StoredProcedure
May 10, 2008 at 5:02 pm
Carl Federl (5/10/2008)
Tried Jeff Moden's suggestion to use "context switching" but I cannot get this to work based on three schemas and a dbo schema stored procedure, where the schema of the table is not specified. Any suggestions?EXECUTE AS USER = 'CarlFederl1'
select * from Foo
exec dbo.foo_list
revert
go
Output is:
name
------
ONE
(1 row(s) affected)
Msg 208, Level 16, State 1, Procedure foo_list, Line 2
Invalid object name 'foo'.
-- Reproduction SQL statements
-- create database and logins not included.
USE CarlFederl
GO
create schema Schema1 authorization dbo;
go
create schema Schema2 authorization dbo;
go
create table Schema1.Foo
(namevarchar(255) not null );
create table Schema2.Foo
(namevarchar(255) not null );
CREATE USER CarlFederl1 FOR LOGIN CarlFederl1 WITH DEFAULT_SCHEMA=Schema1;
CREATE USER CarlFederl2 FOR LOGIN CarlFederl2 WITH DEFAULT_SCHEMA=Schema2;
insert into Schema1.Foo (name) values ('ONE');
insert into Schema2.Foo (name) values ('TWO');
go
create procedure dbo.foo_list as
select * from foo
go
grant execute on dbo.foo_list to CarlFederl1 , CarlFederl2;
grant select on schema1.foo to CarlFederl1 ;
grant select on schema2.foo to CarlFederl2;
go
I think the user name for the Foo table would have to be CarlFederl1... but not sure. Like others, I'd create a totally separate database for each company.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 10, 2008 at 5:46 pm
you're not an oracle shop just getting into SQL are you? This sounds like a suggestion from someone used to Oracles multiple schemas in a database to effectively give multiple databases in one instance. This is not necessary in SQL as an instance supports multiple databases. a database per customer has got to be the best , simplest, most secure way to go.
---------------------------------------------------------------------
May 10, 2008 at 6:16 pm
Thanks guys, I really appreciate all of your answers and suggestions.
Anyways, the company that I'm working for wants to build a multi-tenant data architecture using SQL Server 2005, and as you all know, there are 3 approaches:
- separate databases
- shared database, separate schemas
- shared database, share schema
The second one is the majority choice, but basically as you know, we are trying to use it
with separate schemas for tables only and with shared stored procedures, thats why I'm
having this dilemma.
Since even I wants a separate logins but they are telling me that the connection pooling will be heavy if we are going to have those logins. Does this reasoning valid?
And if ever I can convinced them with different logins, in the said application when a tenant was created, all those objects (tables, stored procedures, functions, etc) should also be generated automatically for that specific tenant. But how?
I can create the tables using dynamic sql like below:
creating tables dynamically for specific tenant:
set @sql = 'CREATE TABLE [' +@schemaname+ '].[MyCustomers] (CustID int IDENTITY (100,1) PRIMARY KEY, CompanyName nvarchar (50));'
exec(@sql)
But I know I will be having trouble with the stored procedures. Why? Because I'm using sp_executesql, so basically the query statements are also in a string form. So another question is, how can I automatically generate the stored procedures for a tenant.
Thanks guys,
Richard
May 11, 2008 at 4:22 am
Regarding your questions:
Q: Is it viable to have an architecture with separate schemas for tables but with single stored procedures that will dynamically work with the sperate schemas?
A: As has been indicated, shared stored procedures are not supported by SQL Server.
Q: If separate logins are used for each customer, will there be significant resource usage, even with connection pooling?
A: Under SQL Server, each connection uses 64K of memory, which is a minuscule amount. With Oracle, the memory usage for each connection is 1Mb ( 16 times as much memory), which is significant and may the reason for the concern.
Q: With an architecture of sperate databases for each customer, how will the database objects be created when there is a new customer?
A: Do not create the objects for each new customer but instead:
1. Have a template database that contains all of the objects and appropriate base data. This db does not need to be on the customers SQL Server.
2. Make a backup of this template database
3. When there is a new customer, restore from the template database backup under a new database name.
Some advantages of sperate databases:
1. Load balancing and capacity flexibility. As the number of customers increases, the need to have additional servers to support the workload will be needed. With sperate databases, the migration solution is easy and reliable, consisting of a database backup and then a restore to the new server ( logins would need to be transferred manually, but this is trivial). With sperate schemas, this is a major undertaking even using the object transfer functionality in SSIS.
2. Software versions. With sperate databases, the schema version can be different and is easily supported. With multiple schemas/shared sps, every customer must be on the same schema version or the shared sps will not work.
3. Customized software. Same reasoning as software versions - easier with sperate databases and not possible with shared stored procedures.
SQL = Scarcely Qualifies as a Language
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply