Hi Group,
Let me explain my problem
============================
declare @tableName varchar(200)
set @tableName = '##TempTab'
/*
Some external dll creating this table definition.
I am just passing the tablename to the dll.
But if the dll not getting the suitable data from the
AS then its unable to create this table.
Now I need to check from the SQL Server whether this
dynamic table get created or not.
If Yes then fire a query else return a hardcoded recordset
*/
================================
Now please guide me how should I track whether this dynamic named table
is created or not.
Thanks in advance.
Regards
Arijit Chatterjee

Hi Arjitt

Try This

IF OBJECT_ID('tempdb..##TempTab') IS NOT NULL
    PRINT '#some_temp_name exists.'
ELSE
    PRINT '#some_temp_name does not exist.'



arijitchatterjee***@yahoo.co.in wrote:
Show quoteHide quote

Thanks a Ton...:)
But I am writing it in dynamic SQL.
After
Execute (@str)
command how would I track the response of that particular execution?
Thanks in advance.
Regards
Arijit Chatterjee

You don't need to use dynamic SQL for this task since you can pass a
variable to the OBJECT_ID function:

DECLARE @ObjectName nvarchar(255)
SET @ObjectName = N'tempdb..##temp'
IF OBJECT_ID(@ObjectName) IS NOT NULL
....

You can also pass parameters in/out of a dynamically executed SQL statement
via sp_executesql.  Below is an example and you can get more info in the
Books Online.  Also, see Erland's article
(http://www.sommarskog.se/dynamic_sql.html) for a more complete discussion.

DECLARE @ObjectID int
EXEC sp_executesql N'SELECT @ObjectID = OBJECT_ID(@ObjectName)',
    N'@ObjectName nvarchar(255), @ObjectID int OUT',
    @ObjectName = N'tempdb..##temp',
    @ObjectID = @ObjectID OUT
IF OBJECT_ID(@ObjectName) IS NOT NULL
....



Show quoteHide quote

Thanks,
I was really looking for this.
Thanks once again.
Regards
Arijit Chatterjee

Glad it helped.

Show quoteHide quote

Why are you creating tables on the fly with dynamic SQL at all?  The
usual reason is to mimic scratch tapes in a non-relational file system.


In RDBMS we have a stable, known at run time data model.  In fact, I
would bet these mafically appearing tables have a naming rule based on
dates or sequences -- just like 1950's scratch tape labels!  In a
relational model, tables would be named for what they are and never for
their physcial creation.

Actually, if you'd done -any- programming out in the field then you'd
realise there are dozens of reasons to do this.

To name but a couple...

Admin screens
Knowledge Management system
Core for drop down box 'meta data'

Show quoteHide quote

Skipping admin scrreens and the problems of having meta data in a
schema, to violate a tiered architecture, I hope that you do not use
SQL for a Knowledge Management system.

I did one of those years ago for Southern California Edison's labor law
department.  Basically, they wanted a legal library for all their Union
contracts, labor law, etc.  Unions are not quite as bad in the US as
the UK or **shudder**  France, but it required a staff of lawyers on
the company payroll.

They hired me because they had just gotten DB2 and wanted to use it for
this project.  Instead I showed them how they use the scanners they
already had in another department to load the text onto an optical disk
and then provide the lawyers with a text search engine that looks like
WestLaw or Lexis.

First of all the SQL approach would not have worked, but attempting it
would have cost well into 6 figures.  They had fallen into the trap of
thinking that the tool they know is the only way to do everything.

Yes I did and it works brilliant and the client was extremely happy - a
government research division here in the UK; data volumes where 100'sGB with
growth anticipated into TB's within 5 years, and that was 4 years ago.

Here is the link to the company: http://www.iframework.co.uk/, they are
still in business after 4 years so I guess it must work.......

That is because you don't have the correct mindset, you have a fixed schema
mindset which for a Knowledge Management product where you build your own
model will not work.

You need to implement the object model through dynamically creating tables
and then the search queries need on that object model need to be dynamic as
well.

The product did cost a lot to develop but it allows you to define your own
object model and then everything is dynamic.

That is not knowledge mangement, its no more than indexing documents using
Microsoft Index Server.

Knowledge Management is hold the documents, understanding the context they
are in and allowing users to search within a context; a user may only want
to search within specific categories...

Perhaps I should go back to those clients and give them some correct advice
based on 20 years of industrial experience and of actually doing the stuff
rather than just theorising....

So you trying to say that having a clients table, a mailing list table, an
individual table - all of which I classify as meta data because its usually
static in comparison with data; are you trying to say they should not be
modelled in schema?

And how on earth does that violate a tiered archiecture?

Show quoteHide quote

Hello Tony,

How goes the Celko crusade:)

Beer with me.

Last week the US Secretary of Offense,Don Rumsfeld,testified before
the senate armed services committee.
Senator:'Are we in a civil war'?
Rumsfeld:'We will stay the course'.
Senator:'Do we have enough troops'?
Rumsfeld:'We are fighting a global war on terror and we are winning'.
Senator:'Are the Iraqies going to take over more security from our troops'?
Rumsfeld:'The president always said this wasn't going to be easy'.
Senator:'Did you screw a giraffe last weekend'?
Rumsfeld:'We will not cut and run'

It's called 'staying on message' at Yale, 'dis-associative personality
disorder'
at Yale medical school.

Do you follow my friend? :)

Timeout - max pool size reached
CLR and Insert
Performance problem with SQL Server 2005
How to do a restore a .BAK database - SQL 2005
TIP:How to Eliminate duplicate rows??