Backing up a schema

Is it possible to only backup the tables that belong to a schema?

Not with our backup utility.

You could use bcp or some other "export" tool.

Avoid to Write the Schema Name in a Function Call in SQL 2005

Avoid Writing the Schema Name in a Function Call in SQL 2005

Hi, everybody, already I try to certificate my code in Oracle PL/SQL to T-SQL to SQL Server 2005, but I have a little trouble, When I Translate the code with SQL Migration Assistant for Oracle (SSMA), this tool, put the schema name before the function name, that is a good practice, but I can avoid to do that, because, The company have a lot of calls in hard-code, and when I execute the call without the schema name, SQL2005 send me a error message, like say, “is not recognize as built-in function name”.

Somebody knows what I can do to do that?

Thanks

KC:

If the function is a scalar function you must precede the function name with the schema; it is a requirement for the way Transact SQL Works.

Available strategies to upgrade a database schema without downtime

There is a great book on database refactoring that contains a comprehensive set or recipies on how to revise databases that are supposed to be always online and may have various clients that can't be upgraded at the same time. I guess this is a typical case with large databases and I would be surpised if Amazon stops their servers just to move a column from one table to another. The book describes necessary steps for such changes. Basically it's all about creating intermediate database schemas that would be used during transition period.

For example, if we need to move a column from one table to another:

Version 1.
Table A columns: Name, Price
Table B columns: Quantity, Date

Let's say we move Price to table B:

Version 2.
Table A columns: Name
Table B columns: Quantity, Date, Price

The book suggests an intermediate version:

Version 1_2.
Table A columns: Name, Price
Table B columns: Quantity, Date, Price
Additional trigger that will synchronize "Price" columns between A and B.

Version 1_2 can be used by both clients written for version 1 and 2. Software developers don't need to rush their upgrades, transition can last months and include several changes.

This technique requires accuracy in version control management, but looks very good to implement non-interruptible database schema upgrade. I wonder if this is the only option available for data schema upgrade with no downtime. I can't think about anything else - it this how large data warehouses updata their databases?That's a sensible method. Another is to create a view (name,price) and expose it as TableA. With an instead of trigger, you then can update the correct base tables.

autoshrink causes "Could not complete cursor operation...table schema changed&quo

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"
Has anyone saw this before ?
If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens ever
y
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.
Autoshrink was turned on as a way to keep physical transaction log size unde
r
control [yes, I know, not a good idea]
Thanks,
Andy Mackie.That is not the way to keep the log file under control. Either it needs
that much space or it doesn't. If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions. If it does need more space than you have you are only hurting
the situation by shrinking it.
Andrew J. Kelly SQL MVP
"A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
news:xn0e2pqso958n30000@.news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
> Has anyone saw this before ?
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
> Thanks,
> Andy Mackie.|||And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#qXFTUhYFHA.3096@.TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control. Either it needs
> that much space or it doesn't. If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions. If it does need more space than you have you are only
hurting
> the situation by shrinking it.
> --
> Andrew J. Kelly SQL MVP
>
> "A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
> news:xn0e2pqso958n30000@.news.microsoft.com...
cause[vbcol=seagreen]
>|||Paul S Randal [MS] wrote:

> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure bu
t
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
> Thanks
OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenanc
e
plans and re-indexing, that is the real problem (all 2nd hand info).
I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups ar
e
scheduled, to keep the log file under control. Hopefully that will sort
things out.
Thanks,
Andy.|||A Mackie wrote:

> Paul S Randal [MS] wrote:
>
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sor
t
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled t
o
run at the same time as some cursor-processing.
Andy.

autoshrink causes "Could not complete cursor operation...table schema changed"

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"
Has anyone saw this before ?
If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens every
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.
Autoshrink was turned on as a way to keep physical transaction log size under
control [yes, I know, not a good idea]
Thanks,
Andy Mackie.
That is not the way to keep the log file under control. Either it needs
that much space or it doesn't. If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions. If it does need more space than you have you are only hurting
the situation by shrinking it.
Andrew J. Kelly SQL MVP
"A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
news:xn0e2pqso958n30000@.news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
> Has anyone saw this before ?
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
> Thanks,
> Andy Mackie.
|||And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#qXFTUhYFHA.3096@.TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control. Either it needs
> that much space or it doesn't. If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions. If it does need more space than you have you are only
hurting[vbcol=seagreen]
> the situation by shrinking it.
> --
> Andrew J. Kelly SQL MVP
>
> "A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
> news:xn0e2pqso958n30000@.news.microsoft.com...
cause
>
|||Paul S Randal [MS] wrote:

> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure but
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
> Thanks
OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenance
plans and re-indexing, that is the real problem (all 2nd hand info).
I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups are
scheduled, to keep the log file under control. Hopefully that will sort
things out.
Thanks,
Andy.
|||A Mackie wrote:

> Paul S Randal [MS] wrote:
>
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sort
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled to
run at the same time as some cursor-processing.
Andy.

autoshrink causes "Could not complete cursor operation...table schema changed"

I've had a report that turning on autoshrink causes the error "Could not
complete cursor operation because the table schema changed after the cursor
was declared"
Has anyone saw this before ?
If a maintenance plan to re-index kicked in during some long running process
caused this error, that I could understand. But why would autoshrink cause
this ? The only thing I can speculate is that autoshrink (which happens every
30 minutes I believe) is taking a sufficiently long time to run, that it may
be timing out the long running application process.
Autoshrink was turned on as a way to keep physical transaction log size under
control [yes, I know, not a good idea]
Thanks,
Andy Mackie.That is not the way to keep the log file under control. Either it needs
that much space or it doesn't. If it doesn't it wont grow past it unless
you are not doing regular log backups or you have long running open
transactions. If it does need more space than you have you are only hurting
the situation by shrinking it.
--
Andrew J. Kelly SQL MVP
"A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
news:xn0e2pqso958n30000@.news.microsoft.com...
> I've had a report that turning on autoshrink causes the error "Could not
> complete cursor operation because the table schema changed after the
> cursor
> was declared"
> Has anyone saw this before ?
> If a maintenance plan to re-index kicked in during some long running
> process
> caused this error, that I could understand. But why would autoshrink cause
> this ? The only thing I can speculate is that autoshrink (which happens
> every
> 30 minutes I believe) is taking a sufficiently long time to run, that it
> may
> be timing out the long running application process.
> Autoshrink was turned on as a way to keep physical transaction log size
> under
> control [yes, I know, not a good idea]
> Thanks,
> Andy Mackie.|||And shrink (auto or otherwise) _may_ cause the error as it has to move IAM
pages. Depending on the kind of internal scan the cursor was doing, the
locks required to move the IAM page may be interpreted the by the scan as
the IAM chain changing and hence the schema changing. I'm not 100% sure but
it kind of makes sense to me. If its causing you difficulty and you can't
turn off autoshrink (you really should..), call PSS to help you.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#qXFTUhYFHA.3096@.TK2MSFTNGP15.phx.gbl...
> That is not the way to keep the log file under control. Either it needs
> that much space or it doesn't. If it doesn't it wont grow past it unless
> you are not doing regular log backups or you have long running open
> transactions. If it does need more space than you have you are only
hurting
> the situation by shrinking it.
> --
> Andrew J. Kelly SQL MVP
>
> "A Mackie" <andrew@.mackie14.freeserve.co.uk> wrote in message
> news:xn0e2pqso958n30000@.news.microsoft.com...
> > I've had a report that turning on autoshrink causes the error "Could not
> > complete cursor operation because the table schema changed after the
> > cursor
> > was declared"
> >
> > Has anyone saw this before ?
> >
> > If a maintenance plan to re-index kicked in during some long running
> > process
> > caused this error, that I could understand. But why would autoshrink
cause
> > this ? The only thing I can speculate is that autoshrink (which happens
> > every
> > 30 minutes I believe) is taking a sufficiently long time to run, that it
> > may
> > be timing out the long running application process.
> >
> > Autoshrink was turned on as a way to keep physical transaction log size
> > under
> > control [yes, I know, not a good idea]
> >
> > Thanks,
> > Andy Mackie.
>|||Paul S Randal [MS] wrote:
> And shrink (auto or otherwise) may cause the error as it has to move IAM
> pages. Depending on the kind of internal scan the cursor was doing, the
> locks required to move the IAM page may be interpreted the by the scan as
> the IAM chain changing and hence the schema changing. I'm not 100% sure but
> it kind of makes sense to me. If its causing you difficulty and you can't
> turn off autoshrink (you really should..), call PSS to help you.
> Thanks
OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's maintenance
plans and re-indexing, that is the real problem (all 2nd hand info).
I've recommended to turn off auto-shrink, make sure no re-indexing is
occuring at the time of the processing, and make sure regular log backups are
scheduled, to keep the log file under control. Hopefully that will sort
things out.
Thanks,
Andy.|||A Mackie wrote:
> Paul S Randal [MS] wrote:
> > And shrink (auto or otherwise) may cause the error as it has to move IAM
> > pages. Depending on the kind of internal scan the cursor was doing, the
> > locks required to move the IAM page may be interpreted the by the scan as
> > the IAM chain changing and hence the schema changing. I'm not 100% sure
> > but it kind of makes sense to me. If its causing you difficulty and you
> > can't turn off autoshrink (you really should..), call PSS to help you.
> >
> > Thanks
> OK, thanks. I'm not sure yet if it's auto-shrink, or whether it's
> maintenance plans and re-indexing, that is the real problem (all 2nd hand
> info).
> I've recommended to turn off auto-shrink, make sure no re-indexing is
> occuring at the time of the processing, and make sure regular log backups
> are scheduled, to keep the log file under control. Hopefully that will sort
> things out.
> Thanks,
> Andy.
Looks like there was a maintenance plan that included a re-index scheduled to
run at the same time as some cursor-processing.
Andy.