1,149 reputation
213
bio website
location
age
visits member for 1 year, 11 months
seen Apr 11 at 13:49

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
Jun
12
comment Database associative table
I think this is entirely subjective to the business process being modeled. I don't see any point in adding a record to a relationship table to represent the absence of a relationship.
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.
Oct
3
comment Heap size too large for data present
The package for this table uses a DELETE when most of them use TRUNCATE, but it's not the only one. Other than that, it's just an ODBC destination in a very simple data flow.
Oct
3
comment Heap size too large for data present
Interesting info, but in this case, this query returns the expected layout
Oct
2
comment Heap size too large for data present
For the same reason the columns are all 50 or 255 wide--because it defaulted that way and no one knew better. I'm out to fix it, but I want to make sure I understand it as much as possible. We have hundreds of similar tables; this is the only one I've noticed this bloat on, but I'd be surprised if it's the only one.