Hello every one ,
I am having problem in fetching data data from another Data base .
I am having 2 data base
1.BankingDB
2.MarketingDB
in this 2 DB i am having a same table MembersTB
i.e in banking i am having table MembersTB an also i am having table MembersTB in MarketingDB also with same column names .
Now i need to display the members record from StartDate to EndDate from both DataBase . I need to append both table from so i am writing query in banking table . For banking table select query is not a problem but i am facing problem in fetching data form another DB(MarketingDB).
Banking Query (Good)
SELECT MemberShipTB.AppNo, MemberShipTB.MemberID, MemberShipTB.Name, MemberShipTB.ReceiptNo, ((MemberShareTb.ShareAmt*MemberShareTb.No_of_Shares)+MemberShareTb.AdminAmt+MemberShareTb.EntranceAmt),MemberShipTB.DOJ FROM AmulyaBankingMarket.dbo. MemberShipTB INNER JOIN AmulyaBankingMarket.dbo.
MemberShareTb ON AmulyaBankingMarket.dbo. MemberShipTB.MemberID = AmulyaBankingMarket.dbo.MemberShareTb.MemberID where MemberShipTB.DOJ between '12/1/2012 12:00:00 am' and '2/19/2013 11:59:59 pm'
since I need to fetch data from another table I need to write in this format
declare @QueryExecution varchar(1000),@CrossDBname varchar(500),@StartDate Datetime,@EndDate datetime
set @StartDate='12/1/2012 12:00:00 am'
set @EndDate='2/19/2013 11:59:59 pm'
declare @QueryExecution varchar(500),@CrossDBname varchar(40)
execute @CrossDBname='MarketingDB.dbo.'
set @QueryExecution= 'SELECT MemberShipTB.AppNo, MemberShipTB.MemberID, MemberShipTB.Name, MemberShipTB.ReceiptNo,((MemberShareTb.ShareAmt*MemberShareTb.No_of_Shares)+MemberShareTb.AdminAmt+MemberShareTb.EntranceAmt),MemberShipTB.DOJ FROM '+@CrossDBname+' MemberShipTB INNER JOIN '+@CrossDBname+' MemberShareTb ON '+@CrossDBname+' MemberShipTB.MemberID = '+@CrossDBname+'MemberShareTb.MemberID where (MemberShipTB.DOJ between '+@startDate+' and '+ @endDate+')'
execute(@QueryExecution)
Here i am specifying DB name in order to fetch data from other DB .
But it is showing error like this
"Msg 241, Level 16, State 1, Line 10
Conversion failed when converting date and/or time from character string."
here MemberShipTB.DOJ is a DateTime column in Table .
so i thing the @startDate and @endDate is been treated as string than DateTime so can anyone suggest for this problem.
Thanks in Advance