|
With SQL Server SSIS add a data transformation task and add a datasource (where the bad data is) and a destination (scrubbed data). drag green arrow from source to destination.
Add a second destination and set on failure of first destination to redirect to second (red arrow). You can use a flat file if you want.
|
|
|
|
|
I NEVER use transforms in the ETL layer. I load the data in a table using large varchar fields and use a procedure to do the transforms. This maybe (is) old fashioned but I find I have much better control over the data using a procedure. I can manage the exceptions more to my liking and the bloody thing doesn't break in the middle of the load.
One reason I dislike SSIS and hate Biztalk is the complexity they instill into a basically simple operation.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
One of our clients have recently had one of their two servers changed. The server that got changed contained Active Directory with all the permissions for all users. The other server has MS SQL Server 2005 installed.
Since the server change, users cannot use the Management Studio from their client machines to log on to SQL Server using Windows Authentication. I'm presuming that this is because since the server change, there is some sort of security ID which has changed meaning that the SQL Server doesn't recognise it even though ther name of the new server is the same and the name of the domain is still the same.
My question is this, what can I do about this??
Thanks in advance for any help.
|
|
|
|
|
Look at the system, security and application logs on SQL Server. Look for security failures and dns errors. yell at the admins.
rinse and repeat..
|
|
|
|
|
Have been doing some research and it seems that I need to update the SIDs in the master database for each login to match those of the newly created users in Active Directory.
If anyone knows of a way to do this then it would be appreciated. Thanks.
|
|
|
|
|
Hi,
Sounds like some or all your logins have orphaned. Check if this is true with sp_validatelogins
Mika
|
|
|
|
|
hi,
I don´t know if i can perform this in sql.
I trigger one particular store_procedure, (for exemple select * from tablename), but i want to focus a particular row, or make the scroll center that row.
Well i think i have to write a procedure with a parameter (id).
But how can a perform this?! if possible 
nelsonpaixao@yahoo.com.br
|
|
|
|
|
nelsonpaixao wrote: i want to focus a particular row, or make the scroll center that row
Data presentation detail like that is not what the database engine does. You are asking about something that happens, or should be happening, in the presentation layer.
|
|
|
|
|
IS batch_control an automatic table in SQL Server database? can anyone tell me where I can find the knowledge about it? Thanks very much.....
|
|
|
|
|
Hi,
doesn't sound like SQL Server's own table to me. In what database this table resides and under what schema?
Mika
|
|
|
|
|
I saw that included in some stored procedures in my company's DB, is that user-defined table or something else? cause I tried to search for that information online, couldn't get either...
|
|
|
|
|
I would guess that it's user defined. Especially if it resides in user database and not under system schemas, it's defenitely user defined (or by an application other that sql server).
Mika
|
|
|
|
|
if it is a temporary table defined by a user, normally how a user can define that in his/her DB, can you give me more information about that? thanks very much
|
|
|
|
|
Temporary tables have always a special prefix so I don't believe that they are temporary tables for following reasons:
- local temporary tables have a prefix # (table name in procedure would be #batch_control)
- global temporary table have a prefix ## (table name in procedure would be ##batch_control)
Mika
|
|
|
|
|
From query analyzer type
sp_help batch_control this will give you information regarding the columns and indexes which may help you understand better what this table does.
Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)
|
|
|
|
|
Using Microsoft SQL 2005 Express I need to be able to prevent the same stored procedure from running more than once at the same time so that if the stored procedure is in progress it does not run again.
Although nothing will break if this occurs the second run will be redundant. I would like to ensure that any unnecessary work performed by the SQL Server is prevented. The Stored Procedure in question performs some housekeeping functionality that has a non-deterministic duration.
I would like to do this all within SQL 2005 Express.
Any help is appreciated. Thank you in advance for your time.
Ant.
I'm hard, yet soft.
I'm coloured, yet clear.
I'm fruity and sweet.
I'm jelly, what am I? Muse on it further, I shall return!
- David Walliams (Little Britain)
|
|
|
|
|
Hi,
You could use locking for this. Create a small table and insert a single row in it. In the beginning of the procedure, update the row in the table to acquire lock and in the end of the procedure commit your changes. For example:
CREATE TABLE ProcedureRun (LastRun datetime);
INSERT INTO ProcedureRun (LastRun) VALUES (NULL);
CREATE PROCEDURE SomeProcedure AS
BEGIN
UPDATE ProcedureRun SET LastRun = GETDATE();
... do some stuff...
COMMIT;
END;
Hope this helps,
Mika
|
|
|
|
|
Thank you Mika, I have decided to take your advise. The concept of creating a "control table" seems a good one.
Ant.
I'm hard, yet soft.
I'm coloured, yet clear.
I'm fruity and sweet.
I'm jelly, what am I? Muse on it further, I shall return!
- David Walliams (Little Britain)
|
|
|
|
|
You're welcome 
Mika
|
|
|
|
|
hi all,
To extract SQLSERVER.exe, we run SQLEXPR /X from the command line which prompt the user to specify the Loaction where he want to extract the files.but i want to extract this file programmatically. so how can perform this task in vb.net ? I know shell command but i want to skip the Step, in which it asks from user for location of the extract files.
|
|
|
|
|
hi all,
I want to store SQL server Express programmaticaly. so first i want to check whether the particular instance is already installed or not. How can i perform this task ?
|
|
|
|
|
I have a telephone call record billing application, with a table CallRecords containing the calls. The column BillItemID is set when the call is "billed" to a bill detail table: the BillItems table.
All good so far, and I wrote and update and delete trigger on CallRecords so that they could not be modified once the BillItemID was set - this prevents some bad application code modifying the data which underlies the bill.
Problem is I now have the requirement from the business to rerate a bill if the wrong rates were applied, and to allow an itemised report on this. The practical upshot is I have to credit the original bill, rerate and reassign the call records to a new BillItem and rebill them. Of course the trigger prevents this, and for now I have some code that disables the trigger, modifies the call Records and re-enables the trigger - which is of course horrible and not a good idea.
What I need to figure out is some way of reassigning and updating the call records while retaining the effect of the trigger - and I'd like some ideas from out there!
Howard
'Howard
|
|
|
|
|
You may want to approach this like a checkbook. Meaning, store a series of transactions where you add the numbers up to determine the current balance. The important thing to remember is that you need 2 dates on the transaction; Posted Date and Effective Date. The Posted date is the date that the transaction was entered into the system and the Effective Date is the date that the transaction should be considered. So you may have received money in your bank account on Monday but your data entry clerk didn't enter it into the system until Thursday. You can then generate queries that show the balance "as of" a given date. This is a classic situation. This will also allow you to have a history of the transactions.
Give it some thought.
David
|
|
|
|
|
Thanks for the reply - I do have all that billing transaction/integrity logic on the system - the bill items are assigned to a Bill, the bill has a bill date when it's posted etc. The triggers for that level don't affect the CallRecords table.
In this instance the original Bill and the BillItems would remain, but the CallRecords that originally underlied the BillItem need to be moved out.
I was trying to figure out a way to write the CallRecords trigger thus:
if (update CallRecords)
where (change is a valid move)
permit
else
rollback
The problem is how does a trigger determine this is a valid update (e.g. to move the call records to a new billitem), as opposed to an invalid one.
I think that the case here is that we can modify the call record if we are ALSO modifying the BillItemID to a new value - this is permitted.
'Howard
|
|
|
|
|
Hi,
If I understood your question correctly, you want to allow or prevent modifications on a row with BillItemID based on the values with earlier situation and new situation. If this is correct you should fetch old situation from table name deleted and new situation from table name inserted in update trigger. After that make relevant comparison for values and decide if the change allowed. These virtual tables are automatically managed by SQL Server.
Mika
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
