How to change the height of a row using expressions?

Is it possible to have a report parameter that will change the height of rows in a report? The height property of a matrix row does not seem to support expressions.

My goal is to instruct a single row sub-report to grow in height until it fits the height of the parent report cell.I thought that I could pass the height of the parent row as a parameter to the sub-report and then change the height of the sub-report using an expression.

Any help will be appreciated,

Dom.

No, currently row height can not be dynamically changed through expression. This feature is on our wish list for a future release.|||

Thanks for the reply, a lot of posts in this section are left unanswered.

Do you know then how I could tell a sub-report to grow in height until it fits the parent cell of the main report?

How to change the data type of an IDENTITY column

Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
MichaelWhy are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:
> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> > Hi.
> > I have an existing table with an INT IDENTITY column; it contains some
> data
> > rows.
> > I want to change the identity column type to smallint (the data values are
> > within range) but am stumped. Here's what I'm trying to do (assume the
> > identity column is called Id):
> >
> > Create tempId smallint column
> > save Id values in tempId
> > Drop Id column
> > Recreate Id column as identity smallint.
> > *** Update Id column with values from tempId
> > Drop tempId column
> >
> > As you know, it fails on the Update step above: cannot update identity
> > column (I've tried set identity_insert on and that makes no difference -
> now
> > where's the set identity_update function...)
> >
> > Can anyone suggest a way of doing it?
> > Thanks
> > Michael
> >
> >
> >
>
>|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:
> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> > Adam,
> > Thanks, but it needs to be an identity column. And I can have it both
> ways!
> > I've just found a way to do what I want:
> > Save the data into a temp table
> > delete the data
> > drop the int identity column
> > recreate the identity column as smallint
> > identity_insert on
> > insert rows from temp table
> > identity_insert off
> > drop temp table.
> >
> > A bit long winded, but it works. (I've left out minor detail re.
> constraints
> > etc.)
> > Thanks
> > Michael
> >
>
>|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

How to Change Precision and Scale in MS SQL Server 2000

Hello,

My table was created by importing from an Excel spreadsheet. Typical fields in the rows are a date and various stock values like Open, High, Low, and Close. Unfortunately, many of the values have the wrong characteristics. These are my problems:

1. The date field has time in addition to the date. I don't want the
time in this field.

2. Many of the amount fields have a large precision, for example,
1.9399999999999999. I want to allow for a precision of 5 and a
scale of 2.

Can I make changes to my table at this point? I looked into Design Table but I don't see any feature allowing me to make changes 'on the fly'.

Any suggestions are welcome.

JoeHowdy,

Usually you can query a datetime column to extract just the date, so dont worry too much about that.

The column precision can be changed ( on the fly as it were ) using an alter table command ( see BOL ) that will automatically change the column precision and in the process round the values to what you want.

Cheers,

SG
( PS - theres nothing quite like a V8 Holden ute.....)|||Thank you for the reply. It occurs to me that one can end up creating a great many different queries if one is interested in comparing possible results/outputs. If DBA's want to save their queries for possible use later do they typically like to store them in a standard folder? Or should one create a special folder within the 'Databases' folder?

Thanks again. I am still new to working with Query Analyzer.

Joe|||I would also like to ask anyone if he or she could advise me as to how I can display only a date (without the time) when I do a query using Query Analyzer. I really don't want to see time displayed.

Can someone assist?

Thanks again.

Joe|||Howdy,

Well, sadly SQL doesnt handle splitting out dates from datetime fields very well.

Assuming you had a column called DATE in a table called INFO, if you want to display JUST the date, you need to extract the hour, min, seconds as characher values then reconstruct into a character format ( and later change to datetime , which by the way gives a defualt date of 01/01/1900)

Now, assuming you have a small table called INFO, with one column called DATE with one value of 2003-10-10 17:23:34

If you xxtract using time the following code -

select convert(varchar(2),datepart(hh,DATE))
+':'+convert(varchar(2),datepart(mm,DATE))
+':'+convert(varchar(2),datepart(ss,DATE))
from INFO

This gives -

17:23:34 ( but in varchar format).

Note too that single digit values WILL NOT have a '0' put in front of them unless you test for it & code for it accordingly.

Unless you get all dates into the same format - e.g. all varchar/char or all in datetime, mixing & matching will give you a headache.

Cheers,

SG.

How to change columns to rows

I have a need to change the columns in a table to rows with values
/****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Test] (
[UserID] [int] NULL ,
[NurseID] [int] NULL ,
[NurseID2] [int] NULL ,
[NurseID3] [int] NULL ,
[ReceptionID] [int] NULL ,
[OfficemanID] [int] NULL ,
[NurseTrainID] [int] NULL ,
[ResidentTrainID] [int] NULL ,
[ResidentTrainID2] [int] NULL ,
[ResidentTrainID3] [int] NULL
) ON [PRIMARY]
END
Insert into test
(UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
values
(1,3,9,4,7)
Select * from Test would give
UserID NurseID, NurseID2
1 3 9
and I need to transform to using SQL2000
Description Users
UserID 1
NurseID 3
NurseID2 9
I may not need the description column
Thanks for the help
Stephen K. MiyasatoHi Stephen,
2005 allows using UNPIVOT clause. Not sure about 2000 though..
http://msdn2.microsoft.com/en-us/library/ms177410.aspx
"Stephen K. Miyasato" wrote:

> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>
>|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||If you were using SQL Server 2005, you could use an UNPIVOT statement.
In this case, I think you'll just have to use a series of UNION ALL
statements to transform the data, as in:
select
'UserID' as Description,
UserID as Users
from test
UNION ALL
select
'NurseID' as Description,
NurseID as Users
from test
UNION ALL
select
'NurseID2' as Description,
NurseID2 as Users
from test|||For SS2000, you can refer to the following.
- How to rotate a table in SQL Server
http://support.microsoft.com/defaul...kb;en-us;175574
Martin C K Poon
Senior Analyst Programmer
====================================
"Stephen K. Miyasato" <miyasat@.flex.com> bl
news:%23d67IiofGHA.4864@.TK2MSFTNGP05.phx.gbl g...
> I have a need to change the columns in a table to rows with values
> /****** Object: Table [dbo].[Test] Script Date: 5/23/2006 6:39:49 AM
> ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [Test] (
> [UserID] [int] NULL ,
> [NurseID] [int] NULL ,
> [NurseID2] [int] NULL ,
> [NurseID3] [int] NULL ,
> [ReceptionID] [int] NULL ,
> [OfficemanID] [int] NULL ,
> [NurseTrainID] [int] NULL ,
> [ResidentTrainID] [int] NULL ,
> [ResidentTrainID2] [int] NULL ,
> [ResidentTrainID3] [int] NULL
> ) ON [PRIMARY]
> END
> Insert into test
> (UserID, NurseID,NurseID2, ReceptionID, OfficemanID)
> values
> (1,3,9,4,7)
> Select * from Test would give
> UserID NurseID, NurseID2
> 1 3 9
> and I need to transform to using SQL2000
> Description Users
> UserID 1
> NurseID 3
> NurseID2 9
> I may not need the description column
> Thanks for the help
> Stephen K. Miyasato
>|||Thanks very much,
That is what I was looking for.
Stephen
"dterrie" <dterrie@.axiomadvisors.net> wrote in message
news:1148404941.573763.96380@.i39g2000cwa.googlegroups.com...
> If you were using SQL Server 2005, you could use an UNPIVOT statement.
> In this case, I think you'll just have to use a series of UNION ALL
> statements to transform the data, as in:
> select
> 'UserID' as Description,
> UserID as Users
> from test
> UNION ALL
> select
> 'NurseID' as Description,
> NurseID as Users
> from test
> UNION ALL
> select
> 'NurseID2' as Description,
> NurseID2 as Users
> from test
>

How to cancel a processing request?

Hello ,

I've execute a wrong sql request from an ETL. This request is inserting lots of rows in a table. I want to cancel that process, is it possible to do this? If yes, how can I cancel that request?

Thanks,

Stop or 'kill' the client application that is doing the inserts.|||

Find out the SPID that the request is running on, and then run KILL <thespidnumber>

You can probably find the SPID by running the query below, or by using sp_who2

SETNOCOUNTON;

DECLARE @.SpID smallint

DECLARE spID_Cursor CURSOR

FORWARD_ONLYREAD_ONLYFOR

SELECTTOP 25 spid

FROM master..sysprocesses

WHEREstatus='runnable'

AND spid > 50 -- Eliminate system SPIDs

AND spid <> 102 -- Replace with your SPID

ORDERBY CPU DESC

OPEN spID_Cursor

FETCHNEXTFROM spID_Cursor

INTO @.spID

WHILE@.@.FETCH_STATUS= 0

BEGIN

PRINT'Spid #: '+STR(@.spID)

EXEC('DBCC INPUTBUFFER ('+ @.spID +')')

FETCHNEXTFROM spID_Cursor

INTO @.spID

END

-- Close and deallocate the cursor

CLOSE spID_Cursor

DEALLOCATE spID_Cursor

|||thx more than I expected

How to cancel a processing request?

Hello ,

I've execute a wrong sql request from an ETL. This request is inserting lots of rows in a table. I want to cancel that process, is it possible to do this? If yes, how can I cancel that request?

Thanks,

Stop or 'kill' the client application that is doing the inserts.|||

Find out the SPID that the request is running on, and then run KILL <thespidnumber>

You can probably find the SPID by running the query below, or by using sp_who2

SETNOCOUNTON;

DECLARE @.SpID smallint

DECLARE spID_Cursor CURSOR

FORWARD_ONLYREAD_ONLYFOR

SELECTTOP 25 spid

FROM master..sysprocesses

WHEREstatus='runnable'

AND spid > 50 -- Eliminate system SPIDs

AND spid <> 102 -- Replace with your SPID

ORDERBY CPU DESC

OPEN spID_Cursor

FETCHNEXTFROM spID_Cursor

INTO @.spID

WHILE@.@.FETCH_STATUS= 0

BEGIN

PRINT'Spid #: '+STR(@.spID)

EXEC('DBCC INPUTBUFFER ('+ @.spID +')')

FETCHNEXTFROM spID_Cursor

INTO @.spID

END

-- Close and deallocate the cursor

CLOSE spID_Cursor

DEALLOCATE spID_Cursor

|||thx more than I expected

How to callapse rows together

I'm need to collaspe a few rows together and output one row per group. The rows come from a flat file and are already sorted by orderid. Basically these are transactions with just the changed fields in them all unchanged fields are null (blank). They need to collapsed down to show the final state of all changed fields per order. Obviously the aggregate transform won't work in this situation since the goal is to collaspe instead of aggregate. In a language, I would normally buffer the previous row and apply the logic as needed until the orderid changed and then write out the new row. Then input file has millions of rows so I also didn't want to cursor through it updating individual rows in a table.

Example:

Orderid StatusCd OrderDt OrdrAmt

1 A 01/01/06 10

1 B

2 X 02/02/06 20

3 03/03/06 30

3 03/04/06

Collapse as

1 B 01/01/06 10

2 X 02/02/06 20

3 03/04/06

Danny,

The logic that you say you would apply in a programming language can be applied in exactly the same in SSIS by using a script component. I recommend you take a look at building an asynchronous script component.

-Jamie

How to calculate a value which takes the prev rows value for calculation.

Hi All,

Please help me with this problem
I have two fields in my report- Production hours and Scheduled Start Time.

For the first record in the report the scheduled start time field will have the current date time field value ,but for consecutive records it is the sum of Prod-hrs+scheduled start time (of the Previous record).

I tried the following formula

if OnFirstRecord then
{@.CurrDateTime}
else
Previous({@.Schd_Start_Time})+{dpRptCSReport.prod_hrs}

but get the error ' A formula cannot refer to itself, either directly or indirectly'
Can you please suggest a way out??

Thanks in advance
RashmiHi All,

Please help me with this problem
I have two fields in my report- Production hours and Scheduled Start Time.

For the first record in the report the scheduled start time field will have the current date time field value ,but for consecutive records it is the sum of Prod-hrs+scheduled start time (of the Previous record).

I tried the following formula

if OnFirstRecord then
{@.CurrDateTime}
else
Previous({@.Schd_Start_Time})+{dpRptCSReport.prod_hrs}

but get the error ' A formula cannot refer to itself, either directly or indirectly'
Can you please suggest a way out??

Thanks in advance
Rashmi

Please can anyone suggest a solution? Please can you help Madhi?|||If OnFirstRecord = true Then ({@.CurrDateTime}; Global NumberVar Test :=
{@.Schd_Start_Time})+{dpRptCSReport.prod_hrs})
Else Previous({Test});
Global NumberVar Test :=
{@.Schd_Start_Time})+{dpRptCSReport.prod_hrs});|||this formula will help you
change currentdatetime with ur need.

global datetimevar z;
if OnFirstRecord then
(
currentdatetime;
z:=currentdatetime;
)
else
(
z+10;
z:=z+10
)

How to build my SQL querry.

Hi.
I know this querry:

"SELECT TOP 10 * FROM MyTable ORDERBY ..."

will give the top 10 rows.

But how can I build a querry that can get the rows from 40-50 (ie).
Thanks.Try below code, it could helps you!

select top 10 * from (Select distinct top 50 * From TBLNAME order by Fieldname desc) a order by Fieldname asc|||This is a good example

http://www.4guysfromrolla.com/webtech/062899-1.shtml