DTS From Mapped Drive Problem

Oct 16, 2007

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

View 5 Replies


ADVERTISEMENT

Can I Used A Shared Drive Rather Than A Mapped Drive With OpenRowSet?

Apr 4, 2008



Hi

I have been trying to use openrowset with a shared drive, and even though the share has "full control" permissions granted to "everyone" and the accout that SQL runs under has been granted explicit full control permissions I am unable to open the file which itself has no security on it.

Can I not use a \ path and only use mapped drives?

Thanks

below works...

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:5People.xls', [Sheet1$])

below doesn't work...

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=\cluster02FileManager5People.xls', [Sheet1$])

View 3 Replies View Related

DTS Not Running On A Mapped Drive

Nov 15, 2005

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

View 2 Replies View Related

Restore Problem W/ UNC Or Mapped Drive

Jan 22, 2001

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?

View 1 Replies View Related

Creating A Database On A Mapped Drive

Mar 23, 2004

Is it possible to create a database (MSSQL2k) on a mapped drive which is not a SAN?

Thanks,
Peter Schauss

View 3 Replies View Related

Sp_attach_db Failed On Mapped Remote Drive

Mar 1, 2001

Hi,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

View 1 Replies View Related

Backing Up Data To Network Drive Mapped

Aug 6, 2003

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.

View 4 Replies View Related

HELP. Back Up Problem When Backing Up To A Mapped Drive On Another Server

Mar 20, 2002

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

View 1 Replies View Related

HELP. Back Up Problem When Backing Up To A Mapped Drive On Another Server

Mar 20, 2002

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

View 1 Replies View Related

SQL 2012 :: Attaching MDF / LDF Hosted On Another Server / Mapped Drive

Mar 20, 2015

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.

View 2 Replies View Related

SQL Server/Windows 2003 Mapped Drive Limit.

May 22, 2006



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 ?



View 1 Replies View Related

SQL Server Admin 2014 :: Configure Agent Access To A Mapped Drive?

Oct 16, 2014

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?

View 1 Replies View Related

Database Connection: Mapped Network Drive (VC++ Express, MSSQL SMS Express, XP)

Sep 8, 2007


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

View 5 Replies View Related

How To Move A Log File From &#39;e&#39; Drive To &#39;f&#39; Drive....

Nov 9, 2000

I am trying to move a log file from one drive to another.

What I have done is add another file to my file group. So now my log has a file on the 'e' drive and one on the 'f' drive. I now want to remove the file on the 'e' drive. I have emptied the file on the 'e' drive. When doing the command:

ALTER DATABASE Uniprodruntime
REMOVE FILE m_rk_runtime_log

I get the following error message..

Server: Msg 5020, Level 16, State 1, Line 1
The primary data or log file cannot be removed from a database.

I have also gone into enterprise manager and tried to delete the file and it does nothing.

Has anyone run into this?

View 2 Replies View Related

SAN Drive Speed Vs Local Drive

Feb 12, 2007

How do you compare SAN drive vs local drive on a 32 bit server?

Is it good idea to move my DB files to a SAN instead of local?

Canada DBA

View 4 Replies View Related

TempDB Keeps Getting Filled And It Is In C Drive But It Should Be In T Drive

Nov 28, 2015

Server: SQL 2008 R2

 1: TempDB keeps getting filled.  Restart of the server has not fixed it. I shrink it, but the space gets filled again. Now I can't even shrink it anymore
2: TempDB is at the wrong location. Its current location is this :C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb

How do I change its location? 

C:Program FilesMicrosoft SQL ServerMSSQL10_50.SQLPROD6MSSQLDATA empdb
Correct location of TempDB should be: TempDB(T:) But its not there

View 13 Replies View Related

Moving A SQL Server 2000 Database From A Local Drive To Another Local Drive

Jan 31, 2008

Being a very novice SQL Server administrator, I need to ask the experts a question.

How do I go about moving a database from 1 drive to another? The source drive (C is local to the server, but the target drive (E is on a Storage Area Network (SAN), although it is still a local drive for the server. I want to move the database from C: to E:. Can someone provide me with instructions?

Thanks,
Rick

View 4 Replies View Related

Show Mapped Connections

May 31, 2001

Hi, Everyone,

Can any one tell me how to monitor the users connected (by mapping) to my windows 2000 machine? Thank.

View 1 Replies View Related

Mapped Drives On SQL Server

Aug 6, 2001

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

View 2 Replies View Related

Help With Mapped Or Linked Database

May 3, 2007

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.

View 1 Replies View Related

Certificate-mapped Logins

Oct 20, 2007

Hello every body
what is certificate-mapped logins? and how we can authenticate with these logins?
thanks

View 1 Replies View Related

Mapped Names In Security Manager

Jul 21, 1999

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.

View 1 Replies View Related

SQL 2012 :: Mapped Credential Is Not Used In Any Query?

Jul 9, 2014

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 .

View 1 Replies View Related

If User Is Mapped To Master - Can They Access All DBs

Mar 5, 2015

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 Related

SQL 2012 :: How To Backup Half Of DBs From A Server On C Drive And Other Half On D Drive

Jan 16, 2015

How to backup half of dbs from a server on C drive and the other half on D drive and vice versa, first half on D drive and other half On C drive using only one job and one stored procedure??

Using scheduling from job add 2 schedules to the job so first schedule backup first half to C and second half to D , the second schedule backup first half to D and second half to D.

View 1 Replies View Related

How To Restore Database From A Mapped Network Drived

Aug 24, 2000

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

View 6 Replies View Related

Access Limitation Of Memo Field Mapped

Jan 23, 2014

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?

View 2 Replies View Related

Do Mapped Columns Need To Be In Exact Order Everytime?

Jan 23, 2008

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

View 5 Replies View Related

DB Engine :: Selecting Credential That Are Mapped To Login?

Nov 23, 2015

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. 

View 6 Replies View Related

DB Engine :: What Is The Use Of Mapped To Certificate / Asymmetric Key / Credentials

Jul 23, 2015

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]

View 3 Replies View Related

How To Change What LOGIN Is Mapped To A Db USER (windows Not SQL)

Aug 17, 2007



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

View 1 Replies View Related

T-SQL (SS2K8) :: Retrieve Points From Table Based On The Fields When Mapped

Dec 1, 2014

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] .....

View 3 Replies View Related

Service User Accounts, Mapped Drives, Backups Question

Jun 26, 2007

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.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved