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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 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 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 !
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 SSISTutorial.dtsConfig
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 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 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 !
Trouble Modifying A Table
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 Replies !
Modifying Existing Data
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 Replies !
Modifying Already Existing DTS Package
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 Replies !
SQL Injection - How To Prevent It?
I am building my first ASP.Net app from scratch and while working on the DAL I came across the problem of SQL Injection. I searched on the web and read different articles but I am still unsure about the answer. My question is should I add db.AddInParameter(dbCommand, "AvatarImageID", DbType.Int32, avatarImageID); Add in Parameters to my C# code to avoid SQL Injection. What is the best practice. I am unclear if the stored procedure already helps me avoid SQl Injection or if I need the add in parameters in the C# methods to make it work. I need some help. Thanks, Newbie My C# update method in the DAL (still working on the code) private static bool Update(AvatarImageInfo avatarImage) { //Invoke a SQL command and return true if the update was successful. db.ExecuteNonQuery("syl_AvatarImageUpdate", avatarImage.AvatarImageID, avatarImage.DateAdded, avatarImage.ImageName, avatarImage.ImagePath, avatarImage.IsApproved); return true; } I am using stored procedures to access the data in the database. My update stored proc set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[syl_AvatarImageUpdate] @AvatarImageID int, @DateAdded datetime, @ImageName nvarchar(64), @ImagePath nvarchar(64), @IsApproved bit AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY UPDATE [syl_AvatarImages] SET [DateAdded] = @DateAdded, [ImageName] = @ImageName, [ImagePath] = @ImagePath, [IsApproved] = @IsApproved WHERE [AvatarImageID] = @AvatarImageID RETURN END TRY BEGIN CATCH --Execute LogError SP EXECUTE [dbo].[syl_LogError]; --Being in a Catch Block indicates failure. --Force RETURN to -1 for consistency (other return values are generated, such as -6). RETURN -1 END CATCH END
View Replies !
Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas
View Replies !
Prevent Sql Job Failure.
Have a job that calls a DTS package, DTS is an Export & Import wizard to copy tables. Someone deleted a table from source and my job failed last night. Inputs appreaciated.
View Replies !
How To Prevent Db Deletion
Hi I want to try and protect myself from my own stupidity. I have a number of sql databases, but one is LIVE. It is easy to drop tables but I want to set something (e.g. a password) which will help prevent me from dropping tables on the live database. Any help/direction here would be appreciated.
View Replies !
Best Way To Prevent Deadlocks
I'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks. Exception type: SqlException Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. In general, what's the best way to resolve this ? Should I see if I can apply "WITH (NOLOCK)" to my data ? Any suggestions are greatly appreciated ! thanks again! mike123
View Replies !
Modifying Records In SQL Database Programmatically
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 Replies !
Modifying Only Part Of A Sql Field Via SPROC?
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 Replies !
Building / Modifying An SQL Database Remotely
Hi Folks,I should begin by saying I'm fairly new to SQL Server. I'm an applicationdeveloper who has built front ends atop Server, but have never done much inthe way of design / development.My question is in two parts;1). What is the best way to remotely "create" an SQL Server database? I'vebeen told that this can be done with SQL Statements, but thought that theremay be a way using XML.2). Can I use this same method to propogate updates to the remote databasewithout affecting the data?To give you a better idea of what I'm trying to do, I have a client thatlives quite a ways up north. I'm building an application that will beinstalled up there. I'd like a method to be able to make changes to thisdatabase on my end (the development copy), create an XML file of the"database structure" and have the other database or application import thisXML file, and add tables, fields and relationships if they don't alreadyexist.Any help would be appreciated.Thanks!Rick
View Replies !
Help On Comparing And Modifying A Resultset Or Table
HI everybody need help badly on the following have to recordsets with the following with the following SQL SELECT place, count(minus)as countminus order by place group by place THE RESULT WOULD BE PLACE countminus ABC 10 DEF 50 HIJ 5 KLM 2 MNO 0 MY OTHER QUERY IS SELECT plus, count(plus) as countplus GROUP BY plus ORDER BY plus THE RESULT WOULD BE: PLUS COUNTPLUS ABC 10 DEF 20 my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS... with this condition.. I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF... THE RESULT WOULD BE THIS..... PLACE countminus COUNTPLUS ABC 10 10 DEF 50 20 HIJ 5 0 KLM 2 0 MNO 0 0 I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS
View Replies !
Modifying Data From Extract Before Import
Hi all, Having some fun with an excel import. I have a excel sheet that has data that needs to be UpSert'ed into 2 different tables. In addition, I need to use a value in the spreadsheet to determine the PK from a reference data table, for one of the UpSert oprations. That is all working now. The thing I'm struggling with is something I am sure is quite simple - but I'm not seeing a solution from attempts, googling or BOL. 2 of the columns I receive have either nothing, or X in them. The columns they go into are defined as BIT, NOT NULL. So, in SQL it would be something relatively simple like: CASE When IsAvailable = 'X' then 1 When IsAvailable is null then 1 ELSE 0 end But I don't know how to do this to data that was in a spreadsheet, and now is a resultset being handed from a task to another task. to outline my current solution: ---- table 1 = this all works ------------- Excel Source --> MultiCast (For Table 1)-->Data conversion for table1-->:Sort for Table1--> Merge Join for table 1 (left Outer join) as 'left' leg Table1 Source --> Sort Table1 --> Merge Join for table 1 (left Outer join) as 'right' leg Merge Join for table 1 --> Conditional Lplit for table1 Conditional Split for table1 (table1 source PK is null) -->Insert Into Table1 Destination Conditional Split for table1 (table1 source PK is not null) -->Update Table1 OLE DB Command ---- table 2 = this needs to be able to convert X/NULL to BIT ------------- MultiCast (For Table 2)-->Copy Column for Table2 -->Data Conversion for Table 2-->table3 lookup to get FK-->Sort for Table2 merge-->Merge Join for table 2 (left outer join) as 'left' leg Table2 Source --> Sort Table2 --> Merge Join for table 2 (left outer join) as 'right' leg Merge Join for table 2 --> Conditional split for table 2 Conditional split for table 2(table2 source PK in null) -->insert into table 2 Conditional split for table 2(table2 source PK in not null) -->update table 2 ole db command ----------------------------------------- Now, if I correct the spreadsheet to have 0's and 1's in the two column, then the process above works. But I cannot (yet) force business to do that. If tried to use SQL Command for the excel source, but there is limited functionality on the command - I cannot do SQL coalese, isnull or case statements, which would allow me to resolve that data at source. I've tried to use derived columns to alter the columns. I think that the REPLACE (IsAvailable, VariableContainingX,VariableContaining1) might work to change X's to 1, but that doesn't resolve the NULL issue. I've tried to use a script component to handle the conversion - which REALLY feels like a bad way to do this - the .Net script is wrote was: -------------.net script code------------- Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports Microsoft.SqlServer.Dts Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If Not (Row.EndOfRowset) Then If (Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X")) Then Row.IsDotComVanEnabled = "1" End If If (Row.IsStoreCollectionEnabled_IsNull) Or (Row.IsStoreCollectionEnabled.Contains("X")) Then Row.IsStoreCollectionEnabled = "1" End If Row.NextRow() End If End Sub End Class ------------------------------------------ I also tried it like this: ----------------1st draft .Net script code-------- Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports Microsoft.SqlServer.Dts Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) If (Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X")) Then Row.IsDotComVanEnabled = "1" End If If (Row.IsStoreCollectionEnabled_IsNull) Or (Row.IsStoreCollectionEnabled.Contains("X")) Then Row.IsStoreCollectionEnabled = "1" End If End Sub End Class ------------------------------------------------- Those both threw the following error; -------------error information----------------- Script Component has encountered an exception in user code: PROJECT NAME: ScriptComponent_a333140d269b413bb1bddef390da7e16 Object reference not set to an instance of an object. at ScriptComponent_a333140d269b413bb1bddef390da7e16.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row) at ScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.Input0_ProcessInput(Input0Buffer Buffer) at xScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) ----------------------------------------------- Any ideas?
View Replies !
Modifying Existing Table Schemas?
By default, I constructed my database using the dbo Schema. I have since created new Schemas, and want to change some of the tables from dbo. Is there a way to change the existing schema of a table without disrupting the data that is inside the table? For example: Current: dbo.Products Desired Change: NewSchemaName.Products Thanks.
View Replies !
Modifying Existing Rendering Formats
Good morning (afternoon/evening) all. I am researching a way to render data in a plain text file, with no formatting. I plan on building out the result set to exactly what I am wanting and just need it to render itself without any formatting logic being taken into consideration. The end result is to generate a QIF file from a random data set. QIF is just a formatted text file. Am I making sense? Any help would be greatly appreciated.
View Replies !
PROBLEMS MODIFYING ROLES IN CUBES
When I try to modify the restrictions I have on roles within the cubes of Analysis Services, I get the error message - "Unable to update role in cube Sales". Error occurring during update of cube 'Sales' 'Error occurred while processing security for cube 'Sales' (Security Error "). I did get one old user to delete out of the manage cubes, however, now under my manage roles in the 'Sales' cube, none of my roles are even listed anymore. Does anyone have any suggestions?? Thanks!
View Replies !
|
TRACKER
