how to shrink Trx Log

Shrink Log file does not seems to make the file size any smaller.
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?
"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David

how to shrink Trx Log

Shrink Log file does not seems to make the file size any smaller.
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David|||... and some details at http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in messa
ge
news:Ouy%239MHIHHA.924@.TK2MSFTNGP02.phx.gbl...
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message news:esbf$KHIHHA.3676@.T
K2MSFTNGP03.phx.gbl...
> You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions
> out to a backup device, then you should be able to shrink the transaction
log. Then set up a
> maintence plan to perform periodic transaction log backups to control the
growth of the log file
> in the future.
> David

how to shrink Trx Log

Shrink Log file does not seems to make the file size any smaller.
The file size remains the even after shrinking it a few time.
The GUI seems ez to use but I may be doing it wrongly.
How do I shink the Log file?"TBoon" <allblacks15@.hotmail.com> wrote in message
news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
> Shrink Log file does not seems to make the file size any smaller.
> The file size remains the even after shrinking it a few time.
> The GUI seems ez to use but I may be doing it wrongly.
> How do I shink the Log file?
>
You can't shrink it if it's full of transactions. Perform a log backup to
copy the transactions out to a backup device, then you should be able to
shrink the transaction log. Then set up a maintence plan to perform
periodic transaction log backups to control the growth of the log file in
the future.
David|||... and some details at http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in message
news:Ouy%239MHIHHA.924@.TK2MSFTNGP02.phx.gbl...
>
> "TBoon" <allblacks15@.hotmail.com> wrote in message news:esbf$KHIHHA.3676@.TK2MSFTNGP03.phx.gbl...
>> Shrink Log file does not seems to make the file size any smaller.
>> The file size remains the even after shrinking it a few time.
>> The GUI seems ez to use but I may be doing it wrongly.
>> How do I shink the Log file?
> You can't shrink it if it's full of transactions. Perform a log backup to copy the transactions
> out to a backup device, then you should be able to shrink the transaction log. Then set up a
> maintence plan to perform periodic transaction log backups to control the growth of the log file
> in the future.
> David

How to show multiple main reports with their subreports one set by

Hi,
I have made a SQL Reporting Services report with the subreport. I need
my report to show multiple records with their subreport at the same time.
At now, I can generate multiple main reports altogether and then they are
followed by the subreport with multiple subreport details. It's just like
the following:
1st Page: 1st record in the Main report
2nd Page: 2nd record in the Main report
3rd Page: 1st Subreport detail (use Table object)
2nd Subreport detail (use Table object)
Inside the table of the 3rd page, it shows the 1st
Subreport details and then 2nd Subreport details.
What I actually need is I want my report to show one record in the main
report and then followed by the corresponding subreport details, then next
page for the next record in the main report and then followed by the
corresponding subreport details.
1st Page: 1st record in the Main report
2nd Page: 1st Subreport detail
3rd Page: 2nd record in the Main report
4th Page: 2nd Subreport detail
Is there anyone who can help me to solve this problem ?
Thanks in advance.
RogerHave you tried putting your subreport inside a table cell? The table could
contain your main rows and your subreports inside the same table as
repeating elements.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:17AC53EB-D966-476E-8B86-30B0864DE28B@.microsoft.com...
> Hi,
> I have made a SQL Reporting Services report with the subreport. I
> need
> my report to show multiple records with their subreport at the same time.
> At now, I can generate multiple main reports altogether and then they are
> followed by the subreport with multiple subreport details. It's just
> like
> the following:
> 1st Page: 1st record in the Main report
> 2nd Page: 2nd record in the Main report
> 3rd Page: 1st Subreport detail (use Table object)
> 2nd Subreport detail (use Table object)
> Inside the table of the 3rd page, it shows the 1st
> Subreport details and then 2nd Subreport details.
> What I actually need is I want my report to show one record in the
> main
> report and then followed by the corresponding subreport details, then next
> page for the next record in the main report and then followed by the
> corresponding subreport details.
> 1st Page: 1st record in the Main report
> 2nd Page: 1st Subreport detail
> 3rd Page: 2nd record in the Main report
> 4th Page: 2nd Subreport detail
> Is there anyone who can help me to solve this problem ?
> Thanks in advance.
> Roger
>

How to share dimensions for multiple cubes in AS 2005?

Hello,

I have a couple of cubes that I created using a wizard in AS 2005. Every time I create a new cube, a AS wizard adds dimensions with 1,2 etc at the end of their names. Is it possible to use/share existing dimensions which are used by other cubes instead of having bunch of dimensions which are the same and the only difference is their name?

Thanks!

When you are going through cube building wizard, once you get to the Review Shared Dimensions page, select all the dimensions that you want to reuse. The wizard will reuse these dimensions and it won't create the dimensions with the 1,2 etc.

Hope this helps.

Van Dieu

|||Altering what you have already generated may be complicated. I don't use the wizard to generate cubes, so I'm not sure how much work is involved to change it. It might be as simple as opening the cube editor, deleting the dimensions that are duplicates, and adding the ones that you want to use, but I doubt it. You'll probably have to alter the data source view and the dimension usage in the cube editor as well.|||

Actually, it is that easy. You can just open up the cube editor and delete all the duplicated dimensions. Then on the dimension usage tab add them back in (the dimensions that you want to share).

Van Dieu

|||Good to know

how to setup this query?

hi,
i have a table which has an ID, Time (unix time in seconds), and a score. th
e data looks something like this:
ID Time Score
1 1079748053937 1.0
2 1079748053945 0.67
3 1079748053945 0.32
4 1079748053945 0.01
5 1079748053950 0.90
6 1079748053950 0.1
my problem is that i want to write a query that will get the highest score a
t a given time. can anyone offer any help'
thanks!If I understand what you want, the highest score for each
time can be obtained with something like:
select time, max(score)
from your table
group by time
-Sue
On Mon, 22 Mar 2004 11:51:14 -0800, "mike"
<anonymous@.discussions.microsoft.com> wrote:

>hi,
> i have a table which has an ID, Time (unix time in seconds), and a score.
the data looks something like this:
>ID Time Score
>1 1079748053937 1.0
>2 1079748053945 0.67
>3 1079748053945 0.32
>4 1079748053945 0.01
>5 1079748053950 0.90
>6 1079748053950 0.1
>my problem is that i want to write a query that will get the highest score
at a given time. can anyone offer any help'
>thanks!

How to Setup Schedule On SSRS on Time Basis?

Hi All,

I am new to SSRS. I subscribe my report through report subscription. I want to schedule my report from 9 AM to 17:30 PM. I setup start time but I am not able to schedule to stop at 17:30 on daily basis.

Please help me ....

Thank you in advance....

Regards,

Balwant Patel.

I don't think this level of flexibility is supported. I'd use the FireEvent API to kick off the schedule programatically.|||

Hi Teo,

I have no idea about FireEvent...I will look on FireEvent but the time being..i schedule the same report twice.

sql

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

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

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

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

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

How to Set Thousand Separator?

Hi,
This topic has been raised a few time in the newsgroup and the solution
has been to set the 'Langauge' property of the report.
In my report, I can't find the Language propertie, neither in the
properties from he menu in VS nor in the Property Grid.
Can anyone point me in the right direction?
Thanks,
DomDom, in the properties pane (press F4 if you can't see it) there is a
drop-down list at the top where you can select objects. Use this and
choose 'Report' and in the 'Misc' properties section you'll see
Language.
If you want to default new reports to your Windows Language, you can do
Tools|Options|Environment|General|International Settings and change it
to 'Same as Office'. This affects anything you do in VS/VB though.
Chris
DominicB wrote:
> Hi,
> This topic has been raised a few time in the newsgroup and the
> solution has been to set the 'Langauge' property of the report.
> In my report, I can't find the Language propertie, neither in the
> properties from he menu in VS nor in the Property Grid.
> Can anyone point me in the right direction?
> Thanks,
> Dom|||Ace, Chris!
Cheers,
Dom
Chris McGuigan wrote:
> Dom, in the properties pane (press F4 if you can't see it) there is a
> drop-down list at the top where you can select objects. Use this and
> choose 'Report' and in the 'Misc' properties section you'll see
> Language.
> If you want to default new reports to your Windows Language, you can do
> Tools|Options|Environment|General|International Settings and change it
> to 'Same as Office'. This affects anything you do in VS/VB though.
> Chris
> DominicB wrote:
> > Hi,
> > This topic has been raised a few time in the newsgroup and the
> > solution has been to set the 'Langauge' property of the report.
> >
> > In my report, I can't find the Language propertie, neither in the
> > properties from he menu in VS nor in the Property Grid.
> >
> > Can anyone point me in the right direction?
> >
> > Thanks,
> > Dom

How to set default date

Hi Guys,

I have a time standard dimension used as a filter to my report.

How can I set the parameter to get the current month and year to be used by the time standard filter.

Select the report, and go to Report->Parameters, select the parameter, in the default values sectoin, set the value there. You can use function too, like get current date, use =Today, and DatePart() to get year, month, date, whatever.

how to set a dimension attribute as a simple "note" field?

I have a simple cube,
dimensions are time and customers (id, name, address, contry, etc), fact are
quantity
On Y axes I have to show the customer name AND the address.
if I drag and drop only the id and name, the query is fast, and it works
fine, If after the name I also add the address, the query becames very slow
I belive this is because of aggregation on the address attribute, that I
really don't need, the address is specific to every customer and it does not
make any sense to do calculation on this.
I only need to show che address near the name, without any aggregation
How can I do it?
Is there any property I have to set?
Thank you
Alessandro Belli
Hello Alessandro,
I understand that you'd like to specify an dimension attribute as a
property not to be aggregated. If I'm off-base, please let me know.
You may want to consider define attribute relationship bewteen attribute
name and address, thus you could display address as member property of name
and do not necessary to drag name attribute to the browser which cause more
calculations and overhead even if they are 1:1 relationship. When you drag
a dimension attribute into the browser it is used as normal attribute
anyway though at running time, it will gain performance improvement when
engine may find it has 1:1 relationship with another attribute.
By default, dimension attribute are related to key dimension attribute by a
attribute relationship. In VS 2005, you could open the dimension in VS
project, and check the attribute relationships defined on the key attribute
( I assume it is "id" in the dimension).
You may want to create a attribute relationship directly on name attribute
and define the relationship bweteen name and address. You will be able to
see address as the member property of the name attribute in browser. You
could right click the a name cell->Show properties in report->Show All
properties in report.
The other option that you could display name/address at the same time ( I
assume it is 1:1 relationship) is that you create a new column on dimension
source table, and merge (name, address) column to a new column such as
name-address. You could use this new column as a attribute directly in the
dimension attribute.
If you have further questions or concerns on the issue, please feel free to
let's know. Than you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications
<http://msdn.microsoft.com/subscripti...s/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscripti...t/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Peter Yang [MSFT] wrote:

> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of name
> and do not necessary to drag name attribute to the browser which cause more
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key attribute
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on dimension
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free to
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> <http://msdn.microsoft.com/subscripti...s/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscripti...t/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
You can add address as property for name attribute and can create
calculated measure on this property.
Regards
Amish Shah
http://shahamishm.tripod.com
|||Peter Yang [MSFT] wrote:

> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of name
> and do not necessary to drag name attribute to the browser which cause more
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key attribute
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on dimension
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free to
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> <http://msdn.microsoft.com/subscripti...s/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscripti...t/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
You can add address as property for name attribute and can create
calculated measure on this property.
Regards
Amish Shah
http://shahamishm.tripod.com
|||Thank you for you suggestion, but is not clear to me how to make a
relationship between name and address.
Is enought to set address.keycolumn= customers.name and
addess.namecolumn=address?
Or what I have to do to make the realtionship?
The key of the dimension is a field named "code". Do I also have to set
name.keycolumn=customers.code?
After this when I browse the cube and I right click the name cell->Show
properties in report->Show All
> properties in report the option is grayed.
Thank's
Alessandro
"Peter Yang [MSFT]" <petery@.online.microsoft.com> ha scritto nel messaggio
news:gdsQ4cR4GHA.2336@.TK2MSFTNGXA01.phx.gbl...
> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of
> name
> and do not necessary to drag name attribute to the browser which cause
> more
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by
> a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key
> attribute
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on
> dimension
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free
> to
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...ult.aspx#notif
> ications
> <http://msdn.microsoft.com/subscripti...s/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscripti...t/default.aspx>.
> ==================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hello Alessandro,
You could try the following steps to add attribute relationship between
name and address attribute.
1. Open customer dimension by double click the dimension on the right panel
2. Drag Address column from the source table in Data Source View to the
Name Attribute on the left panel, and you shall see a new Attribute
relationship under attribute Name.
3. Process the dimension and cube and you shall see member property Address
under Name Attribute
Please see if this could meet your requirement. If you have any concerns or
questions, please let me know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

how to set a dimension attribute as a simple "note" field?

I have a simple cube,
dimensions are time and customers (id, name, address, contry, etc), fact are
quantity
On Y axes I have to show the customer name AND the address.
if I drag and drop only the id and name, the query is fast, and it works
fine, If after the name I also add the address, the query becames very slow
I belive this is because of aggregation on the address attribute, that I
really don't need, the address is specific to every customer and it does not
make any sense to do calculation on this.
I only need to show che address near the name, without any aggregation
How can I do it?
Is there any property I have to set?
Thank you
Alessandro BelliHello Alessandro,
I understand that you'd like to specify an dimension attribute as a
property not to be aggregated. If I'm off-base, please let me know.
You may want to consider define attribute relationship bewteen attribute
name and address, thus you could display address as member property of name
and do not necessary to drag name attribute to the browser which cause more
calculations and overhead even if they are 1:1 relationship. When you drag
a dimension attribute into the browser it is used as normal attribute
anyway though at running time, it will gain performance improvement when
engine may find it has 1:1 relationship with another attribute.
By default, dimension attribute are related to key dimension attribute by a
attribute relationship. In VS 2005, you could open the dimension in VS
project, and check the attribute relationships defined on the key attribute
( I assume it is "id" in the dimension).
You may want to create a attribute relationship directly on name attribute
and define the relationship bweteen name and address. You will be able to
see address as the member property of the name attribute in browser. You
could right click the a name cell->Show properties in report->Show All
properties in report.
The other option that you could display name/address at the same time ( I
assume it is 1:1 relationship) is that you create a new column on dimension
source table, and merge (name, address) column to a new column such as
name-address. You could use this new column as a attribute directly in the
dimension attribute.
If you have further questions or concerns on the issue, please feel free to
let's know. Than you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter Yang [MSFT] wrote:
[vbcol=seagreen]
> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of nam
e
> and do not necessary to drag name attribute to the browser which cause mor
e
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by
a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key attribut
e
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on dimensio
n
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free t
o
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]
You can add address as property for name attribute and can create
calculated measure on this property.
Regards
Amish Shah
http://shahamishm.tripod.com|||Peter Yang [MSFT] wrote:
[vbcol=seagreen]
> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of nam
e
> and do not necessary to drag name attribute to the browser which cause mor
e
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by
a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key attribut
e
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on dimensio
n
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free t
o
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]
You can add address as property for name attribute and can create
calculated measure on this property.
Regards
Amish Shah
http://shahamishm.tripod.com|||Thank you for you suggestion, but is not clear to me how to make a
relationship between name and address.
Is enought to set address.keycolumn= customers.name and
addess.namecolumn=address?
Or what I have to do to make the realtionship?
The key of the dimension is a field named "code". Do I also have to set
name.keycolumn=customers.code?
After this when I browse the cube and I right click the name cell->Show
properties in report->Show All
> properties in report the option is grayed.
Thank's
Alessandro
"Peter Yang [MSFT]" <petery@.online.microsoft.com> ha scritto nel messagg
io
news:gdsQ4cR4GHA.2336@.TK2MSFTNGXA01.phx.gbl...
> Hello Alessandro,
> I understand that you'd like to specify an dimension attribute as a
> property not to be aggregated. If I'm off-base, please let me know.
> You may want to consider define attribute relationship bewteen attribute
> name and address, thus you could display address as member property of
> name
> and do not necessary to drag name attribute to the browser which cause
> more
> calculations and overhead even if they are 1:1 relationship. When you drag
> a dimension attribute into the browser it is used as normal attribute
> anyway though at running time, it will gain performance improvement when
> engine may find it has 1:1 relationship with another attribute.
> By default, dimension attribute are related to key dimension attribute by
> a
> attribute relationship. In VS 2005, you could open the dimension in VS
> project, and check the attribute relationships defined on the key
> attribute
> ( I assume it is "id" in the dimension).
> You may want to create a attribute relationship directly on name attribute
> and define the relationship bweteen name and address. You will be able to
> see address as the member property of the name attribute in browser. You
> could right click the a name cell->Show properties in report->Show All
> properties in report.
> The other option that you could display name/address at the same time ( I
> assume it is 1:1 relationship) is that you create a new column on
> dimension
> source table, and merge (name, address) column to a new column such as
> name-address. You could use this new column as a attribute directly in the
> dimension attribute.
> If you have further questions or concerns on the issue, please feel free
> to
> let's know. Than you.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications
> <[url]http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx" target="_blank">http://msdn.microsoft.com/subscript...ps/default.aspx>.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> <http://msdn.microsoft.com/subscript...rt/default.aspx>.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Alessandro,
You could try the following steps to add attribute relationship between
name and address attribute.
1. Open customer dimension by double click the dimension on the right panel
2. Drag Address column from the source table in Data Source View to the
Name Attribute on the left panel, and you shall see a new Attribute
relationship under attribute Name.
3. Process the dimension and cube and you shall see member property Address
under Name Attribute
Please see if this could meet your requirement. If you have any concerns or
questions, please let me know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

How to separate the DateTime field into Two Fields in View

Hi ,

I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?

Best Regards,

make use of this

DATEPART

Returns an integer representing the specified datepart of the specified date.

Syntax

DATEPART ( datepart , date )

Arguments

datepart

Is the parameter that specifies the part of the date to return. The table lists dateparts and abbreviations recognized by Microsoft? SQL Server?.

Datepart Abbreviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw hour hh minute mi, n second ss, s millisecond ms

The week (wk, ww) datepart reflects changes made to SET DATEFIRST. January 1 of any year defines the starting number for the week datepart, for example: DATEPART(wk, 'Jan 1, xxxx') = 1, where xxxx is any year.

The weekday (dw) datepart returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7. The number produced by the weekday datepart depends on the value set by SET DATEFIRST, which sets the first day of the week.

date

Is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Store dates as character data for earlier dates. When entering datetime values, always enclose them in quotation marks. Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.

If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that precedes the cutoff year. For example, if two digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.

For more information about specifying time values, see Time Formats. For more information about specifying dates, see datetime and smalldatetime.

Return Types

int

Remarks

The DAY, MONTH, and YEAR functions are synonyms for DATEPART(dd, date), DATEPART(mm, date), and DATEPART(yy, date), respectively.

Examples

The GETDATE function returns the current date; however, the complete date is not always the information needed for comparison (often only a portion of the date is compared). This example shows the output of GETDATE as well as DATEPART.

SELECT GETDATE() AS 'Current Date' GO

Here is the result set:

Current Date Feb 18 1998 11:46PM SELECT DATEPART(month, GETDATE()) AS 'Month Number' GO

Here is the result set:

Month Number 2

This example assumes the date May 29.

SELECT DATEPART(month, GETDATE()) GO

Here is the result set:

-- 5 (1 row(s) affected)

In this example, the date is specified as a number. Notice that SQL Server interprets 0 as January 1, 1900.

SELECT DATEPART(m, 0), DATEPART(d, 0), DATEPART(yy, 0)

Here is the result set:

-- 1 1 1900 |||

With datepart you will have to write several statements to getthe correct parts and afterwards join them back concatenating fields.

depending exactly on what you need the correct path is:

a) use datediff as in (if you need dates)

select getdate() /*fulldatetime*/, cast(datediff(day,0,getdate()) as datetime) /*justdate*/, getdate()-cast(datediff(day,0,getdate()) as datetime) /*justtime*/

b) use convert as in (if your ok with strings)

select getdate() /*fulldatetime*/, convert( varchar(20),getdate(),102) /*justdate*/, convert( varchar(20),getdate(),108) /*justtime*/

a) is much faster then (b) and much much faster then datepart solution.

|||

Thanks very much,

how to separate date & time during export

I would like to export sql server 2005 tables to a text file using SSIS. It is easy to do this as a direct export. However, how could I separate the datetime field into two fields: one for date only and one for time only?

Sample:

select getdate() ==> 2007-08-30 14:42:11.870

V

select convert(char, getdate(), 111) as date, convert(char, getdate(), 108) as [time]

==> Date = 2007/08/30

==> Time = 14:44:50

Others datetime format

century century
(yy) (yyyy) standard output format
- - --
- 0 or 100 (*) default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd, yy
8 108 - hh:mms
- 9 or 109 (*) default mon dd yyyy
milliseconds hh:mis:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default dd mon yyyy
milliseconds hh:mms:mmm(24h)
14 114 - hh:mis:mmm(24h)

(*) the default values (style 0 or 100, 9 or 109, and
13 or 113) always return the century (yyyy)

|||

You have to use Derived Column Transformation of Data Flow.

So:

1. Add a Data Flow task to Control Flow window

2.Add OleDB Source then Derived Column and Finaly Flat File Destination linked with arrow

3. Double Click Derived Column and

write Derived Column Name desired then <add as new column> to Derived Column then in expresion : (DT_DBTIME)YourDataColumnName and Data Type database time[DT_DBTIME]

in second line write Derived Column Name desired then Replace 'YourDataColumnName' and in expression (DT_DBDATE)YourDataColumnName and Data Type databasedate[DT_DBDATE]

|||

I would use this expressions in a derived column:

For Date:

RIGHT("00" + (DT_WSTR,2)MONTH(MyDate),2) + "-" + RIGHT("00" + (DT_WSTR,2)DAY(MyDate),2) + "-" + (DT_WSTR,4)YEAR(MyDate)

For time:

(DT_DBTIME)MyDate

How to send report through email one time i.e. without subscription for recurrent emailing?

I want to send a deployed report (report manager), through email, by calling reportingServices webservice. But the requirement is that, time is not fixed for sending this email, so I want to trigger the sending of report as a one time event, no subscription to a daily time...

How should I go about it?

Hello,

As I see it you have two choices, you could either create a one time subscription on the fly (http://technet.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx) or a create a Delivery Extension (http://technet.microsoft.com/en-us/library/ms154050.aspx) that has email as the delivery method. The first option is the easiest and most straight forward. Create a new subscription with a schedule to run the report one time at a time 5 minutes from then.

Good luck,

Larry