Cannot Drop Index and PK Constraints

I am trying to create a new column on a table, but realized that there is already an PK index created. I am unable to drop the PK, or even delete the table. I also am unable to delete the index.Please be more specific - what error messages are you receiving ? Is it because of a system error or a constraint error (like you are deleting a primary key which is a foreign key in another table(s)) ?

Cannot detach suspect db

A db is flagged as suspect. I tried to detach it or take
offline but was unable to as the db is replicated (merge
replication). At the same time I was unable to cancel the
replication because the db wasn't found.
The suspect status was caused by the db failing to close
cleanly after a failed back-up. I also haven't been able
to succesfully run sp_resetstatus.
Any ideas onhow to break out of this catch 22 situation?
RegardsHere's an old post i found that explains how to fix a suspect db :-
http://groups.google.co.uk/groups?q=sql+2000+suspect+fix&hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=dcde2a5a.0309151952.424b43cb%40posting.google.com&rnum=1
--
HTH
Ryan Waight, MCDBA, MCSE
<japan11@.hotmail.com> wrote in message
news:074901c39147$f819ece0$a301280a@.phx.gbl...
> A db is flagged as suspect. I tried to detach it or take
> offline but was unable to as the db is replicated (merge
> replication). At the same time I was unable to cancel the
> replication because the db wasn't found.
> The suspect status was caused by the db failing to close
> cleanly after a failed back-up. I also haven't been able
> to succesfully run sp_resetstatus.
> Any ideas onhow to break out of this catch 22 situation?
> Regards

Cannot create publication after SP1 upgrade

After upgrading SQL Server 2005 with the recent service pack, I am unable to create a publication. When I right-click on [INSTANCE]/Replication/Local Publications and select "New Publication...," the mouse cursor turns into an hourglass, but the wizard never shows up, nor is any error displayed.

There is a logged error that's been showing up that may or may not be related. The error message is "Replication-(null): agent (null) scheduled for retry. Could not clean up the distribution history tables."

Any ideas on how we can restore replication functionality on our SQL Server instance?

Can you run profiler before invoking New Publication wizard to trace which statement is causing the problem? What kind of publications do you have before upgrade to SP1? Is distributor on the same machine as publisher?

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||I ran the profiler, and I'm getting some strange output. Apparently, the command "select * from sysdatabases where name=N'distribution'" is being run over and over again. Strangely enough, there is a row returned from that command that points to where our distribution database was located, but the database isn't shown under the system databases in Management Studio.

Before upgrading to SP1, we were using merge publications with the distributor on the same machine as the publisher.

|||

Can you send sp1 upgrade log files and profiler trace to me? I will send you a separate email.

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

From the sp1 upgrade log file, looks like you have run SP1 upgrade twice, the first time it runs, SQLDEV01 machine is treated as a distributor and distribution database is upgrade successfully(from what the log file says). The 2nd time the sp1 upgrade runs, it didn't treat this machine as distributor and didn't upgrade distribution database (also you mentioned distribution database is no longer marked as system database, which is suspicious, we need to figure out why). Did you notice any error during sp1 upgrade? Why you run sp1 upgrade twice? Did you drop distributor or change replication settings after first sp1 upgrade?

Please also email a profiler trace for the New publication wizard so we can further diagnose the issue. When using profiler, make sure select following options (and unselect any options not mentioned below):

Error and Warnings --> ErrorLog, Exceptions, User Error Message

Stored Procedures --> RPC Starting, RPC Completed, SP Starting, SP Completed, SP Stmt Starting

TSQL --> SQL: Batch Starting, SQL: Batch Completed, SQL: StmtStarting

Thanks,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||It's finally working! I suspect that all the problems stemmed from there being an inconsistency in the sysdatabases table. I ran "DROP DATABASE distributor" under the master database, and afterward, I was able to set up the distributor, publishers, and subscribers without any problems.

Thank you, Zhiqiang Feng, for your email assistance.

Cannot Create Assembly

I'm using SQL Server Management Studio Express and am unable to create an assembly. The following code:

create assembly HelloWorld from 'c:\HelloWorld.dll'

with permission_set = safe

returns the following error:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'assembly'.

I noticed that the word "assembly" isn't in blue text and what's more, if I expand the programmability node on any database, there is no assembly node. Anybody know what I'm doing wrong here?

Thanks in advance.

You are either not connected to a SQL Server 2005 instance or have the compability level to 80 or lower. (Which does not support assemblies / assembly keywords)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Thank you Jens. I'm pretty new to this, and compatability level is something I've never heard of before. Could you fill me in on how to change that or point me to a link. Much obliged.

Cannot Connect to SQL Server

I have been unable to connect since Sat to a SQL 2000 Server. Get the following error when trying to connect through Query Analyzer across the network:

Unable to connect to server
Server: Msg 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General Network Error

In my Event Log I have the following error:

17310: process_loginread: Process 2548 genertaed ftal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Sevrer is terminating the process

When I try to connect through Query Analyzer locally, it locks up when I type in the sa password

Any help would be appreciatedRE:
I have been unable to connect since Sat to a SQL 2000 Server. Get the following error when trying to connect through Query Analyzer across the network:
Unable to connect to server
Server: Msg 11, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General Network Error
When I try to connect through Query Analyzer locally (at the Server Console??) , it locks up when I type in the sa password

Q1 Any help would be appreciated

A1 Some things to consider and / or check may include:

Verify that the Sql Service is running. Is security (still) configured for the connection (sa = standard login) being attempted? Verify that the correct protocol, port, etc. is being accessed.

{More detailed information should make it easier for others to provide more useful / helpful feedback.}|||SQL Server is running. SQL Server Agent Locks Up when you try to start it.|||RE:
SQL Server is running.

Q2 SQL Server Agent Locks Up when you try to start it.

Some issues are apparently now resolved?

A2 Consider trying to:

Verify that the Sql Agent Service security context is configured with valid account information, or try using a local system account. Check the logs (incl. agent logs) for errors. Again, in general the more information available, the easier it is for others to provide helpful feedback.|||Didn't you apply patch against Slammer on version prior to SP2?|||Hi I'm learning SQL on the fly but I hope I might be able to help a bit, even if it's a little bit.
1. Are the computers talking? Can you see the other computer on the network?
2. Were any programs installed recently? It might've replaced a DLL that SQL Server might've needed to logon.
3. Are you sure about the SA password and user? If not, check the user and password.
4. What's the login mode? Might be a good idea to check it out.
5. Hard disk might be dued for final rites.

Good luck.

All the best,

Ka Kwok|||Originally posted by ispaleny
Didn't you apply patch against Slammer on version prior to SP2?

What did this virus do? I have the same problem.|||Originally posted by adrian320
What did this virus do? I have the same problem.

From memory it's a DOS attack. It makes a massive amount of requests to the server.
Regards,
Ka.|||http://www.microsoft.com/sql/techinfo/administration/2000/security/slammer.asp take help to combat the issue.|||Thank you for all the input,

I applied the hotfix for the virus to the 2000 sql servers we have running and they seem to work fine know.

Thanks.

Cannot Connect to SQL on Win2003 Server

Hi everyone,
I recently installed Windows 2003 Server and then Sql Server 2000 on top.
However, I am unable to connect from any other computer to this instance of
Sql2000.
Any ideas?
AbhayAny erorrs?
"Abhay Sobti" <stansoft@.vsnl.com> wrote in message
news:OIF$ZcsOGHA.2300@.TK2MSFTNGP15.phx.gbl...
> Hi everyone,
> I recently installed Windows 2003 Server and then Sql Server 2000 on top.
> However, I am unable to connect from any other computer to this instance
> of
> Sql2000.
> Any ideas?
> Abhay
>|||Make sure, that If you did a standard installation ,that port 1434 and
1433 are accessible from outside the server. These ports are used to
communicate to SQL Server via TCP/IP.
HTH, Jens Suessmeyer.

Cannot connect to RS instance with SS2005 Mgmt Studio or Browser U

I get the message "Cannot connect to $ServerName\$RS2005InstanceName"
Additional Info:
Unable to connect to the remote server(System)
No connection could be made because the target machine actively
refused it (System)
Config Is;
SS2005 Database server ENT SP1(Named instance) $ServerName/$Instance1
SSRS2005 Reporting Services ENT SP1(Named instance) $ServerName/$Instance1
Where the Instancenames are exactly the same, & both the Database server and
RS instances are installed on the same server.
Also get a similar message/errors when trying to access the RS subwebs using
a browser URL.
regardsI'm having basicly the same issue. I can not connect to Report Services
through Management studio or the web interface. I'm running 2003 domain
controller, Small business edition and sql 2005 standard with sql service
pack 1 installed.
"Graham Smith" wrote:
> I get the message "Cannot connect to $ServerName\$RS2005InstanceName"
> Additional Info:
> Unable to connect to the remote server(System)
> No connection could be made because the target machine actively
> refused it (System)
> Config Is;
> SS2005 Database server ENT SP1(Named instance) $ServerName/$Instance1
> SSRS2005 Reporting Services ENT SP1(Named instance) $ServerName/$Instance1
> Where the Instancenames are exactly the same, & both the Database server and
> RS instances are installed on the same server.
> Also get a similar message/errors when trying to access the RS subwebs using
> a browser URL.
> regards
>
>