Check OS Users

Hello,
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.
You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:

>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.
|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results

> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.
|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:

> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
>
>

Check OS Users

Hello,
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:
>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results
> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:
> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
> >Hello,
> >
> >i need to get information about users that belong to the local
> >administrators operating system group.
> >
> >I execute the following procedure and got my information:
> >
> >exec master..xp_cmdshell 'net localgroup administrators'
> >
> >The question is that i need to get this information without the NULLs and
> >the output lines that appear.
> >Is it possible? Is there any other way to get the same information?
> >
> >Thanks and best regards.
>

Check OS Users

Hello,
i need to get information about users that belong to the local
administrators operating system group.
I execute the following procedure and got my information:
exec master..xp_cmdshell 'net localgroup administrators'
The question is that i need to get this information without the NULLs and
the output lines that appear.
Is it possible? Is there any other way to get the same information?
Thanks and best regards.You can also try using the following in query analyzer if
the builtin\administrators group hasn't been removed:
exec master..xp_logininfo 'BUILTIN\Administrators',
'MEMBERS'
-Sue
On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
<CCJM@.discussions.microsoft.com> wrote:

>Hello,
>i need to get information about users that belong to the local
>administrators operating system group.
>I execute the following procedure and got my information:
>exec master..xp_cmdshell 'net localgroup administrators'
>The question is that i need to get this information without the NULLs and
>the output lines that appear.
>Is it possible? Is there any other way to get the same information?
>Thanks and best regards.|||> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible?
You can use INSERT ... EXEC to insert the results into a table and filter as
needed:
CREATE TABLE #Results(OutputLine varchar(8000))
INSERT INTO #Results
EXEC master..xp_cmdshell 'net localgroup administrators'
SELECT *
FROM #Results
WHERE OutputLine IS NOT NULL AND
OutputLine NOT LIKE '-%' AND
OutputLine <> 'The command completed successfully.'
DROP TABLE #Results

> Is there any other way to get the same information?
Consider using application code rather than Transact-SQL. API's like WMI
are much more robust for this sort of thing.
Hope this helps.
Dan Guzman
SQL Server MVP
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:0DEE4267-7967-4552-8F42-7A8E0E6BCCD6@.microsoft.com...
> Hello,
> i need to get information about users that belong to the local
> administrators operating system group.
> I execute the following procedure and got my information:
> exec master..xp_cmdshell 'net localgroup administrators'
> The question is that i need to get this information without the NULLs and
> the output lines that appear.
> Is it possible? Is there any other way to get the same information?
> Thanks and best regards.|||Thanks Sue,
Best regards.
"Sue Hoegemeier" wrote:

> You can also try using the following in query analyzer if
> the builtin\administrators group hasn't been removed:
> exec master..xp_logininfo 'BUILTIN\Administrators',
> 'MEMBERS'
> -Sue
> On Wed, 9 Mar 2005 05:09:06 -0800, "CC&JM"
> <CCJM@.discussions.microsoft.com> wrote:
>
>

Check if file is already open

Hi,

I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel.

So I want to include a code that checks if file "file.xls" is open on user's machine.

If file is open, then message "file "File.xls" is already open. Generating File_1.xls"

Run the code but create the file with file name "file_1.xls"

If file doesn't exist, then run code and create file with file name "File.xls"

So basically I want the code to run and generate the file. Only difference is that if file with same name is already open, then just rename the newly created file.

Here's the code I've created for generating the file:

Public Function getrmpricing()
Dim queryoption As String
Dim ans, Msg As String
Dim fs As Object
Dim sTemplateFile As String
Dim e_TemplateFile As String

On Error Resume Next

sTemplateFile = g_dashboard & "crm proposal input.XLT"
e_TemplateFile = "C:\"

If Forms!rmpricingdataform!BU = "CS" Then
MsgBox "No template available for CS!", vbOKOnly, "RM Pricing Report"
Else

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile sTemplateFile, e_TemplateFile, True

Dim xl As New Excel.Application
xl.Workbooks.Open e_TemplateFile & "crm proposal input.XLT"

DoCmd.OutputTo acOutputQuery, "CustPricingbyRMCrosstabquery", acFormatXLS, "c:\customerpricing.xls", True

Dim xs As New Excel.Application
xs.Workbooks("customerpricing").Activate
xs.ActiveWorkbook.Activate
Select Case Forms!rmpricingdataform!BU

Case "CRM"
xl.Run "'crm proposal input.XLT'!CRM_CAPSPriceTemplate.CRM_CAPSPriceTemplate"

End Select
'xs.Workbooks.CLOSE - NEWLY COMMENTED OUT
xl.Workbooks("crm proposal input.XLT").CLOSE
'xl.Workbooks("crmpricing.xls").Save - NEVER USED

'fs.DeleteFile e_TemplateFile & "crm proposal input.XLT", True - NEWLY COMMENTED OUT
Set fs = Nothing

DoCmd.CLOSE acForm, "rmpricingdataform"
Call AuditTrail("RM Pricing report", "Execute")
End If

End Function

Please advise.

Not the right forum, try this one

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?query=Excel&dg=&cat=en_US_d02fc761-3f6b-402c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us

sql

Check if a windows/nt user account exists

I have some script which creates/grant priveleges to windows/nt users to DB but i frequently get the message below:

Windows NT user or group '<user or group>' not found. Check the name again.

I understand that this is because the said user/group is indeed not present in the environment I'm running the script (ie. testing and production environment).

But is it possible to have some sort of checking whether the user/group exists in the environment so that I could determine whether or not to call some lines of code?

Not sure if this is possible so i had to ask. c",)

SELECT *

FROM sysusers

will show the users from the current database. There are columns like isntname and isntgroup that will tell you if it is an NT user name or group. There are more than just the normal users, so take a look at what is in that table.

|||

If i'm not mistaken that will only show users already registered for the current database.

I needed to check if a NT user name/group is valid/exists in the environment rather than if it is registered for the current database.

Appreciate your help but i think i need something else or could you expound how would that apply for my objective?

Thanks in advance

|||

What version of SQL Server are you using? You could use SUSER_SID('<your_windows_login_name>' and check for non-null value. But this method depends on your configuration - the SQL Server service account, member server or standalone server etc. So there is no easy way to do this from the database engine.

You could use the ADSI OLEDB provider and perform distributed queries but this depends on your network configuration & whether LDAP is allowed and so on. In SQL Server 2005, you can write SQLCLR function to perform the check but this requires enabling CLR on the server, creating assembly with possibly UNSAFE or minimum of EXTERNAL_ACCESS permissions.

Best is to perform the check outside of the database, filter the list of users and create the logins. Or you can simply perform the error check in TSQL. This is also easier with TRY..CATCH in SQL Server 2005.

|||Thanks, will definitely try to look into these.|||I think i'll just go for the try-catch. Thanks|||Try catch should be fine, but be careful that you don't need speed. Performance will be a bigger concern just catching the error instead of checking for it first.

Check for Date

I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.Hi, Watson
You can use:
a) the ISDATE() function (which accepts any date format),
b) a LIKE expression (which accepts a given pattern, but cannot easily
check if the date is valid), or
c) a combination of the above
For example:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0)
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9]
[0-9][0-9]')
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0
AND USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9]
1;0-9][0-9]')
Razvan
Watson SQL wrote:
> I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.|||You could have a CHECK constraint as shown below:
ALTER TABLE YourTableName ADD CONSTRAINT CheckDate CHECK (ISDATE(ColumNName)
= 1 or ColumnName IS NULL)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.|||>I want to enforce users to enter a date or leave the field null into a
> varchar field.

> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Try:
ALTER TABLE dbo.CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 IS NULL OR ISDATE(USER_7) = 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>|||ALTER TABLE CUST_ORDER_LINE ADD CONSTRAINT constraintname CHECK(ISDATE(USER_
7))
No need to explicitly allow NULL. If a CHECK constraint evaluate to TRUE or
UNK, the modification is
allowed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>

Check for Date

I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.Hi, Watson
You can use:
a) the ISDATE() function (which accepts any date format),
b) a LIKE expression (which accepts a given pattern, but cannot easily
check if the date is valid), or
c) a combination of the above
For example:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0)
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0
AND USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
Razvan
Watson SQL wrote:
> I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.|||You could have a CHECK constraint as shown below:
ALTER TABLE YourTableName ADD CONSTRAINT CheckDate CHECK (ISDATE(ColumNName)
= 1 or ColumnName IS NULL)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.|||>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Try:
ALTER TABLE dbo.CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 IS NULL OR ISDATE(USER_7) = 1)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>|||ALTER TABLE CUST_ORDER_LINE ADD CONSTRAINT constraintname CHECK(ISDATE(USER_7))
No need to explicitly allow NULL. If a CHECK constraint evaluate to TRUE or UNK, the modification is
allowed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>

Check db before move items from listbox1 to listbox2 or save them into db

I have two listboxes: listbox1 retrieve rows from tblProfileUsers and listbox2 receive users from listbox1 to save them into the same table tblProfileUsers, but users must have an unique profile associate to him.

So I need that my web form checks if selected users already are on tblProfileUsers and block operation before they saved with a 2nd or 3rd profiles.

I′m using VSTS with SQL 2005.

Check out the SQLExists function.

|||

hi

first you could useselect cout statement to find that user name on those table you want and read the int , if int was 0 it means that there is no such user in that table and you could add that.

for this you could use SqlCommand , SQL connecrtion and SqlDataReader

Check Data and Index Linkage FAILED

Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode?
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc Ferguson
The message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode? Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson
|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode? Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson
|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>
|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>

Check Data and Index Linkage FAILED

Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you do have a problem, you
don't want an automated routine to fox this (I have an article on this on my website,
www.karaszi.com, but the site seems to be down at the moment, so I can't give exact UTL).
If you do check this option, the database indeed need to be in single user mode. Main plan tries to
set it to single user, but that will fail if any user is in the database, which seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Enterprise Manager open? I
> don't have the live database extended. I normally work on our test database and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You can
> then try to repair any single table at a later data during your maintenance
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
See my response. This is indeed what I was referring to. So you are set now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. What is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should modify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were checking and trying to
>> repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional parameters then the DB must
>> be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You can then try to repair
>> any single table at a later data during your maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
--
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:
>> The message implies you were not just checking for problems but you were
>> checking and trying to repair any problems.
>> If you are doing a DBCC CHECKTABLE with one of the following optional
>> parameters then the DB must be in Single User mode :-
>> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
>> Suggest running DBCC CHECKDB, this will report any errors it finds. You
>> can then try to repair any single table at a later data during your
>> maintenance windows.
>> To put your db into single_user mode :-
>> ALTER DATABASE dbname SET Single_user with rollback immediate
>>
>>|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>> From your response, I assumed that it's the Integrity tab that I
>> should modify. So I unchecked "Attempt to repair any minor
>> problems". Is that correct or am I way off. Thanks again.
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>

Check Data and Index Linkage FAILED

Hi,
I'm running SQL Server 2000 and I have a maintenance plan for our
database to backup every 2 hours. Our users normally get in about 5 AM
to 9 AM. I was checking our logs this morning to make sure things went
smoothly and from 1 AM to 7 AM, everything went fine. At 9 AM it tried
to Check Data and Index Linkage and it failed with this message:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement
not
processed. Database needs to be in single user mode.
After that the 9 AM backup didn't execute. I'm assuming that since
users are logged in, it must have put the database in multi-user mode'
Or is it because I have my Enterprise Manager open? I don't have the
live database extended. I normally work on our test database and then
update the live accordingly. Thanks for any help.
Marc FergusonThe message implies you were not just checking for problems but you were
checking and trying to repair any problems.
If you are doing a DBCC CHECKTABLE with one of the following optional
parameters then the DB must be in Single User mode :-
REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
Suggest running DBCC CHECKDB, this will report any errors it finds. You can
then try to repair any single table at a later data during your maintenance
windows.
To put your db into single_user mode :-
ALTER DATABASE dbname SET Single_user with rollback immediate
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
> to backup every 2 hours. Our users normally get in about 5 AM to 9 AM. I
> was checking our logs this morning to make sure things went smoothly and
> from 1 AM to 7 AM, everything went fine. At 9 AM it tried to Check Data
> and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not
> processed. Database needs to be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
> are logged in, it must have put the database in multi-user mode' Or is it
> because I have my Enterprise Manager open? I don't have the live database
> extended. I normally work on our test database and then update the live
> accordingly. Thanks for any help.
> Marc Ferguson|||Don't check the "fix minor problems" in the maint plan. If it turns out you
do have a problem, you
don't want an automated routine to fox this (I have an article on this on my
website,
www.karaszi.com, but the site seems to be down at the moment, so I can't giv
e exact UTL).
If you do check this option, the database indeed need to be in single user m
ode. Main plan tries to
set it to single user, but that will fail if any user is in the database, wh
ich seems to have been
your case. But, as I mentioned, remove that option and you will be fine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:u8nGs%23CIGHA.2900@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I'm running SQL Server 2000 and I have a maintenance plan for our database
to backup every 2
> hours. Our users normally get in about 5 AM to 9 AM. I was checking our
logs this morning to
> make sure things went smoothly and from 1 AM to 7 AM, everything went fine
. At 9 AM it tried to
> Check Data and Index Linkage and it failed with this message:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statemen
t not processed. Database needs to
> be in single user mode.
> After that the 9 AM backup didn't execute. I'm assuming that since users
are logged in, it must
> have put the database in multi-user mode' Or is it because I have my Ente
rprise Manager open? I
> don't have the live database extended. I normally work on our test databa
se and then update the
> live accordingly. Thanks for any help.
> Marc Ferguson|||Thanks Ryan for your response. I have a question though..
I'm not a real guru with MS SQL Server's maintenance and extra features.
What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to
get to my maintenance plan:
Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management | Database Maintenance Plans
From there I created a new plan with:
1. Optimizations (w/ defaults)
2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1
AM to 11:59:59 PM)
4. Transaction Log Backup and Reporting w/ defaults.
From your response, I assumed that it's the Integrity tab that I should
modify. So I unchecked "Attempt to repair any minor problems". Is that
correct or am I way off. Thanks again.
Marc F.
Ryan wrote:
> The message implies you were not just checking for problems but you were
> checking and trying to repair any problems.
> If you are doing a DBCC CHECKTABLE with one of the following optional
> parameters then the DB must be in Single User mode :-
> REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD
> Suggest running DBCC CHECKDB, this will report any errors it finds. You ca
n
> then try to repair any single table at a later data during your maintenanc
e
> windows.
> To put your db into single_user mode :-
> ALTER DATABASE dbname SET Single_user with rollback immediate
>
>
>|||> From your response, I assumed that it's the Integrity tab that I should modify. So I unch
ecked
> "Attempt to repair any minor problems". Is that correct or am I way off. Thanks
again.
See my response. This is indeed what I was referring to. So you are set now.
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features. W
hat is DBCC CHECKTABLE?
> Here's my path through Enterprise Manager to get to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host Name] | Management |
> Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should mo
dify. So I unchecked
> "Attempt to repair any minor problems". Is that correct or am I way off.
Thanks again.
> Marc F.
> Ryan wrote:|||If you're in EnterPrise Manager try the following :-
Tools\SQL Query Analyser
USE yourdatabasename
GO
DBCC CHECKDB
It's safe to run this as it won't place any data locks only schema locks
(people will be able to modify data but not change table schema)
It should return to you any tables that have corruption. You can then
attempt to repair this corruption with DBCC CHECKTABLE.
SQL Server Books Online is a good resource to get started with. Alternitavly
have a look at Tibor's site (if it's available now) www.karaszi.com,
HTH. Ryan
"Marc Ferguson" <marc@.digitalalias.net> wrote in message
news:%23SoviQDIGHA.3056@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks Ryan for your response. I have a question though..
> I'm not a real guru with MS SQL Server's maintenance and extra features.
> What is DBCC CHECKTABLE? Here's my path through Enterprise Manager to get
> to my maintenance plan:
> Console Root | Microsoft SQL Servers | SQL Server Group | [SQL Server
Host
> Name] | Management | Database Maintenance Plans
> From there I created a new plan with:
> 1. Optimizations (w/ defaults)
> 2. Integrity (w/ defaults, scheduled to run every Sunday at noon)
> 3. Complete Backup (w/ defaults, scheduled to run every 2 hours from 1 AM
> to 11:59:59 PM)
> 4. Transaction Log Backup and Reporting w/ defaults.
> From your response, I assumed that it's the Integrity tab that I should
> modify. So I unchecked "Attempt to repair any minor problems". Is that
> correct or am I way off. Thanks again.
> Marc F.
> Ryan wrote:|||Thanks Ryan and Tibor.
Tibor, I saw your response after my last post. ;)
Marc F.
Tibor Karaszi wrote:
>
> See my response. This is indeed what I was referring to. So you are set
> now. :-)
>

Check before INSERT

I have a pretty standard form that inserts users name, office, and team. It generates a random 10 digit ID for them. How would i got about checking the table to make sure that ID doesn't exist?

Here's my insert code.

string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(strConnection);

string usercode = GenPassWithCap(9);

String insertCmd = "INSERT into users (ID, firstname, lastname, office, team) values (@.id, @.firstname, @.lastname, @.office, @.team)";
SqlCommand myCommand = new SqlCommand(insertCmd, myConnection);

myCommand.Parameters.Add(new SqlParameter("@.id", SqlDbType.VarChar, 10));
myCommand.Parameters["@.id"].Value = usercode;

myCommand.Parameters.Add(new SqlParameter("@.firstname", SqlDbType.VarChar, 50));
myCommand.Parameters["@.firstname"].Value = txtFirstName.Text;

myCommand.Parameters.Add(new SqlParameter("@.lastname", SqlDbType.VarChar, 50));
myCommand.Parameters["@.lastname"].Value = txtLastName.Text;

myCommand.Parameters.Add(new SqlParameter("@.office", SqlDbType.VarChar, 75));
myCommand.Parameters["@.office"].Value = dwnOffice.SelectedValue;

myCommand.Parameters.Add(new SqlParameter("@.team", SqlDbType.VarChar, 20));
myCommand.Parameters["@.team"].Value = dwnTeam.SelectedValue;

myCommand.Connection.Open();

myCommand.ExecuteNonQuery();

Do I run a completey different select command before hand and try to match that field?

Instead of an adhoc T-SQL like this, you could pass the parameters to a proc and do a check before inserting.

IF NOT EXISTS(SELECT * FROM TableWHERE Id = @.ID AND Lastname = @.LastName And...)

INSERT INTO Users (...)

Check active SQL server 2000 users/connections

How can I check how many users are connected to my SQL 2000 db. Monitor how
many users are active on the database.
regards
simonOn Jul 4, 1:20 pm, Simon79 <Simo...@.discussions.microsoft.com> wrote:
> How can I check how many users are connected to my SQL 2000 db. Monitor ho
w
> many users are active on the database.
> regards
> simon
To check connected sessions
select * from master..sysprocesses where spid > 50
You can check
select spid, datediff(minute,last_batch ,getdate()) from
master..sysprocesses where spid > 50
for how many minutes the session was idle|||Pls try sp_who2 'active'
HTH,
Paul Ibison|||Hi
sp_who 'active'
"Simon79" <Simon79@.discussions.microsoft.com> wrote in message
news:E3126647-65E7-4CDA-B6C8-D4AC4505E165@.microsoft.com...
> How can I check how many users are connected to my SQL 2000 db. Monitor
> how
> many users are active on the database.
> regards
> simon|||Thanx, all your solutions worked great!

Check active SQL server 2000 users/connections

How can I check how many users are connected to my SQL 2000 db. Monitor how
many users are active on the database.
regards
simon
On Jul 4, 1:20 pm, Simon79 <Simo...@.discussions.microsoft.com> wrote:
> How can I check how many users are connected to my SQL 2000 db. Monitor how
> many users are active on the database.
> regards
> simon
To check connected sessions
select * from master..sysprocesses where spid > 50
You can check
select spid, datediff(minute,last_batch ,getdate()) from
master..sysprocesses where spid > 50
for how many minutes the session was idle
|||Pls try sp_who2 'active'
HTH,
Paul Ibison
|||Hi
sp_who 'active'
"Simon79" <Simon79@.discussions.microsoft.com> wrote in message
news:E3126647-65E7-4CDA-B6C8-D4AC4505E165@.microsoft.com...
> How can I check how many users are connected to my SQL 2000 db. Monitor
> how
> many users are active on the database.
> regards
> simon
|||Thanx, all your solutions worked great!

Check active SQL server 2000 users/connections

How can I check how many users are connected to my SQL 2000 db. Monitor how
many users are active on the database.
regards
simonOn Jul 4, 1:20 pm, Simon79 <Simo...@.discussions.microsoft.com> wrote:
> How can I check how many users are connected to my SQL 2000 db. Monitor how
> many users are active on the database.
> regards
> simon
To check connected sessions
select * from master..sysprocesses where spid > 50
You can check
select spid, datediff(minute,last_batch ,getdate()) from
master..sysprocesses where spid > 50
for how many minutes the session was idle|||Pls try sp_who2 'active'
HTH,
Paul Ibison|||Hi
sp_who 'active'
"Simon79" <Simon79@.discussions.microsoft.com> wrote in message
news:E3126647-65E7-4CDA-B6C8-D4AC4505E165@.microsoft.com...
> How can I check how many users are connected to my SQL 2000 db. Monitor
> how
> many users are active on the database.
> regards
> simon|||Thanx, all your solutions worked great!

check & limit number users

Hi,
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
David Gugick
Quest Software
www.quest.com|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
David Gugick
Quest Software

check & limit number users

Hi,
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
David Gugick
Quest Software
www.quest.com|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
David Gugick
Quest Software

check & limit number users

Hi,
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.
js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
David Gugick
Quest Software
www.quest.com
|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com
|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
David Gugick
Quest Software

check & limit number users

Hi,
Can I check and limit number occurrence users connect to sql server? Please
advice.
Thanks.js wrote:
> Hi,
> Can I check and limit number occurrence users connect to sql server?
> Please advice.
> Thanks.
You can set an overall connection limit by changing the Maximum
Concurrent User Connections in SQL EM or using the "User Connections"
parameter with sp_option. If you're asking whether you can say "USER 1"
can only have two concurrent connections and "USER 2" can have only 1
concurrent connection, I don't think that's possible - unless you do
this from your application and check the sysprocesses table (SQL 7 /
2000) or sys.dm_exec_connections table in SQL 2005.
--
David Gugick
Quest Software
www.quest.com|||Thanks David,
Two more questions:
1. from one computer, my app connect to sql server, two database(db1, db2).
how many connections for this scenario?
2. Is it USER and machine the same thing? can I limit the number occurrence
machine?
Thanks again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uUQD6ziFGHA.3900@.TK2MSFTNGP10.phx.gbl...
> js wrote:
>> Hi,
>> Can I check and limit number occurrence users connect to sql server?
>> Please advice.
>> Thanks.
> You can set an overall connection limit by changing the Maximum Concurrent
> User Connections in SQL EM or using the "User Connections" parameter with
> sp_option. If you're asking whether you can say "USER 1" can only have two
> concurrent connections and "USER 2" can have only 1 concurrent connection,
> I don't think that's possible - unless you do this from your application
> and check the sysprocesses table (SQL 7 / 2000) or sys.dm_exec_connections
> table in SQL 2005.
> --
> David Gugick
> Quest Software
> www.quest.com|||js wrote:
> Thanks David,
> Two more questions:
> 1. from one computer, my app connect to sql server, two database(db1,
> db2). how many connections for this scenario?
> 2. Is it USER and machine the same thing? can I limit the number
> occurrence machine?
You don't really connect to a database. You connect to a server (read: a SQL
Server). You have a default database or you may provide one in the
connection string, but you are still connecting to the server. How many
connections depends on your source code. Presumably you have two Connection
objects (ADO) or two SqlConnection classes (.Net). In that case, you
probably have two separate connections. Is there a reason your application
requires two active connections? If it's a matter of accessing objects in
two different databases on the same server, you can eliminate one connection
simply by using fully qualified references (database.owner.object). At the
very least, you should use the owner.object syntax for all objects
referenced in your SQL. There is not need to switch databases, which is an
expensive operation, in order to access objects in a particular database.
To the second question: a user and a connection are not the same thing. A
user is granted access to SQL Server (or not granted access as the case may
be). That user can establish any number of connections.
--
David Gugick
Quest Software

channel for SSL/TLS ????

My users are getting the following error
"The underlying connection was closed: Could not establish secure channel
for SSL/TLS."
any idea'?when ?
when they open a report?
or when they try to access the web server http://server/reports?
Does your reportserver is protected by a firewall?
does the user can access the http://server/reportserver web site?
"Soan" <Soan@.discussions.microsoft.com> wrote in message
news:00C1B137-5CA9-4FD3-B6AA-86E464145CA8@.microsoft.com...
> My users are getting the following error
> "The underlying connection was closed: Could not establish secure channel
> for SSL/TLS."
>
> any idea'?|||Just a guess, but something is probably out of sync in one of your *.config
files. Either an SSL setting or report server name. Check them out under
the MSRS install folders.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jéjé" <willgart_A_@.hotmail_A_.com> wrote in message
news:OAEFqys5EHA.3416@.TK2MSFTNGP09.phx.gbl...
> when ?
> when they open a report?
> or when they try to access the web server http://server/reports?
> Does your reportserver is protected by a firewall?
> does the user can access the http://server/reportserver web site?
> "Soan" <Soan@.discussions.microsoft.com> wrote in message
> news:00C1B137-5CA9-4FD3-B6AA-86E464145CA8@.microsoft.com...
>> My users are getting the following error
>> "The underlying connection was closed: Could not establish secure channel
>> for SSL/TLS."
>>
>> any idea'?
>