Help Needed With Trigger
Feb 17, 2006
Hi All,
I have this trigger wich runs good!
CREATE trigger trUpdateGEOData
on dbo.BK_Machine
after insert, update
as
updateBK_Machine
setBK_Machine.LOC_Street = GEO_Postcode.STraatID,
BK_Machine.Loc_City = GEO_Postcode.PlaatsID
fromBK_Machine
inner join GEO_Postcode on BK_Machine.loc_postalcode = GEO_Postcode.postcode
and BK_Machine.LOC_Doornumber >= GEO_Postcode.van
and BK_Machine.LOC_Doornumber <= GEO_Postcode.tem
inner join inserted on BK_Machine.MachineID = Inserted.MachineID
Now the thing is that a machine not neccesarily needs a location which mean that if postalcode or doornumber is NULL this trigger should clear the street and city columns.
Does someone have an Idea?
Cheers Wim
View 1 Replies
ADVERTISEMENT
Dec 3, 2007
I'm new to sql server (2000)
and I have to make some trigger before it will be too late :(
I don't have much time to learn it right now. but I'll defiantly do this some time.
I have 2 tables. tbl_A and tbl_B . in tbl_A I have a field named 'money'
and another field 'name' .I need to make a trigger that will run whenever the money field updated.
the update should be like that:
10% of the money that are in the update statement goes to every row in tbl_A where name='<name that used in update query>'. in addition, the 10% and the name goes to tbl_B
View 3 Replies
View Related
Mar 11, 2005
Does anyone have code for a trigger that when the user deletes the record trigger it to insert into another table? Thanks in advance and appreciate your help!
View 4 Replies
View Related
Nov 1, 2007
Hello,
It is rare that I have a user for Triggers, but I have a special case. In a table named tbl_PaymhistCurrentWeek, I have a column called 'AcctCode'. This column will contain data that will be derived from another column called AcctCodeWithZeros. AcctCodeWithZeros contains an account code with leading zeros. The column AcctCode needs to contain the value in AcctCodeWithZeros, only without the leading zeros.
I tried to set up something in a Derived Column Transformation within SSIS, but I can't use PATINDEX.
Here is the latest version of the Trigger:
*****************
CREATE TRIGGER [dbo].[tr_AcctCode]
ON [dbo].[tbl_PaymhistCurrentWeek]
FOR INSERT
AS
BEGIN
INSERT tbl_PaymhistCurrentWeek
(AcctCode, Co, AccountNoWithZeros)
SELECT
id.SUBSTRING([AccountNoWithZeros], PATINDEX('%[^0]%', [AccountNoWithZeros]), 14)AS AcctCode, id.Co, id.AccountNoWithZeros
FROM Inserted id
END
****************
The default for AcctCode is an empty string, which is all I get when I insert into the table. AcctCode will be part of a primary key, so I don't think I can set up AcctCode to be a calculated column. What am I doing wrong?
The DDL for the table is as follows:
tbl_PaymhistCurrentWeek
( AcctCode varchar(10) Default ' ' Not Null,
Co char(2) Not Null,
AcctCodeWithZeros varchar(20) Null
CONSTRAINT (PK_PaymhistCurrentWeek) Primary Key Clustered
(AcctCode, Co)
)
Thank you for your help!
cdun2
View 8 Replies
View Related
Feb 3, 2000
Is it possible to have a login that can access a table, which has an Update trigger on a column, and do some updating on another column but not have the trigger fire?
I cannot disable the Trigger. This db is on production and the trigger cannot be taken off. I also cannot bcp the data out and do the updating and bcp back in. The production table must remain as is, however I need to update some cols without the trigger firing.
Any ideas??
Thank you,
tw
View 2 Replies
View Related
Nov 29, 2007
I am fairly new to using triggers and stored procs in SQL Server and could use some help.
Scenario:
I have a table on SQL Server database running on a Windows server (of course) that contains information about articles. A second server in the shop is set up as a LAMP (Linux, Apache, MySQL, PHP) box. What I would like to do is any time the SQL server table is updated or a record is added, tables in MySQL would then be updated to reflect the changes on the first server. My thought is to try and do this with triggers and a stored proc.
Questions:
1) Assuming MySQL can be accessed through ODBC, can an ODBC connection be set up inside a stored proc or trigger, or would this have to be done through the Windows ODBC Data Sources tool in the control panel?
2) Can a Stored Proc be used in a trigger?
3) Can a Stored Proc call an outside function, such as an API call for third party software?
Thanks in advance.
View 1 Replies
View Related
Feb 16, 2007
I want a stored procedure to run everytime the batch Status field is set to 'Released'
<Code>
CREATE TRIGGER [CSITSS].[tdecker].[FB401BV_TRIGGER] ON [CSITSS].[dbo].[Fbbatch] FOR UPDATE [Status]
AS
DECLARE @RC int
DECLARE @Batch int
SET @Batch = (??? BATCH NUMBER THAT WAS SET TO RELEASE ???)
EXEC @RC = [CSITSS].[tdecker].[GLP_FB401BV_BATCH] @Batch
</Code>
View 9 Replies
View Related
Jun 19, 2008
Hi,
I have a query regarding DML Triggers.
I have created a trigger ON a table AFTER Insert, Update staement. In the trigger's body I am making a call to a stored procedure. In the procedure, I want to know the Trigger invoked due to which event : insert or Update as I have to handle bith the cases differently.
But I am not able to find out any SQL server function or property which can distinguish between INSERT and Update events of a trigger.
Waiting for the needful.
View 4 Replies
View Related
Jun 19, 2008
Hi,
I have a query regarding DML Triggers.
I have created a trigger ON a table AFTER Insert, Update staement. In the trigger's body I am making a call to a stored procedure. In the procedure, I want to know the Trigger invoked due to which event : insert or Update as I have to handle bith the cases differently.
But I am not able to find out any SQL server function or property which can distinguish between INSERT and Update events of a trigger.
Waiting for the needful.
View 2 Replies
View Related
Nov 14, 2006
Hey,
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with:
WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc.
Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below)
Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending
Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government]
Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc.
Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies.
Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Thanks.
View 4 Replies
View Related
Jul 20, 2005
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View 1 Replies
View Related
Dec 5, 2006
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called
'Audit'
with columns
'ActionType' //varchar
'TableName' //varchar
'PK' //varchar
'FieldName' //varchar
'OldValue' //varchar
'NewValue' //varchar
'ChangeDateTime' //datetime
'ChangeBy' //varchar
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public partial class Triggers
{
//A Generic Trigger for Insert, Update and Delete Actions on any Table
[Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger()
{
SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context
string TName; //Where we store the Altered Table's Name
string User; //Where we will store the Database Username
DataRow iRow; //DataRow to hold the inserted values
DataRow dRow; //DataRow to how the deleted/overwritten values
DataRow aRow; //Audit DataRow to build our Audit entry with
string PKString; //Will temporarily store the Primary Key Column Names and Values here
using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection
{
conn.Open();//Open the Connection
//Build the AuditAdapter and Mathcing Table
SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn);
DataTable AuditTable = new DataTable();
AuditAdapter.FillSchema(AuditTable, SchemaType.Source);
SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us
//Get the inserted values
SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn);
DataTable inserted = new DataTable();
Loader.Fill(inserted);
//Get the deleted and/or overwritten values
Loader.SelectCommand.CommandText = "SELECT * from DELETED";
DataTable deleted = new DataTable();
Loader.Fill(deleted);
//Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire)
SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM
ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn);
TName = cmd.ExecuteScalar().ToString();
//Retrieve the UserName of the current Database User
SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn);
User = curUserCommand.ExecuteScalar().ToString();
//Adapted the following command from a T-SQL audit trigger by Nigel Rivett
//http://www.nigelrivett.net/AuditTrailTrigger.html
SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = '" + TName + @"'
and CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn);
DataTable PKTable = new DataTable();
PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table
{
case TriggerAction.Update:
iRow = inserted.Rows[0];//Get the inserted values in row form
dRow = deleted.Rows[0];//Get the overwritten values in row form
PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string
foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns
{
if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "U";//U for Update
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry
}
}
break;
case TriggerAction.Insert:
iRow = inserted.Rows[0];
PKString = PKStringBuilder(PKTable, iRow);
foreach (DataColumn column in inserted.Columns)
{
//Build an Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "I";//I for Insert
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = null;
aRow["NewValue"] = iRow[column.Ordinal].ToString();
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
case TriggerAction.Delete:
dRow = deleted.Rows[0];
PKString = PKStringBuilder(PKTable, dRow);
foreach (DataColumn column in inserted.Columns)
{
//Build and Audit Entry
aRow = AuditTable.NewRow();
aRow["ActionType"] = "D";//D for Delete
aRow["TableName"] = TName;
aRow["PK"] = PKString;
aRow["FieldName"] = column.ColumnName;
aRow["OldValue"] = dRow[column.Ordinal].ToString();
aRow["NewValue"] = null;
aRow["ChangeDateTime"] = DateTime.Now.ToString();
aRow["ChangedBy"] = User;
AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry
}
break;
default:
//Do Nothing
break;
}
AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable
conn.Close(); //Close the Connection
}
}
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values
//and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......"
public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow)
{
string temp = String.Empty;
foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed
{
temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,"));
}
return temp;
}
}
The trick was getting the Table Name and the Primary Key Columns.
I hope this code is found useful.
Comments and Suggestion will be much appreciated.
View 16 Replies
View Related
Oct 30, 2007
Table 1
First_Name
Middle_Name
Surname
John
Ian
Lennon
Mike
Buffalo
Tyson
Tom
Finney
Jones
Table 2
ID
F
M
S
DOB
1
Athony
Harold
Wilson
24/4/67
2
Margaret
Betty
Thathcer
1/1/1808
3
John
Ian
Lennon
2/2/1979
4
Mike
Buffalo
Tyson
3/4/04
5
Tom
Finney
Jones
1/1/2000
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
View 17 Replies
View Related
Feb 5, 2008
A
ID
Name
1
Joe
2
Fred
3
Ian
4
Bill
B
ID
1
4
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger
on a
for insert
as
insert into b(ID)
select i.id
from inserted i
where
--specific USER
View 9 Replies
View Related
Mar 18, 2008
how to create new CLR trigger from existing T-Sql Trigger Thanks in advance
View 3 Replies
View Related
Jul 23, 2005
When a row gets modified and it invokes a trigger, we would like to beable to update the row that was modified inside the trigger. This is(basically) how we are doing it now:CREATE TRIGGER trTBL ON TBLFOR UPDATE, INSERT, DELETEasupdate TBLset fld = 'value'from inserted, TBLwhere inserted.id= TBL.id....This work fine but it seems like it could be optimized. Clearly we arehaving to scan the entire table again to update the row. But shouldn'tthe trigger already know which row invoked it. Do we have to scan thetable again for this row or is their some syntax that allows us toupdate the row that invoked the trigger. If not, why. It seems likethis would be a fairly common task. Thanks.
View 4 Replies
View Related
Jul 20, 2005
Salve, non riesco a disabilitare un trigger su sqlserver nč da queryanalyzer, nč da enterprise manager.In pratica tal cosa riuscivo a farla in Oracle con TOAD, mentre qui nonriesco.Mi interessa disattivarlo senza cancellarlo per poi riattivarlo al bisognosenza rilanciare lo script di creazione.Grazie a tuttiHi I need to disable a DB trigger and I'm not able to do this neither withquery analyzer, neither with enterprise manager.I remeber this job was quite simple using TOAd in Oracle.I'm interested in making it disabled not delete it, without run creationscript.Thanks a lot to everybody.
View 4 Replies
View Related
May 30, 2008
Hi,
I am not sure if this is the right forum to post this question.
I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio.
When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H".
This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table.
What else would cause the database automatically change my update?
Could there be any other place I should look for an update trigger on this table?
Thanks,
View 3 Replies
View Related
Sep 20, 2007
Hi all in .net I've created an application that allows creation of triggers, i also want to allow the deletion of triggers.
The trigger name is kept in a table, and apon deleting the record i want to use the field name to delete the trigger
I have the following Trigger
the error is at
DROP TRIGGER @DeleteTrigger
I'm guessing it dosen't like the trigger name being a variable instead of a static name
how do i get around this?
thanks in advance
-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER RemoveTriggers
ON tblTriggers
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @DeleteTrigger as nvarchar(max)
select @DeleteTrigger = TableName FROM DELETED
IF OBJECT_ID (@DeleteTrigger,'TR') IS NOT NULL
DROP TRIGGER @DeleteTrigger
GO
END
GO
View 7 Replies
View Related
Jan 23, 2008
Hi,
I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out
and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up
Any thoughts?
Here is my t-sql
Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)
select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d
where i.req_guid = d.req_guid
and i.req_fname <> d.req_fname
Thanks,
leo
View 7 Replies
View Related
Sep 24, 2006
Im new to stored procedure, but here's what I want, if someone can get me started and provide this as an example it would be VERY welcome!I have the following, a SP with parameter IsMale. This parameter may be empty. If it's not empty I want to add some text to my selection query: AND IsMale=paramvalueHere's the SP so far:ALTER PROCEDURE [dbo].[spFindUsersAdvanced] --declare parameters here@IsMale bitASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT DISTINCT BirthDate,IsMale, FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID WHERE tblUserData.Username<>''IF @IsMale THENBEGIN AND IsMale='True'ENDELSEBEGIN AND IsMale='False'ENDEND IFEND1. How do I check if the parameter is empty?2. How do I add the text to my selection query?
View 3 Replies
View Related
Oct 30, 2007
Hi all,I am trying to write a stored procedure, which has two insert statements.the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?thanks for your help!
View 10 Replies
View Related
Dec 1, 2004
Hi,
For many to many relationship, what is the minimum number of tables required.
View 1 Replies
View Related
Oct 17, 2005
Hi plz read below is my scenarioI have two diff tables which stores passwords (don't ask why b'coz it was there).Now to make data in both table consistance what I need to do?plz provide optimized solution.Thanks in advance.bye bye.happy coding!!!
View 1 Replies
View Related
Dec 20, 2000
Experts,
I've few questions regarding SQL Server. Appreciated, If any one out there help me with it.
I would like to do T-SQL Programing and would like to know some good book. I'll mostly be doing Stored procedures,Triggers and views programming. Any advice?
On my sql server I've 10 logins. But, I cannot view these logins from the Enterprise GUI. But, When i run a query against the syslogin table, those all 10 logins shows up. Why is that?
any help would be greatly appreciated.
View 1 Replies
View Related
Mar 25, 2004
Access 97.................
I'm trying to construct the following SQL statement but am missing something somewhere.
I get a syntax error with the 1st FROM highlighted.
If i construct a query out of the bit that works and then construct a query on that query then i can get it to work no problem but how to mimic that in SQL ? I can't simply view the SQL of the queries because the query names replace the SQL statements.
I'm trying to SUM the 5 volumes that get returned by the TOP 5 SQL statement. The VotingUniverse_TotalVolumeByType query returns the TotalVolume which will enable me to divide the SUMmed 5 volumes by the TotalVolume to obtain a %age.
SELECT
Type,
Sum(Volume) AS Volume,
TotalVolume
FROM
>>>>>>>>>>this following bit works............
(SELECT TOP 5
VotingUniverse_TopFirmsVolumeByType.Type,
VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
WHERE
(((VotingUniverse_TopFirmsVolumeByType.Volume)
In (SELECT TOP 5 VotingUniverse_TopFirmsVolumeByType.Volume
FROM
VotingUniverse_TopFirmsVolumeByType
ORDER BY Volume DESC)))
ORDER BY
VotingUniverse_TopFirmsVolumeByType.Volume;)
>>>>>>>>>>>end of bit that works
INNER JOIN
VotingUniverse_TotalVolumeByType
ON
Type = VotingUniverse_TotalVolumeByType.Type
GROUP BY
Type,
TotalVolume;
hope this makes sense to someone anyway
thanks
====
Paul
View 5 Replies
View Related
Aug 15, 2002
I have a xxx.exe running on SQL 2000 server as a sql job every hour. For some reason this job sometime just hung in the middle and never finish. But
if I stop it and rerun it, it will be down within 10 seconds. I am wondering
if we can set up something so that the job aborts automatically if it ran more than 10 minutes.
View 1 Replies
View Related
Feb 22, 2001
Hi everyone.
We are running Sql 6.5 sp5 on a Pentium II 350 Mhz with 512 Mb. RAM, 1 ScSi 4 Gb hd, one 8Gb ScSi hd and 2 IDE hd (13,6 Gb and 8Gb) computer running windows NT 4.0 sp 6 without any problems.
Now have bought a new computer. The new one is a Pentium III 733 Mhz 512 Mb Ram with 2 ScSi ultra wide 2 18Gb each and one 40 Gb Ide Hd. We have installed windows NT 4.0 sp 6 and Sql 6.5 sp 5. We have restored our database to make some tests to it. The database is 7 Gb big.
The results of our test are that the old computer is between 20% and 30% faster than the new one when doing sql instructions and using the database even knowing that the hd´s of the new computers are 4 times faster than the old ones.
Would you please give us any help or clue that would make the new computer go faster?
We know that upgrading to sql 7 will work but we need first to make this computer work better with the 6.5.
Thank you.
View 3 Replies
View Related
Jun 18, 2004
Hello,
When I try to run my asp file it gives me this error. :mad:
Technical Information (for support personnel)
Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E07)
Syntax error converting datetime from character string.
/conOpen_inc.asp, line 10
Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)
Page:
GET /Default.asp
Anyone can tell me whats happening ? thank you
View 5 Replies
View Related
Oct 12, 2004
I often use DTS to move databases between the servers. When you choose "Transform" data and “Column Mappings and Transformation” window opens, the default option is “Append rows to destination table”.
Question: Is there a way to change the default option to “Delete rows in destination table”, so I wouldn’t have to go thru the transformation of dozens of tables every time I move databases around.
P.S. I know that I can save DTS package with my settings and use it later. The problem is that every time it might be different database or objects might change. So, it is not a solution for me.
Thanks
View 8 Replies
View Related
Jul 25, 2007
Dear All,
I have a client requirement. We are integrating two applications. So the problem is that, whenever some updates (meaning to say "create" , "delete" , "update") happens in the SQL DB Tables, i needed to track that data and send it to a different system.
What I have thought abt is that if SQL could generate a file with all the values deleted/updated/created in the DB, then I could take those values and do the needful. Please help me..
Thanks,
Sanjo
View 2 Replies
View Related
Feb 28, 2004
Hello guys,
I'm new to the forum and to MS SQL 2K.
I'm trying to a merge similar rows in a table into a single row and put them in a new table.
Example:-
This is my input table
TableA
ID A B C
------------------------
1 jk kl bj
2 sd we op
3 io po kl
1 ui gh ew
2 kl re op
1 qw kj nn
My output table should look like this
TableB
ID A1 B1 C1 A2 B2 C2 A3 B3 C3
-----------------------------------------------------
1 jk kl bj ui gh ew qw kj nn
2 sd we op kl re op
3 io po kl
Please help me on how to create my output.
Thanks in advance,
Sid.
View 11 Replies
View Related
Mar 26, 2004
Hi,
I have a table structure like this
TableName: Common
Columns
PartnerId: int
NativeId: int
FirstName: nvarchar(50)
LastName:nvarchar(50)
1)I should get the records with a minimum native id for a particular PartnerId,
2) if duplicates exists in the above condition i should select top 1 (first record)
How can i do it??
TIA,
sudheer
View 5 Replies
View Related
