Max Dates For Cost Query

Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

DavidOriginally posted by hidvegi
Hi,

I am trying to identify the costs for products with the latest cost date. I am unable to run the query. Is there something that I can do?

SELECT PART,COST, DATE FROM DATA/COSTFILE AS T1 WHERE T1.DATE= (SELECT MAX(DATE) AS T2 FROM DATA/COSTFILE AS T2 WHERE T2.PART=T1.PART)

Thanks,

David

SELECT
T1.PART,
T1.COST,
T1.DATE
FROM
[DATA/COSTFILE] AS T1
WHERE
T1.DATE= (
SELECT MAX(T2.DATE) AS MAXDATE
FROM DATA/COSTFILE AS T2
WHERE T2.PART=T1.PART)

FYI, you shouldn't use ANY special characters in your column names or table names, especially "/\-_.,&%@.+"=".

The only exception to this is "_" which can be used after for sp_name on stored procedures that must reside in master and run on every database.

Matrix SubTotals

I have subtotal at the bottom of the matrix. I am using Distinct Count in the expression field of matrix. But when I run the report, The Subtotal is giving the sum of all not the Distict counts. I can see the properties of Subtotal row. But I can not find the expression Property for that. Please help me anyone know it or had this issue.

I appreciate your help.

Thanks in advance

Rams

Hi ramsk

The subtotal expression in matrix report cannot be directley accesed.
You need to use custom code if you wish to manipulate the data in a different
way. This is because the expression used in the Matrix subtotal is the same as that
of the detail rows by default.

Custom code can be written using vb.NET or c# by going to report properties
and selecting the code tab. Write the function to accept integer values(or whichever data type you use)
and the call it from the detail cell within the matrix using the following expression:

=Code.<insert function name here without sharp bracets>(<field name here without sharp brackets>)

EX: =Code.GetFields(Fields!Net_Invoice.Value)

Now you can do a count on the values using vb/c#
Using this method even the most complex calculations can be done.

Gerhard Davids

|||

Thank you Gerhard Davids. I don't know vb.NET/C# coding. I will try to do that.

Thanks,

Ramsk

|||

Actually,

You can access the subtotal for Matrix report. Click on the green triangle of your matrix totals and you can write your expression there.

Hammer

|||

Hi Hammer,

I'm sorry to have to inform you that this is in fact
not possible. There has been much contravercy
about this and it has been causing many people
headaches including me.

Thus we have been using this very tedious and
redundant workaround.If you follow this link you
see in the newsgroup discussion that clearly
states: it's not possible at this time.

Also if you search around a bit you will get the
same result. Even with CTP2 this functionality
is not available.

Just thaught I'd let you know as not to get some
peoples hopes up.

Gerhard Davids

Matrix Control Reports...

Hi Folks,
I am in the process of creating MSRS 2005 reports which run of a MSAS cube.
I have 2 matrix controls which feed of 2 different datasets. There are 3
columns in each matrix control which are part of a hierarchy. when the user
drills down a parent on the first control, is it possible for the child on
the second matrix control to be also visible.
For example when user clicks on the + sign before "A", "B" is made visible
on first matrix, at the same time "B" should be visible on the second matrix.
2. I have 2 matrix controls side by side, i have to show and hide the 2
based on a report parameter. Is it possible to dynamically alter the location
of the matrix control on the report. For example if the 1st matrix control is
made invisible, can i move 2 matrix control to take the place of the first
matrix cntrol.
Thank you
RamdasHi Ram,
I have the same problem (your second) - I want to decide the location of
the Matrix dynamically at run time. (Reporting Services)
If you've come to a solution, kindly contact me
DAVIDMA4@.YAHOO.COM
David
--
×?×?×?
"Ram" wrote:
> Hi Folks,
> I am in the process of creating MSRS 2005 reports which run of a MSAS cube.
> I have 2 matrix controls which feed of 2 different datasets. There are 3
> columns in each matrix control which are part of a hierarchy. when the user
> drills down a parent on the first control, is it possible for the child on
> the second matrix control to be also visible.
> For example when user clicks on the + sign before "A", "B" is made visible
> on first matrix, at the same time "B" should be visible on the second matrix.
> 2. I have 2 matrix controls side by side, i have to show and hide the 2
> based on a report parameter. Is it possible to dynamically alter the location
> of the matrix control on the report. For example if the 1st matrix control is
> made invisible, can i move 2 matrix control to take the place of the first
> matrix cntrol.
> Thank you
> Ramdas
>

Master-detail subreports and performance.

If I create a master detail-report using subreports, will a separate query
run for each subreport at the time it loads? Lets say I have a report with
500 master rows, will it have to run 500 queries for the subreports before
the first page loads?
I know there is no on-demand subreport load, and from what I read you can't
do a heterogeneos joins between 2 record sets in the same report - so that
the detail recordset would filter under each master row based on an id.
So what is the best way to produce a large master-detail report with
reasonable performance?
Thanks.You are correct, the subreport is executed for each master record. Other
than making sure your database is optimized (correct indexes for example)
there isn't much you can do to speed it up the report running. However, if
the report is only needed for a particular time (for instance, this report
is run daily) you could use snapshots. Read up on that in books online.
The other possibility is to consider drill through. In that case you show
the master tables with a link that when the user wants additional data they
click on the link and use jump to report to pull up the additional data.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David W" <spivey@.nospam.post.com> wrote in message
news:eSsZCOqlHHA.4628@.TK2MSFTNGP06.phx.gbl...
> If I create a master detail-report using subreports, will a separate query
> run for each subreport at the time it loads? Lets say I have a report
> with 500 master rows, will it have to run 500 queries for the subreports
> before the first page loads?
> I know there is no on-demand subreport load, and from what I read you
> can't do a heterogeneos joins between 2 record sets in the same report -
> so that the detail recordset would filter under each master row based on
> an id.
> So what is the best way to produce a large master-detail report with
> reasonable performance?
> Thanks.
>|||Thanks Bruce for confirming my suspicions. I'm definitely not doing a
report that generates over 500 queries to load a page regardless of database
optimizations or snapshot usage.
Well, I ended up doing a SQL join between my master recordset query and my
detail recordset query to produce one mammoth recordset containing all
fields. Then I grouped on the master columns, and displayed the detail
records as expandable sections. Its not efficient by any means but it works
reasonably well, and its only a single query, and it gives me the
master-detail effect.
Any idea when we are going to see the next version of Reporting Services.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23AwSqovlHHA.4552@.TK2MSFTNGP04.phx.gbl...
> You are correct, the subreport is executed for each master record. Other
> than making sure your database is optimized (correct indexes for example)
> there isn't much you can do to speed it up the report running. However, if
> the report is only needed for a particular time (for instance, this report
> is run daily) you could use snapshots. Read up on that in books online.
> The other possibility is to consider drill through. In that case you show
> the master tables with a link that when the user wants additional data
> they click on the link and use jump to report to pull up the additional
> data.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "David W" <spivey@.nospam.post.com> wrote in message
> news:eSsZCOqlHHA.4628@.TK2MSFTNGP06.phx.gbl...
>> If I create a master detail-report using subreports, will a separate
>> query run for each subreport at the time it loads? Lets say I have a
>> report with 500 master rows, will it have to run 500 queries for the
>> subreports before the first page loads?
>> I know there is no on-demand subreport load, and from what I read you
>> can't do a heterogeneos joins between 2 record sets in the same report -
>> so that the detail recordset would filter under each master row based on
>> an id.
>> So what is the best way to produce a large master-detail report with
>> reasonable performance?
>> Thanks.
>|||What you are explaining here is drill down. It is a nice user interface but
not one I do too often because of the amount of data returned.
I would consider looking into using drill through in the future. Users are
very comfortable with it and you get really good performance.
The next release of RS (non-service pack) is with Katmai (next version of
SQL Server). RS is part of SQL Server and releases with both service packs
and major releases for SQL Server. Early on this was not true but now it is
totally in sync with the rest of SQL Server.
http://www.microsoft.com/presspass/press/2007/may07/05-09KatmaiPR.mspx
Right now they are just saying 2008.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David W" <spivey@.nospam.post.com> wrote in message
news:u4QXU2vlHHA.3872@.TK2MSFTNGP04.phx.gbl...
> Thanks Bruce for confirming my suspicions. I'm definitely not doing a
> report that generates over 500 queries to load a page regardless of
> database optimizations or snapshot usage.
> Well, I ended up doing a SQL join between my master recordset query and my
> detail recordset query to produce one mammoth recordset containing all
> fields. Then I grouped on the master columns, and displayed the detail
> records as expandable sections. Its not efficient by any means but it
> works reasonably well, and its only a single query, and it gives me the
> master-detail effect.
> Any idea when we are going to see the next version of Reporting Services.
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23AwSqovlHHA.4552@.TK2MSFTNGP04.phx.gbl...
>> You are correct, the subreport is executed for each master record. Other
>> than making sure your database is optimized (correct indexes for example)
>> there isn't much you can do to speed it up the report running. However,
>> if the report is only needed for a particular time (for instance, this
>> report is run daily) you could use snapshots. Read up on that in books
>> online.
>> The other possibility is to consider drill through. In that case you show
>> the master tables with a link that when the user wants additional data
>> they click on the link and use jump to report to pull up the additional
>> data.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "David W" <spivey@.nospam.post.com> wrote in message
>> news:eSsZCOqlHHA.4628@.TK2MSFTNGP06.phx.gbl...
>> If I create a master detail-report using subreports, will a separate
>> query run for each subreport at the time it loads? Lets say I have a
>> report with 500 master rows, will it have to run 500 queries for the
>> subreports before the first page loads?
>> I know there is no on-demand subreport load, and from what I read you
>> can't do a heterogeneos joins between 2 record sets in the same report -
>> so that the detail recordset would filter under each master row based on
>> an id.
>> So what is the best way to produce a large master-detail report with
>> reasonable performance?
>> Thanks.
>>
>

master.dbo.xp_cmdshell is failing

Please help !
I run an ftp task which picks up a file from an ftp server through a package it runs fine - no errors.
If I schedule the same dts or if I run the following code in query analyser it fails
declare @.cmd varchar(2000)
declare @.ftpcommandfile varchar(2000)
--Get list of remote files
set @.ftpcommandfile = '-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Collections\Dialer\Dialer_Files\GetDialerFile.txt'
set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
Exec master.dbo.xp_cmdshell @.cmd
I get the following message:
output ---
Error opening script file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Collections\Dialer\Dialer_Files\GetDialerFile.txt.
Transfers files to and from a computer running an FTP server service
(sometimes called a daemon). Ftp can be used interactively.
FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
-v Suppresses display of remote server responses.
-n Suppresses auto-login upon initial connection.
-i Turns off interactive prompting during multiple file
transfers.
-d Enables debugging.
-g Disables filename globbing (see GLOB command).
-s:filename Specifies a text file containing FTP commands; the
commands will automatically run after FTP starts.
-a Use any local interface when binding data connection.
-A login as anonymous.
-x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
-r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
-b:async count Overrides the default async count of 3
-w:buffer size Overrides the default transfer buffer size of 65535.
host Specifies the host name or IP address of the remote
host to connect to.
Notes:
- mget and mput commands take y/n/q for yes/no/quit.
- Use Control-C to abort commands.
NULLYes, the error is in the FTP command, the s parameter is included twice.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aman" <Aman@.discussions.microsoft.com> wrote in message
news:641A9145-D95F-4D9B-A037-603546DA9178@.microsoft.com...
> Please help !
> I run an ftp task which picks up a file from an ftp server through a
package it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query
analyser it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile ='-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Collections\Dialer\Dialer_
Files\GetDialerFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
>
---
> Error opening script
file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Collections\Dialer\Dia
ler_Files\GetDialerFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer]
[-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>

master.dbo.xp_cmdshell is failing

Please help !
I run an ftp task which picks up a file from an ftp server through a package it runs fine - no errors.
If I schedule the same dts or if I run the following code in query analyser it fails
declare @.cmd varchar(2000)
declare @.ftpcommandfile varchar(2000)
--Get list of remote files
set @.ftpcommandfile = '-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_Files\GetDialerFile.txt'
set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
Exec master.dbo.xp_cmdshell @.cmd
I get the following message:
output ---
Error opening script file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_Files\GetDialerFile.txt.
Transfers files to and from a computer running an FTP server service
(sometimes called a daemon). Ftp can be used interactively.
FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
-v Suppresses display of remote server responses.
-n Suppresses auto-login upon initial connection.
-i Turns off interactive prompting during multiple file
transfers.
-d Enables debugging.
-g Disables filename globbing (see GLOB command).
-s:filename Specifies a text file containing FTP commands; the
commands will automatically run after FTP starts.
-a Use any local interface when binding data connection.
-A login as anonymous.
-x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
-r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
-b:async count Overrides the default async count of 3
-w:buffer size Overrides the default transfer buffer size of 65535.
host Specifies the host name or IP address of the remote
host to connect to.
Notes:
- mget and mput commands take y/n/q for yes/no/quit.
- Use Control-C to abort commands.
NULL
Add PRINT @.cmd before the xp_cmdshell to see what the caommand is, but I think you have -s: in there twice.
"Aman" wrote:

> Please help !
> I run an ftp task which picks up a file from an ftp server through a package it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query analyser it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile = '-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_Files\GetDialerFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
> output ---
> Error opening script file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_Files\GetDialerFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>
|||Yes, the error is in the FTP command, the s parameter is included twice.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aman" <Aman@.discussions.microsoft.com> wrote in message
news:641A9145-D95F-4D9B-A037-603546DA9178@.microsoft.com...
> Please help !
> I run an ftp task which picks up a file from an ftp server through a
package it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query
analyser it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile =
'-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_
Files\GetDialerFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
>

> Error opening script
file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dia
ler_Files\GetDialerFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer]
[-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>

master.dbo.xp_cmdshell is failing

Please help !
I run an ftp task which picks up a file from an ftp server through a package
it runs fine - no errors.
If I schedule the same dts or if I run the following code in query analyser
it fails
declare @.cmd varchar(2000)
declare @.ftpcommandfile varchar(2000)
--Get list of remote files
set @.ftpcommandfile = '- s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\Col
lections\Dialer\Dialer_Files\GetDialerFi
le.txt'
set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
Exec master.dbo.xp_cmdshell @.cmd
I get the following message:
output
--
--
Error opening script file - s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\C
ollections\Dialer\Dialer_Files\GetDialer
File.txt.
Transfers files to and from a computer running an FTP server service
(sometimes called a daemon). Ftp can be used interactively.
FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a]
[-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers] [
-w:windowsize] [host]
-v Suppresses display of remote server responses.
-n Suppresses auto-login upon initial connection.
-i Turns off interactive prompting during multiple file
transfers.
-d Enables debugging.
-g Disables filename globbing (see GLOB command).
-s:filename Specifies a text file containing FTP commands; the
commands will automatically run after FTP starts.
-a Use any local interface when binding data connection.
-A login as anonymous.
-x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
-r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
-b:async count Overrides the default async count of 3
-w:buffer size Overrides the default transfer buffer size of 65535.
host Specifies the host name or IP address of the remote
host to connect to.
Notes:
- mget and mput commands take y/n/q for yes/no/quit.
- Use Control-C to abort commands.
NULLAdd PRINT @.cmd before the xp_cmdshell to see what the caommand is, but I th
ink you have -s: in there twice.
"Aman" wrote:

> Please help !
> I run an ftp task which picks up a file from an ftp server through a packa
ge it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query analys
er it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile = '- s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\
Collections\Dialer\Dialer_Files\GetDiale
rFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
> output
--
--
> Error opening script file - s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM
\Collections\Dialer\Dialer_Files\GetDial
erFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a
] [-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers]
1;-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>|||Yes, the error is in the FTP command, the s parameter is included twice.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aman" <Aman@.discussions.microsoft.com> wrote in message
news:641A9145-D95F-4D9B-A037-603546DA9178@.microsoft.com...
> Please help !
> I run an ftp task which picks up a file from an ftp server through a
package it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query
analyser it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile =
'- s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\Collections\Dialer\Dialer_
Files\GetDialerFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
>
---
> Error opening script
file - s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\Collections\Dialer\Dia
ler_Files\GetDialerFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-
A] [-x:sendbuffer]
[-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>