Dump tran filename problem

[forums.sybase.com]

People

I want to create a dump file whose filename contains the current date and
time, so I've done this (in 12.5.4):

declare @fdate varchar(20)
declare @execstring varchar(255)
set @fdate = convert (varchar,getdate(), 112) + convert(varchar,
datepart(hh,getdate())) + convert(varchar,datepart(mi,getdate())) +
convert(varchar,datepart(ss,getdate()))
set @execstring = '"c:\sybdumps\jftestdb_trandump_' + @fdate + '.dmp"'
dump tran jftestdb to @execstring

The dump fails with this:
Backup Server: 4.141.2.40: [7] The 'CreateFile' call failed for device
'C:\WINDOWS\system32\"c:\sybdumps\jftestdb_trandump_2008051916488.dmp"' with
error number 123 (The filename, directory name, or volume label syntax is
incorrect). Refer to your operating system documentation for further
details.
Server Message: Number 8009, Severity 16

As you can see, the string ''C:\WINDOWS\system32\' is being inserted before
my string and making the backup fail. I tried 'exec @variable' with
@variable containing the full execution command, but that fails because dump
tran isn't permitted to be exec'd from a variable.

Can anybody explain why rubbish is inserted in my string or advise what
other method is available for creating a transaction dump file containing
the date and time in its name?

Many thanks in advance

Jacques

[Sherlock, Kevin]

Don't include the double quotes in your string. They are not necessary when
using a variable in the "dump" command. Only literals need the double
quotes and @execstring is not a literal, but rather a variable...

try:
...

Post by forums.sybase.com
People
I want to create a dump file whose filename contains the current date and
datepart(hh,getdate())) + convert(varchar,datepart(mi,getdate())) +
convert(varchar,datepart(ss,getdate()))
Backup Server: 4.141.2.40: [7] The 'CreateFile' call failed for device
'C:\WINDOWS\system32\"c:\sybdumps\jftestdb_trandump_2008051916488.dmp"'
with error number 123 (The filename, directory name, or volume label
syntax is incorrect). Refer to your operating system documentation for
further details.
Server Message: Number 8009, Severity 16
As you can see, the string ''C:\WINDOWS\system32\' is being inserted
@variable containing the full execution command, but that fails because
dump tran isn't permitted to be exec'd from a variable.
Can anybody explain why rubbish is inserted in my string or advise what
other method is available for creating a transaction dump file containing
the date and time in its name?
Many thanks in advance
Jacques

[Sherlock, Kevin]

small correction: literals can be quoted with single OR double quotes
(double quotes as long as you don't have "set quoted identifier on" set).

Post by Sherlock, Kevin
Don't include the double quotes in your string. They are not necessary
when using a variable in the "dump" command. Only literals need the
...

Post by forums.sybase.com
People
I want to create a dump file whose filename contains the current date and
datepart(hh,getdate())) + convert(varchar,datepart(mi,getdate())) +
convert(varchar,datepart(ss,getdate()))
Backup Server: 4.141.2.40: [7] The 'CreateFile' call failed for device
'C:\WINDOWS\system32\"c:\sybdumps\jftestdb_trandump_2008051916488.dmp"'
with error number 123 (The filename, directory name, or volume label
syntax is incorrect). Refer to your operating system documentation for
further details.
Server Message: Number 8009, Severity 16
As you can see, the string ''C:\WINDOWS\system32\' is being inserted
because dump tran isn't permitted to be exec'd from a variable.
Can anybody explain why rubbish is inserted in my string or advise what
other method is available for creating a transaction dump file containing
the date and time in its name?
Many thanks in advance
Jacques

[forums.sybase.com]

What an eejut. I overlooked those naughty double quotes, even in the error
message, so out they came and now it works.

Many thanks Kevin.

Jacques

[Mark A. Parsons]

Did you start your dataserver and/or backupserver as a service?

When a dataserver is started as a service it appears that said service is started up in the c:\windows\system32 folder.

I've got ASE running on XP. I startup my dataservers and backupserver as services.

When I issue the following command:

dump database model to 'hereiam.dbdump'
go

I receive the following output:

Creating new disk file C:\WINDOWS\system32\hereiam.dbdump

This would tend to imply that the dataserver and/or backupserver were started as a service and the home/bootup directory
for said services was c:\windows\system32.

Since I did not provide a complete dump path the service's home/bootup directory was prepended to my dump file name.

So, up to this point I'd say that the dump command is not recognizing your full path definition and is instead treating
it as a simple file (sans any directory info) to which it prepends the service's home/bootup directory. ("Duh, Mark!" ?)

-------------------------

So, why isn't Sybase recognizing your full path designation?

Kevin's already alluded to the issue ... you've included a set of double quotes in the @execstring variable.

This gives you a command that looks like:

dump tran jftestdb to '"c:\sybdumps\jftestdb_trandump_2008051916488.dmp"'

When this command is parsed to determine the destination for the dump file the single quotes are stripped off, but the
double quotes are still there, thus leading Sybase (or the OS?) to believe that the file's name is the quoted string
"c:\sybdumps\jftestdb_trandump_20080511916488.dmp". In this scenario you could just as eaisly replace the back slashes
with some other character and Sybase/OS would still treat the whole string as a pathless file name.

You can verify this by issuing the (above) 'dump tran' command. You should get the same dump file created as in your
original post.

Now remove one of the sets of quotes (single or double) and you should see the dump file created where you intended (ie,
in the c:\sybdumps directory).

Once you've got that figured out it's just a question of removing one of the sets of quotes from the 'set @execstring =
' command.

Post by forums.sybase.com
People
I want to create a dump file whose filename contains the current date and
datepart(hh,getdate())) + convert(varchar,datepart(mi,getdate())) +
convert(varchar,datepart(ss,getdate()))
Backup Server: 4.141.2.40: [7] The 'CreateFile' call failed for device
'C:\WINDOWS\system32\"c:\sybdumps\jftestdb_trandump_2008051916488.dmp"' with
error number 123 (The filename, directory name, or volume label syntax is
incorrect). Refer to your operating system documentation for further
details.
Server Message: Number 8009, Severity 16
As you can see, the string ''C:\WINDOWS\system32\' is being inserted before
@variable containing the full execution command, but that fails because dump
tran isn't permitted to be exec'd from a variable.
Can anybody explain why rubbish is inserted in my string or advise what
other method is available for creating a transaction dump file containing
the date and time in its name?
Many thanks in advance
Jacques

[forums.sybase.com]

Many thanks for your your detailed response Mark!

I failed to notice the double quotes in the error message, and of course, as
you spotted, they were the culprits. Removed them, and it now works.

Jacques