Can't drop column

I have a couple of questions relating to a merge
replication database.
1.I was unable to add a column to a table being
replicated using EM, but succeeded using
sp_repladdcolumn. When I tried to drop another column
using sp_repldropcolumn, I get the following
message: "ALTER TABLE DROP COLUMN failed
because 'FieldName' is currently replicated.
2.If I am unable to use EM to drop/add columns,
does this mean that my db is corrupt? How can I check if
my db is corrupt and which tools can I use?
3.The transaction log for a replicated db keeps
growing. The database is about 500 MB and the transaction
log is almost 4GB. I performed a complete backup and even
tried to shrink the log manually but the size did not
change.
I will appreciate any help I can get in resolving these
problems.
Thanks
Emma
is this column a pk, or part of a pk? are there and contraints on this
column?
It is unlikely your database is corrupt. Database base corrpuption errors
normally show up when you query a page telling you a page of the table or
index is inaccessible, your database is inaccessible. To check this run dbcc
checkdb
Regarding your ever expanding database, run dbcc open tran and see if there
are any old open transactions. If so figure out what they are doing and
evaluate killing them. The consider switching to the simple recovery model
and trying to shrink the database again several times. This will cause
locking so it is best to do this off hours. After you do this run a backup,
and then switch back to the full model.
"Emma" <eeemore@.hotmail.com> wrote in message
news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
> I have a couple of questions relating to a merge
> replication database.
> 1. I was unable to add a column to a table being
> replicated using EM, but succeeded using
> sp_repladdcolumn. When I tried to drop another column
> using sp_repldropcolumn, I get the following
> message: "ALTER TABLE DROP COLUMN failed
> because 'FieldName' is currently replicated.
> 2. If I am unable to use EM to drop/add columns,
> does this mean that my db is corrupt? How can I check if
> my db is corrupt and which tools can I use?
> 3. The transaction log for a replicated db keeps
> growing. The database is about 500 MB and the transaction
> log is almost 4GB. I performed a complete backup and even
> tried to shrink the log manually but the size did not
> change.
> I will appreciate any help I can get in resolving these
> problems.
> Thanks
> Emma
>
|||Hilary,
Thanks for your response. dbcc checkdb returned no error.
dbbc opentran returned the following and I don't know
what to do with it.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (305:22434:1)
Thanks
Emma

>--Original Message--
>is this column a pk, or part of a pk? are there and
contraints on this
>column?
>It is unlikely your database is corrupt. Database base
corrpuption errors
>normally show up when you query a page telling you a
page of the table or
>index is inaccessible, your database is inaccessible. To
check this run dbcc
>checkdb
>Regarding your ever expanding database, run dbcc open
tran and see if there
>are any old open transactions. If so figure out what
they are doing and
>evaluate killing them. The consider switching to the
simple recovery model
>and trying to shrink the database again several times.
This will cause
>locking so it is best to do this off hours. After you do
this run a backup,
>and then switch back to the full model.
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
if
transaction
even
>
>.
>
|||Hilary,
In response to your first question, the column that I
can't drop is not a pk or part of a pk and there are no
constraints. There is a relationship between this table
and another table on another column.
Thanks
Emma

>--Original Message--
>is this column a pk, or part of a pk? are there and
contraints on this
>column?
>It is unlikely your database is corrupt. Database base
corrpuption errors
>normally show up when you query a page telling you a
page of the table or
>index is inaccessible, your database is inaccessible. To
check this run dbcc
>checkdb
>Regarding your ever expanding database, run dbcc open
tran and see if there
>are any old open transactions. If so figure out what
they are doing and
>evaluate killing them. The consider switching to the
simple recovery model
>and trying to shrink the database again several times.
This will cause
>locking so it is best to do this off hours. After you do
this run a backup,
>and then switch back to the full model.
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
if
transaction
even
>
>.
>

Can't delete Web Assistant task

I used the Web Assistant tool to monitor a couple of tables. When I was fin
ished with the monitoring after a couple of weeks, I didn't want to monitor
the tables anymore. I deleted the proceedure, and now when I add data to th
e tables, I get an error me
ssage:
Failed@.CDBBaseGroupObj::dbwrite attribute xref.Database error:[Microsoft][ODBC SQL Serv
er Driver][SQL Server] SQL Web Assistant: Could not execute the SQL statemen
t.
I click "OK" on the error message and proceed as normal.
Can anyone point me in the right direction of getting rid of this error mess
age popping up?
thanks,
SKClarkWhen you select the option to monitor tables in the Web Assistant, it
also creates triggers. If all you did was manually drop the stored
procedure, then the triggers likley still exist. At this point you'll
need to delete them manually. In future, use sp_dropwebtask, which
will clean up all of the objects.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Tue, 10 Feb 2004 08:51:08 -0800, "SKClark"
<anonymous@.discussions.microsoft.com> wrote:

>I used the Web Assistant tool to monitor a couple of tables. When I was finished w
ith the monitoring after a couple of weeks, I didn't want to monitor the tables anym
ore. I deleted the proceedure, and now when I add data to the tables, I get an erro
r m
essage:
>Failed@.CDBBaseGroupObj::dbwrite attribute xref.Database error:[Microsoft][ODBC SQL Ser
ver Driver][SQL Server] SQL Web Assistant: Could not execute the SQL stateme
nt.
>I click "OK" on the error message and proceed as normal.
>Can anyone point me in the right direction of getting rid of this error mes
sage popping up?
>thanks,
>SKClark|||cool...I found 3 triggers attached to a table. I deleted them and now the
error message doesn't show up anymore.
Thanks for your help!!! :-)
-- Mary Chipman wrote: --
When you select the option to monitor tables in the Web Assistant, it
also creates triggers. If all you did was manually drop the stored
procedure, then the triggers likley still exist. At this point you'll
need to delete them manually. In future, use sp_dropwebtask, which
will clean up all of the objects.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Tue, 10 Feb 2004 08:51:08 -0800, "SKClark"
<anonymous@.discussions.microsoft.com> wrote:

>I used the Web Assistant tool to monitor a couple of tables. When I was finished w
ith the monitoring after a couple of weeks, I didn't want to monitor the tables anym
ore. I deleted the proceedure, and now when I add data to the tables, I get an er[/
color]
ror message:
>thanks,
>SKClark

Can't delete old maintenance plan job

In SQL 2005, I've migrated my old SQL 2000 maintenance plans to the new
format and I'm trying to delete a couple of migrated plans that I've redone.
I've managed to delete the plans, but the jobs for those plans are still
left. When I try to delete a job, I get the following error:
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
I know what the error means, of course, but I'm not sure about the best way
to solve it. Thanks!Solved it myself -- I had to manually go into the system tables and delete
some orphaned references between the maintenance plans and jobs.
"Gary" wrote:
> In SQL 2005, I've migrated my old SQL 2000 maintenance plans to the new
> format and I'm trying to delete a couple of migrated plans that I've redone.
> I've managed to delete the plans, but the jobs for those plans are still
> left. When I try to delete a job, I get the following error:
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column 'job_id'.
> The statement has been terminated. (Microsoft SQL Server, Error: 547)
> I know what the error means, of course, but I'm not sure about the best way
> to solve it. Thanks!

Can't delete old maintenance plan job

In SQL 2005, I've migrated my old SQL 2000 maintenance plans to the new
format and I'm trying to delete a couple of migrated plans that I've redone.
I've managed to delete the plans, but the jobs for those plans are still
left. When I try to delete a job, I get the following error:
The DELETE statement conflicted with the REFERENCE constraint
"FK_subplan_job_id". The conflict occurred in database "msdb", table
"dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)
I know what the error means, of course, but I'm not sure about the best way
to solve it. Thanks!Solved it myself -- I had to manually go into the system tables and delete
some orphaned references between the maintenance plans and jobs.
"Gary" wrote:

> In SQL 2005, I've migrated my old SQL 2000 maintenance plans to the new
> format and I'm trying to delete a couple of migrated plans that I've redon
e.
> I've managed to delete the plans, but the jobs for those plans are still
> left. When I try to delete a job, I get the following error:
> The DELETE statement conflicted with the REFERENCE constraint
> "FK_subplan_job_id". The conflict occurred in database "msdb", table
> "dbo.sysmaintplan_subplans", column 'job_id'.
> The statement has been terminated. (Microsoft SQL Server, Error: 547)
> I know what the error means, of course, but I'm not sure about the best wa
y
> to solve it. Thanks!

Cant create index on view

Hi,

SQL Server 2005 (SP2) Developer edition on XP Pro

Can anyone help with setting up an indexed view? Part of the select statement calls a couple of very simple functions which are deterministic. The functions which are shown below do not perform any aggregation.

This is the error message.

Msg 8668, Level 16, State 0, Line 2
Cannot create the clustered index 'IX3_SA' on view 'XPS.dbo.SA_INDEXED' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

Function definitions:

create FUNCTION [dbo].[MonthName](@.MonthNo TINYINT)
RETURNS CHAR(3)
with schemabinding
AS
BEGIN
DECLARE @.MonthName CHAR(3)
IF @.MonthNo BETWEEN 1 AND 12 SET @.MonthName = SUBSTRING('JanFebMarAprMayJunJulAugSepOctNovDec',((@.MonthNo*3) -2),3)
RETURN ISNULL(@.MonthName,'')
END

create FUNCTION [dbo].[FinancialYear](
@.Year INT,
@.MonthNo TINYINT,
@.YearEndMonthNo TINYINT)
RETURNS INT
with schemabinding
AS
BEGIN
DECLARE @.FinancialYear INT
IF @.MonthNo <= @.YearEndMonthNo
SET @.FinancialYear = @.Year
ELSE
SET @.FinancialYear = @.Year + 1
RETURN @.FinancialYear
END

The first part of the select statement is as follows:

(SELECT
count_big(*) as CB,

dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS REPORT_FINANCIAL_YEAR,
dbo.MonthName(MTH.Number) + ' ' + CAST(YR.Number AS CHAR(4)) AS REPORT_MONTH,
dbo.MonthName(YEAR_END_MONTH) + ' ' + CAST(dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS CHAR(4)) AS REPORT_FINANCIAL_YEAR_END,

I can create the index on this view if I comment out the calls to the functions so I know that is where the problem is. I can't however see why this is erroring as both only return a single value without any agregation.

Anyone got any ideas on this?

Thanks in advance

David.

Well that met with a deafening silence.

The answer was to move these function calls out of the indexed view definition into a standard view that sits above it.

Can''t connect to SQL Express or SQL 2005 from C# web app and IIS

Hi all,

I'm new to this forum and haven't worked with SQL Server for a couple of years. Now it seems I'm getting back into it. I have SQL Express and SQL 2005 installed on an XP sp2 machine with IIS 5.1. I have a C#/ASP.NET web app in Visual Studio 2005 that keeps getting an error on attempting to connect (SqlConnection object). It's a generic error saying a probable cause is SQL Express default setup not allowing remote connections. Remote connections are turned on for both TCP and UDP. I can connect from within a small C# windows application using the same uid and pwd, so I suspect it's something related to IIS talking to SQLExpress. I would appreciate any help on other things to check. I am also fairly new to using IIS in the development environment.

Thanks

It could be because you didn't specify the instance name when you tried to connect from the C# app? It could also be that SQLBrowser isn't running.

Hope that helps,

John

|||

This is because SQL Server 2005 Express Edition does not accepts remote connections by default:

How to: Enable Network Access During Installation of SQL Server 2005 Express Edition

|||

Thanks John,

Yes, I specified the instance name and verified SQLBrowser is running. COnnection string = "Server=BUTTERFLY\SQLExpress; Initial Catalog=catalog; uid=username; pwd=password;

|||

Ronald,

Thanks for pointing me to this information. It didn't actually solve my dilemma, but it did get the juices flowing. This is related to a project I inherited and upgraded from .NET 1.1 to 2.0. The sql connection string referenced a property for "server=", changing this to "data source=" allowed the connection to open properly. I am reading the links you pointed and they are very helpful. Thanks

Can''t connect to SQL Express or SQL 2005 from C# web app and IIS

Hi all,

I'm new to this forum and haven't worked with SQL Server for a couple of years. Now it seems I'm getting back into it. I have SQL Express and SQL 2005 installed on an XP sp2 machine with IIS 5.1. I have a C#/ASP.NET web app in Visual Studio 2005 that keeps getting an error on attempting to connect (SqlConnection object). It's a generic error saying a probable cause is SQL Express default setup not allowing remote connections. Remote connections are turned on for both TCP and UDP. I can connect from within a small C# windows application using the same uid and pwd, so I suspect it's something related to IIS talking to SQLExpress. I would appreciate any help on other things to check. I am also fairly new to using IIS in the development environment.

Thanks

It could be because you didn't specify the instance name when you tried to connect from the C# app? It could also be that SQLBrowser isn't running.

Hope that helps,

John

|||

This is because SQL Server 2005 Express Edition does not accepts remote connections by default:

How to: Enable Network Access During Installation of SQL Server 2005 Express Edition

|||

Thanks John,

Yes, I specified the instance name and verified SQLBrowser is running. COnnection string = "Server=BUTTERFLY\SQLExpress; Initial Catalog=catalog; uid=username; pwd=password;

|||

Ronald,

Thanks for pointing me to this information. It didn't actually solve my dilemma, but it did get the juices flowing. This is related to a project I inherited and upgraded from .NET 1.1 to 2.0. The sql connection string referenced a property for "server=", changing this to "data source=" allowed the connection to open properly. I am reading the links you pointed and they are very helpful. Thanks

Can''t connect to SQL Express or SQL 2005 from C# web app and IIS

Hi all,

I'm new to this forum and haven't worked with SQL Server for a couple of years. Now it seems I'm getting back into it. I have SQL Express and SQL 2005 installed on an XP sp2 machine with IIS 5.1. I have a C#/ASP.NET web app in Visual Studio 2005 that keeps getting an error on attempting to connect (SqlConnection object). It's a generic error saying a probable cause is SQL Express default setup not allowing remote connections. Remote connections are turned on for both TCP and UDP. I can connect from within a small C# windows application using the same uid and pwd, so I suspect it's something related to IIS talking to SQLExpress. I would appreciate any help on other things to check. I am also fairly new to using IIS in the development environment.

Thanks

It could be because you didn't specify the instance name when you tried to connect from the C# app? It could also be that SQLBrowser isn't running.

Hope that helps,

John

|||

This is because SQL Server 2005 Express Edition does not accepts remote connections by default:

How to: Enable Network Access During Installation of SQL Server 2005 Express Edition

|||

Thanks John,

Yes, I specified the instance name and verified SQLBrowser is running. COnnection string = "Server=BUTTERFLY\SQLExpress; Initial Catalog=catalog; uid=username; pwd=password;

|||

Ronald,

Thanks for pointing me to this information. It didn't actually solve my dilemma, but it did get the juices flowing. This is related to a project I inherited and upgraded from .NET 1.1 to 2.0. The sql connection string referenced a property for "server=", changing this to "data source=" allowed the connection to open properly. I am reading the links you pointed and they are very helpful. Thanks

Can''t connect to SQL Express or SQL 2005 from C# web app and IIS

Hi all,

I'm new to this forum and haven't worked with SQL Server for a couple of years. Now it seems I'm getting back into it. I have SQL Express and SQL 2005 installed on an XP sp2 machine with IIS 5.1. I have a C#/ASP.NET web app in Visual Studio 2005 that keeps getting an error on attempting to connect (SqlConnection object). It's a generic error saying a probable cause is SQL Express default setup not allowing remote connections. Remote connections are turned on for both TCP and UDP. I can connect from within a small C# windows application using the same uid and pwd, so I suspect it's something related to IIS talking to SQLExpress. I would appreciate any help on other things to check. I am also fairly new to using IIS in the development environment.

Thanks

It could be because you didn't specify the instance name when you tried to connect from the C# app? It could also be that SQLBrowser isn't running.

Hope that helps,

John

|||

This is because SQL Server 2005 Express Edition does not accepts remote connections by default:

How to: Enable Network Access During Installation of SQL Server 2005 Express Edition

|||

Thanks John,

Yes, I specified the instance name and verified SQLBrowser is running. COnnection string = "Server=BUTTERFLY\SQLExpress; Initial Catalog=catalog; uid=username; pwd=password;

|||

Ronald,

Thanks for pointing me to this information. It didn't actually solve my dilemma, but it did get the juices flowing. This is related to a project I inherited and upgraded from .NET 1.1 to 2.0. The sql connection string referenced a property for "server=", changing this to "data source=" allowed the connection to open properly. I am reading the links you pointed and they are very helpful. Thanks

Cant connect to server

Hey,
I've had a server running for the last couple of months and earlier today it died, i managed to get Service Pack 2 on it, and now it will start but nothing will interface with it,
especially not coldfusion

Coldfusion says
SQLException occurred in JDBCPool while attempting to connect, please check your username, password, URL, and other connectivity info.

though when i use ODBC and i set a new DSN
if i use Windows Authentication, it goes through, and i test the source and it says succesful
though if i try and use SQL Authentication it says

Connection Failed :
SQL State : '28000'
SQL Server Error : 18452
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for 'username'. Reason : User not associated with a trusted SQL connection

any ideas?Is your sql instance set up for windows only authentification or mixed-mode ?|||Originally posted by rnealejr
Is your sql instance set up for windows only authentification or mixed-mode ?

its ok
sorted it now, it was for SQL Authentication, not mixed or windows

i rebuilt the registry and it worked