I have an app that has some stored procedures running queries across a linked server. In our development environment, the linked database is on a box called 10.xxx.xxx.xx. In the production environment, the linked database will be on a server called something else. What I want to do is have the linked server in the development environment named the same thing as the linked server in the production environment, so that I won't have to change the stored procedures when migrating the project into production.
SQL Server 2005 isn't giving me the option to rename or alias the linked server, though. Is there anyway to do this so that I can have the linked server named the same thing on the two different boxes and not have to change the sp's?
I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:
create view MyExcel as select * from XL_SPS_1...Sheet1$
Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."
I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?
How do I get data on my linked report based on my grouped subtotal and grand total from the main report. The subtotal and grand total are calculated columns.
I have a 3 columns in my matrix in the SSRS summary report. Actn_COAST, ActnCITY and NumbOfAccts.
The following is code for my summary report. The results are shown below.
SELECT Distinct ActnCITY, Count(ACCT) as NumbOfAccts, CASE WHEN ActnCITY in ('NY', 'OH', IN, 'NJ', 'SC', 'NC') THEN 'EAST COAST' WHEN ActnCITY IN ('CA'. 'NV', 'UT', 'WA', 'OR') THEN 'WEST COAST' ELSE 'OTHER' END AS Actn_COAST
FROM tbl1 where ACTNDATE between @STARTDT and @EndDT
Code for my detail report contains the following SQL
SELECT * FROM tbl1 where ACTNDATE between @STARTDT and @EndDT AND @ActnCITY = ActnCITY
I have linked my report based on the NumbOfAccts column. I am able to get data if I click any of the NumbOfAccts values related to the state I want. However when I am not sure how to make the subtotal and grand total work. I want when I click on the subtotal of either coast, I should be able to see records of that coast e.g., if I select 37 I should be able to see all the records in East Coast. If I click on the Grand Total, I only want data related to those 2 coasts.
We are currently running SQL Reporting Services 2000 and have the ability to open a linked report in either a new window or tab just by right-clicking on the link and selecting from the context menu. We are looking to migrate to SQL Reporting Services 2005 and when you right click on a linked report link, there is no option to open the linked report in either a new window or tab.
So my question is, is there a way to have linked reports open in either a new tab or window in SQL Reporting Services 2005? I searched this forum but all I saw were some round-about ways of accomplishing this using javascript. We have a lot of reports and would like to avoid altering each one to allow the ability to open a linked report in a new tab or window.
I have tried using the linked report with CreateLinkedReport(...) but I do not understand the output.
initially I have a report with parameter that user needs to specified before generating . I use something like
CreateLinkedReport("test4", "/Report Server",
"/Report Server/xxx/yyy", props)
where "/Report Server/xxx/yyy", is the linked path to that file.
What happen is that this report 'template' has changed to a 'linked' report with specified parameters. and that new linked report 'test4" is created under /Report Server. And this test4 is like a copy of the orginal "/Report Server/xxx/yyy".
What I want is the opposite where the orginal template stays the same. And the new created test4 is the report that stores the specified values.
Does anyone have a sample of a CreateLinkedReport method they are using? I am using Reporting Services 2005 and Visual Basic 2005. I need the VB code and how I need to call it. I'd like to change one parameter, and then send it to select recipients using a specified schedule.
Can I change the SQL statement on a linked report without modifying the original? I'm trying to limit a category by userid. I basically want to add an additional line to the where clause. Any ideas?
I have a report with a hidden parameter that defaults to the logged in user. In this way, a user can only see his own information. However, I now need to give a group of administrators access to change the value of the parameter (to see information for other people). What is the best/most appropriate way to accomplish this?
I thought I'd try the Linked Report, but do not see how I can set the default value for the User ID parameter to the value of the current logged in user in the Reports Manager. Is that possible?
I also tried creating a report with the parameter open but restrictive access, and then use that report as a subreport on a report where the parameter is closed. However, a general user cannot open the subreport in this case because he doesn't have access to the open report.
I updated a report adding parameter expressions. A linked report based on the report I updated didn't get the parameter expressions when I deployed the base report. Anybody else having that issue? It's not much of a problem. I just have to recreate the linked report which is a nusance.
We have a report that prints correctly (in landscape) in the folder that is deployed to but the linked report in a different folder prints in portrait?
This is supposed to allow us to build a linked report with these properties already set. This post works for a linked report that is already deployed. I want to set the properties on the report as I'm deploying the linked report. The instructions listed don't seem to work, it gives type errors and other related things when I try this approach, has anyone else had any luck doing this?
It says: Error : rsElementTypeMismatch (The value provided for the PageHeight field is not valid for its type. It cannot be converted from a string value to the type that is required.)
What do I do to get around this? I tried changing it to a number, but no luck. I'm trying to do this in rs.exe:
Dim props(7) As [Property] props(0) = New [Property] props(0).Name = "Description" props(1) = New [Property] props(1).Name = "Hidden" props(2) = New [Property] props(2).Name = "PageHeight" props(3) = New [Property] props(3).Name = "PageWidth" props(4) = New [Property] props(4).Name = "BottomMargin" props(5) = New [Property] props(5).Name = "TopMargin" props(6) = New [Property] props(6).Name = "LeftMargin" props(7) = New [Property] props(7).Name = "RightMargin" props = RS.GetProperties(basereportpath, props) RS.CreateLinkedReport(name, parent, basereportpath, props)
I have a base folder (Report Folder A) which has several reports. One of these is a report that is simply a subreport of one of the others in the folder (it uses different default parameters, which is why it's necessary).
I then created another folder (Report Folder B) for users who only have access to some of the data in the reports...so they have browser access to Folder B but not Folder A. All of the reports for the users work except for the one that contains the subreport. When I run the report with a subreport I get the message: "Error: Subreport could not be shown". I'm guessing this is because the users don't have access to the base report. Does anyone have an idea of how to work around this without giving the users access to the base report (in Folder A)?
I deployed a report I created to Reporting Services using Management Studio. From here I could create subscriptions, if I choose. I created a linked report in a different folder from the original report. I defined all the parameters for this linked report. I cannot create subscriptions for the linked report - the context menu items are grayed out.
Anyone know why this might be happening and what I can do to correct it?
We have two domains in our organisation, both with transitive trust of one another.
In our main domain we have a report server which is available for all users across both domains to access and use. Most of the time there isn't a problem but I'm having a bit of an issue with users from our second domain accessing a linked report containing 13 subreports.
The main report is fine and all users can view this, however, all subreports show an error message to users in our second domain.
As a bit of an experiment I have even given one user Content Manager rights on the report server but this hasn't solved the problem. I have checked that permissions are correct on ther server, folders and data sources dozens of times and cannot find any reason why second domain users are having this problem.
If anyone has any suggestions on how to solve this I would be grateful for help.
I've got a report server that I recently inherited. I'll be the first to admit that I'm not an expert on SSRS, but I've figured out a lot in the past few weeks.
One issue I'm having (apparently it's always been there, but nobody ever bothered to fix it in the past) has to do with the report subscriptions and linked reports. We have an SSL cert inside IIS. I have SSRS configured to "require SSL connections" in the Report Server Virtual Directory tab.
So the user gets an email with a link to a report. But the link has an "http://" as opposed to "https://." When you click on the link, it throws your standard "page must be viewed on a secure channel." When I manually append an S to the url, it works great.
There has to be a way to automagically add the S to the url, but I can't seem to figure out how. Anyone ever run into this before? Google has not been good to me this morning either.
I am facing an Issue with my SSRS-Reports. I have a stacked column chart in my report and the columns are linked to another report, a sub-report with 6 parameters. Right now after clicking the columns, the sub-report get opened in the same IE-Tab, with relevant parameters and it works out correctly. Challenge is, to open the new report in a new tab / window.
My Current situation: A Chart report, with Action “go to report” on series properties with 6 parametersChallenge: open linked sub-report in a new tab.Info 1: new path of sub-report looks like this:
I have created a word cloud in SSRS 2008 using Jason Thomas's method but I would like to be able to add an action to click on a word to go to a another report using either the word or an ID integer. how to add an action to go to another report?
i am trying to set up a way of renaming a table in a sp.
ive been using
exec sp_rename 'old table', 'new table'
however if i try and specify to database and owner i end up with a weired db.owm.db.own.table name in the outcome. ive tried putting using it without specifying a db name in a sp and then sched job to exec the sp but it doesnt rename, probably because it doesn know which db to look for the ld table name in. however the sched job doesnt fail?
Hello,I have a shared hosting plan which already has aspnetdb.mdf on the server so of course I cannot use that db name for my site...I have renamed the db and now I get an error Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion My question is, how can I make this thing work? All help is greatly appreciated!
Can anybody please tell me how to rename an sql server express. I have an SQL server instance named 'PC075/Sqlexpress'. I have to change it to 'PC075'. Thanks,
if my computer name is x and sql server was installed and therefore took on the default name of x, can I change the sql server name to y at a later date while the computer name remains as x? If so how do I do it?
The sp_addserver is internal recognition. But I want to connect to sql server using the new name y. I am assuming setup needs to be run only if computer name also changes to y and this is not the case.
Is there a way to rename a object across server using the sp_rename sp. I have a job running across server and I one of the steps requires me to rename a object on a different server.
In EM, when you right click a database and go to properties you can see on the general tab the file name,the location,the space allocated in MB and the file group. Is it possible to change the file name and how you achieve it? Where are these names stored? I really appeciate any suggestions. Thank you very much! Franco
I would like to rename my SQL server. Someone suggested SQLServer and we went with it, but after some discussions with other developers we thought that this name would be a hackers delight. So now I need to rename it to something that wouldn't put a "bulls eye" on its back. Any suggestions? :-)
This machine is not in production just yet, so if I had to reinstall SQL it wouldnt' be that bad of an option.
Hello, I want to rename a server NT4 on whom is bases sql server sql 7 sp 2, who is the precautions to set and can you indicate me the procedure. Thank you in advance. pascal
