Stored procedure parameters with literals not working in SQL Server 2008 R2 -

-

i have stored procedure takes 2 parameters varchar(50). stored procedure few simple queries temp table , returns result set temp table (i removed actual queries)

alter procedure [dbo].[cv_getbooks]     @booknumber varchar(50),     @bookdate varchar(50) begin     -- set nocount on added prevent result sets     -- interfering select statements.     set nocount on;      --a few select statements      if @ismultiple = '0'     begin          select * books booknumber = @booknumber     , bookdate = @bookdate     , bookname  not null     end     else     begin         select * #temp books     booknumber = @booknumber     , bookdate = @bookdate     , bookname   null             select * books     bookauthor not in (select bookauthor #temp)      , booknumber= @booknumber             , bookdate= @bookdate         drop table #temp     end end

i have query installed on local development machine on sql server 2008. have installed on 2 test machines running windows server 2003 , sql server 2008. stored procedure has been working expected.

cv_getbooks '012345678', '06062012' --returns result set expected

i moved test server in remote environment running windows server 2008 , sql server 2008 r2. stored procedure no longer works expected. after running sql profiler have confirmed same code being executed:

cv_getbooks '012345678', '06062012' --run on sql server 2008 r2 returns nothing

when removed quotes query got expected result:

cv_getbooks 012345678, 06062012 --run out quotes server returns expected result set

i have since installed same stored procedure on local version of sql server 2008 r2 , running expected, literal string quotes in place, in first example.

at first thought escape issue passed parameters doesn't seem correct because passed values not contain single quotes.

having installed , had working on many environments, under impression maybe setting in sql server unaware of.

what causing stored procedure not return result set string literals in place on sql server 2008 r2 instance, work correctly out them there?

you did not post table definition of table books, but

cv_getbooks '012345678', '06062012'  --run on sql server 2008 r2 returns nothing  cv_getbooks 012345678, 06062012  --run out quotes server returns expected result set

could caused if booknumber , bookdate numeric rather varchar:

leading 0 in ints dropped, , when converted varchar resulting string not contain leading zero.

it's not clear how data in table affects execution (if statement!) in code.


Comments

Post a Comment

Popular posts from this blog

c# - SVN Error : "svnadmin: E205000: Too many arguments" -

-
i trying reposotries using c# code process svncommand = null; var psi = new processstartinfo("svnadmin"); psi.redirectstandardoutput = true; psi.redirectstandarderror = true; psi.useshellexecute = false; psi.arguments= @"dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump"; using (svncommand = process.start(psi)) { var myoutput = svncommand.standardoutput; var myerror = svncommand.standarderror; debug.write("output :" + environment.newline + environment.newline + myoutput.readtoend() + environment.newline + "error :" + environment.newline + environment.newline + myerror.readtoend()); svncommand.close(); } when use dump command commandline svnadmin dump c:\repositories\myrepo > c:\temp\myrepodumpfile.dump it works fine when try ...
Read more

c++ - Using OpenSSL in a multi-threaded application -

-
i have been writing soap client application in c++ on ubuntu using openssl https transport , pthreads threading. have number of threads - 1 central data acquisition thread periodically gets worker threads make soap requests via shared mutex protected queues. reading documentation openssl found is openssl thread-safe? in openssl faq describes mechanisms required ensure thread safety when using openssl. implemented , works fine. the reason question conceptual difficulty really. thinking implementing same functionality application has already, instead of using threads, create 2 seperate applications : 1 worker thread (of multiple copies running) , main data acquisition thread. use tcp sockets communicate between 2 rather mutex protected queues. may bad idea not important - confusing me would have implement same functions required ensure openssl thread safety in second approach or not? my guess not have , treated independent (indeed surely must many applications use openssl) re...
Read more

All overlapping substrings matching a java regex -

-
is there api method returns (possibly overlapping) substrings match regular expression? for example, have text string: string t = 04/31 412-555-1235; , , have pattern: pattern p = new pattern("\\d\\d+"); matches strings of 2 or more characters. the matches are: 04, 31, 412, 555, 1235. how overlapping matches? i want code return: 04, 31, 41, 412, 12, 55, 555, 55, 12, 123, 1235, 23, 235, 35. theoretically should possible -- there obvious o(n^2) algorithm enumerates , checks substrings against pattern. edit rather enumerating substrings, safer use region(int start, int end) method in matcher . checking pattern against separate, extracted substring might change result of match (e.g. if there non-capturing group or word boundary check @ start/end of pattern). edit 2 actually, it's unclear whether region() expect zero-width matches. specification vague, , experiments yield disappointing results. for example: string line = "xx90xx"; strin...
Read more