ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,436 through 9,450 (of 10,143 total)

RE: using varchar variable to query a table

pramod.chauhan (11/5/2008)
thanks for qiick reply
@ab will have the all employeeid selected in reporting service interface.
and then i want to pass the @ab to stored procedure to give employee details.
there...
^{“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 5, 2008 at 8:09 am

#894270
RE: using varchar variable to query a table

pramod.chauhan (11/5/2008)
firstly set @ab='''483503''' is creating a variable who value is '483503'
Why do you want to store that extra pair of single quotes as part of the variable?
The value...
^{“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 5, 2008 at 7:10 am

#894222
RE: update

Eswar, you code is close:
UPDATE t SET disabled = 1
FROM tasks AS t, milestones AS m
WHERE t.milestoneid = m.milestoneid
AND m.projectid=15
Using proper join syntax this is
UPDATE t SET disabled =...
^{“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

October 30, 2008 at 2:06 am

#891633
RE: Not Equal

Hi Sudhakara
The problem is this: you think your statement should be operating on a row-by-row basis.
Look at a row; if name <> 'name1' and age <> 25 then keep 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

October 28, 2008 at 2:51 am

#890448
RE: Start and End Date Groupings

Hi Debbie
With a clustered index on your table...
DROP TABLE dbo.ECAFALL
CREATE TABLE dbo.ECAFALL(
[Person] [int] NULL,
[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

October 21, 2008 at 3:42 am

#887494
RE: Nested Select?

This should do it...
SELECT TOP 1 d.[ID], d.INFO
FROM (
SELECT TOP 2 s.[ID], p.INFO, p.[DATE]
FROM PRODUCTS p
INNER JOIN SALES s ON s.[ID] = p.[ID]
WHERE p.[DESC] LIKE 'FLAG SET TO%'
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

October 20, 2008 at 9:25 am

#887188
RE: Linking tables

Hi Buzz
The short answer is Yes, they can: it's called Server Linking in BOL. It can be fiddly and may not work depending upon the csv format of the files....
^{“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

October 20, 2008 at 7:39 am

#887107
RE: Nested Select?

This should do the trick:
SELECT TOP 1 * FROM (
SELECT TOP 2 *
FROM PRODUCTS
WHERE ID = 'A1425' AND DESC LIKE 'FLAG SET TO%'
ORDER BY DATE DESC
) d...
^{“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

October 20, 2008 at 7:30 am

#887095
RE: How to add "dot" after every three digits in a number in sql 2005

EdVassie (10/20/2008)
My advice is to do this type of formatting in your application, not in SQL Server.
Although it can be done in SQL Server, application code does this task far...
^{“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

October 20, 2008 at 6:33 am

#887068
RE: Dynamic Query

Try this, then.
DROP TABLE #employee
DROP TABLE #employee1
Create Table #employee
(
Empid int,
Employee varchar(20)
)
Create Table #employee1
(
Empid int,
Employee varchar(20)
)
Insert into #Employee values('1','Sirish')
Insert into #Employee values('2','Sateesh')
Declare @Strquery varchar(100)
SET @StrQuery='Select Empid,Employee from #Employee'
EXEC(@StrQuery)
SET @StrQuery='INSERT INTO #employee1...
^{“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

October 17, 2008 at 9:21 am

#886451
RE: Dynamic Query

Yes it is, provided that the temp table exists from the calling session (of the dynamic statement). You can also use
EXEC('INSERT INTO #Temp (columnlist) SELECT...')
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

October 17, 2008 at 9:07 am

#886431
RE: sp_execute returns error

Cool, many thanks for the feedback.
^{“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

October 17, 2008 at 8:53 am

#886418
RE: Extreme long UPDATE action, advice needed.

It's a long shot, but is there any difference if you use the alias in the UPDATE? I know it can, under some circumstances, make a substantial difference to performance.
update...
^{“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

October 17, 2008 at 8:45 am

#886408
RE: sp_execute returns error

Try this...
[font="Courier New"]DECLARE @passval DATETIME,   @sql NVARCHAR(1000)
SET @passval = (SELECT DATEADD(DAY, -1, GETDATE()))
SELECT @sql = N'declare cursearch cursor for select * from openquery(dbssqts1,
        ''Select convert(varchar(100), name) as accname,
        convert(datetime, loginproperty(name, ''''PasswordLastSetTime'''')) as...
^{“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

October 17, 2008 at 8:31 am

#886397
RE: Now It is your Google... Check it out!

Heh heh! It doesn't have a profanity checker. At least two of my colleagues have something along the lines of "FindTheF****r"!
^{“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

October 17, 2008 at 8:08 am

#886376

Viewing 15 posts - 9,436 through 9,450 (of 10,143 total)