[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSDE vs SQL Server Express 2005

Posted on 2008-11-10
11
Medium Priority
?
408 Views
Last Modified: 2012-05-05
I upgraded from MSDE to SQL server Express 2005.  My asp code that has inner and outer joins in the SQL statements doesn't seem to work the same.  I can write the SQL statement to I.E. and paste it in SQL Management studio and it give results.  The same query running in ASP vbScript under IIS doesn't returns no records.  If I simplfied the query to just one table, it will return records.

Any ideas on what the difference might be?
0
Comment
Question by:GeekRUS
11 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22926619
Are you using TSQL join syntax for your outer joins?

ex:
Select * from YourTable1 t1
,yourtable2 t2
where t1.id *= t2.id


Or ANSI standard syntax

Select * from YourTable1 t1
left outer yourtable2 t2
on t1.id = t2.id

0
 

Author Comment

by:GeekRUS
ID: 22926633
Like this
Or ANSI standard syntax

Select * from YourTable1 t1
left outer yourtable2 t2
on t1.id = t2.id
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22927635
Well I can assure you that queries will be the same in 2000 and 2005 if you are using the ANSI syntax.  Do you want to post your query so that it can be analyzed?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GeekRUS
ID: 22927774
SELECT Photos.PhotoID, Photos.AlbumID, Photos.Sequence, Photos.FileName, Photos.ThumbnailName, TeamDT.PlayerFirstName, PlayersInPic.Inden FROM TeamDT RIGHT OUTER JOIN PlayersInPic ON TeamDT.Id = PlayersInPic.TeamDTID RIGHT OUTER JOIN Photos INNER JOIN EventList ON Photos.EventID = EventList.EventID ON PlayersInPic.PhotoID = Photos.PhotoID WHERE Photos.PhotoID =25751

Thanks for your help!
0
 
LVL 11

Expert Comment

by:Louis01
ID: 22929412
This should not make a difference, but try...
SELECT Photos.PhotoID, Photos.AlbumID, Photos.Sequence, Photos.FileName, Photos.ThumbnailName, TeamDT.PlayerFirstName, PlayersInPic.Inden 
  FROM TeamDT 
	RIGHT OUTER JOIN PlayersInPic 
	 ON TeamDT.Id = PlayersInPic.TeamDTID 
	RIGHT OUTER JOIN ((SELECT * FROM Photos WHERE PhotoID = 25751) Photos INNER JOIN EventList ON Photos.EventID = EventList.EventID)
	 ON PlayersInPic.PhotoID = Photos.PhotoID 

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22931127
Ok.  I reorganized this to make it look more normal to me.  What you want here is everything from photos and event list, with the matching records from playersinpic and teamdt, for photo it 25751?

SELECT Photos.PhotoID, Photos.AlbumID, Photos.Sequence, Photos.FileName, Photos.ThumbnailName, TeamDT.PlayerFirstName, PlayersInPic.Inden
FROM TeamDT
RIGHT OUTER JOIN PlayersInPic
  ON TeamDT.Id = PlayersInPic.TeamDTID
RIGHT OUTER JOIN Photos
  ON PlayersInPic.PhotoID = Photos.PhotoID
INNER JOIN EventList
ON Photos.EventID = EventList.EventID
WHERE Photos.PhotoID =25751
0
 

Author Comment

by:GeekRUS
ID: 22931818
If the record (photoid) exist in Playersinpic I don't want it in the result.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 2000 total points
ID: 22934475
So how about you explain what you DO want?

You want a list of photos (or one photo)? YES/NO
You want the teams "IN" the photo(s)? " YES/NO
You want the people "IN" the photo(s)? YES/NO


0
 

Author Comment

by:GeekRUS
ID: 22934690
You want a list of photos  YES
You want the teams "IN" the photo(s)? " YES
You want the people "IN" the photo(s)? NO

I have a list of photos, a list of players and a list of what players are in what photo.
So if there is a photoid that does not exist in the Playersinpic table I want that in the result
The followiing quert works in Management Studio put not in the code under IIS.

SELECT     Photos.PhotoID, Photos.AlbumID, Photos.Sequence, Photos.FileName, Photos.ThumbnailName, TeamDT.PlayerFirstName, PlayersInPic.Inden
FROM         TeamDT RIGHT OUTER JOIN
                      PlayersInPic ON TeamDT.Id = PlayersInPic.TeamDTID RIGHT OUTER JOIN
                      Photos INNER JOIN
                      EventList ON Photos.EventID = EventList.EventID ON PlayersInPic.PhotoID = Photos.PhotoID
WHERE     (Photos.EventID = 145) AND (PlayersInPic.Inden IS NULL) AND (Photos.PhotoID >= 0)
ORDER BY Photos.PhotoID

0
 

Accepted Solution

by:
GeekRUS earned 0 total points
ID: 22935049
I figured this one out...  I don't understand it but this works.  I changed the OS from sever 2003 web edition to server 2003 SBE.  I also changed the DB from MSDE to SQL Express 2005

The fix was adjusting the line of code the opened the query.
It used to look like this.
     Images.Open Query, adoConnect, 3, 3, 1
No it looks like this
    Images.Open Query, adoConnect

Thanks For your effort.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22935869
To translate that into English, you changed the way you were opening the recordset from:
adOpenStatic CursorType, adLockOptimistic LockType and the Options set to adCmdText.
To the default:
adOpenForwardOnly and adLockReadOnly

And your right it has nothing to do with the version of SQL Server.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question