Puzzling Primary Key Problem

Hi,

I wonder if anybody encountered before with tables which has records with some of the primary keys as null values?

Currently I've found a number of records which isn't suppose to be inserted in the first place from a table.

My table size is over a few millions and currently using SQL Server Standard version

Seems like the primary key got corrupted or something...

Anybody encountered this before??

-deb-If the primary key of the table is autogenerated then you can use

DBCC CHECKIDENT

for Checking the current identity value for the specified table and, if needed, corrects the identity value.|||Thanks but I don't think that's the case because it happens to a non identity column.
By Default even before inserting it SQL Server should have prompted a Primary Key Violation Error but in this case it doesn't and proceed to insert the value into the column as null.|||Can you execute a DBCC Checktable statement on the table and verify the results.|||Will try and see and let you know as the db is at customer's site.
Thanks
:)|||do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

here is what [BOL] has to say on the matter

"When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft SQL Server 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

No null values
No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
"

so i tried this

create table nulltesttable
(
nullcolumn varchar(10) not null
)

and added three rows

insert nulltesttable values (char(9))
insert nulltesttable values (char(13))
insert nulltesttable values (char(32))

then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

select Char(nullcolumn) as 'Null column'
from nulltesttable

there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...|||Thanks... actually that was what I suspect also due to the reason the values are from barcode and sometimes the reader can return some rubbish that is not visible. There are a few records which this rubbish data.

But still I need to look for other possibilities. For my case if I were to select out using a select statement

select * from table
where column is null

It'll return records. :)

That's the very funny thing*sigh*

Originally posted by Ruprect
do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

here is what [BOL] has to say on the matter

"When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft SQL Server 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

No null values
No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
"

so i tried this

create table nulltesttable
(
nullcolumn varchar(10) not null
)

and added three rows

insert nulltesttable values (char(9))
insert nulltesttable values (char(13))
insert nulltesttable values (char(32))

then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

select Char(nullcolumn) as 'Null column'
from nulltesttable

there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...sql

Pushy Matrix

Hello,

I've encountered an issue with a couple of reports and am wondering if anyone has any information on this. I have found a work-around, but would like to know what causes the issue, so maybe I can address it in another manner.

As standard procedure in all my reports, I have a graphic at the top which is set to Fit Proportional and has a width which is 1.25" shorter than the total width of the report. The same height as the image and filling the remainder of the report width next to it, I place a textbox with a simple expression to provide the time the report was run with a small custom message using the Now() function. Below these two items is another textbox which contains the report title. This textbox typically spans the entire width of the report and is positioned about 1/8" beneath the other two items to avoid incidental contact with the image. In some cases, there will be an additional textbox beneath this with a short description of the report and it's function. Finally, the other report items are displayed with the main content of the report.

In situations where the main content of the report contains a matrix item, I encounter an issue when the report is rendered either through the Preview or a browser. This rendering issue also occurs on occasion when exporting to PDF, though I've not tested other export formats in this situation. What occurs is the textbox which displays the time the report was run ends up being pushed to the far right of the display window beyond the width of the report itself. In Preview or the browser, this is shown on one page with this text box far outside the rest of the report. When the issue impacts exporting to PDF, this sole textbox is pushed to a blank additional page which is obviously not acceptable.

For those who may be wondering, I am working with SQL RS 2000 and VS 2003.

The workaround that I've come up with for this issue is to place both the heading graphic and time textbox within a rectangle. No other adjustments are necessary. For some reason, not what I expected to happen when I tried this, the entire rectangle is left on the main page instead of being pushed to another page as I expected. This works, but seems silly to have to place these items in a rectangle to keep some invisible page break from displacing my textbox.

Again, no rush as I already have a work-around, but just out of curiosity, I wanted to see if anyone else had encountered this. Possibly this will give someone else a work-around if they hadn't found one already.

Thanks

This is actually the correct behavior for all versions of RS. In performing layout, RS decides how items will be pushed around the page by expanding items based on a number of factors. One of those factors is the relative positions of the left edge of the pushed item and the right edge of the pushing item. For example, above, you describe a situation where the left edge of your title textbox is to the right of the matrix's right edge. In this situation, when the matrix expands by X" it causes all items farther right than its right edge to also move by X". Your header image didn't move to the right because its left edge was farther left than the matrix's right edge. When you placed the rectangle around both the image and the textbox, you ensured the rectangle, and therefore the textbox, wouldn't be moved because its left edge was farther left than the matrix's right edge.

This same process also applies to vertical movement. Say you have a page containing a textbox and a matrix side-by-side. If the top edge of the textbox is above the bottom edge of the matrix, the textbox will not be moved down when the matrix expands.

Hopefully this helps explain why your work-around works.

Thanks,

John

|||

Thanks again for the information. That all makes sense, but now it poses another problem. In this situation, I have my same header graphic, time stamp and title, but in the body, I have a matrix and a 100% stacked bar chart sitting next to each other. My report is set to landscape dimensions with 1" margins all around. In design, the matrix is 2.5" wide and the chart is 4.25" wide. There is about a 2" gap between the two items. When I preview this report within VS2003, it appears normal in the standard preview and the print preview. Once I upload the report and view it in Internet Explorer 6, the matrix is pushing the chart over to the right. If I export to PDF from there, it is normal again. When the matrix is completely populated with the most data it could contain, there is still about an inch between it and the chart, EXCEPT when viewing in Internet Explorer.

Any ideas would be greatly appreciated. It is not too likely that anyone would be viewing this particular report from Internet Explorer except to export it to PDF, but I would still like it to look the same when viewing in the browser as it does when exporting.

Thanks In Advance!

|||

I had been playing with this same issue for a few hours, your work-around is exactly what I needed. In my case, my textboxes and matrix were separated by a full-page graph. This makes my report 2 pages, which is what I wanted. The situation where "This is normal rendering" is useless in my case, making it simply more difficult to use. Some days, I miss Crystal Reports. Thankfully, most of our apps use Oracle Reports.

Thanks again! Appreciate the help.

|||I'm having a similar issue. I set up a report with 3 subreports in the detail section. Each subreport is a matrix. I want the 3 matrixes to print one after another without a page break and in Preview, it works fine. When I export to PDF, if the matrix has any data at all, it allocates the entire page and forces everything else onto the next page - even if there's only one line of data. The PageBreakAtEnd and PageBreakAtStart properties are set to false.

Pushy Matrix

Hello,

I've encountered an issue with a couple of reports and am wondering if anyone has any information on this. I have found a work-around, but would like to know what causes the issue, so maybe I can address it in another manner.

As standard procedure in all my reports, I have a graphic at the top which is set to Fit Proportional and has a width which is 1.25" shorter than the total width of the report. The same height as the image and filling the remainder of the report width next to it, I place a textbox with a simple expression to provide the time the report was run with a small custom message using the Now() function. Below these two items is another textbox which contains the report title. This textbox typically spans the entire width of the report and is positioned about 1/8" beneath the other two items to avoid incidental contact with the image. In some cases, there will be an additional textbox beneath this with a short description of the report and it's function. Finally, the other report items are displayed with the main content of the report.

In situations where the main content of the report contains a matrix item, I encounter an issue when the report is rendered either through the Preview or a browser. This rendering issue also occurs on occasion when exporting to PDF, though I've not tested other export formats in this situation. What occurs is the textbox which displays the time the report was run ends up being pushed to the far right of the display window beyond the width of the report itself. In Preview or the browser, this is shown on one page with this text box far outside the rest of the report. When the issue impacts exporting to PDF, this sole textbox is pushed to a blank additional page which is obviously not acceptable.

For those who may be wondering, I am working with SQL RS 2000 and VS 2003.

The workaround that I've come up with for this issue is to place both the heading graphic and time textbox within a rectangle. No other adjustments are necessary. For some reason, not what I expected to happen when I tried this, the entire rectangle is left on the main page instead of being pushed to another page as I expected. This works, but seems silly to have to place these items in a rectangle to keep some invisible page break from displacing my textbox.

Again, no rush as I already have a work-around, but just out of curiosity, I wanted to see if anyone else had encountered this. Possibly this will give someone else a work-around if they hadn't found one already.

Thanks

This is actually the correct behavior for all versions of RS. In performing layout, RS decides how items will be pushed around the page by expanding items based on a number of factors. One of those factors is the relative positions of the left edge of the pushed item and the right edge of the pushing item. For example, above, you describe a situation where the left edge of your title textbox is to the right of the matrix's right edge. In this situation, when the matrix expands by X" it causes all items farther right than its right edge to also move by X". Your header image didn't move to the right because its left edge was farther left than the matrix's right edge. When you placed the rectangle around both the image and the textbox, you ensured the rectangle, and therefore the textbox, wouldn't be moved because its left edge was farther left than the matrix's right edge.

This same process also applies to vertical movement. Say you have a page containing a textbox and a matrix side-by-side. If the top edge of the textbox is above the bottom edge of the matrix, the textbox will not be moved down when the matrix expands.

Hopefully this helps explain why your work-around works.

Thanks,

John

|||

Thanks again for the information. That all makes sense, but now it poses another problem. In this situation, I have my same header graphic, time stamp and title, but in the body, I have a matrix and a 100% stacked bar chart sitting next to each other. My report is set to landscape dimensions with 1" margins all around. In design, the matrix is 2.5" wide and the chart is 4.25" wide. There is about a 2" gap between the two items. When I preview this report within VS2003, it appears normal in the standard preview and the print preview. Once I upload the report and view it in Internet Explorer 6, the matrix is pushing the chart over to the right. If I export to PDF from there, it is normal again. When the matrix is completely populated with the most data it could contain, there is still about an inch between it and the chart, EXCEPT when viewing in Internet Explorer.

Any ideas would be greatly appreciated. It is not too likely that anyone would be viewing this particular report from Internet Explorer except to export it to PDF, but I would still like it to look the same when viewing in the browser as it does when exporting.

Thanks In Advance!

|||

I had been playing with this same issue for a few hours, your work-around is exactly what I needed. In my case, my textboxes and matrix were separated by a full-page graph. This makes my report 2 pages, which is what I wanted. The situation where "This is normal rendering" is useless in my case, making it simply more difficult to use. Some days, I miss Crystal Reports. Thankfully, most of our apps use Oracle Reports.

Thanks again! Appreciate the help.

|||I'm having a similar issue. I set up a report with 3 subreports in the detail section. Each subreport is a matrix. I want the 3 matrixes to print one after another without a page break and in Preview, it works fine. When I export to PDF, if the matrix has any data at all, it allocates the entire page and forces everything else onto the next page - even if there's only one line of data. The PageBreakAtEnd and PageBreakAtStart properties are set to false.sql

Pushy Matrix

Hello,

I've encountered an issue with a couple of reports and am wondering if anyone has any information on this. I have found a work-around, but would like to know what causes the issue, so maybe I can address it in another manner.

As standard procedure in all my reports, I have a graphic at the top which is set to Fit Proportional and has a width which is 1.25" shorter than the total width of the report. The same height as the image and filling the remainder of the report width next to it, I place a textbox with a simple expression to provide the time the report was run with a small custom message using the Now() function. Below these two items is another textbox which contains the report title. This textbox typically spans the entire width of the report and is positioned about 1/8" beneath the other two items to avoid incidental contact with the image. In some cases, there will be an additional textbox beneath this with a short description of the report and it's function. Finally, the other report items are displayed with the main content of the report.

In situations where the main content of the report contains a matrix item, I encounter an issue when the report is rendered either through the Preview or a browser. This rendering issue also occurs on occasion when exporting to PDF, though I've not tested other export formats in this situation. What occurs is the textbox which displays the time the report was run ends up being pushed to the far right of the display window beyond the width of the report itself. In Preview or the browser, this is shown on one page with this text box far outside the rest of the report. When the issue impacts exporting to PDF, this sole textbox is pushed to a blank additional page which is obviously not acceptable.

For those who may be wondering, I am working with SQL RS 2000 and VS 2003.

The workaround that I've come up with for this issue is to place both the heading graphic and time textbox within a rectangle. No other adjustments are necessary. For some reason, not what I expected to happen when I tried this, the entire rectangle is left on the main page instead of being pushed to another page as I expected. This works, but seems silly to have to place these items in a rectangle to keep some invisible page break from displacing my textbox.

Again, no rush as I already have a work-around, but just out of curiosity, I wanted to see if anyone else had encountered this. Possibly this will give someone else a work-around if they hadn't found one already.

Thanks

This is actually the correct behavior for all versions of RS. In performing layout, RS decides how items will be pushed around the page by expanding items based on a number of factors. One of those factors is the relative positions of the left edge of the pushed item and the right edge of the pushing item. For example, above, you describe a situation where the left edge of your title textbox is to the right of the matrix's right edge. In this situation, when the matrix expands by X" it causes all items farther right than its right edge to also move by X". Your header image didn't move to the right because its left edge was farther left than the matrix's right edge. When you placed the rectangle around both the image and the textbox, you ensured the rectangle, and therefore the textbox, wouldn't be moved because its left edge was farther left than the matrix's right edge.

This same process also applies to vertical movement. Say you have a page containing a textbox and a matrix side-by-side. If the top edge of the textbox is above the bottom edge of the matrix, the textbox will not be moved down when the matrix expands.

Hopefully this helps explain why your work-around works.

Thanks,

John

|||

Thanks again for the information. That all makes sense, but now it poses another problem. In this situation, I have my same header graphic, time stamp and title, but in the body, I have a matrix and a 100% stacked bar chart sitting next to each other. My report is set to landscape dimensions with 1" margins all around. In design, the matrix is 2.5" wide and the chart is 4.25" wide. There is about a 2" gap between the two items. When I preview this report within VS2003, it appears normal in the standard preview and the print preview. Once I upload the report and view it in Internet Explorer 6, the matrix is pushing the chart over to the right. If I export to PDF from there, it is normal again. When the matrix is completely populated with the most data it could contain, there is still about an inch between it and the chart, EXCEPT when viewing in Internet Explorer.

Any ideas would be greatly appreciated. It is not too likely that anyone would be viewing this particular report from Internet Explorer except to export it to PDF, but I would still like it to look the same when viewing in the browser as it does when exporting.

Thanks In Advance!

|||

I had been playing with this same issue for a few hours, your work-around is exactly what I needed. In my case, my textboxes and matrix were separated by a full-page graph. This makes my report 2 pages, which is what I wanted. The situation where "This is normal rendering" is useless in my case, making it simply more difficult to use. Some days, I miss Crystal Reports. Thankfully, most of our apps use Oracle Reports.

Thanks again! Appreciate the help.

|||I'm having a similar issue. I set up a report with 3 subreports in the detail section. Each subreport is a matrix. I want the 3 matrixes to print one after another without a page break and in Preview, it works fine. When I export to PDF, if the matrix has any data at all, it allocates the entire page and forces everything else onto the next page - even if there's only one line of data. The PageBreakAtEnd and PageBreakAtStart properties are set to false.

Push subscription failing

SQL Server Enterprise Manager encountered errors creating push subscriptions
for the following Subscribers:
JANUS: Error 208: Invalid object name 'sysextendedarticlesview'.
The error above shows up each time that I attempt to push the subscription.
THe view sysextendedarticlesview exist in the publisher database. DOes
anybody know how to fix this thing? THanks
issue this on the subscriber in the subscription database.
create view dbo.sysextendedarticlesview
as
select * from sysarticles
union all
select artid, NULL, creation_script, NULL, description,
dest_object, NULL, NULL, NULL, name, objid, pubid,
pre_creation_cmd, status, NULL, type, NULL,
schema_option, dest_owner from sysschemaarticles
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pete Ocasio" <pete.ocasio@.execupay.com> wrote in message
news:OKRoJYKsEHA.2788@.TK2MSFTNGP09.phx.gbl...
> SQL Server Enterprise Manager encountered errors creating push
subscriptions
> for the following Subscribers:
> JANUS: Error 208: Invalid object name 'sysextendedarticlesview'.
> The error above shows up each time that I attempt to push the
subscription.
> THe view sysextendedarticlesview exist in the publisher database. DOes
> anybody know how to fix this thing? THanks
>
sql