dynamically determin database name

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 2008 Forums

Transact-SQL (2008)

dynamically determin database name

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2011-05-02 : 09:41:59

Good morning,

I have a request from the accounting dept. They use frozen copies of the databases. They want to have a query that looks at a database from 3months, 2 months and last month. They don't want to have to type in the database name. I've done something similar to create backups of these databases. I've included the code I thought might work but it says incorrect syntax near the first database name.

Can someone take a look and tell me what is wrong?

Thanks

Laura


Declare @CreateDBName       varchar(100);
Declare @Date				varchar(100);

SELECT @Date= CAST(YEAR(GETDATE()) AS VARCHAR(4)) + 
left(CONVERT(char(20),DATEADD(MONTH,-3,GETDATE()),101),2)

SET @CreateDBName='P'+@Date

print @CreateDBName

SELECT t.Work_Date, w.Department, 
t.Act_Run_Labor_Hrs, t.Act_Setup_Labor_Hrs, 
t.Act_Run_Hrs, t.Act_Setup_Hrs, 
(t.Act_Setup_Labor_Hrs*t.Setup_Labor_Rate)+(t.Act_Run_Labor_Hrs*t.Run_Labor_Rate) AS NewLabor, 
((t.Act_Setup_Labor_Hrs*t.Setup_Labor_Burden)+(t.act_run_labor_hrs*t.labor_burden)) AS NewVOH, 
((t.Act_Setup_Hrs*t.Machine_Burden)+(t.act_run_hrs*t.Machine_burden)) AS NewFOH, 
jo.Work_Center, 
jo.Job, 
jo.Job, 
j.Part_Number
FROM (((@CreateDBName.dbo.Job_Operation_Time t
	INNER JOIN @CreateDBName.dbo.Job_Operation jo
		ON t.Job_Operation = jo.Job_Operation) 
	LEFT JOIN @CreateDBName.dbo.Employee e
		ON t.Employee = e.Employee) 
	INNER JOIN @CreateDBName.dbo.Work_Center w
		ON jo.Work_Center = w.Work_Center) 
	INNER JOIN @CreateDBName.dbo.Job j
		ON jo.Job = j.Job
WHERE t.Work_Date BETWEEN dbo.fn_FirstDayofPreviousMonth (GETDATE()) 
AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
AND (j.Customer<>'CORNK'
Or j.Customer Is Null)

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-05-02 : 10:03:33

Declare @CreateDBName varchar(100);
Declare @Date varchar(100);

SELECT @Date= CAST(YEAR(GETDATE()) AS VARCHAR(4)) +
left(CONVERT(char(20),DATEADD(MONTH,-3,GETDATE()),101),2)

SET @CreateDBName='P'+@Date

--print (@CreateDBName)

EXEC ('SELECT t.Work_Date, w.Department,
t.Act_Run_Labor_Hrs, t.Act_Setup_Labor_Hrs,
t.Act_Run_Hrs, t.Act_Setup_Hrs,
(t.Act_Setup_Labor_Hrs*t.Setup_Labor_Rate)+(t.Act_Run_Labor_Hrs*t.Run_Labor_Rate) AS NewLabor,
((t.Act_Setup_Labor_Hrs*t.Setup_Labor_Burden)+(t.act_run_labor_hrs*t.labor_burden)) AS NewVOH,
((t.Act_Setup_Hrs*t.Machine_Burden)+(t.act_run_hrs*t.Machine_burden)) AS NewFOH,
jo.Work_Center,
jo.Job,
jo.Job,
j.Part_Number
FROM ((('+@CreateDBName+'.dbo.Job_Operation_Time t
INNER JOIN '+@CreateDBName+'.dbo.Job_Operation jo
ON t.Job_Operation = jo.Job_Operation)
LEFT JOIN '+@CreateDBName+'.dbo.Employee e
ON t.Employee = e.Employee)
INNER JOIN '+@CreateDBName+'.dbo.Work_Center w
ON jo.Work_Center = w.Work_Center)
INNER JOIN '+@CreateDBName+'.dbo.Job j
ON jo.Job = j.Job
WHERE t.Work_Date BETWEEN dbo.fn_FirstDayofPreviousMonth (GETDATE())
AND DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
AND (j.Customer<>''CORNK''
Or j.Customer Is Null)')

--------------------------
http://connectsql.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-02 : 10:12:59

Make sure to read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

mayerl
Yak Posting Veteran

95 Posts

Posted - 2011-05-02 : 10:17:19

Thanks lionofdezert. That did it thanks.

mayerl
Yak Posting Veteran

95 Posts

Posted - 2011-05-02 : 10:18:38

Madhivanan,

Is there another way to get what I want then? I know you're not a fan of dynamic SQL but this was the only way I could think to do what they are asking.

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources