|
Thanks for your anwer
It look like triggers are my other option
I was trying this inside a trigger, but it doesnt work as expected: When trying to modify, i get the Duplicate error even if i give a name that doesnt exist in the table at all
<pre>
IF EXISTS (SELECT * FROM Inserted AS I JOIN Dirs AS D ON I.DirID = D.DirID AND I.Name = D.Name)
BEGIN
ROLLBACK TRAN
RAISERROR('Duplicate',16,1)
END
</pre>
DirID is the primary key, and Name is a description for the given address
Thanks in advance for any feedback
Alexei Rodriguez
|
|
|
|
|
Is it a AFTER trigger or an Instead of trigger? You ought to use Intead of Triggers inside which you have to check if the name already exists and explicitly insert if it does not exist.
If the above trigger is an AFTER trigger, SQL Server will insert the row first, and then fire the trigger, in which case the inserted name will already be present and your EXISTS will return true.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi Guys,
Is there a way wherein I can write an MSSQL script with Distinct command which is good only for one field?
Example:
ITEM Table
ItemCode Description UnitCost
00000001 Center Table $500
00000001 Center &400
00000002 Chair &20
00000002 Chair white &20
Based on the example above, how can I select a records that the ItemCode will only display once?
Result should be like this:
ItemCode Description UnitCost
00000001 Center Table $500
00000002 Chair &20
I tried this SQY Query statement but it doesn't work:
Select Distinct(ItemCode), Description, UnitCost
From Item
Order By ItemCode
Thanks in advance Guys
hifiger2004
|
|
|
|
|
select distinct itemcode,<br />
(select top 1 description from table1 as i1 where i1.itemcode = table1.itemcode) as description,<br />
(select top 1 UnitCost from table1 as i1 where i1.itemcode = table1.itemcode)as UnitCost<br />
from table1
I Love SQL
|
|
|
|
|
Perfect! 
Thanks man
hifiger2004
|
|
|
|
|
my pleasure to help u...
I Love SQL
|
|
|
|
|
I am new to using sql and am stuck with a problem. I am trying to pass 2 parameters into an SQL stored procedure. If the 2 parameters exits I want to return all the information within that row to a datatable. The problem is after lookin at many tutorials online the errors keep coming up that my syntax is incorrect below is the stored procedure if anyone can help me it would be greatly appreciated.
CREATE PROCEDURE dbo.Authenticate_User
@Username varchar(50),
@Password varchar(50)
AS
IF EXISTS
BEGIN
(SELECT * FROM Users WHERE User_Username = @Username AND User_Password = @Password)
END
END IF
RETURN
the syntax erros are:
Incorrect Syntax near the keyword 'BEGIN'
Incorrect Syntax near the keyword 'IF'
|
|
|
|
|
EXISTS works like this:
<br />
IF EXISTS(SELECT * FROM Users WHERE User_Username = @Username AND User_Password = @Password) BEGIN<br />
SELECT * FROM Users WHERE User_Username = @Username AND User_Password = @Password<br />
END<br />
But there is no reason to use it in this case. Most often it is used like this to determine if an UPDATE (record exists) or an INSERT (does not exist) should be done.
If you just change your procedure to this:
<br />
CREATE PROCEDURE dbo.Authenticate_User<br />
@Username varchar(50),<br />
@Password varchar(50)<br />
AS<br />
<br />
SELECT * FROM Users WHERE User_Username = @Username AND User_Password = @Password<br />
<br />
Then you will exactly the same result as with the EXISTS, but you won't be wasting resources querying for the record twice.
|
|
|
|
|
As per subject,
I'm starting to code my database.
Is there a front end GUI app that can assist me with creating, editing and gettting a good view of my tables?
When I was in school, I used to use something called mySQL Enterprise manager, but that seems to be a tool for purchase only.
Suggestions on a free solution?
|
|
|
|
|
|
humblepgmr wrote: I'm starting to code my database.
Is there a front end GUI app that can assist me with creating, editing and gettting a good view of my tables?
When I was in school, I used to use something called mySQL Enterprise manager, but that seems to be a tool for purchase only.
Suggestions on a free solution?
I use a tool call SQLYog - there is a nag screen at startup and quit, but hey, its free
|
|
|
|
|
|
IMO this would be a bad idea. This would place your transaction (even if you're not doing an update) in a distributed context and distributed transactions are really expensive. I don't know if Sybase supports the same syntax, but SQL Server will allow you to insert the results of a stored procedure directly into a table like this:
<br />
INSERT INTO myTable(col1, col2, col3)<br />
EXEC myProc @param1, @param2<br />
This works if the table definition exactly matches the output of the stored proc. To accomplish this you could define a temp table, store the results of the stored proc in the temp table and then select the column you want from the temp table. Like this:
<br />
CREATE TABLE #myTable(col1, col2, col3)<br />
<br />
INSERT INTO myTable(col1, col2, col3)<br />
EXEC myProc @param1, @param2<br />
<br />
SELECT col1 FROM #myTable<br />
<br />
DROP TABLE #myTable<br />
You'll have to look up the syntax, as I don't know if Sybase supports it, but it would be vastly better than running a remote query.
|
|
|
|
|
Thanks but the select column there in your script... It's already done within stored proc but I'm calling the stored proc from an Excel addin developed by my company - wouldn't be able to consume the select.
Mark J. Miller wrote:
CREATE TABLE #myTable(col1, col2, col3)
INSERT INTO myTable(col1, col2, col3)
EXEC myProc @param1, @param2
SELECT col1 FROM #myTable
DROP TABLE #myTable
I also tried selecting @@identity after executing the stored proc, no luck.
Thanks
Norman
devy
|
|
|
|
|
We have set up replication between a SQL 2005 server and a number of laptops with SQL 2K5 Express installed.
On the laptops we use Windows Synchronization Manager to manage the synchronization.
Everything works fine. The only remaining problem is that we are not able to save the login data (username/password) for Subscriber, Publisher and so on. Because of the way our customer has set up their main SQL server we need to use SQL authentication (instead of the preferred Windows authentication).
In the current situation the user has to re-enter usernames and password each time.
Is there a solution for this?
Kind regards,
Brian Wakhutu
Nairobi Kenya
|
|
|
|
|
I'm in SQL 2000 running a query.
I am inserting a lot of record from 1 table to another. A record on the input table doesn't match the destination table format.
I'm getting the following.
Server: Msg 8152, Level 16, State 9, Line 106
String or binary data would be truncated.
The statement has been terminated.
How do I get Query analyzer to tell me which record from the source table is in causing my problem.
Should I Try...Catch? Not sure this works in SQL2000.
Any help welcome.
|
|
|
|
|
check fields which have datatype varchar and set length to 255 or size which you need.
I Love SQL
|
|
|
|
|
Hi thanks,
there are many fields VARCHAR and INT datetime and such, It may not be the Varchar that's the problem. I really need some SQL to allow me to trap the record in error and amend the record, not the table.
H.
|
|
|
|
|
I know of no error trapping (although there might be some low level undocumented stuff) for identifying the row within the set that caused the error. It might not even be very helpful if it is the first of 1 million errors within the set anyway.
Visually compare the sizes of the all varchar (and binary, if any) columns between the table and your input query. At least one of the columns of the input query has a column size that can be larger than is accepted by the table it is being input into.
You can then run a check on the query to find which rows are offending.
<code>SELECT
PrimaryKey,
FROM
WhatEverSource
WHERE
LEN(VarCharColumn) > 25</code>
You can also force the truncation during input which would eliminate the error but, lose some data.
|
|
|
|
|
That's great mike.....
I hate SQL for this reason, it's like something from the 1970's (**this will get them SQL fans going**  )
Why can it not say 'I cannot insert...' and then show me the row !! How simple would that be ??
I do some checking, thanks again...
|
|
|
|
|
You are thinking that SQL is a proceedural language. It is a set based language. It identifies that the set operation failed and the reason why. What seems easy on the surface may be extremely difficult to implement when the queries get highly convoluted. Example: Which row should the error system identify when the source set is composed of a 5 way join and 3 correlated subqueries?
On your side of the fence: I would like SQL to identify the column name that rejected the input data because it is to long.
|
|
|
|
|
I want to be able to click a button that changes all the datasources from selecting from one table to another. eg. If I click a button labeled "January", I want all datasources on my page to select all the same data, but from the January table. And if I click Feb, to select all the same data from the Feb table. All data is within the same database.
Is there a query that can do this or any piece of code that can change them all to select out of the table I need?
Thanks
|
|
|
|
|
Is there a reason why your data is in different tables per month? Would it make more sense to have a month id or some such on just one table as a foreign key?
|
|
|
|
|
ye it does make more sense to have it in one table... Figured its more efficient too... So I added it all into one table. should work out just as fine.
Thanks
|
|
|
|
|
Daniel_Logan wrote: ye it does make more sense to have it in one table... Figured its more efficient too... So I added it all into one table. should work out just as fine.
Did you normalise your database first and then perform denormalisation after carefully evaluating the impact?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
