checking the database existence in sql server

hi to everybody

i'm new member to this site and i don't know where

can be a good place for creating this topic

pardon me for probable mistake.

i need to attach and detach the database in/from

sql server in vb.net with hard-code .i can do this

without using stored procedure and with SQL-DMO

But i can't checking The database Existence

for attaching it.now, how can i check the database

existense for preventing the attach operation.

please help me

thanks a lot

Hi,

using SMO you can use the following script:

new Server("SomeServer").Databases.Contains("SomeDatabase")

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

hi and thanks for your answer

where can i write that script

can you explain more by some example

thanks a lot

|||Hi,

sure a small application would be (with no extensive exception handling)

using System;

using Microsoft.SqlServer.Management.Smo;

using Microsoft.Win32;

namespace SMOProject

{

class Program

{

static void Main(string[] args)

{

Server s = new Server(args[0]);

if (s.Databases.Contains(args[1]))

Console.WriteLine(string.Format("Database {0} exists",args[1]));

}

}

}

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

that is brillient,

Sir could you tell me how to attach "database.dbf"?

Thanks

|||

Hi,

if it is a non-SQL Server database file you will have to import from the file using the DTS Import wizard or any other tools which offer a driver for the dbf files.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

it is a database file

Thanks

|||

But a non-sql server, right ? Or is it just a file which was named with the extension dbf instead of mdf or ndf ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

mmmmm Sory sir, i really forgotten

it is ".mdf"

Dont mind it

Thanks

|||If you have the logfile you can use sp_attachdb or the appropiate GUI task, if you don′t have the logfile you can use sp_attach_single_file_db. But this will only works if the database was closed correctly.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Yes Sir, Problem Solved by following scripts

sp_attach_db 'nwd','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\nwd.mdf',

'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\nwd_log.LDF'

Checking sysobjects for temp table

How can I check for the existence of a temp table before I attempt to drop
it?
Something like:
IF EXISTS( SELECT * FROM sysobjects WHERE Name = '#temp' )
DROP TABLE #temp
#temp exists but I cannot find a record for it in sysobjects. Neither can I
find it in tempdb (tempdb.dbo.sysobjects ).
Can anyone recommend how to handle this situation?Try:
if object_id('tempdb..#tmp') is not null
drop table #tmp
--
-Vishal
"David Frick" <dave@.frickcpa.com> wrote in message
news:uIYX89TTDHA.1992@.TK2MSFTNGP12.phx.gbl...
> How can I check for the existence of a temp table before I attempt to drop
> it?
> Something like:
> IF EXISTS( SELECT * FROM sysobjects WHERE Name = '#temp' )
> DROP TABLE #temp
> #temp exists but I cannot find a record for it in sysobjects. Neither can
I
> find it in tempdb (tempdb.dbo.sysobjects ).
> Can anyone recommend how to handle this situation?
>

Checking for row existence with secondary key

Hi -
I'm no SQL wizard (obviously). I have a table (conceivably very
large (500k+rows)) with a non-unique secondary index. I need an
efficient query to check for row existence using that secondary index.
Any ideas will be appreciated...
Thanks,
BryanIF EXISTS (SELECT * FROM yourTable AS a WHERE a.Col = YourCondition)
-- do your stuff here
Andrew J. Kelly SQL MVP
"Bryan" <bryan@.newsgroups.nospam> wrote in message
news:bijqk11p4ls4fq0dem7gnfee1n6e8vd8d6@.
4ax.com...
> Hi -
> I'm no SQL wizard (obviously). I have a table (conceivably very
> large (500k+rows)) with a non-unique secondary index. I need an
> efficient query to check for row existence using that secondary index.
> Any ideas will be appreciated...
> Thanks,
> Bryan|||Bryan,
DDL would help but try:
SELECT ID
FROM TABLE1
WHERE NOT EXISTS(SELECT * FROM TABLE2 WHERE TABLE2.ID = TABLE1.ID)
HTH
Jerry
"Bryan" <bryan@.newsgroups.nospam> wrote in message
news:bijqk11p4ls4fq0dem7gnfee1n6e8vd8d6@.
4ax.com...
> Hi -
> I'm no SQL wizard (obviously). I have a table (conceivably very
> large (500k+rows)) with a non-unique secondary index. I need an
> efficient query to check for row existence using that secondary index.
> Any ideas will be appreciated...
> Thanks,
> Bryan

Checking for Row Existence

Can someone show me some C# code for detecting if a SQL row exists or not? This seems like a very typical action and I cannot for the life of me find a tutorial online that explains this step. In my code I'm either going to INSERT or UPDATE a record. I tried sending a SELECT command through a ExecuteNonQuery, but only got -1 as a response. Apparently ExecuteNonQuery does not work with SELECT. I then saw that T-SQL has an EXISTS keyword, but I cannot see anyway to use that from within C#.

So...can anyone share the typical code they use to identify if a row exists or not within a database. I guess I was execting there to be some method available to do this sort of thing.

There are two methods i use

Method A)

Which uses the Execute Scalar to return the results of a SQL COUNT

i.e.

 cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";
 Int32 count = (Int32) cmd.ExecuteScalar();

Here is a nice example i found for you

http://www.java2s.com/Code/CSharp/Database-ADO.net/GetrowcountbyExecuteScalar.htm

Method B)

Using a datareader and has.rows

Add ; to make it c# as you see fit

Dim objTransport As SqlDataReader
objTransport = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

If objTransport.HasRows Then
Return ("True")
Else
Return ("False")
End If
myConnection.Close()

Method A is more efficient as your only passing around a very small number between the webserver and database.

Hope that helps

checking for file existence within an udf

hi ng,
hi friends,
i've a problem within an udf to find out wheter a file or a path exists or
not. unc pathes and wildcards have to be supported.
i've already checked the following solutions:
- exec master..xp_fileexist @.filename, @.file_exists=@.exists output
=> wildcards are not supported
=> pathes can't be checked
- exec master..sp_MSget_file_existence @.filename,@.exists=@.exists output
=> no extended stored procedure but works perfect in tsql
=> stored procedures can't be used in udfs
- exec master..xp_cmdshell @.command
=> no reuseable output value
maybe someone can help me.
thanks
dengo
-- hope you'll understand my englishxp_cmdshell *does* return a usable output value. Try this:
declare @.i as integer
exec @.i = master..xp_cmdshell 'dir c:\config.* /b'
print @.i
If @.i = 0 then the command executed successfully and one or more matching
files were found. If @.i <> 0, then no file was found or there was an error,
as might be the case here:
declare @.i as integer
exec @.i = master..xp_cmdshell 'dir c:\config.zzzz* /b'
print @.i
"Robert Denkmayr" <RobertDenkmayr@.discussions.microsoft.com> wrote in
message news:0D9C2CCD-B335-479C-B10B-E944D34A74FF@.microsoft.com...
> hi ng,
> hi friends,
> i've a problem within an udf to find out wheter a file or a path exists or
> not. unc pathes and wildcards have to be supported.
> i've already checked the following solutions:
> - exec master..xp_fileexist @.filename, @.file_exists=@.exists output
> => wildcards are not supported
> => pathes can't be checked
> - exec master..sp_MSget_file_existence @.filename,@.exists=@.exists output
> => no extended stored procedure but works perfect in tsql
> => stored procedures can't be used in udfs
> - exec master..xp_cmdshell @.command
> => no reuseable output value
> maybe someone can help me.
> thanks
> dengo
> -- hope you'll understand my english|||hi michael,
thanks for your reply.
you're right but your reply is my answer - see returncode on errors.
additional a remark from <sp_MSget_file_existence>.
/*
** The return code from xp_cmdshell is not a reliable way to check whether
** the file exists or not. It is always 0 on Win95 as long as xp_cmdshell
succeeds.
*/
another weak spot is that you need an other command for checking the
existence of a path.
dengo
"Michael C#" wrote:

> xp_cmdshell *does* return a usable output value. Try this:
> declare @.i as integer
> exec @.i = master..xp_cmdshell 'dir c:\config.* /b'
> print @.i
> If @.i = 0 then the command executed successfully and one or more matching
> files were found. If @.i <> 0, then no file was found or there was an erro
r,
> as might be the case here:
> declare @.i as integer
> exec @.i = master..xp_cmdshell 'dir c:\config.zzzz* /b'
> print @.i
> "Robert Denkmayr" <RobertDenkmayr@.discussions.microsoft.com> wrote in
> message news:0D9C2CCD-B335-479C-B10B-E944D34A74FF@.microsoft.com...
>
>|||Ahhh you're using Win95 are ya? Wowsa.
I haven't done anything on Win95 with this, but if you have a Win95 box
handy, you might try it out and see if it counts a "No Files Found" error as
a success or not. I have used this method on Win2K, XP Pro and 2003 Server
with no issues.
You might check out xp_dirtree or xp_subdirs for paths. I'd think you'd
need some sort of custom solution if you want to return two separate codes -
one for the existence of a path, and one for the existence of a file.
"Robert Denkmayr" <RobertDenkmayr@.discussions.microsoft.com> wrote in
message news:5E0B835F-FCD7-4770-9C08-B84922D5F8C1@.microsoft.com...
> hi michael,
> thanks for your reply.
> you're right but your reply is my answer - see returncode on errors.
> additional a remark from <sp_MSget_file_existence>.
> /*
> ** The return code from xp_cmdshell is not a reliable way to check whether
> ** the file exists or not. It is always 0 on Win95 as long as xp_cmdshell
> succeeds.
> */
> another weak spot is that you need an other command for checking the
> existence of a path.
> dengo
>
> "Michael C#" wrote:
>|||no i'm not using win95.
for the first time the udf will be used by dts packages on w2k server where
dynamic filename assignment is necessary to avoid overwriting existing files
or using prior created paths. that's why i need a reliable solution.
at the moment i'll implement 'xp_cmdshell' with the appropriate command
either for a filename or a path.
because i'm not familiar with programming in c/c++ the most preferred
solution would be a dll for an extended stored procedure like the
'xpstar.dll' which contains the function for 'xp_fileexists'.
another step would be the sourcecode of 'xpstar.dll' so i could extract the
necessary code for a new dll with the new function.
"Michael C#" wrote:

> Ahhh you're using Win95 are ya? Wowsa.
> I haven't done anything on Win95 with this, but if you have a Win95 box
> handy, you might try it out and see if it counts a "No Files Found" error
as
> a success or not. I have used this method on Win2K, XP Pro and 2003 Serve
r
> with no issues.
> You might check out xp_dirtree or xp_subdirs for paths. I'd think you'd
> need some sort of custom solution if you want to return two separate codes
-
> one for the existence of a path, and one for the existence of a file.
>|||If you're interested in creating your own XP's, here's an article on the
subject: http://www.codeproject.com/database/extended_sp.asp.
You could also look at generating a filename and path using GetDate() and a
random #, and/or a counter value stored in a table which you could increment
each time a new file is created. Not sure of the specifics of your
application, but those are a couple ideas which might work in some
circumstances.
"Robert Denkmayr" <RobertDenkmayr@.discussions.microsoft.com> wrote in
message news:CCD5EF04-52E8-42BA-A9F3-472986C53A6A@.microsoft.com...
> no i'm not using win95.
> for the first time the udf will be used by dts packages on w2k server
> where
> dynamic filename assignment is necessary to avoid overwriting existing
> files
> or using prior created paths. that's why i need a reliable solution.
> at the moment i'll implement 'xp_cmdshell' with the appropriate command
> either for a filename or a path.
> because i'm not familiar with programming in c/c++ the most preferred
> solution would be a dll for an extended stored procedure like the
> 'xpstar.dll' which contains the function for 'xp_fileexists'.
> another step would be the sourcecode of 'xpstar.dll' so i could extract
> the
> necessary code for a new dll with the new function.
>
> "Michael C#" wrote:
>|||i had the same ideas as you can see in a sample of the first implementation
below:
'p2lgb_%y%m%d_%n.txt' ... data file
'p2lgb_%y%m%d_%n.err' ... error file
'\\dfs.dom\dfs\sem' ... interface path
'\\dfs.dom\dfs\archiv\sem\%y' ... archiv path
%y=year, %m=month, %d=day ... using getdate()
%n=unique number ... counter value stored in a table
the problem in this implementation is the unique number. i can't be sure
that the next
number isn't used manually by an user or it's already used in the archiv
directory => check for file existence.
at the new (second) implementation i should check both the interface and
archiv paths for the first unused number (starting at 1) => check for file
existence.
i'll examine the article about writing xp's tomorrow because i'm from
austria and it's already 11:15 pm.
thanks and good night
dengo
"Michael C#" wrote:

> If you're interested in creating your own XP's, here's an article on the
> subject: http://www.codeproject.com/database/extended_sp.asp.
> You could also look at generating a filename and path using GetDate() and
a
> random #, and/or a counter value stored in a table which you could increme
nt
> each time a new file is created. Not sure of the specifics of your
> application, but those are a couple ideas which might work in some
> circumstances.
>

Checking for Existence of File fails during validation

OK. Here's my situation. I check for the existence of a dummy .txt file using a script. I send an e-mail if it does not exist and exit package. The .txt file only exists if another .xls file is present which I import. However, during the validation phase of the package, the package fails because the .xls file does not exist. Is there a way to bypass the validation step? The only solution I came up with is to have a two-step job. The first runs the file check step and sends the e-mail. The second attemps to run the package and fails. Not a very graceful exit.

In your package, make the first task a script task that checks to see if the file exists. If it does, set a package variable to indicate that it does exist. Connect that script task to the data flow task with a Success constraint. Double-click the constraint and add an expression that evaluates to True if the variable indicates that the file exists. On your data flow task, set the DelayValidation property to True.

Let me know if you need additional details.

|||

Thanks for the quick response. I actually didn't use a variable, just some control flow controls to send an e-mail if the first file doesn't exist; I'll try to integrate your suggestion on setting the variables in the future. The trick for me was the delay validation flag on the data flow. Thanks again.

Checking for existence of a table that is already full-text indexe

Full-text indexing was manually set up to work on the development server.
Now, I need to write a SQL script to programmatically set up Full-text
indexing on the staging server and then on the production server.
The SQL script must be rerunnable. It should handle the scenario where
full-text indexing already exists or not, per table, as necessary.
The SQL script works on the first pass successfully (because the staging
server did not have full-text indexing).
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
The SQL script fails on the second pass on the staging server. Because it
tries to create the index that already exists.
So, I added a statement to drop the index before creating the index.
EXEC sp_fulltext_table 'tablename', 'drop'
GO
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
That works because it drops an existing index, then creates the index.
However, the drop statement will fail if an index doesn't exist.
So then, how do I check for the existence of a table index before dropping it?
IF EXISTS ("statement to check for existence of a table index")
BEGIN
EXEC sp_fulltext_table 'tablename', 'drop'
GO
END
EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
GO
I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that I
could use in the if statement.
EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
GO
Any suggestions?
MGBloomfield,
Yes, there are some good T-SQL code examples can be found & modified for
your purposes in the procedures in KB article: 240867 (Q240867) "INF: How to
Move, Copy, and Backup Full-Text Catalog Folders and Files" at:
http://support.microsoft.com/default...b;EN-US;240867
You might also find this code useful as well:
-- To Create/Remove the Existing Full-Text Table Index, Catalog
-- If Full-Text Index exists, DROP that Index,
-- If Full-Text Index does not exist, CREATE that Index.
use pubs
go
IF OBJECTPROPERTY ( object_id('pub_info'),'TableHasActiveFulltextIndex ') = 1
BEGIN
print 'Table pub_info is Full-Text Enabled, dropping Full-Text Index &
Catalog...'
EXEC sp_fulltext_table 'pub_info', 'drop'
EXEC sp_fulltext_catalog 'PubInfo', 'drop'
END
ELSE IF OBJECTPROPERTY (
object_id('pub_info'),'TableHasActiveFulltextIndex ') = 0
BEGIN
print 'Table pub_info is NOT Full-Text Enabled, creating FT Catalog,
Index & Activating...'
EXEC sp_fulltext_catalog 'PubInfo', 'create'
EXEC sp_fulltext_table 'pub_info', 'create', 'PubInfo', 'UPKCL_pubinfo'
EXEC sp_fulltext_column 'pub_info', 'pub_id', 'add'
EXEC sp_fulltext_column 'pub_info', 'pr_info', 'add'
EXEC sp_fulltext_table 'pub_info', 'activate'
END
Regards,
John
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:F80D1509-E8EF-4B4F-9011-BA44DF7EF91C@.microsoft.com...
> Full-text indexing was manually set up to work on the development server.
> Now, I need to write a SQL script to programmatically set up Full-text
> indexing on the staging server and then on the production server.
> The SQL script must be rerunnable. It should handle the scenario where
> full-text indexing already exists or not, per table, as necessary.
> The SQL script works on the first pass successfully (because the staging
> server did not have full-text indexing).
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> The SQL script fails on the second pass on the staging server. Because it
> tries to create the index that already exists.
> So, I added a statement to drop the index before creating the index.
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> That works because it drops an existing index, then creates the index.
> However, the drop statement will fail if an index doesn't exist.
> So then, how do I check for the existence of a table index before dropping
it?
> IF EXISTS ("statement to check for existence of a table index")
> BEGIN
> EXEC sp_fulltext_table 'tablename', 'drop'
> GO
> END
> EXEC sp_fulltext_table 'tablename', 'create', 'Catalog', 'PK_tablename'
> GO
> I looked at sp_help_fulltext_tables, but it doesn't return TRUE/FALSE that
I
> could use in the if statement.
> EXEC sp_help_fulltext_tables 'Catalog', 'tablename'
> GO
> Any suggestions?
>

Checking for database existence on a SQL server using ADO

What is the best way to check for whether a database exists on a server
using ADO. I am thinking that if one connects to the server, and the
connection fails, the Errors object should have some sort of information
to point out to me that the database does not exist. Does anybody have
information about this or any other way to check if a database exists on
a SQL server using ADO ?May be the db exists but you do not have access to it.
AMB
"Edward Diener" wrote:

> What is the best way to check for whether a database exists on a server
> using ADO. I am thinking that if one connects to the server, and the
> connection fails, the Errors object should have some sort of information
> to point out to me that the database does not exist. Does anybody have
> information about this or any other way to check if a database exists on
> a SQL server using ADO ?
>|||Try executing sp_databases and checking if the db is there.
AMB
"Alejandro Mesa" wrote:
> May be the db exists but you do not have access to it.
>
> AMB
> "Edward Diener" wrote:
>|||A_X_L_X wrote:
> In ASP:
> assume Application("strCon") contains a database connection string
>
> Function CheckDBConnection
> Dim oConn
> on error resume next
> Set oConn = Server.CreateObject("ADODB.Connection")
> oConn.ConnectionString = Application("strCon")
> oConn.Open
> if err.number = 0 then
> CheckDBConnection = "success"
> else
> CheckDBConnection = "failure. Error " & err.number & ": " &
> err.description
> end if
> oConn.Close
> Set oConn = Nothing
> err.Clear
> End Function
> This is fast too, especially if database is there, or if it is not.
This tells me that the connection fails, not that the database does not
exist. The connection may fail for other reasons, such as an invalid
user ID or password. I need to track the failure to the fact that the
database itself does not exist on the server. So far I have not found
any information in the SQL Server docs about the error number returned
which says that a database to which I am trying to connect does not
exist. If you have any idea where such information exists in the
documentation, I would be glad to know about it.|||Alejandro Mesa wrote:
> Try executing sp_databases and checking if the db is there.
Thanks, I will look at this stored procedure. I assume it returns the
databases on a server.
>
> AMB
> "Alejandro Mesa" wrote:
>|||Alejandro Mesa wrote:
> Try executing sp_databases and checking if the db is there.
Yes, this should work fine, thank you ! The related problem is if I can
connect to the server without specifying a database. Is this possible ?
In the case of running the stored procedure you specify, sp_databases,
what database do I connect to, or can I just leave it out of my
connection string ?

Checking for a columns existence

I need to check if a particular column exists in a table. If it does then I need to use it in a calculation, otherwise I don't. Here is the sql statement I currently have. It complains when I try to use column I01 in the select statement, even though the if condition evaluates to false. Any suggestions?

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE (TABLE_NAME = 'Test') AND column_name = 'I01')
SELECT 0
ELSE
SELECT I01 FROM TestCan you work it out by looking for the column name in the syscolumns database?|||Originally posted by andyabel
Can you work it out by looking for the column name in the syscolumns database?

It's the same problem. I can find whether it exists or not, but how do I use it in the select statement if it does? Apparently, it checks for the syntax of the query, before evaluating the IF condition.|||Maybe there's a setting that tells whether to verify all code clauses before running, or to verify only at run-time?
(Can't find it in BOL, though, but maybe someone else can)|||Can you fool the compiler by using exec()? e.g.:

declare @.string varchar(100)
set @.string='SELECT I01 FROM Names'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE (TABLE_NAME = 'Test') AND column_name = 'I01')
SELECT 0
ELSE
exec (@.string)|||Originally posted by andyabel
Can you fool the compiler by using exec()? e.g.:

declare @.string varchar(100)
set @.string='SELECT I01 FROM Names'
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns
WHERE (TABLE_NAME = 'Test') AND column_name = 'I01')
SELECT 0
ELSE
exec (@.string)

Thanks for all of your help guys, I think I got it to work.

Checking existence with insert... select OPENXML

Hi,
If I am inserting data into a table and I do not wish to insert if the row is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form that I can use to test for existence of the row in the destination table as I did above. I can always insert into a temp table of course and then use that as the source of the da
ta for the insert and use the first syntax form. But I was just curious whether this can be easily achieved without the use of a temp table.
Cheers
Ken
Yes, it is possible. You just need to add an alias to the OPENXML derived
table. For example:
INSERT INTO YourTable(CustomerID, ContactName)
SELECT CustomerID, ContactName
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20)) AS XM
WHERE NOT EXISTS
(
SELECT 1
FROM YourTable AS x
WHERE x.CustomerID = XM.CustomerID
)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
Hi,
If I am inserting data into a table and I do not wish to insert if the row
is already there I can write:
insert into t1 (c1,c2,c3)
select c1,c2,c3 from t2
where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
c3=t2.c3)
If my source query for the insert uses OPENXML is there any syntactical form
that I can use to test for existence of the row in the destination table as
I did above. I can always insert into a temp table of course and then use
that as the source of the data for the insert and use the first syntax form.
But I was just curious whether this can be easily achieved without the use
of a temp table.
Cheers
Ken
|||Many thanks
Ken
"Narayana Vyas Kondreddi" wrote:

> Yes, it is possible. You just need to add an alias to the OPENXML derived
> table. For example:
> INSERT INTO YourTable(CustomerID, ContactName)
> SELECT CustomerID, ContactName
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20)) AS XM
> WHERE NOT EXISTS
> (
> SELECT 1
> FROM YourTable AS x
> WHERE x.CustomerID = XM.CustomerID
> )
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ken Eng" <KenEng@.discussions.microsoft.com> wrote in message
> news:C2A7166E-7506-4B51-986A-7013210B63CA@.microsoft.com...
> Hi,
> If I am inserting data into a table and I do not wish to insert if the row
> is already there I can write:
>
> insert into t1 (c1,c2,c3)
> select c1,c2,c3 from t2
> where not exists (select * from t1 where c1=t2.c1 and c2=t2.c2 and
> c3=t2.c3)
> If my source query for the insert uses OPENXML is there any syntactical form
> that I can use to test for existence of the row in the destination table as
> I did above. I can always insert into a temp table of course and then use
> that as the source of the data for the insert and use the first syntax form.
> But I was just curious whether this can be easily achieved without the use
> of a temp table.
> Cheers
> Ken
>
>

Checking existence of network file

Hi Guys,

Just wonder if anyone know how can I check the network file (whether exists or not) using TSQL or Extended Stored Procedures?

What I can find out is the xp_fileexist, but it is only meant for searching file residing locally... I have tried mapping the drive of another server to the SQL server, but it just won't do it...

Any idea?

Thanks in advance.

Regards,
TangI have used xp_cmdshell as follows:

exec @.result = xp_cmdshell ('dir \\server\share\filename.ext')

if(@.result = 0)

begin
it's there so do something
end

This has worked well so far...

Check the field existence of a database table

Check the field existence of a database table, if exist get the type, size, decimal ..etc attributes

I need SP

SP

(

@.Tablename varchar(30),

@.Fieldname varchar(30),

@.existance char(1) OUTPUT,

@.field_type varchar(30) OUTPUT,

@.field_size int OUTPUT,

@.field_decimal int OUTPUT

)

as

/* Below check the existance of a @.Fieldname in given @.Tablename */

/* And set the OUTPUT variables */

Thanks

To check existance of a data column, try code below:

IFEXISTS (SELECT *FROMSysObjects soINNERJOINSysColumns scON so.ID = sc.IDWHEREObjectProperty(so.ID,'IsUserTable') = 1AND so.Name ='yourtablename'AND sc.Name ='columnname' )

To get datatype of the data column, try:

SELECT data_typeFROM information_schema.columnsWHERE table_schema ='dbo'AND table_name ='yourtablename'AND column_name ='columnname'

|||

You can also do an sp_Help 'Table' to get all the information.

|||

Hi jackyang,

Thanks for your help.Your first query is running properly but second one has problem

SELECT data_typeFROM information_schema.columnsWHERE table_schema ='dbo'AND table_name ='yourtablename'AND column_name ='columnname'

 does not work

here is 'dbo' static or my databas name (my database name is 'neuron')?

Thanks

|||

You can disregard the table_schema then. It's likely the security schema is not default 'dbo' in your setup.

Just use the code below:

SELECT data_typeFROM information_schema.columnsWHERE table_name ='yourtablename'AND column_name ='columnname'