I am using Visual Studio 2008 and have created a .mdf database within my project. I want to be able to do a few things and I am not quite sure where to start. The program I am writing manages rental properties. Thus, each rental property that the user owns needs its own sql express database. For this to work:
1. I need to be able to dynamically create a database for each property that the user owns.
Is it best to do this as described here http://msdn2.microsoft.com/en-us/library/bb399420.aspx ? Or is it best to have a template database and then make a duplicate of the database using the File.Copy() method.
If it is best to do it programatically as shown in the link above, how do you create relationships between tables?
2. I need to be able to dynamically add users to the database via c# code. How does one do this? Is it possible do through LINQ?
Hopefully someone can point me in the right direction. Thanks
I have a software that is running with SQL Server 2005. The software has an utility to configure replication between 2 databases. When I was trying to configure it, for some reason the tool convert my main user database under system databases.
I need to put it back again under user databases.
Im thinking to backup my database first and put it offline and the restore the database again. But Im not to expert in SQL and also I am not sure if this will work putting back the database as a user database.
I am writing some C# code that builds a package programatically. the package contains a flat file connection manager. I have referenced Microsoft.SqlServer.Dts.Runtime.Wrapper in order that I can use the ConnectionManagerFlatFileClass class.
I get a compilation error on the last line saying I cannot implicitly cast as ConnectionManagerFlatFileClass. Fair enough, so I change the last line of code to:
Now it will compile. But when I run it I get the error:
"Unable to cast COM object of type 'System.__ComObject' to class type "Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerFlatFileClass'. COM components that enter the CLR and do not support IProvideClassInfo or that do not have interop assembly referenced will be wrapped in the __ComObject type. instances of this type cannot be cast to any other class; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface"
Can anyone tell me how I can cast the ConnectionManager.InnerObject as a ConnectionManagerFlatFileClass please?
-Jamie
P.S. My ultimate aim here is to set the metdata of each of the columns defined by the flat file connection manager. I can't see how I can do that without referring to the connection manager as a ConnectionManagerFlatFileClass!
Hi, Will SQL7 create all databases on C: drive only from now on?
After SQL7 Beta3 installation I tried to create a new db but in the 'Browse' window in 'Create DB' procedure I can see only C: & D: drives (btw D: is CD-ROM). It doesn't see my E:, F: & G: hard drives. When I try to create my db using 'create database' statement it again gives me an error 'path not found'.
Also, I have 512 Mb free on C: drive, but when I create new db with 100-mb data + 50-mb log on C: SQL7 says that I don't have enough space for this db.
Does anybody had the same problem already? I need to convert my Sybase db into SQL Server7 and got this problem with creating db right in the start.
Help me please! I've installed SQL Express 2005 on my XP. I'm trying to create a database but I keep getting the error no matter what I do.
Msg 1802, Level 16, State 4, Server MYCOMPUTERSQLSERV, Line 1 CREATE DATABASE failed. Some file names listed could not be created. Check related errors. Msg 5123, Level 16, State 1, Server MYCOMPUTERSQLSERV, Line 1 CREATE FILE encountered operating system error 5(error not found) while attempting to open or create the physical file 'C:Program FilesExos9300DatabaseExos93.mdf'.
Hi, I am a beginner of Reporting Service. I am trying to create a report using multiple databses. For example, I want to create a report called RevenueByCustomer, so I need to get data from the Customer Table of CRM database, which contains customer information, and I also need to get data from Transaction table of Sales database, which contains all the revenue information. In order to get data from both database, I have created two dataset. One is Customer dataset, which get all required customer data from CRM database, and the other is Revenue dataset, which gets data from Sales database, they used seperate datasource (because each datasource only contains one database connection). Now my problem is how can I make them be displayed in one report ? It seems to be like a Master-Detail report, I need to sum up all trasactions for a particular customer and also need to display the customer name with each TotalAmount, but how can I make these two dataset can be merged together or used an extra query to do it?
Please help me, thanks a lot
This Query below is used to calculate the total amount for each customer in Revenue dataset:
SELECT CustomerID, sum(TotalAmount - TotalTaxAmount) AS TotalExcTax, sum(TotalAmount) AS TotalIncTax FROM TransactionMaster WHERE (Updated >= @Start) AND (Updated <= @End) Group By CustomerID
the other one here is used to get customer code and name in Customer dataset:
I seem to be having a number of problems using managment studio (MS) against my locally installed SQL 2005 Standard Edition. E.g. when I create a default database using MS it says...
TITLE: Microsoft SQL Server Management Studio ------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server could not load DCOM. (Microsoft SQL Server, Error: 7404)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=7404&LinkId=20476
However, the hyperlinks are less than helpful. I also get problems doing various maintance work from MS. I don't think its a database engine problem 'cause I can do all the work via TSQL DDL but the interface is so much easier to use (or should be). BTW it's running on XP Pro (firewall problem?).
I seem to be having a number of problems using managment studio (MS) against my locally installed SQL 2005 Standard Edition. E.g. when I create a default database using MS it says...
TITLE: Microsoft SQL Server Management Studio------------------------------ Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476 ------------------------------ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server could not load DCOM. (Microsoft SQL Server, Error: 7404) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=7404&LinkId=20476
However, the hyperlinks are less than helpful. I also get problems doing various maintance work from MS. I don't think its a database engine problem 'cause I can do all the work via TSQL DDL but the interface is so much easier to use (or should be). BTW it's running on XP Pro (firewall problem?).
We have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.
CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50)) INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6') SELECT * FROM #SourceDatabase DROP TABLE #SourceDatabase CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50)) INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6') SELECT * FROM #ArchiveDatabase DROP TABLE #ArchiveDatabase
We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.
the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.
CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1] CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2] CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3] CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4] CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5] CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]
For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?
I need to create a new table on a SQL Server 2005 Database, but the data needed for the table is kept in different databases on different servers.
Half the data is on the SQL 2005 database, so that shouldn't be too much of a problem, but the other half is on a SQL Express database on a different server, is it possible to write a T-SQL query to retrieve this data on the other server?
Normally I use a create table + insert into kind of query to create custom tables but I don't know how to connect to the SQL Express server/database using T-SQL (if it's possible at all?) to do this.
Any tips, hints, ideas very welcome. Please use small words and short sentences.
Hi, I am trying to open or create a sqlexpress database within Visual Studio 2005 Professional in the App_Data folder. If I attempt either method, I get the following dialog box:Required Components MissingConnections to SQL Server files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkID=49251 The link above just takes you to the download page for Sql Server Express 2005. I have both SQL Server 2005 Developer and SQL Server 2005 Expression instances running. The existing database will work properly in my web application, however I cannot access it through Visual Studio.As background, I did have a problem connecting to the database via the web application, receiving a "Failed to generate a User Instance of SQL Server" error. But I was able to fix that by renaming my SQLEXPRESS folder in C:Documents and SettingsuserLocal SettingsApplication DataMicrosoftMicrosoft SQL Server Data. When the web page ran, it created another SQLEXPRESS folder.However, I cannot figure out why I am having the other issue.Thanks in advancePatrick
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
Guys , I have a dedicated server ... I've installed SQL SERVER 2005 and I have a problem to connect to SQL from local machines in the office ... Remote connections are on , server BRowser starts automatically ... for example from remote machinne I mean from Server where SQL is installed I can connect to SQL ... FireWall i've turned off.... What can be the problem ????
Hi, I got a website and everything works fine with SQL Server 2000, but now I modified the way of authentication using Forms and when I enter in a protected area I got this error: An error has occurred while establishing a connection to the server.When connecting to SQL Server 2005, this failure may be caused by thefact that under the default settings SQL Server does not allow remoteconnections. (provider: Named Pipes Provider, error: 40 - Could notopen a connection to SQL Server) The problem is that I don´t have SQL Server 2005 installed so I don´t know why my webapp is trying to contact it, where I can configure this settings so it will look for the Server that exists? Thanks.
In my DAL, I have 3 tables - Footballer (FootballerID = Primary Key) Aspnet Membership Table (UserID=Primary Key) Favourite Footballers (Junction Table with 2 Primary Keys FootballerID and UserID - both with foreign relationships to their respective data table)
On my sports site, visitors can view various footballers and store their favourites in th FavouriteFootballers Table by clicking on a button. This stores the Logged in UserID and FootballerID in the junction table. I now need to write a select query that allows users to click a link and view their favourites which they stored - lets say in a data list on another aspx page. This would include other columns from the Footballer Table such as Name, Age, Profile ect I think I am right in that I need to use a Select clause as I am using SQl data adapters in the DAL. Be very pleased if someone knows how to contruct the query and give me an example how to code a method in the BLL Some code is below
Method that Stores FootballerID and UserIDprotected void DataList1_ItemCommand(object source, DataListCommandEventArgs e) {
// Assuming only have one button in the item templateInt32 fid = Int32.Parse(e.CommandArgument.ToString());Guid userid = (Guid)Membership.GetUser().ProviderUserKey;
// Connect to the db and insert the recordusing (SqlConnection con = new SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDatabase.mdf;Integrated Security=True;User Instance=True")) {
string query = string.Format("INSERT INTO FavouriteFootballer(UserID, ArtistID) VALUES('{0}',{1})", userid, fid); SqlCommand cmd = new SqlCommand(query, con); con.Open(); cmd.ExecuteNonQuery(); con.Close(); }
Code in FavouriteFootballersBLL Its not complete -withotu parameter - i think maybe I also need to reference the Footballer Table and not the Junction one [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]public DataSet1.FavouriteFootballerDataTable GetFootballerFavourites() { return Adapter.GetFootballerFavourites(); }
Hi, I am really having a hard time creating a parameter for the RadioButtonList radSearchField. I want to type 's' in the text the choose the column for the query to execute the Select statement. I was able to create a parameter for the txtSearchText but can't get the radSearchField working. My form contains four objects.
Textbox to perform the query. (name: txtSearchText)
We've A2k db which we plan to migrate to SQL 7.0 very soon. Though am familiar with upsizing wizard BUT don't know how to configure Access forms, reports etc. to talk to SQL back end tables. So that information entered via Access forms can be stored in sql and reports can be generated using sql tables..
Hi, I'm using Exchange server for our mail communication. If wanted to config SQL Mail in SQL Server 2000 is Ok 1. Insall outlook in sql server 2. US Exchange mail server name in sql mail or any procedure I need to do? Thanks, Ravi
What are the steps in configuring sql server 6.5 to have excellent performance? what are the items that I should think of and based on what condition I can set up the configuration?
Hello, I run a small homw office. I am planning to purchase a dell powerdge 1750 server to install SQL server on that. I am confused here about which RAID should I install on this server RAID 1 or RAID 5. The dell customer rep could not tell me the advantages of installing only RAID 1 or only RAID 5 or installing both RAID 1 and RAID 5
I cannot create a Distribution Server on SQL Server 2005 SP1. When I right click on Replication and click "Configure Distribution" and hour glass appears for a minute, and then nothing happens.
I get the same result if I try to create a publication.
Hi All,Here is my questionWhat might you configure on a SQL Server so that a Web server runningon the same machine, with limited memory, will continue to performoptimally?Thank youRaju
I have installed SP2 on my SQL 2005 instance which also houses RS of course. Additionally I installed WSS3.0 without configuring it so it has the Object Model installed (should be enough?)
I am running WSS 3.0 on a seperate Server and have the Add-in installed on there.
Following the installation instructions I am going to Application Management to configure WSS, but there is no section called "Reporting Services". Looking on the Report Server, in the Configuration I see the following error under the SharePoint Integration Setting:
The report server cannot access settings in the SharePoint configuration database. Most likely, the Windows SharePoint Services object model is not installed or the Report Server Web Service and Windows service accounts do not have access to SharePoint databases. To configure service account access, use SharePoint Central Administration.
In my SharePoint Site I do see a SharePoint section under the "Site Settings" for managing shared schedules but I think the main issue is, that I don't see that section in Central Administration.
