q; loop for insert into

Hello,
I have 10 tables (T1Orj,T2Orj,â?¦T10Orj) and I need to find modified rows from
each table and insert them to T1Bak, T2Bak, â?¦T10Bak. Although original and
bak tables have the common fields , the original tables have more fields than
bak tables, and T1Orj, T2Orj, â?¦ T10Orj tables have different table structures.
I can go head and write a insert into query for each table, I am just
wondering Is there any way I can do this in a loop for all tables?"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> Hello,
> I have 10 tables (T1Orj,T2Orj,.T10Orj) and I need to find modified rows
> from
> each table and insert them to T1Bak, T2Bak, .T10Bak. Although original and
> bak tables have the common fields , the original tables have more fields
> than
> bak tables, and T1Orj, T2Orj, . T10Orj tables have different table
> structures.
> I can go head and write a insert into query for each table, I am just
> wondering Is there any way I can do this in a loop for all tables?
>
Do you mean the Orj tables all have the same columns? Why? It would be a lot
simpler to combine them into one. Duplicating tables is almost always a
mistake.
If they are all different then I don't understand how you expect a loop
would help you.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the reply, some type of loop might help me if I can get column
names and table names created dynamically from a table. Then I will perform
only one insert in my code.
"David Portas" wrote:
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:044F928B-258D-4AC3-BF5B-469B7E692C22@.microsoft.com...
> > Hello,
> > I have 10 tables (T1Orj,T2Orj,.T10Orj) and I need to find modified rows
> > from
> > each table and insert them to T1Bak, T2Bak, .T10Bak. Although original and
> > bak tables have the common fields , the original tables have more fields
> > than
> > bak tables, and T1Orj, T2Orj, . T10Orj tables have different table
> > structures.
> > I can go head and write a insert into query for each table, I am just
> > wondering Is there any way I can do this in a loop for all tables?
> >
> Do you mean the Orj tables all have the same columns? Why? It would be a lot
> simpler to combine them into one. Duplicating tables is almost always a
> mistake.
> If they are all different then I don't understand how you expect a loop
> would help you.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>
>

q; Linked server from SQL2005 to SQL2000

Hi
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server =
N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =
N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server =
> N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
> N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =
> N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>
|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:

> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =
> N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>
>
|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:

q; Linked server from SQL2005 to SQL2000

Hi
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
--
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server = N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server => N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:
> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword => N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> >
> > Hi
> > I use the following SQL statements to create a link server.
> > RemoteServerName
> > is an SQL2000 and I am executing this in another machine which is SQL2005.
> >
> > Though the link server is created successfully, I am not able to se the
> > tables under it so could not query anything.
> >
> > --
> >
> > USE [master]
> > EXEC master.dbo.sp_addlinkedserver @.server => > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation compatible', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'data access', @.optvalue=N'true'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc',@.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'rpc out', @.optvalue=N'false'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'connect timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'collation name', @.optvalue=null
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'query timeout', @.optvalue=N'0'
> > EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName',
> > @.optname=N'use remote collation', @.optvalue=N'true'
> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname => > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself => > N'False',
> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
> >
>
>|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
>> Jim
>> First of all , have you enabled remote connections on SQL Server 2005
>> server?
>> I did just testing on my machine and it works fine.
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
>> @.srvproduct=N'SQL Server'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> compatible', @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
>> access', @.optvalue=N'true'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc
>> out',
>> @.optvalue=N'false'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName',
>> @.optname=N'collation
>> name', @.optvalue=null
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
>> timeout', @.optvalue=N'0'
>> GO
>> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use
>> remote
>> collation', @.optvalue=N'true'
>> GO
>> USE [master]
>> GO
>> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
>> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword
>> =>> N'blblbl'
>> GO
>>
>> --usage
>> select * from ServerName.dtabasename.dbo.tablename
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>> >
>> > Hi
>> > I use the following SQL statements to create a link server.
>> > RemoteServerName
>> > is an SQL2000 and I am executing this in another machine which is
>> > SQL2005.
>> >
>> > Though the link server is created successfully, I am not able to se the
>> > tables under it so could not query anything.
>> >
>> > --
>> >
>> > USE [master]
>> > EXEC master.dbo.sp_addlinkedserver @.server =>> > N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation compatible', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'data access', @.optvalue=N'true'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc',@.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'rpc out', @.optvalue=N'false'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'connect timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'collation name', @.optvalue=null
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'query timeout', @.optvalue=N'0'
>> > EXEC master.dbo.sp_serveroption
>> > @.server=N'RemoteServerName\InstanceName',
>> > @.optname=N'use remote collation', @.optvalue=N'true'
>> > EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =>> > N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =>> > N'False',
>> > @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>> >
>>

q; Linked server from SQL2005 to SQL2000

Hi
I use the following SQL statements to create a link server. RemoteServerName
is an SQL2000 and I am executing this in another machine which is SQL2005.
Though the link server is created successfully, I am not able to se the
tables under it so could not query anything.
USE [master]
EXEC master.dbo.sp_addlinkedserver @.server =
N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
@.optname=N'collation compatible', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
@.optname=N'data access', @.optvalue=N'true'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
@.optname=N'rpc',@.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
@.optname=N'rpc out', @.optvalue=N'false'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
@.optname=N'connect timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
@.optname=N'collation name', @.optvalue=null
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
@.optname=N'query timeout', @.optvalue=N'0'
EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
@.optname=N'use remote collation', @.optvalue=N'true'
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself = N'False',
@.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'Jim
First of all , have you enabled remote connections on SQL Server 2005
server?
I did just testing on my machine and it works fine.
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
@.srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
compatible', @.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
access', @.optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out',
@.optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collation
name', @.optvalue=null
GO
EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
timeout', @.optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remote
collation', @.optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
@.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword =
N'blblbl'
GO
--usage
select * from ServerName.dtabasename.dbo.tablename
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
> Hi
> I use the following SQL statements to create a link server.
> RemoteServerName
> is an SQL2000 and I am executing this in another machine which is SQL2005.
> Though the link server is created successfully, I am not able to se the
> tables under it so could not query anything.
> --
> USE [master]
> EXEC master.dbo.sp_addlinkedserver @.server =
> N'RemoteServerName\InstanceName', @.srvproduct=N'SQL Server'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
> @.optname=N'collation compatible', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
> @.optname=N'data access', @.optvalue=N'true'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
> @.optname=N'rpc',@.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
> @.optname=N'rpc out', @.optvalue=N'false'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
> @.optname=N'connect timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
> @.optname=N'collation name', @.optvalue=null
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
> @.optname=N'query timeout', @.optvalue=N'0'
> EXEC master.dbo.sp_serveroption @.server=N'RemoteServerName\InstanceName'
,
> @.optname=N'use remote collation', @.optvalue=N'true'
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname =
> N'RemoteServerName\InstanceName', @.locallogin = NULL , @.useself =
> N'False',
> @.rmtuser = N'remoteUser', @.rmtpassword = N'remotePassword'
>|||Hi Uri,
How do I enable remote connection in SQL2000?
"Uri Dimant" wrote:

> Jim
> First of all , have you enabled remote connections on SQL Server 2005
> server?
> I did just testing on my machine and it works fine.
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedserver @.server = N'ServerName',
> @.srvproduct=N'SQL Server'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collatio
n
> compatible', @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'data
> access', @.optvalue=N'true'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc',
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'rpc out'
,
> @.optvalue=N'false'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'connect
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'collatio
n
> name', @.optvalue=null
> GO
> EXEC master.dbo.sp_serveroption @.server=N'NT_SQL_4', @.optname=N'query
> timeout', @.optvalue=N'0'
> GO
> EXEC master.dbo.sp_serveroption @.server=N'ServerName', @.optname=N'use remo
te
> collation', @.optvalue=N'true'
> GO
> USE [master]
> GO
> EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname = N'ServerName',
> @.locallogin = NULL , @.useself = N'False', @.rmtuser = N'blb', @.rmtpassword
=
> N'blblbl'
> GO
>
> --usage
> select * from ServerName.dtabasename.dbo.tablename
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:81C56D0D-3727-46C5-889F-6AB39E88CCFA@.microsoft.com...
>
>|||Jim
Co to Surface Area Configuration for Services and Connections , then
navigate to Remote Connection and check Local and Remote Connections
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:CEE13CAB-BD2D-4307-98B4-E5F223D6E30F@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> How do I enable remote connection in SQL2000?
> "Uri Dimant" wrote:
>

q; INSRET/UPDATE trigger

Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there i
s
any workaround.
Thanks,Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:

> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case yo
u
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||On Fri, 29 Dec 2006 10:19:01 -0800, JIM.H. wrote:

>Hi,
>I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
>rollback record in the table?
Hi Jim,
It definitely does not. Run the following code in Sql Server Management
Studio or Query Analyzer to see for yourself:
CREATE TABLE Test
(a int NOT NULL PRIMARY KEY);
go
CREATE TRIGGER TestTrig
ON Test AFTER INSERT
AS
RAISERROR('This is a test', 16, 1);
go
INSERT INTO Test (a)
VALUES (1);
SELECT *
FROM Test;
go
DROP TABLE Test;
go

> It seems it does and I am wondering if there is
>any workaround.
Is it possible that your client automatically requests a rollback upon
seeing the error condition? I know some clients (Access, for instance)
that make a habit of rolling back first and asking questions later.
Hugo Kornelis, SQL Server MVP|||JIM.H. wrote:
> Hi Paul,
> Thanks you very much, no try check, here is the code:
> -- duplicate record check
> IF EXISTS (
> SELECT *
> FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
> WHERE i.tID=@.tID
> )
> Begin
> SET @.ErrorD = 'Dup Record'
> RAISERROR(@.ErrorD, 16, 1)
> Return
> End
>
>
Unfortunately you only posted part of the trigger code but this looks
suspiciously like your code is at fault. I suspect your trigger fails
to operate correctly when multiple rows are updated because you don't
appear to be referencing the INSERTED table properly. That might
explain some unpredictable behaviour.
If you want help to rewrite the trigger we'll need a better spec - read
my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

q; INSRET/UPDATE trigger

Hi,
I user RAISERROR(@.Error, 16, 1) in the INSRET/UPDATE trigger, does this
rollback record in the table? It seems it does and I am wondering if there is
any workaround.
Thanks,
Jim - can you post up the trigger code. I'm wondering if there is a try
catch block and a rollback statement in the catch section? In that case you
could use print/xp_logevent depending on your actual requirement.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks you very much, no try check, here is the code:
-- duplicate record check
IF EXISTS (
SELECT *
FROM myTable i INNER JOIN con c ON i.ch = c.B_ch
WHERE i.tID=@.tID
)
Begin
SET @.ErrorD = 'Dup Record'
RAISERROR(@.ErrorD, 16, 1)
Return
End
"Paul Ibison" wrote:

> Jim - can you post up the trigger code. I'm wondering if there is a try
> catch block and a rollback statement in the catch section? In that case you
> could use print/xp_logevent depending on your actual requirement.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Jim,
the raiserror statement can't be doing the rollback with this severity. In
your trigger there is no reference to the INSERTED table - I'm wondering if
this is this really the trigger code? With the reference to @.tID it looks
more like stored procedure code. Also, please check to see if there are any
other triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

q; how to write this trigger

I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If thi
s is
a success, I need to set T1.Status=’Done’ for the updated records. So th
is
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=’Done’, any example would be greatly appreciated
.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:

>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>Done. If this i
s
>a success, I need to set T1.Status=Done for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=Done, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
..
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
Hugo Kornelis, SQL Server MVP

q; how to write this trigger

I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
also have T2 in Database2 which has the same fields and some extra fields.
Now based on the T1.Type=Insert or Update, I need to perform either update
or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>â'Doneâ'. If this is
a success, I need to set T1.Status=â'Doneâ' for the updated records. So this
should only be updated records.
T1 is a frequently inserted table, so there might be more than one record
coming there at the same time. How should I write my insert trigger and
correctly set T1.Status=â'Doneâ', any example would be greatly appreciated.On Wed, 1 Nov 2006 06:09:02 -0800, JIM.H. wrote:
>I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I
>also have T2 in Database2 which has the same fields and some extra fields.
>Now based on the T1.Type=Insert or Update, I need to perform either update
>or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>?Done?. If this is
>a success, I need to set T1.Status=?Done? for the updated records. So this
>should only be updated records.
>T1 is a frequently inserted table, so there might be more than one record
>coming there at the same time. How should I write my insert trigger and
>correctly set T1.Status=?Done?, any example would be greatly appreciated.
Hi Jim,
CREATE TRIGGER JimsTrigger
ON T1 AFTER INSERT
AS
IF @.@.ROWCOUNT = 0 RETURN;
SET NOCOUNT ON;
INSERT INTO T2 (ID, Type, Status, ...)
SELECT ID, Type, Status, ...
FROM inserted
WHERE Type = 'Insert'
AND Status <> 'Done'
UPDATE T2
SET Type = i.Type,
Status = i.Status,
...
FROM inserted AS i
JOIN T2 ON T2.ID = i.ID
WHERE i.Type = 'Update'
AND i.Status <> 'Done'
UPDATE T1
SET Status = 'Done'
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.Type IN ('Update', 'Insert')
AND i.Status <> 'Done'
AND i.ID = T1.ID)
go
Untested, and you'll probably want to add error handling.
--
Hugo Kornelis, SQL Server MVP

q; How to write this function

How can I write a function that accepts a data as a parameter and if the date
is
1. Last Monday in May or
2. First Monday in September or
3. 4th Tuesday in November
returns true otherwise returns false.The best answer, if these are particular dates known ahead of time, is
a calendar table. But brute force works too.
declare @.d datetime
set @.d = '28 Nov 2006'
SELECT CASE WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 5
AND DATEPART(day,@.d) between 25 and 31
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 9
AND DATEPART(day,@.d) between 1 and 7
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Tuesday'
AND DATEPART(month,@.d) = 11
AND DATEPART(day,@.d) between 22 and 28
THEN 'True'
ELSE 'False'
END as Answer
Roy Harvey
Beacon Falls, CT
On Wed, 27 Dec 2006 04:43:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>How can I write a function that accepts a data as a parameter and if the date
>is
>1. Last Monday in May or
>2. First Monday in September or
>3. 4th Tuesday in November
>returns true otherwise returns false.|||JIM
Take a look at Steve Kass's function
create function dbo.NthWeekDay(
@.first datetime, -- First of the month of interest (no time part)
@.nth tinyint, -- Which of them - 1st, 2nd, etc.
@.dow tinyint -- Day of week we want
) returns datetime as begin
-- Note: Returns a date in a later month if @.nth is too large
declare @.result datetime
set @.result = @.first + 7*(@.nth-1)
return @.result + (7 + @.dow - datepart(weekday,@.result))%7
end
go
-- Find the 5th Thursday of August, 2002
select dbo.NthWeekDay('2002/08/01',5,5) as D
select datename(weekday,D) + space(1) + cast(D as varchar(20))
from (
select dbo.NthWeekDay('2002/08/01',5,5) as D
) X
go
drop function NthWeekDay
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:FED1D4FF-5F9E-414B-B115-2118EE32F4EC@.microsoft.com...
> How can I write a function that accepts a data as a parameter and if the
> date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>|||On Wed, 27 Dec 2006 07:18:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Thank you very much for your help, last one should be 4th Thursday (sorry my
>mistake), how would this change the code?
Well the fourth Thursday would be a lot like the fourth Tuesday,
wouldn't it? Just copy the day range from that one.
Roy|||Wow, I've been seeing homework assignments getting posted for 20 years now.
"JIM.H." wrote:
> How can I write a function that accepts a data as a parameter and if the date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>

q; how to write this

Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=’Yes’ first
2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in the
first selection
Combine these two and return the result
Try:
select distinct
ID
, IsYesNo
from
MyTable
where
IsYesNo = 'Yes'
union all
select distinct
ID
, IsYesNo
from
MyTable o
where
IsYesNo = 'No'
and not exists
(
select distinct
*
from
MyTable i
where
i.IsYesNo = 'Yes'
and
i.ID = o.ID
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:7E592B5E-81BB-420A-BDBE-9ED0C67689F4@.microsoft.com...
Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=’Yes’ first
2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in the
first selection
Combine these two and return the result
|||Looks like homework so ignoring the detail of the question but going straight
to the result
select ID, IsYesNo = max(IsYesNo)
from MyTable
Group by ID
"JIM.H." wrote:

> Hello,
> I have MyTable with ID, IsYesNo fields
> ID is duplicated so I need perform select on MyTable with the following
> conditions:
> 1. Select all the ID distinct where IsYesNo=’Yes’ first
> 2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in the
> first selection
> Combine these two and return the result
>

q; how to write this

Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=’Yes’ first
2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in th
e
first selection
Combine these two and return the resultTry:
select distinct
ID
, IsYesNo
from
MyTable
where
IsYesNo = 'Yes'
union all
select distinct
ID
, IsYesNo
from
MyTable o
where
IsYesNo = 'No'
and not exists
(
select distinct
*
from
MyTable i
where
i.IsYesNo = 'Yes'
and
i.ID = o.ID
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:7E592B5E-81BB-420A-BDBE-9ED0C67689F4@.microsoft.com...
Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=’Yes’ first
2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in th
e
first selection
Combine these two and return the result|||Looks like homework so ignoring the detail of the question but going straigh
t
to the result
select ID, IsYesNo = max(IsYesNo)
from MyTable
Group by ID
"JIM.H." wrote:

> Hello,
> I have MyTable with ID, IsYesNo fields
> ID is duplicated so I need perform select on MyTable with the following
> conditions:
> 1. Select all the ID distinct where IsYesNo=’Yes’ first
> 2. Then select all the ID distinct where IsYesNo=’No’ if ID is not in
the
> first selection
> Combine these two and return the result
>

q; how to write this

Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=â'Yesâ' first
2. Then select all the ID distinct where IsYesNo=â'Noâ' if ID is not in the
first selection
Combine these two and return the resultTry:
select distinct
ID
, IsYesNo
from
MyTable
where
IsYesNo = 'Yes'
union all
select distinct
ID
, IsYesNo
from
MyTable o
where
IsYesNo = 'No'
and not exists
(
select distinct
*
from
MyTable i
where
i.IsYesNo = 'Yes'
and
i.ID = o.ID
)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:7E592B5E-81BB-420A-BDBE-9ED0C67689F4@.microsoft.com...
Hello,
I have MyTable with ID, IsYesNo fields
ID is duplicated so I need perform select on MyTable with the following
conditions:
1. Select all the ID distinct where IsYesNo=â'Yesâ' first
2. Then select all the ID distinct where IsYesNo=â'Noâ' if ID is not in the
first selection
Combine these two and return the result|||Looks like homework so ignoring the detail of the question but going straight
to the result
select ID, IsYesNo = max(IsYesNo)
from MyTable
Group by ID
"JIM.H." wrote:
> Hello,
> I have MyTable with ID, IsYesNo fields
> ID is duplicated so I need perform select on MyTable with the following
> conditions:
> 1. Select all the ID distinct where IsYesNo=â'Yesâ' first
> 2. Then select all the ID distinct where IsYesNo=â'Noâ' if ID is not in the
> first selection
> Combine these two and return the result
>

q; how to improve this transaction

Hello,
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and one
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessing
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh

q; how to improve this transaction

Hello,
I have four different transactions such as below and I do one insert and one
update in each transaction and it seem it is slow and creates deadlock with
the user interface.
These transactions are performed against the tables that users are accessing
with another user interface. I have following two questions:
1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
not put index since only two distinct values. Should I put indexes on these
fields?
2. Can I make this transaction let user interface do its task in case
accessing the same rows, I can start transaction again but I do not want
users get disturbed?
.
BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @.TrType AND T2.TextField2 = @.TextField2)
UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@.MyRunDateTime
FROM T2
WHERE (TextField1 = @.TrType AND TextField2 = @.TextField2)
IF @.@.ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
ENDadd a composite, non-clustered index on textfield1,textfield2.
otherwise, you will have to do a table scan for each update.
>|||On Wed, 12 Jul 2006 16:19:01 -0700, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>I have four different transactions such as below and I do one insert and on
e
>update in each transaction and it seem it is slow and creates deadlock with
>the user interface.
>These transactions are performed against the tables that users are accessin
g
>with another user interface. I have following two questions:
>1. T2.TextField1 and TextField2 = @.TextField2 are Ok, Nok fields so I did
>not put index since only two distinct values. Should I put indexes on these
>fields?
>2. Can I make this transaction let user interface do its task in case
>accessing the same rows, I can start transaction again but I do not want
>users get disturbed?
How long does the transaction take if all alone on the machine?
How much IO does it do (from profiler or "set statistic io on" in
query analyzer)?
Are you familiar with the nolock hint?
Do you have access to the code for the other program accessing the
database? That's the one that might need the nolock.
You might want to do the selects for one or both statements (the
update does an implicit select) and put the results into temp files or
@.tables, then insert and update from them, to minimize locking times.
Depends on times and data volumes and PKs.
Josh

q; Great Circle distance calculation

Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle
distance calculationHello JIM.H.,
> Great Circle distance calculation
> Is there any stored procedure or application that implements Great
> Circle
> distance calculation
In SQL Server 2005, no. In Katmai, maybe. Here it is as a UDF for you.
create function dbo.DistanceBetweenInMiles(@.Lat1 float,@.Lon1 float,@.Lat2
float,@.Lon2 float)
returns float
as begin
declare @.radians float,@.dlon float,@.dlat float,@.a float,@.d float
set @.radians = PI()/180.0
set @.dlon = (@.lon1 - @.lon2) * @.radians
set @.dlat = (@.lat1 - @.lat2) * @.radians
set @.a = power(sin(@.dlat/2.0),2.0) + cos(@.lat1 * @.radians) *
cos(@.lat2 * @.radians) * power(sin(@.dlon/2.0),2.0)
set @.d = 3956 * 2.0 * atn2(sqrt(@.a),sqrt(1.0-@.a))
return @.d
end
go
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Jim,
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81360
RLF
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:23712CCA-A9EA-4B5B-B643-2A97F21D0FAE@.microsoft.com...
> Great Circle distance calculation
> Is there any stored procedure or application that implements Great Circle
> distance calculation
>|||On Mon, 15 Oct 2007 05:33:00 -0700, JIM.H. wrote:
>Great Circle distance calculation
>Is there any stored procedure or application that implements Great Circle
>distance calculation
>
Hi Jim,
<plug>
Adam Machanics book "Expert SQL Server 2005 Development" has a complete
chapter on working with spatial data. This of course covers great circle
distance calculations, as well as finding neighbors in a given distance,
or finding the nearest neighbor.
</modesty>
That chapter is authored by me. It is really great.
<modesty>
</plug>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

q; exit stored procedure

Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
follows:
Exec Sp1
Inserts, updates
Exec Sp2
Inserts, upadtes
Exec Sp3
If error Exit Exec Sp1
Can I end Sp1 if I catch an error in Sp3Jim
CREATE PROCEDURE BigOne
AS
DECLARE @.err integer
BEGIN TRANSACTION
EXEC @.err = sp1
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp2
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp3
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
EXEC @.err = sp4
SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
COMMIT TRANSACION
GO
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> follows:
> Exec Sp1
> Inserts, updates
> Exec Sp2
> Inserts, upadtes
> Exec Sp3
> If error Exit Exec Sp1
> Can I end Sp1 if I catch an error in Sp3
>|||Hi Uri,
Thank you very much for your help. I am currently getting Deadlock message
from time to time, I was wondering how this commit rollback will effect it.
Since there will not be auto-commit for each update (I have many updates
before calling SP2, SP3, and one single commit for all the update, will this
increase the chance of deadlock or decrease it?
"Uri Dimant" wrote:
> Jim
> CREATE PROCEDURE BigOne
> AS
> DECLARE @.err integer
> BEGIN TRANSACTION
> EXEC @.err = sp1
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp2
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp3
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> EXEC @.err = sp4
> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> COMMIT TRANSACION
> GO
>
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> > follows:
> >
> > Exec Sp1
> > Inserts, updates
> > Exec Sp2
> > Inserts, upadtes
> > Exec Sp3
> > If error Exit Exec Sp1
> >
> > Can I end Sp1 if I catch an error in Sp3
> >
>
>|||JIM
Read this article
http://www.sql-server-performance.com/deadlocks.asp
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> Hi Uri,
> Thank you very much for your help. I am currently getting Deadlock message
> from time to time, I was wondering how this commit rollback will effect
> it.
> Since there will not be auto-commit for each update (I have many updates
> before calling SP2, SP3, and one single commit for all the update, will
> this
> increase the chance of deadlock or decrease it?
>
> "Uri Dimant" wrote:
>> Jim
>> CREATE PROCEDURE BigOne
>> AS
>> DECLARE @.err integer
>> BEGIN TRANSACTION
>> EXEC @.err = sp1
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp2
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp3
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> EXEC @.err = sp4
>> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
>> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
>> COMMIT TRANSACION
>> GO
>>
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
>> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
>> > follows:
>> >
>> > Exec Sp1
>> > Inserts, updates
>> > Exec Sp2
>> > Inserts, upadtes
>> > Exec Sp3
>> > If error Exit Exec Sp1
>> >
>> > Can I end Sp1 if I catch an error in Sp3
>> >
>>|||Thanks Uri, yes I was reading that article, since the commit will be
performed for a long transaction I will probably get more deadlocks in this
case. Do you agree with me?
"Uri Dimant" wrote:
> JIM
> Read this article
> http://www.sql-server-performance.com/deadlocks.asp
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:3FE71243-9EAA-4C17-BF27-781C41670925@.microsoft.com...
> > Hi Uri,
> >
> > Thank you very much for your help. I am currently getting Deadlock message
> > from time to time, I was wondering how this commit rollback will effect
> > it.
> > Since there will not be auto-commit for each update (I have many updates
> > before calling SP2, SP3, and one single commit for all the update, will
> > this
> > increase the chance of deadlock or decrease it?
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Jim
> >> CREATE PROCEDURE BigOne
> >> AS
> >> DECLARE @.err integer
> >> BEGIN TRANSACTION
> >> EXEC @.err = sp1
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp2
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp3
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> EXEC @.err = sp4
> >> SELECT @.err = coalesce(nullif(@.err, 0, @.@.error)
> >> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> >> COMMIT TRANSACION
> >> GO
> >>
> >>
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:BAFF8A0D-E2CE-462D-AE75-02AEFF9ED379@.microsoft.com...
> >> > Hello I have three stored procedures Sp1 calls Sp2 and Sp2 calls Sp3 as
> >> > follows:
> >> >
> >> > Exec Sp1
> >> > Inserts, updates
> >> > Exec Sp2
> >> > Inserts, upadtes
> >> > Exec Sp3
> >> > If error Exit Exec Sp1
> >> >
> >> > Can I end Sp1 if I catch an error in Sp3
> >> >
> >>
> >>
> >>
>
>

q; duplicate dhcek on two columns

Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or not
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>
|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:

> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigger
> I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
>
|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:

> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
>
>
|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:

> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>
|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sample
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Thanks Hugo, unique constraints would not work since a specific value should
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"Hugo Kornelis" wrote:

> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>
> Hi Arnie,
> You're right - I missed that part of Jim's message.
> If this is indeed the requirement (Jim's post, as already pointed out by
> several others, is not entirely clear), he could use a trigger like
> this:
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> WHERE i.type = 'xyz'
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> Or, as an alternative, use an indexed view:
> CREATE VIEW dbo.MyView WITH SCHEMABINDING
> AS
> SELECT id -- more columns may be added to help other queries
> FROM dbo.<BaseTable>
> WHERE type = 'xyz';
> go
> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
> go
> (Untested)
> --
> Hugo Kornelis, SQL Server MVP
>
|||Or doens't like the responses.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...[vbcol=seagreen]
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "Hugo Kornelis" wrote:

q; duplicate dhcek on two columns

Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or no
t
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=’abc’ duplicated, that is fine.I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-hand...#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:

> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigg
er
> I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or
not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
>|||On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:

>Hi
>Why not have a unique index on these two columns?
Hi John,
Agreed 100%.

>Adapted from http://www.sommarskog.se/error-hand...#triggercontext
>Try the following code in your trigger:
>IF EXISTS (SELECT *
> FROM inserted i
> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> )
>BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Duplicate Values Entered.', 16, 1)
> RETURN
>END
But this won't work. In an AFTER trigger, this will always result in
error since the new rows are already in the table once the trigger
fires. And in an INSTEAD OF trigger, this will catch the addition of a
second row if the first is already there, but it will miss the insertion
of two duplicate rows in a single INSERT execution.
For an AFTER trigger, this would probably work (untested, though):
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:

> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
>
>|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:

> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sampl
e
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Thanks Hugo, unique constraints would not work since a specific value shou
ld
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
>|||On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:

>Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
>probably won't work for him.
>"check if T1.ID and T1.Type='xyz' together are duplicated"
>AND
>"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Hi Arnie,
You're right - I missed that part of Jim's message.
If this is indeed the requirement (Jim's post, as already pointed out by
several others, is not entirely clear), he could use a trigger like
this:
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
WHERE i.type = 'xyz'
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Or, as an alternative, use an indexed view:
CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT id -- more columns may be added to help other queries
FROM dbo.<BaseTable>
WHERE type = 'xyz';
go
CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
go
(Untested)
Hugo Kornelis, SQL Server MVP

q; duplicate dhcek on two columns

Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=â'xyzâ' together are duplicated or not
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=â'abcâ' duplicated, that is fine.I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigger
> I need to check if T1.ID and T1.Type=â'xyzâ' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=â'abcâ' duplicated, that is fine.
>|||On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>Hi
>Why not have a unique index on these two columns?
Hi John,
Agreed 100%.
>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>Try the following code in your trigger:
>IF EXISTS (SELECT *
> FROM inserted i
> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>)
>BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Duplicate Values Entered.', 16, 1)
> RETURN
>END
But this won't work. In an AFTER trigger, this will always result in
error since the new rows are already in the table once the trigger
fires. And in an INSTEAD OF trigger, this will catch the addition of a
second row if the first is already there, but it will miss the insertion
of two duplicate rows in a single INSERT execution.
For an AFTER trigger, this would probably work (untested, though):
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
--
Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>>Hi
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>>Try the following code in your trigger:
>>IF EXISTS (SELECT *
>> FROM inserted i
>> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>>)
>>BEGIN
>> ROLLBACK TRANSACTION
>> RAISERROR('Duplicate Values Entered.', 16, 1)
>> RETURN
>>END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>>Hi
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
>>Try the following code in your trigger:
>>IF EXISTS (SELECT *
>> FROM inserted i
>> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
>>)
>>BEGIN
>> ROLLBACK TRANSACTION
>> RAISERROR('Duplicate Values Entered.', 16, 1)
>> RETURN
>>END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:
> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.4ax.com...
> > On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >
> >>Hi
> >>
> >>Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> >>Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> >>
> >>Try the following code in your trigger:
> >>
> >>IF EXISTS (SELECT *
> >> FROM inserted i
> >> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> >>)
> >>BEGIN
> >> ROLLBACK TRANSACTION
> >> RAISERROR('Duplicate Values Entered.', 16, 1)
> >> RETURN
> >>END
> >
> > But this won't work. In an AFTER trigger, this will always result in
> > error since the new rows are already in the table once the trigger
> > fires. And in an INSTEAD OF trigger, this will catch the addition of a
> > second row if the first is already there, but it will miss the insertion
> > of two duplicate rows in a single INSERT execution.
> >
> > For an AFTER trigger, this would probably work (untested, though):
> >
> > IF EXISTS (SELECT *
> > FROM inserted AS i
> > INNER JOIN <BaseTable> AS b
> > ON i.id = b.id
> > AND i.type = b.type
> > GROUP BY b.id, b.type
> > HAVING COUNT(*) > 1)
> > BEGIN;
> > ROLLBACK TRANSACTION;
> > RAISERROR('Duplicate Values Entered.', 16, 1);
> > RETURN;
> > END;
> >
> > --
> > Hugo Kornelis, SQL Server MVP
>
>|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >Hi
> >
> >Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
> >Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> >
> >Try the following code in your trigger:
> >
> >IF EXISTS (SELECT *
> > FROM inserted i
> > JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> >)
> >BEGIN
> > ROLLBACK TRANSACTION
> > RAISERROR('Duplicate Values Entered.', 16, 1)
> > RETURN
> >END
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sample
code.
John
"JIM.H." wrote:
> Thanks Hugo, unique constraints would not work since a specific value should
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
> > On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
> >
> > >Hi
> > >
> > >Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> > >Adapted from http://www.sommarskog.se/error-handling-I.html#triggercontext
> > >
> > >Try the following code in your trigger:
> > >
> > >IF EXISTS (SELECT *
> > > FROM inserted i
> > > JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> > >)
> > >BEGIN
> > > ROLLBACK TRANSACTION
> > > RAISERROR('Duplicate Values Entered.', 16, 1)
> > > RETURN
> > >END
> >
> > But this won't work. In an AFTER trigger, this will always result in
> > error since the new rows are already in the table once the trigger
> > fires. And in an INSTEAD OF trigger, this will catch the addition of a
> > second row if the first is already there, but it will miss the insertion
> > of two duplicate rows in a single INSERT execution.
> >
> > For an AFTER trigger, this would probably work (untested, though):
> >
> > IF EXISTS (SELECT *
> > FROM inserted AS i
> > INNER JOIN <BaseTable> AS b
> > ON i.id = b.id
> > AND i.type = b.type
> > GROUP BY b.id, b.type
> > HAVING COUNT(*) > 1)
> > BEGIN;
> > ROLLBACK TRANSACTION;
> > RAISERROR('Duplicate Values Entered.', 16, 1);
> > RETURN;
> > END;
> >
> > --
> > Hugo Kornelis, SQL Server MVP
> >|||On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
>probably won't work for him.
>"check if T1.ID and T1.Type='xyz' together are duplicated"
>AND
>"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Hi Arnie,
You're right - I missed that part of Jim's message.
If this is indeed the requirement (Jim's post, as already pointed out by
several others, is not entirely clear), he could use a trigger like
this:
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
WHERE i.type = 'xyz'
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Or, as an alternative, use an indexed view:
CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT id -- more columns may be added to help other queries
FROM dbo.<BaseTable>
WHERE type = 'xyz';
go
CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
go
(Untested)
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"Hugo Kornelis" wrote:
> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
> >Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> >probably won't work for him.
> >
> >"check if T1.ID and T1.Type='xyz' together are duplicated"
> >AND
> >"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> Hi Arnie,
> You're right - I missed that part of Jim's message.
> If this is indeed the requirement (Jim's post, as already pointed out by
> several others, is not entirely clear), he could use a trigger like
> this:
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> WHERE i.type = 'xyz'
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> Or, as an alternative, use an indexed view:
> CREATE VIEW dbo.MyView WITH SCHEMABINDING
> AS
> SELECT id -- more columns may be added to help other queries
> FROM dbo.<BaseTable>
> WHERE type = 'xyz';
> go
> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
> go
> (Untested)
> --
> Hugo Kornelis, SQL Server MVP
>|||Or doens't like the responses.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "Hugo Kornelis" wrote:
>> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>> >Unless the OP has misstated his scenario, I think that a UNIQUE
>> >CONSTRAINT
>> >probably won't work for him.
>> >
>> >"check if T1.ID and T1.Type='xyz' together are duplicated"
>> >AND
>> >"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
>> Hi Arnie,
>> You're right - I missed that part of Jim's message.
>> If this is indeed the requirement (Jim's post, as already pointed out by
>> several others, is not entirely clear), he could use a trigger like
>> this:
>> IF EXISTS (SELECT *
>> FROM inserted AS i
>> INNER JOIN <BaseTable> AS b
>> ON i.id = b.id
>> AND i.type = b.type
>> WHERE i.type = 'xyz'
>> GROUP BY b.id, b.type
>> HAVING COUNT(*) > 1)
>> BEGIN;
>> ROLLBACK TRANSACTION;
>> RAISERROR('Duplicate Values Entered.', 16, 1);
>> RETURN;
>> END;
>> Or, as an alternative, use an indexed view:
>> CREATE VIEW dbo.MyView WITH SCHEMABINDING
>> AS
>> SELECT id -- more columns may be added to help other queries
>> FROM dbo.<BaseTable>
>> WHERE type = 'xyz';
>> go
>> CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
>> go
>> (Untested)
>> --
>> Hugo Kornelis, SQL Server MVP