NoRows SubReport

Greetings,

I have a report which contains three subreports. Each subreport is contained within a rectangle in the main report so I can force a page break after each subreport. In each of the subreports, there is a graphic and a text box on one line, then another text box below those two items, then a table below all that. My problem is that if one of the subreports returns no data, it is resulting in a NoRows sort of situation and not even the graphic and the two text boxes are shown. I understand that you can set a NoRows message, but ideally, I want my NoRows message to be the graphic and the two text boxes, not a plain text message.

I'm not sure if this is even possible or at the very least if I could conditionally force a page break in my rectangles based on whether one of the subreports returns a NoRows situation.

Thanks In Advance

One thing that you can do is create a second dataset that always returns data. You can do this with a query like the following. This causes the SubReport to always report to the processing runtime that it contains data, so that it should always be processed and rendered.

Select 1 as DataValue

However, the problem with this is that the empty table will still contain the Table headers. So, you will have to make the table conditionally visible using an expression such as this.

=IsNothing(Fields!FieldName.Value)

You can pretty much use any field in the dataset to which the table is bound.

Adding the second dataset and adding the conditional visibility to the table should result in the subreport still being displayed, with the table hidden.

Ian|||

This works perfectly. Thank You. In fact, due to the NoRows parameter being set on the subreport, the table headers themselves are not shown, but instead the NoRows message is shown WITH the graphic and two text boxes. This is exactly the result I wanted.

Normalizing Address Information...

THE LAYOUT:
I have two tables: "Applicant_T" and "StreetSuffix_T"

The "Applicant_T" table contains fields for the applicant's current address, previous address and employer address. Each address is broken up into parts (i.e., street number, street name, street suffix, etc.). For this discussion, I will focus on the street suffix. For each of the addresses, I have a street suffix field as follows:

[Applicant_T]
CurrSuffix
PrevSuffix
EmpSuffix

The "StreetSuffix_T" table contains the postal service approved street suffix names. There are two fields as follows:

[StreetSuffix_T]
SuffixID <--this is the primary key
Name

For each of the addresses in the Applicant_T table, I input the SuffixID of the StreetSuffix_T table.

THE PROBLEM:
I have never created a view that would require the primary key of one table to be associated with multiple fields of another table (i.e., SuffixID-->CurrSuffix, SuffixID-->PrevSuffix, SuffixID-->EmpSuffix). I want to create a view of the Applicant_T table that will show the suffix name from the StreetSuffix_T table for each of the suffix fields in the Applicant_T table. How is this done?I got the solution from another forum. It is as follows:

create view ApplicantAddresses
 ( currstreetnumber
 , currstreetname
 , ...
 , currsuffixname
 , prevsuffixname
 , empsuffixname
 )
as
select currstreetnumber
 , currstreetname
 , ...
 , c.name
 , p.name
 , e.name
 from Applicant_T
inner
 join StreetSuffix_T c
 on currsuffix = c.SuffixID
inner
 join StreetSuffix_T p
 on prevsuffix = p.SuffixID
inner
 join StreetSuffix_T e
 on empsuffix = e.SuffixID

|||Having the primary key of one table be associated with mulitple fields in another table is not unusual. However, it does bring a "Spock's raised eyebrow" indicating a table design that could be improved upon. To use the same lookup table more than once you just need to create table aliases so that the query can distinguish between the tables. You also need aliases in the Select clause so you can tell them apart.

SELECT dbo.StreetSuffix_T.Suffix AS CurrSuffix, StreetSuffix_T_1.Suffix AS PrevSuffix, StreetSuffix_T_2.Suffix AS EmpSuffix
FROM dbo.Applicant_T INNER JOIN
 dbo.StreetSuffix_T ON dbo.Applicant_T.CurrSuffix = dbo.StreetSuffix_T.SuffixID INNER JOIN
 dbo.StreetSuffix_T StreetSuffix_T_1 ON dbo.Applicant_T.PrevSuffix = StreetSuffix_T_1.SuffixID INNER JOIN
 dbo.StreetSuffix_T StreetSuffix_T_2 ON dbo.Applicant_T.EmpSuffix = StreetSuffix_T_2.SuffixID

However, the design of Applicant_T seems a bit suspect since you have multiple addresses. Maybe today you want current, former, and employer addresses but in the future you might need another one (spouses employer address, delivery address, second employer address, whatever). With your current design you'd need to add a bunch of new columns whenever you add a new address type.

It may be better to have a separate table that just handles addresses. It might have two keys, one to link back to the applicant_T table and another for the type of address held in that record. (0=current address, 1=previous address, 100=employer address, 1000=dog groomers address).|||McMurdoStation,

You are right. In my pursuit of a solution to a short-term issue, I neglected to consider the longer-term impact of my decision. I am going to change the table design.

Thanks for your input :)|||Yeah, you save less than a byte to normalize that, and lose tons of rotations in the joins.

Sacrifice space for efficiency in runtimes every once in awhile.|||The idea of normalization isn't to save space. If normalizing causes performance bottlenecks you can do things to address that. But that problem is easier to address when and if necessary than having a data model that can't handle change.|||In my particular case, scalability is of high importance. Normalizing the addresses in the manner McMurdoStation suggested, provides such scalability.

Normal view compare to partitioned view

Hi,
I have one million records in one table, My data
contains all the records from for eg. January to December.
I use normal view to select out certain period of
records, for eg. January. then i query the records use
the remote view from foxpro to filter up the records from
this view. Does it make my data selection faster?
Or, do i need to separate out my data into few
tables and then use partitioned view to query the data
out?
Which one is faster?
Thanks.
regards,
florenceHi
1'000'000 rows does not justify Partitioned Views. Once you get into the
1000's of millions it becomes justified (and then you nedd to put each table
on a seperate disk volume to make it perform). Rather make sure that you have
correct indexes and that you configure your hardware correctly.
Regards
Mike
"florencelee" wrote:
> Hi,
> I have one million records in one table, My data
> contains all the records from for eg. January to December.
> I use normal view to select out certain period of
> records, for eg. January. then i query the records use
> the remote view from foxpro to filter up the records from
> this view. Does it make my data selection faster?
> Or, do i need to separate out my data into few
> tables and then use partitioned view to query the data
> out?
> Which one is faster?
> Thanks.
> regards,
> florence
>

noob alert! What i thought was a real simple query...

Hi

I have a table which has the column [itemNumber] Which contains numbers from 000 to 999. I have another table which has the UPC data for given items
I am trying to get results from my query that will show me every number in the itemNumberSet table that does not already exist (in the substring) of the UPCcode column.

By using the query below i am able to retrieve the opposite, and it works by returning results that do exist in the UPCcode column. But I cannot seem to get it to do the opposite which is what i am after. I figured it would be as simple as using NOT IN but that returned 0 results.

SELECT itemNumber FROM itemNumberSet
WHERE itemNumber IN (select SUBSTRING(UPCcode, 9, 3) FROM itemUPCtable)
ORDER BY itemNumber

Thanks for any suggestions you might have.
Jthis perhaps?
SELECT itemNumberSet.itemNumber
FROM itemNumberSet
LEFT OUTER
JOIN itemUPCtable
ON SUBSTRING(itemUPCtable.UPCcode,9,3) = itemNumberSet.itemNumber
WHERE SUBSTRING(itemUPCtable.UPCcode,9,3) IS NULL
ORDER
BY itemNumberSet.itemNumber|||I really need to practice playing with joins, cause as it stands im a hack. I should really be taking a course or something. I see you are in Toronto. I'm in Mississauga. Any tips as to where one might go to be properly educated in SQL?

Thanks again
J|||you can go a long way with online free sql tutorials

however, you need to make sure you are on an actual tutorial site, as in here's-some-good-information-because-i-love-sql type of tutorial site, rather than here's-some-sql-stuff-which-i-lifted-from-somewhere-so-i-can-run-a-lot-of-ads-and-make-money type of tutorial site

there are a couple of good sites listed here: http://r937.com/links.cfm?links=sqlsql

Non-release version (600) is not supported by this version of SQL Server

I downloaded the 101 Samples installation (i.e. 101SamplesCS.msi) from Microsoft's website, which contains SQL Server database files.

While working with the databases in the "Data Access" samples from the 101 Samples projects, I get the following error:

"Database <mdf_name> cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database..."

I assume the database files were created with a beta or CTP version of SQL Server 2005. Does anyone know where I can get an updated version of these database files or scripts to update them?

Please help.

Mike.

I was getting this error back with the Beta versions when it was updated to Beta 2 and the last of the CTP. This generally means that the database file was created with an older version of the express products and can not go through the automatic upgrade processes. To fix it I had to get the SQL Scripts and re create the database structure.

If you have a look at the mdf name and post that we might be able to direct you to the latest versions, but you might find that if you download the latest samples from the downloads site for the SQL Express product you might get the file you need. More then likley it will be the northwind, pups or adventure works database.

|||

the error is caused by having a database that is an older format, usually beta. I believe the 101 Code Samples have been updated recently to contain release format databases. You can find the download link at http://msdn.microsoft.com/vstudio/downloads/101samples/default.aspx.

Try installing a fresh copy of the samples and see if they work. I can confirm the 'Multiple Active ResultSets' sample is able to load the database without a problem.

Mike

|||

I am having an identical problem with the plants database - used in the XPath/XSLT data sample - I've tried reloading the plants.mdf from both the latest c# & VB.net sample on the VS 2005 101 samples with no luck. The actual error is

Database '9D5A6E69A8EF2325FC6D4339CED21FB2_CS101SAMPLESDATAACCESS\XPATH AND XSLT TRANSFORMATIONS ENHANCEMENTS\XPATHXSL\DATABASE\PLANTS.MDF' cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

|||

I would post this issue to the Feedback Center (http://connect.microsoft.com) for Visual Studio, since this is there sample. VS will need to update the sample as opening the file would require a pre-release build of SQL Server 2005 that is not available.

Regards,

Mike

|||

I note I can work around this by using the equivalent PlantInfo table in the C:\Documents and Settings\dlaub\My Documents\MSDN\CS101SamplesAll\CS101SamplesDataAccess\Using the XML data type with SQL Server 2005\XMLDataType\Database\XMLData.mdf database

You have to tweak the source code a bit (different database name, table name, column names)

And you just create a new table in ANY sql 2005 database - all this table has is an identity column and a xml column

|||

This problem

"cannot be upgraded because its non-release version (600) is not
supported by this version of SQL Server. You cannot open a database that
is incompatible with this version of sqlservr.exe. You must re-create
the database."

still exists in the testdata.mdf database (dated 2006-02-20) shipped with the 'DataReader vs. DataSet comparision' example in the CS101SamplesDataAccess folder of the CS101SamplesAll which I have downloaded several times most recently in April 2007.

I'm running Visual Studio 2005 Standard Edition SP1

Robert Wishlaw

Non-release version (600) is not supported by this version of SQL Server

I downloaded the 101 Samples installation (i.e. 101SamplesCS.msi) from Microsoft's website, which contains SQL Server database files.

While working with the databases in the "Data Access" samples from the 101 Samples projects, I get the following error:

"Database <mdf_name> cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database..."

I assume the database files were created with a beta or CTP version of SQL Server 2005. Does anyone know where I can get an updated version of these database files or scripts to update them?

Please help.

Mike.

I was getting this error back with the Beta versions when it was updated to Beta 2 and the last of the CTP. This generally means that the database file was created with an older version of the express products and can not go through the automatic upgrade processes. To fix it I had to get the SQL Scripts and re create the database structure.

If you have a look at the mdf name and post that we might be able to direct you to the latest versions, but you might find that if you download the latest samples from the downloads site for the SQL Express product you might get the file you need. More then likley it will be the northwind, pups or adventure works database.

|||

the error is caused by having a database that is an older format, usually beta. I believe the 101 Code Samples have been updated recently to contain release format databases. You can find the download link at http://msdn.microsoft.com/vstudio/downloads/101samples/default.aspx.

Try installing a fresh copy of the samples and see if they work. I can confirm the 'Multiple Active ResultSets' sample is able to load the database without a problem.

Mike

|||

I am having an identical problem with the plants database - used in the XPath/XSLT data sample - I've tried reloading the plants.mdf from both the latest c# & VB.net sample on the VS 2005 101 samples with no luck. The actual error is

Database '9D5A6E69A8EF2325FC6D4339CED21FB2_CS101SAMPLESDATAACCESS\XPATH AND XSLT TRANSFORMATIONS ENHANCEMENTS\XPATHXSL\DATABASE\PLANTS.MDF' cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

|||

I would post this issue to the Feedback Center (http://connect.microsoft.com) for Visual Studio, since this is there sample. VS will need to update the sample as opening the file would require a pre-release build of SQL Server 2005 that is not available.

Regards,

Mike

|||

I note I can work around this by using the equivalent PlantInfo table in the C:\Documents and Settings\dlaub\My Documents\MSDN\CS101SamplesAll\CS101SamplesDataAccess\Using the XML data type with SQL Server 2005\XMLDataType\Database\XMLData.mdf database

You have to tweak the source code a bit (different database name, table name, column names)

And you just create a new table in ANY sql 2005 database - all this table has is an identity column and a xml column

|||

This problem

"cannot be upgraded because its non-release version (600) is not
supported by this version of SQL Server. You cannot open a database that
is incompatible with this version of sqlservr.exe. You must re-create
the database."

still exists in the testdata.mdf database (dated 2006-02-20) shipped with the 'DataReader vs. DataSet comparision' example in the CS101SamplesDataAccess folder of the CS101SamplesAll which I have downloaded several times most recently in April 2007.

I'm running Visual Studio 2005 Standard Edition SP1

Robert Wishlaw

Non-release version (600) is not supported by this version of SQL Server

I downloaded the 101 Samples installation (i.e. 101SamplesCS.msi) from Microsoft's website, which contains SQL Server database files.

While working with the databases in the "Data Access" samples from the 101 Samples projects, I get the following error:

"Database <mdf_name> cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database..."

I assume the database files were created with a beta or CTP version of SQL Server 2005. Does anyone know where I can get an updated version of these database files or scripts to update them?

Please help.

Mike.

I was getting this error back with the Beta versions when it was updated to Beta 2 and the last of the CTP. This generally means that the database file was created with an older version of the express products and can not go through the automatic upgrade processes. To fix it I had to get the SQL Scripts and re create the database structure.

If you have a look at the mdf name and post that we might be able to direct you to the latest versions, but you might find that if you download the latest samples from the downloads site for the SQL Express product you might get the file you need. More then likley it will be the northwind, pups or adventure works database.

|||

the error is caused by having a database that is an older format, usually beta. I believe the 101 Code Samples have been updated recently to contain release format databases. You can find the download link at http://msdn.microsoft.com/vstudio/downloads/101samples/default.aspx.

Try installing a fresh copy of the samples and see if they work. I can confirm the 'Multiple Active ResultSets' sample is able to load the database without a problem.

Mike

|||

I am having an identical problem with the plants database - used in the XPath/XSLT data sample - I've tried reloading the plants.mdf from both the latest c# & VB.net sample on the VS 2005 101 samples with no luck. The actual error is

Database '9D5A6E69A8EF2325FC6D4339CED21FB2_CS101SAMPLESDATAACCESS\XPATH AND XSLT TRANSFORMATIONS ENHANCEMENTS\XPATHXSL\DATABASE\PLANTS.MDF' cannot be upgraded because its non-release version (600) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

|||

I would post this issue to the Feedback Center (http://connect.microsoft.com) for Visual Studio, since this is there sample. VS will need to update the sample as opening the file would require a pre-release build of SQL Server 2005 that is not available.

Regards,

Mike

|||

I note I can work around this by using the equivalent PlantInfo table in the C:\Documents and Settings\dlaub\My Documents\MSDN\CS101SamplesAll\CS101SamplesDataAccess\Using the XML data type with SQL Server 2005\XMLDataType\Database\XMLData.mdf database

You have to tweak the source code a bit (different database name, table name, column names)

And you just create a new table in ANY sql 2005 database - all this table has is an identity column and a xml column

|||

This problem

"cannot be upgraded because its non-release version (600) is not
supported by this version of SQL Server. You cannot open a database that
is incompatible with this version of sqlservr.exe. You must re-create
the database."

still exists in the testdata.mdf database (dated 2006-02-20) shipped with the 'DataReader vs. DataSet comparision' example in the CS101SamplesDataAccess folder of the CS101SamplesAll which I have downloaded several times most recently in April 2007.

I'm running Visual Studio 2005 Standard Edition SP1

Robert Wishlaw

Nonprintable / encrypted characters is not found

Hi All

I have a table with one column :

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

The column a is filled with encrypted data
(contains control and extended characters).
On my test, the select statement for one row
in table test2 does not always work sucessfully.

Below is my unsucessful statement for the second
row followed by 9 rows data. Each row is displayed
in 2 versions, text and VB ascii code.

select * from test2
where a = '\0<[\
|^[\]}{;\'

----
Row-1
%;>.[
,)]/\-,=/
37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
Row-2
\0<[\
|^[\]}{;\
92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
Row-3
\0<[\ ,)? {=\?
92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
Row-4
\0<[\ \%:_`- ]_
92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
Row-5
\0<[\ \^:& }{;\
92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
Row-6
\0[*_1>\_\ }{@.+
92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
Row-7
].>^/. ]-=}<)^&
93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
Row-8
]0_^{
}^~_|~{^_
93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
Row-9
{
}{31{2|02~||
123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
----

I have 3 questions for all of you :
- Why SQL2000 can not find the second row (certain row)?
- Can SQL2000 handle character 0 to 255 ?
- Does my encrypted method produce bad data for SQL2000?

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!"Anita" <anonymous@.devdex.com> wrote in message
news:4029728d$0$196$75868355@.news.frii.net...
> Hi All
> I have a table with one column :
> CREATE TABLE test2
> ( a char(15)
> primary KEY CLUSTERED )
> The column a is filled with encrypted data
> (contains control and extended characters).
> On my test, the select statement for one row
> in table test2 does not always work sucessfully.
> Below is my unsucessful statement for the second
> row followed by 9 rows data. Each row is displayed
> in 2 versions, text and VB ascii code.
> select * from test2
> where a = '\0<[\
> |^[\]}{;\'
> ----
> Row-1
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> Row-2
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> Row-3
> \0<[\ ,)? {=\?
> 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> Row-4
> \0<[\ \%:_`- ]_
> 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> Row-5
> \0<[\ \^:& }{;\
> 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> Row-6
> \0[*_1>\_\ }{@.+
> 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> Row-7
> ].>^/. ]-=}<)^&
> 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> Row-8
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> Row-9
> {
> }{31{2|02~||
> 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> ----
> I have 3 questions for all of you :
> - Why SQL2000 can not find the second row (certain row)?
> - Can SQL2000 handle character 0 to 255 ?
> - Does my encrypted method produce bad data for SQL2000?
> Thanks in advance
> Anita Hery
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

1. I don't know, but the most obvious reason is that your search string
doesn't exactly match the encrypted string. Did you enter the SELECT
statement exactly as above? If so, then the issue could be that SQL ignores
whitespace, so you would need something like this to handle the newline:

select * from test2
where a = '\0<[\' + char(10) + '|^[\]}{;\'

2. Yes

3. As long as the encrypted string is in a character set supported by the
server (and you could use Unicode if necessary), then there shouldn't be any
specific issues - it's up to your application to do the
encrpytion/decryption.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:402a7e11$1_3@.news.bluewin.ch...
> "Anita" <anonymous@.devdex.com> wrote in message
> news:4029728d$0$196$75868355@.news.frii.net...
> > Hi All
> > I have a table with one column :
> > CREATE TABLE test2
> > ( a char(15)
> > primary KEY CLUSTERED )
> > The column a is filled with encrypted data
> > (contains control and extended characters).
> > On my test, the select statement for one row
> > in table test2 does not always work sucessfully.
> > Below is my unsucessful statement for the second
> > row followed by 9 rows data. Each row is displayed
> > in 2 versions, text and VB ascii code.
> > select * from test2
> > where a = '\0<[\
> > |^[\]}{;\'
> > ----
> > Row-1
> > %;>.[
> > ,)]/\-,=/
> > 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> > Row-2
> > \0<[\
> > |^[\]}{;\
> > 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> > Row-3
> > \0<[\ ,)? {=\?
> > 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> > Row-4
> > \0<[\ \%:_`- ]_
> > 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> > Row-5
> > \0<[\ \^:& }{;\
> > 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> > Row-6
> > \0[*_1>\_\ }{@.+
> > 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> > Row-7
> > ].>^/. ]-=}<)^&
> > 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> > Row-8
> > ]0_^{
> > }^~_|~{^_
> > 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> > Row-9
> > {
> > }{31{2|02~||
> > 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> > ----
> > I have 3 questions for all of you :
> > - Why SQL2000 can not find the second row (certain row)?
> > - Can SQL2000 handle character 0 to 255 ?
> > - Does my encrypted method produce bad data for SQL2000?
> > Thanks in advance
> > Anita Hery
> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!
> 1. I don't know, but the most obvious reason is that your search string
> doesn't exactly match the encrypted string. Did you enter the SELECT
> statement exactly as above? If so, then the issue could be that SQL
ignores
> whitespace, so you would need something like this to handle the newline:
> select * from test2
> where a = '\0<[\' + char(10) + '|^[\]}{;\'
> 2. Yes
> 3. As long as the encrypted string is in a character set supported by the
> server (and you could use Unicode if necessary), then there shouldn't be
any
> specific issues - it's up to your application to do the
> encrpytion/decryption.
> Simon

Oops - it's not at all correct to say that SQL ignores whitespace; I was
thinking of trailing spaces there for some reason. What may have happened is
that Query Analyzer interpreted your newline as ASCII 13 (carriage return):

select ascii('
')

This gives 13, but you need 10, according to what you posted, so the select
query I suggested above should hopefully be what you need.

Simon|||Simon,

Thanks for your reply.

I show the exact data in ascii code. I am sure the source
of my problem is not about mistyping. I first found the
problem in my VB application. The following is a part of it.

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.OpenRecordset(s, dbOpenDynaset)
For i = 1 To rs.RecordCount
Debug.Print rs!a
X = ""
For j = 1 To Len(rs!a)
X = X & Asc(Mid(rs!a, j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" & rs!a & "'"
Set rs1 = cn.OpenRecordset(s, dbOpenDynaset)
If rs1.RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.movenext
Next
End Sub

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Anita,

I ran the following SQL Script and VBScript and it returned both rows. Does
this work in your environment? In any case, you might consider storing the
encrypted value as binary rather than character data.

CREATE TABLE test2
( a char(15)
primary KEY CLUSTERED )

DECLARE @.Value1 char(15)
SET @.Value1 =
CAST(0x253B3E2E5B0A2C295D2F5C2D2C3D2F AS char(15))
DECLARE @.Value2 char(15)
SET @.Value2 =
CAST(0x5C303C5B5C0A7C5E5B5C5D7D7B3B5C AS char(15))

INSERT INTO test2 VALUES(@.Value1)
INSERT INTO test2 VALUES(@.Value2)

SELECT
CAST(a AS binary(15)),
a FROM test2
WHERE a IN(@.Value1, @.Value2)

' test VBScript
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB.1;" _ &
"Data Source=MyServer;" _ &
"Initial Catalog=MyDatabase;" _ &
"Integrated Security=SSPI"

dotest()
WScript.Echo "Done"

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
Do While rs.EOF = false
WScript.Echo rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields("a"))
X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
Next
WScript.Echo X

s = "select * from test2 where a = '" & rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.RecordCount = 0 Then
WScript.Echo "==not found=="
End If
rs.movenext
Loop
End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:402aaa48$0$199$75868355@.news.frii.net...
> Simon,
> Thanks for your reply.
> I show the exact data in ascii code. I am sure the source
> of my problem is not about mistyping. I first found the
> problem in my VB application. The following is a part of it.
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.OpenRecordset(s, dbOpenDynaset)
> For i = 1 To rs.RecordCount
> Debug.Print rs!a
> X = ""
> For j = 1 To Len(rs!a)
> X = X & Asc(Mid(rs!a, j, 1)) & ";"
> Next
> Debug.Print X
> s = "select * from test2 where a = '" & rs!a & "'"
> Set rs1 = cn.OpenRecordset(s, dbOpenDynaset)
> If rs1.RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.movenext
> Next
> End Sub
> Anita Hery
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan Guzman,

Thanks for your reply.

Currently I am working with DAO library / ODBC.
I am not familiar with VB script. So, I tried
using ADO library 2.7 and made a small modification
like this:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim qd As New ADODB.Command
Dim s As String

Private Sub Form_Load()
cn.ConnectionString = "PROVIDER=SQLOLEDB;
SERVER=srv2003;UID=sqlreg;PWD=cas;DATABASE=cas1"
cn.Open
dotest
End Sub

Sub dotest()
Dim i, X, j, s
s = "select * from test2"
Set rs = cn.Execute(s)
i = 0
Do While rs.EOF = False
i = i + 1
Debug.Print "Row " & i & " ----"
Debug.Print rs.Fields("a")
X = ""
For j = 1 To Len(rs.Fields("a"))
X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
Next
Debug.Print X

s = "select * from test2 where a = '" &
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
If rs1.EOF Then ' .RecordCount = 0 Then
Debug.Print "==not found=="
End If
rs.MoveNext
Loop
End Sub

If I use my original data, the above code will
produce the same problem on the same row (row 2).
Below is the output list:

====================
Row 1 ----
%;>.[
,)]/\-,=/
37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
Row 2 ----
\0<[\
|^[\]}{;\
92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
==not found==
Row 3 ----
\0<[\ ,)? {=\?
92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
Row 4 ----
\0<[\ \%:_`- ]_
92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
Row 5 ----
\0<[\ \^:& }{;\
92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
Row 6 ----
\0[*_1>\_\ }{@.+
92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
Row 7 ----
].>^/. ]-=}<)^&
93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
Row 8 ----
]0_^{
}^~_|~{^_
93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
Row 9 ----
{
}{31{2|02~||
123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
=======================

The row 2 can be accessed by the query like this:

select * from test2
where a =
char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
char(123)+char(59)+char(92)

But, I will not use this way.

Anita

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi, Anita.

I was able to recreate your problem and narrowed it down to the
backslash/newline sequence in the character string. As a workaround, I used
a parameter rather than an embedded literal value. This technique also
eliminates the need to escape other special characters like single quotes.
Example below.

Dim cmd As New ADODB.Command
Dim parm1 As ADODB.parameter
cmd.CommandText = "SELECT * FROM test2 WHERE a = ?"
cmd.ActiveConnection = cn
Set parm1 = cmd.CreateParameter("@.Parm1", adChar, 1, 15, s)
cmd.Parameters.Append parm1
cmd.Execute

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:402b81e0$0$202$75868355@.news.frii.net...
> Dan Guzman,
> Thanks for your reply.
> Currently I am working with DAO library / ODBC.
> I am not familiar with VB script. So, I tried
> using ADO library 2.7 and made a small modification
> like this:
> Dim cn As New ADODB.Connection
> Dim rs As New ADODB.Recordset
> Dim qd As New ADODB.Command
> Dim s As String
> Private Sub Form_Load()
> cn.ConnectionString = "PROVIDER=SQLOLEDB;
> SERVER=srv2003;UID=sqlreg;PWD=cas;DATABASE=cas1"
> cn.Open
> dotest
> End Sub
> Sub dotest()
> Dim i, X, j, s
> s = "select * from test2"
> Set rs = cn.Execute(s)
> i = 0
> Do While rs.EOF = False
> i = i + 1
> Debug.Print "Row " & i & " ----"
> Debug.Print rs.Fields("a")
> X = ""
> For j = 1 To Len(rs.Fields("a"))
> X = X & Asc(Mid(rs.Fields("a"), j, 1)) & ";"
> Next
> Debug.Print X
> s = "select * from test2 where a = '" &
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> If rs1.EOF Then ' .RecordCount = 0 Then
> Debug.Print "==not found=="
> End If
> rs.MoveNext
> Loop
> End Sub
> If I use my original data, the above code will
> produce the same problem on the same row (row 2).
> Below is the output list:
> ====================
> Row 1 ----
> %;>.[
> ,)]/\-,=/
> 37;59;62;46;91;10;44;41;93;47;92;45;44;61;47;
> Row 2 ----
> \0<[\
> |^[\]}{;\
> 92;48;60;91;92;10;124;94;91;92;93;125;123;59;92;
> ==not found==
> Row 3 ----
> \0<[\ ,)? {=\?
> 92;48;60;91;92;11;44;41;63;3;123;127;61;92;63;
> Row 4 ----
> \0<[\ \%:_`- ]_
> 92;48;60;91;92;11;92;37;58;95;96;45;5;93;95;
> Row 5 ----
> \0<[\ \^:& }{;\
> 92;48;60;91;92;11;92;94;58;38;7;125;123;59;92;
> Row 6 ----
> \0[*_1>\_\ }{@.+
> 92;48;91;42;95;49;62;92;95;92;7;125;123;64;43;
> Row 7 ----
> ].>^/. ]-=}<)^&
> 93;46;62;94;47;46;8;93;45;61;125;60;41;94;38;
> Row 8 ----
> ]0_^{
> }^~_|~{^_
> 93;48;95;94;123;10;125;94;126;95;124;126;123;94;95 ;
> Row 9 ----
> {
> }{31{2|02~||
> 123;10;125;123;127;51;49;123;50;124;48;50;126;124; 124;
> =======================
> The row 2 can be accessed by the query like this:
> select * from test2
> where a =
> char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
> char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
> char(123)+char(59)+char(92)
> But, I will not use this way.
> Anita
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Dan,

You are absolutely right.

I think my problem is about query translation
on client side (VB). Perhaps, it is a bug
from Microsoft.

If the query is sent using direct method, like :
s = "select * from test2 where a = '" & _
rs.Fields("a") & "'"
Set rs1 = cn.Execute(s)
then it is not guaranteed to work.

I made QA trial to prove that my problem was not
caused by SQL2000 :

CREATE TABLE test3
( a char(15)
primary KEY CLUSTERED,
b char(1) )

INSERT INTO test3
SELECT a, '2' as b FROM test2

--set b='1' on the second row
UPDATE test3 SET b = '1'
WHERE a =
char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
char(123)+char(59)+char(92)

DECLARE @.Value1 char(15)

SELECT @.value1 = a
FROM test3 where b = '1'

--successful second row access
SELECT * FROM test3 where a = @.Value1

Thanks for your reply

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||SQL Server MVP Steve Kass pointed out to me that this behavior is described
in MSKB 164291
<http://support.microsoft.com/default.aspx?scid=kb;en-us;164291>.
Basically, SQL Server interprets a backslash followed a newline as a literal
continuation escape sequence so these characters are ignored in the literal
string. You can repro this in Query Analyzer with the following script:

SELECT 'Continu\
ed string'
GO

Although this behavior is also described in the Books Online in the Embedded
SQL for C section <esqlforc.chm::/ec_6_epr_02_101f.htm>, it is not mentioned
elsewhere as it probably should. A doc bug has been filed so the
documentation issue should be addressed in the future.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Anita" <anonymous@.devdex.com> wrote in message
news:403007ad$0$195$75868355@.news.frii.net...
> Dan,
> You are absolutely right.
> I think my problem is about query translation
> on client side (VB). Perhaps, it is a bug
> from Microsoft.
> If the query is sent using direct method, like :
> s = "select * from test2 where a = '" & _
> rs.Fields("a") & "'"
> Set rs1 = cn.Execute(s)
> then it is not guaranteed to work.
> I made QA trial to prove that my problem was not
> caused by SQL2000 :
> CREATE TABLE test3
> ( a char(15)
> primary KEY CLUSTERED,
> b char(1) )
> INSERT INTO test3
> SELECT a, '2' as b FROM test2
> --set b='1' on the second row
> UPDATE test3 SET b = '1'
> WHERE a =
> char(92)+char(48)+char(60)+char(91)+char(92)+char( 10)+
> char(124)+char(94)+char(91)+char(92)+char(93)+char (125)+
> char(123)+char(59)+char(92)
> DECLARE @.Value1 char(15)
> SELECT @.value1 = a
> FROM test3 where b = '1'
> --successful second row access
> SELECT * FROM test3 where a = @.Value1
> Thanks for your reply
> Anita Hery
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

non-existent images

Hi,
I have a database that contains student-id's, and I created a report
that shows the student ID along with a fotograph of the student. The
picture is fetched from a website, and is not stored in the database.
Problem:
Not all students have a fotograph. If no picture exists, a red cross is
shown on the output. Can this red cross be eliminated, or can the
picture be hidden when there's an invalid URL for the image source?
Tnx,
E.T.You could create a small 1x1 pixel gif and upload to your server.
THen for the image tag, write an expression like
=IIF(IsNothing(Field!PictureURL.Value), "mysmallgif.gif",
field!PictureURL.Value)
That way you will display the gif if the url is invalid. You'll have to
tweak the code to fit your fields etc, but the concept should work.
Kaisa M. Lindahl Lervik
"Erik Thijs" <ErikdotThijs@.removethis.khkdotbe> wrote in message
news:esytuq38GHA.4964@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a database that contains student-id's, and I created a report that
> shows the student ID along with a fotograph of the student. The picture is
> fetched from a website, and is not stored in the database.
> Problem:
> Not all students have a fotograph. If no picture exists, a red cross is
> shown on the output. Can this red cross be eliminated, or can the picture
> be hidden when there's an invalid URL for the image source?
> Tnx,
> E.T.|||Kaisa M. Lindahl Lervik schreef:
> You could create a small 1x1 pixel gif and upload to your server.
> THen for the image tag, write an expression like
> =IIF(IsNothing(Field!PictureURL.Value), "mysmallgif.gif",
> field!PictureURL.Value)
forgot to mention:
The url of the picture is generated in RS, not in the database, based on
the studentID.
For instance, a student has studentID stud0001, then the url for the
image in reporting services becomes:
Image1.Value="http://fotoserver/" & Fields!studentId.Value & ".jpg"
So every student has a picture with a URL value. The question is how to
detect invalid URL's...|||How do you store the images on the server? Do you upload images when you
register the data about your student, or do you have to upload the picture
after you know the StudentID?
If you do it when registering, you might be able to change your db to save
wether or not a picture has been uploaded.
If "studentpicturesaved = true", then Image1.Value = http://fotoserver &
Fields!studentID.Value & ".jpg" else Image1.Value =http://fotoserver.dummypic.gif
Or else you have to access the file server to check if a picture with that
ID is there, which is fussy at best. I'm pretty sure there's no way of
checking if a image source url is valid just before the image is being
rendered on the screen. You have to register if there's a picture in the
database.
Kaisa M. Lindahl Lervik
"Erik Thijs" <ErikdotThijs@.removethis.khkdotbe> wrote in message
news:OTt$zE48GHA.4348@.TK2MSFTNGP03.phx.gbl...
> Kaisa M. Lindahl Lervik schreef:
>> You could create a small 1x1 pixel gif and upload to your server.
>> THen for the image tag, write an expression like
>> =IIF(IsNothing(Field!PictureURL.Value), "mysmallgif.gif",
>> field!PictureURL.Value)
> forgot to mention:
> The url of the picture is generated in RS, not in the database, based on
> the studentID.
> For instance, a student has studentID stud0001, then the url for the image
> in reporting services becomes:
> Image1.Value="http://fotoserver/" & Fields!studentId.Value & ".jpg"
> So every student has a picture with a URL value. The question is how to
> detect invalid URL's...

NonClustered Index Corruption

Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
JerryFirst off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
>> and DBCC CHECKDB (doing both to make sure something else isn't causing
>> the issue) - both hung again (over 5 hours yesterday). Dropping the NC
>> index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that mean?
>> Were they being blocked? Was the spid doing anything? Is the NCI on a
>> single column, computed etc? In any case if you have hardware corruption
>> going on it will usually manifest itself in the most active process.
>> Creating an index on a 150M row table gives a lot of opportunity for
>> corruption if the hardware is suspect. If you have bad disk sectors then
>> creating the index again might very well use the same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
>> the NC index is on a single column - no it is not a computed column.
>> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
>> never completes and has to be stopped manually. It could be a hardware
>> issue (RAID 5). What operations would you suggest to determine if this
>> could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that mean?
>> Were they being blocked? Was the spid doing anything? Is the NCI on a
>> single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table that
>> contains over 150 million records. After determining that the NC index
>> was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original
>> post the NC index is on a single column - no it is not a computed
>> column. Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6
>> hours and never completes and has to be stopped manually. It could be a
>> hardware issue (RAID 5). What operations would you suggest to determine
>> if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the NC
>> index was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
--
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>> Jerry,
>> Sorry I didn't catch the single column. What level of DBCC did you run?
>> Try with the Physical_Only option first just to see if it goes through.
>> In any case unless it is blocked it should not just sit there doing
>> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
>> would have expected some errors if it were disk related. I would run
>> chkdisk to be sure anyway when you can. Next time you try it take a look
>> at the SPID in sysprocesses to see how much I/O CPU etc. it used before
>> it "hangs".
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat
>> DBCC CHECKDB includes DBCC CHECKTABLE. Again as I stated in the
>> original post the NC index is on a single column - no it is not a
>> computed column. Hung? A 20 minute operations (DBCC CHECKDB) takes
>> more than 6 hours and never completes and has to be stopped manually.
>> It could be a hardware issue (RAID 5). What operations would you
>> suggest to determine if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have
>> hardware corruption going on it will usually manifest itself in the
>> most active process. Creating an index on a 150M row table gives a lot
>> of opportunity for corruption if the hardware is suspect. If you have
>> bad disk sectors then creating the index again might very well use the
>> same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the
>> NC index was the cause, I removed the NC index and reran the DBCC
>> CHECKDB statement. The results were no errors after 20 minutes.
>> Today I recreated the NC index (single column). I reran both the
>> DBCC CHECKTABLE and DBCC CHECKDB (doing both to make sure something
>> else isn't causing the issue) - both hung again (over 5 hours
>> yesterday). Dropping the NC index again and the checks work fine.
>> Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>>
>|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> Andrew,
>> No worries. I was running DBCC CHECKDB (with the default options). I
>> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
>> statment. This time the statement returned in a timely manner with no
>> errors reported. I'm thinking the drive(s). Does the SQL Server service
>> deed to be stopped before I have the network guy run the chkdsk command?
>> Thanks again.
>> Jerry
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>> Jerry,
>> Sorry I didn't catch the single column. What level of DBCC did you run?
>> Try with the Physical_Only option first just to see if it goes through.
>> In any case unless it is blocked it should not just sit there doing
>> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
>> would have expected some errors if it were disk related. I would run
>> chkdisk to be sure anyway when you can. Next time you try it take a
>> look at the SPID in sysprocesses to see how much I/O CPU etc. it used
>> before it "hangs".
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat
>> DBCC CHECKDB includes DBCC CHECKTABLE. Again as I stated in the
>> original post the NC index is on a single column - no it is not a
>> computed column. Hung? A 20 minute operations (DBCC CHECKDB) takes
>> more than 6 hours and never completes and has to be stopped manually.
>> It could be a hardware issue (RAID 5). What operations would you
>> suggest to determine if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have
>> hardware corruption going on it will usually manifest itself in the
>> most active process. Creating an index on a 150M row table gives a
>> lot of opportunity for corruption if the hardware is suspect. If you
>> have bad disk sectors then creating the index again might very well
>> use the same space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>>> Hi,
>>>
>>> Yesterday I had some corruption in a nonclustered index on a table
>>> that contains over 150 million records. After determining that the
>>> NC index was the cause, I removed the NC index and reran the DBCC
>>> CHECKDB statement. The results were no errors after 20 minutes.
>>> Today I recreated the NC index (single column). I reran both the
>>> DBCC CHECKTABLE and DBCC CHECKDB (doing both to make sure something
>>> else isn't causing the issue) - both hung again (over 5 hours
>>> yesterday). Dropping the NC index again and the checks work fine.
>>> Any ideas?
>>>
>>> Thanks
>>>
>>> Jerry
>>>
>>
>>
>>
>>
>>
>|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>> The Disk Management tool shows the logical drive as "Healthy". The
>> system log in the Event Viewer shows no HW releated disk errors. I
>> checked the RAID tool on the server and no issues/errors were reported.
>> The network admin says that we will need to wait til afterhours to run
>> chkdsk. Other suggestions?
>> Thanks.
>> Jerry
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>> Andrew,
>> As I said in the original post I was running both to ensure that some
>> other table/index etc... was not causing the issue...yes I knowthat DBCC
>> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original
>> post the NC index is on a single column - no it is not a computed
>> column. Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6
>> hours and never completes and has to be stopped manually. It could be a
>> hardware issue (RAID 5). What operations would you suggest to determine
>> if this could be a HW issue?
>> Thanks
>> Jerry
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>> First off there is no need to run Checktable if you run CheckDB since
>> that includes it. You say they were hung. What exactly does that
>> mean? Were they being blocked? Was the spid doing anything? Is the
>> NCI on a single column, computed etc? In any case if you have hardware
>> corruption going on it will usually manifest itself in the most active
>> process. Creating an index on a 150M row table gives a lot of
>> opportunity for corruption if the hardware is suspect. If you have bad
>> disk sectors then creating the index again might very well use the same
>> space on the drive.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>> Hi,
>> Yesterday I had some corruption in a nonclustered index on a table
>> that contains over 150 million records. After determining that the NC
>> index was the cause, I removed the NC index and reran the DBCC CHECKDB
>> statement. The results were no errors after 20 minutes. Today I
>> recreated the NC index (single column). I reran both the DBCC
>> CHECKTABLE and DBCC CHECKDB (doing both to make sure something else
>> isn't causing the issue) - both hung again (over 5 hours yesterday).
>> Dropping the NC index again and the checks work fine. Any ideas?
>> Thanks
>> Jerry
>>
>>
>>
>

Non significant '0' removed from alphanumeric code

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

Double check all of your metadata links and double check on the advanced editor screens that you don't inadvertently have that column cast as a numeric data type.|||Already done twice! ;-)|||

Sbastien Nunes wrote:

Already done twice! ;-)

Then I'm missing something. A DT_WSTR will not drop leading zeros.|||Are you using SQL in your lookup transformation?|||Yes I am.|||

Sebastion,

Did you get this resolved?

Did you try using data viewers across the Data flow to see at what point the leading zero gets removed...what type of destination are you using...may the probelm be there?

|||

Hi,

No it's not resolved. Yes I tried using Data Viewers, the column where the 0 is removed is an output column from a Lookup Transformation. The Lookup is based on a SQL query.

I have checked the MetaData and everything seems allright.

Thanks for your help.

|||

That is weird...

What type of OLE DB source you are using in the lookup transform?

Did you get same results when running the same query in a native query tool (e.g. SSMS if SQL Server)?

What happens if you try to explicitly cast that column to string type in the lookup query?

|||

Thanks for your answer Rafael, I didn't had the time to test that today, I'll try on monday and I'll let you know what happens.

Non significant '0' removed from alphanumeric code

Hi all,

I have a problem with alphanumeric codes in SSIS.

I have a sql table with a varchar column which contains codes like '080101000', in my SSIS dataflow I have a lookup against this table and the column whith the code is used as output column for my lookup transformation.

In the advance editor the output column datatype is DT_WSTR, but when the code contains only numbers like the code '080101000' the first '0' is removed! It's like the code is at some point transformed to numeric and then inserted in the output column as a string. This in nonsense!!

Does anyone have an idea how to avoid this ?

Double check all of your metadata links and double check on the advanced editor screens that you don't inadvertently have that column cast as a numeric data type.|||Already done twice! ;-)|||

Sbastien Nunes wrote:

Already done twice! ;-)

Then I'm missing something. A DT_WSTR will not drop leading zeros.|||Are you using SQL in your lookup transformation?|||Yes I am.|||

Sebastion,

Did you get this resolved?

Did you try using data viewers across the Data flow to see at what point the leading zero gets removed...what type of destination are you using...may the probelm be there?

|||

Hi,

No it's not resolved. Yes I tried using Data Viewers, the column where the 0 is removed is an output column from a Lookup Transformation. The Lookup is based on a SQL query.

I have checked the MetaData and everything seems allright.

Thanks for your help.

|||

That is weird...

What type of OLE DB source you are using in the lookup transform?

Did you get same results when running the same query in a native query tool (e.g. SSMS if SQL Server)?

What happens if you try to explicitly cast that column to string type in the lookup query?

|||

Thanks for your answer Rafael, I didn't had the time to test that today, I'll try on monday and I'll let you know what happens.