Populate a Table with Stored Proc.

I am looking to populate a Schedule table with information from two
other tables. I am able to populate it row by row, but I have created
tables that should provide all necessary information for me to be
able
to automatically populate a "generic" schedule for a few weeks or
more
at a time.

The schedule table contains:
(pk) schedule_id, start_datetime, end_datetime, shift_employee,
shift_position

A DaysOff table contains:
(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in day
of
week (1-7) form

A CalendarDays table contains:
(pk) date, calendar_dow <-- dow contains the day of week number (as
above) for each day until 2010.

My main question is how to put all of this information together and
have SQL populate the rows with data based on days off. Any
suggestions?Nate (nate.borland@.westecnow.com) writes:

Quote:

Originally Posted by

I am looking to populate a Schedule table with information from two
other tables. I am able to populate it row by row, but I have created
tables that should provide all necessary information for me to be able
to automatically populate a "generic" schedule for a few weeks or more
at a time.
>
The schedule table contains:
(pk) schedule_id, start_datetime, end_datetime, shift_employee,
shift_position
>
>
A DaysOff table contains:
(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in day
of
week (1-7) form
>
>
A CalendarDays table contains:
(pk) date, calendar_dow <-- dow contains the day of week number (as
above) for each day until 2010.
>
>
My main question is how to put all of this information together and
have SQL populate the rows with data based on days off. Any
suggestions?

Just as a reminder, in case you are getting old and don't remember
what you did yesterday, you posted this question yesterday as well,
and I replied by asking some questions, and Plamen Ratchev suggested
some queries. I suggest that you go Google news and find the old
thread and review our replies.

--
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|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Nate (nate.borland@.westecnow.com) writes:

Quote:

Originally Posted by

>I am looking to populate a Schedule table with information from two
>other tables. I am able to populate it row by row, but I have created
>tables that should provide all necessary information for me to be able
>to automatically populate a "generic" schedule for a few weeks or more
>at a time.
>>
>The schedule table contains:
>(pk) schedule_id, start_datetime, end_datetime, shift_employee,
>shift_position
>>
>>
>A DaysOff table contains:
>(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in day
>of
>week (1-7) form
>>
>>
>A CalendarDays table contains:
>(pk) date, calendar_dow <-- dow contains the day of week number (as
>above) for each day until 2010.
>>
>>
>My main question is how to put all of this information together and
>have SQL populate the rows with data based on days off. Any
>suggestions?

>
Just as a reminder, in case you are getting old and don't remember
what you did yesterday, you posted this question yesterday as well,
and I replied by asking some questions, and Plamen Ratchev suggested
some queries. I suggest that you go Google news and find the old
thread and review our replies.
>
>

Hmm. I'm getting old and I don't do daft things like that!
Now, what was I doing before I read this?...

Poor Performance from Web Server

Hi,

I am having a problem with one of my stored procedures in SQL Server 2005. Basically the proc brings back a data set for the ASP.NET front end, but it is running very slowly from .NET.

I have run SQL profiler on the procedure and its taking around 20 seconds to bring back the data for the .NET, where as if I copy and paste the executed SP from profiler into the management studio and run it in a query window, it runs in around 1 second, even if I run DBCC DROPCLEANBUFFERS before I run it. More worryingly, the CPU usage is 40 times higher and the number of reads is 50% higher from .NET.

We have the .NET front end spread over 3 clustered web servers with load balancers and the SQL db is on a dedicated rig. I am having the same problem on my locally published version of the site as well, so I don't think it's an issue with the web site.

If anyone has got any ideas on this then please let me know as I am completely stuck. I should mention that the issue has only recently started occuring and it used to be fine and the rest of the site is fine...

Thanks in advance

Tom

maybe put some trace statements to echo out the time it execute a line of code. This way you can maybe find the bottleneck in your DAL. Are you using the Data Access Application Blocks. I have found those to be very valuable to manage my connections. I never see issues like you are describing anymore. Back in the day, like 4 years ago maybe when I was doing things on my own. Are you trying to fill a custom list or collection with a large set of records? I found that takes way too long and just op for datasets, readers or change my procedure to return a fixed set of rows.

But try the tracing thing to see what line(s) take the longest to execute and I think you will find your issue.

Poor Linked Sybase Server performance

Hi
I have a problem with performance when I execute proc against linked Sybase
Server.
Here is a strings I used to add a server:
sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
Execute of proc is taking a second when it run directly on Sybase, is taking
about 20 sec.
I've never noticed any network problems in our environment before.
Any ideas?
Does anybody know a better alternative to connect to Sybase?
Try the instructions on the sybase website for creating a linked server.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C26C4E8F-D554-47E8-B207-04AF2A6954A9@.microsoft.com...
> Hi
> I have a problem with performance when I execute proc against linked
Sybase
> Server.
> Here is a strings I used to add a server:
> sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
> sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
> Execute of proc is taking a second when it run directly on Sybase, is
taking
> about 20 sec.
> I've never noticed any network problems in our environment before.
> Any ideas?
> Does anybody know a better alternative to connect to Sybase?

Poor Linked Sybase Server performance

Hi
I have a problem with performance when I execute proc against linked Sybase
Server.
Here is a strings I used to add a server:
sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
Execute of proc is taking a second when it run directly on Sybase, is taking
about 20 sec.
I've never noticed any network problems in our environment before.
Any ideas?
Does anybody know a better alternative to connect to Sybase?Try the instructions on the sybase website for creating a linked server.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C26C4E8F-D554-47E8-B207-04AF2A6954A9@.microsoft.com...
> Hi
> I have a problem with performance when I execute proc against linked
Sybase
> Server.
> Here is a strings I used to add a server:
> sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
> sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
> Execute of proc is taking a second when it run directly on Sybase, is
taking
> about 20 sec.
> I've never noticed any network problems in our environment before.
> Any ideas?
> Does anybody know a better alternative to connect to Sybase?sql

Poor Linked Sybase Server performance

Hi
I have a problem with performance when I execute proc against linked Sybase
Server.
Here is a strings I used to add a server:
sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
Execute of proc is taking a second when it run directly on Sybase, is taking
about 20 sec.
I've never noticed any network problems in our environment before.
Any ideas?
Does anybody know a better alternative to connect to Sybase?Try the instructions on the sybase website for creating a linked server.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:C26C4E8F-D554-47E8-B207-04AF2A6954A9@.microsoft.com...
> Hi
> I have a problem with performance when I execute proc against linked
Sybase
> Server.
> Here is a strings I used to add a server:
> sp_addlinkedserver N'hetco_sybase', ' ', N'MSDASQL', N'hetco_sybase'
> sp_addlinkedsrvlogin N'hetco_sybase', false, null, N'sa', 'sa_password'
> Execute of proc is taking a second when it run directly on Sybase, is
taking
> about 20 sec.
> I've never noticed any network problems in our environment before.
> Any ideas?
> Does anybody know a better alternative to connect to Sybase?

Pls Help With JOIN query...

I'm trying to write a stored proc...

Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...

tblItems also contains a category column (int) which contains a categoryID
of 0..3...

I then have a category table which has CategoryID, Name, and DisplayOrder...

So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...

this is what I have now:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder

this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...

Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
the same category name.Just remove the order by ie:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1
/* order by tblCategories.DisplayOrder */

Nobody wrote:

Quote:

Originally Posted by

I'm trying to write a stored proc...
>
Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...
>
tblItems also contains a category column (int) which contains a categoryID
of 0..3...
>
I then have a category table which has CategoryID, Name, and DisplayOrder...
>
So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...
>
this is what I have now:
>
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder
>
this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...
>
Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
the same category name.

|||Okay my mistake. This will do the job:

select a.tblCategories.Name
from (select DISTINCT top 100 percent tblCategories.Name,
tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by
tblCategories.Name,tblCategories.DisplayOrder) a

Unlike other databases, SQL Server does not allow 'order by' within
derived tables, so had to use top etc...

othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

Just remove the order by ie:
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1
/* order by tblCategories.DisplayOrder */
>
Nobody wrote:

Quote:

Originally Posted by

I'm trying to write a stored proc...

Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...

tblItems also contains a category column (int) which contains a categoryID
of 0..3...

I then have a category table which has CategoryID, Name, and DisplayOrder...

So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...

this is what I have now:

select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder

this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...

Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
the same category name.

|||Nobody (nobody@.cox.net) writes:

Quote:

Originally Posted by

I'm trying to write a stored proc...
>
Basically, I have a tblItems table which contains a list of every item
available. One of the columns in this table is the brand... for test
purposes, I hardcoded the BrandID=1...
>
tblItems also contains a category column (int) which contains a categoryID
of 0..3...
>
I then have a category table which has CategoryID, Name, and
DisplayOrder...
>
So basically what I'm trying to do is return a list of Category NAMES that
have items in them for a specifc brand... but I want to sort the returned
categories by the DisplayOrder column...
>
this is what I have now:
>
>
select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by tblCategories.DisplayOrder
>
this does what I want it to do, but its returning TWO columns... Name AND
DisplayOrder... I only want to return Name, but if I take the DisplayOrder
out of the select portion, it errors out because it can't order by that...
>
Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies
of the same category name.

No, you don't need DISTINCT. You need to learn to use EXISTS:

SELECT C.Name
FROM tblCategories C
WHERE EXISTS (SELECT *
FROM tblItems I
WHERE I.CategoryID = C.CategoryID
AND I.BrandID = @.brandid)
ORDER BY C.DisplayOrder

--
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|||On Wed, 22 Nov 2006 16:59:17 -0800, Nobody wrote:

Quote:

Originally Posted by

>I'm trying to write a stored proc...
>
>Basically, I have a tblItems table which contains a list of every item
>available. One of the columns in this table is the brand... for test
>purposes, I hardcoded the BrandID=1...
>
>tblItems also contains a category column (int) which contains a categoryID
>of 0..3...
>
>I then have a category table which has CategoryID, Name, and DisplayOrder...
>
>So basically what I'm trying to do is return a list of Category NAMES that
>have items in them for a specifc brand... but I want to sort the returned
>categories by the DisplayOrder column...
>
>this is what I have now:
>
>
>select DISTINCT tblCategories.Name, tblCategories.DisplayOrder from
>tblCategories
>INNER JOIN tblItems
>on tblCategories.CategoryID = tblItems.CategoryID
>where BrandID=1 order by tblCategories.DisplayOrder
>
>this does what I want it to do, but its returning TWO columns... Name AND
>DisplayOrder... I only want to return Name, but if I take the DisplayOrder
>out of the select portion, it errors out because it can't order by that...
>
>Any ideas? Obviously I need the DISTINCT keyword so I dont get 10 copies of
>the same category name.
>

Hi Nobody,

Since you don't display any columns from tblItems, the only reason to
use it in this query is obviously to check for existance of at least one
row with BrandID equal to 1. That means that you can rewrite your query
as

SELECT c.Name --, c.DisplayOrder
FROM Categories AS c
WHERE EXISTS
(SELECT *
FROM Items AS i
WHERE i.CategoryID = c.CategoryID
AND i.BrandID = 1)
ORDER BY c.DisplayOrder;

You'll probably see a performance increase as well.

--
Hugo Kornelis, SQL Server MVP|||On 23 Nov 2006 01:48:32 -0800, othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

>Okay my mistake. This will do the job:
>
>select a.tblCategories.Name
>from (select DISTINCT top 100 percent tblCategories.Name,
>tblCategories.DisplayOrder
from tblCategories
INNER JOIN tblItems
on tblCategories.CategoryID = tblItems.CategoryID
where BrandID=1 order by
>tblCategories.Name,tblCategories.DisplayOrder) a
>
>Unlike other databases, SQL Server does not allow 'order by' within
>derived tables, so had to use top etc...

Hi othellomy,

Though you can use ORDER BY in a subquery if you also use TOP, the ORDER
BY will only be used to determins which rows meat the TOP criterium;
there is no guarantee that the actual order of the query will be the
same. In fact, SQL Server 2005 will ignore both TOP 100 PERCENT and the
accomanying ORDER BY, since it is essentially a no-op to restrict the
output to 100 percent of the regular output.

If you really want to move the DISTINCT to a subquery (which in this
case is NOT needed - see my reply to Nobody), you could use

SELECT a.Name
FROM (SELECT DISTINCT c.Name, c.DisplayOrder
FROM Categories AS c
INNER JOIN Items AS i
ON i.CategoriID = c.CategoryID
WHERE i.BrandID = 1) AS a
ORDER BY a.DisplayOrder;

(untested)

--
Hugo Kornelis, SQL Server MVP

Pls help in making this proc work

Hi,

I just found a procedure to make pivot tables,But iam getting an error,can someone help in Overcoming the error.

The syntax is as below

CREATE PROCEDURE crosstab

@.select varchar(8000),

@.sumfunc varchar(100),

@.pivot varchar(100),

@.table varchar(100)

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '

+ @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns

WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql= @.sql + '''' + convert(varchar(100), pivot) + ''' = ' +

stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '

+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)

SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)

SET ANSI_WARNINGS ON

just run the procedure and help me to fix the error.The error iam getting is

Msg 156, Level 15, State 1, Procedure crosstab, Line 23

Incorrect syntax near the keyword 'pivot'.

Any help is greatly apprecited.

Thanks,

SVGP

We will need a little more information to help you debug this.

Please provide DDL (Create statements for any tables involved), Sample data (In the form of Insert statements) and the command you are using to execute this stored Procedure (Including parameters)

|||

Hi,

There are so many tables involved with lots of data.

But the problem is not during the execution of proc,its while creating the proc,which doesnot involve any of the tables.

If still you require i will send them,

Thanks,

SVGP

|||

Where you have:

convert(varchar(100), pivot)

Change to

convert(varchar(100), [pivot])

PIVOT is a reserverd word in SQL2005.

(I assume that pivot is a column name?)

HTH!

|||

Yes you are right Thanks a lot

SVGP.

|||

Pls do the following corrections,

Code Snippet

CREATE PROCEDURE crosstab

@.select varchar(8000),

@.sumfunc varchar(100),

@.pivot varchar(100),

@.table varchar(100)

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @.pivot + ' AS [pivot] INTO ##pivot FROM ' + @.table + ' WHERE 1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table + ' WHERE '

+ @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )

WHEN 0 THEN '' ELSE '''' END

FROM tempdb.information_schema.columns

WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql= @.sql + '''' + convert(varchar(100), [pivot]) + ''' = ' +

stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '

+ @.delim + convert(varchar(100), [pivot]) + @.delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)

SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql + ' ')

EXEC (@.select)

SET ANSI_WARNINGS ON

Pls Help - no clues as to what field

I am running a report that is using a stored proc that has been changed. I
made the changes that I am aware of however there seems to be a field
somewhere in an expression that causes this error ... I cannot figure out how
to locate the field!
It doesnt tell me what expression or what field. The error occurs in report
preview and the text is this:
An error occurred during local report processing.
an unexpected error occurred in the report processing.
the expression referenced a non-existing field in the fields collection.Double check your SQL. If you have something that you have wrapped with a
function (ie. LTRIM(tbl.Field), this will not have a fields name. You
would need to do: LTRIM(tbl.Field) as FieldName
Hope that helps
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:E98971F5-7FB1-47E9-BDCA-8EE206328488@.microsoft.com...
>I am running a report that is using a stored proc that has been changed. I
> made the changes that I am aware of however there seems to be a field
> somewhere in an expression that causes this error ... I cannot figure out
> how
> to locate the field!
> It doesnt tell me what expression or what field. The error occurs in
> report
> preview and the text is this:
> An error occurred during local report processing.
> an unexpected error occurred in the report processing.
> the expression referenced a non-existing field in the fields collection.
>|||are you saying that I should check the stored procedure? I am not using sql.
I have verified that all the fields are coming back from the stored
procedure and exist in the fields collection so this is very strange.
"Chris" wrote:
> Double check your SQL. If you have something that you have wrapped with a
> function (ie. LTRIM(tbl.Field), this will not have a fields name. You
> would need to do: LTRIM(tbl.Field) as FieldName
> Hope that helps
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:E98971F5-7FB1-47E9-BDCA-8EE206328488@.microsoft.com...
> >I am running a report that is using a stored proc that has been changed. I
> > made the changes that I am aware of however there seems to be a field
> > somewhere in an expression that causes this error ... I cannot figure out
> > how
> > to locate the field!
> > It doesnt tell me what expression or what field. The error occurs in
> > report
> > preview and the text is this:
> >
> > An error occurred during local report processing.
> > an unexpected error occurred in the report processing.
> > the expression referenced a non-existing field in the fields collection.
> >
> >
>
>|||Yes, Check the syntax in the stored proc, if you want to post it, I'll look
at it as well.
I ran in to this same problem last week. I altered a stored proc and all of
a sudden I was getting this error, found that I had put an
ISNULL(tbl.FieldName, 0) and it came back to SQL Reports as an unknown
field. As soon as I made it ISNULL(tb.FIeldName,0) as 'CurrentValue' the
field was no longer a problem in SQL Reports.
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:7249105D-B46E-40BB-B468-DCC97C47A767@.microsoft.com...
> are you saying that I should check the stored procedure? I am not using
> sql.
> I have verified that all the fields are coming back from the stored
> procedure and exist in the fields collection so this is very strange.
> "Chris" wrote:
>> Double check your SQL. If you have something that you have wrapped with
>> a
>> function (ie. LTRIM(tbl.Field), this will not have a fields name. You
>> would need to do: LTRIM(tbl.Field) as FieldName
>> Hope that helps
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:E98971F5-7FB1-47E9-BDCA-8EE206328488@.microsoft.com...
>> >I am running a report that is using a stored proc that has been changed.
>> >I
>> > made the changes that I am aware of however there seems to be a field
>> > somewhere in an expression that causes this error ... I cannot figure
>> > out
>> > how
>> > to locate the field!
>> > It doesnt tell me what expression or what field. The error occurs in
>> > report
>> > preview and the text is this:
>> >
>> > An error occurred during local report processing.
>> > an unexpected error occurred in the report processing.
>> > the expression referenced a non-existing field in the fields
>> > collection.
>> >
>> >
>>|||this may be a bit of an interesting dilemma. I will let you know the
outcome. I am combining 2 reports into one report. Each report seems to run
successfully one its own. When I put them together and run them ... I get
that error. The interesting part is that the 2 reports use the same stored
procedure however they return different result sets depending on the
parameter values sent. I have 2 separate datasets set up and 2 separate
tables set up to render the data so in theory this should work from what I
have read. I am wondering if I am running into an issue where it somehow
*thinks* a field is missing because it exists in one result set and not the
other? I am reworking this solution again ... step by step to see at what
point it fails. As of now I have each separate report working and I am about
to combiine them again. I will post again. Thanks for your help ... that is
still a possibility (the stored procs) and I will keep that in mind ... I
have to involve another group for that so I need to verifiy the point of
failure first.
"Chris" wrote:
> Yes, Check the syntax in the stored proc, if you want to post it, I'll look
> at it as well.
> I ran in to this same problem last week. I altered a stored proc and all of
> a sudden I was getting this error, found that I had put an
> ISNULL(tbl.FieldName, 0) and it came back to SQL Reports as an unknown
> field. As soon as I made it ISNULL(tb.FIeldName,0) as 'CurrentValue' the
> field was no longer a problem in SQL Reports.
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:7249105D-B46E-40BB-B468-DCC97C47A767@.microsoft.com...
> > are you saying that I should check the stored procedure? I am not using
> > sql.
> > I have verified that all the fields are coming back from the stored
> > procedure and exist in the fields collection so this is very strange.
> >
> > "Chris" wrote:
> >
> >> Double check your SQL. If you have something that you have wrapped with
> >> a
> >> function (ie. LTRIM(tbl.Field), this will not have a fields name. You
> >> would need to do: LTRIM(tbl.Field) as FieldName
> >>
> >> Hope that helps
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:E98971F5-7FB1-47E9-BDCA-8EE206328488@.microsoft.com...
> >> >I am running a report that is using a stored proc that has been changed.
> >> >I
> >> > made the changes that I am aware of however there seems to be a field
> >> > somewhere in an expression that causes this error ... I cannot figure
> >> > out
> >> > how
> >> > to locate the field!
> >> > It doesnt tell me what expression or what field. The error occurs in
> >> > report
> >> > preview and the text is this:
> >> >
> >> > An error occurred during local report processing.
> >> > an unexpected error occurred in the report processing.
> >> > the expression referenced a non-existing field in the fields
> >> > collection.
> >> >
> >> >
> >>
> >>
> >>
>
>|||what i will do is to re create the report layout from scratch since there is
no problem in stored procedure. it may be a reason that you wrongly copied
some expression from another report and it is difficult to point to the
expression where it occurs. There is no debugger for the expression code. if
you assemblie you can debug easily to find the syntax error in the code.
~Bava
"MJT" wrote:
> this may be a bit of an interesting dilemma. I will let you know the
> outcome. I am combining 2 reports into one report. Each report seems to run
> successfully one its own. When I put them together and run them ... I get
> that error. The interesting part is that the 2 reports use the same stored
> procedure however they return different result sets depending on the
> parameter values sent. I have 2 separate datasets set up and 2 separate
> tables set up to render the data so in theory this should work from what I
> have read. I am wondering if I am running into an issue where it somehow
> *thinks* a field is missing because it exists in one result set and not the
> other? I am reworking this solution again ... step by step to see at what
> point it fails. As of now I have each separate report working and I am about
> to combiine them again. I will post again. Thanks for your help ... that is
> still a possibility (the stored procs) and I will keep that in mind ... I
> have to involve another group for that so I need to verifiy the point of
> failure first.
> "Chris" wrote:
> > Yes, Check the syntax in the stored proc, if you want to post it, I'll look
> > at it as well.
> > I ran in to this same problem last week. I altered a stored proc and all of
> > a sudden I was getting this error, found that I had put an
> > ISNULL(tbl.FieldName, 0) and it came back to SQL Reports as an unknown
> > field. As soon as I made it ISNULL(tb.FIeldName,0) as 'CurrentValue' the
> > field was no longer a problem in SQL Reports.
> >
> >
> >
> >
> > "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > news:7249105D-B46E-40BB-B468-DCC97C47A767@.microsoft.com...
> > > are you saying that I should check the stored procedure? I am not using
> > > sql.
> > > I have verified that all the fields are coming back from the stored
> > > procedure and exist in the fields collection so this is very strange.
> > >
> > > "Chris" wrote:
> > >
> > >> Double check your SQL. If you have something that you have wrapped with
> > >> a
> > >> function (ie. LTRIM(tbl.Field), this will not have a fields name. You
> > >> would need to do: LTRIM(tbl.Field) as FieldName
> > >>
> > >> Hope that helps
> > >>
> > >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > >> news:E98971F5-7FB1-47E9-BDCA-8EE206328488@.microsoft.com...
> > >> >I am running a report that is using a stored proc that has been changed.
> > >> >I
> > >> > made the changes that I am aware of however there seems to be a field
> > >> > somewhere in an expression that causes this error ... I cannot figure
> > >> > out
> > >> > how
> > >> > to locate the field!
> > >> > It doesnt tell me what expression or what field. The error occurs in
> > >> > report
> > >> > preview and the text is this:
> > >> >
> > >> > An error occurred during local report processing.
> > >> > an unexpected error occurred in the report processing.
> > >> > the expression referenced a non-existing field in the fields
> > >> > collection.
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >|||thanks for your suggestion ... I would re-create from scratch but the report
is rather complicated so it would be best to try it this way first before
trying to reinvent the wheel. I still suspect it has something to do with
the fact that I am calling the same stored proc twice somehow although like I
said ... the result sets are feeding two different data regions.
"Bava Mani" wrote:
> what i will do is to re create the report layout from scratch since there is
> no problem in stored procedure. it may be a reason that you wrongly copied
> some expression from another report and it is difficult to point to the
> expression where it occurs. There is no debugger for the expression code. if
> you assemblie you can debug easily to find the syntax error in the code.
> ~Bava
> "MJT" wrote:
> > this may be a bit of an interesting dilemma. I will let you know the
> > outcome. I am combining 2 reports into one report. Each report seems to run
> > successfully one its own. When I put them together and run them ... I get
> > that error. The interesting part is that the 2 reports use the same stored
> > procedure however they return different result sets depending on the
> > parameter values sent. I have 2 separate datasets set up and 2 separate
> > tables set up to render the data so in theory this should work from what I
> > have read. I am wondering if I am running into an issue where it somehow
> > *thinks* a field is missing because it exists in one result set and not the
> > other? I am reworking this solution again ... step by step to see at what
> > point it fails. As of now I have each separate report working and I am about
> > to combiine them again. I will post again. Thanks for your help ... that is
> > still a possibility (the stored procs) and I will keep that in mind ... I
> > have to involve another group for that so I need to verifiy the point of
> > failure first.
> >
> > "Chris" wrote:
> >
> > > Yes, Check the syntax in the stored proc, if you want to post it, I'll look
> > > at it as well.
> > > I ran in to this same problem last week. I altered a stored proc and all of
> > > a sudden I was getting this error, found that I had put an
> > > ISNULL(tbl.FieldName, 0) and it came back to SQL Reports as an unknown
> > > field. As soon as I made it ISNULL(tb.FIeldName,0) as 'CurrentValue' the
> > > field was no longer a problem in SQL Reports.
> > >
> > >
> > >
> > >
> > > "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > > news:7249105D-B46E-40BB-B468-DCC97C47A767@.microsoft.com...
> > > > are you saying that I should check the stored procedure? I am not using
> > > > sql.
> > > > I have verified that all the fields are coming back from the stored
> > > > procedure and exist in the fields collection so this is very strange.
> > > >
> > > > "Chris" wrote:
> > > >
> > > >> Double check your SQL. If you have something that you have wrapped with
> > > >> a
> > > >> function (ie. LTRIM(tbl.Field), this will not have a fields name. You
> > > >> would need to do: LTRIM(tbl.Field) as FieldName
> > > >>
> > > >> Hope that helps
> > > >>
> > > >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > > >> news:E98971F5-7FB1-47E9-BDCA-8EE206328488@.microsoft.com...
> > > >> >I am running a report that is using a stored proc that has been changed.
> > > >> >I
> > > >> > made the changes that I am aware of however there seems to be a field
> > > >> > somewhere in an expression that causes this error ... I cannot figure
> > > >> > out
> > > >> > how
> > > >> > to locate the field!
> > > >> > It doesnt tell me what expression or what field. The error occurs in
> > > >> > report
> > > >> > preview and the text is this:
> > > >> >
> > > >> > An error occurred during local report processing.
> > > >> > an unexpected error occurred in the report processing.
> > > >> > the expression referenced a non-existing field in the fields
> > > >> > collection.
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >|||I have tried running the parts of the report separately and they work ... I
put them together and they dont ... I get the "expression referenced a
non-existing field in the fields collection" error message and report wont
run. Since both run separately I am not inclined to think it is stored proc.
Any more ideas?
"Chris" wrote:
> Yes, Check the syntax in the stored proc, if you want to post it, I'll look
> at it as well.
> I ran in to this same problem last week. I altered a stored proc and all of
> a sudden I was getting this error, found that I had put an
> ISNULL(tbl.FieldName, 0) and it came back to SQL Reports as an unknown
> field. As soon as I made it ISNULL(tb.FIeldName,0) as 'CurrentValue' the
> field was no longer a problem in SQL Reports.
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:7249105D-B46E-40BB-B468-DCC97C47A767@.microsoft.com...
> > are you saying that I should check the stored procedure? I am not using
> > sql.
> > I have verified that all the fields are coming back from the stored
> > procedure and exist in the fields collection so this is very strange.
> >
> > "Chris" wrote:
> >
> >> Double check your SQL. If you have something that you have wrapped with
> >> a
> >> function (ie. LTRIM(tbl.Field), this will not have a fields name. You
> >> would need to do: LTRIM(tbl.Field) as FieldName
> >>
> >> Hope that helps
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:E98971F5-7FB1-47E9-BDCA-8EE206328488@.microsoft.com...
> >> >I am running a report that is using a stored proc that has been changed.
> >> >I
> >> > made the changes that I am aware of however there seems to be a field
> >> > somewhere in an expression that causes this error ... I cannot figure
> >> > out
> >> > how
> >> > to locate the field!
> >> > It doesnt tell me what expression or what field. The error occurs in
> >> > report
> >> > preview and the text is this:
> >> >
> >> > An error occurred during local report processing.
> >> > an unexpected error occurred in the report processing.
> >> > the expression referenced a non-existing field in the fields
> >> > collection.
> >> >
> >> >
> >>
> >>
> >>
>
>

Please tell me why @@Error is not picking up the error

in this procedure the Gender column is a bit datatype. as you can see i'am adding a string.

when i exec proc my error message "print 'error'" is not showing. please help.

you can email at cbmorton!at!gmail.com

CREATE PROCEDURE [dbo].[sp_newuserregistration] AS
BEGIN TRAN
INSERT INTO UserLoginInfo (UserName,[Password]) values ('?','?')
SELECT @.@.IDENTITY AS 'Identity'
DECLARE @.UserID AS INT
SET @.UserID = @.@.Identity
INSERT INTO UserAccountInfo (UserID, Email) VALUES (@.UserID, '?')
INSERT INTO UserProfileInfo (UserID, FirstName, LastName, Gender, DateOfBirth) VALUES (@.UserID, '?', '?', 'x', null)
IF @.@.ERROR <> 0
BEGIN
PRINT 'error'

ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END

GO

--EXEC sp_newuserregistration

The batch stops executing on the error and you can not catch it. You will need to use try/catch but it is only available in SQL Server 2005.|||? As Andreas mentioned, this is a batch-aborting exception -- for this reason, I usually like to use SET XACT_ABORT in my stored procedures that use transactions. Setting that option to ON will cause the transaction to automatically roll back in case of an exception. You might want to read the following articles for more background on all of this: http://www.sommarskog.se/error-handling-I.html http://www.sommarskog.se/error-handling-II.html -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <codrakon@.discussions.microsoft.com> wrote in message news:2db52d22-6890-435c-92c1-e794a3077677@.discussions.microsoft.com... in this procedure the Gender column is a bit datatype. as you can see i'am adding a string. when i exec proc my error message "print 'error'" is not showing. please help. you can email at cbmorton!at!gmail.com CREATE PROCEDURE [dbo].[sp_newuserregistration] ASBEGIN TRANINSERT INTO UserLoginInfo (UserName,[Password]) values ('?','?')SELECT @.@.IDENTITY AS 'Identity'DECLARE @.UserID AS INTSET @.UserID = @.@.Identity INSERT INTO UserAccountInfo (UserID, Email) VALUES (@.UserID, '?')INSERT INTO UserProfileInfo (UserID, FirstName, LastName, Gender, DateOfBirth) VALUES (@.UserID, '?', '?', 'x', null)IF @.@.ERROR <> 0BEGINPRINT 'error' ROLLBACK TRANENDELSEBEGINCOMMIT TRANEND GO --EXEC sp_newuserregistration

PLEASE PLEASE HELP - How can I get a return value from a SQL Stored Proc is ASP.NET?

Hi. I'm sorry to bother all of you, but I have spent two days looking
at code samples all over the internet, and I can not get a single one
of them to work for me. I am simply trying to get a value returned to
the ASP from a stored procedure. The error I am getting is: Item can
not be found in the collection corresponding to the requested name or
ordinal.

Here is my Stored Procedure code.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go
ALTER PROCEDURE [dbo].[sprocRetUPC]
@.sUPC varchar(50),
@.sRetUPC varchar(50) OUTPUT

AS

BEGIN
SET NOCOUNT ON;
SET @.sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =
@.sUPC)
RETURN @.sRetUPC

END

Here is my ASP.NET code.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load

Dim oConnSQL As ADODB.Connection

oConnSQL = New ADODB.Connection
oConnSQL.ConnectionString = "DSN=BarcodeSQL"
oConnSQL.Open()

Dim oSproc As ADODB.Command
oSproc = New ADODB.Command
oSproc.ActiveConnection = oConnSQL
oSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
oSproc.CommandText = "sprocRetUPC"

Dim oParam1
Dim oParam2
oParam1 = oSproc.CreateParameter("sRetUPC",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 50)
oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")

Dim res
res = oSproc("sRetUPC")

Response.Write(res.ToString())

End Sub

If I put the line -
oSproc.Execute()

above the "Dim res" line, I end up with the following error:
Procedure or function 'sprocRetUPC' expects parameter '@.sUPC', which
was not supplied. I thought that oParam2 was the parameter. I was also
under the assumption that the return parameter has to be declared
first. What am I doing wrong here?jbonifacejr wrote:

Quote:

Originally Posted by

>
Hi. I'm sorry to bother all of you, but I have spent two days looking
at code samples all over the internet, and I can not get a single one
of them to work for me. I am simply trying to get a value returned to
the ASP from a stored procedure. The error I am getting is: Item can
not be found in the collection corresponding to the requested name or
ordinal.
>
Here is my Stored Procedure code.
>
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Go
ALTER PROCEDURE [dbo].[sprocRetUPC]
@.sUPC varchar(50),
@.sRetUPC varchar(50) OUTPUT
>
AS
>
BEGIN
SET NOCOUNT ON;
SET @.sRetUPC = (SELECT bcdDVD_Title FROM tblBarcodes WHERE bcdUPC =
@.sUPC)
RETURN @.sRetUPC
>
END
>
Here is my ASP.NET code.
>
Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
>
Dim oConnSQL As ADODB.Connection
>
oConnSQL = New ADODB.Connection
oConnSQL.ConnectionString = "DSN=BarcodeSQL"
oConnSQL.Open()
>
Dim oSproc As ADODB.Command
oSproc = New ADODB.Command
oSproc.ActiveConnection = oConnSQL
oSproc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
oSproc.CommandText = "sprocRetUPC"
>
Dim oParam1
Dim oParam2
oParam1 = oSproc.CreateParameter("sRetUPC",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamOutput, 50)
oParam2 = oSproc.CreateParameter("sUPC", ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "043396005396")
>
Dim res
res = oSproc("sRetUPC")
>
Response.Write(res.ToString())
>
End Sub
>
If I put the line -
oSproc.Execute()
>
above the "Dim res" line, I end up with the following error:
Procedure or function 'sprocRetUPC' expects parameter '@.sUPC', which
was not supplied. I thought that oParam2 was the parameter. I was also
under the assumption that the return parameter has to be declared
first. What am I doing wrong here?

Just a few pointers here:
- creating a parameter will just create a parameter. To use it, you need
to add it to the command object using oSProc.Parameters.Append
- in a stored procedure you can only use the RETURN keyword to return an
integer, so @.sRetUPC is out of the question
- if you want to use the value of the output parameter, then you should
access it through the Parameters collection of the Command object. The
syntax you are currently using refers to the resultset, but the stored
procedure does not have one

HTH,
Gert-Jan|||Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...

I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter

Quote:

Originally Posted by

>From there, I appended the parameters in the ASP code...like this

oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.

Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()

This is not working. Do you know how I can get access to the value of
the parameter that is returned?

Quote:

Originally Posted by

Just a few pointers here:
- creating a parameter will just create a parameter. To use it, you need
to add it to the command object using oSProc.Parameters.Append
- in a stored procedure you can only use the RETURN keyword to return an
integer, so @.sRetUPC is out of the question
- if you want to use the value of the output parameter, then you should
access it through the Parameters collection of the Command object. The
syntax you are currently using refers to the resultset, but the stored
procedure does not have one
>
HTH,
Gert-Jan

|||jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
>
I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter
>

Quote:

Originally Posted by

>>From there, I appended the parameters in the ASP code...like this

oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
>
Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()
>
This is not working. Do you know how I can get access to the value of
the parameter that is returned?

Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?

Since I don't even know how your code looks like right now, just two
notes:

1) Use parameter names with leading @.. The underlying provider may
prefer that.

2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)

--
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|||If you look at the top post you will see where I put the code I am
using. I also tried to let people know what happened when I tried their
suggestions. But, thanks for the advice...and I'll look at those SQL
Books online.

Jan

Erland Sommarskog wrote:

Quote:

Originally Posted by

jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...

I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter

Quote:

Originally Posted by

>From there, I appended the parameters in the ASP code...like this

oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.

Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()

This is not working. Do you know how I can get access to the value of
the parameter that is returned?

>
Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?
>
Since I don't even know how your code looks like right now, just two
notes:
>
1) Use parameter names with leading @.. The underlying provider may
prefer that.
>
2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)
>
>
--
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

|||Why are you using stored proc when you can use a function(s)? I always
thought the output parameter was a bit of a hack and clumsy to use.

jbonifacejr wrote:

Quote:

Originally Posted by

If you look at the top post you will see where I put the code I am
using. I also tried to let people know what happened when I tried their
suggestions. But, thanks for the advice...and I'll look at those SQL
Books online.
>
Jan
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

Thank you for your help. Any chance you have a moment to help just a
little more? Here is what I did, but I still can't access the value
output by the stored proc...
>
I removed the Return @.sRetUPC line. I am guessing that I can rely on
the set @.sRetUPC line to set the value of the output parameter
>
>>From there, I appended the parameters in the ASP code...like this
oSproc.Parameters.Append(oParam2)
oSproc.Parameters.Append(oParam1)
--originally I tried to do Param1 then Param2, but I got an error
about the parameter
--type being an output, so I just figured I had them in the wrong
order because the
--first parameter in the code was the output one.
>
Then, I added the line oSproc.Execute()
After that is:
Dim res
res = oSproc.Parameters.Item("sRetUPC").Value.toString()
>
This is not working. Do you know how I can get access to the value of
the parameter that is returned?

Never say "not working" in newsgroup post with explaining what it
means. Do you get an unexpected result? An error message? Something
else?

Since I don't even know how your code looks like right now, just two
notes:

1) Use parameter names with leading @.. The underlying provider may
prefer that.

2) Use adParamInputOutput for the output value. T-SQL does not have any
true output-only parameters. (Save the return value, but there is a
separate enum value for return values as I recall.)

--
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

|||jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

If you look at the top post you will see where I put the code I am
using.

Since then you changed the code according to Gert-Jan's advice, and we
don't know what it looked after that.

Basically, if you only say "not working" without specifying why, and
don't show us the code, don't expect that much help. But that's your call.

--
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|||Thanks Erland...I see where I screwed up. I thought I had explained
what was wrong, but I did that in a different thread in another forum.

Anyway, I got this working using classic ASP and ADODB. Now I am going
to try to get it working over ASP.NET and ADO.NET. Wish me luck.

So far, eveerything works except that I am constantly being told that
the stored procedure expects a parameter that was not supplied.
However, The same two parameters are created and added to the
Parameters of the command object.

I'll continue to work on it and see if I can get it to work. Looks like
I need a datareader or some other object. I found a great KB article
that basically shows me everythig I am doing (right and wrong)...

http://support.microsoft.com/kb/306574
Erland Sommarskog wrote:

Quote:

Originally Posted by

jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

If you look at the top post you will see where I put the code I am
using.

>
Since then you changed the code according to Gert-Jan's advice, and we
don't know what it looked after that.
>
Basically, if you only say "not working" without specifying why, and
don't show us the code, don't expect that much help. But that's your call.
>
>
>
--
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

|||jbonifacejr (jbonifacejr@.hotmail.com) writes:

Quote:

Originally Posted by

Thanks Erland...I see where I screwed up. I thought I had explained
what was wrong, but I did that in a different thread in another forum.

Posting the same question independently to two forums is not a nice
thing to. This means that people can waste time on answering your post,
when it has already been answered elsewhere.

Quote:

Originally Posted by

Anyway, I got this working using classic ASP and ADODB. Now I am going
to try to get it working over ASP.NET and ADO.NET. Wish me luck.
>
So far, eveerything works except that I am constantly being told that
the stored procedure expects a parameter that was not supplied.
However, The same two parameters are created and added to the
Parameters of the command object.

Again, without seeing your code it's hard to tell. There is a difference
between ADO and SqlClient though: with ADO, the parameter names are
just local to the application, so if you misspell a parameter name,
you may get away with it. Not so with SqlClient.

Quote:

Originally Posted by

I'll continue to work on it and see if I can get it to work. Looks like
I need a datareader or some other object.

Since your procedure has an output parameter, but no result set, the most
conventient method to use is ExecuteNonQuery, in which case you only need
the Command object.
--
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

Please make the world all right again.

I have reported some SQL execution plan whackyness on this newsgroup
( http://shrinkster.com/984 ) where a stored proc ran very slowly when
called as a stored procedure. However, if I just pasted the sproc
source code into Query Analyzer, it ran like a champ on the same set of
data. I finally zeroed in on a particular query that was causing this
and, again, it was fast in Query Analyzer and a dog inside the stored
procedure. The 2 methods would also generate a completely different plan.
Anyway, my co-worker found a solution, which is ugly but works great.
He simply placed the query text into a varchar variable and ran it as
Dynamic SQL using EXEC inside the stored procedure. All of a sudden,
the execution plans were identical and the speed was back to normal.
This begs the question - why in the world is this happening? Running
SQL from an EXEC should slow things down, not speed them up. Afaik, it
goes against everything I've ever learned about databases. Is the
optimizer flawed in SQL Server 2000 SP3? Should we go to SP4?
Thanks.
Does your code query a remote server?
PRB: Distributed Queries That Are Wrapped in a Stored Procedure with Input
Parameters May Experience Performance Degradation
http://support.microsoft.com/default...b;en-us;320208
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:eTXPZfV6FHA.2192@.TK2MSFTNGP14.phx.gbl...
>I have reported some SQL execution plan whackyness on this newsgroup
> ( http://shrinkster.com/984 ) where a stored proc ran very slowly when
> called as a stored procedure. However, if I just pasted the sproc source
> code into Query Analyzer, it ran like a champ on the same set of data. I
> finally zeroed in on a particular query that was causing this and, again,
> it was fast in Query Analyzer and a dog inside the stored procedure. The
> 2 methods would also generate a completely different plan.
> Anyway, my co-worker found a solution, which is ugly but works great. He
> simply placed the query text into a varchar variable and ran it as Dynamic
> SQL using EXEC inside the stored procedure. All of a sudden, the
> execution plans were identical and the speed was back to normal.
> This begs the question - why in the world is this happening? Running SQL
> from an EXEC should slow things down, not speed them up. Afaik, it goes
> against everything I've ever learned about databases. Is the optimizer
> flawed in SQL Server 2000 SP3? Should we go to SP4?
> Thanks.
>
|||> This begs the question - why in the world is this happening? Running SQL
> from an EXEC should slow things down, not speed them up. Afaik, it goes
> against everything I've ever learned about databases. Is the optimizer
> flawed in SQL Server 2000 SP3? Should we go to SP4?
Tibor probably identified the culprit - do you take him up on the
suggestion?
|||Geoff N. Hiten wrote:
> Does your code query a remote server?
> PRB: Distributed Queries That Are Wrapped in a Stored Procedure with Input
> Parameters May Experience Performance Degradation
> http://support.microsoft.com/default...b;en-us;320208
>
Nope. Everything is on the same server.
|||Scott Morris wrote:
>
> Tibor probably identified the culprit - do you take him up on the
> suggestion?
Yes, I tried that but it didn't help. There is an article on this topic
which is very informative, but it didn't apply to my situation.
http://blogs.msdn.com/khen1234/archi...02/424228.aspx
|||> Yes, I tried that but it didn't help.
What did you try?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Rizzo" <none@.none.com> wrote in message news:%23FAOPQW6FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Scott Morris wrote:
> Yes, I tried that but it didn't help. There is an article on this topic
> which is very informative, but it didn't apply to my situation.
> http://blogs.msdn.com/khen1234/archi...02/424228.aspx
|||Tibor Karaszi wrote:
> What did you try?
I tried changing variables to parameters and also variables to hardwired
values.
|||Are you saying that when you have the code in a stored procedure, the code is slow compared to when
not? Even if the procedure doesn't have any parameters and you hard-wire the search arguments inside
the procedure code? So basically, you have your TSQL code which is fast, add CREATE PROC on top and
when you execute that proc it is slow? And it doesn't matter if you create the proc or executing the
proc using WITH RECOMPILE? If so, I suggest you open a case with MS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Rizzo" <none@.none.com> wrote in message news:eCBzAlX6FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
> I tried changing variables to parameters and also variables to hardwired values.
|||Tibor Karaszi wrote:
> Are you saying that when you have the code in a stored procedure, the
> code is slow compared to when not? Even if the procedure doesn't have
> any parameters and you hard-wire the search arguments inside the
> procedure code? So basically, you have your TSQL code which is fast, add
> CREATE PROC on top and when you execute that proc it is slow? And it
> doesn't matter if you create the proc or executing the proc using WITH
> RECOMPILE? If so, I suggest you open a case with MS.
Yes, that is precisely what I am saying. I am just a consultant here
and don't have the power to open a case, but I'll see who can take care
of this. Anyway, the problem was solved by executing the query dynamically.

Please make the world all right again.

I have reported some SQL execution plan whackyness on this newsgroup
( http://shrinkster.com/984 ) where a stored proc ran very slowly when
called as a stored procedure. However, if I just pasted the sproc
source code into Query Analyzer, it ran like a champ on the same set of
data. I finally zeroed in on a particular query that was causing this
and, again, it was fast in Query Analyzer and a dog inside the stored
procedure. The 2 methods would also generate a completely different plan.
Anyway, my co-worker found a solution, which is ugly but works great.
He simply placed the query text into a varchar variable and ran it as
Dynamic SQL using EXEC inside the stored procedure. All of a sudden,
the execution plans were identical and the speed was back to normal.
This begs the question - why in the world is this happening? Running
SQL from an EXEC should slow things down, not speed them up. Afaik, it
goes against everything I've ever learned about databases. Is the
optimizer flawed in SQL Server 2000 SP3? Should we go to SP4?
Thanks.Does your code query a remote server?
PRB: Distributed Queries That Are Wrapped in a Stored Procedure with Input
Parameters May Experience Performance Degradation
http://support.microsoft.com/default.aspx?scid=kb;en-us;320208
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:eTXPZfV6FHA.2192@.TK2MSFTNGP14.phx.gbl...
>I have reported some SQL execution plan whackyness on this newsgroup
> ( http://shrinkster.com/984 ) where a stored proc ran very slowly when
> called as a stored procedure. However, if I just pasted the sproc source
> code into Query Analyzer, it ran like a champ on the same set of data. I
> finally zeroed in on a particular query that was causing this and, again,
> it was fast in Query Analyzer and a dog inside the stored procedure. The
> 2 methods would also generate a completely different plan.
> Anyway, my co-worker found a solution, which is ugly but works great. He
> simply placed the query text into a varchar variable and ran it as Dynamic
> SQL using EXEC inside the stored procedure. All of a sudden, the
> execution plans were identical and the speed was back to normal.
> This begs the question - why in the world is this happening? Running SQL
> from an EXEC should slow things down, not speed them up. Afaik, it goes
> against everything I've ever learned about databases. Is the optimizer
> flawed in SQL Server 2000 SP3? Should we go to SP4?
> Thanks.
>|||> This begs the question - why in the world is this happening? Running SQL
> from an EXEC should slow things down, not speed them up. Afaik, it goes
> against everything I've ever learned about databases. Is the optimizer
> flawed in SQL Server 2000 SP3? Should we go to SP4?
Tibor probably identified the culprit - do you take him up on the
suggestion?|||Geoff N. Hiten wrote:
> Does your code query a remote server?
> PRB: Distributed Queries That Are Wrapped in a Stored Procedure with Input
> Parameters May Experience Performance Degradation
> http://support.microsoft.com/default.aspx?scid=kb;en-us;320208
>
Nope. Everything is on the same server.|||Scott Morris wrote:
>>This begs the question - why in the world is this happening? Running SQL
>>from an EXEC should slow things down, not speed them up. Afaik, it goes
>>against everything I've ever learned about databases. Is the optimizer
>>flawed in SQL Server 2000 SP3? Should we go to SP4?
>
> Tibor probably identified the culprit - do you take him up on the
> suggestion?
Yes, I tried that but it didn't help. There is an article on this topic
which is very informative, but it didn't apply to my situation.
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx|||> Yes, I tried that but it didn't help.
What did you try?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Rizzo" <none@.none.com> wrote in message news:%23FAOPQW6FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Scott Morris wrote:
>>This begs the question - why in the world is this happening? Running SQL
>>from an EXEC should slow things down, not speed them up. Afaik, it goes
>>against everything I've ever learned about databases. Is the optimizer
>>flawed in SQL Server 2000 SP3? Should we go to SP4?
>>
>> Tibor probably identified the culprit - do you take him up on the
>> suggestion?
> Yes, I tried that but it didn't help. There is an article on this topic
> which is very informative, but it didn't apply to my situation.
> http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx|||Tibor Karaszi wrote:
>> Yes, I tried that but it didn't help.
> What did you try?
I tried changing variables to parameters and also variables to hardwired
values.|||Are you saying that when you have the code in a stored procedure, the code is slow compared to when
not? Even if the procedure doesn't have any parameters and you hard-wire the search arguments inside
the procedure code? So basically, you have your TSQL code which is fast, add CREATE PROC on top and
when you execute that proc it is slow? And it doesn't matter if you create the proc or executing the
proc using WITH RECOMPILE? If so, I suggest you open a case with MS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Rizzo" <none@.none.com> wrote in message news:eCBzAlX6FHA.1184@.TK2MSFTNGP12.phx.gbl...
> Tibor Karaszi wrote:
>> Yes, I tried that but it didn't help.
>> What did you try?
> I tried changing variables to parameters and also variables to hardwired values.|||Tibor Karaszi wrote:
> Are you saying that when you have the code in a stored procedure, the
> code is slow compared to when not? Even if the procedure doesn't have
> any parameters and you hard-wire the search arguments inside the
> procedure code? So basically, you have your TSQL code which is fast, add
> CREATE PROC on top and when you execute that proc it is slow? And it
> doesn't matter if you create the proc or executing the proc using WITH
> RECOMPILE? If so, I suggest you open a case with MS.
Yes, that is precisely what I am saying. I am just a consultant here
and don't have the power to open a case, but I'll see who can take care
of this. Anyway, the problem was solved by executing the query dynamically.

Please make the world all right again.

I have reported some SQL execution plan whackyness on this newsgroup
( http://shrinkster.com/984 ) where a stored proc ran very slowly when
called as a stored procedure. However, if I just pasted the sproc
source code into Query Analyzer, it ran like a champ on the same set of
data. I finally zeroed in on a particular query that was causing this
and, again, it was fast in Query Analyzer and a dog inside the stored
procedure. The 2 methods would also generate a completely different plan.
Anyway, my co-worker found a solution, which is ugly but works great.
He simply placed the query text into a varchar variable and ran it as
Dynamic SQL using EXEC inside the stored procedure. All of a sudden,
the execution plans were identical and the speed was back to normal.
This begs the question - why in the world is this happening? Running
SQL from an EXEC should slow things down, not speed them up. Afaik, it
goes against everything I've ever learned about databases. Is the
optimizer flawed in SQL Server 2000 SP3? Should we go to SP4?
Thanks.Does your code query a remote server?
PRB: Distributed Queries That Are Wrapped in a Stored Procedure with Input
Parameters May Experience Performance Degradation
http://support.microsoft.com/defaul...kb;en-us;320208
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:eTXPZfV6FHA.2192@.TK2MSFTNGP14.phx.gbl...
>I have reported some SQL execution plan whackyness on this newsgroup
> ( http://shrinkster.com/984 ) where a stored proc ran very slowly when
> called as a stored procedure. However, if I just pasted the sproc source
> code into Query Analyzer, it ran like a champ on the same set of data. I
> finally zeroed in on a particular query that was causing this and, again,
> it was fast in Query Analyzer and a dog inside the stored procedure. The
> 2 methods would also generate a completely different plan.
> Anyway, my co-worker found a solution, which is ugly but works great. He
> simply placed the query text into a varchar variable and ran it as Dynamic
> SQL using EXEC inside the stored procedure. All of a sudden, the
> execution plans were identical and the speed was back to normal.
> This begs the question - why in the world is this happening? Running SQL
> from an EXEC should slow things down, not speed them up. Afaik, it goes
> against everything I've ever learned about databases. Is the optimizer
> flawed in SQL Server 2000 SP3? Should we go to SP4?
> Thanks.
>|||> This begs the question - why in the world is this happening? Running SQL
> from an EXEC should slow things down, not speed them up. Afaik, it goes
> against everything I've ever learned about databases. Is the optimizer
> flawed in SQL Server 2000 SP3? Should we go to SP4?
Tibor probably identified the culprit - do you take him up on the
suggestion?|||Geoff N. Hiten wrote:
> Does your code query a remote server?
> PRB: Distributed Queries That Are Wrapped in a Stored Procedure with Input
> Parameters May Experience Performance Degradation
> http://support.microsoft.com/defaul...kb;en-us;320208
>
Nope. Everything is on the same server.|||Scott Morris wrote:
>
> Tibor probably identified the culprit - do you take him up on the
> suggestion?
Yes, I tried that but it didn't help. There is an article on this topic
which is very informative, but it didn't apply to my situation.
http://blogs.msdn.com/khen1234/arch.../02/424228.aspx|||> Yes, I tried that but it didn't help.
What did you try?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Rizzo" <none@.none.com> wrote in message news:%23FAOPQW6FHA.3804@.TK2MSFTNGP14.phx.gbl.
.
> Scott Morris wrote:
> Yes, I tried that but it didn't help. There is an article on this topic
> which is very informative, but it didn't apply to my situation.
> http://blogs.msdn.com/khen1234/arch.../02/424228.aspx|||Tibor Karaszi wrote:
> What did you try?
I tried changing variables to parameters and also variables to hardwired
values.|||Are you saying that when you have the code in a stored procedure, the code i
s slow compared to when
not? Even if the procedure doesn't have any parameters and you hard-wire the
search arguments inside
the procedure code? So basically, you have your TSQL code which is fast, add
CREATE PROC on top and
when you execute that proc it is slow? And it doesn't matter if you create t
he proc or executing the
proc using WITH RECOMPILE? If so, I suggest you open a case with MS.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Rizzo" <none@.none.com> wrote in message news:eCBzAlX6FHA.1184@.TK2MSFTNGP12.phx.gbl...
[vbcol=seagreen]
> Tibor Karaszi wrote:
> I tried changing variables to parameters and also variables to hardwired values.[/
vbcol]|||Tibor Karaszi wrote:
> Are you saying that when you have the code in a stored procedure, the
> code is slow compared to when not? Even if the procedure doesn't have
> any parameters and you hard-wire the search arguments inside the
> procedure code? So basically, you have your TSQL code which is fast, add
> CREATE PROC on top and when you execute that proc it is slow? And it
> doesn't matter if you create the proc or executing the proc using WITH
> RECOMPILE? If so, I suggest you open a case with MS.
Yes, that is precisely what I am saying. I am just a consultant here
and don't have the power to open a case, but I'll see who can take care
of this. Anyway, the problem was solved by executing the query dynamically.

Please help, Dynamic search stored proc.

Please help, I am trying to write a dynamic search stored procedure using three fields.
I use the same logic on the front end using VB to SQL server which works fine, but never tried a stored procedure with the logic.
Can you please help me, construct a stored procedure.
User can choose any of the three(progno, projno, contractno) fields as a where condition.

I ma using asp.net as front end with sql server backend.

CREATE PROCEDURE dbo.USP_Searchrecords
(@.ProgNO nvarchar(50),
 @.ProjNOnvarchar(50) ,
 @.ContractNOnvarchar(50))
AS
DECLARE @.myselect nvarchar(2000)
DECLARE @.psql nvarchar(2000)
DECLARE @.strsql nvarchar(2000)
SET NOCOUNT ON
@.psql = "SELECT * FROM Mytable"
IF @.ProgNO <> '' then
strsql = WHERE ProgNO = @.ProgNO
end if
If @.ProjNO <> '' then
if strsql <> '' then
strsql = strsql & " and ProjNO =@.ProjNO
ELSE
strsql = wHERE ProjNO =@.ProjNO
END IF
END IF
If @.ContractNO <> '' then
if strsql <> '' then
strsql = strsql & " and ContractNO =@.ContractNO
ELSE
strsql = wHERE ContractNO =@.ContractNO
END IF
END IF
@.myselect = @.psql + @.strsql
EXEC(@.myselect)

Please help. Thank you very much.CREATE PROCEDURE dbo.USP_Searchrecords

@.ProgNO nvarchar(50) = null,
@.ProjNOnvarchar(50) = null ,
@.ContractNOnvarchar(50) = null

AS
select * from mytable
where
(
((@.ProgNO is null) or (progNo = @.ProgNO))
AND
((@.ProjNO is null) or (ProjNo = @.ProjNO))
AND
((@.ContractNO is null) or (ContractNo = @.ContractNO))
)|||Do NOT do this.

::CREATE PROCEDURE dbo.USP_Searchrecords

should have a WITH RECOMPILE option here.

Problem is: Depending on the parameters you will get totally different access paths. Without indicating these should be evaluated EVERY TIME, the FIRST access path will be reused, EVEN if it is totally ridiculous given the exact parameters. This will result in awfully slow queries.|||>>should have a WITH RECOMPILE option here

Thona points out a great reason NOT to use Dynamic SQL if you dont have to.

Having an sp precompiled is one of the main performance benefits of using stored procedures - Unless you make changes to the table structure, introduce new indexes, or use Dynamic SQL you should'nt need to recompile your stored procedures.