I have two queries I would like to combine the output on. they are as follows:
Select substring(WrkSta.[Name],1,2) 'Location'
,count (aexe.ReturnCode) as '# Patched'
from WrkSta, AeXEvt_AeX_SWD_Execution aexe
where WrkSta.WrkStaId=aexe.WrkStaId
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
and (aexe.returncode='0' or
aexe.returncode ='3010')
and aexe.AdvertisementName like 'MS05-035-043%'
group by substring (WrkSta.[Name], 1,2)
---
Returns :
Location # Patched
EP 102
ES 1986
ET 19
AB 174
SU 6
EM 506
and the second one:
Select substring(WrkSta.[Name],1,2) 'Location'
,count (coll.WrkStaId ) as '# Workstation'
from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId
where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596'
and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%'
or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%'
or WrkSta.[Name] like 'ET%')
group by substring (WrkSta.[Name], 1,2)
---
returns:
Location # Workstations
EP 178
ES 2299
ET 24
AB 215
SU 13
EM 582
What I need is :
Location # Workstations # Patched
EP 178 102
EI 2299 1986
ET 24 19
AB 215 174
SU 13 6
EM 582 582
No mater how I try to do a join to do this in a single query I end up with what looks like a cross-join and # workstations and # Patched jump to huge numbers. I obviously am having a problem understanding how to set up the select statements so that I can do this in one query or am I following the wrong direction and should be trying something else?
Once again I appreciate your help in advance....
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
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.
Hi all, I saw a couple of other posts here on this topic, but none quite got to my issue. I'm trying to add error output to a custom source component (not a script, a custom component). The samples all seem to deal with transform components, and my issues seem to be unique to source components.
I have the following code related to error handling ...
Public Overloads Overrides Sub ProvideComponentProperties()
...
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection.New
Dim errorOutput As IDTSOutput90 = ComponentMetaData.OutputCollection(ERRORCOLUMNNAME)
Dim errorColumn As IDTSOutputColumn90 = errorOutput.OutputColumnCollection.New
errorColumn.Name = outColumn.Name
errorColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0) ... End Sub
The confusions I have are: a) the stock advanced properties editor (I haven't provided a custom one) doesn't seem to "realize" that I have an error output, so provides no method to configure. I am believing it would need to know which Output columns can have their error/truncation redirected. I'd have thought setting ErrorRowDisposition on my output column would have been enough to trigger this ?? b) since I don't have any means of configuring, not surprisingly, when I try to connect my error output, designer complains that, "Ths error output cannot receive any error rows. This occurs for several reasons: Input columns or output columns are not yet defined. Error handling is not supported by the component. Error handling is not configured for the component." c) UsesDispoistions would seem to be appropriate only for a transform component
Thanks for reading, and appreciate any help or pointers. Bill
I have a simple SSIS package that runs a query on the db and outputs a fixed width flat file. I have all my column widths defined and in the connection manager i can preview the output. Everything looks great. All the fields fall where they should and each record is on it's own line.
When i run the SSIS program and then go open my text file with a text editor the ouput is all on the same line. I have tried changing my file format from fixed width to ragging right and adding a row delimiter but that doesn't work either. I feel like i'm missing something small here. It could even be an issue w/ my text editor (although i've tried to open the text file in multiple editors). In the flat file connection manager I have my file defined to be 187 characters long, So figure every 187 characters it should output a new line (it should add the carraige return right?).
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
I simply need the ability using SQL to add columns in an existing table before (or after) columns that already exist.
The MS SQL implementation of ALTER TABLE doesn't seem to provide the before or after placement criteria I require. How is this done in MS SQL using SQL or is there a stored procedure I can use?
I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq
Hi,I have an application up and running. I need to add a new column to one of the tables which is currently being used - would adding a new column change or cause errors in the current application? e.g. if the table is being accessed by selecting * from table, will adding a new column cause an error? If there is any circumstance where an error would be caused by adding a new column, I will have to create an entirely new table. If I have to do this - how do I get a column in the new table to have the same values as a column in the old table? Can I create a computed column where column=oldcolumn? Thanks,Jon
hi guys! I have a table with 3 columns but i realized that i need to add 1 column between column 2 and 3. Can anybody please help me on how to do that? Thanks in advance!
Suppose I have a table with the following columns: Year, SalesInEurope,SalesInAmerica, TotalSales. I want to add a new column calledSalesInAsia, say, but I want it to appear before TotalSales. How canthis be achieved?Thanks,Bruno
I have a sp: mysp_getstuff it contains the following:SELECT Adress,City FROM tblUserData WHERE UserName='john'as you can see it returns 2 columns.I also have another SP: mysp_GetNr. This sp returns an integer.I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'So the resultset from mysp_getstuff should be:Adress, City, Number (in which the number column contains the result from mysp_GetNr)How can I do that?
I posed this problem a few days ago, but havent been able to generate the results i need. Suppose my resultset from an sql query gathering totalsales for a given day by a salesrep looks like this:Lastname totalsales orderID-----------------------doe 1403 510doe 500 680 doe 200 701using SUM(Accounts.totalsales) is not adding up the totalsales. What do I need to do to add up the totalsales, and then reassign it to a newfield?netsports
I have been working on a website in asp.net1.1 in vb.net2003. I am using a sql2000 server. I am attempting to add a column to my datagrid that will add the total number of wins and output the number in that colum. With some help, I have been able to write the code. However, I am not sure where to put it. Is it a sql function I need to call from my code to add to the win column? Thanks for your help.
I have a table size 2078mb, number of row +530,900. Is it normal for sql to lock users out of the db when I add a column to the end of the table? I'm running SQL 7.0. The table has 4 col regular indexes. No primary keys. It locked the user out for about 10 min. I thought with SQL 7.0 this problem went away?
We have a SQL Server 6.5 with SP 5a . I want to add an additional column in one of the tables of a database . Since i don't have much experience on SQL 6.5 , i need your precious help in resolving this case . Can somebody help me in this regard in a detail step wise manner ? Enterprise manager does not have facility to add new columns to an existing table in SQL 6.5 . I want to add a Column called 'DM ' with datatype as 'bit' with size as 1 and as non nullable . Any kind of help will be very precious to me .
This is a very simple select but I would like to add a final column that adds the QOH, QOB, QOO and Quantity. How can I accomplish this? Thanks SELECT DISTINCT zcus_MM_Medsurg_Used.stock_no, zcus_MM_Medsurg_QOH_Only.qty_on_hand AS QOH, zcus_MM_Medsurg_Used.QOB, zcus_MM_Medsurg_Used.QOO, zcus_MM_Medsurg_Used.QUANTITY
FROM zcus_MM_Medsurg_QOH_Only INNER JOIN zcus_MM_Medsurg_Used ON zcus_MM_Medsurg_QOH_Only.stock_no = zcus_MM_Medsurg_Used.stock_no
Can I add a column to a database table without dropping and recreating the table?
The problem is that everytime a user creates an action that requires a new table - at the moment I drop the table and recreate the table with the new column.
This requires lots of resources as I have to populate the table again.
I previously posted about a problem where I added a non-NULL DEFAULT 0 bit column to a table with 80 million records. It was taking a LONG time and we needed that database up fast. It ended up taking a total of 17 hours.
Now my coworker added the same non-NULL DEFAULT 0 bit column to another table on another important server. But this table has more like 400 million rows. It's been running for 100+ hours and is still going. We were hoping it would scale linearly (5*80 million records would hopefully take 5*17 hours) but that isn't happening. I have no idea how much longer it will take. I really need this to be done. I'm tempted to cancel but that will incur a potentially massive rollback, right? Any guestimate on how large that would be?
Hi, I would like to alter a table in a sql server database then update the column with data in the same script. However, the database does not recognize the database column if I create it within the script. Is there a way to refresh this within the script so that I can run this in one procedure? If I create the table in one script then update in a second script it will work. Thanks.
I have a pretty simple SQL query that has two columns that contains only numbers. Is there a way i can add an extra column that simply subtracts the number in one column from number in the other column.I query the data tables rather than have a live link so the .csv file gets overwritten every time i run the query.
Hi All, I've one table named tableAB. in that i've added one new column with not null option in the enterprise manager. then i've generated the script, and run the script in client database. because already data is there, it is not accepting to put null value in the new column. so the is missing. anyway backup is there with me.
I am working on creating a configuration database. This will connect to many databases on many different servers. What I would like to do is run sp_MSForEachDB then store the name of the current server and database the package is connected to. Then add both of these into a column before running sp_helpuser. I don't know how confusing this is, but I think I may have the code worked out pretty well. If you have any questions, please post them and I will try to answer as best as I can. What I would like the ending result to look like is...
I have developed a report using report viewer in asp.net web form. The report is generated based on the selection of Department. User have an option to multi select the departments so the department column grows. Lets say user select Business category first and generate the report then user decides to add another department say Engineering. No the report column headers will have addional column "Engineering". I have used the Matrix since I had to group by Name and dates. Everything is good so far. Now I have to add an additional column "Notes" at the end of all the columns. If there is ony department is chosen then Notes column appears after that and if mulitple departments are chosen the Notes column should also appear after all the department column (at the very end). I am really having hard time to accomplish this. Is there any suggestion or solution to finish this? I really appreciate your responses.
I am developing the database application. This is a live database and is being used by the customer. I am to release a new version and I had to add new columns to fit the requirements DateStarted (DateTime), and TotalHours(Int) into a database table.There are already over a 1000 rows in this table.
When the customer wants to look at a record in this table and insert the value into the text boxes (Front-end), when it gets to the DateStarted or TotalHours it comes up with a error message:"
The value for column 'DateStarted' in table 'IncidentTask' is DBNull."The method for inserting is:
What are the possible solutions to this problem? Would it mean checking for a DBNULL before displaying in the textboxes? Or updating the new column rows with an date:
Code Snippet
UPDATE IncidentTask SET DateStarted = '1/1/2005' WHERE (DateStarted IS NULL)
