Max Degree of Parallelism - clearification

Tracy you stated, "SQL itself will still use all of the processors, but one
query alone will use a maximum of 2 at one time." When the query runs I
understand it's limited to only utilizing 2 CPUs simultaneously, how do
threads play a role in this? Is it one thread per CPU? Or can multiple
threads run on a CPU?
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45782C05.8090107@.realsqlguy.com...
> Jaski wrote:
> Setting it to 2 will restrict any given query to using 2 processors
> simultaneously. SQL itself will still use all of the processors, but one
> query alone will use a maximum of 2 at one time.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
On Thu, 07 Dec 2006 20:25:18 GMT, "Jaski" <Jaski@.nospam.com> wrote:

>Tracy you stated, "SQL itself will still use all of the processors, but one
>query alone will use a maximum of 2 at one time." When the query runs I
>understand it's limited to only utilizing 2 CPUs simultaneously, how do
>threads play a role in this? Is it one thread per CPU? Or can multiple
>threads run on a CPU?
Multiple threads can run on a CPU, that's for sure.
It is very confusing, but I think the MAXDOP SQL parameter may control
that, but not the number of physical processors. Or, not.
J.
|||On Fri, 8 Dec 2006 09:34:35 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>A query executing in parallel will not use several threads on the same processor. That would
>decrease performance. SQL Server has one scheduler per processor (core, or logical if HT), and it
>will make sure that the query will use threads over different schedulers (i.e., processors).
I was commonly seeing seven and more SPIDs on a two-processor,
single-core, non-hyper'd system, SQL2K Enterprise.
So, SPID is not quite the same as thread, is it?
I was asking about this back in April and May:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/d961ed7576f6a6f9/8336914737a3e20a?lnk=st&q=group%3Amicrosoft.public .sqlserver.*+author%3Ajxstern&rnum=16&hl=en#833691 4737a3e20a[/url]
J.
|||On Fri, 8 Dec 2006 21:20:36 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>Hmm, I was pretty certain that SQL Server would not use several threads per SPID and processor
>(i.e., one spid would get maximum one thread per processor(=scheduler)). Did you see more rows then
>the number of processors (from sp_who or similar) with the same spid number?
Very definitely.
Certainly surprised me!!

>If so, then you can
>ignore my prior post (as I apparently then have misunderstood things).
OK.

>No. SQL Server has a thread pool. Also, SQL Server has a pool of SPIDs. Whenever a SPID need to do
>something, it uses one or more threads from the thread pool. I might be slightly off here with the
>details, so you might want to check out Slava Oks' blog for details.
http://blogs.msdn.com/slavao/
?
Looks good, thanks.
J.

Matrix: static column which calculates from dynamic column

Hello,
I have a matrix for Turnover that looks like this:
Rows: Department
Columns: Status
Data: Count of employees
It runs beautifully to look like this:
Department Active Terminated
________________________________
Cleaners 6 2
Maintenance 5 1
Painters 4 0
TOTAL 15 3
I would like to add another column to take the # of Active employees
and divide it by the number of Terminated Employees to look like this:
Department Active Terminated Turnover
___________________________________________
Cleaners 6 2 33%
Maintenance 5 1 20%
Painters 4 0 0%
TOTAL 15 3 20%
Does anyone know how I can do that, if possible?
Thank you!
MichelleTo add a new column right click on the last column of the table header
and select *Insert column to the right*. Next, right click on the new
cell, select *Expression*, in the text box on the right enter:
=Fields!Active.Value / Fields.Terminated.Value,
and click ok. Next, right click on the new cell again and this time
select *Properties*, in the Format section on the right select
percentage, click ok and you are done!|||Hi Patrick,
Thank you for your quick reply, unfortunately this doesn't work in my
situation. I cannot have an expression of "=Fields!Active.Value /
Fields.Terminated.Value" because these fields do not exist. There is 1
field called Status which can be either Active or Inactive. Status is
a dynamic colunm on my matrix.
Thank you,
Michelle|||Then reference the value of the textbox,
i.e. =ReportItems!active.Value / ReportItems!terminated.Value|||Add a column to the right, then type = (Fields!Terminated.Value) /
(Fields!Active.Value) in the textbox expression. You may have to format the
percentage by right click the mouse and Select "Properties" and choose
percentage.
or = sum(Fields!Terminated.Value) / sum(Fields!Active.Value).
If you in matrix report, then the formula won't work. you have to
use difference function like the "InScope function" More information is
available at
http://msdn.microsoft.com/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp
Good luck!
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Michelle@.bwalk.com" wrote:
> Hello,
> I have a matrix for Turnover that looks like this:
>
> Rows: Department
> Columns: Status
> Data: Count of employees
>
> It runs beautifully to look like this:
>
> Department Active Terminated
> ________________________________
> Cleaners 6 2
> Maintenance 5 1
> Painters 4 0
> TOTAL 15 3
>
> I would like to add another column to take the # of Active employees
> and divide it by the number of Terminated Employees to look like this:
>
> Department Active Terminated Turnover
> ___________________________________________
> Cleaners 6 2 33%
> Maintenance 5 1 20%
> Painters 4 0 0%
> TOTAL 15 3 20%
>
> Does anyone know how I can do that, if possible?
>
> Thank you!
> Michelle
>

Matrix - Limiting the width

Hi,

I have a matrix object in a report that sometimes runs off the side of the page based on the underlying data. Essentially if there are more than 11 columns it stretches out my page.

How can I fix this? Ideally, I would like to show only the Top 11 results but cannot seem to figure out how (or where, or on what data element) to properly set a filter.

Thanks.

Brad

You mean if there are more than 11 rows?

To get the top 11 results, you can do this:

select top 11 * FROM ... WHERE ....

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
>