473,569 Members | 2,472 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SQL 2005 testing

I've begun some testing with the June beta of SQL 2005. One problem
I've hit is with scalar-valued functions. The error I often get when
executing functions is "Select statements included within a function
cannot return data to a client.". These same functions are working
fine under SQL 2000.

Has anybody seen this behavior or know what the deal is?

Jul 23 '05 #1
7 2230
ca**********@gm ail.com wrote:
I've begun some testing with the June beta of SQL 2005. One problem
I've hit is with scalar-valued functions. The error I often get when
executing functions is "Select statements included within a function
cannot return data to a client.". These same functions are working
fine under SQL 2000.

Has anybody seen this behavior or know what the deal is?

Doesn't a SELECT inside of a routine return a result set to the caller?
What would be the effect of doing this inside, say, a function in a
WHERE clause. You may want to assign the result of that select to a
local variable...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Jul 23 '05 #2
Serge,
Yes, I do assign the results of the select to a local variable. These
functions are simple and I'm not having trouble with how to use
functions. The problem is they work with SQL 2000 but produce errors
with SQL 2005. Here is an example of one of the functions.

create function dbo.vfVendorNam e(@SubscriberID int, @ApplicationNum
int)
returns varchar(100)
as
BEGIN
declare @rtn varchar(100)
select @rtn=Vendorname from vAppVendor
where SubscriberID=@S ubscriberID and ApplicationNum= @ApplicationNum
return @rtn
end

Works fine when called from SQL 2000 but gives "Select statements
included within a function cannot return data to a client." when run
from SQL 2005.

Jul 23 '05 #3
Inside a function you are not permitted to execute a SELECT statement that
returns data to the client. Could you post an example of a function that
returns this error in SQL2005 but not in SQL2000.

--
David Portas
SQL Server MVP
--

<ca**********@g mail.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .
I've begun some testing with the June beta of SQL 2005. One problem
I've hit is with scalar-valued functions. The error I often get when
executing functions is "Select statements included within a function
cannot return data to a client.". These same functions are working
fine under SQL 2000.

Has anybody seen this behavior or know what the deal is?

Jul 23 '05 #4
ca**********@gm ail.com (ca**********@g mail.com) writes:
Yes, I do assign the results of the select to a local variable. These
functions are simple and I'm not having trouble with how to use
functions. The problem is they work with SQL 2000 but produce errors
with SQL 2005. Here is an example of one of the functions.

create function dbo.vfVendorNam e(@SubscriberID int, @ApplicationNum
int)
returns varchar(100)
as
BEGIN
declare @rtn varchar(100)
select @rtn=Vendorname from vAppVendor
where SubscriberID=@S ubscriberID and ApplicationNum= @ApplicationNum
return @rtn
end

Works fine when called from SQL 2000 but gives "Select statements
included within a function cannot return data to a client." when run
from SQL 2005.


I rewrote your function as:

create function dbo.blafs(@Orde rID int, @EmployeeID int)
returns varchar(100)
as
BEGIN
declare @rtn nvarchar(100)
select @rtn = CustomerID from Northwind..Orde rs
where OrderID = @OrderID and EmployeeID = @EmployeeID
return @rtn
end
go
SELECT dbo.blafs (10988, 3)
go

Since I only changed table and column names to a database that I have
available, this function should yield the same result as yours. However,
I seem to recall that I have seen a similar problem discussed in the
beta newsgroups, so there might be an issue somewhere.

Anyway, this is not the place where you should discuss SQL 2005.
Please see http://go.microsoft.com/fwlink/?linkid=31765 for access
information to the beta newsgroups for SQL 2005. This is because
these groups are frequented by more SQL Server developers than
this newsgroup.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5
ca**********@gm ail.com (ca**********@g mail.com) writes:
Yes, I do assign the results of the select to a local variable. These
functions are simple and I'm not having trouble with how to use
functions. The problem is they work with SQL 2000 but produce errors
with SQL 2005. Here is an example of one of the functions.

create function dbo.vfVendorNam e(@SubscriberID int, @ApplicationNum
int)
returns varchar(100)
as
BEGIN
declare @rtn varchar(100)
select @rtn=Vendorname from vAppVendor
where SubscriberID=@S ubscriberID and ApplicationNum= @ApplicationNum
return @rtn
end

Works fine when called from SQL 2000 but gives "Select statements
included within a function cannot return data to a client." when run
from SQL 2005.


I rewrote your function as:

create function dbo.blafs(@Orde rID int, @EmployeeID int)
returns varchar(100)
as
BEGIN
declare @rtn nvarchar(100)
select @rtn = CustomerID from Northwind..Orde rs
where OrderID = @OrderID and EmployeeID = @EmployeeID
return @rtn
end
go
SELECT dbo.blafs (10988, 3)
go

Since I only changed table and column names to a database that I have
available, this function should yield the same result as yours. However,
I seem to recall that I have seen a similar problem discussed in the
beta newsgroups, so there might be an issue somewhere.

Anyway, this is not the place where you should discuss SQL 2005.
Please see http://go.microsoft.com/fwlink/?linkid=31765 for access
information to the beta newsgroups for SQL 2005. This is because
these groups are frequented by more SQL Server developers than
this newsgroup.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #6
Erland,
Thanks for pointing me to correct newsgroup. I'll post my issue there.
I do believe this is an issue with sql 2005 as I have several
functions which do the same type of retrieval - some work and some
don't, yet they all work fine on sql 2000
Carey Loomis

Jul 23 '05 #7
Erland,
Thanks for pointing me to correct newsgroup. I'll post my issue there.
I do believe this is an issue with sql 2005 as I have several
functions which do the same type of retrieval - some work and some
don't, yet they all work fine on sql 2000
Carey Loomis

Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2150
by: Peter Hogg | last post by:
Hello, I recently downloaded 'Microsoft Visual C# 2005 Express Edition Beta'. It's been brilliant, but I couldn't help wondering what more you get when you buy 'Visual Studio .NET'. There must be some considerable advantages of using it considering its price. So I'm asking you what 'Visual Studio .NET' has, that 'Microsoft Visual C# 2005...
5
3204
by: Roy Gourgi | last post by:
Hi, I just installed VS 2005 Express with SQL Server and when I try to run the code that was working with VS 2003 is no longer working. Is it not backward compatible. What has changed. Below is my code. TIA Roy
0
983
by: John Hoge | last post by:
I'm testing VWD, which has some great features over VS.NET2003, but there is one feature that it seems to lack: I want to do my development and testing on a local testing server. Dreamweaver handles this with ease - I can work from files on a network share of a testing server and then test the site through that server. This way I can use...
2
1291
by: Craig Throne | last post by:
I see that .NET Studio 2005 contains a unit test framework. Does anyone know if it can do GUI testing as well. What I would like to do is record UI actions (mouse movements, clicks, keyboard, ...) and play them back at a later time while checking component properties (something similiar to what Rational Robot or WinRunner does). The product...
4
2154
by: Peter Rilling | last post by:
Does VS.NET 2005 Professional support integrated unit testing, or is that only with the team system?
25
2458
by: Marco | last post by:
Hi everyone. I've been trying to move some small applications written in vb.net 2003 to vb.net 2005 express just for testing purposes. I have noticed so far that the applications seem to run much slower in the 2005 version. Especially when it comes to nested loops. I haven't done much testing so it might just be this one computer but I...
4
1914
by: =?Utf-8?B?ZGF2aWQ=?= | last post by:
We have to upgrade our Visual Studio .NET 2003 enterprise edition to .NET 2005. However, there is no enterprise edition for Visual Studio .NET 2005. There are 4 versions available for visual studio team edition 2005, Software Architect, Software Developer, Software Tester, and Team Suite which includes all three architect, developer, and...
5
1700
by: =?Utf-8?B?U2FsYW1FbGlhcw==?= | last post by:
Hi, I know that VS 2005 has a lot of testing features and already used them for doing web load testing. I am wondering if it is possible to load test a win forms application. I don't mean writing unit tests, what I need to do is run a load test against my executable and watch CPU utilization and memory usage. When doing web load testing, a...
0
7618
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8138
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7983
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6287
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5514
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5223
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2117
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1228
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
946
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.