[dba-SQLServer] SQL in SP failing

Thanks Eric,

That almost did the trick...if that were a 'true' representation of what I 
was doing...it worked fine...but I am passing @ID in place of 'test' and @ID 
is the criteria used to isolate the record.  I really needed double quoates 
because @ID value contains single quotes.  I used Char(34) instead and 
worked like a charm.

Thanks Again,

Mark A. Matte

P.S...I still don't understand why it would run sometimes?

>From: "Eric Barro" <ebarro at verizon.net>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: Re: [dba-SQLServer] SQL in SP failing
>Date: Tue, 06 Feb 2007 07:40:36 -0800
>
>Mark,
>
>You need to use CHAR(39) for the single quotes.
>
>CREATE PROCEDURE [Select_Records_1]
>(
>	@ID nvarchar(255)
>)
>AS
>
>declare @sql as varchar(1000)
>
>Set @sql ='insert into tblBrita_ID(case_id,Criteria) SELECT case_id, ' +
>CHAR(39) + 'test' + CHAR(39)
>Set @sql = @sql + ' As Criteria FROM tblCase where case_id=1'
>
>exec (@sql)
>
>--Eric
>
>-----Original Message-----
>From: dba-sqlserver-bounces at databaseadvisors.com
>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mark A
>Matte
>Sent: Tuesday, February 06, 2007 7:23 AM
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] SQL in SP failing
>
>Arthur,
>
>I've pretty much given up on understanding this one. Below is the SP in
>question.  This is not the final version I use...but a simplified version 
>to
>show the problem:
>
>CREATE PROCEDURE [Select_Records_1] @ID nvarchar(255)  AS declare @sql as
>varchar(1000) declare @nsql as nchar(3000) set @sql='insert into
>tblBrita_ID(case_id,Criteria) SELECT case_id, "test"
>As Criteria FROM tblCase where case_id=1'
>set @nsql=@sql
>set nocount on
>exec sp_executesql @nsql
>set nocount off
>*********************
>This works...sometimes...I think I need the double quotes because "test" is
>just some text I put in.  If I don't need the doublequotes how to I show
>that test is NOT a field.
>
>To get this to work...I remove all criteria and the word test(replace with 
>a
>field)...and running the query that calls the SP...if it runs...I add some
>criteria back in...and keep doing this until I have rebuilt the SP.  
>Finally
>it will run...so I save and all is good.  But the next day I change just 
>the
>table name...try to run it...and it fails...I don't get it???  Then I just
>start modifying until I get back to where it started....I am confused.
>
>Thanks,
>
>Mark A. Matte
>
>
> >From: artful at rogers.com
> >Reply-To: dba-sqlserver at databaseadvisors.com
> >To: dba-sqlserver at databaseadvisors.com
> >Subject: Re: [dba-SQLServer] SQL in SP failing
> >Date: Mon, 5 Feb 2007 14:59:04 -0800 (PST)
> >
> >Missed this earlier. The double quotes are your problem.
> >
> >Arthur Fuller
> >Technical Writer, Data Modeler, SQL Sensei Artful Databases
> >Organization www.artfulsoftware.com
> >
> >
> >
> >
> >----- Original Message ----
> >From: Mark A Matte <markamatte at hotmail.com>
> >To: dba-sqlserver at databaseadvisors.com
> >Sent: Thursday, November 30, 2006 2:13:44 PM
> >Subject: [dba-SQLServer] SQL in SP failing
> >
> >
> >Hello All,
> >
> >Any idea why the following SQL will fail in an SP?
> >
> >'insert into tblBrita_ID(case_id,Criteria) SELECT case_id, "test" As
> >Criteria FROM tblCase where case_id=1'
> >
> >The error is "Invalid column name 'test'."  It doesn't always
> >fail...normally 'test' is a parameter passed to the SP...I have
> >modified it to the above for demo purposes...but using just the word
> >'test' as above still gives the same error.
> >
> >Thanks,
> >
> >Mark A. Matte
> >_______________________________________________
> >dba-SQLServer mailing list
> >dba-SQLServer at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> >http://www.databaseadvisors.com
> >
>
>_________________________________________________________________
>Search for grocery stores. Find gratitude. Turn a simple search into
>something more.
>http://click4thecause.live.com/search/charity/default.aspx?source=hmemtaglin
>e_gratitude&FORM=WLMTAG
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.1.411 / Virus Database: 268.17.28/672 - Release Date: 2/6/2007
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
>From predictions to trailers, check out the MSN Entertainment Guide to the 
Academy Awards® 
http://movies.msn.com/movies/oscars2007/?icid=ncoscartagline1