I have a table where inactive names are lower case and active names are
uppercase. Note: Not my design.
Anyways I want to select all names form this table where the name is
uppercase. I see collate and ASCII pop up in searches but the examples
don't seem usable in queries as much as they were for creating tables
and such.
Thanks,
Phil
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies View RelatedHi all,
I have a SP that needs to return a record set based upon the values of calling another SP. The 2nd SP uses the output parameter and works fine. However, the resulting Insert doesnt see the value. I cannot for the life of me work out why not!
My other question is how do I stop the imbedded procedures from outputing a result? When the procedure is run in analyser it produces three sets of data. The first two being the outputs for the two SPs.
Any help would be much appreciated!
Code as follows:
CREATE PROCEDURE [dbo].[trl_GetOpenDurations] AS
--- CALLS the sp trl_CallOpenDuration with the following params
---1 min minute value - int
---2 max minute value - int
---3 return value - int - interval in minutes
set nocount on
declare @min int --- less than 1 min
declare @hour int --- more than 1 min, less than 1 hour
create table #t ([Type] VARCHAR(10) ,[Value] int)
EXECUTE trl_callOpenDuration 0,1,@min
insert into #t ([Type],[Value]) values ('<1Min', @min)
EXECUTE trl_callOpenDuration 1,60,@hour
insert into #t ([Type],[Value]) values ('<1Hour',@hour)
---get data from table
Select * from #t
GO
RESULTS:
<1Min NULL
<1Hour NULL
Hello,
I'm brand new to this forum and I'm stuck. We've got a query which queries three tables: booking, catheter and patients. The following (dodgy) query has pulled off the data for years, without problems:
SELECT
CARDIAC_CATHETER.CARDIAC_CATHETER_ID,
CARDIAC_CATHETER.OPERATOR1,
CARDIAC_CATHETER.TEST_DATE,
BOOKING.STATUS,
BOOKING.TEST_DATE AS BOOKING_TEST_DATE,
BOOKING.PROCEDURE_DATE_TIME,
CARDIAC_CATHETER.ITEM_NO,
CARDIAC_CATHETER.BILLING_CATEGORY,
PATIENTS.REFERRING_PHYSICIAN1,
PATIENTS.HOSPITAL_UNIT_NUMBER
FROM
(CARDIAC_CATHETER INNER JOIN PATIENTS ON CARDIAC_CATHETER.PATIENT_ID = PATIENTS.PATIENT_ID)
LEFT OUTER JOIN BOOKING ON PATIENTS.PATIENT_ID = BOOKING.PATIENT_ID AND 'CATHETER' = BOOKING.CLINIC AND BOOKING.STATUS <> 'CANCELLED' AND BOOKING.PROCEDURE_DATE_TIME >= (CARDIAC_CATHETER.TEST_DATE - 1)
AND BOOKING.PROCEDURE_DATE_TIME < (CARDIAC_CATHETER.TEST_DATE + 1)
WHERE
CARDIAC_CATHETER.TEST_DATE > '2005-04-01' AND CARDIAC_CATHETER.BILLING_CATEGORY LIKE '3%'
About 2500 perfect rows and 12 rows which have duplicate catheter id values. There are six pairs of duplicated records. It's only the cardiac_catheter.catheter_id and cardiac_catheter.test_date which are duplicated - the first row shown below contains correct values, the second doens't. The other values returned, on both rows, are correct. Here is some sample data:
CARDIAC_CATHETER_ID
cat_id1234
cat_id1234
CARDIAC_CATHETER_TEST_DATE
19/12/2005 13:17
19/12/2005 13:17
BOOKING_TEST_DATE
19/12/2005 13:33
19/12/2005 08:46
PROCEDURE_DATE_TIME
20/12/2005 10:30
19/12/2005 08:46
CLINIC
CATHETER
CATHETER
I know the query is rubbish. Has anyone got any idea how it can be improved and sorted so that we don't pull back ambiguous data?
Thanks,
Stewart
I have a stored procedure that selects * from my table, and it seems to be working fine:
USE myDB
GO
IF OBJECT_ID ( 'dbo.GetAll', 'P') IS NOT NULL
DROP PROCEDURE GetAll
GO
CREATE PROCEDURE GetAll
AS
DECLARE ref_cur Cursor
FOR
SELECT * FROM myTable
Open ref_cur
FETCH NEXT FROM ref_cur
DEALLOCATE ref_cur
The problem is, I'm trying to create a DB class, and I'm not sure what Parameter settings I'm supposed to use for my returned values. Can anyone help me finish this?public class dbGet_base
{
public dbGet_base()
{
_requestId = 0;
}
public dbGet_base(Int64 RequestId)
{
this._requestId = RequestId;
getDbValues(RequestId);
}
public void getDbValues(Int64 RequestId)
{
getDbValues(RequestId, "GetAll");
}
public void getDbValues(Int64 RequestId, string SP_Name)
{
using(SqlConnection Conn = new SqlConnection(ConfigurationManager.AppSettings["WSConnection"]))
using (SqlCommand Command = new SqlCommand(SP_Name, Conn))
{
Command.CommandType = CommandType.StoredProcedure;
Command.Parameters.Add("@Request_Id", SqlDbType.Int).Value = RequestId;
Command.Parameters.Add(??
}
}
say i have a table, and in it are two columns, column1 and column2 and i do the following query:
SELECT column1, column2 FROM table WHERE column2 = '12345'
i want to check if column1 has all the same values, so in the first case, no
column1 column2
------- --------
4 12345
9 12345
5 12345
column1 column2
------- --------
9 12345
9 12345
9 12345
in the 2nd case, column1 contains all the same values, so yes
is there anyway i can check this? i would be doing this in a trigger.. say when a new row is inserted, the value of column1 is inserted, but col 2 is null.. so when they try to fill in the value for col2 of that row, the trigger checks to see if the value they put for col 2 is already in the table.. if it isn't, then everything is ok. but if it is already in teh table, then it checks col1 to see if all the values of col1 are the same
i hope this makes sense
thanks
I am getting the following results from my query that contains a subquery, but I don't understand why the values in the [Total Volume M_Active_Previous] are being repeated with the same value. I should be getting different values returned for each row like in the [Total Volume M_Active] column.
Why the values are all the same and how I can fix this?
DayNoDayTotal Volume M_ActiveTotal Volume M_Active_Previous
6 Friday11161 72491
7 Saturday5687 72491
2 Monday14354 72491
1 Sunday3966 72491
4 Wednesday12340 72491
3 Tuesday12018 72491
5 Thursday11833 72491
Here is the SQL I'm using.
Code:
DECLARE @StartDate datetime, @EndDate datetime, @Prev_StartDate datetime, @Prev_EndDate datetime
SET @StartDate = '2011-03-13 00:00:00.000'
SET @EndDate = '2011-03-19 23:59:59.999'
SET @Prev_StartDate = '2011-03-06 00:00:00.000'
SET @Prev_EndDate = '2011-03-12 23:59:59.999'
[Code] ....
Hi Guys,
I have had a look through the forum and I can't seem to be able to find how to do this... I have a vague recollection of achieving this before but it was many years ago since I've done any sql.
I have a table like this
StaffID JobNumber Status
------- --------- ------
1 50 'In Progress'
2 20 'On Hold'
3 30 'Completed'
4 40 'Completed'
what I want to be able to do is change the status to something common if it is not 'Completed' I don't want to update the database though, I just want to return it in a select statement. Is this possible.
My output would be something like;
StaffID JobNumber Status
------- --------- ------
1 50 'NOT Completed'
2 20 'NOT Completed'
3 30 'Completed'
4 40 'Completed'
Many thanks in Advance.
Bobg
I am trying to use a stored procedure inside the scripter in a site server pipeline. Can anyone tell me how the scripter will read the the result which is a variable. The stored procedure is returning the right value when run in query analyzer but I don't know how to retrieve it inside the pipeline.
Thank you
JG
This should be a simple solution, but it has been a long time since I've done any query writing (mostly in Oracle) and I am stumped, so here goes:
We are in the process of converting Access database to MSSQL with web form front ends.
I have a table, all columns are nullable, and want users to be able to query from a form, which has a field for each column and defaults to a % wild card for the entered value.
I want the users to be able to put any string in any field, and have it return each row that matches that, including rows with null values in the other columns, but not the column with the entered criteria.
Here is a sample of the data:
Code:
SQL> select * from test;
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too
7 rows selected.
Now, if I have this SQL run, it will return only rows that have no nulls in any columns:
Code:
select
col1,
col2,
col3,
col4from test
where
col1 like'th%'
and col2 like '%'
and col3 like '%'
and col4 like '%';
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is test too
Now, if I use an OR clause for each column, this mostly works, but the trouble is it will also return rows with null values for the field that has criteria entered in it:
Code:
select
col1,
col2,
col3,
col4from test
where
(col1 like'th%' OR col1 is null)
and (col2 like '%' OR col2 is null)
and (col3 like '%' OR col3 is null)
and (col4 like '%' OR col4 is null);
COL1 COL2 COL3 COL4
----- ----- ----- -----
this is a test
this is not test
this is not
this is test too
is test too
is too
is too
The idea is to only select the first 4 rows in the above example.
I was playing with ISNULL in the select clause, but all it does is substitute a string for a null, and I think CASE will do the same thing.
Is there a way I can write this query so it will return rows with NULL values in any column, except the one(column) that has user entered criteria in it?
The query below gets percentage of coils for a location by anneal cycle. It does this correctly (ie, if you change count(*)/b.total to count(*), b.total ... show both columns, the numbers its pulling are correct), but in its current state (trying to divide count by total), it always returns a zero (no decimal values).
If I change that section to : (count(*)*100/ b.total*100)/100, I get integer percentages: no decimals (this is a problem because some records round off to zero). I'm pretty sure this is the entire problem (why I'm getting zeros as written below), but I don't have a clue how to fix it. Is this some kind of server setting that allows the query to only return decimal values or something?
select a.department, a.cycle, count(*)/ b.total percentage
from inventory a join (select department, count(*) total from inventory
where location not like 'bas%' and archived = 0 and quality_code = 'p'
group by department) b on a.department = b.department
where a.archived = 0 and a.quality_code = 'p' and location not like 'bas%'
group by a.department, a.cycle, b.total
order by a.department, a.cycle
I am building reports against our TFS development db.
One of the reports tracks days spent (Dwell Time) in various status categories (eg: New, Assigned, In Development, Hold, etc) for a given "ticket".
For a fixed list of values from {Work Item].System_State, I can send the results (days in Assigned) to the column named (Assigned) for each status for each event in the [Work Item History], and then sum them for each ticket as:
Ticket ID New Assigned InDev etc
1230001 2 0 0 ...
1230001 0 1 2 ....
SUM 2 1 2 ....
However, I have many different Projects, each of which use their own Status names.
I don't want to duplicate the same basic report, if I can avoid it.
How can I name and generate this data for the unique Status list for each Project?
Simplest analog is: name = First(Fields!Status.Value, "TFSdb")
and allows value for a column name (category) as: =IIF(Fields!Status.Value = First(Fields!Status.Value, "TFSdb"), Fields!Days.Value, 0)
However this fails beause:
1. It only delivers the FIRST status value, and,
2. I cannot SUM an expression which is itself an aggregate (using First).
I have a question regarding the values returned in the SEARCHABLE column of the system stored procedure sp_datatype_info. What do ALL of the values mean? The SQL Server BOL only provides meanings for the values 0 and 1, but I am also seeing values 2 and 3. What are the meanings of these values?
Thanks,
Sean
Hello--
For extracting the link structure of a dependency network with a large number of nodes (for problems having a large number of variables), we have been using the stored procedure:
System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph('{model-name}', value)
The stored procedure returns a resultset with columns: [Node_type], [Node_unique_name_1], [Node_unique_name_2], and [MSOLAP_NODE_SCORE]
Are there any pointers, references or descriptions of the values of [MSOLAP_NODE_SCORE]?
Thanks,
- Paul
I have no problem getting OLE DB Command transformations to support single returns by a procedure.
For example, exec name_of_procedure ?,?,? OUTPUT
However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.
thanks
John
I'm using SSIS 2005 Enterprise edition, I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server). When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.
The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it. URL... but it only works when the first row of data has a number beginning with zero on this column. How to get the data imported correctly?
In sql2005 how can I update a table in my db to give me all uppercase. I have never created a sp but would like to try to do this so can someone point me in the right direction. If a select/update statement is better then please give me suggestion that way also. Thanks in advance
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1445009
MELVIN
CALVIN
A
000142718
12/11/2007 6:44:00 AM
40
503
5831
mark
lama
A
000005831
12/11/2007 7:51:00 AM
41
503
01
8300
tito
fernandez
A
000008300
12/11/6:58:00 AM
41
503
01
2380
ANNABEL
MEZA
A
00000380
12/11/2007 8:06:00 AM
41
503
01
3697
armando
hernandez
A
000003697
12/11/2007 7:24:00 AM
41
503
01
5208
juan
guardiola
A
000005208
12/11/2007 7:29:00 AM
41
503
01
523614
JOHNNY
KELLEY
A
000143625
12/11/2007 8:00:00 AM
41
503
04
How can I create a trigger that obliges UPPERCASE of a field in thedatabase?thanks
View 3 Replies View RelatedI have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
I have been tinkering with triggers and have tried to build one that will format the names in two columns to UPPERCASE.
Trouble is it won't run and I don't know why, how can I accomplish this uppercase task with a trigger?
Hi,
I have a table where the values in the Description field are all upper case. I found a script that will change all the words to UPPERCASE in the first character then LOWERCASE for the remaining until the next space is found. It will also repeat this for all words in the field.
The part I need help with is having this repeat through the entire table. Heres the code and thanks - troy
declare @input varchar(20)
declare @position int
set @input = (SELECT Description FROM tbl_ItemMast where wallysku ='10071')
set @position = 1
SET @input = Upper(substring(@input,1,1))+ LOWER(substring(@input,2,len(@input)-1))
WHILE @position < DATALENGTH(@input)
Begin
set @position = charindex(' ',@input,@position+1)
if @position = 0
goto done
SET @input = REPLACE(@input,substring(@input,@position,2),Upper(substring(@input,@position,2)))
End
DONE:
PRINT @input
Hi,
What is the dowside of not using all uppercase for predicates and key words?
I cannot find to see a problem beside adhering to a clean coding convention. After all I already have color coding so what would be the uppercase for?
Same question for the semicolon ; at the end of a sql block. Is that real necessary not to get in trouble sometime down the road or is it a non-issue. I find like I am now a C# guys if I use these ;
Just curious, I find tedious to switch from all upper case to normal case all the time. And not forget to type the ;
Thanks,
Philippe
Hi all!This is the problem:User enters student name, in the database, only the 1st letter is uppercase and the rest is in Lower case.So, I want to fix this so as it is not case sensitive, i.e. the user can enter a name and it will return the recordwhether they enter it in upper or lower case.My Code:CvtUpperCase.Text = UCase(Content.Text) //stores user's input
Select Case OptionChoice
Case "Student_FirstName" MyCommand = New SqlDataAdapter("select * from [qryStudentDetails] where [qryStudentDetails].[Student_FirstName] like '" & CvtUpperCase.Text & "'" , myConnection)
....Any ideas??
Hi
I am trying to output the data in the table to uppercase. I am using bcp to output as text file.Any possible solution to convert the data to uppercase from the table.Any tsql code for this
Thanks in advance
I am always changing the Sql Query Keywords to UpperCase and formatting the T-sql.
Is there a option so that i can change the keywords to uppercase automatically in the sql 2005 management studio ?
How do most of you format the sql statements?
Please let me know it will save me and most of us like me alot of time.
Your help will be most appreciated.
Thanks in Advance
Savvy
In Query Editor I type statements like this:
"select * from ...."
Does Query Editor support a "macro" facility where I could, via keystorke, uppercase all t-sql keywords? (so it would look like SELECT * FROM...)
TIA,
barkingdog
P.S. You think I'm lazy? I knew a programmer who was so lazy that his password was one character long!
I am trying to convert all my client first and last names in my table to uppercase. They are currently listed as mixed case. Also I wanted to know what is the best way to force the data to UPPERCASE hwen a end user tries to insert or update the clients name. I am thinking about trying a trigger, but I am unsure how to set it up. Thanks for all the help.
View 4 Replies View RelatedI know you are able to display data all uppercase or all lowercase, but how do you display it First letter capital rest lower. Like a First or Last name?
View 3 Replies View RelatedI would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?
View 3 Replies View RelatedThe states in this report are all in caps TEXAS, CALIFORNIA, etc.. Is there a way to use expressions to only have the first letter in uppercase and rest in lowercase?
View 3 Replies View Related
Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
Hellen
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5