Line 1: Incorrect Syntax Near.......(is this a bug?)

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1

Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Line 1: Incorrect Syntax Near.......

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1

Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Line 1: Incorrect syntax near getlistofspclass.

what am i doing wrong.. :(

CREATE PROCEDURE getlistofspclass
(
@.date datetime,
@.s varchar(8000) output
)

AS

select @.s = coalesce(@.s + ' & ', '') + '"' + convert(varchar(10),dbo.calader.timestart)+ ' ' + dbo.classT.classname + '<p>'
FROM dbo.calader INNER JOIN dbo.classT ON dbo.calader.classid = dbo.classT.classcode
WHERE dbo.calader.caledardatedates = @.date
exec( @.s)
GO

The procedure's parameters should not be enclosed in parentheses. Remove the parentheses that surround the parameters and you should have better luck.|||

my god!! i spent 4 hours on that and $60 on a Que sql server programming book. :(

but now it works. i can hang myself now

|||

hello can you help me again. ...my asp.net code is not working.. it will not send the date to the pramater.

can you make a sameple code in asp.net vb of how you will send a date to that stored procuder and how you would get the returned value

please. thank you verymuch

|||

All I have is a C# version I made the other day, sorry. But it's close enough to VB you should be able to figure out how to port it.

1public int InsertNewThread(Thread ThreadToInsert,out int InternalID)2{3using(SqlConnection sqlConnection =new SqlConnection(GetConnectionString()))4{5SqlCommand sqlCommand =new SqlCommand("Thread_Insert", sqlConnection);6sqlCommand.CommandType = CommandType.StoredProcedure;7sqlCommand.Parameters.Add("@.threadSummary", SqlDbType.NVarChar, 75).Value = ThreadToInsert.ThreadSummary;8sqlCommand.Parameters.Add("@.boardID", SqlDbType.Int).Value = ThreadToInsert.BoardID;9sqlCommand.Parameters.Add("@.internalID", SqlDbType.Int).Direction = ParameterDirection.Output;10sqlConnection.Open();11int RowsEffected = sqlCommand.ExecuteNonQuery();12sqlConnection.Close();13InternalID = (int) sqlCommand.Parameters["@.internalID"].Value;14return RowsEffected;15}16}

12/****** Object: Stored Procedure dbo.Thread_Insert Script Date: 7/28/2006 7:24:19 PM ******/3CREATE PROCEDURE Thread_Insert4@.threadSummaryNVARCHAR( 75 ),5@.internalIDINT OUTPUT6AS7INSERT INTO8 threads( threadSummary )910VALUES11 ( @.threadSummary )1213SELECT14@.internalID = SCOPE_IDENTITY ( )1516GO

|||

thanks again. you know the speed of getting the return value was just the same as putting the sql statement on the webpage itself...people say storedprocedures are faster..oh well

thanks everything worked

|||

Stored procedures are faster. If you coded one page without stored procedures, and another with stored procedures, and timed them with your watch, then you won't notice much.

Take a web server (or multiple web servers) using complex queries and time the difference when they are getting hit a few hundred requests per second, and you will notice a difference, especially in older versions of SQL Server. The newer ones have added a few little things that reduce the difference dramatically in most cases (Cached query plans, even for dynamic queries). They basically turn even dynamic queries into a kind of "temporary" stored procedure, getting most of the sp's benefits, a very nice addition to SQL Server.

Limits to length of Stored Proc

In SQL Server 2000, I've got a rather lengthy stored procedure, which
creates a lot of temporary tables as it processes down through a few
sets of data.
When testing it through Query Analyzer, it runs fine (a bit slow
though). But when I try to run it through the ade, it doesn't do
anything. It runs through the procedure in milliseconds but doesn't
seem to ever actually start it. If I change the calling code in the
ade VBA to refer to a different SP, it will call/run the different SP,
so I don't think its the way I call it.
Is there a limit to the number of lines a stored procedure can have,
or some other limit on memory or transactions?I doubt the proc size is the issue. Have you included SET NOCOUNT ON at
the beginning of the proc?

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"C Kirby" <ckirby@.mindspring.com> wrote in message
news:psdqlvklfnjtml6v4jr8ambl2c3fns0lk0@.4ax.com...
> In SQL Server 2000, I've got a rather lengthy stored procedure, which
> creates a lot of temporary tables as it processes down through a few
> sets of data.
> When testing it through Query Analyzer, it runs fine (a bit slow
> though). But when I try to run it through the ade, it doesn't do
> anything. It runs through the procedure in milliseconds but doesn't
> seem to ever actually start it. If I change the calling code in the
> ade VBA to refer to a different SP, it will call/run the different SP,
> so I don't think its the way I call it.
> Is there a limit to the number of lines a stored pr|||Hi

You would not be able to compile the Stored procedure if you have hit a size
limit, although it could be the query cost that is somehow behaving
differently and therefore hitting that limit (see sp_configure/ query
governor cost limit in Books online).

If you can run this through QA it seems most likely that you are not passing
the parameters incorrectly in your code, so try adding some debug
statements! If the procedure is as long as you say, it is probably worth
consider modularising it and splitting it into sub-procedures; you may also
be able to re-write the code to be more efficient. This may also help stop
recompilations.

John

"C Kirby" <ckirby@.mindspring.com> wrote in message
news:psdqlvklfnjtml6v4jr8ambl2c3fns0lk0@.4ax.com...
> In SQL Server 2000, I've got a rather lengthy stored procedure, which
> creates a lot of temporary tables as it processes down through a few
> sets of data.
> When testing it through Query Analyzer, it runs fine (a bit slow
> though). But when I try to run it through the ade, it doesn't do
> anything. It runs through the procedure in milliseconds but doesn't
> seem to ever actually start it. If I change the calling code in the
> ade VBA to refer to a different SP, it will call/run the different SP,
> so I don't think its the way I call it.
> Is there a limit to the number of lines a stored procedure can have,
> or some other limit on memory or transactions?|||Thanks for the help.. I do have NOCOUNT set to on, so I don't think
that is the problem.

I've tried to setup the debugging by using the IF @.@.ERROR <>0 ,
Rollback and return X.
I can't seem to get the front end to actually look at the return value
to tell if the sp ran successfully though, so something isn't quite
right with that.

As far as the parameters go, this sp doesn't use any, so I'm not
sending it any. Could this be why the front end isn't reading the
return parameter?

One question on splitting out the different functions into separate
stored procs. The very first thing the sp does is to create a temp
table holding the records to be manipulated. Right now it is named
#temp (or something like that). In order to reference this table from
another sp, should a use the double # ('##temp')?

On Tue, 9 Sep 2003 09:02:09 +0100, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi
>You would not be able to compile the Stored procedure if you have hit a size
>limit, although it could be the query cost that is somehow behaving
>differently and therefore hitting that limit (see sp_configure/ query
>governor cost limit in Books online).
>If you can run this through QA it seems most likely that you are not passing
>the parameters incorrectly in your code, so try adding some debug
>statements! If the procedure is as long as you say, it is probably worth
>consider modularising it and splitting it into sub-procedures; you may also
>be able to re-write the code to be more efficient. This may also help stop
>recompilations.
>John
>
>"C Kirby" <ckirby@.mindspring.com> wrote in message
>news:psdqlvklfnjtml6v4jr8ambl2c3fns0lk0@.4ax.com...
>> In SQL Server 2000, I've got a rather lengthy stored procedure, which
>> creates a lot of temporary tables as it processes down through a few
>> sets of data.
>> When testing it through Query Analyzer, it runs fine (a bit slow
>> though). But when I try to run it through the ade, it doesn't do
>> anything. It runs through the procedure in milliseconds but doesn't
>> seem to ever actually start it. If I change the calling code in the
>> ade VBA to refer to a different SP, it will call/run the different SP,
>> so I don't think its the way I call it.
>> Is there a limit to the number of lines a stored procedure can have,
>> or some other limit on memory or transactions?|||> I've tried to setup the debugging by using the IF @.@.ERROR <>0 ,
> Rollback and return X.
> I can't seem to get the front end to actually look at the return value
> to tell if the sp ran successfully though, so something isn't quite
> right with that.
> As far as the parameters go, this sp doesn't use any, so I'm not
> sending it any. Could this be why the front end isn't reading the
> return parameter?

The return value is essentially an output parameter. Does your
procedure return resultsets? If so, you may need to consume those
before the return value is available.

> One question on splitting out the different functions into separate
> stored procs. The very first thing the sp does is to create a temp
> table holding the records to be manipulated. Right now it is named
> #temp (or something like that). In order to reference this table from
> another sp, should a use the double # ('##temp')?

The local temp table (#temp) is visible to the nested procs so you don't
need to resort to a global (##temp) table. An issue with global temp
tables is that you'll need to uniquely name them to handle concurrency.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"C Kirby" <ckirby@.mindspring.com> wrote in message
news:4m7ulvo2b8fbn7d230e30es5pl299c8to3@.4ax.com...
> Thanks for the help.. I do have NOCOUNT set to on, so I don't think
> that is the problem.
> I've tried to setup the debugging by using the IF @.@.ERROR <>0 ,
> Rollback and return X.
> I can't seem to get the front end to actually look at the return value
> to tell if the sp ran successfully though, so something isn't quite
> right with that.
> As far as the parameters go, this sp doesn't use any, so I'm not
> sending it any. Could this be why the front end isn't reading the
> return parameter?
> One question on splitting out the different functions into separate
> stored procs. The very first thing the sp does is to create a temp
> table holding the records to be manipulated. Right now it is named
> #temp (or something like that). In order to reference this table from
> another sp, should a use the double # ('##temp')?
> On Tue, 9 Sep 2003 09:02:09 +0100, "John Bell"
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >You would not be able to compile the Stored procedure if you have hit
a size
> >limit, although it could be the query cost that is somehow behaving
> >differently and therefore hitting that limit (see sp_configure/ query
> >governor cost limit in Books online).
> >If you can run this through QA it seems most likely that you are not
passing
> >the parameters incorrectly in your code, so try adding some debug
> >statements! If the procedure is as long as you say, it is probably
worth
> >consider modularising it and splitting it into sub-procedures; you
may also
> >be able to re-write the code to be more efficient. This may also help
stop
> >recompilations.
> >John
> >"C Kirby" <ckirby@.mindspring.com> wrote in message
> >news:psdqlvklfnjtml6v4jr8ambl2c3fns0lk0@.4ax.com...
> >> In SQL Server 2000, I've got a rather lengthy stored procedure,
which
> >> creates a lot of temporary tables as it processes down through a
few
> >> sets of data.
> >> When testing it through Query Analyzer, it runs fine (a bit slow
> >> though). But when I try to run it through the ade, it doesn't do
> >> anything. It runs through the procedure in milliseconds but
doesn't
> >> seem to ever actually start it. If I change the calling code in
the
> >> ade VBA to refer to a different SP, it will call/run the different
SP,
> >> so I don't think its the way I call it.
> >> Is there a limit to the number of lines a stored procedure can
have,
> >> or some other limit on memory or transactions?|||Dan Guzman (danguzman@.nospam-earthlink.net) writes:
> The return value is essentially an output parameter. Does your
> procedure return resultsets? If so, you may need to consume those
> before the return value is available.

To add to what Dan says here, it depends on whether you are using
client-side or server-side cursor. With client-side cursors you can
access the return value directly.

But we are a bit in the dark here, as we have not seen any of your
code, neither the ADO code, nor the SQL code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You guys have *no* idea how much I appreciate the help!!

Here's the code that I am using to call the sp from the Access ade

'call stored procedure
docmd.hourglass true
Set com = New ADODB.Command
With com
.ActiveConnection = getadoconnectstring("SM")
.CommandText = "qryBSTransPost"
.CommandType = adCmdStoredProc
.CommandTimeout = 0
.Execute , , adAsyncExecute

If .Parameters(0) <> 0 Then
'operation failed
MsgBox "Fail"
Else
DoCmd.Hourglass False
MsgBox "Transaction Import Complete", vbOKOnly, "Brokerage
Transactions Imported"
End If

End With
Set com = Nothing

This is all going into a pre-exisiting software package that has the
getadoconnectionstring function. Since the simple stored procs work
with this call, I'm going to say that the connection is ok.

Due to the length of the sp, I won't post it, but would be glad to let
anyone see it (even though it is *ugly*!!). It does not return any
records. It basically looks at a table, picks the records that meet a
certain criteria, creates a temp table to hold the Primary keys to
those records, then runs through a series of data manipulations using
more temp tables, then adds those created records into a couple of
different tables, then changes some values in the original records so
that they no longer meet the initial criteria.

This is the final section of the sp:
IF @.@.ERROR <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN 11
END

COMMIT TRANSACTION
GO

Regardless of what actually happens with the sp when I call it from
the ade, the .parameter(0) value never triggers the 'fail' option..

On Wed, 10 Sep 2003 22:26:36 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>Dan Guzman (danguzman@.nospam-earthlink.net) writes:
>> The return value is essentially an output parameter. Does your
>> procedure return resultsets? If so, you may need to consume those
>> before the return value is available.
>To add to what Dan says here, it depends on whether you are using
>client-side or server-side cursor. With client-side cursors you can
>access the return value directly.
>But we are a bit in the dark here, as we have not seen any of your
>code, neither the ADO code, nor the SQL code.|||Is there some reason you are using the adAsyncExecute option here? If
not, you might try removing the option from your Execute method.

It looks to me like your code isn't written to handle asynchronous proc
execution. The code is checking the return value even though the proc
may still be executing. The code probably works with your other procs
simply because they complete before you check the result.

Also, note @.@.ERROR is changed after every SQL statement so you need to
check it after each statement and perform error processing then. For
example:

BEGIN TRAN
INSERT INTO MyTable VALUES(1)
IF @.@.ERROR <> 0 GOTO ErrorHandler
INSERT INTO MyTable VALUES(2)
IF @.@.ERROR <> 0 GOTO ErrorHandler
COMMIT
RETURN 0

ErrorHandler:
IF @.@.TRANCOUNT > 0 ROLLBACK
RETURN 11

--
Hope this helps.

Dan Guzman
SQL Server MVP

"C Kirby" <ckirby@.mindspring.com> wrote in message
news:d2bvlvgj5c2hqv1mm6fqohdsh641pmifp3@.4ax.com...
> You guys have *no* idea how much I appreciate the help!!
> Here's the code that I am using to call the sp from the Access ade
> 'call stored procedure
> docmd.hourglass true
> Set com = New ADODB.Command
> With com
> .ActiveConnection = getadoconnectstring("SM")
> .CommandText = "qryBSTransPost"
> .CommandType = adCmdStoredProc
> .CommandTimeout = 0
> .Execute , , adAsyncExecute
> If .Parameters(0) <> 0 Then
> 'operation failed
> MsgBox "Fail"
> Else
> DoCmd.Hourglass False
> MsgBox "Transaction Import Complete", vbOKOnly, "Brokerage
> Transactions Imported"
> End If
> End With
> Set com = Nothing
> This is all going into a pre-exisiting software package that has the
> getadoconnectionstring function. Since the simple stored procs work
> with this call, I'm going to say that the connection is ok.
> Due to the length of the sp, I won't post it, but would be glad to let
> anyone see it (even though it is *ugly*!!). It does not return any
> records. It basically looks at a table, picks the records that meet a
> certain criteria, creates a temp table to hold the Primary keys to
> those records, then runs through a series of data manipulations using
> more temp tables, then adds those created records into a couple of
> different tables, then changes some values in the original records so
> that they no longer meet the initial criteria.
> This is the final section of the sp:
> IF @.@.ERROR <> 0
> BEGIN
> ROLLBACK TRANSACTION
> RETURN 11
> END
> COMMIT TRANSACTION
> GO
>
> Regardless of what actually happens with the sp when I call it from
> the ade, the .parameter(0) value never triggers the 'fail' option..
>
> On Wed, 10 Sep 2003 22:26:36 +0000 (UTC), Erland Sommarskog
> <sommar@.algonet.se> wrote:
> >Dan Guzman (danguzman@.nospam-earthlink.net) writes:
> >> The return value is essentially an output parameter. Does your
> >> procedure return resultsets? If so, you may need to consume those
> >> before the return value is available.
> >To add to what Dan says here, it depends on whether you are using
> >client-side or server-side cursor. With client-side cursors you can
> >access the return value directly.
> >But we are a bit in the dark here, as we have not seen any of your
> >code, neither the ADO code, nor the SQL code.|||Looks like that was the problem, Dan! I removed the adAsync option
and the procedure is running!!!! Thanks for the help!!

I'm going to take a shot at splitting the the sp into a few sub
procedures and then see if I can get the return value to work..

Thanks for all the help from everybody!!!!!!!

On Thu, 11 Sep 2003 03:02:13 GMT, "Dan Guzman"
<danguzman@.nospam-earthlink.net> wrote:

>Is there some reason you are using the adAsyncExecute option here? If
>not, you might try removing the option from your Execute method.
>It looks to me like your code isn't written to handle asynchronous proc
>execution. The code is checking the return value even though the proc
>may still be executing. The code probably works with your other procs
>simply because they complete before you check the result.
>Also, note @.@.ERROR is changed after every SQL statement so you need to
>check it after each statement and perform error processing then. For
>example:
>BEGIN TRAN
>INSERT INTO MyTable VALUES(1)
>IF @.@.ERROR <> 0 GOTO ErrorHandler
>INSERT INTO MyTable VALUES(2)
>IF @.@.ERROR <> 0 GOTO ErrorHandler
>COMMIT
>RETURN 0
>ErrorHandler:
>IF @.@.TRANCOUNT > 0 ROLLBACK
>RETURN 11

limiting the number of record sets returned by a stored procedure

Hi,
I know that a stored procedure will return as many results sets as
SELECT statements are in. I want to actually mark which selects should be
returned as results sets and which are just internal. Is there a way of
doing this?
Thanks,
George.George,
There is noway to do this in t-sql. Why are you doing exactly?
if you are doing this kind of operation:
select col1, col2, ..., coln
from table1
where col1 like 'microsoft%'
if @.@.rowcount > 0
...
you can use:
if exists(select * from table1 where col1 like 'microsoft%')
...
AMB
"George Tihenea" wrote:

> Hi,
> I know that a stored procedure will return as many results sets as
> SELECT statements are in. I want to actually mark which selects should be
> returned as results sets and which are just internal. Is there a way of
> doing this?
> Thanks,
> George.
>
>|||Thanks,
Here are some more details. I have a stored procedure like this:
// start of stored proc, then
....
SELECT c1, c2, c3 from ...
WHERE (condition here)
if ( @.returned_rows > 0 ) return 0; /* all ok return*/
/* let the flow continue */
select c1, c2, c3 from...
where (a different condition here)
if ( @.returned_rows > 0 ) return 0; /* all ok return*/
/* let the flow continue */
......................
return 0 /* did not find anything*/
/// end of stored procedure
All this worked ok and I can get the result set from my OLEDB middle tier
using multiple results sets. Of cause I will always get ONLY ONE result set
but OLEDB needs to use the template with multiple results sets to work...
then I had to modify the stored precedure to do an INSERT before finishing.
Here is a scheleton code:
// start of stored proc, then
....
SELECT c1, c2, c3 from ...
WHERE (condition here)
if ( @.returned_rows > 0 ) go to FINISH /* all ok return*/
/* let the flow continue */
select c1, c2, c3 from...
where (a different condition here)
if ( @.returned_rows > 0 ) go to FINISH; /* all ok return*/
/* let the flow continue */
......................
FINISH:
INSERT INTO ....
VALUES (...)
return 0
/// end of stored procedure
The problem is this INSERT. For some reasons I cannot understand, my OLEDB
consumer templates will think that there are 3 result sets instead of 2! And
when I try to read the last one it will just crash while binding the
columns, which is normal because this last result set is bogus!!?
Thanks,
George.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F7B5BE46-FCA1-485B-902C-9F8D37629C78@.microsoft.com...
> George,
> There is noway to do this in t-sql. Why are you doing exactly?
> if you are doing this kind of operation:
> select col1, col2, ..., coln
> from table1
> where col1 like 'microsoft%'
> if @.@.rowcount > 0
> ...
> you can use:
> if exists(select * from table1 where col1 like 'microsoft%')
> ...
>
> AMB
>
> "George Tihenea" wrote:
>|||If you need these "other" select statements for debugging what I do is
add a debug variable to each stored procedure:
declare @.Debug int
and then in the TSQL code I test it:
if @.Debug = 1
Select * from Scheduler
Of course you can only run these from Query Analyzer interactively but
typically that is where you draw the line for debugging SPs.
On Thu, 17 Mar 2005 08:53:01 -0500, "George Tihenea"
<tihenea@.comcast.net> wrote:

> Hi,
> I know that a stored procedure will return as many results sets as
>SELECT statements are in. I want to actually mark which selects should be
>returned as results sets and which are just internal. Is there a way of
>doing this?
> Thanks,
> George.
>

Limiting concurrent execution of a T-SQL stored procedure

Hello,

I have a quite large set of legacy stored procedures. The stored procedures are not reentrant, i.e. can not safely be executed in parallel by multiple threads. The procedures are not called multiple times simultaneously in the system, because of this restriction. There's however no explicit constraint to prevent multiple calls to these stored procedures and thus it's possible to e.g. accidentally call the procedures many times simultaneously.

My problem is, how to prevent multiple simultaneous executions of the stored procedures? Is there a native way to do this? In modern programming languages there are language constructs to prevent simultaneous access to code and variables (synchronized in Java, lock in C#). Is there something similar in T-SQL? What could be a workaround?

One options could be to use SQL Server jobs to prevent simultaneous accesss - only one instance of a job can be running at time. This is, however, problematic since if the job cannot be started because another instance is already running, the second instance will never be started. This is not what I won't.

JM

Refer to Books Online about the use of sp_getapplock and sp_releaseapplock.

Also, refer to this post. And this one.

|||

Thanks Arnie for your quick and good answer!

r,

JM

|||

Yes..

You can achive it using few tricks..using sp_getapplock.

But you need a wrapper storedproc or batch for this. Check the following example it will help you to desing your wrapper sp/batch statement.

Sample:

Code Snippet

Create table MyTable

(

RowId int identity(1,1),

HitStartedAt datetime,

HitTimestamp datetime,

UserName varchar(100)

)

Go

Create proc LegacyProc (@.user varchar(100), @.CalledTime datetime)

as

Begin

Insert Into MyTable

Values(@.CalledTime, getdate(), @.user);

--To wait for 10 sec : not required for your procedures, producing the latency to check the concurrent users action

WAITFOR DELAY '000:00:10'

End

Go

Create Proc MyProc

(

@.user varchar(100)

)

as

Begin

Declare @.PorcName as NVarchar(1000), @.CalledTime datetime

Begin Tran

--To get the Current SP Name, it should be unique for each SP / each batch

SET @.PorcName =object_name(@.@.ProcID)

SET @.CalledTime = Getdate()

--Lock the Current Proc

Exec sp_getapplock @.Resource = @.PorcName, @.LockMode = 'Exclusive'

--Execute Your Legacy Procedures

Exec LegacyProc @.user, @.CalledTime

--Release the lock

Exec sp_releaseapplock @.Resource = @.PorcName

Commit Tran

End

limite the resource a stored procedure can use

Dear sir,
Some times, some unimportant stored procedure on my application are using
too much resource, etc 50% of CUP power. Is there a way to set the priority
of a SP or limite the max resource it can use?
thanks,
Guoqi Zheng
http://www.ureader.comThe only thing you can limit in SQL Server is how many processors a SQL
statement can use on a multi-processor machine. You can do this by adding
the OPTION (MAXDOP n) at the end of the statement, where n is the maximum
number of processors you want the statement to use.
Jacco Schalkwijk
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:dfc72681649d44efadd824ba066e2126@.ur
eader.com...
> Dear sir,
> Some times, some unimportant stored procedure on my application are using
> too much resource, etc 50% of CUP power. Is there a way to set the
> priority
> of a SP or limite the max resource it can use?
> thanks,
> Guoqi Zheng
> http://www.ureader.com|||To add on to Jacco's response, consider index and query tuning if you
haven't already done so. Excessive CPU utilization can be an indication
that additional indexes are needed or a poorly formulated query. It's best
to address the underlying cause when possible.
Hope this helps.
Dan Guzman
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:dfc72681649d44efadd824ba066e2126@.ur
eader.com...
> Dear sir,
> Some times, some unimportant stored procedure on my application are using
> too much resource, etc 50% of CUP power. Is there a way to set the
> priority
> of a SP or limite the max resource it can use?
> thanks,
> Guoqi Zheng
> http://www.ureader.com|||Thanks.
The CPU usage is because of some calculation. like using SELECT COUNT(*),
SUM(*) to update another table, and the records to be counted or summed are
a few 100000s.
I haven't know what might be better to do those calculation yet. The
calculation needs to be done a few times per day.
any suggestions?
regards,
Guoqi Zheng
http://www.ureader.com|||You might consider using an indexed view for frequently used aggregations.
Covering indexes may also help.
Hope this helps.
Dan Guzman
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:b685cc9d335546c0891cf9e7fe284d09@.ur
eader.com...
> Thanks.
> The CPU usage is because of some calculation. like using SELECT COUNT(*),
> SUM(*) to update another table, and the records to be counted or summed
> are
> a few 100000s.
> I haven't know what might be better to do those calculation yet. The
> calculation needs to be done a few times per day.
> any suggestions?
> regards,
> Guoqi Zheng
> http://www.ureader.com

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanksSQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:

> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanks
SQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:

> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

limit the resources of a job

hi
we develop a stored procedure that run a dll, the dll consumes all the
processor, and take a long of time (2 days) processing data.
its posible limit the amount of cpu uses by a single stored procedure?
i know that using the windows system resource manager its posible limit
the amount of cpu uses by all sql server, but it's possible for only a
stored procedure?
thanksSQL Server can't control what your external process does. You said it
is the DLL and not the proc that consumes your resources.
Two days is an extremely long time to execute a proc. Why not invoke
your code from outside SQL Server using .NET or VB or something else?
That way you may be able to add some code to monitor and control what
happens during processing.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Alternatively, you may try to relax the grip on the processor (e.g. by not
using tight loops) in the code of the DLL if that doesn't lead to
unacceptable performance.
Linchi
"hongo32" wrote:
> hi
> we develop a stored procedure that run a dll, the dll consumes all the
> processor, and take a long of time (2 days) processing data.
> its posible limit the amount of cpu uses by a single stored procedure?
> i know that using the windows system resource manager its posible limit
> the amount of cpu uses by all sql server, but it's possible for only a
> stored procedure?
> thanks
>

Limit the number of records returned in Stored procedure.

In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.

Query

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 the top 100 records:

SELECT TOP 100 *

Select the top 10% of the total number of records:

SELECT TOP 10 percent *

|||

Or if you want to retrieve a limited number of records, but not necessarily the top X records, you could use the row_number() function. For example, to retrieve records 250-299 you could try

 
1SELECT*
2FROM3(
4SELECT *, row = row_number()OVER (ORDER BY id)
6FROM yourtable
7) a
8WHERE rowBETWEEN 250AND 299