Deadlocks Workaround?

8114 Workaround
The very simplified version of my problem is that these

Select DISTINCT Cast(KWID as NUMERIC)
FROM OV_MID

Select DISTINCT Convert(Numeric,KWID)
FROM OV_MID

should work, but don't because KWID is a varchar and somewhere in there is something that won't convert.

I get this error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

I would love to find out which rows are causing the error, but more importantly I would like to have a Null value where the conversion doesn't work and the numeric values where it does work.

I have already deleted all obvious non-numeric characters, but I believe there are some line terminators being read as carriage returns in this table. :confused:

Any workaround or way to determine which rows have KWID that cannot be converted to numeric would be most appreciated.

Thanks!

View Replies !

---

How Can I Workaround This Problem?
Hello everbody,

this query:

SELECT * FROM TBL_DEVICE_DRIVERS

 WHERE (DD_CATEGORIES & 2147483648) > 0

bring the error message:

Invalid operator for data type.

The value 2147483648 is hex 0x80000000 for bitwise

joining defined.

I try to cast it to decimal, but it does not work.

Have anybody an idea?

Thanks for answers!

 

View Replies !

---

Need Workaround For DATEPART(wk, ...) Function !
The function "DATEPART(wk, [valid_date])" appears to have the following bug:

DATEPART(wk, date) returns week 53 for the following dates (checked years 2000, 2001, 2002):
year 2000: 12-24-2000 through 12-30-2000 inclusive
year 2001: 12-30-2000 through 12-31-2001 inclusive
year 2002: 12-29-2002 through 12-31-2002 inclusive

DATEPART(wk, date) returns week 54 for the following dates:
year 2000: 12-31-2000

SQL2000 SP1.

Are there any known workarounds/fixes/patches for this (other than just hand-coding the function?)

Thanks!

David Schneider
Engineering Manager
iScribe, Inc.
DSchneider@iscribe.com

View Replies !

---

Workaround For Precendence Constraint
Hi,

 

Any insights to this issue would be great.

Following is my problem statement. I have write around 20Batch programs and in each batch i have atleast 7-8 data validations. If any of the data validation fails then i have to perform a log operation and exit.

 

Now I have each of my data validation as a Script Task (Control Flow) which inturn would call my SP and set the "Status" variable accordingly

 

I have written a "OnVariableValueChanged" Event with Raise Change Event for "Status" variable set to "True" Now in this event i check if Status = False, if it is false then i perform the log operation and throw a new DTSException to abort the control flow execution. The event gets fired but it continues to process the next control step(but i wanted it stop there). I could have acheived this by setting a precendence constraint (Status = True) for all of my control flow task but i feel the other approach to be very elegant.

 

Any suggestions to make this work??

 

Thanks & Regards

Arvind T N

View Replies !

---

Lookup Limitation Workaround
Hello,

I have found this thread regarding the limitation of the LOOKUP transformation query to be configured using values passed from variables -> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=142837&SiteID=1

Has anyone implemented a workaround on this limitation?  Or does anyone have a sample script component which can address the lookup limitation?

Any help on this would be greatly appreciated.

Thanks!

-Fritzie-

View Replies !

---

UDF Taking Too Long To Run Workaround ?
I have written a UDF into which I pass a table name, field name, value of the field, whether alpha characters are valid, whether numerics are valid, and a string of alphanumerics that are valid. I return back a string with all invalid characters removed. Unfortunately when I use this on names and addresses in an 12000 row table, it takes forever to run. Can anyone think of an easy way to do this which isn't so labour intensive. Please see code below.

NB CHAR(32) is space, CHAR(45) is -,CHAR(39) is '

CREATE FUNCTION dbo.UDF_RemoveInvalidCharacters
(  @sTableName varchar(50),-- e.g. 'Contact'
  @sFieldname varchar(50),-- e.g. 'Lastname'
 @sFieldValue  varchar(500),-- e.g. 'Jeremi@h O''Grady84'
  @sAlphaValid char(1),-- e.g. 'Y'
  @sNumericValid char(1),--e.g. 'N'
  @sAlphanumericsValid varchar(500))--'CHAR(32):CHAR(45):CHAR(39)'
RETURNS varchar(500)
AS
BEGIN
 DECLARE @sReturnValue   varchar(500),
   @nTableID   int,
   @nFieldLength   int,
  @nCurrentPos   int,
  @sTestChar   char(1),
  @sValid   char(1),
  @nAlphanumericPos  int,
  @sAlphanumericTest  varchar(8),
  @sTempTestChar   varchar(8),
  @sAlphasFound  char(1),
  @sNumericsFound char(1),
  @sAlphanumericsFound char(1)

 --Get ID of table that the field is on
 SELECT  @nTableID = [id]
 FROM  SYSOBJECTS
 WHERE  [name] = @sTableName

 --Get the length of the field
 SELECT  @nFieldLength = sc.length
 FROM  SYSOBJECTS so, SYSCOLUMNS sc
 WHERE  so.id = @nTableID
 AND  sc.id = @nTableID
 AND  sc.name = @sFieldName

 --Initialise values
 SET @sReturnValue = ''
 SET @nCurrentPos = 1
 SET @sValid = 'N'
 SET @sAlphasFound = 'N'
 SET @sNumericsFound = 'N'
 SET @sAlphanumericsFound = 'N'

 --Test each character to ensure it is valid before adding it to the return string, a string consisting solely of alphanumeric characters would be wrong
 WHILE @nFieldLength >= @nCurrentPos
 BEGIN
  SET @sTestChar = substring(@sFieldValue,@nCurrentPos,1)
  IF @sAlphaValid = 'Y' --alphas are valid
  BEGIN
   IF UPPER(@sTestChar) in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
   BEGIN
    SET @sValid = 'Y'
    SET @sAlphasFound = 'Y'
   END
    
  END
  IF @sNumericValid = 'Y' AND @sValid <> 'Y'--numerics are valid
  BEGIN
   IF @sTestChar in ('0','1','2','3','4','5','6','7','8','9')
   BEGIN
    SET @sValid = 'Y'
    SET @sNumericsFound = 'Y'
   END
  END
  SET @nAlphanumericPos = 1
  WHILE LEN(@sAlphanumericsValid) > @nAlphanumericPos AND @sValid <> 'Y' --alphanumerics that are valid
  BEGIN
   IF CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid))) > 0
   BEGIN
    SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid)))-1)
   END ELSE
   BEGIN
    SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,(LEN(@sAlphanumericsValid)-@nAlphanumericPos)+1)
   END
   SET @sTempTestChar = 'CHAR(' + RTRIM(LTRIM(STR(ASCII(@sTestChar)))) + ')'
   IF @sTempTestChar = @sAlphanumericTest AND (@sAlphasFound = 'Y' OR @sNumericsFound = 'Y') --alphanumerics are only valid once we have alpha or numerics
   BEGIN
    SET @sValid = 'Y'
    SET @sAlphanumericsFound = 'Y'
   END
   SET @nAlphanumericPos = @nAlphanumericPos + LEN(@sAlphanumericTest) + 1
  END
  IF @sValid = 'Y'
  BEGIN
   SELECT @sReturnValue = @sReturnValue + @sTestChar
  END
  SET @nCurrentPos = @nCurrentPos + 1
  SELECT @sValid = 'N'
 END
 IF @sAlphanumericsFound = 'Y' AND @sNumericsFound = 'N'  AND @sAlphasFound = 'N' --alphanumerics on their own are not valid
 BEGIN
  SELECT @sReturnValue = ''
 END
 RETURN @sReturnValue --in the example I would get Jeremih O'Grady

View Replies !

---

Workaround CREATE MASTER KEY??
At some point in time, when I release my code from developemnt to production, somebody will onvoke the SQL Script containg my certificates and symettric keys based on my master key. Unfortunately this seems a bit of a weekness as my SQL SCRIPT contains the CREATE MASTER KEY ENCRYPTION BY PASSWORD stement which has the password itself in clear. (script gets invoked from a  command line in a batch script which is all under documeny mangement control). Obviously I would not like my password to be be in clear anywhere - i.e. not in document control nor viewable from whoever invokes the script. What is best pracrice to adopt on this? - encrypt the script file?

Thanks

View Replies !

---

Workaround For No Aggregates In A Grouping
 

I am trying to group on the number of distict field values.  Basically:

 

=Ceiling(CountDistinct(Fields!ClientID.Value, Nothing)/10)

 

So for every 10 different clients, I get a group.  I get an error with the above function saying aggregates are not allowed in a grouping expression.  I tried creating a text box with the running value:

=RunningValue(Fields!ClientID.Value,CountDistinct,Nothing)

The value of the text box is correct, but I can't reference that text box in the grouping function.  It says the textbox is undefined.

 

Any suggestions for a workaraound?

 

Thanks.

 

 

 

View Replies !

---

Whileprintingrecords Equivalent Or Workaround?
I'm converting crystal reports to SSRS reports right now and came across this function that I'm not familiar with.
It's a formula field in crystal that has this formula:

quote:whileprintingrecords;
NumberVar RTCurrent;
NumberVar RT31to60;
NumberVar RT61to90;
NumberVar RT91to120;
NumberVar RTOver120;
if {@AgedDays} < 31 then RTCurrent := RTCurrent + {@BalanceDue} else
if ({@AgedDays} > 30 and {@AgedDays} < 61) then RT31to60 := RT31to60 + {@BalanceDue} else
if ({@AgedDays} > 60 and {@AgedDays} < 91) then RT61to90 := RT61to90 + {@BalanceDue} else
if ({@AgedDays} > 90 and {@AgedDays} < 121) then RT91to120 := RT91to120 + {@BalanceDue} else
if {@AgedDays} > 120 then RTOver120 := RTOver120 + {@BalanceDue}

@Aged days is just an integer, but that shouldn't matter for this thread.

Is there just a While loop equivalent for this in SSRS?

View Replies !

---

Slow Database Connection...workaround
I have a portal site that has many iframes loading various pages. One of the iframes requires data from a database that has a slow connection and right now there is nothing we can do about the slow connection and is something we have to live with.
What seems to be happening though is that even though each page is loading seperatly in an iframe, when the page loads with the slow connection, it seems to hold up processing on the server for the other frames until the connection has been established with the server. It can be something like 10 seconds. I am guessing trying to establish the connection is holding up the worker process on IIS???
So I am trying to find a workaround bearing in mind there is nothing we can do about the slow connection for the time being? Does anyone have any suggestions? One I am thinking of is forcing this frame to load last so at least the other frames are not being held up. Another is maybe to use a seperate thread, but does anyone have any idea on this?
Thanks in advance 
 
 

View Replies !

---

Full Text Search Workaround?
Me and a friend are setting up a .net project on a shared hosting server.....the thing is, they dont seem to allow the use of full text search.....when i connect to the server on Enterprise Manager, the option for "Create new catalog" is disabled.

we need to give users the ability to search by keywords........what's a good workaround for this without using FTS?

any advice?

thanks.

G.

View Replies !

---

Tournament App - Union Not Updateable. Is There A Workaround?
Tournament App - Union fails. Is there a workaround?

Hello.

I'm creating an 32 team tournament bracket application online, using Access as my db, because of low number of users. I will convert immediately to MS SQL if my problem can be remedied, but from what I have read MS SQL may not work either...

I'd like it to automatically select the winners from each round after I enter the scores. However, after round 2 (going into round three), I am running into problems because access says that "my recordset is not updateable".

Here's what I do.

Round 1: I manually enter the 2 player matches into 16 seperate game tables (ie, R1GAME1 - R1GAME16)

Round 2 (where automation begins): Round 2 Table selects the winner from from each game table (ex. - SELECT *
FROM R1G1 WHERE Score1=(SELECT MAX(Score1) FROM R1G1); /////// This gives me the 16 winners' Tables from round 1 (which I call R2TEAM1 - R2TEAM16), inwhich I am able to enter the round 2 scores into. However, these tables are 1 record tables, and I need to create "matchups" like I did in round 1, to compare and select the highest round 2 scores.

Round 3 (here's where I run into problems): I entered the round 2 scores in each of the respective R2TEAMx tables, and then created 8 UNION tables, to compare the scores: The UNION table, called R2TEAM1_vs_R2TEAM2, uses this: SELECT * FROM R2TEAM1 UNION SELECT * FROM R2TEAM2.

I then create another query (called R3Team1) to select the MAX score from this table: SELECT *
FROM R2TEAM1_vs_R2TEAM2 WHERE Score2=(SELECT MAX(Score2) FROM R2TEAM1_vs_R2TEAM2);

Now, It grabs the winner perfectly, and I should be ready to enter the round 3 scores into this new table, but I get the error message : "This recordset is not updateable". It is because of the UNION table, but I cant figure out a way around using the UNION statement to create matchups for subsequently selecting the highest scorer.

I would appreciate any help, either an Access solution or MS SQL solution.

Thanks.

View Replies !

---

Workaround For Integer Limitation In Dateadd?
it looks like anything larger than max value for an integer in dateadd's 2nd parameter creates an overflow exception.  This pretty much forces us to work no more finitely than minutes in our app.  Without a stored proc or ss2008, is there a workaround in sql?

View Replies !

---

Report Footer - Need Viable Workaround
After digging for some time now into the "guts" of SSRS, I am wondering if anyone out there has any ideas which might help me at this point.
 
I am trying to write an Invoice report.
 
Each report can have 1 to n invoices on it.
Each invoice can have 1 to n line items (spanning several pages for the larger ones)
Each page must have a fixed header and footer with account and payment information on it (the page header and page footer work OK for this).
 
And here is the problem.  Each invoice must also include 1 to n images at the end of the report.  2 on a page and take up an entire 8.5 by 11 inch page. (spanning many pages when many line items exist)
 
Since the report already has a page header and footer with the report detail stuffed in a table in the middle of the page (report body), I am stuck. 
 
I have read several posts which talk about having a can-grow container  with a subreport in the existing footer, but I can't even come close to getting this to work. My footer would have to take up the entire page and having nothing but a subreport in it.
 
I can not provide a link to the images in the report, as each report must print in its entirety without user involvement (no drilling down).
 
I am thinking that my report is too complex for SSRS at this time.  I would love to be proven wrong by someone on this forum.
 
Thanks for any and all replies.
 
John

View Replies !

---

Workaround For Cross Database Queries
Hi all,
 
Could you please suggest workaround for systems that have cross database queries and want to use mirroring.  
 
Thanks,
Avi

View Replies !

---

Workaround To Connect To SQL Server When Ports Blocked?
My SQL Server is a shared account at MaximumASP.com a client just deployed my .NET application on GoDaddy.com and they have all there ports blocked and my app cannot connect to the SQL Server. Using "Network Library =dbmssocn" in the connection string did not help and GoDaddy will not help.  MY QUESTION IS: how can I get my .NET app to connect to the SQL Server? web service? This is the first time I have run into this problem. There seems like there has to be some way. THANKS IN ADVANCE!

View Replies !

---

WorkAround For Text Columns In Deleted Table???
Hi!
I am trying to use the tables deleted and inserted to audit rows and they do not work with datatypes of text, ntext, image.
Any suggestion is welcome.

Thanks in advance

Mariyah

View Replies !

---

Validating XML Before Processing In SSIS - A Good Workaround
Hi all,

 

Like many, I was a bit surprised and frustrated by the apparent 'issue' with the XML Task when using it inside a ForEach container. Unless I've missed an update to this, it seems that if you have multiple XML files that you wish toprocess with the validate functionality of the XML Task, if you put this task inside a ForEach container the XML Task will return the result of the first file's validation for the rest of the files in the folder. Not good!

 

After a bit of digging I found the sample code kindly posted by someone that also ran into this problem. (See post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1001433&SiteID=1).

 

The key to that workaround was the use in code of the XMLReader and XMLReaderSettings classes - a reasonable approach, although it seems like a lot of code for what should be a simple task.

 

As an alternative, I thought I'd post the workaround that I've successfully implemented. It's very easy to implement:

 

Firstly, add your XSD schema into SQL Server's schema collections using the CREATE XML SCHEMA COLLECTION statement, for example:

 




Code Snippet
CREATE XML SCHEMA COLLECTION MyXMLSchema AS '<xs:schema...'



 

Insert your XSD schema between the quotes, and you'll then have a copy of your schema loaded into SQL Server that can be referenced as dbo.MyXMLSchema.
Next, I wrote a stored proc that takes the XML to be validated as an input paramter and returns either 'OK' or the validation error message as an output parameter:


 




Code Snippet


CREATEPROC [dbo].[pValidateXML]

    (@SourceXML        XML,

     @ValidationResult VARCHAR(1000) OUTPUT)
AS

   -- This proc validates the passed XML against thedbo.MyXMLSchema
     -- schema. If the XML is valid then 'OK' is returned in
     -- @ValidationResult. If theXML is not valid then the XML
     -- validation error is returned.

 

 SET @ValidationResult = 'OK'

 

 BEGIN TRY
 
      DECLARE @Validator TABLE (XMLToValidate XML (dbo.MyXMLSchema))
      INSERT INTO @Validator VALUES (@SourceXML)

     RETURN 0

 

 END TRY

 

 BEGIN CATCH
 
      SET @ValidationResult = ERROR_MESSAGE()
      RETURN ERROR_NUMBER()

 

 END CATCH

 

The key to this is the use of a SQL variable of type TABLE that specifies a single column of type XML which is a typed xml column that's bound to the schema dbo.MyXMLSchema which we created earlier.

 

The proc then attempts to insert the passed XML into our table variable. At this point SQL Serverwill do the XML validation for you as it will not allow invalid XML to be inserted into our typed XML column.

 

Using the brilliant TRY...CATCH error handling that's new to SQL Server 2005, you can trap any errors that occur and return a handy message to the calling process bypicking up the ERROR_MESSAGE() variable in our CATCH block and assigning it to our output parameter.

 

The next step is to call the proc from SSIS using an EXECUTE SQL task:


  - Set up your database connection

 

- Specify the database connection in the EXECUTE SQL task

 

- Set the command to: EXEC pValidateXML @SourceXML, @ValidationResult OUTPUT
(Note that it's vital that you include the OUTPUT modifier next to the @ValidationResult parameter. If you don't then SSIS will complain that you're trying to change the type of a variable at runtime)


- You'll need two variables as parameters to the stored proc - one to hold the XML to validate, and another to receive the validation message (this will either be the string 'OK' if validation was successful, or a validation error message if the XML was not valid).

 

For info on reading XML from a file into a variable, see this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1448465&SiteID=1

 

When the storec proc is executed you can use conditional branching in your SSIS package based on the value of the SSIS variable that received the @ValidationResult output parameter. Typically you'll want to handle invalid XML with one path through your package, and valid XML with another. To achieve this, create two precedence constraints on your EXECUTE SQL task. The successful route should have an evaluation operation of type 'Expression AND Constraint'. The constraint value selected from the dropdown should be 'SUCCESS' and the expression should be '@varMyValidationResult == "OK"'. Substitute in the name of the SSIS variable that you used to receive the output parameter above. For validation failures we'll create a second precedence constraint, this time with an evaluation operation of type 'Expression OR Constraint'. The constraint value selected this time is 'FAILURE' and the expression should be '@varMyValidationResult != "OK"'. Be careful when selecting the evaluation operation - make sure you get your AND and OR the right way round!

 

I implemented this today and it works really well. I hope you have the same experience. Feel free to ask questions if you have any problems with this.</xs:schema...'

View Replies !

---

Workaround For Aggregate Function In Table Fliter?
Hi,

 

Is there any way to work around the problem of not being able to use Aggregate functions in Table Filter expressions?

 

I have for example, the following kind of data being returned by the Dataset query:

 
DATE                             VAL1         VAL2            VAL3
1/1/2007 12:00:00 AM          1                  5                    0
1/2/2007 12:00:00 AM          5                 10                 15
1/3/2007 12:00:00 AM          3                  6                    9
1/4/2007 12:00:00 AM          4                  8                    2

 

In the report layout, I have a Table through which I need to show only data for the Maximum Date from above, so the output should look like the following:
  DATE                               VAL1      VAL2      VAL3
   1/4/2007                                 4                8               2
 
This would have been easy if I could use the Max or Last function in the Table filter. But this is obviously not possible. Also, I do not have the option of using another Dataset just to get the Max Date value.
 
So, is there any other way to achieve what I'm looking for?
 
 
-Aayush
 

View Replies !

---

Insert, Update Issue - Stored Procedure Workaround
any stored procedure guru's around ?

I'm going nuts around here.
ok basically I've create a multilangual website using global en local
resources for the static parts and a DB for the dynamic part.
I'm using the PROFILE option in asp.net 2.0 to store the language preference of visitors. It's working perfectly.

but Now I have some problems trying to get the right inserts.

basically I have designed my db based on this article:
http://www.codeproject.com/aspnet/LocalizedSamplePart2.asp?print=true

more specifically:
http://www.codeproject.com/aspnet/LocalizedSamplePart2/normalizedSchema.gif



ok now let's take the example of Categorie, Categorie_Local, and Culture

I basically want to create an insert that will let me insert categories into my database with the 2 language:

eg.
in categorie I have ID's 1 & 2
in culture I have:
ID: 1
culture: en-US
ID 2
culture: fr-Be

now the insert should create into Categorie_Local:

cat_id culture_id name
1 1 a category
1 2 une categorie


and so on...


I think this thing is only do-able with a stored procedure because:

1. when creating a new categorie, a new ID has to be entered into Categorie table
2. into the Categorie_local I need 2 rows inserted with the 2 values for 2 different cultures...



any idea on how to do this right ?
I'm a newbie with ms sql and stored procedures :s



help would be very very appreciated!
thanks a lot

View Replies !

---

Need Workaround For 4000-character Limit On CLR Sproc Parameters
I've written a managed (C#) stored procedure with the following signature:


[Microsoft.SqlServer.Server.SqlProcedure]
public static void Sproc(string startDate, string endDate, string idList)...



Sometimes when I call this sproc, my comma-separated list of IDs exceeds 4000 characters.  How can I get around this problem? 

I guess I need something equivalent to NVarchar(MAX), but for CLR sprocs instead of TSQL.

Any thoughts?

View Replies !

---

Understanding 'Assembly Blah Was Not Found In The SQL Catalog' And Workaround
 
This is the message I get trying to load the assembly into my database:
€˜Create failed for SqlAssembly €˜Microsoft.Adapter.SAP.SAPProvider€™
 Assembly €˜microsoft.oba.metadata.metadataaccess, version=1.0.0.0, culture=neutral, publickeytoken=€¦ €˜ was not found in the SQL catalog. (Microsoft SQL Server, Error: 6503)
I heard one opinion that said that it appears that SQL the CLR Hosted Environment  tries to load the whole assembly dependency graph and doesn't find the dependent assembly in its catalog. That is consistent with the restriction of the CLR Hosted Environment  not supporting managed code dynamically generated (
http://msdn2.microsoft.com/en-us/library/ms131047.aspx )
My questions are: what is microsoft.oba.metadata.metadataaccess and how can I preload it in SQL Server? Assuming the assembly lives somewhere in the server, is there a way to refer to it inside SQL Server without loading it? How is the SAP provider related to Office Business Applications Services (that's the only acronym I could find relating Microsoft with OBA)?
thanks,
Gustavo
 
 

View Replies !

---

Ambiguous Complex Type Definition With XML Source. Workaround?
Hi,

This could well be down to my _limited_ knowledge of XSD.

 

I have a document and SXD supplied by 3rd party.

Both documents are valid, according to XMLSpy.

When I give the document and xsd to SSIS XML Source it complains about ambiguous complex types.

 

In the XML doc there is an element called Allowance that has child elements.

There is also a group which references many other elements including Allowance.

When I remove the group, SSIS stops complaining about allowance.

 

Would the problem stem from SSIS creating an output called Allowance ('cause of it's children), getting to the group and again, 'cause allowance has children, try create another output called Allowance.

Is my understanding of this correct? Is there a work around for a situation like this?

The only thing I can come up with is deleting the group....

Possible to alias an element? Could alias the Group > Allowance g_Allowance.

 

Cheers,

Crispin

 

View Replies !

---

Boolean Data Type Not Available (workaround For Checkbox.checked Storing?)
I have a page for inventory price entry that I have used for a while. Now I need to add a checkbox for whether or not the price includes shipping. I added the checkbox to the form and had it posting 'True' or 'False' to the database as nchar(10) data type. When the gridview pulls up the data, I have the Item Template like this, so it shows a disabled checkbox either checked or not: <asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Convert.ToBoolean(Eval("Shipping")) %>' Enabled="False" /> This works fine for displaying the values, but I copied the checkbox to the Edit Item Template, but did not disabled this one. At first, I didn't change the databindings, leaving it Convert.ToBoolean(Eval("Shipping")), which allowed me to go into Edit mode, change the checkbox, then click update. At which point it would return to it's original state (meaning Update wasn't actually updating). However if I change the databindings, then the page won't display. I checked the SQL statement, and sure enough, it has theUpdateCommand="UPDATE [PartsTable] ... SET [Shipping] = @Shipping... WHERE [PartID] = @original_PartIDAfter fiddling with the sql statement, now I get Object cannot be cast from DBNull to other types. I think that means that the checkbox is sending a null value to the database.Any insight as to how to get this to work is much appreciated. Thanks in advance.

View Replies !

---

SQL 2005 SP2 Error 29506 Workaround Doesn't Work - Data On LUNs
I have had the same Error 29506 that a lot of people are having when installing SP2 for SQL 2005.  I've tried the install with myself (a Domain Admin), local Administrator, cascaded full rights down the entire file system structure and still not luck.  One thing I'm wondering if it is hanging me up is that all of my databases and logs are not on C:.  They are on LUNS on a NetApp SAN (Data is on M: and Logs is on L:).  Even the system databases (Master, Model, etc.) are on the LUNs.  The error logs referenced permissions to the data directory under the default installation path on C:.  Anyone else have this problem?  Got a fix?  I really don't want to migrate all of my data back to the local machine, apply the patch, them migrate back.  Surely this SP should be able to read the data location from the SQL engine.  And surely others have their databases on SANs....   I'm at a loss.
 
Thanks.

View Replies !

---

Continuous Priniting - PDF (MSFT Workaround: Make Sure That Your Report Does Not Use The Subreport Control.)
I have a report that has one subreport.  I am finding that if the entire content of the subreport will not fit within the space remaining on the page that it will not start displaying data from that subreport until the next page of the report, leaving a blank section in the report.  I would like it to display as much as possible on the first page and then continue on subsequent pages.

Is there a way?

View Replies !

---

Deadlocks
Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).

We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.

One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.

What do you guys think?

View Replies !

---

Deadlocks
Hi EverybodyI am new to sqlserver 2000.I know basics of locks.but i dont know how toresolve deadlock issues.I am cofusing by reading articles with 90%information and remaining 10% missing.Can any one help me which is the goodsite to learn and resolve deadlocks.Note: I create deadlock. when i try to trace deadlock using dbcc traceon(1205,3604,-1).In error log showing nothing about the deadlock.showing created traceon.........Any help would be appreciated.--Message posted via http://www.sqlmonster.com

View Replies !

---

Deadlocks, Why?
We have a problem with a table giving us deadlock issues and we can'tfigure out why.It's a table we write to fairly often perhaps 50 times a minute. Andalso do a select of 200 rows at a time from 4 servers every 5 minutes or so.We are only keeping 48 hours worth of rows in the table which averagesat 30000 a day on a busy day.This table has 1 PK and 2 FKs plus one TEXT column which does notparticipate in the WHERE clause.We are using binded variables.We have applied the latest patch to SQL2003 server running onWindows2003. The patch is supposed to resolve deadlock issues.Anyone have any advice on how to alleviate this problem.Thanks

View Replies !

---

Deadlocks
If an instance of SQL 2005 was in use and was using row versioning,under what circumstances would the below error occur?Transaction (Process ID 56) was deadlocked on lock resources withanother process and has been chosen as the deadlock victim. Rerun thetransactionWe used to get this sort of thing when a large copy process was runningunder a transaction, but all it was doing was reading the records andcreating brand new records yet would still lock the entire table. Oncewe enabled the row versioning, we stopped having this issue, but itseems that there are some circumstances in which it still happens, i.e.the above error.Any ideas how that might occur?

View Replies !

---

Too Many Deadlocks
Hi,

I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks.
During past year, the number of users has grow (from 100 before to 500 or 700 now)


*** Deadlock Detected ***
- Requested by: SPID 360 ECID 0 Mode "S"
- Held by: SPID 113 ECID 0 Mode "S"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 113 ECID 0 Mode "S"
- Held by: SPID 374 ECID 0 Mode "X"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 374 ECID 0 Mode "IX"
- Held by: SPID 360 ECID 0 Mode "S"
Table: TABLE_2
Database: MYDB
== Lock: PAG: 22:1:2428
== Deadlock Lock participant information:
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1
Input Buf: s p _ e x e c u t e 8
Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1
== Session participant information:
== Deadlock Detected at:
==> Process 360 chosen as deadlock victim


I have done :
- rebuild indexes on all tables (fillfactor 90)
- analysed memory activity

Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?

Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.


Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700


Thanks for any help.

View Replies !

---

Deadlocks (I Think)
Hi folks,

I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.

I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

I get:

55860978LCK_M_XPAG: 13:1:2573

54AWAITING COMMANDsleeping sa 11499
55UPDATE sleeping sa 21499


respectively. Any help would be welcome.

Thanks in advance,
Don

View Replies !

---

Deadlocks
This is probably a stupid question, but...
Is there any way to totally avoid deadlocks. In some critical applications
we have removed transactions entirely, counting on other means to maintain
database consistency. We still get deadlocks in this area. These are mainly
inserts, and the only thing I can think is that updates to the indexes are
causing multiple page locks which result in deadlocks. Is this true?

Will deadlocks be eliminated in 7.0 with row level locking for this situation?
Or will index page splits still cause a possibility of deadlock contention?

ben

View Replies !

---

Deadlocks
Hi guys,

View Replies !

---

Deadlocks
Hi guys,
Does SQL Server 6.5 log deadlock errors automatically into the errorlog, or do I need to use traceflags ??

TIA
Ju.DBA

View Replies !

---

DeadLocks
I am getting the following dead lock error message writtent to the Error Log.

How do i interpret this...?


2002-07-10 11:49:52.88 spid3 Node:1
2002-07-10 11:49:52.88 spid3 KEY: 6:1531868524:1 (1e0040209980) CleanCnt:1 Mode: X Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26429de0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:62 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 62 ECID: 0 Statement Type: INSERT Line #: 67
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_Save;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)
2002-07-10 11:49:52.88 spid3
2002-07-10 11:49:52.88 spid3 Node:2
2002-07-10 11:49:52.88 spid3 KEY: 6:1695345104:1 (ffffffffffff) CleanCnt:1 Mode: Range-S-U Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26450f20 Mode: Range-S-U Flg:0x0 Ref:1 Life:02000000 SPID:58 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 58 ECID: 0 Statement Type: INSERT Line #: 250
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_IPAQManagerFetchFilterDetail;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:62 ECID:0 Ec:(0x3bb5f4f8) Value:0x2649e040 Cost:(0/2340)
2002-07-10 11:49:52.88 spid3 Victim Resource Owner:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)

View Replies !

---

Deadlocks
We have an application that runs on both Oracle 8.1.7 and SQL Server 2000. Due to some poor design, we end up with a table being inserted/updated/selected by multiple processes, almost simutaniously and randomly. Obviously, this is a recipe for deadlocks. What is a bit strange is that we rarely see deadlocks on Oracle, but lots of deadlocks on SQL 2000. One explanation I heard is that Oracle tends to use row-level locks, while SQL server tends to use page-level locks. In adition, SQL server is inefficient in dealing with index updates (we do update some indexed columns quite often).

I am not very satisfied with the explanation. Can someone please shed some lights on this?

Thanks a lot.

View Replies !

---

Deadlocks
Is there any way to totally avoid deadlocks. In some critical applications
we have removed transactions entirely, counting on other means to maintain
database consistency. We still get deadlocks in this area. These are mainly
inserts, and the only thing I can think is that updates to the indexes are
causing multiple page locks which result in deadlocks. Is this true?

Will deadlocks be eliminated in 7.0 with row level locking for this situation?
Or will index page splits still cause a possibility of deadlock contention?

Thanks!
ben

View Replies !

---

Deadlocks
Ah yes, those dreaded deadlocks!!

Well I've removed the clustered indexes, I've created non-clustered indexes, I've tried to streamline the code, but I'm still getting deadlocks.

The worst thing about them is that I don't appear able to trap them at the client end. I'm using Visual Foxpro 6, and I get an ODBC error window with "Driver is probably out of resources..." and that's that.

Are there any known methods of trapping these ODBC errors?

TIA,
Simon
UK

View Replies !

---

DeadLocks
I have a merge replication with 3 subscribers and 1 publisher. The merge
replication is continuously. The merge process is regularly stopped due to
a deadlock and the merge replication is chosen as victim. I assume that
the conflicting transaction is the backup-process. I want to automatically
detect this and restart the synchronisation. How can I do this ?

Thanks, Jay

View Replies !

---

DeadLocks
Hi ,

I have a problem with a SP in 6.5. When i try to run a Stored Proc which is a simple select statement dumped into a temp table in a particular database, I lock other users who are tring to log into other databases some in tempdb database. When i try to kill the process the rollback takes almost 45 mins or so..till then no one can log on to the server.

The SP works fine when no one is logged into the Great Plains server. One more thing i observed is that, the SP when run results on a deadlock only when the owner is a user. If the owner is DBO it works fine.

Can anybody throw some light on this.

Thanks in Advance
Siv

View Replies !

---

Deadlocks
Hi,

we have a production inviremont that is running for about 10 months. Since a couple of weeks we are having problems with "Deadlocks".

This cant be due to an increase in data size on the tables that are having the issues because these are cleaned in the same transaction that populates them.

These tables are used to store temporary data that the production system needs to calculate the correct price for any given order. This transaction takes between 0.5 to 1 second to commit.

We are running on a dual processor machine with 1 Gb of RAM with SQL Server 7 - sp 3, Windows NT 4 sp 6, Microsoft Transaction Server.

In all our queries and stored procedures we use the optimizer hints (nolock) for select statements and (rowlock) for updates or deletes.

Any help and/or suggestions would be appriciated.

View Replies !

---

Deadlocks
hi,

We have a SQL 2005 transaction database server that suddenly started to issue deadlock errors last week on most of the databases on that server and a lot of timeout errors. Before that, that database server performed very well and timeouts were minimal to zero. I am not sure what changed for it to have these performance problems.

The only major change we did was to convert several varchar columns to nvarchar in several tables (as part of internationalization initiatives). We did not modify the procs from varchar to nvarchar though but would be doing that phase by phase.

There is also one proc in which we used the snapshot isolation level of sql server 2005. These are only 2 major changes done within the past 2 weeks. Would these be the cause for these deadlocks and timeouts on our web-based application?

Any ideas?

Thx
Sri

View Replies !

---

Deadlocks On A Web Page
Morning All,
Am getting the following error from a number of users and am sort of wondering where to start in terms of diagnosing the problem. If anyone could give me any pointers on where to start in diagnosing the issue I would be grateful.
"System.Data.SqlClient.SqlException: Transaction (Process ID 282) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
 
 

View Replies !

---

Deadlocks In Profiler
I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks weregenerated by Loadrunner scripts (stress testing) simulating applicationSQL via an ODBC DSN connection.2 things are puzzling me in the SQL Profiler traces that I have logged1) There are a large number of Lock:Timeout events but the 'locktimeout' setting is the default 'wait forever' so I dont know what istiming out.2)When say 2 distinct SPIDs are in a Deadlock Chain, they are using thesame ClientProcessId at the time of deadlock. What is theClientProcessId and is it relevant to the deadlock?Thank you in advance for any replies.

View Replies !

---

Monitoring Deadlocks
SQL 2000 SP3Howdy all.I saw some code out there somewhere that logged deadlock info includingSQL code that caused it as well as the user information.Any ideas ?Thanks,Craig

View Replies !

---

Deadlocks And Use Of Nolock
I am getting lot of deadlocks in my application. As it is very complexti avoid deadlocks at this stage of application we have done few stepsto lessen the impact.We have added retries after deadlock is capturted.We have added select * from TABLE with (nolock) wherever possible.But interestingly second step is not working. I have few simple selectstatements where i am using nolock criteria still I am gettingdeadlock victim error. Any idead why it happening. I thought as soonas I put nolock in the query it will ignore all the locks.My sp isCREATE procedure sp_Check_denomination@supply_till_idint,@product_codechar(4),@iso_currency_codechar(3),@denominationmoneyasdeclare @product_id numeric(5)select @product_id = product_id from product with (nolock) whereproduct_code = @product_codeif exists (select *from transaction_inventory TI with (nolock),product_ccy_denom PCD with (nolock)where TI.supply_till_id = @supply_till_idand TI.product_id = @product_idand TI.iso_currency_code= @iso_currency_codeand TI.denomination = @denominationand TI.product_id = PCD.product_idand TI.iso_currency_code = PCD.iso_currency_codeand TI.denomination = PCD.denominationand PCD.product_id=@product_idand PCD.denomination = @denominationand PCD.iso_currency_code=@iso_currency_codeand PCD.tradeable = 1)beginreturn(1)endelsebeginreturn(0)endGO

View Replies !

---

Deadlocks In Sql Server
HiI'm having a problem with deadlocks in a table in SQL server whentrying to update it through Biztalk 2004. There is no problem when Iuse the same Biztalk solution to update a similar dummy table, butwhen I try updating the original table in the production database,some transactions are updated successfully whereas others become thevictim of the deadlock (Transaction (Process ID 185) was deadlocked onlock resources with another process and has been chosen as thedeadlock victim. Rerun the transaction). The table that is updated isalso being used by another application that just selects rows from it.As a workaround, I have used recursion in the code that updates thetable. The function is put through a recursive loop whenever thedeadlock exception(#1205) is caught. It keeps on trying to update thetable until the updation is successful or another exception (not thedeadlock one) is caught. i.e.Bool Update_IVR(string amount, string customer_id){Try{//updation code}}Catch (exception ex){If( ex.message == deadlock message){Bool succ =Update_IVR (amount, customer_id) //recursionReturn succ;}Else //error handling code}After introducing this code, the problem did not occur for the next13000 transactions. Then I got the error again four times along with atimeout error (Timeout expired. The timeout period elapsed prior tocompletion of the operation or the server is not responding). Howeverfor the next 17000 transactions (to date) this error has not showedup.ThanksHasan

View Replies !

---