Is there a sql database function to check connection string?

Im working on a db library and I have everything working, what Im wanting to do is though is add a method that can check a connection string to make sure it is actually working. Right now, I have it doing a simple select * query to a particular table and returning true or false if an exception is caused. But I want to make the library as generic as possible, so is there another way to test teh connection string and tell if its working or not?
Thanks,
CedricHmm. Well you could try something like SELECT GETDATE() or SELECT @.@.VERSION, which are not database schema-specific.

is the forum working?

is the forum working?yes the forum is working quite nicely.
Greg Jackson
PDX, Oregon|||At the time your reading this - No
J
;)
>--Original Message--
>
>.
>

is the forum working?

is the forum working?yes the forum is working quite nicely.
Greg Jackson
PDX, Oregon

Is primar key/foreign key evil in SQL Server?

I worked with Oracle/Sybase before, and I used PK/FK quite often, I had PK
for every table.
Since I started working in SQL Server, I saw serveral big applications (over
2000 concurrent users) don't use PK at all, they just use clustered unique
index and there's no foreign keys either, the integrities are enforced by
triggers.
Can anybody shed some light on me?It seems you've been to a lot of really bad places. :)
I've seen some pretty lousy models myself - that's database models (not
super models). They were in production and had all the problems one would
expect (and more). But I'm pretty sure the ones I've seen would be just as
bad on any other platform (if the same people were involved in designing
them).
ML
http://milambda.blogspot.com/|||Where is Joe?
This should be your favorite topic.
"Matthew" wrote:

> I worked with Oracle/Sybase before, and I used PK/FK quite often, I had PK
> for every table.
> Since I started working in SQL Server, I saw serveral big applications (ov
er
> 2000 concurrent users) don't use PK at all, they just use clustered unique
> index and there's no foreign keys either, the integrities are enforced by
> triggers.
> Can anybody shed some light on me?|||> Since I started working in SQL Server, I saw serveral big applications
> (over
> 2000 concurrent users) don't use PK at all, they just use clustered unique
> index and there's no foreign keys either, the integrities are enforced by
> triggers.
I've seen this too. Fortunately, bad design is not restricted to a single
platform. Your experiences are indicative of you confronting environments
with bad designers, not bad database platforms. This could easily have been
Oracle, DB2, Access, dBase IV, mySQL, etc.
A|||If the same developers have created an Oracle database, then I would expect
to see the same poor implementation there as well. This type of design is
typical when the database is implemented by application developers. They
consider referential integrity constraints to be too rigid and blame them
for making their applications blow up. What they are probably doing is
inserting invalid data into the system and then re-coding it later in some
batch process or at the reporting level.
"Matthew" <Matthew@.discussions.microsoft.com> wrote in message
news:FEF63455-FF98-4AA2-B2B6-C8C0D0FC82F9@.microsoft.com...
>I worked with Oracle/Sybase before, and I used PK/FK quite often, I had PK
> for every table.
> Since I started working in SQL Server, I saw serveral big applications
> (over
> 2000 concurrent users) don't use PK at all, they just use clustered unique
> index and there's no foreign keys either, the integrities are enforced by
> triggers.
> Can anybody shed some light on me?|||Yes, Aaron,
You're absolutely right. Now I know it's the problem from the designers.
I have seen three big SQL Server systems having the same problem, so I
thought it's database specific.
"Aaron Bertrand [SQL Server MVP]" wrote:

> I've seen this too. Fortunately, bad design is not restricted to a single
> platform. Your experiences are indicative of you confronting environments
> with bad designers, not bad database platforms. This could easily have be
en
> Oracle, DB2, Access, dBase IV, mySQL, etc.
> A
>
>|||One day, a law will be passed mandating that only certified database
engineers (preferably Microsoft certified) be allowed to implement a
production database. Until then, we must tolerate these shade tree DBAs.
I'll bet their butt cleavage shows when they bend over to reboot the server.
;-P
"Matthew" <Matthew@.discussions.microsoft.com> wrote in message
news:321963C4-6FE0-4D64-B183-4A75F236B16C@.microsoft.com...
> Yes, Aaron,
> You're absolutely right. Now I know it's the problem from the designers.
> I have seen three big SQL Server systems having the same problem, so I
> thought it's database specific.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Yeah, thanks a lot for THAT mental image.
"JT" <someone@.microsoft.com> wrote in message
news:u%23zfILSTGHA.5440@.TK2MSFTNGP10.phx.gbl...
> One day, a law will be passed mandating that only certified database
> engineers (preferably Microsoft certified) be allowed to implement a
> production database. Until then, we must tolerate these shade tree DBAs.
> I'll bet their butt cleavage shows when they bend over to reboot the
> server. ;-P|||While no longer a developer, I was one and I designed and implements
many a production database. I am proud to say, that for the most part,
if the database server could do it, I tried to make use of it and that
included maintaining referential integrity. Two reasons: 1) The DBMS
was a tried and true method for maintaining referential integrity and
2) why would I want to write a bunch of validation code when all I need
to do is setup the proper PKs, FKs and relationships, and then trap an
error message should bad data attempt to get in. I subscribe to the
theory that never letting bad data in is always better than trying to
figure how to deal with it once it got in. Not all developers are
neanderthal database designers. Its the ones who are that give the rest
of us a bad name.|||> Not all developers are
> neanderthal database designers.
I never said that. Though I do believe there is substantial value in
separating the tasks, if and when you can.
A

IS NULL not working

Hi I am trying to do this on a temp table
DELETE FROM #output where [tst1] is NULL
this for some reason doesnt seem to work.
on the other hand when i do the below
insert into db_comp
select tst1, tst2,tst3 from #output
where tst1 is not null
seems to work. does anyone know why this is happening.
regards,
njPlease elaborate on "doesn't seem to work". Are there errors? Are you certai
n
there are rows in the table that correspond to the criteria in the WHERE
clause?
ML
http://milambda.blogspot.com/|||NJ skrev:

> Hi I am trying to do this on a temp table
> DELETE FROM #output where [tst1] is NULL
> this for some reason doesnt seem to work.
> on the other hand when i do the below
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
> seems to work. does anyone know why this is happening.
> regards,
> nj
As ML wrote, you need to explain how the above does not work...
To me it looks consistent with #output having no rows where tst1 is
NULL (if by 'not working' you mean the first statement didn't delete
anything)...
/impslayer, aka Birger Johansson|||Here you are using IS NULL
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"NJ" <npaulus@.hotmail.com> wrote in message
news:1147766869.501289.38100@.g10g2000cwb.googlegroups.com...
> Hi I am trying to do this on a temp table
> DELETE FROM #output where [tst1] is NULL
> this for some reason doesnt seem to work.
> on the other hand when i do the below
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
> seems to work. does anyone know why this is happening.
> regards,
> nj
>|||Here you are using IS NULL

> DELETE FROM #output where [tst1] is NULL
Here you are using IS NOT NULL

> select tst1, tst2,tst3 from #output
> where tst1 is not null
They are mutually exclusive.
If SELECT * FROM #output WHERE [tst1] IS NULL returns no rows then it won't
delete any.
Sorry about the part post on the other answer, I hit enter and something
else which so happens to be the short cut to posting the post!
Tony/.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"NJ" <npaulus@.hotmail.com> wrote in message
news:1147766869.501289.38100@.g10g2000cwb.googlegroups.com...
> Hi I am trying to do this on a temp table
> DELETE FROM #output where [tst1] is NULL
> this for some reason doesnt seem to work.
> on the other hand when i do the below
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
> seems to work. does anyone know why this is happening.
> regards,
> nj
>|||Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> Sorry about the part post on the other answer, I hit enter and something
> else which so happens to be the short cut to posting the post!
CTRL-Enter is a shortcut for Send in Outlook, and I believe in Outlook
Express (that I see that you are using).
Now, is that a really stupid keyboard shortcut or what!
Myself, I'm go to press F8 now. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I know Erland, dam stupid.
I seem to do it every now and again, but after doing it once i always do it
again - so, expect some half posts from me today...
tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97C57FAA43A96Yazorman@.127.0.0.1...
> Tony Rogerson (tonyrogerson@.sqlserverfaq.com) writes:
> CTRL-Enter is a shortcut for Send in Outlook, and I believe in Outlook
> Express (that I see that you are using).
> Now, is that a really stupid keyboard shortcut or what!
> Myself, I'm go to press F8 now. :-)
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||The first statement the delete statement doesnt delete anything. let me
put this more into perspective
part 1:
DELETE FROM #output where [tst1] is NULL
Insert into db_comp
select * from #output
part 2:
insert into db_comp
select tst1, tst2,tst3 from #output
where tst1 is not null|||NJ skrev:

> The first statement the delete statement doesnt delete anything. let me
> put this more into perspective
> part 1:
> DELETE FROM #output where [tst1] is NULL
> Insert into db_comp
> select * from #output
> part 2:
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
/me scratches his head.
You use "is null" for the delete statement, but "is not null" for that
second part... If there are no rows in #output with tst1 being NULL,
then it shouldn't delete anything.
In part 2, OTOH, you select (and insert those) rows where tst1 is NOT
NULL, that is, the rows that you weren't trying to delete in the first
statement.
/impslayer, aka Birger Johansson|||Hi NJ,
How many rows does this return?
SELECT * FROM #output where [tst1] is NULL
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"NJ" <npaulus@.hotmail.com> wrote in message
news:1147780233.453202.150570@.v46g2000cwv.googlegroups.com...
> The first statement the delete statement doesnt delete anything. let me
> put this more into perspective
> part 1:
> DELETE FROM #output where [tst1] is NULL
> Insert into db_comp
> select * from #output
> part 2:
> insert into db_comp
> select tst1, tst2,tst3 from #output
> where tst1 is not null
>

Is Null

Does anybody have an idea why this query is not working in SQL Server?

select * from users where designation is null

The table has numerous rows where the designation value is NULL. Is this a db setting that needs to be changed?

Thanks!

Maybe the values aren't null? Could they be an empty string? Try returning the length of each value for the relevant rows and see what it comes back with.

|||

select * from users where IsNull(designation, '') = ''

That will handle all null values along with any empty string values.

|||

It turned out that NULL was being stored as a string in that field. I may have changed the field from allowing nulls to not allowing nulls and in the process, those NULL values were converted to a string. At least that's my theory.

Thanks for your help!

Is not Null clause not working for me

I've got a strange problem with my sql not working. I'm trying to query a
table and have a where clause to return all the values in a field which are
not null(in other words left blank) The query can be seen below.
However this query is still returning all of the records in the table even
though the u_creditor_ref field has mainly null values.
My table looks like the following: -
u_surname u_firstname u_account_name u_creditor_ref
Cairns Stephen
Kelly Michael 456789
McCartney&Son
Walsh John 4565
Has anyone any idea what i'm doing wrong. Im running this query in query
analyzer
Select u_surname, u_firstname, u_account_name, u_creditor_ref
from UserObject
where u_creditor_ref is not nullStephen
I think you have an empty string in the row
Try adding WHERE col <>''
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||Perhaps you don't have NULL for those rows. Perhaps it is a blank or similar
? Can you post a
reproduction script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which ar
e
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||"which are
not null(in other words left blank) "
Keep in mind that "blank" LEN(Column) = 0 , doesnt mean that they are NULL
!
If there is an empty string inserted you have to check for that LEN(
u_creditor_ref) = 0, if you have addtionaly also NULL values in there you
have to add "AND u_creditor_ref IS NULL)
Check the Column for NULL Values With (Select Count(*), CASE u_creditor_ref
WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END From Yourtable Group by CASE
u_creditor_ref WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
> are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||That works cheers
"Uri Dimant" wrote:

> Stephen
> I think you have an empty string in the row
> Try adding WHERE col <>''
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> are
>
>|||Yeah your right. it looked like a null but if i did either of the following
it worked
where u_creditor_ref <>''
or
where u_creditor_ref != ''
which of these is better to use or do they mean the exact same thing?
"Tibor Karaszi" wrote:

> Perhaps you don't have NULL for those rows. Perhaps it is a blank or simil
ar? Can you post a
> reproduction script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
>
>|||Use <>. They do the same, but <> is ANSI SQL compliant, so you might just wa
nt to get into the
habit...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the followin
g
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>|||Use <> for spec purposes.
Jens Suessmeyer.
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the
> following
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>