What's the best mechanism for creating a reporting database? I have a Microsoft Dynamics ERP system that I'm using and all our reports are currently run of the live production database. I'd like to create a reporting database that has a relatively fresh copy of the live database to run reports off. It appears mirroring is the way to go, but I'd like to get recommendations from people on this.
Is there a good site that explains how to set this up? Any thing to watch out for?
I'm having a problem right now. I'm trying to create an offline copy of a database(live) in my local machine. The database is maintained in a different server machine and I want a copy of the particular DB as an offline copy.
The problem is there's DTS feature in SQL Server 2000 but I don't find any such tool in SQL Server 2005 edition. Can anyone please guide me on how to do this process?
Situation: SQL Server 2000. At my new employer they have a production database on one server and a copy of it that is set to read only on another server which is used for reporting.
#1 They have an SQL Server Agent job on the production server that: (2 times a day)
Backs up the production database Copies the backup file to a directory on the reporting server. (Its pretty big and can take time if there are problems with the LAN)
#2 They have an SQL Server Agent job on the Reporting server that: (scheduled to run 2 hours or so after the job on server 1 has run€¦they figured that it would be a safe bet that the backup and copy process of the first job would be done by then)
Breaks the user connections to the reporting database Performs a restore on the reporting database using the backup file that was copied to the holding directory by the production job. Sets some permissions for various users. Sets the reporting database to READ ONLY. What I would like to do is find a more efficient way to create this reporting database, I have started doing research into DTS methods but would like some opinions from more experienced users.
I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:
alter database DBmirrorTest Set Partner = N'TCP://SQL2.mycom.com:5022'; go
The error message is:
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.
I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)
1. backup database DBmirrorTest on SQL1
2. backup database log
3. copy db and log backup files to SQL2
4. restore db with norecovery
5. restore log with norecovery
6. create endpoints on both SQL1 and SQL2
CREATE ENDPOINT [Mirroring]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER)
7. enable mirror on mirror server SQL2
:connect SQL2
alter database DBmirrorTest
Set Partner = N'TCP://SQL1.mycom.com:5022';
go
8. Enable mirror on primary server SQL1
:connect SQL1
alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go
This is where I got the error.
The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy
Hi I'm new in SSIS I'm trying to create SSIS that can copy tables,permission,index,constrant... to a sqlserver2000 from sqlserver2005. Can any one help stepbystep.
if i have a given database (a model) and i want to copy this database in the same database instance. Is it ok to copy the mdf and ldf file and attach the files with a new database name in the same instance.
Our DB has around 30 SProcs - I need to move them into a script, so that it can be easily added to another server. The way I'm creating the script is to highlight all the SProcs, then copy - in my notepad screen, I paste, which gives me one script, which includes all the individual creation scripts for the Sprocs. However, I'm getting an error when I create the script - Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'SP_MySproc'. The stored procedure will still be created. So - what's a good, and/or easy way to structure the script, so that I can easily find WHERE to put Which SProc Script, in the list?
Hello I am a software developer with minimal SQL server administration skills. Currently I am using SQL Server 2000.I need to know if there is a way to copy a particular table from a database, and to copy the table into a different database.Basically on a project I am working on we are using a table named "Customers" from a database named QTR. We need to copy this database table into a different database named "Research". How can this be done? Is if very complicated?
Can anyone give me info on how the report processing page works in reporting services.
My application makes some pretty heavy queries to the database and I would like to have a message appear on the page whilst the request is being processed.
Something identical to the way reporting services deals with this would be absolutely perfect!
I have three SSRS reports that I modified. The original reports had several subscriptions associated with them. When I deployed the new reports, I placed the old reports into a folder called Archive and renamed them. Now I have these new reports with the original names of the first versions of the reports.
My question is is it possible to somehow replicate the schedules of the original reports to the new reports? There are about 30 subscriptions with between 5-10 recipients, and it would be a real pain to have to manually recreate those subscriptions.
I have a report whose parameters are limited by the user who is logged in. Ex. if a user had full access they would see all 100 hospitals in our hospital parameter, if another user had limited access they might only see 5 - this will vary largely on who is logged in. The data source that drives these parameters are set as follows:
Credentials stored securely in the report server, username and password are entered and Impersonate the authenticated user after a connection has been made to the data source is checked.I now want to cache this report for X minutes. So I goto Properties/Execution and select 'Cache a temporary copy of the report. Expire copy of report after a number of minutes'.
Applying this gives me the error: Credentials used to run this report are not stored.
I have a reporting server with 5 databases which are currently being updated via log shipping. These databases need to be (read) accessed by the users. All users have System Admin perms due in order to access the databases. I need to tighten security on the server and remove SA perms from the users. The largest database is about 8gb and is growing slowly. What is the best way to move the databases efficiently from the source server to the reporting server? The log shipped databases can't be backed up and restored due to the standby mode. DTS is an option but may pose time issues due to database size especially as the databases grow in the future. Replication is another option but i have heard it has alot of issues. Any help is welcome. Thanks.
Hi, I'm trying to create a form with reporting services. Basically I am trying to draw this form using lines that has 2 columns down the page. The problem I have is the fields can grow on either side of the page, so the lines will not line up. I have tried putting this in a list box and then text boxes inside of these, but this will not work. I have not been able to get just the line to work because it doesn't grow with the list either. Has anyone else been able to design a form like this? Any ideas/suggestions would be greatly appreciated!Thanks SQLNewbie1000
I am trying to create new subscription on on of my reports. but some reason when I right click on the subscription the create new subscription is disabled. I have same permissions on the other reports also but it is anabled. Is there any thing do I need to do to create new subscription. The data source on that report is shared.Please some body let me know. Thank you!
I have a server report which made by ssrs 2005, I am using vs 2005 to build an aspx page and call this report which already uses dynamic query. I use parameters and send these to reportviewer with setparameter method. I dont use DataBind and Refresh method. SetParameter is enough to generate Report. When report generates everything is fine. But when i export this report to excel the pictures in this report get smaller to fit in cell height. I used rendering extensions but its same.
And i used copy contents of reportviewer and paste it to excel file. There is interesting case here. If this is the first time that i run the report and copy and paste. No image is seen in excel workseet but other report elements is fine. But if i click on refresh button on reportviewer toolbar and report regenerates, i try to copy and paste and it works fine, and if i dont close ie window copy and paste progress works good and i dont need to click on refresh button on each report to copy and paste?
This is so interesting subject, i tried lots of things for example i tried to use databind and ServerReport.Refresh methods maybe it acts like clicking on refresh button but this wasnt the same only difference is it took more time to generate the report.
Hi, i have a problem, i had an ASPX (ASP 2.0, VS2005) application installed on a 2003 server´s IIS, the application uses SQL server and reporting services, and it was installed and running smooth on the same server, a software provider came one day and for some reason uninstalled and then reinstalled my IIS instalation , the thing is that my reporting server stoped working because the reporting services Virtual Directories and Application Pools are gone, is there any way to recreate this part or is there a way to create it manualy, i tryed to uninstall reporting services in oreder to reinstall it but it doesn´t uninstall because it can not find the IIS entrys , please i need a solution for my problem, any help will be highly apreciated.
i have a ssrs report in which i have creating rows with heading of months name from jan to dec i.e. jan|feb|mar|apr|....i have a parameter start date.i want when i enter start date from march supoose i enter date 03/22/2015 then display months from mar to dec.
I've been assigned the task of setting up access to our SQL Server 2005 box. A consultant developing for us has accessing to 2 databases and I've set this up fine. It appears however that one of these databases is re-copied over to the server every night to keep data reasonably current.
I'm not interesting in changing this method as I'm not the maintainer (as yet).
Basically I would like to know if I've setup access to this database (it works fine), when the database is updated (with an SSIS package) the account seems to get deleted. Do the original permissions from the source database overwrite those of its destination?
I'm using SQL Server Management Studio Express and I'm trying to figure out how to copy a table(s) from my local database to my web hosting database. I know how to do it in 2000, but it's completely different now. Is this feature not allowed on SSMSE? If so, then how do I deploy database tables to a web host?Also, how do you add local database(s) to SSMSE? I tried to use 'attach database' in SSMSE and it wouldn't allow me to navigate to My Documents folder where the database resides. Thanks...
I am trying to create SSRS report with tabs. Something like this
Tab 1 Tab2 Tab 3 Tab4 Tab5
The end user will input a value which will be passed to all the DataSets (my case each Tab will display some information from a DataSet). So when the user clicks on Tab1, it will display relevant information form DataSet1 and when the user clicks Tab2, it will hide the Tab1 information and should display the Tab2 information from DataSet2.I am able to achieve the following:
1. Making the tab clickable 2. Navigating from one tab to another (dispalying relevant information for each Tab) - Using Go to BookMark or Go to Report Option in the Action Property
But I am not able to hide the inactive tab report data. I am building reports and deploying it in SharePoint 2010.
I am new to report builder. I have created a report with a calculated cell that returns a true or false value based on a true or false value from another column (from a dataset field). I want to create a filter for the calculated field (which is a ReportItem), that will only show cells that are True.
I have tried several ways to add this filter, but each time, I get an error message saying that filter or sorting cannot be created for ReportItems.
We have this sporadic problem where when we go in to save an edit on a SSRS report subscription, it gives us a duplicate job number error. When I look at the Jobs in the Agent, there are duplicates, so I need to manually delete both duplicate jobs, then the subscription save will work. If we delete just 1 of the jobs, the issue seems to clear for a while, but then reoccurs. This has happened to us on multiple reports, but not consistently. The subscriptions look fine.
If I want to copy the data from Table1 in Database A to Table2 in Database B but Table1 column name is code , Table 2 column name is vesselcode. (Code = vesselcode)
How to copy all data from Table1 in Database A to Table2 in Database B ? Do I need to write the SQL statment ? and Can I use Server Enterprise Manager Tool?Thx a lot.
Hello,I need to copy a table from an 8i oracle database to a sqlserver 2000 database.Is it possible to use the command "COPY FROM ... TO ..." ?So, what is the correct syntax ?Thanks for your helpCyril
Hi, I have a complicated sql server mobile database (.sdf) and need to create a SQL SERVER database with the same tables. How can I do it without scripting the whole thing? I thought of using the views.information_schema databases, but it is still a lot of coding.
Data Source of the report is AS/400 and I need to create a report in visual studio 2013 with ODBC Connection type and deploy report onto share point 2013. How to create report, data source and deploy on share point.
My problem is how we will create ODBC connection with AS/400 and connect report with AS/400.
I have uninstalled and reinstalled several times and I can't get Reporting Services to create the virtual directories in Default Web Site, instead it creates a new web site called Report Server with the virtual directories under that, this Report Server website points to C:Program FilesMicrosoft SQL ServerInetPubwwwroot. Any ideas as to why this happens? My Default Web Site is called Default Website from the initial IIS install , I am on XP Pro SP 2 with IIS 5.1. This is an issue for me since I don't want to have on another site that doesn't have the aspnet mapings.
I am dumbfounded and can't figure out what to do to make it not do that with the default configuration option checked during install. Should I uninstall and reinstall again and not use the default configuration?
