Hi,
I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?
Thanks,
MariaHi Maria,
try if putting the statement
SET XACT_ABORT OFF
in the TSQL-stream of the step solves your problem.
Showing posts with label instances. Show all posts
Showing posts with label instances. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Automatically migratring metadata between instances of SQL Server
I want to move meta data between two copies of the same database running
on different machines. I do most of my work on one machine, but
sometimes have to work on a slow laptop. If I need to migrate metadata
(updated or new Stored Procedures and tables) from one machine to my
laptop, or back, is there a way I can automate the process? Is there
some way to say: hey sync this stored procedure definition in one
instance of SQL server with that in another instance?
Another way of asking the same question, but in more specific terms, is
whether there is a good way of backing up the metadata for a Stored
procedure or Table to a text file that can be placed in a source control
tool such as StarTeam or CVS? I know I can click on this and select that
and save the metadata, and then load that in on the other machine by
clicking on that and selecting this. But I want to get away from all
that laborious clicking and just run a script that automates the
process. For instance, how can I script the process of right clicking on
a table and then selecting "All Tasks | Generate SQL Script".
Thanks for your help or any hints you can offer.
- Charlie
Hi,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"mark baekdal" wrote:
> check out www.dbghost.com for comparison, updating
> straight from your source code.
> database running
> machine, but
> migrate metadata
> machine to my
> process? Is there
> definition in one
> specific terms, is
> for a Stored
> a source control
> and select that
> other machine by
> away from all
> automates the
> right clicking on
> Script".
>
|||Hi,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"mark baekdal" wrote:
> check out www.dbghost.com for comparison, updating
> straight from your source code.
> database running
> machine, but
> migrate metadata
> machine to my
> process? Is there
> definition in one
> specific terms, is
> for a Stored
> a source control
> and select that
> other machine by
> away from all
> automates the
> right clicking on
> Script".
>
sql
on different machines. I do most of my work on one machine, but
sometimes have to work on a slow laptop. If I need to migrate metadata
(updated or new Stored Procedures and tables) from one machine to my
laptop, or back, is there a way I can automate the process? Is there
some way to say: hey sync this stored procedure definition in one
instance of SQL server with that in another instance?
Another way of asking the same question, but in more specific terms, is
whether there is a good way of backing up the metadata for a Stored
procedure or Table to a text file that can be placed in a source control
tool such as StarTeam or CVS? I know I can click on this and select that
and save the metadata, and then load that in on the other machine by
clicking on that and selecting this. But I want to get away from all
that laborious clicking and just run a script that automates the
process. For instance, how can I script the process of right clicking on
a table and then selecting "All Tasks | Generate SQL Script".
Thanks for your help or any hints you can offer.
- Charlie
Hi,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"mark baekdal" wrote:
> check out www.dbghost.com for comparison, updating
> straight from your source code.
> database running
> machine, but
> migrate metadata
> machine to my
> process? Is there
> definition in one
> specific terms, is
> for a Stored
> a source control
> and select that
> other machine by
> away from all
> automates the
> right clicking on
> Script".
>
|||Hi,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il
"mark baekdal" wrote:
> check out www.dbghost.com for comparison, updating
> straight from your source code.
> database running
> machine, but
> migrate metadata
> machine to my
> process? Is there
> definition in one
> specific terms, is
> for a Stored
> a source control
> and select that
> other machine by
> away from all
> automates the
> right clicking on
> Script".
>
sql
Wednesday, March 7, 2012
Automate to get version number for a list of SQL Instances
We have a bunch of SQL Server instances in our domain. Some of them
are in SQL 2000 and some are in 2005. I need to be able to find out
what service pack, edition and version each of the instances are
running on, what will be the best way to go about it. I would prefer
not to do it manually, i have a list of instances all saved in a
table. I would like to loop through this table connect to each
instance and get the result I want and save it in the same table.
My challenge is none of the servers are linked and I am not able to
get openrowset to work with trusted connection, I am able to get what
I need using SQL user but that requires me to add SQL login to each of
the servers before I can go about my script. I am sure other DBA's
have gone through this, can someone please suggest or give ideas.
Any help in this reagrd will be greatly appreciated.
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
If you can access the file system on the target servers then you can obtain
the version number via VBScript:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Wscript.Echo objFSO.GetFileVersion("C:\Program Files\Microsoft SQL
Server\MSSQL$SS2K\Binn\sqlservr.exe")
--
David Portas|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
The below is not reliable script. You will have to go throu each server and
run SERVERPROPRTY to get what you want.
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||I had to monitor several hundred instances before and I did monitoring each
day by creating linked servers through TSQL right before I executed my
monitoring scripts. After the scripts finished I would drop the link server
so I would not have hundreds of linked servers setting around.
Also try using OSQL to make a connection. You can put build the OSQL
connection string from your table for each server and then paste all the
connection strings into a batch job which points to a script file with your
monitoring code.
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||The ideal approach is not to do this in T-SQL, but in a little client app
written in a real programming language. Typically, you would want to collect
a lot more info than just versions and build numbers. Having a little client
app gives you ultimate flexibility in whatever inventory information you may
fancy to collect.
Linchi
"shub" wrote:
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
are in SQL 2000 and some are in 2005. I need to be able to find out
what service pack, edition and version each of the instances are
running on, what will be the best way to go about it. I would prefer
not to do it manually, i have a list of instances all saved in a
table. I would like to loop through this table connect to each
instance and get the result I want and save it in the same table.
My challenge is none of the servers are linked and I am not able to
get openrowset to work with trusted connection, I am able to get what
I need using SQL user but that requires me to add SQL login to each of
the servers before I can go about my script. I am sure other DBA's
have gone through this, can someone please suggest or give ideas.
Any help in this reagrd will be greatly appreciated.
Thanks"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
If you can access the file system on the target servers then you can obtain
the version number via VBScript:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Wscript.Echo objFSO.GetFileVersion("C:\Program Files\Microsoft SQL
Server\MSSQL$SS2K\Binn\sqlservr.exe")
--
David Portas|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/whats-version-of-sql-server.html
The below is not reliable script. You will have to go throu each server and
run SERVERPROPRTY to get what you want.
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||I had to monitor several hundred instances before and I did monitoring each
day by creating linked servers through TSQL right before I executed my
monitoring scripts. After the scripts finished I would drop the link server
so I would not have hundreds of linked servers setting around.
Also try using OSQL to make a connection. You can put build the OSQL
connection string from your table for each server and then paste all the
connection strings into a batch job which points to a script file with your
monitoring code.
"shub" <shubtech@.gmail.com> wrote in message
news:1194190723.073489.207550@.z9g2000hsf.googlegroups.com...
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>|||The ideal approach is not to do this in T-SQL, but in a little client app
written in a real programming language. Typically, you would want to collect
a lot more info than just versions and build numbers. Having a little client
app gives you ultimate flexibility in whatever inventory information you may
fancy to collect.
Linchi
"shub" wrote:
> We have a bunch of SQL Server instances in our domain. Some of them
> are in SQL 2000 and some are in 2005. I need to be able to find out
> what service pack, edition and version each of the instances are
> running on, what will be the best way to go about it. I would prefer
> not to do it manually, i have a list of instances all saved in a
> table. I would like to loop through this table connect to each
> instance and get the result I want and save it in the same table.
> My challenge is none of the servers are linked and I am not able to
> get openrowset to work with trusted connection, I am able to get what
> I need using SQL user but that requires me to add SQL login to each of
> the servers before I can go about my script. I am sure other DBA's
> have gone through this, can someone please suggest or give ideas.
> Any help in this reagrd will be greatly appreciated.
> Thanks
>
Automate reinitialization
Currently, we have many MSDE instances that subscribe to our main server.
Morning, we have to re-init whenever there's a failed subscription.
Is there a way or script to automate the reinitialization? Also, how can
we indicate it to use the current snapshot or generate a new snapshot?
Thanks in advance!
Duy
Schedule the Reinitialize subscriptions having data validation failures job.
I would create a schedule that runs twice a day.
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
"Duy Nguyen" <tieuta@.hotmail.com> wrote in message
news:OYZgrv3tHHA.1728@.TK2MSFTNGP06.phx.gbl...
> Currently, we have many MSDE instances that subscribe to our main server.
> Morning, we have to re-init whenever there's a failed subscription.
> Is there a way or script to automate the reinitialization? Also, how can
> we indicate it to use the current snapshot or generate a new snapshot?
> Thanks in advance!
> Duy
>
Morning, we have to re-init whenever there's a failed subscription.
Is there a way or script to automate the reinitialization? Also, how can
we indicate it to use the current snapshot or generate a new snapshot?
Thanks in advance!
Duy
Schedule the Reinitialize subscriptions having data validation failures job.
I would create a schedule that runs twice a day.
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
"Duy Nguyen" <tieuta@.hotmail.com> wrote in message
news:OYZgrv3tHHA.1728@.TK2MSFTNGP06.phx.gbl...
> Currently, we have many MSDE instances that subscribe to our main server.
> Morning, we have to re-init whenever there's a failed subscription.
> Is there a way or script to automate the reinitialization? Also, how can
> we indicate it to use the current snapshot or generate a new snapshot?
> Thanks in advance!
> Duy
>
Automate Database Project
We use seperate database instances for development, qa, ua, and production with multiple databases in each environment. So, I'm looking for a way to automate the database changes to these different environments.
After creating a database project with change scripts and database references, I can't seem to find a way to automate these changes out to a server/environment.
We're currently using SQL 2000 + 2005, Visual Studio 2003 + 2005, and Visual Source Safe 6 (researching Team Foundation System).
Any ideas/thoughts?
If I am reading your message correctly you already have a change script that you want to apply to your target systems and do this in an automated fashion? One possible solution is to deploy the script using SQLCMD. If I did not read this correctly, and you're looking for the capability to generate a change script, then you would need to shop for a 3rf party tool as we do not have this capability.
Automate Database Project
We use seperate database instances for development, qa, ua, and production with multiple databases in each environment. So, I'm looking for a way to automate the database changes to these different environments.
After creating a database project with change scripts and database references, I can't seem to find a way to automate these changes out to a server/environment.
We're currently using SQL 2000 + 2005, Visual Studio 2003 + 2005, and Visual Source Safe 6 (researching Team Foundation System).
Any ideas/thoughts?
If I am reading your message correctly you already have a change script that you want to apply to your target systems and do this in an automated fashion? One possible solution is to deploy the script using SQLCMD. If I did not read this correctly, and you're looking for the capability to generate a change script, then you would need to shop for a 3rf party tool as we do not have this capability.
Subscribe to:
Posts (Atom)