How to control MS SQL Server DataBases permissions through visual basic program

Hello,

Could someone help me to view, add, delete, and modify users of SQL Server DataBase and permissions of them on this database through Visual Basic Programming

Because I want to do VB application through which I will view, add,delete, and modify users of SQL Server DataBase and their permissions on this database.

Thank you very very muchYou can use the system stored procedures.|||Originally posted by rnealejr
You can use the system stored procedures.

Thank you very much Mr. rnealejr for your reply.

Would you like to send me a sample code in visual basic to view ,for example, users of SQL Server DataBase and their permissions??

I will be thankful|||Which version of sql server are you using 7 or 2000 ?|||Originally posted by rnealejr
Which version of sql server are you using 7 or 2000 ?
Hello, Mr. rnealejr
I use MS SQL Server 2000.
Also, I have a problem with SQL Server.
I have about 50 tables and views in an Oracle DataBase and I have tried to translate them to SQL Server DataBases.
They were translated succefully but there was two problems :
1- Key constraints were not translated? (i.e. the destination SQL Server database became without primary key and foreign key constraints and aother constraints)
2- Also, views are translated into tables not into view ??

Could you slove these problems?

After I get more information I will be able to serve your good forum with good posts becuase until now I am student

Thank you very much|||How did you translate from oracle to sql server ?|||You can use sp_helpuser, sp_helplogins - use the ado command object.|||Originally posted by rnealejr
How did you translate from oracle to sql server ?

Yes
I have used DTS Wizard to do this translation but tables were translated without constraints and also views were translated into tables instead of corresponding views although all tables, from whcih views get data, are available in the translated database.

Thank you very much Mr.

how to connect with sqlexpress2005 & asp.net for update insert delete codings

I want to know about the how to connect with asp.net 2005 and sqlexpress through codings using parameters

import System.Data.SqlClient;

related update delete insert codings ...

How to know about the make the new connection with different databases sqlserver, oracle mysql...

Hi

Have you had a lookhttp://quickstarts.asp.net/QuickStartv20/default.aspx andhttp://www.asp.net/learn/data-access/.

Work through these samples.. They explain how to access databases using declarative coding (sqldatasource and the likes) and coe behind coding.

Hope that helps.

VJ

How to completely delete any trace of data from a SQL2000 data fil

Hi all. I'm attempting to generate policies and procedures for dealing with
the possibility of classified data contamination in a SPS 2003 deployment,
which uses SQL 2000 for storage of all content. By "classified data
contamination", I mean that a user accidentally puts classified data into an
unclassified system, which is a major no-no. This is purely a SQL 2000 issue
- just mentioning SPS 2003 for background info.
Basically, I need to make sure that once a content item is deleted via SPS
2003 (which deletes the associated rows for the content item from the SQL
2000 content DB - yes, I have checked), that it's extremely difficult to
retrieve the content item or any residual data from it.
I conducted the following test series:
1. Upload a test "classified" word document into SPS 2003.
2. Delete the test "classified" word document via SPS 2003 (at this point
all the DB rows associated with the document are gone - can't see them in
EM/Query Analyzer). Text of data file still contains the text of the document.
3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
the text of the document.
4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
file still contains the text of the document.
Only idea I've found is scripting out the DB and rebuilding it, but this is
DML-based, which would void support for SPS. Any other ideas?How are you verifying that ' Text of data file still contains the text of the
document'?
"BAG" wrote:
> Hi all. I'm attempting to generate policies and procedures for dealing with
> the possibility of classified data contamination in a SPS 2003 deployment,
> which uses SQL 2000 for storage of all content. By "classified data
> contamination", I mean that a user accidentally puts classified data into an
> unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> - just mentioning SPS 2003 for background info.
> Basically, I need to make sure that once a content item is deleted via SPS
> 2003 (which deletes the associated rows for the content item from the SQL
> 2000 content DB - yes, I have checked), that it's extremely difficult to
> retrieve the content item or any residual data from it.
> I conducted the following test series:
> 1. Upload a test "classified" word document into SPS 2003.
> 2. Delete the test "classified" word document via SPS 2003 (at this point
> all the DB rows associated with the document are gone - can't see them in
> EM/Query Analyzer). Text of data file still contains the text of the document.
> 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> the text of the document.
> 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> file still contains the text of the document.
> Only idea I've found is scripting out the DB and rebuilding it, but this is
> DML-based, which would void support for SPS. Any other ideas?|||Didn't have to do anything fancy to do the verification, actually. Using a
text editor robust enough to allow me to view the large data files (I've used
TextPad, UltraEdit 32, and Boxer on various occasions), I did the following:
1. Opened the file.
2. Used the Find function to search for marker text strings I placed in the
document.
3. Found the marker text strings. These were embedded in the full text of
the document, which was present in its entirety.
-B
"Absar Ahmad" wrote:
> How are you verifying that ' Text of data file still contains the text of the
> document'?
> "BAG" wrote:
> > Hi all. I'm attempting to generate policies and procedures for dealing with
> > the possibility of classified data contamination in a SPS 2003 deployment,
> > which uses SQL 2000 for storage of all content. By "classified data
> > contamination", I mean that a user accidentally puts classified data into an
> > unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> > - just mentioning SPS 2003 for background info.
> >
> > Basically, I need to make sure that once a content item is deleted via SPS
> > 2003 (which deletes the associated rows for the content item from the SQL
> > 2000 content DB - yes, I have checked), that it's extremely difficult to
> > retrieve the content item or any residual data from it.
> >
> > I conducted the following test series:
> > 1. Upload a test "classified" word document into SPS 2003.
> > 2. Delete the test "classified" word document via SPS 2003 (at this point
> > all the DB rows associated with the document are gone - can't see them in
> > EM/Query Analyzer). Text of data file still contains the text of the document.
> > 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> > the text of the document.
> > 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> > file still contains the text of the document.
> >
> > Only idea I've found is scripting out the DB and rebuilding it, but this is
> > DML-based, which would void support for SPS. Any other ideas?|||Are you opening 'data files' of the SQL Server Database?
I am sorry if my question looks stupid.
"BAG" wrote:
> Didn't have to do anything fancy to do the verification, actually. Using a
> text editor robust enough to allow me to view the large data files (I've used
> TextPad, UltraEdit 32, and Boxer on various occasions), I did the following:
> 1. Opened the file.
> 2. Used the Find function to search for marker text strings I placed in the
> document.
> 3. Found the marker text strings. These were embedded in the full text of
> the document, which was present in its entirety.
> -B
> "Absar Ahmad" wrote:
> > How are you verifying that ' Text of data file still contains the text of the
> > document'?
> >
> > "BAG" wrote:
> >
> > > Hi all. I'm attempting to generate policies and procedures for dealing with
> > > the possibility of classified data contamination in a SPS 2003 deployment,
> > > which uses SQL 2000 for storage of all content. By "classified data
> > > contamination", I mean that a user accidentally puts classified data into an
> > > unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> > > - just mentioning SPS 2003 for background info.
> > >
> > > Basically, I need to make sure that once a content item is deleted via SPS
> > > 2003 (which deletes the associated rows for the content item from the SQL
> > > 2000 content DB - yes, I have checked), that it's extremely difficult to
> > > retrieve the content item or any residual data from it.
> > >
> > > I conducted the following test series:
> > > 1. Upload a test "classified" word document into SPS 2003.
> > > 2. Delete the test "classified" word document via SPS 2003 (at this point
> > > all the DB rows associated with the document are gone - can't see them in
> > > EM/Query Analyzer). Text of data file still contains the text of the document.
> > > 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> > > the text of the document.
> > > 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> > > file still contains the text of the document.
> > >
> > > Only idea I've found is scripting out the DB and rebuilding it, but this is
> > > DML-based, which would void support for SPS. Any other ideas?|||Exactly. In this case, the DB in question just has one data file (.mdf) and
one log file
(.ldf). I found the text of the document in the .mdf even after I confirmed
it was deleted in the tables.
-B
"Absar Ahmad" wrote:
> Are you opening 'data files' of the SQL Server Database?
> I am sorry if my question looks stupid.
> "BAG" wrote:
> > Didn't have to do anything fancy to do the verification, actually. Using a
> > text editor robust enough to allow me to view the large data files (I've used
> > TextPad, UltraEdit 32, and Boxer on various occasions), I did the following:
> >
> > 1. Opened the file.
> > 2. Used the Find function to search for marker text strings I placed in the
> > document.
> > 3. Found the marker text strings. These were embedded in the full text of
> > the document, which was present in its entirety.
> >
> > -B
> >
> > "Absar Ahmad" wrote:
> >
> > > How are you verifying that ' Text of data file still contains the text of the
> > > document'?
> > >
> > > "BAG" wrote:
> > >
> > > > Hi all. I'm attempting to generate policies and procedures for dealing with
> > > > the possibility of classified data contamination in a SPS 2003 deployment,
> > > > which uses SQL 2000 for storage of all content. By "classified data
> > > > contamination", I mean that a user accidentally puts classified data into an
> > > > unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> > > > - just mentioning SPS 2003 for background info.
> > > >
> > > > Basically, I need to make sure that once a content item is deleted via SPS
> > > > 2003 (which deletes the associated rows for the content item from the SQL
> > > > 2000 content DB - yes, I have checked), that it's extremely difficult to
> > > > retrieve the content item or any residual data from it.
> > > >
> > > > I conducted the following test series:
> > > > 1. Upload a test "classified" word document into SPS 2003.
> > > > 2. Delete the test "classified" word document via SPS 2003 (at this point
> > > > all the DB rows associated with the document are gone - can't see them in
> > > > EM/Query Analyzer). Text of data file still contains the text of the document.
> > > > 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> > > > the text of the document.
> > > > 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> > > > file still contains the text of the document.
> > > >
> > > > Only idea I've found is scripting out the DB and rebuilding it, but this is
> > > > DML-based, which would void support for SPS. Any other ideas?

How to completely delete any trace of data from a SQL2000 data fil

Hi all. I'm attempting to generate policies and procedures for dealing with
the possibility of classified data contamination in a SPS 2003 deployment,
which uses SQL 2000 for storage of all content. By "classified data
contamination", I mean that a user accidentally puts classified data into an
unclassified system, which is a major no-no. This is purely a SQL 2000 issue
- just mentioning SPS 2003 for background info.
Basically, I need to make sure that once a content item is deleted via SPS
2003 (which deletes the associated rows for the content item from the SQL
2000 content DB - yes, I have checked), that it's extremely difficult to
retrieve the content item or any residual data from it.
I conducted the following test series:
1. Upload a test "classified" word document into SPS 2003.
2. Delete the test "classified" word document via SPS 2003 (at this point
all the DB rows associated with the document are gone - can't see them in
EM/Query Analyzer). Text of data file still contains the text of the document.
3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
the text of the document.
4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
file still contains the text of the document.
Only idea I've found is scripting out the DB and rebuilding it, but this is
DML-based, which would void support for SPS. Any other ideas?
How are you verifying that ' Text of data file still contains the text of the
document'?
"BAG" wrote:

> Hi all. I'm attempting to generate policies and procedures for dealing with
> the possibility of classified data contamination in a SPS 2003 deployment,
> which uses SQL 2000 for storage of all content. By "classified data
> contamination", I mean that a user accidentally puts classified data into an
> unclassified system, which is a major no-no. This is purely a SQL 2000 issue
> - just mentioning SPS 2003 for background info.
> Basically, I need to make sure that once a content item is deleted via SPS
> 2003 (which deletes the associated rows for the content item from the SQL
> 2000 content DB - yes, I have checked), that it's extremely difficult to
> retrieve the content item or any residual data from it.
> I conducted the following test series:
> 1. Upload a test "classified" word document into SPS 2003.
> 2. Delete the test "classified" word document via SPS 2003 (at this point
> all the DB rows associated with the document are gone - can't see them in
> EM/Query Analyzer). Text of data file still contains the text of the document.
> 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> the text of the document.
> 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> file still contains the text of the document.
> Only idea I've found is scripting out the DB and rebuilding it, but this is
> DML-based, which would void support for SPS. Any other ideas?

How to completely delete any trace of data from a SQL2000 data fil

Hi all. I'm attempting to generate policies and procedures for dealing with
the possibility of classified data contamination in a SPS 2003 deployment,
which uses SQL 2000 for storage of all content. By "classified data
contamination", I mean that a user accidentally puts classified data into an
unclassified system, which is a major no-no. This is purely a SQL 2000 issue
- just mentioning SPS 2003 for background info.
Basically, I need to make sure that once a content item is deleted via SPS
2003 (which deletes the associated rows for the content item from the SQL
2000 content DB - yes, I have checked), that it's extremely difficult to
retrieve the content item or any residual data from it.
I conducted the following test series:
1. Upload a test "classified" word document into SPS 2003.
2. Delete the test "classified" word document via SPS 2003 (at this point
all the DB rows associated with the document are gone - can't see them in
EM/Query Analyzer). Text of data file still contains the text of the documen
t.
3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
the text of the document.
4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
file still contains the text of the document.
Only idea I've found is scripting out the DB and rebuilding it, but this is
DML-based, which would void support for SPS. Any other ideas?How are you verifying that ' Text of data file still contains the text of th
e
document'?
"BAG" wrote:

> Hi all. I'm attempting to generate policies and procedures for dealing wit
h
> the possibility of classified data contamination in a SPS 2003 deployment,
> which uses SQL 2000 for storage of all content. By "classified data
> contamination", I mean that a user accidentally puts classified data into
an
> unclassified system, which is a major no-no. This is purely a SQL 2000 iss
ue
> - just mentioning SPS 2003 for background info.
> Basically, I need to make sure that once a content item is deleted via SPS
> 2003 (which deletes the associated rows for the content item from the SQL
> 2000 content DB - yes, I have checked), that it's extremely difficult to
> retrieve the content item or any residual data from it.
> I conducted the following test series:
> 1. Upload a test "classified" word document into SPS 2003.
> 2. Delete the test "classified" word document via SPS 2003 (at this point
> all the DB rows associated with the document are gone - can't see them in
> EM/Query Analyzer). Text of data file still contains the text of the docum
ent.
> 3. Shrink the DB via DBCC SHRINKDATABASE. Text of data file still contains
> the text of the document.
> 4. Shrink the individual files in the DB via DBCC SHRINKFILE. Text of data
> file still contains the text of the document.
> Only idea I've found is scripting out the DB and rebuilding it, but this i
s
> DML-based, which would void support for SPS. Any other ideas?

how to clean all messages of a queue ?

How to clean all messages of a queue ?

Online books says it can be done as below:
RECEIVE * FROM ExpenseQueue

But it only delete a row every time,and I found all the three statement only delete a row:

RECEIVE * FROM ExpenseQueue
RECEIVE TOP(1) * FROM ExpenseQueue
RECEIVE TOP(n) * FROM ExpenseQueue

The RECEIVE statement (as it exists today) only fetches messages belonging to a single conversation group at a time. So the only way to get rid of all messages is to call RECEIVE in a loop:

WHILE (1)
BEGIN
RECEIVE * FROM ExpenseQueue
IF (@.@.ROWCOUNT = 0)
BREAK
END

Are you just looking at means to clean up the queue, or do you really want to fetch multiple conversation groups in a single batch. If it is the latter, could you explain your requirements further to help us improve the product in future versions?

Thanks,
Rushi
--
Developer, Service Broker Team, SQL Server Engine
Microsoft Corporation

|||Rushi's answer assumes that you indeed just want to get rid of the messages and leave the conversations active. If you also want to clean up the conversations - say you have run a bunch of tests and found your application doesn't work correctly - you might want to try one of these scripts which work in ascending dgrees of severity. If you truely don't care at all about the messages or conversations, try the last one:

-- End conversations that are in an error state

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'ER'

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

-- End conversations that are half closed

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints where state = 'DI'

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

-- End all conversations

declare @.handle uniqueidentifier

declare conv cursor for select conversation_handle from sys.conversation_endpoints

open conv

fetch NEXT FROM conv into @.handle

while @.@.FETCH_STATUS = 0

Begin

END Conversation @.handle with cleanup

fetch NEXT FROM conv into @.handle

End

close conv

deallocate conv

|||scripts of Roger_MS can solve my problem, but seems it clean all messages of all queues. If I want to clean a queue only, should I use my Queue_Name to replace sys.conversation_endpoints ? Just as

declare conv cursor for select conversation_handle from Queue_Name

And I think it is convenient to use a statement like "alter queue XXX with cleanup" .

Thank you.|||That would clean up one end of the conversations in the queue. Keep in mind that conversations always involve 2 queues so if you want to clean up the conversations you will have to clean up both queues. This is definitely not something you want to do regularly. Clearing out a queue is equivalent to truncating a table - there will often be valuable data destroyed if you do this so this would generally be something you do only during development.