Click here to Skip to main content
15,886,026 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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)
SQL
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


SQL
  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
Posted

Insted of declaring your variable as datetime declare it as varchar
as i have declared below and used in your code

----------------------

SQL
declare @QueryExecution varchar(1000),@CrossDBname varchar(500),@StartDate varchar(10),@EndDate varchar(10)

  set @StartDate='2013-01-01' 
  set @EndDate='2013-01-31'



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 (convert(varchar(10),MemberShipTB.DOJ,121)) between convert(varchar(10),'+@startDate+',121) and convert(varchar(10),'+@endDate+',121)'			
 execute(@QueryExecution)



I hope it will help you

Note: i have not tested this code on my server ,sorry if it does not work.
 
Share this answer
 
just need to append a single quot ''' and convert it to varchar
here is the answer

SQL
'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 '''+convert(varchar(30),@startDate)+''' and '''+ convert(varchar(30),@endDate)+''')'
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900