|
Hi Guys I need help with writing the following query
The Query
Select daydesc as description ,Features,<br> adfull, askprice, bedrooms as bedroom,rooms,<br> floorarea as sqm, (pADDRESS1 + ''+ PADDRESS2+ ''+PADDRESS2+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS ,directions <br> <br>from dbo.PROPERTY as Property<br> where property_id = @id for xml auto,elements
Which Currently returns this
< Property><br> <description>Space and comfort set the tone of this stylish four bedroomed dormer bungalow on the Moynalty Road just outside the village of Mullagh. Situated just short stroll into Mullagh village and all it's amenities, this proeprty offers a purchaser a spacious family home with open-plan kitchen, dining and living areas. Althrough deceptive from the outside, this property is surprisingly spacious and well-laid out with three bedrooms (one en-suite) and family bathroom on the first floor and a very useful double bedroom with adjacent guest w.c, large sitting room, kitchen/dining, separate living/dining and utility room on the ground floor. Viewing by appointment with the Auctioneers is highly recommended.</description><br> <features>Deceptively spacious family home <br>Quality cream fitted kitchen <br>Two fine reception rooms <br>Slate-tiled kitchen/dining room <br>Television points in all rooms <br>Just a short stroll or minutes drive from the village of Mullagh <br>Close to local Primary School<br>Close to shops, school, church, hotel, playgroup etc.<br>On bus route to secondary schools</features><br> <askprice>3.7000000e+005</askprice><br> <bedroom>4</bedroom><br> <rooms>Entrance Hall (5.38m x 1.78m)<br>Guest W.C. (2.39m x 1.40m)<br>Bedroom (4.35m x 2.51m)<br>Sitting Room (5.98m x 3.62m)<br>Kitchen/Dining Room (8.22m x 3.68m)<br>Utility Room (2.84m x 2.42m)<br>Dining/Living Room (5.38m x 2.84m)<br>Landing ()<br>Bedroom (5.16m x 2.38m)<br>En-Suite Shower Room (1.92m x 1.87m)<br>Bedroom (4.91m x 2.83m)<br>Bedroom (4.17m x 2.88m)<br>Family Bathroom (3.44m x 2.19m)<br></rooms><br> <sqm>166</sqm><br> <directions>From Kells, travel about 4.5 miles into Moynalty village, taking left at the RC church and travel almost two miles. The property is just outside the village of Mullagh on the right hand side.</directions><br></Property>
It works as expected However, what I would like to achieve is that within the Features field, which is held as a text field in the db, contains the features separated by carriage return statements. I would like to extract features XML so that it looks like
<Features><br><feature>blah</feature><br><feature>blah blah</feature><br></Features>
Is this possible ?
I thought about using something like
CHARINDEX(features,CHAR(13)+CHAR(10))as feature
But a little unsure of how to make it work? Any tips welcome
|
|
|
|
|
Some of the fomatting didn't appear to come through - you may like to modify your post.
Either remove the HTML from the post and click the "Ignore HTML tags in this message (good for code snippets)" or ensure that all opening angle brackets are properly defined as <
|
|
|
|
|
I don't understand, The post seems to be ok on my side?
|
|
|
|
|
cykophysh39 wrote: The post seems to be ok on my side?
There is a set of pre tags with nothing apparently in between.
|
|
|
|
|
Thanks for your time but I managed to find a solution
it goes something like this
select daydesc as description ,
cast('<feature>'
+ replace(cast(Features as varchar(max)), char(13)+char(10), '<feature>')
+ '' as xml) AS Features,
adfull, askprice, bedrooms as bedroom,rooms,
floorarea as sqm, (pADDRESS1 + ''+ PADDRESS2+ ''+PADDRESS2
+ ''+PADDRESS3+ ''+ PADDRESS4) AS ADDRESS ,directions
from dbo.PROPERTY as Property
where property_id = @id
for xml auto,elements
|
|
|
|
|
Fair enough. 
|
|
|
|
|
I am using ado from within an excel to update a table in a remote sqlsvr 2005 database. This is fairly knew for me. It works, but the creation of the recordset object takes about 15 seconds. I don't know if the delay is something I can do on my end (excel/vba/ado) or if it's a sql server issue. The table I am adding the record to is fairly large (at least to me it's big) at 1.6 million rows. All other ado statements run blindly fast. Below is my vba code.
Sub SendSchChg()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sConnString As String
sConnString = "Provider=SQLOLEDB;server=XXXXXX-SQL02;UID=xxxxx;PWD=xxxxx;DATABASE=datalog"
cn.Open sConnString
rs.Open "Select LineID, ProcessValueID, dts, PointValue from datalog", _
cn, adOpenKeyset, adLockOptimistic, adCmdText
With rs
.AddNew
!LineID = 12
!ProcessValueID = 60
!dts = Now()
!PointValue = 1
.Update
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
|
|
|
|
|
If you are just adding new records then you might want to try adding "where 0 = 1" to your select statement. That way it won't try to read the full contents of the SQL-Server table into your PC's memory.
|
|
|
|
|
Perfect! It flies now! Thanks!
|
|
|
|
|
I am trying to take Backup and Restore Using VB.NET or C#.NET
Can you please help me how to do.
With code snippet.
Yugan
|
|
|
|
|
There are no ADO.Net commands/methods which perform a backup. But you can submit a query using an ADO.Net command to perform a backup or restore the same way you would submit a SELECT, INSERT, UPDATE or DELETE query to the server. Your best source for syntax would be SQL Server Books Online (aka BOL). Here's a quick snippet to get you started (C#):
<br />
using(SqlConnection conn = new SqlConnection("connection string here")){<br />
SqlCommand cmd = new SqlCommand();<br />
cmd.Connection = conn;<br />
cmd.CommandText = "BACKUP DATABASE [datbase name here] TO DISK = N'file path and name of backup here'";<br />
cmd.CommandType = CommandType.Text;<br />
<br />
conn.Open();<br />
<br />
cmd.ExecuteNonQuery();<br />
}<br />
Just make sure that the file path of the backup is a path which exists on the server where SQL Server is installed. You may also want to set the CommandTimeout property if the database is very large.
|
|
|
|
|
Hi
How to find foreign key for a given table in sql server 2005.
exec sp_fkeys @pktable_name='authors' //It is working fine in sql 2000
But the same is not working for 2005.
exec sp_fkeys @pktable_name='[Production].[Product]'
also I tried
exec sp_fkeys @pktable_name='[Test].[Production].[Product]'
Can any one help me please
kesavan
|
|
|
|
|
Try this:
sp_fkeys @PKTable_name = N'Product', @PKTable_owner = N'Sales'
(This, of course, assumes that you have a product table in a Sales schema).
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks a lot pete
kesavan
|
|
|
|
|
hi,
i have a trigger on a table and in the trigger i want to RAISE an ERROR and also log the error into a table..
The error works fine (it is raised) but it seems there is no record in my logTable. i suppose the complete transaction is rolled back, including my insert into my logtable.
any idea how i can solve this problem can i exclude my insert into my logtable from the roll back ?
thx
Kurt
|
|
|
|
|
The normal way to do this is to insert the item into the log table inside a different transaction.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thx for the response,
so this means i can't do it inside the trigger ?
if the [After] trigger raises an error it rolles back al the previous actions. including the insert in the table (A) ... and including the insert in the error log table (B).
i suppose there must be a way to log errors in triggers on tables... without using an application but straight from the database server.. our i must use a stored proc to insert into my table (A) (the one that has a trigger) by using begin try and begin catch
Thx
Kurt
|
|
|
|
|
i am not getting how to generate a data set and configuration wizard of data adapter
Plz let me know the procedure for retrieving records from database using sql statement
|
|
|
|
|
Use it....
Dim CnnString as String ="server=serverIp;uid=test;password=test;database=yourdatabase"
Dim Cnn as new System.data.SqlClient.SqlConnection(CnnString)
Cnn.Open();
Dim AD as new System.data.SqlClient.SqlDataAdapter("Select * From TableName",Cnn)
Dim DS as New DataSet
AD.Fill(DS)
Cnn.Close()
For each Dr as DataRow in Ds.Tables(0).Rows
Msgbox(Dr("column1")
Msgbox(Dr("column2")
......................
.........................
next
Parwej Ahamad
g_parwez@rediffmail.com
|
|
|
|
|
I have an SQL datasource hooked up to a stored procedure. I am succesfully retrieving data and showing it in a data list. I would like to make the data list sortable. I have created links in the header section of the datalist and these also succesfully trigger events. I have also built the stored procedure to accept a sort parameter which would allow the results to be sorted on any criteria.
Ok so I have everything set up and working that I need. My problem now is how, on triggered sorting events, do I alter the sqldatasource, to pass the sort parameter back to the database. I have played with the SortParameterName="" property to no avail.
Thanks in advance.
|
|
|
|
|
If you switched to using a datagrid or better yet a gridview there is an allowsorting property to the gridview. Then all of the sorting is just done for you. I don't use datalists a lot, but if you need to use it I am guessing that you would have to do some manual binding to get sorting to work.
Hope that helps.
Ben
|
|
|
|
|
Yeah I am well aware of the Gridview and it's automatic sorting and paging features but it has the obvious constriction of having to use columns. In this case each row in my table has a nested table inside it which also needs to have sorting applied to it. There are also TD's that stretch over multiple TD's. Therefore a grid view would be inappropriate so I have had to use Datalist.
The thing is I wanted it to use the SQLDATASOURCE to pass the ORDERBY value to my SP just like the Grid View would do automatcally for you. I managed using the article above. You just need to create your own link buttons and rig them to set an ORDER BY value. Then simply databind the datalist, ensuring that the bind uses your sorting parameter and bobs your uncle youv got the exact same functionality.
Works a treat espceially with an UpdatePanel around it too!!
|
|
|
|
|
Hello
I am using Sql Server 2005.
I have two different servers.
I was trying to use the wizard of Copy Database in order to copy a DB from one server to the other.
After arriving to the last stage where the copying process starts, I get an error in the field of
Execute SQL Server Agent JOB : The job failed. Check the event log on the destination server for details.
and on the destination server in the event log I get the message :
Package "CDW_DT-A1_MLS09_1" failed.
where DT-A1 is the source server and MLS09 is the second server.
Anyone has ideas how to overcome this problem ?
Thanks,
Clint
|
|
|
|
|
You may want to check what you are trying to copy. For instance, if you try to copy a database user/role across - this might be causing it to fail.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hey
Thanks for helping.
I don't think that is the problem.
Can you see any other reason ?
I think that the server which I am trying to copy to has an older sqlserver 2005.
How can I do the copying ?
Clint
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
