Jeff Moden

Forum Replies Created

Viewing 15 posts - 37,636 through 37,650 (of 59,098 total)

RE: OPENROWSET mysteriously stopped working

ZenDada (7/16/2010)
This worked last week:
select top 1 a.* from
OPENROWSET('SQLOLEDB','myServer';'myLogin';'myPassword',
'SELECT * FROM myDB..myTable') AS a
Now I get errors:
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 8:45 pm

#1194451
RE: Insert data into one table column with multiple values of another table column

This should do it... (untested)
INSERT INTO TABLE1
(Name, Age)
SELECT 'JIM' AS Name,
Age2 ...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 8:39 pm

#1194450
RE: Something like a running total...

marty.seed (11/10/2009)
I figured out how to do it for the weekly total rows...
=iif(InScope("Date2"), sum(Fields!Sales.Value), last(Fields!Sales.Value))
Now the conundrum lies in trying to total the total columns correctly
Hi Marty,
Did you ever get...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 5:33 pm

#1194442
RE: Generating SubTotals using GROUPING

Nice job, Sunil. I especially liked the fact that you compared what some folks go through with multiple queries (in one form or another) and the simplicity of WITH...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 5:32 pm

#1194441
RE: Generating SubTotals using GROUPING

Eric L Hackett (7/13/2010)
How would one go about grouping by dates, i.e. month, year?
So a query from 12/30/2009 thru 1/2/2010 would have something similar to this.
Meter Day ...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 5:28 pm

#1194440
RE: Expire rather than Delete records in a table

How about WHERE EndDate <= GETDATE(). That way you can "future date" expiration dates and never have to worry about them.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 4:58 pm

#1194434
RE: COPY SQL QUERY

Ram:) (7/16/2010)
I think still I didn't give much information about my problem..
Here is the clear information..
I have around 50 database with same schema..
I have one additional database to store the...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 4:56 pm

#1194432
RE: COPY SQL QUERY

seth delconte (7/16/2010)
Jeff Moden (7/16/2010)
seth delconte (7/16/2010)
I think there's a way to do that other than running Profiler to record the queries...
Cool... what is it?
Typo...I meant I DON'T think there's...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 4:52 pm

#1194431
RE: Performance on Views that span databases on multiple servers

Ummmm... cross server joins are quite a bit different than cross database joins. I try to avoid cross server joins whenever possible. In fact, I've gone out of...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 4:44 pm

#1194427
RE: ssis problem in T-Sql

riajain9 (7/14/2010)
Hi everyone,
I have a strange requirement in SSIS, that I wanted to share with you all, and need some idea on how can I implement it...
I have to associate...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 4:02 pm

#1194418
RE: COPY SQL QUERY

seth delconte (7/16/2010)
I think there's a way to do that other than running Profiler to record the queries...
Cool... what is it?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 3:37 pm

#1194402
RE: Report with 52 Weeks (A year) of data as columns

Pre-aggregation is essential to such reports. Please see the following articles...
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
Yes, you can use a Matrix to do similar... if there's not too much data to clog the pipe...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 3:31 pm

#1194398
RE: Expire rather than Delete records in a table

Here's a pretty good link that explains SCD's quite nicely....
http://en.wikipedia.org/wiki/Slowly_changing_dimension
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 3:19 pm

#1194390
RE: Expire rather than Delete records in a table

When I need to use the "future expire" method, I use 9999-12-31 for the future date. As a side bar, I prefer the "future expire" method when start and...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 3:14 pm

#1194387
RE: SUBSTRING coding problem

Kingston Dhasian (7/16/2010)
Try this
DECLARE @strVariable VARCHAR(100)
SET @strVariable = 'J:\SQL 2008 Database Backups\High School Baseball\HighSchoolBaseball_Full.bak'
SELECTSUBSTRING( @strVariable, 1, LEN( @strVariable ) - CHARINDEX( '\', REVERSE( @strVariable ) ) )
Here i have used...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

July 16, 2010 at 3:00 pm

#1194377

Viewing 15 posts - 37,636 through 37,650 (of 59,098 total)