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?
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?
I am having a weird problem occur when trying to insert missing records based on an INSERT INTO...SELECT statement.
Basically, I am populating a temporary table with data, then inserting into a main table those records from the temporary table that aren't in the main table. In addition, I am updating those records that are in the main table.
When I call the following SQL, all of the records from the temporary table are inserted even though those records already exist. Bizzarrely the update statement works perfectly.
INSERT INTO AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName) SELECT Identifier, AffiliateID, 1, AffiliateCategoryName, NetworkCategoryName FROM #TempTradeDoublerCategories WHERE #TempTradeDoublerCategories.AffiliateCategoryName NOT IN ( SELECT AffiliateCategoryName FROM AffiliateCategories )
I am not sure why it is doing this and it's driving me crazy. If anyone has any ideas why this is happening, or knows of a better way to accomplish this then please let me know.
Are there some issues with string comparisons going on here?
The table that populates the temp table is defined as:
INSERT INTO #TempTradeDoublerCategories (AffiliateCategoryName) SELECT DISTINCT CAST(merchantName AS VARCHAR(255)) AS MerchantName FROM Staging_TradeDoubler_Categories ORDER BY MerchantName
UPDATE #TempTradeDoublerCategories SET #TempTradeDoublerCategories.Identifier = Staging_TradeDoubler_Categories.id, #TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Categories.AffiliateID, #TempTradeDoublerCategories.NetworkCategoryName = Staging_TradeDoubler_Categories.[name] FROM Staging_TradeDoubler_Categories WHERE Staging_TradeDoubler_Categories.merchantName = #TempTradeDoublerCategories.AffiliateCategoryName
UPDATE #TempTradeDoublerCategories SET #TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.AffiliateID FROM Staging_TradeDoubler_Affiliates WHERE #TempTradeDoublerCategories.AffiliateID = Staging_TradeDoubler_Affiliates.TradeDoublerAffiliateID
Print 'Inserting records'
INSERT INTO AffiliateCategories (Identifier, AffiliateID, NetworkID, AffiliateCategoryName, NetworkCategoryName) SELECT Identifier, AffiliateID, 1, AffiliateCategoryName, NetworkCategoryName FROM #TempTradeDoublerCategories WHERE #TempTradeDoublerCategories.AffiliateCategoryName NOT IN ( SELECT AffiliateCategoryName FROM AffiliateCategories )
Print 'Updating records'
UPDATE AffiliateCategories SET AffiliateCategories.AffiliateCategoryName = #TempTradeDoublerCategories.AffiliateCategoryName, AffiliateCategories.NetworkCategoryName = #TempTradeDoublerCategories.NetworkCategoryName, DateModified = GETDATE() FROM #TempTradeDoublerCategories WHERE #TempTradeDoublerCategories.AffiliateCategoryName IN ( SELECT AffiliateCategories.AffiliateCategoryName FROM AffiliateCategories )
PRINT 'Dropping table'
IF OBJECT_ID('tempdb..#TempTradeDoublerCategories') IS NOT NULL DROP TABLE #TempTradeDoublerCategories
I need to insert data into a table based on the results returned by a select statement. Basically, the select statement below gives me a list of all the work orders created in the last hour.
select worknumber from worksorderhdr where date_created > DATEADD(HOUR, -1, GETDATE())
This might return anywhere between 5 and 50 records each time. What I then need to do is use each of the work numbers returned to create a record in the spec_checklist_remind table. The other details in the insert statement will be the same for each insert, it's just the worknumber from the select statement that needs to be added to the insert where the ?? are below:
I'm trying to fill a temp table whose columns are the same as another table plus it has one more column. The temp table's contents are those rows in the other table that meet a particular condition plus another column that is the name of the table that is the source for the rows being added.
Example: 'permTable' has col1 and col2. The data in these two rows plus the name of the table from which it came ('permTable' in this example) are to be added to #temp.
Data in permTable col1 col2 11, 12 21, 22
Data in #temp after permTable's filtered contents have been added
Hello, I have been working around this issue, but couldn't yet find any solution.I have a stored procedure that calls a method to do a certain repetitive work.In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.for example, there is an input for a function called "Id"Inside the function, if Id = 111I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.Now, inside the function, I need to return a value by executing the above @SQLString as follows:EXEC @SQLStringWhen I need is something likeEXEC @SQLString, @Total OutputReturn (@Total)Are there any ideas ?regards
I setup the query as a stored procedure and changed the SSIS accordingly. Running the sp from within SSMS was fine but the task still returned the wierd results.
This is completely unexpected and giving us headaches. It doesn't even look like well-formed XML to my eyes and won't display in IE without showing problems! Where are the eroneous /'s and ROOT nodes coming from?
I can't imagine we are the only people to have run into this and I'm sure we aren't doing something quite right - just stuck as to what we're doing wrong.
Hopefully I've provided enough info. If not just ask.
We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default:
SQL_Latin1_General_CP1_CI_AS):
USE AdventureWorks2012 ; GO
--Returns 5 records SELECT pp.Name FROM Production.Product AS pp WHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ; GO
The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".
Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.
USE AdventureWorks2012 ; GO
--Returns 8 records SELECT pp.Name FROM Production.Product AS pp WHERE pp.Name >= N'Short' AND pp.Name <= N'Sporu' ; GO
What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
Hello, in my scenerio i am pulling a table from msde to my ppc and then i want to insert a new record to my local table but when i try to do that i faced with an index paradox forexample: if i pulled a table with record index 1 then i am triying to insert a new record at local but at local it tries to insert index 1 again so i can't finish my operation. My primariy index is seed 1 also. How can i achieve my goal?
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'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.
The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.
I have my database: "RequestTrack" My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button: txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
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')
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.
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.
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.
I have the following table called BADSANTA: varchar(30) datetime NAME WHENBAD OJ Simpson 2007-1-12 xx:xx:xx:xxx OJ Simpson 2007-4-2 xx:xx:xx:xxx Monica Lewinsky 2006-7-4 xx:xx:xx:xxx Monica Lewinsky 2006-10-31 xx:xx:xx:xxx Bill Clinton 2006-7-4 xx:xx:xx:xxx Bill Clinton 2006-10-31 xx:xx:xx:xxx Bart Simpson 2006-11-2 xx:xx:xx:xxx Bart Simpson 2006-2-25 xx:xx:xx:xxx Bart Simpson 2005-07-27 xx:xx:xx:xxx
I want the records of the persons latest WHENBAD date. It should return one recordset for each person.
I am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints' FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID WHERE M.EMAIL = @UserEmail
and then i wanted to exec this sp in another procedure i would have
exec my_insert( 1_value, 2_value, 3_value )
My question is how could i exec this will the result set of a select.... something like this
exec my_insert (select 1_value, 2_value, 3_value from another_table).
I know i could have this in an insert result type statement ie...
insert into dest_table (select 1_value, 2_value, 3_value from another_table)
but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement
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>
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?
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
-- 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
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 .
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
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.
