I am in the process of converting an old FoxPro 2.6 based DB to MSSQL 2000..
It appears that FoxPro 2.6 stores it's null value dates as 12:00:00 AM which is causing errors when importing the data into MSSQL 2000.
The format of all of the other dates is mm/dd/yyyy
Has anyone else encountered this? is there a filter, or a a script or switch somewhere to replace the NULL values with something that MSSQL is happier with?
..:: EDIT ::..
Looking @. the properties of the affected columns, it looks like they are set as smalldatetime, but if i switch them to datetime with a length of 8 it imports just fine.
So.. now my task is to update all of the columns from smalldatetime to datetime.
sugggestions?
Thanks in advance!
Joe..::bump::..
anyone?|||Use DTS.sql
Showing posts with label based. Show all posts
Showing posts with label based. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Need Execute SQL component to fail package if zero records
I have a SSIS package that has several Execute SQL Components. One of the first components reurns a Full Result Set of IDs based on a stored procedure call. The stored procedure can return multiple rows. I store the results to an ADO recordset (object variable) to be used later. I want the component to fail, and the package if the return of the stored procedure is zero records. What is the best way to do this? I had a raise error statement if @.@.rowcount was zero but this did not fail the component. Any other suggestions?A way could be to have an extra execute sql task at the begginig with a Select count(*) from...; put the value into a variable and then use an expression in the precedence constraint to continue only if the variable value is greater than 0.|||I have thought about that as a work around. But it seems to me that I should be able to throw an error via RAISERROR or some other method in the stored procedure and have it result in the execute sql statement in the dts package to error as well.|||I don't see another way of doing using a single Execute SQL task. To be honest I don't see anything wrong on implementing pre execution logic in yiour package as far a performande does not suffer too much. Perhaps, you may want to write your own solution using an script task.|||I guess that is the path I will go. I was just figuring there may be an easier way since the Single Result Set fails if nothing is returned, I thought the Full Result Set may have been able to function in the same way. Thanks for the input.
Need Execute SQL component to fail package if zero records
I have a SSIS package that has several Execute SQL Components. One of the first components reurns a Full Result Set of IDs based on a stored procedure call. The stored procedure can return multiple rows. I store the results to an ADO recordset (object variable) to be used later. I want the component to fail, and the package if the return of the stored procedure is zero records. What is the best way to do this? I had a raise error statement if @.@.rowcount was zero but this did not fail the component. Any other suggestions?A way could be to have an extra execute sql task at the begginig with a Select count(*) from...; put the value into a variable and then use an expression in the precedence constraint to continue only if the variable value is greater than 0.|||I have thought about that as a work around. But it seems to me that I should be able to throw an error via RAISERROR or some other method in the stored procedure and have it result in the execute sql statement in the dts package to error as well.|||I don't see another way of doing using a single Execute SQL task. To be honest I don't see anything wrong on implementing pre execution logic in yiour package as far a performande does not suffer too much. Perhaps, you may want to write your own solution using an script task.|||I guess that is the path I will go. I was just figuring there may be an easier way since the Single Result Set fails if nothing is returned, I thought the Full Result Set may have been able to function in the same way. Thanks for the input.
Need distinct rows from table based on (col1) only - overall required multiple c
Need rows of specific table having distinct col1, but along with col1 I also need col2,col3, .... But distinct is required on only col1.
I tried ---
Select col1, col2, ... , coln from table1 where ( select distinct col1 from table1)
But result contain repetitive col1 rows due to wrong syntax of mine. Also tried exist, union, right join but failed finally ....
I tried ---
Select col1, col2, ... , coln from table1 where ( select distinct col1 from table1)
But result contain repetitive col1 rows due to wrong syntax of mine. Also tried exist, union, right join but failed finally ....
give your some site:
one is mser.net,another is msdnx.com,search by google in the site....
Friday, March 9, 2012
Need a trigger based on a row
Hello, everyone
I need to create a trigger to update a FLAG column by row modification. The table likes,
Col1 FLAG
a 0
b 0
c 0
d 0
e 0
If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.
Anyhelp will be approciated.
Thanks a lot
ZYTIf col1 is a primary key, this is relatively easy. If you don't have a primary key, I don't know how to do it. Can you "fill in the blanks" a bit on your requirements?
-PatP|||Col1 doesn't have to be a primary key, but you do need a primary key. Put this in your trigger:
Update YourTable
set FLAG = 1
from YourTable
inner join Deleted on YourTable.PrimaryKey = Deleted.PrimaryKey
where YourTable.Col1 <> Deleted.Col1|||So what happens when the code that launches the trigger is something like:UPDATE yourtable SET col1 = 'a'All of the rows except for one change. Without a PK, things get ugly really fast from a SQL perspective, and I'd guess that the business rules would go berzerk!
-PatP|||All of the rows except for one change.-PatP
I think that is what he would want. All of the rows except one were modified, so set the flags on all but one record.
But yes, as I mentioned in my post, he is going to need a primary key on the table.
I need to create a trigger to update a FLAG column by row modification. The table likes,
Col1 FLAG
a 0
b 0
c 0
d 0
e 0
If "a" in Col1 is changed, the "0" in same row (First row) should be changed to '1'. Other FLAG values should not be changed. The same rule for other row.
Anyhelp will be approciated.
Thanks a lot
ZYTIf col1 is a primary key, this is relatively easy. If you don't have a primary key, I don't know how to do it. Can you "fill in the blanks" a bit on your requirements?
-PatP|||Col1 doesn't have to be a primary key, but you do need a primary key. Put this in your trigger:
Update YourTable
set FLAG = 1
from YourTable
inner join Deleted on YourTable.PrimaryKey = Deleted.PrimaryKey
where YourTable.Col1 <> Deleted.Col1|||So what happens when the code that launches the trigger is something like:UPDATE yourtable SET col1 = 'a'All of the rows except for one change. Without a PK, things get ugly really fast from a SQL perspective, and I'd guess that the business rules would go berzerk!
-PatP|||All of the rows except for one change.-PatP
I think that is what he would want. All of the rows except one were modified, so set the flags on all but one record.
But yes, as I mentioned in my post, he is going to need a primary key on the table.
Monday, February 20, 2012
Near exact matches, help needed
I'm currently working on a match function to compare two char based columns in differnet tables to create a join.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
Thanks in advancePerhaps the SOUNDEX function could help you out: SOUNDEX - Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
Thanks in advancePerhaps the SOUNDEX function could help you out: SOUNDEX - Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
Subscribe to:
Posts (Atom)