Extract Data From A Column

Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...
> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>

Extract Data From A Column

Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...

quote:

---

> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>

---

Extra columns in Excel with SP2

I would like to upgrade to Reporting Services service pack 2 so that I can
specify report colours and specify to move the header into Excel's header.
However when we upgrade to Service Pack 2 it causes extra totals columns to
appear at the end of the months in the generated reports. These columns
could possibly acceptable if they were titled as Total however they are not.
Can anybody make any suggestions in how to generate the report in excel
without the additional columns appearing in the excel output? Many thanks.Does anybody have any ideas about how to do this - any guesses even would be
appreciated. Thanks
"Nicola Jones" wrote:
> I would like to upgrade to Reporting Services service pack 2 so that I can
> specify report colours and specify to move the header into Excel's header.
> However when we upgrade to Service Pack 2 it causes extra totals columns to
> appear at the end of the months in the generated reports. These columns
> could possibly acceptable if they were titled as Total however they are not.
> Can anybody make any suggestions in how to generate the report in excel
> without the additional columns appearing in the excel output? Many thanks.

external images disappear with interactive sort

I have created my report with embedded and external images. There are also
interactive sorting set up for all columns in my report. When I deploy the
report and look at it in the report manager, report appears good. However
when I try to change the sort directin of any any column using the
interactive sort icon then the new page does not have my external image. How
can I fix this so that I can see these images in my Report Manager?
Note: My system name does not have any underscore. But it has a hyphen. The
computer name in <urlroot> element of rsreportserver.config file has full
name in it. Also I tried to change the useSessionCookies entry to false in
ConfigurationInfo table of my Report server to false. But it did not help me.
So I reverted it back to true.This happened with me when I was using VS2005 beta but was fixed with
the real version. FYI|||Hi,
Thanks for posting at the newsgroup!
From your post, my understanding of this issue is: interactive sorting
works well in report manager, however, the sorting image is not working
now. If this is not correct, please feel free to let me know.
So currently I'd suggest we could check your SQL server 2005 version.
Please go to the localhost site and type the reporting service as below:
http://localhost/reportserver
Then at the page, the SQL server 2005 version will be listed. If you have
several instance at the reporting service, please use the url:
http://localhost/reportserver$<instancename>
Furthermore, if you could give us one detailed repro step(we could use the
Adventure works, pubs or northwind sample database) on this issue, that
will be very helpful for our troubleshooting. Thanks a lot!
Look forward to your reply!
Best Regards,
Wei-Dong XU
Microsoft Support
----
This posting is provided "AS IS" with no warranties, and confers no rights.
----
It is my pleasure to be of any assistance.|||Hi all,
The version of Reporting services that I use is "Microsoft SQL Server
Reporting Services Version 9.00.1399.00". This is RTM version of SQL Server
2005.
Your understanding of my problem: "interactive sorting
works well in report manager, however, the sorting image is not working
now."
My Problem: "Interactive sorting works good in report manager and also the
sort image appears good. My report has a customer logo which is basically
created using project type. Thus it is an external image not the embedded
one. This external image which is added to report by me disappears when I
make use of the sorting using the sort icon in Report manager."
Thanks.|||Hi Ring,
Does the URL of your external image stored in database? Is it possible for
you to generate a reproduce sample based on AdventureWorks? Or it will be
appreciated if you could give me some screen shot.
You may attach the sample in the newsgroup. I understand the information
may be sensitive to you, my direct email address is
v-mingqc@.ONLINEmicrosoft.com (please make sure you have REMOVED ONLINE
before you click SEND), you may send the file to me directly and I will
keep secure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
I am not sure if you received my email. Thus I am answering you also in
newsgroup. My image which disappears with interactive sorting is not taken
from database. I initally add image to my report solution. When we try to put
image in our report from tool box, image wizard starts. I am choosing project
type in the image wizard. Then I choose the image which I previosly added to
my solution in the 3rd step of image wizard. Then while I deploy my report to
a folder in ReportServer, I also deploy my image to the same folder. As I do
not know how to add attachments in a web based newgroup viewer, I am not
attaching my screenshots.
Thanks|||Hi,
Yes, thanks so much for emailing the screen shots and I have reproduced it
on my side.
This is a known issue for SQL Server 2005 Reporting Services and will be
fixed in SSRS 2005 SP1.
I am sincerely sorry for the inconvenience it might cause to you and thanks
so much for your understanding.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I just downloaded and installed the SQL Server 2005 SP1 CTP that was made
available on Friday and this problem is not fixed! What is the status on this
problem being fixed by Microsoft? I have an SSRS 2005 deployment that is
scheduled for release in the next month and this issue is a big problem since
it is our company's logo that is not displaying when this error occurs.
Steve
"Michael Cheng [MSFT]" wrote:
> Hi,
> Yes, thanks so much for emailing the screen shots and I have reproduced it
> on my side.
> This is a known issue for SQL Server 2005 Reporting Services and will be
> fixed in SSRS 2005 SP1.
> I am sincerely sorry for the inconvenience it might cause to you and thanks
> so much for your understanding.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>sql

External columns vs. output columns?

Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?

TIA,

Barkingdong

Does this help: http://blogs.conchango.com/jamiethomson/archive/2006/05/23/3984.aspx ?

-Jamie

|||

External columns help to make a snapshot of the external metadata. It is used as reference for future changes in the external source and internal metadata (output columns).

For example, let's say we do not have external columns and at certain point of time you have 2 output columns in the collection and 3 columns in the linked external table (or flat file, etc). That does not give us enough information to determine if we ignored one column at the source or the table was updated with an additional column.

With external columns we have a reference to compare against, and that allows automatic updates of changed metadata, through the ReinitializeMetadata mechanism, without disrupting the downstream components.

HTH.

|||

Your answers (including Jamie's too) surprised me. But I need to slow you down a bit (that's the problem with experts, you know) to better understand your answer. Here are some of my thoughts. Please comment on any portion, in any manner, you wish.

1. So "external" columns relate to metadata used at design time to verify, among other things, that the source and destination mappings are in "sync" or appropriate.

2. I don't remember much discussion of metadata in sql 2000 DTS. Was it there but hidden, did I simply miss it, or is this a sql 2005 enhancement?

3. "...External columns help to make a snapshot of the external metadata. "

Suppose I have a Flat File Source Connector and an OLE DB Destination connector. What does "external" refer to here in SSIS talk? Are the "external columns" metadata references from the Flat File connector that refer to the inputs of the "external" OLE DB Destination connector?

4.I found this BOL reference:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/8f5bd3ed-3e79-43a4-b6c1-435e4c2cc8cc.htm

"

When a component is disconnected from its data source, you can validate the columns in the input and output column collections against the columns at its external data source by using the IDTSExternalMetadataColumnCollection90 interface. This interface lets you maintain a snapshot of the columns at the external data source and map these columns to the columns in the input and output column collection of the component.

Implementing external metadata columns adds a layer of overhead and complexity to component development, because you must maintain and validate against an additional column collection, but the ability to avoid expensive round trips to the server for validation may make this development work worthwhile.

"

That is interesting. Disconnected, ahh...

While I can drag and drop input/output fields between tasks, I gather that I wouldn't normally adjust (unless I was programming in .NET) external\internal metadata columns. In a sense I shouldn't be too concerned with metadata on a daily basis. It's part of the SSIS infrastructure but something that I generally won't touch.

TIA,

Barkingdog

|||

barkingdog wrote:

Your answers (including Jamie's too) surprised me. But I need to slow you down a bit (that's the problem with experts, you know) to better understand your answer. Here are some of my thoughts. Please comment on any portion, in any manner, you wish.

1. So "external" columns relate to metadata used at design time to verify, among other things, that the source and destination mappings are in "sync" or appropriate.

Yeah, that's a good way of looking at it!

barkingdog wrote:

2. I don't remember much discussion of metadata in sql 2000 DTS. Was it there but hidden, did I simply miss it, or is this a sql 2005 enhancement?

Very much a sql 2005 thing.

barkingdog wrote:

3. "...External columns help to make a snapshot of the external metadata. "

Suppose I have a Flat File Source Connector and an OLE DB Destination connector. What does "external" refer to here in SSIS talk? Are the "external columns" metadata references from the Flat File connector that refer to the inputs of the "external" OLE DB Destination connector?

Nothing as sinister as that. Its quite simply the metadata of the thing that SSIS is going to connect to.

barkingdog wrote:

4.I found this BOL reference:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/8f5bd3ed-3e79-43a4-b6c1-435e4c2cc8cc.htm

"

When a component is disconnected from its data source, you can validate the columns in the input and output column collections against the columns at its external data source by using the IDTSExternalMetadataColumnCollection90 interface. This interface lets you maintain a snapshot of the columns at the external data source and map these columns to the columns in the input and output column collection of the component.

Implementing external metadata columns adds a layer of overhead and complexity to component development, because you must maintain and validate against an additional column collection, but the ability to avoid expensive round trips to the server for validation may make this development work worthwhile.

"

That is interesting. Disconnected, ahh...

While I can drag and drop input/output fields between tasks, I gather that I wouldn't normally adjust (unless I was programming in .NET) external\internal metadata columns. In a sense I shouldn't be too concerned with metadata on a daily basis. It's part of the SSIS infrastructure but something that I generally won't touch.

That's a safe bet. Let SSIS do the grunt for you - you just use what it gives you!!

-Jamie

External columns from Sybase stored procedures

I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.

1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).

2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.

3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.

It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.

Any ideas would be greatly appreciated!

I since learned that the OLE DB data source also has a DelayValidation property, but I still see the same errors with this enabled.
|||Well I think I answered my own question. After switching the connection from the "Native\Sybase" type to ".Net Providers\Sybase" and changing the OLE DB connection to a DataReader I found a new option on the DataFlow property that allowed me to change the sql through an expression at runtime. I may have also needed to turn off the ValidateExternalMetadata property too, not sure yet.

External columns from a Sybase stored procedures

I need to execute several stored procedures on a Sybase server and copy the results to SQL Server 2005 tables. While using an ad-hoc sql statement the "Available External Columns" list is correct, however when using a stored procedure the list is empty. I've tried to work around this a couple of ways without success.

1) DelayedValidation.
I ran the sql from the stored procedure body in the OLE DB Source to set the column list, then turned on DelayValidation for the Data Flow component. When I switch to using a stored procedure it still connects to the Sybase database and removes the column list. Still does this even after turning on DelayedValidation for the sequence container and entire package (the OLE DB source itself does not have the option).

2) Using a variable that changes during runtime.
I copied the sql from the Sybase procedure into the default value of a variable. A script changes it to a procedure at runtime. This provides a column list in design mode but throws the error "The external metadata column collection is out of synchronization with the data source columns. The external metadata column xxxx needs to be removed from the external metadata column collection" and repeats for every column in the list. I know that the column names and data types are identical.

3) Manually updated the external/output columns list.
Was very painful and gave me the same errors.

It seems that DelayedValidation is the route I'm supposed to take, but I don't see how it would be any different during runtime.

Any ideas would be greatly appreciated!

I since learned that the OLE DB data source also has a DelayValidation property, but I still see the same errors with this enabled.
|||Well I think I answered my own question. After switching the connection from the "Native\Sybase" type to ".Net Providers\Sybase" and changing the OLE DB connection to a DataReader I found a new option on the DataFlow property that allowed me to change the sql through an expression at runtime. I may have also needed to turn off the ValidateExternalMetadata property too, not sure yet.

Expressions in calucated columns

Where can I find a list of expressions that are allowed in calculated columns?
Thanks
IT PHYTOSAN
On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:

>Where can I find a list of expressions that are allowed in calculated columns?
Hi IT PHYTOSAN,
In Books Online, subject "CREATE TABLE":
computed_column_expression
Is an expression defining the value of a computed column. A computed
column is a virtual column not physically stored in the table. It is
computed from an expression using other columns in the same table. For
example, a computed column can have the definition: cost AS price * qty.
The expression can be a noncomputed column name, constant, function,
variable, and any combination of these connected by one or more
operators. The expression cannot be a subquery.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks,but what is the syntax? What commands are allowed?
IT PHYTOSAN
"Hugo Kornelis" wrote:

> On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
>
> Hi IT PHYTOSAN,
> In Books Online, subject "CREATE TABLE":
> computed_column_expression
> Is an expression defining the value of a computed column. A computed
> column is a virtual column not physically stored in the table. It is
> computed from an expression using other columns in the same table. For
> example, a computed column can have the definition: cost AS price * qty.
> The expression can be a noncomputed column name, constant, function,
> variable, and any combination of these connected by one or more
> operators. The expression cannot be a subquery.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||Example Script
CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Price] [decimal](18, 0) NOT NULL ,
[Qty] [int] NOT NULL ,
[Total] AS ([Price] * [Qty])
) ON [PRIMARY]
GO
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:B26DE2A2-2E51-4E7B-BADA-FBDE15A48004@.microsoft.com...[vbcol=seagreen]
> Thanks,but what is the syntax? What commands are allowed?
> IT PHYTOSAN
> "Hugo Kornelis" wrote:

Expressions in calucated columns

Where can I find a list of expressions that are allowed in calculated column
s?
Thanks
IT PHYTOSANOn Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
[vbcol=seagreen]
>Where can I find a list of expressions that are allowed in calculated columns?[/vbc
ol]
Hi IT PHYTOSAN,
In Books Online, subject "CREATE TABLE":
computed_column_expression
Is an expression defining the value of a computed column. A computed
column is a virtual column not physically stored in the table. It is
computed from an expression using other columns in the same table. For
example, a computed column can have the definition: cost AS price * qty.
The expression can be a noncomputed column name, constant, function,
variable, and any combination of these connected by one or more
operators. The expression cannot be a subquery.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks,but what is the syntax? What commands are allowed?
IT PHYTOSAN
"Hugo Kornelis" wrote:

> On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
>
> Hi IT PHYTOSAN,
> In Books Online, subject "CREATE TABLE":
> computed_column_expression
> Is an expression defining the value of a computed column. A computed
> column is a virtual column not physically stored in the table. It is
> computed from an expression using other columns in the same table. For
> example, a computed column can have the definition: cost AS price * qty.
> The expression can be a noncomputed column name, constant, function,
> variable, and any combination of these connected by one or more
> operators. The expression cannot be a subquery.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Example Script
CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Price] [decimal](18, 0) NOT NULL ,
[Qty] [int] NOT NULL ,
[Total] AS ([Price] * [Qty])
) ON [PRIMARY]
GO
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:B26DE2A2-2E51-4E7B-BADA-FBDE15A48004@.microsoft.com...[vbcol=seagreen]
> Thanks,but what is the syntax? What commands are allowed?
> IT PHYTOSAN
> "Hugo Kornelis" wrote:
>

Expressions in calucated columns

Where can I find a list of expressions that are allowed in calculated columns?
Thanks
IT PHYTOSANOn Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
>Where can I find a list of expressions that are allowed in calculated columns?
Hi IT PHYTOSAN,
In Books Online, subject "CREATE TABLE":
computed_column_expression
Is an expression defining the value of a computed column. A computed
column is a virtual column not physically stored in the table. It is
computed from an expression using other columns in the same table. For
example, a computed column can have the definition: cost AS price * qty.
The expression can be a noncomputed column name, constant, function,
variable, and any combination of these connected by one or more
operators. The expression cannot be a subquery.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks,but what is the syntax? What commands are allowed?
IT PHYTOSAN
"Hugo Kornelis" wrote:
> On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
> >Where can I find a list of expressions that are allowed in calculated columns?
> Hi IT PHYTOSAN,
> In Books Online, subject "CREATE TABLE":
> computed_column_expression
> Is an expression defining the value of a computed column. A computed
> column is a virtual column not physically stored in the table. It is
> computed from an expression using other columns in the same table. For
> example, a computed column can have the definition: cost AS price * qty.
> The expression can be a noncomputed column name, constant, function,
> variable, and any combination of these connected by one or more
> operators. The expression cannot be a subquery.
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Example Script
CREATE TABLE [dbo].[Table1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Price] [decimal](18, 0) NOT NULL ,
[Qty] [int] NOT NULL ,
[Total] AS ([Price] * [Qty])
) ON [PRIMARY]
GO
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"IT PHYTOSAN" <ITPHYTOSAN@.discussions.microsoft.com> wrote in message
news:B26DE2A2-2E51-4E7B-BADA-FBDE15A48004@.microsoft.com...
> Thanks,but what is the syntax? What commands are allowed?
> IT PHYTOSAN
> "Hugo Kornelis" wrote:
>> On Mon, 3 Oct 2005 15:31:03 -0700, IT PHYTOSAN wrote:
>> >Where can I find a list of expressions that are allowed in calculated
>> >columns?
>> Hi IT PHYTOSAN,
>> In Books Online, subject "CREATE TABLE":
>> computed_column_expression
>> Is an expression defining the value of a computed column. A computed
>> column is a virtual column not physically stored in the table. It is
>> computed from an expression using other columns in the same table. For
>> example, a computed column can have the definition: cost AS price * qty.
>> The expression can be a noncomputed column name, constant, function,
>> variable, and any combination of these connected by one or more
>> operators. The expression cannot be a subquery.
>>
>> Best, Hugo
>> --
>> (Remove _NO_ and _SPAM_ to get my e-mail address)

Expression.Like for numbers?

Hi,

Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like?

Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%.

Cheers,

koekie

You can cast the BIGINT to varchar ad test that like:

WHERE CONVERT(VARCHAR(20), ColName) LIKE '%678%'

|||

In other words, something like this:

Expression.Sql("CONVERT(VARCHAR(20), {alias}.ColName) LIKE ?", "%678%", NHibernateUtil.String )

|||

Cool, thanks a lot.

Expression Question

I am working on an aging report and am trying to sum one of the columns that
is the 30-60 Day bucket. I have the expression written as the following;
"=sum(fields!extcost.value) where (fields!interval.value>30 and
Fields!Interval.Value<=60)"
What logic should I be using?
Thank you,
Ryan2 ways to deal with this. In report cell:
=Sum(IIf(Fields!Interval.Value=>30 AND
Fields!Interval.value<=60,Fields!extcost.value,0))
However, I normally try to do this in the SQL with a CASE statement for each
bucket.
SELECT X,Y,Z,Sum(CASE WHEN Interval BETWEEN 30 and 60 THEN extcost ELSE 0
END As Bucket2,A,B,C...
FROM....
Michael C
"Ryan Mcbee" wrote:
> I am working on an aging report and am trying to sum one of the columns that
> is the 30-60 Day bucket. I have the expression written as the following;
> "=sum(fields!extcost.value) where (fields!interval.value>30 and
> Fields!Interval.Value<=60)"
> What logic should I be using?
> Thank you,
> Ryan
>

expression in Visibility>Hidden field = No output to csv

Hi all,
I have a problem with a report I have created. It has around 52 columns and each column is shown or hidden based on a boolean parameter. Simple huh? I though so.

Each column has an expression similar to =IIF(Parameters!showfirstname.Value,False,True) for the Hidden field. This is not the hidden field for the 'cell' or 'header' but for the entire column.

The problem is, the report is correctly displayed as a pdf, tiff, excel file (possibly others), but all columns with an expression as the hidden value are not displayed in the xml or csv output regardless of the parameter value. This also applies if the expression is =IIF(True,False,True) or =IIF(1=1,False,True).

As soon as I change this field back to a simple 'True' or 'False' it displays correctly. I've tried playing around with setting the output options to values other than the default Auto setting to no avail.

There are numerous comments about this on newsgroups online going back to the first release of reporting services but none of them have solutions.

Regards

John Burns

John,

You can't conditionally hide and show data in data renderers (CSV, XML). This is by design. If you have an expression in the Hidden field and 'Auto' in DataOutput tab for text boxes in the column, your data will not be rendered into CSV or XML.

You can set DataElementOutput to Output for textboxes in the cells and in the header, and the coulmn will always be in the output file.

Thanks!

|||

You could in SQL 2000!

I've just upgraded to 2005 and exports to csv format no longer work whereas they did in SQL 2000. I've tracked the culpit down to the visibility statement. If there is an expression

e.g.

=IIf(1=1, false, true)

against the table then the data will render in all formats except csv\xml. This is a breaking change that has been introduced in 2005 so I would expect that microsoft would consider fixing it or producing a hot fix for affected systems. Note that I haven't applied any service packs but haven't seen anything related to this issue in the SP's.

Try this example report which queries sysobjects in the master database (change the data source first)

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=SQLServer;Initial Catalog=master</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>145254b5-d42f-46f6-a007-7898e2fb93b4</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

<PageWidth>21cm</PageWidth>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>20pt</FontSize>

<Color>SteelBlue</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.91429cm</Height>

<Value>Report1</Value>

</Textbox>

<Table Name="table1">

<DataSetName>DataSource1</DataSetName>

<Top>0.91429cm</Top>

<Visibility>

<Hidden>=IIf(1=1, false, true)</Hidden>

</Visibility>

<Width>5.07936cm</Width>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="name">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>name</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!name.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="id">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>id</rd:DefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!id.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.53333cm</Height>

</TableRow>

</TableRows>

</Details>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

<rd:DefaultName>textbox2</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>name</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>id</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.55873cm</Height>

</TableRow>

</TableRows>

<RepeatOnNewPage>true</RepeatOnNewPage>

</Header>

<TableColumns>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

<Height>2.00635cm</Height>

</Body>

<rd:ReportID>10fa5646-bbb8-452c-b281-7f6cb1fc3a6c</rd:ReportID>

<LeftMargin>2.5cm</LeftMargin>

<DataSets>

<DataSet Name="DataSource1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select * from sysobjects</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="name">

<rd:TypeName>System.String</rd:TypeName>

<DataField>name</DataField>

</Field>

<Field Name="id">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>id</DataField>

</Field>

<Field Name="xtype">

<rd:TypeName>System.String</rd:TypeName>

<DataField>xtype</DataField>

</Field>

<Field Name="uid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>uid</DataField>

</Field>

<Field Name="info">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>info</DataField>

</Field>

<Field Name="status">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>status</DataField>

</Field>

<Field Name="base_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>base_schema_ver</DataField>

</Field>

<Field Name="replinfo">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>replinfo</DataField>

</Field>

<Field Name="parent_obj">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>parent_obj</DataField>

</Field>

<Field Name="crdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>crdate</DataField>

</Field>

<Field Name="ftcatid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>ftcatid</DataField>

</Field>

<Field Name="schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>schema_ver</DataField>

</Field>

<Field Name="stats_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>stats_schema_ver</DataField>

</Field>

<Field Name="type">

<rd:TypeName>System.String</rd:TypeName>

<DataField>type</DataField>

</Field>

<Field Name="userstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>userstat</DataField>

</Field>

<Field Name="sysstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>sysstat</DataField>

</Field>

<Field Name="indexdel">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>indexdel</DataField>

</Field>

<Field Name="refdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>refdate</DataField>

</Field>

<Field Name="version">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>version</DataField>

</Field>

<Field Name="deltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>deltrig</DataField>

</Field>

<Field Name="instrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>instrig</DataField>

</Field>

<Field Name="updtrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>updtrig</DataField>

</Field>

<Field Name="seltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>seltrig</DataField>

</Field>

<Field Name="category">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>category</DataField>

</Field>

<Field Name="cache">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>cache</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>12.69841cm</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

expression in Visibility>Hidden field = No output to csv

Hi all,
I have a problem with a report I have created. It has around 52 columns and each column is shown or hidden based on a boolean parameter. Simple huh? I though so.

Each column has an expression similar to =IIF(Parameters!showfirstname.Value,False,True) for the Hidden field. This is not the hidden field for the 'cell' or 'header' but for the entire column.

The problem is, the report is correctly displayed as a pdf, tiff, excel file (possibly others), but all columns with an expression as the hidden value are not displayed in the xml or csv output regardless of the parameter value. This also applies if the expression is =IIF(True,False,True) or =IIF(1=1,False,True).

As soon as I change this field back to a simple 'True' or 'False' it displays correctly. I've tried playing around with setting the output options to values other than the default Auto setting to no avail.

There are numerous comments about this on newsgroups online going back to the first release of reporting services but none of them have solutions.

Regards

John Burns

John,

You can't conditionally hide and show data in data renderers (CSV, XML). This is by design. If you have an expression in the Hidden field and 'Auto' in DataOutput tab for text boxes in the column, your data will not be rendered into CSV or XML.

You can set DataElementOutput to Output for textboxes in the cells and in the header, and the coulmn will always be in the output file.

Thanks!

|||

You could in SQL 2000!

I've just upgraded to 2005 and exports to csv format no longer work whereas they did in SQL 2000. I've tracked the culpit down to the visibility statement. If there is an expression

e.g.

=IIf(1=1, false, true)

against the table then the data will render in all formats except csv\xml. This is a breaking change that has been introduced in 2005 so I would expect that microsoft would consider fixing it or producing a hot fix for affected systems. Note that I haven't applied any service packs but haven't seen anything related to this issue in the SP's.

Try this example report which queries sysobjects in the master database (change the data source first)

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=SQLServer;Initial Catalog=master</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>145254b5-d42f-46f6-a007-7898e2fb93b4</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

<PageWidth>21cm</PageWidth>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:GridSpacing>0.25cm</rd:GridSpacing>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ColumnSpacing>1cm</ColumnSpacing>

<ReportItems>

<Textbox Name="textbox1">

<rd:DefaultName>textbox1</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>20pt</FontSize>

<Color>SteelBlue</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.91429cm</Height>

<Value>Report1</Value>

</Textbox>

<Table Name="table1">

<DataSetName>DataSource1</DataSetName>

<Top>0.91429cm</Top>

<Visibility>

<Hidden>=IIf(1=1, false, true)</Hidden>

</Visibility>

<Width>5.07936cm</Width>

<Details>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="name">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>name</rd:DefaultName>

<ZIndex>1</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!name.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="id">

<DataElementOutput>Output</DataElementOutput>

<rd:DefaultName>id</rd:DefaultName>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>=Fields!id.Value</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.53333cm</Height>

</TableRow>

</TableRows>

</Details>

<Header>

<TableRows>

<TableRow>

<TableCells>

<TableCell>

<ReportItems>

<Textbox Name="textbox2">

<rd:DefaultName>textbox2</rd:DefaultName>

<ZIndex>3</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>name</Value>

</Textbox>

</ReportItems>

</TableCell>

<TableCell>

<ReportItems>

<Textbox Name="textbox3">

<rd:DefaultName>textbox3</rd:DefaultName>

<ZIndex>2</ZIndex>

<Style>

<BorderStyle>

<Default>Solid</Default>

</BorderStyle>

<TextAlign>Right</TextAlign>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<FontFamily>Tahoma</FontFamily>

<FontWeight>700</FontWeight>

<FontSize>11pt</FontSize>

<BorderColor>

<Default>LightGrey</Default>

</BorderColor>

<BackgroundColor>SteelBlue</BackgroundColor>

<Color>White</Color>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Value>id</Value>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

<Height>0.55873cm</Height>

</TableRow>

</TableRows>

<RepeatOnNewPage>true</RepeatOnNewPage>

</Header>

<TableColumns>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

<TableColumn>

<Width>2.53968cm</Width>

</TableColumn>

</TableColumns>

</Table>

</ReportItems>

<Height>2.00635cm</Height>

</Body>

<rd:ReportID>10fa5646-bbb8-452c-b281-7f6cb1fc3a6c</rd:ReportID>

<LeftMargin>2.5cm</LeftMargin>

<DataSets>

<DataSet Name="DataSource1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select * from sysobjects</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="name">

<rd:TypeName>System.String</rd:TypeName>

<DataField>name</DataField>

</Field>

<Field Name="id">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>id</DataField>

</Field>

<Field Name="xtype">

<rd:TypeName>System.String</rd:TypeName>

<DataField>xtype</DataField>

</Field>

<Field Name="uid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>uid</DataField>

</Field>

<Field Name="info">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>info</DataField>

</Field>

<Field Name="status">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>status</DataField>

</Field>

<Field Name="base_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>base_schema_ver</DataField>

</Field>

<Field Name="replinfo">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>replinfo</DataField>

</Field>

<Field Name="parent_obj">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>parent_obj</DataField>

</Field>

<Field Name="crdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>crdate</DataField>

</Field>

<Field Name="ftcatid">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>ftcatid</DataField>

</Field>

<Field Name="schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>schema_ver</DataField>

</Field>

<Field Name="stats_schema_ver">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>stats_schema_ver</DataField>

</Field>

<Field Name="type">

<rd:TypeName>System.String</rd:TypeName>

<DataField>type</DataField>

</Field>

<Field Name="userstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>userstat</DataField>

</Field>

<Field Name="sysstat">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>sysstat</DataField>

</Field>

<Field Name="indexdel">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>indexdel</DataField>

</Field>

<Field Name="refdate">

<rd:TypeName>System.DateTime</rd:TypeName>

<DataField>refdate</DataField>

</Field>

<Field Name="version">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>version</DataField>

</Field>

<Field Name="deltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>deltrig</DataField>

</Field>

<Field Name="instrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>instrig</DataField>

</Field>

<Field Name="updtrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>updtrig</DataField>

</Field>

<Field Name="seltrig">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>seltrig</DataField>

</Field>

<Field Name="category">

<rd:TypeName>System.Int32</rd:TypeName>

<DataField>category</DataField>

</Field>

<Field Name="cache">

<rd:TypeName>System.Int16</rd:TypeName>

<DataField>cache</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<Width>12.69841cm</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>2.5cm</TopMargin>

<PageHeight>29.7cm</PageHeight>

</Report>

Express cannot convert different data types automaticly?

I have a SQL Server 2000 database which works like a charm in my asp app. Joining tables by id columns which are of different data types (eg. int in one table and varchar in the other) is no problem with the SQL Server engine, but Native Client refuses to do so and hands me back an error. Even if columns have a slightly different collation (like Slovenian_CI_AS and SQL_Server_1250_CI_AS) SQL Express native client returns an ERROR?!!! Is it possible that Native client cannot resolve this on its own? Must I change the colattion and datatype on all my tables? Please help!!!!

Peca, developer from Serbia

If you have two columns in two tables, and one is, say PK in one table and FK on the other, then they really should be of the same datatype.

If possible, you should make changes where necessary.

I'm not familiar with Slovenian collations, but different collations may me incompatible between eachother, so there's a chance there could be errors when different collations are used at the same time.

/Kenneth

|||Yes Kenneth, you are right, but the same query works with the SQL SERVER 2000 engine and does not work with EXPRESS database engine. And the problem is that PK's cannot be of the same type in this case. "OLD" sql 2000 engine does not complain but the new one does. I just want to know if this is "solid rock rule" or I can change this behaviour and make him accept these querys?|||

Ah, I see... I haven't tried out those circumstances, so I'm sorry that I don't have a definite answer. Perhaps someone else will chime in with some insights.

/Kenneth

|||

All what you need is to specify collation explicitly in your queries while comparing charater data which collattion differ.

See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/76763ac8-3e0d-4bbb-aa53-f5e7da021daa.htm

|||

Yes, I know that, but thank you anyway... With the collation I can live, but joining tables with different data types as pk's is a different story. If I say - join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").This really works in SQL Server 2000 but not in Express (returns an error). I am developing databases for years now and I've been working on databases with millions of records and hundreds of tables. This database cannot be normalised more than it is...I'll just stick to 2000 and forget about Express...A BIG thanks to all that replied to y question!!!

|||

>>join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").

Joining two tables throug int field and varchar field, MSSQL would try to convert varchar values into integer value and would generate an error as soon as 'aaa' value reached, unless you specify otherwise in your query.

|||

Agreed. That example has never worked in SQL 2000 either.
the conversion will follow the rules for datatype precedence, and the varchar will be converted to int when implicit conversion comes into play.

So, this is not some new behaviour by Express.

Small demo below from SQL Server 2000: (this is the expected behaviour)

create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go
insert #a select 1, 'a'
insert #b select 1, 'b'
insert #b select 'aaa', 'b'
go
select *
from #a join #b
on #a.pk = #b.pk
go

pk a pk b
-- - - -
1 a 1 b

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'aaa' to a column of data type int.

/Kenneth

|||

Kenneth, did I mention that I have the WHERE clausule? Ok, let's end this now and for all ! These are table definitions and bellow is the query that works in SQL 2000 (I can send screenshots to all that do not believe me) and DOES NOT work with EXPRESS (both your and my example).

Your example a bit rewriten:
create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go

insert #a select 1, 'a'
insert #a select 2, 'b'
insert #b select 'aaa', 'be'
insert #b select '1', 'c'
insert #b select '2', 'd'

go

select * from #a ,#b
where #a.pk = #b.pk and (#b.b='c' or #b.b='g') and #a.pk in (1)
go

My example that does the same

table :KOMP_REF pk:SIFRA varchar(50)
table :VESTI pk:ID int

they are not referenced in any way (there is no explicit reference between them e.g. constraints, referencial integrity etc.)

query VESTI:
select id,headline,text,date from vesti where id=11
result:
11 heder <FONT face="Courier New".. 2005-12-23 11:51:13.107

query KOMP_REF:
select * from komp_ref
result:
...
lentro 7 4 43 11 81 1 1
11 7 4 127 -1 22 1 1
...

values 81 and 22 are of column SIFRA_KOMP so I believe that row with the value 81 should be excluded from the join by the where clausule in the query bellow

query:
select vesti.id from vesti,komp_ref
where vesti.id=komp_ref.sifra and
(komp_ref.sifra_komp=22 or komp_ref.sifra_komp=64 or komp_ref.sifra_komp=65 or
komp_ref.sifra_komp=91 or komp_ref.sifra_komp=92 or komp_ref.sifra_komp=93)
and vesti.id IN (11)

Express error msg:
Conversion failed when converting the varchar value 'lentro ' to data type int.

Database is copied from sql2000 into express (just copied files without any import procedure etc.)without any changes (I just changed the conn string in my ASP app). I understand all you said but WHY does 2000 do not report an error? Maybe that is the right question? Why is OR operator so confusing for Express? Without the or #b.b='g' even your example works in express but when I add it only SQL2000 returns a recordset? Any comments?!?

|||Sorry KeWin, I wrote Kenneth by mistake :)|||

:) No worries, Kenneth is what my mother calls me also.
(sry for the late reply)

Unfortunately I don't have the ability to try this on Express at the moment, but...
What if you tried rewriting the join ANSI style instead of the old legacy syntax.

select vesti.id
from vesti
join komp_ref
on vesti.id = komp_ref.sifra
and komp_ref.sifra_komp in ('22', '64', '65', '91', '92', '93')
and vesti.id = 11

..see if anything changes..?

/Kenneth

|||unfortunately...no... still reporting the same error... But that is to be expected since the exapmle I gave above has a 100% valid SQL syntax and there are no reasons why it would not work... And did I mention that it works with 2000? ;) (less than 2000 times daaaaa) ... If I accidentaly found a bug (hope not because it would be a huuuuuuuuge one) the boys and girls from MS owe me a beer :) thanx 4 all Kewin!

Express cannot convert different data types automaticly?

I have a SQL Server 2000 database which works like a charm in my asp app. Joining tables by id columns which are of different data types (eg. int in one table and varchar in the other) is no problem with the SQL Server engine, but Native Client refuses to do so and hands me back an error. Even if columns have a slightly different collation (like Slovenian_CI_AS and SQL_Server_1250_CI_AS) SQL Express native client returns an ERROR?!!! Is it possible that Native client cannot resolve this on its own? Must I change the colattion and datatype on all my tables? Please help!!!!

Peca, developer from Serbia

If you have two columns in two tables, and one is, say PK in one table and FK on the other, then they really should be of the same datatype.

If possible, you should make changes where necessary.

I'm not familiar with Slovenian collations, but different collations may me incompatible between eachother, so there's a chance there could be errors when different collations are used at the same time.

/Kenneth

|||Yes Kenneth, you are right, but the same query works with the SQL SERVER 2000 engine and does not work with EXPRESS database engine. And the problem is that PK's cannot be of the same type in this case. "OLD" sql 2000 engine does not complain but the new one does. I just want to know if this is "solid rock rule" or I can change this behaviour and make him accept these querys?|||

Ah, I see... I haven't tried out those circumstances, so I'm sorry that I don't have a definite answer. Perhaps someone else will chime in with some insights.

/Kenneth

|||

All what you need is to specify collation explicitly in your queries while comparing charater data which collattion differ.

See ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/76763ac8-3e0d-4bbb-aa53-f5e7da021daa.htm

|||

Yes, I know that, but thank you anyway... With the collation I can live, but joining tables with different data types as pk's is a different story. If I say - join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").This really works in SQL Server 2000 but not in Express (returns an error). I am developing databases for years now and I've been working on databases with millions of records and hundreds of tables. This database cannot be normalised more than it is...I'll just stick to 2000 and forget about Express...A BIG thanks to all that replied to y question!!!

|||

>>join the table A with pk datatype int and table B with datatype varchar engine should do the following - join the fields that match ( values 1 -int and "1" varchar) and discard the rest (varchar like "aaa").

Joining two tables throug int field and varchar field, MSSQL would try to convert varchar values into integer value and would generate an error as soon as 'aaa' value reached, unless you specify otherwise in your query.

|||

Agreed. That example has never worked in SQL 2000 either.
the conversion will follow the rules for datatype precedence, and the varchar will be converted to int when implicit conversion comes into play.

So, this is not some new behaviour by Express.

Small demo below from SQL Server 2000: (this is the expected behaviour)

create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go
insert #a select 1, 'a'
insert #b select 1, 'b'
insert #b select 'aaa', 'b'
go
select *
from #a join #b
on #a.pk = #b.pk
go

pk a pk b
-- - - -
1 a 1 b

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'aaa' to a column of data type int.

/Kenneth

|||

Kenneth, did I mention that I have the WHERE clausule? Ok, let's end this now and for all ! These are table definitions and bellow is the query that works in SQL 2000 (I can send screenshots to all that do not believe me) and DOES NOT work with EXPRESS (both your and my example).

Your example a bit rewriten:
create table #a (pk int not null, a varchar(10) not null)
create table #b (pk varchar(10) not null, b varchar(10) not null)
go

insert #a select 1, 'a'
insert #a select 2, 'b'
insert #b select 'aaa', 'be'
insert #b select '1', 'c'
insert #b select '2', 'd'

go

select * from #a ,#b
where #a.pk = #b.pk and (#b.b='c' or #b.b='g') and #a.pk in (1)
go

My example that does the same

table :KOMP_REF pk:SIFRA varchar(50)
table :VESTI pk:ID int

they are not referenced in any way (there is no explicit reference between them e.g. constraints, referencial integrity etc.)

query VESTI:
select id,headline,text,date from vesti where id=11
result:
11 heder <FONT face="Courier New".. 2005-12-23 11:51:13.107

query KOMP_REF:
select * from komp_ref
result:
...
lentro 7 4 43 11 81 1 1
11 7 4 127 -1 22 1 1
...

values 81 and 22 are of column SIFRA_KOMP so I believe that row with the value 81 should be excluded from the join by the where clausule in the query bellow

query:
select vesti.id from vesti,komp_ref
where vesti.id=komp_ref.sifra and
(komp_ref.sifra_komp=22 or komp_ref.sifra_komp=64 or komp_ref.sifra_komp=65 or
komp_ref.sifra_komp=91 or komp_ref.sifra_komp=92 or komp_ref.sifra_komp=93)
and vesti.id IN (11)

Express error msg:
Conversion failed when converting the varchar value 'lentro ' to data type int.

Database is copied from sql2000 into express (just copied files without any import procedure etc.)without any changes (I just changed the conn string in my ASP app). I understand all you said but WHY does 2000 do not report an error? Maybe that is the right question? Why is OR operator so confusing for Express? Without the or #b.b='g' even your example works in express but when I add it only SQL2000 returns a recordset? Any comments?!?

|||Sorry KeWin, I wrote Kenneth by mistake :)|||

:) No worries, Kenneth is what my mother calls me also.
(sry for the late reply)

Unfortunately I don't have the ability to try this on Express at the moment, but...
What if you tried rewriting the join ANSI style instead of the old legacy syntax.

select vesti.id
from vesti
join komp_ref
on vesti.id = komp_ref.sifra
and komp_ref.sifra_komp in ('22', '64', '65', '91', '92', '93')
and vesti.id = 11

..see if anything changes..?

/Kenneth

|||unfortunately...no... still reporting the same error... But that is to be expected since the exapmle I gave above has a 100% valid SQL syntax and there are no reasons why it would not work... And did I mention that it works with 2000? ;) (less than 2000 times daaaaa) ... If I accidentaly found a bug (hope not because it would be a huuuuuuuuge one) the boys and girls from MS owe me a beer :) thanx 4 all Kewin!

exporting to pdf

When we export a report as a PDF and if there are multiple columns in the
report, all the columns in the report do not fit in one single page in the
exported PDF. We would ideally like to accommodate all the columns within one
single page. The only way we could do this today is to reduce the width of
the columns , but this causes the Report to loose its clarity and readability.
Thanks in advance,Go to the report menu item and open "report properties". On the "layout"
tab, increase the "page width" property accordingly. Re-deploy the report,
run and export.
"Chandra" wrote:
> When we export a report as a PDF and if there are multiple columns in the
> report, all the columns in the report do not fit in one single page in the
> exported PDF. We would ideally like to accommodate all the columns within one
> single page. The only way we could do this today is to reduce the width of
> the columns , but this causes the Report to loose its clarity and readability.
> Thanks in advance,
>

Exporting to Excel Tips

Hi !
I am exporting a report to Excel. I have this issue where my labels that
are to the left of my data columns are exported to different rows.
Is there anyone out there can give me some tips or lessons learned when
designing a report that will be exported to Excel?
Thanks,
YI am having a similar problem. I am not using the column headings of the
grid because I need the column headings up in the page header. Instead I
created a row of textboxes that line up with the datagrid up in the header.
It looks great in pdf and html, but as soon as I export to excel some of the
textboxes get pushed down into a lower row. I haven't figured out why yet,
but I think it has something to do with merged cells in excel. If I find
anything I'll post it here.
sebring1130
"Yoshi" wrote:
> Hi !
> I am exporting a report to Excel. I have this issue where my labels that
> are to the left of my data columns are exported to different rows.
> Is there anyone out there can give me some tips or lessons learned when
> designing a report that will be exported to Excel?
> Thanks,
> Y
>
>|||Thanks. I will do the same. Glad to know there are others working on the
same issue.
Take care,
Y
"sebring1130" <sebring1130@.discussions.microsoft.com> wrote in message
news:8F97B4EC-D501-4C9F-B053-D7C867C39332@.microsoft.com...
>I am having a similar problem. I am not using the column headings of the
> grid because I need the column headings up in the page header. Instead I
> created a row of textboxes that line up with the datagrid up in the
> header.
> It looks great in pdf and html, but as soon as I export to excel some of
> the
> textboxes get pushed down into a lower row. I haven't figured out why
> yet,
> but I think it has something to do with merged cells in excel. If I find
> anything I'll post it here.
> sebring1130
>
> "Yoshi" wrote:
>> Hi !
>> I am exporting a report to Excel. I have this issue where my labels that
>> are to the left of my data columns are exported to different rows.
>> Is there anyone out there can give me some tips or lessons learned when
>> designing a report that will be exported to Excel?
>> Thanks,
>> Y
>>

Exporting to Excel

Hi

I am using the DTS package with SQL SERVER 2000. I have a table, and in one
of the columns are links to websites. I am trying to export this to Excel
2002 SP3

a typical text string stored in the table is

=HYPERLINK("www.asite.com","Click")

I had hoped the field would be transferred exactly as is and so would appear
as a hyper link in the Excel document it creates. Unfortunateld it seems to
automatically put a ' in front of it so if I click on the cell, in the bar
at the top I get

'=HYPERLINK("www.asite.com","Click") - Notice the apostrophe

so the full text is displayed in the spreadsheet (rather than the word
Click)

Does anyone know of a way to get rid of the ' thats being added on

thanks in advance

AndyThat's how Excel stores text strings that begin with =. It's how it
seperates it from an expression.

"aaj" <a.b@.c.com> wrote in message news:<40a8d330$0$8090$afc38c87@.news.easynet.co.uk>...
> Hi
> I am using the DTS package with SQL SERVER 2000. I have a table, and in one
> of the columns are links to websites. I am trying to export this to Excel
> 2002 SP3
> a typical text string stored in the table is
> =HYPERLINK("www.asite.com","Click")
> I had hoped the field would be transferred exactly as is and so would appear
> as a hyper link in the Excel document it creates. Unfortunateld it seems to
> automatically put a ' in front of it so if I click on the cell, in the bar
> at the top I get
> '=HYPERLINK("www.asite.com","Click") - Notice the apostrophe
> so the full text is displayed in the spreadsheet (rather than the word
> Click)
> Does anyone know of a way to get rid of the ' thats being added on
> thanks in advance
> Andy|||>
> Does anyone know of a way to get rid of the ' thats being added on
> thanks in advance
> Andy
If you can't find a solution from by modifying the DTS package you could
write a VBA macro in the Excel document that will run when the excel
document is opened and modify the contents of a particulur column
accordingly...

Exporting to CSV

I have a report which is just a simple table with only the detail row (which then as about a dozen columns)

I have uploaded this to report manger. The report generates correctly and I export to CSV.

But when I open the CSV file there are Headings at the top. I want to know is there any way of getting rid of there headers so it is jus the data which shows.

(The headers seem to be the name of the textbox column in reporting services)

See this post.

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

cheers,

Andrew