Best program for SQL database manipulation

Hello All

I am a relative beginner to SQL databases & new to this forum, so please bear with me if my query is too basic and advise if this question belongs somewhere else

I began working at a company that uses a program that stores data in an SQL database running off a Firebird engine

The program itself doesnt come with database management/administration module, so I'll need to use an external program for manipulating data in the tables that the database contains.

I have relatively little knowledge in SQL programming, which is why I would like to know which is the most powerful program for SQL database updating / manipulation?

This database has tables that has an infinite number of joins with other tables - Even MS Access wasnt able to open a few tables in this database because of the number of joins. I have tried Access & Lotus Approach, Approach manages to do a better job than Access, it open the tables & seems like it will let me import external data directly into the SQL table, but takes forever & usually just bums out giving an error after a very long wait..

My question is - Apart from Access & Approach, are there any more powerful, yet user friendly programs out there that can help me update data directly into SQL tables? What options do I have - the tasks I need to perform are pretty simple updating & cleaning of data already in there

Please, I will hugely appreciate any pointers that you guys the experts might have for me in this regard

Thanks
AlexTry Microsoft SQL Server 2005 Express. It's Free & Downloadable From http://msdn.microsoft.com/vstudio/express/sql/sql

Best Practice Analyzer & Custom Best Practices

Hi All,
The BPA is a great tool that can be used to audit an environment for
adherenace to proper configuration and basic T-SQL coding best practices.
Does anyone know if the BPA tool can be customized to include custom
best-practice rules that are in-addition to the standard best practices
included with the tool? For example, say for example if an orgainzation
would like to add custom rules that look for the existance of specific
UserID, specific SQLAgent Jobs, etc.
Thanks in advance for your assistance.
Dave
This is a feature request that the SQL team are well aware of but as you can
imagine with SQL2005 nearing the end of a long road (near being a relative
term!) it might be a while before a version of SQLBPA is released that
supports user defined rules.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"DBADave" <DBADave@.discussions.microsoft.com> wrote in message
news:405E1559-6CB7-4371-822B-CE4C523A5EF3@.microsoft.com...
> Hi All,
> The BPA is a great tool that can be used to audit an environment for
> adherenace to proper configuration and basic T-SQL coding best practices.
> Does anyone know if the BPA tool can be customized to include custom
> best-practice rules that are in-addition to the standard best practices
> included with the tool? For example, say for example if an orgainzation
> would like to add custom rules that look for the existance of specific
> UserID, specific SQLAgent Jobs, etc.
> Thanks in advance for your assistance.
> Dave

Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
it
inside
>
>.
>

Behavioural difference between ALTER and UPDATE

Hi,
This could be a basic question. But still I want to get it
clarified.
Normally if an ALTER or UPDATE command been tried with a
non existing column, SQL Server throws error. To avoid
this, the practice is to place the alter statements inside
an IF EXISTS() block. Only if the column exists/not
exists, the alter would get executed.
I tried using the same technique for an UPDATE. I still
get the error inspite I have an exists() check.
In the below given example, The statement1 (ALTER) does
not throw any error while the statement2(Update)
throws error.
Could somebody please explain the reason behind this?
Regards,
J.P. Job
Eg:.
USE PUBS
go
--Statement1
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
ALTER TABLE AUTHORS DROP COLUMN DUMMY
go
--Statement2
If Exists(select * from information_schema.columns WHERE
TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
Update AUTHORS
SET DUMMY = 'TEST'
goSQL Server compiles the whole batch before executing it. As part of the
compilation process it looks for the objects it is going to access, to
calculate the optimal way to access these objects, using indexes etc. In
other words, all the code will be compiled before it is actually executed,
and not, as it works in script languages, only when it will be executed. And
when the code gets compiled, the update statement needs to have information
about the dummy column, which isn't there, so it errors.
--
Jacco Schalkwijk
SQL Server MVP
"JPJOB" <anonymous@.discussions.microsoft.com> wrote in message
news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
> Hi,
> This could be a basic question. But still I want to get it
> clarified.
> Normally if an ALTER or UPDATE command been tried with a
> non existing column, SQL Server throws error. To avoid
> this, the practice is to place the alter statements inside
> an IF EXISTS() block. Only if the column exists/not
> exists, the alter would get executed.
> I tried using the same technique for an UPDATE. I still
> get the error inspite I have an exists() check.
> In the below given example, The statement1 (ALTER) does
> not throw any error while the statement2(Update)
> throws error.
> Could somebody please explain the reason behind this?
> Regards,
> J.P. Job
>
> Eg:.
> USE PUBS
> go
> --Statement1
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> ALTER TABLE AUTHORS DROP COLUMN DUMMY
> go
> --Statement2
> If Exists(select * from information_schema.columns WHERE
> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
> Update AUTHORS
> SET DUMMY = 'TEST'
> go|||Hi Jacco,
Why the error is not been thrown in the case of ALTER.
Will it not compile ALTER statements before executing?
Regards,
JP. JOB
>--Original Message--
>SQL Server compiles the whole batch before executing it.
As part of the
>compilation process it looks for the objects it is going
to access, to
>calculate the optimal way to access these objects, using
indexes etc. In
>other words, all the code will be compiled before it is
actually executed,
>and not, as it works in script languages, only when it
will be executed. And
>when the code gets compiled, the update statement needs
to have information
>about the dummy column, which isn't there, so it errors.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"JPJOB" <anonymous@.discussions.microsoft.com> wrote in
message
>news:d4b601c3efc3$96dd1bc0$a301280a@.phx.gbl...
>> Hi,
>> This could be a basic question. But still I want to get
it
>> clarified.
>> Normally if an ALTER or UPDATE command been tried with a
>> non existing column, SQL Server throws error. To avoid
>> this, the practice is to place the alter statements
inside
>> an IF EXISTS() block. Only if the column exists/not
>> exists, the alter would get executed.
>> I tried using the same technique for an UPDATE. I still
>> get the error inspite I have an exists() check.
>> In the below given example, The statement1 (ALTER) does
>> not throw any error while the statement2(Update)
>> throws error.
>> Could somebody please explain the reason behind this?
>> Regards,
>> J.P. Job
>>
>> Eg:.
>> USE PUBS
>> go
>> --Statement1
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> ALTER TABLE AUTHORS DROP COLUMN DUMMY
>> go
>> --Statement2
>> If Exists(select * from information_schema.columns WHERE
>> TABLE_NAME = 'AUTHORS' AND COLUMN_NAME = 'DUMMY')
>> Update AUTHORS
>> SET DUMMY = 'TEST'
>> go
>
>.
>

beginning SQL

Hello, I would like to be able to construct a small SQL data base with Visual Basic.Net code for practice. I want to be able to add to the data base, subtract from the data base, perform all Sql procedures ( Select, delete, etc.) I would also like to include the ExecuteNonQuery, ExecuteScalar, and ExecuteReader methods. Can anybody out there recommend a good tutorial for me? Thank you so much. MarcAbelson_2000@.yahoo.com

These resources should be useful for your learning:

http://www.asp.net/learn/

http://www.asp.net/learn/data-access/

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx

http://msdn.microsoft.com/vstudio/express/sql/starterkit/default.aspx

beginner setting up to code in VB

Hey everyone. I'm trying to set up the free microsoft sql 2000 server desktop engine so I can create databases in visual basic using ADO. I would like to do this locally, not over the network or anything. I'm on windows 98. the steps I took are:

1. I downloaded the ms 2000 server desktop engine (msde2000a.exe)
2. Downloaded the .net framework and installed that (version 1.1)
3. Extracted the desktop engine setup to a directory
4. browsed to that directory in DOS prompt and typed 'setup SAPWD="AStrongSAPwd" ... and then it installed
5. opened ODBC Data sources in the control panel
6. in the user DSN tab i highlighted "MQIS" and then pressed 'configure'
7. Pressed 'next' with the default values for all the fields: (name=MQIS, Description=SQL Server' Server=(local) )
8. with the radio button "with sql server authentication.." selected i click on 'client configuration'
9. after i click on that a box comes up saying 'add network library configuration'.

so.. i'm confused on this part.. What should i put for 'server alias' and 'server name'? I want to do this locally. I tried putting my local IP address and the word (local) but after i press 'OK' and then 'Next' (in that previous window that displays the client configuration button) i get an error that says:

Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect())
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied

So.. how do I set up my computer to be able to use the engine locally? I have the vbcode I want to work. It consists of creating a database using the "provider=SQLOLEDB..." line. I can submit the code if anyone wants to see. Any help would be greatly appreciated. Thanks!By the way, i DO NOT have SQL Server installed. I was thinking the engine would sort of be like MS Access where you don't actually need Microsoft Access installed to do database manipulation because the Access Jet Engine is built into windows. So I figured installing the SQL Server engine would do the same thing for me. Is this correct? Or is it mandatory that I have SQL Server installed?|||You will definitely need to have SQL Server installed, it won't ship as part of the OS until LongHorn. You can buy the developer edition for about $50, and you get MSDE (the engine alone, like Jet is to MS-Access) included with any of Microsoft's Professional level developer products.

-PatP