Database Object Deployment
Feb 8, 2006I have a Visual Studio 2005 Database Project, with SQL Server 2005 scripts in it.
How do I make a deployment package with it?
How do guys deploy your database objects/scripts?
ADVERTISEMENT
Object Reference Not Set But Only On Deployment
Feb 27, 2007Please help as I've no idea what to try next!
I had a 2003 project designed by someone else. I upgraded it to 2005 using the conversion wizard.
Now it all works find within vs2005, I deploy it to a location else where on my pc and create a virtaul directory and map it so that I'm using IIS. This all works fine.
As soon as I move it to a virtual machine (windows 2k server SP4 IIS5) I get object reference not set to an instance, basically what I can determine is my sqlconnection fails when it is opened. Can anyone tell me?
Private Sub DoLogin(ByVal bLogin As Boolean)
If bLogin Then
strQueryString = "exec MyQuery'"
End If
dsVerifyPW = GetDataSet(strQueryString, Application("SQL_Connect")) '***
.......
web.config
<configuration>
<appSettings/>
<connectionStrings>
<add name="MyConnectString" connectionString="Server=(local);Database=MyDB;Persist Security Info=True;User ID=Uzer;Password=Uzerpwd" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
*****************************Below from App_Code folder MyModule.vb
Imports System.Data
Imports System.Data.SqlClient
Imports System.Security.Cryptography
Imports System.Web.Mail
Imports System.Text.RegularExpressions
Namespace My.NET
Public Module MyModule
Public Function GetDataSet(ByVal strSQL As String, ByVal strSQL_Connect As String) As DataSet
Try
Dim cnSQl As New SqlConnection(strSQL_Connect)
cnSQl.Open() '**********fails here
Dim dsLoad As New DataSet
Dim cmdLoad As New SqlCommand(strSQL, cnSQl)
Dim daLoad As New SqlDataAdapter(cmdLoad)
daLoad.Fill(dsLoad, "ReturnedData")
GetDataSet = dsLoad
cnSQl.Close()
Catch sqlex As SqlException
GetDataSet = Nothing
Catch ex As Exception
GetDataSet = Nothing
End Try
End Function
from Global.asax.vb on application start up
Application("SQL_Connect") = ConfigurationManager.ConnectionStrings("VacationConnectString").ConnectionString
SELECT Permission Denied On Object 'database Object', Database 'databasename', Owner 'dbo'.
Mar 27, 2006I have created a sql login account called "webuser" and has given public role in my database. In my asp.net application i build connection string using above account and its password . We give permission on store procedure for for the above account to execute .We dont give table level permission for the above account . When we run the application with the above settings it runs fine on test server . However Now i have transfered the databse object to live server with its permissions . Now while I executing the aspx page , I am getting above error . I have checked that the store procedure has execute permission for webuser account and dbo(i.e SA) has all the permissions for all database objects . Still why i am getting error ? (Please note , the thing is working fine in test server)
Pl help
Regards
Server Error: Object Reference Not Set To An Instance Of An Object. Trying To Upload Image In Database
Dec 17, 2007Does any one has any clue for this error ? I did went through a lot of articles on this error but none helped . I am working in Visual studie 2005 and trying to upload image in sql database through a simple form. Here is the code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.IO;
public partial class Binary_frmUpload : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void btnUpload_Click(object sender, EventArgs e)
{if (FileUpload.HasFile == false)
{
// No file uploaded!lblUploadDetails.Text = "Please first select a file to upload...";
}
else
{string str1 = FileUpload.PostedFile.FileName;
string str2 = FileUpload.PostedFile.ContentType; string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
//Initialize SQL Server Connection SqlConnection con = new SqlConnection(connectionString);
//Set insert query string qry = "insert into Officers (Picture,PictureType ,PicttureTitle) values(@ImageData, @PictureType, @PictureTitle)";
//Initialize SqlCommand object for insert. SqlCommand cmd = new SqlCommand(qry, con);
//We are passing Original Image Path and Image byte data as sql parameters. cmd.Parameters.Add(new SqlParameter("@PictureTitle", str1));
cmd.Parameters.Add(new SqlParameter("@PictureType", str2));Stream imgStream = FileUpload.PostedFile.InputStream;
int imgLen = FileUpload.PostedFile.ContentLength;byte[] ImageBytes = new byte[imgLen]; cmd.Parameters.Add(new SqlParameter("@ImageData", ImageBytes));
//Open connection and execute insert query.
con.Open();
cmd.ExecuteNonQuery();
con.Close(); //Close form and return to list or images.
}
}
}
Object reference not set to an instance of an object.
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.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 32:
Line 33: string str2 = FileUpload.PostedFile.ContentType;
Line 34: string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
Line 35:
Line 36: //Initialize SQL Server Connection Source File: c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs Line: 34
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
Binary_frmUpload.btnUpload_Click(Object sender, EventArgs e) in c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs:34
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Object Reference Not Set To An Instance Of An Object (Inserting Data Into Database)
Dec 28, 2004Each time I press submit to insert data into the database I receive the following message. I use the same code on another page and it works fine. Here is the error:
Object reference not set to an instance of an object.
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.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 125: MyCommand.Parameters("@Balance").Value = txtBalance.Text
Line 126:
Line 127: MyCommand.Connection.Open()
Line 128:
Line 129: Try
Source File: c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb Line: 127
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
CreditRepair.CreditRepair.Vb.Creditor_Default.btnSaveAdd_Click(Object sender, EventArgs e) in c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb:127
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
If (Page.IsValid) Then
Dim DS As DataSet
Dim MyCommand As SqlCommand
Dim AddAccount As String = "insert into AccountDetails (Account_ID, Report_ID, Balance) values (@Account_ID, @Report_ID, @Balance)"
MyCommand = New SqlCommand(AddAccount, MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@Account_ID", SqlDbType.Char, 50))
MyCommand.Parameters("@Account_ID").Value = txtAccount_ID.Text
MyCommand.Parameters.Add(New SqlParameter("@Report_ID", SqlDbType.Char, 50))
MyCommand.Parameters("@Report_ID").Value = txtReport_ID.Text
MyCommand.Parameters.Add(New SqlParameter("@Balance", SqlDbType.Char, 50))
MyCommand.Parameters("@Balance").Value = txtBalance.Text
MyCommand.Connection.Open()
MyCommand.ExecuteNonQuery()
Response.Redirect("Customer_Details.aspx?SS='CustHeadGrid.Columns[0].Item.lblSS.Text)")
MyCommand.Connection.Close()
End If
Add New Database Error Object Reference Not Set To An Instance Of An Object
Oct 18, 2006Hi,
I'm new to SQL database.
When I add a new item to project in VS2005: Project-->Add New Item --> Sql Database I got a error msg "object reference not set to an instance of an object" after click "Add" button. The SQL Server Express is running. How do I fix this problem?
Any help would be appreceiated.
CO22006
SSIS File System Deployment Vs SQL Server Deployment
Aug 15, 2007Hi,
Please can you let me know which of the following 2 is a better method to deploy SSIS packages
File System deplyment OR SQL Server deployment
What are the advantages of one over the other?
Thanks,
Mrinali
Database Changes Deployment
Mar 26, 2007Please forgive me if I'm in the wrong category...
How do you deploy changes to a database? Is there something you can run to compare the two versions and create a script to do this? I've heard about the DTS package (now DTSX I believe) but am not sure how to set one up. If anyone could point me to an article or something that might clear things up, it would be greatly appreciated. Thank you!
Deployment With Database
May 17, 2008Hello
I am currently developing a big winforms project that connects to a SQL Server 2005 database.
The idea is that the application will be deployed on random clients whenever needed. These clients have neither SQL Server installed, nor is there a server to provide a central database. The only option is to include the database file with the deploy.
Now, I've searched all around the internet on how to do this. I just can't seem to connect to the database-file from my deployed version. Is there something special that needs to be done?
Regards
- Varcour
Database Deployment - SQL Express
Oct 22, 2007I have an application being developed in VS 2005 using SQL Express. I've built up some new roles and have several users pre-populated that I'd like to deploy to a shared hosting evironment.
The problem is that I seem to keep getting:
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists. Choose a different database name.Cannot attach the file 'd:hostingmember
2agilitysite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.
I saw another post with a similar issue, but that resolution has not seemed to help. Can anybody give me insights as to where the "asanga" is coming from? That might help me diagnose the issue.
Thanks in advance
Deployment - Database Configurations
Nov 14, 2007Greetings,I've been looking around all morning and can't seem to find a good answer on this so I thought I'd post a message here.I'm using the recommended practice of keeping the ASPNETDB.MDF data separate from my site data. (I have my site data located in separate databases but still keep them in the App_Data directory.)I'm using SQL Server 2005 Express on a Server 2003 box and I've implemented scheduled backups on the databases in the Sql Server.The questions I'm having are:1. Why isn't the ASPNETDB.MDF database getting attached to the server? Is it using User Instances?2. If it is using User Instance then isn't this degrading performance? If so, how to force it to not use User Instance?3. If User Instance is how it is supposed to work then how are we supposed to do a full backup of the ASPNETDB.MDF database if it isn't attached in the server? (xcopy doesn't seem to work on User Instances because of the dreaded "is being used by another process" error.)Any recommendations, advice, links, or answers to my questions would be greatly appreciated.
View 1 Replies View RelatedASPNETDB.MDF, My Own Database And Deployment
May 29, 2006This question has been asked before, with vague responses
I'm using Visual Web Developer 2005 Express, SQL Server 2005 Express and SQL Server Management Studio Express.
When I create an application with Login controls, VWD automaticlly creates a ASPENTDB.MDF security database. I also create my own database for my application e.g. abc.mdf. In other words, I have two databases. However, my hosting company supports only one database. What do I (we) do?
I have seen articles on: aspnet_regsql . Does this create a new database with all the security features of ASPNETDB.MDF built into my new database i.e. abc.mdf?
(A) If yes, how do you run it with SQL Server Managment Studio Express? It sounds silly, but I need instructions here please ... Also, do you run this against abc.mdf or do you use it create abc.mdf?
(B) If not, how do you achieve a single database scenario?
I'm sure this is a very common deployment question, which is very confusing for most of us hobbyists that want to deploy their web applications
Accesing Database After Website Deployment
Sep 21, 2006HiI'm using sqlexpress on development machine and on client server. I'm building a webapp using membership and role provided for asp. In web.config I have definet membership provider and role provider to point to my database.After web site deployment I get allways message : Failed to update database "c:appapp.MDF" because the database is read-only, and I'm forced to apply rights for ASPNET. More than that somethimes I must re-register users.I have tried to modify in web.config with <remove name="LocalSqlServer"/> <add name="LocalSqlServer"...> but I get errors if I try to design my dataset file:"Cannot get web application service".TIAPaul
View 4 Replies View RelatedSQL EXPRESS DATABASE DEPLOYMENT QUESTION
Oct 13, 2007
Hi Guys/Gals,
I have developed a database in Visual Studio 2005 (c#) ,using SQL Express as my database, on my local machine and deployed it to a different machine where it is being used in a live environment. No problems.
However, I have continued developing the application on my local machine and have made numerous additions to the structure of the database (on the development machine).
My question...How do I go about deploying the new database structure to the live machine without losing the live data?
Best regards
Jonny M.
Creating A Database For Deployment In SQL Server
Nov 15, 2005I am in the process of designing a Windows Forms 2.0 app, and need advice on the recommended deployment/creation of the database for my customers.
View 1 Replies View RelatedPort Blocking Preventing Deployment Of SSE Database
Jan 27, 2007I am trying to deploy a SQL Server Express database to a remote SQL Server 2005 host. I was planning to copy and paste the data from my local tables to the remote tables using Management Studio Express but my ISP (NTL) appears to be blocking port 1433 and preventing me from getting a connection to the remote database.
Will an upgrade to SQL Server 2005 Developer Edition help me? I understand that it has a Backup & Restore Wizard but how does this work? Does it rely on a remote connection and will it fall foul of the NTL port blocking? Or, can the file that the Backup stage produces be copied into the web-based MSSQL Manager, that my web host provides, to restore to the remote database?
Any help would be much appreciated.
Database Development/Deployment Best Practise For Large Teams
Jan 2, 2008Hi,
I work with a large team developing ASP.NET application that has a large database with over 50 complex stored procedures. It is proving more and more difficult and time consuming to centralise the development and update of the database changes and I was wondering if there were any best practises/tools that could be recommended. I have looked on the web for good articles and haven't found anything difinitive (except that Team Foundation Server is the way forward)..
A brief background to the current process is that everyone develops on the same database, and then updates the stored procedure scripts in source safe (manually). Then when we do a new release someone builds a script of all the database updates and runs it. There are issues related to developers updating there stored procedures over other peoples and other concurrency.
I am looking to move all the developers to start using local databases so that there work only effects them, but then this brings up problems of keeping all the local databases up to date whenever they get the latest source code. The only way I currently see is to build a database update program, that will run and update to the latest version.
Surely this must be a common issue? Anyone have any good ideas/concepts?
Also our setup is Visual Studio 2005, SQL Server 2005 and Source Safe 2005.
Cheers,
Andrew Thomas
Scripting Database Roles For Deployment Upgrades Into Production
Oct 2, 2007I have searched hi and low for hours within the product and online - I cannot find how to have SQL 2005 produce a script to automatically repeat the creation of a database role I have tedious manually created.
I have tried "script object as, create to,<>" - single-liners are produced - no details. More single line results from "<Database>, All Tasks, Generate Scripts".
Am I missing something or is this a bug?
€¦also to note after review blogs and other sources the use of the SQL2005 methods related to security and new schema objects - I feel€¦ dreadfully wrong - what is claimed feels like hype, unsupported by examples and usage cases feels like we better just make all automated processes sysadmin; I know learning curves can be strong but I am not this green or that stupid - where are the DOCS/training materials?
The Database Isn't Working With The Setup Based Deployment Of The Application
Dec 28, 2006
I deployed my application to my newly installed xp pro laptop
The prerequisites are installed
Now that I want to testdrive my database application I get this error:
An attempt to attach an auto-named database file c:/program files........................MyDB.mdf failed. A database with the same name exists, or specified file can not be opened, or it is located on UNC share.
This is what I read in my app.config file;
<connectionStrings>
<add name="MasterDetail.My.MySettings.MyDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|MyDB.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
What could I be doing wrong? If there is need for additional information you have to tell me
Thanks, Richard
Richard
SELECT Permission Denied On Object 'TableID', Database 'Database', Schema 'dbo'
Mar 21, 2007
The error message:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'TestID'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
The log file reads:
---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'TestID'. ---> System.Data.SqlClient.SqlException: SELECT permission denied on object 'TableID', database 'Database', schema 'dbo'.
***Background***
General Users got an error message when trying to access any reports we have created.
All admin have no problems with the reports. Users (Domain Users) are given rights (Browser) to the reports and the Data Sources (Browser) and yet cannot view the reports.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DS2'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
I'll add this from the report logs...
w3wp!processing!1!3/20/2007-11:43:25:: e ERROR: Data source €˜DS2€™: An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source €˜DS2€™. ---> System.Data.SqlClient.SqlException: Cannot open database €œDatabase€? requested by the login. The login failed.
Login failed for user €˜DOMAINUsername€™.
The user has rights via a local group to the report and data source (Browser rights) and the local group has been added as a SQL login.
I gave rights to the databases themselves instead of just to SQL and the error changed (Ah-ha...progress, but why!?!?)
Granting Permission To Object In X Database From A Store Procedure In Y Database
May 16, 2000I need to grant select, insert, update, and delete permission to an object residing in Database "X" from a store procedure in database "Y". I have already tried USE database statement. Any suggestion.
Thanks
Maroof Khan
Database Deployment With VisualStudio 2005 Express Vor Windows Vista
Nov 27, 2006I want to create deployment for MS SQL 2005 Express database, that
creates database, tables and inserts data. I have made a similar with
VisualStudio 2003 and MSDN
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msdedepl.asp?_r=1
For Windows Vista it won`t work. I have tried to create with
Visual Basic 2005 Express, but I didn`t get how can I do it with
ClickOnce.
If you can help me, I`ll bee thankful!!!!
The Database Name Component Of The Object Qualifier Must Be The Name Of The Current Database
Apr 7, 2006Guys,1st of all.. sorry about my english ok? Im from Brazil...Im trying to get a RS connecting through 2 databases with the querybelow:SELECTscar..osbalcao.numeroos,ccell.dbo.atrib_os.osFROMccell.dbo.atrib_osJOIN scar.dbo.osbalcao on ccell.dbo.atrib_os.os =scar.dbo.osbalcao.numeroosWHERE scar.dbo.osbalcao.localprod = '257'AND scar.dbo.osbalcao.status in ('EM')On a SQL IDE i got a good RS, but, when i try to use that same query onDelphi + ZEOS, the SQL Server tells me that "The database namecomponent of the object qualifier must be the name of the currentdatabase".I really dont understand why. I can only tell that the error isreturned from SQL Server.Have anyone got the same trouble?[]'sTiago Gigli
View 1 Replies View RelatedEmbedded SQL Server Express Deployment - Unhandled Exception: Database Already Exists
Mar 7, 2007Good Afternoon.
I am trying to write a short application within which I would like to embed a SQL Server Express database, all of which would be deployed via ClickOnce through a web site. The installation process seems to be working without trouble, including installation of the .NET Framework 2.0 and SQL Server Express 2005.
When the application launches on the new machine, however, I get a big Microsoft .NET Framework "Unhandled exception" error dialog box that indicates the database already exists and that the database file could not be attached. I am working with a clean virtual machine that I know has never had the database installed on it.
I fear I am overlooking something quite straightforward, but since this is the first time I have ever attempted to build a data-bound application, I am not sure where I am going wrong.
Perhaps the source of the problem, or a related problem, might be revealed even when I run the application in debug mode from within Visual Studio. I can run the application once, but if I try to run it again, I get an error when I try to open the database connection that is similar in content (database already exists...could not attach). I can run the SSEUtil and detach the database and then run the application again in debug mode and it works no problem (the first time!).
Lastly, if I install the application on the machine on which it was developed and make sure the database is detached, it will run without any trouble (even repeatedly). But, on a new blank machine, there is no database listed that matches my database name, so I cannot try to detach anything!?
My apologies for such a novice question!
Sean
How Can I Return A Database From The DataBase Explorer Of VB 2005 Express To The Object Explorer Of SQL Server Management Studio
Mar 3, 2008Hi all,
I just realized recently that a database "XYZ" in the Object Explorer of my SQL Server Management Studio Express (SSMSE) is put in the Database Explorer of my VB 2005 Express for processing a Stored Procedure in executing the SELECT statements (not by using Input and/or Output Parameters) during the ADO.NET 2.0-VB 2005 Express programming, then the content of the database "XYZ" is not in the SSMSE. How can I return the database "XYZ" from the DataBase Explorer of VB 2005 Express back to the Object Explorer of SQL Server Management Studio Express (SSMSE) safely? Please help and advise.
Thanks in advance,
Scott Chang
Database...Object Transfer In SQL 6.5
Jul 28, 2000Hi
All,
I am using Databaseobject transfer wizard to transfer objects and data
between two sql server 6.5 servers on schedule basis.unfortunately i specified non existing log directory for transfering these objects.Is there a way to change this this directory in 6.5 or to modify existing object transfer package..
I created several object transfers packages it will take lot of time for deleting and recreate them..
Thanks For your Help,
Kris
Database/Object Transfer In SQL 7?
Sep 24, 1999SQL 7.0 seems to be missing Database/Object Transfer tool, which allows to transfer single objects or whole database from one to another.
Also I couldn't find Backup/Restore one single object option eigther.
Could anyone can comment this, pls?
Michael Gladshteyn
Database Object Transfer
May 28, 1999When I am trying to transfer a database from one server to another everything comes over fine except the Stored Procedures and views. What am I doing wrong?
I'v tried just transfering the SP and views and I am still having the problem.
Thanks
TempTable:There Is Already An Object ...in The Database
Dec 10, 1998I have a stored procedure in which I create a temporary table : ##xyz
The stored procedure runs fine, however if I try to run it again later on
I get the following message:
"Msg 2714, Level 16, State 1
There is already an object named '##xyz' in the database."
If I issue the drop table ##xyz I get the following message:
"Msg 3701, Level 11, State 1
Cannot drop the table '##xyz', because it doesn't exist in the system catalogs
Help!
Thanks in advance...Marisa
Database .....Object Transfer
Jul 28, 2000Hi
All,
I am using Databaseobject transfer wizard to transfer objects and data
between two sql server 6.5 servers on schedule basis.unfortunately i specified non existing log directory for transfering these objects.Is there a way to change this this directory in 6.5 or to modify existing object transfer package..
I created several object transfers packages it will take lot of time for deleting and recreate them..
Thanks For your Help,
Kris
Store Image Object Into Database????
Feb 1, 2005Hi,
How to insert image into database?Anyone know how to do this, please kindly lead me and give me the solution to solve this problem. Thanks a lot.
p@ywen
Invalid Object Name When Trying To Reference A Database
Mar 19, 2006Hi There
Let me start by explaining what I am trying to achieve as there may be a better way to perform this. I have two databases and I want to refer to four tables - one in one database and three in the other in a sql select statement.
When I use the query builder in VWD I can only see tables for the database that is defined in the connection string - so therefore how do I add tables from another database? After reading a few posts I realized that all I have to do is use the syntax: database.owner.table (or [database].[owner].[table]). Unfortunately this didn't work and came up with the error "Invalid Object Name".
Assuming that I must have got the owner incorrect I used the Query Tool in SQL Server Express Edition and it worked perfectly! So my sql must be correct, but why will it not work inside VWD?
The two databases are named assessment and aspnetdb - the assessment database contains the table reviews and the aspnetdb database contains the tables TutorDetails, EmployerDetails and StudentDetails.
Here is the connection string and sql:
Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Assessment.mdf;Integrated Security=True;User Instance=True
SELECT Reviews.Programme, aspnetdb.dbo.TutorDetails.Tutor_DisplayName AS [Tutor Name],
aspnetdb.dbo.EmployerDetails.DisplayName AS [Employer Name], aspnetdb.dbo.StudentDetails.Student_DisplayName AS [Student Name]
FROM Reviews INNER JOIN
aspnetdb.dbo.StudentDetails ON Reviews.StudentID = aspnetdb.dbo.StudentDetails.Username INNER JOIN
aspnetdb.dbo.EmployerDetails ON Reviews.EmployerID = aspnetdb.dbo.EmployerDetails.UserName INNER JOIN
aspnetdb.dbo.TutorDetails ON Reviews.TutorID = aspnetdb.dbo.TutorDetails.UserName
WHERE (Reviews.ReviewID = 15)
Disregard the value of '15' in the WHERE statement as this was just put in to ensure I got some valid data back, this will be replaced with a variable eventually.
I know the sql statement works properly, I think the problem may be something to do with either the connection string - any ideas would be much appreciated.
Regards
Stuart
How To Find Who Is Deleted The Database Or Object?
Aug 5, 2003Hi,
I wanted find the log information if anybody deleted my database or bojects in my server.
Is it possible we can able to find this details in my previous log or any sys tables ?
Thanks