Alternating INSERTs fail

In SQL Server 7, I have a stored procedure that does a simple INSERT. I call
it from ASP.NET.
It only succeeds every other time (i.e. alternating). No error is returned
when it doesn't work. A return value of 1 is always returned for both cases
(indicating 1 row affected) but the new data row simply isn't there exactly
every other time, alternating.
I even ran a trace, and I can see both inserts happen , and nothing else
comes along to delete any of the rows. But the problem persists.
The only difference that I see in the trace is the number of reads, and a
longer duration in the one that succeeds (the second one):
Event ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa0400225912014:57:14.380
Event ClassObject IDDatabase IDTextApplication NameNT User NameSQL
User NameCPUReadsWritesDurationConnection IDSPIDStart Time
+RPC:Completed9InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Providersa03017225912014:57:30.223
Can anyone suggest some more troubleshooting steps I can take here?
I've tried it with the stored procedure as:
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Begin Transaction
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
COMMIT Transaction
================================
and also
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
================================
Thanks,
Greg Holmes
You might try adding the sp:statement completed event to the trace. Do you
have any triggers on the table?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
> In SQL Server 7, I have a stored procedure that does a simple INSERT. I
> call
> it from ASP.NET.
> It only succeeds every other time (i.e. alternating). No error is
> returned
> when it doesn't work. A return value of 1 is always returned for both
> cases
> (indicating 1 row affected) but the new data row simply isn't there
> exactly
> every other time, alternating.
> I even ran a trace, and I can see both inserts happen , and nothing else
> comes along to delete any of the rows. But the problem persists.
> The only difference that I see in the trace is the number of reads, and a
> longer duration in the one that succeeds (the second one):
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
> Can anyone suggest some more troubleshooting steps I can take here?
>
> I've tried it with the stored procedure as:
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Begin Transaction
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> COMMIT Transaction
> ================================
> and also
>
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> ================================
>
> Thanks,
> Greg Holmes
|||"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace. Do you
> have any triggers on the table?
Thanks Dan. I added statement completed to the trace, but all that did was
add a
"sp:statement completed" line before each RPC line for the INSERTs. Those
"sp:statement completed" lines look identical.
This is so weird. The first field in the insert should be incrementing by
1s (by the ASP.NET application), but you can look at the rows and watch it go
up by 2s. I also added an auto incrementing field to the table and you can
watch the phenomenon there too ("1", "3", "5", etc.). It's actually
incrementing the auto-incrementing field, but not leaving a row in the
database, every other time.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
|||And I forgot to add - there are no triggers on the table.
"greg.holmes" wrote:

> "Dan Guzman" wrote:
>
> Thanks Dan. I added statement completed to the trace, but all that did was
> add a
> "sp:statement completed" line before each RPC line for the INSERTs. Those
> "sp:statement completed" lines look identical.
> This is so weird. The first field in the insert should be incrementing by
> 1s (by the ASP.NET application), but you can look at the rows and watch it go
> up by 2s. I also added an auto incrementing field to the table and you can
> watch the phenomenon there too ("1", "3", "5", etc.). It's actually
> incrementing the auto-incrementing field, but not leaving a row in the
> database, every other time.
>
|||OK, here's the only thing that worked to remedy this - as you might expect,
my confidence in the robustness of this solution is low!
1. Switch from using Stored Procedure to local text SQL query.
2. Add an auto-incrementing identity field to the database.
Has to do both. Neither worked by itself.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
|||Another thing you might try is adding Exception, OLEDB Errors and Attention
events to the trace. Out of curiosity, did you change the existing
InternetID column to an IDENTITY or did you add a new column?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...[vbcol=seagreen]
> OK, here's the only thing that worked to remedy this - as you might
> expect,
> my confidence in the robustness of this solution is low!
> 1. Switch from using Stored Procedure to local text SQL query.
> 2. Add an auto-incrementing identity field to the database.
> Has to do both. Neither worked by itself.
>
|||I added a new column. I confess I didn't try making InternetID an identity.
That might have worked.
"Dan Guzman" wrote:

> Another thing you might try is adding Exception, OLEDB Errors and Attention
> events to the trace. Out of curiosity, did you change the existing
> InternetID column to an IDENTITY or did you add a new column?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...
>
|||>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
I was curious about the data in the InternetID and the new IDENTITY column.
Does the InternetID still increment by 2? What about the IDENTITY col?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:B59141B9-485E-4BDC-969A-3CC1B23950D0@.microsoft.com...[vbcol=seagreen]
>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
> "Dan Guzman" wrote:
|||No, now that I'm using a text query in ASP.NET instead of a stored procedure,
and now that I have the identity column in the table, the INSERTs work as
expected. The identity column increments by 1, as does InternetID (if
sequential customers both create an account, which is what this table is for).
So by changing those two things, I must have somehow worked around an issue
that I don't understand, or perhaps an obscure bug.
"Dan Guzman" wrote:

> I was curious about the data in the InternetID and the new IDENTITY column.
> Does the InternetID still increment by 2? What about the IDENTITY col?
>

Alternating INSERTs fail

In SQL Server 7, I have a stored procedure that does a simple INSERT. I cal
l
it from ASP.NET.
It only succeeds every other time (i.e. alternating). No error is returned
when it doesn't work. A return value of 1 is always returned for both cases
(indicating 1 row affected) but the new data row simply isn't there exactly
every other time, alternating.
I even ran a trace, and I can see both inserts happen , and nothing else
comes along to delete any of the rows. But the problem persists.
The only difference that I see in the trace is the number of reads, and a
longer duration in the one that succeeds (the second one):
Event Class Object ID Database ID Text Application Name NT User Name SQL
User Name CPU Reads Writes Duration Connectio
n ID SPID Start Time
+RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
Event Class Object ID Database ID Text Application Name NT User Name SQL
User Name CPU Reads Writes Duration Connectio
n ID SPID Start Time
+RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
.Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
Can anyone suggest some more troubleshooting steps I can take here?
I've tried it with the stored procedure as:
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Begin Transaction
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
COMMIT Transaction
================================
and also
================================
CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
char(40), @.Password as char(15) AS
Insert into AccountLogin (InternetID, UserName, Password) Values
(@.InternetID, @.UserName, @.Password)
================================
Thanks,
Greg HolmesYou might try adding the sp:statement completed event to the trace. Do you
have any triggers on the table?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...
> In SQL Server 7, I have a stored procedure that does a simple INSERT. I
> call
> it from ASP.NET.
> It only succeeds every other time (i.e. alternating). No error is
> returned
> when it doesn't work. A return value of 1 is always returned for both
> cases
> (indicating 1 row affected) but the new data row simply isn't there
> exactly
> every other time, alternating.
> I even ran a trace, and I can see both inserts happen , and nothing else
> comes along to delete any of the rows. But the problem persists.
> The only difference that I see in the trace is the number of reads, and a
> longer duration in the one that succeeds (the second one):
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377864, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 4 0 0 22591 20 14:57:14.380
>
> Event Class Object ID Database ID Text Application Name NT User Name SQL
> User Name CPU Reads Writes Duration Connection ID SPID Start Time
> +RPC:Completed 9 InsertAccountLogin 377865, N'Dec12-Test7', N'Dec12-Test'
> .Net SqlClient Data Provider sa 0 3 0 17 22591 20 14:57:30.223
> Can anyone suggest some more troubleshooting steps I can take here?
>
> I've tried it with the stored procedure as:
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Begin Transaction
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> COMMIT Transaction
> ================================
> and also
>
> ================================
> CREATE PROCEDURE InsertAccountLogin @.InternetID as int, @.UserName as
> char(40), @.Password as char(15) AS
> Insert into AccountLogin (InternetID, UserName, Password) Values
> (@.InternetID, @.UserName, @.Password)
> ================================
>
> Thanks,
> Greg Holmes|||"Dan Guzman" wrote:

> You might try adding the sp:statement completed event to the trace. Do yo
u
> have any triggers on the table?
Thanks Dan. I added statement completed to the trace, but all that did was
add a
"sp:statement completed" line before each RPC line for the INSERTs. Those
"sp:statement completed" lines look identical.
This is so weird. The first field in the insert should be incrementing by
1s (by the ASP.NET application), but you can look at the rows and watch it g
o
up by 2s. I also added an auto incrementing field to the table and you can
watch the phenomenon there too ("1", "3", "5", etc.). It's actually
incrementing the auto-incrementing field, but not leaving a row in the
database, every other time.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...|||And I forgot to add - there are no triggers on the table.
"greg.holmes" wrote:

> "Dan Guzman" wrote:
>
> Thanks Dan. I added statement completed to the trace, but all that did wa
s
> add a
> "sp:statement completed" line before each RPC line for the INSERTs. Those
> "sp:statement completed" lines look identical.
> This is so weird. The first field in the insert should be incrementing by
> 1s (by the ASP.NET application), but you can look at the rows and watch it
go
> up by 2s. I also added an auto incrementing field to the table and you ca
n
> watch the phenomenon there too ("1", "3", "5", etc.). It's actually
> incrementing the auto-incrementing field, but not leaving a row in the
> database, every other time.
>
>|||OK, here's the only thing that worked to remedy this - as you might expect,
my confidence in the robustness of this solution is low!
1. Switch from using Stored Procedure to local text SQL query.
2. Add an auto-incrementing identity field to the database.
Has to do both. Neither worked by itself.
[vbcol=seagreen]
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:26B9E0DA-EFF4-4371-A14A-E3EF007FDD1F@.microsoft.com...|||Another thing you might try is adding Exception, OLEDB Errors and Attention
events to the trace. Out of curiosity, did you change the existing
InternetID column to an IDENTITY or did you add a new column?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...[vbcol=seagreen]
> OK, here's the only thing that worked to remedy this - as you might
> expect,
> my confidence in the robustness of this solution is low!
> 1. Switch from using Stored Procedure to local text SQL query.
> 2. Add an auto-incrementing identity field to the database.
> Has to do both. Neither worked by itself.
>|||I added a new column. I confess I didn't try making InternetID an identity.
That might have worked.
"Dan Guzman" wrote:

> Another thing you might try is adding Exception, OLEDB Errors and Attentio
n
> events to the trace. Out of curiosity, did you change the existing
> InternetID column to an IDENTITY or did you add a new column?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
> news:1D4547FA-94AE-466C-ABE5-741FA72D2970@.microsoft.com...
>|||>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
I was curious about the data in the InternetID and the new IDENTITY column.
Does the InternetID still increment by 2? What about the IDENTITY col?
Hope this helps.
Dan Guzman
SQL Server MVP
"greg.holmes" <gregholmes@.discussions.microsoft.com> wrote in message
news:B59141B9-485E-4BDC-969A-3CC1B23950D0@.microsoft.com...[vbcol=seagreen]
>I added a new column. I confess I didn't try making InternetID an
>identity.
> That might have worked.
> "Dan Guzman" wrote:
>|||No, now that I'm using a text query in ASP.NET instead of a stored procedure
,
and now that I have the identity column in the table, the INSERTs work as
expected. The identity column increments by 1, as does InternetID (if
sequential customers both create an account, which is what this table is for
).
So by changing those two things, I must have somehow worked around an issue
that I don't understand, or perhaps an obscure bug.
"Dan Guzman" wrote:

> I was curious about the data in the InternetID and the new IDENTITY column
.
> Does the InternetID still increment by 2? What about the IDENTITY col?
>sql

Alternate query to get around inner join/outer join at same time

Hi all,

As I am relearning SQL, I came upon a problem that anyone here could probably answer in their sleep. If someone could give me some help, it would be very appreciated!

I am working with three tables, Person, Affiliation, and Entity.

Person has data on people.
Entity has data on entities (like departments or groups) of which people are a part.
Affiliation is how the Person and Entity tables are linked - the Affiliation table contains rows with a PersonID and EntityID. So if Person 5 is part of Entity 6, there will be a record in the Affiliation table corresponding to that.

I want to SELECT a list all people as well as the Entities with which they are affiliated.

I tried doing this by inner joining Entity and Affiliation on Affiliation.EntityID = Entity.EntityID, and then outer joining Person.PersonID *= Affiliation.PersonID to the results (since I want all people returned, even if they are not affiliated with any entities). But when I try this, I get the error

The table 'Affiliation' is an inner member of an outer-join clause. This is not allowed if the table also participates in a regular join clause.

Ok, I realize now that you can't have tables in an inner and outer join in the same query. But is there some solution that will allow me to pull this data in the way I want, without creating a temporary table?

Thanks in advance for the help!Oops, I think I figured out a solution - using outer joins to join Affiliation to Entity and Person.

Seems amazingly obvious. This is what happens when you try working on a database after midnight - obvious things take you hours to realize.

Josh

altering columns performance issues

Good Day:
When altering column width in SQL2000SP3a we find that this is taking a long
running time on tables with many rows.
We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
the data should not need to be transformed, only the column altered.
There are some 20 million rows and some 200 columns that need this
alteration.
Any thoughts on how to speed this up?
We could create new tables and copy the data, but that requires all user
activity to be stopped, and that is not palatable.
I note that Oracle can do this sort of an alter instantly, for some reason.
THANKS
Mr. Lynn Teska
Mayo ClinicHi Lynn
I just wrote an article on ALTER TABLE for SQL Server Magazine, and why some
changes take a long time and other don't. Unfortunately, it won't appear
until January, so I'll give you a sneak preview.
You are right that the data doesn't need to be transformed, but I'm not sure
what you mean by 'only the column altered'. Yes, the column needs to be
altered, and in every single row in the table. For some changes, like
changing to a smaller datatype, only the metadata needs to be changed (but
the existing data does need to be validated to make sure it 'fits' into the
smaller type.)
If you alter a column's datatype to one that needs more storage space, SQL
Server will actually make the physical change to every row to allow the
additional bytes that are needed to store numeric(8,3).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a
long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some
reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||In addition to Kalen's response:
Watch out if you use EM for this. EM generally creates a new table, copy the data etc etc instead of
executing an ALTER TABLE tblname ALTER COLUMN colname command.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lynn Teska" <lteska@.mayo.edu> wrote in message news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||Good Day:
Well here is a hypothetical situation:
We have a 24x7x365 app with a particular table that has some 200
numeric(7,2) columns.
There are a number of other columns with constraints and indexes, but none
of the numeric (7,2) columns is indexed or constrained.
The applications store new data in the table every 1 minute, or thereabouts.
There are 25 Million rows in the table.
We need to widen all the numeric (7,2) columns to numeric (8,3).
We need to minimize or eliminate application outage.
SQL server is doing a huge amount of I/O to alter the columns.
SQL server appears to only be able to alter one column at a time.
Now when I add new columns SQL server is quick as you please.
What strategy would this group think best to accomplish the task of widening
the columns?
Thanks for any insight!
Lynn Teska
Mayo Clinic
--
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyk5vqrnDHA.688@.TK2MSFTNGP10.phx.gbl...
> In addition to Kalen's response:
> Watch out if you use EM for this. EM generally creates a new table, copy
the data etc etc instead of
> executing an ALTER TABLE tblname ALTER COLUMN colname command.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > Good Day:
> >
> > When altering column width in SQL2000SP3a we find that this is taking a
long
> > running time on tables with many rows.
> > We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> > the data should not need to be transformed, only the column altered.
> > There are some 20 million rows and some 200 columns that need this
> > alteration.
> >
> > Any thoughts on how to speed this up?
> > We could create new tables and copy the data, but that requires all user
> > activity to be stopped, and that is not palatable.
> >
> > I note that Oracle can do this sort of an alter instantly, for some
reason.
> >
> > THANKS
> > Mr. Lynn Teska
> > Mayo Clinic
> >
> >
>|||Hi Lynn,
I would like to thank Kalen and Tibor for their help. As I understand, you
want to widen all the numeric (7,2) columns to numeric (8,3) in the table
on the machine. If I have misunderstood, please feel free to let me know.
To change the schema of the table, we can perform SQL statements using
Query Analyzer or Change the table schema directly in SQL Server Enterprise
Manager. Because Enterprise Manager will create a new tale and drop the
original table for changing the schema, I think it is better to use Query
Analyzer with ALTER TABLE statement.
Example:
alter table <table name> alter column <column name> numeric(8,3)
For additional information regarding ALTER TABLE, please refer to the
following article on SQL Server Books Online.
Topic: "ALTER TABLE"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I understand how to do the task with the EM or TSQL.
The problem is the time it takes with 25 million rows is not acceptable in
at 24x7x365 system.
TSQL can only alter one column at a time, and with 200 columns to do that is
not so good.
EM makes copies as you note and that is not efficient in terms of disk i/o.
I am trying to get a strategy that will minimize or eliminate down time and
blocking of the table.
Interestingly Oracle can do this sort of alteration instantly, according to
our vendor.
Thanks
Lynn Teska
Mayo Clinic
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lynn,
> I would like to thank Kalen and Tibor for their help. As I understand, you
> want to widen all the numeric (7,2) columns to numeric (8,3) in the table
> on the machine. If I have misunderstood, please feel free to let me know.
> To change the schema of the table, we can perform SQL statements using
> Query Analyzer or Change the table schema directly in SQL Server
Enterprise
> Manager. Because Enterprise Manager will create a new tale and drop the
> original table for changing the schema, I think it is better to use Query
> Analyzer with ALTER TABLE statement.
> Example:
> alter table <table name> alter column <column name> numeric(8,3)
> For additional information regarding ALTER TABLE, please refer to the
> following article on SQL Server Books Online.
> Topic: "ALTER TABLE"
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Lynn
The vendors says Oracle can do this instantly, but you've never tried it
yourself?
I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
instantly too, that's why you come to a Technical forum to find out. Have
you asked this on an Oracle Technical forum?
As I mentioned, there are some kinds of ALTER TABLEs that can be done
instantly, and there is a lot of confusion, so someone who had not
researched this issue in technical detail could easily tell you that this
was an instantaneous operation, even on SQL Server.
You are wanting to physically change 25 million rows? How could this
possible be an instantaneous operation?
One faster way you might consider is to create a new table using select
into, which is a very fast command, and then dropping the old table, and
renaming the new one to the old name.
SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
col2 ...
INTO newtable
FROM original_table
GO
--recreate constraints, indexes etc on newtable
DROP original_table
EXEC sp_rename newtable, original_table
Good Luck!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I understand how to do the task with the EM or TSQL.
> The problem is the time it takes with 25 million rows is not acceptable in
> at 24x7x365 system.
> TSQL can only alter one column at a time, and with 200 columns to do that
is
> not so good.
> EM makes copies as you note and that is not efficient in terms of disk
i/o.
> I am trying to get a strategy that will minimize or eliminate down time
and
> blocking of the table.
> Interestingly Oracle can do this sort of alteration instantly, according
to
> our vendor.
> Thanks
> Lynn Teska
> Mayo Clinic
>
> "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > Hi Lynn,
> >
> > I would like to thank Kalen and Tibor for their help. As I understand,
you
> > want to widen all the numeric (7,2) columns to numeric (8,3) in the
table
> > on the machine. If I have misunderstood, please feel free to let me
know.
> >
> > To change the schema of the table, we can perform SQL statements using
> > Query Analyzer or Change the table schema directly in SQL Server
> Enterprise
> > Manager. Because Enterprise Manager will create a new tale and drop the
> > original table for changing the schema, I think it is better to use
Query
> > Analyzer with ALTER TABLE statement.
> >
> > Example:
> >
> > alter table <table name> alter column <column name> numeric(8,3)
> >
> > For additional information regarding ALTER TABLE, please refer to the
> > following article on SQL Server Books Online.
> > Topic: "ALTER TABLE"
> >
> > Please feel free to post in the group if this solves your problem or if
> you
> > would like further assistance.
> >
> > Regards,
> >
> > Michael Shao
> > Microsoft Online Partner Support
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> >
>|||Thanks for the info.
The vended product runs on both SQL server and Oracle, I have no reason to
doubt that the change is fast on Oracle.
They seem to be experienced in both platforms.
Thanks for your suggestion I will have a look at it.
Lynn Teska
PS:
This is an expert of a note I received from an Oracle expert:
Oracle always reserves a minimum of space in every database block for
updates, determined by the PCTFREE parameter (10% of the block size by
default, which we don't modify). Hence, Oracle does not have to reserve any
extra space, it's already there. Initially the old rows will surely fit
inside the new definition, as we have just widened the columns. If some
value needs some extra space, it will use the PCTFREE space. If this is not
enough, a phenomenon called row chaining will take place, as part of the row
will have to be stored in another block, and this is not so good for
performance, but it can be attacked later on. In our case this would never
happen as we won't go updating old values.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||All the more interesting since numeric (7,2) and numeric (8,3) both appear
to be stored in 5 bytes.
Lynn
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||Yes, there does appear to be some unexpected behavior going on. I am still
researching this issue.
OTOH, I have just learned that although you are correct, that Oracle does
make this change and all ALTER TABLE changes instantly, as only a metadata
change, the tradeoff is that actual row size adjustment must be made during
actual data use. In fact, if you change the datatype size to something very
different, or smaller, like char(5) to char(3) or char to int, Oracle will
not do any validation at the time of the alter table, but will wait until
your read the row. So you can end up getting conversion errors just by
reading data, which can be very problematic.
So, like many choices, there is a tradeoff. Normally, since altering tables
is a rare occurance, but updating and modifying data is done during
production operations, I'd rather the overhead was accrued during the alter,
when I can plan for it.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> All the more interesting since numeric (7,2) and numeric (8,3) both appear
> to be stored in 5 bytes.
> Lynn
> --
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > Lynn
> >
> > The vendors says Oracle can do this instantly, but you've never tried it
> > yourself?
> >
> > I wouldn't be surprised if a SQL Server vendor said SQL Server could do
it
> > instantly too, that's why you come to a Technical forum to find out.
Have
> > you asked this on an Oracle Technical forum?
> >
> > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > instantly, and there is a lot of confusion, so someone who had not
> > researched this issue in technical detail could easily tell you that
this
> > was an instantaneous operation, even on SQL Server.
> >
> > You are wanting to physically change 25 million rows? How could this
> > possible be an instantaneous operation?
> >
> > One faster way you might consider is to create a new table using select
> > into, which is a very fast command, and then dropping the old table, and
> > renaming the new one to the old name.
> >
> > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
as
> > col2 ...
> > INTO newtable
> > FROM original_table
> > GO
> >
> > --recreate constraints, indexes etc on newtable
> >
> > DROP original_table
> >
> > EXEC sp_rename newtable, original_table
> >
> > Good Luck!
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I understand how to do the task with the EM or TSQL.
> > > The problem is the time it takes with 25 million rows is not
acceptable
> in
> > > at 24x7x365 system.
> > >
> > > TSQL can only alter one column at a time, and with 200 columns to do
> that
> > is
> > > not so good.
> > > EM makes copies as you note and that is not efficient in terms of disk
> > i/o.
> > > I am trying to get a strategy that will minimize or eliminate down
time
> > and
> > > blocking of the table.
> > >
> > > Interestingly Oracle can do this sort of alteration instantly,
according
> > to
> > > our vendor.
> > >
> > > Thanks
> > >
> > > Lynn Teska
> > > Mayo Clinic
> > >
> > >
> > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > Hi Lynn,
> > > >
> > > > I would like to thank Kalen and Tibor for their help. As I
understand,
> > you
> > > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> > table
> > > > on the machine. If I have misunderstood, please feel free to let me
> > know.
> > > >
> > > > To change the schema of the table, we can perform SQL statements
using
> > > > Query Analyzer or Change the table schema directly in SQL Server
> > > Enterprise
> > > > Manager. Because Enterprise Manager will create a new tale and drop
> the
> > > > original table for changing the schema, I think it is better to use
> > Query
> > > > Analyzer with ALTER TABLE statement.
> > > >
> > > > Example:
> > > >
> > > > alter table <table name> alter column <column name> numeric(8,3)
> > > >
> > > > For additional information regarding ALTER TABLE, please refer to
the
> > > > following article on SQL Server Books Online.
> > > > Topic: "ALTER TABLE"
> > > >
> > > > Please feel free to post in the group if this solves your problem or
> if
> > > you
> > > > would like further assistance.
> > > >
> > > > Regards,
> > > >
> > > > Michael Shao
> > > > Microsoft Online Partner Support
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > >
> > >
> > >
> >
> >
>|||OK Thanks.
Let me know what you find.
A 4 hour outage is not a plus for us.
Thanks
Lynn Teska
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Jo3UejoDHA.3040@.TK2MSFTNGP11.phx.gbl...
> Yes, there does appear to be some unexpected behavior going on. I am still
> researching this issue.
> OTOH, I have just learned that although you are correct, that Oracle does
> make this change and all ALTER TABLE changes instantly, as only a metadata
> change, the tradeoff is that actual row size adjustment must be made
during
> actual data use. In fact, if you change the datatype size to something
very
> different, or smaller, like char(5) to char(3) or char to int, Oracle will
> not do any validation at the time of the alter table, but will wait until
> your read the row. So you can end up getting conversion errors just by
> reading data, which can be very problematic.
> So, like many choices, there is a tradeoff. Normally, since altering
tables
> is a rare occurance, but updating and modifying data is done during
> production operations, I'd rather the overhead was accrued during the
alter,
> when I can plan for it.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > All the more interesting since numeric (7,2) and numeric (8,3) both
appear
> > to be stored in 5 bytes.
> >
> > Lynn
> >
> > --
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > > Lynn
> > >
> > > The vendors says Oracle can do this instantly, but you've never tried
it
> > > yourself?
> > >
> > > I wouldn't be surprised if a SQL Server vendor said SQL Server could
do
> it
> > > instantly too, that's why you come to a Technical forum to find out.
> Have
> > > you asked this on an Oracle Technical forum?
> > >
> > > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > > instantly, and there is a lot of confusion, so someone who had not
> > > researched this issue in technical detail could easily tell you that
> this
> > > was an instantaneous operation, even on SQL Server.
> > >
> > > You are wanting to physically change 25 million rows? How could this
> > > possible be an instantaneous operation?
> > >
> > > One faster way you might consider is to create a new table using
select
> > > into, which is a very fast command, and then dropping the old table,
and
> > > renaming the new one to the old name.
> > >
> > > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
> as
> > > col2 ...
> > > INTO newtable
> > > FROM original_table
> > > GO
> > >
> > > --recreate constraints, indexes etc on newtable
> > >
> > > DROP original_table
> > >
> > > EXEC sp_rename newtable, original_table
> > >
> > > Good Luck!
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I understand how to do the task with the EM or TSQL.
> > > > The problem is the time it takes with 25 million rows is not
> acceptable
> > in
> > > > at 24x7x365 system.
> > > >
> > > > TSQL can only alter one column at a time, and with 200 columns to do
> > that
> > > is
> > > > not so good.
> > > > EM makes copies as you note and that is not efficient in terms of
disk
> > > i/o.
> > > > I am trying to get a strategy that will minimize or eliminate down
> time
> > > and
> > > > blocking of the table.
> > > >
> > > > Interestingly Oracle can do this sort of alteration instantly,
> according
> > > to
> > > > our vendor.
> > > >
> > > > Thanks
> > > >
> > > > Lynn Teska
> > > > Mayo Clinic
> > > >
> > > >
> > > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in
message
> > > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > > Hi Lynn,
> > > > >
> > > > > I would like to thank Kalen and Tibor for their help. As I
> understand,
> > > you
> > > > > want to widen all the numeric (7,2) columns to numeric (8,3) in
the
> > > table
> > > > > on the machine. If I have misunderstood, please feel free to let
me
> > > know.
> > > > >
> > > > > To change the schema of the table, we can perform SQL statements
> using
> > > > > Query Analyzer or Change the table schema directly in SQL Server
> > > > Enterprise
> > > > > Manager. Because Enterprise Manager will create a new tale and
drop
> > the
> > > > > original table for changing the schema, I think it is better to
use
> > > Query
> > > > > Analyzer with ALTER TABLE statement.
> > > > >
> > > > > Example:
> > > > >
> > > > > alter table <table name> alter column <column name> numeric(8,3)
> > > > >
> > > > > For additional information regarding ALTER TABLE, please refer to
> the
> > > > > following article on SQL Server Books Online.
> > > > > Topic: "ALTER TABLE"
> > > > >
> > > > > Please feel free to post in the group if this solves your problem
or
> > if
> > > > you
> > > > > would like further assistance.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Michael Shao
> > > > > Microsoft Online Partner Support
> > > > > Get Secure! - www.microsoft.com/security
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

alter view question

how do I find out when was the last time view got modified.You cannot do that in SQL Server 2000
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
>|||"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
You can't (unless SQL 2005 handles this).
You only have the date it was created.
You can:
drop the view and re-create it when you do a modification
add comments to the view about changes
keep the information in a table
use a third party code source tool|||SQL Server 2000 does not store this information. You can garner this
information from a trace, if you want to leave a lightweight one running; or
from 3rd party tools such as Lumigent's Entegra ("who did what to which data
when?" is their catch-slogan).
If you are using SQL Server 2005,
SELECT modify_date FROM sys.views WHERE name='view_name'
(And in 2005 if you want more information, such as who modified it, what app
they used, etc. then you can set up DDL triggers.)
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
>|||I was wondering if I could use trigger to audit the modified date of view,
as trigger is allowed on a view.
"Aaron Bertrand [SQL Server MVP]" wrote:

> SQL Server 2000 does not store this information. You can garner this
> information from a trace, if you want to leave a lightweight one running;
or
> from 3rd party tools such as Lumigent's Entegra ("who did what to which da
ta
> when?" is their catch-slogan).
> If you are using SQL Server 2005,
> SELECT modify_date FROM sys.views WHERE name='view_name'
> (And in 2005 if you want more information, such as who modified it, what a
pp
> they used, etc. then you can set up DDL triggers.)
>
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
>
>|||>I was wondering if I could use trigger to audit the modified date of view,
> as trigger is allowed on a view.
That will fire when the actual DATA changes, not the DEFINITION.|||i don't have sql 2005 installed, but out of curiousity, to find out other
objects' modification date, do you just go to: sys.tables, sys.triggers,
sys.functions, etc?
if I use following query,Can I find out when procedure is last altered in
sql 2005?
I know in sql 2000, last_altered is "fake" column ( data is same as created
date).
but I hope sql 2005 is not like that again.
select routine_name, last_altered from information_schema.routines
"Aaron Bertrand [SQL Server MVP]" wrote:

> That will fire when the actual DATA changes, not the DEFINITION.
>
>|||> I know in sql 2000, last_altered is "fake" column ( data is same as
> created
> date).
> but I hope sql 2005 is not like that again.
> select routine_name, last_altered from information_schema.routines
LAST_ALTERED is correct and accurate in SQL Server 2005, however I urge you
to use the catalog views instead.|||I'm not sure if I understand the original request fully, but in 2005 you can
do DDL triggers that will fire off create, alter, drop statements on all
sorts of meta-data type events. Things like CREATE_TRIGGER (triggers on
your triggers?), ALTER_TABLE, etc. Just do search at the MSDN website for
DDL triggers.
Clint
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ecLMVN2%23FHA.2040@.TK2MSFTNGP14.phx.gbl...
> That will fire when the actual DATA changes, not the DEFINITION.
>|||> I'm not sure if I understand the original request fully, but in 2005 you
> can do DDL triggers that will fire off create, alter, drop statements on
> all sorts of meta-data type events.
Yes, I suggested that, however I am not sure the user is using SQL Server
2005.sql

alter table - change time evaluation

Hi,

I have a table with 70 million records. I want to change the datatype of one of the columns from int to decimal(18,3). What is the best way of doing it? and how can I evaluate the time change before I start and make the change?

thank you,

Tomer

..assuming you want to know how long the operation would take....this varies massively by table definition + indexes + hardware + load

you could use SELECT INTO to create a table of one million records and add indexes identical to those on the production table.

Then drop the index on the int column on that table,

execute an ALTER TABLE ALTER COLUMN to change the datatype

and rebuild the index.

Then multiply the result by 70 though that's only an estimate as the rebuild time may not be linear.

Alter Table

Hello dudes, is there any way to change more than one column at same time, using an ALTER TABLE statement?

Regards,
lottalavaYes use Alter table|||Hi!
You may use following codes.

Alter 'column name1'.... from table

alter 10 tables at the same time

Hi,
In my SQL Server 2000, I have 10 tables which all have the same structure,
the only exception is that they have different table names, e.g., CompanyA,
CompanyB, ..., CompanyJ.
Now I need to add one column in all Company table, is it possible to do it
in one time, instead of doing them 10 times?
Thanks for help.
JasonJason
Pehaps , you want to create only ONE table for companies.
CREATE TABLE Companies
(
CompanyId INT NOT NULL PRIMARY KEY,
CompanyName VARCHAR(50) NOT NULL,
....
)
So , if you still persist in your option ,use
CREATE TABLE T1 (c int) --Replace with the real names
CREATE TABLE T2 (c int)
CREATE TABLE T3 (c int)
GO
SELECT
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 10 tables which all have the same structure,
> the only exception is that they have different table names, e.g.,
> CompanyA, CompanyB, ..., CompanyJ.
> Now I need to add one column in all Company table, is it possible to do it
> in one time, instead of doing them 10 times?
> Thanks for help.
>
> Jason
>|||Do you mean in terms of a transaction or of one statement. One
statement is no possible, you will have do that in spearate statements.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...[vbco
l=seagreen]
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>[/vbcol]|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...[vbco
l=seagreen]
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>[/vbcol]|||Uri posted example code that you have to modify to suit your needs. The code
is executed anywhere
from where you can execute a SQL query, like for instance Query Analyzer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:uKE38k54GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks Uri.
> But how do I execute these script?
>
> "Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP
03.phx.gbl...
>

alter 10 tables at the same time

Hi,
In my SQL Server 2000, I have 10 tables which all have the same structure,
the only exception is that they have different table names, e.g., CompanyA,
CompanyB, ..., CompanyJ.
Now I need to add one column in all Company table, is it possible to do it
in one time, instead of doing them 10 times?
Thanks for help.
Jason
Jason
Pehaps , you want to create only ONE table for companies.
CREATE TABLE Companies
(
CompanyId INT NOT NULL PRIMARY KEY,
CompanyName VARCHAR(50) NOT NULL,
.....
)
So , if you still persist in your option ,use
CREATE TABLE T1 (c int) --Replace with the real names
CREATE TABLE T2 (c int)
CREATE TABLE T3 (c int)
GO
SELECT
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 10 tables which all have the same structure,
> the only exception is that they have different table names, e.g.,
> CompanyA, CompanyB, ..., CompanyJ.
> Now I need to add one column in all Company table, is it possible to do it
> in one time, instead of doing them 10 times?
> Thanks for help.
>
> Jason
>
|||Do you mean in terms of a transaction or of one statement. One
statement is no possible, you will have do that in spearate statements.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>
|||Uri posted example code that you have to modify to suit your needs. The code is executed anywhere
from where you can execute a SQL query, like for instance Query Analyzer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:uKE38k54GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks Uri.
> But how do I execute these script?
>
> "Uri Dimant" <urid@.iscar.co.il> glsD:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.g bl...
>

alter 10 tables at the same time

Hi,
In my SQL Server 2000, I have 10 tables which all have the same structure,
the only exception is that they have different table names, e.g., CompanyA,
CompanyB, ..., CompanyJ.
Now I need to add one column in all Company table, is it possible to do it
in one time, instead of doing them 10 times?
Thanks for help.
JasonJason
Pehaps , you want to create only ONE table for companies.
CREATE TABLE Companies
(
CompanyId INT NOT NULL PRIMARY KEY,
CompanyName VARCHAR(50) NOT NULL,
.....
)
So , if you still persist in your option ,use
CREATE TABLE T1 (c int) --Replace with the real names
CREATE TABLE T2 (c int)
CREATE TABLE T3 (c int)
GO
SELECT
N'ALTER TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
GO
DROP TABLE T1,T2,T3
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
> Hi,
> In my SQL Server 2000, I have 10 tables which all have the same structure,
> the only exception is that they have different table names, e.g.,
> CompanyA, CompanyB, ..., CompanyJ.
> Now I need to add one column in all Company table, is it possible to do it
> in one time, instead of doing them 10 times?
> Thanks for help.
>
> Jason
>|||Do you mean in terms of a transaction or of one statement. One
statement is no possible, you will have do that in spearate statements.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> In my SQL Server 2000, I have 10 tables which all have the same
>> structure, the only exception is that they have different table names,
>> e.g., CompanyA, CompanyB, ..., CompanyJ.
>> Now I need to add one column in all Company table, is it possible to do
>> it in one time, instead of doing them 10 times?
>> Thanks for help.
>>
>> Jason
>|||Thanks Uri.
But how do I execute these script?
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
> Jason
> Pehaps , you want to create only ONE table for companies.
> CREATE TABLE Companies
> (
> CompanyId INT NOT NULL PRIMARY KEY,
> CompanyName VARCHAR(50) NOT NULL,
> .....
> )
>
> So , if you still persist in your option ,use
>
> CREATE TABLE T1 (c int) --Replace with the real names
> CREATE TABLE T2 (c int)
> CREATE TABLE T3 (c int)
> GO
> SELECT
> N'ALTER TABLE ' +
> QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
> FROM
> INFORMATION_SCHEMA.TABLES
> WHERE
> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
> N'.' +
> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>
> GO
>
> DROP TABLE T1,T2,T3
> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> In my SQL Server 2000, I have 10 tables which all have the same
>> structure, the only exception is that they have different table names,
>> e.g., CompanyA, CompanyB, ..., CompanyJ.
>> Now I need to add one column in all Company table, is it possible to do
>> it in one time, instead of doing them 10 times?
>> Thanks for help.
>>
>> Jason
>|||Uri posted example code that you have to modify to suit your needs. The code is executed anywhere
from where you can execute a SQL query, like for instance Query Analyzer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
news:uKE38k54GHA.696@.TK2MSFTNGP06.phx.gbl...
> Thanks Uri.
> But how do I execute these script?
>
> "Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:OMnZOWh4GHA.3600@.TK2MSFTNGP03.phx.gbl...
>> Jason
>> Pehaps , you want to create only ONE table for companies.
>> CREATE TABLE Companies
>> (
>> CompanyId INT NOT NULL PRIMARY KEY,
>> CompanyName VARCHAR(50) NOT NULL,
>> .....
>> )
>>
>> So , if you still persist in your option ,use
>>
>> CREATE TABLE T1 (c int) --Replace with the real names
>> CREATE TABLE T2 (c int)
>> CREATE TABLE T3 (c int)
>> GO
>> SELECT
>> N'ALTER TABLE ' +
>> QUOTENAME(TABLE_SCHEMA) +
>> N'.' +
>> QUOTENAME(TABLE_NAME)+ ' ADD c2 INT'
>> FROM
>> INFORMATION_SCHEMA.TABLES
>> WHERE
>> TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE 'T%'AND
>> OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
>> N'.' +
>> QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
>>
>> GO
>>
>> DROP TABLE T1,T2,T3
>> "Jason Huang" <JasonHuang8888@.hotmail.com> wrote in message
>> news:%23471BFh4GHA.2208@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> In my SQL Server 2000, I have 10 tables which all have the same structure, the only exception is
>> that they have different table names, e.g., CompanyA, CompanyB, ..., CompanyJ.
>> Now I need to add one column in all Company table, is it possible to do it in one time, instead
>> of doing them 10 times?
>> Thanks for help.
>>
>> Jason
>>
>

Allowing Transformations when Creating Publication for Replication

I am at my wits end here. For Replication the Books Online clearly state:

"The option to allow transformations is set at the time you create a publication"

However, I cannot find any options that allow me to do this in the Create Publication Wizard.

Once the Publication has been created I see in the Properties in the Subscription Options tab that "Use DTS to transform data before distributing it to a Subscriber" is set to No and there is no way to change it.

Where am I going wrong?I'd actually like to know the EXACT same thing. I'm trying to use replciation and need only to do some transformations to the data, but as you mention that option is greyed out.

All Required Data Is Not Immediately Available - What to do?

What are some acceptable ways to deal with "required" data that is
unavailable at the time a row is added to a table?
Example: Lab tests are requested for a patient. Some results come in right
away and others come days later. All are eventually required in order for
the patient's lab records to be completed. It is unacceptable to the
customer to wait until ALL the values are available; they want to enter the
values AS they become available.
Obviously we cannot place a "NOT NULL" constraint on the columns in order to
allow for required data coming in late. I'd prefer to avoid having
application-level logic take care of this; but I currently don't see a way
around that.
Suggestions?
Thanks!"Fred Mertz" <A@.B.COM> wrote in message
news:%23DOJXeumGHA.856@.TK2MSFTNGP03.phx.gbl...
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter
> the values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
> to allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!
>
Like this for example:
CREATE TABLE Patients (PatientID INT NOT NULL PRIMARY KEY, PatientLastName
VARCHAR(35) NOT NULL /* ... etc */);
CREATE TABLE PatientTestResults (PatientID INT NOT NULL PRIMARY KEY
REFERENCES Patients (PatientID), Result VARCHAR(20) NOT NULL);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||So it sounds as if you want to enforce data validation before a status
can be changed or checked. If the process of changing status is manual
(i.e, a technician clicks a button that "signs off" on the data), you
could use a trigger to verify that data is complete before allowing
that status to be changed.
If the process is automatic (you just want to check if the status is
complete), you could run a check process in your stored procedure to
look for NULL tuples and report that status is Incomplete.
Tossing some ideas around.
Stu
Fred Mertz wrote:
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter th
e
> values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
to
> allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!|||RE:
<< Tossing some ideas around >>
Just what I was looking for. Your ideas are helpful.|||Just a guess here -REMOVE the NOT NULL constraint?
CREATE a 'holding' table that accepts the incomplete data, and move it to
the 'real' table when complete.
Alternatively, set a default value. If char/varchar datatype default to
'N/A' or whatever makes sense. If numeric datatype, and if zero has
significance, then default to a predetermined 'magic number' that signals
the data is still missing, e.g., 999.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Fred Mertz" <A@.B.COM> wrote in message
news:%23DOJXeumGHA.856@.TK2MSFTNGP03.phx.gbl...
> What are some acceptable ways to deal with "required" data that is
> unavailable at the time a row is added to a table?
> Example: Lab tests are requested for a patient. Some results come in right
> away and others come days later. All are eventually required in order for
> the patient's lab records to be completed. It is unacceptable to the
> customer to wait until ALL the values are available; they want to enter
> the values AS they become available.
> Obviously we cannot place a "NOT NULL" constraint on the columns in order
> to allow for required data coming in late. I'd prefer to avoid having
> application-level logic take care of this; but I currently don't see a way
> around that.
> Suggestions?
> Thanks!
>

All procedure in a stored procedure not being exec

I have a stored procedure that is called but it seems that not all the
Stored procedures are run. If I execute it the second time then they seem to
run after that.
Am I missing something, Like added a wait to finish clause?
Stephen K. Miyasato
MDsync
CREATE PROCEDURE FLG_UpdateFlags
/*Stored Procedure to update All the Flags*/
@.PatNo int
AS
--Put optional Flag procedures here
--
--Procedure to delete FOBT if Colonoscopy duedate is > todays or to be done
Exec FLG_FOBT @.PatNo
--procedure to delete old flags Set LastDate to Visit date in Vistal Signs
Exec FLG_OfficeVisit @.PatNo
-- Update Labs based on sort and ProtScr
Exec FLG_LabDateCholesterol @.PatNoStephen K. Miyasato (miyasat@.flex.com) writes:
> I have a stored procedure that is called but it seems that not all the
> Stored procedures are run. If I execute it the second time then they
> seem to run after that.
How do you conclude this? From where do you run the procedure? Have you
traced the procedure in Profiler to see what is going on?
The only thing I can guess on is that first time you run the procedure,
there is an error that causes the batch to be aborted, but this does
for some reason not happen when you re-run the procedure.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

All Inserts/Updates halted for a 1 minute time period. Looking for a reason why.

We are running SQL 2000 SP3. In looking at a SQL Trace, I noticed that for
a 1 minute time period - there were SQL statements that all finished within
about 10 milliseconds of each other. I immediately suspected blocking as
the culprit but I am 100% sure that blocking was not the cause (I had a
"blocker script" running continuously looking for blocking and nothing
showed up).
When I looked a little closer - I noticed that ALL of these 8 SQL statements
were either Inserting or Updating. All 8 SQL statements were each touching
different tables and were in very small units of work. In looking at the
READS, WRITES and CPU for each of these statements they were all very low.
Again - I have to emphasize there was no database blocking involved.
Furthermore - during this time period I was able to determine that of all
the ~ 6200 SQL statements that were started, these 8 SQL statements were the
only ones updating. All other SL statements were reading only.
This means that for a one-minute time period all Update/Insert activity was
"halted" (without database blocking occurring). Does anyone have an
explanation as to why this could have occurred? Could it be a problem with
cache or transaction log backup?
Also - wanted to indicate that the transaction log backup was not
interfering with this time period. I checked msdb.backupset and I can see
that this questionable time period did not coincide with any t-log backup.
Looking for some advice... Maybe there are some pertainant Perfmon
counters that I could enable?
Thanks in advance!Hi
If you were using sp_blocker_pss80 you may want to decrease the time in your
delay between calls.
You may also want to trace lock escalations, timeouts and deadlocks in
profiler along with events in the audit category such as backup/restore and
DBCC events. Also check out any file expansions in performance monitor.
John
"TJT" wrote:
> We are running SQL 2000 SP3. In looking at a SQL Trace, I noticed that for
> a 1 minute time period - there were SQL statements that all finished within
> about 10 milliseconds of each other. I immediately suspected blocking as
> the culprit but I am 100% sure that blocking was not the cause (I had a
> "blocker script" running continuously looking for blocking and nothing
> showed up).
> When I looked a little closer - I noticed that ALL of these 8 SQL statements
> were either Inserting or Updating. All 8 SQL statements were each touching
> different tables and were in very small units of work. In looking at the
> READS, WRITES and CPU for each of these statements they were all very low.
> Again - I have to emphasize there was no database blocking involved.
> Furthermore - during this time period I was able to determine that of all
> the ~ 6200 SQL statements that were started, these 8 SQL statements were the
> only ones updating. All other SL statements were reading only.
> This means that for a one-minute time period all Update/Insert activity was
> "halted" (without database blocking occurring). Does anyone have an
> explanation as to why this could have occurred? Could it be a problem with
> cache or transaction log backup?
> Also - wanted to indicate that the transaction log backup was not
> interfering with this time period. I checked msdb.backupset and I can see
> that this questionable time period did not coincide with any t-log backup.
> Looking for some advice... Maybe there are some pertainant Perfmon
> counters that I could enable?
> Thanks in advance!
>
>