Table TimeRange
id binary(16),
startTime datetime,
endTime datetime,
isValid tinyint
There are two validation rules: starttime cannot be null, endTime cannot be null (assume that we cannot set the columns as NOT NULL).
We would like to OUTPUT an error record for each validation error for every record in the TimeRange table.
Would there be a single statement that could do this ? (ie. would UPDATE invalid record AND would OUTPUT two validation error records for a record that has startTime = NULL AND endTime = NULL)
Something like:
UPDATE TimeRange
SET isValid = 0
OUTPUT inserted.id,
CASE WHEN inserted.startTime is NULL THEN inserted.startTime
WHEN inserted.endTime is NULL THEN inserted.endTime
END -- Needs to handle the case where both startTime and endTime are invalid
INTO @InvalidRecords
FROM (a SELECT stmt that is a table with a record for each validation error)
MERGE does not have the functionality needed (inserting multiple records for every invalid record).
Have not had success using a UNION ALL, as there is an error updating derived tables.
How do I update a record that has duplicates. For example, I have 3612 orders some of these orders have multiple orderid's I want to update the record for each of these orders that was added most recently.
I am trying to create package something like that..
1- New Customer table as OleDB source component 2- Lookup component - checks customer id with Dimension_Customer table 3- And if same customer exist : I have to update couple fields on Dimension_Customer table 4- if it does not exist then I have insert those records to Dimension_Customer table
I am able to move error output from lookup to Dimension_Customer table using oledb destination but How can I update the existing ones? I have tried to use oledb command but somehow it didnt work my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)
P.no Product complaint1 comp_date1 complaint2 comp_date2 1 pen bad design 05/08/2008 null null 1 pen null null bad quality 05/18/2008 2 toy bad design 05/07/2008 null null 2 toy null null bad quality 05/19/2008
but i need ouput as follows
P.no Product complaint1 comp_date1 complaint2 comp_date2 1 pen bad design 05/08/2008 bad quality 05/18/2008 2 toy bad design 05/07/2008 bad quality 05/19/2008
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.
I'm not sure if I can explain this properly, but I'll try. I need to create an output file with a detailed record. I've been able to create a query that will give me the format and fields of what I need. However, the file layout calls for an additional "B" record if certain criteria is met in the detail immediately following the detail record. For example, if the PaymentMethodID = 10, then I need to add a "B" record that displays the PayeeName. I'm not sure if this would be a separate Select or a Union or maybe an Insert? All of our current layout are just Detail records and a Control Record.
I have a DTS package that outputs the contents of a view into a CSV file, however when the view has no records, an empty file is still created, is there any way to stop this.
I dont want the file to be created if the view has no records.
I have a sql statement that joins two tables and I get back a few thousand records when I run it in query tool in management studio.
But when I use SSIS merge join to join the two tables my output is 0 records.
I did sort the key column in both tables by setting 'sortkeyposition' property to 1 in advanced editor for output of both tables.
however the merge join returns nothing to my destination tables. Also I am doing a inner join. The task runs without error but returns nothing as well.. any ideas?
I have a table that have different groups in it, All the records that belong to one Group have the same GroupID and each GroupID has same columns. I need a query to generate the output in the attachment.
When the user input one GroupID then all the records belong to that group will pull out,
When the user input two GroupIDs then the user need to select another set of parameter (duplicate records or different records or nonoverlap records) to display
Intersect of the two group or different records or except records
when user input three GroupIDs then the user need to display the overlaps among all three groups or different among three groups or dinstinct records among the three groups
I've got a main report with five subreports. Based on a value of a parameter in the main report one the subreports is filled with data, all the other subreports will have no records. When the report is displayed in on the reportserver it is working fine, bit when I export the data to a CVS format, also the element names of the subreports are added to the CSV Output.
When i change the value of Data Output of the subreport item in the main report to Auto it doesn't export the records of the filled subreport.
How can I disable the export of the dataelement names in the CSV export?
Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic. Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.
How should the query be constructed, executed, and then the Count(*) value returned?
"WHERE <condition1> AND <condition 2> ;
"AND <condition 2> " may exist or may not exist in the query; it is dependant."
Hi there, I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks. Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this? User tableUserid Username 1 Bob2 Luke3 John Store procedure resultCompanyname Username Acrm Corp NullMaxwellarm Ltd Null Regards,
I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record, Table1 has the primary key id -- 1 2 3 4 Table2 has the follwing records id year subject ----------------- 1 2000 English 1 2002 French 2 2004 English 2 2005 English 2 2006 English 3 2007 French I want the result to be like this id 2000 2001 2002 2003 2004 2005 2006 2007 ----------------------------------------------------------- 1 English null French null null null null null 2 null null null null English English English null 3 null null null null null null null English
Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.
I have a SSIS package that dumps data from an internal table to a flat file output using standard data flow tasks. The entire table is output - no special SQL. Most of the time the records are placed in the output file in the same order as the internal DB table, but occasionally the order appears to be more random. When that happens, the record order in the internal table is correct - it is just the output.
I can find no properties that seem to affect this. I would appreciate any hints and advice that anyone can give me. Has anyone else encountered this same problem?
Hi and thanks for any advice.Right now I have a for loop that inserts multiple records. The first record is inserted into the database and I am not sure why. Here is the code I am using - Dim intPhotoKeyID As Integer Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc " InsertCmd += ") values " & _ "(@OriginalName, @GalleryID, @PhotoDesc " & _ ") " InsertCmd += "; SELECT CAST(scope_identity() AS int);"
Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString) Dim MyCommand = New SqlCommand(InsertCmd, DBConnection) MyCommand.Connection.Open() MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150)) MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int)) MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text)) myCount = myCount & i 'Is zero for first two records MyCommand.Parameters("@OriginalName").Value = "Orig" & i MyCommand.Parameters("@GalleryID").Value = 5
Try intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar()) MyCommand.Connection.Close() Catch Exp As Exception Response.Write(Exp) 'ResultsLabel.Text = Exp.ToString() End Try DBConnection.Close() Below is the whole procedure which either uploads an image or directory of images and resizes them. Then gets the meta data from the image. Then creates an entry into the database for each image. If I am dealing with 4 images then 4 images are uploaded to the new gallery folder. But 5 entries are added to the database. Thanks again for any help, Jennifer Protected Sub Upload(ByVal sender As Object, ByVal e As EventArgs)
If ListDirectories.SelectedValue.Length > 0 Or fileUpEx.HasFile Then Dim i As Integer Dim selectedDirectory As String = ListDirectories.SelectedValue Dim photoDescription As String Dim photoAuthor As String Dim photoTitle As String = "" Dim photoName As String Dim myFiles As String() Dim fileCount As Integer
If multiUpload.Visible = True Then myFiles = Directory.GetFiles(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/") fileCount = myFiles.Length - 1 Else fileCount = 0 End If
'Get last photo order from Photo table Dim PhotoMaxOrder As Integer = 0 Dim DS As DataSet ' DataSet object Dim SQL As String = "SELECT MAX(PhotoOrder) FROM Photos WHERE GalleryID=" & Request("ID").Trim Dim connString As String = ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString Dim sqlDA = New SqlDataAdapter(SQL, connString) DS = New DataSet sqlDA.Fill(DS, "Photos") If Not DS.Tables("Photos").Rows(0).Item(0) Is System.DBNull.Value Then If DS.Tables("Photos").Rows.Count > 0 Then PhotoMaxOrder = Convert.ToInt32(DS.Tables("Photos").Rows(0).Item(0)) + 1 End If Else PhotoMaxOrder = 1 End If
For i = 0 To fileCount
If multiUpload.Visible = True Then photoName = Right(myFiles(i), InStr(StrReverse(myFiles(i)), "/") - 1) Else photoName = fileUpEx.PostedFile.FileName End If
If InStr(photoName, ".jpg") > 0 Then Dim MyPhoto As Bitmap
If multiUpload.Visible = True Then MyPhoto = New Bitmap(myFiles(i)) Else MyPhoto = Bitmap.FromStream(fileUpEx.PostedFile.InputStream) End If 'testFile = myFiles(i) Try 'Get photo description Dim Make As PropertyItem = MyPhoto.GetPropertyItem("270") Dim ascii As Encoding = Encoding.ASCII photoDescription = ascii.GetString(Make.Value, 0, Make.Len - 1) Catch ex As Exception photoDescription = "" End Try
Try 'Get photo author Dim Make As PropertyItem = MyPhoto.GetPropertyItem("315") Dim ascii As Encoding = Encoding.ASCII photoAuthor = ascii.GetString(Make.Value, 0, Make.Len - 1) Catch ex As Exception photoAuthor = "" End Try
Dim photoXmpData As String = GetXmpXmlDocFromImageStream(MyPhoto)
If Not photoXmpData = "" Then photoTitle = GetXmpXmlNode(photoXmpData) End If
'insert photo record into photo table
Dim intPhotoKeyID As Integer Dim InsertCmd As String = "Insert into Photos (OriginalName, GalleryID, PhotoDesc " InsertCmd += ") values " & _ "(@OriginalName, @GalleryID, @PhotoDesc " & _ ") " InsertCmd += "; SELECT CAST(scope_identity() AS int);"
Dim DBConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnGalleries").ConnectionString) Dim MyCommand = New SqlCommand(InsertCmd, DBConnection) MyCommand.Connection.Open() MyCommand.Parameters.Add(New SqlParameter("@OriginalName", SqlDbType.VarChar, 150)) MyCommand.Parameters.Add(New SqlParameter("@GalleryID", SqlDbType.Int)) MyCommand.Parameters.Add(New SqlParameter("@PhotoDesc", SqlDbType.Text)) myCount = myCount & i 'Is zero for first two records MyCommand.Parameters("@OriginalName").Value = "Orig" & i MyCommand.Parameters("@GalleryID").Value = 5
Try intPhotoKeyID = Convert.ToInt32(MyCommand.ExecuteScalar()) MyCommand.Connection.Close() Catch Exp As Exception Response.Write(Exp) 'ResultsLabel.Text = Exp.ToString() End Try DBConnection.Close()
'check photo width and height Dim NewFilePath As String = Server.MapPath("/appscode/galleries/photos/g" & Request("ID").Trim & "/") & "p" & intPhotoKeyID.ToString & ".jpg" ' & photoName lblMessage2.Text = lblMessage2.Text & InsertCmd If MyPhoto.Width.ToString = "200" Or MyPhoto.Height.ToString = "200" Then 'just copy the image If multiUpload.Visible = True Then File.Copy(myFiles(i), NewFilePath, True) Else fileUpEx.SaveAs(NewFilePath) End If Else 'resize image Dim NewSize As System.Drawing.Size = New System.Drawing.Size(200, 200) ResizePicture(MyPhoto, NewFilePath, NewSize) 'and save it End If MyPhoto.Dispose() GC.Collect() End If
Next If Not myFiles Is Nothing Then If myFiles.Length > 0 And multiUpload.Visible = True Then 'delete directory that was just processed Directory.SetCurrentDirectory(Server.MapPath(".")) Directory.Delete(Server.MapPath(".") & "/tempimages/" & ListDirectories.SelectedValue & "/", True) ListDirectories.Items.Remove(selectedDirectory) 'lblMessage2.Text = "Your file(s) have been added." End If Else 'lblMessage3.Text = "Your file has been added." End If
Else lblMessage2.Text = "Please select a folder." End If
if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.
Now I want the records having flag2=1 only.. I.e ID=3 has flag2=1 where as ID = 1 and 2 has flag1 and flag3 =1 along with flag2=1. I don't want ID=1 and 2.
I can't make ID unique or primary. I tried with case when statements but it I am somehow missing the basic logic.
that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client
this does not seem like a very good solution ( or is it?) If posible it would be nice to get something like this
TermID, Term, RelatedTermsInformation 1 test RelatedTermsTwoDimentionalArray
but I am not sure how this idea could be implemented using the capabilities of SQL.
my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.
I have a situation where I need a table if bad items to match to. Forexample, The main table may be as:Table Main:fd_Id INT IDENTITY (1, 1)fd_Type VARCHAR(100)Table Matcher:fd_SubType VARCHAR(20)Table Main might have a records like:1 | "This is some full amount of text"2 | "Here is half amount of text"3 | "Some more with a catch word"Table Matcher:"full""catch"I need to only get the records from the main table that do not haveanything in the match table. This should return only record 2.
Hi,I have a stored procedure that has to extract the child records forparticular parent records.The issue is that in some cases I do not want to extract all the childrecords only a certain number of them.Firstly I identify all the parent records that have the requird numberof child records and insert them into the result table.insert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select t1.BatchNumber,t1.EntryRecordID,t1.LN,t1.AdditionalQualCritPassedfrom(select BatchNumber,RecordType,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Temp) as t1inner join(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) = max(TotalNumbers)) as t2on t1.BatchNumber = t2.BatchNumberand t1.RecordType = t2.RecordTypeand t1.EntryRecordID = t2.EntryRecordID)then insert the remaining records into a temp table where the number ofrecords required does not equal the total number of child records, andthenloop through each record manipulating the ROWNUMBER to only selectthe number of child records needed.insert into @t_QualificationMismatchedAllocs([BatchNumber],[RecordType],[EntryRecordID],[AssignedNumbers],[TotalNumbers])(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) <max(TotalNumbers))SELECT @QualificationMismatched_RowCnt = 1SELECT @MaxQualificationMismatched = (select count(*) from@t_QualificationMismatchedAllocs)while @QualificationMismatched_RowCnt <= @MaxQualificationMismatchedbegin--## Get Prize Draw to extract numbers forselect @RecordType = RecordType,@EntryRecordID = EntryRecordID,@AssignedNumbers = AssignedNumbers,@TotalNumbers = TotalNumbersfrom @t_QualificationMismatchedAllocswhere QualMismatchedAllocsRowNum = @QualificationMismatched_RowCntSET ROWCOUNT @TotalNumbersinsert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select BatchNumber,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Tempwhere RecordType = @RecordTypeand EntryRecordID = @EntryRecordID)SET @QualificationMismatched_RowCnt =QualificationMismatched_RowCnt + 1SET ROWCOUNT 0endIs there a better methodology for doing this .....Is the use of a table variable here incorrect ?Should I be using a temporary table or indexed table if there are alarge number of parent records where the child records required doesnot match the total number of child records ?
Looking to see if thier is a better way to find the last record entered in a group of records.
What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.
Table example using simplied data.
PolId
CoveragId
EffDate
Status
Limit1
2
1
9/7/2007
a
10000
2
2
9/7/2007
a
150000
2
2
10/1/2007
a
200000
3
1
9/7/2007
a
10000
The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.
The current query returns a single value. from a single table.
Current code used in the select protion on a larger query. bpi = basicpolicyInformation.
( Select c1.limit1 From AFW_Coverage as c1 Where c1.PolId=bpi.PolId and c1.CoverageId = (select max(CoverageId) as CoverageId From AFW_Coverage as c where c.PolId = c1.PolId and c.CoverageCode = 'Dwelling' and status <> 'D' ) and c1.effDate = (select max(Effdate) as Effdate From AFW_Coverage as c where c.PolId = c1.PolId and c.CoverageID = c1.CoverageId )
Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.
I€™m working on a database project that will ultimately contain millions of records for each lot. In addition, each lot will have up to 96 corresponding serial number records.
I would like to add a SQL parameter that would tell the database engine to only return X number of records.
For Example: If table TBL_LOTS contains one million records I would like to limit the return set to 100 for example.
What would I need to add to the SQL command to below to restrict the data set to the first 100 records in the set of one million?
SELECT [LOT NUMBER] FROM TBL_LOTS WHERE [STATMENTS]
I have a table that "Geography" that has the following columns: city, state, zip
There are tons of duplicate cities in this table. I ran this query and it shows me the number of occurrences of each city. I want to delete all the duplicates except for 1. I don't want to do this manually as there are a lot of records.
What would the SQL look like to delete the duplicate records but keep at least one?
I will be calling a stored procedure in SQL Server from SSIS. The stored procedure inserts records in a table by accepting input parameters. In the process, it also generates an output parameter that it passes as part of the parameters defined inside the stored procedure. The output parameter value acts as the primary key value for the record inserted using the stored procedure.
How can I call this stored procedure in SSIS? This is just one of the n steps as I will be extracting the output parameter generated by this stored procedure for the succeeding steps.