Error in sql stored procedure

I've a stored procedure which returns values based on 7 criterias. It was working fine and returned the values properly. I added one more criteria for returning values from 2 database columns based on minimum and maximum values. It's not working properly and gives syntax error. Could someone tell me what mistake I'm doing? Thanks.

ALTERprocedure [dbo].[USP_Account_Search_Mod]

@.ClientCode VARCHAR(7)=''

,@.DebtorName VARCHAR(25)=''

,@.DebtorNumberINT= 0

,@.AccountNumber VARCHAR(30)=''

,@.ReferenceNumber VARCHAR(30)=''

,@.TierINT= 0

,@.Status VARCHAR(5)=''

,@.UserIDINT

,@.MonthDateTime=NULL

,@.FromDateDateTime=NULL

,@.ToDateDateTime=NULL

,@.OriginalMindecimal= 0

,@.OriginalMaxdecimal= 0

,@.CurrentMindecimal= 0

,@.CurrentMaxdecimal=0

,@.lstAmountSelect VARCHAR(3)

,@.IsActivebit= 1

AS

DECLARE

@.SQLTier1Select VARCHAR(2000)

,@.SQLTier2Select VARCHAR(2000)

,@.Criteria VARCHAR(2000)

,@.SQL VARCHAR(8000)

,@.CRI1 VARCHAR(100)

,@.CRI2 VARCHAR(100)

,@.CRI3 VARCHAR(100)

,@.CRI4 VARCHAR(100)

,@.CRI5 VARCHAR(100)

,@.CRI6 VARCHAR(200)

,@.CRI7 VARCHAR(500)

,@.CRI8 VARCHAR(500)

,@.CRI9 VARCHAR(500)

SELECT @.CRI1=''

,@.CRI2=''

,@.CRI3=''

,@.CRI4=''

,@.CRI5=''

,@.CRI6=''

,@.CRI7=''

,@.CRI8=''

,@.CRI9=''

,@.Criteria=''

SELECT @.DebtorName=REPLACE(@.DebtorName,'''','''''');

Print @.DebtorName

if(SELECT UserTypeIDFROM dbo.tbl_Security_UsersWhere UserID= @.UserID)= 3AND @.ClientCode=''

return(-1)

IFLEN(@.DebtorName)> 0

SET @.CRI1=' AND Name like '+'''%'+ @.DebtorName+'%'''

IF @.DebtorNumber> 0

SET @.CRI2=' AND Number = '+CAST(@.DebtorNumberAS VARCHAR(7))

IFLEN(@.AccountNumber)> 1

SET @.CRI3=' AND AccountNumber like '+'''%'+ @.AccountNumber+'%'''

IFLEN(@.ReferenceNumber)> 0

SET @.CRI4=' AND Account like '+'''%'+ @.ReferenceNumber+'%'''

IFLEN(@.ClientCode)> 1

SET @.CRI5=' AND Customer = '+''''+ @.ClientCode+''''

SET @.Status=RTRIM(@.Status)

IF((@.StatusNotIN('ALL','ALA','ALI'))AND(LEN(@.Status)>1))

BEGIN

IF(@.Status='PAID')

SET @.CRI6=''

IF(@.Status='CANC')

SET @.CRI6=' AND Code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryCancelledT1 = 1 OR SearchCategoryCancelledT2 = 1)'

END

--PRINt @.CRI6

IFLEN(CONVERT(CHAR(8), @.Month, 112))> 0

BEGIN

IF(LEN(CONVERT(CHAR(8), @.FromDate, 112))> 0ANDLEN(CONVERT(CHAR(8), @.ToDate, 112))> 0)

BEGIN

SET @.CRI7=' AND Received BETWEEN '+''''+CONVERT(CHAR(8), @.FromDate, 112)+''''+' AND '+''''+CONVERT(CHAR(8), @.ToDate, 112)+''''

END

ELSE

BEGINSET @.CRI7=' AND DATEPART(mm, Received) = DATEPART(mm, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''+') AND DATEPART(yy, Received) = DATEPART(yy, '+''''+CONVERT(CHAR(8), @.Month, 112)+''''

END

END

IF @.lstAmountSelect='ALL'

SET @.CRI8=''

elseIF @.lstAmountSelect='DR'

BEGIN

SET @.CRI8=' AND OriginalBalance >= '+convert(Varchar,@.OriginalMin)+'AND OriginalBalance<='+convert(Varchar,@.OriginalMax)+' AND CurrentBalance >= '+convert(Varchar,@.CurrentMin)+'AND CurrentBalance<='+convert(Varchar,@.CurrentMax)

END

ELSEIF @.lstAmountSelect='OLC'

BEGIN

SET @.CRI8=' AND OriginalBalance < CurrentBalance '

END

ELSEIF @.lstAmountSelect='OGC'

BEGIN

SET @.CRI8=' AND OriginalBalance > CurrentBalance '

END

ELSEIF @.lstAmountSelect='OEC'

BEGIN

SET @.CRI8=' AND OriginalBalance = CurrentBalance '

END

SELECT @.Criteria= @.CRI1+ @.CRI2+ @.CRI3+ @.CRI4+ @.CRI5+ @.CRI6+ @.CRI7+ @.CRI8

--PRINT @.Criteria

--PRINT @.CRI7

if @.Status='ALL'OR @.Status='ALA'OR @.Status='ALI'--All Period

BEGIN

if(@.Status='ALL')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryAllT2 = 1)'

END

if(@.Status='ALA')--All Active

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryActiveT2 = 1)'

END

if(@.Status='ALI')--All Inactive

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT1 = 1)'

SELECT @.SQLTier2Select='SELECT TOP 1000 * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryInactiveT2 = 1)'

END

END

ELSEIF @.Status='PAID'

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria+' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000Tier2.dbo.payhistory ph1 LEFT JOIN Collect2000Tier2.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT2 = 1))'

END

ELSE

BEGIN

SELECT @.SQLTier1Select='SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

SELECT @.SQLTier2Select='SELECT * FROM dbo.UDV_Tier2Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers('+CAST(@.UserIDAS VARCHAR(4))+'))'+ @.Criteria

END

SELECT @.SQL=CASE @.Tier

WHEN 0THEN @.SQLTier1Select+' UNION '+ @.SQLTier2Select+'ORDER BY NAME ASC'

WHEN 1THEN @.SQLTier1Select+'ORDER BY NAME ASC'

WHEN 2THEN @.SQLTier2Select+'ORDER BY NAME ASC '

END

PRINT @.SQL

--SELECT @.SQL

EXEC(@.SQL)

Could you help us help you better by opening your stored procedure up in a query analyzer (like SQL 2005 QA or SQL Express Managment Studio) and parsing the query to determine what line number and what the syntax error is?

That is a lot of code and would be difficult for us to go over line by line to determine what the issue is.

|||

I do not have SQL Query Analyzer. Here is the error I get when I saw in event viewer

Transaction (Process ID 64) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction., Error Source:.Net SqlClient Data Provider.

|||

there's a lock on one of the tables your trying to do an insert on or update on. Talk with your DBA about that error and let him/her know what the Process ID is to help track it down.

Out of curiosity, what environment are you writing your stored procedures in?

|||

I'm writing my stored procedure in SQL Server Management Studio Express

|||

In SQL Server Management Studio Express you can parse your query (stored procedure syntax) and it will either tell you the syntax error along with the line number or tell you that the command was successful, which means you can run (execute) it and thus create your stored procedure.

Error in Sql Server 2000 sp4

I′ve got a problem with SQL server. The server stops responding and gives me
out an error below.
Anyone can help me. When it runs this select in the database the server
keeps with 80% 100% processor up and stops responding all client requests.
Memory
MemoryLoad = 94%
Total Physical = 2047 MB
Available Physical = 116 MB
Total Page File = 8041 MB
Available Page File = 6246 MB
Total Virtual = 2047 MB
Available Virtual = 281 MB
*Stack Dump being sent to E:\Microsoft SQL Server\MSSQL\log\SQLDump0009.txt
*
************************************************** ***************************
**
*
* BEGIN STACK DUMP:
* 02/23/07 10:17:59 spid 89
*
* Exception Address = 00402836
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00000004
* Input Buffer 82 bytes -
* SELECT * from ProdutosParaSiteWeb_ANALISE
Luis,
Is ProdutosParaSiteWeb_ANALISE a view? If so, examine the code for the view
for complexity. Maybe you have uncovered a SQL Server bug, but recrafting
the code could work around the problem.
If ProdutosParaSiteWeb_ANALISE is a table, or the view is very simple, then
you might be concerned with database integrity. Run as DBCC CHECKDB and see
if it reveals any errors.
RLF
"Luis" <Luis@.discussions.microsoft.com> wrote in message
news:D1CA43FA-34B2-4A2A-8D83-529913BB66AB@.microsoft.com...
> Ive got a problem with SQL server. The server stops responding and gives
> me
> out an error below.
> Anyone can help me. When it runs this select in the database the server
> keeps with 80% 100% processor up and stops responding all client requests.
> Memory
> MemoryLoad = 94%
> Total Physical = 2047 MB
> Available Physical = 116 MB
> Total Page File = 8041 MB
> Available Page File = 6246 MB
> Total Virtual = 2047 MB
> Available Virtual = 281 MB
> *Stack Dump being sent to E:\Microsoft SQL
> Server\MSSQL\log\SQLDump0009.txt
> *
> ************************************************** ***************************
> **
> *
> * BEGIN STACK DUMP:
> * 02/23/07 10:17:59 spid 89
> *
> * Exception Address = 00402836
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred writing address 00000004
> * Input Buffer 82 bytes -
> * SELECT * from ProdutosParaSiteWeb_ANALISE
>

Error in Sql Server 2000 sp4

I′ve got a problem with SQL server. The server stops responding and gives m
e
out an error below.
Anyone can help me. When it runs this select in the database the server
keeps with 80% 100% processor up and stops responding all client requests.
Memory
MemoryLoad = 94%
Total Physical = 2047 MB
Available Physical = 116 MB
Total Page File = 8041 MB
Available Page File = 6246 MB
Total Virtual = 2047 MB
Available Virtual = 281 MB
*Stack Dump being sent to E:\Microsoft SQL Server\MSSQL\log\SQLDump0009.txt
*
****************************************
************************************
*
**
*
* BEGIN STACK DUMP:
* 02/23/07 10:17:59 spid 89
*
* Exception Address = 00402836
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred writing address 00000004
* Input Buffer 82 bytes -
* SELECT * from ProdutosParaSiteWeb_ANALISELuis,
Is ProdutosParaSiteWeb_ANALISE a view? If so, examine the code for the view
for complexity. Maybe you have uncovered a SQL Server bug, but recrafting
the code could work around the problem.
If ProdutosParaSiteWeb_ANALISE is a table, or the view is very simple, then
you might be concerned with database integrity. Run as DBCC CHECKDB and see
if it reveals any errors.
RLF
"Luis" <Luis@.discussions.microsoft.com> wrote in message
news:D1CA43FA-34B2-4A2A-8D83-529913BB66AB@.microsoft.com...
> Ive got a problem with SQL server. The server stops responding and gives
> me
> out an error below.
> Anyone can help me. When it runs this select in the database the server
> keeps with 80% 100% processor up and stops responding all client requests.
> Memory
> MemoryLoad = 94%
> Total Physical = 2047 MB
> Available Physical = 116 MB
> Total Page File = 8041 MB
> Available Page File = 6246 MB
> Total Virtual = 2047 MB
> Available Virtual = 281 MB
> *Stack Dump being sent to E:\Microsoft SQL
> Server\MSSQL\log\SQLDump0009.txt
> *
> ****************************************
**********************************
***
> **
> *
> * BEGIN STACK DUMP:
> * 02/23/07 10:17:59 spid 89
> *
> * Exception Address = 00402836
> * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
> * Access Violation occurred writing address 00000004
> * Input Buffer 82 bytes -
> * SELECT * from ProdutosParaSiteWeb_ANALISE
>

Error in Snapshot replication

Hi,
I've a little problem in my sanpshot replication.
I triy to replicate a db in a new server, everythig works fine expect one
stored procedure.
This store procedure, when replicated, generate the error number: 207 -
"Invalid column name 'SELECT c.CliID AS ID_Client, '."
This is a simple sp that create dinamically a select command and execute it
(with the exec command) at the end of the sp.
If I try to copy manually this sp to the replicated server no error occurred
and the sp works fine.
Any suggestion?
Thanks
Hi Ramon,
I am guessing that double-quotes (") instead of single-quotes (') are used
for delimiting string-literals in the stored procedure that you are trying to
replicate and unfortunately this is not supported at all in SQL2000 or
earlier versions. I have posted the following two years back in an attempt to
explain a bit about how this came to be broken although I can imagine that
you will find little comfort in my explanation:
http://groups.google.com/group/micro... e6aaa0ff1c69
While I am glad to say that this particular scenario (using " as string
delimiters in text objects) is handled properly in SQL2005 (only need SQL2005
distributor\snapshot agent), here are a couple of workarounds that you can
implement if you need to stay on SQL2000 or earlier:
1) Use sp_addscriptexec (SQL2000) to implement replication of the stored
procedure definition yourself
2) Alter your stored procedures at your publisher so that single-quotes
instead of double-quotes are used for delimiting string-lterals.
HTH
-Raymond
"Ramon" wrote:

> Hi,
> I've a little problem in my sanpshot replication.
> I triy to replicate a db in a new server, everythig works fine expect one
> stored procedure.
> This store procedure, when replicated, generate the error number: 207 -
> "Invalid column name 'SELECT c.CliID AS ID_Client, '."
> This is a simple sp that create dinamically a select command and execute it
> (with the exec command) at the end of the sp.
> If I try to copy manually this sp to the replicated server no error occurred
> and the sp works fine.
> Any suggestion?
> Thanks
|||I changed the double-quotas with single-quotas (only one sp was
using double-quotaas) and the entire replication works fine.
Thnak you for the suggestion.
"Raymond Mak [MSFT]" wrote:
[vbcol=seagreen]
> Hi Ramon,
> I am guessing that double-quotes (") instead of single-quotes (') are used
> for delimiting string-literals in the stored procedure that you are trying to
> replicate and unfortunately this is not supported at all in SQL2000 or
> earlier versions. I have posted the following two years back in an attempt to
> explain a bit about how this came to be broken although I can imagine that
> you will find little comfort in my explanation:
> http://groups.google.com/group/micro... e6aaa0ff1c69
> While I am glad to say that this particular scenario (using " as string
> delimiters in text objects) is handled properly in SQL2005 (only need SQL2005
> distributor\snapshot agent), here are a couple of workarounds that you can
> implement if you need to stay on SQL2000 or earlier:
> 1) Use sp_addscriptexec (SQL2000) to implement replication of the stored
> procedure definition yourself
> 2) Alter your stored procedures at your publisher so that single-quotes
> instead of double-quotes are used for delimiting string-lterals.
> HTH
> -Raymond
> "Ramon" wrote:

Error in Distribution Agent

Hello.
We're running SQL Server 2000.
I've created a transactional publication with queued updating at server
"DATASERVER1" .
When I attempt to push a subscription I receive an error in the Distribution
Agent, as follows:
Server 'DATASERVER1' is not configured for DATA ACCESS
Can anyone tell me what this means? I cannot find anything on MSDN.
Thank you in advance,
Mike
Can you check the value of sp_configure 'remote access' on the subscriber.
Also, please check select @.@.servername to ensure the name is the
netbiosname.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi, Paul. Thanks for the response.
I ran "sp_configure 'remote access'" and got: minimum=1, maximum=1,
config_value=1, run_value=1.
The name is the NetBios name.
Does it matter that the subscriber and distributor is the same machine?
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uO6qyNGPFHA.1884@.TK2MSFTNGP15.phx.gbl...
> Can you check the value of sp_configure 'remote access' on the subscriber.
> Also, please check select @.@.servername to ensure the name is the
> netbiosname.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Mike,
this is not a problem. Usually it is the other way round - publisher and
distributor on the same box, though - is there a particular reason you are
implementing it this way? As for the message, I'm not sure what the problem
is . Is there connectivity between the servers? Can you open a QA windon
on the publisher and connect to the subscriber ok? What if you try setting
up a linked server on the subscriber to the publisher as a separate test -
do you get the same error?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

error handling flow

i've got a stored procedure (a) that calls another stored procedure
(b). inside b i become a deadlock victim and i die. how can i "catch"
this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
using sql server 2003 so no try/catch. thanks!
arthur
You're not using SQL Server 2003, since it doesn't exists. I assume a typo and you mean 2000.
You cannot catch a deadlock at the TSQL level. You have to do it in the client. This is one of the
nice things with TRY/CATCH in 2005, that you can catch a deadlock situation at the TSQL level.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"arthur" <alangham@.gmail.com> wrote in message
news:1138909906.663154.56220@.z14g2000cwz.googlegro ups.com...
> i've got a stored procedure (a) that calls another stored procedure
> (b). inside b i become a deadlock victim and i die. how can i "catch"
> this in a? using if @.@.error <> 0 in a doesn't seem to work. also, i'm
> using sql server 2003 so no try/catch. thanks!
> arthur
>
|||2000, right. thanks for clearing that up (not the 2000 part, the catch
part)!