Showing posts with label build. Show all posts
Showing posts with label build. Show all posts

Tuesday, March 27, 2012

Derived Query

I'm trying to build a "derived" query from 2 seperate table a Build count
table and a defect table
I've done this before but never with the calculated columns as in
Year(TestDate) as 'Yr'. It fails and returns
Server: Msg 207, Level 16, State 1, Line 17
Invalid column name 'YR'.
Any help ?
Select Year(TestDate) as 'Yr',
DateName(mm,TestDate)as 'Mnth',
Category, SubCategory,
Sum(NumFailures) as 'Fails'
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as 'Yr',
DateName(mm,TestDate)as 'Mnth',
Sum(DropCount) as 'Dropped'
From FTC..FTC_DropCount_Results
Where DropCount > 0
And TestDate >= '09-01-2005'
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON T1.YR = CT.YR
Group By '"Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
news:OgAwBv2HGHA.1728@.TK2MSFTNGP09.phx.gbl...
> I'm trying to build a "derived" query from 2 seperate table a Build count
> table and a defect table
> I've done this before but never with the calculated columns as in
> Year(TestDate) as 'Yr'. It fails and returns
> Server: Msg 207, Level 16, State 1, Line 17
> Invalid column name 'YR'.
> Any help ?
>
> Select Year(TestDate) as 'Yr',
> DateName(mm,TestDate)as 'Mnth',
> Category, SubCategory,
> Sum(NumFailures) as 'Fails'
> From ftc_testresults T1
> INNER JOIN
> (
> Select Year(TestDate) as 'Yr',
> DateName(mm,TestDate)as 'Mnth',
> Sum(DropCount) as 'Dropped'
> From FTC..FTC_DropCount_Results
> Where DropCount > 0
> And TestDate >= '09-01-2005'
> Group By Year(TestDate),
> DateName(mm,TestDate)
> ) CT
> ON T1.YR = CT.YR
>
A column alias cannot be used in the same query in which it is assigned. So
in the outer query T1.YR is not legal. You must repeat the source
expression Year(T1.TestDate). And get rid of the single quotes around
column aliases, and add table aliases to all your column references.
Something like this:
Select Year(T1.TestDate) as Yr,
DateName(mm,T1.TestDate)as Mnth,
T1.Category,
T1.SubCategory,
Sum(T1.NumFailures) as Fails
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as Yr,
DateName(mm,TestDate)as Mnth,
Sum(DropCount) as Dropped
From FTC..FTC_DropCount_Results
Where DropCount > 0
And TestDate >= '09-01-2005'
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON Year(T1.TestDate) = CT.YR
David|||Dave,
Thanks for the quick and accurate reply.....
Curious... what's the taboo about single quotes around column aliases?
Final working product....
Select Year(T1.TestDate) as Yr,
DateName(mm,T1.TestDate) as Mnth,
T1.Category, T1.SubCategory,
IsNull(CT.Dropped , 0) as Dropped,
Sum(T1.NumFailures) as Failures
From ftc_testresults T1
INNER JOIN
(
Select Year(TestDate) as Yr,
DateName(mm,TestDate)as Mnth,
Sum(DropCount) as Dropped
From FTC..FTC_DropCount_Results
Where DropCount > 0
Group By Year(TestDate),
DateName(mm,TestDate)
) CT
ON Year(T1.TestDate) = CT.YR
AND DateName(mm,TestDate) = CT.Mnth
Where T1.TestDate > = '09-01-2005'
Group By T1.TestDate,T1.Category,
T1.SubCategory, ct.dropped;
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:uCN8P72HGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
> news:OgAwBv2HGHA.1728@.TK2MSFTNGP09.phx.gbl...
> A column alias cannot be used in the same query in which it is assigned.
> So in the outer query T1.YR is not legal. You must repeat the source
> expression Year(T1.TestDate). And get rid of the single quotes around
> column aliases, and add table aliases to all your column references.
> Something like this:
>
> Select Year(T1.TestDate) as Yr,
> DateName(mm,T1.TestDate)as Mnth,
> T1.Category,
> T1.SubCategory,
> Sum(T1.NumFailures) as Fails
> From ftc_testresults T1
> INNER JOIN
> (
> Select Year(TestDate) as Yr,
> DateName(mm,TestDate)as Mnth,
> Sum(DropCount) as Dropped
> From FTC..FTC_DropCount_Results
> Where DropCount > 0
> And TestDate >= '09-01-2005'
> Group By Year(TestDate),
> DateName(mm,TestDate)
> ) CT
> ON Year(T1.TestDate) = CT.YR
> David
>|||"Paul Ilacqua" <pilacqu2@.twcny.rr.com> wrote in message
news:u9eSrD3HGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Dave,
> Thanks for the quick and accurate reply.....
> Curious... what's the taboo about single quotes around column aliases?
>
Not a taboo, just a style preference. A column alias is an identifier, a
name, and it should follow the same convention as other identifiers (tables,
columns, table aliases).
David|||>> Curious... what's the taboo about single quotes around column aliases? <<
In Standard SQL, strings are enclosed in single quotes and user created
names are in double quotes. Violating standards is never a good idea;
makes code harder to pport, harder to read and occassionally can scew
up things.
You might also want to look up some old postings of mine as to how a
SELECT statement works.

Sunday, March 25, 2012

derived column - column removal

Once a column is added to the derived column transformation - how do we delete that. its not possible right now. i thnk we just forgot to build that.

It took some time for me to find out how to do this the first time, too...

A right click on the column will help...|||You can either right-click on the row and choose "Delete" from the context menu or press Delete key when a row is selected.

Thanks,

Thursday, March 22, 2012

Deployment to one Database 'Clearing' Another...

Hi,

We have a setup whereby our production server builds to a build cube overnight, and if and when this completes successfully, and passes sanity checks, it automatically backs up and restores the cube over the top of a 'live cube'.

This seemed like a fair enough plan - it allows developers to deploy to the production server without breaking the production-live cube, however, we have noticed that deploying to the build cube database sometimes results in the clearing out of the production cube database (much to the users' dismay!).

Anyone else seen anything similar, or have any ideas as to what the problem could be?

Thanks,

Will.

First time I hear about something like that.

Try and see if you can use Synchronization functionality to deploy your changes from test to production. This is more straightforward then backup-copy-restore.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Thanks for your reply.

We currently build the cube on the production server (and 'deploy' the live cube to a databae on the same server) - we're of the view that synchronization is not possible with two databases on the same server. Is this correct?

Regards,

Will.

|||

You are right. You cannot synch from one database to another on the same server instance.

What I suggest in your case is to install another instance of Analysis Server on the same server machine. This case you achive better separation between test and production. If something wrong goes with the test instance, you can easily restart it or, it would be easier to monitor usage of the production server instance without developers interfere. Then, also you'd be able to use synch. Overall in your case second instance might be cleaner solution.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Sunday, March 11, 2012

deploying reports

Hi all -
If you have a VS project with several reports in it - is there a way to
'cancel' if you accidentally build or deploy the whole project? I know esc.
doesn't work to well - but I currently have two projects open (one for the
.net UI) and the other one solution with multiple projects and reports. I am
not sure if this was the best way to set up all of the reports, as if you
mistakenly build or run the wrong project the whole nine-yards gets
over-written on report server. This wouldn't be such a big deal, however
there seems to be a disconnect between VS and RS in the way certain parameter
options are overwritten when a report is deployed - i.e. certain report
properties (for the way we have this set up) must be set through the RS
interface - it would be wonderful if there was a way to cancel the
re-deployment of every single (50 +) reports...
Thanks,
PeteI never redeploy all of them. I also select the reports I want to deploy,
right mouse click, deploy.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Myles" <Myles@.discussions.microsoft.com> wrote in message
news:47980980-3D06-41D7-93FB-D420040A236B@.microsoft.com...
> Hi all -
> If you have a VS project with several reports in it - is there a way to
> 'cancel' if you accidentally build or deploy the whole project? I know
> esc.
> doesn't work to well - but I currently have two projects open (one for the
> .net UI) and the other one solution with multiple projects and reports. I
> am
> not sure if this was the best way to set up all of the reports, as if you
> mistakenly build or run the wrong project the whole nine-yards gets
> over-written on report server. This wouldn't be such a big deal, however
> there seems to be a disconnect between VS and RS in the way certain
> parameter
> options are overwritten when a report is deployed - i.e. certain report
> properties (for the way we have this set up) must be set through the RS
> interface - it would be wonderful if there was a way to cancel the
> re-deployment of every single (50 +) reports...
> Thanks,
> Pete|||Thanks Bruce -
"Bruce L-C [MVP]" wrote:
> I never redeploy all of them. I also select the reports I want to deploy,
> right mouse click, deploy.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Myles" <Myles@.discussions.microsoft.com> wrote in message
> news:47980980-3D06-41D7-93FB-D420040A236B@.microsoft.com...
> > Hi all -
> >
> > If you have a VS project with several reports in it - is there a way to
> > 'cancel' if you accidentally build or deploy the whole project? I know
> > esc.
> > doesn't work to well - but I currently have two projects open (one for the
> > .net UI) and the other one solution with multiple projects and reports. I
> > am
> > not sure if this was the best way to set up all of the reports, as if you
> > mistakenly build or run the wrong project the whole nine-yards gets
> > over-written on report server. This wouldn't be such a big deal, however
> > there seems to be a disconnect between VS and RS in the way certain
> > parameter
> > options are overwritten when a report is deployed - i.e. certain report
> > properties (for the way we have this set up) must be set through the RS
> > interface - it would be wonderful if there was a way to cancel the
> > re-deployment of every single (50 +) reports...
> >
> > Thanks,
> >
> > Pete
>
>

Friday, February 24, 2012

Deploy SQL Server 2005 Business Intelligence Metadata Samples Toolkit

Today, I download SQL Server 2005 Business Intelligence Metadata Samples Toolkit from Microsoft download center, per readme.txt instructions, build the project, but there was an error as below:

Error 1 The project could not be deployed to the 'localhost' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

After then, I just build the project,but didn't deploy it, then I try to launch
DependencyAnalyzer console application, there was also an there:

Error occurred: A connection cannot be made. Ensure that the server is running.

Are you success on this Toolkit?

Thank you in advance.

Hello,
Do you have solved this error ? I have exactly the name. I don't know how to solved it....

The project could not be deployed to the 'SERVER NAME' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

Thanks for your help.
Regards

Deploy SQL Server 2005 Business Intelligence Metadata Samples Toolkit

Today, I download SQL Server 2005 Business Intelligence Metadata Samples Toolkit from Microsoft download center, per readme.txt instructions, build the project, but there was an error as below:

Error 1 The project could not be deployed to the 'localhost' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

After then, I just build the project,but didn't deploy it, then I try to launch
DependencyAnalyzer console application, there was also an there:

Error occurred: A connection cannot be made. Ensure that the server is running.

Are you success on this Toolkit?

Thank you in advance.

Hello,
Do you have solved this error ? I have exactly the name. I don't know how to solved it....

The project could not be deployed to the 'SERVER NAME' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server.

Thanks for your help.
Regards

Sunday, February 19, 2012

Deploy CLR SQL Project using MSBuild

What is the "official" line on deploying CLR SQL projects through the use of build scripts like NANT and MSBuild?

I'm obviously keen to hook up my project to my continuous integration build but the only things I have found that touch on the subject are:

    Do it by hand yourself using xcopy of assemblies and T-SQL

    Use SQLCLR project

    This old post that says what *might* happen

Cheers

I don't really understand your question; are you asking what MS says about using NANT/MSBUILD for deployment - or are you asking what the community thinks?

Anyway, if your aim is to automate your deployment, and continuous build my preferences would be:
1. scripting
or
2. SQLCLR project (I would say that as I am the developer)

I would not use the VS built in SQL Server project type, as I feel I do not have the control as I would like.

Niels