|
If you want to search for the answer, look at my last reply to the question below.
If you want the answer look at my second reply to indian143 (20:38 28 Jun '10) four questions down.
|
|
|
|
|
Hi All,
Can anybody please help me in executing a query using For Xml Path. Because query with this if I put it in my SQL Server editor, and run it, its giving errors. Is there any specific way to use For Xml Path. Or should I have anything to use it in my SQL Server 2008.
Because any query from any browser is giving errors. What should i do to get rid of it.
One more thing I found in my SQL Server is when I open the "new query" window in my SQL Server and try to write the "FOR XML" I get the RAW and Auto in the intellisense and I dont find the Path mode coming in the intellisense.
Am I miising any dll or any installation process. Because if I write any query using "For Xml Path" not at all compiling.
Can anybody help me in getting this done. I need to use the "For Xml Path", please help me out. Any links or any kind of help is very very welcoming.
Thanks in advance.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
modified on Tuesday, June 29, 2010 9:13 PM
|
|
|
|
|
Could you give us the error detail, transact sql statement as well.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
Is your database in 2000 compatibility mode? FOR XML PATH was introduced in 2005.
|
|
|
|
|
I have run this exec to change the compatitbility
"EXEC sp_dbcmptlevel Ppsapp, 90". But it is not accepting 90 the "SQL Server 2005" compatibility level. It is giving following error
"Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of database compatibility level are 60, 65, 70, or 80.".
Please give me your idea for changing the compatibility level. May be thats why the query is not working.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
80 is Sql Server 2000, 70 is Sql Server 7 etc. You are connecting to a Sql Server 2000 instance not 2008. You cannot use FOR XML PATH. I suggest that you search google for "sql aggregate string concatenation". You may also find this link helps http://www.projectdmx.com/tsql/rowconcatenate.aspx[^]
|
|
|
|
|
Hi All,
We are trying to carry out a mail merge in Word 2007, which should be straight forward only we are running into issues.
We have a SQL Anywhere 10 ODBC Data Source setup on the machine and we are trying to access this database to populate certain areas of a letter. We used the step-by-step mail merge wizard and at Step 3 (Select Recipients), with "Use an Existing List" selected we click on the Browse hyperlink, which opened the "Select Data Source" window, where "+Connect to New Data Source" is selected, which in turn opens the "Data Connection Wizard". The ODBC Data Source previously created is selected and the username and password entered, the test on the connection is successful but when clicking the Ok button we receive the following message:
"The Data Connection Wizard cannot obtain a list of databases from the specified data source"
Does anyone know the steps needed to get around this issue?
Thank you,
Mel
|
|
|
|
|
how the datetime is written in a particular order using sql function?datetime format which i used is
Dec 2 2010 9:40:45:000PM
Dec 02, 2010 11:59:59:000PM
Dec 3 2010 3:45:54:000AM
Dec 3 2010 1:24:23:000AM
Dec 3 2010 12:15:27:000AM
how these datetimes in a correct order?pls help me...
|
|
|
|
|
So you have made the most basic error in data storage, you have stored your dates as varchar. You will have trouble until you reformat your data to datetime. Do it NOW!
However this may work on those formats
Select *
From Table1
Order by convert(datetime,StupidVarcharDateField)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Wow. You got my five for figuring out what the OP wanted. I read the post several times and was left scratching my head.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Chris Meech wrote: was left scratching my head
Been there many times, thankfully between the lot of us someone generally gets the idea the OP is trying to convey.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
convert(datetime,Field) that's it.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
Hi All,
I want a query to get all the column values as comma seprated values, but it should be group by in such a way that it should get the group by value in the prefix of the values of that group. Means the group column value should appear only once and other description column values for that group should precede it.
And all the rows should come as one string. Can anybody help me in writing this query.
Sorry I forgot to tell you that I am using SQL Server.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
modified on Monday, June 28, 2010 5:16 PM
|
|
|
|
|
Can you show us a sample of the data and the output that you want. It would also be nice if you showed us how you have tried to tackle the task.
|
|
|
|
|
Like you have two columns Category and Item. Now I want all the items and categories should come in one single row but with different categories items should be separated by their categories.
For example
You have
Cat1 Item1
Cate2 Item 2
Cate3 Item3
Cate1 Item 2
Cate1 Item3
Cate2 Item1
Cate2 Item 3
Cate3 Item1
Cate3 Item2
Then my output should look as follows
cate1: Item1, Item2, ITem3/Cate2: Item1, Item2, Item3/Cate3:Item1, Item2, Item3.
Can you please help me in achieving it, in performance oriented way. They need that too.
Sorry I forgot to tell you that I am using SQL Server.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
CREATE TABLE #data
(
Category VARCHAR(10),
Item VARCHAR(10)
);
INSERT INTO #data (Category, Item) VALUES ('Cate1', 'Item1');
INSERT INTO #data (Category, Item) VALUES ('Cate2', 'Item 2');
INSERT INTO #data (Category, Item) VALUES ('Cate3', 'Item3');
INSERT INTO #data (Category, Item) VALUES ('Cate1', 'Item 2');
INSERT INTO #data (Category, Item) VALUES ('Cate1', 'Item3');
INSERT INTO #data (Category, Item) VALUES ('Cate2', 'Item1');
INSERT INTO #data (Category, Item) VALUES ('Cate2', 'Item 3');
INSERT INTO #data (Category, Item) VALUES ('Cate3', 'Item1');
INSERT INTO #data (Category, Item) VALUES ('Cate3', 'Item2');
SELECT Category,
STUFF((SELECT ',' + Item AS [text()]
FROM #data AS d2
WHERE d2.Category = d1.Category
ORDER BY Item ASC
FOR XML PATH('')), 1, 1, '') as ItemList
FROM #data AS d1
GROUP BY Category;
DROP TABLE #data;
|
|
|
|
|
This query is not in SQL Server 2008. Should I change anything for running this in SQL Server.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
I wrote this in Sql Server 2008.
|
|
|
|
|
Sorry for troubling you but it is giving error "Incorrect syntax near the keyword 'FOR'". And I dont know much about using FOR in T-SQL. If possible can you pls. help me in that. Why is it coming. I tried by moving ")", but doesnt work.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
I have checked the code in Sql Server Management Studio 2008 and 2005 by creating a new query, copying the code into it and running it; It works fine. How are you trying to run the code?
|
|
|
|
|
Hi Russel,
I have give the example in the previous reply, can you please give me any solution to do it. Its urgent requirement.
For example
You have
Cat1 Item1
Cate2 Item 2
Cate3 Item3
Cate1 Item 2
Cate1 Item3
Cate2 Item1
Cate2 Item 3
Cate3 Item1
Cate3 Item2
Then my output should look as follows
cate1: Item1, Item2, ITem3/Cate2: Item1, Item2, Item3/Cate3:Item1, Item2, Item3.
Can you please help me in achieving it, in performance oriented way. They need that too.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Please do not demand answers otherwise you will find that no one will help you.
|
|
|
|
|
I am sorry pls. I did mistake pls. forgive me. In hurry I did a blunder. Please sorry.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
We have a legacy database that is "configurable" by setting values in a settings table. Many of the SP in the database then pull values from this table and create dynamic SQL to query the DB. We are now having performance issues with these queries.
As an example, we push data to another DB on the same server. The name of that database is stored in the table. The SP retrieves the name, creates a SQL statement and then uses sp_execute to execute the statement.
Is there a better way to handle this so that the SQL can be compiled and optimized?
Thx very much.
Mark Jackson
|
|
|
|
|
Before you make any changes, verify where the performance degredation is.
Have you used SQL Profiler ? You can trace all kinds of things and try to determine what is going on. Generally, if you see high CPU usage on database server, it is an indication of lots of dynamic SQL being parsed / executed. You should see low CPU and lots of I/O on a normally operating db server.
Maybe you just need to re-create the indexes.
|
|
|
|