SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

var1ety: Jul 26, 2004

MrHyde posted:

Thanks for the help. I wasn't really clear. I don't want a list of integers, I just want a list of comma separated values I have, a better example would be ('firstVal','2ndval','3rdval')

I guess the actual problem is I have a list of values from an outside source and I have a table. I want to figure out which values are in the list, but not in the table.

A pattern I've used with Oracle in the past that should be portable is to use a dummy table that is known to have more rows than elements in your input string to parse the string with substr/instr, using your current row number as an offset. You will need to find a way to number the rows, possibly using row_number() or rank(), in the case you do not have a rownum pseudo-column.

See the following (the input string 'a,b,c,d' would be passed in and duplicated in several spots in the query).

code:

select case
         when instr(base_str, ',') = 0 then
          base_str
         else
          substr(base_str, 1, instr(base_str, ',') - 1)
       end AS part
  from (select substr('a,b,c,d', instr('a,b,c,d', ',', 1, rownum) + 1) AS base_str
          from all_objects
         where rownum <=
               length('a,b,c,d') - length(replace('a,b,c,d', ',')) + 1)

code:

PART
b
c
d
a

# ? Jul 1, 2008 22:28

Adbot: ADBOT LOVES YOU

# ? May 31, 2024 16:00

crazypenguin: Mar 9, 2005; nothing witty here, move along

(mysql 5) I have a list of names (application side). I need to populate a table with the ids that refer to rows in the names table. Except some of these names may not yet exist in the names table and should be created.

What I'd like to do is:
1. Figure out what names are in my list (application side) that are NOT in the names table yet.
2. Add them to the names table.
3. Do the relatively simple query to add the ids corresponding the my list of names to the other table.

I can't figure out how to do (1) without either creating a temporary table to hold my list of names that I have application side (how do you select data from a database that ISN'T in the database?), or by finding a list that IS in the database, and computing the inverse application side.

So is there as way of doing (1) easily? Should I go with a temporary table? Or should I do it application side? Or is there some other method of accomplishing my goal that I'm not thinking of? (making the name column UNIQUE and just inserting everything does not count!)

# ? Jul 2, 2008 01:14

var1ety: Jul 26, 2004

crazypenguin posted:

(mysql 5) I have a list of names (application side). I need to populate a table with the ids that refer to rows in the names table. Except some of these names may not yet exist in the names table and should be created.

What I'd like to do is:
1. Figure out what names are in my list (application side) that are NOT in the names table yet.
2. Add them to the names table.
3. Do the relatively simple query to add the ids corresponding the my list of names to the other table.

I can't figure out how to do (1) without either creating a temporary table to hold my list of names that I have application side (how do you select data from a database that ISN'T in the database?), or by finding a list that IS in the database, and computing the inverse application side.

So is there as way of doing (1) easily? Should I go with a temporary table? Or should I do it application side? Or is there some other method of accomplishing my goal that I'm not thinking of? (making the name column UNIQUE and just inserting everything does not count!)

If your names list is small you should pass it to your application. If it's large then a good solution might be to wrap the checking/creation SQL in a function which does nothing if the name exists, and otherwise it creates it. Then you would call this function N times, once for each name in your list.

# ? Jul 2, 2008 03:13

zapateria: Feb 16, 2003

Is it possible to modify a User_type in Sybase?

code:

User_type       Storage_type    Length      Nulls       Default_name    Rule_name       Access_Rule_name 
---------       ------------    ----------- ----------- ------------    ---------       ---------------- 
T_NOKKEL        numeric                   6           0 NULL            NULL            NULL

I need to increase the length of T_NOKKEL.

# ? Jul 2, 2008 13:24

Aredna: Mar 17, 2007; Nap Ghost

PostgreSQL 8.2

I'm using:

code:

substring(a from '^(?:(?:ABC|DEF)-)?(?:[^-]+-){2}([^-]+).*$')

and can't find a good way to make it case insensitive.

I have 34 characters instead of the 6 listed above so changing them all to [Aa][Bb], etc would make it much larger. It also will be harder to read and maintain should the prefixes change.

I could also just do upper(a) and not worry about it, but this will be ran millions of time (although in a reporting environment, not a production environment), so I want to make sure it's as fast as possible.

I know with regexs in postgres you can use ~* instead of *, or even use flags with regex replace to specify what you want, but I can't find a way to do it with a substring using a regex.

Any suggestions?

edit: forgot to mention it's 8.2 so I can't use regexp_matches that was added in 8.3

Aredna fucked around with this message at 19:28 on Jul 3, 2008

# ? Jul 3, 2008 19:24

Victor: Jun 18, 2004

Try prepending your regex with (?i).

# ? Jul 3, 2008 19:30

Aredna: Mar 17, 2007; Nap Ghost

Victor posted:

Try prepending your regex with (?i).

beautiful... thank you!

# ? Jul 3, 2008 19:42

Super Mario Shoeshine: Jan 24, 2005; such improper posting...

This week i started looking at some application made in Visual FoxPro 5.0 and for some reason everytime i want to change a date field in a registry on a .dbf file foxpro just shits in my mouth and laughs at me, so if i want to change the date to 01/01/2005 it automatically shortens it to 01/01/20 despite the fact that the date lenght is invariably 8. I tried to use SQL but to no avail? Can someone help me or tell me how to fix this poo poo.

# ? Jul 5, 2008 00:24

Zombywuf: Mar 29, 2008

Super Mario Shoeshine posted:

This week i started looking at some application made in Visual FoxPro 5.0 and for some reason everytime i want to change a date field in a registry on a .dbf file foxpro just shits in my mouth and laughs at me, so if i want to change the date to 01/01/2005 it automatically shortens it to 01/01/20 despite the fact that the date lenght is invariably 8. I tried to use SQL but to no avail? Can someone help me or tell me how to fix this poo poo.

Foxpro's ADO DB interface (assuming that's what you're using) is very particular about what it accepts as dates. Unfortunately I can't remember what it likes, but I think it's something along the lines of '${YYYY-MM-DD}'. Googling "Foxpro date format" might help.

# ? Jul 5, 2008 15:01

grimace: Jun 20, 2001; Jon Bence don't take
no phone surveys
because he's dead
RIP 19__ - 2009, 580 lbs

Zombywuf posted:

Foxpro's ADO DB interface (assuming that's what you're using) is very particular about what it accepts as dates. Unfortunately I can't remember what it likes, but I think it's something along the lines of '${YYYY-MM-DD}'. Googling "Foxpro date format" might help.

More accuarately, if STRICTDATE is set to 1, the date format has to be {^YYYY-MM-DD}. If you set it to 0, it can be in the format {MM-DD-YYYY}, but an invalid date will still update, but with a blank date.

And I don't know how the CENTURY option would affect any of this. This is from help for SET CENTURY:

quote:

In Visual FoxPro 5.0, issuing SET CENTURY TO without additional parameters sets the century to the current century �1 and rollover to zero if the two-digit year of the system date is less than 50. Century is set to the current century if the two-digit system date is greater than 50. For example, if the current year were 1998, nYear would be 48, the last two digits of 2048 (1998 + 50).

It sounds like Foxpro 5.0 is retarded when it comes to 4 digit years. I've only used 6 and up so I don't really know.

# ? Jul 5, 2008 16:05

joojoo2915: Jun 3, 2006

I have the free SQLEXPRESS 2005 version of MSSQL server loaded on an extra computer here at home that is also running windows server '03. I would really like to be able to make a connection with the SQL server via asp pages that I want to run locally. I have been working on this most of the afternoon and I still have no idea what I'm doing wrong. The SQL server is set up for windows authentication. I have a separate connection file that I am using that contains this code:

<%
prices_connection_string = "Provider=SQLOLEDB; Data Source = KILLBOT\SQLEXPRESS; Initial Catalog = prices; user id = [windows login]; password = [windows password]"
%>

I have tried a ton of different combinations for the user id and so far all I get is:

Microsoft OLE DB Provider for SQL Server error '80004005'

Login failed for user '[windows login]'. The user is not associated with a trusted SQL Server connection.

/pricessite/index.asp, line 6

Using the SQL Surface Area Manager there is a link to add a new administrator but all that takes me to is a page where I can set privileges for the local machine user. If anyone knows of a way to set the user name and password for the sql server or knows what I am doing wrong with the windows authentication user id and password I would love to hear it. Thanks!

# ? Jul 6, 2008 01:51

Victor: Jun 18, 2004

Windows authentication with SQL Server is based on Kerberos or NTLM; in neither of these cases do you specify the username/password in the connection string. Instead, you put "Trusted_Connection=Yes" in the connection string; here are some details.

# ? Jul 6, 2008 02:39

Super Mario Shoeshine: Jan 24, 2005; such improper posting...

That worked wonders. Thanks to you all

# ? Jul 6, 2008 04:05

!Klams: Dec 25, 2005; Squid Squad

I'm not sure if this necessarily goes in here, but I didn't want to start a new thread and this seemed like the most likely 'small questions' type thread to ask in. Basically, I want to start a mailing list.

I've got lots of e-mail addresses of people who've been to gigs, and what gig they went to see. I am allowed to use these addresses for commercial purposes. I want to group them all, I'm guessing in some sort of database, so that I can easily select a subset of them relevant to an upcoming gig. For example, if De La Soul are coming to town, I want to be able to tell all the people who have been to a hip hop gig.

I'm not really sure how to go about doing this. I have a little bit of experience with Microsoft Access, but I don't know how great that is for this sort of thing? Could anyone maybe point me in the right direction? Cheers peeps!

# ? Jul 7, 2008 00:30

Factor Mystic: Mar 20, 2006; Baby's First Post-Apocalyptic Fiction

Situation: I have a table where multiple fields each contain an id to a row in another table. Simplified example tables:

code:

Table: Offers

Id    Name_Id1    Name_Id2    Name_Id3
1     4                  2                   4
2     1                  3                   null
3     2                  null               3

code:

Table: Names

Id    Name
1     Alice
2     Bob
3     Cathy
4     Dan

The goal is to 'fill' queries on Offers with data from Names. I understand that this can be accomplished with a Join, but I don't know how to construct a query that works for all the Name_Id fields separately. If there were just one Name_Id field in the Offers table, I think a general example solution would be:

code:

SELECT Offers.Id, Names.Name FROM Offers, Names WHERE Offers.Id=3 AND Names.Id=Offers.Name_Id

code:

Offers.Id    Names.Name
3                Bob

And that approach works on my actual data, for the other fields, bu I don't know the proper way to separate look ups to the same table on the same field more than once.

E: VVV Bingo, that will do it. Thanks!

Factor Mystic fucked around with this message at 01:13 on Jul 7, 2008

# ? Jul 7, 2008 00:54

epswing: Nov 4, 2003; Soiled Meat

Factor Mystic posted:

Situation: I have a table where multiple fields each contain an id to a row in another table.

(Discussed on synirc, posting for posterity: )

code:

select o.id, n1.name, n2.name, n3.name
from offers o
    inner join names n1 on o.nid1=n1.id
    inner join names n2 on o.nid2=n2.id
    inner join names n3 on o.nid3=n3.id

A little ugly, I'm not sure if there's a way to write one join to snag multiple fields like that though.

Anyone?

# ? Jul 7, 2008 01:12

SHODAN: Feb 20, 2001; NARCISSISTIC QUEEN BITCH:
ASK ME ABOUT BEING BETTER THAN YOU

This is probably a really dumb question, but here goes:

I need to re-index a table so that the primary key id's are sequential, is there some administrative command that does this, or would I have to do something like:

for each record in table
id = position of next record - 1

?

Thanks in advance.

# ? Jul 7, 2008 01:33

Begby: Apr 7, 2005; Light saber? Check. Black boots? Check. Codpiece? Check. He's more machine than kid now.

SHODAN posted:

This is probably a really dumb question, but here goes:

I need to re-index a table so that the primary key id's are sequential, is there some administrative command that does this, or would I have to do something like:

for each record in table
id = position of next record - 1

?

Thanks in advance.

The easiest thing would be to drop the primary key field, then add in a new primary autonumber key field.

However, this begs the question, why do you want to do this? You should never ever need to change a primary key value in a properly designed database.

# ? Jul 7, 2008 02:24

var1ety: Jul 26, 2004

epswing posted:

(Discussed on synirc, posting for posterity: )
code:
select o.id, n1.name, n2.name, n3.name
from offers o
    inner join names n1 on o.nid1=n1.id
    inner join names n2 on o.nid2=n2.id
    inner join names n3 on o.nid3=n3.id
A little ugly, I'm not sure if there's a way to write one join to snag multiple fields like that though.

Anyone?

Using 3 joins is, in my opinion, the appropriate solution to this problem (you will need to use outer joins since the offers table can have nulls). With indexes the look-up against the names table should be very efficient.

Here's a single table join Tom Kyte has suggested for "attribute" tables (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:31263576751669) that will work in this situation.

code:

select o.id,
       max(case when o.name_id1 is not null and n.id = o.name_id1 then n.name end) AS name_1,
       max(case when o.name_id2 is not null and n.id = o.name_id2 then n.name end) AS name_2,
       max(case when o.name_id3 is not null and n.id = o.name_id3 then n.name end) AS name_3
  from offers o
 inner join names n on (n.id = ANY(o.name_id1, o.name_id2, o.name_id3))
 where o.id = 3
 group by o.id

The query plan transform on this query is pretty nasty in Oracle and it's more expensive then doing 3 joins.

# ? Jul 7, 2008 15:55

Victor: Jun 18, 2004

If Factor Mystic is using SQL 2005:

code:

select  up.id, n.name
from    offers o
unpivot (nid for source_column in (nid1, nid2, nid3)) up
inner join names n on n.id = up.nid

Otherwise, I'd suggest union (or union all, if you don't want/need distinct action):

code:

select	o.id, n.name
from	(
	select	id, nid = nid1
	from	offers
	union
	select	id, nid2
	from	offers
	union
	select	id, nid3
	from	offers
) o
inner join names n on n.id = o.nid

# ? Jul 7, 2008 20:25

moww: Jan 16, 2005; Can Type the Hype and Post the Most.

Will someone please help me figure out what's syntactically wrong about this MS SQL query?

code:

SELECT 0 pieces.display_image
FROM pieces
JOIN users
JOIN user_pieces_link AS upl
ON pieces.piece_id = upl.piece_id
AND upl.user_id = users.user_id
WHERE users.username = 'test1'
ORDER BY pieces.entry_date DESC

# ? Jul 8, 2008 00:57

Reaction Cat: Jun 24, 2008

moww posted:

Will someone please help me figure out what's syntactically wrong about this MS SQL query?
code:
SELECT 0 pieces.display_image
FROM pieces
JOIN users
JOIN user_pieces_link AS upl
ON pieces.piece_id = upl.piece_id
AND upl.user_id = users.user_id
WHERE users.username = 'test1'
ORDER BY pieces.entry_date DESC

Are you missing a comma after the 0? Everything else looks fine.

# ? Jul 8, 2008 01:18

moww: Jan 16, 2005; Can Type the Hype and Post the Most.

Reaction Cat posted:

Are you missing a comma after the 0? Everything else looks fine.

The comma doesn't seem to be the problem. The error I get is

code:

Incorrect syntax near the keyword 'WHERE'.

I've also seen

code:

Incorrect syntax near the keyword '.'.

# ? Jul 8, 2008 01:23

atomic johnson: Dec 7, 2000; peeping-tom techie with x-ray eyes

moww posted:

The comma doesn't seem to be the problem.

I'm pretty sure it is. Try putting in a comma after the 0 and re-running it. Or take out the 0 and leave it as just "SELECT pieces.display_image FROM pieces ...."

# ? Jul 8, 2008 02:20

No Safe Word: Feb 26, 2005

moww posted:

Will someone please help me figure out what's syntactically wrong about this MS SQL query?
code:
SELECT 0 pieces.display_image
FROM pieces
JOIN users
JOIN user_pieces_link AS upl
ON pieces.piece_id = upl.piece_id
AND upl.user_id = users.user_id
WHERE users.username = 'test1'
ORDER BY pieces.entry_date DESC

You're missing the JOIN criteria for users. I think it's optional in some dialects (ie, it just looks for identically named fields), but if nothing else I always like having it there. It may be required in T-SQL.

edit: and yes also the comma missing

# ? Jul 8, 2008 02:23

moww: Jan 16, 2005; Can Type the Hype and Post the Most.

No Safe Word posted:

You're missing the JOIN criteria for users. I think it's optional in some dialects (ie, it just looks for identically named fields), but if nothing else I always like having it there. It may be required in T-SQL.

edit: and yes also the comma missing

Thanks for the help so far. I tried adding a comma but I still get the same syntax error. As for the JOIN criteria for users, is it not covered in

code:

AND upl.user_id = users.user_id

I didn't have the foreign keys set for the tables in that query, but even after setting them the syntax error remains. I don't seem to get any syntax errors when I don't use JOIN statements.

EDIT: Grimace, you are a true guru! I thought SQL was a lot more standardized before I started using MS SQL...

moww fucked around with this message at 04:43 on Jul 8, 2008

# ? Jul 8, 2008 03:04

grimace: Jun 20, 2001; Jon Bence don't take
no phone surveys
because he's dead
RIP 19__ - 2009, 580 lbs

moww posted:

Thanks for the help so far. I tried adding a comma but I still get the same syntax error. As for the JOIN criteria for users, is it not covered in
code:
AND upl.user_id = users.user_id 
I didn't have the foreign keys set for the tables in that query, but even after setting them the syntax error remains. I don't seem to get any syntax errors when I don't use JOIN statements.

You have to list the join statements together in TSQL, and they need to be in order. Try this:

code:

SELECT 
0, pieces.display_image
FROM pieces 
JOIN user_pieces_link AS upl 
ON pieces.piece_id = upl.piece_id 
JOIN users 
ON upl.user_id = users.user_id
WHERE users.username = 'test1' 
ORDER BY pieces.entry_date DESC

# ? Jul 8, 2008 04:13

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Is there a way to change a column from decimal 5 to decimal 5, 3 in one command or do I have to grow it to 8, 3, divide by 1000, and then shrink it?

# ? Jul 9, 2008 03:16

chocojosh: Jun 9, 2007; D00D.

In SSIS what is the best way to connect two different data flow tasks. Basic googling said I cannot use a recordset destination/source to share data and that I should instead use a raw file. I'd prefer not to use a raw file as I don't see why I should have to write to disk when I just want to store a bit of data between data flow tasks.

Edit: Another simple SSIS questions:

I have a CSV file that I import. On each line of the CSV file I store the same value in the last field (auto-generated from a program). I want to store that value into a variable so that I can use it elsewhere that may not have access to the CSV file. How can I do that? Would I need to first open the file with an execute script task?

chocojosh fucked around with this message at 15:52 on Jul 9, 2008

# ? Jul 9, 2008 15:21

No Safe Word: Feb 26, 2005

chocojosh posted:

In SSIS what is the best way to connect two different data flow tasks. Basic googling said I cannot use a recordset destination/source to share data and that I should instead use a raw file. I'd prefer not to use a raw file as I don't see why I should have to write to disk when I just want to store a bit of data between data flow tasks.

The first question is "why does this have to be two DFTs?", though I'm sure you considered that.

The crappy but pretty reliable way is to use global temp tables and setting your connection's RetainSameConnection property (under the Expressions) to True. Little muss, little fuss (except that you have to create the table in order to use it as an OLE DB src if you don't want to write a query to query it, and that you have to drop the table at the end of the job).

The "standard" way is raw files, but that involves getting file space somewhere.

Another way, kind of like the first one, is a permanent "staging" table, which is basically like the global temp table except it's a real table that you keep between runs. Obviously you can truncate it as needed to keep the space usage down. I'm a fan of this one where it's feasible.

# ? Jul 9, 2008 16:01

chocojosh: Jun 9, 2007; D00D.

No Safe Word posted:

The first question is "why does this have to be two DFTs?", though I'm sure you considered that.

The crappy but pretty reliable way is to use global temp tables and setting your connection's RetainSameConnection property (under the Expressions) to True. Little muss, little fuss (except that you have to create the table in order to use it as an OLE DB src if you don't want to write a query to query it, and that you have to drop the table at the end of the job).

The "standard" way is raw files, but that involves getting file space somewhere.

Another way, kind of like the first one, is a permanent "staging" table, which is basically like the global temp table except it's a real table that you keep between runs. Obviously you can truncate it as needed to keep the space usage down. I'm a fan of this one where it's feasible.

Why is the first way crappy if I may ask? I think I'd prefer to avoid creating a permanent table in our DB (over 500 tables for entire application) and I'd rather not create a raw file (may be irrational of me,

I have my SSIS package divided into three tasks.

Task 1: Import and filter errors (each error will have an e-mail sent so the appropriate person can fix it manually)
Task 2: Insert records (two out of three cases)
Task 3: Insert the final set of records (these records depend on data from task 2).

I admit that I could combine task 1 and task 2 but my supervisor found my first task a little bit complex as we're both new to SSIS (~20 elements [mainly error handling]) and suggested that I separate it. While it may not be the SSIS way, I do prefer smaller functions/tasks that are modulated. If I'm doing something horribly wrong please let me know.

BTW, another silly quick question -- is there a way to easily REMOVE columns from the flow? I have a bunch of derived columns each creating the same column (text of an e-mail) then I have a union all to combine all of these derived columns. After the derived column is created I *don't* want any of the other columns in the union all (as I've already created the error e-mail text).

Many, many, thanks for your help in figuring out how to actually *do* things.

# ? Jul 9, 2008 16:18

No Safe Word: Feb 26, 2005

chocojosh posted:

Why is the first way crappy if I may ask? I think I'd prefer to avoid creating a permanent table in our DB (over 500 tables for entire application) and I'd rather not create a raw file (may be irrational of me,

Because global temp tables are icky to me. Temp tables in general are icky, global ones even more so.

chocojosh posted:

I have my SSIS package divided into three tasks.

<snip>

This all sounds fine and yeah the global temp tables or real/actual staging tables are what I'd do.

chocojosh posted:

BTW, another silly quick question -- is there a way to easily REMOVE columns from the flow? I have a bunch of derived columns each creating the same column (text of an e-mail) then I have a union all to combine all of these derived columns. After the derived column is created I *don't* want any of the other columns in the union all (as I've already created the error e-mail text).

1) The derived column can replace a given column but I prefer never to do that because:
2) You can choose which columns you want to bring together in the Union. Right-click a row for a given data column and just choose "Delete" and it won't be passed through the Union. Even if you didn't already have one I sometimes just put in a one-input Union to do nothing but eliminate unnecessary rows so I don't get 6-10 warning messages in my logging db every time I process a row because not all the columns are used.

# ? Jul 9, 2008 19:05

chocojosh: Jun 9, 2007; D00D.

No Safe Word posted:

Because global temp tables are icky to me. Temp tables in general are icky, global ones even more so.

This all sounds fine and yeah the global temp tables or real/actual staging tables are what I'd do.

1) The derived column can replace a given column but I prefer never to do that because:
2) You can choose which columns you want to bring together in the Union. Right-click a row for a given data column and just choose "Delete" and it won't be passed through the Union. Even if you didn't already have one I sometimes just put in a one-input Union to do nothing but eliminate unnecessary rows so I don't get 6-10 warning messages in my logging db every time I process a row because not all the columns are used.

Supervisor didn't like any of the three solutions that much for passing data between flows (flat out refused a global temp table and is hesitant for similar reasons as me to use temp raw file). We may just end up shoving it all in one task (the error handling is, as always, the biggest part of the code ironically). It's a huge help though to be able to tell him "here are your options, pick one".. even if we don't like any of them!

Thanks for the union tip. I can't believe I missed that (or why they couldn't put a drat button called delete as they do elsewhere).

# ? Jul 9, 2008 19:21

Randomosity: Sep 21, 2003; My stalker WAS watching me...

Alright, I'm sure this is really simple, but how can I select the rows from table A that do not have associated rows in table B?

# ? Jul 10, 2008 17:35

No Safe Word: Feb 26, 2005

Randomosity posted:

Alright, I'm sure this is really simple, but how can I select the rows from table A that do not have associated rows in table B?

Use a LEFT OUTER JOIN and only SELECT where B.join_column is NULL.

e.g.

code:

SELECT
   a.*
FROM
   a
LEFT OUTER JOIN
   b
   ON b.foo_id = a.foo_id
WHERE
   b.foo_id IS NULL

something like that

# ? Jul 10, 2008 17:43

chocojosh: Jun 9, 2007; D00D.

My learning of SSIS goes from "Hmm.. this tool is pretty neat and I love the concept but they need to get the interface improved and a few bugs fixed" down to "why the hell can't I do something so basic?".

I have a small stored procedure that I have a few custom RAISERROR calls or that an error can be thrown when attempting to insert (constraint being invalidated). I want to get the error message so that I can e-mail a detailed error message to the business analysts (not developers) who can use the UI to make a quick change on production.

Attempt 1: Use an OLE DB Command and set the error to redirect row
Result: The row gets redirected but I can't access the error description. I can access an integer error code that I don't need.
I found this later on (after attempt 3) but it doesn't give detailed error messages. For a unique constraint failing it said something along the lines of "Integrity constraint failed".

Attempt 2: Use an OLE DB Command and set the error to fail component
Result: It would fail the component completely which we don't want (we want to attempt to insert as much data as possible and send error e-mails for those that fail. Also, if I add an onerror handler to send an e-mail, I would get nice e-mails about how a worker thread failed; a great thing to send off to a business analyst.

Alright.. I walked away and took a break for 20 minutes and then thought of another trick.. let's use a stored procedure output parameter.

Attempt 3: Use an OLE DB Command and use an error message output parameter with a conditional split
Result: Couldn't read the output parameter. I've read online reports that it does and doesn't work. I gave up after an hour and a half of playing with my stored proc and SSIS.

Attempt 4: Use google and hope like hell I can find something. I end up finding a blog post that describes a way to get the output parameter from a script task.
Result: I had to do some fiddling with the connections but it *worked* (for some reason my Script Task requires an ADO.NET connection whereas my OLE Sources / Destinations need an OLE destination to the exact same database).

Final solution:
However, I don't like using output parameters for error conditions -- that's why we have exception handling. Consequently, I decided to use an exception. Modifying the script just slightly I catch the exception in the script task and then set it to the read/write variable of the script task instead of using the output parameter.

In summary I've spent about 1.5 days just trying to answer the problem of "How can I send a nice error message to my business analyst/future developers so they know exactly what to fix". What a major pain in the rear end.

# ? Jul 10, 2008 21:12

No Safe Word: Feb 26, 2005

Not much to add except, "yeah, that sounds about right". :smith:

It's not my tool of choice, I just happen to be pretty well versed in it these days. Tomorrow's my last day with SSIS for a while though (new project at work!), huzzah.

# ? Jul 10, 2008 21:35

chocojosh: Jun 9, 2007; D00D.

No Safe Word posted:

Not much to add except, "yeah, that sounds about right".

It's not my tool of choice, I just happen to be pretty well versed in it these days. Tomorrow's my last day with SSIS for a while though (new project at work!), huzzah.

Well, thank you! I feel much better knowing that I'm not incompetent for spending a day and a half to figure out such a small thing (and luckily my boss is very laid back).

# ? Jul 10, 2008 21:58

Victor: Jun 18, 2004

Some hatred of SSIS, nicely packaged.

# ? Jul 11, 2008 00:14

Adbot: ADBOT LOVES YOU

# ? May 31, 2024 16:00

Super Mario Shoeshine: Jan 24, 2005; such improper posting...

sorry for being a pest but i need some advice on how to separate a char field like "QUAKE\QUIT\JOHN" to 3 differents fields. (I am trying to separate the names field on a database by surnames and names.

# ? Jul 11, 2008 17:59

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »