Is there a run command to open enterprise manager?

Hello ,
I know that to open Management studio I use "sqlwb" in the run
window from the start menu. Is there a run command to open up
Enterprise Manager?
Thanks
-L
Yes, but it's a little longer and will vary from this depending on the
location of your program files folder.
mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server
Enterprise Manager.MSC"
Of course, this assumes you have Enterprise Manager and Management Studio
installed on the same machine, and that in his case you really want to use
both tools (I wouldn't, personally).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Learner" <pradev@.gmail.com> wrote in message
news:1178113494.312020.262680@.c35g2000hsg.googlegr oups.com...
> Hello ,
> I know that to open Management studio I use "sqlwb" in the run
> window from the start menu. Is there a run command to open up
> Enterprise Manager?
> Thanks
> -L
>
|||Yes, but it's a little longer and will vary from this depending on the
location of your program files folder.
mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server
Enterprise Manager.MSC"
Of course, this assumes you have Enterprise Manager and Management Studio
installed on the same machine, and that in his case you really want to use
both tools (I wouldn't, personally).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Learner" <pradev@.gmail.com> wrote in message
news:1178113494.312020.262680@.c35g2000hsg.googlegr oups.com...
> Hello ,
> I know that to open Management studio I use "sqlwb" in the run
> window from the start menu. Is there a run command to open up
> Enterprise Manager?
> Thanks
> -L
>
|||On May 2, 9:53 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Yes, but it's a little longer and will vary from this depending on the
> location of your program files folder.
> mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server
> Enterprise Manager.MSC"
> Of course, this assumes you have Enterprise Manager and Management Studio
> installed on the same machine, and that in his case you really want to use
> both tools (I wouldn't, personally).
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "Learner" <pra...@.gmail.com> wrote in message
> news:1178113494.312020.262680@.c35g2000hsg.googlegr oups.com...
>
>
>
> - Show quoted text -
Hello Aaron,
I dont' have both installed on the same box but I do on two different
boxes. So just wondering if there is a run command to open up
Enterprise Manager. I will try with the one you mentioned.
Thanks,
-L

Is there a run command to open enterprise manager?

Hello ,
I know that to open Management studio I use "sqlwb" in the run
window from the start menu. Is there a run command to open up
Enterprise Manager?
Thanks
-LYes, but it's a little longer and will vary from this depending on the
location of your program files folder.
mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server
Enterprise Manager.MSC"
Of course, this assumes you have Enterprise Manager and Management Studio
installed on the same machine, and that in his case you really want to use
both tools (I wouldn't, personally).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Learner" <pradev@.gmail.com> wrote in message
news:1178113494.312020.262680@.c35g2000hsg.googlegroups.com...
> Hello ,
> I know that to open Management studio I use "sqlwb" in the run
> window from the start menu. Is there a run command to open up
> Enterprise Manager?
> Thanks
> -L
>|||Yes, but it's a little longer and will vary from this depending on the
location of your program files folder.
mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server
Enterprise Manager.MSC"
Of course, this assumes you have Enterprise Manager and Management Studio
installed on the same machine, and that in his case you really want to use
both tools (I wouldn't, personally).
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Learner" <pradev@.gmail.com> wrote in message
news:1178113494.312020.262680@.c35g2000hsg.googlegroups.com...
> Hello ,
> I know that to open Management studio I use "sqlwb" in the run
> window from the start menu. Is there a run command to open up
> Enterprise Manager?
> Thanks
> -L
>|||On May 2, 9:53 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Yes, but it's a little longer and will vary from this depending on the
> location of your program files folder.
> mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server
> Enterprise Manager.MSC"
> Of course, this assumes you have Enterprise Manager and Management Studio
> installed on the same machine, and that in his case you really want to use
> both tools (I wouldn't, personally).
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "Learner" <pra...@.gmail.com> wrote in message
> news:1178113494.312020.262680@.c35g2000hsg.googlegroups.com...
>
>
>
>
> - Show quoted text -
Hello Aaron,
I dont' have both installed on the same box but I do on two different
boxes. So just wondering if there is a run command to open up
Enterprise Manager. I will try with the one you mentioned.
Thanks,
-L

Is there a run command to open enterprise manager?

Hello ,
I know that to open Management studio I use "sqlwb" in the run
window from the start menu. Is there a run command to open up
Enterprise Manager?
Thanks
-LYes, but it's a little longer and will vary from this depending on the
location of your program files folder.
mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server
Enterprise Manager.MSC"
Of course, this assumes you have Enterprise Manager and Management Studio
installed on the same machine, and that in his case you really want to use
both tools (I wouldn't, personally).
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Learner" <pradev@.gmail.com> wrote in message
news:1178113494.312020.262680@.c35g2000hsg.googlegroups.com...
> Hello ,
> I know that to open Management studio I use "sqlwb" in the run
> window from the start menu. Is there a run command to open up
> Enterprise Manager?
> Thanks
> -L
>|||Yes, but it's a little longer and will vary from this depending on the
location of your program files folder.
mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server
Enterprise Manager.MSC"
Of course, this assumes you have Enterprise Manager and Management Studio
installed on the same machine, and that in his case you really want to use
both tools (I wouldn't, personally).
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Learner" <pradev@.gmail.com> wrote in message
news:1178113494.312020.262680@.c35g2000hsg.googlegroups.com...
> Hello ,
> I know that to open Management studio I use "sqlwb" in the run
> window from the start menu. Is there a run command to open up
> Enterprise Manager?
> Thanks
> -L
>|||On May 2, 9:53 am, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Yes, but it's a little longer and will vary from this depending on the
> location of your program files folder.
> mmc.exe /s "C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server
> Enterprise Manager.MSC"
> Of course, this assumes you have Enterprise Manager and Management Studio
> installed on the same machine, and that in his case you really want to use
> both tools (I wouldn't, personally).
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "Learner" <pra...@.gmail.com> wrote in message
> news:1178113494.312020.262680@.c35g2000hsg.googlegroups.com...
>
> > Hello ,
> > I know that to open Management studio I use "sqlwb" in the run
> > window from the start menu. Is there a run command to open up
> > Enterprise Manager?
> > Thanks
> > -L- Hide quoted text -
> - Show quoted text -
Hello Aaron,
I dont' have both installed on the same box but I do on two different
boxes. So just wondering if there is a run command to open up
Enterprise Manager. I will try with the one you mentioned.
Thanks,
-L

Is there a Cluster command line I can run which will stop my "SQL Agent Service"?

Gurus,
Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
Cluster command line I can run which will stop my "SQL Agent Service"?
Spin
Hi
How about NET STOP?
John
"Spin" <Spin@.invalid.com> wrote in message
news:656t5iF2eq3dbU1@.mid.individual.net...
> Gurus,
> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
> Cluster command line I can run which will stop my "SQL Agent Service"?
> --
> Spin
>
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e2yjJedkIHA.5368@.TK2MSFTNGP04.phx.gbl...
> Hi
> How about NET STOP?
No can do John. NET STOP stops the service outside of the Cluster
environment. What will happen is the Windows Cluster service will detect
the service down then automatically restart it! What I need is a Cluster
service command line tool which gracefully stops the service per "knowledge"
if you will, of the Cluster service.
|||For the default instance:
cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
For a named instance (assuming that the instance name is SQL2):
cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
/offline
Linchi
"Spin" wrote:

> Gurus,
> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
> Cluster command line I can run which will stop my "SQL Agent Service"?
> --
> Spin
>
>
|||Thanks! I'll shout back on Monday if this works...
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C9EF5727-A88B-4261-B445-266B2C45990C@.microsoft.com...[vbcol=seagreen]
> For the default instance:
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
> For a named instance (assuming that the instance name is SQL2):
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
> /offline
> Linchi
> "Spin" wrote:
|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C9EF5727-A88B-4261-B445-266B2C45990C@.microsoft.com...
> For the default instance:
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
> For a named instance (assuming that the instance name is SQL2):
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
> /offline
> Linchi
Worked!!!!! You are a Gentleman and a Scholar!
Spin

Is there a Cluster command line I can run which will stop my "SQL Agent Service"?

Gurus,
Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
Cluster command line I can run which will stop my "SQL Agent Service"?
--
SpinHi
How about NET STOP?
John
"Spin" <Spin@.invalid.com> wrote in message
news:656t5iF2eq3dbU1@.mid.individual.net...
> Gurus,
> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
> Cluster command line I can run which will stop my "SQL Agent Service"?
> --
> Spin
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e2yjJedkIHA.5368@.TK2MSFTNGP04.phx.gbl...
> Hi
> How about NET STOP?
No can do John. NET STOP stops the service outside of the Cluster
environment. What will happen is the Windows Cluster service will detect
the service down then automatically restart it! What I need is a Cluster
service command line tool which gracefully stops the service per "knowledge"
if you will, of the Cluster service.|||For the default instance:
cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
For a named instance (assuming that the instance name is SQL2):
cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
/offline
Linchi
"Spin" wrote:
> Gurus,
> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
> Cluster command line I can run which will stop my "SQL Agent Service"?
> --
> Spin
>
>|||Thanks! I'll shout back on Monday if this works...
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C9EF5727-A88B-4261-B445-266B2C45990C@.microsoft.com...
> For the default instance:
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
> For a named instance (assuming that the instance name is SQL2):
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
> /offline
> Linchi
> "Spin" wrote:
>> Gurus,
>> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
>> Cluster command line I can run which will stop my "SQL Agent Service"?
>> --
>> Spin
>>|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C9EF5727-A88B-4261-B445-266B2C45990C@.microsoft.com...
> For the default instance:
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
> For a named instance (assuming that the instance name is SQL2):
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
> /offline
> Linchi
Worked!!!!! You are a Gentleman and a Scholar!
--
Spin

Is there a better way than this?

Hi,
I am adding a row to my table with the code below. It seems to be
inefficient to me as I have to basically build my command with the string
builder. There has to be a better way, no?
What I am trying to do is to search my table for the 2 variables ln1 and
ln2. If they are not found, then one row is added to the table with the
respective values of ln1 and ln2. So for example, if my table contained 100
rows and I perform a search for the values ln1 and ln2 and they are not
found, then one row is added to my table with the values of ln1 and ln2.
Therefore, my table will now have 101 rows.
The code below works, it's just that I think there must be a more efficient
way of doing it.
Another thing that I find that is strange is that when a row is added to my
table, it is added in between rows so that the ascending order is
maintained. Why is that, I as have never asked to sort it or index it.
TIA
Roy
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
namespace testing
{
class Class1
{
// private System.Data.DataSet dataSet;
[STAThread]
static void Main(string[] args)
{
string strConnection = @."Data
Source=. \SQLEXPRESS;AttachDbFilename=D:\CSRBC\SQ
L_2\SQL_2.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=True";
int ln1 = 5;
int ln2 = 19;
StringBuilder strCommand = new StringBuilder("INSERT INTO tblSQL_2 (SOBN ,
BN1 ) SELECT ");
strCommand.Append(ln1).Append(" , ").Append(ln2).Append(" WHERE not exists
(select * from tblSQL_2 where SOBN = ");
strCommand.Append(ln1).Append(" AND BN1 = ").Append(ln2).Append(")");
SqlConnection oConnection = new SqlConnection(strConnection);
SqlCommand oCommand = new SqlCommand(strCommand.ToString(), oConnection);
oCommand.Connection.Open();
oCommand.ExecuteNonQuery();
oCommand.Connection.Close();
}
}
}I suggest you use a parameterized SQL statement instead of building a SQL
statement string with the literal values. For example:
SqlCommand oCommand = new SqlCommand(
"INSERT INTO tblSQL_2 (SOBN ,BN1 ) " +
" SELECT @.In1, @.In2" +
" WHERE NOT EXISTS(" +
" SELECT *" +
" FROM tblSQL_2" +
" WHERE SOBN = @.In1 AND" +
" BN1 = @.In2)",
oConnection);
oCommand.Parameters.Add("@.In1", In1);
oCommand.Parameters.Add("@.In2", In2);

> Another thing that I find that is strange is that when a row is added to
> my
> table, it is added in between rows so that the ascending order is
> maintained. Why is that, I as have never asked to sort it or index it.
A table in a relational database is an unordered set of rows. Regardless of
insert order or indexing, you must specify ORDER BY to return data in a
particular sequence.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:RREff.8561$0u1.70303@.weber.videotron.net...
> Hi,
> I am adding a row to my table with the code below. It seems to be
> inefficient to me as I have to basically build my command with the string
> builder. There has to be a better way, no?
> What I am trying to do is to search my table for the 2 variables ln1 and
> ln2. If they are not found, then one row is added to the table with the
> respective values of ln1 and ln2. So for example, if my table contained
> 100
> rows and I perform a search for the values ln1 and ln2 and they are not
> found, then one row is added to my table with the values of ln1 and ln2.
> Therefore, my table will now have 101 rows.
> The code below works, it's just that I think there must be a more
> efficient
> way of doing it.
> Another thing that I find that is strange is that when a row is added to
> my
> table, it is added in between rows so that the ascending order is
> maintained. Why is that, I as have never asked to sort it or index it.
> TIA
> Roy
>
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=D:\CSRBC\SQ
L_2\SQL_2.mdf;Integrated
> Security=True;Connect Timeout=30;User Instance=True";
> int ln1 = 5;
> int ln2 = 19;
> StringBuilder strCommand = new StringBuilder("INSERT INTO tblSQL_2 (SOBN ,
> BN1 ) SELECT ");
> strCommand.Append(ln1).Append(" , ").Append(ln2).Append(" WHERE not exists
> (select * from tblSQL_2 where SOBN = ");
> strCommand.Append(ln1).Append(" AND BN1 = ").Append(ln2).Append(")");
> SqlConnection oConnection = new SqlConnection(strConnection);
> SqlCommand oCommand = new SqlCommand(strCommand.ToString(), oConnection);
> oCommand.Connection.Open();
> oCommand.ExecuteNonQuery();
> oCommand.Connection.Close();
> }
> }
> }
>
>|||"Roy Gourgi" <royng@.videotron.ca> wrote in
news:RREff.8561$0u1.70303@.weber.videotron.net:

> Hi,
> I am adding a row to my table with the code below. It seems to be
> inefficient to me as I have to basically build my command with the
> string builder. There has to be a better way, no?
> What I am trying to do is to search my table for the 2 variables ln1
> and ln2. If they are not found, then one row is added to the table
> with the respective values of ln1 and ln2. So for example, if my table
> contained 100 rows and I perform a search for the values ln1 and ln2
> and they are not found, then one row is added to my table with the
> values of ln1 and ln2. Therefore, my table will now have 101 rows.
> The code below works, it's just that I think there must be a more
> efficient way of doing it.
> Another thing that I find that is strange is that when a row is added
> to my table, it is added in between rows so that the ascending order
> is maintained. Why is that, I as have never asked to sort it or index
> it.
> TIA
> Roy
>
> using System;
> using System.Data;
> using System.Data.Common;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Text;
>
> namespace testing
> {
> class Class1
> {
> // private System.Data.DataSet dataSet;
> [STAThread]
>
> static void Main(string[] args)
> {
> string strConnection = @."Data
> Source=. \SQLEXPRESS;AttachDbFilename=D:\CSRBC\SQ
L_2\SQL_
2.mdf;Integrate
> d Security=True;Connect Timeout=30;User Instance=True";
> int ln1 = 5;
> int ln2 = 19;
> StringBuilder strCommand = new StringBuilder("INSERT INTO tblSQL_2
> (SOBN , BN1 ) SELECT ");
> strCommand.Append(ln1).Append(" , ").Append(ln2).Append(" WHERE not
> exists (select * from tblSQL_2 where SOBN = ");
> strCommand.Append(ln1).Append(" AND BN1 = ").Append(ln2).Append(")");
> SqlConnection oConnection = new SqlConnection(strConnection);
> SqlCommand oCommand = new SqlCommand(strCommand.ToString(),
> oConnection);
> oCommand.Connection.Open();
> oCommand.ExecuteNonQuery();
> oCommand.Connection.Close();
> }
> }
> }
Why use CLR at all, this can perfectly well be done with pure T-SQL. I
leave handling of NULLs to the reader.
create table demo (
val_a int,
val_b int
)
insert into demo values (1,1)
insert into demo values (1,2)
insert into demo values (2,1)
select count(1) from demo where (val_a = 1) and (val_b = 0)
create procedure prDemo @.a int, @.b int as
begin
declare
@.count int;
select
@.count = count(1)
from
demo
where
(val_a = @.a) and (val_b = @.b);
if @.count = 0
insert into demo values (@.a,@.b)
end;
exec prDemo 1,2
exec prDemo 2,2
select * from Demo
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Hi Dan,
Your code works great, but I do get some warning that says that this
line:
oCommand.Parameters.Add("@.ln1", ln1);
has been deprecated and to use:
AddWithValue(string ParameterName, Object value);
You said:
A table in a relational database is an unordered set of rows. Regardless
of
insert order or indexing, you must specify ORDER BY to return data in a
particular sequence.
That is what I thought too, but when I add the row it adds it in between
rows and preserving the ascending order. I never asked it to ORDER BY a
particular sequence.
BTW are you not from Montreal? I think that I once bought a computer
from you. Small world.
Thanks
Roy
*** Sent via Developersdex http://www.examnotes.net ***|||> has been deprecated and to use:
> AddWithValue(string ParameterName, Object value);
I agree that you should avoid the deprecated method. I didn't know which
version of .Net you are using.

> BTW are you not from Montreal? I think that I once bought a computer
> from you. Small world.
Not me - never been to Canada. A bit too cold for my blood :-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Gourgi" <royng@.videotron.ca> wrote in message
news:OO7pB8Q7FHA.476@.TK2MSFTNGP15.phx.gbl...
> Hi Dan,
> Your code works great, but I do get some warning that says that this
> line:
> oCommand.Parameters.Add("@.ln1", ln1);
> has been deprecated and to use:
> AddWithValue(string ParameterName, Object value);
> You said:
> A table in a relational database is an unordered set of rows. Regardless
> of
> insert order or indexing, you must specify ORDER BY to return data in a
> particular sequence.
> That is what I thought too, but when I add the row it adds it in between
> rows and preserving the ascending order. I never asked it to ORDER BY a
> particular sequence.
> BTW are you not from Montreal? I think that I once bought a computer
> from you. Small world.
> Thanks
> Roy
>
> *** Sent via Developersdex http://www.examnotes.net ***|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in
news:ObF82qQ7FHA.1000@.tk2msftngp13.phx.gbl:

> I suggest you use a parameterized SQL statement instead of building a
> SQL statement string with the literal values. For example:
I agree that parameterized is better in this case, but I do not see the
need of CLR. This appears to me to be a very good example of a case in
which CLR should not be used, but T-SQL.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Hi Ole,
Sorry for asking but what is T-SQL?
How do I implement this into my program. I am using C# and SQL 2005 Express
edition.
Do I just put this into my code?
Roy

> Why use CLR at all, this can perfectly well be done with pure T-SQL. I
> leave handling of NULLs to the reader.
> create table demo (
> val_a int,
> val_b int
> )
> insert into demo values (1,1)
> insert into demo values (1,2)
> insert into demo values (2,1)
> select count(1) from demo where (val_a = 1) and (val_b = 0)
> create procedure prDemo @.a int, @.b int as
> begin
> declare
> @.count int;
> select
> @.count = count(1)
> from
> demo
> where
> (val_a = @.a) and (val_b = @.b);
> if @.count = 0
> insert into demo values (@.a,@.b)
> end;
> exec prDemo 1,2
> exec prDemo 2,2
> select * from Demo
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971392C765414olekristianbangaas@.
207.46.248.16...
> "Roy Gourgi" <royng@.videotron.ca> wrote in
> news:RREff.8561$0u1.70303@.weber.videotron.net:
>
> 2.mdf;Integrate
> Why use CLR at all, this can perfectly well be done with pure T-SQL. I
> leave handling of NULLs to the reader.
> create table demo (
> val_a int,
> val_b int
> )
> insert into demo values (1,1)
> insert into demo values (1,2)
> insert into demo values (2,1)
> select count(1) from demo where (val_a = 1) and (val_b = 0)
> create procedure prDemo @.a int, @.b int as
> begin
> declare
> @.count int;
> select
> @.count = count(1)
> from
> demo
> where
> (val_a = @.a) and (val_b = @.b);
> if @.count = 0
> insert into demo values (@.a,@.b)
> end;
> exec prDemo 1,2
> exec prDemo 2,2
> select * from Demo
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||One more thing Ole, would this code be faster than the other code?
Thanks
Roy
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971392C765414olekristianbangaas@.
207.46.248.16...
> "Roy Gourgi" <royng@.videotron.ca> wrote in
> news:RREff.8561$0u1.70303@.weber.videotron.net:
>
> 2.mdf;Integrate
> Why use CLR at all, this can perfectly well be done with pure T-SQL. I
> leave handling of NULLs to the reader.
> create table demo (
> val_a int,
> val_b int
> )
> insert into demo values (1,1)
> insert into demo values (1,2)
> insert into demo values (2,1)
> select count(1) from demo where (val_a = 1) and (val_b = 0)
> create procedure prDemo @.a int, @.b int as
> begin
> declare
> @.count int;
> select
> @.count = count(1)
> from
> demo
> where
> (val_a = @.a) and (val_b = @.b);
> if @.count = 0
> insert into demo values (@.a,@.b)
> end;
> exec prDemo 1,2
> exec prDemo 2,2
> select * from Demo
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Roy Gourgi" <royng@.videotron.ca> wrote in
news:FBHff.8901$0u1.153785@.weber.videotron.net:

> Sorry for asking but what is T-SQL?
> How do I implement this into my program. I am using C# and SQL 2005
> Express edition.
> Do I just put this into my code?
T-SQL is Transact SQL, the SQL variant used by SQL Server. By using code
like the one that I wrote on the SQL Server, you can call the stored
procedure multiple times later. If you do not have any experience with SQL,
I would strongly suggest that you learn a bit of SQL before you continue.
So, no you don't put this directly into your code, but make call to the
stored proceudre from your code.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Roy Gourgi" <royng@.videotron.ca> wrote in
news:HDHff.8902$0u1.155354@.weber.videotron.net:

> One more thing Ole, would this code be faster than the other code?
As far as I've understood .NET CLR, it possibly would since the stored
procedure is precompiled. There are no way that the .NET code will be
faster.
The greatest advantage for you as a developer however, is the option to
modify the logic of the stored procedure in only one location.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messagingsql

Is Stored Procedure and 'in' broken in MSDE 2000/SQL 2000 SP4?

This is not the command that im running but demonstrates the problem just
fine, basically 'in' using statored procedures seems to be performing as 'in'
or 'is' where as sending a query direct (identical) only performs 'in' as
expected
Example
Use msdb
Select * From Sysalerts Where database_name in (Select
convert(sysname(128),null))
returns nothing
but put the select command in a stored proceedure and it will return all the
rows with database_name that is null
Am i not seeing/setting something?
Simon
On Tue, 21 Jun 2005 08:36:05 -0700, "Tuner Fich" <Tuner
Fich@.discussions.microsoft.com> wrote:

>This is not the command that im running but demonstrates the problem just
>fine, basically 'in' using statored procedures seems to be performing as 'in'
>or 'is' where as sending a query direct (identical) only performs 'in' as
>expected
>Example
>Use msdb
>Select * From Sysalerts Where database_name in (Select
>convert(sysname(128),null))
>returns nothing
>but put the select command in a stored proceedure and it will return all the
>rows with database_name that is null
>Am i not seeing/setting something?
>Simon
Hi Simon,
I guess that you normally have the setting SET ANSI_NULLS ON (which is
fine, as it makes SQL Server treat NULLS as defined in the ANSI
standard, making your code more portable). However, when creating stored
procedure, someow the setting gets changed to SET ANSI_NULLS OFF (which
is definitely NOT fine, as it makes SQL Server treat NULLS in a
non-standard way that might appear logical at first glance but is not,
and that will make other database programmers fail to understand your
code).
With the ANSI standard ebhaviour for NULLS, logical expressions use
three-valued logic (True, False and Unknown) and all comparisons to NULL
will always return Unknown. The only valid way to copmpare a column or
variable to NULL is to use "WHERE column IS [NOT] NULL".
Also, stop using [NOT] IN with a subselect, as they are a source of
confusion with ANSI standard settings, and they can always be
transformed into a [NOT] EXISTS subquery that usually performs better as
well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Is SP command quicker than recordset?

Hi,
I have a web site that uses ASP. I want to know if a record exists for
a specific date. I will be doing this 30 times for each refresh of the
page.
What I've setup is a simple SP that takes a date as an input and
returns a count.
CREATE PROCEDURE spGetNumber
(
@.Date smalldatetime
)
AS
SET NOCOUNT ON
DECLARE @.Count tinyint
SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
RETURN @.Count
--
In my ASP, I'm calling a command object.
It seems pretty fast, but I only have 100 records in the table, 1 user
at a time. In the future I expect 100's of users and 10,000's of
records.
Is this the best method?
thx,
Bodihi bodi
u can continue using the SP. it is advisible to contact the database only
once from the ASP page instead of contacting 30 times
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"BodiKlamph@.gmail.com" wrote:

> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||On 7 Aug 2005 11:18:46 -0700, BodiKlamph@.gmail.com wrote:

>Hi,
>I have a web site that uses ASP. I want to know if a record exists for
>a specific date. I will be doing this 30 times for each refresh of the
>page.
Hi Bodi,
Issuing 30 consecutive calls is not a good idea. If the sole purpose is
to find the dates for which no row exists, rewrite the stored procedure
to take two parameters (starting and ending date) and return a resultset
of all dates for which no rows exist.

>What I've setup is a simple SP that takes a date as an input and
>returns a count.
Another thing: if you only need to verify existence, use EXISTS, not
COUNT. A query with COUNT will always continue until all matching rows
are found. A query with EXISTS will stop after the first matching row.

>It seems pretty fast, but I only have 100 records in the table, 1 user
>at a time. In the future I expect 100's of users and 10,000's of
>records.
10,000's of rows is not very much for SQL Server, but still - why incur
extra overhead that can easily be avoided.

>Is this the best method?
The best method involves a calendar table. If you don't have one yet in
your database, go ahead and create it now. Instructions for making a
calendar table and many examples of how you can use one are at this
site: http://www.aspfaq.com/show.asp?id=2519.
Remember that you only need to create the calendar table once! Just
don't forget to add some new rows every year or two.
Once you have a calendar table, you can find the dates without row in
your Race table with the following stored procedure:
CREATE PROC FindDatesWithoutRowInRace
(@.StartDate datetime,
@.EndDate datetime)
AS
SELECT c.dt
FROM dbo.Calendar AS c
WHERE c.dt >= @.StartDate
AND c.dt <= @.EndDate
AND NOT EXISTS
(SELECT *
FROM Race AS r
WHERE e.[Date] = c.dt)
go
The final step would be to change the calling code to issue one call
with two parameters, then fetch and process the rows returned by the
stored procedure.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||A couple things in addition to Chandra's and Hugo's posts...
The return value from an SP is intended to be a success/failure indicator,
like a windows app - returning non-zero indicates failure, so when you get a
count > 0 and return it you're indicating that the SP failed.
If you want a count of matching records you should use an output parameter;
if you simply want to test for existance you should use EXISTS like Hugo
said, and set the output BIT to 1...
Another note, with COUNT - generally you want to use COUNT(*) not
COUNT([field_name]) because then SQL can optimize to use the best index
rather than have to use an index or constraint (assuming one exists) with th
e
specified field.
I'm going to go out on a limb and guess that you're trying to display a
calendar, hence the 30 calls per request. For this scenario you should retur
n
records that fall in a specific date range:
SELECT DISTINCT [date]
FROM [table_owner].Race
WHERE [date] BETWEEN @.lo_date AND @.hi_date
Then populate the calendar from the recordset in your ASP. Rememebr that
datetime includes time, so if you want records thru the end of the last date
specified then you'll have to be sure either all dates in the table have
00:00:00.000 for the time portion or @.lo_date has time 00:00:00.000 and
@.hi_date has time 23:59:59.997.
Be sure your command objkect's type is set to stored procedure, not text
query, for fastest execution.
Good luck, - KH
"BodiKlamph@.gmail.com" wrote:

> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||As above...
But think in terms of SETS of data.
What is the ideal SET of data that the web application could get - KH has
already suggested "the set of dates where there are no races". This may not
be ideal for your real needs, but is definitely a substantial improvement on
making multiple calls that s out many times singular Yes / No answers -
which aren't sets at all, they are simple questions.
- Tim
<BodiKlamph@.gmail.com> wrote in message
news:1123438726.697862.102120@.g43g2000cwa.googlegroups.com...
> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||In addition to the already mentioned ideas:
If you are always making exactly 30 calls, do the different values of date
have a relationship to one another?
For example, using a resultset instead of the return output:
SELECT sum (case when date = @.date then 1 else 0 end) as first,
sum(case when date = dateadd(minute,-1,@.date) then 1 else 0 end)
as second
...
sum(case when date = dateadd(minute,N-1,@.date) then 1 else 0 end)
as Nth
FROM race
WHERE date between @.date and dateadd(minute,N-1,@.date)
or something along these lines and it will probably be faster. Even doing
thirty parms and then putting the thirty sums would likely be better because
of networkIO type stuff.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
<BodiKlamph@.gmail.com> wrote in message
news:1123438726.697862.102120@.g43g2000cwa.googlegroups.com...
> Hi,
> I have a web site that uses ASP. I want to know if a record exists for
> a specific date. I will be doing this 30 times for each refresh of the
> page.
> What I've setup is a simple SP that takes a date as an input and
> returns a count.
> --
> CREATE PROCEDURE spGetNumber
> (
> @.Date smalldatetime
> )
> AS
> SET NOCOUNT ON
> DECLARE @.Count tinyint
> SELECT @.Count = COUNT(ID) FROM Race WHERE Date=@.Date
> RETURN @.Count
> --
> In my ASP, I'm calling a command object.
> It seems pretty fast, but I only have 100 records in the table, 1 user
> at a time. In the future I expect 100's of users and 10,000's of
> records.
> Is this the best method?
> thx,
> Bodi
>|||thx Hugo, your answer seems to best suite my needs.
Yes, 30 calls is quite bad. Returning a recordset with just hte valid
dates is a much better idea. then i can loop in my asp code and add
only the valid calendar days.
question? wut's up with the calendar table you mentioned; why would I
need that?
Can't I just do:
SELECT DISTINCT [Date] FROM myTable WHERE [Date] >= startDate and
[Date] <= endDate
that would return only the valid dates|||K, I finished making the changes.
2 questions.
I have a loop that writes out my calendar days (part of an ASP calendar
class). For each day, i want to see if there is a corresponding record
in the resultset I do this by an inefficeint (tha'ts why i'm here)
recordset.find method. I have to search from record 1 each time tho
This is probaly the wrong group now, since it's more ADO than SQL, but
I'll give it a host since you're already familiar with my question.
I was using set rs = cmd.execute, but it compalined that my recordset
was forward only. So I cahnged it form a SP to a simple recordset.open
(using dbopendynamic so i could use the .find method).
I was thinking it may be easier to use GetRows() then loop through the
much less bulky array. Not sure how I'd do this yet, but I can think
of something...unless there is already a set way of doing this?
thx,
Bodi|||lol
at this point I'm just talking to myself.
I used the GetString method, with a row deliminator of comma
then, instead of .find, I did:
if instr(rsDays, "," & DAY(DateString) & ",")
works like a charm, and i imagine it's way faster to do a string check
then it is a .find
thx everybody|||On 8 Aug 2005 21:13:48 -0700, BodiKlamph@.gmail.com wrote:

>thx Hugo, your answer seems to best suite my needs.
>Yes, 30 calls is quite bad. Returning a recordset with just hte valid
>dates is a much better idea. then i can loop in my asp code and add
>only the valid calendar days.
>question? wut's up with the calendar table you mentioned; why would I
>need that?
>Can't I just do:
>SELECT DISTINCT [Date] FROM myTable WHERE [Date] >= startDate and
>[Date] <= endDate
>that would return only the valid dates
Hi Bodi,
I must have misunderstood you earlier. I thought you wanted to find the
dates that are not yet in your table. The query above will do the
reverse: find the dates that are (at least once) in the table.
But do remember that a datetime column includes a time part as well. If
you don't ensure that the time part is the same for all entries, the
query above might return the same date several times, since the DISTINCT
will look at the time part as well.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Is my database connection alive and viable?

Currently, our application is issuing a 'select count(*) from systypes' command to check to see if the database connection is live (has not been reaped by the firewall.) This is somewhat inefficient. Perhaps a less expensive way to implement this functionality is to issue a ping statement to the instance? At least a more simple SQL query is needed.

I'm sure this function is typical in application architecture. What recommendations do you have for confirming that a database connection is alive and viable from the application?

Stephanie

One way is "select 1"...