Modify data structure

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

Modify Data Source Or CataLog On Data Source

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

Modify data in SQL Server with access mdb

hello

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

This table has 40 rows and 3 primary key.

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

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

hope this helps

Originally posted by anakin_ch
hello

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

This table has 40 rows and 3 primary key.

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

thank you for response

modify constratints

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

modify column output

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

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

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

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

Code Snippet

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

|||

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

Jens K. Suessmeyer

http://www.sqlserver2005.de

Modify column order

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

modify column in tsql

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

---

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

---

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

---

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

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

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


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

---

--=_NextPart_000_002C_01C36020.075903C0--sql

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

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...

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 ;)

sql

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 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

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.

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...
>
>