How to detect sql servers on the net

Hi
Is there a way to detect any instances of sql server on the network.
Like all the Microsoft configuration tools do...
If there exists one, is it also possible to use it within the .net
environment?
Regards
StephanStephan Zaubzer wrote:

> Hi
> Is there a way to detect any instances of sql server on the network.
> Like all the Microsoft configuration tools do...
> If there exists one, is it also possible to use it within the .net
> environment?
> Regards
> Stephan
Use OSQL -L|||I was rather looking for a way to use it within an application
In my application I need a config window where I specify to which server
to connect to...
And I wanna show all available servers in the config window...
amish wrote:
> Stephan Zaubzer wrote:
>
>
> Use OSQL -L
>|||Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
news:438F2929.5030501@.schendl.at:

> I was rather looking for a way to use it within an application
> In my application I need a config window where I specify to which
> server to connect to...
> And I wanna show all available servers in the config window...
>
You can do this in two ways;
1. You can ask for a DataSourceEnumerator from your provider (the method
name may not be exactly that, but you can find it). 2. Use SMO to
enumerate the servers on the network.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||... and to my knowledge, this is still not reliable since broadcasting is u
sed. So a server might be
in another domain, might not respond in time etc and hence will not show up.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Niels Berglund" <nielsb@.develop.com> wrote in message
news:Xns971FB26F1D379nielsbdevelopcom@.20
7.46.248.16...
> Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
> news:438F2929.5030501@.schendl.at:
>
> You can do this in two ways;
> 1. You can ask for a DataSourceEnumerator from your provider (the method
> name may not be exactly that, but you can find it). 2. Use SMO to
> enumerate the servers on the network.
> Niels
>
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:uCt#Ilx9FHA.2816@.tk2msftngp13.phx.gbl:

> ... and to my knowledge, this is still not reliable since broadcasting
> is used. So a server might be in another domain, might not respond in
> time etc and hence will not show up.
>
absolutely!!!! The methods I mentioned earlier are very brittle, and you
can not rely 100% on them. Also, as they use broadcast the methods are
sloooow.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||DataSourcEnumerator exists only in .net 2.0
Since I am working in VS.net 2003 and .net 1.1 I can't use it.
What exactly is SMO and how would I use it?
regards
Stephan
Niels Berglund wrote:
> Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
> news:438F2929.5030501@.schendl.at:
>
>
> You can do this in two ways;
> 1. You can ask for a DataSourceEnumerator from your provider (the method
> name may not be exactly that, but you can find it). 2. Use SMO to
> enumerate the servers on the network.
> Niels
>|||Stephan Zaubzer <stephan.zaubzer@.schendl.at> wrote in
news:uDd8Y019FHA.252@.TK2MSFTNGP15.phx.gbl:
> DataSourcEnumerator exists only in .net 2.0
> Since I am working in VS.net 2003 and .net 1.1 I can't use it.
> What exactly is SMO and how would I use it?
> regards
> Stephan
> Niels Berglund wrote:
[snip]
Sorry Stephan abour DataSourceEnum. I'm afraid that SMO is in that case
not any better either. It is the new management object hierarchy in SQL
2005.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

How to detect if a schema if exists or not so that I will not create same schema agai

How to detect if a schema if exists or not so that I will not create same
schema again?
AND create schema statement should be the first statement of a bach?
--Frank, SQL2005devHello, Frank

> How to detect if a schema if exists or not so that I will not create same
> schema again?
Look into sys.schemas

> AND create schema statement should be the first statement of a bach?
Yes.
For example, you can use something like this:
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
EXEC('CREATE SCHEMA YourSchema')
Razvan|||... or use
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1137403952.648035.289210@.g49g2000cwa.googlegroups.com...
> Hello, Frank
>
> Look into sys.schemas
>
> Yes.
> For example, you can use something like this:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
> EXEC('CREATE SCHEMA YourSchema')
> Razvan
>|||I got it, thanks.
"Razvan Socol" <rsocol@.gmail.com>
'?:1137403952.648035.289210@.g49g2000cwa.googlegroups.com...
> Hello, Frank
>
> Look into sys.schemas
>
> Yes.
> For example, you can use something like this:
> IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name='YourSchema')
> EXEC('CREATE SCHEMA YourSchema')
> Razvan
>

How to delete all data from table when foreign-key exists.

I would like to delete all data from the tables in my database so that I can
test in a clean environment. With no data, it makes creating controlled
data much easier.
I would normally use "truncate", but it seems that I cannot since the tables
I want to delete from have foreign key constraints.
So, I end up using the "delete" command; however, this takes forever since
most that tables have between 100000 and millions of records.
I would imaging the performance issue is related to the fact that "delete"
writes to the transition log while "truncate" does not.
How can I delete all data from a table with foreign key constructs quickly?Disable the foreign key constraints temporarily. What's the point of
keeping them there if you're deleting all the data?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>|||Another option would be to drop the tables and re-create them (in a
dependent order, of course).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>

How to delete all data from table when foreign-key exists.

If you want an easy way which doesn't involve dropping the
constraints, you could use truncate table on all the
tables, but being careful about the order - apply the
command from the outside inwards relative to your schema
eg do the lookup tables first and the most central table
last.
Regards,
Paul IbisonPaul,
I'm afraid that truncate isn't allowed event if you have 0 rows in the
referencing tables. And even if you disable the FK, it isn't allowed. I
usually truncate the tables "at the bottom", these are typically the ones
with the most rows in. Then I just work my way "upward" and do DELETE on
those tables.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote in message
news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
> If you want an easy way which doesn't involve dropping the
> constraints, you could use truncate table on all the
> tables, but being careful about the order - apply the
> command from the outside inwards relative to your schema
> eg do the lookup tables first and the most central table
> last.
> Regards,
> Paul Ibison|||Thanks Tibor - had forgotten this behaviour.
Regards,
Paul Ibison

>--Original Message--
>Paul,
>I'm afraid that truncate isn't allowed event if you have
0 rows in the
>referencing tables. And even if you disable the FK, it
isn't allowed. I
>usually truncate the tables "at the bottom", these are
typically the ones
>with the most rows in. Then I just work my way "upward"
and do DELETE on
>those tables.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote
in message
>news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
the
>
>.
>

How to delete all data from table when foreign-key exists.

I would like to delete all data from the tables in my database so that I can
test in a clean environment. With no data, it makes creating controlled
data much easier.
I would normally use "truncate", but it seems that I cannot since the tables
I want to delete from have foreign key constraints.
So, I end up using the "delete" command; however, this takes forever since
most that tables have between 100000 and millions of records.
I would imaging the performance issue is related to the fact that "delete"
writes to the transition log while "truncate" does not.
How can I delete all data from a table with foreign key constructs quickly?Disable the foreign key constraints temporarily. What's the point of
keeping them there if you're deleting all the data?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>|||Another option would be to drop the tables and re-create them (in a
dependent order, of course).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Peter Rilling" <peter@.nospam.rilling.net> wrote in message
news:OeRzW3a8DHA.3380@.tk2msftngp13.phx.gbl...
> I would like to delete all data from the tables in my database so that I
can
> test in a clean environment. With no data, it makes creating controlled
> data much easier.
> I would normally use "truncate", but it seems that I cannot since the
tables
> I want to delete from have foreign key constraints.
> So, I end up using the "delete" command; however, this takes forever since
> most that tables have between 100000 and millions of records.
> I would imaging the performance issue is related to the fact that "delete"
> writes to the transition log while "truncate" does not.
> How can I delete all data from a table with foreign key constructs
quickly?
>|||If you want an easy way which doesn't involve dropping the
constraints, you could use truncate table on all the
tables, but being careful about the order - apply the
command from the outside inwards relative to your schema
eg do the lookup tables first and the most central table
last.
Regards,
Paul Ibison|||Paul,
I'm afraid that truncate isn't allowed event if you have 0 rows in the
referencing tables. And even if you disable the FK, it isn't allowed. I
usually truncate the tables "at the bottom", these are typically the ones
with the most rows in. Then I just work my way "upward" and do DELETE on
those tables.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote in message
news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
> If you want an easy way which doesn't involve dropping the
> constraints, you could use truncate table on all the
> tables, but being careful about the order - apply the
> command from the outside inwards relative to your schema
> eg do the lookup tables first and the most central table
> last.
> Regards,
> Paul Ibison|||Thanks Tibor - had forgotten this behaviour.
Regards,
Paul Ibison
>--Original Message--
>Paul,
>I'm afraid that truncate isn't allowed event if you have
0 rows in the
>referencing tables. And even if you disable the FK, it
isn't allowed. I
>usually truncate the tables "at the bottom", these are
typically the ones
>with the most rows in. Then I just work my way "upward"
and do DELETE on
>those tables.
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Paul Ibison" <anonymous@.discussions.microsoft.com> wrote
in message
>news:f7e301c3f1b0$fca78140$a401280a@.phx.gbl...
>> If you want an easy way which doesn't involve dropping
the
>> constraints, you could use truncate table on all the
>> tables, but being careful about the order - apply the
>> command from the outside inwards relative to your schema
>> eg do the lookup tables first and the most central table
>> last.
>> Regards,
>> Paul Ibison
>
>.
>