Do CR/LF get stored in a text column

I am capturing user comments in a textarea field on a web form and then storing the comments in a text column in a DB table. I would like to know if the carriage returns and linefeeds get stored in the database (MS SQL 2000)?I would say yes...

USE Northwind
GO

CREATE TABLE myTable99 (col1 varchar(8000))
GO

DECLARE @.x varchar(8000)
SELECT @.x = 'Wasted away again in
Margaritaville'
INSERT INTO myTable99 (Col1) SELECT @.x
SELECT @.x
GO

DROP TABLE myTable99
GO|||[QUOTE][SIZE=1]Originally posted by Brett Kaiser
I would say yes...
[/quote

I second that.sql

do anyone have an idea?

Hi there,

I have number of tasks in my control flow most of them are execute sql task. I want to update one of the column in my table when anyone of the task in the control get fails?

Please let me know if anyone have an idea how to do this.

Thanks and Regards

I think 'event handlers' can do that for you. I dont have specific examples now; but this forum has a lot of info on that.|||

Hi Salas,

You are right.I have done it by using event handler.

Thanks a lot.

DNN DAL SqlDataProvider Passing NULL to SQL Stored Procedure

Hello,

I'm trying to pass a null object to a stored procedure to update a SQL Table boolean field with a null value. My SQL Table boolean column allows nulls.

I'm using an InfoObject which has several properties all corresponding to fields in the SQL Table. One of those fields is a boolean. I create an instance of the InfoObject in my code and assigns values to the various properties. The boolean property in question (call it InfoOjbect.BooleanProperty) is not assigned anything. I then call my StoredProcedure passing the InfoObject to it (using the DotNetNuke DAL architecture) and the final result is the Table's boolean column is populated with a 0 and not a NULL. If I explicitly define the InfoObject.BooleanProperty = null.nullboolean before passing it to the Stored Procedure, the same thing happens. How do I pass a null to the SQL database for a boolean field? I've tried making InfoObject.BooleanProperty = dbnull.value but it won't let me do this saying "dbnull cannot be converted to a boolean." Do I have to explicitly create my InfoObject properties to allow for a null to be assigned to it?

Any help would be greatly appreciated. I'm using the DotNetNuke DAL architecture passing my InfoObject through a dataprovider to the sqldataprovider which calls the SQL Stored Procedure to add the new record to the Table.

Thanks in advance for any help.Please help?!|||The issue was with my InfoObject construction. DNN Core Team provided the solution. You can see it athttp://www.dotnetnuke.com/Community/ForumsDotNetNuke/tabid/795/forumid/118/threadid/41618/threadpage/3/scope/posts/Default.aspx

DMX Queries with MS Time Series

Hi!

I have a table Month_Sales(Month, product_1, .., product_n). The value of column product_i is the sale in this month.

so when i build MS Time Series for this domain, i want to query to find top m product is seld most in next month?

How do i buid that query?

DMX doesn't provide a way to order by the forecast value. However, you should be able to easily do this in client side code. Assuming your model looks like this:

CREATE MINING MODEL SalesForecast(
Product TEXT KEY,
Month LONG KEY TIME,
Sales LONG CONTINUOUS)
USING Microsoft_Time_Series

The following query will return the prediction for the next month for all products:

SELECT Product, Sales from SalesForecast

You can sort the query result by Sales in client code and pick the top N..

Division Calc in Column Returns 0's or 1's

I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
calculated in a previous grouped view (View1). The column in View2 that
devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
record... but they should be actual numbers instead.
The divide column in View2 looks like this: Number2 / Number 1
The SQL statement looks like this:
SELECT Number1, Number2, Number2 / Number1 AS Expr1
FROM dbo.View1
What do you think is going on? Is SQL Server getting the DataTypes mixed
up?
Thank You!!
Scott Buerkley
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95If your numbers are INTs then you're getting integer divisions. For
example:
SELECT 1/2
returns 0.
Try casting one of them to NUMERIC:
SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
Expr1
FROM dbo.View1
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Hard to know for sure without DDL and sample data, but the most likely cause
is that Number1 and Number 2 are bot integer types (int or smallint, etc).
Then SQL does an integer divide and always returns an integer result.
Change one of them to a type which can have a decimal part (float, or
decimal, etc) before doing the divide, something like
SELECT Number1, Number2, (Cast Number2 As Float) / Number1 As Expr1
Tom
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Scott Buerkley wrote:
> I am dividing 2 numbers in a view (View2). The 2 numbers we sums of numbe
r
> calculated in a previous grouped view (View1). The column in View2 that
> devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
> record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
>
You're dividing two integers, coming up with a fractional value, and SQL
is rounding it to return it as an integer.|||Yes, the 2 numbers were integers and this worked!!
Thx,
Scott Buerkley
"Mike C#" <xyz@.xyz.com> wrote in message
news:ODvlDeimGHA.464@.TK2MSFTNGP05.phx.gbl...
> If your numbers are INTs then you're getting integer divisions. For
> example:
> SELECT 1/2
> returns 0.
> Try casting one of them to NUMERIC:
> SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
> Expr1
> FROM dbo.View1
> "Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
> news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95|||Yes, you were all correct. The 2 numbers were integers.
It is working now. Thanks for your help!!
Thx,
Scott Buerkley
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95

Division by zero and computed by column

I've got two integer columns in the table, third one is computed by previous two ones division. That's fine, however sometimes can happen that divided by column is set to zero. How can i avoid division by zero exception, please? TIA

The best way to avoid the problem is to use a CASE statement like:

declare @.aTable table(value1 int, value2 int)
insert into @.aTable values (32, 4)
insert into @.aTable values (5, 0)

select value1,
value2,
case when value2 <> 0 then value1/value2 end
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0 NULL
*/

-- Or maybe:

select value1,
value2,
isnull(convert(varchar(11), case when value2 <> 0 then value1/value2 end), '')
from @.aTable

/*
value1 value2
-- -- --
32 4 8
5 0
*/

|||

Thank you for your reply.

So you prefer to get the result via select command instead of fixed computed by column, right?

|||I am not sure that I understand that last question; what exactly do you mean?|||

You can use the CASE structure in a computed column definition:

Something like:

Code Snippet

ALTER TABLE MyTable

ADD COLUMN MyComputedCol AS ( CASE WHEN ( [Col1] <> 0 ) THEN ( [Col2] / [Col1] ) END )

|||Thanks for picking me up, Arnie! Again, I am asleep at the wheel! Sheesh! To answer your question, Jan, no, I have no issue against using the computed column. As Anie indicated, the computed column should be just fine. (I'm BRAINDEAD today!)|||Wow, that's amazing. Thank you Kent and Arnie!

Divide Integers

I'm trying to divide 2 integers to return a column of C's per thousand with
(Failures / Dropped) * 1000.
I've tried different version of float, cast as decimal etc. I'd like 4-5
didgits after the decimal. What is the correct way to divide integers to
return a usable number.
Yr Mnth Dropped Failures
-- -- -- --
2005 July 126493 610
2005 August 207325 955
2005 September 89714 742
2005 October 225112 1142
2005 November 186264 791
2005 December 146901 774
2006 January 103096 510Try
SELECT CAST((CAST(Failures AS numeric) / Dropped) * 1000 AS decimal(8,4))
FROM [YourTable]
"Paul Ilacqua" wrote:

> I'm trying to divide 2 integers to return a column of C's per thousand wi
th
> (Failures / Dropped) * 1000.
> I've tried different version of float, cast as decimal etc. I'd like 4-5
> didgits after the decimal. What is the correct way to divide integers to
> return a usable number.
> Yr Mnth Dropped Failures
> -- -- -- --
> 2005 July 126493 610
> 2005 August 207325 955
> 2005 September 89714 742
> 2005 October 225112 1142
> 2005 November 186264 791
> 2005 December 146901 774
> 2006 January 103096 510
>
>|||Paul Ilacqua (pilacqu2@.twcny.rr.com) writes:
> I'm trying to divide 2 integers to return a column of C's per thousand
> with (Failures / Dropped) * 1000. I've tried different version of
> float, cast as decimal etc. I'd like 4-5 didgits after the decimal. What
> is the correct way to divide integers to return a usable number.
Does one of:
convert(decimal(18,4), 1E0 * Failures / Dropped * 1000)
round(1E0 * Failures / Dropped * 1000, 4)
meet your requirements? Note that the last expression is float, so it
may have 000000001 or similar at the end when you look at it in Query
Analyzer.
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|||That does work fine.... Thank You very much.
It is sure odd numeric behavior... calculate it in Access or Excel and
it's automatic. Would you call this method "standard" in handling integer
division?
Thanks again
Paul
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:FF7A96CF-B908-4FCD-BE98-6F6AB8FFDC8A@.microsoft.com...
> Try
> SELECT CAST((CAST(Failures AS numeric) / Dropped) * 1000 AS decimal(8,4))
> FROM [YourTable]
>
> --
> "Paul Ilacqua" wrote:
>|||Did you bother to look up the basic rules of integer math in SQL?|||Paul Ilacqua (pilacqu2@.twcny.rr.com) writes:
> That does work fine.... Thank You very much.
> It is sure odd numeric behavior... calculate it in Access or Excel and
> it's automatic. Would you call this method "standard" in handling integer
> division?
Exactly what is odd? integer/integer meaning integer division appears
in several other programming languages as well. And the fact that you
need to round or convert the result to get the number of desired decimals
is not strange at all - how should SQL Server be able to guess what you
want?
Yes, Excel has a number of rules for precisely that, guessing. Sometimes
it works, sometimes it does not. I have some CSV files around which
has data like 19991112093212, which Excel presents as float values
each time I open the file. In fact this is a string (date and time).
But Excel is an interactive tool, so mis-guesses can easily be corrected.
A server like SQL Server must work after more stringent rules.
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|||Erland,
I agree... after I replied I realized that's why I like SQL Server over
Access or Excel (among other reasons) is that allows a finer control over
things like number formatting. Excel uses it's best guess a lot where it is
not always the desired result. "Odd" is a word that is replaced by
"understanding" after you learn something and that's why I frequent these
boards. (and sometimes take some hits for it)
Thanks again
Paul
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9755642E25FB4Yazorman@.127.0.0.1...
> Paul Ilacqua (pilacqu2@.twcny.rr.com) writes:
> Exactly what is odd? integer/integer meaning integer division appears
> in several other programming languages as well. And the fact that you
> need to round or convert the result to get the number of desired decimals
> is not strange at all - how should SQL Server be able to guess what you
> want?
> Yes, Excel has a number of rules for precisely that, guessing. Sometimes
> it works, sometimes it does not. I have some CSV files around which
> has data like 19991112093212, which Excel presents as float values
> each time I open the file. In fact this is a string (date and time).
> But Excel is an interactive tool, so mis-guesses can easily be corrected.
> A server like SQL Server must work after more stringent rules.
>
> --
> 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

Divide by Zero

When I specify a formula between Computed Column Specification, I have two
zero values, getting Divide by Zero error, any idea how can I avoid this? I
still want SQL Server to display Zero if it is 0/0, is this possible in SQL
Server database?
Thanks
J.CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL
)
INSERT INTO #Test VALUES (50,0)
INSERT INTO #Test VALUES (20,10)
INSERT INTO #Test VALUES (0,0)
SELECT *,
CASE WHEN col1>0 AND col2>0 THEN col1/col2 ELSE 0 END FROM #Test
"Joriv" <nojunk@.please.com> wrote in message
news:dumr51$6id$1@.reader01.news.esat.net...
> When I specify a formula between Computed Column Specification, I have two
> zero values, getting Divide by Zero error, any idea how can I avoid this?
> I still want SQL Server to display Zero if it is 0/0, is this possible in
> SQL Server database?
> Thanks
> J.
>|||use case, here is an example
create table #test (value1 numeric (12,2),value2 numeric (12,2))
insert into #test
select 1,0 union all
select 1,0 union all
select 5,3 union all
select 4,2
select case value2 when 0 then 0 else value1/value2 end as SomeValue
from #test
http://sqlservercode.blogspot.com/|||Joriv,
Since 0/0 is not equal to zero, you need to make a different
calculation to get what you want. No programming language
should have a setting to display wrong answers (0 for the result of
0/0), so you have to be specific about what you want. One way
to do this in SQL is
CASE WHEN bottomValue = 0 THEN 0 ELSE topValue/bottomValue END
You don't say whether or not you also want a result of 0 if you have
1/0, 2/0 and so on. The example above will give you a result of 0
in these cases as well.
Steve Kass
Drew University
Joriv wrote:

>When I specify a formula between Computed Column Specification, I have two
>zero values, getting Divide by Zero error, any idea how can I avoid this? I
>still want SQL Server to display Zero if it is 0/0, is this possible in SQL
>Server database?
>Thanks
>J.
>
>|||Thanks Folks for all your responses, even I think I can use isnull,
but the idea is i want to use this Computed Column Spec in table designer,
how can I insert a formula which does this? even when i do this
isnull((table.column1/table.column2), 0), still does not work...getting same
error...any clue?
thanks
J.
"Joriv" <nojunk@.please.com> wrote in message
news:dumr51$6id$1@.reader01.news.esat.net...
> When I specify a formula between Computed Column Specification, I have two
> zero values, getting Divide by Zero error, any idea how can I avoid this?
> I still want SQL Server to display Zero if it is 0/0, is this possible in
> SQL Server database?
> Thanks
> J.
>|||Hi
CREATE TABLE #Test
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 AS CASE WHEN col1>0 AND col2>0 THEN col1/col2 ELSE 0 END
)
INSERT INTO #Test VALUES (50,0)
INSERT INTO #Test VALUES (20,10)
INSERT INTO #Test VALUES (0,0)
SELECT * FROM #Test
"Joriv" <nojunk@.please.com> wrote in message
news:dumteu$7at$1@.reader01.news.esat.net...
> Thanks Folks for all your responses, even I think I can use isnull,
> but the idea is i want to use this Computed Column Spec in table designer,
> how can I insert a formula which does this? even when i do this
> isnull((table.column1/table.column2), 0), still does not work...getting
> same error...any clue?
> thanks
> J.
> "Joriv" <nojunk@.please.com> wrote in message
> news:dumr51$6id$1@.reader01.news.esat.net...
>|||Thanks for the group,
Now I run into another problem, once I set this formula successfully, I
cannot change the value programmatically. anyway I can do this?
Actually what I want to do this, Initally (default) I want to set to a
formula (say col1/col2) but I might change this programmatically in future
if required.
I tried to set this formual in the default/binding value, but I don't think
it likes any formual's over there.
Any ideas?
Thanks
J.
"Joriv" <nojunk@.please.com> wrote in message
news:dumr51$6id$1@.reader01.news.esat.net...
> When I specify a formula between Computed Column Specification, I have two
> zero values, getting Divide by Zero error, any idea how can I avoid this?
> I still want SQL Server to display Zero if it is 0/0, is this possible in
> SQL Server database?
> Thanks
> J.
>|||Here is the query without case/when.
e.g.
select isnull(a/nullif(b,0),0)[div]
from (select 1 a, 0 b
union all select 2,1)x
-oj
"Joriv" <nojunk@.please.com> wrote in message
news:dumteu$7at$1@.reader01.news.esat.net...
> Thanks Folks for all your responses, even I think I can use isnull,
> but the idea is i want to use this Computed Column Spec in table designer,
> how can I insert a formula which does this? even when i do this
> isnull((table.column1/table.column2), 0), still does not work...getting
> same error...any clue?
> thanks
> J.
> "Joriv" <nojunk@.please.com> wrote in message
> news:dumr51$6id$1@.reader01.news.esat.net...
>|||>> Actually what I want to do this, Initally (default) I want to set to a fo
rmula (say col1/col2) but I might change this programmatically in future if
required. <<
Then use VIEWs instead of a computed column, which is portable, which
can be drop and which allows you to have multiple formulas.
What answer did you want for 0/0? I would go with a NULL or catch the
error.

Divide by Zero

When I specify a formula between Computed Column Specification, I have two
zero values, getting Divide by Zero error, any idea how can I avoid this? I
still want SQL Server to display Zero if it is 0/0, is this possible in SQL
Server database?

Thanks
J.use case, here is an example

create table #test (value1 numeric (12,2),value2 numeric (12,2))
insert into #test
select 1,0 union all
select 1,0 union all
select 5,3 union all
select 4,2
select case value2 when 0 then 0 else value1/value2 end as SomeValue
from #test

http://sqlservercode.blogspot.com/|||IF like this:
Update a
Set X=column b/column c
where Column c<>0

Update a
Set X=0
where Column c=0

any help to you?|||Or perhaps something like:

Update a
Set X= case when columnc=0 then 0 else column b/column c end

MC

"yangyang" <loveflying000@.gmail.com> wrote in message
news:1141834929.055768.75450@.v46g2000cwv.googlegro ups.com...
> IF like this:
> Update a
> Set X=column b/column c
> where Column c<>0
> Update a
> Set X=0
> where Column c=0
> any help to you?

Distribution agent failes with Invalid Column name 207 error

Hi,
We have setup one way transactional replication on SQL 2000 SP3a.
It was running fine for weeks and now the distribution agent fails with
"Invalid Column Name 'Name.' " message. I tried re-starting the agent,
even reintializing, but it doesn't work. I set the verboselevel to 3,
and captured the output to a log file.
I tried executing sp_MSupd_Item in QA and I get the same message, but
don't know what's causing it.
I would really appreciate if someone can help me with this.
Thanks
GG
The output log:
[5/11/2005 5:57:22 PM]MSNDMART-STG1.Item: {CALL sp_MSupd_Item
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0, 0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0 ,0,NULL,NULL,NULL,?,NULL,NULL,NULL,NULL,NULL,NULL, NULL,
0,?,0x00000000008000)}
Last transaction timestamp: 0x000011a00000249a000100000000
Transaction seqno: 0x000011a0000024a50004
Command Id: 1
Partial: 0
Type: 68
Command: STLDMART-PRD1Item
Parameterized values for above command(s): {{2005-05-11 17:07:05.523,
'820HK14 '}}
Agent message code 20046. Invalid column name 'Name'.
Repl Agent Status: 6
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 5,
0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 7,
Transaction Seqno = 000011a00000249a000100000000, Command ID = 5
Message: Replication-Replication Distribution Subsystem: agent
STLSQL-PROD04-Item-MSNDMART-STG1-3 failed. Invalid column name
'Name'.[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_alert(3, 3, 7, 14151, ?, 5, N'STLSQL-PROD04', N'Item',
N'MSNDMART-STG1', N'Item', ?)}
ErrorId = 7, SourceTypeId = 1
ErrorCode = ''
ErrorText = '{CALL sp_MSupd_Item
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0, 0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0 ,0,NULL,NULL,NULL,2005-05-11
17:07:05.523,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, '820HK14
',0x00000000008000)} '
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_error(7, 0, 1, ?, N'', ?)}
Category:COMMAND
Source: Failed Command
Number:
Message: {CALL sp_MSupd_Item
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0, 0,NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0 ,0,NULL,NULL,NULL,2005-05-11
17:07:05.523,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, '820HK14
',0x00000000008000)}
Repl Agent Status: 3
ErrorId = 7, SourceTypeId = 5
ErrorCode = '207'
ErrorText = 'Invalid column name 'Name'.'
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
Category:SQLSERVER
Source: MSNDMART-STG1
Number: 207
Message: Invalid column name 'Name'.
Repl Agent Status: 3
ErrorId = 7, SourceTypeId = 5
ErrorCode = '207'
ErrorText = 'Invalid column name 'Name'.'
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
Category:SQLSERVER
Source: MSNDMART-STG1
Number: 207
Message: Invalid column name 'Name'.
Repl Agent Status: 3
ErrorId = 7, SourceTypeId = 5
ErrorCode = '207'
ErrorText = 'Invalid column name 'Name'.'
[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
Category:SQLSERVER
Source: MSNDMART-STG1
Number: 207
Message: Invalid column name 'Name'.
Repl Agent Status: 3
[5/11/2005 5:57:22 PM]MSNDMART-STG1.Item: exec
dbo.sp_MSupdatelastsyncinfo N'STLSQL-PROD04',N'Item', N'', 0, 6,
N'Invalid column name ''Name''.'
Disconnecting from Subscriber 'MSNDMART-STG1'
Disconnecting from Distributor 'STLDBA-PRD1'
Disconnecting from Distributor History 'STLDBA-PRD1'
Microsoft SQL Server Distribution Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent:
STLSQL-PROD04-Item-MSNDMART-STG1-3
Startup Delay: 3427 (msecs)
Connecting to Distributor 'STLDBA-PRD1'
Repl Agent Status: 3
Connecting to Distributor 'STLDBA-PRD1.'
[5/11/2005 5:57:36 PM]STLDBA-PRD1.: exec sp_helpdistpublisher
N'STLSQL-PROD04'
[5/11/2005 5:57:36 PM]STLDBA-PRD1.Dist_Prod04: select @.@.SERVERNAME
can you edit the proc and put [] around a column called name?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GG" <gdabbara@.gmail.com> wrote in message
news:1115855598.735328.54830@.g44g2000cwa.googlegro ups.com...
> Hi,
> We have setup one way transactional replication on SQL 2000 SP3a.
> It was running fine for weeks and now the distribution agent fails with
> "Invalid Column Name 'Name.' " message. I tried re-starting the agent,
> even reintializing, but it doesn't work. I set the verboselevel to 3,
> and captured the output to a log file.
> I tried executing sp_MSupd_Item in QA and I get the same message, but
> don't know what's causing it.
> I would really appreciate if someone can help me with this.
> Thanks
> GG
> The output log:
> [5/11/2005 5:57:22 PM]MSNDMART-STG1.Item: {CALL sp_MSupd_Item
>
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,0,0,0,
NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0,0 ,NULL,NULL,NULL,?,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL,0,?,0x00000000008000)}
> Last transaction timestamp: 0x000011a00000249a000100000000
> Transaction seqno: 0x000011a0000024a50004
> Command Id: 1
> Partial: 0
> Type: 68
> Command: STLDMART-PRD1Item
> Parameterized values for above command(s): {{2005-05-11 17:07:05.523,
> '820HK14 '}}
> Agent message code 20046. Invalid column name 'Name'.
> Repl Agent Status: 6
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_distribution_history(3, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 5,
> 0x01, 0x01)}
> Adding alert to msdb..sysreplicationalerts: ErrorId = 7,
> Transaction Seqno = 000011a00000249a000100000000, Command ID = 5
> Message: Replication-Replication Distribution Subsystem: agent
> STLSQL-PROD04-Item-MSNDMART-STG1-3 failed. Invalid column name
> 'Name'.[5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_alert(3, 3, 7, 14151, ?, 5, N'STLSQL-PROD04', N'Item',
> N'MSNDMART-STG1', N'Item', ?)}
> ErrorId = 7, SourceTypeId = 1
> ErrorCode = ''
> ErrorText = '{CALL sp_MSupd_Item
>
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,0,0,0,
NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0,0 ,NULL,NULL,NULL,2005-05-11
> 17:07:05.523,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, '820HK14
> ',0x00000000008000)} '
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_error(7, 0, 1, ?, N'', ?)}
> Category:COMMAND
> Source: Failed Command
> Number:
> Message: {CALL sp_MSupd_Item
>
(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL ,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,0,0,0,
NULL,NULL,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,0,0 ,NULL,NULL,NULL,2005-05-11
> 17:07:05.523,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, '820HK14
> ',0x00000000008000)}
> Repl Agent Status: 3
> ErrorId = 7, SourceTypeId = 5
> ErrorCode = '207'
> ErrorText = 'Invalid column name 'Name'.'
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
> Category:SQLSERVER
> Source: MSNDMART-STG1
> Number: 207
> Message: Invalid column name 'Name'.
> Repl Agent Status: 3
> ErrorId = 7, SourceTypeId = 5
> ErrorCode = '207'
> ErrorText = 'Invalid column name 'Name'.'
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
> Category:SQLSERVER
> Source: MSNDMART-STG1
> Number: 207
> Message: Invalid column name 'Name'.
> Repl Agent Status: 3
> ErrorId = 7, SourceTypeId = 5
> ErrorCode = '207'
> ErrorText = 'Invalid column name 'Name'.'
> [5/11/2005 5:57:22 PM]STLDBA-PRD1.Dist_Prod04: {call
> sp_MSadd_repl_error(7, 0, 5, ?, N'207', ?)}
> Category:SQLSERVER
> Source: MSNDMART-STG1
> Number: 207
> Message: Invalid column name 'Name'.
> Repl Agent Status: 3
> [5/11/2005 5:57:22 PM]MSNDMART-STG1.Item: exec
> dbo.sp_MSupdatelastsyncinfo N'STLSQL-PROD04',N'Item', N'', 0, 6,
> N'Invalid column name ''Name''.'
> Disconnecting from Subscriber 'MSNDMART-STG1'
> Disconnecting from Distributor 'STLDBA-PRD1'
> Disconnecting from Distributor History 'STLDBA-PRD1'
> Microsoft SQL Server Distribution Agent 8.00.760
> Copyright (c) 2000 Microsoft Corporation
> Microsoft SQL Server Replication Agent:
> STLSQL-PROD04-Item-MSNDMART-STG1-3
> Startup Delay: 3427 (msecs)
> Connecting to Distributor 'STLDBA-PRD1'
> Repl Agent Status: 3
> Connecting to Distributor 'STLDBA-PRD1.'
> [5/11/2005 5:57:36 PM]STLDBA-PRD1.: exec sp_helpdistpublisher
> N'STLSQL-PROD04'
> [5/11/2005 5:57:36 PM]STLDBA-PRD1.Dist_Prod04: select @.@.SERVERNAME
>
|||We don't have a column called name in any of our tables, being
replicated.
Thanks
GG