DB and tables usage information

Hi everybody!
Using SQL Server Enterprise Manager I can see general
information about DB, tables and indexes (Right click
over DB name and See option)
How can I print this information? or export it? Is there
any store procedure to get this information?
Thanks for your help.
LJ.Hi
Check out sp_help, sp_helpdb, sp_helptext in books online.
John
"LJ" <leyla.garcia@.unisabana.edu.co> wrote in message
news:07c901c34249$f638cfb0$a001280a@.phx.gbl...
> Hi everybody!
> Using SQL Server Enterprise Manager I can see general
> information about DB, tables and indexes (Right click
> over DB name and See option)
> How can I print this information? or export it? Is there
> any store procedure to get this information?
> Thanks for your help.
> LJ.sql

datetime to date

Hi, everybody.
I have a parameter from datetime type.
Its' values are stored with date and time values together in the database.
But I only want to show its' date value to the user.
How can I do this?

You can modify the way a Datetime value is shown by adding the ToString() expression.

Take a look at the DateTime.ToString() reference: http://msdn2.microsoft.com/en-us/library/system.datetime.tostring.aspx

|||Thanks Jan.
But my parameter's value is not shown in the report.
So I can't write an expression or a method to this.( as Datetime.ToString() )
I think I should do this by SQL side.
But when I use a convert or cast function on sql side , there exists a type conflict.
Anyway, if you find a solution, share it with me please.|||

If you are using the datetime values as a parameter, the easiest way to do this is to create or modify your parameter query: add a column with only the date value of the other datetime values.

SELECT Date AS DBDate, CONVERT(VARCHAR(10), Date, 101) AS ViewDate
FROM <<Table>>

This query shows also the date in the format mm/dd/yyyy. Then use this query for the parameter: use the DBDate as Value and the ViewDate as label.

Datetime entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:

An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

Each member in your Time dimension is identified using the following format [DimensionName].[AttributeHierarchyName].&[MemberKey]. This is the format that the generated parameter query uses. You can use this format to apply a fiter and limit the members shown. The Report Builder could help you to understand how to set the filter. Alternatively, you can set the Value property of the Date dimension key to the underlying field of DateTime type. However, each SSRS parameter can have only two values (label and value). To pass the selected value to the main query you need to resolve it to a valid member (again [DimensionName].[AttributeHierarchyName].&[MemberKey]). So, it may be more convenient to stick to this format as the parameter value.

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works |||can you tell me exactly how you resolved this? thanks,

Datetime entry for querying analysis service cube

Hi everybody,

I have two problems while using a analysis service cube as data source for a reporting service report.

1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message:

An error occured during local report processing.
The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type.

How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function.

2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message:

An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set 'DIM...'.
Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated.

As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem.

How can I create an input for a restriction on a dimension based on a parameter with a self constructed string?

Thanks,

StSt

However I know that the underlying field and as well the hierachy in the cube is of the format datetime

Each member in your Time dimension is identified using the following format [DimensionName].[AttributeHierarchyName].&[MemberKey]. This is the format that the generated parameter query uses. You can use this format to apply a fiter and limit the members shown. The Report Builder could help you to understand how to set the filter. Alternatively, you can set the Value property of the Date dimension key to the underlying field of DateTime type. However, each SSRS parameter can have only two values (label and value). To pass the selected value to the main query you need to resolve it to a valid member (again [DimensionName].[AttributeHierarchyName].&[MemberKey]). So, it may be more convenient to stick to this format as the parameter value.

|||Thanks this was of help ...even so I don't like the idea of constructing the member representation of the analysis service but it works |||can you tell me exactly how you resolved this? thanks,

Dateformat problem in Query Analyser

Hi Everybody,
i have a small problem ?
i have a two different servers
one is used for test purposes and the other one is used for live proposes

i have a table call Employee in both the servers and i got a filed call Attnd_Dttm

so when i open a query analyzer from the test database and type
select * from Employee where convert(datetime,Attnd_Dttm) like '13/01/2005'
i am getting the correct results

but when i am type the same SQL from the live databse
select * from Employee where convert(datetime,Attnd_Dttm) like '13/01/2005'
it give me the
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

My problem is how come this happen ,cos i checked form the live database server computer it's using a British standard date time format like dd/mm/yyyyy ?

Any Idea to solve this problem,cos i need to run the same SQL in both the servers without any problems ?

regards
suis

Try using this date format: 2005-01-13. (year, month, day, dash-separated). This format seems to work in all localizations.

-Ryan / Kardax

|||Hi Ryan
u r reply is not clear to me,
where can i give this command !

is there any command to find out the sql server date time format
regards
suis
|||

I think that you have differenet default DATEFORMAT option on your servers.

You could use SET SET DATEFORMAT 'mdy'

Or use explicit convert:

select * from Employee where convert(datetime,Attnd_Dttm,103) like '13/01/2005'

|||HI Konstantin Kosinsky
Thanks very much for your comments
u r solution is worked out,
but is there any command to check SQL server DateTime fornat ?

regards
suis

|||

suis,

I wonder if you could alter that column a make it datetime data type. If you can not change the data type of that column, I will suggest to store the value using ISO ('yyyymmdd') or ISO8601 ('yyyy-mm-ddThh:mis.mmm'). This way, SQL Server can interpret the string as a datetime no matter the language or formatdate settings.

Code Snippet

set dateformat dmy

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

set dateformat mdy

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

set language Spanish

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

set language English

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

AMB