web search form from SQL

rough rider

13y1.9k

Hello everyone

I'm hoping someone out there could assist me in this query.

I have two SQL 2008 databases which hold customers contacts information like customers name, address telephone numbers and email addresses, what I want or if some has done something similar is a web base search form that could or would search both database tables and return if any results are found and which database they are in.

this link shows the form fields that can be searchable as an example,

http://www.enflow.com/Cases/Asilomar/AsilImages/MotherContactFormAllContacts.gif

Thanks again

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi13y ago
hi,
I think here collation conflict, so first of all check whether your query works with out collation?
you can also define or change collation of any existing column(s)
Hope this will help you.
Iftikar Hussain13y ago
why you have used collate in your first query?

SELECT FULL_NAME FROM Server12.lawsap.dbo.VW_IA_Fir_Contacts
WHERE FULL_NAME COLLATE Latin1_General_CI_AI
rough rider13y ago
thanks jignesh, i have tried as you suggested but im getting the following error, one which i thik i resolved

error 1:
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.

the sql:
Select * from
(SELECT FULL_NAME FROM Server12.lawsap.dbo.VW_IA_Fir_Contacts
WHERE FULL_NAME COLLATE Latin1_General_CI_AI
union
SELECT Display_NM from server66.Action.dbo.VW_Con_Details) as A

the error2:
Msg 4145, Level 15, State 1, Line 5
An expression of non-boolean type specified in a context where a condition is expected, near 'union'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

Iftikar yes they are on two separate servers

thanks
Jignesh Trivedi13y ago
hi,
If the both the Databases are on same SQL server than you can use query following query.
Select * from(
Select customersname, address,telephonenumber,emailaddress from DataBase1..Table1
union
Select customersname, address,telephonenumber,emailaddress from DataBase2..Table1
) A
and on this query you can apply your where clause.
If the databases are on different server than you have make Linked Server and can use same query above just add linked server name.

Select * from(
Select customersname, address,telephonenumber,emailaddress from server1.DataBase1.dbo.Table1
union
Select customersname, address,telephonenumber,emailaddress from server2.DataBase1.dbo.Table1
) A
Please refer
http://technet.microsoft.com/en-us/library/ff772782.aspx
http://www.c-sharpcorner.com/uploadfile/suthish_nair/linked-servers-in-sql-server-2008/
hope this will help.
Iftikar Hussain13y ago
Hi,
Please let me know, are both the database in same SQL server?

Regards,
Iftikar