Changed fields

In our applications we have a table named ChgMstr (Change
Log) that logs changes made to each record. It includes
the Field Name, Date & Time, the user making the change,
and the Before and After values of the field.
I am trying to setup an Update trigger that will loop
through the Deleted and Inserted fields, find which fields
have changed, and then either INSERT directly into the
ChgMstr table or call a Stored Procedure to do it.
However, I cannot find a way (even with Dynamic SQL) to
build a SELECT string that can be EXECuted. When I do, I
get "Invalid Object" messages for both Deleted and
Inserted. It appears that once the EXEC statement is sent
that Deleted and Inserted go out of scope.
Anyone got any ideas. I can provide the contents of the
trigger, if needed.Bryan,
The inserted and deleted tables will not be available to a stored
procedure. You could copy their contents into a temporary table for use by
the stored procedure, but it seems to me unlikely that you need a stored
procedure just to insert into your ChgMstr table. Can you do something like
this:
insert into ChgMster
select N'ColumnA', getdate(), suser_sname(), d.ColumnA, i.ColumnA
from inserted i, deleted d
where i.primaryKey = d.primaryKey
union all
select N'ColumnB', getdate(), suser_sname(), d.ColumnB, i.ColumnB
from inserted i, deleted d
where i.primaryKey = d.primaryKey
This assumes that the primary key column is not modified. If it is, unless
you have another unique constraint on columns that don't change, the
question of what the original and final values of that column are is not
discernable from the inserted and deleted tables in some multi-row updates,
such as
update T set
pk = case when 1 then 11 when 4 then 12 when 8 then 13 end
from T
where pk in (1, 4, 8)
Then the inserted table will contain rows with pk 11, 12, and 13, and the
deleted table will contain rows with pk 1, 4, and 8, but there is no way to
say which of 1, 4, and 8 because which of 11, 12, 13 by looking at the
inserted and deleted tables.
SK
"Bryan A. Jackson" <bjackson@.focuscmc.com> wrote in message
news:ae5701c40cee$28616dc0$a001280a@.phx.gbl...
> In our applications we have a table named ChgMstr (Change
> Log) that logs changes made to each record. It includes
> the Field Name, Date & Time, the user making the change,
> and the Before and After values of the field.
> I am trying to setup an Update trigger that will loop
> through the Deleted and Inserted fields, find which fields
> have changed, and then either INSERT directly into the
> ChgMstr table or call a Stored Procedure to do it.
> However, I cannot find a way (even with Dynamic SQL) to
> build a SELECT string that can be EXECuted. When I do, I
> get "Invalid Object" messages for both Deleted and
> Inserted. It appears that once the EXEC statement is sent
> that Deleted and Inserted go out of scope.
> Anyone got any ideas. I can provide the contents of the
> trigger, if needed.|||1. Forgive my ignorance, but what does the 'N' in select
N'Column A' do?
2. Will I have to do a union for each field in the table?
I'm looking for a generic way to do this so the trigger
does not need to be changed each time fields are added or
deleted.
Thanks,
Bryan

>--Original Message--
>Bryan,
> The inserted and deleted tables will not be available
to a stored
>procedure. You could copy their contents into a
temporary table for use by
>the stored procedure, but it seems to me unlikely that
you need a stored
>procedure just to insert into your ChgMstr table. Can
you do something like
>this:
>insert into ChgMster
>select N'ColumnA', getdate(), suser_sname(), d.ColumnA,
i.ColumnA
>from inserted i, deleted d
>where i.primaryKey = d.primaryKey
>union all
>select N'ColumnB', getdate(), suser_sname(), d.ColumnB,
i.ColumnB
>from inserted i, deleted d
>where i.primaryKey = d.primaryKey
>This assumes that the primary key column is not
modified. If it is, unless
>you have another unique constraint on columns that don't
change, the
>question of what the original and final values of that
column are is not
>discernable from the inserted and deleted tables in some
multi-row updates,
>such as
>update T set
> pk = case when 1 then 11 when 4 then 12 when 8 then 13
end
>from T
>where pk in (1, 4, 8)
>Then the inserted table will contain rows with pk 11, 12,
and 13, and the
>deleted table will contain rows with pk 1, 4, and 8, but
there is no way to
>say which of 1, 4, and 8 because which of 11, 12, 13 by
looking at the
>inserted and deleted tables.
>SK
>"Bryan A. Jackson" <bjackson@.focuscmc.com> wrote in
message
>news:ae5701c40cee$28616dc0$a001280a@.phx.gbl...
(Change
fields
I
sent
>
>.
>|||Bryan,
The N just signifies that the string is Unicode. Object names in SQL
Server are in Unicode, but it won't hurt to leave this out if the name
only uses ASCII.
I generally don't think it's a great idea to write generic code that
doesn't "know" the table structure. You could generate the code for
triggers like this automatically with something like this:
select 'insert into ChgMster' oneLine, 0 ORDINAL_POSITION
union all
select 'select
'+q_column_name+',
'+' getdate(),
suser_sname(),
d.'+q_column_name+',
'+' i.'+q_column_name+'
from inserted i, deleted d
where i.primaryKey = d.primaryKey
and i.'+q_column_name+'<>d.'+q_column_name+
case when ORDINAL_POSITION = (
select max(ORDINAL_POSITION)
from Northwind.INFORMATION_SCHEMA.Columns
where TABLE_NAME = 'Orders'
) then '' else '
union all'
end, ORDINAL_POSITION
from (
select quotename(COLUMN_NAME) as q_column_name, ORDINAL_POSITION
FROM Northwind.INFORMATION_SCHEMA.Columns
where TABLE_NAME = 'Orders'
) C
order by ORDINAL_POSITION
There are also third-party products to do this kind of logging.
SK
Bryan A. Jackson wrote:
>1. Forgive my ignorance, but what does the 'N' in select
>N'Column A' do?
>2. Will I have to do a union for each field in the table?
>I'm looking for a generic way to do this so the trigger
>does not need to be changed each time fields are added or
>deleted.
>Thanks,
>Bryan
>
>
>to a stored
>
>temporary table for use by
>
>you need a stored
>
>you do something like
>
>i.ColumnA
>
>i.ColumnB
>
>modified. If it is, unless
>
>change, the
>
>column are is not
>
>multi-row updates,
>
>end
>
>and 13, and the
>
>there is no way to
>
>looking at the
>
>message
>
>(Change
>
>fields
>
>I
>
>sent
>

Change the DATETIME format to DD/MM/YYYY by using CONVERT command.

I have a database table named EMP and I have a column named
DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
YYYY format by using the CONVERT command by SQL query.What is the
solution ?
Thank you.
Amritendu Paul
hi Paul,
amripaul@.gmail.com wrote:
> I have a database table named EMP and I have a column named
> DOB(datetime) .I want to retrieve just the MM/DD/YYYY part by using
> the CONVERT command.And also I need to change the MM/DD/YYYY to DD/MM/
> YYYY format by using the CONVERT command by SQL query.What is the
> solution ?
> Thank you.
> Amritendu Paul
please have a look at CONVERT function in BOL:
SET NOCOUNT ON;
SELECT CONVERT(varchar(10), GETDATE(), 103) AS [my formatted date];
--<--
my formatted date
11/06/2007
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply

Change Named to Default Instance

We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
installation. The SQL 2K install is running under the default instance and
therefore to install the SQL 2005 we will need to run it under a named
instance. After the SQL 2005 has been configured and we are confident the DB
is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
default instance. Can anyone describe the steps need to do this. We want to
avoid having to re-write a lot of connetion strings in client applications
which would require is to re-deploy those apps.
Thanks in advance.
RT
Hi,
To add on to Tibor; you could use the automated upgrade of sql 2000 to sql
2005. steps
1. Take a complete backup of sql 2000 (including system databases)
2. Run the SQL 2005 setup program
3. This will automatically upgrades the existing SQL 2000 to SQL 2005
4. After the completion of the upgrade the existing SQL 2000 will be
upgraded to SQL 2005 with the same server name
5. After this there will not be any connecting string chnages
The only this incase of issues; you may need to install SQL 2000 and restore
all the databases backed up...
Take a look into:-
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Thanks
Hari
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the
> DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005
> the
> default instance. Can anyone describe the steps need to do this. We want
> to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT
|||Sorry to use an existing post but when I try to start a new thread, nothing
happends.
I just reinstalled SQL Server 2000. During the install. I used the default
Server name of GATEWAY_COMPUTE. Now when I try to start SQL Server, the
Server name comes up as GATEWAY_COMPUTE, services is SQL Server. When I
click start I receive error message ' The service did not start due to a
login failure'.
Can someone tell me how to correct this?

Change Named to Default Instance

We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
installation. The SQL 2K install is running under the default instance and
therefore to install the SQL 2005 we will need to run it under a named
instance. After the SQL 2005 has been configured and we are confident the DB
is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
default instance. Can anyone describe the steps need to do this. We want to
avoid having to re-write a lot of connetion strings in client applications
which would require is to re-deploy those apps.
Thanks in advance.
RTYou cannot change instance name, nor from a default to a named instance (or
other way around). One
option is to install 2005 again, as a named instance (after removing 2000).
Then copy the databases
(backup/restore, detach/attach, for instance). But there are extra steps han
dling the data inside
the system databases, though...
Or, handle this on the client machines:
Either change the connection string.
Or, create an alias for the named instance, with only the machine name as th
e alias name.
(Basically, rename it seen from the client's perspective.)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the
DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005 th
e
> default instance. Can anyone describe the steps need to do this. We want t
o
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT|||Hi,
To add on to Tibor; you could use the automated upgrade of sql 2000 to sql
2005. steps
1. Take a complete backup of sql 2000 (including system databases)
2. Run the SQL 2005 setup program
3. This will automatically upgrades the existing SQL 2000 to SQL 2005
4. After the completion of the upgrade the existing SQL 2000 will be
upgraded to SQL 2005 with the same server name
5. After this there will not be any connecting string chnages
The only this incase of issues; you may need to install SQL 2000 and restore
all the databases backed up...
Take a look into:-
http://www.microsoft.com/technet/pr...5/sqlupgrd.mspx
Thanks
Hari
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the
> DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005
> the
> default instance. Can anyone describe the steps need to do this. We want
> to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT

Change Named to Default Instance

We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
installation. The SQL 2K install is running under the default instance and
therefore to install the SQL 2005 we will need to run it under a named
instance. After the SQL 2005 has been configured and we are confident the DB
is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
default instance. Can anyone describe the steps need to do this. We want to
avoid having to re-write a lot of connetion strings in client applications
which would require is to re-deploy those apps.
Thanks in advance.
RTYou cannot change instance name, nor from a default to a named instance (or other way around). One
option is to install 2005 again, as a named instance (after removing 2000). Then copy the databases
(backup/restore, detach/attach, for instance). But there are extra steps handling the data inside
the system databases, though...
Or, handle this on the client machines:
Either change the connection string.
Or, create an alias for the named instance, with only the machine name as the alias name.
(Basically, rename it seen from the client's perspective.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005 the
> default instance. Can anyone describe the steps need to do this. We want to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT|||Hi,
To add on to Tibor; you could use the automated upgrade of sql 2000 to sql
2005. steps
1. Take a complete backup of sql 2000 (including system databases)
2. Run the SQL 2005 setup program
3. This will automatically upgrades the existing SQL 2000 to SQL 2005
4. After the completion of the upgrade the existing SQL 2000 will be
upgraded to SQL 2005 with the same server name
5. After this there will not be any connecting string chnages
The only this incase of issues; you may need to install SQL 2000 and restore
all the databases backed up...
Take a look into:-
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx
Thanks
Hari
"RT" <RT@.discussions.microsoft.com> wrote in message
news:2BD744AB-1A64-4F49-8FE5-9A388A52B72D@.microsoft.com...
> We are looking to install SQL 2005 Standard next to a SQL 2000 Enterprise
> installation. The SQL 2K install is running under the default instance and
> therefore to install the SQL 2005 we will need to run it under a named
> instance. After the SQL 2005 has been configured and we are confident the
> DB
> is how we want it we want to uninstall the SQL 2K and make the SQL 2005
> the
> default instance. Can anyone describe the steps need to do this. We want
> to
> avoid having to re-write a lot of connetion strings in client applications
> which would require is to re-deploy those apps.
> Thanks in advance.
> RT|||Sorry to use an existing post but when I try to start a new thread, nothing
happends.
I just reinstalled SQL Server 2000. During the install. I used the default
Server name of GATEWAY_COMPUTE. Now when I try to start SQL Server, the
Server name comes up as GATEWAY_COMPUTE, services is SQL Server. When I
click start I receive error message ' The service did not start due to a
login failure'.
Can someone tell me how to correct this?