Hello~
I am wondering if anyone can recommend a basic, "How To" article, column or book for someone who has never worked with Access but is familiar with DB programming.
I need to know how to submit queries, and print reports.
Any help is appreciated.
Thank you!
Shelli
select atleast one record where the date different is > 5 or 10 years.
I could only figure out for 5 years. so this is what I have to do.
Select top 1 Claims# where DateDiff(yy,FromDate,ThroughDate) >5
get the CalimsId of the result and do a query on that id to select all the values of the outcome id.
how i do that.
thanks
Hello, I really have a problem writing queries for the dynamic values. i follow the below mentioned method to write the queries but its really confusing. ex: str = "SELECT SO.Description,SO.DiscountPct,SO.Type,SO.Category,SO.StartDate,SO.EndDate,SO.MinQty,SO.MaxQty," + "S.Name AS ProductSubCategory,P.Name AS ProductName, C.Name AS ProductCategory FROM Production.Product P " + "INNER JOIN Production.ProductSubcategory S ON P.ProductSubcategoryID = S.ProductSubcategoryID " + "INNER JOIN Production.ProductCategory C ON S.ProductCategoryID = C.ProductCategoryID " + "INNER JOIN Sales.SpecialOfferProduct SOP ON P.ProductID = SOP.ProductID " + "INNER JOIN Sales.SpecialOffer SO ON SOP.SpecialOfferID = SO.SpecialOfferID " + "WHERE '" + txtStartDate.Text + "' between SO.StartDate AND SO.EndDate AND '" + txtEndDate.Text + "' BETWEEN SO.StartDate AND SO.EndDate " + "AND SO.Description Like '" + txtSpecialDesc.Text + "%'"; can anybody help me in writing the queries for dynamic values in an easy way. Thank you Sandeep Chavva
View 3 Replies View RelatedHiI am trying to write two Select * statements to the same text fileusing bcp (from a stored procedure).But cannot find a way of appending to a file using bcp.Does anyone know if this is possible or is there another way of writingmultiple queries to a file from a stored procedure?ThanksCaro
View 2 Replies View RelatedHello all,
Does anyone know of a utility to migrate Access 97 Queries to SQL Server 7?
I've over 400 of them and I am trying not to have to do them by hand.
Also I tried to use to migration wizard from MS instead of the DTS and all I got was Error after error. 'Invalid Date/Time', 'Error with YES/NO field' and 'Timed out' were the most common. In the end only 4 tables out of 89 were migrated.
Any suggestions would be greatly Appreciated.
-Ray
I am trying to get the same results in SQL server with a query as it works in access
There are two tables:
TITLES
Fields in query: TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.TitleState, TITLES.VehYR, TITLES.VehMake, TITLES.VehModel
Fields in Table:
[TitleID]
,[TitleNum]
,[TitleState]
,[VehYR]
,[VehMake]
,[VehModel]
,[NewORUsed]
[code]...
How this can be ONE SQL Query?In query designer on access theleft table is TITLES_IN_OFFICES_qry and the table on the right isTITLOC.I have never used a query as a table before in a select .
I have an Access database that used to produce a mass of Performance Indicators from Access tables. The data is now held on SQL Server and I run the Access queries from the SQL tables. I wouldlike to move all the queries over to SQL but not sure if I can do that. Here's an example of one of the queries (the SQL view)
SELECT tblCalls.*
FROM tblCalls
WHERE (((tblCalls.Call_date)>=[Forms]![ReportParams]![SDate] And (tblCalls.Call_date)<=[Forms]![ReportParams]![EDate]) AND ((tblCalls.NotAccepted)=False) AND ((tblCalls.Completed_time) Is Not Null) AND ((tblCalls.Category)="fly tipping"));
SELECT Month([Call_date]) AS Mnth, DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) AS Qtr, Sum(Work_Days([Call_date],[Completed_time])) AS RespTime, Sum(1) AS Count, Sum(Work_Days([Call_date],[Completed_time]))/[Count] AS AvgTime
FROM qryFlyTippingStatsSummary1
GROUP BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0)
ORDER BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0);
I am using SQL2005 (Installed on VISTA). It was running smoothly until today....for some reason I cannot access any tables in queries that I try to run in Management Studio. I can open the tables and see the information, but all queries I try to run result in the following error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.BEYLEVEL16'.
I tried to run simple select queries on other tables but I get the same error message. Do not understand, eveything was working fine yesterday.
Any suggestions greatly appreciated.
Thanks
Hi,
I'm working on an electronic register which people use to sign in and out of a building. It uses 3 tables, one to store personal information (Name & Visitor Number), one to store what time they come in, and one to store what time they leave. Visitor Number is the primary key in the Visitor_Info table, and is used as a foreign key in the other 2 tables.
I've been asked to put in an 'emergency print' function where, when you click the print button, it compares the data in the time in database and the time out database and prints a list of people still in the building.
So, what I'm trying to do is create a SQL query that will give me this information. Having never used SQL before, it's a little tricky. I've spent the last few days looking at tutorials on the net and learning about queries and this is what I've come up with:
Select Time_In_Table.Visitor_Number
Not Union
Select Time_Out_Table.Visitor_Number
If I'm right (and I'm probably not), this will give me the people who are in the time in table, but not in the time out table.
Can someone please help me by letting me know what I'm doing wrong in the statement above, and also, advise me as to where it goes since none of the tutorials I've found cover where to put the statement in my programming? At a guess, I'd say it goes in the print button click function.
Does anyone out there know of any utility that can Migrate Queries to SQL Server 7. I have over 400 of them and Dread doing them all by Hand.
Thanks,
-Ray
I have a query in Access that has an iif statement in the select system like so:
SELECT [01_qryCommonCost_01].*, tblPercent_Afe.AfeDescription,
tblPercent_Vendor.[Vendor%], tblPercent_Afe.[Afe%], IIf([manual%] Is Not
Null,[Manual%],IIf([PropType]="NonMkt",0,IIf([Vendor%] Is Not
Null,[Vendor%],IIf([Afe%] Is Not Null And [GlType]<>"OpExp",[Afe%],[Gl%]))))
AS [%], 0+round2([Amount]*[%]) AS Allocated,
0+round2([Allocated]*[Salvage%]) AS Salvage
FROM (01_qryCommonCost_01 LEFT JOIN tblPercent_Vendor ON
([01_qryCommonCost_01].GlType = tblPercent_Vendor.GlTyp) AND
([01_qryCommonCost_01].VendorName = tblPercent_Vendor.VendorName)) LEFT JOIN
tblPercent_Afe ON ([01_qryCommonCost_01].AfeNo = tblPercent_Afe.AfeNo) AND
([01_qryCommonCost_01].Group = tblPercent_Afe.Group)
But I can't figure out how to nest the iif statement in T-SQL. Any hints?
Hi All,
I hope I am at the right place to post this question:
How can I move the tables with its data from Access to SQL Server 7.0 using SQL queries. Later I might put everything in the store procedure and have a third party running the store-procedure to do it by itself.
Please advice what should I do first? Do I create new tables in the s SQL Server 7 or can I move the data and create the tables at the same time?..Thank you.
I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
I have an application where users can enter data into any (or all) of 6 search fields,
to produce a filtered query.
This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?
Hoping you can help
Alex
PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;
SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp
FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));
Is there an easy way to convert Access Queries to SQL Views without doing it manually?I have used the Databse tool to migrate tables, but cannot see to find something similiar for queries.
View 3 Replies View RelatedIs there any easy way to pass (dynamically) parameters to pass-throughqueries,when working with MS Access as front-end for SQL Server ?Thanks.
View 1 Replies View RelatedI have upsized some Access 2002 tables to Sql Server 2005 express. I put together a query and when I try to execute it, it tells me the MS Access is an earlier version prior to SQL server 2005 and it will not save the queries. When I try to run them anyway, I get the repeated message and the query never executes. I'm taking a T-SQL class and this is how the instructor. Is they a fix for SQL Server or Access to get around this or do I have to revert back to MDSE 2000?? Can MDSE 2000 and SQL Server be loaded onto the same machine (laptop)? Help!!!!! I am very new at this...
View 5 Replies View RelatedI know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
Any advice would be greatly appreciated.
Many thanks
Smilla
Hi,
We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???
Thanks
Pete Burton
(IT Support)
Durham Aged Mineworkers Homes Association
Hello,
I am facing a huge problem in my sql server database using access as a front end.The main problem is trying to execute queries "views" ,since they reside on sql server now,and using variables or parameters in reports and forms to filter on this query.
Ex.
how can the following be implemented using the same query but in sql server?
Access
------
SELECT MAT_Charts.YYYYMM
FROM MAT_Charts
WHERE ((([Area_Code] & "-" & [GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
GROUP BY MAT_Charts.YYYYMM;
It is specifically this statement in which I am interested:
[GROUP_CODE])=[Reports]![MAT_Chart_C1].[MAT_Key]))
Thank you very much for your concern.
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedI 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.
Again, I am relatively new so take it easy on me!
Thanks,
grinch
Hello,
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.
Thanks,
Dave
Hi,
I am new to this programming and SQL.
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
BindingSource1.EndEdit()
'CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
Dim rowsAffected As Integer = 0
rowsAffected = CustomerTableAdapter1.Update(MyCompanyDataSet1.Customer)
MessageBox.Show(rowsAffected.ToString())
End Sub
End Class
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?
Thanks
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.
View 7 Replies View RelatedHi.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.
View 8 Replies View Relatedhi, I'm trying to write to a database from a web page. The code is throwing back no errors but it just isn't writing to the database. Am I missing something? void SubmitDetails(Object s, EventArgs e) { SqlConnection objConn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"]); SqlCommand objCmd; objCmd = new SqlCommand( "INSERT INTO Member_Information (Name, Address_Line_1, Address_Line_2, Address_Line_3, Email_Address, Contact_Number, " + "Password, Name_Of_Bank, Account_Number, Sort_Code, Bank_Address_Line_1, Bank_Address_Line_2, Bank_Address_Line_3) " + "VALUES (@Name, @Address_Line_1, @Address_Line_2, @Address_Line_3, @Email_Address, @Contact_Number, " + "@Password, @Name_Of_Bank, @Account_Number, @Sort_Code, @Bank_Address_Line_1, @Bank_Address_Line_2, @Bank_Address_Line_3)", objConn); objCmd.Parameters.AddWithValue("@Name", txtName.Text); objCmd.Parameters.AddWithValue("@Address_Line_1", txtAL1.Text); objCmd.Parameters.AddWithValue("@Address_Line_2", txtAL2.Text); objCmd.Parameters.AddWithValue("@Address_Line_3", txtAL3.Text); objCmd.Parameters.AddWithValue("@Email_Address", txtEmail.Text); objCmd.Parameters.AddWithValue("@Contact_Number", txtContact.Text); objCmd.Parameters.AddWithValue("@Password", txtPassword.Text); objCmd.Parameters.AddWithValue("Name_Of_Bank", txtBank.Text); objCmd.Parameters.AddWithValue("Account_Number", txtAN.Text); objCmd.Parameters.AddWithValue("@Sort_Code", txtSort.Text); objCmd.Parameters.AddWithValue("@Bank_Address_Line_1", txtBA1.Text); objCmd.Parameters.AddWithValue("@Bank_Address_Line_2", txtBA2.Text); objCmd.Parameters.AddWithValue("@Bank_Address_Line_3", txtBA3.Text); objConn.Open(); objCmd.ExecuteNonQuery(); objConn.Close(); }
View 4 Replies View RelatedHello, 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
Hey everyone,
I'm making a site and I need to write some code that will do the following things:
Write a statement for the Page_Load event to pull the information from the db and load it into a textbox and a textbox with multiline attribute.
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 ******/
CREATE TABLE [dbo].[tblProduction] (
[TransactionID] [int] IDENTITY (1, 1) NOT NULL ,
[DealID] [int] NOT NULL ,
[PhaseID] [tinyint] NOT NULL ,
[TransactionTimeStamp] [smalldatetime] NOT NULL ,
[Comments] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblCompany] WITH NOCHECK ADD
CONSTRAINT [PK_tblCompany] PRIMARY KEY CLUSTERED
(
[CompanyID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblContractStatus] WITH NOCHECK ADD
CONSTRAINT [PK_tblContractStatus] PRIMARY KEY CLUSTERED
(
[StatusID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhaseType] WITH NOCHECK ADD
CONSTRAINT [PK_tblPhaseType] PRIMARY KEY CLUSTERED
(
[PhaseTypeID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] WITH NOCHECK ADD
CONSTRAINT [PK_tblDeals] PRIMARY KEY CLUSTERED
(
[DealID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblPhase] WITH NOCHECK ADD
CONSTRAINT [PK_tblPhase] PRIMARY KEY CLUSTERED
(
[PhaseID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblProduction] WITH NOCHECK ADD
CONSTRAINT [PK_tblProduction] PRIMARY KEY CLUSTERED
(
[TransactionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblDeals] ADD
CONSTRAINT [DF_tblDeals_StatusID] DEFAULT (5) FOR [StatusID]
GO
ALTER TABLE [dbo].[tblProduction] ADD
CONSTRAINT [DF_tblProduction_TransactionTimeStamp] DEFAULT (getdate()) FOR [TransactionTimeStamp]
GO
ALTER TABLE [dbo].[tblDeals] ADD
CONSTRAINT [FK_tblDeals_tblCompany] FOREIGN KEY
(
[CompanyID]
) REFERENCES [dbo].[tblCompany] (
[CompanyID]
),
CONSTRAINT [FK_tblDeals_tblContractStatus] FOREIGN KEY
(
[StatusID]
) REFERENCES [dbo].[tblContractStatus] (
[StatusID]
),
CONSTRAINT [FK_tblDeals_tblPhaseType] FOREIGN KEY
(
[PhaseTypeID]
) REFERENCES [dbo].[tblPhaseType] (
[PhaseTypeID]
)
GO
ALTER TABLE [dbo].[tblPhase] ADD
CONSTRAINT [FK_tblPhase_tblPhaseType] FOREIGN KEY
(
[PhaseTypeID]
) REFERENCES [dbo].[tblPhaseType] (
[PhaseTypeID]
)
GO
ALTER TABLE [dbo].[tblProduction] ADD
CONSTRAINT [FK_tblProduction_tblDeals] FOREIGN KEY
(
[DealID]
) REFERENCES [dbo].[tblDeals] (
[DealID]
),
CONSTRAINT [FK_tblProduction_tblPhase] FOREIGN KEY
(
[PhaseID]
) REFERENCES [dbo].[tblPhase] (
[PhaseID]
)
GO
exec sp_addextendedproperty N'MS_Description', N'Identifier', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'StatusID'
GO
exec sp_addextendedproperty N'MS_Description', N'Description', N'user', N'dbo', N'table', N'tblContractStatus', N'column', N'Status'
GO
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'
GO
exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'tblProduction', N'column', N'TransactionTimeStamp'
GO
---------------------------------------
And here's some data
---------------------------------------
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')
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