And i have data in a file as follows ( column delimeter is | and row deleimeter is new line character ( windows ) )
4000000|10/25/2007 6:07:32 AM
4000001|10/25/2007 6:07:32 AM
4000002|10/25/2007 6:07:32 AM
4000003|10/25/2007 6:07:32 AM
4000004|10/25/2007 6:07:32 AM
BULK INSERT dbo.BulkTest FROM 'c:\insert.dat' WITH (FIELDTERMINATOR = '|',ROWTERMINATOR='
')
When i execute this statement its failing with the follwing message.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (SubDate ).
Can any one please let me know whats wrong in this and what should be done to make it work
Quick response will be appreciated
Thanks much
~Mohan
Hi, I'm inserting a datetime values into sql server 2000 from c#
SQL server table details Table nameate_test columnname datatype No int date_t DateTime
C# coding SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI"); connectionToDatabase.Open(); DataTable dt1 = new DataTable(); dt1.Columns.Add("no",typeof(System.Int16)); dt1.Columns.Add("date_t", typeof(System.DateTime)); DataRow dr = dt1.NewRow(); dr["no"] = 1; dr["date_t"] = DateTime.Now; dt1.Rows.Add(dr); for(int i=0;i<dt1.Rows.Count;i++) { string str=dt1.Rows["no"].ToString(); DateTime dt=(DateTime)dt1.Rows["date_t"]; string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')"; SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase); cmd.ExecuteNonQuery(); MessageBox.Show("saved"); } When I run the above code, data is inserted into the table The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column. Is there any conversion needed for millisecond values?
Hi thereI'm still finding my way in SQL server so the problem might be very simple (hopefully...).Would anybody have any idea why: grant select on table1 to ReadGroup works fine, and grant create table to ReadGroup works fine, yet grant select to ReadGroup results in Server: Msg 156, Level 15, State 1, Line 1Incorrect syntax near the keyword 'to'.? Any help would be immeasurably appreciatedCheers!
well...here is the error I get...Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 1, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 2, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 3, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 4, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 5, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 6, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 7, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 8, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 9, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 10, column 8 (Splits). Msg 4863, Level 16, State 1, Server TRADES, Line 1 Bulk insert data conversion error (truncation) for row 11, column 8 (Splits). Msg 4865, Level 16, State 1, Server TRADES, Line 1 Could not bulk insert because the maximum number of errors (10) was exceeded. Msg 7399, Level 16, State 1, Server TRADES, Line 1 OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.]. The statement has been terminated.Is the problem something about the :OD:0A at the end of each row, rather than just the :0A??? If so, how the heck do I specify that? I think based on my testing so far (also tried ' ' as the rowtermination param, but then it gives me a truncation error for the first row only, leading me to think it cannot find the end of the row that way.
I am still looking through archives and on the web, but have not seen anything specific to my issue yet...and cannot believe that I am the first to BULK INSERT this kind of data.
I'm trying to setup transaction replication between 2 servers. This is a one-way replication: Server A to Server B, not Server B to Server A.
I am able to replicate all the tables except one. I added commands to the agent so that it would create an output file, possibly with more or better information.
Here is a portion of the error causing the failure
Agent message code 20037. The process could not bulk copy into table '"tblSuppContractFee"'. [5/5/2006 8:02:10 PM]01sqlft003.distribution: {call sp_MSadd_distribution_history(4, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, 6, 0x01, 0x01)} Adding alert to msdb..sysreplicationalerts: ErrorId = 65, Transaction Seqno = 000075400000ff9b000b00000002, Command ID = 6 Message: Replication-Replication Distribution Subsystem: agent 01sqlft003-EDGE-01SQLFT004-4 failed. The process could not bulk copy into table
[5/5/2006 8:02:10 PM]01SQLFT004.EDGE_REPLICATION: exec dbo.sp_MSupdatelastsyncinfo N'01sqlft003',N'EDGE', N'', 0, 6, N'The process could not bulk copy into table ''"tblSuppContractFee"''.'
Can somebody help me in finding a solution for this error? I don't see any Error Text and there are no resources available for the error code throwing up in the log file.
I have a dataflow step (flat file -> Sql Server Destination), with a batch size of 2500 records. It fails consistently around 3.6 million records in, with only this error -
[SQL Server Destination [4076]] Error: Unable to prepare the SSIS bulk insert for data insertion. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "SQL Server Destination" (4076) failed with error code 0xC0202071. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. [DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202071. There may be error messages posted before this with more information on why the thread has exited. [Flat File Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. [DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (1) returned error code 0xC02020C4. 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. There may be error messages posted before this with more information about the failure. [DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread1" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
How can I debug this further to see what's going wrong?
I am currently struggling with a problem bulk inserting data into sql server.
The application I am writing is multi-threaded and downloads about 2000 records every 2 seconds on x amount of threads (depending on bandwidth).
What I would like to do is find a 'friendly' way to insert this data into sql server without hammering the cpu.. I have tried the following with little success.
1, Using a single insert and thread.sleep(x * 20) to allow for massive data input, altough this made the application more stable and lowered cpu usage to very little the data takes about 60 times longer to download and process into the database.
2, Using a SqlDataAdapter and DataSet and updating the database via the .Update method of the data adapter.. Simply this was awful and took forever to process the data into the database.. (Took about 30 seconds to process 2000 records and Command Timeout was high).
3, Using OpenXML in SqlServer and parsing the data as an XML string (nText), although this method is fast its still very CPU intensive. I have to set the command timeouts very high to allow for this approach (because of the multi-threaded nature of the app).
Does anyone have any idea's on a cpu friendly approach to this problem ??
I'm using asp.net and the SqlBulkCopy command to insert a few lines of data into a table. I have the SqlBulkCopyOption.FireTriggers set, and a simple trigger on the table to take the inserted data and update a couple of other tables. Works fine if I only insert one record, but as soon as there are multiple records it fails.
So I presume that the trigger isn't firing on every line, but only once when the bulk insert is done?
Am I missing a trick? I can post some code if it will help - I'll have to get a screengrab of the error too.
The following produces no errors, but does not place any rows in the table:
USE MoneyManager GO BULK INSERT DailyPrices FROM 'C:DownloadsSecuritiess20070105.dat' WITH ( firstrow = 2, formatfile = 'C:DownloadsDailyPrices.fmt', errorfile = 'c:downloadsprice_error_file.txt' )
Hi at all,I'm trying to bulk insert a uniqueidentifier column from unicode file.In my file I have guid generated from c# application and they areformatted in this way (separated by "|") :guid | field1 | field2fc0c0c42-438e-4897-96db-8b0489e873ef|field1|field2In my destination table I have three column:id (uniqueidentifier)field1 (nvarchar)field2 (nvarchar)I use in bulk insert a format file like this :9.031SQLNCHAR00"| "1IDLatin1_General_CI_AS2SQLNCHAR00"| "2Field1Latin1_General_CI_AS3SQLNCHAR00"| "3Field2Latin1_General_CI_ASand I use this scriptBULK INSERT [dbo].[KWTA2] FROM 'd:WTA2.txt'WITH (FORMATFILE = 'd:wta2Format.FMT')It doesn't work, it prints outMsg 8152, Level 16, State 13, Line 2String or binary data would be truncated.I've also tried to specify in FMT file SQLUNIQUEID instead of SQLNCHARand it works perfectly but it imports another data. For example theguid fc0c0c42-438e-4897-96db-8b0489e873ef became00350031-0039-0033-3100-300030003000Please can you help me?Why sql converts alphanumerical GUID into only numbers ID?How can I bulk insert GUID? (I didn't find anything googling around : )Thanks!Bob
Hi Andrea, I have made a table which contain data inserted manually and also data that was inserted by using bulk insertion. I have no problems using the table with Grid View. But when I try to use a query like the following:
SELECT * FROM dbo.last WHERE VMake = 'Honda' AND VType = 'sedan' AND VColor = 'Red';
I would only get the data that were inserted manually. When I use the same query to filter data from a table that the data was inserted by using bulk insert, I get column names but no data.
Using SS2K, I'm getting the following error while bulk inserting:Column 'warranty_expiration_date' cannot be modified because it is acomputed column.Here is my bulk insert statement:BULK INSERT dbo.TestDataFROM 'TestData.dat'WITH (CHECK_CONSTRAINTS,FIELDTERMINATOR='|',MAXERRORS = 1,FORMATFILE='TestData.fmt')The computed column is not referenced in the format file and the data filedoes not contain the computed data.Thanks
I am trying to BULK INSERT csv files using a stored procedure in SQL SERVER 2008R2 SP3. Although the files contain several thousand lines and BULK INSERT returns no errors, no data is actually imported into the table. Every field in the table is a NVARCHAR(50) datatype.
Here is the code for the operation (only the parameters for the insert itself):
set @open = 'bulk insert [DWHStaging].[dbo].[Abverkaufsquote] from ''' set @path = 'G:DataStagingDWHStagingSourceAbverkaufsquote' set @params = ''' with (firstrow = 2 , datafiletype = ''widechar'' , fieldterminator = '';'' , rowterminator = '' '' , codepage = ''1252'' , keepnulls);'
The csv file originates from a DB2 database. Using exactly the same code base I can import several other types of CSV files without problem.
The files are stored on the local server with as UCS2 Little Endian and one difference is that the files that do not import do not include a BOM. The other difference is that the failed files are non-UNICODE files.
I just wanted to insert only SubjectIds into my table 'Subjects' which has the follwing schama ignoring the classes The row delimeter is " " and the column delimeter is ' | '
Table Subjects {
ID (Autoincrement) SubjectId varchar(20) }
Can any one provide the format file for doing this or suggest anyway to do this? Please do note that the file may contain millions of records
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) 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.
Hi, I have a data file which consists of data as below, 4 PPU_FFA7485E0D|| T_GLR_DET_11||
While iam inserting into table using bulk insert, this pipe(||) is also getting inserted into the table, here is my query iam using to insert the data using bulk insert.
BULK INSERT TABLE_NAME FROM FILE_PATH WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''')
I keep getting the following error message but I don't see what's wrong with my code
Server Error in '/Admin' Application.
Arithmetic overflow error converting expression to data type datetime.The statement has been terminated. 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: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Source Error:
Line 147:cmdSql.Parameters.Add("@event_active","1") Line 148: Line 149:cmdSql.ExecuteNonQuery() Line 150: Line 151:pnlForm.Visible = FalseSource File: c:hostingwebhost4lifememberNYACOAadmincalendar.aspx Line: 149 Stack Trace:
hi there, i have a calendar that i put into a string ilke this string str = Calendar1.SelectedDate.ToShortDateString(); the result is dd/mm/yyyy date which is great, but for inserting into my DB (MSSQL) it needs to be a datetime field, however when i convert it Datetime dtDate = Convert.ToDateTime(str); it takes my date and adds 00:00:00 onto the end and this is not what i want! i just want the dd/mm/yyy how do i do this, it has to be simple but i have been searching for hours and cant find anything, i am using ASP.NET 2 and C# Thanks
Short version: The best/fastest way to load large amounts of data from a comma delimited text file into an SQL Server table. Where the text file contains date fields in ccyy/mm/dd format and the SQL Server table defines those fields as datetime data types.
Details: When I attempt to load files (using either bcp or BULK INSERT) containing datetime data the load process errors because the datetime fields in my text file are in ccyy/mm/dd format and the default format for SQL Server is mm/dd/yy. I have been unable to change the default format by using the SET DATEFORMAT statement (apparently the SET DATEFORMAT statement will not work for bcp because bcp runs outside of the SQL Server session???). The only alternatives that I have come up with are: 1) Change the format of date fields in the text file from ccyy/mm/dd to mm/dd/ccyy. 2) Create a temporary table that defines the date fields as a char(n) datatype. Then load the data into the temp table. Then SET the DATEFORMAT to ccyy/mm/dd. Then copy the temp table into the permanent table (the permanent table using datetime data types).
Both of these alternatives would require additional processing time. Since this is a process that loads large amounts of data on a monthly (soon to be weekly) basis, speed is of the essence.
BULK INSERT itis.taxonomic_units FROM '<dir path to input file>/taxonomic_units.txt' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|', KEEPIDENTITY, KEEPNULLS
)
Here is a sample of a row that I get an error when it is processed through the above BULK INSERT statement: 50||Bacteria||||||||invalid||No review; untreated NODC data|unknown|unknown||1996-06-13 14:51:08.0||||1|10|07/29/1996|| The error is:
Server: Msg 4864, Level 16, State 1, Line 1Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).
Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.
I got a few *.csv files which I have to import in a table. The Insert looks like this:
bulk insert actocashdb.dbo.MyTable from 'c:UpdatesPLUArtikel_01.csv' with ( fieldterminator = ';', DATAFILETYPE = 'char', tablock, codepage = 'ACP' )
There is a field which contains a Date and Time in US-Format (e.g. 08/15/2007 03:15:00 PM). Some of my SQL - Servers (in fact they are just MSDE) are configured as German language. So, when I try to Insert the File I get a Type Mismatch Error, because the MSDE awaits the Date in German Format (e.g. 15.08.2007 15:15:00). Are there any options somewhere so I can Insert this Date? NOTE: It is not possible to reconfigure the MSDE to English.
I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand. The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value�), so I don’t know how I can insert this value when the field is blank? Can anyone shed some light please? Thanks
Hi, I am working with "sql server enterprise manager". How can I insert datetime(i.e. date) value in the datetime column in my database using an sql command?
Eg. If I have a table named Table1 in my database, and this table is composed of one primary key column and one datetime column (named dateofreceipt), how should the syntax look like?
HiI am trying to insert value retrieved from Now() into a datetime field in my MSDE database, but I am getting the following error, and I have no idea what is going wrong.Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Here is the code I am using:Dim user As String = MyContext.User.Identity.Name.ToString Dim TimeDate As DateTime = Now() Dim status As String = "Pending"
With SqlOrders.InsertParameters .Item("UserName").DefaultValue = user .Item("OrderDate").DefaultValue = TimeDate .Item("Status").DefaultValue = status End With SqlOrders.Insert()The date is being returned in this format 23/03/2006 02:01:52, which is the same format as it should appear in the database. could anyone please tell me where I am going wrong?
Hello all.I am attempting to insert a row into a table with a datetime column:When the insert statement contains a value for the millisecond portionof the data time column: ie. {ts '2003-11-05 12:02:43:2960'}I get 'Syntax error converting datetime from string'When I insert a value like: {ts '2003-11-05 12:02:43'}with no millisecond value it succeeds.Any help would be appreciated.Thanks
i use this statement to insert into sql tablecmdInsert = New SqlCommand("insert into empbill ([deptcode],[personNo,[entrydate]] ) values(" & Val(eno.Text) & "," & Val(TextBox5.Text) & ", " & dd.Text & "')", db) cmdInsert.ExecuteNonQuery() dd.Text value is 22/5/2008 it come from datetime calender the entrydate field datatype is datetime i found data always 1/1/1900 00:00:00 how i enter my date
Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer. That's my problem: if the server is in english version, I have to insert date with this code:
DateTime.Today.ToString("MM/dd/yyyy")
instead if the server is in italian version, I have to insert date with this code:
DateTime.Today.ToString("dd/MM/yyyy")
Is there a way to insert a date in standard way, without knowing the server version?
Using Server Explorer in VB 2005, I am manually entering data in a table in a SQL Server 2005 Express database that includes a DateTime field. I have tried every conceivable format, but no matter what I try get one of these 2 errors:
1. String was not recognized as valid DateTime
2. Operand type class; text incompatible with DateTime
I have Googled this to death, but no example which involves trying to enter the data manually, say from Server Explorer.
Formats tried include all datetime formats (mmddyy, yymmdd, using dashes or slashes, enclosing in single quotes or pound signs).
I would appreciate if someone could please give me an example that I can literally insert without error.
Im trying run a stored procedure, but I am getting the following message when trying to execute it.
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
which seems strange, as there is no integer going to a datetime field. Also, it does not return the value that is incorrect, nor the parameter that is failing. If there is anyway to view this in VS 2005 Team suite, please let me know.
It should also be noted that when I execute this sproc from within sql management studio, it executes fine.
This is the stored procedure im trying to execute...
in my .net app, all the datetime objects are valid dates, so I could not see why this would be generating the error that it is, so I ran the SQL Profiler, and this was the output. Again, i cannot see where im going wrong...
please note, I have seperated the output from the single slab of text, making sure I didnt remove anything...
Hi all,I am getting this error when insert values from one table to another inthe first table the values are varchar (10). In the second they aredatetime. The format of the data is mm/dd/yyyy to be easily convertedto dates. The conversion in this case is implicit as indicated in SQLServer documentation. Here is my query:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionUNIONSELECT program_id + 100000, program_name, start_date, end_date,campaign_id AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.programsUNIONSELECT execution_id + 200000, execution_name, start_date,end_date, program_id + 100000 AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.executionsUNIONSELECT wave_id + 300000, wave_name, start_date, end_date,mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL ASListIDFROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executionsWHERE mysqlfactiva.dbo.waves.execution_id =mysqlfactiva.dbo.executions.execution_idI am referencing programs table two times. If I just select this all Iget all data I need. When doing insert I get a message:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value. The statement has been terminated.If I execute just first part of the query before first union, I insertdata fine:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionAs soon as I use union I get the above error. This is very strangesince even when I execute the query using first union where the datescome from the same table 'programs' I get the error. Why I can insertfrom programs first time and can's second time?Any help will be appreciated.Thanks,Stan
Hi, Assume I have a table name "myTime". This table is simply only have 1 (one) DATETIME field "MyTestTime" (also serve as a primary number).Table MyTime- MyTestTime : SQLTYPE DATETIMETo insert a new row into this field, I simply wrote :SqlCommand sqlCommand = new SqlCommand("insert into MyTime values('2006-01-09')", sqlConnection); I got the value of "2006-01-09" from a textbox or other relevan control.I realize when I try to use "SELECT * FROM MyTime" statement, MSSQL server 2000 automatically convert my date value from "2006-01-09" to "01/09/2006" (from YYYY-MM-DD to MM/DD/YYYY). I don't know why this one must be converted to MM/DD/YYYY automatically (I believe this behavior is depend on some "setting option" in my MSSQL server - but I don't know which one).The challenge is :In my country, the actual date format is like German Date format (DD-MM-YYY). Well I know this is only "Customization" problem. But how insert datetime value given from sql query to a datetime variable?// Connect to database, make a query, get the datareader result, and bla bla blaDateTime aDateTime = new DateTime;aDateTime = Convert.ToDateTime(myDataReader["PostDate"].ToString());// close connectionMy question isHow can I make sure that aDateTime's day is 09 not 01. How my program know that 09 is day not month. I can't use string.split() method because it's possible that my database setting will change from "mm-dd-yyyy" to "dd-mm-yyyy"thanks
