Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
SimonOnly with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name='''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement = 'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
--
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegroups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>sql

Information_Schema query

Hi,
I'm trying to return the number of columns in a table in a different
database, I would like to do this via passing values to the
information_schema so it will check different databases. Currently I
have the following code that works:
select count(*) from database1.information_Schema.columns where
table_Name= @.table_name
** where database1 is the name of the database and @.table_name is a
variable that will change. I would like it so that the database name
can be changed as well, I've tried the following code but it wont run,
reports error next to .
select count(*) from @.database.information_Schema.columns where
table_Name= @.table_name
Is it possible to pass a variable to the information_schema like I am
trying? If not is there a way round this?
Thanks
Simon
Only with Dymanic SQL
Declare @.database varchar(30)
Declare @.table_name varchar(30)
set @.database ='DBName'
set @.table_name ='tableName'
Exec('
select count(*) from '+@.database+'.information_Schema.columns where
table_Name= '''+@.table_name+'''')
Madhivanan
|||Thank for the help.
I'm trying to put the result (i.e. however number of columns) into a
variable of type int.
I've tried both this lines of code but they wont run:
select @.column_limit = ('Exec(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
and:
Exec('select '+@.column_limit+'=(select count(*) from
'+@.database_name+'.information_Schema.columns where table_Name=
'''+@.table_name+''')')
where column_limit is a variable of type int that hold the value of the
number of columns.
Thanks in advance
Simon
|||You execute use a parameterized query with sp_executesql to return output
values from a dynamic SQL statement. For example
DECLARE @.SqlStatement nvarchar(4000)
DECLARE @.database_name sysname
DECLARE @.table_name sysname
DECLARE @.column_limit int
SET @.database_name = 'MyDatabase'
SET @.table_name = 'MyTable'
SET @.SqlStatement =
'SELECT @.column_limit = COUNT(*)
FROM '+@.database_name+'.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @.table_name_param'
EXEC sp_executesql @.SqlStatement,
N'@.column_limit int OUT,
@.table_name_param sysname',
@.column_limit OUT,
@.table_name_param = @.table_name
SELECT @.column_limit
Also, check out http://www.sommarskog.se/dynamic_sql.html
Hope this helps.
Dan Guzman
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128338137.321077.56320@.f14g2000cwb.googlegro ups.com...
> Thank for the help.
> I'm trying to put the result (i.e. however number of columns) into a
> variable of type int.
> I've tried both this lines of code but they wont run:
> select @.column_limit = ('Exec(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> and:
> Exec('select '+@.column_limit+'=(select count(*) from
> '+@.database_name+'.information_Schema.columns where table_Name=
> '''+@.table_name+''')')
> where column_limit is a variable of type int that hold the value of the
> number of columns.
> Thanks in advance
> Simon
>

INFORMATION_SCHEMA on another database

I want to get the max lenght of a column on a table in anther database. I
have a linked server pointing to that database. INFORMATION_SCHEMA will not
work as it reports only works on the current database.
How can I do this.
Thanks
kevinJust tried this on my server, using a four-part name to reference the table:
SELECT linked_srv.catalog.information_schema.[columns]
Worked fine. Is the other database a SQL server db?
"kevin" wrote:

> I want to get the max lenght of a column on a table in anther database. I
> have a linked server pointing to that database. INFORMATION_SCHEMA will n
ot
> work as it reports only works on the current database.
> How can I do this.
> Thanks
> kevin|||Perhaps you can use stored procedures like below for the remote server?
sp_catalogs
sp_linkedservers
sp_indexes
sp_primarykeys
sp_foreignkeys
sp_tables_ex
sp_columns_ex
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:AAB87C6E-97BF-4039-A93D-F072284EC25E@.microsoft.com...
>I want to get the max lenght of a column on a table in anther database. I
> have a linked server pointing to that database. INFORMATION_SCHEMA will n
ot
> work as it reports only works on the current database.
> How can I do this.
> Thanks
> kevin|||Mark;
when connected to MyLocalServer, if I execute this:
SELECT *
from MyLinkedServer.MyLinkedDB.information_schema.[columns]
I get
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'MyLinkedServer' does not contain table
'"MyLinkedDB"."information_schema"."columns"'. The table either does not
exist or the current user does not have permissions on that table.
from MyLinkedServer I get what I expect.
I know that the linked server is set up properly because I have SP's running
.
The linked server is using
"Mark Williams" wrote:
> Just tried this on my server, using a four-part name to reference the tabl
e:
> SELECT linked_srv.catalog.information_schema.[columns]
> Worked fine. Is the other database a SQL server db?
> "kevin" wrote:
>|||In 2000, the info schema views only exists physically in the master database
, which is most likely
why you get this error. Use the system tables or the system stored procedur
es I posted in the other
post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:1E532DC4-6127-433D-972A-1C0E134AA9F2@.microsoft.com...
> Mark;
> when connected to MyLocalServer, if I execute this:
> SELECT *
> from MyLinkedServer.MyLinkedDB.information_schema.[columns]
> I get
> Server: Msg 7314, Level 16, State 1, Line 1
> OLE DB provider 'MyLinkedServer' does not contain table
> '"MyLinkedDB"."information_schema"."columns"'. The table either does not
> exist or the current user does not have permissions on that table.
> from MyLinkedServer I get what I expect.
> I know that the linked server is set up properly because I have SP's runni
ng.
> The linked server is using
> "Mark Williams" wrote:
>

information_schema for temp table ?

Hi there!

I'm trying to find how can I get the information_schema for a temp table.

I'm trying to find all columns of a temp table.

So it will be something like this SELECT * FROM information_schema.columns

But it doesn't work for temp table, I tried tempdb.dbo.information_schema.columns ... nada...

Please help!

Thanks,

Or Thoi don't know what you are really trying to do but try...

SELECT * FROM #MyTempTable WHERE 1 = 0 will give the column names.

I know I do not want know the answer but why do you not know the structure of the temp table?|||I don't know the structure of my temp table because I use pivot tables.

And I transfer my data into an Excel worksheet using VB6. And when I do this, I loose all my columns name.

So it's a real pain in the a**.|||I don't know the structure of my temp table because I use pivot tables.

I am not sure why this matters. Store them in an array of variables and transfer them out too. ReDim sucks I know.

And I transfer my data into an Excel worksheet using VB6. And when I do this, I loose all my columns name.

it aint on the resume no more but i did some VB6 once upon a time. how are are you doing the export? There are a few ways to do this. Recently there was a thread here and there is some info on sqlteam about how to BCP out column names. Have you thought about using BCP?

However I am guessing you are doing the old Open #1 FOR OUTPUT or whatever it was or perhaps you are using filesystem objects.|||No I use CopyFromRecordSet of the Excel.Application.

What's BCP by the way ?

I am not sure why this matters. Store them in an array of variables and transfer them out too. ReDim sucks I know.

As I use a date as a pivot the number of columns still increase day by day|||bulk copy program. google it. or filesystem objects. heck google SQL Server DTS. Or "Visual Basic 6 Open file". I even bet if you check your VBA documentaion, you will find a way to do this. There are many ways to skin a cat.|||I did google the bulk copy, which is very intresting feature of sql.

I also checked at the vba documentions and no body mention how to do that or if it's doable... anyways ...

I'll try to find a way...

Thanks|||I found the best way to do it by myself, it was so easy lol... shame on me.|||enlighten us.|||As i said, I use it in a VB6 app that I made, and the results of the stored proc is stored in a record set so I build an array like this

array(#)= rs.fields(#).name...

It was SOOOO simple...

anyway... thank you all

Information_schema ?

Hello,
When the procedure : sp_tables is executed (master db), the table owners are either dbo or INFORMATION_SCHEMA,
any detail about this last ? (to be precise, table_type is view and not table).
ThanksThese are system views set up in the master database.

They can be used as templates for other databases to get meta data info. Like table, constraint and view info.

All you do is copy them accross into query anyalyzer and create them on your user database.

Cheers

Information_Schema

Hi,
Is there a way to get table Creation Date
using Information_Schema?
Thanks,
RogerNo.
SELECT crdate FROM sysobjects
David Portas
SQL Server MVP
--|||One caveat to be aware of is that crdate is not updated when a stored
procedure is changed using the ALTER PROCEDURE statement. It truly reflects
when the procedure/object was first created, not when it was last changed or
compiled.
--Brian
(Please reply to the newsgroups only.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124218635.981530.150360@.o13g2000cwo.googlegroups.com...
> No.
> SELECT crdate FROM sysobjects
> --
> David Portas
> SQL Server MVP
> --
>sql

Information Schema Query Question

Hello All,
Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?
Thanks in Advance,
AkintoyeHere's an example:
SELECT TC.TABLE_SCHEMA AS TableOwner,
TC.TABLE_NAME,
TC.CONSTRAINT_TYPE,
TC.CONSTRAINT_NAME,
KCU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE IN
(
'PRIMARY KEY',
'UNIQUE'
)
ORDER BY TC.TABLE_SCHEMA, TC.TABLE_NAME, TC.CONSTRAINT_NAME, KCU.COLUMN_NAME
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in message
news:4366775c$1@.ibixwebf.ibi.com...
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoye
>|||"Akintoye Olorode" <akintoye_olorode@.iwaysoftware.com> wrote in
news:4366775c$1@.ibixwebf.ibi.com:

> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
You're looking for simething like this? (SQL Server 2005):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_schema + '.' + tc.table_name = <schema>.<table_name> )
I guess that would be approximately like this in SQL Server 2000 (Not
tested):
select ccu.column_name from
information_schema.constraint_column_usage ccu inner join
information_schema.table_constraints tc
on (tc.constraint_name = ccu.constraint_name)
where
(tc.constraint_type in ('unique','primary key')) and
(tc.table_name = <table_name> )
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in
news:Xns9700F370E9A4Dolekristianbangaas@.
207.46.248.16:
(...)
Ooops, stayed a littlebit too long in my outbox :( Sorry folks.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging

Information Schema Query Question

Hello All,

Could someone help provide a query that I can run to
determine the primary key and unique columns for any
given table ?

Thanks in Advance,

Akintoyeselect t.table_name, t.constraint_type, t.constraint_name,
c.column_name
from information_schema.TABLE_CONSTRAINTS t
join information_schema.CONSTRAINT_COLUMN_USAGE c ON t.constraint_name
= c.constraint_name
where constraint_type IN ('UNIQUE', 'PRIMARY KEY')
and t.table_name = <Table Name
Akintoye Olorode wrote:
> Hello All,
> Could someone help provide a query that I can run to
> determine the primary key and unique columns for any
> given table ?
> Thanks in Advance,
> Akintoyesql

Information schema

How can I find out if a specific column is an Identity column or which
column in a specific table is the Identity column?
Thank you.Lookup the metadata function COLUMNPROPERTY in SQL Server Books Online. It
has an argument which takes the value IsIdentity that can be used for such
requirements.
Anith|||Try,
use northwind
go
select
table_schema,
table_name,
column_name,
ident_seed(table_schema + '.' + quotename(table_name)) as col_ident_seed,
ident_incr(table_schema + '.' + quotename(table_name)) as col_ident_incr,
ident_current(table_schema + '.' + quotename(table_name)) as
col_ident_current
from
information_schema.columns
where
objectproperty(object_id(table_schema + '.' + quotename(table_name)),
'IsUserTable') = 1
and objectproperty(object_id(table_schema + '.' + quotename(table_name)),
'IsMSShipped') = 0
and columnproperty(object_id(table_schema + '.' + quotename(table_name)),
column_name, 'IsIdentity') = 1
order by
table_schema,
table_name,
ordinal_position
go
AMB
"Vik" wrote:

> How can I find out if a specific column is an Identity column or which
> column in a specific table is the Identity column?
> Thank you.
>
>

Information on RAID, Table size calculation

Where can I find some info on RAID 1 and RAID 5. Can we implement both on
One machine. To do so how many harddrives are needed along with RAID
controllers(1 or 2)?
What's the best way to calculate table space/db size requirement with
varchar, bigint, image and text fields. Can I find a spreadsheet etc on the
net?
Also can some one post scripts to backup and restore (Point in Time) ? Any
good web site on backup, restore and tracing?
Thanks
BVRPlease read http://www.baarf.com/ before deploying any database on top of
RAID 5.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Uhway" <Uhway@.discussions.microsoft.com> wrote in message
news:0B3439D9-DBD6-4BEF-B4B3-27B37FFD8529@.microsoft.com...
> Where can I find some info on RAID 1 and RAID 5. Can we implement both on
> One machine. To do so how many harddrives are needed along with RAID
> controllers(1 or 2)?
> What's the best way to calculate table space/db size requirement with
> varchar, bigint, image and text fields. Can I find a spreadsheet etc on
> the
> net?
> Also can some one post scripts to backup and restore (Point in Time) ?
> Any
> good web site on backup, restore and tracing?
> Thanks
> BVR

Information on RAID, Table size calculation

Where can I find some info on RAID 1 and RAID 5. Can we implement both on
One machine. To do so how many harddrives are needed along with RAID
controllers(1 or 2)?
What's the best way to calculate table space/db size requirement with
varchar, bigint, image and text fields. Can I find a spreadsheet etc on the
net?
Also can some one post scripts to backup and restore (Point in Time) ? Any
good web site on backup, restore and tracing?
Thanks
BVRPlease read http://www.baarf.com/ before deploying any database on top of
RAID 5.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"Uhway" <Uhway@.discussions.microsoft.com> wrote in message
news:0B3439D9-DBD6-4BEF-B4B3-27B37FFD8529@.microsoft.com...
> Where can I find some info on RAID 1 and RAID 5. Can we implement both on
> One machine. To do so how many harddrives are needed along with RAID
> controllers(1 or 2)?
> What's the best way to calculate table space/db size requirement with
> varchar, bigint, image and text fields. Can I find a spreadsheet etc on
> the
> net?
> Also can some one post scripts to backup and restore (Point in Time) ?
> Any
> good web site on backup, restore and tracing?
> Thanks
> BVR

Information on RAID, Table size calculation

Where can I find some info on RAID 1 and RAID 5. Can we implement both on
One machine. To do so how many harddrives are needed along with RAID
controllers(1 or 2)?
What's the best way to calculate table space/db size requirement with
varchar, bigint, image and text fields. Can I find a spreadsheet etc on the
net?
Also can some one post scripts to backup and restore (Point in Time) ? Any
good web site on backup, restore and tracing?
Thanks
BVR
Please read http://www.baarf.com/ before deploying any database on top of
RAID 5.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Uhway" <Uhway@.discussions.microsoft.com> wrote in message
news:0B3439D9-DBD6-4BEF-B4B3-27B37FFD8529@.microsoft.com...
> Where can I find some info on RAID 1 and RAID 5. Can we implement both on
> One machine. To do so how many harddrives are needed along with RAID
> controllers(1 or 2)?
> What's the best way to calculate table space/db size requirement with
> varchar, bigint, image and text fields. Can I find a spreadsheet etc on
> the
> net?
> Also can some one post scripts to backup and restore (Point in Time) ?
> Any
> good web site on backup, restore and tracing?
> Thanks
> BVR

Information in Log and Data file

Hi,
any one explain what information is available in log and what in data file.
For example
Am inserting a row into a table. how this will get stored.
When the information is transformed to data file.
thanks,
Herbert
Hi
You may want to start by browsing the Arcitecture section of Books online.
John
"Herbert" wrote:

> Hi,
> any one explain what information is available in log and what in data file.
> For example
> Am inserting a row into a table. how this will get stored.
> When the information is transformed to data file.
> thanks,
> Herbert
sql

Information in Log and Data file

Hi,
any one explain what information is available in log and what in data file.
For example
Am inserting a row into a table. how this will get stored.
When the information is transformed to data file.
thanks,
HerbertHi
You may want to start by browsing the Arcitecture section of Books online.
John
"Herbert" wrote:

> Hi,
> any one explain what information is available in log and what in data file
.
> For example
> Am inserting a row into a table. how this will get stored.
> When the information is transformed to data file.
> thanks,
> Herbert

Information in Log and Data file

Hi,
any one explain what information is available in log and what in data file.
For example
Am inserting a row into a table. how this will get stored.
When the information is transformed to data file.
thanks,
HerbertHi
You may want to start by browsing the Arcitecture section of Books online.
John
"Herbert" wrote:
> Hi,
> any one explain what information is available in log and what in data file.
> For example
> Am inserting a row into a table. how this will get stored.
> When the information is transformed to data file.
> thanks,
> Herbert

info about sysprocesses

Dear all,
I want to know all the possible values for the status field bring up for
sysprocesses table. Values such 'running' or 'sleeping' seems very
evident but there is one so-called 'DEF-WK...' or something like that which
I haven't idea.
In this occasion I am not be able to find it inside the BOL
Does anyone know how do I figure out such values?
Thanks in advance,
EnricHi
Look at the code from the system SP sp_who2
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:C9B83081-19A4-4A33-AD4C-F75AE450B394@.microsoft.com...
> Dear all,
> I want to know all the possible values for the status field bring up for
> sysprocesses table. Values such 'running' or 'sleeping' seems very
> evident but there is one so-called 'DEF-WK...' or something like that
> which
> I haven't idea.
> In this occasion I am not be able to find it inside the BOL
> Does anyone know how do I figure out such values?
> Thanks in advance,
> Enric

Infinity problem

Hi,

I have a table with some database fields and some calculated values. Sometimes it happens that I divide by 0 or null. As a result I get 'Infinity' in my textbox, is it possible to get rid of this 'message'?

greetz

Im not sure what the return value of that message is .... but if its a string containing the word "Infinity" you could try something like this:

Your field that sometimes returns infinity is: CalculatedField
IIf(CalculatedField = "Infinity", "Write your expression when true", CalculatedField)

That expression is used for a new calculated field and that field you can use in a textbox

|||

That could idd be a solution, but isn't there any way to use formatting. I don't like changing the value of my textbox.

greetz

|||

I recommend to add a custom code function for the division (in Report -> Report Properties -> Code). Call that custom code function inside of performing the division directly in the expression.

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

-- Robert

inedex - should I cluster?

Hi, I have the following delema:
I have a table that has the following fields:
companyid
warehouseid
year
month
productid
val1
...
valn
there are 3 companies, 15 warehouse, 4 year history, 12 months, 3000 prodocts.
the primary key is : companyid, warehouseid, year, month, productid
a) At the end of each month new values are recorded on the table.
b) During the month updates are issued to the current month.
c) queries are usualy include:
* company, warehouse, year, month
* company, year, month, product
Question: what is a good clusterd index for this table, what other indexes
should I implement.
Thx,
Juan"jccondor" <jccondor@.discussions.microsoft.com> wrote in message
news:1373BADF-2A63-4D61-853C-C6A9B894DF73@.microsoft.com...
> Hi, I have the following delema:
> I have a table that has the following fields:
> companyid
> warehouseid
> year
> month
> productid
> val1
> ...
> valn
> there are 3 companies, 15 warehouse, 4 year history, 12 months, 3000
> prodocts.
> the primary key is : companyid, warehouseid, year, month, productid
> a) At the end of each month new values are recorded on the table.
> b) During the month updates are issued to the current month.
> c) queries are usualy include:
> * company, warehouse, year, month
> * company, year, month, product
> Question: what is a good clusterd index for this table, what other indexes
> should I implement.
>
The primary key looks like a good clustered index. Since the queries
usually include the four leading PK columns (company, warehouse, year,
month), or the leading column and other PK columns (company, year, month,
product), a clustered PK will be helpful. Of course the updates will
specify the whole key.
For other indexes, look at the workload. You may want a secondary index on
(year,month) or (product).
David
David

Infinite Loop in cursor

Hi

I have an infinite loop in a trigger I and I cant reslove it.

In my system the user updates a stock table from the GUI and on the update I need to check values to see if I need to add records to a StockHistory table. For Example: If the user changes the grade of Product X from A to B then I need to add a new line in StockHistory for product X grade A that decrements the total number of products in the warehouse. Similary I need to increase the quantity of stock for Product X grade B.
I had the trigger working for single updates but now when stock is added to the database (from another db) it has status of 'New'. This isn't actually 'in stock' until the user sets the status to 'Goods In'. This process will then update the status for all records in the category. This caused my trigger to fail as the 'inserted' table now contains many records.
Now the problem I have is the trigger is in an infinite loop. It always shows the id of the first record it finds and the @.Quantity values increases as expected. I've taken all my procesing code out of the trigger and adding some debugging stuff but it still doesnt work:
CREATE TRIGGER [StockReturns_on_change] ON [dbo].[StockReturns]
FOR UPDATE
AS
DECLARE INDIVIDUAL Cursor Cursor for all the rows being updated
FOR
SELECT Id FROM inserted
OPEN INDIVIDUAL
FETCH NEXT FROM INDIVIDUAL INTO @.Id
select @.Quantity = 1
print @.@.FETCH_STATUS
print @.Id
print @.Quantity
WHILE @.@.FETCH_STATUS = 0
begin
select @.Quantity = @.Quantity + 1
print @.@.FETCH_STATUS
print @.Id
print @.Quantity
-- Get the next row from the inserted table
FETCH NEXT FROM INDIVIDUAL INTO @.Id
End -- While loop on the cursor
-- no close off the cursors
CLOSE INDIVIDUAL
DEALLOCATE INDIVIDUAL


Can you help me please?
Kind Regards

i think this is not an infinite query but a "long running" query. its going to finish

its just taking a lot of time

i suggest you get rid of the cursor replace it with a faster and better update code that process multiple records at the same time

use aggregate function(sum, count,min, max.. etc) in place of your counters (@.x=@.x+1)

cursors are very slow way of doing things

regards,

|||

Thanks for the reply.

Nop its an infinite loop. The print statements show that id is always the same and the quanityt counter is being incremented showing that its travelling round the cursor. Or are you saying its looping around but still waiting for the first transaction to finish? I went out yesterday for 20 minutes and it was still showing the 1st record.

If I knew what code to write that would work then id do it. Unfortunatley this is the only way I can think of doing it.

Each record needs to be treated seperately as i have to inpect the values of 2 variables (both in inserted and deleted) to see if they have changed for each record. I cannot do a bulk insert.

|||

here are your watch point

1. cursors are realy slow

2. maybe the triggers are in recurssion. meaning this trigger is fired by an update event of table1. in case the "update triggers" updates the same table (table1) again its going to call the same update trigger again and the cycle go an and on until 32 level deep per record.

if thats the case it will realy take a while to finish

solution:

you can write queries or correlated subqueries that joins your inserted and deleted table with the base tables to do the comparison.

such as

update basetable1 set fieldname = select count(xx) from

from inserted where inserted.id=basetable.id

in this way you do a one way trip to the server so even if it will be in recursion it is still fast

|||

this trigger is on the stock table and it doesnt insert or update anything. Check the code i posted. It merely attempts to get the next value from the "inserted" table. I'm running that exact code and it does loop forever.

Heres dome debug ive collected whilst running the sql "Update stockreturns set itemstatus = 1 where id = 26301": (so its only updating 1 record)

0 fetch status
26301 id updating
1 loop counter
0
26301
2
0
26301
3
0
26301
4
0
26301
5
0
26301
6
0
26301
7
0
26301
8
0
26301
9
etc

Regards

|||

i've modified your trigger and apply it to northind. employees

here's the code

use northwind

CREATE TRIGGER [StockReturns_on_change] ON [dbo].[employees]
FOR UPDATE
AS
declare @.id int
declare @.quantity int
DECLARE INDIVIDUAL Cursor Cursor for all the rows being updated

FOR
SELECT employeeId FROM inserted

OPEN INDIVIDUAL

FETCH NEXT FROM INDIVIDUAL INTO @.Id

select @.Quantity = 1

print @.@.FETCH_STATUS
print @.Id
print @.Quantity

WHILE @.@.FETCH_STATUS = 0
begin

select @.Quantity = @.Quantity + 1

print @.@.FETCH_STATUS
print @.Id
print @.Quantity

-- Get the next row from the inserted table
FETCH NEXT FROM INDIVIDUAL INTO @.Id

End -- While loop on the cursor

-- no close off the cursors
CLOSE INDIVIDUAL
DEALLOCATE INDIVIDUAL

go

update employees set lastname='joey' where employeeid=1

and heres the result

0
1
1
0
1
2

|||

now i've tried this

begin transaction
update employees set lastname='joey' where employeeid<6

and here's the result

0
1
1
0
1
2
0
2
3
0
3
4
0
4
5
0
5
6

(5 row(s) affected)

meaning this is not a infinite loop but a slow running query. how many records are you updating. by the way whats your requirements?

|||

Hi Joey

Thanks for your efforts in trying to get me to understand!!

My requirements, ok here goes.

The user prints a stock manifest when stock comes into the warehouse which copies all the records to do with that manifest (store and date) from another database to my stock database. When these records are copied they are copied with a statusid of 0 (new stock). The manifest can have anywhere between 1 and 100 products on it.

The warehouse will then classify each product into grades A,B,C or D. The products by default are grade C in the database. After the products for this manifest have been classified a "goods in confirmation" report is produced which updates all the products on that manifest from statusid = 0 to statusid = 1 (goods in). Meaning that only now will they appear on stock reports and can be picked for despatched.

Now, we have a table called stockHistory which holds movements for every product (piece of equipment). Only when the products are cliassified as statusid = 1 do they offically enter the warehouse and so the historic table needs to be updated to include these products. Historic data is never changed or deleted. Only inserts are allowed. The historic table also holds the total number of each product in stock so when we are adding Product X Grade C to the warehouse we get the max(id) for Product X Grade C and increment the total quantity value by 1 as we are added a product to the warehouse of that type.

But, the stock can also go to Despatched which means we decrement a value. Despatched items have status of 5.

Also the grade of the stock can change which will mean we need to decrement from the old grade and increment from the new grade.

My trigger before i tried to get it working looked like:

SELECT @.newItemState = (SELECT itemStatus FROM Inserted)
SELECT @.oldItemState = (SELECT itemStatus FROM deleted)
select @.OldGrade = (select Grade from deleted) -- fetch the product id
select @.Grade = (select Grade from inserted) -- fetch the product id

DECLARE @.State tinyint

-- If its a product going to stock then insert
if (@.oldItemState = 0 and @.newItemState = 1)
begin
select @.State = 1
end

-- If the grade has changed we need to remove from old grade and add to new grade
else if (@.newItemState = 1 and @.Grade <> @.OldGrade)
begin
select @.State = 2
end

-- If the item has been dispatched remove from new grade
else if (@.newItemState = 5)
begin
select @.State = 3
end

so then i increment the current value on state 1 and 2 and decrement the value of the old product on state 2 and 3.

So if its state 1 or 3 then you use the values contained in "inserted" otherwise if state = 2 then i need to use the record of the old product grade "deleted" to decrement the quantity and use the new product grade to increment the quantity.

Damn that was hard work explaining, I hope that makes your understanding of my problem easier!!

At first I was under the impression that the trigger would fire for each SINGLE update but when I found out it didnt I thought looping round the inserted table was the thing to do.

So why does my loop go on forever and yours stops. Both bits of code are reading from the "inserted" table and simply printing the "id". We arent updating any other tables to cause a problem. Is there a setting in SQL 2000 which I have switched off?

Regards

|||

You don't need a cursor loop to perform this logic. You can just do it via DML statements alone.

-- If its a product going to stock then insert

insert into ....

select ....

from inserted as i

join deleted as d

on d.key_col = i.key_col

where d.itemStatus = 0 and i.itemStatus = 1

-- If the grade has changed we need to remove from old grade and add to new grade
/* I am not sure if remove means update existing row or delete from table. */

Anyway, I hope you get the idea. Using cursor loop for this is probably overkill and has performance implications. It is much simpler to write series of DML statements by inspecting the rows of inserted/deleted tables as necessary. If you need more help then please post some sample schema, data and expected results for one particular case. You can then do the same for the rest.

|||

OK cheers for that but Ive got one thing I have forgotten to say.

The stockHistory table contains 2 columns that come from a different table when the product is being despatched.

When a product is despatched the advicenote and processorid both need to be from the picklist table.

The stock table holds the fk to the picklist table called picklistid. From the picklist table I need the processorid and the despatchnote which get inserted into the processorid and advicenote columns respectively of the stockHistory table.

So I don't think I can simply do a bulk insert using data straight from the inserted table. Or can I?

Thanks

|||

UNBELIEVABLE!!

Joey, I just tried my code against the nortwind.employees table and it looped continuously. I tried your code and it worked.

I then have put your code against my stock table and run the upodate and it worked.

I've compared yours against mine and they are (as far as I can see) identical.

Im going to give it a go now and see what happens!!

Cheers

|||

IVE GOT IT.

THE LINE

-- Get the next row from the inserted table

causes it to forever loop. Without this it works fine!

|||

All sorted now and the trigger works fine.

Cheers for testing out my code Joey.