I have an OLE-DB Command transformation that inserts a row. If the insert SQL command fails for some reason, I use the "Redirect Row" option to send the row to another OLE-DB Command transformation that logs the error on that row to a "failed rows" table. In this table I log the ErrorCode and ErrorColumn values that come with the error path from the first OLE-DB Command.
OK, that's all working great. However, here's the kicker: there's no error description value. The ErrorCode value, naturally, is the decimal form of an HRESULT--for example, -1071607696. Without some further information, however, this code is not useful for troubleshooting.
Has anyone figured out a trick here? I'm not even certain that this is an SSIS HRESULT, since it could for all I know be from the OLE-DB layer, the database layer, or somewhere else.
In our database is a decimal field with format YYMMDDhhmmss.9999999999where the 9s are random digits. I'm trying to strip off just theYYMMDD and put it in date form.So far I came up with:SUBSTR(TO_CHAR(rec_num,99999999999),1,6) AS Date which returns YMMDD.For example where the rec_num is 30608124143.47069519725 the abovefunctions return 30608.I tried wrapping another TO_CHAR around it to try to format it to adate but this seems like it's a bit much for this purpose.Any suggestions would be appreciated.-David
I'm working on a recurring multi-day appointment program. Basically the user can choose a meeting on multiple days of the week over a span of time. For example: Tuesday and Thursday from 10:00 to 10:30 from December 1st 2004 to February 27th 2005.
So I've decided the best way to handle this is to assign a value to each day of the week like so: MON = 1 TUE = 2 WED = 4 THU = 8 FRI = 16 SAT = 32 SUN = 64
So if the user picks TUE and THU that would be 2 + 8 = 10. The value is unique and seems to work.
So the values would be: @begin_date = '12/01/2004' @begin_time = '10:00 AM' @end_date = '02/27/2005' @end_time = '10:30 AM' @recur_days = 10
Now I want to pass the values to stored procedure that will decode the recur_days variable and create entries in a table for each date. I'm struggling to figure out 2 things
1. How do I decode the 10 back into 2(TUE) + 8(THU) ( I think it has something to do with the bitwise AND "&" operator but I'm not sure how to use it.)
2. What is the best way to loop through the date range and create a record for each day?
I have a table column type as nText, however there are some Chinese character stored in that field and it is a messed up as it is not readable.
In my vb.net code, I did Convert to unicode by getting the byte of each character and encode it with UTF8 e.g:
Public Shared Function ConvertToUnicode(ByVal s As String) As String
' Convert To Unicode
Dim MyBytes As Byte() = Encoding.Default.GetBytes(s)
Dim GBencoding As Encoding = System.Text.UTF8Encoding.UTF8
Return GBencoding.GetString(MyBytes)
End Function
This works well but ,the problem is that it slows down the process quite alot, and I wonder are there any text encoding method I can use in SQL that can run when i do the SELECT Statement?
SELECT Convert(MyNTEXTColumn) .... something like that?
I've scripted a sql trace for Sql Server 2000 and added the filename in the appropreate location andthe result is a column called ErrorCode with a value of 14. Can anyone tell me what this error code means and what could be infered about what I need to resolve it?
When a user finally confirms his order in a shopping cart application (using MS-Access as the backend), the items he has purchased are populated in a Access DB table named Orders & his personal details (like name, mail, billing address, shipping address etc.) are populated in another table named CustDetails. Both the DB tables have a column named OrderID. This column is the Primary Key in the CustDetails table & the Foreign Key in the Orders table. The Session.SessionID becomes the unique OrderID.After confirming an order, if the user refreshes the page, the app will try to populate the same Session.SessionID in the CustDetails table but since the column OrderID is a Primary Key column in the table CustDetails, it won't accept duplicate OrderIDs. Under such circumstances, an OleDbException will be raised.Since a DB app can throw other OleDbExceptions other than the one about which I mentioned above, I want to display custom error messages to the user. For e.g. if he refreshes the page after confirming his order, I want to display a message saying "Your order has already been placed".To do this, I tried using the ErrorCode property of the OleDbException class but what I found is the ErrorCode changes from time to time! Had a particular ErrorCode been assigned to the error, I could have done something like this (assuming that the ErrorCode for the above error is -12345 which is constant):Try 'some codeCatch ex As OleDbException If (ex.ErrorCode = -12345) Then Response.Write("Your order has already been placed") ElseIf (ex.ErrorCode = <some other constant ErrorCode>) Then Response.Write("Another custom error message") End IfEnd TryBut I can't do the above since the ErrorCode changes from time to time.So how do I display custom error messages to users under such circumstances?Of course, I can use the Message property of the OleDbException class but that would be a rather tedious workaround.
Hi, In my local , application is working fyn...but when i had uploaded in the file in Live and executed the SP's ,It is showing up the error 8144... Whene i had checked in google it is showing: has too many arguments specified: Msg 8144....But i had not passed to many arguments... (The same SP is working in local but not in live)... The related files also i had shifted to live...
This is the SP I Have used.... while Submiting iam getting error GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
--- STORED PROCEDURE USP_CREATE_USER TO SAVE THE APPLICATION INFORMATION INTO DATABASE
SET @ERRORCODE=@@ERROR IF @ERRORCODE <> 0 BEGIN ROLLBACK TRANSACTION TRANSUPDATE SET @ERRORCODE=@@ERROR RETURN END ELSE BEGIN COMMIT TRANSACTION TRANSUPDATE SET @ERRORCODE=50002 RETURN END
SET @ERRORCODE=@@ERROR IF @ERRORCODE <> 0 BEGIN ROLLBACK TRANSACTION TRANSINSERT SET @ERRORCODE=50004 RETURN END ELSE BEGIN COMMIT TRANSACTION TRANSINSERT SET @ERRORCODE=50001 RETURN
END
END
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
This is a request to the Microsoft SSIS staff. I did a search for the keywords in the description and can already see a lot of people frustrated about this.
A lot of well designed integration packages will route error messages to an error table or file. SSIS appends a couple of extra error columns called ErrorCode and ErrorColumn. At runtime, an extra column is visible in the DataViewer called ErrorCode - Description.
The description is hardcoded into the dtsmsg.h file in the SDK folder of Sql Server. This file is very hard to parse.
Can some from SSIS please post an XML or CSV file with all the error codes and descriptions for us to download please. This will be such a big help in debugging.
Hello, My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type. The dropdown options on the form will be as follows: ComplimentsComplaintsGeneral CommentsSuggestions Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item. For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter. However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated. If you need more information, I will provide whatever is needed. As always, thanks for everyone's assistance. Chris
Hi,I'm trying to execute server-side trace with SqlServer 2000 oncluster. After scripting trace by Profiler I executed this script inQueryanalyzer and got error code 12. According to BOL this code saysthat file is not created but I don't know why, is there are anyobstacles which preventing to add this trace??Does anybody had that problem?If it helps I added script which I tried to execute:-- Create a Queuedeclare @rc intdeclare @TraceID intdeclare @maxfilesize bigintset @maxfilesize = 5exec @rc = sp_trace_create @TraceID output, 0, N'C:Profiler_result esult.trc', @maxfilesize, NULLif (@rc != 0) goto error-- Client side File and Table cannot be scripted-- Set the eventsdeclare @on bitset @on = 1exec sp_trace_setevent @TraceID, 10, 1, @on/*here are n-setevents*/exec sp_trace_setevent @TraceID, 43, 35, @on-- Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigintexec sp_trace_setfilter @TraceID, 1, 1, 6, N'history'exec sp_trace_setfilter @TraceID, 1, 1, 6, N'move_history'exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'exec sp_trace_setfilter @TraceID, 35, 1, 6, N'kis'-- Set the trace status to startexec sp_trace_setstatus @TraceID, 1-- display trace id for future referencesselect TraceID=@TraceIDgoto finisherror:select ErrorCode=@rcfinish:goThanks in advance for any adviceRegards,Bartolo
SSIS automatically adds the columns ErrorCode and ErrorColumn to the error pipe routing bad rows. However, both these columns appear to be meaningless. We need to translate the error column to the actual problem column and the error code to a meaningful error. ErrorColumn is the more important issue for us. The question is 'Are these just useless columns? If not, how do we translate them into something useful?'
Installed SQL express 2005 w/Studio Manager. Everything installed Ok except got an error IIS was not running or not installed. When installing a software program, when it tries to create the database I get an error at the initialization:
An error has occured while processing an SQL statement. Please refer to the system administrator guide for further assistance. ERRORCODE:37000 ERROR:CREATE DATABASE failed. Some of the file names cound not be created. Check related errors. SQL STATEMENT CREATE DATABASE xxxx
Has anyone seen or know a fix for this? Thanks in advance.
I have another problem, this time with a flatfile source component. The file is pretty simple organized and in fact everything works as expected.
The file itself comes from an FTP server, so it is not available at designtime. For setting up the flatfile source i downloaded it, but afterwards i delete it, set 'dalayvalidation' to true and try to execute.
FTP download works fine, the file is there but then i get the errormessage:
Copier ErrorCodes [586]: The data type for "output column "Flat File Source Error Output Column" (610)" is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.
When i try to set the datatype of the Error Output Column (610) to DT_NEXT i only get an errormessage telling me that this would not be a valid value for this property.
The only chance i have to get it working again is to completely delete the flatfile-source, readd and setup it again, then everything works... but only until i remove the files and try to download them via FTP. It's strange :-( and annoying.
Within my SSIS packages I use a logging logic quite similar as the one described by Jamie Thomson here : http://blogs.conchango.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-Custom-Logging-Using-Event-Handlers.aspx
But I still have a small "problem". When my package end with success the ErrorCode returned at the OnInformation event handler level is :
0x4001100B
1073811467
DTS_I_COMMITTINGTRANSACTION
Committing distributed transaction started by this container.
Instead of :
0x40013001
1073819649
DTS_MSG_PACKAGESUCCESS
Package "__" finished successfully.
Another example.
When there's an integrity constraint violation, I've got the following ErrorCode returned :
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
I am transfering a database from sql server 2000 to sql server 2005 using copy database wizard. I have this error coming up error
Error: ERROR : errorCode=-1073548784 description=Executing the query "EXEC dbo.sp_grantdbaccess @loginame = N'Testusername', @name_in_db = N'username' " failed with the following error: "The login already has an account under a different user name.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer() at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()
How do i handle this error...looked online but didn't find right answers...
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
I would like to cast (convert) data type decimal(24,4) to decimal(21,4). I could not do this using standard casting function CAST(@variable as decimal(21,4)) or CONVERT(decimal(21,4),@variable) because of the following error: "Arithmetic overflow error converting numeric to data type numeric." Is that because of possible loss of the value?
I am currently redirecting lookup failures into error tables with ErrorCode and ErrorColumn. It works fine until I want to transfer data into the archived database. The SSIS pacakage generate by SQL Exporting tool is throwing an "duplicate name of 'output column ErrorCode and ErrorColumn" error. This is caused by oledb source error output. The error output automatically add ErrorCode and ErrorColumn to the error output selection and not happy with it.
I think the question is down to "How to importing/exporting data when table contains ErrorCode or ErrorColumn column?"
I'm sorry for asking this, as I'm sure tha the answer is in one of the 208 matches I found searching on "ErrorCode". Unfortunately, this project is overdue and I need a solution fairly soon.
I should add that I've only been developing in SSIS since August 2007.
I have a complicated package, loading about 17 outputs of the XML Source into staging tables. I have been using the error outputs of any standard components I use, out of faith that, if Microsoft provided them, then they should be useful for something. I've been directing all of the error outputs for one of the 17 "tables" from the XML Source into a Union All, and then into an "Error staging table", for each of the 17 outputs. This table includes all of the possible columns, but permits nulls for all of them. It also incldues the ErrorCode and ErrorColumn.
Unfortunately, if these latter two are useful for anyting, I haven't found it yet.
Right now, working with our first "mostly real" data, I'm getting 100% of my input rows written into error tables. Unfortunately, the information in the tables are of limited use in determining what went wrong. For instance, the ErrorColumn seems only to be populated if there was a specific error with a specific column. The Lookup component, doesn't seem to populate ErrorColumn, even if only one column was used for the lookup! No information about the component producing the error output is supplied in the error output, either, which makes it difficult to determine which of the five or so possible error outputs is the one that produced the particular error row.
This proves that I'm missing something simple. How do people handle errors? In my custom components, I learned to use the Fire* methods to produce detailed messages before redirecting the row to the error output, but this sort of thing is not available through the standard components.
Do I really have to create a separate "add useful information to the error output" component, and use it on each error output?
Sorry for the attitude, but just when I think I'm winning, SSIS brings me back down to earth!
I wanted to convert a dataset from vb.net (2.0) to an .XLS file, by MS Jet. My national standard is using decimal commas, not decimal points for numbers signing the beginning of decimal places. But the MS Jet Engine uses decimal point,in default. Therefore, in the Excel file only string formatted cells can welcome this data, not number formatted. How can I solve or get around this problem? (with jet if it possible) iviczl
I'd like to convert a Decimal value into a string so that the entireoriginal value and length remains intact but there is no decimal point.For example, the decimal value 6.250 is selected as 06250.Can this be done?
I am designing some reports for a German branch of my company and need to replace decimal point with a comma and the thousand comma seperator with a decimal point.
e.g. ‚¬1,500,123.00 to ‚¬1.500.123,00
Is there a property that I can change in the report designer to allow this to happen or is this something I need to convert in a Stored Proc.
Hi, I got a problem. I installed Microsoft SQL Server Management Studio Express 2005 version. And I created a Compact database. I created an connection in SSMSE to connect the database and opened a query form. then, i run the following sql:
Select * from Table1
It returned 3 records to me. After that, I used program to insert record into this table. Then i ran this sql again, it still show me 3 records. I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right? Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Hi everyone, What is the main difference between first form normalizations and second form normalization ? In my opinion, they are both generated for the same operation which is to prevent redundancy(in other words; duplication of data in several records). So would you please explain it to me ?
I am having a file in which amount fields are given in a Packed Decimal format. Can anyone suggest me how I can read this data element from the file and convert it into SQL decimal datatype.
File is a fixed length. All the amount fields are given in Packed Decimal Format and rest of the fields are given in text format. How can i identify and convert only those packed decimals using SQL/.Net.
Example : a row in a file that has some packed decimals 158203508540188236252EUR20BZK0030 Å“& 20060715 0001010100010101
Hai all,I am getting an DB_E_ERRORSINCOMMAND exception when I try to open arecordset or execute query in VC++. But when I run the same query inQuery Analyzer, it is working fine. I am sure the connection string iscorrect. I am running a collection of queries and finally commit thetransaction, but everytime it not throwing the exception on same query,eachtime different queries throw exception randomly.Can anyone tell whats the problem?Urgent, Please help...Looking forward for the response..Thanx in advance...
