intermittent locks

We are experiencing problems intermittently.

Users report problems of various types including timeout messages. We investigate and find a user has acquired a lock which is blocking other users.

We contact the user and they have usually completed their activity and are not always aware of any problem despite them owning a lock.

When the user logs out of the application the lock clears and the system returns to normal.

Indexes have been rebuilt, auto update statistics is on.

Does anyone have any suggestions? :cool:The first thing I'd do is start watching for locks to determine how often they occur, and ask the users if they know of any activity that causes the problems associated with locking/blocking (that may give you clues about what you need to watch).

Once you understand what you are looking for, run a trace using SQL Profiler at the same time as a Performance Monitor trace watching for locking/blocking. The PerfMon trace will show you when the problem occurs, the Profiler trace will show you what caused the problem.

When you understand the cause of the problem, you can then look at changing the application to avoid the problem.

-PatP|||We are trying to gather more information from the users to track this down.

Anecdotally users believe that they have finished their activity and are simply still logged on or are running searches.

We haven't needed to kill a session, the user simply logs off.

It's almost as though the lock has been taken but not released when the activity has finished.

Does this sound likely/possible? If so any ideas what could be causing it?|||Does this sound likely/possible? If so any ideas what could be causing it?Yes, it sounds rather likely.

I'd suspect that the problem is something that the code is doing "behind the curtains" that the user is completely unaware of, but is still causing havok. Until you can compare the two traces (or provide LOTS of additional insight into your application and server configuration), we can only guess.

-PatP

Integrity Constraints

I had a question regarding using XML as a database rather than a data format.
Can some/all types of integrity constraints that we (can) have in a SQL database be represented/mapped to XML?
Could anyone explain or give some pointers to this...
-Aayush
I'm not sure exactly what you're asking. In general, XML isn't a database
so using it as a database doesn't make sense. In the limited scope of the
topic of this newsgroups, SQL Server 2000 and SQLXML allow you to store the
data in an XML document as one or more rows of normal relational data.
Because the XML is mapped to relational data, the integrity constraint on
the relation data apply so for example you may not be able to insert a
document that has order lines if no corresponding order header exists. Note
that this is possible because XML is being shredded into relational data and
is not an inherent part of XML. In general, XML schemas don't support
defining or enforcing most integrity constraints.
Does this answer your question?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Aayush Puri" <anonymous@.discussions.microsoft.com> wrote in message
news:3EC02BEB-4AEA-438C-AB11-4D4F33C1CD6D@.microsoft.com...
>I had a question regarding using XML as a database rather than a data
>format.
> Can some/all types of integrity constraints that we (can) have in a SQL
> database be represented/mapped to XML?
> Could anyone explain or give some pointers to this...
>
> -Aayush
>
|||Hey,
Thankx for the reply. Yeah I know that it makes little sense to use XML as
a database rather than a data format...but the app. which I am trying to
design is for users *not* having any SQL database. I was just just wondering
if XSD allows me to specify constraints alike SQL or if possible things like
triggers etc...
U got my point right.
Thankx,
-Aayush

Integrity across multiple database types

Since most use SQL server, I thought I would post the question here.
Is it possible, or is there a product or DBMS that enforces referential integrity across multiple databases and database types? Such as SQL Server, Oracle, etc...
Thanks,
ZathDRI(declarative referential integrity) is Peter Chen ERD 1976 enable data delete and update in multiple tables at the same time but you cannot set it up across two databases but if you plan very well you can use linked server to access separate DRI(declarative referential integrity) rules setup in two databases. CASCADE ON DELETE, CASCADE ON UPDATE, ON DELETE NO ACTION and ON UPDATE NO ACTION the later will not allow delete or update. Run a search for all in the BOL(books online). Hope this helps.|||Thanks, just the start I needed.
Zath

Integration Services Data Types Maximum Length

Hi,

Is there a way in-code to determine the maximum length of a Integration Services Data Type.

I need to determine based on the data type what the maximum length of a column is IN-CODE.

However, the column.Length property only gives me a length for DT_WSTR and DT_STR values. This is the only property that would seem to remotely give me the right answer.

I need to know the maximum lengths in columns for DT_BOOL, DT_CY, DT_I2, DT_I4, DT_I8, DT_NUMERIC, and DT_UI1. I can always hard-code these values into my program, but that makes no sense. There has to be some sort of way to determine what the maximum possible length of these values are.

For numeric values I could use the column.Precision value but that still leaves with with a lot of data types without a maximum length.
It might help if you explain why you want to know this information.

A DT_I4 is 4 bytes long, for instance, but it can hold -2,147,483,648 to 2,147,483,647. So which value do you want? Do you see the problem?

Data types are fixed in what they can handle, so why not just hard code them into your "IN-CODE"?|||In your example, for DT_I4, -2,147,483,648 is a length of 11 (excluding commas, but including the sign). Thats the kind of value I need to know. I apologize if I wasn't clear on that.

Well, yes, I could hard-code them. But I was wondering whether that type of information is actually stored (which really if you think about it, shouldn't be that difficult, and also worthwhile so a person does not have the compute what each one would be).

Hard-coding is something you also try to avoid if there is a way to avoid it (and hence my question as to whether I can avoid this or not).

Thanks

|||But that's just it, the length of a DT_I4 is 4, not 11.

The "length" of 11 is not stored anywhere. It's a limitation of the storage allocated to the data type. There are probably internal bounds checks, but I'm guessing they are not exposed. You could always try one of the many programming forums in MSDN to see if someone has an idea, as this really isn't an SSIS issue. SSIS data types are simply mapped to structures in .Net.

And the max length of a DT_STR is 8000 bytes. DT_WSTR is 4000 bytes. But more importantly, there is no such thing as a DT_STR, DT_I4, etc... in code. So what SSIS has as limitations may not be the same as the structures available in your code. (A DT_I4 equates to the Integer (Int32) structure.)|||

theddern wrote:

Hi,

Is there a way in-code to determine the maximum length of a Integration Services Data Type.

I need to determine based on the data type what the maximum length of a column is IN-CODE.

However, the column.Length property only gives me a length for DT_WSTR and DT_STR values. This is the only property that would seem to remotely give me the right answer.

I need to know the maximum lengths in columns for DT_BOOL, DT_CY, DT_I2, DT_I4, DT_I8, DT_NUMERIC, and DT_UI1. I can always hard-code these values into my program, but that makes no sense. There has to be some sort of way to determine what the maximum possible length of these values are.

For numeric values I could use the column.Precision value but that still leaves with with a lot of data types without a maximum length.

There is no maximum/minimum length for those data types. They are just what they are - they never change. The length simply isn't relevant.

-Jamie

|||

theddern wrote:

In your example, for DT_I4, -2,147,483,648 is a length of 11 (excluding commas, but including the sign).

No its not. The length of it is 4. 4 bytes that is.

If the value that you want is 11 then just cast it as a DT_STR and get the length of that.

-Jamie

|||So what you are saying is that I need to hard-code the lengths if I need to know those particular type of values?

|||

theddern wrote:

So what you are saying is that I need to hard-code the lengths if I need to know those particular type of values?

Yes! I just can't see the value in knowing the "lengths" of data types.|||And to clarify, I need to know what the maximum length (in characters) a particular column can be if that particular data was transfered to a command delimited flat file.

I was trying to figure that out based on IDTSColumn90.DataType because that would be the most logical place to start.
|||Are you wanting the display length for each data type?|||

theddern wrote:

And to clarify, I need to know what the maximum length (in characters) a particular column can be if that particular data was transfered to a command (sic) delimited flat file.

Why? If it is a delimited file, who cares?|||If he wanted to document specs for a fixed-length output file, he would find these useful. |||

theddern wrote:

So what you are saying is that I need to hard-code the lengths if I need to know those particular type of values?

No. I'm saying you don't need to care.

|||

Phil Brammer wrote:

theddern wrote:

And to clarify, I need to know what the maximum length (in characters) a particular column can be if that particular data was transfered to a command (sic) delimited flat file.

Why? If it is a delimited file, who cares?

Very very very good point.

This seems a very strange thing to want to do.

|||Yes

Integration Services Data Types

Hi, I have a question regarding the Integration Services Data Types.

From http://msdn2.microsoft.com/en-us/library/ms141036(d-printer).aspx, I found a table that shows me the Mapping of Integration Services Data Types to Database Data Types.

For example, how the DT_BOOL Data Type maps to bit for SQL Server.

In this case, I am okay, as I know exactly what the mapping is, however, for some of the datatypes, I do not.

Here is an example. The DT_CY datatype maps to smallmoney and money ... how do I know which one to map to? For me, which one I map to does indeed matter because their representation is different.

DT_NUMERIC maps to decimal and numeric ... this one does not matter as much

DT_STR/DT_WSTR ... I need to know whether its char, varchar, ncahr, or nvarchar for padding purposes mostly.

Any help would be gladly appreciated.
As for DT_CY, you pick. Either will work.

DT_STR = varchar, char
DT_WSTR = nvarchar, nchar|||From what I am doing with the values, I can not just pick for DT_CY. I need to know whether it is actually smallmoney, or money.

Same goes with DT_STR and varchar, char ... I need to know whether its one or the other.

And similarly for nvarchar/nchar for DT_WSTR.

I am passing these values to an application that needs to know what is what because it treats each value differently.

|||

theddern wrote:

From what I am doing with the values, I can not just pick for DT_CY. I need to know whether it is actually smallmoney, or money.

Same goes with DT_STR and varchar, char ... I need to know whether its one or the other.

And similarly for nvarchar/nchar for DT_WSTR.

I am passing these values to an application that needs to know what is what because it treats each value differently.

You are on the wrong end of the question though.

YOU have to decide which SQL Server data type best fits the data. SSIS doesn't dictate that; you do.

So yes, you have to pick and stick with it. Do you understand the differences in the SQL Server datatypes? You might not ever use char/nchar (retains trailing spaces) so that might solve that issue for you. DT_CY, well, you just need to know what the data supports and choose the correct one.|||Alright thanks