Creating Reporting Databases
Sep 11, 2002
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.
View 2 Replies
ADVERTISEMENT
Nov 2, 2004
Ok I have the Sql all written and I was going to use MYSql but it wasn't working. I was told that MSSQL worked with the files but I can't for the life of me figure out how to use it. I installed the desktop version so it is running but now how do I create and edit databases and tables and so forth. Help is greatly apprieciated as the only other sql I have done is for php forums. Thanks and If you could tell me if I need to download another program to do it graphically or something that would be awesome.
EDIT: I am using Windows Server 2003 Enterprise if that makes any difference. ISS 6.0 (kind of understand some of it)
View 1 Replies
View Related
Jun 29, 2006
hello,
i am trying to create a database by using a store procedure. This stored procedure takes two input parameters. i want to assign these parameters to the 'Filename' attributes when i'm creating the database both for the .mdf and .ldf files. However i keep getting an error.
These work -------
FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdblog.mdf',
FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdblog.ldf',
These do'nt work--------
FILENAME = @Databasepath,
FILENAME = @Databaselogpath,
Here is my code:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE rico_dbasescript
@Databasepath varchar(100) = 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdb.mdf' , @Databaselogpath varchar(100)= 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdblog.ldf'
AS
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
DROP DATABASE [Sardonyxrioctestdb]
CREATE DATABASE Sardonyxrioctestdb
ON
( NAME = 'Sardonyxrioctestdb_dat',
FILENAME = @Databasepath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdb.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sardonyxrioctestdb_log',
FILENAME = @Databaselogpath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctes tdblog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
I am still researching my problem but i would appreciate any help. Thanks guys.
JamaicanGuy
**I am a newbie .net developer.
Jah Bless!!!!!!!!!!!!
View 1 Replies
View Related
Oct 13, 2007
When i create a database and later on want to add a file in it
Alter database db1
Add File
(Size = 2MB,
Maxsize = 5MB,
Filegrowth = 20%)
this gives me an error because there's no filename.
I would like to know if filename is mandatory?
Thanks as i got an error when running saying no filename.
View 2 Replies
View Related
Apr 21, 2007
Hi,
I'm using SQL Server 2005 Express for an application.
SQL Server 2005 Express supports 4GB user data per database. Because need over 4GB(may be 20GB ~ 30GB) data space, I should split database which each max size up to 200 MB. And create 100 databases.
Is it reasonable? If true, what about the performance or system's overhead?
Thank you.
View 1 Replies
View Related
Mar 20, 2007
When creating databases is it a good idea to use the master.dbo.sysdatabases database and then create databases under this.
View 4 Replies
View Related
Apr 23, 2001
how do you create a database from an .mdf file?
View 2 Replies
View Related
Jul 11, 2001
Is this possible? I seem to remember doing it with SQL Server 7 a long time ago. The Microsoft Knowledgebase says it's not possible with 4.2 through 6.5, but nothing about 7.0 and up.
View 2 Replies
View Related
Nov 6, 2005
I want to be able to create a duplicate database and update / refresh it daily automatically. This database copy would be used for testing purposes so I don't want it to write back to the original database. Is replication of some kind the answer?
View 4 Replies
View Related
Jul 13, 2006
Obadiah writes "hello,
I am a newbie to developing .net applications unfortunately my boss and my colleaques think i am a pro (interview went too well) and i would like to get at least this months paycheck before am fired....so i here's my dumb question:
I want to create a SQL database from my vb.net application using stored procedures, however i want to be able to set the Filename attribute ('Filename = C:Program FilesMicrosoft SQL ServerMSSQL$SARDONYXINSTANCEData estdb.mdf') by means of a input parameter to the stored procedure. like so...
Filename = @databasepath.
HERE is what i have:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE rico_dbasescript
@Databasepath varchar(100) = 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdb.mdf' , @Databaselogpath varchar(100)= 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdblog.ldf'
AS
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Sardonyxrioctestdb')
DROP DATABASE [Sardonyxrioctestdb]
CREATE DATABASE Sardonyxrioctestdb
ON
( NAME = 'Sardonyxrioctestdb_dat',
FILENAME = @Databasepath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdb.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sardonyxrioctestdb_log',
FILENAME = @Databaselogpath,
--FILENAME ='c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdblog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
The above procedure works fine when Filename = 'c:program filesmicrosoft sql servermssql$sardonyxinstancedataSardonyxrioctestdb.mdf'
but bombs out when Filename = @Databaselogpath.
Thank you in advance."
View 2 Replies
View Related
Nov 9, 2006
Hi,
I want to create two databases by restoring from a
single backup file in sql server. I am using 2005-sqlexpress .Is it possible?
Thanx in advance..
View 3 Replies
View Related
Jul 20, 2005
I attempted to create a view in SQL Server 2000 that Unions twoqueries. The first part of the query gets data from the local server,the second part gets info from a linked server. (The query works finein Query Analyzer.)I received this error when I tried to save the query:ODBC error: [Microsoft][ODBC SQL Server Driver] The operation couldnot be performed because the OLE DB provider 'SQLOLEDB' was unable tobegin a distributed transaction.[Microsft][ODBC SQL Server Driver][SQL Server][OLE/DB providerreturned message: New transaction cannot enlist in the specifiedtransaction coordinator.]After a little reading I discovered the "Database limitation":"A view can be created on a table only in the database the viewcreator is accessing".That's my problem... is there a simple solution or alternative tocreating a view?Thanks,Matt
View 2 Replies
View Related
Dec 10, 2007
Dear Readers,
Is it possible to create a query that access multiple sql express databases? Or is it possible to link a table in one database from another?
View 1 Replies
View Related
Apr 14, 2008
I have VB2008 express edition and SQL server 2005. When i try to create a new local datasource in my project, the datasource seems to be created just fine however an error message comes up. If i try to create the datasource by selecting it on the 'add new item' menu, an error message says
"The data provider required to connect to the local data file could not be found. The file will be added to the project but the typed Dataset associated with the project will not be created."
If I try to create the datasource by selecting 'add new datasource' from the datasources pane, i can create the connection to a new or existing database but when i click next to proceed to the page defining the dataset, an error message says
"An error occured while converting this connection:
Data provided identified by guid ' 7c602b5b-accb-4acd-9dc0-ca66388c1533 ' could not be loaded"
PLEASE HELP!!
I have no idea how to fix it
thanks,
jordan
View 3 Replies
View Related
Feb 27, 2008
Hi,
Any body please give me some TSQL Stored procedure for
creating an SQL user and to give db access permission to all the databases available on a the server.
Mujeeb
View 5 Replies
View Related
Nov 28, 2006
Hi,
For various reasons a new desktop application I am developing is using the SQL Server 2005 Compact Edition Release Candidate to store local data. On my development machine (XP SP2, VS2005, SQL Server 2005 Developer Edition) the software is able to create the database file, and read and write to it fine.
However on our test machine, which does not have any of the development tools the software is unable to create the database. A System.Data.SqlServerCe.SqlCeException is thrown. The message is: "SQL Mobile usage is restricted on this platform. To use SQL Mobile, you must install SQL Server 2005, Visual Studio 2005, or the Tablet PC SKU." Obviously this goes somewhat against the blurb for SQL Server CE.
Is this something that will be fixed shortly? Or is it the intention that databases can't be created on machines that do not SQL Server or Visual Studio installed? (Incidentally the reason for creating the database on the fly is that we are encrypting it, using the users credentials, so that only they can open the database.)
Thanks, Steve
View 4 Replies
View Related
May 6, 2008
My application uses 2 databases from 2 different server. When i create a report model in business intellegence development studio I can only create 1 datasource for each database, and 1 datasource view for each datasource. Then when I create a new report, I can only use one datasource.
Is it possible that my report can be built on more than one databases?
thanks in advance
View 4 Replies
View Related
Jul 16, 2007
Hey everyone,
I have just started getting into to SQL and am completely brand new to the whole concepts of relational databases. Someone on this forum pointed to the MSDN videos on LEARNVISUALSTUDIO.NET which have been very helpful. Unfortunately while learning about relational databases and looking at the program that I want to design and make using them, I have run into a pretty big wall, concerning the primary key and foreign key.
For my program, I am trying to save an object, and lets say the base class is SLIDE. Now SLIDE will store basically most of the information that I will ever want to store, such as timeCreated and mainText and slideID(primarykey). But there are other classes that derive from slide that will store just a bit more information than that because of what they are. Lets say there is a class derived from SLIDE called PERSON that stores its parentNode, which is to say something that this slide specifically belongs to and has a reference to. Now the tricky part is that in this program, every single slide can have a reference to another slide, that you would see displayed and that you could just click on and view if you wanted to.
Now relating what I just told about the classes in my program to a relation database standpoint is what confuses me. If I make a table SLIDE, it would hold incomplete data about the PERSON object, because that object has more data than SLIDE has. The answer to this was to make another table called PERSON, which would have more columns. But now we arrive at the big problem: The primary key called maybe SLIDEID would be different in this new PERSON table than in the other table called SLIDE (or any other derived class). Therefore the link between these tables is messed up. In my object orientated mind I am thinking of static class variables that would still stay constant for derived classes, so that when I make a PERSON slide it will increment off of the primary key of the SLIDE table. In other words, is there some sort of super TABLE that you can derive from, like an abstract class, where the primary keys of other tables can build off of because they will be used as the same type of reference to eachother.
If none of this made sense to the reader, I am greatly sorry. I do not really know what else I can say to convey to you the problem I have. Maybe its because I am so used to object orientated languages that this is making it so difficult to explain. If however you do understand what I am talking about, please think about it and help me find a solution to this problem. I am not an experienced programmer, but I do very much enjoy it and I am very excited about starting to make this program, and I have learned that before I start coding it is very important to have a very firm design in mind first.
Thank your for reading,
Jeremy
View 5 Replies
View Related
Aug 23, 2005
Hi, I have looked and loked for the answer to this question and made no progress. I want to install MSDE on one of our servers, however, when I went to install it, I found that there was another MSDE already installed and running (it was an agent running with our backup software Veritas). Is it alright to create another database and have 2 running simultaneous on the same data engine? Is there any type of limitations that I sould know about - such as number of allowed accesses at one time? Any help is greatly appreicated.
View 1 Replies
View Related
Jun 9, 2007
Good Day,
I am having a problem with creating databases in MS SQL 2005 Server. When I attempt to assign an User Mapping for my SQL user account to the newly created database, the "dbo" user and schema is already assigned. When I try to drop this user in favor of my own, I receive an error message: Cannot alter the user "dbo" (Microsoft SQL Server, Error: 15150). I am connected to my database engine through the "sa" account.
Regards,
Keith
View 6 Replies
View Related
Dec 5, 2007
This post is an extension onto the one below....
Please read my question at the bottom... cheers
---------------------------------------------------------------------------------------------------------------------------------------
Sept. 26th 2006
Hello all,
I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it.
Any help is appreciated and thanks in advance,
- Noble Bell
I'd be interested in knowing how VS doesn't allow you to create a database. What error are you getting?
There are two ways to create databases, depending on your goal:
To just create a database on your server, do the following:
Open the Server Explorer
Right-click on Data Connections
Click Create New SQL Server Database
Specify Server Name and Database Name
Your database will be created and you can start working with it.
Embed a database in your project:
On the Project menu, click Add New Item.
Select SQL Database from the list and give it a name.
Click OK
This will run you thorugh a wizard to create the database.
If your having problems doing either of these, you may not have SQL Server installed on your computer or VS might be pointing to the wrong Instance Name. Check out the Option dialog under Database Tools:ata Connection and verify that the correct instance is specified.
- Mike Wachel - MSFT
---------------------------------------------------------------------------------------------------------------------------------------
I am trying to create a similar project and I also recieve an error while trying to create a database...
"An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could Not Open A Connection to SQL Server."
Now let me explain what i am trying to do ...
I am trying to create a web interface for the data that I want to store in the SQL database. I have installed VS2005 & SQL Server Express 2005 on my local machine. I want to develope this project on my local machine and then transfer it to a server once i have finished the project.
If i am doing this all wrong, please let me know !
View 2 Replies
View Related
Jan 17, 2007
OK I think I am missing something here. I have installed the newly released SQL Compact Edition, Server Tools, and Tools for VS SP1. According to the documentation you can do the following to create a SQL Compact Edition DB:
Creating a SQL Server Compact Edition database on the server
In SQL Server Management Studio, open Object Explorer.
In Object Explorer, click Connect, and then choose SQL Server Compact Edition.
In the Connect to Server dialog box, select <New Database€¦> from the Database file drop-down list.
In the Create New SQL Server Compact Edition Database dialog box, type a file path and file name for the new database file. You can optionally select the default sort order and choose whether you want to encrypt or password-protect the database. If you choose to encrypt or password-protect the database, type a password, and then click OK.
Click Connect to connect to the new SQL Server Compact Edition database. The database is now displayed in Object Explorer.
I see no reference to or any option to create or connect to a SQL Server Compact Edition database in SQL Mgmt Studio. I had SQL 2005 Mobile installed previously and this still shows as an option. What I am missing here? Is SQL Mobile now SQL Compact Editon?
Thanks in advance,
Jack
View 5 Replies
View Related
Sep 27, 2006
Hello all,
I use Visual Studio 2005 Standard. I would like to know the best way that I can create Sql Server 2005 Express databases visually since VS2005 Std. does not allow me to do it.
Any help is appreciated and thanks in advance,
View 1 Replies
View Related
Nov 19, 2007
Dear Reader,
I'm having some problems writing T-sql statement in visual studio 2005 because i cant find either the query analyzer or enterprise manager in the version of sql server installed. Please kindly put me through that or do i need to create the database outside visual studio. Please will be greatful for this if u can help.
View 1 Replies
View Related
Jan 12, 2006
we are using an object database for our OLTP but for reporting we have
got some issues about performance as the cpu becames a bottleneck.And
we want to be able to run on low end computers...
One of our team members suggested to replicate the object database to a
SQL table.But just a single one.The most denormalized thing ever.(358 coloumns)
is this the fastest way we can get in reporting?
*we don't want harddisk,ram or cpu to became a bottleneck. ( must run on cheap staff)
View 2 Replies
View Related
Jan 29, 2001
We are in the design phase of a project where it is estimated there will be 150 SQL Server databases - each one housing similar information on each of 150 client sites. The client needs a reporting tool to analyze and make comparisons across databases. We were thinking about a using a data warehouse. Does SQL Server have a native solution to this type of problem ? Or is there another, better way to approach this ?
View 1 Replies
View Related
Apr 19, 2007
I am trying to move my reporting databases on Server1 to another server (Server2). The databases are named ReportServer$Server1 and ReportServer$Server1TempDB, and I would like them to be named ReportServer$Server2 and ReportServer$Server2TempDB on Server2. I have followed all the steps in the KB articles on moving databases, and I have restored the key from Server1 onto Server2. I can use the Report Manager to change the Data Source connection string on Server2. However, when I try to access a report, or delete an unwanted object, it always errors out, and there is a reference to ReportServer$Server1TempDB in the error message.
Where is this dependency on the old name coming from? Is there no way to rename the report server databases?
Thx
Helen
View 1 Replies
View Related
Nov 8, 2006
Hey all. Sorry to ask such a basic question, but I am needing to combine data from about 3 different databases into 1 ad hoc report. I know that you can include multiple datasets, but I need this to act as 1 dataset so that the table data between all databases are related to one another.
I know very little about the potential of analysis services / integration services, so I don't know if they contain the answer to my problem.
Does anyone have a suggestion of how I should approach this problem (as always, speed is a bit of a consideration here as well).
Thanks!
View 6 Replies
View Related
Jun 17, 2015
I would like to setup replica for one of the databases for reporting. The current environment is a 2 node cluster(active/passive). I would like to add a 3rd node that can server as a secondary replica. The secondary replica will be on asynchronous commit mode.
The database that needs to have alwayson setup has column level encryption enabled.
* Do I need to backup and restore the service master key on secondary server in order to have the column level encryption to work on secondary server?
* What would be preferred Quorum settings?
* What is the setting for 'readable secondary' for primary and replica db?
* What should be the setting for 'Connections in Primary Role' for primary and replica db?
* We are trying to setup without a Listner. Do I need to setup AG Listner? Can the application exclusively use the [secondary instance name].[replica DB name] without a listner?
View 2 Replies
View Related
May 8, 2007
Hi!
We have multiple databases (1 per client) and we want to use reporting services for ad-hoc reporting. Our authentication is done by using client's database so security is custom.
What is the best way to set this up? Is it possible to somehow allow users/admins to use only their database and never see any others? I know we can install a separate instance of RS and do that but I am wondering if there is a better way to run multiple clients doing different reports using exclusively their databases without having to create a new instance.
Thanks
View 13 Replies
View Related
Dec 1, 2015
I'm new to SSRS. I've installed SQL Server 2014 on my Windows 7 machine and logged in web interface at localhost/Reports, created data source with ODBC driver connecting to my PostgreSQL database, I've selected "Credentials stored securely in the report server" and provided login and password. Now I wanted to create some test report. So I clicked on Report Builder which launched a desktop application (not ideal I must say) where I tried to click through a wizard to create some report.
I tried table wizard, selected "Create a dataset" and selected my data source. It asked me to provide password again and then I got following error:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Even though test connection works just fine, even from that very same window.
So my questions are:
How do I fix that issue so I could create reports using SSRS and my PostgreSQL database?How do I embed the user/password into connection so users don't have to re-enter the password? The reason is, I want to create data source through SSRS API from my own application and I don't want to have users be bothered with passwords
Just to clarify - I tried creating ODBC data source through admin tools in Control pannel and gave it the same name but it didn't work, both SQL Server and PostgreSQL are installed on the same machine.Â
View 7 Replies
View Related
Sep 19, 2007
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
View 10 Replies
View Related
Jan 22, 2008
Hi All,
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?
Thanks
Steve
View 5 Replies
View Related
