Is there a way to setup a mapped database between ms sql servers? I have 2 mssql servers, however, if someone connects to sql1 and tires to use a database that son sql2, i want it to map to that without giving an error that the database doesnt existst.
I tried linking them but it still says "xxx database doesnt exists" since it recides on the other server.
Is it possible to create a database (MSSQL2k) on a mapped drive which is not a SAN?
Thanks,
Peter Schauss
How to Restore database from a Mapped Network Drived where i have taken
backup from a production server and want to restore in Development server, without copying it to local harddrive. I do not have enough space on my local Drive. I am trying Following command, but i get error, saying cannot recognize 'g:mssql7ackupProduction.bak'.
RESTORE DATABASE TestRestore
FROM disk ='g:mssql7ackupProduction.bak'
WITH REPLACE,
MOVE 'TestRestore' TO 'c:mssql7dataNewNwind.mdf',
MOVE 'TestRestore_log' TO 'c:mssql7dataNewNwind.ldf'
Where g is my mapped drive Pavilion$h
Hi
I have VC++ express and MSSQL SMS express and have an application working nicely locally. The Data explorer and data connections part works really easily.
Now, I want to make the application available to my home network.
I mapped the drive where the database is and called it Z: so I could put my "release" on my other network PC and assumed it would find Z: if I mapped the shared network drive on that machine and called it Z:
But: I can't even add the mapped connection on the local machine, I get:
The file "Z:databasescalorie.mdf" is on a network path not supported for database files. An attempt to attach.....etc"
It works fine on the original F drive.......
Am I approaching this the wrong way. How should I distribute to network PCs?
thanks hopefully
David
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
   @server = N'Machine123Instance456',
   @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'Â Â
Hi, Everyone,
Can any one tell me how to monitor the users connected (by mapping) to my windows 2000 machine? Thank.
Hi,
I mapped a drive on to my SQL Server box. It points to another server from the same domain. When I try to backup or restore a database, I can't see this mapped drive through my SQL Server. Even if I type the entire path, SQL Server wouldn't take it. I don't have a clue about why it is not working. Can anyone throw some light on this. Your help is grately appreciated.
Thanks,
Varma
I created a DTS local package on the SQL Server. It's basically importing a text file into a table in my database. This file resides in a mapped drive (X:) from another server. When I schedule the DTS as a job, it fails. It doesn't execute any of the workflow in the design.
However, when I copy the text file into a drive local to the SQL server (D:), it runs flawlessly.
What I do right now is I have a windows scheduled task that runs a batch file that copies the text file from X: to D: at certain time intervals. Then the job scheduler runs to import it.
What am I missing? How come the job scheduler can't read the file directly from the mapped drive?
Any info would be appreciated. TIA
Hey guys
I am having a problem with a DTS package that pulls from a flat file off a mapped drive. When the package is ran alone, it runs perfectly but the stored proc that I took from an example from the net will not execute the DTS properly and I am unsure as to why it will not do so.
CREATE PROC spExecuteDTS
@Server varchar(255),
@PkgName varchar(255), -- Package Name (Defaults to most recent version)
@ServerPWD varchar(255) = Null,-- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@IntSecurity bit = 0,-- 0 = SQL Server Security, 1 = Integrated Security
@PkgPWD varchar(255) = ''-- Package Password
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @hr int, @ret int, @oPKG int, @Cmd varchar(1000)
-- Create a Pkg Object
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUTPUT
IF @hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @oPKG, @hr
RETURN 1
END
-- Evaluate Security and Build LoadFromSQLServer Statement
IF @IntSecurity = 0
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "' + SUSER_SNAME() + '", "' + @ServerPWD + '", 0, "' + @PkgPWD + '", , , "' + @PkgName + '")'
ELSE
SET @Cmd = 'LoadFromSQLServer("' + @Server +'", "", "", 256, "' + @PkgPWD + '", , , "' + @PkgName + '")'
EXEC @hr = sp_OAMethod @oPKG, @Cmd, NULL
IF @hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Execute Pkg
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Check Pkg Errors
EXEC @ret=spDisplayPkgErrors @oPKG
-- Unitialize the Pkg
EXEC @hr = sp_OAMethod @oPKG, 'UnInitialize'
IF @hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
-- Clean Up
EXEC @hr = sp_OADestroy @oPKG
IF @hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @oPKG , @hr
RETURN 1
END
RETURN @ret
GO
that is the stored proc that i am using along with a couple error trapping ones but this being the one that does the actual execution. Is there anything i can change about this in order for it to run the DTS properly from the mapped drive?
thank you
Hello every body
what is certificate-mapped logins? and how we can authenticate with these logins?
thanks
From my workstation (with SQL Server 7 Desktop Edition SP3), I seem unable to restore a database on my Server (SQL Server Standard Edition SP3). I am logged into both machines, and I am an Administrator on both machines. Using either a UNC or Mapped Drive (see below)
RESTORE DATABASE ogAEC FROM ogAECDump WITH REPLACE , RECOVERY , STATS
, MOVE 'AEC_Data' TO 'Og-sqlsrvrC-DriveMSSQL7DataogAEC_Data.MDF'
, MOVE 'AEC_Log' TO 'Og-sqlsrvrC-DriveMSSQL7DataogAEC_Log.LDF'
RESTORE DATABASE ogAEC FROM ogAECDump WITH REPLACE , RECOVERY , STATS
, MOVE 'AEC_Data' TO 'Q:MSSQL7DataogAEC_Data.MDF'
, MOVE 'AEC_Log' TO 'Q:MSSQL7DataogAEC_Log.LDF'
I get
Server: Msg 3156, Level 16, State 2, Line 1
The file 'Og-sqlsrvrC-DriveMSSQL7DataogAEC_Data.MDF' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
But I am successful if I run the essentially command locally from the server:
RESTORE DATABASE ogAEC FROM ogAECDump WITH REPLACE , RECOVERY , STATS
, MOVE 'AEC_Data' TO 'C:MSSQL7DataogAEC_Data.MDF'
, MOVE 'AEC_Log' TO 'C:MSSQL7DataogAEC_Log.LDF'
What can I do to be able to restore DBs from my workstation?
Hi,
I am trying to grant users permissions to our database, but when I add the users through a local group, I receive a mapped name of DOMAIN_USERNAME.
We are using standard security, with the SSQL server in a resource domain. I have created a local group with the global group inside it from the accounts domain. This seems usual MS practice to me.
We do not want these long cumbersome login names, but I am slowly worrying that there may perhaps be no way around it. Can't they just login with their normal username?
Any light...
Jack.
Due to a previous (mis)configuration, i need to grant readwrite permission on a share from a MSSQL DB User.The SQL user will launch t-sql queries on demand and they cannot be scheduled.
I've created a credential object in SSMS, configured it with the correct AD user and mapped it to the MSSQL DB user.Now, if i execute a simple t-sql backup:
BACKUP DATABASE [DB] TO DISK = N'IP.ADD.RE.SSshareDB.bak' WITH NOFORMAT, NOINIT, NAME = N'DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
I get an access denied error, monitoring the sqlserv.exe process via procmon, i see that the Sql Server process is not impersonating the AD user configured in the credential, it still try to access using the local machine account .
If a user is mapped to "master", (in login properties, user mapping) are they able to access all dbs, even though "master" is the only one with the check mark?
View 3 Replies View RelatedHi,all.
I tried to issue:
sp_attach_db pubs,'F:anthonypubs.mdf','F:anthonypubs.ldf'
where the pubs.mdf and pubs.ldf are on a remote mapped drive F:,
I got error:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name 'f:anthonypubs.ldf' may
be incorrect.
Is attaching a database ONLY supported for local servers ??
What about creating database on remote drive? (I have same error msg back)
Thx
Anthony
I am new to the DB Administration.
How do i back up the data to a network drive mapped on a day to day basis.
Our company is migrating a Microsoft Access 2010 backend database to a SQL Server 2008 database. One of the memo fields in the Access backend can store up to 150 Kb of Unicode data. To store this data in SQL server, we found that we can use the following data types:
ntext = (2^30) - 1 = 1,073,741,823 bytes.
nvarchar(max) = (2^31) - 1 = 2,147,483,647 bytes.
Because ntext will be deprecated in future releases of SQL Server, the only good alternative to store an Access memo field in SQL server is to use nvarchar(max), which is what Microsoft recommends for large Unicode texts.Storing a large amount of text like 150 Kb in an nvarchar(max) field using only SQL server works as expected. However, if Access is used to store the data in a table linked to SQL server, the maximum number of characters allowed is only 4000. We found that this limitation is imposed by the ODBC driver that limits nvarchar(max) to 4000 characters.
The connection string we are currently using to link a table to SQL server is this:
ODBC;DRIVER={SQL Server Native Client 10.0};SERVER= SQLEXPRESS;DATABASE=TestDB;Trusted_Connection=No;UID=uid;PWD=pwd;
Any solution for this limitation storing large amounts of data in a Microsoft Access memo field mapped to an nvarchar(max) data field in a SQL Server database?
In my SSIS package I have a text file source that I am mapping to a destination table. I have an error component that logs any row level errors and have noticed that it is not logging the correct field. I know this because I have a few different sources that submit the same files and have looked at the source of both. THE ONLY DIFFERENCE in the one that works versus the one that does not is that 2 of the 25+ columns are switched. I would not think this would matter because field A in the text file is mapped to field A in the database.
Does the order in which the fields come into the SSIS package matter?
Thanks
is any way to select SQL logins that are Mapped To Credentials as using this query I get only NULL:
SELECT credential_id FROM sys.server_principals WHERE name = 'test_login'
I see the list of Mapped Credentials in SQL login properties with GUI, but can not select this option.Â
1) what is the use of mapped to certificate/asymmetric key/credentials
2) how to use mapped to certificate/asymmetric key/credentials
3) in which conditions we can use mapped to certificate/asymmetric key/credentials which is present on login properties page
[URL]
Is there a form of ALTER USER or other option that would allow me to change the Windows-based login associated with a user account?
According to BOL, I cannot use the sp_change_users_login trick since I want to rewire up to a Windows login, not a SQL Server login.
-- Margo
Hi,
I have a rather odd problem that hopefully you'll be able to shed some light on.
We want to back up the databases to a hard drive held on another server so I mapped the drive in explorer to the drive then went into Enterprise manager and tried to create a backup device and it won't see the mapped drive.
I've tried mapping to my PC and I can see that via enterprise managers backup stuff (infact any PC in the office works) but it won't see any of the servers even though we can map to them and access them via windows explorer.
I've tried when logged on via sa and the windows NT administrator and still no luck. In fact no matter what I type or do it fails and keeps telling me device error or device off line which it isn't.
On our test instance of SQL Sever we can backup to other servers but not the new live version!
Any thoughts on what might cause this to happen and how to fix it?
Help much appriciated.
Thanks Helen
Hi,
I have a rather odd problem that hopefully you'll be able to shed some light on.
We want to back up the databases to a hard drive held on another server so I mapped the drive in explorer to the drive then went into Enterprise manager and tried to create a backup device and it won't see the mapped drive.
I've tried mapping to my PC and I can see that via enterprise managers backup stuff (infact any PC in the office works) but it won't see any of the servers even though we can map to them and access them via windows explorer.
I've tried when logged on via sa and the windows NT administrator and still no luck. In fact no matter what I type or do it fails and keeps telling me device error or device off line which it isn't.
On our test instance of SQL Sever we can backup to other servers but not the new live version!
Any thoughts on what might cause this to happen and how to fix it?
Help much appriciated.
Thanks Helen
Although clearly not best practice for a produciton DB, is it supported to attach MDF/LDF files that are hosted on a network drive?
I have been exploring options to host a read only archive database that we need to access on a very short term adhoc basis.
I am setting up 4 Windows 2003 server with SQL server 2005 .
I am using a new SAN device and I am setting up over 37 separate devices.
Three of the machines will be the production machines. The fourth machine will
contains log shipped copies of 37 databases.
I would like to keep each the 37 databases on its own device on the new SAN device.
The problem that I am running into is that I can only map drives up to the letter Z .
Is it possible to map more than 26 drives on a Windows 2003 server ?
I need to retrieve the pts from table #test1 based on the fields when mapped..
Name & Type columns should match ..Based on the data below the output should be
id NameTypeCode CityIType BPS
1EGFN432 HY F2 10
2 EG FN 432 ON F1 20
3 EG FN 433 On F1 30
It has order of priority to get points
1.When all of the field matches then get the respective pts .
2.When name and Type matched and rest fields doesnt match but is null in #test2 table then retrieve those pts
--drop table #test
--drop table #test1
Create table #test
(
id int identity(1,1) not null,
Name varchar(100) NULL,
[Code] .....
This has been extremely confusing for me.
I want to just make a simple backup.
first of all when i choose the pick a folder to backup, no mapped drives I make are even THERE.
I realize this is probably related to the account being used, okay I thought let me change the user account to a network admin account... I still cannot see the drive.
Can't this thing just accept whatever I tell it to access like any other program??
You would think they would at least keep the standard Open File dialog so we can use the network browser or something...
I've changed my accounts all to NETWORK SERVICE, then LOCAL SYSTEM, then a DOMAIN ADMIN...
I can't get this to work correctly on this freshly installed server... can someone please help?
I'm at the point where I don't care if i have to just re-install the damn thing...
Just someone please tell me what to pick for the accounts.
Bonus: I have this same issue with reporting services and Services for Unix NFS Mapped drives.
How can I map a drive with NETWORK SERVICE Credentials so it finds the datasource path?
I've only been able to do something like this with psexec and Local System.
When logged in as Domain Admin it will show a disconnected network drive that you cant get rid of but system account can use.
I got thrown into a new project that is going to require me to update an SQL server database tables from an Access table on the backend of an Oracle database on another server. At the end of each day the Access dabase will be updated from the Oracle database.
What I need to do, is when the Access database is updated I need to have the table in the SQL database automaticaly updated. When a new record is added to the Access table I need the new record added to the SQL table. When a record is deleted in the Access table I need to keep that record in the SQL table and set a field to a value (such as 0). And when a record is updated in Access, have it updated in SQL.
Needless to say this is a bit out of my area and not sure how to accomplish this.
Any help is greatly appreciated.
I'm trying to create a job that will copy a backup file from a mapped network drive to a local folder however it keeps failing due to a privilege.
The command is:
copy 10.10.10.5f$ est.nightly H:MSSQLBackup est.nightly
I can run the command successfully from the command prompt, however using the same command in a SQL Server job it fails with "Access is denied".
How do I configure the mapped network drive to allow the Sql Server Agent to get access to it?
I have a Microsoft Access 2007 Database that I want to move the data from Access 2007 to SQL Server 2005 Express. I have SQL Server 2005 installed but if I link to the Access 2007 Database so I can run the insert into Query to copy the data I get an error message stating that the linked Server failed the connection test. What am I missing here? I used the
sp_addlinkedsvr to link the access Database and I've used the sp_addlinkedsvrlogin but nothing works. I get the same error message every time.
Hi,
We have a Webshop that is very old and we can't change the SQL server that it access.
But we had to upgrade the MS SQL server to a new hardware so we now have two SQL 2000 servers (the old one and the new one). All relevant data is on the new one but the webshop ONLY read from the old one)
How can I link the database on the old server soit can read and write to teh database on the new server.
What I mean is that I want the webserver to access the old SQL server (with a linked database to the new SQL server) so all data on the old database is the new database.
Is this possible and in that case how?
Both SQL servers are SQL 2000 (old one SP3 and new one SP4) Its no problem to upgrade the old one to SP4 aswell.
/Martin
We have SQL Server 2000 on a Windows 2000 server and a mySQL database running on a Windows 2000 server.
We have used MyOLEDB driver(OLEDB) to link the mysql database.
How can I access the data stored in a Linked server?
Any help would be appreciated.
Thanks!!
this is our code¡G
exec sp_addlinkedserver @server='OLEDB_test',
@srvproduct=N'',
@provider=N'MySQLProv',
@datasrc=N'203.xx.xx.xx',
@catalog = N'Store'
exec sp_addlinkedsrvlogin 'OLEDB_test','false',null,'root',''
select * from OLEDB_test.Store.root.Books
or
select * from OLEDB_test.Store..Books
error message:
Can't create OLE DB Provider 'MySQLProv' instance¡C
OLE DB error trace [Non-interface error: CoCreate of DSO for MySQLProv returned 0x80040154]¡C
by the way¡G
1.We Create an ODBC System DSN that points to our MYSql server
2.We use OLEDB for ODBC to create a linked server to mySQL
3.We have got the query to work with openquery
but we can't update¡Bdelete¡Bmodify the data stored in a Linked server
Hello!
I am Paolo, a 3rd year college student taking up computer science. I am currently doing a project as part of my on the job training but am stumped! Maybe you can help! ;)
software used:
Sql Server 2000
The facts:
1. there is a CARS database on server 1 (carId, carDescription)
2. there is an issues and problems tracking database on server 2 (problemId, problemDescription)
3. I am supposed to extend the functionality of the issues and problems tracking database to include the cars. i.e. the database on server 2 should look like: (problemID, problemDescription, carId)
The question:
Is there a way i can put a foreign key constraint on server2's carID so that i can only input carIDs which are in the CARS table on server1?
The only way i can think of is to make a new table in server1 and just drag a froeign key relationship in enterprise manager's designer, but my boss doesnt want to keep both information on the same db!
Any ideas?
Many thanks!