Function That Works In Sql Server Management Studio Does Not Work In Derived Column Transformation Editor
Hi
I'm a relative SQL Server newbee and have developed a function that converts mm/dd/yyyy to yyy/mm/dd for use as in a DT_DBDATE format for insert into a column with smalldatatime.
I receive the following erros when using the function in the Derived Column Transformation Editor. First, the function, then the error when using it as the expression Derived Column Transformation Editor.
Can anyone explain how I can do this transformation work in this context or suggest a way either do the transformation easier or avoid it altogerher?
Thanks for the look see...
******************************
ALTER FUNCTION [dbo].[convdate]
(
@indate nvarchar(10)
)
RETURNS nvarchar(10)
AS
BEGIN
-- Declare the return variable here
DECLARE @outdate nvarchar(10)
set @outdate =
substring(@indate,patindex('%[1,2][0-9][0-9][0-9]%',@indate),4)+'/'+
substring(@indate,patindex('%[-,1][0-9][/]%',@indate),2)+'/'+
substring(@indate,patindex('%[2,3][0,1,8,9][/]%',@indate),2)
RETURN @outdate
END
********************************
And the error...
expression "lipper.dbo.convdate(eomdate)" failed. The token "." at line number "1", character number "11" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
Error at Data Flow Task [Derived Column [111]]: Cannot parse the expression "lipper.dbo.convdate(eomdate)". The expression was not valid, or there is an out-of-memory error.
Error at Data Flow Task [Derived Column [111]]: The expression "lipper.dbo.convdate(eomdate)" on "input column "eomdate" (165)" is not valid.
Error at Data Flow Task [Derived Column [111]]: Failed to set property "Expression" on "input column "eomdate" (165)".
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetInputColumnProperty(Int32 lInputID, Int32 lInputColumnID, String PropertyName, Object vValue)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnComponentUI.SaveColumns(ColumnInfo[] colNames, String[] inputColumnNames, String[] expressions, String[] dataTypes, String[] lengths, String[] precisions, String[] scales, String[] codePages)
at Microsoft.DataTransformationServices.Design.DtsDerivedColumnFrameForm.SaveAll()
View Complete Forum Thread with Replies
Related Forum Messages:
Is Possible To Call A VB.NET Function Within Derived Transformation Editor
Hi, In a nut shell I want to be able to instruction some Data Analysts on how to modify SSIS packages using the simpliest solutions possible. This is because there are many different data sources and some of these data sources have a huge number of fields, and yes you guessed it these data sources are subject to change on a regular basis. A very common task they will need to do is to modify an SSIS package to do a to transform of a source date string format of "YYYYMMDD" into a date data type field within a table. Similar threads have advised the use of the Data Flow Transformations->Derived Column for this sort of thing. So within the Expression Text box I have inserted the following SSIS compatible SQL to convert the above string into a british format date data type; - Code Snippet (SUBSTRING(DOB_SRC,8,2) + "/" + SUBSTRING(DOB_SRC,5,2) + "/" + SUBSTRING(DOB_SRC,1,4)) But really what I want to be able to do is to instruct the Data Analysts to do is something like; - ConvertTextToDate(DOB_SRC) Where I previously defined that behaviour of ConvertTextToDate as a public VB.NET function. Can someone please help. I'm pretty certain I'm not the only one with this type of requirement. Thanks in advance, Kieran.
View Replies !
Derived Column Transformation Editor
Greetings, I am attempting to create a flat file delimited by |. I am using (ISNULL(LIN1_OPT_ADDR) ? "" : LIN1_OPT_ADDR + "| ") to replace the blank address column with the pipe delimiter. So that a row that would consist of: Customer Number,Name,Address Line1,City,State 12345,ACE HARDWARE INC. ,801 Rockefeller St.,New York, New York 56789,BUILDING SUPPLY INC., ,Wichita, Kansas Should end up as: 12345|ACE HARDWARE INC.|801 Rockefeller St.|NEW YORK|NEW YORK 56789|BUILDING SUPPLY INC.||Wichita|Kansas When I run the data flow to create the flat file the file contains the following: 12345|ACE HARDWARE INC.|801 Rockefeller St.|NEW YORK|NEW YORK 56789|BUILDING SUPPLY INC.| | |Wichita|Kansas Can anyone tell me what I am doing wrong? Thanks.
View Replies !
Derived Column Transformation Editor Question
Help... I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this: if[message] isNull then "NA" else [message] where [message] is the input column. Thanks!
View Replies !
Derived Column Transformation Editor Question
Help... I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this: if[message] isNull then "NA" else [message] where [message] is the input column. Thanks!
View Replies !
Round Function In Derived Column Transformation
Can somebody please help me with the implementation of a logic in round off to the left of a decimal point. Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help Value x Excel SSIS 627900 16200 16221 187000 4800 4831 277760 7200 7175 763000 19700 19711 1387500 35800 35844 1465200 37900 37851 2725000 70400 70396 292800 7600 7564 317200 8200 8194 The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
View Replies !
Round Function In Derived Column Transformation
Can somebody please help me with the implementation of a logic in round off to the left of a decimal point. Something like this in excel "=ROUND(x/12*31%,-2)" is to be implemented in SSIS. The Round function in the derived column is not permitting -2 for the length parameter. Please help Value x Excel SSIS 627900 16200 16221 187000 4800 4831 277760 7200 7175 763000 19700 19711 1387500 35800 35844 1465200 37900 37851 2725000 70400 70396 292800 7600 7564 317200 8200 8194 The table lists the values for X in the formula and the respective result calculated by Excel. I would want SSIS to give the same results like excel is giving. Please help me to make it work.
View Replies !
Derived Column NEEDS An Editor Window!
The "window" if you call it that to enter in complex derived column logic is painful (at best). At a minimum, the entry line should (just like in the cases where SQL is entered into a ole db command, sql exec etc) open a editor window to allow you to work in something other than a single long line. In addition, allowing for cr/lf in the text would then allow the text to be formated/indented in meaninful ways. The issue now, once you nest a few statements together, it get VERY confusing and not easy to read since the whole thing is required to be on a single line. (This same issue exists with line in which you need to enter the text in a conditional split). THANKS!
View Replies !
Derived Column Editor In SSIS
I need to check if the date is Null then use today's date and if not do something else. If RowModifiedOn IS NULL Then GETUTCDATE() ELSE DATEADD("Hh",@[User::TimeZone],RowModifiedOn) End If What do I do wrong here? Can I do something like it? RowModifiedOn == NULL ? GETUTCDATE() : DATEADD("Hh",@[User::TimeZone],RowModifiedOn) Thanks.
View Replies !
SQL Query That Works In SQL Server Management Studio, But Doesn't On .NET 2.0 Page
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasource and a gridview, no data is displayed.anyone know what my problem is?
View Replies !
SELECT Statement Works In SQL Server Management Studio But Not In SSRS
Hi, I'm attempting to extract some yearly average figures from our DB. I've written a SELECT statement in SQL Server MS which returns exactly what I need: SELECT YEAR, CAllSource, AVG(CallTotal) AS [Average calls per day] FROM (SELECT COUNT(CallID) AS CallTotal, DATEPART(YEAR, Recvddate) AS Year, CASE WHEN CallSource IN ('Auto Ticket', 'Email') THEN 'Email' WHEN CallSource IN ('Phone') THEN 'Phone' ELSE 'Other' END AS CallSource FROM Calllog where DATEPART(MONTH, Recvddate) = 3 AND DATEPART(dw, RecvdDate) NOT IN ('7', '1') GROUP BY RecvdDATE, callsource) as sub GROUP BY YEAR, CallSource ORDER BY YEAR, CallSource The problem is that when I attempt to use this in SSRS I get the following error: "sub.Year is not a recognised DATEPART Option". Now as you can see, "sub.Year" is not even mentioned in the expression. However I noticed that when running the query, SSRS automatically adds "sub." before the YEAR in the section highlighted in yellow above. a) Why is it doing this, and b) does anyone know of a workaround/fix? Thanks Matt
View Replies !
Cast/Convert Mmddyy In String To New DB_DATETIMESTAMP Column In Derived Column Transformation
Hi, I have dates in "mmddyy" format coming from the sources and they are older dates of mid 80s like 082580 for instance. When I cast it this way (DT_DBTIMESTAMP) Source_Date , It says ok but throws a runtime error. When I hardcode a date in same format, (DT_DBTIMESTAMP) "082580" , It becomes red (an indication of syntax error) . Please note that we use double quotes in expressions in Derived Column Transformation; So an anticipation that using double quotes over single ones would be the syntax problem would be wrong. Any help in this will sincerely be appreciated. Thanks
View Replies !
Help Me With Derived Column Transformation
I have this expression (Registered_Units == Limited_Units) ? 0 : (Painted_Units / (Registered_Units - Limited_Units)) * 100) It gives me 0.00, although those 3 columns have values. if I cast it like this (Registered_Units == Limited_Units) ? 0 : ((((DT_DECIMAL,2)Painted_Units) / ((DT_DECIMAL,2) (Registered_Units - Limited_Units))) * 100) It gives me correct answer in whole number but .00 after decimal. Any clue how to fix it ?
View Replies !
Derived Column Transformation
Hello. I am using Derived Column transformation for calculating the age of individual and then adding the column to my final destination. In SQL, the DOB is varchar(50) and the output column I am creating should be Integer. Here is the expression I am using for calculating the age: (DATEDIFF("DAY",(DT_DBTIMESTAMP)TRIM(DOB),(DT_DBTIMESTAMP)TRIM([Service Date])) / 365.25) In SQL, I have no problems getting the age of a person, but I am having difficulties using Derived Column Transformation. I get the following error when executing my package: Error: 0xC0049067 at Data Flow Task, Derived Column [2086]: An error occurred while evaluating the function.Error: 0xC0209029 at Data Flow Task, Derived Column [2086]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (2086)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (2877)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Any assistnace would be greatly appreciated.
View Replies !
Help In Derived Column Transformation
Hi All I m designing SSIS having Data Flow as Flat File > Derived Column Transformation > Destination Table but while transfering record from text file to table i need some logic here my text file is "ID"|"Name" 1012|"10AA" 1013|"10BB" logic is 10 should be replace by variable VID = 98 I defined this veriable as int My ID column in database is int and Name column is varchar(50) I try here expression like ID != 98 ? @[User::VID] : ID it work fine but for varchar column I am not able to do I got error when i write expression SUBSTRING(Name,1,2) != "98" ? [DT_STR, 50, 1252] "(@[User::VID])" + rest of value of column : [Name] my final output in table should be like ID..Name 98..98AA 98..98BB Please help me out T.I.A
View Replies !
Derived Column Transformation Expression
Hi. I am using the following expression to check if the first charcter of a string is not the letter "E" and if it is, strip it off by selecting the remainder of the string: SUBSTRING([Derv.comno],1,1) == "E" ? SUBSTRING([Derv.comno],2,10) : [Derv.comno] This is ok in 99.9% of cases, but ideally I would like to be able to check, and alter the string if the first charcter is anything but numeric I had something like this in mind: SUBSTRING([Derv.comno],1,1) != ("1","2","3") ? SUBSTRING([Derv.comno],2,10) : [Derv.comno] but the syntax is incorrect. Could you tell me if what I am attempting is actually possible, and if so, point me in the right direction regarding the syntax! Thanks
View Replies !
Error On Derived Column Transformation
Hi all€”Given a date field called [Reading Date] and a time field called [Reading Time], I am attempting to use the following transformation on the field to test for nulls and combine the data into a single field called [Reading Date/Time]: (DT_WSTR)([Reading Date]== "") || ISNULL([Reading Date]) || (DT_WSTR)([Reading Time]== "") || ISNULL([Reading Time])? (DT_DBDATE)GETDATE() : (DT_DATE)(SUBSTRING((DT_WSTR,8)[Reading Date],5,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],7,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],1,4) + " " + SUBSTRING((DT_WSTR,8)[Reading Date],1,8)) I get the following error: Error at Data Flow Task [Derived Column1[177]]: Attempt to parse the expression "(DT_WSTR)([Reading Date]== "") || ISNULL([Reading Date]) || (DT_WSTR)([Reading Time]== "") || ISNULL([Reading Time])? (DT_DBDATE)GETDATE() : (DT_DATE)(SUBSTRING((DT_WSTR,8)[Reading Date],5,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],7,2) + "/" + SUBSTRING((DT_WSTR,8)[Reading Date],1,4) + " " + SUBSTRING((DT_WSTR,8)[Reading Date],1,8))" failed. The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed or might be missing part of a required element such as a parenthesis. Here is a sample [Reading Date]: 05/07/08 Here is a sample [Reading Time]: 19:45:48 I need to be able to handle exceptions if one or both contain NULL or no data at all. Any suggestions on how to fix this? Thanks, Jon
View Replies !
Change Value Using A Derived Column Transformation
I am trying to change the value from "T" to "A" in trans_type column based on the value in Transfer_of_Provider column.. I like to change "T" to "A" if the value of Transfer_of_provider column is "Y". "Transfer_of_provider" coulmn has NULL value.. I've used the below logic to do that... but becuase of NULL value in "Transfer_of_provider" column .. it keep giving me a truncation error.. TRans_type = (ISNULL(Transfer_Of_Provider) ? "N" : Transfer_Of_Provider) == "Y" ? "'T" : "A" How can I make this work?
View Replies !
SSIS - Derived Column Transformation
Hello All, Can someone help me out in providing the STEPS to solve this problem. My scneario is, I've a table which has got 2 fields and 5 default row values have been filled in. Now, using the above, duirng package runtime, it need to dynamically create additional field and has to store values like for.e.g (0001 America). I'm getting the following error while executing the ssis package. 1. [DTS.Pipeline] Warning: Component "Derived Column" (1170) has been removed from the Data Flow task because its output is not used and its inputs have no side effects. If the component is required, then the HasSideEffects property on at least one of its inputs should be set to true, or its output should be connected to something. 2. [DTS.Pipeline] Warning: Source "OLE DB Source Output" (87) will not be read because none of its data ever becomes visible outside the Data Flow Task. Please suggest with your valuable solution at the earliest. Thanks Vaiydeyanathan.V.S
View Replies !
Derived Column Transformation - Error
Can i call the FUNCTION within another FUNCTION Like SUBSTRING(CHECK_NO,2,LEN(CHECK_NO) - 1) ??? I am reading the Check_No "1234321" from the flat file. The file holds all the value within double quote and values are sepearated by comma. Objective: I am trying to elimiate the double quote using "Dervied Column'. Strange: The above FUNCTION is working fine while construct the SQL Query. Pls help me. Thank you.
View Replies !
Passing Variable To Derived Column Transformation
I am trying to pass a variable set by a Row count transformation to a derived column transformation. There are actually three separate Row Count transformations storing to different variables. In the derived column trans. the expression i am using is @[User::ClientCount] and the variable type is Int32 and I am passing it to an I4 database column. After the derived column trans. all rows just come out with 0s in the row count columns. Does anyone know why this would be? Please let me know if you need more information.
View Replies !
Using User Variables In Derived Column Transformation
My derived column transformation with a user variable expression displays the user variable's default and not its assignned value. Background: I built a script component that seems to effectively assign a new value to a user variable. I use local variables within the component, make the assignment in the PostExecute subroutine, and check by writing the user variable to a messagebox there. In the derived column transformation I create a new column and insert the user variable in its expression field, add a data viewer, and send it to a data destination. The data viewer shows the user variable default and not the assignned value. I replaced the user variable with a system variable in the expression field and that works fine. Have folks successfully used this scenario before? Any ideas? Thanks.
View Replies !
Importing Dates Using Derived Column Transformation
Hi All I've got a flatfile which contains a column SALEDATE with this data 20070802 '' 20070804 '' 20070805 20070901 I've got a table with a column SALEDATE datetime. I use a derived column with this expression SALEDATE <add as new column> (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2)) database date [DT_DBDATE] This expression fails whenever it comes across a blank field. How can evaluate my expression to insert a null if it comes across a blank field? Thank You
View Replies !
...Works In Management Studio, Not In .NET Anymore
I have an ASP.NET/ADO.NET page i wrote. It was based off of the development database during most development, and it switched over seamlessly to the production database. Except for yesterday, when i went back to edit the page. It simply stopped returning results on my queries. To see if it was caused by bad values being passed to the sqldatasource, i tried it in the "Configure Data Source..." window, and it returns zero rows. When passing all of the exact same parameters to the same procedure in the same DB engine, even using the same login information, it works. Just not in my ADO.NET app anymore... Can anyone tell me what the hell could be going on here, please? I already rebuilt the page from an older version this morning, being very minimal on changes, and the last thing i changed before it stopped working was the connection string... *BUT* I have triple-checked that against the canon string we have always used in every working copy. I'm losing a lot of hair over this . Any help would be greatly appreciated. Thanks in advance stoland
View Replies !
MSSQL Management Studio View Editor Destroys Where-Clauses With Date-Functions
Hello, i've written the following query: SELECT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT, dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1, dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT, dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT FROM dbo.KALENDER INNER JOIN dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID WHERE (SELECT MONTH(s.datum_zeit)) = ((SELECT MONTH(GETDATE()))-2)and (SELECT year(s.datum_zeit)) = (SELECT year(GETDATE())) order by s.stunde_id when copying that query to the view editor and executing it, it trys to fix it somehow to: SELECT TOP (100) PERCENT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT, dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1, dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT, dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT FROM dbo.KALENDER INNER JOIN dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID WHERE ((SELECT MONTH(s.datum_zeit) AS Expr1 FROM ) = (SELECT MONTH(GETDATE()) AS Expr1) - 2) AND ((SELECT YEAR(s.datum_zeit) AS Expr1 FROM ) = (SELECT YEAR(GETDATE()) AS Expr1)) ORDER BY s.STUNDE_ID ... but this causes syntax-errors. I don't understand why this query works fine in the query editor but then gets automatically "destroyed" by the view editor. Do i have to use more statements to get the working query to run inside a view? Thanks alot for reading.
View Replies !
Major Problem With REPLACE-expression In The Derived Column Transformation
I'm importing a csv-file delimited with semicolons. Firstly I LTRIM the columns "in place" and the data imports fine. All the numbers in right columns in the target table. Then I add another Derived Colum Transformation to replace decimal character comma (,) to a dot (.) in order to convert the string/varchar value to numeric. But here I run into trouble. Running the task ends in success but the result in the target table (same as above) is not. All the commas are now dots as expected but what is worse is that SSIS have added values in cells that should not be there. I get values in cells that shoud be empty! Shortly: Only LTRIM([Column1]) as expression and "Derived Column" as Replace 'Column1' works OK. But adding REPLACE-expression (i.e REPLACE(LTRIM([Column1]) , "," , ".") to this breaks things up I'm aware that I could do this with SQL but this is not the point... Any ideas? BR Jompe
View Replies !
Sql Statement Case In Where Works Management Studio Not In C#
I have a bit of an odd problem in an sql statement where it works in management studio but not in c# when setting up a table adapter the sql is: DECLARE @week char(2) SET @Week = 7 SELECT student_id, acad_period, register_id, register_group, week_no, absence_code, attendance_type FROM dbo.sttdstud WHERE (student_id LIKE '%') AND (register_id LIKE '%') AND (register_group LIKE '%') AND week_no = Case @Week when null Then '#' Else @Week End the idea is if they don't enter a number it would bring up all records. This works in mangaement studio but c# brings up the error sql server doesn't support udt on excecution of the sql. This is sql server 2000. Any ideas why this doesn't work and how to fix it.
View Replies !
Ho Do I Get The Details From A DTS Transform Data Task Into An SSIS Derived Column Transformation?
i have too many DTS packages to migrate to SSIS, and while examining a DTS package in BIDS (converted with the migration utility) i tried to edit the resulting migrated package, which opened the DTS interface with the two connection icons joined by the big fat arrow with a gear on it...not exactly what i had in mind, iow, it looks like SSIS on the outside, but its still DTS on the inside. So I stripped out a series of components from a more complex package hoping that simplifying it would reveal the contents of old DTS Transformations tab at least partially set up in a Derived Column transformation. Can i get there from here, or must i recreate every stinking definition in a derived column manually from the ground up? thanks very much for your help
View Replies !
Can I Use SQL Server Management Studio Express To Work With SQL Server Everywhere?
Can I use SQL Server Management Studio Express to work with this...SQL Server Everywhere? I bought VS2005 Pro and installed everything, including the Developers edition of SQL Server 2005. I would love to play with this and try converting old Access desktop applications. Unfortunately I am unsure how to create and modify a new database for SQL Server Everywhere using SSMSE. I have several tables that have a thousand records or so used for €˜Look-Up€™ tables and really need a way to import those tables. Working within VS2005 and manually creating tables just isn€™t cutting the mustard here€¦ What can I do?
View Replies !
DB Update Fails In Program, Works In Management Studio?
I have an SQL statement that, when run through SQL Server management studio works fine. However, when I run it on my ASP page, it doesn’t update the data! I have tried both as a stored procedure and as a simple commandText update statement. All I do is simly update the value of a column based on another column – nothing particularly complex: update customer set dateChanged = System.DateTime.Now.ToString("dd-MMM-yyyy"), CURRSTAT = 'Active',custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END,cust_Changing_To = NULLFROM customers_v WHERE CURRSTAT = 'Changing' As you can see, nothing that complex. The line that is causing the problem is the case: custType = case WHEN cust_Changing_To IS NOT NULL THEN cust_Changing_To ELSE custType END all it does is if another nullable integer column is not null, sets it to the value of that column, else it retains its existing value. Like I say, this works in Management studio, but I cannot get it to execute programatticaly from an asp page. No exceptions are being thrown, it just doesn’t update the data. Any ideas? Thanks
View Replies !
Procs Fails In MS Access, Works In Management Studio
I have a stored procedure which is run through MS Access (yuck). It does not appear to fail, but it does not populate certain tables, and is also rather complex. I did not write it, and am trying my best to fix it. However, when I run the same procedure with the same parameters from within Management Studio (database state is the same, I restore from backup before trying out each execution) it populates the tables correctly. I have profiled both executions (from MS and from Access) and it is running with the same NTUsername, and thus the same permissions. The process used to work and then some changes were made to the DB which seem to have broken it from Access. As far as I know, the Access code has not changed, although it may have done so. The proc call from Access uses the same parameters, and does not throw an error, although it does not appear to close the connection to the DB (I'm looking into this). Access uses an ODBC connection to connect to SQL Server. Any ideas?
View Replies !
Connection Issue With Enterprise Manager But Works With Management Studio
Hi,I have an interesting scenario. I have a SQL Server 2000 Standard Edition instance running on Computer A. I'm trying to access it through computers B and C.Computer B has Sql Server 2005 Express Edition installed and I was able to use its Management Studio and connect to instance on Computer A.Computer C has SQL Server 2000 Standard Edition installed. When I try to connect to the instance on Computer A, I get connection failed message.I checked some settings. Both TCP/IP and Named pipes are enabled on the instance in A. The TCP/IP port is set to 1433 for both client and server.Please give me some ideas as to how I can solve this problem. -Thanks
View Replies !
Query That Works In Management Studio, Fails In Reporting Services
I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services: declare @starttime as datetime declare @endtime as datetime declare @timezone as integer declare @date as datetime set @timezone = 1 set @date = '5/1/2007' set @starttime = dateadd(hh, @timezone, @date) set @endtime = dateadd(d, 1, @starttime) select @Starttime, @endtime from site Error Message: TITLE: Microsoft Report Designer ------------------------------ An error occurred while executing the query. The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. ------------------------------ ADDITIONAL INFORMATION: The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database). Any help would be greatly appreciated!
View Replies !
Can't Get Diagrams To Work In SQL Management Studio
I can't get diagrams to work in SQL Management Studio. When I try to open that folder in the object browser, I get: "Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects." That's great, but what exactly constitutes a "valid login" and who or what is it supposed to be?
View Replies !
How To Use Coalesce Function In Derived Column Component?
Hi all, You know the coalesce function is not offered in the derived column component. how can I use the coalesce function within the component? And how can I use a user defined function within derived column component? Would you be kind enough to give an example? Thanks! Any reply or comment is highly appriciated. Robert
View Replies !
Sample Problem : Function Replace() In Derived Column
Hello All. Hopefully someone out there will have an idea as this isdriving me nuts. Ihave some sample problem. I want to use function replace() on Derived Column. For example. when strDate = 2007/03/22 I used ==> replace(strDate, "/", "") ==> 20060322 But If strTest = "123.10" ====>> 123.10 How can i do to replace ( " )double qoute ? by function replace() what is a statement for replace (") in Derived Column ? please tell me for this event. any suggesstion appreciated Thank you very much. Chonnathan
View Replies !
Derived Column Returning No Data On GetDate() Function
Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View Replies !
Extending Derived Column Transform With Custom Function Library
When data is imported from our legacy system, the same functions need to be applied to several columns on different tables. I want to build a kind of "Function Library", so that the functions I define can be re-used for columns in several packages. The "Derived Column" transform seems ideal, if only I could add my list of user-defined functions to it. Basically I want to inherit from it, and add my own list of functions for the users to select. Is this possible ? What other approaches could I take to building about 30 re-usable functions?
View Replies !
Query Works SQL Server Studio Not Visual Studio
I have SSRS in Visual Studio. I created a query that works fine in SQL Server Management Studio, but when pasted into Visual Studio I get the error "An expression of non-boolean type specified in a context where a condition is expected, near '('. Here is the query. Can anyone help on why this isn't working? Thanks. SELECT CASE WHEN MONTH(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = MONTH(GETDATE()) AND YEAR(dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate) = YEAR(GETDATE()) THEN dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount ELSE 0 END AS CurrentMonth, CASE WHEN SubString(dbo.MAS_CCS_GL_Account.Account,1,3) = '400' THEN 'ALEDO' ELSE ' ' END AS Location, dbo.MAS_CCS_ARN_InvHistoryHeader.SOTransDate, dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber, dbo.MAS_CCS_AR1_CustomerMaster.CustomerName, dbo.MAS_CCS_ARO_InvHistoryDetail.DetailSeqNumber, dbo.MAS_CCS_ARO_InvHistoryDetail.LineType, dbo.MAS_CCS_GL_Account.Account, dbo.MAS_CCS_ARO_InvHistoryDetail.SOExtChargeAmount FROM dbo.MAS_CCS_AR1_CustomerMaster, dbo.MAS_CCS_ARN_InvHistoryHeader, dbo.MAS_CCS_ARO_InvHistoryDetail, dbo.MAS_CCS_GL_Account WHERE dbo.MAS_CCS_AR1_CustomerMaster.CustomerNumber = dbo.MAS_CCS_ARN_InvHistoryHeader.CustomerNumber AND dbo.MAS_CCS_ARN_InvHistoryHeader.InvoiceNumber = dbo.MAS_CCS_ARO_InvHistoryDetail.InvoiceNumber AND dbo.MAS_CCS_ARO_InvHistoryDetail.SOGLSalesAcct = dbo.MAS_CCS_GL_Account.AccountKey
View Replies !
Telnet Connection Works, Sql Cmd Connection Works, SQL Server Managment Studio 2005 Does Not
I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running telnet sql5.hostinguk.net 1433 and sqlcmd -S sql5.hostinguk.net -U username -P password See below: Active Connections Proto Local Address Foreign Address State TCP 0.0.0.0:25 0.0.0.0:0 LISTENING TCP 0.0.0.0:80 0.0.0.0:0 LISTENING TCP 0.0.0.0:135 0.0.0.0:0 LISTENING TCP 0.0.0.0:443 0.0.0.0:0 LISTENING TCP 0.0.0.0:445 0.0.0.0:0 LISTENING TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING UDP 0.0.0.0:445 *:* UDP 0.0.0.0:500 *:* UDP 0.0.0.0:1025 *:* UDP 0.0.0.0:1030 *:* UDP 0.0.0.0:3456 *:* UDP 0.0.0.0:4500 *:* UDP 81.105.102.47:123 *:* UDP 81.105.102.47:1900 *:* UDP 81.105.102.47:5353 *:* UDP 127.0.0.1:123 *:* UDP 127.0.0.1:1086 *:* UDP 127.0.0.1:1900 *:* Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning. The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below: TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider) I would expect this as the DNS has not been advised to encrypt the conection. This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not. This is on a XP machine trying to connect to the remote webhosting company via the internet. I can ping the server I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled I do not have any aliases set up No I do not force encryption I wonder if you have any further suggestions to this problem?
View Replies !
Skip Row - Script Transformation Editor
Howdy! I am reading in a deliminated file. In the Script Transformation Editor, if the UPC does not past the checksum test, I want to throw the row out right then. I am not sure how to do that...but it is probably really simple.] Thanks, Linda Here is my script: ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components 'Option Strict Off Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Private Function DoubleTest(ByVal Value As String) As Boolean Dim d As Double If Not Double.TryParse(Value, d) Then 'Windows.Forms.MessageBox.Show(Value + " is not numeric") Return False End If If Double.IsNaN(d) Then 'Windows.Forms.MessageBox.Show(Value + " is NaN") Return False End If 'Windows.Forms.MessageBox.Show(Value + " = " + d.ToString()) Return True End Function Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim d As Double Dim CheckDigit As Integer = 0 Dim CheckOdd As Integer Dim CheckEven As Integer ' Copy each source column to the destination column Row.WholesalerCode = Trim(Row.WholesalerCode) If Row.UPCNumber.Length = 12 Then ' 12 Digit Checksum CheckOdd = Convert.ToInt16(Row.UPCNumber.Substring(0, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(2, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(4, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(6, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(8, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(10, 1), 10) * 3 CheckEven = Convert.ToInt16(Row.UPCNumber.Substring(1, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(3, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(5, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(7, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(9, 1), 10) If ((CheckOdd + CheckEven) + 1.0) / 10.0 = Round(((CheckOdd + CheckEven) + 1.0) / 10.0, 0) Then CheckDigit = 1 If ((CheckOdd + CheckEven) + 2.0) / 10.0 = Round(((CheckOdd + CheckEven) + 2.0) / 10.0, 0) Then CheckDigit = 2 If ((CheckOdd + CheckEven) + 3.0) / 10.0 = Round(((CheckOdd + CheckEven) + 3.0) / 10.0, 0) Then CheckDigit = 3 If ((CheckOdd + CheckEven) + 4.0) / 10.0 = Round(((CheckOdd + CheckEven) + 4.0) / 10.0, 0) Then CheckDigit = 4 If ((CheckOdd + CheckEven) + 5.0) / 10.0 = Round(((CheckOdd + CheckEven) + 5.0) / 10.0, 0) Then CheckDigit = 5 If ((CheckOdd + CheckEven) + 6.0) / 10.0 = Round(((CheckOdd + CheckEven) + 6.0) / 10.0, 0) Then CheckDigit = 6 If ((CheckOdd + CheckEven) + 7.0) / 10.0 = Round(((CheckOdd + CheckEven) + 7.0) / 10.0, 0) Then CheckDigit = 7 If ((CheckOdd + CheckEven) + 8.0) / 10.0 = Round(((CheckOdd + CheckEven) + 8.0) / 10.0, 0) Then CheckDigit = 8 If ((CheckOdd + CheckEven) + 9.0) / 10.0 = Round(((CheckOdd + CheckEven) + 9.0) / 10.0, 0) Then CheckDigit = 9 If CheckDigit = Convert.ToInt16(Row.UPCNumber.Substring(11, 1), 10) Then Row.UPCNumber = String.Concat("00", Row.UPCNumber) Else 'Throw out row because checksum did not match. <=== what do i do here??????????????? End If ElseIf Row.UPCNumber.Length = 14 Then ' 14 Digit Checksum Else ' Throw out row because checksum did not match. <=== what do i do here??????????????? End If If Not DoubleTest(Row.RetailPrice) Then Row.RetailPrice_IsNull = True 'Row.RetailPrice = String.Empty End If End Sub End Class
View Replies !
Visual Studio Database File And SQL Server Management Studio Express Question
I have a database in my "App_Data" folder of my visual studio project. I can view it fine in Visual Studio's built-in tools for managing a database attached to a solution. However i recently started playing around with the SQL Server Management Studio Express program. When i attach my database to Management Studio, and try to run my program it crashes. I think it might be a permissions error?!? When i detatch it and reattach it in visual studio it runs fine again. Any suggestions? ThanksJason
View Replies !
SQL Server Management Express Studio Management Tools
I have recently installed the SQL Server Management Studio Express but I do not find Management Tools in order to create scheduled backups and shrinking of the databases. I was under the impression that this should be included in the Management Studio. I use the SQL 2005 Express for smaller customers who run the SQL on a desktop unit. I need a way to backup the data to a server machine for backup purposes. I have uninstalled and reinstalled to no avail.
View Replies !
SQL 2008 Developer Edition Management Studio Does Not Work For Compact Edition 3.5 Databases
I am attempting to use the SQL Server 2008 Developer Edition (Management Studio) to create/manage a Compact Edition 3.5 database. My problem is that I cannot even create/open the compact database in Management Studio. Any help would be GREATLY appreciated. TIA Problem Creating the Compact Database: Open 2008 Management Studio From menu choose File | Connect Object Explorer... Choose SQL Server Compact Edition as the Server type. Choose <New Database...> as the Database file. Browse to folder where file will be created (C:MyDatabase#1.sdf was used in my example) Leave all other field as default selections OR make changes, either way it does not work. Click OK. At this point the OK button just becomes disabled and nothing happens. Problem Opening An Existing Compact Database: Open 2008 Management Studio From menu choose File | Connect Object Explorer... Choose SQL Server Compact Edition as the Server type. Choose <Browse for more...> as the Database file and locate a ".sdf" file that I created using VS2008. Enter password, if any. Click OK. The following exception is displayed: TITLE: Connect to Server ------------------------------ Cannot connect to C:Documents and SettingsdarrinbMy DocumentsVisual Studio 2008ProjectsTestSQLCompactEdition35TestSQLCompactEdition35AFS.sdf. ------------------------------ ADDITIONAL INFORMATION: Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476 ------------------------------ Could not load file or assembly 'Microsoft.SqlServerCe.Enumerator, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) (mscorlib) ------------------------------ BUTTONS: OK ------------------------------
View Replies !
Visual Studio 2005 Standard And SQL Server Management Studio?
I am new to visual studio and I am still not sure of all its components and features. I installed visual studio 2005 standard edition but cannot find SQL Server Management Studio? I guess this must be because it is not included with Visual studio 2005 standard. Is it included with VS 2005 professional? I want to add pictures of products to my shopping site using an SQL database and I’ve been told that SQL Server Management studio is required as it is a graphical tool. How would I go about obtaining the SQL server management studio. There seems to be different versions of SQL server that it is confusing to know which one to purchase. Will the SQL server 2005 version that comes with Visual studio standard be sufficient for me now right? I want to create a shopping site with hundreds, perhaps even thousands of products. I want to use an SQL server 2005 database. The database will include ‘dynamically generated’ product images if that is the correct terminology. My goodness, it seems I still have so much to learn. Thanks
View Replies !
Transformation Editor Or SQL Eitor. How To Search And Replace?
Hi SSIS Gurus, Coild anybody halp me, how to achive search and replace in the Expressions (Transformation Editor) or in SQL Query box? If this functionality isn't presented, what is preffered woraround to achieve it? I have a lot of transformations (about 100) in the derived column task and its too difficult to find transformation what i need.
View Replies !
|
TRACKER
