Error: Column Name "X" appears more than once in the result column list.

Hello,

I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form:

I'll post the error below. Any clues on how to fix this? Thanks in advance!!!

~Derrick

Column name 'Assigned_To' appears more than once in the result column list.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.

Source Error:

Line 1444: }Line 1445: try {Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery();Line 1447: return returnValue;Line 1448: }

What does your UPDATE command look like?|||

Thanks for the reply. When I go to my Details.aspx page (the page giving me the error), I click on the Details View Control -> Configure Data Source, Click the Update Tab, and here is the Method Signature:

Update(String Assigned_To, String Request_type, String First_Name, String Last_Name, String Username, Nullable Created_On, String Request_Summary, String Expr1, Int16 Original_Request_Id), returns Int32

Does this help?

|||

I was able to resolve this issue. It had to do with the "String Expr1" part of the Update function. I removed any reference to Expr1 from all my data sets, refreshed the schemes in the web forms, and everything worked as it should.

Thanks for anyone who looked into it!

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
>|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name strin
g.
> You could try declaring your parameters as type varchar, but you have to b
e
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' = (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
> > Ok, I previously had a thread about "Column name as variable".
> > Here's a proc I wrote, and it compiles ok:
> > alter PROCEDURE rptRRTP_By_Date_Range
> > -- Add the parameters for the stored procedure here
> > @.Update_Field as sysname,
> > @.start_date as datetime,
> > @.end_date as datetime,
> > @.contr_Stat as varchar(2),
> > @.seq as int
> > AS
> > declare @.sql_stat as varchar(255)
> > BEGIN
> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> > ',' + @.end_date + ')
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
> > where sequence = ' + @.seq
> > Execute (@.sql_stat)
> > END
> > GO|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>> You can't pass a function in as a parameter. Try:
>> DECLARE @.dt DATETIME;
>> SET @.dt = GETDATE();
>> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
> Ok, did that, but see error below.
>
>
>> --
>> Aaron Bertrand
>> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
>> "d.s." <nodamnspa...@.yahoo.com> wrote in message
>> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>>
>> > Ok, I previously had a thread about "Column name as variable".
>> > Here's a proc I wrote, and it compiles ok:
>> > alter PROCEDURE rptRRTP_By_Date_Range
>> > -- Add the parameters for the stored procedure here
>> > @.Update_Field as sysname,
>> > @.start_date as datetime,
>> > @.end_date as datetime,
>> > @.contr_Stat as varchar(2),
>> > @.seq as int
>> > AS
>> > declare @.sql_stat as varchar(255)
>> > BEGIN
>> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =>> > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
>> > ',' + @.end_date + ')
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
>> > where sequence = ' + @.seq
>> > Execute (@.sql_stat)
>> > END
>> > GO
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?
You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegro ups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

Error with Bit datatype.

I created a table called "test" with only one column of bit data type.

I opened the table using management studio and tried to enter the number "1" It is throwing the below error

Microsoft SQL Server Management Studio

Invalid value for cell (row 1, column 1).

The changed value in this cell was not recognized as valid.
.Net Framework Data Type: Boolean
Error Message: String was not recognized as a valid Boolean.

Type a value appropriate for the data type or press ESC to cancel the change.

OK Help

But when I enter true or false it accepts it as a valid value.

When I execute the query
insert into test values (1)
the insert goes fine.

I opened table via management studio I am seeing "true" "false" as values in the table.
When I do a select in the query analyzer I am getting "1" and "0" as results.

Is this the way it is indented to work?

Santhosh

Here is my take...

When SSMS (written in VS.NET) opens a table, it is using a datagrid -and the datagrid is converting to a boolean because VS.NET doesn't have a bit datatype.

If you open a query window, and execute a SQL query that returns the data from the table, you will see that the data is indeed stored as a bit datatype [ 0/1/NULL ].

|||Arnie,
Thanks for the reply. Doesnt it sound stupid?|||I agree that there could be a bit more information available about how SSMS is working behind the scenes.|||Thats because the different UIs are owned by different groups at MS. They *might* get aligned in the future.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Error with a fieldname that does not exist anymore.

I get an :
[Microsoft][ODBC SQL Server Driver}[SQL Server]Invalid column name
'oldfieldname_eid'
While inserting in the Enterprise Manager. The 'oldfieldname_eid' does not
exist in any table of the target database. The error only occurs if an
insert trigger is defined. The insert trigger does not contain a string
resembling the 'oldfieldname_eid'. The name has been used in the past for a
field.
The error does reproduce, after a reset of the client, after setting the
database offline and online again.
The error does disappear when the trigger is 'removed' but reappears
again when the trigger is recreated. The error does not appear when
a 'dummy' trigger is used, only containing "print 'hello'"
Does anybody have a suggestion ?
ben brugmanIt would have been quite helpful if you had posted the text of the trigger,
because that seems to be where the problem is, now we have to make a guess.
One thing I can think of is if you have a view on the table that contained
the 'oldfieldname_eid' column, and that view contains SELECT *. The
definition of a view is not updated automatically when the underlying table
changes, so the column might still exist in the view definition.
Try SELECT table_name FROM information_schema.columns
WHERE column_name = 'oldfieldname_eid'
to find a view or table that includes a column with that name.
--
Jacco Schalkwijk
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:O$T6PNioDHA.2512@.TK2MSFTNGP09.phx.gbl...
> I get an :
> [Microsoft][ODBC SQL Server Driver}[SQL Server]Invalid column name
> 'oldfieldname_eid'
> While inserting in the Enterprise Manager. The 'oldfieldname_eid' does not
> exist in any table of the target database. The error only occurs if an
> insert trigger is defined. The insert trigger does not contain a string
> resembling the 'oldfieldname_eid'. The name has been used in the past for
a
> field.
> The error does reproduce, after a reset of the client, after setting the
> database offline and online again.
> The error does disappear when the trigger is 'removed' but reappears
> again when the trigger is recreated. The error does not appear when
> a 'dummy' trigger is used, only containing "print 'hello'"
> Does anybody have a suggestion ?
> ben brugman
>
>|||> Try SELECT table_name FROM information_schema.columns
> WHERE column_name = 'oldfieldname_eid'
The field 'oldfieldname_eid' does not appear in any View/table or trigger.
It does not appear anywhere anymore in the complete database.
There does not exist a field 'oldfieldname_eid'.
If we change the fieldname into 'AAAAAA' we get the following error
that the field 'newfieldname_eid' does not exist
> > [Microsoft][ODBC SQL Server Driver}[SQL Server]Invalid column name
> > 'newfieldname_eid'
> It would have been quite helpful if you had posted the text of the
trigger,
> because that seems to be where the problem is, now we have to make a
guess.
For the trigger see the end of the message.
Adding a new column to the table with 'newfieldname_eid' next to 'AAAAAA'
and
then deleting the 'AAAAAA' field did solve the problem. We expect that the
count
of columns did have a mismatch in the internal meta data and that this was
solved
by adding and deletion of columns. (But this is a guess).
(The table appears to be exactly as when the error occured but the error is
'gone').
(probably a bug).
We still would like to know what happened !
ben brugman
The trigger.
The trigger :
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [TR_I_Tabel2] ON dbo.Tabel1
FOR INSERT
NOT FOR REPLICATION
AS
INSERT INTO
Tabel2 (Field1, Field2, Field3, Field4, Field5, Field6)
SELECT
Field1, Field2, Field3, current_Field4, current_Field5, current_Field6
FROM inserted
UPDATE
Tabel1
SET
Field7 =NULL
FROM
Tabel1
INNER JOIN
inserted
ON
Tabel1.Field3 = inserted.Field3
INNER JOIN
Table3
ON
Table3.Field4 = inserted.current_Field4
WHERE
Table3.status_nbr >= 50
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
> Jacco Schalkwijk
> SQL Server MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:O$T6PNioDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > I get an :
> >
> > [Microsoft][ODBC SQL Server Driver}[SQL Server]Invalid column name
> > 'oldfieldname_eid'
> >
> > While inserting in the Enterprise Manager. The 'oldfieldname_eid' does
not
> > exist in any table of the target database. The error only occurs if an
> > insert trigger is defined. The insert trigger does not contain a string
> > resembling the 'oldfieldname_eid'. The name has been used in the past
for
> a
> > field.
> > The error does reproduce, after a reset of the client, after setting the
> > database offline and online again.
> >
> > The error does disappear when the trigger is 'removed' but reappears
> > again when the trigger is recreated. The error does not appear when
> > a 'dummy' trigger is used, only containing "print 'hello'"
> >
> > Does anybody have a suggestion ?
> >
> > ben brugman
> >
> >
> >
>

Error while creating inline function - CREATE FUNCTION failed because a column name is not speci

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

|||

You have to specify an alias for the columns in the select statement. Change the query to somthing like this:

Code Snippet

SELECT ident_current('orders') AS 'ID'

I hope this answers your question.

Best regards,

Sami Samir

|||Thats easy:

Code Snippet

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders') AS Newidentity)

GO

Jens K. Suessmeyer.

http://www.sqlserver2005.de