I was wondering if it is possible to use a generic list object to use as the datasource/data for a report? Right now we use these business objects to display information on our website and would be great it we could re-use those objects and create reports based on them. We are doing some calculations etc in the business layer to build these objects, so if we could re-use, that would be best. Thanks!
I need to have a report with summary and details. The summray list is 'EmpolyeeID' and 'Name'. The details are 'emails' and 'phone numbers'. One Employee could have one or more emails, and one or more phone numbers.
The dataset are 1. select EmployeeID, Name from Empolyee 2. select EmployeeID, email from Emails where EmployeeID = @ID 3. select EmployeeID, PhoneNumber from PhoneNumbers where EmployeeID = @ID
I'd like to have one summary list with dataset 1. The list contains two nested lists, one for email, the other for phone numbers.
I would like to display different addresses in the same list:
I have the query for married couples working, Also, I have a separate query of non-married people working.I would like to create a separate datasets for unmarried people in the same report.-Is there way to setup another the second data set without duplicating the fields from the first datasource. I tried to use alias on the second datasets and it did not work okay.
In general (I know.. lots of factors) but in general will adding more RAM to a SQL box help the performance? I'm working w/ 256MB right now, and deciding whether to add 256MB or 512MB? Any advice
Thanks and sorry so generic but this is a vendored app and they know of design issues but for now that can't be changed.
I want to be able to query with a start date(@start) and then calculate 1 year from there. I figured since the date is of type integer just add 364 and it will give me the day before 1 year. So if its 1998-05-01, Id get 1999-04-31.
Here's the scenario: whenever a change (insert/update/delete) has occured in any of the tables, it needs to be logged by adding the changed table's name, and changed columns' names (if any) into the special ChangeLog table. So, is it possible for me to write a generic trigger that would get the name of the table it just executed upon and names of every column affected by the UPDATE and then insert those into the Log table? If this is not possible, how do you suggest it should be done?
I'm familiar with standard SQL but T-SQL is completely new to me, so please bear with me if my question makes no sense...
How to disappear the generic bar in the report, or to disappear some options (example export). I speak about the bar which in the top of the report or there are the numbers of pages.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows. Table 2: Large table with CLOB, 10,000,000 rows
select CLOB from table2 where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
Hello, Can someone please guide me to online resources or books on how to design generic database? I mean, I can have a "Record" that can have a set of fixed fields like: ID, Title, CreatedOn, etc ... and I can add as many properties I want to that record in a vertical way. Then based on the generic tables present, I can fit in the same table, a Record for Customer, a Record for House, a Record for Order. All share the same set of Fields, but each has its own set of proeprties. This way, I design my database once, and customize it to fit any type of applications I need. Is there something like that or just dreaming? thanks
I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.
I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.
For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @tablename, @code, @desc, and then create a new record in the table specified by @tablename.
I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.
I am attempting to create a generic stored procedure in SQL Server 6.5 which returns a record count of a specific table passed in as a parameter. The stored procedure looks like this"
CREATE PROCEDURE asp_GetRecordCount @tablename varchar(30), @recordcount integer OUTPUT AS /* Generic stored procedure which returns number of records in a table. */ SELECT @recordcount = COUNT(*) FROM @tablename RETURN GO
The problem is that when I save the stored procedure, SQL Server balks at the FROM @tablename entry. Any suggestions? Thanks.
My current project requires me to convert a mysql based software to a more generic one. I started by designing separate db class files and separated the lower level connection queries from the business logic. By doing this, I now have mssql.class, mysql.class, sqllite.class etc..
But am not sure how to handle sql functions in queries. For instance, one of my queries need the use of a date function to add minutes to a db field.
In mysql, I accomplish this using
dbfield+interval '$arg' minute between date1 and date1
But in mssql I cannot use this type of query. It seems I'll have to use date_add() function. How do I handle this situation?
I have a bunch of tables for my basketball club's d/b. I want to be able to interrogate them to build a web page showing player stats. For example, I want a table showing the top 5 scorers, top 5 3-point scorers, etc. What's got me foxed is how to join it all up and then sort it into the top 5 order. The main tables in question are T_Member, T_Fixture, T_Season and T_FixtureTeam:
Where you see the same field name, that field data is common for all tables that it appears in. In other words, for example, the T_FixtureTeam 'FixtureNo' and 'MemberNo' fields are made up of 'T_Fixture.FixtureNo' and 'T_Member.MemberNo' respectively.
Ready? The user will select a season at the top of the page (although there's a default to the current season (CurrentSeason is a Boolean field). The query needs to: - Find all fixtures in that season - Find all members who played in those fixtures - Find the top 5 3-point scorers (or whatever stat I'm gathering) for those matches - Sort the resulting data
I figure I need a JOIN on MemberNo but, as I say, the rest of it has my head spinning.
I'm receiving "a generic error occurred in gdi+" processing error while running a preview of my report inside visual studio. When this report is opened by a client, their browser will just hang (Not Responding).
Other reports don't have error. The report will not render any chart although thousands of record will be need to output.
We want our database to be unicode complaint and i need a generic collation that should do the work irrespective of the fact what data is entered in the database. Can someone suggest me any generic collation for that.
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
Hey I'm using this DAL to populate a generic list called assetDetailListHowever I'm adding a 2nd result set to my stored proc, protected void GenerateAssetDetails<T>(DbDataReader dataReader, ref List<AssetDetails> assetDetailList) { while (dataReader.Read()) { // Create a new culture AssetDetails assetDetail = new AssetDetails(); // Set the culture properties assetDetail.FileName = dataReader.GetString(dataReader.GetOrdinal("filename")); assetDetail.Title = dataReader.GetString(dataReader.GetOrdinal("title")); assetDetail.AltTag = dataReader.GetString(dataReader.GetOrdinal("alt_tag")); assetDetailList.Add(assetDetail); dataReader.NextResult();// this is the 2nd result set AssetContent assetContent = new AssetContent(); assetContent.content_id = dataReader.GetInt32(dataReader.GetOrdinal("content_id")); how do I creatre another list .Add(assetContent); } // Close and dispose of the reader dataReader.Close(); dataReader.Dispose(); } #endregion }
I€™ve got a few Script Task in my dtsx. Up to the moment I don€™t need have generic functions but I was wondering if it is possible to use a such functions. I attach you a sample of I say you:
Here is my situation. I have a fairly large query that I am working on in visual studio (data tab). I am at the point where the program will NOT let me type (add SQL code) anymore. I found that clicking the Generic Query Designer (turning the query builder on) button switches the format then allows me to add more code. Well, this solves the running out of room issue, BUT now it does something to my code and it will not process it. Prior to clicking the Generic Query Designer button, everything worked w/ no errors. I did not change anything, but simply pressed the Generic Query Designer button (turning the query builder on) and now it dose not work now. Just for kicks, I have pasted my working origional code into SSMS, added code and it executed just fine in SSMS. But then I pasted back into visual studio and it gave me the same errors?
Has anyone experienced this? Is there a maximum for lines of code? Why does the Generic Query Designer (having query builder on) change my code? All comments are welcome!
I have a question about staging design using SSIS. Has anyone come up with an ETL design that would read table names from a generic table and dynamically create the ETL to stage the table.
1. Have a generic table which would have table name and description and whatever else that was required.
2. Have a master ETL that would enumerate through the table and stage all the table names found in the generic table.
This way I wouldn't have to create an ETL which would hardcode the names of 300-500 tables and have the appropriate 300-500 data sources and targets listed.
Not sure if I am making sense but I hope someone understands the attempt.
Hello every body. First of all sorry for my bad english but I'm french ;-)
My internship consits into making a generic table exporter (with a table list). Export into csv files.
I have tried 2 solutions : 1 - Create a DTS with a Dynamic Properties Task. The problem is I can easily change the destination file but when I change the table source I don't know how to remap the transformations between source and destination (do you see what I mean ?). Any idea ?
2 - Use the bcp command. Very simple but how to do when tables contains the separator caracter ? for example : If a table line is "toto" | "I am , very happy" --> the csv file will look like this : toto, I am , very happy --> problem to get back the data ( to much comma ). Does someone has a solution ?
Last point is how to export the table structure ? For the moment, using the table structure, I generate an sql query which creates a table (I write this query in a file). Isn't there any "cleaner" solution ?
I have a number of tables that contain data that we want to concatenate as a part of our reporting processes. Instead of building a whole series of functions that each concatenate data from only one table, I want to create a function (or functions that work together) to concatenate data from ANY specified table, since the logic is identical. The basic logic is:
Code Snippet DECLARE @x varchar(max) DECLARE @theReturn varchar(max) DECLARE theValues CURSOR FOR SELECT DISTINCT TOP 100 PERCENT <col name to concatenate> FROM <the table in question> WHERE <the identity column in the table> = <a specific value> ORDER BY <col name to concatenate> SET @x = '' SET @theReturn = '' OPEN theValues FETCH NEXT FROM theValues INTO @x WHILE @@FETCH_STATUS = 0 BEGIN SET @theReturn = @theReturn + '; ' + @x FETCH NEXT FROM theValues INTO @x END CLOSE theValues DEALLOCATE theValues SET @theReturn = LEFT(@theReturn, Len(@theReturn) - 2) RETURN @theReturn
The problem I am running into is how to create the "generic" or "abstract" version of the SELECT statement in the cursor. I can certainly pass in the values surrounded by < and > as arguments, but I cannot figure out how to insert them into the SQL and make it work. I can't create a text string and then use the EXEC statement, because in a scalar function SQL Server won't allow it.
I want to use ONE stored procedure to update/insert about 300 tables in a database. I hoped this code would work but it gives me an error:
ALTER PROCEDURE [dbo].[InsertRowsTick] @tableName varchar, @bid float, @ask float, @last float, @volume int, @dateTimed DateTime AS BEGIN SET NOCOUNT ON; INSERT INTO tableName ( bid, ask, last, volume, dateTimed) Values ( @bid, @ask, @last, @volume, @dateTimed) END
When I (1) commented out @tableName parameter and (2) instead of INSERT INTO tableName wrote: INSERT INTO <realtableName> the code (C#) executed and the table was filled.
How can I use a generic table name as a parameter?
I want to make a report where multiple users can use the same report to connect to their databases and then print out the report with information from those databases. Both databases have the exact same tables and fields but the data that is in them is different. However, I have only been able to figure out how to connect the report to one specific database, and therefore the report always prints out information from that database instead of the user specified one. So let's say I want my report to print out the name that is in the database field Name for any database I connect to, how would I do this?
Hi, I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0. if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in if @Status=0 even if I pass @status=0. What am I doing wrong? Any help is appreciated.
ALTER PROCEDURE [dbo].[MyStorProc]
(
@Status smallint,
@RowCount int = NULL,
@FacilityId numeric(10,0) = NULL,
@QueueID numeric (10,0)= NULL,
@VendorId numeric(10, 0) = NULL
)
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
If @Status = 0
BEGIN
SELECT ...... END If @Status = 1 BEGIN SELECT...... END
It's very easy to make a generic xml configuration file for the connection to the database for all packages my project contains. The connectionstring is the same for all these packages
Now I want to do the same for all flat file sources I have in my project where the connectionstring is not the same for all packages.
For example I use the following flat files in my project:
c:AAA.txt
c:BBB.txt
c:CCC.txt
If I move the packages to the production database the flat file sources are located in another directory.
So in fact for the flat file sources there is a generic part for all files (in this case 'c: ') and a specific part ('AAA.txt', 'BBB.txt' and 'CCC.txt').
Can I indicate this in the package configurations sections? And how?
