Query 2 tables - each in a different database

Is there any query code that will allow joining tables that are in different databases running on the same server? If yes, how is the connection to each handled?

That is no problem.

SELECT a.Field, b.Field
FROM dbo.myTable a
JOIN OtherDatabase.dbo.myTable b ON a.myKey = b.myKey

|||

The select code is clear but the connection to the two databases is not when using ADO. Is one connection object used somehow or are the databases linked?

|||

WeslyB is right.

But your user has to have select rights on the other database.

You don't have to handle the rights in ADO.

Regards

Query 2 table in different databases

Is there any query code that will allow joining tables that are in different databases running on the same server? If yes, how is the connection to each handled?

You need to use a 3-part name to reference the table from one database to another. The 3-part name consists of <database>.<schema>.<object>. The connection is only to the server and you can access any database to which you have permissions. You can either switch database context using USE <database> statement or use the 3-part name to reference objects.

Query ... Distinct rows

I have a table as follows
ORDER_ID CODE STATUS
1000 XA3 5
1000 XA1 4
1000 XA7 5
1001 X35 5
1001 XA3 5
I want to run a query that will return the distinct ORDER_ID that is Status
= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
For example above, the result set will be 1001 only (as 1000 has one record
with Status of 4).
I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
appear to work :-(
Thanks in advance!
Wez
On Fri, 17 Jun 2005 03:30:02 -0700, Wez wrote:

> I have a table as follows
>ORDER_ID CODE STATUS
>1000 XA3 5
>1000 XA1 4
>1000 XA7 5
>1001 X35 5
>1001 XA3 5
>I want to run a query that will return the distinct ORDER_ID that is Status
>= 5. If any records have Status <> 5, I dont want that ORDER_ID returned.
>For example above, the result set will be 1001 only (as 1000 has one record
>with Status of 4).
>I have tried using 'HAVING MIN(Status) = 5 AND MAX(Status = 5) but it doesnt
>appear to work :-(
>Thanks in advance!
>Wez
Hi Wez,
This one should work, actually:
SELECT Order_ID
FROM YourTable
GROUP BY Order_ID
HAVING MIN(Status) = 5 AND MAX(Status) = 5
What eexactly does "doesn't appear to work" mean? Error messages? Wrong
results? Blue smoke in the server room? It's hard to help you without
knowing what's happening!
BTW, here's another query that should also work:
SELECT DISTINCT t1.Order_ID
FROM YourTable AS t1
WHERE NOT EXISTS (SELECT *
FROM YourTable AS t2
WHERE t2.Order_ID = t1.Order_ID
AND t2.Status <> 5)
/* Adding the line below might improve performance
AND t1.Status = 5
*/
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo,
when i say didnt work I mean I was getting wrong results i.e. orders
appearing in the result set that had lines not yet equal to status '5'.
However your alternative method has worked well :-)
Thanks,
Wez

Queries or SP

hi which is bettere to use a quesry from the code or to use a Stored Procedure and call the SP from the codeIt is better to use SP, as it has got many advantages|||thanx for answering
but can u list some of them|||Stored Procedure Advantages
Although you can do most of the things a stored procedure can do with simple ad hoc Transact-SQL code, stored procedures have a number of advantages over ad hoc queries, including

Execution plan retention and reuse

Query autoparameterization

Encapsulation of business rules and policies

Application modularization

Sharing of application logic between applications

Access to database objects that is both secure and uniform

Consistent, safe data modification

Network bandwidth conservation

Support for automatic execution at system start-up

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=3

Try seeking information. google's your best friend.

queries longer than 5 mins

Hi I have set up a windows group for our Crystal reporters as they sometimes
have code that runs for way to long is there a cmd I can issue that will cut
of any queries going on longer then 5 minutes just for this particular group
.
Not sure if possible
Thanks
for any help
SammySammy
Use SQL Server Profiler to see what is going on. Look at Duration column.
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:8A577E59-8C2D-48B3-8E77-C410FC41C308@.microsoft.com...
> Hi I have set up a windows group for our Crystal reporters as they
> sometimes
> have code that runs for way to long is there a cmd I can issue that will
> cut
> of any queries going on longer then 5 minutes just for this particular
> group.
> Not sure if possible
>
> Thanks
> for any help
> Sammy

Queries going to Suspend state with wait type ASYNC_NETWORK_IO

Recently we observed a problem. We are running stored procedure through our c# code. Three machines access the server and update or insert in the required tables in the server. If there is no data in the server, on installing first time our application usually our database is clean. then stored procedure works fine, it takes around 10 to 15 sec to execute. Next time if execute the time goes up to minutes like 15 mins. Next time it goes for hours around 4 hrs. Even to update 4 or 5 records it takes time. Initially we thought it was because of the size of the data and we tried to re tune on indexes, it did not solve. But now what we observe is even with less number of records in server also it wouldn't come of the execution for hours.
Now are executing the just the SP in the SQL manager studio to see the time. That one also is executing for hours. when looked at the activity monitor the process goes to suspend state with wait type ASYNC_NETWORK_IO.

When we comment one of the query is working fine.
Is this something to do with the query I am not sure. If that is the case it should not work every time.

The query makes any sence or is there any way to write it in better way

'UPDATE [server].[dbo].[DocumentMetadata] SET DocumentInfoID = b.DocumentInfoID, [Name] = b.[Name], MetadataType = b.MetadataType,
[Value] = b.[Value], ValueType = b.ValueType
FROM [server].[dbo].[DocumentMetadata] a WITH (UPDLOCK)
INNER JOIN (SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MACHINENAME.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + ''') b
ON a.DocumentInfoID = b.DocumentInfoID AND a.[Name] = b.[Name]'

'INSERT INTO [server].[dbo].[DocumentMetadata]
(DocumentInfoID, [Name], MetadataType, [Value], ValueType)
SELECT c.DocumentInfoID, c.[Name], c.MetadataType, c.[Value], c.ValueType
FROM MACHINENAME.[Client].[dbo].[DocumentMetadata] c
INNER JOIN MACHINENAME.[Client].dbo.DocumentInfo DINF ON c.DocumentInfoID = DINF.DocumentInfoID
INNER JOIN MACHINENAME.[Client].dbo.Path d on DINF.NativeFileID = d.PathID
INNER JOIN MYCLI.[Client].dbo.ActiveDataSource ADS ON d.DataSourceID = ADS.DataSourceID
WHERE ADS.ProjectID = ''' + @.ProjID + '''
AND Ltrim(rtrim(c.DocumentInfoID))+ ltrim(rtrim(c.[Name])) NOT IN
(SELECT Ltrim(rtrim(DocumentInfoID))+ ltrim(rtrim([Name])) FROM [server].[dbo].[DocumentMetadata])'

We have been fighting it out for so many days.
Can anybody help

Thanks
knvdssr

ASYNC_NETWORK_IO means SQL server is waiting for network...

How may rows your select statement for INSERT it is returning?

You are using ltrim and rtrim functions against column which result a table scan and it can't use index...

Try to change the query or just comment the query and test it to make it is the issue.

|||It appears you are using a LINKED SERVER to access another server?

If this is the case, try looking at activity monitor on the source. I would suspect you are getting a deadlock or lock problem or something on the linked server.

The wait you are seeing indicates it is waiting on the linked server to finish the query and send records.

|||When you use 4-part name, all of the data from the remote server is pulled across before the join is formulated. If the remote table is large, you will have to wait for a long time for all the data to cross over. Try using openquery() to only pull/update the necessary remote data.

You can lookup some examples here:
http://groups.google.com/groups?q=sqlserver%20oj%20openquery%20update|||

We commented the query which I mentioned earlier, to see if that one is causing the problem. Then the whole Stored procedure finished executing in minutes.

We changed the the query with our using the NOT IN in that query.

Now first time it is taking only 10 minutes for more than 100,000 records.

But the second one is not coming out at all. This time the server is having lot of rows because is already updated once.

All that we see is the process in suspend state or comes out as timed out.

If I am getting a dead lock on the linked server is there any way to solve it?

Could any body help please

|||

Again, take a look at openquery/openrowset. Bringing the entire resultset back is not recommended when the remote table is large.

Q3: Trusted SQL Logins

I can not connect to my Local SQL Server 2005 in C# code,

it throws the error,

The user is not associated with a trusted SQL Server connection.

i tried to change the mode to Mixed Mode, but i don't see it, I see only Windows and SQL Server Auth mode,

i tried to change to SQL Server Mode, but none of user log will work, it says, this login is not in trusted connection,

so i would make any login associated with trusted account.
Thanks,

I have covered how to choose both Windows and SQL authentication in SQL Server in the thread below but if you are getting this error in a web application then the problem is not SQL Server but IIS. In IIS Deny anonymous users and in your Web.Config enable impersonation. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1894423&SiteID=1

|||

"Mixed mode" = "SQL Server and Windows authentication mode"

Maybe you are trying to login using a SQL Login when your authentication is Windows only? Perhaps you need to change your connections string:

Windows Auth

integrated secruity=true

SQL Auth

integrated security=false; uid=user;pwd=pword

HTH!

|||

Thanks for your reply, but i followed the steps, when I change it to SQLServer Authentication mode, it ask for log info, I put SA account, but it gives this error,

SA is not associated with Trusted Account.

Also I don't see the Security page as well as Mixed Mode option when i go to Server properties.
(On the Security page, under Server authentication, select the new server authentication mode, and then click OK)

Any idea,

Thanks,|||

Where are you trying to change the authentication? Open SSMS, right click your servername in Object Explorer, click properties. Down the left hand side you should have a list of pages including the security one where you need to make these changes.

Is this not what you see? Are you using Management Studio? What version of SQL Server is it?

|||You are not in the correct place because you have only two options Window or Both SQL Server and Windows is the only other option so right click on the name of your server in object explorer and go to properties then security. But you still need to enable the System Admin account because during installation if you choose Windows authentication SQL Server disables the SA account. Hope this helps.|||Thanks,

i found it and changed it but still i get an error when I try to connect to Database Engine using that sa account that i enabled it,

1) A connection was succesfully established with the server, but then an error occured during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe)(MSSql Server , Error:233)

2) from asp.net code, i get error like , sa is not in trusted connection list.

any idea,

Thanks,|||

I think you'll need to get yourself back to the SQL Configuration Manager and perhaps enable named pipes? I did a quick search on google for: "No process is on the other end of the pipe" and there were a fair few resources. Have a look there and let us know how you get on.

Good luck!

|||

No you need to enable the SA account before you try to use it and error 233 is covered in the link below and you need to reboot the box after you make all these changes. I have also seen error 233 related to certificates not installed in SQL Server. Hope this helps.

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

Q2: Trusted SQL Logins

I can not connect to my Local SQL Server 2005 in C# code,

it throws the error,

The user is not associated with a trusted SQL Server connection.

i tried to change the mode to Mixed Mode, but i don't see it, I see only Windows and SQL Server Auth mode,

i tried to change to SQL Server Mode, but none of user log will work, it says, this login is not in trusted connection,

so i would make any login associated with trusted account.
Thanks,

I have covered how to choose both Windows and SQL authentication in SQL Server in the thread below but if you are getting this error in a web application then the problem is not SQL Server but IIS. In IIS Deny anonymous users and in your Web.Config enable impersonation. Hope this helps.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1894423&SiteID=1

|||

"Mixed mode" = "SQL Server and Windows authentication mode"

Maybe you are trying to login using a SQL Login when your authentication is Windows only? Perhaps you need to change your connections string:

Windows Auth

integrated secruity=true

SQL Auth

integrated security=false; uid=user;pwd=pword

HTH!

|||

Thanks for your reply, but i followed the steps, when I change it to SQLServer Authentication mode, it ask for log info, I put SA account, but it gives this error,

SA is not associated with Trusted Account.

Also I don't see the Security page as well as Mixed Mode option when i go to Server properties.
(On the Security page, under Server authentication, select the new server authentication mode, and then click OK)

Any idea,

Thanks,|||

Where are you trying to change the authentication? Open SSMS, right click your servername in Object Explorer, click properties. Down the left hand side you should have a list of pages including the security one where you need to make these changes.

Is this not what you see? Are you using Management Studio? What version of SQL Server is it?

|||You are not in the correct place because you have only two options Window or Both SQL Server and Windows is the only other option so right click on the name of your server in object explorer and go to properties then security. But you still need to enable the System Admin account because during installation if you choose Windows authentication SQL Server disables the SA account. Hope this helps.|||Thanks,

i found it and changed it but still i get an error when I try to connect to Database Engine using that sa account that i enabled it,

1) A connection was succesfully established with the server, but then an error occured during the login process. (provider: shared memory Provider, error: 0 - No process is on the other end of the pipe)(MSSql Server , Error:233)

2) from asp.net code, i get error like , sa is not in trusted connection list.

any idea,

Thanks,|||

I think you'll need to get yourself back to the SQL Configuration Manager and perhaps enable named pipes? I did a quick search on google for: "No process is on the other end of the pipe" and there were a fair few resources. Have a look there and let us know how you get on.

Good luck!

|||

No you need to enable the SA account before you try to use it and error 233 is covered in the link below and you need to reboot the box after you make all these changes. I have also seen error 233 related to certificates not installed in SQL Server. Hope this helps.

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

q; a trigger code

I have table T1 and I am trying to extract some date from T1 table based on
inserts, updates and deletes. The destination table T2 has three fields F1,
F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my
first trigger, Can anyone write this trigger for me?I would NEVER write any code that had table names like T1 or T2, and columns
named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
that you can post?
Give us more to work with, and you may get some good assistance here.
For example, the the code snipplet, is TYPE a column name, a literal, a
varable? Where did it come from?
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields
> F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>|||Hi Arnie,
That was just to simplify the case. I do not have actual table definitions
yet. Type is what I am trying to determine in the trigger. This will be
INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
"Arnie Rowland" wrote:
> I would NEVER write any code that had table names like T1 or T2, and columns
> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> that you can post?
> Give us more to work with, and you may get some good assistance here.
> For example, the the code snipplet, is TYPE a column name, a literal, a
> varable? Where did it come from?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields
> > F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
>
>|||JIM.H. wrote:
> I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>
I'm guessing this is some sort of audit mechanism? If so, lots of info
available online regarding auditing, my friend Google found this one for me:
http://www.nigelrivett.net/AuditTrailTrigger.html
Incidentally, this type of activity, if done poorly, can cause BLOCKING
on updates and inserts. Just tossing that out there, since you've been
complaining all week about deadlocks.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, inside the Trigger you will be able to determine if it is executing
because of INSERT, UPDATE, DELETE.
If you are exploring some form of auditing, the archive table 'should' have
additional columns for the user and current date/time. Those are also
available internal to the Trigger.
Let us know how to help you when you're closer to the need.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> Hi Arnie,
> That was just to simplify the case. I do not have actual table definitions
> yet. Type is what I am trying to determine in the trigger. This will be
> INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
>
> "Arnie Rowland" wrote:
>> I would NEVER write any code that had table names like T1 or T2, and
>> columns
>> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
>> that you can post?
>> Give us more to work with, and you may get some good assistance here.
>> For example, the the code snipplet, is TYPE a column name, a literal, a
>> varable? Where did it come from?
>> --
>> Arnie Rowland*
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >I have table T1 and I am trying to extract some date from T1 table based
>> >on
>> > inserts, updates and deletes. The destination table T2 has three fields
>> > F1,
>> > F2, F3, so I need something like
>> >
>> > Insert into T2 (F1, F2, F3)
>> > Select (F1, F2,Type)
>> > From T1
>> >
>> > Type should be defined based on Insert, Update, or Delete. This will be
>> > my
>> > first trigger, Can anyone write this trigger for me?
>> >
>> >
>> >
>> >
>>|||Thansk Arnie. Does this do update and insert safely? how can I do deleted?
CREATE TRIGGER trMyTrigger ON T1
FOR INSERT, UPDATE
AS
if exists (select * from inserted)
Insert Into T2(ID, Name,TrType)
Select ID, Name,'Insert'
From inserted
Where Name='TestData'
else
Update t
Set t.Name=i.Name, t.TrType='Update'
From inserted i INNER JOIN T2 t on i.ID=t.ID
GO
"Arnie Rowland" wrote:
> Yes, inside the Trigger you will be able to determine if it is executing
> because of INSERT, UPDATE, DELETE.
> If you are exploring some form of auditing, the archive table 'should' have
> additional columns for the user and current date/time. Those are also
> available internal to the Trigger.
> Let us know how to help you when you're closer to the need.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
> > Hi Arnie,
> >
> > That was just to simplify the case. I do not have actual table definitions
> > yet. Type is what I am trying to determine in the trigger. This will be
> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
> >
> >
> >
> > "Arnie Rowland" wrote:
> >
> >> I would NEVER write any code that had table names like T1 or T2, and
> >> columns
> >> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> >> that you can post?
> >>
> >> Give us more to work with, and you may get some good assistance here.
> >>
> >> For example, the the code snipplet, is TYPE a column name, a literal, a
> >> varable? Where did it come from?
> >>
> >> --
> >> Arnie Rowland*
> >> "To be successful, your heart must accompany your knowledge."
> >>
> >>
> >>
> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
> >> >I have table T1 and I am trying to extract some date from T1 table based
> >> >on
> >> > inserts, updates and deletes. The destination table T2 has three fields
> >> > F1,
> >> > F2, F3, so I need something like
> >> >
> >> > Insert into T2 (F1, F2, F3)
> >> > Select (F1, F2,Type)
> >> > From T1
> >> >
> >> > Type should be defined based on Insert, Update, or Delete. This will be
> >> > my
> >> > first trigger, Can anyone write this trigger for me?
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Triggers have access to 2 virtual tables, [inserted] and [deleted]. They are
identical in schema to the action table.
If there is an INSERT, [inserted] will have row(s) and [deleted] will be
empty.
If there is an UPDATE, both will have rows,
[deleted] has the state of the data before the UPDATE, and
[inserted] has the state of data after the UPDATE
If there is a DELETE, [deleted] will have row(s) and [inserted] will be
empty.
So you check both tables to determine what action fired the Trigger.
You probably should refer to Books on Line for additional information.
--
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:EF507C01-C12B-439B-B9A9-856113703E38@.microsoft.com...
> Thansk Arnie. Does this do update and insert safely? how can I do deleted?
> CREATE TRIGGER trMyTrigger ON T1
> FOR INSERT, UPDATE
> AS
> if exists (select * from inserted)
> Insert Into T2(ID, Name,TrType)
> Select ID, Name,'Insert'
> From inserted
> Where Name='TestData'
> else
> Update t
> Set t.Name=i.Name, t.TrType='Update'
> From inserted i INNER JOIN T2 t on i.ID=t.ID
> GO
>
> "Arnie Rowland" wrote:
>> Yes, inside the Trigger you will be able to determine if it is executing
>> because of INSERT, UPDATE, DELETE.
>> If you are exploring some form of auditing, the archive table 'should'
>> have
>> additional columns for the user and current date/time. Those are also
>> available internal to the Trigger.
>> Let us know how to help you when you're closer to the need.
>> --
>> Arnie Rowland
>> "To be successful, your heart must accompany your knowledge."
>>
>> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
>> > Hi Arnie,
>> >
>> > That was just to simplify the case. I do not have actual table
>> > definitions
>> > yet. Type is what I am trying to determine in the trigger. This will be
>> > INSER,UPDATE,DELETE trigger, can I somehow figure it out in the
>> > trigger?
>> >
>> >
>> >
>> > "Arnie Rowland" wrote:
>> >
>> >> I would NEVER write any code that had table names like T1 or T2, and
>> >> columns
>> >> named F1, F2, F3. Is this real (and I hope not), or do you have table
>> >> DDL
>> >> that you can post?
>> >>
>> >> Give us more to work with, and you may get some good assistance here.
>> >>
>> >> For example, the the code snipplet, is TYPE a column name, a literal,
>> >> a
>> >> varable? Where did it come from?
>> >>
>> >> --
>> >> Arnie Rowland*
>> >> "To be successful, your heart must accompany your knowledge."
>> >>
>> >>
>> >>
>> >> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
>> >> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>> >> >I have table T1 and I am trying to extract some date from T1 table
>> >> >based
>> >> >on
>> >> > inserts, updates and deletes. The destination table T2 has three
>> >> > fields
>> >> > F1,
>> >> > F2, F3, so I need something like
>> >> >
>> >> > Insert into T2 (F1, F2, F3)
>> >> > Select (F1, F2,Type)
>> >> > From T1
>> >> >
>> >> > Type should be defined based on Insert, Update, or Delete. This will
>> >> > be
>> >> > my
>> >> > first trigger, Can anyone write this trigger for me?
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||Thanks Tracy, this helps a lot.
"Tracy McKibben" wrote:
> JIM.H. wrote:
> > I have table T1 and I am trying to extract some date from T1 table based on
> > inserts, updates and deletes. The destination table T2 has three fields F1,
> > F2, F3, so I need something like
> >
> > Insert into T2 (F1, F2, F3)
> > Select (F1, F2,Type)
> > From T1
> >
> > Type should be defined based on Insert, Update, or Delete. This will be my
> > first trigger, Can anyone write this trigger for me?
> >
> >
> >
> >
> I'm guessing this is some sort of audit mechanism? If so, lots of info
> available online regarding auditing, my friend Google found this one for me:
> http://www.nigelrivett.net/AuditTrailTrigger.html
> Incidentally, this type of activity, if done poorly, can cause BLOCKING
> on updates and inserts. Just tossing that out there, since you've been
> complaining all week about deadlocks.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

q; a trigger code

I have table T1 and I am trying to extract some date from T1 table based on
inserts, updates and deletes. The destination table T2 has three fields F1,
F2, F3, so I need something like
Insert into T2 (F1, F2, F3)
Select (F1, F2,Type)
From T1
Type should be defined based on Insert, Update, or Delete. This will be my
first trigger, Can anyone write this trigger for me?I would NEVER write any code that had table names like T1 or T2, and columns
named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
that you can post?
Give us more to work with, and you may get some good assistance here.
For example, the the code snipplet, is TYPE a column name, a literal, a
varable? Where did it come from?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>I have table T1 and I am trying to extract some date from T1 table based on
> inserts, updates and deletes. The destination table T2 has three fields
> F1,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>|||Hi Arnie,
That was just to simplify the case. I do not have actual table definitions
yet. Type is what I am trying to determine in the trigger. This will be
INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
"Arnie Rowland" wrote:

> I would NEVER write any code that had table names like T1 or T2, and colum
ns
> named F1, F2, F3. Is this real (and I hope not), or do you have table DDL
> that you can post?
> Give us more to work with, and you may get some good assistance here.
> For example, the the code snipplet, is TYPE a column name, a literal, a
> varable? Where did it come from?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:27DE8EE3-67B8-4013-ACE5-81AA58C60D22@.microsoft.com...
>
>|||JIM.H. wrote:
> I have table T1 and I am trying to extract some date from T1 table based o
n
> inserts, updates and deletes. The destination table T2 has three fields F1
,
> F2, F3, so I need something like
> Insert into T2 (F1, F2, F3)
> Select (F1, F2,Type)
> From T1
> Type should be defined based on Insert, Update, or Delete. This will be my
> first trigger, Can anyone write this trigger for me?
>
>
I'm guessing this is some sort of audit mechanism? If so, lots of info
available online regarding auditing, my friend Google found this one for me:
http://www.nigelrivett.net/AuditTrailTrigger.html
Incidentally, this type of activity, if done poorly, can cause BLOCKING
on updates and inserts. Just tossing that out there, since you've been
complaining all week about deadlocks.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Yes, inside the Trigger you will be able to determine if it is executing
because of INSERT, UPDATE, DELETE.
If you are exploring some form of auditing, the archive table 'should' have
additional columns for the user and current date/time. Those are also
available internal to the Trigger.
Let us know how to help you when you're closer to the need.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...[vbcol=seagreen]
> Hi Arnie,
> That was just to simplify the case. I do not have actual table definitions
> yet. Type is what I am trying to determine in the trigger. This will be
> INSER,UPDATE,DELETE trigger, can I somehow figure it out in the trigger?
>
> "Arnie Rowland" wrote:
>|||Thansk Arnie. Does this do update and insert safely? how can I do deleted?
CREATE TRIGGER trMyTrigger ON T1
FOR INSERT, UPDATE
AS
if exists (select * from inserted)
Insert Into T2(ID, Name,TrType)
Select ID, Name,'Insert'
From inserted
Where Name='TestData'
else
Update t
Set t.Name=i.Name, t.TrType='Update'
From inserted i INNER JOIN T2 t on i.ID=t.ID
GO
"Arnie Rowland" wrote:

> Yes, inside the Trigger you will be able to determine if it is executing
> because of INSERT, UPDATE, DELETE.
> If you are exploring some form of auditing, the archive table 'should' hav
e
> additional columns for the user and current date/time. Those are also
> available internal to the Trigger.
> Let us know how to help you when you're closer to the need.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:86BDF37C-B673-473B-9808-36609153D58F@.microsoft.com...
>
>|||Triggers have access to 2 virtual tables, [inserted] and [deleted].
They are
identical in schema to the action table.
If there is an INSERT, [inserted] will have row(s) and [deleted] wil
l be
empty.
If there is an UPDATE, both will have rows,
[deleted] has the state of the data before the UPDATE, and
[inserted] has the state of data after the UPDATE
If there is a DELETE, [deleted] will have row(s) and [inserted] will
be
empty.
So you check both tables to determine what action fired the Trigger.
You probably should refer to Books on Line for additional information.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:EF507C01-C12B-439B-B9A9-856113703E38@.microsoft.com...[vbcol=seagreen]
> Thansk Arnie. Does this do update and insert safely? how can I do deleted?
> CREATE TRIGGER trMyTrigger ON T1
> FOR INSERT, UPDATE
> AS
> if exists (select * from inserted)
> Insert Into T2(ID, Name,TrType)
> Select ID, Name,'Insert'
> From inserted
> Where Name='TestData'
> else
> Update t
> Set t.Name=i.Name, t.TrType='Update'
> From inserted i INNER JOIN T2 t on i.ID=t.ID
> GO
>
> "Arnie Rowland" wrote:
>|||Thanks Tracy, this helps a lot.
"Tracy McKibben" wrote:

> JIM.H. wrote:
> I'm guessing this is some sort of audit mechanism? If so, lots of info
> available online regarding auditing, my friend Google found this one for m
e:
> http://www.nigelrivett.net/AuditTrailTrigger.html
> Incidentally, this type of activity, if done poorly, can cause BLOCKING
> on updates and inserts. Just tossing that out there, since you've been
> complaining all week about deadlocks.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>