Francisco Tapia
fhtapia at gmail.com
Tue Feb 6 12:31:14 CST 2007
You can debug your stored procedures in Query Analyzer by right clicking on the procedure in the object explorer and selecting debug. This will allow you to see what the variable's value is and why it worked sometimes. -- Francisco On 2/6/07, Mark A Matte <markamatte at hotmail.com> wrote: > 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(r) > http://movies.msn.com/movies/oscars2007/?icid=ncoscartagline1 > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > > > -- -Francisco http://sqlthis.blogspot.com | Tsql and More...