Anyone here familiar with stored procedures?

Hi there! I would like to return a default status value (-101) if the -100 and 200 conditions are not met. Is there any way I can go about it?

ALTER PROCEDURE spServiceFormGet (@.TicketNo int, @.Name char(50))
AS
IF NOT EXISTS (SELECT SolutionID FROM ServiceForm where SolutionID = @.TicketNo)
RETURN -100
IF EXISTS (SELECT SolutionID, SolvedBy FROM ServiceForm
WHERE SolutionID = @.TicketNo AND SolvedBy LIKE @.Name
OR SolutionID = @.TicketNo AND SolvedBy IS NULL)
SELECT * FROM serviceform where SolutionID=@.TicketNo
RETURN 200
ELSE <-- something like that
RETURN -101 <-- something like that

Thanks,
-Gabian-Gabian,
Do a google search on MSSQL Output Variables.

You should return a @.outputStatus variable here.

ScAndal|||I am not sure I understand what a return code is buying you in this case - Is there more to the proc than you are posting?

If there is no solutionid matching the ticket (first test) than you know there will be no solutionid matching a ticket AND a name...

Why not just execute the select with the (ticketid, name) filter and interogate the result set for records. If the count = 0 - you know there are no solutionid's matching this ticketno and/or name ??|||Thanks for answering guys, I've got it!

-Gabian-sql

anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
Paul G
Software engineer.
On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:

>I have a query that contains a sub query. The problem is that the sub query
>returns more than 1 value so it fails with the error (Subquery returned more
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column 3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.
|||thanks that is what I was looking for!
Paul G
Software engineer.
"JXStern" wrote:

> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>

anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
--
Paul G
Software engineer.On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:
>I have a query that contains a sub query. The problem is that the sub query
>returns more than 1 value so it fails with the error (Subquery returned more
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column 3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.|||thanks that is what I was looking for!
--
Paul G
Software engineer.
"JXStern" wrote:
> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >I have a query that contains a sub query. The problem is that the sub query
> >returns more than 1 value so it fails with the error (Subquery returned more
> >than 1 value). Here is a simplified version of how it is setup.
> >
> >Select * from table1 where column2 = (select col2 from table2 where column 3
> >=4)
> >So for the example below it should return rows 1 and 3 from table 1 since a
> >and c were returned in the subquery.
> >
> >table 1
> >col1 col2
> >1 a
> >2 b
> >3 c
> >
> >table 2
> >col1 col2 col3
> >1 a 4
> >2 b 2
> >3 c 4
> >thanks.
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>

anyone good with SQL querries?

I have a query that contains a sub query. The problem is that the sub query
returns more than 1 value so it fails with the error (Subquery returned more
than 1 value). Here is a simplified version of how it is setup.
Select * from table1 where column2 = (select col2 from table2 where column 3
=4)
So for the example below it should return rows 1 and 3 from table 1 since a
and c were returned in the subquery.
table 1
col1 col2
1 a
2 b
3 c
table 2
col1 col2 col3
1 a 4
2 b 2
3 c 4
thanks.
--
Paul G
Software engineer.On Tue, 30 Jan 2007 16:35:00 -0800, Paul
<Paul@.discussions.microsoft.com> wrote:

>I have a query that contains a sub query. The problem is that the sub quer
y
>returns more than 1 value so it fails with the error (Subquery returned mor
e
>than 1 value). Here is a simplified version of how it is setup.
>Select * from table1 where column2 = (select col2 from table2 where column
3
>=4)
>So for the example below it should return rows 1 and 3 from table 1 since a
>and c were returned in the subquery.
>table 1
>col1 col2
>1 a
>2 b
>3 c
>table 2
>col1 col2 col3
>1 a 4
>2 b 2
>3 c 4
>thanks.
Select * from table1 where column2 in
(select col2 from table2 where column 3 >=4)
J.|||thanks that is what I was looking for!
--
Paul G
Software engineer.
"JXStern" wrote:

> On Tue, 30 Jan 2007 16:35:00 -0800, Paul
> <Paul@.discussions.microsoft.com> wrote:
>
> Select * from table1 where column2 in
> (select col2 from table2 where column 3 >=4)
>
> J.
>
>sql

any way to check if all the values in a column returned are the same value?

say i have a table, and in it are two columns, column1 and column2 and i do the following query:

SELECT column1, column2 FROM table WHERE column2 = '12345'

i want to check if column1 has all the same values, so in the first case, no

column1 column2
--- ---
4 12345
9 12345
5 12345

column1 column2
--- ---
9 12345
9 12345
9 12345

in the 2nd case, column1 contains all the same values, so yes

is there anyway i can check this? i would be doing this in a trigger.. say when a new row is inserted, the value of column1 is inserted, but col 2 is null.. so when they try to fill in the value for col2 of that row, the trigger checks to see if the value they put for col 2 is already in the table.. if it isn't, then everything is ok. but if it is already in teh table, then it checks col1 to see if all the values of col1 are the same

i hope this makes sense

thanksWithin your trigger, you may determine the min() and max() value of Column1 regarding a certain value for column 2:

SELECT min(Column1), max(column1)
Into Min1, Max1
FROM <YourTable>
WHERE Column2 = <Your current value>
GROUP BY Column2

If Min1=Max1, all values are the same.|||SELECT COUNT(NumValues) FROM (
SELECT COUNT(*) AS NumValues FROM MyTable
WHERE Column2 = MyValue
GROUP BY Column1) AS TempCount;

Any value in defraging a SAN drive?

Are there any quantitative findings published that show there is any merit i
n
defragging a SQL device on a SAN?Steve Clark wrote:
> Are there any quantitative findings published that show there is any
> merit in defragging a SQL device on a SAN?
I would check with your SAN vendor about this. However, quoted from this
MS page:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"On large-scale environments that benefit from more intelligent disk
subsystems, such as SAN (storage area networks) environments, correcting
disk fragmentation is not necessary."
In any case, your vendor should provide you with recommendations.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Any value in defraging a SAN drive?

Are there any quantitative findings published that show there is any merit in
defragging a SQL device on a SAN?Steve Clark wrote:
> Are there any quantitative findings published that show there is any
> merit in defragging a SQL device on a SAN?
I would check with your SAN vendor about this. However, quoted from this
MS page:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
"On large-scale environments that benefit from more intelligent disk
subsystems, such as SAN (storage area networks) environments, correcting
disk fragmentation is not necessary."
In any case, your vendor should provide you with recommendations.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Any value in defraging a SAN drive?

Are there any quantitative findings published that show there is any merit in
defragging a SQL device on a SAN?
Steve Clark wrote:
> Are there any quantitative findings published that show there is any
> merit in defragging a SQL device on a SAN?
I would check with your SAN vendor about this. However, quoted from this
MS page:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
"On large-scale environments that benefit from more intelligent disk
subsystems, such as SAN (storage area networks) environments, correcting
disk fragmentation is not necessary."
In any case, your vendor should provide you with recommendations.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Any there nicer sentence?

If I execute like this
update tcoba
set jml=5,total=jml*5000
So value of field "total" is null. In fact I wish to be its value become
25000.
may be, Any there nicer sentence to solve problem? If can, just one
statement. But not like it:
update tcoba
set jml=5,total=5*5000Since you know that you are setting jml to 5, why can't you use 5 instead of
jml while setting the value for total?
"Use total=5*5000" instead of "total=jml*5000"
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:e5qYjv2lFHA.576@.TK2MSFTNGP15.phx.gbl...
If I execute like this
update tcoba
set jml=5,total=jml*5000
So value of field "total" is null. In fact I wish to be its value become
25000.
may be, Any there nicer sentence to solve problem? If can, just one
statement. But not like it:
update tcoba
set jml=5,total=5*5000|||In the query below, [total] will be multiplied by the original value of
[jml], not the new value of 5. One option is to set a variable to the value
of 5. For example:
declare @.x as int
set @.x = 5
update tcoba set jml=@.x,total=@.x*5000
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:e5qYjv2lFHA.576@.TK2MSFTNGP15.phx.gbl...
> If I execute like this
> update tcoba
> set jml=5,total=jml*5000
> So value of field "total" is null. In fact I wish to be its value become
> 25000.
> may be, Any there nicer sentence to solve problem? If can, just one
> statement. But not like it:
> update tcoba
> set jml=5,total=5*5000
>|||the result you are getting in your column "Total" is because your
column contains NULL initially, and though you have placed jml for
updation before the total, it won't actually update jml first and
update Total after it. But it is good example to know how Update query
works!|||The UPDATE assigns a whole row at a time; it does not work left to
right like a procedural language. Google some of my old posting about
the semantics of UPDATE.

Any quick way to find middle value out of 3?

Please help
declare @.t table (id1 int not null, id2 int not null, id3 int null)
insert @.t values (1,2,null) -- middle null
insert @.t values (10,20,1) -- middle 10
insert @.t values (11,23,12) -- middle 12
insert @.t values (20,100,123) -- middle 100
select *, middle(ID1,id2,id3) --< middle out of 3
FROM @.t
Thank you!There are probably better ways, but this should work.
You could do the same thing using BETWEEN but I prefer to avoid using
BETWEEN.
I assumed that the values could be the same between 2 or 3 of the columns
SELECT ID1, ID2, ID3,
CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
ELSE ID3 END as Middle
FROM @.t
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Farmer" wrote:

> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>
>|||One additional note, this would get a bit uglier if id1 or id2 could also be
null. I was able to cheat a bit since only id3 was allowed to be null, and
you seemed to want the null value for your middle value.
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Ryan Powers" wrote:
> There are probably better ways, but this should work.
> You could do the same thing using BETWEEN but I prefer to avoid using
> BETWEEN.
> I assumed that the values could be the same between 2 or 3 of the columns
> SELECT ID1, ID2, ID3,
> CASE WHEN ID1 >= ID2 AND ID1 <=ID3 THEN ID1
> WHEN ID1 >= ID3 AND ID1 <=ID2 THEN ID1
> WHEN ID2 >= ID1 AND ID2 <=ID3 THEN ID2
> WHEN ID2 >= ID3 AND ID2 <=ID1 THEN ID2
> ELSE ID3 END as Middle
> FROM @.t
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Farmer" wrote:
>|||using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 Middle(SqlInt32 i1, SqlInt32 i2, SqlInt32 i3)
{
if (i1 < i2)
{
if (i3 < i1)
return i1;
if (i3 > i2)
return i2;
return i3;
}
if (i1 > i2)
{
if (i3 > i1)
return i1;
if (i3 < i2)
return i2;
return i3;
}
if (i3 <= i1)
return i1;
return i2;
}
};
William Stacey [MVP]
"Farmer" <someone@.somewhere.com> wrote in message
news:eFURSHkFGHA.1816@.TK2MSFTNGP11.phx.gbl...
> Please help
> declare @.t table (id1 int not null, id2 int not null, id3 int null)
> insert @.t values (1,2,null) -- middle null
> insert @.t values (10,20,1) -- middle 10
> insert @.t values (11,23,12) -- middle 12
> insert @.t values (20,100,123) -- middle 100
>
> select *, middle(ID1,id2,id3) --< middle out of 3
> FROM @.t
>
> Thank you!
>

Any one tell export Sql Database in access hole object

hi All
I want to export my mssqlserver 2000 database with all relation ship and all
default value and all data save in sqlserver Database (I want to export hole
data base object ) in MS Access database is it possible
if it is possible from mssqlserver please tell me if it is possible from any
other third party tool then please tell me
thanks in advanced
from
khurram
I am not aware of tool that does a complete transfer of all objects from SQL
Server to MS Access. You can certainly use SQL Server DTS to move data to
Access. You might want to post this to an Access group as well.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:O1N0qQnZEHA.3988@.tk2msftngp13.phx.gbl...
hi All
I want to export my mssqlserver 2000 database with all relation ship and all
default value and all data save in sqlserver Database (I want to export hole
data base object ) in MS Access database is it possible
if it is possible from mssqlserver please tell me if it is possible from any
other third party tool then please tell me
thanks in advanced
from
khurram
|||Hi,
You can either use Upsizing wizard or SQL DTS to upgrade objects/data from
Access to SQL server.
The details about the tools and steps are detailed in the below
knowledgebase article.
http://support.microsoft.com/default...b;en-us;237980
Thanks
Hari
MCDBA"khurram alam" <khurram.alam@.eintelligencesoft.com> wrote in message
news:O1N0qQnZEHA.3988@.tk2msftngp13.phx.gbl...
> hi All
> I want to export my mssqlserver 2000 database with all relation ship and
all
> default value and all data save in sqlserver Database (I want to export
hole
> data base object ) in MS Access database is it possible
> if it is possible from mssqlserver please tell me if it is possible from
any
> other third party tool then please tell me
> thanks in advanced
> from
> khurram
>

Any improvements to this: Cannot apply value null to property Login: Value cannot be null.

Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.

Jayaram Krishnaswamy wrote:

Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.

Perhaps if you explained what on earth you're talking about someone could help. Explaining how you got this error would be a good place to start.

The software is 16 months old by the way.

-Jamie