November 14, 2005 at 1:57 pm
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.
November 14, 2005 at 2:28 pm
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
November 15, 2005 at 6:47 am
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.
November 15, 2005 at 10:07 am
Why even use a 'temp' table when you can bcp out the data directly through a view. This trick should work in your situation.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
August 22, 2006 at 9:08 am
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
CREATEPROCEDURE [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
October 25, 2007 at 10:02 pm
So, what is the solution to this problem. We are also facing this.
October 27, 2007 at 7:05 pm
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
January 9, 2008 at 12:18 am
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
February 29, 2008 at 4:03 pm
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
February 29, 2008 at 6:00 pm
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply