Showing posts with label alternative. Show all posts
Showing posts with label alternative. Show all posts

Monday, March 19, 2012

Need an opinion: Access vs. SQL Server Express or another alternative?

I inherited this website from a previous developer that uses an Access DB as a backend. Ugh. Apparently, he was extremely limited in what databases he could use. The website is filled with problems related to connecting to this database, which is no surprise. Most of them are related to permissions to the database file, multiple user access, etc.

I am thinking about moving this to a SQL Server Express DB and upgrading the site to the 2.0 framework. Most of my experience is in SQL Server, but since they can't, or won't, pay for the SQL Server Database, is this a good solution? I've considered using MySQL as well, but their host won't provide support for this. Are there any other recommended alternatives?

I would go for SQL Express out of any of those alternatives... and I've done a lot of alternatives. SQL Express is actually very nice, and if you are already farmiliar with SQL Server, then a lot of the syntax will still make sense.

Actually,www.SingingEels.com was running on SQL Express for a while, but when I put it on my shared hosting environment, they have SQL Server 2005, and don't support SQL Express, so I moved it to that.

|||

I suggest that you move toSQL Server 2005 Express Edition which is completely free (though it has less features than the commercial full edition) and good to use for small-medium websites. It is a much better alternative than MS Access, besides its free for commercial use too.

Hope this helps,

Vivek

need an alternative for this simple query

I need to do something like this. how would i do this?
select * from t1
where c1=@.c1
and c2=isnull(@.c2,null)
but i know this wont work because if @.c2 is null it'll try to compare
'c2=null' which should never be true. so how would you do this? thank you.How about:
select * from t1
where c1=@.c1
and COALESCE(c2, '')=iCOALESCE(@.c2,'')
Stu|||wut needs to happen is this...
--when @.c2 is not null
select * from t1
where c1=@.c1
and c2=@.c2
--when @.c2 is null
select * from t1
where c1=@.c1
and c2 is null
How would i do this? I dont think coalesce does it...
sometimes i want to get a value where it's null.|||So in the first case: @.c2 is not null
THe optimizer runs the following query
select * from t1
where c1=@.c1
AND c2 = @.c2 -- if c2 is null, then compare an empty string to @.c2,
which won't match
In the second case, if @.c2 is NULL, then turn it inot an empty string.
You only want columns that are null in the database, and those are also
returned as an empty string. Emptry string=empty string,|||I dont have a problem writing 2 different queries. I need to know if I can
comine the 2 options in one query... thanks. I already have it working with
2
queries...|||try this..
select * from t1
where c1=@.c1
and isnull(c2,0) = isnull(@.c2,0)|||I'm sorry but this wont work also. I need to see if @.c2 is null or not.
if it is not null. I need to 'select * from t1 where c1=@.c1 and c2 = @.c2'
if it's null, I need to 'select * from t1 where c1=@.c1 and c2 is null'
there are null entries in the db and i can't change it. that's how it was
setup... i need to compare @.c2 to a varchar sometime and to NULL sometime...|||I'm sorry, I was trying to explain why the first query I submitted will
work, and obviuosly there is a misunderstanding. Test the query I
submitted the first time:
select * from t1
where c1=@.c1
and COALESCE(c2, '')=COALESCE(@.c2,'')
It will work; I do this often.
Omnibuzz's suggestion is essentially the same as mine, but the
performance will work best depending on the datatype of the column
involved; if a varchar, compare an empty string. If an integer,
compare 0 (assuming that 0 is not a valid value in your colum).|||try this then
select * from t1 where c1=@.c1 and
((c2 is null and @.c2 is null) or (c2 = @.c2))
"tpp" wrote:
> I'm sorry but this wont work also. I need to see if @.c2 is null or not.
> if it is not null. I need to 'select * from t1 where c1=@.c1 and c2 = @.c2'
> if it's null, I need to 'select * from t1 where c1=@.c1 and c2 is null'
> there are null entries in the db and i can't change it. that's how it was
> setup... i need to compare @.c2 to a varchar sometime and to NULL sometime...[/colo
r]|||Hi
create table #t (c1 int ,c2 int,c3 char(1))
insert into #t values (1,10,'a')
insert into #t values (1,null,'b')
select * from #t
declare @.c1 int,@.c2 int
set @.c1=1
set @.c2 =10
if @.c2 is null
select * from #t where c1=@.c1 and c2 is null
else
select * from #t where c1=@.c1 and c2=@.c2
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:F2516C09-2430-48F6-8FBD-2716B77AA640@.microsoft.com...
>I need to do something like this. how would i do this?
> select * from t1
> where c1=@.c1
> and c2=isnull(@.c2,null)
> but i know this wont work because if @.c2 is null it'll try to compare
> 'c2=null' which should never be true. so how would you do this? thank you.

Need Alternative

The following query is being run on SQL Server 2000, so I do not have the ability to use the OpenRowset (BULK ....) option. I am trying to modify this query so that it does not treat the first record as a header record:

select *
from OpenRowset
( 'MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=D:\aDir; HDR=NO; ' ,
' SELECT * FROM D:\aDir\Jj.TXT '
)

Is my best option to switch this over to a BULK INSERT operation? I am looking for options.

hmm do you have to option to change the contents of the .txt or .csv file?
are you just trying to insert the record to a table from that data source?|||No, I cannot change the contents of the .TXT file. Right now, the process processes the data into columns. The file has tilde characters for column separators but is also each field is fix length.|||do have other process after retrieving the data from the source like you have to update a record on the result set etc.?

or are you just going to insert the result set directly to the database? if yes, you can use BCP instead|||Yes, there is a whole army of processes that run after this process -- updates and selects but little or no deleting|||how bout contention issues? will your process run on multiple users and simultaneously?

imo, i would go with bcp or bulk insert to a table variable or temp table|||No, it is a batch process that runs single thread around 3AM when the server is otherwise quiet. I was looking at bulk insert but I want other opinions and I appreciate you working with me.|||

Kent,

This is a kludge, but ...

Create a file with a single row, dummy values.

Concatenate the data file to the 'header' file. Something like...

c:>\copy DummyHeader.txt /A + DataFile.txt /A NewDataFile.txt /A

|||

Thanks, Arnie. In fact, this is presently what I have done; however, since my input files can be a few gigs this is not my first choice, but indeed, it does work. :-)

|||nice idea arnie. i think that would work too. kent's problem now would lead to comparing performance issues beween bcp, bulk insert and openrowset|||

My other thought on the header issue was to get the mainframe job that creates the file to add a dummy header -- iff I can get the help.

[ :-) You are right: it IS PAINFUL! ]

( Now I gotta scrounge up another 10 GB of space so I can run the test. Can you say COMPRESS? )

|||

get the mainframe job that creates the file to add a dummy header

I feel your pain.

|||Kent,

Give the Jet driver a try. For Jet's text engine, whether or not the first row is treated as a header row depends on a registry entry. If you change it, you should have luck. (I don't recommend using xp_regwrite, but it's easier to post this T-SQL-only solution.)

Code Snippet

exec master..xp_regwrite
@.rootkey='HKEY_LOCAL_MACHINE',
@.key='SOFTWARE\Microsoft\Jet\4.0\Engines\Text',
@.value_name='FirstRowHasNames',
@.type='REG_BINARY',
@.value=0x00
go

select F1 as MyColumnName
from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'text; Database=C:\Data;' ,
'SELECT * FROM C:\Data\yourfile.txt'
)


Steve Kass
Drew University
http://www.stevekass.com

|||Excellent information Steve -THANKS!|||

Thanks Steve; I'll give it a roll.

Kent

( That seemed to work fine with all the small test files; not to try it with the large files. )

Well, it works, but it was ... [just caught a nut flush on the river].. significantly slower than the previous version. More testing is required.