Hi:
If I will export my SQL express based database to SQL 2000 database - would I will be facing any compatibiltiy issues with my application. Do I just need to change the connection string in my config file and everything will work?
Danka
No, it won't just "work". It is possible for it to work though. You will need to ensure that your SQL Compatibility level on your express database is set to 80. Even then, I am not sure about express. It may not be possible at all.
I need to periodically duplicate an entire database and rename it. Most
tables are empty but some are to be prepopulated. I was thinking of having
a "template" database called perhaps "EmptyDatabase", and then copy that
into a freshly created database with a new name.
Has anyone coded anything like this?
Thanks.
GSOn Tue, 21 Nov 2006 10:39:21 -0800, George Shubin wrote:
Quote:
Originally Posted by
>Using SQL Server 2005 Express.
>
>I need to periodically duplicate an entire database and rename it. Most
>tables are empty but some are to be prepopulated. I was thinking of having
>a "template" database called perhaps "EmptyDatabase", and then copy that
>into a freshly created database with a new name.
>
>Has anyone coded anything like this?
One thing you can do is add these standard tables to the "model"
database. Each time you create a new database, it is created as a copy
of the "model" database, so each new database will have those tables.
If you don't want these tables in ALL new databases, then I'd create one
database with the required tables and make a full backup. You can then
create copies of that database by using RESTORE DATABASE with the WITH
MOVE option.
--
Hugo Kornelis, SQL Server MVP|||Thanks, Hugo.
Putting the tables in there just might be the ticket. I didn't know that
was one of the purposes of the Model database.
Thanks for the suggestions.
GS
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALIDwrote in message
news:1eacm2900qqojkaim4npoftlibmasratfm@.4ax.com...
Quote:
Originally Posted by
On Tue, 21 Nov 2006 10:39:21 -0800, George Shubin wrote:
>
Quote:
Originally Posted by
>>Using SQL Server 2005 Express.
>>
>>I need to periodically duplicate an entire database and rename it. Most
>>tables are empty but some are to be prepopulated. I was thinking of
>>having
>>a "template" database called perhaps "EmptyDatabase", and then copy that
>>into a freshly created database with a new name.
>>
>>Has anyone coded anything like this?
Quote:
Originally Posted by
>Thanks, Hugo.
>
>Putting the tables in there just might be the ticket. I didn't know that
>was one of the purposes of the Model database.
>
>Thanks for the suggestions.
The main purpose of the model DB is to give you an easy way to insure
that all new datbases are created with the same options. But there are
also many DBAs that stick common "utility" tables in there, such as a
numbers table or a calendar table. Your use is less common, but still
correct use of the model DB.
--
Hugo Kornelis, SQL Server MVP
Our product has a MDF file included with the setup, this needs to be attached at the current SQL Server (Express or not).
Where can we copy the MDF file during setup so it can be attached successfully ? 
Please help !
Thanks
You can always use CREATE DATABASE mydb ON ( FILENAME = 'mydb.mdf')
to attach a database after installing sql server.
refer to http://msdn2.microsoft.com/en-us/library/aa258257(SQL.80).aspx for more details.
or you can do attach database via management studio
Look into this link: http://msdn2.microsoft.com/en-us/library/ms190209.aspx
|||Embedded database feature is only available in SQL Server Express. In express, in connection string you can mention UserInstance and attach mdf file. But in all other versions, you will have to attach the database using some sqlscript explicitly. And also connection string will differ in both method
Refer this link for userinstance :
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx
Madhu
We want to use SqlServer Express as a data recorder for a piece of equipment. The purpose is to store all possible data values the equipment generates for a length of time so that if a problem occurs, we can search through the data to see what happened. The data is floating point numbers, like temperatures, etc.
For example, there are 200 sensors on the equipment. Every second, we want to store the 200 sensor values. The database would be one big table with 200 columns for the sensors and each second we write a row of data. Every day, the equipment would delete data older than 30 days, so that the database doesn't grow past a certain size.
Questions:
1. Any obvious reason we can't do this?
2. 30 days * 200 values * 4 bytes/value creates a 2 GB database. SqlServer Express should be able to handle that, right?
3. The equipment is running at a customer site. If the customer has a problem, we would like to be able to say to them something like, "Retrieve 3 hours of data starting last Monday at noon for Sensors A, B, and C and email it to us." We plan to give them an application that will let them put in a time range and select which sensors; it will search the database, collect the resulting data and put it in a file to send to us. Any recommendations on what format the file be in? Text? XML? Is there an obvious format that one uses to store a chunk of data from a database in?
The number and types of sensors will be different on each piece of equipment, so we don't have a predefined table or report format, we have to create it on the fly.
Thanks in advance for your thoughts.
1. I've done this many times.
2. SQL Express is limited to a 4GB database. With indexes, your data size should fit -but may be close. You will want to index the datetime column -make it the primary key.
3. Transfer files 'should' be easy to use by the recipient. xml is good, csv is good; both are easy to create and transfer. Some folks think that xml is the panacea.
Will you be using Kepware/Linkmaster?
|||1. Good - you give me hope!
2. Why will I want to index the datetime column? Does it make it faster to search?
3. Wouldn't XML add a lot of overhead to the size of the file?
I never heard of Kepware/Linkmaster, but I'm going to look them up right now.
|||You indicated that you would be searching for data from a datetime range. Searching a 2GB table will be quite 'slow' without the indexing. However, you will need to examine the trade-offs, less insertion overhead in index maintenance vs. slower query responses. If the queries are a 'rare' occurrance, then you may choose to forgo the indexing and live with slow query responses.
xml does add to file size, but the resulting files can be easily opened in Excel. In your situation, where you are the only recipient of the transfer file, csv may be a good solution. (Even the Fixed field table output may work for you too.)
|||Time to create a table and play around. Thank you for your advice.Hi,
I recently contacted my hosting company's customer support about my databases not working - saying that I use sql express (which they support).
The guy recommended:
"I would suggest you to upgrade the db's to use mssql 2005."
"Thisis because, sql express is built for development environment. When youare in development environment, you are accessing everything withadministrator permission. However, in live hosting environment (whenthere are differnet kind of permission restrictions), sql express oftenfailed on attaching database."
Does anyone have any opinion on that? Would it be best to change db's to use mssql 2005? How complicated/time consuming will it be to upgrade?
Thanks!
Jon
Hi jbear123
Here is a link from microsoft showing you the difference between all of the SQL Server Products.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
The only major difference express has over the other versions is that it is limited to 4 GB is size and will only use one process if you are running it on a multiprocessor machine. Other than that they are the same, and permissions are set up the same.
This is my understanding.
Hope this helps
Regards
ScottyB
solved.
It was to do with security
Hi,
I am trying to use DataAdapter.Update to save a file stream into SQl Express.
I have a dialog box that lets user select the file:
openFileDialog1.ShowDialog();
I want to put
openFileDialog1.OpenFile();
Into
this.documentTableAdapter.Update(this.docControllerAlphaDBDataSet.Document.DocumentColumn);
I am thinking that it might just be some syntax issue, but I looked online, and didn't find much answers.
Thanks,
Ke
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=622943&SiteID=1
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Hi,
Can someone please tell me what are all the data types in MS SQL Server 2005 Express used for ?
I have only used Access before and the data types in Access are -
DATA TYPE INFORMATION STORED
Autonumber - A number that is assigned automatically and never changes thereafter.
Currency - Amount in the currency format chosen.
Date/Time - Date and Time. The Format property chosen on the general tab to date alone or time alone or both in different formats.
Hyperlink - Hyperlink addresses.
Lookup - Values that come from another table, a query, or a list of values that are supplied. Select the Lookup Wizard data type to set the lookup field automatically.
Memo - Large bodies of text - upto 64,000 characters in length.
Number - True numbers such as quantities.
OLE Object - Any OLE object such as a picture, sound, or word processing document.
Text - Any written text upto 255 characters in length, numbers, hyphens and nonnumeric charaters.
Yes/No - Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off.
I am familiar with the data types given above, and I couldn't find any similar data types in SQL Server. I do not have any programming knowledge and I use the GUI tools of SQL Server Management Studio Express and Visual Basic 2005 Express. All the data types I found there (listed below) seemed alien to me and I couldn't make head or tail out of it. Can someone please be kind enough to list out all the types of data stored under the information stored column, after the corresponding data type from SQL server, that I have mentioned below ? I need to know what exactly is stored in each data type. I have already checked the SQL Server 2005 books online for the data types but could not find anything of substance there.
DATA TYPE INFORMATION STORED
bigint
binary (50)
bit
char (10)
datetime
decimal (18,0)
float
image
int
money
nchar (10)
ntext
numeric (18,0)
nvarchar (50)
nvarchar (max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
timestamp
tinyint
uniqueidentifier
varbinary (50)
varbinary (max)
varchar (50)
varchar (max)
xml
Also can someone please tell me what are the data types in SQL Server that correspond to the all the data types from Access ?
I especially need to know the data type for Yes/No (boolean); OLE Object; memo; and Lookup, in SQL Server.
I would like to know if there is any data type or format for a data type for storing 'telephone numbers'. This is of special interst to me, as I would like to store data in particular fields in my 'contacts' database as telephone nos, and create a button on my forms that allows users to dial the selected tel no in the record by cliking the 'dial' button, which would dial the no using the windows dialer provided by Win XP, using a normal V9.0 voice/data/fax dialup modem. I have tired to insert a button in Access before, which opens an application, in this case, the windows dialer, and then the user can switch windows, get the required phone no from the record, switch back to the dialer, and enter the no and dial. But, instead of this tedious process, wouldn't it be easier if the tel no is stored as a separate data type instead of a normal number data type and all the user has to do is select the tel no in the record on the form and (maybe) right click it and select the option to dial on the menu that appears.
Or, even if there is no right click menu, the user can select the tel no record, and then click on the dial button on the form and it would dial the no. I think the Windows Address Book has a similar feature. If there is a way to it, can someone please tell me about it ?
I request you to please give me all the directions using the GUI tools of SQL Server Management Studio Express and Visual Basic Express, and not anything from T-SQL or any other programming language as I do not know anything about programming, and it wouldn't help me at all.
You're speaking to a rookie here who has just used the GUI tools of MS Access before. But I can copy paste a bit of code here and there, and if I understand it, I might even try modifying it a bit to meet my requirements.
Greetings.
If you are to use SQL Server (any version) you MUST understand SQL. Changing from Access to SQL Server Express is not a good idea if you want to stay away from programming, either.
There is nothing in your scenario I can see that makes me think you need to use SQL Server (Express), Access is a more logical choice for you. That is, unless you want to learn programming.
Hope this helps
|||I think in this case I might have to start learning some programming along the way. BTW, what programming languages, apart from T-SQL, were you refering to, Gorm ? Because, I know that while T-SQL is mostly English-like syntax, and would be relatively easy for me to learn, I am very, very bad at maths and I guess that doesn't bode well for learning any programming, does it ?
And, the reason I chose to switch from Access to SQL Server, was because I had begun to face many limitations in Access, especially in finance related databases. ( I will come back for more help in designing the structure of financial databases later ).
Meanwhile, while I'm learning, could someone please help me out with the datatypes ?
|||? Hi Rishi, Here's a short description of SQL Server's datatypes. More information can easily be found in Books Online. Numeric datatypes For numeric information, you have three subcategories: Whole numbers: bit (0 or 1), tinyint (0 - 255), smallint (-32,768 - 32,767), int (approx -2 billion - 2 billion), bigint (approx -9 quintillion - 9 quintillion). A larger range comes with a larger storage size - which might induce a performance penalty. Also, the bit datatype has some funny behaviour on conversions, so you might wish to avoid it. Fixed point: numeric(n,m) or decimal(n,m). These are synonymous. The first number (n) gives the total number of digits; the second number (m) is the number of digits after the decimal point. So numeric(5,2) would be used to store values from -999.99 up to 999.99. Floating point: float or real (again, synonyms - sort of; see Books Online for the details). Of limited use, except in some scientific applications. Never use it for monetary amounts, because rounding in the base-2 notation that is used internally can cause strange behaviour. Date/time datatypes Datetime and smalldatetime. Datetime represents dates from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second. Smalldatetime represents dates from January 1, 1900 through June 6, 2079, with accuracy to the minute, but uses less storage size. There are no adta types for only date, only time, or for timespans. Character/text datatypes Char(n) and nchar(n) for fixed-length character strings of length n (shorter strings will be padded with spaces); varchar(n) and nvarchar(n) for variable-length character strings of maximum length n (uses n [or n*2 for nvarchar] bytes of storage, plus 2 extra bytes to store the current length); varchar(max) and nvarchar(max) are special versions of varchar and nvarchar for very long character strings (up to 2 billion bytes). The difference between char/varchar and nchar/nvarchar is that nchar/nvarchar accept Unicode characters, but use 2 bytes to store each character. There are also the text and ntext datatypes, but the are only included for compatibility with earlier versions, and they will disappear in a future version. Binary strings Binary(n), varbinary(n), and varbinary(max) are very much like char and varchar, except that they are used to store binary data instead of string data. And image, like text and ntext, exists for compatibility only. Other That leaves us with some special datatypes: cursor, table, sql_variant, uniqueidentifier, and xml. The xml datatype is new and will probably be used more and more over the coming months as it's capabilities are explored by the users; the other four have all proven to be useful is some situations - but none of them are relevant if you are new to SQL Server. For much more details on all datatypes, I suggest you consult Books Online. -- Hugo Kornelis, SQL Server MVP <Rishi Khetan@.discussions.microsoft.com> schreef in bericht news:8cc5461b-5253-4e95-b9fe-77cdca82a5e1@.discussions.microsoft.com... Hi, Can someone please tell me what are all the data types in MS SQL Server 2005 Express used for ?I have only used Access before and the data types in Access are - DATA TYPE INFORMATION STOREDAutonumber - A number that is assigned automatically and never changes thereafter.Currency - Amount in the currency format chosen.Date/Time - Date and Time. The Format property chosen on the general tab to date alone or time alone or both in different formats.Hyperlink - Hyperlink addresses.Lookup - Values that come from another table, a query, or a list of values that are supplied. Select the Lookup Wizard data type to set the lookup field automatically.Memo - Large bodies of text - upto 64,000 characters in length.Number - True numbers such as quantities.OLE Object - Any OLE object such as a picture, sound, or word processing document.Text - Any written text upto 255 characters in length, numbers, hyphens and nonnumeric charaters.Yes/No - Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. I am familiar with the data types given above, and I couldn't find any similar data types in SQL Server. I do not have any programming knowledge and I use the GUI tools of SQL Server Management Studio Express and Visual Basic 2005 Express. All the data types I found there (listed below) seemed alien to me and I couldn't make head or tail out of it. Can someone please be kind enough to list out all the types of data stored under the information stored column, after the corresponding data type from SQL server, that I have mentioned below ? I need to know what exactly is stored in each data type. I have already checked the SQL Server 2005 books online for the data types but could not find anything of substance there. DATA TYPE INFORMATION STOREDbigintbinary (50)bitchar (10)datetimedecimal (18,0)floatimageintmoneynchar (10)ntextnumeric (18,0)nvarchar (50)nvarchar (max)realsmalldatetimesmallintsmallmoneysql_varianttexttimestamptinyintuniqueidentifiervarbinary (50)varbinary (max)varchar (50) varchar (max)xml Also can someone please tell me what are the data types in SQL Server that correspond to the all the data types from Access ?I especially need to know the data type for Yes/No (boolean); OLE Object; memo; and Lookup, in SQL Server. I would like to know if there is any data type or format for a data type for storing 'telephone numbers'. This is of special interst to me, as I would like to store data in particular fields in my 'contacts' database as telephone nos, and create a button on my forms that allows users to dial the selected tel no in the record by cliking the 'dial' button, which would dial the no using the windows dialer provided by Win XP, using a normal V9.0 voice/data/fax dialup modem. I have tired to insert a button in Access before, which opens an application, in this case, the windows dialer, and then the user can switch windows, get the required phone no from the record, switch back to the dialer, and enter the no and dial. But, instead of this tedious process, wouldn't it be easier if the tel no is stored as a separate data type instead of a normal number data type and all the user has to do is select the tel no in the record on the form and (maybe) right click it and select the option to dial on the menu that appears.Or, even if there is no right click menu, the user can select the tel no record, and then click on the dial button on the form and it would dial the no. I think the Windows Address Book has a similar feature. If there is a way to it, can someone please tell me about it ? I request you to please give me all the directions using the GUI tools of SQL Server Management Studio Express and Visual Basic Express, and not anything from T-SQL or any other programming language as I do not know anything about programming, and it wouldn't help me at all.You're speaking to a rookie here who has just used the GUI tools of MS Access before. But I can copy paste a bit of code here and there, and if I understand it, I might even try modifying it a bit to meet my requirements.|||Thanks a lot Hugo, you've been a great help.
But I've still got a few questions -
How do I store images in my database, and what datatype should I select for it, since you mention that the image datatype is only there for compatibility reasons and will dissappear in the near future ?
What datatype should I select for storing Yes/No or the checkbox type of value. ( Like in Access, you could select the boolean data type and then you got a choice as to what it displayed in the table view, either a checkbox, or a Yes/No value.)
What datatype should I select for storing a Lookup value from another table ?
Are the money and small money datatypes used for storing currency values, as per the currency type set in Windows Regional Settings ?
What are the timestamp and uniqueidentifier datatypes used for ?
I couldn't find what I was looking for in Books Online, so I would be really grateful if you could also provide me with the relevant links for the information I am looking for in Books Online.
Rishi.
|||? Hi Rishi, That's a lot of questions!! >>How do I store images in my database, and what datatype should I select for it, since you mention that the image datatype is only there for compatibility reasons and will dissappear in the near future ? Use varbinary(max), the replacement datatype for image. You can do many things with varbinary(max) that you could never do with image; that's the reason why the image datatype will be phased out. >>What datatype should I select for storing Yes/No or the checkbox type of value. ( Like in Access, you could select the boolean data type and then you got a choice as to what it displayed in the table view, either a checkbox, or a Yes/No value.) I'd recommend a CHAR(1) with a CHECK constraint to limit the contents to 'Y' and 'N' (or 'T' and 'F'), plus a NOT NULL constraint. There are also a lot of people who recommend using a numeric datatype and use e.g. 1 for true and 0 for false, but I find that the character values are much more obvious when reviewing table data. I would recommend against using the BIT datatype. It is merely confusing, since it is somehow similar to a Boolean (the official name for Yes/No) datatype, yet doesn;t behave like a true Boolean at all. >>What datatype should I select for storing a Lookup value from another table ? In a real relational design, there are no "lookup" tables and "other" tables - just tables. During information analysis, you will determine if a list of valid values for a column should be modeled as a CHECK constraint, or as a table. I'll use some examples to explain. Case #1: You have to store a currency code. You will, of course, use the three-letter ISO standard codes. The company will only do business in US Dollas, European Euro's, or Japanese Yen; no change to this policy is expected. No data needs to be stored that is functionally dependant on the currency code. In this case, you'll use a hard-coded dropdown (or other input thingie) in the front-end and a CHECK constraint in the database: CREATE TABLE SomeTable (....., CurrencyCode char(3) NOT NULL CHECK (CurrencyCode IN ('USD', 'EUR', 'JPY')), ...... ) Case #2: You have to store a currency code. You will, of course, use the three-letter ISO standard codes. Because the list of accepted currencies is subject to frequent change, or because some other information is functionally dependant on the currency code, you design a table of Currencies. The ISO currency code will of course be the primary key, and hence also the datatype to use for referencing columns. The dropdown (or other input thingie) in the front-end will be built at run-time by reading the Currencies table; integrity is guarded in the DB by a FOREIGN KEY constraint: CREATE TABLE Currencies (CurrencyCode char(3) NOT NULL PRIMARY KEY, -- Other columns ) CREATE TABLE SomeTable (....., CurrencyCode char(3) NOT NULL REFERENCES Currencies(CurrencyCode), ...... ) Case #3: Departments in the company are identified by name. These names can be up to 60 characters. For performance reasons, you decide to use a surrogate key for all references to the Departments table. You decide on an integer datatype with the IDENTITY property. The dropdown (or other input thingie) in the front-end will be built at run-time by reading the Departments table (make sure that the department name is shown; the front-end should remember the corresponding surrogate key value); integrity is guarded in the DB by a FOREIGN KEY constraint: CREATE TABLE Departments (DeptID int NOT NULL PRIMARY KEY IDENTITY, DeptName varchar(60) NOT NULL UNIQUE, -- Other columns ) CREATE TABLE OtherTable (....., DeptID int NOT NULL REFERENCES Departments(DeptID)), ...... ) * Note: output should use views or stored procedure that return only DeptName; DeptID is intended for internal use only and must never be shown to the end user!!!! >>Are the money and small money datatypes used for storing currency values, as per the currency type set in Windows Regional Settings ? Better not use money and smallmoney at all. In some ways, they behave very much the same as decimal(19,4) / decimal(10,4). The main differences are that they are formatted somewhat different when output, and that they can introduce some very nasty rouding problems in certain calculations. DECLARE @.a money, @.b money, @.c decimal(19,4), @.d decimal(19,4)SET @.a = 123.45SET @.b = 67.89SET @.c = 123.45SET @.d = 67.89 SELECT (@.a / @.b) * @.bSELECT (@.c / @.d) * @.d >>What are the timestamp and uniqueidentifier datatypes used for ? Timestamp is the most inadequately named datatype ever - the contents of a timestamp column have no relation whatsoever with current time. In SQL Server 2000, a new name (rowversion) was introduces. I believe (and hope!) that Microsoft intend to shift to using only the name rowversion and get rid of the name timestamp. If a table has a column with the rowversion (or timestamp) column, then that column will automatically be changed on every insert or update of the row. You can not set values to this column yourself. This is useful to implement optimistic locking: store the rowversion when reading data for display on the screen; when the user is finished making changes and wants to save them back to the DB, check if the rowversion is unchanged before updating the data. If it has changed, someone else has changed the data while the user was busy; frontend can take action (or ask user for action to take). The uniqueidentifier is used for storing globally unique identifiers (GUID) - a 16-byte value, usually represented in hexadecimal form, in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (e.g. 6F9619FF-8B86-D011-B42D-00C04FC964FF). They are designed for very specific scenario's where surrogate key values have to be generated at different locations. Typically only in replication scenario's. >>I couldn't find what I was looking for in Books Online, so I would be really grateful if you could also provide me with the relevant links for the information I am looking for in Books Online. Did you use Books Online on the Internet, or do you have them installed on your computer? In the first case, I recommend you to download the complete package and install them locally. I like the search capabilities of the "local install" Books Online version better than the search capabilities of the online version. -- Hugo Kornelis, SQL Server MVP|||There appears to be quite alot of knowledge on this forum, I have 1 simple question I would like answered:How do I create a data record that auto increments? Or does that require additional coding?
Thanks!
|||? Hi owned, >>How do I create a data record that auto increments? Or does that require additional coding? Pick one of the numeric datatypes, then add the IDENTITY property. CREATE TABLE MyTable (KeyColumn int NOT NULL IDENTITY, OtherCol varchar(20) NOT NULL, PRIMARY KEY (KeyColumn), UNIQUE (OtherCol) ) go INSERT INTO MyTable (OtherCol) VALUES ('First') INSERT INTO MyTable (OtherCol) VALUES ('Second') SELECT * FROM MyTable go DROP TABLE MyTable go -- Hugo Kornelis, SQL Server MVP|||
Thanks Hugo,
Although I couln't understand your examples since I don't know any programming ;-), that's allright. I got the info that I needed. You've been very helpful. And I've been refering to the Internet version of Books Online as I thought since the name was books 'Online', so the online version would be more comprehensive and would have more material. Anyway, 180 MB is a HUGE download size, so I don't think I'll be getting the offline version anytime soon.
|||Hi ! This is great forum for data type.Best bet is to use VARCHAR with appropriate length for each. There is no specific types for them in SQL. All logics to make sure data entered is in correct format should be handled by your application (email format validation, password rule validation, etc.).
Hi,
Can someone please tell me what are all the data types in MS SQL Server 2005 Express used for ?
I have only used Access before and the data types in Access are -
DATA TYPE INFORMATION STORED
Autonumber - A number that is assigned automatically and never changes thereafter.
Currency - Amount in the currency format chosen.
Date/Time - Date and Time. The Format property chosen on the general tab to date alone or time alone or both in different formats.
Hyperlink - Hyperlink addresses.
Lookup - Values that come from another table, a query, or a list of values that are supplied. Select the Lookup Wizard data type to set the lookup field automatically.
Memo - Large bodies of text - upto 64,000 characters in length.
Number - True numbers such as quantities.
OLE Object - Any OLE object such as a picture, sound, or word processing document.
Text - Any written text upto 255 characters in length, numbers, hyphens and nonnumeric charaters.
Yes/No - Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off.
I am familiar with the data types given above, and I couldn't find any similar data types in SQL Server. I do not have any programming knowledge and I use the GUI tools of SQL Server Management Studio Express and Visual Basic 2005 Express. All the data types I found there (listed below) seemed alien to me and I couldn't make head or tail out of it. Can someone please be kind enough to list out all the types of data stored under the information stored column, after the corresponding data type from SQL server, that I have mentioned below ? I need to know what exactly is stored in each data type. I have already checked the SQL Server 2005 books online for the data types but could not find anything of substance there.
DATA TYPE INFORMATION STORED
bigint
binary (50)
bit
char (10)
datetime
decimal (18,0)
float
image
int
money
nchar (10)
ntext
numeric (18,0)
nvarchar (50)
nvarchar (max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
timestamp
tinyint
uniqueidentifier
varbinary (50)
varbinary (max)
varchar (50)
varchar (max)
xml
Also can someone please tell me what are the data types in SQL Server that correspond to the all the data types from Access ?
I especially need to know the data type for Yes/No (boolean); OLE Object; memo; and Lookup, in SQL Server.
I would like to know if there is any data type or format for a data type for storing 'telephone numbers'. This is of special interst to me, as I would like to store data in particular fields in my 'contacts' database as telephone nos, and create a button on my forms that allows users to dial the selected tel no in the record by cliking the 'dial' button, which would dial the no using the windows dialer provided by Win XP, using a normal V9.0 voice/data/fax dialup modem. I have tired to insert a button in Access before, which opens an application, in this case, the windows dialer, and then the user can switch windows, get the required phone no from the record, switch back to the dialer, and enter the no and dial. But, instead of this tedious process, wouldn't it be easier if the tel no is stored as a separate data type instead of a normal number data type and all the user has to do is select the tel no in the record on the form and (maybe) right click it and select the option to dial on the menu that appears.
Or, even if there is no right click menu, the user can select the tel no record, and then click on the dial button on the form and it would dial the no. I think the Windows Address Book has a similar feature. If there is a way to it, can someone please tell me about it ?
I request you to please give me all the directions using the GUI tools of SQL Server Management Studio Express and Visual Basic Express, and not anything from T-SQL or any other programming language as I do not know anything about programming, and it wouldn't help me at all.
You're speaking to a rookie here who has just used the GUI tools of MS Access before. But I can copy paste a bit of code here and there, and if I understand it, I might even try modifying it a bit to meet my requirements.
Greetings.
If you are to use SQL Server (any version) you MUST understand SQL. Changing from Access to SQL Server Express is not a good idea if you want to stay away from programming, either.
There is nothing in your scenario I can see that makes me think you need to use SQL Server (Express), Access is a more logical choice for you. That is, unless you want to learn programming.
Hope this helps
|||I think in this case I might have to start learning some programming along the way. BTW, what programming languages, apart from T-SQL, were you refering to, Gorm ? Because, I know that while T-SQL is mostly English-like syntax, and would be relatively easy for me to learn, I am very, very bad at maths and I guess that doesn't bode well for learning any programming, does it ?
And, the reason I chose to switch from Access to SQL Server, was because I had begun to face many limitations in Access, especially in finance related databases. ( I will come back for more help in designing the structure of financial databases later ).
Meanwhile, while I'm learning, could someone please help me out with the datatypes ?
|||? Hi Rishi, Here's a short description of SQL Server's datatypes. More information can easily be found in Books Online. Numeric datatypes For numeric information, you have three subcategories: Whole numbers: bit (0 or 1), tinyint (0 - 255), smallint (-32,768 - 32,767), int (approx -2 billion - 2 billion), bigint (approx -9 quintillion - 9 quintillion). A larger range comes with a larger storage size - which might induce a performance penalty. Also, the bit datatype has some funny behaviour on conversions, so you might wish to avoid it. Fixed point: numeric(n,m) or decimal(n,m). These are synonymous. The first number (n) gives the total number of digits; the second number (m) is the number of digits after the decimal point. So numeric(5,2) would be used to store values from -999.99 up to 999.99. Floating point: float or real (again, synonyms - sort of; see Books Online for the details). Of limited use, except in some scientific applications. Never use it for monetary amounts, because rounding in the base-2 notation that is used internally can cause strange behaviour. Date/time datatypes Datetime and smalldatetime. Datetime represents dates from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second. Smalldatetime represents dates from January 1, 1900 through June 6, 2079, with accuracy to the minute, but uses less storage size. There are no adta types for only date, only time, or for timespans. Character/text datatypes Char(n) and nchar(n) for fixed-length character strings of length n (shorter strings will be padded with spaces); varchar(n) and nvarchar(n) for variable-length character strings of maximum length n (uses n [or n*2 for nvarchar] bytes of storage, plus 2 extra bytes to store the current length); varchar(max) and nvarchar(max) are special versions of varchar and nvarchar for very long character strings (up to 2 billion bytes). The difference between char/varchar and nchar/nvarchar is that nchar/nvarchar accept Unicode characters, but use 2 bytes to store each character. There are also the text and ntext datatypes, but the are only included for compatibility with earlier versions, and they will disappear in a future version. Binary strings Binary(n), varbinary(n), and varbinary(max) are very much like char and varchar, except that they are used to store binary data instead of string data. And image, like text and ntext, exists for compatibility only. Other That leaves us with some special datatypes: cursor, table, sql_variant, uniqueidentifier, and xml. The xml datatype is new and will probably be used more and more over the coming months as it's capabilities are explored by the users; the other four have all proven to be useful is some situations - but none of them are relevant if you are new to SQL Server. For much more details on all datatypes, I suggest you consult Books Online. -- Hugo Kornelis, SQL Server MVP <Rishi Khetan@.discussions.microsoft.com> schreef in bericht news:8cc5461b-5253-4e95-b9fe-77cdca82a5e1@.discussions.microsoft.com... Hi, Can someone please tell me what are all the data types in MS SQL Server 2005 Express used for ?I have only used Access before and the data types in Access are - DATA TYPE INFORMATION STOREDAutonumber - A number that is assigned automatically and never changes thereafter.Currency - Amount in the currency format chosen.Date/Time - Date and Time. The Format property chosen on the general tab to date alone or time alone or both in different formats.Hyperlink - Hyperlink addresses.Lookup - Values that come from another table, a query, or a list of values that are supplied. Select the Lookup Wizard data type to set the lookup field automatically.Memo - Large bodies of text - upto 64,000 characters in length.Number - True numbers such as quantities.OLE Object - Any OLE object such as a picture, sound, or word processing document.Text - Any written text upto 255 characters in length, numbers, hyphens and nonnumeric charaters.Yes/No - Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. I am familiar with the data types given above, and I couldn't find any similar data types in SQL Server. I do not have any programming knowledge and I use the GUI tools of SQL Server Management Studio Express and Visual Basic 2005 Express. All the data types I found there (listed below) seemed alien to me and I couldn't make head or tail out of it. Can someone please be kind enough to list out all the types of data stored under the information stored column, after the corresponding data type from SQL server, that I have mentioned below ? I need to know what exactly is stored in each data type. I have already checked the SQL Server 2005 books online for the data types but could not find anything of substance there. DATA TYPE INFORMATION STOREDbigintbinary (50)bitchar (10)datetimedecimal (18,0)floatimageintmoneynchar (10)ntextnumeric (18,0)nvarchar (50)nvarchar (max)realsmalldatetimesmallintsmallmoneysql_varianttexttimestamptinyintuniqueidentifiervarbinary (50)varbinary (max)varchar (50) varchar (max)xml Also can someone please tell me what are the data types in SQL Server that correspond to the all the data types from Access ?I especially need to know the data type for Yes/No (boolean); OLE Object; memo; and Lookup, in SQL Server. I would like to know if there is any data type or format for a data type for storing 'telephone numbers'. This is of special interst to me, as I would like to store data in particular fields in my 'contacts' database as telephone nos, and create a button on my forms that allows users to dial the selected tel no in the record by cliking the 'dial' button, which would dial the no using the windows dialer provided by Win XP, using a normal V9.0 voice/data/fax dialup modem. I have tired to insert a button in Access before, which opens an application, in this case, the windows dialer, and then the user can switch windows, get the required phone no from the record, switch back to the dialer, and enter the no and dial. But, instead of this tedious process, wouldn't it be easier if the tel no is stored as a separate data type instead of a normal number data type and all the user has to do is select the tel no in the record on the form and (maybe) right click it and select the option to dial on the menu that appears.Or, even if there is no right click menu, the user can select the tel no record, and then click on the dial button on the form and it would dial the no. I think the Windows Address Book has a similar feature. If there is a way to it, can someone please tell me about it ? I request you to please give me all the directions using the GUI tools of SQL Server Management Studio Express and Visual Basic Express, and not anything from T-SQL or any other programming language as I do not know anything about programming, and it wouldn't help me at all.You're speaking to a rookie here who has just used the GUI tools of MS Access before. But I can copy paste a bit of code here and there, and if I understand it, I might even try modifying it a bit to meet my requirements.|||Thanks a lot Hugo, you've been a great help.
But I've still got a few questions -
How do I store images in my database, and what datatype should I select for it, since you mention that the image datatype is only there for compatibility reasons and will dissappear in the near future ?
What datatype should I select for storing Yes/No or the checkbox type of value. ( Like in Access, you could select the boolean data type and then you got a choice as to what it displayed in the table view, either a checkbox, or a Yes/No value.)
What datatype should I select for storing a Lookup value from another table ?
Are the money and small money datatypes used for storing currency values, as per the currency type set in Windows Regional Settings ?
What are the timestamp and uniqueidentifier datatypes used for ?
I couldn't find what I was looking for in Books Online, so I would be really grateful if you could also provide me with the relevant links for the information I am looking for in Books Online.
Rishi.
|||? Hi Rishi, That's a lot of questions!! >>How do I store images in my database, and what datatype should I select for it, since you mention that the image datatype is only there for compatibility reasons and will dissappear in the near future ? Use varbinary(max), the replacement datatype for image. You can do many things with varbinary(max) that you could never do with image; that's the reason why the image datatype will be phased out. >>What datatype should I select for storing Yes/No or the checkbox type of value. ( Like in Access, you could select the boolean data type and then you got a choice as to what it displayed in the table view, either a checkbox, or a Yes/No value.) I'd recommend a CHAR(1) with a CHECK constraint to limit the contents to 'Y' and 'N' (or 'T' and 'F'), plus a NOT NULL constraint. There are also a lot of people who recommend using a numeric datatype and use e.g. 1 for true and 0 for false, but I find that the character values are much more obvious when reviewing table data. I would recommend against using the BIT datatype. It is merely confusing, since it is somehow similar to a Boolean (the official name for Yes/No) datatype, yet doesn;t behave like a true Boolean at all. >>What datatype should I select for storing a Lookup value from another table ? In a real relational design, there are no "lookup" tables and "other" tables - just tables. During information analysis, you will determine if a list of valid values for a column should be modeled as a CHECK constraint, or as a table. I'll use some examples to explain. Case #1: You have to store a currency code. You will, of course, use the three-letter ISO standard codes. The company will only do business in US Dollas, European Euro's, or Japanese Yen; no change to this policy is expected. No data needs to be stored that is functionally dependant on the currency code. In this case, you'll use a hard-coded dropdown (or other input thingie) in the front-end and a CHECK constraint in the database: CREATE TABLE SomeTable (....., CurrencyCode char(3) NOT NULL CHECK (CurrencyCode IN ('USD', 'EUR', 'JPY')), ...... ) Case #2: You have to store a currency code. You will, of course, use the three-letter ISO standard codes. Because the list of accepted currencies is subject to frequent change, or because some other information is functionally dependant on the currency code, you design a table of Currencies. The ISO currency code will of course be the primary key, and hence also the datatype to use for referencing columns. The dropdown (or other input thingie) in the front-end will be built at run-time by reading the Currencies table; integrity is guarded in the DB by a FOREIGN KEY constraint: CREATE TABLE Currencies (CurrencyCode char(3) NOT NULL PRIMARY KEY, -- Other columns ) CREATE TABLE SomeTable (....., CurrencyCode char(3) NOT NULL REFERENCES Currencies(CurrencyCode), ...... ) Case #3: Departments in the company are identified by name. These names can be up to 60 characters. For performance reasons, you decide to use a surrogate key for all references to the Departments table. You decide on an integer datatype with the IDENTITY property. The dropdown (or other input thingie) in the front-end will be built at run-time by reading the Departments table (make sure that the department name is shown; the front-end should remember the corresponding surrogate key value); integrity is guarded in the DB by a FOREIGN KEY constraint: CREATE TABLE Departments (DeptID int NOT NULL PRIMARY KEY IDENTITY, DeptName varchar(60) NOT NULL UNIQUE, -- Other columns ) CREATE TABLE OtherTable (....., DeptID int NOT NULL REFERENCES Departments(DeptID)), ...... ) * Note: output should use views or stored procedure that return only DeptName; DeptID is intended for internal use only and must never be shown to the end user!!!! >>Are the money and small money datatypes used for storing currency values, as per the currency type set in Windows Regional Settings ? Better not use money and smallmoney at all. In some ways, they behave very much the same as decimal(19,4) / decimal(10,4). The main differences are that they are formatted somewhat different when output, and that they can introduce some very nasty rouding problems in certain calculations. DECLARE @.a money, @.b money, @.c decimal(19,4), @.d decimal(19,4)SET @.a = 123.45SET @.b = 67.89SET @.c = 123.45SET @.d = 67.89 SELECT (@.a / @.b) * @.bSELECT (@.c / @.d) * @.d >>What are the timestamp and uniqueidentifier datatypes used for ? Timestamp is the most inadequately named datatype ever - the contents of a timestamp column have no relation whatsoever with current time. In SQL Server 2000, a new name (rowversion) was introduces. I believe (and hope!) that Microsoft intend to shift to using only the name rowversion and get rid of the name timestamp. If a table has a column with the rowversion (or timestamp) column, then that column will automatically be changed on every insert or update of the row. You can not set values to this column yourself. This is useful to implement optimistic locking: store the rowversion when reading data for display on the screen; when the user is finished making changes and wants to save them back to the DB, check if the rowversion is unchanged before updating the data. If it has changed, someone else has changed the data while the user was busy; frontend can take action (or ask user for action to take). The uniqueidentifier is used for storing globally unique identifiers (GUID) - a 16-byte value, usually represented in hexadecimal form, in the format xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx (e.g. 6F9619FF-8B86-D011-B42D-00C04FC964FF). They are designed for very specific scenario's where surrogate key values have to be generated at different locations. Typically only in replication scenario's. >>I couldn't find what I was looking for in Books Online, so I would be really grateful if you could also provide me with the relevant links for the information I am looking for in Books Online. Did you use Books Online on the Internet, or do you have them installed on your computer? In the first case, I recommend you to download the complete package and install them locally. I like the search capabilities of the "local install" Books Online version better than the search capabilities of the online version. -- Hugo Kornelis, SQL Server MVP|||There appears to be quite alot of knowledge on this forum, I have 1 simple question I would like answered:How do I create a data record that auto increments? Or does that require additional coding?
Thanks!
|||? Hi owned, >>How do I create a data record that auto increments? Or does that require additional coding? Pick one of the numeric datatypes, then add the IDENTITY property. CREATE TABLE MyTable (KeyColumn int NOT NULL IDENTITY, OtherCol varchar(20) NOT NULL, PRIMARY KEY (KeyColumn), UNIQUE (OtherCol) ) go INSERT INTO MyTable (OtherCol) VALUES ('First') INSERT INTO MyTable (OtherCol) VALUES ('Second') SELECT * FROM MyTable go DROP TABLE MyTable go -- Hugo Kornelis, SQL Server MVP|||
Thanks Hugo,
Although I couln't understand your examples since I don't know any programming ;-), that's allright. I got the info that I needed. You've been very helpful. And I've been refering to the Internet version of Books Online as I thought since the name was books 'Online', so the online version would be more comprehensive and would have more material. Anyway, 180 MB is a HUGE download size, so I don't think I'll be getting the offline version anytime soon.
|||Hi ! This is great forum for data type.Best bet is to use VARCHAR with appropriate length for each. There is no specific types for them in SQL. All logics to make sure data entered is in correct format should be handled by your application (email format validation, password rule validation, etc.).
Hello, I am following this article about building an ASP.Net application which uses SQL Express 2005. The document calls for building a couple of tables. One column is identified with data type of "Byte" which are is available for selection within the Visual Studio 2005 interface. The following values show in the drop down of data type in VS2005:
Bigint
Binary(5)
Bit
Char(10)
DateTime
Decimal (18,0)
Float
Image
Int
Money
NChar(10)
NText
Numeric(18,0)
Nvarchar(50)
Nvarchar(max)
Real
SmallDateTime
SmallInt
SmallMoney
Sql_Variant
Text
Timestamp
TinyInt
UniqueIdentifier
Varbinary(50)
VarBinary(max)
varchar(10)
Varchar(max)
XML
I choose Char with a size of 1. Would this be correct, or am I missing something?
Thanks in advance for your assistance!!
SqlByte maps to tinyint.Hi,
I am a bit confused...
is the data type in sql express similar to DateTime? in other words can i convert this timestamp to DateTime or can i use this data type instead of the data type DateTime?
regards,
rnv
Even they store both 8 bytes, they are not the same, read more for this in the BOL.
timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ta-tz_6fn4.asp
If you want to use something like rowversioning, use ROWVERSION not datetime, as datetime has only the limitation to a precision of 3ms.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||thanks a lot.
|||Could you please mark the post as solved then, thanks.Jens.
Hi,
I am using SQL Server 2005 Express. I am creating a table, which will have a comments field. Whats an appropriate data type that should be used for a "comments" field? So far, I have unlimited text and characters can be entered, but that a limitation can be placed.
Thanks
Chirs
Most likely, you will be best served with varchar(max).
Varchar(max) will hold up to 2 GB of data.
Is Data Tuning Advisor available for any Express version?
--Thanks
No, DTA is not part of the Express offering.
Regards,
Mike Wachal
SQL Express team
-
Please mark your thread as Answered when you get your solution.
How can I make a DTS in MSSQL Server Management Studio Express?
I know how to do that in SQL Server Enterprise Manager.
Is this restricted in the express?
Thanks.
SSIS (aka, DTS) is only available with SQL Server 2005 Standard Edition and above. With Standard Edition, you can use Business Intelligence Studio to make SSIS packages.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
|||I'm not fully certain, but the original poster likely wasn't talking about SSIS. Most people no longer mistakenly call SSIS by the name DTS. The question is "How can I make a DTS in MSSQL Server Management Studio Express?" I took this to mean - "How do I make a DTS package for SQL 2000 using SQL 2005's Managment Studio Express?"
Microsoft has released the Feature Pack for SQL 2005. In it, there is an item called Microsoft SQL Server 2000 DTS Designer Components.
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
It is unclear whether or not this runs in the Expess studio, but I haven't gotten it to work. I believe it will likley run in the full version of Managment Studio, but I haven't tried. Anyone have any success with DTS Designer Components and the Express edition?
How can I make a DTS in MSSQL Server Management Studio Express?
I know how to do that in SQL Server Enterprise Manager.
Is this restricted in the express?
Thanks.
SSIS (aka, DTS) is only available with SQL Server 2005 Standard Edition and above. With Standard Edition, you can use Business Intelligence Studio to make SSIS packages.
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
|||I'm not fully certain, but the original poster likely wasn't talking about SSIS. Most people no longer mistakenly call SSIS by the name DTS. The question is "How can I make a DTS in MSSQL Server Management Studio Express?" I took this to mean - "How do I make a DTS package for SQL 2000 using SQL 2005's Managment Studio Express?"
Microsoft has released the Feature Pack for SQL 2005. In it, there is an item called Microsoft SQL Server 2000 DTS Designer Components.
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
It is unclear whether or not this runs in the Expess studio, but I haven't gotten it to work. I believe it will likley run in the full version of Managment Studio, but I haven't tried. Anyone have any success with DTS Designer Components and the Express edition?
