The Heap™ – Consultancy ©® - 2014-09-22 (page 1 of 2)

5:40 AM

@MeNoTalk As far as I know, nvarchar is UTF-16 (LE).

Actually it says in nchar and nvarchar (Transact-SQL) that it uses the UNICODE UCS-2 character set (not UTF-16 technically).

MeNoTalk

6:17 AM

@AndriyM thank!

so I guess that's bad news... that's 16 bit minimum for each character :/

8:59 AM

@MeNoTalk I think it's just 16. minimum and maximum. 2 bytes for each character.

@ShawnMelton Yeah. But Microsoft's policy to use Azure in naming 2 (or 3?) similar but different things doesn't help anyone. As obvious by the comments needed to clear what they were referring to.

11:31 AM

what is the exact procedure for creating tables and inserting values in SQL.Is it first create table or create database

and then the insert in values

@user285oo6 Try it yourself! Nothing better than trying things and see what works and what not.

i tried and got the table using the create table and then the insert values

but when i insert say 4 records in the table then i get an error message ORA0071

so i think the procedure may vary

*ORA-00942: table or view does not exist

@yper what's a good place to start for sql developer i am completely new to it

@user285oo6 Well, the message says it. You have to create the table first. You can't insert into something that doesn't exist.

ok i typed create table employee(ename varchar2(10) ,city varchar2(10)); and then later

insert into employee values ('ANi','Vancouver');
insert into employee values ('SHANE','Dallas');
insert into employee values ('JAY','Mexico');

@yper after giving the above i enter desc employee;

and it gives the above error

@user285oo6 What's "desc employee" and how are you entering it?

11:47 AM

desc employee; is describe the table

@AndriyM it is entered after create table,insert values and then desc employee;

@user285oo6 Can you provide the exact insert statement?

@AndriyM insert into employee values ('ANi','Vancouver');
insert into employee values ('SHANE','Dallas');
insert into employee values ('JAY','Mexico');

after the above statements

desc employee;

oh, desc employee was the command, sorry

It says here that ORA-00942 is also returned when you don't have enough privileges, although I'm not sure why you wouldn't have desc privileges on an object you created yourself.

@Andr now i get a closed connection error

12:27 PM

morning everyone

MeNoTalk

@ypercube no, utf-16 characters can be either 16 or 32 bit long...

morning, @Lamak

@MeNoTalk In SQL Server NVARCHAR uses 2 bytes per character

plus a 2 byte overhead per column per row to store the length

morning all

@JNK was it 2 bytes for the length?

@bluefeet good morning

@JNK and an extra one if its nullable

@Lamak yeah 2 bytes for length

12:34 PM

@MeNoTalk nvarchar is not UTF-16, it's UCS-2, according to the manual. UCS-2 is exactly 16 bits per character.

that'll give you up to 64k values

@JNK My memory is getting worse

It needs two bytes b/c 2 byte will only give you up to 255 and we need to track up to 4k length settings

I have a OCD thing where I won't use a varchar under (5) normally

since you could make it a char and save some bytes

doesn't matter so much know but when I was dealing with the healthcare billing data with 1b row tables it added up

Mikael Eriksson

@JNK, @Lamak, @AndriyM, @MeNoTalk Just to confuse matters a bit. From SQL Server 2012 you can use UTF-16 collations by selecting a collation named something "_sc" and those can use up to 4 bytes. msdn.microsoft.com/en-us/library/…

@MikaelEriksson oh wow didn't know that

MeNoTalk

12:42 PM

thanks guys.. didn't know about UCS-2 thing...

I am no programmer after all :)

@MikaelEriksson Yes, it does help to confuse things a little more, thanks :)

4

Q: Alternatives for a LEFT OUTER JOIN query

Why on earth would you use a product that has a bug in outer joins?

0

Problem I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER JOINS. SQL Query SELECT * FROM tSysNls WHERE nlsGuid IN ( SELECT nlsGuid = CASE WHEN de_...

1:26 PM

@JNK Why would you have a table where all the columns are nvarchar, there is no pk and one that looks like a pk is nlsGUID nvarchar(207). 414 bytes (or 828 if they are fans of utf-16 :)

@ypercube lots of questions in that question.

1:51 PM

Don't understand Remus' attitude about instead of triggers

I don't understand your objection to instead of triggers. In cases where you will have a high rate of failure, you would rather write to a table then roll it back, with all the additional costs involved, than not do anything at all? I certainly agree that the OP's problem is not an ideal use case, but that doesn't mean there are zero use cases - even if you don't count the use case with views. — Aaron Bertrand ♦ 4 mins ago

@MaxVernon I'm not sure your query is equivalent (regarding your answer to that ugly OUTER join problem)

@ypercube yeah, I suppose it depends on the three fields involved in the "JOIN" being defined as "NOT NULL". Or do you have some other thing I didn't think of?

It is after all, monday morning, and I've only had a single coffee. :-)

@MaxVernon Too complicated to have a definite opinion. But the nulls checked in the original query are produced by the outer joins. Your query seems to check existing in the tables nulls. Doesn't seem right.

Declarative integrity is always better than a trigger. An after trigger is always better than an instead of trigger. Instead-of triggers have 'funky' behavior in a lot of situations, they are opaque to access path optimizations in DML, they make isolation levels behave erratic. Instead-of triggers scream 'I should had been an access stored procedure instead'. And I don't buy the 'do the work twice' argument at all, optimizing the exception path should not influence the design, specially at the cost of the slowing the frequent path. — Remus Rusanu Jun 7 '12 at 7:32

That seems to be the bulk of his reasoning. My quoting him doesn't mean I agree 100% with what he says.

@ypercube just considering the subquery for a minute, it seems to me WHERE X=Y OR Y IS NULL would be the same as a simple LEFT JOIN X ON X=Y, no?

2:01 PM

@PaulWhite thanks, I get some of his arguments, but I still don't think that lands the entire user base at ZERO use cases.

@AaronBertrand He exaggerates sometimes. Could be worse. he could be like Kejser :)

@AaronBertrand Seems to me, the INSTEAD OF wouldn't exist if it had zero use cases.

@MaxVernon well, autoshrink exists

and EOMONTH()

@AaronBertrand yeah, and sometimes you want that behavior. Admittedly very seldom.

2:03 PM

@MaxVernon I would never let any customer turn that on, I don't care how badly they think they want it.

@MaxVernon I think no. They are not.

@ypercube that works well for some things too. I agree with you it's pretty crap for most things.

INSTEAD OF was primarily added (I think) to support custom logic for updatable views, where they wouldn't normally be so. Also Oracle supports them, and/or BEFORE triggers something something.

@ypercube I'm no scientist, so I'll have to defer. Would love to know more.

BEFORE trigger would be a lot more useful. Especially for DDL (which doesn't have before or instead of - create an index that takes 12 hours then roll it back because it violated naming scheme - awesome!)

2:04 PM

I worked with a POS (point-of-sale, not the other meaning) system where it was essential the in-shop MSDE instances ran with auto shrink on.

@PaulWhite because they were on hard drives insufficient to hold peak data?

^^^ there's a better fix for that

Just saying. :-)

@AaronBertrand The in-shop machines had very limited hardware, yes.

@AaronBertrand Like what?

@PaulWhite Like outfitting the machines with drives capable of storing the amount of data they need to store?

Sizing the database so that it doesn't need to grow or shrink based on peak load etc.?

@AaronBertrand Retail is not a high-margin enterprise. Every $ counts when you have tens of thousands of these things out there in various states of repair and in various places in a busy retail environment with no on-site tech people.

TL;DR I was aware of the alternatives and auto shrink was the way that worked best for us.

There are obviously going to be cases where a feature can be justified, but that doesn't make it the best choice - it is usually a compromise.

2:09 PM

Naturally. For me, in those specific circumstances, it was the best choice. I doubt it is a very common scenario these days, given modern-ish hardware etc.

People use cursors all the time when 99% of the time there are better answers - all comes down to how much time and brains you can invest in writing it better and refactoring.

@ypercube I feel rather stupid now. Thanks for pointing out my error.

Q: SQL Server partitioning of many-to-many tables - triggers

Why would you use partitioning here?

0

Many to many tables usually have only foreign keys to related tables, like EmployeeId PositionId Is it good idea to add column only for partitioning and set its value with INSTEAD OF INSERT/UPDATE trigger from one of the related tables that has such a column?

sql-server partitioning

Well this was also quite a number of years ago. The big problem, IIRC, was that the local hard disk was indeed extremely small (~10GB). It just was not possible to configure the database files at peak size, because then the one-a-week bulk-SKU-price changes would fail. Anyway, there were lots of considerations, specific to the precise business and technology involved.

Even Microsoft (90K employees) couldn't possibly see a ton of benefit from partitioning a table with two ints.

@PaulWhite But if the database needed to auto-shrink after that bulk process was done, isn't it possible that the bulk process itself could have caused the database to try to grow beyond the capacity of the drive?

If a customer brought that problem to me today, and hardware wasn't an option, I would probably have looked into ways to make that process less impactful (bulk in chunks with commits/log backups/checkpoints in between).

2:17 PM

@AaronBertrand There were many such processes, I am simplifying of course. And many reasons for the database to grow, not just bulk-change processing. The POS was also a commercial package, with limited scope for e.g. chunking bulk ops. Trust me when I say I considered these options.

I obviously don't know all of the specifics in your scenario and what other variables you were dealing with at the time. I do accept that 10 years ago auto-shrink was probably a much more attractive option than it is today.

I can't think of a scenario where I would enable auto-shrink today, in production.

But that equally doesn't mean there isn't some $multi-billion business out there that wouldn't fall over immediately if the option were removed from the product.

@PaulWhite Oh certainly, I'm not advocating for its removal (at least not in this conversation). Was just saying that a feature's existence does not necessarily mean it's because there are good use cases.

Agreed. At least not these days. I was reminiscing :)

It might no sound it, but it was a fun engagement.

Auto-close, for example - I would never let a customer use that, either, but there are some use cases (e.g. budget shared database hosting with no SLAs)

2:22 PM

They also used auto-close! MSDE limited to 1GB RAM. POS system used many databases.

off-topic but @AaronBertrand getting any sleep with the newborn? :)

...so customers either don't care or have no grounds to complain when their app takes 5 seconds to negotiate connectivity to their database...

@bluefeet it's actually the 2-year old that is up a lot over the past few days - wants to be part of the action.

I would sleep through the feedings if Madeline wasn't constantly wanting to help

string-representation

Really?

Meh.

@AaronBertrand well she feels left out. :)

We had that with our older one too, @AaronBertrand

2:33 PM

She is cute with her though

adorable!

@AaronBertrand so cute

free my hands so that I may claw myself and everyone about me with my razor sharp baby talons!

she was born with super daggers

Madeline too, we had to put mittens on her constantly so she wouldn't scratch herself or carve out one of her own eyeballs inadvertently

@AaronBertrand I could have used mittens this weekend - I nearly cut my finger off with a knife

knife blade thru my finger nail = emergency trip to urgent care

2:37 PM

My brother-in-law is a chef and his mantra is: You burn, you learn

You cut, you're an idiot

@bluefeet YOUCH!

@bluefeet agh

@billinkc I truly felt/feel like an idiot

@bluefeet meh, it happens

and of course we just sharpened our kitchen knives

My favorite cooking horror story features my old coworker and his wife. They had plenty of escapades - like the cling wrap has fallen from the top shelf more than once and the sharp side lacerates her arm resulting in ER visits

This one though, this one took the cake.

His parents were coming over and she was all nervous about everything being just right, getting flustered about the whatever in the over not turning out right

2:40 PM

ugh

CROSS JOIN is not the same as an INNER JOIN — bluefeet ♦ 9 secs ago

Just as he walks into the kitchen, she plucks the dish from the oven.

Barehanded

Straightens up and suddenly realizes what the heck she's done, screams and begins to drop the dish

James

@billinkc How bad were the burns

?

He sees it falling and dives to save it from breaking. Catches it and proceeds to also give himself second degree burns

Food continues falling and shatters on the ground and they have to pile into a manual transmission vehicle to drive themselves to the ER

It's funny now to hear the story because it's long past but oh my god, what a comedy of errors

@bluefeet are they really the same in MySQL? Reason I hate that crappy platform #24,561

Or are the docs really that wrong? (Reason #24,562)

@AaronBertrand The docs actually say that but then they show using an ON

wrong, wrong, wrong info

2:47 PM

Referring to a problematic column as "infected" is most excellent. — Eric Hauenstein 58 mins ago

It's MySQL what do you want?

Citing issues with MySQL documentation is like rearranging deck chairs on the Titanic.

I'd really like to say, you should ignore the MySQL docs - they don't even properly teach how to group by.

It's open source

the people writing the docs are the same ones asking questions on SO

James

@AaronBertrand @bluefeet I think the docs mean that INNER without an ON clause is equivalent to CROSS.

(in MySQL)

Which is what your comment says... sorry.

No, there is no reason why any sane person would use a CROSS JOIN here. CROSS JOIN implies that you want a Cartesian product - while it is possible to coerce it to behave like an INNER JOIN with additional filters, I don't see the point. When you want an INNER JOIN, use INNER JOIN; when you want a CROSS JOIN, use CROSS JOIN. Regardless of what the open source documentation says. I can ride my tricycle on a lot of highways where non-motorized vehicles aren't explicitly forbidden, but that doesn't mean it is sane or logical to do so. — Aaron Bertrand 12 secs ago

2:57 PM

@James yep

sqlfiddle.com/#!2/4f2edb/1

In MySQL INNER JOIN with no ON is a CROSS JOIN because why not?

@JNK because MySQL

Yosemite Sam was in charge of writing the optimizer that day

MySQL has its own rules for SQL, that's why its MySQL

Messwith Yourhead SQL

why are they assuming MySQL?

+1. The only "sane" reason that someone would replace the keyword "INNER" with "CROSS" in the OP example is that they were using MySQL, and they are aware that in MySQL "JOIN", "INNER JOIN" and "CROSS JOIN" are synonymous. — spencer7593 1 min ago

James

2:59 PM

@bluefeet It's named after Monty Widenius's daughter My, but your interpretation is sadly more meaningful : (

A: How To Recover Data From A Corrupt Mdf File?

A $349+ product that can't possibly deliver on the promise to recover any corrupt MDF?

-2

there is some possible solution for that: method one: using MDF repair tool http://www.mdfrepair.com/ method two: if you have the corrupted DB then: 1)shutdown SQL server 2)move the files to another location or rename it 3) start SQL server again 4) create a new DB with the same info (databa...

has @Kermit already posted the link to the article with the reporter in alaska who quit on air?

@swasheck not yet. but it's missing the context

ktuu.com/news/news/…

►

^ missing the context

3:07 PM

@swasheck blocked

context not needed

@swasheck hey, congrats about your presentantion. I read that it was very good

@Lamak thanks. i'm not sure it was but ... thanks nonetheless

The desk person was pretty flummoxed - "Sorry for that. We... we'll be right back. Pardon for us. Meanwhile..." then continues with the story.

> “Now everything you heard is why I, the actual owner of the Alaska Cannabis Club, will be dedicating all of my energy for fighting for freedom and fairness which begins with legalizing marijuana here in Alaska," she said. "And as for this job, well, not that I have a choice but, fuck it, I quit.”

3:08 PM

Anyway I don't think the pot lady will ever work on TV again.

.@swasheck can tell us tons of info about statistics....but can he change the color of his SSMS background? #sqlsat331 #sqlsatdenver

Tweeted by SQLBob on September 20, 2014 at 4:01 PM

@AaronBertrand no. probably not

@swasheck can you?

Uh oh did you have yellow text on lime green background

What the heck is hsqldb

for those that assumed MySQL on that question

> The database is HSQLDB

Oh my gosh no

stackoverflow.com/tags/hsqldb/info

Written in Java

3:16 PM

> HSQLDB is extremely fast, especially when used in memory, or when the database is relatively small

Our application is dog slow, even in memory!

@AaronBertrand what could go wrong

@Lamak it took me 5 minutes ... and only one person in the room knew how to do it

@AaronBertrand dark solar theme

yay for swiftkey!

love swiftkey

Ugh swiftkey on iOS8 is horrible

I actually couldn't get it to work, suspect they still have some kinks

I switched to that keyboard and it was blank. The only way I could switch back was to completely uninstall it.

ugh

3:27 PM

@AaronBertrand Haven't upgraded yet

Q: pivoting data returning an error in sql server?

0

Previously, i asked for help in using pivot. I was able to create a query without using the pivot. Now i have a problem, when i run my query i am getting an error "Cannot create a row of size 10422 which is greater than the allowable maximum row size of 8060.". Please find the table structure an...

sql-server pivot

@AaronBertrand hm. i've not had problems on ios8 yet ... there are a few hoops

@bluefeet who on earth is going to consume a report with > 280 columns?

And couldn't that same person still consume it if it was flipped 90 degress?

@swasheck ooh, I like hoops, can you elaborate?

3:30 PM

@AaronBertrand install. General->Keyboards->Add New Keyboard->Third-Party->Swiftkey ... General->Keyboards->"Swiftkey - Swiftkey"->Allow Full Access

Yep, did all that. Keyboard was blank.

hm. bummer. sorry to hear it. i've exceeded my iphone troubleshooting capabilities

It might be device specific too, this is an iPhone 6, maybe they have trouble with the different screen size or some underlying technical thingamabobber

Never tried any 3rd party keyboards before this phone.

interesting. this is a 5 so that may be the case

I'll try it again maybe later this week or after I see them ship an update.

But my first experience was extremely frustrating.

3:33 PM

i'm sure

@AaronBertrand but they probably aren't that smart

Ignorance is no excuse. (Well, that applies to anyone but NFL's head office.)

Q: Convert 'dd/MM/yyyy hh24:mm:ss' char to datetime in sql server

@bluefeet what is the current "read the manual of the first google search" close reason in SO?

trying to decide how to close this question before MAli answers it

-1

I'd like to know please which code to use in order to convert a string variable whose format is 'dd/MM/yyyy hh24:mm:ss' into datetime type : declare @var as char(19); set @var = '22/09/2014 16:30:20'; select CONVERT(DATETIME, @var, ?); Also i'm wondering whether there are any other ways to con...

sql sql-server datetime type-conversion nvarchar

@Lamak too broad works

Shawn Melton

Huh, why don't they have "too localized" on SO...that seems more appropriate than too broad

3:46 PM

@ShawnMelton gone a long time ago

it was used wrong far too often

Shawn Melton

Yeah I can see that happen on SO

we have a process that needs to take a production backup and then attach it to a different server weekly - for reporting needs. Should we drop the existing DB on the reporting server or restore with replace?

@bluefeet What is the concern? Why do you think you need to drop it first?

@AaronBertrand the process we have in place drops first and then attaches the backup

I've always used restore with replace. In either case you need to kick users out (setting to single_user always worked for me).

restores or attaches?

I don't see any major difference, or a need to change it

3:49 PM

sorry, drops, then restores

we've been having some issues and I'm trying to figure out if we need to change the process

What are "some issues"?

@bluefeet it does?

@AaronBertrand don't like my vague answers? I've been spending too much time on SO

I think the problem is more the process in FTPing the prod copies, we are getting some corruption with the backups and the drop/restore is failing

@Lamak for that particular question, it could be answered a number of ways - so too broad

@bluefeet I don't think that will be fixed by changing the restore process.

I would first drop FTP and use a more reliable VPN tunnel if you can.

@bluefeet alright then (though it's already closed)

3:52 PM

@AaronBertrand I'm not sure that is possible. I'll have to find out

I know they do the same thing drop/restore and restore with replace - just wondering if one is preferred over the other

@bluefeet can't think of any reason, no.

ok, thx

4:14 PM

@bluefeet Restore with replace avoids the overhead of creating the database files. Non-log files can be IFIed but there's still an overhead, and logs have to be zeroed, IIRC.

> I must perform this operation under oracle 9 and sql server 9

0

Q: How to do this select?

I must perform this operation under oracle 9 and sql server 9 I have on table with images inside and it can be quite heavy. I want to select the ten first rows then the ten next etc... until the end of the table. If possible I would like to have the same request to do that for oracle and sql ser...

sql sql-server oracle

@PaulWhite ok, thanks

@PaulWhite does it really though? What if the source database files have changed?

I confess I haven't thoroughly tested all potential scenarios, but I don't think SQL Server can always reuse the existing files.

Neither have I. I would hope it is smart enough to extend existing files, and just add the minimum necessary.

And if you have half-decent I/O and/or this is an overnight process for a reporting system that is only used during the day, I'd rather focus on the FTP issue that is making the restore corrupt than muss with the existing restore process.

Sure. Just answering the original question to the best of my knowledge.

4:25 PM

No, it's a good point, however I recall that being less beneficial in my case because the data file sizes did fluctuate drastically (in both directions) due to the nature of the app (databases were re-purposed and right-sized). I'm just reflecting what I would do currently.

I saw this DBA answer so I wasn't sure

If only I didn't already have 43 blog posts in progress...

@AaronBertrand I'm sure you're right that the FTP side is the place to concentrate effort.

@billinkc I use a combination of shellrunas with keepass

@billinkc shellrunas adds an entry on the context menu to prompt for credentials, and you can configure keepass to do automated entry of credentials using a keybind

@Gonsalu Ohhhh, I'm making note of this. Thank you

4:36 PM

@billinkc so I would just right click SSMS or Excel, f.e., and then press Ctrl+Alt+A, and it fills it in for me. It's pretty cool, because you can keep a lot of credentials encrypted, so you don't have to have one .bat file per app per customer

@billinkc No problem! Thought I would share since I'm really happy with the solution :-)

It's a bummer not being in the domain with SSDT-BI... even if you run it with shellrunas or runas /netonly, SSAS deployment process is spawned without the domain credentials context, so you have to run the deployment wizard separately

same thing for previews of SSRS, although for that one you have a workaround

Preview is where I'd always forget. For cube stuff, we didn't even try. Just RDC into a domain joined box

It's really weird, but instead of the preview, if you right-click the report and select "Render" (or view or something, I forget the name of the option), a window pops up and it renders the report with the domain credentials

Q: How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

4:51 PM

Who wants to run backups from a batch file?

0

I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.

sql-server-2008-r2 sqlcmd

@PaulWhite, I was reading your answer about MERGEing a table subset (dba.stackexchange.com/a/30653/4134) and you mention that there's problems as well with the CTE approach, have you blogged about this somewhere else?

I did blog about something like this today, but that is not exactly what I had in mind.

sqlperformance.com/2014/09/system-configuration/…

@AaronBertrand sounds like good use cases for SSIS

@Gonsalu anything external - I don't think most of those really require the complexity of SSIS, all depends on your expertise. Active Directory surely not.

Can executing an SSIS package be made asynchronous from inside a SQL Server job, trigger, procedure, etc.? I'm not even sure. And would you still need xp_cmdshell in some of those cases?

@AaronBertrand how do you feel about having linked servers to read from Active Directory? Would you recommend against it?

4:58 PM

@Gonsalu I'd have to understand the context. Why does SQL Server need to read anything from Active Directory?

@AaronBertrand Yeah, it can run asynch. No, in SQL Server Agent you get a specific option to run SSIS packages, so you don't need xp_cmdshell. You need to define accounts if you want to run it under isolated credentials, tho...

@AaronBertrand I have some cases where I have to get the list of users that belong to an AD group, to materialize many-to-many tables to support SSAS dynamic security. I usually create a linked server to get the user membership information, but I'm not sure if it's the best solution

By accounts, I mean proxy accounts

@Gonsalu what if it's not in a job?

@Gonsalu Yes in SQL Agent (2012+)

@AaronBertrand you can call dtexec to run packages

@Gonsalu and how do you call dtexec? xp_cmdshell. Ouch (for most people).

In a lot of scenarios, SSIS is no less "external" and for the same reasons as just about anything else.

5:06 PM

@AaronBertrand if you're on SQL Server 2012, you have stored procedures to start package execution as well

With 2012 and the project deployment model, they build the CLR methods into the catalog so you can just run a package from SSMS without needing to shell out to xp_cmdshell and then hitting dtexec. It's all a matter of whether you specify the sync/asynch parameter

@AaronBertrand, but I understand what you mean

@Gonsalu repeat: (for most people).

@billinkc yeah, that's true, it has to be with the project deployment model. I've been using it so much I almost forget about the package model

@billinkc, do you even consider the package deployment model for new projects?

I haven't found a compelling reason to do so

5:10 PM

@Gonsalu don't use SSAS, and don't understand the differences in the security model there. What alternatives do you have instead linked servers?

If someone's heavily invested in their current process for auditing, logging, configuration etc, then I suppose package deployment model makes sense but the integrated management with the SSISDB is awfully compelling to me. As an admin, I would think they'd also be pushing for it

@AaronBertrand using SSIS with script tasks, I guess? I can't think of more options... I try to avoid script tasks, so that's I opted for the linked server instead

@billinkc yeah, that's my line of thinking as well

@Gonsalu to read AD? Yes, I would use a linked server instead of SSIS.

@AaronBertrand thanks! Glad you think the same, I'm always second-guessing myself when I choose stuff that I'm not totally comfortable with

I have not had a pleasant time with AD linked servers, usually because I need data that is in the I8 format

A .NET extract (to file or to table) has served me better. YMMV

5:18 PM

@billinkc in projects that use the project deployment model, do you use the server execution id for batch ids in tables, or do you use something else?

Q: Relating ExecutionInstanceGUID to the SSISDB

Yes, our 1 table "auditing" framework would record the server execution id

5

The 2012 release of SQL Server Integration Services, SSIS, has delivered an SSISDB catalog which tracks the operations of packages (among other things). The default package execution for solutions using the Project Deployment model will have logging to the SSISDB turned on. When a package execu...

sql-server-2012 ssis

Do you just keep the execution id to join with catalog tables, or do you store information that is on the catalog tables, to be on the safe side?

We stored the execution id in our audit tables to be able to relate what we cared about (insert/update counts & package duration) to specific executions. That way, "our 100 row insert rand like crap, why?" look at the catalog for the same time frame and discover someone else has launched packages.

That was the theory at least. The heavy, concurrent workload never materialized

the reason I'm asking is because at my current project, I'm just storing that execution id, but I'm afraid that the SSIS clean up will get rid of some execution information, and I'll lose the ability to know that execution id 1234 was from an execution that ran 2 years ago or something

It's not too important for the project, but I'd like to know what I'm dealing with :-)

Hadn't thought about that. The 2012 client is about 3 months out before they'd start getting log data archived out. I think my assumption is that I don't much care about looking for trends that far out but that would change depending on actual needs

5:26 PM

@billinkc great answer, btw

Our logging and reporting stuff is really just to cover our backside as well as give a quick sniff of whether things went well

Sounds similar to what we're using it for :-)

5:43 PM

@Gonsalu No, I don't think so. The only place I remember writing about those particular MERGE considerations is in that answer.