Create a string with carriage return and line feed

Hello,
Is it possible to create a variable that has carriage return and line feed
embed inside. Say I have 3 fields: field1, field2, field3. I want to return
a
string with carriage returns and line feeds between field1, 2 and field2, 3.
Thanks in advanceLOOK in BOL for CHAR,
TAB Char(9)
LineFeed Char(10)
CarriageReturn Char(13)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Ed Chiu" <EdChiu@.discussions.microsoft.com> schrieb im Newsbeitrag
news:462D3FCC-28E9-4660-A5F7-559D5CB3CA33@.microsoft.com...
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
> return a
> string with carriage returns and line feeds between field1, 2 and field2,
> 3.
> Thanks in advance|||> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
return a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
Include CHAR(13)+CHAR(10) when you are concatenating the strings.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Try,
select 'Microsoft' + char(13) + char(10) + 'SQL' + char(13) + char(10) +
'Server' + char(13) + char(10) + '2000'
AMB
"Ed Chiu" wrote:

> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advance|||DECLARE @.var VARCHAR(50)
SET @.var = 'joe' + CHAR(13) + CHAR(10) + 'blow'
SELECT @.var
It's up to the consumer of the data to display it properly. For example
in Query Analyzer, if you choose to display results in a grid, it
replaces the carriage return, line feed with spaces. If you choose to
display in text mode, it'll put the carriage return, line feed in there
for you.
If you're returning this to a .net dataset, you can display it properly.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
Ed Chiu wrote:
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advancesql

Create a Sequential Line Count for Group...

I have a report that groups by item number but then is filtered by a
parameter to only show the top # the user has entered. Does anyone
know if it is possible to number the results I return so that the user
can see how that particular record ranks with other records? Basically
I want to be able to have the user see 1 for the top item sold, 2 for
the second most items sold, etc...Try using
=Rownumber(Groupname)
--
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
"Brent" <Brent.Raymond@.gmail.com> wrote in message
news:1126532242.793502.287600@.o13g2000cwo.googlegroups.com...
>I have a report that groups by item number but then is filtered by a
> parameter to only show the top # the user has entered. Does anyone
> know if it is possible to number the results I return so that the user
> can see how that particular record ranks with other records? Basically
> I want to be able to have the user see 1 for the top item sold, 2 for
> the second most items sold, etc...
>

crashing DTS on table import

Hi
I am having some trouble importing certain tables from an odbc database
(Sage Line 50). Most of the tables import without any trouble whatsoever,
however there are 2 tables (SOP_items and POP_items) that always crash dts
when it tries to import them. The DTS screen simply dissappears from the
screen with no error. I realise that it is beyond the scope of this
newsgroup to ask about the specific odbc database. However I was wondering
is there a log somewhere which might help me to shed light on what in the
tables is making it crash. To note I can import and link to the tables in
access with no trouble whatsoever. Thanks for your time.
Dan
There will be no DTS log because it sounds as though something is crashing
DTS. Have you looked in Event Viewer?
Have you tried SPing (What SP are you running?)?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
> wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>
|||Thanks,
It has SP3 on it, just looking at the mdac 2.7 sp1, see if this sorts it.
Dan
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eVWcPREwEHA.2568@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> There will be no DTS log because it sounds as though something is crashing
> DTS. Have you looked in Event Viewer?
> Have you tried SPing (What SP are you running?)?
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
in
>
|||Change of plan, going to install Microsoft Data Access Components (MDAC)
2.8, didn't see the newer version at first. I already have SP3 of SQL, it
does advise that SP3a is only for versions below SP3, and as such I
shouldn't install it? Looked in the event log to see if there is any log of
an error, but there is nothing whatsoever. Thanks.
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>
|||MDAC 2.8 doesn't seem to have made any difference. Any ideas?
Thanks
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Change of plan, going to install Microsoft Data Access Components (MDAC)
> 2.8, didn't see the newer version at first. I already have SP3 of SQL, it
> does advise that SP3a is only for versions below SP3, and as such I
> shouldn't install it? Looked in the event log to see if there is any log
of[vbcol=seagreen]
> an error, but there is nothing whatsoever. Thanks.
> Dan
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
> wondering
the[vbcol=seagreen]
in
>
|||Since this is an ODBC data source, you can try to turn on
ODBC tracing on the box where the package runs. Make sure to
turn the trace off after you have crashed with tracing
turned on. You can then go through the tracing log file and
look for ODBC errors.
-Sue
On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
<it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:

>MDAC 2.8 doesn't seem to have made any difference. Any ideas?
>Thanks
>Dan
>"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
>news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
>of
>whatsoever,
>dts
>the
>the
>in
>
|||Hi
Thanks for the responses, I think that I have worked out where the problem
was occuring. There were date columns in those two tables which were
causing the crash. The dates were in the form dd/mm/yyyy as well as
mm/dd/yyyy, so no wonder it had trouble with the data - what a wonderful
accounting package, if only it used SQL as the native database! Thanks for
all of your help
Dan
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:gu1fo0h2h1n4j4f3pnkgh8u2lfvl3h3tth@.4ax.com... [vbcol=seagreen]
> Since this is an ODBC data source, you can try to turn on
> ODBC tracing on the box where the package runs. Make sure to
> turn the trace off after you have crashed with tracing
> turned on. You can then go through the tracing log file and
> look for ODBC errors.
> -Sue
> On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
> <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:
(MDAC)[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
database[vbcol=seagreen]
crash[vbcol=seagreen]
tables
>

crashing DTS on table import

Hi
I am having some trouble importing certain tables from an odbc database
(Sage Line 50). Most of the tables import without any trouble whatsoever,
however there are 2 tables (SOP_items and POP_items) that always crash dts
when it tries to import them. The DTS screen simply dissappears from the
screen with no error. I realise that it is beyond the scope of this
newsgroup to ask about the specific odbc database. However I was wondering
is there a log somewhere which might help me to shed light on what in the
tables is making it crash. To note I can import and link to the tables in
access with no trouble whatsoever. Thanks for your time.
DanThere will be no DTS log because it sounds as though something is crashing
DTS. Have you looked in Event Viewer?
Have you tried SPing (What SP are you running?)?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
> wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>|||Thanks,
It has SP3 on it, just looking at the mdac 2.7 sp1, see if this sorts it.
Dan
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eVWcPREwEHA.2568@.TK2MSFTNGP11.phx.gbl...
> There will be no DTS log because it sounds as though something is crashing
> DTS. Have you looked in Event Viewer?
> Have you tried SPing (What SP are you running?)?
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Change of plan, going to install Microsoft Data Access Components (MDAC)
2.8, didn't see the newer version at first. I already have SP3 of SQL, it
does advise that SP3a is only for versions below SP3, and as such I
shouldn't install it? Looked in the event log to see if there is any log of
an error, but there is nothing whatsoever. Thanks.
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>|||MDAC 2.8 doesn't seem to have made any difference. Any ideas?
Thanks
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Change of plan, going to install Microsoft Data Access Components (MDAC)
> 2.8, didn't see the newer version at first. I already have SP3 of SQL, it
> does advise that SP3a is only for versions below SP3, and as such I
> shouldn't install it? Looked in the event log to see if there is any log
of
> an error, but there is nothing whatsoever. Thanks.
> Dan
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
> wondering
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Since this is an ODBC data source, you can try to turn on
ODBC tracing on the box where the package runs. Make sure to
turn the trace off after you have crashed with tracing
turned on. You can then go through the tracing log file and
look for ODBC errors.
-Sue
On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
<it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:

>MDAC 2.8 doesn't seem to have made any difference. Any ideas?
>Thanks
>Dan
>"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
>news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
>of
>whatsoever,
>dts
>the
>the
>in
>|||Hi
Thanks for the responses, I think that I have worked out where the problem
was occuring. There were date columns in those two tables which were
causing the crash. The dates were in the form dd/mm/yyyy as well as
mm/dd/yyyy, so no wonder it had trouble with the data - what a wonderful
accounting package, if only it used SQL as the native database! Thanks for
all of your help
Dan
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:gu1fo0h2h1n4j4f3pnkgh8u2lfvl3h3tth@.
4ax.com...
> Since this is an ODBC data source, you can try to turn on
> ODBC tracing on the box where the package runs. Make sure to
> turn the trace off after you have crashed with tracing
> turned on. You can then go through the tracing log file and
> look for ODBC errors.
> -Sue
> On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
> <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:
>
(MDAC)[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
database[vbcol=seagreen]
crash[vbcol=seagreen]
tables[vbcol=seagreen]
>

CR 10 creating charts

I am using Visual Studio 2005 VB.NET and Crystal Reports 10. I need to create a line graph on a crystal report by sending it a dataset from the vb.net page. I am new to this. Does anyone have any example code or know of any websites that demonstrate how to do this?Please see here (http://www.dev-archive.com/forum/showthread.php?t=409951)

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXS
Hello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default...b;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/default...b;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:
|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:

> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?
|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
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
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>
|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> 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
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXSHello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribi en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/defaul...kb;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:

> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
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
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribi en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> 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
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>

CPU 100%

Hello
We have running our server-client application on line. The server Win2000
is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
the server.
What do you suggest me to solve or to mitigate this matter?
THXSHello Harvey,
Are you still able to connect to SQL Server? If you can, connect to SQL
Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
active'. This will show you all the active connections in SQL Server.
That will give you an idea which connection(s) is consuming all the CPU
time.
I also recommend reviewing the following article on using Profiler and
Performance monitor to troubleshoot performance issue.
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
Hope this helps.
Yih-Yoon Lee
Harvey Triana wrote:
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||Thanks Yih-
Saludos.
"Yih-Yoon Lee" <yihyoon.online@.gmail.com> escribió en el mensaje
news:%23dfb302%23EHA.2112@.TK2MSFTNGP14.phx.gbl...
> Hello Harvey,
> Are you still able to connect to SQL Server? If you can, connect to SQL
> Server using Query Analzyer as a sysadmin. Execute the command 'sp_who2
> active'. This will show you all the active connections in SQL Server.
> That will give you an idea which connection(s) is consuming all the CPU
> time.
> I also recommend reviewing the following article on using Profiler and
> Performance monitor to troubleshoot performance issue.
> HOW TO: Troubleshoot Application Performance with SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;224587
> Hope this helps.
> Yih-Yoon Lee
> Harvey Triana wrote:
>> Hello
>> We have running our server-client application on line. The server
>> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
>> (process sqlservr.exe) is the great consumer of memory. I am afraid a
>> collapse in the server.
>> What do you suggest me to solve or to mitigate this matter?
>> THXS|||"Harvey Triana" <harveytriana@.hotmail.com> wrote in
news:uauwev1#EHA.1564@.TK2MSFTNGP09.phx.gbl:
> Hello
> We have running our server-client application on line. The server
> Win2000 is showing (Tasks Administrator) use of CPU 100%. SQL Server
> (process sqlservr.exe) is the great consumer of memory. I am afraid a
> collapse in the server.
Run a trace to find out what SQL Server operations are running. Do you have
the likes of autoshrink on? Are you indexes correctly defined? Are
statistics upto date?|||Also make sure you are on SP3 or better... If the proc is 100% consistently
( even with no users), that is an indicator of the slammer virus...
Otherwise, look for big queries as the others have suggested.
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
"Harvey Triana" <harveytriana@.hotmail.com> wrote in message
news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
> Hello
> We have running our server-client application on line. The server Win2000
> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
> the server.
> What do you suggest me to solve or to mitigate this matter?
> THXS
>|||ok Wayne
...
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> escribió en el mensaje
news:ewix%23f9%23EHA.208@.TK2MSFTNGP12.phx.gbl...
> Also make sure you are on SP3 or better... If the proc is 100%
> consistently
> ( even with no users), that is an indicator of the slammer virus...
> Otherwise, look for big queries as the others have suggested.
>
> --
> 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
> "Harvey Triana" <harveytriana@.hotmail.com> wrote in message
> news:uauwev1%23EHA.1564@.TK2MSFTNGP09.phx.gbl...
>> Hello
>> We have running our server-client application on line. The server
>> Win2000
>> is showing (Tasks Administrator) use of CPU 100%. SQL Server (process
>> sqlservr.exe) is the great consumer of memory. I am afraid a collapse in
>> the server.
>> What do you suggest me to solve or to mitigate this matter?
>> THXS
>>
>

Counting Question

Below is what I currently have in query. I have a question for the line tha
t
has an asterisk before it.
Is there a way to show if a card number has shown up more than once on a
given settlement _batch_num? I have no clue what to put as the COUNT CASE
since I am searching over a period of settlement_batch_num's. I just need
cards that duplicate only on the same settlement_batch_num. Is there anyway
to list the card also? The settlement_batch_num is a certain day. Thanks
for any help.
Use Winpayment
GO
SELECT S.card_acceptor_identification STORE,
COUNT (M.message_type) TOTAL_#_TRANS,
COUNT(CASE WHEN id_code_1 = 'DB' and response_code <> '00' THEN 1 END)
DEBIT_DECLINED
*COUNT(CASE When card_num = ? and settlement_batch_num = ? )
FROM Store S
Left Join financial_message M
On M.card_acceptor_identification = S.card_acceptor_identification
And (settlement_batch_number >= '867'
And settlement_batch_nunber <= '888')
GROUP BY S.card_acceptor_identificationOn Tue, 24 May 2005 08:48:04 -0700, tarheels4025 wrote:

>Below is what I currently have in query. I have a question for the line th
at
>has an asterisk before it.
>Is there a way to show if a card number has shown up more than once on a
>given settlement _batch_num? I have no clue what to put as the COUNT CASE
>since I am searching over a period of settlement_batch_num's. I just need
>cards that duplicate only on the same settlement_batch_num. Is there anywa
y
>to list the card also? The settlement_batch_num is a certain day. Thanks
>for any help.
(snip)
Hi tarheels,
Since you're not exactly new to this group, you probably won't be
surprised to be pointed to www.aspfaq.com/5006.
From your description, I *THINK* that it's possible to do what you want
to do - but I also *KNOW* that I don't really understand your exact
requirements.
Post table structure, sample data and expected output (as indicated on
www.aspfaq.com/5006), then we'll be able to help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

counter inside select statement?

Hi, can you add a counter inside a select statement to get a unique id line of the rows?

In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order.

This is the normal way:

SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID) ... some more where/order by statements

This is what i want:

DECLARE @.tempCounter bigint
SET @.tempCounter = 0
SELECT @.tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID)... some more where/order by statements and at the end.. (SELECT @.tempCounter = @.tempCounter + 1)

Anyone know if this can be done?

Use Row Num ( if you are using SQL SErver 2005 ) as

Select ROW_NUMBER() OVER(ORDER BY Some_Field ) AS rownum,
* from Table_Nane

|||

yes i am using MS SQL 2005, it worked for half of the problem, ill display the SP below, what the SP does is to select the posts from a single user, by ordering them first by topicID then by date created.
In order to bring back just what the client need, i incorperated a custom paging system.

DECLARE @.first_idint, @.startRowint
SET @.startRowIndex=(@.startRowIndex- 1)* @.maximumRows+ 1
IF @.startRowIndex= 0
SET @.startRowIndex= 1

SETROWCOUNT @.startRowIndex

SELECT @.first_id=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC)FROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)ORDERBY forum_answer.topicidASC, forum_answer.idDESC

SETROWCOUNT @.maximumRows

DECLARE @.tempVarint
SELECT @.tempVar=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC), forum_answer.topicid, forum_answer.id, forum_answer.username, forum_answer.answer, forum_answer.created, profile_publicinfo.signatureFROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)AND(@.first_id<= @.tempVar)ORDERBY forum_answer.topicidASC, forum_answer.idDESC

SETROWCOUNT 0

The first thing we do, is to get the id for the first post that should be returned. This works with the ROW_NUMBER method, the second select statement, takes xx number of rows with start from the position it recieves from the first select statement. So if @.first_id is 5 and maximumRows is 5, then the second select statement will only take rows 5->10 from the table.

The problem is the ROW_NUMBER inside the second select statement, sql screams if i put it at the end like (@.first_id <= ROW_NUMBER...

Msg 4108, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 32
Windowed functions can only appear in the SELECT or ORDER BY clauses.

SQL also screams if i put it in the beginning (as shown above), then i get the following error:

Msg 141, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 33
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Thats why i wanted a way to calculate the rows and get a ID according to that. Any idea of how to proceed?

Counter by Group

How can I create a counter (line#) by group were it resets = 1 whenever a new group starts.

Customer Line#

A 1

A 2

A 3

B 1

C 1

C 2

Hello,

Try putting this in your detail row:

=RowNumber("GROUPNAME")

Get the GROUPNAME by right-clicking on your group header row and select "Edit Group...". Copy the Name and place it in the RowNumber function.

Hope this helps.

Jarret

|||

This works as specified but it does not solve my real problem.

I have a query - customer master joined to ship-to's.

I am trying to use the RowNumber expression with a Parameter "Show Ship-To's" - Y/N

If Show = N and RowNumber <> 1 then I do not want lines 2 - X to show.

This works. However, it treats the display as if all the lines are there. Page 1 might show only 3 accounts, Page2 might show

10 accounts, Page 3 shows 5 accounts, etc, etc

Using a Parameter how can I tell it to include all the lines or just the 1st line of each group of customer#'s. If Ship-To = N

then each page should have 50 Master accounts with no ship-to data.

Account Ship-to

A

B

C

D

E
F

...

or

A 1

2

3

B 1

2

C 1

It's almost as if I need to condition the query to get the data from Customer Master or Customer Master joined to Ship-To

based on the Parameter "Ship-To" (value = Y/N). That way, the correct data is there before it ever trys to format the report.

Detailed explinations/examples needed as I have no knowledge of VB and Reporting Services other than what I can get out of a book.