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?
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Wednesday, March 28, 2012
Hide Columns in Matrix Report
Hi,
I have a matrxi report with 6 Columns (Col 1, 2, ...6). Based on the parameters I select I wanted to hide 3 of the columns (Col 2, 4 and 6. But the problem is when I hide the columns, it leaves a gap. That means, the matrix report shows Col 1, 3 and 5 with gap in between the Columns.
There is no Column Visibility property in matrix report.
Please help me as I am looking to solve this issue for a long time now.
Thanks,
S Suresh
I have not been able to solve this issue either. I am not sure if it's a design intent or a bug, but it is frustrating.
On one report i actually had to put multiple matrix side by side, using the same dataset, grouping, etc, and hid the entire matrix. It's messy, but it does the trick.
BobP
|||Having 6 columns in your matrix, suggest they map on to 6 columns in your source query and you have a static group.
If your columns have no further nesting then I suggest switching to using a table as you can achieve equivalent results. When you highlight a column in a table and change it's visibility you don't get the gap.
If that is not possible then you can get around this by pivoting the data in your query so that rather than having 6 measure columns you get 2 columns with MeasureType and MeasureValue. You should then be able to group the matrix columns by MeasureType, then, based on your parameter selection, filter the 3 unwanted measures in the source query.
And that will definately work.
|||Thanks Adam. But my 6 columns are populated with values from dimensions. Can you tell me if there is any solution to hide column in matrix?
Thanks,
S Suresh
|||Thanks Bob. I already messed up with multiple matrix to fill the rows.
Thanks,
S Suresh
|||No, you cannot hide a column in a matrix and have it reclaim it's space.
BobP
|||Suresh,
What I suggested is possible as I've already done it. When you say "my columns are populated from dimensions" what do you actually mean?
On the Layout tab do you see 1 column or 6 i.e. is this a dynamic group or a static group?
It doesn't matter if they are coming from different dimesions, you can just manufacture calculated members and measures within your MDX query. This requires you to stop using the query designer and switch to custom MDX view. Then you use syntax such as:
Code Snippet
WITH
MEMBER [Measures].[MyMeasure] AS 'SOME MDX here'
MEMBER [SomeOtherDimension].[MyMember] AS 'Some MDX here'
SELECT {[Measures].[MyMeasure]} ON 0
FROM [YourCube]
WHERE (...)
Post some sample MDX with a brief explanation of the dimensions and the parameters you wish to use to control visibility of the columns and I'll show you what I mean.
Hide Columns
Hi,
I have created a report that contains a table. The table returns data w/
multiple statuses. I have created a group based on the status and have a
page break at the end of the group (so each page will display only one status
type). I would like to dynamically show / hide columns based on the status.
Is this possible? I tried setting the visibility of the individual cells,
however, this leaves a gap where the cells were (which is not desirable)â?¦
Any thoughts?
Thanks!Try setting the visibility property on the column instead of the
individual cells.
On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> Hi,
> I have created a report that contains a table. The table returns data w/
> multiple statuses. I have created a group based on the status and have a
> page break at the end of the group (so each page will display only one status
> type). I would like to dynamically show / hide columns based on the status.
> Is this possible? I tried setting the visibility of the individual cells,
> however, this leaves a gap where the cells were (which is not desirable)...
> Any thoughts?
> Thanks!|||Hi Jen,
Can this be done with a Cross-Tab Report? I dont see the Visibility Property
of the column group :( .
--
With Thanks and Regards,
Raja Krishnamurthy
"Jen" wrote:
> Try setting the visibility property on the column instead of the
> individual cells.
> On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have created a report that contains a table. The table returns data w/
> > multiple statuses. I have created a group based on the status and have a
> > page break at the end of the group (so each page will display only one status
> > type). I would like to dynamically show / hide columns based on the status.
> > Is this possible? I tried setting the visibility of the individual cells,
> > however, this leaves a gap where the cells were (which is not desirable)...
> >
> > Any thoughts?
> >
> > Thanks!
>
>
I have created a report that contains a table. The table returns data w/
multiple statuses. I have created a group based on the status and have a
page break at the end of the group (so each page will display only one status
type). I would like to dynamically show / hide columns based on the status.
Is this possible? I tried setting the visibility of the individual cells,
however, this leaves a gap where the cells were (which is not desirable)â?¦
Any thoughts?
Thanks!Try setting the visibility property on the column instead of the
individual cells.
On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> Hi,
> I have created a report that contains a table. The table returns data w/
> multiple statuses. I have created a group based on the status and have a
> page break at the end of the group (so each page will display only one status
> type). I would like to dynamically show / hide columns based on the status.
> Is this possible? I tried setting the visibility of the individual cells,
> however, this leaves a gap where the cells were (which is not desirable)...
> Any thoughts?
> Thanks!|||Hi Jen,
Can this be done with a Cross-Tab Report? I dont see the Visibility Property
of the column group :( .
--
With Thanks and Regards,
Raja Krishnamurthy
"Jen" wrote:
> Try setting the visibility property on the column instead of the
> individual cells.
> On Sep 5, 4:18 pm, David <Da...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > I have created a report that contains a table. The table returns data w/
> > multiple statuses. I have created a group based on the status and have a
> > page break at the end of the group (so each page will display only one status
> > type). I would like to dynamically show / hide columns based on the status.
> > Is this possible? I tried setting the visibility of the individual cells,
> > however, this leaves a gap where the cells were (which is not desirable)...
> >
> > Any thoughts?
> >
> > Thanks!
>
>
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.
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.
Monday, March 26, 2012
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....
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....
Friday, March 23, 2012
Hidden max number of columns in Management Studio?
Hi,
The following is observed in the SQLServer 2005 Management Studio Visual
Editor:
Open table (Right Click option on a table) fails when I try it on a table
with lots of columns in it > 820 columns! Currently the table has no rows
(empty table). The same table opens fine with sql 2000 enterprise manager.
This table has been migrated/imported from sql 2000 without any errors! Other
imported tables with less number of columns can be opened in the visual
editor!
Wondering is there a hidden limit in terms of number of columns with
SQLserver 2005 Management Studio (Grid) Visual Editor?
I get SQL Executaion error
Error Source: Microsoft.VisualStudio.DataTools
Exception has been thrown by the target of an invocation
Is this a known behavior/limitation/bug? Is there a workaround?
Thanks
Kan,
See
http://www.sql-server-performance.co...ID=9016鵬
You may wish to report this as a bug at the suggested URL
http://lab.msdn.microsoft.com/produc...k/default.aspx
(also in the thread).
Steve Kass
Drew University
Kan wrote:
>Hi,
>The following is observed in the SQLServer 2005 Management Studio Visual
>Editor:
>Open table (Right Click option on a table) fails when I try it on a table
>with lots of columns in it > 820 columns! Currently the table has no rows
>(empty table). The same table opens fine with sql 2000 enterprise manager.
>This table has been migrated/imported from sql 2000 without any errors! Other
>imported tables with less number of columns can be opened in the visual
>editor!
>Wondering is there a hidden limit in terms of number of columns with
>SQLserver 2005 Management Studio (Grid) Visual Editor?
>I get SQL Executaion error
>Error Source: Microsoft.VisualStudio.DataTools
>Exception has been thrown by the target of an invocation
>Is this a known behavior/limitation/bug? Is there a workaround?
>Thanks
>
>
The following is observed in the SQLServer 2005 Management Studio Visual
Editor:
Open table (Right Click option on a table) fails when I try it on a table
with lots of columns in it > 820 columns! Currently the table has no rows
(empty table). The same table opens fine with sql 2000 enterprise manager.
This table has been migrated/imported from sql 2000 without any errors! Other
imported tables with less number of columns can be opened in the visual
editor!
Wondering is there a hidden limit in terms of number of columns with
SQLserver 2005 Management Studio (Grid) Visual Editor?
I get SQL Executaion error
Error Source: Microsoft.VisualStudio.DataTools
Exception has been thrown by the target of an invocation
Is this a known behavior/limitation/bug? Is there a workaround?
Thanks
Kan,
See
http://www.sql-server-performance.co...ID=9016鵬
You may wish to report this as a bug at the suggested URL
http://lab.msdn.microsoft.com/produc...k/default.aspx
(also in the thread).
Steve Kass
Drew University
Kan wrote:
>Hi,
>The following is observed in the SQLServer 2005 Management Studio Visual
>Editor:
>Open table (Right Click option on a table) fails when I try it on a table
>with lots of columns in it > 820 columns! Currently the table has no rows
>(empty table). The same table opens fine with sql 2000 enterprise manager.
>This table has been migrated/imported from sql 2000 without any errors! Other
>imported tables with less number of columns can be opened in the visual
>editor!
>Wondering is there a hidden limit in terms of number of columns with
>SQLserver 2005 Management Studio (Grid) Visual Editor?
>I get SQL Executaion error
>Error Source: Microsoft.VisualStudio.DataTools
>Exception has been thrown by the target of an invocation
>Is this a known behavior/limitation/bug? Is there a workaround?
>Thanks
>
>
hidden fields rendering on server only!
I have a report with a matrix in which I conditionally hide one of the data
columns. This is working properly in preview on my development studio but
when I deploy to the report server it is showing these hidden data columns
when it is not supposed to - if I export the rendered report from the server
... it exports to pdf and excel with the data columns properly hidden - so
how can I fix this ... I cant understand what is happening. Please help as
release date is approaching. Thank youUpdate on this - I put the same conditional expressions on the border
property of the columns I want hidden and also the background color of the
column that gets conditionally colored - this seems to work although it
doesnt explain why it worked w/out that in dev studio AND why it exported
correctly - the only problem being on the server in html format. Still would
like to know why because if I had known that then I could have saved a lot of
time. Thanks
"MJT" wrote:
> I have a report with a matrix in which I conditionally hide one of the data
> columns. This is working properly in preview on my development studio but
> when I deploy to the report server it is showing these hidden data columns
> when it is not supposed to - if I export the rendered report from the server
> ... it exports to pdf and excel with the data columns properly hidden - so
> how can I fix this ... I cant understand what is happening. Please help as
> release date is approaching. Thank you
columns. This is working properly in preview on my development studio but
when I deploy to the report server it is showing these hidden data columns
when it is not supposed to - if I export the rendered report from the server
... it exports to pdf and excel with the data columns properly hidden - so
how can I fix this ... I cant understand what is happening. Please help as
release date is approaching. Thank youUpdate on this - I put the same conditional expressions on the border
property of the columns I want hidden and also the background color of the
column that gets conditionally colored - this seems to work although it
doesnt explain why it worked w/out that in dev studio AND why it exported
correctly - the only problem being on the server in html format. Still would
like to know why because if I had known that then I could have saved a lot of
time. Thanks
"MJT" wrote:
> I have a report with a matrix in which I conditionally hide one of the data
> columns. This is working properly in preview on my development studio but
> when I deploy to the report server it is showing these hidden data columns
> when it is not supposed to - if I export the rendered report from the server
> ... it exports to pdf and excel with the data columns properly hidden - so
> how can I fix this ... I cant understand what is happening. Please help as
> release date is approaching. Thank you
Hidden columns/blank PDF page(s)
I have been trying for some time to create a columnar report where certain columns can be hidden as requested by the user. I have tried placing the complete column set in the report, and then hiding a subset of columns, but blank pages are produced when rendered to PDF since the report body does not appear to shrink as columns are hidden and justify themselves to the left. I have tried a table, list, and matrix to no avail...
Does anyone know of a workaround, a trick, or even a hack that may accomplish what I am after?
Thanks.
Oh well, guess there is no way to do it. I have seen many other posts of people trying to do the same thing. I wish someone from Microsoft would just say it's impossible so I could forget about trying to come up with a workaround... Doesn't seem like the functionality I am after is asking too much. Actually, I pretty much consider the PDF rendering the blank space to extra pages to be a rendering bug... Seems the renderer could be capable of clipping blank space... My 0.02 rant.|||I'm having the same issue so if anyone, especially someone from MSFT, has any ideas I'd be very eager to hear them.|||Hello Iago,
I have found a work around for this problem. It’s a bit hacky, but does work.
1.Collapse all table columns to the smallest they can go so width = .125
2.Resize the DESIGN gird to the smallest it can go
3.Set the CanGrow property = FALSE for each cell that has text.This will suppress the vertical size of the text to the row height that you have specified
4.Create a new report in the same project.
5.Create a dummy dataset in the sub report.I usually use: SELECT NULL
6.Pull a table onto the grid and delete all but one cell
7. Set the cell = “”
8.Set the column width to the column width you had in the original report say .625
9.Return to your original report and create an additional header on the report
10. Drag a sub report into each column of the new header cell and set it equal to the new report
11. Run the report.Hidden columns now only take up the .125 space.
Hidden columns/blank PDF page(s)
I have been trying for some time to create a columnar report where certain columns can be hidden as requested by the user. I have tried placing the complete column set in the report, and then hiding a subset of columns, but blank pages are produced when rendered to PDF since the report body does not appear to shrink as columns are hidden and justify themselves to the left. I have tried a table, list, and matrix to no avail...
Does anyone know of a workaround, a trick, or even a hack that may accomplish what I am after?
Thanks.
Oh well, guess there is no way to do it. I have seen many other posts of people trying to do the same thing. I wish someone from Microsoft would just say it's impossible so I could forget about trying to come up with a workaround... Doesn't seem like the functionality I am after is asking too much. Actually, I pretty much consider the PDF rendering the blank space to extra pages to be a rendering bug... Seems the renderer could be capable of clipping blank space... My 0.02 rant.|||I'm having the same issue so if anyone, especially someone from MSFT, has any ideas I'd be very eager to hear them.|||Hello Iago,
I have found a work around for this problem. It’s a bit hacky, but does work.
1.Collapse all table columns to the smallest they can go so width = .125
2.Resize the DESIGN gird to the smallest it can go
3.Set the CanGrow property = FALSE for each cell that has text.This will suppress the vertical size of the text to the row height that you have specified
4.Create a new report in the same project.
5.Create a dummy dataset in the sub report.I usually use: SELECT NULL
6.Pull a table onto the grid and delete all but one cell
7. Set the cell = “”
8.Set the column width to the column width you had in the original report say .625
9.Return to your original report and create an additional header on the report
10. Drag a sub report into each column of the new header cell and set it equal to the new report
11. Run the report.Hidden columns now only take up the .125 space.
Hidden columns/blank PDF page(s)
I have been trying for some time to create a columnar report where certain columns can be hidden as requested by the user. I have tried placing the complete column set in the report, and then hiding a subset of columns, but blank pages are produced when rendered to PDF since the report body does not appear to shrink as columns are hidden and justify themselves to the left. I have tried a table, list, and matrix to no avail...
Does anyone know of a workaround, a trick, or even a hack that may accomplish what I am after?
Thanks.
Oh well, guess there is no way to do it. I have seen many other posts of people trying to do the same thing. I wish someone from Microsoft would just say it's impossible so I could forget about trying to come up with a workaround... Doesn't seem like the functionality I am after is asking too much. Actually, I pretty much consider the PDF rendering the blank space to extra pages to be a rendering bug... Seems the renderer could be capable of clipping blank space... My 0.02 rant.|||I'm having the same issue so if anyone, especially someone from MSFT, has any ideas I'd be very eager to hear them.|||Hello Iago,
I have found a work around for this problem. It’s a bit hacky, but does work.
1.Collapse all table columns to the smallest they can go so width = .125
2.Resize the DESIGN gird to the smallest it can go
3.Set the CanGrow property = FALSE for each cell that has text.This will suppress the vertical size of the text to the row height that you have specified
4.Create a new report in the same project.
5.Create a dummy dataset in the sub report.I usually use: SELECT NULL
6.Pull a table onto the grid and delete all but one cell
7. Set the cell = “”
8.Set the column width to the column width you had in the original report say .625
9.Return to your original report and create an additional header on the report
10. Drag a sub report into each column of the new header cell and set it equal to the new report
11. Run the report.Hidden columns now only take up the .125 space.
sqlHidden columns/blank PDF page(s)
I have been trying for some time to create a columnar report where certain columns can be hidden as requested by the user. I have tried placing the complete column set in the report, and then hiding a subset of columns, but blank pages are produced when rendered to PDF since the report body does not appear to shrink as columns are hidden and justify themselves to the left. I have tried a table, list, and matrix to no avail...
Does anyone know of a workaround, a trick, or even a hack that may accomplish what I am after?
Thanks.
Oh well, guess there is no way to do it. I have seen many other posts of people trying to do the same thing. I wish someone from Microsoft would just say it's impossible so I could forget about trying to come up with a workaround... Doesn't seem like the functionality I am after is asking too much. Actually, I pretty much consider the PDF rendering the blank space to extra pages to be a rendering bug... Seems the renderer could be capable of clipping blank space... My 0.02 rant.|||I'm having the same issue so if anyone, especially someone from MSFT, has any ideas I'd be very eager to hear them.|||Hello Iago,
I have found a work around for this problem. It’s a bit hacky, but does work.
1.Collapse all table columns to the smallest they can go so width = .125
2.Resize the DESIGN gird to the smallest it can go
3.Set the CanGrow property = FALSE for each cell that has text.This will suppress the vertical size of the text to the row height that you have specified
4.Create a new report in the same project.
5.Create a dummy dataset in the sub report.I usually use: SELECT NULL
6.Pull a table onto the grid and delete all but one cell
7. Set the cell = “”
8.Set the column width to the column width you had in the original report say .625
9.Return to your original report and create an additional header on the report
10. Drag a sub report into each column of the new header cell and set it equal to the new report
11. Run the report.Hidden columns now only take up the .125 space.
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?
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?
Hidden Columns Problem in Excel
Hi there,
Been so annoyed to get rid of all the hidden columns after exporting my report to Excel which when I merge and centre then unhide, shows blank columns. The hidden column could appear in the middle of report or at the end of each report.
Known from somewhere that this might cause by the below mentioned facts and i've got some of the solutions with me but may i know is there a guideline to follow when designing a report for Excel and meanwhile get rid of hidden column's problem ?
i. The width of each and every tables are not aligned when there are more than 2 tables presented in a page vertically (each one on top of another) and therefore, gotta make sure the location from the left (start location) for all tables are the same, and the total width of the table are equal to the sum of width of each column, and also the length of every single column in all tables is align
--SOMEHOW, this is not enough to fix the problem and so i have to try steps ii (as follows).
ii. The text width in a field is greater than the width of the field. (Need to keep on trying for various width until the hidden column gone) e.g. i had allocate 2cm for a 'int' type field and apparently, i could see from preview the value just occupied bout half of the field. However, i need to expand the field to 3cm at last to accomodate the value or hidden column may appear when i export to Excel.
--SO, is there a way to know how much space a field needs and is data type affect the column width needed?
iii. Matrixes' width is greater than page width after expanding (This is hard to measure because we do not know how many columns a matrix is going to expand), and the best i could do is to set it less than or equal to the lenght of (page width - left margin -right margin) but somehow the hidden column still appear
-- ANY better solution to deal with this?
Any comment is welcome and thank you in advance!
Cheers,
Julez
Here is some information about merged cells with the Excel renderer:
http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx
-Chris
|||
Hi Chris,
Thank you so much! This is really helpful!! =)
Cheers,
Julez
Hidden columns exporting in CSV
I am writing a report to export columns dynamically. The column's Hidden
property is controlled by an expression (=Cbool(parameters.hidecol.value))
which works ok in Excel and PDF export. However, whenever I export in CSV,
this property is ignored and the column is never exported.
Is this a bug in reporting service or is it by design?
I have SQL reporting service 2005 with SP1 and all the hotfixes.Imran,
I got a reply from a MS contact on msdn. Here is his reply for anyone
else with the same problem...
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!
----
- DenisL (SQL RS Team)
Imran wrote:
> I am writing a report to export columns dynamically. The column's Hidden
> property is controlled by an expression (=Cbool(parameters.hidecol.value))
> which works ok in Excel and PDF export. However, whenever I export in CSV,
> this property is ignored and the column is never exported.
> Is this a bug in reporting service or is it by design?
> I have SQL reporting service 2005 with SP1 and all the hotfixes.|||Actually I was expecting a solution rather than a refresher on "status quo"
from Microsoft. Ofcourse I am aware of this issue and there are several
codable workaround for this... Was curious if someone had already done this
or if another hotfix is on its way to resolve this.
Imran
"johnburns007@.gmail.com" wrote:
> Imran,
> I got a reply from a MS contact on msdn. Here is his reply for anyone
> else with the same problem...
> 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!
>
> ----
> - DenisL (SQL RS Team)
>
> Imran wrote:
> > I am writing a report to export columns dynamically. The column's Hidden
> > property is controlled by an expression (=Cbool(parameters.hidecol.value))
> > which works ok in Excel and PDF export. However, whenever I export in CSV,
> > this property is ignored and the column is never exported.
> >
> > Is this a bug in reporting service or is it by design?
> >
> > I have SQL reporting service 2005 with SP1 and all the hotfixes.
>
property is controlled by an expression (=Cbool(parameters.hidecol.value))
which works ok in Excel and PDF export. However, whenever I export in CSV,
this property is ignored and the column is never exported.
Is this a bug in reporting service or is it by design?
I have SQL reporting service 2005 with SP1 and all the hotfixes.Imran,
I got a reply from a MS contact on msdn. Here is his reply for anyone
else with the same problem...
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!
----
- DenisL (SQL RS Team)
Imran wrote:
> I am writing a report to export columns dynamically. The column's Hidden
> property is controlled by an expression (=Cbool(parameters.hidecol.value))
> which works ok in Excel and PDF export. However, whenever I export in CSV,
> this property is ignored and the column is never exported.
> Is this a bug in reporting service or is it by design?
> I have SQL reporting service 2005 with SP1 and all the hotfixes.|||Actually I was expecting a solution rather than a refresher on "status quo"
from Microsoft. Ofcourse I am aware of this issue and there are several
codable workaround for this... Was curious if someone had already done this
or if another hotfix is on its way to resolve this.
Imran
"johnburns007@.gmail.com" wrote:
> Imran,
> I got a reply from a MS contact on msdn. Here is his reply for anyone
> else with the same problem...
> 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!
>
> ----
> - DenisL (SQL RS Team)
>
> Imran wrote:
> > I am writing a report to export columns dynamically. The column's Hidden
> > property is controlled by an expression (=Cbool(parameters.hidecol.value))
> > which works ok in Excel and PDF export. However, whenever I export in CSV,
> > this property is ignored and the column is never exported.
> >
> > Is this a bug in reporting service or is it by design?
> >
> > I have SQL reporting service 2005 with SP1 and all the hotfixes.
>
Wednesday, March 21, 2012
Hi enterprise manager prob.
Hi. I got Enterprise Manager to work finally.
However, the Columns are backwards when I retrieve all rows, and when i go
to design a table.
Meaning to say that the ID starts on right side of screen, and scrollbar is
on left side (everything is backwards)
in options of enterprise manager, it says english is default language.
thanksThis sounds more like a Windows setting to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Michael" <Michael@.nospamplease.com> wrote in message
news:OEpXDJqCEHA.2592@.TK2MSFTNGP12.phx.gbl...
> Hi. I got Enterprise Manager to work finally.
> However, the Columns are backwards when I retrieve all rows, and when i go
> to design a table.
> Meaning to say that the ID starts on right side of screen, and scrollbar
is
> on left side (everything is backwards)
> in options of enterprise manager, it says english is default language.
> thanks
>
>|||Hi Tibor:
I had it working with enterprise manager 7.0. When I upgraded to 8.0 it's
like that. every other application on my machine is left to right -> only
that is right to left. Everything is English on my machine. Very weird.
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ertxVMqCEHA.1588@.tk2msftngp13.phx.gbl...
> This sounds more like a Windows setting to me...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Michael" <Michael@.nospamplease.com> wrote in message
> news:OEpXDJqCEHA.2592@.TK2MSFTNGP12.phx.gbl...
go
> is
>|||Got it to work - It was Windows Settings, thanks Tibor!!!
"Michael" <Michael@.nospamplease.com> wrote in message
news:OEpXDJqCEHA.2592@.TK2MSFTNGP12.phx.gbl...
> Hi. I got Enterprise Manager to work finally.
> However, the Columns are backwards when I retrieve all rows, and when i go
> to design a table.
> Meaning to say that the ID starts on right side of screen, and scrollbar
is
> on left side (everything is backwards)
> in options of enterprise manager, it says english is default language.
> thanks
>
>
However, the Columns are backwards when I retrieve all rows, and when i go
to design a table.
Meaning to say that the ID starts on right side of screen, and scrollbar is
on left side (everything is backwards)
in options of enterprise manager, it says english is default language.
thanksThis sounds more like a Windows setting to me...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Michael" <Michael@.nospamplease.com> wrote in message
news:OEpXDJqCEHA.2592@.TK2MSFTNGP12.phx.gbl...
> Hi. I got Enterprise Manager to work finally.
> However, the Columns are backwards when I retrieve all rows, and when i go
> to design a table.
> Meaning to say that the ID starts on right side of screen, and scrollbar
is
> on left side (everything is backwards)
> in options of enterprise manager, it says english is default language.
> thanks
>
>|||Hi Tibor:
I had it working with enterprise manager 7.0. When I upgraded to 8.0 it's
like that. every other application on my machine is left to right -> only
that is right to left. Everything is English on my machine. Very weird.
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ertxVMqCEHA.1588@.tk2msftngp13.phx.gbl...
> This sounds more like a Windows setting to me...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Michael" <Michael@.nospamplease.com> wrote in message
> news:OEpXDJqCEHA.2592@.TK2MSFTNGP12.phx.gbl...
go
> is
>|||Got it to work - It was Windows Settings, thanks Tibor!!!
"Michael" <Michael@.nospamplease.com> wrote in message
news:OEpXDJqCEHA.2592@.TK2MSFTNGP12.phx.gbl...
> Hi. I got Enterprise Manager to work finally.
> However, the Columns are backwards when I retrieve all rows, and when i go
> to design a table.
> Meaning to say that the ID starts on right side of screen, and scrollbar
is
> on left side (everything is backwards)
> in options of enterprise manager, it says english is default language.
> thanks
>
>
Monday, March 19, 2012
Hexadecimal condition check and Identity columns
hi , i am using transactional replication between two Sql server 2000 . my
tables have identity columns as the primary keys with the identity property
set to Yes( not for replication). Now when I am generating the replication
intrinsic stored procs for the subscriber , I have a problem with the update
stored procs which are generated at the subscriber side. Each of these Update
command SPs ( which are each for a table) have two update statements in them
and a IF statement which chooses one of the udpate statements to run
depending on a binary hexa decimal field. If the condition satifies , it run
the first update query which unlike the second update query updates the
identity column as well and this update query is always selected by the
Update statement to run. The second if stement is run when the hexadicmal
conditions is not met and the funny part is that it never ever happens. I had
to comment out the part of the first update query which tried to update the
identity column. Now my queston are
1.why is replication trying to update the identity columns when it know
that they can not be updated.
2.What is the binary hexadecimal field used for, why is this the condition
in each and every case select statement in every field?
Please help me with this hexadecimal paradigm
The IF statement checks to see if the PK is being updated, and if so
executes the update statement differently. This is not related to identity
columns. If you have an identity column on the subscriber, compilation of
this procedure may fail. That's because the identity attribute shouldn't be
there - it's implicitly treated as read-only. If you had queued updating
subscribers the logic in these sps is different and there is no update or
insert into the identity columns because the identity attribute is expected
on the subscriber.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
tables have identity columns as the primary keys with the identity property
set to Yes( not for replication). Now when I am generating the replication
intrinsic stored procs for the subscriber , I have a problem with the update
stored procs which are generated at the subscriber side. Each of these Update
command SPs ( which are each for a table) have two update statements in them
and a IF statement which chooses one of the udpate statements to run
depending on a binary hexa decimal field. If the condition satifies , it run
the first update query which unlike the second update query updates the
identity column as well and this update query is always selected by the
Update statement to run. The second if stement is run when the hexadicmal
conditions is not met and the funny part is that it never ever happens. I had
to comment out the part of the first update query which tried to update the
identity column. Now my queston are
1.why is replication trying to update the identity columns when it know
that they can not be updated.
2.What is the binary hexadecimal field used for, why is this the condition
in each and every case select statement in every field?
Please help me with this hexadecimal paradigm
The IF statement checks to see if the PK is being updated, and if so
executes the update statement differently. This is not related to identity
columns. If you have an identity column on the subscriber, compilation of
this procedure may fail. That's because the identity attribute shouldn't be
there - it's implicitly treated as read-only. If you had queued updating
subscribers the logic in these sps is different and there is no update or
insert into the identity columns because the identity attribute is expected
on the subscriber.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Monday, March 12, 2012
Here is a question I cant seem to find the answer to
Hi all. I'm kind of stuck. I may be having a general conceptual error here.
Suppose I have a table with two columns in the primary key, ID and VERSION.
Suppose I have a child table that I want a relationship on ID only and not VERSION.
Is there a way to do this besides creating a check constraint? (ie When I print and ERD it gives the lines, so the "non" db folks can "see" the relationships)
not sure if this is possible or not but any clues you all can give would be most helpful.
Thanks,
bill
s e g fa h l t @. l o n g b o y s . n e tThe conceptual error you have made is mixing up the parent and child. The table with id and version should be the child, and then it can have a foreign key relationship with the parent.
CREATE TABLE parent (
id int NOT NULL ,
string char (10) NULL
)
GO
ALTER TABLE parent ADD CONSTRAINT
PK_parent PRIMARY KEY CLUSTERED
(id)
GO
CREATE TABLE child (
id int NOT NULL ,
version int NOT NULL ,
string char (10) NULL
)
GO
ALTER TABLE child ADD CONSTRAINT
PK_child PRIMARY KEY CLUSTERED
(id, version)
GO
ALTER TABLE child ADD CONSTRAINT
FK_child_parent FOREIGN KEY
(id) REFERENCES parent (id)
GO
Hope this helps.|||Thanks for the feedback. My apologies for not being more clear on the subject. I opted for brevity instead of details.
Here is my situation.
I have a very large table which I want to split up into smaller tables. The tables [should] ideally have a 1-1 relationship between them all.
I also need to implement a transaction tracking solution such that I can determine if any column of any of the tables was changed, when it was changed, and who changed it, and be able to rollback if necessary to any particular point in time.
Finally, I also need to implement a modification approval system, such that any proposed changes must be captured, run through the red-tape corporate pipeline, then when approved, put into the table as actual data. While the changes are in the approval process, I need to be able to show the entire record with the proposed changes to one set of individual, and the entire record with only the approved changes/data to another set of people.
There is non-trivial amount of tables I have to do this with.
I came up with 2 feasible solutions.
solution 1 is : Each table which I need to track will have a copy table(same structure). It will store the proposed changes and any change history.
solution 2 is a bit more abstract. For each of my tables which I need to have transaction auditing and modification approval on, I will add two columns. a version column and a stage column. The version column will be used mainly for reporting and an easy way to see changelog history for any particular item ID. The stage column will be of "proposed", "review", "approved", "rejected", "obsolete". All the data will be contained in one table. so for each unique record(identified by ID) I will actually have multiple records with the same ID. But each successive record will have a higher version number. As new Version Numbers are added, the stage will be moved to "obsolete" when the changes are approved. then that record's stage will be set to "approved" and it will become the official record.
I just came across this yesterday, so i'm still fleshing out my "solution". I like the idea of solution 2 as its compact and fairly easy to code for. However, the more I think about it, the more I feel I will break anykind of RI I can put and still use the DB to enforce it. I don't think I even need a primary key of (ID, Version), but you need a primary key, to have a foreign key. If I could get the relationship, without having a primary key(just have a clustered, non-unique index on ID) then that would be sufficient. However, I'm pretty new to Sql Server, so I don't know if there is a way to do that. Any Ideas?
here is a visual of what my solution 2 would be like.
I have a table
create table table1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key (id, version)
)
I have a child table, which I need a RI check on ID only of the parent table. It too, will have a version column, but does not relate to the parent table. It will however, be part of the primary key of the child table.
create table child1ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)
create table child2ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)
I need the relationship from table1 to child[12]ToTable1 to be a many to many on ID, but I don't care about the version.
Clear as mud? I know. it kind of gives me a headache too.
:)
Thanks for you help.
bill
Suppose I have a table with two columns in the primary key, ID and VERSION.
Suppose I have a child table that I want a relationship on ID only and not VERSION.
Is there a way to do this besides creating a check constraint? (ie When I print and ERD it gives the lines, so the "non" db folks can "see" the relationships)
not sure if this is possible or not but any clues you all can give would be most helpful.
Thanks,
bill
s e g fa h l t @. l o n g b o y s . n e tThe conceptual error you have made is mixing up the parent and child. The table with id and version should be the child, and then it can have a foreign key relationship with the parent.
CREATE TABLE parent (
id int NOT NULL ,
string char (10) NULL
)
GO
ALTER TABLE parent ADD CONSTRAINT
PK_parent PRIMARY KEY CLUSTERED
(id)
GO
CREATE TABLE child (
id int NOT NULL ,
version int NOT NULL ,
string char (10) NULL
)
GO
ALTER TABLE child ADD CONSTRAINT
PK_child PRIMARY KEY CLUSTERED
(id, version)
GO
ALTER TABLE child ADD CONSTRAINT
FK_child_parent FOREIGN KEY
(id) REFERENCES parent (id)
GO
Hope this helps.|||Thanks for the feedback. My apologies for not being more clear on the subject. I opted for brevity instead of details.
Here is my situation.
I have a very large table which I want to split up into smaller tables. The tables [should] ideally have a 1-1 relationship between them all.
I also need to implement a transaction tracking solution such that I can determine if any column of any of the tables was changed, when it was changed, and who changed it, and be able to rollback if necessary to any particular point in time.
Finally, I also need to implement a modification approval system, such that any proposed changes must be captured, run through the red-tape corporate pipeline, then when approved, put into the table as actual data. While the changes are in the approval process, I need to be able to show the entire record with the proposed changes to one set of individual, and the entire record with only the approved changes/data to another set of people.
There is non-trivial amount of tables I have to do this with.
I came up with 2 feasible solutions.
solution 1 is : Each table which I need to track will have a copy table(same structure). It will store the proposed changes and any change history.
solution 2 is a bit more abstract. For each of my tables which I need to have transaction auditing and modification approval on, I will add two columns. a version column and a stage column. The version column will be used mainly for reporting and an easy way to see changelog history for any particular item ID. The stage column will be of "proposed", "review", "approved", "rejected", "obsolete". All the data will be contained in one table. so for each unique record(identified by ID) I will actually have multiple records with the same ID. But each successive record will have a higher version number. As new Version Numbers are added, the stage will be moved to "obsolete" when the changes are approved. then that record's stage will be set to "approved" and it will become the official record.
I just came across this yesterday, so i'm still fleshing out my "solution". I like the idea of solution 2 as its compact and fairly easy to code for. However, the more I think about it, the more I feel I will break anykind of RI I can put and still use the DB to enforce it. I don't think I even need a primary key of (ID, Version), but you need a primary key, to have a foreign key. If I could get the relationship, without having a primary key(just have a clustered, non-unique index on ID) then that would be sufficient. However, I'm pretty new to Sql Server, so I don't know if there is a way to do that. Any Ideas?
here is a visual of what my solution 2 would be like.
I have a table
create table table1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key (id, version)
)
I have a child table, which I need a RI check on ID only of the parent table. It too, will have a version column, but does not relate to the parent table. It will however, be part of the primary key of the child table.
create table child1ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)
create table child2ToTable1 (
id int not null,
version int not null,
stageId int not null,
more columns,
primary key(id, version)
)
I need the relationship from table1 to child[12]ToTable1 to be a many to many on ID, but I don't care about the version.
Clear as mud? I know. it kind of gives me a headache too.
:)
Thanks for you help.
bill
Wednesday, March 7, 2012
Help: UNION vs. CONTAINSTABLE
I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns.
Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank.
I'm not sure if this is possible. So far, I have:
1SELECT RANK, field_1, field_2, ..., field_mFROM2(3SELECT RANK, field_1, field_2, ..., field_mFROM T_1,4CONTAINSTABLE(T_1, field_i,@.searchText) searchTable5WHERE KEY = T_1.field_i6UNION7SELECT RANK, field_1, field_2, ..., field_mFROM T_2,8CONTAINSTABLE(T_2, field_i,@.searchText) searchTable9WHERE KEY = T_2.field_i10UNION11.12.13.14UNION15SELECT RANK, field_1, field_2, ... field_mFROM T_n,16CONTAINSTABLE(T_n, field_i,@.searchText) searchTable17WHERE KEY = T_n.field_i18)19ORDER BY RANKDESC
I haven't tried it yet, but it seems wrong. How do we actually do this?
You should first create a CTE (Common Table Expression) or a VIEW:http://www.singingeels.com/Articles/Understanding_SQL_Complex_Queries.aspx
You would do something like this:
WITH MyTables (ColA, ColB etc...) AS ( SELECT * FROM T_1 UNION SELECT * FROM T_2)
now.. you do your query with "MyTables" as the table name...
|||I'm now getting an error that says:
Cannot use a CONTAINS or FREETEXT predicate on object 'MyTables' because it is not full-text indexed.
That's true, though. The table we created isn't full-text enabled.
----------
My other option is to do a query for each table I want searched, then merge them all into a DataSet, then reorder the DataSet by rank. How tedious, though, especially if I want pagination of large tables.
|||I read somewhere that SQL Server 2005 does not support free text searching over multiple tables. There's something called SQLONE that actually allows FTS over multiple tables.
Monday, February 27, 2012
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!
Sunday, February 19, 2012
HELP: Delayed Inserts in SQL Server!
Hi all,
I have a small table with the following columns:
Refno (int), CampaignID(int), Value (int)
Sometimes a record takes >30 - 40 seconds to write. Is there a way to
ensure that a record is written in a timely manner?
Other info: I do have a stored procedure which loops the table waiting
for a response:
WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
AND @.LOOPCOUNTER < 30
BEGIN
SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
/* Pause Loop Delay in hh:mm:ss:ms format */
WAITFOR DELAY '00:00:00.200'
END
Also, once a record is read, it is deleted from the table. Could the
deletes be locking the table?
Thank you for ANY help.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/Your deletes and your looping SELECT can both be blocking the inserts.
Can you post DDL for the table, including any indexes and constraints?
Also, how many rows are in the table?
Your loop might be more efficient expressed as:
WHILE NOT EXISTS (SELECT * FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/|||"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
>
What version of SQL Server are you running? I am assuming 2000.
What indexes are on the table?
Why do you have a WHILE loop running? That is most likely the cause of your
problem. It is probably holding locks on the table while an INSERT is
attempting to grab locks.
Is the looping doing something for you? Some type of notification? Could
you use a trigger on INSERT to accomplish the same task without using the
loop?
Rick Sawtell
MCT, MCSD, MCDBA|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> Can you post DDL for the table, including any indexes and constraints?
> Also, how many rows are in the table?
Approximately 20 - 30 rows at a time. After a record is read, it is deleted
from the table.
There are no indexes and constraints on the table. I am running SQL Server
2000.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
The While loop is used for notification - An ASP script executes a stored
procedure which loops the table waiting for the insert. Once the insert is
completed, the loop will complete and the value is returned to the ASP
page.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:uoA11mcxEHA.2624@.TK2MSFTNGP11.phx.gbl:
> What version of SQL Server are you running? I am assuming 2000.
Yes, SQL Server 2000.
> What indexes are on the table?
There are no indexes - I dropped all indexes in hopes that the INSERT
query will run faster.
> Why do you have a WHILE loop running? That is most likely the cause
> of your problem. It is probably holding locks on the table while an
> INSERT is attempting to grab locks.
The While loop is used for notification - An ASP script executes a
stored procedure which loops the table waiting for the insert to
complete. Once the insert is completed, the loop will find the record
and the value is returned to the ASP page. If no value is found within X
seconds the SP exits and the default value returned.
> Is the looping doing something for you? Some type of notification?
> Could you use a trigger on INSERT to accomplish the same task without
> using the loop?
Do you know of any other way to pause the execution of a select
statement and wait for a notification? A trigger *would* be nice... but
the problem is that the value must be returned to an external process (a
VoiceXML server which can only call webpages):
Here is the DDL for the table in case it might help you.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||You could start by defining a primary key... Refno, perhaps?
And maybe ease up the SELECT loop a bit, and/or use a (NOLOCK) hint to
ensure that it doesn't hold locks on the table...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B95DA8324nntprogerscom@.140.99.99.130...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> > Can you post DDL for the table, including any indexes and constraints?
> > Also, how many rows are in the table?
> Approximately 20 - 30 rows at a time. After a record is read, it is
deleted
> from the table.
> There are no indexes and constraints on the table. I am running SQL Server
> 2000.
> CREATE TABLE [dbo].[DialAnswerLookup] (
> [campID] [int] NOT NULL ,
> [refno] [int] NOT NULL ,
> [answeredby] [int] NULL
> ) ON [PRIMARY]
> GO
>
> The While loop is used for notification - An ASP script executes a stored
> procedure which loops the table waiting for the insert. Once the insert is
> completed, the loop will complete and the value is returned to the ASP
> page.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/
I have a small table with the following columns:
Refno (int), CampaignID(int), Value (int)
Sometimes a record takes >30 - 40 seconds to write. Is there a way to
ensure that a record is written in a timely manner?
Other info: I do have a stored procedure which loops the table waiting
for a response:
WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
AND @.LOOPCOUNTER < 30
BEGIN
SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
/* Pause Loop Delay in hh:mm:ss:ms format */
WAITFOR DELAY '00:00:00.200'
END
Also, once a record is read, it is deleted from the table. Could the
deletes be locking the table?
Thank you for ANY help.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/Your deletes and your looping SELECT can both be blocking the inserts.
Can you post DDL for the table, including any indexes and constraints?
Also, how many rows are in the table?
Your loop might be more efficient expressed as:
WHILE NOT EXISTS (SELECT * FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/|||"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B8FF7AC71Dnntprogerscom@.140.99.99.130...
> Hi all,
> I have a small table with the following columns:
> Refno (int), CampaignID(int), Value (int)
> Sometimes a record takes >30 - 40 seconds to write. Is there a way to
> ensure that a record is written in a timely manner?
>
> Other info: I do have a stored procedure which loops the table waiting
> for a response:
> WHILE ((SELECT COUNT(*) FROM DIALANSWERLOOKUP WHERE REFNO = @.REFNO) = 0)
> AND @.LOOPCOUNTER < 30
> BEGIN
> SET @.LOOPCOUNTER = @.LOOPCOUNTER + 1
> /* Pause Loop Delay in hh:mm:ss:ms format */
> WAITFOR DELAY '00:00:00.200'
> END
> Also, once a record is read, it is deleted from the table. Could the
> deletes be locking the table?
> Thank you for ANY help.
>
What version of SQL Server are you running? I am assuming 2000.
What indexes are on the table?
Why do you have a WHILE loop running? That is most likely the cause of your
problem. It is probably holding locks on the table while an INSERT is
attempting to grab locks.
Is the looping doing something for you? Some type of notification? Could
you use a trigger on INSERT to accomplish the same task without using the
loop?
Rick Sawtell
MCT, MCSD, MCDBA|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> Can you post DDL for the table, including any indexes and constraints?
> Also, how many rows are in the table?
Approximately 20 - 30 rows at a time. After a record is read, it is deleted
from the table.
There are no indexes and constraints on the table. I am running SQL Server
2000.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
The While loop is used for notification - An ASP script executes a stored
procedure which loops the table waiting for the insert. Once the insert is
completed, the loop will complete and the value is returned to the ASP
page.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||"Rick Sawtell" <quickening@.msn.com> wrote in
news:uoA11mcxEHA.2624@.TK2MSFTNGP11.phx.gbl:
> What version of SQL Server are you running? I am assuming 2000.
Yes, SQL Server 2000.
> What indexes are on the table?
There are no indexes - I dropped all indexes in hopes that the INSERT
query will run faster.
> Why do you have a WHILE loop running? That is most likely the cause
> of your problem. It is probably holding locks on the table while an
> INSERT is attempting to grab locks.
The While loop is used for notification - An ASP script executes a
stored procedure which loops the table waiting for the insert to
complete. Once the insert is completed, the loop will find the record
and the value is returned to the ASP page. If no value is found within X
seconds the SP exits and the default value returned.
> Is the looping doing something for you? Some type of notification?
> Could you use a trigger on INSERT to accomplish the same task without
> using the loop?
Do you know of any other way to pause the execution of a select
statement and wait for a notification? A trigger *would* be nice... but
the problem is that the value must be returned to an external process (a
VoiceXML server which can only call webpages):
Here is the DDL for the table in case it might help you.
CREATE TABLE [dbo].[DialAnswerLookup] (
[campID] [int] NOT NULL ,
[refno] [int] NOT NULL ,
[answeredby] [int] NULL
) ON [PRIMARY]
GO
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
http://members.ebay.com/aboutme/coolspot18/|||You could start by defining a primary key... Refno, perhaps?
And maybe ease up the SELECT loop a bit, and/or use a (NOLOCK) hint to
ensure that it doesn't hold locks on the table...
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns959B95DA8324nntprogerscom@.140.99.99.130...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> news:eSEjFmcxEHA.2012@.TK2MSFTNGP15.phx.gbl:
> > Can you post DDL for the table, including any indexes and constraints?
> > Also, how many rows are in the table?
> Approximately 20 - 30 rows at a time. After a record is read, it is
deleted
> from the table.
> There are no indexes and constraints on the table. I am running SQL Server
> 2000.
> CREATE TABLE [dbo].[DialAnswerLookup] (
> [campID] [int] NOT NULL ,
> [refno] [int] NOT NULL ,
> [answeredby] [int] NULL
> ) ON [PRIMARY]
> GO
>
> The While loop is used for notification - An ASP script executes a stored
> procedure which loops the table waiting for the insert. Once the insert is
> completed, the loop will complete and the value is returned to the ASP
> page.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> http://members.ebay.com/aboutme/coolspot18/
Subscribe to:
Posts (Atom)