I saw the strangest thing when I imported some data into a column
today. One column contained musical notes instead of data. Has anyone
ever seen anything like this? I have no idea where this came from.
Thanks in advance.
I have a database column like this ColName(CHAR(1), null)I want to set the column to Null if no value is passed to the database.When I set my parameter to " " OR Nothing the column is not Null as I would expect. Instead a square character is there instead. The type of square you get when word or excel cannot resolve a char it puts a square in instead. This 'square' then doesn't appear as Null.Anyone know what is causing this???
View 4 Replies View RelatedI had to update pricelist at local-db on the basis of prices in another SQL by matching the description. When i JOIN the tables, no record gets displayed, but the descrptions at both tables were exactly the same i believed. Took a lot of time in identifying the discrepancy; the data was like this:
CREATE TABLE #ABC (price money, DESCRIPTION VARCHAR(200))
GO
INSERT #ABC SELECT 19999,'SQL2000
'
GO
SELECT * FROM #ABC WHERE DESCRIPTION ='SQL2000'
--(0 row(s) affected)
SELECT * FROM #ABC WHERE LTRIM(RTRIM(DESCRIPTION))='SQL2000'
--(0 row(s) affected)
select * from #ABC WHERE REPLACE(DESCRIPTION,'
','')='SQL2000'
--(1 row(s) affected)
GO
DROP TABLE #ABC
:rolleyes: I believe, ENTER has some value within a field.
Hello,
In an SSIS package, I am attempting to copy data from a source where I am using the .NET provider for ODBC. The destination is a SQL Server 2005 table, and the connection uses the OLE DB provider for SQL Server.
On occasion, the data in a column called ADDR1 can contain a character at the end of the string that comes across in appearance as a box (when observed from a UTF-8 encoded text file). When this occurs, the record makes its way to the error output. I've tried using RTRIM and TRIM, and a couple of things with REPLACE in a Derived Column transform, but I can't seem to eliminate this extra character.
If I don't configure the error output, then the package fails on a truncation error on the ADDR1 column. The error output shows that the physical number of characters in the string never excedes the allowable character length (30).
Any suggestions?
Thank you for your help!
cdun2
Hi!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());
Hi Friends
I have a identity column in a table, while adding records,
it fills the column with different values, for eg. 75 after 90.
In which situation, it may occur and how to solve this.
Thanks in advance.
Rajasekar
Can someone se whats wrong here!
DECLARE @TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))
INSERT INTO @TEMP
select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo
from mytable
group by GOgo
order by ID desc
Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102
Invalid column name 'prov'.
For me, very very strange!
Hi members,
I am facing a grange error, I hv an asp .net application running with sql server 2000 on a web server.
one table say tblProducts, I have few colums in it with an ID Primery Key field which is auto increment.
I have around 1500 records in it, what i am seeing is that autonumbers are not comming in sequence. it is skiping few numbers after rendom intervals.
like
1
2
3
4
5
7
9
12
13
14
like this there is no error in the code as its only a simple insert query. can anybody tell me what could be the reason.
regards
Aloha
Hi All,
I have a company table with CompanyName and Address details.
In the Company Name field - I have got companies with names like - 'The 1% Club', '99% Pure Water', 'The 1% Golfer' etc...
I want to search for Companies with % using the LIKE clause - Say for ex.
SELECT CompanyName from Company WHERE CompanyName LIKE 'The 1%%'
I was expecting the above query to return - All Companies starting with 'The 1%' - So from the above list - I expected it to return - 'The 1% Club' and 'The 1% Golfer'.
Unfortunately the query isn;t accepting % in the WHERE LIKE clause except for the wildcard character.
Is there a way out to escape the Wildcard Characters present in the Field Values while searching.
Thanks,
Loonysan
When generating a check constraint to guarantee that a character column cannot be blank is it best to use comparison operators such as col1 <> '' or to use LEN(col1) > 0? Note that the column in marked as not nullable.
View 5 Replies View Related
Hello,
I am having an issue when attempting to retrieve data from SPSS via a ADO.NETDBC Connection
using the DataReader source. What seems to be occurring is that the DataReader is reading a column that
has a length of 255 and what it is doing is taking the first 200 characters and starts repeating the
characters starting at character 201, in this way erasing any data held in positions 201 to 255.
Another way of saying this:
This statement returns data in the results but I have noticed the data is incorrect. It seems to only
be selecting the initial 200 characters of the 255 in the field. Then it starts to repeat the
first 200 characters again to complete the full selection of the 255 characters
Here is an example:
Instead of:
€œXXXXX changed my life before it got worse. It was very informative. They gave me the information. They left it up to me to ponder over it and make the decision on what I wanted to do. It informed me on what drugs do to your body and mind. I was stress€?
I end up getting:
€œXXXXX changed my life before it got worse. It was very informative. They gave me the information. They left it up to me to ponder over it and make the decision on what I wanted to do. It informed XXXXX changed my life before it got worse. It was very€?
Source Column Datatype that SSIS can see is of Unicode string [DT_WSTR] type. Also it correctly identifies
the length is 255 in both the External Column, and Output Column section.
I have a column that returns client numbers.
The client numbers are 4-6 characters in length. A period (.) is added to the end of the client number, and then one last digit (1-4) is affixed at the end to denote a categorization.
In SQL, I need to figure out how to divide these results into two columns, one for the client number and one for the categorization number.
EG: client #4334.1 would become 4334 for client # and 1 for categorization number
or
Client #424561.3 would become 424561 for Client # and 3 for categorization number.
I have to strip out the period in the process and leave myself with just the numeric characters divided into two columns.
Ive been researching my brains out on string queries and substring queries and I can't figure out how to parse out the period and/or to have SQL understand that I need everything BEFORE the period for one column and everything AFTER the period for the second.
Is it possible to do this? I really need help on this one.
Thank you :)
How to remove Character From Column,Just Character. I want to work with number.
View 4 Replies View Related
Hello:
I have a field hold varchar. I would like to calculate how many character in the field when user enter the data and save in the database.
Is there any way to do?
Thanks,
Snow
i am working on a small project, that I have found that someone is storing a float as a varchar(). But there are also some actual words in the same column.
I am trying to determine how I can select only the rows with alphabetical characters in that column.
Some of the data is:
1.5008e+015
1.54453e+015
1.51922e+015
1.51922e+015
1.52243e+015
but there is a mix of alphabetical characters in there as well.
1.51922e+015
1.53122e+015
FMCIT
ABCNP
FMCPNG
1.62073e+015
1.6127e+015
I want to be able to select the rows with only the alphabetical characters. There is a huge mix, and I am assuming that every first letter is one of the 26 alphabetical character used. How can I write a query to use a REGEX to select any and all rows that cannot be CAST as a Float? I have nill to no experience using REGEX.
I am trying to replace all special characters in a column with one special character.
Example:
Table: dbo.Employee
Column: Name
Name
-------
edwardneuman!"<]
mikemoreno)'>$:
JeffJensen"?>"
I am trying to get the namepart to the left of ANY special character. To achieve this, I am thinking of replacing all the special characters with a single special character so that I can find the first occurrence of that special character and grab left of the special character (SUBSTRING/CHARINDEX). This way I don't need to loop through all the special characters.
I am expecting the following results:
Name
-------
edwardneuman<<<<
mikemoreno<<<<<
JeffJensen<<<<
In DTS/SSIS packages, How do to data validation or check for special characters and remove them at Copy column level.
thanks,
Hi guys/ladies I'm still having some trouble formatting a select statement correctly.
I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database.
Here is my select statement cut down to the most basic elements.
SELECT commentFROM informix.ipr_stucomWHERE (comment > 70)
The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error
"Character to numeric conversion error"
How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values?
I have tried case with cast and isnumeric... I don't think that I have the formating correct.
I have also used:
WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F'))
This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last
I have the following scenario, The contents of main file are like :
ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And I need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).
How do I select everything to the right of the dash when the number of characters to the left of the dash varies?
I'd like to return the left-most character from an 8 character string & the third from the left character too.
Like this ABC00123 returns AC
$query = "SELECT LEFT(uninum,3), RIGHT(uninum,5), clmarea, Date FROM tblunimov";
$result = mysql_query($query) or die(mysql_error());
echo "<div class='tblstyle1'>";
echo "<table class='tblstyle1'>";
echo "<tr><th>ini</th><th>item</th><th>area</th><th>date</th></tr>";
while($row = mysql_fetch_array($result)){
[Code] ....
Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!
View 7 Replies View RelatedHi everyone,
I€™m suffering a queer behaviour when I use BIDS. Concretely, when I open a dtsx from my project (it has 10 packages) many times Sequence Container and Data Flow tasks are invisible. I mean, its lines are not visible at all whereas its titles are. I mean, what you see is just a white box€¦
Then, I€™m gonna Data Flow layer and I have to do double-clik over the tasks and are visible but on Control Flow I don€™t see how to solve.
Curiously in our development and production server such behaviour doesn€™t happen (we are accessing by mean Terminal Server from our workstations)
How odd!. Everything is fine except this.
I want to remark you that such project has been copied from the server, this is, these packages are been built on the server
Thanks for your thougts or ideas,
I have a datasource where i assign the control Parameter value depending on whether theres a value in the querystring or a gridview selectedvalue is indeed selected.When i do this using a querystring it works fine, however, when i do it using gridView_selectedIndexChanged it doesn't work.Here's the code <asp:SqlDataSource ID="jobDetailsDS" runat="server" ConnectionString="<%$ ConnectionStrings:promanConn %>" SelectCommand="SELECT tblJobs.intJobId, tblJobs.intJobStaffId, tblJobs.intJobUserId, tblJobs.intJobCategoryId, tblJobs.strJobTitle, tblJobs.strJobDesc, tblJobs.strJobNotes, tblJobs.dateJobLogged, tblJobs.dateJobDeadline, tblJobs.dateJobCompleted, tblJobs.intJobPriorityId, tblJobs.strJobSolution, tblJobs.boolJobCompleted, tblPriority.priorityName, tblPriority.id, tblPriority.priorityOrder, tblStaff.staffName, tblStaff.id AS staffID, tblProblemCategoriesLookup.strProblemCatName, tblProblemCategoriesLookup.intProblemCatId, tblStaff_1.id AS Expr1, tblStaff_1.staffName AS UserName FROM tblJobs INNER JOIN tblPriority ON tblJobs.intJobPriorityId = tblPriority.id INNER JOIN tblStaff ON tblJobs.intJobStaffId = tblStaff.id INNER JOIN tblProblemCategoriesLookup ON tblJobs.intJobCategoryId = tblProblemCategoriesLookup.intProblemCatId INNER JOIN tblStaff AS tblStaff_1 ON tblJobs.intJobUserId = tblStaff_1.id WHERE (tblJobs.intJobId = @intJobId)" InsertCommand="INSERT INTO tblJobs(intJobStaffId, intJobUserId, intJobCategoryId, strJobTitle, strJobDesc, strJobNotes, dateJobLogged, dateJobDeadline, intJobPriorityId, boolJobCompleted) VALUES (@intJobStaffId, 56 , 39, @strJobTitle, @strJobDesc, @strJobNotes,{ fn NOW() }, { fn NOW() }, @intJobPriorityId, 0); "> <SelectParameters> <asp:Parameter Name="intJobId" Type="int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="intJobStaffId" Type="Int32" /> <asp:Parameter Name="strJobTitle" Type="String" /> <asp:Parameter Name="strJobDesc" Type="String" /> <asp:Parameter Name="strJobNotes" Type="String" /> <asp:Parameter Name="intJobPriorityId" Type="Int32" /> <asp:Parameter Name="intJobUserId" Type="Int32" /> </InsertParameters> </asp:SqlDataSource>Code Behind ElseIf dest > 0 Then jobDetailsDS.SelectParameters.Item("intJobId").DefaultValue = Request.QueryString("dest") notesDS.SelectParameters.Item("intJobId").DefaultValue = Request.QueryString("dest") notesDS.InsertParameters.Item("intJobId").DefaultValue = Request.QueryString("dest") gvActiveJobs.Visible = "False" End If End Sub Protected Sub gvActiveJobs_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvActiveJobs.SelectedIndexChanged gvActiveJobs.Visible = "False" jobDetailsDS.SelectParameters.Item("intJobId").DefaultValue = gvActiveJobs.SelectedValue notesDS.SelectParameters.Item("intJobId").DefaultValue = gvActiveJobs.SelectedValue notesDS.InsertParameters.Item("intJobId").DefaultValue = gvActiveJobs.SelectedValue End Sub I also debugged and checked the value of gvActiveJobs.SelectedValue and it is what i was expecting but its still no good.Can anyone help?ThanksMatt
View 2 Replies View RelatedI moved a table from one file group to another file group. After moving the table the row count show 0 but when I open the table it’s returning all the rows. Is something wrong with the table? Do I have created a cluster index and drop on the table?
View 1 Replies View RelatedHi,
I have table in my db thats used as a temporary table. At the end of the day it contains thousands of records which get summarized then all are deleted. I recently moved the application over to a new server with pretty much the same hardware config and noticed a big performance hit when running queries on this temp table (455 milliseconds as opposed to 1) In short, the fix was dropping the table and re-creating it.
Anybody know how this could be? I know that even if you delete records from a table, the table still seems to retain its physical size on the hard drive, could this have something to do with it?
Thanks
CREATE TABLE #users(users varchar(50),pasw varchar(50),title varchar(50))
insert into #users select 'test','abcdef','ceo'
SELECT * FROM #users
alter table #users alter column pasw nvarchar(50)
update #users set pasw=pwdencrypt(pasw)
SELECT * FROM #users
What happens to the TITLE column?
I had a table like the above with users and passwords in the Production DB. The password column had VARCHAR type. I changed it to NVARCHAR and encrypted the passwords. When i executed the SELECT *, the title column appeared like this. :eek:
If i query the table by column names instead of * i see the correct values. I couldn't understand what behaviour is it.
Howdy!
Hi Experts,
Am able to restart my local sql server using a login which dont have a sysadmin privileage.This login only have access to a particular database .Its only having datareader and datawriter as DB roles but still am able to restart the server.Please help
TIA
RKNAIR
I am using c# and ASP.NET 2 and I am getting a very strange error. I
have a field called CompanyID in SQL Server 2005, and it allows null
values in it. When this CompanyID is not NULL, ie for example it is
202, then when i press the update button it allows me to update
perfectly in the database. However if the CompanyID is NULL,
and I try to update the companyID, to lets say 202, the application
just crashes with the following message:- System.InvalidCastException: Object cannot be cast from DBNull to other types. I tried to set some breakpoints in the application to debug, however it does not even pass through these breakpoints! Any ideas on what the problem can be or how I can debug? Thanks for your help and time Johann
This is the actual statement displayed from Response.Write in classic ASP. INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '06-012497',TaskNum,TaskDesc,TaskMemo,Account,'2006-Oct-3',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum = '000002' There are 4 records returned from the SELECT part of the statement. In some situations, 4 records are inserted to WOTasks table, in others, only 1 record is inserted. I can't find out why 1 record, instead of 4, record is inserted. A form page submits the form to the save page using post method. The above statement is contained in the save page. When one of the form textbox is filled, 1 record is inserted. When the textbox is not filled, 4 records are inserted. You may think the textbox has something to do with the behaviour. I also think so but the content of the textbox does not affect the sql statement. In both cases, the insert statement is the same. In the actual codes, only strings in quotes are variables and the rest are hardcoded. When I run the statement in SQL Server, 4 records are affected. No such problem when connected with Access.The actual code belowSub AddTask(ByVal proc, ByVal wonum) Dim sSQL sSQL = "INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '" & wonum & _ "',TaskNum,TaskDesc,TaskMemo,Account,'" & curDate & "',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum='" & proc & "'" 'Response.Write sSQL:Response.End conn.Execute sSQL, , 128 End Sub
View 2 Replies View RelatedHi there I have this statement I have written as follows:strCommand = "SELECT * FROM tblstock WHERE Type='"&Statement &"' AND Description like'"&criteria &"%' OR Tag like'"&criteria &"%'OR Location like'"&criteria &"%' OR LAN like'"&criteria &"%' OR RAM like'"&criteria &"%' OR CD like'"&criteria &"%' OR OS like '" &criteria &"%' OR SN like'" &criteria &"%' OR DeviceStatus like'" &criteria &"%' ORDER BY " &sSortStr The variable "Statement" is passed into the sub as "PC" which means only records of type "PC" should be displayed along with any other criteria. The issue I'm having is that when I specify criteria I'm also recieving other types eg "Cameras" if they contain any of my criteria.. I can't understand how because in the statement I tell it only to display records of type "PC".. Anybody know what I'm doing wrong? Thanks
View 3 Replies View RelatedI've done a new tabel that insert the UserId that in a uniqueidentifier get from Membership.GetUser().ProviderUserKeySo if I want to make a select statement threw storedprocedure in codebehind it runs as it shouldCode behindDim GetCustomersCars As CustomerCarByUserId = New CustomerCarByUserId MyCars.DataSource = GetCustomersCars.CarByUserId(Membership.GetUser().ProviderUserKey)MyCars.DataBind() But in when I use ObjectDataSource it fails<asp:ObjectDataSource id="ObjectDataSource1" runat="server" selectmethod="CarByUserId" typename="CustomerCarByUserId"> <SelectParameters> <asp:Parameter defaultvalue="Membership.GetUser().ProviderUserKey" name="UserId" type="Object" /> </SelectParameters> </asp:ObjectDataSource>I've tried with Membership.GetUser().ProviderUserKey.ToString(), but that doesnt work. Error message:InvalidCastExceptionI connect to the same source in both cases.Any one with an Idee ?
View 1 Replies View Related