osql ou bcp pour créer fichier .csv ???

il vaut mieux créer une fichier de format pour ce faire.

dans l'aie en ligne :
"Utilisation de fichiers de format"
fichiers d'extension .fmt.

A +

--
Frédéric BROUARD, MVP SQL Server. Expert SQL / spécialiste Delphi, web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
************************ www.datasapiens.com *************************

Oliv' a écrit:

Bonjour,
je recadre un précédent post.

Comment parametrer osql ou bcp pour créer un fichier .csv (delimiteur ;)
compatible avec excel.
Il faut qu'il n'y ai pas d'espace qui viennent compléter les données

deux exemples qui ne fonctionnent pas vraiment comme voulu :

à partir de l'invite de commande :
osql -S SERVEUR -E -s";" -w 20000 -Q"select * from sysusers" -o
c:temptestosql.csv -R

--> résultat des espaces dans 'name' ou encore dans "sid" et taille 95ko

bcp "select * from sysusers" queryout "c:temptestbcp.csv" -c -S
serv_gsr -T

--> résultat pas d'espace, pas de ligne de titre et séparateur ;
taille 2ko


je lance cela à partir d'uine procédure stockée en faisant appel à
xp_cmdshell

S'il existe une autre méthode à partir d'une procédure stockée je suis
preneur.

Oliv'

Bonjour à tous voici ce que je suis arrivé à faire à partir d'une ps
existante .
Cela permet d'envoyer un mail avec un fichier au format .csv dans lequel on
a le résultat d'une requête.

certains print peuvent être supprimés mais ils servent pour débogage.

Oliv'

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

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO

Create Procedure dbo.sp_SQLSMTPMail_csv
@vcTo varchar(2048) = null,
@vcBody varchar(8000) = '',
@vcSubject varchar(255) = null,
@vcAttachments varchar(1024) = null,
@vcQuery varchar(8000) = null,
@vcQueryTitre varchar(255) ='',
@vcFrom varchar(128) = null,
@vcCC varchar(2048) = '',
@vcBCC varchar(2048) = '',
@vcSMTPServer varchar(255) = '', -- put local network smtp server
name here
@cSendUsing char(1) = '2',
@vcPort varchar(3) = '25',
@cAuthenticate char(1) = '0',
@vcDSNOptions varchar(2) = '0',
@vcTimeout varchar(2) = '30',
@vcSenderName varchar(128) = null,
@vcServerName sysname = null


As

/*******************************************************************/
--Name : sp_SQLSMTPMail_csv
--Server : Generic
--Description : SQL smtp e-mail using CDOSYS, OLE Automation and a
-- network smtp server; For SQL Servers running on
-- windows 2000.
--
--Note : Be sure to set the default for @vcSMTPServer above to
-- the company network smtp server or you will have to
-- pass it in each time.
--
--Comments : Getting the network SMTP configured to work properly
-- may require engaging your company network or
-- server people who deal with the netowrk SMTP server.
-- Some errors that the stored proc returns relate to
-- incorrect permissions for the various SQL Servers to
-- use the SMTP relay server to bouce out going mail.
-- Without proper permissions the SQL server appears as
-- a spammer to the local SMTP network server.
--
--Parameters : See the 'Syntax' Print statements below or call the
-- sp with '?' as the first input.
--
--Date : 08/22/2001
--Author : Clinton Herring
--modifiee : Olivier catteau 8/12/2004
--History : modification pour création fichier .csv
/*******************************************************************/

Set nocount on

-- Determine if the user requested syntax.
If @vcTo = '?'
Begin
Print 'Syntax for sp_SQLSMTPMail_csv (based on CDOSYS):'
Print 'Exec master.dbo.sp_SQLSMTPMail_csv'
Print ' @vcTo (varchar(2048)) - Recipient e-mail address
list separating each with a '';'' '
Print ' or a '',''. Use a ''?''
to return the syntax.'
Print ' @vcBody (varchar(8000)) - Text body; use embedded
char(13) + char(10)'
Print ' for carriage returns.
The default is nothing'
Print ' @vcSubject (varchar(255))) - E-mail subject. The
default is a message from'
Print ' @@servername.'
Print ' @vcAttachments (varchar(1024)) - Attachment list
separating each with a '';''.'
Print ' The default is no
attachments.'
Print ' @vcQuery (varchar(8000)) - In-line query or a query
file path; do not '
Print ' use double quotes within
the query.'
Print ' @vcQueryTitre varchar(255) - Indiquez le chemin du
fichier des titres à fusionner au fichier résultat query'
Print ' @vcFrom (varchar(128)) - Sender list defaulted to
@@ServerName.'
Print ' @vcCC (varchar(2048)) - CC list separating each
with a '';'' or a '','''
Print ' The default is no CC
addresses.'
Print ' @vcBCC (varchar(2048)) - Blind CC list separating
each with a '';'' or a '','''
Print ' The default is no BCC
addresses.'
Print ' @vcSMTPServer (varchar(255)) - Network smtp server
defaulted to your companies network'
Print ' smtp server. Set this in
the stored proc code.'
Print ' @cSendUsing (char(1)) - Specifies the smpt server
method, local or network. The'
Print ' default is network, a
value of ''2''.'
Print ' @vcPort (varchar(3)) - The smtp server
communication port defaulted to ''25''.'
Print ' @cAuthenticate (char(1)) - The smtp server
authentication method defaulted to '
Print ' anonymous, a value of
''0''.'
Print ' @vcDSNOptions (varchar(2)) - The smtp server delivery
status defaulted to none,'
Print ' a value of ''0''.'
Print ' @vcTimeout (varchar(2)) - The smtp server
connection timeout defaulted to 30 seconds.'
Print ' @vcSenderName (varchar(128)) - Primary sender name
defaulted to @@ServerName.'
Print ' @vcServerName (sysname) - SQL Server to which the
query is directed defaulted'
Print ' to @@ServerName.'

Print ''
Print ''
Print 'Example:'
Print 'sp_SQLSMTPMail_csv ''<user@mycompany.com>'', ''This is a
test'', @vcSMTPServer = <network smtp relay server>'
Print ''
Print 'The above example will send an smpt e-mail to
<user@mycompany.com> from @@ServerName'
Print 'with a subject of ''Message from SQL Server <@@ServerName>''
and a'
Print 'text body of ''This is a test'' using the network smtp server
specified.'
Print 'See the MSDN online library, Messaging and Collaboration, at '
Print 'http://www.msdn.microsoft.com/library/ for details about
CDOSYS.'
Print 'subheadings: Messaging and Collaboration>Collaboration Data
Objects>CDO for Windows 2000>'
Print
'Reference>Fields>http://schemas.microsoft.com/cdo/configuration/>smtpserver
field'
Print ''
Print 'Be sure to set the default for @vcSMTPServer before compiling
this stored procedure.'
Print ''
Return
End


-- Declare variables
Declare @iMessageObjId int
Declare @iHr int
Declare @iRtn int
Declare @iFileExists tinyint
Declare @vcCmd varchar(1500)
Declare @vcQueryOutPath varchar(50)
Declare @dtDatetime datetime
Declare @vcErrMssg varchar(255)
Declare @vcAttachment varchar(1024)
Declare @iPos int
Declare @vcErrSource varchar(255)
Declare @vcErrDescription varchar(255)

-- Set local variables.
Set @dtDatetime = getdate()
Set @iHr = 0

-- Check for minimum parameters.
If @vcTo is null
Begin
Set @vcErrMssg = 'You must supply at least 1 recipient.'
Goto ErrMssg
End

-- CDOSYS uses commas to separate recipients. Allow users to use
-- either a comma or a semi-colon by replacing semi-colons in the
-- To, CCs and BCCs.
Select @vcTo = Replace(@vcTo, ';', ',')
Select @vcCC = Replace(@vcCC, ';', ',')
Select @vcBCC = Replace(@vcBCC, ';', ',')

-- Set the default SQL Server to the local SQL Server if one
-- is not provided to accommodate instances in SQL 2000.
If @vcServerName is null
Set @vcServerName = @@servername

-- Set a default "subject" if one is not provided.
If @vcSubject is null
Set @vcSubject = 'Message from SQL Server ' + @vcServerName

-- Set a default "from" if one is not provided.
If @vcFrom is null
Set @vcFrom = 'SQL-' + Replace(@vcServerName,'','_')

-- Set a default "sender name" if one is not provided.
If @vcSenderName is null
Set @vcSenderName = 'SQL-' + Replace(@vcServerName,'','_')

-- Create the SMTP message object.
EXEC @iHr = sp_OACreate 'CDO.Message', @iMessageObjId OUT
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error creating object CDO.Message.'
Goto ErrMssg
End

-- Set SMTP message object parameters.
-- To
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'To', @vcTo
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "To".'
Goto ErrMssg
End

-- Subject
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Subject', @vcSubject
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "Subject".'
Goto ErrMssg
End

-- From
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'From', @vcFrom
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "From".'
Goto ErrMssg
End

-- CC
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'CC', @vcCC
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "CC".'
Goto ErrMssg
End

-- BCC
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'BCC', @vcBCC
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "BCC".'
Goto ErrMssg
End

-- DSNOptions
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'DSNOptions', @vcDSNOptions
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "DSNOptions".'
Goto ErrMssg
End

-- Sender
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'Sender', @vcSenderName
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "Sender".'
Goto ErrMssg
End

-- Is there a query to run?
If @vcQuery is not null and @vcQuery <> ''
Begin
-- We have a query result to include; temporarily send the output to
the
-- drive with the most free space. Use xp_fixeddrives to determine
this.
-- If a temp table exists with the following name drop it.
If (Select object_id('tempdb.dbo.#fixeddrives')) > 0
Exec ('Drop table #fixeddrives')

-- Create a temp table to work with xp_fixeddrives.
Create table #fixeddrives(
Drive char(1) null,
FreeSpace varchar(15) null)

-- Get the fixeddrive info.
Insert into #fixeddrives Exec master.dbo.xp_fixeddrives

-- Get the drive letter of the drive with the most free space
-- Note: The OSQL output file name must be unique for each call within
the same session.
-- Apparently OSQL does not release its lock on the first file
created until the session ends.
-- Hence this alleviates a problem with queries from multiple
calls in a cursor or other loop.
Select @vcQueryOutPath = Drive + ':TempQueryOut' +
ltrim(str(datepart(hh,getdate()))) +
ltrim(str(datepart(mi,getdate()))) +
ltrim(str(datepart(ss,getdate()))) +
ltrim(str(datepart(ms,getdate()))) + '.csv'
from #fixeddrives
where FreeSpace = (select max(FreeSpace) from #fixeddrives )

-- Check for a pattern of '\*' or '?:'.
-- If found assume the query is a file path.
If Left(@vcQuery, 35) like '\%%' or Left(@vcQuery, 5) like '_:%'
Begin
Select @vcCmd = 'osql /S' + @vcServerName + ' /E /i' +
convert(varchar(1024),@vcQuery) +
' /o' + @vcQueryOutPath + ' -n -w5000 '
End
Else
Begin
Select @vcCmd ='bcp' +' "'+ @vcQuery +'" queryout "' +@vcQueryOutPath
+'" -c -S ' + @vcServerName +' -T -t";" -C ACP'

print @vcCmd
/*
Select @vcCmd = 'osql /S' + @vcServerName + ' /E /Q"' + @vcQuery +
'" /o' + @vcQueryOutPath + ' -n -w5000 '
*/

End

-- Execute the query
Exec master.dbo.xp_cmdshell @vcCmd, no_output

-----------Fusionne un fichier contenant le titre des colonnes avec le
resultat de la requete
If @vcQueryTitre is not null and @vcQueryTitre <> ''
Begin
declare @vcCmdajout_titre varchar (256),
@NvcQueryOutPath varchar (256),
@OldvcQueryOutPath varchar (256)

select @NvcQueryOutPath =
left(@vcQueryOutPath,3)+'N'+right(@vcQueryOutPath,25)
print @NvcQueryOutPath
select @vcCmdajout_titre= 'copy '+@vcQueryTitre+'+'+ @vcQueryOutPath + ' '
+@NvcQueryOutPath +' /a /y'
print @vcCmdajout_titre

Exec master.dbo.xp_cmdshell @vcCmdajout_titre, no_output
select @OldvcQueryOutPath=@vcQueryOutPath
select @vcQueryOutPath=@NvcQueryOutPath
end


-- Add the query results as an attachment if the file was successfully
created.
-- Check to see if the file exists. Use xp_fileexist to determine
this.
-- If a temp table exists with the following name drop it.
If (Select object_id('tempdb.dbo.#fileexists')) > 0
Exec ('Drop table #fileexists')

-- Create a temp table to work with xp_fileexist.
Create table #fileexists(
FileExists tinyint null,
FileIsDirectory tinyint null,
ParentDirectoryExists tinyint null)

-- Execute xp_fileexist
Insert into #fileexists exec master.dbo.xp_fileexist @vcQueryOutPath

-- Now see if we need to add the file as an attachment
If (select FileExists from #fileexists) = 1
Begin
-- Set a variable for later use to delete the file.
Select @iFileExists = 1

-- Add the file path to the attachment variable.
If @vcAttachments is null
Select @vcAttachments = @vcQueryOutPath
Else
Select @vcAttachments = @vcAttachments + '; ' +
@vcQueryOutPath
End
End

-- Check for multiple attachments separated by a semi-colon ';'.
If @vcAttachments is not null
Begin
If right(@vcAttachments,1) <> ';'
Select @vcAttachments = @vcAttachments + '; '
Select @iPos = CharIndex(';', @vcAttachments, 1)
While @iPos > 0
Begin
Select @vcAttachment = ltrim(rtrim(substring(@vcAttachments, 1,
@iPos -1)))
Select @vcAttachments = substring(@vcAttachments, @iPos + 1,
Len(@vcAttachments)-@iPos)
EXEC @iHr = sp_OAMethod @iMessageObjId, 'AddAttachment', @iRtn
Out, @vcAttachment
IF @iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out,
@vcErrDescription Out
Select @vcBody = @vcBody + char(13) + char(10) + char(13)
+ char(10) +
char(13) + char(10) + 'Error adding
attachment: ' +
char(13) + char(10) + @vcErrSource +
char(13) + char(10) +
@vcAttachment
End
Select @iPos = CharIndex(';', @vcAttachments, 1)
End
End

-- TextBody
EXEC @iHr = sp_OASetProperty @iMessageObjId, 'TextBody', @vcBody
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message parameter "TextBody".'
Goto ErrMssg
End

-- Other Message parameters for reference
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MimeFormatted', False
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'AutoGenerateTextBody', False
--EXEC @iHr = sp_OASetProperty @iMessageObjId, 'MDNRequested', True

-- Set SMTP Message configuration property values.
-- Network SMTP Server location
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
@vcSMTPServer
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field
"smtpserver".'
Goto ErrMssg
End

-- Sendusing
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
@cSendUsing
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field
"sendusing".'
Goto ErrMssg
End

-- SMTPConnectionTimeout
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPConnectionTimeout").Value',
@vcTimeout
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field
"SMTPConnectionTimeout".'
Goto ErrMssg
End

-- SMTPServerPort
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPServerPort").Value',
@vcPort
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field
"SMTPServerPort".'
Goto ErrMssg
End

-- SMTPAuthenticate
EXEC @iHr = sp_OASetProperty @iMessageObjId,
'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPAuthenticate").Value',
@cAuthenticate
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error setting Message configuraton field
"SMTPAuthenticate".'
Goto ErrMssg
End

-- Other Message Configuration fields for reference
--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SMTPUseSSL").Value',True

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/LanguageCode").Value','en'

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendEmailAddress").Value',
'Test User'

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendUserName").Value',null

--EXEC @iHr = sp_OASetProperty @iMessageObjId,
--'Configuration.Fields("http://schemas.microsoft.com/cdo/configuration/SendPassword").Value',null

-- Update the Message object fields and configuration fields.
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Configuration.Fields.Update'
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error updating Message configuration fields.'
Goto ErrMssg
End

EXEC @iHr = sp_OAMethod @iMessageObjId, 'Fields.Update'
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error updating Message parameters.'
Goto ErrMssg
End

-- Send the message.
EXEC @iHr = sp_OAMethod @iMessageObjId, 'Send'
IF @iHr <> 0
Begin
Set @vcErrMssg = 'Error Sending e-mail.'
Goto ErrMssg
End
Else
Print 'Mail sent.'

Cleanup:
-- Destroy the object and return.
EXEC @iHr = sp_OADestroy @iMessageObjId
--EXEC @iHr = sp_OAStop

-- Delete the query output file if one exists.
If @iFileExists = 1
Begin
Select @vcCmd = 'del ' + @vcQueryOutPath
Exec master.dbo.xp_cmdshell @vcCmd, no_output
If @vcQueryTitre is not null and @vcQueryTitre <> ''
begin
Select @vcCmd = 'del ' + @OldvcQueryOutPath
Exec master.dbo.xp_cmdshell @vcCmd, no_output
end
End
Return

ErrMssg:
Begin
Print @vcErrMssg
If @iHr <> 0
Begin
EXEC sp_OAGetErrorInfo @iMessageObjId, @vcErrSource Out,
@vcErrDescription Out
Print @vcErrSource
Print @vcErrDescription
End

-- Determine whether to exist or go to Cleanup.
If @vcErrMssg = 'Error creating object CDO.Message.'
Return
Else
Goto Cleanup
End


Go

Grant Execute on dbo.sp_SQLSMTPMail_csv to Public
Go