|
Hi,
Maybe somebody can explain a weird phenomenon I am, sometimes, seeing.
Using Microsoft SQL server (edition 2000) I have a stored procedure which returns a particular set of results. The result set is a simple SELECT from a temporary table, the odd result occurs in this returned column:
WC_Diff = CASE WHEN EmpDiff > 0 AND WC_ID > 0 THEN
'+' + CAST(EmpDiff AS VARCHAR(3)) + ' (' + CAST((NoOfEmp - EmpDiff) AS VARCHAR(3)) + ' » ' + CAST (NoOfEmp AS VARCHAR(3)) + ')'
WHEN EmpDiff < 0 AND WC_ID > 0 THEN
CAST (EmpDiff AS VARCHAR(3)) + ' (' + CAST((NoOfEmp - EmpDiff) AS VARCHAR(3)) + ' » ' + CAST(NoOfEmp AS VARCHAR(3)) + ')'
ELSE NULL END,
The weird bit is the way the '»' character changes every now and again.
Using Microsoft's Query analyzer (or a variety of other client programs) the '»' character is sometimes modified to a '+' character. In the few cases it happens all I need to do to get the right output again is recompile the procedure without any change at all.
I first thought it was a problem with 8 bit ASCII to 7 bit ASCII conversion but that is not right: '»' has hex code 0xBB which would be converted to 0x3B (';') but it becomes 0x2b ('+').
I am baffled: anyone out there who knows what may be wrong ?
Any suggestions much appreciated.
|
|
|
|
|
Hi Again,
It took a while but I figured out what is the cause and the solution (google helped a lot).
Cause: each night the procedure is recompiled using 'OSQL' (to keep a development server up to date after copying the real server's data bases just before that). That command line utility uses OEM code page 437 and when reading the usual ANSI format SQL files it converts various things which causes the character to change.
Solution: when reading UNICODE format files OSQL makes no conversions and everything works. Both the Query analyzer and OSQL produce the same result.
Once you know its a piece of cake and very logical but it definitely does not show up in any manuals, at least none that I have seen.
Bye for now,
Filip
|
|
|
|
|
Hi I need a connection code to connect to my database and it will select and this play the result on a new page. Can anyone help me on that?.
|
|
|
|
|
You can start here[^].
Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman
|
|
|
|
|
Hi All,
Every time I do paging on database results, I feel wonder why there is no native commands to do paging on Sql queries. Pagination is a basic requirement to fetch data from large tables.
All I can do that to fetch entire data into a temporary result set like temporary table or CTE and apply paging into that result set. But why I have to fetch entire table to get 20 or 30 records? I know there is a better alternative for this is that to select only primary key fields instead of entire columns and use the primary keys to create the page. But still I have to select entire table at first.
Is there any better solution for this? Thanks in advance.
Regards,
Thomas
|
|
|
|
|
SSEAR wrote: Pagination is a basic requirement to fetch data from large tables.
No it isn't.
It is a "basic" requirement for GUIs. Which is no more relevant to a database than the color that you use to display the results.
SSEAR wrote: Is there any better solution for this?
The 'best' solution is to require your users to provide enough information that you don't need to page at all. Users do NOT use large lists in a random fashion. They know what they are looking for so make them tell you that.
If you are still left with pages then you must first start by defining what happens when user A goes to lunch and while gone user B adds something new into the list that user A was using. (Vast majority of time it doesn't matter but the business users must decide that.)
After that then you research the specific database to find out how to return a fixed size list out of a larger query. This is a 'page'. It involves determining how to select the next 'page'.
|
|
|
|
|
jschell wrote: It is a "basic" requirement for GUIs. Which is no more relevant to a database than the color that you use to display the results.
I agree. I was just worrying about the overkill of fetching entrie table. Think about TOP{no of records}. Can the pagination implement like this? Oky, I am not ignoring the additional overhead to calculate the 'current page' on Sql Server.
jschell wrote: The 'best' solution is to require your users to provide enough information that you don't need to page at all. Users do NOT use large lists in a random fashion. They know what they are looking for so make them tell you that.
This is not pratical in many situations. Consider a shopping web site which have a statergy is to force the customer to buy products even though they really dont require it. The customer may doesnt have idea what they want and like to surf through available items.
|
|
|
|
|
SSEAR wrote: This is not pratical in many situations.
Actually it is practical in most real situations.
SSEAR wrote: Consider a shopping web site which have a statergy is to force the customer to
buy products even though they really dont require it. The customer may doesnt
have idea what they want and like to surf through available items.
And is that exactly what you are implementing?
From those same sites - do they allow the customers an option to pick out which customer they are from a list of all customers?
|
|
|
|
|
As jschell pointed out paging is a purely presentation layer issue, databases deal with data not pages!
Having said that there are a number of hacks to get paging from the database, CTE is one you are familiar with there is also row_number() and top. These are all hacks to meet requests such as your, be thankfull you have some work around!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: be thankfull you have some work around
I thank god for not make me as a 'PLZ GV ME CODZ' programmer 
|
|
|
|
|
Hi,
Try below
If you want page index 1 (between 10-9 and 10)
page index 2 (between 20-9 and 20)
.
.
page index 25 (between 250-9 and 250)
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY OBJECT_ID) AS [PageIndex],* FROM sys.objects
) AS TB1
WHERE PageIndex BETWEEN 10-9 AND 10
|
|
|
|
|
i want to insert mathematical and chemical formula in SQL server database 2005 using xls file and want to retrive it on webpage using asp.netwith c#.iam able to insert other data into database but in place of chemical and mathematical formula its showing null and i am unable to retrive it also so please help me how to short out this problem
|
|
|
|
|
See here[^]; please do not crosspost.
Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman
|
|
|
|
|
pankaj3086 wrote: i want to insert mathematical and chemical formula in SQL server
database 2005 using xls file
That doesn't mean much.
It is referring to a file, not symbols.
If you are getting data from an xls file and importing it into SQL Server and then attempting to display it...then the FIRST step is to figure out if the import step is actually working by looking at the data that is in the database. And figure out if it correct in the database.
And attempting to display it (as characters) is NOT looking at the data.
|
|
|
|
|
in database only its showing null...
|
|
|
|
|
So either you are getting it wrong from the spreadsheet or you are inserting it wrong.
|
|
|
|
|
I'm not sure this is correct forum, but it is used in Access DB.
I have existing XML file with following nodes (I think that is correct term)
<subname>
<name>SOME TEXT NAME</name>
</subname></code>
I would like to know how to construct a form (ASP or HTML) where I could have user enter the 'SOME TEXT NAME' part and it would APPEND to end of XML document.
I have seen some scripts and code that got close, but I did not understand how to modify to my needs. Any help would be appreciated greatly!
Thanks
|
|
|
|
|
gdmoland wrote: I have seen some scripts and code that got close, but I did not understand how
to modify to my needs.
That is called a learning process and for that you need a book and possibly a teacher. This is a support forum where we help developers solve problems not teach how to develop applications.
Your lack of understanding cannot be solved by a forum post!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How can i connect SQL database with Vb6.0 application? 
|
|
|
|
|
|
That's assuming that I know what ADO is and how to use it.
|
|
|
|
|
Not really. I don't.
|
|
|
|
|
Ngobi Arthur wrote: How can i connect SQL database with Vb6.0 application?
By writing code. VB6 is antique, there should be some manuals in the library. If there aren't, there's always the online tutorials[^].
If you are looking for a language to learn, then go for VB.NET.
Bastard Programmer from Hell 
|
|
|
|
|
I'm trying to import an Access 2007 db using the SQl Sever Migration Assistant into a SQL Sever 2008 Express database. All the steps work fine in the migration assistant, and in the pane on the lower left I can even see the db in the target server, but when I open the SQL Sever Express db with SQL Sever Management Studio, the db is not there?
I can browse the following folder from windows and I can see the MDF file, but if I try to attach the db, I can browse as far as the DATA folder, but I cant open it to select the actual db.
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\simmstock.mdf
Also if I try to move the mdf file, Windows wont allow it because it is being used by SQL Server???
Solved: I had another instance of SQL server installed on the machine that I was not even aware of  (probably from a Visual Studio Installation) and I had migrated the Access database to the wrong server instance
modified 8-Apr-12 10:44am.
|
|
|
|
|
Is there a better way to get one row from a database. I have been using the Fill Method of the DataAdapter to fill a datatable, and then returning the first row of the table
Dim dr As DataRow
Using cn As New OleDb.OleDbConnection(cnStr)
Dim cmd As New OleDb.OleDbCommand(strSelect, cn)
Dim da As New OleDb.OleDbDataAdapter(cmd)
Try
cn.Open()
da.Fill(dt)
If dt.Rows.Count >= 1 Then
dr = dt.Rows(0)
End If
Catch ex As Exception
Finally
cn.Close()
dt = Nothing
End Try
End Using
Return dr
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
