Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Nested queries

Author  Topic 

colgie
Starting Member

18 Posts
Posted - 2010-08-18 : 15:18:14
I have to select client name from one database and then query another for information about that client. Is it best to open two connections and nest them or put all the results from the first query into an array, close the connection and then open another connection. Or doesn't it matter?

Thanks for your help on this.

Kristen
Test

22859 Posts
Posted - 2010-08-18 : 17:27:48
Why not just include the "remote" database in your query?

SELECT Col1, Col2, ...
FROM dbo.MyCLientNameTable AS T1
     JOIN MyOtherDatabase.dbo.MyClientInformationTable AS T2
         ON T2.ClientID = T1.ClientID
WHERE T1.SomeColumn = 'SomeValue'
Go to Top of Page

colgie
Starting Member

18 Posts
Posted - 2010-08-19 : 06:30:37
Looks like just what I want but I'm not sure how to declare the databases and connections in Classic asp - can you or anyone else help?
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-08-19 : 10:55:22
You don't have to declare a connection. Connect to DatabaseA as normal, in your query reference DatabaseB (as per my example). You will need to have permission on DAtabaseB - same as you have to have on DatabaseA.

If DatabaseB is on a different server its slightly more complicated, but still doable with much the same syntax, and a connection to a single database.
Go to Top of Page

colgie
Starting Member

18 Posts
Posted - 2010-08-19 : 17:44:06
I'm sort of getting it now but sorry to be a pain I need further help.
If you can see your way clear to spoon feeding me - here is a description of my scenario:

Database1 is called NAFLcustomers and the table is called customers
it contains email addresses in a field called cusemail
(a customer who wants to be emailed with all the coupons issued by particular businesses)
It also contains ten fields called couponname1,couponname2 etc. of which some may be empty.
(The names of the businesses the customer wants an email about)
Database2 is called NAFLcoupons and the table is called coupons
It contains a field called couponname
(the company issuing a particular coupon - it may appear several times in the table)
It also contains a field called image
(the location of the actual coupon image jpeg)

I need a table which contains a list of email addresses
(cusemails from Database1)
and the locations of the coupon images they want sent to them
(image fields from Database2)

Ideally I'd like the query but If you don't have time (or the inclination) to sort all this out - of course I understand. Even if not perhaps you could tell me if it is possible and whether I would be better off doing it with two connections which is much more simple for me to implement or whether I should persist and learn the necessary technology because of the clumsyness and possible time overhead of the two connections.

In my Global ASA file I have the following for both databases:

Application("NAFLcoupons") = "Provider=SQLNCLI;Server=xxxxxx;Database=NAFLcoupons;Uid=xxxxxx;Pwd=xxxxxx;"
Application("NAFLcustomers") = "Provider=SQLNCLI;Server=xxxxxx;Database=NAFLcustomers;Uid=xxxxxx;Pwd=xxxxxx;"

Is that all I need for permissions?

Thanks a trillion in advance
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2010-08-20 : 01:30:14
Please post the DDL of the two tables. (DDL means a script for the CREATE for the tables and columns, and the foreign keys between the tables)
Go to Top of Page

colgie
Starting Member

18 Posts
Posted - 2010-08-20 : 04:20:09
There are no foreign keys in either table
This is coupons and the relevant fields are cname and image

<%
Dim strSQL
strSQL = strSQL & "CREATE TABLE coupons "
strSQL = strSQL & "("
strSQL = strSQL & "bname varchar (8000), "
strSQL = strSQL & "badd1 varchar (8000), "
strSQL = strSQL & "badd2 varchar (8000), "
strSQL = strSQL & "btown varchar (8000), "
strSQL = strSQL & "bstate varchar (8000), "
strSQL = strSQL & "bzip varchar (8000), "
strSQL = strSQL & "bt1p1 varchar (50), "
strSQL = strSQL & "bt1p2 varchar (50), "
strSQL = strSQL & "bt1p3 varchar (50), "
strSQL = strSQL & "bt1ex varchar (50), "
strSQL = strSQL & "bt2p1 varchar (50), "
strSQL = strSQL & "bt2p2 varchar (50), "
strSQL = strSQL & "bt2p3 varchar (50), "
strSQL = strSQL & "bt2ex varchar (50), "
strSQL = strSQL & "bfax1 varchar (50), "
strSQL = strSQL & "bfax2 varchar (50), "
strSQL = strSQL & "bfax3 varchar (50), "
strSQL = strSQL & "bcontact varchar (8000), "
strSQL = strSQL & "bemail varchar (8000), "
strSQL = strSQL & "star1 varchar (50), "
strSQL = strSQL & "star2 varchar (50), "
strSQL = strSQL & "star3 varchar (50), "
strSQL = strSQL & "billcode varchar (8000), "
strSQL = strSQL & "pageaddress varchar (8000), "
strSQL = strSQL & "category varchar (8000), "
strSQL = strSQL & "subcategory varchar (8000), "
strSQL = strSQL & "template varchar (8000), "
strSQL = strSQL & "couponid varchar (8000), "
strSQL = strSQL & "cname varchar (8000), "
strSQL = strSQL & "caddress varchar (8000), "
strSQL = strSQL & "cemail varchar (8000), "
strSQL = strSQL & "cweb varchar (8000), "
strSQL = strSQL & "ctag varchar (8000), "
strSQL = strSQL & "ctp1 varchar (50), "
strSQL = strSQL & "ctp2 varchar (50), "
strSQL = strSQL & "ctp3 varchar (50), "
strSQL = strSQL & "expiry1 varchar (50), "
strSQL = strSQL & "expiry2 varchar (50), "
strSQL = strSQL & "expiry3 varchar (50), "
strSQL = strSQL & "cont1 varchar (8000), "
strSQL = strSQL & "cont2 varchar (8000), "
strSQL = strSQL & "cont3 varchar (8000), "
strSQL = strSQL & "cont4 varchar (8000), "
strSQL = strSQL & "cont5 varchar (8000), "
strSQL = strSQL & "cont6 varchar (8000), "
strSQL = strSQL & "cont7 varchar (8000), "
strSQL = strSQL & "cont8 varchar (8000), "
strSQL = strSQL & "cont9 varchar (8000), "
strSQL = strSQL & "cont10 varchar (8000), "
strSQL = strSQL & "cont11 varchar (8000), "
strSQL = strSQL & "cont12 varchar (8000), "
strSQL = strSQL & "cont13 varchar (8000), "
strSQL = strSQL & "cont14 varchar (8000), "
strSQL = strSQL & "cont15 varchar (8000), "
strSQL = strSQL & "cont16 varchar (8000), "
strSQL = strSQL & "cont17 varchar (8000), "
strSQL = strSQL & "cont18 varchar (8000), "
strSQL = strSQL & "cont19 varchar (8000), "
strSQL = strSQL & "cont20 varchar (8000), "
strSQL = strSQL & "cont21 varchar (8000), "
strSQL = strSQL & "cont22 varchar (8000), "
strSQL = strSQL & "cont23 varchar (8000), "
strSQL = strSQL & "cont24 varchar (8000), "
strSQL = strSQL & "enable varchar (50), "
strSQL = strSQL & "free1 varchar(50), "
strSQL = strSQL & "detailsflag varchar (50), "
strSQL = strSQL & "designflag varchar (50), "
strSQL = strSQL & "newflag varchar (50), "
strSQL = strSQL & "image varchar (8000), "
strSQL = strSQL & "mapaddress varchar (8000), "
strSQL = strSQL & "tempcat varchar (8000), "
strSQL = strSQL & "tempsub varchar (8000), "
strSQL = strSQL & "tempimage varchar (8000),"
strSQL = strSQL & "tempmapaddress varchar (8000),"
strSQL = strSQL & "tempcname varchar (8000),"
strSQL = strSQL & "alphaday varchar (8000),"
strSQL = strSQL & "alpha varchar (8000),"
strSQL = strSQL & "free2 varchar (50)"
strSQL = strSQL & ")"
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("NAFLcoupons")
conn.Execute strSQL
conn.Close
Set conn = Nothing
%>

This is customers and obviously cusemail and cname1,cname2 etc are the relevant fields
<%
Dim strSQL
strSQL = strSQL & "CREATE TABLE customers"
strSQL = strSQL & "("
strSQL = strSQL & "cusemail varchar (8000),"
strSQL = strSQL & "cuspass varchar (8000),"
strSQL = strSQL & "cname1 varchar (8000),"
strSQL = strSQL & "cname2 varchar (8000),"
strSQL = strSQL & "cname3 varchar (8000),"
strSQL = strSQL & "cname4 varchar (8000),"
strSQL = strSQL & "cname5 varchar (8000),"
strSQL = strSQL & "cname6 varchar (8000),"
strSQL = strSQL & "cname7 varchar (8000),"
strSQL = strSQL & "cname8 varchar (8000),"
strSQL = strSQL & "cname9 varchar (8000),"
strSQL = strSQL & "cname10 varchar (8000),"
strSQL = strSQL & "activatecode varchar (8000),"
strSQL = strSQL & "activateflag varchar (8000)"
strSQL = strSQL & ")"
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("NAFLcustomers")
conn.Execute strSQL
conn.Close
Set conn = Nothing
%>
I gave them most of the fields varchar(8000) because I was told it doesn't affect performance and I was getting errors from too much text being entered in some of them.

Any comments on how to improve them will be welcomed as I am very new to SQL, have a very sketchy knowledge and am struggling along
Go to Top of Page

colgie
Starting Member

18 Posts
Posted - 2010-08-23 : 13:31:18
have I posted these in the correct way and in the correct place. I have been waiting anxiously for your next post. I expect you are extremely busy so I don't want to put any pressure on you. I'd just like to know that you have seen my posts.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site