May 23, 2012 at 3:47 pm
OK, I've been poking at this for days and can't figure out why its not working.
SQL Server 2008 R2
I have a number of examples where I use Insert Into with an EXEC, e.g.
INSERT INTO @jobs
EXEC master.dbo.xp_cmdshell @root_dir
This works perfectly. Rows are inserted into @jobs. Never fails.
However, when trying to do this with a particular procedure that I've created, I never get records returned. Nor do I get an error message. If I just call it, it returns two rows, never fails, no errors.
EXEC Work.dbo.P_UTIL_LIST_FILES
@root_dir = 'C:\DataFiles' ,
@filename_filter = 'AccountFile'
This, however, inserts zero rows in #Files. No error, just no data.
INSERT INTO #files (FileFullName, FileSize, DateCreated)
EXEC Work.dbo.P_UTIL_LIST_FILES
@root_dir = 'C:\DataFiles' ,
@filename_filter = 'AccountFile'
The return from Work.dbo.P_UTIL_LIST_FILES
is a select * from the table below:
DECLARE @files TABLE
( FileFullName varchar(200),
FileSize varchar (20),
DateCreated datetime)
The destination table in the proc I'm executing the Exec Into is created as follows:
CREATE TABLE #files
( FileFullName varchar(200),
FileSize varchar (20),
DateCreated datetime)
As you can see, column names and data types match. But clearly something is going wrong since I get no data inserted.
Anyone bumped into this recently and have an idea of why this isn't working?
Many thanks!!!
May 24, 2012 at 12:28 am
At first I thought you might be getting an error message from having a nested EXEC in your stored procedure:
Msg 8164, Level 16, State 1, Procedure p_Test, Line 5
An INSERT EXEC statement cannot be nested.
But if you aren't getting an error message, I'm wondering if you're inadvertently calling a different database or schema for a stored procedure which isn't returning any data?
When I run the following, I get 3 rows returned from #files:
SET NOCOUNT ON
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'p_Get_Files')
DROP PROCEDURE p_Get_Files
GO
CREATE PROCEDURE p_Get_Files AS
BEGIN
DECLARE @files TABLE (filename VARCHAR(50))
INSERT @files VALUES ('file1'),('file2'),('file3')
SELECT filename
FROM @files
END
GO
IF OBJECT_ID('tempdb..#files') IS NOT NULL
DROP TABLE #files
GO
CREATE TABLE #files (filename VARCHAR(50))
INSERT #files
EXEC p_Get_Files
SELECT * FROM #files
If you post the source code for the stored procedure, it will be easier to duplicate the problem...
May 24, 2012 at 3:33 am
This was removed by the editor as SPAM
May 24, 2012 at 7:26 am
If you could provide a complete listing of the code in the procedure that might be informative. Also, as someone else suggested, is everything in the dbo schema?
The probability of survival is inversely proportional to the angle of arrival.
May 24, 2012 at 7:44 am
Thanks, guys.
And first to the important stuff: sturner... Does this mean you are trying to slow down or that you are trying to see what's in front of you? Either way, I like it and it does resemble my life at work as well. Practically, those would be the main reasons for s-turning when I'm flying my Bücker Jungmann.
I didn't post the full code since it is a bit long. However, it does contain another Exec Into statement. I was unaware that 'no nesting of EXEC INTO' extended to nesting in the proc called by the EXEC INTO. If that is the case, then that would explain the problem.
Testing. Back with you shortly.
May 24, 2012 at 7:54 am
And we have a winner!!!! 8kb is scoring high on the psychic developer scale.
It appears that the problem was due to the procedure being called in the exec into, Work.dbo.P_UTIL_LIST_FILES, having an exec into within it. Dang nabbit!
Holy cow! That one makes my head hurt. Sure would be nice if they could bubble that error up to me rather than have me scratching my head for days. I coded around the issue but it was making me itchy.
Genius, you are a genius!
Thanks!
May 24, 2012 at 8:00 am
Fly Girl (5/24/2012)
And first to the important stuff: sturner... Does this mean you are trying to slow down or that you are trying to see what's in front of you? Either way, I like it and it does resemble my life at work as well. Practically, those would be the main reasons for s-turning when I'm flying my Bücker Jungmann.
Believe it or not I had to google Bücker Jungmann because I am unfamiliar with this AC. Now that I have I am totally jealous. The coolest aerobatic thing I've flown is a Boeng Stearman but that Bücker must be sweet. What's the stall speed and is yours modified in any way?
I used to own a Piper Comanche 250 which I loved and flew all over the southeast US in. Sure hated to sell her but then life sort of got in the way. :crying:
The probability of survival is inversely proportional to the angle of arrival.
May 24, 2012 at 2:47 pm
The Bücker is a load of fun. There is pretty much nothing practical you can do in it: it sucks for transportation (2 hrs of fuel max, little cross wind capability, wooden prop so it can't do rain and you're sitting out in the weather going 100 miles an hour); only holds two people and a hanky.
However for pure fun flying it's the ticket. Check youtube account 628bj for evidence.
Mine is modified. It was built by the Spanish Air Force in 1963 (yep, they were really up with the times using a 1934 design to train pilots in the 1960s). My husband and I flew it for ~4 years on an original engine but when that one ate a valve we tried to rebuild one from our 5 spares and eventually caved and hung a Lycoming O320 on it.
Life does get in the way, but I hope it clears out for you soon!
May 24, 2012 at 3:10 pm
Fly Girl (5/24/2012)
The Bücker is a load of fun. There is pretty much nothing practical you can do in it: it sucks for transportation (2 hrs of fuel max, little cross wind capability, wooden prop so it can't do rain and you're sitting out in the weather going 100 miles an hour); only holds two people and a hanky.However for pure fun flying it's the ticket. Check youtube account 628bj for evidence.
Mine is modified. It was built by the Spanish Air Force in 1963 (yep, they were really up with the times using a 1934 design to train pilots in the 1960s). My husband and I flew it for ~4 years on an original engine but when that one ate a valve we tried to rebuild one from our 5 spares and eventually caved and hung a Lycoming O320 on it.
Life does get in the way, but I hope it clears out for you soon!
I grew up flying a 1948 Aeronca Super Chief with my dad. He bought it after it was flipped over in a windstorm in the early 70s. We rebuilt it together from the ground up and flew it for many years. Wanna talk about slow...ground cruising speed ~50mph.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2012 at 4:34 pm
The first plane I 'owned' was technically a 1/5 share in an Aeronca Champ. There is still a very soft spot in my heart for Aeroncas--even if the cars on the highway are going faster than I am!
May 25, 2012 at 6:36 am
Its all about the journey, not the destination. That's the one downside of flying fast: Its cool but its over all too quickly.
The probability of survival is inversely proportional to the angle of arrival.
May 25, 2012 at 7:28 am
I can remember on many occasions when it was windy we would fly backwards. Just pick a spot and point straight into the wind, slow down to just above stall speed and the wind would literally have us flying in reverse. Never got my own ticket but have always wanted to...maybe someday.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 7:38 am
Sean Lange (5/25/2012)
I can remember on many occasions when it was windy we would fly backwards. Just pick a spot and point straight into the wind, slow down to just above stall speed and the wind would literally have us flying in reverse. Never got my own ticket but have always wanted to...maybe someday.
That's one way to build time. 🙂 I have never flown backwards, but I have "hovered" lol.
The probability of survival is inversely proportional to the angle of arrival.
May 25, 2012 at 7:42 am
sturner (5/25/2012)
Sean Lange (5/25/2012)
I can remember on many occasions when it was windy we would fly backwards. Just pick a spot and point straight into the wind, slow down to just above stall speed and the wind would literally have us flying in reverse. Never got my own ticket but have always wanted to...maybe someday.That's one way to build time. 🙂 I have never flown backwards, but I have "hovered" lol.
We are in Kansas so strong winds are plentiful. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 25, 2012 at 8:12 am
Hey, guys, the fun of flying is still there. Aeronca Chiefs are a bargain. If you do this kind of work, it should be accessible to you. Life is short: GO FLY!!!!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply