In Beta 1 & 2 there was a UTC date and a true Date only type supported. I guess these got pulled from the final bits. What would be the best way to create a true Ansi standard date/timestamp (ie. one that includes timezone). I'm thinking CLR probably. If so is there a way to get ahold of the old UTC date functionality (from Beta 2) so that if it is brought back at some later service pack we would be somewhat in compliance with it's features and functions in our homebrewed UDT.
Hi, I'be just started to import data into sqlserver from ms access, and it automatically stores my dates as smallint length 4. Is this ok? For display purposes I want dd/mm/yyyy. Will 4 bytes store this?
Hello All,i have a sql database with a table as follows. CustID (PK, Varchar(50) not null )Address ( VarChar(max), null)City ( VarChar(max), null)State( VarChar(max), null)Zip ( VarChar(5), null) ----------------------------- this is not working cuts off the leading zero like ( 01234) is shown as 1234DateShipped( datetime, null) --------------------- this display 05/18/2008 12:00AM. i just want to display (05/18/08).how can we do the above two. any suggestions please. i tried smalldatetime no luck . i appreciate it.Thanks.
I've been writing this stuff for a while, and can't seem to come to the conclusion of how I should be retrieving data and assigning this data to variables.
Since i'm using SQL Server, I'm convinced that I should be using the datareaders GetSqlDouble (or whatever) function, but this would mean i need my local variables to be one of the SQL types. The problem with that is, that there will have to be lots of conversions done by me to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order to retrieve them correctly (Null values included), I need to retrieve them with GetSqlDateTime(), then when it comes time to display the date in a table, i must first check for nulls, then convert to a string. This seems to be very cumbersome. Would I be better off just using GetDateTime(), and the .ToString method, and ignoring Sql Types all together?
so, basically, how are you guys using your sql server data? with the supplied sql types, and doing all of the post-processing work manually? I feel like i'm having trouble conveying my issue...hopefully someone knows what i mean....i'd just like some direction to save trouble in the long run, since i feel like there's got to be a better way...
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
The following columns cannot be mapped: [Department, DEP_CLEGALCODE] One or more columns do not have supported data types, or their data types do not match.
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
Hi, Please help me to know this question. How many types of UDF are in SQL SERVER 2000? Please tell me if anybody knows the answer. Thanks in advance, Joydeep
HI i have a big problem i have an exericse to do on SQL for school but i havne tbeen told what they are. All i have to do is to match the follwing togehter and say why they would use it any ideas?
in my work I use some "objects", that i tune using "Parameters" So i have table MyObjects And ObjectParameters-OP In the table OP i want field for Parameter Type(Type)... now i using type Varchar(63) and then i need for parameters of money type, i will get it by SELECT * FROM ObjectParameters WHERE Type = 'money'... at the sql.ru i got an answer, where they suggest me using type "sysname", in MSDN i have found that it is equivalent nvarchar(128), i a cant find any differences between my solution and sql.ru solution.
AND main question is.. can i using ID for my types from View sys.types, And can i be sure that it will be the same ID in another version SQL Server (like 2000, 2008) or another installation of SQL Server for same type...
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
Hi, When creating a database in the Management Studio, you get to select the database owner, and should that be usually the Administrator and ASPNET account?Also, what is this Object Types option for? There seem to be only one "Login" option in there, what does that serve as?
i want to check the price of sql server 2005 in my country as a consideration to take when deciding on wich db to build.i dont understand the licensing method. what 5 clt stands for ? whats the basic 64 bit 1 processor dual core license i need for a commercial website ? thanx,
I have a really dumb question regarding SQL data types. I have a couple columns in a table that are specified as MONEY. These columns are being read from my web app and displayed on the website for reference when filling in other information.
My problem is that when it is displayed on the website it give four decimal places instead of two. For example I want it to report $33,000.29 but what is actually displayed is $33,000.2965.
How can I set up either SQL or my web app so that it only displays the two decimal places? I've looked into changing the datatype already but SMALLMONEY and MONEY have the same type of decimal values.
I ham trying to convert a 'System.Data.CommandType' to 'System.Web.UI.WebControls.SqlDataSourceCommandType'.
but I get Error 4 Cannot implicitly convert type 'System.Data.CommandType' to 'System.Web.UI.WebControls.SqlDataSourceCommandType'. An explicit conversion exists (are you missing a cast?) D:InetpubwwwrootHelp_DeskApp_CodeHelpDeskSql.cs 99 42 http://localhost/Help_Desk/ My problem is I cant seem to get a explicit conversion to work. if it cast it I get an. Specified argument was out of the range of valid values.Parameter name: value . when running the page. but no build errors, does anyone know how to cast this?
Hi, I am getting this error: {"XML parsing: line 1, character 43, unable to switch the encoding"} System.Data.SqlClient.SqlException when I run the code below. I know it is caused by the fact that the encoding of the XML file I'm trying to insert is not utf-16, but rather utf-8. However I would like to be able to enter any encoding. Is this possible? If not is there a way to convert the encoding before I insert? Or any other ideas anyone might have. Thanks! XmlDataSource xds = new XmlDataSource(); xds.DataFile = tbLink.Text.Trim(); xds.XPath = "rss/channel/item"; XmlDocument xmlDoc = new XmlDocument(); xmlDoc = xds.GetXmlDocument(); string strConn = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; sqlComm.Parameters.Add(new SqlParameter("@XMLData", xmlDoc.InnerXml)); strSQL = " INSERT INTO tblCastStore ( intCastID, CastXML ) VALUES ( @@IDENTITY, @XMLData );"; sqlComm.CommandText = strSQL; try { sqlConn.Open(); sqlComm.ExecuteNonQuery(); sqlConn.Close(); } catch (SqlException se) { lblError.Text = se.Message; }
I hope someone can help me. I am running a visual basic program which creates a recordset based on a stored procedure. No matter what cursor type I set I always end up with a static cursor on my recordset. I think the stored procedure is over-writing the cursor type. As I know very little about TSQL, I don't know how to fix this. Any Ideas?
I am looking for a chart of SQL Server data types and information about them, such as usage, constraints, etc. Could anyone point me in the right direction?
I'm just getting used to using SQL 7 Database and am looking for some recommendations on what DATA TYPES to use when designing tables.
The site I am working on now uses cold fusion to enter, update, and insert mainly text and articles. Now the problem with the articles is that some of them are pretty big. And when inserting or updating them, if they go over the limit of characters I get an error.
I know in access I used to use the data type "MEMO" and be able to put a large piece of info in that field.
What's the equivalent for SQL server databe?
What are the most used/common or recommended data types that should be used when putting tables together?
Hello, dear Friends! My situation is a following: I have some not big databases with only English data and some conteining data in Hebrew. My question is: May I manage two collation types- meaning the database collation type will be for example some kind of Latin and some databases' collation will be in Hebrew. If you have any experience of managing DB's in two absolutely different languages (Japanesse, Russian, Chinesse, Arabic with English) please tell me about it. Best regards. Marina.:rolleyes:
hi, i'm trying to build an online forum using sql server 2000 and coldfusion mx.
could anybody help me as to what datatypes i should use for my database fields?
i'll be using 2 simple tables to start with, ie TOPICS and POSTS. each will contain fairly basic information such as name of poster, time, data, title, message etc.
any help would be greatly appreciated. thanx, zaffi.
Which data type do I need to assign to my field to let users enter characters such as +=-_| etc....pretty much every single character from a standard keyboard....
i am using sql server 2000, i am new to databases, could you please tell me what are the different character types, example "varchar , numeric...etc" How to find out the difference between them.... Where do i find a tutorial for this kind of basic knowledge ?
is it possible to have several replication types within the same database? for example, for two tables i want "merge replication" with a central server, where twice a day data is merged? and for three other tables, i want "transactional replication" with a central server, where data is immediately inserted?
I am pretty new with SQL and was dabbling in a test database that is linked with a software application.
I made a change to a table - Lets call this (Table1). Table1 has a multiple columns.
Column1 is defined as numeric(9) Column2 is defined as varchar(20)
In this case, I wanted to make all values in Column2 = Column1 that did not have a value defined so I ran the following query:
update Table1 set Column2 = Column1 where Column2 = ''
This took effect but the particular software application doesn't recognize these values because they are the wrong data type.
How can I convert all data in a column to varchar(20) if it is another data type (i.e. in this case numeric(9))?
I've tried in Enterprise manager to redesign the table and adjusted the length to 21 and then back and saved it but this did not work. I was looking at Convert and Cast functions but don't know how to write one to accomplish what I need to do here.
Half of the data in Column2 is varchar(20) and half is numeric(9). I want all to be varchar(20).
Im comparing values. if i add the values which is numeric or monetary in the column and if it is greater than zero it will insert to a table, but the datatype of my column([Day 30 AMT] etc. is char. i want to implement like these like these because it cant return a result if i query..
I want to store a decimal value which I get from an execute sql task into a package variable. Which data type should I specify when creating the package variable?
I'm wondering when it's connectig to SERVER1, is it using windows authentication? is there a way to setup so it uses SQL Server Authentication? Thanks,
I have successfully made a couple of SSIS packages to read data from a legacy AS400 to SQL Server 2005. These are working fine. I am now working on one that is trying to pull some financial data and I keep getting the following error for the decimal columns:
"The component ciew is unavailable. Make sure the component view has been created. The input column "input column AMT1 (2870) cannot be mapped to "external metadata column AMT1(2852) because they have different data types."
Now I made sure that the data sitting on the AS400 is of type decimal and the SQL server column is the same. What else do I need to look for?
