I'm inserting records from a table to another table, having multiple columns
SampleQuery :
insert into tableB (col1,col2....col10) select col1,col2....col10 from tableA
* while executing this query im getting error. (like varchar cant convert to numberic) * Here I have no clue in which column it is * also dont know which row causes it
I have a small project to be done in which I need to fetch the pdf file from a my system and save it in database and also fetch the name of it and save it in the database.
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8 DECLARE @DetailQuestionID INT = 1380 /*------- I need the query to run for the below three data. Here i'm updating my planids that already exists in my database*/ DECLARE @planids VARCHAR(MAX) = '2,4,5'
I need to pull all records from the Item table and then I need to populate the most recent OrderNo and O.DateCreated. I got this far but if there is a part in the item table that does not have an order against it, I do not get a value and my goal is to see any parts that have not been ordered in the last year. Something like this:
SELECT I.PartNumber, I.Description, I.DateCreated FROM item I CROSS APPLY (SELECT TOP 1 O.OrderNo, O.DateCreated FROM Orders O WHERE O.PartNumber = I.PartNumber ORDER BY O.DateCreated DESC) O PartNumberOrderNoO.DateCreated 1A1XXX 1CHXX1 1/8/2014 2A2XXX 1CHXX3 1/20/2014 3A3XXX NULL NULL 1B1XXX 2CHXX1 2/10/2014 2B2XXX 2CHXX3 2/22/2014 3B3XXX NULL NULL
I have query with an expensive Key Lookup on a joined table. The predicate is the column that I'm joining on, and the output list contains two columns from the joined table.
I've created a basic non-clustered index covering the predicate column and include-ing the two output columns. However, the execution plan ignores this, and insists on using the primary key of the joined table to do the expensive key lookup. I've tried adding the included columns to the index directly and there's no change. I've also tried running dbcc freeproccache and no change.
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table - Unable to modify table. Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails. The statement has been terminated.
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
I have an SSIS job that is pumping to a SQL Server Destination, hundreds of gigabytes of raw text files. Today I received this strange error - does anyone have insight? Also, how would I make the data tasks more stable and robust so that this doesn't cause package failure (retries, or something?)
[SQL Server Destination [4076]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
I have a procedure in Oracle that contains the following cursor:
CURSOR SCHED_TRIPS IS SELECT TRAVELDATE, STOP_NUM, TRIPID, STOP_TYPE, PROMISED_TIME, ETA, PERFORM_TIME, DEPART_TIME, ETD, DRIVERWAIT, PASSENGERWAIT, TRIPTIME, GROUP_ID FROM Dbo.SCHEDTRIPS_VIEW WHERE UNQ_ID = SESSION_ID AND TRUNC(TRAVELDATE) = TRUNC(TDATE) AND DISPOSITION <> 'V'; BEGIN FOR S IN SCH_TRIPS LOOP UPDATE dbo.SCHEDULES T SET T.DIRTYBIT = 1 WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE AND (T.STOP_NUM <> S.STOP_NUM OR T.ETA <> S.ETA);
UPDATE dbo.SCHEDULES T SET T.STOP_NUM = S.STOP_NUM, T.PROMISED_TIME = S.PROMISED_TIME, T.ETA = S.ETA, T.ETD = S.ETD, T.LAST_CHANGED = SYSDATE WHERE T.TRIPID = S.TRIPID AND T.STOP_TYPE = S.STOP_TYPE; END LOOP; COMMIT ; END;
My problem is with the line shown in Red. What will be the T-SQL equivalent for this line.
I want to fetch max of Field2 if duplicate records in Field1 and rest of the values of field1 , below is the sample format.
Field1 Field2 Field3 Field4 32 375 abc-xyz A 32 379 xyz-efg A 55 405 abc-xyz B 55 407 xyz-efg B 132 908 abc-xyz C 132 999 xyz-efg C 152 800 abc-xyz D 152 850 xyz-efg D 155 900 abc-xyz E 156 925 abc-xyz F 157 935 abc-xyz G
I am using the open query method to connect a Oracle server. Below is my code to connect to oracle,when I execute the same query in oracle it fetches 199 rows whereas in Sqlserver it returns only 66 rows. I have tried only one record based on id..sqlserver query returns 0 rows..whereas the oracle returns 4 rows..Can some one tell me what will be the problem
SET QUOTED_IDENTIFIER OFF declare @sql varchar(750) select @sql = "SELECT * from openquery(PTTSTATUS," + '"' + "SELECT A.PROJECT_ID,C.STATUS_NAME ,A.CNUMBER FROM PTT.PTT_PROJECT A, PTT.PTT_STATUS C WHERE (C.STATUS_NAME IN ('Closed', 'Cancelled')) AND A.PROJECT_STATUS_ID = C.STATUS_ID AND A.CNUMBER is not null ORDER BY A.CNUMBER " + '")' EXEC (@SQL)
Below is a XML column data. How to get the Id and respective Names for "Case Manager" Dropdown ONLY in SQL server 2008. I don't want to get anything related to "Intake Staff" drop down.
I'm importing from an Excel spreadsheet into an SQL Table using DTS.
Two things:
1. I don't want anything imported into Column 1 of the table as this has been designed to automatically increment (creating a primary key). I haven't worked out how to do this.
2. How can I get the DTS package to ignore the top 2 rows of the Excel spreadsheet (header information)?
An answer to one will be great but both will be tremendous!!
I have a problem with a view in MS SQL Server 2000. The View concatenates 3 fields (prefix, partnumber, suffix) using hte "+" operator and one or more of these fields may sometimes be null. The problem is that a null value in any of the three fields causes the concatenation to return null even if there are valid values in on or both of the other fields. I thought I might be able to work around this by creating a view containing CASE statements to render the null values as zero length strings, which concatenate properly (e.g., "SELECT CASE WHEN PREFIX IS NULL THEN '' ELSE PREFIX END 'prefix2' FROM [table_name]"). But SQL server will not let me save a view containing a CASE statement. Anybody know how to resolve this problem?
By the way, I also tried to use UNION views to work around this but SQL server 2000 will not let me save views with UNION sataments even though it runs them properly when views with UNION statements that were created in SQL server 7.0 are imported. What's up with the inability to save a view just because it can't be rendered in the gui pane of the query builder??
I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.
I have a temp table named getInvoice where I have five important columns
1. invoice number 2.group 3.invoice status 4. Invoice Expiration date 5. Creation date time
and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.
An example is below :
InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime
My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.
The query I wrote is below
declare myData cursor for select distinct invoiceNumber from #getInvoice declare @invoiceNumber varchar(30) open myData fetch next from myData into @invoiceNumber while @@FETCH_STATUS = 0
I have an SSIS job that is pumping to a SQL Server Destination, hundreds of gigabytes of raw text files. Today I received this strange error ? Also, how would I make the data tasks more stable and robust so that this doesn't cause package failure (retries, or something?)
[SQL Server Destination [4076]] Error: An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.".
So I have this query that is ignoring my date filter and for the life of me I can't figure out why. Was hoping some guru could explain it to me. Here goes:
This query ignores my date filter:
SELECT rcv.Name AS MachineName, r.JobId, j.Name AS JobName, r.CreateTime AS JCreateTime, rsv.Name AS JResultStatus, rpv.Path + rpv.Name AS ResourcePool, rcvv.ResourceConfigurationVal AS Dimension
FROM dbo.Result_View AS r INNER JOIN dbo.ResourceConfiguration_View AS rcv ON r.ResourceConfigurationId = rcv.Id INNER JOIN dbo.Job_View AS j ON r.JobId = j.Id INNER JOIN dbo.ResultStatus_View AS rsv ON r.ResultStatusId = rsv.Id INNER JOIN dbo.Resource_View AS rv ON rcv.ResourceId = rv.Id INNER JOIN dbo.ResourcePool_View AS rpv ON rv.ResourcePoolId = rpv.Id RIGHT JOIN dbo.ResourceConfigurationValue_View AS rcvv ON rv.LatestResourceConfigurationId = rcvv.ResourceConfigurationId LEFT JOIN dbo.Dimension_View AS d ON rcvv.DimensionId = d.Id
WHERE (r.CreateTime > DATEADD(DAY, -15, GETDATE())) AND (rcv.Name LIKE 'PNP%') AND (d.Id = 859) OR (d.Id = 860)
If I comment out the last two joins and associated select/filters, all of the sudden the date filter works again. From everything I have read, the joins are supposed to be processed BEFORE the filters are applied in the virtual table. My DB goes back a number of years and contains millions of records. W/O the date filter, the query takes a very, very long time to run.
I have just transferred my site to a new server with SBS R2 Premium, so the site's database changed from SQL 2000 to SQL 2005. I find that searches are now returning results in random order, even though they use a view with an Order By clause to force the order I want. I find that the results are unordered when I test the view with Management Studio, so the issue is unrelated to my VB/ASP Net code. Using my SQL update tool (SQL Compare, from Redgate) I find that there are no differences in the views, or the underlying tables. Using Management Studio to test a number of views, I find that I have a general problem with all views. For example, one of the simpler views is simply a selection of fields from one table, with an Order By clause on the tables primary key: - SELECT TOP (100) PERCENT GDQid, GDQUser, GDQGED, GDQOption, gdqTotalLines, GDQTotalIndi, GDQRestart, GDQCheckpointMessage, GDQStarted, GDQFinished, gdqCheckpointRecordCountr FROM dbo.GEDQueue ORDER BY GDQid DESC If I right-click the view (from Management Studio's Object Explorer pane), select Design from the menu to show the view's design, and then click the Execute SQL icon, the view's results are displayed perfectly, in descending order of GDQid. However, if I select "Open View" the view's results are displayed out of order. When I do this with the SQL 2000 database, both Design/Execute and Open View correctly display the data in the correct order. Is there something that I should check in the SQL 2005 installation - some option that has been set incorrectly? Regards, Robert Barnes
I am using the SqlDataSource to access the dB from my page. Basically this is what I do with it ds.SelectParameters.Clear(); ds.SelectParameters("Id", TypeCode.Int32, id.ToString());
DataSourceSelectArguments dssa = new DataSourceSelectArguments(); dssa.MaximumRows = 1; dssa.AddSupportedCapabilities(DataSourceCapabilities.Page);
DataView dv = (DataView)ds.Select(dssa); if (dv.Count > 0) { // collect the information string title = (string)dv[index].Row.ItemArray[0]; } And the SelectCommand attribute of the SqlDataSource is set in design mode to "SELECT * from vw_Items ORDER BY Category". So, since I am trying to retrieve just the item with the given Id I was expecting just one record but when I step through I see that the data view has a count of 9 (all records in the table) !!! What am I doing wrong here? why can't it return just one? as per the select statement which after adding the parameter should be something like "SELECT * FROM vw_Items WHERE ID = 5 ORDER BY Category
If you have a table called Person and has ID, Age, Gender and Name. And you have a stored procedure: CREATE PROCEDURE [dbo].[GetName]@Age TINYINT,@Gender TINYINTASBEGIN SELECT Name FROM Person WHERE Age = @Age AND Gender = @Gender END You want some times to send @Age as null to get all ages (or in other words, you are not concerned with the age) or @Gender as null to get all genders. A way to do that is to build your query string dynamically and execute it with EXEC command. However, this is a bad solution because you are losing most of the SP benefits and writting longer and more complicated procedures, specially that you have a lot of parameters! Another solution that I thought of is having your select like this: SELECT Name FROM Person WHERE Age = ISNULL(@Age,Age) AND Gender = ISNULL(@Gender, Gender) However, I noticed that when the value of the column is NULL, you will get false results, obviously because you are using = operator in comparing a value with null while you should be using IS operator, so this method did not work. I am wondering if any one has a good solution for this. Adam Tibi
In our schools we have a number of East-European, Turkish, Scandinavian, ... students. Their names contain "special" characters, like Ö, Ü, Ø, ... Our users want to be able to search for student names without having to enter those special characters. Most often they don't know the exact spelling of the names and they get "no match found" messages as a result.
They want to have persons with the name Ösgür, Osgueld, ... in the result set after entering "osgu" in the search field.
What is the best way to do this? I was thinking about using another collation near the LIKE, but I don't know if that would work and how it should be done. The Database collation is Latin1_General_CI_AS.
Hi, I am using SQL Server 2000. In database i am having one column named Address which contains full address of the customer. While searching i want to ignore starting numeric or alphanumeric values. Kinly guide how I can ignore numeric or alphanumeric values while searching the data.
Hi, I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that.
Thanks in advance, John select part from transactions T where (T.transdate between '20070701' and '20070705') and (T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part
Here is the beginning of the Transactions table create script
CREATE TABLE [Transactions] ( [RecNo] [int] IDENTITY (1,1) NOT NULL, [Part] [nvarchar] (30) NOT NULL , [TransDate] [nvarchar] (8) NOT NULL , [TransType] [nvarchar] (4) NOT NULL , [FromLoc] [nvarchar] (10) ,
The 'Part' column is an alphanumeric field. The problem I am having is that the Order By seems to ignore the hyphen character '-' when the returned rows are ordered by the Part (which can contain hyphens in any column).
Here is an example of what I get.
130909N9 130909N9 130909N9 1-480698-0 * These two should not be here 1-480699-0 * 15-423 164-07700 164-07700 164-07700 1683
I was expecting this ( and I get this in and older database ).
068-03000 068-03000 06A19956 074-03200 077-367-0 08DU08 1-480698-0 * These should be here eariler in the data 1-480699-0 100-364072 100-364072
This may be a silly question out of ignorance, but I'm working in an environment where I am a DBA for a server, but I'm not a local admin on the windows server for the first time, so sorry if it is
Anyway, I have set up some traces that are writing to a file stored in a directory I have full control on. However, the trace files themselves are not inheriting the folder permissions, even though the folder is configured to do so. The only logins with access are the Local System account, the SQL Server service account local group, and the local administrators group.
Is this something that SQL Server does on its own, or would this have to be something the system admins have set up with group policy or something? If its SQL Server, is there some configuration setting I can change to get it to stop doing that so I can stop bugging the admins to give me access every time I run a trace?
