Collation problem: order

Showing posts with label order. Show all posts

Tuesday, March 27, 2012

Combining strings

Let's assume we have two tables - Customers and Orders.

I need a query that will return a string value containing a list of order titles from the Orders table for a particular customer.

How can this be done?

Thanks.

Hi vkh,

you have to use a function approach for this, as it can be seen on (sort of, I would vary this one to a temporary table rather than a cursor, but just to show you the iterative approach)

http://www.sqlteam.com/item.asp?ItemID=2368

HTH; jens Suessmeyer.

|||Thank you!

Thursday, March 22, 2012

CombinedID

Hi,

Please find the SQL script to create a mini version of my database and some data at the bottom of this post. Please insert the data in the order I posted below.

Here's a quick narative of what the database is all about. This is a database that allows our company to keep track of all new contracts -- we call them deals -- and our progress on these contracts. You can think of it as a project management system. The key point here is that we have different types of projects so each contract goes through a different set of phases as we work on it. The phases a certain type of project goes through are
determined in tblPhaseType. So when we have a new deal, we put its data into tblDeals table and select the PhaseTypeID for that deal based on the kind of project it is.

Now, here's my question. Determining what phases we've completed for a specific deal is real easy. All I have to do is just, select the completed phases from tblProduction and INNER JOIN it with tblDeals and tblCompany and I'm done.

My challange is determining the remaining phases. For this, I have to first determine all phases for a specific contract then subtract out the ones that are already completed. To achieve this, I came up with a method but I'm not sure if that's the best way to do it. I combine the the Deal and Phase ID's to come up with a unique ID which I call CombinedID. Then I tell my stored procedure to exclude all CombinedID's that are in the sub-query which comes from tblProduction.

Here's the code for that stored procedure.
--------


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOCREATE PROCEDURE dbo.spRemainingPhases
AS SELECT dbo.tblDeals.DealID, dbo.tblPhase.PhaseID,
CAST(dbo.tblDeals.DealID AS varchar(2)) + CAST(dbo.tblPhase.PhaseID AS
varchar(2))
 AS CombinedID
FROM dbo.tblDeals INNER JOIN
 dbo.tblPhaseType ON dbo.tblPhaseType.PhaseTypeID =
dbo.tblDeals.PhaseTypeID INNER JOIN
 dbo.tblPhase ON dbo.tblPhaseType.PhaseTypeID =
dbo.tblPhase.PhaseTypeID
WHERE (NOT ((CAST(dbo.tblDeals.DealID AS varchar(2)) +
CAST(dbo.tblPhase.PhaseID AS varchar(2))) IN
 (SELECT CAST(tblProduction.DealID AS
varchar(2)) + CAST(tblProduction.PhaseID AS varchar(2)) AS CombinedID
 FROM tblProduction)))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--------

My question is: Is this the best way to handle this. I'm just not so crazy
about this CombinedID business. I should be able use and AND construct and
use DealID and PhaseID as they are. I just couldn't get this to work though.

Here's the SQL Script create all the necessary tables in the database
----------


if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
GOif exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/10/2003
10:25:26 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblProduction]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/10/2003 10:25:26
AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDeals]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/10/2003 10:25:26
AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhase]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/10/2003
10:25:26 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/10/2003
10:25:26 AM ******/
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblPhaseType]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/10/2003
10:25:27 AM ******/
CREATE TABLE [dbo].[tblCompany] (
 [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/10/2003
10:25:28 AM ******/
CREATE TABLE [dbo].[tblPhaseType] (
 [PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/10/2003 10:25:29
AM ******/
CREATE TABLE [dbo].[tblDeals] (
 [DealID] [int] IDENTITY (1, 1) NOT NULL ,
 [CompanyID] [int] NOT NULL ,
 [DealDate] [smalldatetime] NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [CashAmount] [smallmoney] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/10/2003 10:25:29
AM ******/
CREATE TABLE [dbo].[tblPhase] (
 [PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL ,
 [PhaseTypeID] [tinyint] NOT NULL ,
 [PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
 [PhasePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/10/2003
10:25:29 AM ******/
CREATE TABLE [dbo].[tblProduction] (
 [TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
 [DealID] [int] NOT NULL ,
 [PhaseID] [tinyint] NOT NULL ,
 [TransactionTimeStamp] [smalldatetime] NOT NULL ,
 [Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompany] ADD
 CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
 (
 [CompanyID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhaseType] ADD
 CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED
 (
 [PhaseTypeID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
 (
 [DealID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhase] ADD
 CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED
 (
 [PhaseID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR
[TransactionTimeStamp],
 CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED
 (
 [TransactionID]
 ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
 CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
 (
 [CompanyID]
 ) REFERENCES [dbo].[tblCompany] (
 [CompanyID]
 ),
 CONSTRAINT [FK_tblDeals_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblPhase] ADD
 CONSTRAINT [FK_tblPhase_tblPhaseType] FOREIGN KEY
 (
 [PhaseTypeID]
 ) REFERENCES [dbo].[tblPhaseType] (
 [PhaseTypeID]
 )
GO
ALTER TABLE [dbo].[tblProduction] ADD
 CONSTRAINT [FK_tblProduction_tblDeals] FOREIGN KEY
 (
 [DealID]
 ) REFERENCES [dbo].[tblDeals] (
 [DealID]
 ),
 CONSTRAINT [FK_tblProduction_tblPhase] FOREIGN KEY
 (
 [PhaseID]
 ) REFERENCES [dbo].[tblPhase] (
 [PhaseID]
 )
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the type of
phase structure this deal will go through', N'user', N'dbo', N'table',
N'tblDeals', N'column', N'PhaseTypeID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage
value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column',
N'PhasePercentage'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo',
N'table', N'tblProduction', N'column', N'TransactionTimeStamp'
GO

=========================================

And here's the data
----------


INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited')INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases')
INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer
Info',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write
script',2.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.50000
0000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit
commercial',2.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer
info',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write
script',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print
ad',1.500000000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.50000
0000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000
000000000e-001)
INSERT INTO [tblPhase]
([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000
000000000000e-001)
INSERT INTO [tblDeals]
([CompanyID],[DealDate],[PhaseTypeID],[CashAmount])VALUES(1,'Aug 5 2003
12:00:00:000AM',1,120.0000)
INSERT INTO [tblDeals]
([CompanyID],[DealDate],[PhaseTypeID],[CashAmount])VALUES(2,'Sep 9 2003
12:00:00:000AM',2,150.0000)
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10
2003 10:23:00:000AM','Received company logo')
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10
2003 10:23:00:000AM','Finished writing script')
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10
2003 10:23:00:000AM','Just received company info')
INSERT INTO [tblProduction]
([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10
2003 10:24:00:000AM','Finished designing ad copy')

The key here is a LEFT OUTER JOIN to your tblProduction. This query should get you the remaining phases for all of your deals:


SELECT
dbo.tblDeals.DealID,
dbo.tblPhase.PhaseID
FROM
dbo.tblPhaseType
INNER JOIN
dbo.tblDeals ON dbo.tblDeals.PhaseTypeID = dbo.tblPhaseType.PhaseTypeID
INNER JOIN
dbo.tblPhase ON dbo.tblPhaseType.PhaseTypeID = dbo.tblPhase.PhaseTypeID
LEFT OUTER JOIN
dbo.tblProduction ON dbo.tblProduction.DealID = dbo.tblDeals.DealID AND dbo.tblProduction.PhaseID = dbo.tblPhase.PhaseID
WHERE
 dbo.tblProduction.DealID IS NULL

Terri|||That's exactly it. Thank you very much Terri. Your help is very much appreciated.

Monday, March 19, 2012

combine data from different records with same ID

I have a table contains comments. User scan create as many comments they wa
nt.
my job is to combine and rearrange all comments in order of dates and time.
acct date time Comments
-- -- -- ---
08 01/04/2001 170852 0Conveyed stips.
84 01/04/2001 173740 test!
84 01/04/2001 173812 test2!
02 01/04/2001 180502 spoke to mbr and nd
01 01/05/2001 115548 joint life
01 01/05/2001 115550 Please fund loan.
18 01/05/2001 185220 Sent
18 01/05/2001 185238 Sent completed application
Desired Result:
acct Comments
----
--
08 Conveyed stips. 01/04/2001: 170852
84 test! - Ford 01/04/2001: 173740 test2! 01/04/2001: 173812
02 spoke to mbr and nd 01/04/2001: 180502
01 joint life 01/05/2001: 115548 Please fund loan. 01/05/2001: 1155
50
18 Sent 01/05/2001: 185220 Sent completed application 01/05/2001:
185238
Thanks in Advance,
CulamUse a document management system (textbase)and not SQL system.|||You haven't stated what datatypes these columns are.
Do type conversions as required and use the concatenation operator ( + ) to
achieve the results you want. What seems to be the difficulty in doing so?
Anith|||I converted all the data to VARCHAR and using a operator (+) to combine data
,
but I need to roll up all records with same id into one record. That is
what I need help in.
"Anith Sen" wrote:

> You haven't stated what datatypes these columns are.
> Do type conversions as required and use the concatenation operator ( + ) t
o
> achieve the results you want. What seems to be the difficulty in doing so?
> --
> Anith
>
>|||I see. This does not seem to be a right job for SQL Server. One good
approach to such problems is to retrieve the resultset and leverage the
string concatenation and loop-like functionality of a client programming
language to create the result.
The approaches in SQL are all more or less complex and cumbersome. Some of
the such hacks can be found at:
http://groups.google.ca/groups?selm...FTNGP09.phx.gbl
Anith

Sunday, March 11, 2012

com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover

Hi all

I have written a shell script that connects to a SQL Server 2005 database from Linux in order to monitor various areas of SQL.

One of the databases that are being monitored is mirrored, which is no problem in itself as I use the failoverPartner property in my connection string before I pass the TSQL. Unfortunately when the principal/mirror status changes, I get a constant stream of Failure Audit (Login failure) messages in the Mirror server Windows event log even though the failoverPartner property works and redirects to the partner, returning the correct information.

Here is a trace of the connection:

22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property : serverName Value:SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:databaseNameValue:TESTDATABASE
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:failoverPartnerValue:SERVER2
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:integratedSecurityValue:false
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:loginTimeoutValue:3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: Calling securityManager.checkConnect(SERVER1,1433)
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: securityManager.checkConnect succeeded.
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: Start time: 1172160120083 Time out time: 1172160123083 Timeout Unit Interval: 240
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt server name: SERVER1 port: 1433
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt endtime: 1172160120323
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt No: 0
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: Connecting with server: SERVER1 port: 1433 Timeout slice: 232 Timeout Full: 3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: *** SQLException:[Thread[main,5,main], IO:5571e, Dbc:a8327] com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "TESTDATABASE" requested by the login. The login failed. Msg 4060, Level 11, State 1, Cannot open database "TESTDATABASE" requested by the login. The login failed.
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt server name: SERVER2 port: 1433
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt endtime: 1172160120369
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt No: 1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: Connecting with server: SERVER2 port: 1433 Timeout slice: 237 Timeout Full: 3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: adding new failover info server: SERVER1 instance: null database: TESTDATABASE server provided failover: SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.FailoverInfo failoverAdd
FINE: Failover detected. failover partner=SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.FailoverMapSingleton putFailoverInfo
FINE: Failover map add server: SERVER1; database:TESTDATABASE; Mirror:SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: End of connect
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerStatement <init>
FINE: Statement properties ID:0 Connection:1 Result type:1003 (2003) Concurrency:1007 Fetchsize:128 bIsClosed:false tdsVersion:com.microsoft.sqlserver.jdbc.TDSVersion@.15fea60 bCp1252:false useLastUpdateCount:true isServerSideCursor:false
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteStatement
FINE: Executing (not server cursor)
USE TESTDATABASE
SELECT "TESTCOLUMN" FROM TEST_TABLE ORDER BY DateTime
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection close
FINE: Closing connection ID:1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property : serverName Value:SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:databaseNameValue:TESTDATABASE
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:failoverPartnerValue:SERVER2
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:integratedSecurityValue:false
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.Util parseUrl
FINE: Property:loginTimeoutValue:3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: Calling securityManager.checkConnect(SERVER1,1433)
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: securityManager.checkConnect succeeded.
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: Start time: 1172160120736 Time out time: 1172160123736 Timeout Unit Interval: 240
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt server name: SERVER1 port: 1433
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt endtime: 1172160120976
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt No: 0
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: Connecting with server: SERVER1 port: 1433 Timeout slice: 233 Timeout Full: 3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerException logException
FINE: *** SQLException:[Thread[main,5,main], IO:5571e, Dbc:a8327] com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open database "TESTDATABASE" requested by the login. The login failed. Msg 4060, Level 11, State 1, Cannot open database "TESTDATABASE" requested by the login. The login failed.
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt server name: SERVER2 port: 1433
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt endtime: 1172160121031
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: This attempt No: 1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connectHelper
FINE: Connecting with server: SERVER2 port: 1433 Timeout slice: 236 Timeout Full: 3
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover
FINE: adding new failover info server: SERVER1 instance: null database: TESTDATABASE server provided failover: SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.FailoverInfo failoverAdd
FINE: Failover detected. failover partner=SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.FailoverMapSingleton putFailoverInfo
FINE: Failover map add server: SERVER1; database:TESTDATABASE; Mirror:SERVER1
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerConnection connect
FINE: End of connect
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerStatement <init>
FINE: Statement properties ID:0 Connection:1 Result type:1003 (2003) Concurrency:1007 Fetchsize:128 bIsClosed:false tdsVersion:com.microsoft.sqlserver.jdbc.TDSVersion@.15fea60 bCp1252:false useLastUpdateCount:true isServerSideCursor:false
22-Feb-2007 16:02:00 com.microsoft.sqlserver.jdbc.SQLServerStatement doExecuteStatement
FINE: Executing (not server cursor)
USE TESTDATABASE
SELECT DATEDIFF(SECOND, DATETIME, GETDATE())
FROM TEST_TABLE
22-Feb-2007 16:02:01 com.microsoft.sqlserver.jdbc.SQLServerConnection close
FINE: Closing connection ID:1

Connect string:

java -classpath /conf/javasql/sqljdbc_1.1/enu/sqljdbc.jar:/conf/javasql/jisql/lib/jisql.jar com.xigole.util
.sql.Jisql -user SOMEUSER -password SOMEPASSWORD -driver com.microsoft.sqlserver.jdbc.SQLServerDriver -input $QUERYFILE -cstring jdbc:sqlserver://SERVER1;DataBaseName=TESTDATABASE;failoverPartner=SERVER2;loginTimeout=3

I cant supply a native database in the connection string (i.e Master) and then switch to the mirrored database in TSQL because the failoverPartner property does not apply to the session, only to the initial connection.

Has anyone got any suggestions?

Thanks

The client will try to connect to the last successfully connected server in the current session. If the database on that server is currently inactive (it is the case when a the current active database becomes inactive and the mirror takes over) a login failure occurs, this gets logged in the windows even log as a login failure event. The driver will catch this and retry the connection to the mirror and the connection succeeds. This behavior is expected. What is your worry here? You do not want to see this error messages in the log? Or you are simply worried about the these messages and wants to understand what is going on.

Mugunthan

COM security Policy

I am trying to create a data extension and this extension has to access a
COM+ application in order to run as a domain user to access files on the
network but every time I try running this code it errors out with a security
permission error. How do I setup permission to access the COM+ application?
--
RYAN SCHOUTENNever mind this is not an issue
--
RYAN SCHOUTEN
"RYAN SCHOUTEN" <ryanttr@.yahoo.com> wrote in message
news:uyU736ERFHA.2744@.TK2MSFTNGP10.phx.gbl...
> I am trying to create a data extension and this extension has to access a
> COM+ application in order to run as a domain user to access files on the
> network but every time I try running this code it errors out with a
security
> permission error. How do I setup permission to access the COM+
application?
> --
> RYAN SCHOUTEN
>

COLUMNS_UPDATED()

SQL Server 2000
BOL says:
The COLUMNS_UPDATED function returns the bits in order from left to right,
with the least significant bit being the leftmost. The leftmost bit
represents the first column in the table; the next bit to the right
represents the second column, and so on.
But in the example, bitmask to check the colums 2,3,4 calculated as 14,
in which rightmost bit is the first column in the table. is there something
wrong here?Yeah. what you are asking makes sense.
Looks like the bit stream is looked at as a string than a binary number, if
thats what you concern is.
Say for a 8 column table the first 4 are updated, then the columns_updated()
will read as
1111
and if 2 and 3 are updated its going to be
011
All it means is that the 0 has a significance to give out the position of
the column being updated or not and we cannot say 011 and 11 are equal in
this context.
and coming to your question,
14 is read is 0111 rather than 1110.
Its using a reverse binary system.. I believe.. But a good point you pointed
out nevertheless.
Lets wait for the other's comments though :)|||prefect a crit :
> SQL Server 2000
> BOL says:
> The COLUMNS_UPDATED function returns the bits in order from left to right,
> with the least significant bit being the leftmost. The leftmost bit
> represents the first column in the table; the next bit to the right
> represents the second column, and so on.
NOT AT ALL !
The bit calculate is based on the ordinal position deliver by
INFORMATION_SCHEMA.COLUMNS
Dmo :
CREATE TABLE T_TEST_BITCOLS_TBC
(COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT,
COL5 INT)
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL2
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL3
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL5
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL2 INT
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL6 INT
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
GO
CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
(TABLE_NAME SYSNAME,
BIT_COLS INT)
GO
CREATE TRIGGER E_U_TCU
ON T_TEST_BITCOLS_TBC
FOR UPDATE
AS
INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
GO
UPDATE T_TEST_BITCOLS_TBC
SET COL2 = 0
GO
SELECT *
FROM T_TRIGGER_COLS_UPDATED_TCU
TABLE_NAME BIT_COLS
-- --
T_TEST_BITCOLS_TBC 32
SELECT COLUMN_NAME, ORDINAL_POSITION,
POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
COLUMN_NAME ORDINAL_POSITION BIT_COL
-- -- --
COL1 1 1
COL4 4 8
COL2 6 32 <====
COL6 7 64

> But in the example, bitmask to check the colums 2,3,4 calculated as 14,
> in which rightmost bit is the first column in the table. is there somethi
ng
> wrong here?
YES !
>
A +
--
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||thanks , i guess rightmost bit of every byte corresponds to first one of
the every 8 column regarding the ordinal of column.
surely , bit order of columns is read from system tables.
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:OeCvlFFaGHA.1228@.TK2MSFTNGP02.phx.gbl...
> prefect a crit :
> NOT AT ALL !
>
> The bit calculate is based on the ordinal position deliver by
> INFORMATION_SCHEMA.COLUMNS
> Dmo :
>
> CREATE TABLE T_TEST_BITCOLS_TBC
> (COL1 INT,
> COL2 INT,
> COL3 INT,
> COL4 INT,
> COL5 INT)
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL2
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL3
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL5
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL2 INT
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL6 INT
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
> GO
> CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
> (TABLE_NAME SYSNAME,
> BIT_COLS INT)
> GO
>
> CREATE TRIGGER E_U_TCU
> ON T_TEST_BITCOLS_TBC
> FOR UPDATE
> AS
> INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
> SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
> GO
> UPDATE T_TEST_BITCOLS_TBC
> SET COL2 = 0
> GO
> SELECT *
> FROM T_TRIGGER_COLS_UPDATED_TCU
> TABLE_NAME BIT_COLS
> -- --
> T_TEST_BITCOLS_TBC 32
>
> SELECT COLUMN_NAME, ORDINAL_POSITION,
> POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
> COLUMN_NAME ORDINAL_POSITION BIT_COL
> -- -- --
> COL1 1 1
> COL4 4 8
> COL2 6 32 <====
> COL6 7 64
>
>
>
> YES !
>
> A +
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

columns order in entities

Hi friends
i've report model with entities that depend on views. my question is , currently all columns in a entity in the same order as view has them. i mean if i have a view like below

create view vname
as
select name,addess,status from mytable

when i create entity based on this i get attributes in this order
name,address,status
but i want
address,name,status

how can i change it to alphabatical order ?
i know i can change manually in model designer window but there are too many fields to sort !!
is there any better way of doing this ?
Thanks for your helphi guys
so there is no way ?|||is it something for next version ?

columns order in entities

Hi friends
i've report model with entities that depend on views. my question is , currently all columns in a entity in the same order as view has them. i mean if i have a view like below

create view vname
as
select name,addess,status from mytable

when i create entity based on this i get attributes in this order
name,address,status
but i want
address,name,status

Thursday, March 8, 2012

Columns Order

Dear All
Can I Alter tables to change the columns orders, Or Add Column in a
specified order? How?
AhmedAhmed
I don't think that it make sense. It does not matter what is a column's
order though you can SELECT col1,col2 Or SELECT col,col1
If you persist, you can add the column by EM in order that you want.
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:%232KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
> Ahmed
>|||No and no. Only way is to re-create the table (which is what Enterprise Mana
ger does when you do it
graphically).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:%232KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a specif
ied order? How?
> Ahmed
>|||> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
More importantly, WHY?
http://www.aspfaq.com/2528|||The only reason I can think of for changing the order of columns is that you
want to use SELECT *. Using SELECT * is a common bad practice usually
committed by the lazy or the incompetent. I only use it in query analyzer
for debugging. SELECT * is one of the things I look for in profile traces
when I'm analyzing a system. If I find it in a trace, then I know that I
will have to spend a lot more time determining whether or not any change I
make will break existing code, and consequently, I will have to charge the
customer a lot more money at every stage of the project.
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:#2KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
> Ahmed
>|||Dear All
Thanks for replay
I know it's not a live or death feature, I need to arrange the columns
according to some rules, like if it is a primary key or doesn't allow null,
to make the table structure in client database same as the development
database, or anything else. I'm just asking how to do it.
Anyway, if we can do it through the enterprise manager I think it is not
difficult to make it through the T_SQL.
Thanks
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:Om6jiC7sFHA.524@.TK2MSFTNGP12.phx.gbl...
> The only reason I can think of for changing the order of columns is that
> you
> want to use SELECT *. Using SELECT * is a common bad practice usually
> committed by the lazy or the incompetent. I only use it in query analyzer
> for debugging. SELECT * is one of the things I look for in profile traces
> when I'm analyzing a system. If I find it in a trace, then I know that I
> will have to spend a lot more time determining whether or not any change I
> make will break existing code, and consequently, I will have to charge the
> customer a lot more money at every stage of the project.
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:#2KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
>|||Ahmed Hashish (a_hashish@.hotmail.com) writes:
> Thanks for replay
> I know it's not a live or death feature, I need to arrange the columns
> according to some rules, like if it is a primary key or doesn't allow
> null, to make the table structure in client database same as the
> development database, or anything else. I'm just asking how to do it.
> Anyway, if we can do it through the enterprise manager I think it is not
> difficult to make it through the T_SQL.
You are completely right, and Brian is wrong. There are several good reasons
why one want to have columns in a certain order, and SELECT * is not one
of them. But have PK columns first in the table, and in the correct order
is one. And wanting have logically related columns close to each other is
another.
Anyway, there is no syntax for this, not even in SQL 2005. What Enterprise
Manager does is to create a new table, and then move over data to that
table, and recreate foreign keys etc. In a script, I should add, that has
several serious flaws. While these are fairly easy to address, an ALTER
TABLE command would be easier to use.
On MSDN Product Feedback Centre you can submit bugs and suggestions for
SQL 2005, and then other people can vote on these submissions. One
suggestion that was submitted earlier this year, was precisely about this
matter. By now, it has assembled 21 votes, which I think makes it the
most voted-on suggestion for SQL 2005. So you are not the first one to
ask for this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> You are completely right, and Brian is wrong. There are several good
reasons
> why one want to have columns in a certain order, and SELECT * is not one
> of them. But have PK columns first in the table, and in the correct order
> is one. And wanting have logically related columns close to each other is
> another.
The location of columns in a table is immaterial either from a performance
standpoint, or for any other reason. A primary key constraint always
creates an index, which means that the key values are copied into the a
B-tree structure, so there is no need for the columns to be adjacent.
Variable length columns are always separated from fixed length columns at
the physical layer, and depending on the option settings when the table is
created, nullable fixed-length character columns are treated in the same way
as variable-length character columns. In addition, SQL Server reads an
extent at a time, so there is no performance penalty for having intervening
columns within a row since the entire row is in memory anyway. Therefore,
it is pointless to try to force the columns to be in a specific order,
unless you're going to use SELECT *.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CE72C7ED62Yazorman@.127.0.0.1...
> Ahmed Hashish (a_hashish@.hotmail.com) writes:
columns
> You are completely right, and Brian is wrong. There are several good
reasons
> why one want to have columns in a certain order, and SELECT * is not one
> of them. But have PK columns first in the table, and in the correct order
> is one. And wanting have logically related columns close to each other is
> another.
> Anyway, there is no syntax for this, not even in SQL 2005. What Enterprise
> Manager does is to create a new table, and then move over data to that
> table, and recreate foreign keys etc. In a script, I should add, that has
> several serious flaws. While these are fairly easy to address, an ALTER
> TABLE command would be easier to use.
> On MSDN Product Feedback Centre you can submit bugs and suggestions for
> SQL 2005, and then other people can vote on these submissions. One
> suggestion that was submitted earlier this year, was precisely about this
> matter. By now, it has assembled 21 votes, which I think makes it the
> most voted-on suggestion for SQL 2005. So you are not the first one to
> ask for this.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||> I also like to start the table definition with the columns of the
> Primary Key, preferably in the order of the Primary Key definition.
Sure, when you're first designing the table, I think we all tend to create
the column structure in a logical order.
Whether it is a logical thing to do after the table has been created, I'm
not so sure about that. Why would we be adding the primary key to the table
as an afterthought?
More often than not, this request seems to stem from issues like "I want all
my numeric columns together" or "I want such and such column at the 'end' of
the table."|||Brian Selzer (brian@.selzer-software.com) writes:
> The location of columns in a table is immaterial either from a
> performance standpoint, or for any other reason. A primary key
> constraint always creates an index, which means that the key values are
> copied into the a B-tree structure, so there is no need for the columns
> to be adjacent. Variable length columns are always separated from fixed
> length columns at the physical layer, and depending on the option
> settings when the table is created, nullable fixed-length character
> columns are treated in the same way as variable-length character
> columns. In addition, SQL Server reads an extent at a time, so there is
> no performance penalty for having intervening columns within a row since
> the entire row is in memory anyway. Therefore, it is pointless to try
> to force the columns to be in a specific order, unless you're going to
> use SELECT *.
If only computers were reading the table, you would be right. Almost, more
a little later.
But the table is also used by people. If you are going to develop something
in a database, it may be perfectly OK to you if you look at the table
definition and the columns appear in the order they were added to the table.
Personally, I prefer to see column in a logical order, for instance PK
column first, auditing columns at the end, and related column adjancent
to each other.
This also matters when you do a SELECT * from Query Analyzer for debugging
reasons. Which I do a lot. (SELECT * in code is another matter.)
There is also a technical reason. Say that your column order is accidental,
and now you are to bulk load out from a table one server to the same table
on another server. If you know about it, you write a format file (jolly
good fun for a 100-column table!). If you don't know about it, you may
be informed of errors. Then again you may not, because the colunms that
were in different order were of the same data types.
Column order is not about performance. It's about usability.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Saturday, February 25, 2012

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
BkThe order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:
> There is no 'default' ordering of rows. Itzik sheds some interesting light
> on the subject:
> http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
> http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
> http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> > Can someone tell me what defines the order of SQL output columns when i
> > use a
> > default query on a table like "SELECT * from <Table> ".
> >
> > Is there a way to alter the default ORDER of these columns?
> >
> > I am aware of the ORDER BY but will not be able to use it for various
> > reasons.
> >
> > Regards
> > Bk
>|||As mentioned by Arnie, it is considered a bad practice to use "SELECT *"
in production code (with the exception of its use in an EXISTS clause).
When using SELECT * on a table, the columns in the resultset will match
the order in the table definition. There is no way to change this using
DML. The only way to change its order (apart from explicitely naming the
columns in the desired order) is to redefine the table.
HTH,
Gert-Jan
BK-Chicago wrote:
> Can someone tell me what defines the order of SQL output columns when i use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
Bk
The order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk
|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:

> There is no 'default' ordering of rows. Itzik sheds some interesting light
> on the subject:
> http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
> http://www.sqlmag.com/article/articleid/92887/sql_server_blog_92887.html
> http://www.sqlmag.com/article/articleid/92888/sql_server_blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
>

Column Ordering in SELECT statement

Can someone tell me what defines the order of SQL output columns when i use
a
default query on a table like "SELECT * from <Table> ".
Is there a way to alter the default ORDER of these columns?
I am aware of the ORDER BY but will not be able to use it for various
reasons.
Regards
BkThe order is non-deterministic. It usually is the same order as the PK on
the base table (first FROM table), but may vary as query plans change.
ORDER BY is the only way to force an output order.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||If you are referring to how the columns are presented, it is a 'best
practice' to explicitly specify the columns desired, in the order desired.
Using SELECT * is universally considered a very 'bad' practice.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||There is no 'default' ordering of rows. Itzik sheds some interesting light
on the subject:
http://www.sqlmag.com/article/artic...blog_92886.html
http://www.sqlmag.com/article/artic...blog_92887.html
http://www.sqlmag.com/article/artic...blog_92888.html
Hope this helps.
Dan Guzman
SQL Server MVP
"BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
> Can someone tell me what defines the order of SQL output columns when i
> use a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk|||The articles were quite useful. Thanks for the info.
"Dan Guzman" wrote:

> There is no 'default' ordering of rows. Itzik sheds some interesting ligh
t
> on the subject:
> http://www.sqlmag.com/article/artic...blog_92886.html
> http://www.sqlmag.com/article/artic...blog_92887.html
> http://www.sqlmag.com/article/artic...blog_92888.html
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "BK-Chicago" <BKChicago@.discussions.microsoft.com> wrote in message
> news:71E80EA1-B606-45AF-B8A3-D48699A27ABF@.microsoft.com...
>|||As mentioned by Arnie, it is considered a bad practice to use "SELECT *"
in production code (with the exception of its use in an EXISTS clause).
When using SELECT * on a table, the columns in the resultset will match
the order in the table definition. There is no way to change this using
DML. The only way to change its order (apart from explicitely naming the
columns in the desired order) is to redefine the table.
HTH,
Gert-Jan
BK-Chicago wrote:
> Can someone tell me what defines the order of SQL output columns when i us
e a
> default query on a table like "SELECT * from <Table> ".
> Is there a way to alter the default ORDER of these columns?
> I am aware of the ORDER BY but will not be able to use it for various
> reasons.
> Regards
> Bk

Column order/presentation in virtual table (result set from viewor UDF)

I was just messing around with some ad hoc views and table returning
UDFs today so I could look at and print out data from a small table
and noticed something strange.

If I stick my select statement into a View the columns are returned in
the order I specify in the SELECT, but if the same statement is in a UDF
(so I can specify a parameter), the columns are not returned in the
order specified in statement.

I know that relations don't have a specified column order, but it was my
understanding that a SELECT statement could be used to define how you
want your data presented. Views seem to respect the order specified in
the SELECT, but functions don't.

What am I missing? Is there some way to force the order of the columns
returned from a SELECT?

View:

CREATE VIEW dbo.View1
AS
SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,
ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE '4.2.%')

Column order from this view:
Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,
ElseStmt, NextStmt

Function:

ALTER FUNCTION dbo.Function1
(@.SearchPrm varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,
ChildStmt, ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE @.SearchPrm) )

Column order from this function:
Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,
ParentStmt, ForStmt

Table:
(I know that this table isn't entirely normalized, but it serves my
purposes to have a matrix instead of a fully normalized relation):

CREATE TABLE dbo.tblStmt (
StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,
Ident VARCHAR(255),
Text TEXT,
ErrorText TEXT,
Type INT,
ParentStmt VARCHAR(255),
ChildStmt VARCHAR(255),
IfStmt VARCHAR(255),
ForStmt VARCHAR(255),
ThenStmt VARCHAR(255),
ElseStmt VARCHAR(255),
NextStmt VARCHAR(255),
FullName VARCHAR(255),
LocalName VARCHAR(255),
Method INT
)

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')

INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')

INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')I forgot to mention an important detail. I'm creating the VIEW and
FUNCTION within the context of a Microsoft Access ADP file that's
pointed at the SQL Server 2000 database in question.

If I execute this statement in Query Analyzer:
SELECT * FROM dbo.Function1('4.2.%')
the columns are output as specified in the column list inside the
Function definition (Ident, Text, ..., etc.).

If I double-click on the Function's object in MS Access and enter 4.2.%
in the prompt for the parameter, then the column list is output in the
strange order as noted below (Type, Text, ElseStmt, ..., etc.).

So, this may actually be a Microsoft Access problem, but if anyone has
any information, I'd appreciate it. Thanks.

Beowulf wrote:

Quote:

Originally Posted by

I was just messing around with some ad hoc views and table returning
UDFs today so I could look at and print out data from a small table
and noticed something strange.
>
If I stick my select statement into a View the columns are returned in
the order I specify in the SELECT, but if the same statement is in a UDF
(so I can specify a parameter), the columns are not returned in the
order specified in statement.
>
I know that relations don't have a specified column order, but it was my
understanding that a SELECT statement could be used to define how you
want your data presented. Views seem to respect the order specified in
the SELECT, but functions don't.
>
What am I missing? Is there some way to force the order of the columns
returned from a SELECT?
>
View:
>
CREATE VIEW dbo.View1
AS
SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,
ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE '4.2.%')
>
Column order from this view:
Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,
ElseStmt, NextStmt
>
Function:
>
ALTER FUNCTION dbo.Function1
(@.SearchPrm varchar(255))
RETURNS TABLE
AS
RETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,
ChildStmt, ThenStmt, ElseStmt, NextStmt
FROM dbo.tblStmt
WHERE (Ident LIKE @.SearchPrm) )
>
Column order from this function:
Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,
ParentStmt, ForStmt
>
Table:
(I know that this table isn't entirely normalized, but it serves my
purposes to have a matrix instead of a fully normalized relation):
>
CREATE TABLE dbo.tblStmt (
StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,
Ident VARCHAR(255),
Text TEXT,
ErrorText TEXT,
Type INT,
ParentStmt VARCHAR(255),
ChildStmt VARCHAR(255),
IfStmt VARCHAR(255),
ForStmt VARCHAR(255),
ThenStmt VARCHAR(255),
ElseStmt VARCHAR(255),
NextStmt VARCHAR(255),
FullName VARCHAR(255),
LocalName VARCHAR(255),
Method INT
)
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')
>
INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,
NextStmt
VALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')
>
INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmt
VALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')

|||It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Razvan|||Razvan Socol wrote:

Quote:

Originally Posted by

It is indeed an MS Access problem (specific to ADP-s). You can reorder
the columns in the resulting datasheet freely and Access remembers the
position and width of the columns when you open the table/view/function
again. Access usually asks "Do you want to save changes to the layout
of function '...' ?". If you respond "yes", it stores this information
in the database, using extended properties for the objects and for the
columns.

Thanks for the reply, even though the question turned out to be off-topic.

Column order issue

Hi
When I want to use all (or at least many) columns in a table, I'm used to
just click the "Column" header in the Object browser window and then drag it
over to the "code pane". In Query Analyzer I was use to get all the columns
in the same order as they where showed in the object browser to the left.
When using MicroSoft SQL Server Mamagement Studio, I get the columns in
alphabetically order when I drag them over, eventhough they are shown in
another order in the Object Browser window.
Does anybody know if this is a setting that can be changed somewhere?
Regards
SteenAre you on May CTP? When I drag the columns folder to the query window, I get the columns listed in
the same order as in my CREATE TABLE statement...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi
> When I want to use all (or at least many) columns in a table, I'm used to just click the "Column"
> header in the Object browser window and then drag it over to the "code pane". In Query Analyzer I
> was use to get all the columns in the same order as they where showed in the object browser to the
> left. When using MicroSoft SQL Server Mamagement Studio, I get the columns in alphabetically order
> when I drag them over, eventhough they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
> Regards
> Steen
>|||Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:
> Are you on May CTP? When I drag the columns folder to the query
> window, I get the columns listed in the same order as in my CREATE
> TABLE statement...
> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>> Hi
>> When I want to use all (or at least many) columns in a table, I'm
>> used to just click the "Column" header in the Object browser window
>> and then drag it over to the "code pane". In Query Analyzer I was
>> use to get all the columns in the same order as they where showed in
>> the object browser to the left. When using MicroSoft SQL Server
>> Mamagement Studio, I get the columns in alphabetically order when I
>> drag them over, eventhough they are shown in another order in the
>> Object Browser window. Does anybody know if this is a setting that can
>> be changed somewhere?
>> Regards
>> Steen|||Strange. I don't have a 2000 to test against. Perhaps there is a difference. I would report this to
the beta forums if I were you...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.microsoft.com...
> Hi Tibor
> I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the same table into a Qury
> Analyser I get the columns in the same order as they are shown.
> I've just tried to get the "CREATE TABLE" from the table, and here the columns are listed in the
> "correct" order which is the order they have been typed in and not alphabetically.
> Regards
> Steen
> Tibor Karaszi wrote:
>> Are you on May CTP? When I drag the columns folder to the query
>> window, I get the columns listed in the same order as in my CREATE
>> TABLE statement...
>> "Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>> news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>> Hi
>> When I want to use all (or at least many) columns in a table, I'm
>> used to just click the "Column" header in the Object browser window
>> and then drag it over to the "code pane". In Query Analyzer I was
>> use to get all the columns in the same order as they where showed in
>> the object browser to the left. When using MicroSoft SQL Server
>> Mamagement Studio, I get the columns in alphabetically order when I
>> drag them over, eventhough they are shown in another order in the
>> Object Browser window. Does anybody know if this is a setting that can be changed somewhere?
>> Regards
>> Steen
>|||This is a multi-part message in MIME format.
--040307040208010607010408
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
I've got the June CTP and I see the same behaviour with SSMS & a
SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Steen Persson (DK) wrote:
>Hi Tibor
>I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
>same table into a Qury Analyser I get the columns in the same order as they
>are shown.
>I've just tried to get the "CREATE TABLE" from the table, and here the
>columns are listed in the "correct" order which is the order they have been
>typed in and not alphabetically.
>Regards
>Steen
>Tibor Karaszi wrote:
>
>>Are you on May CTP? When I drag the columns folder to the query
>>window, I get the columns listed in the same order as in my CREATE
>>TABLE statement...
>>"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
>>news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com...
>>
>>Hi
>>When I want to use all (or at least many) columns in a table, I'm
>>used to just click the "Column" header in the Object browser window
>>and then drag it over to the "code pane". In Query Analyzer I was
>>use to get all the columns in the same order as they where showed in
>>the object browser to the left. When using MicroSoft SQL Server
>>Mamagement Studio, I get the columns in alphabetically order when I
>>drag them over, eventhough they are shown in another order in the
>>Object Browser window. Does anybody know if this is a setting that can
>>be changed somewhere?
>>Regards
>>Steen
>>
>
>
--040307040208010607010408
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>I've got the June CTP and I see the same behaviour with SSMS &
a SQL2000(SP4) database (i.e. alphabetical order). However,
interestingly, when I use SSMS to do the same with a Yukon DB, the
columns are listed in their ordinal positions.<br>
</tt>
<div class="moz-signature">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Steen Persson (DK) wrote:
<blockquote
cite="miduMLgZp5hFHA.2680@.TK2MSFTNGSA02.privatenews.microsoft.com"
type="cite">
<pre wrap="">Hi Tibor
I'm on the June CTP, but I'm querying a SQL 2000 database. When I drag the
same table into a Qury Analyser I get the columns in the same order as they
are shown.
I've just tried to get the "CREATE TABLE" from the table, and here the
columns are listed in the "correct" order which is the order they have been
typed in and not alphabetically.
Regards
Steen
Tibor Karaszi wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Are you on May CTP? When I drag the columns folder to the query
window, I get the columns listed in the same order as in my CREATE
TABLE statement...
"Steen Persson (DK)" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:spe@.REMOVEdatea.dk"><spe@.REMOVEdatea.dk></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com">news:umleXt4hFHA.1404@.TK2MSFTNGSA02.privatenews.microsoft.com</a>...
</pre>
<blockquote type="cite">
<pre wrap="">Hi
When I want to use all (or at least many) columns in a table, I'm
used to just click the "Column" header in the Object browser window
and then drag it over to the "code pane". In Query Analyzer I was
use to get all the columns in the same order as they where showed in
the object browser to the left. When using MicroSoft SQL Server
Mamagement Studio, I get the columns in alphabetically order when I
drag them over, eventhough they are shown in another order in the
Object Browser window. Does anybody know if this is a setting that can
be changed somewhere?
Regards
Steen
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--040307040208010607010408--|||Steen Persson (DK) (spe@.REMOVEdatea.dk) writes:
> When I want to use all (or at least many) columns in a table, I'm used
> to just click the "Column" header in the Object browser window and then
> drag it over to the "code pane". In Query Analyzer I was use to get all
> the columns in the same order as they where showed in the object browser
> to the left. When using MicroSoft SQL Server Mamagement Studio, I get
> the columns in alphabetically order when I drag them over, eventhough
> they are shown in another order in the Object Browser window.
> Does anybody know if this is a setting that can be changed somewhere?
Like Tibor, I was not able to repeat this. And I also tried against
SQL 2000.
I can't recall having seen any setting for this.
If you do this on Northwind..Orders, what do you see? I see:
OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,
ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
ShipPostalCode, ShipCountry
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||OK, so I was able to repeat this on SQL 2000. And investigating the issue
further by using Profiler, I found that the bug applies to both SQL 2000 and
SQL 2005. To wit the cause is there the underlying SELECT statement does not
have any ORDER BY clause. The queries are different, but both are missing
ORDER BY.
I've filed bug FDBK32428 about this. In the bug report I left open for
both alphabetic order and column-number order, but indicated that the
latter is probably what users expects.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Column Order in Table

Hi,
I know that physical order of a column is not important in tables but I like
to know if it is possible to force column order when I alter a table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think there
should be a system sp/func to alter that.
Thanks,
Leila> I know that physical order of a column is not important in tables but I
like
> to know if it is possible to force column order when I alter a table to
add
> column or change the order later.
Only by dropping the table and re-creating it.
> It seems that the orders are stored in syscolumns table but I think there
> should be a system sp/func to alter that.
No, do not attempt this. Why do you care where the column is?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||In addition to what Aaron said...
From BOL
System tables should not be altered directly by any user.
Don't do this, ever.
I tried this as an experiment in a test database once and screwed up royally
the database.
Good thing it was a test database as I had no other choice then to delete
it.
There is no system sp/func for this and there probably never will be as the
order of the columns is not important. You can reorder columns in Enterprise
Manager but behind the scenes it drops and recreates the table.
"Leila" <Leilas@.hotpop.com> wrote in message
news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I know that physical order of a column is not important in tables but I
like
> to know if it is possible to force column order when I alter a table to
add
> column or change the order later.
> It seems that the orders are stored in syscolumns table but I think there
> should be a system sp/func to alter that.
> Thanks,
> Leila
>|||> No, do not attempt this. Why do you care where the column is?
Just interested!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uKMreex0EHA.3408@.tk2msftngp13.phx.gbl...
> > I know that physical order of a column is not important in tables but I
> like
> > to know if it is possible to force column order when I alter a table to
> add
> > column or change the order later.
> Only by dropping the table and re-creating it.
> > It seems that the orders are stored in syscolumns table but I think
there
> > should be a system sp/func to alter that.
> No, do not attempt this. Why do you care where the column is?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>|||FWIW, I was interested too, just on general principle. I know it doesn't
"matter" all that much, but I want to know how to do whatever I want with
the data.
I knew it was possible to do it in the Enterprise Manager, but I didn't know
it was dropping and recreating the table. Thanks.
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
> In addition to what Aaron said...
> From BOL
> System tables should not be altered directly by any user.
> Don't do this, ever.
> I tried this as an experiment in a test database once and screwed up
> royally
> the database.
> Good thing it was a test database as I had no other choice then to delete
> it.
> There is no system sp/func for this and there probably never will be as
> the
> order of the columns is not important. You can reorder columns in
> Enterprise
> Manager but behind the scenes it drops and recreates the table.
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
>> Hi,
>> I know that physical order of a column is not important in tables but I
> like
>> to know if it is possible to force column order when I alter a table to
> add
>> column or change the order later.
>> It seems that the orders are stored in syscolumns table but I think there
>> should be a system sp/func to alter that.
>> Thanks,
>> Leila
>>
>|||If you want to see the code:
In Enterprise manager open a table in design mode
Change the order of a column (don't save the change)
Click the "Save Change Script" icon
In one of my base tables, this produced 287 lines of code.
"Paul Pedersen" <no-reply@.swen.com> wrote in message
news:u0A2Zx$0EHA.2884@.TK2MSFTNGP11.phx.gbl...
> FWIW, I was interested too, just on general principle. I know it doesn't
> "matter" all that much, but I want to know how to do whatever I want with
> the data.
> I knew it was possible to do it in the Enterprise Manager, but I didn't
know
> it was dropping and recreating the table. Thanks.
>
> "raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
> news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
> > In addition to what Aaron said...
> >
> > From BOL
> > System tables should not be altered directly by any user.
> >
> > Don't do this, ever.
> > I tried this as an experiment in a test database once and screwed up
> > royally
> > the database.
> > Good thing it was a test database as I had no other choice then to
delete
> > it.
> >
> > There is no system sp/func for this and there probably never will be as
> > the
> > order of the columns is not important. You can reorder columns in
> > Enterprise
> > Manager but behind the scenes it drops and recreates the table.
> >
> > "Leila" <Leilas@.hotpop.com> wrote in message
> > news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
> >> Hi,
> >> I know that physical order of a column is not important in tables but I
> > like
> >> to know if it is possible to force column order when I alter a table to
> > add
> >> column or change the order later.
> >> It seems that the orders are stored in syscolumns table but I think
there
> >> should be a system sp/func to alter that.
> >> Thanks,
> >> Leila
> >>
> >>
> >
> >
>|||Even more useful info! Thanks again.
You might have noticed, I'm new to SQL Server (from FoxPro).
"raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
news:uQotB3$0EHA.1188@.tk2msftngp13.phx.gbl...
> If you want to see the code:
> In Enterprise manager open a table in design mode
> Change the order of a column (don't save the change)
> Click the "Save Change Script" icon
> In one of my base tables, this produced 287 lines of code.
> "Paul Pedersen" <no-reply@.swen.com> wrote in message
> news:u0A2Zx$0EHA.2884@.TK2MSFTNGP11.phx.gbl...
>> FWIW, I was interested too, just on general principle. I know it doesn't
>> "matter" all that much, but I want to know how to do whatever I want with
>> the data.
>> I knew it was possible to do it in the Enterprise Manager, but I didn't
> know
>> it was dropping and recreating the table. Thanks.
>>
>> "raydan" <rdanjou@.savantsoftNOSPAM.com> wrote in message
>> news:OFpVmvx0EHA.3500@.TK2MSFTNGP09.phx.gbl...
>> > In addition to what Aaron said...
>> >
>> > From BOL
>> > System tables should not be altered directly by any user.
>> >
>> > Don't do this, ever.
>> > I tried this as an experiment in a test database once and screwed up
>> > royally
>> > the database.
>> > Good thing it was a test database as I had no other choice then to
> delete
>> > it.
>> >
>> > There is no system sp/func for this and there probably never will be as
>> > the
>> > order of the columns is not important. You can reorder columns in
>> > Enterprise
>> > Manager but behind the scenes it drops and recreates the table.
>> >
>> > "Leila" <Leilas@.hotpop.com> wrote in message
>> > news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
>> >> Hi,
>> >> I know that physical order of a column is not important in tables but
>> >> I
>> > like
>> >> to know if it is possible to force column order when I alter a table
>> >> to
>> > add
>> >> column or change the order later.
>> >> It seems that the orders are stored in syscolumns table but I think
> there
>> >> should be a system sp/func to alter that.
>> >> Thanks,
>> >> Leila
>> >>
>> >>
>> >
>> >
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C4D4C1.127F3640
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Anthony,
It's arguable that DBMSs would be better if they stayed closer to the =relational model, but since column ordinal positions are part of the =ANSI SQL standard, I think it's appropriate to provide that attribute of =a column in metadata. If it's a downfall of anything, it's a downfall =of ANSI SQL, not each DBMS. SQL Server doesn't expose physical column =order to the user, and while it doesn't, it could even vary from row to =row without the user knowing (it doesn't, but it could, so long as =select * queries returned columns in order of their (virtual, and stored =in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical =characteristic of the database. They are part of the metadata, just =like column types and names, and they aren't a reflection of the =physical layout of the data. Microsoft does document the way in which =column data is stored within a row, since it can be beneficial to know =for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are =stored before variable-length columns, for example (regardless of =ordinal position) and without looking it up, I'm not sure whether the =ordinal position attribute of a column (which is exposed in the ANSI =INFORMATION_SCHEMA metadata views) is even respected within those two =categories. Long data (text, ntext, image), can even be stored out of =the row's data page, and tables with a non-clustered index store some =column data in more than one place. The physical layout of data in a =SQL Server table is not exposed to the user.
That said, I agree that columns should almost always be named, and the =few T-SQL features that rely on the column's ordinal position should be =avoided if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:OZecUbL1EHA.1652@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, =under the hood. That's laudable; however, because SS is a physical =system, it is limited to physical media and, thus, must store =information about column order because, as a physical system, it must =manipulate the information at the physical level.
The downfall of most DBMS products is that they often expose certain =physical characteristics that should have been shielded from =end-users...even Database Administrators, Engineers, and Developers. =This is just another case where this is not so.
Any DML should manipulate column-level information on a NAME basis =only, and, thus, ordinal position is irrelevant. Therefore, any attempt =to alter this is meaningless. Now, you can affect the outcome but, as =the other respondents have said, you must drop and recreate or create a =temp table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
-- "Leila" <Leilas@.hotpop.com> wrote in message =news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables =but I like
to know if it is possible to force column order when I alter a table =to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I think =there
should be a system sp/func to alter that.
Thanks,
Leila
--=_NextPart_000_001D_01C4D4C1.127F3640
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Anthony,
It's arguable that DBMSs would be better if =they stayed closer to the relational model, but since column ordinal positions are =part of the ANSI SQL standard, I think it's appropriate to provide that =attribute of a column in metadata. If it's a downfall of anything, it's a =downfall of ANSI SQL, not each DBMS. SQL Server doesn't expose physical =column order to the user, and while it doesn't, it could even vary from row to =row without the user knowing (it doesn't, but it could, so long as select * =queries returned columns in order of their (virtual, and stored in metadata, not =physical) ordinal position.
SQL Server's column ordinal positions are not an =exposed physical characteristic of the database. They are part of the =metadata, just like column types and names, and they aren't a reflection of =the physical layout of the data. Microsoft does document the way in =which column data is stored within a row, since it can be beneficial to =know for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save perhaps for =some undocumented DBCC commands. Fixed-length columns are stored before =variable-length columns, for example (regardless of ordinal position) =and without looking it up, I'm not sure whether the ordinal =position attribute of a column (which is exposed in the ANSI INFORMATION_SCHEMA metadata =views) is even respected within those two categories. Long data (text, =ntext, image), can even be stored out of the row's data page, and tables with a =non-clustered index store some column data in more than one place. =The physical layout of data in a SQL Server table is not exposed to the user.
That said, I agree that columns should almost =always be named, and the few T-SQL features that rely on the column's ordinal =position should be avoided if at all possible.
Steve Kass
Drew University
"AnthonyThomas" wrote in message news:OZecUbL1EHA.1652=@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, under the =hood. That's laudable; however, because SS is a physical system, it is =limited to physical media and, thus, must store information about column order =because, as a physical system, it must manipulate the information at the =physical level.

The =downfall of most DBMS products is that they often expose certain physical =characteristics that should have been shielded from end-users...even Database =Administrators, Engineers, and Developers. This is just another case where this =is not so.

Any DML =should manipulate column-level information on a NAME basis only, and, thus, =ordinal position is irrelevant. Therefore, any attempt to alter this is meaningless. Now, you can affect the outcome but, as the other respondents have said, you must drop and recreate or create a temp =table, migrate the data, drop the original, and, then, rename the temp. =This is how the Visual Database designer does it.

Sincerely,

Anthony = Thomas

--
"Leila" wrote in =message news:eZRp6Ox0EHA.3616=@.TK2MSFTNGP11.phx.gbl...Hi,I know that physical order of a column is not important in tables but =I liketo know if it is possible to force column order when I alter =a table to addcolumn or change the order later.It seems that the =orders are stored in syscolumns table but I think thereshould be a system =sp/func to alter that.Thanks,Leila

=
--=_NextPart_000_001D_01C4D4C1.127F3640--|||This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C4D6C0.C2567F50
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I do like to have some semblance of order in column order, if only for =display and design purposes. For instance, it's a lot easier for me to =deal with a table if the columns name, address, city, state, and zip =appear next to each other and in that order.
"Steve Kass" <skass@.drew.edu> wrote in message =news:eSW%230rO1EHA.2192@.TK2MSFTNGP14.phx.gbl...
Anthony,
It's arguable that DBMSs would be better if they stayed closer to =the relational model, but since column ordinal positions are part of the =ANSI SQL standard, I think it's appropriate to provide that attribute of =a column in metadata. If it's a downfall of anything, it's a downfall =of ANSI SQL, not each DBMS. SQL Server doesn't expose physical column =order to the user, and while it doesn't, it could even vary from row to =row without the user knowing (it doesn't, but it could, so long as =select * queries returned columns in order of their (virtual, and stored =in metadata, not physical) ordinal position.
SQL Server's column ordinal positions are not an exposed physical =characteristic of the database. They are part of the metadata, just =like column types and names, and they aren't a reflection of the =physical layout of the data. Microsoft does document the way in which =column data is stored within a row, since it can be beneficial to know =for troubleshooting, design, optimization, and so on. But no T-SQL =language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are =stored before variable-length columns, for example (regardless of =ordinal position) and without looking it up, I'm not sure whether the =ordinal position attribute of a column (which is exposed in the ANSI =INFORMATION_SCHEMA metadata views) is even respected within those two =categories. Long data (text, ntext, image), can even be stored out of =the row's data page, and tables with a non-clustered index store some =column data in more than one place. The physical layout of data in a =SQL Server table is not exposed to the user.
That said, I agree that columns should almost always be named, and =the few T-SQL features that rely on the column's ordinal position should =be avoided if at all possible.
Steve Kass
Drew University
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message =news:OZecUbL1EHA.1652@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of =SS, under the hood. That's laudable; however, because SS is a physical =system, it is limited to physical media and, thus, must store =information about column order because, as a physical system, it must =manipulate the information at the physical level.
The downfall of most DBMS products is that they often expose certain =physical characteristics that should have been shielded from =end-users...even Database Administrators, Engineers, and Developers. =This is just another case where this is not so.
Any DML should manipulate column-level information on a NAME basis =only, and, thus, ordinal position is irrelevant. Therefore, any attempt =to alter this is meaningless. Now, you can affect the outcome but, as =the other respondents have said, you must drop and recreate or create a =temp table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.
Sincerely,
Anthony Thomas
-- "Leila" <Leilas@.hotpop.com> wrote in message =news:eZRp6Ox0EHA.3616@.TK2MSFTNGP11.phx.gbl...
Hi,
I know that physical order of a column is not important in tables =but I like
to know if it is possible to force column order when I alter a =table to add
column or change the order later.
It seems that the orders are stored in syscolumns table but I =think there
should be a system sp/func to alter that.
Thanks,
Leila
--=_NextPart_000_0008_01C4D6C0.C2567F50
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

I do like to have some =semblance of order in column order, if only for display and design purposes. For instance, =it's a lot easier for me to deal with a table if the columns name, address, =city, state, and zip appear next to each other and in that order.
"Steve Kass" wrote in message news:eSW%230rO1EHA.=2192@.TK2MSFTNGP14.phx.gbl...
Anthony,

It's arguable that DBMSs would be better if =they stayed closer to the relational model, but since column ordinal =positions are part of the ANSI SQL standard, I think it's appropriate to =provide that attribute of a column in metadata. If it's a downfall of =anything, it's a downfall of ANSI SQL, not each DBMS. SQL Server doesn't expose = physical column order to the user, and while it doesn't, it could even =vary from row to row without the user knowing (it doesn't, but it could, so =long as select * queries returned columns in order of their (virtual, and =stored in metadata, not physical) ordinal position.

SQL Server's column ordinal positions are not an =exposed physical characteristic of the database. They are part of the =metadata, just like column types and names, and they aren't a reflection of =the physical layout of the data. Microsoft does document the way in =which column data is stored within a row, since it can be beneficial to =know for troubleshooting, design, optimization, and so on. But no =T-SQL language constructs exist to access the information that way, save =perhaps for some undocumented DBCC commands. Fixed-length columns are stored =before variable-length columns, for example (regardless of ordinal position) =and without looking it up, I'm not sure whether the ordinal position attribute of a column (which is exposed in the ANSI INFORMATION_SCHEMA metadata views) is even respected within those two categories. Long data (text, ntext, image), can even be stored =out of the row's data page, and tables with a non-clustered index store some =column data in more than one place. The physical layout of data in a =SQL Server table is not exposed to the user.

That said, I agree that columns should =almost always be named, and the few T-SQL features that rely on the column's ordinal = position should be avoided if at all possible.

Steve Kass
Drew University

"AnthonyThomas" wrote in message news:OZecUbL1EHA.1652=@.TK2MSFTNGP11.phx.gbl...
Understand that you just want to know more about the clockworks of SS, under the =hood. That's laudable; however, because SS is a physical system, it is =limited to physical media and, thus, must store information about column order =because, as a physical system, it must manipulate the information at the =physical level.

The =downfall of most DBMS products is that they often expose certain physical characteristics that should have been shielded from end-users...even = Database Administrators, Engineers, and Developers. This is =just another case where this is not so.

Any =DML should manipulate column-level information on a NAME basis only, and, thus, =ordinal position is irrelevant. Therefore, any attempt to alter this =is meaningless. Now, you can affect the outcome but, as the other = respondents have said, you must drop and recreate or create a temp =table, migrate the data, drop the original, and, then, rename the =temp. This is how the Visual Database designer does it.

Sincerely,

Anthony Thomas

--
"Leila" wrote =in message news:eZRp6Ox0EHA.3616=@.TK2MSFTNGP11.phx.gbl...Hi,I know that physical order of a column is not important in tables =but I liketo know if it is possible to force column order when I =alter a table to addcolumn or change the order later.It seems that =the orders are stored in syscolumns table but I think thereshould =be a system sp/func to alter =that.Thanks,Leila

--=_NextPart_000_0008_01C4D6C0.C2567F50--|||>>
I do like to have some semblance of order in column order, if only for
display and design purposes. For instance, it's a lot easier for me to deal
with a table if the columns name, address, city, state, and zip appear next
to each other and in that order.
Okay, so if you build the table and somehow forget to include the address
column, then drop the table and re-create it. They're not going to change
the ALTER TABLE command for cosmetics.
--
http://www.aspfaq.com/
(Reverse address to reply.)

Tuesday, March 27, 2012

Combining strings

Thursday, March 22, 2012

CombinedID

Monday, March 19, 2012

combine data from different records with same ID

Sunday, March 11, 2012

com.microsoft.sqlserver.jdbc.SQLServerConnection loginWithFailover

COM security Policy

COLUMNS_UPDATED()

columns order in entities

columns order in entities

Thursday, March 8, 2012

Columns Order

Saturday, February 25, 2012

Column Ordering in SELECT statement

Column Ordering in SELECT statement

Column Ordering in SELECT statement

Column order/presentation in virtual table (result set from viewor UDF)

Column order issue

Column order issue

Column order issue

Column Order in Table

Collation problem

Blog Archive

About Me