Once again, no records returned....

The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?

------------------------------
CREATE PROCEDURE spUnitsbyUnitID
@.unitid int
AS

SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled

FROM tbl_units as A

INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
INNER JOIN tbl_contacts as D on A.unitid = D.unitid
INNER JOIN tbl_cameras as E on A.unitid = E.unitid

WHERE A.UnitID = @.unitID
GO
-------------------------------Are you sure that there are records in all 5 tables with the same unitid ? Using inner joins, you are limiting the result set to that scenario.

Jeff|||You are missing the OUTPUT keyword which is required for stored Procs returning none Numeric value. As per the previous post you can only use INNER JOIN if both tables are equal and the ANSI SQL OUTER JOIN limit is four because OUTER JOIN has default NULL condition but that may not work for HTTP applications because HTTP aplications are Stateless. Check out the MSDN link below.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_3q7n.asp Hope this helps.

Kind regards,
Gift Peddie|||Found the problem, 1 of the tables didn't have a test value entered in for the unitID. Thanks for the help!sql

OLEDB command gets compile errors but works in Query analyzer.

The following statement is valid in query analyzer but will not compile as a prepared statement in an OLEDB command in DTS 2005.

delete
from
purEncumbrance_Fct
where
AgreementId = ?
and FundId = ?
and AccountId = ?
and coalesce(PODistributionId,0) = coalesce(?,0)
and coalesce(VoucherDistributionId,0) = coalesce(?,0)

Why does this statement not compile?

KenTry putting square brackets around the table and column names. I have a vague recollection of this working for me in the dim and distant past.

-Jamie|||Jamie,

I tried your suggestion with great hopes, but it did not work.

I wonder if this is a bug or a limitation with prepared statements. I know this command works in query analyzer, so maybe if I place it in a stored procedure it will work. I don't want to have to manage another piece of code, but if that is what it takes, I will.|||Not tested, but I know the statement prepare stuff and OLE-DB parameters can be rather fussy. Try loosing the coalesce(?, 0) and just use ?. Assuming that works, handle the coalesce values through a derived column, e.g.

ISNULL(Column) ? 0 : Column

Old SQL Design Tools

Hi Guys...

I was familiar to use sqlserver2000 more than 2 years, and Utilized from beneficial Tools Like Enterprise Manager and Query Analyzer which was so helpfull... .

afterthen I decide to get knowledge on SqlServer 2005 Enterpise Edition.

and after install 2CD's and SP1 , I try to find the Old Tools mentioned above , or find any DataBase as example , but I find nothing ... so is there any tool do the role to related tools in the last version , or should I get another vesion instead like Express or ...etc.

Thanks

Basel

There should be SQL Server Management Studio, which is both EM and QA at once (an many other features as well).

If only it's not an Express version, either.

|||

just a little word of inspiration

"Don't get intimidated by the new UI"

everything has changed. though you can find it out easily

|||

Inspiration is all what I need to start great new day

Tnax to send your helpful space