can someone post me an example trigger where.. inserting a new record in one table, creates a new record in another table with a new unique identifier incrementing by 1 from the last record.
so that I can edit it to suit my own work like:
eg. when I insert new person into people table, using a person_no as key.
= creates new pay record in pay table ith a pay_id as key like '0004' 1 more than the last record if it was '0003', and then adds that new person_no to the record for reference.
I know this is a really easy trigger, but it would be my first! Any example to edit would be great! thanks in advance
Steve suggested I post my question here because this is where all the "experts" reside who like a little challenge. I'll simplify my problem down to two example tables with two fields each for the purpose of solving this "hard part". My only requirement for the solution is that it has to be a SQL only solution (no stored procs) as my client can only run Access - yuck...
Here's the deal. There are two tables, PEOPLE and JOBS. They look like this:
I want to report which people are assigned to which jobs as shown. One line per job with the names concatenated with commas in between:
Clean Adam Wash Bill,Carl Dry Bill Polish Adam,Carl,Dave
All four fields are TEXT fields in this design, but they don't have to be. I have full control over the database, fields, types, etc. If there's a better way then just suggest it. As you can see, the "ASSIGNED_TO" field is a concatenation of the IDs. Goal is just to report the DESC and people's NAMEs.
How about it? Any slick way of doing this? Played around with using SubQueries, IIF, LIKE on ID and ASSIGNED_TO, and came close but never got it all.
I appreciate any tips or suggestions. Thanks a bunch!
I need a sum of such records whose id are continous.I mean the output should be
id sum 2 15 8 50 12 25
Id 1 & 2 are continous so it is added.But after 2 the next number is not 3 so it wont be in the sum of 1 & 2.From 5 to 8 numbers are continous so those diff are added & so on.
SELECT sum(cantidad) AS suma FROM Movimiento WHERE ID=[puntp] AND id_sorteo=[sorteo] AND tipo="Entrada"; SELECT sum(cantidad) AS resta FROM Movimiento WHERE ID=[puntp] AND id_sorteo=[sorteo] AND tipo="Salida";
I want to join in one simple query and i want to display the result of suma-resta
Thnk you ! i hope u can help me, I'm going to explode my brain hehe
Ok i've tried everything setting table collation to cyrillic setting the whole table language settings to Russian nothing works-i can only save ?????? instead of Russian symvols.(yes datatype is ntext not just text) I can directly input Russian letters from EMS SQL manager, and it will be ok, and i can even read it on my pages. But even if i try to execute SQL command through EMS manager (as opposite to just typing in the data) it will give me ????? again. If I save my ASP.net page in unicode it will give me ?? with some weird ` types symbols-but 70% of symbols would still be ????. I have already spend 45 hours googling and browsing forums but i still cannot solve it! I am so frustrated! MS SQL Server 2005 Framework 2 ASP.net code looks like Win XP sp2
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.Odbc;
using System.Data.SqlClient;
public partial class registration : System.Web.UI.Page
SqlConnection myConnection = new SqlConnection("server=(local);database=main;Trusted_Connection=yes;");
///ыуаыпвараеоеаореавп is russian random letters
SqlCommand myCommand = new SqlCommand("UPDATE quest SET Name='ыуаыпвараеоеаореавп' WHERE id=1", myConnection);
myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader();
myConnection.Close();
if i manually stop the script here and look at my dr variable i will see exactly sql command that i wrote (with no ????) but after execution in my database it is still ?????????????????????????????? Best Regards
've installed sql 2005 xpress with advanced services......also installed the toolkit that came along..
all 'm interested in is to perform certain data mining operations on the adventureworks db..
;ve also installed the advn works db.........
now here is the prob.......
jus like how the sql online book instructed me i first started off with the datamining tutorial..where i was told to first OPEN BIDS select new PROJECT then make sure that the analysis Services template was selected and then go ahead with selection of the particular sln file....
the problem is that I DON'T SEE ANY ANALYSIS SERVICE TEMPLATE....all i c is a REPORT template..
i really need help guys..... 'm new to .net really feeling helpless...
Hii Folks This is my Table Order(OrderNo, CartID, TotalAmount, Name, City, Email, Zip, Date), Then I have my code which I need to insert data into database, but OrderNo is automatically inserted this is my code, but when I run it it shows the error page, if I remove the direction to my error page, it does not show anything and I don't see any error, could any one check for it please Imports System Imports System.Data.SqlClientPartial Class Checkout Inherits System.Web.UI.PageProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadAmountLabel.Text = Session("OrderTotal").ToString() SessionLabel.Text = Session.SessionID.ToString() End SubProtected Sub ContinueButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ContinueButton.Click Dim shopp As New SqlDataSource()shopp.ConnectionString = ConfigurationManager.ConnectionStrings("SHOESConnectionString").ConnectionString shopp.InsertCommandType = SqlDataSourceCommandType.Text shopp.InsertCommand = "INSERT INTO Order(CartID, TotalAmount, Name, City, Email, Zip, Date) VALUES (@CartID, @TotalAmount, @Name, @City, @Email, @Zip, @Date)"shopp.InsertParameters.Add("CartID", SessionLabel.Text) shopp.InsertParameters.Add("TotalAmount", AmountLabel.Text)shopp.InsertParameters.Add("Name", NameTextBox.Text) shopp.InsertParameters.Add("City", CityTextBox.Text)shopp.InsertParameters.Add("Email", EmailTextBox.Text) shopp.InsertParameters.Add("Zip", ZipTextBox.Text)shopp.InsertParameters.Add("Date", DateTime.Now()) Dim rowaffected As Integer = 0 Try rowaffected = shopp.Insert()Catch ex As Exception Server.Transfer("ErrorPage.aspx") End Try shopp = Nothing If rowaffected <> 1 ThenServer.Transfer("ErrorPage.aspx") ElseServer.Transfer("success_shopping.aspx") End IfEnd Sub End Class
Finding myself in a bit of a lull at work, I'm trying to create some template packages / solutions, encapsulating some of the best practices that get discussed on here.
I'm thinking about passing configurations from Parent to Child packages, something we all must do on a regular basis. I've always done this a particular way, without thinking too much about it, but I'm not sure if this way is the best. I'd be interested in your opinions.
Scenario:
Parent.dtsx calls Child1.dtsx, Child2.dtsx etc, using Execute Package Task. Parent has variable Source, and variable Destination, populated from a configuration (my preference is SQL table, but whatever). These variables are ultimately used to populate the ConnectionString property of the source and destination Connection Managers of the Child packages.
Here's the question: Is it better for the Child packages to look up to their parent, and inherit the values of these variables through Parent Package Configurations, or to look directly to the configuration, and pick them up from there? Or does it not matter?
I tend to assign lots and lots of variables in Parent.dtsx, and pull them down into Child.dtsx with Parent Package Configs, but I'm thinking "is this good/bad/unimportant?"
Reading about avoiding deadlocks in SQL, I have found different opnions about whether using or not NOLOCK/ROWLOCK hints on SELECT/UPDATE/DELETE statements. I have several applications executing transactions on the same databases and tables, including inserts, updates and deletes. I wonder if by using NOLOCK/ROWLOCK I could decrease the chances of having deadlocks. At least using ROWLOCK on my update statements?. I just need some advice here.
I have another question guys I need to send reports to different departments using sql. would this be the xp_sendmail function in sql. Can this be made into a stored procedure? What I'm going to do is make some reports which will constantly be updated with a query. These reports need to be emailed to differnt departments every 2 weeks. Is there a trigger or stored procedure I can setup through sql to do this? Does that make any sense??
I have a asp.net 2.0 app that has a sql server express backend. I have been scratching my head for weeks trying to find a good wasy to encrypt my connection string. I store the string in web.config (which in itself is in theory "safe" because it's not servable) and I have scoured the web to try to find a simple way to encrypt the connection string. Even using user store would require running aspreg_iis.exe on the host with a command prompt (which clearly I cannot do). It seems that everyone's solution requires running a command prompt executable on the server.
So I am left to wonder what everyone else does. I am not comfortable using just the web.config because a determined hacker could get to that info. Then again, a determined hacker could get into any system , I guess. There are no credit card #'s stored in this app, but in theory there must be a way. I run all access to the db through stored procedures with permissions, for whatever it's worth.
Why don't you folks (SQL SERVER Management Studio Team or SQL SERVER Database Engine Team) provide an option by using which I would be in a position to know what all the other objects are depended on a selected object and vice versa. That means:
When I select a Table within the SQL SERVER Management Studio if there is an option for understanding "Which all the objects (tables or views) it is depended on (I mean Foreign key and etc... kind of relations) and also Which all the objects (Tables, Views, Stored Procedures and etc...) are using this table", It would be of great help for all developers.
I'm banging my head against the wall. It's probably pretty simple so here goes...
I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)
I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?
if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?
try { //Open up the connection conn.Open(); //Setup the Transaction trans = conn.BeginTransaction();
//First query in transaction pre_query = "Delete from Menu where spec_name=" + "'" + spec_name_array[i].Text.ToString() + "'" ;
//Second query in transaction query = "INSERT into Menu VALUES ('" + Calendar1.SelectedDate +"','" + spec_name_array[i].Text.ToString() +"','" + spec_desc_array[i].Text.ToString() +"','" +spec_price_array[i].Text.ToString() +"',1)"; SqlCommand comm = new SqlCommand(pre_query,conn); //Setup the command to handle transaction comm.Transaction = trans; //Execute first query comm.ExecuteNonQuery(); //Add in second query comm.CommandText = query; //Execute second query comm.ExecuteNonQuery(); trans.Commit(); } catch(SqlException ex) { com_label.Text = "Submission Not complete, did you forget the date or something else?"; //Undo all queries trans.Rollback(); } finally { conn.Close(); }
This may not be the right place for this post...but my head is hurting and I can't think right now.
I have a SQL table of users. Each record has a required 'Birthdate' field. I'd like to order this by the days left till the give birthday, keeping in mind that the year may be 1980 or their birthday might have been 2 weeks ago.
I have written 2 custom connection mgr€™s. One connects the data from an oracle source One connects so I can put the data on a sql server. These both work well and it makes it a lot easier across development, test, production.
But now I would like a good way to do this
I have 10 tables to copy from oracle. The only difference is just the name of the table.
If I have a 64 bit version of SQL 2005 installed, will "select @@version" indicate it is a 64 bit version? If not, how do I determine such? Thanks in advance.
I have a column in my report that i want to show percentage change from the previous year to this year. So i enter all kinds of different type of formats for the formula, and keep getting the wrong answers or error messeges. Here's what i have tried recently:
hi, I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions? SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)
Ok - I am still a bit weak on SQL. My understanding of FK relationships in a database are to reinforce the data integrity - Correct? For example, does it make sure that the id given for SiteID does indeed exists. Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?
scenario I have 2 tables one is called "Site" which contains columns ID, Address1, Address2,City,StateID,ZipCode and the second table is called "State" which contains columns ID,Name,Abbreviation. I am looking to make a view that contains all the sites with the state inner joined on stateid to make my all my other queries easier. That is so I can just select from the view without having to do the inner join on the state. My question is when a new record is added to table Site do I have "recreate"/update the view?
I am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.
This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how??? Any help would be awesome.
Sub Button1_Click(sender As Object, e As EventArgs) ???? = MyQueryMethod(txtPhone.Text)
I have a simple SQL table. The data looks like this:
products_ID----category----product_local
1----CORE----0
2----BASE----0
3----BRANCH----1
4----LEAF----3
I need to create a SQL statement that produces all category items where that ROW's products_ID is not found in ANY product_local in the table. So in the table above only the category BASE and LEAF would be printed because their products_ID are not found in any product_local in the table.
OK, I have two cols of data: COL1 COL2 test ing tams ert test pan ted ted hom jis ted sam tams dom test ut
I need to simply pull one row of data from the given rows. Whats in COL2 is not relevant. So an example would be: I want to retrieve a row(any row, but only one) where COL1 = 'test' An ecceptable result woould be: COL1 COL2 test ing
or
COL1 COL2 test pan
or
COL1 COL2 test ut
I would appreciate any help. This is driving me nuts. Seems like it would be easy. TIA, STue
I'm using bcp to import data into my database. the datetime format in the bcp file is ymd and my database has mdy datetime format. Is there any easy way to convert ymd into mdy when using bcp?
