how to exec a stored procedure

hi,

how do I exec stored procedure that accept parameter and return a single value?

here is example of report

stu_id = ******

stu_name = ****

subject | marks

aa****** | call sp_mark and return student mark for that particular student id and subject

bb****** | call sp_mark and return student mark for that particular student id and subject

cc****** | call sp_mark and return student mark for that particular student id and subject

thks,

You cannot call a stored procedure per row if you mean that with your mentioned design, you would have to get all the information within one procedure to display it in the bound table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Charles,

Have you tried using a user defined function in place of the stored procedure?

Simone

|||

A potentially better performing alternative to a user defined function would probably be a derived table containing the marks for each student by subject. You would then join on the table.

Something like

select stu_id, stu_name, subject, mark

from students s

left outer join (select stu_id, subject, marks from marks) m on m.stu_id = s.stu_id

Of course you would need to summarize the marks into a table...

cheers,

Andrew

How to exclude zero values when sorting?

I have a datagrid with a "sort" field I want to use to sort the rows in ascending order. However, I want values with a 0 or NULL value to be displayed last. I can't figure out how to do a sort (preferably in the SQL) that returns the empty values last. Is this possible?This isn't pretty but why don't you give it a go:
SELECT * FROM mytable WHERE myfield > 0 ORDER BY myfield ASC
UNION SELECT * FROM mytable WHERE myfield = 0

Regards
Fredr!k|||Unfortunately, this isn't valid syntax becuase ORDER BY must be at the end of the query. I get "Incorrect syntax near the keyword 'UNION'." when I try

SELECT * FROM Photo WHERE PhotoOrder > 0 ORDER BY PhotoOrder ASC UNION SELECT * FROM Photo WHERE PhotoOrder = 0|||To use the UNION operator in SQL Server all your Data types must be the same and the same order in both tables, but UNION is restrictive because it performs an Implicit DISTINCT by eliminating DUPLICATES. So if eliminating duplicates is not important try UNION ALL, if it still fails the it is INNER JOIN if both tables are equal or OUTER JOIN if they are not equal. Hope this helps.

Kind regards,
Gift Peddie|||I'm confused - how is this relevant to my question?|||I got what I wanted with
"ORDER BY IsNull(PhotoORDER, 1000)"

Unfortunately, zeroes will still sort first, but I can NULLify them on data entry|||You might try:

ORDER BY
 CASE WHEN ISNULL(PhotoOrder,0) = 0 THEN 2 ELSE 1 END,
 CASE WHEN ISNULL(PhotoOrder,0) <> 0 THEN PhotoOrder

Terri|||I was only replying your UNION error not your original post. I will try to be clear in the future.

Kind regards,
Gift Peddiesql

How to evaluate sum on change of group?

I have a data set that is grouped based on 2 fields, but the value of the set
that I want to add by Group 1 is the same data that repeats for the first
group.
Example:
Value Grp1 Grp2
================= 100 1 1
100 1 2
100 1 3
200 2 1
200 2 2
200 2 3
I want to get a total of Value, but only evaluate the total when Grp1
changes. Currently, when I use the Sum function, it adds all values, giving
me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
In Crystal Reports, there was a method to evaluate a sum only on the change
of a particular group. Is there some similar method in Reporting Services to
achieve this?It is available in SSRS as well but you need to try it and see how far you
can use this solutions. it goes like this.
= RunningValue(Fields!field1.Value, Sum, <groupname>) so it evaluates to tat
particular group or the scope.
Amarnath
"Ben Shaffer" wrote:
> I have a data set that is grouped based on 2 fields, but the value of the set
> that I want to add by Group 1 is the same data that repeats for the first
> group.
> Example:
> Value Grp1 Grp2
> =================> 100 1 1
> 100 1 2
> 100 1 3
> 200 2 1
> 200 2 2
> 200 2 3
>
> I want to get a total of Value, but only evaluate the total when Grp1
> changes. Currently, when I use the Sum function, it adds all values, giving
> me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> In Crystal Reports, there was a method to evaluate a sum only on the change
> of a particular group. Is there some similar method in Reporting Services to
> achieve this?
>|||Ben,
You could also use grouping on the report. You could put a group sum in
the group header or footer, and then a grand total or a count of the
groups in the table footer.
When you use a normal sum function in a group, it sums only the group.
-Josh
Ben Shaffer wrote:
> I have a data set that is grouped based on 2 fields, but the value of the set
> that I want to add by Group 1 is the same data that repeats for the first
> group.
> Example:
> Value Grp1 Grp2
> =================> 100 1 1
> 100 1 2
> 100 1 3
> 200 2 1
> 200 2 2
> 200 2 3
>
> I want to get a total of Value, but only evaluate the total when Grp1
> changes. Currently, when I use the Sum function, it adds all values, giving
> me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> In Crystal Reports, there was a method to evaluate a sum only on the change
> of a particular group. Is there some similar method in Reporting Services to
> achieve this?|||Thanks for the reply. I've tried using the scope parameter of the aggregate
function, but get report compilation errors when I do so. What you've
suggested with the "RunningValue" function is essentially the same as using
the Sum function.
I oversimplified my data set to really show my problem. Here's a slightly
different version:
field1 Grp1 Grp2
=================100 1 1
100 1 1
100 1 1
100 1 2
100 1 3
On the footer for group 2, I can just show the most recent value of field1.
i.e. when grp2 changes, I just display =Fields!field1.Value instead of a sum
to get the value that I need to display.
However, when I try to create a Sum of the 1st field on the footer for group
1, I get the total of all values of field1.
i.e. =Sum(Fields!field1.Value) on the footer for group 1 yields a value of
500. What I want to get is for the sum to evaluate only when group 2
changes, to yield a value of 300.
I have tried using the scope parameter to the sum function to do this.
i.e. =Sum(Fields!field1.Value, "Grp2")
When I try this, I get a report compile error telling me that:
"The value expression for textbox 'x' has a scope parameter that is not
valid for an aggregate function. The scope parameter must be set to a string
constant that is equal to either the name of a containing group, the name of
a containing data region, or the name of a data set."
What I understand of the error message is that I can't set the scope of the
sum function to be based on a group that group 1 contains. That it has to be
set to a group that contains group 1 instead.
Am I using scope incorrectly? If this is the way that scope functions, then
the "scope" of the aggregate function must control when the running total
resets to zero, rather than controlling when the running total gets evaluated
(which is the functionality that I *need*).
It doesn't make sense that I wouldn't have this capability with MS Reporting
Services, as lesser reporting tools (such as Crystal and R&R) all provided
this kind of functionality with running totals in reports.
Further suggestions would be hugely appreciated.
"Amarnath" wrote:
> It is available in SSRS as well but you need to try it and see how far you
> can use this solutions. it goes like this.
> = RunningValue(Fields!field1.Value, Sum, <groupname>) so it evaluates to tat
> particular group or the scope.
> Amarnath
> "Ben Shaffer" wrote:
> > I have a data set that is grouped based on 2 fields, but the value of the set
> > that I want to add by Group 1 is the same data that repeats for the first
> > group.
> > Example:
> > Value Grp1 Grp2
> > =================> > 100 1 1
> > 100 1 2
> > 100 1 3
> > 200 2 1
> > 200 2 2
> > 200 2 3
> >
> >
> > I want to get a total of Value, but only evaluate the total when Grp1
> > changes. Currently, when I use the Sum function, it adds all values, giving
> > me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> > and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> >
> > In Crystal Reports, there was a method to evaluate a sum only on the change
> > of a particular group. Is there some similar method in Reporting Services to
> > achieve this?
> >|||Thanks for your reply, but I've already done this. I've better explained my
situation in a reply to Amarnath above. Further help in regard to that post
would be greatly appreciated. Thanks :)
"Josh" wrote:
> Ben,
> You could also use grouping on the report. You could put a group sum in
> the group header or footer, and then a grand total or a count of the
> groups in the table footer.
> When you use a normal sum function in a group, it sums only the group.
> -Josh|||This might be a dumb question, but I have to ask...
You said:
However, when I try to create a Sum of the 1st field on the footer for
group
1, I get the total of all values of field1.
i.e. =Sum(Fields!field1.Value) on the footer for group 1 yields a value
of
500. What I want to get is for the sum to evaluate only when group 2
changes, to yield a value of 300.
You are trying to sum Group 2 to get a sum of 300, but you are summing
in the footer of group 1 and getting 500. Can't you just sum in the
group 2 footer? That would give you 300, and you would only get the sum
(group footer) every time group 2 changes...
-Josh
Ben Shaffer wrote:
> Thanks for the reply. I've tried using the scope parameter of the aggregate
> function, but get report compilation errors when I do so. What you've
> suggested with the "RunningValue" function is essentially the same as using
> the Sum function.
> I oversimplified my data set to really show my problem. Here's a slightly
> different version:
> field1 Grp1 Grp2
> =================> 100 1 1
> 100 1 1
> 100 1 1
> 100 1 2
> 100 1 3
> On the footer for group 2, I can just show the most recent value of field1.
> i.e. when grp2 changes, I just display =Fields!field1.Value instead of a sum
> to get the value that I need to display.
> However, when I try to create a Sum of the 1st field on the footer for group
> 1, I get the total of all values of field1.
> i.e. =Sum(Fields!field1.Value) on the footer for group 1 yields a value of
> 500. What I want to get is for the sum to evaluate only when group 2
> changes, to yield a value of 300.
> I have tried using the scope parameter to the sum function to do this.
> i.e. =Sum(Fields!field1.Value, "Grp2")
> When I try this, I get a report compile error telling me that:
> "The value expression for textbox 'x' has a scope parameter that is not
> valid for an aggregate function. The scope parameter must be set to a string
> constant that is equal to either the name of a containing group, the name of
> a containing data region, or the name of a data set."
> What I understand of the error message is that I can't set the scope of the
> sum function to be based on a group that group 1 contains. That it has to be
> set to a group that contains group 1 instead.
> Am I using scope incorrectly? If this is the way that scope functions, then
> the "scope" of the aggregate function must control when the running total
> resets to zero, rather than controlling when the running total gets evaluated
> (which is the functionality that I *need*).
> It doesn't make sense that I wouldn't have this capability with MS Reporting
> Services, as lesser reporting tools (such as Crystal and R&R) all provided
> this kind of functionality with running totals in reports.
> Further suggestions would be hugely appreciated.
> "Amarnath" wrote:
> > It is available in SSRS as well but you need to try it and see how far you
> > can use this solutions. it goes like this.
> >
> > = RunningValue(Fields!field1.Value, Sum, <groupname>) so it evaluates to tat
> > particular group or the scope.
> >
> > Amarnath
> >
> > "Ben Shaffer" wrote:
> >
> > > I have a data set that is grouped based on 2 fields, but the value of the set
> > > that I want to add by Group 1 is the same data that repeats for the first
> > > group.
> > > Example:
> > > Value Grp1 Grp2
> > > =================> > > 100 1 1
> > > 100 1 2
> > > 100 1 3
> > > 200 2 1
> > > 200 2 2
> > > 200 2 3
> > >
> > >
> > > I want to get a total of Value, but only evaluate the total when Grp1
> > > changes. Currently, when I use the Sum function, it adds all values, giving
> > > me a total of 900. I need a way to take the value of '100' when Grp1 = 100,
> > > and add it to the value of '200' when Grp1 = 2 to give me a total of 300.
> > >
> > > In Crystal Reports, there was a method to evaluate a sum only on the change
> > > of a particular group. Is there some similar method in Reporting Services to
> > > achieve this?
> > >

How to enter manually a "uniqueIdentifier" type value

I created a DB and a table in Visual Studio .NET 2003. For one of the column
I choose the data type uniqueidentifier.
How can I add manually a unique ID value in the table value editor?
OK, figured it out: select newID() as the default value.
"Frank" <someone@.nospam.com> wrote in message
news:c5f6j4$dpe$1@.news.mch.sbs.de...
> I created a DB and a table in Visual Studio .NET 2003. For one of the
column
> I choose the data type uniqueidentifier.
> How can I add manually a unique ID value in the table value editor?
>

How to enter a date parameter in Debugger

(This is prob. a really dumb question but it's driving me mad!!...)

I am using the Debugger in SQL Query Analyzer & want to set the value of a datetime parameter prior to executing the stored proc. The "Debug procedure" window allows me to specify the parameter values - but I can't get it to accept a datetime. The language is us_english & I've tried most ways if specifying the date - 01/02/2004, with/out quotes, 02 Jan 2004, as a full datetime, swapping day/month values etc etc. The procedure always fails immediately with: Invalid character value for cast specification.

Thanks.Got it ... finally!

yyyy-mm-dd

Argggggh.

how to ensure unique value over multiple columns

I'm looking for a way to ensure a value of a column in an insert or update is unique over multiple columns. For example, in this table
accounts
id varchar(32)
readkey varchar(16)
writekey varchar(16)
I want to ensure that when a row of accounts is inserted or updated that the union of all readkey values and writekey values contains no duplicates.

I know some "hard" ways to do this (like a second table of all keys, or a trigger that tests new readkey values against all readkey and writekey values, and likewise new writekey values against all readkey and writekey values, and so on). But I'm betting that savvy SQL folks know a better way. In case it matters, I'm using IBM DB2 8.1.

Thanks
Billcreate a composite key with unique attribute.
not sure if it will work in DB2 though|||Thanks but that doesn't do it. I'm not trying to ensure that no combination of readkey || writekey ever occurs twice. I need to ensure that no readkey is the same as any other readkey or writekey, and no other writekey is the same as any other readkey or writekey.

Bill|||I don't know DB2. In standard SQL you can create a constraint something like:

ALTER TABLE accounts a1
ADD CONSTRAINT c1
CHECK (NOT EXISTS (SELECT NULL FROM accounts a2 WHERE a2.readkey = a1.writekey));

That, along with UNIQUE constraints on the 2 columns, would do it.

Alternatively, perhaps a Materialized View based on:

SELECT 'R' AS mode, readkey AS key FROM accounts
UNION
SELECT 'W' AS mode, writekey AS key FROM accounts

... with a unique constraint on (key).|||Thanks for the suggestions. I couldn't make either work, but the ideas in them provided a way.

The CHECK constraint was my first idea, but I learned that CHECK constraints cannot depend on values from more than one row, so the SELECT which examines the whole table will not work.

The materialized view was a good idea but doesn't work, since materialized views do not permit UNION - the query has to be a subset query.

What I did get to work was to create a (regular) VIEW using the UNION roughly as you suggested, then create triggers for insert and update that throw an error if the key already exists in the union view. The view and two triggers is more complex than I hoped, but at this point I'm happy just to have a solution.

Thanks again for the help.|||In DB2 v8 you can use a sequence object for this purpose.

How to ensure column uniqueness

Hi,
Coming from an Oracle background, I'm used to being able
to create a unique key on a column that allows many null
values, ie if a value exists it must be unique, otherwise
it can be null.
It appears as though SQL Server allows only 1 null value
in the same situation, ie create a unique constraint on a
column and once you try to insert a 2nd row with a null
value I get a constraint violation. [Thx to those that
replied to my last question on this]
I don't really want to write triggers testing for such
conditions. Is there some form of constraint that can do
this for me?
TIA,
SJTYour observations are correct. You can create a view conatining all rows but the NULL. Then create a
unique index (or possible a unique constraint) on that index.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"SJT" <scott.taylor@.pwcs.com.au> wrote in message news:054601c36ad9$46dd1f90$a501280a@.phx.gbl...
> Hi,
> Coming from an Oracle background, I'm used to being able
> to create a unique key on a column that allows many null
> values, ie if a value exists it must be unique, otherwise
> it can be null.
> It appears as though SQL Server allows only 1 null value
> in the same situation, ie create a unique constraint on a
> column and once you try to insert a 2nd row with a null
> value I get a constraint violation. [Thx to those that
> replied to my last question on this]
> I don't really want to write triggers testing for such
> conditions. Is there some form of constraint that can do
> this for me?
>
> TIA,
> SJT|||You can use an indexed view to enforce uniqueness only for non-NULL values:
CREATE TABLE Sometable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL)
GO
CREATE VIEW Sometable_Unique_Non_NULL
WITH SCHEMABINDING
AS SELECT colx FROM dbo.Sometable WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON Sometable_Unique_Non_NULL (colx)
INSERT INTO Sometable VALUES (1,1)
INSERT INTO Sometable VALUES (2,NULL)
INSERT INTO Sometable VALUES (3,NULL)
--
David Portas
--
Please reply only to the newsgroup
--

How to eliminate the NULL field values

I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?Check 'allow nulls' and 'default value' settings in the access tables.|||and to solve for the export problem...

Use a query to export the table, and use
Select ISNULL(yourfield,"") AS FIELD1...sql

How to eliminate the commas from the end in sql query when the column value is null

HI

I have three different columns as email1,email2 , email3.I am concatinating these columns into one i.e EMail like

select ISNULL(dbo.tblperson.Email1, N'') +';'+ISNULL(dbo.tblperson.Email2, N'') +';'+ISNULL(dbo.tblperson.Email3, N'')ASEmail from tablename.

One eg of the output of the above query when email2,email3 are having null values in the table is :

jacky_foo@.mfa.gov.sg;;

means it is inserting semicoluns whenever there is a null value in the particular column. I want to remove this extra semicolumn whenever there is null value in the column.

Please let me know how can i do this

If you just change SQL a bit you have the answer, see below

select ISNULL(dbo.tblperson.Email1+ ';', N'') + ISNULL(dbo.tblperson.Email2 + ';', N'') + ISNULL(dbo.tblperson.Email3 + ';', N'')ASEmail from tablename.

|||

I tried this it worked a bit but not completely.Now I am getting the semicolumn at the end if there is null for the third column or you can say for the last column.

|||

There is probably a quick easy way to do it, but this will work:

select ISNULL(dbo.tblperson.Email1, N'') + ISNULL(CASE WHEN dbo.tblperson.Email1 IS NOT NULL THEN ';' ELSE '' END+dbo.tblperson.Email2, N'') + ISNULL(CASE WHEN dbo.tblperson.Email1 IS NOT NULL OR dbo.tblperson.Email2 IS NOT NULL THEN ';' ELSE '' END+dbo.tblperson.Email3, N'')ASEmail from tablename.

|||

Another way is to first normalize your data:

SELECT Email1 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email2 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email3 As Email FROM dbo.tblPerson

Now use the normalized data in a query like:

DECLARE @.Email varchar(max)

SELECT @.Email=ISNULL(@.Email+';','')+Email

FROM (

SELECT Email1 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email2 As Email FROM dbo.tblPerson
UNION ALL
SELECT Email3 As Email FROM dbo.tblPerson

) t1

SELECT @.Email AS Email

|||

Another way is to use one of the many string concatenation techniques once your data has been normalized.

There is a CONCATENATE aggregation function that you can install that will do the trick. (Microsoft supplies one somewhere, google "T-SQL string concatenation aggregate").

There is another technique using the FOR XML/PATH to do the same thing, but it's also kind of messy.

|||

I appreciate your reply. This query worked for me.

How to eliminate duplicate data

I have a table with 68 columns. If all the columns hold the same value except for one which is a datetime column I want to delete all but one of the duplicate rows. Preferably the latest one but that is not important. Can someone show me how to accomplish this?

You can use the Group By fucntion

SELECT col_a, col_b, col_c From Table GROUP BY col_a, col_b, col_c

If you want the newest date - you can also using HAVING Clause

SELECT col_a, col_b, col_c From Table GROUP BY col_a, col_b, col_c HAVING max(col_date)

WHERE col_a - col_c are the columns with the same data and col_date is your date column

AWAL

|||Is this a easier process if I manually delete them? I just want to view the duplicate data but my problem is that the DateTime column in my table is unique unlike all the other columns with the same values.|||

John -

I'm not quite certain what you mean but if your datetime column is not unique but you want dups of all the other columns just don't group by the datetime column.

AWAL

|||

Assuming that your data isn't too big, you can use a technique like this:

drop table removeDups
go
create table removeDups(
column1 int,
column2 int,
column3 int,
column4 int,
column5 int,
column6 int,
column7 int,
column8 int,
column9 int,
datevalue datetime)

insert removeDups
select 1,1,1,1,1,1,1,1,1,getdate()
waitfor delay '00:00:01'
insert removeDups
select 1,1,1,1,1,1,1,1,1,getdate()
waitfor delay '00:00:01'
insert removeDups
select 1,1,1,1,1,1,1,1,1,getdate()
waitfor delay '00:00:01'

insert removeDups
select 2,2,2,2,2,2,2,2,2,getdate()
waitfor delay '00:00:01'
insert removeDups
select 2,2,2,2,2,2,2,2,2,getdate()
waitfor delay '00:00:01'
insert removeDups
select 3,3,3,3,3,3,3,3,3,getdate()
waitfor delay '00:00:01'

delete from removeDups
where not exists
(select *
from ( select min(dateValue) as dateValue,column1,column2,column3,column4,column5,column6, column7, column8, column9
from removeDups
group by column1,column2,column3,column4,column5,column6, column7, column8, column9) as mins
where mins.dateValue = removeDups.dateValue
and mins.column1 = removeDups.column1
and mins.column2 = removeDups.column2
and mins.column3 = removeDups.column3
and mins.column4 = removeDups.column4
and mins.column5 = removeDups.column5
and mins.column6 = removeDups.column6
and mins.column7 = removeDups.column7
and mins.column8 = removeDups.column8
and mins.column9 = removeDups.column9)

select *
from removeDups

I figure once you finish this process you will probably want to hurt the person who gave you this design, even if it is yourself. Try to identify a key amongst the 68 columns and add a unique constraint so you can never get in this position again :)

How to dublicate a row ?

I am transferring data from one database to another directly with some minor changes.

But in one step i need to dublicate a row (if a column value satisfies a condition). How can i achieve this without using "Multicast-Conditional" split (which requires dublication of all rows and then condition proceeds!). By the way the reason i don't want to use Multicast is the table i am processing has about 20 columns and 20 M rows :(

Thanks in advance !

You could use an asynchronous Script Transform. In an asynchronous transform your are responsible for reading the input buffer rows and adding them to the output buffer, so you can do this conditionally, one or more times for each input row. They are a bit painful I find as you have to manually define the output columns in the output buffer by hand which can be tedious.

Perhaps you should reconsider your reluctance to use the Multicast. It does not immediately copy all data, consuming twice the memory. Data is only duplicated as and when required. Up and till that point it uses a pointer like behaviour to reference the existing data. The cost will only come when you force a change in the buffer. I would think that this would not as costly as you may first think. Why not try both methods of a smaller (narrower) dataset, faster to develop a test case.

Creating an Asynchronous Transformation with the Script Component
(http://msdn2.microsoft.com/en-us/library/0d814404-21e4-4a68-894c-96fa47ab25ae.aspx)

How to drop default value Constrain in query?

What's query dropping Default value constraint from a column?I'd use ALTER TABLE DROP CONSTRAINT (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp) myself.

-PatP

How to DROP DEFAULT constraint?

We initially created a table (SQL Server 2000) having a column with a
DEFAULT value of 0. We want to drop the column, but we cannot until the
DEFAULT constraint is removed. Documentation indicates we should be able to
use the command:
"ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
but it gives us a syntax error.
Other documentation indicates the "DROP DEFAULT" option is deprecated and we
shouldn't use it.
Can anyone tell me how to do this?Hi
ALTER TABLE...DROP CONSTRAINT......
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
> We initially created a table (SQL Server 2000) having a column with a
> DEFAULT value of 0. We want to drop the column, but we cannot until the
> DEFAULT constraint is removed. Documentation indicates we should be able
> to use the command:
> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
> but it gives us a syntax error.
> Other documentation indicates the "DROP DEFAULT" option is deprecated and
> we shouldn't use it.
> Can anyone tell me how to do this?
>|||Thanks Uri...
Unfortunately, when the DEFAULT contraint was added, it wasn't given a name:
"ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
Apparently, the system created its own named constraint which I found in
sysobjects. Am I stuck querying the sysobjects table for the name of the
constraint (based on the table/column names) and then using the retrieved
name in the DROP CONSTRAINT clause?
...mcs
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi
> ALTER TABLE...DROP CONSTRAINT......
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
>> We initially created a table (SQL Server 2000) having a column with a
>> DEFAULT value of 0. We want to drop the column, but we cannot until the
>> DEFAULT constraint is removed. Documentation indicates we should be able
>> to use the command:
>> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
>> but it gives us a syntax error.
>> Other documentation indicates the "DROP DEFAULT" option is deprecated and
>> we shouldn't use it.
>> Can anyone tell me how to do this?
>|||Hi
Yes , run sp_helpconstraint 'tablename'
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri...
> Unfortunately, when the DEFAULT contraint was added, it wasn't given a
> name:
> "ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
> Apparently, the system created its own named constraint which I found in
> sysobjects. Am I stuck querying the sysobjects table for the name of the
> constraint (based on the table/column names) and then using the retrieved
> name in the DROP CONSTRAINT clause?
> ...mcs
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> ALTER TABLE...DROP CONSTRAINT......
>> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
>> news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
>> We initially created a table (SQL Server 2000) having a column with a
>> DEFAULT value of 0. We want to drop the column, but we cannot until the
>> DEFAULT constraint is removed. Documentation indicates we should be able
>> to use the command:
>> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
>> but it gives us a syntax error.
>> Other documentation indicates the "DROP DEFAULT" option is deprecated
>> and we shouldn't use it.
>> Can anyone tell me how to do this?
>>
>|||Okay, thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uWngbCEoGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes , run sp_helpconstraint 'tablename'
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
>> Thanks Uri...
>> Unfortunately, when the DEFAULT contraint was added, it wasn't given a
>> name:
>> "ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
>> Apparently, the system created its own named constraint which I found in
>> sysobjects. Am I stuck querying the sysobjects table for the name of the
>> constraint (based on the table/column names) and then using the retrieved
>> name in the DROP CONSTRAINT clause?
>> ...mcs
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> ALTER TABLE...DROP CONSTRAINT......
>> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
>> news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
>> We initially created a table (SQL Server 2000) having a column with a
>> DEFAULT value of 0. We want to drop the column, but we cannot until
>> the DEFAULT constraint is removed. Documentation indicates we should be
>> able to use the command:
>> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
>> but it gives us a syntax error.
>> Other documentation indicates the "DROP DEFAULT" option is deprecated
>> and we shouldn't use it.
>> Can anyone tell me how to do this?
>>
>>
>

How to DROP DEFAULT constraint?

We initially created a table (SQL Server 2000) having a column with a
DEFAULT value of 0. We want to drop the column, but we cannot until the
DEFAULT constraint is removed. Documentation indicates we should be able to
use the command:
"ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
but it gives us a syntax error.
Other documentation indicates the "DROP DEFAULT" option is deprecated and we
shouldn't use it.
Can anyone tell me how to do this?Hi
ALTER TABLE...DROP CONSTRAINT......
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
> We initially created a table (SQL Server 2000) having a column with a
> DEFAULT value of 0. We want to drop the column, but we cannot until the
> DEFAULT constraint is removed. Documentation indicates we should be able
> to use the command:
> "ALTER TABLE MyTable ALTER COLUMN MyColumn DROP DEFAULT"
> but it gives us a syntax error.
> Other documentation indicates the "DROP DEFAULT" option is deprecated and
> we shouldn't use it.
> Can anyone tell me how to do this?
>|||Thanks Uri...
Unfortunately, when the DEFAULT contraint was added, it wasn't given a name:
"ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
Apparently, the system created its own named constraint which I found in
sysobjects. Am I stuck querying the sysobjects table for the name of the
constraint (based on the table/column names) and then using the retrieved
name in the DROP CONSTRAINT clause?
...mcs
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
> Hi
> ALTER TABLE...DROP CONSTRAINT......
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:u5Q2lzDoGHA.1244@.TK2MSFTNGP05.phx.gbl...
>|||Hi
Yes , run sp_helpconstraint 'tablename'
"Meade Swenson" <mswenson@.e-specs.com> wrote in message
news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
> Thanks Uri...
> Unfortunately, when the DEFAULT contraint was added, it wasn't given a
> name:
> "ALTER TABLE MyTable ADD MyColumn INT DEFAULT 0"
> Apparently, the system created its own named constraint which I found in
> sysobjects. Am I stuck querying the sysobjects table for the name of the
> constraint (based on the table/column names) and then using the retrieved
> name in the DROP CONSTRAINT clause?
> ...mcs
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uvG7W4DoGHA.4124@.TK2MSFTNGP03.phx.gbl...
>|||Okay, thanks.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uWngbCEoGHA.4464@.TK2MSFTNGP04.phx.gbl...
> Hi
> Yes , run sp_helpconstraint 'tablename'
> "Meade Swenson" <mswenson@.e-specs.com> wrote in message
> news:OFRZtAEoGHA.4616@.TK2MSFTNGP05.phx.gbl...
>

How to drop data value filed in Report Builder "Chart"

Report Builder (SQL 2005 September CTP) does not allow me to drop a field into the "Drag and Drop Data Value" box.

Are there any restrictions on the data type for the value series?

BTW, I love the tool!

Data values (Y-values) in charts should be numeric fields.

-- Robert

How to do this?

I need to select the row that has the highest value for a particular field.

For example, lets say we have an employee table, and an employee can appear more than 1 time because an employee has several job functions:

Emp_Id 1
Function Clerk

Emp_Id 1
Function Manager

I need to select the row with the highest function, in this case manager( because m > c)

How can i do this, lets say the table name is Employee.SELECT MAX(Emp_id)?|||Thanks :)

This is the query that I got.

SELECT EMP_ID, MAX(FUNCTION)
GROUP BY EMP_ID