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

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. 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

Re: Microsoft OLE DB Provider for ODBC Drivers error "80040e14"

Bhavna (bhavnabakshi@hotmail.com) writes:
> 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. 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)

You get the error because you try to use " as a string delimiter. By
default, " delimits identifiers not string literals.

But you should never interpolate values into the query string. And in
this case there is no reason to use dynamic SQL at all:

UPDATE tblWrkshops
SET Exported = 1,
ExportDate = GETDATE(),
ExportedBy = @user
FROM tblWrkshops W
JOIN iter_intlist_to_table (@selmeetings) i ON W.SetupNumb = i.number
WHERE (tblWrkshops.Exported is null) OR (tblWrkshops.Exported <> 1)


You find iter_intlist_to_table on
http://www.sommarskog.se/arrays-in-sql-2000.html#iter-list-o f-integers

Note: that article is for SQL 2000. If you are on SQL 2005, there is
a separate article for SQL 2000.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx