ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,346 through 9,360 (of 10,143 total)

RE: Cannot get value from SELECT statement

Hi Johann
Looking at this from a slightly different angle...which variables do you have populated before you run this bit of code (nicked from Gail's post, thanks Gail)...
SELECT @BuildID = BuildID
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

November 28, 2008 at 4:38 am

#904697
RE: SP Error - String or binary data would be truncated

Hi
You could truncate using LEFT() as follows:
[font="Courier New"]DROP TABLE #tblClients
CREATE TABLE #tblClients (
Client_short_Name NVARCHAR(30) NOT NULL,
Client_long_name NVARCHAR(100) NULL,
CountryCode VARCHAR(5) NULL,
ClientID NVARCHAR(10) NOT NULL)
DECLARE @Client VARCHAR(255)
SET @Client = 'This is...
^{“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

November 28, 2008 at 4:15 am

#904673
RE: SP Error - String or binary data would be truncated

pri.amin (11/28/2008)
Ok cant seem to get around this,
the error is:
Msg 8152, Level 16, State 4, Procedure procImportAllFiles, Line 33
String or binary data would be truncated.
The statement has been terminated.
dbo.tblClients
Client_short_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

November 28, 2008 at 3:12 am

#904646
RE: Functio to get Amount in words.

rbarryyoung (11/27/2008)
Chris:
I just notice that you are putting "and" after your Hundreds, like this:
nine hundred and seventy seven thousand, six hundred and ninety two dollar(s)
Technically, this is...
^{“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

November 28, 2008 at 2:56 am

#904641
RE: Functio to get Amount in words.

rbarryyoung (11/27/2008)
Well I did test them on my laptop. Chris's ran in 13 & 15 seconds, while Jeff's ran in 5.1 and 5.2 seconds.
Testing in my reporting environment yields...
^{“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

November 28, 2008 at 2:52 am

#904638
RE: Functio to get Amount in words.

Jeff Moden (11/27/2008)
Chris Morris (11/27/2008)
Here's a 2k/2k5-friendly version with a permanent lookup table. It translates 30,000 numbers from a standard tally table, in both SQL Server versions, in about 4...
^{“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

November 27, 2008 at 12:43 pm

#904468
RE: Functio to get Amount in words.

Here's a 2k/2k5-friendly version with a permanent lookup table. It translates 30,000 numbers from a standard tally table, in both SQL Server versions, in about 4 seconds.
[font="Courier New"]IF EXISTS...
^{“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

November 27, 2008 at 10:40 am

#904429
RE: Determining the Nearest Record to a Given Location

Hello
A quick Google confirms that this requirement is fairly common nowadays. The following thread describes a couple of sprocs which could be used to help, with a minimum of fuss.
http://www.tek-tips.com/viewthread.cfm?qid=1499975&page=11
This...
^{“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

November 27, 2008 at 8:02 am

#904347
RE: Case in a where Clause

Hi Thatok
If you are intending to eliminate from your result using the condition
If the fifth character = 0 then the sixth character should not be 0.
then shouldn't this do 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

November 27, 2008 at 2:36 am

#904192
RE: Functio to get Amount in words.

Jeff Moden (11/26/2008)
Chris Morris (11/26/2008)
Chris, the link in your signature is broken. You'r missing the right hand bracket on the first instance of the URL IFCode ShortCut.
Thanks Jeff, it's...
^{“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

November 27, 2008 at 2:28 am

#904190
RE: Functio to get Amount in words.

rbarryyoung (11/26/2008)
My guess would be that it is the SUBSTRING()'s in your ON clauses.
You're spot on. It works fine in 2k, I should have tested it in 2k5 :blush:. 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

November 27, 2008 at 12:51 am

#904164
RE: Functio to get Amount in words.

Matt Miller (11/26/2008)
Chris Morris (11/26/2008)
UNION ALL shouldn't be necessary because none of the rows are duped.
I'll test it for speed tomorrow using a permanent table. A whole minute for...
^{“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

November 26, 2008 at 2:35 pm

#904056
RE: Functio to get Amount in words.

TheSQLGuru (11/26/2008)
That looks pretty slick! A minor nitpick would be to use UNION ALL for populating the table variable. Also, if this were to be used regularly I...
^{“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

November 26, 2008 at 2:30 pm

#904051
RE: IDENTITY_INSERT ON problem

Hi Nishant
It's exactly as your error message states, you can't use
INSERT INTO NewDataBase.Extensions.FieldSelectionGroups
SELECT *
FROM OldDataBase.Extensions.FieldSelectionGroups
you have to use
INSERT INTO NewDataBase.Extensions.FieldSelectionGroups (column1, column2 etc)
SELECT column1, column2 etc
FROM OldDataBase.Extensions.FieldSelectionGroups
Cheers
ChrisM
^{“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

November 26, 2008 at 2:20 pm

#904040
RE: Functio to get Amount in words.

Here's another way, similar. Quite quick.
[font="Courier New"]ALTER FUNCTION dbo.ToWords(@TheNumber DECIMAL (15,2), @CurrencyName VARCHAR(20), @JoinWord VARCHAR(20), @CentsName VARCHAR(20))
RETURNS VARCHAR(200)
-- USAGE: SELECT dbo.ToWords(1.01, 'dollar(s)', 'and', 'cent(s)')
-- MAX. VALUE: 999999999999.99 (twelve nines...
^{“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

November 26, 2008 at 10:00 am

#903906

Viewing 15 posts - 9,346 through 9,360 (of 10,143 total)