Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supportedsql

Adding transaction causes connection failure?

I have some tables that I need to wipeout and reload once per day. So I have created a T-SQL task that does a truncate table, then a data flow task, then a update statistics task. Running those works fine, but as soon as I put them all into a sequence container and set the container to require transactions, the first step gets a connection failure. "Failed to acquire connection "<name>". Connection may not be configured correctly or you may not have the right permissions on this connection". I am using a .Net SQLClient data provider for the T-SQL task and in the data flow task I have to use a OLEDB provider so that I can run the task locally in development.

Is there something I am doing wrong or is there some other way to handle truncate, load, update stats all in a transaction?

Thanks.

Tim

Unfortunately, the ADO.NET Connection Manager doesn't currently support DTC transaction enlistment, so the sequence container can't coordinate one transaction across your three tasks. I believe your best workaround (and one which will keep all the activity on one connection, which seems preferable in your case anyway), would be to use just the OLE DB connection.

To do this, you'll need to turn your Execute T-SQL task into a plain Execute SQL task (your TRUNCATE TABLE will work fine there). Next, create an Execute SQL Task to run your UPDATE STATISTICS statement (use the "View T-SQL" option in your Update Statistics task editor to see the SQL you'll want to run). You'll then be able to get rid of the ADO.NET connection manager, and your Sequence Container will be able to properly coordinate the transaction across the three operations.

Hope this helps!

|||That didn't work. I still get the failed to acquire connection error.|||

After further research I found that it was because the DTC was not set up to allow inbound transactions on the server. The config settings in question are described here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=230390&SiteID=1

|||So fixing the DTC config helped me get through the first hurdle of connection failures, but now, it just gets stuck in the validation phase of my data flow.|||

Interesting. I just saw this myself, and what's happenning is that the execution of the TRUNCATE in a transaction appears to cause DTC to obtain a Schema Modification lock (LCK_M_SCH_M). This in turn blocks your destination's ability to fetch metadata for your destination, if ValidateExternalMetadata is True on your destination. Try disabling the destination's ValidateExternalMetadata.

-David

|||

That fixed it. I now have all the tasks running in a transaction. It seems odd that the truncate would cause a lock that doesn't allow reads of schema data. Thanks for your help.

Tim

|||I have the same problem.

I have a Sequence Container with 2 sql task and 2 Dataflow task inside.

Transaction option :
On the package : Supported On the sequence container : Required On the 4 task inside the container : Supported

Adding rows to Oracle of MsAccess from T-SQL?

Hi everyone,

I'd like to know how to update/insert rows to sources different than Microsoft.

Should I use OpenDataSource(), OpenRowSet() functions or they are deprecated with Sql25k?

Thanks in advance for your time and for your inputs,

Both OpenDataSource and OpenRowSet can be used for inserting and updating data in data sources other than SQL Server. They are both supported by SQL Server 2005. You can also use linked servers.

Please tell me if you need further information.

Best regards,
Sami Samir

|||

If you are moving millions of rows of data, I found the fastest way to move from Access or SQL Server to ORACLE was via an intermediate text file.

A data load that would have taken around 10 hours to move one table via an ODBC connection from SQL Server to ORACLE took only a few minutes via text export from SQL Server, and SQL*Loader with ORACLE. (I don't know if you consider a few hundred megabytes a large amount of data. I consider it "typical" of a single table. I had many such tables to transfer.)

Another problem I ran into was mixed-case table names that could be created in ORACLE by uploads from SQL Server. My recollection is that you need to surround the names with double-quotes, ", to get ORACLE to recognize the tables with the mixed-case names.

I don't recall trying UPDATE or INSERT statements. I always logged in to ORACLE to drop the tables that I needed to create to perform the data uploads when I used an ODBC connection for "small" tables (with only tens of thousands of rows).

I hope others here may have more direct experience with the UPDATE and INSERT issues you are contemplating.

Dan

Adding rows to Oracle of MsAccess from T-SQL?

Hi everyone,

I'd like to know how to update/insert rows to sources different than Microsoft.

Should I use OpenDataSource(), OpenRowSet() functions or they are deprecated with Sql25k?

Thanks in advance for your time and for your inputs,

Both OpenDataSource and OpenRowSet can be used for inserting and updating data in data sources other than SQL Server. They are both supported by SQL Server 2005. You can also use linked servers.

Please tell me if you need further information.

Best regards,
Sami Samir

|||

If you are moving millions of rows of data, I found the fastest way to move from Access or SQL Server to ORACLE was via an intermediate text file.

A data load that would have taken around 10 hours to move one table via an ODBC connection from SQL Server to ORACLE took only a few minutes via text export from SQL Server, and SQL*Loader with ORACLE. (I don't know if you consider a few hundred megabytes a large amount of data. I consider it "typical" of a single table. I had many such tables to transfer.)

Another problem I ran into was mixed-case table names that could be created in ORACLE by uploads from SQL Server. My recollection is that you need to surround the names with double-quotes, ", to get ORACLE to recognize the tables with the mixed-case names.

I don't recall trying UPDATE or INSERT statements. I always logged in to ORACLE to drop the tables that I needed to create to perform the data uploads when I used an ODBC connection for "small" tables (with only tens of thousands of rows).

I hope others here may have more direct experience with the UPDATE and INSERT issues you are contemplating.

Dan

Adding NT login using T-SQL

Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
doesn't seem to have this capability.
Thank you in advance for your help!
Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!

Adding NT login using T-SQL

Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogi
n
> doesn't seem to have this capability.
> Thank you in advance for your help!

Adding NT login using T-SQL

Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
doesn't seem to have this capability.
Thank you in advance for your help!Use sp_grantlogin for that.
As of 2005, you use CREATE LOGIN for both types of logins.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:1C6B2795-8C35-45B2-B584-3B615CE505D9@.microsoft.com...
> Is it possible to create new login (NT or Windows) using T-SQL? sp_addlogin
> doesn't seem to have this capability.
> Thank you in advance for your help!

Adding new columns with check constraints using same batch

I'm using a stored procedure to add fields to an existing table.

These fields must have check constraints and I need to use one T-SQL batch.

In Sql2000 Ok. In Sql2005, if table exists, I get error "invalid column xxxxx" in Add Constraint statement before new column creation.

the code is

Declare @.Setup bit

Set @.Setup = 1

if @.Setup = 1 Begin

--Alter Table MyTable Add MyField Numeric(1, 0) Not Null Default 3

Exec mySp_Add_Column 'MyTable', 'MyField', 'Numeric (1, 0) Not Null', '3'

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0

Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)

End Else Begin

-- drop column

End

GO

If MyTable does not exist and, naturally, I add it before of check constraints (using another Sp which add tables) ok.

If I add FK to new fields, ok.

Now I have to split batch in two parts as workaround...

Can anyone tell me if this is a bug or a "fix" for previous versions?

Many thanks,

Giulio

Interesting, it probably is an undocumented "fix" that exists pretty much because it is illogical to add a check constraint when something doesn't exist, so when they parse it (before they execute it) it fails.

I would personally just add the check constraint creation to the ADD column proc, if I were you. It would just require a parameter, and it would be easy enough. The parameter I would add would be @.checkCriteria. Then pass in 'MyField >= 1 and MyField <= 3)'

Then you can formulate the name (or at worst add another parameter as override) and build the statement in the proc. You can add the constraint when you are adding the column too:

create table test
(
testId int primary key
)
go
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

|||Another workaround might be this:

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0
exec('
Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)')

Steve Kass
Drew University
www.stevekass.com

|||

Thanks Louis and Steve.

I've just resolved problem. Since in Sql 2000 this problem did not exist (using same T-Sql code) I'd like to know if it's a bug...

Really, if I use Alter Table, instead of mySp_Add_Column to add new columns, I get error too... So if I can understand parser can't know what SP wants to do, why doesn't parser understand I'll add new column before of check constraints? Why can I add FKs on new fields while I can't add CKs?

It seems a parser bug...

Bye,

Giulio

|||

Really? When I execute:

create table test
(
testId int primary key
)
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

in a single batch, it works just fine. What error are you getting?

|||

ok, in this way it works...

Only when I add a column to an existing table and, after, I add a check constraint, all in one batch, I get error...

And only after I installed SqlSrv 2k5... on SqlSrv 2k it works fine...

adding column using t-sql

How can I add a column to the begining of the table using
t-sql? I know the syntax, but it adds it as the last
column on the table.
TIA,
Vic
Hi,
No, you cant do that. Using TSQL statements (ALTER table) you cant insert a
column inbetween or first. It
always add the new column in the end.
Using Enterprise manager we can insert columns inbetween, but enterprise
manager does this in the below way
1. Export data out
2. Drop the table
3. Create the new table with new column added
4. Loads back the data.
This will not be a good mechanism if your data volume in your table is high.
Thanks
Hari
SQL Server MVP
"Vic" <vduran@.specpro-inc.com> wrote in message
news:080601c53f17$3e349bc0$a501280a@.phx.gbl...
> How can I add a column to the begining of the table using
> t-sql? I know the syntax, but it adds it as the last
> column on the table.
> TIA,
> Vic
|||> This will not be a good mechanism if your data volume in your table is
high.
Also, you should not rely on column order. In a relation, column and row
order is not defined. You should always specify column names in your DML
statements.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

adding column using t-sql

How can I add a column to the begining of the table using
t-sql? I know the syntax, but it adds it as the last
column on the table.
TIA,
VicHi,
No, you cant do that. Using TSQL statements (ALTER table) you cant insert a
column inbetween or first. It
always add the new column in the end.
Using Enterprise manager we can insert columns inbetween, but enterprise
manager does this in the below way
1. Export data out
2. Drop the table
3. Create the new table with new column added
4. Loads back the data.
This will not be a good mechanism if your data volume in your table is high.
Thanks
Hari
SQL Server MVP
"Vic" <vduran@.specpro-inc.com> wrote in message
news:080601c53f17$3e349bc0$a501280a@.phx.gbl...
> How can I add a column to the begining of the table using
> t-sql? I know the syntax, but it adds it as the last
> column on the table.
> TIA,
> Vic|||> This will not be a good mechanism if your data volume in your table is
high.
Also, you should not rely on column order. In a relation, column and row
order is not defined. You should always specify column names in your DML
statements.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

adding column using t-sql

How can I add a column to the begining of the table using
t-sql? I know the syntax, but it adds it as the last
column on the table.
TIA,
VicHi,
No, you cant do that. Using TSQL statements (ALTER table) you cant insert a
column inbetween or first. It
always add the new column in the end.
Using Enterprise manager we can insert columns inbetween, but enterprise
manager does this in the below way
1. Export data out
2. Drop the table
3. Create the new table with new column added
4. Loads back the data.
This will not be a good mechanism if your data volume in your table is high.
Thanks
Hari
SQL Server MVP
"Vic" <vduran@.specpro-inc.com> wrote in message
news:080601c53f17$3e349bc0$a501280a@.phx.gbl...
> How can I add a column to the begining of the table using
> t-sql? I know the syntax, but it adds it as the last
> column on the table.
> TIA,
> Vic|||> This will not be a good mechanism if your data volume in your table is
high.
Also, you should not rely on column order. In a relation, column and row
order is not defined. You should always specify column names in your DML
statements.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Adding Column To A Table At a Specific Position

Dear All
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.htmlOn Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:

>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:

> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:

> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>

Adding Column To A Table At a Specific Position

Dear All
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.html
On Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:

>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:

> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>
|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:

> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>

Adding Column To A Table At a Specific Position

Dear All
Is there a way in T-Sql where I can add a column in a specific order to an
existing table. For instance, if I have the following table
1. EmpID
2.Fname
3. Mname
4. Lname
5. Add1
6.Add2
I want to insert a Column PHNo before column 5. Is there a possibility in
T-SQL for this.
Hope to hear from you soon.
TIA.
Saleem
--
Muhammad Saleem Usmani
9-Sassi Town House
Abdullah Haroon Road
Karachi
PH: 92-21-521-2042-3
FAX: 92-21-521-2046
CELL: 0300-822-0105
support-karachi@.autosoftdynamics.com
usmani@.autosoftdynamics.com
I choose Polesoft Lockspam to fight spam, and you?
http://www.polesoft.com/refer.htmlOn Thu, 4 Nov 2004 15:25:12 +0500, Muhammad Saleem Usmani wrote:
>I want to insert a Column PHNo before column 5. Is there a possibility in
>T-SQL for this.
Hi Muhammad,
No, the only way you can achieve this is by dropping and recreating the
table.
Why would you want this? A table in a relational database is UNordered by
definition; you should assign no meaning to the cardinal positions of
columns, nor should you use code that relies on column order.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
No. The only way would be to re-create the table with the new structure and
copy the data over.
If you need to have a specific order for a select, you should specifcy the
columns in the select statement.
Regards
Mike
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>|||I agree with the others... When I first started doing SQL I was all into the
physical ordering of the columns, because my background was COBOL, and it
was important there...But trying to keep up with this will be a nightmare...
It would be better to begin to enforce with the programmers, never to select
* , always use column names. Always use the column list in an insert
statement ie
insert into mytab ( col1, colu2) values ( 1,2)
this frees you up from the logical ordering of columns...
Good luck to you!
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Muhammad Saleem Usmani" <support-karachi@.autosoftdynamics.com> wrote in
message news:%23VUd$glwEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>|||No. Just in EM.
"Muhammad Saleem Usmani" wrote:
> Dear All
> Is there a way in T-Sql where I can add a column in a specific order to an
> existing table. For instance, if I have the following table
> 1. EmpID
> 2.Fname
> 3. Mname
> 4. Lname
> 5. Add1
> 6.Add2
> I want to insert a Column PHNo before column 5. Is there a possibility in
> T-SQL for this.
> Hope to hear from you soon.
> TIA.
> Saleem
>
> --
> Muhammad Saleem Usmani
> 9-Sassi Town House
> Abdullah Haroon Road
> Karachi
> PH: 92-21-521-2042-3
> FAX: 92-21-521-2046
> CELL: 0300-822-0105
> support-karachi@.autosoftdynamics.com
> usmani@.autosoftdynamics.com
>
> I choose Polesoft Lockspam to fight spam, and you?
> http://www.polesoft.com/refer.html
>
>