My report's data source is a stored procedure which takes 4 parameters. If I execute the sp from the data tab it returns the correct data and columns; but if I right click the Report Datasets from the Datasets view and click on refresh I'm always missing one column. Is this related to the way the sp was written? Anybody had same expericence before? Thanks!
Is it possible to refresh an identity column so that it re-numbers all columns in order starting with the identity seed without having to drop and recreate the column?
View 2 Replies View RelatedHi
I always seem to come back to dataset issues. Nonetheless, here is my problem:
I am retrieving data using a stored procedure that lists the number of bookings per hour and the revenue that generates also per hour. So, you enter a single date in the stored procedure, for example 2001-10-01 and it will bring back 24 rows corresponding to the 24 hours (obviously) and two columns, one displaying the number of bookings and one displaying how much revenue.
Pretty simple so far.
I have a report using SQL Reporting Services that allows users to enter two dates and therefore you get two tables with the relevant dates data. For example, table one has date 1's data and table 2 has as you'd expect date 2's data. Both tables use different datasets.
The reason why I am using different tables and different datasets is that the stored procedure I use only allows the use of one date at a time therefore, I use a second dataset to generate the second date's data.
The problem occurs when I want to calculate a difference between the two date's data, for example have another table that shows the differrence between table one and table two for bookings for each corresponding hour.
Anyone have any ideas as to how I could achieve this?
Hi, I was wondering wich is teh easiest way to solve this:
I have one data set that comes from a sql query with many columns.
I have another data set that comes from a txt file that I am reading.
Both data set have one column in common (for example ID).
In the sql data set, I have many rows and I want to keep only the ones that have the same ID from the data set of my txt file. It looks like a inner join if we talk in sql terms.
So, which is the easiest way to solve this?
Thanks!
When we use Partition switch and load the data to a table, can we refresh the indexes for specific partition, so that we don't need to rebuild / refresh for complete is this possible ?
View 1 Replies View RelatedI have table which has 5 columns(col1,col2,col3,col4,col5) very simple and some data .
I would like to create a tabular report datasets on the report like shown below with grouping on col1
like
col1 some static text and dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
col1 some static & dynamic text
------------------------
col1 col2 col3 col4
data data data data
------------------------
Note I need to keep all the tables on the same page and when exported in excel they should come on same page sheet.
My report has two data sets that hold inventory from two different departments.
ds_DeptA and ds_DeptB
I have a table, that pulls the DeptB status of DeptA record and displays it. This returns empty when the lookup fails to make a match, which is fine. Typically means DeptB does not have the record yet. I need to count these empty (null) feilds and populate it in a Text box outside of the table.
I just can't figure out the syntax with multiple datasets. I can't use the lookup expression as part of the count expression since the count expression is not contained in a table that has a dataset.
table: ds_DeptA
fields:
ID
Name
date_set_to_DeptB
<<Expr>> =Lookup(Fields!ID.Value,Fields!DeptA_ID.Value,Fields!DeptB_Status.Value, "ds_DeptB")
I have a web page that has been in production for over a year. On occasion the web app users report sporadic errors which last a few minutes then go away. The page loads up a generic DataSet, and then that DataSet is assigned to a datagrid on the page. The error is that a column that is expected to be in the dataset is not found. I know this column must exist in the database because its a required field. After we reset IIS, then all returns to normal and the errors go away. Below is the stack trace.
Exception: System.IndexOutOfRangeException Message: Cannot find column LastName. Source: System.Data at System.Data.DataTable.ParseSortString(String sortString) at System.Data.DataView.UpdateIndex(Boolean force) at System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, DataFilter newRowFilter) at System.Data.DataView..ctor(DataTable table, String RowFilter, String Sort, DataViewRowState RowState) at MySystem.Web.GuestSearch.ShowResults(DataSet ds, Int32 currentPageIndex) at MySystem.Web.GuestSearch.LoadResults(Int32 currentPageIndex) at MySystem.GuestSearch.btnSearch_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain()
Our DBA can find no problems on the database side and our web servers don't log any system event problems either. I do not understand why resetting IIS helps the issue. I need to find the real cause of the problems rather than just resetting IIS all the time. Any ideas?
I have a SSIS package that processes a CSV style file. For illustration purposes lets say the file is as follows:
R1C1, R1C2, R1C3
R2C1, R2C2, R2C2
R3C1, R3C2, R3C2
So I setup a flat file csv connection with comma as column delim, CR/LF as row delim, etc. Everything works as planned and the package executes.
Now lets say the file comes in wrong and has two columns instead of three. It looks like this.
R1C1, R1C2
R2C1, R2C2
R3C1, R3C2
The SSIS file manager reads the file as two rows vs failing. So it reads the above as this...
R1C1, R1C2, {LF/CR}R2C1
R2C2, {LF/CR}R3C1, R3C2
This is obviously not right. You get similar issues if they send an extra column where data is just added to the last column. How do you get SSIS to fail or error out if the column count is wrong?
I have table with column having values 1,2,3,5,6,10.
I want to get the missing values in that column between 1 and 10 i.e., min and max... using sql query.
I want to get the values 4,7,8,9.
I have a server which is not running optimally and I checked the default trace. I have around 600 entries in the default trace which are all Missing Column Statistics and the database is tempdb.is_auto_create_stats_on and is_auto_update_stats_on are both 1 for tempdb.
View 2 Replies View RelatedSQL 2k, DDL below.I have a simple table with the following data:fldYear fldCode1 fldCode22000 ABC1 ABC122000 ABC1 ABC132001 ABC1 ABC122002 ABC1 ABC122002 ABC1 ABC13I need to know, for every distinct combination of fldCode1 andfldCode2, if there are any years missing.For example,SELECT DISTINCT fldCode1, fldCode2 FROM MyTablereturnsABC1 ABC12ABC1 ABC13I need to know that in 2001 there was no entry for ABC1/ABC13Thanks!Edwardif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[MyTable]GOCREATE TABLE [dbo].[MyTable] ([fldYear] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[fldCode2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GO
View 2 Replies View RelatedI'm a little bit unsure...
declare @t table
(col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10) )
insert into @t values ('A123', 'Test', '','')
insert into @t values ('', 'Test 1', 'Y','N')
[Code] ...
Which comes out as
col1 col2 col3 col4
(No column name)
A123 Test
1
Test 1
Y N 0
Y N
0
A125 Test
1
Test 9
Y N 0
N Y
0
but what I would like is col 1 populated As A123 until it hits A125 then populated A125 etc. I was thinking about using the iff but not getting anywhere fast.
Create Table Sample (ID int not null primary key, RefID int , SeqNo int , Name varchar(10) )
insert into Sample
select 1, 1000, 1, 'Mike'
union
select 2, 1000, 2, 'Mikey'
union
select 3, 1000, 3, 'Michel'
union
select 4, 1001, 1, 'Carmel'
union
[code]....
select * from SampleI have here sample data given. What I want to do is, I want to check the RefID which is not having proper order of sequence number. If you see the RefID 1000, 1001 they are having properly sequence order in SeqNo field. But it is not in RefID 1002. RefID 1002 does not have proper order. It is because user has deleted a row which was having seqno 2. So i want to get what are all the RefID's are not having properly sequenced. So that I would be able to know these are all the RefID's are affected by delete statement that was done by user.
Getting events in the default trace saying missing column statistics on a column...
1.The column is the primary key column ( identity )
I'm trying to import XML Data from a .xml file and then running openXML command to insert relevent data into SQL server 2005.
First I'm importing XML records with help of Script Task(In SSIS) in batch of 1000 records in temporary String n passing this to Stored procedure ,which parses XML String with help of "sp_xml_preparedocument" .This SP it gives following error..
"An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name."
I'm clueless about error,can some someone guide to workaround?
I have created a tabular model with VS 2013 Ultimate. I deployed the model several times and everything works great with Power Pivot.
One of the dimension tables, "Age", has an integer field that is null. I updated that column in SSMS to have values, processed the table in VS and the "Age" column now has integer values.
I deployed the model again, connected with Power Pivot, and the "Age" column still has not data. I refreshed the Excel spreadsheet several times, started a new one, deployed several times and there is still not data in the "Age" column even though it clearly exists in VS.
I opened SSMS on the Analysis Service and queried the table in question and it also shows no data in the Age column even though it is in the data table and the VS model.
I can't find anything different about this column than others that contain data. How can this be happening?
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
I need to compare the contents of a column.
I have a table with 1 column. Below are the sample contents..
DateInterval
2012-06-01 00:30:00.000
2012-06-01 01:00:00.000
2012-06-01 01:30:00.000
2012-06-01 02:00:00.000
2012-06-01 02:30:00.000
[code]....
as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:
DateInterval
2012-06-01 18:00:00.000
2012-06-01 20:30:00.000
if it is not doable, we can get the nearest record of all missing records like this:
DateInterval
2012-06-01 17:30:00.000
2012-06-01 20:00:00.000
or this:
DateInterval
2012-06-01 18:30:00.000
2012-06-01 21:00:00.000
or this:
DateInterval
2012-06-01 17:30:00.000
2012-06-01 18:30:00.000
2012-06-01 20:00:00.000
2012-06-01 21:00:00.000
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
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
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?
sql2000
I have an existing data warehouse that is refreshed nightly from system1.
We will be moving to a new system2 1/1/06.
The old system1 will be phased out by the end of 2006.
Several tables on system2 will have different keys.
I have to move the data from both systems in the data warehouse.
Here's the issue.
I have to totaly refresh the data warehouse nightly.
Once system2 is phased out how do I keep the data from system1
and refresh only data from system2.
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 RelatedHi,
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
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
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
How do you join two datasets in reporting services... one from sql and another from oracle?
Thanks
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
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 RelatedI 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?
Hi All,
I have got two datasets. Some 'normal' rows with state values and an addtional dataset with the translation from state value to plain text (value / text pair).
A table object is attached to the first dataset, but I would like to show the plain text from the second dataset in a group header. So I'd like to do some kind of 'look up in another dataset'.
I wasn't able to find a function that could help me solve this problem. Does anyone has any ideas on this?
Kind regards.
Is it possible to connect to SAS dataset using MS SQL Server 2005? If yes, then please explain how do I access SAS data source.
View 1 Replies View Related