Modify data structure

I would like to change the data type from date time to timestamp and
nvarchar(14) to nvarchar(20).
Are there any impact to change a live database?
Thanks millions in advance,Hi ,
nvarchar(14) to nvarchar(20) shouldn't be a problem
But
a timestamp is not a date value. It is a binary value which is linked to
the current row. So changing that will clase a problem to your clients.
Before changing
Thisis from BOL
timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database. timestamp is
used typically as a mechanism for version-stamping table rows. The storage
size is 8 bytes.
Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data
type defined in the SQL-92 standard. The SQL-92 timestamp data type is
equivalent to the Transact-SQL datetime data type.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:70B6B3CC-1490-4C00-8232-DB0486C5D0AE@.microsoft.com...
>I would like to change the data type from date time to timestamp and
> nvarchar(14) to nvarchar(20).
> Are there any impact to change a live database?
> Thanks millions in advance,

Modify Data Source Or CataLog On Data Source

I have a set of 20 reports. I want to be able to point these 20 reports at
10 different sources of data. The data structures are identical at all 10
sources, but they contain different data. You could think of it as each
source of data represents a customer and I want to use a shared set of
reports (maintained in one and only one place) to present data against these
10 different sources. I would like to be able to either modify a reports
data source or the catalog contained within a given data source at report
execution time. I can store in a session variable the name of the catalog or
data source for which the report should be executed against, but I need a
method to modify this information prior to execution...but modify it only in
memory on the web server so as not to physically save it back to the report
server. Physically saving it back to the report server would create data
contention issues and should be unecessary. I've been unable to find a
method that allows this, however. It seems all the web service methods I've
found in this area involve actually saving the new datasource information on
the server as opposed to in memory at render time. Any ideas?Two techniques that people use is to pass the database you want to use as a
parameter and pass that through to a stored procedure that then does an exec
select @.SQL = 'select * from ' + @.DB + '.dbo.sometable'
exec (@.SQL)
You can also do something similar in the query definition by using the
generic query designer and setting it to an expression.
= "select * from " & @.DB & ".dbo.sometable"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"OLAPMonkey" <OLAPMonkey@.discussions.microsoft.com> wrote in message
news:104E7130-A005-4F0E-8A24-6F70261870C6@.microsoft.com...
> I have a set of 20 reports. I want to be able to point these 20 reports
at
> 10 different sources of data. The data structures are identical at all 10
> sources, but they contain different data. You could think of it as each
> source of data represents a customer and I want to use a shared set of
> reports (maintained in one and only one place) to present data against
these
> 10 different sources. I would like to be able to either modify a reports
> data source or the catalog contained within a given data source at report
> execution time. I can store in a session variable the name of the catalog
or
> data source for which the report should be executed against, but I need a
> method to modify this information prior to execution...but modify it only
in
> memory on the web server so as not to physically save it back to the
report
> server. Physically saving it back to the report server would create data
> contention issues and should be unecessary. I've been unable to find a
> method that allows this, however. It seems all the web service methods
I've
> found in this area involve actually saving the new datasource information
on
> the server as opposed to in memory at render time. Any ideas?|||Unfortunately, I'm reporting against Analysis Services so I cannot adjust the
database on the fly via MDX in a similar fashion to what you've outlined here
with SQL. Any other ideas?
"Bruce L-C [MVP]" wrote:
> Two techniques that people use is to pass the database you want to use as a
> parameter and pass that through to a stored procedure that then does an exec
> select @.SQL = 'select * from ' + @.DB + '.dbo.sometable'
> exec (@.SQL)
> You can also do something similar in the query definition by using the
> generic query designer and setting it to an expression.
> = "select * from " & @.DB & ".dbo.sometable"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "OLAPMonkey" <OLAPMonkey@.discussions.microsoft.com> wrote in message
> news:104E7130-A005-4F0E-8A24-6F70261870C6@.microsoft.com...
> > I have a set of 20 reports. I want to be able to point these 20 reports
> at
> > 10 different sources of data. The data structures are identical at all 10
> > sources, but they contain different data. You could think of it as each
> > source of data represents a customer and I want to use a shared set of
> > reports (maintained in one and only one place) to present data against
> these
> > 10 different sources. I would like to be able to either modify a reports
> > data source or the catalog contained within a given data source at report
> > execution time. I can store in a session variable the name of the catalog
> or
> > data source for which the report should be executed against, but I need a
> > method to modify this information prior to execution...but modify it only
> in
> > memory on the web server so as not to physically save it back to the
> report
> > server. Physically saving it back to the report server would create data
> > contention issues and should be unecessary. I've been unable to find a
> > method that allows this, however. It seems all the web service methods
> I've
> > found in this area involve actually saving the new datasource information
> on
> > the server as opposed to in memory at render time. Any ideas?
>
>|||Nope, sorry.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"OLAPMonkey" <OLAPMonkey@.discussions.microsoft.com> wrote in message
news:AFF50CB4-124F-4644-81DB-1E223D90E17A@.microsoft.com...
> Unfortunately, I'm reporting against Analysis Services so I cannot adjust
the
> database on the fly via MDX in a similar fashion to what you've outlined
here
> with SQL. Any other ideas?
>
> "Bruce L-C [MVP]" wrote:
> > Two techniques that people use is to pass the database you want to use
as a
> > parameter and pass that through to a stored procedure that then does an
exec
> >
> > select @.SQL = 'select * from ' + @.DB + '.dbo.sometable'
> > exec (@.SQL)
> >
> > You can also do something similar in the query definition by using the
> > generic query designer and setting it to an expression.
> > = "select * from " & @.DB & ".dbo.sometable"
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "OLAPMonkey" <OLAPMonkey@.discussions.microsoft.com> wrote in message
> > news:104E7130-A005-4F0E-8A24-6F70261870C6@.microsoft.com...
> > > I have a set of 20 reports. I want to be able to point these 20
reports
> > at
> > > 10 different sources of data. The data structures are identical at
all 10
> > > sources, but they contain different data. You could think of it as
each
> > > source of data represents a customer and I want to use a shared set of
> > > reports (maintained in one and only one place) to present data against
> > these
> > > 10 different sources. I would like to be able to either modify a
reports
> > > data source or the catalog contained within a given data source at
report
> > > execution time. I can store in a session variable the name of the
catalog
> > or
> > > data source for which the report should be executed against, but I
need a
> > > method to modify this information prior to execution...but modify it
only
> > in
> > > memory on the web server so as not to physically save it back to the
> > report
> > > server. Physically saving it back to the report server would create
data
> > > contention issues and should be unecessary. I've been unable to find
a
> > > method that allows this, however. It seems all the web service
methods
> > I've
> > > found in this area involve actually saving the new datasource
information
> > on
> > > the server as opposed to in memory at render time. Any ideas?
> >
> >
> >sql

Modify data in SQL Server with access mdb

hello

I'm trying to use access to modify data in SQL Server 2000 tables. Everything is ok on most tables, but only one made me troubles. This one when I'm doing something on it is saying that an other user modify this record and I cannot save this information.

This table has 40 rows and 3 primary key.

I'm the only one to work on this database.

thank you for responseDoes your table have any bit data types which allow null values? Access can't deal with null value data bit columns and if you modify a row with a null value it gives you an error saying it has already been modified by another user.

hope this helps

Originally posted by anakin_ch
hello

I'm trying to use access to modify data in SQL Server 2000 tables. Everything is ok on most tables, but only one made me troubles. This one when I'm doing something on it is saying that an other user modify this record and I cannot save this information.

This table has 40 rows and 3 primary key.

I'm the only one to work on this database.

thank you for response

modify constratints

Hi all
In Table1 I have prmary key constraint and it is refernced by some column
as a foreign key in Table2
Now I want to modify my constraint by TSQL in primary key table ,
How to do it?
For ex
in primary key table
ALTER TABLE [dbo].[table1]
add
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(
[col1]
) ON [PRIMARY]
GO
in foreign key table
ALTER TABLE [dbo].[table2] ADD
CONSTRAINT [FK_table2_table1] FOREIGN KEY
(
[colA]
) REFERENCES [dbo].[table1] (
[col1]
)
GO
Now I want to change constraint PK_Table1 in primary key table to
NonClustered by TSQL Code. How to do it ?
I can not drop the constraint in primary key table since it is referenced by
other table so how to modify it to Nonclustered?
Amdrop the foreign key constraints and then recreate them.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"AM" <anonymous@.examnotes.net> wrote in message
news:OUDysV0VFHA.3540@.TK2MSFTNGP15.phx.gbl...
> Hi all
> In Table1 I have prmary key constraint and it is refernced by some column
> as a foreign key in Table2
> Now I want to modify my constraint by TSQL in primary key table ,
> How to do it?
> For ex
> in primary key table
> ALTER TABLE [dbo].[table1]
> add
> CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
> (
> [col1]
> ) ON [PRIMARY]
> GO
> in foreign key table
> ALTER TABLE [dbo].[table2] ADD
> CONSTRAINT [FK_table2_table1] FOREIGN KEY
> (
> [colA]
> ) REFERENCES [dbo].[table1] (
> [col1]
> )
> GO
> Now I want to change constraint PK_Table1 in primary key table to
> NonClustered by TSQL Code. How to do it ?
> I can not drop the constraint in primary key table since it is referenced
> by
> other table so how to modify it to Nonclustered?
>
> --
> Am
>|||But If I change the primary key constrarint from EM then I don't have to
drop foreign key constraint.
Same can not be done by code?
Thanks
AM
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uNTnUU2VFHA.2660@.TK2MSFTNGP10.phx.gbl...
> drop the foreign key constraints and then recreate them.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "AM" <anonymous@.examnotes.net> wrote in message
> news:OUDysV0VFHA.3540@.TK2MSFTNGP15.phx.gbl...
column
referenced
>|||Behind the scenes, EM drops and recreates foreign key constraints.
In EM, clique the "save script" icon after you have made the change and you
will see.
"AM" <anonymous@.examnotes.net> wrote in message
news:uzH99z9VFHA.3488@.TK2MSFTNGP10.phx.gbl...
> But If I change the primary key constrarint from EM then I don't have to
> drop foreign key constraint.
> Same can not be done by code?
> Thanks
> AM
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
> news:uNTnUU2VFHA.2660@.TK2MSFTNGP10.phx.gbl...
> --
> column
> referenced
>

modify column output

using sql database, i have a smallmoney column. when i enter an amount, 50.00 for example, i have 50.0000 displayed. is there a way to only have 50.00 displayed?

same with the smalldatetime, is there a way to limit the display to "mm/dd/yyyy" without the "hh:mms am"

To get 2 decimal places (instead of 4) just use the ROUND() function to get rid of the 'extra' 2 digits.

As far as the date issue, have a look at the SQL function CONVERT in BOL. Basically something like this should return just the date portion:

Code Snippet

SELECT CONVERT(varchar(10), GETDATE(), 101)

|||

Formatting should be done on the client not on the server. if you have an additional presentation layer above the SQL Server, do you formatting work here, as SQL Server is mainly optmized / made for storing and retrieving data.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Modify column order

Hello,
I currently have a table with 2 primary keys, and 6
columsn following that. I want to add 2 new columns, that
will also be primary keys. I want to insert these new
columns in position 3 and 4.
Is there a way to do this without dropping the table and
recreating it in the correct column order ?
eg:
Table : dbo.TMCLASS has columns as follows
COUNTRYCODE (pk)
CLASS (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I want a new structure (PROPERTYTYE and SEQUENCENO added)
Table : dbo.TMCLASS
COUNTRYCODE (pk)
CLASS (pk)
PROPERTYTYPE (pk)
SEQUENCENO (pk)
EFFECTIVEDATE
GOODSSERVICES
INTERNATIONALCLASS
ASSOCIATEDCLASSES
CLASSHEADING
CLASSNOTES
PROPERTYTYPE
I am tryign to avoid dropping and recreating the table as
there is data at client sites.
Thanks,
AlisonDon't think so. I also don't know why you would want to. I see a few
people asking for this aesthetic change. If you qualify all your statements
instead of using * then column order is irrelevant. May look prettier in EM
or other tool but that is about it I thnk.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Alison Bell" <abell@.cpaglobal.com> wrote in message
news:047f01c36552$898f9d40$a601280a@.phx.gbl...
> Hello,
> I currently have a table with 2 primary keys, and 6
> columsn following that. I want to add 2 new columns, that
> will also be primary keys. I want to insert these new
> columns in position 3 and 4.
> Is there a way to do this without dropping the table and
> recreating it in the correct column order ?
> eg:
> Table : dbo.TMCLASS has columns as follows
> COUNTRYCODE (pk)
> CLASS (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I want a new structure (PROPERTYTYE and SEQUENCENO added)
> Table : dbo.TMCLASS
> COUNTRYCODE (pk)
> CLASS (pk)
> PROPERTYTYPE (pk)
> SEQUENCENO (pk)
> EFFECTIVEDATE
> GOODSSERVICES
> INTERNATIONALCLASS
> ASSOCIATEDCLASSES
> CLASSHEADING
> CLASSNOTES
> PROPERTYTYPE
> I am tryign to avoid dropping and recreating the table as
> there is data at client sites.
> Thanks,
> Alison
>|||I don't think there is a way either. Reason for wanting
the specific order is to match "alltables" script
generated by ERwin for data transfer at a later date. It
just means modifying the generated script each time we
make a DB change, as ERwin places the columns in pos 3
and 4 (non modifyable).
Thanks,
Alison
>--Original Message--
>Don't think so. I also don't know why you would want
to. I see a few
>people asking for this aesthetic change. If you qualify
all your statements
>instead of using * then column order is irrelevant. May
look prettier in EM
>or other tool but that is about it I thnk.|||Problem being I must do this via SQL scripting. It is
part of an upgrade script being shipped to client sites.
>--Original Message--
>easiest way is use EM's (pet's tool) table designer. Sql
>2000 will drop and create with your requirements. You
>don't have to do anything.
>>--Original Message--
>>I don't think there is a way either. Reason for
wanting
>>the specific order is to match "alltables" script
>>generated by ERwin for data transfer at a later date.
It
>>just means modifying the generated script each time we
>>make a DB change, as ERwin places the columns in pos 3
>>and 4 (non modifyable).
>>Thanks,
>>Alison
>>--Original Message--
>>Don't think so. I also don't know why you would want
>>to. I see a few
>>people asking for this aesthetic change. If you
qualify
>>all your statements
>>instead of using * then column order is irrelevant.
May
>>look prettier in EM
>>or other tool but that is about it I thnk.
>>.
>.
>|||Then you have to re-create the table (this is what EM does under covers anyhow). There is no
functionality in TSQL (or ANSI SQL, as I remember) to change column order.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Alison Bell" <abell@.cpaglobal.com> wrote in message news:0fe901c366bd$edc2e210$a001280a@.phx.gbl...
> Problem being I must do this via SQL scripting. It is
> part of an upgrade script being shipped to client sites.
> >--Original Message--
> >easiest way is use EM's (pet's tool) table designer. Sql
> >2000 will drop and create with your requirements. You
> >don't have to do anything.
> >
> >>--Original Message--
> >>I don't think there is a way either. Reason for
> wanting
> >>the specific order is to match "alltables" script
> >>generated by ERwin for data transfer at a later date.
> It
> >>just means modifying the generated script each time we
> >>make a DB change, as ERwin places the columns in pos 3
> >>and 4 (non modifyable).
> >>
> >>Thanks,
> >>Alison
> >>
> >>--Original Message--
> >>Don't think so. I also don't know why you would want
> >>to. I see a few
> >>people asking for this aesthetic change. If you
> qualify
> >>all your statements
> >>instead of using * then column order is irrelevant.
> May
> >>look prettier in EM
> >>or other tool but that is about it I thnk.
> >>
> >>.
> >>
> >.
> >

modify column in tsql

How can I modify a column in tsql in, say, an sp? For example, suppose I
have a column called 'eff'; it's a float and it allows null values. I want
to change it to be a column that does not allow nulls and which instead has
a default value of 0 (or 0.00). The following:
alter table defaulttable modify column eff float not null default 0.00
throws an error (incorrect syntax near the keyword 'column'.
Tx for any help.
Bernie YaegerThis is a multi-part message in MIME format.
--=_NextPart_000_02F6_01C36019.41D01270
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You wouldn't normally do this in a proc. The error is in the word ="modify":
alter table defaulttable alter column eff float not null default 0.00
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message =news:QERZa.42546$_R5.12697593@.news4.srv.hcvlny.cv.net...
How can I modify a column in tsql in, say, an sp? For example, suppose =I
have a column called 'eff'; it's a float and it allows null values. I =want
to change it to be a column that does not allow nulls and which instead =has
a default value of 0 (or 0.00). The following:
alter table defaulttable modify column eff float not null default =0.00
throws an error (incorrect syntax near the keyword 'column'.
Tx for any help.
Bernie Yaeger
--=_NextPart_000_02F6_01C36019.41D01270
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You wouldn't normally do this in a =proc. The error is in the word "modify":
alter table defaulttable =alter column eff float not null default =0.00
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Bernie Yaeger" =wrote in message news:QERZa=.42546$_R5.12697593@.news4.srv.hcvlny.cv.net...How can I modify a column in tsql in, say, an sp? For example, suppose =Ihave a column called 'eff'; it's a float and it allows null =values. I wantto change it to be a column that does not allow nulls and which =instead hasa default value of 0 (or 0.00). The following: alter table =defaulttable modify column eff float not null default 0.00throws an error (incorrect =syntax near the keyword 'column'.Tx for any help.Bernie Yaeger

---

--=_NextPart_000_02F6_01C36019.41D01270--|||Tom
that will raise an error at the DEFAULT
This will work though
alter table defaulttable alter column eff float not null
ALTER TABLE defaulttable ADD CONSTRAINT ABC DEFAULT 0.00 for eff
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Hi Allan,
Tx again Allan for your continued assistance.
Bernie
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:Oy28J1DYDHA.652@.TK2MSFTNGP10.phx.gbl...
> Tom
> that will raise an error at the DEFAULT
> This will work though
>
> alter table defaulttable alter column eff float not null
> ALTER TABLE defaulttable ADD CONSTRAINT ABC DEFAULT 0.00 for eff
>
>
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||This is a multi-part message in MIME format.
--=_NextPart_000_002C_01C36020.075903C0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Hi Tom,
Tx for your help.
Bernie
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:e74RMrDYDHA.1580@.tk2msftngp13.phx.gbl...
You wouldn't normally do this in a proc. The error is in the word ="modify":
alter table defaulttable alter column eff float not null default 0.00
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Bernie Yaeger" <berniey@.cherwellinc.com> wrote in message =news:QERZa.42546$_R5.12697593@.news4.srv.hcvlny.cv.net...
How can I modify a column in tsql in, say, an sp? For example, =suppose I
have a column called 'eff'; it's a float and it allows null values. I =want
to change it to be a column that does not allow nulls and which =instead has
a default value of 0 (or 0.00). The following:
alter table defaulttable modify column eff float not null =default 0.00
throws an error (incorrect syntax near the keyword 'column'.
Tx for any help.
Bernie Yaeger
--=_NextPart_000_002C_01C36020.075903C0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Hi Tom,
Tx for your help.
Bernie
"Tom Moreau" = wrote in message news:e74RMrDYDHA.1580=@.tk2msftngp13.phx.gbl...
You wouldn't normally do this in a =proc. The error is in the word "modify":

alter table defaulttable =alter column eff float not null default 0.00
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Bernie Yaeger" =wrote in message news:QERZa=.42546$_R5.12697593@.news4.srv.hcvlny.cv.net...How can I modify a column in tsql in, say, an sp? For example, =suppose Ihave a column called 'eff'; it's a float and it allows null =values. I wantto change it to be a column that does not allow nulls and =which instead hasa default value of 0 (or 0.00). The following: alter table =defaulttable modify column eff float not null default 0.00throws an error (incorrect syntax near the keyword 'column'.Tx for any help.Bernie Yaeger

---

--=_NextPart_000_002C_01C36020.075903C0--sql