Many times here I have read about scripting DTS packages to copy them to another server. How is this done? Under "All tasks/Generate SQL Scripts" there is no option for DTS. Sould I be looking elsewhere??
View 2 RepliesI am currently running SQL7 sp3 and will be moving the database to SQL2k. I have restored the database without any problems, but have not moved the dts packages. What would be the best solution: script the packages, save the packages to the 2k db, or just build from scratch? Any feedback would be appreciated. Thanks! -J-
View 2 Replies View RelatedHi to all
Is there any option in sql server DTS or any other third party tool that can script data. By scripting data i mean that....
if a table "Employee" contains 50 rows, i want the tool to write 50 insert queries for me so that i can run in it anywhere.
Problem is i have to insert data in a remote server where i cannot use DTS. I just have a text area to write my query and press the run button..
Hope u understand my problem. In case of any explanation please reply. Waiting for your response. Thanx in advance.
by to all
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!
SQL 7
I am trying to run a script written for an asp page (pure vbscript) in a active script job. I am getting errors on:
Server.CreateObject("ADODB.Connection")
Yes, ADO is up to date and loaded. The error says
Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Object required: 'Server' Error on Line 14. The step failed.
Thoughts ..
Craig
I know it's a simple question, just can't figure it out:
How do I script a new column into an existing table in SQL? I am using MS SQL 7.0 and need to create several new columns in existing tables.
thanks all.
Does anyone know how to prevent or change the
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
that appear between each Create? I can't see any options for changing then or eliminating them when generating the SQL Script for my stored procedures.
Thanks,
Scott
I have to come up a script that will be going across 60 different servers that will be looking for document type that are not being used across all servers and then deleting them.
To start with:
Select Enabled from MHGROUP.CUSTOM5
where Enabled ='N'
then delete records
and where would I run this type of script or should I say can this be done?
Thanks
Anyone know how to write a script for sql 6.5 that identifies what column is a primary key on a table? (INFORMATION_SCHEMA doesn't exist) Thanks for the help in advance.
View 10 Replies View RelatedWhat I need to do - can it be done without scripting?
1.) I need to get a list of sp's from a table (EXEC SQL task)
2.) Next, loop through the list of sp's and execute (FOREACHLOOP)
3.) Then, write the output of each sp to a uniquely named file. (DATA FLOW task)
So far, I have had success with 1 and 2. But getting the DATA FLOW task to work has been imposssible thus far.
I tried everything I can think of. I even created a child package that gets called from the FOREACHLOOP, using a Parent Variable Configuration to pass down the name of the stored procedure variable to the child package. No dice.
I am ready to throw in the towel here.
So, my question is, can what I want to do even be DONE without scripting? I was trying to avoid the "manual" approach to this project. It seems like what I want to do "should" be so simple.
In sum, I am simply trying to read a list of sp's from a table, execute the sp's, then write the output of the sp's to a uniquely named file.
Can this be done without scripting? I am beginning to think NOT.
Sigh. This is driving me crazy.
Please, any advice!!
Thanks
Hi Gurus/MVPS:
Can you please share or show me the code for scripting out stored procedures in SQL Server 2005 using SQL Cmd?. I need to perform the following:
1). Script out the text of the stored procedure
2). Output to a sql file (text file) under some directory like C:sql
3). Import the file into the destination db and run it.
The above three need to be automated in a DTS Package. I tried to use the DMO but my source server is a SQL2k5 and so DTS desginer does not let me use the ActiveX with DMO against SQL 2k5. I need to run the first step against a SQL2k5 machine and the second and third on a SQL2k machine.
Please help!!!.
Thanks
Ankith
I just starting working for a new client this week. They have a custom install program written in VB.NET which installs the database to SQL Server 2000. Normally, when I want to create a SQL script to create a database, I would go into Enterprise Manager, right-click on a database, and select All Tasks | Generate SQL Script. However, that's not how this client works. Much to my horror, the CREATE DATABASE, CREATE TABLE, etc. statements are both hard-coded and hand-coded within the install program itself. When I asked them why they weren't using SQL Server's scripting capabilities to automatically generate the script, they said that SQL Server scripting is buggy and that it doesn't work properly. It was hard getting specific details but they said it would leave out indexes, for example. Now, I'm no SQL Server expert, but I've never had a problem using SQL Server's scripting capabilities. The database they use isn't that big - maybe 20-30 tables. So, before I make an issue out of it, I was wondering what everyone else's experience with SQL Server 2000's scripting features? If you've used SQL Servers scripting to recreate a database, does it work? Did you have issues?
View 6 Replies View RelatedCan you write a script that compares table changes. I have a testing database with a bunch of table changes that i need to move to production and instead of handtypeing the changes in I was wondering if there was a method of creating a script.
View 1 Replies View RelatedHi,
Just getting into scripting sql. I have a little script that adds and drops a default value for a column. But I cannot get it to run when the column already has a default value specified. If I didn't script the contraint and specify a name I have no name to specify when trying to drop it.
ALTER TABLE users ADD CONSTRAINT dev1 DEFAULT 1 FOR ns_email_flag
ALTER TABLE users DROP CONSTRAINT dev1
How do I remove any existing default values using script?
TIA,
Seoras.
I would like to move over all of my DTS packages and corresponding jobs onto another MS SQL server machine. The problem that I have is that I haven't been able to find any straight-forward info on how to script up these jobs, move them onto another machine and execute them. I have scanned through previous posts written by people with the same problem, but every response is simply "script up the jobs and move them onto another server." Maybe I'm missing something, but how "exactly" do I get a job to show up on this destination machine in EM, in the management/sql server agent/jobs folder. I did the jobs/all tasks/script job... but then what? I saved it on this destination machine, but am unclear as what to do next. The MS SQL Server Administrators companion book is of no help on this subject, and I do not understand how this simple task can be so confusing and is not documented. Any help would be greatly appreciated.
Thanks in advance!
TB
I know there must be a way to script GRANTS for all users for specific objects(tables, sp's, etc). Can't find much in BOL, etc. Does anyone know how?
Any comments would be appreciated.
How can I get a script of triggers (drop & create them) without scripting the tables with them ?
Thanks!
Is there any way to script DTS Packages? :confused:
View 1 Replies View RelatedAre you kidding me?
I can't create 1 object per file anymore AND I can't have the drop created along with the create
Are they forcing you to use ALTER?
What a piece of junk
Hi all,
Is there any way to script all the triggers in a database just the way we script tables and stored procedures?
currently I am using the system tables.
regards,
Harshal.
Does anyone here know anything about the DCS Scripting Language.
A contract vacancy has arisen using this language but I've never heard of it and there does seem to be anything on the internet about it. Can anyone help?
Hi,
Does anyone have scripts that generate the following:
1. All PK
2. All FK
3. All Indexes
Separate from the table DDL.
( EM can generate but with table DDL as well )
rockmoose
Dear friends,
I have alot of tables to create in sql server 2005. They are all ready to copy and paste. Is there a window where I can just script all in at once?
Thankyou very much
Chopsmum
Hi all.
I am looking to write a script to create several new tables. What i would like to know is how do i check if they already exist before i create them and if they already exist dont create them? If they dont exist, create them.
Cheers people.
Does anyone have any insight as to what is wrong with my SQL statement? All tables exist in this DB however, I get the messages at the bottom.
CREATE TABLE animal_colors(row_id INT NOT NULL IDENTITY(1,1) ,color VARCHAR(50))
INSERT INTO animal_colors (color)
SELECT
[description]
FROM
color
DECLARE @i INT
DECLARE @row_count INT
SET @i = 1
SET @row_count = (SELECT MAX(row_id) FROM animal_colors)
WHILE @i < @row_count
BEGIN
UPDATE animal
SET color =
(SELECT TOP 1 color
FROM color
WHERE color.[description] = animal_colors.color
and animal_colors.row_id = @i
)
FROM color
INNER JOIN animal ON animal.color = color.color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i
DELETE FROM color WHERE color NOT IN (SELECT TOP 1 color FROM color
WHERE color.[description] = animal_colors.color AND animal_colors.row_id = @i)
AND color.[description] = animal_colors.color AND animal_colors.row_id = @i
SET @i = @i + 1
END
DROP TABLE animal_colors
Result:
Server: Msg 107, Level 16, State 2, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 16
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 27
The column prefix 'animal_colors' does not match with a table name or alias name used in the query.
Does anyone know of a way to script tables without the default brackets that surround the table/columns? I've looked under the scripting options but don't believe I can find anything regarding this. Thanks
View 5 Replies View RelatedHi all,
I am new to SSIS...and wanted to know where can i get a lot of info on how to deal with ActiveX Scripttasks & Script tasks in SSIS...the place i am working has a lot of VB Scripting done in DTS Packages...having a hard time in understanding the Scripts, as i am more like back-end guy and wanted to learn a lot in SSIS, once i understand the scripts it will help me a great deal as to how to approach the tasks...Is there any website which teaches how to avoid Scripting in SSIS as i read somewhere that Scripting should be avoided as much as possible by making using of so many tasks in the SSIS tool.
I will look forward for someone to help me out and show me a way.
thanks
ravi
Nothing much that i can do..!!
Hi,
I have just taken over the DBA role for an application which at best you can describe their DR plans as woeful! So basically i am starting from scratch and trying to automate the database re-creation as much as possible. I have managed to write some bespoke scripts for backup devices, and user ids etc., but the main bulk of the work is in the re-creation of the 300 databases they have (boy i wish i had been here when they did this database design!!).
Anyway, onto my question. I know that i can go to each database in SQL Enterprise manager (SQL 2000 by the way), and right click and select generate scripts and it will do it for me, but i was wondering (and hoping) that there was a API interface that i could utilise which would allow me to pass in the database name and for it to generate the scripts automatically. Height of laziness i know, but it would allow me to generate the scripts for the databases on a regular basis in order to keep the DR recovery scripts up to date.
Thank you in advance for your help, even though i am guessing the answer will be "Tough, you have to do it by hand!"
Regards
Mark
I currently have an odd requirement esp as its my first ever SQL script.
At present ignoring all else there are two tables:
USE [Live2]
GO
/****** Object: Table [dbo].[Person] Script Date: 01/16/2008 10:08:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person](
[Pers_PersonId] [int] NOT NULL,
[Pers_CompanyId] [int] NULL,
[Pers_PrimaryAddressId] [int] NULL,
[Pers_PrimaryUserId] [int] NULL,
[Pers_Salutation] [nchar](10) NULL,
[Pers_FirstName] [nchar](30) NULL,
[Pers_LastName] [nchar](40) NULL,
[Pers_MiddleName] [nchar](30) NULL,
[Pers_Suffix] [nchar](20) NULL,
[Pers_Gender] [nchar](6) NULL,
[Pers_Title] [nchar](40) NULL,
[Pers_TitleCode] [nchar](40) NULL,
[Pers_Department] [nchar](30) NULL,
[Pers_Status] [nchar](40) NULL,
[Pers_Source] [nchar](40) NULL,
[Pers_Territory] [nchar](40) NULL,
[Pers_WebSite] [nchar](40) NULL,
[Pers_MailRestriction] [nchar](40) NULL,
[Pers_PhoneCountryCode] [nchar](5) NULL,
[Pers_PhoneAreaCode] [nchar](20) NULL,
[Pers_PhoneNumber] [nchar](30) NULL,
[Pers_EmailAddress] [nchar](255) NULL,
[Pers_FaxCountryCode] [nchar](5) NULL,
[Pers_FaxAreaCode] [nchar](20) NULL,
[Pers_FaxNumber] [nchar](30) NULL,
[Pers_CreatedBy] [int] NULL,
[Pers_CreatedDate] [datetime] NULL,
[Pers_UpdatedBy] [int] NULL,
[Pers_UpdatedDate] [datetime] NULL,
[Pers_TimeStamp] [datetime] NULL,
[Pers_Deleted] [tinyint] NULL,
[Pers_LibraryDir] [nvarchar](255) NULL,
[Pers_SegmentID] [int] NULL,
[Pers_ChannelID] [int] NULL,
[Pers_UploadDate] [datetime] NULL,
[pers_SecTerr] [int] NULL,
[Pers_WorkflowId] [int] NULL,
[pers_Dear] [nchar](40) NULL,
[pers_DMCCoID] [nchar](60) NULL,
[pers_Legacy_Contact] [nchar](119) NULL,
[pers_Accommodation] [nvarchar](40) NULL,
[pers_Group] [nchar](119) NULL,
[pers_Recruitment] [nvarchar](40) NULL,
[pers_Training] [nvarchar](40) NULL,
[pers_dob] [nchar](8) NULL,
[pers_origin] [nvarchar](40) NULL,
[pers_OCR] [numeric](24, 6) NULL,
[pers_level] [nvarchar](40) NULL,
[pers_booked] [datetime] NULL,
[pers_advisor] [int] NULL,
[pers_learnertype] [nvarchar](40) NULL,
[pers_qualification] [nvarchar](40) NULL,
[pers_SecondNVQ] [nvarchar](40) NULL,
[pers_learnerNo] [numeric](24, 6) NULL,
[pers_inductdate] [datetime] NULL,
[pers_initial_iag] [datetime] NULL,
[pers_MidPt_iag] [datetime] NULL,
[pers_exit_iag] [datetime] NULL,
[pers_lmt_advisor] [int] NULL,
[pers_Type_c] [nvarchar](40) NULL,
[pers_fund_type] [nvarchar](40) NULL,
[pers_inductionpack] [datetime] NULL
) ON [PRIMARY]
Also:
USE [Live2]
GO
/****** Object: Table [dbo].[Person_Link] Script Date: 01/16/2008 10:00:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Person_Link](
[PeLi_PersonLinkId] [int] NOT NULL,
[PeLi_PersonId] [int] NOT NULL,
[PeLi_CompanyID] [int] NULL,
[PeLi_Type] [nchar](40) NULL,
[PeLi_CreatedBy] [int] NULL,
[PeLi_CreatedDate] [datetime] NULL,
[PeLi_UpdatedBy] [int] NULL,
[PeLi_UpdatedDate] [datetime] NULL,
[PeLi_TimeStamp] [datetime] NULL,
[PeLi_Deleted] [tinyint] NULL
) ON [PRIMARY]
My current script is:
INSERT INTO Person_Link
(PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_Type, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy, PeLi_UpdatedDate,
PeLi_TimeStamp, PeLi_Deleted)
SELECT MAX(pl.PeLi_PersonLinkId) + 1 AS Expr1, pr.Pers_PersonId, '92929' AS Expr2, NULL AS Expr3, '1' AS Expr4, NULL AS Expr5, '1' AS Expr6, NULL
AS Expr7, NULL AS Expr8, NULL AS Expr9
FROM Person AS pr INNER JOIN
Person_Link AS pl ON pr.Pers_PersonId = pl.PeLi_PersonId
WHERE (pr.Pers_CompanyId IS NULL)
GROUP BY pr.Pers_PersonId
The goal is for every row in the person table which does not have a companyID insert a row to the PersonLink table with a company ID of 92929.
Also the colum PersonLinkID in the PersonLink table does not auto ascend.
Can someone kindly point the errors of my ways?
Are they kidding?
I can't create one file per procedure and the DROPS are only created separately?
Are they forsing you to use ALTER as well?
Or am I missing something obvious?
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Add yourself!
http://www.frappr.com/sqlteam
Has anyone used this shiit ??
its an absolutely useless pile of crap ??
I can't do simple things that were done easily with Active X ..
USELESS...