.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Issue with data conversion

Posted By:      Posted Date: September 02, 2010    Points: 0   Category :Sql Server
Hi Guys,   Input data format: 9261.00001e+012 Out put Required in NVARCHAR  I am using below expression in derived column but still its not converting. (DT_WSTR,50)new_master_plu Any help much appreciated. Thanks,            

View Complete Post

More Related Resource Links

Data Truncation issue with Enterprise Library Logging WriteLog stored Proc


Hi ,

I'm using Enterprise Library Logging  feature for logging. The issue i am facing is when the Logging message is too large(more than 65534 chars) ,complete data  is not logged in the Formatted Mesage column which is  of data Type nText .

I am able insert complete data if i try inserting from Sql insert Query from sql management studio. Do i need to add any attributes to data base listener or do i need to change the sp.

 Is there any way to increase the WriteLog stored proc param size in EnterpriseLibrary.Logging config file ? . Please let me know.


Thanks In Advance.

Data Conversion - string to datetime help!!!

Hi all, I need help on concatenate two fields into one then display it as datetime format as such yyyy-mm-dd hh:mm:ss AM/PM. The db is on SQL 2008 SP1. The date and time columns are on the same table and store as varchar. Date has varchar(10) and time has varchar(16). Date column shows result from query is 20100827 while the time column is either 070101.000000 or 070101. The question is how go about concatenate the two together and display in the format above in a report? How should I write my select query for this report? I am using SQL 2008 R2 BI Development Studio to design my report. Thank you. VCHS

Conversion Failed when Converting the nvarchar value 'N' to data type int.

Hi.. I am facing a strange situation. when i executing the following query it is returning the error SELECT  s.row_id as shipment,    SUM(CAST(la5.attr_value AS INT)) AS Cases     FROM    shipment s WITH(NOLOCK)    INNER JOIN shipping_shift ss WITH(NOLOCK) ON s.shipping_shift = ss.row_id    INNER JOIN shift WITH(NOLOCK) ON ss.shift_id = shift.shift_id    INNER JOIN shipment_lot sl WITH(NOLOCK) ON s.po_id = sl.po_id AND s.cust_id = sl.cust_id AND          s.so_line_no = sl.so_line_no and s.ship_date_local = sl.ship_date_local     INNER JOIN lot_attr la5 WITH(NOLOCK) ON sl.lot_no = la5.lot_no AND sl.item_id = la5.item_id AND la5.attr_id = (SELECT attr_id FROM attr WITH(NOLOCK) WHERE attr_desc = 'CaseQty' AND attr_grp = 3)  WHERE s.spare2 = 'RELEASED'       AND shift.shift_desc = 'c'   AND DAY(ss.production_date) = DAY('2010-08-09 00:00:00.000')    AND MONTH(ss.production_date) = MONTH('2010-08-09 00:00:00.000')    AND YEAR(ss.production_date) = YEAR('2010-08-09 00:00:00.000') GROUP BY s.row_id  Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the nvarchar value 'N' to data type int. The column  type of attr_value of lot_attr table is a USER D

Importing xml data with bcp issue

I am trying to transfer one table's data from one server to another. The table structure on both servers are identical: CREATE TABLE [dbo].[_CachedQueriesArchive]( [id] [int] NULL, [statement_text] [varchar](max) NULL, [execution_count] [bigint] NULL, [avg_logical_reads] [bigint] NULL, [last_logical_reads] [bigint] NULL, [min_logical_reads] [bigint] NULL, [max_logical_reads] [bigint] NULL, [plan_handle] [varbinary](64) NULL, [query_plan] [xml] NULL, [cursor_type] [varchar](max) NULL ) I am using bcp DBNAME.._CachedQueriesArchive out e:\temp\cq.dat -N -T statement to export data. And bcp DBNAME.._CachedQueriesArchive in e:\temp\cq.dat -N -T to import. All records were imported successfuly. The issue is that column "query_plan" (of "xml" type) is filled in source DB in all 22 records. But in target DB it is filled only in 2 (two!) records. Other columns were imported perfectly. Tried bcp with -e option. The error file was empty. Tried BULK INSERT [_CachedQueriesArchive] FROM 'e:\temp\cq.dat' WITH (DATAFILETYPE='widenative') for import - same result. Tried bcp with -w option instead of -N. No success. Tried importing file on the source server - everything was fine (all 22 records was imported with their "query_plan" data). The problem occurs only on target server. Servers have different versions: source - 9.0.3257 target - 9.0.4053

Convert Data from TXT file and face a length issue! Need help!

Hi All, I am trying to convert some TXT files in to sqlserver database by using SSIS.  The problem I am facing right now is that some TXT files I have are fixed with line length 286.  However, for the some other TXTs they have 296 characters per line. The reason why this happened is some one added one more field before generating those TXTs.  Instead of creating two SSIS packages for converting those TXTs, can I find another way to get around this?   

Data conversion in SSIS

I am creating an SSIS package, My source DB is ORACLE and destination is SQL Server 2005 I am moving the contents of a Table AO in Oracle DB to  Table B in SQL Server2005 A specific Column in  "Renvenue " in AO table is of data type NUMBER  , it contains  decimal values like 1040.334, 19191.33, 454545.22 I have a corresponding column 'Rev" in Table B ( in SQLserver DB) of datatype Numeric(18,2)   I have created a SSIS package using Data source Reader, DataConversion , OLEDB Destination  in the dataflow task Everything is working fine, but I am getting incorrect data for example 1040.334, 19191.33, 454545.22   for these values I am getting 1040.00 , 19191.00 , 454545.00 the decimal values are not getting transferred to the destination table. I tried converting the datatype to Numeric {DT_NUMBERIC] 18 -2   and even declimal(DT_DECIMAL] scale 2 but still not getting the decimal values   Can some one tell me where am I going wrong...      

Data Reader Destination issue

Hi, I'm new to BI, I created a package to test the data reader destination the dtsx is executing well every thing is green but what after that. The question is how to use this Data reader destination? As I know as a .net programmer there  is a class named data reader that could be used to retieve data in connected mode programmatically using a  connection and a command objects, but in the case of data reader destination how to use it. Or am I confusing the data reader that I kno in the ADO .Net with this one used in BIDS  Should I add a script task after the data reader destination or should I consume this data reader within my proper separate code with a dll or an exe that I create as a .Net project ? For instance, two properties are remaquables for me those are locted within the custom proeprties  datareader and  usercomponentdatatype   The complexity resides in the simplicity

Data query task issue

Hi Someone help me undersand how to implement a Data meaning query  in SSIS project step by step. even a small package for testing this task ThanksThe complexity resides in the simplicity

OLE DB Destination issue in ForEach Data Flow

Has anyone encountered the following problem:I have a ForEach container with a single task in it - a Data Flow. The Data Flow uses a Source Script transformation to read each file and writes out to several output streams, each of which is connected to a OLE DB Destination.The ForEach executes for each file (in my example 3 times) but only the data from the last execution is in the DB tables.This from is my output window:SSIS package "PIF.dtsx" starting.Information: 0x4004300A at ETL Audit, DTS.Pipeline: Validation phase is beginning.Information: 0x4004300A at Load V6 Policy, DTS.Pipeline: Validation phase is beginning.Information: 0x40043006 at Load V6 Policy, DTS.Pipeline: Prepare for Execute phase is beginning.Information: 0x40043007 at Load V6 Policy, DTS.Pipeline: Pre-Execute phase is beginning.Information: 0x4004300C at Load V6 Policy, DTS.Pipeline: Execute phase is beginning.Information: 0x402090DF at Load V6 Policy, stg_RiskLocation [5449]: The final commit for the data insertion has started.Information: 0x402090DF at Load V6 Policy, stg_PDMRFeed [5277]: The final commit for the data insertion has started.Information: 0x402090DF at Load V6 Policy, stg_Policy [5208]: The final commit for the data insertion has started.Information: 0x402090E0 at Load V6 Policy, stg_RiskLocation [5449]: The final commit for the data insertion has ended.Information: 0x402090DF at Load V6 Pol

Issue with Data Access Layer DLL

Hi, I am working on a project in which I have made changes to LINQ to SQL code in data access layer project. Initially the code was using Single() extension method, I have changed it to SingleOrDefault() was earlier method was throwing exception if no records found in db. After making changes I ran the project but during debugging VS 2008 was showing that file is changed and do you want to reload the file box. When selected the file, the debugger was still executing the commented code (Single() method). I tried cleaning the entire solution and rebuilding it but it did not help. All the projects (Presentation, BL, DAL) are under single solution. Can any one please help me to solve the issue? Thanks in advance.

External Lists and Infopath forms issue with Secondary Data Sources

I've followed this tutorial for creating and customising forms for external data (http://blogs.msdn.com/infopath/archive/2010/03/11/customize-an-external-list-form-in-infopath-2010.aspx).  The only difference being that my seconday data source is not an XML file (I've tried database connection and existing sharepoint lists both internal and external) When running the design checker I get the message "Additional Data connections not supported" and my form won't publish.  Any ideas?

SSIS Excel Connection Manager Data Type Conversion Issues with SS Agent Job

Hi All! I have an issue I've been trying to fix but can't seem to figure it out. I was hoping a kind person would point me in the right direction. :o) I have an SSIS package that uses an excel connection manager source, and I want to run this package through a job scheduled in the SQL server agent. The data types for the excel file fields are 2 (DT_WSTR) and 5 (DT_R8). When I run the package directly through the SSIS package (VS solution) all of the data fields are properly imported into the database table. But...when I run this package through the SQL server agent job, ONLY the string (DT_WSTR) fields in each row are being imported, all of the float fields are imported as NULL. I set the data types for these float fields as "float" in the SQL server import table (data type). Even though the excel source float fields are indicating a type of DT_R8 in the excel connection manager and I set the data types in the SQL server table to "float", I also used the data conversion component and set the type to "float" as a fail-safe. I guess I should add to that the data access mode in the excel connection manager is using a custom code to select only those columns that I needed and to trim rows that I didn't need. Here's my code that I have in the excel source editor: select f1, f2, f3, f5, f6, f7, f8 from [mdo$] where f2 <> 'Rep Name'

Help resolve an issue for importing data

Hello All, What is the best way to get info from a directory on machine to import into SQL?  I basically need the file path and file size of a certain directory.  I tried using the command prompt and print to a text file, but I can't find a way to get JUST file path and file size.    I'm looking into some free software online like "Treesize" but I'm not sure that'll do it. I know I can do this prgrammatically, but I'm just curious to see if there is a faster way because I am dealing with large directories. Any suggestions would be appreciated. Thanks!

Problem with Conversion of numeric data from sql table to csv

I have a numeric value which is of data type Numeric(38,12) in sql server 2000. Now loading to csv what happens is 2000--data looks like 115,834,000.00 when loaded to csv ---115834000--this is how it looks. but I want it to look like as it is in the database. It would be of great help to me, if anybody can tell me the work around. Thanks, PreenSheen

Help to resolve an error "The conversion of a char data type to a datetime data type resulted in an

Why am I getting an error when executing this: select convert(datetime, '2010-09-12T18:11:48', 120) The message is: "Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." I don’t get the error if I remove “T” from the string. But I need it to work with “T”.

Ajax issue: delay in getting data from web service using innerHTML, please guide

I am working on an ajax application which will display about a million records in an html table. Web service returns records from server, I build a logn string by concatinating data and tags and than put this string using innerHTML (not using DOM for getting better performance). For testing I have put 6000 recods in database (stored procedure takes about 4 seconds in completion of its execution). While testing on local system (database and application on same machine) it took about 5 minutes to display the records in page. After deplying on web server it did not responde even for more time. It looks very low performance. I put records in a CSV file and its weight was less than 2 MB. I couldn't understand why string concatinations to build html table and putting string in innerHTML is taking such a huge time (if it is the issue). Requiment is to show about million records in web page but performance on just 6000 records is disappointing. I am not gettign what to do to increase performance. Kindly guide me and help me.  

SharePoint Data Source for InfoPath Issue

I've setup an InfoPath form which uses web services to pull an XML data source into the form.  The form works great internally, but when users on the extranet try to use it they get a "Cannot Access Data Source" error.  I have tried connecting to the web service via the extranet and it works just fine, but as a data source in the InfoPath form, extranet users cannot access it.  I've tried using both the intranet and extranet paths to the web service with no luck.  Any ideas?
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend