Sorry for reposting a similar message but I really need help.

I have a query that looks like that :

SELECT DISTINCT
A_CLIENTS.idclient,
A_CLIENTS.CLICODE,
A_CLIENTS.CLIEMAIL,
FROM A_CLIENTS
WHERE A_CLIENTS.IDSOCIETE=@idsociete
AND TT.LOGDATE<@ddate

I would like to generate a xls file from a DTS with the result of this
query.
But I don't know how to pass the parameters @idsociete and @ddate
Also this dts must be called from a stored procedure.

Thanks for your help

Stan

Are you using 2000 ?

If yes then change

SELECT DISTINCT
A_CLIENTS.idclient,
A_CLIENTS.CLICODE,
A_CLIENTS.CLIEMAIL,
FROM A_CLIENTS
WHERE A_CLIENTS.IDSOCIETE=@idsociete
AND TT.LOGDATE<@ddate


to

SELECT DISTINCT
A_CLIENTS.idclient,
A_CLIENTS.CLICODE,
A_CLIENTS.CLIEMAIL,
FROM A_CLIENTS
WHERE A_CLIENTS.IDSOCIETE= ?
AND TT.LOGDATE< ?

You then map, using the parameters button, global variables to the ?
placeholders

When you call the package using DTSRUN you can use the /A switch to pass
values to the global variables
--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Stan Sainte-Rose" <stan (AT) cyber972 (DOT) com> wrote

Thanks Allan for your post.
I ve tried what you ve said but I still have a problem..
In fact my sproc was not completed and it blocks when I change the
@variables with ?

The completed sproc is :

SELECT DISTINCT
A_CLIENTS.idclient,
A_CLIENTS.CLICODE,
A_CLIENTS.CLIEMAIL,
nb=(SELECT COUNT(*) FROM A_LOGCLIENTS WHERE
A_LOGCLIENTS.IDCLIENT=A_CLIENTS.clientid)
FROM A_LOGCLIENTS INNER JOIN A_CLIENTS ON
A_CLIENTS.idclient=A_LOGCLIENTS.idclient
WHERE A_CLIENTS.IDSOCIETE=@societe AND A_LOGCLIENTS.logdate<@ddate

I get an error message on the Count statement when I change the @societe and
@ddate by ?

* By the way, the SQL Query must saved in a Sproc right or just to be used
when I create the query for the DTS ?
* How can I called this DTS from an another Sproc and pass the different
parameters ?
* And last question, when I param the Query for the DTS, I want to export
the result as a excel file.
I don't have problems for that, but, I would like to add dynamically a path
for the result file.
How can I do that ?

Thanks again for your help

Stan

use SET NOCOUNT ON in the proc

You can use the statement simply as is inside a DataPump task or an
ExecuteSQL task.

You .

You can pass parameters between procs as you would normally and simply pass
them into the proc that calls the package.

Dynamic results file

Connections
(http://www.sqldts.com/default.aspx?101)

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"Stan Sainte-Rose" <stan (AT) cyber972 (DOT) com> wrote