CREATE TABLE [x_Note] (
[x_NoteId] [int] IDENTITY (1, 1) NOT NULL ,
[Note] [varchar] (7200) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOT
NULL ,
CONSTRAINT [PK_x_NoteId] PRIMARY KEY CLUSTERED
(
[x_NoteId],
) WITH FILLFACTOR = 90 ON [USERDATA] ,
) ON [USERDATA]
GO
My clients want me to take the contents of the Note column for each row
and combine them. In other words, they basically want:
Note = Note [accumulated from previous rows] + Char(13) [because they
want a carriage return] + Note [from current record].
What is the most efficient and relatively painless way to do this? I
think it might require a cursor, but I'm not sure if there is a more
elegant set-based method to make this happen.
I have 2 tables First is Student_detail and another is Employee_detail. Student_detail have 14 fields like (stud_Firstname,stud_Lastname...) and Employee_detail have 17 fields like(emp_Firstname,emp_Lastname...).there is no relationship between these two table and also not in a relationship with any other table in my database.This is a structure of my db. but i want to get the records from these two table whose first name is same for both the tables.as well as the result of this query will first show me Student_detail record first and then Employee_detail record.but not in a one row.it should be display in one by one.
How to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields.
The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected.
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.
This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.
I need to return one record with concatenated string fields from a table that may contain several records. I think a cursor will be able to do what I want, but I'm not very experienced at writing them.
My data
HDR DMCD 107 TEX 107 AIR 107 LG 108 TEX 108 CAR 109 SM
I want the result of my query to find adn return each header and return the 1 or more DMCD field values concatenated. i.e.
Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!
I'm working on a report where my table is as follows:
WITH SampleData (ID,NAME,[VALUE]) AS ( SELECT 170983,'DateToday','6/04/2014' UNION ALL SELECT 170983,'DateToday','6/04/2014' UNION ALL SELECT 170983,'employee','1010' UNION ALL SELECT 170983,'employee','1010'
[Code] .....
Here is my query against the table above:
SELECT ID ,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON ,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE ,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE ,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE ,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS ,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):
I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query: -------- select distinct 'On-Demand' as 'Business Line', o.OrderID as 'Order #', isnull(d.DisplayCode,'UNK') as Hub, isnull(rz.RouteID,'UNK') as 'Default Route', 'On-Demand' as 'Assigned Route',
When I try and create the following stored procedure, I get the following error message: Any ideas as to what went wrong? Here is the stored procedure USE [DBS07]GO/****** Object: StoredProcedure [dbo].[updateMarketName] Script Date: 09/17/2007 22:28:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[updateSubMarketName]( @inMarketId int, @inSubMarketId int, @inSubMarketDescription nvarchar(100), @inActive nvarchar(2), @inLastUpdateDate datetime, @inLastUpdateUser nvarchar(100))AS SET NOCOUNT OFF;UPDATE [SubMarket] SET [SubMarketDescription] = @inSubMarketDescription, [Active]= @inActive, [LastUpdateDate] = @inLastUpdateDate,[LastUpdateUser] = @inLastUpdateDateUserWHERE ([MarketId] = @inMarketId)AND (SubMarketId]= @inSubMarketId) Here is my error message Error message in Red:Msg 137, Level 15, State 2, Procedure updateSubMarketName, Line 12Must declare the scalar variable "@inLastUpdateDateUser".
I have one procedure that gets executed many times per day(thousands at least). I consistently get blocking in my database, as users compile this stored procedure. How can I keep this from recompiling all the time, and bogging down my database? I tried the keep plan option in the portion of the code that uses tembdb, and that doesn't work. thanks.
To all, When a stored proc is executed in SQL server 2000, it is holding a EXCLUSIVE [COMPILE] lock on the proc and the proc os getting recompiled every time it is executed. This is happening with most of the procs that are called from this proc. When multiple users are executing the same process they are having to wait until the other users are done compiling the procs. The lapse time is growing exponentially with multiple users.
I have looked at several places to find a solution for this. Microsoft Articles Q243586 and Q263889 have provided me with some options; but at this point, I need a miracle.
All these procs users temp tables. I have got the code changed to replace most of them with Table datatypes (on SQL2K only) . Some of them still need to use temp tables as they are cross referenced by multiple procs.
I am hopeful, there is some one out there who has dealt with this kind of situations before. Any ideas/sugessions are greatly appreciated..
This morning one of our jobs failed, so I eventually ran the SSIS package manually and found the flow "hanging" at a script task. The fix was to open the script in design mode and hit Save, which I believe compiles the code. Then the package ran as it has normally for several months.
There was a recent Windows update run on this server. I don't know what was updated, as it was the DBA that did that. It seems possible that a .NET framework update would cause this problem, does anyone have any thoughts on this or anything else causing this?
Given the extent of some SSIS environments, and ours is pretty extensive, this could be a real pain to go in and manually recompile each and every script task.
I have a custom application that on occasion requires thousands of TSQLfiles (on the file system) to be compiled to the database.What is the quickest way to accomplish this?We currently have a small vbs script that gets a list of all the files,the loops around a call to "osql". each call to osql opens/closes aconnection to the destination database (currently across the network).
Hello!I am trying to investigate strange problem with particular storedprocedure. It runs OK for several days and suddenly we start gettingand lotof locks. The reason being [COMPILE] lock placed on this procedure. Asaresult, we have 40-50 other connections waiting, then next connectionusingthis procedure has [COMPILE] lock etc. Client is fully qualifyingstoredprocedure by database/owner name and it doesn't start with sp_. I knowthese are the reasons for [COMPILE] lock being placed. Is theresomethingelse that might trigger this lock? When troubleshooting this issue, Inoticed there was no plan for this procedure in syscacheobjects. Thestoredprocedure is very simple (I know it could be rewritten/optimized butourdeveloper wrote it):CREATE PROCEDURE [dbo].[vsp_mail_select]@user_id int,@folder_id int,@is_read bit = 1, --IF 1, pull everything, else just pull unread mail@start_index int = null, --unused for now, we return everything@total_count int = null output, -- count of all mail in specifiedfolder@unread_count int = null output -- count of unread mail in specifiedfolderASSET NOCOUNT ONselect m1.* from mail m1(nolock) where m1.user_id=@user_id andfolder_id=@folder_id and ((@is_read=0 and is_read=0) or (@is_read=1))orderby date_sent descselect @total_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and ((is_read=0 and@is_read=0)or (@is_read=1))select @unread_count = count(mail_id) from mail m1(nolock) wherem1.user_id=@user_id and folder_id=@folder_id and is_read=0GOI was monitoring server for a couple of day before and I am not surewhythis happens every 3-4 days only!Any help on this matter would be greately appreciated!Thanks,Igor
I have a requirement to do a data migration work for a Japanese client. The problem is I don't know Japanese and the client SME does not know English. So, my requirement is, I should be able to create a SSIS package in English and then should be able to compile or translate it into Japanese so that my Japanese SME can understand the package. Is it possible to do? If so, how can I do it?
actually i try to create a native c++ desktop-application that will have to access a SQL CE 3.1 db-file over OLE DB. For that purpose i want to use the same self written wrapper class(for the OLE DB stuff) that i use for a native WM2003 application that creates the db files to be accessed on the desktop application. The WM2003 application including my wrapper class compiles fine with eVC++ 4.0. But now with VS 2005 including the same class i got this compile error: "Redefinition of class IRowsetBookmark". I have to add that i used the application wizard to generate a mfc sdi application with database support(ODBC is used in this app, too). I figured out that in the following 2 inlcuded files IRowsetBookmark is defined: 1)The manually included "ssceoledb30.h" in my wrapper class. 2)The probably from the application wizard included "oledb.h" (in "Microsoft Visual Studio 8VCPlatformSDKInclude")
My workaround for the moment is to comment the class definition in the file "ssceoledb30.h" out because i don't use this OLE DB interface in my wrapper class. But i don't think that this is the way that microsoft supposed to go.
Does anybody had this problem and solved it other way than simply comment one of the class definition out?
I would appreciate it when someone out there knows the answer and could tell me.
The stored procedure, below, results in this error when I try to compile...
Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'.
However the select statement itself runs perfectly well as a query, no errors.
The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.
What gives with this?
Thanks in advance R.
The code:
Code Snippet
-- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80);
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! );
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items> from A left join B on B.id_A = A.id left join C on C.id_B = B.idthis now looks like select <many items> from A left join (B join C on C.id_B = B.id ) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
When I install the SQL Server 2005 SP2 on my vista laptop, I get the following message: SQL server setup failed to compile te Managed Object Format (MOF) file c:program filesmicrosoft SQL Server90sharedsqlmgmproviderxpsp2up.mof. To proceed, see "troubleshooting an installation of SQL Server 2005" or "how to: View SQL Server 2005...
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
