Raising Errors In Validate() Method

Feb 8, 2006

Hi,

I am building a custom component. In the Validate() method I am checking for various things. Here's a section of my code:
if (ComponentMetaData.InputCollection[0].InputColumnCollection.Count != 1)
{
return DTSValidationStatus.VS_ISBROKEN;
throw new Exception("You need to select one and only one column");
}



The problem I'm finding is that my Exception never gets thrown so I never see the error in the UI. if I reverse the 2 lines like so:
if (ComponentMetaData.InputCollection[0].InputColumnCollection.Count != 1)
{
throw new Exception("You need to select one and only one column");
return DTSValidationStatus.VS_ISBROKEN;
}

Then I get my error message in the UI but I never get anything returned from my Validate() method - which doesn't seem quite right to me.

I don't like using FireError() either because it merely puts the error into the Task list window - and who ever looks at the Task list window???



I'm basically just after some guidance about what people think is the "proper" way of doing this. At the moment I prefer to throw an exception because that way I get some errors in the UI.

Regards

Jamie

View 6 Replies


ADVERTISEMENT

Raising Errors In A Catch Block In SQL2005

May 3, 2006

When a catchable error occurs in a try block, control is passed to theassociated catch block. While I am then able to examine properties ofthe error using such functions as ERROR_NUMBER() in the catch block,the error-logging scheme used in my company requires that the actualerror be raised so that it can be picked up by components in the nexttier. I appear to be unable to do that -- I can raise some other errorthat has all of the properties of the original except the error number.For example, division by zero raises error 8134. If the code thatproduces that error is enclosed in a try block, I seem to be unable toraise that error from the catch block. Of course, if I never used thetry block to begin with this wouldn't be a problem, but then wewouldn't have the advantages of this structure as it applies to othererrors, some of which we may wish to handle differently. WhileTry-Catch looks great as a way of allowing us to handle errors in acustomized way and to avoid having all errors be passed up to ourmiddle tier, it seems that we are unable to pass ANY such errorsforward, and that is a problem for us, too. Is my understanding ofthis situation correct, or is there some way around this problem, e.g.,is there any way I could have raised error 8134, in the above example?Thanks.

View 2 Replies View Related

Raising DB Update/insert Messages

Oct 23, 2007

I want to raise the custom success message after insert and update something like say 5 records updated successfully or inserted successfully?Is there a way i can do that in stored procedure?Also,can someone enlighten me on 'ReturnValue' part of [ ParameterDirection.ReturnValue;]here.
I am coding in c# ,(sql server 2005)and my insert and update codes are in DAL,which gets called from BLL like this.  public int updateRecord(int data1, string text1, string text2, bool ind, string sproc,Label lblErr) {

using (SqlConnection fbConnect = new SqlConnection(appProperty.dbconnect))
{
SqlCommand fbCmd = new SqlCommand(sproc, fbConnect);
fbCmd.CommandType = CommandType.StoredProcedure;
..... parameter stuff......
SqlParameter val = fbCmd.Parameters.Add(new SqlParameter("@ReturnValue", SqlDbType.Int));
val.Direction = ParameterDirection.ReturnValue;
try{
fbConnect.Open();
fbCmd.ExecuteNonQuery();}
catch(Exception ex){lblErr.Text = ex.Message}
int iSuccess = (int)fbCmd.Parameters["@ReturnValue"].Value;//I am NOT using this effectively.How can i make use of it?
return iSuccess;

}

View 3 Replies View Related

Raising Error From Trigger To Stored Proc

Jul 23, 2005

I feel like I'm missing something obvious here, but I'm stumped...I have a stored procedure with code that looks like:INSERT INTO MyTableA ( ...fields... ) VALUES (...values...)IF (@@ERROR <> 0)BEGINROLLBACK TRANSACTION;RAISERROR('An error occurred in the stored proc.', 16, 1);RETURN(1);END--FORCING AN ERROR AT THE END FOR TESTING PURPOSESRAISERROR('Proc Successful',16,1)On MyTableA, there is a trigger that loops through the inserted data andstops the insert in certain circumstances, returning an error:IF (some criteria)BEGINROLLBACKRAISERROR('An error occurred in the trigger.',16,1)RETURNENDWhen I call the stored procedure from VB (connecting via RDO) witherror-causing data, the trigger successfully stops the insert, and adds thetrigger-error-msg to the errors collection, but it does NOT seem to createan error situation back in the stored procedure. The procedure finishes upwith the "Proc Successful" message, so that when I iterate through theerrors collection back in VB, I have "Proc Successful" followed by "An erroroccurred in the trigger."Is there some way I'm not finding to have the calling procedure recognizethat a raiserror occurred in the trigger and behave appropriately for anerror situation?Jen

View 1 Replies View Related

Script Task Raising OnError Events

Oct 11, 2005

Is there a way I can stop a script task of a control flow from raising OnError Events? or Is there a way I can Set Dts.Results to Fail without having the script task raise OnError event. 

View 8 Replies View Related

Raising User Friendly Message In Case Of DB Error

Oct 20, 2007

Hi,I am using sql server 2005 and  i am having table with a column which has unique key constraint.So if the user tries to enter the duplicate value for that particular column and try to save it ,it errors out in runtime..how do i raise the user friendly error message in this DB error, when user tries to save it.Thanks

View 9 Replies View Related

Send Request To Stored Procedure From A Method And Receive The Resposne Back To The Method

May 10, 2007

Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString)    {      //call stored procedure  } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.  

View 3 Replies View Related

Update Method Is Not Finding A Nongeneric Method!!! Please Help

Jan 29, 2008

Hi,
 I just have a Dataset with my tables and thats it
 I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
 Please help if anyone has a solution
 
Thanks

View 7 Replies View Related

How To Validate Tables In DTS

Jan 4, 2000

Hai,

Can any one suggest me how to use the DTS to validate and transfer data from couple of tables of one database to different Server database
Is there any procedure how to pass variables (validations) while using DTS.
Can I use the following code for validation in DTS, if so can any one direct me how to use.


For example"

DECLARE CUR_X CURSOR FOR
SELECTsource column FROM sourcetable
WHERE sourcetable.sourcecolumn <> [some value]
ORDER BY sourcetable.sourcecolumn
OPEN CUR_X
FETCH NEXT
INTO CUR_X WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO destinationserver.destinationtable(Destination columns)
SELECT source columns
FROM source table
WHERE source column = @sourcecolumn

Will appreciate for your time

View 1 Replies View Related

Validate Telephone No.

Aug 6, 2004

i want this column to accept telephone number of this format
000-000-0000

what's validation expression do i use? or how can get this db to accept this tel no. format
i use varchar for data type
thanks

View 2 Replies View Related

How Do You Validate And Scrub Using DTS?

Feb 11, 2004

When I use the DTS GUI and insert a "Bulk Insert Task" the main tab says:

"Import text files into SQL Server. You cannot validate, scrub, or transform data using this task".

So my question is, what shoud you use to validate and scrub?

In particular I have fixed-format text file with some occasional bad records (e.g. wrong length, empty record). What should I be using? If you suggest vbscript could you show me some examples? I'm new to vbscript.

Thanks!

View 2 Replies View Related

How To Validate Timestamp

Aug 31, 2013

How to validate timestamp,i have tried so many time ,but iam not getting correct result, hwo to validate source data has timestamp or not ? i would like result s if the data is timestamp that should be 1, if the data is not timestamp that should be 0 ,iam using code like this

CASE
WHEN TO_CHAR(OE_TECH_VLD_FROM_DTTM,'ddmonyyyy:hh:mi:ss.sssssss') then 1
ELSE 0
END

View 7 Replies View Related

Validate By Column Name

Jan 17, 2006

Hi,
I would like to validate a large file using an IS package before importing it into a table using IS. The Validation rules are stored in the database against each column name

Question
1. Is there a way I can get the column names of Input (coming from the file)? (So I could check them against the validation table)
2. Can I store these rules in memory, may be using an array if so how do i create an array?
3. What would be the best way to go abt doing this?? (I really appreciate any ideas)

Appreciate your help...

View 1 Replies View Related

DTExec /validate

May 23, 2008

HI, with a dataflow that has delay validation property = true, DTExec will not try to validate it when I call it with /validate option. Is there a way to see if the dataflow validate even though the delay validation property is set to true?

Thanks,

View 3 Replies View Related

Help Needed To Validate 2 Users At Once

Sep 12, 2007

hi all, i want to ask about validating 2 users (2 userids and 2 pws) with one button. my database table called login and it has loginId and password.have tried
"SELECT * FROM login Where loginId = '" + hoName + "', '" + toName + "' AND password = '" + hoPassword + "', '" + toPassword + "'"
please help me!!

View 5 Replies View Related

Validate Sql Statements Before Inserting?

Mar 31, 2005

Hi experts!

I need to know how can i validate the sql querries so that they don't contain any scripts.

I am not using any web application or any other application.

I am only using query analyzer to write statements and execute.

So all my co workers are doing the same thing to insert the information
in database by using query analyzer. And at the end of the each day the
database files are shipped at another warehouse.

So where in sql server 2000/7 i can validate those statements before inserting them.

I don't want the my co-workers to know that i am validating their querries.

Thanks

View 3 Replies View Related

How To Validate Timestamp In ETL Studio

Aug 31, 2013

How to validate timestamp, I have tried so many time ,but iam not getting correct result, hwo to validate source data has timestamp or not ? i would like result s if the data is timestamp that should be 1, if the data is not timestamp that should be 0 , I am using code like this :

................................................................
CASE
WHEN TO_CHAR(OE_TECH_VLD_FROM_DTTM,'ddmonyyyy:hh:mi:ss.sssssss') then 1
ELSE 0
END
..................................................................

View 1 Replies View Related

Validate Subscriptions, Turn It Off

Feb 17, 2006

I added Validate Subscriptions to my publication using the procedure outlined in BOL. Procedure is listed below. I want to stop the validation for this publication. I do not want to validate any longer. How can I stop this process?
To validate transactional data using SQL Server Enterprise Manager

At the Distributor, expand Replication Monitor, expand Publishers, and then expand a specific Publisher.


Right-click a transactional publication, and then click Validate subscriptions.


Choose whether you want to validate all subscriptions or just specific subscriptions, and if you want to validate specific subscriptions, select those in the text box.


To choose the type of validation, click Validation Options.


Choose whether you want to compute a fast rowcount based on cached table information, compute an actual row count by querying the tables directly, or compute a fast row count and if differences are found, compute an actual row count.


You can also choose to enable Compare checksums to validate data, a binary checksum (if the Subscriber is running SQL Server 2000), and you can choose to stop the Distribution Agent after the validation has completed.

View 5 Replies View Related

How To Validate The Parameter Values?

Apr 25, 2007

Hi,

Is there any way to validate the input paratemers for the report? For example: I want to restrict the value to be less than 100 in one parameter. How to achieve this?



Thnx in advance.

View 2 Replies View Related

Validate Stored Procedure

Aug 23, 2006

Can i enforce the sql to refuse create the stored procedure if it select of non existing table?

View 3 Replies View Related

Could Not Validate The Service Accounts

Mar 22, 2008



Trying to install Backup Exec 12 which comes bundled with SQL Server 2005 Express.
OS is a clean install of Swedish Windows Server 2003 Std R2, fully patched.


SQL fails to install, and the following is in the SQL summary-log:

Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.2.3042.00
Install : Failed
Log File : C:ProgramMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0002_VAXSRV02_SQL.log
Last Action : Validate_ServiceAccounts
Error String : SQL Server Setup could not validate the service accounts. Either the service accounts have not been provided for all of the services being installed, or the specified username or password is incorrect. For each service, specify a valid username, password, and domain, or specify a built-in system account.
The logon account cannot be validated for the service SQL Server.
Error Number : 28075

Install log:
"C:Documents and SettingsadministratorSkrivbordBEWS_12.1364_32BIT_VERSIONWINNTINSTALLSQLExpressSQLEXPR.exe" /wait /qn /norestart /lv "C:ProgramMicrosoft SQL Server90Setup BootstrapLOGSummary.txt" INSTANCENAME=BKUPEXEC INSTALLSQLDIR="C:ProgramMicrosoft SQL Server" INSTALLSQLDATADIR="C:ProgramMicrosoft SQL Server" INSTALLSQLSHAREDIR="C:ProgramMicrosoft SQL Server" SQLACCOUNT="NT AUTHORITYSYSTEM" SQLPASSWORD="" ADDLOCAL=SQL_Engine,SQL_Data_Files,SQL_Replication,Client_Components,Connectivity SAPWD=**** DISABLENETWORKPROTOCOLS=0
03-19-2008,13:52:10 : V-225-53: ERROR: Failed to install SQL Express BKUPEXEC instance with error 28075.


Since the installation of SQL is bundled with the Backup Exec installation, there is no(?) possibility for me to specify usernames for the different services. The Backup Exec installation is initiated under the Domain Admin's login.

I suspect the problem occurs because of the OS not being English, but I am not sure. Have installed earlier versions of Backup Exec with SQL Server 2005 Express, on Swedish Windows Server 2003, before without issues.
No help at Veritas/Symantec's homepage.

Grateful for any help.

View 4 Replies View Related

When Does ConnectionManagerBase.Validate() Get Executed?

Feb 13, 2008

[Microsoft follow-up]

Hi,
I'm pondering building a custom connection manager and within the Validate() method I want to initiate a connection to a SQL Server instance, check something out, and then close he connection at the end of the method. Here's the code I have so far:




Code Snippet
public override Microsoft.SqlServer.Dts.Runtime.DTSExecResult Validate(Microsoft.SqlServer.Dts.Runtime.IDTSInfoEvents infoEvents)
{
DTSExecResult execRes = DTSExecResult.Success;
if (String.IsNullOrEmpty(_serverName))
{
infoEvents.FireError(0, "SqlConnectionManager", "No server name specified", String.Empty, 0);
execRes = DTSExecResult.Failure;
}
else
{

//Establish a connection and check that it is pointing to an MDM DB
SqlConnection sqlConnToValidate = new SqlConnection();
sqlConnToValidate.ConnectionString = this._connectionString;
try
{
sqlConnToValidate.Open();
if (!IsMDMDatabase(sqlConnToValidate))
{
execRes = DTSExecResult.Failure;
}
}
catch (Exception e)
{
infoEvents.FireError(0, "MDMConnectionManager", e.Message, String.Empty, 0);
execRes = DTSExecResult.Failure;
}
finally
{
if (sqlConnToValidate.State != ConnectionState.Closed)
sqlConnToValidate.Close();
}
}
return execRes;
}






I'm worried about the overhead of establishing a connection every time Validate() is called. So the questions are


When does Validate() get called?

Does it get called for every component/task that uses it?

Does anyone suspect that what I'm doing here is necassarily a bad thing or not?

Thanks
Jamie

View 5 Replies View Related

SQL Server 2008 :: How To Make Sproc Return Errors For Underlying Table Errors

Jul 1, 2015

I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.

The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?

View 1 Replies View Related

How To Create My Validate Login SP IN Sql 2000 ?

Jan 24, 2006

hi,all:
         I'm new to Sql 2000,now I have a login  asp.net page and I used the sql 2000 database.
my login page included a user id and password need user inputed.  if the user input the correct userid
and password ,IE will transfer to main  page,or there will show eorror message in login page.
my SP like this:
 CREATE  PROCEDURE dbo.Usp_Accounts_ValidateLogin  @userid char(4) , @EncPassword  binary AS      if  (select  count(*)    from  hhmxUserData where Userid=@userid and UserPWD=@EncPassword) >0  
         return  1
     else
       return 0GOmy asp.net code like this:
      dim result As Integer
     dim rowsAffected as integer
            myConnection.Open()            Dim command As SqlCommand = BuildIntCommand(storedProcName, parameters)                rowsAffected = command.ExecuteNonQuery                result = CInt(command.Parameters("ReturnValue").Value)                myConnection.Close()
            Return result
I test it in sql 2000,it's ok.but when I performed it and retrieve the "returnValue", it still return 0 .
so how can I create my correct SP  ?
 
thanks so much.

View 3 Replies View Related

Validate Data Type Before Inserting

Feb 26, 2003

I have a temp table which is used to store data before inserting to the real permanent tables. All columns of the temp table have nvarchar type.

How do I validate the data in temp table before doing the actual insert? For example, I need to ensure a field is a valid Datetime (currently in temp of nvarchar) before inserting to a Datetime field. Can this be done using Transact-SQL?

Thanks.

View 1 Replies View Related

Write A Query To Validate Data

Sep 10, 2014

I'm trying to write a query to validate the data.

Here is the scenario:
1. The table has Three columns 1.ID, 2.Sqno, 3. Adj
2. The values for adj are (0,1,2)

Case1: The Sqno should start at '001000' for adj in (0,2) and increment by 2, i.e the next sqno would be '001002' and '001004' so on.
Case2: The sqno should start at '001001' for adj in (1) and increment by 2 i.e the next sqno would be '001003' and '001005' so on.

Finally when you do order by sqno and group by ID it will be a running sqno.

ID Sqno Adj
123A 001000 0
123A 001001 1
123A 001002 2
123A 001003 1
123A 001004 2
123A 001005 1
123A 001006 0
123A 001007 1
123A 001008 2
123A 001009 1

write a query that can validate this scenario.

View 2 Replies View Related

SQL Query Check And Validate (Urgent)

Jan 9, 2007

Hello All,

It seems the following queries are causing my DB connections to time out, but I cant seem to figure out why.

These queries reside within my xml.config file.

The connection to the DB is fine and live. I get timeout errors every few hours or so.

Can anyone take a look at the queries which I pasted below and tell me why I keep getting timeout errors? PLEASE, I need all the help I can get.

<query name="Products" rowElementName="Product">
<sql>
<![CDATA[
SELECT p.*,
pv.VariantID, pv.name VariantName, pv.Price, pv.Description VariantDescription, isnull(pv.SalePrice, 0) SalePrice, isnull(SkuSuffix, '') SkuSuffix, pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points,
sp.name SalesPromptName,
isnull(e.Price, 0) ExtendedPrice
FROM Product p
join productvariant pv on p.ProductID = pv.ProductID
join SalesPrompt sp on p.SalesPromptID = sp.SalesPromptID
left join ExtendedPrice e on pv.VariantID=e.VariantID and e.CustomerLevelID=@CustomerLevelID
WHERE p.ProductID = @ProductID
and p.Deleted = 0
and pv.Deleted = 0
and p.Published = 1
and pv.Published = 1
ORDER BY p.ProductID, pv.DisplayOrder, pv.Name
]]>
</sql>
<queryparam paramname="@CustomerLevelID" paramtype="runtime" requestparamname="CustomerLevelID" sqlDataType="int" defvalue="0" validationpattern="" />
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" />
</query>

<query name="Ratings" rowElementName="Rating">
<sql>
<![CDATA[
SELECT ProductID, CAST ( AVG(CAST(Rating AS decimal)) as decimal (10,2)) as Rating
FROM Rating
WHERE ProductID = @ProductID
Group By ProductID
ORDER BY ProductID
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" />
</query>

<query name="TotalRatings" rowElementName="TotalRating">
<sql>
<![CDATA[
SELECT ProductID, Count(Rating) as TotalRating
FROM Rating
WHERE ProductID = @ProductID
Group By ProductID
ORDER BY ProductID
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" />
</query>

<query name="popup" rowElementName="popit">
<sql>
<![CDATA[
SELECT ProductID as rateID
FROM Product
WHERE ProductID = @ProductID
Group By ProductID
]]>
</sql>
<queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^d{1,10}$" />
</query>

View 9 Replies View Related

Validate INT, SMALLINT, TINYINT & DECIMAL

Jan 10, 2008

Hi all,

I found a UDF on the web to validate INT data contained in a VARCHAR field:

http://blog.sqlauthority.com/2007/08/11/sql-server-udf-validate-integer-function/

I modified it to accept NULL values and conform more closely to INT specification. Here is my modified function:


CREATE FUNCTION [dbo].[udfIsValidINT]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @ShiftByOne INT;
IF LEFT(@Number, 1) = '-'
SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1;
SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0)
IF (PATINDEX('%[^0-9-]%', @Number) = 0
AND CHARINDEX('-', @Number) <= 1
AND @Number NOT IN ('.', '-', '+', '^')
AND LEN(@Number)>0
AND LEN(@Number)<11
AND @Number NOT LIKE '%-%')
SELECT @Ret = CASE WHEN CONVERT(BIGINT,@Number) - @ShiftByOne <= 2147483647
THEN 1 ELSE 0 END
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidINT('2147483648')
SELECT dbo.udfIsValidINT('2147483647')
SELECT dbo.udfIsValidINT('-200')
SELECT dbo.udfIsValidINT('-2147483649')
SELECT dbo.udfIsValidINT('32900')
SELECT dbo.udfIsValidINT('1.79E+308')
GO


I also have a separate function for SMALLINT:

CREATE FUNCTION [dbo].[udfIsValidSMALLINT]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @ShiftByOne INT;
IF LEFT(@Number, 1) = '-'
SELECT @Number = SUBSTRING(@Number, 2, LEN(@Number)), @ShiftByOne=1;
SELECT @Number = COALESCE(@Number,'0'), @ShiftByOne = COALESCE(@ShiftByOne,0)
IF (PATINDEX('%[^0-9-]%', @Number) = 0
AND CHARINDEX('-', @Number) <= 1
AND @Number NOT IN ('.', '-', '+', '^')
AND LEN(@Number)>0
AND LEN(@Number)<6
AND @Number NOT LIKE '%-%')
SELECT @Ret = CASE WHEN CONVERT(INT,@Number) - @ShiftByOne <= 32677 THEN 1 ELSE 0 END
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidSMALLINT('589')
SELECT dbo.udfIsValidSMALLINT('-200')
SELECT dbo.udfIsValidSMALLINT('-32900')
SELECT dbo.udfIsValidSMALLINT('32900')
SELECT dbo.udfIsValidSMALLINT('1.79E+308')


and one for TINYINT:


CREATE FUNCTION [dbo].[udfIsValidTINYINT]
(
@Number VARCHAR(100)
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @L TINYINT;
SET @L = LEN(@Number);
SET @Number = COALESCE(@Number,'0');
IF (PATINDEX('%[^0-9]%', @Number) = 0
AND @L>0
AND @L<4)
SELECT @Ret = CASE WHEN CONVERT(SMALLINT,@Number) < 256 THEN 1 ELSE 0 END
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidTINYINT('256')
SELECT dbo.udfIsValidTINYINT('-1')
SELECT dbo.udfIsValidTINYINT('0')
SELECT dbo.udfIsValidTINYINT('255')
SELECT dbo.udfIsValidTINYINT('1.79E+308')


And, finally, a separate function for DECIMAL validation:

CREATE FUNCTION [dbo].[udfIsValidDECIMAL]
(
@Number VARCHAR(100),
@Scale TINYINT,
@Precision TINYINT
)
RETURNS BIT
BEGIN
DECLARE @Ret BIT, @L TINYINT, @DSI TINYINT;
SET @Number = COALESCE(@Number,'0');
IF LEFT(@Number, 1) = '-'
SELECT@Number = SUBSTRING(@Number, 2, LEN(@Number));
SET @L = LEN(@Number);
SET @DSI = @L - LEN(REPLACE(@Number,'.',''))
IF(
PATINDEX('%[^0-9.]%', @Number) = 0
ANDCHARINDEX('-', @Number) = 0
AND@DSI <= 1
AND@L>0
AND@L<=@Scale+@DSI+ CASE @DSI WHEN 1 THEN @L-CHARINDEX('.', @Number) ELSE 0 END
AND @Scale - @Precision >= CASE @DSI WHEN 1 THEN CHARINDEX('.', @Number) - 1 ELSE @L END
)
SELECT @Ret = 1
ELSE
SET @Ret = 0
RETURN @Ret
END
GO
SELECT dbo.udfIsValidDECIMAL('256',2,0)
SELECT dbo.udfIsValidDECIMAL('-1',1,0)
SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,17)
SELECT dbo.udfIsValidDECIMAL('10.123456789123456789',18,16)
SELECT dbo.udfIsValidDECIMAL('-255.0000000000000001',3,0)
SELECT dbo.udfIsValidDECIMAL('1.79E+308',9,2)


Node that the DECIMAL validation function specifically tests whether the input number can legally convert to a given decimal scale and precision. Converting a value of 0.234234 over to DECIMAL(1,0) will work, but SQL will truncate the actual decimals to fit it in that space. However, it will throw an error if you have too many whole digits.

On the whole, I was rather rushed to get these created, so there may be some errors I didn't notice. I'm interested in any improvements you guys can make to improve performance or make them cleaner.

Thanks for looking!

- Shane

View 2 Replies View Related

Xml Task: How To Validate Using Multiple Schemas

Mar 26, 2007

I have an xml task that I have set up to validate my xml using a XSD. It seems to be working OK. However, I have had to wrap my xml in a SOAP envelope before I send it to the validation task so I need to include an additional schema for the soap message header. That "header" schema has an <xsd:import> of the soap envelope schema. When I try to <xsd:include> the message header schema I get this:

"Warning 313 The targetNamespace 'blah blah' of included/redefined schema should be the same as the targetNamespace ' blah blah blah' of the including schema."

Is it not possible to use the Xml Task to validate the entire document including the SOAP Envelope due to the differing target namespaces? Thanks for any suggestions.

(I have SP2 installed.)

View 2 Replies View Related

Make A Validate Rule In A Table

Nov 11, 2006

Hi

In access i can make a rule
like if i have a Coloumn to date
i can make a rule to say that this fields data
shall be > date

can i do this also in sql and how?

regards

alvin

View 1 Replies View Related

T-SQL (SS2K8) :: Script To Validate DB Objects Before And After Migration

Nov 6, 2014

I am looking for a script which capture the object details like constraints, clustered & non-clustered indexes, primary & foreign keys, etc before migration & also after migration and give me the differences after comparison if any. Since this is on production environment, we do not have approval to use any comparison tool. Being a DBA, I am very poor in TSQL programming.script which can compare the differences before & after migration.

View 7 Replies View Related

How To Validate File Name Consist Of Todays Date

Apr 5, 2014

I have file name like clm_05-04-2014.I need to valid whether file name consist of todays date in SSIS or Sqlserver.

If the file name is clm_04-04-2014 then this is yesterday's file so not valid.When i run ths SSIS package today the file name should consist of todays date in file else i need to pass message as invalid file .CLM will be common..

View 1 Replies View Related

• Validate Data Content On Production And Secondar

Mar 14, 2008

Hi, we are using sql 2000, what would be the good way to validate different databases current data with seconday server's data after logshipping applied? thank you

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved