How To (the Steps) To Create DTS Package In 2005 As In The Case Of SQL 2000
May 30, 2007
Hi,
Warm Wishes for all !!
I used to create DTS Package in SQL Server 2000 and some times schedule accordingly , but in SQL 2005 although there is a provision to buid the same through integration service but couldnt able to succeed.
( the goal is to transfer the data from one table to another in different database)
Is there any document or any one can provide the steps involved in developing DTS Package in SQL 2005
Thanks
Aravind
View 1 Replies
ADVERTISEMENT
May 7, 2007
hi,
what are the steps to create a linked server, I have tried unsuccesfully:
The scenario is the following:
From server A I want to be able to execute a select statement to a table in server B like
select * from [server B].myRemoteDb.dbo.myremTable
I am administrator of server A but not of server B. I tried the
sp_addlinkedserver syntax but have not been succesful, so I would be grateful if somebody could just list the steps necessary to link Server B from Server A,
thank you
View 4 Replies
View Related
Jun 7, 2006
I am going nuts trying to get this to work. Maybe someone can help me. I am running sql server 2000 and am using a dts package. The package runs fine on sql server. When I access it using asp.net I get the following error:
----------------
The execution of the following DTS Package succeeded:
Package Name: MapsImport
Package Description: Import Excel to Maps table
Package ID: {C56FF415-CD35-461E-98E4-BB2430163413}
Package Version: {30415D05-B121-4C4B-991C-43496EA47090}
Package Execution Lineage: {EAF14EB3-F6C1-4D9B-A4B9-46E31AF4B608}
Executed On: NS23
Executed By: ASPNET
Execution Started: 6/7/2006 3:33:12 PM
Execution Completed: 6/7/2006 3:33:17 PM
Total Execution Time: 5.11 seconds
Package Steps execution information:
Step 'Copy Data from Sheet1$' to [dbname].[dbo].[Maps] Step' failed
Step Error Source: Microsoft JET Database Engine
Step Error Description:Failure creating file.
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003436
Step Execution Started: 6/7/2006 3:33:12 PM
Step Execution Completed: 6/7/2006 3:33:17 PM
Total Step Execution Time: 5.015 seconds
Progress count in Step: 0
----------
I searched through several forums and found that this seems to be a permissions problem. I set the IIS process to Low, I also added the <identity impersonate="true" /> tag to my web.config file. After adding the tag I get a new error message:
----------
No Steps have been defined for the transformation Package.
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.Runtime.InteropServices.COMException: No Steps have been defined for the transformation Package.
------------------
I am at a stand-still trying to get any further. Can anyone teel me what else I might be able to try to resolve this problem?
Here is my code for executing the package:
-----------------
Sub Page_Load(Src As Object, E As EventArgs)
Dim cnnstring as String="Data Source=NS32;Initial Catalog=dbname;Pooling=False;Min Pool Size=100;Max Pool Size=200;User ID=userid;Password=password"
Dim cnn as SqlConnection
Dim cmd as SqlCommand
Dim rs as SqlDataReader
Dim sql as String="Truncate Table Maps"
'Empty Equipment Contract Pricing table
cnn=New SqlConnection(cnnstring)
cnn.Open()
cmd=New SqlCommand(sql, cnn)
sql="DELETE FROM Maps WHERE Name IS NULL"
cmd=New SqlCommand(sql, cnn)
rs=cmd.ExecuteReader()
'check to see if table is empty
If rs.HasRows Then
Response.Write("<p><b>Failed to empty table.</b></p>")
Else
Response.Write("<p><b>Table successfully emptied.</b><br><br>Importing Data...<br>Please wait...</p>")
End If
'declare variables for DTS
Dim objDTSPackage, objDTSStep, strResult, blnSuccess
Const DTSSQLStgFlag_Default = 0
Const DTSStepExecResult_Failure = 1
'Use stored procedure on sql server to import data
objDTSPackage = Server.CreateObject("DTS.Package")
blnSuccess = True
'Load package from sql server
objDTSPackage.LoadFromSQLServer ("NS32", "user", "pass", DTSSQLStgFlag_Default, "pass", "", "", "MapsImport")
'Explanation: LoadFromSQLServer ("ServerName", "Username", "Password", "Flags", "PackagePassword", "PackageGUID", "PackageVersionGUID", "Package Name", "PersistsHost")
objDTSPackage.Execute
'walk through steps and check for errors
For Each objDTSStep in objDTSPackage.Steps
If objDTSStep.ExecutionResult = DTSStepExecResult_Failure Then
strResult = strResult & "Package " & objDTSStep.Name & " failed.<br><br>"
blnSuccess = False
Else
strResult = strResult & "Package " & objDTSStep.Name & " succeeded.<br><br>"
End If
Next
'display success or failure message
If blnSuccess Then
Response.Write ("<p><b>Package Succeeded.</b></p>")
Else
Response.Write ("<p><b>Package Failed.</b></p>")
End If
Response.Write ("<p>"& strResult &"</p>")
rs.Close
cnn.Close
'Response.Redirect("list.aspx")
End Sub
----------------------------
The code fails on the line where it says:
objDTSPackage.Execute
Thanks in advance!
-Mike
View 2 Replies
View Related
Mar 11, 2008
Hi all.
Any body give me one example,how to create drill Through reports in Reporting Services.
Regards.
View 5 Replies
View Related
Feb 25, 2008
Hi, Apologies in advance if you get confused by reading this.... I am trying to create an additional step in the Create Wizard User Control that is provided by ASP.net. The additional step that I want to add will come after a user will create their username, password, email etc. The information which I want to save in the extra step are details such as firstname, lastname, address, height, weight etc. (I am creating an online weight management system for dieticians).When I run through the application, the username, password etc save perfectly to the database, but nothing happens with the other "personal information". There are no errors thrown so I don't know where the problem is coming from.I have included the code below as I have it:The code behind the Register.aspx file is as follows: <asp:SqlDataSource ID="InsertExtraInfo" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString1 %>" InsertCommand="INSERT INTO [aspnet_UserInformation] ([first_name], [surname], [address1], [address2], [city], [country], [number], [height], [weight]) VALUES (@txtFirstName, @txtSurname, @txtAddress1, @txtAddress2, @txtCity, @txtCountry, @txtNumber, @txtHeight, @txtWeight)" ProviderName="<%$ ConnectionStrings:ASPNETDBConnectionString1.ProviderName %>"> <InsertParameters> <asp:ControlParameter Name="txtFirstName" Type="String" ControlID="txtFirstName" PropertyName="Text" /> <asp:ControlParameter Name="txtSurname" Type="String" ControlID="txtSurname" PropertyName="Text" /> <asp:ControlParameter Name="txtAddress1" Type="String" ControlID="txtAddress1" PropertyName="Text" /> <asp:ControlParameter Name="txtAddress2" Type="String" ControlID="txtAddress2" PropertyName="Text" /> <asp:ControlParameter Name="txtCity" Type="String" ControlID="txtCity" PropertyName="Text" /> <asp:ControlParameter Name="txtCountry" Type="String" ControlID="txtCountry" PropertyName="Text" /> <asp:ControlParameter Name="txtNumber" Type="String" ControlID="txtNumber" PropertyName="Text" /> <asp:ControlParameter Name="txtHeight" Type="String" ControlID="txtHeight" PropertyName="Text" /> <asp:ControlParameter Name="txtWeight" Type="String" ControlID="txtWeight" PropertyName="Text" /> </InsertParameters> </asp:SqlDataSource>
Then the code I have behind the Register.aspx.cs page is: protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e) { TextBox UserName = (TextBox)CreateUserWizard1.FindControl("UserName"); SqlDataSource DataSource = (SqlDataSource)CreateUserWizard1.FindControl("InsertExtraInfo"); MembershipUser User = Membership.GetUser(UserName.Text); //object UserGUID = User.ProviderUserKey; DataSource.InsertParameters.Add("UserId", UserGUID.ToString()); DataSource.Insert(); } I know there is a problem with the code on the aspx.cs page but I cant figure it out. I need the username / password information to relate to the personal details information. I know I have to create a foreign key in the asp_UserInformation table that will link to the username in the asp_Membership (where all the username / password info is stored) Any help will do, I'm almost in tears here!!!The Spud
View 1 Replies
View Related
Jul 14, 2006
Hello.
I am using the "SSIS Log Provider for SQL Server" to log events to a table for "OnError" and "OnPostExecute" events of a package. This works as expected and provides a nice clean output on the execution steps of the package.
I am curious as to why I do not see any detail for any/all tasks that fall under the "Data Flow" section of the package though. For instance, on my "Control Flow" tab, I added a "Data Flow" task that simply loads a few tables from a target to destination server. However, there is nothing shown in the logging output. Just that a Data Flow task was initiated. And when I'm configuring this logging under "SSIS-->Logging" in the checkbox area on the left, you cannot "drill into" data flow steps.
Is there a reason why there is no detailed logging for Data Flow tasks? Would getting to that require me to create a custom log provider?
Thanks for the help.
Greg
View 1 Replies
View Related
Jan 23, 2006
Can anyone give me a quick tutorial on how to create a DTS package in 2005 using Business intelligense studio.
I want to import a file from a ftp location and load it into the database as a table.
I know how to do it in 2000, but can use some help in 2005.
thanks
View 4 Replies
View Related
Aug 15, 2006
I am 100% new to SQL. I have used Access for ever. I need to know how to take a Access 2003 database and covert it over to SQL 2005. I have no idea how to complete this. I was told to do a DTS package but I do not see any options for this.
Any help would be great!
Thank you
View 5 Replies
View Related
Nov 16, 2007
I have a problem with a SQL Server (2000) Agent Job that has 3 steps that don't produce the desired outcome when invoked in total but will produce the desired outcome if the first step is broken up from the last two steps.
1) The first step uses third party software to generate PDFs to a folder.
2) The second step executes the following command to ftp the PDFs to a folder and move these PDFs to a backup folder:
EXEC master..xp_cmdshell 'm: & cd C:Program Filesfoldername1 AutoGeneral_Ftp & dir & FTP -n -s:C:Progra~1foldername1AutoGeneral_FTPupload_G eneral.ibc www.someorgname.org & move /Y
\networkdriveArchiveGeneral*.pdf \networkdriveArchiveackupsGeneral'
3) The third step determines if any files remain in the folder that contained the PDFs. If files are still in the folder then the FTP and move step (step 2) did not work and a stored procedure is invoked to send an email to the appropriate administrator
DECLARE @result int
EXEC @result = master..xp_cmdshell 'Dir "\networkdriveArchiveGeneral*.pdf" | find /i "file"'
IF @result = 0
exec ibc_sp_Email_Report_Failure 'FTP General Report'
When I invoke this job from Enterprise Manager and view the job history, it says that all steps executed successfully (which I understand may be the case even if the files were not FTPed or moved since the command can still return a code indicating success even though it didn't do what I expected.) In fact, the PDF is generated and written to \networkdriveArchiveGeneral
eportname.pdf. Steps 2 and 3 do not do what I expect. The PDFs still remain in the folder.
But when I start the job from step 2, the files are then moved. So, invoking the entire job does not move the files; invoking the job from step 2 moves the files.
Simple, you think, it is obvious that you didn't set up your job steps correctly in that step 1 does not go to the next step upon success. But I already checked that. Step 1 goes to the next step upon success.
Anybody ever come upon this problem? Any suggestions as to what else I can look at?
Thanks in advance,
Stedan
View 6 Replies
View Related
Apr 22, 2006
I watched the How do I video "Create a Full-Featured Customer Login Portal" several times, and have compared my code to the downloaded VB code. I seem to be missing something.
I am trying to replicate what he does in the video, where he uses the login name to query another database and display the matching data. In the video he starts by using a querystring (page.aspx?userID=name) before creating a login page.. My program works with this method. However, later he creates the login page, and the system seems to automatically link the user database with the northwind database and give results without the querystring. There is no indication of how this is done. When I examined the downloaded code, he actually uses a session variable in the Page_Load event on the orders page. There is no explanation of this either, just a quick "I'll paste this from the clipboard."
I have compared my code line by line, and it matches exactly. I can only guess that there is a missing step where a relationship is established between the ASPNETDB.mdf file and the northwind.mdf file. Can anyone explain this to me? Thank you.
My goal is to have a user log in, and the site automatically query the second database for that users statistics, and display them.
View 3 Replies
View Related
Mar 22, 2007
I just want the simple steps. I think this is linking through get external db? Is it different from SQL Server 2005?
Is this an Access question or a SQL server question.
thanks,
View 3 Replies
View Related
May 4, 2007
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
View 3 Replies
View Related
Mar 4, 2006
Hello,
Thanks for reviewing my question. I have installed VisualStudio 2005 on a computer and would like to know if I can install SQL 2005 Server and replace the SQL Express version? Will VS recognize it?
Many Thanks to the Experts
Peter
View 2 Replies
View Related
Apr 15, 2008
I've created SQL Server Agent jobs through management studio on SQL Server 2005. I can view and edit these jobs when I am logged into the server via remote desktop, but when trying to administer these jobs through Management Studio on a different machine, the steps do not appear in the job properties window.
Anybody else see this behavior? Know why it occurs? Is it a bug, or another wonderful "feature" of Manglement Studio?
View 10 Replies
View Related
Aug 23, 2007
I have a SQL 2000 database on one server that I'm trying to transfer to a new server. The new server has Small Business Server R2 Premium Edition, which includes SQL 2005.
I tried to transfer the database in the normal way that I'd create a copy database, which was: -1. Backup the source database2. Create a new database (empty) in the new server3. Restore to this database from the backup taken in #1.
Of course on previous occasions I wasn't changing version, but I expected that this would either fail immediately (message "incorrect version" or some such), or else work.
At first this appeared to transfer OK. It took about 20 minutes to create the backup (just over 5GB), and about the same time to restore it. The restore terminated with a message "Database has been restored successfully"). However, the database's icon in Management Studio's Object explorer showed a little up arrow, and alongside the database name "(restoring ....)". Clicking on the database icon did not expand it. 3 hours later it still was like this. Examining the server showed zero activity.
I expect that it's failed, and I should delete the new "(restoring...)" database and start again. How should I create this SQL 2005 database?
View 3 Replies
View Related
Jan 27, 2006
I have some simple sql that I use to create tables in a database in 2000. Have done it many times, many sql 2000 instances are fresh installs. Now on a 2005 fresh install it doesn't work<br>
USE MyDB
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE "care"."MyObjects"
(
"DId" integer NULL ,
"MyObjectID" integer NOT NULL ,
"idType" integer NULL ,
"name" varchar(64) NULL ,
"description" varchar(1024) NULL ,
CONSTRAINT "pk_MyObject" PRIMARY KEY NONCLUSTERED ("MyObjectID"),
)
go
I get "The specified schema name "care" either does not exist or you do
not have permission to use it."
I have used the db owner, sa both to try and create the tables using 90/Tools/Binn/OSQL.exe but no go. What am I missing ?
Thanks
View 1 Replies
View Related
Apr 6, 2007
Dear friends, can we do this? converting a package made in ssis 2005 to dts packages of 2000? or we can somehow make the same working on sql server 2000? any ideas?
thanks,
View 12 Replies
View Related
Mar 30, 2007
have upgraded to SQL Server 2005 from 2000 ( to 9.0.1399).
The database seems to be functioning in order, however - I am unable to crerate a publication in order to replciate the database.
I am using the New Publication Wizard and see the follwoing error:
TITLE: New Publication Wizard
------------------------------
New Publication Wizard encountered one or more errors while retrieving the list of articles on 'nld_tradeinvoicing'. The list of articles may not be complete.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantInitArticleList&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Invalid column name 'options'. (Microsoft SQL Server, Error: 207)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Could anyone help to pinpoint the casual factor behind this?
View 3 Replies
View Related
Mar 28, 2007
I am not sure where to ask this question. So I figured here may be ok
I have several DTS 2000 packages and I have SQL 2005 installed on my local machine. However the rest of the company is still using SQL 2000 (for now). So I do not want to convert them to ssis yet. One of the things we just did to the packages is make all flat files use \localhost and any servers point to (local). Now in ssis this works fine but using the DTS 2000 package designer it does not like either of these at all. The reason we wanted to do this is so when we switch out servers there is no changes to our DTS packages.
View 2 Replies
View Related
Aug 22, 2006
I have a package designed and working correctly for months now. My challenge now is to run this package on a box that does not have SQL 2005 SSIS install. It has SQL 2000 installed.
My question is - Can I compile the SSIS into EXE or any other kind of DTS package to run on a none-SQL 2005 box?
Thanks!
--Jon
View 1 Replies
View Related
May 13, 2008
I have successfully used migrate wizard to migrate DTS pacakge to TrainingDTS.dtsx.
What should I do next? when I run
C:>dtexec /file "C:TrainingDTS.dtsx"
Error: 2008-05-13 09:14:31.36
Code: 0xC0029172
Source: File Transfer Protocol Task undefined FTP Task
Description: The connection is empty. Verify that a valid FTP connection is p
rovided.
End Error
Error: 2008-05-13 09:14:31.36
Code: 0xC0024107
Source: File Transfer Protocol Task undefined
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
View 2 Replies
View Related
Apr 4, 2006
Hi,
I have to create a migration package ..means package should migrate the sql server 2000 tables to 2005 tables (Not dealing with data at this point of time and ignoring SPs,DTS packages).But there are lot of normalisation ans schema changes in 2005 compared to 2000.Like,
- One 2000 table devided into 3-4 tables in 2005
- Lot of changes in the filed names
- Handling integrity relationship between the newversion tables
Being new to SSIS ,iam in confusion like how to start and where to start.can you pls tell me the steps(Structured way) i have to fallow
-- I have around 8-9 tables in 2000 ,I have to migrate them into 18-19 tables (with some new fileds )
-- For each table i have to create one package(bcoz lot of transformations are there) or I can create one package for all of those ? but the finally i have to handover one package to the client
pls ask me if u need any further info to come up with the explanation..bcoz iam not sure whether i provided enough info or not
Thanks for ur help
Niru
View 2 Replies
View Related
Apr 4, 2008
We have several jobs that if any step fails, we need to continue with the next step. Due to this we have the both 'On Success Action' and 'On Failure Action' set to 'Go to the next step'. Our only issue is that when all the steps are done we still require the job itself to fail.
So my question is this, is there anyway the status of a job can be queried to determine if any of it's steps failed?
View 6 Replies
View Related
May 13, 2008
Hi All,
One of my user was able create DTS package using DTS Wizard, working from his workstation and saved this DTS in Legacy(in Data Transaformation services) on different SQL 2005 EE SP 2(9.0.3042) production server..
At same time he has no access to msdb on this SQL 2005 server(he also not sysadmin for this server).. How this could happen..??
View 2 Replies
View Related
Dec 20, 2007
hi friends,
Anyone give give me the steps we have to follow for debussing a stored procedure in sql server 2005...
View 2 Replies
View Related
Jan 26, 2007
Hi there,
I am doing a project on web usage mining of my universities server logs and im just wondering how i go about mining them in sql server 2005?
Do i mine them in one table? do i normalise the web log data? what algorithms will i use on them as im trying to get usage patterns from the users and also where most of the users come from.
Thanks in advance
Gary
View 3 Replies
View Related
Jan 14, 2008
-> Communicate with WebService from MS SQL 2005 CLR Procedure, Quick Steps
1- Create SQL project in V.S 2005.
2- Add new Trigger Class, right click on project select Add -> New Item.
3- Add Web Service reference.
4- Use impersonation technique to communicate with Service.
Example:
using (WindowsIdentity id = SqlContext.WindowsIdentity)
{
WindowsImpersonationContext context = id.Impersonate();
/////////////////////////////
proxy = new WebServiceProxy();
proxy.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
proxy.HelloWorld();
/////////////////////////////
context.Undo();
}
5- Set SQL Project Permission Level to External, change from the project properties Database Tab
"Permission Level Select the permission level from the drop-down list to specify a set of code
access permissions granted to the assembly when accessed by Microsoft SQL Server. The value can be
Safe, External, or Unsafe; these values correspond to the SQL Server permission sets SAFE,
EXTERNAL_ACCESS, and UNSAFE, respectively. Safe is the default.
This setting corresponds to the PERMISSION_SET argument for the SQL Server CREATE ASSEMBLY command.
" MSDN
6- "The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts
the database and the contents within it. By default, this setting is OFF, but can be set to ON by
using the ALTER DATABASE statement.
Note:
To set this option, you must be a member of the sysadmin fixed server role." MSDN
ALTER DATABASE [Database Name] SET TRUSTWORTHY ON
.
7- Build and Deploy assembly.
8- The web service reference generates XmlSerializers assembly, there are many ways to resolve
this issue, a quick way would be to add the XmlSerializers assembly to the database where the
main CLR assembly is deployed.
From "SQL Server Management Studio" select the target database, goto "Programmability" then ->
"Assemblies", there right click "New Assembly" and specify reference.
Hope this is helpful to others.
Regards
Rabeeh Abla
View 1 Replies
View Related
Jul 20, 2005
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
View 2 Replies
View Related
Oct 12, 2007
I need to know the steps on how to configure the SSRS Forms Authentication and its IIS Configuration. What specific Config Files should be modified?
i am using IIS 6.0, Windows Server 2003, SQL Server 2005.
Please help....
View 1 Replies
View Related
Jul 25, 2007
Hi
I have just take my system back to the factory defaults after some unsuccessful attempts to install 64 bit and 32 bit sql server on my notebook. I am looking to use either 32 or 64 bit (I have disks for both) and I would like to get a step by step guide that will help me through the process.
I tried an install of the 64 bit version, applied SP2, and it went smoothly but the report services browser didn't work. I tried to change some IIS settings but I didnt document what I changed so i was unable to undo the changes that i made when these didnt work.
I tried several combinations of things but then decided that a clean install on a clean system was the best option.
I need the SQL engine , reporting services, analysis services - and possibly integration services - i am not sure if i need notification services (dont know what it does anyway!)
I also have vs2005 - should i install that first or after sql server 2005 SP2?
Thanks
Doug
View 1 Replies
View Related
Oct 10, 2007
Currently I receive the following error when executing script within a DTS package in SQL 2005 (it seems to be working in SQL 2000):
Processed 27008 pages for database 'Marketing', file 'Marketing_Data' on file 5.
Processed 1 pages for database 'Marketing', file 'Marketing_Log' on file 5.
BACKUP DATABASE successfully processed 27009 pages in 15.043 seconds (14.708 MB/sec).
(5 row(s) affected)
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
The code I am using is:
-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB varchar(200)
SET @DB = 'Marketing'
-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'C:SQL2005 dbsMarketing.dat'
-- the new database name
DECLARE @TestDB2 varchar(200)
SET @TestDB2 = datename(month, dateadd(month, -1, getdate())) + convert(varchar(20), year(getdate())) + 'Inst1'
-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'C:SQL2005 dbs' + @TestDB2
DECLARE @RestoreLog varchar (2000)
SET @RestoreLog = 'C:SQL2005 dbs' + @TestDB2
-- ****************************************************************
-- no change below this line
-- ****************************************************************
DECLARE @query varchar(2000)
DECLARE @DataFile varchar(2000)
SET @DataFile = @RestoreFile + '.mdf'
DECLARE @LogFile varchar(2000)
SET @LogFile = @RestoreLog + '.ldf'
IF @DB IS NOT NULL
BEGIN
SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
EXEC (@query)
END
-- RESTORE FILELISTONLY FROM DISK = 'C: empackup.dat'
-- RESTORE HEADERONLY FROM DISK = 'C: empackup.dat'
-- RESTORE LABELONLY FROM DISK = 'C: empackup.dat'
-- RESTORE VERIFYONLY FROM DISK = 'C: empackup.dat'
IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB2)
BEGIN
SET @query = 'DROP DATABASE ' + @TestDB2
EXEC (@query)
END
RESTORE HEADERONLY FROM DISK = @BackupFile
DECLARE @File int
SET @File = @@ROWCOUNT
DECLARE @Data varchar(500)
DECLARE @Log varchar(500)
SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
CREATE TABLE #restoretemp
(
LogicalName varchar(500),
PhysicalName varchar(500),
type varchar(10),
FilegroupName varchar(200),
size int,
maxsize bigint
)
INSERT #restoretemp EXEC (@query)
SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
PRINT @Data
PRINT @Log
TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp
IF @File > 0
BEGIN
SET @query = 'RESTORE DATABASE ' + @TestDB2 + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +
' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
EXEC (@query)
END
View 1 Replies
View Related
Jun 6, 2001
Hi,
I have two job J1 and J2, each one has 10 steps. Now I want J2 to be the 11th step of J1 and I did not want manually type all the steps of J2 to be 11-20 step of J2. Is there an easy way through TSQL to do this ?
sp_add_jobstep only works when the step is OS command or a script but not a job
View 2 Replies
View Related
