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,
Friday, March 30, 2012
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
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
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
>
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:mm
s 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.
> >>
> >>.
> >>
> >.
> >
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
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"
--=_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"
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"
--=_NextPart_000_002C_01C36020.075903C0--sql
Modify Chart Y-Scale
Hi
I have the following Problem:
I have to do a Graphical analysis from a survey, for that i decided to use
the ReportingServices for VS.NET.
And for the Data Displaying i took the Chart object.
The Data is placed correctly into the chart, but there is one problem.
For the Y-Scale i defined a maximum (4) and a minimum (0) value. (this works)
But now for the Scale-Display i dont want to show 0 - 1 - 2 - 3 - 4 on the
side, i want to put C - B - A - A+ - A++.
I tried so many ways to force that, but it never worked. I already tried to
set the Letters by IIF() Statements, i tried to detect the right letter by
scanning the scale with chart1.ValueAxis. and so on.
Im really thankful for your help
thank you, greetings
Marcel GaufroidDid you look into Bar charts (they flip X- and Y-axis)? You could define 5
static series values which represent the groups C, B, A, A+, A++ and show
the values along the X-axis.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marcel Gaufroid / cyrex" <Marcel Gaufroid /
cyrex@.discussions.microsoft.com> wrote in message
news:3D8FEEBE-72FE-4605-8999-987F4CE83225@.microsoft.com...
> Hi
> I have the following Problem:
> I have to do a Graphical analysis from a survey, for that i decided to use
> the ReportingServices for VS.NET.
> And for the Data Displaying i took the Chart object.
> The Data is placed correctly into the chart, but there is one problem.
> For the Y-Scale i defined a maximum (4) and a minimum (0) value. (this
works)
> But now for the Scale-Display i dont want to show 0 - 1 - 2 - 3 - 4 on the
> side, i want to put C - B - A - A+ - A++.
> I tried so many ways to force that, but it never worked. I already tried
to
> set the Letters by IIF() Statements, i tried to detect the right letter by
> scanning the scale with chart1.ValueAxis. and so on.
> Im really thankful for your help
> thank you, greetings
> Marcel Gaufroid|||wich Bar charts?
i have a Simple-Scatter Chart.
"Robert Bruckner [MSFT]" wrote:
> Did you look into Bar charts (they flip X- and Y-axis)? You could define 5
> static series values which represent the groups C, B, A, A+, A++ and show
> the values along the X-axis.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Marcel Gaufroid / cyrex" <Marcel Gaufroid /
> cyrex@.discussions.microsoft.com> wrote in message
> news:3D8FEEBE-72FE-4605-8999-987F4CE83225@.microsoft.com...
> > Hi
> >
> > I have the following Problem:
> >
> > I have to do a Graphical analysis from a survey, for that i decided to use
> > the ReportingServices for VS.NET.
> > And for the Data Displaying i took the Chart object.
> >
> > The Data is placed correctly into the chart, but there is one problem.
> >
> > For the Y-Scale i defined a maximum (4) and a minimum (0) value. (this
> works)
> > But now for the Scale-Display i dont want to show 0 - 1 - 2 - 3 - 4 on the
> > side, i want to put C - B - A - A+ - A++.
> >
> > I tried so many ways to force that, but it never worked. I already tried
> to
> > set the Letters by IIF() Statements, i tried to detect the right letter by
> > scanning the scale with chart1.ValueAxis. and so on.
> >
> > Im really thankful for your help
> >
> > thank you, greetings
> > Marcel Gaufroid
>
>|||i also got the problem now that just one point is shown on the Chart the
first), the others arent shown. why is that?
i havent used any aggregate functions ... :(
"Marcel Gaufroid / cyrex" wrote:
> Hi
> I have the following Problem:
> I have to do a Graphical analysis from a survey, for that i decided to use
> the ReportingServices for VS.NET.
> And for the Data Displaying i took the Chart object.
> The Data is placed correctly into the chart, but there is one problem.
> For the Y-Scale i defined a maximum (4) and a minimum (0) value. (this works)
> But now for the Scale-Display i dont want to show 0 - 1 - 2 - 3 - 4 on the
> side, i want to put C - B - A - A+ - A++.
> I tried so many ways to force that, but it never worked. I already tried to
> set the Letters by IIF() Statements, i tried to detect the right letter by
> scanning the scale with chart1.ValueAxis. and so on.
> Im really thankful for your help
> thank you, greetings
> Marcel Gaufroid
I have the following Problem:
I have to do a Graphical analysis from a survey, for that i decided to use
the ReportingServices for VS.NET.
And for the Data Displaying i took the Chart object.
The Data is placed correctly into the chart, but there is one problem.
For the Y-Scale i defined a maximum (4) and a minimum (0) value. (this works)
But now for the Scale-Display i dont want to show 0 - 1 - 2 - 3 - 4 on the
side, i want to put C - B - A - A+ - A++.
I tried so many ways to force that, but it never worked. I already tried to
set the Letters by IIF() Statements, i tried to detect the right letter by
scanning the scale with chart1.ValueAxis. and so on.
Im really thankful for your help
thank you, greetings
Marcel GaufroidDid you look into Bar charts (they flip X- and Y-axis)? You could define 5
static series values which represent the groups C, B, A, A+, A++ and show
the values along the X-axis.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marcel Gaufroid / cyrex" <Marcel Gaufroid /
cyrex@.discussions.microsoft.com> wrote in message
news:3D8FEEBE-72FE-4605-8999-987F4CE83225@.microsoft.com...
> Hi
> I have the following Problem:
> I have to do a Graphical analysis from a survey, for that i decided to use
> the ReportingServices for VS.NET.
> And for the Data Displaying i took the Chart object.
> The Data is placed correctly into the chart, but there is one problem.
> For the Y-Scale i defined a maximum (4) and a minimum (0) value. (this
works)
> But now for the Scale-Display i dont want to show 0 - 1 - 2 - 3 - 4 on the
> side, i want to put C - B - A - A+ - A++.
> I tried so many ways to force that, but it never worked. I already tried
to
> set the Letters by IIF() Statements, i tried to detect the right letter by
> scanning the scale with chart1.ValueAxis. and so on.
> Im really thankful for your help
> thank you, greetings
> Marcel Gaufroid|||wich Bar charts?
i have a Simple-Scatter Chart.
"Robert Bruckner [MSFT]" wrote:
> Did you look into Bar charts (they flip X- and Y-axis)? You could define 5
> static series values which represent the groups C, B, A, A+, A++ and show
> the values along the X-axis.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Marcel Gaufroid / cyrex" <Marcel Gaufroid /
> cyrex@.discussions.microsoft.com> wrote in message
> news:3D8FEEBE-72FE-4605-8999-987F4CE83225@.microsoft.com...
> > Hi
> >
> > I have the following Problem:
> >
> > I have to do a Graphical analysis from a survey, for that i decided to use
> > the ReportingServices for VS.NET.
> > And for the Data Displaying i took the Chart object.
> >
> > The Data is placed correctly into the chart, but there is one problem.
> >
> > For the Y-Scale i defined a maximum (4) and a minimum (0) value. (this
> works)
> > But now for the Scale-Display i dont want to show 0 - 1 - 2 - 3 - 4 on the
> > side, i want to put C - B - A - A+ - A++.
> >
> > I tried so many ways to force that, but it never worked. I already tried
> to
> > set the Letters by IIF() Statements, i tried to detect the right letter by
> > scanning the scale with chart1.ValueAxis. and so on.
> >
> > Im really thankful for your help
> >
> > thank you, greetings
> > Marcel Gaufroid
>
>|||i also got the problem now that just one point is shown on the Chart the
first), the others arent shown. why is that?
i havent used any aggregate functions ... :(
"Marcel Gaufroid / cyrex" wrote:
> Hi
> I have the following Problem:
> I have to do a Graphical analysis from a survey, for that i decided to use
> the ReportingServices for VS.NET.
> And for the Data Displaying i took the Chart object.
> The Data is placed correctly into the chart, but there is one problem.
> For the Y-Scale i defined a maximum (4) and a minimum (0) value. (this works)
> But now for the Scale-Display i dont want to show 0 - 1 - 2 - 3 - 4 on the
> side, i want to put C - B - A - A+ - A++.
> I tried so many ways to force that, but it never worked. I already tried to
> set the Letters by IIF() Statements, i tried to detect the right letter by
> scanning the scale with chart1.ValueAxis. and so on.
> Im really thankful for your help
> thank you, greetings
> Marcel Gaufroid
Modify CellsetGrid
Hello there
I am using Richard Kutchaks CellsetGrid though this issue is of a general kind. I want to filter on a hierarchy. This filtering occurs as a subcube. I intend only to chose one member at a time. The thing is that I want to read the property "level" from the chosen (filtered) member. I can't use currentmember since no member is actually chosen, but the filtered hierarchy points at a member.
Is there a way to catch that member and through that actually read properties of it? I don't care if it fails when more than one member is chosen.
This code worked when I used the old way of filtering which cannot be used with CellsetGrid:
CASE WHEN [Prisme Dimension].[Hierarki Prisme Budget].currentmember.LEVEL.NAME = [Anvisning].[Hierarki Anvisning].currentmember.Properties("Level")
THEN 1
ELSE 0
END
This is the code that the CellsetGrid fires (with me modifying the calculated member so you can see partly what I want to achieve:
WITH MEMBER [Measures].[Test2] AS
'tail(EXISTING [Anvisning].[Hierarki Anvisning],1).item(0).name'
SELECT
{[Measures].[Test2]} ON COLUMNS
from
(Select
{{[Anvisning].[Hierarki Anvisning].[Anvisning 2].&[2]&[1]}
}
on 0 from [writebacktest]) CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
Background for interested folks:
Each member of a organisation has a property with the name of a level of another dimension. I want to combine these two things. When a user choses a organisation member, it automatically gets the other dimensions level and will, when the user drillsdown to that level, active a action. This action is the key to enable input of forecasts (they should only be made on the level that is given by the organisation's property).
Johan
I actually found out by extensive search in this forum, the very answer. It doesn't fit perfectly but it is an answer. This MDX will work. Notice how I've added the filtered dimension once again in the query. This is not supported out of the box in the CellSetGrid which unfortunately is the only "MDX Compatibility=2" viewer I've got. SQL 2005 tools are not level 2 compatible (funny enough).
WITH MEMBER [Measures].[Test2] AS
'tail(EXISTING [Anvisning].[Hierarki Anvisning],1).item(0).Properties("Level")'
SELECT
{[Measures].[Test2]} ON COLUMNS,
[Anvisning].[Hierarki Anvisning].[Anvisning 2] ON ROWS
from
(Select
{{[Anvisning].[Hierarki Anvisning].[Anvisning 2].&[2]&[1]}
}
on 0 from [writebacktest]) CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
//Johan
|||What we should tell Richard, is that CellSetGrid should modify its query generation to do the same thing as Excel 2007 does. When there is a single member selected - use WHERE clause. I.e.
WITH MEMBER [Measures].[Test2] AS
'tail(EXISTING [Anvisning].[Hierarki Anvisning],1).item(0).Properties("Level")'
SELECT
{[Measures].[Test2]} ON COLUMNS
from [writebacktest])
WHERE [Anvisning].[Hierarki Anvisning].[Anvisning 2].&[2]&[1]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
|||Well, this issue has learned me a bit about the new query practices. Richard has been so kind to expose his sourcecode. I thought of actually to try to see how I could modifiy the query methods myself. It might be that I need to add more dimensions and so it should be possible to set a query parameter: Use Where Clause and Use Subcube.
//Johan
Modify all store proc in DB in one shot
Is it possible that i can use a store proc to modify all the rest of my store procedures that i have in my DB ??
I have so many created allready and it will be to long to go throught each one of them to modify my text inside.
what i wish to do is a store proc that will allow me to loop to all my store proc of the current DB and look inside for specific text that i would like to change with the new value !!
any advise or example..
thanx.My strong suggestion is to script all of the stored procedures (including their permissions) using SQL Enterprise Mangler, and save two copies of the script. Edit one of the scripts to suit your fancy, then play it into your database. Test, test, test, then test some more.
When you discover what you mangled, play in the pristine script and start over!
-PatP|||Are the procedures stored in files or do they solely exist in sqlserver? If the latter, you have perhaps a challenge. I'm not sure if this will work: select the inside from syscomments (column text), then change what you want. You could commit the change to the syscomments table but you'll have to reconfigure sql server to allow this and I am not sure if that's a good idea. Besides, you'll have to think about having to let sql server know the procedure changed so it'll recompile (in order to make sure it'll use the latest version). Instead you could try to have the contents of the procedure executed using sp_executesql, don't forget the drop and create commands though.
I would not recommend either one since you'll have some issues to consider prior actually doing the changes.|||btw: Pat's suggestion is way preferred over mine|||Hey look at that...a cross post :D
SQLTeam (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35267)
Check out the link in the link...let me know what you think...
I have so many created allready and it will be to long to go throught each one of them to modify my text inside.
what i wish to do is a store proc that will allow me to loop to all my store proc of the current DB and look inside for specific text that i would like to change with the new value !!
any advise or example..
thanx.My strong suggestion is to script all of the stored procedures (including their permissions) using SQL Enterprise Mangler, and save two copies of the script. Edit one of the scripts to suit your fancy, then play it into your database. Test, test, test, then test some more.
When you discover what you mangled, play in the pristine script and start over!
-PatP|||Are the procedures stored in files or do they solely exist in sqlserver? If the latter, you have perhaps a challenge. I'm not sure if this will work: select the inside from syscomments (column text), then change what you want. You could commit the change to the syscomments table but you'll have to reconfigure sql server to allow this and I am not sure if that's a good idea. Besides, you'll have to think about having to let sql server know the procedure changed so it'll recompile (in order to make sure it'll use the latest version). Instead you could try to have the contents of the procedure executed using sp_executesql, don't forget the drop and create commands though.
I would not recommend either one since you'll have some issues to consider prior actually doing the changes.|||btw: Pat's suggestion is way preferred over mine|||Hey look at that...a cross post :D
SQLTeam (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35267)
Check out the link in the link...let me know what you think...
modify ALIAS SQL Server Name with SQL 2005
Hello,
I would like create/modify/update the alias SQL server name on the SQL 2005 version.
I had a database mirroring on one base, and when the primary server fail, i would like my secondary alias become as the primary. I need this because of the application whom use my SQL server.
I tried with
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
But this way doesn't work...
Anyone got an idea to change alias SQL server in transact?
Thanks for your help!!!
Hello,
I've found my answer on this site
http://blogs.developpeur.org/christian/archive/2006/11/04/SQL-Server-_3A00_-Alias-de-serveurs.aspx
We can change the alias SQL Server 2005 directly in the registry.
See u ;)
modify ALIAS SQL Server Name with SQL 2005
Hello,
I would like create/modify/update the alias SQL server name on the SQL 2005 version.
I had a database mirroring on one base, and when the primary server fail, i would like my secondary alias become as the primary. I need this because of the application whom use my SQL server.
I tried with
sp_dropserver <old_name>
GO
sp_addserver <new_name>, local
GO
But this way doesn't work...
Anyone got an idea to change alias SQL server in transact?
Thanks for your help!!!
Hello,
I've found my answer on this site
http://blogs.developpeur.org/christian/archive/2006/11/04/SQL-Server-_3A00_-Alias-de-serveurs.aspx
We can change the alias SQL Server 2005 directly in the registry.
See u ;)
sqlModify a domain name Windows 2000 server and SQL server 2000
Hello.
We are using a stand alone Windows 2000 server with only SQL server 2000
running on it.
Now, these server is alone with his proper domain name.
This Windows 2000 server will join a new domain as a member.
So we will change his domain name.
We are only using TCPIP connection beetween clients apps and SQL server.
Is there any problem for the client apps, or SQL server himself.
Thanks in advance for your answers.
Jean Jacques SerpoulI am currently in the same process. If anyone has done this or knows of doc
s please let us know.
Thankyou,
Shawn
We are using a stand alone Windows 2000 server with only SQL server 2000
running on it.
Now, these server is alone with his proper domain name.
This Windows 2000 server will join a new domain as a member.
So we will change his domain name.
We are only using TCPIP connection beetween clients apps and SQL server.
Is there any problem for the client apps, or SQL server himself.
Thanks in advance for your answers.
Jean Jacques SerpoulI am currently in the same process. If anyone has done this or knows of doc
s please let us know.
Thankyou,
Shawn
Modify a domain name Windows 2000 server and SQL server 2000
Hello.
We are using a stand alone Windows 2000 server with only SQL server 2000
running on it.
Now, these server is alone with his proper domain name.
This windows 2000 server will join a new domain as a member.
So we will change his domain name.
We are only using TCPIP connection beetween clients apps and SQL server.
Is there any problem for the client apps, or SQL server himself.
Thanks in advance for your answers.
Jean Jacques SerpoulI am currently in the same process. If anyone has done this or knows of docs please let us know
Thankyou
Shawn
We are using a stand alone Windows 2000 server with only SQL server 2000
running on it.
Now, these server is alone with his proper domain name.
This windows 2000 server will join a new domain as a member.
So we will change his domain name.
We are only using TCPIP connection beetween clients apps and SQL server.
Is there any problem for the client apps, or SQL server himself.
Thanks in advance for your answers.
Jean Jacques SerpoulI am currently in the same process. If anyone has done this or knows of docs please let us know
Thankyou
Shawn
Modify a column from Nvarchar(50) to a DateTime
Hello,
I have a column that is a currently set as nvarchar(50) and is called DateEmployed.
There are over a hundred rows that contain dates which is in nvarchar format.
This column now needs to be changed to a DateTime datatype. (Don't ask me it was not set
to a dateTime when this was first designed - I wasn't here)
However, I have to change this column to a DateTime without destroying the data.
Is there any easy way to write some script or use studio management to change this.
Currently the data is displayed like this in this column dd/MM/yyyy i.e. 25/8/2007.
The method I am using to try and change this is by going to studio management clicking
modify on the column and changing the datatype from a nvarchar(50) to a DateTime.
I get this following error message:
- Unable to modify table.
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.
Any suggestions would be most grateful,
Thanks,
Steve
The error is because your data format is 'confusing' to SQL Server. The ISO standard is [ yyyy/mm/dd ]
First you need to change the data in the existing column to be of a format that will allow the column datatype to change.
This 'should' work for you (untested):
UPDATE MyTable
SET DateEmployed = convert( nvarchar(50), convert( datetime, DateEmployed, 103 ), 111 )
Then change the datatype.
Modify [Permission]
hi guys,
My clients cann't update the sql database through ASP scripts.
How can i give the permission to add and modify the rows and columns?
Thank youYou can use SP_ADDROLEMEMBER and give them db_datawriter fixed db role.
My clients cann't update the sql database through ASP scripts.
How can i give the permission to add and modify the rows and columns?
Thank youYou can use SP_ADDROLEMEMBER and give them db_datawriter fixed db role.
Modify /concatenate varbinary data in SQL Server 2005
I am trying to store large files /images in SQL Server 2005.
I have used varbinay data type for the table column. At front end i am storing value in blob and inserting record to this table. but SQL Server does not allow to insert directly blob data to varbinary . So we need to convert blob data to varbinary using convert() function. After conversion, check the data ,it gives some junk values. How do i insert blob data into table with column data type as varbinary ?
2) We are trying to send blob data to SQL server 2005 in chunks. How to do it if column in table is varbinary ?
Please advice.
Regards
Rithik
You can check the following article to get the idea.
Insert BLOB from ADO.NET
http://support.microsoft.com/kb/309158
Learn more facts about BLOB when you design database
http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=true
|||Hi,
Thanks for your advice.
We are using powerbuilder 10 as front end and sql server 2005 as database. We use updateblob statement to update blob data to varbinary column in SQL Server script as follows.
1)Script :
UpdateBlob r_test Set blob_data = :lblob_filedata Where file_id =21;
2)And we used stored procedure as follows:
CRAETE PROCEDURE [dbo].[R_UPLOAD_FILES_TO_DB]
(
@.V_SOURCE_DATA VARBINARY(MAX),
@.V_FILE_ID NUMERIC(8)
)
AS
UPDATE R_TEST
SET DATA = @.V_SOURCE_DATA WHERE FILE_ID = @.V_FILE_ID
Blob data can have text files,bitmap files ,or any other file having size less than 2 GB.
The Table R_test Design :
Create table r_test
(data varbinary(max),fle_id numeric(8))
But when we checked the data we found it's not same.
SQL Server varbinary column stored blob data in different format. Why the data appears different ?
Please advice
Regards
Rithik
|||I want to store binary data in database. I have byte array, but i am unable to insert that in database because i don't know how to insert variable name of type BYTE in sql insert query.my code is as follow
dim byteArray() as byte
strSQL = "INSERT INTO TEMP(TEMP_NAME,TEMP_BIN) VALUES('SANSHARK',?)"
how do i pass my byteArray in ?sql
Modifing XML document is too slow
I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but it works too slow.
Is there another way? Thanks' a lot.
I use such sequence T-SQL:
Declare
@.doc XML,
@.ixml nvarchar(4000)
SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)
SET @.ixml = 'SET @.Doc.modify(''
declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";
declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";
insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'
exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT
In order for me to help you troubleshoot the performance please explain the shape of your XML data and the scenario you are using XML in.
General notes.
If your scenario is collecting changes made to a set of tables and aggregating them into an XML document you may want to aggregate each change into a smaller XML instance in a dedicated XML column and then aggregate multiple instances into a bigger XML document upon retrieval.
If your scenario is adding data from database tables to an existing document on the client side you may retrieve your data from the server in XML form using FOR XML and then combine the new and the existing XML documents using XSLT.
If you need to add data from database tables to an XML document stored in an XML variable/parameter the most performing will be to compose a new instance, for example:
WITH XMLNAMESPACES(
'http://schemas.microsoft.com/office/infopath/2003/dataFormSolution' AS dfs,
'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)
SELECT @.doc =
(
SELECT
@.doc.query('dfs:myFields/dfs:dataFields/*') AS "dfs:dataFields",
(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "dfs:dataFields"
FOR XML PATH, TYPE, ROOT('dfs:myFields')
);
If reconstruction is too complex you can combine the old and new XML into a single instance, insert the new part into the appropriate place, and then delete the new part:
-- combine the old and the new XML
WITH XMLNAMESPACES(
'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)
SELECT @.doc =
(
SELECT
@.doc,
(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "Wrapper"
FOR XML PATH, TYPE
);
-- insert the new XML fragment into the appropriate place
SET @.doc = @.doc.modify('
declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";
insert /Wrapper/* into (dfs:myFields/dfs:dataFields)[1]
');
-- delete the new XML fragment
SET @.doc = @.doc.modify('delete /Wrapper');
Inserting bigger XML fragments using dynamic query with modify(‘insert…’) method on XML type is not recommended in terms of performance.
Best regards,
Eugene Kogan,
Technical Lead,
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
The code samples were not verified and may contain syntax errors.
Thanks’ a lot.
I use completely another way, simple serializing with some handwriting end tags, time was decreased from 2 minutes 38 seconds to 7 seconds.
Modifing XML document is too slow
I need insert many dates from tables to XML file, I use T-SQL, everything is OK, but it works too slow.
Is there another way? Thanks' a lot.
I use such sequence T-SQL:
Declare
@.doc XML,
@.ixml nvarchar(4000)
SELECT @.ixml=(SELECT * FROM SAMPLE "d:SAMPLE" for XML AUTO)
SET @.ixml = 'SET @.Doc.modify(''
declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";
declare namespace d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields";
insert ('+@.ixml+') into (dfs:myFields/dfs:dataFields)[1] '')'
exec sp_ExecuteSql @.STMT = @.ixml, @.PARAMS = N'@.DOC XML OUT', @.DOC = @.DOC OUT
In order for me to help you troubleshoot the performance please explain the shape of your XML data and the scenario you are using XML in.
General notes.
If your scenario is collecting changes made to a set of tables and aggregating them into an XML document you may want to aggregate each change into a smaller XML instance in a dedicated XML column and then aggregate multiple instances into a bigger XML document upon retrieval.
If your scenario is adding data from database tables to an existing document on the client side you may retrieve your data from the server in XML form using FOR XML and then combine the new and the existing XML documents using XSLT.
If you need to add data from database tables to an XML document stored in an XML variable/parameter the most performing will be to compose a new instance, for example:
WITH XMLNAMESPACES(
'http://schemas.microsoft.com/office/infopath/2003/dataFormSolution' AS dfs,
'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)
SELECT @.doc =
(
SELECT
@.doc.query('dfs:myFields/dfs:dataFields/*') AS "dfs:dataFields",
(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "dfs:dataFields"
FOR XML PATH, TYPE, ROOT('dfs:myFields')
);
If reconstruction is too complex you can combine the old and new XML into a single instance, insert the new part into the appropriate place, and then delete the new part:
-- combine the old and the new XML
WITH XMLNAMESPACES(
'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' AS d)
SELECT @.doc =
(
SELECT
@.doc,
(SELECT * FROM SAMPLE FOR XML RAW('d:SAMPLE'), TYPE) AS "Wrapper"
FOR XML PATH, TYPE
);
-- insert the new XML fragment into the appropriate place
SET @.doc = @.doc.modify('
declare namespace dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution";
insert /Wrapper/* into (dfs:myFields/dfs:dataFields)[1]
');
-- delete the new XML fragment
SET @.doc = @.doc.modify('delete /Wrapper');
Inserting bigger XML fragments using dynamic query with modify(‘insert…’) method on XML type is not recommended in terms of performance.
Best regards,
Eugene Kogan,
Technical Lead,
SQL Server Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
The code samples were not verified and may contain syntax errors.
Thanks’ a lot.
I use completely another way, simple serializing with some handwriting end tags, time was decreased from 2 minutes 38 seconds to 7 seconds.
Modifing values from one database to another
I have several databases in a merge replication.
there are two servers linked so i can send data from one to the other. I
mean Server Fenix is linked to Neptune. Neptune is replicating an Feniz is
the Replicator.
When one table in one of the remote computers is changed the trigger make
price =0. This trigger is NOT FOR REPLICATION, because it was causing to many
conflicts before, because it also executed a Store Procedure that change the
Price to some value.
My problem is that I have a store procedure that once this price=0 it has to
delete some data in one databes of NEPTUNE that is not being replicated.
The problem is that I get an error message saying:
SERVER NEPTUNE IS NOT CONFIGURED FOR DATA_ACCESS
So I try to link Neptun to one of those remote machines, but it is not
posible because they are replicating, I guess...
The way I try to reach Neptune si:
DELETE [NEPTUNE].LABORATORIO.DBO.TABLAPUNTOSCOMPRA
WHERE...
I do not know if this is too confusing...
Thanks a lot if some one can help me,
Lina
try this. sp_serveroption 'Neptune', 'data access',true
or
sp_serveroption 'Neptune', 'data access','true'
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
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:81870ED4-8413-46A0-A522-9E43B586F41C@.microsoft.com...
>I have several databases in a merge replication.
> there are two servers linked so i can send data from one to the other. I
> mean Server Fenix is linked to Neptune. Neptune is replicating an Feniz is
> the Replicator.
> When one table in one of the remote computers is changed the trigger make
> price =0. This trigger is NOT FOR REPLICATION, because it was causing to
> many
> conflicts before, because it also executed a Store Procedure that change
> the
> Price to some value.
> My problem is that I have a store procedure that once this price=0 it has
> to
> delete some data in one databes of NEPTUNE that is not being replicated.
> The problem is that I get an error message saying:
> SERVER NEPTUNE IS NOT CONFIGURED FOR DATA_ACCESS
> So I try to link Neptun to one of those remote machines, but it is not
> posible because they are replicating, I guess...
> The way I try to reach Neptune si:
> DELETE [NEPTUNE].LABORATORIO.DBO.TABLAPUNTOSCOMPRA
> WHERE...
> I do not know if this is too confusing...
> Thanks a lot if some one can help me,
> Lina
|||Thank you, Hilary.
I have this two sentences inside the store procedure and they do no work.
exec sp_serveroption 'NEPTUNO','data access','true'
EXEC sp_serveroption 'NEPTUNO', 'collation compatible', 'true'
"Hilary Cotter" wrote:
> try this. sp_serveroption 'Neptune', 'data access',true
> or
> sp_serveroption 'Neptune', 'data access','true'
>
> --
> 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
> "Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
> news:81870ED4-8413-46A0-A522-9E43B586F41C@.microsoft.com...
>
>
there are two servers linked so i can send data from one to the other. I
mean Server Fenix is linked to Neptune. Neptune is replicating an Feniz is
the Replicator.
When one table in one of the remote computers is changed the trigger make
price =0. This trigger is NOT FOR REPLICATION, because it was causing to many
conflicts before, because it also executed a Store Procedure that change the
Price to some value.
My problem is that I have a store procedure that once this price=0 it has to
delete some data in one databes of NEPTUNE that is not being replicated.
The problem is that I get an error message saying:
SERVER NEPTUNE IS NOT CONFIGURED FOR DATA_ACCESS
So I try to link Neptun to one of those remote machines, but it is not
posible because they are replicating, I guess...
The way I try to reach Neptune si:
DELETE [NEPTUNE].LABORATORIO.DBO.TABLAPUNTOSCOMPRA
WHERE...
I do not know if this is too confusing...
Thanks a lot if some one can help me,
Lina
try this. sp_serveroption 'Neptune', 'data access',true
or
sp_serveroption 'Neptune', 'data access','true'
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
"Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
news:81870ED4-8413-46A0-A522-9E43B586F41C@.microsoft.com...
>I have several databases in a merge replication.
> there are two servers linked so i can send data from one to the other. I
> mean Server Fenix is linked to Neptune. Neptune is replicating an Feniz is
> the Replicator.
> When one table in one of the remote computers is changed the trigger make
> price =0. This trigger is NOT FOR REPLICATION, because it was causing to
> many
> conflicts before, because it also executed a Store Procedure that change
> the
> Price to some value.
> My problem is that I have a store procedure that once this price=0 it has
> to
> delete some data in one databes of NEPTUNE that is not being replicated.
> The problem is that I get an error message saying:
> SERVER NEPTUNE IS NOT CONFIGURED FOR DATA_ACCESS
> So I try to link Neptun to one of those remote machines, but it is not
> posible because they are replicating, I guess...
> The way I try to reach Neptune si:
> DELETE [NEPTUNE].LABORATORIO.DBO.TABLAPUNTOSCOMPRA
> WHERE...
> I do not know if this is too confusing...
> Thanks a lot if some one can help me,
> Lina
|||Thank you, Hilary.
I have this two sentences inside the store procedure and they do no work.
exec sp_serveroption 'NEPTUNO','data access','true'
EXEC sp_serveroption 'NEPTUNO', 'collation compatible', 'true'
"Hilary Cotter" wrote:
> try this. sp_serveroption 'Neptune', 'data access',true
> or
> sp_serveroption 'Neptune', 'data access','true'
>
> --
> 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
> "Lina Manjarres" <LinaManjarres@.discussions.microsoft.com> wrote in message
> news:81870ED4-8413-46A0-A522-9E43B586F41C@.microsoft.com...
>
>
Modifing the row that invokes a trigger from within that trigger
When a row gets modified and it invokes a trigger, we would like to be
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:
CREATE TRIGGER trTBL ON TBL
FOR UPDATE, INSERT, DELETE
as
update TBL
set fld = 'value'
from inserted, TBL
where inserted.id= TBL.id
...
This work fine but it seems like it could be optimized. Clearly we are
having to scan the entire table again to update the row. But shouldn't
the trigger already know which row invoked it. Do we have to scan the
table again for this row or is their some syntax that allows us to
update the row that invoked the trigger. If not, why. It seems like
this would be a fairly common task. Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
The trigger does "know" which rows have been updated, they are in the
inserted recordset.
If you want to eliminate table scans put an index on the "joining"
columns between inserted and the updated table: in your case the [id]
column in the TBL table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQmgkHIechKqOuFEgEQKj0ACg/gThmkK3I5FVs014i96EY1KEqyEAniEQ
TeGhjpWGcoYjj51fomXBxth4
=ilXF
--END PGP SIGNATURE--
nosbtr1 wrote:
> When a row gets modified and it invokes a trigger, we would like to be
> able to update the row that was modified inside the trigger. This is
> (basically) how we are doing it now:
> CREATE TRIGGER trTBL ON TBL
> FOR UPDATE, INSERT, DELETE
> as
> update TBL
> set fld = 'value'
> from inserted, TBL
> where inserted.id= TBL.id
> ...
> This work fine but it seems like it could be optimized. Clearly we are
> having to scan the entire table again to update the row. But shouldn't
> the trigger already know which row invoked it. Do we have to scan the
> table again for this row or is their some syntax that allows us to
> update the row that invoked the trigger. If not, why. It seems like
> this would be a fairly common task. Thanks.|||On 21 Apr 2005 14:11:20 -0700, nosbtr1 wrote:
>When a row gets modified and it invokes a trigger, we would like to be
>able to update the row that was modified inside the trigger. This is
>(basically) how we are doing it now:
>CREATE TRIGGER trTBL ON TBL
>FOR UPDATE, INSERT, DELETE
>as
>update TBL
> set fld = 'value'
>from inserted, TBL
> where inserted.id= TBL.id
>...
>This work fine but it seems like it could be optimized. Clearly we are
>having to scan the entire table again to update the row. But shouldn't
>the trigger already know which row invoked it. Do we have to scan the
>table again for this row or is their some syntax that allows us to
>update the row that invoked the trigger. If not, why. It seems like
>this would be a fairly common task. Thanks.
Hi nosbtr1,
1. There is no "special syntax" to find the rowS (note the plural)
affected by the statement that fired the trigger, other than what you
are already using: the inserted and deleted pseudo-tables.
2. Your statement that this will cause a table scan is incorrect - if
you have a PRIMARY KEY constraint, a UNIQUE constraint or an INDEX
defined for the id column in the table, SQL Server can use an index seek
(followed by a bookmark lookup if the index used is nonclustered).
3. Your trigger won't do anything on delete operations. First because
the inserted pseudotable is always empty on a delete, and second becuase
the rows you are attempting to modify are already removed from the
table.
4. Why not rewrite the UPDATE statement above to the ANSI-compliant
alternative:
UPDATE Tbl
SET Fld = 'value'
WHERE EXISTS (SELECT *
FROM inserted
WHERE inserted.id = Tbl.id)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||You could use an instead of trigger, in this way you actually control
the way the data is updated in the table.
the code is much more complex though|||nosbtr1 (nosbtr1@.yahoo.com) writes:
> When a row gets modified and it invokes a trigger, we would like to be
> able to update the row that was modified inside the trigger. This is
> (basically) how we are doing it now:
> CREATE TRIGGER trTBL ON TBL
> FOR UPDATE, INSERT, DELETE
> as
> update TBL
> set fld = 'value'
> from inserted, TBL
> where inserted.id= TBL.id
> ...
> This work fine but it seems like it could be optimized. Clearly we are
> having to scan the entire table again to update the row. But shouldn't
> the trigger already know which row invoked it.
A trigger fires once per statement, and thus many rows may be affected.
The main performance thing with triggers is that one should be aware of
that the inserted/deleted tables are fairly slow. If you trigger makes
frequent references to these tables, it's a good idea to insert the data
into the pseudo tables into table variables, and work with these instead.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Modifing the Model DB
Could anyone please advise how to modify the location of
the datafile and logfile of the model database?
I need to change them from drive C to drive D.
Thank you.Hi Nathalie,
See the following knowledge base article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Nathalie" <nathalbr@.cellcom.co.il> wrote in message
news:7e5701c35a7f$5742ae00$a001280a@.phx.gbl...
> Could anyone please advise how to modify the location of
> the datafile and logfile of the model database?
> I need to change them from drive C to drive D.
> Thank you.sql
the datafile and logfile of the model database?
I need to change them from drive C to drive D.
Thank you.Hi Nathalie,
See the following knowledge base article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224071
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Nathalie" <nathalbr@.cellcom.co.il> wrote in message
news:7e5701c35a7f$5742ae00$a001280a@.phx.gbl...
> Could anyone please advise how to modify the location of
> the datafile and logfile of the model database?
> I need to change them from drive C to drive D.
> Thank you.sql
Modifing Data in SQL
Hello, and sorry for the vauge title.
Basically, I am building a web application where people can enter in raffles. So I set up a SQL DataBase, where I have a table for each raffel going on. In each, I have a UserName collum, and a ID collum, the ID being the key collum. On my page, I basically have a TextBox and a Button.
I know I add a SqlDataSorce to the page, and use a INSERT command, however I am not sure how to parse this in a way to make it so that when you type your UserName into the TextBox, and then click the submit button, it will create a row using the UserName.
Thanks for the help!
I'm assuming that your key column is an identity column, so we don't have to create a new key.
You have a seperate table for each raffle, and each one has its own username column? I suspect that you're better off with one table for all raffles, and a key field for the raffle that a row is for. That way you don't have to create a new table every time someone does a raffle.
Let's assume that the function GetTable() returns the name of the table for the raffle the user's signing up for.
source.InsertCommand = "Insert into " + GetTable() + "(UserName) Values(@.UserName)"
Then bind the data source to your textbox.
|||Thanks, but I don't get the last part, "Then bind the data source to your textbox." Because if you do that, then it won't interact with the database.
And if you ment it the other way around, (Bind the textbox to the data source), I can't. You can't bind textboxes to datasources.
Help?
|||I don't do much with SqlDataSources. If it were a straight SqlCommand, then inthe click event of your button, I'd have
cmd.Parameters["@.UserName"].value = txtUserName.Text;
cmd.ExecuteNonQuery();
|||
Sorry if this is really obvious, and I'm just stupid, but I can't figure out what to use for "cmd". I think you are refering to the SqlCommand, (Insert), but that doesn't work. (I've also tried InsertCommand).
Sorry that I can't get this.
modifing a running trace
Is there a way to modify a running trace without stopping and re-createing it?
I have a trace running for SOX Audting and I need to add a new user to be
traced.
I created the trace in a script not the GUI in EM.
When I created it I used the following code to ignore this user and now I
need to add him to the trace:
exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
I have the trace writing out to files, and I am up to approx #1000. If I
stop the trace I will need to move the file out of the directory, and
re-start at #1. I am hoping to avoid renumbering.
TIA,
Joe
jaylou wrote:
> Is there a way to modify a running trace without stopping and
> re-createing it?
> I have a trace running for SOX Audting and I need to add a new user
> to be traced.
> I created the trace in a script not the GUI in EM.
> When I created it I used the following code to ignore this user and
> now I need to add him to the trace:
> exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
> I have the trace writing out to files, and I am up to approx #1000.
> If I stop the trace I will need to move the file out of the
> directory, and re-start at #1. I am hoping to avoid renumbering.
> TIA,
> Joe
Why not use an auto-numbering scheme for the trace file names or just
add the current date/time to the suffix of the trace file name. You need
to stop the trace in order to change its filter criteria.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks,
That was what I was afraid of
Joe
"David Gugick" wrote:
> jaylou wrote:
> Why not use an auto-numbering scheme for the trace file names or just
> add the current date/time to the suffix of the trace file name. You need
> to stop the trace in order to change its filter criteria.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Hi Jaylou
I have a system that takes care of this & various other SQL Profiler
management issues. It's soon to be released to market but is available for
testing now if you're interested at www.gajsoftware.com (to get the eval,
you simply sig up, activate your account & visit the "editions" page).
Basically it provides for multi-server, schedulable, template based profiler
management. If you change the profiler template (via an easy to use gui),
the background trace management service will automatically re-configure the
trace for you & take care of the file rollover issues.
Regards,
Greg Linwood
SQL Server MVP
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:724A9BBE-FCFC-4401-B42A-91CFF5EA5CD5@.microsoft.com...
> Is there a way to modify a running trace without stopping and re-createing
> it?
> I have a trace running for SOX Audting and I need to add a new user to be
> traced.
> I created the trace in a script not the GUI in EM.
> When I created it I used the following code to ignore this user and now I
> need to add him to the trace:
> exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
> I have the trace writing out to files, and I am up to approx #1000. If I
> stop the trace I will need to move the file out of the directory, and
> re-start at #1. I am hoping to avoid renumbering.
> TIA,
> Joe
I have a trace running for SOX Audting and I need to add a new user to be
traced.
I created the trace in a script not the GUI in EM.
When I created it I used the following code to ignore this user and now I
need to add him to the trace:
exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
I have the trace writing out to files, and I am up to approx #1000. If I
stop the trace I will need to move the file out of the directory, and
re-start at #1. I am hoping to avoid renumbering.
TIA,
Joe
jaylou wrote:
> Is there a way to modify a running trace without stopping and
> re-createing it?
> I have a trace running for SOX Audting and I need to add a new user
> to be traced.
> I created the trace in a script not the GUI in EM.
> When I created it I used the following code to ignore this user and
> now I need to add him to the trace:
> exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
> I have the trace writing out to files, and I am up to approx #1000.
> If I stop the trace I will need to move the file out of the
> directory, and re-start at #1. I am hoping to avoid renumbering.
> TIA,
> Joe
Why not use an auto-numbering scheme for the trace file names or just
add the current date/time to the suffix of the trace file name. You need
to stop the trace in order to change its filter criteria.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Thanks,
That was what I was afraid of
Joe
"David Gugick" wrote:
> jaylou wrote:
> Why not use an auto-numbering scheme for the trace file names or just
> add the current date/time to the suffix of the trace file name. You need
> to stop the trace in order to change its filter criteria.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
|||Hi Jaylou
I have a system that takes care of this & various other SQL Profiler
management issues. It's soon to be released to market but is available for
testing now if you're interested at www.gajsoftware.com (to get the eval,
you simply sig up, activate your account & visit the "editions" page).
Basically it provides for multi-server, schedulable, template based profiler
management. If you change the profiler template (via an easy to use gui),
the background trace management service will automatically re-configure the
trace for you & take care of the file rollover issues.
Regards,
Greg Linwood
SQL Server MVP
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:724A9BBE-FCFC-4401-B42A-91CFF5EA5CD5@.microsoft.com...
> Is there a way to modify a running trace without stopping and re-createing
> it?
> I have a trace running for SOX Audting and I need to add a new user to be
> traced.
> I created the trace in a script not the GUI in EM.
> When I created it I used the following code to ignore this user and now I
> need to add him to the trace:
> exec sp_trace_setfilter @.TraceID, 11, 0, 7, N'jsisserly'
> I have the trace writing out to files, and I am up to approx #1000. If I
> stop the trace I will need to move the file out of the directory, and
> re-start at #1. I am hoping to avoid renumbering.
> TIA,
> Joe
Modified Stored Procedure Scripts Wrong ANSI Settings
Query Analyzer for 2000 was smart enough to realize that a stored procedure was created with the ANSI settings like such:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
go
However, when I use SSMS to modify a stored procedure from the context menu in the object explorer, instead get:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
Which is not even the default for my connection.
I'm working on a legacy code base with tons of double quoted strings, so I really need the ANSI settings to stay where they were without fighting the SQL editor about it.
Any suggestions? Is this a bug? I'm using SQL2005 RTM.
Matthew Martin
I would do this from your script in the query pane:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO
CREATE PROCEDURE <yourprocedure>
...
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO
Should be something like this at the end.
HTH, Jens Suessmeyer.
|||True, I can revert to OSQL and generate the script correctly with my keyboard, the problem is that EM used to be able to script out a stored procedure with the correct settings (namely the settings that were in effect when the stored procedure was last altered), now with SSMS, right clicking on a stored procedure and selecting modify will script out a ALTER PROCEDURE script with the wrong settings.
Either I've hit a SSMS bug or I haven't found the 'Make-it-work-the-way-it-used-to' check box. I'm hoping it is the later.
Matthew Martin
|||This is a known issue in SSMS. It will be fixed in SP1.
Modified Report not rendering over an existing one
Hi,
Is it posible to overwwrite an existing report (in the same folder) with a
newly modified one. I recently made some changes to an existing report,
deployed it to the same report folder in the report manager but the new
changes failed to overwrite the old one. May be I should delete the old one?
Thx
ChuckMost likely session snapshots or execution caching were involved when you
did not see the updated report on the first rendering in report manager.
Hitting Ctrl+F5 should re-render the report.
Note: when republishing over an existing report, report parameter
information and data source information will *not* be overwritten on the
report server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:8CB60C27-A7D3-43E9-8777-2140EF8BE85A@.microsoft.com...
> Hi,
> Is it posible to overwwrite an existing report (in the same folder) with a
> newly modified one. I recently made some changes to an existing report,
> deployed it to the same report folder in the report manager but the new
> changes failed to overwrite the old one. May be I should delete the old
one?
> Thx
> Chuck|||Thx Robert for answering my question however, I did try hitting Ctrl + F5 to
no avail; would it be possible to have the execution cach flushed in order to
render the updated report?
"Robert Bruckner [MSFT]" wrote:
> Most likely session snapshots or execution caching were involved when you
> did not see the updated report on the first rendering in report manager.
> Hitting Ctrl+F5 should re-render the report.
> Note: when republishing over an existing report, report parameter
> information and data source information will *not* be overwritten on the
> report server.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:8CB60C27-A7D3-43E9-8777-2140EF8BE85A@.microsoft.com...
> > Hi,
> > Is it posible to overwwrite an existing report (in the same folder) with a
> > newly modified one. I recently made some changes to an existing report,
> > deployed it to the same report folder in the report manager but the new
> > changes failed to overwrite the old one. May be I should delete the old
> one?
> >
> > Thx
> >
> > Chuck
>
>
Is it posible to overwwrite an existing report (in the same folder) with a
newly modified one. I recently made some changes to an existing report,
deployed it to the same report folder in the report manager but the new
changes failed to overwrite the old one. May be I should delete the old one?
Thx
ChuckMost likely session snapshots or execution caching were involved when you
did not see the updated report on the first rendering in report manager.
Hitting Ctrl+F5 should re-render the report.
Note: when republishing over an existing report, report parameter
information and data source information will *not* be overwritten on the
report server.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:8CB60C27-A7D3-43E9-8777-2140EF8BE85A@.microsoft.com...
> Hi,
> Is it posible to overwwrite an existing report (in the same folder) with a
> newly modified one. I recently made some changes to an existing report,
> deployed it to the same report folder in the report manager but the new
> changes failed to overwrite the old one. May be I should delete the old
one?
> Thx
> Chuck|||Thx Robert for answering my question however, I did try hitting Ctrl + F5 to
no avail; would it be possible to have the execution cach flushed in order to
render the updated report?
"Robert Bruckner [MSFT]" wrote:
> Most likely session snapshots or execution caching were involved when you
> did not see the updated report on the first rendering in report manager.
> Hitting Ctrl+F5 should re-render the report.
> Note: when republishing over an existing report, report parameter
> information and data source information will *not* be overwritten on the
> report server.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:8CB60C27-A7D3-43E9-8777-2140EF8BE85A@.microsoft.com...
> > Hi,
> > Is it posible to overwwrite an existing report (in the same folder) with a
> > newly modified one. I recently made some changes to an existing report,
> > deployed it to the same report folder in the report manager but the new
> > changes failed to overwrite the old one. May be I should delete the old
> one?
> >
> > Thx
> >
> > Chuck
>
>
modified replicated table without drop replication first
hi :
i set up a merge replication on sql server 2000.
how to modified replicated tables without dropping replication ?
Cheers
Nick
Nick,
sp_repladdcolumn and sp_repldropcolumn can be used.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||As a followup, what if a column is modified? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23$roTGN%23FHA.3308@.TK2MSFTNGP11.phx.gbl...
> Nick,
> sp_repladdcolumn and sp_repldropcolumn can be used.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
please take a look at this article:
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks. i read the article but I'm still not clear on how to handle. We
are using 1 Publisher and all anonymous (laptop) subscribers and are using
Merge Subscriptions. We make changes to tables failrly often. The database
is not real large (400MB) so I want to use the easiest possible way. What
do you suggest? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OIJ9XJP%23FHA.1988@.TK2MSFTNGP12.phx.gbl...
> David,
> please take a look at this article:
> http://www.replicationanswers.com/AddColumn.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||David,
if you can afford it, the simplest method is probably to reinitialize.
Either way, you're safest by synchronizing all subscribers before undergoing
these changes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
i set up a merge replication on sql server 2000.
how to modified replicated tables without dropping replication ?
Cheers
Nick
Nick,
sp_repladdcolumn and sp_repldropcolumn can be used.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||As a followup, what if a column is modified? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23$roTGN%23FHA.3308@.TK2MSFTNGP11.phx.gbl...
> Nick,
> sp_repladdcolumn and sp_repldropcolumn can be used.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
please take a look at this article:
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks. i read the article but I'm still not clear on how to handle. We
are using 1 Publisher and all anonymous (laptop) subscribers and are using
Merge Subscriptions. We make changes to tables failrly often. The database
is not real large (400MB) so I want to use the easiest possible way. What
do you suggest? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OIJ9XJP%23FHA.1988@.TK2MSFTNGP12.phx.gbl...
> David,
> please take a look at this article:
> http://www.replicationanswers.com/AddColumn.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||David,
if you can afford it, the simplest method is probably to reinitialize.
Either way, you're safest by synchronizing all subscribers before undergoing
these changes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Modified image filegroup never get saved
I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peter
Hi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John
|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
>
>
>
|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John
|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peter
Hi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John
|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
>
>
>
|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John
|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John
Modified image filegroup never get saved
I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.Which version of SQL 2005 are you using?
> > Problem:
> > I created a table and gave the properties "Text/Image Filegroup" the
> > value of the secondary filegroup, gave the properties "Filegroup or
> > Partition Scheme Name" the value of primary file group. After I save
> > it. Then I open it for edit again, the value of the properties
> > "Text/Image Filegroup" was not showing secondary, instead, it show
> > primary.I assume this was through SSMS? As far as I can see there is not a way of
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
> > I'd try a few times to change it, from primary to secondary, then save.
> > After that open for modify again, it still revert back to primary
> > filegroup. Seems like the changes I made to that particular properties
> > can't be saved.Creating the table with T-SQL seems to be ok!
>
> > Anyone can help?
> > Thanks in advance.
> > wodoy.peterJohn|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm using SQL Server 2005 SP1 developer edition.
> > The complete version name and number: Microsoft SQL Server 2005 -
> > 9.00.2047.00
> > Is there any reason behind MS make the properties editable in user
> > interface only, but infact, it can't be saved/changed through the
> > SSMS?This would be one for the program group! I would guess one reason would be
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.Which version of SQL 2005 are you using?
> > Problem:
> > I created a table and gave the properties "Text/Image Filegroup" the
> > value of the secondary filegroup, gave the properties "Filegroup or
> > Partition Scheme Name" the value of primary file group. After I save
> > it. Then I open it for edit again, the value of the properties
> > "Text/Image Filegroup" was not showing secondary, instead, it show
> > primary.I assume this was through SSMS? As far as I can see there is not a way of
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonly
> in SSMS (on my version (SP1))
>
> > I'd try a few times to change it, from primary to secondary, then save.
> > After that open for modify again, it still revert back to primary
> > filegroup. Seems like the changes I made to that particular properties
> > can't be saved.Creating the table with T-SQL seems to be ok!
>
> > Anyone can help?
> > Thanks in advance.
> > wodoy.peterJohn|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
> > I'm using SQL Server 2005 SP1 developer edition.
> > The complete version name and number: Microsoft SQL Server 2005 -
> > 9.00.2047.00
> > Is there any reason behind MS make the properties editable in user
> > interface only, but infact, it can't be saved/changed through the
> > SSMS?This would be one for the program group! I would guess one reason would be
> because once the table has data it would be a very expensive thing to change
> which could cause severe problems. But then if there is no data in the table
> I don't see why it can't be changed. You may want to log this athttps://connect.microsoft.com/SQLServer/Feedback
> John
Modified image filegroup never get saved
I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
>
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonl
y
> in SSMS (on my version (SP1))
>
>
>
>
>
>|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
>
> because once the table has data it would be a very expensive thing to chan
ge
> which could cause severe problems. But then if there is no data in the tab
le
> I don't see why it can't be changed. You may want to log this athttps://co
nnect.microsoft.com/SQLServer/Feedback
> John
Problem:
I created a table and gave the properties "Text/Image Filegroup" the
value of the secondary filegroup, gave the properties "Filegroup or
Partition Scheme Name" the value of primary file group. After I save
it. Then I open it for edit again, the value of the properties
"Text/Image Filegroup" was not showing secondary, instead, it show
primary.
I'd try a few times to change it, from primary to secondary, then save.
After that open for modify again, it still revert back to primary
filegroup. Seems like the changes I made to that particular properties
can't be saved.
Anyone can help?
Thanks in advance.
wodoy.peterHi
"wodoy.peter" wrote:
> I'm testing out the SQL Server 2005 filegroup for Text/Image Filegroup.
>
Which version of SQL 2005 are you using?
> Problem:
> I created a table and gave the properties "Text/Image Filegroup" the
> value of the secondary filegroup, gave the properties "Filegroup or
> Partition Scheme Name" the value of primary file group. After I save
> it. Then I open it for edit again, the value of the properties
> "Text/Image Filegroup" was not showing secondary, instead, it show
> primary.
I assume this was through SSMS? As far as I can see there is not a way of
specify a different location for an image column is to script the table.
Where were you changing it? Table properties for text filegroup is readonly
in SSMS (on my version (SP1))
> I'd try a few times to change it, from primary to secondary, then save.
> After that open for modify again, it still revert back to primary
> filegroup. Seems like the changes I made to that particular properties
> can't be saved.
Creating the table with T-SQL seems to be ok!
> Anyone can help?
> Thanks in advance.
>
> wodoy.peter
>
John|||I'm using SQL Server 2005 SP1 developer edition.
The complete version name and number: Microsoft SQL Server 2005 -
9.00.2047.00
Is there any reason behind MS make the properties editable in user
interface only, but infact, it can't be saved/changed through the
SSMS?
wodoy.peter
On Jan 28, 2:26 am, John Bell <jbellnewspo...@.hotmail.com> wrote:[vbcol=seagreen]
> Hi
> "wodoy.peter" wrote:
>
> specify a different location for an image column is to script the table.
> Where were you changing it? Table properties for text filegroup is readonl
y
> in SSMS (on my version (SP1))
>
>
>
>
>
>|||Hi
"wodoy.peter" wrote:
> I'm using SQL Server 2005 SP1 developer edition.
> The complete version name and number: Microsoft SQL Server 2005 -
> 9.00.2047.00
> Is there any reason behind MS make the properties editable in user
> interface only, but infact, it can't be saved/changed through the
> SSMS?
This would be one for the program group! I would guess one reason would be
because once the table has data it would be a very expensive thing to change
which could cause severe problems. But then if there is no data in the table
I don't see why it can't be changed. You may want to log this at
https://connect.microsoft.com/SQLServer/Feedback
John|||Thanks.
On Jan 28, 4:06 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "wodoy.peter" wrote:
>
> because once the table has data it would be a very expensive thing to chan
ge
> which could cause severe problems. But then if there is no data in the tab
le
> I don't see why it can't be changed. You may want to log this athttps://co
nnect.microsoft.com/SQLServer/Feedback
> John
modified date on table after alter command
Using SQL Server 2000 - Trying to find a modified date for table after
issuing an alter table command. Sysobjects has crdate I can not find modifed
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.
There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shah
issuing an alter table command. Sysobjects has crdate I can not find modifed
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.
There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shah
modified date on table after alter command
Using SQL Server 2000 - Trying to find a modified date for table after
issuing an alter table command. Sysobjects has crdate I can not find modife
d
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shahsql
issuing an alter table command. Sysobjects has crdate I can not find modife
d
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shahsql
modified date on table after alter command
Using SQL Server 2000 - Trying to find a modified date for table after
issuing an alter table command. Sysobjects has crdate I can not find modifed
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shah
issuing an alter table command. Sysobjects has crdate I can not find modifed
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shah
Modified Date of a file
Hi All,
Think this must be pretty easy to do but can't find the commands anywhere... how can i use T-SQL to find the modified date of a particular file (say c:\temp\test.out) ?
Thanks
JohnYou could use extended sp xp_getfiledetails or xp_cmdshell to check info about file. If you need use this information late - save it in table.
insert mytable--table has to have structure according to sp recordset.
master..xp_cmdshell "dir c:\myfile.zip"
insert mytable--table has to have structure according to sp recordset.
master..xp_getfiledetails "c:\myfile.zip"|||Thanks Snail, worked a treat
Think this must be pretty easy to do but can't find the commands anywhere... how can i use T-SQL to find the modified date of a particular file (say c:\temp\test.out) ?
Thanks
JohnYou could use extended sp xp_getfiledetails or xp_cmdshell to check info about file. If you need use this information late - save it in table.
insert mytable--table has to have structure according to sp recordset.
master..xp_cmdshell "dir c:\myfile.zip"
insert mytable--table has to have structure according to sp recordset.
master..xp_getfiledetails "c:\myfile.zip"|||Thanks Snail, worked a treat
Modified Date For Stored Procedures
In SQL Server is there a way to know when a procedure was last
modified? I only see the "Create Date" column on the Enterprise
Manager.
modified? I only see the "Create Date" column on the Enterprise
Manager.
Thanks Experts!jjone99 (jjone99@.hotmail.com) writes:
> In SQL Server is there a way to know when a procedure was last
> modified? I only see the "Create Date" column on the Enterprise
> Manager.
No, in SQL 2000 there is not.
This is addressed in the next version of SQL Server, currently in beta.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Subscribe to:
Posts (Atom)