I'm hoping someone can help me. I am trying to generate query results in xml. I am using FOR XML AUTO and FOR XML AUTO, elements.
The problem is that the results are returned in xml but are trunacted - the entire results set is not returned - the results cut off midway through one row...
I am using SQL 2000 - I have installed sp2 - this made no difference in the behavior
I'd be grateful for any info anyone has on this. Thanks!
I've never been able to grasp the usage of JOIN when querying the database, and would like some help building the correct SQL query for my task.My database table looks like this:
tableid parentid item
1 NULL Fruits
2 NULL Colours
3 NULL Sizes
4 1 Apples
5 1 Grapes
6 1 Bananas
7 1 Peachs
8 2 Red
9 2 Blue
10 2 Green
11 3 X-Small
12 3 Small
13 3 Medium
14 3 Large
15 3 X-Large
16 4 Granny Smith
17 4 Golden Delicious
18 10 Lime Green
19 10 Dark Green
20 10 Light GreenAs you can see this table uses internal foreign key linking (not sure what the correct term is). I am trying to write a query that will JOIN the correct columns and their values (...and then have it set using "for xml auto". My expected generated xml should look like this (note that I am not outputting the XML because it will be used as a datasource, but this is for clarity):<Categories> <Category ID="1" Name="Fruits" checked="false"> <SubCategory Id="4" Name="Apples" checked="false"> <SubSubCategory Id="16" Name="Granny Smith" checked="false" /> <SubSubCategory Id="17" Name="Golden Delicious" checked="false" /> </SubCategory> <SubCategory Id="5" Name="Grapes" checked="false" /> <SubCategory Id="6" Name="Bananas" checked="false" /> <SubCategory Id="7" Name="Peachs" checked="false" /> </Category> <Category ID="2" Name="Colours" checked="false"> <SubCategory Id="8" Name="Red" checked="false" /> <SubCategory Id="9" Name="Blue" checked="false" /> <SubCategory Id="10" Name="Green" checked="false" /> <SubSubCategory Id="18" Name="Lime Green" checked="false"> <SubSubCategory Id="19" Name="Dark Green" checked="false"> <SubSubCategory Id="20" Name="Light Green" checked="false"> </SubCategory> </Category> <Category ID="3" Name="Sizes" checked="false"> <SubCategory Id="11" Name="X-Small" checked="false" /> <SubCategory Id="12" Name="Small" checked="false" /> <SubCategory Id="13 Name="Medium" checked="false" /> <SubCategory Id="14" Name="Large" checked="false" /> <SubCategory Id="15" Name="X-Large" checked="false" /> </Category></Categories>Can anyone help me with this query?
Since upgrading from SQL Server Management Studio 2008 R2, I've noticed that it no longer autosaves queries that have not been manually saved first. If a file has been manually saved the autorecover files end up in the following directory:
%appdata%MicrosoftSQL Server Management Studio11.0AutoRecoverDatSolution1
However, I have ended up in the situation where I have unsaved queries when my computer has crashed and have not been able to recover them.
I have also found references to .sql files stored in temp files in the following directory, but the files here seem to be very haphazardly caught:
Hi all, I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file. How can i do that. I am using asp.net vb.net.
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
I get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. conn.Open() Dim strSql As String
Does anyone know how to use for xml auto that will format an xml response to show the parent/child relationships? I currently have the parents and children in a temp table and can only get a formatting so that each one is returned at a parent. how can i select the results from the temp table so for xml auto will return the properly formatted xml file?
The upgrade adviser for for 2k5 says something about derived tables being handled differently between 2k and 2K5 and it says to query the tables directly but this does not seem to make much sense because I thought FOR XML AUTO just created some generic XML for presentation purposes. These 2 stored procedures that it is complaining about do query the tables directly and they use the FOR XML AUTO to control the output.Does anyone know if I have to worry about this? I am tempted to let this slide and check out this part of the application after the migration happens tomorrow for QA to start testing.Yes I have been googling, checking my books and digging around in BOL. I am not seeing anything.DISREGARD: I found my derived table. It appears to change the output of the XML. Perfect.
I am using sql server 2000 and want to know how to get xml out of the database that looks like this using for xml auto <Clients> <Client ID="1"> <Employer="Company1" /> <Employer="Company2" /> <Contact type="phone"> <contact type="email" value="test@test.com"> <contact type="phone" value="555-5555"> </Client> <Client ID="2"> <Employer="Company3" /> <Employer="Company4" /> <Contact type="phone"> <contact type="email" value="test@test.com"> <contact type="phone" value="555-5555"> </Client></Clients> The problem I am having is that Contact is nested inside employer when I select Employer before Contact and the opposite happens when I select Contact first. They both join to the Client table so I would assume they both should nest directly under Client. How do I get different fields to nest directly under the same element like above?
I have to generate mails automatically based on databse (SQL SERVER) table,In that table we have expirydate as one column and based on expirydate I have to generate the mails automatically,Please guide me to solve this issue. where we have to run the stored procedure. Do we have to use jobscheduler? please guide me how to use it Thanks in avance regards, Raja.
Hi all, I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it. So if I for example select all Dates older than today's date, I would want something like this:
1 10/12/2006
2 10/18/2006
3 10/20/2006
4 10/22/2006
5 10/30/2006 Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually". Is this possible? And if yes, how ? :)
Hi All, I want to write a console application to send email. There is a Date field in the SQL Server and I need to send email 2 weeks before that date.I have no idea how to write a console application and make it work.Does anybody have code for this? If so please post it. Thanks a lot, Kumar.
It's been a long time since I've had to check an index for the highest value, then add 1, to create a new unique key. These past few years, it seems this is usually done for you. But now that I'm working with MS-SQL, I don't see it. Is it there? It's doesn't seem to be inherent in the definition.
Hello, Firstly Hello to everyone I'm new the forum and fairly new to .net I'm working on web datbase application using visual studios 05 and MS SQL05 I've used 2003 (briefly) before but 2005 is very new to me. To my problem I download the GUI interface from microsoft so I can now setup a local database and do my own testing. I have created the table and fields with in it however on a particular table i have made a primary Key and left it as an INT but I would like to set it as auto increment ! I dont know how to select that option as i was used to mysql way of doing things or does this have to be done as a stored procedure ? Any assistance much appreciated.
I am very new to using SQL server 7. I've always used mysql in the past. I cant figure out howto create a autoincrementing key for my tables... is it possible to do in SQL7?? If so.. how.. i thought you just set the datatype to auto increment etc...
can anyone give suggestions how to generating a number starting with certain numbers, example 33###, because when i insert new record into datatabase i want the number start 33111, or something and next record is 33112 thanks
My requirement is to get the autoincrement column once a new row is inserted, we need the autoincrement value to update other tables, at present I am using an insert trigger in which I am extracting the autoincrement column from the 'inserted' table, but how far this work perfectly when multiple users insert simultaneously. Can any of you suggest me the best way to extract the actual value inserted.
Now the scenario is :
sp which insert a row Begin tran insert ... select @returnKey = (select retkey from #temptab) drop #temptab Commit Tran
Trigger on insert
insert idcolumn into #temptab select autokey from inserted
If user A & B inserts row exactly at same time, will this method return the exact auto value what A and B have inserted to them respectively.
I would like to avoid using a cursor. I am updating several rows in a table with sequential numbers starting at a number I pass into the Stored Procedure. Is there a way to do this with one update statement?
Users for a long time have been able to post new topics in our forums. However, a short time ago, the some users began to experience problems. What I have narrowed it down to is that upon inserting into the table, sometimes id value for the topic is the same as an id that is already in the table, so it fails to insert the record (due to a constraint). However, the topic id column is an auto-increment column and should just assign the next number for the id value.
I have a SQL server though a hosting company and I am trying to send autoemails using xp_sendmail. The permissions were set and I used the following command to test it.
Can someone explain me how does work that flags? # “Auto Close Flag� # “Auto Create Statistics Flag� # “Auto Shrink Flag� # “Auto Update Flag�
ok ok, I suppose that they shrink, update statistics and stuff automatically, but... when? every second? is it ok to leave all them as true?
I have important a table from mircosoft access into ms sql server 2000, I've created a new ID row and set the primary key - but I need to use the ID from microsoft access as well. therefore I'd like to add an auto increment +1 on the old access ID field... but how do I do that?
