Microsoft OLE DB Provider for ODBC Drivers error ''80040e14''

Question

Post reply

Microsoft OLE DB Provider for ODBC Drivers error ''80040e14''

bhavnabakshi

SSC Eights!

Points: 848
More actions
August 14, 2007 at 2:44 pm

#103400

I have recently updated the stored procedure and now I am getting

"Microsoft OLE DB Provider for ODBC Drivers error '80040e14' .

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name

'bbakshi'. " error message. When I am inserting user name in exportedby field. Here is the stored procedure

CREATE PROCEDURE stp_ExportMeetings @selmeetings varchar(8000), @user

varchar(50)

AS

DECLARE @sql varchar(8000)

DECLARE @empty varchar(1)

SET @empty = ''

SELECT @sql = 'UPDATE tblWrkshops SET Exported = 1, ExportDate =

GETDATE(), ExportedBy = "' + @user + '"

WHERE (tblWrkshops.Exported is null) OR (tblWrkshops.Exported <> 1)

and tblWrkshops.SetupNumb in (' + @selmeetings + ')'

EXEC (@sql)

SELECT @sql = 'SELECT tblWrkshops.MeetingCode, SessionNumber=

ISNULL(tblWrkshopSubjects.SessionNumb, ''01''),

tblSessionCons.ConsultantCode,

tblWrkshopSubjects.SubjectTitle, GenSubject =

tbl_Subject_Code.parent_session_code,

tbl_lkp_ParentSessionCodes.ParentSessionTitle

FROM tblWrkshops LEFT JOIN tblWrkshopSubjects ON

tblWrkshops.SetupNumb = tblWrkshopSubjects.SetupNumb

LEFT JOIN tblSessionCons ON tblWrkshopSubjects.WkshopSubjctID =

tblSessionCons.WkshopSubjctID AND

tblSessionCons.ConsultantType="Primary"

LEFT JOIN tbl_Subject_Code ON tblWrkshopSubjects.GenSubject =

tbl_Subject_Code.Subject_Code_pk

LEFT JOIN tbl_lkp_ParentSessionCodes ON

tbl_Subject_Code.parent_session_code =

tbl_lkp_ParentSessionCodes.ParentSessionCode

WHERE tblWrkshops.SetupNumb in (' + @selmeetings + ') and

tbl_lkp_ParentSessionCodes.DeleteMe=0'

EXEC (@sql)

GO

>From the asp page I am passing the parameters

strSQL = "stp_ExportMeetings '" & selected_meetings & "', " &

strExportUser & ""

objRS.Open strSQL, objCN

Any help is appreciated!

Thanks,

Bhavna

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

I'm betting it has to do with dynamic sql; also "Primary" should be in single, not double quotes, right?

if you add one of the many split function examples here on SSC, you could get rid of the dynamic SQL

here's how i would do it:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SPLIT]') and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[SPLIT]

GO

CREATE FUNCTION SPLIT (

@str_in VARCHAR(8000),

@separator VARCHAR(4) )

RETURNS @strtable TABLE (strval VARCHAR(8000))

AS

BEGIN

DECLARE

@Occurrences INT,

@Counter INT,

@tmpStr VARCHAR(8000)

SET @Counter = 0

IF SUBSTRING(@str_in,LEN(@str_in),1) <> @separator

SET @str_in = @str_in + @separator

SET @Occurrences = (DATALENGTH(REPLACE(@str_in,@separator,@separator+'#')) - DATALENGTH(@str_in))/ DATALENGTH(@separator)

SET @tmpStr = @str_in

WHILE @Counter <= @Occurrences

BEGIN

SET @Counter = @Counter + 1

INSERT INTO @strtable

VALUES ( SUBSTRING(@tmpStr,1,CHARINDEX(@separator,@tmpStr)-1))

SET @tmpStr = SUBSTRING(@tmpStr,CHARINDEX(@separator,@tmpStr)+1,8000)

IF DATALENGTH(@tmpStr) = 0

BREAK

END

RETURN

END

GO

--usage example:

select strval from dbo.split('one,two,three',',')

GO

CREATE PROCEDURE stp_ExportMeetings @selmeetings varchar(8000), @user

varchar(50)

AS

BEGIN

DECLARE @sql varchar(8000)

DECLARE @empty varchar(1)

SET @empty = ''

UPDATE tblWrkshops

SET

Exported = 1,

ExportDate = GETDATE(),

ExportedBy = @user

WHERE ((tblWrkshops.Exported is null) OR (tblWrkshops.Exported <> 1) )

AND tblWrkshops.SetupNumb in (select strval from dbo.Split(@selmeetings,',') )

SELECT

tblWrkshops.MeetingCode,

SessionNumber= ISNULL(tblWrkshopSubjects.SessionNumb, '01'),

tblSessionCons.ConsultantCode,

tblWrkshopSubjects.SubjectTitle,

GenSubject = tbl_Subject_Code.parent_session_code,

tbl_lkp_ParentSessionCodes.ParentSessionTitle

FROM tblWrkshops

LEFT JOIN tblWrkshopSubjects ON tblWrkshops.SetupNumb = tblWrkshopSubjects.SetupNumb

LEFT JOIN tblSessionCons ON tblWrkshopSubjects.WkshopSubjctID = tblSessionCons.WkshopSubjctID

AND tblSessionCons.ConsultantType='Primary'

LEFT JOIN tbl_Subject_Code ON tblWrkshopSubjects.GenSubject = tbl_Subject_Code.Subject_Code_pk

LEFT JOIN tbl_lkp_ParentSessionCodes ON tbl_Subject_Code.parent_session_code = tbl_lkp_ParentSessionCodes.ParentSessionCode

WHERE tblWrkshops.SetupNumb in (select strval from dbo.Split(@selmeetings,',') )

AND tbl_lkp_ParentSessionCodes.DeleteMe=0

END --PROC

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vadba SSChampion Points: 11133 More actions · Answer 2

Lowell is right about the double quotes - they should be single quotes. Otherwise SQL Server thinks your are referring to a column name ( unless someone ran the statement SET QUOTED_IDENTIFIER OFF )

However, you have the same type of error in the first query (the UPDATE). If we stick with the dynamic SQL, replace each double quote with two single quotes, like this:

----------------------------------------------------------------

SELECT @sql = 'UPDATE tblWrkshops

SET Exported = 1

, ExportDate = GETDATE()

, ExportedBy = ''' + @user + '''

WHERE (tblWrkshops.Exported is null) OR (tblWrkshops.Exported <> 1)

AND tblWrkshops.SetupNumb in (' + @selmeetings + ')'

----------------------------------------------------------------

In those rare instances where you use dynamic SQL, always print the query string before executing it. You want to make sure the command it correct.

PRINT @sql

--EXEC (sql)

bhavnabakshi SSC Eights! Points: 848 More actions · Answer 3

bhavnabakshi

SSC Eights!

Points: 848

August 16, 2007 at 11:06 am

#727439

Thanks All!

I have changed the query to single quotes and that worked.

Thanks,

Bhavna