Check if primary key exists

Hi!

I have created a formview which I among other things uses to insert new values into a database. What I want to check is if the primary key which is put into the form already exists in the db. If it is I want to get a message to my web page, if not the data can be inserted.

How can I do this?

And if the only way to control this is to create a stored procedure. How do I write such a proc?

If you attempt to insert a duplicate value into a PK field, an exception will be thrown. Why not set it to auto-increment and avoid all of this?

HTH,
Ryan

Check for inactive database

Hi

I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).

What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.

Cheers

AFAIK ....there is no simple method or query to find which database is used when. The best method is to run profiler/Trace and see

Madhu

|||You can check the last modified date of the phyiscal files. Im not sure abt this..
|||

Hey

Thanks for your responses. As suggested, I am using Profiler to watch the db for a period of time.

Cheers

Alec

|||When I face the problem you are facing, I change the status of the database to Offline, and then see if anybody notices. If nobody complains after a week/month or so, I remove the databases. If someone screams, it is then easy to identify who is using the database you think should not be in use.sql

Check for inactive database

Hi

I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).

What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.

Cheers

AFAIK ....there is no simple method or query to find which database is used when. The best method is to run profiler/Trace and see

Madhu

|||You can check the last modified date of the phyiscal files. Im not sure abt this..
|||

Hey

Thanks for your responses. As suggested, I am using Profiler to watch the db for a period of time.

Cheers

Alec

|||When I face the problem you are facing, I change the status of the database to Offline, and then see if anybody notices. If nobody complains after a week/month or so, I remove the databases. If someone screams, it is then easy to identify who is using the database you think should not be in use.

Check for inactive database

Hi

I am working at a site that has no dedicated DBA, and our Sql Server 2000 environment has recently been playing up. There are several databases on the Production server that should be inactive (for applications no longer used).

What is the best way for me to see if there has been any recent activity on these databases (short of taking them offline and waiting for screams). Please keep in mind that I am a Sql Server novice.

Cheers

AFAIK ....there is no simple method or query to find which database is used when. The best method is to run profiler/Trace and see

Madhu

|||You can check the last modified date of the phyiscal files. Im not sure abt this..
|||

Hey

Thanks for your responses. As suggested, I am using Profiler to watch the db for a period of time.

Cheers

Alec

|||When I face the problem you are facing, I change the status of the database to Offline, and then see if anybody notices. If nobody complains after a week/month or so, I remove the databases. If someone screams, it is then easy to identify who is using the database you think should not be in use.

Check constraint does not work (compare with null)

Hi!

I have a table with a check constraint. But unfortunately it does not
work like I wanted.

CREATE TABLE MAP
(
[R_ID] [T_D_ID] NOT NULL,
[R_ID1] [T_D_ID] NULL,
CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),
CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID])
)

R_ID1 should always have the value of R_ID or Null
The following statements should cause errors:

insert into map (R_ID, R_ID1)values(1,2);
update map set R_ID1=3 where R_ID=1;

But there occur no errors. Does anyone have an idea? It is an SQL Server
2000.

TIA
SusanneChange it to:

CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Susanne Klemm" <Susanne.Klemm@.appliedsystems.de> wrote in message
news:441e9f63$0$43596$bfcc4b32@.reader.news.celox.d e...
Hi!

I have a table with a check constraint. But unfortunately it does not
work like I wanted.

CREATE TABLE MAP
(
[R_ID] [T_D_ID] NOT NULL,
[R_ID1] [T_D_ID] NULL,
CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 = NULL),
CONSTRAINT [PK_MAP] PRIMARY KEY ([R_ID])
)

R_ID1 should always have the value of R_ID or Null
The following statements should cause errors:

insert into map (R_ID, R_ID1)values(1,2);
update map set R_ID1=3 where R_ID=1;

But there occur no errors. Does anyone have an idea? It is an SQL Server
2000.

TIA
Susanne|||Your constraint should be

CONSTRAINT CHECK_ID1 CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),|||Tom Moreau wrote:
> Change it to:
> CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .

Change it to:

CHECK (R_ID1 = R_ID)

The UNKNOWN case where R_ID1 is null will still be permitted.

Better still, get rid of R_ID1, which is apparently redundant - except
maybe if it is part of a foreign key. In the case of a foreign key I
would still look for a better design without the nullable column.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Doh! Coffee... I need coffee...

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1142860722.911871.308570@.v46g2000cwv.googlegr oups.com...
Tom Moreau wrote:
> Change it to:
> CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),
> --
> Tom
> ----------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .

Change it to:

CHECK (R_ID1 = R_ID)

The UNKNOWN case where R_ID1 is null will still be permitted.

Better still, get rid of R_ID1, which is apparently redundant - except
maybe if it is part of a foreign key. In the case of a foreign key I
would still look for a better design without the nullable column.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Tom Moreau wrote:
> Change it to:
> CHECK (R_ID1 = R_ID OR R_ID1 IS NULL),

Thank you, this worked.

Susanne|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Change it to:
> CHECK (R_ID1 = R_ID)
> The UNKNOWN case where R_ID1 is null will still be permitted.

Actually, the data-modelling tool that I use, PowerDesiger 9.5, insist on
adding IS NULL conditions to all my column constraints for my nullable
columns. I would guess the reason for this is that there was a bug in SQL
2000 RTM where NULL values actually can give you constraint violations.
(There is a similar bug with rules that has been around since SQL 7 RTM,
and I suspect never will get fixed.)

> Better still, get rid of R_ID1, which is apparently redundant - except
> maybe if it is part of a foreign key. In the case of a foreign key I
> would still look for a better design without the nullable column.

To me it looks like a funny sort of bit column, as there are only two
possible values. But maybe Susanne only gave us a scaled-down example,
and the resl-world table looks a little different.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Cheap solution for .Net/sql giving high availability?

Hi
I am looking into finding a cheap solution for a .Net/sql server web
app that gives high availability and hardware redundancy.
Given that performance isn't a great issue, I was wondering if the
following would work:
Two servers running Windows 2003 Standard and clustered using Network
Load Balancing (NLB). Each server running SQL 2005 Standard and
configured so that one server mirrors the other.
Would this configuration mean if either server failed the application
would keep working on the remaining server without any intervention? If
not what other options do I have? Having one active server with another
as a backup would also be fine but I assume this would require some
maintenance to switch the servers over when a failure occurs.
In terms of the actual servers I was looking at the Dell PowerEdge 860
Quad Core Intel Xeon X3210 2.13GHz as these would only need one sql
server licence per server. Storage using RAID 1 for more redundancy and
a UPS for short power outages. I know I could get cheaper servers but
the largest cost would be SQL server 2005 (approx 4100) so getting a
2 server solution would be Ideal.
Any more suggestions on setup or hardware would be appreciated.
Thanks
Henry
If you're using SQL Server 2005, why not investigate Failover Clustering or
Database Mirroring? Both are available on the standard edition (with
limitations compared to enterprise ed).
In what you described below, you have 2 physically independent databases
running (NLB doesn't mean anything to SQL Server). How would you keep them
both in sync? You mentioned configured so that they mirror each other, did
you mean using DB Mirroring or something else? This is not a trivial issue.
With homegrown solution you need to ensure you don't get into situations
where data is out of sync but your application/users are not aware of it.
HP has some nifty cluster-in-a-box solutions with the DL38x and DL58x
(beefier) platforms. They're relatively inexpensive. Haven't worked with
Dell hardware in some time but they probably have similar offerings also.
If you don't want to deal with Failover Clustering, you can get just the 2
servers and implement DB Mirroring.
In your case, I'd recommend DB Mirroring.
joe.
"Henry" <henry.nelson@.gmail.com> wrote in message
news:1169572502.397351.275050@.m58g2000cwm.googlegr oups.com...
Hi
I am looking into finding a cheap solution for a .Net/sql server web
app that gives high availability and hardware redundancy.
Given that performance isn't a great issue, I was wondering if the
following would work:
Two servers running Windows 2003 Standard and clustered using Network
Load Balancing (NLB). Each server running SQL 2005 Standard and
configured so that one server mirrors the other.
Would this configuration mean if either server failed the application
would keep working on the remaining server without any intervention? If
not what other options do I have? Having one active server with another
as a backup would also be fine but I assume this would require some
maintenance to switch the servers over when a failure occurs.
In terms of the actual servers I was looking at the Dell PowerEdge 860
Quad Core Intel Xeon X3210 2.13GHz as these would only need one sql
server licence per server. Storage using RAID 1 for more redundancy and
a UPS for short power outages. I know I could get cheaper servers but
the largest cost would be SQL server 2005 (approx 4100) so getting a
2 server solution would be Ideal.
Any more suggestions on setup or hardware would be appreciated.
Thanks
Henry

Char compare to String

hi
I using C# to write a ASP.NET page
and I need to compare a string variable to a Char field in the SQL table in
SQL Server 2000
how can I write a SQL statement to do that?\
I try to use "SELECT Name from Employee Where pass="+password
it shows me an "Invalid column name error"
How can I fix that ?
ThanksYou have to put the value between apostrophes.
"SELECT Name from Employee Where pass = '" + password + "'"
in sql is should look like:
select [name] from employee where pass = '@.#WE$%'
AMB
"Lam" wrote:

> hi
> I using C# to write a ASP.NET page
> and I need to compare a string variable to a Char field in the SQL table i
n
> SQL Server 2000
> how can I write a SQL statement to do that?\
> I try to use "SELECT Name from Employee Where pass="+password
> it shows me an "Invalid column name error"
> How can I fix that ?
> Thanks
>
>|||Hi
name is a reserved SQL keyword, so you have put put it in brackets like
Alejandro indicated.
Regards
Mike
"Alejandro Mesa" wrote:
> You have to put the value between apostrophes.
> "SELECT Name from Employee Where pass = '" + password + "'"
> in sql is should look like:
> select [name] from employee where pass = '@.#WE$%'
>
> AMB
>
> "Lam" wrote:
>|||Thanks Mike. I thought the error was related to the comparison, but also was
trying to indicate what you said (my fault, I did it in t-sql, not in
english).
AMB
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> name is a reserved SQL keyword, so you have put put it in brackets like
> Alejandro indicated.
> Regards
> Mike
> "Alejandro Mesa" wrote:
>

Chaning an Instance name

Hi
I have a quick question about renaming an instance.
I used sp_dropserver and dropped the instance name
I used sp_addserver '<servername>, 'local' and changed the local instance
name.
After stopping and restarting the instance, i was able to connect to the new
instance name.
But i can still connect using the old instance name also.
I want to drop the old instance name completely.
How do i do that?
--Uday
See question number 5 in:
Frequently asked questions - SQL Server 2000 - multiple instances
http://support.microsoft.com/newsgroups/default.aspx
AMB
"Udayasree" wrote:

> Hi
> I have a quick question about renaming an instance.
> I used sp_dropserver and dropped the instance name
> I used sp_addserver '<servername>, 'local' and changed the local instance
> name.
> After stopping and restarting the instance, i was able to connect to the new
> instance name.
> But i can still connect using the old instance name also.
> I want to drop the old instance name completely.
> How do i do that?
> --Uday

Chaning an Instance name

Hi
I have a quick question about renaming an instance.
I used sp_dropserver and dropped the instance name
I used sp_addserver '<servername>, 'local' and changed the local instance
name.
After stopping and restarting the instance, i was able to connect to the new
instance name.
But i can still connect using the old instance name also.
I want to drop the old instance name completely.
How do i do that?
--UdaySee question number 5 in:
Frequently asked questions - SQL Server 2000 - multiple instances
http://support.microsoft.com/newsgroups/default.aspx
AMB
"Udayasree" wrote:

> Hi
> I have a quick question about renaming an instance.
> I used sp_dropserver and dropped the instance name
> I used sp_addserver '<servername>, 'local' and changed the local instance
> name.
> After stopping and restarting the instance, i was able to connect to the n
ew
> instance name.
> But i can still connect using the old instance name also.
> I want to drop the old instance name completely.
> How do i do that?
> --Uday

Changing/Moving SQL Server 2000 to new Domain

Hi
I wonder if the follwoing operation can be done ?
Can I move SQL Server 2000 running on Windows 2000 from
one Domain to another ? Will the server and DB's will be
functional ?
Thx
Bar
Hi,
No issues, Only thing is you need to create a Trust relationship with old
domain, So that users in the old domain also can access.
But the following things also need to be considered
1. If the SQL server in new domain is a new name then, Issue the below
commands based on the new server name
sp_dropserver <Old server name>
go
sp_addserver <new server name>,Local
2. If it is new ip address
Change the connectiction string in your application server or connection
file
3. If you are using any server alias using TCP/IP
Modify the existing alias with new IP address using "CLient Network
utility"
Thanks
Hari
MCDBA
"Bar" <anonymous@.discussions.microsoft.com> wrote in message
news:701101c42fcf$29f05ad0$a601280a@.phx.gbl...
> Hi
> I wonder if the follwoing operation can be done ?
> Can I move SQL Server 2000 running on Windows 2000 from
> one Domain to another ? Will the server and DB's will be
> functional ?
> Thx
> Bar

Changing/Moving SQL Server 2000 to new Domain

Hi
I wonder if the follwoing operation can be done '
Can I move SQL Server 2000 running on Windows 2000 from
one Domain to another ? Will the server and DB's will be
functional '
Thx
BarHi,
No issues, Only thing is you need to create a Trust relationship with old
domain, So that users in the old domain also can access.
But the following things also need to be considered
----
1. If the SQL server in new domain is a new name then, Issue the below
commands based on the new server name
sp_dropserver <Old server name>
go
sp_addserver <new server name>,Local
2. If it is new ip address
Change the connectiction string in your application server or connection
file
3. If you are using any server alias using TCP/IP
Modify the existing alias with new IP address using "CLient Network
utility"
Thanks
Hari
MCDBA
"Bar" <anonymous@.discussions.microsoft.com> wrote in message
news:701101c42fcf$29f05ad0$a601280a@.phx.gbl...
> Hi
> I wonder if the follwoing operation can be done '
> Can I move SQL Server 2000 running on Windows 2000 from
> one Domain to another ? Will the server and DB's will be
> functional '
> Thx
> Bar