Here after the Union, the col. WWDTA should have data but its' coming back blanks, but all cols are aligned. why is this?
SELECT
ALL T01.OHORD#, T01.OHSLR#,T01.OHORDT, T01.OHORDD,
T01.OHTTN$, '' as WWDTA
FROM ASTDTA.OEORHDOH T01,
ASTDTA.OETRANOT T02
WHERE T01.OHORD# = T02.OTORD#
I have a column that has 75 values, 50 are unique/25 are duplicates. Ineed to be able to bring back a list of the duplicates, listing allrows even if more than two have the same value. I need to be able todo this using t-sql.Thanks,Tim
I need to always have a formatting of 999,999,999.00 in a column called PropertyMap9.
PropertyMap9 is always a nvarchar(max)
How do I SET a value using a CONVERT or CAST to accomplish this ?
Why does this give me a syntax error ? The PropertyMap7 set actually works and performs the calculation and sets the new value for that row, however the next line gives an error. I need all 3 columns PropertyMap9, 8, and 7 to always have the above formatting, while still maintaining the nvarchar(max) datatype in the column. HELP!
update PropertyMapValues
set PropertyMap7 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap8 AS money) - (CAST(PropertyMap9 AS money)),1)
set PropertyMap9 = CONVERT(NVARCHAR(MAX),CAST(PropertyMap9 AS money))
END
Msg 102, Level 15, State 1, Procedure UpdatePropertyMap7, Line 59
After several hours of trying, I trow the towel in the ring and come here to ask a question.
Source system uses a timestamp column in the transaction tables. which is equal to a non-nullable binary(8) datatype (sql 2000 bol).
What I want to do is get the timestamp at the start of the transfer and at the end of the transfer of data. and store these in a controltable
I try to do this in 2 sql execute tasks:
sqltask 1: "select @@DBTS AS SourceTimestamp" and map the resultset to a variable. Here come's the first problem what variable type to take ?
DBNULL works (meaning it doesn't give errors) (BTW: is there a way to put a variable as a watch when debugging sql tasks ?)
INT64 and UINT64 don't work error message that types for column and parameter are different
STRING works
Then I want to store this variable back in a table of a different data source
sqltask2: "insert into controltable values(getdate(), ?)" and make an input parameter that takes the previous timestamp ...
if I took DBNULL as a type for the variable there doesn't seem to be a single parameter type that works ???
if i take STRING as a type for the variable I have to modify the sql to do the explicit conversion from string to binary so I change CAST(? as binary). It doesn't return any error but the value stored in the table is 0x00000000000 and not the actual timestamp.
Any help on this one ? Why are the INT64/Bigint not working here, you can perfectly do a convert(bigint, timestampfield) in sql ?
How came the SQL datatypes, and the variable datatypes, parameter datatypes are so badly alligned to each other (and all seem to use different names) ?
12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
New to Database Mirroring and I have a question about the Principal database server. I have a Database Mirroring setup configured for High-safety with automatic fail over mode using a witness.
When a fail over occurs because of a lost of communication between the principal and mirror, the mirror server takes on the roll of Principal. When communication is returned to the Principal server, at some point does the database that was the previous Principal database automatically go back to being the Principal server?
I need to run two reports each of A5 Size to run back to page and print on single A4 paper means in 1st half Sale bill will be printed and in second half Gate Pass Will Be Printed both report will be on same page and size and shape should be maintained. How to do it.
i have a table Animals with column names id and Name eg1 Tiger2 Lion3 Elephant4 Cheetah5 Zebra i want the order to be anything of my chossing when i select from it. eg how could i get it to display results in this order (when i do select * from Animals): CheetahElephantZebraTiger Lion Thanks
Hello, I am trying to pull run this sql statement but it's bombing out at the comm.ExecuteNonQuery();. ..
Could someone help me figure this out.. Ex.System.Int32 bum = System.Convert.ToInt32(Request.QueryString["dum"]); SqlConnection conn = new SqlConnection("Data Source=**********************");SqlCommand comm = new SqlCommand(); comm.Connection = conn;SqlDataAdapter myadapter = new SqlDataAdapter(comm);DataSet myset = new DataSet(); conn.Open(); comm.CommandText = "Select * from Order_Forms where (Order_Num = " + Session["dum1"] + " ) ";
hi, is it possible to get the back up of a particular database automatically at fixed interval of time i.e for every two days or two hours etc. i am sql server 2005 as back end of my project. please show me a way in sql express edition too along with sql sever edition.
One can never consent to creep,when one feels an impulse to soar RAMMOHAN
We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?
Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?
I'm new at this so I apologize in advance for my ignorance.
I'm creating a website that collects dates in a calendar control (from Peter Blum). When the page containing the control loads it populates the calendar with dates from the database (that have previously been selected by the user). The user then can delete existing dates and/or add new dates.
I create a dataset when the page loads and use it to populate the calendar. When the user finishes adding and deleting dates in the calendar control I delete the original dates from the dataset and then write the new dates to the dataset. I then give execute the data adapter update command to load the contents of the dataset back into the database. This command involves using parameterized queries. For example the Insert command is:
Dim cmdInsert As SqlCommand = New SqlCommand("INSERT INTO Requests VALUES(@fkPlayerIDNumber, @RequestDate, @PostDate, @fkGroupID)", conn)
I am trying to recover a set of tables with data that was dropped. This data was not backed up. Please advice, is there any way this data can be recovered.
Hello,I am hoping you can help me with the following problem; I need to process the following steps every couple of hours in order to keep our Sql 2000 database a small as possible (the transaction log is 5x bigger than the db).1.back-up the entire database2.truncate the log3.shrink the log4.back-up once again.As you may have determined, I am relatively new to managing a sql server database and while I have found multiple articles online about the topics I need to accomplish, I cannot find any actual examples that explain where I input the coded used to accomplish the above-mentioned steps. I do understand the theory behind the steps I just do not know how to accomplish them!If you know of a well-documented tutorial, please point me in the right direction.Regards.
Hi,I m using vwd2005 and sql express,c#.I have a question here.In my database i have a table named Table1(id,name,age,country,email,phone) The values for these fields are inserted from the webform.Id is auto generated.name and age is passed from the textbox within the webform.country is passed from dropdownlist within the webform.email and phone is passed from listbox(multiple email and phone) within the webform.Now my problem is i want to retrieve all these row values back into the textboxes,dropdownlist and listbox at the same timeon the click of the retrieve button within the web form . Further more i want to make few changes on it and update the database on the click of another button update within the form.I hope u are getting it.How would the query would be in this case.?can u demonstrate the concept in one simple running example along with code?thanks.jack.
Hello Everyone,I 've trying to retreive data back into webform from the database from past couple of weeks.But i m not able to retrieve it completely.Here is my scenario.I've tried to explain in detail but in simple language.I hope u will get my point.Ok here it is.plz run my code:-i have three tables in sql.they are:-grup(id,grp) (1,abc) (2,xyz) organization(id,oid,gid,organisation,orgphone) (1,1,1,ibm,1234567) (2,1,2,microsoft,6543210) (3,2,1,oracle,7654323) (4,2,2,apple,9876543) userform(id,title,name,address,email,organizationid) (1,mr,jack,usa,jack@jack.com,1) userform.aspx page<%@ Page Language="C#" AutoEventWireup="true" CodeFile="userform.aspx.cs" Inherits="userform" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <div style="text-align: center"> <table> <tr> <td style="width: 100px"> Title</td> <td style="width: 100px"> <asp:DropDownList ID="DropDownList1" runat="server"> <asp:ListItem>mr</asp:ListItem> <asp:ListItem>miss</asp:ListItem> <asp:ListItem>ms</asp:ListItem> </asp:DropDownList></td> </tr> <tr> <td style="width: 100px"> Name</td> <td style="width: 100px"> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Add</td> <td style="width: 100px"> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 100px; height: 26px;"> Email</td> <td style="width: 100px; height: 26px;"> <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox></td> </tr> </table> </div> </div> <div style="text-align: center"> <div> <div> <asp:ScriptManager ID="ScripManager1" runat="server"> </asp:ScriptManager> <div> <table> <tr> <td style="width: 100px"> Group</td> <td style="width: 100px"> <asp:DropDownList ID="ddl1" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddl1_SelectedIndexChanged"> </asp:DropDownList></td> </tr> </table> </div> <div> <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <table> <tr> <td> Organisation </td> <td> <asp:DropDownList ID="ddl2" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddl2_SelectedIndexChanged"> </asp:DropDownList> </td> </tr> <tr> <td> org-phone </td> <td> <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox> </td> </tr> <tr> <td> </td> <td> <asp:TextBox ID="TextBox5" runat="server" Width="45px"></asp:TextBox></td> </tr> </table> </ContentTemplate> <Triggers> <asp:AsyncPostBackTrigger ControlID="ddl1" EventName="SelectedIndexChanged" /> </Triggers> </asp:UpdatePanel> <br /> <table> <tr> <td style="width: 100px"> <asp:Button ID="Button1" runat="server" Text="save" OnClick="Button1_Click" /> </td> <td style="width: 100px"> <asp:Button ID="Button3" runat="server" Text="update" /></td> <td style="width: 100px"> <asp:Button ID="Button2" runat="server" Text="cancel" /></td> </tr> </table> </div> </div> </div> </div> </form></body></html>userform.cs page public partial class userform : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { AssignDDLDataSource(); ddl1_SelectedIndexChanged(null, null); } } private void AssignDDLDataSource() { // SqlConnection string sqlQry = "SELECT GRP DISPMEM, ID VALMEM FROM GRUP"; SqlCommand cmd = new SqlCommand(sqlQry, con); ddl1.DataSource = cmd.ExecuteReader(); ddl1.DataTextField = "DISPMEM"; ddl1.DataValueField = "VALMEM"; ddl1.DataBind(); } protected void ddl1_SelectedIndexChanged(object sender, EventArgs e) { AssignSubjectDDL(ddl1.SelectedValue); } private void AssignSubjectDDL(string val) { // SqlConnection string sqlQry = "SELECT ORGANISATION DISPMEM, GID VALMEM FROM ORGANIZATION where OID = '" + val + "'"; SqlCommand cmd1 = new SqlCommand(sqlQry, con); ddl2.DataSource = cmd1.ExecuteReader(); ddl2.DataTextField = "DISPMEM"; ddl2.DataValueField = "VALMEM"; ddl2.DataBind(); ddl2_SelectedIndexChanged(null, null); } protected void ddl2_SelectedIndexChanged(object sender, EventArgs e) { AssignTextBoxValues(ddl1.SelectedValue, ddl2.SelectedValue); } private void AssignTextBoxValues(string teamval, string memval) { // SqlConnection string sqlQry = "Select ID,ORGANISATION,ORGPHONE from ORGANIZATION where OID = '" + teamval + "' AND GID = '" + memval + "'"; SqlCommand cmd = new SqlCommand(sqlQry, con); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { TextBox5.Text = rdr["ID"].ToString().Trim(); TextBox4.Text = rdr["ORGPHONE"].ToString().Trim(); } } protected void Button1_Click(object sender, EventArgs e) { // SqlConnection string sqlQry = "INSERT into USERFORM (title, name, address, email, organizationid) VALUES(@title,@name,@address,@email,@organizationid)"; SqlCommand cmd = new SqlCommand(sqlQry, con); cmd.CommandType = CommandType.Text; cmd.Parameters.AddWithValue("title", DropDownList1.SelectedValue.Trim()); cmd.Parameters.AddWithValue("name", TextBox1.Text); cmd.Parameters.AddWithValue("address", TextBox2.Text); cmd.Parameters.AddWithValue("email", TextBox3.Text); cmd.Parameters.AddWithValue("organizationid", TextBox5.Text); cmd.ExecuteNonQuery(); Response.Redirect("detailsview.aspx"); }} detailsview.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="detailsview.aspx.cs" Inherits="detailsview" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" EnablePagingCallbacks="True" Height="50px" HorizontalAlign="Center" Width="125px"> </asp:DetailsView> </div> </form></body></html> detailsview.cspublic partial class detailsview : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { //SqlConnection string str = "select title,name,address,email,grp,organisation,orgphone from userform as a inner join organization as b on a.organizationid=b.id inner join grup as c on b.oid=c.id"; SqlDataAdapter oda = new SqlDataAdapter(str, conn); conn.Open(); DataSet ods = new DataSet(); oda.Fill(ods, "info"); DetailsView1.DataSource = ods.Tables["info"].DefaultView; DetailsView1.DataBind(); }}My problem :-Now when user fills up the userform and clicks the save button the details are shown in the details view.Now i want to add a Edit hyperlink next to the records in the detailsview so that when i click on it i would be redirected to userform.aspx form with all the textboxes,dropdownlist etc be populated from the related data from the database.(here table userform).Now i want to edit these data in the userform.aspx page and finally when i click the update button at end i want all the data be updated in the database aswell as in details view too.at the same time i want this form to redirect to detailsview.aspx .i hope ur getting it?i m able to do partial part only.Not completely. So can u now help me modify the code accordingly?Thanks in advance.Jack.
I have the following code but do not know the best way to return the updatedDataTable back to the database. I believe I can use the Update method of theData Adapter, BUT if true, I also believe I have to 'long-hand' write codefor each individual column data that's being added......this seems a bitdaft considering that the data is already in the disconnected data table.Have I lost the plot?? Based on the code below, what is the correctapproach?Note: sqlcnn is defined at module level.Public Sub AddRequest(ByVal Eng As String, ByVal Bran As String, ByVal ReqAs String) Implements IHelpSC.AddRequestDim dtNew As New DataTable("dtNew")Dim drNew As DataRowsqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)sqlda.Fill(dtNew)'Add and populate the new datarow with'data passed into this subroutinedrNew = dtNew.NewRowdrNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToStringdrNew("Engineer") = EngdrNew("Branch") = BrandrNew("Request") = ReqdtNew.Rows.Add(drNew)End SubHope one of you wizards can help.Rgds.....and Merry Christmas.Phil
Hi guys,Got a problem now :( please help...now we got a project handling records saved in a table in a sql2000(will upgraded to 2005 soon) server. every month around a millionrecords will be inserted.now user raised a request, that is, once criterios are matched, theproject should do some backend handle, for example, ifSELECT colID, fieldC, fieldD FROM dataTable WHERE fieldA ='fieldA_valueB'returns some recordset, for each @fieldC, @fieldD we shall do someback-end trick, maybeUPDATE dataTable SET fieldE = 'fieldE_valueF' WHERE fieldC = @fieldCAND fieldD = @fieldDlet's say such a rule is named as rule01. Hope I'm expressing theproblem clearly?my questoin is, shall we do this in Database side, using triggers, orby informing our .net project to do it?1. since the records are coming around 1 millison per month, how can wehandling the performance issue?2. now the rules are still somewhat simple, seems at least we could doit by EXEC or SP_EXECUTESQL. but rules may turn quite complex, forexample, audit log, or more complex issues, shall we do it in .netprogram? but how can a trigger in SQL database informing a .netprogram, or webservice, or windows service? by executeing an executableconsole program?3. user may raise more and more rules, how could we provied a flexiblesolution? i mean we're trying to build it less hard-coded. seems in sqldatabase EXEC or SP_EXECUTESQL are still somewhat flexible, while in..net to do something like eval() in javascript or EXEC in sql server isa little bit troublesome. but, put all these bussiness logic in storedprocedure sounds a little bit weired.guys, hope i have made myself clear. any suggestion? Thanks very mcuhyours,athos.
When a SQL statement is executed against a SQL Server database is therea server-side setting which dictates the size of the fetch buffer? Weare having some blocking issues on a new server install which do notoccur on the old server until a much larger volume of data is selected.Any help appreciated.
I have question regarding SQL Transactional Replication methodology
1. Let's say successfully created SQL Transactional Replication and running / transferring data from publisher to subscriber
2. Now one day the source production / publisher SQL Server is down and the remaining DR SQL Server is up (subscriber)
3. Next day, we fixed and bring up the production / publisher SQL Transactional Replication server, then how can we sync back all existing data records from subscriber into publisher side ?
I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.
however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)
Here's my code
SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment WHERE [TicketID] = t.TicketID ORDER BY DateAssigned ASC) = t.TicketID
WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u INNER JOIN Profiles p ON u.UserID = p.UserID
Hello guys My server crashed but luckily I was not able to get back my files with help of recovery software. Now, all I have from the database are just sql server database primary data .mdf and sql server databaseTransaction Log Files .ldf. I need to restore these data back to sql server.
Please could someone tell me how to restore these two file types back to my sql sever 2007 database? Thanks netboy
I'm trying to get a list of items by there last invoice date, but I keep getting errors. I have to join two table to get all the information needed to match a date with an item number.
select i.[Invoice-date], l.[Item-no] from [Invoice] as i join [invoice-line] as l on i.[Seq-no] = l.[Seq-no] (Select MAX(I2.[Invoice-date]) as LastDate, l2.[ITEM-No] from [invoice] AS I2 JOIN [invoice-line] AS L2 on i2.[Seq-no] = l2.[Seq-no] GROUP BY L2.[Item-no]) ITEMNUMBER WHERE i.[invoice-date] = lastdate.[invoice-date] and l.[item-no] = itemnumber.[item-no]
However, I keep getting this..
Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'Select'. Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ')'.
