Dynamic Coding

Question

Dynamic Coding

TRACEY-320982

SSChampion

Points: 13653
More actions
December 18, 2008 at 8:14 am

#133001

i have many views which have the following:
myview
select * from REPORTDB.DELTEK.EMPL
I want to remove the REPORTDB to use a dynamic value picked up from a database
the reason is when REPORTDB is rebuilt from REPLICATED DATA all tables are deleted and the replication data is rebuild from new snapshots.
During this time application fail so i want to change the path to the PRIMARY Publisher databasename
in the interim
Example:
declare @dbname nvarchar(30),
@sql nvarchar(4000)
SELECT @dbname = REPORTINGDBNAME FROM DBA_Config
(This is just two columns in the table NAME and DATABASENAME
REPORTINGDBNAME REPORTDB
Make the views become stored procedures.
select @SQL = 'SELECT * FROM ' + @dbname + '.MYTABLE'
EXEC(@SQL)
Any one know how to achieve this without having a stored procedure and keeping the views.
Could views call functions
Thanks

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Michelle Ufford-459603 SSCommitted Points: 1642 More actions · Answer 1

Hi Tracey,

You could use stored procedures to change the view definition. If necessary, you can use sys.databases to find the appropriate database name.

Also, I'd highly recommend the use of sp_executeSQL with parameters if you're going to write prepared SQL. More info on that can be found here: http://msdn.microsoft.com/en-us/library/aa172445.aspx

HTH. Happy Holidays!

Regards,

Michelle Ufford
SQLFool.com - Adventures in SQL Tuning

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

Do you know how to change the view with a stored procedure - that might be worth visiting.

Almost like a search and replace, any guidance would be appreciated.

View would remain the same name with new db.

Michelle Ufford-459603 SSCommitted Points: 1642 More actions · Answer 3

You could try something like...

Create Procedure exampleProc

As

Begin

Declare @mySQL nvarchar(1000)

, @databaseName varchar(128);

Select Top 1 @databaseName = name

From sys.databases

Where name Like 'yourDatabaseName%'

Order By database_id Desc;

Select @mySQL = N'Alter view myView As Select Top 15 * From ' + @databaseName +'.dbo.yourTableName';

Execute sp_executeSQL @mySQL;

End;

Then just execute this proc to alter your view whenever necessary.

Regards,

Michelle Ufford
SQLFool.com - Adventures in SQL Tuning

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

Cannot understand the select top 15?

Select @mySQL = N'Alter view myView As Select Top 15 * From ' + @databaseName +'.dbo.yourTableName';

If i have 50 views i would have to declare them all

Select @mySQL = N'Alter view myView As Select Top 15 * From ' + @databaseName +'.dbo.yourTableName';

Select @mySQL = N'Alter view myView1 As Select Top 15 * From ' + @databaseName +'.dbo.yourTableName';

Select @mySQL = N'Alter view myView2 As Select Top 15 * From ' + @databaseName +'.dbo.yourTableName';

Michelle Ufford-459603 SSCommitted Points: 1642 More actions · Answer 5

Sorry for the confusion, that's just sample code.

You'll want to use your own view definition for the @mySQL parameter.

Regards,

Michelle Ufford
SQLFool.com - Adventures in SQL Tuning

noeld SSC Guru Points: 96590 More actions · Answer 6

If you alter a view while users are connected you should also run sp_refreshview @viewname = 'viewname' 😉

* Noel

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

I would recommend using synonyms for this. Much easier to manage and change as needed. Example:

-- create a schema for the synonyms, not really needed - could use dbo schema

CREATE SCHEMA MySchema AUTHORIZATION dbo;

CREATE SYNONYM MySchema.MyTable AS ReportDB.dbo.MyTable;

CREATE SYNONYM MySchema.MySecondTable AS ReportDB.dbo.MySecondTable;

...

Create your view as:

CREATE VIEW dbo.MyView AS

SELECT ...

FROM MySchema.MyTable

GO

Now, to change the view over to another database:

DROP SYNONYM MySchema.MyTable;

CREATE SYNONYM MySchema.MyTable AS NewDatabase.dbo.MyTable;

You could easily create a stored procedure that does the above for all synonyms. It would be very similar to the one already provided - but instead of altering the view, you would drop and recreate the synonyms.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Michelle Ufford-459603 SSCommitted Points: 1642 More actions · Answer 8

That's pretty neat, Jeffrey... I've never used synonyms before. Then again, I don't really have any need to, but still an interesting approach. 🙂

Regards,

Michelle Ufford
SQLFool.com - Adventures in SQL Tuning

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 9

Looks interesting.

Never used that before - Can you help me with a real example 🙂

This way i really understand if i can see a real example.

Here is one view

DATABASENME = REPORTDB

Create VIEW [ARST].[vw_ARS_ACCT] AS

SELECT *

FROM REPORTDB.DELT.ACCT REPORTDB = (DATABASENAME)

WHERE ACCT_ID IN DELT (SCHEMA), ACCT =TABLENAME

(SELECT ACCT_ID

FROM REPORTDB.DELT.ORG_ACCT

WHERE ORG_ID LIKE '9.010.1%')

---Now REPORTDB Goes down and i need all to point to REPORTDB_STANDBY (DATABASENAME)

I have other views with

Create VIEW [BALIT].[vw_BAL_ACCT] AS

Thank you ............

You may save me some coding of 100+ views to Stored procedures and even prevent

developers having to change code too.

Looking forward to your email.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

So, the first thing we need to do is define the schema:

CREATE SCHEMA DELT AUTHORIZATION dbo;

Then, we would create the synonyms:

CREATE SYNONYM DELT.ACCT AS REPORTDB.DELT.ACCT

CREATE SYNONYM DELT.ORG_ACCT AS REPORTDB.DELT.ORG_ACCT;

Create the view as:

Create VIEW [ARST].[vw_ARS_ACCT] AS

SELECT *

FROM DELT.ACCT REPORTDB = (DATABASENAME)

WHERE ACCT_ID IN DELT (SCHEMA), ACCT =TABLENAME

(SELECT ACCT_ID

FROM DELT.ORG_ACCT

WHERE ORG_ID LIKE '9.010.1%')

Now, when REPORTDB goes down - we repoint the synonyms by recreating them:

DROP SYNONYM DELT.ACCT;

CREATE SYNONYM DELT.ACCT AS REPORTDB_STANDBY.DELT.ACCT;

DROP SYNONYM DELT.ORG_ACCT;

CREATE SYNONYM DELT.ORG_ACCT AS REPORTDB_STANDBY.DELT.ORG_ACCT;

That should be it - verify the syntax in Books Online (I am doing this just from memory). You could easily create a procedure that reads from sys.synonyms - and generates the drop/create statements for you.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 11

Thanks again, i will be looking in to this and doing some testing with it.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 12

Just tried it out, thats pretty good - thanks very much for sharing.

--Code change is FOR instead of AS.

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 13

Question if i have the

PUBLISHED DB SERVER1 Database: MAINDB replicated to ----> REPORTDB (synonym) i repoint these back to PUBLISHED DB MAINDB so that i can rebuild REPORTDB tables when it goes down.

Would the replications tables get repointed back to PUBLISHED DB?

Maybe i can only use the SYNONYM on other db thats are not published.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 14

Tracey, I am not sure what you are asking here. If you drop and create a synonym - the synonym will now be a synonym for the new object.

Synonyms do not 'direct' a statement - they are just another name for an object. This way - your code stays the same, but the object the synonym references changes.

Not sure if that answers your question.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs