July 20, 2010 at 4:43 pm
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[export_file]
AS
BEGIN
declare @EmployeeNumber char(10)
declare @LastName char(20)
declare @FirstName char(20)
DECLARE @MI char(1)
declare @Company decimal(18,0)
declare @ShiftCode Char(10)
-- Create temp table to store data
create table #Extractemployee
( EmployeeNumber char(10) not null , LastName char(20) null , FirstName char(20) null , MI char(1) null , Company decimal(18,0) ,
ShiftCode Char(10) null )
-- Create a new cursor
declare temp_cursor cursor for
select *
from LISI_APPS_A100.dbo.Employee
-- Open the cursor and retrieve the first record
open temp_cursor
fetch next from temp_cursor
into @EmployeeNumber, @LastName,@FirstName,@MI,@Company,@ShiftCode
--- Loop through the recordset and add data to temp table
while @@fetch_status = 0
begin
insert into #Extractemployee
values (@EmployeeNumber, @LastName,@FirstName,@MI,@Company,@ShiftCode)
---Fetch next record from the recordset
fetch next from temp_cursor
into @EmployeeNumber, @LastName,@FirstName,@MI,@Company,@ShiftCode
end
-- Close the cursor and release resources
close temp_cursor
deallocate temp_cursor
begin
--- Create import file using BCP through xp_cmdshell
declare @bcpCommand varchar(8000)
SET @bcpCommand = 'bcp "select * from #Extractemployee" queryout c:\dump.txt -STORDB01'
EXEC LISI_APPS_A100..xp_cmdshell @bcpCommand
END
i getting error
User name not provided, either use -U to provide the user name or use -T for Trusted Connection
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]
NULL
July 20, 2010 at 4:45 pm
You have to tell it how to connect to the database server, either Windows authentication or SQL authentication. With Windows authentication, you simply use -T. With SQL authentication, you use -U and -P.
bcp ... -T
bcp ... -Usomeuser -Psomepwd
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 20, 2010 at 5:19 pm
SET @bcpCommand = 'bcp "select * from #Extractemployee" queryout c:\dump.txt -S TORDB01,-U Kumaravels,-P AppsUser'
i put server name as -S TORDB01
USER NAME AS -U Kumaravels
password as -P AppsUser'
still giving me same error
July 20, 2010 at 5:50 pm
declare @bcpCommand varchar(8000)
SET @bcpCommand = 'bcp "select * from #Extractemployee" queryout c:\dump.txt -c -STORDBDO1 -Ukumaravels -Pasd123
I geting error
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 53
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connecti
ons.
NULL
July 20, 2010 at 11:01 pm
It seems the database server is unable to connect to TORDBDO1. Are you sure that's the proper name? Is there perhaps an instance you should reference? Perhaps try TORDBDO1,PortNumber where PortNumber is the listening port of the SQL database server.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 21, 2010 at 6:48 am
From what I see in your code, you are building a temp table, then opening up a cursor and inserting all columns from all rows from the Employee table into this temp table. Then you want to send this to a csv file.
Well, first off I'd suggest eliminating the temp table and cursor, and just bcp the table itself:
declare @bcpCommand varchar(8000)
SET @bcpCommand = 'bcp LISI_APPS_A100.dbo.Employee out c:\dump.txt -STORDB01 -UMyUser -PMyPassword -c -t,'
EXEC LISI_APPS_A100..xp_cmdshell @bcpCommand
Note the "-t," - that specifies to use a comma as a delimiter between the columns, which you are missing.
As for the command not running: Is your server a named instance? If so, make the server parameter be -STORDB01\YourInstanceName
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 21, 2010 at 2:44 pm
i using bcp command only now.But i need to add header in following code. how use bcp command to add header
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[exportfile]
-- Add the parameters for the stored procedure here
--<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
---<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--select * from LISI_APPS_A100.dbo.Employee
declare @sql varchar(8000)
--select @sql = 'bcp "select * from ##test" queryout "tordb01.HiShear.local\C:\txtfile\test1.csv" -c -t, -T -S' + @@servername
select @sql = 'bcp "select * from LISI_APPS_A100.dbo.Employee" queryout "C:\txtfile\exportfile.txt" -c -T'
--(-c -t| -T -S'+ @@servername)
exec LISI_APPS_A100..xp_cmdshell @sql
END
July 21, 2010 at 2:48 pm
To add a header to bcp, you'd have to use a view instead: http://www.dbforums.com/microsoft-sql-server/989335-bcp-out-headers.html
The view method is not elegant, and makes the code hard to read as you need to convert everything to varchar in order to union them together.
You should instead consider using SSIS/DTS as it's very easy to export data and include headers.
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
July 29, 2010 at 8:03 pm
You might also wish to consider using osql or isql for exporting files with headers.
Check BOL for details and syntax.
_____________
Code for TallyGenerator
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply