Jeff Moden

Forum Replies Created

Viewing 15 posts - 32,011 through 32,025 (of 59,099 total)

RE: Issue with cast and convert function in the update statement

shalini72011 (11/7/2011)
Hi,
I have a query with update statement
UPDATE #Rpt
SET Productfamily = PD.pt_sp_type_c
FROM #Rpt 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)

November 7, 2011 at 5:48 pm

#1404232
RE: Remote query never ends and cannot be killed

This all works kind of like a call to xp_CmdShell. In order to kill the process, you have to kill the spid that made the call (which you're already...
--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)

November 7, 2011 at 5:43 pm

#1404230
RE: How to remove extra space from OSQL output?

Since the -s parameter defaults to a space, you need to (IIRC) explicity say -s"" (dash s followed by two double quotes with no spaces in between).
--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)

November 7, 2011 at 5:35 pm

#1404229
RE: SELECT tagnames (not attributes) from xml code

If you only have two tags names, do you really need to get the actual tag name or will just knowing it's in there be enough? If the latter...
--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)

November 7, 2011 at 5:20 pm

#1404224
RE: How to Get Instance Name from T-SQL

ashwin4all1 (7/10/2009)
Hi
All,
You better To use the following command for Getting the instance name.
EXECUTE xp_regread @rootkey='HKEY_LOCAL_MACHINE',
...
--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)

November 7, 2011 at 5:17 pm

#1404221
RE: How to modify code to display '21:30:00 PM' as '09:30:00 PM'?

adonetok (11/7/2011)
The code below will display as '21:30:00 PM'
How to modify code to display as '09:30:00 PM'?
declare @date datetime
set @date = '2011-11-08 21:30:00.000'
select CONVERT(VARCHAR, @date, 108) + ' '...
--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)

November 7, 2011 at 5:15 pm

#1404220
RE: Tricky Calculation, Multiple Query?!?!

Scott Coleman (11/7/2011)
You could also use PIVOT.
Please see the following for why you might want to consider good ol' fashioned CROSS TABs instead.
http://www.sqlservercentral.com/articles/T-SQL/63681/
Of most importance is the performance chart near...
--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)

November 7, 2011 at 4:58 pm

#1404217
RE: Select the document name from a column contaning full path and file name

Steve Kinnaman (11/7/2011)
Yes That work great. A friend suggested to replace DATALENGTH with LEN, and that worked as well.
I agree with TOM. I recommend you continue to use DATALENGTH...
--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)

November 7, 2011 at 4:28 pm

#1404212
RE: Formating numbers

Sounds like you're back to trying to write a formatting function. You could save yourself a lot of time by just skipping that and spend more time on passing...
--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)

November 7, 2011 at 4:24 pm

#1404211
RE: converting to INT from varchar

Sean Lange (11/7/2011)
ryvlad (11/7/2011)
hi I found this function somewhere on web and it have been very helpful 🙂
just run it on your DB and you can always reference 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)

November 7, 2011 at 4:17 pm

#1404209
RE: Starting with "WITH"

The Dixie Flatline (11/7/2011)
I agree with Chris.
Because I inadvertantly started (this very entertaining and informative discussion) by making a "suckish" comment, I feel compelled to admit that I have...
--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)

November 7, 2011 at 4:14 pm

#1404208
RE: multiple rows into single row - PIVOT?

Because of the variable column names, you'll likely need a bit of dynamic SQL. The link below provides and example of how to do that. The data in...
--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)

November 7, 2011 at 8:16 am

#1403973
RE: Question about stored procs, xp_cmdshell, and .NET applications

kramaswamy (11/7/2011)
I don't think there is a proxy account set up. I was looking into that, but I'm not sure which login I need to use in the proxy for...
--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)

November 7, 2011 at 7:58 am

#1403964
RE: Question about stored procs, xp_cmdshell, and .NET applications

I didn't look back to see but do you have the xp_CmdShell proxy set up?
Also, is this a direct connection or are you "talking" through more than one connection?
--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)

November 7, 2011 at 7:35 am

#1403954
RE: Starting with "WITH"

SQL Kiwi (11/6/2011)
I don't agree that recursive CTEs are RBAR by the way: they're simply set-iterative, a bit like Hugo's 'lasagne' running total code.
I have to agree to disagree then......
--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)

November 6, 2011 at 9:40 pm

#1403774

Viewing 15 posts - 32,011 through 32,025 (of 59,099 total)