Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
Ori
Use the DTS copy objects function. You can set this up easily via the
wizard. Watch out for the option to copy dependent objects as well, as you
may wind up moving a table along with a SP.
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori
|||Take a look at Data Transformation Services. Right-click Tables, select All
Tasks > Export data, and when you get to the screen that offers three
choices, select "Copy objects and data..."
The wizard is fairly self-explanatory.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori
|||Transfer via DTS?
Script and execute via Query Analyzer or Enterprise Manager?
--=20
Keith
"ori" <anonymous@.discussions.microsoft.com> wrote in message =
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
>=20
> How can I copy many sp's without recreate them by code=20
> between two databases ?
>=20
> Thanks,
>=20
> Ori
|||Normal procedure is to generate a script of the required stored procedures,
and run that script on your target database.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
Ori
Showing posts with label sps. Show all posts
Showing posts with label sps. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
Copying SPs from one DB to another
I'm working on a system that will create a new DB that copies the SPs from
an older DB.
The purpose of this project is to create smaller, yearly DBs that will
require fewer table scans (since most searches are done on only the current
year). I would like to mimic the old DB year by year, copying all table
structure, a few tables worth of data (so far so good on those queries), but
I'm unsure on how to copy SPs from one DB to another.
Any thoughts?
Chad Edge
Seattle, WAYou can create the procedures in model, so when you create the new DB, they
get created automatically.
You can also try SQL Compare from www.red-gate.com, which will help you
generate scripts to synchronize two databases.
"Chad Edge" <xcasex@.hotmail.com> wrote in message
news:D2746C57-08D3-4A58-B0C0-6D7725D27C73@.microsoft.com...
> I'm working on a system that will create a new DB that copies the SPs from
> an older DB.
> The purpose of this project is to create smaller, yearly DBs that will
> require fewer table scans (since most searches are done on only the
> current year). I would like to mimic the old DB year by year, copying all
> table structure, a few tables worth of data (so far so good on those
> queries), but I'm unsure on how to copy SPs from one DB to another.
> Any thoughts?
> Chad Edge
> Seattle, WA
>|||Aaron,
Thanks for your help on that. I'll look up the link and work on the scripts.
I'm actually wondering now if we even need to copy the SP's. I guess it will
depend on what amount of weight we want to put on the interface-level.
What we're trying to do is make smaller seasonal db's without changing much
of the interfaces access (there's an internal edit tool, and an external
read-only view tool). If we keep our current, 'master' database in-place w/
the SP's, then we could adjust those SP's to choose which smaller, seasonal
db to get data from. If we do things that way, then we won't need to manage
all the SP's on multiple db's.
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHxOBWAlGHA.1936@.TK2MSFTNGP04.phx.gbl...
> You can create the procedures in model, so when you create the new DB,
> they get created automatically.
> You can also try SQL Compare from www.red-gate.com, which will help you
> generate scripts to synchronize two databases.
>
>
> "Chad Edge" <xcasex@.hotmail.com> wrote in message
> news:D2746C57-08D3-4A58-B0C0-6D7725D27C73@.microsoft.com...
>|||> What we're trying to do is make smaller seasonal db's without changing
> much of the interfaces access (there's an internal edit tool, and an
> external read-only view tool). If we keep our current, 'master' database
> in-place w/ the SP's, then we could adjust those SP's to choose which
> smaller, seasonal db to get data from. If we do things that way, then we
> won't need to manage all the SP's on multiple db's.
I recommend having the schema spread across the dbs, which will allow for
the app to simply change its connection string, instead of your main control
database needing to generate a dynamic SQL string EXEC('EXEC ' + @.dbname + '
.dbo.ProcedureName');|||Aaron,
My worry about doing that would be having to manage the SP's from season to
season.
So just to clarify, you're saying this is not recommended:
1. Master db, currently the only db, is called MAINEVENTS1. This db has all
SP's, and stores about 10 years worth of information (events)
2. We'd like to create new, seasonal db's called 'EVENTS_YYxYY' (eg:
EVENTS_06x07)
I'm considering this approach since it would shrink the amount of table
data that would need to be scanned, since most event results are polled for
the current 'season'
3. When an administrator logs in using (currently CFM) our admin interface
(on our intranet), they would have a new dropdown "Select Season:" which
would set their session for a specific season
4. When visitors to our Web site (again, using CFM), they would by default
get the current season set as their session, but would have text-links to
previous seasons.
5. Passing the session variable of which season is selected to the query
string would fire the correct SP from MAINEVENTS1, which would then retrieve
the database information from the correct DB.
In our first year, that would mean that there'd be two DB's :
MAINEVENTS1 with the last 10 years worth of data, and EVENTS_06x07.
6. This fall, we'd be adding new event types (concerts, speaking
engagements, etc) that would only be added to the newer DB's in the seasons
that they happened (no need to add Public Speaking to the MAINEVENTS1 since
it never happened).
7. The next round of updates would allow us to split MAINEVENTS1 into
EVENTS_01x02, 03_04, etc. further shrinking our query source
8. The MAINEVENTS1 would then become a very small DB, just a gateway of SPs
that query other dbs and format the data.
The reason I've been approaching the project this way is so the Online
interface only needs minor changes, the SP's only need minor changes, and we
don't have to manage SP's from season to season (say we change the way we'd
like data formatted; we'd have to track the changes down and verify control
over each database - bad form in my opinion).
The main reasons for approaching the project this way:
1. Shrink the databases since there's thousands of rows in our ten-year db.
2. Lock past events so they don't get overwritten (we've had issues where a
venue will change it's name; if we update our database the way it's
organized now, all events in the past are then related to the new venue
name - this makes it a nightmare to try and track in the past where an event
occurred because the venue in the past will no longer exist in our db).
I know #2 should be addressed, but we're not able to make such drastic
changes to the data at this time. Perhaps a round 3 we can restructure. For
now, we're getting about 2 million hits a month for our events and I'd like
to keep the impact small.
Hopefully that's not boring you to tears, I just needed to dump my brain
onto the screen to make sure we're speaking the same language.
Thanks again for your suggestions. I'll look at the structure and the SP's a
little more and see what work is ahead. I've got to make a decision by the
end of this w
so I can roll out the replication by EOM June.
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u3DsmsIlGHA.2304@.TK2MSFTNGP02.phx.gbl...
> I recommend having the schema spread across the dbs, which will allow for
> the app to simply change its connection string, instead of your main
> control database needing to generate a dynamic SQL string EXEC('EXEC ' +
> @.dbname + ' .dbo.ProcedureName');
>|||> My worry about doing that would be having to manage the SP's from season
> to season.
What is there to manage? If you store the SPs in model, then every new
database you create will have the SPs automatically.
When you have a changed SP to deploy, you run a loop to create a series of
CREATE or ALTER procedure statements, to generate a script you can run,
deploying to all databases at once.
We have two production systems that use this model, one has over 3000
identical databases spread across three machines, and the other has over 750
identical databases across three different environments (dev, qa,
production).
I use red-gate's SQL compare to synchronize model between environments.
And by naming the databases with a common naming scheme, I was able to write
a stored procedure in master that gets all the databases from sys.databases
matching that naming scheme.
I store the create and/or alter scripts in a file called change.sql, then
run a C# command line program that calls the stored procedure, and creates
an output file for each database.
Deploying changes really couldn't be simpler (except when there are
exceptions between environments). Everything configurable (such as network
share paths, SMTP servers, ftp passwords, etc.) are stored in properties
tables, nothing is hard-coded, and it makes it really easy to take the one
customer who explodes in activity from one server to another. Just backup
the database, restore on another server, and update a flag in the control
database that tells you what server the app can find that database.
A|||> I store the create and/or alter scripts in a file called change.sql, then
> run a C# command line program that calls the stored procedure, and creates
> an output file for each database.
Sorry, let me explain that sentence a little better.
Let's say I have a fix to a single stored procedure that needs to be
broadcast to all databases in an environment.
I store a single ALTER PROCEDURE script in change.sql.
My C# app grabs the stored procedure from master, steps through the 750+
databases, and for each one, reads change.sql, adds a USE [dbname] in front
of it, and appends it to a file called deploy.sql. Now I can run a single
.sql script to deploy my changes to every single database.
Sure, there are pros and cons, and yes that many databases sounds like a lot
to manage, and it is. But "a lot" does not necessarily equate to "hard"...
there are many ways to make the multi-db scenario much easier to manage.
One of these days I'm going to write an article on it...
A|||Aaron,
I'm liking where you're going (excluding the fact that it's all new ground
to me; I've stayed as far from MS coding as I have been able in my career).
One thing I might not have mentioned: We're going to get into situations
where we'll need a *controller* sp that takes variables and chooses which
database to retrieve the data from. For example:
Right now (with MAINEVENTS1) we have a table of Artists (musicians, let's
say). If you want to retrieve information about a specific Artist (say,
their instrument, the places they've played, tickets sold, etc) you run a
query using their ArtistID (just an example). That works in the MAINEVENTS1
db.
Now lets say we create EVENTS_06x07 that adds additional columns of data
that weren't available in the past (such as, height and weight, which
changes as the year goes on - just to make it dynamic). We won't have that
information for anything in the past, so we'd like to not make any changes
to MAINEVENTS1. However, we would now need to make the query (SP) understand
where to get it's data, and how much detail (columns) to return, based on
the year passed (pre-06 or post-06).
I picture (in my ideal candy-land imagination) a controller SP that would
take two variables (the ArtistID, and the Year), determine which database to
run the SP (or better yet, add the correct formatting to the SP itself), run
the SP and return the proper column data.
This is where replicating SPs gets tricky: one DB may have changes that a
previous years DB won't.
Also, leaving SP's in the Model would not automatically roll-out the changes
until a new DB was created, or when we create an entirely separate
application (such as something in C#).
My example is very elementary; there's trickier situations (statistics about
artists, about venues, about groups, etc) that will change from year to
year. That's where centrally managing SP's and having the MAINEVENTS1 manage
the connections and returns makes so much sense to me.
Again, thanks for your insight. You're making a lot of sense and I've
already started working with the Red-Gate software today (the dependency
tracker is flipping me out).
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23ESReGJlGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Sorry, let me explain that sentence a little better.
> Let's say I have a fix to a single stored procedure that needs to be
> broadcast to all databases in an environment.
> I store a single ALTER PROCEDURE script in change.sql.
> My C# app grabs the stored procedure from master, steps through the 750+
> databases, and for each one, reads change.sql, adds a USE [dbname] in
> front of it, and appends it to a file called deploy.sql. Now I can run a
> single .sql script to deploy my changes to every single database.
> Sure, there are pros and cons, and yes that many databases sounds like a
> lot to manage, and it is. But "a lot" does not necessarily equate to
> "hard"... there are many ways to make the multi-db scenario much easier to
> manage. One of these days I'm going to write an article on it...
> A
>|||> Now lets say we create EVENTS_06x07 that adds additional columns of data
> that weren't available in the past (such as, height and weight, which
> changes as the year goes on - just to make it dynamic).
So, in every db, add weight and height to the table(s) or view(s), adjust
procedure(s) that exist there, and only update those customers that have
that data (otherwise it is NULL).
Displaying these columns is something the app can decide based on the
settings in the control db, not based on the data itself.
Adding columns to a stored procedure should not change the behavior of the
app... but the app *can* use those new columns if it deems it should.
A
an older DB.
The purpose of this project is to create smaller, yearly DBs that will
require fewer table scans (since most searches are done on only the current
year). I would like to mimic the old DB year by year, copying all table
structure, a few tables worth of data (so far so good on those queries), but
I'm unsure on how to copy SPs from one DB to another.
Any thoughts?
Chad Edge
Seattle, WAYou can create the procedures in model, so when you create the new DB, they
get created automatically.
You can also try SQL Compare from www.red-gate.com, which will help you
generate scripts to synchronize two databases.
"Chad Edge" <xcasex@.hotmail.com> wrote in message
news:D2746C57-08D3-4A58-B0C0-6D7725D27C73@.microsoft.com...
> I'm working on a system that will create a new DB that copies the SPs from
> an older DB.
> The purpose of this project is to create smaller, yearly DBs that will
> require fewer table scans (since most searches are done on only the
> current year). I would like to mimic the old DB year by year, copying all
> table structure, a few tables worth of data (so far so good on those
> queries), but I'm unsure on how to copy SPs from one DB to another.
> Any thoughts?
> Chad Edge
> Seattle, WA
>|||Aaron,
Thanks for your help on that. I'll look up the link and work on the scripts.
I'm actually wondering now if we even need to copy the SP's. I guess it will
depend on what amount of weight we want to put on the interface-level.
What we're trying to do is make smaller seasonal db's without changing much
of the interfaces access (there's an internal edit tool, and an external
read-only view tool). If we keep our current, 'master' database in-place w/
the SP's, then we could adjust those SP's to choose which smaller, seasonal
db to get data from. If we do things that way, then we won't need to manage
all the SP's on multiple db's.
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHxOBWAlGHA.1936@.TK2MSFTNGP04.phx.gbl...
> You can create the procedures in model, so when you create the new DB,
> they get created automatically.
> You can also try SQL Compare from www.red-gate.com, which will help you
> generate scripts to synchronize two databases.
>
>
> "Chad Edge" <xcasex@.hotmail.com> wrote in message
> news:D2746C57-08D3-4A58-B0C0-6D7725D27C73@.microsoft.com...
>|||> What we're trying to do is make smaller seasonal db's without changing
> much of the interfaces access (there's an internal edit tool, and an
> external read-only view tool). If we keep our current, 'master' database
> in-place w/ the SP's, then we could adjust those SP's to choose which
> smaller, seasonal db to get data from. If we do things that way, then we
> won't need to manage all the SP's on multiple db's.
I recommend having the schema spread across the dbs, which will allow for
the app to simply change its connection string, instead of your main control
database needing to generate a dynamic SQL string EXEC('EXEC ' + @.dbname + '
.dbo.ProcedureName');|||Aaron,
My worry about doing that would be having to manage the SP's from season to
season.
So just to clarify, you're saying this is not recommended:
1. Master db, currently the only db, is called MAINEVENTS1. This db has all
SP's, and stores about 10 years worth of information (events)
2. We'd like to create new, seasonal db's called 'EVENTS_YYxYY' (eg:
EVENTS_06x07)
I'm considering this approach since it would shrink the amount of table
data that would need to be scanned, since most event results are polled for
the current 'season'
3. When an administrator logs in using (currently CFM) our admin interface
(on our intranet), they would have a new dropdown "Select Season:" which
would set their session for a specific season
4. When visitors to our Web site (again, using CFM), they would by default
get the current season set as their session, but would have text-links to
previous seasons.
5. Passing the session variable of which season is selected to the query
string would fire the correct SP from MAINEVENTS1, which would then retrieve
the database information from the correct DB.
In our first year, that would mean that there'd be two DB's :
MAINEVENTS1 with the last 10 years worth of data, and EVENTS_06x07.
6. This fall, we'd be adding new event types (concerts, speaking
engagements, etc) that would only be added to the newer DB's in the seasons
that they happened (no need to add Public Speaking to the MAINEVENTS1 since
it never happened).
7. The next round of updates would allow us to split MAINEVENTS1 into
EVENTS_01x02, 03_04, etc. further shrinking our query source
8. The MAINEVENTS1 would then become a very small DB, just a gateway of SPs
that query other dbs and format the data.
The reason I've been approaching the project this way is so the Online
interface only needs minor changes, the SP's only need minor changes, and we
don't have to manage SP's from season to season (say we change the way we'd
like data formatted; we'd have to track the changes down and verify control
over each database - bad form in my opinion).
The main reasons for approaching the project this way:
1. Shrink the databases since there's thousands of rows in our ten-year db.
2. Lock past events so they don't get overwritten (we've had issues where a
venue will change it's name; if we update our database the way it's
organized now, all events in the past are then related to the new venue
name - this makes it a nightmare to try and track in the past where an event
occurred because the venue in the past will no longer exist in our db).
I know #2 should be addressed, but we're not able to make such drastic
changes to the data at this time. Perhaps a round 3 we can restructure. For
now, we're getting about 2 million hits a month for our events and I'd like
to keep the impact small.
Hopefully that's not boring you to tears, I just needed to dump my brain
onto the screen to make sure we're speaking the same language.
Thanks again for your suggestions. I'll look at the structure and the SP's a
little more and see what work is ahead. I've got to make a decision by the
end of this w
so I can roll out the replication by EOM June.Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u3DsmsIlGHA.2304@.TK2MSFTNGP02.phx.gbl...
> I recommend having the schema spread across the dbs, which will allow for
> the app to simply change its connection string, instead of your main
> control database needing to generate a dynamic SQL string EXEC('EXEC ' +
> @.dbname + ' .dbo.ProcedureName');
>|||> My worry about doing that would be having to manage the SP's from season
> to season.
What is there to manage? If you store the SPs in model, then every new
database you create will have the SPs automatically.
When you have a changed SP to deploy, you run a loop to create a series of
CREATE or ALTER procedure statements, to generate a script you can run,
deploying to all databases at once.
We have two production systems that use this model, one has over 3000
identical databases spread across three machines, and the other has over 750
identical databases across three different environments (dev, qa,
production).
I use red-gate's SQL compare to synchronize model between environments.
And by naming the databases with a common naming scheme, I was able to write
a stored procedure in master that gets all the databases from sys.databases
matching that naming scheme.
I store the create and/or alter scripts in a file called change.sql, then
run a C# command line program that calls the stored procedure, and creates
an output file for each database.
Deploying changes really couldn't be simpler (except when there are
exceptions between environments). Everything configurable (such as network
share paths, SMTP servers, ftp passwords, etc.) are stored in properties
tables, nothing is hard-coded, and it makes it really easy to take the one
customer who explodes in activity from one server to another. Just backup
the database, restore on another server, and update a flag in the control
database that tells you what server the app can find that database.
A|||> I store the create and/or alter scripts in a file called change.sql, then
> run a C# command line program that calls the stored procedure, and creates
> an output file for each database.
Sorry, let me explain that sentence a little better.
Let's say I have a fix to a single stored procedure that needs to be
broadcast to all databases in an environment.
I store a single ALTER PROCEDURE script in change.sql.
My C# app grabs the stored procedure from master, steps through the 750+
databases, and for each one, reads change.sql, adds a USE [dbname] in front
of it, and appends it to a file called deploy.sql. Now I can run a single
.sql script to deploy my changes to every single database.
Sure, there are pros and cons, and yes that many databases sounds like a lot
to manage, and it is. But "a lot" does not necessarily equate to "hard"...
there are many ways to make the multi-db scenario much easier to manage.
One of these days I'm going to write an article on it...
A|||Aaron,
I'm liking where you're going (excluding the fact that it's all new ground
to me; I've stayed as far from MS coding as I have been able in my career).
One thing I might not have mentioned: We're going to get into situations
where we'll need a *controller* sp that takes variables and chooses which
database to retrieve the data from. For example:
Right now (with MAINEVENTS1) we have a table of Artists (musicians, let's
say). If you want to retrieve information about a specific Artist (say,
their instrument, the places they've played, tickets sold, etc) you run a
query using their ArtistID (just an example). That works in the MAINEVENTS1
db.
Now lets say we create EVENTS_06x07 that adds additional columns of data
that weren't available in the past (such as, height and weight, which
changes as the year goes on - just to make it dynamic). We won't have that
information for anything in the past, so we'd like to not make any changes
to MAINEVENTS1. However, we would now need to make the query (SP) understand
where to get it's data, and how much detail (columns) to return, based on
the year passed (pre-06 or post-06).
I picture (in my ideal candy-land imagination) a controller SP that would
take two variables (the ArtistID, and the Year), determine which database to
run the SP (or better yet, add the correct formatting to the SP itself), run
the SP and return the proper column data.
This is where replicating SPs gets tricky: one DB may have changes that a
previous years DB won't.
Also, leaving SP's in the Model would not automatically roll-out the changes
until a new DB was created, or when we create an entirely separate
application (such as something in C#).
My example is very elementary; there's trickier situations (statistics about
artists, about venues, about groups, etc) that will change from year to
year. That's where centrally managing SP's and having the MAINEVENTS1 manage
the connections and returns makes so much sense to me.
Again, thanks for your insight. You're making a lot of sense and I've
already started working with the Red-Gate software today (the dependency
tracker is flipping me out).
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23ESReGJlGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Sorry, let me explain that sentence a little better.
> Let's say I have a fix to a single stored procedure that needs to be
> broadcast to all databases in an environment.
> I store a single ALTER PROCEDURE script in change.sql.
> My C# app grabs the stored procedure from master, steps through the 750+
> databases, and for each one, reads change.sql, adds a USE [dbname] in
> front of it, and appends it to a file called deploy.sql. Now I can run a
> single .sql script to deploy my changes to every single database.
> Sure, there are pros and cons, and yes that many databases sounds like a
> lot to manage, and it is. But "a lot" does not necessarily equate to
> "hard"... there are many ways to make the multi-db scenario much easier to
> manage. One of these days I'm going to write an article on it...
> A
>|||> Now lets say we create EVENTS_06x07 that adds additional columns of data
> that weren't available in the past (such as, height and weight, which
> changes as the year goes on - just to make it dynamic).
So, in every db, add weight and height to the table(s) or view(s), adjust
procedure(s) that exist there, and only update those customers that have
that data (otherwise it is NULL).
Displaying these columns is something the app can decide based on the
settings in the control db, not based on the data itself.
Adding columns to a stored procedure should not change the behavior of the
app... but the app *can* use those new columns if it deems it should.
A
Copying Permissions on SPs and Views in Merge Rep
Hi,
I've come across another issue with my merge replication on SQL 2005 SP2.
Every time it recreates the SPs or views (due to snapshot, or changes) it
drops all of the custom persmissions to roles, or users.
I found a "Copy permission" on the tables, but can't find it for views or
procs. Is there a way to automatically have this happen on the SPs and views?
If not, can someone point me in the way of a viable workaround? (such as a
script/CLR to run based on triggers, schedules, whatever)
Ryan S
Sr SQL DBA
1Jn5:12
I script the permissions out for the views and procs and then use a post
snapshot command to apply them.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
> Hi,
> I've come across another issue with my merge replication on SQL 2005 SP2.
> Every time it recreates the SPs or views (due to snapshot, or changes) it
> drops all of the custom persmissions to roles, or users.
> I found a "Copy permission" on the tables, but can't find it for views or
> procs. Is there a way to automatically have this happen on the SPs and
> views?
> If not, can someone point me in the way of a viable workaround? (such as a
> script/CLR to run based on triggers, schedules, whatever)
> --
> Ryan S
> Sr SQL DBA
> 1Jn5:12
|||BTW, here is something I came up with yesterday to do this somewhat
dynamically for the entire server...
if object_id('Tempdb.dbo.##permissions') != 0
Drop table ##permissions
Create Table ##Permissions (Own varchar(270), Ob varchar(270), Grantee
varchar(270), Grantor varchar(270), ProtectType varchar(270), Act
varchar(270), Col varchar(270), DB Varchar(1000))
exec sp_msForeachdb '
use [?]
if ''?'' not in (''master'', ''msdb'', ''tempdb'', ''Model'')
Begin
Declare @.DB nvarchar(1000)
, @.Cmd nvarchar(4000)
set @.DB = ''?''
print ''?''
Insert Into ##Permissions (Own , Ob , Grantee , Grantor , ProtectType ,
Act , Col)
exec sp_helprotect
update ##Permissions set DB = @.DB
where DB is null
Delete from ##Permissions
-- remove permissions for system objects
where ob in (Select sysobjects.Name COLLATE SQL_Latin1_General_CP1_CI_AS
From sysobjects
where OBJECTPROPERTY (sysobjects.id, ''IsSystemTable'') = 1)
End
'
Select 'Use [' + DB + '] ;
if Object_id(''' + Ob + ''') is not null
' + rtrim(ltrim(ProtectType)) + ' ' + rtrim(ltrim(Act)) + ' on [' +
rtrim(ltrim(Ob)) + '] to [' + rtrim(ltrim(Grantee)) + ']'
from ##Permissions p
where ob != '.'
and grantee != 'public' --Do not copy public permissions
and left(grantee, 2) != 'MS' --Remove replication object permissions
drop table ##permissions
Ryan S
Sr SQL DBA
1Jn5:12
"Hilary Cotter" wrote:
> I script the permissions out for the views and procs and then use a post
> snapshot command to apply them.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
> news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
>
>
I've come across another issue with my merge replication on SQL 2005 SP2.
Every time it recreates the SPs or views (due to snapshot, or changes) it
drops all of the custom persmissions to roles, or users.
I found a "Copy permission" on the tables, but can't find it for views or
procs. Is there a way to automatically have this happen on the SPs and views?
If not, can someone point me in the way of a viable workaround? (such as a
script/CLR to run based on triggers, schedules, whatever)
Ryan S
Sr SQL DBA
1Jn5:12
I script the permissions out for the views and procs and then use a post
snapshot command to apply them.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
> Hi,
> I've come across another issue with my merge replication on SQL 2005 SP2.
> Every time it recreates the SPs or views (due to snapshot, or changes) it
> drops all of the custom persmissions to roles, or users.
> I found a "Copy permission" on the tables, but can't find it for views or
> procs. Is there a way to automatically have this happen on the SPs and
> views?
> If not, can someone point me in the way of a viable workaround? (such as a
> script/CLR to run based on triggers, schedules, whatever)
> --
> Ryan S
> Sr SQL DBA
> 1Jn5:12
|||BTW, here is something I came up with yesterday to do this somewhat
dynamically for the entire server...
if object_id('Tempdb.dbo.##permissions') != 0
Drop table ##permissions
Create Table ##Permissions (Own varchar(270), Ob varchar(270), Grantee
varchar(270), Grantor varchar(270), ProtectType varchar(270), Act
varchar(270), Col varchar(270), DB Varchar(1000))
exec sp_msForeachdb '
use [?]
if ''?'' not in (''master'', ''msdb'', ''tempdb'', ''Model'')
Begin
Declare @.DB nvarchar(1000)
, @.Cmd nvarchar(4000)
set @.DB = ''?''
print ''?''
Insert Into ##Permissions (Own , Ob , Grantee , Grantor , ProtectType ,
Act , Col)
exec sp_helprotect
update ##Permissions set DB = @.DB
where DB is null
Delete from ##Permissions
-- remove permissions for system objects
where ob in (Select sysobjects.Name COLLATE SQL_Latin1_General_CP1_CI_AS
From sysobjects
where OBJECTPROPERTY (sysobjects.id, ''IsSystemTable'') = 1)
End
'
Select 'Use [' + DB + '] ;
if Object_id(''' + Ob + ''') is not null
' + rtrim(ltrim(ProtectType)) + ' ' + rtrim(ltrim(Act)) + ' on [' +
rtrim(ltrim(Ob)) + '] to [' + rtrim(ltrim(Grantee)) + ']'
from ##Permissions p
where ob != '.'
and grantee != 'public' --Do not copy public permissions
and left(grantee, 2) != 'MS' --Remove replication object permissions
drop table ##permissions
Ryan S
Sr SQL DBA
1Jn5:12
"Hilary Cotter" wrote:
> I script the permissions out for the views and procs and then use a post
> snapshot command to apply them.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
> news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
>
>
Copying multiple SPs in SS 2005
I am totally stumped. In SQL Server 2000, I would fire up my EM and right-click on multiple SPs and then Generate Scripts. I then would start QA and run the script on a different DB. This was a very convenient feature to copy SPs over from one DB to other as well as from one machine to other. I can't seem to do the same in SQL Management Studio. Is it possible? If yes, how?
Now this may not be the right place to ask but since VS and SS go hand-in-hand, I thought I would ask.
Hth,
Scott|||According to Microsoft's SQL Team:
>> Multi-select script didn't get in till recently – its in post-IDW15 builds and will be in the next public CTP of SQL tools.
Sunday, February 19, 2012
copy tables + SPs from one db to another
I have two databases and want to copy tables (with table data) and stored procedures from one database to another.
both databases have the same name (so management studio is not letting me have both up)...
what should I do?
thanks.
What do you mean both databases have the same name? Do both servers have the same name? If both databases have the same name if you try to attach one into the server with a database of the same name you can specify a new name while attaching.
You can generate script to move the schema and stored procedures. The data is where the answers above will make a difference.
||| Yes they're on the same server. basically i have my db... a friend was working on a db with the same name on his machine and so i am now trying to merge his stuff into mine. (i have his .mdf/.ldf files)
hope that makes sense.
i will only want to take certain tables(with data)/ SP's from his db...not everything..
how can i do it?
thanks
|||You can use DTS to replace tables on your DB with the one's from your friend's DB. If you have the mdf/ldf files, rename them and attach them on your server. then you can use DTS to transfer objects/data between the 2 db's. IF you need to MERGE, then its a whole different scenario.
|||what is DTS?
will i be able to transfer the table data too?
|||DTS = Data Transformation Services. Read up books on line for more info.
Subscribe to:
Posts (Atom)