My report has two groups, company and error type for each company. Company1
Functional Error Data Error Other Error My goal is to show the subtotals for each error types when I show Company's subtotal/total; in the group 1 footer area. Total for Functional Error: Total for Data Error: Total for Company1:
I am designing my first SQL database and need some insight on datatypes. What datatype is equivalent to autonumber like in MS Access. When I convert an auto number field to to SQL it comes as int with a length of 4, Does this mean it will only auto increment to 9999? I need the field to increment forever. Also what datatypes should be used for phone number with area code and zip code. Thanks
Hi i'm using the FileSystemObject to loop through a file and read out all the data within it (it's an html page, so loads of tags and free text) and then put it in a field in a table. I am using the nText datatype but i don't think i use Full-Text Searching with this, is there any other i can use that produces the same result?
I'm 17 years old and I'm from Belgium, and I have to make a school project. I have to make a program in Visual Basic and my database is SQL Server 2000. I've already made my tables but I dont know excactly what all the datatypes in SQL Server are. They are a little bit different then Access datatypes and I dont know what to use, ntext, text, nvar, ... I can't find a list of the meaning of the datatypes on the net, maybe I'm searching with the wrong keywords(+"sql server" +datatypes in yahoo), but could please someone give me a link of a site where there is that kind of information?
I'm new to SQLServer and I'm creating some tables. In Oracle I always used varchar as this was the default and also recommended. The default in SQLServer seems to be char. Am I right in thinking that varchar is still best to use, as the field can then be of variable length rather than fixed?
I have a SQL 2005 database that's created by a survey data collectionsystem. Users of this system are fairly non-technical and have littleto no conscious control over the datatypes. As a result, the data ismostly stored quite inefficiently as varchars. For example, there isdata that could be stored in a column of bits and it's stored as avarchar value of 0 or 1. (Yuck, I know.)I am building a reporting system using this raw data and have a newtable structure designed that is much more efficient (and better forreporting). Does anyone have any suggestions for getting this datainto my new structure? Specifically, how would you recommend checkingthat varchar field and determining it could be stored as a bit?
i want to have a table for members of a club/society and i wnt to be able to maintain the structure of their member id numbers, eg com212 for a committee member, or mem019 for ordinary members.
is there a way to automate this using some sort of auto-incriment function and use this as the primary key for the table?
I have created a coldfusion webpage form that writes to a sql database. For comment boxes they can enter as much text as they need. Do I want to choose a varchar for my datatype and put a riduculously high number for characters or should I should use a text datatype and if I use text are there specific options I should set up?
I am currently creating a database tables in SQL Server 2005. I am looking for a link to a website that might explain to me the correct configuration for tabels in a sql server database such as the recommended datatypes for specific fields, eg datatype for short characters, or numbers etc.
Can some1 tell me the data types used in SQL Server for what like, i use Access so like i feel the data types are more intuitive, but for SQL Server in ASP, nchar, nvarchar, there are so many and i dunno what is what. any sites for beginners? cos i searched google and the terms they use to describe data types are like "variable length" "Unicode" i don't exactly know them. something like A-B, integers etc will be good.
Two datatype problems: (1) Datetime conversion (2) Foreign Currency format.
The first problem is that I am trying to convert source data from varchar to datetime. The source data is in CSV format and is displayed as follows - '1111999052349' to represent 1-Nov-1999 05:23:49. Have converted to a numeric value and then tried to convert to datetime but this just returns the following message 'Server: Msg 8115, Level 16, State 2, Line 3 Arithmetic overflow error converting expression to data type datetime.' However you can convert to a timestamp but the value returned is not meaningful (e.g. 0x0E0000013DFA4EE8).
Problem 2 concerns the French currency. The source data is in CSV format and is displayed as follows - '00000001,9700' to represent Fr1.97. Need to convert this to a numeric field or alternatively get SQL 7 to recognise it as a money field.
Hi all. Im tryin to export (DTS) my some SQL server tables, many of which contain 'bit' datatypes. However, when DTS/SQL Serv. moves these bit datatypes out, it changes bit values to True/False values - which makes sense - however these are all going to plugin to web frontends where the SQL specifies condtions like: "if column1 = 0 then" etc.. Is there anyway to get SQL server to export bit datatypes as just numeric values of 0/1?
New to developing. We are working on a project using asp.net and c# where data is pulled out of an sql database via multiple tables and displayed via a details view. The user can then update the information and submit it back to the database.
The problem we are encountering is:
Within the database there are multiple tables joined by a FK for example there is a Service Group ID column with the Records main table linked to the Service Group table which holds an ID column and a Service Group column so ID1 = Service Group A. Any records within the main table which has an ID of 1, is shows as Service Group A in the details view.
What we wold like to do is when a user updates the Service Group via a drop down, the ID goes into the main table rather than the Service Group name so if they select Service Group B, an ID of 2 goes into the Records main table. We aren't sure how to go about doing the conversion of Service Group B to an ID 2? We have been looking at converting datatypes but don't think it is right for what we are trying to do,
Any suggestions would be gratefully recieved Apologies if this is in the wrong forum.
Hi all, i'm looking for an article on how to use DataTypes of SQLServer in T-Sql script writing !!!? As you know the Books-Online has limited example and i have trouble undertanding for example how to use Binary,varbinary Decimal,Float,real...in T-sql script
Could anyone showm me an instructive article on the net? Thanks in advance.
Does anyone have a good way to detect LOB datatypes for a table or index? I am doing index rebuilds with the ONLINE option and need to work around indexes with LOB datatypes in them...
how do you convert from a date to an int ? as well as converting from Varchar to and Int
in SQL server 2000 ?
I am retrieving the GetDate() which i store in column as Varchar, i then want to use it within my select statement to calulate data which i want to return i.e
DECLARE @DateValue AS VARCHAR(20)
SELECT @DateValue = ApplicationSettingValue FROM ApplicationSettings WHERE ApplicationSettingKey = 'ProcessDate'
SELECT ccy_code, NULL, xrate_date, sterling_xrate FROM SylvanTrans.dbo.SIADHP_XRate_Hist WHERE xrate_date >= (CONVERT(int, @DateValue) - 35 ) ORDER BY xrate_date
now my as you can see in my WHERE CLAUSE i want to calculate what is returned using the GETDATE() stored in my @DateValue Variable, but the conversion throws a syntax error:
Server: Msg 245, Level 16, State 1, Line 3 Syntax error converting the varchar value 'GetDate()' to a column of data type int.
what am idoing wrong? i know it is something simple, unless there is no conversion from varchar to int?, i also tried setting the datatype for my variable as datetime but i got the same sort of error with DATETIME replacing the VARCHAR in the error!!!
I want to combine three fields together as a description in a select statement. When I try using the & or + I'm told that the datatypes are incompatable. How can I join them? (Item_Description is Nvarchar, Item_Cost is Money, Is_Active is bit) Select Item_Description &' '& Item_Cost &' '& Is_Active As Description FROM tblItemList I tried Casting this but same incompatable message. Select Item_Description &' '& CAST(Item_Cost AS NVARCHAR) &' '& CAST(Is_Active AS NVARCHAR) As Description FROM tblItemList What I'm hoping to end up with is: Brake Pedal, $36.00, True
I have a quick question about SQL Server 2000 supported datatypes!!
I have been trying to search on the "dreaded" Microsoft KB site to try and find information and details about the different supported data types on SQL Serv 2000, and to no avail!!!
Could anyone help me out and give me a rundown on the different data types that are supported on SQL Serv 2000, and maybe a quick explanation for each datatype, so I can make a better database?!??! I would really much appreciate any advice that anyone could offer...
I would like to concatenate text datatypes. Is this possible?? I'm using field1 + " " + field2 I cannot convert to varchar since the size is larger than 8000.
Is it possible to change the owner of a User Defined Dataype? I've recently had to change the owner of one of my databases and the tables from a specific user to 'dbo'. The UDD's are the only objects I am unable to change the ownership of.
Any suggestions anyone? Thanks for any advice! Toni Eibner
Can anyone explain to me how a column defined with a "bit null" datatype is physically stored in MSSQL? Is it stored like a "tinyint null" physically? In other words, how many bytes on the row on the page does a "bit null" datatype consume (assuming a non-null value 0, or 1 is the current value).
Is there any good documentation about the physical storage layout for a data page?
I have been inserting data From Access 2000 using the DTS import wizard. By default, I get the following datatypes
Access 2000 SQL Server 2000 datetime converts to smalldatetime text converts to nvarchar memo converts to nText Is there a way to change the defaults? I would prefer to have varchar data in SQL server instead of nvarchar, and datetime instead of smalldatetime
Hi everybody!I'm working on a migration from Informix to SQL Server... I'm doing itthrough DTS and ODBC.What I'm doing is using the DTS, my data origin is tha Informixdatabase and mi target is the SQL Server database that I just created,this is via IBM Informix ODBC and I copy the tables... but the resultsare something like this:--INFORMIX--create table r_cap_dbt_uni(num_db smallint ,cve_uni char (8) ,fecha_mens datetime YEAR to MONTH ,valor float ,primary key ( num_db, cve_uni, fecha_mens ) ,foreign key ( num_db )references cat_db_tec ( num_db ));-- SQL SERVER --create table r_cap_dbt_uni(num_db smallint ,cve_uni char (8) ,***********************************************fecha_mens smalldatetime ,valor float ,***********************************************primary key ( num_db, cve_uni, fecha_mens ) ,foreign key ( num_db )references cat_db_tec ( num_db ));as you can see the float and datetime year to month data types changelike the example before.... I changed the "float" datatype manually to"decimal" datatype... but I don't know what to do with the "datetimeyear to month" Informix datatype, I mean, I dn't know why the DTSchanges this datatype to "smalldatetime"... how can I map this? what'sthe process?Thank you very much!Rodolfo
Hi! I'm investigating if it is an good idea to use some form of "bitmap index", e.g. I have an normal table with 100-200 columns, but sometimes I just want to know: "Is there an value in the cell" (...where col20 is not null)
So I will measure if I get some benifit, so If you tried this, let me hear, but otherwise see only the rest:
If I remember correctly SQL Server will group bit-columns in some way minimizing storage needs, but my questions: how much storage is needed for an bit column: 1 or 2 bit (the second bit, because we can have three states: 0,1 and null)
Will 8 bit-columns need 1 byte or 2 byte?
I tried some tests with a number table (1mio rows), but It didn't give me the answer.
So I have Excel Source in my dataflow - so I do a simple transform and try to save it to db and guess what - a freaking error message: Columns blahblah and BlahBlah cannot convert between unicode and non-unicode datatype. I cant figure this MS stuff out - why the heck is this data from excel unicode to begin with & I just dont feel it is right to always use the derived columns to convert datatypes when dealing with Excel data. Am I missing something again or is it another one of those MS gotchas?
-- Note - all profanity in this post is replaced with words like freaking, stuff, gotcha and so on by the author
I€™ve made a SSIS package which take a sql statement and carry on to the .xls file but when I launch that package appears these errors:
Error at Data Flow Task [Excel Destination [31]]: Column "descripcion" cannot convert between unicode and non-unicode string data types. (Microsoft.DataTransformationServices.VsIntegration)
Firstly, i need to work out why I cannot change my datatypes(please see query)
Code Snippet
SELECT * FROM (
SELECT top 10
ref,
RecordDate,
TransactionID,
StatusChangedTimeStamp,
TransactionStatus,
PartyTransactionStatus,
BadDeliveryReason,
TradingDaysRef
FROM (
SELECT 1 seq,
'ref' ref,
'RecordDate' RecordDate,
'TransactionID' TransactionID,
'TransactionStatus' TransactionStatus,
'StatusChangedTimeStamp' StatusChangedTimeStamp,
'PartyTransactionStatus' PartyTransactionStatus,
'BadDeliveryReason' BadDeliveryReason,
'TradingDaysRef' TradingDaysRef
UNION ALL
SELECT 2 seq,
cast(ref as bigint),
RecordDate,
TransactionID,
StatusChangedTimeStamp,
TransactionStatus,
PartyTransactionStatus,
BadDeliveryReason,
TradingDaysRef
FROM dbo.ParticipantTradeStatusChange
) x
order by seq, RecordDate
) y
The error returned is:
Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to bigint.
The reason for me doing this, is exporting both column names & data to a xls file.
Secondly, once i get the query to complete...I kindly ask, how would i make this query a little swifter in which, i mean, select the top 100 from the table then SELECT the columns...when i do
