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?
>
Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts
Thursday, March 29, 2012
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
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
Tuesday, March 27, 2012
Alternate coloring in Groups
Hi,
im able to get the alternate coloring in tables using the code
=iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
when i insert a group in the table for the weekenddate.the alternate
coloring of rows has disappeared...
i cant get where im going wrong...
im grouping the record based on the weekend date...
Thanks in advamce for ur help,Using RowNumber can be a bit hit and miss.
I use a bit of code to handle this, it also works at any group level.
In report properties|Code type the following;
Public Dim Lv(4) As Boolean
Public Function Switch(ByRef Value As Boolean) As Boolean
Value = Not Value
Return Value
End Function
In the BackgroundColor property of the first cell on a row use the
following expression;
=IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
In all subsequent cells use;
=IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
Use different subscripts with Lv for different group levels.
This also works in matrix regions too.
Chris
CCP wrote:
> Hi,
> im able to get the alternate coloring in tables using the code
> =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> when i insert a group in the table for the weekenddate.the alternate
> coloring of rows has disappeared...
> i cant get where im going wrong...
> im grouping the record based on the weekend date...
> Thanks in advamce for ur help,|||Thank u very much Chris ,
It worked...
"Chris McGuigan" wrote:
> Using RowNumber can be a bit hit and miss.
> I use a bit of code to handle this, it also works at any group level.
> In report properties|Code type the following;
> Public Dim Lv(4) As Boolean
> Public Function Switch(ByRef Value As Boolean) As Boolean
> Value = Not Value
> Return Value
> End Function
> In the BackgroundColor property of the first cell on a row use the
> following expression;
> =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> In all subsequent cells use;
> =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> Use different subscripts with Lv for different group levels.
> This also works in matrix regions too.
> Chris
>
> CCP wrote:
> > Hi,
> > im able to get the alternate coloring in tables using the code
> > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > when i insert a group in the table for the weekenddate.the alternate
> > coloring of rows has disappeared...
> > i cant get where im going wrong...
> > im grouping the record based on the weekend date...
> >
> > Thanks in advamce for ur help,
>|||Chris,
Can i sort the records within the groups'
Thanks,
"CCP" wrote:
> Thank u very much Chris ,
> It worked...
>
> "Chris McGuigan" wrote:
> > Using RowNumber can be a bit hit and miss.
> >
> > I use a bit of code to handle this, it also works at any group level.
> >
> > In report properties|Code type the following;
> >
> > Public Dim Lv(4) As Boolean
> >
> > Public Function Switch(ByRef Value As Boolean) As Boolean
> > Value = Not Value
> > Return Value
> > End Function
> >
> > In the BackgroundColor property of the first cell on a row use the
> > following expression;
> >
> > =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> >
> > In all subsequent cells use;
> >
> > =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> >
> > Use different subscripts with Lv for different group levels.
> > This also works in matrix regions too.
> >
> > Chris
> >
> >
> >
> > CCP wrote:
> >
> > > Hi,
> > > im able to get the alternate coloring in tables using the code
> > > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > > when i insert a group in the table for the weekenddate.the alternate
> > > coloring of rows has disappeared...
> > > i cant get where im going wrong...
> > > im grouping the record based on the weekend date...
> > >
> > > Thanks in advamce for ur help,
> >
> >|||Sure, right click the row tag of the relevant group, click edit group
and you will see a sorting tab.
Chris
CCP wrote:
> Chris,
> Can i sort the records within the groups'
> Thanks,
> "CCP" wrote:
> > Thank u very much Chris ,
> > It worked...
> >
> >
> > "Chris McGuigan" wrote:
> >
> > > Using RowNumber can be a bit hit and miss.
> > >
> > > I use a bit of code to handle this, it also works at any group
> > > level.
> > >
> > > In report properties|Code type the following;
> > >
> > > Public Dim Lv(4) As Boolean
> > >
> > > Public Function Switch(ByRef Value As Boolean) As Boolean
> > > Value = Not Value
> > > Return Value
> > > End Function
> > >
> > > In the BackgroundColor property of the first cell on a row use the
> > > following expression;
> > >
> > > =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> > >
> > > In all subsequent cells use;
> > >
> > > =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> > >
> > > Use different subscripts with Lv for different group levels.
> > > This also works in matrix regions too.
> > >
> > > Chris
> > >
> > >
> > >
> > > CCP wrote:
> > >
> > > > Hi,
> > > > im able to get the alternate coloring in tables using the code
> > > > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > > > when i insert a group in the table for the weekenddate.the
> > > > alternate coloring of rows has disappeared...
> > > > i cant get where im going wrong...
> > > > im grouping the record based on the weekend date...
> > > >
> > > > Thanks in advamce for ur help,
> > >
> > >
im able to get the alternate coloring in tables using the code
=iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
when i insert a group in the table for the weekenddate.the alternate
coloring of rows has disappeared...
i cant get where im going wrong...
im grouping the record based on the weekend date...
Thanks in advamce for ur help,Using RowNumber can be a bit hit and miss.
I use a bit of code to handle this, it also works at any group level.
In report properties|Code type the following;
Public Dim Lv(4) As Boolean
Public Function Switch(ByRef Value As Boolean) As Boolean
Value = Not Value
Return Value
End Function
In the BackgroundColor property of the first cell on a row use the
following expression;
=IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
In all subsequent cells use;
=IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
Use different subscripts with Lv for different group levels.
This also works in matrix regions too.
Chris
CCP wrote:
> Hi,
> im able to get the alternate coloring in tables using the code
> =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> when i insert a group in the table for the weekenddate.the alternate
> coloring of rows has disappeared...
> i cant get where im going wrong...
> im grouping the record based on the weekend date...
> Thanks in advamce for ur help,|||Thank u very much Chris ,
It worked...
"Chris McGuigan" wrote:
> Using RowNumber can be a bit hit and miss.
> I use a bit of code to handle this, it also works at any group level.
> In report properties|Code type the following;
> Public Dim Lv(4) As Boolean
> Public Function Switch(ByRef Value As Boolean) As Boolean
> Value = Not Value
> Return Value
> End Function
> In the BackgroundColor property of the first cell on a row use the
> following expression;
> =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> In all subsequent cells use;
> =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> Use different subscripts with Lv for different group levels.
> This also works in matrix regions too.
> Chris
>
> CCP wrote:
> > Hi,
> > im able to get the alternate coloring in tables using the code
> > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > when i insert a group in the table for the weekenddate.the alternate
> > coloring of rows has disappeared...
> > i cant get where im going wrong...
> > im grouping the record based on the weekend date...
> >
> > Thanks in advamce for ur help,
>|||Chris,
Can i sort the records within the groups'
Thanks,
"CCP" wrote:
> Thank u very much Chris ,
> It worked...
>
> "Chris McGuigan" wrote:
> > Using RowNumber can be a bit hit and miss.
> >
> > I use a bit of code to handle this, it also works at any group level.
> >
> > In report properties|Code type the following;
> >
> > Public Dim Lv(4) As Boolean
> >
> > Public Function Switch(ByRef Value As Boolean) As Boolean
> > Value = Not Value
> > Return Value
> > End Function
> >
> > In the BackgroundColor property of the first cell on a row use the
> > following expression;
> >
> > =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> >
> > In all subsequent cells use;
> >
> > =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> >
> > Use different subscripts with Lv for different group levels.
> > This also works in matrix regions too.
> >
> > Chris
> >
> >
> >
> > CCP wrote:
> >
> > > Hi,
> > > im able to get the alternate coloring in tables using the code
> > > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > > when i insert a group in the table for the weekenddate.the alternate
> > > coloring of rows has disappeared...
> > > i cant get where im going wrong...
> > > im grouping the record based on the weekend date...
> > >
> > > Thanks in advamce for ur help,
> >
> >|||Sure, right click the row tag of the relevant group, click edit group
and you will see a sorting tab.
Chris
CCP wrote:
> Chris,
> Can i sort the records within the groups'
> Thanks,
> "CCP" wrote:
> > Thank u very much Chris ,
> > It worked...
> >
> >
> > "Chris McGuigan" wrote:
> >
> > > Using RowNumber can be a bit hit and miss.
> > >
> > > I use a bit of code to handle this, it also works at any group
> > > level.
> > >
> > > In report properties|Code type the following;
> > >
> > > Public Dim Lv(4) As Boolean
> > >
> > > Public Function Switch(ByRef Value As Boolean) As Boolean
> > > Value = Not Value
> > > Return Value
> > > End Function
> > >
> > > In the BackgroundColor property of the first cell on a row use the
> > > following expression;
> > >
> > > =IIf(Code.Switch(Code.Lv(1)),"WhiteSmoke","LightGrey")
> > >
> > > In all subsequent cells use;
> > >
> > > =IIf(Code.Lv(1),"WhiteSmoke","LightGrey")
> > >
> > > Use different subscripts with Lv for different group levels.
> > > This also works in matrix regions too.
> > >
> > > Chris
> > >
> > >
> > >
> > > CCP wrote:
> > >
> > > > Hi,
> > > > im able to get the alternate coloring in tables using the code
> > > > =iif(RowNumber(Nothing) Mod 2,"WhiteSmoke", "LightGrey")
> > > > when i insert a group in the table for the weekenddate.the
> > > > alternate coloring of rows has disappeared...
> > > > i cant get where im going wrong...
> > > > im grouping the record based on the weekend date...
> > > >
> > > > Thanks in advamce for ur help,
> > >
> > >
Thursday, March 22, 2012
Alter table script
Hello,
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
this.
> which
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got.
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> into
> this.
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
persist
>into
VB.NET)
do
>this.
constraints etc
>
>
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
this.
> which
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got.
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> into
> this.
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computers security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
persist
>into
VB.NET)
do
>this.
constraints etc
>
>
Alter table script
Hello,
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I want to insert a new column in a data table using code (from VB.NET)
> > Is there a stored procedure or does any example script exists to do
this.
> >
> > I need to take in account any existing keys, indexes, constraints etc
> which
> > exist on the table.
> >
> > Thanks
> > Tim
> >
> >
> >
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got. :)
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> > Do you want to:
> >
> > 1) Add a column to an SQL table through .NET? or
> > 2) Add a column to a DataTable in .NET? or
> > 3) Add a column through a DataTable in .NET and have the change persist
> into
> > the source table in SQL?
> >
> > Christian Smith
> >
> > "Tim Marsden" <TM@.UK.COM> wrote in message
> > news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I want to insert a new column in a data table using code (from VB.NET)
> > > Is there a stored procedure or does any example script exists to do
> this.
> > >
> > > I need to take in account any existing keys, indexes, constraints etc
> > which
> > > exist on the table.
> > >
> > > Thanks
> > > Tim
> > >
> > >
> > >
> >
> >
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Do you want to:
>> 1) Add a column to an SQL table through .NET? or
>> 2) Add a column to a DataTable in .NET? or
>> 3) Add a column through a DataTable in .NET and have the change
persist
>into
>> the source table in SQL?
>> Christian Smith
>> "Tim Marsden" <TM@.UK.COM> wrote in message
>> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
>> > Hello,
>> >
>> > I want to insert a new column in a data table using code (from
VB.NET)
>> > Is there a stored procedure or does any example script exists to
do
>this.
>> >
>> > I need to take in account any existing keys, indexes,
constraints etc
>> which
>> > exist on the table.
>> >
>> > Thanks
>> > Tim
>> >
>> >
>> >
>>
>
>
I want to insert a new column in a data table using code (from VB.NET)
Is there a stored procedure or does any example script exists to do this.
I need to take in account any existing keys, indexes, constraints etc which
exist on the table.
Thanks
TimDo you want to:
1) Add a column to an SQL table through .NET? or
2) Add a column to a DataTable in .NET? or
3) Add a column through a DataTable in .NET and have the change persist into
the source table in SQL?
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I want to insert a new column in a data table using code (from VB.NET)
> Is there a stored procedure or does any example script exists to do this.
> I need to take in account any existing keys, indexes, constraints etc
which
> exist on the table.
> Thanks
> Tim
>
>|||Thanks for reply
I want to add a column to a SQL server table.
If I have to do it through a .NET datatable so be it.
Thanks
"Christian Smith" <csmith@.digex.com> wrote in message
news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> Do you want to:
> 1) Add a column to an SQL table through .NET? or
> 2) Add a column to a DataTable in .NET? or
> 3) Add a column through a DataTable in .NET and have the change persist
into
> the source table in SQL?
> Christian Smith
> "Tim Marsden" <TM@.UK.COM> wrote in message
> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > I want to insert a new column in a data table using code (from VB.NET)
> > Is there a stored procedure or does any example script exists to do
this.
> >
> > I need to take in account any existing keys, indexes, constraints etc
> which
> > exist on the table.
> >
> > Thanks
> > Tim
> >
> >
> >
>|||You should be able to use the standard SQL DDL throught the SqlCommand
class. Adding a column should not affect any of the indexes or constraints
since they could not have possibly been defined to include a column that
does not yet exist. That might be an issue for deleting a column but I
can't imagine that it would affect an addition.
Alternately, I think that there is a way to modify the table structure of a
DataTable in .NET and have it push the change up to a SQL database if they
are linked. I tried to get it to work once before but couldn't get it and
did what I needed a different way.
Sorry that is all I got. :)
Christian Smith
"Tim Marsden" <TM@.UK.COM> wrote in message
news:OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl...
> Thanks for reply
> I want to add a column to a SQL server table.
> If I have to do it through a .NET datatable so be it.
> Thanks
>
> "Christian Smith" <csmith@.digex.com> wrote in message
> news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
> > Do you want to:
> >
> > 1) Add a column to an SQL table through .NET? or
> > 2) Add a column to a DataTable in .NET? or
> > 3) Add a column through a DataTable in .NET and have the change persist
> into
> > the source table in SQL?
> >
> > Christian Smith
> >
> > "Tim Marsden" <TM@.UK.COM> wrote in message
> > news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > I want to insert a new column in a data table using code (from VB.NET)
> > > Is there a stored procedure or does any example script exists to do
> this.
> > >
> > > I need to take in account any existing keys, indexes, constraints etc
> > which
> > > exist on the table.
> > >
> > > Thanks
> > > Tim
> > >
> > >
> > >
> >
> >
>|||Hi Tim,
I am reviewing you post and since I have not heard from you for some time,
I wonder whether you have solved you problem or you still have any
questions about that. I agree with our community member's suggestion, that
is to use the DDL of T-SQL as the following example:
use pubs
go
CREATE TABLE dbo.testaddcol1
(
id int NULL
) ON [PRIMARY]
go
ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
go
Hope this helps and I am waiting on your replay. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for all the suggestions.
I have discovered there is no easy way to alter a table in code, the
complexity of indexes, permissions etc is to great.
The simple functions can be performed easily with ALTER TABLE and a few
stored procedures, he complex stuff, I have left alone.
Tim
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:N994H%23b9DHA.704@.cpmsftngxa07.phx.gbl...
> Hi Tim,
> I am reviewing you post and since I have not heard from you for some time,
> I wonder whether you have solved you problem or you still have any
> questions about that. I agree with our community member's suggestion, that
> is to use the DDL of T-SQL as the following example:
> use pubs
> go
> CREATE TABLE dbo.testaddcol1
> (
> id int NULL
> ) ON [PRIMARY]
> go
> ALTER TABLE dbo.testaddcol1 ADD col1 varchar(25) NULL
> go
> Hope this helps and I am waiting on your replay. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hello Tim,
As Christian has stated, you can use standard SQL DDL/DML thru SQL
SqlCommand Class.
You code should pretty much look like this..(modify the table/col
names etc as per your need )
Imports System
Imports System.Data
Imports System.Data.SqlClient
Try
Dim myConnString As String ="User ID=myUID;password=myPWD;Initial
Catalog=pubs;Data Source=mySQLServer"
Dim myAlterQuery As String = "ALTER TABLE dbo.testaddcol1 ADD col1
varchar(25) NULL"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(myAlterQuery, myConnection)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
Catch ex As Exception
MessageBox.Show(ex.ToString())
End Try
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer?s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
>From: "Tim Marsden" <TM@.UK.COM>
>References: <eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl>
<uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl>
>Subject: Re: Alter table script
>Date: Thu, 12 Feb 2004 14:44:42 -0000
>Lines: 40
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
>Message-ID: <OvTUBbX8DHA.3880@.tk2msftngp13.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: host213-122-124-46.in-addr.btopenworld.com
213.122.124.46
>Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msft
ngp13.phx.gbl
>Xref: cpmsftngxa07.phx.gbl microsoft.public.sqlserver.server:328897
>X-Tomcat-NG: microsoft.public.sqlserver.server
>Thanks for reply
>I want to add a column to a SQL server table.
>If I have to do it through a .NET datatable so be it.
>Thanks
>
>"Christian Smith" <csmith@.digex.com> wrote in message
>news:uB3hKMX8DHA.3200@.TK2MSFTNGP09.phx.gbl...
>> Do you want to:
>> 1) Add a column to an SQL table through .NET? or
>> 2) Add a column to a DataTable in .NET? or
>> 3) Add a column through a DataTable in .NET and have the change
persist
>into
>> the source table in SQL?
>> Christian Smith
>> "Tim Marsden" <TM@.UK.COM> wrote in message
>> news:eDM073W8DHA.1428@.TK2MSFTNGP12.phx.gbl...
>> > Hello,
>> >
>> > I want to insert a new column in a data table using code (from
VB.NET)
>> > Is there a stored procedure or does any example script exists to
do
>this.
>> >
>> > I need to take in account any existing keys, indexes,
constraints etc
>> which
>> > exist on the table.
>> >
>> > Thanks
>> > Tim
>> >
>> >
>> >
>>
>
>
Alter Table question
(using SQL Server 2000)
I notice that in the enterprise manager, I can insert a column into an
existing table at any position that I want (so, if my table has 3 columns,
and I want to add a fourth, I can put the column at the end, but I could
also insert it between the 1st and second columns).
Is there a way to do that with an SQL Alter Table statement (control
position of the new column)?No. If you script the code that EM uses you will see that it actually
creates a new table from scratch and then populates it with the old data.
--
David Portas
--
Please reply only to the newsgroup
--
"J.Marsch" <jeremy@.ctcdeveloper.com> wrote in message
news:OhX4r3TqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> (using SQL Server 2000)
> I notice that in the enterprise manager, I can insert a column into an
> existing table at any position that I want (so, if my table has 3 columns,
> and I want to add a fourth, I can put the column at the end, but I could
> also insert it between the 1st and second columns).
> Is there a way to do that with an SQL Alter Table statement (control
> position of the new column)?
>
>|||Wow. That response was just about instantaneous. Thank you!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:KNSdnc3thOvL-i-iRVn-tA@.giganews.com...
> No. If you script the code that EM uses you will see that it actually
> creates a new table from scratch and then populates it with the old data.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "J.Marsch" <jeremy@.ctcdeveloper.com> wrote in message
> news:OhX4r3TqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > (using SQL Server 2000)
> > I notice that in the enterprise manager, I can insert a column into an
> > existing table at any position that I want (so, if my table has 3
columns,
> > and I want to add a fourth, I can put the column at the end, but I could
> > also insert it between the 1st and second columns).
> >
> > Is there a way to do that with an SQL Alter Table statement (control
> > position of the new column)?
> >
> >
> >
>
I notice that in the enterprise manager, I can insert a column into an
existing table at any position that I want (so, if my table has 3 columns,
and I want to add a fourth, I can put the column at the end, but I could
also insert it between the 1st and second columns).
Is there a way to do that with an SQL Alter Table statement (control
position of the new column)?No. If you script the code that EM uses you will see that it actually
creates a new table from scratch and then populates it with the old data.
--
David Portas
--
Please reply only to the newsgroup
--
"J.Marsch" <jeremy@.ctcdeveloper.com> wrote in message
news:OhX4r3TqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> (using SQL Server 2000)
> I notice that in the enterprise manager, I can insert a column into an
> existing table at any position that I want (so, if my table has 3 columns,
> and I want to add a fourth, I can put the column at the end, but I could
> also insert it between the 1st and second columns).
> Is there a way to do that with an SQL Alter Table statement (control
> position of the new column)?
>
>|||Wow. That response was just about instantaneous. Thank you!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:KNSdnc3thOvL-i-iRVn-tA@.giganews.com...
> No. If you script the code that EM uses you will see that it actually
> creates a new table from scratch and then populates it with the old data.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "J.Marsch" <jeremy@.ctcdeveloper.com> wrote in message
> news:OhX4r3TqDHA.2216@.TK2MSFTNGP12.phx.gbl...
> > (using SQL Server 2000)
> > I notice that in the enterprise manager, I can insert a column into an
> > existing table at any position that I want (so, if my table has 3
columns,
> > and I want to add a fourth, I can put the column at the end, but I could
> > also insert it between the 1st and second columns).
> >
> > Is there a way to do that with an SQL Alter Table statement (control
> > position of the new column)?
> >
> >
> >
>
alter table problem?
in a stored procedure , i've the following coding
...
create table tbl (
a int, b int
)
insert into tbl values (1, 2)
alter table tbl drop column b
select * from tbl
...
sql server returns "Column name or number of supplied values does not match
table definition."
Could anyone help me please.the table is temp table
"Win" <aaa@.aaa.com> wrote in message
news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not
match
> table definition."
> Could anyone help me please.
>|||Win
create table #tbl (
a int, b int
)
insert into #tbl values (1, 2)
GO
alter table #tbl drop column b
GO
select * from #tbl
drop table #tbl
"Win" <aaa@.aaa.com> wrote in message
news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
> the table is temp table
> "Win" <aaa@.aaa.com> wrote in message
> news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> match
>|||Cannot alter table '#tbl' because this table does not exist in database
'abc'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
> Win
> create table #tbl (
> a int, b int
> )
> insert into #tbl values (1, 2)
> GO
> alter table #tbl drop column b
> GO
> select * from #tbl
> drop table #tbl
>
> "Win" <aaa@.aaa.com> wrote in message
> news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
>|||Win
On my machine it works file. What version are you using?
"Win" <aaa@.aaa.com> wrote in message
news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Cannot alter table '#tbl' because this table does not exist in database
> 'abc'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
>|||The procedure is parsed as a unit, before any execution has taken place. So,
when the SELECT is
parsed, the ALTER hasn't occurred yet, hence the error message. This is expe
cted. If you post the
logic behind doing this, someone might provide an alternative, or you can us
e dynamic SQL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Win" <aaa@.aaa.com> wrote in message news:%23XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...darkred">
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not matc
h
> table definition."
> Could anyone help me please.
>|||my coding is inside a stored procedure
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uToyZl2OGHA.428@.tk2msftngp13.phx.gbl...
> Win
> On my machine it works file. What version are you using?
>
> "Win" <aaa@.aaa.com> wrote in message
> news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
not
>
...
create table tbl (
a int, b int
)
insert into tbl values (1, 2)
alter table tbl drop column b
select * from tbl
...
sql server returns "Column name or number of supplied values does not match
table definition."
Could anyone help me please.the table is temp table
"Win" <aaa@.aaa.com> wrote in message
news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not
match
> table definition."
> Could anyone help me please.
>|||Win
create table #tbl (
a int, b int
)
insert into #tbl values (1, 2)
GO
alter table #tbl drop column b
GO
select * from #tbl
drop table #tbl
"Win" <aaa@.aaa.com> wrote in message
news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
> the table is temp table
> "Win" <aaa@.aaa.com> wrote in message
> news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> match
>|||Cannot alter table '#tbl' because this table does not exist in database
'abc'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
> Win
> create table #tbl (
> a int, b int
> )
> insert into #tbl values (1, 2)
> GO
> alter table #tbl drop column b
> GO
> select * from #tbl
> drop table #tbl
>
> "Win" <aaa@.aaa.com> wrote in message
> news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
>|||Win
On my machine it works file. What version are you using?
"Win" <aaa@.aaa.com> wrote in message
news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Cannot alter table '#tbl' because this table does not exist in database
> 'abc'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
>|||The procedure is parsed as a unit, before any execution has taken place. So,
when the SELECT is
parsed, the ALTER hasn't occurred yet, hence the error message. This is expe
cted. If you post the
logic behind doing this, someone might provide an alternative, or you can us
e dynamic SQL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Win" <aaa@.aaa.com> wrote in message news:%23XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...darkred">
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not matc
h
> table definition."
> Could anyone help me please.
>|||my coding is inside a stored procedure
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uToyZl2OGHA.428@.tk2msftngp13.phx.gbl...
> Win
> On my machine it works file. What version are you using?
>
> "Win" <aaa@.aaa.com> wrote in message
> news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
not
>
Monday, March 19, 2012
Alter Table add Column - How do you add a column after say the second column
When you use "Alter Table add Column", it adds the column to the end of the list of fields.
How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?
Create table T1 ( a varchar(20), b varchar(20), c varchar(20))
Alter table add column x varchar(20)
so that the resulting table is
T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)
Can this be done programmatically?
One option is to do the following (but it might take a while if you have lots of data):
- insert all data in a temp table
- drop the original table
- create a table with the new column in place
- insert all the data from the temp table in the new table
Make sure you take into account all constraints, indexes, triggers, ...
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Order of columns is irrelevant in a table. You can retrieve columns in a specific order when you query the table. It doesn't have to be physically added in the same order and the internal row format also doesn't store it in the order you specified in the CREATE TABLE. So why do you care where the column is in the CREATE TABLE? What are you trying to do with that information?|||I was under the impression some SQL scripts would break. Am I mistaken?
insert into T1 select * from T2 ( I do run into these SQL scripts)
How does the Enterprise Manager insert a column into the table without having to drop the table and re-creating it?
Thanks.
|||If you alter either T1 or T2, then you need to address the query anyway.
Enterprise manager copies the data out, recreates the table with the column in the position you designated, then copies the data back in....much as was described in an earlier post.
|||I was hoping that was able to run a script that would be able to add a column (let's say column in the second position)
to both T1 and T2 so that I wouldn't have to searching for all the previous scripts that would break.
When you do use a script to Alter a table the column is added to the end. I end up having to go to the Enterprise Manager and drag and drop the column that is at the end of the table to the 2nd position.
I don't think the Enterprise Manager is recreating the table at this time. It should be simply moving the pointers in syscolumns for instance. I am just guessing, but that would be more` efficient.
|||Then you are quite 'wrong' with your 'guess'.
Enterprise Managler most definitely
creates a new table with the column order you desire,
transfers all the data to the new table,scripts out and adds the necessary constraints,
drops the old table,and then renames the new table to the same name as the old table.
Alter Table add Column - How do you add a column after say the second column
When you use "Alter Table add Column", it adds the column to the end of the list of fields.
How do you insert the new column to position number 2 for instance given that you may have more than 2 columns?
Create table T1 ( a varchar(20), b varchar(20), c varchar(20))
Alter table add column x varchar(20)
so that the resulting table is
T1 a varchar(20), x varchar(20), b varchar(20), c varchar(20)
Can this be done programmatically?
One option is to do the following (but it might take a while if you have lots of data):
- insert all data in a temp table
- drop the original table
- create a table with the new column in place
- insert all the data from the temp table in the new table
Make sure you take into account all constraints, indexes, triggers, ...
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||Order of columns is irrelevant in a table. You can retrieve columns in a specific order when you query the table. It doesn't have to be physically added in the same order and the internal row format also doesn't store it in the order you specified in the CREATE TABLE. So why do you care where the column is in the CREATE TABLE? What are you trying to do with that information?|||I was under the impression some SQL scripts would break. Am I mistaken?
insert into T1 select * from T2 ( I do run into these SQL scripts)
How does the Enterprise Manager insert a column into the table without having to drop the table and re-creating it?
Thanks.
|||If you alter either T1 or T2, then you need to address the query anyway.
Enterprise manager copies the data out, recreates the table with the column in the position you designated, then copies the data back in....much as was described in an earlier post.
|||I was hoping that was able to run a script that would be able to add a column (let's say column in the second position)
to both T1 and T2 so that I wouldn't have to searching for all the previous scripts that would break.
When you do use a script to Alter a table the column is added to the end. I end up having to go to the Enterprise Manager and drag and drop the column that is at the end of the table to the 2nd position.
I don't think the Enterprise Manager is recreating the table at this time. It should be simply moving the pointers in syscolumns for instance. I am just guessing, but that would be more` efficient.
|||Then you are quite 'wrong' with your 'guess'.
Enterprise Managler most definitely
creates a new table with the column order you desire,
transfers all the data to the new table,scripts out and adds the necessary constraints,
drops the old table,and then renames the new table to the same name as the old table.
Sunday, March 11, 2012
Alter Table
Warning: The table 'top32_kan_g2_nb' has been created but its maximum row size (14199) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
try doing a
This is the message I am getting, when i have altered the table definition(i.e. copy the table and pasted in Query Analyzer) using alter.My requirement is to delete some fields from the table at EOD daily. The query is executed but the above warning message appears and the fields I required has been deleted.
Is the above message important or can I use the same method again.As you are removing some of the fields using alter it can work for you provided that it follows the mentioned condition in the warning.
Contact your DBA regarding the warning message .|||
Quote:
Originally Posted by balasach82
Warning: The table 'top32_kan_g2_nb' has been created but its maximum row size (14199) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
This is the message I am getting, when i have altered the table definition(i.e. copy the table and pasted in Query Analyzer) using alter.My requirement is to delete some fields from the table at EOD daily. The query is executed but the above warning message appears and the fields I required has been deleted.
Is the above message important or can I use the same method again.
try doing a
SELECT only, selected, fields, here INTO NEWTABLE from DAILYTABLE..
Friday, February 24, 2012
Almost there with bulk insert
I have BULK INSERT T-SQL statements that work for all of my basic data
tables except for one.
Well, the real problem is one of two:
tables except for one.
Here is the problem statement (with obvious replacement of irrelevant
path info):
BULK INSERT igbconts_tmp FROM 'C:\\my_code_path\\IGBCONTS.txt'
WITH (KEEPNULLS,
FORMATFILE = 'C:\\my_data_path\\contacts.fmt');
And here is the output from this statement:
Msg 8152, Level 16, State 14, Line 3
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
This tells me precisely nothing about where the real problem lies. I
am reluctant to post either the table definition or the format file
since they are large (the table, and thus the data file, has 104
fields. However, the first few lines in the format file are:
8.0
105
1 SQLCHAR 0 0 "\"" 0 dummy ""
2 SQLCHAR 0 0 "\",\"" 1 contact_id ""
3 SQLCHAR 0 0 "\",\"" 2 full_name ""
4 SQLCHAR 0 0 "\",\"" 3 last_name ""
And here are the last couple lines:
104 SQLCHAR 0 0 "\",\"" 103 user_defined_field15 ""
105 SQLCHAR 0 0 "\"\r\n" 104 user_defined_field16 ""
The table was created using the string length information given to us
by the data provider, and those fields that are not strings consist of
a few datetime values and a moderate number of floating point numbers.
The message suggests to me that one of the fields is too small for what
was actually found in the corresponding column in the data file for at
least one record. But in addition to there being over 100 columns,
there are several thousand records in the data file!
How do I determine precisely where the problem lies?
Thanks,
TedTed (r.ted.byers@.rogers.com) writes:
Quote:
Originally Posted by
I have BULK INSERT T-SQL statements that work for all of my basic data
tables except for one.
>
Here is the problem statement (with obvious replacement of irrelevant
path info):
>
BULK INSERT igbconts_tmp FROM 'C:\\my_code_path\\IGBCONTS.txt'
WITH (KEEPNULLS,
FORMATFILE = 'C:\\my_data_path\\contacts.fmt');
>
And here is the output from this statement:
>
Msg 8152, Level 16, State 14, Line 3
String or binary data would be truncated.
The statement has been terminated.
>
(0 row(s) affected)
>
This tells me precisely nothing about where the real problem lies.
Well, the real problem is one of two:
1) The data file contains occasional fields that are longer that
the receiving columns in the database.
2) There is a mismatch between the data file and the format file
somewhere, so the fields get out of sync with the data in the
table.
You can decide which of these cases you have by running the command
SET ANSI_WARNINGS OFF before you do the bulk insert. This legacy
setting turns of the check for truncation. Then do a SELECT on the
table, and if the data looks OK, then it's the first alternative. And
if it's a mess, it's the second.
For the first situation, if you want to track down where the errors
are, you have to turn to BCP and use the -e argument to specify an
error file. Yes, you can specify an error file with BULK INSERT too,
but BULK INSERT and BCP behaves differently in this case. BULK
INSERT just aborts, and writes nothing to the error file. BCP
imports the rest of the rows, and writes a message to the error file
so you can see which are the problematic records in the file.
--
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|||Thanks Erland
Turning off the warnings helped solve the problem. The data looked
fine, except for the last column. It had over half a dozen fields
embedded in it (but no useful data). It turns out that the data file
has half a dozen more fields than the supplier documented. I modified
the format file slightly to put the extra fields into an extra column
that is ignored when data is put into the table (column number 0), and
now all works flawlessly. I'll be talking with my colleagues and our
supplier, and this issue will certainly be addressed, but for now,
ignoring the extra fields is OK since we have no idea what they're
supposed to contain in hte rare instance they have data and the
supplier didn't bother to document them.
Thanks again,
Ted
Allowing truncation of data on insert
I am using SQL 2000. Is there a DB option that will allow an insert to successfully complete even though the data will be truncated in some of the fields?try this...
set ansi_warnings off
create table #tmp(f1 varchar(1))
insert into #tmp values ('!')
insert into #tmp values ('!!')
select * from #tmp
drop table #tmp
go
set ansi_warnings on
create table #tmp(f1 varchar(1))
insert into #tmp values ('!')
insert into #tmp values ('!!')
select * from #tmp
drop table #tmp
go|||Another approach is to ensure the data is truncated when you insert it by explicitly truncating it yourself.
Eg INSERT INTO myTable (myColName)
VALUES(CAST('abcdefg' AS varchar(4)))
set ansi_warnings off
create table #tmp(f1 varchar(1))
insert into #tmp values ('!')
insert into #tmp values ('!!')
select * from #tmp
drop table #tmp
go
set ansi_warnings on
create table #tmp(f1 varchar(1))
insert into #tmp values ('!')
insert into #tmp values ('!!')
select * from #tmp
drop table #tmp
go|||Another approach is to ensure the data is truncated when you insert it by explicitly truncating it yourself.
Eg INSERT INTO myTable (myColName)
VALUES(CAST('abcdefg' AS varchar(4)))
Sunday, February 12, 2012
All tables under partitioned union view get locked - how to reduce?
Hi. We are using a partitioned view across a number of tables. They are
partitioned on a single column. We use the view to insert and update rows in
the underlying tables.
When loading all the rows for table XYZ through the view we find that SQL
Server is creating IX locks against all of the tables even though all the
rows are for one table only as identified by the check constraint on the
partitioned column.
Is there any way to get SQL Server to lock only the table that will be
affected?
McG
y
[url]http://mcgy.blogspot.com[/url]"McGy" <anon@.anon.com> wrote in message
news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update rows
> in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
>
Partitioned views have serious limitations. You should expect to have to
directly address the underlying tables for many operations. Qu|||Cheers David. I am considering duplicating the load stored procedures, 1 per
table, so as to remove the dependency on the partitioned view. It will make
maintenance a bit more complex but ultimately performance should improve.
Does that sound sensible?
Thanks.
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4TVf3khGHA.3860@.TK2MSFTNGP02.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
rows
SQL
the
> Partitioned views have serious limitations. You should expect to have to
> directly address the underlying tables for many operations. Qu
>|||"McGy" <anon@.anon.com> wrote in message
news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
> Cheers David. I am considering duplicating the load stored procedures, 1
> per
> table, so as to remove the dependency on the partitioned view. It will
> make
> maintenance a bit more complex but ultimately performance should improve.
> Does that sound sensible?
>
Yes. And you can always use dynamic SQL to load the table.
David|||The only problem with dynamic SQL is that the stored procedure would be a
nightmare to maintain in that form as it uses lots of variables. Also,
because of the stored procedure's size it takes several seconds to compile.
Presumably we would take that compilation hit each time the dynamic SQL is
constructed and prepared for execution?
McGy
[url]http://mcgy.blogspot.com[/url]
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> "McGy" <anon@.anon.com> wrote in message
> news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
improve.
> Yes. And you can always use dynamic SQL to load the table.
> David
>|||Not necessarily; if you use sp_executeSQL with variables you are
essentially creating a parameterized SQL statement which increases the
likelihood that the execution plan will be reused.
This issue is interesting to me; we use partioned views as well.
However all of our inserts are done with bulk load methods, so locking
hasn't been a problem (yet). I understand the SQL 2005's partitoned
tables are much better than the the views; yet another reason to
consider upgrading.
Stu
McGy wrote:
> The only problem with dynamic SQL is that the stored procedure would be a
> nightmare to maintain in that form as it uses lots of variables. Also,
> because of the stored procedure's size it takes several seconds to compile
.
> Presumably we would take that compilation hit each time the dynamic SQL is
> constructed and prepared for execution?
> --
> McGy
> [url]http://mcgy.blogspot.com[/url]
>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> improve.|||Hmmm. Interesting point on the sp_executesql. I'll take a look.
We use bulk insert too; but we also need to apply a bunch of rules to the
records that get loaded. They get loaded twice: once in to a transaction
table which are all inserts - the other in to an aggregation table that
applies certain rules depending upon what type the transaction is.
Just thought of a problem on the dynamic SQL front. The stored procedure
would be well in excess of the 8000 char limit on variable sizes. Is there a
way to work around that?
McGy
[url]http://mcgy.blogspot.com[/url]
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1149427023.769413.5440@.i39g2000cwa.googlegroups.com...
> Not necessarily; if you use sp_executeSQL with variables you are
> essentially creating a parameterized SQL statement which increases the
> likelihood that the execution plan will be reused.
> This issue is interesting to me; we use partioned views as well.
> However all of our inserts are done with bulk load methods, so locking
> hasn't been a problem (yet). I understand the SQL 2005's partitoned
> tables are much better than the the views; yet another reason to
> consider upgrading.
> Stu
> McGy wrote:
a
compile.
is
procedures, 1
will
>|||McGy (anon@.anon.com) writes:
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update
> rows in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
Are the intent locks causing any real problems? I ran a quick test, and
I was not able detect any locking problems, but I might have missed
something. (I was only testing concurrent SELECT statements.)
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|||Hi Erland. I am not actually sure if those IX locks are causing a problem.
What is the impact of an IX lock? When we were loading multiple files in
production we noticed that some were being blocked waiting for others to
complete. We assumed it was because of the view creating locks against all
the tables - but perhaps not?
McGy
[url]http://mcgy.blogspot.com[/url]
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97D9732464F2Yazorman@.127.0.0.1...
> McGy (anon@.anon.com) writes:
> Are the intent locks causing any real problems? I ran a quick test, and
> I was not able detect any locking problems, but I might have missed
> something. (I was only testing concurrent SELECT statements.)
>
> --
> 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|||McGy (anon@.anon.com) writes:
> Hi Erland. I am not actually sure if those IX locks are causing a problem.
> What is the impact of an IX lock? When we were loading multiple files in
> production we noticed that some were being blocked waiting for others to
> complete. We assumed it was because of the view creating locks against all
> the tables - but perhaps not?
The purpose of an intent lock is to tell "I am working here, so don't
try to take all this place for your own".
If a transaction updates a few rows in a table it acquires an X lock
on these rows, and also an IX lock on the table. This prevents other
processes from getting an X-lock on the table. Or put into other words,
the process that wants an exclusive lock on the table, does not need to
check if any rows are currently locked.
In case of the partitioned view the IX locks are there to prevent other
processes from acquire exclusive locks on the other tables, as there
could suddenly appear a row that should be inserted the other tables.
SQL Server cannot conclude that all data that is being inserted goes
only into one table in the view.
So, yes, if you have different insert processes in parallel, they will
block each other. And a process what would try "SELECT COUNT(*) FROM
pview" or anything else that requires a scan would also probably be
blocked, even with a condition that filtered out the table being
loaded.
If you want to run parallel loads at maximum speeds, you will probably
have to load into the underlying tables directly.
I will have to admit that I had read up in Books Online on what an
intent lock really is.
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
partitioned on a single column. We use the view to insert and update rows in
the underlying tables.
When loading all the rows for table XYZ through the view we find that SQL
Server is creating IX locks against all of the tables even though all the
rows are for one table only as identified by the check constraint on the
partitioned column.
Is there any way to get SQL Server to lock only the table that will be
affected?
McG
y[url]http://mcg
y.blogspot.com[/url]"McGy" <anon@.anon.com> wrote in messagenews:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update rows
> in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
>
Partitioned views have serious limitations. You should expect to have to
directly address the underlying tables for many operations. Qu|||Cheers David. I am considering duplicating the load stored procedures, 1 per
table, so as to remove the dependency on the partitioned view. It will make
maintenance a bit more complex but ultimately performance should improve.
Does that sound sensible?
Thanks.
McG
y[url]http://mcg
y.blogspot.com[/url]"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:O4TVf3khGHA.3860@.TK2MSFTNGP02.phx.gbl...
> "McG
y" <anon@.anon.com> wrote in message> news:eIsUGsjhGHA.4368@.TK2MSFTNGP03.phx.gbl...
rows
SQL
the
> Partitioned views have serious limitations. You should expect to have to
> directly address the underlying tables for many operations. Qu
>|||"McG
y" <anon@.anon.com> wrote in messagenews:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
> Cheers David. I am considering duplicating the load stored procedures, 1
> per
> table, so as to remove the dependency on the partitioned view. It will
> make
> maintenance a bit more complex but ultimately performance should improve.
> Does that sound sensible?
>
Yes. And you can always use dynamic SQL to load the table.
David|||The only problem with dynamic SQL is that the stored procedure would be a
nightmare to maintain in that form as it uses lots of variables. Also,
because of the stored procedure's size it takes several seconds to compile.
Presumably we would take that compilation hit each time the dynamic SQL is
constructed and prepared for execution?
McG
y[url]http://mcg
y.blogspot.com[/url]"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> "McG
y" <anon@.anon.com> wrote in message> news:%23AUMJNvhGHA.4252@.TK2MSFTNGP04.phx.gbl...
improve.
> Yes. And you can always use dynamic SQL to load the table.
> David
>|||Not necessarily; if you use sp_executeSQL with variables you are
essentially creating a parameterized SQL statement which increases the
likelihood that the execution plan will be reused.
This issue is interesting to me; we use partioned views as well.
However all of our inserts are done with bulk load methods, so locking
hasn't been a problem (yet). I understand the SQL 2005's partitoned
tables are much better than the the views; yet another reason to
consider upgrading.
Stu
McG
y wrote:> The only problem with dynamic SQL is that the stored procedure would be a
> nightmare to maintain in that form as it uses lots of variables. Also,
> because of the stored procedure's size it takes several seconds to compile
.
> Presumably we would take that compilation hit each time the dynamic SQL is
> constructed and prepared for execution?
> --
> McG
y> [url]http://mcg
y.blogspot.com[/url]>
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:#cVWYyzhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> improve.|||Hmmm. Interesting point on the sp_executesql. I'll take a look.
We use bulk insert too; but we also need to apply a bunch of rules to the
records that get loaded. They get loaded twice: once in to a transaction
table which are all inserts - the other in to an aggregation table that
applies certain rules depending upon what type the transaction is.
Just thought of a problem on the dynamic SQL front. The stored procedure
would be well in excess of the 8000 char limit on variable sizes. Is there a
way to work around that?
McG
y[url]http://mcg
y.blogspot.com[/url]"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1149427023.769413.5440@.i39g2000cwa.googlegroups.com...
> Not necessarily; if you use sp_executeSQL with variables you are
> essentially creating a parameterized SQL statement which increases the
> likelihood that the execution plan will be reused.
> This issue is interesting to me; we use partioned views as well.
> However all of our inserts are done with bulk load methods, so locking
> hasn't been a problem (yet). I understand the SQL 2005's partitoned
> tables are much better than the the views; yet another reason to
> consider upgrading.
> Stu
> McG
y wrote: a
compile.
is
procedures, 1
will
>|||McG
y (anon@.anon.com) writes:> Hi. We are using a partitioned view across a number of tables. They are
> partitioned on a single column. We use the view to insert and update
> rows in the underlying tables.
> When loading all the rows for table XYZ through the view we find that SQL
> Server is creating IX locks against all of the tables even though all the
> rows are for one table only as identified by the check constraint on the
> partitioned column.
> Is there any way to get SQL Server to lock only the table that will be
> affected?
Are the intent locks causing any real problems? I ran a quick test, and
I was not able detect any locking problems, but I might have missed
something. (I was only testing concurrent SELECT statements.)
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|||Hi Erland. I am not actually sure if those IX locks are causing a problem.
What is the impact of an IX lock? When we were loading multiple files in
production we noticed that some were being blocked waiting for others to
complete. We assumed it was because of the view creating locks against all
the tables - but perhaps not?
McG
y[url]http://mcg
y.blogspot.com[/url]"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97D9732464F2Yazorman@.127.0.0.1...
> McG
y (anon@.anon.com) writes: > Are the intent locks causing any real problems? I ran a quick test, and
> I was not able detect any locking problems, but I might have missed
> something. (I was only testing concurrent SELECT statements.)
>
> --
> 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|||McG
y (anon@.anon.com) writes:> Hi Erland. I am not actually sure if those IX locks are causing a problem.
> What is the impact of an IX lock? When we were loading multiple files in
> production we noticed that some were being blocked waiting for others to
> complete. We assumed it was because of the view creating locks against all
> the tables - but perhaps not?
The purpose of an intent lock is to tell "I am working here, so don't
try to take all this place for your own".
If a transaction updates a few rows in a table it acquires an X lock
on these rows, and also an IX lock on the table. This prevents other
processes from getting an X-lock on the table. Or put into other words,
the process that wants an exclusive lock on the table, does not need to
check if any rows are currently locked.
In case of the partitioned view the IX locks are there to prevent other
processes from acquire exclusive locks on the other tables, as there
could suddenly appear a row that should be inserted the other tables.
SQL Server cannot conclude that all data that is being inserted goes
only into one table in the view.
So, yes, if you have different insert processes in parallel, they will
block each other. And a process what would try "SELECT COUNT(*) FROM
pview" or anything else that requires a scan would also probably be
blocked, even with a condition that filtered out the table being
loaded.
If you want to run parallel loads at maximum speeds, you will probably
have to load into the underlying tables directly.
I will have to admit that I had read up in Books Online on what an
intent lock really is.
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
Subscribe to:
Posts (Atom)