Wednesday, September 25, 2013

[T-SQL] Help required to extra image from varbinary column and create image on file system

[T-SQL] Help required to extra image from varbinary column and create image on file system

Help required to extra image from varbinary column and create image on file system

Posted: 21 Sep 2011 04:41 AM PDT

SQLServer 2008.Problem: I have a table populated by an external system which stores image data in a varbinary column. I need to create a trigger on the table so that on an insert to the table, the trigger fires and picks up the newly inserted record and extracts the varbinary data and outputs it to a physical image file with a name composed of the recordID + '.JPG' (or whatever graphic format is used) on the hard disk then I can write a row to another table with the filename. I don't mind if the generated file is a JPG or GIF or PNG although I would prefer it not to be a BMP due to the size of these.Creating the trigger is no problem - the issue I am having is extracting the data from varbinary column and creating the graphic file.I've tried googling for this and I can't find a complete solution anywhere. Ideally this needs to run on the SQLServer within a trigger so that it is fully automated.Can anyone help me with this?Many thanksCharlotte CB

Query Needed

Posted: 24 Sep 2013 09:33 PM PDT

Dear All Currently I am getting following result sets Registerno SubjectCode SubjectName Qno Marks12402223 171906 Quality And Reliability Engineering 1 412402223 171906 Quality And Reliability Engineering 2 1012402223 171906 Quality And Reliability Engineering 3 1112402223 171906 Quality And Reliability Engineering 4 512402223 171906 Quality And Reliability Engineering 5 914124223 170903 Power System Protection 1 314124223 170903 Power System Protection 2 414124223 170903 Power System Protection 3 614124223 170903 Power System Protection 4 314124223 170903 Power System Protection 5 1But I want following result sets Registerno SubjectCode SubjectName Qno1 Qno2 Qno3 Qno4 Qno5 12402223 171906 Quality And Reliability Engineering 4 10 11 5 914124223 170903 Power System Protection 3 4 6 3 1Please help me

Top Randomized Results with Multiple Qualifying Conditions

Posted: 24 Sep 2013 06:58 AM PDT

I found this to be an interesting exercise for the day. I've been assigned the task of picking the "winners" in a lottery, which must be random, but the aggregate results need to meet multiple criteria.It would be easy if there was just a "single" criteria .. you could just pick the top "n" lottery results until the criteria was met. But I need to consider multiple factors.And yes I can easily do this with what is described as "RBAR" ...The table:[code="sql"]CREATE TABLE #Ticket (TicketID NUMERIC(18,0) PRIMARY KEY, sRace CHAR(1), iAge NUMERIC(18,0), sParty CHAR(1))[/code]Some data (I have thousands of records for each lottery)[code="sql"]INSERT INTO #TicketSELECT '18986', 'B', '40', 'D' UNION ALLSELECT '39189', 'W', '63', 'R' UNION ALLSELECT '42223', 'B', '46', 'D' UNION ALLSELECT '44106', 'W', '82', 'R' UNION ALLSELECT '44365', 'B', '52', 'D' UNION ALLSELECT '45726', 'W', '42', 'D' UNION ALLSELECT '53323', 'W', '49', 'D' UNION ALLSELECT '58770', 'W', '70', 'R' UNION ALLSELECT '59624', 'B', '33', 'D' UNION ALLSELECT '59788', 'B', '36', 'D' UNION ALLSELECT '77297', 'W', '72', 'R' UNION ALLSELECT '81772', 'W', '66', 'R' UNION ALLSELECT '90966', 'W', '76', 'R' UNION ALLSELECT '95401', 'W', '56', 'R' UNION ALLSELECT '96760', 'B', '39', 'D' UNION ALLSELECT '101073', 'W', '68', 'R' UNION ALLSELECT '107167', 'B', '44', 'R' UNION ALLSELECT '108750', 'W', '57', 'R' UNION ALLSELECT '123544', 'A', '69', 'R' UNION ALLSELECT '124403', 'A', '44', 'R' UNION ALLSELECT '124937', 'W', '49', 'R' UNION ALLSELECT '126040', 'A', '56', 'R' UNION ALLSELECT '127882', 'A', '75', 'R' UNION ALLSELECT '128238', 'W', '26', 'R' UNION ALLSELECT '132748', 'W', '77', 'R' UNION ALLSELECT '133906', 'W', '35', 'D' UNION ALLSELECT '134248', 'B', '37', 'R' UNION ALLSELECT '136046', 'H', '43', 'D' UNION ALLSELECT '136253', 'W', '55', 'R' UNION ALLSELECT '138220', 'W', '52', 'D' UNION ALLSELECT '140297', 'B', '76', 'R' UNION ALLSELECT '140457', 'W', '36', 'D' UNION ALLSELECT '148863', 'B', '62', 'R' UNION ALLSELECT '148943', 'W', '69', 'R' UNION ALLSELECT '148959', 'W', '57', 'R' UNION ALLSELECT '151948', 'B', '64', 'R' UNION ALLSELECT '152141', 'B', '46', 'D' UNION ALLSELECT '153106', 'W', '53', 'R' UNION ALLSELECT '156206', 'W', '52', 'R' UNION ALLSELECT '160553', 'W', '60', 'R' UNION ALLSELECT '161406', 'B', '27', 'D' UNION ALLSELECT '161663', 'B', '25', 'D' UNION ALLSELECT '161987', 'W', '36', 'R' UNION ALLSELECT '162127', 'W', '61', 'R' UNION ALLSELECT '181421', 'B', '27', 'D' UNION ALLSELECT '181818', 'B', '42', 'D' UNION ALLSELECT '181954', 'W', '57', 'R' UNION ALLSELECT '182168', 'W', '66', 'R' UNION ALLSELECT '182292', 'I', '47', 'R' UNION ALLSELECT '182379', 'W', '44', 'R'[/code]So I can simply "lotterize" the winners with a CTE like this:[code="sql"]WITH lottery AS (SELECT TicketID, sRace, sParty, iAge, ABS(CHECKSUM(NEWID()))%100000+1 AS iRandom FROM #ticket )SELECT TOP 5 * FROM lottery ORDER BY iRandom[/code]**BUT**, I need to be assured that at least 80% of the "winners" have sRace = "W" **AND** 80% of the winners have sParty = "R"So considering this sample result set (it will of course vary each time you run the code) TicketID sRace sParty iAge iRandom181954 W R 57 282124937 W R 49 586696760 B D 39 7212133906 W D 35 9853140457 W D 36 1568459788 B D 36 19464108750 W R 57 20181101073 W R 68 20440161663 B D 25 20612123544 A R 69 2110080%+ (or 4+) must be sRace = "W" and 80%+ (or 4+) must be sParty = "R"Or logicially (and more importantly from a coding perspective) no more than 1 of each can be other than that.So the winners would have to beTicketID sRace sParty iAge iRandom181954 W R 57 282124937 W R 49 586696760 B D 39 7212108750 W R 57 20181101073 W R 68 20440

Advice on complex logic with embedded functions.

Posted: 16 Sep 2013 09:22 AM PDT

Hi,I have a scenario were under certain circumstances I need to use the earliest start date and then the latest finish date then work out days between these dates for records that need to be grouped together by a typeLatest finish date - earliest start date.In other circumstances, but using data from the same table I need to just use the start and finish date working out the days between without grouping by type.Finish date - start date.This determines the number of days another calculation needs to be greater than in order to qualify for my query. If the days between falls between certain ranges this gets me the days to be greater than called a qualifying period. The other calculation takes the start date, same as number 2 above and measures the days between.Actual finish - start dateI then check to see if this number of days is > the qualifying days.To determine if I should use just the start / finish date or the latest start / finish date I have a function that analysis about 10 parameters to work out what the rows type are first and then I can work out which method to use.All the information for parameters comes from three tables. All joined with inner joins.At the moment I feel I have functions embedded with functions and its not clean but messy.Just woundering how other people tacke complex scenarios like these. One option is I can pass in the rows unique id, have a select statement in a function get everything it needs and perform the calculation, but this feels I would be selecting from the same tables twice when I can use things like Max(date) over (Id) type logic.I could have a view with all the complex logic and self join using the Id mentioned above but this feels it would perfom not as well.Because the data is from 3rd party database, we cannot add indexes to improve performance or add application logic. I can select data only.Thank youEliza

Convert a single column into multiple rows

Posted: 24 Sep 2013 02:03 AM PDT

Helloi have a big task in front of mei have something like this in the text field. in a tableI have two columns Author ID and Descriptionthe description field is as followsDate: 09/04/2013 12:36:24 ReportName Person: Jack Jilll!!!!!!DD Name D/U R F PN!!!----------------------------------------------------------------------------------------------------------------------------------------!!!BookName1 100Pages Written Studymaterial!!!BookName2 200Pages Written Studymaterial!!!The Above is the whole text . To get this i had to concatenate 2 fields and replace Char(13) with !!!.All the other text is in the column value1 and columnvalue2.Now from this i need to put Starting with BookName1 to end of !!! in one row and then BookName2 in second row. for that Author IDSo it should it beAuthorID Description1234 BookName1 100Pages Written Studymaterial1234 BookName2 200Pages Written Studymaterial I used !!! to seperate which text gets into next row.I dont need anything that is before the BookName1Can you please suggest a way to do this.

Query help - summarize data with some complexities - TSQL gurus needed

Posted: 24 Sep 2013 01:45 AM PDT

I'm not exactly sure how best to tackle this. Below is the setup. I removed columns that are inconsequential to the problem and changed the column names and values, but are otherwise indicative of the actual data. Basically, need to generate dynamic descriptions based on a summary of data within a table called InvoiceLine[code="sql"]--CREATE TABLECREATE TABLE InvoiceLine (InvoiceNumber INTEGER, FranchiseLoc VARCHAR(20), Distribution VARCHAR(3))[/code][code="sql"]--INSERT SAMPLE DATAINSERT INTO InvoiceLine VALUES (259,'Coastal Carolina','MJB'),(259,'Coastal Carolina','MMA'),(259,'Coastal Carolina','NEA') ,(259,'Coastal Carolina','PVA'),(259,'Lexington','CC'),(259,'Lexington','HB'),(259,'Lexington','SS'),(259,'Lexington','WB') ,(248,'Ottawa','HA'),(248,'Ottawa','HB'),(248,'Lexington','500'),(248,'Lexington','501'),(248,'Lexington','AB') ,(248,'Lexington','AK'),(248,'Lexington','BP'),(248,'Lexington','CC'),(248,'Lexington','HB'),(248,'Lexington','ILX') ,(248,'Lexington','MF'),(248,'Lexington','SS'),(248,'Lexington','WB')[/code][code="sql"]--Pulling everything from the table show 21 rows--259 and 248 are distinct values for InvoiceLineNumber--from which a summary needs pulledSELECT * FROM InvoiceLine[/code]Given above data, this is what they want the resultset to look like:[code="sql"]SELECT 259 AS InvoiceLineNumber,'Coastal Carolina | Lexington' AS [Description], 'Coastal Carolina: MJB, MMA, NEA, PVA | Lexington: CC, HB, SS, WB' AS DescriptionDetailUNIONSELECT 248,'Lexington | Ottawa','Lexington: 500, 501, AB, AK, BP, CC, HB, ILX, MF, SS, WB | Ottawa: HA, HB'[/code]Basically, put unique FranchiseLoc values for a given InvoiceLineNumber into a single column called Description in a row separate by a vertical pipe. That alone isn't too difficult, but for DescriptionDetail, it gets a little crazier. Put unique FrancechiseLoc values for a InvoiceLineNumber into a single column called DescriptionDetail, but add unique distributions per FrancechiseLoc after a colon. This is where I'm getting a bit lost.Any help/thoughts appreciated. Let me know if any questions.

Ignore Error in T-SQL

Posted: 24 Sep 2013 01:12 AM PDT

Hi,How do we ignore an error in T-SQL? For e.g.:- The following code throw an error once the @lCounter reach at 15 and will come out. Requirement is it should go till 1000. Even if there are errors in between.[code="sql"]DECLARE @lCounter SMALLINTDECLARE @lError SMALLINTSET @lCounter = 0WHILE (@lCounter <= 1000 )BEGIN SELECT CONVERT(VARCHAR, GETDATE(), @lCounter) SET @lCounter = @lCounter + 1END[/code]Thanks in advance
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog