|
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.
|
|
|
|
|
mjackson11 wrote: pull values from this table and create dynamic SQL
That's insane, but doing it in other code probably won't make much of an improvement.
However, what I would prefer to do is to have a Connection to each database open and copy the data via a DataReader and ExecuteNonQuery. One of the benefits of this technique (in my experience) is the ability to log progress and errors as appropriate. This also makes moving one of the databases to another server less problematic.
|
|
|
|
|
PIEBALDconsult wrote: That's insane
Insane it may be, but, there are business cases where this method is, in fact, required.
We have a data warehouse system that needs to extract data from a various third party or legacy systems. We have no control over if and when the database structures will change in the other systems and we do not have access to the databases to add views to define the data we need.
Since I, personally, DETEST seeing SQL statements hard-coded in an application, I am left with the option of storing the SQL statements for use at run-time.
So.... the SQL statements are stored in a database and SQL statements are dynamically 'built' for execution.
Is it perfect? No.
Does it work? Yes.
If someone can present a better solution that fits into the business climate at hand, I would be more than willing to listen.
Tim
|
|
|
|
|
Tim Carmichael wrote: Since I, personally, DETEST seeing SQL statements hard-coded in an application
That's where they belong, so you know they can't be easily changed. And it is to be hoped that any changes are properly tracked in your version control system with a proper paper trail and then tested.
Stored procedures are too fragile and should be avoided at all costs. I've even had stored procedures "disappear"... there one day, gone the next.
|
|
|
|
|
Hi All,
I am Stuck with xp_cmdshell to execute a Vb Script file?
Can Any one tell me about the folder permission i have to do on the folder when excuting through commandshell?
Thanks
|
|
|
|
|
Do not repost the same question. Just continue with your thread below.
|
|
|
|