In my project, I have came across a field in which, I have to insert only time in my sql server 2005 version database through ASP.NET using C#. I dont want the date to be entered/displayed. I also would like to know, how I can be able to get time from the user(which control you would suggest me to use for getting time) and store it in sql server. I have to count the no. of hrs and minutes in my project, so need this help from anyone who can suggest me. Thank you.
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. 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.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing "OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere" " on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager. The Expression is as follows
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While "Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures. 2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ? 3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
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?
The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.
When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.
At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.
When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...
=IIF(reportitems!textbox66.
When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.
If I complete the IIF statement manually so that it spells out .....
So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?
Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
I've got XEON x64 server and after SQL Server 2005 installation found that the platform in property says AMD64 and not INTEL64 as expected. I was assured by our service provider that AMD64 platform is the correct one as it was compiled for INTEL x64. Does anybody have an answer to it?
hi i export tables from Local to Online Server But some tables have a column with Identity=True But after export tables that Property is not True How I can change it True With Query Analyzer????????/
Hello I have a table with two columns. First column is type and second column is ID. ID column must be unique and I want to give values in ascending order to the new records. For example first record's ID is 1 ,second one's is 2 ... How can I do this in sql server 2005?
Hi All!We are doing new development for SQL Server 2000 and also moving fromSQL 7.0 to SQL Server 2000.What are cons and pros for using IDENTITY property as PK in SQL SERVER2000?Please, share your experience in using IDENTITY as PK .Does SCOPE_IDENTITY makes life easier in SQL 2000?Is there issues with DENTITY property when moving DB from one serverto another? (the same version of SQL Server)Thank you in advance,Andy
We are setting up a job for the SQL Server Agent via SSMS. The Job Step Type is SSIS.
In the Job Step Properties window, on the Set values tab, you can enter Values to override your package variables - normally all well and good.
However in this particular case, the variable Value contains semicolons ( - it is a Connection String for an ODBC driver. Eg: Driver={Client Access ODBC Driver (32-bit)};system=MYSERVER;...
The behaviour for this Value is weird:
If the Value is not surrounded with double quotes ("), the job fails with "The command line parameters are invalid." If the Value is surrounded with double quotes ("), the job will run as intended. The catch is: that entry and any subsequent "Set Values" entries disappear next time the Job Step Properties window is opened.
This looks like a bug with the parsing of those strings by the Job Step Properties window?
I wanted to create a package to copy the objects from one database to another and replace those object if they already exist. Therefore, before the package executes you do not know whether all the objects exist on the target server or only some of them.
Using the 'Transfer SQL Server Objects Task' I have found that I cannot get this to execute cleanly by itself. If I set the 'DropObjectsFirst' to false then an error is thrown if the object exists and if I set it to true then an error is thrown if it does not exist.
In order to get round this I have had to create an 'Execute SQL Task' to list all the objects and then go through them dropping them on the target server in a for each loop before executing the 'Transfer SQL Server Objects Task' with 'Transfer SQL Server Objects Task' set to false.
However, is there a better way of achieving this or am I missing something in the 'Transfer SQL Server Objects Task'?
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table: ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time. Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
Hi, I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date '31/08/2006 23:28:59' I get the error "...datetime data type resulted in an out-of-range datetime value" I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be? cheers, Ernest
I am inserting date and time data into a SQL Server 2012 Express table from an application. The application is providing the date and time as a string data type. Is there a TSQL way to convert the date and time string to an SQL datetime date type? I want to do the conversion, because SQL displays an error due to the
My date and time string from the application looks like : 3/11/2014 12:57:57 PM
Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot.
I am trying to get a function I created in VB 5 for Access and Excel to work in SQL 2005. I was able to update the old VB code to work in VB 2005. I compiled and made a .dll, and I was able to register the new Assembly in SQL Server. When I try to create the Function, I get an error:
AS EXTERNAL NAME FluidProps.[FluidProps.FluidProperties.Fluids].Temperature
Error returned:
Msg 6573, Level 16, State 1, Procedure Temperature, Line 21
Method, property or field 'Temperature' of class 'FluidProps.FluidProperties.Fluids' in assembly 'FluidProps' is not static.
Here is the code (part of it) in the VB class:
Header:
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
Imports System.Security
Imports System.Security.Permissions
Namespace FluidProperties
'Option Strict Off
'Option Explicit On
Public Partial Class Fluids
Function:
Function Temperature(ByRef FluidName As Object, ByRef InpCode As Object, ByRef Units As Object, ByRef Prop1 As Object, Optional ByRef Prop2 As Object = Nothing) As Object
If I change the Function Temperature to Static, I get an error that functions cannot be Static. Its been a long time since I created the code and am having a hard time in my older age of getting the cobwebs out to make it work.
I have no problem getting the function to work if I call it from a VB form....so what do I need to do to get it to work on data in my SQL server?
select convert(datetime,'04-20-' + right(term,4)) as dt, 'Deposit' as type, a.* from dbo.status_view a
where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.
The SQL above returns
2004-04-20 00:00:00.000 Deposit ...
Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:
select * from ( select convert(datetime,'04-20-' + right(term,4)) as dt, 'Deposit' as type, a.* from dbo.status_view a ) where dt >= a.submit_date
I get the following error:
Syntax error converting datetime from character string.
Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...
Hi,I have a text file that contains a date column. The text file will beimported to database in SQL 2000 server. After to be imported, I wantto convert the date column to date type.For ex. the text file look likeName dateSmith 20003112Jennifer 19991506It would be converted date column to ydm database in SQL 2000 server.In the table it should look like thisName DateSmith 2000.31.12Jennifer 1999.15.06Thanks in advance- Loi -
Hi All i have a column in the table its for only Year and the datatype is of Datetime and it is used for some computations so i have not used Varchar or numbers so can any one help me out how only capture year in the field...So whenever i use Year it gives error "String was not recognized as Valid DateTime" please any one help me out
I'm going slowly insane trying to get a Date AND Time value stored in SQL 2000 from ASP.NET 2.0. The following works fine in Query Analyzer, but fails when run from ASP.NET:INSERT INTO tbl_WIP_DATA_DateTime (ObjectInstanceID, ObjectPropertyID, PropertyData_DateTime) VALUES (32226,7,'20060120 10:43:44') Does anyone have any suggestions? CheersVatic
hi, How do i convert a varchar field into the datetime data type? the reason i need this lies in the requirement that in the earlier data base the column that is hlding the date value is having the data type as varchar. and in the new design the column data type is datetime. i am using sql scripts for the data migration from the older design to the newer and got stuck with this datetime convertion issue. do let me know the best possible solution.
following are the sample data that is theer in the older table for the date.
12/12/2003 1/13/2007 01132004 1-1-2004 1.2.2001
there is no uniformity of the data that is stored currently.
I have a table that has a unique ID and a datetime of when something changed.
See example: IDÂ Â Â TimeStamp 16094Â Â Â 2013-11-25 11:46:38.357 16095Â Â Â 2013-11-25 11:46:38.430 16096Â Â Â 2013-11-25 11:46:38.713 16097Â Â Â 2013-11-25 11:46:38.717 16098Â Â Â 2013-11-25 11:46:38.780
[Code] ....
Is there a way I can calculate the difference between row 16106 and 16105 and enter it in line 10601.
I have very little C# experience and I am in over my head here. This script will insert all data into the data base after I have removed all refference to the date.
I know I have to change the datatype in code, but I have tried everything I can think of.
Any suggestion would be greatly appreciated.
The .aspx.cs file is below:
using System; using System.Configuration; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.IO; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls;
private int SaveToDB(string newsTitle, string newsBody, string d, byte[] imgbin, string imgcontenttype) { //use the web.config to store the connection string SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
SqlCommand command = new SqlCommand( "INSERT INTO Cover (cover_title,cover_body,cover_date,cover_image,cover_imgcontenttype) VALUES ( @news_title,@news_body,@news_date,@img_data,@img_contenttype )", connection );
Can someone tell me if this is a SQL Server bug? I tried this in both version 7 and 2000, the results are the same.
DECLARE @timeA DATETIME DECLARE @timeB DATETIME DECLARE @msDiff INT
SET @timeA = GETDATE() SET @msDiff = 0
WHILE @msDiff <= 10 BEGIN SET @timeB = DATEADD(ms,@msDiff,@timeA) PRINT 'If adding ' + CONVERT(VARCHAR,@msDiff) + ' milliseconds to Time B, then Time B is ' + CONVERT(VARCHAR,DATEDIFF(ms,@timeA,@timeB)) + ' millisecond greater than Time A' SET @msDiff = @msDiff + 1 END
This seems like a serious bug if an application depends heavily on milliseconds comparison.
