Combining Text and value in Trigger

Hi,

I have written a trigger that emails a specified person.

I am trying to include a body of the email which comprises of the stock level and a warning. Pulling out my hair...Help

Code so far in the trigger is :

CREATE TRIGGER Warnings ON [dbo].[tbl_sql_cartridges_kh]
for update
AS
declare @.SL as int
declare @.SS as int
declare @.Msg as nvarchar(100)
set @.SL= (select stock_level from inserted)
set @.SS =(select cartridge_key from inserted)
set @.Msg = 'Print Cartridges Level Warning'
if @.SL < 3
begin

exec sp_send_cdontsmail 'Print-Cartridges','XXX@.XXXX.co.uk','Print Cartridges Level Warning',@.Msg
end

I would like the @.Msg to say something like Cartridge XXX stock level is YYY, where XXX and YYY are taken from the table after update. I can get the values, but cant put them in the MSG string..

Like @.msg & @.SL (SL being Stock Level)

Many Thanks

KenFirst problem you have is that you are treating the virtual tables as if they have only 1 row...inserted may have n rows, so

set @.SL= (select stock_level from inserted)

Would only return the last results...

Second, sending emails from a trigger is very messy. Why not just do it from a stored procedure? If all the code is isolated to sproc calls then you're golden. If you allow dynamic sql from code, then it's a problem...

As for the email, we a notus lotes so we're hosed here...|||This calls a stored procedure.

The trigger will only ever have 1 row as this Sql dbase has adreamweaver front end that only lets a singke line be updated.

I can grab any items that have been updated, I just cant combine them.

I have made sure all constraints are working..

It actually tells you @.SS will be cartridge HP045a for example and @.SL could 1.

I need the @.msg to say something like Cartridge HP045a stock level is now 1.

The Cdonts procedure is effective and uses SMTP and works well..

Combining strings

Let's assume we have two tables - Customers and Orders.

I need a query that will return a string value containing a list of order titles from the Orders table for a particular customer.

How can this be done?

Thanks.

Hi vkh,

you have to use a function approach for this, as it can be seen on (sort of, I would vary this one to a temporary table rather than a cursor, but just to show you the iterative approach)

http://www.sqlteam.com/item.asp?ItemID=2368

HTH; jens Suessmeyer.

|||Thank you!

Combining Stored Procedures

I have two stored procedures

ALTER PROCEDURE dbo.qryCountOne
(@.inputID int)
AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
(dbo.TableOne.value = @.inputID)

ALTER PROCEDURE dbo.qryCountTwo
(@.inputID int)
AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
(dbo.TableTwo.value = @.inputID)

What would be the best way to combine these two, so that I only have to
make one database query, and the two values (CountOne, and CountTwo)
will get returned to me?

Any help\pointers greatly appreciated,

Noel"Noel" <vbgooglegroups@.yahoo.com> wrote in message
news:1120752722.113719.37280@.g44g2000cwa.googlegro ups.com...
>I have two stored procedures
> ALTER PROCEDURE dbo.qryCountOne
> (@.inputID int)
> AS SELECT COUNT(*) AS CountOne FROM dbo.TableOne WHERE
> (dbo.TableOne.value = @.inputID)
> ALTER PROCEDURE dbo.qryCountTwo
> (@.inputID int)
> AS SELECT COUNT(*) AS CountTwo FROM dbo.TableTwo WHERE
> (dbo.TableTwo.value = @.inputID)
> What would be the best way to combine these two, so that I only have to
> make one database query, and the two values (CountOne, and CountTwo)
> will get returned to me?
>
> Any help\pointers greatly appreciated,
> Noel

Output parameters are usually the best way to return scalar values from a
stored proc, so perhaps something like this?

create proc dbo.GetRowCounts
@.TableOneID int
@.TableOneCount int OUTPUT,
@.TableTwoID int,
@.TableTwoCount int OUTPUT
as
begin
select @.TableOneCount = count(*)
from dbo.TableOne
where col = @.TableOneID

select @.TableTwoCount = count(*)
from dbo.TableTwo
where col = @.TableTwoID
end

If you have to use a result set instead of output parameters, then see
"UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
MSSQL, so if that is the real column name, you might want to consider
changing it if possible - see "Reserved Keywords" in BOL.

Simon|||Simon Hayes (sql@.hayes.ch) writes:
> If you have to use a result set instead of output parameters, then see
> "UNION ALL" in Books Online. By the way, 'value' is a reserved keyword in
> MSSQL, so if that is the real column name, you might want to consider
> changing it if possible - see "Reserved Keywords" in BOL.

It's listed among the "Future keywords". Given the record of SQL Server
I would not hold my breath until all those words become reserved.

T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> T-SQL has this funny notion of unreserved keywords, and they seem to grow in number with every release.<<

They got that idea from ANSI, which has such a list when we were
looking at the SQL3 working draft.|||--CELKO-- (jcelko212@.earthlink.net) writes:
>>> T-SQL has this funny notion of unreserved keywords, and they seem to
grow in number with every release.<<
> They got that idea from ANSI, which has such a list when we were
> looking at the SQL3 working draft.

Nah, I was thinking of things like OUTPUT - which must have been around
since the 80s. OUTPUT is a keyword, but it's not reserved and you
can create a table or a column with that name, without any quoting.

But I assume you were thinking of the list of "Future keywords". That
does indeed seem like an ANSI list.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's great, thanks!

Noel|||Yes, I agree it's unlikely to be a problem, but I generally prefer to
recommend that people follow best practices as documented by Microsoft.
For me, that's a better option than assuming that something has never
been a problem in the past, so it's going to be OK in the future (cf
the short article in this month's SQL Server Magazine on xp_reg% procs
behaviour in SP4).

Simon

Combine text columns

I have 2 text data type columns that I would like to combine into a new column. I'd also like to add a newline character between each column value when I combine them.

I've tried columnA + columnB but that didn't work.

How could I do that?

Hi,

you can do it like this

select columnA + ' ' + columnB as columnAB from tableX

Grz, Kris.

|||

Here's the error that it produces:

Msg 402, Level 16, State 1, Line 1

The data types text and varchar are incompatible in the add operator.

|||

In that case you need to cast the varchar to type text. You can do that by using the Transact-SQL functionCAST.

Grz, Kris.

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Coma separated string value as function parameter

Hi

Let’s say I have employees table that contains id column for the supervisor of the employee.

I need to create a function that gets coma separated string value of the supervisors’ ids,

And return the ids of employees that the ENTIRE listed supervisors are there supervisor.

(some thing like “Select id from employees where supervisor=val_1 and supervisor=val_2 and… and supervisor=val_N)

Is there a way to create this function without using sp_exec?

I’ve created a function that splits the coma separated value to INT table.

(For use in a function that do something like:

“Select id from employees where supervisor in (select val from dbo.SplitToInt(coma_separated_value))

)

Thanks ,

Z

Here it is,

Code Snippet

alter function splittoint(@.values varchar(8000), @.delimiter varchar(10))

returns @.result table (value int)

as

begin

declare @.v as varchar(8000);

while charindex(@.delimiter,@.values) <> 0

begin

set @.v = substring(@.values,1,charindex(@.delimiter,@.values)-1);

if isnumeric(@.v)=1

insert into @.result

values(@.v);

set @.values = substring(@.values,charindex(@.delimiter,@.values)+1,len(@.values))

end

if isnumeric(@.values)=1

insert into @.result

values(@.values);

return;

end

Go

Select * from splitToint('1,2,3,4,56,A',',')

|||

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

|||

Thanks, but it’s not what I meant…

Let me rephrase the question…

Select * from TBL where ID in ([list]) is equal to:

Select * from TBL where ID=val_1 OR ID=val_2 OR … OR ID=val_n

How can I create a query that is equal to:

Select * from TBL where ID=val_1 AND ID=val_2 AND … AND ID=val_n

(without sp_exec !)

Thanks

|||

If your final goal is to create a select statement, then because the list can change, you have use dynamic sql and so sp_executesql or exec('...').

AMB

|||

“in” create a dynamic “OR” query.

There’s no “built in” way to create a dynamic “AND” query?

|||

Yes, it is. Google for "relational division".

select

a.c1

from

dbo.t1 as a

inner join

dbo.ufn_split('1, 3, 4, 5, 8, 9') as b

on a.c2 = b.c1

group by

a.c1

having

count(distinct a.c2) = (select count(distinct c.c1) from dbo.ufn_split('1, 3, 4, 5, 8, 9') as c)

go

AMB

|||Thanks!

Column's default value in 2005

HI,
Dose column's default value can be set as 2 columns' "+, - , *, / " ?
I want ColumnC's default value as ColumnA/ColumnB.
Any idea? Thanks for any advice!
Angi
angi a crit :
> HI,
> Dose column's default value can be set as 2 columns' "+, - , *, / " ?
> I want ColumnC's default value as ColumnA/ColumnB.
> Any idea? Thanks for any advice!
> Angi
>
NO... Default and Not Null constraint can only be set on a single row.
To do that, you must use a trigger :
CREATE TRIGGER <trig_name> ON <table_name>
FOR INSERT
AS
UPDATE <table_name>
SET ColumnC = ColumnA/ColumnB
FROM <table_name> T
INNER JOIN inserted i
ON T.<key_col> = i.<key_col>
WHERE ColumnC IS NULL
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Column's default value in 2005

HI,
Dose column's default value can be set as 2 columns' "+, - , *, / " ?
I want ColumnC's default value as ColumnA/ColumnB.
Any idea? Thanks for any advice!
Angiangi a crit :
> HI,
> Dose column's default value can be set as 2 columns' "+, - , *, / " ?
> I want ColumnC's default value as ColumnA/ColumnB.
> Any idea? Thanks for any advice!
> Angi
>
NO... Default and Not Null constraint can only be set on a single row.
To do that, you must use a trigger :
CREATE TRIGGER <trig_name> ON <table_name>
FOR INSERT
AS
UPDATE <table_name>
SET ColumnC = ColumnA/ColumnB
FROM <table_name> T
INNER JOIN inserted i
ON T.<key_col> = i.<key_col>
WHERE ColumnC IS NULL
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Column's default value in 2005

HI,
Dose column's default value can be set as 2 columns' "+, - , *, / " ?
I want ColumnC's default value as ColumnA/ColumnB.
Any idea? Thanks for any advice!
Angiangi a écrit :
> HI,
> Dose column's default value can be set as 2 columns' "+, - , *, / " ?
> I want ColumnC's default value as ColumnA/ColumnB.
> Any idea? Thanks for any advice!
> Angi
>
NO... Default and Not Null constraint can only be set on a single row.
To do that, you must use a trigger :
CREATE TRIGGER <trig_name> ON <table_name>
FOR INSERT
AS
UPDATE <table_name>
SET ColumnC = ColumnA/ColumnB
FROM <table_name> T
INNER JOIN inserted i
ON T.<key_col> = i.<key_col>
WHERE ColumnC IS NULL
A +
--
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

column-defulat-value as auto-increment?

Thank you very much for the reply!
well, there is no way to update an identity column,
but is there a way to define a default value to a column
that makes auto-increment?
i tried in the SQL-Server-Enterprize-Manager\Design-Table
to set the default value of a column to MAX(recid_)+1...
any more ideas?
Thanks again,
edo.> well, there is no way to update an identity column,
> but is there a way to define a default value to a column
> that makes auto-increment?
> i tried in the SQL-Server-Enterprize-Manager\Design-Table
> to set the default value of a column to MAX(recid_)+1...
> any more ideas?
You could do this in a trigger, but maybe it might make more sense to
explain why you need this behavior.|||i made this trigger:
CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
FOR INSERT
AS
declare @.i1 int
declare c1 cursor for select Max(recid) from jobs
open c1
fetch next from c1 into @.i1
close c1
deallocate c1
update T1 set recid_=@.i1+1 where recid is NULL
do you think it's the most effecient way?
i doubt because i wonder first, wether i must open a
cursor, and second wether there is no direct way to
update the current inserted record, ruther than
search "where recid is NULL"
thank,
edo.
>--Original Message--
>> well, there is no way to update an identity column,
>> but is there a way to define a default value to a
column
>> that makes auto-increment?
>> i tried in the SQL-Server-Enterprize-Manager\Design-
Table
>> to set the default value of a column to MAX(recid_)
+1...
>> any more ideas?
>You could do this in a trigger, but maybe it might make
more sense to
>explain why you need this behavior.
>
>.
>|||You don't need a cursor:
SET @.i = (SELECT MAX(recid) FROM jobs)
Also, you use the INSERTED table to get the modified row(s).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"edo" <ewilde@.nana.co.il> wrote in message news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
> i made this trigger:
> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
> FOR INSERT
> AS
> declare @.i1 int
> declare c1 cursor for select Max(recid) from jobs
> open c1
> fetch next from c1 into @.i1
> close c1
> deallocate c1
> update T1 set recid_=@.i1+1 where recid is NULL
>
> do you think it's the most effecient way?
> i doubt because i wonder first, wether i must open a
> cursor, and second wether there is no direct way to
> update the current inserted record, ruther than
> search "where recid is NULL"
> thank,
> edo.
> >--Original Message--
> >> well, there is no way to update an identity column,
> >> but is there a way to define a default value to a
> column
> >> that makes auto-increment?
> >>
> >> i tried in the SQL-Server-Enterprize-Manager\Design-
> Table
> >> to set the default value of a column to MAX(recid_)
> +1...
> >> any more ideas?
> >
> >You could do this in a trigger, but maybe it might make
> more sense to
> >explain why you need this behavior.
> >
> >
> >.
> >|||Thanks for your helped,
i implemented your suggestion about the
SET @.i = (SELECT MAX(recid) FROM T1)
but i tried somthing like:
update inserted set recid=1
and got an error:
"the logical tables INSERTED and DELETED can not be
updated."
?
thanks agian,
edo.
>--Original Message--
>You don't need a cursor:
>SET @.i = (SELECT MAX(recid) FROM jobs)
>Also, you use the INSERTED table to get the modified row
(s).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
>"edo" <ewilde@.nana.co.il> wrote in message
news:0afe01c36d21$31eab6c0$a101280a@.phx.gbl...
>> i made this trigger:
>> CREATE TRIGGER trg_auto_inc ON [dbo].[T1]
>> FOR INSERT
>> AS
>> declare @.i1 int
>> declare c1 cursor for select Max(recid) from jobs
>> open c1
>> fetch next from c1 into @.i1
>> close c1
>> deallocate c1
>> update T1 set recid_=@.i1+1 where recid is NULL
>>
>> do you think it's the most effecient way?
>> i doubt because i wonder first, wether i must open a
>> cursor, and second wether there is no direct way to
>> update the current inserted record, ruther than
>> search "where recid is NULL"
>> thank,
>> edo.
>> >--Original Message--
>> >> well, there is no way to update an identity column,
>> >> but is there a way to define a default value to a
>> column
>> >> that makes auto-increment?
>> >>
>> >> i tried in the SQL-Server-Enterprize-Manager\Design-
>> Table
>> >> to set the default value of a column to MAX(recid_)
>> +1...
>> >> any more ideas?
>> >
>> >You could do this in a trigger, but maybe it might
make
>> more sense to
>> >explain why you need this behavior.
>> >
>> >
>> >.
>> >
>
>.
>|||> update inserted set recid=1
You can't update the inserted / deleted tables.
Maybe you could show your table structure, sample data, and the results you
are trying to achieve, rather than have us reverse engineer your existing
trigger code. It might be that a trigger isn't even necessary for this, or
it might be that you could approach the trigger in a completely different
way. Your narrative a few posts back is difficult to follow, but might be
easier to understand if you show us your actual schema design. There might
be a much more efficient approach to whatever it is you mean by "cloning"...

Column with Select Statement

Hi, i have a doubt, can a column have the value of a select? I mean, i'm making a photo gallery and on the categories table i need to know how many photos i have, so i need to count in the table photos the ones associated with the id of the category, the problem is that i'm listing categories with a datalist, is there a way so that a column on the categories table have the result of the count?

Thanks in advance, if you don't understood my question feel free to ask me again and i'll try to explain it better, i really need this.

No, as you're seeking for a computed column, and a computed column can only be derived from other columns in the same table, so you can't add a column to the Categories table which stores the count from the Photoes table. Maybe you cancreate a view which contains the count of photoes as well as other information you need in your application, and then query on the view:

CREATE VIEW CategoryPhotoCnt AS
SELECT COUNT(PhotoID) AS PhotoCnt,c.CategoryID,c.CategoryName
FROM Categories c join Photos p ON p.CID = c.CategoryID
GROUP BY c.CategoryID,c.CategoryName

Column width

i am using reporting services 2000 & my backend is sql 2000.
In one of my reports, i have a parameter which needs to take value more than
1400 characters but in reporting services report view i can only give 1200
characters. Kindly let me know how do i resolve this issue.WOW! A parameter with 1400 characters? Where does that value come
from? Probably not from user input I'll bet.
Anyway, if the SSRS max is 1200, I doubt there's going to be a way to
expand that. Alternatively, you're going to have to look at
abbreviating the param value. Obvious I know, but without more info,
it's going to be hard to offer helpful suggestions. Post some more
details please and we can give it a shot.
toolman
Hasan Dalwai wrote:
> i am using reporting services 2000 & my backend is sql 2000.
> In one of my reports, i have a parameter which needs to take value more than
> 1400 characters but in reporting services report view i can only give 1200
> characters. Kindly let me know how do i resolve this issue.

Column Value Lookup

What is the best way to lookup a value in a column from another data set?
I am trying to locate and display the employee name based on the employee id
found in another data set.
I.E.
13 = Terry Ward
14 = Peter Jackson
Thank you.I might be wrong, but I don't think you can do that, unfortunately.
You need to make the EmployeeID a parameter that you can use to query your
employee database table for names.
What are you trying to do? Might be a different way of doing it.
Kaisa M. Lindahl Lervik
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> What is the best way to lookup a value in a column from another data set?
> I am trying to locate and display the employee name based on the employee
> id
> found in another data set.
> I.E.
> 13 = Terry Ward
> 14 = Peter Jackson
> Thank you.|||In the Task table, employees are identified as numeric values. References to
the actual employee names are found in the Employee table.
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To =dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
"Kaisa M. Lindahl Lervik" wrote:
> I might be wrong, but I don't think you can do that, unfortunately.
> You need to make the EmployeeID a parameter that you can use to query your
> employee database table for names.
> What are you trying to do? Might be a different way of doing it.
> Kaisa M. Lindahl Lervik
> "Terry" <Terry@.discussions.microsoft.com> wrote in message
> news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > What is the best way to lookup a value in a column from another data set?
> >
> > I am trying to locate and display the employee name based on the employee
> > id
> > found in another data set.
> >
> > I.E.
> > 13 = Terry Ward
> > 14 = Peter Jackson
> >
> > Thank you.
>
>|||Can you join the Task table to the emloyee table similar to the way the
tblIssue table is joined to the tblEmployee table is below to return the
employee name?
e.g. Something like this...
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Terry" wrote:
> In the Task table, employees are identified as numeric values. References to
> the actual employee names are found in the Employee table.
> SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> dbo.tblIssue.Opened_By,
> dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> dbo.tblIssue.Closed_Date
> FROM dbo.tblIssue
> INNER JOIN
> dbo.tblEmployee ON dbo.tblIssue.Assigned_To => dbo.tblEmployee.Employee_ID
> INNER JOIN
> dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> "Kaisa M. Lindahl Lervik" wrote:
> > I might be wrong, but I don't think you can do that, unfortunately.
> > You need to make the EmployeeID a parameter that you can use to query your
> > employee database table for names.
> >
> > What are you trying to do? Might be a different way of doing it.
> >
> > Kaisa M. Lindahl Lervik
> > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > What is the best way to lookup a value in a column from another data set?
> > >
> > > I am trying to locate and display the employee name based on the employee
> > > id
> > > found in another data set.
> > >
> > > I.E.
> > > 13 = Terry Ward
> > > 14 = Peter Jackson
> > >
> > > Thank you.
> >
> >
> >|||Do I need to create a new dataset and include the employee name in the report
in order to obtain the employee name along with the existing dataset called
IT_Projects?
DATASET 1:
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To = dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
DATASET 2:
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Matt" wrote:
> Can you join the Task table to the emloyee table similar to the way the
> tblIssue table is joined to the tblEmployee table is below to return the
> employee name?
> e.g. Something like this...
> SELECT dbo.tblEmployee.Name
> FROM tblTask
> JOIN tblEmployee
> ON tblTask.Employee_ID = tblEmployee.Employee_ID
> "Terry" wrote:
> > In the Task table, employees are identified as numeric values. References to
> > the actual employee names are found in the Employee table.
> >
> > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > dbo.tblIssue.Opened_By,
> > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > dbo.tblIssue.Closed_Date
> > FROM dbo.tblIssue
> > INNER JOIN
> > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > dbo.tblEmployee.Employee_ID
> > INNER JOIN
> > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> > > I might be wrong, but I don't think you can do that, unfortunately.
> > > You need to make the EmployeeID a parameter that you can use to query your
> > > employee database table for names.
> > >
> > > What are you trying to do? Might be a different way of doing it.
> > >
> > > Kaisa M. Lindahl Lervik
> > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > What is the best way to lookup a value in a column from another data set?
> > > >
> > > > I am trying to locate and display the employee name based on the employee
> > > > id
> > > > found in another data set.
> > > >
> > > > I.E.
> > > > 13 = Terry Ward
> > > > 14 = Peter Jackson
> > > >
> > > > Thank you.
> > >
> > >
> > >|||Ok, it sounds you are"...trying to locate and display the employee name based
on the employee id found in another data set."
So, it sounds like you have 1 dataset that does not return the employee name
and another dataset that does return the employee name. Instead of trying to
perform a lookup between the 2 datasets that you have, the best thing to do
would be to modify the query that currently does not include the employee
name to include the employee name in the select statement. Does that make
more sense?
If you want to post the 2 dataset queries that you have, that may help me
understand your situation better.
"Terry" wrote:
> Do I need to create a new dataset and include the employee name in the report
> in order to obtain the employee name along with the existing dataset called
> IT_Projects?
> DATASET 1:
> SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> dbo.tblIssue.Opened_By,
> dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> dbo.tblIssue.Closed_Date
> FROM dbo.tblIssue
> INNER JOIN
> dbo.tblEmployee ON dbo.tblIssue.Assigned_To => dbo.tblEmployee.Employee_ID
> INNER JOIN
> dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> DATASET 2:
> SELECT dbo.tblEmployee.Name
> FROM tblTask
> JOIN tblEmployee
> ON tblTask.Employee_ID = tblEmployee.Employee_ID
> "Matt" wrote:
> > Can you join the Task table to the emloyee table similar to the way the
> > tblIssue table is joined to the tblEmployee table is below to return the
> > employee name?
> >
> > e.g. Something like this...
> > SELECT dbo.tblEmployee.Name
> > FROM tblTask
> > JOIN tblEmployee
> > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> >
> > "Terry" wrote:
> >
> > > In the Task table, employees are identified as numeric values. References to
> > > the actual employee names are found in the Employee table.
> > >
> > > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > > dbo.tblIssue.Opened_By,
> > > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > > dbo.tblIssue.Closed_Date
> > > FROM dbo.tblIssue
> > > INNER JOIN
> > > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > > dbo.tblEmployee.Employee_ID
> > > INNER JOIN
> > > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> > >
> > > "Kaisa M. Lindahl Lervik" wrote:
> > >
> > > > I might be wrong, but I don't think you can do that, unfortunately.
> > > > You need to make the EmployeeID a parameter that you can use to query your
> > > > employee database table for names.
> > > >
> > > > What are you trying to do? Might be a different way of doing it.
> > > >
> > > > Kaisa M. Lindahl Lervik
> > > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > > What is the best way to lookup a value in a column from another data set?
> > > > >
> > > > > I am trying to locate and display the employee name based on the employee
> > > > > id
> > > > > found in another data set.
> > > > >
> > > > > I.E.
> > > > > 13 = Terry Ward
> > > > > 14 = Peter Jackson
> > > > >
> > > > > Thank you.
> > > >
> > > >
> > > >|||Thank you for your speedy response.
However, please review the following 2 dataset queries being used.
How can I include the employee name without causes JOIN conflicts?
DATASET 1:
SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
dbo.tblIssue.Opened_By,
dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
dbo.tblIssue.Closed_Date
FROM dbo.tblIssue
INNER JOIN
dbo.tblEmployee ON dbo.tblIssue.Assigned_To = dbo.tblEmployee.Employee_ID
INNER JOIN
dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
DATASET 2:
SELECT dbo.tblEmployee.Name
FROM tblTask
JOIN tblEmployee
ON tblTask.Employee_ID = tblEmployee.Employee_ID
"Matt" wrote:
> Ok, it sounds you are"...trying to locate and display the employee name based
> on the employee id found in another data set."
> So, it sounds like you have 1 dataset that does not return the employee name
> and another dataset that does return the employee name. Instead of trying to
> perform a lookup between the 2 datasets that you have, the best thing to do
> would be to modify the query that currently does not include the employee
> name to include the employee name in the select statement. Does that make
> more sense?
> If you want to post the 2 dataset queries that you have, that may help me
> understand your situation better.
> "Terry" wrote:
> > Do I need to create a new dataset and include the employee name in the report
> > in order to obtain the employee name along with the existing dataset called
> > IT_Projects?
> >
> > DATASET 1:
> >
> > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > dbo.tblIssue.Opened_By,
> > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > dbo.tblIssue.Closed_Date
> > FROM dbo.tblIssue
> > INNER JOIN
> > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > dbo.tblEmployee.Employee_ID
> > INNER JOIN
> > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> >
> > DATASET 2:
> >
> > SELECT dbo.tblEmployee.Name
> > FROM tblTask
> > JOIN tblEmployee
> > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> >
> > "Matt" wrote:
> >
> > > Can you join the Task table to the emloyee table similar to the way the
> > > tblIssue table is joined to the tblEmployee table is below to return the
> > > employee name?
> > >
> > > e.g. Something like this...
> > > SELECT dbo.tblEmployee.Name
> > > FROM tblTask
> > > JOIN tblEmployee
> > > ON tblTask.Employee_ID = tblEmployee.Employee_ID
> > >
> > > "Terry" wrote:
> > >
> > > > In the Task table, employees are identified as numeric values. References to
> > > > the actual employee names are found in the Employee table.
> > > >
> > > > SELECT dbo.tblIssue.Opened_Date, dbo.tblEmployee.Name,
> > > > dbo.tblIssue.Opened_By,
> > > > dbo.tblIssue.Issue_Summary, dbo.tblIssue.Issue_Description,
> > > > dbo.tblIssue.Targeted_Date, dbo.tblIssue.Status,
> > > > dbo.tblIssue.Closed_Date
> > > > FROM dbo.tblIssue
> > > > INNER JOIN
> > > > dbo.tblEmployee ON dbo.tblIssue.Assigned_To => > > > dbo.tblEmployee.Employee_ID
> > > > INNER JOIN
> > > > dbo.tblStatus ON dbo.tblIssue.Status = dbo.tblStatus.Status_Name
> > > >
> > > > "Kaisa M. Lindahl Lervik" wrote:
> > > >
> > > > > I might be wrong, but I don't think you can do that, unfortunately.
> > > > > You need to make the EmployeeID a parameter that you can use to query your
> > > > > employee database table for names.
> > > > >
> > > > > What are you trying to do? Might be a different way of doing it.
> > > > >
> > > > > Kaisa M. Lindahl Lervik
> > > > > "Terry" <Terry@.discussions.microsoft.com> wrote in message
> > > > > news:E1879358-1C7F-48C7-96DF-C0D5DF641886@.microsoft.com...
> > > > > > What is the best way to lookup a value in a column from another data set?
> > > > > >
> > > > > > I am trying to locate and display the employee name based on the employee
> > > > > > id
> > > > > > found in another data set.
> > > > > >
> > > > > > I.E.
> > > > > > 13 = Terry Ward
> > > > > > 14 = Peter Jackson
> > > > > >
> > > > > > Thank you.
> > > > >
> > > > >
> > > > >

Column Properties Window Bug in Management Studio?

This issue deals with where to see the default value for a column in Sql Server 2005 Management Studio.

I have a default value of (1) in a column of type bit:

If you right click on the column in Management Studio object explorer and choose properties, the default value does not appear under "Default Binding" or "Default Schema" in the Column Properties window.

If you select the table and then choose View -> Summary, double click on the Columns folder, then right click -> properties on the column, again the default value does not appear under "Default Binding" or "Default Schema" in the Column Properties window.

If you right click on the table or column and choose "Modify", then select the column, the default value appears in "Default Value or Binding".

If you script the table out (right click -> Script Table as -> Create to), the default value for that column appears in the script.

I'm wondering if this is by design or a bug? Obviously right click-> properties is not a reliable way to view the properties on a column.

Hello Eric,

This issue seems to be a bug in Column Properties page. Could you please report this issue through Connect web site

https://connect.microsoft.com/SQLServer/Feedback

Click on Submit Feedback

Thanks

Sethu Srinivasan

SQL Server Manageability Dev Team

Column is constrained to be unique. Value 123 already

I'm using sqlDataAdpater to call a stored procedure for insert
statements.
The stored procedure inserts into all columns except primary key, which
it then selects back at end.
The program does a number of these inserts and then reads back from
database, this all works okay. Later when I go to do more inserts I
get the error message
Column is constrained to be unique. Value 123 already
Below is the stored procedure.
Any help would be greatly appreciated.
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int =null output, @.SelectionOrig_ID int =null,
@.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
=null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
=null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
@.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
)
as
insert into [dbo].[Selection]
(
SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
string2, string3, string4, string5, string6, string7, Hidden, NameLong,
Team_ID, Percentage
)
values
(
@.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
@.Hidden, @.NameLong, @.Team_ID, @.Percentage
)
select * From Selection Where Selection_ID = @.@.IDENTITY
GOChange to this:
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- --
select @.Selection_ID Selection_ID
Return(0)
-- ---
but also consider the following, you can use the SP to do both Inserts and
Updates, switching based on whether or not you pass in a null or non-null
@.Selection_ID as follows
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
-- ---
This has the benefiy of ONLY Updating the columns for which you actually
pass non-null values to the SP.
"dermot" wrote:

> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>
"dermot" wrote:

> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Missed one word in Raiserror statement
CREATE procedure [dbo].[prcSelectionInsert]
(
@.Selection_ID int = null output,
@.SelectionOrig_ID int =null,
@.SelectionIndex int =null,
@.Event_ID int =null,
@.Name varchar(25)=null,
@.Type varchar(50) =null,
@.Odd varchar(8) =null,
@.string1 varchar(50) =null,
@.string2 varchar(50) =null,
@.string3 varchar(50) =null,
@.string4 varchar(50) =null,
@.string5 varchar(50) =null,
@.string6 varchar(50) =null,
@.string7 varchar(50) =null,
@.Hidden bit =null,
@.NameLong varchar(50) =null,
@.Team_ID int = null,
@.Percentage int =null
)
As
If @.Selection_ID Is Null
Insert [dbo].[Selection]
(SelectionOrig_ID, SelectionIndex,
Event_ID, Name, Type, Odd,
string1,string2, string3, string4,
string5, string6, string7, Hidden,
NameLong,Team_ID, Percentage)
Values (@.SelectionOrig_ID, @.SelectionIndex,
@.Event_ID, @.Name, @.Type, @.Odd,
@.string1, @.string2, @.string3, @.string4,
@.string5, @.string6, @.string7,@.Hidden,
@.NameLong, @.Team_ID, @.Percentage)
-- --
Set @.Selection_ID = Scope_Identity()
-- ---
Else If Exists (Select * From [dbo].[Selection]
Where Selection_ID = @.Selection_ID)
Update [dbo].[Selection] Set
SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
Event_ID = IsNull(@.Event_ID, Event_ID),
Name = IsNull(@.Name, Name),
Type = IsNull(@.Type, Type),
Odd = IsNull(@.Odd, Odd)
string1 = IsNull(@.string1, string1),
string2 = IsNull(@.string2, string2),
string3 = IsNull(@.string3, string3),
string4 = IsNull(@.string4, string4),
string5 = IsNull(@.string5, string5),
string6 = IsNull(@.string6, string6),
string7 = IsNull(@.string7, string7),
Hidden = IsNull(@.Hidden, Hidden),
NameLong = IsNull(@.NameLong, NameLong),
Team_ID = IsNull(@.Team_ID, Team_ID),
Percentage = IsNull(@.Percentage, Percentage)
Where Selection_ID = @.Selection_ID
-- ---
Else
Begin
Raiserror('Someone has deleted or removed Selection %d.',
16,1,@.Selection_ID)
Return(-1)
End
Select @.Selection_ID Selection_ID
Return(0)
"CBretana" wrote:
> Change to this:
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- --
> select @.Selection_ID Selection_ID
> Return(0)
> -- ---
> but also consider the following, you can use the SP to do both Inserts and
> Updates, switching based on whether or not you pass in a null or non-null
> @.Selection_ID as follows
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int = null output,
> @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null,
> @.Event_ID int =null,
> @.Name varchar(25)=null,
> @.Type varchar(50) =null,
> @.Odd varchar(8) =null,
> @.string1 varchar(50) =null,
> @.string2 varchar(50) =null,
> @.string3 varchar(50) =null,
> @.string4 varchar(50) =null,
> @.string5 varchar(50) =null,
> @.string6 varchar(50) =null,
> @.string7 varchar(50) =null,
> @.Hidden bit =null,
> @.NameLong varchar(50) =null,
> @.Team_ID int = null,
> @.Percentage int =null
> )
> As
> If @.Selection_ID Is Null
> Insert [dbo].[Selection]
> (SelectionOrig_ID, SelectionIndex,
> Event_ID, Name, Type, Odd,
> string1,string2, string3, string4,
> string5, string6, string7, Hidden,
> NameLong,Team_ID, Percentage)
> Values (@.SelectionOrig_ID, @.SelectionIndex,
> @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4,
> @.string5, @.string6, @.string7,@.Hidden,
> @.NameLong, @.Team_ID, @.Percentage)
> -- --
> Set @.Selection_ID = Scope_Identity()
> -- ---
> Else If Exists (Select * From [dbo].[Selection]
> Where Selection_ID = @.Selection_ID)
> Update [dbo].[Selection] Set
> SelectionOrig_ID = IsNull(@.SelectionOrig_ID, SelectionOrig_ID),
> SelectionIndex = IsNull(@.SelectionIndex, SelectionIndex),
> Event_ID = IsNull(@.Event_ID, Event_ID),
> Name = IsNull(@.Name, Name),
> Type = IsNull(@.Type, Type),
> Odd = IsNull(@.Odd, Odd)
> string1 = IsNull(@.string1, string1),
> string2 = IsNull(@.string2, string2),
> string3 = IsNull(@.string3, string3),
> string4 = IsNull(@.string4, string4),
> string5 = IsNull(@.string5, string5),
> string6 = IsNull(@.string6, string6),
> string7 = IsNull(@.string7, string7),
> Hidden = IsNull(@.Hidden, Hidden),
> NameLong = IsNull(@.NameLong, NameLong),
> Team_ID = IsNull(@.Team_ID, Team_ID),
> Percentage = IsNull(@.Percentage, Percentage)
> Where Selection_ID = @.Selection_ID
> -- ---
> Else
> Begin
> Raiserror('Someone has Selection %d.', 16,1,@.Selection_ID)
> Return(-1)
> End
>
> Select @.Selection_ID Selection_ID
>
> Return(0)
> -- ---
> This has the benefiy of ONLY Updating the columns for which you actually
> pass non-null values to the SP.
> "dermot" wrote:
>
> "dermot" wrote:
>|||Script out the entire table if you could, just to make sure. I assume there
is no triggers, and that you know where the value 123 comes from?
If you can repeat the operation, use profiler and capture a trace of it
failing. Post that and we can see.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"dermot" <dfrench@.tommyfrench.co.uk> wrote in message
news:1109971489.057346.206560@.l41g2000cwc.googlegroups.com...
> I'm using sqlDataAdpater to call a stored procedure for insert
> statements.
> The stored procedure inserts into all columns except primary key, which
> it then selects back at end.
> The program does a number of these inserts and then reads back from
> database, this all works okay. Later when I go to do more inserts I
> get the error message
> Column is constrained to be unique. Value 123 already
> Below is the stored procedure.
> Any help would be greatly appreciated.
> CREATE procedure [dbo].[prcSelectionInsert]
> (
> @.Selection_ID int =null output, @.SelectionOrig_ID int =null,
> @.SelectionIndex int =null, @.Event_ID int =null, @.Name varchar(25)
> =null, @.Type varchar(50) =null, @.Odd varchar(8) =null, @.string1
> varchar(50) =null, @.string2 varchar(50) =null, @.string3 varchar(50)
> =null, @.string4 varchar(50) =null, @.string5 varchar(50) =null, @.string6
> varchar(50) =null, @.string7 varchar(50) =null, @.Hidden bit =null,
> @.NameLong varchar(50) =null, @.Team_ID int =null, @.Percentage int =null
> )
> as
> insert into [dbo].[Selection]
> (
> SelectionOrig_ID, SelectionIndex, Event_ID, Name, Type, Odd, string1,
> string2, string3, string4, string5, string6, string7, Hidden, NameLong,
> Team_ID, Percentage
> )
> values
> (
> @.SelectionOrig_ID, @.SelectionIndex, @.Event_ID, @.Name, @.Type, @.Odd,
> @.string1, @.string2, @.string3, @.string4, @.string5, @.string6, @.string7,
> @.Hidden, @.NameLong, @.Team_ID, @.Percentage
> )
> select * From Selection Where Selection_ID = @.@.IDENTITY
> GO
>|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.|||Folks,
Many thanks for the help after doing the trace I have found the problem
and have been able to sort out.

Column Invisibility

Hey,

I am retrieving values from a database I have setup in sql server. Basically I want to make a column invisible if the value is null.

Here is the code I tried, but it didn't work.

=IIf(Fields!FundingCode.Valueisnothing,True, False)

I would greatly appreciate the help

a column of what? Datagrid, Gridview? What?

Column in profiler that shows no. of rows returned

I want to see for every stored procedure that completes, how many rows were
returned as output ? Is there a column in profiler that shows that value or
is there any other way to get it as opposed to running every query manually
on my Management Studio session.
Using SQL 2005.
Thanks
Found the answer.. Its the rowcounts column.
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>
|||Hi Hassan
There is nothing to show the rows for the entire procedure, since one
procedure can do lots of different things. However, if you also capture
SP:StmtCompleted, the IntegerData column will show the rows affected for
each statement within your stored procedure.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>
|||Wow... I'd never noticed that column before. I've always just used
IntegerData.
However, it appears that Rowcounts is for all rows affected, not just rows
returned. If your procedure does any data modification operations, the rows
changed will be included in the rowcounts.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%23vIphkZLIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Found the answer.. Its the rowcounts column.
> "Hassan" <hassan@.test.com> wrote in message
> news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>

Column in profiler that shows no. of rows returned

I want to see for every stored procedure that completes, how many rows were
returned as output ? Is there a column in profiler that shows that value or
is there any other way to get it as opposed to running every query manually
on my Management Studio session.
Using SQL 2005.
ThanksFound the answer.. Its the rowcounts column.
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Hi Hassan
There is nothing to show the rows for the entire procedure, since one
procedure can do lots of different things. However, if you also capture
SP:StmtCompleted, the IntegerData column will show the rows affected for
each statement within your stored procedure.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Wow... I'd never noticed that column before. I've always just used
IntegerData.
However, it appears that Rowcounts is for all rows affected, not just rows
returned. If your procedure does any data modification operations, the rows
changed will be included in the rowcounts.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%23vIphkZLIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Found the answer.. Its the rowcounts column.
> "Hassan" <hassan@.test.com> wrote in message
> news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>

Column in profiler that shows no. of rows returned

I want to see for every stored procedure that completes, how many rows were
returned as output ? Is there a column in profiler that shows that value or
is there any other way to get it as opposed to running every query manually
on my Management Studio session.
Using SQL 2005.
ThanksFound the answer.. Its the rowcounts column.
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Hi Hassan
There is nothing to show the rows for the entire procedure, since one
procedure can do lots of different things. However, if you also capture
SP:StmtCompleted, the IntegerData column will show the rows affected for
each statement within your stored procedure.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>I want to see for every stored procedure that completes, how many rows were
>returned as output ? Is there a column in profiler that shows that value or
>is there any other way to get it as opposed to running every query manually
>on my Management Studio session.
> Using SQL 2005.
> Thanks
>|||Wow... I'd never noticed that column before. I've always just used
IntegerData.
However, it appears that Rowcounts is for all rows affected, not just rows
returned. If your procedure does any data modification operations, the rows
changed will be included in the rowcounts.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.test.com> wrote in message
news:%23vIphkZLIHA.3940@.TK2MSFTNGP05.phx.gbl...
> Found the answer.. Its the rowcounts column.
> "Hassan" <hassan@.test.com> wrote in message
> news:%2326rYcZLIHA.5224@.TK2MSFTNGP02.phx.gbl...
>>I want to see for every stored procedure that completes, how many rows
>>were returned as output ? Is there a column in profiler that shows that
>>value or is there any other way to get it as opposed to running every
>>query manually on my Management Studio session.
>> Using SQL 2005.
>> Thanks
>