x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Export out entire database

For some reason this seems to be the season for people needing to dump a database to text....

 

Well Here's one way...should rewrite it to use INFORMATION_SCHEMA, but it works well enough..

If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_bcp_out_database]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_bcp_out_database]
GO

CREATE PROC isp_bcp_out_database
   @dbName sysname
 , @fp varchar(255)
 , @User varchar(255)
 , @Pwd varchar(255)
AS
/*
 EXEC isp_bcp_out_database
    'Northwind'
  , 'd:\Data\Northwind\'
  , 'sa'
  , ''

*/

SET NOCOUNT ON

DECLARE bcpout CURSOR FOR
 SELECT  -- 'EXEC Master..xp_cmdshell ' +
--    '"D:\MSSQL7\Binn\bcp.exe ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
    'bcp ' + db_Name() + '.[' + TABLE_SCHEMA + '].[' + TABLE_NAME+'] '
  + 'out ' + @fp + '\DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.dat  '
  +
'-S'+@@SERVERNAME+' -U'+@User+' -P'+@Pwd+' '
  +
'-f'+@fp+'FORMAT\'+TABLE_SCHEMA +'_'+REPLACE(TABLE_NAME,' ','_')+'.fmt '
  + ' > ' + @fp + 'DATA\'+TABLE_SCHEMA +'_'+ REPLACE(TABLE_NAME,' ','_') + '.log'
  -- + ', no_output' AS CMD
   FROM    INFORMATION_SCHEMA.Tables
  WHERE    TABLE_TYPE = 'BASE TABLE'
      ORDER BY TABLE_SCHEMA, TABLE_NAME

DECLARE @CMD varchar(8000)

--create table a (id int identity(1,1), Add_Dt datetime DEFAULT GetDate(), s varchar(1000))
-- DROP TABLE a
OPEN bcpout

FETCH NEXT FROM bcpout INTO @CMD

WHILE @@FETCH_STATUS = 0
 BEGIN
  SELECT @CMD
  SELECT @CMD = 'ECHO ' + @CMD + ' > ' + @fp + '\bcpout.bat'
  EXEC master..xp_cmdshell @CMD
  SELECT @CMD = @fp + '\bcpout.bat'
  SELECT @CMD
  insert a (s)
  exec master..xp_cmdshell @cmd


  FETCH NEXT FROM bcpout INTO @CMD
 END

CLOSE bcpout
DEALLOCATE bcpout

 select id, ouputtmp = s from a

SET NOCOUNT OFF

drop table emp2

Print | posted on Thursday, June 17, 2004 2:16 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Export out entire database

But it is using the INFORMATION_SCHEMA? Did you go back and modify it?
6/17/2004 7:01 PM | Tara
Gravatar

# re: Export out entire database

Damn: I must've done it in my sleep...

Thanks for looking Tara...Great script the other day...

I should start a folder called SQL Toolbox...

6/18/2004 9:38 AM | Brett
Gravatar

# re: Export out entire database

I've got a Toolbox database on my laptop where I store everything.
6/18/2004 1:58 PM | Tara
Gravatar

# re: Export out entire database

Yeah, me to...but if we put stuff here, I can go anywhere...

6/18/2004 2:10 PM | Brett
Gravatar

# re: Export out entire database

I tried running this and came up with the following error:

Error = [Microsoft][ODBC SQL Server Driver]Unable to open BCP host data-file


What do I need to do/change?
7/1/2004 2:26 PM | Anne
Gravatar

# re: Export out entire database

Anne,

You have to understand that this is running under the context of the id the Agent runs on.

In other words, on the server is where the mapping must be.

bcp can't find the plave to write this file. The server must have a D: drive or a D mapping with a folder called \Data under it, otherwise that's the message you will receive.

Send me an Email through contact and I'll try and help you more...or try SQLTeam!


7/1/2004 2:32 PM | Brett
Gravatar

# re: Export single table data into Excel

I am trying to export one table data into Excel through bcp command. But it is saying "Unable to open bcp host data files".

can u help me in this regard

my mail id is
sanjeev@consultesoft.com
11/3/2005 1:54 AM | Sanjeev
Gravatar

# re: Export out entire database

I have a question that bcp can not the remote server drives but local drives . So, bcp can only out files in local drives. Right???
10/12/2006 12:45 PM | baron
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET