OPENQUERY throws Error 7357 when the source SP uses temporary table.

Hello Everybody / Anybody,

Sorry but exiting problem!

The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.

Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).

I'm importing data from Excel. Before import, I want to validate it against my master list values.

[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]

In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]

I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table.

I think you got what the situation is!! Woh!

Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!!

- The Code -

---

create proc usp_SelectData
as
create table #xx (FixedCol int)
insert into #xx select 1 union select 2
select * from #xx
drop table #xx

create proc usp_SelectData2
as
create table xx (FixedCol int)
insert into xx select 1 union select 2
select * from xx
drop table xx
-- Please replace MyDB with your current Database
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')

-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.]
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine

---

Thanks in advance...

Hi,

normally the OLEDB provider is expecting to get something back from the query. So try to put in either a return 0 or a simple Select or string stating ('Statement executed') at the bottom of the query.

BTW, why don′t you just use the select statement rather than creating tables and so on.. select 1 union select 2 ?

HTH, Jens Sü?meyer.

http://www.sqlserver2005.de

sql

Open Connection

Hello everybody

I would like to know more about the number of possible connection to a sql server

is it by pool ? or there is max for all the database ? all the server ?

how I can get the number of connection open ?

Thx in advance

If you run sp_configure and look for "user connections" it will tell you the max number of connections to the server. If you run sp_who2 you can see total connections at that time, and sp_who2 active gives active connections doing something.

|||

Ok, perfect

thank you very much

Only functions and extended stored procedures can be executed from within a function

Hi everybody,

When i try to excute a function i got the following error message:
" Only functions and extended stored procedures can be executed from within a function."

I wrote the following stored procedure wich update the sequence id :
---------------------
CREATE PROCEDURE dbo.pr_get_id_sequence
(@.p_nom_sequence varchar(100),
@.p_nom_table varchar(100),
@.p_id_sequence numeric OUTPUT ) AS

-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @.v_id_sequence numeric

SET @.v_id_sequence = -1

UPDATE GST_SEQUENCE
SET @.v_id_sequence = ID_SEQUENCE = ID_SEQUENCE + 1
WHERE NOM_SEQUENCE = @.p_nom_sequence
AND NOM_TABLE = @.p_nom_table

SET @.p_id_sequence = @.v_id_sequence
RETURN @.p_id_sequence

Then, i wrote the following function which return the last value of sequence id:
--------------------
CREATE FUNCTION dbo.fu_get_id_sequence
(@.p_nom_sequence varchar(100),
@.p_nom_table varchar(100)
) returns int AS

BEGIN
DECLARE @.v_id_sequence numeric

Execute dbo.pr_get_id_sequence @.p_nom_sequence, @.p_nom_table, @.v_id_sequence

RETURN @.v_id_sequence

END

And, when i try to call the function, i got the error message:
-------------------
select dbo.fu_get_id_sequence ('SEQ', 'TABLE')

By the way i'm using SQL Server 2005. Also, I tried to create the function by incliding the UPDATE statement but it didn't work.

Can anyone help me ?
Thank you

PaulThe error messages clearly point to the source of error: you cannot call stored procedures, as well as perform updates, deletes and inserts in T-SQL functions. That's why you cannot execute your function. You'll have to create stored procedure instead of function to be able to call another stored procedure. Also, all output parameters should be explicitly marked as output when you call a stored procedure:

Execute dbo.pr_get_id_sequence @.p_nom_sequence, @.p_nom_table, @.v_id_sequence output