How to create scripts (drop then create) in sql 2005

I'm using sql 2005. One thing I could do in sql 2k which I don't see in sql
05 is when I script objects in EM it would check to see if the object exists
first, and if so, it would drop it before creating it. I don't see such an
option in sql 05 SSMS. Its a much better scripting wizard but seems to be
missing the most basic functions.
any advice?
Thanks.
moondaddy@.noemail.noemailAs far as I know there is no such choice in the wizard, you would need to
run it twice, one for 'Generate DROP statements only' and one for 'Generate
CREATE statements only'.
Note that the SP1 description still shows 'Generate the script as DROP
statements, CREATE statements, or DROP statements followed by CREATE
statements."
Ben Nevarez, MCDBA, OCP
Database Administrator
"moondaddy" wrote:

> I'm using sql 2005. One thing I could do in sql 2k which I don't see in s
ql
> 05 is when I script objects in EM it would check to see if the object exis
ts
> first, and if so, it would drop it before creating it. I don't see such a
n
> option in sql 05 SSMS. Its a much better scripting wizard but seems to be
> missing the most basic functions.
> any advice?
> Thanks.
> --
> moondaddy@.noemail.noemail
>
>|||Check out http://www.sqlteam.com/item.asp?ItemID=23185 and
http://weblogs.sqlteam.com/billg/ar...1/22/8414.aspx.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"moondaddy" <moondaddy@.noemail.noemail> wrote in message
news:uLMUBQWdGHA.3712@.TK2MSFTNGP03.phx.gbl...
> I'm using sql 2005. One thing I could do in sql 2k which I don't see in s
ql 05 is when I script
> objects in EM it would check to see if the object exists first, and if so,
it would drop it before
> creating it. I don't see such an option in sql 05 SSMS. Its a much bette
r scripting wizard but
> seems to be missing the most basic functions.
> any advice?
> Thanks.
> --
> moondaddy@.noemail.noemail
>

How to create objects under dbo without DDL admin rights??

Does someone have any suggestions on what rights to grant a user to allow
them to only create stored procedures, functions under dbo without granting
DDL admin?Hi,
Open Database properties and the under permissions you can set for user to
create and alter objects.
Danijel Novak
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
> Does someone have any suggestions on what rights to grant a user to allow
> them to only create stored procedures, functions under dbo without
> granting
> DDL admin?|||That I know, but I want a user to be able to create a new procedure, but
create it as dbo.procedure. As far as I know, you cannot do this without DDL
Admin rights.
"Danijel Novak" wrote:

> Hi,
> Open Database properties and the under permissions you can set for user to
> create and alter objects.
> --
> Danijel Novak
>
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
>
>|||In SQL 2000 and earlier versions, the user must be either:
- a db_ddladmin role member
- a db_owner role member
- the database owner
- a sysadmin role member
Of course, membership in these role provides considerably more permissions
as well.
Note that the ability to create dbo-owned procs and functions essentially
allows users to retrieve and manipulate data in all dbo-owned tables. This
is one reason why one typically allows only DBAs to create dbo-owned
objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
> Does someone have any suggestions on what rights to grant a user to allow
> them to only create stored procedures, functions under dbo without
> granting
> DDL admin?|||Hi,
true said by Dan , that's why ! we only do it with only
- a db_ddladmin role member
- a db_owner role member
- the database owner
- a sysadmin role member , so in shore please don't permit it to not dbo
user.
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"Dan Guzman" wrote:

> In SQL 2000 and earlier versions, the user must be either:
> - a db_ddladmin role member
> - a db_owner role member
> - the database owner
> - a sysadmin role member
> Of course, membership in these role provides considerably more permissions
> as well.
> Note that the ability to create dbo-owned procs and functions essentially
> allows users to retrieve and manipulate data in all dbo-owned tables. Thi
s
> is one reason why one typically allows only DBAs to create dbo-owned
> objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Barr" <JohnBarr@.discussions.microsoft.com> wrote in message
> news:344873E1-75CC-441F-A9FF-B63ECF38E7A9@.microsoft.com...
>
>

How to create dynamic reports based on custom business objects?

How can we create dynamic reports(or reports created by the end user) by
selected fields from the list of custom business objects.
We would like to offer full data consolidation. Right Crystal offers this
type of functionality but I would like to steer away from that.
Any ideas how to proceed or whether is it even feasible. We do not want to
use reflection on the objects, I mean we can use it but that would not be the
best approach.
ThanksHi Amarnath,
you said SSRS has a new feature using which report builder could be given to
end user to create reports.
Is this Report builder browser based(so that the end user does not need to
install anything) and could design reports and modify the existing reports,
if yes, how to make use of this?
Thanks
Ponnu
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:9A2A1500-E374-45F1-8288-56CB8EC97883@.microsoft.com...
> What do you mean by custom BO, is it in the form of dll or assembly sort
> of
> thing then you can always refer the assembly in SS 2005. If it is in the
> form
> of Stored proc then you can always refer that in the report query..
> Infact in RS you need to create a report model and give it to the end
> users,
> SSRS new feature has report builder which is given to the end user to
> create
> their own reports.
>
> Amarnath
>
> "Atul Bahl" wrote:
>> How can we create dynamic reports(or reports created by the end user) by
>> selected fields from the list of custom business objects.
>> We would like to offer full data consolidation. Right Crystal offers this
>> type of functionality but I would like to steer away from that.
>> Any ideas how to proceed or whether is it even feasible. We do not want
>> to
>> use reflection on the objects, I mean we can use it but that would not be
>> the
>> best approach.
>> Thanks

How to Copy Objects with SQL 2005 SSIS

The problem is this: In SQL 2000 DTS there was an option for "Copy Objects and Data Between SQL Servers". However, this option has been removed in SQL 2005 SSIS. Apparently the only way to do this in SQL 2005 is to create a .DTSX package in SQL Server Business Intelligence Developement Studio or VS 2005. You do this by creating a new Integration Service Project and using the Transfer SQL Objects Task. Within the properties of this task you can select any of the options that were available in the SQL 2000 DTS export wizard. I have set up a test package that will copy a stored procedure from one db to another but I am unable to get it to work. It runs fine but the result is that the SP is not copied.
I am new to Visual Studio and I think I probably just need help in know ing how to run a package in SQL Server Management Studio. I was able to import the package into SSIS in the Management Studio and run it without errors not with the expected result (the copy of an SP from one db to another). I'm sure there are people besides me who would like to have the ability to easily perform ad hoc copies of objects between SQL servers. If anyone has any experience with using a SSIS package to do this please help. Thanks!Set CopySchema to "True" in your "Transfer SQL Server Objects" task.|||Thanks for your reply. When I do this and run the package I get the error "Stored procedure uspTest does not exist at the source". Actually the SP does exist at the source and I have dbo access to the entire db. Not sure what is breaking down here.

How to copy object between SqlServer 2005

How to copy objects like View or Tables from on SqlServer 2005 to another?
If you are talking about just hte DDL then script it out and run that script
on the other server. If you need data as well you can use the wizard in
SSIS.
Andrew J. Kelly SQL MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ey%23EdWOJGHA.1728@.TK2MSFTNGP14.phx.gbl...
> How to copy objects like View or Tables from on SqlServer 2005 to another?
>

How to copy object between SqlServer 2005

How to copy objects like View or Tables from on SqlServer 2005 to another?If you are talking about just hte DDL then script it out and run that script
on the other server. If you need data as well you can use the wizard in
SSIS.
--
Andrew J. Kelly SQL MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ey%23EdWOJGHA.1728@.TK2MSFTNGP14.phx.gbl...
> How to copy objects like View or Tables from on SqlServer 2005 to another?
>

How to copy object between SqlServer 2005

How to copy objects like View or Tables from on SqlServer 2005 to another?If you are talking about just hte DDL then script it out and run that script
on the other server. If you need data as well you can use the wizard in
SSIS.
Andrew J. Kelly SQL MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ey%23EdWOJGHA.1728@.TK2MSFTNGP14.phx.gbl...
> How to copy objects like View or Tables from on SqlServer 2005 to another?
>