I have a view that give me the data of all the batched. Now I am using a query on view to get a single batched data. when I am using direct query it was taking 0 sec but when I am using Through view "select * from myView where batched=2" then its taking 30 mnt.
I recieve duplicate customer bill data which I want to consolidate using SQL query. How to summarize data using single query...both Qty and Price. If either one of price or qty is -ve then both should be -ve.
Hi I have a table named UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out
This a microsoft SQL 2000 server. I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID. Here is my problem: Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.
I know how to get the user table names from the DB as follows:
SELECT name FROM dbo.sysobjects WHERE xtype = 'U' and name like 'Elect_Sub%Daily'
What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".
delete from tableName where Date_Stamp = '2008-3-18'
DECLARE @ServerCIName varchar(5000) SELECT * FROMÂ dbo.INC WHERE Status in ('Assigned','In Progress','Pending') and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)) In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.
But @ServerName contains multiple values and it is dynamic (not constant).
If a Select is done on a column whose data type is nvarchar(16) and contains only numerals (UPC numbers) the select does not return the record.
1. Query with numerals in nvarchar column works as long as multiple records are returned (LIKE '012%') 2. Numeric (INT only one tested) columns works as expected 3. String columns with alpha data works as expected 4. Problem only exist when running in Device Emulator and/or actual device. 5. Same test on desktop app runs as expected. 6. Windows Mobile 6, Vista Ultimate 7. Same results when when connection to device from SSMS 8. SQL Servers comes on
Previous thread discussion of this problem (I thought that Parameters corrected problem, but not in all cases???)
I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table:
"This operation conflicts with another pending operation on this transaction. The operation failed."
The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine.
Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction?
I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place?
I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.
So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.
I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.
My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.
Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)
I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).
The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.
I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.
In my project i want a report. In that report data is getting from more than one data sources(systems). While creating data source view i used named query for both primary and secondary data source. But at the time of crating "Report Model" i am getting below error.
An error occurred while executing a command. Message: Invalid object name 'Table2'. Command: SELECT COUNT(*) FROM (SELECT SerialNum, ModelNum AS com_model FROM Table2) t
Is there any way to create a report with multiple data sources?
Hi, I have received a text file in the following format: MthYear Customer Quantity Price Total Apr2003 Allan 100 5 500 --------- Austin 25 2 50 --------- George 1500 1 1500 ---------- Jessy 200 2 400 Apr2004 Jerry 600 3 1800 --------- Stella 250 2 500 June2005 XXXX 50 5 250
I am exporting this text file in Sql Server database table. After exporting I need to Update the MtnYear field marked as ------- to appropriate year. ie The MthYear field for Austin, George,Jessy should be updated with Apr2003, The MthYear field for Stella should be updated with Apr2004 iiily, for other records. Let me inform if this is possible with the help of a single query or any better way of doing it. The records that i need to update is more than 2 lakhs. I am sorry if u feel this questionis not apropriate.
So I have code to fill my repeater with a data: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim cnn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("db_ConnStr").ConnectionString) Dim cmd1 As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter("SELECT aspnet_Users.UserName, COUNT(forum_posts.post_id) AS IlePost, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date FROM aspnet_Users INNER JOIN forum_posts ON aspnet_Users.uID = forum_posts.user_id INNER JOIN forum_posts AS forum_posts_1 ON aspnet_Users.uID = forum_posts_1.user_id GROUP BY aspnet_Users.UserName, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date HAVING (forum_posts_1.topic_id = @topic_id) ORDER BY forum_posts_1.post_date ASC", cnn) cmd1.SelectCommand.Parameters.AddWithValue("@topic_id", Request.QueryString("ID")) Dim ds As Data.DataSet = New Data.DataSet cnn.Open() cmd1.Fill(ds, "posts") Repeater1.DataSource = ds.Tables("posts") Page.DataBind() cnn.Close() End Sub And I want to take the value from column which I bolded - IlePost. I want take this out at compare with some condition. For example:Dim label as label = Repeater1.Findcontrol("Label1")If COLUMN < 10 thenlabel.text = "Less than 10"elselabel.text = "More than 10"I hope you understand me :-)
Table Name:-emp table structure:- Name Gender A Male B Male C FeMale D FeMale I want only one update query to update this table like this table structure:- Name Gender A FeMale B FeMale C Male D Male please help me?
Hi Guys,This is my Problem.A table contain following desing for handling different level of categories, bu it is dynamic int_categoryid,int_parent_categoryid,int_categorylevel,str_categoryname,bit_activethatall.I want to list data from table as following ordercategorry_parent11 category_child12 category_child13 category_child23 category_child22categorry_parent21.............................................................................................like this..ie we can insert parent category and sub category to n level dynamically without adding a new tableplease mail me for another clarification....please help meregardsAbdul
I just need the single select to result the docnbr which is repeating the values. In the above case I want my result like below where the DocNbr 00002 and 00004 repeated their values.
Hi groupI have a rather peculiar question, and I really don't know how to solvethis within an SQL statement:Given a view (v), that results in:IDX-----------------1a1b2a2c3aI'd like to query the view with something like:SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:ID-----------------1or in another case:SELECT ID FROM v WHERE (X='a' OR X='c')would give:ID-----------------123how can this be done?TIAbernhard--www.daszeichen.chremove nixspam to reply
Say the following are the columns of a tableA B C D E FCan a aggregate function like sum be applied to A like sum(a) and thenorder by b and c similarly aggregate function on d and group by e andf using a single query...Regards,Jai
I have a question which was given to me in a test. Question is :: print value from 1-20 in a single column using single select query without using any table. If any one have solution, then please help me.
hello i am a beginner i it possible to get this result by single query ?
i got a int column and a datetime column is it possible to get the sum(int column) for all the year 2007 and by the april2007 and by the month n year by the person name Tony
I have a query that I'm stumped on. The data has about 6000 records, of which about 460 of those have distinct dealer names and ids.
I am trying to condense this data so that there is only one record for each dealer, but my 3rd column has different values which is why some dealers have only 2 records, and others may have 10.
I know how to select distinct values, but what I want is to return a list of comma separated values for my 3rd column which consists of all the unique values from that 3rd column for that dealer.
For instance, say I have a dealer with 8 records. They have a unique ID and Dealer Name, but the 8 records appear because the dealer has 8 entries for its activity. The activities may or may not be unique.
So for example I have dealer ABC who has an id of 54, their name is ABC, but for their activites they have 8 entries (apple, orange, banana, pear, apple, banana, mango, peach).
I wnt to be able to select the distinct id, name, and distinct activities so that when my query is returned and displayed the record appears as such:
I have a table with 1 column containing numeric values, and I need to create a query which displays the count of values for each set of conditions. For example, if the table contains the values 1,2,3,4,5 and 6, the output of the query should look like this:
L M H ----------------------- 3 2 1
(L are values below 4, M between 4 and 5, H is 6 and above)
I need to write a single query that will append the values from one table into another table but I need to update a single field in the table with a static value. What I'm working with is an Access .adp with an SQL 2000 backend. The database is used to track ticket sales, payments, and contact info for season ticket holders. Prior to upsizing, each year the database would be copied and then choice bits modified for the next year.
In other words, the history was in several databases, i.e. db2001, db2002, db2003 and for the current year dbcurrent. I decided to create a historic tables in the current DB rather than have umpteen DBs on my SQL server.
My problem is that I need to create a query that appends to my table the previous years data and then updates the season field to reflect the season that the data came from.
In short, say I have a table named accounts with fields account, customer, addr1, addr2, ..., ticket type and I want it to end up in account_hist with fields account, customer, addr1, addr2, ..., ticket type, SEASON and make the season equal say 8 which represents the 8th season the team has played. I can get both queries to work seperately :D , but for end user ease, I want to perform both actions at the same time :confused: . Can anybody point me in the right direction? Thanks
I am using 3 tables: projects, project_manager and project_employee
projects
- project_id (int, PK) - project_name
project_manager
- project_id (int, PK) - manager_id (int, PK)
project_employee
- project_id (int, PK) - employee_id (int, PK)
Assume that a manager is currently logged in (so we know what is his ID), what I trying to do is write a query that will display a grid-view showing:
project_id, project_name and number of employees in project.
I tried these quires to test:
SELECT project_id, count(emp_id) as NumberOfEmployees into #tempTable FROM project_employee WHERE project_id IN (SELECT project_id FrOM projects) GROUP BY project_id
SELECT projects.project_id, projects.project_name, NumberOfEmployees FROM projects, #tempTable WHERE projects.project_id = #tempTable.project_id
Problems are: 1 - I used two queries not one 2 - The result is for all projects of all managers, I just want to display projects for a specific manager (for example mag_id = 5) 3 - it created a temporary table - which I dont want.
Is there anyway to get what I want in a single query?
