I am trying to render a report with a barcode in it to pdf. When I open the
pdf, the line with the barcode is not there. I know the font is on all the
necessary machines. Does anyone have any ideas?i´ve experienced the same when my cell was a tiny bit to small.
(yes i have checked the can grow option)
just making it a bit higher solved that for me.
it still doesnt render perfectly, since it moves my barcode approx 1 cm down
when i render a PDF, but hope it helps you anyway
// andrew
"Chris Arnold" wrote:
> I am trying to render a report with a barcode in it to pdf. When I open the
> pdf, the line with the barcode is not there. I know the font is on all the
> necessary machines. Does anyone have any ideas?
Showing posts with label file. Show all posts
Showing posts with label file. Show all posts
Sunday, March 25, 2012
Tuesday, March 20, 2012
Balancing IO. File Groups vs Raid
I will be doing some performance testing on financial application next
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.
month. Without going into a lot of details, I suspect I will have a
potential bottleneck when writing to the log file.
My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
rpm drives, with about 1GB of memory on the array for caching.
This is a benchmark environment, so I am not concerned about loosing
data. I am looking for a little guidance on using raid (0 or 10)
and/or file groups to spread IO to db objects (log file(s), data,
indexes, tempdb, etc). I have read about and played with file groups
enough to know that SQL server does some level of load balancing
across file, but am unclear it is in parallel or serialized.
Common wisdom seems to be to separate data, non-clustered index, logs,
and tempdb onto separate files, but I am unclear on how to make best
use of the high-speed disk array. I'd greatly appreciate opinions on
which would perform better; one file on a stripe set of N drives (raid
0 or 10), N files in a file group placed on N (non-striped) drives, or
a combination of the two? Is the answer the same for both log and data
(or index) files?
Thanks,
-Bernie"Bernie" <velivis@.ix.netcom.com> wrote in message
news:e707e6a.0312111018.475c199a@.posting.google.co m...
> I will be doing some performance testing on financial application next
> month. Without going into a lot of details, I suspect I will have a
> potential bottleneck when writing to the log file.
> My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
> DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
> rpm drives, with about 1GB of memory on the array for caching.
> This is a benchmark environment, so I am not concerned about loosing
> data. I am looking for a little guidance on using raid (0 or 10)
> and/or file groups to spread IO to db objects (log file(s), data,
> indexes, tempdb, etc). I have read about and played with file groups
> enough to know that SQL server does some level of load balancing
> across file, but am unclear it is in parallel or serialized.
> Common wisdom seems to be to separate data, non-clustered index, logs,
> and tempdb onto separate files, but I am unclear on how to make best
> use of the high-speed disk array. I'd greatly appreciate opinions on
> which would perform better; one file on a stripe set of N drives (raid
> 0 or 10), N files in a file group placed on N (non-striped) drives, or
> a combination of the two? Is the answer the same for both log and data
> (or index) files?
Can't answer all of those (partly I'm not fully awake. :)
But you absolutely want your logs on RAID 1 or RAID 10, NOT RAID 0.
Remember, RAID 0 has no redundancy. If you lose your transaction log, you
lose data. Or more specifically, you lose your ability to recover up to the
most recent point in time.
Ideally, I'd probably dedicate some LUN to the Data (this lun can comprise
of a RAID 5, RAID 10, etc, depending on needs, disk space, etc. (I prefer
if I have the space to use RAID 10), have another for TEMPDB (though I've
always cheated on this and put it on my DATA disk, and then a separate RAID
10 for logs.
The best reason I can see for separate filegroups is to separate out indices
to their own drive (we do this) or to break out very large tables so that
you can use some more advanced backup schemes.
That's my 2 cents at 1:00 AM.
> Thanks,
> -Bernie|||The main things are that the transaction log shouldn't be on the same
disk (or array) as the data, and neither should the non-clustered
indexes.
Here's a setup I like for a six-drive box:
3 disks in a RAID-5 array for the data.
2 disks using RAID-1 (mirroring) for the transaction log.
1 disk for the non-clustered indexes.
My theory is if the singleton goes bad, we just reconstruct the
indexes, no big deal.
With more than six drives you can get fancier, e.g. have two data
files on separate RAID-5 arrays such that tables that are typically
joined in queries are not on the same array.
On 11 Dec 2003 10:18:37 -0800, velivis@.ix.netcom.com (Bernie) wrote:
>I will be doing some performance testing on financial application next
>month. Without going into a lot of details, I suspect I will have a
>potential bottleneck when writing to the log file.
>My hardware setup is a quad 2.8 Xeon Dell server direct attached to a
>DELL/EMC CX200 (Fibre channel array with 10 X 30something GB, 15,000
>rpm drives, with about 1GB of memory on the array for caching.
>This is a benchmark environment, so I am not concerned about loosing
>data. I am looking for a little guidance on using raid (0 or 10)
>and/or file groups to spread IO to db objects (log file(s), data,
>indexes, tempdb, etc). I have read about and played with file groups
>enough to know that SQL server does some level of load balancing
>across file, but am unclear it is in parallel or serialized.
>Common wisdom seems to be to separate data, non-clustered index, logs,
>and tempdb onto separate files, but I am unclear on how to make best
>use of the high-speed disk array. I'd greatly appreciate opinions on
>which would perform better; one file on a stripe set of N drives (raid
>0 or 10), N files in a file group placed on N (non-striped) drives, or
>a combination of the two? Is the answer the same for both log and data
>(or index) files?
>Thanks,
>-Bernie|||"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:nqnntvk42k1b5fjq2ui7f8fa0huttaps42@.4ax.com...
> The main things are that the transaction log shouldn't be on the same
> disk (or array) as the data, and neither should the non-clustered
> indexes.
> Here's a setup I like for a six-drive box:
> 3 disks in a RAID-5 array for the data.
> 2 disks using RAID-1 (mirroring) for the transaction log.
> 1 disk for the non-clustered indexes.
Personally I would not do this (single disk for non-clustered indices).
Mostly because in my environment (others of course will vary) loss of the
indices will effectively make the DB useless from a DB point of view.
> My theory is if the singleton goes bad, we just reconstruct the
> indexes, no big deal.
> With more than six drives you can get fancier, e.g. have two data
> files on separate RAID-5 arrays such that tables that are typically
> joined in queries are not on the same array.|||If it happens and inconveniences the users, we're that much closer to
a SAN. :)
On Mon, 15 Dec 2003 03:57:12 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:
>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:nqnntvk42k1b5fjq2ui7f8fa0huttaps42@.4ax.com...
>> The main things are that the transaction log shouldn't be on the same
>> disk (or array) as the data, and neither should the non-clustered
>> indexes.
>>
>> Here's a setup I like for a six-drive box:
>>
>> 3 disks in a RAID-5 array for the data.
>> 2 disks using RAID-1 (mirroring) for the transaction log.
>> 1 disk for the non-clustered indexes.
>Personally I would not do this (single disk for non-clustered indices).
>Mostly because in my environment (others of course will vary) loss of the
>indices will effectively make the DB useless from a DB point of view.
>>
>> My theory is if the singleton goes bad, we just reconstruct the
>> indexes, no big deal.
>>
>> With more than six drives you can get fancier, e.g. have two data
>> files on separate RAID-5 arrays such that tables that are typically
>> joined in queries are not on the same array.|||"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
news:bcgttvkt2u15bnfmlr9m6d1a1u6f2256k0@.4ax.com...
> If it happens and inconveniences the users, we're that much closer to
> a SAN. :)
Now see, thre I like how you think. :-)|||:)
Actually your previous post made me realize that I should organize
something the tech guys can use to make the database quickly reusable
in the event the singleton fails and I'm not around. So thank you!
:)
I think maybe I will create an empty filegroup "EMERGENCY" on the RAID
5 array, and make a script to drop all the nonclustered indexes from
the filegroup that's on the singleton and then recreate them in
EMERGENCY. Whaddya think?
On Tue, 16 Dec 2003 15:01:43 GMT, "Greg D. Moore \(Strider\)"
<mooregr@.greenms.com> wrote:
>"Ellen K." <72322.enno.esspeeayem.1016@.compuserve.com> wrote in message
>news:bcgttvkt2u15bnfmlr9m6d1a1u6f2256k0@.4ax.com...
>> If it happens and inconveniences the users, we're that much closer to
>> a SAN. :)
>>
>
>Now see, thre I like how you think. :-)
Balancing data between files
We are storing all our SQL 2000 databases on SAN LUNs, and one of our databases currently uses a single 40GB file which is approaching capacity. If we add further files using different LUNs, the data will start being added to these new files. My questions are these: if we were to add a number of new LUNs to this database, is there a way to redistribute the existing data so it is balanced across all files in order to gain the most benefit from having multiple files, rather than just dispersing the additional fragments across the new files?
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.I would opt for more spindles and heads to move the data quicker. Best way I have found is to create new filegroups, then drop primary index on old filegroup and then recreate primary index on desired filegroup. This accomplishes two things.
First, it forces the move of the table data to the new filegroup since the leaf node of primary index **IS** the data page. Secondly, you get not only an index reorg, you get a contiguous page allocation based on the primary index.
Prior to this move, you might want to look at your fill factors to see if they need to be adjusted, because this would be a great time to do that too!|||Also, moving nonclustered indexes in the way described by tomh53 is a great idea as well - not only do you equally distribute your data, but also separate table and its indexes onto different devices, which is generally a good thing to do.sql
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.I would opt for more spindles and heads to move the data quicker. Best way I have found is to create new filegroups, then drop primary index on old filegroup and then recreate primary index on desired filegroup. This accomplishes two things.
First, it forces the move of the table data to the new filegroup since the leaf node of primary index **IS** the data page. Secondly, you get not only an index reorg, you get a contiguous page allocation based on the primary index.
Prior to this move, you might want to look at your fill factors to see if they need to be adjusted, because this would be a great time to do that too!|||Also, moving nonclustered indexes in the way described by tomh53 is a great idea as well - not only do you equally distribute your data, but also separate table and its indexes onto different devices, which is generally a good thing to do.sql
Monday, March 19, 2012
bad page ID
How i can repair database ms-sql when attach file i have:
error 823 I/O error (bad page ID) detected during read at offset 0x itd...
Robert LisHi
error 823 I/O error (bad page ID) detected during read at offset 0x itd...
Robert LisHi
If you are trying to attach a DB, and during the attach process you get this
error, you can't do much but to revert to a backup as you can't attach a DB
that SQL Server finds to be suspect.
On the other hand, if the DB is online, then you can run DBCC CHECKDB.
--
----------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robert-SoftMAR" <robert@.softmar.com.pl> wrote in message
news:d6qu0d$ude$1@.news.lublin.pl...
> How i can repair database ms-sql when attach file i have:
> error 823 I/O error (bad page ID) detected during read at offset 0x itd...
> Robert Lis|||Robert-SoftMAR (robert@.softmar.com.pl) writes:
> How i can repair database ms-sql when attach file i have:
> error 823 I/O error (bad page ID) detected during read at offset 0x itd...
Books Online says this about eror 823:
Check the accessibility and condition of the device in question.
Run hardware diagnostics and correct problems, if possible.
Restore damaged files from the latest database backup. Restoring from a
database backup should always be considered the primary means of fixing
a damaged database.
If you don't have a backup or if the errors detected are very isolated,
the repair functionality of DBCC CHECKDB may be useful. However, using
DBCC CHECKDB can be more time consuming than restoring the damaged
files from a backup, and you may not be able to recover all your data.
Caution If running DBCC CHECKDB with one of the repair clauses does
not correct the problem or if you are unsure how this process may
affect your data, contact your primary support provider.
The way to find this information is simply to search for 823.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks
If I tray DBCC CHECKDB, I have error:
Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'gg_system'. No entry
found with that name. Make sure that the name is entered correctly.
This DB is not in list sysdatabases and I can't add this DB. And I don't
have any backup of DB. Maybe another repair software?
Robert Lis
Uytkownik "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> napisa w
wiadomoci news:4290ff5e$1_3@.news.bluewin.ch...
> Hi
> If you are trying to attach a DB, and during the attach process you get
> this error, you can't do much but to revert to a backup as you can't
> attach a DB that SQL Server finds to be suspect.
> On the other hand, if the DB is online, then you can run DBCC CHECKDB.
> --
> ----------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Robert-SoftMAR" <robert@.softmar.com.pl> wrote in message
> news:d6qu0d$ude$1@.news.lublin.pl...
>> How i can repair database ms-sql when attach file i have:
>> error 823 I/O error (bad page ID) detected during read at offset 0x
>> itd...
>> Robert Lis
>>|||"Robert-SoftMAR" <robert@.softmar.com.pl> wrote in message
news:d6r1oa$1vb$1@.news.lublin.pl...
> Thanks
> If I tray DBCC CHECKDB, I have error:
> Server: Msg 911, Level 16, State 1, Line 1
> Could not locate entry in sysdatabases for database 'gg_system'. No entry
> found with that name. Make sure that the name is entered correctly.
> This DB is not in list sysdatabases and I can't add this DB. And I don't
> have any backup of DB. Maybe another repair software?
Sounds like you're pretty much up a creek w/o a paddle then.
I don't know of any tools that can read/repair off-line files for SQL
Server.|||Yes, I now this massage in Books Online but i can't do:
DBCC CHECKDB
beacouse i have error:
Could not locate entry in sysdatabases for database 'gg_system'. No entry
found with that name. Make sure that the name is entered correctly.
Robert Lis
Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.se> napisal w wiadomosci
news:Xns965F261413F3Yazorman@.127.0.0.1...
> Robert-SoftMAR (robert@.softmar.com.pl) writes:
>> How i can repair database ms-sql when attach file i have:
>> error 823 I/O error (bad page ID) detected during read at offset 0x
>> itd...
> Books Online says this about eror 823:
> Check the accessibility and condition of the device in question.
> Run hardware diagnostics and correct problems, if possible.
> Restore damaged files from the latest database backup. Restoring from a
> database backup should always be considered the primary means of fixing
> a damaged database.
> If you don't have a backup or if the errors detected are very isolated,
> the repair functionality of DBCC CHECKDB may be useful. However, using
> DBCC CHECKDB can be more time consuming than restoring the damaged
> files from a backup, and you may not be able to recover all your data.
>
> Caution If running DBCC CHECKDB with one of the repair clauses does
> not correct the problem or if you are unsure how this process may
> affect your data, contact your primary support provider.
>
> The way to find this information is simply to search for 823.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Robert-SoftMAR (robert@.softmar.com.pl) writes:
> Yes, I now this massage in Books Online but i can't do:
> DBCC CHECKDB
> beacouse i have error:
> Could not locate entry in sysdatabases for database 'gg_system'. No entry
> found with that name. Make sure that the name is entered correctly.
> Robert Lis
It appears that that .mdf files is a useless pile of bits. If that database
is important to you, by all means open a case with Microsoft. This is
nothing that can be sorted out over a newsgroup.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Sunday, March 11, 2012
Bad Directory- DTS - Global Variables
I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
Bad Directory- DTS - Global Variables
I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
KarenHi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
Bad Directory- DTS - Global Variables
I am trying to set Global Variable that will open a txt file and import the
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
Karen
Hi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
data into a sql table.
I am currently using the sample I found at sqldts.com (Looping, Importing
and Archiving).
When I set the variable for filename = dbo.cadfilepath.filename, I get a Bad
Directory error.
My filename data is stored in another table - I am looking for the best
approach to calling the filename from the table = cadfilepath and opening
the actual text file and importing the data into my project table.
ie.
Table = CADFilepath(pathname, projectnum)
Pathname ProjectNum
\\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
\\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
\\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
I want to access the info in CADFIlePath and then open the first record's
text file and import this data into my Project Table, then repeat the
process until .EOF.
I know this sounds confusing and I can email you my documentation - since
this website does not allow for attachments.
Any assistances is greatly appreciated.
Karen
Hi
This is a stored procedure which will run a DTS and assign a variable to
file. Modify it for your needs.
I assumed you created the DTS and defined the global varaible.
CREATE proc spDTS
as
declare @.packagename varchar(255)
declare @.userpwd varchar(255) --login pwd
declare @.intsecurity bit --use non-zero to indicate integrated security
declare @.pkgPwd varchar(255) --package password
declare @.hr int
declare @.object int
set @.packagename='PackageName'
set @.userpwd=null
set @.pkgPwd=''
set @.intsecurity=0
--create a package object
EXEC @.hr = sp_OACreate 'DTS.Package', @.object OUTPUT
if @.hr <> 0
Begin
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
end
declare @.svr varchar(15)
declare @.login varchar(15)
select @.login = SUSER_NAME()
select @.svr = @.@.servername
declare @.flag int
select @.flag = 256
EXEC @.hr = sp_OAMethod @.object,
'LoadFromSqlServer',NULL, @.ServerName=@.svr, @.ServerUserName=@.login,
@.PackageName=@.packagename, @.Flags=@.flag, @.PackagePassword = @.pkgPwd
exec @.hr = sp_OAsetproperty
@.object,
'GlobalVariables
("FileLocation").Value', 'c:\MyFile.txt' --set location GV
EXEC @.hr = sp_OAMethod @.object, 'Execute'
IF @.hr <> 0
BEGIN
print 'Execute failed'
EXEC sp_displayoaerrorinfo @.object --, @.hr
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.object, @.hr
RETURN
END
GO
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
|||One thing you can do is to load the Global variable directly from the table.
This is done by adding a Dynamic properties task.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"schaefer" <vkdschaefer1@.attbi.com> wrote in message
news:e%23Q8%23rW1EHA.1408@.TK2MSFTNGP10.phx.gbl...
> I am trying to set Global Variable that will open a txt file and import
the
> data into a sql table.
> I am currently using the sample I found at sqldts.com (Looping, Importing
> and Archiving).
> When I set the variable for filename = dbo.cadfilepath.filename, I get a
Bad
> Directory error.
> My filename data is stored in another table - I am looking for the best
> approach to calling the filename from the table = cadfilepath and opening
> the actual text file and importing the data into my project table.
> ie.
> Table = CADFilepath(pathname, projectnum)
> Pathname ProjectNum
> \\Seasc-Shared\Spln\Shared\Forecast\00000-000.txt 00000-000
> \\Seasc-Shared\Spln\Shared\Forecast\00000-001.txt 00000-001
> \\Seasc-Shared\Spln\Shared\Forecast\00000-002.txt 00000-002
> I want to access the info in CADFIlePath and then open the first record's
> text file and import this data into my Project Table, then repeat the
> process until .EOF.
> I know this sounds confusing and I can email you my documentation - since
> this website does not allow for attachments.
> Any assistances is greatly appreciated.
> Karen
>
Bad data in source file kills package?
I'm reading data from sets of CSV source files with " text qualifiers. Most of the data is good, but I do run into some bad data that doesn't seem to get handled by redirecting rows. The type of data that is killing me is this:
"Business ID","Business Name","Business Phone"
"12345",""A" SPOT, THE ","555-555-5555"
Access sees this as an unparsable record and continues with the rest of the data. SSIS seems to choke on it. I've set the package to allow 1000 errors with no change.
I did set up an error file from my source file, with error output set to redirect row on error and on truncation. The error output seems only to show columns for "Flat File Source Error Output Column", "ErrorCode", and "ErrorColumn" too. (I'm sure there's a good reason, but I've found it really frustrating that it doesn't use the source file column mappings.)
What I'd like to do is redirect (or fix) the bad rows and continue with the rest of the load.
The Execution Results tab shows the following errors.
[Flat File Source [2082]] Error: The column delimiter for column "Business Phone" was not found.
[Flat File Source [2082]] Error: An error occurred while processing file "\\Share\Files\flat_file.csv" on data row 464.
[DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (2082) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread1" has exited with error code 0xC0047039.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
Any help would be much appreciated!
two years past...I encounter the same problem.
Just output the source file to other format: tab as delimiter, execl..... to skip the problem.
|||Hi lund,This is because you were using " as text qualifier and there was a data that had value just the same as the text qualifier. The solution is you should replace the " of your data value with another character like '. Or just use another text qualifier.
Best regards,
Hery|||
The other two posters are correct. If you have any control over the source file consider changing the delimeter (to tabs) and/or the text qualifier (to something that doesn't appear in your text.)
Failing that, I have had some success in the past in pre-processing files. Basically you can load the file line by line, treating each line as a single record, and do some processing on the line to clean it.
Then you output the clean lines back into a new file.
Processing could be simple (eg count the number of commas in a row, or verify that if you split a row you get a set number of columns, and they are the correct width.)
Preferred approach is to fix the source data to export to something other than comma separated, though. There is a good reason that .tab files were invented!
Dylan.
backwards compatibility of the MDF file 2000 and 2005
I'm looking for information on if the SQL Database files are compatable between SQL 2000 and SQL 2005.
I know I can take a mdf from SQL 2000 and attach it to a SQL Server 2005 server and everyting works.
So the real quesiton is : Can I take a SQL Server database that started in SQL 2000, attach it to SQL Server 2005 and work with it, (including schema changes) and then attach it to a SQL Server 2000 server.
In the above scenario I'm keeping the compatability level at 8 (2000).
Does MS have any articals on this subject.
I need to know the limitations since the products I'm working on have to support both versions and so do our internal tools.
Thanks,
D
The MDF file format is different between SQL 2000 and 2005 and are not compatible. Compatability level affects the behavior of certain functionality, not the file format that SQL 2005 uses.
You should be able to write applications that can support both SQL 2000 and SQL 2005 without too much problem, but you will not be able to swap the actuall MDF file between the different versions.
Regards,
Mike Wachal
SQL Express
I have been able to take a 2000 database and use it with SQL 2005 but not the other way around.
Are you saying that I should not be using a 2000 database on a 2005 server?
Thanks for the help
|||You can go from SQL2K to SQL2K5 but not the other way.backward compatability
Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
--
Paul G
Software engineer.Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
--
Paul G
Software engineer.
"Geoff N. Hiten" wrote:
> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > Hi,
> > I was just wondering if you build a database in sql2005 and then save the
> > file(backup dbase) would it work to perform a restore from this file with
> > sql2000?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>|||oops, someone asked the same question just a few posts earlier!
--
Paul G
Software engineer.
"Paul" wrote:
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:
> > No.
> >
> > Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> > will restore to 2000 and 2005, but not 2008 (forthcoming).
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > > Hi,
> > > I was just wondering if you build a database in sql2005 and then save the
> > > file(backup dbase) would it work to perform a restore from this file with
> > > sql2000?
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
--
Paul G
Software engineer.Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
--
Paul G
Software engineer.
"Geoff N. Hiten" wrote:
> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > Hi,
> > I was just wondering if you build a database in sql2005 and then save the
> > file(backup dbase) would it work to perform a restore from this file with
> > sql2000?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>|||oops, someone asked the same question just a few posts earlier!
--
Paul G
Software engineer.
"Paul" wrote:
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:
> > No.
> >
> > Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> > will restore to 2000 and 2005, but not 2008 (forthcoming).
> >
> > --
> > Geoff N. Hiten
> > Senior SQL Infrastructure Consultant
> > Microsoft SQL Server MVP
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> > > Hi,
> > > I was just wondering if you build a database in sql2005 and then save the
> > > file(backup dbase) would it work to perform a restore from this file with
> > > sql2000?
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
backward compatability
Hi,
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
Paul G
Software engineer.
Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
Paul G
Software engineer.
"Geoff N. Hiten" wrote:
> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
>
|||oops, someone asked the same question just a few posts earlier!
Paul G
Software engineer.
"Paul" wrote:
[vbcol=seagreen]
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:
I was just wondering if you build a database in sql2005 and then save the
file(backup dbase) would it work to perform a restore from this file with
sql2000?
thanks.
Paul G
Software engineer.
Paul
No, it will not.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||No.
Compatibility is forward only, typically for two versions. A SQL 7.0 backup
will restore to 2000 and 2005, but not 2008 (forthcoming).
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
> Hi,
> I was just wondering if you build a database in sql2005 and then save the
> file(backup dbase) would it work to perform a restore from this file with
> sql2000?
> thanks.
> --
> Paul G
> Software engineer.
|||Ok thanks for the information, I will use 2000 to build the dbase since we
are using both 2000 production/ 2005 testing(production in near future) .
Paul G
Software engineer.
"Geoff N. Hiten" wrote:
> No.
> Compatibility is forward only, typically for two versions. A SQL 7.0 backup
> will restore to 2000 and 2005, but not 2008 (forthcoming).
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:60AF58FA-67EE-4AF7-94A9-4AC5B3E34732@.microsoft.com...
>
|||oops, someone asked the same question just a few posts earlier!
Paul G
Software engineer.
"Paul" wrote:
[vbcol=seagreen]
> Ok thanks for the information, I will use 2000 to build the dbase since we
> are using both 2000 production/ 2005 testing(production in near future) .
> --
> Paul G
> Software engineer.
>
> "Geoff N. Hiten" wrote:
Thursday, March 8, 2012
Backups: Syncing File and Database backups
Hi,
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or i
s
> there a better way to do this?
> Thanks!
> Ross
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or i
s
> there a better way to do this?
> Thanks!
> Ross
Backups: Syncing File and Database backups
Hi,
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
RossHi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross
Backups: Syncing File and Database backups
Hi,
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
Ross
Hi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross
What's the best practice when trying to synchronize the backup of a set of
databases and a set of files on the file system?
I currently create backup schedules using the Maintenance Plan wizard. Is
there a way to trigger an event when the scheduled backup completes? Or is
there a better way to do this?
Thanks!
Ross
Hi
You can add additional steps to the maintainance plan's backup job, or
alternatively you may want to start the job from batch script run by the
windows scheduler and run xp_sqlmaint (the command executed by the job step
of the maintainance job) though isql.
John
"Ross" wrote:
> Hi,
> What's the best practice when trying to synchronize the backup of a set of
> databases and a set of files on the file system?
> I currently create backup schedules using the Maintenance Plan wizard. Is
> there a way to trigger an event when the scheduled backup completes? Or is
> there a better way to do this?
> Thanks!
> Ross
Backups with filename macros
Greetings, I think this should have a simple answer (whether yes or no).
I would like to make backups to a file in which the filename contains the day of the week. I would assume I could use %d or something like that to do macro substition for the current DOW.
Here is what I am doing (so that this makes more sense):
Right click on your database, All Tasks --> Backup Database...
In the "Destination" section, I am adding a Disk destination and specifying a filename, such as "db_monday.sqlbackup" and "db_tuesday.sqlbackup", then consequently setting the schedule to backup every Monday, Tuesday, etc. I would like to set the schedul
e to backup every day, but specify a macro in the filename to substitute the day of the week. I have tried putting in "db_%d.sqlbackup", but the backup then creates a file named exactly that.
Is this possible to do? If so, how?
Thanks in advance,
Hegg
You could build some dynamic SQL to do that, something like below.
declare @.cmd varchar(100)
set @.cmd='BACKUP DATABASE YOURDB TO DISK=''C:\temp\DB' +
datename(weekday,getdate()+1) + '.sqlbackup'''
exec (@.cmd)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg
|||MiniSQLBackup allows you to use macros for backup type, server, instance and
database names, and the current date time elements, in addition to
generating smaller and encrypted backup files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg
I would like to make backups to a file in which the filename contains the day of the week. I would assume I could use %d or something like that to do macro substition for the current DOW.
Here is what I am doing (so that this makes more sense):
Right click on your database, All Tasks --> Backup Database...
In the "Destination" section, I am adding a Disk destination and specifying a filename, such as "db_monday.sqlbackup" and "db_tuesday.sqlbackup", then consequently setting the schedule to backup every Monday, Tuesday, etc. I would like to set the schedul
e to backup every day, but specify a macro in the filename to substitute the day of the week. I have tried putting in "db_%d.sqlbackup", but the backup then creates a file named exactly that.
Is this possible to do? If so, how?
Thanks in advance,
Hegg
You could build some dynamic SQL to do that, something like below.
declare @.cmd varchar(100)
set @.cmd='BACKUP DATABASE YOURDB TO DISK=''C:\temp\DB' +
datename(weekday,getdate()+1) + '.sqlbackup'''
exec (@.cmd)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg
|||MiniSQLBackup allows you to use macros for backup type, server, instance and
database names, and the current date time elements, in addition to
generating smaller and encrypted backup files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg
Backups with filename macros
Greetings, I think this should have a simple answer (whether yes or no).
I would like to make backups to a file in which the filename contains the da
y of the week. I would assume I could use %d or something like that to do m
acro substition for the current DOW.
Here is what I am doing (so that this makes more sense):
Right click on your database, All Tasks --> Backup Database...
In the "Destination" section, I am adding a Disk destination and specifying
a filename, such as "db_monday.sqlbackup" and "db_tuesday.sqlbackup", then c
onsequently setting the schedule to backup every Monday, Tuesday, etc. I wo
uld like to set the schedul
e to backup every day, but specify a macro in the filename to substitute the
day of the week. I have tried putting in "db_%d.sqlbackup", but the backup
then creates a file named exactly that.
Is this possible to do? If so, how?
Thanks in advance,
HeggYou could build some dynamic SQL to do that, something like below.
declare @.cmd varchar(100)
set @.cmd='BACKUP DATABASE YOURDB TO DISK=''C:\temp\DB' +
datename(weekday,getdate()+1) + '.sqlbackup'''
exec (@.cmd)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg|||MiniSQLBackup allows you to use macros for backup type, server, instance and
database names, and the current date time elements, in addition to
generating smaller and encrypted backup files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg
I would like to make backups to a file in which the filename contains the da
y of the week. I would assume I could use %d or something like that to do m
acro substition for the current DOW.
Here is what I am doing (so that this makes more sense):
Right click on your database, All Tasks --> Backup Database...
In the "Destination" section, I am adding a Disk destination and specifying
a filename, such as "db_monday.sqlbackup" and "db_tuesday.sqlbackup", then c
onsequently setting the schedule to backup every Monday, Tuesday, etc. I wo
uld like to set the schedul
e to backup every day, but specify a macro in the filename to substitute the
day of the week. I have tried putting in "db_%d.sqlbackup", but the backup
then creates a file named exactly that.
Is this possible to do? If so, how?
Thanks in advance,
HeggYou could build some dynamic SQL to do that, something like below.
declare @.cmd varchar(100)
set @.cmd='BACKUP DATABASE YOURDB TO DISK=''C:\temp\DB' +
datename(weekday,getdate()+1) + '.sqlbackup'''
exec (@.cmd)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg|||MiniSQLBackup allows you to use macros for backup type, server, instance and
database names, and the current date time elements, in addition to
generating smaller and encrypted backup files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg
Backups with filename macros
Greetings, I think this should have a simple answer (whether yes or no)
I would like to make backups to a file in which the filename contains the day of the week. I would assume I could use %d or something like that to do macro substition for the current DOW
Here is what I am doing (so that this makes more sense)
Right click on your database, All Tasks --> Backup Database..
In the "Destination" section, I am adding a Disk destination and specifying a filename, such as "db_monday.sqlbackup" and "db_tuesday.sqlbackup", then consequently setting the schedule to backup every Monday, Tuesday, etc. I would like to set the schedule to backup every day, but specify a macro in the filename to substitute the day of the week. I have tried putting in "db_%d.sqlbackup", but the backup then creates a file named exactly that
Is this possible to do? If so, how
Thanks in advance
HeggYou could build some dynamic SQL to do that, something like below.
declare @.cmd varchar(100)
set @.cmd='BACKUP DATABASE YOURDB TO DISK=''C:\temp\DB' +
datename(weekday,getdate()+1) + '.sqlbackup'''
exec (@.cmd)
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg|||MiniSQLBackup allows you to use macros for backup type, server, instance and
database names, and the current date time elements, in addition to
generating smaller and encrypted backup files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg
I would like to make backups to a file in which the filename contains the day of the week. I would assume I could use %d or something like that to do macro substition for the current DOW
Here is what I am doing (so that this makes more sense)
Right click on your database, All Tasks --> Backup Database..
In the "Destination" section, I am adding a Disk destination and specifying a filename, such as "db_monday.sqlbackup" and "db_tuesday.sqlbackup", then consequently setting the schedule to backup every Monday, Tuesday, etc. I would like to set the schedule to backup every day, but specify a macro in the filename to substitute the day of the week. I have tried putting in "db_%d.sqlbackup", but the backup then creates a file named exactly that
Is this possible to do? If so, how
Thanks in advance
HeggYou could build some dynamic SQL to do that, something like below.
declare @.cmd varchar(100)
set @.cmd='BACKUP DATABASE YOURDB TO DISK=''C:\temp\DB' +
datename(weekday,getdate()+1) + '.sqlbackup'''
exec (@.cmd)
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg|||MiniSQLBackup allows you to use macros for backup type, server, instance and
database names, and the current date time elements, in addition to
generating smaller and encrypted backup files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Hegg" <hegg@.netwidedev.com> wrote in message
news:9A9889C3-E278-47B6-90D9-BD1E12BAB623@.microsoft.com...
> Greetings, I think this should have a simple answer (whether yes or no).
> I would like to make backups to a file in which the filename contains the
day of the week. I would assume I could use %d or something like that to do
macro substition for the current DOW.
> Here is what I am doing (so that this makes more sense):
> Right click on your database, All Tasks --> Backup Database...
> In the "Destination" section, I am adding a Disk destination and
specifying a filename, such as "db_monday.sqlbackup" and
"db_tuesday.sqlbackup", then consequently setting the schedule to backup
every Monday, Tuesday, etc. I would like to set the schedule to backup
every day, but specify a macro in the filename to substitute the day of the
week. I have tried putting in "db_%d.sqlbackup", but the backup then
creates a file named exactly that.
> Is this possible to do? If so, how?
> Thanks in advance,
> Hegg
Wednesday, March 7, 2012
backups explained
Beginner's query.
please explain the OFA (open file agent) or lock rule when databases are being backed up.
I know files cannot be backed up if open, but what about database tables-not metadata, but data.
and what about the images accessed by a database-the reports or the docuemnts-they are backed up separately?
where can i find some basic rules for DB's...Short of DB's for dummies.Databases can be backed up while on-line.
Don't try to copy the .mdf/.ldf files - they won't be restorable probably - see backup database in bol.|||What concequences (if any) does open file agent or perhaps locks in this case have when backups are in operation and records are being updated?
For example on NT a file will not be backed up if open. I know the mdf and ldf files (or is it trn also) take logs, and snapshots for transactions, so that db's can be restored to a past point in time (rollback?). i know that bak files can be copied and used to create a database (restore maybe), but what about in db's?
Or is it simply that at that moment a backup is being written to file, and if the transaction is not fully committed prior to or at that time, it is not backed up, but will be included in the next back up...|||mdf file is the database file
ldf is the log file
bak is the database backup
trn is the transaction log file backup
It is not advisable to restore the db based on the mdf and ldf files - as they may be open at the time of backup. The only way to be sure you can restore is to use the bak and trn files. These will also take care of database locking and incomplete transactions at the time of the backup.|||Any pages updated while the backup is taking place are marked and written again to the end of the backup file.
Enough of the transaction log is backed up to allow a restore. Uncommitted transactions are rolled back at the restore.
The backup will slow down all processes on the server but will not stop any activity on the database.|||thanks all
appreciate your time :)
have just been on a sql 2000 admin course so it all sounds alot smipler now!
cheers
please explain the OFA (open file agent) or lock rule when databases are being backed up.
I know files cannot be backed up if open, but what about database tables-not metadata, but data.
and what about the images accessed by a database-the reports or the docuemnts-they are backed up separately?
where can i find some basic rules for DB's...Short of DB's for dummies.Databases can be backed up while on-line.
Don't try to copy the .mdf/.ldf files - they won't be restorable probably - see backup database in bol.|||What concequences (if any) does open file agent or perhaps locks in this case have when backups are in operation and records are being updated?
For example on NT a file will not be backed up if open. I know the mdf and ldf files (or is it trn also) take logs, and snapshots for transactions, so that db's can be restored to a past point in time (rollback?). i know that bak files can be copied and used to create a database (restore maybe), but what about in db's?
Or is it simply that at that moment a backup is being written to file, and if the transaction is not fully committed prior to or at that time, it is not backed up, but will be included in the next back up...|||mdf file is the database file
ldf is the log file
bak is the database backup
trn is the transaction log file backup
It is not advisable to restore the db based on the mdf and ldf files - as they may be open at the time of backup. The only way to be sure you can restore is to use the bak and trn files. These will also take care of database locking and incomplete transactions at the time of the backup.|||Any pages updated while the backup is taking place are marked and written again to the end of the backup file.
Enough of the transaction log is backed up to allow a restore. Uncommitted transactions are rolled back at the restore.
The backup will slow down all processes on the server but will not stop any activity on the database.|||thanks all
appreciate your time :)
have just been on a sql 2000 admin course so it all sounds alot smipler now!
cheers
Backups causing connectivity issues
I'm maintaining a database with an automated backup process.
Periodically, it does a "BACKUP DATABASE" to a local file and then do a file copy across the network to another system. This file is 2.7GB and when it is being copied it causes network problems; specifically ADO connections to the database get broken.
Does anyone have experience with this kind of issue?A DOS Copy is causing a network problem?
I wouldn't think so.
Where's the code, and what's the specific error message.|||A DOS Copy is causing a network problem?
I wouldn't think so.
Where's the code, and what's the specific error message.
I have a C++ ADO client application that gets:
Database COM exception.
HRESULT = -2147467259 (0x80004005)
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
This client application is running 24/7 on many different workstations. They all encounter this error and shutdown at the same time. It seems to only encounters this error during the file copy operation I mentioned. I assume that the 2.7GB file copy consumes so much bandwidth that it disrupts other network activity. CPU use on the server is low. I wouldn't have guessed this as the problem but that is certainly what the symptoms suggest.|||I still wouldn't put any money down on that...
But as check, why not copy the file over during periods of low activity...|||I still wouldn't put any money down on that...
But as check, why not copy the file over during periods of low activity...
We do. We copy it over at midnight. The C++ client app I mentioned needs to run 24/7/365 though and it can't lose network connectivity to the database (this is all on a LAN and has UPS).|||OK...out of the box thinking...is there anything else affected?
You sure it's not something to do with the way the app is constructed?
I'm no network guy, but can you put a sniffer on the network?|||DOS copy may be the same as Windows copy. As I understand things, Windows copy eats a lot of bandwidth in parity checks, and whatnot. Can you use FTP to deliver the database to an FTP server? That may be a lighter connection.|||Thanks guys. Those suggestions might work but the network guys agreed to just backup to a separate local hard drive. That will definitely spare the network.|||For files of these sizes to move over LAN people usually set up a dedicated NIC on a different subnet. If you spare a couple of bucks wisely (SQLLiteSpeed) you may shrink the size of the backup file down to around 500MB.
Periodically, it does a "BACKUP DATABASE" to a local file and then do a file copy across the network to another system. This file is 2.7GB and when it is being copied it causes network problems; specifically ADO connections to the database get broken.
Does anyone have experience with this kind of issue?A DOS Copy is causing a network problem?
I wouldn't think so.
Where's the code, and what's the specific error message.|||A DOS Copy is causing a network problem?
I wouldn't think so.
Where's the code, and what's the specific error message.
I have a C++ ADO client application that gets:
Database COM exception.
HRESULT = -2147467259 (0x80004005)
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.
This client application is running 24/7 on many different workstations. They all encounter this error and shutdown at the same time. It seems to only encounters this error during the file copy operation I mentioned. I assume that the 2.7GB file copy consumes so much bandwidth that it disrupts other network activity. CPU use on the server is low. I wouldn't have guessed this as the problem but that is certainly what the symptoms suggest.|||I still wouldn't put any money down on that...
But as check, why not copy the file over during periods of low activity...|||I still wouldn't put any money down on that...
But as check, why not copy the file over during periods of low activity...
We do. We copy it over at midnight. The C++ client app I mentioned needs to run 24/7/365 though and it can't lose network connectivity to the database (this is all on a LAN and has UPS).|||OK...out of the box thinking...is there anything else affected?
You sure it's not something to do with the way the app is constructed?
I'm no network guy, but can you put a sniffer on the network?|||DOS copy may be the same as Windows copy. As I understand things, Windows copy eats a lot of bandwidth in parity checks, and whatnot. Can you use FTP to deliver the database to an FTP server? That may be a lighter connection.|||Thanks guys. Those suggestions might work but the network guys agreed to just backup to a separate local hard drive. That will definitely spare the network.|||For files of these sizes to move over LAN people usually set up a dedicated NIC on a different subnet. If you spare a couple of bucks wisely (SQLLiteSpeed) you may shrink the size of the backup file down to around 500MB.
Backups and log file growth issues
I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:
BACKUP DATABASE [DSS] TO DISK =
N'G:\MSSQL\Backup\DSSbackup.BAK'
WITH INIT , NOUNLOAD , NAME = N'DSS backup',
SKIP , STATS = 10, NOFORMAT
Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?
Thanks!
Additional information: I have used the scripts below to backup my database and logfile including using the INIT to truncate the log file. My database file is 9.2 GB and my log file is 9.9 GB. After running the scripts, a transaction log backup file is created (512 KB), however my ldf log file remains 9.9 GB. Why are the transactions not being written to the database file and log file entries being released?
/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:\mssql\backup\database\DSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:\mssql\backup\database\DSS.bak'
WITH FILE = 1
GO
/* This script backs up the DSS log file and truncates the file */
BACKUP LOG [DSS]
TO DISK = N'g:\mssql\backup\logfiles\DSS_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO
Thanks!
I have few observation
(a) in this script what you are doing is you are overwriting the earlier backup with the current one... which is not at all recommended. if the backup file got corrupted means... you will endup without any backup.. this is a serious issue. You should not use INIT in this case i believe.
(b) to transaction log size under control , you should increase the frequency of the TL backup. If currently you are configured TL backup daily then make it hourly with out INIT option. If you overwrite the backup of TL then there is no point in taking the TL Backup
(c) if you do not want POINT-in-Time restore you can change the Recovery model to Simple , which will control the size to a certain limit
(d) If you want to trucate the LOg then use WIth TRUNCATE ONLY option with the backup script
EG.
backup log [DSS] with truncate_only
dbcc shrinkfile(DSS_Log)
Read more about TL architecture and Backup method /Recovery model in BOL
Read these article also :-
FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235
FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139
FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375
FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html
FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318
Madhu
|||
Thank you for your response.
(a)The backup database and log files are backed up to tape regularily so I am not in danger of losing the backup.
(b) & (c) This is a new database, not yet released to production, so I will plan accordingly as you suggested to ensure a point-in-time recovery is available.
(d) As this database is new, I will try your script commands to reduce the log file this one time.
This would fall into the class of a one-off increase. I just created this database and imported several SQL2000 tables. I then re-wrote the SQL2000 stored procedures which truncate and reload the tables, so I feel this caused the log file increase. I will try the DBCC shrink this time, however, I do not plan to use it regularily.
Thanks again for your response!
|||I performed a full database backup and a transaction log backup. I ran the following query:
DBCC SHRINKFILE (DSS_log)
..and received the following message:
Cannot shrink log file 2 (DSS_log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I am assuming there may be uncommitted transactions in the log file. Is there a T-SQL query to force all transactions to be committed and then erased from the log file?
Subscribe to:
Posts (Atom)