Can Someone Please Tell Me How To Go About Changiing A Datasource/dataset Schema?
Apr 17, 2007
I have managed to cobble up a VS2005 PPC app that has 10 or so tables in it. Of course when I started binding to the tables in my app, pretty soon I ran into an error I had made defining a datatype for a column in one table. Single instead of string. I have spent most of the day trying to modify something to change this column datatype. No luck.
1. Seems you cannot edit the schema in VS2005 to modify the datatype. After messing around, now the project will not open at all.
2. Cannot get the ADS wizard to go anymore, so cannot make .mdb schema change and pull down updated column/table.
3. Thought SQL Server 2005 had a tool you could just open up a .sdf file and change the schema. No luck. At least no intuitive way showed itself after I installed it.
4. Thought SQL Server Express Editition could open such a .sdf file and modify it so installed it. Again, could not figure out how this works either.
I suppose I could start over from scratch and create a new app datasource/dataset in VS2005, but I have done that so many times before, I am getting crabby thinking about doing it again. There must be a better way??? Isn't there?
Firstly, i have a solution that is made up of multiple projects. One of these projects contains the majority of the business based datasets that are used throughout the applications.
One of the front ends that I have in the solution is a web site. I am wanting to use the report viewer control to display a report in local mode. I created a new report and i am trying to set its data source to one of the pre-existing datasets that I have and can not figure out how to do this.
Also these datasets do not have adaptors I populate the datasets manually. So the second half of what i need to know is once I have told a report to use one of my existing reports when do I do the population of the dataset.
Lastly if the answer for how to do this is different in local vs. remote modes i would be interested in what the difference is in this scenario.
Hi !!! I have a problem, I don't know how to do... I have 2 dataset from 2 differents XML dataSource... I want the result of one dataSet to be a parameter for the second !
I try to add a parameter in param tab, with expression, I choose DataSet and =Sum(Fields!ID.Value, "DataSetID") but when I execute the query, the prompt for the param appear, but the value of id was <NULL>...
Someone have ideas to resolve this problem ? thank you for your help !
I have a VS 2005 Web Appplication solution that uses SQL Server Reporting Services 2005. One of the projects is a Report Server Project. In this project I have an RDL file that has: 1.)a non-shared XML DataSource (no credentials), 2.)a DataSet with a Command Type of Text and a Query String that is as follows: <Query> <XmlData> <Root> <LOAD LOAD_ID_PREFIX="" LOAD_ID="" TRUCK="" DIAGRAM=""/> </Root> </XmlData> </Query>
3.)a field that comes from the Database that is of VarBinary(Max) that is a JPEG Image
What happens is, is that by doing #2 above, at design time I can create tables and hook into my datasets and get the report all designed out the way I want. Then at runtime, I have a Stored Procedure that uses the "FOR XML AUTO" option so that I can represent my results from the database in an XML fashion which will allow me to replace the XML Schema in between the Root tags above (in #2) with my actual data (which still has the same schema). I then save the modified RDL to a database table field and move on to happily ever after......in this case, not. In this case, since an VarBinary is involved, unless I want to get a pointer back for the Diagram Column, I have to use the "FOR XML AUTO, BINARY BASE64" option. This returns an Encoded string to represent the Binary value that was in the Database....and this is the partial source of my angst....read on.
My approach that I described above worked flawlessly in all cases where an image wasn't involved. This report is the only report in the whole system I am working on that will have an image and it is the last one being done, so that's why I didn't run into this earlier.
I have tried all kinds of things at different levels, but the thing that I have tried that seemed to me to have the most potential was to use Embedded Code to take the Encoded ASCII Value and convert into something binary that the SSRS Reporting Engine likes (so my image will show), but I have been unsuccessful thus far. The error message I keep getting now from my Embedded Function is: "Invalid length for a Base-64 char array", which I have not been able to resolve thus far....and I do know that the Binary value in the database is good becuase I can see it in pages in my application otherwise.
This aside, is there anyone out there that knows how to take the value of a VarBinary(Max) field and insert it into the Text (XML) DataSet in the markup of an RDL file....and have it appear correctly when the report is rendered in something like the standard Report Viewer Control (or even when being previewed at design time inside of Visual Studio for that matter)?
Just to be clear, the root of my problem, as I see and understand it, is that I have a VarBinary value in a database that is being converted to a string (ASCII...I am presuming) due to how I am writing my stored procedure and then needs to go back to something else binary that the SSRS Engine will accept so that I can see my Image in the report when I render it.
I am wishful that someone out there understands my problem and has run into this and has a solution......but I am wishful of winning the lottery, too, and that hasn't happened yet...............
Hello,I'm using a typed dataset to access my database (SQL Express); I have a table that returns data, which two fields are null; I'm getting this error:{"Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."}There are no foreign-key or unique key constraints, so it has to be a non-null issue; I changed the two fields in the table to have a null value of Empty instead of Throw Exception, but I don't understand what the problem is coming from... I'm at a loss.Thanks.
I have some questions about creating SQL Server CE databases. Based on my experiments and what I've read on these forums, it looks like there are a couple ways to create a database schema. I can edit the database schema via the Server Explorer in Visual Studio, or use an external program like SQL Server Management Studio and somehow convert those files to .sdf files.
I find Visual Studio's built in tools to be cumbersome to use and limited in functionality, and using SSMS seems like a roundabout way of approaching the problem. I understand Microsoft will be releasing better tools with Orcas, but in the meantime, I'm wondering if there are alternative ways to generate database schemas.
For instance, I find Visual Studio's DataSet designer fairly easy to use. The DataSet designer generates schema definitions (.xsd files), and an instantiated DataSet can both read and write schema definitions via Read/WriteXMLSchema. Furthermore, DataAdapter's Fill and FillSchema methods can be used to push a schema from a database to a DataSet. So, can I somehow go the other direction and push a schema from a DataSet to a database? It seems like all the tools are there...
For example, if I create the DataSet schema, could I use a small app to create a new .sdf file, instantiate a DataSet, write the schema from the DataSet to the database, and then save the .sdf file? Or given the generated .xsd file, is there any way to create a SQL database from that?
All- I'm a newbie working on a VWD project. A couple questions arose when attempting to work with a project's dataset and its underlying SQL Server Express database:
Is it true that when changing the schema of a database in SQL Server Express through the VWD Database Explorer, one must take care to manually make parallel changes in the dataset? For example, if you make a relation between two tables in Database Explorer, you must do the same with the counterpart tables in the dataset? For example, I find that if I delete or create a relationship between two tables in the database, I must manually do the same in the dataset.
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
I am Creating Click Once Windows Application with Reporting Services 2005.I have created Report and Published on Report Server.In my windows application I am successfully able to view my published report through report viewer control.
Now in my application I am getting a dataset from my custom webservice. I want this dataset data to be added to my report as datasource at runtime on Client Side ,as my report is on Report Server.
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
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.
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
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
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
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer. but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.
I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value, Nothing)) Released_DT = a date - 09/03/2015 or 09/02/2015 Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
Hi every body... I have a probleme I have a web Services which contains a method getValue(IDEq (int), idIndicator(int), startTime(dateTime), endTime(dateTime)) I need to call this method. But my problem is how pass parameter ? I see the tab Param but it isn't work as I wait,... maybe I do a mistake...
I want that statTime and endTime are select by the user via a calendar for example... now idIndicator and idEq was result of an other dataSet from a xml datasource...
But I don't how integrate dynamically... I try to enter a parameter via the param tab, and create and expression : =First(Fields!idEq.Value, "EquipmentDataSet") but when i execute the query, the promter display <NULL>... So I don't know how to do and if it is possible ! I hope someone can help me ! Thank you !
I working on a form using an SQL DataSource. I am going to add a where clause. In that where clause a value from a label will be passed from the user. How do I pass to the HTML Source the label value from the where clause. The value can change from user to user. Sample below: SELECT [KeyRolesID], [KeyRolesDesc] FROM [KeyRoles] WHERE ([JobCodeFamily] = @JobCodeFamily). I want to replace the @JobCodeFamily which is currently coded with a value from the Sql DataSource in the design form with a label value entered by the user.
but it doesn't work, I tried use a pure asp.net web service and it works. can anyone help me to set the connecting string for datasource and query for dataset when it using wcf as the web service? Thanks in advance.
I have a question about the datasource that we create on the Report Manager. What is it used for? In the below connection string which is given in the "Connection String" text box of a datasource link:
What is this datasource used for? Is it used only to use the ReportServer database or even our queries incorporated in the RDL files will be run against this datasource? I think the latter should not happen because while creating RDL files also, we provide a connection string to run the stored procedures against that datasource.
Please let me know about the same.
Thanks a lot and let me know if the question is not clear.
I have this web store that I have been creating. When a customer goes to check out, he has to log in, then he is redirected to a page where he can view/add/or edit shipping and billing address. I have based all the sql statements on the profile username, adding records and retrieving them works just fine. When I go to change something in the info it uses an sql statement that updates based on "Where AccountUserName = @AccountUserName", I have @AccountUserName set to Profile("Username"). Keep in mind this works fine for adding new or bring up current records. I even put in code in the updated event for the sql data source to post a msgbox telling me how many rows were affected, it says 1 even though I dont see any change in the data. What am I doing wrong here, it's driving me nuts, its just a very simple update.
My god.All I want to do is post the contents of a web form to a database sql express 2005 or access using C#. Why can I find nothing for this very simple process online?Can you tell I am totally frustrated? So lets say i have a few text fields and I want to click submit and have that entered into a table. I use C# for code behind. So I can do basic C# programming and I can to webforms with visual web developer 2005 dragging and dropping for textboxs and a button to the aspx page. But then the mystery begins for me. How the hell do I simple post that form data to the table. I understand connection string basics. I aslo can design and create tables. But tying this all together is becoming a problem. I don't want to use gridview formview detailview or any of that canned UI stuff. I also don't understand VB and when I see VB examples they only cloud my already cloud ASP.NET world. Please help by posting examples, and maybe some links or books to add to my newbish collection. Thanks,Frank
