Access Database Datatypes, ADOX and VS2005 Question

I'm using ADOX 2.8 for table creation: The following is an example of a column defintion:

If CreateNewTable Then CreateNewTable = a.CreateColumn("ReferenceCount", ADOX.DataTypeEnum.adInteger)

If CreateNewTable Then CreateNewTable = a.CreateColumn("Document", ADOX.DataTypeEnum.adLongVarBinary) 'Oleobject

If CreateNewTable Then CreateNewTable = a.CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18) 'text

Where CreateColumn looks like this:

Public Function CreateColumn(ByVal ColumnName As String, ByVal Datatype As ADOX.DataTypeEnum, Optional ByVal Size As Integer = 0) As Boolean

'ADOX.CreateColumn- Called by Common.CreateNewTable

'CreateColumn creates a column described in the Table object so it assumes it is set.

'One method of setting it is to call Select Table after opening the database

If Not Me.ConnectionIsOpen Then

MsgBox("CreateColumn - Failed to Create Column : " _

& ColumnName, MsgBoxStyle.Exclamation, cNoConn)

Return False

End If

Dim col As New ADOX.Column

col.Name = ColumnName

Try

col.Type = Datatype

Catch e As Exception

MsgBox("CreateColumb - Failed to Create Column : " _

& ColumnName, MsgBoxStyle.Exclamation, e.Message)

col = Nothing

Return False

End Try

If Size <> 0 Then col.DefinedSize = Size

Try

Table.Columns.Append(ColumnName, Datatype)

Catch e As Exception

If Err.Number() <> 0 Then

MsgBox(Err.Source & "-->" & Err.Description, , "Error")

End If

MsgBox("CreateColumb - Failed to Append Column : " _

& ColumnName, MsgBoxStyle.Exclamation, e.Message)

Return False

End Try

col = Nothing

Return True

End Function

in CreateColumn("EntityID", ADOX.DataTypeEnum.adWChar, 18)

the 18 specifies the field width in the database. Yet no matter whether I use adWChar or

adVarWChar, Access always shows the field size to be 255.

Does anyone know why or how to fix that?

I can see that you created col variable, and assigned values to the fields of it. But, unless I'm missing something, I do not see how it is used in Table.Columns.Append call.

I guess that you should either use col instead of ColumnName, or add Size as a third parameter, and do not use col at all.

access column

hi,

how can i access column name from SQL using query??

Thanks in advance

If the logged on user has the proper rights then you can use the following query:

-- for selecting the column names of the orders -- table in Northwind databaseselect [name]fromsyscolumnswhere id =object_id('orders')

|||

Thanks fren,it works

|||

Not at all, we are here to help eac other

Access accessing SQL {}

Im using Access to access a SQL database. For one of the queries i see the following under column:

{ fn month(dbo.TableName.Date) }

Ive never used this structure before but im confused to what it is? Can anyone explain please?

Thanks

The curly braces and 'fn' server to denote a OBDC level function.

The T-SQL equilivent would be:

month( YourDateColumn )

|||

Hello

Thanks for that. Could you explain a bit more on that please? As far as im concerned i didnt think there would be an ODBC link as it was connecting straight to a SQL database (checking ODBC theres no link to a SQL database either). Maybe an example if you can on what this user could have been trying to achieve would be great?

Thanks

Access a spaced column name

I have a table column like "First Name". How do I select that from the table.
It gives me an error if I write query:
select "First Name" from table1.
Any clues?
Thanks
VinnieI use brackets, likeSELECT [First Name]
FROM table1-PatP|||Thanks for the info

about WITH XMLNAMESPACES

Hello experts!
I'm using a table including one column declared as xml-datatype.
This table holds instances of xml-documents, that use one of two schemas.
Let's call them schema1 and schema2, where schema1 is Version 1 of the
schema, and schema2 the extended Version 2.
Version 2 holds everything of Version 1 in the same structure, but allowes
some more fields.
The table has the following fields:
keyfield
schemaversion
xmldata
I now want to create a view, where the customer could easily access the data
inside the xml.
My idea was a view like
WITH XMLNAMESPACES(DEFAULT 'xmlns1')
SELECT ...
FROM ...
WHERE schemaversion = 1
UNION
WITH XMLNAMESPACES(DEFAULT 'xmlns2')
SELECT ...
FROM ...
WHERE schemaversion = 2
But the second WITH-statement seems to be unsupported.
Has anyone of you an idea, how i could access based on both schemas?
Thanks very much!
Maxok, i found some kind of workaround.
i created two views on the table, on using the namespace of schema1 and the
other of schema2.
every view includes a where clause on the data to only process the rows it
has to.
finally i created a third view which creates a union of the other two views
and which is the "interface" to the customer.
hmm, at the moment, as i do not have very much data to test on, the speed
seems to be satisfying, but i wonder how this will change if i get more and
more data.
so, if anyone of you has another idea, please share it!
thanks!
max
"Markus Emayr" <essmayr/at/racon-linz.at> schrieb im Newsbeitrag
news:uqwa2qrHHHA.1248@.TK2MSFTNGP03.phx.gbl...
> Hello experts!
> I'm using a table including one column declared as xml-datatype.
> This table holds instances of xml-documents, that use one of two schemas.
> Let's call them schema1 and schema2, where schema1 is Version 1 of the
> schema, and schema2 the extended Version 2.
> Version 2 holds everything of Version 1 in the same structure, but allowes
> some more fields.
> The table has the following fields:
> keyfield
> schemaversion
> xmldata
> I now want to create a view, where the customer could easily access the
> data inside the xml.
> My idea was a view like
> WITH XMLNAMESPACES(DEFAULT 'xmlns1')
> SELECT ...
> FROM ...
> WHERE schemaversion = 1
> UNION
> WITH XMLNAMESPACES(DEFAULT 'xmlns2')
> SELECT ...
> FROM ...
> WHERE schemaversion = 2
> But the second WITH-statement seems to be unsupported.
> Has anyone of you an idea, how i could access based on both schemas?
> Thanks very much!
> Max
>|||Why not use a prefix for each and declare it in a single with statement:
WITH XMLNAMESPACES('xmlns1' as "s1", 'xmlns2' as "s2")
SELECT ...
FROM ...
WHERE schemaversion = 1
UNION
SELECT ...
FROM ...
WHERE schemaversion = 2
Best regards
Michael
"Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
news:uqwa2qrHHHA.1248@.TK2MSFTNGP03.phx.gbl...
> Hello experts!
> I'm using a table including one column declared as xml-datatype.
> This table holds instances of xml-documents, that use one of two schemas.
> Let's call them schema1 and schema2, where schema1 is Version 1 of the
> schema, and schema2 the extended Version 2.
> Version 2 holds everything of Version 1 in the same structure, but allowes
> some more fields.
> The table has the following fields:
> keyfield
> schemaversion
> xmldata
> I now want to create a view, where the customer could easily access the
> data inside the xml.
> My idea was a view like
> WITH XMLNAMESPACES(DEFAULT 'xmlns1')
> SELECT ...
> FROM ...
> WHERE schemaversion = 1
> UNION
> WITH XMLNAMESPACES(DEFAULT 'xmlns2')
> SELECT ...
> FROM ...
> WHERE schemaversion = 2
> But the second WITH-statement seems to be unsupported.
> Has anyone of you an idea, how i could access based on both schemas?
> Thanks very much!
> Max
>

about tsql

in tsql,how can select the data rely on the column order
,not the column name
such as
select column1,column2
from...You may need to pur like this ... select col1,col2 from
table name order by 1,2
Hope, I understood the question correctly.
Suresh
>--Original Message--
>in tsql,how can select the data rely on the column order
>,not the column name
>such as
>select column1,column2
>from...
>.
>|||Why would you want to do this' If you could state your problem, maybe we
can give you an alternate solution.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
> in tsql,how can select the data rely on the column order
> ,not the column name
> such as
> select column1,column2
> from...
>|||i want to get several tables' first column's data
and the table name store in one table, and of course the
table's first column's name is different
in short
i want use a loop update several table's first column
>--Original Message--
>Why would you want to do this' If you could state your
problem, maybe we
>can give you an alternate solution.
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>http://www32.brinkster.com/srisamp
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
>> in tsql,how can select the data rely on the column order
>> ,not the column name
>> such as
>> select column1,column2
>> from...
>
>.
>|||In the SQL language, you refer to columns by name, not position. There are two exceptions: "SELECT
*" and INSERT without a column name list.
You can use the INFORMATION_SCHEMA views to read each table name and the name of the first column
name. And based on that, you can construct your SQL statement and run that through dynamic SQL (EXEC
@.sql). The info schema views are documented in Books Online and I've done a diagram of them at
http://www.dbmaint.com/info_schema.asp.
You can find a lot of info on dynamic SQL at:
http://www.algonet.se/~sommar/
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:083801c3b8bb$29ff2cd0$a501280a@.phx.gbl...
> i want to get several tables' first column's data
> and the table name store in one table, and of course the
> table's first column's name is different
> in short
> i want use a loop update several table's first column
>
>
> >--Original Message--
> >Why would you want to do this' If you could state your
> problem, maybe we
> >can give you an alternate solution.
> >--
> >HTH,
> >SriSamp
> >Please reply to the whole group only!
> >http://www32.brinkster.com/srisamp
> >
> >"frank" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
> >> in tsql,how can select the data rely on the column order
> >> ,not the column name
> >>
> >> such as
> >> select column1,column2
> >> from...
> >>
> >
> >
> >.
> >|||>--Original Message--
>In the SQL language, you refer to columns by name, not
position. There are two exceptions: "SELECT
>*" and INSERT without a column name list.
thanks a lot
>You can use the INFORMATION_SCHEMA views to read each
table name and the name of the first column
>name. And based on that, you can construct your SQL
statement and run that through dynamic SQL (EXEC
>@.sql). The info schema views are documented in Books
Online and I've done a diagram of them at
>http://www.dbmaint.com/info_schema.asp.
>You can find a lot of info on dynamic SQL at:
>http://www.algonet.se/~sommar/
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:083801c3b8bb$29ff2cd0$a501280a@.phx.gbl...
>> i want to get several tables' first column's data
>> and the table name store in one table, and of course the
>> table's first column's name is different
>> in short
>> i want use a loop update several table's first column
>>
>>
>> >--Original Message--
>> >Why would you want to do this' If you could state your
>> problem, maybe we
>> >can give you an alternate solution.
>> >--
>> >HTH,
>> >SriSamp
>> >Please reply to the whole group only!
>> >http://www32.brinkster.com/srisamp
>> >
>> >"frank" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:010a01c3b89d$430c9460$a001280a@.phx.gbl...
>> >> in tsql,how can select the data rely on the column
order
>> >> ,not the column name
>> >>
>> >> such as
>> >> select column1,column2
>> >> from...
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

About The Connect to Server Dialog

In server name column, it apears all the server name that i uesed before, but some of them are not available now, how do I remove them?

There isn't a way to be very surgical about this, but you can use the "big hammer" approach and delete the mru.dat file in C:\Documents and Settings\{you}\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell. This obliterates the most-recently-used list of servers. The connection dialog will create a new mru.dat file the next time you connect to a server.

About ordering of columns

i add new column using alter command but i always found it in the end of table but
i want to add it in particular position between the columns.........
how to do so ..........:)The only way to do that is dropping the table and recreating it with the columns in the position you want.

But why do you want to do this? Column order has no significant meaning in the database and can be arbitrarily changed in the the statements you use.|||I think if you use EM or SSMS to add a column you can add a column to the middle as well, but this likely does just what lexiflex suggests under the covers.

I agree with lexiflex: why do you care? The only reason I can think of is you are using "select *" in your app code, which is a no-no.|||You CAN move columns around in EM when in table design view. Just drag
the column to the position you want, and save the design.

ummmm... I would NEVER do this, I just saw somebody else do it (heh heh)|||You CAN move columns around in EM when in table design view. Just drag the column to the position you want, and save the design.but under the covers, EM creates a new table, copies the data from your original table, and then drops it|||EM creates a new table, copies the data from your original table, and then drops it
Which brings me to another reason for not using EM and making scripts yourself...

Try adding a NOT NULL-column without a default somewhere in between the other columns. Running the EM-script will cause an error about NULL-values in NOT NULL-columns just before it drops your original table and renames the temp-table. Thus making all the data from that table magically disappear... :S|||You CAN move columns around in EM when in table design view. Just drag
the column to the position you want, and save the design.

ummmm... I would NEVER do this, I just saw somebody else do it (heh heh)
I freely admit to doing this all the time.
Why? Because I like my data columns in logical groups, thats why.
But why do you want to do this? Column order has no significant meaning in the database and can be arbitrarily changed in the the statements you use.Absolutely correct. The database does not care about column order. But PEOPLE do...

ABout identity column in replication

Hi Guys,
I read the microsoft article about how to handle the identity column in
replication. There is sentence: " If you are using transactional replication
with the immediate-updating Subscribers option, do not use the IDENTITY NOT
FOR REPLICATION design." That means I can not use the IDENTITY NOT FOR
REPLICATION option when I want to use transactional replication with the
immediate-updating Subscribers options, is that correct? That also means I
cannot created identity column in subscriber, right? If I do need to create
identity column in the subscriber, how can I work around? Thanks
This isn't correct. If you have immediate updating you don't need NFR as the
transaction is applied on the publisher before the subscriber.
You can use NFR and identity columns on the subscriber, however, but it is
not necessary.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:43F4ECD0-EDC3-4932-AF29-C2782E66CEDD@.microsoft.com...
> Hi Guys,
> I read the microsoft article about how to handle the identity column in
> replication. There is sentence: " If you are using transactional
> replication
> with the immediate-updating Subscribers option, do not use the IDENTITY
> NOT
> FOR REPLICATION design." That means I can not use the IDENTITY NOT FOR
> REPLICATION option when I want to use transactional replication with the
> immediate-updating Subscribers options, is that correct? That also means I
> cannot created identity column in subscriber, right? If I do need to
> create
> identity column in the subscriber, how can I work around? Thanks

About Identity Column

Dear all,
I use the identity property that makes generating unique numeric
values in my table "OrderDetail". Its primary keys include the column
"OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
that if the application keep going to use for many years. Is it possible the
identity value will be overflowed? or it will restart from 1 again
automatically after the overflow. And now, i make this column type is "Int".
Anyone can help? Thanks.
Best Rdgs
EllisYou'll get an overflow when the identity value exceeds the maximum value for
the data type If this is a possibility in your situation, consider using a
data type with a larger maximum value, such as bigint or decimal.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> Dear all,
> I use the identity property that makes generating unique numeric
> values in my table "OrderDetail". Its primary keys include the column
> "OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
> that if the application keep going to use for many years. Is it possible
> the
> identity value will be overflowed? or it will restart from 1 again
> automatically after the overflow. And now, i make this column type is
> "Int".
> Anyone can help? Thanks.
> Best Rdgs
> Ellis
>|||Many Thanks !! Otherwise I'll be in big trouble when the system get
overflow. But now, I can I solve this problem now? and I'm using int data
type, what's its maximum value of it?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> You'll get an overflow when the identity value exceeds the maximum value
for
> the data type If this is a possibility in your situation, consider using
a
> data type with a larger maximum value, such as bigint or decimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
wonder[vbcol=seagreen]
>|||Hello Ellis,
from books online:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
(2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
integer.
Regards,
Tomislav Kralj
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
> Many Thanks !! Otherwise I'll be in big trouble when the system get
> overflow. But now, I can I solve this problem now? and I'm using int data
> type, what's its maximum value of it?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> for
> a
> wonder
>|||Which means that if your IDENTITY is set with the default (1,1), you can
store one row per second for the next 68 years. Just to put things in
perspective
Jacco Schalkwijk
SQL Server MVP
"Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
> Hello Ellis,
> from books online:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
> integer.
>
> Regards,
> Tomislav Kralj
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>|||Or with bigint, you can store one mullion rows per second for 1142 years
:-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:e2g%23QR5LFHA.568@.TK2MSFTNGP09.phx.gbl...
> Which means that if your IDENTITY is set with the default (1,1), you can
> store one row per second for the next 68 years. Just to put things in
> perspective
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
> news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
>

About Identity Column

Dear all,
I use the identity property that makes generating unique numeric
values in my table "OrderDetail". Its primary keys include the column
"OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
that if the application keep going to use for many years. Is it possible the
identity value will be overflowed? or it will restart from 1 again
automatically after the overflow. And now, i make this column type is "Int".
Anyone can help? Thanks.
Best Rdgs
Ellis
You'll get an overflow when the identity value exceeds the maximum value for
the data type If this is a possibility in your situation, consider using a
data type with a larger maximum value, such as bigint or decimal.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> Dear all,
> I use the identity property that makes generating unique numeric
> values in my table "OrderDetail". Its primary keys include the column
> "OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
> that if the application keep going to use for many years. Is it possible
> the
> identity value will be overflowed? or it will restart from 1 again
> automatically after the overflow. And now, i make this column type is
> "Int".
> Anyone can help? Thanks.
> Best Rdgs
> Ellis
>
|||Many Thanks !! Otherwise I'll be in big trouble when the system get
overflow. But now, I can I solve this problem now? and I'm using int data
type, what's its maximum value of it?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> You'll get an overflow when the identity value exceeds the maximum value
for
> the data type If this is a possibility in your situation, consider using
a[vbcol=seagreen]
> data type with a larger maximum value, such as bigint or decimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
wonder
>
|||Hello Ellis,
from books online:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
(2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
integer.
Regards,
Tomislav Kralj
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
> Many Thanks !! Otherwise I'll be in big trouble when the system get
> overflow. But now, I can I solve this problem now? and I'm using int data
> type, what's its maximum value of it?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> for
> a
> wonder
>
|||Which means that if your IDENTITY is set with the default (1,1), you can
store one row per second for the next 68 years. Just to put things in
perspective
Jacco Schalkwijk
SQL Server MVP
"Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
> Hello Ellis,
> from books online:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
> integer.
>
> Regards,
> Tomislav Kralj
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>
|||Or with bigint, you can store one mullion rows per second for 1142 years
:-)
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:e2g%23QR5LFHA.568@.TK2MSFTNGP09.phx.gbl...
> Which means that if your IDENTITY is set with the default (1,1), you can
> store one row per second for the next 68 years. Just to put things in
> perspective
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
> news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
>

About Identity Column

Dear all,
I use the identity property that makes generating unique numeric
values in my table "OrderDetail". Its primary keys include the column
"OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
that if the application keep going to use for many years. Is it possible the
identity value will be overflowed? or it will restart from 1 again
automatically after the overflow. And now, i make this column type is "Int".
Anyone can help? Thanks.
Best Rdgs
EllisYou'll get an overflow when the identity value exceeds the maximum value for
the data type If this is a possibility in your situation, consider using a
data type with a larger maximum value, such as bigint or decimal.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> Dear all,
> I use the identity property that makes generating unique numeric
> values in my table "OrderDetail". Its primary keys include the column
> "OrderHeaderNo" and the identity column. It's fine up to now. But I wonder
> that if the application keep going to use for many years. Is it possible
> the
> identity value will be overflowed? or it will restart from 1 again
> automatically after the overflow. And now, i make this column type is
> "Int".
> Anyone can help? Thanks.
> Best Rdgs
> Ellis
>|||Many Thanks !! Otherwise I'll be in big trouble when the system get
overflow. But now, I can I solve this problem now? and I'm using int data
type, what's its maximum value of it?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
> You'll get an overflow when the identity value exceeds the maximum value
for
> the data type If this is a possibility in your situation, consider using
a
> data type with a larger maximum value, such as bigint or decimal.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
> > Dear all,
> >
> > I use the identity property that makes generating unique numeric
> > values in my table "OrderDetail". Its primary keys include the column
> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
wonder
> > that if the application keep going to use for many years. Is it possible
> > the
> > identity value will be overflowed? or it will restart from 1 again
> > automatically after the overflow. And now, i make this column type is
> > "Int".
> > Anyone can help? Thanks.
> >
> > Best Rdgs
> > Ellis
> >
> >
>|||Hello Ellis,
from books online:
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
(2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
integer.
Regards,
Tomislav Kralj
"Ellis Yu" <ellis.yu@.transfield.com> wrote in message
news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
> Many Thanks !! Otherwise I'll be in big trouble when the system get
> overflow. But now, I can I solve this problem now? and I'm using int data
> type, what's its maximum value of it?
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum value
> for
>> the data type If this is a possibility in your situation, consider using
> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>|||Which means that if your IDENTITY is set with the default (1,1), you can
store one row per second for the next 68 years. Just to put things in
perspective :)
--
Jacco Schalkwijk
SQL Server MVP
"Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
> Hello Ellis,
> from books online:
> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
> integer.
>
> Regards,
> Tomislav Kralj
> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>> Many Thanks !! Otherwise I'll be in big trouble when the system get
>> overflow. But now, I can I solve this problem now? and I'm using int data
>> type, what's its maximum value of it?
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum value
>> for
>> the data type If this is a possibility in your situation, consider
>> using
>> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique
>> > numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
>> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>>
>|||Or with bigint, you can store one mullion rows per second for 1142 years
:-)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:e2g%23QR5LFHA.568@.TK2MSFTNGP09.phx.gbl...
> Which means that if your IDENTITY is set with the default (1,1), you can
> store one row per second for the next 68 years. Just to put things in
> perspective :)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tomislav Kralj" <tomislav.kralj1@.zg.htnet.hr> wrote in message
> news:eqOb%23t3LFHA.3852@.tk2msftngp13.phx.gbl...
>> Hello Ellis,
>> from books online:
>> Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1
>> (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is
>> integer.
>>
>> Regards,
>> Tomislav Kralj
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:OX2T1V2LFHA.2888@.TK2MSFTNGP12.phx.gbl...
>> Many Thanks !! Otherwise I'll be in big trouble when the system get
>> overflow. But now, I can I solve this problem now? and I'm using int
>> data
>> type, what's its maximum value of it?
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:O7k81B2LFHA.732@.TK2MSFTNGP12.phx.gbl...
>> You'll get an overflow when the identity value exceeds the maximum
>> value
>> for
>> the data type If this is a possibility in your situation, consider
>> using
>> a
>> data type with a larger maximum value, such as bigint or decimal.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Ellis Yu" <ellis.yu@.transfield.com> wrote in message
>> news:ecrdWi1LFHA.904@.tk2msftngp13.phx.gbl...
>> > Dear all,
>> >
>> > I use the identity property that makes generating unique
>> > numeric
>> > values in my table "OrderDetail". Its primary keys include the column
>> > "OrderHeaderNo" and the identity column. It's fine up to now. But I
>> wonder
>> > that if the application keep going to use for many years. Is it
>> > possible
>> > the
>> > identity value will be overflowed? or it will restart from 1 again
>> > automatically after the overflow. And now, i make this column type is
>> > "Int".
>> > Anyone can help? Thanks.
>> >
>> > Best Rdgs
>> > Ellis
>> >
>> >
>>
>>
>>
>

About adding a new column...

Suppose I have a table with the following columns: Year, SalesInEurope,
SalesInAmerica, TotalSales. I want to add a new column called
SalesInAsia, say, but I want it to appear before TotalSales. How can
this be achieved?
Thanks,

BrunoWell first of all I would strongly recommend you change the design of
your table. It's a mistake to represent data in column names because it
makes your data much harder to manipulate and maintain. Basic design
sense would indicate is that the "region" is an attribute and
"America", "Asia", etc are values. In other words one would normally
expect to see ONE column for Region and one column for Sales Value.
Presenting different regions as columns is something you should do in a
report, not in a table.

To answer your specific question, there is no single command to add a
column at a particular ordinal position. The only way to do that in a
table is to re-create the table, or maybe in this case to rename the
columns and repopulate the data. In a production application table
column order is mostly unimportant - the order is determined by the
column order in a SELECT statement, not in a table. If you feel you
have a good reason to change the column order in the table on a
non-production system then you could try using the table designer in
Enterprise Manager. EM will allow you to re-order the columns and
re-create the table for you. EM will also generate the script for you
if you want to see how it's done.

Hope this helps.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> In a production application table column order is mostly unimportant -

Yes, if no humans ever look at the database - which is highly unlikely
if the system is in production.

I just get so tired of this. Everytime someone asks about placing a column
in a certain order in a table, there is always someone knowledgable who
needs to tell that person he does not need to do that.

Column order *does* matter, because it makes it so easy to work with.
The fact that SQL Server does not provide any simple command to change
order is no reason to tell people they want the wrong thing.

--
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 agree with most of what you said. What I mean by "mostly unimportant"
is that for most people most of the time the column order returned by
SELECT * is not important enough to justify the effort and impact of
recreating a table. That's my experience anyway.

> The fact that SQL Server does not provide any simple command to change
> order is no reason to tell people they want the wrong thing.

I don't agree with this. There is good reason to tell people they are
wrong. Implied column ordering is a serious failing of SQL and has lead
to much bad code and incorrect results. I want to discourage anyone
from building column order dependencies into their systems. I would do
so even if SQL Server made it a trivial task to re-order columns - in
fact I'd be even more vocal about it if there were a trivial solution!
I agree that there is a case for physically ordering columns for
support and development purposes but I don't want people to confuse
that service delivery issue with their business's presentational
requirements.

--
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> I agree with most of what you said. What I mean by "mostly unimportant"
> is that for most people most of the time the column order returned by
> SELECT * is not important enough to justify the effort and impact of
> recreating a table. That's my experience anyway.

I would agree that with the current state of the art, that rearranging
columns is a bit too much hassle. Then again, if I were to take over
maintenance were column order were accidental, and particularly different
in different in different instances of the same schema, I might consider
it. (After all, I have the tools to do this rather easily. It is just
the time to run it that could be expensive.)

> I don't agree with this. There is good reason to tell people they are
> wrong. Implied column ordering is a serious failing of SQL and has lead
> to much bad code and incorrect results. I want to discourage anyone
> from building column order dependencies into their systems. I would do
> so even if SQL Server made it a trivial task to re-order columns - in
> fact I'd be even more vocal about it if there were a trivial solution!
> I agree that there is a case for physically ordering columns for
> support and development purposes but I don't want people to confuse
> that service delivery issue with their business's presentational
> requirements.

One should not forget that there actually users out there whose only
application is Open Table in Enterprise Manager. I don't like it myself,
but there was a huge outcry when Microsoft tried to leave it out from
Management Studio in SQL 2005.

Of course, using SELECT * in combination with references to columns by
number in client code is extremely bad practice.

--
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 am asked to do this on a very regular basis.

The logic I normally go through is as follows:

-delete trigger for the original table (if it exists);
-delete all views for the original table, if they exist (they will keep
referencing the original table even after it is renamed)
-rename the original table to table_name_old (executing the native
sp_rename stored procedure)
-create the new table in the correct structure
-create new default value constraints unless you're okay with system
generated names for default value constraints
-insert into new_table (all columns except for new one) as select (all
columns) from original table
-recreate view(s) if applicable
-recreate trigger if applicable
*then confirm all is well before dropping the old table.

I would recommend making a SQL script for this process, and keeping it
as a template for future use.

I also would recommend SQL scripts over EM or any other GUI: All that
GUIs will ultimately do is provide a nice pretty set of screens and
buttons, and then generate a SQL script which you may or may not see.
Getting used to coding the SQL script will eliminate the middle-man,
and allow you to comfortably replicate the same process again and
again.

I can fire you a sample script with the logic that I use if you like.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96D057FB844Yazorman@.127.0.0.1...
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> > In a production application table column order is mostly unimportant -
> Yes, if no humans ever look at the database - which is highly unlikely
> if the system is in production.
> I just get so tired of this. Everytime someone asks about placing a column
> in a certain order in a table, there is always someone knowledgable who
> needs to tell that person he does not need to do that.
> Column order *does* matter, because it makes it so easy to work with.
> The fact that SQL Server does not provide any simple command to change
> order is no reason to tell people they want the wrong thing.

I can see your point of view (and often wish in EM I could see columns in
the order I want) but to a point have to disagree with it.

Let me pull a Celko here, but I think getting into the mindshift of thinking
of a table and realizing that order of columns should not be a physical
attribute of the table.

The minute folks assume they are, they start accepting code like select *
from bar and assuming that * will return columns in a specific manner. This
burned us on a code change a year or so ago where due to the way the schema
was changing (partly due to replication) the order of the columns DID in
fact change and of course the programmers who wrote the code in the height
of the dotcom era saved 30 seconds (or about 2 days "internet time" :-) by
typing * instead of a proper column list.

This was fine for 3-4 years until suddenly production code on a high volume
website broke.

Ideally Enterprise Manager could access some sort of metadata that would
store the desired view of columns, independent of the physical layout of the
table.

> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||BD (bobby_dread@.hotmail.com) writes:
> I also would recommend SQL scripts over EM or any other GUI: All that
> GUIs will ultimately do is provide a nice pretty set of screens and
> buttons, and then generate a SQL script which you may or may not see.
> Getting used to coding the SQL script will eliminate the middle-man,
> and allow you to comfortably replicate the same process again and
> again.

You can use EM to get a script, that you can work from. But beware
that the script that EM generates has several flaws, that you need to
fix:

o Remove all BEGIN TRANSACTION and COMMIT TRANSACTION, except the first
BEGIN and the last COMMIT.

o Remove all GO. Instead wrap all the ALTER and CREATE TABLE statements
in EXEC.

o Add SET XACT_ABORT ON first in the script.

o Replace all WITH NOCHECK in the script WITH CHECK.

o Review the script carefully, so that it does not include changes to
do not intend to make. Yes, EM, may add such changes.

The first three points has to do with the transaction scope. You may
not always want a transaction, if you have large tables. But in such
case, you must be prepared to restore a backup if the script fails.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> I can see your point of view (and often wish in EM I could see columns in
> the order I want) but to a point have to disagree with it.
> Let me pull a Celko here, but I think getting into the mindshift of
> thinking of a table and realizing that order of columns should not be a
> physical attribute of the table.
> The minute folks assume they are, they start accepting code like select
> * from bar and assuming that * will return columns in a specific manner.
> This burned us on a code change a year or so ago where due to the way
> the schema was changing (partly due to replication) the order of the
> columns DID in fact change and of course the programmers who wrote the
> code in the height of the dotcom era saved 30 seconds (or about 2 days
> "internet time" :-) by typing * instead of a proper column list.
> This was fine for 3-4 years until suddenly production code on a high
> volume website broke.

Yes, SELECT * does not belong in application code. (Unless it's some
simple throw-away thing like keep track of the local sports club.)

Just like, it is not good practice to call a routine (be that a C++
function of stored procedure) with 20 parameters with positional actual
parameters. Yet, few would argue that it's a good idea to have the
parameters in random order.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 13 Sep 2005 17:06:25 -0700, BD wrote:

>I am asked to do this on a very regular basis.
>The logic I normally go through is as follows:
(snip)
>I would recommend making a SQL script for this process, and keeping it
>as a template for future use.

Hi BD,

If you make a generic template, then you might wish to include dropping
and recreation of foreign key constraints that reference the table to be
changed. You omitted that from your shortlist.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes, that is true. I was working from memory, and neglected to mention
the FKs.

Which is another good reason for using scripts - memory ain't always
all it could be. ;)|||And, recreating any PK constraints, depending on the nature of the
column change.

I'm pretty sure PK constraints will also follow the SID of the table,
and refer to the 'old' table even after it is renamed, so they should
be dropped and recreated. I could be mistaken on that point, though.|||On 14 Sep 2005 15:59:22 -0700, BD wrote:

>And, recreating any PK constraints, depending on the nature of the
>column change.
>I'm pretty sure PK constraints will also follow the SID of the table,
>and refer to the 'old' table even after it is renamed, so they should
>be dropped and recreated. I could be mistaken on that point, though.

Hi BD,

PK constraints (and all other constraints that are defined on the table)
will be dropped when the table is dropped. They should of course be
included in the CREATE TABLE statement for the new table, or added with
ALTER TABLE statements.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Right; so if you want to preserve the names of your PK constraints, it
wouldn't be enough to just recreate the new table, drop the old one,
and assume that the constraints will be intact and point to where you
expect them to... ;)