ChrisM@Work

Forum Replies Created

Viewing 15 posts - 6,811 through 6,825 (of 10,143 total)

RE: sp_send_dbmail

Walton (7/27/2011)
Thanks Brandie.
I think i need to go with the option 1. But I am curious about the hidden ansi character. Would you please explain what is ansi hidden character...
^{“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

July 28, 2011 at 6:40 am

#1360024
RE: Today's Random Word!

bopeavy (7/27/2011)
crookj (7/27/2011)
bopeavy (7/27/2011)
--------------------------------------------------------------------------------
Brandie Tarvin (7/27/2011)
--------------------------------------------------------------------------------
bopeavy (7/27/2011)
--------------------------------------------------------------------------------
Ray K (7/27/2011)
--------------------------------------------------------------------------------
WOTD: coffee
Tea
Green
Green green grass of home....
Grass
ERROR -- Does not compute!
It's not unusual.
^{“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

July 27, 2011 at 7:57 am

#1359268
RE: Stored Procedure - Urgent Help Needed

Thanks for stepping up Brandie 🙂
I tried it with a 2-column and 3-column output. Since the result sets are both run into the same table, you get a column mismatch...
^{“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

July 27, 2011 at 6:21 am

#1359148
RE: Stored Procedure - Urgent Help Needed

Here's an easy way but it depends upon the structure and content of the two result sets:
-- Make a test proc returning 2 result sets
CREATE PROCEDURE dbo.junk AS
SELECT SetID...
^{“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

July 27, 2011 at 2:29 am

#1359007
RE: # of characters

pdanes2 (7/26/2011)
ChrisM@Work (7/26/2011)
That's slick, and definitely going in my toolbox. I never would have guessed that could be done with set-based code.
Thanks! Must admit though, it's borrowed from the teachings...
^{“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

July 26, 2011 at 6:59 am

#1358468
RE: # of characters

DECLARE @WordToCheck VARCHAR(30) = 'SQL SERVER'
SELECT Letter, COUNT(*)
FROM(
SELECT Letter = SUBSTRING(@WordToCheck, n.n, 1)
FROM (SELECT TOP 30 n = ROW_NUMBER() OVER(ORDER BY [Name]) FROM sys.columns) n
WHERE n.n <= LEN(@WordToCheck)
) d
WHERE Letter...
^{“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

July 26, 2011 at 4:59 am

#1358360
RE: # of characters

What datatype is the string?
DECLARE @Variable_Char CHAR(30), @Variable_Varchar VARCHAR(30)
SET @Variable_Char = 'String to measure '
SET @Variable_Varchar = 'String to measure '
SELECT LEN(@Variable_Char), LEN(@Variable_Varchar), DATALENGTH(@Variable_Char), DATALENGTH(@Variable_Varchar)
SELECT LEN(REPLACE(@Variable_Varchar,...
^{“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

July 26, 2011 at 2:30 am

#1358292
RE: Valid RBAR scenario

Decide on the logic then worry about the code. For instance, you could stop traversing if a previous number is reached and use the prior number. You could set 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

July 22, 2011 at 7:05 am

#1357026
RE: Strange error in TSQL 2005

"...on server. It works fine on mine local machine..." - do you mean you have installed a local server and it runs ok there? Are the functions dbo.split the same...
^{“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

July 22, 2011 at 6:43 am

#1357014
RE: Improve the efficiency of SQL query?

Dom, can you confirm which version of SQL Server you are connecting to? Cheers.
^{“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

July 22, 2011 at 5:40 am

#1356965
RE: Multiple Full Joins

rgtft (7/21/2011)
Chris,
Wouldn't your SQL miss the case:
Acct2 Sec77 Only in Account
I was playing around with this; that's the only reason I ask.
You're absolutely right, good spot.
^{“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

July 21, 2011 at 10:32 am

#1356591
RE: Multiple Full Joins

-- Naming a table [Index] is NOT advisable.
SELECT ad.ACCOUNTID, x.SecurityID,
MatchStatus = CASE WHEN a.ACCOUNTID IS NULL THEN 'Only in Index' ELSE 'Matched' END
FROM [INDEX] x
CROSS JOIN (SELECT DISTINCT...
^{“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

July 21, 2011 at 9:52 am

#1356554
RE: ISNull Not working

Not much information to go on.
Run the following code and post back comments.
SELECT
OrgCode AS UnitState ,
OrgCode As State,
COUNT(*) AS Rows_Per_OrgCode
--IsNull(Count(*), 0) As [SLRP Approved]
FROM tLookup_StateTable...
^{“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

July 21, 2011 at 4:53 am

#1356254
RE: Help with two SQL issues

Try this - IN and appropriate OUT on the same row:
;WITH SequencedData AS (
SELECT *,
PersonID = DENSE_RANK() OVER(ORDER BY strLastName, strFirstName),
rn = ROW_NUMBER() OVER (ORDER BY strLastName, strFirstName, datAdded)...
^{“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

July 21, 2011 at 3:09 am

#1356221
RE: Problem with the code

Krasavita (7/20/2011)
For some reason the code is not working properly, can someone assist me...Thank you
Ensure that each result expression evaluates to the same datatype:
DECLARE @col005 VARCHAR(15) = 'SOME$VA.LUE-'
SELECT CASE
WHEN @col005...
^{“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

July 20, 2011 at 9:02 am

#1355723

Viewing 15 posts - 6,811 through 6,825 (of 10,143 total)