Hi all,
I have to insert records using transact iin the stored procedure. I have some thing like:
DECLARE @Err varchar(100)
DECLARE insertQ varchar(1000)
SET @Err = 'Insertion data'
SET @insertQ =('INSERT INTO dbo.T_ERRORLOG (ERROR_DESCR) VALUES(' + @Err + ')')
EXEC insertQ
But it don't work. Can you help me to solve the problem plz?
Thanks a lot
TT
This is a bit lengthy, but lets say we have three tables
a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue
which shows records like below
Table 1 - tblSaleStatementCustomer
ID  CustomerName   VoucherType   Outbound   Inbound   CustomerType
----------------------------------------------------------------------------------------------
1   ABC                Sales         10000        0          Dealer
2   MNC               Sales          9000        0          Dealer
3   MNC               Sales          4000        0          Dealer
Table 2 - Â tblCreditors
ID  Name   OpeningBalance
----------------------------------------------------------------------------------------------
1 Â Â ABC Â Â Â Â Â 20000 Â
2 Â Â MNC Â Â Â Â 15000Â
3 Â Â XBM Â Â Â Â 18000
4 Â Â XYZ Â Â Â Â Â 12000
Lets say we are executing this query below to retrieve each customer and the amount associated to a tableÂ
"INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE Â - Inbound END) AS AMOUNT from
tblSaleStatementCustomer  WHERE CustomerType = 'Dealer' GROUP BY CustomerName"
Which display the data like below
DEALER Â Â Â Â Â Â Â Â AMOUNT
------------------------------------------------
ABC Â Â Â Â Â Â Â Â Â Â Â Â Â 2000
XYZ Â Â Â Â Â Â Â Â Â Â Â Â Â 1000 Â Â
However I have one more table TABLE2 which contains two columns
DEALER Â Â Â Â Â Â Â Â OPENING
-------------------------------------------------------
ABC Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 5000
XYZ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 7000
I want to modify my query in such a way that AMOUNT column should also add
OPENING from TABLE2 So that I must get the result like below
DEALER Â Â Â Â Â Â Â Â AMOUNT
------------------------------------------------
ABC Â Â Â Â Â Â Â Â Â Â Â Â Â Â 7000
XYZ Â Â Â Â Â Â Â Â Â Â Â Â Â Â 8000 Â Â
I'm trying to insert records into "holding" table and write back identity column value (Entry_Key) to the original table. So my setup is I have two tables; tblEWPBulk and tbleFormsUploadEWP. Users will enter records into tblEWPBulk and use BatchID to group records, once batch entry has been completed (usually less than 30 records) user will click on UploadAll button and insert records (not all fields) into tbleFormsUploadEWP. One record in tblEWPBulk can be sent multiple times to the holding table but tblEWPBulk will need to have latest Entry_Key captured. Records are sent from holding table to DB2 z/VSE using SQL stored procedure and based on certain logic records are marked uploaded or certain error capture... that part works fine.
So for example I want to sendÂ
BatchID, AccountNumber, Period, ReceiveDate, AccountType, ReturnType, NetProfitOrLoss, TaxCredit FROM tblEWPBulk to the holding table and write back Entry_Key (identity column) back to the record in tblEWPBulk (field called UploadEntryKey). As I said one record could be sent to the holding table multiple times until uploaded or deleted and UploadEntryKey always needs to be updated so that when results are processed response from the DB2 can be inserted into table and presented to the user.
No foreign key relationship exists since records in the holding table get sent to the archive table and table is truncated and entry_key starting value reset back to 2000... just some DB2 restrictions.Â
I have the following query:
BEGIN TRAN
Declare @StartDt date = '2015-03-15'
Declare @EndDt date = DATEADD(M, 1, @StartDt)
declare @Days int = DATEDIFF(d, @StartDt, @EndDt)
declare @TBLSales as table(SaleDate date, Value money)
DECLARE @Today date
declare @TBLSalesCounts as table( StatusDesc varchar(100), Value money)
[Code] ....
I end up with the following result :
How would I alter my while loop to only insert the sum total of each day, instead of creating duplicates for each day.
E.g.
2015-04-22
1150.00
2015-04-21
 785.00
2015-04-20
 750.00
The above error message shows with the following T-SQL code of a dynamic query:
declare CheckColumn cursor for exec('select distinct ' + @myColumnName + ' from ' + @myTableName)The 2 varchar variables are properly declared. What's wrong syntactically?Thanks.
alter PROCEDURE Select_ShortInitials
(
@intRetVal int=null
)
as
begin
if (@intRetVal=1)
begin
begin Transaction
declare @PresentEmp as varchar(50)
declare @PEmp as varchar(50)
declare @strInsertStatment as varchar(8000)
set @strInsertStatment=''
declare InsertStatmentSupportedPhonesCur cursor
global
scroll
dynamic
optimistic
for
select name from smartjot.dbo.sysobjects where name like 'Employee_%' and name not like 'Employee_details%'
create table #tempShort(Short varchar(50))
open InsertStatmentSupportedPhonesCur
fetch first from InsertStatmentSupportedPhonesCur into @PresentEmp
while @@fetch_status = 0
begin
declare InnerCursor cursor
global
scroll
dynamic
optimistic
for
EXEC ('select distinct ShortInitial from ' + @PresentEmp)
--select distinct ShortInitial from @PresentEmp
open InnerCursor
fetch first from InnerCursor into @PEmp
while @@fetch_status = 0
begin
insert into #tempShort(Short) values(@PEmp)
fetch next from InnerCursor into @PEmp
end
close InnerCursor
deallocate InnerCursor
fetch next from InsertStatmentSupportedPhonesCur into @PresentEmp
end
close InsertStatmentSupportedPhonesCur
deallocate InsertStatmentSupportedPhonesCur
--Preparing Insert Statment for SupportedPhones Table - End
select * from #tempShort
if(@@error=0)
begin
commit transaction
end
else
begin
rollback transaction
end
end
end
I already used temptable, it works fine in SQL Server but doesn't work in Visual Studio. (it returns me "incorrect syntax near ',').
Here is my sql:
SET @sql = 'SELECT
RMI.Response_Date,
RMI.Master_Incident_Number,
RMI.Jurisdiction,
RVA.Radio_Name,
RVA.Response_Number,
RMI.Division,
RMI.Battalion,
RMI.Address,
RMI.Call_Disposition,
RMI.Cancel_Reason,
RMI.Problem,
RMI.Time_CallEnteredQueue,
RVA.Time_Enroute,
RVA.Time_ArrivedAtScene,
RG.Region,
RN.District ' +
' INTO RPT_PRIME_JOB_DTL ' +
' FROM ' + @server_name + 'Response_Master_Incident RMI ' +
'LEFT OUTER JOIN ' + @server_name + 'Response_Vehicles_Assigned RVA
ON RMI.ID = RVA.Master_Incident_ID ' +
'LEFT OUTER JOIN AKSDCSVR01.CAD_Lookup_Tables.dbo.Radio_Name RN
ON RVA.Radio_Name = RN.Radio_Name Collate SQL_Latin1_General_CP1_CI_AS ' +
'LEFT OUTER JOIN AKSDCSVR01.CAD_Lookup_Tables.dbo.Region RG
ON RMI.Jurisdiction = RG.Jurisdiction Collate SQL_Latin1_General_CP1_CI_AS ' +
'WHERE ' +
' RG.Region = ''' + @region + '''' +
' AND RN.District IN (SELECT Item FROM dbo.StringArrayIntoTable(''' + @district + ''', '','') ' +
' AND (RMI.response_date >= ''' + CONVERT(VARCHAR(10), @date_from,111) + '''' +
' AND RMI.response_date <''' + CONVERT(VARCHAR(10),@date_to+1,111) + ''')' +
' AND RVA.Radio_Name LIKE ''PD%'''
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EXEC (@sql)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT *
FROM RPT_PRIME_JOB_DTL
Thank
Following is the stored procedure iam trying to create.Here i am trying to
First create a table with the table name passed as parameter
Second I am executing a dynamic sql statement ("SELECT @sql= 'Select * from table") that returns some rows.
Third I want to save the rows returned by the dynamic sql statement ("SELECT @sql= 'Select * from table") in the tablei created above.All the columns and datatypes are matching.
This table would be further used with cursor.
Now i am getting a syntax error on the last line.Though i doubt whether the last 3 lines will execute properly.Infact how to execute a sp_executesql procedure in another dynamic sql statement.ANy suggestions will be appreciated.
CREATE PROCEDURE [dbo].[sp_try]
@TempTable varchar(25)
AS
DECLARE @SQL nvarchar(MAX)
DECLARE @SQLINSERT nvarchar(MAX)
BEGIN
--create temp table
SELECT @Sql= N'CREATE TABLE ' + QUOTENAME(@TempTable) +
'(
ContactName varchar (40) NOT NULL ,
ContactId varchar (30) NOT NULL ,
ContactrMessage varchar (100) NOT NULL,
)'
EXEC sp_executesql @Sql, N'@TempTable varchar(25)', @TempTable = @TempTable
SELECT @sql= 'Select * from table'
SELECT @sqlinsert = 'INSERT INTO ' + quotename( @TempTable )
SELECT @sqlinsert = @sqlinsert + EXEC sp_executesql @sql, N'@Condition varchar(max)', @Condition=@Condition
EXEC sp_executesql @SQLINSERT, N'@TempTable varchar(25)', @TempTable = @TempTable
Lets say we have two tables
tblPayments (Contains the records of Payments we made)
ID Â Â Â Â DATE Â Â Â Â Â Â Â Â Â AMOUNT Â Â Â Â BANK
----------------------------------------------------------
1 Â Â Â Â Â 05/05/2015 Â Â Â Â 5000 Â Â Â Â Â Â Â Natwest
2 Â Â Â Â Â 05/05/2015 Â Â Â Â 2000 Â Â Â Â Â Â Â Lloyds
3 Â Â Â Â Â 05/06/2015 Â Â Â Â 3500 Â Â Â Â Â Â Â Natwest
4 Â Â Â Â Â 05/07/2015 Â Â Â Â 4000 Â Â Â Â Â Â Â Natwest
5 Â Â Â Â Â 05/08/2015 Â Â Â Â 1500 Â Â Â Â Â Â Â Lloyds
tblReceipts (Contains the records of Receipts we received)
ID Â Â Â Â DATE Â Â Â Â Â Â Â Â Â AMOUNT Â Â Â Â BANK Â
----------------------------------------------------------
1 Â Â Â Â Â 05/06/2015 Â Â Â Â 5000 Â Â Â Â Â Â Â Natwest
2 Â Â Â Â Â 05/06/2015 Â Â Â Â 2000 Â Â Â Â Â Â Â Lloyds
3 Â Â Â Â Â 05/07/2015 Â Â Â Â 3500 Â Â Â Â Â Â Â Natwest
4 Â Â Â Â Â 05/07/2015 Â Â Â Â 4000 Â Â Â Â Â Â Â Natwest
5 Â Â Â Â Â 05/08/2015 Â Â Â Â 1500 Â Â Â Â Â Â Â Lloyds
Now, I also have a blank table (tblBankStatement) which contain the following columns
ID Â Â Â DATE Â Â Â Â Â Â Â Â RECEIPT Â Â Â Â Â Â Â Â PAYMENT Â Â Â Â Â Â Â BANK
-----------------------------------------------------------------------------
I want that when I execute the query, the query should INSERT the records to the New Table (tblBankStatement) from
tblPayments and tblReceipts by Date Ordered in ascending way WHEREBank should be 'Natwest'.
Also the Amount Column Data in tblPayments should be Inserted into the Payment Column in tblBankStatement and the Amount Column Data in tblReceipts should be Inserted into the Receipt Column in tblBankStatement.
So I could get the data just like below
tblBankStatement
ID Â Â Â DATE Â Â Â Â Â Â Â Â RECEIPT Â Â Â Â Â Â Â PAYMENT Â Â Â Â Â Â Â BANK
--------------------------------------------------------------
1 Â Â Â Â 05/05/2015 Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Â Â Â Â Â Â 5000 Â Â Â Â Â Â Â Natwest
2 Â Â Â Â 05/06/2015 Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Â Â Â Â Â Â 3500 Â Â Â Â Â Â Â Natwest
3 Â Â Â Â 05/06/2015 Â Â Â Â Â Â 5000 Â Â Â Â Â Â Â Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Natwest
4 Â Â Â Â 05/07/2015 Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Â Â Â Â Â 4000 Â Â Â Â Â Â Â Natwest
5 Â Â Â Â 05/07/2015 Â Â Â Â Â Â 4000 Â Â Â Â Â Â Â Â Â Â Â Â Â 0.00 Â Â Â Â Â Â Â Natwest
What query should I write to perform the task above.         Â
I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??
USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]
[Code] ....
Basically, I have a table with a column that stores mathematical formulas in string format. When my UDF is executed, it needs to select an appropriate formula from this table and evaluate it using values that are stored in local variables.Â
Look at the example below:
Suppose I have a string named @vcFormula that contains the following:"@dVar1 + @dVar2 / @dVar2"Now suppose I have a variable named @dVar1 that contains a value of 1.0, and variable @dVar2 contains a value of 2.5. I can use the REPLACE function to change my original string to look like this:"1.0 + 2.5 / 2.5"
Now I want to execute this string and find the numeric result, placing it in a variable named @dResult. The following works, but presents a problem:CREATE TABLE #Result (dResult decimal(20, 10))INSERT #Result EXEC('SELECT ' + @vcFormula)SELECT @dResult = dResult FROM #ResultThe problem with using this method comes from the fact that I need to be able to evaluate @vcFormula from within a user-defined function, but temporary tables are not allowed inside UDF's.Â
So I attempted to change the temporary table above into an instance of the TABLE data type. This didn't work either because EXEC cannot be used to populate instances of the TABLE data type. Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's. Specifically, is there any way to execute a command/formula that is contained within a string other than by using EXEC?Â
I need to insert the results into a temp table and i recieve the "MSDTC on server 'servername' is unavailable error".
declare @thestringall varchar(1000)
set @thestringall = 'select statment here'
insert into #temptable exec (@thestringall)
Thanks
Wooanaz
I need get the o/p of a system sp into a table. I am doing the following,
insert #repl_monitor
exec [distribution].sys.sp_replmonitorhelpsubscription @publisher =
N'FGRWA0508', @publisher_db = N'DB_Name', @publication = N'publication'
Code is really not important. Any sys SP can replace the above code.
I am getting the following error
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested.
I have seen the following link which discuss this issue,
http://www.sommarskog.se/share_data.html
But there is no solution there.
I tried with sp_executesql and EXEC(), but unable to get the result. Can anyone put some light?
------------------------
I think, therefore I am - Rene Descartes
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp
Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
I've got a case where I am bringing older data into a brand new DB. Unfortunately, there are several versions of the old DB and I won't know ahead of time which one I will be running against. The data's basicaly the same, the table names are the same, but the column schema is NOT.
Basiclly, I want to use a conditional on the Insert.
Code Block
CREATE TABLE TestTable
(Col1 int NULL,
Col2 varchar(50) NULL)
GO
IF EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG= 'TestDB'
AND TABLE_NAME ='TestTable'
AND COLUMN_NAME= 'Fred')
INSERT TestTable
(Col1, Col2, Fred)
VALUES
(1,'test', 'fredvalue')
This results in an "Unknown column" error.
What am I missing here and how can I accomplish it.
I know that I COULD build a SQL string and Exec it, but that is such a PITA and not very elegant.
Any help would be appreciated
Hi,
I have a stored procedure that transfers data from one database to another. I do a lot of manipulation to the data in between because although both databases are for the same purpose, the database designs are different. The two databases exist on two different servers. Till now my transfer procedure used a linked server to pull the data from one server to the other. This process takes a ton of time with complex queries across linked servers. I was researching on a faster method to transfer data and the only other working method seemed to be the insert..exec statement with which I can call a remote stored procedure which would run and return the results of the complex queries which I can then use locally as I wish.
My main question is, can I rely on insert..exec to work consistently and/or is there another method to achieve what I need that I'm totally ignorant of? I already know of inline/multi table value functions (they wont work across servers); openquery/openrowset (I need to be able to pass parameters); creating permanent tables to be used and then destroyed (not very appealing). I read an excellent article about these online by Erland Sommarskog (http://www.sommarskog.se/share_data.html) but I'm hoping someone can explain a little further.
Thanks all,
Anand
There must be some workaround for this. I need to grab the data from a stored proc on another server and place it into a temp table. I do not have the option to change the proc.
help if you can.
Hi,
has anyone come across deadlocks on sysindexes in tempdb where the insert/exec combination is used.
eg
create table #fred (IntColumn int)
insert into #fred exec ProcThatSelectsAnIntColumn
This is being done in a stored procedure, and is deadlocking with other procs which are doing vanilla #table work - creating, inserting into, updatind, selecting from, etc.
I have noticed similar deadlocks where a #table is created inside an explict transaction, and I wondered whether there is an implicit transaction created, but @@Nestlevel is not changing either before or after the insert/exec.
I can't find any references in knowledgebase.
Any pointers appreciated.
Cheers
Simon
________________________
Simon Davis
Bankers Trust Australia Limited
Asset Management Technology
Ph: 61 2 9259 9137
<mailto:Simon.Davis@Bankerstrust.com.au>
Hi all,
I have an sp that sends cdomail which requires 4 variables.
I want an after insert trigger that fills in the values for the sp from the record just submitted, how can i do that?
Sp code
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/************************************************** *******************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
************************************************** *********************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', 'smtp.bbeyond.nl'
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg
GO
Hello,I want to share my experiences about using insert into exec which mayhelp others .Using SQL Server 2000, SP3 .Two Proceduers - Parent SP caliing a Child SP (nested ) . No Explicittransactions .I have defined a # table in Parent SP and calling a Child SP like thisinsert into #temp exec childsp ......Child SP has Select * from local # temp table ( local to child SP) as the last statement .When number of records are less ( around 1000 - 5000) Parent SPexecutes but slow .When the Child SP returns higher number of rows ( 1,00,000 or more )the SP will be running for hours with out completion .Although executing the child SP , with exec ChildSP .... with sameparameters it is completed in 2 mins for 3,00,000 rows .Resolution : - Define a temp table (say #tempChild ) in the Parent SP..In the Child SP instead of select * replace with insert into#tempChild select * from ...Also note that this problem is not noticed in SQL 2000 Server with SP4..This may be due to SP executing in implicit transactions .
View 1 Replies View RelatedI try to select a store procedure in SqlExpress2005 which inside store procedure execute another store procedure,
When I select it but it prompt error messages "An INSERT EXEC statement cannot be nested.".
In Fire bird /Interbase store procedure we can nested. Below are the code;
declare @dtReturnData Table(doccode nvarchar(20), docdate datetime, debtoraccount nvarchar(20))
Insert Into @dtReturnData
Exec GetPickingList 'DO', 0, 37256, 'N', 'N', 'YES'
Select doccode, docdate, debtoraccount
From @dtReturnData
Inside the GetPickList It will do like this, but most of the code I not included;
ALTER PROCEDURE GETPICKINGLIST
@doctype nvarchar(2),
@datefrom datetime,
@dateto datetime,
@includegrn char(1),
@includesa char(1),
@includedata nvarchar(5)
AS
BEGIN
declare @dtReturnData Table(doccode nvarchar(20),
docdate datetime,
debtoraccount nvarchar(20))
IF (@DOCTYPE = 'SI')
BEGIN
Insert Into @dtSALESINVOICEREGISTER
Exec SALESINVOICEREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
ELSE
BEGIN
Insert Into @dtDELIVERYORDERREGISTER
Exec DELIVERYORDERREGISTER @DateFrom, @DateTo, @IncludeGRN, @IncludeSA, @IncludeData
END
Select doccode,docdate,debtoraccount From @dtReturnData
END
So how can I select a nested store procedure? can someone help me
Hi,
I have written a master proc which calls another proc (say proc1).
This proc1 has insert-exec statements, for eg insert into #temp exec proc1.
i.e. multiple times the proc would be nested.
This the err thrown :
An INSERT EXEC statement cannot be nested.
Is it possible to resolve it..
Hi All,
I am having a problem with nested insert exec. Say for example I have three stored procedure procA, procB and procC.
I am executing procedure procC in procB; I am storing the values returned by procC in a tempTable. The code is
INSERT INTO #Temp
EXEC procC
And in procA, I am executing procB, and the values returned by procB are stored in another temp table. The code is
INSERT INTO #TempOne
EXEC procC
When I execute the procA, I am getting error as
An INSERT EXEC statement cannot be nested.
My requirement is like this, please give me a solution.
Thanks
i have a 3 or 4 cursors, and in the inner cursor i am inserting into a table from a sproc. i keep getting the error
An INSERT EXEC statement cannot be nested.
heres the actual insert code:
set @SQLString = 'EXEC ScoresGetlines '+cast(@customerID as char(10))+',' + cast(@programId as char(10))+',' + '"'+ @period +'",NULL,NULL,0'
INSERT INTO reportData
exec (@sqlString)
ive tried just a simple :
insert into reportdata
exec scoreGetLines @customerId,@programID...........
that still doesnt work. same error. how can this be sorted
I am using the following statement in a SP.
EXECUTE (' INSERT INTO #OutPut
EXEC h_DailyDividend
@TickerTable = '+@TickerTableName+',
@DateTable = '+@DateTableName+',
@Units = '+@Units
)
AND IN h_DailyDividend I am using the following statement.
EXECUTE (' INSERT INTO #TickerTable
EXEC h_SecMstr_SecMap_TQAExch_Info
@IDList = '+@TickerTable+',
@IsTable = 1,
@Type = 0,
@OutPutFormat = 0,
@VenType = 14 ')
And i am getting the following error.
Message: An INSERT EXEC statement cannot be nested.
Can any body help me out how to solve this problem.
Regards
Sulaman
Hi,
I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties
Result Set : None
ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Thanks in Advance.
Hi
When i use my insert-exec in my proc, say it Y, and the inner proc is X (don't contain an other insert-exec). when i call the Y proc in sql query analyzer the call is executed and it get me back results.
But when I call the Y proc from an ASP page, the execution is aborted, and I don’t know why, I have tested many thinks but it doesn’t work (for exp: the use commit transaction, set ....).
Please could you tell me if i miss some things i should add to my procs for the IIS web server could have right to execute my Y proc
Configuration of my application
- IIS Web server 6.0
- Sql Server 2000 SP 2.0
- TSQL as a sql language (of course)
- ASP as web porgramming language
- The call of the insert exec proc is as following:
o INSERT INTO #TMP_F_B (
o Field1 ,
o Field2,
o Field3 ,
o …,
o )
o EXEC dbo.ps_a5s_rpt_charge700 @util, @annee , @perimetre , @secteur , @poste , @fournisseur , @err , @lib_err
Many thanks
Sincerly
HI,
WELL WE HAVE BEEN TRYING TO AUTOMATE A PROCEDURE OUT HERE,AND WE ARE TRYING TO CONVERT MOST OF THE THINGS INTO PROCEDURES.
BUT WE ARE GETTING A FEW HICCUPS. PLS HELP
THIS IS HOW IT GOES :-
CREATE PROCEDURE MY_PROC1
AS
BEGIN
ST1 .........;
ST2..........;
END
CREATE PROCEDURE MY_PROC2
AS
BEGIN
CREATE TABLE #TMP2
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP2
EXEC MY_PROC1
ST1 .........;
ST2..........;
END
THIS PROCEDURE TOO RUNS WELL ,AFTER TAKING THE DATA FROM THE FIRST PROC IT MANIPUATES THE DATA ACCORDING TO THE CRITERIA SPECIFIED
NO PROBLEM TILL NOW.......
BUT,
CREATE PROCEDURE MY_PROC3
AS
BEGIN
CREATE TABLE #TMP3
(COL1 DATATYPE
COL2 DATATYPE)
INSERT INTO #TMP3
EXEC MY_PROC2
ST1 .........;
ST2..........;
END
THEN IT GIVES AN ERROR AS :-
"An INSERT EXEC statement cannot be nested."
CAN'T WE , FROM A PROCEDURE CALL A PROCEDURE WHICH CALLS A PROCEDURE........
WHAT IS THE NESTING LEVEL OF A PROCEDURE ?
IS THERE ANY WAY AROUND IT OR CAN IT BE DONE BY CHANGING SOME SETTINGS ?
PLS HELP ME OUT IN THIS
THANKS
I'd *like* to execute a sProc within another sProc, because this other one (let's call it dataProc) will provide me with results I need that are calculated based off execing other procs within that proc itself.
I'd like to use this data in a new sProc (we'll call it newProc), but I don't need NEARly all of the columns. The dataProc returns 1 row with 42 columns. I need about 4-5 of those for my newProc.
The dataProc does not have any output variables, and I do not want to change the signature, because several pages/apps use this existing dataProc.
My question:
Is there a way to INSERT INTO table EXEC dataProc
*Without* making a temp table that takes in every single column the dataProc puts out?
Any help is much appreciated.. thanks!
Hi,I have a sproc with 5 params that takes about 40 seconds to return.But when I Create a Temp table and do aInsert Into #tempExec sproc param1, param2, param3, param4, param5it never returns...any ideas?Thanks,Bill
View 1 Replies View RelatedHi,all,When I use following sql, an error occurs:insert into #tmprepEXECUTE proc_stat @start,@endThere is a "select * from #tmp " in stored procedure proc_stat, and theerror message is :Server: Msg 8164, Level 16, State 1, Procedure proc_stat, Line 42An INSERT EXEC statement cannot be nested.What's the metter? Any help is greatly appreciated. Thanks
View 2 Replies View RelatedI have 3 development SQL Servers A, B & C, all running SQL 2000 sp3 and Windows 2003. Servers B & C have a linked server pointing to A, and A has one pointing to B & C. The linkedservers all have RPC , RPC out enabled. I have a stored procedure called test on server A.
Create Proc test
as
Select Top 5 first_name, last_name from people
GO
--code ran on Servers B & C:
create table #tmptbl (nm varchar(100), nm2 varchar(100))
insert into #tmptbl
Exec ServerA.db1.dbo.test
When the Insert....Exec code above is ran from server B it works fine, however when I run it from Server C, I get error 7391
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]
But regular linked server calls (directly to tables) and openquery calls work fine from either server...
Eg
insert into #tmp
Select top 5 first_name, last_name from ServerA.db1.dbo.people
--and this works also
insert into #tmp
Select * from openquery(ServerA, 'Exec db1.dbo.test')
Both servers (B & C) appear to be configured the same, and
I have reconfigured MSDTC on all three boxes through control panel and component manager, have tried using SET xact_abort, SET implicit_transactions, registry hacks (TurnoffRPCsecurity), basically everything listed on Microsoft, and everything I've been able to find in these groups.
If anyone has any ideas, I'd like to hear them.
Tim.