problem with sql string

I am trying to return with count 0

I know that's not possible but how can I see them

This is a report that will tell me when there are no items showing on the partyguide page

selectcount(*)as reltheme, display_namefrom bb_guide_party

leftjoin(select bb_guide_party_seqfrom bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_idand b.active_Flag= 1) dd

on bb_guide_party.bb_guide_party_seq= dd.bb_guide_party_seq

and bb_guide_party.active_flag=1

groupby display_name

havingcount(display_name)<1

Maybe you can something like this:

select * from (

selectcount(*)as reltheme, display_namefrom bb_guide_party

leftjoin(select bb_guide_party_seqfrom bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_idand b.active_Flag= 1) dd

on bb_guide_party.bb_guide_party_seq= dd.bb_guide_party_seq

and bb_guide_party.active_flag=1

groupby display_name

) where reltheme = 0

Hope it helps

|||

the problem is that the count is always equal or greater then 1 because if a row shows up it is 1 count.

I need to check when it doesn't have a row.

|||

This is a total stab in the dark, since i'm not familiar with your data (which is the one, which is the many), but...

select a.bb_guide_party_seq, count(a.bb_guide_party_seq)
from bb_guide_party_dept a
inner join binbox_dept b
on a.dept_id = b.dept_id
left outer join bb_guide_party c
on c.bb_guide_party_seq = a.bb_guide_party_seq
where b.active_Flag = 1
and c.bb_guide_party_seq is null
group by a.bb_guide_party_seq

|||

selectdistinct dept_name

from bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_id

and b.active_Flag= 1

selectdistinct dept_name

from bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_id

I need to return what doesn't show up in the first select statement

|||

selectdistinct dept_name

from bb_guide_party_dept d, binbox_dept ewhere d.dept_id= e.dept_id

and e.active_Flag= 1

union

selectdistinct dept_name

from bb_guide_party_dept a, binbox_dept bwhere a.dept_id= b.dept_id

and e.dept_namenot the sameas b.dept_name

problem with SP to return last @@Identity

I need to retun the last inserted Identity value. - The SP below has a synta
x
error on the Set line. How can I fix it?
create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Set @.e = Select @.@.Identity
return
go
Thanks,
Rich"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
>I need to retun the last inserted Identity value. - The SP below has a
>syntax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich
Set @.e = @.@.Identity
or
Select @.e = @.@.Identity
Option 1 is preferred for a single assignment.
Also look up scope_identity() in BOL.|||The offending line should be
SELECT @.e = @.@.IDENTITY
however, why create a stored procedure to get @.@.IDENTITY, when you can just
retrieve its value within a batch using SELECT @.@.IDENTITY?
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||create procedure stp_GetIdentity
@.e int output
as
SET NOCOUNT ON
Select @.e = @.@.Identity
return
go
--This seems to work
"Rich" wrote:

> I need to retun the last inserted Identity value. - The SP below has a syn
tax
> error on the Set line. How can I fix it?
> create procedure stp_GetIdentity
> @.e int output
> as
> SET NOCOUNT ON
> Set @.e = Select @.@.Identity
> return
> go
> Thanks,
> Rich|||Thanks. From what I understand Scope_Identity works within a specified scop
e
which I interpret to mean if you insert a row into tbl1 which contains 10
rows in one procedure and also insert a row into tbl2 which contains 700 row
s
in another procedure and you only want to return the Identity value in tbl1
you could use Scope_Identity.
May I ask how Scope_Identity would be implemented in my SP to return the
Identity value of the last inserted row into tbl1?
?
"Raymond D'Anjou" wrote:

> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:E3FF5947-5ED7-4E0A-BCD7-486D27C51375@.microsoft.com...
> Set @.e = @.@.Identity
> or
> Select @.e = @.@.Identity
> Option 1 is preferred for a single assignment.
> Also look up scope_identity() in BOL.
>
>|||"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:6E159771-9CB3-45ED-95D1-7EF10E72DFFA@.microsoft.com...
> Thanks. From what I understand Scope_Identity works within a specified
> scope
> which I interpret to mean if you insert a row into tbl1 which contains 10
> rows in one procedure and also insert a row into tbl2 which contains 700
> rows
> in another procedure and you only want to return the Identity value in
> tbl1
> you could use Scope_Identity.
> May I ask how Scope_Identity would be implemented in my SP to return the
> Identity value of the last inserted row into tbl1?
>
set @.a = scope_identity()
scope_identity() has another advantage.
If you have a trigger on a table that inserts a row into another table with
an identity column.
@.@.identity in your stored procedure will return the ID of the last insert,
that is, the one in your trigger.
scope_identity() will return the ID you want.sql

Problem with Select Top ....(incorrect syntax near '@p')

hello,
i want to return a nr of records depending on the parameter @.nrRecords
I tryed to execute this in the queryanalyser - but i get a error, what is
wrong?
Declare @.nrRecords int
SET @.nrRecords=10
SELECT TOP @.p companyName
FROM myTable
the error is
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.nrRecords'.
thanksexamnotes (td1369@.discussions.microsoft.com) writes:
> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
> the error is
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '@.nrRecords'.
In SQL 2000 you must specify a constant with TOP. In SQL 2005 you can
use an expression, but it must be in parens:
SELECT TOP(@.nrRecords)
The best alternative in SQL 2000, is to use SET ROWCOUNT instead:
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
SET ROWCOUNT 0
By the way, TOP or SET ROWCOUNT without an ORDER BY is not really
meaningful, is it is not defined which rows you get.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Da ich sch=E4tze, da=DF Du mit einem SQL Server 2k unterwegs bist, mu=DF
ich Dir leider sagen, da=DF dies die variable TOP Deklaration erst ab
SQL2k5 funtkioniert, anonsten bleibt Dir nur noch Dynamic SQL =FCbrig.
http://www.sommarskog.se/dynamic_sql.html
HTH, jens Suessmeyer.|||Sorry posted in the wrong language:
I assume that your base is a SQL 2k, unfortunately this doesn=B4t work
with this, this declaration only works with SQL2k, therefore onyl
dynamic SQL remains as a solution for you.
http://www.sommarskog.se/dynamic_sql.html=20
HTH, jens Suessmeyer.|||> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
Two problems.
(1) in SQL Server 2000, TOP does not take a parameter.
(2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
meaningless without ORDER BY. SQL Server is free to return the rows in any
order it deems fit, which means your result can change completely from one
execution to the next.
I'll assume you meant to put an ORDER BY clause, and your solution might
look like this:
DECLARE @.nrRecords INT
SET @.nrRecords = 10
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
ORDER BY companyName
SET ROWCOUNT 0|||good idea with the ROWCOUNT ...,
thanks - it works
best resgards
"Aaron Bertrand [SQL Server MVP]" wrote:

> Two problems.
> (1) in SQL Server 2000, TOP does not take a parameter.
> (2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
> meaningless without ORDER BY. SQL Server is free to return the rows in an
y
> order it deems fit, which means your result can change completely from one
> execution to the next.
> I'll assume you meant to put an ORDER BY clause, and your solution might
> look like this:
> DECLARE @.nrRecords INT
> SET @.nrRecords = 10
> SET ROWCOUNT @.nrRecords
> SELECT companyName
> FROM myTable
> ORDER BY companyName
> SET ROWCOUNT 0
>
>

Problem with Returning a recordset from a stored procedure in ADO/VBScript

I'm having trouble getting a recordset out of stored procedure in ADO. The SP executes without errors, but the recordset object I return into is always closed.

Here is my code:
<%
.....
Set cmm = Server.CreateObject("ADODB.Command")
Set cmm.ActiveConnection = Connect
cmm.CommandType = adCmdStoredProc
cmm.CommandText = "dbo.client_updates_proc"
cmm.Parameters.Refresh
cmm.Parameters(1) = client_id
Set logRS = cmm.Execute()

if not logRS.EOF then
.....
%>

My SP has one parameter, which I set above, and it ends with a select statement. When I run the SP in Query Analyzer, it outputs the table of results as is should, but I always get an error on 'if logRS.EOF then', saying that the object is closed.A good place to start looking is the ADO Connection Error collection. Check to see if Connect.Errors.Count > 0. If so, you will probably find your problem there.

Also, you can try adding SET NOCOUNT ON at the beginning of your SP, and SET NOCOUNT OFF at the end, before you return your recordset. Sometimes the command object stops asking for data when it gets the "X records affected" messages.

Finally, if that doesn't work, try being more explicit with your parameter naming. A good (and more readable) approach would be to use the CreateParameter function.

CreateParameter([Name As String], [Type As DataTypeEnum = adEmpty], [Direction As ParameterDirectionEnum = adParamInput], [Size As ADO_LONGPTR], [Value]) As Parameter

Assume your parameter is an INT named @.my_param

cmm.Parameters.Append cmm.CreateParameter("@.my_param",3,1, 4,client_id)

[Note: the values of DataTypeEnum and ParameterDriectionEnum can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaenumnz_2.asp ]

Hope this helps...|||Ahh. Thank you so much. It was the NOCOUNT property.

Problem with return value of stored procedure when using tableadapters

hello

Could you please help me with this problem?

I have a stored procedure like this:

ALTER PROCEDUREdbo.UniqueChannelName

(

@.UserNamenvarchar(50),

@.ChannelNamenvarchar(50)

)

AS

return5;

Then inside of my dataset, I added a new query(dataset1.QueriesTableAdapter) to handle above mentioned stored procedure. Properties window is showing that return type of this adapter is of type int32 as we expected to be.

now I want to use it inside of my code:

DataSet1TableAdapters.QueriesTableAdapter b =new DataSet1TableAdapters.QueriesTableAdapter();

int i;

i=Convert.ToInt32( b.UniqueChannelName("Ahmad","test"));

as you may guess, the return type of b.UniqueChannelName("Ahmad","test") is object and needs to be type-casted before assigning it's value to i; but even after explicit type casting, the value of i is always set to 0, not 5.

could you please show me the way?

many thanks in advance

There is a bug / undocumented feature / downright bad design in ADO.NET whereby a stored procedure with both output parameters and a dataset to return, will not populate the output parameter until all the dataset has been read.

Try reading the dataset to completion before reading the output parameter, otherwise you will have to split your stored procedure into an output parameter part and a dataset part.

|||

Thanks for your fast response.

I'm new to ado.net and couldn't understand what you mean by reading the dataset. Could you please refer me to an example?

thanks

|||

>I'm new to ado.net and couldn't understand what you mean by reading the dataset.

Try assigning the dataset to the control, databind it and then read the output parameter value.

|||

Thanks, Iwill try it tomorrow.

|||

My code is now like this:

DataSet1TableAdapters.QueriesTableAdapter b =new DataSet1TableAdapters.QueriesTableAdapter();

int i;

GridView2.DataSource = b.UniqueChannelName("Ahmad","test");

GridView2.DataBind();

i=Convert.ToInt32( b.UniqueChannelName("Ahmad","test"));

But the problem persists

|||

Please post your stored procedure.

|||ALTER PROCEDURE dbo.UniqueChannelName(@.UserName nvarchar(50),@.ChannelName nvarchar(50))ASdeclare @.Out intSET NOCOUNT ON;return 5;|||

ALTER PROCEDURE dbo.UniqueChannelName

(

@.UserName nvarchar(50),

@.ChannelName nvarchar(50)

)

AS

declare @.Out int

SET NOCOUNT ON;

//return 5;

SET @.OUT=5

// You can get it using AddOutParameter and getOutParameter method

Do not forget to mark as an answer on the post that helped you.

|||

You need to modify your stored procedure to
ALTER PROCEDURE dbo.UniqueChannelName
( @.UserName nvarchar(50),
@.ChannelName nvarchar(50),
@.Out INT OUTPUT -- To get output from a stored procedure parameter you must define it as such
)AS
SET NOCOUNT ON;
SET @.OUT=5

The calling code will need to change

|||

Thanks

Do you mean I must use Database classs and I can't use dataset for my purpose?Infact I prefred using datasets.

Plus

@.out was a local variable inside my code, can it send data out?

Plus

Later I want to change my stored procedure to a select statement like this:

select @.out=count(Autonumber) from ...

what should I do for returning the value?

again thanks for your incorporation

|||

Ok

nowUniqueChannelName method became like this:

UniqueChannelName(string UserName, string ChannelName, ref int? Out)

but unfortunately I don't know how to use the last parameter. could you please help?

thanks

|||

> Plus @.out was a local variable inside my code, can it send data out?

It has to become an output argument of the stored procedure. A local variable is purely local.

For small datasets selected by a SELECT A, B, C FROM FRED, they can be modified to SELECT @.out AS OUT, A, B, C FROM FRED

If the dataset is small the overhead is minimal, otherwise you need to formally define a Command object with all the arguments.

|||

Dear Allstar,

You gave too much of information to me. thanks alot.

I think if you give me a guide on using that reference variable, my questions will be finished.

Any how, thanks alot.

|||I am looking for an example in an old VS2003 project, but the search is taking longer than I anticipated.

Problem With Result Set in SQL Task

Hello,

I have a SQL Task configured to return a single row, and a single column value. The SQL Statement looks like this;

SELECT MAX(InvoiceDate) AS InvoiceDate
FROM dbo.DailySettlementData

The statement parses without a problem. In the 'Result Set' of the SQL Task I have the following;

Result Name; InvoiceDate, Variable Name; LatestTableDate

'LatestTableDate' is of type 'DateTime' (and I'm wondering now if this needs to be of type 'Object')

I need this MAX(InvoiceDate) value in a later step that checks this value against another date type variable.

I'm not getting the result I expect. Do I have the variable for the result set in the SQL Task set up correctly?

Thank you for your help!

cdun2

I assume that the variable name is "User::LatestTableDate", right?

Otherwise that looks fine. It doesn't need to be an object datatype. You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.|||

Phil Brammer wrote:

You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.

One of my variables needed to have 'EvaluateAsExpression' set to True. Now it works fine.

A couple of questions related to working with SSIS in Visual Studio;

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Thank you again for your help.

cdun2

|||

cdun2 wrote:

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Close them before saving the project and closing it. It will open whatever you left open the previous time.|||

cdun2 wrote:

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250573&SiteID=1

Problem with query

Hi,
I have a query that is supposed to return records and make a left join where
one field is not null, but for some reason is not working properly and
returns the records even though they are null.
SELECT *
FROM cases a
left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
MAX(y.Dateinitiated)
From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
Any help is greately appreciated.
AleksAleks,
The reason the query seems to be returnning records from Activities,
where the Dateinitiated column is null, is because you have specified an
Outer Join.
When you specify An Outer Join, Al records from the Outer table are
returned, even when there is no match on the other side. You actually are
NOT returning any data from Activities Table where Dateinitiated column is
null. If you look at those rows, you'll probably notice that all the field
s
from Activities table are null there...
"Aleks" wrote:

> Hi,
> I have a query that is supposed to return records and make a left join whe
re
> one field is not null, but for some reason is not working properly and
> returns the records even though they are null.
> --
> SELECT *
> FROM cases a
> left join activities as w on a.id = w.caseid AND w.Dateinitiated = (Select
> MAX(y.Dateinitiated)
> From Activities y Where y.caseid = a.id AND y.ActType ='HISTORY' and
> y.dateinitiated IS NOT NULL and y.processtep IS NOT NULL)
> --
> Any help is greately appreciated.
> Aleks
>
>

Problem with Query

Hi people, I need your help:

I made a query which should return the values:

Customer's Name (table CUSTOMERS)
Customer's ID (table CUSTOMERS)
Customer's City (table CITY)
Date of Visits to Customer (table VISITS)
Visit's Status (table STATUSVIS) = returns value '0' - if the customer was visited or returns '1' if the customer wasn't at his office.

My problem is: to get the value of the field City I have 2 tables for Customers Addresses, they are CUSTADD1 and CUSTADD2, and my customer can only have one address in one of those tables.

The query I made isn't working and I have no clue how to fix it up.

--------------------------SELECT distinct(VISITS.visdate), CUSTOMERS.customername, CUSTOMERS.customerID, CITY.cityname

FROM
CUSTOMERS, VISITS, STATUSVIS, CUSTADD1, CUSTADD2, CITY
WHERE
CUSTOMERS.customerID in (CUSTADD1.customerID, CUSTADD2.customerID) and
CUSTADD1.cityname = CITY.cityname or
CUSTADD2.cityname = CITY.cityname and
CUSTOMERS.customerID = VISITS.customerID and
STATUSVIS.status = 0
--------------------------

Does someone have an idea about what isn't working in this query?

Thanks in advance,
GiseleYour query is failing because you are mixing AND and OR comparisons in your WHERE clause without defining their order of precedence with parentheses, and becuase you have an unjoined table (STATUSVIS) in your statement.

The query would be a lot clearer if you would establish table relationships with JOIN statements rather than in your WHERE clause. Joining tables in the WHERE clause is a bad habit. Very bad.

Here is your query rewritten using JOINs. Notice how the CITY table isn't necessary. You aren't pulling and data from it that you can't get from the CUSTADD tables. Also, the STATUSVIS table has been dropped because it was not joined in the original query anyway. If you want to add its criteria to the query, join it to one of the existing tables (CUSTOMER?).

The isnull function returns the cityname from CUSTADD1 if it exists, otherwise it returns the value from CUSTADD2. Modify it if you want different selection logic.

Also, I'm not sure if the DISTINCT clause is necessary. I doubt it, but it depends on your data.

Please read up on the SELECT statement in Books Online and familiarize yourself with good coding practices.

SELECT distinct(VISITS.visdate),
CUSTOMERS.customername,
CUSTOMERS.customerID,
isnull(CUSTADD1.cityname, CUSTADD2.cityname) cityname
FROM CUSTOMERS
inner join VISITS on CUSTOMERS.customerID = VISITS.customerID
left outer join CUSTADD1 on CUSTOMERS.customerID = CUSTADD1.customerID
left outer join CUSTADD2 on CUSTOMERS.customerID = CUSTADD2.customerID|||blindman, you gotta start using COALESCE instead of ISNULL, it will go further

gisele, your problems with ANDs and ORs is quite common, i have seen many people write them linearly and expect them to be evaluated linearly

however, they are evaluated as though there were parentheses around any terms connect with AND

for example

... WHERE x=1 AND y=4 OR z=7

will be evaluated as

... WHERE (x=1 AND y=4) OR z=7

ANDs always take precedence over ORs

this type of expression is called a boolean expression, named after Jim Boolean, inventor of the word AND

:cool:|||Now it worked, thanks a lot guys!|||COALESCE vs ISNULL?

What's the diff for two values?

..and the word OR was first coined by George Orwell, who also, by the way, invented the OReo cookie.|||there is no difference in sql server

my advice was meant for sql in general

COALESCE is standard

ISNULL isn't (i think)

besides, then you will never get it mixed up with the NULLIF function

:cool:|||Yes, ISNULL it not ANSI standard, while COALESCE is, so I suppose COALESCE is preferable.