VS Built-in Query Builder - Not Possible To Save Diagram Pane Content
Feb 18, 2008
I have VS2008 Express and I am using Sql Server Compact Edition 3.5 which is distributed with it.
As I experienced, there is no possibility to use this database from Sql Server Management Studio (because it is version 3.5 and Management Studio demands version 3.1).
So, the only possibility to design a query is to use the built in Query Builder in Visual Studio Express. This query builder does not save the Diagram Pane content layout. I mean, when a query has 10 tables, I place these tables in the Diagram Pane in some order that makes sense (e.g. to make all relations visible). After saving and reopening the query in Query Builder, all tables are on the left side, in a complete disorder.
Is it possible to somehow save the Diagram Pane content along with the SQL ??
1)I created a linked server to a oracle database, works fine. Now I am writing queries, using "Query Desgner", when I do a "Add table" linked server tables do not appear. Is this a limitation. I can get around the problem by creating a view.
2) When I use "Microsoft OLE DB Provider for Oracle" for linked server, some times connection seems to get lost. On one occassion, the next day it was fine, it re-established itself.
I'm working on a new SSRS implementation, and have created a Report Model against an SSAS database. I launced Report Builder and can see and select the model in the list, but the OK button at the bottom of the pane is greyed out so I'm unable to actually start building a report against it. Any ideas on what I need to change to make the button work? I have already verified that I have full access to the data source as well as the model, in addition to the underlying SSAS DB, so I don't believe it is a permissions issue... But I'm certainly open to any suggestions.
Either edit the query from Dataset edit dialog or in the query builder (switch the design mode to show the query string), I can't save the query and always get the following error:
TITLE: Microsoft Visual Studio ------------------------------
The query cannot be retrieved from the query builder. Check the query for syntax errors. Reporting Services will continue to use the most recent valid query.
hi there, i have a payment page which uses javascript to calculate payments and what remains etc. That all works fine, the problem i have is that when you are finished and you go to the next page it should save all of the records to the order table where the order_id = a querystring. Now i have tried using an insert method and an update method, not really sure whats the difference when putting data into an existing record. What happens though is that i can save the contents of labels etc but it wont save the content of dropdown lists (of which there are 2) i just cant work it out, i have posted my code below string strOrderID = Convert.ToString(Request.QueryString["OrderID"]); string strDiscountPercent = Convert.ToString(5656);double dblDiscountMoney = Convert.ToDouble(txtDiscountMoney.Text); string strPaymentMethod1 = ddlPaymentMethod1.SelectedValue;double dblPaymentAmount1 = Convert.ToDouble(txtPaymentAmount1.Text); string strPaymentRecipt1 = txtPaymentRecipt1.Text;string strPaymentMethod2 = ddlPaymentMethod2.SelectedValue; double dblPaymentAmount2 = Convert.ToDouble(txtPaymentAmount2.Text);string strPaymentRecipt2 = txtPaymentRecipt2.Text; string sConnectionStringCustInfo = "my connection string"; SqlConnection objConnOID = new SqlConnection(sConnectionStringCustInfo); //This is the sql statement.using (objConnOID) { objConnOID.Open(); string sqlUpDate = "UPDATE tbl_order SET discount_Percent = " + txtDiscountPercent.Text + ", discount_money = " + dblDiscountMoney + ", payment_method1 = " + strPaymentMethod1 + =========== this is the problem line here! ", payment_amount1 = " + dblPaymentAmount1 + ", payment_ref1 = " + strPaymentRecipt1 + ", payment_amount2 = " + dblPaymentAmount2 +", payment_ref2 = " + strPaymentRecipt2 +
"WHERE order_ID = " + strOrderID;
SqlCommand objCmd1 = new SqlCommand(sqlUpDate, objConnOID);
try {
objCmd1.ExecuteNonQuery(); }
and because it throws an error it wont update the database with any other record. does anyone have any ideas, i have spent hours last night trying to figure this one out and im sure its simple! but i need some help!
Prior to SP2, when you clicked the save button it would take you to the root of Report Server. With SP2 (in old-style Report Manager mode, not Sharepoint integrated mode) it takes you to the /Models directory by default when you click save. Is there a way around this?
If I launch Report Builder via the following URL, it does fix the problem. But it doesn't let me pick table/matrix/chart. I would like to override the default save location AND be able to let the user pick table/matrix/chart:
I'm developing my first Analysis Server report in SRS. Everything was going well when I discovered the filters & parameters pane was missing in the MDX Query Designer in data mode. I have my columns and rows but need to drag a dimension into the filters & parameters pane in MDX Query Designer and set a filter and parameter on it. Any idea how to make it viewable?
I have environment that use SSRS 2005 ad hoc reporting.
One of the users (out of 200) is unable to open or save reports in the report builder.
the user succeed to open the report builder, when he try to save report that he build from shema he get error. as well when he try to open an existing report that build in the report builder he also get an error,
On open the error is:
The numeric portion of 2pt cannot be parsed as a unit of the type point.
The Initilizer for 'Microsoft.ReportingServices.Design.Constants' threw an exception
On Save the error is:
System.IO.StreamReader: Deserialization failed: The type initializer for 'Microsoft.ReportingServices.Design.Constants' threw an exception. Line 2, position 2
Please note that I succeed with second user to create ad hoc report on the first user
computer. I also failed to with the first user to create ad hoc on another computer.
I have created a database with several tables. I want to create a database diagram to show the relationshipbetween the tables. Below are the steps from the SQL Server 2005 documentation on how to create a database diagram. The problem is that when I right click on the Database Diagrams folder I am only given two options. They are:"Working with SQL Server diagrams" and "Refresh"There is no menu choice to create a new database diagram. Can anyone tell me what the problem here is?
To create a new database diagramIn Object Explorer, right-click the Database Diagrams folder or any diagram in that folder. Choose New Database Diagram on the shortcut menu. The Add Table dialog box appears. Select the required tables in the Tables list and click Add. The tables are displayed graphically in the new database diagram.
Hi, I am trying to filter data within my report by a date range (FromDate - ToDate), which is using a cube as a datasource.
My Issue: I have the filtering working ok but if i select a date which is outside the range of the data within my cube for example if i select the starting date for the range as 1/Jan/1965 but by data starts from 15/Jan/1965 then no data is returned.
Within the MDX query within the STRTOSET function i am using 'constrained' which is around the date parameter i.e. StartDate for Range.
My question is has anyone or is it possible to use date values outside of the range of the data within my cube and get a correct dataset returned. If so could you please explain how with an example.
In Report Builder(SSRS 2005 sp2), is there a way to automatically save a report the user is working on? The problem is that somtimes a user would be spending a long time working on a new report and the connection would timeout and the user has to start all over again.
I'm a relative newbie to ASP.NET development (web development in general) so please forgive the ignorance. ;-) Ok, I'm using VWD to design and test my application and I'm using the standard membership provider system. I've read in various places not to muck around with the aspnet_* tables as it can cause havoc hehe. Therefore, I've created my own Member table to accommodate more fields that the aspnet_Users table does not have (since I don't want to mess around with that table) such as address, city, town, zip, birth date, etc. Obviously, I have a field in Member that corresponds to the userId primary key in aspnet_Users. Now, there are times when I need to display the username of a person's profile on a page. SELECT aspnet_Users.UserName<br>FROM aspnet_Users, Member<br>WHERE querystringid=Member.memberId AND Member.userId = aspnet_Users.UserId Now, this seems fine and dandy when there are few entries in the aspnet_Users table, but what if I tried that query when aspnet_Users grew to a hypothetical one million entries?? Does anyone know if those fields in aspnet_Users are properly indexed? I can't seem to determine one way or another with VWD's database explorer. I don't want to run the risk of a full table scan. But if those fields aren't indexed, I don't know if I should modify that table in any way. What to do? TIA
We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).
I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?
I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.
Thanks in advance ripern
-- Retrieves the value of column @columnName for credential id @credID ALTER PROCEDURE [dbo].[PMC_User_GetUserValue] @credID int, @columnName nvarchar(50) AS
DECLARE @userDataTable nvarchar(50) DECLARE @userDataID int DECLARE @statement nvarchar(500) SET @statement = ' '
SET @userDataID = (SELECT PMC_UserMapping.fk_userDataID FROM PMC_UserMapping INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id WHERE PMC_User.fk_credentials_id = @credID)
SET @userDataTable = (SELECT PMC_UserType.userDataTable FROM PMC_UserType INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id WHERE PMC_User.fk_credentials_id = @credID)
SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)
-- Checks whether the given column name exists in the user data table for the given credential id. IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName ) BEGIN EXEC (@statement) END
I have a column with datatype varchar(2000). When I use the select statement to query for this column I only can see the content up 256 characters. How can I query to view the entire string above 256 characters?
Hello, I'm learning about asp .net and I've created a simple application with SQLServer integration. I have 3 text boxes where I can insert text and then use them as parameters for the Select query string of a SqlDataSource which is then bound to a DataGrid.My question is: Is there any default configuration I can use with the SqlDataSource which do not add a given field as a parameter to the WHERE clause if the text box content is empty ? Sure I can sweep through all my text boxes, check if they're empty and dynamically build the WHERE clause with correct parameters, but is theres a built-in, more elegant way to solve this ?Thanks in advance.Arashikage
anyone know how to delete diagram in database that show relationship between tables?or anyone know how to open the existing relation diagram and existing query in database?wonder why my sqlexpress which come wit visual basic web developer 2005 only hav new query button and new diagram button~dont have edit exisiting diagram or edit existing query~==.=~~hope someone help me~
I need to know All those unique IDs that have "start" against them, but have not got "stop" against them, I'm not interested in the other content strings.
I need to query it based on a specific application ID.
there will be many records from each IDnum, but only one start for each. I need to know all those IDnums that have "started" and not "stopped"
1 Banana2 Orange3 Apple Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
We can save query output save as CSV file directly from the Query Analyzer window. I have done it at last few year before. Now I need it.Can anyone please give the one example for the same.
I have a report with a Matrix table. When it€™s fully populated I have 5 rows. These rows are different categories in a CRM system. In these categories there are a lot of Opportunities listed. The thing here is that category 1-4 exist in one table, really a view, and category 5 exist in another different view. So to gather all the information into one single Matrix table to later have subtotals and stuff I just made a little Union of the two select queries.
Now one the column in the table is of course Topic. And from here my client wants to be linked to the right CRM card. The problem I have encountered is that how can I have to different addresses on one single cell in the table?
In the Navigation pane in the cell that corresponds to the Topic, for now I have an address like this: =string.Format("http://{0}/SFA/opps/edit.aspx?id={1}", Parameters!srv.Value, Fields!objectid.Value)
What I need to change when it€™s a category 5 Opportunity is €œopps€? to €œcat5€?and add €œ&etc=10008€? to the end of the address. Any ideas?
PS. {0} and {1} are parameters, one server parameter and one opportunity ID parameter.
Why is it that only SOME of the tables in my database show up in the Entities pane?
There are a total of six tables in my database, yet only three of them are available to me in the Entities pane. I have looked at permissions, etc, and I am using the SA account. It seems like I should see either ALL or NONE of the tables, but that is not the case.
When I change some data in a row in my Results Pane and hit enter, I receive a message that "Data has changed since the Results pane was last retrieved." I click the yes button to "commit your changes to the database anyway," but then I get an error message telling me that my row was not updated. The message says "Error Message: The updated row has changed or been deleted since data was last retrieved. Correct the errors and retry or press ESC to cancel the change(s)." This time, the only option I have is to click the OK button. Can anyone give me some idea as to what I need to do to be able to update the row data in this way? Thanks.
I would like to know if there are any good MDX query builder tools out there. The one that is shipped with AS does not suffice in that it does not support more than 2 axes. Also if there are any are there any that make life a little easier in that they are drag and drop and all but create the statement for you?
I need to create a matrix in SSRS using cubes and in doing so need to write MDX queries, not my strong point. Are there any tools that can help me "auto create" the statement with drag and drop functionality?
Hi people!I'm looking for an SQL query builder application that helps me constructSELECT queries with full respect of underlaying relational model. It wouldbe nice if the app is database independant because my work covers MSSQL,mySQL and Oracle DB.OpenSource and/or comercial suggestions are welcome!*** thanx ***
HI, in a lookup component, when I want to use the query builder, it will show tables from a different connection manager than the one I picked for being the lookup source. E.g. connection manager A points to a schema that contains table1 and table2 and connection manager B points to a schema that contains table 5 and table 6.
When I configure the lookup component to use connection manager B, I still see connection manager A tablles; isntead of seeing table 5 and 6 I see table 1 and 2.
Hello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.
Just learning SQL Server, using MS Step by Step book. I think I should be able to right click in query designer pane and save-as query. I see this option in context menu. When I try to save file, I don't get file dialog to specify file name. I also don't get error. Any ideas?
HI When you right click open table to return all rows, click on the "Show/Hide Grid pane" icon . When you click on any row the first column called "Column" a dropdown shows all the filed names of that particular table. This feature used to work fine untill recently wherein when I click on the column, I don't get to see all the filed names but now it just shows TableName.* i.e. all columns. This is the case when I try to create a view in enterprise manager also. When you add a table to create a view, I cannot see all the filed names . I'm not sure what to do , to get the field names to appear? Is it some kind of setting in enterprise manager. All the info would be appreciated. Ta N
Is there any way to have the results pane auto hide ? Im lazy and dont want to manually (ctl-R) hide each time i run something, then starting writing code again
I am using the ReportViewer control with SRS2005. I have an extremely large report (243 pages) - however. It takes an extremely long time to load the report as well as populate the document map pane.
When one clicks a link in the document map pane, it refreshes the entire page - and thus causes the report to take another 2-3min to repopulate the document map.
However, if I were to access the Report Server directly, clicking a link only causes the main content page to be refreshed.
This is a substantial difference between selectively refreshing as opposed to refreshing the ENTIRE page and hence taking 2-3min between links.
