How can I open an SQL database and be the only one who has access to it?

Hello,
I'm opening an ADODB connection to an SQL database in VB6 using the
adModeShareExclusive mode for the connection object. The connection opens
successfully, but I'm still able to modify records in the database using
Enterprise Manager. Why is this? I want to open the SQL database and lock
out all other connections, i.e. anyone who tries to connect to it after I
have a successful adModeShareExclusive mode connection established should
get an error when they try to connect. At least that's what I'm trying to
accomplish so that I can do a complete rebuild of the database. I don't
want to kick everyone out of their connections when I try to connect. I'll
just keep trying until I finally get a connection when no one else is
connected. Does anyone out there know how I can do this? Maybe I should
simply be using a different mode, but which one?
Thanks,
William Hildebrand
hi William,
"William Hildebrand" <wahilde@.cableone.net> ha scritto nel messaggio
news:10lr3gghg65f074@.corp.supernews.com
> Hello,
> I'm opening an ADODB connection to an SQL database in VB6 using the
> adModeShareExclusive mode for the connection object. The connection
> opens successfully, but I'm still able to modify records in the
> database using Enterprise Manager. Why is this? I want to open the
> SQL database and lock out all other connections, i.e. anyone who
> tries to connect to it after I have a successful adModeShareExclusive
> mode connection established should get an error when they try to
> connect. At least that's what I'm trying to accomplish so that I can
> do a complete rebuild of the database. I don't want to kick everyone
> out of their connections when I try to connect. I'll just keep
> trying until I finally get a connection when no one else is
> connected. Does anyone out there know how I can do this? Maybe I
> should simply be using a different mode, but which one?
> Thanks,
> William Hildebrand
you can perhaps try another way...
setting the database in single user mode
ALTER DATABASE db_name
SET SINGLE_USER
after you established your ADO connection... eventually trap errors that
could be raised and, before closing your connection, execute
ALTER DATABASE db_name
SET MULTI_USER
please see
http://msdn.microsoft.com/library/de...aa-az_4e5h.asp
for ALTER DATABASE statement's synopsis, and the "termination" options that
could grant you further granularity...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

how can i get the base tables underlying a given view object ?

i would like to know if i can, thru a query, find the base tables
underlying a given view object.
eg: somebody has created a view as
create view empMaster as
select * from emp1
union all
select * from emp2
now given the view empMaster, i want to know the underlying tables (i.e.
emp1,emp2)
thanks
Vivek T S
Member Technical Staff (Inucom)
try sp_depends viewname
HTH
"Vivek T S" wrote:

> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)
|||Vivek T S wrote:
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables
> (i.e. emp1,emp2)
> thanks
Or run sp_help on the view to see the actual SQL. sp_depends is a good
choice as well, but is not always accurate with stored procedures. Not
sure of the same problem with views.
David G.
|||exec sp_helptext empMaster
This will show the TSQL definition used to define the view.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)
|||David G. wrote:
> Vivek T S wrote:
> Or run sp_help on the view to see the actual SQL. sp_depends is a good
> choice as well, but is not always accurate with stored procedures. Not
> sure of the same problem with views.
Oops.. meant sp_helptext.. Thanks Kalen.
David G.
|||Thanks guys.
One more question. Can i do the same using say an ODBC program (which is my
real interest)
"Kalen Delaney" wrote:

> exec sp_helptext empMaster
> This will show the TSQL definition used to define the view.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
>
>

how can i get the base tables underlying a given view object ?

i would like to know if i can, thru a query, find the base tables
underlying a given view object.
eg: somebody has created a view as
create view empMaster as
select * from emp1
union all
select * from emp2
now given the view empMaster, i want to know the underlying tables (i.e.
emp1,emp2)
thanks
--
Vivek T S
Member Technical Staff (Inucom)try sp_depends viewname
HTH
"Vivek T S" wrote:
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)|||Vivek T S wrote:
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables
> (i.e. emp1,emp2)
> thanks
Or run sp_help on the view to see the actual SQL. sp_depends is a good
choice as well, but is not always accurate with stored procedures. Not
sure of the same problem with views.
--
David G.|||exec sp_helptext empMaster
This will show the TSQL definition used to define the view.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)|||David G. wrote:
> Vivek T S wrote:
>> i would like to know if i can, thru a query, find the base tables
>> underlying a given view object.
>> eg: somebody has created a view as
>> create view empMaster as
>> select * from emp1
>> union all
>> select * from emp2
>> now given the view empMaster, i want to know the underlying tables
>> (i.e. emp1,emp2)
>> thanks
> Or run sp_help on the view to see the actual SQL. sp_depends is a good
> choice as well, but is not always accurate with stored procedures. Not
> sure of the same problem with views.
Oops.. meant sp_helptext.. Thanks Kalen.
--
David G.

how can i get the base tables underlying a given view object ?

i would like to know if i can, thru a query, find the base tables
underlying a given view object.
eg: somebody has created a view as
create view empMaster as
select * from emp1
union all
select * from emp2
now given the view empMaster, i want to know the underlying tables (i.e.
emp1,emp2)
thanks
--
Vivek T S
Member Technical Staff (Inucom)try sp_depends viewname
HTH
"Vivek T S" wrote:

> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)|||Vivek T S wrote:
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables
> (i.e. emp1,emp2)
> thanks
Or run sp_help on the view to see the actual SQL. sp_depends is a good
choice as well, but is not always accurate with stored procedures. Not
sure of the same problem with views.
David G.|||exec sp_helptext empMaster
This will show the TSQL definition used to define the view.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
> i would like to know if i can, thru a query, find the base tables
> underlying a given view object.
> eg: somebody has created a view as
> create view empMaster as
> select * from emp1
> union all
> select * from emp2
> now given the view empMaster, i want to know the underlying tables (i.e.
> emp1,emp2)
> thanks
> --
> Vivek T S
> Member Technical Staff (Inucom)|||David G. wrote:
> Vivek T S wrote:
> Or run sp_help on the view to see the actual SQL. sp_depends is a good
> choice as well, but is not always accurate with stored procedures. Not
> sure of the same problem with views.
Oops.. meant sp_helptext.. Thanks Kalen.
David G.|||Thanks guys.
One more question. Can i do the same using say an ODBC program (which is my
real interest)
"Kalen Delaney" wrote:

> exec sp_helptext empMaster
> This will show the TSQL definition used to define the view.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Vivek T S" <VivekTS@.discussions.microsoft.com> wrote in message
> news:65066EF5-2964-43D6-B50A-79B3207F098C@.microsoft.com...
>
>

How can I get or set a foreign key's 'on update cascade' or 'on insert cascade' by SQLDMO?

Hello,everyone!
I have a problem with sqldmo's KEY object,please help me!
When creating a foreign key constraint with 'on update cascade ' or 'on delete no action ' by T-SQL,we can do like this:
ALTER TABLE [dbo].[myForeignTableName] with nocheck
ADD CONSTRAINT [FK_mykeyname] FOREIGN KEY
([myForeignColumn])
REFERENCES [dbo].[myPrimaryTableName]
([myPrimaryKeyColumn])
on delete no action on update cascade

But,how can I set or get ' on delete no action on update cascade ' by SQLDMO's KEY object?

Thank you very much!
Toly.

DMO does not support cascaded delete. SMO however, does support it.|||

A thousand thanks for Michiel Wories's help! Your answer is very very heplful to me!

Koly.