Change Identify/Primary Key Column Data Type?

Hi ...

I've

taken over a project where the tables were created with

identity/primary key columns of type DECIMAL(12,0). The latest addition

to the project is to replicate data down to Pocket PC applications.

Replication requires that identify/primary key columns be of type

INT/BIGINT.

I've attempted to ALTER TABLE xxx ALTER COLUMN yyy

BIGINT; and it fails. Failed due to all the foreign key constraints that have been created. While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.

I then did an ALTER TABLE xxx NOCHECK CONSTRAINT

ALL; for every table in the system to disable checking of foreign keys

and then attempted to alter the column to a bigint and it still failed.

How

can I change the column from Decimal to BIGINT - or do I have to create

new tables, import all the data, get rid of the original tables? Please

tell me I don't have to do the latter.

Thanks ...Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.

Change FK error: "Unable to delete relationship..."

Hello,
I have three tables in my existing database, (in a SQL Server 2000 server).
- a "Students" table,
- a "Classes" table, and
- a "StudentIsInClass" table.
The "StudentIsInClass" table is the relationship table linking a record in
the Students table to a record in the Classes table. This is effected with
two foreign keys:
FK_StudentIsInClass_Students
FK_StudentIsInClass_Classes
Recently, there was a problem in our application when changing the
"StudentNumber" for a Student. This is the primary key and so I get an erro
r
because of the relationship. The fix I want to apply is to change the
foreign key FK_StudentIsInClass_Students. I need to turn on the function of
"Cascade Update Related Fields". I use the GUI functions in SQL Server
Enterprise Manager to do this but, when I try to save the change, I get an
error:
'Students' table
- Unable to delete relationship 'FK_StudentIsInClass_Students'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]'FK_StudentIsInClass_Students' ''
[Microsoft][ODBC SQL Server Driver][SQL Server]'''
(The ' result because my SQL Server is running on a foreign language
server, so I can't read the exact error text.)
Does anyone have a clue why I would get this error? Any suggestions for
what to do about it? I appreciate any advice and help.
Many thanks in advance,
JustinJustin Little (JustinLittle@.discussions.microsoft.com) writes:
> Recently, there was a problem in our application when changing the
> "StudentNumber" for a Student. This is the primary key and so I get an
> error because of the relationship. The fix I want to apply is to change
> the foreign key FK_StudentIsInClass_Students. I need to turn on the
> function of "Cascade Update Related Fields". I use the GUI functions in
> SQL Server Enterprise Manager to do this but, when I try to save the
> change, I get an error:
> 'Students' table
> - Unable to delete relationship 'FK_StudentIsInClass_Students'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]'FK_StudentIsInClass_Students' ''
> [Microsoft][ODBC SQL Server Driver][SQL Server]'''
> (The ' result because my SQL Server is running on a foreign language
> server, so I can't read the exact error text.)
> Does anyone have a clue why I would get this error? Any suggestions for
> what to do about it? I appreciate any advice and help.
It would certainly have helped to see the text of the error message.
I assume that the foreign language does not use the Latin script?
Anyway, rather than using Enterprise Manager for chaning tables, which
is dangerous as the table designer is buggy, use Query Analyzer and
type the DLL.
DECLARE @.err int
BEGIN TRANSACTION
ALTER TABLE StudentsIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students
SELECT @.err = @.@.error
IF @.err = 0
BEGIN
ALTER TABLE StudentsIsInClass ADD
CONSTRAINT FK_StudentIsInClass_Students FOREIGN KEY (StudentID)
REFERECENS Students(StudentID)
ON UPDATE CASCADE
ON DELETE CASCADE
SELECT @.err = @.@.error
END
IF @.err = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
It is not unlikely that that this will fail too, but at least in that
case you will see an error number. You can then look up that number in
SQL Server Books Online, provided that you have an English version of
Books Online installed on your computer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Many thanks for your advice on using Query Analyzer to get a readable error
message. (Thanks also for the tip about bugs in the Enterprise Manager GUI,
I'll bare that in mind in the future.)
I took your SQL procedure and used it in Query Analyzer. I find that there
is an error that occurs when I try to execute the line that reads "ALTER
TABLE StudentIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students".
The error number I get is Msg 3728.
Server: Msg 3728, Level 16, State 1, Line 1
'dbo.FK_StudentIsInClass_Students' 並不是條件約束。
The server, as you guessed, does not use a Latin script. It's Chinese, so
the error message comes through in Chinese. On Query Analyzer I can see the
Chinese characters instead of a bunch of question marks. Books Online gives
the English message as:
'%.*ls' is not a constraint.
This seems strange to me. Also in Query Analyzer, in the Object Browser
I've expanded the tree beneath my "StudentIsInClass" table, and opened the
Constraints sub-section. There are three constraints listed there.
- PK_StudentIsInClass (StudentNumber, ClassID)
- FK_StudentIsInClass_Students (StudentNumber)
- FK_StudentIsInClass_Classes (ClassID)
If I try to right-click and delete any of these relationships I get the same
error message. (In fact, I can't delete any of constraints on any tables in
my entire database!)
Do you have any idea why I'm getting this confusing message?
Many thanks for your support.
Justin
PS: I'm connecting to the database using a user id called "bigbyte",
supplied to me by the ISP. The tables all belong to this user id (e.g.
"bigbyte.StudentIsInClass"), however I notice that the constraints are
members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do you
think that this could be the cause of my current problems?|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> The error number I get is Msg 3728.
> Server: Msg 3728, Level 16, State 1, Line 1
> 'dbo.FK_StudentIsInClass_Students' ''
> The server, as you guessed, does not use a Latin script. It's Chinese,
> so the error message comes through in Chinese. On Query Analyzer I can
> see the Chinese characters instead of a bunch of question marks. Books
> Online gives the English message as: '%.*ls' is not a constraint.
Now, that was an interesting message...

> PS: I'm connecting to the database using a user id called "bigbyte",
> supplied to me by the ISP. The tables all belong to this user id (e.g.
> "bigbyte.StudentIsInClass"), however I notice that the constraints are
> members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do
> you think that this could be the cause of my current problems?
Most probably. I've tried to recreate this situation, but I was not
successful. When I create tables owned by a non-dbo user, and set up
a constraint, the constraint is owned by that user. The same happens
if I create the tables as owned by dbo, and then change ownership.
Maybe there was a bug in some previous version/service pack of SQL Server
that could cause this situation.
Since this is an abnormal situation, it is difficult to give sugestions
that I know work. I would try to change ownership of the table to
dbo, and then back to bigbyte.
You may have to talk to your ISP about this, as you may need someone
with dbo rights to perform this operation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
I got dbo rights from the ISP for the day. I tried what you suggested about
changing the ownership of the table. Now I'm no SQL Server guru but I found
out that the stored procedure for doing this is called sp_changeobjectowner.
So I tried running the following command:
EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
I get an error message saying:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 6
3
無法變更物件 'StudentIsInClass' 的擁有人或其所屬的_物件
_為新的擁有人 'dbo' 已有相同名稱的物件。
or in English:
Cannot change owner of object '%ls' or one of its child objects because the
new owner '%ls' already has an object with the same name.
HOWEVER, when I try to run the same sp on a table that has no constraints,
it runs through just fine and changes the ownership. Could this mean that
the database has some inconsistencies in the constraints that it is storing?
When I first created these tables (about a year or more ago) I used the
"bigbyte" user. Later, I noticed that all the ownerships had changed to
"dbo". I guessed that the ISP had been mucking around and changed it all fo
r
reasons best known to them. (Like I said, I'm no guru so I don't know what
all this ownership stuff is really about anyway.) However, later I started
having trouble, particularly in using DTS scripts to export this live data t
o
my "dev" database and I asked them to change the ownership back to "bigbyte"
.
Now it seems all my tables are owned by "bigbyte" but all the constraints
are owned by "dbo".
Is there a stored procedure or something I can use to try to track down
multiple instances of the same constraint name, possibly owned by different
owners? Perhaps my original constraints are still bouncing around somewhere
inside the db's system tables, unknown to all of us?
Many thanks,
Justin
"Erland Sommarskog" wrote:

> Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> Now, that was an interesting message...
>
> Most probably. I've tried to recreate this situation, but I was not
> successful. When I create tables owned by a non-dbo user, and set up
> a constraint, the constraint is owned by that user. The same happens
> if I create the tables as owned by dbo, and then change ownership.
> Maybe there was a bug in some previous version/service pack of SQL Server
> that could cause this situation.
> Since this is an abnormal situation, it is difficult to give sugestions
> that I know work. I would try to change ownership of the table to
> dbo, and then back to bigbyte.
> You may have to talk to your ISP about this, as you may need someone
> with dbo rights to perform this operation.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> I got dbo rights from the ISP for the day. I tried what you suggested
> about changing the ownership of the table. Now I'm no SQL Server guru
> but I found out that the stored procedure for doing this is called
> sp_changeobjectowner.
> So I tried running the following command:
> EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
> I get an error message saying:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner,
> Line 63 ' 'StudentIsInClass' '''' 'dbo' ''
> or in English: Cannot change owner of object '%ls' or one of its child
> objects because the new owner '%ls' already has an object with the same
> name.
> HOWEVER, when I try to run the same sp on a table that has no
> constraints, it runs through just fine and changes the ownership. Could
> this mean that the database has some inconsistencies in the constraints
> that it is storing?
You bet!
At this point, I would create a new set of tables copy all data over to
the new tables.
If you don't have a script saved for creating the tables, you can use
the Enterprise Manager to generate scripts. Make sure that you get triggers,
indexes and foreign keys.
Now, let's see what is the best way to have these two sets of tables in
parallel. I think the best is that youy edit the script so that all
table and trigger names are prefixed by "guest.". Constraints should not
be prefixed. Then use INSERT to copy data over. Take the occassion to add
ON UPDATE CASCADE.
Once this is done drop the existing tables - and let's hope *that* is
possible.
Finally, run sp_changeobjectowner to change ownership to bigbyte or dbo,
which ever you feel best.

> When I first created these tables (about a year or more ago) I used the
> "bigbyte" user. Later, I noticed that all the ownerships had changed to
> "dbo". I guessed that the ISP had been mucking around and changed it
> all for reasons best known to them. (Like I said, I'm no guru so I
> don't know what all this ownership stuff is really about anyway.)
Ownership of objects is like ownership of files in a file system.
But there is a second side of the coin. In SQL 2000, an owner is also a
schema. (This changes in SQL 2005). A schema is essentially a namespec.
A user has a default schema, which in SQL 2000 is always equal to his
user name. When you say "SELECT * FROM tbl" without specifying schema/owner,
SQL Server first looks in your default schema, then in the default schema of
the database, which in SQL 2000 always is dbo.
The guest user is a pre-defined user, and also has a schema, which I
suggested that you should use above, to permit you to have two sets of
tables at the same time.
I should add that in SQL 2000, having all objects owner by dbo - and thus
in the dbo schema - is what people do 99% of the time.

> Is there a stored procedure or something I can use to try to track down
> multiple instances of the same constraint name, possibly owned by
> different owners? Perhaps my original constraints are still bouncing
> around somewhere inside the db's system tables, unknown to all of us?
You would have to run queries on sysobjects to find out. But this database
seems a bit mashed, so you will need some brute force to get it working.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Change Field Name

I got 30 tables and I want to change one field name ,
Can I use SQL script to do that ?
Thanks a lot .Hi,
look for the procedure sp_rename which is described in the BOL in
detail.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--sql

Change default value

I need to change current default values from df_curr_user to df_login_user
for about 600 differrent fileds in different tables. Should I do it by using
sp_unbindefault and sp_bindefault for each column? How can I do it
programmatically?
Thanks in advance for any help!
PerayuAre you talking about changing the default constraint name?
AMB
"Perayu" wrote:

> I need to change current default values from df_curr_user to df_login_user
> for about 600 differrent fileds in different tables. Should I do it by usi
ng
> sp_unbindefault and sp_bindefault for each column? How can I do it
> programmatically?
> Thanks in advance for any help!
>
> Perayu
>
>|||Not only the default constraint name. I want to replace it to a new one.
Actually, I can't change the name or update current used df_curr_user
because it is bound to columns. So, I defined a new one as df_login_user and
try to replace it. But have no idea how to do it programmatically.
Thanks.
Perayu
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:654DA86C-7C7B-4DBF-8DCD-99599075D685@.microsoft.com...
> Are you talking about changing the default constraint name?
>
> AMB
> "Perayu" wrote:
>|||See if this helps.
use northwind
go
create default df_current_user as current_user
go
create default df_login_user as suser_sname()
go
create table t1 (
c1 nvarchar(256)
)
go
create table t2 (
c1 nvarchar(256)
)
go
create table t3 (
c1 nvarchar(256)
)
go
create table t4 (
c1 nvarchar(256)
)
go
exec sp_bindefault 'df_current_user', 't1.c1'
exec sp_bindefault 'df_current_user', 't2.c1'
exec sp_bindefault 'df_current_user', 't3.c1'
exec sp_bindefault 'df_current_user', 't4.c1'
go
select
table_name,
column_name,
column_default
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_current_user as %'
go
declare @.sql nvarchar(4000)
declare c cursor local fast_forward
for
select
'exec sp_unbindefault ''' + table_name + '.' + column_name + '''' as cmd
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_current_user as%'
open c
while 1 = 1
begin
fetch next from c into @.sql
if @.@.error != 0 or @.@.fetch_status != 0 break
exec sp_executesql @.sql
set @.sql = replace(@.sql, 'unbindefault', 'bindefault ''df_login_user'',')
exec sp_executesql @.sql
end
close c
deallocate c
go
select
table_name,
column_name,
column_default
from
information_schema.columns
where
objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
and column_default like '%create default df_login_user as %'
go
drop table t1, t2, t3, t4
go
drop default df_current_user, df_login_user
go
AMB
"Perayu" wrote:

> Not only the default constraint name. I want to replace it to a new one.
> Actually, I can't change the name or update current used df_curr_user
> because it is bound to columns. So, I defined a new one as df_login_user a
nd
> try to replace it. But have no idea how to do it programmatically.
> Thanks.
> Perayu
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:654DA86C-7C7B-4DBF-8DCD-99599075D685@.microsoft.com...
>
>|||It works like a charm!
Thank you so much.
Perayu
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E94A4876-0EF7-470B-8577-BF702B21B53F@.microsoft.com...
> See if this helps.
> use northwind
> go
> create default df_current_user as current_user
> go
> create default df_login_user as suser_sname()
> go
> create table t1 (
> c1 nvarchar(256)
> )
> go
> create table t2 (
> c1 nvarchar(256)
> )
> go
> create table t3 (
> c1 nvarchar(256)
> )
> go
> create table t4 (
> c1 nvarchar(256)
> )
> go
> exec sp_bindefault 'df_current_user', 't1.c1'
> exec sp_bindefault 'df_current_user', 't2.c1'
> exec sp_bindefault 'df_current_user', 't3.c1'
> exec sp_bindefault 'df_current_user', 't4.c1'
> go
> select
> table_name,
> column_name,
> column_default
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_current_user as %'
> go
> declare @.sql nvarchar(4000)
> declare c cursor local fast_forward
> for
> select
> 'exec sp_unbindefault ''' + table_name + '.' + column_name + '''' as cmd
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_current_user as%'
> open c
> while 1 = 1
> begin
> fetch next from c into @.sql
> if @.@.error != 0 or @.@.fetch_status != 0 break
> exec sp_executesql @.sql
> set @.sql = replace(@.sql, 'unbindefault', 'bindefault ''df_login_user'',')
> exec sp_executesql @.sql
> end
> close c
> deallocate c
> go
> select
> table_name,
> column_name,
> column_default
> from
> information_schema.columns
> where
> objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> and column_default like '%create default df_login_user as %'
> go
> drop table t1, t2, t3, t4
> go
> drop default df_current_user, df_login_user
> go
>
> AMB
> "Perayu" wrote:
>|||btw, does anyone know a 'clean' way to get the default value of a
default-bound column ?
for example:
create table t1(col varchar(10))
create default s as 'none'
sp_bindefault s, 't1.col'
I can find this value from information_schema.columns, like this:
select column_default
from information_schema.columns
where table_name = 't1'
and it returns:
create default s as 'none'
however, I believe extracting the string after the 'as' to get default
value isn't the best way. Can't imagine that sql server performs
inserts and calculates a default value on-the-flight
thanks,
Tam|||Sometime, the default value is not always a constant and must be done
on-the-flight. Like what I have is using SUSER_SNAME(), which will depends
on the login name.
Perayu
"Tam Vu" <vuht2000@.yahoo.com> wrote in message
news:1125429897.170823.60630@.g43g2000cwa.googlegroups.com...
> btw, does anyone know a 'clean' way to get the default value of a
> default-bound column ?
> for example:
> create table t1(col varchar(10))
> create default s as 'none'
> sp_bindefault s, 't1.col'
> I can find this value from information_schema.columns, like this:
> select column_default
> from information_schema.columns
> where table_name = 't1'
> and it returns:
> create default s as 'none'
> however, I believe extracting the string after the 'as' to get default
> value isn't the best way. Can't imagine that sql server performs
> inserts and calculates a default value on-the-flight
> thanks,
> Tam
>|||Perayu - I agree, but there're times that default values are constant,
would it do on the flight anyway ?sql

Change datatype from varchar to bigint not working

Hello,

I would like to change the datatype on a particular column from varchar to bigint across 100's of tables within a database.

I have the command ready which is:

ALTER TABLE tablename ALTER COLUMN columnname BIGINT

The problem happening is that it seems there are constraints across all the columns in every tables.

The error message is:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__tablename__columnname__0ABD916C' is dependent on column 'columnname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN columnname failed because one or more objects access this column.

I understand that if I delete this constraint, then it will let me modify the datatype of the column, but since there are tons of them and they are randomly named, how do I achive changing the datatype across multiple tables in bulk.Hi

This should help you:
http://www.sqlteam.com/article/default-constraint-names

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do ?
Thanks for your kind help
> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do ?
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', it
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:

> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do '
Thanks for your kind help> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do '
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', it
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:
> > in myDatabase, over 50 tables.
> > Every table got the field 'userid' , Now I need to change the field length
> > from char(10) to char(15)
> > How Can I do that ? Any simple and fast way to do '
> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

Change Datafield Length for all Table

in myDatabase, over 50 tables.
Every table got the field 'userid' , Now I need to change the field length
from char(10) to char(15)
How Can I do that ? Any simple and fast way to do '
Thanks for your kind help> in myDatabase, over 50 tables.
> Every table got the field 'userid' , Now I need to change the field length
> from char(10) to char(15)
> How Can I do that ? Any simple and fast way to do '
You could create a cursor, loop though your tables and dynamically
concatenate the ALTER TABLE string and execute it with the EXECUTE
statement. Unfortunately I don't see any other shortcut.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Also you can use undocumented SP sp_Msforeachtable 'your_command [?]', i
t
executes supplied command on each table in your DB, (it is similar to the
cursor on each table).
"Dejan Sarka" wrote:

> You could create a cursor, loop though your tables and dynamically
> concatenate the ALTER TABLE string and execute it with the EXECUTE
> statement. Unfortunately I don't see any other shortcut.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> www.SolidQualityLearning.com
>
>

change column names

i am trying to change column names in a bunch of tables.

why is this not right?

is there any sp that i can use as i have to change this in a lot of tables across two databases?

Alter Table Answers

Change Product NewProduct varchar(35)

This works.

EXEC sp_rename 'Answers2.[Product]', 'NewProduct', 'COLUMN'

now how do i do multiple columns.......

if anyone can figure faster?

|||You can't, just execute a batch query (a normal query with more queries in it) like this:

EXEC sp_rename 'Answers2.[Product]', 'NewProduct1', 'COLUMNA' GO

EXEC sp_rename 'Answers3.[Product]', 'NewProduct2', 'COLUMNB' GO

EXEC sp_rename 'Answers4.[Product]', 'NewProduct3', 'COLUMNC' GO

|||You can do some very clever things with sysobjects and syscolumns, provided all the column names are the same that you want to rename. Write a stored procedure that gets a list of the tables that contain that column. Then write a dynamic sql statement or just a standard sql statement that ouputs an alter command for each row returned. Check the syntax of the coded created and execute if it is OK.|||

select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

from syscolumns col

join sysobjects obj

on col.id = obj.id

where col.name = 'Product'

-

That will output a load of dynamic SQL. Now copy and paste into Query Analyser or the query execution window, test and execute.

For more SQL Server tips, check out my blog:

http://blogs.claritycon.com/blogs/the_englishman/default.aspx

HTH

|||

You may need to alter the query to:

:

select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

from syscolumns col

join sysobjects obj

on col.id = obj.id

where col.name = 'Product'

and obj.[type] = 'U'

-

To ensure only tables are returned from sysobjects

|||Yes you can, with syscolumns and sysobjects. See my post|||Not a very good idea messing around with system tables. Write a small SP for creating DDL and execute that DDL script.

change column names

i am trying to change column names in a bunch of tables.

why is this not right?

is there any sp that i can use as i have to change this in a lot of tables across two databases?

Alter Table Answers

Change Product NewProduct varchar(35)

This works.

EXEC sp_rename 'Answers2.[Product]', 'NewProduct', 'COLUMN'

now how do i do multiple columns.......

if anyone can figure faster?

|||You can't, just execute a batch query (a normal query with more queries in it) like this:

EXEC sp_rename 'Answers2.[Product]', 'NewProduct1', 'COLUMNA' GO

EXEC sp_rename 'Answers3.[Product]', 'NewProduct2', 'COLUMNB' GO

EXEC sp_rename 'Answers4.[Product]', 'NewProduct3', 'COLUMNC' GO

|||You can do some very clever things with sysobjects and syscolumns, provided all the column names are the same that you want to rename. Write a stored procedure that gets a list of the tables that contain that column. Then write a dynamic sql statement or just a standard sql statement that ouputs an alter command for each row returned. Check the syntax of the coded created and execute if it is OK.|||

select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

from syscolumns col

join sysobjects obj

on col.id = obj.id

where col.name = 'Product'

-

That will output a load of dynamic SQL. Now copy and paste into Query Analyser or the query execution window, test and execute.

For more SQL Server tips, check out my blog:

http://blogs.claritycon.com/blogs/the_englishman/default.aspx

HTH

|||

You may need to alter the query to:

:

select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

from syscolumns col

join sysobjects obj

on col.id = obj.id

where col.name = 'Product'

and obj.[type] = 'U'

-

To ensure only tables are returned from sysobjects

|||Yes you can, with syscolumns and sysobjects. See my post|||Not a very good idea messing around with system tables. Write a small SP for creating DDL and execute that DDL script.

Change collations in many tables defined using COLLATE

I have (inherited a) a db with lots of tables looking like the definition at the end of the message.

I need to change all these tables, so that all the textual fields follow the default DB collation.

Any help appreciated!
THANX...

CREATE TABLE [Club] (
[id] [int] NOT NULL ,
[name] [varchar] (100) COLLATE Greek_CI_AS NOT NULL ,
[address] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[zoomAreaId] [int] NULL ,
[phone] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[contact] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[clubCategoryId] [int] NULL ,
[unused] [varchar] (2) COLLATE Greek_CI_AS NULL ,
[monday] [int] NULL ,
[tuesday] [int] NULL ,
[wednesday] [int] NULL ,
[thursday] [int] NULL ,
[friday] [int] NULL ,
[saturday] [int] NULL ,
[sunday] [int] NULL ,
[comments] [text] COLLATE Greek_CI_AS NULL ,
[URL] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[image] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[priceCategoryId] [int] NULL ,
[ratingId] [int] NULL ,
[entryDate] [datetime] NULL ,
[WAPText] [varchar] (255) COLLATE Greek_CI_AS NULL ,
[SMSText] [varchar] (160) COLLATE Greek_CI_AS NULL ,
[SMSAddress] [varchar] (50) COLLATE Greek_CI_AS NULL ,
[active] [bit] NOT NULL ,
[content_id] [bigint] NULL ,
[disp_as_propos] [bit] NULL ,
[date2disp] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOALTER TABLE...ALTER COLUMN would do the trick, but you have to drop all indexes and all constraints. If you have DBArtisan it would be very easy to do, or you can script both DROP <table_name>.<index_name> and ALTER TABLE <table_name> DROP CONSTRAINT and then re-create them yourself.|||Also, BOL:

You cannot alter the collation of a column that is currently referenced by:

A computed column.
An index.
Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
A CHECK constraint.
A FOREIGN KEY constraint.

change collation of a type of text column

Hi!
I have to change a complate database collation. After I had changed the
database collation I go through the tables and its columns and make a DDL
command on collated columns :
"ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
It work on varchar and char columns but I receive an error message in case
of text columns :
"Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'Ic_DbFields' because it is 'text'."
I use OLEDB.
Enterprise Manager can change the collation on text columns.
What can I do?
I nedd to use SQL-DMO?
thanks for any help :
ImreCheck if you have a full-text index using that column. if so, then you have
to remove it before changing the collation.
AMB
"Imre Ament" wrote:

> Hi!
> I have to change a complate database collation. After I had changed the
> database collation I go through the tables and its columns and make a DDL
> command on collated columns :
> "ALTER TABLE table ALTER COLUMN column text COLLATE Hungarian_CI_AS NULL"
> It work on varchar and char columns but I receive an error message in case
> of text columns :
> "Server: Msg 4928, Level 16, State 1, Line 1
> Cannot alter column 'Ic_DbFields' because it is 'text'."
> I use OLEDB.
> Enterprise Manager can change the collation on text columns.
> What can I do?
> I nedd to use SQL-DMO?
> thanks for any help :
> Imre|||I don't think you can alter collation for blob columns.
One option can be to add a new column with desired collation, update the new
column with the value
of the old column, drop the old column and rename the new column. Column ord
er will not be
preserved, of course.
Another option is to create a new table. My guess is that this is what Enter
prise Manager does.
Imre: pressing "save change script" will show you how EM does this. I doubt
it is exposed in DMO, as
the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But yo
u could have a look, of
course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
> Check if you have a full-text index using that column. if so, then you hav
e
> to remove it before changing the collation.
>
> AMB
> "Imre Ament" wrote:
>|||You are right. Thanks for the comment.
AMB
"Tibor Karaszi" wrote:

> I don't think you can alter collation for blob columns.
> One option can be to add a new column with desired collation, update the n
ew column with the value
> of the old column, drop the old column and rename the new column. Column o
rder will not be
> preserved, of course.
> Another option is to create a new table. My guess is that this is what Ent
erprise Manager does.
> Imre: pressing "save change script" will show you how EM does this. I doub
t it is exposed in DMO, as
> the DaVinci tools (the diagramming etc tools) are not exposed in DMO. But
you could have a look, of
> course.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:F8284F2D-0D4B-4910-8481-E594FEDB3F40@.microsoft.com...
>

Change collation all db tables and db

If I need to copy a db to another server but I need to chnage the new db
collation and the collation of all the columns, is there a better way than:
1. Create new DB with desired collation
2. Create table scripts without collation option or constraints; run them
3. Use BCP or DTS to move the data
4. Script contraints, stored procs, views....; run them
I ran DTS object move but that always seems to copy the column collation.
Ideally I would like to somehow force a backup or restore to use a given
collation.
-Peterhttp://groups.google.co.uk/groups?q...
&rnum=2
Jacco Schalkwijk
SQL Server MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> If I need to copy a db to another server but I need to chnage the new db
> collation and the collation of all the columns, is there a better way
> than:
> 1. Create new DB with desired collation
> 2. Create table scripts without collation option or constraints; run them
> 3. Use BCP or DTS to move the data
> 4. Script contraints, stored procs, views....; run them
> I ran DTS object move but that always seems to copy the column collation.
> Ideally I would like to somehow force a backup or restore to use a given
> collation.
> --
> -Peter|||Thanks Jacco. I guess there is no easy way to change collation.
-Peter
"Jacco Schalkwijk" wrote:

> http://groups.google.co.uk/groups?q...r />
bl&rnum=2
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Peter L." <PeterL@.discussions.microsoft.com> wrote in message
> news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
>
>

Change collation all db tables and db

If I need to copy a db to another server but I need to chnage the new db
collation and the collation of all the columns, is there a better way than:
1. Create new DB with desired collation
2. Create table scripts without collation option or constraints; run them
3. Use BCP or DTS to move the data
4. Script contraints, stored procs, views....; run them
I ran DTS object move but that always seems to copy the column collation.
Ideally I would like to somehow force a backup or restore to use a given
collation.
-Peter
http://groups.google.co.uk/groups?q=...phx.gbl&rnum=2
Jacco Schalkwijk
SQL Server MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> If I need to copy a db to another server but I need to chnage the new db
> collation and the collation of all the columns, is there a better way
> than:
> 1. Create new DB with desired collation
> 2. Create table scripts without collation option or constraints; run them
> 3. Use BCP or DTS to move the data
> 4. Script contraints, stored procs, views....; run them
> I ran DTS object move but that always seems to copy the column collation.
> Ideally I would like to somehow force a backup or restore to use a given
> collation.
> --
> -Peter
|||Thanks Jacco. I guess there is no easy way to change collation.
-Peter
"Jacco Schalkwijk" wrote:

> http://groups.google.co.uk/groups?q=...phx.gbl&rnum=2
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Peter L." <PeterL@.discussions.microsoft.com> wrote in message
> news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
>
>

Change collation all db tables and db

If I need to copy a db to another server but I need to chnage the new db
collation and the collation of all the columns, is there a better way than:
1. Create new DB with desired collation
2. Create table scripts without collation option or constraints; run them
3. Use BCP or DTS to move the data
4. Script contraints, stored procs, views....; run them
I ran DTS object move but that always seems to copy the column collation.
Ideally I would like to somehow force a backup or restore to use a given
collation.
--
-Peterhttp://groups.google.co.uk/groups?q=bart+duncan+collate+database+jacco&hl=en&lr=&group=microsoft.public.sqlserver.*&selm=%23%24c1OFoaEHA.1652%40TK2MSFTNGP09.phx.gbl&rnum=2
--
Jacco Schalkwijk
SQL Server MVP
"Peter L." <PeterL@.discussions.microsoft.com> wrote in message
news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> If I need to copy a db to another server but I need to chnage the new db
> collation and the collation of all the columns, is there a better way
> than:
> 1. Create new DB with desired collation
> 2. Create table scripts without collation option or constraints; run them
> 3. Use BCP or DTS to move the data
> 4. Script contraints, stored procs, views....; run them
> I ran DTS object move but that always seems to copy the column collation.
> Ideally I would like to somehow force a backup or restore to use a given
> collation.
> --
> -Peter|||Thanks Jacco. I guess there is no easy way to change collation.
-Peter
"Jacco Schalkwijk" wrote:
> http://groups.google.co.uk/groups?q=bart+duncan+collate+database+jacco&hl=en&lr=&group=microsoft.public.sqlserver.*&selm=%23%24c1OFoaEHA.1652%40TK2MSFTNGP09.phx.gbl&rnum=2
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Peter L." <PeterL@.discussions.microsoft.com> wrote in message
> news:62681A24-84DC-4E41-A1B0-FD85D8F93AB5@.microsoft.com...
> > If I need to copy a db to another server but I need to chnage the new db
> > collation and the collation of all the columns, is there a better way
> > than:
> >
> > 1. Create new DB with desired collation
> > 2. Create table scripts without collation option or constraints; run them
> > 3. Use BCP or DTS to move the data
> > 4. Script contraints, stored procs, views....; run them
> >
> > I ran DTS object move but that always seems to copy the column collation.
> >
> > Ideally I would like to somehow force a backup or restore to use a given
> > collation.
> >
> > --
> > -Peter
>
>

challenging sql queries

here is a structure of tables in a database

CUSTOMERS (CustID*, CompanyName, ContactName, ContactTitle, Address,
City, Region, PostalCode, Country, Phone, Fax)

EMPLOYEES (EmployeeID*, Lastname, Firstname, Title, TitleofCourtesy,
Birthdate, Hiredate, Address, City, Region, Postalcode, Country,
Homephone, Extension, Reportsto)

ORDERS (OrderID*, CustID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,
Shipregion, ShipPostalCode, ShipCountry)

ORDER_DETAILS (OrderID*, ProductID*, UnitPrice, Quantity, Discount)

PRODUCTS (ProductID*, ProductName, SupplierID, PL_ID, QuantityPerUnit,
UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)

PRODUCT_LINES (PL_Id*, PL_Name, Description)

SHIPPERS (ShipperID*, CompanyName, Phone)

SUPPLIERS (Supplierid*, Companyname, Contactname, Contacttitle,
Address, City, Region, Postalcode, Country,Phone,Fax)

* - Primary keys

queries (these are problem questions given in a competition!!!)

1.) In which month of 1997 was the most number of orders placed? List
the month, number of orders placed during that month , number of
customers who placed orders in that month , gross (i.e. disregard
discount) total value of orders placed .

2.)
Determine the products for which there has been at least one
order during each month of 1997. Display for these products: the
Product ID, Product name, Product Line Name, number of orders placed
during 1997 , number of customers who have ordered the product in 1997
, and gross (i.e. disregarding discount) total value of orders placed
for the product in 1997 .

3.) Find the customers who have placed orders during 1996 & 1997 for
products in every product line AND who have had their orders handled
by either all employees or all but one employee. Display the customer
ID, company name, address, city, country and number of orders placed

4.) For each product line, find the top three customers (based on
maximum total purchase value; ignore discounts). Display the Product
Line name, the Customer ID & Company name, and the total purchase
value for that customer in the product line . Sort the output by
Product Line and within that, the line revenue figure (highest first).
i think rank() shud b used

any clues/ideas ?On 6 Mar 2004 02:15:00 -0800, usenetdada@.yahoo.com (dada) wrote:

>any clues/ideas ?

Go to class more often?

--
Mike Sherrill
Information Management Systems|||"dada" <usenetdada@.yahoo.com> wrote in message
news:f7851403.0403060215.7c7b3094@.posting.google.c om...
> here is a structure of tables in a database

This is not a structure of any sort of daatabase. There's nothing here
about datatypes.

Is CustID a character field? A numeric, or something else?

There's no sample data either.

Do your homework and then come back with proper DDL and example data and
then perhaps folks can help.

> CUSTOMERS (CustID*, CompanyName, ContactName, ContactTitle, Address,
> City, Region, PostalCode, Country, Phone, Fax)
> EMPLOYEES (EmployeeID*, Lastname, Firstname, Title, TitleofCourtesy,
> Birthdate, Hiredate, Address, City, Region, Postalcode, Country,
> Homephone, Extension, Reportsto)
> ORDERS (OrderID*, CustID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,
> Shipregion, ShipPostalCode, ShipCountry)
> ORDER_DETAILS (OrderID*, ProductID*, UnitPrice, Quantity, Discount)
> PRODUCTS (ProductID*, ProductName, SupplierID, PL_ID, QuantityPerUnit,
> UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
> PRODUCT_LINES (PL_Id*, PL_Name, Description)
> SHIPPERS (ShipperID*, CompanyName, Phone)
> SUPPLIERS (Supplierid*, Companyname, Contactname, Contacttitle,
> Address, City, Region, Postalcode, Country,Phone,Fax)
> * - Primary keys
> queries (these are problem questions given in a competition!!!)
> 1.) In which month of 1997 was the most number of orders placed? List
> the month, number of orders placed during that month , number of
> customers who placed orders in that month , gross (i.e. disregard
> discount) total value of orders placed .
> 2.)
> Determine the products for which there has been at least one
> order during each month of 1997. Display for these products: the
> Product ID, Product name, Product Line Name, number of orders placed
> during 1997 , number of customers who have ordered the product in 1997
> , and gross (i.e. disregarding discount) total value of orders placed
> for the product in 1997 .
> 3.) Find the customers who have placed orders during 1996 & 1997 for
> products in every product line AND who have had their orders handled
> by either all employees or all but one employee. Display the customer
> ID, company name, address, city, country and number of orders placed
> 4.) For each product line, find the top three customers (based on
> maximum total purchase value; ignore discounts). Display the Product
> Line name, the Customer ID & Company name, and the total purchase
> value for that customer in the product line . Sort the output by
> Product Line and within that, the line revenue figure (highest first).
> i think rank() shud b used
> any clues/ideas ?|||At any of the universities I have attended or taught at, attempting to
present the work of other people as your own work is grounds for
expulsion or termination.

A few years back, I filed a compliant against a student at a University
in New Zealand for this. I do not know what action was against him.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> This is not a structure of any sort of daatabase. There's nothing here
> about datatypes.
> Is CustID a character field? A numeric, or something else?
> There's no sample data either.

Of course there is! Don't you recognize Northwind when you see it?

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94A57A1CE8C0Yazorman@.127.0.0.1...
> Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> > This is not a structure of any sort of daatabase. There's nothing here
> > about datatypes.
> > Is CustID a character field? A numeric, or something else?
> > There's no sample data either.
> Of course there is! Don't you recognize Northwind when you see it?

Touche. I'll go slink back into my corner now. :-)

> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Joe,

the world is full of people trying to make something to survive, why
somes few others giving orders to, says "this is what I have done..."

Don't you recognize G. Bush and american soldiers ?
French humour, you can say !

A +

Joe Celko a crit:
> At any of the universities I have attended or taught at, attempting to
> present the work of other people as your own work is grounds for
> expulsion or termination.
> A few years back, I filed a compliant against a student at a University
> in New Zealand for this. I do not know what action was against him.
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

--
Frdric BROUARD, MVP Microsoft SQL Server. Langage SQL / Delphi / web
Livre SQL - col. Rfrence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour dbutants et pros : http://sqlpro.developpez.com
****************** mailto:brouardf@.club-internet.fr ******************

challenging search task is not working as expected

Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1

-

-searchword- column name

--

Learn more about melons row0

--

%.txt row1

-

table2

-testname- column name

--

FKOV43C6.EXE

-
frusdr.txt

-
FRUSDR.TXT

SPGP_FWPkg_66G.zip

readme.txt

--
README.TXT

-
watermelon.exe

-
Learn more about melons read me.txt

-

Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================

select * from @.table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @.table1 ta

JOIN @.table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @.table1)

)

===============================================================================

script to create tables

============================================================================

DECLARE @.table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @.table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @.table1 (

searchword

) VALUES ( '%.txt' )

DECLARE @.table2 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @.table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @.table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @.table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @.table2 (

testname

) VALUES ( 'readme.txt' )

INSERT INTO @.table2 (testname

) VALUES ('README.TXT' )

INSERT INTO @.table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @.table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @.table2

DECLARE @.table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )

===================================================================================

Here it is:

DECLARE @.num AS int;
SELECT @.num = COUNT(*) FROM @.table1;

SELECT tb.[testname] FROM @.table1 ta
cross JOIN (select distinct * from @.table2) tb
where tb.[testname] LIKE + '%' + ta.searchword + '%'
group by tb.[testname] having count(ta.searchword) = @.num
|||

maybe you also should take an look on "full text index":

f. ex. contains-function

|||

Hi Zuomin,

Thank you very much.It worked perfectly. Thanks for spending your valuable time.

challenging search task is not working as expected

Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1

-

-searchword- column name

--

Learn more about melons row0

--

%.txt row1

-

table2

-testname- column name

--

FKOV43C6.EXE

-
frusdr.txt

-
FRUSDR.TXT

SPGP_FWPkg_66G.zip

readme.txt

--
README.TXT

-
watermelon.exe

-
Learn more about melons read me.txt

-

Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================

select * from @.table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @.table1 ta

JOIN @.table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @.table1)

)

===============================================================================

script to create tables

============================================================================

DECLARE @.table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @.table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @.table1 (

searchword

) VALUES ( '%.txt' )

DECLARE @.table2 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @.table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @.table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @.table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @.table2 (

testname

) VALUES ( 'readme.txt' )

INSERT INTO @.table2 (testname

) VALUES ('README.TXT' )

INSERT INTO @.table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @.table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @.table2

DECLARE @.table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )

===================================================================================

Here it is:

DECLARE @.num AS int;
SELECT @.num = COUNT(*) FROM @.table1;

SELECT tb.[testname] FROM @.table1 ta
cross JOIN (select distinct * from @.table2) tb
where tb.[testname] LIKE + '%' + ta.searchword + '%'
group by tb.[testname] having count(ta.searchword) = @.num
|||

maybe you also should take an look on "full text index":

f. ex. contains-function

|||

Hi Zuomin,

Thank you very much.It worked perfectly. Thanks for spending your valuable time.

Challenging Join question

I have a query in which I have 2 tables, but I join one of the tables to twice. I use the result to populate a drop down list

here is the data in table 1 (BaselineControlPairings)

ID SITEID BaselineID ControlID Description

2 4495 2 1 Jones - Jun 07

here is the data in table 2 (BaselineLog)

TESTID SITEID StartDate EndDate DataSetID

4 4495 2007-05-30 2007-06-07 1

5 4495 2007-06-09 2007-06-15 2

I want the query to do the following.. select all records from

BaselineControlPairings where the site id = 4495,

than get the associated BaselineLog record Start and end date where

BaselineControlPairings.BaselineID = BaselineLog.DataSetID

than get the associated BaselineLog record Start and end date where

BaselineControlPairings.ControlID = BaselineLog.DataSetID

Now I string the result together to be displayed.

Here is my query code

Code Snippet

SELECT BaselineControlPairings.TestID

,CONVERT(varchar(12), BaselineLog.StartDate, 110)as BLStart

,CONVERT(varchar(12), BaselineLog.EndDate, 110)as BLEnd

,CONVERT(varchar(12), BaselineLog_1.StartDate, 110)as CTRLStart

,CONVERT(varchar(12), BaselineLog_1.EndDate, 110)as CTRLEnd

,BaselineControlPairings.Description +' Baseline: '+CONVERT(varchar(12)

, BaselineLog.StartDate, 110)+' - '+CONVERT(varchar(12)

, BaselineLog.EndDate, 110)+' -- '+'Control: '+CONVERT(varchar(12)

, BaselineLog_1.StartDate, 110)+' - '+CONVERT(varchar(12), BaselineLog_1.EndDate, 110)as dates

FROM BaselineControlPairings INNERJOIN

BaselineLog AS BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID

INNERJOIN

BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID

WHERE(BaselineControlPairings.siteid = 4495)

here is the result of my query, which we know is wrong because it should contain only 1 result

Jones Jun 07 Baseline: 05-08-2007 - 05-14-2007 -- Control: 05-18-2007 - 05-25-2007
Jones Jun 07 Baseline: 06-09-2007 - 06-15-2007 -- Control: 05-18-2007 - 05-25-2007
Jones Jun 07 Baseline: 05-08-2007 - 05-14-2007 -- Control: 05-30-2007 - 06-07-2007
Jones Jun 07 Baseline: 06-09-2007 - 06-15-2007 -- Control: 05-30-2007 - 06-07-2007

I should only have one record in the result because I should only have the number of results that are in the

BaselineControlPairings with a matching SiteID

I know the issue is occuring in the join, but I have no idea on how to resolve it and I have spent numerous hours on this. any suggestions?

hi, your sample data and your desired output doesn't seem to match.

anyway here's my hunch,

SELECT BaselineControlPairings.ID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12)
, BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12)
, BaselineLog.EndDate, 110) + ' -- ' + 'Control: ' + CONVERT(varchar(12)
, BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM BaselineControlPairings INNER JOIN
BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 4495)|||Thanks for the reply. I tried your solution, and although better its still broken. I am still getting some duplications but not as many. Also here is my actual code and data:

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd

,BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates

FROM BaselineControlPairings INNER JOIN
BaselineLog AS BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID INNER JOIN
BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
WHERE (BaselineControlPairings.siteid = 4495)

The data I am using:

BaselineLog Table

WhoProvisTestID SiteID StartDate EndDate Results DataSetID BLorCTRL

NULL 55 4495 2007-05-27 2007-06-04 NULL 5 0

NULL 56 4495 2007-05-27 2007-06-04 NULL 2 0

NULL 57 4495 2007-05-27 2007-06-04 NULL 3 1

NULL 58 905 2007-05-28 2007-05-31 NULL 3 0

NULL 59 905 2007-05-28 2007-05-31 NULL 1 0

NULL 60 907 2007-05-27 2007-05-29 NULL 17 0

BaselineControlPairings Table

TestID SiteID BaselineID ControlID Description

-- -- -- -- -

25 905 3 1 hgc

26 4495 5 3 df

27 4495 2 5 df

against thes tables I should receive 1 result for the query using ID 905 and 2 for 4495, BUT
for 905 I receive 2 rows both for testID 25, yet there is only 1 test id.

here are the results for 905:
TestID BLStart BLEnd CTRLStart CTRLEnd dates
--
25 05-27-2007 06-04-2007 05-28-2007 05-31-2007 hgc Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-28-2007 - 05-31-2007
25 05-28-2007 05-31-2007 05-28-2007 05-31-2007 hgc Baseline: 05-28-2007 - 05-31-2007 -- Control: 05-28-2007 - 05-31-2007

and for 4495 I should get 2 rows, 1 for test id 26 and one for id 27. Yet I get (2) for 26 and one for 27.

ARGH ! ! !

Results for id 4495
TestID BLStart BLEnd CTRLStart CTRLEnd dates
--
26 05-27-2007 06-04-2007 05-27-2007 06-04-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-27-2007 - 06-04-2007
26 05-27-2007 06-04-2007 05-28-2007 05-31-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-28-2007 - 05-31-2007
27 05-27-2007 06-04-2007 05-27-2007 06-04-2007 df Baseline: 05-27-2007 - 06-04-2007 -- Control: 05-27-2007 - 06-04-2007

any help is greatly appreciated!|||hi

i'm not sure on what you've meant was still broken, but based on your test data and expected result, if you add BaselineControlPairings.SiteID = BaselineLog_1.SiteID and BaselineControlPairings.SiteID = BaselineLog.SiteID respectively on your joins would result to what you're expecting.

select *
into #BaseLog
from (
select NULL as WhoProvis
, 55 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 5 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 56 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 2 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 57 as TestID
, 4495 as SiteID
, '2007-05-27' as StartDate
, '2007-06-04' as EndDate
, NULL as Results
, 3 as DataSetID
, 1 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 58 as TestID
, 905 as SiteID
, '2007-05-28' as StartDate
, '2007-06-31' as EndDate
, NULL as Results
, 3 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 59 as TestID
, 905 as SiteID
, '2007-05-28' as StartDate
, '2007-06-31' as EndDate
, NULL as Results
, 1 as DataSetID
, 0 as BLorCTRL
UNION ALL
select NULL as WhoProvis
, 60 as TestID
, 907 as SiteID
, '2007-05-27' as StartDate
, '2007-06-29' as EndDate
, NULL as Results
, 17 as DataSetID
, 0 as BLorCTRL
) BaseLog

select *
into #BaselineControlPairings
from (
select 25 as TestID
, 905 as SiteID
, 3 as BaselineID
, 1 as ControlID
, 'hgc' as Description
UNION ALL
select 26 as TestID
, 4495 as SiteID
, 5 as BaselineID
, 3 as ControlID
, 'df' as Description
UNION ALL
select 27 as TestID
, 4495 as SiteID
, 2 as BaselineID
, 5 as ControlID
, 'df' as Description
) BaselineControlPairings

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM #BaselineControlPairings BaselineControlPairings INNER JOIN
#BaseLog BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
#BaseLog BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 905)

SELECT BaselineControlPairings.TestID
, CONVERT(varchar(12), BaselineLog.StartDate, 110) as BLStart
, CONVERT(varchar(12), BaselineLog.EndDate, 110) as BLEnd
, CONVERT(varchar(12), BaselineLog_1.StartDate, 110) as CTRLStart
, CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as CTRLEnd
, BaselineControlPairings.Description + ' Baseline: ' + CONVERT(varchar(12), BaselineLog.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog.EndDate, 110) + ' -- '
+ 'Control: ' + CONVERT(varchar(12), BaselineLog_1.StartDate, 110) + ' - ' + CONVERT(varchar(12), BaselineLog_1.EndDate, 110) as dates
FROM #BaselineControlPairings BaselineControlPairings INNER JOIN
#BaseLog BaselineLog_1 ON BaselineControlPairings.ControlID = BaselineLog_1.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog_1.SiteID INNER JOIN
#BaseLog BaselineLog ON BaselineControlPairings.BaselineID = BaselineLog.DataSetID
AND BaselineControlPairings.SiteID = BaselineLog.SiteID
WHERE (BaselineControlPairings.siteid = 4495)

drop table #BaseLog
drop table #BaselineControlPairings|||Thanks, this fixed it... I think the mistake I was making was not writing to a temp file.|||hi, i'm sorry i don't think that writing to a temp file made the difference. maybe you might have misplaced the ON filter for the SiteID

Central vs separated databases

What you have described is a very small database these days.
But, if you are going to grow it by over 200 tables, in 1 year,
it sounds like you have a design problem. Nothing you
have described needs 200 tablesto accomplish properly.
Robert
On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
> Now +- 1GB.
>
> And what if you want a base table for common thing like name,
> address,nationality... or a postal code table (wich will be used in
> more than one database).
> Maybe yours isn't a good exapmle but what about following situation:
> Employee database, intranet database, room reservation (for employees
> and others),...
> People can be in all of the databases.
> So shouldn't it be better in this situation to create a general
> database for the table people (and use a trigger to check te
> integrity).- Hide quoted text -
> - Show quoted text -
On 8 nov, 20:03, raibeart <raibe...@.gmail.com> wrote:
> What you have described is a very small database these days.
> But, if you are going to grow it by over 200 tables, in 1 year,
> it sounds like you have a design problem. Nothing you
> have described needs 200 tablesto accomplish properly.
> Robert
> On Nov 6, 8:30 am, HDI <hd...@.hotmail.com> wrote:
>
>
>
>
>
>
> - Tekst uit oorspronkelijk bericht weergeven -
Because this are only a few of the project for the comming 5 years.