Hide Duplicates Attribute Not working

We are creating a matrix report with multiple row headers. There are
duplicate values in the first column. The duplicate values are hidden but we
would like them to be displayed. The â'HideDuplicatesâ' attribute is
unchecked. Ultimately, this report will be exported to Excel and we will be
using these column values to filter the data so I need values in every column
for every row. Is there another attribute that we are missing?
Thanks,
TylerHi Tyler
Did you find a solution to this problem? We have the exact same requirement
- need to output a matrix report to Excel with all cells showing.
Thanks
"Tyler Allbritton" wrote:
> We are creating a matrix report with multiple row headers. There are
> duplicate values in the first column. The duplicate values are hidden but we
> would like them to be displayed. The â'HideDuplicatesâ' attribute is
> unchecked. Ultimately, this report will be exported to Excel and we will be
> using these column values to filter the data so I need values in every column
> for every row. Is there another attribute that we are missing?
> Thanks,
> Tyler|||No - I'm an MSDN subscriber, so I expected a response from MS, but nothing.
"Phil Jeffrey" wrote:
> Hi Tyler
> Did you find a solution to this problem? We have the exact same requirement
> - need to output a matrix report to Excel with all cells showing.
> Thanks
>
> "Tyler Allbritton" wrote:
> > We are creating a matrix report with multiple row headers. There are
> > duplicate values in the first column. The duplicate values are hidden but we
> > would like them to be displayed. The â'HideDuplicatesâ' attribute is
> > unchecked. Ultimately, this report will be exported to Excel and we will be
> > using these column values to filter the data so I need values in every column
> > for every row. Is there another attribute that we are missing?
> >
> > Thanks,
> > Tyler

hide columns with no data

I'm trying to create a table where if the column has no data then it is not
visible. e.g. Sum(values)=0 then hide=true type of thing
This is causing a problem with printing PDF as this still includes all the
blank columns, I understand the explanation as to why this happens.
I've scoured the web and have seen many suggestions but nothing seems to work.
The 'cangrow' property is only valid for height not width.
It is not possible to set an expression on width etc etc
Did anyone ever figure out a way around this issue?I would suggest you to do this hiding part or remove the column itself using
your sql query and use matrix so that depending on the column this will be
displayed.
and all this can be done, provided you dont change/show/hide the columns
very frequently.
Amarnath
"adolf garlic" wrote:
> I'm trying to create a table where if the column has no data then it is not
> visible. e.g. Sum(values)=0 then hide=true type of thing
> This is causing a problem with printing PDF as this still includes all the
> blank columns, I understand the explanation as to why this happens.
> I've scoured the web and have seen many suggestions but nothing seems to work.
> The 'cangrow' property is only valid for height not width.
> It is not possible to set an expression on width etc etc
> Did anyone ever figure out a way around this issue?

hide column in a matrix

I'm trying to hide a column in a matrix. I have noticed that you can't just
set the columns visibility mode (no such proprty) so I tried the following:
1. set the visibility of the cells and lower level headers to hidden - works
but the upper level header still takes the space of the hidden lower level
column
2. set the column's width - it can't get an expression. and there is no way
of setting it to be 0 (0.03 is the min)
Any ideas?
Thanks,
EfiHello Efi,
Unfortunately, since the Matrix Column is used for show the Matrix data and
it could not be set to hidden.
You may need to use the Dynamic Group in the dataset.
Sincerely,
Wei Lu
Microsoft Online Community 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.|||Can you please elaborate on using Dynamic Group in the dataset.
Thanks,
"Wei Lu [MSFT]" wrote:
> Hello Efi,
> Unfortunately, since the Matrix Column is used for show the Matrix data and
> it could not be set to hidden.
> You may need to use the Dynamic Group in the dataset.
> Sincerely,
> Wei Lu
> Microsoft Online Community 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.
>|||Hello Efi,
For example, you may use the Union All to Union the data set and add a
filter in the Matrix to filter the column.
Sincerely,
Wei Lu
Microsoft Online Community 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 ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community 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.

Hide Column Header in the Results

I have a stored procedure and I want to save the results in a file. I don't
want the column header to be in the file. I need to generate the file daily.
It has just one column. Example the results is showing:
Msg
{1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111XXX@.MYE@.-}
{2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111X@.MYE@.-}
I want the following results without the Header and extra line in the file.
How can I achive this. I have tried isql and isql/w but no success yet.
{1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111XXX@.MYE@.-}
{2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111X@.MYE@.-}
Use osql instead, with option -h -1.
Example:
C:\TEMP>osql -Spivotalr5 -E -Q"select top 5 orderid, orderdate from
northwind..o
rders" -h-1
AMB
"Fraz" wrote:

> I have a stored procedure and I want to save the results in a file. I don't
> want the column header to be in the file. I need to generate the file daily.
> It has just one column. Example the results is showing:
> Msg
> --
> {1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111XXX@.MYE@.-}
> {2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111X@.MYE@.-}
> I want the following results without the Header and extra line in the file.
> How can I achive this. I have tried isql and isql/w but no success yet.
> {1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111XXX@.MYE@.-}
> {2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D :111X@.MYE@.-}
>

Hide Column Header in the Results

I have a stored procedure and I want to save the results in a file. I don't
want the column header to be in the file. I need to generate the file daily.
It has just one column. Example the results is showing:
Msg
--
& #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
& #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111X@.MYE@.-}
I want the following results without the Header and extra line in the file.
How can I achive this. I have tried isql and isql/w but no success yet.
& #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
& #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111X@.MYE@.-}Use osql instead, with option -h -1.
Example:
C:\TEMP>osql -Spivotalr5 -E -Q"select top 5 orderid, orderdate from
northwind..o
rders" -h-1
AMB
"Fraz" wrote:

> I have a stored procedure and I want to save the results in a file. I don'
t
> want the column header to be in the file. I need to generate the file dail
y.
> It has just one column. Example the results is showing:
> Msg
> --
> & #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111X@.MYE@.-}
> I want the following results without the Header and extra line in the file
.
> How can I achive this. I have tried isql and isql/w but no success yet.
> & #123;1:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111XXX@.MYE@.-}
> & #123;2:@.:20:040705@.:21B:XXXX@.:33A:040705
XXX00000,@.:55D:111X@.MYE@.-}
>

Hide Column Header in the Results

I have a stored procedure and I want to save the results in a file. I don't
want the column header to be in the file. I need to generate the file daily.
It has just one column. Example the results is showing:
Msg
--
{1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111XXX@.MYE@.-}
{2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111X@.MYE@.-}
I want the following results without the Header and extra line in the file.
How can I achive this. I have tried isql and isql/w but no success yet.
{1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111XXX@.MYE@.-}
{2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111X@.MYE@.-}Use osql instead, with option -h -1.
Example:
C:\TEMP>osql -Spivotalr5 -E -Q"select top 5 orderid, orderdate from
northwind..o
rders" -h-1
AMB
"Fraz" wrote:
> I have a stored procedure and I want to save the results in a file. I don't
> want the column header to be in the file. I need to generate the file daily.
> It has just one column. Example the results is showing:
> Msg
> --
> {1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111XXX@.MYE@.-}
> {2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111X@.MYE@.-}
> I want the following results without the Header and extra line in the file.
> How can I achive this. I have tried isql and isql/w but no success yet.
> {1:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111XXX@.MYE@.-}
> {2:@.:20:040705@.:21B:XXXX@.:33A:040705XXX00000,@.:55D:111X@.MYE@.-}
>sql

hide column based on Parameter

Hello All,
I need an expression that hides a column based on the report parameter.
So, I add the following expression to column Visibility property:
=IIF(Parameters!active.Value = "Current", True, False)
Which gives me the following expression error:
"Input string was not in the correct format"
Any help is appreciated, I've already searched through previous newgroups
entries/bol with no luck.
Thanks in advance,
RobMy apologies.
Issue has been resolved.
Thanks, R
"Rob" wrote:
> Hello All,
> I need an expression that hides a column based on the report parameter.
> So, I add the following expression to column Visibility property:
> =IIF(Parameters!active.Value = "Current", True, False)
> Which gives me the following expression error:
> "Input string was not in the correct format"
> Any help is appreciated, I've already searched through previous newgroups
> entries/bol with no luck.
> Thanks in advance,
> Rob
>
>

Hide Calculated Column in Matrix Report And show only in Total

Hi All,

I need to show the Cumulative calculated value only in Total by year/Group. I could not use Visibility expression using

InScope, as it creates *Blank column. Please go thru details below.

Year

Month01 02 03 Total

Salary Salary Salary Salary Cumulative (Calc)

Employee01 20 5 25 25

Employee02 10 10 20 45

.....

Total

How can i achieve this?. Any suggestion on this would be appreciated.

Thanks,

You have a couple of options:

1 - Can you add Total Salary as part of your dataset and return that for each Employee/Year ?
If so, then you just include that field in your cumulative statement, so no need for the column

2 - You may be able to take both Tot Salary and Cumulative salary and place them in a single cell using the cells in cells technique. Select your cell and drop a Rectangle control into it. You’ll notice the background change from solid white to the transparent grid pattern. Next, select a textbox control and drop this into the rectangle. You have to get it perfect and sometimes it’s a bit annoying when you don’t land exactly on the control. Next, drop in another textbox so it rests directly beside your first one.
In one cell add your Sal Tot, and in the right textbox the Cumulative Salary. Set the Sal Tot textbox to hidden=True and as small width as possible. To the end user it will appear as though this is a single column.

Hide a Static Row/Column in a Matrix

How can I hide a static row/column in a matrix? The static row/column should
only display depending on a parameter.
thanks,The column should have a visibility property, you can put an expression in
there I think.
"Jiabin Xie" wrote:
> How can I hide a static row/column in a matrix? The static row/column should
> only display depending on a parameter.
> thanks,
>|||On Feb 28, 8:40 am, adolf garlic
<adolfgar...@.discussions.microsoft.com> wrote:
> The column should have a visibility property, you can put an expression in
> there I think.
>
>
> "Jiabin Xie" wrote:
> > How can I hide a static row/column in a matrix? The static row/column should
> > only display depending on a parameter.
> > thanks,
Here is an example. Select a cell in the matrix in Layout view, open
the 'Properties' window and in Appearance -> Visibility -> Hidden,
select '<Expression...>' from the drop down menu and enter something
like:
=iif(Parameters!SomeParameterName.Value = "TargetValue", true, false)
Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Hide a Row or Column in Detail but show in Subtotal

It there a way I can hide a ROW in detail but show subtotal.
Example
c1 c2 c3 c4 Total
Project Task1 Actual 8 7 6 4 25
Schedule 8 8 8 8 32
Task2 Actual 2 2 2 2 8
Schedule 3 3 3 3 12
Total Project Actual 10 9 8 6 33
Schedule 11 11 11 11 44
Difference -1 -2 -3 -5 -11
Difference is the Row not in Task Group but I want difference to show in
subtotal. Is there a way to hide whole row on detial but show in Subtotal. I
tried Inscope. I can get blank row in detail and difference in subtotal. But
it does takes whole row space for each task. I am trying to hide the ROW
somehow to minimize the space.
c1 c2 c3 c4 Total
Project Task1 Actual 8 7 6 4 25
Schedule 8 8 8 8 32
Task2 Actual 2 2 2 2 8
Schedule 3 3 3 3 12
Total Project Actual 10 9 8 6 33
Schedule 11 11 11 11 44
Difference -1 -2 -3 -5 -11
Thanks for response
--
Gaurav Wason
gwason@.gmail.com
MCP - Project Server
Project Made Easy
Project Archive Tool
Project Owner Tool
http://projectmadeeasy.comOn Mar 19, 9:40 pm, Gaurav Wason <gwa...@.gmail.com> wrote:
> It there a way I can hide a ROW in detail but show subtotal.
> Example
> c1 c2 c3 c4 Total
> Project Task1 Actual 8 7 6 4 25
> Schedule 8 8 8 8 32
> Task2 Actual 2 2 2 2 8
> Schedule 3 3 3 3 12
> Total Project Actual 10 9 8 6 33
> Schedule 11 11 11 11 44
> Difference -1 -2 -3 -5 -11
> Difference is the Row not in Task Group but I want difference to show in
> subtotal. Is there a way to hide whole row on detial but show in Subtotal. I
> tried Inscope. I can get blank row in detail and difference in subtotal. But
> it does takes whole row space for each task. I am trying to hide the ROW
> somehow to minimize the space.
> c1 c2 c3 c4 Total
> Project Task1 Actual 8 7 6 4 25
> Schedule 8 8 8 8 32
> Task2 Actual 2 2 2 2 8
> Schedule 3 3 3 3 12
> Total Project Actual 10 9 8 6 33
> Schedule 11 11 11 11 44
> Difference -1 -2 -3 -5 -11
> Thanks for response
> --
> Gaurav Wason
> gwa...@.gmail.com
> MCP - Project Server
> Project Made Easy
> Project Archive Tool
> Project Owner Toolhttp://projectmadeeasy.com
As far as I know there is not a way around this: particularly, if you
are doing the Difference as part of the table. You might, however, try
doing the difference either in the stored procedure or query that is
sourcing the report and include it in a separate textbox below the
table -or- use a separate text box below the table and include an
expression there that includes an aggregate with a reference to the
dataset (or reference to a separate dataset that determines the
difference). Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

hide a column in a matrix

How can I hide a column in a matrix? I set the visibility of the column that
I want hidden to True but it still reserves that space in the report. I want
the hidden column to take up no space.
StephanieOn Sep 25, 10:12 am, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> How can I hide a column in a matrix? I set the visibility of the column that
> I want hidden to True but it still reserves that space in the report. I want
> the hidden column to take up no space.
> Stephanie
The best way to control this is to restrict the extra column in the
stored procedure/query that is sourcing the report: via a cursor or
while loop. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks for the suggestion. Modifying the query is not the way I want to go.
It is a very complex dynamic query with multiple parameters passed in from
the calling report.
I came up with a much easier solution. I have 2 matrixes in the report.
They are both in rectangles. The rectangles are hidden or visible based on a
passed parameter. It works wonderfully.
Stephanie
"EMartinez" wrote:
> On Sep 25, 10:12 am, Stephanie <Stepha...@.discussions.microsoft.com>
> wrote:
> > How can I hide a column in a matrix? I set the visibility of the column that
> > I want hidden to True but it still reserves that space in the report. I want
> > the hidden column to take up no space.
> >
> > Stephanie
>
> The best way to control this is to restrict the extra column in the
> stored procedure/query that is sourcing the report: via a cursor or
> while loop. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Hide a column (value) in a subtotal of a matrix?

Hello,
I am trying to hide (or possible show a calculated value in a subtotal)
a value in a matrix. My dataset returns something in this format.
RowHeader1, RowHeader2, ColumnName, ColumnType, Amount
1, 1, Total, Amount, 100
1, 1, Total2, Amount, 0
1, 1, Variance, Percent, 1.00
1, 2, Total, Amount, 50
1, 2, Total2, Amount, 55
1, 2, Variance, Percent, .10
I have row groups on RowHeader1 and RowHeader2. Also, I have a Column
Group on ColumnName and I have SUM(Fields!Amount.Value) in the Data
cell. The Matrix looks something like this:
Total Total2 Variance
1 1 100 0 100%
2 50 55 10%
TOTAL 150 55 110%
What I would like to do is either have the correct value in the
SubTotal field for the Variance (which I don't think is possible) or
just hide it. I tried to use InScope() as a start but I have been
getting nowhere.
Any help would be greatly appreciated.
Thanks,
AbeIs there a way to know if you are in the Subtotal Row or not?
Thanks|||Apparently you looked already at the InScope function. If you have multiple
row/column groupings you need to make sure that your conditional expression
considers all cases. E.g.
=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In
Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of
ColumnGroup1", "In Subtotal of entire matrix"))
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Abe" <abe@.flonet.com> wrote in message
news:1106156667.460735.143790@.c13g2000cwb.googlegroups.com...
> Is there a way to know if you are in the Subtotal Row or not?
> Thanks
>|||Thanks a lot for your help, I really appreciate it.

Hide a column

Hi there,sometimes I need to hide a column in my crystalReportViewer and compress the other columns,can I do thisHi,

U can hide a column. To do this, select the field u want to suppress in the "Detail Section"

Right Click and select the "Format Field" Option > Go to "Common Tab" > Check the "Suppress" box >Click OK|||but in this way it's place still blank I need that the column behind it become to it's place...So if I Have Col1 Col2 Col3 Col4 and I've hide Col2 I want thet the report become Col1 Col3 Col4|||2 ways of doing it...

1. Dont place COL2 in the report

2. Suppress Col2, then move Col3 to Col2's position . Thus u can have only Col1, Col3 and Col4 displayed in yr report.

If you still require assistance, pls forward the report to me and I shall try to assist you.|||I will explain : I have 4 columns Amiunt,Amount in a Base Currency,Amount in a First Currency,Amount in aecond Currency,in run time the user can choose the columns to display in the report,he can choose not to display Amount in a First Currency so I need to desappear this column from the report in run time...thx a lot....

Hidding column in query

Here is my code.

SELECT [Owner].[First Name], [Owner].[Last Name], [Condo].[Unit Number],
[Condo].[Weekly Rate], [Condo].[Linens]

FROM [Owner], [Condo]

WHERE [Condo].[Linens]=True

AND [Owner].[Owner ID]=[Condo].[Owner ID]

ORDER BY [Condo].[Unit Number];

I am trying to not display "Linens" column in my query result.Any idea how
can i achive that. Thanks in advanceW. Adam (wogla@.sbcglobal.net) writes:
> Here is my code.
> SELECT [Owner].[First Name], [Owner].[Last Name], [Condo].[Unit Number],
> [Condo].[Weekly Rate], [Condo].[Linens]
> FROM [Owner], [Condo]
> WHERE [Condo].[Linens]=True
> AND [Owner].[Owner ID]=[Condo].[Owner ID]
> ORDER BY [Condo].[Unit Number];
> I am trying to not display "Linens" column in my query result.Any idea how
> can i achive that. Thanks in advance

Well, just don't display it!

Seriously, this is likely to be a GUI issue, and you should probably
ask in a forum devoted to the GUI tool you are using.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"W. Adam" <wogla@.sbcglobal.net> wrote in message
news:tL5Cb.11236$aw2.5741825@.newssrv26.news.prodig y.com...
> Here is my code.
> SELECT [Owner].[First Name], [Owner].[Last Name], [Condo].[Unit Number],
> [Condo].[Weekly Rate], [Condo].[Linens]
> FROM [Owner], [Condo]
> WHERE [Condo].[Linens]=True
> AND [Owner].[Owner ID]=[Condo].[Owner ID]
> ORDER BY [Condo].[Unit Number];
>
>
> I am trying to not display "Linens" column in my query result.Any idea how
> can i achive that. Thanks in advance

There are two answers to this question. The first being, just don't select
the field

SELECT [Owner].[First Name], [Owner].[Last Name], [Condo].[Unit Number],
[Condo].[Weekly Rate]
FROM [Owner], [Condo]
WHERE [Condo].[Linens]=True
AND [Owner].[Owner ID]=[Condo].[Owner ID]
ORDER BY [Condo].[Unit Number];

- or -

If you need the linens value but don't want to display it, handle this in
business layer (class, dll, module, mts, etc.), or your presentation layer
(application, web page, etc.).

BV.
www.iheartmypond.com

Hidden columns showing on export

I am allowing users to specify through a parameter whether they want a column
to be visible or not. If they specify visible = false, the column does not
show on the report. However, when that same report is exported, the column is
visible in the export.
Does anyone have a solution for this?Hello.
I am having a similar problem. I'm trying to make some table columns hidden
(collapsed). The regular online report view is fine and the hidden columns
are hidden and propery collapsed. However, when I export (to pdf) the hidden
columns are blank but not collapsed, except for the table footer row, so the
table footer is misaligned with the rest of the table. The columns in the
table footer row collapsed propery, but the rest of the table's hidden
columns are blank but did not collapse. So I end up with a table with more
header and detail columns than footer columns.
Any idea what is happening?
Thanks,
Brian.
"khonerka" wrote:
> I am allowing users to specify through a parameter whether they want a column
> to be visible or not. If they specify visible = false, the column does not
> show on the report. However, when that same report is exported, the column is
> visible in the export.
> Does anyone have a solution for this?

HELP: Updating column with data from another column

I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
John
update DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
Russel Loski, MCSD.Net
|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:

> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textstring2%'
> --
> Russel Loski, MCSD.Net
>
|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
Russel Loski, MCSD.Net
"John Steen" wrote:

> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could I
> be referring to the database incorrectly?
> Thanks,
> John
>
|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:

> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
>

HELP: Updating column with data from another column

I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
Johnupdate DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textstring2%'
--
Russel Loski, MCSD.Net|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:
> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textstring2%'
> --
> Russel Loski, MCSD.Net
>|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
--
Russel Loski, MCSD.Net
"John Steen" wrote:
> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could I
> be referring to the database incorrectly?
> Thanks,
> John
>|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:
> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
> > Thanks for the quick reply, Russell! That makes better sense. I ran the
> > query and am now getting this error:
> >
> > Server: Msg 7202, Level 11, State 2, Line 2
> > Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
> > the server to sysservers.
> >
> > Since DB2 is a database on the same server as DB1, and not a server, could I
> > be referring to the database incorrectly?
> >
> > Thanks,
> > John
> >
>

HELP: Updating column with data from another column

I post a message about this several months ago, but I may have misstated the
problem. So here goes again. My apologies for the duplicate.
I have two databases, each with identical schema. I need to update about
1,000 columns in DB1.table1, which are currently null, with data from the
same columns in DB2.table1. The tables essentially contain the same data,
except for the columns I need to update.
I'm using a unique ID column as the key. E.g., the value of the ID column
in DB1.table1.row1 is identical to the value of the ID in DB2.table1.row1.
I'm not the strongest at writing queries, but I tried using this query:
update DB1.dbo.table1
set DB1.dbo.table1.textcol = Db2.dbo.table1.textcol
where
(select IDcol from DB2.dbo.table1
where IDcol in (select IDcol from DB1.dbo.table1)
and DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textst
ring2%'
It errored out with this:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'textstring2%'.
It seems like a simple thing to do. Can anyone shed some light on what I'm
doing wrong?
Thanks!
Johnupdate DB1.dbo.table1
set textcol = Db2.dbo.table1.textcol
from DB1.dbo.table1
inner join
DB2.dbo.table1
on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
where DB2.dbo.table1.textcol like
'%textstring1'+char(13)+char(10)+'textst
ring2%'
Russel Loski, MCSD.Net|||Thanks for the quick reply, Russell! That makes better sense. I ran the
query and am now getting this error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to add
the server to sysservers.
Since DB2 is a database on the same server as DB1, and not a server, could I
be referring to the database incorrectly?
Thanks,
John
"RLoski" wrote:

> update DB1.dbo.table1
> set textcol = Db2.dbo.table1.textcol
> from DB1.dbo.table1
> inner join
> DB2.dbo.table1
> on DB1.dbo.table1.IDCol = DB2.dbo.table1.IDCol
> where DB2.dbo.table1.textcol like
> '%textstring1'+char(13)+char(10)+'textst
ring2%'
> --
> Russel Loski, MCSD.Net
>|||That sounds like you have two periods together or you have a four part
(db.owner.table.column) where a table is expected (which would be
interpretted as server.db.owner.table).
--
Russel Loski, MCSD.Net
"John Steen" wrote:

> Thanks for the quick reply, Russell! That makes better sense. I ran the
> query and am now getting this error:
> Server: Msg 7202, Level 11, State 2, Line 2
> Could not find server 'DB2' in sysservers. Execute sp_addlinkedserver to a
dd
> the server to sysservers.
> Since DB2 is a database on the same server as DB1, and not a server, could
I
> be referring to the database incorrectly?
> Thanks,
> John
>|||Thanks, Again, Russell. I'll check it out.
John
"RLoski" wrote:

> That sounds like you have two periods together or you have a four part
> (db.owner.table.column) where a table is expected (which would be
> interpretted as server.db.owner.table).
> --
> Russel Loski, MCSD.Net
>
> "John Steen" wrote:
>
>

HELP: sp_help and object browser report view column sizes differently

Hi,

I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
SQL Query Analyzer's object browser to view the columns returned by a view,
I find that sp_help is reporting stale information.

In a recent schema change, for example, someone lengthened a varchar column
from 15 to 50 characters. If we use sp_help to find out about a view that
depends upon this column, it still shows up as VARCHAR(15), whereas the
object browser correctly reports it as VARCHAR(50).

Dropping and recreating the view fixes the problem, but we have quite a few
views, and dropping and re-creating all of them any time a schema change is
made is something we want to avoid. I tried using DBCC CHECKDB in hopes that
it would 'refresh' SQL Server's information, but no luck.

(if you're curious as to why I don't just use the object browser instead,
read boring technical details below)

Has anyone seen this before? Is there some other way (other than
re-creating every view) to tell SQL Server to "refresh" it's information?

Thanks!

-Scott

-------
Boring Technical Information:

The reason this is an issue for us (i.e., I can't just use the object
browser instead) is that our object model classes are built using standard
metadata query methods in Java that seem to be returning the same stale
information that sp_help is returning. These methods are a part of the
standard JDK, so we can't easily fiddle with them. Anyway, as a result, our
object model (at least with respect to views) may not match our current
schema!A view need to expose its columns and each columns datatypes in the system
tables, just like a table. However, in SQL Server, this information is not
refreshed when you modify an underlying object (like ALTER TABLE). This is
why sp_help will show you the old information, it picks it up from
syscolumns. Repro below:
USE tempdb
GO
DROP VIEW v
GO
DROP TABLE t
GO
CREATE TABLE t(c1 varchar(10))
GO
CREATE VIEW v AS SELECT c1 FROM t
GO
EXEC sp_help v
GO
ALTER TABLE t ALTER COLUMN c1 VARCHAR(20)
GO
EXEC sp_help v -- Here, the info is still old
EXEC sp_refreshview v
EXEC sp_help v

Note that you can use sp_refreshview to refresh the view definition.

QA's object browser doesn't pick up the meta-data from syscolumns, that is
why it can show current information. Here's what QA seems to be doing to
pick up the meta-data info:

declare @.P1 int
set @.P1=1
exec sp_prepare @.P1 output, NULL, N'SELECT * FROM [tempdb].[dbo].[v]', 1
select @.P1
exec sp_unprepare 1
--
Tibor Karaszi

"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message
news:OOWdnchUCvygmzWiRTvUrg@.texas.net...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a
view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar
column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a
few
> views, and dropping and re-creating all of them any time a schema change
is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes
that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result,
our
> object model (at least with respect to views) may not match our current
> schema!|||Found it:

sp_refreshview - Refreshes the metadata for the specified view. Persistent
metadata for a view can become outdated because of changes to the underlying
objects upon which the view depends.

"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message
news:OOWdnchUCvygmzWiRTvUrg@.texas.net...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a
view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar
column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a
few
> views, and dropping and re-creating all of them any time a schema change
is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes
that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result,
our
> object model (at least with respect to views) may not match our current
> schema!|||run to refresh the view when the metadata is outdated...

exec sp_refreshview 'viewname'

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message
news:OOWdnchUCvygmzWiRTvUrg@.texas.net...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a
view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar
column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a
few
> views, and dropping and re-creating all of them any time a schema change
is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes
that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result,
our
> object model (at least with respect to views) may not match our current
> schema!|||"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message news:<OOWdnchUCvygmzWiRTvUrg@.texas.net>...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a few
> views, and dropping and re-creating all of them any time a schema change is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result, our
> object model (at least with respect to views) may not match our current
> schema!

See sp_refreshview in Books Online, which is intended for exactly this situation.

Simon|||Hi

Try looking at sp_refreshview. Previous posts have described ways to
do this for all tables if you need to write a procedure.

John

"ScottyBaby" <scottseansmith2NO@.SPAM.hotmail.com> wrote in message news:<OOWdnchUCvygmzWiRTvUrg@.texas.net>...
> Hi,
> I've run into a curious problem with MS SQL Server 8.0. Using sp_help and
> SQL Query Analyzer's object browser to view the columns returned by a view,
> I find that sp_help is reporting stale information.
> In a recent schema change, for example, someone lengthened a varchar column
> from 15 to 50 characters. If we use sp_help to find out about a view that
> depends upon this column, it still shows up as VARCHAR(15), whereas the
> object browser correctly reports it as VARCHAR(50).
> Dropping and recreating the view fixes the problem, but we have quite a few
> views, and dropping and re-creating all of them any time a schema change is
> made is something we want to avoid. I tried using DBCC CHECKDB in hopes that
> it would 'refresh' SQL Server's information, but no luck.
> (if you're curious as to why I don't just use the object browser instead,
> read boring technical details below)
> Has anyone seen this before? Is there some other way (other than
> re-creating every view) to tell SQL Server to "refresh" it's information?
> Thanks!
> -Scott
> -------
> Boring Technical Information:
> The reason this is an issue for us (i.e., I can't just use the object
> browser instead) is that our object model classes are built using standard
> metadata query methods in Java that seem to be returning the same stale
> information that sp_help is returning. These methods are a part of the
> standard JDK, so we can't easily fiddle with them. Anyway, as a result, our
> object model (at least with respect to views) may not match our current
> schema!

HELP: MFC ODBC SQL Server problems

I got a problem where in SQL Server a table has a column of type REAL & size
(precision) 4. It's meant to store double types for C++ variable values.
In SQL Server, the value 1E+10 translates to 10000000000 in C++ fine, via
MFC's CRecordset class (DBCORE.CPP). However any numbers above this value
translates to wrong numbers. Such as:
1.1E+10 => 10000000512
1.00001E+10 => 10000001024
1.01E+10 => 10099999744
1.0000001E+10 => 10000100352
Strange huh? Has anybody experienced this before? Does anyone know what is
wrong? I tried upgrading my SQL Server 2000 to SP4 and even upgraded MAC2.6
to SP2. Still no success.You should not use "Real" to handle a big number like "1E+10" since real
data type does not have enough precisions to do the job. If you change
the data type to "Float", you will have better chance to get the number
right. Or if you want to store "Exact" numbers, you should use "decimal"
as your data type.
Charles Zhang
http://www.speedydb.com
SpeedyDB ADO.NET is the fastest, most secure, and most flexible ADO.NET
Provider over Wide Area Netword (WAN).
Andrew Wan wrote:
> I got a problem where in SQL Server a table has a column of type REAL & si
ze
> (precision) 4. It's meant to store double types for C++ variable values.
> In SQL Server, the value 1E+10 translates to 10000000000 in C++ fine, via
> MFC's CRecordset class (DBCORE.CPP). However any numbers above this value
> translates to wrong numbers. Such as:
> 1.1E+10 => 10000000512
> 1.00001E+10 => 10000001024
> 1.01E+10 => 10099999744
> 1.0000001E+10 => 10000100352
> Strange huh? Has anybody experienced this before? Does anyone know what is
> wrong? I tried upgrading my SQL Server 2000 to SP4 and even upgraded MAC2.
6
> to SP2. Still no success.
>
>