More ports for SQL Server

Hi,
Is it possible to configure SQL Server to listen to more ports beside 1433?
Or necessarily one port at a time can be used for SQL Server?
Thanks in advance,
LeilaYes you can use port other than 1433. Muliple SQL Server instance should use,
rather must use UNIQUE port number in a Server.
Piku.
"Leila" wrote:
> Hi,
> Is it possible to configure SQL Server to listen to more ports beside 1433?
> Or necessarily one port at a time can be used for SQL Server?
> Thanks in advance,
> Leila
>
>|||In addition, you can have a SQL instance listening on multiple ports. Just
add the additional port(s) in a comma-separated format (e.g. using Server
Network config), and restart the SQL instance.
Linchi
"Leila" wrote:
> Hi,
> Is it possible to configure SQL Server to listen to more ports beside 1433?
> Or necessarily one port at a time can be used for SQL Server?
> Thanks in advance,
> Leila
>
>sql

More ports for SQL Server

Hi,
Is it possible to configure SQL Server to listen to more ports beside 1433?
Or necessarily one port at a time can be used for SQL Server?
Thanks in advance,
LeilaYes you can use port other than 1433. Muliple SQL Server instance should use
,
rather must use UNIQUE port number in a Server.
Piku.
"Leila" wrote:

> Hi,
> Is it possible to configure SQL Server to listen to more ports beside 1433
?
> Or necessarily one port at a time can be used for SQL Server?
> Thanks in advance,
> Leila
>
>|||In addition, you can have a SQL instance listening on multiple ports. Just
add the additional port(s) in a comma-separated format (e.g. using Server
Network config), and restart the SQL instance.
Linchi
"Leila" wrote:

> Hi,
> Is it possible to configure SQL Server to listen to more ports beside 1433
?
> Or necessarily one port at a time can be used for SQL Server?
> Thanks in advance,
> Leila
>
>

More Materialized View questions

sql2k sp3
Im having a really hard time getting my head into
Materialized Views.
Accoring to BOL:
When a unique clustered index is created on a view, the
view is executed and the result set is stored in the
database in the same way a table with a clustered index is
stored.
Now this sounds good and makes sense. However, say I have
a Materialized View:
create view MatTest
as
select * from table1
Create unique clustered index... on MatTest(Column1)
So, if Im reading BOL correctly, the results are stored in
the DB already. But then what happens when I am selecting
from my view and put a WHERE clause on the SELECT?
select * from MatTest
where column1 = bla
and column2 = bla
Wouldn't the view then have to rebuild the results just
like a normal query would?
Also, (dont know if I would do this one, just curious)
what if I want to use Dynamic SQL to call my view from an
SP and be able to feed in Parameters? Is the clustered
index still used?
Thanks to all for your insights.
TIA, ChrisRChris,
Let's say we have the following non-materialized view:
CREATE VIEW NonMaterialized
AS
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
When you run a query against the view, e.g.:
SELECT *
FROM NonMaterialized
WHERE ColA = 5
What really happens is that your query is re-written first as:
SELECT *
FROM
(SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
WHERE ColA = 5
And then most likely optimized into:
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
WHERE ColA = 5
So using this view, you still do all of the work, at query time, as you
would selecting from the base tables.
Now, assume that we've materialized the view. SQL Server now stores the
entire results of the view in the same way that it stores table data. So
when you exercise this query:
SELECT *
FROM Materialized
WHERE ColA = 5
There are no base tables to JOIN. This query can use indexes directly
against the view, as if it were a base table itself.
Does that make more sense?
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
> sql2k sp3
> Im having a really hard time getting my head into
> Materialized Views.
> Accoring to BOL:
> When a unique clustered index is created on a view, the
> view is executed and the result set is stored in the
> database in the same way a table with a clustered index is
> stored.
>
> Now this sounds good and makes sense. However, say I have
> a Materialized View:
> create view MatTest
> as
> select * from table1
> Create unique clustered index... on MatTest(Column1)
>
> So, if Im reading BOL correctly, the results are stored in
> the DB already. But then what happens when I am selecting
> from my view and put a WHERE clause on the SELECT?
> select * from MatTest
> where column1 = bla
> and column2 = bla
> Wouldn't the view then have to rebuild the results just
> like a normal query would?
> Also, (dont know if I would do this one, just curious)
> what if I want to use Dynamic SQL to call my view from an
> SP and be able to feed in Parameters? Is the clustered
> index still used?
>
> Thanks to all for your insights.
> TIA, ChrisR
>|||Perfect. Thats what I was missing. Also, BOL doesnt metion
needing Enterprise Ed to use these, but I seem to recall
reading that its a must. Do you know?
>--Original Message--
>Chris,
>Let's say we have the following non-materialized view:
>CREATE VIEW NonMaterialized
>AS
> SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
>When you run a query against the view, e.g.:
>SELECT *
>FROM NonMaterialized
>WHERE ColA = 5
>What really happens is that your query is re-written
first as:
>SELECT *
>FROM
> (SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
>WHERE ColA = 5
>And then most likely optimized into:
>SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
> WHERE ColA = 5
>So using this view, you still do all of the work, at
query time, as you
>would selecting from the base tables.
>Now, assume that we've materialized the view. SQL Server
now stores the
>entire results of the view in the same way that it stores
table data. So
>when you exercise this query:
>SELECT *
>FROM Materialized
>WHERE ColA = 5
>There are no base tables to JOIN. This query can use
indexes directly
>against the view, as if it were a base table itself.
>Does that make more sense?
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
>> sql2k sp3
>> Im having a really hard time getting my head into
>> Materialized Views.
>> Accoring to BOL:
>> When a unique clustered index is created on a view, the
>> view is executed and the result set is stored in the
>> database in the same way a table with a clustered index
is
>> stored.
>>
>> Now this sounds good and makes sense. However, say I
have
>> a Materialized View:
>> create view MatTest
>> as
>> select * from table1
>> Create unique clustered index... on MatTest(Column1)
>>
>> So, if Im reading BOL correctly, the results are stored
in
>> the DB already. But then what happens when I am
selecting
>> from my view and put a WHERE clause on the SELECT?
>> select * from MatTest
>> where column1 = bla
>> and column2 = bla
>> Wouldn't the view then have to rebuild the results just
>> like a normal query would?
>> Also, (dont know if I would do this one, just curious)
>> what if I want to use Dynamic SQL to call my view from
an
>> SP and be able to feed in Parameters? Is the clustered
>> index still used?
>>
>> Thanks to all for your insights.
>> TIA, ChrisR
>
>.
>|||"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
No, I have indexed views running on Standard Edition servers. I believe
that the restriction is that Standard Edition will not automatically
consider them when you query base tables. For instance, if you created this
view:
CREATE VIEW AView
AS
SELECT ColA, COUNT_BIG(*) AS RowCount
FROM dbo.YourTableA
GROUP BY ColA
... and then you materialized it, and then you did the following query:
SELECT ColA, COUNT(*) AS RowCount
FROM dbo.YourTableA
WHERE ColA = 'ABC'
GROUP BY ColA
Enterprise Edition will actually be able to automatically use AView to
answer the query. In Standard Edition, to use the view, you'd have to do:
SELECT ColA, RowCount
FROM AView|||Yes you need EE for the view to be used automatically. Otherwise you must
specify the NOEXPAND hint.
--
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
>
> >--Original Message--
> >Chris,
> >
> >Let's say we have the following non-materialized view:
> >
> >CREATE VIEW NonMaterialized
> >AS
> > SELECT TblA.ColA, TblB.ColB
> > FROM TblA
> > JOIN TblB ON TblA.PK = TblB.PK
> >
> >When you run a query against the view, e.g.:
> >
> >SELECT *
> >FROM NonMaterialized
> >WHERE ColA = 5
> >
> >What really happens is that your query is re-written
> first as:
> >
> >SELECT *
> >FROM
> > (SELECT TblA.ColA, TblB.ColB
> > FROM TblA
> > JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
> >WHERE ColA = 5
> >
> >And then most likely optimized into:
> >
> >SELECT TblA.ColA, TblB.ColB
> > FROM TblA
> > JOIN TblB ON TblA.PK = TblB.PK
> > WHERE ColA = 5
> >
> >So using this view, you still do all of the work, at
> query time, as you
> >would selecting from the base tables.
> >
> >Now, assume that we've materialized the view. SQL Server
> now stores the
> >entire results of the view in the same way that it stores
> table data. So
> >when you exercise this query:
> >
> >SELECT *
> >FROM Materialized
> >WHERE ColA = 5
> >
> >There are no base tables to JOIN. This query can use
> indexes directly
> >against the view, as if it were a base table itself.
> >
> >Does that make more sense?
> >
> >
> >"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> Im having a really hard time getting my head into
> >> Materialized Views.
> >>
> >> Accoring to BOL:
> >>
> >> When a unique clustered index is created on a view, the
> >> view is executed and the result set is stored in the
> >> database in the same way a table with a clustered index
> is
> >> stored.
> >>
> >>
> >> Now this sounds good and makes sense. However, say I
> have
> >> a Materialized View:
> >>
> >> create view MatTest
> >> as
> >> select * from table1
> >>
> >> Create unique clustered index... on MatTest(Column1)
> >>
> >>
> >> So, if Im reading BOL correctly, the results are stored
> in
> >> the DB already. But then what happens when I am
> selecting
> >> from my view and put a WHERE clause on the SELECT?
> >>
> >> select * from MatTest
> >> where column1 = bla
> >> and column2 = bla
> >>
> >> Wouldn't the view then have to rebuild the results just
> >> like a normal query would?
> >>
> >> Also, (dont know if I would do this one, just curious)
> >> what if I want to use Dynamic SQL to call my view from
> an
> >> SP and be able to feed in Parameters? Is the clustered
> >> index still used?
> >>
> >>
> >> Thanks to all for your insights.
> >>
> >> TIA, ChrisR
> >>
> >
> >
> >.
> >

More Materialized View questions

sql2k sp3
Im having a really hard time getting my head into
Materialized Views.
Accoring to BOL:
When a unique clustered index is created on a view, the
view is executed and the result set is stored in the
database in the same way a table with a clustered index is
stored.
Now this sounds good and makes sense. However, say I have
a Materialized View:
create view MatTest
as
select * from table1
Create unique clustered index... on MatTest(Column1)
So, if Im reading BOL correctly, the results are stored in
the DB already. But then what happens when I am selecting
from my view and put a WHERE clause on the SELECT?
select * from MatTest
where column1 = bla
and column2 = bla
Wouldn't the view then have to rebuild the results just
like a normal query would?
Also, (dont know if I would do this one, just curious)
what if I want to use Dynamic SQL to call my view from an
SP and be able to feed in Parameters? Is the clustered
index still used?
Thanks to all for your insights.
TIA, ChrisR
Chris,
Let's say we have the following non-materialized view:
CREATE VIEW NonMaterialized
AS
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
When you run a query against the view, e.g.:
SELECT *
FROM NonMaterialized
WHERE ColA = 5
What really happens is that your query is re-written first as:
SELECT *
FROM
(SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
WHERE ColA = 5
And then most likely optimized into:
SELECT TblA.ColA, TblB.ColB
FROM TblA
JOIN TblB ON TblA.PK = TblB.PK
WHERE ColA = 5
So using this view, you still do all of the work, at query time, as you
would selecting from the base tables.
Now, assume that we've materialized the view. SQL Server now stores the
entire results of the view in the same way that it stores table data. So
when you exercise this query:
SELECT *
FROM Materialized
WHERE ColA = 5
There are no base tables to JOIN. This query can use indexes directly
against the view, as if it were a base table itself.
Does that make more sense?
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
> sql2k sp3
> Im having a really hard time getting my head into
> Materialized Views.
> Accoring to BOL:
> When a unique clustered index is created on a view, the
> view is executed and the result set is stored in the
> database in the same way a table with a clustered index is
> stored.
>
> Now this sounds good and makes sense. However, say I have
> a Materialized View:
> create view MatTest
> as
> select * from table1
> Create unique clustered index... on MatTest(Column1)
>
> So, if Im reading BOL correctly, the results are stored in
> the DB already. But then what happens when I am selecting
> from my view and put a WHERE clause on the SELECT?
> select * from MatTest
> where column1 = bla
> and column2 = bla
> Wouldn't the view then have to rebuild the results just
> like a normal query would?
> Also, (dont know if I would do this one, just curious)
> what if I want to use Dynamic SQL to call my view from an
> SP and be able to feed in Parameters? Is the clustered
> index still used?
>
> Thanks to all for your insights.
> TIA, ChrisR
>
|||Perfect. Thats what I was missing. Also, BOL doesnt metion
needing Enterprise Ed to use these, but I seem to recall
reading that its a must. Do you know?

>--Original Message--
>Chris,
>Let's say we have the following non-materialized view:
>CREATE VIEW NonMaterialized
>AS
> SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
>When you run a query against the view, e.g.:
>SELECT *
>FROM NonMaterialized
>WHERE ColA = 5
>What really happens is that your query is re-written
first as:
>SELECT *
>FROM
> (SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK) AS NonMaterialized
>WHERE ColA = 5
>And then most likely optimized into:
>SELECT TblA.ColA, TblB.ColB
> FROM TblA
> JOIN TblB ON TblA.PK = TblB.PK
> WHERE ColA = 5
>So using this view, you still do all of the work, at
query time, as you
>would selecting from the base tables.
>Now, assume that we've materialized the view. SQL Server
now stores the
>entire results of the view in the same way that it stores
table data. So
>when you exercise this query:
>SELECT *
>FROM Materialized
>WHERE ColA = 5
>There are no base tables to JOIN. This query can use
indexes directly
>against the view, as if it were a base table itself.
>Does that make more sense?
>
>"ChrisR" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:8f7a01c49680$3eb535e0$a301280a@.phx.gbl...
is[vbcol=seagreen]
have[vbcol=seagreen]
in[vbcol=seagreen]
selecting[vbcol=seagreen]
an
>
>.
>
|||"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
No, I have indexed views running on Standard Edition servers. I believe
that the restriction is that Standard Edition will not automatically
consider them when you query base tables. For instance, if you created this
view:
CREATE VIEW AView
AS
SELECT ColA, COUNT_BIG(*) AS RowCount
FROM dbo.YourTableA
GROUP BY ColA
... and then you materialized it, and then you did the following query:
SELECT ColA, COUNT(*) AS RowCount
FROM dbo.YourTableA
WHERE ColA = 'ABC'
GROUP BY ColA
Enterprise Edition will actually be able to automatically use AView to
answer the query. In Standard Edition, to use the view, you'd have to do:
SELECT ColA, RowCount
FROM AView
|||Yes you need EE for the view to be used automatically. Otherwise you must
specify the NOEXPAND hint.
Andrew J. Kelly SQL MVP
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:8f3001c49686$bc967ea0$a501280a@.phx.gbl...[vbcol=seagreen]
> Perfect. Thats what I was missing. Also, BOL doesnt metion
> needing Enterprise Ed to use these, but I seem to recall
> reading that its a must. Do you know?
>
> first as:
> query time, as you
> now stores the
> table data. So
> indexes directly
> message
> is
> have
> in
> selecting
> an

more expression syntax

Hi,

This is a follow up to an earlier question. I'm having a heck of a time here. What I'm doing is reading a value into a SSIS variable and trying to evaluate it. I know the value is huge (over 4000 chars) and what I think should happen in my package isn't (I guess because this variable is so big).

What I WANTED to do is a straight character check:

@.[User::xml_output] == "ABC"

but that wasn't working... so I deceided to try the len function.

However xml_output is too big and it's also not working. How would I check to see if the len is greater than 17 characters? Here is what I have so far...and none of it works.

len(trim(DT_WSTR,18,1252)@.[User::xml_output])) > 17

len(trim(@.[User::xml_output])) > 17

(DT_WSTR,18)@.[User::xml_output] > 17

I just want to trim @.[User::xml_output] and see if it's greater than 17 characters. Any help would be appreciated.

Thanks,

Phil

Well, you can do it with script. Phil is laughing, but I'm serious. Script can handle strings over 4,000 chars, but expressions apparently cannot.

I don't know if you're trying to do this in a data flow or a control flow, but here is the code for a control flow script task that gets the length of your variable and puts it into an integer variable named "length". You would of course need to list "xml_output" and "length" in the ReadOnlyVariables and ReadWriteVariables properties respectively.

Code Snippet

Public Sub Main()
Dts.Variables("length").Value = Dts.Variables("xml_output").Value.ToString.Length
Dts.TaskResult = Dts.Results.Success
End Sub

|||Thanks.|||

JayH wrote:

Well, you can do it with script. Phil is laughing, but I'm serious. Script can handle strings over 4,000 chars, but expressions apparently cannot.

If you're referring to me, well, I'm always in favor of using the right tool for the job. That includes being efficient. This solutions works perfectly because his string is over 4,000 characters.|||

I figured you guys worked together or something

It's actually a huge shortcoming of expression syntax to not be able to include over 4000 characters. I was trying to evaluate some XML output and it was over the 4k limit and it basically always assumed it was under 17 characters because that's what my expression syntax was looking for either greater than or less than 17 characters. The less than would always hit because it just ignored the large size of the variable. Work-arounds are good, but in this case I think the language needs to adapt....

Phil

sql

More conversation_endpoints

So I took the time to build a reproduction of the conversation_endpoint problem that was discussed in another thread. I build two databases, with a send and receive queue. This is essentially the way the code works here at my site. I have a script near the bottom that sends messages every 5 minutes for 2 hours. If there is any logic that removes conversation_endpoints 30 min then the Message record table will show them.

Please let me know what I am doing wrong, so I can change my production code to help eliminate the large buildup in the sys.conversation_endpoints.

Thanks!

use master

go

if exists ( select * from sys.databases where name = 'SBSource' )

drop database SBSource

go

if exists ( select * from sys.databases where name = 'SBTarget' )

drop database SBTarget

go

-- Setup environment for test

create database SBSource

GO

ALTER DATABASE SBSource SET ENABLE_BROKER

ALTER DATABASE SBSource SET TRUSTWORTHY ON

GO

create database SBTarget

GO

ALTER DATABASE SBTarget SET ENABLE_BROKER

ALTER DATABASE SBTarget SET TRUSTWORTHY ON

GO

use SBSource

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[SourceQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBSourceTest] authorization [dbo]

ON QUEUE [dbo].[SourceQueue]

( [Test] );

CREATE ROUTE [ToTarget] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBTargetTest' , ADDRESS = N'LOCAL';

GO

create procedure dbo.ProcessEndDialogMessages

as

begin

set nocount on;

declare @.conversation_handle uniqueidentifier,

@.message_type sysname,

@.message_body xml;

begin transaction;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

while @.conversation_handle IS NOT NULL

begin

end conversation @.conversation_handle;

commit;

begin transaction;

set @.conversation_handle = null;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

end

commit;

end

go

Alter QUEUE dbo.[SourceQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessEndDialogMessages, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go

use SBTarget

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[TargetQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBTargetTest] authorization [dbo]

ON QUEUE [dbo].[TargetQueue]

( [Test] );

CREATE ROUTE [ToSource] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBSourceTest' , ADDRESS = N'LOCAL';

GO

create table dbo.MessageRecord ( Conversation_handle uniqueidentifier, Inserted datetime )

go

create procedure dbo.ProcessTargetQueue

as

begin

set nocount on;

declare @.conversation_handle uniqueidentifier,

@.message_type sysname,

@.message_body xml;

begin transaction;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

while @.conversation_handle IS NOT NULL

begin

insert into dbo.MessageRecord ( Conversation_handle, Inserted ) values ( @.conversation_handle, getdate() );

end conversation @.conversation_handle;

commit;

begin transaction;

set @.conversation_handle = null;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

end

commit;

end

GO

Alter QUEUE dbo.[TargetQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessTargetQueue, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go

use sbsource

go

-- start sending messages, check count in conv_endpoints along the way

set xact_abort on

set nocount on

declare @.EndAt datetime,

@.msg xml,

@.ch uniqueidentifier;

set @.EndAt = DATEADD( hh, 2, getdate() )

set @.msg = '<message>dfsafa</message>';

while getdate() < @.EndAt

begin

set @.ch = null;

begin transaction;

begin dialog conversation @.ch

from service [SBSourceTest]

to service 'SBTargetTest'

on contract [Test]

with

encryption=off;

send on conversation @.ch message type [msgTest] (@.msg);

note the abscence of an end conversation, so no fire and forget!

commit;

waitfor delay '00:05:00'

end

GO

-- check on data after complete.

select state, count(*)

from SBSource.sys.conversation_endpoints

group by state;

select state, count(*)

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

group by state;

select m.*, c.*

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

select *

from SBTarget.dbo.targetqueue

Please use the SQLServer feedback site to report this issue: https://connect.microsoft.com/SQLServer/Feedback

Thanks,
~ Remus

More bugs in SSRS 2k5 ....

Here is another one thats been bothering me for quite some time and I believe for user with regional and language settings to non-US English.

I have my regional settings set to Canadian English. I am using Calender controls to pick up Start, End dates in my report. When I pick a date from calender it automatically converts it behind the scenes to US English I believe. Therefore when I run the report, I get the result set, say from Oct 8 instead of Aug 10. Further, if I select the date as 13 Aug, the following error gets thrown (as a result that the SSRS is not able to identify 13 being any month of course)

"An error occured during local report processing. The value provided for the report parameter 'StartDate' is not valid for its type".

Is there a solution to this problem? The underlying date type is DateTime, so why should it matter which regional setting I use?

Comments anyone?

EDIT

The SSRS controls comes with Calendar property you need to use that, because if you use the NOW SSRS will run your report with current date time in your box, I have used it. But I think you have mistaken TimeZone for DateTime, you can get DateTime in SQL Server while TimeZone is .NET there are work around solutions now and it is implemented in VS2008. Try the thread below for details.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

I never said I am using TimeZone. Let me try to explain once more. I have my regional settings in Control Panel >> Regional and Language Options>>Regional Options>>Standards and Formats - set to English(Canada) and Location set to Canada, further in the Advanced Tab its also set to English (Canada).

Now back in report, I have defined two report parameters: StartDate and EndDate. Their data type is set to DataTime. Therefore when I preview the report, the parameters display Calenders next to the boxes automatically. I pick a date, say 14 Aug 2007 from this calender. The date is formatted as 14/08/2007 in the text box (Canadian format dd/mm/yyyy). Then I hit the View Report button, and its then that the error gets thrown because for some reason report server/sql server does not accept this date format. Notice that I have not done any thing fancy with the date time parameter, simply out of the box usage.

So, I did this test. I switched the computer settings to English (United States) and wolla...everythings works fine. However thats not desirable as this would require the users to use US-English is all applications such as Word, Excel etc.

The thread you provided the link for does not address my problem unfortunately, it talks about TimeZone in .NET.

I suspect that users with English (UK, AUS, NZ) are also facing same problem. I would highly appreciate if some one can provide a solution to this problem.

|||

Moved to the reporting services forum...

|||? Dint I post in SSRS in first place?

|||

SQL Server can do what you want you just need the correct table design, 2005 also comes with GETUTDATE function you can use. Run a search in the BOL for GETUTCDATE and CAST and CONVERT, there is a guideline for SQL Server DateTime design and how to get datetime in the format you want in the links below.

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||To add to this discussion, I've noticed that Report Manager and Report Server behave differently when viewing the same report. One suffers from the bug and the other doesn't.

|||

Well, I do not have SQL Server SP2 installed. Not sure if installing that would make a diff and I can't do that without DBA's consent.

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||

Ok, so may be who ever wrote this Calender control had a grudge against us Canadians. Jus kidding

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

sql

More bugs in SSRS 2k5 ....

Here is another one thats been bothering me for quite some time and I believe for user with regional and language settings to non-US English.

I have my regional settings set to Canadian English. I am using Calender controls to pick up Start, End dates in my report. When I pick a date from calender it automatically converts it behind the scenes to US English I believe. Therefore when I run the report, I get the result set, say from Oct 8 instead of Aug 10. Further, if I select the date as 13 Aug, the following error gets thrown (as a result that the SSRS is not able to identify 13 being any month of course)

"An error occured during local report processing. The value provided for the report parameter 'StartDate' is not valid for its type".

Is there a solution to this problem? The underlying date type is DateTime, so why should it matter which regional setting I use?

Comments anyone?

EDIT

The SSRS controls comes with Calendar property you need to use that, because if you use the NOW SSRS will run your report with current date time in your box, I have used it. But I think you have mistaken TimeZone for DateTime, you can get DateTime in SQL Server while TimeZone is .NET there are work around solutions now and it is implemented in VS2008. Try the thread below for details.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1860968&SiteID=1

|||

I never said I am using TimeZone. Let me try to explain once more. I have my regional settings in Control Panel >> Regional and Language Options>>Regional Options>>Standards and Formats - set to English(Canada) and Location set to Canada, further in the Advanced Tab its also set to English (Canada).

Now back in report, I have defined two report parameters: StartDate and EndDate. Their data type is set to DataTime. Therefore when I preview the report, the parameters display Calenders next to the boxes automatically. I pick a date, say 14 Aug 2007 from this calender. The date is formatted as 14/08/2007 in the text box (Canadian format dd/mm/yyyy). Then I hit the View Report button, and its then that the error gets thrown because for some reason report server/sql server does not accept this date format. Notice that I have not done any thing fancy with the date time parameter, simply out of the box usage.

So, I did this test. I switched the computer settings to English (United States) and wolla...everythings works fine. However thats not desirable as this would require the users to use US-English is all applications such as Word, Excel etc.

The thread you provided the link for does not address my problem unfortunately, it talks about TimeZone in .NET.

I suspect that users with English (UK, AUS, NZ) are also facing same problem. I would highly appreciate if some one can provide a solution to this problem.

|||

Moved to the reporting services forum...

|||? Dint I post in SSRS in first place?|||

SQL Server can do what you want you just need the correct table design, 2005 also comes with GETUTDATE function you can use. Run a search in the BOL for GETUTCDATE and CAST and CONVERT, there is a guideline for SQL Server DateTime design and how to get datetime in the format you want in the links below.

http://www.karaszi.com/SQLServer/info_datetime.asp

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

|||This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||To add to this discussion, I've noticed that Report Manager and Report Server behave differently when viewing the same report. One suffers from the bug and the other doesn't.|||

Well, I do not have SQL Server SP2 installed. Not sure if installing that would make a diff and I can't do that without DBA's consent.

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

|||

Ok, so may be who ever wrote this Calender control had a grudge against us Canadians. Jus kidding

D Wall wrote:

This is interesting as I have reports that are used by people with a variety of regional settings, probably at least half of them set to dd/mm/yyyy.

Doing it exactly as you describe works correctly for us, UK users set the date in the format that is consistent with their regional settings, US as mm/dd/yyyy, and both return the same results.

I will say that we have seen at odd times that if you pick too fast from the calendar control it doesn't seem to quite register (even though the correct date shows) and I've gotten that error. Don't know if that helps much.

What version number are you at?

Monthly parameter expressions [Formerly:Queried parameters]

Hello,

I need to be able to set the date parameters of a report dynamically when it is run based on system time. The problem I am having is being able to compare the dates (StartDate & EndDate) against [Service Date 1]. Essentially this report will only pull the current month's data.

The date fields being created with the GETDATE, DATEADD & DATEDIFF functions are working correctly. Do I need to create a separate dataset to be able to run the parameters automatically in the actual report?

Any help would be greatly appreciated!

SELECT TodaysDate =GetDate()-2,dbo.[Billing Detail].[Service Date 1], DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0) AS StartDate, DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0)) AS EndDate, dbo.[Billing Detail].Billing, dbo.[Billing Detail].Chart, dbo.[Billing Detail].Item,
dbo.[Billing Detail].[Sub Item], dbo.Patient.[Patient Code], dbo.Patient.[Patient Type], dbo.[Billing Header].Charges, dbo.Practice.Name
FROM dbo.[Billing Detail] INNER JOIN
dbo.Patient ON dbo.[Billing Detail].Chart = dbo.Patient.[Chart Number] INNER JOIN
dbo.[Billing Header] ON dbo.[Billing Detail].Billing = dbo.[Billing Header].Billing CROSS JOIN
dbo.Practice
WHERE (dbo.[Billing Detail].Item = 0) AND (dbo.[Billing Detail].[Sub Item] = 0) AND (dbo.[Billing Detail].[Service Date 1] Between StartDate AND EndDate

Phorest,

You should be able to add the parameters to your query. If you are going against SQL Server, you can replace your parameters with @.StartDate AND @.EndDate. Then in the properies of the dataset, you can assign those parameters to Parameters!StartDate.Value and Parameters!EndDate.Value, respectively.

Jessica

|||

Thanks for your reply!

OK,

I think what I need to do is write the expression as a non-queried default value. However when I paste in what I know works in SQL Management Studio it returns an error "Name 'mm' is not declared"

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

I tried putting an integer after DATEADD(mm, X , 102 DATEDIFF... but i can't get beyond intellisense. How can I fix my expression to work with Reporting Services?

What I need is to have expressions to choose the first day of the month to the last day of the same month compared to NOW()

|||

Apparently that is the trick to use non-queried default values as an expression, However what I posted yesterday will not work as an expression due to the expressions limitations in SSRS:

<@.StartDate> =DATEADD(mm, DATEDIFF(mm, 0, DATEADD(yy, 0, GETDATE())), 0)

<@.EndDate> =DATEADD(dd, - 1, DATEADD(mm, DATEDIFF(mm, -1, GETDATE()), 0))

Now I am using:

<@.StartDate> =DATEADD("D", -30, NOW())

<@.EndDate> =DATEADD("D", 1, NOW())

After much searching and experimentation I can get this to work well, but it isn't exactly what I want. Does any one have any tips as to being able to write the expression to select the first day of the current month and last day of the month?

It seems to be just beyond my grasp at this time...

Thanks!

|||

Phorest,

I'm afraid I misunderstood what you're trying to do. If you want a query that returns rows where the [Billing Detail].[Service Date 1] is between the start and the end of the current month, you can do that all in SQL.

It would look something similar to:

WHERE dbo.[Billing Detail].[Service Date 1]

BETWEEN dateadd(mm, datediff(mm,0,getdate()), 0)

AND dateadd(ms,-3,dateadd(mm, datediff(m,0,getdate() ) + 1, 0))

Does that work for you?

Jessica

|||

I'll have to try that in the SQL, though I was more after an expression more as a datetime datatype so it picks all the dates in the current month only and the user can then adjust the parameter manually after the initial running of the report if they so choose.

Thanks!

|||

I found what I was looking for here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1581230&SiteID=1

In the Report Parameters properties I set the DataType to DateTime and using the Default Values, Non-Queried radio button set the expressions like the following:

@.StartDate =DateSerial(Year(NOW()), Month(NOW)) +0,1) gives me the first date of the current month.

@.EndDate =DateSerial(Year(NOW()), Month(NOW)) +1,0) gives me the last date of the current month.

All is wellnow!

sql

Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler ?. What is the unit of the
Duration? sec or ms ?
Thanks.
The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler ?. What is the unit of the
> Duration? sec or ms ?
> Thanks.
|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.

>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>
>.
>
|||As a standard I always put start time and end time as the first two columns in my traces, this helps me do other things too.
Hope this helps

Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler '. What is the unit of the
Duration? sec or ms ?
Thanks.The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler '. What is the unit of the
> Duration? sec or ms ?
> Thanks.|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.

>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>
>.
>|||As a standard I always put start time and end time as the first two columns
in my traces, this helps me do other things too.
Hope this helps

Monitoring Transaction Time

How do I monitor how long a transaction/SQL Statement
takes in SQL Profiler '. What is the unit of the
Duration? sec or ms ?
Thanks.The unit of duration in SQL Profile is milliseconds.
The duration is the total duration of the transaction, form the moment the
request from the client came in until the moment that the receipt of the
last data has been acknowledged by the client. If you just want the
processor time, look at the CPU column in your profiler trace.
--
Jacco Schalkwijk
SQL Server MVP
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
> How do I monitor how long a transaction/SQL Statement
> takes in SQL Profiler '. What is the unit of the
> Duration? sec or ms ?
> Thanks.|||No. I just want to see how long each transaction/SQL
statement takes.
Thanks.
>--Original Message--
>The unit of duration in SQL Profile is milliseconds.
>The duration is the total duration of the transaction,
form the moment the
>request from the client came in until the moment that the
receipt of the
>last data has been acknowledged by the client. If you
just want the
>processor time, look at the CPU column in your profiler
trace.
>--
>Jacco Schalkwijk
>SQL Server MVP
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:f08101c43da1$f5682a50$a401280a@.phx.gbl...
>> How do I monitor how long a transaction/SQL Statement
>> takes in SQL Profiler '. What is the unit of the
>> Duration? sec or ms ?
>> Thanks.
>
>.
>|||As a standard I always put start time and end time as the first two columns in my traces, this helps me do other things too
Hope this helpssql

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
Thanks and Regards,
Prasanth
You can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
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
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth
sql

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
--
Thanks and Regards,
PrasanthYou can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
--
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
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth

Monitoring Statistics inside a DTS package

Hi,
Can some one tell me if I can monitor the statistics information for each
query
running inside a DTS package. The DTS package takes a long time to complete.
I need this help to fine tune the DTS pacakge.
--
Thanks and Regards,
PrasanthYou can
Set Statistics IO on
Set statistics Time on
etc...but save the output to a file..
you might also run Profiler to capture additional information.
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
"Prasanth" <Prasanth@.discussions.microsoft.com> wrote in message
news:8E3F74FE-5505-4A44-9E1E-0F1948AF3D18@.microsoft.com...
> Hi,
> Can some one tell me if I can monitor the statistics information for each
> query
> running inside a DTS package. The DTS package takes a long time to
complete.
> I need this help to fine tune the DTS pacakge.
> --
> Thanks and Regards,
> Prasanth

Monitoring service broker

Hi!
We have next problem in production.
We have one target that accepts messages from many initiators. Some time target can not get message from some initiator. It may be due hardware problem, for example technitian switch off router. In general service broker succeed to receive all messages when hardware problem was solved (if it takes less than retantion time on initiator side). I would like to build some job that would look at queue of target and could say (or trigger) that from the initiator A for some period of time target did not get any message or did not succeed to close conversation or it constantly have some connectivity problem (like duplicated message).

Any idea?

There are various traces you can use that indicate potential problems, like Message Drop, Message Classify with the subclass Delayed or Connection with the subclass Closing (unless is a ordinary timeout close due to lack of activity). Using the sp_trace_... procedure(s) some automation can be built around them.

An easier solution is to monitor the sys.transmission_queue on all parties involved. If messages are building up, is an indication of a problem that needs to be investigated.

Monitoring Query Performance

Is there any table in SQL Server 2000 which lists the queries currently
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.
Not really. You have some information in master..syscacheobjects, but that are only the cached plans
(those that are cached in the first place, of course). You can have a profiler trace running to pick
up the measures you are interested in and to your tuning based on that profiler trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, number
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.

Monitoring Query Performance

Is there any table in SQL Server 2000 which lists the queries currently
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.Not really. You have some information in master..syscacheobjects, but that are only the cached plans
(those that are cached in the first place, of course). You can have a profiler trace running to pick
up the measures you are interested in and to your tuning based on that profiler trace.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, number
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.

Monitoring Query Performance

Is there any table in SQL Server 2000 which lists the queries currently
cached in memory and statistics on those queries e.g. execution time, number
of executions cost, etc. I would like to be able to determine the queries
that have the highest cost to target for performance tuning first.Not really. You have some information in master..syscacheobjects, but that a
re only the cached plans
(those that are cached in the first place, of course). You can have a profil
er trace running to pick
up the measures you are interested in and to your tuning based on that profi
ler trace.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Simon" <Simon@.discussions.microsoft.com> wrote in message
news:C5531A9C-7344-4734-B041-7A83FA31F5F8@.microsoft.com...
> Is there any table in SQL Server 2000 which lists the queries currently
> cached in memory and statistics on those queries e.g. execution time, numb
er
> of executions cost, etc. I would like to be able to determine the queries
> that have the highest cost to target for performance tuning first.

Monitoring progress on a join

I'm doing a big old join on one table with 10,000,000 rows, and
another with
400,000 rows. As you can imagine, this is taking a long time.

Is there any way to monitor the progress of the join after executing
the sql statement (more specifically, from code)?

(Oh, and any good practices for speeding up this join would be
appreciated, too).

Thanks,

AndrewAndrew (ajperrins@.hotmail.com) writes:
> I'm doing a big old join on one table with 10,000,000 rows, and
> another with
> 400,000 rows. As you can imagine, this is taking a long time.
> Is there any way to monitor the progress of the join after executing
> the sql statement (more specifically, from code)?

If it's a SELECT statement that produces a result set, I don't think
there is much you can monitor. You can keep an on the cpu, physical_io
and memusage columns in sysprocesses, but since you don't know what
the target values, you can only see that the process is working, but
not how much work that is left.

If you are also inserting the data into a table, a SELECT with NOLOCK
on the table can give some progress indication. On the INSERT that
is, so if finding the qualifying rows is what takes time, you will
still not see much.

> (Oh, and any good practices for speeding up this join would be
> appreciated, too).

Without any knowledge about the query and the tables, it is difficult
to give precise advice. But selective indexes for the WHERE condition.
If you are not including too many columns, a covering index or two may
be worth investigating.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In addition to Erland's notes: if your query is about the only thing
running on the machine, and you have collected information from previous
runs, then you can use

select @.@.cpu_busy, @.@.io_busy

to monitor the cpu and i/o usage before and during your execution, and
deduct the progress.

Hope this helps,
Gert-Jan

Andrew wrote:
> I'm doing a big old join on one table with 10,000,000 rows, and
> another with
> 400,000 rows. As you can imagine, this is taking a long time.
> Is there any way to monitor the progress of the join after executing
> the sql statement (more specifically, from code)?
> (Oh, and any good practices for speeding up this join would be
> appreciated, too).
> Thanks,
> Andrew|||On 15 Sep 2003 12:01:53 -0700 in comp.databases.ms-sqlserver,
ajperrins@.hotmail.com (Andrew) wrote:

>I'm doing a big old join on one table with 10,000,000 rows, and
>another with
>400,000 rows. As you can imagine, this is taking a long time.
>Is there any way to monitor the progress of the join after executing
>the sql statement (more specifically, from code)?
>(Oh, and any good practices for speeding up this join would be
>appreciated, too).

The tools you want are in Query Analyser: Show Execution Plan, also
Index analysis (on that data you might want to leave that running
overnight <g>). Don't know about "from code" :-\

--
A)bort, R)etry, I)nfluence with large hammer.

(replace sithlord with trevor for email)