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
ADVERTISEMENT
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
Dec 9, 2007
Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
View 9 Replies
View Related
Dec 9, 2007
Hi can anyone help me with the format of my stored procedure below.
I have two tables (Publication and PublicationAuthors). PublicaitonAuthors is the linking table containing foreign keys PublicaitonID and AuthorID. Seeming as one Publication can have many authors associated with it, i need the stored procedure to create the a single row in the publication table and then recognise that multiple authors need to be inserted into the linking table for that single PublicationID. For this i have a listbox with multiple selection =true.
At the moment with the storedprocedure below it is creating two rows in PublicaitonID, and then inserting two rows into PublicationAuthors with only the first selected Author from the listbox??? Can anyone help???ALTER PROCEDURE dbo.StoredProcedureTest2
@publicationID Int=null,@typeID smallint=null,
@title nvarchar(MAX)=null,@authorID smallint=null
AS
BEGIN TRANSACTION
SET NOCOUNT ON
DECLARE @ERROR Int
--Create a new publication entry
INSERT INTO Publication (typeID, title)
VALUES (@typeID, @title)
--Obtain the ID of the created publication
SET @publicationID = @@IDENTITY
SET @ERROR = @@ERROR
--Create new entry in linking table PublicationAuthors
INSERT INTO PublicationAuthors (publicationID, authorID)
VALUES (@publicationID, @authorID)
SET @ERROR = @@ERROR
IF (@ERROR<>0)
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
View 12 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
May 19, 2006
Hi,
I have a set of packages, which I need to run through Sql Agent to automate it. This package runs Ok when I use DtexecUI but when I run it through SQl Agent with the same options it fails.
THe error it fails on is the old 0xC0202009, acquire connection failed error. The component on which it fails is an access database with the location and query fed by variables that pick up this from a table in a sql server database. THe access database is on a shared drive on a different machine
Does anyone have any idea as to why this should be happening
Thanks
Nathan
View 3 Replies
View Related
Dec 11, 2007
Does the validation time of DTSX package depend on the amount of data?
I have a data transfer task which contains a oledb source data from a SQL Server 2005 view. Then the data go through 3 Lookup transfromations before going into another view, also on SQL Server 2005 but a different database.
The purpose of this package is to load fact data, so it has to deal with few million of rows. Before setting DelayValidation to true, it takes few minutes just to open the package in BI Studio. Now with DelayValidation set to false, I can open the package without any problem. But it takes more than 5 minutes during Validation, Prepare for Execution and Pre-execution Phase. During the time the memory usage and cpu time on SQL Server goes up significantly. The CPU doesn't hit 100% though. My client machine doesn't have any significant activity.
I have similar packages ( Oledb view -> 3 or 4 lookup Transformations -> Oledb Table) but dealing with dimension data. With DelayValidaion set to False, those packages can be opened in BI Studio within few seconds. They also take only few seconds during those 3 phases before starting actual execution phase.
So I have an impression that the validation time depends on the amount of data in the database. Shouldn't it just depends on the metadata?
View 5 Replies
View Related
Apr 3, 2007
I want to validate my input report parameters while user clicks the View Report Button. How can I accompalish this in SSRS.
ex: Report parameters should accept only numberic values.
View 4 Replies
View Related
Dec 6, 2014
I am importing data from a legacy system. The date column is a varchar and in the destination DB it is a proper DateTime.
That said I have invalid data and need to clean / accommodate for it as I go. What I need is if the date is valid then insert it, if not then ue a default of '01/01/1900' I am doing the following and it is not working.
CASE
WHEN isDATE(RatifiedDate) = 1 THEN RatifiedDate
WHEN isDate(RatifiedDate) = 0 THEN '01/01/1900'
END as RatDate
<hr noshade size='1' width='250' color='#BBC8E5'>
View 6 Replies
View Related
Aug 20, 2006
I have a package with an XML Task with OperationType = Validate. The source is a string variable and the second operand is a file connection to a schema file. The schema file <include>'s other schemas. I run the package in debug mode in Visual Studio with no errors. I exit Visual Studio. Then I open Visual Studio again and run the package in debug mode like before and it fails in the xml task with error: "Task failed to validate "No schema to use in validation was found.". I don't understand why it fails since no changes were made. The strange thing is that if I click the File Connection Manager for the schema file and click Browse and then reselect the same schema file again then it runs OK. Note that I have to exit Visual Studio and not just close the package in order to repro the error. It seems like exiting Visual Studio frees some resource that is essential to the validation. Reselecting the schema file in the Connection Manager seems to restore the resource. (SP1)
View 2 Replies
View Related
Aug 15, 2007
[This is one of those cases where I think I know the answer, but I hope I'm wrong!]
I have a data flow which is processing data from the XML Source. There are 16 outputs from the XML Source. I have to perform a variety of validations on these outputs: things like "column 1 is required if column 2 has value 'a' or 'b'", or "column 1 or column 2 may be present, but not both".
For lookups and such, I use the Lookup component and its error output, both to redirect rows that fail the lookup, and to capture the data, column number and error code.
But, how do I do the same for "normal" validations?
If I have to use the Conditional Split transform, then I'll have to have one output per validation, and use a Union All to combine the rows again for output to an error file. This will also cost an extra "Derived Column" transform per output, in order to get a column number and possible error code per failed validation.
Worse, it's a pain to have to maintain all the columns in such a large "Union All"!
If I had the time, I might write a "Conditional Error" transform. It might be fun. But I have to be done by the end of this month, and don't even have time to create the UI for evaluating expressions!
Any tips or tricks or pointers to such would be very welcome.
View 9 Replies
View Related
