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.
How to capture the Error Msg 8114 in sql server 2000.
Below is the sample code i have used.
CREATE TABLE Test ( Column1 int IDENTITY, Column2 int NOT NULL )
USE tempdb go ALTER PROCEDURE CHK_INSERT @Column2 int AS DECLARE @ExecQuery nVARCHAR(4000) set @ExecQuery='INSERT test VALUES ('+cast(@Column2 as varchar)+')' EXEC (@ExecQuery) IF @@ERROR <>0 BEGIN PRINT 'Error Occured' END
Ok, so I have some horribly convuluted SQL that I would love to optomize. I'm not happy leaving it in it's current state, that's for sure!
I'm currently working on our test bed servers, so obviously my stats are out because of the "crap-ness" (yes, that's the technical term) of the hardware, but still, it should NEVER need to take this long!!
Basically, the issue arises in the nasty join to the career table (one employee can have multiple career lines). Just to make things complicated, employees can have any number of career records on any given date, these can even be input for future career events. The following SQL picks out the latest-current career date for each employee based on the career_date being <= GetDate() and the date of entry for this date being the greatest.
From the above we want to return 2007-01-01 | 2006-05-05 13:54:18.000
SET STATISTICS IO ON SET STATISTICS TIME ON
SELECT a.sAMAccountNameAs 'sAMAccountName' , a.userPrincipalNameAs 'userPrincipalName' , 'TRUE'As 'Modify' , RTRIM(e.unique_identifier)As 'employeeID' , RTRIM(e.employee_number)As 'employeeNumber' , RTRIM(e.known_as) + CASE WHEN RTRIM(e.surname) IS NOT NULL THEN ' ' + RTRIM(e.surname) ELSE NULL ENDAs 'displayName' , RTRIM(e.known_as)As 'givenName' , RTRIM(e.surname)As 'sn' , RTRIM(c.job_title)As 'title' , RTRIM(c.division)As 'company' , RTRIM(c.department)As 'department' , RTRIM(l.description)As 'physicalDeliveryOfficeName' , RTRIM(REPLACE(am.dn,'\',''))As 'manager' , t.full_mobile + CASE WHEN RTRIM(t.mobile_number) IS NOT NULL THEN ' (DD: ' + RTRIM(t.mobile_number) + ')'ELSE NULL END As 'mobile' , t.mobile_numberAs 'otherMobile' , ad.address_ad_countryAs 'c' , ad.address_ad_address1 + CASE WHEN ad.address_ad_address2 IS NOT NULL THEN ', ' + ad.address_ad_address2 ELSE NULL END + CASE WHEN ad.address_ad_address3 IS NOT NULL THEN ', ' + ad.address_ad_address3 ELSE NULL END + CASE WHEN ad.address_ad_address4 IS NOT NULL THEN ', ' + ad.address_ad_address4 ELSE NULL END + CASE WHEN ad.address_ad_address5 IS NOT NULL THEN ', ' + ad.address_ad_address5 ELSE NULL ENDAs 'streetAddress' , ad.address_ad_poboxAs 'postOfficeBox' , ad.address_ad_cityAs 'l' , ad.address_ad_CountyAs 'st' , ad.address_ad_postcodeAs 'postalCode' , RTRIM(ad.address_ad_telephone) + CASE WHEN RTRIM(a.othertelephone) IS NOT NULL AND RTRIM(ad.address_ad_telephone) IS NOT NULL THEN ' (Ext: ' + RTRIM(a.othertelephone) + ')' ELSE CASE WHEN RTRIM(a.othertelephone) IS NOT NULL AND RTRIM(ad.address_ad_telephone) IS NULL THEN 'Ext: ' + RTRIM(a.othertelephone) ELSE NULL END ENDAs 'telephoneNumber' FROM employee e LEFT JOIN career c ON c.parent_identifier = e.unique_identifier AND c.career_date =( SELECTmax(c2.career_date) FROMpwa_master.career c2 WHEREc2.parent_identifier = c.parent_identifier ANDc2.career_date <= GetDate() ) AND c.datetime_created =( SELECT max(c3.datetime_created) FROMpwa_master.career c3 WHEREc3.parent_identifier = c.parent_identifier ANDc3.career_date = c.career_date ) LEFT OUTER JOIN AD_Import am ON am.employeeNumber = c.manager_number INNER JOIN AD_Import a ON a.employeeID = e.unique_identifier LEFT JOIN AD_Telephone t ON t.unique_identifier = e.unique_identifier LEFT JOIN AD_Address ad ON ad.address_pwa_location = e.location LEFT JOIN xlocat l ON l.code = c.location WHERE (a.employeeNumber IS NOT NULL OR a.employeeID IS NOT NULL)
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times: CPU time = 15203 ms, elapsed time = 8114 ms.
Any advice on what I can do to optomize?
Oh judt to point out that "employee" is a view on the "Table 'people'." EDIT: I know it's pointing out the obvious, but I'm pulling out the managers "DN" from AD_Import based on the manager_number and employeeNumber matching.
Hi All,I have read about deadlocks here on Google and I was surprised to readthat an update and a select on the same table could get into adeadlock because of the table's index. The update and the selectaccess the index in opposite orders, thereby causing the deadlock.This sounds to me as a bug in SQL Server!My question is: Could you avoid this by reading the table with a'select * from X(updlock)' before updating it? I mean: Would thisresult in the update transaction setting a lock on the index rowsbefore accessing the data rows?Merry Christmas!/Fredrik Möller
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
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?
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?
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
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.
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:
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
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?
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.
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?
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!
When i exec an sp it runs a sum, sometimes it is possible that there is a 0 value, it then returns an error Divide by zero error encountered How can i work around this error
My sp code is
CREATE proc CP_avgloss_rings @mID varchar(10), @startdate datetime, @enddate datetime as select ((sum(vtp)-(sum(moneyout)))/100) / ((sum(playtime))/ 3600) as avgloss from dbo.rings where machineID = @mID and convert(varchar,njdate,121) between convert(varchar,@startdate,121) and convert(varchar,@enddate,121) GO
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
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.
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
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.
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.
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.
