Can some tell me where I can find the list of latest updates for SQL Server
2005? I'd like to find a way of being informed when the latest update is
released (I'm assuming Microsoft Update doesn't list the cumulative update
packages released every so often?)?
Thanks - John.
No, there is no definitive place to watch. I will include a bunch of posts
I have made on new builds below. I try to stay on top of it, but I am only
human, and have other responsibilities, like day job, family, etc.
http://sqlblog.com/blogs/aaron_bertrand/archive/2006/12/12/419.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/03/06/service-pack-2a.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/03/28/an-update-on-recent-sql-server-2005-builds.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/04/04/the-drama-continues-more-post-sp2-fallout.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/04/11/more-on-gdrs-qfes-and-the-aftermath-of-sp2.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/05/03/some-new-or-updated-sql-server-2005-builds.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/06/27/staying-on-top-of-sql-server-2005-builds-and-hotfixes.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/06/27/some-new-and-updated-sql-server-2005-builds.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/09/11/new-cumulative-update-is-available.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2007/10/16/quick-update-new-cumulative-update-has-been-released.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2008/02/06/list-of-sql-server-2005-post-sp2-builds.aspx
http://sqlblog.com/blogs/aaron_bertrand/archive/2008/02/19/i-would-rather-see-sp3-but-i-ll-take-cu6-instead.aspx
And then these guys try to keep a list too, but everyone seems to have
little holes in their lists here and there:
http://www.aspfaq.com/sql2005/show.asp?id=20
http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx
http://sqlserverbuilds.blogspot.com/
http://www.sqlservercentral.com/articles/Administration/2960/
Microsoft is trying to maintain a list, but the following one is only 3152+
http://support.microsoft.com/kb/937137/
"John Davis" <jdavis707@.hotmail.com> wrote in message
news:uTv4DiMkIHA.2276@.TK2MSFTNGP05.phx.gbl...
> Can some tell me where I can find the list of latest updates for SQL
> Server 2005? I'd like to find a way of being informed when the latest
> update is released (I'm assuming Microsoft Update doesn't list the
> cumulative update packages released every so often?)?
> Thanks - John.
|||You'd think the best place to keep track of this type of stuff would MS <sigh>.
Linchi
"Aaron Bertrand [SQL Server MVP]" wrote:
> No, there is no definitive place to watch. I will include a bunch of posts
> I have made on new builds below. I try to stay on top of it, but I am only
> human, and have other responsibilities, like day job, family, etc.
> http://sqlblog.com/blogs/aaron_bertrand/archive/2006/12/12/419.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/03/06/service-pack-2a.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/03/28/an-update-on-recent-sql-server-2005-builds.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/04/04/the-drama-continues-more-post-sp2-fallout.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/04/11/more-on-gdrs-qfes-and-the-aftermath-of-sp2.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/05/03/some-new-or-updated-sql-server-2005-builds.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/06/27/staying-on-top-of-sql-server-2005-builds-and-hotfixes.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/06/27/some-new-and-updated-sql-server-2005-builds.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/09/11/new-cumulative-update-is-available.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2007/10/16/quick-update-new-cumulative-update-has-been-released.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2008/02/06/list-of-sql-server-2005-post-sp2-builds.aspx
> http://sqlblog.com/blogs/aaron_bertrand/archive/2008/02/19/i-would-rather-see-sp3-but-i-ll-take-cu6-instead.aspx
> And then these guys try to keep a list too, but everyone seems to have
> little holes in their lists here and there:
> http://www.aspfaq.com/sql2005/show.asp?id=20
> http://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx
> http://sqlserverbuilds.blogspot.com/
> http://www.sqlservercentral.com/articles/Administration/2960/
> Microsoft is trying to maintain a list, but the following one is only 3152+
> http://support.microsoft.com/kb/937137/
>
> "John Davis" <jdavis707@.hotmail.com> wrote in message
> news:uTv4DiMkIHA.2276@.TK2MSFTNGP05.phx.gbl...
>
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
lastmodified
Hi,
Can someone provide me a simple trigger where it updates a lastmodified
column.
It should update the column lastmodified whenever an update occurs on all
the other columns.
ThnxCREATE TRIGGER tr_YourTrigger
ON YourTable
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
UPDATE YourTable SET [LastModified] = GETDATE()
WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
Andrew J. Kelly SQL MVP
"Jason" <jlewis@.hotmail.com> wrote in message
news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can someone provide me a simple trigger where it updates a lastmodified
> column.
> It should update the column lastmodified whenever an update occurs on all
> the other columns.
> Thnx
>|||In addition to Andrew's response you can use DEFAULT constraint
CREATE TABLE #Test
(
col DATETIME DEFAULT GETDATE()
)
INSERT INTO #Test DEFAULT VALUES
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> CREATE TRIGGER tr_YourTrigger
> ON YourTable
> FOR UPDATE
> AS
>
> IF @.@.ROWCOUNT > 0
> UPDATE YourTable SET [LastModified] = GETDATE()
> WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jason" <jlewis@.hotmail.com> wrote in message
> news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
all[vbcol=seagreen]
>|||That only works for Inserts. He specifically asked for Updates.
Andrew J. Kelly SQL MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> In addition to Andrew's response you can use DEFAULT constraint
> CREATE TABLE #Test
> (
> col DATETIME DEFAULT GETDATE()
> )
> INSERT INTO #Test DEFAULT VALUES
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> all
>|||Thanks, my mistake.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:esONVXbGFHA.560@.TK2MSFTNGP15.phx.gbl...
> That only works for Inserts. He specifically asked for Updates.
> --
> Andrew J. Kelly SQL MVP
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
lastmodified[vbcol=seagreen]
>
Can someone provide me a simple trigger where it updates a lastmodified
column.
It should update the column lastmodified whenever an update occurs on all
the other columns.
ThnxCREATE TRIGGER tr_YourTrigger
ON YourTable
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
UPDATE YourTable SET [LastModified] = GETDATE()
WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
Andrew J. Kelly SQL MVP
"Jason" <jlewis@.hotmail.com> wrote in message
news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can someone provide me a simple trigger where it updates a lastmodified
> column.
> It should update the column lastmodified whenever an update occurs on all
> the other columns.
> Thnx
>|||In addition to Andrew's response you can use DEFAULT constraint
CREATE TABLE #Test
(
col DATETIME DEFAULT GETDATE()
)
INSERT INTO #Test DEFAULT VALUES
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> CREATE TRIGGER tr_YourTrigger
> ON YourTable
> FOR UPDATE
> AS
>
> IF @.@.ROWCOUNT > 0
> UPDATE YourTable SET [LastModified] = GETDATE()
> WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jason" <jlewis@.hotmail.com> wrote in message
> news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
all[vbcol=seagreen]
>|||That only works for Inserts. He specifically asked for Updates.
Andrew J. Kelly SQL MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> In addition to Andrew's response you can use DEFAULT constraint
> CREATE TABLE #Test
> (
> col DATETIME DEFAULT GETDATE()
> )
> INSERT INTO #Test DEFAULT VALUES
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> all
>|||Thanks, my mistake.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:esONVXbGFHA.560@.TK2MSFTNGP15.phx.gbl...
> That only works for Inserts. He specifically asked for Updates.
> --
> Andrew J. Kelly SQL MVP
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
lastmodified[vbcol=seagreen]
>
Last update of SQL-table
Hi All,
Can you tell how to diplay the last update of a table.
Many thanks,
Regards,"Hicham" <Mechmachi@.hotmail.com> wrote in message
news:01bc01c35abc$60cf0f80$a501280a@.phx.gbl...
> Can you tell how to diplay the last update of a table.
>
Unless you've coded your own trigger to archive table data before it's
updated, there is no way to display the last update.
Steve
Can you tell how to diplay the last update of a table.
Many thanks,
Regards,"Hicham" <Mechmachi@.hotmail.com> wrote in message
news:01bc01c35abc$60cf0f80$a501280a@.phx.gbl...
> Can you tell how to diplay the last update of a table.
>
Unless you've coded your own trigger to archive table data before it's
updated, there is no way to display the last update.
Steve
last update of database
DB - SQL2000 or SQL7
OS - W2K or NT
Is there a sp or any way to check when a database is last accessed by or
updated by user ?
TIA
No, SQL Server doesn't track this information, unless you want to use a
third party tool to review the transaction logs. You can put code into your
own procedures, or triggers on tables if you for some reason allow direct
access to tables, to track whatever components of the transaction you like.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:e7DI$JeNEHA.556@.tk2msftngp13.phx.gbl...
> DB - SQL2000 or SQL7
> OS - W2K or NT
>
> Is there a sp or any way to check when a database is last accessed by or
> updated by user ?
> TIA
>
>
OS - W2K or NT
Is there a sp or any way to check when a database is last accessed by or
updated by user ?
TIA
No, SQL Server doesn't track this information, unless you want to use a
third party tool to review the transaction logs. You can put code into your
own procedures, or triggers on tables if you for some reason allow direct
access to tables, to track whatever components of the transaction you like.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:e7DI$JeNEHA.556@.tk2msftngp13.phx.gbl...
> DB - SQL2000 or SQL7
> OS - W2K or NT
>
> Is there a sp or any way to check when a database is last accessed by or
> updated by user ?
> TIA
>
>
last update of database
DB - SQL2000 or SQL7
OS - W2K or NT
Is there a sp or any way to check when a database is last accessed by or
updated by user ?
TIANo, SQL Server doesn't track this information, unless you want to use a
third party tool to review the transaction logs. You can put code into your
own procedures, or triggers on tables if you for some reason allow direct
access to tables, to track whatever components of the transaction you like.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:e7DI$JeNEHA.556@.tk2msftngp13.phx.gbl...
> DB - SQL2000 or SQL7
> OS - W2K or NT
>
> Is there a sp or any way to check when a database is last accessed by or
> updated by user ?
> TIA
>
>
OS - W2K or NT
Is there a sp or any way to check when a database is last accessed by or
updated by user ?
TIANo, SQL Server doesn't track this information, unless you want to use a
third party tool to review the transaction logs. You can put code into your
own procedures, or triggers on tables if you for some reason allow direct
access to tables, to track whatever components of the transaction you like.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:e7DI$JeNEHA.556@.tk2msftngp13.phx.gbl...
> DB - SQL2000 or SQL7
> OS - W2K or NT
>
> Is there a sp or any way to check when a database is last accessed by or
> updated by user ?
> TIA
>
>
last update of database
DB - SQL2000 or SQL7
OS - W2K or NT
Is there a sp or any way to check when a database is last accessed by or
updated by user ?
TIANo, SQL Server doesn't track this information, unless you want to use a
third party tool to review the transaction logs. You can put code into your
own procedures, or triggers on tables if you for some reason allow direct
access to tables, to track whatever components of the transaction you like.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:e7DI$JeNEHA.556@.tk2msftngp13.phx.gbl...
> DB - SQL2000 or SQL7
> OS - W2K or NT
>
> Is there a sp or any way to check when a database is last accessed by or
> updated by user ?
> TIA
>
>
OS - W2K or NT
Is there a sp or any way to check when a database is last accessed by or
updated by user ?
TIANo, SQL Server doesn't track this information, unless you want to use a
third party tool to review the transaction logs. You can put code into your
own procedures, or triggers on tables if you for some reason allow direct
access to tables, to track whatever components of the transaction you like.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:e7DI$JeNEHA.556@.tk2msftngp13.phx.gbl...
> DB - SQL2000 or SQL7
> OS - W2K or NT
>
> Is there a sp or any way to check when a database is last accessed by or
> updated by user ?
> TIA
>
>
Last Update date/time
Hello.
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql
Last Update date/time
Hello.
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
Last Update date/time
Hello.
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
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/ 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.
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.
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.
Wednesday, March 21, 2012
Last Select Statement
Any ideas how I can find out when was the last update/select made in a database in SQL Server? Even if I can find any user login information, that would be useful too.
Regards,
Bala
With the SQL Server Profiler tool, you can monitor querying activity, including who's running the query. But you only get that information when it's running... I don't know of a general activity log SQL Server keeps that you could study.
-Ryan / Kardax
|||You could try using Lumigent's Log Explorer or Red-Gate's Log Rescue, however these will not help with tracking SELECT statements but will help track modifications to the database's data or structure:
http://www.lumigent.com/products/le_sql.html
http://www.red-gate.com/products/SQL_Log_Rescue/index.htm
Note that the ability to obtain such information from a database's log file is dependent on both the database's recovery model and when the last log backup was taken (if appropriate).
Chris
Monday, March 12, 2012
last day of the month
Hi all,
I have Date_Mmmyy column in my table that stores dates in MMMYY format
for example; "OCT01","OCT04","AUG04". I need to update another column in
this table, which is DateTime, with the last day of the Date_Mmmyy value.
Here is an example...
DECLARE @.test table
(
Date_Mmmyy varchar(5),
Date_Datetime datetime
)
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT01')
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT04')
INSERT INTO @.test(Date_Mmmyy)
VALUES('AUG04')
SELECT *
FROM @.test
I would like to see...
OCT01 2001-09-30
OCT04 2004-09-30
AUG04 2004-08-31
Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
October is the 10th month not the 9th.
SELECT date_mmmyy,
dateadd(month,1,cast('01' + date_mmmyy as datetime))-1
FROM @.test
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjC8+IechKqOuFEgEQJEdQCeI7osM0fWzMiL
l/e3qENjIa25TV0AoKRZ
SYBhDB51p4ZJy+UuV2Sjy0Rk
=8ZVj
--END PGP SIGNATURE--
sql wrote:
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31|||> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
UPDATE @.test SET Date_Datetime=dateadd(day, -1, dateadd(month, 1,
'1'+Date_Mmmyy))
> SELECT *
> FROM @.test
>
Regards
Mr Tea
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>|||Thank you all for your replies. I got this working with your help.
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>
I have Date_Mmmyy column in my table that stores dates in MMMYY format
for example; "OCT01","OCT04","AUG04". I need to update another column in
this table, which is DateTime, with the last day of the Date_Mmmyy value.
Here is an example...
DECLARE @.test table
(
Date_Mmmyy varchar(5),
Date_Datetime datetime
)
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT01')
INSERT INTO @.test(Date_Mmmyy)
VALUES('OCT04')
INSERT INTO @.test(Date_Mmmyy)
VALUES('AUG04')
SELECT *
FROM @.test
I would like to see...
OCT01 2001-09-30
OCT04 2004-09-30
AUG04 2004-08-31
Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
October is the 10th month not the 9th.
SELECT date_mmmyy,
dateadd(month,1,cast('01' + date_mmmyy as datetime))-1
FROM @.test
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjC8+IechKqOuFEgEQJEdQCeI7osM0fWzMiL
l/e3qENjIa25TV0AoKRZ
SYBhDB51p4ZJy+UuV2Sjy0Rk
=8ZVj
--END PGP SIGNATURE--
sql wrote:
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31|||> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
UPDATE @.test SET Date_Datetime=dateadd(day, -1, dateadd(month, 1,
'1'+Date_Mmmyy))
> SELECT *
> FROM @.test
>
Regards
Mr Tea
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>|||Thank you all for your replies. I got this working with your help.
"sql" <donotspam@.nospaml.com> wrote in message
news:eygksbbJFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I have Date_Mmmyy column in my table that stores dates in MMMYY format
> for example; "OCT01","OCT04","AUG04". I need to update another column in
> this table, which is DateTime, with the last day of the Date_Mmmyy value.
> Here is an example...
> DECLARE @.test table
> (
> Date_Mmmyy varchar(5),
> Date_Datetime datetime
> )
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT01')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('OCT04')
> INSERT INTO @.test(Date_Mmmyy)
> VALUES('AUG04')
> SELECT *
> FROM @.test
> I would like to see...
> OCT01 2001-09-30
> OCT04 2004-09-30
> AUG04 2004-08-31
> Thanks.
>
Last connect/update/modify date
Hi all,
Could anyone give me any information on how to obtain information from my SQL Server db's that will indicate the date of last connection/update/user activity?
Many thanks,
MeganThere are SP's and other ways to list some info about what's happening right now in the database, but I can't find anything about what happened earlier.
Maybe a log explorer tool could help with some parts.|||Thanks for the response :)|||Under Management -> Current Activity, will give you some information. You can also enable successful/failed login auditing - this is probably what you will need to enable as well as using sql profiler.|||The following is a good article:
article (http://www.ddart.net/mssql/sql2000/html/adminsql/ad_security_2ard.htm)|||Is the contents of that web site the same as in Books On-Line?|||> You can also enable successful/failed login auditing
Where do I do this?|||SQL profiler is the best tool for this - but you can also use auditing under the sql server properties -> security -> "Audit Level". Under SQL Profiler - Create a trace -> Events - you will see several auditing choices.|||Thanks to all who responded!
I ended up creating a custom trace template in Profiler and create my trace files based on it.
It's not awe-inspiring, but it's the only solution I've come across so far without looking at purchasing some kind of 3rd party log-miner/auditing software.
Could anyone give me any information on how to obtain information from my SQL Server db's that will indicate the date of last connection/update/user activity?
Many thanks,
MeganThere are SP's and other ways to list some info about what's happening right now in the database, but I can't find anything about what happened earlier.
Maybe a log explorer tool could help with some parts.|||Thanks for the response :)|||Under Management -> Current Activity, will give you some information. You can also enable successful/failed login auditing - this is probably what you will need to enable as well as using sql profiler.|||The following is a good article:
article (http://www.ddart.net/mssql/sql2000/html/adminsql/ad_security_2ard.htm)|||Is the contents of that web site the same as in Books On-Line?|||> You can also enable successful/failed login auditing
Where do I do this?|||SQL profiler is the best tool for this - but you can also use auditing under the sql server properties -> security -> "Audit Level". Under SQL Profiler - Create a trace -> Events - you will see several auditing choices.|||Thanks to all who responded!
I ended up creating a custom trace template in Profiler and create my trace files based on it.
It's not awe-inspiring, but it's the only solution I've come across so far without looking at purchasing some kind of 3rd party log-miner/auditing software.
Friday, March 9, 2012
Large(ish) update
I'm updating every row in a 200mb table. I'm trying to work out why it
running so slowly and have found that it increases the size of the log file
by 625mb an then empties it, I believe, but leaves it at the same size. I've
got the recovery mode set to simple which I thought was not meant to use the
log file. Can someone tell me what's going on and if there's some method of
speeding this up.
Thanks
MichaelHi Michael,
Simple recovery mode still uses the log file during the transaction, but
then truncates it after the transaction. The log file is used during the
transaction such that the transaction can be rolled back, if necessary (e.g.
if it hits an error or if you cancel the update halfway through). To battle
transaction log growth during large transactions, many people split the
transactions into batches.
Please refer to the following search for many threads related to this topic:
http://groups.google.com/groups?as_...r />
=2005&saf
e=off
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:evIv9OVzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> I'm updating every row in a 200mb table. I'm trying to work out why it
> running so slowly and have found that it increases the size of the log
> file by 625mb an then empties it, I believe, but leaves it at the same
> size. I've got the recovery mode set to simple which I thought was not
> meant to use the log file. Can someone tell me what's going on and if
> there's some method of speeding this up.
> Thanks
> Michael
>|||Every transaction is logged--even if you use the simple recovery mode. That
way the transaction can be rolled back.
To speed up the update you need to split it into smaller chunks. If the
individual updates are small enough, they will fit into the transaction log
without making it grow. I'm assuming that the growth is probably why the
update is running so slow.
How do you split it up? It depends. If you are setting a column to a
specific value, then you can use something like:
SET ROWCOUNT 1000
AGAIN:
UPDATE tableName SET columnName = <newColumnValue> WHERE columnName !=
<newColumnValue>
IF @.@.ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0
(Sometimes it's better to use > or < instead of != to speed things up even
more.)
There are many ways to do this, but I'd need a better description of what
you're trying to do and DDL for the table.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:evIv9OVzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> I'm updating every row in a 200mb table. I'm trying to work out why it
> running so slowly and have found that it increases the size of the log
> file by 625mb an then empties it, I believe, but leaves it at the same
> size. I've got the recovery mode set to simple which I thought was not
> meant to use the log file. Can someone tell me what's going on and if
> there's some method of speeding this up.
> Thanks
> Michael
>|||Hi,
its better to update records in smaller chunk then deleting as whole limit
row / record size for deleting in 1000 or as you like , so that you can
control T-LOG file size
Refer following thread :
http://www.sql-server-performance.c...?TOPIC_ID=10706
;-)
Regards
running so slowly and have found that it increases the size of the log file
by 625mb an then empties it, I believe, but leaves it at the same size. I've
got the recovery mode set to simple which I thought was not meant to use the
log file. Can someone tell me what's going on and if there's some method of
speeding this up.
Thanks
MichaelHi Michael,
Simple recovery mode still uses the log file during the transaction, but
then truncates it after the transaction. The log file is used during the
transaction such that the transaction can be rolled back, if necessary (e.g.
if it hits an error or if you cancel the update halfway through). To battle
transaction log growth during large transactions, many people split the
transactions into batches.
Please refer to the following search for many threads related to this topic:
http://groups.google.com/groups?as_...r />
=2005&saf
e=off
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:evIv9OVzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> I'm updating every row in a 200mb table. I'm trying to work out why it
> running so slowly and have found that it increases the size of the log
> file by 625mb an then empties it, I believe, but leaves it at the same
> size. I've got the recovery mode set to simple which I thought was not
> meant to use the log file. Can someone tell me what's going on and if
> there's some method of speeding this up.
> Thanks
> Michael
>|||Every transaction is logged--even if you use the simple recovery mode. That
way the transaction can be rolled back.
To speed up the update you need to split it into smaller chunks. If the
individual updates are small enough, they will fit into the transaction log
without making it grow. I'm assuming that the growth is probably why the
update is running so slow.
How do you split it up? It depends. If you are setting a column to a
specific value, then you can use something like:
SET ROWCOUNT 1000
AGAIN:
UPDATE tableName SET columnName = <newColumnValue> WHERE columnName !=
<newColumnValue>
IF @.@.ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0
(Sometimes it's better to use > or < instead of != to speed things up even
more.)
There are many ways to do this, but I'd need a better description of what
you're trying to do and DDL for the table.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:evIv9OVzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> I'm updating every row in a 200mb table. I'm trying to work out why it
> running so slowly and have found that it increases the size of the log
> file by 625mb an then empties it, I believe, but leaves it at the same
> size. I've got the recovery mode set to simple which I thought was not
> meant to use the log file. Can someone tell me what's going on and if
> there's some method of speeding this up.
> Thanks
> Michael
>|||Hi,
its better to update records in smaller chunk then deleting as whole limit
row / record size for deleting in 1000 or as you like , so that you can
control T-LOG file size
Refer following thread :
http://www.sql-server-performance.c...?TOPIC_ID=10706
;-)
Regards
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.
Subscribe to:
Posts (Atom)