install SQL on WinXP: date

Showing posts with label date. Show all posts

Tuesday, March 20, 2012

Can not change filter on model report - UPDATED 7th June

I have a very simple report based on a test model.

The report is filtered via a date field and runs ok.

If I try to change the filter I get the following error message:

Operation Failed

Short time pattern does not include hours and minutes

Does anyone know how I can correct this?

Regards

John

UPDATE

This only happens if I try to filter between two dates, a single date works correctly!

Can you please save report that fails to file and post it?|||

Alexandre

This is the rdl for the failing report.

<?xml version="1.0" encoding="utf-8"?>

<Query>

<DataSourceName>dataSource1</DataSourceName>

</BaseEntity>

</EntityRef>

</Expression>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

<Path>

</RolePathItem>

</Path>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

</Details>

</Grouping>

</Groupings>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperty>

</CustomProperties>

</Expression>

</Filter>

</Hierarchy>

</Hierarchies>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperties>

</Expression>

</CustomProperties>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperties>

</Expression>

</CalculatedAttributes>

</SemanticQuery></CommandText>

</Query>

<DataField>Epex Referral</DataField>

</Field>

<DataField>Created</DataField>

</Field>

<DataField>Close Date</DataField>

</Field>

<DataField>Patient</DataField>

</Field>

</Field>

</Fields>

</DataSet>

</DataSets>

<DataSourceReference>/Models/Mars Cutdown</DataSourceReference>

<rd:DataSourceID>4d1edcd6-3c78-4e68-9566-ef952e495be4</rd:DataSourceID>

</DataSource>

</DataSources>

<Body>

<Style>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:Watermark</Name>

<Value>Click to add title</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Created.Value</Value>

<Styl>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<Value>=Fields!CloseDate.Value</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Patient.Value</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

<GroupExpression>=Fields!EpexReferral.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=IIf(True, Fields!expr1.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

<SortExpression>=IIf(True, Fields!Created.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

</Sorting>

</Details>

<DataSetName>dataSet</DataSetName>

<Hidden>=Fields!Created.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!CloseDate.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!Patient.IsMissing</Hidden>

</Visibility>

</TableColumn>

</TableColumns>

<SortExpression>=Fields!Created.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Created</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!CloseDate.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Close Date</Value>

<Style

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!Patient.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Patient</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</Table>

<Value>=String.Format("Total Epex Referrals" & Chr(58) & " {0}", COUNTROWS("dataSet"))</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

<Value>TotalRows</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<Value>Filter: Epex Referrals with: Created from 01/01/2005 to 01/01/2006</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

Value>FilterDescription</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

</ReportItems>

</Body>

</Report>

|||

Is this error happening when you are editing report or when you are running this report? Can you save this report to report server and run it from Report Manager (http://rshost/reports)?

Thanks!

|||

The report executes with no problems, I only get the error if I try to change the filter.

What is strange, I can get around the error by apply to separate date filters, one using Greater Than Or Equal and the other Less Than or Equal.

This gets around the problem but I would like to offer my end users the option of From and To as this is what the use in other parts of SSRS.

Regards

John

|||

We have the same problem even after installing SP1 and the hotfixes. Has anybody a solution/answer?

Regards

Werner

|||This is a known issue that will be fixed in SP2. Unfortunately the only way to avoid this error for now is to change your Windows regional settings to use a 12-hour clock.|||

Bob,

thanks for the immediate answer, when can we approx. expect SP2?

Regards

Werner

|||No committed dates yet, but I believe it's on track for later this year.|||

Bob,

i tried to change the format in the regional settings like you said but no changes. We use a german W2003 server... When i select a date field in the report builder filter, select a date from the datetime picker (22.06.2006) and try to save it with the ok button i get a message box saying 'no valid date'...?!?

Can i change anything in the model to solve the problem? Is this filter problem caused by SP1 (in this case we could think about setting up the SQL Server again)?

Regards

Werner

Can not change filter on model report - UPDATED

I have a very simple report based on a test model.

The report is filtered via a date field and runs ok.

If I try to change the filter I get the following error message:

Operation Failed

Short time pattern does not include hours and minutes

Does anyone know how I can correct this?

Regards

John

UPDATE

This only happens if I try to filter between two dates, a single date works correctly!

Can you please save report that fails to file and post it?|||

Alexandre

This is the rdl for the failing report.

<?xml version="1.0" encoding="utf-8"?>

<Query>

<DataSourceName>dataSource1</DataSourceName>

</BaseEntity>

</EntityRef>

</Expression>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

<Path>

</RolePathItem>

</Path>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

</Details>

</Grouping>

</Groupings>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperty>

</CustomProperties>

</Expression>

</Filter>

</Hierarchy>

</Hierarchies>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperties>

</Expression>

</CustomProperties>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperties>

</Expression>

</CalculatedAttributes>

</SemanticQuery></CommandText>

</Query>

<DataField>Epex Referral</DataField>

</Field>

<DataField>Created</DataField>

</Field>

<DataField>Close Date</DataField>

</Field>

<DataField>Patient</DataField>

</Field>

</Field>

</Fields>

</DataSet>

</DataSets>

<DataSourceReference>/Models/Mars Cutdown</DataSourceReference>

<rd:DataSourceID>4d1edcd6-3c78-4e68-9566-ef952e495be4</rd:DataSourceID>

</DataSource>

</DataSources>

<Body>

<Style>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:Watermark</Name>

<Value>Click to add title</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Created.Value</Value>

<Styl>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<Value>=Fields!CloseDate.Value</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

<Value>=Fields!Patient.Value</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

<GroupExpression>=Fields!EpexReferral.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=IIf(True, Fields!expr1.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

<SortExpression>=IIf(True, Fields!Created.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

</Sorting>

</Details>

<DataSetName>dataSet</DataSetName>

<Hidden>=Fields!Created.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!CloseDate.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!Patient.IsMissing</Hidden>

</Visibility>

</TableColumn>

</TableColumns>

<SortExpression>=Fields!Created.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Created</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!CloseDate.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Close Date</Value>

<Style

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!Patient.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Patient</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</Table>

<Value>=String.Format("Total Epex Referrals" & Chr(58) & " {0}", COUNTROWS("dataSet"))</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

<Value>TotalRows</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<Value>Filter: Epex Referrals with: Created from 01/01/2005 to 01/01/2006</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

Value>FilterDescription</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

</ReportItems>

</Body>

</Report>

|||

Is this error happening when you are editing report or when you are running this report? Can you save this report to report server and run it from Report Manager (http://rshost/reports)?

Thanks!

|||

The report executes with no problems, I only get the error if I try to change the filter.

What is strange, I can get around the error by apply to separate date filters, one using Greater Than Or Equal and the other Less Than or Equal.

This gets around the problem but I would like to offer my end users the option of From and To as this is what the use in other parts of SSRS.

Regards

John

|||

We have the same problem even after installing SP1 and the hotfixes. Has anybody a solution/answer?

Regards

Werner

|||This is a known issue that will be fixed in SP2. Unfortunately the only way to avoid this error for now is to change your Windows regional settings to use a 12-hour clock.|||

Bob,

thanks for the immediate answer, when can we approx. expect SP2?

Regards

Werner

|||No committed dates yet, but I believe it's on track for later this year.|||

Bob,

Can i change anything in the model to solve the problem? Is this filter problem caused by SP1 (in this case we could think about setting up the SQL Server again)?

Regards

Werner

sql

Monday, March 19, 2012

Can NEWLINE in FORMAT?

I have a date field and use the format: dd MM/dd/yy
The output is: Fri 10/15/04
I would like this instead:
Fri
10/15/04
Is there a way to add a line break using the simple format codes?
Thanks, JerryI have found that adding
... & chr(10) & chr(13) & ...
seems to do the trick.
"Jerry Nixon" wrote:
> I have a date field and use the format: dd MM/dd/yy
> The output is: Fri 10/15/04
> I would like this instead:
> Fri
> 10/15/04
> Is there a way to add a line break using the simple format codes?
> Thanks, Jerry
>|||I do not know of a way that you can add a line break when typing the format
code into the Format property. It can be done in several ways when using an
expression. For example:
=Format( Fields!YoureDate.Value, "ddd") + ControlChars.crlf + Format(
Fields!YourDate.Value, "MM/dd/yy")
"Jerry Nixon" wrote:
> I have a date field and use the format: dd MM/dd/yy
> The output is: Fri 10/15/04
> I would like this instead:
> Fri
> 10/15/04
> Is there a way to add a line break using the simple format codes?
> Thanks, Jerry
>

Saturday, February 25, 2012

Can I use date control for parameter in RS2000?

Can I use date control for parameter in RS2000?
--
Seeing your smile always brings me joyRS 2000 does not come with a date picker for parameters. To do this you
would have to have your own web pages and then use either URL integration or
web services to integrate with RS. RS 2005 does have a date picker control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Macro" <Jihong.Liu@.gmail.com> wrote in message
news:5FA06B84-339D-4ED6-93FA-EE6F3E4CDCB9@.microsoft.com...
> Can I use date control for parameter in RS2000?
> --
> Seeing your smile always brings me joy|||I use date type report parameters but convert them to strings when
mapping to stored procedure parameters. E.g.,
CStr(Parameters!BeginDate.value).
Bruce L-C [MVP] wrote:
> RS 2000 does not come with a date picker for parameters. To do this you
> would have to have your own web pages and then use either URL integration or
> web services to integrate with RS. RS 2005 does have a date picker control.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Macro" <Jihong.Liu@.gmail.com> wrote in message
> news:5FA06B84-339D-4ED6-93FA-EE6F3E4CDCB9@.microsoft.com...
> > Can I use date control for parameter in RS2000?
> >
> > --
> > Seeing your smile always brings me joy

Can I use a variable to hold the target table name.

We need to pull from a table that is named tablename_mmddyy and populate a table with the same format tablename_mmddyy. The date will be different every month so I want to be able to build the tablenames every month. Is there a way to do this in SSIS? Thank you.Yes, using expressions in your variables, you can do what you want.

Create a variable and then for its properties, set EvaluateAsExpression to True and then build your expression accordingly in the Expression box.

Then in your OLE DB source/destination, you can use that variable as the table. Just know that you'll want to probably set DelayValidation to True on your connection managers when you do this.

There are plenty of examples in this forum if you search for them on how to build expressions, if you need.|||I found the option for this under Data Access Mode but we need to do the same thing for the database name which will be in a similar format e.g. databasename_mmddyy and the source db name will change every month.|||Same thing. You can use expressions on your connection managers to dynamically create the current connection string.|||The same principals can be applied to a connection manager (i.e. database name of database server). You can use an expression to build the connection string from a variable in the properties window of the connection manager.|||Any examples of this?|||

agentf1 wrote:
Any examples of this?

In the ConnectionString expression box for a connection manager. Also it can't hurt to set DelayValidation to True.

"Data Source=" + @.[User::YourDatabaseNameVariable] +";Initial Catalog=Report;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"|||What goes in the database name field? I am assuming that it gets overridden by the expression.|||

agentf1 wrote:
What goes in the database name field? I am assuming that it gets overridden by the expression.

The ConnectionString will override any other parameters.|||

agentf1 wrote:
Any examples of this?

http://search.live.com/results.aspx?FORM=QBJK&q1=macro%3Ajamiet.ssis&q=expressions

-JT

|||This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable? It also appears that Data Source controls the server and Catalog is the database name.|||

agentf1 wrote:
This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable?

That's impossible (unless one of us has misunderstood). An expression on the ConnectionString property of a connection manager only changes that connection manager.

agentf1 wrote:

It also appears that Data Source controls the server and Catalog is the database name.

Correct.

-Jamie

|||

Jamie Thomson wrote:

agentf1 wrote:
This seems to be changing both of my OLE DB Connections. Is there a way one can be static and the other updated by the variable?

That's impossible (unless one of us has misunderstood). An expression on the ConnectionString property of a connection manager only changes that connection manager.

agentf1 wrote:

It also appears that Data Source controls the server and Catalog is the database name.

Correct.

-Jamie

My bad, I had the expression on both connections. I am OK, thanks for all the help, worked beautifully.

Friday, February 24, 2012

Can I tell when SQL Server was upgraded?

Unusal question but I am trying to find out the date an instance of SQL
Server was upgraded from 7.0 to 2000.
It was probably about 6 months ago and I am trying to pin it down to a
specific date.
I thought that maybe the creation date of system tables in msdb or Master
would give me a clue but they all seem to be 8/6/2000 which is definitely
not correct.
The logs have long since recycled.
Does anyone have any other ideas?Wild guess, but search for iss files (the install directory for your
instance). I'm thinking that the upgrade will produce an ISS file and the
date for this can be the answer. My machine shows 2002-08-06 for my sp1
instance and 2003-03-22 for my sp3 instance. Note that the machine was most
probably installed after sp1 was released but before sp3 ...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"DaveF" <davef@.comcast.net> wrote in message
news:efck5Xj%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Unusal question but I am trying to find out the date an instance of SQL
> Server was upgraded from 7.0 to 2000.
> It was probably about 6 months ago and I am trying to pin it down to a
> specific date.
> I thought that maybe the creation date of system tables in msdb or Master
> would give me a clue but they all seem to be 8/6/2000 which is definitely
> not correct.
> The logs have long since recycled.
> Does anyone have any other ideas?
>
>|||You can also try looking for sqlstp.log which I think is in c:\winnt by
default.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DaveF" <davef@.comcast.net> wrote in message
news:efck5Xj%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Unusal question but I am trying to find out the date an instance of SQL
> Server was upgraded from 7.0 to 2000.
> It was probably about 6 months ago and I am trying to pin it down to a
> specific date.
> I thought that maybe the creation date of system tables in msdb or Master
> would give me a clue but they all seem to be 8/6/2000 which is definitely
> not correct.
> The logs have long since recycled.
> Does anyone have any other ideas?
>
>

Can I tell when SQL Server was upgraded?

Unusal question but I am trying to find out the date an instance of SQL
Server was upgraded from 7.0 to 2000.
It was probably about 6 months ago and I am trying to pin it down to a
specific date.
I thought that maybe the creation date of system tables in msdb or Master
would give me a clue but they all seem to be 8/6/2000 which is definitely
not correct.
The logs have long since recycled.
Does anyone have any other ideas?Wild guess, but search for iss files (the install directory for your
instance). I'm thinking that the upgrade will produce an ISS file and the
date for this can be the answer. My machine shows 2002-08-06 for my sp1
instance and 2003-03-22 for my sp3 instance. Note that the machine was most
probably installed after sp1 was released but before sp3 ...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"DaveF" <davef@.comcast.net> wrote in message
news:efck5Xj%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Unusal question but I am trying to find out the date an instance of SQL
> Server was upgraded from 7.0 to 2000.
> It was probably about 6 months ago and I am trying to pin it down to a
> specific date.
> I thought that maybe the creation date of system tables in msdb or Master
> would give me a clue but they all seem to be 8/6/2000 which is definitely
> not correct.
> The logs have long since recycled.
> Does anyone have any other ideas?
>
>|||You can also try looking for sqlstp.log which I think is in c:\winnt by
default.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DaveF" <davef@.comcast.net> wrote in message
news:efck5Xj%23DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Unusal question but I am trying to find out the date an instance of SQL
> Server was upgraded from 7.0 to 2000.
> It was probably about 6 months ago and I am trying to pin it down to a
> specific date.
> I thought that maybe the creation date of system tables in msdb or Master
> would give me a clue but they all seem to be 8/6/2000 which is definitely
> not correct.
> The logs have long since recycled.
> Does anyone have any other ideas?
>
>

Sunday, February 12, 2012

Can I Merge a table with itself ?

Hi,
I have a table which contains several integer columns that represent
similiar date related information. ie
RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
I need a view of the table where these different columns have been merged
into a single column, so where there was one row with x number of integers,
I would have X number of rows with a single integer. Is this possible ?
What I am actually wanting to do is sort the rows based on the value that is
highest / lowest from the values in the row.
Can this be done ?
Thanks,
Jon.Bishman,
select
a.c1,
case b.c1
when 1 then a.c2
when 2 then a.c3
when 3 then a.c4
end as new_c2
from
dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
select 3) as b
go
-- or
select c1, c2 as new_c2
from dbo.t1
union all
select c1, c3
from dbo.t1
union all
select c1, c4
from dbo.t1
go
AMB
"Bishman" wrote:
> Hi,
> I have a table which contains several integer columns that represent
> similiar date related information. ie
> RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> I need a view of the table where these different columns have been merged
> into a single column, so where there was one row with x number of integers,
> I would have X number of rows with a single integer. Is this possible ?
> What I am actually wanting to do is sort the rows based on the value that is
> highest / lowest from the values in the row.
> Can this be done ?
> Thanks,
> Jon.
>
>|||Bishman,
If you are working with 2005, then you can also use new operator "unpivot".
create table dbo.t1 (
a int not null identity unique,
b int,
c int,
d int
)
go
insert into dbo.t1(b, c, d) values(10, 20, 30)
insert into dbo.t1(b, c, d) values(40, 50, 60)
go
select
a, val
from
(
select a, b as c1, c as c2, d as c3
from dbo.t1
) as pvt
unpivot
(val for c in (c1, c2, c3)) as unpvt
go
drop table dbo.t1
go
AMB
"Alejandro Mesa" wrote:
> Bishman,
> select
> a.c1,
> case b.c1
> when 1 then a.c2
> when 2 then a.c3
> when 3 then a.c4
> end as new_c2
> from
> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
> select 3) as b
> go
> -- or
> select c1, c2 as new_c2
> from dbo.t1
> union all
> select c1, c3
> from dbo.t1
> union all
> select c1, c4
> from dbo.t1
> go
>
> AMB
> "Bishman" wrote:
> > Hi,
> >
> > I have a table which contains several integer columns that represent
> > similiar date related information. ie
> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
> >
> > I need a view of the table where these different columns have been merged
> > into a single column, so where there was one row with x number of integers,
> > I would have X number of rows with a single integer. Is this possible ?
> >
> > What I am actually wanting to do is sort the rows based on the value that is
> > highest / lowest from the values in the row.
> >
> > Can this be done ?
> >
> > Thanks,
> >
> > Jon.
> >
> >
> >|||Alejandro,
Thanks for these responses.
I like the unpivot one particuarly. I am just seeing if I can get it to work
now !!
I will let you know.
Thanks again,
Jon
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
> Bishman,
> If you are working with 2005, then you can also use new operator
> "unpivot".
> create table dbo.t1 (
> a int not null identity unique,
> b int,
> c int,
> d int
> )
> go
> insert into dbo.t1(b, c, d) values(10, 20, 30)
> insert into dbo.t1(b, c, d) values(40, 50, 60)
> go
> select
> a, val
> from
> (
> select a, b as c1, c as c2, d as c3
> from dbo.t1
> ) as pvt
> unpivot
> (val for c in (c1, c2, c3)) as unpvt
> go
> drop table dbo.t1
> go
>
> AMB
>
> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >|||This is working well except I am trying to go past 4 columns ( I need 6)
I get this error when I go to 5 columns....
The type of column "cole" conflicts with the type of other columns specified
in the UNPIVOT list.
with this SQL
USE CastleProspect_Dev
select
ExtractNumber,rowID, RenewalDate
from
(select ExtractNumber,rowID,HomeRenewalMonthDay as a,ContentsRenewalMonthDay
as b,MotorRenewalMonthDay as c, BreakdownRenewalMonthDay as d ,
PetRenewalMonthDay as e from dbo.ExtractDetail) as pvt
unpivot
(RenewalDate for aa in (a, b, c, d, e)) as unpvt
where extractnumber = 99
go
Any Ideas '
Thanks again. This looks like just what I need.
Jon
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
> Alejandro,
> Thanks for these responses.
> I like the unpivot one particuarly. I am just seeing if I can get it to
> work now !!
> I will let you know.
> Thanks again,
> Jon
>
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
> message news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
>> Bishman,
>> If you are working with 2005, then you can also use new operator
>> "unpivot".
>> create table dbo.t1 (
>> a int not null identity unique,
>> b int,
>> c int,
>> d int
>> )
>> go
>> insert into dbo.t1(b, c, d) values(10, 20, 30)
>> insert into dbo.t1(b, c, d) values(40, 50, 60)
>> go
>> select
>> a, val
>> from
>> (
>> select a, b as c1, c as c2, d as c3
>> from dbo.t1
>> ) as pvt
>> unpivot
>> (val for c in (c1, c2, c3)) as unpvt
>> go
>> drop table dbo.t1
>> go
>>
>> AMB
>>
>> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible
>> > ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >
>|||Go It !
The two remaining columns have different data type to the other 4, Sorted.
This works really well. Thanks again.
Jon.
"Bishman" <jonathan.bishop@.btinternet.com> wrote in message
news:%23vWauL0iHHA.1388@.TK2MSFTNGP05.phx.gbl...
> This is working well except I am trying to go past 4 columns ( I need 6)
> I get this error when I go to 5 columns....
> The type of column "cole" conflicts with the type of other columns
> specified in the UNPIVOT list.
> with this SQL
> USE CastleProspect_Dev
> select
> ExtractNumber,rowID, RenewalDate
> from
> (select ExtractNumber,rowID,HomeRenewalMonthDay as
> a,ContentsRenewalMonthDay as b,MotorRenewalMonthDay as c,
> BreakdownRenewalMonthDay as d , PetRenewalMonthDay as e from
> dbo.ExtractDetail) as pvt
> unpivot
> (RenewalDate for aa in (a, b, c, d, e)) as unpvt
> where extractnumber = 99
> go
>
> Any Ideas '
> Thanks again. This looks like just what I need.
> Jon
> "Bishman" <jonathan.bishop@.btinternet.com> wrote in message
> news:OCrA8rziHHA.4496@.TK2MSFTNGP05.phx.gbl...
>> Alejandro,
>> Thanks for these responses.
>> I like the unpivot one particuarly. I am just seeing if I can get it to
>> work now !!
>> I will let you know.
>> Thanks again,
>> Jon
>>
>>
>> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
>> message news:FD238AE8-02AF-41C1-AD50-8D7DD45B42CD@.microsoft.com...
>> Bishman,
>> If you are working with 2005, then you can also use new operator
>> "unpivot".
>> create table dbo.t1 (
>> a int not null identity unique,
>> b int,
>> c int,
>> d int
>> )
>> go
>> insert into dbo.t1(b, c, d) values(10, 20, 30)
>> insert into dbo.t1(b, c, d) values(40, 50, 60)
>> go
>> select
>> a, val
>> from
>> (
>> select a, b as c1, c as c2, d as c3
>> from dbo.t1
>> ) as pvt
>> unpivot
>> (val for c in (c1, c2, c3)) as unpvt
>> go
>> drop table dbo.t1
>> go
>>
>> AMB
>>
>> "Alejandro Mesa" wrote:
>> Bishman,
>> select
>> a.c1,
>> case b.c1
>> when 1 then a.c2
>> when 2 then a.c3
>> when 3 then a.c4
>> end as new_c2
>> from
>> dbo.t1 as a cross join (select 1 as c1 union all select 2 union all
>> select 3) as b
>> go
>> -- or
>> select c1, c2 as new_c2
>> from dbo.t1
>> union all
>> select c1, c3
>> from dbo.t1
>> union all
>> select c1, c4
>> from dbo.t1
>> go
>>
>> AMB
>> "Bishman" wrote:
>> > Hi,
>> >
>> > I have a table which contains several integer columns that represent
>> > similiar date related information. ie
>> > RenewalMonthDay,HomeMonthDay,MotorMonthDay etc.
>> >
>> > I need a view of the table where these different columns have been
>> > merged
>> > into a single column, so where there was one row with x number of
>> > integers,
>> > I would have X number of rows with a single integer. Is this possible
>> > ?
>> >
>> > What I am actually wanting to do is sort the rows based on the value
>> > that is
>> > highest / lowest from the values in the row.
>> >
>> > Can this be done ?
>> >
>> > Thanks,
>> >
>> > Jon.
>> >
>> >
>> >
>>
>

Tuesday, March 20, 2012

Can not change filter on model report - UPDATED 7th June

Can not change filter on model report - UPDATED

Monday, March 19, 2012

Can NEWLINE in FORMAT?

Saturday, February 25, 2012

Can I use date control for parameter in RS2000?

Can I use a variable to hold the target table name.

Friday, February 24, 2012

Can I tell when SQL Server was upgraded?

Can I tell when SQL Server was upgraded?

Sunday, February 12, 2012

Can I Merge a table with itself ?

Can I Merge a table with itself ?

install SQL on WinXP

Blog Archive

About Me