7hertz

query analyzer, keyboard shortcuts

· · (1) · ·

similar to visual studio, a few gems here.

norton & sql server

· · (0) · ·

norton antivirus has been preventing sql server 2000 from starting up -- causing this event log :

Error: 17883, Severity: 1, State: 0
The Scheduler 0 appears to be hung. SPID 0, ECID 0, UMS Context 0x02EFA3C8

disabiling norton (or excluding all relevant sql server directories from nav), will set things straight. many DBA's will tell you norton is a problem. in rare cases the only solution is to remove nav entirely. yes.

update with var assignment

· · (0) · ·

here's an example of how you can perform a variable assignment in an sql update statement. each row processed by the update *also* performs the assignment.

the key is to use this syntax...

SET @x = MyIntegerColumn = @x + 1

this example just increments a value - so each row updated gets a higher value (most cases you would use an index for such a task).

pivot row into a comma delimited list

· · (1) · ·

ok, whoa, just picked this one up. usually variable assignment from a select statement is thought of as returning one final *single* value (whatever that happens to be).

if instead you perform some logical operations a variable in the meantime you can also continuously calculate and update a variable based on each subsequent row returned.

DTS & sql agent ownership problem

· · (0) · ·

i'm having trouble with a sql job calling a dts package. the dts package runs fine, but does not run when executed from a job (my domain account is set as owner of the job, and i'm a system admin as well - but the sql server agent is a local account not a domain account)

ordinarily that would be fine - i'm the owner of the job so when the agent executes the job it should use the security context of my account. however i believe there is an additional twist to that, related to DTS packages. here's the catch . . .

DTS returns success even when step fails

· · (1) · ·

I have a DTS package that calls another DTS package. For the first time, I noticed behavior where the subordinate package fails, but the calling package did not error and stop processing (all using the 'continue on success' workflow properties).

unlike other packages that do this properly without issue, i went ahead checked the "fail package on first error" property under error handling on the subordinate package - then the calling package stopped on that step as desired.

my theory is that the type of error causing the subordinate package to stop was not reported as an error in the same manner to the calling package - that's a feature or bug i'm not sure ?!

sql server error 7405

· · (0) · ·

BUG: Can't Use SQL Enterprise Manager to Create Stored Procedures Containing Linked Server Objects

This article was previously published under MSKB: Q296769

deleting duplicates (more)

· · (0) · ·

here's a version uses a self join, on a derivative table instead of a view... this example uses "mytableid" to distinguish records (by adding a index there) - but you might have used "mydatefield" in a similar manner (assuming the date field differentiates the records, and you adjust the comparisons around accordingly).

read more...
 

sql job accounts and sqlagent

· · (0) · ·

the user (or security context) a sql job runs under varies based on how the job is launched. meaning you might have tested everything, and it's all working fine - only to find your scheduled task fails (with something along the lines of 'access denied').

maintenance plan, error 5070

· · (0) · ·

i got an error while executing a database maintenance plan. looking into it closer, revealed that the plan was performing an integrity check with the repair option set.

binary files differ, vss file types

· · (0) · ·

visual source safe doesn't always recognize text file types properly. when checking-in files using 'auto' type -- it treats them as binary.

enterprise mananger taskpad view error

· · (0) · ·

i get the following error sometimes when i click on a database in Enterprise Manager in Task Pad View:

Internet Explorer Script Error
Line: 307
Char: 2
Error: Unspecified Error
Code: 0
URL: res://C:\Program%20Files\Microsoft%20SQL%20Server\80
\Tools\Binn\Resources\1033\sqlmmc.rll/Tabs.html
Do you want to continue running scripts?
Yes|No

ordering views

· · (0) · ·

'Order by', in SQL Views :

SQL Server usually doesn't allow an ORDER BY clause in views. However, you can use this workaround: Just add a TOP 100 PERCENT clause to the front of the SELECT list.

maximum page size in sql server

· · (0) · ·

What is the Maximum Page Size in SQL Server 2000?

tree hierarchies in sql server

· · (0) · ·

different ways to handle a tree hierarchies in sql server...

ms access left outer join

· · (0) · ·

i'm not a big fan, but sometimes ms access makes for a decent tool. however, syntax for various sql operations are different. access can still perform a left outer join, here's how...

case insensitive searches

· · (1) · ·

A default SQL Server installation is case insensitive, which means that SQL Server will not differentiate between upper and lower case characters/letters.

Here are some ways to perform case sensitive searches using different methods.

computed columns and indexes

· · (0) · ·

computed columns have some benefits, along with some restrictions (like being deterministic). what's interesting is that SQL doesn't necessarily store the computed column data...

datetime cast

· · (0) · ·

more ways to get/deal with datetime values...

set v. select

· · (2) · ·

What is the difference between SET and SELECT when assigning values to variables, in T-SQL?

sub queries and result sets

· · (0) · ·

while writing a query that seemed straight forward to me, i got the error : "Cannot perform an aggregate function on an expression containing an aggregate or a subquery."

the issue perhaps wasn't the logic of what i was trying to accomplish, but just the order and placement of operations.

the solution? you can always collect your data into a temp table and then work with that. but you don't have to...

read more...
 

stored procedure parameters

· · (0) · ·

i actualy forgot you can do this (got used to stuffing paramters in order!)...

you can explicitly name your parameters inline (in the TSQL exec string).

date strings

· · (0) · ·

i've explored a few more date to string format conversions. nothing really new here, but here are some observations.

date comparison / first of month

· · (0) · ·

there are a few ways to compare days, or return the first day of the month in sql server.

rename or (force) drop database

· · (0) · ·

here's a way to rename, or force a database drop. i'm not sure if you need or want the rollback transaction lines. but here you go...

also on renaming, you could dettach the database, move the data / log files, then reattach.

it's your birthday...

· · (0) · ·

here's a way to calculate someone's age on a given date. also, you can also sort by birthday (month/day) excluding the year.

the query just performs a datediff, BUT adjusts by 0/1 given the time of year -- before or after the person's birthday.

i.e. you just need to adjust by "-1" if the date you're evaluating is prior to the birthday in that year.

can't log into database?

· · (0) · ·

This throws me off now and then. I quickly create a database in EnterpriseManager then run a script to create objects using Query Analyzer. Since I can to log in as 'sa', everything seems fine -- but I can't log in using a connection string even though 'sa' *should* be the dbo for the database. so what's going on? there's a catch.

check for daylight saving time

· · (3) · ·

This procedure uses the sDayOfMonth procedure, to get the calendar day, then checks if that day is subject to daylight saving time.

This procedure is only accurate to the "day" and not the *hours* prior to the change at 2am (which was just fine for my application).

determine the calendar day of month

· · (0) · ·

Here's a stored procedure that returns the calendar "day of month number", given the year, month, and occurance (1st,2nd,...) and the day of week in question (sunday,monday,...).

I used this for another procedure that determines if daylight saving time is in effect on a given day. see the notes regarding validating input for other use cases, but it should be quite useful.

adding a composite column constraint

· · (0) · ·

unique constraints are actually implemented as indexes really, so it might not be obvious where to go to do this in enterprise manager.

to add a unique composite column constraint in sql server, you can bring up the design table dialog (right click on the table) in enterprise manager, then click on indexes and keys.

cross database join

· · (0) · ·

Make sure to install service pack 3 on SQL Server 2000 -- and check "enable cross ownership chaining".

otherwise you'll have to grant permissions on individual tables (beyond what you might expect to get that to work), as a workaround