How to free up the space ...

Hi all,
I have a table with column A char(1000) and then alter the table to
varchar(1000) and update tableA set A = rtrim(A)
I know a stupid way to free up the space by create a new table and move the
data to the new table. Is there a command to free up the empty space?
Thanks in advance!Hi
DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
"Atenza" <Atenza@.mail.hongkong.com> wrote in message
news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move
> the data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>|||Hi,
The update using rtrim was not needed here as SQL Server does not store
spaces after the data. If you insert 'Pink Floyd ' SQL Server only
stores 'Pink Floyd'. Changing to varchar was enough.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Atenza" wrote:
> Hi all,
> I have a table with column A char(1000) and then alter the table to
> varchar(1000) and update tableA set A = rtrim(A)
> I know a stupid way to free up the space by create a new table and move the
> data to the new table. Is there a command to free up the empty space?
> Thanks in advance!
>
>|||Hi Ben,
I have done few tests on char and varchar. I found that insert 'Pink Floyd
' is different from ''Pink Floyd' into varchar. SQL Server does not store
spaces after the data applied to SQLSever 2005? coz i am using SQLSever
2000, is this the reason?
In CASE 1, use char(100)
In CASE 2, use varchar(100) by insert 'Pink Floyd '
In CASE 3, use varchar(100) by insert 'Pink Floyd'
In CASE 4, use char(100) and alter to varchar(100)
In CASE 5, use char(100) and move to new table varchar(100)
In CASE 4, for new create data, i think it should be saved spaces. But for
the old data, it seems that spaces cannot be released.
It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or
i have misunderstand something?
Here is my test result:
CASE 1:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
db size 16128KB
CASE 2:
CREATE TABLE dbo.Table2
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table2 values('Pink Floyd ')
while (select count(*) from table2) < 100000
insert into table2 select * from table2
sp_spaceused table2
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 4552 KB 4488 KB 8 KB
56 KB
db size 5504KB
CASE 3:
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 values('Pink Floyd')
while (select count(*) from table3) < 100000
insert into table3 select * from table3
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
db size 4096KB
CASE 4:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 15240 KB 15200 KB 8 KB
32 KB
CASE 5:
CREATE TABLE dbo.Table1
(
col1 char(100) NOT NULL
) ON [PRIMARY]
insert into table1 values('Pink Floyd ')
while (select count(*) from table1) < 100000
insert into table1 select * from table1
sp_spaceused table1
name rows reserved data index_size
unused
-- -- -- -- --
--
Table1 131072 14792 KB 14768 KB 8 KB
16 KB
CREATE TABLE dbo.Table3
(
col1 varchar(100) NOT NULL
) ON [PRIMARY]
insert into table3 select rtrim(col1) from table1
sp_spaceused table3
name rows reserved data index_size
unused
-- -- -- -- --
--
Table3 131072 3144 KB 3136 KB 8 KB
0 KB
"Ben Nevarez" <bnevarez@.sjm.com> wrote in message
news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
> Hi,
> The update using rtrim was not needed here as SQL Server does not store
> spaces after the data. If you insert 'Pink Floyd ' SQL Server
> only
> stores 'Pink Floyd'. Changing to varchar was enough.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>|||> SQL Server does not store
> spaces after the data applied to SQLSever 2005?
For char, SQL Server always store the specified length. It pads the string with spaces.
For varchar, SQL Server by default store trailing spaces. Whether or not to do this depends on the
setting of ANSI_PADDING when the table/column is *created*.
Rebuilding the indexes on the table should give you back the space. If the table doesn't have a
clustered index, then you either have to create on (and possibly drop it), or export/import.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Atenza" <Atenza@.mail.hongkong.com> wrote in message news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Ben,
> I have done few tests on char and varchar. I found that insert 'Pink Floyd ' is different from
> ''Pink Floyd' into varchar. SQL Server does not store
> spaces after the data applied to SQLSever 2005? coz i am using SQLSever 2000, is this the reason?
> In CASE 1, use char(100)
> In CASE 2, use varchar(100) by insert 'Pink Floyd '
> In CASE 3, use varchar(100) by insert 'Pink Floyd'
> In CASE 4, use char(100) and alter to varchar(100)
> In CASE 5, use char(100) and move to new table varchar(100)
> In CASE 4, for new create data, i think it should be saved spaces. But for the old data, it seems
> that spaces cannot be released.
> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution or i have misunderstand
> something?
>
> Here is my test result:
> CASE 1:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> db size 16128KB
>
> CASE 2:
> CREATE TABLE dbo.Table2
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table2 values('Pink Floyd ')
> while (select count(*) from table2) < 100000
> insert into table2 select * from table2
> sp_spaceused table2
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
> db size 5504KB
>
> CASE 3:
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 values('Pink Floyd')
> while (select count(*) from table3) < 100000
> insert into table3 select * from table3
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
> db size 4096KB
>
> CASE 4:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> BEGIN TRANSACTION
> SET QUOTED_IDENTIFIER ON
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> SET ARITHABORT ON
> SET NUMERIC_ROUNDABORT OFF
> SET CONCAT_NULL_YIELDS_NULL ON
> SET ANSI_NULLS ON
> SET ANSI_PADDING ON
> SET ANSI_WARNINGS ON
> COMMIT
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_Table1
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> GO
> IF EXISTS(SELECT * FROM dbo.Table1)
> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
> GO
> DROP TABLE dbo.Table1
> GO
> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
> GO
> COMMIT
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>
>
> CASE 5:
> CREATE TABLE dbo.Table1
> (
> col1 char(100) NOT NULL
> ) ON [PRIMARY]
> insert into table1 values('Pink Floyd ')
> while (select count(*) from table1) < 100000
> insert into table1 select * from table1
> sp_spaceused table1
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
> CREATE TABLE dbo.Table3
> (
> col1 varchar(100) NOT NULL
> ) ON [PRIMARY]
> insert into table3 select rtrim(col1) from table1
> sp_spaceused table3
> name rows reserved data index_size unused
> -- -- -- -- --
> --
> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>
>
>
> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:utEoaQQkGHA.4672@.TK2MSFTNGP02.phx.gbl...
> Hi
> DBCC DBREINDEX (if your table has indexes) , or DBCC SHRINKFILE
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:eVFE1vPkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>|||thank you very much! it works!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uKxJ6QSkGHA.4652@.TK2MSFTNGP05.phx.gbl...
>> SQL Server does not store
>> spaces after the data applied to SQLSever 2005?
> For char, SQL Server always store the specified length. It pads the string
> with spaces.
> For varchar, SQL Server by default store trailing spaces. Whether or not
> to do this depends on the setting of ANSI_PADDING when the table/column is
> *created*.
> Rebuilding the indexes on the table should give you back the space. If the
> table doesn't have a clustered index, then you either have to create on
> (and possibly drop it), or export/import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Atenza" <Atenza@.mail.hongkong.com> wrote in message
> news:utQ2DbRkGHA.3816@.TK2MSFTNGP02.phx.gbl...
>> Hi Ben,
>> I have done few tests on char and varchar. I found that insert 'Pink
>> Floyd ' is different from ''Pink Floyd' into varchar. SQL Server does not
>> store
>> spaces after the data applied to SQLSever 2005? coz i am using SQLSever
>> 2000, is this the reason?
>> In CASE 1, use char(100)
>> In CASE 2, use varchar(100) by insert 'Pink Floyd '
>> In CASE 3, use varchar(100) by insert 'Pink Floyd'
>> In CASE 4, use char(100) and alter to varchar(100)
>> In CASE 5, use char(100) and move to new table varchar(100)
>> In CASE 4, for new create data, i think it should be saved spaces. But
>> for the old data, it seems that spaces cannot be released.
>> It seems that only CASE 5 can free up spaces. Is CASE 5 the only solution
>> or i have misunderstand something?
>>
>> Here is my test result:
>> CASE 1:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> db size 16128KB
>>
>> CASE 2:
>> CREATE TABLE dbo.Table2
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table2 values('Pink Floyd ')
>> while (select count(*) from table2) < 100000
>> insert into table2 select * from table2
>> sp_spaceused table2
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 4552 KB 4488 KB 8 KB 56 KB
>> db size 5504KB
>>
>> CASE 3:
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 values('Pink Floyd')
>> while (select count(*) from table3) < 100000
>> insert into table3 select * from table3
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>> db size 4096KB
>>
>> CASE 4:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> BEGIN TRANSACTION
>> SET QUOTED_IDENTIFIER ON
>> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> SET ARITHABORT ON
>> SET NUMERIC_ROUNDABORT OFF
>> SET CONCAT_NULL_YIELDS_NULL ON
>> SET ANSI_NULLS ON
>> SET ANSI_PADDING ON
>> SET ANSI_WARNINGS ON
>> COMMIT
>> BEGIN TRANSACTION
>> CREATE TABLE dbo.Tmp_Table1
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> GO
>> IF EXISTS(SELECT * FROM dbo.Table1)
>> EXEC('INSERT INTO dbo.Tmp_Table1 (col1)
>> SELECT CONVERT(varchar(100), col1) FROM dbo.Table1 TABLOCKX')
>> GO
>> DROP TABLE dbo.Table1
>> GO
>> EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
>> GO
>> COMMIT
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 15240 KB 15200 KB 8 KB 32 KB
>>
>>
>> CASE 5:
>> CREATE TABLE dbo.Table1
>> (
>> col1 char(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table1 values('Pink Floyd ')
>> while (select count(*) from table1) < 100000
>> insert into table1 select * from table1
>> sp_spaceused table1
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table1 131072 14792 KB 14768 KB 8 KB 16 KB
>> CREATE TABLE dbo.Table3
>> (
>> col1 varchar(100) NOT NULL
>> ) ON [PRIMARY]
>> insert into table3 select rtrim(col1) from table1
>> sp_spaceused table3
>> name rows reserved data index_size
>> unused
>> -- -- -- -- --
>> --
>> Table3 131072 3144 KB 3136 KB 8 KB 0 KB
>>
>>
>>
>> "Ben Nevarez" <bnevarez@.sjm.com> wrote in message
>> news:ABD23E33-63E4-4C3B-A0A1-179A2B5E2706@.microsoft.com...
>> Hi,
>> The update using rtrim was not needed here as SQL Server does not store
>> spaces after the data. If you insert 'Pink Floyd ' SQL Server
>> only
>> stores 'Pink Floyd'. Changing to varchar was enough.
>> Ben Nevarez, MCDBA, OCP
>> Database Administrator
>>
>> "Atenza" wrote:
>> Hi all,
>> I have a table with column A char(1000) and then alter the table to
>> varchar(1000) and update tableA set A = rtrim(A)
>> I know a stupid way to free up the space by create a new table and move
>> the
>> data to the new table. Is there a command to free up the empty space?
>> Thanks in advance!
>>
>>
>

How to format measure descriptions

When using a matrix in Reporting Services to design a
report based on a cube, you are presented with a detail
column for your measure and a column to the left of that
for the description of the measure. My report has several
rows of measures that present (for example) both units
and dollars. I want to be able to leave a blank line
between the units section and the dollars section. I also
want to be able to place a heading for each section. The
preference is that the heading be off to the left rather
than above -- but i am not able to add a second
description column. So I placed text in the description
column to simulate the desired formatting.
For example:
Units Prod1
Prod2
Prod3
Total
Sales Prod1
Prod2
Prod3
Total
This approach looks fine in the designer. But when i
publish the report and view it in IE, all extra spaces
are gone (both horizontally and vertically) so the
descriptions look as below:
Units Prod1
Prod2
Prod3
Total
Sales Prod1
Prod2
Prod3
Total
Any suggestions on how to get a better presentation of
the descriptions?Try Using Padding ?
"Daniel Edwards" <DanielEdwards@.discussions.microsoft.com> wrote in message
news:A5663DB5-D5DD-4132-AEA5-A9D19EDFE713@.microsoft.com...
> When using a matrix in Reporting Services to design a
> report based on a cube, you are presented with a detail
> column for your measure and a column to the left of that
> for the description of the measure. My report has several
> rows of measures that present (for example) both units
> and dollars. I want to be able to leave a blank line
> between the units section and the dollars section. I also
> want to be able to place a heading for each section. The
> preference is that the heading be off to the left rather
> than above -- but i am not able to add a second
> description column. So I placed text in the description
> column to simulate the desired formatting.
> For example:
> Units Prod1
> Prod2
> Prod3
> Total
> Sales Prod1
> Prod2
> Prod3
> Total
> This approach looks fine in the designer. But when i
> publish the report and view it in IE, all extra spaces
> are gone (both horizontally and vertically) so the
> descriptions look as below:
> Units Prod1
> Prod2
> Prod3
> Total
> Sales Prod1
> Prod2
> Prod3
> Total
> Any suggestions on how to get a better presentation of
> the descriptions?
>

How to format a part of a textbox

Hey all,

Does anyone know a way to format part of a textbox ? I need just the column value to be bold.

Like this:

"Just a static text Some BOLD Text other static text..."

My expression:

="Just a static text " + Fields!FieldName.Value + " other static text..."

Is there a way to apply formatting just to Fields!FieldName.Value ?

Thanks!

myLabel.Text ="hello " +"<b>" + Fields!FieldName.Value + "</b>" +" not bold";

the above will work just fine for you (in VB.net)

|||

U can also apply CSSClass via <SPAN></SPAN>

|||

The above suggestions can't work because it's aReporting Services textbox and not just a regular asp.net textbox

|||

Well, you may not like this suggestion...

how about having three text boxes. Set the middle textbox to be bold.

|||

looks like thats the only option... i know u can type in alt+89 will give u Y but i couldnt find something that will make something bold... if u can put latin characters in then u can some those keystrokes.

Hope this Helps.

Regards

Karen

|||

Hi,

Thanks for the suggesion :)

I have tried it. It's also not an option, when I place 3 textboxes I have an annoying spaces between them and an alignment problem.

I suppose I'll have to leave it as normal text and to give up the "bold idea" :(

|||

Hi,

From your description, you want to format a part of contents in textbox of reporting service, right?

Unfortunately, Currently, Reporting Services doesn't support rendering "HTML as HTML" in order to avoid the HTML injection attack.

Now I just find two possible workarounds for your from russch's blog.

Post-processing: After a report has been fully rendered, intercept the document and re-process it, turning the HTML (displayed as a string) into HTML which is "really" displayed.

Custom Control (2005 only): One could theoretically build a custom control in 2005 which takes the HTML/RTF, saves the rendered output as an image, and then displays the image inside the custom report item. This looks really hard as the managed GDI namespace doesn't give us anything to easily approach this sort of scenario.

Quoted from Russch's blog.
http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx

Thanks.

|||

Thank you very much!!!

I'll try this.

sql

How to force a page break on a multi-column report ?

When I use the PageBreakAtEnd on the table or on a group in the table, all it does is create a new column of the column report.

I'd want it to start a new page, how can I do this ? Should I work around this issue using code ?

Background: What I need to achieve is a report with 2 columns where the list of products in category 1 are listed in the left column and then snake to the 2nd column on the same page, then to column 1 on page 2, column 2 on page 2, etc...

When it comes to category 2, it should start a fresh new page regardless of whether the previous product was rendered in column 1 or column 2.

I get the snaking to work using the "Columns" property of the report Body. However page breaks do not start a new page, they just start a new column.

What if you created an outer group which evaluated the same expression, and put the page break on that group instead? Would that work for you?|||

Hi Alan,

If u want page break after specifi records on report u can try by modifying the(.rdl) file which is XML and set the tag property as specific inches might work for u.For that u have to set the tag value 2 in or any 'x' in where tag name is 'InteractiveHeight'.

(<InteractiveHeight>11in</InteractiveHeight>)

I hope this'll help u for forcing the page break.

Regards,

Vikas

sql

How to flow a table into a second column

s it stands, my table skips the second column on the page and flows
into the first column on the second page, and then the first column on
the third page, ad nauseum. I want the table to fill up both columns on
the page before breaking to the next page. All the docs I've found
state that flowing into multiple columns like this happens
automagically. If so, what's the incantation I'm missing?
This is being attempted on VS2005 Beta 2 on XP.
TIA,
NoelI am having the same problems. I discovered that if you right click on the
report preview and select "print preview" from the pop-up menu, the columns
are magically displayed.
Now how does one publish this report so that the multi-columns display
properly on the web?!!
"Noel Weichbrodt" wrote:
> s it stands, my table skips the second column on the page and flows
> into the first column on the second page, and then the first column on
> the third page, ad nauseum. I want the table to fill up both columns on
> the page before breaking to the next page. All the docs I've found
> state that flowing into multiple columns like this happens
> automagically. If so, what's the incantation I'm missing?
> This is being attempted on VS2005 Beta 2 on XP.
> TIA,
> Noel

How to flag a row and allow max one row flagged in the table.

Hi,

I am new to SQL.

which is the best way to flag a row?

Create a bit column IsFlagged?

Additionally, I want to create a table-level constraint that allows max 1 row flagged true in the whole table. I can't nest a select expression in the CREATE TABLE statement CONSTRAINT clause that counts flagged rows.

So how do I do this?

Appreciate the help.

you can do something like this -

create table FlaggableDomain(ID int unique not null, Name sysname primary key not null)

populated something like this

ID DomainName
1 'person'
2 'place'
3 'thing'

track your flags with this -

create table FlaggedDomain (ItemID int not null, DomainID int not null, primary key (ItemID, DomainID))

to flag a row Person row -

insert into FlaggedDomain (ItemID, DomainId)
select person.id, domain.ID from
person
where name = 'SOME NAME' and
FlaggableDomain.DomainName = 'person'

to clear -

delete from FlaggedDomain where DomainId in
( select idFlaggableDomain
from FlaggableDomain
where DomainName = 'person')

to get flagged person -

select person.*
from person p inner join FlaggedDomain f
on p.id = f.ItemID

|||

Thanks for taking the time Blair,

Let me summarize what I made out of your reply:

1.Create a table of flags(FlaggableDomain)
2. Make a linking table(FlaggedDomain) linking flags with any other table(person), whose rows I want to flag.
3. By having a composite primary key for the linking table(FlaggableDomain) you ensure the person table can only have one row that has the flag 'person'.

This definitely is a solution, and probably the most professional one. However, I now face having to do 2 extra tables whenever I want to implement flagging logic, or have a central depository of many dissimilar flags and always having to join to this depository to be able to use the flags. In the former a table bloat, in the latter clunky code. Not quite like an enumeration in vb/c# which groups constants,hopefully, logically, depending on the author :).

There is one nice feature about your way, that is in a table that can have mixed rows of things/persons/places, more than one type of flag can be stored in the same column. Thus I can make sure max 1 'thing', max 1 'place', and max 1 'person' rows exist in that mixed table. I don't have that need now but it is a neat way.

A not so nice feature: not just any table's rows can be flagged this way. Only tables which have a simple(1 column), int primary key, which is the most common one but there are exceptions and they wouldn't be flaggable this way.

What about table level constraint?
I know I couldn't nest a select statement (and count) inside the constraint expression. Are there any options along those lines?

Forgive me if I am discovering the wheel out loud.

|||

However, I now face having to do 2 extra tables whenever I want to implement flagging logic, or have a central depository of many dissimilar flags and always having to join to this depository to be able to use the flags. In the former a table bloat, in the latter clunky code.

the code is not clunky at all. . . make views from simple select statements.

Always bear in mind that a primary key is a 'tuple' that defines a unique entity within a domain. Whereever possible, it should be a piece of data that you can look at without knowledge of the db and know what it is (natural key) . And as you noted, often times the primary key is compound and creating a foreign key reference would be unwieldly. When this is the case, the approach is to use what is called a unique, non-null "surrogate key" and propagate that value in foreign key references. You might find it useful to use uniqueidentifier fields. UniqueID's are cheap to create and generatate.

Tables are cheap to create too. Joins are cheap too.

Databases are geared to work with them - as long as the indexes are properly defined.

I am not sure if a table constraint is possible. Even if it were, it would take a total table scan to assure that the constraint we enforced.

google "Surrogate Key"

|||

I looked into surrogate keys and will definitely rethink doing composite keys in the future.

Learned a lot from you!

Thanks a bunch.

|||

cheers!

|||

Blair,

Can I milk you for a bit more advice?

Is this type of flagging appropriate to implement data versioning?

say a sports league. A player can be active in just one team, but I want to keep prior team membership in a foreign key table. Only one of those team memberships would be flagged 'current' as we discussed. The rest are just stale versions, but I want to keep that history data.

When the player moves to another team, it would have to be a transaction removing the flag from the old team, and flagging the new one?

Is this the right way of doing versioning?

Carl

|||

I don't know if versioning is the right term. I would consider this a Transaction. Note: i am using this in the natural sense of the word, not database concurrency management.

Legend: Table(KeyFields, SurrogateKeyField, SupplementalField), with foreign keys marked by Field -> Table(KeyField) and I am using integer ID's for brevity. ID data type is irrelevant with the condition that it is easily generated.

First, Active Status on a team would be enforced by Roster(PlayerID, TeamID)

Next, I would have a table TransactionType(TransID, TransName, TransAbbr)

this would be populated by something like:

1, Draft, DR
2, Free-Agent, FA
3, Injured, IR
4, Disabled, DL
5, Trade Out, TO
6, Trade In, TI
7, Released, RE

I would create a PlayerHistory table to track the status changes of players -

PlayerHistory(HistoryID, PlayerID -> Player(PlayerID), TeamID -> Team(TeamID), TransID -> TransactionType(TransID), DateOfEntry , RelatedHistory -> PlayerHistory(HistoryID))

Note the self reference on player history, this would be used for trades - Each trade out has a related trade in. For all other entries in a players history, the related history would be null.

Say team1 traded player 1 to team 2 for player 2 on 1/1/2006 -

1, 1, 1, 5, 1/1/2006, 2
2, 1, 2, 6, 1/1/2006, 1
3, 2, 2, 5, 1/1/2006, 4
4, 2.,1, 6, 1/1/2006, 3

This could get more elaborate - TradeMaster (TradeID, AgreementDocument)

TradeHistory(TradeID, PlayerHistoryID -> PlayerHistory(HistoryID))

This could be used to track the Trade-Outs that were affected by the aggreement. From our example above:

TradeMaster might be:
1, "Team 1 will give Team 2 a first round draft pick next year"

And TradeHistory would simply be:
1, 1
1, 3

You could trigger off of TradeHistory to automatically insert PlayerHistory and update Roster

Now you might be thinking, as you said "Table Bloat." Tables and Disk-Space is cheap. What is expensive is processor time! You want to minimize the amount of clock cycles that need to be executed, both in the client application or the database server. Inserting or changing data that is indexed or constrained is expensive. Get it in the table in a way that minimizes changes to indexed fields. If you have to manipulate data that is indexed or constrained, you want to do it once, if possible.

Finally, it only takes the execution of 3 instructions to retrieve a set of rows based on index. Indexes on bit fields give no benefit.

Other things to google "Normal-Form" and "DKNF" - Note: DKNF is an ideal, strive for it, but it is not always feasible.

Database Theory is as much an art as it is a science. People get PhD's in Database Therory, but there is no "Right" model for a given Domain Set, but there are models that are more proper than others. I liken it to the game othello, "A minute to learn, a lifetime to master."

Oh yeah! Don't forget to eat and sleep! (Yeah, as if you will be able to sleep once you get started thinking about your data model)

Don't get me started on the term "Normal" and it roots in mathematics.

Good luck and have fun.

|||

Thanks Blair,

You answered even my next couple of questions.

Good advice re the sleep bit!

Cheers

How to fix DT_Text and DT_NText Read Only in Script Component

I have a script component that I have written and works as long as the Output columns on the script are string types. When I change the output column type to text (since the size could be essentially unlimited) it gives an error in the script component that the property is read only.

Here is the code line that fails with Property Payments is read only.

Output0Buffer.Payments = fieldValues(i)

If I change the column payments to DT_Wstr it works without issue, but I want to use text incase the value is large.

Here is the error if you try to run the actual script even though I know it has an error.

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [Script Component [85]]: Error 30526: Property 'Payments' is 'ReadOnly'.
Line 86 Column 13 through 69
Error 30526: Property 'Ops' is 'ReadOnly'.
Line 155 Column 13 through 65

Error at Data Flow Task [Script Component [85]]: Error 30526: Property 'Payments' is 'ReadOnly'.
Line 86 Column 13 through 69
Error 30526: Property 'Ops' is 'ReadOnly'.
Line 155 Column 13 through 65

Error at Data Flow Task [DTS.Pipeline]: "component "Script Component" (85)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

Try explicitly calling SetString() on the column. Any better?

Thanks
Mark

|||First things first, make sure the input column usage type is set to Read/Write.

The reason it doesn't work is that Blob data types have a different interface in pipline script components.

Then, to set to value to a text field (DT_TEXT or DT_NTEXT) in a pipeline script component, use AddBlobData(), as in:

Imports System.Text
...

Output0Buffer.Payments.AddBlobData(Encoding.Unicode.GetBytes(SomeStringHere))|||I had just figured it out before this post.. but my code was way worse... yours works well and is clean. Thanks!

How to findout if the column is Unique or Key?

---

I use sp_columns to retrieve colum information of a table.
But, it doesn't return the columns' index information such as Key or Unique..

How can I get these info along with those from sp_columns?

You want to take a look at sp_helpindex, sp_pkeys, sp_foreignkey stored procedures. Also, you want to take a look at info views (i.e. information_schema.*).

How to find which stored procs and UDFs reference a column

How can I list the stored procedures and user-defined functions that reference a given column? I could search ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES for '%MyColumnName%' but MyColumnName is not always unique.

Thanks.

Which version of sql server are you using ?|||SQL Server 2000.|||You can try the sql server specific system table "sysdepends". Look it up in Books Online. I am not aware of an INFORMATION SCHEMA view that will help you in determining such references.|||

The sysdepends table seems helpful, but it only gives the table, not the columns. Here's the query I used:

SELECT sp.name as StoredProc, dep.name AS DependentObject
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id AND sp.xtype = 'P'
INNER JOIN sysobjects dep ON dep.id = sd.depid

Any further clues to linking the columns? (I suppose I could query for occurrences of the column name within the stored procedures depending on the column's table, but that's an approximation, since column names in the procedure text could be from a different table.)

|||

The depnumber column in sysdepends should give you the column id of the table that the procedure/function references. You can modify your query above to also join the depnumber column in sysdepends with the id column in syscolumns to get the name of the column.

Let me know if that works for you

|||

Seems I'm close, but the following query doesn't always yield complete results:

SELECT sp.name as StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

I'm trying to verify it as follows:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

Thank you for your consideration.

|||

If you are using dynamic sql to create the procedures/functions then sql server may not be able to track the references. Also, if you are using deferred name resolution [i.e create the procedure first and then create the table that is being referenced by the procedure] then sql server will not be able to track the references.

By any chance, is this the case?

|||

There are many cases under which the dependency tracking in SQL Server will not work. Here are the common cases:

1. Creation of dependent stored procedures in out-of-order fashion for example

2. Use of temporary tables or table variables in SELECT/DML statements will defer compilation of the statement so there will be no dependency information saved

3. Use of dynamic SQL

4. In case of permanent tables, if the object doesn't exist then deferred name resolution / compilation will kick-in at run-time and there will be no dependency information for this case also

Please take a look at the blog entry below for some queries on how to do this in SQL Server 2005 assuming that the dependency information is present.

http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx

So given the various restrictions it is probably unlikely that you have dependency information for most objects other than references, schema bound objects etc. You will have to mostly maintain this information manually or use your source code control system to scan your scripts assuming you use some keywords mechanism to tag scripts for example.

|||

I take it back -- after carefully comparing results, it looks like it's working great. Thanks so much for all your help!!

SELECT sp.name AS StoredProc
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE tbl.name = 'MyTable' AND col.name = 'MyColumn'
ORDER BY sp.name

Confirming/cross-checking query:

SELECT obj.[name], cmt.[text]
FROM syscomments cmt
INNER JOIN sysobjects obj ON obj.id = cmt.id
WHERE text like '%MyTable%'
AND [text] LIKE '%MyColumn%'

Reverse query:

SELECT sp.name as StoredProc, tbl.name AS [Table], col.name AS [Column]
FROM sysobjects sp
INNER JOIN sysdepends sd ON sp.id = sd.id
INNER JOIN sysobjects tbl ON tbl.id = sd.depid
INNER JOIN syscolumns col ON col.colid = sd.depnumber AND col.id = sd.depid
WHERE sp.name = 'MyProc'
ORDER BY tbl.name, col.name

|||I've discovered that the sysdepends table is not reliable/complete unless the stored procedure is created (or recreated) after the table(s) that it references. [Or at least, I can say that querying for columns referenced by a proc may not show up any, but then if the proc is dropped and recreated, the same query can now show its column dependencies.]|||

As Umachandar pointed out, there are instances where sysdepends will not work correctly.

|||Recreating SPs or altering SPs are costly operations since they will block access to the SP metadata. So you have to probably schedule this during a maintenance window. And if you don't have a mechanism to know which ones to alter then you will have to do this for all the SPs and this can take considerable time depending on the number of SPs/UDFs in the database. So there are several issues if you rely completely on the server dependency information.

How to find trailing spaces in a column

Is there a way to find out (Script, tool e.t.c) if there
are any trailing spaces in a varchar column '
Please correct me if I am wrong but if you specify a
varchar column with 255 and enter only 15 character, it
should occupy only 15 Right '
It seems like we have varchar columns in several tables
with trailing spaces. I am thinking it is an application
problem but could it be SQL Server problem '
Thanks for any help.........> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
One method:
SELECT MyColumn from MyTable
WHERE DATALENGTH(MyColumn) <> DATALENGTH(RTRIM(MyColumn))
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct (plus overhead).
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
Probably an application issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........|||Steve,
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct. However, the application might include trailing spaces in the INSERT statement. Whether SQL Server
will keep those of not depends on the setting of ANSI_PADDINGS when the table (or column) was created. Use
"sp_help tblname" to find out.
To find rows with trailing spaces, you can do something like (be prepared for a table scan):
SELECT * FROM authors WHERE SUBSTRING(REVERSE(au_lname), 1, 1) = ' '
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <anonymous@.discussions.microsoft.com> wrote in message news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........|||Thanks for all your help Dan & Tibor.....
>--Original Message--
>Is there a way to find out (Script, tool e.t.c) if there
>are any trailing spaces in a varchar column '
>Please correct me if I am wrong but if you specify a
>varchar column with 255 and enter only 15 character, it
>should occupy only 15 Right '
>It seems like we have varchar columns in several tables
>with trailing spaces. I am thinking it is an application
>problem but could it be SQL Server problem '
>Thanks for any help.........
>.
>sql

How to find trailing spaces in a column

Is there a way to find out (Script, tool e.t.c) if there
are any trailing spaces in a varchar column ?
Please correct me if I am wrong but if you specify a
varchar column with 255 and enter only 15 character, it
should occupy only 15 Right ?
It seems like we have varchar columns in several tables
with trailing spaces. I am thinking it is an application
problem but could it be SQL Server problem ?
Thanks for any help.........
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column ?
One method:
SELECT MyColumn from MyTable
WHERE DATALENGTH(MyColumn) <> DATALENGTH(RTRIM(MyColumn))

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
Correct (plus overhead).

> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem ?
Probably an application issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column ?
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem ?
> Thanks for any help.........
|||Steve,

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
Correct. However, the application might include trailing spaces in the INSERT statement. Whether SQL Server
will keep those of not depends on the setting of ANSI_PADDINGS when the table (or column) was created. Use
"sp_help tblname" to find out.
To find rows with trailing spaces, you can do something like (be prepared for a table scan):
SELECT * FROM authors WHERE SUBSTRING(REVERSE(au_lname), 1, 1) = ' '
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <anonymous@.discussions.microsoft.com> wrote in message news:1a84e01c44eec$32d0a740$a301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column ?
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right ?
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem ?
> Thanks for any help.........

How to find trailing spaces in a column

Is there a way to find out (Script, tool e.t.c) if there
are any trailing spaces in a varchar column '
Please correct me if I am wrong but if you specify a
varchar column with 255 and enter only 15 character, it
should occupy only 15 Right '
It seems like we have varchar columns in several tables
with trailing spaces. I am thinking it is an application
problem but could it be SQL Server problem '
Thanks for any help.........> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
One method:
SELECT MyColumn from MyTable
WHERE DATALENGTH(MyColumn) <> DATALENGTH(RTRIM(MyColumn))

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct (plus overhead).

> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
Probably an application issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:1a84e01c44eec$32d0a740$a301280a@.phx
.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........|||Steve,

> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
Correct. However, the application might include trailing spaces in the INSER
T statement. Whether SQL Server
will keep those of not depends on the setting of ANSI_PADDINGS when the tabl
e (or column) was created. Use
"sp_help tblname" to find out.
To find rows with trailing spaces, you can do something like (be prepared fo
r a table scan):
SELECT * FROM authors WHERE SUBSTRING(REVERSE(au_lname), 1, 1) = ' '
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <anonymous@.discussions.microsoft.com> wrote in message news:1a84e01c44eec$32d0a740$a
301280a@.phx.gbl...
> Is there a way to find out (Script, tool e.t.c) if there
> are any trailing spaces in a varchar column '
> Please correct me if I am wrong but if you specify a
> varchar column with 255 and enter only 15 character, it
> should occupy only 15 Right '
> It seems like we have varchar columns in several tables
> with trailing spaces. I am thinking it is an application
> problem but could it be SQL Server problem '
> Thanks for any help.........

How to find the Product of 4 Numbers in a Column?

Hi I have a table with this data

Description Number

-
Something1 2
Something2 3
Something3 4
Something4 6

I would like to find the product of 4 numbers (2*3*4*6) as output (144)

Please advice

Thanks

hi try this

SELECT *
INTO #Number
FROM (
SELECT 1 as Something,2 as Numbers UNION ALL
SELECT 1 as Something,3 as Numbers UNION ALL
SELECT 1 as Something,4 as Numbers UNION ALL
SELECT 1 as Something,6
) Number

DECLARE @.Product int

SET @.Product = 1

SELECT @.Product = @.Product * Numbers
FROM #Number

SELECT @.Product

DROP TABLE #Number|||

There was a request here to find a generic method of finding aggregate products of column entries:

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

Umachandar posted a reference to a site here that has a generic solution that can be used:

http://www.umachandar.com/technical/SQL6x70Scripts/Main21.htm

|||

Nice answer

But I am expecting answer without using those values in the select statement

Some times I have only 3 rows sometimes I have 6 or 7 rows

But I want to find the product of those numbers in a Column

Please advice

Thanks

|||hi, those were only sample data to test if the query works. you can then filter the values from the other columns.

umandars' aggregate product were great! two thumbs up umandar..|||

Here's little way to do what you ask.

Create a string that is the formula. Evaluate the string:

Code Snippet

declare @.Formula varchar(8000)

select @.Formula = isnull(@.Formula + ' * ' + convert(varchar,[Number]),convert(varchar,[Number])) from <DataTable>

select @.Formula

exec ('select ' + @.Formula)

|||

Simply superb

I got my answer from your query

Thanks a lot

With Regards

How to find the Froiengn key refrence column and refrence table through T-SQL

Hi,
I am creating a tool for generating SQL Scripts.
I want the sql statement for getting the table name , column name ,
reference table name ,reference column name of a particular foreign key.
In this case I know only the foreign key name.
Thanks and Regards,
SathiamoorthyOJ has written this script
create procedure usp_findreferences
@.tbname sysname=null
as
set nocount on
Print 'Referenced:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referenced_parent_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.constraint_name
join information_schema.constraint_column_usage c2 on
r.unique_constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
print ''
print 'Referencing:'
select c1.table_name,
c1.column_name,
fkey=r.constraint_name,
referencing_child_table=c2.table_name,
c2.column_name
from information_schema.constraint_column_usage c1 join
information_schema.referential_constraints r on
c1.constraint_name=r.unique_constraint_name
join information_schema.constraint_column_usage c2 on
r.constraint_name=c2.constraint_name
where c1.table_name=coalesce(@.tbname,c1.table_name)
order by case when @.tbname is null then c1.table_name else c2.table_name end
go
--test run
exec usp_findreferences 'Orders'
drop proc usp_findreferences
"Sathiamoorthy" <someone@.microsoft.com> wrote in message
news:OZP0tPJLGHA.536@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am creating a tool for generating SQL Scripts.
> I want the sql statement for getting the table name , column name ,
> reference table name ,reference column name of a particular foreign key.
> In this case I know only the foreign key name.
> Thanks and Regards,
> Sathiamoorthy
>|||Thank you very much.
sathyamoorthy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uGo7LUJLGHA.1180@.TK2MSFTNGP09.phx.gbl...
> OJ has written this script
> create procedure usp_findreferences
> @.tbname sysname=null
> as
> set nocount on
>
> Print 'Referenced:'
> select c1.table_name,
> c1.column_name,
> fkey=r.constraint_name,
> referenced_parent_table=c2.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.constraint_name
> join information_schema.constraint_column_usage c2 on
> r.unique_constraint_name=c2.constraint_name
> where c1.table_name=coalesce(@.tbname,c1.table_name)
> order by case when @.tbname is null then c1.table_name else c2.table_name
end
>
> print ''
> print 'Referencing:'
> select c1.table_name,
> c1.column_name,
> fkey=r.constraint_name,
> referencing_child_table=c2.table_name,
> c2.column_name
> from information_schema.constraint_column_usage c1 join
> information_schema.referential_constraints r on
> c1.constraint_name=r.unique_constraint_name
> join information_schema.constraint_column_usage c2 on
> r.constraint_name=c2.constraint_name
> where c1.table_name=coalesce(@.tbname,c1.table_name)
> order by case when @.tbname is null then c1.table_name else c2.table_name
end
> go
>
> --test run
> exec usp_findreferences 'Orders'
> drop proc usp_findreferences
> "Sathiamoorthy" <someone@.microsoft.com> wrote in message
> news:OZP0tPJLGHA.536@.TK2MSFTNGP09.phx.gbl...
>

how to find that the table column exists through program

i need to check that the column exist in the table if yes the update/insert value in the column else i need to add the new column like new browser name in the table.

after the search i found some thing like and make the procedure like

Dim daAs SqlDataAdapter, dsAs DataSet, dcAs DataColumn, foundsAsBoolean

Try

Conn.Open()

cmd =New SqlCommand(str, Conn)

da =New SqlDataAdapter(cmd)

ds =New DataSet

da.Fill(ds,"tbls")

ForEach dcIn ds.Tables(0).Columns

If UCase(colnames) = UCase(dc.ColumnName)Then

founds =True

ExitFor

Else

founds =False

EndIf

Next

Catch exAs Exception

Finally

Conn.Close()

EndTry

Return founds

sugesstions on this is required.................

how to find statistics for a column

I am trying to code a tsql routine to check for the existence of statistics before a column is dropped. If the stats exist I want to drop the stats first and then drop the column. I Can do this manually but need it in a script.
MikeUm...why?

blindman|||First reason is that the column cannont be dropped while the statistic is pressent.
Second, there are 15 db's that need to have the obsolete column(s) dropped.
Third, all steps are documented.
Fourth, gui's take 2 long. Just run a command.
Fifth, There are dozens of obsoleted columns that are being dropped in the next release.

mike

eg.sample of the errors generated.
Server: Msg 5074, Level 16, State 8, Line 9

The statistics 'HHAltCouponName' is dependent on column 'HHAltCouponName'.

Server: Msg 4922, Level 16, State 1, Line 9

ALTER TABLE DROP COLUMN HHAltCouponName failed because one or more objects access this column.

how to find out if a column has a default (sp_bindefault)

How do I find out if a column has a default bound to it (created with
sp_bindefault)?See if you can massage the query below:
SELECT c.name, OBJECT_NAME(c.cdefault)
FROM syscolumns c
WHERE OBJECTPROPERTY(c.cdefault, 'IsConstraint') = 1 ;
Anith|||Jacobus Terhorst wrote:
> How do I find out if a column has a default bound to it (created with
> sp_bindefault)?
It's undocumented and not supported, but you can use sp_MShelpcolumns.
Have a look at the text column.
create table A12345 (
col1 int not null default 5,
col2 nvarchar(10) not null default N'ABC')
exec sp_MShelpcolumns N'[dbo].[A12345]', @.orderby = 'id'
go
Drop Table A12345
Go
David Gugick
Imceda Software
www.imceda.com|||Thank you!
Jacobus Terhorst
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uMDkACpJFHA.1172@.TK2MSFTNGP12.phx.gbl...
> See if you can massage the query below:
> SELECT c.name, OBJECT_NAME(c.cdefault)
> FROM syscolumns c
> WHERE OBJECTPROPERTY(c.cdefault, 'IsConstraint') = 1 ;
> --
> Anith
>

how to find out column name with sql server store procedure

Hi
I have table which has Fields A,B,C and D for example. is thier any way to
retrive these field column as inline table function or store procedure.
thanks
some thing like select column_name from xxxx
thanksUSE pubs
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'autho
rs'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:B4AEB01C-6652-4681-AE71-6842385E4E17@.microsoft.com...
> Hi
> I have table which has Fields A,B,C and D for example. is thier any way to
> retrive these field column as inline table function or store procedure.
> thanks
> some thing like select column_name from xxxx
> thanks
>|||Or get it all in one variable with this technique:
USE pubs
GO
DECLARE @.fields VARCHAR(1000)
SELECT @.fields = ISNULL( @.fields, '' ) + column_name + ', '
FROM INFORMATION_SCHEMA.columns
WHERE table_name = 'authors'
-- Trim trailing space and comma
SET @.fields = SUBSTRING( @.fields, 1, LEN( @.fields) -1 )
SELECT @.fields
-- sp_columns gives some useful information too.
EXEC sp_columns 'authors'
"Tibor Karaszi" wrote:

> USE pubs
> SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'aut
hors'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amjad" <amjad@.discussions.microsoft.com> wrote in message
> news:B4AEB01C-6652-4681-AE71-6842385E4E17@.microsoft.com...
>
>

How to find matching profiles?

Users have to answer 17 simple yes/no questions and the answers are stored in an column for each question as tinyint 0/1 values.

At least that's what seems reasonable to me at the moment.

The table is under my control so I could change it if needed.

Now from several tenthousend or maybe hundreds of thousends of entries I need to find those with the closest match. Of course, I need all of the entries that have the exact same answers and this is no problem. But - at least if there are not enough full matches - then I need all records that have maybe 16,15,14... matches out of the 17 answers.

I have not yet the idea on how to handle this without quering 17*16 different answer schemes.

Hi,

I wouldn′t store the data denormalized. The better way to store it IMHO is to normalize the data, if you want closer machtes you can also setup a score for each answered question: Here is an extract of a possible solution:

CREATE TABLE Question
(
QuestionId INT
QuestionText VARCHAR(100)
)

CREATE TABLE Anwers
(
QuestionId INT
AnswerId INT
AnswerValue VARCHAR(10) --indicates the right answer
Score INT
)

CREATE TABLE AnweredQuestions
(
QuestionId INT
AnswerId INT
ParticipantId INT
AnswerValue
)

Thats just a quick one, could be sure more normalized, but these tables could be easy joined and scored as well in one query rather than using the different columns and its even more extensible than your current one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I actually have set up the tables more close to what you recommended, like this and then of course the user table. I first have to do some frontenmd stuff and then will come back to see how to handle this.

Actually there are no right or wrong answers since questions have the form like: Iwrite down what I need to buy before I go shopping. true/false.

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestion] (
[question_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_categoryID] [int] NOT NULL ,
[question_weight] [int] NULL ,
[question_text_DE] [nvarchar] (255) ,
[question_text_FR] [nvarchar] (255) ,
[question_text_IT] [nvarchar] (255) ,
[question_active] [tinyint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestionUser] (
[question_user_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_user_userID] [int] NOT NULL ,
[question_user_questionID] [int] NOT NULL ,
[question_user_questionanswer] [int] NOT NULL
) ON [PRIMARY]
GO|||

Hi,

glad to hear that you picked up some ideas. There sure could be more normalized (but I do not want to exaggerate :-) ). Come back if you have any more questions, you are welcome :-)

I keep an eye on the post I answered, anyway if I overlook your answer or rerequest, feel free to contact me through my website which is mentioned below.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thanks, very friendly.

I actually I became totally lost.

I received a hint from my boss but this one needs a totally denormalized table and only allows answers as yes or no.

Something like this:

From this kind of table

CREATE TABLE #test (

[person_id] [smallint] IDENTITY(1,1),
[person_name] [varchar] (20),
[answer001] [tinyint],
[answer002] [tinyint],
[answer003] [tinyint],
[answer004] [tinyint],
[answer005] [tinyint],
[answer006] [tinyint],
[answer007] [tinyint],
[answer008] [tinyint],
[answer009] [tinyint]
)

I should do a query like this:

SELECT t1.person_name AS p1_name, t2.person_name AS p2_name
, '''' AS person_rank
, t1.answer009*1+t1.answer008*2+t1.answer007*4+t1.answer006*8+t1.answer005*16+t1.answer004*32+t1.answer003*64+t1.answer002*128+t1.answer001*256 AS p1_value
, t2.answer009*1+t2.answer008*2+t2.answer007*4+t2.answer006*8+t2.answer005*16+t2.answer004*32+t2.answer003*64+t2.answer002*128+t2.answer001*256 AS p2_value

FROM #test t1, #test t2
WHERE t1.person_id <> t2.person_id
AND t1.person_name = 'Fritz' AND t2.person_name <> t1.person_name

and then find matches with something like this (in ColdFusion since we couldn't find the respectively SQL functions)

<cfoutput query="qgetmatchesprodandtype">
<cfset tmp = QuerySetCell(qTest, 'person_rank', 9- Len(Replace(FormatBaseN(BitXor(qTest.p1_value,qTest.p2_value),2), "0", "", "ALL")), qTest.CurrentRow)>
</cfoutput>

So, I could really need some enlightening examples

I'd prefer to match answers by counting the number of matches for each question and user undependently if the answer can only be 0/1 or any value between 0-9 (ore anything else)|||I might look at doing it this way:

Let's assume that you have a table of traits of people and you have a candidate and want to find the closest matches.... Kind of like a dating service.

If I take the absolute value of (person1.trait1 - person2.trait1) then if that is 0 they are a match on that trait, if it is 1 then they are not a match....

It follows that if I sum up the abs values of the subtracted trait pairs then the lower the overall sum the more "compatible" the two individual are:

That would lead me to look at:

Select
p1.name,
p2.name,
Sum(
abs(p1.trait1 - p2.trait1) +
abs(p1.trait2 - p2.trait2) +
abs(p1.trait3 - p2.trait3) +
.....
abs(p1.trait17 - p2.trait17)
) As matchfactor
From members as p1, members.p2
Where p1.id <> p2.id And p1.name = 'smith'
Order by matchfactor

|||

Jens Sü?meyers answer below exactly answered my problem if I added a
ORDER BY Numberofmatches DESC

the following query will solve your described problem:

SELECT

t2.[question_user_UserId],COUNT(*) AS Numberofmatches

FROM [dbo].[tCmsElementCustomPartnermatchQuestionUser] t1

INNER JOIN

tCmsElementCustomPartnermatchQuestionUser t2

ON t1.[question_user_questionID] = t2.[question_user_questionID] AND

t1.[question_user_questionanswer] = t2.[question_user_questionanswer] AND NOT

t1.[question_user_userid] = t2.[question_user_userid] --to eliminate the actual user which has the best match with himself :-)

Where T1.[question_user_userid] = 15

Group by t2.[question_user_UserId]

Let me know if that worked for you.

-Jens.

How to find matching profiles?

Users have to answer 17 simple yes/no questions and the answers are stored in an column for each question as tinyint 0/1 values.

At least that's what seems reasonable to me at the moment.

The table is under my control so I could change it if needed.

Now from several tenthousend or maybe hundreds of thousends of entries I need to find those with the closest match. Of course, I need all of the entries that have the exact same answers and this is no problem. But - at least if there are not enough full matches - then I need all records that have maybe 16,15,14... matches out of the 17 answers.

I have not yet the idea on how to handle this without quering 17*16 different answer schemes.

Hi,

I wouldn′t store the data denormalized. The better way to store it IMHO is to normalize the data, if you want closer machtes you can also setup a score for each answered question: Here is an extract of a possible solution:

CREATE TABLE Question
(
QuestionId INT
QuestionText VARCHAR(100)
)

CREATE TABLE Anwers
(
QuestionId INT
AnswerId INT
AnswerValue VARCHAR(10) --indicates the right answer
Score INT
)

CREATE TABLE AnweredQuestions
(
QuestionId INT
AnswerId INT
ParticipantId INT
AnswerValue
)

Thats just a quick one, could be sure more normalized, but these tables could be easy joined and scored as well in one query rather than using the different columns and its even more extensible than your current one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||I actually have set up the tables more close to what you recommended, like this and then of course the user table. I first have to do some frontenmd stuff and then will come back to see how to handle this.

Actually there are no right or wrong answers since questions have the form like: Iwrite down what I need to buy before I go shopping. true/false.

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestion] (
[question_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_categoryID] [int] NOT NULL ,
[question_weight] [int] NULL ,
[question_text_DE] [nvarchar] (255) ,
[question_text_FR] [nvarchar] (255) ,
[question_text_IT] [nvarchar] (255) ,
[question_active] [tinyint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tCmsElementCustomPartnermatchQuestionUser] (
[question_user_id] [int] IDENTITY (1, 1) NOT NULL ,
[question_user_userID] [int] NOT NULL ,
[question_user_questionID] [int] NOT NULL ,
[question_user_questionanswer] [int] NOT NULL
) ON [PRIMARY]
GO|||

Hi,

glad to hear that you picked up some ideas. There sure could be more normalized (but I do not want to exaggerate :-) ). Come back if you have any more questions, you are welcome :-)

I keep an eye on the post I answered, anyway if I overlook your answer or rerequest, feel free to contact me through my website which is mentioned below.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thanks, very friendly.

I actually I became totally lost.

I received a hint from my boss but this one needs a totally denormalized table and only allows answers as yes or no.

Something like this:

From this kind of table

CREATE TABLE #test (

[person_id] [smallint] IDENTITY(1,1),
[person_name] [varchar] (20),
[answer001] [tinyint],
[answer002] [tinyint],
[answer003] [tinyint],
[answer004] [tinyint],
[answer005] [tinyint],
[answer006] [tinyint],
[answer007] [tinyint],
[answer008] [tinyint],
[answer009] [tinyint]
)

I should do a query like this:

SELECT t1.person_name AS p1_name, t2.person_name AS p2_name
, '''' AS person_rank
, t1.answer009*1+t1.answer008*2+t1.answer007*4+t1.answer006*8+t1.answer005*16+t1.answer004*32+t1.answer003*64+t1.answer002*128+t1.answer001*256 AS p1_value
, t2.answer009*1+t2.answer008*2+t2.answer007*4+t2.answer006*8+t2.answer005*16+t2.answer004*32+t2.answer003*64+t2.answer002*128+t2.answer001*256 AS p2_value

FROM #test t1, #test t2
WHERE t1.person_id <> t2.person_id
AND t1.person_name = 'Fritz' AND t2.person_name <> t1.person_name

and then find matches with something like this (in ColdFusion since we couldn't find the respectively SQL functions)

<cfoutput query="qgetmatchesprodandtype">
<cfset tmp = QuerySetCell(qTest, 'person_rank', 9- Len(Replace(FormatBaseN(BitXor(qTest.p1_value,qTest.p2_value),2), "0", "", "ALL")), qTest.CurrentRow)>
</cfoutput>

So, I could really need some enlightening examples

I'd prefer to match answers by counting the number of matches for each question and user undependently if the answer can only be 0/1 or any value between 0-9 (ore anything else)|||I might look at doing it this way:

Let's assume that you have a table of traits of people and you have a candidate and want to find the closest matches.... Kind of like a dating service.

If I take the absolute value of (person1.trait1 - person2.trait1) then if that is 0 they are a match on that trait, if it is 1 then they are not a match....

It follows that if I sum up the abs values of the subtracted trait pairs then the lower the overall sum the more "compatible" the two individual are:

That would lead me to look at:

Select
p1.name,
p2.name,
Sum(
abs(p1.trait1 - p2.trait1) +
abs(p1.trait2 - p2.trait2) +
abs(p1.trait3 - p2.trait3) +
.....
abs(p1.trait17 - p2.trait17)
) As matchfactor
From members as p1, members.p2
Where p1.id <> p2.id And p1.name = 'smith'
Order by matchfactor

|||

Jens Sü?meyers answer below exactly answered my problem if I added a
ORDER BY Numberofmatches DESC

the following query will solve your described problem:

SELECT

t2.[question_user_UserId],COUNT(*) AS Numberofmatches

FROM [dbo].[tCmsElementCustomPartnermatchQuestionUser] t1

INNER JOIN

tCmsElementCustomPartnermatchQuestionUser t2

ON t1.[question_user_questionID] = t2.[question_user_questionID] AND

t1.[question_user_questionanswer] = t2.[question_user_questionanswer] AND NOT

t1.[question_user_userid] = t2.[question_user_userid] --to eliminate the actual user which has the best match with himself :-)

Where T1.[question_user_userid] = 15

Group by t2.[question_user_UserId]

Let me know if that worked for you.

-Jens.