One for the record books

I'm inside EM. I click on a database, then the Stored Procedure list.
Everything's . When I try to scroll down I get a Message Box:
System Error [x]
The memory manager has failed.
[OK] [Cancel]
Obviously, I rebooted, but what the heck would have happened if I hit
[Cancel] ?!?
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James HetfieldApparently the memory manager would no longer have failed :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OSjUAZKtFHA.2008@.TK2MSFTNGP10.phx.gbl...
> I'm inside EM. I click on a database, then the Stored Procedure list.
> Everything's . When I try to scroll down I get a Message Box:
> System Error [x]
> The memory manager has failed.
> [OK] [Cancel]
> Obviously, I rebooted, but what the heck would have happened if I hit
> [Cancel] ?!?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Musha ring dum a doo dum a da!" -- James Hetfield
>|||>> System Error [x]
> Apparently the memory manager would no longer have failed :)
Just try telling that to the memory manager.
Peace & happy computing,
Mike Labosh, MCSD
"Musha ring dum a doo dum a da!" -- James Hetfield|||"We can't fix the actual problem right now, but we're giving you three ways
to close this window."
ML

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

OMG: an Oracle Question :)

Hello...

I have a small Oracle question... *ducks*

In SQL-Server I can return more then one resultset in a single stored Procedure (Get Parents and all child Objects for that/those parent(s))... Is the same possible in Oracle? I think I remember that Oracle cant do that due to the different handling of Selects in SPs?

Thats it...

Also I want to know if there is a reverse Copy of the Book ""SQL Server 2000 for the Oracle DBA"

Oracle allows you to return one or more results via REF CURSOR parameters from functions/procedures. These are more powerful than just returning results. For example, you can pass REF CURSOR parameter from one procedure to another.

Oledb stored procedure rowset binding

I have a strange problem with an OleDB call to a stored procedure that
returns a rowset.
Only the first time I execute the query, after I restart SqlServer, my
program crashes because
the rowset is empty. All next calls (after restarting my program, of
course) run successfully.

The context is:
1) if I do not bind the output rowset, my program doesn't crash
2) If I run the call without the binding and then I run the call with
the binding, it doesn't crash
3) It is not the first query I do in my session
4) the call crashes only if in the SP there are some inserts: it is a
well known problem with OleDB, but in many
other cases I have fixed it setting NOCOUNT to ONgigi (gigisoave@.libero.it) writes:
> I have a strange problem with an OleDB call to a stored procedure that
> returns a rowset.
> Only the first time I execute the query, after I restart SqlServer, my
> program crashes because
> the rowset is empty. All next calls (after restarting my program, of
> course) run successfully.
> The context is:
> 1) if I do not bind the output rowset, my program doesn't crash
> 2) If I run the call without the binding and then I run the call with
> the binding, it doesn't crash
> 3) It is not the first query I do in my session
> 4) the call crashes only if in the SP there are some inserts: it is a
> well known problem with OleDB, but in many
> other cases I have fixed it setting NOCOUNT to ON

Since you don't post any code, it is very difficult to tell. Then again,
even if you had, it would probably still have been difficult to tell.

First, exactly what API are you using? The OLE DB API itself? OLE DB
consumer templates? Something else? Which language do you use?

Next, let me try to see if I understand the scenario:

1) You start your machine.
2) SQL Server starts.
3) You start your application.
4) It runs some queries, and then it comes to this procedure that does
not return a rowset.
5) Your program crashes.
6) You restart your application, and now everything works fine.

Here I assumed that SQL Server ran locally. What happens if you have
SQL Server on a remote machine? Does the app still crash on first
access of SQL Server restart? What if you reboot the client machine
and run again?

Finally, while the start and stop of SQL Server could have something
to do with it, it could also be as trivial as buffer overrun, assuming
that you are programming in C++.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I can answer all your questions:

1) If I connect to a remote sqlserver the situation remains the same
2) I use AOleDB consuner template from VisualC++, but I know the
problem is in call to ICommand::Execute at oledb level.
3) when I restrat the server, the connection and the application are
closed.
4) Your scenario description is good.
Bye and thank you

> Since you don't post any code, it is very difficult to tell. Then again,
> even if you had, it would probably still have been difficult to tell.
> First, exactly what API are you using? The OLE DB API itself? OLE DB
> consumer templates? Something else? Which language do you use?
> Next, let me try to see if I understand the scenario:
> 1) You start your machine.
> 2) SQL Server starts.
> 3) You start your application.
> 4) It runs some queries, and then it comes to this procedure that does
> not return a rowset.
> 5) Your program crashes.
> 6) You restart your application, and now everything works fine.
> Here I assumed that SQL Server ran locally. What happens if you have
> SQL Server on a remote machine? Does the app still crash on first
> access of SQL Server restart? What if you reboot the client machine
> and run again?
> Finally, while the start and stop of SQL Server could have something
> to do with it, it could also be as trivial as buffer overrun, assuming
> that you are programming in C++.

OLEDB error exceeds maximum length of 128

I created a stored proc which is working perfectly even in SQL query analyser even if my query exceeds 128, but when i run it through asp I'm getting the error code 80040E14 which is mentioned in this subject, how can i work around on this?you can start here: http://support.microsoft.com/kb/306518

OLEDB driver omits SP return value

Hi,

I have a problem with some ATL7/OLEDB code. I'm trying to execute stored procedures from my application. I do get a result set back, but I never get a correct return value back. I've been going through MSDN articles as well as other articles on the web, and I just cannot see what I'm doing wrong!

I have this piece of ATL code:
<pre>
// code generated on den 6 juni 2002, 10:56

[
db_command(L"{ ? = CALL dbo.MyProc(?) }")
]
class CMyProc
{
public:
[ db_column(1, status=m_dwIdStatus, length=m_dwIdLength) ] LONG m_Id;
/* and so on - 7 columns total */

[ db_param(1, DBPARAMIO_OUTPUT) ] LONG m_RETURN_VALUE;
[ db_param(2, DBPARAMIO_INPUT) ] LONG m_id;

void GetRowsetProperties(CDBPropSet* pPropSet)
{
pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
}
};

</pre>

When I use this code I do:
<pre>
CSession dbsession;
CDataSource dbsource;
CDBPropSet dbinit(DBPROPSET_DBINIT);
/* fill dbinit with connection parameters */
/* HRESULTs not checked for brevity*/
dbsource.Open(_T("SQLOLEDB.1"), &dbinit);
dbsession.Open(dbsource);

CMyProc proc;
proc.m_id = 1;
proc.Open(dbsession);

TRACE("Return value is %d\n", proc.m_RETURN_VALUE);

/* Take care of result set - which exists and is valid! */
</pre>

The return value is <tt>-858993460</tt> which is <tt>0xcccccccc</tt>. In Visual C++ this value is used as an "uninitialized" stack value during debug. It seems as if ATL never bothers to update my return value parameters! I even made a little experiment with an extra output parameter - same thing there.

I'm using Visual C++ .NET and the SQL 2000 OLEDB provider (SP2). I've also tried this without attributed programming in both Visual C++ .NET and Visual C++ 6.

Does anybody have a clue what is going on??Is the stored procedure using set nocount on ? If not, put it in the stored procedure.

Good luck.|||Thanks for replying!

I tried with and without nocount and had no success.

I tried something else the other day though; I generated a non-attributed CCommand-class using CMultipleResults as template parameter. Then I could get all out parameters by calling GetNextResult() on the command object.

So my question is now; how do I make the attribute-injector generate a CCommand class for me using CMultipleResults?

OLE DB: How to set the property DBPROP_SSCE_TRANSACTION_COMMIT_MODE correctly?

Hello,

because of the fact that the database sdf-file is stored on a cf-card, i want that all commit transactions will be flushed to the file immediately.
So i want to open the database with the above property. But i don't know the type and value i have to place into the variant. The following code fragment gave me the error "DB_S_ERRORSOCCURRED when i try to set the property with IDBInitialize::SetProperties() :

//Initialize property DBPROP_SSCE_TRANSACTION_COMMIT_MODE
dbprop_ssce_session[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;
dbprop_ssce_session[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop_ssce_session[0].vValue.vt = VT_I4;
dbprop_ssce_session[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;

//Initialize property set DBPROPSET_SSCE_SESSION
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION;
dbpropset[1].rgProperties = dbprop_ssce_session;
dbpropset[1].cProperties = 1;
//(there is another property for the path not shown here)

I would appreciate it when someone out there knows the answer and could tell me.

Kind regards,
Andre

Your code looks ok. I use the ATL OLE DB Consumer Templates for this and they also set the colid member to DB_NULLID. This is the only thing I see missing from your code.

Other than this, I would also look at the OLE DB error parameters to get more details.

|||

Every thing is okay except that you are setting the property on IDBInitialize. This is a session related property and so should be set using ISessionProperties.

If the question is answered, please mark it as answered.

Thanks

Raja

|||Thanks Raja,

that was exactly the crux of the matter!

Kind regards,
Andre

OLE DB: How to set the property DBPROP_SSCE_TRANSACTION_COMMIT_MODE correctly?

Hello,

because of the fact that the database sdf-file is stored on a cf-card, i want that all commit transactions will be flushed to the file immediately.
So i want to open the database with the above property. But i don't know the type and value i have to place into the variant. The following code fragment gave me the error "DB_S_ERRORSOCCURRED when i try to set the property with IDBInitialize::SetProperties() :

//Initialize property DBPROP_SSCE_TRANSACTION_COMMIT_MODE
dbprop_ssce_session[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;
dbprop_ssce_session[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop_ssce_session[0].vValue.vt = VT_I4;
dbprop_ssce_session[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;

//Initialize property set DBPROPSET_SSCE_SESSION
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION;
dbpropset[1].rgProperties = dbprop_ssce_session;
dbpropset[1].cProperties = 1;
//(there is another property for the path not shown here)

I would appreciate it when someone out there knows the answer and could tell me.

Kind regards,
Andre

Your code looks ok. I use the ATL OLE DB Consumer Templates for this and they also set the colid member to DB_NULLID. This is the only thing I see missing from your code.

Other than this, I would also look at the OLE DB error parameters to get more details.

|||

Every thing is okay except that you are setting the property on IDBInitialize. This is a session related property and so should be set using ISessionProperties.

If the question is answered, please mark it as answered.

Thanks

Raja

|||Thanks Raja,

that was exactly the crux of the matter!

Kind regards,
Andre

ole db transformation - redirect rows problem

Hi, i have a OLE DB transformation that executes a stored procedure. The stored procedure RAISERROR when an error is caught. I have set the OLE DB transformation to redirect rows. But instead, the task turns red and fails. Did i do something wrong here ?Did you set the error output to redirect rows, or is it still set to the default of failing the component?|||Of course, if the source stored proc is failing, what rows will you be diverting?|||

ah... i compared Lookup transform and OLE DB transform. For lookup transform, you can control "ErrorRowDisposition" for both Command Input and Command Output....

But for OLE DB Transform, you can control "ErrorRowDisposition" for only input, not output !!

So i guess I cannot RAISERROR in the stored proc and let OLE DB transform separate the "bad" records from the "good" records.

Perhaps I need to modify the stored proc to catch all errors - and return an errorCode parameter as output ? And then check this output parameter to see what records are not processed correctly ?

please advice !

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 DataSource w\ stored procedure not populating column metadata

I'm having some issues getting OLE DB Data Sources to work w\ stored procs in SSIS. Here's the situation.

I have an OLE DB Data Source set up to call a stored proc w\ no parameters. The stored procedure loops through a set of databases and inserts data from each database into a results table. I'm attempting to return the results table to SSIS, but the Available External Columns are not populating. However, previewing the query in SSIS does show results. The insert in to the results table is done by a call to sp_executesql.

I've tried setting the results table up as a temp table, table variable, and static table. I have NOCOUNT set ON and am only returning one recordset. I've seen the other threads in here about similar problems, but none of their solutions seem to work for me.

Any help would be much appreciated....

I believe that there are known problems around using SPs in OLE DB Source components. Its something to do with requiring that the query that returns the data is the first statement in the proc. This enables SSIS to understand the metadata of the returned result set.

I know this doesn't help you in your case though. I do not know if there is a workaround or not having never faced this problem before myself. I hope what I've said above goes some way to helping though.

-Jamie

|||I think OLE DB Command will execute the Store proc|||Try converting the sp to a multi-statement table-valued function, giving Data Access Mode for your OLE DB source as SQL Statement and specifying the statement SELECT * FROM your_table_valued_function(); the wizard will gussy the SQL up

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

OLE Automation and "global variable"

I have an OLE automation object that I am instantiating inside of a stored
proc using
sp_OACreate 'myObject', @.object OUT.
The stored proc is called multiple times, each time instantiating a new
instance of the object (and then destroying it when it is finished).
Is it possible in SQL Server to create a global variable to refer to the
OLE automation object?
IOW can I instantiate the object outside the proc, assign the @.object
pointer to a global variable and then refererence this global variable
inside my stored proc so I don't have to create the object every time it is
called?
Perhaps a more basic question is: is it possible to create global variables
in T-SQL and if so, how?
Thanks
DaveAs far as I know, OLE Automation objects in SQL Server can only exist inside
the scope where they were created.
There are also no global variables in SQL, but you could create a temporary
(or permanent) table to store values that can be accessed "globally".
I strongly recommend you destroy all objects after use, even those
implicitly created by the methods used. But then again you mentioned that yo
u
already do so. :)
SQL 2005 also supports (and controls) CLR, maybe that can provide you with
more functionality.
ML
http://milambda.blogspot.com/