I use this utility procedure to create files from tables (or views)
Can be called from any DB due to the sp_ name and executes in the
current DB
Dano Smith
USE Master
GO
IF OBJECT_ID('dbo.sp_CreateFileFromTable') IS NOT NULL DROP PROCEDURE
dbo.sp_CreateFileFromTable
GO
CREATE PROCEDURE dbo.sp_CreateFileFromTable
@DatabaseName VARCHAR(128) = NULL,
@TableName VARCHAR(128),
@Path VARCHAR(1000),
@FileName VARCHAR(100) = NULL,
@FieldTerminator VARCHAR(10) = ',',
@Debug BIT = 0,
@Success BIT = 0 OUTPUT,
@ErrorMessage VARCHAR(255) = '' OUTPUT
/*
Procedure: dbo.sp_CreateFileFromTable
Creates character based file from a table or view (owned by "DBO") in
specified location with specified name
If field terminator is empty string, none is used.
Row terminator is always "\r\n" or Char(13) + char(10) AKA "CRLF"
LastChange By Explanation
---------------------------------------------------------------
Dano - created
---------------------------------------------------------------
EXEC DBA.dbo.sp_CreateFileFromTable
@Path = '\\sqlinterface\sqlinterface\',
@FileName = 'SQLAlias.txt',
@FieldTerminator = ',',
@TableName = 'SQLAlias'
,@Debug = 0
--SELECT * FROM vwNewValidDealers
SP_HELP
*/
AS
SET NOCOUNT ON
IF ( @DatabaseName IS NULL) BEGIN
SET @DatabaseName = DB_NAME(DB_ID())
END
DECLARE
@Error INT,
@RowCount INT,
@UserName SYSNAME,
@SQL VARCHAR(4000)
IF ( @FileName IS NULL ) BEGIN
SET @FileName = @@SERVERNAME + '_' + @TableName + '.txt'
END
SET @UserName = USER_NAME()
IF ( LEFT(@TableName, 2) = '##' ) BEGIN
SET @SQL = 'master..xp_cmdshell ''bcp ' + @TableName
+ ' OUT ' + @Path + @FileName + ' -t"' + @FieldTerminator + '" -S '
+ @@SERVERNAME + ' -T -c' + ''''
END ELSE BEGIN
SET @SQL = 'master..xp_cmdshell ''bcp ' + @DatabaseName + '.dbo.' +
@TableName
+ ' OUT ' + @Path + @FileName + ' -t"' + @FieldTerminator + '" -S '
+ @@SERVERNAME + ' -T -c' + ''''
END
IF ( @Debug = 1 ) BEGIN
SELECT @SQL AS SQLToExecute
END ELSE BEGIN
EXEC (@SQL)
END
GO
-----Original Message-----
From: ryan
[mailto:mssqldba-ezmlmshield-x23785170.[Email address protected]
Sent: Wednesday, March 28, 2007 2:58 PM
To: LazyDBA Discussion
Subject: Exporting Data
Importance: Low
Hi all -
Please excuse my ignorance. I am very new to this db stuff and obviously
I don't know what I am doing, but I am glad to have found this list
because I have learned a lot from just lurking over the posts.
I have a question I am hoping someone can either help me with or tell me
it isn't possible. I've done a bit of searching and think the answer
will be the later, but here it goes:
Is there a way to write a query and have it export the result to a csv
file (or something similar)? I am able to do this with no problems in
MySQL using 'into outfile' - but I can't seem to find a way with MSSQL
2000.
Any ideas/suggestions?
Thanks,
Ryan
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page