Controlling the language returned from Cubes

Hi!
Hopefully someone can help me with this, because I am really stuck.
I am reporting from a cube whose members have captions defined for
multiple languages. I would like to control the language displayed using a
report parameter. These reports will be rendered using Web Services.
Is there a way I can set the language setting of the query dynamically?
Setting the locale for the report did not seem to make any difference :>(.
Any help would be greatly appreciated.
Thanks,
BobBob Hug wrote:
> Hi!
> Hopefully someone can help me with this, because I am really
> stuck. I am reporting from a cube whose members have captions
> defined for multiple languages. I would like to control the language
> displayed using a report parameter. These reports will be rendered
> using Web Services. Is there a way I can set the language setting
> of the query dynamically? Setting the locale for the report did not
> seem to make any difference :>(.
> Any help would be greatly appreciated.
> Thanks,
> Bob
Could the rs:ParameterLanguage be a help for you?
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
roland|||Can't you just ask for the appropriate member caption in whatever language
you want?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmemberprops_8ier.asp.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hug" <rhug@.brassring.com> wrote in message
news:uoIhIAVXEHA.3988@.tk2msftngp13.phx.gbl...
> Hi!
> Hopefully someone can help me with this, because I am really stuck.
> I am reporting from a cube whose members have captions defined for
> multiple languages. I would like to control the language displayed using a
> report parameter. These reports will be rendered using Web Services.
> Is there a way I can set the language setting of the query dynamically?
> Setting the locale for the report did not seem to make any difference :>(.
> Any help would be greatly appreciated.
> Thanks,
> Bob
>|||Brian,
Thanks for the response.
The cube is configured exactly as your reference
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmemberprops_8ier.asp
describes. My problem is how to control the language that is returned in the
report. I believe that the cube is correctly configured. Analysis Manager
and OWC both display the expected language based upon the language setting
of the client. Reporting Services' reports return the expected language when
I set the locale of the Reporting Server. The locale of the Reporting Server
seems to determine the language that the cube returns, perhaps because this
is determining the language of the connection? When I change the Report
Server's locale to French, for example, I get French returned like I expect.
Perhaps this simply a reflection of my lack of MDX knowledge, but I have not
been able to find a way to specify in the query the language I want in
effect. There does not seem to be anything like 'Set Language'.
Any ideas?
Thanks,
Bob
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:e1uhcOaXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> Can't you just ask for the appropriate member caption in whatever language
> you want?
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmemberprops_8ier.asp.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bob Hug" <rhug@.brassring.com> wrote in message
> news:uoIhIAVXEHA.3988@.tk2msftngp13.phx.gbl...
> > Hi!
> > Hopefully someone can help me with this, because I am really stuck.
> > I am reporting from a cube whose members have captions defined for
> > multiple languages. I would like to control the language displayed using
a
> > report parameter. These reports will be rendered using Web Services.
> > Is there a way I can set the language setting of the query
dynamically?
> > Setting the locale for the report did not seem to make any difference
:>(.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks,
> > Bob
> >
> >
>

Control the WorkSheet Names when export to Excel

When I exported the report to Excel File with Multiple Worksheets, can I name
the worksheets with the values of the field I group on?
--
Thanks,
Albert ChowUnfortunately, that's not supported yet.
--
Adrian M.
MCP
"Albert" <Albert@.discussions.microsoft.com> wrote in message
news:DDD282FE-DEB3-41F6-8E1D-AEED8AB4B17C@.microsoft.com...
> When I exported the report to Excel File with Multiple Worksheets, can I
> name
> the worksheets with the values of the field I group on?
> --
> Thanks,
> Albert Chow

Continued issues with SQL2K5 - SP2

I continue to have issues with SP-2 for SQL 2005 suite. It has now gotten so bad, I have multiple installations, including the TOOLS ONLY on my laptop failing, that I am going to stop ALL future installations of SP-2. The COM+ failures have not been resolved that I can determine. I have tried uninstalling, I have tried SP-1 before SP-2, every combination one can find. HELP.

Time: 05/07/2007 13:24:05.631
KB Number: KB921896
Machine: GA029-MDGRAVES
OS Version: Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Package Language: 1033 (ENU)
Package Platform: x86
Package SP Level: 2
Package Version: 3042
Command-line parameters specified:
Cluster Installation: No

**********************************************************************************
Prerequisites Check & Status
SQLSupport: Passed

**********************************************************************************
Products Detected Language Level Patch Level Platform Edition
Setup Support Files ENU 9.00.1399.06 x86
SQL Server Native Client ENU 9.00.3042.00 x86
Client Components ENU RTM 9.00.1399.06 x86 STANDARD
MSXML 6.0 Parser ENU 6.00.3883.8 x86
Backward Compatibility ENU 8.05.1054 x86

**********************************************************************************
Products Disqualified & Reason
Product Reason

**********************************************************************************
Processes Locking Files
Process Name Feature Type User Name PID

**********************************************************************************
Product Installation Status
Product : Setup Support Files
Product Version (Previous): 1399
Product Version (Final) : 3042
Status : Success
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\Redist9_Hotfix_KB921896_SqlSupport.msi.log
Error Number : 0
Error Description :
-
Product : SQL Server Native Client
Product Version (Previous): 3042
Product Version (Final) :
Status : Not Selected
Log File :
Error Description :
-
Product : Client Components
Product Version (Previous): 1399
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB921896_sqlrun_tools.msp.log
Error Number : 29549
Error Description : MSP Error: 29549 Failed to install and configure assemblies c:\Program Files\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2146233087
Error message: Unknown error 0x80131501
Error description: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)
-
Product : MSXML 6.0 Parser
Product Version (Previous): 3883
Product Version (Final) : 6.10.1129.0
Status : Success
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\Redist9_Hotfix_KB921896_msxml6.msi.log
Error Number : 0
Error Description :
-
Product : Backward Compatibility
Product Version (Previous): 1054
Product Version (Final) : 2004
Status : Success
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\Redist9_Hotfix_KB921896_SQLServer2005_BC.msi.log
Error Number : 0
Error Description :
-

**********************************************************************************
Summary
One or more products failed to install, see above for details
Exit Code Returned: 29549

Here's an external blog that discusses the workaround for the issue:

http://geekswithblogs.net/waterbaby/archive/2006/08/03/87048.aspx

Thanks,
Sam Lester (MSFT)

|||The link to XP fix was great. Thanks.

CONTAINS with AND across multiple Columns

How come when I am doing a CONTAINS search across multiple columns on
a table that I have full text indexed I don't get any matches when one
word is contained in one column and the other word is contained in the
other column in the same row of data? Here is a query where first
name is in one column and last name is in another column. Is the only
option to physically store this information concatenated together so
my search will behave as expected?
SELECT *
FROM dbo.Person
WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')
This is by design. In SQL 2000 a freetext search could look across columns.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Spencer" <spencer@.tabbert.net> wrote in message
news:227246c4-bc9e-40b8-a64b-1dd2c2ae8df6@.g21g2000hsh.googlegroups.com...
> How come when I am doing a CONTAINS search across multiple columns on
> a table that I have full text indexed I don't get any matches when one
> word is contained in one column and the other word is contained in the
> other column in the same row of data? Here is a query where first
> name is in one column and last name is in another column. Is the only
> option to physically store this information concatenated together so
> my search will behave as expected?
> SELECT *
> FROM dbo.Person
> WHERE CONTAINS ((FIRST_NAME,LAST_NAME),'"BARRY*" AND "SMITH*"')

CONTAINS and wildcard

Is it a good idea to have multiple contains? I have this query:

Select * from myTable where contains (Col1, 'Africa') or (Col2, 'Africa')

Also, I tried this, didn't return anything:

Select * from myTable where contains (Col1, 'Africa*') or (Col2, 'Africa')

Both Col1 and Col2 has the string 'Africa' and 'African' in it.

--sharifOn Oct 29, 8:59 pm, Sharif Islam <mis...@.npspam.uiuc.eduwrote:

Quote:

Originally Posted by

Is it a good idea to have multiple contains? I have this query:
>
Select * from myTable where contains (Col1, 'Africa') or (Col2, 'Africa')
>
Also, I tried this, didn't return anything:
>
Select * from myTable where contains (Col1, 'Africa*') or (Col2, 'Africa')
>
Both Col1 and Col2 has the string 'Africa' and 'African' in it.
>
--sharif

Hi Sharif,

What version of SQL Server are you using? What is the error message
you are getting? Is the table configured for Full-Text Indexing?

Also - your syntax for the CONTAINS statement is incorrect. Try:
SELECT *
FROM myTable
WHERE CONTAINS(Col1, '"Africa*"')
OR CONTAINS(Col2, '"Africa*"')

Good luck!
J|||jhofmeyr@.googlemail.com wrote:

Quote:

Originally Posted by

On Oct 29, 8:59 pm, Sharif Islam <mis...@.npspam.uiuc.eduwrote:

Quote:

Originally Posted by

>Is it a good idea to have multiple contains? I have this query:
>>
>Select * from myTable where contains (Col1, 'Africa') or (Col2, 'Africa')
>>
>Also, I tried this, didn't return anything:
>>
>Select * from myTable where contains (Col1, 'Africa*') or (Col2, 'Africa')
>>
>Both Col1 and Col2 has the string 'Africa' and 'African' in it.
>>
>--sharif

>
Hi Sharif,
>
What version of SQL Server are you using? What is the error message
you are getting? Is the table configured for Full-Text Indexing?
>
Also - your syntax for the CONTAINS statement is incorrect. Try:
SELECT *
FROM myTable
WHERE CONTAINS(Col1, '"Africa*"')
OR CONTAINS(Col2, '"Africa*"')

ah, that was it, thanks!

--sharif

Consuming Multiple Messages In Parallel from Multiple Windows Services

Hi Remus

What if I need multiple clients to read (RECEIVE) the same message?

Would it be possible?

Thanks

No.

A message can only be received once. Normally the first RECEIVE statement removes it from the queue, so no other RECEIVE can find the same message.

Also there is no way for the clients to specify the message to be received. With a WHERE clause the RECEIVE statement at most can restrict the result set to a particular conversation, but not to a particular message.

And finally RECEIVE statement is always executing in READ COMMITED isolation level, so two clients cannot receive messages from the same conversation group in different transactions, since each RECEIVE will attempt to place an exclusive lock on the conversation group and only one transaction can have an exclusive lock at any given moment.

HTH,
~ Remus

|||

If you are looking at a publish/subscribe type scenario, where you want messages to be delivered to multiple services, you could implement a service that maintains a list of subscriber services and upon receiving a message, sends a copy out each of its subscribers. See the sample on Remus' blog:

http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx

|||

Hi Rushi/Remus

I tested that example, setting the same subscription from two different clients. Then I sent a publication, and read messages.

For what I understand in that example when a client subscribes for a particular publication, his conversationID is saved on a table.

When a publication occurs a procedure sends messages to all subscribers, using that conversationID.

BUT, in case there are two subscriptions and a single client is listening for messages, two identical messages are read by the client.

In case there are two clients listening a lot of confusion, sometimes one client gets two messages, sometimes one, sometimes nothing...

I was expecting , since the conversationID seems to address to a single endpoint, only one message...

|||

Assuming that on a publish/subscribe scenario each client must create a unique subscription, I realize that each client have to create its own queue and service.

The problem is sending messages then.

The initiator should send the same message to all queues, but how? The number of queues created is not defined, is there a way to do it?

Is my theory correct? Or am I on the wrong direction?

Thanks for helping

|||

The subscribers are individual conversations. If they are on the same queue, then you must use the RECEIVE ... FROM queue WHERE conversation_handle = ... syntax to retrieve only the notifications for a given client (subscription).

If you use the RECEIVE w/o a WHERE clause, then the clients will mix the notifications, if they are on the same queue.

In the pub/sub sample at http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx the initiator doesn't know nor need to how many clients/queues are there. It will iterate through subscriptions and send a message to each one. Clients can be on the same queue or on different queue, it doesn't matter. The subscription notifications are all reply messages (from target to initiator, since is the client that initiates the subscription), so the pub/sub service does not need to know upfront how many clients are there, it just sends replies on the existing dialogs.

HTH,
~ Remus

|||

Thanks for the clarification Remus.

Now it's working good!

|||

Hi,

I have a couple of questions to make.

How can i trigger notifications to my application ( C#)

without hanging in WaitFor Operation?

It's possible for broker service to call some remote

object that my application provide ?

Can Publish/Subscribe using Broker Service be used

for a low latency notifications (150ms ) max with milions of

messages published per second?

Thanks in advance

Srgio

Consuming Multiple Messages In Parallel from Multiple Windows Services

Hello All,

After hitting limitations in the SQL CLR world that bar us from invoking COM objects we are forced to use windows services to read the messages off the Service Broker Queues.
Unfortunately we loose the auto activation feature in the Queues, but we can still read messages and perform the SQL work under one transaction.

We are going to attempt to take N messages simultaneously from the Queue, though N instances of a windows service. If the messages send to the queue are one message per conversation, will we be able to achieve having N readers take messages off simultaneounsly?

Thank you very much,

Lubomir

P.S. if anyone has a better approach to obtaining the message in "out of sql code" or invoking external (not assemblies stores in SQL server) code libraries, that would be etremely nice to hear. I have thought about invoking a web service through CLR, but that is probably too much overhead - MSMQ seems much more appealing than a web service;

Lubomir,

Retrieving messages from a queue with the RECEIVE statement should be regarded similar with running an UPDATE statement on a table. Multiple clients (Windows Services in you case) can run concurent updates (receives in your case) as long as they don't try to update the same rows (messages in your case). The difference is that in the RECEIVE case there is a built in mechanism to choose what rows should be updated (i.e. what messages should be dequeued) in order to avoid update conflicts. Each RECEIVE will grab the next available (i.e. not locked) conversation group, lock it, and then retrieve (dequeue) messages from conversations in this group. In fact, one can use any of the tools that show query plans (Profiler, Management Studio, Query Analyzer) and ask for the query plan of the RECEIVE in order to understand what this statement does.

So yes, RECEIVE statements can be issued in parallel and they will execute simultaneously.

There is an External Activator sample at you might want to take a look at, http://www.gotdotnet.com/codegallery/codegallery.aspx?id=9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319.

HTH,
~ Remus

|||Thank you very much for the insight, Remus. The external activator seems rather promising.

Lubomir|||

Thanks

So if I understand well I should build a different conversation for each client, and replicate same messages on different conversation, so that every client gets the message.

But what if the number of clients isn't fixed?

Any suggestion?

|||

Basically this is a Publish/Subscribe scenario. Look at this example at http://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx and see if you can start building something from it.

HTH,
~ Remus

Constraints and query plans

Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
DannyIt depends on the query, but in some cases the optimizer does take advantage
of constraints. Also, you may want to consider indexing some of your FK
columns.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
Does having enforced foreign key constraints between tables help query
performance? In our reporting database we have many views with multiple
joins so that report writing is easier. But with each additional join the
optimizer generally scans or seeks the index on all joined tables whether or
not the query requests columns from the table. Currently there are no
foreign key constraints, by adding and enforcing them would the queries
produce better plans?
Thanks,
Danny|||Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||And here it is! :-) Basically, I just changed the NOT EXISTS to EXISTS.
In the query plan, note that the SELECT after the FK has been added does not
refer to the Orders table at all:
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
drop table OrderDetails, Orders
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eXmNc6gTGHA.2656@.TK2MSFTNGP10.phx.gbl...
Sorry about that but the example I gave you doesn't produce the desired
result. (I was comparing the query cost of the FK build with the SELECT.)
The rest of the commentary still stands. I'll see if I can conjure up some
code.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:O9PnB1gTGHA.6048@.TK2MSFTNGP11.phx.gbl...
IIRC, doing a WHERE EXISTS/NOT EXISTS can be expedited with a FK in some
circumstances. Here's an example. Run the following script with Show
Execution Plan turned on (Ctrl+K):
use tempdb
go
select
*
into
Orders
from
Northwind.dbo.Orders
select
*
into
OrderDetails
from
Northwind.dbo.[Order Details]
alter table Orders
add
constraint PK_Orders primary key (OrderID)
alter table OrderDetails
add
constraint PK_OrderDetails primary key (OrderID, ProductID)
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
alter table OrderDetails
add
constraint FK1_OrderDetails foreign key (OrderID) references Orders
go
select
*
from
OrderDetails od
where not exists
(
select
*
from
Orders o
where
o.OrderID = od.OrderID
)
go
the last two SELECT's are identical, but the second one has a lower query
cost.
Also, CHECK constraints do make a difference in partitioned views, since
only the tables whose CHECK constraints satisfy the search criteria are
tapped.
In 2005, there are plan guides that may be of assistance to you:
http://msdn2.microsoft.com/en-us/library/ms190417(en-US,SQL.90).aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Danny" <djscroggins@.verizon.net> wrote in message
news:lWkUf.8672$I7.2391@.trnddc03...
Can you give me a basic example of where the optimizer would take advantage
of a foreign key constraint? I understand creating indexes on the colums.
In any cases does it decide not to seek or scan an index because of a
constraint is in place? Or is it that the optimizer has more information
for find the optimal plan where as with just indexes it may stop and choose
a plan that is good enough?
Our views get very complex due to the number of joins. When a query has
more than about six joins the number of potential plans is really large and
sometimes the resulting plan is not optimal. We are hoping that in 2005 the
optimizer does a better job with many joins.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23foIBzaTGHA.792@.TK2MSFTNGP10.phx.gbl...
> It depends on the query, but in some cases the optimizer does take
> advantage
> of constraints. Also, you may want to consider indexing some of your FK
> columns.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or
> not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||Actually, YES:
http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_122104.mspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
Danny
> Does having enforced foreign key constraints between tables help query
> performance?
Actually NO. However it is a good practice to create an index on FK column
and then it does improve perfomance.
FK is a logical concept. It prevents from an unexpectred deletion for
example.
Please read an article about FK in the BOL get a whole picture.
"Danny" <djscroggins@.verizon.net> wrote in message
news:iqbUf.3553$4N1.230@.trnddc06...
> Does having enforced foreign key constraints between tables help query
> performance? In our reporting database we have many views with multiple
> joins so that report writing is easier. But with each additional join the
> optimizer generally scans or seeks the index on all joined tables whether
> or not the query requests columns from the table. Currently there are no
> foreign key constraints, by adding and enforcing them would the queries
> produce better plans?
> Thanks,
> Danny
>|||In a large reporting environment, the differences between foreign key
constraints when using views is usually negligible.
In other words, the solution I think you are using is a bunch of large
canned views showing a gazillion columns, and then reports pick and
choose teh data and columns they really need from that view.
These views are VERY slow as the optimizer has a tough time figuring
out which of the gazillion indexes to utilize to get the right data.
The next step is to pass parameters to a stored procedure for
frequently used, particularly slow queries. By simply moving the code
from a view to a stored procedure, speed will come back.
In the longer run, if you can afford it, OLAP is the BEST reporting
solution for analysts. It is sooooo much faster, it is unreal, but
there is a learning curve for all involved.|||Thanks.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23iS339sTGHA.4900@.TK2MSFTNGP12.phx.gbl...
> Actually, YES:
> http://www.microsoft.com/technet/abouttn/subscriptions/flash/tips/tips_122104.mspx
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23fbPQzaTGHA.4140@.TK2MSFTNGP10.phx.gbl...
> Danny
>> Does having enforced foreign key constraints between tables help query
>> performance?
> Actually NO. However it is a good practice to create an index on FK column
> and then it does improve perfomance.
> FK is a logical concept. It prevents from an unexpectred deletion for
> example.
> Please read an article about FK in the BOL get a whole picture.
>
> "Danny" <djscroggins@.verizon.net> wrote in message
> news:iqbUf.3553$4N1.230@.trnddc06...
>> Does having enforced foreign key constraints between tables help query
>> performance? In our reporting database we have many views with multiple
>> joins so that report writing is easier. But with each additional join
>> the
>> optimizer generally scans or seeks the index on all joined tables whether
>> or not the query requests columns from the table. Currently there are no
>> foreign key constraints, by adding and enforcing them would the queries
>> produce better plans?
>> Thanks,
>> Danny
>

Consolidating multiple lookups

In many of my packages I have to translate an organizational code into a surrogate key. The method for translating is rather convoluted and involves a few lookup tables. (For example, lookup in the OrgKey table. If there is a match, use that key; if not, do a lookup of the first 5 characters in the BUKey table. If there is a match, use that key; if not, do a lookup of the first 2 characters... You get the idea.)

Since many of my packages use this same logic, I would like to consolidate it all into one custom transformation. I assume I can do this with a script transform, but then I'd lose all the caching built into the lookup transforms.

Should I just bite the bullet, and copy and paste the whole Rube Goldberg contraption of cascading lookup transforms into each package? Or is there a better solution I'm overlooking?The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

A second unit of re-use is a custom transform (not a script transform). THe script transform has "cut and paste" inheritence, which is no inheritance at all, so a change to one implementation forks it from the original, or both from both. That kind of re-use does not sounds as relevant to your scenario.

Nevertheless, I tend to the look at script transformations (for the most part) as proof of concept for custom transforms. If and once they are converted over to Custom transforms, then there is no more cut-and-paste inheritence (i.e. you can fix bugs once, not N times) Without taking advantage of the two re-usable objects (packages and custom components), forking will likely occur.|||

jaegd wrote:

The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?

That said, it sounds like a custom transform is probably the way to go.|||

Hubajube wrote:

jaegd wrote:

The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?

That said, it sounds like a custom transform is probably the way to go.

Correct. The only easy way to "send" to a subpackage is via staging tables (either database tables or flat files)

consolidating multiple data/log files

Hello:
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.
Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.

consolidating multiple data/log files

Hello:
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx
.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.

consolidating multiple data/log files

Hello:
I've recently migrated a 6.5 datbase to 2000. As it was
under 6.5, the database was configured with multiple data
and log devices, which has been retained in its migrated
2000 database.
I'd like to consolidate multiple .ndf & .ldf files to a
single .mdf & .ldf file, respectively for its data and
log. Any ideas how & if its possible?
Thanks.Rob,
First do a DBCC SHRINKFILE(..., EMPTYFILE) followed by
ALTER DATABASE <databasename>
DROP FILE ..
Refer BooksOnLiune for syntax'ndetails.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:1cfff01c4534e$ad942c20$a501280a@.phx.gbl...
> Hello:
> I've recently migrated a 6.5 datbase to 2000. As it was
> under 6.5, the database was configured with multiple data
> and log devices, which has been retained in its migrated
> 2000 database.
> I'd like to consolidate multiple .ndf & .ldf files to a
> single .mdf & .ldf file, respectively for its data and
> log. Any ideas how & if its possible?
> Thanks.