How To Inform The Administrator About The Occurance Of The Record Entry In The Sql Databse?
Dec 14, 2000
Hello all
my problem is like this
I have a windows nt server 4.0 .its having IIS4.0 as well as Ms sql server 7.0 installed. I am using ASP ( active server pages) to store the fields entered by the customers in the order processing form in my site( thats the default page--form)
this form calls the asp file and asp file successfully stores the fields in the corresponding table.Now the problem is that how can i configure my sql server in such a way that sql server can mail me( as i am the administrator) informing the new order placed by the customer.
I know sql mail , and i have already configured that also. but how can i create the trigger for the table updation?
i tried using the enterprise manager, i was using xp_sendmail. but sql says "the stored procedure can't be found"
We are trying to set up SQLAgent Proxy account. If the SQLServer service id is a domain admin, do we still have to add it to the local administrators group?
Please help! I am new in SQL Server 2000 Administration. My manager had ask me to manage a SQL Server, but Windows Adminstrator refuse to give me Local Admin rights. I only have sa account to login to databases. I don't know any excuses or reasons to give to Windows Adminstrator so that he can give me the local admin rights. If any one have this answer, please help. Thank you.
I want updated to few of person of any changes in database just by sending to their emails in every 2 hours as an example. I go through the example given but I do not know the step how to run stored procedures. The Information that I want to give to them is like as:
Date From : 23/02/2008 Date To: 24/02/2008 Number of user : 3
My draft table is like this
Sequence_No Submitted_Dt Name -------------------- ------------------- ------------------------
1 2/21/2008 4:16:45 PM John 2 2/22/2008 4:16:45 PM Dean 3 2/23/2008 4:16:45 PM Rick 4 2/24/2008 4:16:45 PM Van
select top 1 MWh from ( select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID ) as [Elleverantor 2 MWh]
But when I put in my long select it doesnt work. Also the long script works fine without these rows inserted. I hope someone can tell me why. //Tommy
I post the entire script and the error message can be found below.
Msg 156, Level 15, State 1, Line 59 Incorrect syntax near the keyword 'select'. Msg 170, Level 15, State 1, Line 114 Line 114: Incorrect syntax near '-'.
SELECT tDeklaration.Diarienummer, IO.OrgNr, IO.OrgNamn, tDeklaration.KontaktpersonNamn, tDeklaration.KontaktpersonEpost, tDeklaration.KontaktpersonTele, (select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID) as [Egen producerad], -- Hämta ut Orgnamnet för en Elleverantör. Finns det inte i Tabellen ta intressentID:t och hoppa iväg till IREG. (select Case When (Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL then (Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNamn from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID = (SELECT top 1 IntressentID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) ELse (Select OrgNamn from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 Namn], (select Case When (Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) IS NULL then (Select intressentregister_skarp.dbo.tIntressentOrganisation.OrgNr from intressentregister_skarp.dbo.tIntressentOrganisation Where intressentregister_skarp.dbo.tIntressentOrganisation.IntressentID = (SELECT top 1 IntressentID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) ELse (Select OrgNr from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) End) [Elleverantor 1 OrgNr], (Select MWh from tDeklarationElleverantor Where DeklarationElleverantorID = (SELECT top 1 DeklarationElleverantorID FROM tDeklarationElleverantor WHERE tDeklaration.DeklarationID = tDeklarationElleverantor.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL )) as [Elleverantor 1 MWh],
-- Here is the problem....... select top 1 MWh from ( select Top 2 Mwh, tDeklarationElleverantorID from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID and tDeklarationElleverantor.Borttagen IS NULL order by DeklarationElleverantorID ) as [Elleverantor 2 MWh],
(select (select sum(tDeklarationElleverantor.MWh) from tDeklarationElleverantor where tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID) + (select sum(tForbrukadEgen.EgenProduceradEl) from tForbrukadEgen where tForbrukadEgen.DeklarationID = tDeklaration.DeklarationID) ) as [Summa använd och vidare fakturerad], ElTillverkning AS Tillverkningsprocess, ElDel AS [Tillverkningsprocess del av år], ForsaljningsVarde AS Försäljningsvärde, (select Case When ElTillverkning > 0 or Forsaljningsvarde > 0 Then ElTillverkning/Forsaljningsvarde ELse 0 End) As Kvot, (select Case When ElTillverkning <= 0 or Forsaljningsvarde <= 0 Then 0 When ElTillverkning/Forsaljningsvarde < 40 then 0 When ElTillverkning/Forsaljningsvarde >= 40 and ElTillverkning/Forsaljningsvarde < 50 then ElTillverkning/Forsaljningsvarde * 0.5 When ElTillverkning/Forsaljningsvarde >= 50 and ElTillverkning/Forsaljningsvarde < 60 then ElTillverkning/Forsaljningsvarde * 0.75 ELse ElTillverkning/Forsaljningsvarde End) as [Undantagen elmängd],
(select (select SUM(tHjalpkraft.Bruttoproduktion * tSchablonAr.Schablon)/100 from tHjalpkraft INNER JOIN tSchablonAr ON tHjalpkraft.KraftslagID = tSchablonAr.KraftslagID where tHjalpkraft.DeklarationID = tDeklaration.DeklarationID and tSchablonAr.Ar=2007) + (select SUM( tFaktiskHjalpkraft.NettoProduktion) from tFaktiskHjalpkraft where tDeklaration.DeklarationID = tFaktiskHjalpkraft.DeklarationID)) AS [Hjälpkraft],
--Summa avdrag för vidare fakturerad el (ny kolumn, summan under rubrik 5)
(select SUM(tFornybarFrom07.Fornybar) from tFornybarFrom07 where tFornybarFrom07.DeklarationID = tDeklaration.DeklarationID) AS [Förnybar], tDeklaration.Kvotplikt, tDeklaration.BegartAnnullerat as Annullering, tDeklaration.Ovrigt as [Övriga Upplysningar], tDeklaration.Kvotplikt - tDeklaration.Annullerat AS Saknas, tDeklaration.DeklarationStatusID, tDeklarationStatus.StatusIntern FROM tDeklaration INNER JOIN tDeklarationStatus on tDeklaration.DeklarationStatusID = tDeklarationStatus.DeklarationStatusID INNER JOIN tRegistreradKvotpliktIntressent ON tDeklaration.RegistreradKvotpliktID = tRegistreradKvotpliktIntressent.RegistreradKvotpliktID INNER JOIN tIntressentforadOrg ON tIntressentforadOrg.IntressentforadOrgID = tRegistreradKvotpliktIntressent.IntressentForadOrgID LEFT OUTER JOIN intressentregister_skarp.dbo.tIntressentOrganisation IO ON IO.IntressentID = tIntressentforadOrg.ForadAgarID --left outer join tDeklarationElleverantor on tDeklarationElleverantor.DeklarationID = tDeklaration.DeklarationID WHERE (tDeklaration.AktuellIntern = 1) AND (tDeklaration.KategoriID = 3) AND (tDeklaration.DeklarationStatusID <> 1) AND (tDeklaration.DeklarationStatusID <> 3)and tdeklaration.kvotpliktar=2007 order by IO.OrgNamn
I have a table that contains the following records: ptrecidpaidbyamtchequenoname 4791A X A1200097760LOWE, Bernard 4791A X A4380097760LOWE, Bernard 4791A X A620106406LOWE, Bernard
I need to create a view that returns only the FIRST occurance of each distinct ptrecid. I do NOT want to sum amt/chequeno, but rather return only the values in the first record. Using a group by gets me close, but since the amt and chequeno are not unique, they do not group. Min also does not work, as 620 is returned for amt, and 097760 is returned for chequeno.
The desired result from this query would return: 4791A X A1200097760LOWE, Bernard
I am trying to create a view that shows the first occurance of a particular value in a column.
For example, I have a table that contains multiple part numbers that can be associated with mulitple customers. The first occurance of the part number in the table is associated with the main customer for that part number. I want this view to only show the main customer for each part number.
I am able to do this in Access using the following SQL statement: SELECT dbo_CustPattMast.PATTERN_NUM, First (dbo_CustPattMast.CUST_NUM) FROM dbo_CustPattMast GROUP BY dbo_CustPattMast.PATTERN_NUM
When trying to create the view in the SQL Manager, it will not allow me to use the First statement.
I am making a sql query and its bring back hundreds of results but I only need the first one. I am aware that getting the first one will be faster as well. Is there an efficient way to do this?
Declare @MYTable Table ( RecordID int identity, EmployeeID int, JobCode varchar(4) ) Insert into @MYTable Values(1,'123') Insert into @MYTable Values(2,'123') Insert into @MYTAble Values (1,'123') Insert into @MyTable Values (2, '123') Insert into @MyTable Values (3,'123') Insert into @MYTable Values (1,'222') Insert into @MYtable Values (3,'222') Insert into @MYTable Values (1,'222') Insert into @MyTable Values (4,'222') Insert into @MyTable Values (5,'123') Insert into @MyTable Values (6,'123') Insert into @MyTable Values (7,'457')
Select * from @Mytable
The each employee can come to this table multiple time however the job code should be different for each every single record. So If I have to check this error and see which record has been repeated with the same Employee ID and JobCode and how many times. how would i do that Thanks,
Is there a simple command to find the 18th occurance/position of ";" in a field and I need to do this in the SQL code.
I can use charindex to find the 1st position and I can write a function to keep chopping off the data at the start of the field until I get to the 18th occurance but it takes 18 lines or so of code.
Is there a simpler way of doing this?
I need to find the 18th occurance of ";" and then take the next 4 characters.
i'm making a web page for a clinic.it needs to be able to search for patients by first name, surname, date of birth and patient number.i'm using visual web developer and i have my database, my data source and GridView grid.i want 4 text boxes for my first name, surname etc. when u click enter on any of them i want to retrieve all their data and display it in the gridview.at the moment i have one text box on the web page and through the "Configure data source" option on the grid view i can retrieve the specified data but for only this one item, e.g. SELECT * FROM [Patients] WHERE ([DOB] = @DOB). if i add another text box to my web page, and don't do anything to it, the query wont run. if i add and "AND" statement to the query, e.g. SELECT * FROM [Patients] WHERE (([DOB] = @DOB) AND ([FirstName] = @FirstName)), again it won'r run or return and data. any ideas on what i can do or where i'm going wrong. thanks
Dear All I've made transactional replication between two SQL 2005 servers.Everything looks fine, synchronization working fine, no errors, however size of replicated database file = 97 Mb,on Publications server the database file size = 184 Mb. What is wrong :S ? Best RegardsPiotr
hi, im totally new to this sql thing. i have a set of classes here and my objective is to show that they can connect to the database. how am i supposed to do this?
How to create a database in MS SQLserver 2005 express edition. Pleae help me to get started.I have learned some SQL commands. But i want to execute those in SQLserver 2005.But i don't know the procedure for creating a database.please help me on this.
Im creating a sample database for purposes of keeping track of employee's jobs + billing codes for that job. currently they just use an excel spreadsheet to keep track of billing.
Question 1:
What would be the easiest way for the manager to enter billing codes? Billing codes are numbers like 956, 958, 805 and they co-relate to prices for each billing code.
1. I want the manager to enter the billing code and have the sql database find out how much that code is and add them up daily. This would prevent the manager from having to input prices and billing codes, and extra step.
Any links on the above topic would be helpful. I'm not looking for anyone to spoon feed me code just point me in the right direction. Im relatively new to sql having only a class of sql @ ITT.
Heres the way I have the db tables laid out currently.
hi everyoneCould someone please help me in following:One of my database in SQL 2000 going Offline automatically. When ibring it back Online its Ok for 20/30 minutes and then again appear asOffline. I had similar problem when one of the database keep going to'Single user' automatically.Any idea what happening.Thank you*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi, there is an existing application running on a SQL Server database.Now I have to built a different app, which will have two common tableswith the existing database (Customers, sales)Is there any benefit (performance mainly) creating the tables for thenew app in a new database? (On the same machine and the same instance)*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
I went and detached a database. It is no longer showing in EM. I try to attach the database and I do not have any MDF files anywhere on my PC. Where did my database go when I detached it? How can I get it back. Please help.
I am doing one E-commerce Application , throgh C#. & sql server express editon. Both my Local & Web Hosting servers are in sql server express 2005. I designed Databse Throghly, to my local server. Now I want to Transfer tables to my web server. I have already Sql server mangement studio Express. I can not do.. I spent, more than 5 yrs time..
I have a system running on SQL Sever and i am trying to create a second file on a diffrent computer so that I can teach people how to use sql sever (express) without risking losing any data that I have on my proper database.
I have created a backup of my database which is in a dmp file and I would like to import this into SQL Server Management Studio Express so that I can make changes to the databse without affecting the one that is running on my website.
I am new to sql server. I would like to now how to add somebody as an administrator so that he/she can connect to my instances. what is the easiest way to do it>? thanks....
I would like to run some queries on two tables that reside in two different database (located on the same server). The databases are both MS SQL 2000.How do I create a database link (or a table link??) so that I can create the queries from the Enterprise Manager or the Query Analyzer? Thanks a lot, Christian
I am using the SQLEXPRESS database, and connect from to locations:
Windows Service ASP.NET web application.
whenever I start the windows service I get the error: Cannot open user default database. Login failed. so I am going to visual studio and close the connection. then I close the web browser, and the windows service succeed connect to database. but then, after windows service is connected, I open the web browser application and fails to login because the login system using the same database of windows service, and since the connection is being used by windows service, login is impossible.
Do you know how I can allow 2 connections at the same time for the same database? what are the risks to do that? and how do I handle this risks. thanks alot
Hi, I have a SQL Server 2005 Express database dbase.mdf which I cannot access. It works fine in VWD and the development server using the following connection string: String connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbase.mdf;Integrated Security=True;User Instance=True"But when the site is not on the development server but on IIS, I get a SqlException saying "User does not have permission to perform this action." when I execute the connection.Open() command. I find it extremely frustrating that something which works in the development server fails on the real server. Can anyone help me with this? Do I have to change my connection string, or perhaps set some permissions...I have already given the IUSR_MACHINENAME account and the ASPNET account full access to dbase.mdf. Surely this must be an issue that lots of people deal with... How do other people manage to log to access databases? Any help would be much appreciated. Thanks,P1000
can any body tell me how to update the database thru dataadpater ??????? and another imp thing......... if there r 2000 rows in database ..........and updation of 2 rows takes place i want that whole of the database should not be updated ......only those two rows are updated
Can any one help me please?????? thanks in advance
hello all the problem with me,im for the first time using SQL Server and ASP,so i have little knowledge baout these two progs.i want to know how pages in ASP are connected to databse. second, i installed SQL Server2005 along with asp from the same CD(option of instalation of both was available). There is also a way use SQL Server within the ASP Environment(addnew itmes>sqldatabase>). Question: if i create a databse through above method. then how i will use it in asp.net.Although i can do it by draging "SQL Data sourcr" from "Data" category in the "toolbox"however, i want to manually connect the application to Db.Something like this Page_load(0 { //db connection code goes here.//sqlcommand goes here. con.open() } i want some controls like txtboxes and label to manualy connect to databse tables. plz someone forward the code,
Hi Friends,I have one table in the databse,i.e userTable with one field userNameIn my form I have one Label ,textbox for entering the userName and One button for submit,So I am entering the data into the table(userTable) after clicking on the submit buttonBut my problem is before entering the data into the table I want to find wheather the given data exits or notif its not exists the data has to insert into the table otherwise its has to display the message"the user is already existed"for this I wrote the code like this in C# public Boolean isUserExists() { SqlCommand cmduserName = new SqlCommand("select count(*) from userTable where userName= " + txtuserName.Text + ")", conn); SqlDataReader rdr = null; rdr = cmduserName.ExecuteReader(); conn.Open(); int count = 0; while (rdr.NextResult()) { count = rdr.GetInt32(1); } conn.Close(); if (count == 0) { return false; } else { return true; } protected void Button1_Click(object sender, EventArgs e) { if(isUserExists()) { Response.Write("Opps ! User already Exists"); } SqlCommand adapInsert = new SqlCommand("insert into userName values('" + txtuserName.Text + "')",conn); conn.Open(); adapInsert.ExecuteNonQuery(); conn.Close(); Response.Write("data inserted"); } is it write or not because I am not getting the output .please tell me any one where I have to change the code ThanksGeeta
