How Do You Determine The Base Data Type Of A Column!

Sep 28, 2001

Does anyone know how to determine the base datatype of a column? I've tried using sp_columns but if there are user defined datatypes on the column it returns that name instead of the base datatype. I've also tries accessing systypes and syscolumns to determine the base datatypes.

Any tips or trick would really be apreciated!

Kurt

View 4 Replies


ADVERTISEMENT

SQL Server 2012 :: Determine Which Column Is Causing Error Converting Data Type Varchar To Numeric?

Aug 14, 2014

I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:

Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.

My problem is that Line 6 is:

set @brn_pk = '0D4BDE66347C440F'

so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?

View 3 Replies View Related

How To Determine Day Of Week From Datetime Data Type

Feb 16, 2001

Our reporting group would like to generate reports based on day of the week. Is there a function or other means of determining the day based on data in a datetime field? Thanks for any assistance in advance.

Brad

View 2 Replies View Related

How Do I Use The Visual Studio 2005 To Make A Data Base Column Unique?

Jun 14, 2007

I am sorry if this is the wrong forum for this question but I can not find better fit.
I am using Visual Studio 2005 to write an asp.net web site which includes an SQL data base. In the databaseI have a table called 'ACCOUNTS' which has primary key column called 'ID' and a nvarchar column called 'Name'.
I want the values in the 'Name' column to be unique in the table so that all accounts have unique names. I don't want to make the 'Name' column the primary key because I want people to be able to change the name of their account after it has been created.
It seems to me there must be some way to add such a constraint to the table but I can find none. I have searched the documentation and seen loads of references to unique columns but I can't find any way to set a column to unique thru the IDE (or any other way). Can anyone help me?
Thanks in advance for any help. 
 

View 2 Replies View Related

Bulk Insert Task Failing On Data Type Conversion For A Destination Column Of Type Bit

Jul 6, 2006

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.

SSIS package "Package3.dtsx" finished: Failure.

View 5 Replies View Related

XML - Multiple Base For Derived Type Is Not Suppor

Apr 7, 2008

Hi,

Very basic beginner XML question. Is it possible to have an attribute name repeated in a schema ? I received an XML and XSD file for which I'm getting the error "multiple base for derived type on dte_effective is not supported." "dte_effective" is repeated under several elements. Is there a way to resolve this error by adding something to the schema to indicate that the date is specific to each element ? Can't figure this one out. I've googled the error and found nothing to resolve this.
XSD file sample is below...
Thanks, J.

<xs:element name="Element1">
<xs:complexType>
<xs:attribute name="dte_effective" "NewDate" use="optional">
<xs:annotation>
<xs:documentation>effective date field</xs:documentation>
</xs:annotation>
</xs:attribute>

<xs:element name="Element2">
<xs:complexType>
<xs:attribute name="dte_effective" type="NewDate" use="optional">
<xs:annotation>
<xs:documentation>effective date field</xs:documentation>
</xs:annotation>
</xs:attribute>

View 7 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

Determine Type By Variable?

Sep 25, 2013

I have a situation where I need to determine which type this record is. If type ID is 20, 23, or 25, then select F otherwise it is P.

Select FirstName
,Lastname
,address
, (If typeid = 20, 23, or 25 then F else P) as TypeDesc
From sometable

The number(20,23,25) are hardcoded in the stored procedure and I have them in a variable because there are over 30 different numbers.

View 3 Replies View Related

System.Data.SqlClient.SqlException: Syntax Error Converting The Varchar Value 'V' To A Column Of Data Type Int

Aug 31, 2006

 I am using  a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure;  SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse

View 3 Replies View Related

Column Data Type

Jun 17, 2005

Hello,  I would like to get the columns in a table, this works fine:SELECT syscolumns.* FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name = 'Customers'ORDER BY syscolumns.colidHowever, is there a way to get Column_Data_Type as for example "nchar" or "varchar" instead of having it as numbers.Can anybody help ?thanks

View 7 Replies View Related

Asking For Column Data Type

Feb 11, 2004

This is an easy one , I need to know the way to check programatically for an SQL server 2000 column data type , if the result is a code , which codes are for the diferent datatypes ( varchar , text , smallint , etc ) , if you send me an url is ok.

thank you

View 2 Replies View Related

Getting The Column's Data Type

Mar 10, 2008

Hi,
I'm trying to figure out the best way to determine the data_type of a column, given the table name and column name. Once I've determined it's a charactor string I can get the length using COL_LENGTH, but I can't fund a command or procedure that will return the data type.


Thank you.

View 1 Replies View Related

SQL Change Column Data Type

Aug 20, 2007

How do I programatically  change a column (say username) in a table (say tblusers) from varchar(25) to varchar(100)I am looking for something likealter tblusers set username as  varchar(100) I know the above statement in nonsensical but it conveys the idea. 

View 3 Replies View Related

ACCESS SQL COLUMN DATA TYPE

Oct 19, 2007

HELLO
i have an sql server database, with a table an some columns.
how can i get the data type of each column and the lengh define in the database table?
 

View 4 Replies View Related

Changing Column Data Type

Nov 23, 2007

I want to change a column's data type from bit to int.  There are data in the table already.  I'm wondering if it is save/correct way to issue the following command to change the data type for that column.ALTER TABLE database_tableALTER COLUMN my_bit_column INT; Thanks.

View 1 Replies View Related

Rename A Column And Its Data Type

Feb 27, 2008

What is the way to rename a column in a table along with its datatype.


Thanks

View 2 Replies View Related

Which Data Type Should I Use To Store XML In A Column In DB?

Jan 29, 2006

I have xml string that needs to be stored in a field in the DB. I was looking for recommendations for the data type I can use in such a scenario.

View 1 Replies View Related

Determine Rowguid In Column And Edit Column Information ASP.NET 1.1 C#

Dec 19, 2006

What query should I run? 
1. To determine whether a column is a rowguid or not using C# .NET 1.1
2. To add/modify column information and be able to set/change:
- Primary key
- Column Name
- Data Type
- Length
- Allow Null
- Default value
- Precision
- Scale
- Identity
- Identity Seed
- Identity Increment
- Row guid
 
Thanks a lot!

View 6 Replies View Related

Query Data Type Of Column In DB Table

Aug 14, 2006

Hi All
I want to retrieve the data type of table column and validate the input data whether same as data type of table column before insert into database. Any suggestion?
I use asp.net + msde
 
Thank you.

View 6 Replies View Related

How Do I Get Data Type Of Each Column In A Sqldatasource Control?

Sep 6, 2007

Let's say... the sqldatasource has 3 columns: TableKey(int), TableName(string) and StartDate(datetime) and i'm writing a method to return the data type based on the column name.. for example: GetDataType("StartDate") should return "datetime"...what should i do?

View 2 Replies View Related

Sql Script To Change Type Of Column With Data

Sep 29, 2007

I have a table say Friend which has a column "IsSingle VARCHAR(10)" and this column has values like yes or No
Now I want to change type of column IsSingle from VARCHAR to BIT, if I try to do it manaually SQL throws error that cannot convert yes to bit etc.
I know that we can write a script to do this task but i dont know how ?
Any pointers,links, suggestions will help me to start with.
 
Thanks for your help. 
 
 

View 2 Replies View Related

Create A Column With Binary Data Type

Feb 13, 2005

Hi, in reference to the article:

http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx

I would like to know how to create a 'password' column where the data type is a binary type of length 16
This is my table:

CREATE TABLE Staff
(
Namevarchar (50)PRIMARYKEY,
Password ???? NOT NULL,
Role varchar (50)NOT NULL
)

Please review my code. Thanks in advance for helping me out here!
-Gabian-

View 2 Replies View Related

@local Variable Column Data Type

May 23, 2001

In a stored proc, can you declare a local variable that is an existing column in a table & then based on other criteria, do an order by using the local variable?

View 1 Replies View Related

The Value You Entered Is Not Consistent With The Data Type Of The Column.

Jun 6, 2005

Hi..

I am trying to add new record to any table in my database.
But I can't.

I am tryting to add a value (1500 character) and it gives the message below

"The value you entered is not consistent with the data type of the column."



I have tried alot.
The result is my columns in my table accept maximum 900 character.

But the type of my column I try to add a value is text..

I am too angry with SQL

Please help

View 10 Replies View Related

Set Image Data Type Column To Null?

Oct 30, 2013

so i have a table with 25million rows. this table has an image data type column and i want to set this column to null. what is the most efficient way to archive my goal. SQL SERVER 2008

View 3 Replies View Related

Excel Column Data Type Issue

Oct 27, 2006

Ok, hopefully a simple issue with a simple fix. I'm importing data from an Excel spreadsheet with values such as: 10, 20, 20 Tall, 40, 40 Tall. The column imports but is treated as numeric with values 10, 20, 20, 40, 40. I set the cells to be treated as text in Excel. Any suggestions?

View 2 Replies View Related

The Value You Entered Is Not Consistent With The Data Type Or Length Of The Column

Feb 3, 2001

I keep getting this error:

'The value you entered is not consistent with the data type or length of the column'

when trying to enter data into a feild, the feild type is char and the length
is 100 i'm entering text 3 words long but no where near a 100 characters long
any one know why this is happening?

View 1 Replies View Related

Create Table: Want A Column With Money Data Type

Dec 14, 2007

Hi all!

I want to create a table. One of the columns should be in the data type MONEY with two digits on the right side of the decimal point.
How is that possible?

Thanks a lot and greetings from vienna

landau

View 2 Replies View Related

Can A Column Data Type Be Changed On A Replicated Table?

Dec 21, 2006

On sqlserver 2000 transactional replication:

How would I best go about changing a published table's column from smallint to int? I could not find anything about it in BOL or MS.com. I do not think EM/Replication Properties allows the change. I suspect I have to run "Alter Table/Column" on the Publisher and each Subscriber the old-fashioned way. Is that true?



Thanks!

View 3 Replies View Related

What Is The Best Practice To Change A Column's Data Type In A Very Big Table?

Dec 11, 2007



Hi All,

I am using SQL Server 2000 Enterprise Edition fully patched. Database is in Simple Recovery mode.

I need to change a column's data type from "int(4)" to "smallint(2)". I know for sure that there will be no data (precision) lost, because I know the possible values that this column could have.

My problem is that the table I am dealing with has 600,000,000 records in it. I dropped all indexes before I tried to alter the table. But still it is taking forever and filling up my 280GB disk with transaction log file.

I know that in Oracle, if I want I can turn off logging and do these kind of modifications relatively faster.

I was wondering if there is a way of disabling logging before running this alter command.

What is the best practice to handle a situation of this sort?

I appreciate your help.

Thanks in advance,
Sinan Topuz

View 3 Replies View Related

Column, Parameter, Or Variable #1: Cannot Find Data Type SqlDatareader

Sep 28, 2006

Hello Everyone,A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index)   {    if(dr.GetSqlValue(index) == null)      return SqlInt32.Null;    else      return dr.GetSqlInt32(index)   }I than allways get the following ErrorMessage:Column, parameter, or variable #1: Cannot find data type SqlDatareader.Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.Would appreciate any kind of help! Thanks

View 1 Replies View Related

Problem With Converting The Nvarchar Value 'xxxxxID' To A Column Of Data Type Int.

Jan 23, 2007

Hi,
I have a webform with a drop-down listbox, binded to a table and a GridView, binded to another table. My goal is when I select a value (country in my case) in the drop-down listbox to change what is shown in the GridView, based on the ID that I get from the drop-down listbox.
Here is the code (the important parts...):
Select a Country : <asp:DropDownList ID="CountriesDropDownList" runat="server" DataSourceID="SqlDataSource2" DataTextField="Name" DataValueField="CountryID" Width="220px" AutoPostBack="True">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT [CountryID], [Name] FROM [DS_Country]">
</asp:SqlDataSource>
<asp:GridView ID="ProvincesGridView" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="ProvinceID,CountryID,Name,ShortName,Description" DataSourceID="SqlDataSource1" BackColor="White" BorderColor="#E7E7FF" BorderStyle="None" BorderWidth="1px" CellPadding="3" HorizontalAlign="Left" >
<Columns>
..............................
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DineSelectConnectionString %>"
DeleteCommand="DELETE FROM DS_Province WHERE ProvinceID=@ProvinceID"
SelectCommand="SELECT ProvinceID,CountryID,Name,ShortName,Description FROM DS_Province WHERE CountryID=@ddlCountryID"
UpdateCommand="UPDATE DS_Province SET CountryID=@CountryID,Name=@Name,ShortName=@ShortName,Description=@Description WHERE ProvinceID=@ddlCountryID"
InsertCommand="INSERT INTO DS_Province (CountryID,Name,ShortName,Description) VALUES (@CountryID,@Name,@ShortName,@Description)">
<SelectParameters>
<asp:ControlParameter Name="ddlCountryID" ControlID="CountriesDropDownList" PropertyName="DataValueField" />
</SelectParameters>
</asp:SqlDataSource>
 
When I run it I get this error "Syntax error converting the nvarchar value 'CountryID' to a column of data type int.". If I specify the Type="Int" in <asp:ControlParameter>..</> I get "Input string was not in a correct format. "
This is supposed to be easy, but I can't make it work ! Any suggestions will be more than welcome.
Thanks.

View 10 Replies View Related

How? : Using A Varchar, Text Data Type Variable As Valid Column Name.

Jun 2, 2008

Cannot use dynamic sql in current context. So need some help regarding this.I am developing a stored procedure to update a table. Sending Column names as parameters, but not able to use them as given below.INSERT INTO Books (@Column1, @Column2) values.. Any way to execute without using dynamic sql?..Thanx.   

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved