ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,766 through 3,780 (of 10,143 total)

RE: Return random records in a table-valued function?

pietlinden (1/8/2014)
Thanks for the ideas, everybody.
I got it to work, which is good, but I had to resort to <whisper>a dreaded cursor.</whisper> I know, it's a total cheat, but...
^{“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 9, 2014 at 3:14 am

#1678720
RE: OUTER APPLY

quindraco (1/8/2014)
Thank you! You've finally sussed it. I went back to my function and modified it to rtrim the select from the table with the mangled data type...
^{“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 8, 2014 at 9:27 am

#1678471
RE: OUTER APPLY

Is [address] the only filter/parameter you are intending to pass in?
^{“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 8, 2014 at 7:49 am

#1678422
RE: OUTER APPLY

If you are still wondering why the last query in your script returns NULL for the fullfilename (or whatever), it's because you've been inconsistent with your datatypes.
-- Columns BOOK 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 8, 2014 at 7:40 am

#1678419
RE: Select statement to show users in more than one company

Smash125 (1/8/2014)
Nice Chris.
Sometimes 😀
^{“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 8, 2014 at 7:05 am

#1678410
RE: OUTER APPLY

Your sample code generates errors:
Msg 208, Level 16, State 1, Procedure imageFileNameFromAddress, Line 5
Invalid object name 'dbo.imageFileFromBookPage'.
(1 row(s) affected)
(3 row(s) affected)
Msg 208, Level 16, State 1, Line 3
Invalid object name...
^{“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 8, 2014 at 7:04 am

#1678409
RE: Arthimatic overflowerror converting expression to data type datetime

The whole of your output (everything in the SELECT list) is unrelated to anything else in your query. It looks like a bunch of columns but the syntax is incorrect....
^{“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 8, 2014 at 6:48 am

#1678405
RE: Select statement to show users in more than one company

Here's a proposal which reads the source table only once (you didn't see that table spool)
SELECT
[User ID], [Group ID], [Company]
FROM (
SELECT
[User ID], [Group ID] = MAX([Group ID]), [Company],...
^{“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 8, 2014 at 6:12 am

#1678394
RE: please help me

elham_azizi_62 (1/8/2014)
Two tables main1,mytable are common in this fields:mytable.savabegh with main1.name,mytable.linknumber with main1.linknumber
ChrisM@Work (1/8/2014)
... including INSERTs to populate it with some data.
^{“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 8, 2014 at 5:00 am

#1678378
RE: There is insufficient system memory in resource pool 'default' to run this query

krishnamohan6669 (1/7/2014)
Found that more MAX I/O Waittime is recorded with spotlight,so trying to increase the virtual file size. should wait and see wether this occurs again.
Jeff is proposing...
^{“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 8, 2014 at 3:33 am

#1678362
RE: please help me

elham_azizi_62 (1/8/2014)
my favorit out put is:
rownumber main2.linknumber main2.savabegh main1.linknumber
1 29027 ...
^{“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 8, 2014 at 3:00 am

#1678357
RE: Arthimatic overflowerror converting expression to data type datetime

tamer.h (1/8/2014)
Thank you ChrisM@Work
it works perfectly.
Good, thanks for the feedback.
I'm now trying to copy the result of query to another table in another DB useing INSERT INTO but 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 8, 2014 at 2:42 am

#1678355
RE: please help me

elham_azizi_62 (1/8/2014)
... I want to apply another column that exist in another table with name main1,in main1 'name' filed equall with 'savabegh' filed in main2:
main1:linknumber(int),name(varchar)
main2:linknumber(int),savabegh(varchar)
I want to have...
^{“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 8, 2014 at 2:13 am

#1678348
RE: please help me

elham_azizi_62 (1/8/2014)
thanks for your reply guys.
I create this sp:
ALTER procedure [dbo].[icanSP_Convert_GetEntityDependencies]...
As Gail has already pointed out, you're unnecessarily overcomplicating your code. Your stored procedure is the equivalent to this:
ALTER procedure...
^{“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 8, 2014 at 2:08 am

#1678345
RE: SubProcedure (Subroutines) within Stored Procedure

skb 44459 (1/7/2014)
Do you have an example ? I don't necessarily want different Stored Procedures.
I need multiple SubRoutines within One Stored Procedure, so that they can share the Variables 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 7, 2014 at 8:17 am

#1678097

Viewing 15 posts - 3,766 through 3,780 (of 10,143 total)