Finding a Deadlock

Are there any type of utilities on SQL Enerprise manager that can help me
locate a deadlock?The two most useful utilities are DBCC TRACEON and SQL Profiler.
SQL Profiler has Lock:Deadlock & Lock:Deadlock Chain events which are useful
in interpreting deadlocks, but issuing the command: "DBCC TRACEON (3605,
1204, -1)" establishes a trace within SQL Server which writes detailed
deadlock debug info to the SQL Server Error Log.
SQL Profiler is a separate GUI tool from the EM, but can be launched from
the EM's tools menu. DBCC TRACEON is a command - you issue it from the Query
Analyser, but it writes output to the SQL Error Logs which can in turn be
read from the EM (under the Management node).
HTH
Regards,
Greg Linwood
SQL Server MVP
"MFRASER" <mfraser@.henwoodenergy.com> wrote in message
news:OKugdAm%23DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Are there any type of utilities on SQL Enerprise manager that can help me
> locate a deadlock?
>|||Greg's advice is good. I would add, check out BOL for information about interpretting the output of trace flag 1204. The main thing is that it will identify the proc or SQL code which causes the deadlock and the resource that is deadlocked (row, page, key etc)
Cheers

Finding a Deadlock

Are there any type of utilities on SQL Enerprise manager that can help me
locate a deadlock?The two most useful utilities are DBCC TRACEON and SQL Profiler.
SQL Profiler has Lock:Deadlock & Lock:Deadlock Chain events which are useful
in interpreting deadlocks, but issuing the command: "DBCC TRACEON (3605,
1204, -1)" establishes a trace within SQL Server which writes detailed
deadlock debug info to the SQL Server Error Log.
SQL Profiler is a separate GUI tool from the EM, but can be launched from
the EM's tools menu. DBCC TRACEON is a command - you issue it from the Query
Analyser, but it writes output to the SQL Error Logs which can in turn be
read from the EM (under the Management node).
HTH
Regards,
Greg Linwood
SQL Server MVP
"MFRASER" <mfraser@.henwoodenergy.com> wrote in message
news:OKugdAm%23DHA.1844@.TK2MSFTNGP11.phx.gbl...
> Are there any type of utilities on SQL Enerprise manager that can help me
> locate a deadlock?
>|||Greg's advice is good. I would add, check out BOL for information about inte
rpretting the output of trace flag 1204. The main thing is that it will iden
tify the proc or SQL code which causes the deadlock and the resource that is
deadlocked (row, page, key
etc).
Cheers

find type of a column

hi

is it possible to find a column's type inside stored procedure? I am going to get the name of the table and then work with the columns and I just need the type of the column if I have had the name of it

regards

Maybe something like this:

use tempdb
go

create table ##typeExample (anotherType integer, targetType double precision)

declare @.objectName sysname set @.objectName = '##typeExample'
declare @.columnName sysname set @.columnName = 'targetType'

select c.colid,
c.[name] as columnName,
t.[name] as type
from sysobjects o
inner join syscolumns c
on o.id = c.id
and o.[name] = @.objectName
and c.[name] = @.columnName
-- and o.type = 'U' -- Uncomment if you want only user tables
-- and o.type in ('U','V') -- Uncomment if you want tables and views
inner join systypes t
on c.xtype = t.xtype

-- - Output: -

-- colid columnName type
-- -
-- 2 targetType float

go

drop table ##typeExample
go

Dave

FIND OUT IT IS A NUMBER OR STRINg

I have a column with varchar type..
It has some records like this...
isbuluyorum
11222
a1285
12r23
How can i find out it is a number or string ?Savas Ates wrote:
> I have a column with varchar type..
> It has some records like this...
> isbuluyorum
> 11222
> a1285
> 12r23
>
> How can i find out it is a number or string ?
SELECT isbuluyorum AS is_numeric
FROM your_table
WHERE isbuluyorum NOT LIKE '%[^0-9]%' ;
SELECT isbuluyorum AS is_alpha
FROM your_table
WHERE isbuluyorum LIKE '%[^0-9]%' ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||table name =savo
column name=sal type=CHAr
records
sal
saalasdad
12da
122
dasd212
I applied
SELECT sal AS is_numeric
FROM savo
WHERE sal NOT LIKE '%[^0-9]%' ;
SELECT sal AS is_alpha
FROM savo
WHERE sal LIKE '%[^0-9]%' ;
It returned
is_alpha is_numeric
saalasdad Nothing
12da
122
dasd212
"Savas Ates" <in da club>, haber iletisinde unlar
yazd:%23sVx3MBJGHA.3000@.TK2MSFTNGP14.phx.gbl...
>I have a column with varchar type..
> It has some records like this...
> isbuluyorum
> 11222
> a1285
> 12r23
>
> How can i find out it is a number or string ?
>|||SELECT sal, isnumeric(sal)
FROM savo
"Savas Ates" <in da club> wrote in message
news:eyFEMjBJGHA.3752@.TK2MSFTNGP11.phx.gbl...
> table name =savo
> column name=sal type=CHAr
> records
> sal
> saalasdad
> 12da
> 122
> dasd212
> I applied
> SELECT sal AS is_numeric
> FROM savo
> WHERE sal NOT LIKE '%[^0-9]%' ;
> SELECT sal AS is_alpha
> FROM savo
> WHERE sal LIKE '%[^0-9]%' ;
> It returned
> is_alpha is_numeric
> saalasdad Nothing
> 12da
> 122
> dasd212
>
>
> "Savas Ates" <in da club>, haber iletisinde unlar
> yazd:%23sVx3MBJGHA.3000@.TK2MSFTNGP14.phx.gbl...
>|||Savas Ates wrote:
> I have a column with varchar type..
> It has some records like this...
> isbuluyorum
> 11222
> a1285
> 12r23
>
> How can i find out it is a number or string ?
http://www.aspfaq.com/show.asp?id=2390
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||On Sat, 28 Jan 2006 16:40:28 +0200, "Savas Ates" <in da club> wrote:

>table name =savo
>column name=sal type=CHAr
>records
>sal
>saalasdad
>12da
>122
>dasd212
>I applied
>SELECT sal AS is_numeric
> FROM savo
> WHERE sal NOT LIKE '%[^0-9]%' ;
>SELECT sal AS is_alpha
> FROM savo
> WHERE sal LIKE '%[^0-9]%' ;
>It returned
> is_alpha is_numeric
>saalasdad Nothing
>12da
>122
>dasd212
Hi Savas,
Since the datatype is CHAR (not VARCHAR), all values are padded with
spaces. And spaces are not numeric.
Try:
SELECT sal AS is_numeric
FROM savo
WHERE RTRIM(sal) NOT LIKE '%[^0-9]%' ;
SELECT sal AS is_alpha
FROM savo
WHERE RTRIM(sal) LIKE '%[^0-9]%' ;
Hugo Kornelis, SQL Server MVP|||isnumeric will not work reliably in a situation like this as "numbers" with
an e or a d (I think a few others also) will match as numeric according to
this function.
"Carl Johansen" <carl@._NOSPAM_carljohansen.co.uk> wrote in message
news:drgcjq$r6f$1@.nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
> SELECT sal, isnumeric(sal)
> FROM savo
> "Savas Ates" <in da club> wrote in message
> news:eyFEMjBJGHA.3752@.TK2MSFTNGP11.phx.gbl...
>

Find floats with exponential notation

I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
Matt
The float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>
|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt
|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt
|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.

>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>

Find floats with exponential notation

I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
MattThe float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
> > The size at which a float is displayed using the E notation is built
> > into the front end program performing the display. It is not an
> > attribute of SQL Server.
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.
>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>> The size at which a float is displayed using the E notation is built
>> into the front end program performing the display. It is not an
>> attribute of SQL Server.
>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>

Find floats with exponential notation

I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
MattThe float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using e
xp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decim
al
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into
a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows whe
re
> that field is exponential notation. So, if there is ANY way to determine i
f
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation wheth
er
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.

>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:

>I understand that, but I still need to identify those that display using ex
p
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decima
l
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows wher
e
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whethe
r
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>

Find Fileds Type

How Can I Find Fileds type (for example Bit or nvarchar or ntext ,...) in Sql Server;
with how sql parametr or query ?You could start by look at syscolumns|||Here is what I do in the EntityBroker O/R mapper (in the part about syncrhonizing database schemata) fto find out how the db does look at the moment.

Field types:

::SELECT *
::FROM information_schema.columns
::INNER JOIN information_schema.tables
::ON information_schema.columns.table_catalog = information_schema.tables.table_catalog
::AND information_schema.columns.table_Name = information_schema.tables.table_name
::ORDER BY information_schema.columns.table_catalog,
::information_schema.columns.table_schema,
::information_schema.columns.table_name, information_schema.columns.ordinal_position

Find column name and datatype of a given table

Hi,
How do we find the "column name" and "data type" of all the columns in a table. Assuming that I know the Table name or Table Object ID. I am using Microsoft SQL Server 2000.
Thanks
-SudhakarHi sudhakar_112

Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

Specifically:
SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

HTH|||Thank you..

using the following query lists only the columns that are acessible to the user. If the user does not have permission on a column it will not be displayed. But I want a query to display all column names irrespective of the user has any permissions to modify or select.

Thanks
-Sudhakar


Hi sudhakar_112

Have a peak at the INFORMATION_SCHEMA tables in BoL - terribly useful.

Specifically:
SELECT COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable'

HTH|||You can construct a query on the syscolumns and sysobjects tables - these are not dependent on the user permissions.