I have a remote database on a server.
I am an authenticated user to a database on the server.
I want to get the data in the database to my local computer
I don't have a shell (or any other way) access to the directory
related to sql server
so what can i do to get a copy of the data?
I am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.
I can create a linked server using the option "Be made using the login's current security context" but can not when trying to specify the security context, i.e. sa and the sa password. When I try I get the following message:
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.
I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.
The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:
Executed as user: NT AUTHORITYSYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.
I'm sure the two errors are related. Any ideas what is going on?
i have a local SQL Server Express 2005 on my local machine and i want my software to update from the SQL Expres to the SQL Server when the user click the save button to keep an image of the local database on the remote server without having to backup a file and restore it?
*** I am using C# and SQl Server 2005
Hi All,
I am completely new to VS2005 and .net2.0 but I am learning fast! But one thing that I am struggling with is the deployment of my website, or to be more precise the deployment of the data that was created during development on my local machine.
I have used VS2005 to create a simple personal site. I have created a single user (myself) in an admin and friends role. So far so good. The site works great on my local machine, all the default photos provided with the site display correctly etc and I can log in OK.
My remote server (ie my web hosts) provide me with a separate SQL server on which to install my databases. I have created a database on this server and configured it so that it contains all the correct tables and schemas etc. I have modified my web config file to connect to this database and when I browse to my default.aspx page it displays in my browser fine...EXCEPT that none of the data ( ie the photos and user data) is in the database so therefore no pictures display and I can't log in!!
What I can't figure out is how do I get all the data from my local app_data folder into my new remote database!!?? I can't find anything that explains how to do this. Surely this is one of the most important operations of all since data is what .net2.0 is all about!
Thanks in anticipation
I have a form to submit some data to a database located in the App_Data folder. I have the following connection string:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Siven.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
On the form I'm using a DetailsView control and using a SqlDataSource as the datasource, pointing to the above connection string. In realtime, when i submit the data, I receive the following error:
Server Error in '/' Application.
An error has occurred 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I'm using discountasp.net as the host, and I did not elect to use the SQL Server databases, I'm just going to use SQL 2005 express. I dont understand why it's not pointing to the db when it's there (and to make sure, I re-uploaded). Is there something I missed here?
I have a SQL Server 2005 instance which resides on a dedicated server hosted by a third party. I am able to connect to this server through my local SQL Management Studio, however, my local ASP.NET 2.0 web application cannot connect. I get the error below. The connection string I use is formatted as...
Connection String:
Server=thesever;uid=myuserid;pwd=mypassword;database=mydatabase
Error:
Failed to connect to database: An error has occurred 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)
The hosting providr claims that the SQL Server configuration settings are as they should be as fars as TCP/IP and Named Pipes are concerned. Does anyone know what might be going on?
I know this is strickly not a website question, but dunno where else to post...To remotely admin and monitor some functions of the website, I wish to use a local application to connect to the MSSQL DB which is held on the remote webhosting serverI have the following code: Dim StrSQLUN As String = "[UN]"
Dim StrSQLPW As String = "[PW]"
Dim StrServer As String = "[IP][INSTANCE]"
Dim StrDB As String = "[DB]"
Dim strTimeOut As String = "Connection Timeout=0;"
Dim pStrSQLConn As String = "Server=" & StrServer & ";Database=" & StrDB & ";User Id=" & StrSQLUN & ";Password=" & StrSQLPW & ";" & strTimeOut
Dim sqlConn As New SqlClient.SqlConnection(pStrSQLConn)
If sqlConn.State = ConnectionState.Closed Then sqlConn.Open()
This has basically been take from the existing code on the website, but changing to the server details. I had just started dev'ing this app when the admins decided to move the SQL server over to a different server. It was working on the old one, but the new one doesn't. It just times out after whatever time you put in the timeout variable. 0=unlimited, and so just sits there.I am also using the MSSQL Server Management Studio locally to connect to the same database, and although slow, does connect after about a minute or so. I thought they would be using the same type of underlying connection to access the server and database? Is this correct?
Can they put restrictions in place for this specific sort of data access?Does anyone have any suggestions on how to resolve this issue??Thanks for any helpAdam.
Hi Guys!
I'm trying to find a way to either importexport or synchronise an SQL 2005 db between a local and remote server, is there any way to do this programatically or otherwise? doesn't have to be the entire db, (like just aspnet_users and related tables will do)
Thanks for the help all!
Hello everyone.
I'm currently trying to figure out a good way to keep my Local and Remote Databases in sync. Whenever I make changes to one or the other I spend a lot of time manually adjusting the other to match. I want to be able to set up an update page that accesses my Remote database and Local database and Updates the two accordingly.
I really don't know where to start with this one. I'm not sure at all as to how to simultaneously connect to two different databases, on two different servers, and Udate from one another. Does anyone know any good articles to get me started?
Thanks ahead for your replies.
-Alec
Hi all, I have moved hosting companies and am trying to get my website and MSSQL database sorted on the new server. I am using an enterprise type tool and can connect to the database the new hosting company has created on their SQL server with no problem. I now want to upload the data I had in the database previously. All I have to do this from is .BAK and .TRN files on my local hard drive. Using the restore option from the menu, I tried to select the .BAK file I have saved to restore from it, and got the following error:
"EXECUTE permission denied on object 'xp_availablemedia', database 'master', owner 'dbo'."
I also tried the advice given in this post as the problem seems similar and follows the same pattern: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40841
but there are loads of users listed for sp_who - it seems to be the users for all the databases on the host's server so I didn't follow the next step to disconnect them!
Any advice as to what I need to do would be much appreciated.
Cheers
Lisa
Hello-I have a Sql Server 2000 database offsite that I would like to back upto a local machine. I am using Enterprise Manager on a local machine toadminister the remote db.Whats the best way to schedule backups so the remote db is backed up tothe local machine?Can this be done in Enterprise Manager?Would you recommend any 3rd party software?Thanks!MB
View 1 Replies View RelatedI have a bak file backup of my database which is a database backup done via SSMS 2008.I have imported this bak file to SSMS 2014 and working currently. The bak file is about 4 gb size.I need to transfer the entire database to remote server which is also sql server 2014. How can i do this efficiently?
View 3 Replies View RelatedHello all,
We are currently testing all our applications on Vista and SQL Server 2005. Everything is running and working, but our applications that connect to SQL Server 2005 are running very slow. We have our client installed on a vista pc running SQL Server 2005 on the same pc. I was trying to troubleshoot the problem and realized that we had "Local connections only" checked in the Surface Area Configuration tool, and changed it to "Local and remote connections". After making this change all our apps seem to be running much faster. I just didn't expect this to be the problem since we were connecting from the same computer that is running the server. Can anyone explain to me why this has made the difference in performance?
Thanks for your time and attention,
DrewV
Hi
I am trying to deploy our service broker application into our live enviroment. I have been using the excellent service listings manager to configure security and routes when deploying to our test enviroments and had no problems. However, after having gone through the same steps in live, i can not get it working.
The messages I try sending from the remote initiating service arrives at the target service, but the ack is not sent back from the target service. I have used profiler to find out what is going on and noticed that on the target after the ack is received, a broker:message classify event occurs with an event sub class of 1 - Local. It then starts going through lots of broker: Message undeliverable events. From what I understand this means that the target service has classified that the reply to the message should be sent to a local service instead of a remote service which is not the case.
I think the remote route is set up correctly on the target server. I have run this query on the target server after one message has been sent
select r.*
from sys.conversation_endpoints e
inner join sys.routes r on e.far_service = r.remote_service_name and e.far_broker_instance = r.broker_instance
and it returns the row from sys.routes that points to the initiating service.
Can anybody help with what else I can do to solve this problem?
Antony
What is the usual way to develop ASP.NET websites locally using a database and then migrate everything to the real webserver?
Here's what I'm doing now -- tell me what I should be doing instead (I'm a newbie developer).
I'm using Visual Studio 2005 Standard with a SQL Server 2005 database, to develop a small C# ASP.NET website. I'm the only developer.
Hello,
i've developed a website using VB.NET, and SQL SERVER 2K, in my dev environment works properly (SQL Server and WEB SERVER are in the same machine).
When i put everything in the target machine, where the webserver is but not the sql, i get the following error:
Sql SErver does not exist or access denied
I've tried so many ways to do this, ODBC, ADO.NET, OLEDB.....and everything ends in the same error.
Also if i try to connect with ODBC (from odbc manager) it seems to work (test connection succesfull), but in asp.net nothing works.
I've tried to access the server with:
INSTANCE_NAME
SERVER_NAMEINSTANCE_NAME
IP,PORT
I think (they are checking this), that the auth mode is set to MIXED MODE.
Any idea to save my life?? ;)
Thanks ppl
Is there an easy way to backup a database on a remote SQL Server to a backup file on my local computer?
View 3 Replies View RelatedI have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).
However, when I execute a query from a remote machine I get an error (see below for the exact message).
I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?
Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?
Errors:
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".
Environment: SQL 2005, Standard Edition SP2
Thanks in advance for any suggestions.
David Rueter
drueter@assyst.com
how ?
View 4 Replies View RelatedGreetings friends,
Not sure if this is the right place for my question but here goes anyway.
I have an instance of SQL Server 2005 installed on my DEV PC. A colleague of mine wants to access my server from his machine which connected to the same network.
I logged on to SSMS and added him as a new login.
Will he be able to connect to it now or do I have to do more than just that?
Your help would be appreciated.
Hi 2 All,
I have installed MS SQL 2005 in my system. I have created one database in my server. When iam running my application with this connections it is working fine in my local system. When i uploaded the database to some where dataserver, Iam not getting the connectins due to no remote access .
Some body suggested that to start the SQL browser in the SQL Server Configuration Manager.
I go there n tried to start the service when i put the service properties to Automatic or Manual i am getting the error message that is saying that the service is disabled cannot start automatically.
What could be the wrong?
Please suggest me.
Thanks and regards,
I am not able to connect my local SQL Server Management Studio to a remote server hosting our Integration Services. I get the infamous "Access Denied". I have walked through Kirk Haselden's howto at:
http://www.sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx
but the problem persists.
Thanks for any help.
Keehan
Hi all,
I would be very glad if someone can suggest me what techniques I should use in the following scenario:
I have 2 SQL Server databases : DB1 and DB2. DB 1 is on a remote server (hosting server) and DB 2 is on a local server.
Some tables of each db contain tables that are polulated and changed by the appropriate application, i.e.:
DB1.users, DB1.orders,... etc are managed by "webapplication"
DB1.products ... are not managed by "webapplication" : in fact only used to read from
DB2.products, DB2.customers, .... etc are managed by "winapplication"
DB2.orders,....are partially managed by "winapplication"
Since the amount of data can go over 100000 records i'm wondering what would by the best approach to :
- synchronize the data of DB1.products and DB2.products in DB1 on remote server (updated newly added rows and update changed rows,....)
- the products data is only (at the moment) added, edited and deleted on the local server
I think SQLBulkCopy will not do the job. Should it be possible with some query? Or...?
Any suggestions are appreciated!!
O.
Excuse me, I’m a relative newbie.
My question: What’s the best practice for developing locally and then uploading to a production web site?�
Here’s my plan:
I will use ASP.NET 2.0. I want to use Visual Web Developer 2005 Express Edition to develop locally using SQL Server 2005 Express Edition (.mdf file).
Then I want to upload my changes to a web site that uses the full version of SQL Server 2005 (not the Express Edition.)
I know how to upload my .aspx files to the remote site using Visual Web Developer.
But how should I handle my data? I imagine I can keep a little test data in my local .mdf database, and then just make sure the tables on the remote SQL Server always exactly mirror what I’ve got locally.
Then I just upload any changed .aspx files, which should work both locally and remotely.
Is this the best way? Or will I have to every time first change something in my .aspx files, to handle the data connection difference between my local db to the remote db?
Much Thanks
Hi
I have an sqlserver setup locally to chew thru pricefiles from our suppliers. The pricefiles comes in various formats mostly txt-files and are inserted via bulk-inserts and separeted by GO-statements, in the end I end up with a table containing unique partnumbers, the average price for the part, and number in stock. What i would like is to have a transact- statement in the end of all thoose sql-statements that copies the final table to a remote sqlserver that contains the DB used on my website. How do i connect to a remote server thru a sql-statement? is it possible?
If i just get the connection part explained, i can figure out the rest by myself.
Thanks
/Jonas
Hi and thanks in advance for your help.
I have a dilemma and I need some expert help with this. I am trying to make a copy of a remote Database and then replicated the DB and it's contents on my local machine so I can build a test site to run data with. I tried to remot into the server machine but I guess that feature is not allowed by the honest of the remote machine. Since I build on my local machine and would like to be able to test data on my local machine.... I would like to copy the remote DB to my local computer and then I can run my test app on my local server. The version of SQL on the remote machine is SQL Express 2005 and I have SQL 2005 on my machine. Please give me how to accomplish this please.
Dollarjunkie
Is there any way to backup a remote SQL Server that is on a hosted account to a local drive? We have a web hosted account that has a SQL Server that we do not have full admin rights on, just dbo access to the data and structure. We would like to be able to do backups and restores to our local development server if possible. Perhaps vis DTS or some similar means? WE cannot use the normal backup/restore as we do not have right to these fucntions nor can we access the servers local drives directly.
View 2 Replies View RelatedHi All,Im testing error handling on my website and if I stop my local sql server 2000 then run the web app, I get a SqlException which is good, but when I restart the sql server then I get a System.InvalidOperationException that will not go away. ( its coming from a ExecuteScalar() call) This happens on my remote database as well.It also happens if I kill the connection on my remote machine then reconnect. after I reconnect I can access the database using dbvis so I know its there.Am I not clearing something after the first error? If I restart the machine all is well.Thanks in advance for you help!-Keith
View 2 Replies View RelatedHello all,
I'm a bit new to SQL Server and T-SQL (my background is in DB2), so hoping you can help me with this. I'm writing a T-SQL script that's hopefully going to scan a bunch of SQL server instances and record the results. We've got a mix of SQL2K and SQL2K5 instances, and for part of my script I only want to run something if the remote instance is SQL2K.
I'd tested this on my local PC with several instances created and it worked fine. When I try and run it for instances located on other servers it barfs :eek: . From what I've found from looking up the error message it looks like I've got myself a loopback -- but I don't know how to get around it - any ideas please?
DECLARE @tempdata TABLE (scratch sql_variant)
DECLARE @debug tinyint
DECLARE @server sysname
DECLARE @version varchar(20)
SET @debug=1
SET @server='SQLSERVER_INSTANCE'
INSERT INTO @tempdata (scratch) EXEC ('[' + @server + '].master.dbo.sp_executesql N''SELECT SERVERPROPERTY(''''ProductVersion'''')''')
SELECT @version = convert(varchar(100),scratch) from @tempdata
DELETE FROM @tempdata
IF @debug>0 BEGIN PRINT @server + ' is running SQL Server version: ' + @VERSION END
IF charindex('8.00',@version) > 0 BEGIN
PRINT 'SQL2K-only code goes here'
END
The error I get is:
OLE DB provider "SQLNCLI" for linked server "SQLSERVER_INSTANCE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SQLSERVER_INSTANCE" was unable to begin a distributed transaction.
I get this error running from a SQL2K5 instance against both SQL2K or SQL2K5
TIA, moff.
Hi all
I am about updating some fields with local date and time using blw API
Quote
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Public Function LocalDateTime()
Dim MyTime As SYSTEMTIME
GetLocalTime MyTime
Debug.Print "The Local Date is:" & MyTime.wMonth & "-" & MyTime.wDay & "-" & MyTime.wYear
Debug.Print "The Local Time is:" & MyTime.wHour & ":" & MyTime.wMinute & ":" & MyTime.wSecond
End Function
Unquote
This works fine however due to it's not really easy for an administrator to keep checking every times wheter local date and time are correctly updated for each front end users computer i would that to reconsider my function and making it to read local date and time from remote SQL Server. my concern is to help administrator to save time as he should only need to ensure that Server is running with correct time...
thkx for any advise
I have a SQL Server 2014 installation on a server (CHRIS-PCCHRISSQL).I have SQL Server 2014 management studio installed on local server called Pootle.I have gone through the configuration on server (CHRIS-PCCHRISSQL) inc the following:
(1) I have set up a user on CHRIS-PCCHRISSQL called sqladminuser at server level with the Server Roles of 'Public' and 'SysAdmin'
(2) The computers are both on the same homegroup.
(3) On Chris-PC , I have opened up the firewall port 1433 as Inbound Rule
(4) On Chris-PC, Within SQL Server Configuration Manager,the 'SQL Server Network Configuration for Protocols for CHRISSQL' have been set up as follows:
- The TCP protocol is enabled
- I have set up IP2
as follows:
Active: Yes
Enabled: No
IP Addres: 192.168.0.3
However when I try to connect from SQL Server Management Studio 2014 on my local machine Pootle to Chris-PCCHRISSQL using SQL Server Authentication with the user sqladminuser
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)how to set up a remote connection from SQL Server Management Studio
on my local machine Pootle to CHRIS-PCCHRISSQL
We currently have a PPTP connection set up for our developers toaccess our development SQL server through a VPN tunnel. When theyneed to copy tables up to the dev SQL from their local machine theysimply do a DTS copy.However, we are now moving to a thin client solution where they willbe working on a terminal server. They will have access to thedevelopment SQL servers and SQL tools such as EM and QA. However,they will not have access to their local SQL server and, therefore,will not be able to directly perform DTS copies. We have exploredseveral possibilities such as exporting tables to a .csv or .mdb fileand then importing them on the development SQL server but this is notideal because things are lost in that process (e.g. primary keys,field names, data types, etc.)My question is this: Is there a way to export and then import SQLtables without losing dependent objects such as primary keys and datatypes in the process? If any of you are working with a similarsituation I would really like to hear how your remote users copyobjects from their remote location to your SQL servers. Thanks!Ryan--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict211310.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630
View 4 Replies View RelatedHi all,
Currently, my (small) intranet site is storing it's data on a remote SQL server. The danger with this, as has happened several times now, is that the application is twice as vulnerable; if either the webserver or the dataserver malfunctions or is unreachable, the application won't work.
I only recently discovered the possibility to use local database files (MDF files), and this seems like a much better solution for my site. But now I want to transfer the tables that are residing on the dataserver, to the MDF file. The database only contains tables. How do I handle this? I do not have access to the dataserver, only to a few databases that are residing on it. Is this possible using Visual Studio 2008? I have read about a "Bulk Copy Program" (bcp) which is included with SQL Server, but I cannot find a download for just that application.
Or is this totally not the way to go? I've discovered MDF files are a bit more problematic with concurrent connections; having tables open in Visual Studio results in "Site offline" or "Cannot open database" error messages on the website. Problems I've never had to deal with using SQL Server, but they are only minor problems.
Thanks,
Peter