EASY! Trim string?

I have values in a matrix takin too much space. Since they are just used as the matrix column header, cant i trim it or something. To display just the first 3 letters of the the attribute, and not the whole string? Whats the code for that?

=Fields!Collision_Type.Value thats my field right now.

Please help! thanks!

=LEFT(Fields!Collision_Type.Value,1,3) I believe. Or some variant of that syntax.|||

I believe it should be Mid(Fields!Collision_Type.Value,1,3)

|||

Yes, you can change it to this to always show the first 3 characters:

=Left(Fields!Collision_Type.Value, 3)

Jarret

|||Thanks for the quick response, this worked great!|||This one worked as well, hum i wonder what the difference is? But they both seem to display the same. Thanks!|||

There is no difference really, the Mid function allows you to give it a starting position and how many characters to return.

Jarret

Easy way to view DB

Hi,
I have several DB with 80+ tables in each. I would like to find out what is
the best method to learn and view the relationships of these tables. I made
a SQL diagram of all the tables but since there are so many tables, it is
not easy to view the 'big picture'. Does anyone have any suggestions or may
be a tool that shows DBs in a better view?
Thank you.Greg has a nice product that would serve you well here.
http://www.ag-software.com/ags_scribe_index.aspx
--
-oj
RAC & QALite!
http://www.rac4sql.net
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:%23doHPiayDHA.3772@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have several DB with 80+ tables in each. I would like to find out what
is
> the best method to learn and view the relationships of these tables. I
made
> a SQL diagram of all the tables but since there are so many tables, it is
> not easy to view the 'big picture'. Does anyone have any suggestions or
may
> be a tool that shows DBs in a better view?
> Thank you.
>

Easy way to update multiple databases

SQL 2K, Win 2K
My company is in the process of developing a new app that will have a lot of
client specific databases (30 to 50) with identical schemas and stored
procedures. My job as the DBA will be to make the schema and SP changes to
all of these when we have updates.
I have a similar app now that I have a script with several Use Database
commands and I just paste the changes in between the Use Database commands,
but I only have to do this for a few databases.
Does anybody know of a way to automate this with scripts or a tool to apply
the schema and SO change to all user databases on a server.
Thanks.
MikePerhaps DB Ghost would fill your needs.
www.dbghost.com
or
http://www.innovartis.co.uk/Home.aspx
-- Keith
"Mike" <Mike@.Comcast.net> wrote in message =news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> > My company is in the process of developing a new app that will have a =lot of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP =changes to
> all of these when we have updates.
> > I have a similar app now that I have a script with several Use =Database
> commands and I just paste the changes in between the Use Database =commands,
> but I only have to do this for a few databases.
> > Does anybody know of a way to automate this with scripts or a tool to =apply
> the schema and SO change to all user databases on a server.
> > Thanks.
> > Mike
> >|||Hi
If you are using a version control system to maintain your database code.
The most obvious way to upgrade would be use scripts called from osql/isql
or possibly DMO.
John
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||Check out if www.red-gate.com or www.dbghost.com does what you want.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||I wrote a little vb app that gets a list of db's to apply
changes to. you just paste the SQL syntax (ie "Alter
Table x add constraint y...") and click go, and it runs
the command on all dbs. I populate a simple table w/ the
names of db's to run the command against.
Also, I put together an app that uses a template database
(with the tables, sp's, triggers, that you want), and
pushes the schema out to a list of databases (in my case,
that list is in a table).
If you want the VB code for either, just let me know.
>--Original Message--
>SQL 2K, Win 2K
>My company is in the process of developing a new app that
will have a lot of
>client specific databases (30 to 50) with identical
schemas and stored
>procedures. My job as the DBA will be to make the schema
and SP changes to
>all of these when we have updates.
> I have a similar app now that I have a script with
several Use Database
>commands and I just paste the changes in between the Use
Database commands,
>but I only have to do this for a few databases.
>Does anybody know of a way to automate this with scripts
or a tool to apply
>the schema and SO change to all user databases on a
server.
>Thanks.
>Mike
>
>.
>|||I would appreciate it if you could share the app with me. It doesn't look
like DBGhost or SQL Compare will do it.
Thanks.
Mike
"Gene Daigle" <anonymous@.discussions.microsoft.com> wrote in message
news:024101c3bb73$7b7e3e10$a401280a@.phx.gbl...
> I wrote a little vb app that gets a list of db's to apply
> changes to. you just paste the SQL syntax (ie "Alter
> Table x add constraint y...") and click go, and it runs
> the command on all dbs. I populate a simple table w/ the
> names of db's to run the command against.
> Also, I put together an app that uses a template database
> (with the tables, sp's, triggers, that you want), and
> pushes the schema out to a list of databases (in my case,
> that list is in a table).
> If you want the VB code for either, just let me know.
>
> >--Original Message--
> >SQL 2K, Win 2K
> >
> >My company is in the process of developing a new app that
> will have a lot of
> >client specific databases (30 to 50) with identical
> schemas and stored
> >procedures. My job as the DBA will be to make the schema
> and SP changes to
> >all of these when we have updates.
> >
> > I have a similar app now that I have a script with
> several Use Database
> >commands and I just paste the changes in between the Use
> Database commands,
> >but I only have to do this for a few databases.
> >
> >Does anybody know of a way to automate this with scripts
> or a tool to apply
> >the schema and SO change to all user databases on a
> server.
> >
> >Thanks.
> >
> >Mike
> >
> >
> >.
> >

Easy way to sync

If you want help interpreting the error messages, then
please post them up. There's a lot of experience on this
newsgroup, and you might find that there's no need to
purchase a 3rd party tool at all.
SQL Datacompare would be an alternative if you want to go
down that path, or DataMirror.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Hi Paul,
Thanks for your reply. Is there are any free good utilities available for
this ?
Thanks again,
-Aruna
"Paul Ibison" wrote:

> If you want help interpreting the error messages, then
> please post them up. There's a lot of experience on this
> newsgroup, and you might find that there's no need to
> purchase a 3rd party tool at all.
> SQL Datacompare would be an alternative if you want to go
> down that path, or DataMirror.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||SQL Server Replication
(AFAIK there aren't any other data-synchronization free
utilities.)
Rgds,
Paul Ibison (SQL Server MVP)

Easy way to remove international alphabet from all rows?

Is there an easy way to loop through all rows and remove all
international alphabet characters from a column in a table, for example
remove German umlauts "" and convert them to a simple "u".
Thanks,
lqSee the following thread:
http://groups-beta.google.com/group...b45abaa5cf410b8

Razvan|||OK< thanks on that. How about keeping out these characters in the first
place?|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Is there an easy way to loop through all rows and remove all
> international alphabet characters from a column in a table, for example
> remove German umlauts "" and convert them to a simple "u".

Here is a much smarter version than the one in the linkn Razvan foun

create table T (
s nvarchar(20)
)
insert into T values ('cre')
insert into T values ('pt de fois gras')
insert into T values ('HLNE MELANON')

update T
set s = convert(varchar(20), s) COLLATE Cyrillic_General_CS_AS
go
select * from T
go
drop table T

This assumes that your data only has Latin characters.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> OK< thanks on that. How about keeping out these characters in the first
> place?

I don't know, nuke the parts of the world where we insist on using other
languages than English?

Seriously, I think it would be a very bad idea. If you want to send
mail to these persons, you do want to spell their names properly, don't
you?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||That's only one of the problems! I want to store the data with the
international characters but then I need to strip out the international
characters for certain output, such as in e-mail addresses...
lq|||Technically, international characters are permitted in email addresses
and domain names so stripping them out is still a bad idea.

You'll have to build your own table to translate characters that you
deem to be unacceptable. Google for some conversion tables but there
isn't really a single answer because it depends on which characters you
need to support (don't forget the two-byte character sets used in the
Far East and elsewhere for example).

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> That's only one of the problems! I want to store the data with the
> international characters but then I need to strip out the international
> characters for certain output, such as in e-mail addresses...

I would recomment that you store the e-mail address. While the e-mail
address often can be formed as firstname.lastname@.domain.xxx, this is
not a requirement.

And the local transformation may not be the one you expect. in German
after often replaced with ae, oe and ue. (Whether this is actually common
in mail addresses, I don't know.)

If you are thinking of the comment part of the e-mail address:

John Smith <johnsmith@.example.com
(John Smith is the comment here) You should either encode the comment
according to MIME as per RFC2047, or send as-is. But don't misspell
people's names.

(By the way, permit me to point out that the term "international characters"
is a misnomer. The internationalest characters I can think of is the
English alphabet A-Z. Non-ASCII characters is a better term, and
technically precise.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,
Thanks for that. You're right, I do mean non-ASCII characters...
This gets tricky because many city names and people names contain
non-ASCII characters and I want to display then properly, however, some
systems do not support non-ASCII characters and they need to be
cleansed for output to those systems, so as you point out, there is not
an always correct translation into ASCII characters.|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Thanks for that. You're right, I do mean non-ASCII characters...
> This gets tricky because many city names and people names contain
> non-ASCII characters and I want to display then properly, however, some
> systems do not support non-ASCII characters and they need to be
> cleansed for output to those systems, so as you point out, there is not
> an always correct translation into ASCII characters.

Systems in 2005 that is not capable to handle non-ASCII characters? So,
not all programs are capable to handle Unicode, but one would at least
imagine that they should be able handle 8-bit charset. If I were you,
I would consider to attempt to kill these systems by starvation and
not feed them any data. :-)

On a more serious point, there are one letter that the snippet I
posted does not handle and that is . This gets converted into a ?.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Windows file names is an example on one such system...|||Which Windows are you referring to? FAT32? NTFS does support non-ASCII
characters in files names, at least it does under Win 2003 so I don't
know why it wouldn't under other versions.

--
David Portas
SQL Server MVP
--|||laurenq uantrell (laurenquantrell@.hotmail.com) writes:
> Windows file names is an example on one such system...

The 8.3 names, yes, but you rarely have to see them. Even less rarely
have to form them. As long as you are on NTFS you can use the whole range
of Unicode, and use letters from any script. The FAT files systems may
not support this, but I'm pretty sure that they support the full range
of the current ANSI code page.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

easy way to mark all the fk's and triggers NFR?

Is there a script somehwhere which would alter all constraints and
recreate/alter triggers with NFR property set to 1?
or
would one of you guys know of an easy way to do it?
Thank you.
Tejas.
Following should help. PLEASE TEST IT BEFORE USING IT IN A REAL ENVIRONMENT.
You may uncomment the commented lines if trying to do the same for individual
tables.
SET NOCOUNT ON
GO
DECLARE@.tbl_namevarchar(50)
--SELECT@.tbl_name = 'tbl_name'
/*
Creating the command to drop constraints on the table
*/
SELECT'ALTER TABLE ' + object_name(fkeyid) + ' DROP CONSTRAINT ' +
object_name(constid) + CHAR(10) + 'GO' + CHAR(10)
FROMsysreferences
-- WHERE object_name(fkeyid) = @.tbl_name
-- ORobject_name(rkeyid) = @.tbl_name
ORDER BY object_name(fkeyid)
/*
Creating the command to re-create all the dropped constraints
*/
SELECT'ALTER TABLE ' + object_name(a.fkeyid) + ' ADD CONSTRAINT ' +
object_name(constid) + ' FOREIGN KEY (' + b.name + ') REFERENCES ' +
object_name(a.rkeyid) + '(' + c.name + ') NOT FOR REPLICATION' + CHAR(10) +
'GO' + CHAR(10)
FROM sysforeignkeys a,
syscolumns b,
syscolumns c
WHERE a.fkey = b.colid
AND a.fkeyid = b.id
AND a.rkey = c.colid
AND a.rkeyid = c.id
-- AND (object_name(a.rkeyid) = @.tbl_nameOR
-- object_name(a.fkeyid) = @.tbl_name)
ORDER BY object_name(a.fkeyid)
GO
"Tejas Parikh" wrote:

> Is there a script somehwhere which would alter all constraints and
> recreate/alter triggers with NFR property set to 1?
> or
> would one of you guys know of an easy way to do it?
> Thank you.
> Tejas.

easy way to list tables & columns?

I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software