checking the existance of fields and types

I'm trying to write a program in cold fusion to check the existance of
fields and data types according to requirements

I was looking at the syscolumns table for some of this information but
I've discovered that in my playing (creating and deleting tables) thre
are multiple entries for a field that is in table that is created then
deleted then created again.

Is this a problem? Is there a better way to get at the information ie
the table, field, and type exist in a database?Use the INFORMATION_SCHEMA views. See Books Online for details.

Most metadata can be retrieved more easily from the info schema than
from system tables. There are some exceptions but system tables are
best left alone unless you really have to use them. They will be
supported only for backwards compatibility and won't reflect new
features in future versions.

--
David Portas
SQL Server MVP
--|||William Kossack (kossackw@.njc.org) writes:
> I'm trying to write a program in cold fusion to check the existance of
> fields and data types according to requirements
> I was looking at the syscolumns table for some of this information but
> I've discovered that in my playing (creating and deleting tables) thre
> are multiple entries for a field that is in table that is created then
> deleted then created again.

This sounds very strange. My guess is that you are joining syscolumns
with systypes incorrectly. (Those two tables are indeed a bit tricky
to match up.) Care to post a query that gives funny result?

> Is this a problem? Is there a better way to get at the information ie
> the table, field, and type exist in a database?

Some people tout the INFORMATION_SCHEMA views, but since they only
give a subset of the metadata information, they're pretty useless in
my opinion. They are mainly interesting if you want to write portable
meta-data queries.

There are also functions like object_id, columnproperty which can be
useful at times, but they have the drawback that they are restricted
to the current database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Checking for file existance in DTS

I have a DTS package that extracts information and puts it in an ASCII file
for upload to our bank. The upload is done by a scheduled task. Since I
don't always know if this task is successful, (the file ascii file will be
deleted if it is), I need to check for the existance of this file at the
beginning of the DTS package to stop it from running if the file already
exists.
This is on SQL2000
My questions are...
1) Is there a way to check for the existance of an ascii file on the server
from inside a DTS package, and then stop the package if it exists.
2) (alternately) Is there a way to set a DTS data transformation that
copies the data from a table into the ascii file so that it will append
rather than overwritting the ascii file.
Sorry if this is a duplicate, I think I lost a previous version of this
before it was posted.
Charlietake a look at the code below I have it in an internal package that I use in
a lot of packages so that I don't have duplicate code
FolderPath = (DTSGlobalVariables("gvFolderPath").value)
FilePath = DTSGlobalVariables("gvFilePath").value
Dim FSO
Function Main()
Set FSO = CreateObject("Scripting.FileSystemObject")
MoveFile(FSO)
Main = DTSTaskExecResult_Success
End Function
Function MoveFile(FSO)
MoveFrom = FolderPath & FilePath
If FSO.FileExists(MoveFrom) Then
DTSGlobalVariables("gvPackageError").value =0
else
DTSGlobalVariables("gvPackageError").value = 1
End If
End Function
"Charlie Chisholm" <charlie.chisholm@.goodwill-suncoast.com> wrote in message
news:bJe3e.38592$Fz.5460@.tornado.tampabay.rr.com...
>I have a DTS package that extracts information and puts it in an ASCII file
>for upload to our bank. The upload is done by a scheduled task. Since I
>don't always know if this task is successful, (the file ascii file will be
>deleted if it is), I need to check for the existance of this file at the
>beginning of the DTS package to stop it from running if the file already
>exists.
> This is on SQL2000
> My questions are...
> 1) Is there a way to check for the existance of an ascii file on the
> server from inside a DTS package, and then stop the package if it exists.
> 2) (alternately) Is there a way to set a DTS data transformation that
> copies the data from a table into the ascii file so that it will append
> rather than overwritting the ascii file.
> Sorry if this is a duplicate, I think I lost a previous version of this
> before it was posted.
> Charlie
>

Checking existance of record in Stored Procedure

Hi,

I am new to Stored Procedures. There is a simple procedure I want to create.

The scenario is this :

There is a Table named Test. In that table is a Column named 'TestID'. Now I want to create a Stored Procedure which inserts a record in Test, but if 'TestID' passed to it already exists, it should throw an error.

I am using C# and SQL Server 2005 Express.
Some code in C# also would be helpful.

You would need to create EITHER a unique index on the column, or a TRIGGER.

With the information at hand, I would vote for a UNIQUE index, and set the column to NOT NULL (so that a value MUST be supplied).

|||Thanks for the reply.

But I want to manage it programaticaly. I know about the constraints and SQL. But I am learning T-SQL and have created a scenario by myself to work on.

Basically I want to know how to throw error from stored proc? Or any way to let the app. know of some exceptional situations, like in .Net?|||

Inside your sp put the following code..

Code Snippet

Create Procedure YouSpName(@.TestId int, other params....)

as

Begin

If Exists(Select 1 From Test Where TestId=@.TestId)

Begin

Raiserror ('Recorde Alreay Found', 16, 1)

Return

End

--Your actual code will be appended here

..

..

..

End

|||Thanks, It worked fine.|||

One of the 'hallmarks' of a competent and experienced SQL Server developer/dba, is to understand the impact of various options.

A TRIGGER executing consumes more server resources than a CONSTRAINT. A TRIGGER will, most likely, issue locks on data that could, under high usage scenarios, cause blocking situations for other users.

A CONSTRAINT has NONE of the negative impact of a TRIGGER, in fact, it is the least impactful way to force data conformance. When a CONSTRAINT is indicated, it is the 'best' option to engage. And a CONSTRAINT failure will definitely 'throw' an error that the application can catch and handle.

|||Thanks Arnie,

I can see what you are saying. I should have used the Unique Constraint. But as I said, this not the real thing. It's not part of a project or even a program. I wanted to know how to tell the application that an unexpected situation occurred in the stored procedure. Same as throwing exceptions in C#, VB or any .Net language. So, I created this hopeless scenario.

But I appreciate your concern and this information you typed might be useful to a lot of people who doesn't know this.

Thanks.