Problem With Nested INSERT EXEC

Jun 16, 2006

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

View 5 Replies


ADVERTISEMENT

Problem With Insert Exec In Nested SP

Jul 24, 2006

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 Related

An INSERT EXEC Statement Cannot Be Nested.

Sep 19, 2007

I 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

View 1 Replies View Related

INSERT EXEC Statement Cannot Be Nested

Apr 12, 2006

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

View 13 Replies View Related

Nested Insert Exec Statement

Feb 7, 2008

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

View 4 Replies View Related

ERROR:- An INSERT EXEC Statement Cannot Be Nested.

May 3, 2004

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

View 13 Replies View Related

An INSERT EXEC Statement Cannot Be Nested Error.

Nov 28, 2005

Hi,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 Related

Insert Into...exec-- Help

Jan 21, 2004

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

View 2 Replies View Related

INSERT-EXEC

Apr 19, 2007

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

View 8 Replies View Related

Conditional Insert Without Using Exec

Dec 27, 2007

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

View 3 Replies View Related

Insert Exec Reliability

Oct 23, 2007

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

View 11 Replies View Related

Insert Into Exec Distributedprocname

Nov 9, 2007

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.

View 7 Replies View Related

Deadlocks In Tempdb With Insert Exec SQL 6.5

May 25, 1999

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>

View 2 Replies View Related

After Insert Trigger Exec Sp Problems

Jul 6, 2004

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

View 5 Replies View Related

How To Have More Than One INSERT-EXEC Active At A Time.

Nov 9, 2006

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..

View 5 Replies View Related

Insert Records Using EXEC Syntax In Transact

Sep 14, 2004

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

View 4 Replies View Related

Urgent Please:Insert-Exec Not Working From Asp Page

May 20, 2008

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

View 2 Replies View Related

Need To Pull A Select Few Results From INSERT EXEC

Aug 17, 2006

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!

View 8 Replies View Related

Insert Into #temp Exec Sproc Not Working

Aug 15, 2005

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 Related

Nested Executes In Insert Statements

Mar 19, 2001

Is this a limitation of SQL server. I am running a quite complex sp that I wrote which uses exec to execute an SQL string. Running the SP produces the desired results but if I try to use this sp with an insert statement then I get an error message that exec cannot be nested in an insert statement.....any help would be appreciated

View 2 Replies View Related

INSERT INTO Nested Table Error

Jan 9, 2007

I am running the DMX below and I am getting an error we I go to train my structure.  It is probably something stupid but I do not see the problem.  It seems like the SKIP isn't being recognized but I am not sure.  Here is the error:

INSERT INTO error: The '[MSA].[HospitalID]' nested table key column is not bound to an input rowset column.
DMX:
CREATE MINING STRUCTURE [Hospital_Structure] (
 [HospitalID] LONG KEY,
 [SponsorshipTypeID] LONG DISCRETE,
 [GeographicTypeID] LONG DISCRETE,
 [CaseMixIndex] DOUBLE CONTINUOUS,
 [PercentGovtPayers] DOUBLE CONTINUOUS,
 [TotalNumberInpatientCases] LONG CONTINUOUS,
 [MSA] TABLE (
  [HospitalID] LONG KEY,
  [MSAGroupMember] TEXT DISCRETE
 )
);
GO
 
ALTER MINING STRUCTURE [Hospital_Structure]
ADD MINING MODEL [Hospital_Model] (
 [HospitalID],
 [SponsorshipTypeID],
 [GeographicTypeID],
 [CaseMixIndex],
 [PercentGovtPayers],
 [TotalNumberInpatientCases],
 [MSA] (
  [HospitalID],
  [MSAGroupMember]
 )
) USING Microsoft_Clustering;
GO


INSERT INTO MINING STRUCTURE [Hospital_Structure]
(
 [HospitalID],
 [SponsorshipTypeID],
 [GeographicTypeID],
 [CaseMixIndex],
 [PercentGovtPayers],
 [TotalNumberInpatientCases],
 [MSA] (SKIP, [MSAGroupMember])
)
SHAPE { 
  OPENQUERY([localhost],'
 SELECT
  [HospitalID],
  [SponsorshipTypeID],
  [GeographicTypeID],
  [CaseMixIndex],
  [PercentGovtPayers],
  [TotalNumberInpatientCases]
 FROM
  [dm].[vw_HospitalClustering_Inputs]
 ORDER BY
  [HospitalID]') }
APPEND
(
  {OPENQUERY([localhost],'
 SELECT
  [HospitalID],
  [MSAGroupMember]
 FROM
  [dm].[vw_HospitalClustering_InputsNested]
 ORDER BY
  [HospitalID],
  [MSAGroupMember]')
}
RELATE [HospitalID] TO [HospitalID]
) AS [MSA]

Thanks in advance

View 1 Replies View Related

Insert ... Exec Unable To Begin A Distributed Transaction

Feb 17, 2004

I 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.

View 3 Replies View Related

Insert Query With Nested Select And Parameter

Jul 20, 2005

hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN

View 1 Replies View Related

Create User Only With Permissions, To Select, Insert, Update, Delete, And Exec Sps

May 18, 2006

Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.

Is any easy way to do this?

A database role and then assing that role to the user?

View 4 Replies View Related

Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)

May 14, 2008

Hello,

I have a stored procedure where I run an insert statement. I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string. See examples of what I am talking about in both cases below:

I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------

DECLARE @NewTableNameOut as varchar(100)


Set @NewTableNameOut = 'TableToInsertInto'


EXEC(
Insert Into @NewTableNameOut
Select * From tableToSelectFrom
)

------------------------------------------------------------------------------------

I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.


I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.

------------------------------------------------------------------------------------

DECLARE @NewTableNameOut as varchar(100)


Set @NewTableNameOut = 'TableToInsertInto'


EXEC(
'Insert Into ' + @NewTableNameOut + ' ' +
'Select * From tableToSelectFrom'
)

------------------------------------------------------------------------------------



It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string.

Is this possible at all?

If you need more info please let me know.

View 1 Replies View Related

Attempt To Return Record Set In INSERT...EXEC Statement From ODBC Source(non MSSQL)

Jan 17, 2007

Greeting.

I use OdbcConnection inside clr procedure, for getting data. If I use simple EXEC dbo.clr_proc - all is OK. If I use INSERT...EXEC I recive error message: Distributed transaction enlistment failed.

I set MSDTC security options for No Authentification and Allow inbound and Allow outbound, but it's no use.

Have this problem solution? May be, I must use another method to get my data?



P.S. Linked Servers and OPENQUERY is not applicable. Sybase not describe columns in stored proc result set and one stored proc may return different result set by params.

P.S.S. Sorry for bad english.









View 1 Replies View Related

Using An Exec Query To Insert Pdf, .doc File Into Table From A Dir Path Which Is A Field In Another Table

Aug 5, 2007

I have the following query in sql 2005:


PROCEDURE [dbo].[uspInsert_Blob] (

@fName varchar(60),

@fType char(5),

@fID numeric(18, 0),

@bID char(3),

@fPath nvarchar(60)

)



as

DECLARE @QUERY VARCHAR(2000)

SET @QUERY = "INSERT INTO tblDocTable(FileName, FileType, ImportExportID, BuildingID, Document)

SELECT '"+@fName+"' AS FileName, '"+@fType+"' AS FileType, " + cast(@fID as nvarchar(18)) + " as ImportExportID, '"+@bID+"' AS BuildingID, * FROM OPENROWSET( BULK '" +@fPath+"' ,SINGLE_BLOB)

AS Document"

EXEC (@QUERY)

This puts some values including a pdf or .doc file into a table, tblDocTable.

Is it possible to change this so that I can get the values from a table rather than as parameters. The Query would be in the form of: insert into tblDocTable (a, b, c, d) select a,b,c,d from tblimportExport.

tblImportExport has the path for the document (DocPath) so I would subsitute that field, ie. DocPath, for the @fPath variable.

Otherwise I can see only doing a Fetch next from tblIportExport where I would put every field into a variable and then run this exec query on these. Thus looping thru every row in tblImportExport.

Any ideas how to do this?

View 1 Replies View Related

Can Exec Select But Can't Exec Sp

Oct 31, 2007

I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).

I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.

These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.

Can some one explain why?

View 5 Replies View Related

Using Exec

Feb 29, 2000

When I use EXEC in a stored procedure ( after building complex option logic) it produces an returns an error of 'Access denied' on the underlying tables.
All objects are dbo owned and execute permission has been given to all users.
Can ant one help?
Rob

View 1 Replies View Related

Exec

Jul 22, 2003

When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"...
Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?

Thanks

View 4 Replies View Related

Exec In My Sp

Nov 1, 2007

I am trying to create a awkward sp, just need to know if i can do this somehow, here i piece of the code...

create procedure IM_SP_TrPreProc /*@TableName Varchar(255),*/ @SystemFileName Varchar(255)

---------------------------------------------
--Param1 = Tablename
--Param2 = Systemfilename
---------------------------------------------

as

declare @TableName Varchar(255);--Just For Testing---DELETE!!
declare @Filename varchar(255); --Store Distinct filename
declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName
declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units
declare @SumValue Varchar(255);
Set @TableName = 'TrDs01' -- Testing Only--DELETE!!

------------------------Set Statements using @TableName Var------------------------------------------

Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName
Set @SumUnits = 'select sum(Units) from ' + @TableName
Set @SumValue = 'Select sum(Value) from ' + @TableName

------------------------------------------------------------------------------------------------------

Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate,
UnitsSum,ValueSum,RecordCount)

Select(@Filename),(exec(DSNo)), ................

Just the Bold and underlined bit "exec(DSNo)"..... is this doable in some way? can i use exec to retrieve the value to insert to data supplier. As far as i know i have to do it like this because im using a variable as the table name...

View 2 Replies View Related

Exec Sql

Apr 28, 2006

I need help understanding the syntax of the "exec sql" statement.

i am looking at code that build an sql string such as

sql="exec SOMETHING Session("id")"

or something like that.

then, there is

conn.execute(sql)

My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.

i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.

in particular, the code i am examining is

sql="exec SurveyDelete "&"'" & Session("StudentID") & " ' "
conn.execute(sql)

i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?

View 2 Replies View Related

MDX And EXEC

Feb 17, 2006

Hi,

Can I execute my MDX statement as in the exhibit format!



DECLARE @test VARCHAR(MAX)

SET @test = 'WITH test AS (SELECT * FROM merchants)'

EXEC(@test)

SELECT * FROM test



If I don't use that dynamice sql statement, everything work fine.



Thanks,

Myo

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved