ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,866 through 8,880 (of 10,143 total)

RE: Strange Error !!!!!!!!!

When you EXEC @lstr, it's executing in a different scope and the variables created in the body of the batch are not available to that scope.
Consider using sp_executesql, which supports...
^{“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 29, 2009 at 5:15 am

#934535
RE: Help with combining 2 queries

It looks simple enough to get away with this, but check your performance:
INSERT INTO TableA
(ColA1,
ColA2,
ColA3)
SELECT ColB1,
SUM(ColB2) AS ColB2,
SUM(CASE WHEN ColB4 BETWEEN 0 AND 10 THEN ColB3 ELSE 0...
^{“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 29, 2009 at 4:30 am

#934508
RE: SP failed due to "ResultSet"

You would benefit from adopting table aliases. Really.
Before
INSERT INTO
dbo.tblClients(Client_Short_Name,Client_Long_Name,CountryCode,ClientID)
SELECT LEFT
(dbo.vwImportAllNewClientIDs.Client, 30),
dbo.vwImportAllNewClientIDs.Client,
dbo.vwImportAllNewClientIDs.CountryCode,
dbo.vwImportAllNewClientIDs.
ClientID
FROM dbo.vwImportAllNewClientIDs
After
INSERT INTO dbo.tblClients (Client_Short_Name, Client_Long_Name, CountryCode, ClientID)
SELECT LEFT(c.Client, 30), c.Client, c.CountryCode,...
^{“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 28, 2009 at 7:37 am

#933812
RE: SP failed due to "ResultSet"

A Little Help Please (1/28/2009)
Could this be the problem?
It is a problem, which of course you can deal with because you've now read about the LEFT function in BOL.
So...
^{“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 28, 2009 at 7:18 am

#933782
RE: SP failed due to "ResultSet"

A Little Help Please (1/28/2009)
When I run you select statment I just get one row returned.
I think I found the issue, can you tell me how i would truncate 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 28, 2009 at 6:58 am

#933769
RE: SP failed due to "ResultSet"

A Little Help Please (1/28/2009)
Thanks again for your time!
CheckDate imports only one row one column
27/01/09
Any more suggestions?
Yes - run this:SELECT convert(char(8),date) FROM DealbookV2.dbo.CheckDate
What happens when you run the stored...
^{“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 28, 2009 at 6:20 am

#933745
RE: SP failed due to "ResultSet"

Nobody has replied to this because it was difficult to read through all of the code. I've spent a few moments reformatting it, in particular putting in table aliases, 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 28, 2009 at 5:48 am

#933716
RE: SP failed due to "ResultSet"

It's a problem with a subquery in the stored procedure dbo.procImportAllFiles.
Best post the sproc.
^{“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 27, 2009 at 11:15 am

#933403
RE: Delete query help

David Fullerton (1/27/2009)
This works Thanks Cris
Thanks for the feedback David.
^{“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 27, 2009 at 10:33 am

#933366
RE: Are the posted questions getting worse?

Grant Fritchey (1/27/2009)
As to writing coherently, I have a tendency to be thinking more about what I want to say rather than precisely how to say it. I've gone back...
^{“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 27, 2009 at 10:10 am

#933333
RE: Are the posted questions getting worse?

GilaMonster (1/27/2009)
Bob Hovious (1/27/2009)
One more post towards 1000.... and close to the original topic here. How many people get annoyed by people who cut and paste data rather...
^{“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 27, 2009 at 10:03 am

#933330
RE: Are the posted questions getting worse?

Bob Hovious (1/27/2009)
One more post towards 1000.... and close to the original topic here. How many people get annoyed by people who cut and paste data rather than...
^{“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 27, 2009 at 9:48 am

#933313
RE: Reducing common data into fewer rows.

ssetzer (1/27/2009)
Thank you both for your assistance. It appears that Mark's solution works well (and better than the loop solution I had attempted). Chris - your solution worked...
^{“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 27, 2009 at 8:52 am

#933250
RE: Delete query help

Use the WHERE clause instead?
DELETE b
FROM dbo.ADIM_ASSOCIATE_CONTRACTOR_RawImport b
INNER JOIN dbo.ADIM_ASSOCIATE_CONTRACTOR a
ON a.[EMPLOYEE_NUMBER] = b.[EMPLOYEE_NUMBER]
AND a.[ASSOCIATE_CONTRACTOR_FLAG] = b.[ASSOCIATE_CONTRACTOR_FLAG]
AND a.[DOM_INTL_IN] = b.[DOM_INTL_IN]
AND a.[CFC_NETWORK_ID] = b.[CFC_NETWORK_ID]
AND a.[NAME_PREFIX] = b.[NAME_PREFIX]
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 27, 2009 at 8:26 am

#933227
RE: searching for ASCII ranges in a string

You could use a tally table:
DECLARE @Search_Surname VARCHAR(40)
SET @Search_Surname = 'MIxED CaSE SUrNAME'
SELECT @Search_Surname = STUFF(@Search_Surname, NUMBER, 1, '#')
FROM NUMBERS
WHERE NUMBER <= LEN(@Search_Surname)
AND ASCII(SUBSTRING(@Search_Surname, NUMBER, 1)) > 90
SELECT REPLACE(@Search_Surname,...
^{“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 27, 2009 at 5:44 am

#933112

Viewing 15 posts - 8,866 through 8,880 (of 10,143 total)