Why Do Varchar And Nvarchar Return Different Resultset In A Search?
Mar 7, 2008
CREATE TABLE #TEST (Keyfield varchar(30) NULL)
INSERT INTO #Test (keyfield) VALUES ('M-S Logistics');
INSERT INTO #Test (keyfield) VALUES ('Monster Racing');
INSERT INTO #Test (keyfield) VALUES ('Mueller Farms');
DECLARE @Search AS nvarchar(30), @Search2 AS varchar(30)
--Query 1
SET @Search = 'Monster Racing'
SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search;
--Query 2
SET @Search2 = 'Monster Racing'
SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search2;
-- Why does query 2 return different result than query 1
View 3 Replies
ADVERTISEMENT
Mar 16, 2004
Hello all,
I want to be able to be able to return a resultset from a Stored Procedure.
Something like :
CREATE PROCEDURE LSNOnAJob
@MyJobNo AS INT,@MyLsn VarChar(10) OUTPUT
AS
SELECT @MyLsn = dbo.TSample.ISmpShortCode
FROM dbo.TJob INNER JOIN
dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN
WHERE (dbo.TJob.IJobN = @MyJobNo)
GO
I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.
I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.
View 3 Replies
View Related
Jul 7, 2006
Hi all,
I need to return only the top row of a ResultSet that is generated from my Query but can't seem to find anything in SQL Server 3.0 Mobile that allows me to do that. I know in SQL Server (Desktop) I could use the TOP keyword. The query I have is as follows, and just need the top row. Now the returned resultset will, in time, be massive, and there is just no way I can afford to return this amount of data in my application only to take to the top/first row. The query I have is as follows...
SELECT *
FROM tbl_NSP_AnswerSet
WHERE (DateCompleted IS NOT NULL)
ORDER BY DateCompleted DESC
(I want the last record that was inserted into the database)
Thanks
View 9 Replies
View Related
Apr 19, 2006
I can not get a multiple row resultset to display or even get sent to my client application running on coldfusion.
What is the problem with the code?
How do i display and return a resultset to my coldfusion client application?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.nTransaction
(@pAccountNo varchar(30), @N int, @ntransCursor CURSOR VARYING OUTPUT, @nValue varchar(4000) OUTPUT)
AS
set @N = 0
SET ROWCOUNT @N
SET @ntransCursor = CURSOR FOR
-- FORWARD_ONLY STATIC
SELECT CONVERT(varchar,Eh.EntryID), Eh.EntryReference,E.AccountNo, E.Narrative, E.Amount, Eh.EntryDate
FROM entryheaders as Eh cross join entrys as E
WHERE Eh.EntrySerial = E.EntrySerial and AccountNo = @pAccountNo
ORDER BY Eh.EntryID DESC
SET ROWCOUNT 0
OPEN @ntransCursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @ntransCursor into @nValue
END
CLOSE @ntransCursor
DEALLOCATE @ntransCursor;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Pls i need urgent help!!!
View 2 Replies
View Related
Nov 27, 2014
I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.
I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:
Code:
while (@I <= @count)
begin
BEGIN TRY
-- delete all previous rows inserted in @customerRow for previous counts @I
delete from @customerRow
-- this is inserting the current row that we want to save in database
insert into @customerRow
[Code] .....
This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:
Code:
begin catch
IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
[Code] ....
Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations.
View 3 Replies
View Related
Aug 28, 2007
i have used nvarchar as my datatype in sql server 2000 now
i have decided to change to varchar as i can increase the character length from 4000 to 8000
Do I Lose data if i change the datatype.
View 7 Replies
View Related
Jul 10, 2003
I have a table using nvarchar(for what ever reason which beyond me why its a nvarchar...) that I would like to change to a varchar. There is no unicode in the fields so I don't have to worry about but I don't want to lose any text data. Will coverting the data type lose data?
Thanks
View 9 Replies
View Related
May 16, 2006
So I have an existing table that looks like:
ID BIGINT
VAL VARCHAR(128)
I am converting this table to something that will be multi language compliant. My question is, I know that NVARCHAR's take double the space of a VARCHAR. Do I actually need to double the length of the VAL field to store the same amount of data or does the DB handle that?
Basically I want to store a 128 character NVARCHAR.. do I need to set my table up like this:
ID BIGINT
VAL NVARCHAR(256)
or
ID BIGINT
VAL NVARCHAR(128)
View 3 Replies
View Related
Feb 26, 2007
Hi,I am new to MS SQL. When I create a column in a table, when shall Iuse nvarchar or varchar? Please help.Thanks,Mike
View 5 Replies
View Related
Dec 18, 2006
from the definition, i know that "n" means uni-code. but what is the exact advantage of having nchar or nvarchar over char or varchar?
View 1 Replies
View Related
Jun 19, 2004
Hello again everyone....
I have another question for everyone....
I am currently cleaning up my database to get its total size down and am not sure how nvarchar and varchar work exactly.
When defining the length of a varchar or nvarchar in enterprise manager, will that effect the size of the entry (as far as data size) no matter what the length of the entry? In other words, will there be a difference in Data Size for an entry with the length of 4 characters with a definition of varchar(4) versus an entry with the length of 4 characters with a definition of varchar(50).
****If there is no difference, is there any reason in trying to best guess the size to give nvarchar or varchar columns? It would seem easier to just define the lengths of columns which need variable lengths to 200 or 400 just to save time in not trying to best guess what the size might be...*****
Thanks ahead for any help...
-Alec
View 3 Replies
View Related
Aug 4, 2006
Hi,
Can someone please explain to me how the datapages in Microsoft SQL Server 2000 works. The pages are supposed to be 8K, that is 8192 bytes of which only 8060 are accessible for data storage (due to overhead).
Now, I currently have a table containing 8 fields. Two of these fields are varchar and should be converted to nvarchar. One of the varchar fields is limited to 255 characters and the other to 4000 characters. When I convert the 255 characters field to nvarchar it works just fine, but when I want to convert the 4000 characters field I get an error from MS SQL saying that it gets to big. Is the error only for the 4000 characters field (which growths to 8000 bytes when using nvarchar instead of varchar) or must the whole table fit into one datapage?
Could a blob maybe solve my problem, or will I face new problems when storing unicode characters in a blob?
Thanks in advance
View 3 Replies
View Related
Jan 14, 2006
I have a table with a Varchar field that will contain encrypted data. Since each byte can have a value from 0 through 255, can I use Varchar or should I change the field to NVarchar? The reason I ask is that during testing, the Varchar field sometimes is truncated, supposed to be 16 bytes but ends up as 5 or 6 or something less than 16.
View 2 Replies
View Related
Mar 9, 2007
Hi,I have a pretty straightforward question to do with variable length fields I hope someone can help me with:When using varchar (or nvarchar), is there any point in specifying a smaller length than the maximum? Does it save space or improve performance at all?ThanksRedit: I suppose the max rowsize is an issue. any others?
View 2 Replies
View Related
Feb 17, 2006
What is the difference between the nvarchar and varchar datatypes? Which should be used?
View 2 Replies
View Related
Apr 10, 2006
What is the diferent between varchar an nvarchar?
View 4 Replies
View Related
May 22, 2006
hi
could any one help me in differentiating between varchar and nvarchar
Thanks in advance
View 4 Replies
View Related
Oct 10, 2007
I have table with a field defined as nvarchar. I want to change it to varchar. I have a stored procedure which defines the parameter @strCall_desc as nvarchar(4000). Are there going to be ay problems with running this sp if I just change the field type as described.
TIA
View 6 Replies
View Related
Jul 20, 2005
HiThe maximum length of a nvarchar could be 4000 characters while that ofvarchar could be 8000.We are trying to use unicode which would require that the datatype forone our fields be converted from varchar to nvarchar. But looks likethis would result in loss of existing data.Is there a way to do this without loss of data?Many thanks.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Mar 14, 2006
I am using SQL Express
I have a very simple function to retreive the maximum value (MemberID) from a member table. The memberID column is in "nvarchar(8)" type.
The following shows the function:
ALTER FUNCTION dbo.GetLastMemberID()
RETURNS nvarchar(8)
AS
BEGIN
RETURN (SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo)
END
When i run the function, only the first 4 character (say 'IM00') is returned.
If I change the "RETURNS nvarchar(8)" to either "RETURNS nvarchar(16)" or "RETURNS varchar(8)", whole column (8 characters) is returned.
My question: should I use nvarchar(16) or varchar(8) in the function in this case?
Thanks
View 7 Replies
View Related
Apr 11, 2006
Please I know this is fustrating but I really need help with this issue:
I am getting data conversion error when I tried to load data from one SQl table to another SQL table using SSIS.
The source table has a column with data type nvarchar(max). Also the destination table has the same data type nvarchar(max) but I keep getting conversion error when I use SCD transformation.
Error: " Input column "des" (116) has a long object data type of DT_TEXT, DT_NTEXT or DT_IMAGE which is not supported"
I am fine when I use OLEDB destination but I want to do an incremental load.
Is there a quick fix for this?
Thanks
Omon
View 5 Replies
View Related
Jun 26, 2007
what is the difference between nvarchar and varchar
View 3 Replies
View Related
Jun 20, 2007
I have looked at several explinations and I understand the difference between unicode and non-unicode.
I get that the basic idea around storage is "double", 2 bytes instead of 1.
My question is, does the 2 byte instead of 1 byte rule apply even if I am storing a char that doesn't need the full to bytes.
for arguments sake I have a table called "UnicodeTable" and one column called "Letter".
If I store the letter "A" on the first row of the "UnicodeTable" does the size of my database increase by 2 bytes?
View 1 Replies
View Related
May 4, 2004
I have an existing application that relies on a SQL Server database.
I want to switch all varchar fields to nvarchar so it can handle multiple languages.
The database has ~25 tables, many of which have varchar fields. I want to convert them all to nvarchar.
The database has ~150 stored procedures, many of which have varchar fields. I want to convert them all to nvarchar.
Are there any tools out there that would let me convert the tables of my choosing, and the stored procedures of my choosing, so that any 'varchar' mentions are changed to 'nvarchar' ? I've only used SQL Query Analyzer to write queries and use MS Access (and some SQL Enterprise Manager) to make the tables and relationships.
Thanks,
-Bret
View 1 Replies
View Related
Aug 20, 2001
We have few stored procedures that use nvarchar datatype, this was not issue on SQL server 7.0 but in 2000 becomes a big issue.
For example query that runs for 3 minutes in SQL server 2000 by replacing NVARCHAR to VARCHAR the same query runs for 2 seconds.
The biggest challenge that I have deals with tables and user-defined datatypes of NVARCHAR that has been bounded to the table.
How can I alter those without data corruption?
View 2 Replies
View Related
Apr 19, 2004
Hi,
Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ???
Also, what really is the benefit of one over the over :confused:
Thanks
View 7 Replies
View Related
May 10, 2006
Hi, I'm starting a new application in java using JTDS jdbc driver(http://jtds.sourceforge.net) and SQLServer 2005 Express.I have to design the database from scratch and my doubt is if I have to usevarchar or nvarchar fields to store string data.Any experience about performance issues using nvarchar instead of varchar(considering that Java internally works in unicode too)?Thanks in advance,Davide.
View 4 Replies
View Related
Jun 29, 2007
There is a view in a SQL Server database that I need to connect to. If I connect to the database via Management Studio, the column CLIENT_NUMBER is nvarchar(15). Now in SSIS, if I add an OLE DB Source, access using a SQL Command, click Build Query, and add the view, I can see CLIENT_NUMBER as nvarchar(15) there too. Now I click OK, go to Columns, and I see that in both External Column and Output Column, CLIENT_NUMBER is specified as a DT_STR of length 30! The same thing happens if I use Table or view mode, and it happens with every nvarchar column here.
The kicker here is that I know this was working before. When I opened this package for the first time in weeks, I could see the Output Column as DT_WSTR length 15, so I know things were working then. In the meantime, I had installed SP2. Has anyone else heard about an issue like this? It certainly isn't happening with every package. Should I just take the ugly way out and CAST all of these nvarchar columns as nvarchars?
View 4 Replies
View Related
Feb 12, 2007
On the ntext, text, and image (Transact-SQL) page at
http://msdn2.microsoft.com/en-us/library/ms187993.aspx
it states
"Important:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types."
Considering this warning, is VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) support going to be implemented in SQL Server Compact Edition?
Robert Wishlaw
View 1 Replies
View Related
Jan 15, 2005
Hi,
I have an ASP.NET application that uses VARCHAR extensively in the tables and, more importantly, stored procedures (a couple hundred of them).
This app needs to start accepting foreign language in some areas, so I was wondering if there was some way to go through the tables and, more importantly, the stored procedures and change all "VARCHAR" references to "NVARCHAR" ?
Are the stored procedures stored as a text file somewhere on the server? If so I could use some sort of "replace" software utility to go through and change all VARCHAR to NVARCHAR
thanks!
-Bret
View 2 Replies
View Related
Dec 2, 2005
can anybody please explain me why microsoft using nvarchar/nchar instead of varchar/char in northwind database and pubs database. I know if a column holds unicode data you should use nvarchar or nchar but for me all those tables in northwind/pubs are not holding unicode data. but still why microsoft settled for nchar/nvarchar.
View 2 Replies
View Related
Jul 20, 2005
I have an application with highly compressable strings (gzip encodingusually does somewhere between 20-50X reduction.) My base 350MBdatabase is mostly made up of these slowly (or even static) strings. Iwould like to compress these so that my disk I/O and memory footprintis greatly reduced.Some databases have the ability to provide a compressedtable, compressed column, or provide a user defined function tocompress an indvidual Field with a user defined function[ala. COMPRESS() and DECOMPRESS() ].I could right a UDF with an extended prodcedure if I need to but I'mwondering if there are any other known methods to do this in MS SQLServer 2000 today?--Frederick Staatsfrederick dot w dot staats at intel dot com (I hate junk mail :-)
View 6 Replies
View Related