Strange Issure When Importing Data From ODBC
Apr 12, 2007
Hi, there;
I have a SSIS package that importing data from ODBC source. It failed because of those long exception. To find out what causes the problem, I tried to import data by DTS package in SQL 2000. I just select small chunk of data(about 27 rows), the package gives error:
The number of failing rows exceeds the maximum specified.
Insert error, column 45('PrevStmtBal',DBTYPE_NUMERIC), status 12: Invalid status for bound data.
Insert error, column 45(LastSTmtBal,DBTYPE_NUMERIC), status 12: Invalid status for bound data.
.......: same error for other columns with same data type.
But, if tried these 27 rows one by one, I can import those data without any problem! This tells me that there is no data format issue with that part of data.
My ODBC source is D3.
Does anybody know what the problem is?
Thanks.
View 8 Replies
ADVERTISEMENT
Nov 27, 2007
HiI've got an Ingres database of some 200 tables which I need to importevery night into SQL Server 2005 for use by Reporting Services. Mostof the tables will come across unchanged (a few need massaging tohandle time intervals correctly), but the Import Wizard only seems towant to import one table (or more accurately query) at a time. I seemto remember the old 2000 Import Wizard handled multiple tables - isthere any way of processing multiple tables in 2005, or must I resignmyself to writing 200 import packages in SSIS.Chloe CrowderThe British Library
View 4 Replies
View Related
May 3, 2006
I'm having an odd issue importing data from an ODBC source using a DataReader Source.
There's data in the source table, and if I use a non-SSIS tool to query the source, all the data is visible. However, if I use the SSIS DataReader source, It returns no rows, and throws no error.
Any ideas for figuring this one out? This is affecting about 10% of the tables from this data source, and it's our ERP application, so there's limits on what I can do to the source schemas.
Thanks!
View 1 Replies
View Related
Mar 26, 2007
Hi, there;
I try to import data from ODBC using C# programmatically. Is there any sample code we can have a look. Like how to read schema from ODBC source table and then create source column...
Thanks
View 3 Replies
View Related
Nov 28, 2007
hello there! i have a problem importing data from a remote connection using Sybase ASE ODBC driver(the sybase odbc driver is the only way we can access the database from the remote connection, we've already tried this in crystal reports). in the sql server import export wizard i chose the .net framework data provider for odbc. then below the wizard requires the following info: the connection string, DSN and driver. i specified the correct driver and dsn. in the connection string i specified the user id and the server name but it still produces error. the error says:
the operation could not be completed.
additional information:
ERROR[01000][SYBASE][ODBC Sybase driver][Sybase]ct_connect(): user api layer: internal Client Library error: HAFAILOVER: Trying to connect to server.
ERROR[01000][SYBASE][ODBC Sybase driver][Sybase]ct_connect(): user api layer: internal Client Library error: HAFAILOVER: Trying to connect to server.
i need your help guys... thnx
View 2 Replies
View Related
Apr 23, 2008
Hi All,
Not sure if this question belongs in this area or the server area but I'll start here. Here is my problem. We have an C/C++ app that was originally written for SQL 2000 and uses DBLibrary. We have converted it to SQL 2005 and are using ODBC/Native client to access the SQL 2005 database. This all works great. So we were doing some performance testing and we noticed that our update performance seems slower in the SQL 2005/ODBC case than it did in the SQL 2000/DBLibrary case. Inserts and queries all perform great, in fact the inserts are significantly faster in the SQL 2005/ODBC case which is good. We are using Array inserts/updates/queries wherever possible as this is faster obviously. In our update case, it takes 1.14 seconds to update 2000 rows in table in the SQL 2005/ODBC case, while SQL 2000/DBLibrary case takes .39 seconds to the exact same thing. The table in question is a 12 column table with all integer columns, with an index on the first three columns.
So my question is this. Is there something different about Array Updates in SQL 2005 ? I've looked thru the list of hot fixes available since SQL 2005 SP2 and haven't seen anything that directly mentions Updates so I'm hesitant to go off and start applying the hot fixes to our server to see if the behavior changes. It seems strange to me that Array Inserts would be very fast, but Updates wouldn't be. I've checked the ODBC Data Source and we aren't doing anything fancy there. I'm not actually even sure if this problem is client side or server side as I said earlier.
If anyone has any ideas or thoughts that would be great since this is really bugging me.
Here is a sample of what our C code is doing. This is simplied and I've removed a lot of our own code but these are the SQL calls that we are making in order so maybe can see something wrong I'm doing.
//----Sample Code -------------------------------------------------------------------------------------
pSQL = "Update bob set VV=? where VI=?" // not done this way actually in our code but just to show the update text
status = SQLPrepare ( hS, (UCHAR *)pSQL, SQL_NTS );
// Called twice with nCol = 1 and then with nCol = 2
status = SQLSetParam (
hS,
abs(nCol),
cType, // cType = 5 = SQL_C_LONG
sqlType, // sqlType = 4 = SQL_INTEGER
38, // size needed in case the column is numeric or decimal
0,
p16Data, // Pointer to my array of data
NULL
);
SQLSetStmtAttr(hS, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)numrows, 0); // numrows = 2000
SQLSetStmtAttr(hS, SQL_ATTR_PARAMS_PROCESSED_PTR, &p16cRows, 0);
status = SQLExecute (hS);
SQLParamOptions(hS, 1, NULL);
SQLFreeStmt ( hS, SQL_RESET_PARAMS );
//----Sample Code -------------------------------------------------------------------------------------
Thanks,
Nick
View 5 Replies
View Related
Apr 25, 2008
Hello,
I am having an issue when attempting to retrieve data from SPSS via a ADO.NETDBC Connection
using the DataReader source. What seems to be occurring is that the DataReader is reading a column that
has a length of 255 and what it is doing is taking the first 200 characters and starts repeating the
characters starting at character 201, in this way erasing any data held in positions 201 to 255.
Another way of saying this:
This statement returns data in the results but I have noticed the data is incorrect. It seems to only
be selecting the initial 200 characters of the 255 in the field. Then it starts to repeat the
first 200 characters again to complete the full selection of the 255 characters
Here is an example:
Instead of:
€œXXXXX changed my life before it got worse. It was very informative. They gave me the information. They left it up to me to ponder over it and make the decision on what I wanted to do. It informed me on what drugs do to your body and mind. I was stress€?
I end up getting:
€œXXXXX changed my life before it got worse. It was very informative. They gave me the information. They left it up to me to ponder over it and make the decision on what I wanted to do. It informed XXXXX changed my life before it got worse. It was very€?
Source Column Datatype that SSIS can see is of Unicode string [DT_WSTR] type. Also it correctly identifies
the length is 255 in both the External Column, and Output Column section.
View 2 Replies
View Related
Dec 17, 2007
Got an error of
Error during Transformation 'DirectCopyXform' for Row number 813. Errors encountered so far in this task: 1.
TransformCopy 'DirectCopyXform' conversion error: Destination does not allow NULL on column pair 1 (source column 'Customer' (DBTYPE_R8), destination column 'Customer' (DBTYPE_STR))
What does that mean?
I have an excel file named Customer List with fields such as Customer, Name, City.
I want to import it into a syspro table called SyproCompanyF.ArCustomer.
View 4 Replies
View Related
Jul 31, 2006
I am having problems reading from and ODBC conncetion from Oracle RDB into SSIS. I am using a DataReader source which uses a ADO.NET odbc connection to an oracle RDB database. I am having that write to a flat file. When I read integers from the source, it works just fine. When I read character data (char(48) for example), it gives me trucation errors. Is the DataReader source capable of reading char data from an odbc connection?
Here is the errors I receive:
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Data Flow Task, Flat File Destination [792]: The processing of file "D:Documents and SettingsAdministratorDesktop est.txt" has started.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC020902A at Data Flow Task, DataReader Source [575]: The "component "DataReader Source" (575)" failed because truncation occurred, and the truncation row disposition on "output column "REPORT_PART_NUMBER" (789)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Error: 0xC02090F5 at Data Flow Task, DataReader Source [575]: The component "DataReader Source" (575) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (575) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Data Flow Task, Flat File Destination [792]: The processing of file "D:Documents and SettingsAdministratorDesktop est.txt" has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Flat File Destination" (792)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (6) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
Thanks,
Alex
View 11 Replies
View Related
Sep 28, 2015
I setup this package to import data from a Sharepoint list to a SQL Server data table. The primary key of my SQL table is mapped to the Title column of my Sharepoint list. There is a possibility that duplicate values will be entered in the Title field of the Sharepoint list. So when importing data into my table via SSIS, my package always error-out when there it comes across duplicate values. how you others have managed data integrity when importing from a Sharepoint list with the Title column being mapped to the primary key of a table.
View 4 Replies
View Related
Sep 10, 2007
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
View 4 Replies
View Related
Jul 7, 2015
We have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.Â
View 6 Replies
View Related
Jun 18, 2007
I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.
In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.
thanks
View 6 Replies
View Related
Apr 21, 2008
Using SQL Server 2005, I hve encountered a very strange error which I cannot figure out. The simplified stored proc below basically does a select and sorts the records based on the column name given in the @SortCol parameter. When I enter the field names bapk, jhpk, etc everything works fine. The records are returned sorted by the field name entered into the parameter. The first eight variables are field names and types When I enter the field name 'descr' into the @SortCol variable/parameter, I receive the error: Msg 295, Level 16, State 3, Procedure CMSCheck2, Line 28Conversion failed when converting character string to smalldatetime data type. I don't see where this is coming from at all. The problem is in the stored proc lines beginning with 'CASE @SortCol'If I take out the lines and just use OVER (Order By descr) as RowNum -- everything is fineIf I delete the line WHEN 'descr' THEN descr -- everything is fine, it just falls through to the default. What is the problem? Where is a conversion to smalldatetime coming from? I've been working on this one for a while, and cannot figure it out. It must be something simple. Thanks,Mike Thomas Below is the stored proc and a testing script TESTING SCRIPTUSE biz03GODECLARE @bapk int, @jhpk int, @transdate smalldatetime, @number smallint, @descr nvarchar(50), @debit decimal(9,2), @credit decimal(9,2), @cmonth tinyint, @StartRowIndex int, @MaxRows int, @AlphaChar varchar(1), @SortCol varchar(20) SET @MaxRows = 10SET @StartRowIndex = 0SET @SortCol = 'descr'SET @AlphaChar = ''EXEC [dbo].[CMSCheck2] @bapk, @jhpk, @transdate, @number, @descr, @debit, @credit, @cmonth, @StartRowIndex, @MaxRows, @AlphaChar, @SortCol STORED PROC ALTER PROCEDURE [dbo].[CMSCheck2] @bapk int, @jhpk int, @transdate smalldatetime, @number smallint, @descr nvarchar(50), @debit decimal(9,2), @credit decimal(9,2), @cmonth tinyint, @StartRowIndex int, @MaxRows int, @AlphaChar varchar(1) = null, @SortCol varchar(20) = nullASBEGINSET NOCOUNT ONDECLARE @lPaging bitIF @AlphaChar is null SET @lPaging = 0ELSE SET @lPaging = 1;WITH BankListTemp AS (SELECT bapk, jhpk, transdate, number, descr, debit, credit, cmonth, ROW_NUMBER() OVER (ORDER BY CASE @SortCol WHEN 'bapk' THEN bapk WHEN 'jhpk' THEN jhpk WHEN 'transdate' THEN transdate WHEN 'number' THEN number WHEN 'descr' THEN descr -- problem here -- WHEN 'debit' THEN debit WHEN 'credit' THEN credit WHEN 'cmonth' THEN cmonth ELSE bapk END) as RowNum FROM bank)SELECT TOP (@MaxRows) bapk, jhpk, transdate, number, descr, debit, credit, cmonth, RownumFROM ( SELECT BankListTemp.*, (SELECT COUNT(*) FROM BankListTemp) AS RecCount FROM BankListtemp) BankEND
View 6 Replies
View Related
Oct 17, 2006
Dear all,
I've got a SSIS package which comprises a pair of Sql Task and after that a Data Flow.
Such Data Flow own two Data Viewers, one between my Excel Source and my Derived Column and the another one between my Derived Column and my Ole DB Destination. Up to here, everything's fine.
Package really works properly but when I execute only the first Sql Task runtime shows (as usual) Excel Source Ouput Data Viewer1 and Derived Column Output Data Viewer1. Both windows empty, of course. Problem comes when I choose 'Copy Data'. Inmediately, appears a dialog with the message:
"Windows has encountered a problem and needs to close"
I wonder, at first, why on earth these two windows are showed when I execute a task which belongs to Control Flow no Data Flow????? What happen?
Maybe my doubt is a little bit devious, crazy.
Thanks in advance for your input,
View 6 Replies
View Related
Apr 22, 2008
Using SQL Server 2005, I have encountered a very strange error which I cannot figure out. The simplified stored proc below basically does a select and sorts the records based on the column name given in the @SortCol parameter. When I enter the field names bapk, jhpk, etc everything works fine. The records are returned sorted by the field name entered into the parameter. The first eight variables are field names and types
When I enter the field name 'descr' into the @SortCol variable/parameter, I receive the error:
Msg 295, Level 16, State 3, Procedure CMSCheck2, Line 28
Conversion failed when converting character string to smalldatetime data type.
I don't see where this is coming from at all. The @SortCol variable is always a string. It contains the name of a field. The problem is in the stored proc lines beginning with 'CASE @SortCol' .If I take out the lines and just use OVER (Order By descr) as RowNum -- everything is fine. If I delete the line WHEN 'descr' THEN descr -- everything is fine, it just falls through to the default.
What is the problem? Where is a conversion to smalldatetime coming from? I've been working on this one for a while, and cannot figure it out. It must be something simple.
Thanks,
Mike Thomas
Below is the stored proc and a testing script
TESTING SCRIPTUSE biz03
GO
DECLARE
@bapk int,
@jhpk int,
@transdate smalldatetime,
@number smallint,
@descr nvarchar(50),
@debit decimal(9,2),
@credit decimal(9,2),
@cmonth tinyint,
@StartRowIndex int,
@MaxRows int,
@AlphaChar varchar(1),
@SortCol varchar(20)
SET @MaxRows = 10
SET @StartRowIndex = 0
SET @SortCol = 'descr'
SET @AlphaChar = ''
EXEC [dbo].[CMSCheck2]
@bapk,
@jhpk,
@transdate,
@number,
@descr,
@debit,
@credit,
@cmonth,
@StartRowIndex,
@MaxRows,
@AlphaChar,
@SortCol
STORED PROC
ALTER PROCEDURE [dbo].[CMSCheck2]
@bapk int,
@jhpk int,
@transdate smalldatetime,
@number smallint,
@descr nvarchar(50),
@debit decimal(9,2),
@credit decimal(9,2),
@cmonth tinyint,
@StartRowIndex int,
@MaxRows int,
@AlphaChar varchar(1) = null,
@SortCol varchar(20) = null
AS
BEGIN
SET NOCOUNT ON
DECLARE @lPaging bit
IF @AlphaChar is null
SET @lPaging = 0
ELSE
SET @lPaging = 1;
WITH BankListTemp AS
(SELECT bapk, jhpk, transdate, number,
descr, debit, credit, cmonth, ROW_NUMBER()
OVER (ORDER BY
CASE @SortCol
WHEN 'bapk' THEN bapk
WHEN 'jhpk' THEN jhpk
WHEN 'transdate' THEN transdate
WHEN 'number' THEN number
WHEN 'descr' THEN descr -- problem here --
WHEN 'debit' THEN debit
WHEN 'credit' THEN credit
WHEN 'cmonth' THEN cmonth
ELSE bapk
END) as RowNum
FROM bank)
SELECT TOP (@MaxRows) bapk, jhpk, transdate, number,
descr, debit, credit, cmonth, Rownum
FROM
( SELECT BankListTemp.*,
(SELECT COUNT(*) FROM BankListTemp) AS RecCount
FROM BankListtemp) Bank
END
View 7 Replies
View Related
Jul 17, 2007
hi
when i m importing data from excel to Sql using DTS the column which has text content was not imported as same in excel sheet. whereas a special character is appearing in between the lines. the text field contains multiple lines but the conetent is imported in single line .
ex:
ARIZONA
ALABAMA
STATE
but i m getting imported
as :
ARIZONA ALABAMA STATE
How to Format a single column while importing?
Regards
Raj
View 1 Replies
View Related
Mar 19, 2007
Hi all :)
I'm wondering if SSIS will be the solution to the problem I'm working on.
Some of our customers give us an Excel sheet with data they want to insert or update in the database.
I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.
This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.
But now I€™ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.
Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I€™ve looked at programmatically creating the package but I€™ve got to say that€™s quit hard to do€¦ It would be easier to write the whole thing myself than to create the package trough code ;)
If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.
So how should I solve this problem? Use SSIS or not?
Thnx :)
Wouter de Kort
View 6 Replies
View Related
Dec 10, 2003
Hi Everybody,
On localhost this application works fine but when I put on remote server. I am getting following errors. For both localhost and server, I am using same remote sql 2000. I will appreciate any help.
Thanks,
Arif
Server Error in '/' Application.
--------------------------------------------------------------------------------
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.]
Microsoft.Data.Odbc.OdbcConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode) +27
Microsoft.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method) +838
Microsoft.Data.Odbc.OdbcCommand.ExecuteNonQuery() +80
Calgary.venues.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootCalgarySitevenuesvenues.aspx.vb:32
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
View 5 Replies
View Related
Apr 7, 2005
:(
Hey guys. I got some problem here.
I have an Excel spreadsheet. the data is something like :
COL1 COL2 COL3
DATA1 1 DETAIL1
DATA2 2 DETAIL2
DATA3 3 DETAIL3
DATA4 4 DETAIL4
DATA5 5 DETAIL5
DATA6 6 DETAIL6
DATA7 7 DETAIL7
etc
there are 1877 * 3 cells in total in the spreadsheet.
but when I execute a query like:
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=Test.xls;User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
It returns 2132 * 5 units of data!!! And the "F4" and "F5" column just have NULL. so do row 1878 to 2132. I'm confused with that.
I need your help~ 3X in advance. :)
View 4 Replies
View Related
May 29, 2008
I have a fact table which is a custim query in the data source view, I process and work with the data, after somewhile I make some changes in the data source view query but all my changes does not reflect in the cube after processing the same old data returned.
please help ASAP, I want to fix this issue
View 12 Replies
View Related
May 1, 2008
Hi there, i copied some insert code from one page to another and it keeps throwing me this error, i was wondering if anyone can help me out. Here is the error
System.Data.SqlClient.SqlException: The name "Credit" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Admin_expenses.Button1_Click(Object sender, EventArgs e) in d:hostingmemberashdfhtdocswwwAdminexpenses.aspx.cs:line 40
This is my code, i cant see anything obvious and as i said i just copied it across, the only things changed were the names and the sqlstringprotected void Button1_Click(object sender, EventArgs e)
{decimal intamount = Convert.ToDecimal(txtAmount.Text);
string sConnectionStringExpense = "Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx";SqlConnection objConnExpense = new SqlConnection(sConnectionStringExpense);
using (objConnExpense)
{
objConnExpense.Open();string sqlExpense = "INSERT INTO tbl_expenses (expense_amount, expense_payment_type, expense_type, expense_description) " +
"VALUES (" + intamount + "," + ddlPaymentType.SelectedItem + "," + ddlExpenseType.SelectedValue + "," + txtDescription.Text + ")";SqlCommand objCmdExpense = new SqlCommand(sqlExpense, objConnExpense);
try
{
objCmdExpense.ExecuteNonQuery();
}catch (Exception ex)
{lblcomplete.Text = "!!Adjust stock for product at location " + Convert.ToString(ex);
}
finally
{
objConnExpense.Close();
}
}
View 6 Replies
View Related
Mar 18, 2007
A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
EXEC sp_addlinkedserver
@server = 'server_name',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'odbc_dsn_name'
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
Any assistance would be appreciated.
Thanks,
View 3 Replies
View Related
Jun 11, 2006
I'm new to SQL and DTS packages. I am trying to import data from an excel spreadsheet to an SQL server table via DTS package. It seems that the excel task looks at the first few records in a column to determine the datatype for that column. If the first few records are text, the entire column is imported as text. If numeric, the entire column is imported as numeric.
There are about 25,000 records. In one field, the most important one, about half of the records begin with letters and the rest are all numbers. It is the subscriber ID field, and some subscriber IDs are all numbers, some are letters and numbers. The entire column should be imported as text. However, when I run the transform data task from the excel connection, none of the records that are all numbers are imported. I end up correctly importing only 13,000 of the 25,000 records. The rest are imported with the subscriberID field as <NULL>.
I tried using the CAST or CONVERT function in the SQL query, but get the error message "Undefined Function."
Can anyone give me some help? Thanks,
Jim
View 4 Replies
View Related
Jun 27, 2006
hello,
I create a txt file with a bash script, and i need to use it in a DTS package. But, i don't know how i can specify the type of my column. So in the transformations task, i have an error due to an incompatible type. what can i do to fix this error ?
thanks,
View 8 Replies
View Related
Nov 28, 2005
I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table.
Now that I have the data in this table, I want to import any data that is not in my main table.
This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column.
DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table.
How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates.
Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar.
Code:
select
partno2,
MAX (partno) as partno,
alt,
MAX (C_alt) as C_alt,
Max (cmpycd) as cmpycd,
MAX (type) as type,
compFN,
MAX (pndesc) as pndesc,
MAX (equipment) as equipment
into tbl_CLEANED
from tbl_CLEANING
group by partno2, alt, compFN
ORDER BY partno, compFN
The three main columns I need to check against are:
partno2
alt
compFN
I have named the columns the same in both tables.
partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table.
How can I compare these tables and import only unique info to the MAIN table?
In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?
View 3 Replies
View Related
Nov 30, 2007
The strange in this problem is that i got this problem suddenly and before all was running without any error.
The script that cause the error is in the trigger for insertion on a table, there is somewhere where i convert from varchar to bigint, but all seems fine to me i printed the varchar variable and the conversion looks valid, also i included several print statement in the script for debugging purposes and i feel everything is right and there shouldn't be any error.
I don't think anything changed in my db, so i wonder where this problem is comming from ?
this is the code:
Code Block
-- Private Sending start
declare @tmpmsg nvarchar(200)
set @tmpmsg = ltrim(rtrim(@message)) -- Remove leading and trailing blanks
if (left(@tmpmsg,4))='خاص' and len(@tmpmsg)>6
begin
declare @msg_error nvarchar(70)
declare @msg_error1 nvarchar(70)
declare @msg_error2 nvarchar(70)
declare @msg_error3 nvarchar(70)
declare @sender_id bigint
set @msg_error1 = 'الرجاء إرسال كلمة خاص متبوعة برقم ملÙ? الشخص المطلوب ثم الرسالة الشخصية'
set @msg_error2 = 'الرجاء تسجيل ملÙ?Ùƒ قبل إستخدام خدمة الرسائل الخاصة'
set @msg_error3 = 'إن ملÙ? الشخص المطلوب غير موجود'
-- The message should not go to chat filtration
update received_in set sent = 3 where reqid = @id
-- if the sender profile doesn't exist, send him a message indicating that.
SELECT @sender_id = profile_id FROM profiles where msisdn=@msisdn
if @sender_id is not null
begin
set @tmpmsg = ltrim(right(@tmpmsg, len(@tmpmsg)-3))
-- Now the message looks like '12345 this is a test message'
declare @profileid bigint
declare @strprofileid nvarchar(50)
declare @posSpace int
set @posSpace = charindex(' ', @tmpmsg)
if @posSpace = 0 -- Not found (the message format is like this 'pri 3432434 ' or 'pri dfsdffsd '
begin
set @msg_error = @msg_error1
-- Insert the message into the filtration screen to be fixed by a filtrator, the recipient_id will be set to 0
insert into mobile_chat (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, 0, @tmpmsg, @thedate, @thetime)
end
else
begin
set @strprofileid = left(@tmpmsg, @posSpace-1)
-- first char separating profileid and util message
if isnumeric(@strprofileid) = 1
begin
set @profileid = @strprofileid
declare @utilmsg nvarchar(200)
set @utilmsg = ltrim(right(@tmpmsg, len(@tmpmsg)-@posSpace))
-- Right message format
print 'Profile id=' + ltrim(str(@profileid))
print 'Message=' + @utilmsg
declare @recmsisdn varchar(16)
select @recmsisdn = msisdn From profiles where profile_id=@profileid
-- if the receiver profile doens't exist, send him a message indicating that
if @recmsisdn is not null
begin
-- All fine: msg format, sender and receiver
if left(@recmsisdn,3)='000'
Insert into Inbox (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, @profileid, @utilmsg, @thedate, @thetime)
else
insert into mobile_chat (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, @profileid, @utilmsg, @thedate, @thetime)
end
else
begin
-- Insert the message into the filtration screen to be fixed by a filtrator, the recipient_id will be set to 0
insert into mobile_chat (sender_id, recipient_id, message, datein, timein)
Values (@sender_id, 0, @tmpmsg, @thedate, @thetime)
set @msg_error = @msg_error3
end
end
else
begin
set @msg_error = @msg_error1
end
end
end
else
-- The sender profile is not registered with us
set @msg_error = @msg_error2
if @msg_error is not null
begin
print @msg_error
if left(@msisdn, 3) = '966' -- We can only send replies to KSA mobiles
INSERT INTO cgi_sms_services.dbo.cgi_sms_in(msisdn, sms_code, message, reqport, smstype)
VALUES (@msisdn, '1000000002', @msg_error, 86367, 'wcsms')
end
end -- Private Sending End
View 3 Replies
View Related
Jun 5, 2007
Hi, all,
Again I encountered a very strange problem which displayed the predicted attribute values as percentage format? The data type of the attribute is actually double, why is that?
That's really frustrated.
Thanks a lot in advance for your kind advices and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
View 3 Replies
View Related
Feb 12, 2005
I have created a DTS package for SQL Server, saved this as a VB file and upgraded this to the .NET framework.
It is now saved in a file called Shortages.vb.
What I want to do now is add this to an existing ASP.NET project and be able to call this DTS function by the click of a button.
Is this possible and how can I go about it? Can I just add it to the Click event of a button?
View 3 Replies
View Related
Mar 17, 2002
Hallo every one,
I', working for a school project.
The setuation is an old database that has to be repaird.
All the data from each table of the old data base are stored in different tabdelimited text files.
I designed a new database, but the tables are a little different.
When I try to import the data I get some errors, because of the changed tables.
An example of my new setuation is a new table thas as colums from 2 different tabdelimited text files.
My question is, how do I solve thos problem!
Do I have to use DTS?
What do I have to do.
tnx
icheron.
View 1 Replies
View Related
Jan 12, 2001
How can you can import results into table from system procedures.
For Ex. if you exec sp_spacesued, how do I import the results into the table.
Thank You
View 1 Replies
View Related
Sep 29, 2004
I have a process that calls a proc that BCP's a delimited file into a table. Well the SOX police say a header and footer must be added to the file. Needless to say this screws my BCP process. Does anyone know how to strip a header and footer record from a text file using transact sql or have any other suggestions to strip the records?
View 6 Replies
View Related
Dec 4, 2006
I am trying to import data from excel into my server, but get this error message:
Error during transformation 'directcopyXform' for row number 1. Errors encountered so far in this task 1. TransformCopy 'DirectCopyXform' conversion error: Conversion invalid datatypes on coulumn pair 19 (source column '*9' (DBTYPE_WSTR( destination column 'F19' (DBTYPE_R8)
Anyone get me in the right direction?
Scott
View 6 Replies
View Related
