In SQL Server 2000, I would like to attach a Database that is the file of the Database on another system. I made a copy of my Client's Database at his site. Now I would like to attach it to my server and then copy the data from my Client's DB to mine.
The only problem is they have the same file names, so when I try to attach the copy I get an error message. Is there any way to change the name of the data file and log file that a database points to so that I can attach it to my Server?
Thanks in advance for your help!Look up RESTORE DATABASE in Books Online. You need to use the WITH MOVE option.
RESTORE DATABASE database
WITH
MOVE 'logical data file' TO 'somenewfile.mdf',
MOVE 'logical log file' TO 'somenewfile.ldf'
You can find the name of the files by using: RESTORE FILELISTONLY FROM DISK = 'file.bak'
Showing posts with label client. Show all posts
Showing posts with label client. Show all posts
Thursday, March 29, 2012
Attach db w/no log file
I had a client run out of disk space because their transaction log grew too
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:
> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >I had a client run out of disk space because their transaction log grew too
> > large. They accidentally deleted the tran log (didn't go to Recycle bin
> > because of size), and their last good backup was from 3 weeks ago. If I
> > try
> > to attach the database either through Enterprise Manager or QA it tells me
> > the physical file name for the ldf is incorrect. Is there a way to get
> > around this error? Can I restore the old backup to a different name and
> > use
> > its ldf file? Where is the info stored that tells the system what log
> > file
> > is associated with the database? Can I modify that somehow? Thanks!
>
>|||You can't add an unmacthing transaction log file to a database. The purpose of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup that the lsn number for
the log records matches with what to expect for the database. If restore isn't a viable option, I
suggest you let MS Support help you. They might have a way to get the database back, in some shape.
And, make sure you give access to the system to people who might do these operations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew too
>> > large. They accidentally deleted the tran log (didn't go to Recycle bin
>> > because of size), and their last good backup was from 3 weeks ago. If I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew
>> >too
>> > large. They accidentally deleted the tran log (didn't go to Recycle
>> > bin
>> > because of size), and their last good backup was from 3 weeks ago. If
>> > I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells
>> > me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name
>> > and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode and
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:
> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> > Thanks for the reply. I just tried this and it is still telling me that
> > the
> > ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> > would that potentially mess up my database? Thanks.
> >
> > "Michael C#" wrote:
> >
> >> Look up sp_attach_single_file_db in BOL
> >>
> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >> >I had a client run out of disk space because their transaction log grew
> >> >too
> >> > large. They accidentally deleted the tran log (didn't go to Recycle
> >> > bin
> >> > because of size), and their last good backup was from 3 weeks ago. If
> >> > I
> >> > try
> >> > to attach the database either through Enterprise Manager or QA it tells
> >> > me
> >> > the physical file name for the ldf is incorrect. Is there a way to get
> >> > around this error? Can I restore the old backup to a different name
> >> > and
> >> > use
> >> > its ldf file? Where is the info stored that tells the system what log
> >> > file
> >> > is associated with the database? Can I modify that somehow? Thanks!
> >>
> >>
> >>
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>> Check and see if it actually attached. If I recall correctly,
>> sp_attach_single_file_db will give a warning message when it doesn't find
>> the log file, but it should still attach successfully.
>>
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>> > Thanks for the reply. I just tried this and it is still telling me
>> > that
>> > the
>> > ldf file 'may be incorrect.' Can I reattach using the old log file?
>> > Or
>> > would that potentially mess up my database? Thanks.
>> >
>> > "Michael C#" wrote:
>> >
>> >> Look up sp_attach_single_file_db in BOL
>> >>
>> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >> >I had a client run out of disk space because their transaction log
>> >> >grew
>> >> >too
>> >> > large. They accidentally deleted the tran log (didn't go to Recycle
>> >> > bin
>> >> > because of size), and their last good backup was from 3 weeks ago.
>> >> > If
>> >> > I
>> >> > try
>> >> > to attach the database either through Enterprise Manager or QA it
>> >> > tells
>> >> > me
>> >> > the physical file name for the ldf is incorrect. Is there a way to
>> >> > get
>> >> > around this error? Can I restore the old backup to a different name
>> >> > and
>> >> > use
>> >> > its ldf file? Where is the info stored that tells the system what
>> >> > log
>> >> > file
>> >> > is associated with the database? Can I modify that somehow?
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:
> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >I had a client run out of disk space because their transaction log grew too
> > large. They accidentally deleted the tran log (didn't go to Recycle bin
> > because of size), and their last good backup was from 3 weeks ago. If I
> > try
> > to attach the database either through Enterprise Manager or QA it tells me
> > the physical file name for the ldf is incorrect. Is there a way to get
> > around this error? Can I restore the old backup to a different name and
> > use
> > its ldf file? Where is the info stored that tells the system what log
> > file
> > is associated with the database? Can I modify that somehow? Thanks!
>
>|||You can't add an unmacthing transaction log file to a database. The purpose of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup that the lsn number for
the log records matches with what to expect for the database. If restore isn't a viable option, I
suggest you let MS Support help you. They might have a way to get the database back, in some shape.
And, make sure you give access to the system to people who might do these operations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew too
>> > large. They accidentally deleted the tran log (didn't go to Recycle bin
>> > because of size), and their last good backup was from 3 weeks ago. If I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew
>> >too
>> > large. They accidentally deleted the tran log (didn't go to Recycle
>> > bin
>> > because of size), and their last good backup was from 3 weeks ago. If
>> > I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells
>> > me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name
>> > and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode and
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:
> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> > Thanks for the reply. I just tried this and it is still telling me that
> > the
> > ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> > would that potentially mess up my database? Thanks.
> >
> > "Michael C#" wrote:
> >
> >> Look up sp_attach_single_file_db in BOL
> >>
> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >> >I had a client run out of disk space because their transaction log grew
> >> >too
> >> > large. They accidentally deleted the tran log (didn't go to Recycle
> >> > bin
> >> > because of size), and their last good backup was from 3 weeks ago. If
> >> > I
> >> > try
> >> > to attach the database either through Enterprise Manager or QA it tells
> >> > me
> >> > the physical file name for the ldf is incorrect. Is there a way to get
> >> > around this error? Can I restore the old backup to a different name
> >> > and
> >> > use
> >> > its ldf file? Where is the info stored that tells the system what log
> >> > file
> >> > is associated with the database? Can I modify that somehow? Thanks!
> >>
> >>
> >>
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>> Check and see if it actually attached. If I recall correctly,
>> sp_attach_single_file_db will give a warning message when it doesn't find
>> the log file, but it should still attach successfully.
>>
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>> > Thanks for the reply. I just tried this and it is still telling me
>> > that
>> > the
>> > ldf file 'may be incorrect.' Can I reattach using the old log file?
>> > Or
>> > would that potentially mess up my database? Thanks.
>> >
>> > "Michael C#" wrote:
>> >
>> >> Look up sp_attach_single_file_db in BOL
>> >>
>> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >> >I had a client run out of disk space because their transaction log
>> >> >grew
>> >> >too
>> >> > large. They accidentally deleted the tran log (didn't go to Recycle
>> >> > bin
>> >> > because of size), and their last good backup was from 3 weeks ago.
>> >> > If
>> >> > I
>> >> > try
>> >> > to attach the database either through Enterprise Manager or QA it
>> >> > tells
>> >> > me
>> >> > the physical file name for the ldf is incorrect. Is there a way to
>> >> > get
>> >> > around this error? Can I restore the old backup to a different name
>> >> > and
>> >> > use
>> >> > its ldf file? Where is the info stored that tells the system what
>> >> > log
>> >> > file
>> >> > is associated with the database? Can I modify that somehow?
>> >> > Thanks!
>> >>
>> >>
>> >>
>>
Attach db w/no log file
I had a client run out of disk space because their transaction log grew too
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:
> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>
>|||You can't add an unmacthing transaction log file to a database. The purpose
of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup
that the lsn number for
the log records matches with what to expect for the database. If restore isn
't a viable option, I
suggest you let MS Support help you. They might have a way to get the databa
se back, in some shape.
And, make sure you give access to the system to people who might do these op
erations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. I just tried this and it is still telling me that t
he
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode an
d
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:
> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...[vbcol=seagreen]
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:
> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>
>|||You can't add an unmacthing transaction log file to a database. The purpose
of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup
that the lsn number for
the log records matches with what to expect for the database. If restore isn
't a viable option, I
suggest you let MS Support help you. They might have a way to get the databa
se back, in some shape.
And, make sure you give access to the system to people who might do these op
erations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. I just tried this and it is still telling me that t
he
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode an
d
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:
> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...[vbcol=seagreen]
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>
Thursday, March 22, 2012
Asynchrous query
I have a query that because the database is across the country it takes time
to bring a large amount of data to the client executing a command. I was
wondering if there was a way to execute an asychronus command? Ideally I
would like to get periodic feedback on the progress. I am sure that most of
the delay is network related but it is hard to tell how close to finishing a
command is by just executing the ExecuteScalar from a SQL command.
Thank you for your suggestions.
KevinOn Sat, 8 Jul 2006 16:25:01 -0700, Kevin Burton
<KevinBurton@.discussions.microsoft.com> wrote:
>I have a query that because the database is across the country it takes tim
e
>to bring a large amount of data to the client executing a command. I was
>wondering if there was a way to execute an asychronus command? Ideally I
>would like to get periodic feedback on the progress. I am sure that most of
>the delay is network related but it is hard to tell how close to finishing
a
>command is by just executing the ExecuteScalar from a SQL command.
>Thank you for your suggestions.
Easiest way is probably to create the extra thread in your app code.
Don't know any standard way of monitoring progress.
If you program down at the ODBC/dblib level (is that still even
possible on SQL2005?), with server-side cursors, then you can monitor
progress. But it's a lot of work! Hmm, I suppose, in that extra
thread of yours, you could use server-side cursors and monitor it even
at the OLEDB level!
What language is your client written in?
(Does OLEDB support async queries? I don't know, I've been writing
nothing but TSQL for too long now!)
Josh|||ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExec
uteReader, etc. Doesn't
seem to be a BeginExecuteScalar, though.
But you should carefully consider in what way you want the execution to be a
synchronous.
If you use any of above mentioned methods, you cannot read off of the result
s until the whole set
has been passed to the client.
Using a regular ExecuteReader, you can read the results as they are sent to
the client. You don't
have to wait for the whole set until you can present data to the end user. N
ote that SQL Server can
use worktables and don't send the result until it is done, depending on the
execution plan. A FAST
hint can be useful, but that can increase the total query cost (sometimes ra
dically).
Above doesn't matter for a ExecuetScalar, of course, but it can be important
when reading a number
of rows.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>I have a query that because the database is across the country it takes tim
e
> to bring a large amount of data to the client executing a command. I was
> wondering if there was a way to execute an asychronus command? Ideally I
> would like to get periodic feedback on the progress. I am sure that most o
f
> the delay is network related but it is hard to tell how close to finishing
a
> command is by just executing the ExecuteScalar from a SQL command.
> Thank you for your suggestions.
> Kevin|||Thank you for the tip. My client is written in .NET. I guess I was hoping
for some access to the network as I indicated before the physical database
resides a long way away so I am suspecting that the actual query is probably
quick enough it is just tranferring the data across the network that takes
the time. The thing of it is that I what the code somehow to adjust for the
long distance data transfer. I can't do that as far as I can see without
knowing about the network being used to transfer the data. Anyway, thanks
again. If the bottle neck does in fact prove to be the SQL query then I will
look into your suggestions.
Kevin|||I started to look at these calls and it brought up another question. The dat
a
that is being read is a column with XML data. It is typed 'text' and the
server is running 2000 so I don't have an option to type the column as XML.
I
would however like to use ExecuteXmlReader but I get an exception indicatin
g
that the data must be XML. How do I convince the client that it is XML data?
My current query looks like:
SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
OrderNumber='yyy'
It seems silly to read in a large chunk of "string" data, pass it to a
MemoryStream, build an XmlReader, and then use that to build an XML document
(specifically an XPath document because I want to do some XPath searches on
the data). If I can get an XmlReader directly from the SQL query via
ExecuteXmlReader it seems more efficient. Is this possible with the data
structured as I indicated?
Thank you.
Kevin
"Tibor Karaszi" wrote:
> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndEx
ecuteReader, etc. Doesn't
> seem to be a BeginExecuteScalar, though.
> But you should carefully consider in what way you want the execution to be
asynchronous.
> If you use any of above mentioned methods, you cannot read off of the resu
lts until the whole set
> has been passed to the client.
> Using a regular ExecuteReader, you can read the results as they are sent t
o the client. You don't
> have to wait for the whole set until you can present data to the end user.
Note that SQL Server can
> use worktables and don't send the result until it is done, depending on th
e execution plan. A FAST
> hint can be useful, but that can increase the total query cost (sometimes
radically).
> Above doesn't matter for a ExecuetScalar, of course, but it can be importa
nt when reading a number
> of rows.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>|||That is a bit too high in the API stack for me to be able to comment on. Per
haps FOR XML in the
query would allow you to type it as XML? I suggest you post this to an ADO.N
ET group, unless someone
jump in here with more specific recommendations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:9342A66A-B585-4890-8E8B-719114510350@.microsoft.com...[vbcol=seagreen]
>I started to look at these calls and it brought up another question. The da
ta
> that is being read is a column with XML data. It is typed 'text' and the
> server is running 2000 so I don't have an option to type the column as XML
. I
> would however like to use ExecuteXmlReader but I get an exception indicat
ing
> that the data must be XML. How do I convince the client that it is XML dat
a?
> My current query looks like:
> SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
> OrderNumber='yyy'
> It seems silly to read in a large chunk of "string" data, pass it to a
> MemoryStream, build an XmlReader, and then use that to build an XML docume
nt
> (specifically an XPath document because I want to do some XPath searches o
n
> the data). If I can get an XmlReader directly from the SQL query via
> ExecuteXmlReader it seems more efficient. Is this possible with the data
> structured as I indicated?
> Thank you.
> Kevin
> "Tibor Karaszi" wrote:
>sql
to bring a large amount of data to the client executing a command. I was
wondering if there was a way to execute an asychronus command? Ideally I
would like to get periodic feedback on the progress. I am sure that most of
the delay is network related but it is hard to tell how close to finishing a
command is by just executing the ExecuteScalar from a SQL command.
Thank you for your suggestions.
KevinOn Sat, 8 Jul 2006 16:25:01 -0700, Kevin Burton
<KevinBurton@.discussions.microsoft.com> wrote:
>I have a query that because the database is across the country it takes tim
e
>to bring a large amount of data to the client executing a command. I was
>wondering if there was a way to execute an asychronus command? Ideally I
>would like to get periodic feedback on the progress. I am sure that most of
>the delay is network related but it is hard to tell how close to finishing
a
>command is by just executing the ExecuteScalar from a SQL command.
>Thank you for your suggestions.
Easiest way is probably to create the extra thread in your app code.
Don't know any standard way of monitoring progress.
If you program down at the ODBC/dblib level (is that still even
possible on SQL2005?), with server-side cursors, then you can monitor
progress. But it's a lot of work! Hmm, I suppose, in that extra
thread of yours, you could use server-side cursors and monitor it even
at the OLEDB level!
What language is your client written in?
(Does OLEDB support async queries? I don't know, I've been writing
nothing but TSQL for too long now!)
Josh|||ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExec
uteReader, etc. Doesn't
seem to be a BeginExecuteScalar, though.
But you should carefully consider in what way you want the execution to be a
synchronous.
If you use any of above mentioned methods, you cannot read off of the result
s until the whole set
has been passed to the client.
Using a regular ExecuteReader, you can read the results as they are sent to
the client. You don't
have to wait for the whole set until you can present data to the end user. N
ote that SQL Server can
use worktables and don't send the result until it is done, depending on the
execution plan. A FAST
hint can be useful, but that can increase the total query cost (sometimes ra
dically).
Above doesn't matter for a ExecuetScalar, of course, but it can be important
when reading a number
of rows.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>I have a query that because the database is across the country it takes tim
e
> to bring a large amount of data to the client executing a command. I was
> wondering if there was a way to execute an asychronus command? Ideally I
> would like to get periodic feedback on the progress. I am sure that most o
f
> the delay is network related but it is hard to tell how close to finishing
a
> command is by just executing the ExecuteScalar from a SQL command.
> Thank you for your suggestions.
> Kevin|||Thank you for the tip. My client is written in .NET. I guess I was hoping
for some access to the network as I indicated before the physical database
resides a long way away so I am suspecting that the actual query is probably
quick enough it is just tranferring the data across the network that takes
the time. The thing of it is that I what the code somehow to adjust for the
long distance data transfer. I can't do that as far as I can see without
knowing about the network being used to transfer the data. Anyway, thanks
again. If the bottle neck does in fact prove to be the SQL query then I will
look into your suggestions.
Kevin|||I started to look at these calls and it brought up another question. The dat
a
that is being read is a column with XML data. It is typed 'text' and the
server is running 2000 so I don't have an option to type the column as XML.
I
would however like to use ExecuteXmlReader but I get an exception indicatin
g
that the data must be XML. How do I convince the client that it is XML data?
My current query looks like:
SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
OrderNumber='yyy'
It seems silly to read in a large chunk of "string" data, pass it to a
MemoryStream, build an XmlReader, and then use that to build an XML document
(specifically an XPath document because I want to do some XPath searches on
the data). If I can get an XmlReader directly from the SQL query via
ExecuteXmlReader it seems more efficient. Is this possible with the data
structured as I indicated?
Thank you.
Kevin
"Tibor Karaszi" wrote:
> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndEx
ecuteReader, etc. Doesn't
> seem to be a BeginExecuteScalar, though.
> But you should carefully consider in what way you want the execution to be
asynchronous.
> If you use any of above mentioned methods, you cannot read off of the resu
lts until the whole set
> has been passed to the client.
> Using a regular ExecuteReader, you can read the results as they are sent t
o the client. You don't
> have to wait for the whole set until you can present data to the end user.
Note that SQL Server can
> use worktables and don't send the result until it is done, depending on th
e execution plan. A FAST
> hint can be useful, but that can increase the total query cost (sometimes
radically).
> Above doesn't matter for a ExecuetScalar, of course, but it can be importa
nt when reading a number
> of rows.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>|||That is a bit too high in the API stack for me to be able to comment on. Per
haps FOR XML in the
query would allow you to type it as XML? I suggest you post this to an ADO.N
ET group, unless someone
jump in here with more specific recommendations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:9342A66A-B585-4890-8E8B-719114510350@.microsoft.com...[vbcol=seagreen]
>I started to look at these calls and it brought up another question. The da
ta
> that is being read is a column with XML data. It is typed 'text' and the
> server is running 2000 so I don't have an option to type the column as XML
. I
> would however like to use ExecuteXmlReader but I get an exception indicat
ing
> that the data must be XML. How do I convince the client that it is XML dat
a?
> My current query looks like:
> SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
> OrderNumber='yyy'
> It seems silly to read in a large chunk of "string" data, pass it to a
> MemoryStream, build an XmlReader, and then use that to build an XML docume
nt
> (specifically an XPath document because I want to do some XPath searches o
n
> the data). If I can get an XmlReader directly from the SQL query via
> ExecuteXmlReader it seems more efficient. Is this possible with the data
> structured as I indicated?
> Thank you.
> Kevin
> "Tibor Karaszi" wrote:
>sql
Asynchrous query
I have a query that because the database is across the country it takes time
to bring a large amount of data to the client executing a command. I was
wondering if there was a way to execute an asychronus command? Ideally I
would like to get periodic feedback on the progress. I am sure that most of
the delay is network related but it is hard to tell how close to finishing a
command is by just executing the ExecuteScalar from a SQL command.
Thank you for your suggestions.
KevinOn Sat, 8 Jul 2006 16:25:01 -0700, Kevin Burton
<KevinBurton@.discussions.microsoft.com> wrote:
>I have a query that because the database is across the country it takes time
>to bring a large amount of data to the client executing a command. I was
>wondering if there was a way to execute an asychronus command? Ideally I
>would like to get periodic feedback on the progress. I am sure that most of
>the delay is network related but it is hard to tell how close to finishing a
>command is by just executing the ExecuteScalar from a SQL command.
>Thank you for your suggestions.
Easiest way is probably to create the extra thread in your app code.
Don't know any standard way of monitoring progress.
If you program down at the ODBC/dblib level (is that still even
possible on SQL2005?), with server-side cursors, then you can monitor
progress. But it's a lot of work! Hmm, I suppose, in that extra
thread of yours, you could use server-side cursors and monitor it even
at the OLEDB level!
What language is your client written in?
(Does OLEDB support async queries? I don't know, I've been writing
nothing but TSQL for too long now!)
Josh|||ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
seem to be a BeginExecuteScalar, though.
But you should carefully consider in what way you want the execution to be asynchronous.
If you use any of above mentioned methods, you cannot read off of the results until the whole set
has been passed to the client.
Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
have to wait for the whole set until you can present data to the end user. Note that SQL Server can
use worktables and don't send the result until it is done, depending on the execution plan. A FAST
hint can be useful, but that can increase the total query cost (sometimes radically).
Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
of rows.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>I have a query that because the database is across the country it takes time
> to bring a large amount of data to the client executing a command. I was
> wondering if there was a way to execute an asychronus command? Ideally I
> would like to get periodic feedback on the progress. I am sure that most of
> the delay is network related but it is hard to tell how close to finishing a
> command is by just executing the ExecuteScalar from a SQL command.
> Thank you for your suggestions.
> Kevin|||Thank you for the tip. My client is written in .NET. I guess I was hoping
for some access to the network as I indicated before the physical database
resides a long way away so I am suspecting that the actual query is probably
quick enough it is just tranferring the data across the network that takes
the time. The thing of it is that I what the code somehow to adjust for the
long distance data transfer. I can't do that as far as I can see without
knowing about the network being used to transfer the data. Anyway, thanks
again. If the bottle neck does in fact prove to be the SQL query then I will
look into your suggestions.
Kevin|||I started to look at these calls and it brought up another question. The data
that is being read is a column with XML data. It is typed 'text' and the
server is running 2000 so I don't have an option to type the column as XML. I
would however like to use ExecuteXmlReader but I get an exception indicating
that the data must be XML. How do I convince the client that it is XML data?
My current query looks like:
SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
OrderNumber='yyy'
It seems silly to read in a large chunk of "string" data, pass it to a
MemoryStream, build an XmlReader, and then use that to build an XML document
(specifically an XPath document because I want to do some XPath searches on
the data). If I can get an XmlReader directly from the SQL query via
ExecuteXmlReader it seems more efficient. Is this possible with the data
structured as I indicated?
Thank you.
Kevin
"Tibor Karaszi" wrote:
> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
> seem to be a BeginExecuteScalar, though.
> But you should carefully consider in what way you want the execution to be asynchronous.
> If you use any of above mentioned methods, you cannot read off of the results until the whole set
> has been passed to the client.
> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
> have to wait for the whole set until you can present data to the end user. Note that SQL Server can
> use worktables and don't send the result until it is done, depending on the execution plan. A FAST
> hint can be useful, but that can increase the total query cost (sometimes radically).
> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
> of rows.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
> >I have a query that because the database is across the country it takes time
> > to bring a large amount of data to the client executing a command. I was
> > wondering if there was a way to execute an asychronus command? Ideally I
> > would like to get periodic feedback on the progress. I am sure that most of
> > the delay is network related but it is hard to tell how close to finishing a
> > command is by just executing the ExecuteScalar from a SQL command.
> >
> > Thank you for your suggestions.
> >
> > Kevin
>|||That is a bit too high in the API stack for me to be able to comment on. Perhaps FOR XML in the
query would allow you to type it as XML? I suggest you post this to an ADO.NET group, unless someone
jump in here with more specific recommendations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:9342A66A-B585-4890-8E8B-719114510350@.microsoft.com...
>I started to look at these calls and it brought up another question. The data
> that is being read is a column with XML data. It is typed 'text' and the
> server is running 2000 so I don't have an option to type the column as XML. I
> would however like to use ExecuteXmlReader but I get an exception indicating
> that the data must be XML. How do I convince the client that it is XML data?
> My current query looks like:
> SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
> OrderNumber='yyy'
> It seems silly to read in a large chunk of "string" data, pass it to a
> MemoryStream, build an XmlReader, and then use that to build an XML document
> (specifically an XPath document because I want to do some XPath searches on
> the data). If I can get an XmlReader directly from the SQL query via
> ExecuteXmlReader it seems more efficient. Is this possible with the data
> structured as I indicated?
> Thank you.
> Kevin
> "Tibor Karaszi" wrote:
>> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc.
>> Doesn't
>> seem to be a BeginExecuteScalar, though.
>> But you should carefully consider in what way you want the execution to be asynchronous.
>> If you use any of above mentioned methods, you cannot read off of the results until the whole set
>> has been passed to the client.
>> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
>> have to wait for the whole set until you can present data to the end user. Note that SQL Server
>> can
>> use worktables and don't send the result until it is done, depending on the execution plan. A
>> FAST
>> hint can be useful, but that can increase the total query cost (sometimes radically).
>> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a
>> number
>> of rows.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
>> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>> >I have a query that because the database is across the country it takes time
>> > to bring a large amount of data to the client executing a command. I was
>> > wondering if there was a way to execute an asychronus command? Ideally I
>> > would like to get periodic feedback on the progress. I am sure that most of
>> > the delay is network related but it is hard to tell how close to finishing a
>> > command is by just executing the ExecuteScalar from a SQL command.
>> >
>> > Thank you for your suggestions.
>> >
>> > Kevin
>>
to bring a large amount of data to the client executing a command. I was
wondering if there was a way to execute an asychronus command? Ideally I
would like to get periodic feedback on the progress. I am sure that most of
the delay is network related but it is hard to tell how close to finishing a
command is by just executing the ExecuteScalar from a SQL command.
Thank you for your suggestions.
KevinOn Sat, 8 Jul 2006 16:25:01 -0700, Kevin Burton
<KevinBurton@.discussions.microsoft.com> wrote:
>I have a query that because the database is across the country it takes time
>to bring a large amount of data to the client executing a command. I was
>wondering if there was a way to execute an asychronus command? Ideally I
>would like to get periodic feedback on the progress. I am sure that most of
>the delay is network related but it is hard to tell how close to finishing a
>command is by just executing the ExecuteScalar from a SQL command.
>Thank you for your suggestions.
Easiest way is probably to create the extra thread in your app code.
Don't know any standard way of monitoring progress.
If you program down at the ODBC/dblib level (is that still even
possible on SQL2005?), with server-side cursors, then you can monitor
progress. But it's a lot of work! Hmm, I suppose, in that extra
thread of yours, you could use server-side cursors and monitor it even
at the OLEDB level!
What language is your client written in?
(Does OLEDB support async queries? I don't know, I've been writing
nothing but TSQL for too long now!)
Josh|||ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
seem to be a BeginExecuteScalar, though.
But you should carefully consider in what way you want the execution to be asynchronous.
If you use any of above mentioned methods, you cannot read off of the results until the whole set
has been passed to the client.
Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
have to wait for the whole set until you can present data to the end user. Note that SQL Server can
use worktables and don't send the result until it is done, depending on the execution plan. A FAST
hint can be useful, but that can increase the total query cost (sometimes radically).
Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
of rows.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>I have a query that because the database is across the country it takes time
> to bring a large amount of data to the client executing a command. I was
> wondering if there was a way to execute an asychronus command? Ideally I
> would like to get periodic feedback on the progress. I am sure that most of
> the delay is network related but it is hard to tell how close to finishing a
> command is by just executing the ExecuteScalar from a SQL command.
> Thank you for your suggestions.
> Kevin|||Thank you for the tip. My client is written in .NET. I guess I was hoping
for some access to the network as I indicated before the physical database
resides a long way away so I am suspecting that the actual query is probably
quick enough it is just tranferring the data across the network that takes
the time. The thing of it is that I what the code somehow to adjust for the
long distance data transfer. I can't do that as far as I can see without
knowing about the network being used to transfer the data. Anyway, thanks
again. If the bottle neck does in fact prove to be the SQL query then I will
look into your suggestions.
Kevin|||I started to look at these calls and it brought up another question. The data
that is being read is a column with XML data. It is typed 'text' and the
server is running 2000 so I don't have an option to type the column as XML. I
would however like to use ExecuteXmlReader but I get an exception indicating
that the data must be XML. How do I convince the client that it is XML data?
My current query looks like:
SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
OrderNumber='yyy'
It seems silly to read in a large chunk of "string" data, pass it to a
MemoryStream, build an XmlReader, and then use that to build an XML document
(specifically an XPath document because I want to do some XPath searches on
the data). If I can get an XmlReader directly from the SQL query via
ExecuteXmlReader it seems more efficient. Is this possible with the data
structured as I indicated?
Thank you.
Kevin
"Tibor Karaszi" wrote:
> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc. Doesn't
> seem to be a BeginExecuteScalar, though.
> But you should carefully consider in what way you want the execution to be asynchronous.
> If you use any of above mentioned methods, you cannot read off of the results until the whole set
> has been passed to the client.
> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
> have to wait for the whole set until you can present data to the end user. Note that SQL Server can
> use worktables and don't send the result until it is done, depending on the execution plan. A FAST
> hint can be useful, but that can increase the total query cost (sometimes radically).
> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a number
> of rows.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
> >I have a query that because the database is across the country it takes time
> > to bring a large amount of data to the client executing a command. I was
> > wondering if there was a way to execute an asychronus command? Ideally I
> > would like to get periodic feedback on the progress. I am sure that most of
> > the delay is network related but it is hard to tell how close to finishing a
> > command is by just executing the ExecuteScalar from a SQL command.
> >
> > Thank you for your suggestions.
> >
> > Kevin
>|||That is a bit too high in the API stack for me to be able to comment on. Perhaps FOR XML in the
query would allow you to type it as XML? I suggest you post this to an ADO.NET group, unless someone
jump in here with more specific recommendations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
news:9342A66A-B585-4890-8E8B-719114510350@.microsoft.com...
>I started to look at these calls and it brought up another question. The data
> that is being read is a column with XML data. It is typed 'text' and the
> server is running 2000 so I don't have an option to type the column as XML. I
> would however like to use ExecuteXmlReader but I get an exception indicating
> that the data must be XML. How do I convince the client that it is XML data?
> My current query looks like:
> SELECT XMLSent FROM dbo.OutboundTable WHERE documentTransactionID=xxx AND
> OrderNumber='yyy'
> It seems silly to read in a large chunk of "string" data, pass it to a
> MemoryStream, build an XmlReader, and then use that to build an XML document
> (specifically an XPath document because I want to do some XPath searches on
> the data). If I can get an XmlReader directly from the SQL query via
> ExecuteXmlReader it seems more efficient. Is this possible with the data
> structured as I indicated?
> Thank you.
> Kevin
> "Tibor Karaszi" wrote:
>> ADO.NET 2.0 has asynchronous operations, like BeginExecuteReader and EndExecuteReader, etc.
>> Doesn't
>> seem to be a BeginExecuteScalar, though.
>> But you should carefully consider in what way you want the execution to be asynchronous.
>> If you use any of above mentioned methods, you cannot read off of the results until the whole set
>> has been passed to the client.
>> Using a regular ExecuteReader, you can read the results as they are sent to the client. You don't
>> have to wait for the whole set until you can present data to the end user. Note that SQL Server
>> can
>> use worktables and don't send the result until it is done, depending on the execution plan. A
>> FAST
>> hint can be useful, but that can increase the total query cost (sometimes radically).
>> Above doesn't matter for a ExecuetScalar, of course, but it can be important when reading a
>> number
>> of rows.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Kevin Burton" <KevinBurton@.discussions.microsoft.com> wrote in message
>> news:1417C3D0-681F-4DC0-9F4F-38DB3C2F8DCE@.microsoft.com...
>> >I have a query that because the database is across the country it takes time
>> > to bring a large amount of data to the client executing a command. I was
>> > wondering if there was a way to execute an asychronus command? Ideally I
>> > would like to get periodic feedback on the progress. I am sure that most of
>> > the delay is network related but it is hard to tell how close to finishing a
>> > command is by just executing the ExecuteScalar from a SQL command.
>> >
>> > Thank you for your suggestions.
>> >
>> > Kevin
>>
ASYNC_NETWORK_IO issue
I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?
SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.
Thanks
-Ashis
Hi, Ashis,
Did you encounter this problem during replication? If not, can you please post your question to "SQL Server Database Engine " or "Transact SQL" alias? That way, you have a better chance of getting an answer.
Thanks,
Zhiqiang Feng
Tuesday, March 20, 2012
Associating Report Viewer (without UI) with Data Set
Hello all - I am looking to use the ReportViewer Control to automatically print out some client based reports.
I started with the "Print a report from a console app" sample from http://www.gotreportviewer.com/.
I have managed to convert an existing server based report from an RDL to an RDLC, and added it to my Console Project with VB.NET. In addition, I created a new Data Set within this project that returns the data from the original stored procedure that was used for the RDL file. I believe I have also successfully passed in the correct parameters to the sproc, but now I need to associate this new Data Set with the RDLC report. The RDLC has a simple table control that has a few fields from the new Data Set. When the report is rendered, I receive an exception indicating the following:
{"A data source instance has not been supplied for the data source "Production_stpProductionByDay"."}
It would seem that I need to programmatically associate my Data Set with the report, but I cannot seem to figure out the correct syntax.
Here is my Run method:
m_PhaseID = 1
m_ShiftID = 1
m_DateTime = Now()
Dim report As LocalReport = New LocalReport()
Dim parReport(2) As ReportParameter
Dim parDay As New ReportParameter("Day", m_DateTime)
Dim parPhaseID As New ReportParameter("PhaseID", m_PhaseID)
Dim parShiftID As New ReportParameter("ShiftID", m_ShiftID)
parReport(0) = parDay
parReport(1) = parPhaseID
parReport(2) = parShiftID
report.ReportPath = "C:\Documents and Settings\Kruse\My Documents\Visual Studio 2005\Projects\ShellyReportPrint\ShellyReportPrint\Production.rdlc"
report.SetParameters(parReport)
Export(report)
m_currentPageIndex = 0
Print()
Any help would be greatly appreciated! Thanks..
I have yet to find a solution for this issue... has anyone else implemented something similar?
If not, I guess I will have to wait until TechEd 2K6!
Monday, March 19, 2012
Assistance in connecting a SQL Server Client to SQL Server sitting in a shared environment
Hi,
I need to connect to a SQL server thats running in say abc.trident.com and also sits in a shared environment..
I have couple of questions
1) That SQL server is accessible from my network, yet when I swtich on my enterprise manager I am unable to view that in the list of running SQL servers in the populated list.
Is it because its sitting in a shared environment I am unable to view that?
2) What is the connection striing I should use to connect to the server..When I try to configure a SQL server registrations it normally asks for SQL Server name along with the user authentication
Should I mention fill the Server: field as
abc.trident.com
or
// abc.trident.com/abc.trident.com
to connect to the clustered server.
Can some one tell me the proper connection string if both aforementioned ones are incorrect?
Thanks in Advance
What error it throws !?
1) That SQL server is accessible from my network, yet when I swtich on my enterprise manager I am unable to view that in the list of running SQL servers in the populated list.
Is it because its sitting in a shared environment I am unable to view that?
>> Is this server is out side your network!!!? have you tried connecting using QA? is NET DDE and NET DDE DDSM service are running ? Is NETBIOS enabled?
2) What is the connection striing I should use to connect to the server..When I try to configure a SQL server registrations it normally asks for SQL Server name along with the user authentication
Should I mention fill the Server: field as
abc.trident.com
or
// abc.trident.com/abc.trident.com
to connect to the clustered server.
Refer connectionstring.com for different connection strings.
Regards
Hemantgiri S. Goswami
|||
1) is NET DDE and NET DDE DDSM service are running ? Is NETBIOS enabled?
Yes
2) The connectionstring.com site appears to be dead and has some irrevlant ads. can you tell me if my connection string format to represent a Clustered SQL server is correct or any other site from which I can get the accurate info
Efforts in googling ended in vain.
3) The error
<SQLServer does not exist or access denied ConnectionOpen.connect())>
Here the SQL server lies out side my network and the network is even accessible from a Tracert.
but the SQL server is up and running according to the Admin.
|||No takers!|||Hi,
You have said that the server is outside network.... If it is behind firewall have you check the proper firewall rule is placed in order to get you access !? and Port too...
Refer this KB for the workaround http://support.microsoft.com/kb/328306/en-us
Hemantgiri S. Goswami
Saturday, February 25, 2012
ASPNETDB.MDF?
Using VS2005, VB backend and javascript,
I have developed a relatively simple site - its got a few (12) simple aspx pages but its mostly client side javascript. Keeping disk storage costs down is a big concern with this my site. The disk usage for the site is ~24M. Since this was larger than I expected I started inspecting the files comprising my site and found that the "ASPNETDB.MDF" in my "App_data" folder is consuming 10.2M by itself. The thing is that site only has a few pages with calls to SQL Server - but I never did anything (that I know of) with ASPNETDB.MDF. Through VS2005, I opened up the MDF file and poked around, everything that I looked at was empty (NULL).
So my questions are:
What is causing the ASPNETDB.MDF to consume 10.2M even thought I can't see any data stored in it?Is there anyway for my to reduce the size of this file? If so, how?Your input appreciated.
Should I be posting this in a different forum?|||Hi,
The ASPNETDB contains a lot of information about table structure and membership information about your site.
To check what is occupying the storage, you can run DBCC CHECKDB on your local machine to see the allocation, structural, and logical integrity of all the objects in the specified database.
For more information, please check the following link.
http://msdn2.microsoft.com/en-us/library/aa258278(SQL.80).aspx
HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!
Friday, February 24, 2012
ASPNETDB.MDF - need to use SQL Server 2000
Hi,
I'm building an intranet app for a client using ASP.NET 2. The client is running SQL Server 2000 - and does not have plans to upgrade to 2005 anytime soon.
Is there a script that I can use to create the objects in the ASPNETDB database so that I can do this in SQL Server 2000?
Also, what additional changes would I have to make in order for the application to point to a SQL Server 2000 database with these objects?
Thanks in advance, Al
MDF(Microsoft data file) is only one half of a SQL Server database, the other half is the LDF(Log data file). That said the Personnal and Club starter kits comes with the membership database and Microsoft created a SQL Server 2000 version of both so I think you can install both take the membership related tables, triggers and stored procs. You know it is all manual so you just right click on the object in Object browser in Query Analyzer and click on create to generate the scripts. Hope this helps.
http://www.microsoft.com/downloads/details.aspx?FamilyId=0DD83A11-6980-4951-A192-DA6EACC6A19E&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=2EE85ED4-7613-47E2-8375-17222B150E4F&displaylang=en
|||Hi luckydog,
as alternative in clean way, you can run aspnet_regsql in command prompt and choose any database instance that you want to have membership tables inside it.
Run it in .Net Framework SDK Command Prompt
Subscribe to:
Posts (Atom)