Max Query: solution

Showing posts with label solution. Show all posts

Wednesday, March 28, 2012

Help with SQL 2005 solutions

Hi,
I want to be able to use source control from within SQL 2005. I know
how to add a new solution with a project attached. And I know how to
add existing .sql scripts to it. My quesiton is, how can I split them
up so that I can add all stored procedure scripts into a folder called
Stored Procedures, all views into a folder called Views etc instead of
them all going into the Query folder.
Appreciate any help on this.nomad
If I understood you correctly
1) Open Solution within you have added .sql files (1-for SP,2-for Views)
2) When you click on the file it opens a object explorer windows
3) Add your object to opened window (relevant for object type) and save it.
So , the next time you open the file your script should be saved there
"nomad" <d.bedgood@.ntlworld.com> wrote in message
news:1172065721.265467.175850@.a75g2000cwd.googlegroups.com...
> Hi,
> I want to be able to use source control from within SQL 2005. I know
> how to add a new solution with a project attached. And I know how to
> add existing .sql scripts to it. My quesiton is, how can I split them
> up so that I can add all stored procedure scripts into a folder called
> Stored Procedures, all views into a folder called Views etc instead of
> them all going into the Query folder.
> Appreciate any help on this.
>|||On 21 Feb, 14:16, "Uri Dimant" <u...@.iscar.co.il> wrote:
> nomad
> If I understood you correctly
> 1) Open Solution within you have added .sql files (1-for SP,2-for Views)
> 2) When you click on the file it opens a object explorer windows
> 3) Add your object to opened window (relevant for object type) and save it.
> So , the next time you open the file your script should be saved there
> "nomad" <d.bedg...@.ntlworld.com> wrote in message
> news:1172065721.265467.175850@.a75g2000cwd.googlegroups.com...
> > Hi,
> > I want to be able to use source control from within SQL 2005. I know
> > how to add a new solution with a project attached. And I know how to
> > add existing .sql scripts to it. My quesiton is, how can I split them
> > up so that I can add all stored procedure scripts into a folder called
> > Stored Procedures, all views into a folder called Views etc instead of
> > them all going into the Query folder.
> > Appreciate any help on this.
I know how to do all of this. My question is, in the solution you
have folders called Connections, Queries & Miscellaneous. I want to
be able to add folders called Stored Procedures, Views etc so that I
can add my SP .sql scripts into Stored Procedure folder, Views scripts
into Views folder etc so that it is easier to differentiate between
your scripts.|||Ahh, I see what you mean. I'm affraid you cannot do that
"nomad" <d.bedgood@.ntlworld.com> wrote in message
news:1172068385.736309.197940@.k78g2000cwa.googlegroups.com...
> On 21 Feb, 14:16, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> nomad
>> If I understood you correctly
>> 1) Open Solution within you have added .sql files (1-for SP,2-for Views)
>> 2) When you click on the file it opens a object explorer windows
>> 3) Add your object to opened window (relevant for object type) and save
>> it.
>> So , the next time you open the file your script should be saved there
>> "nomad" <d.bedg...@.ntlworld.com> wrote in message
>> news:1172065721.265467.175850@.a75g2000cwd.googlegroups.com...
>> > Hi,
>> > I want to be able to use source control from within SQL 2005. I know
>> > how to add a new solution with a project attached. And I know how to
>> > add existing .sql scripts to it. My quesiton is, how can I split them
>> > up so that I can add all stored procedure scripts into a folder called
>> > Stored Procedures, all views into a folder called Views etc instead of
>> > them all going into the Query folder.
>> > Appreciate any help on this.
> I know how to do all of this. My question is, in the solution you
> have folders called Connections, Queries & Miscellaneous. I want to
> be able to add folders called Stored Procedures, Views etc so that I
> can add my SP .sql scripts into Stored Procedure folder, Views scripts
> into Views folder etc so that it is easier to differentiate between
> your scripts.
>|||On 21 Feb, 14:43, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Ahh, I see what you mean. I'm affraid you cannot do that
> "nomad" <d.bedg...@.ntlworld.com> wrote in message
> news:1172068385.736309.197940@.k78g2000cwa.googlegroups.com...
> > On 21 Feb, 14:16, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> nomad
> >> If I understood you correctly
> >> 1) Open Solution within you have added .sql files (1-for SP,2-for Views)
> >> 2) When you click on the file it opens a object explorer windows
> >> 3) Add your object to opened window (relevant for object type) and save
> >> it.
> >> So , the next time you open the file your script should be saved there
> >> "nomad" <d.bedg...@.ntlworld.com> wrote in message
> >>news:1172065721.265467.175850@.a75g2000cwd.googlegroups.com...
> >> > Hi,
> >> > I want to be able to use source control from within SQL 2005. I know
> >> > how to add a new solution with a project attached. And I know how to
> >> > add existing .sql scripts to it. My quesiton is, how can I split them
> >> > up so that I can add all stored procedure scripts into a folder called
> >> > Stored Procedures, all views into a folder called Views etc instead of
> >> > them all going into the Query folder.
> >> > Appreciate any help on this.
> > I know how to do all of this. My question is, in the solution you
> > have folders called Connections, Queries & Miscellaneous. I want to
> > be able to add folders called Stored Procedures, Views etc so that I
> > can add my SP .sql scripts into Stored Procedure folder, Views scripts
> > into Views folder etc so that it is easier to differentiate between
> > your scripts.
Thanks for your reply. I have got around it by having seperate
project for Stored Procedures and seperate project for Views.
Another question, if you don't mind. Obviously I will have these
scripted stored procedures and views within my solution, but that
still won't stop anyone from opening up the actual stored procedure or
view and altering it, without going through source control. Is there
a way of locking the actual stored procedures and views down so they
can only be altered via the scripts in source control?
Appreciate your help.|||nomad
There you have an option to add your scripts to VSS to keep versions
Regarding who can access to stored procedures , you can deny to users view
or alter your stored procedures, but this is a matter of your company's
policy
"nomad" <d.bedgood@.ntlworld.com> wrote in message
news:1172069673.298471.272100@.j27g2000cwj.googlegroups.com...
> On 21 Feb, 14:43, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Ahh, I see what you mean. I'm affraid you cannot do that
>> "nomad" <d.bedg...@.ntlworld.com> wrote in message
>> news:1172068385.736309.197940@.k78g2000cwa.googlegroups.com...
>> > On 21 Feb, 14:16, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> >> nomad
>> >> If I understood you correctly
>> >> 1) Open Solution within you have added .sql files (1-for SP,2-for
>> >> Views)
>> >> 2) When you click on the file it opens a object explorer windows
>> >> 3) Add your object to opened window (relevant for object type) and
>> >> save
>> >> it.
>> >> So , the next time you open the file your script should be saved there
>> >> "nomad" <d.bedg...@.ntlworld.com> wrote in message
>> >>news:1172065721.265467.175850@.a75g2000cwd.googlegroups.com...
>> >> > Hi,
>> >> > I want to be able to use source control from within SQL 2005. I
>> >> > know
>> >> > how to add a new solution with a project attached. And I know how
>> >> > to
>> >> > add existing .sql scripts to it. My quesiton is, how can I split
>> >> > them
>> >> > up so that I can add all stored procedure scripts into a folder
>> >> > called
>> >> > Stored Procedures, all views into a folder called Views etc instead
>> >> > of
>> >> > them all going into the Query folder.
>> >> > Appreciate any help on this.
>> > I know how to do all of this. My question is, in the solution you
>> > have folders called Connections, Queries & Miscellaneous. I want to
>> > be able to add folders called Stored Procedures, Views etc so that I
>> > can add my SP .sql scripts into Stored Procedure folder, Views scripts
>> > into Views folder etc so that it is easier to differentiate between
>> > your scripts.
> Thanks for your reply. I have got around it by having seperate
> project for Stored Procedures and seperate project for Views.
> Another question, if you don't mind. Obviously I will have these
> scripted stored procedures and views within my solution, but that
> still won't stop anyone from opening up the actual stored procedure or
> view and altering it, without going through source control. Is there
> a way of locking the actual stored procedures and views down so they
> can only be altered via the scripts in source control?
> Appreciate your help.
>|||Take a look at visual studio team edition for database professionals.
I think it's the answer to your problem.
More information at http://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx
regards
"nomad" wrote:
> On 21 Feb, 14:43, "Uri Dimant" <u...@.iscar.co.il> wrote:
> > Ahh, I see what you mean. I'm affraid you cannot do that
> >
> > "nomad" <d.bedg...@.ntlworld.com> wrote in message
> >
> > news:1172068385.736309.197940@.k78g2000cwa.googlegroups.com...
> >
> > > On 21 Feb, 14:16, "Uri Dimant" <u...@.iscar.co.il> wrote:
> > >> nomad
> > >> If I understood you correctly
> > >> 1) Open Solution within you have added .sql files (1-for SP,2-for Views)
> > >> 2) When you click on the file it opens a object explorer windows
> > >> 3) Add your object to opened window (relevant for object type) and save
> > >> it.
> > >> So , the next time you open the file your script should be saved there
> >
> > >> "nomad" <d.bedg...@.ntlworld.com> wrote in message
> >
> > >>news:1172065721.265467.175850@.a75g2000cwd.googlegroups.com...
> >
> > >> > Hi,
> >
> > >> > I want to be able to use source control from within SQL 2005. I know
> > >> > how to add a new solution with a project attached. And I know how to
> > >> > add existing .sql scripts to it. My quesiton is, how can I split them
> > >> > up so that I can add all stored procedure scripts into a folder called
> > >> > Stored Procedures, all views into a folder called Views etc instead of
> > >> > them all going into the Query folder.
> >
> > >> > Appreciate any help on this.
> >
> > > I know how to do all of this. My question is, in the solution you
> > > have folders called Connections, Queries & Miscellaneous. I want to
> > > be able to add folders called Stored Procedures, Views etc so that I
> > > can add my SP .sql scripts into Stored Procedure folder, Views scripts
> > > into Views folder etc so that it is easier to differentiate between
> > > your scripts.
> Thanks for your reply. I have got around it by having seperate
> project for Stored Procedures and seperate project for Views.
> Another question, if you don't mind. Obviously I will have these
> scripted stored procedures and views within my solution, but that
> still won't stop anyone from opening up the actual stored procedure or
> view and altering it, without going through source control. Is there
> a way of locking the actual stored procedures and views down so they
> can only be altered via the scripts in source control?
> Appreciate your help.
>|||On Feb 21, 3:31 pm, Zekske <Zek...@.discussions.microsoft.com> wrote:
> Take a look at visual studio team edition for database professionals.
> I think it's the answer to your problem.
> More information athttp://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx
> regards
> "nomad" wrote:
> > On 21 Feb, 14:43, "Uri Dimant" <u...@.iscar.co.il> wrote:
> > > Ahh, I see what you mean. I'm affraid you cannot do that
> > > "nomad" <d.bedg...@.ntlworld.com> wrote in message
> > >news:1172068385.736309.197940@.k78g2000cwa.googlegroups.com...
> > > > On 21 Feb, 14:16, "Uri Dimant" <u...@.iscar.co.il> wrote:
> > > >> nomad
> > > >> If I understood you correctly
> > > >> 1) Open Solution within you have added .sql files (1-for SP,2-for Views)
> > > >> 2) When you click on the file it opens a object explorer windows
> > > >> 3) Add your object to opened window (relevant for object type) and save
> > > >> it.
> > > >> So , the next time you open the file your script should be saved there
> > > >> "nomad" <d.bedg...@.ntlworld.com> wrote in message
> > > >>news:1172065721.265467.175850@.a75g2000cwd.googlegroups.com...
> > > >> > Hi,
> > > >> > I want to be able to use source control from within SQL 2005. I know
> > > >> > how to add a new solution with a project attached. And I know how to
> > > >> > add existing .sql scripts to it. My quesiton is, how can I split them
> > > >> > up so that I can add all stored procedure scripts into a folder called
> > > >> > Stored Procedures, all views into a folder called Views etc instead of
> > > >> > them all going into the Query folder.
> > > >> > Appreciate any help on this.
> > > > I know how to do all of this. My question is, in the solution you
> > > > have folders called Connections, Queries & Miscellaneous. I want to
> > > > be able to add folders called Stored Procedures, Views etc so that I
> > > > can add my SP .sql scripts into Stored Procedure folder, Views scripts
> > > > into Views folder etc so that it is easier to differentiate between
> > > > your scripts.
> > Thanks for your reply. I have got around it by having seperate
> > project for Stored Procedures and seperate project for Views.
> > Another question, if you don't mind. Obviously I will have these
> > scripted stored procedures and views within my solution, but that
> > still won't stop anyone from opening up the actual stored procedure or
> > view and altering it, without going through source control. Is there
> > a way of locking the actual stored procedures and views down so they
> > can only be altered via the scripts in source control?
> > Appreciate your help.
Uri, How would you restrict view access but still allow the users to
alter?
Thanks for your time
Dmaon|||> Uri, How would you restrict view access but still allow the users to
> alter?
So , you say , they won't see what they altered/cretaed? :-))
"nomad" <d.bedgood@.ntlworld.com> wrote in message
news:1172152747.129528.282960@.j27g2000cwj.googlegroups.com...
> On Feb 21, 3:31 pm, Zekske <Zek...@.discussions.microsoft.com> wrote:
>> Take a look at visual studio team edition for database professionals.
>> I think it's the answer to your problem.
>> More information
>> athttp://msdn2.microsoft.com/en-us/teamsystem/aa718807.aspx
>> regards
>> "nomad" wrote:
>> > On 21 Feb, 14:43, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> > > Ahh, I see what you mean. I'm affraid you cannot do that
>> > > "nomad" <d.bedg...@.ntlworld.com> wrote in message
>> > >news:1172068385.736309.197940@.k78g2000cwa.googlegroups.com...
>> > > > On 21 Feb, 14:16, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> > > >> nomad
>> > > >> If I understood you correctly
>> > > >> 1) Open Solution within you have added .sql files (1-for SP,2-for
>> > > >> Views)
>> > > >> 2) When you click on the file it opens a object explorer windows
>> > > >> 3) Add your object to opened window (relevant for object type)
>> > > >> and save
>> > > >> it.
>> > > >> So , the next time you open the file your script should be saved
>> > > >> there
>> > > >> "nomad" <d.bedg...@.ntlworld.com> wrote in message
>> > > >>news:1172065721.265467.175850@.a75g2000cwd.googlegroups.com...
>> > > >> > Hi,
>> > > >> > I want to be able to use source control from within SQL 2005. I
>> > > >> > know
>> > > >> > how to add a new solution with a project attached. And I know
>> > > >> > how to
>> > > >> > add existing .sql scripts to it. My quesiton is, how can I
>> > > >> > split them
>> > > >> > up so that I can add all stored procedure scripts into a folder
>> > > >> > called
>> > > >> > Stored Procedures, all views into a folder called Views etc
>> > > >> > instead of
>> > > >> > them all going into the Query folder.
>> > > >> > Appreciate any help on this.
>> > > > I know how to do all of this. My question is, in the solution you
>> > > > have folders called Connections, Queries & Miscellaneous. I want
>> > > > to
>> > > > be able to add folders called Stored Procedures, Views etc so that
>> > > > I
>> > > > can add my SP .sql scripts into Stored Procedure folder, Views
>> > > > scripts
>> > > > into Views folder etc so that it is easier to differentiate between
>> > > > your scripts.
>> > Thanks for your reply. I have got around it by having seperate
>> > project for Stored Procedures and seperate project for Views.
>> > Another question, if you don't mind. Obviously I will have these
>> > scripted stored procedures and views within my solution, but that
>> > still won't stop anyone from opening up the actual stored procedure or
>> > view and altering it, without going through source control. Is there
>> > a way of locking the actual stored procedures and views down so they
>> > can only be altered via the scripts in source control?
>> > Appreciate your help.
> Uri, How would you restrict view access but still allow the users to
> alter?
> Thanks for your time
> Dmaon
>

Help with SQL 2005 solutions

nomad
If I understood you correctly
1) Open Solution within you have added .sql files (1-for SP,2-for Views)
2) When you click on the file it opens a object explorer windows
3) Add your object to opened window (relevant for object type) and save it.
So , the next time you open the file your script should be saved there
"nomad" <d.bedgood@.ntlworld.com> wrote in message
news:1172065721.265467.175850@.a75g2000cwd.googlegr oups.com...
> Hi,
> I want to be able to use source control from within SQL 2005. I know
> how to add a new solution with a project attached. And I know how to
> add existing .sql scripts to it. My quesiton is, how can I split them
> up so that I can add all stored procedure scripts into a folder called
> Stored Procedures, all views into a folder called Views etc instead of
> them all going into the Query folder.
> Appreciate any help on this.
>
Ahh, I see what you mean. I'm affraid you cannot do that
"nomad" <d.bedgood@.ntlworld.com> wrote in message
news:1172068385.736309.197940@.k78g2000cwa.googlegr oups.com...
> On 21 Feb, 14:16, "Uri Dimant" <u...@.iscar.co.il> wrote:
> I know how to do all of this. My question is, in the solution you
> have folders called Connections, Queries & Miscellaneous. I want to
> be able to add folders called Stored Procedures, Views etc so that I
> can add my SP .sql scripts into Stored Procedure folder, Views scripts
> into Views folder etc so that it is easier to differentiate between
> your scripts.
>

Help with SQL 2005 solutions

Wednesday, March 7, 2012

Help with pivot table

Hi!

I`ve been struggling to find a solution to make a report from MS SQL, and I think pivot table is the key but I`m not sure on how to do this. Hope someone can help, here is the case :

I have one table "Products" and one table "Packages". Every product in the Products table has 1 - * Packages (unit which the product is sold by)

The Products table looks something like this :

Id | productText | productGroup | etc

And the Packages table like this :

Id | productId | packageType | weight | etc

What I want to do is to flat this out so I can get the Product and all it`s packages in one record. Something like this :

I would really appriciate if someone could help me out with this.

Regards,
KnutYou need to use DECODE for the purpose.

Help with ordering the SSIS packages the way they appear in the project

In solution explorer under SSIS Packages there are many packages. I name them specifically so that they should be ordered with their names, but no the solution explorer shows them in the order I added them, I couldn't figure out a way but removing all the packages and adding them in the right order to fix that problem. I have to kinda redo this if I want to add a new package not to the end.

Any ideas?

All here: http://solidqualitylearning.com/blogs/erik/archive/2005/08/08/1269.aspx

-Jamie

|||Thank you, I didn't want to edit the configuration without knowing what I was doing.

Monday, February 27, 2012

Help with multiple IIFs, or need suggestion of better solution.

I am trying to check multiple fields from a db to see if they have either a 1 or 0 value, and if there is a 1, then write a value into a text box. I need to check multiple fields, and if all of them are checked then I have to insert the value for each into the text box. If it was just checking one condition it woudl be easy, because I could just nest IIF's until it was true.

So I can't do because once the truth clause is satisfied it will exit the loop: IIF(Fields!Fielda.Value = 1,"Fielda",IIF(Fields!Fieldb.Value=1,"Fieldb"....)

I also cannot do:
=IIfFields!Fielda.Value=1,"Fielda,"")
=IifFields!Fieldb.Value=1,"Fieldb,"")

Is there a way to have a whole bunch of IIF's, or can anyone think of another way to do this?

Much appreciated.

Use the "And" operator. It would look like this:

iif (Fields!Fielda.Value = 1 and Fields!Fieldb.Value=1 and Fields!Fieldc.Value=1, "Fielda", "")

|||Ryan, I appreciate the answer, but I think you misunderstood. I want it to say if Fielda = 1 then insert text, and if Fieldb = 1 then insert text, not if all of them = 1.

This would be the ideal situation:

=IIF(Fields!Fielda.Value=1,"Fielda","")
IIF(Fields!Fieldb.Value=1,"Fieldb","")
IIF(Fields!Fieldc.Value=1,"Fieldc","")
And so on for all the fields for this particular text box.

Or another example (that I've tried that did not work)
=IIF(Fields!Fielda.Value=1,"Fielda","") &
IIF(Fields!Fieldb.Value=1,"Fieldb","") &
IIF(Fields!Fieldc.Value=1,"Fieldc","") &

I can't use what you said because that would only evaluate one statement, and I need to evaluate 8 different statements. That's the problem. Is there a way to have mutliple seperate IIF's in an expression like I have above? If not, is there another solution?
|||

One question is what is the datatype on the database field? If it is boolean then you should be able to do:

=IIF(Fields!Fielda.Value,"Fielda","") + IIF(Fields!Fieldb.Value,"Fieldb","")...

The + should work for concatenation since all of the fields area string. Another thing that I have seen is that you may have to do CDec on the database fields to force a datatype match.

=IIF(CDec(Fields!Fielda.Value)=1,"Fielda","") + IIF(CDec(Fields!Fieldb.Value)=1,"Fieldb","") ...

|||

Use the Report Properties.Code.Custom Code feature.

1.Create a function in the CODE section

2. Pass all your field values to the function

3. The return value is used in the textbox.

You have a lot more coding power in the CODE section than you do with expressions.

Hope this helps.

|||

Can you do it in SQL using case statement ?

|||

=switch(Fields!FieldA.Value = 1, "A", Fields!FieldB.Value = 1, "B", true, "")

Thanks, Donovan.

Wednesday, March 28, 2012

Help with SQL 2005 solutions

Help with SQL 2005 solutions

Help with SQL 2005 solutions

Wednesday, March 7, 2012

Help with pivot table

Help with ordering the SSIS packages the way they appear in the project

Monday, February 27, 2012

Help with multiple IIFs, or need suggestion of better solution.

Max Query

Blog Archive

About Me