SQL Server - Schema
I have two set of tables with the same name/structures/data but different schema; one set of tables with "TRP" schema and another set of tables with "DEV" schema. The TRP schema is for the production tables, and the DEV schema is for the testing tables.
I have a procedure to run using those tables. There are occasions where I need to run this procedure using the testing tables (tables with DEV schema). How do I specify in my procedure which set of tables to use on the top so that I don't have to go into every lines in the codes and change the schema of the table?
My procedure is very long, I'm just pasting here the first few lines of my procedure so that you can tell me where and how and can modify my procedure to use the DEV or the TRP set of tables in the procedure.
ALTER PROCEDURE [TRP].[FFS_validation_chec
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--updating DealStatus on APC/DRG according to CTR
exec TRP.sp_FFS_updDealStatus_M
declare @intCheckCount smallint
-- reset TRP.Validation_check/detai
DELETE FROM TRP.Validation_Check where source = 'ffs'
DELETE FROM TRP.Validation_Check_detai
--PRINT '1-- Check_if Year <> Year Plan/Act'
INSERT INTO [PSF_Gen].[TRP].[Validatio
SELECT getdate(), 'FFS', 'Check_01',
count(*) as Check_1 from (
SELECT 'Check_01' as Check_point_1, FFSContr_ID, Par_id, Trend_type, TypeOfContract, [Year], year(PlanEffDt) as Year_Plan, year(ActEffDt) as Year_Actual, DealStatus, max(updatedt) as UpdateDt
FROM TRP.FFS_Contracts
WHERE year(PlanEffDt) <> 2009 and (year(PlanEffDt) <> [Year] and year(ActEffDt) <> [Year])
and Par_id not in ('770324630 A','940535360 A','951816017 A','952282647 A','952294234 A','956064971 A')
GROUP BY FFSContr_ID, Par_id, Trend_type, TypeOfContract, [Year], year(PlanEffDt), year(ActEffDt), DealStatus
) x having count(*) <> 0
I have a procedure to run using those tables. There are occasions where I need to run this procedure using the testing tables (tables with DEV schema). How do I specify in my procedure which set of tables to use on the top so that I don't have to go into every lines in the codes and change the schema of the table?
My procedure is very long, I'm just pasting here the first few lines of my procedure so that you can tell me where and how and can modify my procedure to use the DEV or the TRP set of tables in the procedure.
ALTER PROCEDURE [TRP].[FFS_validation_chec
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--updating DealStatus on APC/DRG according to CTR
exec TRP.sp_FFS_updDealStatus_M
declare @intCheckCount smallint
-- reset TRP.Validation_check/detai
DELETE FROM TRP.Validation_Check where source = 'ffs'
DELETE FROM TRP.Validation_Check_detai
--PRINT '1-- Check_if Year <> Year Plan/Act'
INSERT INTO [PSF_Gen].[TRP].[Validatio
SELECT getdate(), 'FFS', 'Check_01',
count(*) as Check_1 from (
SELECT 'Check_01' as Check_point_1, FFSContr_ID, Par_id, Trend_type, TypeOfContract, [Year], year(PlanEffDt) as Year_Plan, year(ActEffDt) as Year_Actual, DealStatus, max(updatedt) as UpdateDt
FROM TRP.FFS_Contracts
WHERE year(PlanEffDt) <> 2009 and (year(PlanEffDt) <> [Year] and year(ActEffDt) <> [Year])
and Par_id not in ('770324630 A','940535360 A','951816017 A','952282647 A','952294234 A','956064971 A')
GROUP BY FFSContr_ID, Par_id, Trend_type, TypeOfContract, [Year], year(PlanEffDt), year(ActEffDt), DealStatus
) x having count(*) <> 0
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I go with the second option? Please show me how do I create different users in SQL with default schema set to either TRP or Dev? I'm still learning about the sql server.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I use this line?
ALTER USER currentuser WITH DEFAULT_SCHEMA = TRP
What do I replace currentuser with? How do I know what is my currentuser?
ALTER USER currentuser WITH DEFAULT_SCHEMA = TRP
What do I replace currentuser with? How do I know what is my currentuser?
ASKER
I got this error message:
Cannot alter the user 'currentuser', because it does not exist or you do not have permission.
Cannot alter the user 'currentuser', because it does not exist or you do not have permission.
ASKER
When I run this "select current_user" it shows I'm a "dbo"
So I changed the code like this:
ALTER USER dbo WITH DEFAULT_SCHEMA = Dev
and I got this error:
Cannot alter the user 'dbo'.
So I changed the code like this:
ALTER USER dbo WITH DEFAULT_SCHEMA = Dev
and I got this error:
Cannot alter the user 'dbo'.
Yes because dbo is a default user created by SQL and hence you cannot alter the properties of dbo.
'currentuser' was just a reference and is not a keyword. You will have to use actual user id. Create a user and if you are running the SP in SQL mgmt studio, connect to SQL using this user id.
eg.
CREATE USER TRP_user
WITH DEFAULT_SCHEMA=TRP
Login into SQL using TRP_user user id and then execute the SP.
'currentuser' was just a reference and is not a keyword. You will have to use actual user id. Create a user and if you are running the SP in SQL mgmt studio, connect to SQL using this user id.
eg.
CREATE USER TRP_user
WITH DEFAULT_SCHEMA=TRP
Login into SQL using TRP_user user id and then execute the SP.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ALTER USER currentuser WITH DEFAULT_SCHEMA = TRP
Because I have many procedures to run and I don't want to changes every procedure each time, I just need to change one in the Param table.