How can i do ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How can i do ?

Author

Topic

muzaffar_ali99
Starting Member

33 Posts

Posted - 2009-06-09 : 02:54:58

Hi
I have a problem that i have made a database for dataloading in which first data comes and then migrated to another database.
My problem is i want a global variable or something that i can use instead of naming that database because there are many migration procedures and i have to find and replace db name in many stored procds also i dont want to use dynamic sql using variable then executing it.
Here is example of problem
INSERT INTO [Migrantdb].[dbo].StudentAssessment(SCDS, Year, Grade, SubjID, ProficiencyLevel)
SELECT S.SCDS, Year, Grade, SubjID, 'P2' as ProficiencyLevel
FROM StudentAssessment_Staging TA
I want MigrantDb to be changed to something that i change at one palce and it get reflected in all stored procedures.
I searched on net it says to make a table then use its value but for that purpose i have make dynamic sql that i dont want.

Thanks
Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 03:08:07

You can use SYNONYMs.

E 12°55'05.63"
N 56°04'39.26"

muzaffar_ali99
Starting Member

33 Posts

Posted - 2009-06-09 : 03:12:23

THanks for the quick reply, can you please give me an example/sample of SYNONYMs, please ignore my ignorance.

thanks
regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 03:15:41

http://www.lmgtfy.com/?q=create+synonym+in+sql+server

Then click on the first suggested link.

Synonyms very much work like an "alias" for the underlying object.

E 12°55'05.63"
N 56°04'39.26"

muzaffar_ali99
Starting Member

33 Posts

Posted - 2009-06-09 : 03:29:52

ok thanks i got what SYNONYM are but it lets me define to specific table name but i only need to define the db name SYNONYM because defining synonym for every table name will be another long process then.
CREATE SYNONYM ProdCat
FOR [SChoolPortfolio].dbo

SELECT * FROM ProdCat.SubjectDefinition
it gives me error "Invalid object name 'ProdCat.SubjectDefinition'."
but i want generic level at only db level not tables level.

Thanks
Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 03:48:39

How often will the synonyms change?

E 12°55'05.63"
N 56°04'39.26"

muzaffar_ali99
Starting Member

33 Posts

Posted - 2009-06-09 : 04:08:54

After some months or whenever destination db name change required.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-09 : 04:21:25