This 'should' work:
SELECT convert( varchar(10), MyColumn, 101 )
For example, using today's date:
SELECT convert( varchar(10), getdate(), 101 )
-
05/25/2007
It the style number which indicates how the system should transform your data.
Here is more detail.
http://msdn2.microsoft.com/en-us/library/ms187928.aspx
|||thanks a lot MVPsqlI need to determine the number of values in a set that are non-unique in the set.
In SQL, I would do the following:
select sum(value_count) as Ties
from (select value, count(*) as value_count
from source
group by value
having count(*) >1
)
In MDX, I have a set defined in a query that is passed into several custom statistical functions. I would like to be able to accomplish this without writing another external function, as it seems the sort of thing that should be easy...
Here is the query I'm trying to plug this into. Suggestions welcome.
with
set [data] as ( nonempty({
[Sample Date].[Time].[Day] }) *{ [Measures].[Parm Value] } )
MEMBER [Sen's Slope] as 'Statistics.Sen_Slope({[data]})'
MEMBER [Kendall's Tau] as 'Statistics.KENDALLS_TAU({[data]})'
MEMBER [Significant] as 'Statistics.Significance({[data]})'
MEMBER [MK_Z] as 'Statistics.Mann_Kendall_Z({[data]})'
member [measures].[Samples] as [data].count
member [measures].[Average] as avg([data])
member [measures].[Median] as Median([data])
member [measures].[Last Sample] as tail([data],1).item(0)
MEMBER [Ties] as '0'
select {[Samples],[Average],[Median],[Last Sample], [Kendall's Tau], [Sen's Slope], [MK_Z], [Significant], [Ties] } on 0
FROM ( SELECT ( [Sample Date].[Time].[2002 Q4]:[Sample Date].[Time].[2006 Q3] ) on 0,
{([Site Hierarchy].[Site Hierarchy].[Site ID].[0829SD6001] ,
[Analysis Parameter].[Analysis Parameter].[Acidity (ACD)])} ON 1
FROM [Sample Data])
;
Hi Clayton,
This sample from Adventure Works, where value is [Reseller Order Quantity], seems to work:
SQL query:
>>
select sum(order_count) as Ties
from
(select OrderQuantity, count(*) as order_count
from
(select ProductKey, sum(OrderQuantity) as OrderQuantity
from dbo.FactResellerSales
group by ProductKey) ps
group by OrderQuantity
having count(*) > 1) oc
87
>>
MDX query:
>>
With
Member [Measures].[PrdTies] as
Count(Filter(Order(NonEmpty(
[Product].[Product].[Product].Members,
{[Measures].[Reseller Order Quantity]}),
[Measures].[Reseller Order Quantity], BDESC) as OrdPrds,
(OrdPrds.CurrentOrdinal < OrdPrds.Count
And [Measures].[Reseller Order Quantity] =
([Measures].[Reseller Order Quantity],
OrdPrds.Item(OrdPrds.CurrentOrdinal)))
OR (OrdPrds.CurrentOrdinal > 1
And [Measures].[Reseller Order Quantity] =
([Measures].[Reseller Order Quantity],
OrdPrds.Item(OrdPrds.CurrentOrdinal-2)))))
select {[Measures].[PrdTies]} on 0
from [Adventure Works]
PrdTies
87
>>
|||
Deepak,
Thanks for the thourough response. I've done some testing, comparing results from the MDX you wrote to the ADOMD CLR proc I wrote, and I was getting different results. I've traced through the CLR version and manually counted the values in the set, and the CLR proc was correct. I've clipped out the relevant code in case you want to compare results. It may be the differences in how I'm calling the CLR functions by passing in a set versus the Measure method, which is working over the complete set of data from the subcube query.
Here is your test MDX query, with a measure added for the CLR function. In this case, both methods return the same count, as expected. I'm just curious as to what might be causing me to get dfferent counts between the two methods when used in my cube...
With
set [data] as ( NonEmpty([Product].[Product].[Product].Members)
*([Measures].[Reseller Order Quantity]) )
member [CountOfTies] as 'Statistics.CountOfTies({[data]})'
Member [Measures].[PrdTies] as
Count(Filter(Order(NonEmpty(
[Product].[Product].[Product].Members,
{[Measures].[Reseller Order Quantity]}),
[Measures].[Reseller Order Quantity], BDESC) as OrdPrds,
(OrdPrds.CurrentOrdinal < OrdPrds.Count
And [Measures].[Reseller Order Quantity] =
([Measures].[Reseller Order Quantity],
OrdPrds.Item(OrdPrds.CurrentOrdinal)))
OR (OrdPrds.CurrentOrdinal > 1
And [Measures].[Reseller Order Quantity] =
([Measures].[Reseller Order Quantity],
OrdPrds.Item(OrdPrds.CurrentOrdinal-2)))))
select {[Measures].[PrdTies], [CountOfTies]} on 0
from [Adventure Works];
Assembly code:
using System;
using System.Collections;
using Microsoft.AnalysisServices;
using Microsoft.AnalysisServices.AdomdServer;
namespace Statistics
{
public sealed class Statistics
{
private Statistics()
{
}
[CLSCompliant(false)]
// function to return a sum of the counts for tied values
public static Object CountOfTies(Set data1)
{
try
{
//number of Tuples in the set
Int32 v = data1.Tuples.Count;
if (v > 1)
{
//dimension a new array to hold values for passing into GetCountOfTies()
Double[] ValueArray;
ValueArray = new Double[v];
MDXValue mdxval;
Int32 i = 0;
// convert tuple set into an array
foreach (Tuple t in data1.Tuples)
{
mdxval = t;
ValueArray[i++] = mdxval.ToDouble();
}
// get array of any ties
Int32[] ResultArray = GetCountOfTies(ValueArray);
//sum counts to get results to return
i = 0;
foreach (Int32 j in ResultArray)
{i = i + j;}
return i;
}
else {return 0;}
}
catch (Exception e)
{
Console.WriteLine(e);
return "ERROR";
}
}
private static Int32[] GetCountOfTies(double[] doubleArray)
{
//create an array of counts of unique values, where there are ties in values.
//the resulting array will contain one element for each value with ties.
Array.Sort(doubleArray);
ArrayList numbers = new ArrayList();
double? lastVal = null;
int currentCount = 0;
foreach (double val in doubleArray)
{
if (lastVal != null && lastVal != val)
{
if (currentCount > 1)
numbers.Add(currentCount);
currentCount = 1;
}
else
{currentCount++;}
lastVal = val;
}
return (Int32[])numbers.ToArray(typeof(Int32));
}
}
}
Need help
I made a mistake an attached a read only database to the server (now it is grey marked).
When I try to select this read only database in the MS SQL Server Management Studio, the server hangs up.
I removed the read only attribute from the .mdf and .ldf File and made a reboot of the server. Still the same problem.
How can I detach this read only database or how can I set the attribute to read/write (always hangs up the server, when I try to access this db)
I tried also:
alter database readOnly_dbname set read_write
or
USE master;
GO
EXEC sp_dboption 'readOnly_dbname', 'read only', 'FALSE';
Many thanks for an answer.
Kusi
Did you use sp_detachdb 'DbName' ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||I have the same problem. In management studio, the only thing that you can do is to make delete on the read-only database. You will have an error, but the database will be well released from SQL Server. I didn't find a way to make it back non read-only... by scripting or in the management studio.
Someone has a solution for that problem?
|||I found a solution. The issue is related to the files security.
Be sure that the .mdf and .ldf files have the NETWORK SERVICE and the SQLServer2005MSSQLUser$YourPc$YourSqlInstanceName security users with Full Control permission checked on them.
Hi,
If I have ad hoc SQL statements created by users, which could be parameterized, how could I derive the parmeters at runtime. I cannot use CommandBuilder.DeriveParameters() as that is for StoredProcedures only.
Just use Split on the SQL string? Or is there a better way, such as a third-party .Net Component?
Thanks
John
We need more input on your problem. What does it mean that the users are creating the SQL Strings on their own, how does one look like ?Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||
Hi,
The User's created SQL could be anything (they are writing a report!), but here is a trivial example
SELECT CustomerID, CustomerName FROM Customers WHERE CustomerID = @.CustomerID
Clearly, I have to Pop up a Window to the User for them to supply the actual run time value for @.CustomerID. Just like MS Access or the VS2005's Dataset Designer's Query Builder. Once I have the values I can populate Parameters Collection.
I was hoping someone would have some advise over Parsing SQL strings.
Thanks
John
|||Best thing would be to regex the string and search for the matches within the string.
Jens K.
|||Hi,
I've been looking at the General SQL Parser component, and it looks like I can simply get to the Field, Parameter pairs using this. Product is easily found, just do a Web search.
But, out of interests, Jens. Do you have contacts within the Microsoft Dev teams to find out how they do it in VS2005's Dataset Designer's Query Builder and Management Studio's Query Designer?
Rgds
John
