Is there a way to supress output on one column in a SP, using data from the same row?
Like This:
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active
FROM dbo.tblResidents
But which returns null for some of the columns if DOT is not null?
DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning.
I tried several variations of the following, but I can't figure it out
CREATE PROCEDURE [dbo].[spTesting] AS
BEGIN
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility
FROM dbo.tblResidents A
WHERE DOT IS NULL
UNION
SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility
FROM dbo.tblResidents I
END
GO
I wrote a stored procedure that executes a dbcc sqlperf(logspace) statement many times. Therefore, I would like to suppress the output, "DBCC execution completed...". Can this be done? I have checked Books Online for a trace flag or set command to no avail.
The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....
I'm trying to suppress whitespace in a drilldown for textboxes that have suppress duplicates applied.
I have a matrix report that is showing whitespace in a drilldown because I am supressing duplicates. Based on what I read in other forums, if I set the ToggleItem to Len(FieldName)=0 that should supress the whitespace, right?
I can see that I have a field in the toggleitem called: Firstname. If I put the value Len(Firstname)=0 in the toggleitem property, then I get the error: The textbox 'textbox21' has Len(Firstname)=0' as a toggle item. Toggle items must be text boxes that share the same scope as the hidden item. I think the code 'Len' is throwing it off.
If I put the value "Firstname" in the toggleitem property, then it doesn't return the error, so I know that firstname is a valid value for toggleitem, but setting the value to firstname doesn't suppress anything.
If someone can tell me how to supress a textbox based on a value, then this may get rid of the whitespace I'm trying to suppress. Any ideas? Thanks...
i'm retrieving addresses from a database and displaying them in my report. i have an addr line 2 for addition address data if needed. i have placed this addr line 2 on its own detail row. however i do not want that row to display if there is no data. the following is happening even though i have set the visibility on the row and text field to =iif(fields!addr2="",false,true)
the name prints on the first line, the main address on the second line, i have a space where addr line 2 would have been, finally i get the city, state, zip on the last line.
expected outcome i would like is that addr line 2 does not appear for those addresses that addr line 2 does not have any data. if addr line 2 does have data then print.
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
select hierarchy.hiername,devicefail.deviceid ,sum(DATEDIFF(minute,started,ended)) as duration ,100 - SUM(datediff(minute,started,ended))/(672 * 60.0000)*100 AS Uptime from devicefail LEFT JOIN device ON device.deviceid = devicefail.deviceid LEFT JOIN hierarchy ON device.hierlevel = hierarchy.hierlevel where devicefail.started >= '2013-02-01 00:00:00'and devicefail.ended <='2013-02-28 23:59:59' and devicefail.componentid like 201 or devicefail.componentid like 0 group by devicefail.deviceid,hierarchy.hiername,devicefail. componentid order by hiername
I have two queries I would like to combine the output on. they are as follows:
Select substring(WrkSta.[Name],1,2) 'Location' ,count (aexe.ReturnCode) as '# Patched' from WrkSta, AeXEvt_AeX_SWD_Execution aexe where WrkSta.WrkStaId=aexe.WrkStaId and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' or WrkSta.[Name] like 'ET%') and (aexe.returncode='0' or aexe.returncode ='3010') and aexe.AdvertisementName like 'MS05-035-043%' group by substring (WrkSta.[Name], 1,2)
--- Returns : Location # Patched EP 102 ES 1986 ET 19 AB 174 SU 6 EM 506
and the second one:
Select substring(WrkSta.[Name],1,2) 'Location' ,count (coll.WrkStaId ) as '# Workstation' from WrkSta join AeXNSCollectionMembership coll on WrkSta.WrkStaId=Coll.WrkStaId where coll.CollectionGuid = '38F5DAFC-E09D-49A5-A0FD-370983CA7596' and (WrkSta.[Name] like 'ES%' or WrkSta.[Name]like 'EM%' or WrkSta.[Name] like 'EP%' or WrkSta.[Name]like 'AB%' or WrkSta.[Name] like 'SU-NP%' or WrkSta.[Name] like 'ET%') group by substring (WrkSta.[Name], 1,2)
--- returns:
Location # Workstations EP 178 ES 2299 ET 24 AB 215 SU 13 EM 582
What I need is :
Location # Workstations # Patched EP 178 102 EI 2299 1986 ET 24 19 AB 215 174 SU 13 6 EM 582 582
No mater how I try to do a join to do this in a single query I end up with what looks like a cross-join and # workstations and # Patched jump to huge numbers. I obviously am having a problem understanding how to set up the select statements so that I can do this in one query or am I following the wrong direction and should be trying something else? Once again I appreciate your help in advance....
Hello,I was wondering if anyone can help me figure something out.Is it possible to do a querey in MS SQL server and have the resultsreturned so that each result in each row is preceeded by the columnname?eg. instead of usual output -colName1, colValue1,colName2,colValue2,colName3,colValue3 ?Also I would like to only have this for certain columns ie in theabove example only for columns 2 and 3Thank you! :-)Yas
I would like to get the actual name of the column that has the error. Using the ErrorColumn (int value) I thought there would be some type of lookup collection based on the input (like column names)- if there is, can someone tell me how to get to it?
I have my error output writing to a stored proc, but instead of "32226" as the column name, I need to have the actual name of the column. I am going from Flat File to OLE DB Destination. I have a Script Component getting the output to write to my sproc, and I just need to get the column name.
I have a Data Flow Task that extracts some data using a DataReader Source and loads it to a Raw File Destination. I am getting the following error message:
[DataReader Source [2357]] Error: The value was too large to fit in the output column "LASTCOL" (2558).
I thought that using a Raw File Destination would avoid this type of problem. How can I resolve this issue?
using sql database, i have a smallmoney column. when i enter an amount, 50.00 for example, i have 50.0000 displayed. is there a way to only have 50.00 displayed? same with the smalldatetime, is there a way to limit the display to "mm/dd/yyyy" without the "hh:mms am"
The problem is solved but still I would like to know what is going on behind the scenes. I was always thinking that the native client was involved and not oledb.
Using the Export/Import wizard you still end up going via SQL Server Agent that's where the Credential/Proxy items show up.
I would like to change the output of a column into a hyperlink however I am not sure that is possible without some further post-processing. I am aware I would have to do:
Code: select '<a href="' + URL + '" target="_blank" nav="web">' + tbl_ID.ID + '</a>' from tbl_ID however this obviously just exports the text <a href="[URL]" target="_blank" nav="web">[ID]</a>
This would work fine if I was exporting it into some sort of HTML table however this doesn't work with excel for example. Is there a way to make this possible or would I have to create a macro in excel to add the url with the ID separately?
I'm importing data from and oracle database to an SQL one through a SSIS package, I'm getting this error: "The output column "earned_hours" has a precision that is not valid. The precision must be between 1 and 38". the package runs but returns this column as NULL values
earned_hours is of type "NUMBER" in oracle (some of the values are decimals), I tried making it numeric(x,y),float or decimal(x,y), but I'm still getting the same results.
does anybody know why is this happening or have a solution for this error?
I use bcp command to output to excel, it works. But I want to format the excel, some column width are too small,user need adjust the column width, otherwise it shows ######.
How can I set columns width when I use bcp output to excel.
Also, can bcp command output to multiple excel sheets and add report title in each excel sheet?
ID AMT 1001 1234.560 1001 34.560 1001 134.000 1002 45.000 1002 3456.000 1003 5678.999
I need to create a fixed length data file..For example, ID char(6) and amt num(10.3) and sum(23.3)
It should be group and order by ID : 01 Represent ID line and 02 represent amt ( there can be multiple amt records) and 03 represent sum of amt. 01 + ID 02 + AMT 03 + Sum(AMT)
The output should look like this.. How do this either using a t-sql or SSIS?
I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,
Select SerialNumber From SerialNumbers Where OrderID = @OrderID
If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.
When configuring error output, I want everything that is good in the row to make it to the destination, and then the offending column that is causing an error to be set to NULL, and then sent to the destination as well. In addition, I want to take the offending column's data, and route it over to an error holding table. I know about the ability to redirect the whole row, but I just sort of want to redirect just that column. For example....
Have a table with 5 columns
col1 int null,
col2 int null,
col3 char(3) null,
col4 bit null,
col5 int null
My data flow loads data from a flat file and has a record that looks like this
1 5 ABC R 3
I want the row to make it to the destination as follows....
1 5 ABC NULL 3
Then the offending data needs to go over to my error table
Iam redirecting the error output of a OLEDB destination component to a script component. My aim is to create a HTML report having the information about the bad records, the error occuring in the rows and the column name that fails. The error output provided two new columns i.e the errorcode and errorcolumn , the errorcolumn value for a bad record gives the linage id for the column, is there a way to derieve the name of the column by using the lineage id?
I have an ODBC connection manager to a Progress database. In that database there is a column declared as a string of 10 characters long. However, some data in this column is actually up to 15 characters long. This makes my DataReader Source fail everytime I try to run my package because it sets the output column like this :
Datatype : Unicode string [DT_WSTR] Length : 10
Is there any way to solve this without changing the datatype in the Progress database (that is beyond my control) ?
I'm just beginning to use DTS. I have a test table which has only two column, char (10) and decimal (5,0). I want to do a simple transformation by adding the second column by 100 with the following statement, but I receive a type mismatch error!
Function Main() DTSDestination("a")=DTSSource("a") DTSDestination("b")=DTSSource("b").Value + 100 Main = DTSTransformStat_OK End Function
or
Function Main() dim x DTSDestination("a")=DTSSource("a") x = DTSSource("b").Value + 100 DTSDestination("b").Value = x Main = DTSTransformStat_OK End Function
Hello friends I need a very ergent help!!!!!!!!!!!!! Problem: my sql server on production had everything grayed out, when I want to administer this server as a SA. eg: I can't add users, I can't backup and so on. but the database is still running and users are accessing it. This SQL database is accessed by an application(it can also create users on the backend). I don't know how it happend ( i cant even get to errorlog). I beleive somehow sa user rights were taken away from him. Can anyone tell me how to restore sa with all rights.
Please help me on this matter, because I have to do lots of important tasks on this server.
Test1 that lists all the training courses that i can count to find out the total as below.
select count (distinct Training_Course) as total from Test1
Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.
select Cust_id, count (Attended) as TotalAttend from Test2 Group by Cust_id
What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.
Make any sense? Any help/suggestions gratefully recieved.
I'm very rusty with my SQL, and could use a little assistance on building this query. Thanks for taking the time to help me. It should be a relatively simple Count() query but I'm not getting the right results for some reason and I'm hoping somebody can point out my error to me.
I'm attempting to count the number of times each m_id is returned after running this query:
SELECT m_id FROM taglink WHERE m_id <> '25' AND t_id IN ( SELECT t_id FROM taglink WHERE m_id = '25' )
which returns: m_id 33 34 34 35 35 35 36
I want to count the number of times that m_id is returned so that the results of my query will be:
m_id | count 33 | 1 34 | 2 35 | 3 36 | 1
In my attempt to do this, I run the following query:
SELECT m_id, count( m_id ) AS "count" FROM taglink WHERE m_id IN ( SELECT m_id FROM taglink WHERE m_id <> '25' AND t_id IN ( SELECT t_id FROM taglink WHERE m_id = '25' ) ) GROUP BY m_id
The problem I'm having is that the query returns:
m_id | count 33 | 3 34 | 2 35 | 3 36 | 3
It seems to return the count of "3" for fields that should count "1," but count correctly if the fields count "2" or "3."
Perhaps somebody can spot my error... I can't seem to wrap my brain around this one. Thanks so much for your time!