sql rowcount: type

Showing posts with label type. Show all posts

Thursday, March 22, 2012

_hypmv error

I just tried to update a column type from varchar(32) to varchar(64) in a
table,but got some dependency error like:
The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
But,in the dependency listing for the table I cant view any of the above,but
I have
got admin access.What are these dependecies for?any pointer will be greatly
appreciated.
A wild guess is that someone has been running Index Tuning izard which has created "Hypothetical
Materialized Views" (called Indexed Views), and these from some reason haven't been dropped. I'd
check if these are views and if your applications aren't using these, consider dropping them, using
DROP VIEW command.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:53B89EB6-C82A-49B2-96C2-84A98CB02FC0@.microsoft.com...
>I just tried to update a column type from varchar(32) to varchar(64) in a
> table,but got some dependency error like:
> The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
> But,in the dependency listing for the table I cant view any of the above,but
> I have
> got admin access.What are these dependecies for?any pointer will be greatly
> appreciated.
sql

_hypmv error

I just tried to update a column type from varchar(32) to varchar(64) in a
table,but got some dependency error like:
The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
But,in the dependency listing for the table I cant view any of the above,but
I have
got admin access.What are these dependecies for?any pointer will be greatly
appreciated.A wild guess is that someone has been running Index Tuning izard which has created "Hypothetical
Materialized Views" (called Indexed Views), and these from some reason haven't been dropped. I'd
check if these are views and if your applications aren't using these, consider dropping them, using
DROP VIEW command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"c_ani" <cani@.discussions.microsoft.com> wrote in message
news:53B89EB6-C82A-49B2-96C2-84A98CB02FC0@.microsoft.com...
>I just tried to update a column type from varchar(32) to varchar(64) in a
> table,but got some dependency error like:
> The object '_hypmv_0' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_9625' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_7898' is dependent on column 'EMAILREFERENCE'.
> The object '_hypmv_0_6539' is dependent on column 'EMAILREFERENCE'.
> But,in the dependency listing for the table I cant view any of the above,but
> I have
> got admin access.What are these dependecies for?any pointer will be greatly
> appreciated.

Sunday, March 11, 2012

[SQL 2005] SQLCLR UDT Comparison Methods

OK, so I'm creating a simple UDT. I want to expose a method that performs a
comparison of two objects of the type of the UDT and returns a "boolean"
value based on the result that SQL Server can use in T-SQL statements and
expressions. I understand you can't override comparison operators like = and <>. Ideally I'd like to achieve something like the following:
DECLARE @.x MyUDT
DECLARE @.y MyUDT
SELECT @.x = 'Test Value'
SELECT @.y = 'Test Value'
IF @.x.IsEqual(@.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Or alternatively:
IF MyUDT::IsEqual(@.x, @.y)
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Anyone done this, or can point me to someplace with a code sample?
Thanks in advance.Almost forgot to mention. IsByteOrdered is not really an option, since this
particular type is an unordered space and <, > operators make no sense on
it. = and <> are valid operations on this type.
Thanks
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like => and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>|||Mike
You want to compare two values , is it possible that
SELECT @.x = 'Test value'
SELECT @.y = 'Test Value'
So , you need to PRINT 'Not Equal' , am I right?
Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
SELECT a.ID, a.CheckSum
From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsA ) a
Inner Join (
Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
FROM dbo.authorsB ) b
On a.ID = b.ID
Where a.CheckSum != b.CheckSum
"Mike C#" <xyz@.xyz.com> wrote in message
news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
> OK, so I'm creating a simple UDT. I want to expose a method that performs
> a comparison of two objects of the type of the UDT and returns a "boolean"
> value based on the result that SQL Server can use in T-SQL statements and
> expressions. I understand you can't override comparison operators like => and <>. Ideally I'd like to achieve something like the following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Or alternatively:
> IF MyUDT::IsEqual(@.x, @.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
> Anyone done this, or can point me to someplace with a code sample?
> Thanks in advance.
>|||Uri,
I want to do it in a SQLCLR UDT by exposing a comparison method. I could
avoid the checksum functions and just use IF @.x.ToString = @.y.ToString
directly; or create a UDF to do the comparison. I'd rather expose a method
of the UDT so I can encapsulate the logic all in one spot and avoid
conversions and additional calculations/manipulations.
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23vrKJQaIHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Mike
> You want to compare two values , is it possible that
> SELECT @.x = 'Test value'
> SELECT @.y = 'Test Value'
>
> So , you need to PRINT 'Not Equal' , am I right?
>
> Perhaps you can look at BINARY_CHECKSUM(*) in the BOL for comparison
>
> SELECT a.ID, a.CheckSum
> From (Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsA ) a
> Inner Join (
> Select au_id as "ID", BINARY_CHECKSUM(*) as "CheckSum"
> FROM dbo.authorsB ) b
> On a.ID = b.ID
> Where a.CheckSum != b.CheckSum
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:eq21xCaIHHA.960@.TK2MSFTNGP04.phx.gbl...
>> OK, so I'm creating a simple UDT. I want to expose a method that
>> performs a comparison of two objects of the type of the UDT and returns a
>> "boolean" value based on the result that SQL Server can use in T-SQL
>> statements and expressions. I understand you can't override comparison
>> operators like = and <>. Ideally I'd like to achieve something like the
>> following:
>> DECLARE @.x MyUDT
>> DECLARE @.y MyUDT
>> SELECT @.x = 'Test Value'
>> SELECT @.y = 'Test Value'
>> IF @.x.IsEqual(@.y)
>> PRINT 'Equal'
>> ELSE
>> PRINT 'Not Equal'
>> Or alternatively:
>> IF MyUDT::IsEqual(@.x, @.y)
>> PRINT 'Equal'
>> ELSE
>> PRINT 'Not Equal'
>> Anyone done this, or can point me to someplace with a code sample?
>> Thanks in advance.
>|||Mike C# (xyz@.xyz.com) writes:
> OK, so I'm creating a simple UDT. I want to expose a method that
> performs a comparison of two objects of the type of the UDT and returns
> a "boolean" value based on the result that SQL Server can use in T-SQL
> statements and expressions. I understand you can't override comparison
> operators like = and <>. Ideally I'd like to achieve something like the
> following:
> DECLARE @.x MyUDT
> DECLARE @.y MyUDT
> SELECT @.x = 'Test Value'
> SELECT @.y = 'Test Value'
> IF @.x.IsEqual(@.y)
> PRINT 'Equal'
> ELSE
> PRINT 'Not Equal'
Apart from that the syntax most certainly would have to be
IF @.x.IsEqual(@.y) = 1
what is really your problem?
In the Point sample in Books Online, there is a method Distance where the
input is another point. See
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f535e2ac722.htm
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989CD80E49468Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> OK, so I'm creating a simple UDT. I want to expose a method that
>> performs a comparison of two objects of the type of the UDT and returns
>> a "boolean" value based on the result that SQL Server can use in T-SQL
>> statements and expressions. I understand you can't override comparison
>> operators like = and <>. Ideally I'd like to achieve something like the
>> following:
>> DECLARE @.x MyUDT
>> DECLARE @.y MyUDT
>> SELECT @.x = 'Test Value'
>> SELECT @.y = 'Test Value'
>> IF @.x.IsEqual(@.y)
>> PRINT 'Equal'
>> ELSE
>> PRINT 'Not Equal'
> Apart from that the syntax most certainly would have to be
> IF @.x.IsEqual(@.y) = 1
> what is really your problem?
My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
returns or compares a boolean value. My problem is that there appears to be
no way to use a SqlBoolean return value from a method in an IF statement. I
want to know if that is correct or not. Is SqlBoolean of any use whatsoever
in the context of a UDT?
> In the Point sample in Books Online, there is a method Distance where the
> input is another point. See
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/1e5b43b3-4971-45ee-a591-3f535e2ac722.htm
Unfortunately I'm not interested in returning a numeric result of a
calculation on two UDTs. I'm interested in comparing two UDT's that are not
byte-ordered for equality. I notice that the MS Point sample is
byte-ordered; does this indicate that a point (X, Y+1000000) is actually
"less than" the point (X+1, Y-100000)? I noticed in my UDT that when I
remove byte-ordering (since byte-ordering is inapplicable in my case), I
cannot use the T-SQL "=" comparison operator on two variables of the UDT's
type. The UDT is Format.Native.
If you decide for some reason that "less than" and "greater than" have no
meaning in regards to your UDT, and remove the byte-ordering, what's the
best way to test them for equality/inequality? Is returning arbitrary "flag
values" from methods and comparing those flag values for equality to
constants the only way? Is there no way to return a boolean value (aka,
SqlBoolean) and use it in an IF statement?|||Mike C# (xyz@.xyz.com) writes:
> My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
> returns or compares a boolean value. My problem is that there appears
> to be no way to use a SqlBoolean return value from a method in an IF
> statement.
There is:
IF @.x.IsEqual(@.y) = 1
Of if you prefer:
IF @.x.IsEqual(@.y) = 'true'
SqlBoolean is the .Net correspondence to the T-SQL data type bit, and this
how you use bit in T-SQL. T-SQL is funny, because while it has boolean
expressions, it does not have a boolean data type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Mike,
> Unfortunately I'm not interested in returning a numeric result of a
> calculation on two UDTs. I'm interested in comparing two UDT's that are
> not byte-ordered for equality. I notice that the MS Point sample is
> byte-ordered; does this indicate that a point (X, Y+1000000) is actually
> "less than" the point (X+1, Y-100000)? I noticed in my UDT that when I
> remove byte-ordering (since byte-ordering is inapplicable in my case), I
> cannot use the T-SQL "=" comparison operator on two variables of the UDT's
> type. The UDT is Format.Native.
> If you decide for some reason that "less than" and "greater than" have no
> meaning in regards to your UDT, and remove the byte-ordering, what's the
> best way to test them for equality/inequality? Is returning arbitrary
> "flag values" from methods and comparing those flag values for equality to
> constants the only way? Is there no way to return a boolean value (aka,
> SqlBoolean) and use it in an IF statement?
How about still using byte ordering, but implemented in your own way? You
could implement IBinarySerialize interface in your UDT and then in the Write
method write additional 8 bytes in the beginning of the serialized value.
You can calculate the value in this 8 bytes anyway you like, so you can
define also what is greater than and what is lower thaan, not just equal.
When you deserialize a value from this UDT, i.e. in the Read method, just
skip these first 8 bytes.
--
Dejan Sarka
http://www.solidqualitylearning.com/blogs/|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:uCbKXtrIHHA.5000@.TK2MSFTNGP03.phx.gbl...
> How about still using byte ordering, but implemented in your own way? You
> could implement IBinarySerialize interface in your UDT and then in the
> Write method write additional 8 bytes in the beginning of the serialized
> value. You can calculate the value in this 8 bytes anyway you like, so you
> can define also what is greater than and what is lower thaan, not just
> equal. When you deserialize a value from this UDT, i.e. in the Read
> method, just skip these first 8 bytes.
Dejan, in some instances greater than and less than are meaningless. This
is the case here. Equality and inequality, however, have meaning. I would
like to get the functionality of equality and inequality without less than,
greater than, etc.
Thanks.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989D60A6568D6Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> My problem really is that IF @.x.IsEqual(@.y) = 1 is not a function that
>> returns or compares a boolean value. My problem is that there appears
>> to be no way to use a SqlBoolean return value from a method in an IF
>> statement.
> There is:
> IF @.x.IsEqual(@.y) = 1
> Of if you prefer:
> IF @.x.IsEqual(@.y) = 'true'
> SqlBoolean is the .Net correspondence to the T-SQL data type bit, and this
> how you use bit in T-SQL. T-SQL is funny, because while it has boolean
> expressions, it does not have a boolean data type.
You know what it boils down to? They need to allow you to override T-SQL
operators for UDT's, plain and simple. For all that, I may as well just
write IF CAST(@.x AS VARBINARY) = CAST(@.y AS VARBINARY). I was hoping taht
(as a next best option), there would be a way to treat a .NET method as a
boolean expression, but that appears to not be the case. Looks like it's
just a matter of returning a value and arbitrarily assigning meaning to it.
Thanks.|||Mike C# (xyz@.xyz.com) writes:
> You know what it boils down to? They need to allow you to override T-SQL
> operators for UDT's, plain and simple.
Because you would wear out your fingers if you have to type "= 1"? C'mon,
I know have C# in your alias, but you are not in Kansas anymore. When in
Rome, do as the Romans.
But if you feel strongly about it, http://connect.microsoft.com is where
you can submit suggestions for enhancements.
> For all that, I may as well just write IF CAST(@.x AS VARBINARY) => CAST(@.y AS VARBINARY).
Yes, if your equality check is that simple. The above would not work for
some of T-SQL data types in some collations.
> I was hoping taht (as a next best option), there would be a way to treat
> a .NET method as a boolean expression, but that appears to not be the
> case. Looks like it's just a matter of returning a value and
> arbitrarily assigning meaning to it.
You would have precisely the same issue with a UDF that returns 0 or 1.
Or for that matter a bit column in a table which represents something
that is off or on.
Why would you need a special syntax just because it's a .Net UDT?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989DB977C7FECYazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> You know what it boils down to? They need to allow you to override T-SQL
>> operators for UDT's, plain and simple.
> Because you would wear out your fingers if you have to type "= 1"? C'mon,
> I know have C# in your alias, but you are not in Kansas anymore. When in
> Rome, do as the Romans.
Simple consistency. You use "=1" as the status for a match, Harry uses
"=0", Linda uses "='True'", Gorky from B.F.E. Sweden uses "='Y'". BTW,
haven't been to Kansas myself and just in case you didn't get the memo, the
Romans did as the Romans do while in Rome, and the Roman Empire went bye-bye
for precisely that reason.
> But if you feel strongly about it, http://connect.microsoft.com is where
> you can submit suggestions for enhancements.
Fell strongly? Not hardly. I was just asking a question - "Is it
possible?" Apparently not. That's the end of that as far as I'm concerned.
>> For all that, I may as well just write IF CAST(@.x AS VARBINARY) =>> CAST(@.y AS VARBINARY).
> Yes, if your equality check is that simple. The above would not work for
> some of T-SQL data types in some collations.
Just wondering what "some of T-SQL data types in some collations" has to do
with comparing the binary representation of a SQLCLR UDT?
>> I was hoping taht (as a next best option), there would be a way to treat
>> a .NET method as a boolean expression, but that appears to not be the
>> case. Looks like it's just a matter of returning a value and
>> arbitrarily assigning meaning to it.
> You would have precisely the same issue with a UDF that returns 0 or 1.
> Or for that matter a bit column in a table which represents something
> that is off or on.
Consistency.
> Why would you need a special syntax just because it's a .Net UDT?
How's this for special syntax?
DECLARE @.m1 MyUDT
DECLARE @.m2 MyUDT
IF @.m1 = @.m2
PRINT 'Equal'
ELSE
PRINT 'Not Equal'
Unforunately (as I said previously) it doesn't seem to work when the Byte
Ordering attribute is set to false. It would appear that the Romans feel if
two items can't be sorted, then neither can they be equal.|||Mike C# (xyz@.xyz.com) writes:
> Simple consistency.
Why then ask for a special feature for .Net UDT methods?
> You use "=1" as the status for a match, Harry uses
> "=0", Linda uses "='True'", Gorky from B.F.E. Sweden uses "='Y'".
Using bit to represent boolean values with 1 for truth is quite standard
in the T-SQL world. So why should .Net UDT methods be special?
>> For all that, I may as well just write IF CAST(@.x AS VARBINARY) =>> CAST(@.y AS VARBINARY).
>> Yes, if your equality check is that simple. The above would not work for
>> some of T-SQL data types in some collations.
> Just wondering what "some of T-SQL data types in some collations" has to
> do with comparing the binary representation of a SQLCLR UDT?
As an example that equality is not always possible by byte-comparing,
even if it might be in your case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns989E6F414054Yazorman@.127.0.0.1...
> Mike C# (xyz@.xyz.com) writes:
>> Simple consistency.
> Why then ask for a special feature for .Net UDT methods?
>> You use "=1" as the status for a match, Harry uses
>> "=0", Linda uses "='True'", Gorky from B.F.E. Sweden uses "='Y'".
> Using bit to represent boolean values with 1 for truth is quite standard
> in the T-SQL world. So why should .Net UDT methods be special?
It is also common in the T-SQL world to use 'Y', 'T', and 'True' for
Yes/True. Yet none of these (1 inclusive) is the same thing as TRUE, or you
could logically expect something like this to work:
IF 1
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
However the result of 1 = 1 *is* TRUE, so you can expect this to work:
IF 1 = 1
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
Or even this:
DECLARE @.i INT
DECLARE @.j INT
SELECT @.i = 1
SELECT @.j = 1
IF @.i = @.j
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
*Yet this doesn't*:
DECLARE @.i MyUDT
DECLARE @.j MyUDT
SELECT @.i = '(1,2,3)'
SELECT @.j = '(1,2,3)'
IF @.i = @.j
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
It appears that .NET UDT equality operators are "special" for non-byte
ordered types. As I stated, the conclusion seems to be if two items can't
be sorted they can't be compared for equality.
>> Just wondering what "some of T-SQL data types in some collations" has to
>> do with comparing the binary representation of a SQLCLR UDT?
> As an example that equality is not always possible by byte-comparing,
> even if it might be in your case.
I don't see how collation even comes into the picture here, but the fact
that equality is not always possible by byte-comparing is another reason it
would be just *dandy* to have the capability of overloading the T-SQL
equality operator for your UDT.
Bottom line is this: I asked if it was possible to do "x". You said it is
*not* possible to do "x".
That's great. Case closed. Problem solved. Over and out.

[rsInvalidExpressionDataType] The Value expression used in image ‘image1’ returned a data type t

[rsInvalidExpressionDataType] The Value expression used in image ‘image1’ returned a data type that is not valid.

[rsInvalidDatabaseImage] The Value expression for the image ‘image1’ did not evaluate to an image.

Hi How should i solve this problem

Regards

KAren

Try this out:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=967272&SiteID=1

|||

thanks Greg,

got it to work.. Set the image as an external....

Tuesday, March 6, 2012

[MS Sql] Data type nvarchar

Hi everybody,

I'm using SQL Server Management Studio Express.
I'm trying to create a field which contains a text entered by the user. So, it should be able to contain at least 500 characters.

I used the type "nvarchar(MAX)". The problem is that the type contains about 50 characters max!!
I couldn't find out where and how to fix that.
If you have any idea :)

Thanks a lotType over the the MAX with the desired length of 500.|||Hi tmorton
Of course I tried nvarchar(500) (forgot to tell that). But it's not working either.
It sounds like nvarchar(MAX) is the max length type, and limited to about 50...
Thanks for your reply. And if someone has any idea :)|||That's all it takes from the SQL Server end. If you are only managing to store 50 characters in that column, then I would venture a guess that your data is being truncated before it hits the database. What does your code look like that is adding the data to your table?|||I believe that SQL Management Studio truncates text that it displays. Use something else to view/edit the column.|||Hi
It's weird, it's working now. And yet I didn't change anything particular. Maybe a "bug" on SQL Server... weird though. Thanks a lot to you guys :)

Thursday, February 16, 2012

[Bug] Deadlock: Wait Type CXPACKET with CLUSTERED INDEXES

Our site has experienced a dead lock issue with a query that uses parallel
processing. Anyone know how to report this to Microsoft?
When running a load for one of our datamarts we have a query that uses 12
threads of execution, we have several queries like this but only 1 which
locks up. Half way to 3/4 of the way through execution many of threads stop
and are waiting with a type CXPACKET, CPU utilizations grinds to halt and the
query never returns. It ran this entire weekend, Friday 11:30 PM to Monday
9:00 AM.
Our workaround, believe it or not, was to change the primary key's on a
couple lookup tables from CLUSTERED to NON-CLUSTERED. This fixed the problem
and still allows us to use parallel execution and we did not have to resort
to OPTION (MAXDOP 1)
There is clearly some kind of bug with SQL Server and the creation and
execution of the query plan. How do I get a solid core dump and log of the
server and submit a bug report to MS so they can fix this.
AdamAdam,
If you are up-to-date on service packs and not just encountering
http://support.microsoft.com/kb/293232/, the best way to report this is
probably to open a case with Microsoft Product Support (PSS). This
doesn't sound like the kind of problem that can easily be distilled down
to a reproducible Query Analyzer script.
If the problem is caused by a bug, there's a good chance the support
incident will be free, but whether or not there will be a fix in the
short term is hard to say. But changing indexes to nonclustered isn't a
very convenient workaround, and PSS might be able to find a better
workaround. There are occasional newsgroup reports of this situation,
and you can browse recent threads here to see if there is a better
workaround:
http://groups-beta.google.com/groups?q=CXPACKET+sqlserver&start=0&scoring=d&hl=en&
Steve Kass
Drew University
Adam wrote:
>Our site has experienced a dead lock issue with a query that uses parallel
>processing. Anyone know how to report this to Microsoft?
>When running a load for one of our datamarts we have a query that uses 12
>threads of execution, we have several queries like this but only 1 which
>locks up. Half way to 3/4 of the way through execution many of threads stop
>and are waiting with a type CXPACKET, CPU utilizations grinds to halt and the
>query never returns. It ran this entire weekend, Friday 11:30 PM to Monday
>9:00 AM.
>Our workaround, believe it or not, was to change the primary key's on a
>couple lookup tables from CLUSTERED to NON-CLUSTERED. This fixed the problem
>and still allows us to use parallel execution and we did not have to resort
>to OPTION (MAXDOP 1)
>There is clearly some kind of bug with SQL Server and the creation and
>execution of the query plan. How do I get a solid core dump and log of the
>server and submit a bug report to MS so they can fix this.
>Adam
>|||Thanks Steve, I will go the PSS route.
"Steve Kass" wrote:
> Adam,
> If you are up-to-date on service packs and not just encountering
> http://support.microsoft.com/kb/293232/, the best way to report this is
> probably to open a case with Microsoft Product Support (PSS). This
> doesn't sound like the kind of problem that can easily be distilled down
> to a reproducible Query Analyzer script.
> If the problem is caused by a bug, there's a good chance the support
> incident will be free, but whether or not there will be a fix in the
> short term is hard to say. But changing indexes to nonclustered isn't a
> very convenient workaround, and PSS might be able to find a better
> workaround. There are occasional newsgroup reports of this situation,
> and you can browse recent threads here to see if there is a better
> workaround:
> http://groups-beta.google.com/groups?q=CXPACKET+sqlserver&start=0&scoring=d&hl=en&
>
> Steve Kass
> Drew University
> Adam wrote:
> >Our site has experienced a dead lock issue with a query that uses parallel
> >processing. Anyone know how to report this to Microsoft?
> >
> >When running a load for one of our datamarts we have a query that uses 12
> >threads of execution, we have several queries like this but only 1 which
> >locks up. Half way to 3/4 of the way through execution many of threads stop
> >and are waiting with a type CXPACKET, CPU utilizations grinds to halt and the
> >query never returns. It ran this entire weekend, Friday 11:30 PM to Monday
> >9:00 AM.
> >
> >Our workaround, believe it or not, was to change the primary key's on a
> >couple lookup tables from CLUSTERED to NON-CLUSTERED. This fixed the problem
> >and still allows us to use parallel execution and we did not have to resort
> >to OPTION (MAXDOP 1)
> >
> >There is clearly some kind of bug with SQL Server and the creation and
> >execution of the query plan. How do I get a solid core dump and log of the
> >server and submit a bug report to MS so they can fix this.
> >
> >Adam
> >
> >
>|||On Mon, 20 Dec 2004 12:59:05 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>Our workaround, believe it or not, was to change the primary key's on a
>couple lookup tables from CLUSTERED to NON-CLUSTERED. This fixed the problem
>and still allows us to use parallel execution and we did not have to resort
>to OPTION (MAXDOP 1)
I believe it.
You don't happen to use any little lock hints in your query, do you?
I recently had some queries hang up because the programmer had
helpfully specified tablockx, and this turned out to be
counterproductive.
J.

Saturday, February 11, 2012

@local_var really cannot hold table name ?

Hi,
I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
from year 1997, 1998 and a027 is just specification of product type. I
need carry out repeated operataions over these tables. To generate
their names is easy, for example

use [P5-01]
declare @.Yint int, @.Y char(2), @.tabName char(8)
set @.Yint = 1997
while @.Yint < 2002
begin
set @.tabName = 'ag' + substring(convert(char(4),@.Yint),3,2)+'a027'
print @.tabName
set @.Yint = @.Yint + 1
/*
select *
from @.tabName
*/
end

prints properly

ag97a027
ag98a027
ag99a027
ag00a027
ag01a027

but when I uncomment the select statement, MS-SQL server responds:
Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@.tabName'.

How to use @.local_variable for browsing through tables in above
described way?

thanks
MartinOn 23 May 2004 23:10:43 -0700, Martin Mrazek wrote:

>Hi,
>I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
>from year 1997, 1998 and a027 is just specification of product type. I
>need carry out repeated operataions over these tables. To generate
>their names is easy, for example
>use [P5-01]
>declare @.Yint int, @.Y char(2), @.tabName char(8)
>set @.Yint = 1997
>while @.Yint < 2002
>begin
> set @.tabName = 'ag' + substring(convert(char(4),@.Yint),3,2)+'a027'
> print @.tabName
> set @.Yint = @.Yint + 1
>/*
> select *
> from @.tabName
>*/
>end
>prints properly
>ag97a027
>ag98a027
>ag99a027
>ag00a027
>ag01a027
>but when I uncomment the select statement, MS-SQL server responds:
>Server: Msg 137, Level 15, State 2, Line 11
>Must declare the variable '@.tabName'.
>How to use @.local_variable for browsing through tables in above
>described way?
>thanks
>Martin

Hi Martin,

The error message is caused because SQL Server accepts either a literal
table name or a table variable in the from clause. The @. denotes the next
thing as a variable, so SQL Server will try to find a table variable
called @.tabName, which it can't find.

The best answer to your question is: change your table design. Generally
speaking, 99 out of 100 cases where each year's data is stored in a
seperate table are better off storing everything in one table (adding a
"year" column if there isn't already a "date" column that can serve this
purpose), or using a two table design (one "current" table and one
"history" table), again adding a "year" column if needed.

If you feel you must do it this way, google this newsgroup for "dynamic
SQL". That's the kludge that will do what you're trying to achieve.

(I assume that the above SQL is used only to illustrate your problem and
that you're not actually using "select *" in production code or printing
all data in all tables - who would ever want to read that much output??).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||mrazek@.compik.fd.cvut.cz (Martin Mrazek) wrote in message news:<dcd39e84.0405232210.5a75aeb6@.posting.google.com>...
> Hi,
> I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
> from year 1997, 1998 and a027 is just specification of product type. I
> need carry out repeated operataions over these tables. To generate
> their names is easy, for example
> use [P5-01]
> declare @.Yint int, @.Y char(2), @.tabName char(8)
> set @.Yint = 1997
> while @.Yint < 2002
> begin
> set @.tabName = 'ag' + substring(convert(char(4),@.Yint),3,2)+'a027'
> print @.tabName
> set @.Yint = @.Yint + 1
> /*
> select *
> from @.tabName
> */
> end
> prints properly
> ag97a027
> ag98a027
> ag99a027
> ag00a027
> ag01a027
> but when I uncomment the select statement, MS-SQL server responds:
> Server: Msg 137, Level 15, State 2, Line 11
> Must declare the variable '@.tabName'.
> How to use @.local_variable for browsing through tables in above
> described way?
> thanks
> Martin

You can use dynamic SQL for this (see below), but read this first to
understand why you probably shouldn't:

http://www.sommarskog.se/dynamic_sql.html#Sales_yymm

Simon

declare @.tblname sysname
set @.tblname = 'MyTable'
exec('select * from ' + @.tblname)|||Martin Mrazek (mrazek@.compik.fd.cvut.cz) writes:
> How to use @.local_variable for browsing through tables in above
> described way?

The answer is you don't. Well, you can if you go over hops of dynamic
SQL, but you should really only have one table, with year and product
types as keys in your one single table.

If you have some application which really requires all these tables,
then set up a view over all the tables, with year and product type
added:

CREATE VIEW all_my_tables (year, product_type, col1, col2, col3, ...) AS
SELECT '1998', 'a027', col1, col2, col3,
FROM ag98a027
UNION ALL
SELECT '1999', 'a027', col1, col2, col3,
FROM ag99a027
UNION ALL
...

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, March 22, 2012

_hypmv error

_hypmv error

Sunday, March 11, 2012

[SQL 2005] SQLCLR UDT Comparison Methods

[SQL 2005] SQLCLR UDT Comparison Methods

[SQL 2005] SQLCLR UDT Comparison Methods

[rsInvalidExpressionDataType] The Value expression used in image ‘image1’ returned a data type t

Tuesday, March 6, 2012

[MS Sql] Data type nvarchar

Thursday, February 16, 2012

[Bug] Deadlock: Wait Type CXPACKET with CLUSTERED INDEXES

Saturday, February 11, 2012

@local_var really cannot hold table name ?

sql rowcount

Blog Archive

About Me