This one has bothered me ever since sql server 2000.
When you do an insert into a table with literally hundreds of char or varchar columns and you get the error that the insert failed due to data loss/truncation on a column.
Is there anyway in 2005 to actually find out what column ? Since there are hundreds is is literally a long process of going though each column 1 by 1 manually.
The database engine surely MUST know what column this occurred on so why can it not tell you which column the truncation occurred on ?
Can this be done in 2005 if not will this information be available in 2008 ?
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I have a huge db with many services ,users and applications hitting the db. Suddenly one of our column is nullified , we are not able track who /how it is done,
Can any one tell be whatz the best way to identify this???? trace(what events to select ), trigger or what????
Okay, I now have some dynamic SQL working. This is the SQL statement I have for a report in Reporting Services:
DECLARE @SQL nvarchar(4000)
SET @SQL=(SELECT AdHocSQL
FROM RptValueTypeMap
WHERE RptValueTypeMap.SectionCd in ('ITEM0010'))
EXECUTE (@SQL)
We have a table set up that actually holds different SQL statements based on the report items. This is reading the SQL statement from AdHocSQL for the Report item #0010 and it is returning the results. However, it does return the correct value, but under (No Column Name). I have tried to incorporate an "AS", but I get errors when I try this.
I am familiar, but new to SQL statements and I would like this to return a field so I can use this value in the report. What do I need to do?
I don't wnat SQL's Identify column's format ( which is 1�2�3 ...)I want my Prikey column is looks like starts in 0000000001�0000000002�0000000003....I set the Prikey columns type is Char(10) not nullis it possible to setting my identify column as I want?
Hi, I want to know how to remove identify property from a column without recreating the whole table... When I do it in Enterprise Manager, it actually drop and recreate the table in background. I just like to know if there is other way without recreating the tables. Thanks! Xiao Tan
DECLARE @EffLevels TABLE (ChangePoint int, Value Int)
INSERT@EffLevels SELECT'1000', '767' UNION ALL--Changed SELECT'1000', '675' UNION ALL SELECT'1001', '600' UNION ALL--Changed SELECT'1001', '545' UNION ALL SELECT'1001', '765' UNION ALL SELECT'1000', '673' UNION ALL--Changed SELECT'1002', '343' UNION ALL--Changed SELECT'1002', '413' UNION ALL SELECT'1002', '334' UNION ALL SELECT'1001', '823'--Changed
-- My Result should be -- ChangePointPrevChangePointValue -- 1000Null767 -- 1001 1000 675 -- 1000 1001 765 -- 1002 1000 343 -- 1001 1002 823
I have two columns, one column has a document ID and a given document can have many pages. The second column has the pages. Now I want to find out when the page number is broken. For example, if doc ID 1 has 3 rows and each of the three has 1,2,3 and then the fourth row has document 1 but the value jumps from 3 to 7 and then goes to 8,9,10 and then jumps again and starts from 17, i want to have the ranges identified.
DocID Page Number 1   1 1 2 1 3 1 7 1 8 1 9 1 10 1 17 1 18 1 19 1   20
I have a data file that has numeric data that looks like:
1.123456
And this column is defined as a DT_NUMERIC(18.6) in the flat file conn mgr.
As an experiment, I changed the destination column to a NUMERIC(18,0) - hoping that this would throw a truncation error at the flat file task level (where I have Truncation on all columns set to "fail component").
Not a peep. It loaded the data into the table, chopping off the 6 digits after the decimal point.
You would THINK that this would cause an error, but no. Why is this? The flat file task complains about all kinds of things, but this is such a gross error, you would think it would catch it!
I have a table with Million plus records. Due to Running Totals article, I have been able to calculate the Trial_Balance for all months.
Now I am trying to provide a Beginning Balance for all months and the Logic is the Beginning Balance of July would be the Trial_Balance of June. I need to be able to do this for multiple account types. So the two datasets that need to be included in logic is actindx and Calendar_Month.
For actindx of 2 and Calendar_Month of 2014-01-01The Trial_Balance_Debit is 19585.46 This would make the Beginning_Balance of actindx 2 and Calendar_Month of 2014-02-01 19585.46
I am trying to do some type of self join, but not sure how to include each actindx number differently.
Table creation and data insert is below.
SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[TrialBalance]( [Trial_Balance_ID] [int] IDENTITY(1,1) NOT NULL,
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
Using MDS 2012: I have an entity "XYZ_Entity". In "XYZ_Entity" entity I have 2 domain based Columns "DealerGroup" and "Dealer".
While inserting information into "XYZ_Entity" entity user can select the required dealer group from domain base Dealer Group values. Now for selecting Dealer he wants the dealers to be filter based on selected dealer group and he can select from the filtered list. reason to do that is he don't want to go through thousands of dealers and select an incorrect one.
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data. For example:
I have a sql query which bring data of 3 columns
ID | FISRT NAME | LAST NAME 1 MIKE MORGAN 2 SARA JOHANES
So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.
Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.
Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column. So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it? Hope I was clear Thanks for your help.
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
Hi everyone, I am using SSIS, and I got the folowing error, I am loading several CSV files in a OLE DB, Becasuse the file is finishing and the tak dont realize of the anormal termination, making an overflow. So basically what i want is to control the anormal ending of the csv file. please can anyone help me ???
I am getting the following error after replacing the '""' with '|'. The replacng is done becasue some text sting contains "" wherein the DFT was throwing an error as " The column delimiter could not foun".
[Flat File Source [8885]] Error: The column data for column "CountryId" overflowed the disk I/O buffer. [Flat File Source [8885]] Error: An error occurred while skipping data rows. [DTS.Pipeline] Error: The PrimeOutput method on component "Flat File Source" (8885) returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
[DTS.Pipeline] Error: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0047039.
[DTS.Pipeline] Information: Post Execute phase is beginning.
Hi there, I am a new member of this site and I am not very much aware of T-sql's working. My question is what if I need to get one column's data to be the heading of another column. To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this StuID - - - SubID - - - -Tot - - -Obt 1 - - - - - - -1 - - - - - - -50 - - - 38 1 - - - - - - -2 - - - - - - -50 - - - 41 1 - - - - - - -3 - - - - - - -50 - - - 42 1 - - - - - - -4 - - - - - - -50 - - - 40 2 - - - - - - -1 - - - - - - -50 - - - 35 2 - - - - - - -2 - - - - - - -50 - - - 40 2 - - - - - - -3 - - - - - - -50 - - - 42 2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4 1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40 2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted. Thanks.
in Table A Column is PriKey No Name 1 1 Apple 2 2 Orange 3 3 Juicy in Table B column is Prikey ColumnA Price 1 1 10 2 3 2 3 2 5
TableA.Prikey have Depency with TableB.ColumnA
when I am trying to Delete data from Table A , I got error message becaue the depency how to delete data when there have depency?
I just know when table have trigger , we can disable trigger before delete Data, and enable trigger when data deleted does there have a way to disable depency and then enable ?
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A B C D E F G H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
Hi, I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time. thanks. varun
need some help converting a table which has one row for each year. Displaying the data so the each row will contain all of the information for the title and all of the years displayed in separate columns.
I have a SQL Querry which returns the data looking like this (the querry is grouping by Title and by year, then adding up the quantity of rows):
Basic Information needed for Life Insurance 25 2005 Basic Information needed for Life Insurance 45 2006 Ea Chairmans Conference 10 2005 EA Chairmans Conference Press Release 33 2005 EA Chairmans Conference Press Release 21 2006 EA Chairmans Inner Circle Press Release 16 2005 EA Chairmans Inner Circle Press Release 46 2006 EA Honor Ring Press Release 13 2005 EA Honor Ring Press Release 35 2006 EA National Conference Press Release 6 2005 EA National Conference Press Release 9 2006
I need this data displayed like this:
Title 2005 2006 Basic Information needed for Life Insurance 25 45 Ea Chairmans Conference 10 10 EA Chairmans Conference Press Release 33 21 EA Chairmans Inner Circle Press Release 16 46 EA Honor Ring Press Release 13 35 EA National Conference Press Release 6 9
What I want to do is to basically say:
If Row 1 Title = Row 2 Title and Row 1 Year = Row 2 (Year + 1)) Then
add to grid Row 1 Title, Row 1 Qty, Row 2 Qty
Skip Row 2 and go to Row 3 (repeat this step until end of data)
End
Is there any way in the SQL querry to retrieve this data in this format so that I can display it in a datagrid?
Is there any way to display the data in this pattern.
Below is the code I am using now, but since the moving of the data is done in code it is very slow. ****************** Global Variables
Dim dbName As String = "Region_Web_Page_Hits" Dim ColumnCount As Integer
Function ReturnColumnHeaderAndHitsDataSet() As System.Data.DataSet '******** This will bring back the column Headers and Gross Hits per Column Dim ColumnHeaderAndHitsQueryString As String = "SELECT DISTINCT TOP 100 PERCENT " & _ "YEAR(dat_Date) AS str_Year, COUNT(str_PageName) AS int_YearHits " & _ "FROM dbo.tbl_web_page_hits " & _ "WHERE (str_URL LIKE N'%press_Release%') " & _ "GROUP BY YEAR(dat_Date) " & _ "HAVING (Not (Year(dat_Date) Is NULL)) " & _ "ORDER BY YEAR(dat_Date)"
Dim ColumnHeaderAndHitsDataSet = LoadDataArray(ColumnHeaderAndHitsQueryString, dbName) Return ColumnHeaderAndHitsDataSet End Function
Function ReturnTableDataSet() As System.Data.DataSet 'This query string will return the data needed to display the report. Dim TableQueryString As String = "SELECT TOP 100 PERCENT " & _ "str_PageName, COUNT (str_PageName) AS int_Hits, YEAR(dat_Date) AS str_Year " & _ "FROM dbo.tbl_web_page_hits WHERE str_URL LIKE '%Press_Release%' " & _ "GROUP BY str_PageName, YEAR(dat_Date) ORDER BY str_PageName" Dim TableDataSet = LoadDataArray(TableQueryString, dbName) ColumnCount = TableDataSet.Tables(0).Columns.Count Return TableDataSet End Function
'******* Function to Load the Data Array Function LoadDataArray(ByVal queryString, ByVal dbName) As System.Data.DataSet Dim ConnectionString As String = ConnectionStr(dbName) Dim sqlConnection As SqlConnection = New SqlConnection(ConnectionString) Dim sqlCommand As SqlCommand = New SqlCommand(queryString, sqlConnection) Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(sqlCommand) Dim dataSet As DataSet = New DataSet dataAdapter.Fill(dataSet) sqlConnection.Close() Return dataSet End Function
Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If Not Page.IsPostBack Then Dim Sort_Field As String = "" dg_Data.DataSource = QueryMethod(Sort_Field) dg_Data.DataBind() End If End Sub
Currently I have a bunch of Classic ASP code which puts the data in to an array and then into a table with a series of if statments, but it is really slow.
I want to bind it to a data grid and if possible let the SQL statement format the data for me.
I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help. What I want to do is give a column in a table the same value as another column from the same table. For example: Table:Requests A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is : Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be created where the referencing column 'child_table.child_id' is an identity column.
Hi guys, If I have a temporary table called #CTE With the columns [Account] [Name] [RowID Table Level] [RowID Data Level] and I need to change the column type for the columns: [RowID Table Level] [RowID Data Level] to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time. What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance, Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE ALTER COLUMN [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE MODIFY [RowID Table Level] INT IDENTITY(1,1), [RowID Data Level] INT;
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
Hi I’m trying to alter a table and delete a column I get the following error. The object 'DF__Morningst__LastU__19EB91BA' is dependent on column 'LastUpdated'. ALTER TABLE DROP COLUMN LastUpdated failed because one or more objects access this column. I tried deleting the concerned constraint. But the next time I get the same error with a different constraint name. I want to find out if I can dynamically check the constraint name and delete it and then drop the column. Can anyone help.IF EXISTS(SELECT 1FROM sysobjects,syscolumnsWHERE sysobjects.id = syscolumns.idAND sysobjects.name = TablenameAND syscolumns.name = column name)BEGIN EXECUTE ('ALTER TABLE tablename DROP CONSTRAINT DF__SecurityM__DsegL__08C105B8')EXECUTE ('ALTER TABLE tablenameDrop column columnname)ENDGO
