One column missing from datasets even after refresh?

My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!

I have.

One thing you can do is check for a matching .rdl.data file and blow that away, see if next data pull rewrites the cached data.

Another thing you can probably do is just add it in manually. Rightclick on the *name* of the dataset (underneath the Report DataSets node in the Datasets window) and choose "Add..."

>L<

|||

Thanks for the help! The first method doesn't work. Even if I add a new report from scratch, the available fields are missing that column if I put the sp in the query builder from report build wizard.

Rightclick on the name of the dataset and add the missing column works, but after I deploy the report and go view the report from brower, I got the following message:

An error has occurred during report processing.

Query execution failed for data set 'RegionalLevelByWeek'.

For more information about this error navigate to the report server on the local server machine, or enable remote errors

|||Hmm. I think the first method doesn't work if you have a (usually generated) schema file associated

with the data -- I'm not looking at reports right now, so I could be wrong) -- you might have to "refresh" that file as well.

On the second one, do you have to re-deploy the datasource to the server or something? (remember it's "don't overwrite datasources during deploy" by default) Same is true with default values of params -- given your situation as you put it in this message, that might well be involved.

Now that I look at what you're saying here though... As far as your current thing with the two SP's, you could just put a dummy column into the first result set, couldn't you? The important thing to remember is that there is going to be *one* set of columns associated with that report, never two, so if your proc can return differently based on a param you pretty much have to make them match.

I'm sorry I can't do a worked example right now, it's always *something* find-able though...

>L<

sql

Once again, no records returned....

The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?

------------------------------
CREATE PROCEDURE spUnitsbyUnitID
@.unitid int
AS

SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled

FROM tbl_units as A

INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
INNER JOIN tbl_contacts as D on A.unitid = D.unitid
INNER JOIN tbl_cameras as E on A.unitid = E.unitid

WHERE A.UnitID = @.unitID
GO
-------------------------------Are you sure that there are records in all 5 tables with the same unitid ? Using inner joins, you are limiting the result set to that scenario.

Jeff|||You are missing the OUTPUT keyword which is required for stored Procs returning none Numeric value. As per the previous post you can only use INNER JOIN if both tables are equal and the ANSI SQL OUTER JOIN limit is four because OUTER JOIN has default NULL condition but that may not work for HTTP applications because HTTP aplications are Stateless. Check out the MSDN link below.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_3q7n.asp Hope this helps.

Kind regards,
Gift Peddie|||Found the problem, 1 of the tables didn't have a test value entered in for the unitID. Thanks for the help!sql

on EXEC a sp

Please provide more information for others to better understand you problem.
Do you want to send the email when the procedure returns certain result? How
often is the procedure being run? Where exactly is the information regarding
sending the email?
AnithAnith,
Yes, I would like the sp to send an email when the results = TIME. The sp
can be executed using the Agent Job and should be executed daily at 00:01.
Thank you.
"Anith Sen" <anith@.bizdatasolutions.com> escribi en el mensaje
news:edX7krOWGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Please provide more information for others to better understand you
> problem. Do you want to send the email when the procedure returns certain
> result? How often is the procedure being run? Where exactly is the
> information regarding sending the email?
> --
> Anith
>

OLE DB error

I have a stored procedure that consists 4 set of statements (delete rows and
then insert rows from excel files with openrowset). It returns error when
execute the 4th insert statement. The error message is:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file ''. It is already opened exclusively by another user, or you
need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
Its strange, I think, that there is no file in the error message... BTW,
the stored procedure can be executed successfully if any one insert
statement is commented. Does openrowset or MS Jet database engine has any
limitation? Could anyone please tell me how can I solve this issue?
Any help would be appreciated.
P.S. I'm running SQL Server 2000
SChi Squirrel,
Could you please be so kind to post the aforementioned stored procedure here
?
"Squirrel" wrote:

> I have a stored procedure that consists 4 set of statements (delete rows a
nd
> then insert rows from excel files with openrowset). It returns error when
> execute the 4th insert statement. The error message is:
>
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> [OLE/DB provider returned message: The Microsoft Jet database engine canno
t
> open the file ''. It is already opened exclusively by another user, or yo
u
> need permission to view its data.]
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: ].
>
> It?|s strange, I think, that there is no file in the error message... B
TW,
> the stored procedure can be executed successfully if any one insert
> statement is commented. Does openrowset or MS Jet database engine has any
> limitation? Could anyone please tell me how can I solve this issue?
>
> Any help would be appreciated.
>
> P.S. I'm running SQL Server 2000
>
> SC
>
>|||here. thanks.
CREATE PROCEDURE convert_data
@.userid varchar(8)
as
BEGIN TRANSACTION UpdateAll
DELETE table1
INSERT INTO table1
SELECT id, name, cat, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table1.xls', 'select * from [sheet1$]')
DELETE table2
INSERT INTO table2
SELECT id, serial, add_1, add_2, add_3, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table2.xls', 'select * from [sheet1$]')
DELETE table3
INSERT INTO table3
SELECT table1_id, table2_id, serial, type, amount, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table3.xls', 'select * from [sheet1$]')
DELETE table4
INSERT INTO table4
SELECT code, num, description, getdate(), @.userid
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0; HDR=YES;
IMEX=1;Database=d:\data\table4.xls', 'select * from [sheet1$]')
COMMIT TRANSACTION UpdateAll
GO
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:EC55EB71-31E0-4A15-8BAA-5A04C45E0640@.microsoft.com...
> hi Squirrel,
> Could you please be so kind to post the aforementioned stored procedure
> here?
> "Squirrel" wrote:
>

OLE DB Command and Stored Procedure that returns value and/or error

Guys,

could someone please tell me : am I supposed to use the OLE DB

Command in a dataflow to call a stored procedure to return a value? Or

is it just supposed to be used to call a straightforward insert

statement only?

What I am hoping to do:

I have a table with a few columns and one identity column. In a

dataflow I would like to effect an insert of a record to this table and

retrieve the identity value of the inserted record... and I'd like to

store the returned identity in a user variable.

If I AM supposed to be able to do this... then how on earth do I do it?

I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.

In the Advanced Editor any time I try to add an output column (by

clicking on Add Column) I just get an error dialog that says "the

component does not allow adding columns to this input or output)

So, am getting pretty concussed .. banging my head of the wall like this...

So put me out of my misery someone please.... is the OLE DB Command intended for this or not?

Thanks

PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=490010&SiteID=1

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ

OLE DB Command and Stored Procedure that returns value and/or error

Guys,
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
Thanks
PJ

I'm not terribly au fait with the OLE DB Command other than using it for UPDATEs but I do know that you can execute a stored procedure that uses values from the pipeline as parameters.

I suspect that you cannot get return values from the sproc and add that returned value into the pipieline - I stand to be corrected though. I have never tried it.

-Jamie

|||

PJ,

You can probably look at this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=490010&SiteID=1

You can pass back return value into the pipe and use it in a derived column.

|||

PJ,

I do not know how many rows are you planning to insert every time; but trying to capture the identity value at the dataflow sounds a kind of expensive from performance standpoitn. Have you consider to generate your own surrogatekey in a script task instead?

Rafael Salas

|||

Hi there Rafael,

Yeah, I suppose it could get pretty slow if you have a lot of data.. I only have ten small files (10 rows each) to load.. so its not a huge performance hit.

An I already have a DAL written for other applications to call stored procs's and was hoping to use that....

Its a while ago that I wrote the previous post.. and solved the problem in the meantime by writing a script task and calling the stored proc from inside that...

It's an ok approach for me (using small amounts of data) but I remember it seemed like a lot of work to do something I think should be easy to do...

Thanks

PJ