Database as variable

what distinguishes the data on each server?

could you create a distributed view over the tables and so not have to worry about which server held the data?

what is your actual data scenario?

Lowfatspread:
We deal with two entities in our business model.
  Structured Settlements and Lottery
By law the databases cannot integrate data.  The "views" issue is a gray area because we're only creating a reporting system.
..but when it comes to the Feds we'd rather not take a chance

Also...the select statement I'm using is actually populating a #temp table that is immediatelly killed when the report has been printed.

There is also the option of using a condition. It means the query would have to be maintained in 2 placed but might make it more readable than using a dynamic query.

In addition, if going down the dynamic route it may also be safter to use square braces in case the DB name needs them due to a character such as a hypen which would not be liked. Also, son't forget the schema.

SELECT @Sql ='SELECT fields FROM [' + @dbname +'].dbo.Table'

DECLARE @db sysname
SET @db = 'CRMUAT_01'

IF	@db = 'CRMUAT_01'
BEGIN
	SELECT	p.*
	FROM    CRMUAT_01.dbo.P_Deals p LEFT JOIN    
		CRMUAT_01.dbo.P_Events_Table pe ON p.DealID = pe.dealID LEFT JOIN    
		CRMUAT_01.dbo.P_Events_Table_Types pet ON pe.EventTypeID = pet.EventTypeID
END
ELSE IF @db = 'CRMPROD_01'
BEGIN
	SELECT	p.*
	FROM    CRMPROD_01.dbo.P_Deals p LEFT JOIN    
		CRMPROD_01.dbo.P_Events_Table pe ON p.DealID = pe.dealID LEFT JOIN    
		CRMPROD_01.dbo.P_Events_Table_Types pet ON pe.EventTypeID = pet.EventTypeID

Select allOpen in new window

Can you create 2 copies or SP on each server and then call proc as
exec dbName..procName
i
or define 2 connection strings in your application and exec proc on one or another connection

My attached query in dynamic sql runs and I get the "1256842" records created message.
However...
I can't access the #temp table.
Any ideas?

declare @sql nvarchar(MAX)
declare @dbname nvarchar(30)
set @dbname = 'CRMPROD_01'

set @sql = '
SELECT  p.Rep,pet.EventType,pet.eventDescription,    
YEAR(pe.EventData) as YR,    
                sum(case when MONTH(pe.EventData)=1 then 1 else 0 end) as Jan,    
                sum(case when MONTH(pe.EventData)=2 then 1 else 0 end) as Feb,    
                sum(case when MONTH(pe.EventData)=3 then 1 else 0 end) as Mar,    
                sum(case when MONTH(pe.EventData)=4 then 1 else 0 end) as Apr,    
                sum(case when MONTH(pe.EventData)=5 then 1 else 0 end) as May,    
                sum(case when MONTH(pe.EventData)=6 then 1 else 0 end) as Jun,    
                sum(case when MONTH(pe.EventData)=7 then 1 else 0 end) as Jul,    
                sum(case when MONTH(pe.EventData)=8 then 1 else 0 end) as Aug,    
                sum(case when MONTH(pe.EventData)=9 then 1 else 0 end) as Sept,    
                sum(case when MONTH(pe.EventData)=10 then 1 else 0 end) as Oct,    
                sum(case when MONTH(pe.EventData)=11 then 1 else 0 end) as Nov,    
                sum(case when MONTH(pe.EventData)=12 then 1 else 0 end) as Dec,  
                                count(pe.EventData) as YTD into #temp
FROM    ' + @dbname + '.dbo.P_Deals p LEFT JOIN    
		' + @dbname + '.dbo.P_Events_Table pe ON p.DealID = pe.dealID LEFT JOIN    
		' + @dbname + '.dbo.P_Events_Table_Types pet ON pe.EventTypeID = pet.EventTypeID    
Where pet.active = ''True'' and pe.EventData is not null and pe.EventData <> ''''
Group by p.Rep,pet.EventType,pet.eventDescription,year(pe.EventData),pet.EventTypeID  
order by p.rep, pet.EventTypeID'

exec (@sql)

Select allOpen in new window

Bodestone:
  Ok...final question...
This data is usually @ 10,000 records at most.
Is there any reason why I can't just do an insert into temp and then drop the table afterwards?
Does that start making a significant log or db size issue?

Not sure I follow what you mean there. Do you mean insert all the data from both databases into a temp table with an additional column of DBName and then query it with a parameter?

Temp tables are always dropped when the connection that created them ends. This is why when creating a temp table within an exec statement it cannot be accessed afterwards because it is automatically dropped when that statement is finished.

In your dynamic SQL you can insert into ##temp which would continue to exest outside the exec statement but you would then have to explicitly drop it to be certain.

Bodestone:
 
Perfect...didn't know about the ##
Heres's my final insert...I drop the ##temp at the end of the stored procedure
 

declare @sql nvarchar(MAX)
set @sql = '
SELECT p.Rep,pet.EventType,pet.eventDescription,
YEAR(pe.EventData) as YR,
sum(case when MONTH(pe.EventData)=1 then 1 else 0 end) as Jan,
sum(case when MONTH(pe.EventData)=2 then 1 else 0 end) as Feb,
sum(case when MONTH(pe.EventData)=3 then 1 else 0 end) as Mar,
sum(case when MONTH(pe.EventData)=4 then 1 else 0 end) as Apr,
sum(case when MONTH(pe.EventData)=5 then 1 else 0 end) as May,
sum(case when MONTH(pe.EventData)=6 then 1 else 0 end) as Jun,
sum(case when MONTH(pe.EventData)=7 then 1 else 0 end) as Jul,
sum(case when MONTH(pe.EventData)=8 then 1 else 0 end) as Aug,
sum(case when MONTH(pe.EventData)=9 then 1 else 0 end) as Sept,
sum(case when MONTH(pe.EventData)=10 then 1 else 0 end) as Oct,
sum(case when MONTH(pe.EventData)=11 then 1 else 0 end) as Nov,
sum(case when MONTH(pe.EventData)=12 then 1 else 0 end) as Dec,
count(pe.EventData) as YTD
into ##temp
FROM ' + @dbname + '.dbo.P_Deals p LEFT JOIN
' + @dbname + '.dbo.P_Events_Table pe ON p.DealID = pe.dealID LEFT JOIN
' + @dbname + '.dbo.P_Events_Table_Types pet ON pe.EventTypeID = pet.EventTypeID
Where pet.active = ''True'' and pe.EventData is not null and pe.EventData <> ''''
Group by p.Rep,pet.EventType,pet.eventDescription,year(pe.EventData),pet.EventTypeID
order by p.rep, pet.EventTypeID'
exec (@sql)
 

Don't forget it was Dhaest who gave you the solution. I just helped out with a couple of minor details.

Dhaest: and Bodestone:
You guys mind a 350 /150 split?

Just to let you know:

You can avoid dynamic SQL and/or duplicate queries by putting the procedure in the master db, naming it beginning with "sp_", and using a system proc to mark it as a system object.  It will then automatically reference whatever db it's called from.

ScottPletcher:  Thanks...but this stored procedure is in a REPORTS Database and needs to reference any one of 5 databases on the same SQL server Instance.
 

Aye but given the circumstances I thought in this instance messing with the master DB could get messy. Also the procedure looks like it is being called from a reporting DB to fetch data from 2 other DBs.

I'm happy with whatever Dhaest is. If he wants more it is fine by me.

>> but this stored procedure is in a REPORTS Database and needs to reference any one of 5 databases on the same SQL server Instance. <<

That's exactly what I'm talking about.  If the code is in the master db, it can reference any db on that server **without changing the code in any way**.

It's not really "messy", you just have to document that it's there.  But I can understand if you don't want to use it.  Then again, you might reconsider after having errors for the third time because your dupliicate copies of the code got out of sync :-) .

Bodestone:
You ok with the 150?  I'd like to close this question

I'm happy with 150. Cheers.

Scott.

What the issue is here is, as you say, with a system procedure it operated on the database it is being called from.

The procedure will be called from databaseA but wants to retrieve data from databaseB or databaseC

With the dynamic SQL solution there is only one set of code to maintain.

Excellent job folks!