I would like to know how can i check from SQL statement that
whether LinkedSERVER exists or not like we search a table from "sysobjects"
Is there a way where i can search my LinkedServer exists ?
if exists (select * from sysobjects where name = ['mylinkedserver'])
Hi,
I granted all the rights on the database to the user (db_owner, public, db_datawriter, etc...) However, I didn't grant the "System Administrators" role. I also specifically granted the select on those tables (sysobjects and sysindexes) for the user and checked through ‘sp_helprotect sysobjects’ command whether there are no specific revokes for for that user.
However, the user is still getting the below error while trying to expand the "Tables" view in the Enterprise Manager.
ERROR 229: SELECT permission denied on object 'sysobjects', database 'My_test', owner 'dbo', SELECT permission denied on object 'sysindexes', database 'My_test', owner 'dbo'.
Also, the user claims that he can’t seem to do anything with the database, he can’t view any objects, and, when he goes to Query Analyzer, if he tries to run a SELECT query on a table (that he know that this table exists), he gets this error:
SELECT permission denied on object 'tblBillingTrans', database 'My_Test', owner 'dbo'.
Any help would be greatly appreciated!
Thanks,
Alla
I have a table that has the following fields:tblECHECKID (autonumber identity column)PTIDBatchnumPageDataPointDPValueThere are many other tables on which I run a script that tests to makesure the data meets certain entry requirements (I don't want to putrestrictions on the forms because the data entry personel have toenter what is on the survey).Anything that fails is copied to the tblECHECK table. All the datacopied into the table are identified by a batchnumer (batchnum).I want to write a script that will look at all the records in thetblECHECK table, and delete any duplicates (the batch numbers are theonly fields that would be different).I have tried the following with no success (it returns 0 records):Select *From tblECHECKWhere batchnum ='batch8' and not exists (select * from tblECHECK wherebatchnum = 'batch7')Any help would be appreciated.
View 1 Replies View RelatedIF( NOT( EXISTS( SELECT 1 FROM updatetable WHERE ScriptName = 'MY_Scripts' )))
RAISERROR('script_dateupdates is required prior to running this script.', 30,1);
This is the Exists statment on the top. I was trying to find out what does it read. Can someone reat that for me.
Also why Not is before the brackets why it is not like If NOT EXISTS (Select....... ) acutally this is my concern to find out.
Thanks,
I have a Select statement that was working just fine:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName) SELECT Silo_ID,'" + Keyword + "', '" + strUsername + "' FROM SiloNames WHERE Silo_Name = '" + Uri + "'";
the PM now wants to capture the internal User, which I have done but I am having real problems with the syntax if the internal User exists:
string sSqlCmd = "INSERT INTO SiloKeywords (Silo_ID, Keyword, UserName, IsInternal) SELECT Silo_ID,'" + Keyword + "', '" + strUserName + "', if EXISTS(SELECT * FROM InternalUsersList WHERE Alias = '" + arrResult[1] + "') 1 ELSE 0 FROM SiloNames WHERE Silo_Name = '" + Uri + "'";
I am completely lost here.
Thanks for any help
I tried this:IF Exists (Select 1 from TESTA..POP10100 where PONumber=@PONumber) and(Select 1 from TESTA..POP30300 where POPType<>2 andPOPRCTNM=@ReceiptNo)BEGINENDIt says Incorrect Syntax error? I tried to cover the whole thing withan extra parantheses. It wont work eitherWhat am i doing wrongThanks, Girish
View 2 Replies View RelatedI am trying to use the following statement/query to delete any rows in tblcntslist table where tblcntslist.Company2 = tblCompany.Name.
Basically I am trying to look in the tblCompany table, look at the 'name' column, see if there are any entries in there that match entries in the tblcntslist.Company2 column. If there are matches, I want to delete them from the tblcntslist table.
I already "massaged" both table columns and removed all special characters, spaces, and other meaningless info that could result in incorrect matching. This way it is a match based on pure letter/number strings. I even set all as UCase in both table columns.
Code:
Delete from tblcntslist
where exists
(select * from tblCompany INNER JOIN tblCompany
ON tblCompany.Name = tblcntslist.Company2
where tblCompany.Name = tblcntslist.Company2)
I am getting the following error:
Server: Msg 1013, Level 15, State 1, Line 4
Tables or functions 'tblCompany' and 'tblCompany' have the same exposed names. Use correlation names to distinguish them.
I am a bit confused as to why. Any ideas?
I am trying to determine the existence of at least one row in my Detail table using EXISTS in my SELECT list from my Main table.SELECT M.ID,EXISTS(SELECT 1 FROM Detail D WHERE D.ID = M.ID) as HasDataFROM Main MCan this be done this way?I was hoping that using EXISTS would find a row and move on thus increasing performance.
View 15 Replies View RelatedIm trying to select rows on the following criteria
My app has a user that can have x jobs, each job has related entries which are marked has unread or read. I need to return any jobs that contain any unread entries also I need to return other jobs that have no entries marked as read.
I am able to return the following:
Comment UnReadMy Job 164
Comment UnReadFor Cam 166
Comment ReadThe Job 157
Comment ReadThird 159
Comment ReadMy Job 164
On Site Visit ReadMy newest job for log test 167
but as you can see jobid 164 appears in both groups and I need it to be one or the other
I tried using the if exists statement and was only able to return one group either unread or read, not both. I've tried everything but I'm new and I figure that theres got to be a more elegant way. Heres my sql:
CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID
@UserID INT
AS
BEGIN
IF EXISTS(SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead')
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'UnRead'
ORDER BY BackUp_Read ASC
END
ELSE
BEGIN
SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact,
Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who
FROM Master_Jobs INNER JOIN
Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN
Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN
User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN
Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN
Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN
Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND
Job_Assignments.UserID = Users.UserID
WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND
BackUp_Read = 'Read'
ORDER BY BackUp_Read ASC
END
END
GO
Thanks again
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
Hi,
When i try to save my stored procedure.. i am getting the above error and this is my sproc
1 INSERT INTO Statement..ClientSources
2 (
3 ClientId,
4 ClientSourceId,
5 SourceName
6 )
7 Select Distinct
8 @ClientId,
9 SOURCE_NUM,
10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
11 WHEN 2 Then SRC2NAME
12 WHEN 3 THEN SRC3NAME
13 WHEN 4 THEN SRC4NAME
14 WHEN 5 THEN SRC5NAME
15 WHEN 6 THEN SRC6NAME
16 WHEN 7 THEN SRC7NAME
17 WHEN 8 THEN SRC8NAME
18 WHEN 9 THEN SRC9NAME
19 WHEN 10 THEN SRC10NAME
20 WHEN 11 THEN SRC11NAME
21 WHEN 12 THEN SRC12NAME
22 WHEN 13 THEN SRC13NAME
23 WHEN 14 THEN SRC14NAME
24 WHEN 15 THEN SRC15NAME
25 END
26 FROM
27 PlanDBF p
28 Where
29 p.PLAN_NUM = s.PLAN_NUM
30 ) as SourceName
31 FROM
32 SourceDBF s
33 Where
34 SOURCE_NUM NOT IN (
35 SELECT DISTINCT
36 ClientSourceId
37 --SourceName
38 FROM
39 Statement..ClientSources
40 Where
41 ClientId = @ClientId
42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Any help will be appreciated.
Regards
Karen
I am getting error [[Msg 116, Level 16, State 1, Line 7 .Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.]] for the below script.
==============================================
Declare @mSql2 Nvarchar(max)
SET @mSql2= (select * from Tablename)
================================================
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store   Type   Sales
11 Â Â Â Â Â A Â Â Â Â 1000
23 Â Â Â A Â Â Â 1980
23 Â Â Â B Â Â Â 50
5 Â Â Â Â B Â Â Â 560
I want to filter the store in "where clause" Â where
1)- if the store has type A and  B, then assign only A
2)- if the store has  type A associated with it then assign A
3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type
from table1
where (TYPE]= Â (case when [TYPE] in ('A','B') then 'A'
when [TYPE]='A' then 'A' else 'B'end))
GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
Hey gang,
I've got a query and I'm really not sure how to get what I need. I've got a unix datasource that I've setup a linked server for on my SQL database so I'm using Select * From OpenQuery(DataSource, 'Query')
I am able to select all of the records from the first two tables that I need. The problem I'm having is the last step. I need a field in the select statement that is going to be a simple yes or no based off of if a customer number is present in a different table. The table that I need to look into can have up to 99 instances of the customer number. It's a "Note" table that stores a string, the customer number and the sequence number of the note. Obviously I don't want to do a straight join and query because I don't want to get 99 duplicates records in the query I'm already pulling.
Here's my current Query this works fine:
Select *From OpenQuery(UnixData, 'Select CPAREC.CustomerNo, CPBASC_All.CustorCompName, CPAREC.DateAdded, CPAREC.Terms, CPAREC.CreditLimit, CPAREC.PowerNum
From CPAREC Inner Join CPBASC_All on CPAREC.CustomerNo = CPBASC_All.CustomerNo
Where DateAdded >= #12/01/07# and DateAdded <= #12/31/07#')
What I need to add is one more column to the results of this query that will let me know if the Customer number is found in a "Notes" table. This table has 3 fields CustomerNo, SequenceNo, Note.
I don't want to join and select on customer number as the customer number maybe repeated as much as 99 times in the Notes table. I just need to know if a single instance of the customer number was found in that table so I can set a column in my select statement as NotesExist (Yes or No)
Any advice would be greatly appreciated.
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
How can you modify the crdate in sysobjects?
View 1 Replies View RelatedI am not aboe to view the userdefined tables in sql 2005. not sure whether my user dont have permission to view. how to view my user permission or not ..
another one , is there any table to find system table instead sysobjects,information_schema in 2000.
Any idea what are different types in sysobjects table? How can I seperate views only?
select distinct type from sysobjects
type
----
P
U
S
V
K
D
R
I would very much like to create a trigger on the sysobjects table in my database. When I attempt to do so, I get a permission error. Is there some way I can change the permission setting to allow me to do this?
Thanks.
HI there , i have recently upgraded my publishers to 2005.
Transactional replciation seems to be running fine.
However in SS2000 i always used to query sysobjects on replinfo to see if a table was replicaed and how it was replicated, 1 = snapshot , 3 = transactional etc.
However after upgrading replinfo on sysobjects is 0 for all my articles, whats up with this ?
Must i use sp_helparticle only now? Also how does the system know if an object is replicated now , i used to think it used the replinfo column on sysobjects, how is this different in 2005?
Thanx
Hi all,
I have a question about accessing a specific column from the database.
I know when we use: select name from sysobjects would select all the table names and the constraints from the database. I want to select all the column names in each of the table. How can I go about doing this.
Thank you
Hi there,
Once in a while, I find down there are some missing objects in production database. Because we share the sa password with couple of developers, therefore, it's hard find down who did it. So, I try to create a trigger in sysobjects table to prevent this problem, however, I keep getting the error message "error 229: create trigger permission denied on object 'sysobjects'"
although I log in using sa. Can someone give me some suggestions how to prevent this from happening beside using trace profiler and also why do I get the denied message when create trigger on sysobject even with sa login.
Thanks in advance
hi,
can any one tell what is the difference between data in xtype and type columns in sysobjects?
Hello everyone.
I'm facing to a serious problem.
I wanted to migrate SQL 2000 -> SQL 2005.
First of all, I tried to attach my *.mdf file to SQL 2000. but It was not only failed but also SQL 2005 failed to recover my *mdf file.
Now, I think my *.MDF file has system tables for SQL 2005 because when trying to attach *.mdf to SQL 2000 engine send the following message :
--------------------------------------------------------------------------
Msg 906, Level 22, State 2, Line 1
Could not locate row in sysobjects for system catalog 'Sysobjects' in database 'rudol.net'. This system catalog should exist in all databases. Run DBCC CHECKTABLE on sysobjects in this database.
--------------------------------------------------------------------------
Also, I tried again to attach the *.mdf to SQL 2005 but SQL 2005 doesn't really works.
Stupidly, I didn't back up the *.mdf file.
How do I attach it to SQL 2005 or recovery "sysobjects" table for SQL 2000?
Please answer.
P.S.
Forgive my terrible English. English is not my mother tongue.
HI friends,
i have deleted the 'sysobjects' table by accident. then follows the problem.
1. default value constraint not working
2. auto increment constraint not working
is that because of this sysobjects problem.
help me out to fix this problem...
hav fun, njoy
sundaram
Hi,I'm using "select [name] from sysobjects where OBJECTPROPERTY(id,N'IsUserTable') = 1" to get all user-defined tables(about 17000tables)and found out there are tables missing in sysobjects.How can I refresh/rebuild sysobjects table?is there a better way to get a list of user-defined tables?thanks,
View 2 Replies View RelatedEveryone,
Is there a reference to describe the relevance of the Category column in the sysobjects table? I'm trying to track down stored procedures that are set to automatically run at startup and the only thing I can find that is consistent about those that are set to autorun is that they have a value of 16 in the Category column. Is this consistent? What else may I glean from this column?
Thanks
Tim
I have a question about the xtype field in the sysobjects view. What do these characters mean? I think I've found lists, but are they complete?
What is the difference between FN and FS?
Hey all,
Not sure how I did this, but I dropped a table and it still shows up in sysobjects. This breaks a job that rebuilds indexes because the table doesn't exist. I tried recreating and dropping the table, but the extra entry is still there. Would it be okay to just delete the row from sysobjects? This has happened in a 2000 and 2005 db at the same time.
Thanks for any advice.
Reposted from 2000 area per suggestion. The site is running SQL 2000 build 2187.
I've got an 'accidental client' (sister company without a DBA at the moment) who has a problem that I've never seen before. They have a table that SQL claims to not exist in sysobjects. But it does exist in sysobjects as a User Table:
quote:select * from MyTable
Msg 604, Level 21, State 5, Line 1
Could not find row in sysobjects for object ID 55671246 in database 'MyTable'. Run DBCC CHECKTABLE on sysobjects.
dbcc checkdb ('MyDatabase')
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable2'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable3'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable4'. Check sysobjects.
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable5'. Check sysobjects.
DBCC results for 'routingengine_historical'.
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 310 rows in 21 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 839 rows in 20 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 470 rows in 35 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 3 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 145 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 19 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 421 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 4 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'MyDatabase'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
dbcc checktable ('MyTable') with ALL_ERRORMSGS
Msg 2501, Level 16, State 1, Line 1
Could not find a table or object named 'MyTable'. Check sysobjects.
dbcc checktable ('sysobjects') with ALL_ERRORMSGS
DBCC results for 'sysobjects'.
There are 483 rows in 14 pages for object 'sysobjects'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I've fully verified the correct existence of the table and how it links to the object ID by running statements like:
quote:select * from sysobjects where name = 'MyTable' and type = 'U'
select object_id('MyTable')
select object_name(55671246)
Odd that when selecting data from the table the error refers to the database by the table name. Running checktable on sysobjects shows no errors.
Running sp_help 'MyTable' works and shows the expected results as far as columns, indexes, and such. The table structure is fully visible in Management Studio and can be visually explored. Only when querying data is there an issue.
All tables are owned by dbo.
Ideally I need to recover these tables. If that is impossible, I'd like to remove the tables and cleanly free up all the related data pages.
Any thoughts?
Hi,I'm trying to determine with my program whether or not a given databasesupports a given feature set. To do this I'm querying for certain storedprocedures in the sysobjects table and if they are present, making theassumption the database will support the given feature. The problem is Ican't find a certain stored procedure in the sysobjects table, even though Iknow it exists and can see other similar procedures using:select * from dbo.sysobjects order by nameIts as if all of my other stored procedures are in the sysobjects tableexcept this one, the one I'm specifically querying for. Are there certainreasons why a proc won't appear in the sysobjects table? Is this somethingI need to fix?Thanks,Robin
View 4 Replies View RelatedCould anyone, please, explain to me why I have negative values inRowModCtr column in sysobjects table? I have tested that after I updatestatistics the RowModCtr column is reset to 0. But why do I havenegative values in the first place? Thx.
View 2 Replies View RelatedHi All,
I've written a scalar function in C# for the MS SQL Server 2005 that has to have access to two databases in one instance of a server ("database1" and "database2"). I got information from database1, calculate the name of the database2 and got information from user's tables in the database2. But when I'm trying to get informatin from "sys.tables", "sys.columns", "sysobjects" I always get an error "System.Data.SqlClient.SqlException: This statement has attempted to access data whose access is restricted by the assembly."
All databases are inside one server, I can run this quieries in SQL Server management Studio - could anybody help me what should I set/change to get metainformation from another database in the same server (in fact I need a list of fields of a curtain table).
Thanks,
Alex Gerasimov
P.S. Atrribute [SqlFunction(DataAccess = DataAccessKind.Read)] is in the applicaiton.