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 Complete Forum Thread with Replies
Related Forum Messages:
Why Is Reporting Services Designer Modifying My Sql Statement???
Hi! I hope that Microsoft can weigh in on this one: I have a sql statement as part of a report that I'm developing using VS2005 and SSRS. The WHERE clause (part of a much larger SELECT / UNION initially is: WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.FI_Customer IN (@Customers)) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) I'm adding "All" support so I'm modifying it to: WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.FI_Customer IN (@Customers) OR 'All' IN (@Customers)) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs) OR 'All' IN (@Customers)) On pressing the "!" button to test the query the designer is changing my sql to: WHERE (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.FI_Customer IN (@Customers)) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) OR (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.LOB_INSTECSpecificCodeNumber IN (@LOBs)) AND ('All' IN (@Customers)) OR (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND (fi.FI_Customer IN (@Customers)) AND ('All' IN (@Customers)) OR (s.cnt = 51) AND (fi.FI_Status = 0) AND (fe.FE_Status = 0) AND ('All' IN (@Customers)) AND ('All' IN (@Customers)) Note the removal of my parentheses, the repetition of logic and placement of the ORs. I've verified that both versions produce the same results but why is it being changed? Perhaps the designer thinks that its version is optimized but I (or others here) have to maintain it and my version (at least in my humble opinion) is more readable as to the intent. Please Microsoft share your thoughts on this "feature"??? Is there any way to prevent it? Thanks! Glenn L
View Replies !
Loop In Select Lookup Statement
Hello All, Below is a simple Select statement performing a Lookup into a SQL database and returning the columns (associated with the Row) in to Cells on an eForm. The issue I have is there are 42 rows (which go up and down) and do not feel like writing 42 select statements. select RiskDescriptor, RiskImpactLowDescriptor, RiskImpactMediumDescriptor, RiskImpactHighDescriptor from [Risk Descriptors] where [RiskDescriptor ID] in (1) order by [RiskDescriptor ID]; <<1@Cell104>> <<2@Cell105>> <<3@Cell106>> <<4@Cell107>> I would like to add a loop, adding 1 to the RiskDescriptor ID and 4 to the Cells. So on second pass in the loop: RiskDescriptor ID = 2 <<1@Cell108>> <<2@Cell109>> <<3@Cell110>> <<4@Cell111>> Third pass in the loop: RiskDescriptor ID = 3 <<1@Cell112>> <<2@Cell113>> <<3@Cell114>> <<4@Cell115>> and so on. The Until portion of the loop can be hardcode (42 in this example) but would rather use an EOL or Query the DB for the total number of RiskDescriptor ID. This way when the DB changes (ID's go up or down) the SQL Statement does not need to be notified. It is a JDBC call from within the eForm. I would appreciate any help on how to format a loop in a SQL Statement
View Replies !
Lookup Component Error: Statement(s) Could Not Be Prepared.
Hi all, I recieve an error when I use the Lookup component in SSIS that reads: Statement(s) could not be prepared. I'm using a SQL 2005 DB as the source which runs into a lookup table and is use to compare records with an SQL 2000 Database. I've created connection managers successfully to both these databases. When trying to use the results of an SQL Query for the lookup to the SQL 2000 database (which is a linked server) and I try to map the columns, the error pops up and exits out of the lookup properties Window The details to the error read: Program Location: at Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.Connections.SQLTaskConnectionOleDbClass.PrepareSQLStatement(String sql, Boolean bypassPrepare) at Microsoft.DataTransformationServices.Design.DtsConnectionCommonControl.CheckSqlQuery() I'm looking to use the results of this comparison to output in some form of a report. Ideas would be greatly appreciated!
View Replies !
Lookup Transform Issuing One Select Statement Per Input Row
I am using the lookup transform with the following settings: Reference table: Use results of an SQL query. The query retrieves the surrogate key and four business key columns from a dimension table which contains a few thousand rows. Columns: business keys in the incoming data are mapped to the business keys in the reference table, and the surrogate key is looked up from the reference table. Advanced: Enable memory restriction is OFF (and the other items on the Advanced tab are greyed out). I assumed that this means that the lookup transform would cache all of the rows in the SQL query, and then perform the lookups against this cache. This is the behaviour that I saw when I was running the package in my local environment in the BIDS debugger. However, a colleague was doing some profiling on our production database server, and noticed that the lookup transform is instead issuing a single SQL query for each row of input. Our production ETL server has many GBs of free RAM available (way more than enough to cache the contents of the lookup table in memory), and given that memory restriction is disabled, I don't understand why the lookup transform is behaving in this fashion. Does anyone have an explanation for this? I'm probably misunderstanding a key concept here. Thanks, Greg
View Replies !
How To Reference Dataflow Elements In A SQL Statement Embedded In A Lookup Transformation?
Hi guys, I need to use a SQL statement to lookup a value from a SQL server database table that relates to a column in my dataflow. Imagine a SQL database table called 'cars' with values of Year | Description 2005 Ferrari 355 2005 Ferrari 355 Spider 2006 Ferrari 355 F1 In my data flow I have Year and Model eg. Year | Model 2005 355 2006 355 In my SQL statement I want to select from the 'cars' table where the years match exactly but the 'description' is like the 'Model'. eg. %355% So essentially, how do I construct the 'like' clause in the select statement to reference the 'Model' column please? thanks for your help, Chris
View Replies !
Performance Expectations For Fuzzy Lookup Against 25mill Row Lookup Table
We did some "at scale" fuzzy lookup tests today and were rather disappointed with the performance. I'm wanting to know your experience so I can set my performance expectations appropriately. We were doing a fuzzy lookup against a lookup table with 25 million rows. Each row has 11 columns used in the fuzzy lookup, each between 10-100 chars. We set CopyReferenceTable=0 and MatchIndexOptions=GenerateAndPersistNewIndex and WarmCaches=true. It took about 60 minutes to build that index table, during which, dtexec got up to 4.5GB memory usage. (Is there a way to tell what % of the index table got cached in memory? Memory kept rising as each "Finished building X% of fuzzy index" progress event scrolled by all the way up to 100% progress when it peaked at 4.5GB.) The MaxMemoryUsage setting we left blank so it would use as much as possible on this 64-bit box with 16GB of memory (but only about 4GB was available for SSIS). After it got done building the index table, it started flowing data through the pipeline. We saw the first buffer of ~9,000 rows get passed from the source to the fuzzy lookup transform. Six hours later it had not finished doing the fuzzy lookup on that first buffer!!! Running profiler showed us it was firing off lots of singelton SQL queries doing lookups as expected. So it was making progress, just very, very slowly. We had set MinSimilarity=0.45 and Exhaustive=False. Those seemed to be reasonable settings for smaller datasets. Does that performance seem inline with expectations? Any thoughts to improve performance?
View Replies !
Fuzzy Lookup Error When Adding Additional Lookup Columns
I'm working with an existing package that uses the fuzzy lookup transform. The package is currently working; however, I need to add some columns to the lookup columns from the reference table that is being used. It seems that I am hitting a memory threshold of some sort, as when I add 3 or 4 columns, the package works, but when I add 5 columns, the fuzzy lookup transform fails pre-execute: Pre-Execute Taking a snapshot of the reference table Taking a snapshot of the reference table Building Fuzzy Match Index component "Fuzzy Lookup Existing Member" (8351) failed the pre-execute phase and returned error code 0x8007007A. These errors occur regardless of what columns I am attempting to add to the lookup list. I have tried setting the MaxMemoryUsage custom property of the transform to 0, and to explicit values that should be much more than enough to hold the fuzzy match index (the reference table is only about 3000 rows, and the entire table is stored in less than 2MB of disk space. Any ideas on what else could be causing this?
View Replies !
Is It Possible To Lookup Value Based On Two Tables Using Lookup Task
Hi All, Actually this is in regard to SCD Type 2 Dimension, Scenario is like that I am moving Fact table from some old source and I have dimensionA description value in fact which I want to replace with appropriate id from Dimension Table and that Dimension table is SCD Type 2 based on StartDate and EndDate and Fact Table doesn't contains direct date value rather there is timeId in Fact so to update the value in Fact table I have to Join Time Dimension table and other Dimension Table to replace fact Description with proper Id. Lets assume DimensionA Structure id Description StartDate EndDate Fact Table id measure1 measure2 TimeId Description Time Dimension TimeId Date Day Hour ...
View Replies !
How To Pick Nearby Text Of Lookup Terms With Help Of Term Extraction/Term Lookup
I am designing a ssis package,This is intends to mine text data(Data extracted from websites). Term lookup/Term extraction has been used as tools for mining. I have lookup terms defined with me for reference table,but the main problem lie in extracting the nearby text/number/charcters to these lookup terms during mining. For example : I found noun "Email" 200 (frequency score) times in my text,Now I want to extract nearby email address(this is also true for PhoneNumber,Address attributes also).so how can I achieve this with SSIS. If u have some idea/suggestion to carry out this challenge with or without Term Extraction/Term Lookup,plz do write here.
View Replies !
&&"Row Yielded No Match During Lookup&&" When Using 2 Columns In Lookup
I am doing a lookup that requires mapping 2 columns in the column mapping section. When I do this, I get the error "Row yielded no match during lookup" . The SQL that I captured in SQL profiler does find the record when I run it in Management Studio. I have already tried trimming everything to no avail. Why is this happening? I tried enabling memory restrictions but then I my package hangs and I get a SQLDUMPER_ERRORLOG.log file with the following logged: 07/24/07 13:35:48, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514) 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ProcessID = 5952 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ThreadId = 0 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Flags = 0x0 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDumpFlags = 0x0 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, SqlInfoPtr = 0x0100C5D0 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, DumpDir = <NULL> 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExceptionRecordPtr = 0x00000000 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ContextPtr = 0x00000000 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ExtraFile = <NULL> 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, InstanceName = <NULL> 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, ServiceName = <NULL> 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used 07/24/07 13:35:48, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used 07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used 07/24/07 13:35:49, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:Program FilesMicrosoft SQL Server90SharedErrorDumpsSQLDmpr0033.mdmp 07/24/07 13:35:49, ACTION, DtsDebugHost.exe, Watson Invoke: No Why am I getting this error with "Enable Memory Restriction"?
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 !
&&"Row Yielded No Match During Lookup&&" While There Is No Row Going Through The Lookup
Hi all, I don't understand what's happening here. I have a Conditional Split with 3 outputs. On the first output I have a lookup, when I execute the package I have 56 rows going through the Conditional Split, all rows are then going to the 2nd and 3rd output but the lookup on the first output generates an error "Row yielded no match during lookup". I don't understand why the lookup is generating an error while there is no row going through it. Any idea ? Sébastien.
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 !
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 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 !
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 !
|
TRACKER
