MS SQL Server :: Modifying SSISTutorial.dtsConfig

One Package Two Dtsconfig Files
hi, all;
i'd like to resolve this problem:
We are Developing packages in Dev.Server; if we are Deploying our packages to Prod.Server; we need to modify the connection string and related properties of the connection manager.
1. How to modify the connection manager while deploying to production server?
2. can we create 2 dtsconfig file for a single package; is there any mechanism for instructing sequel server to use a particular dtsconfig file for our package.

i modified the config file in order to change the connection string. but its affecting only the config file not the package. while running the package its reffering the old connection string only.

please provide me some help

Thanks

View Replies !

Dtsconfig Helps At Runtime

hi i want to know what are the purposes of dtsconfig file for the package.
once we deploy our package, if we want to modify the connection string for the new excel source or data source or destination, how to modify the connection details at client site, or in the server,

please help me to modify these connection details after or before deployment at server machine.

View Replies !

Editing .dtsconfig Files

Apologies if this has been posted before and feel free to point me to an existing thread if it has.

I currently have a dev, stage and live environment for a package

i have created a load.dtsconfig file that contains various bits such as connection strings, email to, from, etc which i created via the package configurations in the dev package. I then made a few changes via XML Notepad

I went to use the same config file on stage and decided to add a further property. I selected package configurations on stage and selected edit it the package configurations box, added the property and clicked finish. However it reset all the existing properties to the values that exist in the actual package so all the changes i made via XML notepad were overwritten.

Is there anyway you can get the Package configurations editor to retain the values from the .dtsconfig file rather than reading in the values from the package???

cheers

Scott

View Replies !

How Can I Do A Dtsconfig File For Connections?
Hi,

Up to now, we're using SSIS variables for storing values such as server name, database or several paths.

All these variables are required when our developers give to us their DTSX packages for to be updated in production scenarios.

And now, we'd like to use xml files but I don't know how to. We've already got xml files for Data Flow, Sql Task and so on..

Thanks indeed for your time,

View Replies !

Setting Expressions In DtsConfig
Hi, i'm new in this forum and in SSIS, so I apologize if mine is a stupid question and also for my english.

I have this problem: i have to develop an ETL package and i have to set the transformation between input and output columns using a configuration file, i.e. my client want to change the transformation simply modifying a config file( for example from UPPER to LOWER ).

I tried using dtsconfig and setting EvaluateAsExpression with value 1( true ) and Expression UPPER[Column 2] but i get an error saying that Column 2 is not a variable.

There is a way to do it?

I don't know if i explained the problem well.

Thanks in advance

View Replies !

Single Line DtsConfig Files
When I edit package configuration files inside of Visual Studio, it puts all the XML on a single line. Does anyone know how to automatically spread the information onto multiple lines, to make comparing file differentials easier.

Thank you,
Mike

View Replies !

Database Connection In Dtsconfig File
Hi frnds,

im running the SSIS package " data" in XXXdatabase.

now i have to run the same package in different database and in different server.

so i mentioned all the database config details in sql_server.dtsconfig file.

here i had attached my file :-

<?xml version="1.0"?><DTSConfiguration><DTSConfigurationHeading>
<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="Package.Connections[YYY ].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=YYY;User ID=XXX;Password=XXX;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;</ConfiguredValue>
</Configuration>
</DTSConfiguration>

after i attached the file in package configurations .

i was unable to see the db connection in connection managers.

i tried to attach the .dtsconfig file in (New OLEDB connection manager) but after clicking the test connection " Testconnection failed because of an error in intializing provider.The connectionString property is not intialized"

Please help me in this.

Thnkx in advance.

View Replies !

Package Deployment With Dtsconfig File
Hi,

I've created a deployment manifest to install my package and associated config file. It installs fine (as a file system package), however each time I launch the execute package wizard, I have to browse the file system to select the configuration I want to use. Is there any way of installing a package so that the config file is used by default? My work around is to use the package execution wizard to create figure out what the dtexec parameters are - then use dtexec to actually run it.

Also I tried installing in MSDB, the error message implied that SSIS requires a default instance of SQL 2005 in order to use MSDB as a package store - is this true?

Dave

View Replies !

.dtsConfig ConfiguredValue Contains Environment Variable?
is this possible?

<Configuration ConfiguredType="Property" Path="Package.Variables[User::varFolderName].Properties[Value]" ValueType="String">
<ConfiguredValue>%enviroment variable%</ConfiguredValue>
</Configuration>

it would be really useful. it looks like the .dtsconfig file needs to be maintained on each install independently. This makes maintenance a nightmare. it would be a lot nicer if the .dtsconfig files were more like templates rather than hard coded values to specific system resources.

View Replies !

DTSConfig File Not Found Error
I was requested to build and move and existing SSIS package from the developers local PC to the Dev server. Both are running SQL 2K5, I built the following command file so SQL Agent can run the import job.

REM *** Run for FileImport
set FileImportLoadData=C:SSISConfigDEVFileImportLoadData.dtsConfig
"C:Program FilesMicrosoft SQL Server90DTSBinndtexec" /f "C:SSISConfigFileImportFileImportLoadData.dtsx"

All files are in the proper place but when the jobs runs the following message shows up in the history;

Message
Executed as user: LIGHTHOUSE1SQLEXEC. ...system32>set FileImportLoadData=C:SSISConfigDEVFileImportLoadData.dtsConfig C:WINDOWSsystem32>"C:Program FilesMicrosoft SQL Server90DTSBinndtexec" /f "C:SSISConfigDEVFileImportLoadData.dtsx"Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:54:00 AM Warning: 2008-05-08 08:54:01.77 Code: 0x80012014 Source: FileImportLoadData Description: The configuration file "FileImportLoadData.dtsConfig" cannot be found. Check the directory and file name. End Warning Warning: 2008-05-08 08:54:01.77 Code: 0x80012059 Source: FileImportLoadData Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Progress: 2008-05-08 08:54:01.82 Source... Process Exit Code 0. The step succeeded.

My questions is how do I fix this and is this really and error?

Jay B.

View Replies !

Why Is The DtsConfig File Not Included In Build?
I use an XML configuration file for my SSIS package, and I have "Enable package configurations" checked. When I build the package, however, there is no dtsConfig file in the output (set to in) or the Deployment folder, and the config file is not included in the SSISDeploymentManifest file. There are no build errors or warnings.

This was working fine the last time I worked on this package (~9 months ago), but not now. I have since installed SP2, but can't confirm if the problem is related to a "fix" in SP2.

View Replies !

Incorrect Warning About Missing DtsConfig File
I built my package on my development machine with a config file at a given location. Let's say:-

C:DevelopmentdtsConfig.xml

Then I shipped my package to the Production machine and ran my package (in a .bat file) as follows:-

DTExec /FILE "C:ProductionPackage.dtsx" /CONFIGFILE "C:ProductiondtsConfig.xml"

But I get the following output:-

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:01:27 PM
Warning: 2007-11-30 22:01:29.52
   Code: 0x80012012
   Source: Package
   Description: The configuration file name "C:DevelopmentdtsConfig.xml" is not valid. Check the configuration file name.
End Warning
...

But then it goes on to read values from my Production config file.
How can I stop the annoying warning about my Development config file-path (which doesn't exist on the Production machine)

Thanks

View Replies !

Where Should The .dtsconfig File Be Deployed On Dev,Prod Server
I am migrating from local to Dev,QA and Prod.
I created a .dtsconfig file containing database connection strings to Dev database. What is the "location" on the Dev server where this .dtsconfig file nees to be deployed to??

Thanks for your help.

View Replies !

Changing The Content Of The DtsConfig File Using Script Task
Hello,

I have setup a package configuration and it ran fine; however, I would like to be able to use the script task to change content of the dtsConfig file. For example, I can get the newServerName using the SQL Task, then how can I assign the newServerName value to the DataSource in the package configuration file? Is this possible?

Thanks,
Andrew

View Replies !

One Package Two Configurations; App.LoadFromSqlServer(); Package.ImportConfigurationFile(Dev/Prod.dtsconfig);
Hi all,
I need one more help!
we can select the dtsconfig file with the Environment variable(indirect configuration);
but i need to select the configuration file at runtime; i've to load the package from the server and apply local configuration file to the package and run in a web server;

Requirements;
1. i have packages with its xml configurations for connection strings alone!
2. i deployed it on the server
3. Trying to execute the packages in a web page onclick event;

We have ASP.net WebPage; in On_Click Event, i have this code;

Application ap = new Application();
Package pk = ap.LoadFromSqlServer("\PROJECT", "itsssqldb", "pmo_package_user", "password", null);
pk.ImportConfigurationFile(@"Packagesdev_staging.dtsConfig");
pk.Execute();
here PROJECT is the Package name; i'm loading the package from the Common Sequel Server 2005; and applying the configuration file to that package and trying to execute this;
but its not using the config file what i mentioned; its returning failure!!

Can you please help me to resolve this issue!,

(The intention of doing this job is to select the configuration file( prod, dev) at runtime)

View Replies !

Modifying XML In SQL
Greetings

DECLARE @Xml AS XML
Given the above variable I get using a SELECT @Xml ... FOR XML, AUTO, is it possible to change the value of one of the elements in the XML file in SQL 2005?

Thanks

View Replies !

.dtsConfig &&"stuck&&" Values

I have an SSIS package with a small number of connections for various databases, both SQL2K5 and AS2K5.

When I build the package the .dtsConfig file is produced but the contents for some of those connections are not accurate relative to the package itself. There are some old values retained in the .dtsConfig file.

Worse, when I deploy the package to another machine the installation should allow me to change these values to ones appropriate for the new server. While I am able to change them they are not retained and the old values crop back up making the package fail during execution.

Any idea where I can smack this thing to unstick these values?

Would specifics help? I can post the file(s) if need be.

Thank you in advance,

Erik Klein

View Replies !

Modifying A Field
Hello,

I need to modify a field in my table to add increment, the field already has data(1,2,3....). The thing is I am working with sqlserver 6.5(heard it all).

How would I do that using the tool? Using SQL?

Thanks in advance.

View Replies !

Modifying Data
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 Replies !

Modifying The DTS Package.
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 Replies !

Modifying The DTS Package.
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 Replies !

Modifying Job Notification
Is there a way to modify the information sent on a SQL Server job notification? The information sent now is rather lengthy and exceeds my pager capacity. As a result, the job status (Succeeded, Failed), which is the most important information does not appear. The workaround I am using now is that I have a job step which utilizes a xp_sendmail to notify of job completion.

View Replies !

Modifying A Column
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 Replies !

Creating And Modifying A Job
I really hope someone can help me with this: I've been struggling with it all day to no avail. We have a db user (batch: is only used in overnight processes) who has some rights, but not all, and as a result of this, the user cannot run the execute command. And now it needs to.

So we had the bright idea of getting batch to create a job to do what it needed to. But, of course, batch is the owner of the job, so the same rights issue occurs. So I thought about creating job using sysadmin, then letting batch modify it as it needs to (changing the run date, etc), but it looks to me like a job created by sa is 'invisible' to batch. Any update attempt is met with "The specified @job_id does not exist". If I create the job with batch, no prob, but that's not going to work for me.

Please, does anyone have any ideas here?

View Replies !

Help In Modifying Query
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 Replies !

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

View Replies !

Modifying Report
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 Replies !

Help With Modifying Query
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 Replies !

Needs Help With Modifying Stored Procedure
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 Replies !

Modifying A Lot Of Stored Procedures
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 Replies !

Problem Modifying Out-of-the-box TFS Report
My office has adopted Team Foundation Server to track and manage thedevelopment and deployment process at our site. I have been asked tomodify one of the standard work item reports, "Related Work Items,"that ships with TFS. I've successfully done some of these, whichinvolved changing the MDX code for the datasets that generate defaultparameter values, but I am running into problems modifying the maindataset, dsRelatedWorkItems. I am using VS2005. When I look at thecomponents of this dataset on the tree diagram for all datasets, allof the correct fields are present, I can Preview the report and itworks. When looking at the Data page, I see the MDX query on top anda results pane on the bottom. The results pane correctly lists all ofthe fields I saw in the tree diagram across the top. But the queryitself does not look like the query that would have generated thesecolumns. Rather, the columns in the result pane correspond to therows in the query, not the columns. When I execute the query on thedata page, all of those columns disappear in the result pane, and theonly column that shows up is the single field that precedes the ONCOLUMNS statement in the MDX. At this point, if I attempt to previewthe report, it fails because it doesn't have the expected fields inthe dataset. If I try to switch to the design mode to see whetherthere is any alternate definition of the dataset lurking somewhere, itlooks like all dataset definitions are lost. The properties page forthe dataset shows the fields correctly if I haven't yet executed thequery. If I try to make any of the modifications that I need to make(which are simple -- I just need to remove some of the slicerconditions in the WHERE clause), I am again unable to preview thereport.It looks like the query that I see in the query pane is not what isreally being used for the dataset, and I'm stumped. I am not anexperienced MDX user, but I can do fairly basic modifications of thetype that are needed here. It seems like my problem isn't MDX syntax,but rather how (or where) Reporting Services is keeping the query thatunderlies this dataset.Anybody out there have a clue that might get me back on track withthis? Thanks.

View Replies !

Modifying User Security
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 Replies !

Deleting/modifying Of Job Fails
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 Replies !

Modifying Table Column
Can someone tell me the easiest/best way to make a modification to a table where you change a column constraint from not null to null? Would you rename the table, create the new one with the different column and then transfer the old data into the new definition?
thanks

View Replies !

Name Of Modifying User In Trigger ?
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 Replies !

Modifying Table Size
Hi guys,

I am trying to modify my table sizes. I need to extend the size of certain columns in several tables and keep the data presently in those tables also.
On my first attempt I successfully made the changes but had no data after dropping the original tables. Then I had to restore from a back up and I was
back to where I started.
Does anyone know a reliable way of doing this with out having to create another DB and transferring the data after making the size changes to my tables ?

Thanks,
Alan

View Replies !

Modifying SYSJOBSTEPS Table
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 Replies !

Using Update For Modifying Text
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 Replies !

Modifying System Table
Quick Question: I want to change an execution time for 345 jobs and do not want to do it one by one in EM. I have changed the active_start_time and next_run_time in the sysschedules table but it is not showing up within the EM job folder under the next run date as being modified. Does anyone know what I am missing?

TIA,
Daimon

View Replies !

Modifying/deleting DTS Packages??
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 Replies !

Modifying Publication In SQL 7.0- Urgent Help .
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 Replies !

Is That Possible To Prevent Modifying The Name Of An IDTSInputColumn90?
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 Replies !

Modifying SQL Statement In A Lookup

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 Replies !

Modifying Generated XSD File
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 Replies !

Modifying SSIS Jobs?
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 Replies !

Modifying Model AttributeIDs...possible?
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 Replies !

Modifying With 3rd Party Application?
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 Replies !

Modifying View Data
Hi,

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

Best Regards,

View Replies !

Modifying A Table From A View
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 Replies !

Modifying Blog Query For Use
I have been using this blog entry as a guide to finding the differences between an original table and one I modified: http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

Here is the relevant snippet from my SQL query.

SELECT MIN(Testing) AS TableName, Testing.Key, Testing.[Article Number], Testing.[Coder Initials], Testing.[Research Report Details], Testing.[%military participants], Testing.[%commercial participants], Testing.[%non-profit participants], Testing.[%educational participants], Testing.[Mean Participant Age],.....

FROM (SELECT 'Affectivity_Data' as TableName, Affectivity_Data.Key, Affectivity_Data.[Article Number], Affectivity_Data.[Coder Initials], Affectivity_Data.[Research Report Details], Affectivity_Data.[%military participants], Affectivity_Data.[%commercial participants], Affectivity_Data.[%non-profit participants], Affectivity_Data.[%educational participants], Affectivity_Data.[Mean Participant Age],......

UNION ALL SELECT 'Affectivity_Data_Original' as TableName, Affectivity_Data_Original.Key, Affectivity_Data_Original.[Article Number], Affectivity_Data_Original.[Coder Initials], Affectivity_Data_Original.[Research Report Details], Affectivity_Data_Original.[%military participants], Affectivity_Data_Original.[%commercial participants], Affectivity_Data_Original.[%non-profit participants], Affectivity_Data_Original.[%educational participants], Affectivity_Data_Original.[Mean Participant Age], Affectivity_Data_Original.[SD Participant Age],....)
AS tmp
GROUP BY Key
HAVING COUNT(*)=1
ORDER BY Key;

I am getting popup boxes for filling in field values in the new table, with no data in the table unless I enter it in the popups. I have tested the union statement; it works. A pointer on what I might be doing wrong in the rest of the statement would be much appreciated. Using Access 2007 if that is applicable.

Thanks ahead of time.

View Replies !