Please find the necessary SQL scripts to generate a small version of my database and some data at the bottom of this post.
Here's a short description of what the database is all about: It's a project tracking and management system. Contracts go into the tblDeals table. Because each project may be different in nature, project phases are defined in tblPhaseType and tblPhase tables. The table used to keep track of what's going on is the tblProduction table.
Here's what I need to do. When a project is completed -- meaning it has gone through all the phases that it needs to go through -- I want a trigger to fire up and change the contract status in the tblDeals table to "Completed" whose value is 1. When a new contract gets entered into the table, the Contract Status is set to 5 by default which means "In Progress" -- as defined in tblContractStatus. The tricky part is that because, each project is different and has different number of phases, the trigger has to make sure that all the phases have been submitted into the tblProduction table for that particular deal.
I'd really appreciate some help here. Thanks in advance for all your help.
---------------------------------------
Here's the script
---------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblCompany]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblCompany
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblContractStatus]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblContractStatus
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblDeals_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDeals] DROP CONSTRAINT FK_tblDeals_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblPhase_tblPhaseType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblPhase] DROP CONSTRAINT FK_tblPhase_tblPhaseType
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblDeals]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblDeals
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblProduction_tblPhase]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblProduction] DROP CONSTRAINT FK_tblProduction_tblPhase
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblProduction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblProduction]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDeals]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblDeals]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhase]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhase]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCompany]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCompany]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblContractStatus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblContractStatus]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:48 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblPhaseType]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblPhaseType]
GO
/****** Object: Table [dbo].[tblCompany] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblCompany] (
[CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblContractStatus] Script Date: 11/20/2003 11:30:50 AM ******/
CREATE TABLE [dbo].[tblContractStatus] (
[StatusID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Status] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhaseType] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblPhaseType] (
[PhaseTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Desription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblDeals] Script Date: 11/20/2003 11:30:51 AM ******/
CREATE TABLE [dbo].[tblDeals] (
[DealID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyID] [int] NOT NULL ,
[DealDate] [smalldatetime] NOT NULL ,
[PhaseTypeID] [tinyint] NOT NULL ,
[CashAmount] [smallmoney] NOT NULL ,
[StatusID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblPhase] Script Date: 11/20/2003 11:30:52 AM ******/
CREATE TABLE [dbo].[tblPhase] (
[PhaseID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[PhaseTypeID] [tinyint] NOT NULL ,
[PhaseDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PhasePercentage] [float] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[tblProduction] Script Date: 11/20/2003 11:30:52 AM ******/
exec sp_addextendedproperty N'MS_Description', N'Determines the type of phase structure this deal will go through', N'user', N'dbo', N'table', N'tblDeals', N'column', N'PhaseTypeID'
GO
exec sp_addextendedproperty N'MS_Description', N'Identifies the current status of deal', N'user', N'dbo', N'table', N'tblDeals', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Determines the percentage value of the phase', N'user', N'dbo', N'table', N'tblPhase', N'column', N'PhasePercentage'
INSERT INTO [tblPhaseType] ([Desription])VALUES('TV Commercial - 4 Phases')
INSERT INTO [tblPhaseType] ([Desription])VALUES('Full Campaign - 6 Phases')
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Customer Info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Write script',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Shoot',3.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(1,'Edit commercial',2.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Customer info',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Write script',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Design print ad',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Shoot',1.500000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Edit',2.000000000000000e-001)
INSERT INTO [tblPhase] ([PhaseTypeID],[PhaseDescription],[PhasePercentage])VALUES(2,'Publish',2.000000000000000e-001)
INSERT INTO [tblContractStatus] ([Status])VALUES('Completed')
INSERT INTO [tblContractStatus] ([Status])VALUES('Hold')
INSERT INTO [tblContractStatus] ([Status])VALUES('Collections')
INSERT INTO [tblContractStatus] ([Status])VALUES('Legal')
INSERT INTO [tblContractStatus] ([Status])VALUES('In Progress')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Johnny''s Remodeling')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Perfect Cut Lawncare')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Useless Ideas Unlimited')
INSERT INTO [tblCompany] ([CompanyName])VALUES('Try-It-Again, Inc.')
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(1,'Aug 5 2003 12:00:00:000AM',1,120.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(2,'Sep 9 2003 12:00:00:000AM',2,150.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(3,'Sep 10 2003 12:00:00:000AM',2,130.0000,5)
INSERT INTO [tblDeals] ([CompanyID],[DealDate],[PhaseTypeID],[CashAmount],[StatusID])VALUES(4,'Nov 20 2003 12:00:00:000AM',1,190.0000,5)
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,1,'Nov 10 2003 10:23:00:000AM','Received company logo')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,2,'Nov 10 2003 10:23:00:000AM','Finished writing script')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,5,'Nov 10 2003 10:23:00:000AM','Just received company info')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(2,7,'Nov 10 2003 10:24:00:000AM','Finished designing ad copy')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,3,'Nov 20 2003 11:29:00:000AM','Did more work')
INSERT INTO [tblProduction] ([DealID],[PhaseID],[TransactionTimeStamp],[Comments])VALUES(1,4,'Nov 20 2003 11:29:00:000AM','Finally finished the job')
Any help appreciated! Is there any performance enhancements to be gained by storing frequently 'trigger-written-to' databases on a seperate disk to the source database? In particular, we keep a 'history' database of all inserts/updates/deletes against records, activated by triggers, and I was wondering if I would gain performance enhancement by locating the two databases on different disks? Thanks in advance
Suppose also that the ACTIVITY table is already populated with several records, say with activity_id = 1, 2, and 3.
OBJECTIVE: When a new member record is added to MEMBER, say member_id 10, insert one record in the HEADCOUNT table for EACH activity in ACTIVITY for that member. Thus, if member #10 is added to MEMBER, then the trigger (or some other mechanism) would add the following records to HEADCOUNT (which, say, already has 30 records):
I've been advised that a trigger should do the trick for this, but as I'm totally new to SQL, I'll need some help. I'm guessing some iterating SQL command language might be required, but as I'm new to SQL, I don't know how to proceed.
Note that I'm building an ASP.NET application based on VB, and so records will be added to MEMBER through a tableadapter INSERT command. (Though I suspect this has no bearing on trigger behavior.)
I am developing an application in vb.net 2005 using SQL Server 2000. In this I have two tables SessionMaster and SessionChild. Fields of session master - SessionMastId, Start_Date, End_Date, Session_Type, Fields of session child - SessionChildId, SessionMastId, UserName, Comment. SessionMastId and SessionChildId are primary keys of respective tables and also they are auto increment fields. Please how to write trigger to insert record into both tables at a time.
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.
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'
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.
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.
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)
I have some experience with MS SQL mostly installation, configuration, maintaining, etc. But I am trying to teach myself some TSQL. I have bought a few beginner books and have some test machines. In advance, I appreciate any feedback!
I have a database with two tables. I want to calc the min salary for a class of employees. The "wage" is in one table and the "status" is in another.
USE Database GO SELECT MIN(Wage) AS "Min Salary", Status FROM Employee, Job WHERE Status = '1' GROUP BY Wage, Status GO
The result set brings back all not the "status" class that I want.
I wrote a stored procedure in SQL 2K5, and I would like to write to the sql log without using a rasieerror function. I just would like to log the sucessfull steps without exiting the proc. I am not sure how to do this. Any help would be appreciated.
I am following the: Lesson 8: Obtaining Data from a SQL Server 2005 Express Edition Database video series. I cannot get the following to write changes to the SQL Database:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
I do get the message box to popup showing 1 row of changes when I make one change, however that change does not get written to the database. Is this enough info to have any idea why?
I was hoping that someone might be able to help a SQL statement for this, if possible. I have two tables, InvoiceDetails and InvoiceLineItems. What I'd like to do is write a query that will return all invoices, but also return a value InvoiceTotal Based on SUM(InvoiceLineItems.qty * InvoiceLineItems.unitPrice). I've tried a few different methods of writing this, but haven't had much luck. Can anyone shed some light on this for me? Thanks.
Hi.I realize this may have been asked a thousand times before, but it's still not working for me. I would appreciate any help with it. First, I created a GridView, and from it I created a new SqlDataSource and let it point to the database in C:My ProjectApp_DataASPNETDB.MDF. This automatically created a connectionString in the Web.Config file, saying the following: "R1ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename="C:My ProjectApp_DataASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" /> Now when I try to programatically use that connection string, the program crashes. Below is my code: <script runat="server"> String sqlConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename="C:My ProjectApp_DataASPNETDB.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True";
protected void Page_Load(object sender, EventArgs e) { using (SqlConnection conn = new SqlConnection(sqlConnectionString)) { conn.Open(); const String selectQuery = "SELECT StatusName FROM Status ORDER BY StatusName"; Label1.Text = ""; using (SqlCommand cmd = new SqlCommand(selectQuery, conn)) { SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { Label1.Text += dr[0]; while (dr.Read()) { Label1.Text += ", "; Label1.Text += dr[0]; } Label1.Text += ". "; } else Label1.Text = "None."; } if (conn != null) conn.Close(); } } Here's the error message I'm getting: Server Error in '/My Project' Application.
Keyword not supported: 'c:my projectapp_dataaspnetdb.mdf";integrated security'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: Keyword not supported: 'c:my projectapp_dataaspnetdb.mdf";integrated security'.
Source Error:
Line 15: protected void Page_Load(object sender, EventArgs e)Line 16: {Line 17: using (SqlConnection conn = new SqlConnection(sqlConnectionString))Line 18: {Line 19: conn.Open();
Source File: c:My Project est.aspx Line: 17
Stack Trace:
[ArgumentException: Keyword not supported: 'c:my projectapp_dataaspnetdb.mdf";integrated security'.] System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +417 System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) +99 System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +52 System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +25 System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) +141 System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) +38 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +4 System.Data.SqlClient.SqlConnection..ctor(String connectionString) +21 ASP.test_aspx.Page_Load(Object sender, EventArgs e) in c:My Project est.aspx:17 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745 My question is: How should I write the connection string? I have a feeling the problem is with the quotes ("), but I can't figure out how to write it otherwise.Thank you very much.
Hello, I have two tables: Customer Training Requests has say two columns.Customer - Class Requested a 1b 1c 1a 2b 2c 3a 3 I have another table called packages, which has packages of class.Package No. Class1 11 2 I want to write a query which will return only those customers who have requested exactly the classes in a particular package.so in above example if the package is 1 the query should return only customer b as only customer b has requested class 1 and 2. Any suggestions? Thanks,Paresh.
Hello, how can do the following using sql: if exists(select * from siteindex where site_url like '%msn.com')ThenUpdate siteindex Set dmodified = GetDate(), isFullPage = 0, fp_flname = 'filename', fp_iWidth = 800, fp_iheight = 600 Where site_url = '%msn.com' else insert into siteindex (site_url, dcreated, dmodified, isFullPage, fp_flname, fp_iWidth, fp_iHeight) Values ('msn.com', GetDate(), GetDate(), 0, 'filename', 800, 600) I cant get the syntax right. Thanks!
Hi all, I have a problem with retrieving data from lookup tables. Ok here is the issue I have a Tables ... Tables | Schools StuAccount Fields | Sch_Id (pk) Student_ID (pk) School_Name AccountableSch_Id DistrictName TestedSch_Id ParticipatedSch_Id Ok, so i have two tables Schools and StuAccount, each has one primary key and and the StuAccount has three foreign keys that associates to the Schools TABLE which has a the definition of school names wich i want to retrieve for all three foreign key in the stuaccount table. Here is what i want .... Student_ID AccountableSch_Id Shool_Name( this the name of the AccountableSch_Id ) TestedSch_Id Shool_Name( this the name of the TestedSch_Id) ParticipatedSch_Id Shool_Name( this the name of the ParticipatedSch_Id) I just want to look up the id and put the school name in the final resulting join and i have no idea how to lookup the table School three time and put school name for all three foreign keys.?? Any help will be much appreciated. Thanks
i have a query and i would like to write the contents of the dataset out directly to a file and not bother with creating another temp table and then exporting it with a command with a dts to a csv file.
it is a type of reporting that i am trying to do but i just need to export the raw data i retreive from the query
Is it possible to do the following without cursors or creating an identity column:
I have a table from legacy data with ~ 1 million records. I need to insert this into the new table which has a unique varchar(11) key. For the new system this key is generated by calling a SP that returns the next key in sequence. To put the legacy data records in the same table I want to first create a new column at end of legacy data table and populate this using SQL without going thru using cursor and calling the SP for each and every record to get a unique varchar(11) key.
I have a table that has records that have multiple entries with only a slight varitation to accomodate client needs. I want to filter out the duplicates and call only one record row. How can I do this type of thing and make it work; example - SELECT * FROM clients WHERE client_id DISTINCT I know that is not correct but that is what I need. I need to get all the columns but only one record for each client using the client_id field as the source of information.????
please help me write a query: I have a user function dbo.udf_Valid which is return true or false (@SID ,@PType,@Group,@Date)
Table 1 with this info: SID PType Group 12 12 123 45 1 456
Table2 with this info: PType PType2 12 13 12 45 12 8 1 8 1 9
when I pass the data from table1 to the function I am Okay. The complexity starts when table2 is involved. I need to pass PType2 to the function if Ptype is exists in table1. If function returns true for one of the PType2 return true in all other cases false.
I need to be able to filter out duplicates that have different values in a particular field.
For example, I have a table that contains the following:
Code:
WBS1 WBS2 WBS3 Amount Section 123 13 00 475 F 123 13 00 0 L 123 21 01 125 C 123 24 03 50 L
I need to filter out the first two lines because they have the same values for the first three columns but the last column under 'Section' has different values.
Hello I'm new on sql server and I wonder if there's an specific order for writing a query's clauses (joins, and other conditions in where clause or even the order of the tables in the from clause), I mean, I have a query that takes 1 minute executing, but if I execute the same query after moving the tables in the from clause (changing the order) It takes 5 seconds, could you please tell me Why ??? and what's the best order from writing a query ?
This is the query I was talking about:
SELECT TREP_VALOR_PRECIOS.CDPRECIO, TREP_VALOR_PRECIOS.NMANO, TREP_VALOR_PRECIOS.NMSEMANA, ISNULL(DBO.FNCREP_VALORPRECIO(TREP_PRECIOS.CDPRECI O,0,TREP_VALOR_PRECIOS.NMSEMANA,TREP_VALOR_PRECIOS .NMANO),0) VRPAGO_COBRO, UN_TASASCAMBI.TASTASACAMB VRTASA_CAMBIO, TREP_PRECIOS.CDTIPO_PRECIO, TREP_VALOR_PRECIOS.SNACTIVO, 'N' SNACTIVAR FROM UN_TASASCAMBI, -- I just moved this one to the end of the from clause T2_CALENDARIO, -- I just moved this one to the end of the from clause UN_MONEDEXTRA, TREP_PRECIOS TREP_PRECIOS_BASE, TREP_VALOR_PRECIOS, TREP_PRECIOS WHERE T2_CALENDARIO.CALFECHAINICIAL BETWEEN UN_TASASCAMBI.TASFECHDESD AND UN_TASASCAMBI.TASFECHHAST AND UN_TASASCAMBI.TASCOMPANIA = UN_MONEDEXTRA.MEXCOMPANIA AND UN_TASASCAMBI.TASMONECAMB = UN_MONEDEXTRA.MEXCODIGO AND T2_CALENDARIO.CALSEMANA = TREP_VALOR_PRECIOS.NMSEMANA AND T2_CALENDARIO.CALANO = TREP_VALOR_PRECIOS.NMANO AND T2_CALENDARIO.CALTIPOFRUTA = '01' AND UN_MONEDEXTRA.MEXCOMPANIA = TREP_PRECIOS_BASE.CDCOMPANIA AND UN_MONEDEXTRA.MEXCODIGO = TREP_PRECIOS_BASE.CDMONEDA AND TREP_PRECIOS_BASE.CDMONEDA <> 'PESOC' AND TREP_PRECIOS_BASE.CDPRECIO = TREP_VALOR_PRECIOS.CDPRECIO AND TREP_VALOR_PRECIOS.SNACTIVO = 'S' AND TREP_VALOR_PRECIOS.CDPRECIO = DBO.FNCREP_OBTENER_PRECIO_BASE(TREP_PRECIOS.CDPREC IO) AND TREP_PRECIOS.CDTIPO_PRECIO = 'F'
Now, I want search for records with employee id 1 in a way that if column emp1 contains 1 then I want the data in column emp2, and if column emp2 contains 1, then I want data in column emp1.
The output has to be a single column with no duplicate values. In the above example, for employee id 1, the output would be, 2,5 and 6 only.
This table has lakhs of records. I have to scan both columns for a given employee id. What will be the most optimized way to retrieve the data faster. Also, do I need to restructure this table for faster data retrieval? I have indexes on emp1 and emp2 columns. Do we need union here and if yes, what is the best optimized query for the same?
Mine Below Query is taking too much execution time. I am using this query in one of my sp. Is there any other way to write this query?
Declare @p_Location_Code nvarchar(10) Declare @p_ShowNearFarRate int DECLARE @p_Data_Show_Flag int
Set @p_ShowNearFarRate = 1 Set @p_Data_Show_Flag = 1
select X.Main_Party_Role , X.SET_ID, X.TradeVisionId, X.EntityId, DBO.GET_ENTITY_TICKER (X.EntityId) Ticker, X.Done, X.MatchDate, X.Main_Party_Id, X.BUYER_MNEMONIC, X.SELLER_MNEMONIC, X.Other_Party_Id, DBO.GET_DDMONYYYY_DATE_FORMAT(X.Main_Near_Dt) Main_Near_Dt, DBO.GET_DDMONYYYY_DATE_FORMAT(X.Main_Far_Dt) Main_Far_Dt, X.Amounts, 'Amount' = Case When ((ISNULL(X.Amount,0) >20) ) then Case When @p_Location_Code = 'NY44' then '20+' Else convert(nvarchar,x.Amount) End Else convert(nvarchar,x.Amount) End, X.Price, X.Duration, X.Other_Party_Bro, X.Main_Party_Bro, X.Main_Party_State, X.Other_Party_State, 'Main_Party_Near_Parse' = Case When (X.Main_Party_State=184 and X.Other_Party_State=184) THEN X.Main_Party_Near_Parse Else Case When @p_ShowNearFarRate = 1 then X.Main_Party_Near_Parse End End, 'Main_Party_Far_Parse' = Case When (X.Main_Party_State=184 and X.Other_Party_State=184) THEN X.Main_Party_Far_Parse Else Case When @p_ShowNearFarRate = 1 then X.Main_Party_Far_Parse End End, X.Main_Party_Spread BPS, 'Main_Party_Amt' = Case When @p_Location_Code <> 'NY44' then X.Main_Party_Amt Else Case When (ISNULL(X.DONE,0) = 1 or X.Main_Party_State = 184 or X.Main_Party_State = 106) then X.Main_Party_Amt Else Case When ISNULL(X.Main_Party_Amt,0) <= 20 then X.Main_Party_Amt Else 20 End End End, X.Other_Party_Near_Parse, X.Other_Party_Far_Parse, X.Other_Party_Spread, X.Other_Party_Amt, X.LOCATION_CODE, X.Color_Value Org_Color_Value, 'Color_Value' = Case When @p_Location_Code = 'NY44' then Case When X.Color_Value=500 then 500 + X.CallMe_Btn_Value Else Case When X.Color_Value>= 284 and Isnull(x.done,0) <> 1 then 284 + X.CallMe_Btn_Value Else Case When X.Color_Value=201 then 0 + X.CallMe_Btn_Value Else Case When X.Color_Value=200 then 0 + X.CallMe_Btn_Value Else Case When X.Color_Value=184 then 0 + X.CallMe_Btn_Value Else Case When X.Color_Value=106 then 16 + X.CallMe_Btn_Value Else Case When X.Color_Value=101 then 0 + X.CallMe_Btn_Value Else Case When X.Color_Value=100 then 0 + X.CallMe_Btn_Value Else Case When X.Color_Value=16 then 16 + X.CallMe_Btn_Value Else 0 + X.CallMe_Btn_Value End End End End End End End End End Else Case When X.Color_Value>=300 then 300 + X.CallMe_Btn_Value Else Case When X.Color_Value=284 then 284 + X.CallMe_Btn_Value Else Case When X.Color_Value=201 then 201 + X.CallMe_Btn_Value Else Case When X.Color_Value=200 then 200 + X.CallMe_Btn_Value Else Case When X.Color_Value=184 then 184 + X.CallMe_Btn_Value Else Case When X.Color_Value=106 then 106 + X.CallMe_Btn_Value Else Case When X.Color_Value=101 then 101 + X.CallMe_Btn_Value Else Case When X.Color_Value=100 then 100 + X.CallMe_Btn_Value Else Case When X.Color_Value=16 then 16 + X.CallMe_Btn_Value Else 0 + X.CallMe_Btn_Value End End End End End End End End End End, X.Main_Party_Strike_State, X.Other_Party_Strike_State, 'BACK_COLOR' = (DBO.Fn_Get_TradeVisionRR_Back_Color_Value (@p_Location_Code, X.Color_Value)) , 'FORE_COLOR' = (DBO.Fn_Get_TradeVisionRR_Fore_Color_Value (@p_Location_Code, X.Color_Value)) FROM ( SELECT Y.TradeVisionFileIdSET_ID, 'BUYER'Main_Party_Role, Y.TradeVisionId, Y.EntityId, Y.Done, Y.MatchDate, Y.BuyerIdMain_Party_Id, DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERId) BUYER_MNEMONIC, 'SELLER_MNEMONIC' = Case When Y.Done = 1 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID) Else Case When ( @p_Location_Code <> 'NY44' AND Y.BuyerState = 184AND Y.BuyerState = 184AND Y.IsBNearPraceTouched = 1 AND Y.IsSNearPraceTouched = 1 AND Y.IsBFarPraceTouched = 1AND Y.IsSFarPraceTouched = 1 AND Y.IsBSpreadTouched = 1 AND Y.IsSSpreadTouched = 1 AND Y.IsBAmtTouched = 1 AND Y.IsSAmtTouched= 1 AND Y.BuyerNearPrace = Y.SellerNearPrace AND Y.BuyerFarPrace = Y.SellerFarPrace AND Y.BuyerSpread = Y.SellerSpreadAND Y.BuyerAmt = Y.SellerAmT) THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID) Else Case When isnull(@p_Data_Show_Flag,1)=3 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERId) Else '*' End End End, Y.SellerIdOther_Party_Id, Y.NearDateMain_Near_Dt, Y.FarDateMain_Far_Dt, Y.AmountsAmounts, Y.AmountAmount, Y.PricePrice, Y.DurationDuration, Y.SellerBroOther_Party_Bro, Y.BuyerBroMain_Party_Bro, Y.BuyerStateMain_Party_State, Y.SellerStateOther_Party_State, Y.BuyerNearPraceMain_Party_Near_Parse, Y.BuyerFarPraceMain_Party_Far_Parse, Y.BuyerSpreadMain_Party_Spread, Y.BuyerAmtMain_Party_Amt, Y.SellerNearPraceOther_Party_Near_Parse, Y.SellerFarPraceOther_Party_Far_Parse, Y.SellerSpreadOther_Party_Spread, Y.SellerAmtOther_Party_Amt, Y.LOCATION_CODE, Y.Buyer_Strike_StateMain_Party_Strike_State, Y.Seller_Strike_StateOther_Party_Strike_State, (DBO.GET_TRADEVISIONRR_TRADEVISIONID_CUST_STATUS('NY44', 18, Y.TradeVisionId,Y.TRADEVISION_VERSION ,'BUYER' )) Color_Value , 'CallMe_Btn_Value' = Case When Y.Buyer_Strike_State = 206 then 1000 Else 0 End from DBO.TradeVisionRR Y WHERE Y.LOCATION_CODE = @p_Location_Code AND Y.TRADEVISION_VERSION = (DBO.GET_TRADEVISIONRR_SET_MAX_VERSION('NY44', '02/05/2008', 169 ,Y.TradeVisionId )) AND Y.TRADEVISIONFILEID = 169 ------------------------------------------------------------ UNION ALL ------------------------------------------------------------ select Y.TradeVisionFileIdSET_ID, 'SELLER'Main_Party_Role, Y.TradeVisionId, Y.EntityId, Y.Done, Y.MatchDate, Y.SellerIdMain_Party_Id, 'BUYER_MNEMONIC' = Case When Y.Done = 1 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID) Else Case When (@p_Location_Code <> 'NY44' AND Y.BuyerState = 184AND Y.BuyerState = 184AND Y.IsBNearPraceTouched = 1 AND Y.IsSNearPraceTouched = 1 AND Y.IsBFarPraceTouched = 1AND Y.IsSFarPraceTouched = 1 AND Y.IsBSpreadTouched = 1 AND Y.IsSSpreadTouched = 1 AND Y.IsBAmtTouched = 1 AND Y.IsSAmtTouched= 1 AND Y.BuyerNearPrace = Y.SellerNearPrace AND Y.BuyerFarPrace = Y.SellerFarPrace AND Y.BuyerSpread = Y.SellerSpreadAND Y.BuyerAmt = Y.SellerAmT) THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID) Else Case When isnull(@p_Data_Show_Flag,1)=3 THEN DBO.GET_CUSTOMER_MNEMONIC(Y.BUYERID) Else '*' End End End, DBO.GET_CUSTOMER_MNEMONIC(Y.SELLERID) SELLER_MNEMONIC, Y.BuyerIdOther_Party_Id, Y.NearDateMain_Near_Dt, Y.FarDateMain_Far_Dt, Y.AmountsAmounts, Y.AmountAmount, Y.PricePrice, Y.DurationDuration, Y.BuyerBroOther_Party_Bro, Y.SellerBroMain_Party_Bro, Y.SellerStateMain_Party_State, Y.BuyerStateOther_Party_State, Y.SellerNearPraceMain_Party_Near_Parse, Y.SellerFarPraceMain_Party_Far_Parse, Y.SellerSpreadMain_Party_Spread, Y.SellerAmtMain_Party_Amt, Y.BuyerNearPraceOther_Party_Near_Parse, Y.BuyerFarPraceOther_Party_Far_Parse, Y.BuyerSpreadOther_Party_Spread, Y.BuyerAmtOther_Party_Amt, Y.LOCATION_CODE, Y.Seller_Strike_StateMain_Party_Strike_State, Y.Buyer_Strike_StateOther_Party_Strike_State, (DBO.GET_TRADEVISIONRR_TRADEVISIONID_CUST_STATUS('NY44', 169, Y.TradeVisionId,Y.TRADEVISION_VERSION ,'SELLER' )) Color_Value , 'CallMe_Btn_Value' = Case When Y.Seller_Strike_State = 206 then 1000 Else 0 End from DBO.TradeVisionRR Y WHERE Y.LOCATION_CODE = @p_Location_Code AND Y.TRADEVISION_VERSION = (DBO.GET_TRADEVISIONRR_SET_MAX_VERSION('NY44', '02/05/2008', 169 ,Y.TradeVisionId )) AND Y.TRADEVISIONFILEID = 169 ) X where X.EntityId IN ( Select Distinct Entity_Id from Fn_Get_Allowed_Entity_List('NY44' , 169 , '02/05/2008' ,200000 )) and X.Main_Party_Id = 101901 Order By X.TradeVisionId
Help! I need to write a query that looks at one table with two columns. One column has 20K records and the second column has 25K records. I need to compare the two columns and pull out the difference between the two columns. The majority of the two columns data is the same but there are some that do not have a match between the two. The data is not in any current order. HELP! THANKS!
Hi, I need some help writing a query, and would rather not go into detail inside here. But if there is someone more advanced in SQL that could help me, could you please write me at g35gurl@yahoo.com. Thanks!
