Do Mapped Columns Need To Be In Exact Order Everytime?
Jan 23, 2008
In my SSIS package I have a text file source that I am mapping to a destination table. I have an error component that logs any row level errors and have noticed that it is not logging the correct field. I know this because I have a few different sources that submit the same files and have looked at the source of both. THE ONLY DIFFERENCE in the one that works versus the one that does not is that 2 of the 25+ columns are switched. I would not think this would matter because field A in the text file is mapped to field A in the database.
Does the order in which the fields come into the SSIS package matter?
I am familiar with the ALTER TABLE syntax that can be used to add columns to an existing table, but the columns are appended to the end of the table. Enterprise Manager allows you to insert a column in the desired place in the table, but I suspect that behind the scenes it creates a temp table with the new structure, drops the old version of the table and renames the temp table to the orginal table name (I could be wrong on this).
Is there a way to insert a new column in a specific place in a table using SQL rather than EM?
Is there a dynamic management view or system procedure which I can use to find out what columns are in an index, what columns are as an INCLUDE in the index and whether or not the column(s) are ascending or descending. This is excluding the utilities I already know about below:
sys.indexes sys.index_columns sp_helpindex dm_db_index_physical_stats dm_db_index_operational_stats I only ask because it is a pain to look through the sys.indexes and sys.index_columns tables every time I want to know about what columns are in the index created. I also know that scripting the index would give me the information I need but there must be a better way.
Hi, I am using MS SQL Server 2005 9.00.1399.00. I am trying to make a PivotReport. Everything is fine, but the columns are are ordered alphabetically, but i want that they are ordered in a specific way. So i just created a new column ProcId in the Query, but how can i order the columns with the new column but display the other values.
and thats what gets written to the flatfile destination.
i want SSIS to preserve the column order and write it as col1,col2,col3,col4 instead of what is happening now and it gets written as col2,col4,col3....
i am running sql2k5 and SSIS with sp2
is this a bug is there a solution for this or change the order of the columns for the flat file destination
Alter table ABC Add constraint Primary_KEY_ABC PRIMARY KEY (DATE, Amount)
but i get the error primary key already exists. So is there any way I can generate a unique name everytime I run the proc, its runs only once a day so can i use getdate() in the name somehow. is it possible.
I'm unable to specify multiple columns in my order by statement if i use a case statement. Does anyone know why this is, or what syntax would make this work?
Thanks
SELECT .... ORDER BY (CASE Lower(@SortExpression) WHEN 'prodname' THEN prodname, prodprice WHEN 'prodsize' THEN prodsize, prodname WHEN 'prodprice' THEN prodprice, prodname Else prodcompany, prodname END)
According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?
I have 2 columns in a table namely ColA and ColB.all DML operations are through views n every view has Where clause i.e where ColA=€?€? with check option . All most all my DML queries are using where clause on ColB Where ColB=€?€?
Now my question is I have a clusted index on both ColA and ColB.in which order I have to create cluster index . i.e ColA ASC,ColB ASC or ColB ASC,ColA ASC.
Is there any performance gain we can achieve with their order
I have a report that displays data based on the last 12 months. Is there a way I can order the columns (header and data) based on the month it was run. eg. If I were to run the report in March, I want the columns to be ordered like this:
MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB
If run the report in April, I want the columns to be ordered like this:
APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB, MAR
So, the columns in the report are always ordered (12 months backward) based on the month it was run.
I'm intersted only to see if the login happens three time, but i'm not able to see that. Also, there was no Audit Logout. Does SQL Server automatically pool connections ? If i run Openrowset 3 times, shouldn't it open & close connection 3 times ?
I am relatively new to complex queries and need creating a query using a CASE in order to update columns to be either A or B. A few things about this is that I am joining tables from linked servers as well. This is the last part. I execute the query and receive the error:
Incorrect syntax near the keyword 'from'.
select (select FirstName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorFirstName, (select LastName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorLastName, (select PersonID from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as SupervisorEmployeeID,
Here is the situation. I have created a package that takes 50 columns from a comma delimited flat file. I then validate and clean the data. Next I add two columns that were not in the original source file. These two columns need to be in the 5th and 9th column position when the file is then re-written to a text file. How do i get those two columns to write out in the desired order? Any ideas?
I need to create a number of flat files, all with the same layout and sourced from the the same table, but with different criteria.
The first set of (three) flat files file is created out of a simple Conditional Split transformation: If Source Table row number > 40,000 route to File 3; if row number > 20,000, route to file 2, otherwise route to file 1. This gives me 20,000 rows in files 1 & 2 and the remainder in file 3.
I also want to create a fourth flat file by joining the Source Table with a sample table and selecting only those rows where the Customer numbers match. I'm currently doing this in two stages: An Execute SQL Task performs the join and inserts the selected rows into a Destination table (identical layout to source table), and then a simple data flow moves the rows from the Destination table into the fourth flat file.
My problem is that the order of the columns in the first three flat files is different from the fourth file. I've tried creating the fourth flat file with a single data flow using a Merge Join transformation which didn't work because the tables aren't sorted in the correct sequence, and I couldn't get an OLE DB Command transformation to work either.
I'm not sure why the column order of the 4th file should be different seeing as how its contents are sourced from the same Source table, but is there a cunning way of setting this up so that the columns end up in the same order?
When I open a report definition, go to a dataset and open it, the CommandType ALWAYS goes to "Text". I don't want "Text", I want "Stored Procedure". I NEED "Stored Procedure".
I have also found that the CommandText for a different dataset will change even though I have not opened it.
I guess the only solution I have is to open the RDL files, search for the dataset entries and then fix the XML code. This is a real pain in the buttocks when I am working with 300+ reports.
Is there a fix for this bug? Or are you going to FORCE everyone to use "Text"?
I am trying to finish this project so that we can release it to our customers but now I have to wonder why we decided to go with Reporting Services, especially now that we have to do double edits on each and every report.
This appears to be a change in behaviour between SQL 2000 & 2005. Can anyone confirm?
We have two tables with same schema but different column orders. In Sql 2000, the statement
Insert Into table1 select * from table2
appears to map the column names between the two tables. There is one column out of order, however Sql 2000 doesn't seem to care and correctly inserts the data.
In Sql 2005 the behaviour is to return the columns in the order of table2, rather than mapping column names. This results in incorrect values being added to the columns. (Column shift)
The Sql 2005 behaviour seems to be correct, and select * is bad practice anyway, however I would like to confirm why this was changed and whether there is a service pack/hotfix in 2000 that would have the same result.
We are running compatibility mode in Sql 2005 v9.0.3042.
Is there a way to permanently change the order of the columns in Job Activity Monitor?
I'd like to move Duration to the right of Step Name, but this only lasts so long as I have JAM open. Once I close it and re-open, JAM goes back to its default column order. Google gives me nothing but the temporary "drag and drop" method that I already know about.
Every time I execute DBCC DBREINDEX (Table_Name, '',0)WITH NO_INFOMSGS the server hangs. No error messages in windows 2000 logs or in the SQL 7.0 logs.
The table is about 2 gig in size and the index is a clustered index on two fields.
The server has 1 gig of memory and the cache hit ratio, and cache flushes both look good. Any ideas? Any help would be greatly appreciated.
This means, I am using the Application object and the Package object to actually load the package using the path to it.
So my code looks something like this:
-----
pkgLocation = @"<package_path>/Package1.dtsx";
app = new Application();
pkg = app.LoadPackage(pkgLocation, null);
pkgResults = pkg.Execute();
Console.WriteLine(pkgResults.ToString());
Console.ReadKey();
---
My package reads in a flat file (located on another server) and transforms it and saves it to a database (on that same server). And mind you, I can execute this package just fine when I do it manually from within BIDS.
But when I try to execute the above mentioned code in my C# solution (compiled to a command line executable), I always get a "Failure".
Can somebody point out what I am doing wrong here?
In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).
I'm reading values from a named range within an Excel spreadsheet using the Excel ODBC driver. If I ask for all columns within a range, using e.g. select * from 'named range', does the driver ensure that the returned rowset has the same column and row ordering as in the spreadsheet? In other words, if a named range on a spreadsheet is the block of cells:
name age
richard 54
jemima 27
I want to make sure that my returned rowset is not going to be:
age name
jemima 27
richard 54
I know that proper databases do not guarantee the order of returned values (unless you specify it) but since Excel is a fixed view of data I was hoping that a returned rowset of values would retain their spreadsheet ordering.
Hi guys, Im desperately hoping someone here can help me. Ive been trying to set up merge replication between 2 servers all weekend and it keeps failing. Im setting this up as follows The Publisher/Distributor is a SQL Server 2005 machine, the database being replicated is 4gb, and the only subscriber is a SQL Server 2000 SP4 machine. I set up my publication normally, and set up the push subscription, adn set it to initialize immediately. It weill then start bulk copying and go on for about 3-4 hours before it fails and gives me the errors:
Error messages: The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001 The process could not bulk copy into table '"dbo"."CONTENT"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037 An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 0) Get help: http://help/0 One or more BLOB columns could not be sent to the server, attempt to recover from the problem failed. (Source: MSSQLServer, Error number: 0) Get help: http://help/0 Communication link failure (Source: MSSQLServer, Error number: 0) Get help: http://help/0 Unspecified error (Source: MSSQLServer, Error number: 0) Get help: http://help/0 Failed to send batch after max errors (Source: MSSQLServer, Error number: 0)
This is driving me crazy as I havent slept in ages trying to get this fixed. Anyone have any idea whats going on? Its always one of 2 tables so far that keep failing, ContactLog and Content, what can I do?
I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.
As per definition,
stats_column_id int
1-based ordinal within set of stats columns
This script duplicates this for me.
BEGIN TRAN GO use tempdb GO CREATE TABLE [dbo].[ItemProperties]( [itmID] [int] NOT NULL, [cpID] [smallint] NOT NULL, [ipuID] [tinyint] NOT NULL,
[Code] ....
The result I get is this:
object_id      stats_name                                     stats_column_list 1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, cpID, ipuID,
and
object_id      index_name                                     index_column_list 1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID, ipuID, cpID,
Also a query I used to discover this in my db is:
WITH stat AS ( SELECT s.object_id ,s.name as stats_name ,( SELECT c.name + ', ' as [data()] FROM sys.stats_columns as sc
Why does Error log show database restarting everytime a transactionlog backup runs?It also runs a checkdb as well.The log backups are scheduled through a maintenance plan.Is this normal?
I am copying data ( ~600000 rows) using SqlBulkCopy. The operation fails after copying 390000 rows with the follwoing exception ( this happens every time when i run and after the same number of rows copied). Is anything else i need to do differently. The server has 35GB of free space & 1 GB of RAM.
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapse d prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConn ection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateO bject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.ReadBuffer() at System.Data.SqlClient.TdsParserStateObject.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataRe ader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at PSMigrate.Program.MigrateWorkItesLatestData() in E:dd_tfs_beta3vsetSCMworkitemtrackingTo olsPSMigrateProgram.cs:line 522 at PSMigrate.Program.MigrateData() in E:dd_tfs_beta3vsetSCMworkitemtrackingToolsPSMigrate Program.cs:line 106 at PSMigrate.Program.Main(String[] args) in E:dd_tfs_beta3vsetSCMworkitemtrackingToolsPSMi grateProgram.cs:line 86
Here is the part of the code
using (SqlCommand cmd = new SqlCommand())
{
SqlDataReader dataReader;
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
conn.Open();
dataReader = cmd.ExecuteReader();
// write the data to the server
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(m_destConnString))
{
// column mappings
// event settings
sqlBulkCopy.NotifyAfter = 5000;
sqlBulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(RowsCopiedEventHandler);
can any help me why the same exact code works in vb.net but not in C# . case is very simple I have a table called MenuItems which has menu items and a table sizeandprice which has price for each menuitem based on the size. simple case of 1 to many relationship between menuitems table to sizeandprice table. I am trying to display in a gridview control couple of fields from menuitems table and have another template field in which i am display price and size field from the child table which is sizeand price. so basically this is how my code looks like Page in vb works fine <%@ Page Language="VB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <script runat="server"> Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) If e.Row.RowType = DataControlRowType.DataRow Then Dim ds As SqlDataSource = CType(e.Row.FindControl("SqlDataSource2"), SqlDataSource) ds.SelectParameters("fkMenuItemID").DefaultValue = GridView1.DataKeys(e.Row.RowIndex).Value End If End Sub </script> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Untitled Page</title> </head> <body> <form id="form1" runat="server"> <div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>" SelectCommand="SELECT [MenuItemID], [MenuItemType], [ItemName] FROM [MenuItems]"></asp:SqlDataSource> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="MenuItemID" DataSourceID="SqlDataSource1" OnRowDataBound="GridView1_RowDataBound"> <Columns> <asp:BoundField DataField="MenuItemID" HeaderText="MenuItemID" InsertVisible="False" ReadOnly="True" SortExpression="MenuItemID" /> <asp:BoundField DataField="MenuItemType" HeaderText="MenuItemType" SortExpression="MenuItemType" /> <asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" /> <asp:TemplateField HeaderText="Size And Price"> <ItemTemplate> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PPQ_DataConnectionString1 %>" SelectCommand="SELECT [ItemSize], [ItemPrice] FROM [SizeAndPrice] WHERE ([fkMenuItemID] = @fkMenuItemID)"> <SelectParameters> <asp:Parameter Name="fkMenuItemID" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource2"> <ItemTemplate> <%#Eval("ItemSize")%>: <%#Eval("ItemPrice", "$ {0:F2}")%><br /> </ItemTemplate> </asp:Repeater> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView>
</div> </form> </body> </html>
if you would notice that in order populate my repeater with childrows i need to know the menuitemid from the parent row which i do in rowdatabound event however same code in C# does not render any values for my item price and size defined inside the repeater control. here is how the c# page looks like
I'm currently working on a match function to compare two char based columns in differnet tables to create a join.
In this particular case, I'm using a few different approaches to create a higher match ratio, as typos do happen.
For instance I use a join function using convert(char(10), tbla.field) = convert(char(10), tblb.field) to match only using the first 10 characters, as a lot of records have different endings, but are in fact the same.
Are there any other ways I could attempt to make matches? I was wondering if there was a dedicated string comparison operation giving me a percentage feedback. Debut joining dbut would give an 80% match, and thus I would leave it up to the user to decide to minimum match requirements.
OK, this is a simple question for you, but i am a newbie to SQL2000, and i need to find out which Service Pack we are currently running. Could anybody please give me a short reply :)
I've a table with values that have the '-' char, ex:LO-AS-1.I'm trying to search the table for that value but it returns more records since it breaks the search term into several others:
SELECT * FROM sys.dm_fts_parser ('"LO-AS-1"', 2070, 0, 0) returns: lo-as-1 lo as - noise word 1 - noise word nn1 - noise word
Is there anyway of searching for the exact word? With LIKE it works fine but it's very slow....
