see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.|||As Aaron mentions – use a source control, that’s what they are there for
.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/ In...Mgt
.pdf)
which outlines the approach I’ve taken with unparalleled success. I’d lo
ve an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environme
nt
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be ne
w
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determin
e
what needs to be changed on the target environment and then these changes ar
e
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event a
n
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||As Aaron mentions – use a source control, that’s what they are there for
.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/ In...Mgt
.pdf)
which outlines the approach I’ve taken with unparalleled success. I’d lo
ve an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environme
nt
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be ne
w
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determin
e
what needs to be changed on the target environment and then these changes ar
e
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event a
n
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||You can see this in information_schema.routines (column name is last_altered
)
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
[vbcol=seagreen]
> You can see this in information_schema.routines (column name is last_alter
ed)
> "Francesco" wrote:
>|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.
Showing posts with label enterprise. Show all posts
Showing posts with label enterprise. Show all posts
Friday, March 23, 2012
last update date for a procedure
see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||You can see this in information_schema.routines (column name is last_altered)
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
> You can see this in information_schema.routines (column name is last_altered)
> "Francesco" wrote:
> > see SqlServer Enterprise Manager: the list of stored procedures has just
> > name, owner, type and create date as columns.
> > I'd like to have also the date of the last update for a stored procedure.
> > This can help me a lot.
> >
> > Is this possible? Is this date recorder in the system tables?
> > Thanks.
> >|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.sql
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||As Aaron mentions â' use a source control, thatâ's what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf)
which outlines the approach Iâ've taken with unparalleled success. Iâ'd love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1. The source code needs to added to your source control.
2. Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why â' and this is just my
opinion â' I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3. Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLLâ's, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4. The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5. You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||You can see this in information_schema.routines (column name is last_altered)
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
> You can see this in information_schema.routines (column name is last_altered)
> "Francesco" wrote:
> > see SqlServer Enterprise Manager: the list of stored procedures has just
> > name, owner, type and create date as columns.
> > I'd like to have also the date of the last update for a stored procedure.
> > This can help me a lot.
> >
> > Is this possible? Is this date recorder in the system tables?
> > Thanks.
> >|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.sql
last update date for a procedure
see SqlServer Enterprise Manager: the list of stored procedures has just
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.
Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.
|||As Aaron mentions – use a source control, that’s what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf)
which outlines the approach I’ve taken with unparalleled success. I’d love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1.The source code needs to added to your source control.
2.Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3.Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4.The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5.You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||As Aaron mentions – use a source control, that’s what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf)
which outlines the approach I’ve taken with unparalleled success. I’d love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1.The source code needs to added to your source control.
2.Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3.Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4.The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5.You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||You can see this in information_schema.routines (column name is last_altered)
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
[vbcol=seagreen]
> You can see this in information_schema.routines (column name is last_altered)
> "Francesco" wrote:
|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.
name, owner, type and create date as columns.
I'd like to have also the date of the last update for a stored procedure.
This can help me a lot.
Is this possible? Is this date recorder in the system tables?
Thanks.
Hi,
SQL Server will not store the modified date and time of the procedure.So if
you use ALter Procedure you cant see the modified date and time.
Thanks
Hari
SQL Server MVP
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||Hi
No, SQL Server does not track such info.
Are you only a person in the company to make changes in stored procedures
and other objects?
"Francesco" <Francesco@.discussions.microsoft.com> wrote in message
news:BE716295-7938-4A53-BA43-7A74DF780488@.microsoft.com...
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
No. This is what source control is for.
|||As Aaron mentions – use a source control, that’s what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf)
which outlines the approach I’ve taken with unparalleled success. I’d love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1.The source code needs to added to your source control.
2.Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3.Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4.The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5.You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||As Aaron mentions – use a source control, that’s what they are there for.
This is simply said rather than done however and has been the subject of my
concern for many years now. There is a document
(http://www.innovartis.co.uk/pdf/Inno...ange_Mgt. pdf)
which outlines the approach I’ve taken with unparalleled success. I’d love an
open debate on this subject as I still see no solution other than the one I
offer which gives an automated approach to database change management that
will save your company a lot of money, which I believe (IMHO) - is our
primary function as IT personnel.
When you have your source code in your source control for any thing - be it
applications, dynamic link libraries, user controls or databases there are
some necessary processes that need to be followed.
1.The source code needs to added to your source control.
2.Developers check out the source code to make changes. With application
code integrated development environments are readily available that will
check out the relevant files however these environments for SQL Server are
not so abundant and suffer many quirks which is why – and this is just my
opinion – I still find Query Analyzer to be the best development environment
meaning I still have to check out the files from the source control via the
source controls interface.
3.Developers add new source code to the source control when new
functionality is required. IE: when dealing with applications this may be new
forms, DLL’s, classes etc and when dealing with databases this may be new
tables or stored procedures. Different objects may be open to different
groups to develop as many organizations require that only a certain
individuals have the necessary abilities to make changes to tables for
example. These rules can be part of the source control allowing for a single
coherent view of functionality across the enterprise.
4.The code needs to be versioned at regular intervals to create snapshots
that facilitate deployment and code audits. All source controls have this
functionality IE: Visual Source Safe uses labels for this functionality.
5.You need a process that can deliver all the changes made to the code base
to the relevant areas. When you are dealing with applications this means
compiling the source code and deploying the compiled code to the required
machines. When talking databases this means compiling the source code and
deploying the changes to the required environments. I think this is where
people start to question this approach. Compile a database? Building a
database from the source code, compiles the source code. This step ensures
any changes made that break existing code are identified. Too often I see
broken code due to deployed code changes that ignore this fact. With
application code, compilers throw errors when dependencies are invalid. Now
that the source code has been validated a comparison can be made to determine
what needs to be changed on the target environment and then these changes are
made. For this step I always implement an automated process on a machine
where a copy of the end environment can be held to test the deployment. The
process records a SQL delta file which can then be used (if the process has
been successful) on those environments requiring the changes. In the event an
error occurs, the error can be identified, the person responsible can be
identified, the time the error was made can be identified creating an ideal
environment for the code to be fixed quickly and the process repeated to
verify the change/fix.
This is what I like to call database change management and DB Ghost
(http://www.dbghost.com) is the result of the many years of thinking to
facilitate this process.
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||You can see this in information_schema.routines (column name is last_altered)
"Francesco" wrote:
> see SqlServer Enterprise Manager: the list of stored procedures has just
> name, owner, type and create date as columns.
> I'd like to have also the date of the last update for a stored procedure.
> This can help me a lot.
> Is this possible? Is this date recorder in the system tables?
> Thanks.
>
|||try testing this column, you should see that it doesn't work...
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Database change management for SQL Server
"harvinder" wrote:
[vbcol=seagreen]
> You can see this in information_schema.routines (column name is last_altered)
> "Francesco" wrote:
|||> You can see this in information_schema.routines (column name is
> last_altered)
NO, the data is not accurate unless you've never modified the stored
procedure. The datetime value stored here is always the same as created
date, and is never updated on ALTER... this will be more reliable using the
catalog views in SQL Server 2005, but for now the data is not stored.
Last TSQL Command Batch
Does anyone know where Enterprise Manager (MSSQL 2000) gets the "Last TSQL Command Batch:" information from that shows when you display the properties for a process listed under current activity/Process Info for a server.
This could be useful for a bespoke system monitoring utility in my system.Google is your friend
http://www.developersdex.com/gurus/code/488.asp
This could be useful for a bespoke system monitoring utility in my system.Google is your friend
http://www.developersdex.com/gurus/code/488.asp
Friday, March 9, 2012
Largest SQL Server 2000 Enterprise Ed. Databases
I like to obtain information on several of the largest
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evalua.../wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techin...scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evalua.../wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techin...scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx
.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
Largest SQL Server 2000 Enterprise Ed. Databases
I like to obtain information on several of the largest
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
Mike
Hi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluat...wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinf...calability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
Mike
Hi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluat...wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinf...calability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
Largest SQL Server 2000 Enterprise Ed. Databases
I like to obtain information on several of the largest
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluation/compare/wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
SQL Server 2000 Enterprise Edition databases? With the
hardware specification and disk space (SAN, LUN, etc.)
they are using.
Also would be helpful the average number of transactions
per minute the server is handling.
If the application used a third party utility to enhance
application scheduling?
I would like to obtain this information for trading floor
application that I'm am working on.
Thanks,
MikeHi,
Have a look into the below link for details.
http://www.microsoft.com/sql/evaluation/compare/wintercorp.asp
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike|||Mike,
'Scalability and Very Large Database (VLDB) Resources'
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
'Transaction Processing Performance Council'
http://www.tpc.org/
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:15a7501c446c5$e8df33a0$a101280a@.phx.gbl...
> I like to obtain information on several of the largest
> SQL Server 2000 Enterprise Edition databases? With the
> hardware specification and disk space (SAN, LUN, etc.)
> they are using.
> Also would be helpful the average number of transactions
> per minute the server is handling.
> If the application used a third party utility to enhance
> application scheduling?
> I would like to obtain this information for trading floor
> application that I'm am working on.
> Thanks,
> Mike
large varchar update fails on sql 2000 server, works on dev. machine
A production system (server 2003 enterprise edition) sql 2000 server,
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
1
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this' Is this me'
ThanksOn Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td>
</tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&am
p;nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:<
/
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
[vbcol=seagreen]
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td>
</tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&am
p;nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:<
/
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?
> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
1
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this' Is this me'
ThanksOn Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td>
</tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&am
p;nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:<
/
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
[vbcol=seagreen]
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td>
</tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&am
p;nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:<
/
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?
> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.
large varchar update fails on sql 2000 server, works on dev. machine
A production system (server 2003 enterprise edition) sql 2000 server,
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
1
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this? Is this me?
Thanks
On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this? Is this me?
> Thanks
[vbcol=seagreen]
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this? Is this me?
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?
> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?
|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
1
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this? Is this me?
Thanks
On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this? Is this me?
> Thanks
[vbcol=seagreen]
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this? Is this me?
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?
> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?
|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.
large varchar update fails on sql 2000 server, works on dev. machine
A production system (server 2003 enterprise edition) sql 2000 server,
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id = 1
--
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this' Is this me'
ThanksOn Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id => 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
>>What's with this' Is this me'
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id => 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?
> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id = 1
--
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this' Is this me'
ThanksOn Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id => 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
>>What's with this' Is this me'
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id => 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?
> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.
Wednesday, March 7, 2012
Large text block into ntext
How can I get a block of text into an ntext field. Have tried copy and paste
out of Notepad into field in Enterprise Manager? But it truncates.
Is there a command line that will put text into specific field in specific
table?
Use the ado stream object or textcopy
have a look at this link for more info on how to do this.
http://www.indexserverfaq.com/SQLLoadADO.htm
http://www.indexserverfaq.com/TextCopy.htm
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
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:C3C68066-04B7-4C81-A7C1-E95EF0FC4E8A@.microsoft.com...
> How can I get a block of text into an ntext field. Have tried copy and
paste
> out of Notepad into field in Enterprise Manager? But it truncates.
> Is there a command line that will put text into specific field in specific
> table?
>
out of Notepad into field in Enterprise Manager? But it truncates.
Is there a command line that will put text into specific field in specific
table?
Use the ado stream object or textcopy
have a look at this link for more info on how to do this.
http://www.indexserverfaq.com/SQLLoadADO.htm
http://www.indexserverfaq.com/TextCopy.htm
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
"EdwardH" <EdwardH@.discussions.microsoft.com> wrote in message
news:C3C68066-04B7-4C81-A7C1-E95EF0FC4E8A@.microsoft.com...
> How can I get a block of text into an ntext field. Have tried copy and
paste
> out of Notepad into field in Enterprise Manager? But it truncates.
> Is there a command line that will put text into specific field in specific
> table?
>
Subscribe to:
Posts (Atom)