|
Sorry, should have mentioned that if you have any null fields, you will get null results. If you concatenate to a null field, it makes the result null (no matter what is in the other fields). Are any of the fields not nullable? You can either...
A. Add a where clause to get the records where nullable fields are not null. i.e.
where OWNER_DIRECTION_2 is not null
The problem with this is you won't get records that you should get.
B. Use a case statement inside the query. This seems like a lot of typing, but it should work.
select
( case when OWNER_DIRECTION_2 is null then '' else OWNER_DIRECTION_2 end )
Do this for each field that is nullable. What this does is if the field is null, it returns an empty string (the ''), otherwise it returns the field's value.
To put it together...
select
(
case when OWNER_NUMBER is null then '' else OWNER_NUMBER end + ' ' +
case when OWNER_SUBNUMBER is null then '' else OWNERSUBNUMER end + ' ' +...
I'll let you finish it.
Good luck
|
|
|
|
|
Or even better: (From MS SQL2K BOL)
COALESCE
Returns the first nonnull expression among its arguments. Use like this:
<br />
SELECT COALESCE(OWNER_NUMBER, '') + ' ' + COALESCE(OWNER_SUBNUMBER, '') + ...<br />
I'll also let you finish it.
|
|
|
|
|
Probably a problem with some of the values beeing NULL. Any string that is added to NULL (and vice versa) will give the answer NULL, if not otherwise set up in either database options or connection specific options.
You can decide the behaviour of SQL Server when adding strings with NULL. The settings for this come in two forms. One for setting it for one specific connection:
SET CONCAT_NULL_YIELDS_NULL OFF
And one for setting the default for the database as a whole, and all connections to it (unless a connection specific setting reverses this):
EXEC sp_dboption 'some_database', 'concat null yields null', 'FALSE'
The two examples above, both set up SQL Server to treat NULL as an empty string when adding to other strings.
See MS SQL2K BOL for more info on this ("CONCAT NULL YIELDS NULL")...
|
|
|
|
|
Hi,
I'm trying to create an extension dll that references a
database via ADO,
using VC 6(SP 5). The problem comes when the application
is exited and
it prints the following error:
'Unhandled exception in <application> (MSDART.DLL)
0xC0000005: Access Violation'
This happened two times. Once when i try to
release 'Connection' or 'Recordet',
second after my dll's db's class destructor, if i comment
Releasing.
I was trying with three Mdacs. 2.7,2.7SP1, and 2.8. with 2
OSs. 2k(SP4) and XP(SP1)
but none of them resolved my problem.
I'm Sure that 'CoUninitialize' wasn't called before releasing, becouse
if I call "CoInitialize" ,before releasing, the return value is "S_FALSE", which mean "The COM library is already initialized on this apartment".
I'll be glad to see any ideas and pointers.
Best Regards:
KalliMan.
a
|
|
|
|
|
I'm sure there's something in the MSKB about this or related issues; something to do with what you can and can't call from the DLL termination function. It sounds like a dependency problem, trying to unload yours after MSDART unloads or something similar...
Steve S
|
|
|
|
|
Hi Steve,
Thanks for your advice, but after 3 days search on net I could not find anything which may help me. I found that I'm not the first with this problem, too.
see 'http://forums.devshed.com/t36005/s.html'. Same problem, but no answer.
I cannot unload "MSDART.dll" before mine, because it's a SYSTEM dll.I have no access. (I get only ACCESS Violation )) ). I just know its description: "Microsoft Data Access - OLE DB Runtime Routines". bla bla...
Has anybody some other Ideas?
a
|
|
|
|
|
In that case, I'd be tempted to get the XP symbols and get a proper callstack for this to see where it's occurring, like in your DllMain, your app's WinMain, or whatever.
I wasn't implying that YOU were unloading MSDART, just that it was being unloaded first.
As a hack you might try and add a method to your DLL that allows you to cleanup any or all of your objects before you unload the DLL (if you're unloading explicitly anyway, you can put a call in to do this at that point). That would help you tell if it's related.
Steve S
|
|
|
|
|
I thing, i found my solution.
Im my case the problem was, that I'm Using A Global DBManager's classes Variable,
from DLL which Load my DB DLL. Immediatly when I make it member in each class where used,
the problem DISAPPEAR,
Both DLL, are "implicted Linked"(i.e. export classes with (__declspec(dllimport))).
Strange, isn't it?
P.S. Avrything was Clear. Evrething was Released ... .
KalliMan.
a
|
|
|
|
|
Hi,
plz, help me with the following, (the code is attached)
Trying to insert, I have the following message:
(1 row(s) affected)
Server: Msg 8152, Level 16, State 9, Procedure Trig_PUD, Line 8
String or binary data would be truncated.
The statement has been terminated.
the Code is:
CREATE TABLE PersonalFile
(
IDnum int identity(1,1) not null,
PersonalFile varchar(10) not null
CONSTRAINT PK_PersonalFile
PRIMARY KEY
)
CREATE TABLE University
(
PersonalFile varchar(10) not null
CONSTRAINT PK_UniversityPF
PRIMARY KEY
REFERENCES PersonFile,
University varchar(50) not null
)
CREATE TABLE Department
(
PersonalFile varchar(10) not null
CONSTRAINT PK_DepartmentPF
PRIMARY KEY
REFERENCES PersonFile,
Department varchar(50) not null
)
CREATE VIEW PUD
AS
SELECT P.PersonalFile, U.University, D.Department
FROM PersonalFile P, University U, Department D
WHERE (P.PersonalFile = U.PersonalFile) and (P.PersonalFile = D.PersonalFile)
CREATE TRIGGER Trig_PUD ON PUD
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO PersonalFile(PersonalFile)
SELECT PersonalFile
FROM inserted
INSERT INTO University(PersonalFile,University)
SELECT University, PersonalFile
FROM inserted
INSERT INTO Department(PersonalFile, Department)
SELECT PersonalFile, Department
FROM inserted
END
insert into PUD(PersonalFile, University, Department)
values('123445', 'Univerdity1', 'Department1')
Best Regards,
exceter.
|
|
|
|
|
I found what the mistake was myself.
thanx anyway.
I hope you will answer my future questions.
Best Regards
exceter.
|
|
|
|
|
what is print in sql server 2000?
I have the ff. code:
declare @CurrentDate datetime
set @CurrentDate = 2003/7/11
print @CurrentDate
The query returns:
Jan 27 1900 12:00AM
-----------------------------------------------
Is this returned value the same as the ones returned by a select statement?
What is this basically used for?
"To teach is to learn twice"
|
|
|
|
|
Under SQL Server 2000, is it possible to review the transaction log for a specific database? If so, how?
Semper Fidelis
|
|
|
|
|
On http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocDBCC.htm[^] I found this:
8. DBCC log
This command is used to view the transaction log for the specified database.
Syntax:
DBCC log ( {dbid|dbname}, [, type={-1|0|1|2|3|4}] )
PARAMETERS:
Dbid or dbname - Enter either the dbid or the name of the database
in question.
type - is the type of output:
0 - minimum information (operation, context, transaction id)
1 - more information (plus flags, tags, row length, description)
2 - very detailed information (plus object name, index name,
page id, slot id)
3 - full information about each operation
4 - full information about each operation plus hexadecimal dump
of the current transaction log's row.
-1 - full information about each operation plus hexadecimal dump
of the current transaction log's row, plus Checkpoint Begin,
DB Version, Max XDESID
by default type = 0
To view the transaction log for the master database, run the following command:
DBCC log (master)
Also, there are some third-party tools that will let you look into, and manipulate db-logs...
|
|
|
|
|
This is what I needed.
Thank you for your help.
Semper Fidelis
|
|
|
|
|
Hi all,
I am working on a tool to execute sql files(something like Query Analyzer) using ADO.Net & C#. I try to add a function which can cancel any executing queries. To achieve this, I think I need to use multi-thread. I want to create a thread to execute the sql, meanwhile, if user click the cancel button, then create another thread to stop it. I believe it can work, but because I am a very beginner in these(ADO.Net, C# & multi-thread ), I can not work it out now. Please help!
Thanks in advance!
yyf
|
|
|
|
|
|
HELP
Can anybody help me with a short and simple guide on how to create an access 2000 database and being able to update it using .Net.
If we can start with a simple address book that somebody can or know of a site with help.
Anything will be appreciated.
Thanks
|
|
|
|
|
Hey
How can i run a query which can be an insert/update query from one database referring another database.
For example
i need to insert a row in my database1.table1 from database2
thanks
Cheers,
Venkatraman Kalyanam
Bangalore - India
"Being Excellent is not a skill, it is an attitude"
Reality is an illusion caused by caffeine deficiency(one Microsoft Research scholor)
|
|
|
|
|
Is this- Two SQL Server databases on the same server instance
- Two SQL Server databases on two different server instances
- Not SQL Server at all?
If the first or second:
You can reference any object in SQL Server (table, view, function) by a full name: server.database.owner.object. The first three can be omitted when referencing an object in the same database as you're currently accessing; owner is typically only used to disambiguate where two users have an object with the same name, although functions require the owner part.
The simplest syntax for referring to an object in another database on the same server - say you wanted to access the titles table in the pubs database is pubs..titles (note the two dots, signifying that the owner of titles has been omitted).
If you want to refer to an object on another server, you need to set up a linked server, which you can do in Enterprise Manager, under Security > Linked Servers.
I believe Access has the concept of foreign tables.
|
|
|
|
|
Hi,
I'm retrieving the changes from a dataset and creating an XmlDataDocument
for transport to a web service, however any rows with a RowState of Deleted
don't get propagated to the XmlDataDocument...is there something that I'm missing?...
Any help would be appreciated as I'm slowly going out of my mind .
Regards
Kieron
|
|
|
|
|
Hi !!
does anyone know how to do an autonumbering index when creating a new table with SQL ?
Thanks !
Jerome
|
|
|
|
|
What SQL implementation are you using?
- Mike
|
|
|
|
|
It will be crossdatabase : Access, MySQL, SQL Server and Oracle (and maybe some others)....
|
|
|
|
|
As far as I know, every SQL implementation has a different way of making autoincrement columns, e.g., MySQL uses the AUTO_INCREMENT modifer, Oracle uses sequence objects, PostgreSQL uses the SERIAL type, SQL Server uses IDENTITY, etc.
- Mike
|
|
|
|
|
These may be a stupid questions, but I have not been able to figure it out except for having a SPs that are slightly different.
Is it possible in a MS-SQL Server T-SQL Stored Procedure to do something like this pseudo code:
SELECT SUM(Amount) FROM Sales
if @StoreID > 0
then WHERE StoreID = @StoreID
else WHERE StoreID > 0
And also something like this, where @StoreID is a comma delimited list of StoreIDs
SELECT SUM(Amount) FROM Sales
if CAST(@StoreID as integer) > 0
then WHERE StoreID = @StoreID
else WHERE StoreID IN (@StoreID)
Note: My SELECT statements are much (much) more complicated than this. These where minimized for clarity.
Maybe I guess the real question is "even if I can do this, should I"? I am trying to minimize the business logic and the number of different SPs I have.
Thanks in advance,
Konstantine
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
