Can we disable SA Account in SQL Server.

Hi,
I am running SQL Server 2000 SP3. For Security purpose i want to
disable SA Account.
But i am not sure that, we can disable SA account.
Please give me reply.
Thanks & Regards,
Sajid C.Hi
You cannot disable SA account. You can remove BUILTIN/Administrators login
to ensure that users of Windows admin group do not get autamically access
to SQL Server
<csajid@.gmail.com> wrote in message
news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> Hi,
> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
> But i am not sure that, we can disable SA account.
> Please give me reply.
> Thanks & Regards,
> Sajid C.
>|||Uri Dimant wrote:
[vbcol=seagreen]
> Hi
> You cannot disable SA account. You can remove BUILTIN/Administrators login
> to ensure that users of Windows admin group do not get autamically access
> to SQL Server
>
> <csajid@.gmail.com> wrote in message
> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
If you are usin SQL Server 2005 you can disable it by
alter login sa disable
even you can rename it
alter login sa with name = <new name for sa>
Regards
Amish Shah
http://shahamishm.tripod.com|||amish
> disable SA Account.
Yep, but he is using SQL Server 2000
"amish" <shahamishm@.gmail.com> wrote in message
news:1155030451.500469.238540@.m73g2000cwd.googlegroups.com...
> Uri Dimant wrote:
>
> If you are usin SQL Server 2005 you can disable it by
> alter login sa disable
> even you can rename it
> alter login sa with name = <new name for sa>
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>|||With SQL 2000, Give the sa account a complex and strong password, lock the
password in a safe, and don't allow anyone to use the sa account.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<csajid@.gmail.com> wrote in message
news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> Hi,
> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
> But i am not sure that, we can disable SA account.
> Please give me reply.
> Thanks & Regards,
> Sajid C.
>|||Hi all,
Thanks for your Reply.
Thanks & Regards,
Sajid C.
Arnie Rowland wrote:[vbcol=seagreen]
> With SQL 2000, Give the sa account a complex and strong password, lock the
> password in a safe, and don't allow anyone to use the sa account.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <csajid@.gmail.com> wrote in message
> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...

Can we disable SA Account in SQL Server.

Hi,
I am running SQL Server 2000 SP3. For Security purpose i want to
disable SA Account.
But i am not sure that, we can disable SA account.
Please give me reply.
Thanks & Regards,
Sajid C.Hi
You cannot disable SA account. You can remove BUILTIN/Administrators login
to ensure that users of Windows admin group do not get autamically access
to SQL Server
<csajid@.gmail.com> wrote in message
news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> Hi,
> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
> But i am not sure that, we can disable SA account.
> Please give me reply.
> Thanks & Regards,
> Sajid C.
>|||Uri Dimant wrote:
> Hi
> You cannot disable SA account. You can remove BUILTIN/Administrators login
> to ensure that users of Windows admin group do not get autamically access
> to SQL Server
>
> <csajid@.gmail.com> wrote in message
> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > I am running SQL Server 2000 SP3. For Security purpose i want to
> > disable SA Account.
> >
> > But i am not sure that, we can disable SA account.
> >
> > Please give me reply.
> >
> > Thanks & Regards,
> > Sajid C.
> >
> >
If you are usin SQL Server 2005 you can disable it by
alter login sa disable
even you can rename it
alter login sa with name = <new name for sa>
Regards
Amish Shah
http://shahamishm.tripod.com|||amish
>> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
Yep, but he is using SQL Server 2000
"amish" <shahamishm@.gmail.com> wrote in message
news:1155030451.500469.238540@.m73g2000cwd.googlegroups.com...
> Uri Dimant wrote:
>> Hi
>> You cannot disable SA account. You can remove BUILTIN/Administrators
>> login
>> to ensure that users of Windows admin group do not get autamically
>> access
>> to SQL Server
>>
>> <csajid@.gmail.com> wrote in message
>> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
>> > Hi,
>> >
>> > I am running SQL Server 2000 SP3. For Security purpose i want to
>> > disable SA Account.
>> >
>> > But i am not sure that, we can disable SA account.
>> >
>> > Please give me reply.
>> >
>> > Thanks & Regards,
>> > Sajid C.
>> >
>> >
> If you are usin SQL Server 2005 you can disable it by
> alter login sa disable
> even you can rename it
> alter login sa with name = <new name for sa>
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>|||With SQL 2000, Give the sa account a complex and strong password, lock the
password in a safe, and don't allow anyone to use the sa account.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<csajid@.gmail.com> wrote in message
news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> Hi,
> I am running SQL Server 2000 SP3. For Security purpose i want to
> disable SA Account.
> But i am not sure that, we can disable SA account.
> Please give me reply.
> Thanks & Regards,
> Sajid C.
>|||Hi all,
Thanks for your Reply.
Thanks & Regards,
Sajid C.
Arnie Rowland wrote:
> With SQL 2000, Give the sa account a complex and strong password, lock the
> password in a safe, and don't allow anyone to use the sa account.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <csajid@.gmail.com> wrote in message
> news:1155022050.211678.192190@.b28g2000cwb.googlegroups.com...
> > Hi,
> >
> > I am running SQL Server 2000 SP3. For Security purpose i want to
> > disable SA Account.
> >
> > But i am not sure that, we can disable SA account.
> >
> > Please give me reply.
> >
> > Thanks & Regards,
> > Sajid C.
> >

Can we access Active Directory Group in a report?

Is there a way that we can tell what active directory group the person belongs to that is running the report? I know that you can detect a user id, but I need to access the Active Directory Group that they belong to.

I'm not sure if there is another method, but this is the method I used for checking if a user belongs to a particular group. It can be altered to return the group:

Added to the code window.

Code Snippet

Function IsMemberOfGroup(ByVal UserName As String, ByVal GroupName As String) As Boolean
Try
Dim ent As System.DirectoryServices.DirectoryEntry = New System.DirectoryServices.DirectoryEntry("LDAP://DC=domainname,DC=com")

Dim srch As System.DirectoryServices.DirectorySearcher = New System.DirectoryServices.DirectorySearcher("(CN=" + GroupName + ")")
Dim coll As System.DirectoryServices.SearchResultCollection = srch.FindAll()
For Each rs As System.DirectoryServices.SearchResult In coll
Dim resultPropColl As System.DirectoryServices.ResultPropertyCollection = rs.Properties
For Each memberColl As Object In resultPropColl("member")
Dim gpMemberEntry As System.DirectoryServices.DirectoryEntry = New System.DirectoryServices.DirectoryEntry("LDAP://" + memberColl.ToString)
Dim userProps As System.DirectoryServices.PropertyCollection = gpMemberEntry.Properties
Dim obVal As Object = userProps("sAMAccountName").Value
If obVal.ToString = UserName Then Return True
Next
Next
Catch ex As Exception
'Trace.Write(ex.Message)
End Try
Return False
End Function

can view reports created in RS after upgrading to RS sp1?

Hi,
My server is currently running Reporting Services. I want to upgrade it
to reporting services sp1 or sp2. I wanna know after upgrading it to
sp1, will I will be able to view my older reports which is created
using reporting services?No problem. I suggest sp2. Also, upgrade your designer as well.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sheerin" <susheelkaur@.gmail.com> wrote in message
news:1141189981.337655.34260@.e56g2000cwe.googlegroups.com...
> Hi,
> My server is currently running Reporting Services. I want to upgrade it
> to reporting services sp1 or sp2. I wanna know after upgrading it to
> sp1, will I will be able to view my older reports which is created
> using reporting services?
>

can TLog backups cause blocking

I just had tons of blocking in my DB. I tracked down the culprit and killed
the spid... mistakingly without running dbcc inputbuffer first. So I the
looked in the error log and am 99% sure that what I killed was a TLog backup
based on the message:
BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
So my question is can a TLog backup cause blocking. I see how a TLog backup
could be blocked if a big transaction was occuring when it was run... but
could that in turn block other user activity? If so, how do I prevent this
from happening again?
TIA, ChrisRHi,
As far i am concerned Transaction log backup will not block any users. It
can only block if you execute 2 transaction log backups in parallel for the
same database or if both full database backup and transaction log backup
runs in parallel on the same database.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and
> killed the spid... mistakingly without running dbcc inputbuffer
> first. So I the looked in the error log and am 99% sure that what I
> killed was a TLog backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a
> thread exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how
> do I prevent this from happening again?
>
> TIA, ChrisR
The t-log backup only affects committed transactions. Open transactions
are not backed up. If you are backing up the log to the same drive where
you keep data, indexes, or other databases, it's possible you are
causing undue disk activity which could cause all transactions to slow
down. How long does the t-log backup normally take? Where are you
backing up?
--
David Gugick
Imceda Software
www.imceda.com|||The backup normally takes 30 - 60 seconds. I am backing up locally... but
not to the data drive.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%230ImFwhYFHA.4024@.TK2MSFTNGP10.phx.gbl...
> ChrisR wrote:
>> I just had tons of blocking in my DB. I tracked down the culprit and
>> killed the spid... mistakingly without running dbcc inputbuffer
>> first. So I the looked in the error log and am 99% sure that what I
>> killed was a TLog backup based on the message:
>> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
>> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
>> error 995(The I/O operation has been aborted because of either a
>> thread exit or an application request.).
>>
>> So my question is can a TLog backup cause blocking. I see how a TLog
>> backup could be blocked if a big transaction was occuring when it was
>> run... but could that in turn block other user activity? If so, how
>> do I prevent this from happening again?
>>
>> TIA, ChrisR
> The t-log backup only affects committed transactions. Open transactions
> are not backed up. If you are backing up the log to the same drive where
> you keep data, indexes, or other databases, it's possible you are causing
> undue disk activity which could cause all transactions to slow down. How
> long does the t-log backup normally take? Where are you backing up?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Another question, I did see "set textsize" occuring during this block. Ive
never used and and was wondering if it could be the culprit.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> Another question, I did see "set textsize" occuring during this
> block. Ive never used and and was wondering if it could be the
> culprit.
That statement just determines how much data from text columns is
returned with a SELECT statement. If anything, it should quicken queries
that access text/ntext data if the value is set lower than the average
number of bytes in the column.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Try to use 1204 trace flag to get more info about locks next time.
Look if you would have there BULK-OP-LOG locks - this particular type
of locks caused by transaction log backup. But as already been mentioned
it shouldn't be an issue to the uncommited transactions.
And usually this type of locks shouldn't cause any issues at all.
Regards.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||Transaction log backups can cause long transactions. I see stable pattern,
when during backup of transaction log on different disk from log disk or data
files disks, stored procedures which work, average, 30 ms, run near 2.5-3 sec.
"ChrisR" wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||The reason most people see performance issues with Log backups is because
their disk subsystem is not up to par, especially the one the log file is
on. Since the Log file must be read to back it up if that causes a lot of
disk queuing your system will suffer overall. If the log backup is causing a
bottleneck on that drive all new log writes will be affected. Since all log
activity is sequential it starts to affect all transactions from that point
on until the backup is done and the bottleneck is gone. This is not a Log
or Backup issue but an inadequately configured I/O system.
--
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:8CEB19AF-0F9B-4C52-9DDF-711A533C6BFA@.microsoft.com...
> Transaction log backups can cause long transactions. I see stable pattern,
> when during backup of transaction log on different disk from log disk or
> data
> files disks, stored procedures which work, average, 30 ms, run near 2.5-3
> sec.
> "ChrisR" wrote:
>> I just had tons of blocking in my DB. I tracked down the culprit and
>> killed
>> the spid... mistakingly without running dbcc inputbuffer first. So I the
>> looked in the error log and am 99% sure that what I killed was a TLog
>> backup
>> based on the message:
>> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
>> device
>> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error
>> 995(The
>> I/O operation has been aborted because of either a thread exit or an
>> application request.).
>>
>> So my question is can a TLog backup cause blocking. I see how a TLog
>> backup
>> could be blocked if a big transaction was occuring when it was run... but
>> could that in turn block other user activity? If so, how do I prevent
>> this
>> from happening again?
>>
>> TIA, ChrisR
>>
>>sql

can TLog backups cause blocking

I just had tons of blocking in my DB. I tracked down the culprit and killed
the spid... mistakingly without running dbcc inputbuffer first. So I the
looked in the error log and am 99% sure that what I killed was a TLog backup
based on the message:
BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup device
'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
So my question is can a TLog backup cause blocking. I see how a TLog backup
could be blocked if a big transaction was occuring when it was run... but
could that in turn block other user activity? If so, how do I prevent this
from happening again?
TIA, ChrisRHi,
As far i am concerned Transaction log backup will not block any users. It
can only block if you execute 2 transaction log backups in parallel for the
same database or if both full database backup and transaction log backup
runs in parallel on the same database.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and
> killed the spid... mistakingly without running dbcc inputbuffer
> first. So I the looked in the error log and am 99% sure that what I
> killed was a TLog backup based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a
> thread exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how
> do I prevent this from happening again?
>
> TIA, ChrisR
The t-log backup only affects committed transactions. Open transactions
are not backed up. If you are backing up the log to the same drive where
you keep data, indexes, or other databases, it's possible you are
causing undue disk activity which could cause all transactions to slow
down. How long does the t-log backup normally take? Where are you
backing up?
David Gugick
Imceda Software
www.imceda.com|||The backup normally takes 30 - 60 seconds. I am backing up locally... but
not to the data drive.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%230ImFwhYFHA.4024@.TK2MSFTNGP10.phx.gbl...
> ChrisR wrote:
> The t-log backup only affects committed transactions. Open transactions
> are not backed up. If you are backing up the log to the same drive where
> you keep data, indexes, or other databases, it's possible you are causing
> undue disk activity which could cause all transactions to slow down. How
> long does the t-log backup normally take? Where are you backing up?
> --
> David Gugick
> Imceda Software
> www.imceda.com|||Another question, I did see "set textsize" occuring during this block. Ive
never used and and was wondering if it could be the culprit.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>|||ChrisR wrote:
> Another question, I did see "set textsize" occuring during this
> block. Ive never used and and was wondering if it could be the
> culprit.
That statement just determines how much data from text columns is
returned with a SELECT statement. If anything, it should quicken queries
that access text/ntext data if the value is set lower than the average
number of bytes in the column.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Try to use 1204 trace flag to get more info about locks next time.
Look if you would have there BULK-OP-LOG locks - this particular type
of locks caused by transaction log backup. But as already been mentioned
it shouldn't be an issue to the uncommited transactions.
And usually this type of locks shouldn't cause any issues at all.
Regards.
"ChrisR" wrote:

> I just had tons of blocking in my DB. I tracked down the culprit and kille
d
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog back
up
> based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup devi
ce
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(T
he
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backu
p
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||Transaction log backups can cause long transactions. I see stable pattern,
when during backup of transaction log on different disk from log disk or dat
a
files disks, stored procedures which work, average, 30 ms, run near 2.5-3 se
c.
"ChrisR" wrote:

> I just had tons of blocking in my DB. I tracked down the culprit and kille
d
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog back
up
> based on the message:
> BackupVirtualDeviceFile::RequestDurableM
edia: Flush failure on backup devi
ce
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(T
he
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backu
p
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>|||The reason most people see performance issues with Log backups is because
their disk subsystem is not up to par, especially the one the log file is
on. Since the Log file must be read to back it up if that causes a lot of
disk queuing your system will suffer overall. If the log backup is causing a
bottleneck on that drive all new log writes will be affected. Since all log
activity is sequential it starts to affect all transactions from that point
on until the backup is done and the bottleneck is gone. This is not a Log
or Backup issue but an inadequately configured I/O system.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:8CEB19AF-0F9B-4C52-9DDF-711A533C6BFA@.microsoft.com...[vbcol=seagreen]
> Transaction log backups can cause long transactions. I see stable pattern,
> when during backup of transaction log on different disk from log disk or
> data
> files disks, stored procedures which work, average, 30 ms, run near 2.5-3
> sec.
> "ChrisR" wrote:
>

can TLog backups cause blocking

I just had tons of blocking in my DB. I tracked down the culprit and killed
the spid... mistakingly without running dbcc inputbuffer first. So I the
looked in the error log and am 99% sure that what I killed was a TLog backup
based on the message:
BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
I/O operation has been aborted because of either a thread exit or an
application request.).
So my question is can a TLog backup cause blocking. I see how a TLog backup
could be blocked if a big transaction was occuring when it was run... but
could that in turn block other user activity? If so, how do I prevent this
from happening again?
TIA, ChrisR
Hi,
As far i am concerned Transaction log backup will not block any users. It
can only block if you execute 2 transaction log backups in parallel for the
same database or if both full database backup and transaction log backup
runs in parallel on the same database.
Thanks
Hari
SQL Server MVP
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>
|||ChrisR wrote:
> I just had tons of blocking in my DB. I tracked down the culprit and
> killed the spid... mistakingly without running dbcc inputbuffer
> first. So I the looked in the error log and am 99% sure that what I
> killed was a TLog backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a
> thread exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how
> do I prevent this from happening again?
>
> TIA, ChrisR
The t-log backup only affects committed transactions. Open transactions
are not backed up. If you are backing up the log to the same drive where
you keep data, indexes, or other databases, it's possible you are
causing undue disk activity which could cause all transactions to slow
down. How long does the t-log backup normally take? Where are you
backing up?
David Gugick
Imceda Software
www.imceda.com
|||The backup normally takes 30 - 60 seconds. I am backing up locally... but
not to the data drive.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%230ImFwhYFHA.4024@.TK2MSFTNGP10.phx.gbl...
> ChrisR wrote:
> The t-log backup only affects committed transactions. Open transactions
> are not backed up. If you are backing up the log to the same drive where
> you keep data, indexes, or other databases, it's possible you are causing
> undue disk activity which could cause all transactions to slow down. How
> long does the t-log backup normally take? Where are you backing up?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Another question, I did see "set textsize" occuring during this block. Ive
never used and and was wondering if it could be the culprit.
"ChrisR" <noemail@.bla.com> wrote in message
news:%23GD2jhhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
>I just had tons of blocking in my DB. I tracked down the culprit and killed
>the spid... mistakingly without running dbcc inputbuffer first. So I the
>looked in the error log and am 99% sure that what I killed was a TLog
>backup based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup
> device 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system
> error 995(The I/O operation has been aborted because of either a thread
> exit or an application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog
> backup could be blocked if a big transaction was occuring when it was
> run... but could that in turn block other user activity? If so, how do I
> prevent this from happening again?
>
> TIA, ChrisR
>
>
|||ChrisR wrote:
> Another question, I did see "set textsize" occuring during this
> block. Ive never used and and was wondering if it could be the
> culprit.
That statement just determines how much data from text columns is
returned with a SELECT statement. If anything, it should quicken queries
that access text/ntext data if the value is set lower than the average
number of bytes in the column.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Try to use 1204 trace flag to get more info about locks next time.
Look if you would have there BULK-OP-LOG locks - this particular type
of locks caused by transaction log backup. But as already been mentioned
it shouldn't be an issue to the uncommited transactions.
And usually this type of locks shouldn't cause any issues at all.
Regards.
"ChrisR" wrote:

> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>
|||Transaction log backups can cause long transactions. I see stable pattern,
when during backup of transaction log on different disk from log disk or data
files disks, stored procedures which work, average, 30 ms, run near 2.5-3 sec.
"ChrisR" wrote:

> I just had tons of blocking in my DB. I tracked down the culprit and killed
> the spid... mistakingly without running dbcc inputbuffer first. So I the
> looked in the error log and am 99% sure that what I killed was a TLog backup
> based on the message:
> BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device
> 'VDI_87B83311-A31D-45AA-BE8D-CE08E15BE176_0'. Operating system error 995(The
> I/O operation has been aborted because of either a thread exit or an
> application request.).
>
> So my question is can a TLog backup cause blocking. I see how a TLog backup
> could be blocked if a big transaction was occuring when it was run... but
> could that in turn block other user activity? If so, how do I prevent this
> from happening again?
>
> TIA, ChrisR
>
>
>
|||The reason most people see performance issues with Log backups is because
their disk subsystem is not up to par, especially the one the log file is
on. Since the Log file must be read to back it up if that causes a lot of
disk queuing your system will suffer overall. If the log backup is causing a
bottleneck on that drive all new log writes will be affected. Since all log
activity is sequential it starts to affect all transactions from that point
on until the backup is done and the bottleneck is gone. This is not a Log
or Backup issue but an inadequately configured I/O system.
Andrew J. Kelly SQL MVP
"andsm" <andsm@.discussions.microsoft.com> wrote in message
news:8CEB19AF-0F9B-4C52-9DDF-711A533C6BFA@.microsoft.com...[vbcol=seagreen]
> Transaction log backups can cause long transactions. I see stable pattern,
> when during backup of transaction log on different disk from log disk or
> data
> files disks, stored procedures which work, average, 30 ms, run near 2.5-3
> sec.
> "ChrisR" wrote:

Can this be optimized? Newbie question

Hi,
I'm running the following SQL to get values for 4 fields. It is
unacceptably slow. I have no control over the structure of the
database, field names, indexes etc. - what I'm given as far as DB
design is all I'm going to get. If anyone could make any suggestions
I'd really appreciate it!

Thanks,
Bill

SELECT DISTINCT
T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
T_RECEIVING_DETAIL.amount
FROM T_MULTILIST, T_RECEIVING_DETAIL,
T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
T_RECEIVING
WHERE
(
T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
AND T_ORDER.depository_id =T_DEPOSITORY.id
AND T_REQUISITION.id =T_ORDER.requisition_id
AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
AND T_REQUISITION_DETAIL.multilist_code
=T_MULTILIST_GRADE.multilist_code
AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code

AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
'06/01/2006'
AND T_MULTILIST.expiration_year > '2005'
AND T_MULTILIST.code IN ('0043','1043')
AND T_DEPOSITORY.depository_type = 'PRIVATE'
AND T_RECEIVING.status <> 'PAID'
)
wgblackmon@.yahoo.com wrote:

> Hi,
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!
> Thanks,
> Bill

Well, it sounds like you're pretty much screwed. How many rows
does the query return? Is it appreciably faster if you remove
the 'DISTINCT' and do you have the opportunity to detect
and ignore duplicates at the client? Can you even find out
what indexes are on the tables or get the query plan for this?
There may be other query criteria that you could drop, and
instead post-qualify rows in the client.
Hope this (or someone else smarter) helps,
Joe Weinstein at BEA Systems

> SELECT DISTINCT
> T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
> T_RECEIVING_DETAIL.amount
> FROM T_MULTILIST, T_RECEIVING_DETAIL,
> T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
> T_RECEIVING
> WHERE
> (
> T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
> AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
> AND T_ORDER.depository_id =T_DEPOSITORY.id
> AND T_REQUISITION.id =T_ORDER.requisition_id
> AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
> AND T_REQUISITION_DETAIL.multilist_code
> =T_MULTILIST_GRADE.multilist_code
> AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code
> AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
> '06/01/2006'
> AND T_MULTILIST.expiration_year > '2005'
> AND T_MULTILIST.code IN ('0043','1043')
> AND T_DEPOSITORY.depository_type = 'PRIVATE'
> AND T_RECEIVING.status <> 'PAID'
> )|||I'm using this query (and up to 20 similar ones combined with 'UNION')
in a Crystal Report. The report may or may not be able to remove dupes,
but I doubt it (I'm new at Crystal Reports). I'm using DBArtisan to
design the query. The database is an undocumented nightmare with few
indexes. I know it's hideous, but I was hoping I was missing something
really obvious...:)|||Bill,

There is nothing wrong with the query, except that maybe the DISTINCT is
not necessary and could save some time if you dropped it.

The key of this query's performance is in the available indexes (and
maybe the hardware configuration). If no usuable indexes are available
and the tables are large then this query will run like a dog. You should
really turn to the DBA who can put the proper indexes in place...

Gert-Jan

"wgblackmon@.yahoo.com" wrote:
> Hi,
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!
> Thanks,
> Bill
> SELECT DISTINCT
> T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,
> T_RECEIVING_DETAIL.amount
> FROM T_MULTILIST, T_RECEIVING_DETAIL,
> T_MULTILIST_GRADE,T_REQUISITION,T_REQUISITION_DETA IL,T_ORDER,T_DEPOSITORY,
> T_RECEIVING
> WHERE
> (
> T_RECEIVING_DETAIL.invoice_number =T_RECEIVING.invoice_number
> AND T_RECEIVING_DETAIL.order_id =T_ORDER.id
> AND T_ORDER.depository_id =T_DEPOSITORY.id
> AND T_REQUISITION.id =T_ORDER.requisition_id
> AND T_REQUISITION_DETAIL.requisition_id =T_REQUISITION.id
> AND T_REQUISITION_DETAIL.multilist_code
> =T_MULTILIST_GRADE.multilist_code
> AND T_MULTILIST_GRADE.multilist_code =T_MULTILIST.code
> AND T_ORDER.requisition_time_stamp BETWEEN '05/31/2005' AND
> '06/01/2006'
> AND T_MULTILIST.expiration_year > '2005'
> AND T_MULTILIST.code IN ('0043','1043')
> AND T_DEPOSITORY.depository_type = 'PRIVATE'
> AND T_RECEIVING.status <> 'PAID'
> )|||T_MULTILIST.description may be wide. Sorting wide result sets may be
slow. try removing duplicates before joining with T_MULTILIST. Look up
article "The Less SQL Server Sorts, the Faster It Responds"|||wgblackmon@.yahoo.com (wgblackmon@.yahoo.com) writes:
> I'm running the following SQL to get values for 4 fields. It is
> unacceptably slow. I have no control over the structure of the
> database, field names, indexes etc. - what I'm given as far as DB
> design is all I'm going to get. If anyone could make any suggestions
> I'd really appreciate it!

My newsserver had an outage, so the reply I posted originally got lost.
What I said in that post was not that fantastic:

Without know the tables and indexes it's about impossible to give
suggestions. If you post the CREATE TABLE and CREATE INDEX statements
(don't forget constraints!), as well some indication of table sizes,
we might be able to give some tips.

Even better if you can run:

SET STATISTICS PROFILE ON
go
-- query goes here
go
SET STATISTICS PROFILE OFF
go

and post the output. (Preferably in an attachment, as the output is far too
wide for news article).

However, a few minutes later one more thing occurred to me, and that was
when I discovered that the newsserver was sick.

Anyway, what you could try is to run DBCC DBREINDEX on all involved tables.
While it is not going to cause the query to run with the speed of light
all of a sudden, you could see an improvement with 20-30% if there is
serious fragmentation of the tables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Can the use of Shrinkfile break the Transaction Log chain?

My Log Shipping process has been up and running fine for about 6 months now.
Then as part of the preparation for upgrading the front end application for
the log shipped database, my partner DBA thought he would help the backup
speed by running a SHRINKFILE over the database files.
It was on or about this time that log shipping stopped working and reported
errors just like the ones you get when the transaction log has been
truncated. Basically it thinks the T Log chain has been broken.
Can SHRINKFILE break the chain? I thought it only compacted the unused file
space?
In my tests log shipping was not sensitive to shrinkfile statements. Perhaps
something else has gone wrong.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GRP" <GRP@.discussions.microsoft.com> wrote in message
news:BD8BC1B6-FD32-43C2-B34E-7CA0397CAC35@.microsoft.com...
> My Log Shipping process has been up and running fine for about 6 months
now.
> Then as part of the preparation for upgrading the front end application
for
> the log shipped database, my partner DBA thought he would help the backup
> speed by running a SHRINKFILE over the database files.
> It was on or about this time that log shipping stopped working and
reported
> errors just like the ones you get when the transaction log has been
> truncated. Basically it thinks the T Log chain has been broken.
> Can SHRINKFILE break the chain? I thought it only compacted the unused
file
> space?
|||Hilary, thanks for taking the time to test this out. The only other
explanation I can think of is that the other DBA truncated the log prior to
the shrinkfile.
"Hilary Cotter" wrote:

> In my tests log shipping was not sensitive to shrinkfile statements. Perhaps
> something else has gone wrong.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "GRP" <GRP@.discussions.microsoft.com> wrote in message
> news:BD8BC1B6-FD32-43C2-B34E-7CA0397CAC35@.microsoft.com...
> now.
> for
> reported
> file
>
>

Can the Output parameter length be more than 8000 characters?

Hi!
I am running one SP - which needs to return strings, seperated by
delimitter. I am using output parameter of type Varchar (8000). I learned
that this is maximum length allowed.
Now what problem I am facing is, for a particular field, the delimitted text
is getting higher than 8000 characters and that is why the rest of the value
is getting truncated.
Can you guys let me know any better way of achieving this?
I will be extremely thankful to you.
Regards,
SachinYou'll have to select the data instead of using an output param... Or
upgrade to SQL Server 2005 and use VARCHAR(MAX) instead :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
message news:880D2034-115A-4E86-9D38-97BD909BF564@.microsoft.com...
> Hi!
> I am running one SP - which needs to return strings, seperated by
> delimitter. I am using output parameter of type Varchar (8000). I learned
> that this is maximum length allowed.
> Now what problem I am facing is, for a particular field, the delimitted
> text
> is getting higher than 8000 characters and that is why the rest of the
> value
> is getting truncated.
> Can you guys let me know any better way of achieving this?
> I will be extremely thankful to you.
> Regards,
> Sachin|||Instead of Varchar(8000) ... how about using TEXT or NText as your datatype?
Best Regards
Vadivel
http://vadivel.blogspot.com
http://thinkingms.com/vadivel
"Sachin Vaishnav" wrote:

> Hi!
> I am running one SP - which needs to return strings, seperated by
> delimitter. I am using output parameter of type Varchar (8000). I learned
> that this is maximum length allowed.
> Now what problem I am facing is, for a particular field, the delimitted te
xt
> is getting higher than 8000 characters and that is why the rest of the val
ue
> is getting truncated.
> Can you guys let me know any better way of achieving this?
> I will be extremely thankful to you.
> Regards,
> Sachin|||Thanks. Using 2005 is not possible for me now. I will have to manage fromw
what I have already :)
Anyways, as per your other suggestion, the problem in that is, I am already
having one select returned out of the SP. So, there is no point in that also
.
Can some cursor type of output or XML type of output is useful to me?
I need to send it back to the API and the API is used by UI.
Help me,
Sachin
"Adam Machanic" wrote:

> You'll have to select the data instead of using an output param... Or
> upgrade to SQL Server 2005 and use VARCHAR(MAX) instead :)
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
> message news:880D2034-115A-4E86-9D38-97BD909BF564@.microsoft.com...
>
>|||Stored procedures can return multiple rowsets... Why not use two?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
message news:DB3F424E-01C2-49DB-B7B7-99D22F91CD24@.microsoft.com...
> Thanks. Using 2005 is not possible for me now. I will have to manage fromw
> what I have already :)
> Anyways, as per your other suggestion, the problem in that is, I am
> already
> having one select returned out of the SP. So, there is no point in that
> also.
> Can some cursor type of output or XML type of output is useful to me?
> I need to send it back to the API and the API is used by UI.
> Help me,
> Sachin
> "Adam Machanic" wrote:
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Ogxg9Nr7FHA.1416@.TK2MSFTNGP09.phx.gbl...
> Stored procedures can return multiple rowsets... Why not use two?
...or use multiple Output parameters.
When one reaches the 8000 character limit, insert the rest in the 2nd.
But I'd prefer Adam's solution, 2 recordsets.|||Hi!
Thanks a lot. Is it possible to have 2 RS from SP? Well, I was unable to get
once. Can I have some example of the same?
Thanks
Sachin
"Adam Machanic" wrote:

> Stored procedures can return multiple rowsets... Why not use two?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
> message news:DB3F424E-01C2-49DB-B7B7-99D22F91CD24@.microsoft.com...
>
>|||Sure...
CREATE PROCEDURE TWO_RESULT_SETS
AS
BEGIN
SELECT 1
SELECT 2
END
GO
EXEC TWO_RESULT_SETS
GO
DROP PROCEDURE TWO_RESULT_SETS
GO
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
message news:FD089362-31C1-49B2-83B0-88ED1F9FD5FC@.microsoft.com...
> Hi!
> Thanks a lot. Is it possible to have 2 RS from SP? Well, I was unable to
> get
> once. Can I have some example of the same?
> Thanks
> Sachin
> "Adam Machanic" wrote:
>|||Thanks a lotl!
However, I know this. But i guess, the problem is perhaps, when I write 2
selects in the SP, if I am using ADODB.Recordset to retrieve the data, I
won't get the result of both the record set. Right?
So, can you suggest me how do I tackle that one? :)
Thanks again!
Regards,
Sachin
"Adam Machanic" wrote:

> Sure...
> --
> CREATE PROCEDURE TWO_RESULT_SETS
> AS
> BEGIN
> SELECT 1
> SELECT 2
> END
> GO
> EXEC TWO_RESULT_SETS
> GO
> DROP PROCEDURE TWO_RESULT_SETS
> GO
> --
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Sachin Vaishnav" <SachinVaishnav@.discussions.microsoft.com> wrote in
> message news:FD089362-31C1-49B2-83B0-88ED1F9FD5FC@.microsoft.com...
>
>|||Set rsSecond = rsFirst.NextRecordset()
cheers,
</wqw>

Can start new database from Web Matrix

I am running Web Matrix version 0.5, .Net version 1.1 on a computer with XP Pro.

Web Matrix works fine and returns forms properly.

I installed MSDE from the website (SQL2KDeskSP3a.exe) Ibelieve that SQL Server IS running, because I see the tower icon with agreen arrow; when I double-click that I get a message that it isrunning SQL Server.

When I click the "data" tab in Web Matrix I get the blankworkspace. Then I click the New Connection icon at the top left,which opens up a dialog box. I change "Windows Authentication" to"SQL Authentication." That opens up the Username/Passwordprompt. I am entering "sa" for the username (I AM NOT SURE IFTHAT IS CORRECT) and "**secret**" for the password (that's what Ientered in the command prompt when I setup the MSDE). Then Iclick "Create a New Database." I am asked to enter a name.

After a pause, I get an error message: "Unable to connect tothe database server. SQL Server does not exist or access denied.Connection Open (Connect ( )). OK

Do you have any ideas?

I installed the newer (version 0.6) Matrix and now I can find the database.

Can SQLServer produce Excel Spreadsheet output ?

Deaa group,

I am using SQLServer 2000 in an XP Sp2. I would like to do the
following:

I have a program running on a database server that generates some data
which are loaded to the database. This program is used in a web
application, invoked by some java program and JSP scripts. (I am
frontend illiterated.)

The question is, is it possible to write a stored procedure to generate
output in excel spreadsheet? So that user could call this procedure
and get spreadsheet output on the client side.

Any pointer to a solution would be immensely apprecaited.

thanks,
charia<cpeters5@.gmail.com> wrote in message
news:1120580708.814110.191080@.z14g2000cwz.googlegr oups.com...
> Deaa group,
> I am using SQLServer 2000 in an XP Sp2. I would like to do the
> following:
> I have a program running on a database server that generates some data
> which are loaded to the database. This program is used in a web
> application, invoked by some java program and JSP scripts. (I am
> frontend illiterated.)
> The question is, is it possible to write a stored procedure to generate
> output in excel spreadsheet? So that user could call this procedure
> and get spreadsheet output on the client side.
> Any pointer to a solution would be immensely apprecaited.
> thanks,
> charia

As far as I know, there's no direct way to export to an .xls from a stored
proc. DTS can export data to Excel, and you can execute a package from a
stored proc in various ways:

http://www.sqldts.com/default.aspx?210

By using ActiveX steps in a DTS package, you could control all the details
of the .xls file name, structure, column headers etc. via the Excel COM
interface, but you would need to actually install Excel on the server in
order to do that, which may not be possible (or desirable).

Another option would be calling bcp.exe via xp_cmdshell to create a CSV or
tab-delimited file. In the end, the easiest solution might be to find a Java
or JSP module of some sort which can export to Excel - then you just return
the result set to the client or middle tier as usual, and let it create the
file, which is probably a cleaner solution than dealing with presentation in
the database itself.

Simon|||i know ASP can generate an xls from data selected by a SP. i bet there
is some way JSP can do it as well, i'm just not a web developer =P

Can Sql Server Express 2005 Run a Sql Server 2000 database?

Should be a quick question:

I've got a client with a Sql Server 2000 database. He wants to hook it up to a server that is only running Sql Server Express 2005. Can this work?

Thanks!

Tell him to Backup the database and send you the .bak you can restore it as 2005 database. I have posted a FAQ about moving SQL Server databases and there is a free chapter about Backup and Restore in the link below. Hope this helps.

http://forums.asp.net/thread/1454694.aspx

|||

Thanks! I will recommend this to my client.

For argument's sake though; could a 2000 database run in 2005 w/out any changes?

|||Yes you could run it without changes if needed it will be related to stored proc rewrites and if it comes with Text columns you may want to change to Varchar(Max) it let you run text like in row data. And if there are indexes drop them and create new one. Hope this helps.|||

Before you start backup - restore check if he is not using features which are not available on SQL Express like SQL Agent for example?

Thanks

Can SQL Server 2005 Evaluation be used to upgrade a system?

I have multiple development SQL Servers running Enterprise edition that I want to upgrade for application testing before upgrading our production system. All of the servers meet the hardware and software requirements for SQL Server 2005 Enterprise edition. However, when I try to install to use the SQL Server 2005 Evaluation Edition to upgrade the servers in place, I get the blocked upgrade message:

Name: Microsoft SQL Server 2000 Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online. Edition check: Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.

Can the Evaluation Edition be used to upgrade these servers or do you need the full blown version?

thanks

Sorry, upgrades are prohibited *to* the Eval edition.|||I am getting this error now myself and I don't know why. I installed the trial SQL Server Express for my boss. He then asked if I would install the trial SQL Server 2005 becuase it has more capabilities he would like to try out. So I installed 2005. He noticed that there was no management studio installed and needed. So I uninstalled it and am trying to reinstall it and am getting this error. I haven't even used the software yet! I don't know what to do!|||

I was able to continue the installation but just before I click OK to install, I see this in a window

The following components that you selected will not be changed:

Client Components

that might be why I had my original problem. That problem was the Management Studio was never installed which prompted me to reinstall the system alltogether thinking I must have not checked that installation option.

Ahh!!!

Can SQL Server 2005 Evaluation be used to upgrade a system?

I have multiple development SQL Servers running Enterprise edition that I want to upgrade for application testing before upgrading our production system. All of the servers meet the hardware and software requirements for SQL Server 2005 Enterprise edition. However, when I try to install to use the SQL Server 2005 Evaluation Edition to upgrade the servers in place, I get the blocked upgrade message:

Name: Microsoft SQL Server 2000

Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.

Edition check:

Your upgrade is blocked because of edition upgrade rules. For more information about edition upgrades, see the Version and Edition Upgrades topic in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.

Can the Evaluation Edition be used to upgrade these servers or do you need the full blown version?

thanks

Sorry, upgrades are prohibited *to* the Eval edition.|||I am getting this error now myself and I don't know why. I installed the trial SQL Server Express for my boss. He then asked if I would install the trial SQL Server 2005 becuase it has more capabilities he would like to try out. So I installed 2005. He noticed that there was no management studio installed and needed. So I uninstalled it and am trying to reinstall it and am getting this error. I haven't even used the software yet! I don't know what to do!|||

I was able to continue the installation but just before I click OK to install, I see this in a window

The following components that you selected will not be changed:

Client Components

that might be why I had my original problem. That problem was the Management Studio was never installed which prompted me to reinstall the system alltogether thinking I must have not checked that installation option.

Ahh!!!

Can SQL Lose Records?

We are running SQL 7 with a front end that links to the tables through ODBC.
In our main table, the user has no way to delete a record through the
interface, though it is possible to delete it by opening the ODBC link.
Users would have no reason to delete a record, but one of our records turned
up missing.

Now, it's possible that a user may have accidentally deleted the record.
But, since users don't have any reason to delete records, and since they
don't access the ODBC links, it seems unlikely (though possible).

I was wondering if anyone had every heard of SQL Server ever "losing" a
record that had previously been saved. I checked the nightly backup from the
night after it was added, and the record was there. So either a user deleted
it, or somehow it got lost in SQL Server. I have no code that deletes
records in this table in any way, shape or form, so it couldn't have been
malfunctioning code.

So, while I have a hard time believing that SQL Server would just "lose" a
record, I also know that anything's possible, so I thought I'd ask if anyone
had ever heard of such a thing.

Thanks!

NeilOn Jun 20, 2:53 pm, "Neil" <nos...@.nospam.netwrote:

Quote:

Originally Posted by

We are running SQL 7 with a front end that links to the tables through ODBC.
In our main table, the user has no way to delete a record through the
interface, though it is possible to delete it by opening the ODBC link.
Users would have no reason to delete a record, but one of our records turned
up missing.
>
Now, it's possible that a user may have accidentally deleted the record.
But, since users don't have any reason to delete records, and since they
don't access the ODBC links, it seems unlikely (though possible).
>
I was wondering if anyone had every heard of SQL Server ever "losing" a
record that had previously been saved. I checked the nightly backup from the
night after it was added, and the record was there. So either a user deleted
it, or somehow it got lost in SQL Server. I have no code that deletes
records in this table in any way, shape or form, so it couldn't have been
malfunctioning code.
>
So, while I have a hard time believing that SQL Server would just "lose" a
record, I also know that anything's possible, so I thought I'd ask if anyone
had ever heard of such a thing.
>
Thanks!
>
Neil

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.|||I've never seen or heard of a row going missing either, and I spent
plenty of time using 7.0.

Along with what Alex suggested I would suggest doing a complete set of
DBCC integrity checks on the database.

Roy Harvey
Beacon Falls, CT

On Wed, 20 Jun 2007 19:53:32 GMT, "Neil" <nospam@.nospam.netwrote:

Quote:

Originally Posted by

>We are running SQL 7 with a front end that links to the tables through ODBC.
>In our main table, the user has no way to delete a record through the
>interface, though it is possible to delete it by opening the ODBC link.
>Users would have no reason to delete a record, but one of our records turned
>up missing.
>
>Now, it's possible that a user may have accidentally deleted the record.
>But, since users don't have any reason to delete records, and since they
>don't access the ODBC links, it seems unlikely (though possible).
>
>I was wondering if anyone had every heard of SQL Server ever "losing" a
>record that had previously been saved. I checked the nightly backup from the
>night after it was added, and the record was there. So either a user deleted
>it, or somehow it got lost in SQL Server. I have no code that deletes
>records in this table in any way, shape or form, so it couldn't have been
>malfunctioning code.
>
>So, while I have a hard time believing that SQL Server would just "lose" a
>record, I also know that anything's possible, so I thought I'd ask if anyone
>had ever heard of such a thing.
>
>Thanks!
>
>Neil

|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

So, while I have a hard time believing that SQL Server would just "lose"
a record, I also know that anything's possible, so I thought I'd ask if
anyone had ever heard of such a thing.

Well, I have lost rows, but that was a on a system where no one was looking
at the event log or the DBCC logs, and finally the database broke down,
with several levels of corruption.

As Roy said, run DBCC. If it comes up with corruption, then that may be
the answer.

But I'm prepared to place my bets that there was a human involved.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||

Quote:

Originally Posted by

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.
>

If I add a trigger prohibiting any deletes, then it wouldn't be possible for
me to go in and delete a record if I ever needed to, right? Or is there a
way to set up a trigger so that it can allow the delete in some cases?

Thanks.|||I'm not familiar with DBCC. Can you point me in the right direction?

Thanks.

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9955ED0DF77BAYazorman@.127.0.0.1...

Quote:

Originally Posted by

Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

>So, while I have a hard time believing that SQL Server would just "lose"
>a record, I also know that anything's possible, so I thought I'd ask if
>anyone had ever heard of such a thing.

>
Well, I have lost rows, but that was a on a system where no one was
looking
at the event log or the DBCC logs, and finally the database broke down,
with several levels of corruption.
>
As Roy said, run DBCC. If it comes up with corruption, then that may be
the answer.
>
But I'm prepared to place my bets that there was a human involved.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||On Jun 20, 5:01 pm, "Neil" <nos...@.nospam.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Never heard of it. Revoke delete permissions from all your users. Add
a trigger prohibiting any deletes.

>
If I add a trigger prohibiting any deletes, then it wouldn't be possible for
me to go in and delete a record if I ever needed to, right? Or is there a
way to set up a trigger so that it can allow the delete in some cases?
>
Thanks.

You can disable the trigger for the duration of your delete.
Alternatively you can have you trigger allow you to do whatever you
want, based on user_id() or suser_id(). Trigger can be bypassed using
nested triggers and or recursive trigger setting. There are other ways
best described in T-SQL Programming by Itzik Ben-Gan.

http://sqlserver-tips.blogspot.com/|||Neil (nospam@.nospam.net) writes:

Quote:

Originally Posted by

I'm not familiar with DBCC. Can you point me in the right direction?

There are several DBCC commands, but the one of interest here is DBCC
CHECKDB which checks the database for consistency errors. If the database is
of any size, run it off-hours.

You should regularly run DBCC on your database, for instance as part of a
maintenance job, and make sure that you get alerted if it finds any errors.

If memory serves, you just say "DBCC CHECKDB" in the database you want to
examine. But check Books Online for details.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello,

You can think of SQL Profiler as well if still you doubt on the SQL
Activities for some particular time.

Thanks
Ajay

Can Sql 2000 and 2005 express work together?

I have a server (win server 2003) that is running Symantec Backup Exec for backups. I found that this soft uses an SQL server 2000 instance to keep his data... The problem is that I developed an application in asp.net (with VS2003) that use an SQL server 2005 express database. What I want to know is if I install SQL server 2005 express, is the backup will still work? I mean that the 2005 express wont upgrade sql 2000? What do I have to do to make it works?

hi,

SQL Server 2000 (including MSDE) and SQL Server 2005 (including SQLExpress) cna be installed "side by side"...

you can install up to 16 different instances per box, and each instance will maintain it's own binaries and registry settings separated from all other instances.. even at different service pack levels..

obviously you have not to mismatch instance's names.. only 1 instance per box can be the "default" instance, known as "ComputerName" or "." or "(Local)" (the last 2 only for local connections), where all the other instances have to be "named" instances, known as "ComputerName\InstanceName" or ".\InstanceName" or "(Local)\InstanceName" ... further info at http://msdn2.microsoft.com/en-us/library/ms143547.aspx

a little "note" should be mentioned about shared components.. this binaries are actually shared among all instances of the same code base.. so all 2000 related shared binaries will be shared among all 2000 instances, where 2005 shared components will be shared with all 2005 instances, at the higher service pack level..

another "side note".. and this is COM related.. COM components do not allow "side by side" implementation, so that SQL Server 2005 installs some of them at the 2005 edition level.. and SQL Server 2000 will "inherit" those components.. think about SQL-DMO and the like, but you should not be interested with that as 2005 versions are downwards compatible...

regards

|||Thanks a lot!

Can RS 2005 uses all the new enhancements running on SQL server 2000 SP3a?

Hello,
Can RS 2005 uses all the new enhancements running on SQL server 2000
SP3a?
SteveYes. All the Reporting Services enhancement. Obviously not the SQL Server
2005 enhancements. I am running this environment and RS 2005 is fully
featured. Absolutely no difference in functionality. The db used for
object/metadata storage can be either SQL Server 2000 or 2005 BUT you need a
2005 license regardless.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"steve kwon" <steven,kwon@.lmco.com> wrote in message
news:eIqOTXwEGHA.1396@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Can RS 2005 uses all the new enhancements running on SQL server 2000
> SP3a?
> Steve
>

Can OWC and ASP.NET be used to connect to an OLAP cube running on SQL Server 2005 ?

Hello, I'm from Romania and I have a small problem. I recently found on MSDN an articol and an ASP.NET project showing how to browse an OLAP cube from a web page, using OWC controls. The cube was built using Analysis Services for SQL Server 2000.

My question is can the same be done with a cube running on SQL Server 2005 (browse it from ASP.NET page using OWC) ? If so, can anyone please give a link to an tutorial or sample project ?
Or if not, please offer a solution on how to browse an OLAP cube from SQL Server
2005 from ASP.NET.

Thanks.

For the client application using OWC all you need to do is to install new version of the AS OLEDB provider. OWC can work with both AS200 and AS2005. Same goes for Excel and all major Analysis Services clients.

You can install "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider " from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi. It's me again, I have downloaded and installed all necessary stuff, and project runs seems to run fine, but the Office Web Control says it cannot find the object specified by me.

Here's the OLAPconnection string :

value="Provider=MSOLAP.2;Data Source=http://localhost/elena;Initial Catalog=Aplicatie LogyCars;"

Here is the block of code where i attempt to connect ;

Try

Dim objPT As PivotTableClass = New PivotTableClass

objPT.ConnectionString = strOLAPConn

'strDataMember = "CubLogyCar"

strDataMember = "Cub LogyCar"

objPT.DataMember = strDataMember

m_XML = objPT.XMLData

objPT = Nothing

Catch err As Exception

m_XML = "<err>" & err.Source & " - " & err.Message & "</err>"

Finally

End Try

Return (m_XML)

End Function

There's no error, but still does not connect. "Cub LogyCar" is the name of the OLAP cube buuilt inside the Aplicatie LogyCars solution. Both of them are correctly written in code and web.config file.

Any Suggestions ?

|||For Analysis Service 2005, the provider should be MSOLAP.3

Can OWC and ASP.NET be used to connect to an OLAP cube running on SQL Server 2005 ?

Hello, I'm from Romania and I have a small problem. I recently found on MSDN an articol and an ASP.NET project showing how to browse an OLAP cube from a web page, using OWC controls. The cube was built using Analysis Services for SQL Server 2000.

My question is can the same be done with a cube running on SQL Server 2005 (browse it from ASP.NET page using OWC) ? If so, can anyone please give a link to an tutorial or sample project ?
Or if not, please offer a solution on how to browse an OLAP cube from SQL Server
2005 from ASP.NET.

Thanks.

For the client application using OWC all you need to do is to install new version of the AS OLEDB provider. OWC can work with both AS200 and AS2005. Same goes for Excel and all major Analysis Services clients.

You can install "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider " from http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi. It's me again, I have downloaded and installed all necessary stuff, and project runs seems to run fine, but the Office Web Control says it cannot find the object specified by me.

Here's the OLAPconnection string :

value="Provider=MSOLAP.2;Data Source=http://localhost/elena;Initial Catalog=Aplicatie LogyCars;"

Here is the block of code where i attempt to connect ;

Try

Dim objPT As PivotTableClass = New PivotTableClass

objPT.ConnectionString = strOLAPConn

'strDataMember = "CubLogyCar"

strDataMember = "Cub LogyCar"

objPT.DataMember = strDataMember

m_XML = objPT.XMLData

objPT = Nothing

Catch err As Exception

m_XML = "<err>" & err.Source & " - " & err.Message & "</err>"

Finally

End Try

Return (m_XML)

End Function

There's no error, but still does not connect. "Cub LogyCar" is the name of the OLAP cube buuilt inside the Aplicatie LogyCars solution. Both of them are correctly written in code and web.config file.

Any Suggestions ?

|||For Analysis Service 2005, the provider should be MSOLAP.3