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 net. Show all posts
Showing posts with label net. 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
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
>> >
>> >
>> >
>>
>
>
Sunday, March 11, 2012
Alter Stored Procedure with asp.net code
I have looked all around and I am having no luck trying to figure out how to alter a stored procedure within an asp.net application.
Here is a short snippet of my code, but it keeps erroring out on me.
Try
myCommand.CommandText = "Using " & DatabaseName & vbNewLine & Me.txtStoredProcedures.Text
myCommand.ExecuteNonQuery()
myTran.Commit()
Catch ex As Exception
myTran.Rollback()
Response.Write(ex.ToString())
End Try
The reason for this is because I have to propagate stored procedures across many databases and was hoping to write an application for it.
Basically the database name is coming from a loop statement and I just want to keep on going through all the databases that I have chosen and have the stored procedure updated (altered) automatically
So i thought the code above was close, but it keeps catching on me. Anybody's help would be greatly appreciated!!!
This is one of the things that make stored procedures a maintenance nightmare.
It should be "USE", not "USING". It may be an idea to use separate connections or at least to execute the USE statement separately.
|||
Well, I changed it to Use (I should have seen that already) and still got nothing. I did try to do one stored proc at a time, but it kept catching on me. Any other ideas?
Why not just run the stored procedure create/update within Query Analyser / SQL Server Management Studio?
alter replication triggers
I have a customer application where I utilize merge replication between
sqlserver2000 and pocketpc's that are running sqlserverce2.0 (vb.net 2003).
It appears that the customer has some tables that they use to keep track of
who modifies the data in certain tables. they want the same functionality
from me.
Since I'm utilizing replication I'm not 100% sure how to accomplish this.
I looked at the trigger that replication creates for on insert . I was
thinking I could add the code necesary to populate the log table there, but
am afraid of screwing up the replication.
I'm not very strong on triggers, anybody have any ideas/suggestions?
Thanks
In SQL Server 2000 you can have multiple triggers on a table, so I'd just
create your own separate ones which can be added using sp_addscriptexec or
as part of the article's properties.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sqlserver2000 and pocketpc's that are running sqlserverce2.0 (vb.net 2003).
It appears that the customer has some tables that they use to keep track of
who modifies the data in certain tables. they want the same functionality
from me.
Since I'm utilizing replication I'm not 100% sure how to accomplish this.
I looked at the trigger that replication creates for on insert . I was
thinking I could add the code necesary to populate the log table there, but
am afraid of screwing up the replication.
I'm not very strong on triggers, anybody have any ideas/suggestions?
Thanks
In SQL Server 2000 you can have multiple triggers on a table, so I'd just
create your own separate ones which can be added using sp_addscriptexec or
as part of the article's properties.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Sunday, February 19, 2012
Allowing null dates
My users want to be able to enter nothing in a date field.
I'm using asp.net v2, vb.net, and VS 2005 for my application. I'm not sure what to do or what code to write to allow the user not to enter a date and keep from hitting the sqldatetime overflow error.
I could use some help.
Thanks
If the application variable/control value for the datatime value is empty (EmptyString), your application should set the input parameter to SqlType.dbNull -NOT the input control.text value.|||
Thank you.
I figured out I needed to write that code in my business logic layer. I know this is an issue with a lot of people. Here is how I solved my problem.
I set the variable to Nullable (of DateTime)
And made the following check
If Not fldIntake.HasValue Then ClientApp.SetfldIntakeNull() Else ClientApp.fldIntake = fldIntake.Value
Allowing a conenction to a SQL Server 2005 database from another computer on a LAN
I am working with one other person on a VS 2005 vb.net web project that accesses SQL Server 2005. Both the computers are connected and my partner can run the application on his computer from his VS 2005 but we are getting an error on our first databind to a gridview on the page we are trying to run the error is below
A connection was successfully established with the
server, but then an error occurred during the
pre-login handshake. When connecting to SQL Server
2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow
remote connections. (provider: Named Pipes Provider,
error: 0 - No process is on the other end of the pipe.)
I check the properties of the SQL Server and the check box is checked that says allow remote access. I am not sure what to do.
Hi,
First, please try to allow remote connections for TCP/IP and Named Pipe according to the following KB article.
http://support.microsoft.com/kb/914277/en-us
If that still doesn't work, you can check the following link for troubleshooting.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=275050&SiteID=1
Thursday, February 16, 2012
Allow iis to connect to a sql express database
Folks,
I have an asp.net web app developed in visual studio 2005. The app works fine with the development server from visual studio and sql express. When I try to test the app under IIS before deploying to my host I get a permission not allowed message.
"[SqlException (0x80131904): User does not have permission to perform this action.]".
How do I give my web app access to my sql server database?
Thanks
Doug
These references should help:
Web Applications -Connect to SQL Server
Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395
How To: Create a Service Account for an ASP.NET 2.0 Application
http://msdn2.microsoft.com/en-us/library/ms998297.aspx
How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms998300.aspx
Allow iis to connect to a sql express database
Folks,
I have an asp.net web app developed in visual studio 2005. The app works fine with the development server from visual studio and sql express. When I try to test the app under IIS before deploying to my host I get a permission not allowed message.
"[SqlException (0x80131904): User does not have permission to perform this action.]".
How do I give my web app access to my sql server database?
Thanks
Doug
These references should help:
Web Applications -Connect to SQL Server
Configuring an ASP.NET 2.0 Application to Work with Microsoft SQL Server 2000 or SQL Server 2005
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395
How To: Create a Service Account for an ASP.NET 2.0 Application
http://msdn2.microsoft.com/en-us/library/ms998297.aspx
How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0
http://msdn2.microsoft.com/en-us/library/ms998300.aspx
Subscribe to:
Posts (Atom)