Jeff Moden

Forum Replies Created

Viewing 15 posts - 30,406 through 30,420 (of 59,101 total)

RE: Case Statements nesting more than 10 levels

Create the proc on the linked server and then execute it from there.
It would also be a much better thing if someone were to properly normalize that table
--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)

April 9, 2012 at 6:29 pm

#1471016
RE: join -- The amount of the most recent child record

Or, you can just write a little bit of code to do the same thing. Please see the following article.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--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)

April 9, 2012 at 6:24 pm

#1471015
RE: Running xp_cmdshell under non system admin

Here's one of many solutions available as of SQL Server 2005 where the user only has EXECUTE privs on a stored procedure that uses xp_CmdShell and the user can NOT...
--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)

April 9, 2012 at 6:16 pm

#1471014
RE: Running xp_cmdshell under non system admin

balbirsinghsodhi (4/9/2012)
Thanks Jeff,
How about doing like this ?
CREATE PROCEDURE dbo.usp_ExecCmdShellProcess
AS
BEGIN
DECLARE @job NVARCHAR(100) ;
...
--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)

April 9, 2012 at 6:07 pm

#1471011
RE: Recursive CTE for Item supersession question

You might want to take a good look at the following articles before you use a recursive CTE.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/T-SQL/74118/
--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)

April 9, 2012 at 4:52 pm

#1470990
RE: Running xp_cmdshell under non system admin

I would NEVER give a login for a human or application the privs necessary to execute xp_CmdShell directly. In fact, I'd never give a human or application more than...
--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)

April 9, 2012 at 4:48 pm

#1470986
RE: How reliable execuion plan estimates are!!

GilaMonster (4/9/2012)
Jeff Moden (4/9/2012)
The absolute best way to measure these types of things is with SQL Server Profiler. The absolute best way to use SQL Server Profiler is to...
--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)

April 9, 2012 at 4:34 pm

#1470975
RE: Try to enclose ' within SQL Output

True enough. I'm really hoping that a date/time wouldn't exceed 128 characters. 🙂
--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)

April 9, 2012 at 4:31 pm

#1470973
RE: Modified DateTimeStamp - No Default on record creation

L' Eomot Inversé (4/9/2012)
Jeff Moden (4/8/2012)
I prefer they both be NOT NULL and, like Gail identified, will make both the same on INSERT. I also use a trigger to...
--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)

April 9, 2012 at 4:28 pm

#1470972
RE: How to you use dbmail with BCP

opc.three (4/9/2012)
Jeff Moden (4/8/2012)
Yes, we could use SSIS and all different manner of tools but, being the hardcore data-troll that I am, I'll use some hardcore T-SQL and a little...
--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)

April 9, 2012 at 4:11 pm

#1470967
RE: How to you use dbmail with BCP

opc.three (4/9/2012)
I ran the demo code and it works as advertised (i.e. emails a file with an xls extension) but when I open the attachment in Excel 2010 I receive...
--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)

April 9, 2012 at 4:08 pm

#1470966
RE: Try to enclose ' within SQL Output

As an alternative...
+ ' SET insert_date = ' + QUOTENAME(@InsertDate,'''')
--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)

April 9, 2012 at 7:32 am

#1470650
RE: How to get multiple of Rows of data into one single row in the CSV format

For a bit more detail on the subject, please see the following article...
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
--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)

April 9, 2012 at 7:31 am

#1470647
RE: Question on Pitvoting data in SQL

Joining a Tally Table to VARCHAR(MAX) (during delimited splitting, for example), even if there are less than 8000 characters, will cause the join to run twice as slow as an...
--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)

April 9, 2012 at 7:25 am

#1470643
RE: Get Some Cross Training Today

Although I believe that some cross-training is certainly necessary on the part of DBAs and hardcore SQL Developers, what I'd really like to see is more of those folks learning...
--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)

April 9, 2012 at 7:19 am

#1470635

Viewing 15 posts - 30,406 through 30,420 (of 59,101 total)