Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
Mike
Please do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

Please help debug.

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikePlease do not multi post.
Syntax usage is incorrect. Please use the below code to modify NOT NULL to
NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

PLEASE HELP aspnet_regsql.exe - what does it require to run stand-alone?

Does anyone know what aspnet_regsql.exe requires to run stand-alone? I have to apply the .NET 2.0 Management/Role API to a sql2000 database on a machine that does not have the .net 2.0 framework installed.

Hi,

you can use the tool to generate a .sql file that you can execute on your database with SQL analyzer.

aspnet_regsql.exe -E -S localhost -d pubs -A all -sqlexportonly c:\membership.sql

This will create the necessary SQL statements for membership, providers, users, ... for the pubs database. The output of it, a .sql file, will be written to the C:\ root in this little example.

Grz, Kris.

|||You dee man. Thank you!|||Learn something new everyday, thanks XIII.

PLEASE HELP !>> 2145 Locks, displayed from SP_LOCK 143

HI, Can someone PLEASE HELP ME to better understand what is happening in the
SQL2000 SP4 database when someone runs a posting process that creates 2145
locks. There are about 300 users connected to this database, entering
transactions all day and some through the night, and most of the time
everything runs along smoothly, I've seen 300 - 600 locks on normal OPs, but
when a posting is run (I know little about that actual application) it
creates thousands of locks and transactions grind to a halt. I've been
monitoring the locks while writing this and that one SPID has a death grip
on 2145 locks, this has been for at least 15 minutes. I've been the SLQ DBA
here for just under a year, and this is one of many issues that needs
attention. IS THIS AN APPLICATION THING, IS THIS SOMETHING THAT I NEED TO
CHANGE ON THE SQL CONFIGURATION, >> WHAT CAN I DO? IS THIS AN
ENGINEERING QUESTION'Check the Query from that particular SPID (user) via Profiler.
to immideatly resolve the issue: kill the SPId that is Blocking:
(Carefully examine that this particular SPID for other blocking
scenarios)
Read more about NOLOCK and ISOLATION LEVELS.
Get the developers to use this often if there are heavy transactions,
specially for "select" stuff.
Get a nolock on all SPs that this application or the Posting Process
uses.
More about blocking in BOL:
Maninder
MCDBA|||THANK YOU!! It took about 20 minutes for this process to complete, and then
all locks cleared. The Developers are another Company and discussing how
there app works, is not easy. Would you have any ideas as to why a process
that effects maybe 15,000 entries, would lock 2145 records (am I correct to
assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
that process') and takes 20 minutes.
Server is Win2003 x64, 4g Ram, Xeon 3.0 GHz Dual Proc, with HyperThreading
On(shows 4 Proc's in TaskManager), Dual gigabit NICs, SQL2000 SP4.
=========================================="Maninder" <msdhanjal@.gmail.com> wrote in message
news:1170345674.343261.97400@.a75g2000cwd.googlegroups.com...
> Check the Query from that particular SPID (user) via Profiler.
> to immideatly resolve the issue: kill the SPId that is Blocking:
> (Carefully examine that this particular SPID for other blocking
> scenarios)
> Read more about NOLOCK and ISOLATION LEVELS.
> Get the developers to use this often if there are heavy transactions,
> specially for "select" stuff.
> Get a nolock on all SPs that this application or the Posting Process
> uses.
> More about blocking in BOL:
> Maninder
> MCDBA
>|||WANNABE wrote:
> THANK YOU!! It took about 20 minutes for this process to complete, and then
> all locks cleared. The Developers are another Company and discussing how
> there app works, is not easy. Would you have any ideas as to why a process
> that effects maybe 15,000 entries, would lock 2145 records (am I correct to
> assume that SP_LOCK 143 shows me the number of RECORDS that are locked by
> that process') and takes 20 minutes.
>
No, it shows you the number of LOCKS that the process has in place. You
need to look at the lock type to determine if they are rows, pages,
tables, etc...
My guess is that there are missing or inadequate indexes on some of the
tables involved in this posting process, which result in table locks. I
would pursue this before going with NOLOCK. Using NOLOCK is an
often-abused band-aid for poorly designed systems. It exposes you to
possible reading "dirty" data, and should be used carefully.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

planning a replication

hallo
we have a sql2000 in the headquarter, and 3 new remote branches opening in few weeks with no sql servers installed yet.
We plan to setup a merge replica. The headquarter will have the highest activity on the replicated tables.
Shall we use all SQL2000 in the remote branches? Or could we use SQL2005, using a 2005 as a publisher and distributor?
TIA

If possible, upgrade all your nodes to SQL 2005. Merge replication performs and scales much better with the new pre-computed partitions. If this is not possible, then the only supported configuration is that the version of the distributor >= version of the publisher >= version of the subscriber. You can find more info about this in topic Using Multiple Versions of SQL Server in a Replication Topology".

|||ok thanks.
we're not going to upgrade the only sql2000 server we have, but i'd prefere to use sql2005 in the remote branches: in this scenario, i could set one of the 2005 as distributor and publisher.
This would be in contrast with the network topology, that centers everything in the main plant; BTW there will not be a big traffic of replicated data: a theoretical maximum of about 10 records/minute.