install SQL on WinXP: field

Showing posts with label field. Show all posts

Tuesday, March 27, 2012

Can not insert input field with length gt 128

I got this problem that really puzzled me. I used a stored procedure to
insert records into SQL. When the field of input string contained more than
128 characters, it gave me this message:
The identifier that starts with 'Today is a nice day..........Good bye'
is too long. Maximum length is 128.
But here I am not sending in an identifier name, I am sending in the actual
input which is more than 128 characters long. The input field in the data
base is defined as varchar(1000).
Any ideas?What is the size on the parameter declaration in the stored procedure? I'll
bet you a shiny new nickel it's VARCHAR(128) or CHAR(128).
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:D4B955D4-C318-4E38-BB9F-77B439561881@.microsoft.com...
>I got this problem that really puzzled me. I used a stored procedure to
> insert records into SQL. When the field of input string contained more
> than
> 128 characters, it gave me this message:
> The identifier that starts with 'Today is a nice day..........Good bye'
> is too long. Maximum length is 128.
> But here I am not sending in an identifier name, I am sending in the
> actual
> input which is more than 128 characters long. The input field in the data
> base is defined as varchar(1000).
> Any ideas?|||> What is the size on the parameter declaration in the stored procedure?
> I'll bet you a shiny new nickel it's VARCHAR(128) or CHAR(128).
Actually, it sounds like the call he is making to the database doesn't
properly encapsulate this string. It seems SQL Server has mistaken the
string for an identifier (otherwise the error would be "string or binary
data would be truncated"). This usually happens when someone uses " instead
of ' for delimiting a string, but there are other possibilities as well.
"pelican", how about showing us the actual code you are using, instead of
vaguely describing it -- then we don't have to guess. I suppose DDL for the
table you are trying to INSERT into wouldn't hurt either (see
http://www.aspfaq.com/5006).|||Good point, probably an embedded ' in the input somewhere that wasn't
properly escaped by the app. Interesting that it would pick 128 as the
cut-off size, however. I would definitely need to see the code for the SP
and the input string.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:u6KSE6AUFHA.2712@.TK2MSFTNGP09.phx.gbl...
> Actually, it sounds like the call he is making to the database doesn't
> properly encapsulate this string. It seems SQL Server has mistaken the
> string for an identifier (otherwise the error would be "string or binary
> data would be truncated"). This usually happens when someone uses "
> instead of ' for delimiting a string, but there are other possibilities as
> well.
> "pelican", how about showing us the actual code you are using, instead of
> vaguely describing it -- then we don't have to guess. I suppose DDL for
> the table you are trying to INSERT into wouldn't hurt either (see
> http://www.aspfaq.com/5006).
>|||> Good point, probably an embedded ' in the input somewhere that wasn't
> properly escaped by the app. Interesting that it would pick 128 as the
> cut-off size, however.
That is the maximum size for an identifier. I don't think it is an errant
', it just received this string that it thinks is supposed to be a column,
and is saying, WHOA, this column name is bigger than 128, hold on. It could
be 129 or 8000 characters... the same error will get kicked out.|||Thank you all so much! "AB - MVP" is right, once I changed the double quote
(") to single ('), it worked fine! In the stored procedure, the size on the
parameter is varchar(1000). I did not realize that the "" sign will make SQ
L
believe I was passing an identifier instead of the actually content.
You are my life saver...
"AB - MVP" wrote:

> Actually, it sounds like the call he is making to the database doesn't
> properly encapsulate this string. It seems SQL Server has mistaken the
> string for an identifier (otherwise the error would be "string or binary
> data would be truncated"). This usually happens when someone uses " inste
ad
> of ' for delimiting a string, but there are other possibilities as well.
> "pelican", how about showing us the actual code you are using, instead of
> vaguely describing it -- then we don't have to guess. I suppose DDL for th
e
> table you are trying to INSERT into wouldn't hurt either (see
> http://www.aspfaq.com/5006).
>
>|||I read about the identifier length in the help section. It said the same
thing as AB-MVP pointed out, that 128 is the maximum length per row for an
identifier. I just never dreamed that a " will do it.
Thanks a lot!|||Yeah it's called a "quoted identifier" and can be circumvented by using
"parameterized queries".
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:6F3F8CAB-1EBD-45A6-BF74-4DC272EA1804@.microsoft.com...
> Thank you all so much! "AB - MVP" is right, once I changed the double
> quote
> (") to single ('), it worked fine! In the stored procedure, the size on
> the
> parameter is varchar(1000). I did not realize that the "" sign will make
> SQL
> believe I was passing an identifier instead of the actually content.
> You are my life saver...
> "AB - MVP" wrote:
>|||Yep that's what they get for slapping a SQL statement together with +'s
instead of using parameterized queries.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:uGWbvCBUFHA.1896@.TK2MSFTNGP14.phx.gbl...
> That is the maximum size for an identifier. I don't think it is an errant
> ', it just received this string that it thinks is supposed to be a column,
> and is saying, WHOA, this column name is bigger than 128, hold on. It
> could be 129 or 8000 characters... the same error will get kicked out.
>

Tuesday, March 20, 2012

Can not change filter on model report - UPDATED 7th June

I have a very simple report based on a test model.

The report is filtered via a date field and runs ok.

If I try to change the filter I get the following error message:

Operation Failed

Short time pattern does not include hours and minutes

Does anyone know how I can correct this?

Regards

John

UPDATE

This only happens if I try to filter between two dates, a single date works correctly!

Can you please save report that fails to file and post it?|||

Alexandre

This is the rdl for the failing report.

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

<Query>

<DataSourceName>dataSource1</DataSourceName>

</BaseEntity>

</EntityRef>

</Expression>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

<Path>

</RolePathItem>

</Path>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

</Details>

</Grouping>

</Groupings>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperty>

</CustomProperties>

</Expression>

</Filter>

</Hierarchy>

</Hierarchies>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperties>

</Expression>

</CustomProperties>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperties>

</Expression>

</CalculatedAttributes>

</SemanticQuery></CommandText>

</Query>

<DataField>Epex Referral</DataField>

</Field>

<DataField>Created</DataField>

</Field>

<DataField>Close Date</DataField>

</Field>

<DataField>Patient</DataField>

</Field>

</Field>

</Fields>

</DataSet>

</DataSets>

<DataSourceReference>/Models/Mars Cutdown</DataSourceReference>

<rd:DataSourceID>4d1edcd6-3c78-4e68-9566-ef952e495be4</rd:DataSourceID>

</DataSource>

</DataSources>

<Body>

<Style>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:Watermark</Name>

<Value>Click to add title</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

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

<Styl>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

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

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

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

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

<GroupExpression>=Fields!EpexReferral.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=IIf(True, Fields!expr1.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

<SortExpression>=IIf(True, Fields!Created.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

</Sorting>

</Details>

<DataSetName>dataSet</DataSetName>

<Hidden>=Fields!Created.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!CloseDate.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!Patient.IsMissing</Hidden>

</Visibility>

</TableColumn>

</TableColumns>

<SortExpression>=Fields!Created.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Created</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!CloseDate.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Close Date</Value>

<Style

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!Patient.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Patient</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</Table>

<Value>=String.Format("Total Epex Referrals" & Chr(58) & " {0}", COUNTROWS("dataSet"))</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

<Value>TotalRows</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<Value>Filter: Epex Referrals with: Created from 01/01/2005 to 01/01/2006</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

Value>FilterDescription</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

</ReportItems>

</Body>

</Report>

|||

Is this error happening when you are editing report or when you are running this report? Can you save this report to report server and run it from Report Manager (http://rshost/reports)?

Thanks!

|||

The report executes with no problems, I only get the error if I try to change the filter.

What is strange, I can get around the error by apply to separate date filters, one using Greater Than Or Equal and the other Less Than or Equal.

This gets around the problem but I would like to offer my end users the option of From and To as this is what the use in other parts of SSRS.

Regards

John

|||

We have the same problem even after installing SP1 and the hotfixes. Has anybody a solution/answer?

Regards

Werner

|||This is a known issue that will be fixed in SP2. Unfortunately the only way to avoid this error for now is to change your Windows regional settings to use a 12-hour clock.|||

Bob,

thanks for the immediate answer, when can we approx. expect SP2?

Regards

Werner

|||No committed dates yet, but I believe it's on track for later this year.|||

Bob,

i tried to change the format in the regional settings like you said but no changes. We use a german W2003 server... When i select a date field in the report builder filter, select a date from the datetime picker (22.06.2006) and try to save it with the ok button i get a message box saying 'no valid date'...?!?

Can i change anything in the model to solve the problem? Is this filter problem caused by SP1 (in this case we could think about setting up the SQL Server again)?

Regards

Werner

Can not change filter on model report - UPDATED

I have a very simple report based on a test model.

The report is filtered via a date field and runs ok.

If I try to change the filter I get the following error message:

Operation Failed

Short time pattern does not include hours and minutes

Does anyone know how I can correct this?

Regards

John

UPDATE

This only happens if I try to filter between two dates, a single date works correctly!

Can you please save report that fails to file and post it?|||

Alexandre

This is the rdl for the failing report.

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

<Query>

<DataSourceName>dataSource1</DataSourceName>

</BaseEntity>

</EntityRef>

</Expression>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

<Path>

</RolePathItem>

</Path>

</AttributeRef>

</Expression>

</AttributeRef>

</Expression>

</Details>

</Grouping>

</Groupings>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperty>

</CustomProperties>

</Expression>

</Filter>

</Hierarchy>

</Hierarchies>

<FunctionName>GreaterThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

<FunctionName>LessThanOrEquals</FunctionName>

</AttributeRef>

</Expression>

<DataType>DateTime</DataType>

</Literal>

</Expression>

</Arguments>

</Function>

</Expression>

</Arguments>

</Function>

</CustomProperties>

</Expression>

</CustomProperties>

</Expression>

</Arguments>

</Function>

</CustomProperty>

</CustomProperties>

</Expression>

</CalculatedAttributes>

</SemanticQuery></CommandText>

</Query>

<DataField>Epex Referral</DataField>

</Field>

<DataField>Created</DataField>

</Field>

<DataField>Close Date</DataField>

</Field>

<DataField>Patient</DataField>

</Field>

</Field>

</Fields>

</DataSet>

</DataSets>

<DataSourceReference>/Models/Mars Cutdown</DataSourceReference>

<rd:DataSourceID>4d1edcd6-3c78-4e68-9566-ef952e495be4</rd:DataSourceID>

</DataSource>

</DataSources>

<Body>

<Style>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:Watermark</Name>

<Value>Click to add title</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

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

<Styl>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

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

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<DataElementOutput>Output</DataElementOutput>

<ReportName>=DataSources!dataSource1.DataSourceReference</ReportName>

</Parameter>

<Value>Detail</Value>

</Parameter>

<Value>Drillthrough</Value>

</Parameter>

<Value>=DataSets!dataSet.RewrittenCommandText</Value>

</Parameter>

<Value>=CreateDrillthroughContext()</Value>

</Parameter>

</Parameters>

</Drillthrough>

</Action>

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

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<FontWeight>Normal</FontWeight>

<BackgroundColor>White</BackgroundColor>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

<GroupExpression>=Fields!EpexReferral.Value</GroupExpression>

</GroupExpressions>

</Grouping>

<SortExpression>=IIf(True, Fields!expr1.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

<SortExpression>=IIf(True, Fields!Created.Value, Nothing)</SortExpression>

<Direction>Descending</Direction>

</SortBy>

</Sorting>

</Details>

<DataSetName>dataSet</DataSetName>

<Hidden>=Fields!Created.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!CloseDate.IsMissing</Hidden>

</Visibility>

</TableColumn>

<Hidden>=Fields!Patient.IsMissing</Hidden>

</Visibility>

</TableColumn>

</TableColumns>

<SortExpression>=Fields!Created.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Created</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!CloseDate.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Close Date</Value>

<Style

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

<SortExpression>=Fields!Patient.Value</SortExpression>

<SortExpressionScope>table_EpexReferral</SortExpressionScope>

</UserSort>

<Value>Patient</Value>

<Style>

<Default>LightGrey</Default>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Tahoma</FontFamily>

<Color>White</Color>

</Style>

</Textbox>

</ReportItems>

</TableCell>

</TableCells>

</TableRow>

</TableRows>

</Header>

</Table>

<Value>=String.Format("Total Epex Referrals" & Chr(58) & " {0}", COUNTROWS("dataSet"))</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

<Value>TotalRows</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

<Value>Filter: Epex Referrals with: Created from 01/01/2005 to 01/01/2006</Value>

<Style>

<FontWeight>Normal</FontWeight>

</Style>

<Name>rb:SpecialContent</Name>

Value>FilterDescription</Value>

</CustomProperty>

</CustomProperties>

</Textbox>

</ReportItems>

</Body>

</Report>

|||

Is this error happening when you are editing report or when you are running this report? Can you save this report to report server and run it from Report Manager (http://rshost/reports)?

Thanks!

|||

The report executes with no problems, I only get the error if I try to change the filter.

What is strange, I can get around the error by apply to separate date filters, one using Greater Than Or Equal and the other Less Than or Equal.

This gets around the problem but I would like to offer my end users the option of From and To as this is what the use in other parts of SSRS.

Regards

John

|||

We have the same problem even after installing SP1 and the hotfixes. Has anybody a solution/answer?

Regards

Werner

|||This is a known issue that will be fixed in SP2. Unfortunately the only way to avoid this error for now is to change your Windows regional settings to use a 12-hour clock.|||

Bob,

thanks for the immediate answer, when can we approx. expect SP2?

Regards

Werner

|||No committed dates yet, but I believe it's on track for later this year.|||

Bob,

Can i change anything in the model to solve the problem? Is this filter problem caused by SP1 (in this case we could think about setting up the SQL Server again)?

Regards

Werner

sql

Monday, March 19, 2012

Can NEWLINE in FORMAT?

I have a date field and use the format: dd MM/dd/yy
The output is: Fri 10/15/04
I would like this instead:
Fri
10/15/04
Is there a way to add a line break using the simple format codes?
Thanks, JerryI have found that adding
... & chr(10) & chr(13) & ...
seems to do the trick.
"Jerry Nixon" wrote:
> I have a date field and use the format: dd MM/dd/yy
> The output is: Fri 10/15/04
> I would like this instead:
> Fri
> 10/15/04
> Is there a way to add a line break using the simple format codes?
> Thanks, Jerry
>|||I do not know of a way that you can add a line break when typing the format
code into the Format property. It can be done in several ways when using an
expression. For example:
=Format( Fields!YoureDate.Value, "ddd") + ControlChars.crlf + Format(
Fields!YourDate.Value, "MM/dd/yy")
"Jerry Nixon" wrote:
> I have a date field and use the format: dd MM/dd/yy
> The output is: Fri 10/15/04
> I would like this instead:
> Fri
> 10/15/04
> Is there a way to add a line break using the simple format codes?
> Thanks, Jerry
>

Can MSRS render HTML?

Hi,
I have a HTML tags inside on of my dataset's fields. Is there anyway that I
have the contents of the field rendered on the report?
Can I at least extract the pure text from the HTML tags and show them on the
report?
Thank you,
AlanReporting Services currently does not support rich text fields. This is
planned for a future version. You can write a custom function to strip out
the HTML tags.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"A.M" <Hate-Spam@.nowhere.com> wrote in message
news:%231Id6APUFHA.2768@.tk2msftngp13.phx.gbl...
> Hi,
>
> I have a HTML tags inside on of my dataset's fields. Is there anyway that
> I have the contents of the field rendered on the report?
>
> Can I at least extract the pure text from the HTML tags and show them on
> the report?
>
> Thank you,
> Alan
>
>

Thursday, March 8, 2012

Can Iif( do a substring query on a storage field?

Ok,
This is what I don't understand. Hidden rows with downline dealers
only has '5678'. But the visible row that needs a drill down might
have in the dealerlineageid field (which is a field not to be
displayed)
'5678' and '2754' and 10 other dealer numbers (multiple dealer
numbers).
How would I know if '5678' is in there as a string value (which is just
one of the many numbers in that field)? If it has multiple
dealer numbers separated by commas? Wouldn't the expression for the
"hidden" value need to be a substring query? How would I do that?
Thanks,
TrintI always prefer preparing the data from base on. Where do you get the data
you want to render in the report, is there a chance to normalize it before
passing it to the report ?
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"trint" <trinity.smith@.gmail.com> schrieb im Newsbeitrag
news:1113916563.747603.202200@.l41g2000cwc.googlegroups.com...
> Ok,
> This is what I don't understand. Hidden rows with downline dealers
> only has '5678'. But the visible row that needs a drill down might
> have in the dealerlineageid field (which is a field not to be
> displayed)
> '5678' and '2754' and 10 other dealer numbers (multiple dealer
> numbers).
> How would I know if '5678' is in there as a string value (which is just
> one of the many numbers in that field)? If it has multiple
> dealer numbers separated by commas? Wouldn't the expression for the
> "hidden" value need to be a substring query? How would I do that?
> Thanks,
> Trint
>|||No, it comes from a group within our company that is dedicated only
toward sql server and I am only in c#.net and reporting services.
Trint

can I use the value of field in last record?

I handle a output by a function with three parameters

the first is a value of Field [a1] in current record,

the second is value of Field [a1] in last record,

the third is value of Field [a1] in next record,

is it possible in RS2005?

Yes you can use them in a given scope (table/list):

Fields!a1.Value

Last(Fields!a1.Value, "Dataset1")

Fields!a1.Value(RowNumber("Dataset1")+1)

Shyam

Saturday, February 25, 2012

Can I use a data field returned from my report as part of the URL address?

I am familiar with the navigation hyperlink action of "Jump to URL" but I cant get it to use the data returned in the report as part of the URL address. When I enter the Url into the expression editor: ="http://internal.company.address/sys1/sys2/caseEdit.pl?case="&"Fields!CaseID.Value" it opens an explorer window but it echos the address and tries to use the text 'Fields!CaseID.Value' instead of the data returned in that field.

Can this be done?

="http://internal.company.address/sys1/sys2/caseEdit.pl?case=" & Fields!CaseID.Value

|||Thanks, this answers my question. Seems so simply once I see the answer.

Friday, February 24, 2012

Can I split a field based on a character?

Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.Here's a question for the SQL gurus out there:
I have a varchar(20) field DIAGNOSISCODE in a table that can either be null, or contain up to 3 comma-separated codes, each of which relates to a description in another table. For example, some sample rows might be
8060
8060,4450
8060,4123,3245
Now I need to structure a query to return these values from this single field as three fields CODE1, CODE2, CODE3, with NULL as appropriate for example
CODE1=8060, CODE2=4450, CODE3=NULL.
I have been using CASE along with CHARINDEX and PATINDEX but it it becoming extremely messy. Can anyone think of a "neater" way to return three fields from this one field?
Any help very greatly appreciated.
Thanks, Simon.

Don't let blindman see this post; he'll pull out the complete works of E.F. Codd!!! :D

Seriously, do a google on fn_Split(). I think you will find it will suit your purposes.

Regards,

hmscott|||check that site,I think that is the best and simple way to do.
http://www.sqlteam.com/item.asp?ItemID=2652|||The requirements are very close to the ones of generating a remittance advice when running Claims Reimbursement...It's been awhile, but I had to deal with the same scenario. First, I stored Dx codes separately from Claim Details. I did have a reference from ClaimDxCodes back to Claim Details table to retain dependency of CPT codes on diagnosis codes (Dx). When printing the Remittance Advice I transformed the data to present it in the format that you're trying to accomplish, using Crystal (the original app was written in VB4 with SQL 6.5). Later, when I recoded the reporting piece for ActiveReports control, I created a sub-report to display the Dx codes as a comma-separated list. But the concept remained the same, - ClaimHeader-->>ClaimDetail-->>ClaimDxCodes. Do not store Dx codes in the same table as your CPT codes, or your claim header info! Doing so violates the fundamentals of relational database principles, and B. Lindman WILL pull out the complete works of Codd at you...Let the massacre begin!..|||That's got me out of a tight spot, thanks very much.
Cheers, Simon.|||I'm really a nice guy. Really.

Sunday, February 19, 2012

Can I sort on fields by in paramters

I need to sort a report in 3 ways 2 fields and one expression.
When i add a field to a parameter it says the following:
"Fields cannot be used in report parameter expressions"
The expression I need to sort on is this
=(sum(Fields!p1beløb.Value) - sum(Fields!p2beløb.Value)) /
(0.001+sum(Fields!p1beløb.Value))
It works if I use this as sort on the group item, but I need to choose
between 3 kinds.
How can I sort the report if I can't use fields to tell how I want it
sorted
?
Jack
--
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at fÃ¥ 45071 spam-mails.
Betalende brugere fÃ¥r ikke denne besked i deres e-mails.
Hent gratis SPAMfighter her: www.spamfighter.dkCreate a function in the code area which accepts the Parameter ( 1,2 or 3 )
and all of the fields you wish to sort on..
The function should use the parameter in a case/switch statement to choose
which value to return. In one case it should return the value of the
expression you mentioned.
In the sort column pu
=Code.GetSort(Parameters!SortVal.Value,......)
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
news:ebEGvm9tFHA.1472@.TK2MSFTNGP15.phx.gbl...
>I need to sort a report in 3 ways 2 fields and one expression.
> When i add a field to a parameter it says the following:
> "Fields cannot be used in report parameter expressions"
> The expression I need to sort on is this
> =(sum(Fields!p1beløb.Value) - sum(Fields!p2beløb.Value)) /
> (0.001+sum(Fields!p1beløb.Value))
> It works if I use this as sort on the group item, but I need to choose
> between 3 kinds.
> How can I sort the report if I can't use fields to tell how I want it
> sorted
> ?
> Jack
>
>
> --
> Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> Den har indtil videre sparet mig for at fÃ¥ 45071 spam-mails.
> Betalende brugere fÃ¥r ikke denne besked i deres e-mails.
> Hent gratis SPAMfighter her: www.spamfighter.dk
>|||I think this is somehow difficult, doesn't other designers need to sort the
result differently based upon fields ?
Jack
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> skrev i en meddelelse
news:eZbuG$DuFHA.3740@.TK2MSFTNGP14.phx.gbl...
> Create a function in the code area which accepts the Parameter ( 1,2 or
3 )
> and all of the fields you wish to sort on..
> The function should use the parameter in a case/switch statement to choose
> which value to return. In one case it should return the value of the
> expression you mentioned.
> In the sort column pu
> =Code.GetSort(Parameters!SortVal.Value,......)
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jack Nielsen" <no_spam jack.nielsen@.get2net.dk> wrote in message
> news:ebEGvm9tFHA.1472@.TK2MSFTNGP15.phx.gbl...
> >I need to sort a report in 3 ways 2 fields and one expression.
> >
> > When i add a field to a parameter it says the following:
> >
> > "Fields cannot be used in report parameter expressions"
> >
> > The expression I need to sort on is this
> > =(sum(Fields!p1beløb.Value) - sum(Fields!p2beløb.Value)) /
> > (0.001+sum(Fields!p1beløb.Value))
> >
> > It works if I use this as sort on the group item, but I need to choose
> > between 3 kinds.
> >
> > How can I sort the report if I can't use fields to tell how I want it
> > sorted
> > ?
> >
> > Jack
> >
> >
> >
> >
> > --
> > Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
> > Den har indtil videre sparet mig for at fÃ¥ 45071 spam-mails.
> > Betalende brugere fÃ¥r ikke denne besked i deres e-mails.
> > Hent gratis SPAMfighter her: www.spamfighter.dk
> >
> >
>

Sunday, February 12, 2012

Can I output the result of sp_spaceused to a table ?

Firstly, I created a table : test01 as follow :
Field Data type
db_name char (80)
db_size char (20)
unallocated_space char (20)
reserved char (20)
data char (20)
index_size char (20)
unused char (20)
Then, I executed this command in SQL Analyzer :
INSERT INTO test01 EXEC sp_spaceused
But I only got this result :
Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
Insert Error: Column name or number of supplied values does not match table
definition
Can I output the result of : EXEC sp_spaceused to a table ?
Was my SQL statement correct or incorrect ?sp_spaceused has two result sets, so no you can't do that directly...
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:c8t0tc$91f2@.imsp212.netvigator.com...
> Firstly, I created a table : test01 as follow :
> Field Data type
> db_name char (80)
> db_size char (20)
> unallocated_space char (20)
> reserved char (20)
> data char (20)
> index_size char (20)
> unused char (20)
> Then, I executed this command in SQL Analyzer :
> INSERT INTO test01 EXEC sp_spaceused
> But I only got this result :
> Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
> Insert Error: Column name or number of supplied values does not match
table
> definition
>
> Can I output the result of : EXEC sp_spaceused to a table ?
> Was my SQL statement correct or incorrect ?
>
>|||You can do that at a table level:
Example for Northwind Employees table.
Create Table #Temp1
(Tablename CHAR(80),
rows int,
un_space VARCHAR(50),
reserved VARCHAR(50),
data VARCHAR(50),
index_size VARCHAR(50))
GO
Insert into #Temp1
Exec sp_spaceused 'Employees'
GO
Select * from #Temp1
GO
Drop table #Temp1
GO
>--Original Message--
>Firstly, I created a table : test01 as follow :
>Field Data type
>db_name char (80)
>db_size char (20)
>unallocated_space char (20)
>reserved char (20)
>data char (20)
>index_size char (20)
>unused char (20)
>Then, I executed this command in SQL Analyzer :
>INSERT INTO test01 EXEC sp_spaceused
>But I only got this result :
>Server: Msg 213, Level 16, State 7, Procedure
sp_spaceused, Line 142
>Insert Error: Column name or number of supplied values
does not match table
>definition
>
>Can I output the result of : EXEC sp_spaceused to a
table ?
>Was my SQL statement correct or incorrect ?
>
>.
>|||Try looking at the code in master..sp_spaceused. You could probably pick
out the portions into separate procs. Would loose the features of new
sp_spaceused during a SQL Server version upgrade but if you are desperate
enough this is an option.
-Paritosh
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:eoF5B4ZQEHA.3744@.TK2MSFTNGP10.phx.gbl...
> sp_spaceused has two result sets, so no you can't do that directly...
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:c8t0tc$91f2@.imsp212.netvigator.com...
> > Firstly, I created a table : test01 as follow :
> >
> > Field Data type
> > db_name char (80)
> > db_size char (20)
> > unallocated_space char (20)
> > reserved char (20)
> > data char (20)
> > index_size char (20)
> > unused char (20)
> >
> > Then, I executed this command in SQL Analyzer :
> >
> > INSERT INTO test01 EXEC sp_spaceused
> >
> > But I only got this result :
> >
> > Server: Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 142
> > Insert Error: Column name or number of supplied values does not match
> table
> > definition
> >
> >
> > Can I output the result of : EXEC sp_spaceused to a table ?
> > Was my SQL statement correct or incorrect ?
> >
> >
> >
>|||> Would loose the features of new
> sp_spaceused during a SQL Server version upgrade but if you are desperate
> enough this is an option.
Right, but to be honest, that is slightly safer than relying on the sp_ to
not change... since if you *could* do insert #table exec sp_spaceused and
the resultset changes, suddenly your code breaks. If your code is merely
*based on* the sp_ then it won't break when the sp_ changes.
Then again, your code would likely rely on calls to system tables such as
sysindexes etc. which are going to change and which will eventually
disappear, so neither method would be truly safe...
A|||Thanks to everybodies.
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:115ac01c441a1$917301b0$a001280a@.phx.gbl...
> You can do that at a table level:
> Example for Northwind Employees table.
>
> Create Table #Temp1
> (Tablename CHAR(80),
> rows int,
> un_space VARCHAR(50),
> reserved VARCHAR(50),
> data VARCHAR(50),
> index_size VARCHAR(50))
> GO
> Insert into #Temp1
> Exec sp_spaceused 'Employees'
> GO
> Select * from #Temp1
> GO
> Drop table #Temp1
> GO
>
> >--Original Message--
> >Firstly, I created a table : test01 as follow :
> >
> >Field Data type
> >db_name char (80)
> >db_size char (20)
> >unallocated_space char (20)
> >reserved char (20)
> >data char (20)
> >index_size char (20)
> >unused char (20)
> >
> >Then, I executed this command in SQL Analyzer :
> >
> >INSERT INTO test01 EXEC sp_spaceused
> >
> >But I only got this result :
> >
> >Server: Msg 213, Level 16, State 7, Procedure
> sp_spaceused, Line 142
> >Insert Error: Column name or number of supplied values
> does not match table
> >definition
> >
> >
> >Can I output the result of : EXEC sp_spaceused to a
> table ?
> >Was my SQL statement correct or incorrect ?
> >
> >
> >
> >.
> >

Tuesday, March 27, 2012

Can not insert input field with length gt 128

Tuesday, March 20, 2012

Can not change filter on model report - UPDATED 7th June

Can not change filter on model report - UPDATED

Monday, March 19, 2012

Can NEWLINE in FORMAT?

Can MSRS render HTML?

Thursday, March 8, 2012

Can Iif( do a substring query on a storage field?

can I use the value of field in last record?

Saturday, February 25, 2012

Can I use a data field returned from my report as part of the URL address?

Friday, February 24, 2012

Can I split a field based on a character?

Sunday, February 19, 2012

Can I sort on fields by in paramters

Sunday, February 12, 2012

Can I output the result of sp_spaceused to a table ?

Can I output the result of sp_spaceused to a table ?

Can I output the result of sp_spaceused to a table ?

install SQL on WinXP

Blog Archive

About Me