Modifying DTS In 2005

Sep 27, 2007

Can I modify 2000 DTS in 2005.I mean to say add some functionality to the existing DTS.

View 1 Replies


ADVERTISEMENT

Modifying A Column

Jan 10, 2002

I have an existing table called OrderHeader with a column called ID. The ID column is currently set as an INT. I want to run a script to modify that column to be an IDENTIY. I've tried the following:

ALTER TABLE OrderHeader ALTER COLUMN [ID] INT IDENTITY

but this does not work.

Any idea how to accomplish this?

Thanks!

Mike

View 1 Replies View Related

Modifying Data

Apr 28, 2004

I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string.


Alter Procedure usp_UpdateJobName

@JobNameOld nchar, @JobNameNew nchar

As

UPDATE
JobName
SET
JobName=@JobNameNew

FROM
tblRMADATA

WHERE
tblRMADATA.JobName=@JobNameOld

View 4 Replies View Related

Modifying The DTS Package.

Apr 28, 2004

I have a DTS package package1 which imports an excel sheet into my DataBase.ANd its saved and scheduled and running fine.Actually the excel sheet will be located daily @ c:ruceexceldata.But now we wanted to change the location of the file to c:ruce1ewexceldata

So how and where can I change my package.

Thanks.

View 2 Replies View Related

Modifying The DTS Package.

May 5, 2004

I have a scheduled DTS package which gets the data from a text file and imports into a sql table.The table will be droped and created whenever the DTS package runs.previusly the table has 6 rows and now i wanted to add a column row_id which is having an idenity seed.So how can I modify my package so that I can get done with my requirements.

View 1 Replies View Related

Help With Modifying Query

May 29, 2008

Hi,

I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.

What I want to add is the following data.

For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate

Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.


Any assistance is much appreciated!!

thanks once again
mike123


SELECT CONVERT(varchar(10),referDate,112) AS referDate,

SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0',
SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1',
SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2',
SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3',
SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3',
SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',

count(*) as totalRefers,
count(distinct(referUserID)) as totalUsers,

SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail',
SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk',
SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca',
SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk',
SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail',
SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol',
SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',

SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual',
SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'



FROM tblreferemails R

WHERE DateDiff(dd, referDate, GetDate()) < 5

GROUP BY CONVERT(varchar(10),referDate,112)

ORDER BY referDate DESC









CREATE TABLE [dbo].[tblUserDetails]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[EmailAddress] [varchar](50) NULL,
[Date] [datetime] NULL,
[Active] [tinyint] NULL
)




CREATE TABLE [dbo].[tblReferEmails](
[emailID] [int] IDENTITY(1,1) NOT NULL,
[referUserID] [int] NOT NULL,
[destinationName] [varchar](50) NULL,
[emailaddress] [varchar](50) NOT NULL,
[referDate] [datetime] NOT NULL,
[referalTypeID] [int] NULL,
[deleted] [int] NULL,
[emailSendCount] [int] NULL,
[lastDateSent] [smalldatetime] NULL
) ON [PRIMARY]

GO

View 3 Replies View Related

Help In Modifying Query

Nov 19, 2007

Greetings

I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on

I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType grouped by the CallDetailID and ordered by the date created
SCRIPTS

DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)

INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, Null

DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)

INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 2, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 3, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 2, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532

DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)

INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '11/5/2007 12:12:34 PM'
--
--select * from @Request
Query written to achieve the requirement
UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL

select * from @Request
OUTPUT :
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 2
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 1
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 1
66234 Mutuk SA059 72 185 1
2232362 Buck SA055 67 195 1

EXPECTED OUTPUT: (See the last column for unique nos).
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 2
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 2
66234 Mutuk SA059 72 185 2
2232362 Buck SA055 67 195 2

How can I modify the query to achieve the expected output?

Thanks:

View 9 Replies View Related

Modifying Report

Feb 13, 2008

Hi our reports server has some reports on it.

we need to modify some of them. how can i get those reports on to "SQL BUSSINESS INTELLIGECSE DEVELOPMENT STUDIO".

can we get rdl file from reporting server ?

View 3 Replies View Related

Needs Help With Modifying Stored Procedure

Oct 16, 2007

I need help with modifying this procedure to join JobTypeGallery, Remodel on JobTypeGallery.TypeID and Remodel.TypeID.
I would like for it the procedure to not allow deleting a record from JobTypeGallery if there are any records in Remodel Table that is associated with JobTypeGallery. Can someone please help me modify this stored procedure?
Create PROCEDURE [dbo].[spDeleteJobTypeGallery]  @typeid int  AS  delete from jobTypeGallery where typeID = @typeid   GO

View 4 Replies View Related

Modifying A Lot Of Stored Procedures

Dec 6, 2004

I probably know that I don't want to do this, but I have an odd case.

I have a database with a bunch of stored procedures and functions. 128 of them reference a different database than the one they're in. For testing puposes, I need to temporarily re-point these functions and procedures to a different database.

I suspect the answer will be a resounding 'No', but I was wondering if it was possible to somehow run a query against syscomments where I could update all of those objects with the temporary database name rather than edit each and every one of them. Conceptually, it would just be an UPDATE using REPLACE. Pretty comfortable with that, but I'm always very reluctant to mess with stuff like that.

I'll probably still just do it through QA, but I was wondering if it's possible to do something like that and thought it might be an interesting topic for discussion.

Thanks,
Pete

View 2 Replies View Related

Modifying Publication In SQL 7.0- Urgent Help .

Jul 31, 2002

Hello ,

I want to drop a table from a publication , so that i can copy some data from another server . After the copy , i want to add the article or the table back again to the publication without making any changes to the subscribers configuration .

Is it really possible on SQL 7.0 ? Right now it does not allow me to copy the data to the published table or even drop the article (table) and it says that the article is published for replication and cannot be dropped or modified .
The table is configured for transactional replication .

I will have to drop the entire publication and create all the subcribers again .

Any help in this regard please .
Sameer

View 2 Replies View Related

Modifying/deleting DTS Packages??

Feb 1, 2001

I've learned to create DTS packages, saving them in a SQL database. But how can one modify a saved DTS package? And how does one delete a saved DTS package?

thanks,

Boog

View 2 Replies View Related

Name Of Modifying User In Trigger ?

Jan 3, 2000

I have a trigger that emails me each time an order is modified by a manager. I can't recall how to find out which user is performing the modification. Is there a global variable such as @@username?

Thanks,

-Darin.

View 2 Replies View Related

Modifying SYSJOBSTEPS Table

Dec 19, 2002

I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts?

Thanks!

View 3 Replies View Related

Using Update For Modifying Text

Aug 27, 2003

Hello All,

I would like to update some values in my table that have this value: XXXX-XXXX. I would like to update the '-' and change it to a '_'. Does anyone know the best way to do this?

I tried this but I get a "Subquery returned more than 1 value error"

UPDATE PS_MTCH_RULES
SET MATCH_RULE_ID = (SELECT SUBSTRING (MATCH_RULE_ID,1,4)+'_'+SUBSTRING(MATCH_RULE_ID,6, 4) FROM PS_MTCH_RULES WHERE MATCH_RULES_ID <> 'ERS')

Any assistance would be appreciated.

Thank you.

E David Ramos

View 1 Replies View Related

Deleting/modifying Of Job Fails

May 2, 2004

When I try to delete/modify a job on my sqlserver the following error occurs:

error 14274: Cannot Add, update or delete a job that originated from a n MSX server.


What does that mean and how can I solve this ?

View 1 Replies View Related

Modifying Already Existing DTS Package

May 23, 2008

HI Gurus,

I am tring to add table to alraedy existing DTS pacakge to refresh data from other server.

I dragged items from tool list (SQL SERVER Entripice manager)

Ther ia m not able to connect those two icons(one source and other destination).

How can i proced to next step.



Thanks,
ServerTeam

View 3 Replies View Related

Modifying A Table From A View

Jun 18, 2008

I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table.

I also have an inner join view with the initials from both tables as the joining field.

I want the users to be able to modify the data table (MainTable) but also display the users name field.

here is the sql of the view:

SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName,
dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify,
dbo.MainTable.AdvocateInitials
FROM dbo.InitialsListing INNER JOIN
dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials

View 3 Replies View Related

Modifying A Field In Table?

Jan 21, 2014

I have a field called "assescode9" in a table called "Assessment" which has the length of "6" and I want to change this length to 7 or 8 in SQL. I tried right click on the field and modify but when I want to save it says you can not save the changes because it requires a Drop table and recreate or you do not have an option enabled to recreate the table, how do I do this?

I have never done dropping a table and recreate it?

View 3 Replies View Related

Modifying Existing Data

Nov 24, 2005

Hi Guys,

I am new to sql server 2000, and need a little help.

I have a table called CMRC_Products with various columns, there is one column called Product Images that has the name of every image in my catalog over 4000, I want to add to each row in that particular column .jpg without loosing what is already in there

I have tried:

UPDATE CMRC_Products
SET ProductImage = ProductImage&' .jpg'

But I get an error,

any help would be much appreciated, cheers

View 1 Replies View Related

Trouble Modifying A Table

Mar 13, 2007

Hi There,

I am trying to add a new field to a table using enterprise manager by right clicking on the table, choosing design table and adding the new field/column. But when I choose save changes, I receive an error message:

------------------------------------------------------------
Errors were encountered during the save process. Some of the database objects were not saved.
'Comments' Table
-Unable to modify table
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL SERVER] ...
... Cannot drop the table 'dbo.Comments' because it is being used for replication
-----------------------------------------------------------

If it helps I am trying to add a binary (bit) field and a text field of 50 characters. I think the replication it refers to is a merge replication (not sure though is there a way i can find out in EM?)

I have read some articles which refer to this problem and suggest using stored procedures but I really dont know how to do that, having never used them before. I need an idiots guide please!

Let me know if I need to provide more info. Thanks.

View 3 Replies View Related

Modifying User Security

Jul 20, 2005

I have an asp page that currently is creating a database and a userlogin for that database. After everything successfully (I thought)executed, I tried to change my connection properties for the serverand then login as this new user. It wouldn't allow me to, so I loggedback in as the administrator and looked at the properties for the newlogin. On the general tab, it had the user's default databasespecified as the new database that I had created in the asp page, butwhen I went to the database access tab, the database was not selected.So, I'm not sure how to set that in my script. I've done somesearching in BOL, but I can't figure it out. Also, if there's a wayto do this in a query, or stored procedure, will it also specify whattype of role the user has (public, db_owner, etc.)? Thanks.

View 5 Replies View Related

Modifying View Data

Mar 28, 2006

Hi,

I was asking how can I make updating and deleting for data through database views in Microsoft SQL Server 2005




Best Regards,

View 5 Replies View Related

Is That Possible To Prevent Modifying The Name Of An IDTSInputColumn90?

Dec 7, 2006

Hi,

I'm building a custom SSIS data flow component and I create myself input and output columns from a custom property of my pipeline component. That's why I don't want the user to modify the name of the input and output columns by using the advanced editor.

Is there a way either to make input/output column properties - at least the name - readonly or to override any PipelineComponent method to throw an exception like we do when we want to prevent the user from adding/removing input/output colulmns with methods like PipelineComponent.DeleteOutputColumn?

Thanks for your help,

David

View 2 Replies View Related

Modifying Generated XSD File

Jan 18, 2006

Hello,

I am working with an .xml file that I want to break up into various tables. A couple of fields in my xml file include html tags (<p> tags specifically). The generated xsd file thinks these are nested xml tags and creates a "P" table for the information contained. Is there a way I can modify the generated xsd within the designer? Or is the only way to fix this is to manually modify the xsd?

There is also an <id> tag within the xml. However, SSIS is not using that <id> as the primary key as it adds data to the various tables that I've specified. Instead it appears to be creating its own primary key - calling it "Id". Is there some way to specify that SSIS use the <id> tag contained within the xml instead of creating its own primary key?

Thank you for any help.

View 1 Replies View Related

Modifying With 3rd Party Application?

Oct 30, 2006

Hi,
I have recently started using SQL Server with a 3rd party application and have come across a problem and i have no idea how to fix it...

The database has a column of numbers that go up either by 10 or 100 depending on the letter in another column. eg:

100 M
110 P
120 P
200 M
300 M
400 M
500 M
510 P
520 P

The 3rd party application has just been updated and now can add new rows to this table. The only problem is that the added row needs a new number, and the rows below need to be recalculated.

Is there anyway this can be done? Any help or suggestions would be great!

Cheers,
BL

View 1 Replies View Related

Modifying Data Through Access

Jun 24, 2015

I have a project where I need the ability to update data in a SQL table (SQL 2008) from a tool like Access or Excel.  My SQL table has 3 records: Employee number, Employee Name, and a yes / no value (1 or 0).  I want to be able to display the table data (in Access or Excel), and be able to have the user modify the yes / no value, but not the Employee number or Employee name. How to handle this in SQL.  Should I connect Access directly to the SQL table? 

View 5 Replies View Related

Modifying SQL Statement In A Lookup

Apr 21, 2006



Hi there,

I'm trying to modify the SQL statement of a Lookup Transformation.

In my DataSet I have 2 columns A, B - I join against a table on columns A and B by

But I only want to match on column B - where column A doesn't match. (I'm looking for duplicate values of B)

So I've tried modifying the SQL statement to:

select * from
(select * from [dbo].[LookupTable]) as refTable
where [refTable].[ColumnB] = ? and [refTable].[ColumnA] <> ?

(The paremeter mapping takes care of itself).

Anyway, it seems sometimes I'm returning rows where it HAS matched on ColumnA.

I'm I on the right track here?



Cheers,

dave.

View 1 Replies View Related

Modifying SSISTutorial.dtsConfig

Apr 7, 2008



I am going thru the Integration Services Tutorial found in Books Online and I'm on Lesson 3. It has to do with creating a configuration file for the source file location. I followed the steps and now I need to open the .dtsConfig file and type in the folder location.

The exact direction says,

"


In Notepad or any other text editor, locate and open the SSISTutorial.dtsConfig configuration file that you created by using the Package Configuration Wizard in the previous task.


Change the value of the ConfiguredValue element to match the path of the New Sample Data folder that you created in the previous task. Do not surround the path in quotes.


Save the change, and then close the text editor.
"

When I open the file, I see this. What am I supposed to do here?

<?xml version="1.0" ?>

- <DTSConfiguration>


- <DTSConfigurationHeading>


<DTSConfigurationFileInfo GeneratedBy="FFTWayi" GeneratedFromPackageName="Lesson3" GeneratedFromPackageID="{74BC5CCC-5C51-4ABD-9263-AD3C000E9E1C}" GeneratedDate="4/7/2008 11:25:53 AM" />
</DTSConfigurationHeading>

- <Configuration ConfiguredType="Property" Path="Package.Variables[User::varFolderName].Properties[Value]" ValueType="String">


<ConfiguredValue />
</Configuration>
</DTSConfiguration>

View 13 Replies View Related

Modifying SSIS Jobs?

Jan 31, 2008

I have a brief question.

With a DTS scheduled job if I needed to make a modification, I just changed the DTS and saved it, and the job would automatically
Run the latest DTS (so long as the name was the same)

When I migrate a package to SSMS and schedule it , if I need to modify the package, do I need to modify it through BIDS (visual studio), then re-import it to SSMS and then reschedule it ?


Or is there a quicker way of modifying it ?

Thanks

View 2 Replies View Related

Modifying Model AttributeIDs...possible?

Jan 2, 2008

So the AttributeIDs used in a Report Model..they look and smell like a GUID..but they are rather finicky. For one, they need to start with a "G".

Anyway, say I want to create my own GUIDs for these models..seems I can do so in limited fashion by creating a GUID, replacing the first char with a G. Of course, change all references to that particular item n the model.. Redploy the model and it works! I can create new reports all day long.

But something strange happens behind the scenes..somewhere. So you can no longer use the wizard to update your model...even after changing the GUID references back to the original ones the model builder chose for you. I did a WinDiff on the before and after and saw absolutely no difference in the files..Something stored on the server maybe and not shows in Visual Studio.

Long story short...is there anyway I can construct or edit models with my own GUIDS or is there a super secret algorithm that I must adhere to? I dont want to simply use the .GenerateModel method...I really need to use my own GUIDs..but it appears I am missing some piece to make this work perfectly.

View 2 Replies View Related

Modifying Records In SQL Database Programmatically

Jan 23, 2008

VWD 2005 Express.  Visual Basic.  SQL Server 2005.
I know how to set up SqlDataSources and their insert, delete, update, and select commands.  I also have code for querying a Sql table and populating a dataset and scanning the dataset for values.  However, I do not know how to modify records in a Sql table programmatically.  Here is what I need to do:
I need to open a Sql table.
I need to process record bny record to check a particular field for a particular value.
If the field has the particular value, I need to change it and write the record back to the table with the modification.
The table is named "SystemUser" and the field is call "SystemUserTypeId." 
Can anyone provide me with sample code that would accomplish this?  Thanks for the help.

View 5 Replies View Related

Modifying Only Part Of A Sql Field Via SPROC?

Nov 12, 2003

I have about 200 records with a field (fileName) that have an ampersand(&) somewhere in them. I need to update the fileNames and remove all occurances of "&" and replace it with "-"; without modifying any other part of the file name.

Is there a SQL command I can use to update only part of a field?

View 1 Replies View Related







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