DBA Dilemma

We have a little dilemma with our DBA staff regarding reporting services and
using a general account, we call RSAdmin, for executing stored procs in the
background and bringing back the results via Reporting Services.
Our DBA is suggesting that we stay away from a general account like this
because of the implicit security that is associated with it. Our argument to
them is that if we start granting by individual, then that will make their
job even that much more complex and the maintenance on that is a nightmare
with people moving and quitting, etc..
Has anyone experience issues like this and how did you go about resolving
them.
Any answer is GREATLY appreciated and Thanks in advance.
wnfisbaI do not ever use the account of the person running the report. I have a
generic account say Reports (I run in mixed mode). This account is only used
for reports and is readonly. When I have a stored procedure it gives this
account execute permissions. This is very clean and is definitely not a
security hole. What user sees what is controled by who is given rights to
what report. So I control that at the Report Server.
Another advantage of this is that this allows connection pooling. Connection
pooling requires the connection to be exactly the same. If the connection is
for a different user then there will be a new connection created (or perhaps
multiple connections) per user running a report. Connection pooling both
puts less strain on the database server and it improves your performance.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:C576900D-1F8B-4DA6-B3EE-7F1642B034B4@.microsoft.com...
> We have a little dilemma with our DBA staff regarding reporting services
> and
> using a general account, we call RSAdmin, for executing stored procs in
> the
> background and bringing back the results via Reporting Services.
> Our DBA is suggesting that we stay away from a general account like this
> because of the implicit security that is associated with it. Our argument
> to
> them is that if we start granting by individual, then that will make their
> job even that much more complex and the maintenance on that is a nightmare
> with people moving and quitting, etc..
> Has anyone experience issues like this and how did you go about resolving
> them.
> Any answer is GREATLY appreciated and Thanks in advance.
> wnfisba

DB2 Stored Procedures using ODBC

I am trying to call DB2 z/OS Stored Procedures from the report designer using
a DataDirect ODBC driver. I get a result set with a simple SP which has no
input parameters. If I call a SP with one input parameter defined as
integer, my result set is returned if I code the value in the call statement.
But, if I give it a parameter marker and supply the value after being
prompted, I get an error stating "VALUE OF INPUT HOST VARIABLE NUM 001 NOT
USED; WRONG DATA TYPE".
My guess is that the parameter marker may get passed instead of the value,
or the integer is not being converted correctly. If I change the datatype of
the input parm in my SP to CHAR(1), everything works fine. Note: I am not
using the input parms in my SP. They are being ignored for testing reasons.
If I used the IBM OLE DB driver, my above scenario works fine. This leads
me to believe I have an ODBC setting issue (or some other programmer error).
And for the record, I am using the DataDirect ODBC driver instead of the IBM
OLE DB because of licensing concerns.
Has anyone had similar issues while calling DB2 Stored Procedures via an
ODBC driver?Hello,
Based on the symptom, it is more like that the issue exist in the odbc
driver. If you are use SQL 2005, You could try latest Microsoft OLEDB
provider for DB2
http://www.microsoft.com/downloads/details.aspx?FamilyID=D09C1D60-A13C-4479-
9B91-9E8B9D835CDC&displaylang=en
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks. We are using SQL 2000 and accessing DB2 on z/OS. I am also in
contact with the driver's support team, but I was wondering if anyone has
tried something similar. Reporting Services works great with this driver for
mapping to tables, but using Stored Procedures has proved difficult.
""privatenews"" wrote:
> Hello,
> Based on the symptom, it is more like that the issue exist in the odbc
> driver. If you are use SQL 2005, You could try latest Microsoft OLEDB
> provider for DB2
> http://www.microsoft.com/downloads/details.aspx?FamilyID=D09C1D60-A13C-4479-
> 9B91-9E8B9D835CDC&displaylang=en
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hello,
I think the driver support team shall be the best resource on this issue.
You may also want to try some other drivers if possible
http://www.codeproject.com/dotnet/DotnetDb2.asp
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

db_owner member execute problems

I've made a user a member of the db_owner role of a particular database.
However, this account does not have permission to execute a stored procedure
and I can't figure out why. The error message is shown below...
Server: Msg 15247, Level 16, State 1, Procedure sp_addmessage, Line 19
User does not have permission to perform this action.
Any help appreciated
Regards
PaulOnly members of the sysadmin or serveradmin server roles can
execute sp_addmessage.
-Sue
On Fri, 15 Jul 2005 10:25:30 +0100, "Paul Hatcher"
<paul.hatcher@.online.nospam> wrote:

>I've made a user a member of the db_owner role of a particular database.
>However, this account does not have permission to execute a stored procedur
e
>and I can't figure out why. The error message is shown below...
>Server: Msg 15247, Level 16, State 1, Procedure sp_addmessage, Line 19
>User does not have permission to perform this action.
>Any help appreciated
>Regards
>Paul
>|||Sue
Thanks for that - I was misreading the message as a lack of ability to run
the sp - I forgot that I was creating a custom message inside it!
Regards
Paul
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:voafd15qnose10e9dvdlrobdt9ohmh4s8p@.
4ax.com...
> Only members of the sysadmin or serveradmin server roles can
> execute sp_addmessage.
> -Sue
> On Fri, 15 Jul 2005 10:25:30 +0100, "Paul Hatcher"
> <paul.hatcher@.online.nospam> wrote:
>
>

DB to DB sp

Hi,
i am writing a tool that interfaces to a third-party database. I will be
creating a database for my tool which contains stored procedures that
accesses tables from the third-party database. I am doing this only to aviod
manipulating the third party database. My database will be located in the
same server as the third-party database.
My question is, if i were to create these stored procedures and views that
accesses only tables on the third-party database in my database, would my
app still benefit from the performance gain which stored procedures and
views offers?
regards"Emmanuel" <emmanuel@.email.com> wrote in message
news:OP8RCD4kFHA.3580@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i am writing a tool that interfaces to a third-party database. I will be
> creating a database for my tool which contains stored procedures that
> accesses tables from the third-party database. I am doing this only to
> aviod manipulating the third party database. My database will be located
> in the same server as the third-party database.
> My question is, if i were to create these stored procedures and views that
> accesses only tables on the third-party database in my database, would my
> app still benefit from the performance gain which stored procedures and
> views offers?
> regards
>
That depends...
Are you going to use OPENROWSET and/or OPENQUERY, or are you going to create
a Linked server?
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Rick,
No, since the database will be created on the same server i will access the
third-party db tables using queries like:
SELECT column from [ThirdPartyDB].[dbo].[ThirdPartyTable]
and at most i would make queries which join some tables..
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:OenmiI4kFHA.3312@.tk2msftngp13.phx.gbl...
> "Emmanuel" <emmanuel@.email.com> wrote in message
> news:OP8RCD4kFHA.3580@.TK2MSFTNGP09.phx.gbl...
> That depends...
> Are you going to use OPENROWSET and/or OPENQUERY, or are you going to
> create a Linked server?
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> My question is, if i were to create these stored procedures and views that
> accesses only tables on the third-party database in my database, would my
> app still benefit from the performance gain which stored procedures and
> views offers?
Yes, if on the same server (instance).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Emmanuel" <emmanuel@.email.com> wrote in message news:OP8RCD4kFHA.3580@.TK2MSFTNGP09.phx.gbl
..
> Hi,
> i am writing a tool that interfaces to a third-party database. I will be
> creating a database for my tool which contains stored procedures that
> accesses tables from the third-party database. I am doing this only to avi
od
> manipulating the third party database. My database will be located in the
> same server as the third-party database.
> My question is, if i were to create these stored procedures and views that
> accesses only tables on the third-party database in my database, would my
> app still benefit from the performance gain which stored procedures and
> views offers?
> regards
>|||Yes, this will work; we do it all the time.
Having had a lot of expeience with integrating with 3rd-part databases,
here's some hard-earned lessons:
1. If you plan on EVER upgrading the third-party aplication, build a
relationship with the vendor. Let them know what you're doing, and
find out if they have a recommended solution for what you are planning
to do.
2. Always try to integrate using an application layer BEFORE going to
the data layer. Sometimes you have no choice, but if they support an
API, use it. It will save you some major headaches when they change
their schema at a later date (and that happens more often than you
know).
3. If there is no API, see if you can get them to commit to a standard
schema for exposng their database (much like Microsoft's
INFORMATION_SCHEMA for their system tables).
HTH,
Stu|||Hi,
thanks everyone! this was really helpful.
regards,
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1122564554.859930.125160@.g44g2000cwa.googlegroups.com...
> Yes, this will work; we do it all the time.
> Having had a lot of expeience with integrating with 3rd-part databases,
> here's some hard-earned lessons:
> 1. If you plan on EVER upgrading the third-party aplication, build a
> relationship with the vendor. Let them know what you're doing, and
> find out if they have a recommended solution for what you are planning
> to do.
> 2. Always try to integrate using an application layer BEFORE going to
> the data layer. Sometimes you have no choice, but if they support an
> API, use it. It will save you some major headaches when they change
> their schema at a later date (and that happens more often than you
> know).
> 3. If there is no API, see if you can get them to commit to a standard
> schema for exposng their database (much like Microsoft's
> INFORMATION_SCHEMA for their system tables).
> HTH,
> Stu
>

DB Restore with Transaction File Only

hi,
I have a sql where data files and transaction files are
stored seperatedly... and one time a problem occured when
data files become corrupted and the only thing left is the
transaction file.... and the databases went offline... my
question is can i restore from a full db backup and then
attach the existing transaction files...?No. It's not possible nor logical to just attach the trx log file to an
existing database. If you have made trx log backups consistently since the
last full backup, you might have a chance of restoring your database to its
previous state prior to the corruption.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"perry" <pramos@.codex-systems.com> wrote in message
news:7c8b01c4956b$985122f0$a601280a@.phx.gbl...
> hi,
> I have a sql where data files and transaction files are
> stored seperatedly... and one time a problem occured when
> data files become corrupted and the only thing left is the
> transaction file.... and the databases went offline... my
> question is can i restore from a full db backup and then
> attach the existing transaction files...?|||perry,
Yes you can! As long as the transaction log chain (all transaction log
backups since your last full backup) is uninterrupted since your last
full backup it will work great. This scenario is exactly what the
transaction log backups are for. Good luck, if you need further help,
post back.
Look up transaction log in Books online, there's some great information
in there.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
perry wrote:
> hi,
> I have a sql where data files and transaction files are
> stored seperatedly... and one time a problem occured when
> data files become corrupted and the only thing left is the
> transaction file.... and the databases went offline... my
> question is can i restore from a full db backup and then
> attach the existing transaction files...?|||perry,
After reading Peter's post I noticed you asked if you can *attach* the
transaction log file. No, you can't, you can however restore the
transaction log chain as mentioned in my post. Sorry for the error.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark Allison wrote:
> perry,
> Yes you can! As long as the transaction log chain (all transaction log
> backups since your last full backup) is uninterrupted since your last
> full backup it will work great. This scenario is exactly what the
> transaction log backups are for. Good luck, if you need further help,
> post back.
> Look up transaction log in Books online, there's some great information
> in there.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> perry wrote:
>> hi,
>> I have a sql where data files and transaction files are stored
>> seperatedly... and one time a problem occured when data files become
>> corrupted and the only thing left is the transaction file.... and the
>> databases went offline... my question is can i restore from a full db
>> backup and then attach the existing transaction files...?|||But what if I have a singel transaction log backup which I
have set up to notrucate, waht is the possible data
recovery i can have......will all trasactions prior to
the last full database backup up to the trasaction backup
(which is run daily and overwrites it in a single file)
will be recovered...thanks for the post..your a great
help...
>--Original Message--
>No. It's not possible nor logical to just attach the trx
log file to an
>existing database. If you have made trx log backups
consistently since the
>last full backup, you might have a chance of restoring
your database to its
>previous state prior to the corruption.
>--
>Peter Yeoh
>http://www.yohz.com
>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
free!
>
>"perry" <pramos@.codex-systems.com> wrote in message
>news:7c8b01c4956b$985122f0$a601280a@.phx.gbl...
>> hi,
>> I have a sql where data files and transaction files are
>> stored seperatedly... and one time a problem occured
when
>> data files become corrupted and the only thing left is
the
>> transaction file.... and the databases went offline...
my
>> question is can i restore from a full db backup and then
>> attach the existing transaction files...?
>
>.
>|||Perry,
Are you saying that you do BACKUP LOG ... WITH NO_TRUNCATE?
If so, then the transaction log (the ldf file) will grow and grow and the space within never be
reused.
Anyhow, assuming that you do, you might be able to use such a backup. But if you read Books Online,
it only documents that option for doing a transaction log backup when the database is damaged...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"perry" <anonymous@.discussions.microsoft.com> wrote in message
news:7e8801c4957d$bc828ee0$a501280a@.phx.gbl...
>
> But what if I have a singel transaction log backup which I
> have set up to notrucate, waht is the possible data
> recovery i can have......will all trasactions prior to
> the last full database backup up to the trasaction backup
> (which is run daily and overwrites it in a single file)
> will be recovered...thanks for the post..your a great
> help...
>
>>--Original Message--
>>No. It's not possible nor logical to just attach the trx
> log file to an
>>existing database. If you have made trx log backups
> consistently since the
>>last full backup, you might have a chance of restoring
> your database to its
>>previous state prior to the corruption.
>>--
>>Peter Yeoh
>>http://www.yohz.com
>>Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
> free!
>>
>>"perry" <pramos@.codex-systems.com> wrote in message
>>news:7c8b01c4956b$985122f0$a601280a@.phx.gbl...
>> hi,
>> I have a sql where data files and transaction files are
>> stored seperatedly... and one time a problem occured
> when
>> data files become corrupted and the only thing left is
> the
>> transaction file.... and the databases went offline...
> my
>> question is can i restore from a full db backup and then
>> attach the existing transaction files...?
>>
>>.|||Like Tibor, I'm assuming you have consistently backed up the log with the
NO_TRUNCATE option (btw, strange thing to do). However, each of the trx log
backup will only contain the log data since the last log backup. It will
not store whatever log data is in the log file (I'm guessing you use the
NO_TRUNCATE option because you wanted your trx log backups to always contain
all log data since the last full backup). Did a quick test to confirm this.
I guess you now have a very large ldf file, and want to be able to recover
the data into the mdf file. No idea how this can be done, or even possible.
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"perry" <anonymous@.discussions.microsoft.com> wrote in message
news:7e8801c4957d$bc828ee0$a501280a@.phx.gbl...
>
> But what if I have a singel transaction log backup which I
> have set up to notrucate, waht is the possible data
> recovery i can have......will all trasactions prior to
> the last full database backup up to the trasaction backup
> (which is run daily and overwrites it in a single file)
> will be recovered...thanks for the post..your a great
> help...
>
> >--Original Message--
> >No. It's not possible nor logical to just attach the trx
> log file to an
> >existing database. If you have made trx log backups
> consistently since the
> >last full backup, you might have a chance of restoring
> your database to its
> >previous state prior to the corruption.
> >
> >--
> >Peter Yeoh
> >http://www.yohz.com
> >Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
> free!
> >
> >
> >"perry" <pramos@.codex-systems.com> wrote in message
> >news:7c8b01c4956b$985122f0$a601280a@.phx.gbl...
> >>
> >> hi,
> >> I have a sql where data files and transaction files are
> >> stored seperatedly... and one time a problem occured
> when
> >> data files become corrupted and the only thing left is
> the
> >> transaction file.... and the databases went offline...
> my
> >> question is can i restore from a full db backup and then
> >> attach the existing transaction files...?
> >
> >
> >.
> >|||Peter,
Interesting. Just so I understand you correctly, a scenario:
0. BACKUP LOG
1. BACKUP DB
2. BACKUP LOG NO_TRUNCATE
3. BACKUP LOG NO_TRUNCATE
4. BACKUP LOG NO_TRUNCATE
Status now is:
The ldf file contains all log records since 0.
The backup file produced in step 4 contains the log records since 3.
Is above what you test showed? If so, then my gut feeling about NO_TRUNCATE is correct and the name
of the parameter is misleading (something I've been trying to communicate to MS with little
success). The name of the option implies that you can keep doing log backups and only apply the
latest. I've always explained this option from what it is documented as in Books Online, to backup
log of a broken database...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:ePpBp9YlEHA.592@.TK2MSFTNGP11.phx.gbl...
> Like Tibor, I'm assuming you have consistently backed up the log with the
> NO_TRUNCATE option (btw, strange thing to do). However, each of the trx log
> backup will only contain the log data since the last log backup. It will
> not store whatever log data is in the log file (I'm guessing you use the
> NO_TRUNCATE option because you wanted your trx log backups to always contain
> all log data since the last full backup). Did a quick test to confirm this.
> I guess you now have a very large ldf file, and want to be able to recover
> the data into the mdf file. No idea how this can be done, or even possible.
> --
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "perry" <anonymous@.discussions.microsoft.com> wrote in message
> news:7e8801c4957d$bc828ee0$a501280a@.phx.gbl...
>>
>> But what if I have a singel transaction log backup which I
>> have set up to notrucate, waht is the possible data
>> recovery i can have......will all trasactions prior to
>> the last full database backup up to the trasaction backup
>> (which is run daily and overwrites it in a single file)
>> will be recovered...thanks for the post..your a great
>> help...
>>
>> >--Original Message--
>> >No. It's not possible nor logical to just attach the trx
>> log file to an
>> >existing database. If you have made trx log backups
>> consistently since the
>> >last full backup, you might have a chance of restoring
>> your database to its
>> >previous state prior to the corruption.
>> >
>> >--
>> >Peter Yeoh
>> >http://www.yohz.com
>> >Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
>> free!
>> >
>> >
>> >"perry" <pramos@.codex-systems.com> wrote in message
>> >news:7c8b01c4956b$985122f0$a601280a@.phx.gbl...
>> >>
>> >> hi,
>> >> I have a sql where data files and transaction files are
>> >> stored seperatedly... and one time a problem occured
>> when
>> >> data files become corrupted and the only thing left is
>> the
>> >> transaction file.... and the databases went offline...
>> my
>> >> question is can i restore from a full db backup and then
>> >> attach the existing transaction files...?
>> >
>> >
>> >.
>> >
>|||> Status now is:
> The ldf file contains all log records since 0.
> The backup file produced in step 4 contains the log records since 3.
Right on. The backup file size in step 4 is not the cumulative size of the
files produced in step 2 and 3.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23GGEXMZlEHA.2948@.TK2MSFTNGP11.phx.gbl...
> Peter,
> Interesting. Just so I understand you correctly, a scenario:
> 0. BACKUP LOG
> 1. BACKUP DB
> 2. BACKUP LOG NO_TRUNCATE
> 3. BACKUP LOG NO_TRUNCATE
> 4. BACKUP LOG NO_TRUNCATE
> Status now is:
> The ldf file contains all log records since 0.
> The backup file produced in step 4 contains the log records since 3.
> Is above what you test showed? If so, then my gut feeling about
NO_TRUNCATE is correct and the name
> of the parameter is misleading (something I've been trying to communicate
to MS with little
> success). The name of the option implies that you can keep doing log
backups and only apply the
> latest. I've always explained this option from what it is documented as in
Books Online, to backup
> log of a broken database...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
news:ePpBp9YlEHA.592@.TK2MSFTNGP11.phx.gbl...
> > Like Tibor, I'm assuming you have consistently backed up the log with
the
> > NO_TRUNCATE option (btw, strange thing to do). However, each of the trx
log
> > backup will only contain the log data since the last log backup. It
will
> > not store whatever log data is in the log file (I'm guessing you use the
> > NO_TRUNCATE option because you wanted your trx log backups to always
contain
> > all log data since the last full backup). Did a quick test to confirm
this.
> >
> > I guess you now have a very large ldf file, and want to be able to
recover
> > the data into the mdf file. No idea how this can be done, or even
possible.
> >
> > --
> > Peter Yeoh
> > http://www.yohz.com
> > Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
> >
> >
> > "perry" <anonymous@.discussions.microsoft.com> wrote in message
> > news:7e8801c4957d$bc828ee0$a501280a@.phx.gbl...
> >>
> >>
> >> But what if I have a singel transaction log backup which I
> >> have set up to notrucate, waht is the possible data
> >> recovery i can have......will all trasactions prior to
> >> the last full database backup up to the trasaction backup
> >> (which is run daily and overwrites it in a single file)
> >> will be recovered...thanks for the post..your a great
> >> help...
> >>
> >>
> >> >--Original Message--
> >> >No. It's not possible nor logical to just attach the trx
> >> log file to an
> >> >existing database. If you have made trx log backups
> >> consistently since the
> >> >last full backup, you might have a chance of restoring
> >> your database to its
> >> >previous state prior to the corruption.
> >> >
> >> >--
> >> >Peter Yeoh
> >> >http://www.yohz.com
> >> >Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
> >> free!
> >> >
> >> >
> >> >"perry" <pramos@.codex-systems.com> wrote in message
> >> >news:7c8b01c4956b$985122f0$a601280a@.phx.gbl...
> >> >>
> >> >> hi,
> >> >> I have a sql where data files and transaction files are
> >> >> stored seperatedly... and one time a problem occured
> >> when
> >> >> data files become corrupted and the only thing left is
> >> the
> >> >> transaction file.... and the databases went offline...
> >> my
> >> >> question is can i restore from a full db backup and then
> >> >> attach the existing transaction files...?
> >> >
> >> >
> >> >.
> >> >
> >
> >
>|||> Right on. The backup file size in step 4 is not the cumulative size of the
> files produced in step 2 and 3.
Indeed! I just wrote an article on the topic, with some SQL code proving the point...
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter Yeoh" <nospam@.nospam.com> wrote in message news:e$ni%23PZlEHA.2224@.tk2msftngp13.phx.gbl...
>> Status now is:
>> The ldf file contains all log records since 0.
>> The backup file produced in step 4 contains the log records since 3.
> Right on. The backup file size in step 4 is not the cumulative size of the
> files produced in step 2 and 3.
> Peter Yeoh
> http://www.yohz.com
> Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23GGEXMZlEHA.2948@.TK2MSFTNGP11.phx.gbl...
>> Peter,
>> Interesting. Just so I understand you correctly, a scenario:
>> 0. BACKUP LOG
>> 1. BACKUP DB
>> 2. BACKUP LOG NO_TRUNCATE
>> 3. BACKUP LOG NO_TRUNCATE
>> 4. BACKUP LOG NO_TRUNCATE
>> Status now is:
>> The ldf file contains all log records since 0.
>> The backup file produced in step 4 contains the log records since 3.
>> Is above what you test showed? If so, then my gut feeling about
> NO_TRUNCATE is correct and the name
>> of the parameter is misleading (something I've been trying to communicate
> to MS with little
>> success). The name of the option implies that you can keep doing log
> backups and only apply the
>> latest. I've always explained this option from what it is documented as in
> Books Online, to backup
>> log of a broken database...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:ePpBp9YlEHA.592@.TK2MSFTNGP11.phx.gbl...
>> > Like Tibor, I'm assuming you have consistently backed up the log with
> the
>> > NO_TRUNCATE option (btw, strange thing to do). However, each of the trx
> log
>> > backup will only contain the log data since the last log backup. It
> will
>> > not store whatever log data is in the log file (I'm guessing you use the
>> > NO_TRUNCATE option because you wanted your trx log backups to always
> contain
>> > all log data since the last full backup). Did a quick test to confirm
> this.
>> >
>> > I guess you now have a very large ldf file, and want to be able to
> recover
>> > the data into the mdf file. No idea how this can be done, or even
> possible.
>> >
>> > --
>> > Peter Yeoh
>> > http://www.yohz.com
>> > Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
>> >
>> >
>> > "perry" <anonymous@.discussions.microsoft.com> wrote in message
>> > news:7e8801c4957d$bc828ee0$a501280a@.phx.gbl...
>> >>
>> >>
>> >> But what if I have a singel transaction log backup which I
>> >> have set up to notrucate, waht is the possible data
>> >> recovery i can have......will all trasactions prior to
>> >> the last full database backup up to the trasaction backup
>> >> (which is run daily and overwrites it in a single file)
>> >> will be recovered...thanks for the post..your a great
>> >> help...
>> >>
>> >>
>> >> >--Original Message--
>> >> >No. It's not possible nor logical to just attach the trx
>> >> log file to an
>> >> >existing database. If you have made trx log backups
>> >> consistently since the
>> >> >last full backup, you might have a chance of restoring
>> >> your database to its
>> >> >previous state prior to the corruption.
>> >> >
>> >> >--
>> >> >Peter Yeoh
>> >> >http://www.yohz.com
>> >> >Need smaller SQL2K backup files? Use MiniSQLBackup Lite,
>> >> free!
>> >> >
>> >> >
>> >> >"perry" <pramos@.codex-systems.com> wrote in message
>> >> >news:7c8b01c4956b$985122f0$a601280a@.phx.gbl...
>> >> >>
>> >> >> hi,
>> >> >> I have a sql where data files and transaction files are
>> >> >> stored seperatedly... and one time a problem occured
>> >> when
>> >> >> data files become corrupted and the only thing left is
>> >> the
>> >> >> transaction file.... and the databases went offline...
>> >> my
>> >> >> question is can i restore from a full db backup and then
>> >> >> attach the existing transaction files...?
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>>
>

DB Performance

I ave the following Code in my Stored procedure.

Declare Cursor for table A
WHILE @.@.FETCH_STATUS = 0
Get values from other function based on some business logic.
INSERT Into another table B
(or)
UPDATE to another table B
END

I have to insert/update values to table B, one by one row. So, it is taking more time.
Is there any way to collect the values into a temporary storage and Insert/update or Move the values to table B.Why do you use a cursor ? It's not performant for this type of query...|||While you can certainly do that, in some instances (such as those which don't involve the tables possibly being updated in real-time), you might be able to accomplish a similar effect by wrapping the request in a transaction having an appropriate isolation-level. What you're trying to do here is to delay disk-writes and make more efficient use of in-memory buffers.

You can also sometimes achieve good results by sorting the records that are being updated, so that they are presented to the system (say) in primary-key order. Once again, we're trying to improve the chances that the next record we're going to be updating is nearby to the last one, and thus "still available in our in-memory buffers."

Sorting is an "unexpectedly fast and efficient" thing for a computer to do. "Random-access anything" is inherently slower due to the mechanical delays of moving the read/write head about.
"I/O!... I/O!... it's off to disk we go!" ;)
"Seek, and ye shall wait." :D|||Originally posted by fadace
Why do you use a cursor ? It's not performant for this type of query...

How can avoid Cursor. My Exact query is like this..
DECLARE cur_populate_CachePrograms CURSOR FOR
SELECT e.EmployeeCode,
FROM tbl_Employee e
WHERE e.IsActive = 1
ORDER BY e.EmployeeCode

OPEN cur_populate_CachePrograms

FETCH NEXT FROM cur_populate_CachePrograms INTO @.EmployeeCode

WHILE @.@.FETCH_STATUS = 0
BEGIN
exec usp_PopulateCacheTable_VarPay3 @.EmployeeCode, @.IsVarPay3NeedComments OUTPUT

IF EXISTS(select EmployeeCode from tbl_Program where EmployeeCode = @.EmployeeCode)
BEGIN
UPDATE tbl_CacheEmployeeToProgram
SET IsVarPay3NeedComments = @.IsVarPay3NeedComments,
WHERE @.EmployeeCode = EmployeeCode
END
ELSE
BEGIN
INSERT INTO tbl_Program
(EmployeeCode, IsVarPay3NeedComments) VALUES (@.EmployeeCode,@.IsVarPay3NeedComments)
END

FETCH NEXT FROM cur_populate_CachePrograms INTO @.EmployeeCode

END
CLOSE cur_populate_CachePrograms
DEALLOCATE cur_populate_CachePrograms|||Depending on what is going on in usp_PopulateCacheTable_VarPay3, you might be able to change it to a function which returns @.IsVarPay3NeedComments. You can then use the function in-line in you SQL statement.

Something like, and this is sketchy guess-work with what info I have...

UPDATE tbl_CacheEmployeeToProgram
SET IsVarPay3NeedComments = uFN_PopulateCacheTable_VarPay3(tbl_Employee.Employ eeCode)
FROM tbl_CacheEmployeeToProgram
INNER JOIN tbl_Employee

SELECT e.EmployeeCode,
FROM tbl_Employee e
WHERE e.IsActive = 1
ORDER BY e.EmployeeCode|||Sorry, hit the Enter Key accidently. Let's try that again...

Depending on what is going on in usp_PopulateCacheTable_VarPay3, you might be able to change it to a function which returns @.IsVarPay3NeedComments. You can then use the function in-line in you SQL statement.

Something like, and this is sketchy guess-work with what info I have...

UPDATE tbl_CacheEmployeeToProgram
SET IsVarPay3NeedComments = uFN_PopulateCacheTable_VarPay3(tbl_Employee.Employ eeCode)
FROM tbl_CacheEmployeeToProgram
INNER JOIN tbl_Employee
ON tbl_CacheEmployeeToProgram.EmployeeCode = tbl_Employee.EmployeeCode
INNER JOIN tbl_Program
ON tbl_Employee.EmployeeCode = tbl_Program.EmployeeCode|||Oh, and add

WHERE tbl_Employee.IsActive=1|||usp_PopulateCacheTable_VarPay3 is going to return more than one value...
So, I can't use function|||You can have it return a result set (TABLE)|||But if function uFN_PopulateCacheTable_VarPay3 returns TABLE variable which contains more than one variable, how do I set to multiple columns.

UPDATE tbl_program
SET IsVarPay3NeedComments = uFN_PopulateCacheTable_VarPay3(tbl_Employee.Employ eeCode)|||Use it like a table in your select statement:

SELECT IsVarPay3NeedComments FROM dbo.uFN_PopulateCacheTable_VarPay3(tbl_Employee.Em ployeeCode)

So your statement would be something like:

UPDATE tbl_CacheEmployeeToProgram
SET IsVarPay3NeedComments = mytable.IsVarPay3NeedComments
FROM uFN_PopulateCacheTable_VarPay3(tbl_Employee.Employ eeCode) mytable
INNER JOIN tbl_CacheEmployeeToProgram
ON mytable.EmployeeCode = tbl_CacheEmployeeToProgram.EmployeeCode
INNER JOIN tbl_Employee
ON tbl_CacheEmployeeToProgram.EmployeeCode = tbl_Employee.EmployeeCode
INNER JOIN tbl_Program
ON tbl_Employee.EmployeeCode = tbl_Program.EmployeeCode

In order for that to work (in my statement anyway), you should return a table that is something like:
RETURNS TABLE (EmployeeCode INT, IsVarPay3NeedComments VARCHAR(100))|||and the WHERE statement again!!! DOH!

DB Objects stored in VSS

Way Way Way back, IIRC there was discussion that SQL Server 2005 would integrate into Source Safe in such a way, that you could version your objects to Source Safe.

Is this still there, or was it ever there? I know there are third party tools that can do it, but none of them seem to be updated to support SQL2005, and would would rather not use a third party tool.

So basically, short of managing SCRIPT files via Source Safe, can it be done?

No. As far as I know, the plan was to allow users to script any object to a file and manage the script in Visual Source Safe.|||

I think there are just too many issues with MS's VSS that it is better to direct the effort to an entity solely focused on this utility. I would not shy away from MS on this but in the meantime I advocate the use of a better alternative.

VSS will be fixed in due time but not as of yet.

DB Objects stored in VSS

Way Way Way back, IIRC there was discussion that SQL Server 2005 would integrate into Source Safe in such a way, that you could version your objects to Source Safe.

Is this still there, or was it ever there? I know there are third party tools that can do it, but none of them seem to be updated to support SQL2005, and would would rather not use a third party tool.

So basically, short of managing SCRIPT files via Source Safe, can it be done?

No. As far as I know, the plan was to allow users to script any object to a file and manage the script in Visual Source Safe.|||

I think there are just too many issues with MS's VSS that it is better to direct the effort to an entity solely focused on this utility. I would not shy away from MS on this but in the meantime I advocate the use of a better alternative.

VSS will be fixed in due time but not as of yet.

DB Objects stored in VSS

Way Way Way back, IIRC there was discussion that SQL Server 2005 would integrate into Source Safe in such a way, that you could version your objects to Source Safe.

Is this still there, or was it ever there? I know there are third party tools that can do it, but none of them seem to be updated to support SQL2005, and would would rather not use a third party tool.

So basically, short of managing SCRIPT files via Source Safe, can it be done?

No. As far as I know, the plan was to allow users to script any object to a file and manage the script in Visual Source Safe.|||

I think there are just too many issues with MS's VSS that it is better to direct the effort to an entity solely focused on this utility. I would not shy away from MS on this but in the meantime I advocate the use of a better alternative.

VSS will be fixed in due time but not as of yet.

DB is ReadOnly?

Hi all,
I am using DAO 3.6 to connect to a SQL Server db on a remote machine.
I can open and close the connection, read records, run stored procedures
but when I try to add a record I get "object or database is read only"
If I connect to the database using ADO I can add records.
Here is my code:
' ----
Dim wrk_DAO As DAO.Workspace
Dim cnn_DAO As DAO.Connection
Dim rec_DAO as DAO.Recordset
Set wrk_DAO = CreateWorkspace("MyWorkspace", "sa", "pw", dbUseODBC)
Set cnn_DAO = wrk_DAO.OpenConnection("MyConnection", dbDriverNoPrompt,
False, _
"ODBC;DATABASE=SQLTest;UID=sa;PWD=pw;DSN=odbc_test ")
Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic)
rec_DAO.AddNew '<<< ERROR occurs here
rec_DAO!Field1 = 1
rec_DAO!Field2 = "Test"
rec_DAO.Update
rec_DAO.Close
cnn_DAO.Close
' ----
If I use the same code but only read, it works fine.
What am I doing wrong?
Thanks.
kpg
"kpg" <ipost@.thereforeiam.com> wrote in message
news:%23Oremk8pEHA.2588@.TK2MSFTNGP12.phx.gbl...
> I am using DAO 3.6 to connect to a SQL Server db on a remote machine.
> I can open and close the connection, read records, run stored procedures
> but when I try to add a record I get "object or database is read only"
> If I connect to the database using ADO I can add records.
> Here is my code:
> ' ----
> Dim wrk_DAO As DAO.Workspace
> Dim cnn_DAO As DAO.Connection
> Dim rec_DAO as DAO.Recordset
> Set wrk_DAO = CreateWorkspace("MyWorkspace", "sa", "pw", dbUseODBC)
> Set cnn_DAO = wrk_DAO.OpenConnection("MyConnection", dbDriverNoPrompt,
> False, _
> "ODBC;DATABASE=SQLTest;UID=sa;PWD=pw;DSN=odbc_test ")
> Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic)
> rec_DAO.AddNew '<<< ERROR occurs here
> rec_DAO!Field1 = 1
> rec_DAO!Field2 = "Test"
> rec_DAO.Update
> rec_DAO.Close
> cnn_DAO.Close
> ' ----
> If I use the same code but only read, it works fine.
> What am I doing wrong?
It may not be anything you are doing "wrong", other than using a possibly
incompatible, old library to access SQL Server. DAO was written an intended
to be a library for Jet. The fact that your code works in ADO speaks to the
proper library to use...
Steve
|||"Steve Thompson" wrote
> It may not be anything you are doing "wrong", other than using a possibly
> incompatible, old library to access SQL Server.
<snip>
Yes, thanks for the reply. I solved my little problem however...
If I set the locking type to 'dbOptimisticValue' it works fine:
Re:
'--
Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic, _
dbExecDirect, dbOptimisticValue)
'--
I guess the default was a snapshot?
BTW: I would never really consider using DAO for a SQL Server DB,
I was mainly interested in benchmarking the difference between Access, SQL
Server, ADO and DAO, for use in a management proposal.
I use a SQL Server and an Access db located on the same machine on my LAN.
This way the netword has to be involved in all IO.
My test is not very rigid from a scientific standpoint and the results are
not very
suprising, but here the are:
Add 1000
Add x100
Read
SP
Delete
Total
ADO ACCESS
0.07
1.66
0.03
0.03
0.02
1.81
DAO ACCESS
0.06
1.2
0.03
0.02
0.01
1.32
ADO SQL
1.54
1.63
0.03
0.03
0.02
3.25
DAO SQL
0.66
2.67
0.05
0.04
0.03
3.45
times are in seconds.
Add 1000: adds 1000 small records by opening the db once, looping, then
closing.
Add x100: adds the same recodes bu7t opens and closes the db for each record
(100 times)
Read: reads the 1100 records by opening once, reading in a while loop,
closing.
SP: run a stored procedure instead of a SELECT Query, otherwise the same as
Read
Delete: execute a DELETE FORM Table command.
kpg
In theory, there is no difference between theory and practice.
But, in practice, there is - Jan L.A. van de Snepscheut
|||I'm glad you found that -- the default is snapshot from what I remember (and
that is digging back a while).
BTW, speed is only one factor, I would be more concerned about stability,
portability and support of your code. While DAO is fast (and it has always
been known for speed), DAO is not the way to go for a strategic software
development with SQL Server. You can achieve higher performance in SQL
Server by taking advantage of VIEWS and Stored Procedures (for data
manipulation).
Steve
"kpg" <ipost@.thereforeiam.com> wrote in message
news:upbmBF$pEHA.596@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Steve Thompson" wrote
possibly
> <snip>
> Yes, thanks for the reply. I solved my little problem however...
> If I set the locking type to 'dbOptimisticValue' it works fine:
> Re:
> '--
> Set rec_DAO = cnn_DAO.OpenRecordset(strSQL, dbOpenDynamic, _
> dbExecDirect, dbOptimisticValue)
> '--
> I guess the default was a snapshot?
> BTW: I would never really consider using DAO for a SQL Server DB,
> I was mainly interested in benchmarking the difference between Access, SQL
> Server, ADO and DAO, for use in a management proposal.
> I use a SQL Server and an Access db located on the same machine on my LAN.
> This way the netword has to be involved in all IO.
> My test is not very rigid from a scientific standpoint and the results are
> not very
> suprising, but here the are:
>
> Add 1000
> Add x100
> Read
> SP
> Delete
> Total
> ADO ACCESS
> 0.07
> 1.66
> 0.03
> 0.03
> 0.02
> 1.81
> DAO ACCESS
> 0.06
> 1.2
> 0.03
> 0.02
> 0.01
> 1.32
> ADO SQL
> 1.54
> 1.63
> 0.03
> 0.03
> 0.02
> 3.25
> DAO SQL
> 0.66
> 2.67
> 0.05
> 0.04
> 0.03
> 3.45
>
> times are in seconds.
> Add 1000: adds 1000 small records by opening the db once, looping, then
> closing.
> Add x100: adds the same recodes bu7t opens and closes the db for each
record
> (100 times)
> Read: reads the 1100 records by opening once, reading in a while loop,
> closing.
> SP: run a stored procedure instead of a SELECT Query, otherwise the same
as
> Read
> Delete: execute a DELETE FORM Table command.
> --
> kpg
> In theory, there is no difference between theory and practice.
> But, in practice, there is - Jan L.A. van de Snepscheut
>
>

Db Error 10039

When executing a Stored Procedure i am getting error 10039
"Attempt to convert data stopped by syntax error in source field"
intermittently
Can anyone tell me why this happens or resolution for this issue
--
vivek
Chennai1. Are you using a format file? If so, all of the variables have to be
of the type VARCHAR no matter what their original type was. After the
import they are converted back.
2. Check the source field when the error occurs. There has got to be an
error in the data at least to my way of thinking.
Kelly
"rvivekraj" <rvivekraj@.hotmail.com> wrote in message
news:rvivekraj@.hotmail.com:
> When executing a Stored Procedure i am getting error 10039
> "Attempt to convert data stopped by syntax error in source field"
> intermittently
> Can anyone tell me why this happens or resolution for this issue
> --
> vivek
> Chennai|||Hi
Looks like a DB library error. What version of SQL Server are you using, and
what version of DB library are you using? DB Library has not beed updated
since SQL Server 6.5 and does not understand the new datatypes supplied in
SQL 7.0 and 2000.
Regards
Mike
"rvivekraj" wrote:

> When executing a Stored Procedure i am getting error 10039
> "Attempt to convert data stopped by syntax error in source field"
> intermittently
> Can anyone tell me why this happens or resolution for this issue
> --
> vivek
> Chennai