Click here to Skip to main content
15,886,199 members

SQL Query problem in fetching data from cross DataBase using where condition

arunrv asked:

Open original thread
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
Tags: SQL Server 2005, SQL Server

Plain Text
ASM
ASP
ASP.NET
BASIC
BAT
C#
C++
COBOL
CoffeeScript
CSS
Dart
dbase
F#
FORTRAN
HTML
Java
Javascript
Kotlin
Lua
MIDL
MSIL
ObjectiveC
Pascal
PERL
PHP
PowerShell
Python
Razor
Ruby
Scala
Shell
SLN
SQL
Swift
T4
Terminal
TypeScript
VB
VBScript
XML
YAML

Preview



When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
  4. Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
Please note that all posts will be submitted under the http://www.codeproject.com/info/cpol10.aspx.



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