Developing with Enterprise tools, deploying without them

I have designed my database using Sql 2000 Developer Edition, but I need to apply that design to MSDE. I can run the script on a database using MSDE through Enterprise manager on my machine and, using Enterprise manager again, back the database up. How do I restore that backup to a MSDE database on another machine without Enterprise tools?

In other words, I need to install MSDE on another machine and restore a backed up database design to it, without Enterprise tools on the host machine.

Also: If I want to use exactly the same database I have developed using Enterprise tools packaged with Sql 2000 Developer Edition, how may I back 'that' database up so that it may be restored to a MSDE database or replicate the database in question to an MSDE client?

Thank you,
John SpurlinYou can execute a script or query using OSQL from the command line. See the follwoing MSDN article for more details:

osql Utility

Hope this helps

Regards

Wayne Phipps

Developing a SQL server DB (noob question)

I'm beginning development on a medium/ sized in-house web based system. Well moving from asp/mysql to asp.net/sql server. Re-doing DB design as well.

My question is does it make sense for me to develop the SQL DB in Visual Studio 2005 pro, and then later move it to a real SQL server? Or basically what is the best and/or most practical way to do this development. Any other IDE's out there?

Thanks

I'd just use SQL Server from the outset. I find Entreprise Manager cumbersome to use, so I just use it to create the database, then create an Access adp file as my main interface to create tables, views and stored procedures. adp files give you the speed and functionality of Access when manipulating SQL Server DB's. I do go back to Entreprise Manager to do things like create table relationships.

|||

So you're basically suggesting developing the DB using the Access IDE, correct? I fail to see how that would be beneficial, Access has different data types than SQL server. When using an ADP file would Access then know the correct column types? Also, how would that ease the development of my web application?

Does anyone else agree with the above comment? The lack of responses makes me feel like everyone else thinks that is the best answer.

|||

With an adp file, you are essentially using the interface of Access to manage your SQL Database. It is still a full SQL Server database; it is not an mdb with native Access types. This means that the datatypes available and field options are all from SQL Server, (e.g. you have VARCHAR instead of Text, etc). You can also do things that create Stored Procedures. It has most of the functionality of Entreprise Manager for creating and maintaining your database.

Reasons I like adp files over Entreprise Manager:

1. adp files allow you to build forms for basic data entry / editing

2. Entreprise Manager Stored Procedure windows are modal. If you are writing a procedure and need to look up a field name in a table, you have to close the window (which you can't do with partially written syntax) first. with an adp the Procedure window can be minimized or tiled.

3. adp files allow you to sort and filter tables in datasheet view. Entreprise Manager doesn't.

4. Speed. adp files are faster for looking up data.

adp files do lack some features, such as the ability to build relationships or triggers. For this use Entreprise Manager.

Developer licensing

Ok, simple question:

We have 10 developers. All 10 use ONE server to do development work (design, coding, testing ONLY!). We have a Developer Edition licence for EACH developer. Is this OK? We have 10GB plus in test data and do not want to replicate that to each desktop.

According to the Developer Edition EULA, I would say NO. It looks like you can only have one developer (and a few testers, I forget the number) per server. It says NOTHING about having more than one developer access the same server under this license. The Developer Edition EULA appears to be designed around installing a database on a desktop just for that developer, not installing a central database that all developers use.AFAIK thats fine.

You would only need to switch from the Dev licence once your apps are in an actual production environment ie making someone very rich. In which case you would need the appropriate server licence, cal's, vs's and so forth.

It is a very difficult field to understand, however Microsoft are getting ever better at making things clear and easy to understand; this site should make it even easier for you to answer questions; http://www.microsoft.com/licensing/default.mspx

Developer design Best Practices

I have about twenty three MSSQL servers in use and need to consolidate those
down to as few as possible. What I would like to do is haev two nodes for
production and two nodes for development. The main concern my developers keep
throwing at me is that they need a generic account to use for creating tables
and stored procedures and the like so when they code them for web use they
don't have to worry about who owns the table. In thepast they utilized this
approacha and as soon as i went to tighten down on the logins they started
complaining about needing to have this generic account. Can Someone help me
with a guideline white paper or some resources to identify the best way to
payout both the server end for development and production and then the
security to go along with that design. These servers would only be used in an
intranet environment although the databases themselves may eventually migrate
to a dmz. Thanks in advance
Gkurtas wrote:
> I have about twenty three MSSQL servers in use and need to
> consolidate those down to as few as possible. What I would like to do
> is haev two nodes for production and two nodes for development. The
> main concern my developers keep throwing at me is that they need a
> generic account to use for creating tables and stored procedures and
> the like so when they code them for web use they don't have to worry
> about who owns the table. In thepast they utilized this approacha and
> as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone
> help me with a guideline white paper or some resources to identify
> the best way to payout both the server end for development and
> production and then the security to go along with that design. These
> servers would only be used in an intranet environment although the
> databases themselves may eventually migrate to a dmz. Thanks in
> advance
Are you saying the developers want an admistrator account for the
production servers? I've often been on projects where the lead developer
on a project would have an admin account on the development server, but
usually other developers do not. I wouldn't see why more than one or two
project managers or senior developers would need admin rights on the dev
server. On a production server, no way.
David G.
|||In addition to David's post which I couldn't agree more.
My understanding is that you are talking about object owner, i.e. special
database user. It is a good practice if you have only one owner for all
objects. This way you don't have a broken ownership chain when a sp or a
view use table or other object. This way SQL Server checks permissions on
the topmost object in the chain only. This is not only performance gain;
administration is easier as well, since you have to grant permissions on the
topmost objects only. Additionally you get better security, because you can
have a layer of sp's and views between application/users and tables. So, if
you decide for one owner only, then I suggest you to use dbo, as this user
can't be dropped and leave orphaned objects.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in message
news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
> I have about twenty three MSSQL servers in use and need to consolidate
those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development. The main concern my developers
keep
> throwing at me is that they need a generic account to use for creating
tables
> and stored procedures and the like so when they code them for web use they
> don't have to worry about who owns the table. In thepast they utilized
this
> approacha and as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone help
me
> with a guideline white paper or some resources to identify the best way to
> payout both the server end for development and production and then the
> security to go along with that design. These servers would only be used in
an
> intranet environment although the databases themselves may eventually
migrate
> to a dmz. Thanks in advance
|||Hi,
I agree with the two post's . My Opinion: Dev teams
should not connect to production servers, there must be a
DBA Team that checks and runs all DDL statements on
production environments. This way the DBA team can control
all the database structure and you have a central point to
alter production servers. This could lead to a more
effective checking and control. On the other hand you may
choose to have dbo ownership in all objects since the
admin accounts are all from the DBA team or for instance
you could create an different account just for objects
ownership an either protect the account just for DBA team
use.
Hope this helps
Regards

>--Original Message--
>In addition to David's post which I couldn't agree more.
>My understanding is that you are talking about object
owner, i.e. special
>database user. It is a good practice if you have only one
owner for all
>objects. This way you don't have a broken ownership chain
when a sp or a
>view use table or other object. This way SQL Server
checks permissions on
>the topmost object in the chain only. This is not only
performance gain;
>administration is easier as well, since you have to grant
permissions on the
>topmost objects only. Additionally you get better
security, because you can
>have a layer of sp's and views between application/users
and tables. So, if
>you decide for one owner only, then I suggest you to use
dbo, as this user
>can't be dropped and leave orphaned objects.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
to consolidate[vbcol=seagreen]
>those
haev two nodes for[vbcol=seagreen]
concern my developers[vbcol=seagreen]
>keep
use for creating[vbcol=seagreen]
>tables
them for web use they[vbcol=seagreen]
thepast they utilized[vbcol=seagreen]
>this
logins they started[vbcol=seagreen]
Can Someone help[vbcol=seagreen]
>me
identify the best way to[vbcol=seagreen]
production and then the[vbcol=seagreen]
would only be used in[vbcol=seagreen]
>an
may eventually
>migrate
>
>.
>
|||I am interested in how you are going to go about consolidating 23 servers
down to 4.
Are you going to use more beefier server/storage to host multiple database
in 1 server or multiple instances of SQL Server, or utilize VMs to run
non-production SQL Server?
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote
>I have about twenty three MSSQL servers in use and need to consolidate
>those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development.
|||I have exactly this issue, as well.
We have a lot of contractors come in and devlop. In the past, they had
their NT Login and were put into the dbo role.
However, this creates a LOT of problems once the contractor leaves. We have
to change the owner of all the objects before we can drop the contractor. My
predecessor simply left the logins active. (!!)
So, what you are saying is you give them the dbo password and let them
connect as that user? How then do you know who did what?
This is only for Dev. Developers are not given access to production.
Limited access to test.
|||Just to clarify - currently all our databases are owned by different people,
some of them gone, and objects within the database are owned by different
people, some of them gone. I would like to have one owner for all of them to
simplify things, and what I have done previously is have a user 'dbo' who is
assigned to the dbo role. But then, I haven't had this many developers
accessing things before.
"Amanda" wrote:

> I have exactly this issue, as well.
> We have a lot of contractors come in and devlop. In the past, they had
> their NT Login and were put into the dbo role.
> However, this creates a LOT of problems once the contractor leaves. We have
> to change the owner of all the objects before we can drop the contractor. My
> predecessor simply left the logins active. (!!)
> So, what you are saying is you give them the dbo password and let them
> connect as that user? How then do you know who did what?
> This is only for Dev. Developers are not given access to production.
> Limited access to test.
>
|||Amanda wrote:[vbcol=seagreen]
> Just to clarify - currently all our databases are owned by different
> people, some of them gone, and objects within the database are owned
> by different people, some of them gone. I would like to have one
> owner for all of them to simplify things, and what I have done
> previously is have a user 'dbo' who is assigned to the dbo role. But
> then, I haven't had this many developers accessing things before.
> "Amanda" wrote:
You can let developers create procedures under their own user ids for
testing purposes. When they are ready to have the procedure elevated to
dbo status in the development database (once it's been tested), they
could ask the senior developer or dba assigned to the project. They
should then remove the one they own to avoid execution problems during
testing.
In general, I would say all objects in a database should be dbo owned by
the time you hit production. In fact, it's faster to execute procedures
using the "dbo." prefix rather than leaving it off if you are not the
dbo (as is the case for users in a production environment). This avoids
any potential ownership issues as well.
David G.
|||OK - this is REALLY freaky.
Since there is only me as a dba, I can't go with the option of scripting all
their objects in dev. We do that for production and test - they develop them
and then I move them, and the owner is dbo.
There is NO DBO User in our dev database!!
It simply doesn't show up in the GUI. Of course it exists in the system
table.
I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure this
can be done via SQL, but it is pretty freaky that dbo isn't showing up.
we have moved this database all over - but I've never seen this happen
before. When I run sp_change_users_login it doesn't show up in report, nor
can I fix it.
Do you guys think I should be concerned?
|||Amanda wrote:
> OK - this is REALLY freaky.
> Since there is only me as a dba, I can't go with the option of
> scripting all their objects in dev. We do that for production and
> test - they develop them and then I move them, and the owner is dbo.
> There is NO DBO User in our dev database!!
> It simply doesn't show up in the GUI. Of course it exists in the
> system table.
> I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure
> this can be done via SQL, but it is pretty freaky that dbo isn't
> showing up.
> we have moved this database all over - but I've never seen this happen
> before. When I run sp_change_users_login it doesn't show up in
> report, nor can I fix it.
> Do you guys think I should be concerned?
I adminit it can be a little confusing. You are correct in that "dbo" is
not really a user in the system. When an object is owned by "dbo" it
just means it is owned by the database owner, which is normally a system
administrator.
By logging into a database as "sa" or an administrator account or a
member of the database owner group, you'll end up creating objects as
"dbo" if you do not specify an owner in the create statement. If you
login as a user that can create tables and specify the user name before
the object name, you end up creating a table owned by that user name.
Someone should be monitoring all the database object creation in
development to make sure it is done properly. It's not always an option
to have a busy dba monitoring development servers. In this case, I
suggest you designate a project manager or senior developer to perform
object creation in the development database as "dbo" owned objects.
Developers create procedures using their own IDs and when they are ready
tell someone on the team to compile as dbo. That's one option.
I realize this adds a level of overhead to development for those
developers who do not have proper rights. But with any project there is
an element of overhead designed to prevent accidents like someone
dropping tables or procedures accidentally.
If you want to mitigate this somewhat, you can let all developers create
procedures, but only allow senior developers the right to manage tables
and views.
In any case, this is sometimes a project manager's decision and maybe
you should defer to that person (of course, give him/her your
recommendations).
In any case, no access to production, except possibly the project
manager. Remember that if a developer gets on the production database
and messes something up, you inherit the problem and the responsibility
and all blame... well, need I say more.
David G.

Developer design Best Practices

I have about twenty three MSSQL servers in use and need to consolidate those
down to as few as possible. What I would like to do is haev two nodes for
production and two nodes for development. The main concern my developers keep
throwing at me is that they need a generic account to use for creating tables
and stored procedures and the like so when they code them for web use they
don't have to worry about who owns the table. In thepast they utilized this
approacha and as soon as i went to tighten down on the logins they started
complaining about needing to have this generic account. Can Someone help me
with a guideline white paper or some resources to identify the best way to
payout both the server end for development and production and then the
security to go along with that design. These servers would only be used in an
intranet environment although the databases themselves may eventually migrate
to a dmz. Thanks in advanceGkurtas wrote:
> I have about twenty three MSSQL servers in use and need to
> consolidate those down to as few as possible. What I would like to do
> is haev two nodes for production and two nodes for development. The
> main concern my developers keep throwing at me is that they need a
> generic account to use for creating tables and stored procedures and
> the like so when they code them for web use they don't have to worry
> about who owns the table. In thepast they utilized this approacha and
> as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone
> help me with a guideline white paper or some resources to identify
> the best way to payout both the server end for development and
> production and then the security to go along with that design. These
> servers would only be used in an intranet environment although the
> databases themselves may eventually migrate to a dmz. Thanks in
> advance
Are you saying the developers want an admistrator account for the
production servers? I've often been on projects where the lead developer
on a project would have an admin account on the development server, but
usually other developers do not. I wouldn't see why more than one or two
project managers or senior developers would need admin rights on the dev
server. On a production server, no way.
--
David G.|||In addition to David's post which I couldn't agree more.
My understanding is that you are talking about object owner, i.e. special
database user. It is a good practice if you have only one owner for all
objects. This way you don't have a broken ownership chain when a sp or a
view use table or other object. This way SQL Server checks permissions on
the topmost object in the chain only. This is not only performance gain;
administration is easier as well, since you have to grant permissions on the
topmost objects only. Additionally you get better security, because you can
have a layer of sp's and views between application/users and tables. So, if
you decide for one owner only, then I suggest you to use dbo, as this user
can't be dropped and leave orphaned objects.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in message
news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
> I have about twenty three MSSQL servers in use and need to consolidate
those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development. The main concern my developers
keep
> throwing at me is that they need a generic account to use for creating
tables
> and stored procedures and the like so when they code them for web use they
> don't have to worry about who owns the table. In thepast they utilized
this
> approacha and as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone help
me
> with a guideline white paper or some resources to identify the best way to
> payout both the server end for development and production and then the
> security to go along with that design. These servers would only be used in
an
> intranet environment although the databases themselves may eventually
migrate
> to a dmz. Thanks in advance|||Hi,
I agree with the two post's :). My Opinion: Dev teams
should not connect to production servers, there must be a
DBA Team that checks and runs all DDL statements on
production environments. This way the DBA team can control
all the database structure and you have a central point to
alter production servers. This could lead to a more
effective checking and control. On the other hand you may
choose to have dbo ownership in all objects since the
admin accounts are all from the DBA team or for instance
you could create an different account just for objects
ownership an either protect the account just for DBA team
use.
Hope this helps :)
Regards
>--Original Message--
>In addition to David's post which I couldn't agree more.
>My understanding is that you are talking about object
owner, i.e. special
>database user. It is a good practice if you have only one
owner for all
>objects. This way you don't have a broken ownership chain
when a sp or a
>view use table or other object. This way SQL Server
checks permissions on
>the topmost object in the chain only. This is not only
performance gain;
>administration is easier as well, since you have to grant
permissions on the
>topmost objects only. Additionally you get better
security, because you can
>have a layer of sp's and views between application/users
and tables. So, if
>you decide for one owner only, then I suggest you to use
dbo, as this user
>can't be dropped and leave orphaned objects.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in
message
>news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
>> I have about twenty three MSSQL servers in use and need
to consolidate
>those
>> down to as few as possible. What I would like to do is
haev two nodes for
>> production and two nodes for development. The main
concern my developers
>keep
>> throwing at me is that they need a generic account to
use for creating
>tables
>> and stored procedures and the like so when they code
them for web use they
>> don't have to worry about who owns the table. In
thepast they utilized
>this
>> approacha and as soon as i went to tighten down on the
logins they started
>> complaining about needing to have this generic account.
Can Someone help
>me
>> with a guideline white paper or some resources to
identify the best way to
>> payout both the server end for development and
production and then the
>> security to go along with that design. These servers
would only be used in
>an
>> intranet environment although the databases themselves
may eventually
>migrate
>> to a dmz. Thanks in advance
>
>.
>|||I am interested in how you are going to go about consolidating 23 servers
down to 4.
Are you going to use more beefier server/storage to host multiple database
in 1 server or multiple instances of SQL Server, or utilize VMs to run
non-production SQL Server?
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote
>I have about twenty three MSSQL servers in use and need to consolidate
>those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development.|||I have exactly this issue, as well.
We have a lot of contractors come in and devlop. In the past, they had
their NT Login and were put into the dbo role.
However, this creates a LOT of problems once the contractor leaves. We have
to change the owner of all the objects before we can drop the contractor. My
predecessor simply left the logins active. (!!)
So, what you are saying is you give them the dbo password and let them
connect as that user? How then do you know who did what?
This is only for Dev. Developers are not given access to production.
Limited access to test.|||Amanda wrote:
> Just to clarify - currently all our databases are owned by different
> people, some of them gone, and objects within the database are owned
> by different people, some of them gone. I would like to have one
> owner for all of them to simplify things, and what I have done
> previously is have a user 'dbo' who is assigned to the dbo role. But
> then, I haven't had this many developers accessing things before.
> "Amanda" wrote:
>> I have exactly this issue, as well.
>> We have a lot of contractors come in and devlop. In the past, they
>> had their NT Login and were put into the dbo role.
>> However, this creates a LOT of problems once the contractor leaves.
>> We have to change the owner of all the objects before we can drop
>> the contractor. My predecessor simply left the logins active. (!!)
>> So, what you are saying is you give them the dbo password and let
>> them connect as that user? How then do you know who did what?
>> This is only for Dev. Developers are not given access to production.
>> Limited access to test.
You can let developers create procedures under their own user ids for
testing purposes. When they are ready to have the procedure elevated to
dbo status in the development database (once it's been tested), they
could ask the senior developer or dba assigned to the project. They
should then remove the one they own to avoid execution problems during
testing.
In general, I would say all objects in a database should be dbo owned by
the time you hit production. In fact, it's faster to execute procedures
using the "dbo." prefix rather than leaving it off if you are not the
dbo (as is the case for users in a production environment). This avoids
any potential ownership issues as well.
--
David G.|||Amanda wrote:
> OK - this is REALLY freaky.
> Since there is only me as a dba, I can't go with the option of
> scripting all their objects in dev. We do that for production and
> test - they develop them and then I move them, and the owner is dbo.
> There is NO DBO User in our dev database!!
> It simply doesn't show up in the GUI. Of course it exists in the
> system table.
> I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure
> this can be done via SQL, but it is pretty freaky that dbo isn't
> showing up.
> we have moved this database all over - but I've never seen this happen
> before. When I run sp_change_users_login it doesn't show up in
> report, nor can I fix it.
> Do you guys think I should be concerned'
I adminit it can be a little confusing. You are correct in that "dbo" is
not really a user in the system. When an object is owned by "dbo" it
just means it is owned by the database owner, which is normally a system
administrator.
By logging into a database as "sa" or an administrator account or a
member of the database owner group, you'll end up creating objects as
"dbo" if you do not specify an owner in the create statement. If you
login as a user that can create tables and specify the user name before
the object name, you end up creating a table owned by that user name.
Someone should be monitoring all the database object creation in
development to make sure it is done properly. It's not always an option
to have a busy dba monitoring development servers. In this case, I
suggest you designate a project manager or senior developer to perform
object creation in the development database as "dbo" owned objects.
Developers create procedures using their own IDs and when they are ready
tell someone on the team to compile as dbo. That's one option.
I realize this adds a level of overhead to development for those
developers who do not have proper rights. But with any project there is
an element of overhead designed to prevent accidents like someone
dropping tables or procedures accidentally.
If you want to mitigate this somewhat, you can let all developers create
procedures, but only allow senior developers the right to manage tables
and views.
In any case, this is sometimes a project manager's decision and maybe
you should defer to that person (of course, give him/her your
recommendations).
In any case, no access to production, except possibly the project
manager. Remember that if a developer gets on the production database
and messes something up, you inherit the problem and the responsibility
and all blame... well, need I say more.
--
David G.|||The database owner login is a special case and is not specified like a
regular user. You can execute sp_changedbowner to specify the owner or
correct the mapping following a restore or attach:
USE MyDatabase
EXEC sp_changedbowner 'PIMDevDBO'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amanda" <Amanda@.discussions.microsoft.com> wrote in message
news:C9078D21-D0C3-468E-A328-FC0AB622B95F@.microsoft.com...
> OK - this is REALLY freaky.
> Since there is only me as a dba, I can't go with the option of scripting
> all
> their objects in dev. We do that for production and test - they develop
> them
> and then I move them, and the owner is dbo.
> There is NO DBO User in our dev database!!
> It simply doesn't show up in the GUI. Of course it exists in the system
> table.
> I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure this
> can be done via SQL, but it is pretty freaky that dbo isn't showing up.
> we have moved this database all over - but I've never seen this happen
> before. When I run sp_change_users_login it doesn't show up in report,
> nor
> can I fix it.
> Do you guys think I should be concerned'
>

Dev/Learning Environment

I haven't used RS yet as we have leaned on Crystal Reports but I want to
learn and design in RS. We already have a live intranet RS page or two
developed by former employees hence I want to fill that gap of knowledge.
My questions :
While playing and designing, is it better to take my SQL dev box and add RS,
IIS, .net etc or should I just to tap into the live server that's already set
up? Any risks to running dev on the live box?
The data would reside on a third sql server.
Thanks for any best practice advice you can spare.
--
Mike
If data falls in the woods and nobody is there to see it ...... ?One gotcha that I had was getting SharePoint to use RS. Apparenlty I needed
to install some additional DLL to allow SharePoint to display RS as web
parts and for about an hour I had production SP down as things didn't go
smoothly.
I know you didn't mention SP - but in case you are gong to look forward to
it as your web portal.
"Tigermikefl" <Tigermikefl@.discussions.microsoft.com> wrote in message
news:23619A8F-2482-4CAA-9241-9C854FCE7898@.microsoft.com...
>I haven't used RS yet as we have leaned on Crystal Reports but I want to
> learn and design in RS. We already have a live intranet RS page or two
> developed by former employees hence I want to fill that gap of knowledge.
> My questions :
> While playing and designing, is it better to take my SQL dev box and add
> RS,
> IIS, .net etc or should I just to tap into the live server that's already
> set
> up? Any risks to running dev on the live box?
> The data would reside on a third sql server.
> Thanks for any best practice advice you can spare.
> --
> Mike
> If data falls in the woods and nobody is there to see it ...... ?

Determining runtime or design time during Validate - workarounds?

I've seen a couple of posts in this forum on this subject. If anyone knows of a workaround it would be great to hear.

The problem is this. I'm writing a component that looks a bit like an OLE DB destination: it writes to something that looks like a table. During design time I want the component to update the list of available destination columns if they change - so I want Validate to return VS_NEEDSNEWMETADATA if it detects a change. However during runtime I only want to validate that the component will run ok. So I still want to check what the destination looks like but if, say, someone has just added a column then my determination is it is ok to procede with the execution.

ValidateExternalMetadata doesn't help in this case because I still want to validate against the destination. I just don't want to raise VS_NEEDSNEWMETADATA during runtime because it aborts execution and I can determine that although there is a change to the destination it is not one that will cause the component to fail.

Any thoughts and experience on this would be great to hear!

Martin

Hmmm...

I can accept that if the destination changes you still want to return VS_ISVALID based on some logic that you will code. But surely the same logic applies at design-time as well doesn't it?

I'm afraid I don't know of a way to determine if the Validate() method is running at design-time or execution-time or not.

-Jamie

|||

Hi Jamie,

Thanks a lot for your thoughts. Yes that's right: I definitely want to return VS_ISVALID at runtime. But I need to return VS_NEEDSNEWMETADATA at design time because (as far as I understand SSIS component coding) that is the only way to instruct the SSIS design environment that you have detected a change that should update the meta data. If I didn't return VS_NEEDSNEWMETADATA then the component's meta data would be frozen until the user did something goofy like change the destination table name property to something else and then back again - we've all used too many apps like that :-)

One thing I do want to avoid is a scheme where the designing user has to remember to flick some component property when they release their package into the runtime environment.

My current thoughts for a workaround are:

1. If I create a custom UI then I would know if the user pops up the custom designer - I could delay updating the meta data until then. Pretty cheesey though.

2. I introduce a package variable which could be set by the user and checked by my component. If it is true then Validate only ever returns VS_ISVALID. I think there is something about package variables only having their default value at design time, but can have other values at runtime.

Cheers,
Martin

|||

Hi Martin,

the curent Validate/ReinitializeMetadata mechanism is simply not designed for this. In our definition VS_NEEDSNEWMETADATA means there is a metadata mismatch and it will most likely cause the execution to fail, so it is not a valid state.

If you need this logic to be applied only at the design-time I would put it to a custom UI. You can add extra validation and refresh services to you implementation of IDTScomponentUI, and mimic the Validate/RMD protocol.

HTH,

Bob

Determining runtime or design time during Validate - workarounds?

I've seen a couple of posts in this forum on this subject. If anyone knows of a workaround it would be great to hear.

The problem is this. I'm writing a component that looks a bit like an OLE DB destination: it writes to something that looks like a table. During design time I want the component to update the list of available destination columns if they change - so I want Validate to return VS_NEEDSNEWMETADATA if it detects a change. However during runtime I only want to validate that the component will run ok. So I still want to check what the destination looks like but if, say, someone has just added a column then my determination is it is ok to procede with the execution.

ValidateExternalMetadata doesn't help in this case because I still want to validate against the destination. I just don't want to raise VS_NEEDSNEWMETADATA during runtime because it aborts execution and I can determine that although there is a change to the destination it is not one that will cause the component to fail.

Any thoughts and experience on this would be great to hear!

Martin

Hmmm...

I can accept that if the destination changes you still want to return VS_ISVALID based on some logic that you will code. But surely the same logic applies at design-time as well doesn't it?

I'm afraid I don't know of a way to determine if the Validate() method is running at design-time or execution-time or not.

-Jamie

|||

Hi Jamie,

Thanks a lot for your thoughts. Yes that's right: I definitely want to return VS_ISVALID at runtime. But I need to return VS_NEEDSNEWMETADATA at design time because (as far as I understand SSIS component coding) that is the only way to instruct the SSIS design environment that you have detected a change that should update the meta data. If I didn't return VS_NEEDSNEWMETADATA then the component's meta data would be frozen until the user did something goofy like change the destination table name property to something else and then back again - we've all used too many apps like that :-)

One thing I do want to avoid is a scheme where the designing user has to remember to flick some component property when they release their package into the runtime environment.

My current thoughts for a workaround are:

1. If I create a custom UI then I would know if the user pops up the custom designer - I could delay updating the meta data until then. Pretty cheesey though.

2. I introduce a package variable which could be set by the user and checked by my component. If it is true then Validate only ever returns VS_ISVALID. I think there is something about package variables only having their default value at design time, but can have other values at runtime.

Cheers,
Martin

|||

Hi Martin,

the curent Validate/ReinitializeMetadata mechanism is simply not designed for this. In our definition VS_NEEDSNEWMETADATA means there is a metadata mismatch and it will most likely cause the execution to fail, so it is not a valid state.

If you need this logic to be applied only at the design-time I would put it to a custom UI. You can add extra validation and refresh services to you implementation of IDTScomponentUI, and mimic the Validate/RMD protocol.

HTH,

Bob