Network integration for fresh install of 2005

I am working as a consultant on a project to upgrade an Access 2000 db to SQL Server. My client has bought off on everything, and they're ordering a new dedicated box this week. They're getting a rackmount machine running Windows Server and SQL Server 2005.

My expectation was that their IT people would take care of administrative & security issues such as setting the machine up, installing & starting SQL Server and configuring the proper accounts. Suprisingly though, they're going to mount the machine in the rack, turn it on and let me do all of this.

I'm new at SQL Server, but I'm comfortable working with database objects (tables, views, etc...) I'm not so comfortable with the networking or administrative side of things. It took me a couple of hours to be able to connect to the DB on my desktop machine over my home network. You can see why I'm intimidated at the prospect of getting things integrated & running on a pretty large corporate network.

Maybe I'm overblowing this, but this just gives me the willies.

Are my fears justified? Am I going to be ok if I just follow the standard procedures for allowing TCP/IP connections to the server? Am I going to have to deal with firewalls? (I think I know the answer - "it depends on their network.." - that's what I'm scared of.. I don't know anything about their network, and I don't know enough about networking in general to figure things out..)

Are there any other best practices that I should follow for a fresh install?So much depends on their network configuration that only their network people can give you a complete and correct answer to your questions. With that said, the general setup isn't difficult or complicated, the only problems I've ever run into arise when coping with installation specific problems.

Specific points to consider. If you don't know that you or your project need it, don't enable it. This especially applies to network protocols (only enable TCP/IP unless there is compelling reason for another protocol). If you don't need SQL Authentication, only select Windows Authentication.

Their network administrators are giving you a "blank check" to set things up as you'd like. This is idiocy on their part, but you should take advantage of it! Get the box running, in a minimal configuration. Set the box up as a "lone wolf" machine, then only after you are done getting things set up to suit yourself tell them you need a domain administrator login to join the box to their domain (this should put them into an outright panic), then stand back and watch the fun as they backpeddle furiously! At that point, you already have a tested, working configuration... Their only choices are to either a) give you the network equivalent of god-like powers, or b) pick up the ball that you've handed them and very quickly figure out how to run with it.

Note: This is evil, but they have brought it on themselves. The network administrators have already done one of the most stupid things that they are capable of doing, in order to force you to do all of their legwork for them. You are simply doing what they've asked, and will be presenting them with the most awful choice possible, but one that they ought to know is coming because there isn't any alternative.

-PatP|||Sorry I haven't been back for a few days..

I really don't want to do anything to create any friction with them. So far they've been fairly cooperative in allowing an outside consultant (me) to do development work for a department that's under their jurisdiction. They could easily send one email to upper management that would a) make my primary client's life miserable and b) completely cut me out of any work for them in the future.

After talking with one of their network techs yesterday, the server is up & running. It's 2005, running on a Windows Server 2003 Virtual Machine. She's going to be there, so hopefully she can take care of any network issues that arise. From what she said yesterday, they don't have any internal firewalls, so that should simplify things.

I'm going over there in a few hours to establish my login account, create a test database, and verify connectivity between it and an Access installation on an end user's machine.

Any final words of wisdom?

nested loop function

I want to know how to create a recursive loop/function in SQL, I can't seem to figure out how to do it.

The database table I am working with is simply the following:

SeedID, ThisParentSeedID

1, 0

2, 1

3, 1

4, 2

5, 4

6, 5

7, 6

8, 7

9, 7

10, 7

11, 10

12, 0

13, 0

14, 0

The example table above shows that SeedID 1 = the parent level of the data. SeedID 2 and 3 are children of SeedID 1, 4 is child of 2, 5 is child of 4... 12 13 and 14 are also parent levels (they are not children of anything).

I want to know how to create a SQL script that is "object oriented" in that I will not have to create as many levels of nested scripts as there are nested "children" in the data.

What I am wanting to figure out is, with a single script, "which sub-children are assigned to [@.SeedID]"? So if this script was called, and @.SeedID = 1, it would return (2,3,4,5,6,7,8,9,10,11). If @.SeedID = 12, it would return null. If @.SeedID = 7, it would return (8,9,10,11)

I have tried to keep my question and data as simple as possible for the sake of getting some feedback or help. If you want me to clarify or explain better, please ask me to!

CTE (Common Table expression) is best suited for your needs. Here you go

Declare @.ID as INT
SET @.ID = 7

;WITH myCTE AS
(
SELECT SeedID, ParentID FROM Seeds where SeedID = @.ID
UNION ALL
SELECT Seeds.SeedID, Seeds.ParentID From Seeds INNER JOIN myCte
ON Seeds.ParentID = myCte.SeedID
)
SELECT SeedID FROM myCTE where SeedID <> @.ID

|||

--Create the functionCREATE FUNCTION dbo.udf_GetChildren (@.parentIdint )RETURNSVarchar(100)ASBEGINDECLARE @.Childvarchar(100)SELECT @.Child =coalesce(@.child,'') + (casewhen @.childisnot nullthen','else''end ) +convert(varchar,SeedId)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdSELECT @.Child = @.Child + dbo.udf_GetChildren(seedid)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdReturnCoalesce(@.child,'')END--Call the functionSELECT dbo.udf_GetChildren(1)

needing help to connect to server

I am new working with JDBC. I am having problems with
having the drivers reconized. I have gotten the 3 .jar
files. Here is what I do any detailed info would help.
I have taken the code off the site
http://support.microsoft.com/default.aspx?scid=kb;en-
us;313100#4
and added the user and pass that I need.
I compile it javac xxx.java
I have tried to specify where the drivers are I have moved
then in the dir where the xxx.java files are and I still
get this error
java.lang.ClassNotFoundException:
com.microsoft.jdbc.sqlserver.SQLServerDriver
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged
(Native Method)
at java.net.URLClassLoader.findClass(Unknown
Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass
(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown
Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at Connect.getConnection(Connect.java:29)
at Connect.displayDbProperties(Connect.java:47)
at Connect.main(Connect.java:83)
Error Trace in getConnection() :
com.microsoft.jdbc.sqlserver.SQLServerDriver
Any help that you can give me is great. Take in mind that
I am new at this and will need a little more detail
responce.
Thanks Tom
"Tom Teer" wrote:

> I am new working with JDBC. I am having problems with
> having the drivers reconized. I have gotten the 3 .jar
> files. Here is what I do any detailed info would help.
> I have taken the code off the site
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;313100#4
> and added the user and pass that I need.
> I compile it javac xxx.java
> I have tried to specify where the drivers are I have moved
> then in the dir where the xxx.java files are and I still
> get this error
> java.lang.ClassNotFoundException:
> com.microsoft.jdbc.sqlserver.SQLServerDriver
> at java.net.URLClassLoader$1.run(Unknown Source)
> at java.security.AccessController.doPrivileged
> (Native Method)
> at java.net.URLClassLoader.findClass(Unknown
> Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at sun.misc.Launcher$AppClassLoader.loadClass
> (Unknown Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at java.lang.ClassLoader.loadClassInternal(Unknown
> Source)
> at java.lang.Class.forName0(Native Method)
> at java.lang.Class.forName(Unknown Source)
> at Connect.getConnection(Connect.java:29)
> at Connect.displayDbProperties(Connect.java:47)
> at Connect.main(Connect.java:83)
> Error Trace in getConnection() :
> com.microsoft.jdbc.sqlserver.SQLServerDriver
> Any help that you can give me is great. Take in mind that
> I am new at this and will need a little more detail
> responce.
> Thanks Tom
>
Tom,
It appears that you are still unable to load the drivers for SQL Server.
If the driver's JAR files are inside the same directory as your application,
make sure that you have . (dot) as one of the arguments in your system's
classpath.
Alternatively, add the JAR files to your system's CLASSPATH. That would make
them available to any Java application system-wide.
Be sure to add them to the classpath in the form
%location%/msutils.jar;%location/mssqlserver.jar - that is with the full file
name.
Hope this helps,
Yuval
|||"Tom Teer" wrote:

> I am new working with JDBC. I am having problems with
> having the drivers reconized. I have gotten the 3 .jar
> files. Here is what I do any detailed info would help.
> I have taken the code off the site
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;313100#4
> and added the user and pass that I need.
> I compile it javac xxx.java
> I have tried to specify where the drivers are I have moved
> then in the dir where the xxx.java files are and I still
> get this error
> java.lang.ClassNotFoundException:
> com.microsoft.jdbc.sqlserver.SQLServerDriver
> at java.net.URLClassLoader$1.run(Unknown Source)
> at java.security.AccessController.doPrivileged
> (Native Method)
> at java.net.URLClassLoader.findClass(Unknown
> Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at sun.misc.Launcher$AppClassLoader.loadClass
> (Unknown Source)
> at java.lang.ClassLoader.loadClass(Unknown Source)
> at java.lang.ClassLoader.loadClassInternal(Unknown
> Source)
> at java.lang.Class.forName0(Native Method)
> at java.lang.Class.forName(Unknown Source)
> at Connect.getConnection(Connect.java:29)
> at Connect.displayDbProperties(Connect.java:47)
> at Connect.main(Connect.java:83)
> Error Trace in getConnection() :
> com.microsoft.jdbc.sqlserver.SQLServerDriver
> Any help that you can give me is great. Take in mind that
> I am new at this and will need a little more detail
> responce.
> Thanks Tom
>
|||Tom,
You could also try running your application as:
java -cp .;.\msbase.jar;.\msutil.jar;.\mssqlserver.jar xxx
Sue Purkis
DataDirect Technologies
yuvalz <yuvalz@.discussions.microsoft.com> wrote in message news:<19B25152-8E15-4893-B56A-6D3A4FE7D85D@.microsoft.com>...[vbcol=seagreen]
> "Tom Teer" wrote:

Need Your Thoughts

I currently have an Access application that I have been working with for a
couple of years now, it is growing in size rapidly. I am thinking that I
need to be planning to take it to the next level.
Is SQL Server an appropriate thought?
Never have dealt with it before, but I have been reading a little about it.
I kinda like the thought of how it deals with records rather than Jet.
Is it appropriate to think about a web based interface to share the database
or is that thought total wrong?
I think this will be a new learning curve for me but thats alright, I thrive
on challenges.
If someone could take a few moments and en-lighten me with their thoughts it
would greatly be appriciated.
Thanks
David WI would pick Microsoft SQL Server over MSAccess any day of the w. I
can't think of any situation where I would prefer to use MSAccess. SQL
Server is a proper multi-user RDBMS (relational database management
system). It was designed like that from the ground up. Using a web
server as a front end to a SQL database is very common. There would be
literally millions of applications that do this. Companies like Barnes
& Noble (http://www.barnesandnoble.com/), Home Shopping Network
(http://www.hsn.com/) & Radio Shack (http://www.radioshack.com/) all do
this exact thing.
*mike hodgson*
http://sqlnerd.blogspot.com
David W wrote:

>I currently have an Access application that I have been working with for a
>couple of years now, it is growing in size rapidly. I am thinking that I
>need to be planning to take it to the next level.
>Is SQL Server an appropriate thought?
>Never have dealt with it before, but I have been reading a little about it.
>I kinda like the thought of how it deals with records rather than Jet.
>Is it appropriate to think about a web based interface to share the databas
e
>or is that thought total wrong?
>I think this will be a new learning curve for me but thats alright, I thriv
e
>on challenges.
>If someone could take a few moments and en-lighten me with their thoughts i
t
>would greatly be appriciated.
>Thanks
>David W
>
>|||>> I currently have an Access application that I have been working with for
a couple of years now, it is growing in size rapidly. <<
LOL!!n Welcome to developer and DBA hell! ACCESS sucks and does not
scale.
Good and probably true if the app is any good.
Yes, or another SQL product, depending on other issues.
It will be a lot of UN-learning.

Need Urgent Help...............

Hi i am manish,working wth sql server2000
Need Urgent Help............
Currently,we are preparing our one software,
I want to enfore some security check ,so that user of that software not able
to watch what kind of data that software contains
security i need for that
1) application level: no other external software are allowed
2)default Windows NT groups are to be disable
3) user should able to execute any kind select ,etc query
Regards
ManishHave a look at Application Roles in BOL. By activating an application role
from your application and by only granting permissions to that application
role, you should be able to get the behaviour you require
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Manish Mokadam" <manish.mokadam@.honeywell.com> wrote in message
news:471E447A-D6B2-4361-8814-AF59362FA34D@.microsoft.com...
> Hi i am manish,working wth sql server2000
> Need Urgent Help............
> Currently,we are preparing our one software,
> I want to enfore some security check ,so that user of that software not
able to watch what kind of data that software contains
> security i need for that
> 1) application level: no other external software are allowed
> 2)default Windows NT groups are to be disable
> 3) user should able to execute any kind select ,etc query
>
> Regards
> Manish

Need urgent help !

Our backups have stopped working. The only change made was the server it
self was moved from one Domain to another. Our backup scripts do work
across our different domains. I've Googled and searched MS web site but have
not found anything that helps.
Here is the error that we are getting:
"Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
A cursor with the name 'ms_db' does not exist."
Here is the stored proc:
CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
null
AS
-- if the @.backup parameter is specified as 'backup', -
-- it will dump the dbs regardless of the @.sched_day
declare @.srvr_nm varchar(35),
@.cmptr_nm varchar(30),
@.rpt_loc varchar(50),
@.backup_loc varchar(50),
@.hist_rtntn int ,
@.hist_loc varchar(50),
@.sched_day int,
@.dmp_cd varchar(10),
@.drv_ltr char(1),
@.doit varchar(255),
@.dbname varchar(30),
@.backup_old varchar(50),
@.dw int,
@.dd int
--NEW ADDITION--
select @.dd = datepart(dd, getdate() )
select @.dw = datepart(dw, getdate() )
select @.srvr_nm = @.@.servername
exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
@.cmptr_nm output,
@.rpt_loc output,
@.backup_loc output,
@.hist_rtntn output,
@.hist_loc output,
@.sched_day output,
@.dmp_cd output
--@.@.sched_day represents the backup day of the week, 1 thru 7
-- 1 being sunday and 7 being saturday, 0 is dump that day
select @.drv_ltr = substring(@.rpt_loc,1,1)
select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'echo cd\..\'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
if @.backup = 'backup' select @.sched_day = 0
select @.hist_loc = @.hist_loc+@.srvr_nm+'\'
select @.backup_loc = @.backup_loc+@.srvr_nm+'\'
select @.backup_old = @.backup_loc
if datepart(dd,getdate() ) between 1 and 7
and @.srvr_nm not in ('SXBOX23','WABOX3')
and @.hist_rtntn = 1 and @.sched_day <> 0
select @.backup_loc = @.hist_loc
if datepart(dd,getdate() ) = 1
and @.srvr_nm not in ('SXBOX23','WABOX23')
and @.hist_rtntn = 1 and @.sched_day = 0
select @.backup_loc = @.hist_loc
select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'del /q '+@.rpt_loc+'*.log'
exec master..xp_cmdshell @.doit,no_output
-- checks for the dbnm parameter and backups just the dbnm.
if @.dbnm is not null and @.backup is not null
begin
select @.dbname = @.dbnm
if @.srvr_nm in ("SXBOX23", "WABOX23")
and @.dw in (2,5) -- Mon or Thu
begin -- We are going to dump
select @.sched_day = 0
if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
select @.backup_loc = @.hist_loc
else
select @.backup_loc = @.backup_old
end
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -BkUpDB ' + @.backup_loc +
' -BkUpMedia DISK' +
' -BkUpOnlyIfClean' +
' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
exec master..xp_sqlmaint @.doit
end
else
if @.dbnm is not null and @.backup is null
begin
select @.dbname = @.dbnm
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
exec master..xp_sqlmaint @.doit
end
else
if @.dbnm is null
begin
if @.dmp_cd = 'USERDB'
begin
declare ms_db cursor for
select name from master..sysdatabases
where dbid <> 2 order by dbid
for read only
end
if @.dmp_cd = 'SYSTEMDB'
begin
declare ms_db cursor for
select name from master..sysdatabases
where dbid < 5 and dbid <> 2 order by dbid
for read only
end
select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'del /q '+@.rpt_loc+'*.log'
exec master..xp_cmdshell @.doit,no_output
open ms_db
fetch next from ms_db into @.dbname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
if @.srvr_nm in ("SXBOX23", "WABOX23")
and @.dw in (2,5) -- Mon or Thu we are going to dump
begin
select @.sched_day = 0
if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
select @.backup_loc = @.hist_loc
else
select @.backup_loc = @.backup_old
end
if @.dw = @.sched_day or
@.sched_day = 0 /* daily backups */
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -BkUpDB ' + @.backup_loc +
' -BkUpMedia DISK' +
' -BkUpOnlyIfClean' +
' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
else
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
exec master..xp_sqlmaint @.doit
END
fetch next from ms_db into @.dbname
END
close ms_db
deallocate ms_db
end
if @.dbnm is not null or @.backup is null
begin
select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
exec master..xp_cmdshell @.doit,no_output
end
select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
exec master..xp_cmdshell @.doit,no_output
select @.dbnm = @.dbname
select @.doit = 'echo off'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'echo --Dbcc and Dumps Report--
>'+@.rpt_loc+'maintchk.rpt'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
'+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
\\'+@.cmptr_nm+'\'+@.drv_ltr+'$\'+substring(@.rpt_loc ,4,len(@.rpt_loc)-3)+'dump_rpt.log
^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
select @.doit='echo if errorlevel 1 findstr "Destination"
'+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
select @.doit = @.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
if @.dw = @.sched_day or @.sched_day = 0
begin
select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
"MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
end
GO
Forgot to say that this is SQL2000 server with SP3a running on a Windows
2000 SP4 server.
Joe
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..\'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+'\'
> select @.backup_loc = @.backup_loc+@.srvr_nm+'\'
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \\'+@.cmptr_nm+'\'+@.drv_ltr+'$\'+substring(@.rpt_loc ,4,len(@.rpt_loc)-3)+'dump_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>
|||I notice that if @.dmp_cd is neither USERCD nor SYSTEMCD, you fall through
with ms_db not declared which would give you this error. It might be worth
PRINT'ing @.dmp_cd to see what it is set to. Good coding practice would be
to abort the script and declare an error if it's not one of the values you
expect. No idea if this is your problem but if it's not now, it may be next
time.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..\'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+'\'
> select @.backup_loc = @.backup_loc+@.srvr_nm+'\'
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \\'+@.cmptr_nm+'\'+@.drv_ltr+'$\'+substring(@.rpt_loc ,4,len(@.rpt_loc)-3)+'dump_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>
|||> I've Googled and searched MS web site but have not found anything that
> helps.
That's not surprising since this is not a SQL Server system procedure.

> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
Walking through your code, the reason for the immediate error is that
neither cursor was declared because @.dmp_cd is other than 'USERDB' or
'SYSTEMDB'. It looks like @.dmp_cd is assigned via the following snippet:
select @.srvr_nm = @.@.servername
exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
@.cmptr_nm output,
@.rpt_loc output,
@.backup_loc output,
@.hist_rtntn output,
@.hist_loc output,
@.sched_day output,
@.dmp_cd output
I suggest you check to ensure @.@.SERVERNAME returns the expect name and, if
so, that sp_qg_get_backup_info is returning a valid @.dmp_cd value.
BTW, you should never name user stored procedures with a 'sp_' prefix.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..\'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+'\'
> select @.backup_loc = @.backup_loc+@.srvr_nm+'\'
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \\'+@.cmptr_nm+'\'+@.drv_ltr+'$\'+substring(@.rpt_loc ,4,len(@.rpt_loc)-3)+'dump_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>
|||I can't believe what I am reading.
You wrote scripts, not to automate backups, but to run SQL maintenance plans
with programmatically determined parameters?! Most likely there is a
permissions issue somewhere that broke this specific script. However, the
underlying problem is executing the maintenance utility directly. I would,
and have, written scripts that invoke the specific backup and file
maintenance tasks directly for each database. I even wrote a "master"
script that creates the jobs for a new database. I find that far more
maintainable than a strange, kludgey script that invokes an even stranger
and kludgier (albiet a Microsoft approved kludge) maintenance utility.
I like the SQL Maintenance plans for simple systems and beginning DBAs.
Anyone who can write a script this complex should be able to do the same
using T-SQL backup commands. And the SQL Agent has a fairly nifty and
reliable scheduler that does have a supported t-sql interface. This is a
major example of re-inventing the wheel, only to come up with a lopsided
octagon.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..\'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+'\'
> select @.backup_loc = @.backup_loc+@.srvr_nm+'\'
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \\'+@.cmptr_nm+'\'+@.drv_ltr+'$\'+substring(@.rpt_loc ,4,len(@.rpt_loc)-3)+'dump_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>
|||I have to ask, "Why!" but not because the script is failing but rather why
did you write it in the first place?!
First off, sp_qg_dump_db causes a scan of the master database. (amatuerish).
Second, to do a backup in a script takes about 1 line for a full backup of
either the log or data or 1 line for an incremental.
Try thinking "small code is good code" and it will usuall work nowhere you
put it.
d.
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
have
> not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..\'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+'\'
> select @.backup_loc = @.backup_loc+@.srvr_nm+'\'
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to
dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
>
\\'+@.cmptr_nm+'\'+@.drv_ltr+'$\'+substring(@.rpt_loc ,4,len(@.rpt_loc)-3)+'dump_
rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>

Need urgent help !

Our backups have stopped working. The only change made was the server it
self was moved from one Domain to another. Our backup scripts do work
across our different domains. I've Googled and searched MS web site but have
not found anything that helps.
Here is the error that we are getting:
"Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
A cursor with the name 'ms_db' does not exist."
Here is the stored proc:
CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
null
AS
-- if the @.backup parameter is specified as 'backup', -
-- it will dump the dbs regardless of the @.sched_day
declare @.srvr_nm varchar(35),
@.cmptr_nm varchar(30),
@.rpt_loc varchar(50),
@.backup_loc varchar(50),
@.hist_rtntn int ,
@.hist_loc varchar(50),
@.sched_day int,
@.dmp_cd varchar(10),
@.drv_ltr char(1),
@.doit varchar(255),
@.dbname varchar(30),
@.backup_old varchar(50),
@.dw int,
@.dd int
--NEW ADDITION--
select @.dd = datepart(dd, getdate() )
select @.dw = datepart(dw, getdate() )
select @.srvr_nm = @.@.servername
exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
@.cmptr_nm output,
@.rpt_loc output,
@.backup_loc output,
@.hist_rtntn output,
@.hist_loc output,
@.sched_day output,
@.dmp_cd output
--@.@.sched_day represents the backup day of the week, 1 thru 7
-- 1 being sunday and 7 being saturday, 0 is dump that day
select @.drv_ltr = substring(@.rpt_loc,1,1)
select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
exec master..xp_cmdshell @.doit,no_output
if @.backup = 'backup' select @.sched_day = 0
select @.hist_loc = @.hist_loc+@.srvr_nm+''
select @.backup_loc = @.backup_loc+@.srvr_nm+''
select @.backup_old = @.backup_loc
if datepart(dd,getdate() ) between 1 and 7
and @.srvr_nm not in ('SXBOX23','WABOX3')
and @.hist_rtntn = 1 and @.sched_day <> 0
select @.backup_loc = @.hist_loc
if datepart(dd,getdate() ) = 1
and @.srvr_nm not in ('SXBOX23','WABOX23')
and @.hist_rtntn = 1 and @.sched_day = 0
select @.backup_loc = @.hist_loc
select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'del /q '+@.rpt_loc+'*.log'
exec master..xp_cmdshell @.doit,no_output
-- checks for the dbnm parameter and backups just the dbnm.
if @.dbnm is not null and @.backup is not null
begin
select @.dbname = @.dbnm
if @.srvr_nm in ("SXBOX23", "WABOX23")
and @.dw in (2,5) -- Mon or Thu
begin -- We are going to dump
select @.sched_day = 0
if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
select @.backup_loc = @.hist_loc
else
select @.backup_loc = @.backup_old
end
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -BkUpDB ' + @.backup_loc +
' -BkUpMedia DISK' +
' -BkUpOnlyIfClean' +
' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
exec master..xp_sqlmaint @.doit
end
else
if @.dbnm is not null and @.backup is null
begin
select @.dbname = @.dbnm
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
exec master..xp_sqlmaint @.doit
end
else
if @.dbnm is null
begin
if @.dmp_cd = 'USERDB'
begin
declare ms_db cursor for
select name from master..sysdatabases
where dbid <> 2 order by dbid
for read only
end
if @.dmp_cd = 'SYSTEMDB'
begin
declare ms_db cursor for
select name from master..sysdatabases
where dbid < 5 and dbid <> 2 order by dbid
for read only
end
select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'del /q '+@.rpt_loc+'*.log'
exec master..xp_cmdshell @.doit,no_output
open ms_db
fetch next from ms_db into @.dbname
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
if @.srvr_nm in ("SXBOX23", "WABOX23")
and @.dw in (2,5) -- Mon or Thu we are going to dump
begin
select @.sched_day = 0
if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
select @.backup_loc = @.hist_loc
else
select @.backup_loc = @.backup_old
end
if @.dw = @.sched_day or
@.sched_day = 0 /* daily backups */
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -BkUpDB ' + @.backup_loc +
' -BkUpMedia DISK' +
' -BkUpOnlyIfClean' +
' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
else
select @.doit =
' -D "'+ @.dbname + '"' +
' -CkAlNoIdx' +
' -CkDBNoIdx' +
' -CkCat' +
' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
exec master..xp_sqlmaint @.doit
END
fetch next from ms_db into @.dbname
END
close ms_db
deallocate ms_db
end
if @.dbnm is not null or @.backup is null
begin
select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
exec master..xp_cmdshell @.doit,no_output
end
select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
exec master..xp_cmdshell @.doit,no_output
select @.dbnm = @.dbname
select @.doit = 'echo off'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'echo --Dbcc and Dumps Report--[vbcol=seagreen]
>'+@.rpt_loc+'maintchk.rpt'
exec master..xp_cmdshell @.doit,no_output
select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
'+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
\'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dump_
rpt.log
^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
select @.doit='echo if errorlevel 1 findstr "Destination"
'+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt[vbcol=seagreen]
exec master..xp_cmdshell @.doit,no_output
select @.doit = @.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
if @.dw = @.sched_day or @.sched_day = 0
begin
select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
"MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
exec master..xp_cmdshell @.doit,no_output
end
GOForgot to say that this is SQL2000 server with SP3a running on a Windows
2000 SP4 server.
Joe
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dum
p_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>|||I notice that if @.dmp_cd is neither USERCD nor SYSTEMCD, you fall through
with ms_db not declared which would give you this error. It might be worth
PRINT'ing @.dmp_cd to see what it is set to. Good coding practice would be
to abort the script and declare an error if it's not one of the values you
expect. No idea if this is your problem but if it's not now, it may be next
time.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dum
p_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>|||> I've Googled and searched MS web site but have not found anything that
> helps.
That's not surprising since this is not a SQL Server system procedure.

> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
Walking through your code, the reason for the immediate error is that
neither cursor was declared because @.dmp_cd is other than 'USERDB' or
'SYSTEMDB'. It looks like @.dmp_cd is assigned via the following snippet:
select @.srvr_nm = @.@.servername
exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
@.cmptr_nm output,
@.rpt_loc output,
@.backup_loc output,
@.hist_rtntn output,
@.hist_loc output,
@.sched_day output,
@.dmp_cd output
I suggest you check to ensure @.@.SERVERNAME returns the expect name and, if
so, that sp_qg_get_backup_info is returning a valid @.dmp_cd value.
BTW, you should never name user stored procedures with a 'sp_' prefix.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dum
p_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>|||I can't believe what I am reading.
You wrote scripts, not to automate backups, but to run SQL maintenance plans
with programmatically determined parameters?! Most likely there is a
permissions issue somewhere that broke this specific script. However, the
underlying problem is executing the maintenance utility directly. I would,
and have, written scripts that invoke the specific backup and file
maintenance tasks directly for each database. I even wrote a "master"
script that creates the jobs for a new database. I find that far more
maintainable than a strange, kludgey script that invokes an even stranger
and kludgier (albiet a Microsoft approved kludge) maintenance utility.
I like the SQL Maintenance plans for simple systems and beginning DBAs.
Anyone who can write a script this complex should be able to do the same
using T-SQL backup commands. And the SQL Agent has a fairly nifty and
reliable scheduler that does have a supported t-sql interface. This is a
major example of re-inventing the wheel, only to come up with a lopsided
octagon.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
> have not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
> 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
> \'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dum
p_rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>|||I have to ask, "Why!" but not because the script is failing but rather why
did you write it in the first place?!
First off, sp_qg_dump_db causes a scan of the master database. (amatuerish).
Second, to do a backup in a script takes about 1 line for a full backup of
either the log or data or 1 line for an incremental.
Try thinking "small code is good code" and it will usuall work nowhere you
put it.
d.
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:dpm8gf$1cne$1@.sxnews1.qg.com...
> Our backups have stopped working. The only change made was the server it
> self was moved from one Domain to another. Our backup scripts do work
> across our different domains. I've Googled and searched MS web site but
have
> not found anything that helps.
>
> Here is the error that we are getting:
> "Server: Msg 16916, Level 16, State 1, Procedure sp_qg_dump_db, Line 143
> A cursor with the name 'ms_db' does not exist."
> Here is the stored proc:
> CREATE PROCEDURE sp_qg_dump_db @.backup char(6)= null, @.dbnm varchar(30) =
> null
> AS
> -- if the @.backup parameter is specified as 'backup', -
> -- it will dump the dbs regardless of the @.sched_day
> declare @.srvr_nm varchar(35),
> @.cmptr_nm varchar(30),
> @.rpt_loc varchar(50),
> @.backup_loc varchar(50),
> @.hist_rtntn int ,
> @.hist_loc varchar(50),
> @.sched_day int,
> @.dmp_cd varchar(10),
> @.drv_ltr char(1),
> @.doit varchar(255),
> @.dbname varchar(30),
> @.backup_old varchar(50),
> @.dw int,
> @.dd int
> --NEW ADDITION--
> select @.dd = datepart(dd, getdate() )
> select @.dw = datepart(dw, getdate() )
> select @.srvr_nm = @.@.servername
>
> exec SERVERA.REP0001..sp_qg_get_backup_info @.srvr_nm,
> @.cmptr_nm output,
> @.rpt_loc output,
> @.backup_loc output,
> @.hist_rtntn output,
> @.hist_loc output,
> @.sched_day output,
> @.dmp_cd output
> --@.@.sched_day represents the backup day of the week, 1 thru 7
> -- 1 being sunday and 7 being saturday, 0 is dump that day
> select @.drv_ltr = substring(@.rpt_loc,1,1)
> select @.doit = 'echo '+@.drv_ltr+': >'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo cd\..'+substring(@.rpt_loc,4,len(@.rpt_loc)-3)+'
> exec master..xp_cmdshell @.doit,no_output
> if @.backup = 'backup' select @.sched_day = 0
> select @.hist_loc = @.hist_loc+@.srvr_nm+''
> select @.backup_loc = @.backup_loc+@.srvr_nm+''
> select @.backup_old = @.backup_loc
>
> if datepart(dd,getdate() ) between 1 and 7
> and @.srvr_nm not in ('SXBOX23','WABOX3')
> and @.hist_rtntn = 1 and @.sched_day <> 0
> select @.backup_loc = @.hist_loc
> if datepart(dd,getdate() ) = 1
> and @.srvr_nm not in ('SXBOX23','WABOX23')
> and @.hist_rtntn = 1 and @.sched_day = 0
> select @.backup_loc = @.hist_loc
>
> select @.doit = 'del /q '+@.rpt_loc+@.dbnm+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
>
> -- checks for the dbnm parameter and backups just the dbnm.
> if @.dbnm is not null and @.backup is not null
> begin
> select @.dbname = @.dbnm
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu
> begin -- We are going to dump
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw = 5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is not null and @.backup is null
> begin
> select @.dbname = @.dbnm
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> exec master..xp_sqlmaint @.doit
> end
> else
> if @.dbnm is null
> begin
> if @.dmp_cd = 'USERDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid <> 2 order by dbid
> for read only
> end
> if @.dmp_cd = 'SYSTEMDB'
> begin
> declare ms_db cursor for
> select name from master..sysdatabases
> where dbid < 5 and dbid <> 2 order by dbid
> for read only
> end
> select @.doit = 'del /q '+@.rpt_loc+'*.rpt'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'del /q '+@.rpt_loc+'*.log'
> exec master..xp_cmdshell @.doit,no_output
> open ms_db
> fetch next from ms_db into @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> if @.srvr_nm in ("SXBOX23", "WABOX23")
> and @.dw in (2,5) -- Mon or Thu we are going to
dump
> begin
> select @.sched_day = 0
> if @.dd between 15 and 18 and not ( @.dd = 18 and @.dw =
5)
> and (@.dbname LIKE "PROD%" OR @.dbname = "DB1")
> select @.backup_loc = @.hist_loc
> else
> select @.backup_loc = @.backup_old
> end
> if @.dw = @.sched_day or
> @.sched_day = 0 /* daily backups */
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -BkUpDB ' + @.backup_loc +
> ' -BkUpMedia DISK' +
> ' -BkUpOnlyIfClean' +
> ' -Rpt "' +@.rpt_loc+@.dbname+ '.rpt"'
> else
> select @.doit =
> ' -D "'+ @.dbname + '"' +
> ' -CkAlNoIdx' +
> ' -CkDBNoIdx' +
> ' -CkCat' +
> ' -Rpt "' + @.rpt_loc + @.dbname+'.rpt"'
> exec master..xp_sqlmaint @.doit
> END
> fetch next from ms_db into @.dbname
> END
> close ms_db
> deallocate ms_db
> end
>
> if @.dbnm is not null or @.backup is null
> begin
> select @.doit = 'copy '+@.rpt_loc +@.dbnm+'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> end
> select @.doit = 'copy '+@.rpt_loc +'*.rpt '+@.rpt_loc+ 'dump_rpt.log'
> exec master..xp_cmdshell @.doit,no_output
> select @.dbnm = @.dbname
> select @.doit = 'echo off'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo --Dbcc and Dumps Report--
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = 'echo findstr /C:"SQLMAINT.EXE Process Exit Code: 1"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+ 'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 echo Unexpected Error: Check
>
\'+@.cmptr_nm+''+@.drv_ltr+'$'+substrin
g(@.rpt_loc,4,len(@.rpt_loc)-3)+'dump_
rpt.log
> ^>^>'+@.rpt_loc+'maintchk.rpt >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if not errorlevel 1 blat ' +@.rpt_loc+'maintchk.rpt -t
> dba@.ourdomain.com -s "MSSql Dumps ' + @.@.servername +' " -noh2
> exec master..xp_cmdshell @.doit,no_output
> select @.doit='echo if errorlevel 1 findstr "Destination"
> '+@.rpt_loc+'dump_rpt.log ^>^>'+@.rpt_loc+'maintchk.rpt
> exec master..xp_cmdshell @.doit,no_output
> select @.doit = @.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> if @.dw = @.sched_day or @.sched_day = 0
> begin
> select @.doit = 'blat ' +@.rpt_loc+'maintchk.rpt -t dba@.ourdomain.com -s
> "MSSql Dumps ' + @.@.servername +' " -noh2 >>'+@.rpt_loc+'srch.bat'
> exec master..xp_cmdshell @.doit,no_output
> end
> GO
>
>