Can not call SET ROWCOUNT in a UDF - Example

I forgot to supply an example. The following is a minimal function that
gives the error
CREATE FUNCTION KUKU()
RETURNS INT
BEGIN
SET ROWCOUNT 1
return 1
END
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message
news:O2BhOfTFGHA.3200@.tk2msftngp13.phx.gbl...
> When I call SET ROWCOUNT <number> inside a User Defined Function I get the
> following error:
> Error 443: Invalid use of 'UNKNOWN TOKEN' within a function.
> I couldn't find anything about this error in the BOL.
> Any solution ?
> TIA
> Boaz Ben-Porat
> Milestone systems
> Debmark
>A scalar UDF, by definition, returns exactly one "row", so what do you need
to override it for, and what do you consider to be a "row"? This returns a
single value... there are no rows or columns...
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message
news:eqhochTFGHA.216@.TK2MSFTNGP15.phx.gbl...
>I forgot to supply an example. The following is a minimal function that
>gives the error
> CREATE FUNCTION KUKU()
> RETURNS INT
> BEGIN
> SET ROWCOUNT 1
> return 1
> END
>
>
> "Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message
> news:O2BhOfTFGHA.3200@.tk2msftngp13.phx.gbl...
>

Can not call SET ROWCOUNT in a UDF

When I call SET ROWCOUNT <number> inside a User Defined Function I get the
following error:
Error 443: Invalid use of 'UNKNOWN TOKEN' within a function.
I couldn't find anything about this error in the BOL.
Any solution ?
TIA
Boaz Ben-Porat
Milestone systems
DebmarkUse the SET directive before calling the function?
SET NOCOUNT ON
SELECT * FROM dbo.function()
?
"Boaz Ben-Porat" <bbp@.milestone.dk> wrote in message
news:O2BhOfTFGHA.3200@.tk2msftngp13.phx.gbl...
> When I call SET ROWCOUNT <number> inside a User Defined Function I get the
> following error:
> Error 443: Invalid use of 'UNKNOWN TOKEN' within a function.
> I couldn't find anything about this error in the BOL.
> Any solution ?
> TIA
> Boaz Ben-Porat
> Milestone systems
> Debmark
>|||Seems that SET ROWCOUNT isn't allowed in a UDF.
If you're using QA with syntax coloring, note that ROWCOUNT is not
colored as a keyword, so that would be why the 'UNKNOWN TOKEN' is being
returned.
Boaz Ben-Porat wrote:
> When I call SET ROWCOUNT <number> inside a User Defined Function I get the
> following error:
> Error 443: Invalid use of 'UNKNOWN TOKEN' within a function.
> I couldn't find anything about this error in the BOL.
> Any solution ?
> TIA
> Boaz Ben-Porat
> Milestone systems
> Debmark
>|||correction: SET <OPTION> ON is not allowed in a UDF. (set rowcount, set
nocount, etc.)
Trey Walpole wrote:
> Seems that SET ROWCOUNT isn't allowed in a UDF.
> If you're using QA with syntax coloring, note that ROWCOUNT is not
> colored as a keyword, so that would be why the 'UNKNOWN TOKEN' is being
> returned.
> Boaz Ben-Porat wrote:
>|||Hello, Boaz
You cannot use SET ROWCOUNT in a UDF, because a function cannot contain
statements that cause side-effects. If you wish to use a constant for
SET ROWCOUNT, you can use the "TOP n" clause, instead. If you want to
use a variable (or a parameter) you can use the "TOP (n)" clause in SQL
Server 2005 (but I understand that you use SQL Server 2000). If you
cannot upgrade to SQL Server 2005, it may be possible to use a
subquery, but it will be slower (you should consider using a stored
procedure instead of a function).
Razvan

Can MSDE trigger call a .NET remote object?

Hi,
I wonder if MSDE trigger can call a remote .NET object
,i want to get notify when a new row was added or changed
also if I have an access application(forms etc...) can I still use it with
MSDE?
thanks in advance.
hi Julia,
"Julia" <codewizard@.012.net.il> ha scritto nel messaggio
news:u6ih4$XwEHA.4048@.TK2MSFTNGP15.phx.gbl
> Hi,
> I wonder if MSDE trigger can call a remote .NET object
> ,i want to get notify when a new row was added or changed
>
actually it should not be the case... you could perhaps "shell" out to
server's local resources, but this is not a good practice :D
this is usually not the correct scenario for client server applications...
if you really need something like that, schedule a data refresh from time to
time, say, every hour :D
stop kidding, ok.. perhaps you should rely on other forms of data
investigation..

> also if I have an access application(forms etc...) can I still use it
> with MSDE?
>
usually yes... an .ADP project will manage you connection(s) to a SQL
Server/MSDE database, while the Access project only includes forms
definitions, reports and so on..
http://www.amazon.com/exec/obidos/tg...glance&s=books
... a little dated, but still valuable book by Mary Chipman
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Can I use SQL_Mobile as PC desktop server or PC local cache server?

Can I use SQL_Mobile as PC desktop server or PC local cache server?
I want many data to be cached at user's PC,
and call for this data to be created transparently as for remote
main SQL server.
Is it possible to do without creating complex structure of doubling functionality
of main queries to single SQL server ?
some articles, samples, links, keywords ?
thanks you

SQL Server Mobile cannot be used on Desktop for production use. It is restricted by licensing terms.

The next version of SQL Server Mobile known as SQL Server Everywhere is available for production use on Dekstop. The CTP of SQL Server Everywhere is at the following location - http://www.microsoft.com/sql/ctp_sqleverywhere.mspx The data can be cached in the user's PC in SQL Server Everywhere. The data can be replicated between SQL Server Everywhere and SQL Server by Merge or RDA Replication. Link to Replication is - http://msdn2.microsoft.com/en-us/library/ms171801(SQL.90).aspx

Regards

Ambrish

Can I use dynamic SQL in a stored procedure to call another stored

Hi,
I am having one stored procedure named SP1 which is having one input and one
output parameter as follows
CREATE PROCEDURE dbo.SP1
@.xmldoc TEXT
,@.FLAG_EXEC VARCHAR(5)
AS
BEGIN
...
END
I have write another stored procedure named SP2 which is creating a dynamic
SQL statement like
CREATE PROCEDURE dbo.SP2
AS
BEGIN
DECLARE @.CURR_PROC_NAME VARCHAR(125)
DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
DECLARE @.STR_RET_QRY_MN VARCHAR(125)
DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
@.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE'' '
print @.STR_EXECUTE_QRY
EXEC (@.STR_EXECUTE_QRY)
END
but it always says to declare @.STR_RETURN_STRING variable. Can I do like
above? If yes then how? If no then why?
With thanx in advance,Hi Rajendra,
this does not work because each EXEC is running in it's own process.
That means in fact that proc2 does not know anything about the variables
you have declared in proc1.
You should handle it as follows:
Proc2 (Sub-Proc) writes the ReturnValue into a table
Proc2 (Calling Proc) reads after the run of Proc2 the value from the table
Another way is to work with "sp_executesql". See BOL for details...
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm|||Rajendra (Rajendra@.discussions.microsoft.com) writes:
> I have write another stored procedure named SP2 which is creating a
> dynamic SQL statement like
> CREATE PROCEDURE dbo.SP2
> AS
> BEGIN
> DECLARE @.CURR_PROC_NAME VARCHAR(125)
> DECLARE @.STR_XML_AS_VARCHAR VARCHAR(125)
> DECLARE @.STR_RET_QRY_MN VARCHAR(125)
> DECLARE @.STR_EXECUTE_QRY VARCHAR(4000)
> SET @.STR_EXECUTE_QRY = 'exec ' + @.CURR_PROC_NAME + ''''+
> @.STR_XML_AS_VARCHAR + ''' , @.STR_RET_QRY_MN OUTPUT ,@.FLAG_EXEC = ''FALSE''
'
> print @.STR_EXECUTE_QRY
> EXEC (@.STR_EXECUTE_QRY)
> END
> but it always says to declare @.STR_RETURN_STRING variable. Can I do like
> above? If yes then how? If no then why?
You are making it too complicated. Just say:
EXEC @.CURR_PROC_NAME @.STR_XML_AS_VARCHAR, STR_RET_QRY_MN OUTPUT,
@.FLAG_EXEC = 'FALSE'
This will execute the procedure of which the name is in @.CURR_PROC_NAME.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx