for reference though, the format is not too difficult: it's INTO #Tablename just before the first from

SELECT

ColumnList

INTO #TMP

FROM ATable

JOIN OtherTables ON ...

UNION

SELECT columnList

FromBTable

Sossoliso (3/14/2011)

---

I once Supported an Finance/Accounts System which used Triggers to alert agents when a sale had been made. One day the trigger to could send out any mails... The...

yeah, i think that's the issue; the value passed, when it comes in as a string, is not guaranteed to be in the format you are expecting:

to_date(:StartDate,'MM/DD/YYYY hh24:mi:ss')

if StartDate...

I sat thru a Webinar on SQL server ; the presenter was raving about how Twitter was the best thing to learn about SQL, so easy to ask a question,...

if you use parameters instead of constructing the strings, that issue would go away, but you can doe the fix with a little bit of REPLACE magic, i think.

you need...

spin (3/14/2011)

---

hi,

just got my head around all of the responses.

so...It turns out i was running trigger to email on our test system which doesn't have the correct permissions to truncate...

i believe if the login CREATED a view, by default, it is it's owner, and thus can alter or drop the objects it creates.

if you want the login to be...

damn i knew this and didn't remember it; I'll blame it on caffiene deficiency.

you'll want to stop and start the instance in single user mode

sqlservr.exe -m -s SQLEXPRESS

http://msdn.microsoft.com/en-us/library/ms180965.aspx

I've recently been spending a lot of time studying and scripting repsonses to Brent Ozars SQL Blitz! script.

It identifies a great set of things to look for, and has been...

Edit: reread your request: realized you wanted an entire instance, and not a single database.

my advice then is a cursor which runs the alter database command for each database.

EXEC sp_msforeachdb...

just confirming: looks like the disable of a trigger does not count as a DDL event, so it is not tracked int eh default trace; i created both a...

i think you need to just change the order by then;

order by case when isnumeric([first char], then the rest of what i suggested.

besides what I posted, what have you...

which article of Jeffs are you referring to?

how about using the PARSENAME function to chop up the IP address?

--using Stefan's setup:

/*--Results

(No column name)(No column name)

1172.24

322.25

3223.25

*/

SELECT Count(IPAddress),parsename(IPAddress,4) + '.' + parsename(IPAddress,3)

FROM #tempIPTable

Group By parsename(IPAddress,4) + '.' + parsename(IPAddress,3)

there may be a more efficient stripNonNumeric function, but this works:

select *

from Numbers

order by CONVERT(float, dbo.StripNonNumeric(code)),

code

/*--results

1

1a

7a

9ce

12

45

78

x78

100

a458

*/

and the function i used:

CREATE FUNCTION StripNonNumeric(@OriginalText VARCHAR(8000))

RETURNS VARCHAR(8000)

BEGIN

DECLARE...