Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

bcp temp table within a SP Expand / Collapse
Author
Message
Posted Monday, November 14, 2005 1:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 12, 2012 10:41 PM
Points: 199, Visits: 98

I need bcp out/in chunck of data within a SP. The logic is like

1. create local temp table

2. select data into the above local temp table

3. bcp out the data from the local temp table

 

Is it possible to accomplish within a SP. It seems when bcp, it establish a new session that can't access that local temp table.

 

Any input will be sppreciated.

 

Post #236983
Posted Monday, November 14, 2005 2:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, May 12, 2014 12:57 PM
Points: 2,285, Visits: 4,225
Local temporary tables are visible only to the creating session but global temporary tables are visible to all sessions.

To create a global temporary table, prefix the table name with two number signs such as

Create table ##table_name

You need to be carefull with the life span of Global temporary tables, as they are automatically dropped when the session that created the table ends AND every other connection stops its reference.

This might be easier just to create a permanent table in tempdb and then drop it at the end of the process.


SQL = Scarcely Qualifies as a Language
Post #236992
Posted Tuesday, November 15, 2005 6:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 12, 2012 10:41 PM
Points: 199, Visits: 98

Global temp table won't work for me in my situation. As this SP need create/run parallel in multiple databases in a huge SQL server (SAN) for various clients.

 

Post #237154
Posted Tuesday, November 15, 2005 10:07 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:54 AM
Points: 3,188, Visits: 2,280
Why even use a 'temp' table when you can bcp out the data directly through a view. This trick should work in your situation.



Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #237256
Posted Tuesday, August 22, 2006 9:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 2, 2010 3:21 PM
Points: 2, Visits: 33
This is an issue I have also run across in SQL 2005. It works fine using BCP from SQL 2000 but BCP from 2005 seems to handle the temporary table scope differently. Just as an example BCPing out the following:

Create a stored procedure

CREATE PROCEDURE [dbo].[gsp_foo]
AS
BEGIN
CREATE TABLE #dog(Col int)
INSERT INTO #dog VALUES(1)
SELECT * FROM #dog
DROP TABLE #dog
END

BCP Queryout from the procedure in 2005
c:\>BCP "EXEC dbc.gsp_foo" queryout "text.txt" -SRemoteServer -T

Results
SQLState = 42S02, NativeError = 208
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '#dog'.

This code works just fine if you run the BCP from a SQL 2000 server.

Puzzled,
Michael Mockus
Post #303224
Posted Thursday, October 25, 2007 10:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 1, 2008 1:40 PM
Points: 2, Visits: 12
So, what is the solution to this problem. We are also facing this.
Post #415205
Posted Saturday, October 27, 2007 7:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 1, 2008 1:40 PM
Points: 2, Visits: 12
The following approach worked for us. Any drawback in this? Added 'fmtonly off' for BCP.

create procedure test1
as
set nocount on
IF(1 = 0)
BEGIN
select name = '' , id = ''
set fmtonly off
return

end
select top 10 name , id into #temp1 from sysobjects

select * from #temp1
Post #415749
Posted Wednesday, January 9, 2008 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 5, 2008 6:42 AM
Points: 4, Visits: 29
Hi, maybe this can help you. i did a similiar thing to what you are trying, just adapt your temp table and select query for the BCP...

CREATE PROCEDURE [dbo].[PROC_NAME]

AS
BEGIN

declare @CLOCKINGS varchar(100)
declare @LogID varchar(100)
declare @clock varchar (255)
DECLARE @bcpCommand varchar(2000)
declare @error varchar (100)


-- Create temp table to store data

create table Temp_Table (CLOCK varchar(255),
[LogID] [nvarchar](32) NULL,)

-- Create a new cursor

declare temp_cursor cursor for
select ssMainData.dbo.CLOCKINGS.[FILE], ssMainData.dbo.CLOCKINGS.LogID
from ssMainData.dbo.CLOCKINGS

-- Open the cursor and retrieve the first record

open temp_cursor
fetch next from temp_cursor
into @CLOCKINGS, @LogID

--- Loop through the recordset and add data to temp table

while @@fetch_status = 0
begin
insert into Temp_Table
values (@CLOCKINGS, @LogID)

---Fetch next record from the recordset

fetch next from temp_cursor
into @CLOCKINGS, @LogID

end

-- Close the cursor and release resources


close temp_cursor
deallocate temp_cursor

begin

--- Create import file using BCP through xp_cmdshell

SET @bcpCommand = 'bcp "select ssMainData..TEMP_TABLE.CLOCK FROM ssMainData..TEMP_TABLE" queryout c:\dump.txt -T -S CVE\CVE -c -t'

EXEC ssMainData..xp_cmdshell @bcpCommand


END

--- Drop temp_table

begin

drop table ssmaindata..temp_table

end


end

hope it helps...

Clive
Post #440440
Posted Friday, February 29, 2008 4:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 29, 2008 6:44 PM
Points: 1, Visits: 2
Hi

I need to know that if bcp import only text archive (*.txt)
I neen to know if bcp imports *.DBF (Foxpro)

Thank you very much

Elena Susy Moya Huamanchumo
email: elenamoya@pexport.com.pe
elenina_blue@hotmail.com
moya.es@pucp.edu.pe
Post #462725
Posted Friday, February 29, 2008 6:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:06 PM
Points: 36,711, Visits: 31,160
BCP will not import .DBF files. You'll need to use DTS for that. Or, you can use a linked server or OpenRowSet with the .DBF provider to link directly to the files. I've not done that to .DBF files before so I can't walk you through it.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #462745
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse