select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;
If you notice, the earliest dtmready is 7/1/2006. What I need is to return an additional row when the earliest dtmready is after today. The desired row would be:
A1 0 (today's date)
Background: I am running SQL Server 2000 SP4 and the results of the query are returned to a java program at a level where I do not have the ability to create a new row. So, it would be ideal if I could create the sql that returns a row with a dtmready of today with a count of 0.
HI When I'v click in Visual Web Developer 2008 to build web site it has made a new MDB file name ASPNETDB. MDF So now I have 2 mdb file (MyFileName.mdf and ASPNETDB. MDF) What is that extra (ASPNETDB. MDF) file and do I need it and why? Thanks assaf
I used excel to import data to my database, I found out a problem, my program is linked with the database, when the program show data from the database, it has an extra '@' symbol, In order to remove it, I need to go to the database to press space bar and backspace at the field. How could I use SQL instead of using space bar and backspace?
I have a strange SQL statement Select * from TableName WH
Basicly I have a code that creates a Dynamic SQL statement in this case I thought that I will get an error on the above statement but infact I did not I tested it on the Enterprise Manager and it works fine I changed the WH to WHER it works as long as its not a reserved word so if I put my name or what ever after that sql it still works unless I use two words with a space between them it will give an error on the second one not the first
Can anyone tell me why is that I do not know if its a feature or what
Our company wants to run web based application in folowing way.
Browser --> WebServer -->Sql Server
Sql server is part part of corporation domain and has about 25 more databases
Should we dedicate extra SQL server to run Web apps , or it would be safe to run web apps on corporation Sql Server? or If any one can point on links on this subject?. Thank you
I have some fields in my database that are char(50)
I use an insert to put some text into the fields (using perl) when I look at the fields I find that spaces are used to "pad out" the text to the field size.
This didn't happen with MySQL and PHP, is there any way to disable this?
Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'Multiflex_YBS', index 'mf_InvObject.I_mf_InvObject_Archived' (ID 308196148) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:1189627:69) with values (Archived = Jul 6 2005 10:04PM and InvObjectId = 4374699) points to the data row identified by ().
Before I start just wanna say thanks for all the top advice so far - much appreciated. My current quandry is this:-
I have a table called STOCK with columns ITEM_ID, ITEM & PRICE. Most of the stock is my own produce but i do buy-in some items. What i want to do is add some extra columns to STOCK for the supplier details on those items i buy in. The problem is that a lot of the fields in the supplier details columns will be empty. Would this be a problem or would it be better to have a seperate table for supplier details?
INSERT@Sample SELECT'a', 1, 'new', 1,'1/1/2003' UNION ALL SELECT'a', 2, 'renewal',2,'8/2/2003' UNION ALL SELECT'a', 3, 'renewal', 3, '2/1/2004'UNION ALL SELECT'a', 4, 'renewal', 4,'11/1/2004' UNion ALL SELECT'a', 5, 'renewal' ,5, '3/8/2006'UNION ALL SELECT'a', 6, 'new', 1, '3/1/2004' UNION ALL SELECT'a', 7, 'new', 1,'12/3/2005' UNION ALL SELECT'a', 8, 'renewal',2, '8/2/2005'UNION ALL SELECT'a', 9, 'renewal', 3,'9/2/2006'UNION ALL SELECT'a', 10, 'renewal',4, '10/2/2006'UNION ALL SELECT'a', 11, 'renewal', 5, '11/2/2006' UNION ALL SELECT'b', 1, 'new', 1,'8/2/2003'UNION ALL SELECT'b', 2, 'new', 1,'9/2/2003'UNION ALL SELECT'b', 3, 'new', 1,'8/2/2005'UNION ALL SELECT'c', 1, 'new', 1,'8/2/2004'UNION ALL SELECT'c', 2, 'renewal',2,'8/2/2005' UNION ALL SELECT'c', 3, 'renewal',3,'9/2/2005' UNION ALL SELECT'd', 2, 'new',1 ,'8/2/2005'UNION ALL SELECT'd', 3, 'renewal',2, '4/2/2006'UNION ALL SELECT'd', 4, 'renewal',3, '6/2/2006'UNION ALL SELECT'd', 4, 'renewal',4,'9/2/2006'
I want to remve all records serials startign with type 'new' (neworder 1) which is later than ,1/1/2005' and subsquncial renewals The desire result should be:
Hi! I need to sync an application on windows mobile 5 that uses sql server mobile with sql server 2005. I've tried Merge and it populated the tables on the server with new fields and triggers. Does RDA do the same thing?
I have asked for the following questions and I need your advises.Utilizing the Northwind database suppied with SQL Server, create SQL tosolve each of the exercises listed.1.I want to contact all customers who have received over $1,000 indiscounts on orders this year. Give me the name and phone number of theperson to contact at the customers site. Also, list the orders wherethe total discount was greater than $100. Remember, discount is apercentage of the price.2.Give me a list of suppliers and products where we do not have thestock on hand to fill the orders to be shipped. List out the customerand order information for each of the products involved.3.Give me a list of all orders that were shipped after the requireddate for the week of Jan 7, 2001. I want to know the name of theemployees that were responsible for the orders.4.We are having a golf tournament and I need some prizes. Give me alist of the top 5 shippers by dollar amount in the last year.5.Some customers are taking us for a ride on shipping. Give me a listof customers and the orders involved where more than ½ of their ordersare being shipped to a region other than their home region.Please advise ...thanks a lot
I have worked with straight-forward simplistic SSIS up to now, so I apologize if this is a simple question. I have a SQL table destination that accepts daily data from an AS400 table. We now have a need to datestamp the incoming data so I added a new column to the destination table where I want today's date. Is there a data flow transformation that I need to use to default the destination column to today's date? Time is not a consideration and not needed, just the date.
Hi, I am declaring a table with 2 column Id, and Name. Id is a integer column but Name is a Varchar. I am declaring Name as VARCHAR(100) you can say that varchar(100) is more than enougf for a name column. But lets suppose ifn futuer user will enter data only till 100 characters but lets suppose i put or declare it as NAME VARCHAR(4000), That means a column requires only 100 memory space but i am giving 4000 to that column So in case of SQL any drwbacks are there for this approach. Meaning my 3900 space will not be used so will it a wrong thing or we can declare it as ,,, it will put no effect??? or what are the drawback??
I'm trying to make a report with the following layout:
Car Sales 2004 2005 Var % Total 10 20 50% Green 7 14 50% Red 3 6 50%
I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'. I've built part of the example but I can't add the final column ( the VAR% column). If I try to add a static column in the end it apears only one year. The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years. Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?
I've managed to grouped by Category field nicely and added extra calculated column for handling to total plus as well as Total for each group plus added a new row for TOTAL in the TABLE and returned as follow:
Now I want to put another extra row underneath TOTAL for handling the calculated but the business rule is only for displaying from the first group for instance:
TOTAL: $3032 Calculated Average per fruits: $1010.67 ($3032 / 3 UNITS).
3 UNITS is coming from Units for Apple and Banana.
I don't know if I can do this cause i did try to use
But it's successfully compiled but the report result error (#Error) on that field only. Any ideas? Also ... someone mentioned that I should look at the SCOPING but I don't work it how's work anyway.
I thought a simple like this can be handled quite easily instead of creating a new report view type in the the same dataset to handle this particular extra line.
When I export to PDF or Tiff, I am getting an extra blank page at the bottom of the document. Any ideas what might be causing this blank page during my export?
I need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.
I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?
This is an example of how I want my report to look (I can already achieve the bits highlighted bold)
Daily Data
Commission (GroupData cell 1) Interest (GroupData CELL 2) GroupData etc €¦ SUBTOTAL (DTD) MTD TOTAL YTD TOTAL
Book Currency =sum(Fields!DTD.VALUE)
........
=sum(Fields!MTD.VALUE) =sum(Fields!YTD.VALUE)
and this is an example of the data being returned by my Sproc
Book CCY GroupedData DTD MTD YTD
ABC GBP Commission £0.01 £0.09 -£0.10
ABC GBP Interest £0.02 £0.29 £0.11
ABC GBP Brokerage £0.12 £0.06 £0.20
When I use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.
Daily Data
Grouped Data CELL 1 Grouped Data CELL 2
Book Name Currency DTD TOTAL MTD TOTAL YTD TOTAL DTD TOTAL MTD TOTAL YTD TOTAL
I’m using a SQLDataSource to populate a dropdown. The SQL table I use to populate the drop down has two columns. I only want one of them to be displayed in the drop down but I need to make decisions later in the code based on both columns. How do I access that second column in the datasource?
I'm encountering a strange problem in all the applications I'm working on and am totally dumbfounded as to why it's occuring:From a standard web form I'm inserting a record using a stored procedure. (I'm writing this to a SQL 2000 db - where the column types and variables are all consistant) No matter what I do, the columns are padded with extra characters maxing out the field length (if it's nchar or nvarchar or char) after insert or update. I've tried Trim - ing the field.text values that I'm feeding to the @variables used in my stored procedure. I've even RTRIM() - ed the @variables within the stored procedure. No matter what I do I get extra spaces padding the end of the intended column input. Ideas anyone???Thanks in advance. - Abe
When I enter a data in a table, SQL Server automatically completes the data with blanks up to length of column. this happens in a web form also in Management Studiıo, Whan am I doing wrong ? does Collation have anything with it ? SQL Server 2005 / Developer Edition
I noticed our log files are getting way too big. I found that a previous SQL guy had set up the following scheduled job with the TSQL statement:
BACKUP LOG DEV TO DevDailyTranLog with noinit
This runs every 20 minutes.
There's also a Maint Plan to do a Complete backup every night and a Transaction Backup every hour (could be set to 20 min)
Why do you suppose the BACKUP LOG job exists ? If the maint plan were set to backup transaction every 20 min, wouldn't the 2 jobs be duplicating each other ?
Also, I notice that the Tran Logs specified in the database properties seem awfully big (4 gig), shouldn't they automatically be truncated when the daily full backup occurs ? (full recovery model)
Feel free to call me and straighten me out or to get more info.
I am trying to create a SSIS package with a csv flat file for the both the destination and source. I cannot control the source file. I need to be able to handle an extra comma in the source file which occasionally contains suffixes i.e. {John, Smith, Jr,} which could cause some records to be 9 fields and others 10. Any ideas?
I have text file having three column like 300;Dev Fot;30097 400;Kit Mol;79684
now i have to insert this in DB with SSIS but at the same time I want another column insert in DB table with ID like 1;300;Dev Fot;30097 2;400;Kit Mol;79684
my table structure is Table_name (ID not null,EmpID not null;desc Not null;FinID Null)
How may extra bits does a NULL column contain.And how many extra bits does a varchar column contain.(I 've worked with Ingres and in that environment it both needed 2 extrabits)Bye,Arno de Jong, The Netherlands
We are running into problems on our development environment with extraparameters being added on to a stored procedure call. Instead of theexpected stored procedure call, the trace shows something like this:declare @P1 intset @P1=NULL<<expected stored procedure call>> , @P1 output, <<repeat of firstthree sp parms>>select @P1The developer has checked the code, and I have checked the SP - bothseem to match what is in production (which works fine). The databasesare on the same server, and the apps are running on seperate webservers.I'm guessing this may be some sort of configuration issue with ADO,SQL, or something else - has anyone run into something similar tothis? Thanks!Dave
I keep getting extra space at the bottom of each page on my report when it is deployed to the web server but I don't get this extra space on the report server. Does anyone know if this is in the report layout or if the problem is in the web page itself?
I have a derived column component which has an regular output and an error output.
This works fine.
I want to add an extra regular output path.
I create a new OLEDB destination component and name it X
I right click on the derived column component and select "add path"
I then select X as the recipient of the new path.
And it errors with this garbage below
How can all the inputs be used up, it is a new component! It doesn't have any inputs!
Anyone know why this is happening (apart from the fact that it is not possible for me to go more than about 4 hours without encoutering another cryptic SSIS error that has no corresponding help topic on the MS website)
error message: (once again the MS help topic returns nothing)
===================================
All the available inputs on the target component are connected to outputs. Edit this component to define new inputs before proceeding. (Microsoft Visual Studio)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=DtsComponentNoOutputsFree&LinkId=20476
------------------------------ Program Location:
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewPath(PipelineComponentMetadata sourceDtsComponentObject, PipelineComponentMetadata destinationDtsComponentObject) at Microsoft.DataTransformationServices.Design.DataFlowDiagram.OnAddPath(MenuCommand sender, CommandHandlingArgs args) at Microsoft.DataWarehouse.Design.CommandHandlingInfoMap.HandleCommand(MenuCommand menuCommand) at Microsoft.DataWarehouse.Design.ComponentDiagram.InvokeCommand(MenuCommand menuCommand) at Microsoft.DataTransformationServices.Design.DataFlowDiagram.InvokeCommand(MenuCommand menuCommand) at Microsoft.DataWarehouse.Design.ComponentDiagram.Microsoft.DataWarehouse.Interfaces.ICommandTarget.InvokeCommand(MenuCommand menuCommand)
I have designed an SSIS package and in a data flow task I've defined my source and destination components and mapped all the fields. The task works fine as long as I have the same number of fields in my CSV file as what I have defined in the task.
The issue is that if my CSV contains more fields than what are declared in the mapping, then the extra fields at the end of each line are concatenated into the last column defined in my map. For instance:
I've read a MSDN article on enhancing error outputs with a script component. This solution added the script component astride the error output, and it was able to add a column with the error description text.
I would like to do something a little different. I'd like to add information from my custom component. When the component causes an error, it generally "knows why" - it has available to it the information that made it decide there was an error. I'd like to include that information in error outputs, but I haven't seen a way to do that.
Is this even possible using the error output directly? I could abandon error outputs and use Conditional Split, along with an "iserror" column plus my extra columns, but I'd prefer not to do that.
