Losing temporal tables

Hi, i want to know if there's any particular reason why a temporal
table can be dropped before closing the session.
I'm having the following problem: i create the temp table when a form
of my application is created, work with it and then drop it on the
form's close event.
It works fine most of the time, but from time to time the table seems
to be dropped before i close the form because i'm having a: #MyTable
doesn't exists error message.
Any ideas?
Working against SQL2K, W2K Server, from a W2K Pro machine.
Thanx
If your connection is getting dropped at any time you will loose the temp
table. Temp tables are really designed for a brief life span. If you need
to hold certain information for long periods of time like that you may want
to consider using real tables. Or better yet maybe a RS on the client.
Andrew J. Kelly SQL MVP
"Guillermo Casta?o A" <guillermoc74@.hotmail.com> wrote in message
news:9350d78d.0409170946.33db2291@.posting.google.c om...
> Hi, i want to know if there's any particular reason why a temporal
> table can be dropped before closing the session.
> I'm having the following problem: i create the temp table when a form
> of my application is created, work with it and then drop it on the
> form's close event.
> It works fine most of the time, but from time to time the table seems
> to be dropped before i close the form because i'm having a: #MyTable
> doesn't exists error message.
> Any ideas?
> Working against SQL2K, W2K Server, from a W2K Pro machine.
> Thanx

Looping through Column to Insert

Attempting to insert a new Item for each Vendor into a Vendor Item Catalog. However, the Vendors are in no particular order.

ie... sample data

CompanyID,VendorID,ItemID

Comp1,1004,Event1

Comp1,3433,Event2

Comp1,45343,Event3

I need to be able to loop through to the next Vendor that doesnt already have this ItemID.

Any ideas?

Maybe something like this:

declare @.source table
( CompanyID varchar(8),
VendorID integer,
ItemID varchar(8)
)
insert into @.source
select 'Comp1', 1004, 'Event1' union all
select 'Comp1', 3433, 'Event2' union all
select 'Comp1',45343, 'Event3'

declare @.target table
( CompanyID varchar(8),
VendorID integer,
ItemID varchar(8)
)
insert into @.target
select 'Comp1', 1004, 'Event1'

insert into @.target

select CompanyID,
VendorID,
ItemID
from @.source a
where not exists
( select 0 from @.target b
where a.companyId = b.companyId
and a.vendorId = b.vendorId
and a.ItemId = b.ItemId
)

select * from @.target
/*
CompanyID VendorID ItemID
-- --
Comp1 1004 Event1
Comp1 3433 Event2
Comp1 45343 Event3
*/

|||

Are you trying to INSERT or UPDATE?

Adamus

|||

I am attempting to INSERT. I tried using the suggested code WHERE NOT EXISTS but it returned nothing.

|||It would be good if you post the code you are executing with a description of the tables involved.|||

CODE:

declare @.CompID as varchar(5),

@.VendID as varchar(15)

SET @.CompID = SELECT CompanyID FROM ARIVendorCatalog WHERE NOT EXISTS

(SELECT TOP 1 CompanyID FROM ARIVendorCatalog

WHERE VendorItemID = 'Events - Internal Meetings')

SET @.VendID = SELECT VendorID FROM ARIVendorCatalog WHERE NOT EXISTS

(SELECT TOP 1 VendorID FROM ARIVendorCatalog

WHERE VendorItemID = 'Events - Internal Meetings')

INSERT INTO ARIVendorCatalog

VALUES (@.CompID, @.VendID, 'Events - Internal Meetings', 'Events - Internal Meetings', '0', 'Z-US$', '','','')

I Figure that once inserted, the TOP vendor will change every time until there is none left.

|||

Ilana:

You might be able to accomplish the insert with something like this:

create table ARIVendorCatalog
( CompID varchar(5),
VendorID varchar(15),
VendorItemID varchar(30),
Column_04 varchar(30),
Column_05 varchar(10),
Column_06 varchar(10),
Column_07 varchar(10),
Column_08 varchar(10),
Column_09 varchar(10)
)
go

insert into ARIVendorCatalog
select 'Comp1', 'Vend1', 'Events - Internal Meetings', 'Events - Internal Meetings', '0', 'Z-US$', '', '', '' union all
select 'Comp1', 'Vend2', 'Bricka Bracka Firecracker', 'Bugs Bunny, Bugs Bunny', '0', 'Z-US$', '', '', '' union all
select 'Comp2', 'Vend3', 'Help Richard Starr', 'Bingo Night', '0', 'Z-US$', '', '', ''

insert into ARIVendorCatalog
select CompID,
VendorID,
'Events - Internal Meetings',
'Events - Internal Meetings',
'0', 'Z-US$', '', '', ''
from ( select CompID,
VendorID,
max ( case when VendorItemID = 'Events - Internal Meetings'
then 1 else 0 end
) as hasTargetEvent
from ARIVendorCatalog
group by CompID, VendorID
having max ( case when VendorItemID = 'Events - Internal Meetings'
then 1 else 0 end
) = 0
) source

select * from ARIVendorCatalog

/*
CompID VendorID VendorItemID Column_04 Column_05 Column_06 Column_07 Column_08 Column_09
- - - - -
Comp1 Vend1 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp1 Vend2 Bricka Bracka Firecracker Bugs Bunny, Bugs Bunny 0 Z-US$
Comp2 Vend3 Help Richard Starr Bingo Night 0 Z-US$
Comp1 Vend2 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp2 Vend3 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
*/

|||

Ilana:

There is a much better way of doing this if you happen to also have a "VENDOR" table that lists all unique active combinations of CompID and VendorID. If that is true you will not need to compute the aggregate in my last example and you might be able to do something like this:

create table ARIVendorCatalog
( CompID varchar(5),
VendorID varchar(15),
VendorItemID varchar(30),
Column_04 varchar(30),
Column_05 varchar(10),
Column_06 varchar(10),
Column_07 varchar(10),
Column_08 varchar(10),
Column_09 varchar(10)
)
go

insert into ARIVendorCatalog
select 'Comp1', 'Vend1', 'Events - Internal Meetings', 'Events - Internal Meetings', '0', 'Z-US$', '', '', '' union all
select 'Comp1', 'Vend2', 'Bricka Bracka Firecracker', 'Bugs Bunny, Bugs Bunny', '0', 'Z-US$', '', '', '' union all
select 'Comp2', 'Vend3', 'Help Richard Starr', 'Bingo Night', '0', 'Z-US$', '', '', ''

create table Vendor
( CompID varchar(5),
VendorID varchar(15),
vendorName varchar(20)
)
go

insert into Vendor
select 'Comp1', 'Vend1', 'Jaba Vendor' union all
select 'Comp1', 'Vend2', 'Blue Vendor' union all
select 'Comp2', 'Vend3', 'Gold Vendor' union all
select 'Comp2', 'Vend4', 'New Vendor'

insert into ARIVendorCatalog
select CompID,
VendorID,
'Events - Internal Meetings',
'Events - Internal Meetings',
'0', 'Z-US$', '', '', ''
from Vendor a
where not exists
( select 0 from ARIVendorCatalog b
where a.CompID = b.CompID
and a.VendorID = b.VendorID
and VendorItemId = 'Events - Internal Meetings'
)

select * from ARIVendorCatalog

/*
CompID VendorID VendorItemID Column_04 Column_05 Column_06 Column_07 Column_08 Column_09
- - - - -
Comp1 Vend1 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp1 Vend2 Bricka Bracka Firecracker Bugs Bunny, Bugs Bunny 0 Z-US$
Comp2 Vend3 Help Richard Starr Bingo Night 0 Z-US$
Comp1 Vend2 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp2 Vend3 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
Comp2 Vend4 Events - Internal Meetings Events - Internal Meetings 0 Z-US$
*/

Loop Through SQL Server Instance ?

Hi,
Is it possible to loop through dynamically through all sql instances in a particular environment ?
Thanking you in advance.
Regards,
RanjitHi,

Is it possible to loop through dynamically through all sql instances in a particular environment ?

So you dynamically finding all SQL Servers and the connecting through (insert ADO or DMO here)? Or do you mean looping through a list of servers provided by another source.

The former can be a bit tricky; lots of ways to "hide" a SQL Instance (google SQL Recon). The latter should be relatively trivial.

Regards,

hmscott|||Hi hmscott .. Yup ..I wanted the former ..
Thanks so much for the sql recon .. It did rack up my grey cells to dig further .. It seems they will be coming out with a command line version in the near future ..

Just wondering about "lots of ways to "hide" a SQL Instance" .. the one I can think of being a newbie is change the default port of sql server ..any other ? ..

Thanks,
Ranjit.

looooong backup differential

I am making backup of several data bases to tape, Sunday run backup full and the particular days backup differential, the problem is that backup differential delays more than backup full, I must mention that I am using the option nounload.:confused:Originally posted by lvigo
I am making backup of several data bases to tape, Sunday run backup full and the particular days backup differential, the problem is that backup differential delays more than backup full, I must mention that I am using the option nounload.:confused:

Can't you dump to disk, then copy the file to tape?|||No, i don't have disk for thaht, but what is the technical solution ??|||Originally posted by lvigo
No, i don't have disk for thaht, but what is the technical solution ??

You have to have a hard drive...is it raid? How big is the hard drive?

Are you using the gui to set up backups or using t-sql?

Also what version of sql server?|||I don't have disk space for make a backup. The version is 7. with sp 3|||Originally posted by lvigo
I don't have disk space for make a backup. The version is 7. with sp 3

you can use UNC filenames to make the backup...but going across the network mught be slower...

But if you're running out of room on tape...then you might have no choice.

Are you overwriting the nightly dump, or appending?|||I need make the backup to tape, full backup is executed the weekend and on the same tape we append the differential backup of the other days.|||Originally posted by lvigo
I need make the backup to tape, full backup is executed the weekend and on the same tape we append the differential backup of the other days.

Are you using 1 tape? How full is it? Can you swap out the Sunday tape with the full back ups and put a new 1 in for differential...if the get to big can you swap them out every night...

just guessing...need to know what you're doing...

looking up client side connection info

I am trying to create an audit trail using triggers on a particular table
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.
Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string that
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve
|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Jiho Han wrote:
that[vbcol=seagreen]
that[vbcol=seagreen]
but I
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve

looking up client side connection info

I am trying to create an audit trail using triggers on a particular table
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string that
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Jiho Han wrote:
> > the very least. Is it possible to retrieve a OLE DB connection string
that
> > the client used to connect to the server in the first place? I know
that
> > the connection string wouldn't really be of any interest to SQL server
but I
> > was hoping that it would forward the info anyway.
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve

looking up client side connection info

I am trying to create an audit trail using triggers on a particular table
for inserts, updates, and deletes.
This would be a simple task if the users were logging in under different
names but there is a single login that gets used by all connections.
Luckily the application has its own user table and the table I am creating
the audit for has a column that contains the application user that modified
the row. This is fine for inserts and updates but a problem for deletes.
So before I go looking for alternatives - a stored procedure for all access,
etc. - I wanted to know whether there is a way to find the client side
connection info by way of @.@.SPID somehow. A client machine name or IP at
the very least. Is it possible to retrieve a OLE DB connection string that
the client used to connect to the server in the first place? I know that
the connection string wouldn't really be of any interest to SQL server but I
was hoping that it would forward the info anyway.
Thanks in advance.Jiho Han wrote:
> the very least. Is it possible to retrieve a OLE DB connection string tha
t
> the client used to connect to the server in the first place? I know that
> the connection string wouldn't really be of any interest to SQL server but
I
> was hoping that it would forward the info anyway.
I have searched high and low for such information and could not find any
reliable source for it.
What you can use is SET CONTEXT_INFO on the client side to have it
deposit up to 128 bytes of any info you like. Triggers can then read
this packet of info as follows:
SELECT context_info
FROM master.dbo.sysprocesses
WHERE spid = @.@.spid
Steve|||Thanks for the info.
I don't really control the client, well, not all of them anyway. But that
may come in handy some day...
Jiho
"Steve Troxell" <steve_troxell@.hotmail-nospamforme.com> wrote in message
news:emJfEFrGFHA.3876@.TK2MSFTNGP14.phx.gbl...
> Jiho Han wrote:
that[vbcol=seagreen]
that[vbcol=seagreen]
but I[vbcol=seagreen]
> I have searched high and low for such information and could not find any
> reliable source for it.
> What you can use is SET CONTEXT_INFO on the client side to have it
> deposit up to 128 bytes of any info you like. Triggers can then read
> this packet of info as follows:
> SELECT context_info
> FROM master.dbo.sysprocesses
> WHERE spid = @.@.spid
>
> Steve

Looking into all the tables of a schema

Hi everybody,
Is there any method using which I can search some numbers in all
numeric fields in all the tables of a particular schema ?
This is urgent. Thnaks in advance.
Ratan wrote:
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema ?
>
> This is urgent. Thnaks in advance.
>
Possible? YEs... easy? Not so much... basically you'd have to create a
list of all tables in thedata base, loop through it, then look at all of
the columns in that table, find ones that match one type (you'd have to
know what that is in advance), then build a SQL statement that would
look at those fields for your value and return the results...
-ca
|||Ratan
I think Aaron wrote this script which will give you an idea
CREATE PROCEDURE [dbo].[listTableColumns]
@.table SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tid INT,
@.is VARCHAR(32),
@.ii VARCHAR(32),
@.ic VARCHAR(32)
SELECT
@.tid = OBJECT_ID(@.table),
@.is = CAST(IDENT_SEED(@.table) AS VARCHAR(32)),
@.ii = CAST(IDENT_INCR(@.table) AS VARCHAR(32)),
@.ic = CAST(IDENT_CURRENT(@.table) AS VARCHAR(32))
CREATE TABLE #pkeys
(
t_q SYSNAME, t_o SYSNAME, t_n SYSNAME,
cn SYSNAME, ks INT, pn SYSNAME
)
INSERT #pkeys EXEC sp_pkeys @.table
CREATE TABLE #sc
(
cn SYSNAME, formula NVARCHAR(2048)
)
INSERT #sc SELECT
cl.name, sc.text
FROM syscolumns cl
LEFT JOIN syscomments sc
ON cl.id = sc.id AND sc.number = cl.colid
WHERE cl.id = @.tid
SELECT
[COLUMN NAME] = i_s.column_name,
[DATA TYPE] = UPPER(DATA_TYPE)
+ CASE WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN
'(' + CAST(NUMERIC_PRECISION AS VARCHAR)
+ ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
ELSE '' END
+ CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN
' IDENTITY (' + @.is + ', ' + @.ii + ')' ELSE '' END
+ CASE RIGHT(DATA_TYPE, 4) WHEN 'CHAR' THEN
' ('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' ELSE '' END
+ CASE IS_NULLABLE WHEN 'No' THEN ' NOT ' ELSE ' ' END
+ 'NULL' + COALESCE(' DEFAULT ' + SUBSTRING(COLUMN_DEFAULT,
2, LEN(COLUMN_DEFAULT)-2), ''),
[CURRENT IDENTITY] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN @.ic ELSE '' END,
[FORMULA] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsComputed')
WHEN 1 THEN (SELECT SUBSTRING(formula, 2, len(formula)-2)
FROM #sc WHERE cn=i_s.column_name)
ELSE '' END,
[PRIMARY KEY?] = CASE WHEN pk.cn IS NOT NULL THEN 'Yes' ELSE '' END,
[COLUMN DESCRIPTION] = COALESCE(s.value, '')
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
#pkeys pk
ON
pk.cn = i_s.column_name
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
i_s.TABLE_NAME = @.table
ORDER BY
i_s.ORDINAL_POSITION
DROP TABLE #pkeys
DROP TABLE #sc
END
GO
Sample usage:
CREATE TABLE [dbo].[fakeTable]
(
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[dt] [smalldatetime] NOT NULL DEFAULT (getdate()),
[body] [varchar] (255) NOT NULL DEFAULT (''),
[email] [varchar] (255) NULL,
[y] AS (datepart(year,[dt]))
)
GO
EXEC listTableColumns 'faketable'
DROP TABLE fakeTable
"Ratan" <ratan.nitrkl@.gmail.com> wrote in message
news:b1d3b4e4-f3b1-42b3-a501-4156c5dd4857@.e10g2000prf.googlegroups.com...
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema ?
>
> This is urgent. Thnaks in advance.
>

Looking into all the tables of a schema

Hi everybody,
Is there any method using which I can search some numbers in all
numeric fields in all the tables of a particular schema '
This is urgent. Thnaks in advance.Ratan wrote:
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema '
>
> This is urgent. Thnaks in advance.
>
Possible? YEs... easy? Not so much... basically you'd have to create a
list of all tables in thedata base, loop through it, then look at all of
the columns in that table, find ones that match one type (you'd have to
know what that is in advance), then build a SQL statement that would
look at those fields for your value and return the results...
-ca|||Ratan
I think Aaron wrote this script which will give you an idea
CREATE PROCEDURE [dbo].[listTableColumns]
@.table SYSNAME
AS
BEGIN
SET NOCOUNT ON
DECLARE @.tid INT,
@.is VARCHAR(32),
@.ii VARCHAR(32),
@.ic VARCHAR(32)
SELECT
@.tid = OBJECT_ID(@.table),
@.is = CAST(IDENT_SEED(@.table) AS VARCHAR(32)),
@.ii = CAST(IDENT_INCR(@.table) AS VARCHAR(32)),
@.ic = CAST(IDENT_CURRENT(@.table) AS VARCHAR(32))
CREATE TABLE #pkeys
(
t_q SYSNAME, t_o SYSNAME, t_n SYSNAME,
cn SYSNAME, ks INT, pn SYSNAME
)
INSERT #pkeys EXEC sp_pkeys @.table
CREATE TABLE #sc
(
cn SYSNAME, formula NVARCHAR(2048)
)
INSERT #sc SELECT
cl.name, sc.text
FROM syscolumns cl
LEFT JOIN syscomments sc
ON cl.id = sc.id AND sc.number = cl.colid
WHERE cl.id = @.tid
SELECT
[COLUMN NAME] = i_s.column_name,
[DATA TYPE] = UPPER(DATA_TYPE)
+ CASE WHEN DATA_TYPE IN ('NUMERIC', 'DECIMAL') THEN
'(' + CAST(NUMERIC_PRECISION AS VARCHAR)
+ ', ' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
ELSE '' END
+ CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN
' IDENTITY (' + @.is + ', ' + @.ii + ')' ELSE '' END
+ CASE RIGHT(DATA_TYPE, 4) WHEN 'CHAR' THEN
' ('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+')' ELSE '' END
+ CASE IS_NULLABLE WHEN 'No' THEN ' NOT ' ELSE ' ' END
+ 'NULL' + COALESCE(' DEFAULT ' + SUBSTRING(COLUMN_DEFAULT,
2, LEN(COLUMN_DEFAULT)-2), ''),
[CURRENT IDENTITY] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsIdentity')
WHEN 1 THEN @.ic ELSE '' END,
[FORMULA] = CASE COLUMNPROPERTY(@.tid, COLUMN_NAME, 'IsComputed')
WHEN 1 THEN (SELECT SUBSTRING(formula, 2, len(formula)-2)
FROM #sc WHERE cn=i_s.column_name)
ELSE '' END,
[PRIMARY KEY?] = CASE WHEN pk.cn IS NOT NULL THEN 'Yes' ELSE '' END,
[COLUMN DESCRIPTION] = COALESCE(s.value, '')
FROM
INFORMATION_SCHEMA.COLUMNS i_s
LEFT OUTER JOIN
#pkeys pk
ON
pk.cn = i_s.column_name
LEFT OUTER JOIN
sysproperties s
ON
s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)
AND s.smallid = i_s.ORDINAL_POSITION
AND s.name = 'MS_Description'
WHERE
i_s.TABLE_NAME = @.table
ORDER BY
i_s.ORDINAL_POSITION
DROP TABLE #pkeys
DROP TABLE #sc
END
GO
Sample usage:
CREATE TABLE [dbo].[fakeTable]
(
[id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[dt] [smalldatetime] NOT NULL DEFAULT (getdate()),
[body] [varchar] (255) NOT NULL DEFAULT (''),
[email] [varchar] (255) NULL,
[y] AS (datepart(year,[dt]))
)
GO
EXEC listTableColumns 'faketable'
DROP TABLE fakeTable
"Ratan" <ratan.nitrkl@.gmail.com> wrote in message
news:b1d3b4e4-f3b1-42b3-a501-4156c5dd4857@.e10g2000prf.googlegroups.com...
> Hi everybody,
> Is there any method using which I can search some numbers in all
> numeric fields in all the tables of a particular schema '
>
> This is urgent. Thnaks in advance.
>

Looking For Technique To Default Execute Permission on any new SPr

Hi,
I'm looking for a way, for when any procedure is created or updated in a
user database, that a particular DB role be granted execute permissions on
the proc.
This role is a: datareader,datawriter, and ddladmin, but can not be
db_owner. I know that I can grant execute en-masse using a variety of T-SQL
techniques, but I would have to do this periodically. Also, adding a GRANT
on the tail-end of the CREATE PROCEDURE would not work for me.
I was beginning to create a trigger on sysobjects for CREATE,UPDATE when the
object type is 'P' (for procedure) to then issue the GRANT, but soon realize
d
that triggers on system tables are not permitted!
Does anyone have any suggestions?
Thanks very much,
SteveTIf you are on SQL Server 2005, you could grant execute
permissions on the schema to the role -
GRANT EXECUTE ON SCHEMA::YourSchema TO YourRole
Otherwise, not much you can do if you can't add a grant
statement to the create procedure script.
-Sue
On Thu, 2 Nov 2006 13:49:02 -0800, SteveT
<SteveT@.discussions.microsoft.com> wrote:

>Hi,
>I'm looking for a way, for when any procedure is created or updated in a
>user database, that a particular DB role be granted execute permissions on
>the proc.
>This role is a: datareader,datawriter, and ddladmin, but can not be
>db_owner. I know that I can grant execute en-masse using a variety of T-SQ
L
>techniques, but I would have to do this periodically. Also, adding a GRANT
>on the tail-end of the CREATE PROCEDURE would not work for me.
>I was beginning to create a trigger on sysobjects for CREATE,UPDATE when th
e
>object type is 'P' (for procedure) to then issue the GRANT, but soon realiz
ed
>that triggers on system tables are not permitted!
>Does anyone have any suggestions?

Looking for help with an SQL query.

well, i want to select rows by date from a file. but I want
in particular one sum of values from the rows that fall WITHIN
a supplied date range, and a second sum of values from the
rows that have dates FOR ALL TIME UP TO the second date in the date range.

the former, by itself, might be:

SELECT id, value RangedValue
FROM myFile
WHERE date >= [lower date range value]
AND date <= [higher date range value]

and the latter, by itself, might be:

SELECT id, value AllTimeValue
FROM myFile
WHERE date <= [higher date range value]

but I need to grab the two separate sums (RangedValue and AllTimeValue)
using one SQL statement.

I'm thinking that the UNION might work, but my preliminary results are
taking a huge amount of time, and apparently smegging up the (rather
stupid, external) report generator to boot.
If you like the UNION idea, please give me an example.

I should mention that the report generator at very least can do the
(summing) part. I could do the summing at either the SQL level or the
report level. I should also mention that although I only talk about the
one file here (myFile), in fact I need to join to and pull values from
its "parent" file, although I don't think that that should change my
fundamental problem.

Any ideas?

Cheers in advance!

-GlennYou could try something like this:
Select Id
, Sum(Value) Alltimevalue
, Sum(Case
When Date >= [Lower Date Range Value] Then Value
Else 0 End) As Rangedvalue
From Myfile
Where Date <= [Higher Date Range Value];
;)