1,236 reputation
414
bio website
location
age
visits member for 2 years, 5 months
seen Oct 13 at 19:19

May
23
comment QUERY - pivot multiple columns, variable number of rows
If your version IDs are different for every recipe, then you're either going to end up with two columns for every row in your table, or your version in the column name would not match the version ID.
May
17
comment Performance hit of CONVERT from nvarchar to money (SQL Server)
Mostly I was thinking of Aaron's arguments against it, but sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/… doesn't have a lot of detail, and the SO question he links to suffers the same problem of the accepted answer not having the interesting info
May
16
comment List of fallback values in SQL
Something like SELECT TOP (1) FolderName FROM dbo.FallbackFolders WHERE FolderExists = 1 ORDER BY Precedence?
May
16
comment Performance hit of CONVERT from nvarchar to money (SQL Server)
You probably don't want to use MONEY--go DECIMAL. See stackoverflow.com/questions/582797/…
May
14
comment How to find duration in a dataset
It needed a slight change in how the NULLs were filtered. It still doesn't match the desired output exactly, as it shows the first and last PollTime for a NULL set, rather than the first of the NULL set and the first of the following set. May do the trick, though, if a simple "+1" is appropriate for your application.
May
14
comment Loading image from file system into SQL query using openrowset
It's not going to work that way without using dynamic SQL. See stackoverflow.com/questions/6935598/….
Feb
12
comment Timeout while using table valued parameter
Re: MERGE -- I really want MERGE to work well/reliably. I've gotten comfortable with the syntax and find it convenient for my most common tasks--especially when I'm dumping the output to a log table. Most of the issues I've read about seem to be with features we don't make heavy use of (indexed views, filtered indexes, triggers), so I haven't abandoned it (for this environment at least)--though I realize it may bite me later.
Feb
12
comment Timeout while using table valued parameter
I started using the #temp with this pattern (unless the data set is fairly small) after starting to run into performance issues using large table variables. I'm operating under an assumption that a TVP is equivalent to a table variable inside of a proc--but even so, it may well be an overreaction to be using the #temp without specific indication to need it in this circumstance. There is no PK on the TVP (it isn't by definition unique--but I could move the row_number upstream to make it so).
Feb
12
comment Timeout while using table valued parameter
@Thomas -- That's what I was looking for! I'd found references to that attached to a connection string, but not to the SqlCommand, and I didn't make the connection. cmd.CommandTimeout = 0; let it complete successfully! Now to figure out if this was a good idea to begin with...
Feb
12
comment Timeout while using table valued parameter
@Martin -- Didn't change anything, but got me looking more at the proc itself. I was dumping the TVP into a temp table immediately, then doing the merge. Thinking an index might help, I added a clustered PK constraint, but also no change. Now I'm starting to think that dumping 1m rows into a temp table isn't saving any I/O over rewriting the table...maybe I'm coming at this all wrong
Sep
30
comment how to downgrade a statement for SSIS
Whoops! Corrected.
Jul
16
comment Run job once a minute, every day, except during a certain time
Or one schedule that runs every day from 11:30pm to 06:59pm and another that runs every day except Saturday from 07:00pm to 11:29pm
Jun
28
comment Error using an Execute SQL script to load a date variable in SSIS
Looking at your settings again, I see what you were doing--I missed the output direction setting. I can't say why that shouldn't work, other than that SSIS SQL task parameters are flaky as heck. Possibly this?
Jun
27
comment Is there any way to tell the number of matches found in a database value matched with LIKE?
are you searching for 'love'? If you're changing the search string in your testing, you need to change the 4 to match the length of the string.
Jun
27
comment Vendor Wants to Run MSDB job every 5 minutes for Business Application
You don't necessarily have to give them sysadmin to let them modify SQL agent jobs--check out msdn.microsoft.com/en-us/library/ms188283(v=sql.105).aspx
May
29
comment Efficient way to fetch records since a given criteria
As long as you have an appropriate index on ID, I don't think the top (n) ... where ID > @id construct is going to be your problem. It sounds like you need to tune the complex joins.
May
16
comment Fixed length text file insert into SQL table
Not having experience with FORMAT FILE, I've looked into it briefly, but I haven't seen an example that addresses files like the example, where the header has one set of columns, the data rows another, and the footers another. Can you suggest a source for info?
May
16
comment Fixed length text file insert into SQL table
In my very limited experience, SQL isn't as bad at string manipulation as everyone says--this is how I'd try it if SSIS vaporized itself, and if the performance wasn't acceptable, I'd try something else.
May
6
comment pros/cons of different ways to store whether a record is one of two options?
I can't vouch for best practice, but I'd go with option 3. You can make it a bit if you're sure you only need the two options, and changing it to an int later (if your prognosticator is faulty) would probably be significantly easier than fixing one of the other options. Probably not a bad idea to still build the corresponding AddressType table for a reference, even if you don't code to use it.
Oct
3
comment Heap size too large for data present
I rebuilt the table and it's now less than 14 MB. Y'all were leading me to it, but I think looking up DELETE WITH (TABLOCK) made it sink in why the pages weren't being deallocated. The simplest explanation seems to be that the table was once loaded with way too much data, and those pages have been allocated ever since. I notice now that avg_page_space_used_in_percent has jumped greatly, so that may help me find other offenders.