|
hey thanks a lot for the reply and help. Its working fine. I just used replace where I was doing join. Something like this:
SELECT CONVERT(nvarchar(12), b.pexpiry_date, 101) as Expiry_Date FROM Table_A a
left join Table_B b with(NoLock)
on replace(a.pno,',','') = b.pnumber
Thanks again... 
|
|
|
|
|
Good, thats what I meant
Bob
Ashfield Consultants Ltd
|
|
|
|
|
DECLARE @value AS VARCHAR(255)<br />
<br />
SET @value='78,567,9'<br />
<br />
SELECT REPLACE(@value,',','')
I see you have already soultion but I hope you don't mind for my answer.
Regards.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Good Morning All
I have been Running a Stored Procedure from another Server and it Crashed,Am restoring my Application to a new Server. i have restored my DB's into that SQL Server and my SP's are there and my Application complains that the SP's were not Found. What i did i recompiled the SP's and try again from the Management Studio to run the Proc itself not from the App and it gave me the same Error. e.g here is what i did
Alter Procedure [sde].[PrcSelect_lock_Pro_View]
(
@Lis_key varchar(50)
)
as
SELECT Primary_ID,
Boundary_area,
Attrib_Code,
Lis_Key,
Func_Key,
Actual_Extent,
Improved_Value,
Site_Value,
Purchase_price,
Purchase_date,
prop_id,
confidence_score,
fin_ls_prediction,
new_lis_key,
new_func_key,
Tshwane_Prediction,
matchflag,
source,
flagtype,
Locked,
New,
Country
FROM Property_mass WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key)
AND (Locked IS NULL)
Order by Lis_key,Func_key
Update Property_Mass
set Locked = 1
WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key And Locked IS NULL)
When i run this code it execute fine , but when you run the PS and pass the Param then it says
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'PrcSelect_lock_Pro_View'
Even if i see the SP.
What could be a Problem.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Are you specifying the correct owner ([sde].) when you run the SP?
|
|
|
|
|
Thanks it worked.
i was supposed to use the sde user.
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi,
Iam facing a problem with Postgres 8.1.4 Windows native version.
I am writing a simple application that executes an update query in a postgres database.
This query execution will be called in a loop for 500 times.
After starting the execution of the exe, I am switching off the Postgres server machine (say abrupt shutdown of the machine happened).
In this scenario, my sample application hangs.
Application code snippet would be like the below.
DBObj.Open (NULL, FALSE, FALSE, ConnectionString);
SQL = "Update mytable set sno=1 where sno=1";
for(int n=0; n <500; n++)
{
Try
{
WriteLog(“Start”);
DBObj.ExecuteSQL (strSql);
WriteLog(“End”);
}
Catch Exceptions…{}
}
Application hangs in the ExecuteSql statement itself.
This problem is not happening with Postgres 7.2.4 that runs on top of Cygwin.
And also this problem is not happening if I shutdown the server alone from Services
Instead of machine shutdown.
Any help is appreciated.
Thanks in advance.
|
|
|
|
|
I would have thought that the executesql would return an error if the server dissapears!. Presumably it never gets into the catch exceptions area!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ya ,
The timeout is not happening any way to solve the problem?
|
|
|
|
|
So no error, no reaction, how does the app crash! I'm clutching at straws here it feels like stepping onto a non existent step!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No it doesn't crash the ExecuteSQL doesn't return and the appilcation hangs /.
|
|
|
|
|
krishnan, I'm now going to ignore this thread and hope it goes away, sorry I have no idea how you are going to deal with this problem.
Idea, does it show the same behaviour in the release and debug versions. Huh oh damm the straw got away...
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Do you have a settable property for CommandTimeout as in ADO.NET? Setting that might solve the problem.
If not, you may have to run a dummy query like Select GETDATE() before you run your actual query to check if the DB server is up and running.
|
|
|
|
|
Executing that query also invloves executesql right?
It will also hang in that statement 
|
|
|
|
|
My WinForms application runs with SQLite database and I am very satisfied with it. Now I need to add export/import feature to my application, so I need also data format for these export files.
First I thought that XML or just some binary format could work, but then I realized that I could use directly some small SQLite database file. The export database file could contain only a single table with export data, or it could even be a part of the main database with the same format.
Do you recommend to use directly a database file for exporting/importing of data? It would be probably easier for me to work with SQLite format, compared to another format. Users will not need to edit the export files in any way, and the data will be always imported only back to my application, possibly located on another machine.
Thanks,
Petr
|
|
|
|
|
You are not describing and Export/Import functionality as this implies you are exporting from your application for use elswhere. This requires an open format usually CSV for large volumes or XML if the volume is not large.
Your requirement of transferring data between 2 versions of YOUR app is a different animal altogether. Use the tool your are most familiar with. Also if it is a sQLLite db it is less open to the user, therefore safer.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sorry, I dind't point out that the export/import feature will serve only to transfer data between two copies of my app, so the SQLite format will be probably the best bet.
|
|
|
|
|
Actually you did point out the requirement, I was just being pedantic. SQLLite is by far the best solution for this requirement!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am starting a C# Winforms application where part of the main data entry form consists of seven comboboxes allowing the user to select a single item from each combobox, or leave the column blank if they so choose. I am representing the choices in the comboboxes as text, of course, but in my main table I will store the unique ID (int) that they choose.
I want the user to be able to maintain the lists that populate the seven comboboxes. What is the best database design for doing this? I have tried two different methodologies so far. First I created seven tables, one for each combobox, since each one has a different meta data type. The tables had three columns - ID, Name, and Description. The ID was the primary key that I related to the main table.
Then I thought it would be better to combine all seven lists into one "meta data" table by simply adding a TYPEID column to the table. That made designing the form to maintain the meta data lists a bit easier. However, when I try to relate my "metadata" table to my main table ON MULTIPLE COLUMNS AND enforce referential integrity I get the following error in the SQL Designer and the VS Dataset Designer:
" Introducing FOREIGN KEY constraint 'FK_docs_status' on table 'docs' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. "
What is the best design pattern? Should I maintain seven different "metadata" tables?
Thank you.
Elgin Rogers
modified on Sunday, August 10, 2008 9:15 AM
|
|
|
|
|
Hi,
In my opinion, your approach with only single "metadata" is better that seven different ones. What about the foreign keys? From your error message I understand that Status is a "metadata" table and docs is an actual data table.
How does the Docs refers to Status? If the constraint is set to ON DELETE CASCADE, do you really want that when a status is deleted, docs which have the corresponding status are also deleted?
If the problem is with update (ON UPDATE CASCADE), I think you shouldn't use keys that are updatable, but instead use surrogate keys.
Also does the main table have seven foreign key columns, one for each choice user makes. This error also occurs if you use the same column for two different foreign keys.
Mika
|
|
|
|
|
Mike,
Sorry, I forgot to clarify the error messsage. Yes the "docs" table is my main table and the "status" is one type in my metadata table. Hence the FK_docs_status relationship name. Some examples of other metadata types/relationships are "dept" and the relationship is FK_docs_dept, or "classcode" relationship FK_docs_classcode.
I have a Name field that represents the choices for each metdata type. For example a status type may have three choices, "Pending", "Complete", and "Closed." Dept may have "Accouting", "Shipping", etc. You get the idea.
I am not allowing the user to modify the uique ID in the metadata table. However, when the user deletes one of the metadata "status" items or one of the "dept" items, I want to "Set Null" the status column or dept column respectively, in my docs table.
Does that clarify it?
|
|
|
|
|
Yes, that seems clear. This is a bit problematic situation in SQL Server, since it does not allow multiple modifying references between same tables. Only NO ACTION is allowed several times. For example:
CREATE TABLE Choice (
[Choice] int not null,
[Type] int,
[Name] varchar(50));
ALTER TABLE CHOICE ADD CONSTRAINT pk_Choice PRIMARY KEY ([Choice]);
CREATE TABLE Doc (
[Doc] int not null,
[Status] int,
[Dept] int);
ALTER TABLE Doc ADD CONSTRAINT pk_Doc PRIMARY KEY ([Doc]);
ALTER TABLE Doc ADD CONSTRAINT fk_Doc_Status FOREIGN KEY ([Status]) REFERENCES Choice ([Choice]) ON DELETE SET NULL;
ALTER TABLE Doc ADD CONSTRAINT fk_Doc_Dept FOREIGN KEY ([Dept]) REFERENCES Choice ([Choice]) ON DELETE SET NULL; -- This fill fail because of the limitation
ALTER TABLE Doc ADD CONSTRAINT fk_Doc_Dept FOREIGN KEY ([Dept]) REFERENCES Choice ([Choice]) ON DELETE NO ACTION; -- However since this is restricting, it will work
What I would suggest is that you use triggers to enforce this kind of referential integrity. Of course you can use declarative constraints on some of the columns, but not on all of them.
Hope this helps,
Mika
|
|
|
|
|
Mike,
Thanks for your suggestions. But I think I'm going to go back to seven separate tables. I can't see any advantage to keeping all the different metadata types in one table if I'm going to have to do extra steps like use triggers to enforce referential integrity. Why do all that if its built right into the designer.
Also, on several other forums others are telling me to go with seven tables in order to use the actions of "Set Null" on delete or Cascade updates, etc.
Right now I can't remember the exact reason I decided to go with one table instead of seven. But if I run into an issue with that design, I will post another question here.
Elgin
|
|
|
|
|
You're welcome. I believe that there's no right answer for this kind of question so you are most likely doing the right choice. Hope that the project goes smoothly!
Mika
|
|
|
|
|
I would recommend the 7 seperate tables approach. It is easier to manage your relationships. Reports and queries are simpler.
While I have never used the meta data approach I have seen a number of discussions here on the subject. Some get quite heated but I beleive the consensus is to retain the tables.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
