SELECT ChannelCatID as '@type',
ChannelID as 'Channel/@id',
ChannelName as 'Channel/Name',
RSSFeedURL as 'Channel/URL'
FROM ChannelMaster
FOR XML PATH('Item'), ROOT('DOC')
It gives me this result
<DOC>
-<Item Type="xyz">
--<Channel id="a">
---<Name>aaaaa<Name/>
---<URL>aaaaa<URL/>
--</Channel>
-</Item>
-<Item Type="xyz">
--<Channel id="b">
---<Name>bbbb<Name/>
---<URL>bbbb<URL/>
--</Channel>
-</Item>
-<Item Type="xyz">
---
-</Item>
-<Item Type="xyz">
---
-</Item>
------
------
------
</DOC>
I want it this way :
all channels of Type xyz should come under singe <Item Type="xyz"> tag instead of seperate tags
what changes should i make, plezz help meout wit dis ??
how to insert many data in the database..?? i had the coding but it will return value to 0.. so how i'm gonna to make it appear my value.. <?php require_once('Connections/mas.php'); ?>
<?php
$host="localhost"; // Host name $username="root"; // Mysql username $password="123"; // Mysql password $db_name="mas"; // Database name $tbl_name="medic_registered_course"; // Table name
// Connect to server and select database. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB");
// Get values from form $matric_no=$_POST['matric_no']; $block=$_POST['block']; $s_code=$_POST['s_code']; $sessi=$_POST['sessi']; $year=$_POST['year']; $group=$_POST['group'];
// Insert data into mysql $sql="INSERT INTO $tbl_name(`matric_no`, `block`, `s_code`, `sessi`, `year`, `group`)VALUES('$matric_no', '$block', '$s_code', '$sessi', '$year', '$group')"; $result=mysql_query($sql);
// if successfully insert data into database, displays message "Successful". if($result){ echo "Successful"; echo "<BR>";
}
else { echo "ERROR"; }
// close connection mysql_close(); ?> <?php mysql_select_db($database_mas, $mas); $query_terd = "SELECT medic_block_reg.matric_no, medic_block_reg.block, medic_block_reg.name,medic_subject_offered.s_code, medic_block_reg.sessi, medic_block_reg.`year`, medic_block_reg.`group`, medic_block_reg.status, medic_subject_offered.s_title FROM medic_block_reg, medic_subject_offered ORDER BY `matric_no` ASC"; $terd = mysql_query($query_terd, $mas) or die(mysql_error()); $row_terd = mysql_fetch_assoc($terd); $totalRows_terd = mysql_num_rows($terd);
I have a datagrid in my file along with an Update Query.
My Update Query basically adds the numerical values in two columns together when the page is loaded. This means whenever the page is Refreshed the Update query is fired.
This is my Update Query (which is in an Stored Procedure):
UPDATE Rental SET TotalFee = ExtraFee + TotalFee WHERE DaysOverdue >= 0
I have declared my query in the 'Page_Load' part of the coding, because I want the query to run automatically. Not manually by a button.
My main question is that how can I get the query to run only once a day, no matter how many times the page is loaded.
I have a table with about 20,000 records. So, to minimise performance issues, i try to only retrieve the top 100 records. The databaset retrieved can be paged and sorted. Below is my code, but it's taking quite a while even to only load the 100 records. Any suggestions?Private Sub BindGrid() Dim connectionString As String = "MyConnectionString" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT TOP 100 [Person].* FROM [Person] ORDER BY " & viewstate("sortField").ToString() & " " & viewstate("sortDirection").ToString() Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet) Try DataGrid1.DataSource = dataSet DataGrid1.Databind() Catch e As Exception DataGrid1.CurrentPageIndex = 0 End TryEnd Sub
Requirement: I wanted to create another table based on the column values. For eg: I have to take the employee id and check for what value he has under owns column in the table. I take only 3 values and then these values should go to the newly created columns (owns1, owns2,owns3). if there is no value for any of these columns it should have null values loaded in them. The result of the modification should look like this:->
Note: eventhough employeeid 3 owns more than 3 things we only take 3 of what he owns and populate to above coloumns. In addition to it, the column OWNS will have more than 500 different values in them.
Its kind of urgent and if anyone knows how to , Can you please help me on this. Thanks a lot. -- Ragulan ;)
I am using a local variable to capture datetime and then select records from another table by making use of the above local variable result. But the query is running too slow when I use the local variable
declare @a smalldatetime select @a=last_run_time from job_status where job_des='sample'
SELECT * FROM History WHERE CHANGE_DATE> @a
Instead of the above select statement if I use the below statement it is returning results quickly. Can someone help me in tuning the above query. SELECT * FROM History WHERE CHANGE_DATE> '5/23/2008 6:22:00.000 AM '
History table has columns ( case number, change_date, change_desc). I have two indexes defined on the above table one is on case_number and the other on change_date.I tried using force index but still the query is running slow.
SET @propname = NULL -- can be Null or have wild card values
SELECT col1, col2, col3, propertyname FROM testtable where col1 = 1 and col2 = 2 and col3 = 3 and (@propname IS NULL OR propertyname LIKE @propname)
col1, col2, col3 are part of a clustered index
propertyname is a nonclustered index
This is the predicament. If the "@propname is NULL" is first in the OR statement then the query will use the clustered index for finding the record. If I put "@propname is NULL" last then it uses the propertyname index no matter what. So I either get full index scans if NULL is ever used on property names or I get consistent two second long searches on the clustered index. Any way to have the best of both worlds? Or do I have to divide up my query into more stored procedures?
I want to make a sqldatasource to insert data ito a table with values from textfields page. I want to use the sqldatasource programically (not bind it to a formcontrol). I drag a sqldatasource from the toolbox to the design surface and start configuring the datasource. I spesify a custom sql statement, select the "Insert" tab and insert the table I want to store into the builder. Then I select the fields and the values (parameters from tue textfields. I test it with the "Execute query" button and the results is stored in the table. (everything seems ok) I press the "OK" button but both the "Next" and "Finish" buttons are dissabled, o I can not store the query. What is going wrong. Can someone please help me ? Tom Knardahl
I have a pivot table query I am running and wanted to find out if there was a way to pull in the dates like getdate() - 12 months, getdate() - 11 months, etc. instead of hard coding the dates.
Here is my query
SELECT Client, [4/1/2007 12:00:00 AM] AS Month1, [5/1/2007 12:00:00 AM] AS Month2, [6/1/2007 12:00:00 AM] AS Month3, [7/1/2007 12:00:00 AM] AS Month4, [8/1/2007 12:00:00 AM] AS Month5, [9/1/2007 12:00:00 AM] AS Month6, [10/1/2007 12:00:00 AM] AS Month7, [11/1/2007 12:00:00 AM] AS Month8, [12/1/2007 12:00:00 AM] AS Month9, [1/1/2008 12:00:00 AM] AS Month10, [2/1/2008 12:00:00 AM] AS Month11, [3/1/2008 12:00:00 AM] AS Month12, [4/1/2008 12:00:00 AM] AS Month13, Engineer FROM (SELECT Client, DollarsBilled, SlipDates, Engineer FROM dbo.MonthlyClientBillables) p PIVOT (SUM(DollarsBilled) FOR SlipDates IN ([4/1/2007 12:00:00 AM], [5/1/2007 12:00:00 AM], [6/1/2007 12:00:00 AM], [7/1/2007 12:00:00 AM], [8/1/2007 12:00:00 AM], [9/1/2007 12:00:00 AM], [10/1/2007 12:00:00 AM], [11/1/2007 12:00:00 AM], [12/1/2007 12:00:00 AM], [1/1/2008 12:00:00 AM], [2/1/2008 12:00:00 AM], [3/1/2008 12:00:00 AM], [4/1/2008 12:00:00 AM])) AS pvt
I.E. I WANT two columns C1# and C2#, where C1# contains data from 2015 and C2# contains data from previous year (2014). If 2015 data is not present, then C1# will contain data of 2014 and C2# will contain data of 2013.
Hello, I have a table of data with the following columns:ParticipantResult (win or lose)The table has other data in, but I want to make a league to sort by the amounts of wins. For example: Participant Won PlayedA 5 10B 4 7C 3 3 I want this to show up on web page. I was hoping to do this through the source code on the web page, but I am struggling to do it.Is there a better way to do this, or a place where I can see an example of the code. Many Thanks, Oliver
I hope I can explain this somewhat coherantly. Here goes..
I have a products database. I want to select product details for items having distinct style codes. IE if there are 20 products that different from one another in some way, and 3 different style numbers throughout them, I want to end up with 3 products selected. I don't care which ones, as long as i have a sample product for each style. I've attemped to use GROUP BY to do this, but it's not giving me what I want. I managed to get right number of products, but the query seems to have mixed and matched the fields together in an undesireable way. I guess this kinda makes sense, since providing aggregate functions for each of the extra fields is necessary. SO THE QUESTION IS: How do I select (number of distinct styles) products, and still keep the records in tact as they should be?
Here is a sample...
Code:
SELECT DISTINCT Min(team) as team, min(pict) as pict, min(colors) as colors, min(league) as league, min(product_description) as product_description, min(category) as category, min(subcategory) as subcategory, min(style) as style FROM prods WHERE (style='style1' or style='style2' or style='style3') AND league='leaguex' AND soldout='' GROUP by style ORDER by style
Thanks very much for any help!! I'm sure I'm going about this all wrong.
What is the best method/model for having real time replication set up and also needing the ability to drop and recreate SP's/Tables/UDF's etc? I keep getting the error of the SP is being used in replication so it cannot be dropped...
Hi alllI have these tables below with 3 fields each. I want to get the recordin table 1 whose field number value is same in table 2 but field number2 on both tables are different. I mean i want the record1500 800 2. Insight: Table 1 is modules ordered and table 2 is modulesdelivered. I want to get 1500 800 2.beacuse module 800 was ordered butin table 2 module 503 was delivered. can some one help me with a joinnice weekeendTable 110 5012 101600502100 502 31500800 2Table 21500503 114004000100502 10100600100502 3
Please see the detail of tables with fields in SQL server 2000.·TableStudent .Table Good Qualities( It is a list of Students) (It is a list of Good Qualities)1-Student Id (P.Key)1- Serial No. (P. Key)2-Student Name 2- Good Quality· Table Bad Qualities .Table Future Plans(It is a list of Bad Qualities) (It is a list of Plans)1-Serial No. (P. Key) 1- Serial No. (P. Key)2-Bad Quality 2- Future Plans·Table-Personality1-Student Name/ID2-Good Qualities3-Bad Qualities4-Future PlansData will store in Personality table like thatStudent IDG.Q.No B.Q.NoF.P.No101 3 2 1101 4 5 NUll101 8 Null Null202 4 8 9202Null 3 1Required ResultNow I have to generate a report of One student andsometimes of many students comprising of their Name, G.qualities, BadQualities and Future Plan. To generate report User input will be onlystudent id.I Need help in making relationship of these tables as well as inMaking a Query. Any suggestion to get the required result. Speciallyplease look at the Personality Table because I do not understandPrimary key for it. Should I just include the Serial No. as Primarykey.Please make necessary changes to get the required result.I shall be really thankful to you for your kind favor.
I have a dimension that has the following members:
Directors
Senior Managers
Managers
Senior Associates
Associates
But they way they need to be displayed includes subtotals. Senior Managers and Managers are placed into Management and Staff is a total of everyone except Directors. They should appear on the report in something like this:
Directors 10
Senior Managers 15
Managers 9
Management 24
Senior Associates 17
Associates 40
Staff 81
Total 91
What is the best way to do this? Right now I am using a matrix because I am also showing headcounts by Period. Is a table a better way to go? Is it something that should be done in my dimension?
I can do the Total part. I am just not sure how to make the subtotals inbetween the other lines.
I have been reading up on how replicating schema changes work, but I must be missing something. When I configured a database for replication, it added a rowguid column to each table. This I understand.
I want to test making a schema change and replicating that change. For example, I tried to add a column to an existing table. When I tried to save the table i get the following error:
"Error validating the default for column rowguid".
"- Unable to modify table. It is invalid to drop the default constraint on the rowguid column that is used by merge replication. The DDL operation failed inside merge DDL replication manipulation. The transaction ended in the trigger. The batch has been aborted."
So, what am I missing? Do i need to drop the rowguid column, add the new column, and then re-add the rowguid column?
In my ScriptTask, i am checking the file validity. Now if the file is found incorrect, I do not want the DataFlowTask and other to execute. Instead i want the file to be moved to Error and the loop to proceed with the next file. If the file validity is correct, DFT and ExecuteSQL execute. How do i do this?
Some explanation supported by examples would help.
hi I am not able to create connection string through vwd express for sql express database file n getting a error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) plz tell me where is the problem n how can i resolve it Thanks n with regardsCN
Hi Guys, I came here again to ask something about my application, Is there a way to make 2 columns unique with each other even if they don't have a primary key? If there's a way how? I wanna make a rule so, my application wont replicate its record. Graciaz,Nhoyti
I have no idea but want to learn it how to make random names with sql server... I have a table, called table1, for colums Firstname and Last name I want it to make random names, so much it is possible it can get, in table2 where the colums is named Names
Playing with DTS for converting text data to SQL Server table and sending email messages. Package is to be scheduled hourly.
1. Is there any easy way to force DTS to choose Success or Failure based on a SQL Task? Wish to check contents of a table for new values and branch accordingly in DTS.
2. When new records are brought in I wish to build a custom email message rather than attaching a text file. Any Idea how to build a custom message?
I am attempting to auto scan an error log on a Unix based system and email myself when errors occur. This is an exercise to test scheduling of jobs, bridging between technologies and sending out meaningful messages based on the process via email.
Is there any possible way to make a row delete itself at a certain date and time? I am tring to make an "On_Sale" table and perticular items(rows) must expire at a certain Date and time. I have an idea to delete a row when it expires but it will cause an extra burden on my application. So is it possible to have SQL responsible for deleting a row at a certain time and date?
Also, I have another question: when I asign a PK Identity to a certain column is there a way I can enforce consecutive order of PK values? For example if I delete a row and the PK value was 5 it will reoder the whole table so all PK values are in consecutive order:
hi. i'm trying to make a report in asp that sums up a whole bunch of statistics. the report is quite lengthy and takes about 2 minutes to load. i thought i would solve that problem by making a table to dump the statistics into instead of calculating the statistics every time the report is viewed. this way, the page would just read the table with the statistics already put in it. these statistics would be updated every day or two so i don't need anything up to the minute.however, i have run into a small problem which is actually updating the information in the table. my new table is called OfficeReport and it looks like this:UserID Stat1 Stat2 Stat3 ...... Stat3223 0 0 0 056 0 0 0 072 0 0 0 0this is a query for one of the statistics i am currently using:SELECT DefendantCase.UserID, COUNT(DefendantCase.UserID) AS CountOfUserID FROM DefendantCase LEFT JOIN UserDescription ON DefendantCase.UserID=UserDescription.UserID WHERE UserDescription.Status=1 AND UserDescription.UserType=1GROUP BY DefendantCase.UserIDif i run this query, this is what i get:UserID CountOfUserID54 2160 1052 294 427 22how can i modify this query so its output updates the same UserID column in the OfficeReport table? Thanks!
I have a table to which data is being imported via bcp. When I get this data there are some fields nvarchar that has some decimal, for instance 1098.000000 is always 6 zeros..What I want to achieve is to make the above number only 1098
So what I did was this: LEFT (AMOUNT, LEN(AMOUNT)-7) AS AMOUNT
But i get an error saying invalid lenght parameter passed to the left or substring
