Loopback Adapter Hides Instance Names

I have laptops that require the loopback adapter when they are not
connected. However when they connect back to the network the networked
servers display just the computer name not the / instancename. Any
ideas? It only happens on network instances. The local instances are
correct. This happens in Enterprise Manager, Access connection tool
and using SQL-DMO.
Gary Shane LimIn message <ogfj31dfgdq76mvs61hj3aofv0k6vpg1j0@.4ax.com>, Shane Lim
<gslim@.blizzardice.com> writes
>I have laptops that require the loopback adapter when they are not
>connected. However when they connect back to the network the networked
>servers display just the computer name not the / instancename. Any
>ideas? It only happens on network instances. The local instances are
>correct. This happens in Enterprise Manager, Access connection tool
>and using SQL-DMO.
>Gary Shane Lim
Have you tried Bridging the Loopback Adapter to your main Lan Adapter.
This way your network settings remain the same whether on the network or
not.
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Loopback Adapter Hides Instance Names

I have laptops that require the loopback adapter when they are not
connected. However when they connect back to the network the networked
servers display just the computer name not the / instancename. Any
ideas? It only happens on network instances. The local instances are
correct. This happens in Enterprise Manager, Access connection tool
and using SQL-DMO.
Gary Shane Lim
In message <ogfj31dfgdq76mvs61hj3aofv0k6vpg1j0@.4ax.com>, Shane Lim
<gslim@.blizzardice.com> writes
>I have laptops that require the loopback adapter when they are not
>connected. However when they connect back to the network the networked
>servers display just the computer name not the / instancename. Any
>ideas? It only happens on network instances. The local instances are
>correct. This happens in Enterprise Manager, Access connection tool
>and using SQL-DMO.
>Gary Shane Lim
Have you tried Bridging the Loopback Adapter to your main Lan Adapter.
This way your network settings remain the same whether on the network or
not.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
sql

Loopback Adapter Hides Instance Names

I have laptops that require the loopback adapter when they are not
connected. However when they connect back to the network the networked
servers display just the computer name not the / instancename. Any
ideas? It only happens on network instances. The local instances are
correct. This happens in Enterprise Manager, Access connection tool
and using SQL-DMO.
Gary Shane LimIn message <ogfj31dfgdq76mvs61hj3aofv0k6vpg1j0@.4ax.com>, Shane Lim
<gslim@.blizzardice.com> writes
>I have laptops that require the loopback adapter when they are not
>connected. However when they connect back to the network the networked
>servers display just the computer name not the / instancename. Any
>ideas? It only happens on network instances. The local instances are
>correct. This happens in Enterprise Manager, Access connection tool
>and using SQL-DMO.
>Gary Shane Lim
Have you tried Bridging the Loopback Adapter to your main Lan Adapter.
This way your network settings remain the same whether on the network or
not.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

loop to run 'Create Trigger' script?

I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@.TriggerName' error.

if exists (select * from sysobjects where id =
object_id (N'dbo.tgUsersAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgUsersAudit
go

CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = tblUsers

..................from here the code is the same for all

You can add to this script.

set nocount on
declare @.cursor cursor,
@.triggerName sysname

set @.cursor = cursor for (select name
from sys.triggers)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.triggerName
if @.@.fetch_status <> 0
break

select @.triggername

end

If you are using 2000, use:

select name
from sysobjects
where xtype = 'tr'

for the select statement...

|||

The triggers don't yet exist, so they won't be in sysobjects.

I tried putting the 'create trigger' code in a sp, with table name and trigger name as parameters but get a syntax error when using a variable.

|||

Just to clarify based on a comment from a related thread-

"On a related note, this is really not a very good approach. You should create a different trigger for every table (I like the approach of creating the triggers automatically using the loop, but not like this, as it will be problematic and slow)."

I am creating different triggers for each table, but they are created by a common script that dynamically determines the column names, etc. I would like a loop to run the create script for each table, as opposed to editing the table name and trigger name and running the script manually for each table.

|||

Sorry, I misunderstood :) Will this work for you:

set nocount on
declare @.cursor cursor,
@.tableName sysname

set @.cursor = cursor for (select name
from sys.tables)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.tableName
if @.@.fetch_status <> 0
break

select 'if exists (select * from sysobjects where id =
object_id (N''dbo.tg' + @.tableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)
drop trigger dbo.tg' + @.tableName + 'Audit
go

CREATE TRIGGER tg' + @.tableName + 'Audit on ' + @.tableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.tableName + '''

<more code here>
'

end

|||Yes, this is along the lines of what I want to do- however... my code is just too ugly and I'm struggling with getting the string all to concatenate correctly (can't get the quotes right and don't have time to play with it right now). I guess for now I'll just have to run it separately for each table- boo hoo!|||

Though you have said you want to do it one table by one table, I am still put this code here incase it can save you some energy.

select 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + name + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + name + 'Audit
go'
from sys.tables

SELECT '
CREATE TRIGGER tg' + name + 'Audit on ' + name + ' FOR insert, update, delete
AS
DECLARE name varchar(128)
SET name = ''' + name + '''
' + char(13) +
'<more code here>
' + char(13) +
char(13)
from sys.tables

This will generate two batch. First one clean existing trigger. The scaond one has the head part of each trigger. Replace "<more code here>" with your common code. That will give you the excutable batch to do the job.

|||

I really want to get it to work, but am short of time and just can't seem to get it right.

I would generate the table names from a populated cursor vs from sysobjects because not all of the tables in the db should be audited by this trigger, so I was thinking something like the following.

set nocount on
declare @.cursor cursor
declare @.tblTableNames table (TableName varchar(30))

insert into @.tblTableNames (TableName) values ('tblCompanies')
insert into @.tblTableNames (TableName) values ('tblDepartments')
insert into @.tblTableNames (TableName) values ('tblManagementLevels').......

set @.cursor = cursor for (select TableName
from @.tblTableNames)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.tableName

if @.@.fetch_status <> 0
break

<create script code here.....>

end

close cursor
deallocate cursor

Also, something I am having trouble with is that my <create script code here.....> is full of somewhat complex code, embedded strings, etc. Posted below for your reading enjoyment...

if exists (select * from sysobjects where id =
object_id (N'dbo.tgDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgDepartmentsAudit
go

CREATE TRIGGER tgDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'

-

DECLARE @.fieldname varchar(128),
@.pkJoinClause varchar(1000),
@.sql nvarchar(2000),
@.UpdateDate varchar(21),
@.UserName varchar(128),
@.TriggerType nchar(1),
@.rowId int,
@.maxRowId int,
@.str1 varchar(100),
@.str2 varchar(100)

SET @.UserName = SYSTEM_USER
SET @.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

--detemine type of trigger
IF exists(select * FROM inserted) AND exists(select * from deleted)
SET @.TriggerType = 'U'
else
IF exists(select * FROM inserted)
SET @.TriggerType = 'I'
ELSE
SET @.TriggerType = 'D'

--get all Column names for table
SELECT c1.COLUMN_NAME as colName, c1.ORDINAL_POSITION as RowId into #tblFieldNames
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c1 ON t1.TABLE_NAME = c1.TABLE_NAME
WHERE t1.TABLE_NAME = @.tableName

-- Get PRIMARY KEY columns
select c.COLUMN_NAME as colName INTO #primaryKeyFields
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on (c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
WHERE pk.TABLE_NAME = @.tableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'

--Create Join clause for primary key field(s)
SELECT @.pkJoinClause = coalesce(@.pkJoinClause + ' and', ' on') + ' i.' + PKF.colName + ' = d.' + PKF.colName
FROM #primaryKeyFields PKF

--Throw error if no primary key
IF @.pkJoinClause IS NULL
BEGIN
raiserror('no PK ON TABLE %s', 16, -1, @.TableName)
RETURN
END

-
-- the 'inserted' and 'deleted' tables have limitations, dump to temp tables for greater control
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

--get number of columns
select
@.rowId = min(RowId),
@.MaxRowId = max(RowId)
from #tblFieldNames

-- Loop through fields and build Sql string
while @.RowId <= @.MaxRowId
BEGIN
SELECT @.fieldname = colName FROM #tblFieldNames WHERE RowId = @.RowId

SELECT @.sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @.sql = @.sql + ' select ''' + @.TableName + ''''
SELECT @.sql = @.sql + ',''' + @.TriggerType + ''''
SELECT @.sql = @.sql + ',''' + @.fieldname + ''''
SELECT @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',''' + @.UpdateDate + ''''
SELECT @.sql = @.sql + ', 1'
SELECT @.sql = @.sql + ',''' + @.UserName + ''''
SELECT @.sql = @.sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @.sql = @.sql + @.pkJoinClause
SELECT @.sql = @.sql + ' WHERE (''' + @.TriggerType + ''' = ''I'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''D'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''U'' AND '
SELECT @.sql = @.sql + '((i.' + @.fieldname + ' <> d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ' OR (''' + @.fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NULL AND d.' + @.fieldname + ' is NOT null)'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NOT NULL AND d.' + @.fieldname + ' is null)))'

EXEC (@.sql)
set @.RowId = @.RowId + 1
END

Drop Table #ins
Drop Table #del
Drop Table #tblFieldNames
Drop Table #primaryKeyFields

go

Incidentally, if you're interested in what the actual audit table looks like, run this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAuditAdmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAuditAdmin]
GO

CREATE TABLE [dbo].[tblAuditAdmin] (
[UpdateDate] [datetime] NOT NULL ,
[TableAltered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateNumber] [int] NULL ,
[Action] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblAuditAdmin] ADD
CONSTRAINT [PK_tblAuditAdmin] PRIMARY KEY CLUSTERED
(
[UpdateDate],
[TableAltered],
[FieldName]
) ON [PRIMARY]
GO

|||

Sorry. Forgot to tell you how to use the code I posted yesterday.

Change the output of Query Analyzer to 'text'. excute the code. Copy the output to edit portion of Query Analyzer. Then replace "<more code here>" with the common part of your code. Now you will have the script.

This makeshift works well, If you don't want to spend too much time.

|||

Oh, I get it! This worked great, thanks to Aego!

For what it's worth, the following is what I ended up with.

-- To create this trigger for each table, insert the table name into the 2nd line of code (set @.TableName = 'TblCompanies'),
-- then highlight and execute the "first block of code" (inside dashed lines). Then paste the output from this into the
-- second block. Select that code all the way to the bottom and execute to create the trigger for that table. Do this
-- for each table name that you want the trigger created for. Alternatively, you can just set the table name and trigger
-- name manually for each table in the second block of code.

--First block of code...
declare @.TableName varchar(50)
set @.TableName = 'tblDepartments'

declare @.string varchar(5000)
set @.string = 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + @.TableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + @.TableName + 'Audit ' + + char(13) +
'go ' + char(13) +

'CREATE TRIGGER tg' + @.TableName + 'Audit on ' + @.TableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.TableName + '''
' + char(13)

print @.string

--Second block of code...
--replace code in this block with the output from above block and execute from here down
if exists (select * from sysobjects where id = object_id (N'dbo.tgtblDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgtblDepartmentsAudit
go
CREATE TRIGGER tgtblDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'

...... the rest of my code.......

loop to run 'Create Trigger' script?

I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@.TriggerName' error.

if exists (select * from sysobjects where id =
object_id (N'dbo.tgUsersAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgUsersAudit
go

CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = tblUsers

..................from here the code is the same for all

You can add to this script.

set nocount on
declare @.cursor cursor,
@.triggerName sysname

set @.cursor = cursor for (select name
from sys.triggers)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.triggerName
if @.@.fetch_status <> 0
break

select @.triggername

end

If you are using 2000, use:

select name
from sysobjects
where xtype = 'tr'

for the select statement...

|||

The triggers don't yet exist, so they won't be in sysobjects.

I tried putting the 'create trigger' code in a sp, with table name and trigger name as parameters but get a syntax error when using a variable.

|||

Just to clarify based on a comment from a related thread-

"On a related note, this is really not a very good approach. You should create a different trigger for every table (I like the approach of creating the triggers automatically using the loop, but not like this, as it will be problematic and slow)."

I am creating different triggers for each table, but they are created by a common script that dynamically determines the column names, etc. I would like a loop to run the create script for each table, as opposed to editing the table name and trigger name and running the script manually for each table.

|||

Sorry, I misunderstood :) Will this work for you:

set nocount on
declare @.cursor cursor,
@.tableName sysname

set @.cursor = cursor for (select name
from sys.tables)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.tableName
if @.@.fetch_status <> 0
break

select 'if exists (select * from sysobjects where id =
object_id (N''dbo.tg' + @.tableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)
drop trigger dbo.tg' + @.tableName + 'Audit
go

CREATE TRIGGER tg' + @.tableName + 'Audit on ' + @.tableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.tableName + '''

<more code here>
'

end

|||Yes, this is along the lines of what I want to do- however... my code is just too ugly and I'm struggling with getting the string all to concatenate correctly (can't get the quotes right and don't have time to play with it right now). I guess for now I'll just have to run it separately for each table- boo hoo!|||

Though you have said you want to do it one table by one table, I am still put this code here incase it can save you some energy.

select 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + name + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + name + 'Audit
go'
from sys.tables

SELECT '
CREATE TRIGGER tg' + name + 'Audit on ' + name + ' FOR insert, update, delete
AS
DECLARE name varchar(128)
SET name = ''' + name + '''
' + char(13) +
'<more code here>
' + char(13) +
char(13)
from sys.tables

This will generate two batch. First one clean existing trigger. The scaond one has the head part of each trigger. Replace "<more code here>" with your common code. That will give you the excutable batch to do the job.

|||

I really want to get it to work, but am short of time and just can't seem to get it right.

I would generate the table names from a populated cursor vs from sysobjects because not all of the tables in the db should be audited by this trigger, so I was thinking something like the following.

set nocount on
declare @.cursor cursor
declare @.tblTableNames table (TableName varchar(30))

insert into @.tblTableNames (TableName) values ('tblCompanies')
insert into @.tblTableNames (TableName) values ('tblDepartments')
insert into @.tblTableNames (TableName) values ('tblManagementLevels').......

set @.cursor = cursor for (select TableName
from @.tblTableNames)
open @.cursor

while (1=1)
begin
fetch next from @.cursor into @.tableName

if @.@.fetch_status <> 0
break

<create script code here.....>

end

close cursor
deallocate cursor

Also, something I am having trouble with is that my <create script code here.....> is full of somewhat complex code, embedded strings, etc. Posted below for your reading enjoyment...

if exists (select * from sysobjects where id =
object_id (N'dbo.tgDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgDepartmentsAudit
go

CREATE TRIGGER tgDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'

-

DECLARE @.fieldname varchar(128),
@.pkJoinClause varchar(1000),
@.sql nvarchar(2000),
@.UpdateDate varchar(21),
@.UserName varchar(128),
@.TriggerType nchar(1),
@.rowId int,
@.maxRowId int,
@.str1 varchar(100),
@.str2 varchar(100)

SET @.UserName = SYSTEM_USER
SET @.UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(12), getdate(), 114)

--detemine type of trigger
IF exists(select * FROM inserted) AND exists(select * from deleted)
SET @.TriggerType = 'U'
else
IF exists(select * FROM inserted)
SET @.TriggerType = 'I'
ELSE
SET @.TriggerType = 'D'

--get all Column names for table
SELECT c1.COLUMN_NAME as colName, c1.ORDINAL_POSITION as RowId into #tblFieldNames
FROM INFORMATION_SCHEMA.TABLES t1
INNER JOIN INFORMATION_SCHEMA.COLUMNS c1 ON t1.TABLE_NAME = c1.TABLE_NAME
WHERE t1.TABLE_NAME = @.tableName

-- Get PRIMARY KEY columns
select c.COLUMN_NAME as colName INTO #primaryKeyFields
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE c on (c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME)
WHERE pk.TABLE_NAME = @.tableName
AND CONSTRAINT_TYPE = 'PRIMARY KEY'

--Create Join clause for primary key field(s)
SELECT @.pkJoinClause = coalesce(@.pkJoinClause + ' and', ' on') + ' i.' + PKF.colName + ' = d.' + PKF.colName
FROM #primaryKeyFields PKF

--Throw error if no primary key
IF @.pkJoinClause IS NULL
BEGIN
raiserror('no PK ON TABLE %s', 16, -1, @.TableName)
RETURN
END

-
-- the 'inserted' and 'deleted' tables have limitations, dump to temp tables for greater control
SELECT * INTO #ins FROM inserted
SELECT * INTO #del FROM deleted

--get number of columns
select
@.rowId = min(RowId),
@.MaxRowId = max(RowId)
from #tblFieldNames

-- Loop through fields and build Sql string
while @.RowId <= @.MaxRowId
BEGIN
SELECT @.fieldname = colName FROM #tblFieldNames WHERE RowId = @.RowId

SELECT @.sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @.sql = @.sql + ' select ''' + @.TableName + ''''
SELECT @.sql = @.sql + ',''' + @.TriggerType + ''''
SELECT @.sql = @.sql + ',''' + @.fieldname + ''''
SELECT @.sql = @.sql + ',convert(varchar(1000),d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',convert(varchar(1000),i.' + @.fieldname + ')'
SELECT @.sql = @.sql + ',''' + @.UpdateDate + ''''
SELECT @.sql = @.sql + ', 1'
SELECT @.sql = @.sql + ',''' + @.UserName + ''''
SELECT @.sql = @.sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @.sql = @.sql + @.pkJoinClause
SELECT @.sql = @.sql + ' WHERE (''' + @.TriggerType + ''' = ''I'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''D'')'
SELECT @.sql = @.sql + ' OR (''' + @.TriggerType + ''' = ''U'' AND '
SELECT @.sql = @.sql + '((i.' + @.fieldname + ' <> d.' + @.fieldname + ')'
SELECT @.sql = @.sql + ' OR (''' + @.fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NULL AND d.' + @.fieldname + ' is NOT null)'
SELECT @.sql = @.sql + ' OR (i.' + @.fieldname + ' IS NOT NULL AND d.' + @.fieldname + ' is null)))'

EXEC (@.sql)
set @.RowId = @.RowId + 1
END

Drop Table #ins
Drop Table #del
Drop Table #tblFieldNames
Drop Table #primaryKeyFields

go

Incidentally, if you're interested in what the actual audit table looks like, run this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblAuditAdmin]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblAuditAdmin]
GO

CREATE TABLE [dbo].[tblAuditAdmin] (
[UpdateDate] [datetime] NOT NULL ,
[TableAltered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FieldName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdateNumber] [int] NULL ,
[Action] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[OldValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NewValue] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblAuditAdmin] ADD
CONSTRAINT [PK_tblAuditAdmin] PRIMARY KEY CLUSTERED
(
[UpdateDate],
[TableAltered],
[FieldName]
) ON [PRIMARY]
GO

|||

Sorry. Forgot to tell you how to use the code I posted yesterday.

Change the output of Query Analyzer to 'text'. excute the code. Copy the output to edit portion of Query Analyzer. Then replace "<more code here>" with the common part of your code. Now you will have the script.

This makeshift works well, If you don't want to spend too much time.

|||

Oh, I get it! This worked great, thanks to Aego!

For what it's worth, the following is what I ended up with.

-- To create this trigger for each table, insert the table name into the 2nd line of code (set @.TableName = 'TblCompanies'),
-- then highlight and execute the "first block of code" (inside dashed lines). Then paste the output from this into the
-- second block. Select that code all the way to the bottom and execute to create the trigger for that table. Do this
-- for each table name that you want the trigger created for. Alternatively, you can just set the table name and trigger
-- name manually for each table in the second block of code.

--First block of code...
declare @.TableName varchar(50)
set @.TableName = 'tblDepartments'

declare @.string varchar(5000)
set @.string = 'if exists (select * from sysobjects where id = object_id (N''dbo.tg' + @.TableName + 'Audit'') and
objectproperty (id, N''IsTrigger'') = 1)' + char(13) +
'drop trigger dbo.tg' + @.TableName + 'Audit ' + + char(13) +
'go ' + char(13) +

'CREATE TRIGGER tg' + @.TableName + 'Audit on ' + @.TableName + ' FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = ''' + @.TableName + '''
' + char(13)

print @.string

--Second block of code...
--replace code in this block with the output from above block and execute from here down
if exists (select * from sysobjects where id = object_id (N'dbo.tgtblDepartmentsAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgtblDepartmentsAudit
go
CREATE TRIGGER tgtblDepartmentsAudit on tblDepartments FOR insert, update, delete
AS
DECLARE @.TableName varchar(128)
SET @.TableName = 'tblDepartments'

...... the rest of my code.......

Loop through tables names to use with update

Hi,

I have a group of tables that need to be updated with all the same colums name. I want to make a SP then pass the name of the table to it. Is this possiable? if so how do I do this

Thanks

Hi
can you try this
sp_msForEachTable @.command1='Update ? set colname = value'

you can also loop thu a cursor against this quey

select Table_Name from Information_Schema.Tables
|||

Please do not use the undocumented stored procedures. They are meant for internal use and any dependency that you take on undocumented SPs/functions is risky for your code. It can be broken anytime due to a change whether it is a service pack or new release. We do not make any guarantees on the interface/behavior of the undocumented SPs.

For this particular problem, you can use a cursor loop using INFORMATION_SCHEMA.TABLES or sys.tables views.

|||That is not the question, the question is how to write a loop that loops through tables, updating their row-definitions. Not just to display table-namessql

loop through few table in sp

Hi,
I have a lot monthly tables, in order to query some data for last few month,
I have to check a few tables. And the table names are determined by today's
date. If I find the record, I don't need to query other tables anymore. how
can I do it in store procedure? ThanksIf the table names are based on the current date, you'll need to use dynamic
SQL for your query.
Like this:
declare @.today datetime
set @.today = getdate()
declare @.qry nvarchar(1000)
set @.qry = 'select * from sometable_' +
replace(convert(nvarchar(10),@.today,120)
,'-','') + ' where col = 1'
sp_executesql @.qry
But of course, if you want to look through a bunch of tables and stop
searching, you might want to consider populating a temporary table with the
tables you want to check (search through sysobjects for them perhaps), and
then grab the top record, delete it out of your temporary table, search
through the table, and if you find the data, drop out of your while loop. If
you run out of tables to check, you're done and you haven't found it.
But if it's a fixed list of tables, you could just write it out in full,
with return statements appropriately placed.
Hope this helps,
Rob
"Jen" wrote:

> Hi,
> I have a lot monthly tables, in order to query some data for last few mont
h,
> I have to check a few tables. And the table names are determined by today'
s
> date. If I find the record, I don't need to query other tables anymore. ho
w
> can I do it in store procedure? Thanks|||Jen
Can you create a view with an UNION ALL clause to combine those tables and
then query the view BETWEEN required dates?
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:26AB2E6F-CED2-4559-B48E-1F8FDF6123EB@.microsoft.com...
> Hi,
> I have a lot monthly tables, in order to query some data for last few
> month,
> I have to check a few tables. And the table names are determined by
> today's
> date. If I find the record, I don't need to query other tables anymore.
> how
> can I do it in store procedure? Thanks|||thanks. I need to query myTable20060523 first, if record found then I am
done; otherwise I need to continue query myTable200604, myTable200603...,
etc. up to 6 tables. how can I loop through these tables? Thanks
"Rob Farley" wrote:
> If the table names are based on the current date, you'll need to use dynam
ic
> SQL for your query.
> Like this:
> declare @.today datetime
> set @.today = getdate()
> declare @.qry nvarchar(1000)
> set @.qry = 'select * from sometable_' +
> replace(convert(nvarchar(10),@.today,120)
,'-','') + ' where col = 1'
> sp_executesql @.qry
>
> But of course, if you want to look through a bunch of tables and stop
> searching, you might want to consider populating a temporary table with th
e
> tables you want to check (search through sysobjects for them perhaps), and
> then grab the top record, delete it out of your temporary table, search
> through the table, and if you find the data, drop out of your while loop.
If
> you run out of tables to check, you're done and you haven't found it.
> But if it's a fixed list of tables, you could just write it out in full,
> with return statements appropriately placed.
> Hope this helps,
> Rob
>
> "Jen" wrote:
>|||This code will update only tables name like cust and update value of id
to 100.
create table cust (id int)
GO
create table cust1 (id int)
GO
insert into cust values(10)
go
insert into cust1 values(10)
go
select * from cust
go
select * from cust1
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname
set @.db = DB_NAME()
Declare @.tabname sysname
set @.tabname= '%cust%'
set @.sql ='select ''update '' + QUOTENAME(table_SCHEMA) + ''.'' +
QUOTENAME(table_NAME) + '' set id = 100 '' FROM
INFORMATION_SCHEMA.tables ' +
'WHERE table_type = ''base table'''
if @.tabname is not null
set @.sql = @.sql + N' AND table_NAME LIKE ''' + @.tabname+ ''''
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
print @.ret
select * from cust
select * from cust1
You can specify name pattern for your table like '%cust%' here in
sample and it will update or do other operation only on that tables.
Regards
Amish Shah|||This code will update only tables name like cust and update value of id
to 100.
create table cust (id int)
GO
create table cust1 (id int)
GO
insert into cust values(10)
go
insert into cust1 values(10)
go
select * from cust
go
select * from cust1
declare @.ret int
declare @.sql nvarchar(4000)
declare @.db sysname
set @.db = DB_NAME()
Declare @.tabname sysname
set @.tabname= '%cust%'
set @.sql ='select ''update '' + QUOTENAME(table_SCHEMA) + ''.'' +
QUOTENAME(table_NAME) + '' set id = 100 '' FROM
INFORMATION_SCHEMA.tables ' +
'WHERE table_type = ''base table'''
if @.tabname is not null
set @.sql = @.sql + N' AND table_NAME LIKE ''' + @.tabname+ ''''
exec @.ret = master.dbo.xp_execresultset @.sql,@.db
print @.ret
select * from cust
select * from cust1
You can specify name pattern for your table like '%cust%' here in
sample and it will update or do other operation only on that tables.
Regards
Amish Shah|||can I create view in the store procedure? Is there any side effect or
performance issue? How about more than one user is executing the same
procedure?Thanks
"Uri Dimant" wrote:

> Jen
> Can you create a view with an UNION ALL clause to combine those tables and
> then query the view BETWEEN required dates?
>
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:26AB2E6F-CED2-4559-B48E-1F8FDF6123EB@.microsoft.com...
>
>|||--Try something like this:
declare @.qry nvarchar(2000)
declare @.found bit
set @.found = 0
declare @.done bit
set @.done = 0
declare @.tablenames table (id int identity(1,1), name varchar(128));
insert into @.tablenames
select top 6 name
from sysobjects
where name like 'mytable%'
order by 1 desc
declare @.tablename varchar(128)
declare @.tableid int
while (@.found = 0 and @.done = 0)
begin
select top 1 @.tablename = name, @.tableid = id
from @.tablenames
order by id
if (@.@.rowcount = 0)
begin
set @.done = 1
end
else --search through the table
begin
delete from @.tablenames where id = @.tableid
set @.qry = 'declare @.misc int; select @.misc = id from ' + @.tablename + '
where somecol = 15' --This won't return a value, but will set @.@.rowcount
exec sp_executesql @.qry
if (@.@.rowcount > 0)
begin
set @.found = 1
end
end
end
-- Look at the values of @.found and @.tablename to see if you found it, and
what table you found it in
if (@.found = 1)
begin
select @.tablename
end
"Jen" wrote:
> thanks. I need to query myTable20060523 first, if record found then I am
> done; otherwise I need to continue query myTable200604, myTable200603...,
> etc. up to 6 tables. how can I loop through these tables? Thanks
> "Rob Farley" wrote:
>sql

loooong columnnames

Hi,

A long time ago I set up a database with *very* descriptive and therefore
long table names. :(
I did the same with the column names. :((
The column names even repeat the table name. :(((
This was done in a period in which I was still using a lot of dynamic sql
:(((( and sql in the code of the app :(((((.
The tables all have the prefix 'tbl' (yes, I know) :((((((
There are even ghastly underscores in it too :(((((((
As a result, I'm starting to develop a nasty case of carpal tunnel syndrom
(aka RSI) :((((((((

For example there are names like:
dbo.tbl_SalesOrderLine.SalesOrderLine_SalesOrder
I have to type them in QA a gazillion times a day
The number of objects in the database has grown well beyond even beginning
to think about renaming them all.

Now my question: Is there an MS SQL client utility out there that has
autocomplete?

Cheers,

Bas"Bas" <nomailplease> wrote in message news:<412e45ab$0$144$e4fe514c@.dreader9.news.xs4all.nl>...
> Hi,
> A long time ago I set up a database with *very* descriptive and therefore
> long table names. :(
> I did the same with the column names. :((
> The column names even repeat the table name. :(((
> This was done in a period in which I was still using a lot of dynamic sql
> :(((( and sql in the code of the app :(((((.
> The tables all have the prefix 'tbl' (yes, I know) :((((((
> There are even ghastly underscores in it too :(((((((
> As a result, I'm starting to develop a nasty case of carpal tunnel syndrom
> (aka RSI) :((((((((
> For example there are names like:
> dbo.tbl_SalesOrderLine.SalesOrderLine_SalesOrder
> I have to type them in QA a gazillion times a day
> The number of objects in the database has grown well beyond even beginning
> to think about renaming them all.
> Now my question: Is there an MS SQL client utility out there that has
> autocomplete?
> Cheers,
> Bas

There's a list of alternative client tools here:

http://www.aspfaq.com/show.asp?id=2442

And of course:

http://www.google.com/search?source...de+autocomplete

Simon

Lookup table structure

I have another lookup table question

so say i have an employee table, and a department table. all the department table will do is store the names of the different departments so how should this be set up using MSSQL

Employee
EmployeeId
FN
LN
DeptName (fk)

Department
DeptId (pk) unique
DeptName unique

*** Or could i do this to make the employee record more meaningful without needing a join. ***

Employee
EmployeeId
FN
LN
DeptName (fk)

Department
DeptId (pk) unique
DeptName unique

*** Or should i just do this and get rid of the Id Field***
Employee
EmployeeId
FN
LN
DeptName (fk)

Department
DeptName (pk) unique

thanks all

I would use DeptID as the FK in Employees -NOT DeptName.

|||+1 I can second that.

Jens K. Suessmeyer.

http://www.sqlserver2005.de