|
For a few thousand rows and freely distributable you may want to consider the size of the extra files you are distrubuting and how easy it is to have your installation file connect everything up on installation.
* SQL Server 2005 Express Edidtion is quite bulky, so you may not want that.
* SQLITE might also be appropriate for you - It is public domain and I think there are only one to two DLLs that you need to add to you to your install.
What I'd do is draw up a matrix containing all the database products you are looking at across the top and all the features you are interested in down the side. For example:
DB -> SqlServer MySQL Firebird SQLITE
/Feature Express
Redist.. Y ? ? Y
Install
Size Large ? ? 1-2 DLLs
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
-- modified at 3:58 Wednesday 17th May, 2006
|
|
|
|
|
Hi, folks!
I hope I can explain this properly...
I have two tables. One is a list of people's first and last names and there is an Identity column to uniquely select the proper row.
The other table is a list of email forwards; in other words I want to keep track of how a particular email is passed about with the group. This table has a column that uniquely id's the message, a column for the id of the forwarder and a column for the id of the forwardee. The id's come from the usernames table. So far, so good.
<br />
TABLE [Employees] (<br />
[idnum] [int] IDENTITY (1, 1) NOT NULL ,<br />
[firstname] [varchar] (50) NOT NULL ,<br />
[lastname] [varchar] (50) NOT NULL ,<br />
)<br />
<br />
TABLE [tblDownLogForwards] (<br />
[idnum] [int] IDENTITY (1, 1) NOT NULL ,<br />
[TicketNumber] [int] NOT NULL ,<br />
[IdForwardFrom] [int] NOT NULL ,<br />
[IdForwardTo] [int] NOT NULL ,<br />
[ForwardDate] [datetime] NOT NULL <br />
)<br />
Now, I want to report these forwards, but of course I want to make it easy to interpret so instead of the id's I want to include the user's first and last names, something like this:
<br />
msgNum - ForwarderFirstName - ForwarderLastName - ForwardeeFirstName - ForwardeeLastName<br />
How do I specify an SQL statement to replace the user id's with their names? I thought the following might do the trick, but it only selects records having the same id as forwarder and forwardee:
<br />
select ticketnumber, forwarddate, Employees.lastname as FromLast, Employees.firstname as FromFirst, Employees.lastname as ToLast, Employees.firstname as ToFirst from tblDownlogforwards inner join Employees on Employees.idnum = tblDownlogforwards.IdForwardFrom and Employees.idnum = tblDownlogforwards.IdForwardTo<br />
If I elliminate the last equality, I get records where the ForwardTo columns have the same person's name, even when another id is present.
I'm very perplexed by this...
Thanks!
'til next we type...
HAVE FUN!! -- Jesse
-- modified at 14:17 Tuesday 16th May, 2006
|
|
|
|
|
SELECT ticketnumber,forwarddate, FR.lastname AS FromLast,FR.FirstName as FromFirst, FT.lastname AS ToLast, FT.FirstName as ToFirst
FROM tblDownLogForwards INNER JOIN Employees FR ON tblDownLogForwards.IdForwardFrom = FR.idnum
INNER JOIN Employees FT ON tblDownLogForwards.IdForwardTo = FT.idnum
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Eric,
Bingo! Thanks a bunch!
BTW, love your .sig. A guy I worked with many years ago once wore a tee-shirt which said on the back:
"I must hurry and catch up with the others, for I am their leader!" I never knew where it came from. Thanks!
'til next we type...
HAVE FUN!! -- Jesse
|
|
|
|
|
I have one stored procedure, and it having UPDATE and INSERT statements, The INSERT / UPDATE queries are inside a Begin transaction, if the transaction fails then rollback the transaction otherwise commit it.
If the error occur rollback the transaction as well as i want to catch the exception and throwuh it to the user in the DA Class
I am calling the stored peocedure in the DA Class
please tell me any suggestions
Awaiting for reply
thanks
-
|
|
|
|
|
IF @@ERROR <> 0
ROLLBACK
RAISEERROR...
ELSE
COMMIT
|
|
|
|
|
Or don't have the transaction in teh sproc, but handle it through the DA class:
SqlTransaction tran = new SqlTransaction(conn);
try
{
... call sproc
tran.Commit();
}
catch (SqlException sqlEx)
{
tran.Rollback();
}
|
|
|
|
|
Be careful with this approach. Some Transactions may still get automatically rolled back, which will cause the rollback statement in the code to throw another exception.
Typically, SQL server rolls back transactions when a severe error or a connection loss occurs. This can be handled by issuing the rollback transaction in a nested try catch, and handling the error if the transaction was already rolled back.
We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene
|
|
|
|
|
how can i return 0 (false) for an error to insert data in SQL database. i am using ASP.Net (using vb.net) to call the stored procedure....
my procedure is...
CREATE PROCEDURE Insert_Book (
@arg_Title VARCHAR(255),
@arg_Price VARCHAR,
@arg_PublishDate DATETIME,
@arg_BookID INT OUT)
AS
DECLARE @rc Int
BEGIN
INSERT INTO
Book (
Title,
Price,
PublishDate )
VALUES (
@arg_Title,
@arg_Price,
@arg_PublishDate );
/*SELECT @arg_BookID = @@IDENTITY;*/
IF @@ERROR <> 0
BEGIN
/*SELECT @rc = 0*/
/*Print convert(Varchar,"An error occurred while adding the new Book information")*/
RETURN (99)
END
ELSE
BEGIN
/*SELECT @rc =99*/
/*PRINT Convert(Varchar,"The new author information has been loaded")*/
RETURN (0)
END
procedure call is...
con = New clarion.connection
Conn = New SqlClient.SqlConnection
Conn.ConnectionString = con.GetConnectionString()
Dim cmdtxt As SqlClient.SqlParameter
Dim objErr As SqlClient.SqlException
Dim txtAbbreviation As String
Dim txtReturnCode As String
Conn.Open()
Dim cmd As New SqlClient.SqlCommand("Insert_Book", Conn)
cmd.CommandType = CommandType.StoredProcedure
cmdtxt = cmd.Parameters.Add("@arg_Title", "Nisha")
cmdtxt.Direction = ParameterDirection.Input
cmdtxt = cmd.Parameters.Add("@arg_Price", "")
cmdtxt.Direction = ParameterDirection.Input
cmdtxt = cmd.Parameters.Add("@arg_PublishDate", "01/05/06")
cmdtxt.Direction = ParameterDirection.Input
cmdtxt = cmd.Parameters.Add("@arg_BookID", "")
cmdtxt.Direction = ParameterDirection.Input
cmdtxt = cmd.Parameters.Add("@@Error", SqlDbType.Int)
cmdtxt.Direction = ParameterDirection.ReturnValue
cmd.ExecuteNonQuery()
Conn.Close()
If Convert.ToInt32(cmd.Parameters.Item("@@error").Value) <> 0 Then
txtAbbreviation = Convert.ToString(cmd.Parameters.Item("RC").Value)
txtReturnCode = Convert.ToString(cmd.Parameters.Item("@@error").Value)
Else
txtAbbreviation = Convert.ToString(cmd.Parameters.Item("@arg_Price").Value)
txtReturnCode = Convert.ToString(cmd.Parameters.Item("@@error").Value)
End If
Dinesh Sharma
Software Engineer
|
|
|
|
|
look up ExecuteScaler
object retValue = [SqlCommand].ExecuteScalar() ;
|
|
|
|
|
How do I access Query Designer from Microsoft SQL Server Management Studio? I mean the equivalent in SQL Server Enterprise Manager of
Table -> Open Table -> Query
I just can't find the equivalent from the menus.
Also how to access DTS?
Probably all very obvious!
Thanks
Majella
|
|
|
|
|
Just open a table and then click on one of the toolbar buttons on the lower toolbar that appears. You have diagram, sql and criteria buttons.
Kevin
|
|
|
|
|
I have this script here. But I need to redo it as a sub Query can someone help me?
SELECT
fp.flt_numb,
a.name as 'destination airport',
a.cty_iata_code,
b.name as 'possible landing airport'
FROM flight_profiles fp
JOIN airports a
ON a.iata_code = fp.to
JOIN airports b
ON a.cty_iata_code = b.cty_iata_code
ORDER BY fp.flt_numb;
|
|
|
|
|
|
I need to re-write this as a sub query:
Select
f.FLT_NUMB as 'Flight Number',
b.Name as 'Arrive Airpots',
a.Name as 'Airports'
from airports a
join flight_profiles f
join airports b
on a.cty_iata_code = f.to and b.iata_code = f.to
group by a.Name
order by f.flt_numb
|
|
|
|
|
I got re-wrote like this
select
d.flight_no,
d.destination,
a.Name as "Alternative Airort"
from airports a,
((SELECT b.FLIGHT_NUMBER FLIGHT_NO,
b.DESTINATION DESTINATION,
a1.cty_IATA_CODE AS CTY_CODE,
a1.name as airport_names
FROM AIRPORTS a1,
((SELECT FLT_NUMB FLIGhT_NUMBER,
F.TO DESTINATION FROM FLIGHT_PROFILES F) AS b)
WHERE b.DESTINATION=a1.IATA_CODE)as D)
where a.cty_iata_code=d.cty_code
order by d.flight_no
and its work fine.
|
|
|
|
|
Hi,
well i think i'm so stupide to ask that but i really tried ma besqt with it & it doesn't work
i created a stored procedure in this procedure i have 1 parameter type datetime
& i want to select * from a table where day(this table.dat_start)/month(dat_start)/year(the parameter) = the parameter
i will make it more clear
supposing parameter is '02/05/2006'
(select * from season where day(dat_start)/month(dat_start)/year('02/05/2006') = '02/05/2006')
it dosen't want to of course because the two types are defferent but i did a convert & it dosen't ant too
(select * from season where convert(datetime,day(dat_start)/month(dat_start)/year('02/05/2006')) = '02/05/2006')
& i did a cast & no result
(select * from season where cast(day(dat_start)/month(dat_start)/year('02/05/2006')as datetime) = '02/05/2006')
well i talked a lot i think i hope you understand please if you have the solution can you help, me ?
thank you very much
try to be good if you can't be the best
|
|
|
|
|
create Proc p_date (@dateS varchar(111))
as
Begin
select * from season where Convert(varchar,dat_start,111)=@dateS
end
if u have problem with this proc just mail me
moinul@apollodhaka.com
Moinul
Sr.Executive IT
Apollo Hospitals Dhaka
|
|
|
|
|
I need to run my program on my client machine.
I had developed my program on my machine and created the DB on my machine.
Now I need to transfer the db and my EXE file on to another machine
The server name on my machine shows
Running - \\Rch1 - MSSQLServer
But the server name on the second machine
Running - \\Krnot - MSSQLServer
How do I replace the servername.
My code;
Public conn As New OleDbConnection("Provider=sqloledb;server=Rch1;Database=Rch;Trusted_Connection=YES;")
Tnx
|
|
|
|
|
You should always put that kind of information in the app.cofig file. That way it can be changed when the application is moved around. Hardcoding this information into the app is just going to cause problems (as you have seen)
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Pls Could you give me some more info on this.
Tnx
|
|
|
|
|
|
What is the best way of distributing an empty (of transactional data) database in MS_SQL2k?
I have a database which has gone through system test and want to use this as the UAT baseline database - maybe do a backup and restore as a new database..or use tha database copy wizard?
Any other options?
'--8<------------------------
Ex Datis:
Duncan Jones
Merrion Computing Ltd
|
|
|
|
|
Duncan Edwards Jones wrote: I have a database which has gone through system test and want to use this as the UAT baseline database - maybe do a backup and restore as a new database..or use tha database copy wizard?
I have some NUnit tests that restore a database in the TestFixtureSetUp method. It works very well - although sometimes it can be a little slow, so I have a set of delete scripts in reserve if I'm debugging though the unit tests and I need a quick reset between runs.
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
-- modified at 9:01 Monday 15th May, 2006
|
|
|
|
|
You can use SQL Server Enterprise Manager to Generate Scripts of your entire database.
Right Click db -> All Tasks -> Generate SQL Script...
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
