Indexes On Datasets Created Froma Union All Task
Apr 25, 2007
Hi
I have a package that takes data from 3 different sources, unions them all together and then performs a lookup against a tabel on a different server. The 3 sources of data creates a dataset with quite a large volume of rows (approx 100,000) and the look up takes an age to complete.
Is there anyway of adding an index of sorts to the dataset created by the union to speed the process up? I guess i could output the union all to a temp table and look up against this but wondered if there was another way
thanks
scott
View 8 Replies
ADVERTISEMENT
Aug 5, 2015
Struggling to come up with an overall sum for each individual user when using the below code
SELECT
Practice.ibvStaffCategorisation.StaffId
,Practice.ibvStaffTotalsCL2Y.Period
,SUM(Practice.ibvStaffTotalsCL2Y.ChargeableMinutes) AS Sum_ChargeableMinutes
,SUM(Practice.ibvStaffTotalsCL2Y.NonChargeableMinutes) AS Sum_NonChargeableMinutes
[Code] ....
As I am using union all it shows two values for each user, the problem is I am getting the data via lookup like below
=Lookup(Fields!StaffId.Value, Fields!StaffId.Value, Fields!Sum_ChargeableAmount.Value, "ABSTimeRollingY")
I don't think I can use a Sum within a lookup so at the moment it is only bringing through the first value for each user but I would like it to bring through both.
Is there a way to do this within the dataset or via the query in the table?
View 2 Replies
View Related
Jun 27, 2006
Hi,
I have a view V1 created as a plain UNION from 2 tables:
SELECT * FROM T1
UNION
SELECT * FROM T2
If I create another view V2 just filtering V1 through a WHERE clause (e.g. SELECT * FROM V1 WHERE x < y) I don't get any rows returned.
However, if I write the SELECT clause within each branch of the former UNION, I correctly get all the rows.
Is this a known bug of SQL Server 2000? If yes, is there a SP fixing it?
thx,
smiley61
View 2 Replies
View Related
Aug 28, 2015
The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.
query to find list of indexes on the tables on which the views has been created.
View 4 Replies
View Related
Apr 11, 2006
If you add a "Union All" task to your Data Flow the output columns get configured by the first input you connect to it.
However, if you later want to modify the input columns on the inputs (perhaps after adding an upstream Data Conversion) there seems to be no way to get it to forget the original set of output columns.
Ideally, there would be a button you can press to reset the output columns based on the earliest added input. At the very least it ought to forget its output columns when the last input is removed.
Other tasks offer a dialogue to resolve input / output mismatches but not Union All.
or am I missing something?
jks
View 7 Replies
View Related
Feb 21, 2008
Hi,
Does anyone know of a good resource for displaying data to an excel file from a SQL stored procedure. I'm working with VS 2005 and I have a webpage that will retrieve data from SQL 2000 and display it to an excel file. All examples I've found on the net was not exactly what I was looking for.
Thanks
View 4 Replies
View Related
Aug 22, 2007
Hi,
I have a package in which i have programatically created dataflow task. It used to work fine but now it fails with series of errors out of which one is below
Error 30002: Type 'MainPipe' is not defined. Line 63 Columns 33-40 Line Text: Dim DataFlowTask As MainPipe = CType(DataFlowTaskHost.InnerObject, MainPipe)
It seems all the function and classes referd from following dlls is not working:
Microsoft.SqlServer.DTSRuntimeWrap.dll
Microsoft.SqlServer.DTSPipelineWrap.dll
I say so because i get the following error also:
Error 30652: Reference required to assembly 'Microsoft.SqlServer.DTSRuntimeWrap, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' containing the type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90'. Add one to your project. Line 86 Columns 33-64 Line Text: DtsConvert.ToConnectionManager90(ChildPackage.Connections(""Source"")) These dlls are in GAC and in C:Program FilesMicrosoft SQL Server90SDKAssemblies with same version but still it gives above error.
Thanks
Mohit
View 11 Replies
View Related
Apr 4, 2008
I've created a Union All data flow task which unions string fields from a couple of queries. I found that I needed to increase the length of the string field in the queries. Increasing the length causes an error in my Union All task. I can't seem to find a way to change the property of the fields in the Union All task. Most of the other data flow tasks have an advanced editor where I can change the length or type of the fields on the input and or output. I can't seem to find this for the Union All task.
Is there any way short of deleting the Output column and recreating it? (I like the order of my original output columns)
Thanks,
Bill Webster
View 6 Replies
View Related
Jul 20, 2006
Hello dear Forum,
I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.
As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...
VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...
If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:
Package Validation Error: "Invalid object name 'myToBeCreatedTable'".
Can't i create tables on the fly which i use then in my dataflow tasks?
Kind regards,
Wolfgang
Hello dear Forum,
I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.
As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...
VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...
If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:
Package Validation Error: "Invalid object name 'myToBeCreatedTable'".
Can't i create tables on the fly which i use then in my dataflow tasks?
Kind regards,
Wolfgang
View 2 Replies
View Related
Jul 20, 2006
Hello dear Forum,
I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.
As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...
VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...
If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:
Package Validation Error: "Invalid object name 'myToBeCreatedTable'".
Can't i create tables on the fly which i use then in my dataflow tasks?
Kind regards,
Wolfgang
View 1 Replies
View Related
Jul 19, 2007
Hi all,
We are running SQL Server 2005 Standard edition on a Vista Business machine. Whenever we try to use DTS to import or export data we get this message:
The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered.
Of course we have explicitly registered this dll and others.
Also we reinstalled SQL Server (and did it again. And did it again).
And we did the same for Visual Studio Standard (and again).
Any ideas in this elaborate group?
Thanks,
Tjerk
View 5 Replies
View Related
Sep 10, 2007
Hello!! searching information about how to migrate some date from an old data base (any tipe) from SQL I´v found this:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...)]
Does anybody know how does it works and how to use it????I´d like to know because I have to load data from a text file to a SQL Data Base and this seems to be te fastest an easiest way to do it...Thanks!!!!bye!
View 1 Replies
View Related
Jun 12, 2014
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
View 4 Replies
View Related
Jun 29, 2015
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.Â
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go  CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
View 3 Replies
View Related
Apr 29, 2008
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
View 3 Replies
View Related
Jul 1, 2014
I'm working to improve performance on a database I've inherited, and there are several thousand indexes. I've got a list of ones which should definitely exist within the database, and I'm looking to strip out all the others and start fresh, though this list is still quite large (1000 or so).
Is there a way I can remove all the indexes that are not in my list without too much trouble? I.e. without having to manually go through them all individually. The list is currently in a csv file.
I'm looking to either automate the removal of indexes not in the list, or possibly to generate the Create statements for the indexes on the list and simply remove all indexes and then run these statements.
As an aside, when trying to list all indexes in the database, I've found various scripts to do this, but found they all seem to produce differing results. What is the best script to list all indexes?
View 5 Replies
View Related
Nov 6, 2006
Hi all,
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
Any idea why this could happen ?
View 18 Replies
View Related
Jun 21, 2007
OK. I give up and need help. Hopefully it's something minor ...
I have a dataflow which returns email addresses to a recordset.
I pass this recordset into a ForEachLoop configuring the enumerator as (Foreach ADO Enumerator). I also map the email address as a variable with index 0.
I then have a Execute SQL task which receives this email address as a varchar variable (parameter 0) which I then use in my SQL command to limit the rows returned. I have commented out the where clause and returned all rows regardless of email address to try to troubleshoot this problem. In either event, I then use a resultset to store the query result of type object and result name 0.
I then pass this resultset into a script variable to start parsing the sql rows returned as type object. ( I assume this is the correct way to do this from other prior posts ...).
The script appears to throw an exception at the following line. I assume it's because I'm either not passing in the values properly or the query doesn't return anything. However, I am certain the query works as it executes just fine at the command prompt.
Try
ds = CType(Dts.Variables("VP_EMAIL_RESULTS_RS").Value, DataSet)
My intent is to email the query results to each email address with the following type of data by passing the parsed data from the script to a send mail task. Email works fine and sends out messages but the content is empty. I pass the parsed data as string values to the messagesource and define the messagesourcetype as a variable in the mail task.
part number leadtime
x 5
y 9
....
Does anyone have any idea what I might be doing wrong?
thanks
John
View 5 Replies
View Related
Sep 18, 2007
So I'm reading http://www.sql-server-performance.com/tips/clustered_indexes_p2.aspx and I come across this:
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead. [6.5, 7.0, 2000, 2005] Updated 3-5-2004
Does this mean if I have say a table called Item with a clustered index on a column in it called itemaddeddate, and several non-clustered indexes associated with that table, that if a record gets modified and it's itemaddeddate value changes, that ALL my indexes on that table will get rebuilt? Or is it referring to the table structure changing?
If so does this "pseudocode" example also cause this to occur:
sqlstring="select * from item where itemid=12345"
rs.open sqlstring, etc, etc, etc
rs.Fields("ItemName")="My New Item Name"
rs.Fields("ItemPrice")=1.00
rs.Update
Note I didn't explicitly change the value of rs.fields("ItemAddedDate")...does rs.Fields("ItemAddedDate")=rs.Fields("ItemAddedDate") occur implicitly, which would force the rebuild of all the non-clustered indexes?
View 4 Replies
View Related
Jun 25, 2015
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
create table #fragmentation
(
FragIndexId BigInt Identity(1,1),
--IDENTITY(int, 1, 1) AS FragIndexId,
DBNAME nvarchar(4000),
TableName nvarchar(4000),
[Code] ....
View 5 Replies
View Related
Sep 17, 2006
What is the difference please?
View 1 Replies
View Related
Jul 5, 2006
I typically use DataAdapters and Datasets from the toolbox when connecting to a SQL database. Since switching to VS 2005, I am not able to find (or add) these items to the tool box for web site development.
Any ideas on how I can get them back? Or is there a better alternative to retrieving the data so I can process it? Any help or suggestions would be appreciated.
Thank you
View 1 Replies
View Related
Apr 19, 2007
Can anyone advise me on how to view the contents of a dataset in VS dotnet 2003..
I had filled a dataset from a query...But i don know what happens after filling the qry results.It might be helpful if someone tells me to view the contents of the dataset
View 1 Replies
View Related
Jun 6, 2007
hi
i am using older ado datasets in a borland 6 program.
i need to retrieve the data per record count....
example is that i need all 1000 records but i only want to retrieve them 100 at a time....
how will i accomplish this?
View 1 Replies
View Related
Dec 28, 1999
Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
View 1 Replies
View Related
Oct 25, 2007
I have become a big fan of the datasets in Visual Studio 2005. I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention? I have read that using stored procedures is a good method for prevention. Should I be using SP rather than SQL within my methods in the data table?
View 5 Replies
View Related
May 3, 2007
Hi,
Is there any possibility of combining data from two datasets to a single report item. If there is any thing that we can do, can any one please tell me what to do, so that my problem will be solved.
Thanks
Rajeev
View 1 Replies
View Related
Mar 27, 2008
Hi,
I have a table which has over 450,000 records in it, I have now split this into 4 so each table has around 100,000 records in it but I'm still having the problem of the data being returned really slowly.
What I need to do to this data is group it by a code and show the total for each code for every month of the year (this is currently based on one column and selecting the data accordingly). I have created views and put some indexes onto my table but the results are still being returned slowly. Does anyone have any suggestions of how I can speed this up?
Thanks
Gemma
View 5 Replies
View Related
Sep 6, 2007
Hello experts,
I have a report with four different datasets that slice certain record by different days of the week. I can do the individual summing for each dataset by putting a , "datasetname" at in the expression. I also have 4 matrixes in the report that are grouped on certain transactions within each timeslice. My problem is that if I use the dataset name in the fields, the groups get ignored. Do I have to put the dataset name in each group expression? for example, here is my group expression...
Code Snippet
=iif(Left(Fields!TestName.Value,4)="VTAM","VTAM Logon",
iif(Left(Fields!TestName.Value,4)="CICS","VTAM Logon",
iif(Left(Fields!TestName.Value,4)="Shaw","SHAW Main Menu",
iif(Left(Fields!TestName.Value,7)="Inquiry","Inquiry Menu",
iif(Left(Fields!TestName.Value,6)="Search","Search Menu",
iif(Left(Fields!TestName.Value,6)="SEAX A","SEAX Auxiliary Search Menu",
iif(Left(Fields!TestName.Value,8)="SEAX - B","SEAX",
iif(Left(Fields!TestName.Value,4)="STLN","STLN","Other Value"))))))))
Not sure where I put the dataset name in this.
Thanks,
C
View 1 Replies
View Related
Jan 2, 2007
How do you join two datasets in reporting services... one from sql and another from oracle?
Thanks
View 2 Replies
View Related
Aug 8, 2007
Hello all,
I have a report with a table and a chart. It uses dataset1 as the data source.
All works fine.
I create a new dataset called dataset2.
The queries are exactly the same. The only differences between the 2 datasets is the database server and the fact that one of the columns is a smallint (in dataset2) and an int(in Dataset1)
I change the datasetName property of both the table and the chart to use dataset2.
When I run the report I get a conversion error stating that there was an overflow of int2 while using dataset1. I have verified the report is not using dataset1 anywhere. If I delete dataset1 and run the report the error goes away. If I add it back, I get the error again. Why is the report looking at dataset1 if it is not referenced at all in the report? Does SQL RS cache the datasets and verify each when it compiles?
regards,
Bill
View 9 Replies
View Related
Apr 29, 2008
If I have a dataset that has ID and CODE, and another dataset that has CODE and DESCRIPTION, is it possible to relate these two datasets to one another so I put the ID and Code in columns 1 and 2 and then put the related DESCRIPTION in column 3 on the report table layout? In other words, can you "JOIN" two datasets, or must all that be done in the query and create a unified dataset for the report contents?
View 6 Replies
View Related
May 31, 2007
I have a report that I have created with multiple subreports and datasets. There should be a better solution so I am asking the question here.
The report should display like this:
Object
Object Title
Budget
Curr-Spent
Ytd-Spent
Ytd-Encum
Post-Ytd
Balance
% Remaining
4100
EMPLOYEES
$380.00
$250.93
$343.67
$0.00
$0.00
$61.33
15.94%
4102
EMPLOYEES - TEMPORARY
$149.00
$110.75
$139.70
$0.00
$0.00
$47.30
30.16%
4201
EMPLOYEE BENEFITS
$138.00
$73.16
$12.60
$0.00
$0.00
$32.40
25.28%
Each column represents another dataset.
Each column right now is a subreport so I can use the object as a parameter to the subreports dataset. Each column needs to have the object as a parameter.
Is there a way I can pass a field from the first dataset as a parameter for the next dataset without using subreports?
View 4 Replies
View Related
