The Classifieds starter kit supplied by microsoft has 2 databases - aspnetdb.mdf & classifieddb.mdf.
How do I combine the two into one. I understand that there is now need to have two databases. I also understand that most hosting co's do not support Sql Server Express
Any help offered is appreciated. Please offer help in a Dummies format - I might understand that
Zimmer
Dear Developres,
Actually I'm on the half way of making a portal and I get some problem I need your kindly helps.
at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)???
Thanks in advance.
Is there an easy way to combine to SQL databases? Both DBs have the same structure but different data. If there just so happens to be duplicate records what will happen? Does anyone have any idea of where I should start at? :confused:
View 12 Replies View RelatedI have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.
To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.
To better illustrate:
database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58
The overlapping cusno's are not the same customer.
How can I get all the cusno from the arcus file in database two to the arcus file in database one?
Is this even possible?
Hi.I'm currently working on a project which involves the creation of aweb page which reports selected data to customers from two back-endsystems. I use ASP script language on IIS, the server is MS SQL 2000.Now I'm struggling with combining two tables from the differentdatabases. I'm sure it's simple enough, but I'm a little short on theSQL expertise.I've got two databases, db1 and db2, and then two tables, db1.t1 anddb2.t2. I need to combine these two tables (both tables have amatching key field) to make a list of all items from db1.t1, and thosewho correspond from db2.t2.I can list all items from db1.t1, but I can't seem to get the db2.t2joined in.Can anybody help me with the syntax for this, please ? Help !Answers, hints & tips greatly appreciated.Thanks in advance !Kenneth
View 2 Replies View RelatedI have very limited knowledge of sql server so my question is probably very basic. I have 3 database types with 3 different scripts to create these databases. The 3 applications that use these databases are very similar.
How do I create 1 master database that all 3 applications can use?
I've tried on SQL 2000 to run the first script then second & third but it did not seem to work.
Once I have the master database created, how do I create the 1 master script? Is it easier to create the database & script it or to copy & paste sections from the 3 scripts?
Any help would be appreciated.
Here are the 3 scripts:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Script # 1:
http://www.allstarid.com/files/products/scripts/VW650J.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Script # 2:
http://www.allstarid.com/files/products/scripts/ID301D.sql
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Script # 3:
http://www.allstarid.com/files/products/scripts/RW630E.sql
I have 2 DBs located on separate Sql Servers but the DBs are linked. I am querying data from both DBs but want to combine the results. Here is my query but it doesn't seem to be working.
(SELECT DISTINCT
idname, name, address, address2, awardedtoname, suppno
FROM contract INNER JOIN
house ON contract.idname = house.idname)
JOIN
(SELECT DISTINCT
tpd.PropertyNumber AS [Property No], tpd.Address1 + ' , ' + tpd.Address2 AS Estate, tpd.Address1 AS Address1,
[Code] ....
How I could combine the results?
Currently we have one customer database containing various tables. As part of requirements for a new client, we need to manage their data in a totally separate database. The tables and structure are exactly the same but we would be loading data into a separate database.
I am looking for a way to combine tables with the same name in each database when I run queries, rather than having to query each database separately. Currently we actually have many queries set up in MS Access which use an ODBC link to query the data off SQL server. I am aware it is possible to apply a UNION SELECT in Access from 2 separate ODBC connections, but this is extremely slow.So my initial question is - is there a way to provide access to the tables from both databases over the same ODBC link? If this cannot be done over ODBC I guess we can consider more "modern" methods, but ideally we want to keep this in MS Access as that is where our existing queries are based. I was hoping that some kind of view can be treated as an ODBC connection.I mentioned ideally we want to keep the reporting queries in MS Access.
Two Windows 2003 server,one with SQL 2005 server,another with SQL Express.Is it possible to copy databases from SQL 2005 to SQL Express?Thanks.
View 6 Replies View RelatedI have a small client installable web application which used to work great on 32 bit machines. Well now clients are starting to use x64 Windows 2003 servers and things have begun to unravel. W2K3 x64 does not have any of the JET database drivers needed to communicate to the Access databases and it doesn't look like they're coming anytime soon.
Is SQL Server Express the new replacement for Access databases on x64 machines. Does Microsoft intend on moving some of the JET drivers over from 32 bit servers. I know that you can run IIS in 32 bit mode and everything is fine, but I can't ask everyone to do that.
Is SQL Server Express something that I can expect people to have installed, it sure doesn't sounds like it. After reading some posts, it sounds like it's a bit of a hastle to install.
Thanks in advance for your time.
-Mark
Hi all
Which tool can I use for structure editing of SQL Server Compact 3.5 databases? I'm installed SQL Server Compact 3.5. I have SQL Server Management Studio Express which was installed with SQL Server 2005 Express. Unfortunately this SSMS can create and open only 3.1 databases.
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 !
Hello,
I would like to restore SQL Server Express and its databases from a tape backup to the same server. This is a disaster recovery senario.
I backed up the Master, Model, MSDB and my own test database using SQLCMD scripts. I have no problem restoring these using task manager on the server before the disaster recovery.
However, in my real disaster recovery testing, When the server is restored by tape drive (HP one button disaster recovery), I try to run my SQLCMD restore scripts in task manager and I cannot connect to the sql server. Also I cannot connect with Management studio. I have recieved the following error in event viewer.
----------------------------------------------------------------
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 3411
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
Configuration block version 0 is not a valid version number. SQL Server is exiting. Restore the master database or reinstall.
Data:
0000: 53 0d 00 00 15 00 00 00 S.......
0008: 16 00 00 00 43 00 4f 00 ....C.O.
0010: 50 00 4c 00 45 00 59 00 P.L.E.Y.
0018: 4e 00 45 00 57 00 53 00 N.E.W.S.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 00 00 00 00 ....
Event Type: Warning
Event Source: SQLBrowser
Event Category: None
Event ID: 3
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7024
Date: 9/15/2006
Time: 8:16:36 AM
User: N/A
Computer: COPLEYNEWS
Description:
The SQL Server (SQLEXPRESS) service terminated with service-specific error 3411.
C:Program FilesMicrosoft SQL Server90ToolsBinn>sqlcmd -S.SQLExpr
COPLEYNEWSDATABASEscriptsMASTERFULLRESTORE.sql"
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred whi
shing a connection to the server. When connecting to SQL Server 2005,
re may be caused by the fact that under the default settings SQL Serve
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
----------------------------------------------------------------------
My question is, what is the correct procedure to follow when I want to do a disaster recovery and restore SQL Server Express from tape backup using the Simple Backup method and scripts.
Is it always required to reinstall sql server express from the original program file or is it possible to reinstall from back up tape.
I know my backup and restore scripts work because I tested them on the server before I do the disaster recovery and rebuild that server from tape.
This is some kind of issue with SQL Server Express being restored by tape backup.
Any suggestions, thanks.
I am programming in VB6 using ADO 2.8. This connection and query works in MS access, SQL server 2000 and Sql Server 2005. does not work in Sql server 2005 express. Any Suggestions?
Connection String #1 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:Program FilesMaterial_Management_SystemDATAMain.mdf;Data Source=Steve_Laptopsqlexpress
Connection String #2 Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;AttachDBFileName=C:Program FilesMaterial_Management_SystemDATAItems.mdf;Data Source=Steve_Laptopsqlexpress
Sql Query: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID
Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.PFMS'
Sql Query#2: Select POLINE.ID as POLine_ID, PFMS.ID as Items_ID FROM POLINE LEFT JOIN Items.dbo.PFMS as PFMS ON POLINE.lItem_ID = PFMS.ID
Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Items.dbo.PFMS'.
I am running the queries directly from the 2005 Mgt window to take as many variables out of the equasion. I get the same error in Visual basic
How am I supposed to reference a join of 2 databases? Any suggestions
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,
Hi,
In SQL Server 2005 Express I currently have 2 databases which I€™m working on, my problem is I want to transfer a table from one of the databases to the other but don't know how to go about achieving this? I want the table structure and all the info held within the table transferred across. Any ideas are welcome.
Hello,
My company works with SQL Server 2005 express locally with Visual Studio to develop websites. Everything works very well.
We use SQL Server 2005 express on our production server as well. We change the database over to a non-User Instance when the site is ready to go live. Everything works fine here as well.
We run into issues when databases get near 100 MB. This is well below the stated database size limit for Express of 4GB.
At that point, about once a day, a site with a "large" database will stop responding. The error that we'll get is "Cannot open database 'DBNAME' requested by the login. The login failed. Login failed for user 'DBUSER'
The only way we've found to fix this is to restart the SQL Express service. Obviously, that isn't a very useful alternative.
Has anyone run into anything like this? Could we have some setting wrong?
Would moving to the full version of SQL Server 2005 fix this?
I am trying to figure out the best method for transferring SQL 2005 databases from 2 different physical servers to a new server running windows server 2008 64bit.I own a fully registered copy of Microsoft SQL 2005 32bit Standard Edition. However all of my SQL databases together are only 4GB in size thereby making 2008 SQL Express an option.
Here is the breakdown of the servers:
1) (Old Server1) Windows 2003 Server 32bit - SQL Express 2005 32bit
2) (Old Server2) Windows 2003 Server 32bit - SQL Server 2005 32bit Standard
3) (New Server) Windows 2008 Server 64bit - (Not sure which version to install)
**The objective is to migrate 2 SQL databases from Old Server1 and 1 SQL database from Old Server 2 to the New Server. What version should I install on the new server??? Do I go with 2008 express or install the 2005 Standard Edition?
Pending your response to which version to install, what are the correct steps to migrate (which I have never done before) the databases from both of these servers to the new server? I think this particular situation is unique in that I am transferring from both SQL Express and Standard from 2 different physical servers to a 2008 server that is 64bit.
I have a windows 2003 server which has SQL 2005 Express with advanced services installed on it. Then a few weeks back the company purchased SQL server Standard Edition which comes with SQL Server Management Studio (which has more features than SQL Server Management Studio Express currently installed on the server where sql express is running)I have been trying to schedule a maintanance plan on the SQLExpress Instance database from the SQL Server Management Studio that came with the standard version of sql but i have not been able to have all SQL Server Management Studio functionality available when connected to the SQLExpress instance. So is there a way i can connect to the Express Instance from management studio(that comes with the standard edition of sql) and have all its functions available when working with a SQL express database. Or i must upgrade the express database?
View 5 Replies View RelatedMorning,
I installed the managerment stuid on my machine and now none of my database connections seem to work any more. I keep getting the attached error. Before I installed the studio the system worked fine, I could connect to the database and everything was fine. Does the studio change any permission stuff? I cannot seem to get it to work again at all now, I added in aspnet as a login inside the studio and it still won't let me connect to the database. I cannot find *any* logs anywhere that might be helpful in what it is doing or see any way to debug the connection to find out what the problem is. When I look at the permissions inside of the studio it shows me that the connections should all be fine.
I checked and remote and local connections are setup, although it is only doing local connections anyway since this is a asp.net web server page.
Any idea what the problem is here? This is very frustrating and annoying :( Since I didn't change anything and now nothing I will do gets it to work. I even tried to uninstall the management tool and it still doesn't work.
Cannot open user default database. Login failed.
Login failed for user 'GREENANDFLUFFYASPNET'.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'GREENANDFLUFFYASPNET'.
Hi,When I attempt to add the membership functionality to a database created within VWD Express, by using the aspnet_regsql.exe application (connecting to ./SQLEXPRESS using Windows authentication), the database I've created within VWD Express doesn't appear within the list of accessible databases. I have the same problem when attempting to access the same database within Management Studio Express.Does this mean that databases created from within VWD Express don't use the same storage mechanism or paradigm as those already available within Management Studio Express? Or perhaps I need to somehow register the database using Management Studio Express? As a side note, I'm not sure if the "File/Open File" menu option within Management Studio Express holds the answer to my problems, but rather bizarrely, it crashes Management Studio Express when I select it.Apologies for the rambling - in essence my question is "How can I make databases created within VWD Express appear within aspnet_regsql.exe and/or Management Studio Express?"Many thanks,Jon
View 6 Replies View RelatedHi all,
In my SQL Server Management Studio Express (SSMSE)=>Object Explorer=>Databases, the "NORTHWND" database was screwed up and is deleted completely. But under the same Object Explorer =>Databases, there is a database "C:SSMSE-BookChapter12WINCH12NORTHWND.MDF". I need to use this "NORTHWND" database in the SqlConnection. How can I specify the "Initial Catalog" (or "database") in the New SqlConnection code statement (in the ADO.NET 2.0-VB 2005 Express programming)?
Should it be like the following:
Dim sqlConnection As SqlConnection = New SqlConnction("Data Source=.SQLEXPRESS; Initial Catalog=C:SSMSE-BookChapter12WINCH12NORTHWND; Integrated Security=SSPI;") ? Or what is the right code statement for connecting to it?
Please help and advise.
Thanks in advance,
Scott Chang
This issue is just irritating: hope someone can help.... I recently deleted a whole lot of test websites on my laptop by physically deleting the folders. Each had their own SQL Express database. The database instances still show up in SQL Server Management Studio, although the databases have been removed. I can't drop the datababases with the GUI as it says they're not accessible (that would be right), and manually using the DROP DATABASE command tells me that the physical file can't be found (also correct).Is there a "proper" way to get rid of them without directly manipulating the system tables?
View 4 Replies View RelatedHi - I use the windows scheduler to execute an SP for my SQL Express instance on a server.
The SP loops through the SQL Express databases, and makes a backup - however, it will only backup the attached databases.
Is there anyway I can modify it, to make it backup the databases in my various web sites, within the app_data folder? These aren't attached, but I think get attached when the web application needs them, then detached afterwards - so they are never permanently attached!
Thanks for any help,
Mark
The SP I use us:/**
File Name: BackupExpress.sql
Description: Backs up all databases. This script is mainly meant for SQL Express instances
The script requires a C:ackups directory by default to backup to but can be changed with the @OutputPath variable.
Accompanying file is BackupExpress.cmd, which is used to schedule the script.
Author: Brian Knight
DateChange DescriptionAuthor
8/15/2006Initial releaseBrian Knight
**/
SET QUOTED_IDENTIFIER OFF
USE master
GO
SET NOCOUNT ON
DECLARE @dayofweek varchar(20)
SELECT
@dayofweek = CASE datepart(dw, getdate())
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
DECLARE @OutputPath varchar(500)
DECLARE @DatabaseBackupFile varchar(500)
DECLARE @FolderName varchar(25)
DECLARE @DatabaseName varchar(25)
DECLARE @strSQL varchar(2000)
DECLARE @hostname varchar(255)
SET @hostname = (select replace(convert(varchar(255),serverproperty('SERVERNAME')), '','_'))
SET @OutputPath = 'C:Backups'
DECLARE cur_Backup CURSOR FOR
select name from sysdatabases where name !='tempdb'
OPEN cur_Backup
-- Fetch the db names from Cursor
FETCH NEXT FROM cur_Backup INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DatabaseBackupFile = @OutputPath + '' + @hostname + '-' + @DatabaseName + '-' + @dayofweek + '.bak'
PRINT ''
PRINT ''
print @DatabaseBackupFile
SET @strSQL = 'BACKUP DATABASE '+@DatabaseName+' TO DISK = "'+ @DatabaseBackupFile+ '" WITH RETAINDAYS = 7, NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
PRINT @strSQL
PRINT ''
EXEC (@strSQL)
FETCH NEXT FROM cur_Backup INTO @DatabaseName
END
-- Distroy Cursor
CLOSE cur_Backup
DEALLOCATE cur_Backup
SET NOCOUNT OFF
Hello, As is known, when developing an application in VWDE that features membership as well the application's own database, you have a total of 2 databases. Seeing as a lot of hosters charge per database instance, to make the membership utility viable, it would be prudent to merge each database into one. Quite a few blogs/tutorials, including ScottGu's, recommend merging, but I'm as yet to find one that actually explains the merging process step by step. Advice such as, "run each script against the same database," simply isn't enough.If anyone could shed any light on this topic, it would be much appreciated. Of note, I have SQL Server Management Studio as the recommended deployment tool. If one thing's becoming increasing evident, it's that it would be well worthwhile in finding a host that supports SQL Server 2005 as opposed to 2000. Kind regards.
View 2 Replies View RelatedHello,
I have Visual Studio 2005 Professional.
I was wondering if there were any way to save the .dbo databases created in it to upload to my website? Do the databases must be save as a specific filetype according to the website configuration?
Do most people actually connect to their site's database to modify it or something? Or do they usually connect to an external database and then upload it to their site?
Sorry, but I am kind of new to all of this.
Thanks,
Eng
Autofreak wrote:
Hi All,
I have installed the SQL Server 2005 Express edition and its running fine.
I like to connect to the server and create database. Should I do it
through command prompt only or any tool is available like
Enterprise manager for this ?
Where is my databases? I have 2 different DotNetNuke installations on this machine running very well but in the db management tool I cannot find my databases ... I am positive that they are there somewhere collection/holding data but why doesn't they appear in the SQL Express management tool?
My connection string is the classic:
which says there should be at least one databse --> Database.mdf
Doesn't this .mdf file appear in the SQL Express management tool ? Se picture of my control view
/Johan
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'.
I am developing an application that uses SQL Server Express. Everything is running great, except that I can't figure out how to copy a DataBase from my server at work onto my laptop so that I may test it at home...
How can I copy the DataBase from my Server at work to my laptop? I'm on the network at work, I just can't figure out how to copy it...
Please help! :)
Thanks,
Jacob
Hello,
Does anyone else ever experience this problem, and if so do you know what causes it:
When ever I restore a database called say XXX.mdf, it restores OK and I can view via MSE as XXX.mdf. Then when I visit MSE again there is another database called C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataXXX.mdf - both are linked to the same .mdf file, however if you try to expand XXX.mdf nothing happens as though the database has been detached?
Cheers MArco
1. install a SQL Express instance such as myexpress;
2. create some databases with tables such as db1 and db2;
3. Reinstall SQL Express with the same instance name "myexpress". I assume we need reinstall SQL Express myexpress again becuase of bad operations.
4. Open the myexpress by management tool but I can not see db1 and db2 databases but the datafiles and logfiles are in the data directory.
The question is what I need to do so I can consolidate exited databases so they can be seen in "myexpress" instance by management tool?
Hello all,
I would like my newly converted to .NET 2.0 Windows Forms application to use an Sql Express database instead of MSDE. Previously, development was done on the default SQL Server instance using the full SQL Server 7.0. When deployed, MSDE was used with a named instance. The connection string in the application settings needed to be modified as part of the deployment. Now I've converted to .NET 2.0 and to Sql Server 2005, and/or Sql Express.
What I'd really like to do now is have a project within my solution that, when built, creates the database by running scripts, and deploys that database to the output directory of the executable.
I want this to run as part of a continuous integration solution that, so that every so often, the entire project is checked out, built (code and data), all is copied to a bin/[debug,release] directory, unit tests run, etc.
Has anyone done this successfully? Any pointers?
Thanx,
WNC