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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 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
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14666 Visits: 4355
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1093 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
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23949 Visits: 2527
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-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 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 Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

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

Group: General Forum Members
Points: 92 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
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 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 (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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 (505K reputation)SSC Guru (505K reputation)SSC Guru (505K reputation)SSC Guru (505K reputation)SSC Guru (505K reputation)SSC Guru (505K reputation)SSC Guru (505K reputation)SSC Guru (505K reputation)

Group: General Forum Members
Points: 505445 Visits: 44253
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