Error Reporting from Stored Procedures

How do you report errors from a stored procedure? For example, if the stored
procedure is trying to insert something into a table and the database is
full, how can the stored procedure return an error string to the caller?Hi Swami,
This article might help...
http://www.sqljunkies.com/Article/5...3B6D84BF94.scuk
"Swami" wrote:

> How do you report errors from a stored procedure? For example, if the stor
ed
> procedure is trying to insert something into a table and the database is
> full, how can the stored procedure return an error string to the caller?
>|||Thanks--great article.
"Ryan Randall" wrote:
> Hi Swami,
> This article might help...
> [url]http://www.sqljunkies.com/Article/564F5D77-2F7E-41FB-91C7-353B6D84BF94.scuk[/url
]
>
> "Swami" wrote:
>

Error on inserting record on remote server

Hi,
I get this error when i insert a record from my local server to a
remote server.
Server: Msg 7391, Level 16, State 1, Procedure usp_insSO2NavAppV2, Line
14
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
pls help me
from
Doller
MSDTC might not be started on one of the servers
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"doller" <sufianarif@.gmail.com> wrote in message
news:1127785678.067089.75530@.o13g2000cwo.googlegro ups.com...
> Hi,
> I get this error when i insert a record from my local server to a
> remote server.
>
> Server: Msg 7391, Level 16, State 1, Procedure usp_insSO2NavAppV2, Line
> 14
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> pls help me
> from
> Doller
>

Error on inserting record on remote server

Hi,
I get this error when i insert a record from my local server to a
remote server.
Server: Msg 7391, Level 16, State 1, Procedure usp_insSO2NavAppV2, Line
14
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
pls help me
from
DollerMSDTC might not be started on one of the servers
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"doller" <sufianarif@.gmail.com> wrote in message
news:1127785678.067089.75530@.o13g2000cwo.googlegroups.com...
> Hi,
> I get this error when i insert a record from my local server to a
> remote server.
>
> Server: Msg 7391, Level 16, State 1, Procedure usp_insSO2NavAppV2, Line
> 14
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in th
e
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
>
> pls help me
> from
> Doller
>

error on insert...

I'm getting an error on the insert... I recently when to switch over from the @.param.Oledb type and am now using the ? for the values... Well I switched my updates and now am working on the inserts, but I am getting the error from this statement. I tried taking off the VALUES portion, but that didn't work either...

queryString = "INSERT INTO [griff30_24_stats] ([W_Rec], [Opponent], [L_Rec], [points], [done]) VALUES(?, ?, ?, ?,)"

Dim cmd as new OleDbCommand(queryString,dbConnection)

cmd.Parameters.Add("W_Rec",OleDbType.VarChar).Value = "Y"

'''It looks like you have 5 columns but only 4 question marks. Add afinal question mark (?) after that last comma and that should help.
|||

Thats a copy paste error. I have 5 "?'s" in the code, but I was going wild with the backspace. I still don't see why it's giving me the error is there a different style??

|||Can you show us a little more of the code as well as the exact error message?|||

Man, it was a little syntax error, I was missing a '(apostrophe in a statement). The little things I look over.

Error on insert to

Cannot insert explicit value for identity column in
table 'INCOMING_TEMP' when IDENTITY_INSERT is set to OFf
hi garry,
you will have to SET option identity_insert on the table. also while
inserting you will have to specify column list.
ex:
set identity_insert <table> on
insert into <table> (col1, col2) values(1,1)
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Error on INSERT Query

I want to insert "489d68bf-baa1-415d-94c7-a8e56b-609c61" in a column in sql
server . The data type of this column is varchar (100). i am getting the
folloing error - incorrect syntax near '68bf'. I think it is not accepting
the '-'. How can i get a work around for the '-' so that the above values
gets inserted successfully.
Vijay,
can you post some sample DDL for the table, as well as the insert statement
that's throwing the error? that value should be legal in the column.
David
"Vijay" <Vijay@.discussions.microsoft.com> wrote in message
news:233A3D0E-1A73-424A-BB00-608D9676247B@.microsoft.com...
>I want to insert "489d68bf-baa1-415d-94c7-a8e56b-609c61" in a column in sql
> server . The data type of this column is varchar (100). i am getting the
> folloing error - incorrect syntax near '68bf'. I think it is not accepting
> the '-'. How can i get a work around for the '-' so that the above values
> gets inserted successfully.
|||Are you trying to insert via a stored procedure, or a simple insert
statement?
Try delimiting the string with single quotes. As in:
INSERT INTO YourTable (YourColumn) VALUES
('489d68bf-baa1-415d-94c7-a8e56b-609c61')
Replace YourTable and YourColumn with whatever is appropriate
Keith Kratochvil
"Vijay" <Vijay@.discussions.microsoft.com> wrote in message
news:233A3D0E-1A73-424A-BB00-608D9676247B@.microsoft.com...
>I want to insert "489d68bf-baa1-415d-94c7-a8e56b-609c61" in a column in sql
> server . The data type of this column is varchar (100). i am getting the
> folloing error - incorrect syntax near '68bf'. I think it is not accepting
> the '-'. How can i get a work around for the '-' so that the above values
> gets inserted successfully.
sql

Error on Date Data During Insert

Hi Folx,
I am new to SQL Server and I am struggling with source data for a table with two db_datetime columns. The data can be inserted using native SQL, but errors when I build a Data Flow Container.

Versions:
Microsoft SQL Server Integration Services Designer
Version 9.00.1399.00

Microsoft SQL Server Management Studio
9.00.1399.00

Error:
[Flat File Source [1]] Error: The "output column "extraction_date" (24)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "extraction_date" (24)" specifies failure on error. An error occurred on the specified object of the specified component.

Source Row:

101|100|2006/12/19 23:50:01.00|2006/12/19 23:50:01.00|||

How I got here (in approximate order...):

Using ETL I created a Container in which I created a PackageCreated a Flat File ConnectionCreated a Flat File SourceEdited the columns on the Flat File Connection
database timestamp [DT_DBTIMESTAMP]NOTE: corresponding columns on destination table in SQL Server Management Studio are of type datetime

Created an OLE DB DestinationExecuted the package which returned the above named error.

I mucked about with data types, Derived Columns, Data Conversion, etc and Googled for solutions without success. One of the errors (which I could not duplicate...) I noticed during my "experimentation" was an out-of-memory condtion.

Questions:
01. What is the proper format for my source data?
02. Could this be a memory issue? If so, how do I diagnose it?

In advance, thanks for your help.

Bill

ps: be kind to me...not only am i an old guy, but i'm a unix guy too...

Native SQL that works:

insert into "transaction"
(transaction_id,
org_id,
extraction_date,
create_date,
transaction_size,
profile_count,
version_count)
values
(101,100,
'2006/12/19 23:50:01',
'2006/12/19 23:50:01',
NULL,
NULL,
NULL)Are you sure the flat file source is mapped correctly, with correct lengths, and with a date/time format correct for DB_DBTIMESTAMP?

There should be other errors right next to the one you posted... Can you grab those as well?

Phil|||I also wonder if it's a locale issue within SSIS, or something... That is with respect to the leading year on your DT_DBTIMESTAMP column. Just throwing that out there.|||All the error messages:

[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "extraction_date" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

NOTE: OutputColumnWidth is zero and "grayed out" in the Flat File Connection Manager Editor

[Flat File Source [1]] Error: The "output column "extraction_date" (24)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "extraction_date" (24)" specifies failure on error. An error occurred on the specified object of the specified component.

[Flat File Source [1]] Error: An error occurred while processing file "C:\work\CAPS\SQL\UNL\unloader\cont100.txt" on data row 1.

[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.|||
Locale ID is English (United States)|||Have you redirected your error output to a data viewer to be able to inspect the error row(s)?

Have you previewed the data in the flat file connection? Does it all look correct? Can you post a sample row of data?|||
Well Phil, thought I was going to be slick and redirect to a data viewer, but I don't know what I did wrong because now my package done disappeared on me...in other words, the structure no longer shows in the "Solution Explorer". I can see my Control Flow, Data Flow, and Connection Managers, but don't know how to now display/execute the package!

Before that happened, I did open the Flat File Connection Manager Editor and the data looked correct when I selected "Columns" and "Preview".

I will reconstruct after lunch...and if you would like to take this discussion offline, you can send e-mail to tampa_dba@.yahoo.com. If / when this gets resolved, then we can post the solution...

|||The solution explorer is buggy at best.

Find the physical package, and move it to another folder... Then, in solution explorer, you can add existing package. Select the moved package, and you'll be all set again. You'll then be able to delete the "moved" file.|||
Okay, it's back. Is this what you want?

101|100|2006/12/19 23:50:01.00|2006/12/19 23:50:01.00|||

'Name''Data Type''Precision''Scale''Length''Code Page''Sort Key Position''Comparison Flags''Source Component'
'transaction_id''DT_I4''0''0''0''0''0''''Flat File Source'
'org_id''DT_I4''0''0''0''0''0''''Flat File Source'
'extraction_date''DT_DBTIMESTAMP''0''0''0''0''0''''Flat File Source'
'create_date''DT_DBTIMESTAMP''0''0''0''0''0''''Flat File Source'
'transaction_size''DT_I4''0''0''0''0''0''''Flat File Source'
'profile_count''DT_I4''0''0''0''0''0''''Flat File Source'
'version_count''DT_I4''0''0''0''0''0''''Flat File Source'|||SSIS does not like the fraction in your timestamp column. If I drop that, the package works fine. Now I'm trying to figure out why.|||
thanks for your help. dropping the fractional worked. i don't like that it worked because the datatype should support fractional time, but it is not a requirement.

Error on Create Trigger

I have the following

CREATE TRIGGER dbo.tgrCacheCustomers
ON dbo.Customers
FOR INSERT, UPDATE, DELETE
AS
EXEC sp_makewebtask 'C:\DependencyFile.txt','SELECT top 1 CustomerId FROM customers'

and I get the following error that I dont understand:

Error 21037: [SQL-DMO] The name specified in the Text property's 'CREATE ...' statement must match the Name property, and must be followed by valid TSQL statements.

Any ideas someone?Before anyone can help, please kindly explain what you want to accomplish in:
EXEC sp_makewebtask 'C:\DependencyFile.txt','SELECT top 1 CustomerId FROM customers'

Thanks.|||

This error occurs sometimes when you renmae the trigger manually by using F2 Function key & rename. if the name doesent matches with the name specified in the trigger.

Regards
Salman Zafar

Error number for duplicate column value during an insert for a unique column ?

I have a table in which a non-primary key column has a unique index on it.

If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?

Open a query analyzer and try to manually insert a value into the table and see what SQL complains..

|||

The error number for inserting duplicate value in a column of unique index is : 2601.

Error Msg 7391 in distributed transaction

I have a procedure which is trying to insert data into a table on Server A.
This insert is done based ona return value from a select statement from
Server B . A linked server has been set up for Server B on server A. The
error when populating table by inserting data is
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Is there any other way to get thsi work. TIA
Message posted via http://www.webservertalk.comYou want to start with this kb for your troubleshooting.
http://support.microsoft.com/kb/306212
-oj
"ishaan99 via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:e4a371938b9f454d851da07f5c672039@.SQ
webservertalk.com...
>I have a procedure which is trying to insert data into a table on Server A.
> This insert is done based ona return value from a select statement from
> Server B . A linked server has been set up for Server B on server A. The
> error when populating table by inserting data is
> Server: Msg 7391, Level 16, State 1, Line 1
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB'
> was unable to begin a distributed transaction.
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> Is there any other way to get thsi work. TIA
> --
> Message posted via http://www.webservertalk.com

Error Msg 141 on Bulk Insert

I've got the following SP to automatically insert all files in a directory into the database:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE Imp_Header_PO_sp

@.FilePath varchar(1000) = 'D:\EBT\Outbound\',

@.WIPPath varchar(1000) = 'D:\EBT\Outbound\WIP',

@.ArchivePath varchar(1000) = 'D:\EBT\Outbound\Archive',

@.FileNameMask varchar(1000) = '*Header.txt'

AS

BEGIN

SET NOCOUNT ON;

declare @.Filename varchar(1000),

@.File varchar(1000)

declare @.cmd varchar(2000)

create table #Dir (s varchar(8000))

-- Move Header files to WIP

select @.cmd = 'move ' + @.FilePath + @.FileNameMask + ' ' + @.WIPPath

select @.cmd = 'dir /B ' + @.WIPPath + @.FileNameMask

delete #Dir

insert #Dir exec master..xp_cmdshell @.cmd

delete #Dir where s is null or s like '%not found%'

-- Import file

while exists (select * from #Dir)

begin

select @.FileName = min(s) from #Dir

select @.File = @.WIPPath + @.FileName

select @.cmd = 'bulk insert'

select @.cmd = @.cmd + ' POWebOutHeader'

select @.cmd = @.cmd + ' from'

select @.cmd = @.cmd + ' ''' + replace(@.File,'"','') + ''''

select @.cmd = @.cmd + ' with (Fieldterminator = ',')'

-- Import the data

exec (@.cmd)

-- remove filename just imported

delete #Dir where s = @.FileName

-- Archive the file

select @.cmd = 'move ' + @.WIPPath + @.FileName + ' ' + @.ArchivePath + @.FileName

exec master..xp_cmdshell @.cmd

end

drop table #Dir

END

GO

When I try to execute the code, I get the following error, on this line: select @.cmd = @.cmd + ' with (Fieldterminator = ',')'

Msg 141, Level 15, State 1, Procedure Imp_Header_PO_sp, Line 46

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

I've tried to find a fix for this error, but it seams to only relate to a select statement and not a Bulk Insert. Can someone please help me figure out how to fix this error?

Thanks,

Laura

Causes:

This error occurs when you are assigning the column values from a SELECT statement into local variables but not all columns are assigned to a corresponding local variable.

|||

The error occurs because you need to double your quotes

select @.cmd = @.cmd + ' with (Fieldterminator = '','')'

instead of

select @.cmd = @.cmd + ' with (Fieldterminator = ',')'

Run these 2 example to see the error

declare @.cmd varchar(50)
select @.cmd =''
select @.cmd = @.cmd + ' with (Fieldterminator = ',')'
print @.cmd

declare @.cmd varchar(50)
select @.cmd =''
select @.cmd = @.cmd + ' with (Fieldterminator = '','')'
print @.cmd

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||You are awesome! Thanks!

Error message when using SQLCE on WM2005 - ERROR MSG: Not enough storage is available to complet

Hi All,

I got this Message from my SQLCE.

I have a loop that insert rows into the DB,

Once in a few times I get this error message on one of the rows.

But, after that the next row is insert with no problem

Any one knows this reason?

The message is:

ERROR MSG: Not enough storage is available to complete this operation

The SQL was:

SQL Insert ERROR->

INSERT INTO OrderDetails (fileTimeStamp,orderPPCNum,storeID,itemID,itemName,quantityToPick,colorDescription,colorDetails,bases,diameter,axis,referenceNum,remarks,quantityPicked,quantityOrdered,status,datetime) VALUES('08211640','000121','0001','65700300000','-300 ????? ??????','1','','','','','0','143145','','','',0,getdate())

ERROR MSG: Not enough storage is available to complete this operation.

This error means: An out of memory condition occurred.

|||

Thanks for your answer..

The wierd thing is that I continue to insert many rows into the DB with no problem.

Once in few days I get a problem of one row. I have no problem with the data entry since I insert it again after the problem, while running the procedure again without any change and with no error.

What else can couse that?

If I have this error, is it means that I don't have enough storage and I was assuming that all other rows on that senario were not suppose to enter the DB, but they did.

|||

Another question,

If its the only reason the server send this error message, what should I do before starting to insert records into the DB?

Is there a way to clean the available storage area?

Error message

Hello,
I want to insert data into a temp Table using an Insert Exec statement such
as
insert #Mytable(Col1,...)
exec mysstoreProcedure @.var1
And I have the following message :
Server: Msg 8164, Level 16, State 1, Procedure ps_hasThisSolution, Line 25
An INSERT EXEC statement cannot be nested.
What is the problem ?This is a multi-part message in MIME format.
--=_NextPart_000_0012_01C37912.1B9AAE00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hi ! Following you can seee the 2 procedures :
CREATE Procedure ps_hasThisSolution(
@.p_Client varchar(32) =3D NULL,
@.p_Produit varchar(32) =3D NULL,
@.hasSol int output
)
AS
create table #temp_sw_2 (
CODCLI varchar(10),
CODLOG varchar(10),
VERLOG float,
TYPE varchar(1),
CREATION smalldatetime,
DTEACHAT smalldatetime,
DTEEXPIR smalldatetime,
TYPE_ORDINATEUR varchar(30)
)
SET NOCOUNT ON
declare @.tmp varchar(50)
insert #temp_sw_2(CODCLI, =CODLOG,VERLOG,TYPE,CREATION,DTEACHAT,DTEEXPIR,TYPE_ORDINATEUR)
exec ps_getProductBySolution @.p_Client
if exists (SELECT *
FROM produit_version INNER JOIN
#temp_sw_2 ON produit_version.code_version =3D #temp_sw_2.CODLOG
WHERE #temp_sw_2.CODLOG =3D @.p_Produit )
select @.hasSol =3D 1
else
select @.hasSol =3D 0
return
And the 2nd procedure :
CREATE Procedure ps_getProductBySolution(
@.p_Client varchar(10) =3D Null
)
AS
declare @.p_solution varchar(10), @.p_verlog float, @.p_creation =smalldatetime, @.p_dteachat smalldatetime, @.dteexpir =smalldatetime,@.dteexpir_min smalldatetime
declare @.type_ordinateur varchar(30),@.type_produit varchar(10)
exec ps_getSolution @.p_Client, @.p_solution output, @.p_verlog output, =@.p_creation output, @.p_dteachat output, @.dteexpir output, @.dteexpir_min =output,@.type_ordinateur output,@.type_produit output
if @.p_solution is NULL begin
SELECT WCM.CODCLI, produit_version.code_produit, WCM.VERLOG, WCM.TYPE, =WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur
FROM produit_version INNER JOIN
WCM ON produit_version.code_version =3D WCM.CODLOG INNER JOIN produit ON
produit_version.code_produit=3Dproduit.code_produit AND =produit.type_produit=3D'LG'
WHERE (WCM.CODCLI =3D @.p_Client) end
else
begin
SELECT WCM.CODCLI,produit_version.code_produit, WCM.VERLOG, WCM.TYPE, =WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur
FROM produit_version INNER JOIN
WCM ON produit_version.code_version =3D WCM.CODLOG INNER JOIN
produit ON produit.code_produit =3D WCM.CODLOG AND produit.type_produit ==3D'LG'
WHERE (WCM.CODCLI =3D @.p_Client) AND produit_version.code_produit NOT IN
(SELECT produit_plus.code_produit
FROM produit_plus
WHERE produit_version.code_produit =3D produit_plus.code_produit
AND (produit_plus.type =3D 'SL' OR
produit_plus.type =3D 'SP') AND produit_plus.produit_type =3D 'LG' AND produit_plus.Code_plus ==3D @.p_solution)
UNION
SELECT @.p_Client, @.p_solution, @.p_verlog, null ,@.p_creation, =@.p_dteachat, @.dteexpir,@.type_ordinateur
end
return
"jeff" <jeff@.hotmail.com> wrote in message =news:02ec01c37888$83c24c20$a101280a@.phx.gbl...
> Perheps the mysstoreProcedure procedure too uses Insert > into ... exec <proc> to insert data in some table, is it > true?
> > Can you post the entire procedure code?
> > >--Original Message--
> >Hello,
> >I want to insert data into a temp Table using an Insert > Exec statement such
> >as
> >insert #Mytable(Col1,...)
> >exec mysstoreProcedure @.var1
> >
> >And I have the following message :
> >Server: Msg 8164, Level 16, State 1, Procedure > ps_hasThisSolution, Line 25
> >An INSERT EXEC statement cannot be nested.
> >
> >What is the problem ?
> >
> >
> >.
> >
--=_NextPart_000_0012_01C37912.1B9AAE00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi !
Following you can seee the 2 procedures =:
CREATE Procedure ps_hasThisSolution(@.p_Client varchar(32) =3D NULL,@.p_Produit varchar(32) =3D =NULL,@.hasSol int output)AS
create table #temp_sw_2 (CODCLI varchar(10),CODLOG varc=har(10),VERLOG float,TYPE  =;varchar(1),CREATION smalldatetime,DTEACHAT  =;smalldatetime,DTEEXPIR smalldatetime,TYPE_ORDINATEUR&=nbsp;varchar(30))SET NOCOUNT ONdeclare @.tmp varchar(50)
insert #temp_sw_2(CODCLI, CODLOG,VERLOG,TYPE,CREATION,DTEACHAT,DTEEXPIR,TYPE_ORDINATEUR)exec ps_getProductBySolution @.p_Client
if exists (SELECT * FROM produit_version INNER JOIN #temp_sw_2 ON produit_version.code_versio=n =3D #temp_sw_2.CODLOG WHERE =#temp_sw_2.CODLOG =3D @.p_Produit ) select @.hasSol =3D 1 else select @.hasSol =3D 0return
And the 2nd procedure :
CREATE Procedure ps_getProductBySolution(@.p_Client varchar(10) =3D Null)AS declare @.p_solution varchar(10), @.p_verlog =float, @.p_creation smalldatetime, @.p_dteachat smalldatetime, @.dteexpir smalldatetime,@.dteexpir_min smalldatetime declare =@.type_ordinateur varchar(30),@.type_produit varchar(10)
exec ps_getSolution =@.p_Client, @.p_solution output, @.p_verlog output, @.p_creation output, @.p_dteachat =output, @.dteexpir output, @.dteexpir_min output,@.type_ordinateur =output,@.type_produit output
if @.p_solution is NULL begin SELECT WCM.CODCLI, =produit_version.code_produit, WCM.VERLOG, WCM.TYPE, WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur FROM produit_version INNER JOIN WCM ON = produit_version.code_version =3D WCM.CODLOG INNER JOIN produit =ON produit_version.code_produit=3Dproduit.code_produit AND produit.type_produit=3D'LG' WHERE (WCM.CODCLI =3D @.p_Client) end else begin
SELECT WCM.CODCLI,produit_version.code_produit, WCM.VERLOG, WCM.TYPE, =WCM.CREATION, WCM.DTEACHAT, WCM.DTEEXPIR,type_ordinateur FROM =produit_version INNER JOIN WCM ON produit_version.code_version =3D WCM.CODLOG =INNER JOIN produit ON = produit.code_produit =3D WCM.CODLOG AND =produit.type_produit =3D'LG' WHERE (WCM.CODCLI =3D @.p_Client) AND produit_version.code_produit NOT IN (SELECT produit_plus.code_produit FROM produit_plus WHERE produit_version.code_produit =3D produit_plus.code_produit &n=bsp; AND (produit_plus.type =3D 'SL' OR produit_plus.type =3D 'SP') AND produit_plus.produit_type =3D 'LG' AND produit_plus.Code_plus =3D @.p_solution) UNION SELECT @.p_Client, =@.p_solution, @.p_verlog, null ,@.p_creation, @.p_dteachat, @.dteexpir,@.type_ordinateur end return
"jeff" =wrote in message news:02ec01c37888$83c24c20$a101280a@.phx.gbl...> Perheps the mysstoreProcedure procedure too uses Insert > into ... exec = to insert data in some table, is it > true?> > Can =you post the entire procedure code?> > >--Original Message--> >Hello,> >I want to insert data into a =temp Table using an Insert > Exec statement such> =>as> >insert #Mytable(Col1,...)> >exec mysstoreProcedure =@.var1> >> >And I have the following message :> >Server: =Msg 8164, Level 16, State 1, Procedure > ps_hasThisSolution, Line =25> >An INSERT EXEC statement cannot be nested.> >> =>What is the problem ?> >> >> >.> >

---

--=_NextPart_000_0012_01C37912.1B9AAE00--|||<snip>
There is a third procedure involved - ps_getsolution. You must check the
entire execution chain.