May 31, 2009 at 7:26 pm
I write a SP to retrieve data from database when there is any data inside the table. So far everything is fine but when i try to insert the data 'CONFIRMTYPE' , there is an error Invalid column name 'CONFIRMTYPE' .Anyone have idea whats wrong with it?
Below attached with my SP code and database table script.
Thanks
SP code
----------------------------------------------------
SET NOCOUNT ON
DECLARE @rc int
CREATE TABLE ##Errors(
HAPPENTIME smallDateTime default getdate(),
POSITION1 varchar(200),
CODE int,
CONFIRMTYPE tinyint
)
-- Scan for suspect data
INSERT INTO ##Errors(CODE,POSITION1,CONFIRMTYPE)
SELECT DISTINCT CODE,POSITION1,CONFIRMTYPE
from dbo.CAF_ALARM (nolock)
-- Any suspect records found?
IF EXISTS (SELECT * FROM ##Errors (nolock))
BEGIN
-- Write them to a csv file in vaguely legible format
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT *FROM ##Errors (nolock)" queryout c:\CSVOut.txt -c'
-- send an email with the file.
exec master..xp_sendmail
@recipients= N'mymail@gmail.com',
@subject = N'Scan Errors', -- email title
@type = N'text/html',
@query = N'SELECT * FROM ##Errors (nolock)'
DROP table ##Errors
END
GO
Table script
--------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CAF_ALARM]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CAF_ALARM]
GO
CREATE TABLE [dbo].[CAF_ALARM] (
[AID] [bigint] NOT NULL ,
[ID] [bigint] NOT NULL ,
[SYSTEMTYPE] [smallint] NOT NULL ,
[SERVERID] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[POSITION1] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NETYPE] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ALARMTYPE] [tinyint] NULL ,
[HAPPENTIME] [datetime] NULL ,
[CONFIRMTYPE] [tinyint] NULL ,
[CONFIRMINFO] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FILTERSTATE] [tinyint] NULL ,
[INFO] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WORKSTATE] [tinyint] NULL ,
May 31, 2009 at 9:15 pm
Hello,
The Table Definition provided is for a table named "ALARM“, while the SP references a table named “CAF_ALARM”. By any chance, would this typo be your problem?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 31, 2009 at 9:31 pm
yup, sorry it is just a typo when i post the question and i had edit it already.This not the cause for the error.
Did you have any idea regarding to Invalid column name 'CONFIRMTYPE' ??
May 31, 2009 at 9:48 pm
Hello again,
When I changed the name of the Table in the Create Table script that you provided, the query worked on my Server. (Strictly speaking, I also had to add in the missing CODE column, but that is irrelevant).
Perhaps script out the Table Definition again and re-post it.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 31, 2009 at 10:26 pm
i change the table name ##Errors and its work.
May i know why this will happen?
thanks
June 1, 2009 at 6:38 am
##Errors is a global temp table...the two pound signs make that global instead of just for your proc.
a global temp table is visible to all other processes that might need to reference that table.
is it possible ANOTHER procedure might create a table by the same name, that does NOT have the same columns that you are using?
are you sure you need a global temp table and not just a table for the scope of your query/procedure? since you are dropping the table at the end of your procedure, I highly recommend renaimng it to have a single pound sign...#Errors instead.
Lowell
June 2, 2009 at 8:36 am
Lowell, I was wondering as well why the OP creates a global temp table, but then noticed the call
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT *FROM ##Errors (nolock)" queryout c:\CSVOut.txt -c'
That would not work if the table were a local temp table. Personally I would rather use a real table to perform this kind of job though.
cl_see, are you saying that in your original post (the un-edited one) the ##Error table was called something else? If yes, what was its name?
June 2, 2009 at 8:44 am
excellent point Jan,
it seems to me that if the "global" table is missing a column , it's gotta be because a different process created a table with the same name.
That conflict is not going to resolve itself without changing something.
I'd go with your idea on a permentant table, but other crappy ideas i thought of were:
get rid of the temp table altoghether. both bcp and the sendmail proc can take a query as a parameter...his query is very simple.
or
maybe he can append a spid or timestamp to the end of his global table name, ie ##Errors67
or
do all the work in a real temp table, and only send his final results to the global so he can BCP the table the results....lets see if he's identified anything.
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply