Eric Barro
ebarro at verizon.net
Tue Feb 6 12:41:50 CST 2007
Only if you have SP4 installed. :) -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francisco Tapia Sent: Tuesday, February 06, 2007 10:31 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL in SP failing 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=hme > >mtaglin > >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... _______________________________________________ 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