SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


bcp temp table within a SP


bcp temp table within a SP

Author
Message
John Chiu
John Chiu
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 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.


Carl Federl
Carl Federl
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6912 Visits: 4352
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
John Chiu
John Chiu
SSC-Addicted
SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)SSC-Addicted (465 reputation)

Group: General Forum Members
Points: 465 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.


Rudyx - the Doctor
Rudyx - the Doctor
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10988 Visits: 2503
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."
Michael Mockus
Michael Mockus
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 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
erbabu
erbabu
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 12
So, what is the solution to this problem. We are also facing this.
erbabu
erbabu
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 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
clive van eeden
clive van eeden
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 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
elenina_blue
elenina_blue
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216124 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search