LutzM

Forum Replies Created

Viewing 15 posts - 4,801 through 4,815 (of 5,502 total)

RE: OPENXML problem

Glad I could help 🙂
When learning XQuery you should consider looking into the great series of articles by Jacob Sebastian on this site. Search for "XML Workshop".
His series covers a...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 11, 2009 at 12:47 pm

#1077999
RE: OPENXML problem

I'm not sure how openxml can handle typed xml date (meaning xml data using namespaces).
However, since you're using SS2K5 or above I strongly recommend to look into XQuery. It is...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 11, 2009 at 11:40 am

#1077961
RE: Combining Update Statements

I don't recommend dsdeming's solution since it will set all other values for column USER_ORDER_QTY to NULL that don't match the CASE condition!!
@dsdeming: you might want to change the code...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 11, 2009 at 10:39 am

#1077936
RE: I have a scenario where there are 4 records with same ID. I need to write a code so that i can take only last 3 records

Look for ROW_NUMBER() in BOL (BooksOnLine, the help system usually installed together with SQL Server).
It requires an column for sorting. So you have to think what would be the definition...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 11, 2009 at 10:16 am

#1077924
RE: horizontalTo vertical Columns

Ok, now it makes sense...
Would have helped a lot if the statement
SELECT 1,2,3,4,5,6
had SQL tags around (maybe even with expected output) to look like
SELECT 1,2,3,4,5,6
/* result set
(No column name)(No column...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 11, 2009 at 9:36 am

#1077887
RE: Combine SQL Query together

Lewis Dowson (11/11/2009)
lmu92, I agree you could do the same in one query but I find that it is both more maintainable and readable if you use the CTE method....

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 11, 2009 at 5:09 am

#1077725
RE: horizontalTo vertical Columns

The only difference I can see is the word "SELECT" aat the beginning of the output...
SELECT 'Select '+ stuff((SELECT ', ' + Value From @Temp FOR XML path('')),1,2,'')
Based on your...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 11, 2009 at 3:36 am

#1077705
RE: Best Practise

s_shah-977123 (11/10/2009)
...the max number of users is 15 per application.
A decision whether to go for one or more database(s) is much more related to the context to be stored than...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 10, 2009 at 2:43 pm

#1077546
RE: Combine SQL Query together

Why don't you add the WHERE clause to your CASE statement and do all in one query?
To get the count for each group would be the same as do SUM(CASE...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 10, 2009 at 2:19 pm

#1077525
RE: Incorrect syntax near 'xml' - SQL2000

I'm not sure if it helps to explicit cast the xml result as varchar(8000) or nvarchar(4000).
Another option could be to put the XML subselect into a separate (temp?) table with...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 10, 2009 at 2:01 pm

#1077507
RE: Solving the "Running Total" & "Ordinal Rank" Problems (Rewritten)

Thanxalot for the effort in (re-)writing this great article Jeff!
Adding cross-references to several other great articles and the amount of code to support/prove your point makes it not "only" an...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 10, 2009 at 1:20 pm

#1077473
RE: horizontalTo vertical Columns

Usually, a ColToRowConversion of this kind is done using the FOR XML clause:
SELECT stuff((SELECT ', ' + Value From @Temp FOR XML path('')),1,2,'')
Using your sample data it will result in...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 10, 2009 at 11:58 am

#1077426
RE: Import XML into a SQL Server 2005 table

The problem is described pretty clear:
The system cannot find the path specified
It looks like you're trying to open a file located on a client PC from a (SQL) server.
Either you...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 10, 2009 at 11:46 am

#1077415
RE: SQL select statement results to XL file

What do you mean by "XL file"?
Is it a typo meaning XML structure or do you want to store an "eXtra Large" file?
If the former, please search for "XML Workshop"...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 10, 2009 at 11:40 am

#1077411
RE: Import XML into a SQL Server 2005 table

The problem is you're trying to do the insert twice:
1)
FROM OPENROWSET(
BULK INSERT TestPB FROM 'C:\Documents and Settings\xyz\Desktop\test.xml',
SINGLE_BLOB) AS T(x)
) AS T(x)
and
2)
INSERT INTO TestPB(firstname, lastname)
SELECT
You...

Lutz
A pessimist is an optimist with experience.
How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

November 10, 2009 at 11:27 am

#1077406

Viewing 15 posts - 4,801 through 4,815 (of 5,502 total)