Hi,
I have a table with the follwing values
Table Cats
{
CatID, date
-----------------------
Cat1, D1
Cat2, D2
Cat3, D3
}
I just wanted to check whether Cat1 exists in the table. Can anyone post the query
Thanks
~MOhan
Dear All,
I wanted to know how do I know or check that whether a column exists in a table. What function or method should I use to find out that a column has already exists in a table.
When I run a T-SQL script which i have written does not work. Here is how I have written:
IF Object_ID('ColumnA') IS NOT NULL
ALTER TABLE [dbo].[Table1] DROP COLUMN [ColumnA]
GO
I badly need some help.
Apologies if this has been answered before, but the "Search" function doesn't seem to be working on these forums the last couple of days.
I'd just like to check if a table already exists before dropping it so that I can avoid an error if it doesn't exist. Doing a web search, I've tried along the lines of
"If (object_id(sensor_stream) is not null) drop table sensor_stream"
and
"If exists (select * from sensor_stream) drop table sensor_stream"
In both of these cases I get the error: "There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = if ]"
Sooooo... what is the standard way to check for existence of a table before dropping it? Someone help? This seems like it should be simple, but I can't figure it out.
Thanks in advance!
Dana
Hi!
SS2005:
create table #tmp(a int)
create index idxt1 on #tmp(a)
insert into #tmp values (42)
select * from sys.indexes
where name like '%idx%'
order by name
But I can't see any rows about idxt1 :-(
If I say
create index idxt1 on #tmp(a)
againg I got error, because it exists.
How to check for existence using query?
B. D. Jensen
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
I'm familiar with how to check for the existence of a table before dropping it using the following command:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[xxx]
How does one check for the existence of a temp table (using # syntax) before dropping it? I've tried various flavors of this command and none work. One flavor is
use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#xxx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#xxx]
CREATE TABLE #xxx (
NumID INTEGER IDENTITY(1,1),
Exhibitor_Id INTEGER NOT NULL,
Company_Id INTEGER NOT NULL
)
Thanks.
Nick
This function, F_TEMP_TABLE_EXISTS, checks for the existence of a temp table (## name or # name), and returns a 1 if it exists, and returns a 0 if it doesn't exist.
The script creates the function and tests it. The expected test results are also included.
This was tested with SQL 2000 only.
if objectproperty(object_id('dbo.F_TEMP_TABLE_EXISTS'),'IsScalarFunction') = 1
begin drop function dbo.F_TEMP_TABLE_EXISTS end
go
create function dbo.F_TEMP_TABLE_EXISTS
( @temp_table_name sysname )
returns int
as
/*
Function: F_TEMP_TABLE_EXISTS
Checks for the existence of a temp table
(## name or # name), and returns a 1 if
it exists, and returns a 0 if it doesn't exist.
*/
begin
if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U')and
o.id = object_id( N'tempdb..'+@temp_table_name )
)
begin return 1 end
return 0
end
go
print 'Create temp tables for testing'
create table #temp (x int)
go
create table ##temp2 (x int)
go
print 'Test if temp tables exist'
select
[Table Exists] = dbo.F_TEMP_TABLE_EXISTS ( NM ),
[Table Name] = NM
from
(
select nm = '#temp' union all
select nm = '##temp2' union all
select nm = '##temp' union all
select nm = '#temp2'
) a
print 'Check if table #temp exists'
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
print '#temp exists'
else
print '#temp does not exist'
print 'Check if table ##temp4 exists'
if dbo.F_TEMP_TABLE_EXISTS ( '##temp4' ) = 1
print '##temp4 exists'
else
print '##temp4 does not exist'
go
-- Drop temp tables used for testing,
-- after using function F_TEMP_TABLE_EXISTS
-- to check if they exist.
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
begin
print 'drop table #temp'
drop table #temp
end
if dbo.F_TEMP_TABLE_EXISTS ( '##temp2' ) = 1
begin
print 'drop table ##temp2'
drop table ##temp2
end
Test Results:
Create temp tables for testing
Test if temp tables exist
Table Exists Table Name
------------ ----------
1 #temp
1 ##temp2
0 ##temp
0 #temp2
(4 row(s) affected)
Check if table #temp exists
#temp exists
Check if table ##temp4 exists
##temp4 does not exist
drop table #temp
drop table ##temp2
CODO ERGO SUM
Hi all,
While cleaning up some code, I ran across the following statement in a stored proc - the purpose of which is to determine if a table exists in the local database: SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[XML_PRINTDATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1of course I removed it from the IF just for testing purposes, but my quandry is this...
Why chose that select (converting table name to object ID) rather than just doing THIS:SELECT * FROM dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1
I first thought it was to gain access to the "id" column value (and that may yet be the purpose of it), but the second code seems to work just peachy (I assume because the id column is present in the sysobjects table itself).
A follow-on question is this:
When I try to do the same check from another server (i.e.SELECT * FROM APRECEIVE1.DailyProd.dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1) it of course fails because OBJECTPROPERTY only looks for the id on the local database.
So, do I CARE if it is a user table? (I am reasonably sure it is, of course) and if so, is there a way to check on the remote server for the object type?
Bottom line is I Think I can just simplify things and check for the object name on the remote server, but just don't want to take away any "warm fuzzy feeling" generated by the original stored proc, if such a warm fuzzy is of any benefit (though don't get me started on the relativity of warm fuzzies, I wrote my Thesis on that ;) )
I am using the following script to check existence of table in the Database and create it dynamically...
This is working when table not existed, it error-ed when the table existed...
This script i am using in the Exec Sql Task.....
[Execute SQL Task] Error: Executing the query "declare @ODSDB varchar(50)
declare @SQLSTMT varcha..." failed with the following error: "There is already an object named 'addressTable' in the database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not
set correctly, or connection not established correctly.
declare @ODSDB varchar(50)
declare @SQLSTMT varchar(max)
set @ODSDB = 'SampleDB'
begin
set @SQLSTMT = '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(''' + @ODSDB + '.dbo.addressTable'') and Type=''U'')
[Code] ...........
Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray
View 3 Replies View RelatedI downloaded Quest Software's freeware version ofComparison Suite. When I attempt to install, it tellsme that it requires .NET framework 1.1.4322. I alreadyhave .NET 2.0 installed (as part of the MS-SQL native client install)and I really don't want to mess that up.Is it *safe* to have the installer download/install .NET 1.1to coexist with 2.0? Is it likely to work?
View 1 Replies View RelatedCan 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.
View 1 Replies View RelatedHi Friends,I have one table in the databse,i.e userTable with one field userNameIn my form I have one Label ,textbox for entering the userName and One button for submit,So I am entering the data into the table(userTable) after clicking on the submit buttonBut my problem is before entering the data into the table I want to find wheather the given data exits or notif its not exists the data has to insert into the table otherwise its has to display the message"the user is already existed"for this I wrote the code like this in C# public Boolean isUserExists() { SqlCommand cmduserName = new SqlCommand("select count(*) from userTable where userName= " + txtuserName.Text + ")", conn); SqlDataReader rdr = null; rdr = cmduserName.ExecuteReader(); conn.Open(); int count = 0; while (rdr.NextResult()) { count = rdr.GetInt32(1); } conn.Close(); if (count == 0) { return false; } else { return true; } protected void Button1_Click(object sender, EventArgs e) { if(isUserExists()) { Response.Write("Opps ! User already Exists"); } SqlCommand adapInsert = new SqlCommand("insert into userName values('" + txtuserName.Text + "')",conn); conn.Open(); adapInsert.ExecuteNonQuery(); conn.Close(); Response.Write("data inserted"); } is it write or not because I am not getting the output .please tell me any one where I have to change the code ThanksGeeta
View 3 Replies View RelatedWe are currently running MS-SQL 6.5 and are getting new apps which require 7.0.
The new apps will be on their own server(s). My question is - Can a PC run both the
6.5 client and the 7.0 client (simultaneously) to access both 6.5 and 7.0?
So I want to check a directory for a particular extention of file. ex: *.txt, *.zip etc. (I'm using T-SQL by the way)
I tried this:
Code:
insert #a EXEC master..xp_fileexist 'G:wklyld_SQLRMAPOLLOADUntar*.txt'
this code works as long as I give it a specific file name, but if I try the *.txt or *.zip it wont work.
I've also been trying to run
Code:
EXEC MASTER.dbo.xp_cmdshell 'dir "G:wklyld_SQLRMAPOLLOADUntar*.*"
and then copy the results to a temp table and then run queries against the table. But I havent had much luck with that either.
Can anyone help? Thanks in advance.
Hi All:
Does anyone know where to find the property that disables the broadcast of the sql server from the point where you do not see the server show up in the list when one goes to
"New SQL Server Registration" -> "..."
beside the "Server" field. The dialog itself is titled "Registered SQL Server Properties". Assume both SQL Servers are both behind the firewall.
I have created a table and want to alter that table.
I want to add identity to my existence column.
How to add identity to my existence column?
How is it possible to test at the beginning of a stored procedure if acursor I want to declare already exists? (So I don't cause an error bydeclaring it).ThanksBruno
View 2 Replies View RelatedI'm looking for an efficient t-sql script to loop through all usertables in a db and determine/print the value of each row having adatetime field (including cases where there are multiple datetimefields per row)Help greatly appreciated..
View 2 Replies View RelatedI have a webpage where users can connect with other users by sending them a request. Sending the request just puts a row in a connect table that contains the users id, the targetusers id, a requesteddate, an accepted date and a disconnectdate (for the original requester to end the connection and a reject bit the the target can reject the request. Hoever I need to check to assure that the connect does nt already exist either as pending (requestdate is not null and accept date is null) or both dates are not null (connection already complete.).
ALTER PROCEDURE [dbo].[requestConnect]
-- Add the parameters for the stored procedure here
@requestor int,
@requested int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
[Code] ....
Here is my query:
SELECT dnn_Roles.RoleName, xyzUser.FirstName, xyzUser.LastName, xyzUser.Email, xyzRewardPoint.Points, xyzRewardPoint.RewardID
FROM xyzRewardPoint INNER JOIN
xyzUser ON xyzRewardPoint.UserID = xyzUser.UserId INNER JOIN
dnn_UserRoles INNER JOIN
dnn_Roles ON dnn_UserRoles.RoleID = dnn_Roles.RoleID ON xyzUser.ProviderId = dnn_UserRoles.UserID
WHERE (dnn_UserRoles.RoleID = 3) OR
(dnn_UserRoles.RoleID = 4) OR
(dnn_UserRoles.RoleID = 6)
ORDER BY dnn_UserRoles.RoleID
What I need is to extend this query to detect any users who exist in dnn_UserRoles.RoleID 3, 4 or 6 but do not have a RewardID value of '43' in the xyzRewardPoint table.
Hi,
Does TSQL provide methods to check if a file exists?
For example, a TSQL script will read data in a .dbf file into SQL Server. It will check if the file exists before read. How to do this check?
Thank you.
Hello All,
I'm trying to develop a stored procedure that would do one of TWO things:
1. Return a 'status' that a value does not exist, if I were to provide the parameter via an ASP.NET2.0 page
2. If it does exists, to return the row data associated with that value (id number)
The stored procedure would search a SQL Server table within it self first. It that fails it would look at an Oracle table (work order table). And if that fails to return a 'row' to look through another Oracle table (work request table). If that doesn't occur, then it would throw the result as described in #2.
If the result exists in one of the TWO Oracle tables it would then insert that row into the first SQL Server table that the stored procedure searched through AND would return the row set to the ASP.NET page.
While all this is happening, I was hoping to get some insight as to how to create a "Please Wait..." feedback and then moving to the final result.
Looking forward to the wise words of the many on this forum, as I have experienced in the past! :)
we tried out the following code in query analyser -
create procedure TrialProc
as
select * from sakjdhf
when we executed this piece of TSQL in query analyser, we expected it to give an error or warning that no object by the name of sakjdhf exists ( as actually there is no such table or view in the database ). however to our surprise we got "command completed successfully " !!
does this mean the SQL server does not check for necessary objects when creating a stored procedure ? or is there some setting that we missed out whihch is causing SQL server to overlook the error in the code ?
I've been matching some incoming contacts to existing contacts in a database and need to update, insert, or rematch based on the ifs below.
If name, phone, and email all provided for both, then use the highest Source. So if the Contact has a Source value of 5 and SourceContact has a ContactSource of 1, update Contact with SourceContact
If name, phone and email all provided, and source the same then update to new values.
If name and phone on SourceContact and name and Email on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and email on SourceContact and name and phone on Contact then reset Contact_fk to -1 should not have matched, but should be an insert
If name and phone on SourceContact and name and/or Phone is blank in Contact then update
If name and email on SourceContact and name and/or email is blank in Contact then update
If phone numbers can be different, just update record to SourceContact if it has a same or higher ContactSource
If email address do not match then set SourceContacts to -1 Contact_fk it was computed incorrectly. Both have a non blank email
If Contact_fk is 0 then it is a new contact and just insert it.
IF OBJECT_ID('dbo.SourceContact', 'U') IS NOT NULL DROP TABLE [dbo].[SourceContact];
CREATE TABLE [dbo].[SourceContact]
(
[SourceContact_pk]INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_SourceContact PRIMARY KEY CLUSTERED,
[ContactLastName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactLastName DEFAULT (''),
[ContactFirstName] VARCHAR(30) NOT NULL CONSTRAINT DF_SourceContact_ContactFirstName DEFAULT (''),
[code]....
I have a situation where our stored procedure inserts records from table_1 to table_2 when they don't already exist (uses the EXIST statement) on that table. If table_1 contains multiple records that are the same, it appears after the 1st record has been inserted, it does not recognize it as being there when it checks the existence when attempting to insert record 2.
Here's an example of the script:
insert into table_2 (col1,col2,col3)
select col1,col2,col3
from table_1 t1
where not exists (select '1'
from table_2 t2
where t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3)
Data from Table_1 -- Assume that table_2 does not contain these records
col1 col2 col3
AA 11 A1
AA 11 A1
BB 22 B2
All 3 records would be inserted to table_2 in this example.
Hi,I need to maintain both SQL Server 2000 and 2005 on my PC for supportpurposes. I am using the developer additions. After I installeverything (with no install errors) I can use SQL 2005 Mgt Studio fineuntil I bring up SQL 2000 Query Analyzer or Enterprise Mgr. After that,I cannot bring up SQL 2005 Mgt Studio. It get this message:Package 'Microsoft SQL Management Studio package' failed to loadI click OK, the message comes up again, I click OK again and then MgtStudio disappears.My default instance is under SQL 2000 and I created a named instanceunder SQL 2005 on my PC both with Local System Account.My order of installs was:1) SQL Server 2000 (Server tools)2) SQL Server 2000 SP43) SQL Server 2005 (DB Engine, Integration Services, and Workstationcomponents)I also have .NET 2005 Professional installed.The frustrating thing is I can find no log information anywhere --event log, under my Documents and Settings, or in the SQL Server 2005directories where it was installed.My guess is that when I used Query Analyzer it changed a settingsomewhere or prevented me from using Mgt Studio somehow, but I justcan't figure out what it did. I'd like to avoid a complete re-installwhere it may just happen again.Any help would be appreciated. Thanks much.Tom Vicker
View 2 Replies View RelatedI'm trying to figure out the best way to write a script to deploy environment variables to different servers. To create the variable I'm currently using catalog. create_environment_variable. I want to wrap that in an if not exist statement.I had thought about just blowing away all the variables and recreating them but I thought that wouldn't go over well in prod. I wasn't sure if by deleting the variable, references to the variable would be lost.
View 3 Replies View RelatedOK. 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.
View 3 Replies View RelatedI have a data flow task inside a foreach loop container which will take multiple excel files and load into a sql table. Once all the excel files are loaded into the table, then at the end one of the column gets updated using execute sql task. Now I am trying to check for a file existence, if the file is not present in the folder then the data flow task should not be executed. Any help is greatly appreciated, I am thinking of using file system task for this, but not exactly sure. Thanks in advance.
Hi All,
Not played with SQL for a while and am a bit rusty so please excuse me if I sound like a demented idiot! )
It's for a data migration from something wierd to SQL, I just need to be able to advise whether a table is worth migrating or someone should manually enter the data (i.e. if only six random fields are populated in a table then get a secretary to enter it).
I was hoping someone might have already done this and have a query I can edit?!
As far as I can make out the query needs to loop through every table, loop through every row, check each field for an existance of data and output something useful.
I've stared at this for about 3 hours now and tried several different things and none work.....and it's doing my nut! (
Help!!
Regs
We have a critical Production database on which we want to setup Log-Shipping. We have also purchased the Symantec NetBackup utility for taking Backup to Tape Drives.
We Know that there are some inherent problems with using Log-Shipping and a Backup Strategy together, and thus we were finding out various ways in which we can run both in tandem.
One of them was taking the Symantec Log backups with Copy-Only option.
The main problem with this is that the Symantec Backups becomes dependent on the Log-Shipping Backups and also most of their Log-Backups become useless. Also, we are not sure whether the utility allows Copy-Only backups
The second alternative was to disable the Backup job of Log-Shipping on the Primary Server and to use the Log-Backups done by the Symantec utility for performing the Restores on the Secondary.
Thus, if Log-Shipping is scheduled to run say every 4 hrs, and the Symantec Log-backup happens every 1 hour, then at an interval of every 4 hrs, the Restore Job on the secondary will pick up the 4 backups done by the Symantec Backup utility and Restore each one of them in sequential manner.
But, I guess it is not easy to have a manual Restore Policy in place.
I was really banking on this solution until I found that the Restore Job of the Log-Shipping setup is dependent on the Filename of the Transaction log file which the systems generates automatically, and it won€™t be easy to create a customized Restore Job on the secondary server which takes in all the Log Backups generated by the Symantec Backup utility and Restore the Secondary database.
Have any one of you ever face this issue? Would like to know what is the best way to keep both of them running together.
When attempting to backup a database using Management Studio, I receive a ""Cannot verify the esistence of the backup file location. Do you want to use the backup file location anyway?" yes/no messsage afer entering a file directory which exists. When entering a new directory, when I select "yes," a file locaiton will not be created, however, the backup operation continues to success. Originally, I had configured the sql services to execute under a user account with domain admin and administrative privledges, and I vverified the folder's security config permited write access. I reinstalled and now al SQL services are executing as "Local System" (or "Network Service") with same problem.
Any recommendations as to how to correct what I am doing wrong? (Thanks).
Jamie
__________________________
SQL Server Enterprise 2005 running w/all patches on Windows Server 2003 Server.