ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,676 through 3,690 (of 10,143 total)

RE: problem with openquery

Put a PRINT statement prior to the EXEC.
PRINT @Query
EXECUTE sp_executesql @Query
Run the batch. Copy the output from the Messages window and paste it here.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 3, 2014 at 6:04 am

#1685449
RE: exec proce

elham_azizi_62 (2/3/2014)
please write T-Sql for me.thanks
If I do this, you will remain a novice. You must at least make an attempt to write the TSQL.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 3, 2014 at 5:51 am

#1685445
RE: Write a conditional query

Please post ddl and dml for the tables involved in your query.
This is quite a trivial request and there are several ways to do it - what have you...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 3, 2014 at 5:40 am

#1685440
RE: exec proce

Pass the string into the stored procedure as a string parameter and split it there. Splitting it first and attempting to pass in all of the resolved elements as a...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 3, 2014 at 5:36 am

#1685439
RE: Stored Proc And View Giving Different Results

That's okay, it was always going to be a long shot with no sample data blah blah.
I hope you can take away a few ideas from it though, and...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 31, 2014 at 9:35 am

#1685121
RE: Find max and min where count of unique > x...

Luis Cazares (1/31/2014)
Chris, I think you were expecting different results than the ones the OP asked for.
I would go for the last query from o.fimin.
Heh I see it now.
"In the...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 31, 2014 at 9:30 am

#1685115
RE: Stored Proc And View Giving Different Results

You might get some performance lift by jigging around with your filters. If you're going down this route then do take on board Gail's comments about DISTINCT. You might benefit...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 31, 2014 at 6:48 am

#1685038
RE: Find max and min where count of unique > x...

Always carefully test any solutions offered to you. The one you rejected works with the sample set, the one you appear to have accepted does not. Here's a third offering:
SELECT...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 31, 2014 at 5:29 am

#1685007
RE: How to update select number of rows without join

-- The requirement is the Table1.SetId (1, 2, 3...) is to be assigned to the Table2.SetId
-- for the same number of rows as the value of Table1.MsgCount. For example,
CREATE...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2014 at 6:58 am

#1684639
RE: Generate a Comma-Separated List

SELECT
s.subTypeId, s.subTypeValue, s.subparentId, s.childLevel, s.sortOrder,
s.leafFlag, s.aggregatesSubTypeId, s.sourceIdentifier, s.origId, s.RecId,
camasapavalue = '[' + x.ConcatenatedString + ']'
FROM #SAMPLEDATA s
CROSS APPLY (
SELECT ConcatenatedString = STUFF(
(SELECT ',' + CAST(subTypeID AS VARCHAR(30))
FROM...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2014 at 5:39 am

#1684609
RE: Need help with merge

Can you post up some sample data, Kapil? Showing multiple rows per key? Obfuscate if necessary, but it really would be very useful to see it.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2014 at 3:54 am

#1684582
RE: Need help with merge

Let's say you have a "dupe pair" of rows in your source table - dupes on the basis of a unique key on one or more columns.
Does it matter...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2014 at 3:18 am

#1684575
RE: Need help with merge

MERGE #temp T
Is the table #temp always empty before you start the upsert?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2014 at 2:46 am

#1684567
RE: Need help with merge

Use a traditional upsert.
What you're trying to do in your original MERGE is to update rows which don't exist.
Perform your inserts first from a deduped source set -...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2014 at 2:16 am

#1684556
RE: Are the posted questions getting worse?

wolfkillj (1/29/2014)
I created and used my first extended events session last week, and it was great - easy and extremely helpful. I just posted about it on my blog, if...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2014 at 1:56 am

#1684547

Viewing 15 posts - 3,676 through 3,690 (of 10,143 total)