Database Comparison

I had an application go down yesterday...it was giving a Type Mismatch error and the support team couldn't figure it out.

My suspicion is that a field in the database got populated with a character the vb app didn't like when it renders the information.

I'd like to prove that theory...but the database is large (4gb) and I'm not sure how to do a database comparison to see where the records differ.

Anybody have a script to get me started OR some experience to share?

Thanks,

Alex8675Anybody have some real, useful, practical information that could help us figure out what your problem is?

Exact text of error?
DDL of table?
Datatype of column?

We are not mentalists, here. We don't play guessing games.

database backup not clearing the transaction log...

Hi
I have a 150Gb 'time series' type database running in SQL 2000. every sunday
@. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
creates a big fat transaction log.
At 0030 Monday morning the database is backed up using
BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
NOFORMAT
which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
cleared at 0800 Monday morning when the trans log is backed up (and is
continually backed up every 10 minutes throughout the working day all week
until Saturday) using the following syntax:
surely the database backup at 0030 should clear down the size of the trans
log?

> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
Sure , it does not. You need to BACKUP LOG file in order to truncate
inactive portions in the LOG
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:11993BB5-D220-4AFF-99D1-F0C7FAE045F6@.microsoft.com...
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every
> sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD
> ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
|||Methodology wrote:
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NOUNLOAD ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
transaction log, only a log backup will do that (assuming Full or
Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
of the log file will not change when truncating. Shrinking is a
separate process, and there are many reasons why you don't want to do it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Here is an idea. I did this once at one job I had. I had a similarlarly
large sized databases whose transaction logs "blew out" on a reindex
(weekly). I had a lot of ugly clustered indexes that had to be rebuilt
and took a lot of space. SInce the database was not heavily used during
that period, I did an alter database and put the db on simple mode.
Then it won't blow out the log. I did the re-index on all necessary
tables and then did a full backup. Note, this is dangerous as you break
the transaction log sequence at that time, so you must do an immediate
full backup. In my case, this was ok as it was a mainly Mon-Sat db.
KR
Tracy McKibben wrote:
> Methodology wrote:
> A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
> transaction log, only a log backup will do that (assuming Full or
> Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
> of the log file will not change when truncating. Shrinking is a
> separate process, and there are many reasons why you don't want to do it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
sql

database backup not clearing the transaction log...

Hi
I have a 150Gb 'time series' type database running in SQL 2000. every sunday
@. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
creates a big fat transaction log.
At 0030 Monday morning the database is backed up using
BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT , NO
UNLOAD ,
NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
NOFORMAT
which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is only
cleared at 0800 Monday morning when the trans log is backed up (and is
continually backed up every 10 minutes throughout the working day all week
until Saturday) using the following syntax:
surely the database backup at 0030 should clear down the size of the trans
log?
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
Sure , it does not. You need to BACKUP LOG file in order to truncate
inactive portions in the LOG
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:11993BB5-D220-4AFF-99D1-F0C7FAE045F6@.microsoft.com...
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every
> sunday
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT ,
NOUNLOAD
> ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is
> only
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>|||Methodology wrote:
> Hi
> I have a 150Gb 'time series' type database running in SQL 2000. every sund
ay
> @. 0230 it re-indexes using 'dbcc dbreindex'. this takes several hours and
> creates a big fat transaction log.
> At 0030 Monday morning the database is backed up using
> BACKUP DATABASE [timeseries] TO [Backup_timeseries] WITH INIT ,
NOUNLOAD ,
> NAME = N'timeseries backup - init BAK file', NOSKIP , STATS = 10,
> NOFORMAT
> which DOES NOT CLEAR THE SIZE OF THE TRANSACTION LOG. The trans log is onl
y
> cleared at 0800 Monday morning when the trans log is backed up (and is
> continually backed up every 10 minutes throughout the working day all week
> until Saturday) using the following syntax:
> surely the database backup at 0030 should clear down the size of the trans
> log?
>
A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
transaction log, only a log backup will do that (assuming Full or
Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
of the log file will not change when truncating. Shrinking is a
separate process, and there are many reasons why you don't want to do it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Here is an idea. I did this once at one job I had. I had a similarlarly
large sized databases whose transaction logs "blew out" on a reindex
(weekly). I had a lot of ugly clustered indexes that had to be rebuilt
and took a lot of space. SInce the database was not heavily used during
that period, I did an alter database and put the db on simple mode.
Then it won't blow out the log. I did the re-index on all necessary
tables and then did a full backup. Note, this is dangerous as you break
the transaction log sequence at that time, so you must do an immediate
full backup. In my case, this was ok as it was a mainly Mon-Sat db.
KR
Tracy McKibben wrote:
> Methodology wrote:
> A database backup DOES NOT, repeat, DOES NOT truncate (flush out) the
> transaction log, only a log backup will do that (assuming Full or
> Bulk-Logged modes). Also, truncation != SHRINKING. The physical size
> of the log file will not change when truncating. Shrinking is a
> separate process, and there are many reasons why you don't want to do it.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Data Types Question and Help

Hi Experts,
I dont want to use money data type because the money symbol pops into the
the grid but yet i am little uncomfortable with double - my calculations are
likely to go upto 50 Millions and cents rounded off to 4 decimal. Can you
please make a suggestion in this regard. Your help would be greatly
appreciated
Best Regards
Manish Sawjiani
Three Cheers to Technet for the Help!
How about a suitable DECIMAL() or NUMERIC() datatype?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in message
news:EA8D18EF-C8B7-45D7-A189-3F751B4FD491@.microsoft.com...
> Hi Experts,
> I dont want to use money data type because the money symbol pops into the
> the grid but yet i am little uncomfortable with double - my calculations are
> likely to go upto 50 Millions and cents rounded off to 4 decimal. Can you
> please make a suggestion in this regard. Your help would be greatly
> appreciated
> Best Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Data Types Question and Help

Hi Experts,
I dont want to use money data type because the money symbol pops into the
the grid but yet i am little uncomfortable with double - my calculations are
likely to go upto 50 Millions and cents rounded off to 4 decimal. Can you
please make a suggestion in this regard. Your help would be greatly
appreciated
Best Regards
Manish Sawjiani
--
Three Cheers to Technet for the Help!How about a suitable DECIMAL() or NUMERIC() datatype?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in message
news:EA8D18EF-C8B7-45D7-A189-3F751B4FD491@.microsoft.com...
> Hi Experts,
> I dont want to use money data type because the money symbol pops into the
> the grid but yet i am little uncomfortable with double - my calculations are
> likely to go upto 50 Millions and cents rounded off to 4 decimal. Can you
> please make a suggestion in this regard. Your help would be greatly
> appreciated
> Best Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Data Types Question and Help

Hi Experts,
I dont want to use money data type because the money symbol pops into the
the grid but yet i am little uncomfortable with double - my calculations are
likely to go upto 50 Millions and cents rounded off to 4 decimal. Can you
please make a suggestion in this regard. Your help would be greatly
appreciated
Best Regards
Manish Sawjiani
--
Three Cheers to technet for the Help!How about a suitable DECIMAL() or NUMERIC() datatype?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in messag
e
news:EA8D18EF-C8B7-45D7-A189-3F751B4FD491@.microsoft.com...
> Hi Experts,
> I dont want to use money data type because the money symbol pops into the
> the grid but yet i am little uncomfortable with double - my calculations a
re
> likely to go upto 50 Millions and cents rounded off to 4 decimal. Can you
> please make a suggestion in this regard. Your help would be greatly
> appreciated
> Best Regards
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!

Data types question - Varbinary and timestamps

Greetings once again SSIS friends,

I have some source tables which contain timestamp fields (that's timestamp data type not datetime). My dimension table holds the maximum timestamp value as a varbinary(8).

I want my package to have a variable that holds that value but I don't know which data type to use for this. The reason for this is because I want to use that variable to then retrieve all records from my source table that have a timestamp value greater than the value stored in the variable.

Please advise on what data type is suitable.

Thanks for your help in advance.

Greetings once again guys,

I found the following article which gives a workable solution to my problem, but I am still interested to find out the equivalent of timestamp data type in SSIS.

http://solidqualitylearning.com/blogs/erik/archive/2005/12/09/1499.aspx

|||

DT_BYTES

binary, varbinary, timestamp

binary, varbinary, timestamp

BigBinary, VarBinary

RAW

RAW

You will want to use DT_BYTES. See this article on MSDN.

http://msdn2.microsoft.com/en-us/library/ms141036.aspx

Does this answer your question?

data types help?

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...
thanksAny of the CHAR() related types, such as CHAR(), VARCHAR(), NCHAR(), NVARCHAR(), or TEXT should do nicely.

-PatP|||I have varchar now (on a web site with sql server as a database) but only save the letter and number for this example ab+ not the plus...

But I do have function, that I will probably have to customize,that is causing it only to save letters and numbers,

Thanks for your help once again

Data Types (char, varchar, nchar, nvarchar, ...)

Could someone please help me by explaining which one is best to use and when? For example, storing the word "Corona Del Mar" - which Data Type would be suggested?

Thanks.

I would suggest Varchar(#).

ntype data types support Unicode (non-standard characters). So unless you're expecting complex input (or different languages) char, varchar, and text are what you'll typically be using.

char = fixed length string. If you create a char(10) field and enter "test" the field will actually stored "test" PLUS 6 blank spaces (4 characters in test + 6 blank spaces = 10). So when you output your field, you'll actually get "test ".

varchar = variable length string. This is the bread and butter of most databases. If you create varchar(10) and enter "test", it will store test. The limitation to this field is that it can only store up to 8,000 characters.

text = unlimited text field. Downsides: Text fields are stored OUTSIDE the database record (only a pointer is stored inside the record) so retrieving text fields is slower then retrieving a varchar record. It's also not compatible with some database commands (group etc.)

|||

Chris Pebble:

I would suggest Varchar(#).

ntype data types support Unicode (non-standard characters). So unless you're expecting complex input (or different languages) char, varchar, and text are what you'll typically be using.

char = fixed length string. If you create a char(10) field and enter "test" the field will actually stored "test" PLUS 6 blank spaces (4 characters in test + 6 blank spaces = 10). So when you output your field, you'll actually get "test ".

varchar = variable length string. This is the bread and butter of most databases. If you create varchar(10) and enter "test", it will store test. The limitation to this field is that it can only store up to 8,000 characters.

text = unlimited text field. Downsides: Text fields are stored OUTSIDE the database record (only a pointer is stored inside the record) so retrieving text fields is slower then retrieving a varchar record. It's also not compatible with some database commands (group etc.)

Thank you, that was a very good explaination.

|||

I have a list box that has a few different values "information a, information b, information c" and when any or all are chosen they are stored in my database in one column. What data type would you suggest for this? Thanks.

Data Types

Hello, Is there any document that tells you the pro/cons for using one data
type over another? I just recently upgraded from Access to SQL Server 2000.
I used the Access upgrade wizard and was wondering how it determines when to
assign types to ntext or text. Also, if the character in the text field are
less than 2000 should I convert this field to say varchar(2000) or
nvarchar(2000)?
Thanks
DonIf you need unicode then nvarchar else varchar.
Don't use text unles the field could go over 8000 chars (4000 for nvarchar).
Always review what the upgrade wizard has done - it makes all text fields nvarchar I think which is not good.
It's usually a good time to review the database design.
"Don" wrote:
> Hello, Is there any document that tells you the pro/cons for using one data
> type over another? I just recently upgraded from Access to SQL Server 2000.
> I used the Access upgrade wizard and was wondering how it determines when to
> assign types to ntext or text. Also, if the character in the text field are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>
>|||As Nigel says, review the actual needs, because the upgrade wizard seems to
choose large datatypes... If a tinyint will do , do not use an int or
smallint...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Don" <dons100@.ameritech.net> wrote in message
news:lCkDc.1791$kK4.474@.newssvr16.news.prodigy.com...
> Hello, Is there any document that tells you the pro/cons for using one
data
> type over another? I just recently upgraded from Access to SQL Server
2000.
> I used the Access upgrade wizard and was wondering how it determines when
to
> assign types to ntext or text. Also, if the character in the text field
are
> less than 2000 should I convert this field to say varchar(2000) or
> nvarchar(2000)?
> Thanks
> Don
>

Data Types

Hi...what would be the right data type to use for a field that will hold a
long description that will be keyed in by the user?
Currently, I have it set to use varchar with a size of 3000, but it still
cuts off.
Thanks> Currently, I have it set to use varchar with a size of 3000, but it still
> cuts off.
What does that mean? You are trying to store more than 3,000 characters, or
your stored procedure truncates the entry, or Query Analyzer doesn't show
the full entry, or something else ... ?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
SQL 2000 allows varchar with a max size of 8000. You can use ALTER TABLE
statement to increase the width from 3000 to 8000.
If your problem is just display then try selecting the data with out grid
(text format) option in query analyzer.
Thanks
Hari
MCDBA
"SQL" <nospam@.asdfadsf.com> wrote in message
news:uN9gJH06DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Hi...what would be the right data type to use for a field that will hold a
> long description that will be keyed in by the user?
> Currently, I have it set to use varchar with a size of 3000, but it still
> cuts off.
> Thanks
>|||Assuming this isn't a display issue and you consider this, be careful
,since inserts will then fail when the total length of a row exceeds
8060 bytes. Better to cut the information off than to create a
situation where inserts can cause an error condition. You could also
look into using the text data type. It's not as easy to manipulate
beyond inserts and retrieval, but can hold up to 2 Gig of information,
since it only stores a 16-byte text pointer in the row. If you can,
limit the amount of information inserted at the front end.
If it is a display issue, you will not only need to change the output to
text from grid, but you will also have to go into the Query Analyzer
Tools|Options|[Results] and set the "Maximum Characters per Column:"
value to match or exceed your varchar column length. The default is
256, I believe.
SK
Hari wrote:
>Hi,
>SQL 2000 allows varchar with a max size of 8000. You can use ALTER TABLE
>statement to increase the width from 3000 to 8000.
>
>If your problem is just display then try selecting the data with out grid
>(text format) option in query analyzer.
>Thanks
>Hari
>MCDBA
>"SQL" <nospam@.asdfadsf.com> wrote in message
>news:uN9gJH06DHA.2656@.TK2MSFTNGP11.phx.gbl...
>
>>Hi...what would be the right data type to use for a field that will hold a
>>long description that will be keyed in by the user?
>>Currently, I have it set to use varchar with a size of 3000, but it still
>>cuts off.
>>Thanks
>>
>>
>
>

data types

What is the best data type to use within SQL server in a field for
storing currency?

Regards,
CiarnOn 10 Mar 2005 06:17:03 -0800, chudson007@.hotmail.com wrote:

>What is the best data type to use within SQL server in a field for
>storing currency?

Hi Ciarn,

DECIMAL(??,2), with ?? denoting the total number of digits (including
the cents). So if the maximum value is 5 million, you use DECIMAL(11,2).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(chudson007@.hotmail.com) writes:
> What is the best data type to use within SQL server in a field for
> storing currency?

Hm, what do you mean with "currency"? If you mean amounts, a decimal(n, 2)
as Hugo suggested can be a good idea. Note, though, that for some reason
the money data type has four decimals.

If you are talking about currency prices, such as how many USD you need
by one 1 EUR, then you need many decimals. Usually currency prices go
with six decimals, but often you also need the reverse, and if you
store with six decimals only, the inverted price will suffer a rounding
error. So I would recommend floats in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

data types

what is the equivalent data type of autonumber of access in sql server?Just make it a normal int and set Identity = Yes.

"quartz" <paeng01_66@.yahoo.com> wrote in message
news:9fb191e4.0405231843.6bebf0a9@.posting.google.c om...
> what is the equivalent data type of autonumber of access in sql server?|||paeng01_66@.yahoo.com (quartz) wrote in message news:<9fb191e4.0405231843.6bebf0a9@.posting.google.com>...
> what is the equivalent data type of autonumber of access in sql server?

You can use the IDENTITY property:

create table dbo.Test (
idcol int identity(1,1) not null primary key
)
go

See Books Online for more details.

Simon

Data Types

Hi...what would be the right data type to use for a field that will hold a
long description that will be keyed in by the user?
Currently, I have it set to use varchar with a size of 3000, but it still
cuts off.
Thanks> Currently, I have it set to use varchar with a size of 3000, but it still

quote:

---

> cuts off.

---

What does that mean? You are trying to store more than 3,000 characters, or
your stored procedure truncates the entry, or Query Analyzer doesn't show
the full entry, or something else ... ?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Hi,
SQL 2000 allows varchar with a max size of 8000. You can use ALTER TABLE
statement to increase the width from 3000 to 8000.
If your problem is just display then try selecting the data with out grid
(text format) option in query analyzer.
Thanks
Hari
MCDBA
"SQL" <nospam@.asdfadsf.com> wrote in message
news:uN9gJH06DHA.2656@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hi...what would be the right data type to use for a field that will hold a
> long description that will be keyed in by the user?
> Currently, I have it set to use varchar with a size of 3000, but it still
> cuts off.
> Thanks
>

---

|||Assuming this isn't a display issue and you consider this, be careful
,since inserts will then fail when the total length of a row exceeds
8060 bytes. Better to cut the information off than to create a
situation where inserts can cause an error condition. You could also
look into using the text data type. It's not as easy to manipulate
beyond inserts and retrieval, but can hold up to 2 Gig of information,
since it only stores a 16-byte text pointer in the row. If you can,
limit the amount of information inserted at the front end.
If it is a display issue, you will not only need to change the output to
text from grid, but you will also have to go into the Query Analyzer
Tools|Options|[Results] and set the "Maximum Characters per Column:"
value to match or exceed your varchar column length. The default is
256, I believe.
SK
Hari wrote:

quote:

---

>Hi,
>SQL 2000 allows varchar with a max size of 8000. You can use ALTER TABLE
>statement to increase the width from 3000 to 8000.
>
>If your problem is just display then try selecting the data with out grid
>(text format) option in query analyzer.
>Thanks
>Hari
>MCDBA
>"SQL" <nospam@.asdfadsf.com> wrote in message
>news:uN9gJH06DHA.2656@.TK2MSFTNGP11.phx.gbl...
>
>
>

---

Data types

I need to export the name of certain user tables along with the names of their columns and which type the column contains ie. varchar / integer and so on. I can figure out the table and column names - but how do i retrieve information about the data-types in each column ?

My query so far:

--------
select obj.Name as Tbl,Col.Name as Col
from sysobjects obj, syscolumns col
where obj.xtype='U' and obj.Name like 'netop%' and obj.id=col.id
--------

Thx. in advanceSELECT A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES AS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
ON A.TABLE_NAME = B.TABLE_NAME
WHERE A.TABLE_NAME = 'yourTable'

Very useful views and I strongly recommend that your read more about them on BOL.|||WOW - that was fast - thanks a lot|||Beware of objects with the same name and different owners! Include a join on TABLE_SCHEMA to be safe:

SELECT A.*, A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES AS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_SCHEMA = B.TABLE_SCHEMA

blindman|||Good point and well spotted.

Data type: Text vs VarChar (10000)

I have a table that is used to archive communications.
One of the fields is ComText, I've used the "Text" data type for this field.
Now my question is if I use a varchar data type with the length of (10000) would it improve performance in any way (specialy for reporting purposes)?The maximum row size is less than 8k. Plus, it is not good to have a very long varchar field, the space usage will be poor.|||Thanks for your reply.
Do you have experience regarding this issue and an Access front end, cause it seems to me that access reports have some trobule printing reports with memo fields, specially when the machine has a low RAM.

Data Type varchar and text

I encounter this particular error.

Exception Details:System.Data.SqlClient.SqlException: The data types varchar and text are incompatible in the equal to operator.

Line 21: Dim reader As SqlDataReader = command.ExecuteReader()

This is the first time I'm trying out with MS SQL so I'm abit lost. I hope my code is correct and I've did a little search. I did not set "Text" in my database, I use int and varchar. Here's the affected part of my code and the database.

Dim passwordAs String =""Dim querystringAs String ="SELECT Password FROM Member WHERE Username = @.username"'Dim conn as SqlConnection Using connAs New SqlConnection(ConfigurationManager.ConnectionStrings("mainconnect").ConnectionString)Dim commandAs New SqlCommand(querystring, conn) command.Parameters.Add("@.username", SqlDbType.Text) command.Parameters("@.username").Value = txtLogin.Text conn.Open()Dim readerAs SqlDataReader = command.ExecuteReader()While reader.Read() password = reader("Password").ToString()End While reader.Close()End Using

My database:

User_ID int(4)

Username varchar(50)

Password varchar(255)

Email varchar(50)

Any ideas?

Hi,

line

command.Parameters.Add("@.username", SqlDbType.Text)

should be

command.Parameters.Add("@.username", SqlDbType.VarChar,50)

|||

argh! stupid me.. I forgot about that cause I've been working with MS Access.. Thanks it works!

Data type validation

Hi all,

I want to make a conditional split based on the data type provided by the input.

For example : If the comming (Column x) is of data type (numeric) then pass , else do not pass.

(pass = Case 1

Do not pass = Case 2).

Is there any way for doing so ?

You can use conditional split transformation for this.

The following link will help you get started
http://technet.microsoft.com/en-us/library/ms137886.aspx

Thanks

|||

I think Data Convertion transform is the easiest way to do this, see this for details:

http://technet.microsoft.com/en-us/library/ms186792.aspx

You would read the data as string, then try to convert it to numeric. Configure Error Disposition to redirect row. The "pass" will go to default (green) output, the rows that do not pass will go to error output.

data type to store time

Hi all ,

What datatype should I take to store time in a table -- datetime , float or decimal?

my requirement is to store "Worked Hours in a day by an employee" in the field say, 9 hrs and 30 mins.

I should be able to manipulate data in this field such as total hours present in the month, extra hours worked in a day (considering 9 hrs as standard time),less hours worked in a day, and so on

I would suggest the builtin DATETIME datatype. The advantage of this datatype is that it gives you the ability to directly use the builtin functions that come with TSQL.|||

As Kent indicated, using a datetime is most likely the best option. Yes, it will put the default date of Jan 1st, 1900, but you are interested in the hours.

You will be able to use datetime math functions, AND you don't have to remember to convert 20 minutes to .333333 hr, etc.

IF you are not concerned with seconds and milliseconds, you might consider a smalldatetime datatype.

|||Thanks Kent and Arnie for the quick response. I 'm new to this forum and sql server. Hope many more responses from you guys

Data type to store photo

hello,

I want to store the photo in the sql server 2005 , what datatype should I use?

Moving to SQL Forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=158&SiteID=1

Answer: VarBinary(MAX)

|||

Hi Amit,

You can use the datatype to store the images or data in DATABASE but it is not advisable, best practise says that it is better to store only path of the images or documents and let it play with front end application here is a Research paper availble prepared by ussell Sears; Catharine Van Ingen; Jim Gray http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

HTH

Hemantgiri S. Goswami

|||but wht about replacement or file movement /deletion of the file ...the system /application will crash immediately....