Combining multiple tables - please help!

Hii all.

quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.

The problem:
When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...

Is there a way to join these tables without leavings gaps ?

any help is appreciated.
Neil
Try putting each matrix inside a rectangle, then hide the rectangle containing the table based on the user parameter.

Additionally, put all 3 rectangles within one parent rectangle. Should help with the spacing.|||Hi Andy

thanks for the reply!

While I like that idea and provides a lot more control over the positioning of the tables.. the problem still exits...

It seems that making a textbox "visibility = true" just produces an empty textbox..but still leaves the space allocated to textbox in place... and therefore the gap.. the rectangles dont seem to help.. unless im doing something wrong..

Does this seem right ?
|||I also just tried making the middle rectangle hidden = true and it didnt close the gap as expected.. very confused.. I just draggged the rectangles into the parent rectangle... ?

very strange.. any idea's why this may not be working for me ?
|||Well now I'm confused about what you're doing. One partyou're talking about matrixes, the next you're talking about tables. Which are you using? Matrixes or tables? Do you have 3 separate matrixes or 3 rows in one matrix?

Have you tried setting the visibility property for the whole row (or column) instead of the individual textboxes?|||

Using Matrix

Select appropriate textbox in matrix

Right click

Select Edit Group...

Visibility tab

Select Expression

Add your expression

It will not work if you put it on the textbox properties.

Using Table

Select appropriate column

Go to properties

Go down to visibility tag and select expression

Add your expression

You can use a variety of expressions to hide the columns like parameters or counts.

=IIF(Fields!City.Value=@.City, FALSE, TRUE)

or

=IIF(Trim(Fields!City.Value) = "", TRUE, FALSE)

Hope this helps

|||Sorry, I mean matrix...

My problem is not making the whole matrix invisible... I have 3 of them lined from left to right.
All three have the same row (group headings - sorry I should have made that clear). I dont need all the to show the same headings on the row groups. So, I just want to beable to hide these headings depending on the users selection.

The problem is that making the row headings + total header hidden, leaves a gap between the matrices as if the headers were there but just with no text or borders in them (invisible to the human eye but as if its like a brick stopping the matrix from moving closer to the other ones)

Sorry for the confusion.. thanks for helps !
But any idea.. anyone ?
|||Hi Brady,

Thanks for the suggestion.. while I have been trying that.. the problem there is that bigger Textbox located in the top left of the matrix.. that wont be invisible and therefore will stop my matrix from moving closer to the other matrix..

Is there a way to get rid of that Textbox...?
I have tried deleting it , but does not go away...

Thanks!sqlsql

Combining multiple reports into one print job

I have a vb program that uses many different crystal reports. They are all slightly complex and have subreports already in them. When the user wantes to print the whole project it comes out as 10 different print jobs. I would like to make it one print job or append consecutive reports together to create one report (programaticaly). I would appreciate any help. Thank you.Did you ever get a solution to this issue? I am trying to do a similar thing. If so could you send me some info.

Thanks.sqlsql

combine data from different records with same ID

I have a table contains comments. User scan create as many comments they wa
nt.
my job is to combine and rearrange all comments in order of dates and time.
acct date time Comments
-- -- -- ---
08 01/04/2001 170852 0Conveyed stips.
84 01/04/2001 173740 test!
84 01/04/2001 173812 test2!
02 01/04/2001 180502 spoke to mbr and nd
01 01/05/2001 115548 joint life
01 01/05/2001 115550 Please fund loan.
18 01/05/2001 185220 Sent
18 01/05/2001 185238 Sent completed application
Desired Result:
acct Comments
----
--
08 Conveyed stips. 01/04/2001: 170852
84 test! - Ford 01/04/2001: 173740 test2! 01/04/2001: 173812
02 spoke to mbr and nd 01/04/2001: 180502
01 joint life 01/05/2001: 115548 Please fund loan. 01/05/2001: 1155
50
18 Sent 01/05/2001: 185220 Sent completed application 01/05/2001:
185238
Thanks in Advance,
CulamUse a document management system (textbase)and not SQL system.|||You haven't stated what datatypes these columns are.
Do type conversions as required and use the concatenation operator ( + ) to
achieve the results you want. What seems to be the difficulty in doing so?
Anith|||I converted all the data to VARCHAR and using a operator (+) to combine data
,
but I need to roll up all records with same id into one record. That is
what I need help in.
"Anith Sen" wrote:

> You haven't stated what datatypes these columns are.
> Do type conversions as required and use the concatenation operator ( + ) t
o
> achieve the results you want. What seems to be the difficulty in doing so?
> --
> Anith
>
>|||I see. This does not seem to be a right job for SQL Server. One good
approach to such problems is to retrieve the resultset and leverage the
string concatenation and loop-like functionality of a client programming
language to create the result.
The approaches in SQL are all more or less complex and cumbersome. Some of
the such hacks can be found at:
http://groups.google.ca/groups?selm...FTNGP09.phx.gbl
Anith

COM security Policy

I am trying to create a data extension and this extension has to access a
COM+ application in order to run as a domain user to access files on the
network but every time I try running this code it errors out with a security
permission error. How do I setup permission to access the COM+ application?
--
RYAN SCHOUTENNever mind this is not an issue
--
RYAN SCHOUTEN
"RYAN SCHOUTEN" <ryanttr@.yahoo.com> wrote in message
news:uyU736ERFHA.2744@.TK2MSFTNGP10.phx.gbl...
> I am trying to create a data extension and this extension has to access a
> COM+ application in order to run as a domain user to access files on the
> network but every time I try running this code it errors out with a
security
> permission error. How do I setup permission to access the COM+
application?
> --
> RYAN SCHOUTEN
>

columnname in sql

how to select all column names from sql whose columnvalue is '1' for a specific user

Could you please post the table structure? Ideally the Create Table SQL.

Thanks,

Matt

|||

Thankyou for the response Matt.

Actually the entire column in the table is dynamically created.it will grow everytime wen user add .but it wont happen frequently.so i cant post that table struct.let me describe my needs with this sample

Create table sample(id varchar(10), item1 bit,item2 bit,item3 bit,item4 bit)...... and goes on

now i need to select all column names ,actually i can do this.

but i jus need to select columns whose value is one.

eg.

id item1 item2 item3 item4

zzz 1 0 0 1

yyy 0 1 1 1

my sql qurey or SP should return the column name for specific id whose value is one.

lets say id=zzz it should returnItem1 and item4(i meant column name)

if id = yyy it should return item2,item3,item4(i meant column name)

Thanks in advance

|||

HiTweety@.net,

As far as i know you need to use cursor in your case. But where are planing to store those column names??

See the following codes i've written for you:

declare @.item1int,@.item2int,@.item3int,@.item4intdeclare cursor_test cursorforselect * from test_tblopen cursor_testfetch next from cursor_testwhile @.@.fetch_status=0beginfetch next from cursor_testinto @.item1,@.item2,@.item3,@.item4if(@.item1=1)print'column 1 name'if(@.item2=1)print'column 2 name'if(@.item3=1)print'column 3 name'if(@.item4=1)print'column 4 name'endclose cursor_testdeallocate cursor_test

BTW, if possible, i would suggest you write the code in your application(using c# or other .net languages instead of T-SQL)

Hope my suggestion helps

|||

Hi Thanks for the Response,

i managed to solve this issue by retreiving the column names in table first and then checking the value for one using vb program

Column Sorting

I want to be able to allow a user of a report to sort the records returnedc in a table control based on the column heading they select.

ie say the report returns a list of properties as row headings then a list of cost categories as column headings with cost values as the data. I want the user to be able to click on a column heading say a cost castegory of 'Cleaning' i then want the report to order the properties by 'Cleaning Value'

Is that possoble?
cheers.... anyone...

In the June 2005 CTP, there's a new feature called Interactive Sort that does precisely what you're talking about. To access it, open up the properties of the header's text box and navigate to the Interactive Sort tab of the dialog.|||Sorry whats June 2005 CTP is it to do with SQL Reporting Services?

Regards,
Geoff|||

CTP - Community Technology Preview. It's a post-Beta release of the new Reporting Services 2005 due out this year. More information at -> http://www.microsoft.com/sql/2005/productinfo/ctp.mspx

As far as i know, the interactive sort was not available previous to this version. I was using a previous beta release of the 2005 edition and it did not have this capability.

|||cheers for this i guess i will have to just wait!

Column permissions

1. I denied some user the select permissions on some columns in a table.
However, this user is able to see these columns through the views (SQL
Server 2000). Is there a way to automatically deny the permissions on the
same columns in all the views or it's necessary to set the permissions in
every view?
2. If a column is unchecked in the permissions window, is this the same as
permission granted?
3. I've read somewhere that users should not have access to the tables, only
have access to the views. What is a reason for this recommendation?
Thanks.1) Because of ownership chaining, if the user can read the column in
the view, they can read the column in the table, despite the
permissions being denied.
2) If the column is unchecked, that is permissions revoked, if it is
checked that is permission granted and the red X is denied.
3) This recommendation is based on a few things. First, the problem you
are seeing is remedied by using only views. You don't have to manage
certain permissions twice if everything is in views. Additionally, this
creates an abstraction layer, where the table can be changed and as
long as you update the view, the user never knows about the change.
This also applies to applications. Additionally, direct table access
opens up holes that a DBA may forget to plug. For instance, adding a
new "confidential" column and not denying permission on it. If you
are using views, (not views that just select * from a table), then the
new column will not be available until you explicitly make it so.
Eric Johnson
Consortio Services
Vik wrote:
> 1. I denied some user the select permissions on some columns in a table.
> However, this user is able to see these columns through the views (SQL
> Server 2000). Is there a way to automatically deny the permissions on the
> same columns in all the views or it's necessary to set the permissions in
> every view?
> 2. If a column is unchecked in the permissions window, is this the same as
> permission granted?
> 3. I've read somewhere that users should not have access to the tables, on
ly
> have access to the views. What is a reason for this recommendation?
> Thanks.

column names, table name, user name

Hi;
I am new to SQL2000 & T-SQL
Is there a T-SQL SELECT statement I can use to query these info :
1) existing column names of a table (select * will give col name & all data
rows, I only wish to see columns names)
2) data types of columns (object viewer can do this, but is there a SELECT s
tatemtent to get these info?)
3) existing tables in a database (system & users' tables)
4) existing database users in a databaseFor the first three, you can use sp_help. For the last one, you can use
sp_helpuser.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||1) you could do - select * from table where 0=1
or to get all columns - select column_name from
INFORMATION_SCHEMA.columns
2) You can get all of this from INFORMATION_SCHEMA.columns
3) select table_name from INFORMATION_SCHEMA.tables will get you all user
tables
or select name from sysobjects where type = 's' or type = 'u' to get all
tables
4) select * from sysusers where issqluser = 1
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||Column names and other pertinent info is stored in system table syscolumns.
You can get to your first two questions joining sysobjects with syscolumns.
Question 3 is sysobjects only. Question 4 is sysusers. But after replyin
g I would like to know what
exactly you're trying to learn.
Since you can't answer this question I'm curious what you're trying to do.
What you're trying to learn is in the system tables.

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
Group is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:

> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what?
> Mangesh
>
|||"Mangesh Deshpande" schrieb:

> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.
|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller
|||Thanks a lot.
"doller" wrote:

> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:

> Group is a reserved word. It's a best practice not to use column names th
at
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:

> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:

> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

column management help

I have to read 25 usernames from a single users row

than display to that user the 25 profiles.

I assume this is possible with a subquery right?

Now do i have to make 25 columns 1 for each username or

could it read user1 user2 user3 ? and how please.. Thanks much

Can you explain a little more about how these usernames are currentlystored in your database, and why you need to make them columns? Sounds like they're better suited to being rows, instead ofcolumns.
|||

i have an infinite number of rows with primary key username

each column needs to store up to 25 usernames who sent a message

than im thinking a subquery can display the info for each username for that exact person.

info to be display is picture and some things about themself

How... im still trying to figure out.

i have this in one table

Username - primary key

user1, user2 user3 as columns - for each person that made an action on that users page

store them in a column

My question is: There a simplier way to read these users instead of making a bunch of columns for each user who made an action?

|||

Guys would making subtables cause trouble in the long run?

If i make a sub table for every user?

|||

ck1mark wrote:

My question is: There a simplier way to readthese users instead of making a bunch of columns for each user who madean action?

Hi,
Yep, there is. Any time you have an inclination of creating a tablewith numbered fields like that, it is a huge warning flag that thestructure is not normalized. Almost certainly, a better way is to havea Message table (I'm guessing here on what table names will make sense)that has a MessageID and whatever other fields you need to have tostore whatever information.
Then you have a child table with a structure something like this:
UserID -- Could be the user name or a artificial primary key
MessageID -- A foreign key into the Message table
This way storage is more efficient, you don't have to worry about theone message you'll occasionally get that has 26 users, and SQL isdesigned to handle related tables like this.
Make sense?
Don
|||

help me write this please im having trouble

i need to insert the select statement values

INSERT INTO table1
VALUES username, photo1

(SELECT r.username, photo1
FROM table2 r, table3 p
WHERE r.username = '" & user.identity.name & "' AND r.username = p.username)

|||You might want to check Books Online for the syntax. But it should besomething like this (untested, so may still need some tweaking):

INSERT INTO table1 (username, photo1)
(SELECT r.username, photo1
FROM table2 r, table3 p
WHERE r.username = '" & user.identity.name & "' AND r.username = p.username)

I'm not quite sure what the context is for this statement, so the quotes might need more tweaking as well.
Does that work? If not, what troubles are you having?
By the way, this is dangerous code because of SQL injection. Usinguser.identity.name may be safe, but only if you've made sure the namedoesn't have any bad stuff in it. It's always better to useparameterized queries.
Don

column formula

Hi everybody,

I have an company table and it has 2 columns, Company Code and User Code, I am incrementing "User Code" with "column identity" property of MS SQL Server 2K. I have different companies and those companies have different users. When I increment User Code one by one, of course it doesnt consider whether it is the same company or not.

Question 1: How can I satisfy this condition below?

EX:

company user
1---1
1---2
2---3
2---4
3---5
4---6

What I want is

company user
1---1
1---2
1---3
2---1
2---2
3---1
3---2
3---3

etc.


Question 2: I want to know that whether it is possible to do that by writing column formula or not?Question 1: How can I satisfy this condition below?
EX:
company user
1---1
1---2
2---3
2---4
3---5
What I want is
company user
1---1
1---2
1---3
2---1
2---2
3---1
Question 2: I want to know that whether it is possible to do that by writing column formula or not?

A1 One approach in supporting such a business requirement: one may implement a "key assignment" table that privately tracks and assigns user ID numbers for each company.

A2 It is not exactly clear what is meant by a "column formula"? However, the built in MS Sql Server 2k identity column support / functionality likely won't help much in implementing a typical "key assignment" table. (A "key assignment" table approach, as described in A1, would likely be implemented primarily using stored procedures / triggers, and / or user functions).|||/*
create table companyuser (
"user" int identity(1,1) primary key
,company int not null
)
*/

--ad 1
select
company
,newusernum=(select count(*) from companyuser cu2 where cu1.company=cu2.company and cu1."user"<=cu2."user")
,origusernum="user"
from companyuser cu1

--OR on large table
create table companyuserTMP (
"id" int identity(1,1) primary key
,origusernum int null
,company int not null
)
insert companyuserTMP(company,origusernum)
select company,"user"
from companyuser
order by company,"user"
select
tmp.company
,newusernum=tmp."id"-XXX."id"+1
,origusernum
from companyuserTMP tmp
join (
select "id"=min("id"),company
from companyuserTMP
group by company
) XXX on tmp.company=XXX.company
drop table companyuserTMP

--ad 2-- computed columns can use one row information only, use TR

column formula

Hi everybody,

I have an company table and it has 2 columns, Company Code and User Code, I

am incrementing "User Code" with "column identity" property of MS SQL Server

2K. I have different companies and those companies have different users. When

I increment User Code one by one, of course it doesnt consider whether it is

the same company or not.

Question 1: How can I satisfy this condition below?

EX:

company user
1---1
1---2
2---3
2---4
3---5
4---6

What I want is

company user
1---1
1---2
1---3
2---1
2---2
3---1
3---2
3---3

etc.


Question 2: I want to know that whether it is possible to do that by writing

column formula or not?You may want to add a third column for that information, so that you can have a column with PK for joining other tables to. Are you asking whether a script can be written to modify the user codes after they're entered, or as they're being entered?|||it's ok now, thanx for help

Column Expressions?

Is there any way to use User Defined Functions
in a column expression?

Hi,

Could you please be more specific? It's too vague to guess.

|||

Sure:

in the column expression you can do this:

SUM(parent(someRelationship).SomeNumericField)

I would like to do this:

StringConcat(parent(someRelationship).SomeTextField)

In the above 'StringConcat' would be an
aggregate method that I would define.

Thanks.

|||Off topic for Windows Forms General, moved to SQL Server Database Engine forum.
|||This really has nothing to do with the SQL Server database engine.
I'm not sure why it was moved. This question has do with datasets,
more specifically dataTables and their column definitions.|||

Somewhat of an answer can be found here

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=668775&SiteID=1

Colum Name - Alias

We are thinking of using general purpose column names in our application schema. We want to give the option to the end user to customize the filed names to fit their business . We want to build the functionality on the generic names so that it will work for multiple customes.

Example: We may want to have 10 Strings, 10 numbers and 5 booleans pre defined and reports running off of the table. The customer can name first number as pressure, second one for length and map their data to the table. Other customer can use the first number for temperature and the second one for width.

Is there a way to do it in SQL server w/o having a lookup table for column name aliasing?

Thanks

Option 1: Any Reporting application generally has provision to display a customized column names for the table reports.

Option 2: When you query the table - you can provide column alias for the columns queried for example:

SELECT Column1 AS Pressure, Column2 AS Length FROM TableName

Option 3: You can create multiple views over the base table and the created views can have appropriate column names.

Thanks,

Sankaranarayanan MG

Colour a cell in a report when mouse is over it

Hi to everyone,

Is it possible (and if so, how should it be done?) to change some settings (i.e. the background colour) when user puts the mouse over a cell?

Thanks in advance,

Jesus M

Sorry, let me be more specific. I am using Visual Studio 2005 to develop an RDL file so as to upload it to a Report Server. The control I am using is a Table, although I could use any other (Matrix, for example) if needed for acomplish this :-)

Thanks again

Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)

Hi -

I'm trying to generate a script for User Defined Functions and I get this error:

Generate Script Progress

- Determining objects in database 'MyDBName' that will be scripted. (Success)
Messages
* Prefetch objects failed for Database 'MyDBName' . (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Prefetch+objects+Database&LinkId=20476

ADDITIONAL INFORMATION:
Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.CollectionCannotBeModified&LinkId=20476

I'm using SQL 2005 - with SP1.

Any suggestion what am I missing....

Rakesh

Are you able to generate scripts for these objects using Management Studio? If so, then the problem is with your code, and it would be helpful if you could post the code to see your logic. If not, then there's a problem with your server.

Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)

Hi -

I'm trying to generate a script for User Defined Functions and I get this error:

Generate Script Progress

- Determining objects in database 'MyDBName' that will be scripted. (Success)
Messages
* Prefetch objects failed for Database 'MyDBName' . (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Prefetch+objects+Database&LinkId=20476

ADDITIONAL INFORMATION:
Collection cannot be modified when TextMode has been set (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.CollectionCannotBeModified&LinkId=20476

I'm using SQL 2005 - with SP1.

Any suggestion what am I missing....

Rakesh

Are you able to generate scripts for these objects using Management Studio? If so, then the problem is with your code, and it would be helpful if you could post the code to see your logic. If not, then there's a problem with your server.

Collation Problem - Migration 7.0 to 2000

Hi,
I have a SQL7 user database backup that I load into the
SQL2000 without problem. When the restore operation is
finished I got the database loaded with
Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command
alter database Glb
COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
the on-line book but I got this message
Line 1: Incorrect syntax
near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with
the same tempdb COLLATION to avoid further problem
like "Cannot resolve collation conflict for equal to
operation."This is a multi-part message in MIME format.
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Leave out the quotes:
alter database Glb COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
Hi,
I have a SQL7 user database backup that I load into the SQL2000 without problem. When the restore operation is finished I got the database loaded with Compatibility_52_409_300003 COLLATION.
How could I change the COLLATION set for that database?
I am trying to use this following command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in the on-line book but I got this message
Line 1: Incorrect syntax near 'SQL_Latin1_General_CP1_CI_AS'.
In fact, I am trying to do this to have my database with the same tempdb COLLATION to avoid further problem like "Cannot resolve collation conflict for equal to operation."
--=_NextPart_000_0101_01C35748.686076C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Leave out the quotes:
alter database Glb =COLLATION SQL_Latin1_General_CP1_CI_AS
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...Hi,I have a SQL7 user database backup that I load into the SQL2000 =without problem. When the restore operation is finished I got the database =loaded with Compatibility_52_409_300003 COLLATION.How could I =change the COLLATION set for that database?I am trying to use this following =command alter database Glb COLLATION 'SQL_Latin1_General_CP1_CI_AS' as =described in the on-line book but I got this messageLine 1: Incorrect =syntax near 'SQL_Latin1_General_CP1_CI_AS'.In fact, I am trying to =do this to have my database with the same tempdb COLLATION to avoid further =problem like "Cannot resolve collation conflict for equal to operation."

---

--=_NextPart_000_0101_01C35748.686076C0--|||Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Just curious, what is the database compatibility level? Have you =changed it to 80? Check out sp_dbcmptlevel in the BOL.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message =news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the ISQL too and put the database in the restricted use... and nothing.
Thanks in advance and if you have another suggestion, please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS
>-- >Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the >SQL2000 without problem. When the restore operation is >finished I got the database loaded with >Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book but I got this message
>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with >the same tempdb COLLATION to avoid further problem >like "Cannot resolve collation conflict for equal to >operation."
>
>
--=_NextPart_000_0172_01C3574D.102E6480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Just curious, what is the database =compatibility level? Have you changed it to 80? Check out sp_dbcmptlevel =in the BOL.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Marcelo Sponchiado" wrote =in message news:0e7301c3576d$ae=023d60$a301280a@.phx.gbl...Thanks TomI got the same error without the quotes. I tried in the =ISQL too and put the database in the restricted use... and =nothing.Thanks in advance and if you have another suggestion, please send it.>--Original Message-->Leave out the quotes:>>alter database Glb >COLLATION SQL_Latin1_General_CP1_CI_AS>>-- >Tom>>---=---->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server MVP>Columnist, =SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>>>"Marc=elo Sponchiado" wrote =in message news:0f3101c35768$1e=efe780$a101280a@.phx.gbl...>Hi,>>I have a SQL7 user database backup that I load into the >SQL2000 =without problem. When the restore operation is >finished I got the =database loaded with >Compatibility_52_409_300003 =COLLATION.>>How could I change the COLLATION set for that database?>I am trying =to use this following command >alter database Glb >COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in >the on-line book =but I got this message>Line 1: Incorrect syntax >near 'SQL_Latin1_General_CP1_CI_AS'.>>In fact, I am trying to =do this to have my database with >the same tempdb COLLATION to avoid =further problem >like "Cannot resolve collation conflict for equal to >operation.">>>

---

--=_NextPart_000_0172_01C3574D.102E6480--|||Unfortunately, changing the database's default collation won't change the
collation of any existing tables or columns; it only affects new tables
that are created in that database. In other words, it won't have any
effect on your collation conflict errors.
It is not trivial to change the collation of all the columns in an existing
database. If you have the option, probably the easiest solution by far is
to modify your stored procs slightly so that they use "COLLATE
database_default" when create temp tables. This will tell SQL that the
temp table's collation should be inherited from the current user database,
not tempdb. From that point forward your code will be "server
collation-agnostic", meaning you can move this database to any server with
any collation and the code will run fine. An example:
CREATE #temptbl (
col1 int,
col2 varchar(20) COLLATE database_default,
col3 nchar(4) COLLATE database_default
)
HTH,
Bart
--
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
From: "Tom Moreau" <tom@.dont.spam.me.cips.ca>
References: <0f3101c35768$1eefe780$a101280a@.phx.gbl>
<#J4n2n2VDHA.392@.TK2MSFTNGP11.phx.gbl>
<0e7301c3576d$ae023d60$a301280a@.phx.gbl>
Subject: Re: Collation Problem - Migration 7.0 to 2000
Date: Thu, 31 Jul 2003 10:18:19 -0400
Lines: 154
MIME-Version: 1.0
Content-Type: multipart/alternative;
boundary="--=_NextPart_000_0172_01C3574D.102E6480"
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4927.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4927.1200
Message-ID: <e78Jk62VDHA.3220@.tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.sqlserver.server
NNTP-Posting-Host: 142.108.149.181
Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:298816
X-Tomcat-NG: microsoft.public.sqlserver.server
Just curious, what is the database compatibility level? Have you changed
it to 80? Check out sp_dbcmptlevel in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in message
news:0e7301c3576d$ae023d60$a301280a@.phx.gbl...
Thanks Tom
I got the same error without the quotes. I tried in the
ISQL too and put the database in the restricted use...
and nothing.
Thanks in advance and if you have another suggestion,
please send it.
>--Original Message--
>Leave out the quotes:
>alter database Glb
>COLLATION SQL_Latin1_General_CP1_CI_AS
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Marcelo Sponchiado" <msponch@.terra.com.br> wrote in
message news:0f3101c35768$1eefe780$a101280a@.phx.gbl...
>Hi,
>I have a SQL7 user database backup that I load into the
>SQL2000 without problem. When the restore operation is
>finished I got the database loaded with
>Compatibility_52_409_300003 COLLATION.
>How could I change the COLLATION set for that database?
>I am trying to use this following command
>alter database Glb
>COLLATION 'SQL_Latin1_General_CP1_CI_AS' as described in
>the on-line book but I got this message
>Line 1: Incorrect syntax
>near 'SQL_Latin1_General_CP1_CI_AS'.
>In fact, I am trying to do this to have my database with
>the same tempdb COLLATION to avoid further problem
>like "Cannot resolve collation conflict for equal to
>operation."
>
>