Viewing Database Via Query Analyzer And/or Enterprise Manager
Sep 20, 2004
We are running SQL 2000 SP3 on a Win2K server with multiple user databases. Our current problem is the ability of users to view the database objects via the native tools.
Example: User Bob
Bob has the following rights:
DB A – datareader/datawriter
DB B – datareader/datawriter
DB C – datareader
DB D – datareader
DB E – datareader/datawriter
The user has no builder rights or server roles.
In Enterprise Manager, the user can not see any databases. In Query Analyzer, the user can only see DB A, B, and C. Anyone seen this problem before and/or have suggestions?
I am using SQL Server 2000.I have created a new database in Query Analyzer but I cannot see it inEnterprise Manager.I then created a new database in Enterprise Manager, but I can't see it inQuery Analyzer.How do I get these applications to see the databases that the other creates?Thanks,Greg
How can I force Enterprise Manager and Query Analyzer to use TCP/IP? While my applications connect instantly to the database after I started using the NTL parameter in connect string, these 2 applications take forever and a number of retries to connect.
Hi All,What are the pros and cons between using Enterprise Manager or QueryAnalyzer for my queries.Currently I use Enterprise Manager because I prefer the interface andonly use Query Analayzer when queries time out in Enterprise Manager,but I'm sure there must be more to it.Regards,Ciarán
I run a stored procedure manually each day without fail using QueryAnalyzer.I.E. I right click in the browser window on the storedprocedure hit open and execute. I am trying to get my "DBA" toschedule this job in Enterprise manager and they keep getting syntaxerrors that make no sense and keep asking me to fix the code.Is there any difference in how the job scheduler executes a procedurevrs doing it in Query analyzer via Open/Execute that requires adifferent SQL syntax??? Im thinking that this problem is not the codebut a permissions issue What are your thoughts?
The row counts I am getting from SQL Enterprise Manager and QueryAnalyzer are different. When I double click a table in EnterpriseManager it gives me a row count of say 1000. However, when I select *from that same table in Query Analyzer it returns 1028 rows. Anyoneknow why or better yet, how to fix it? Thanks!
I downloaded the trial version of sql server 2005. but after installation, I couldnt find the Enterprise Manager and Query Analyzer. Any idea about that? Thanks.
Our installation has two instances of SQL Server 7. We have the client tools associated with SQL Server 7 on our NT desktop. We will be implementing two servers running SQL Server 2000 (SMS).
Ideally, on our NT desktop we would like to have one set of client tools (EM, Query Analyzer, etc.) for supporting both versions of SQL Server.
Do we need both versions of client tools on our desktop?
Will SQL Server 2000 client tools support both versions of SQL Server?
I have a project that consists of modifying a report in Crystal. The problem is that I know very little about SQL and I need to add some data items to Crystal using SQL. The guide that I was given refers to Enterprise Manager and SQL Query Analyzer but they have since upgraded to SQL Server Management Studio. Can anyone translate the following statement into SQL Server Management Studio so that I can complete this process?
Best regards,
We will now edit the View on the MS SQL server. Open Enterprise Manager and find the cst_adEnroll_MyInitials _vw in the Views section of the database. Right-click, select Copy, then go to the menu bar and Select Tools and SQL Query Analyzer. Once Query Analyzer opens: Change CREATE to ALTER before the view name.
I am try to do a query of dates. I have an access adp front end with sql2000 backend I am trying to query vacation dates. the start date month and day is based on month(hiredate) & day(hiredate) i need to find the number of days taken during a specific period i have to use entering start date and end date and days.
i am trying a query that returns sum of days before the end date changes.
it works in sql analyzer but doesn't when i paste it in EM doesn't want to save it. it says error by if..
heres the code.
SE EMPLOYEE
IF { fn CURDATE() } > (SELECT distinct cast(Year(dbo.[Employment Information].[Hire Date]) as varchar(30)) + '-' + CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) FROM dbo.[Employment Information] INNER JOIN dbo.[Employee Vacation] ON dbo.[Employment Information].[Employee ID] = dbo.[Employee Vacation].[Employee ID])
BEGIN
SELECT dbo.[Employee Vacation].[Employee ID], SUM(dbo.[Employee Vacation].[Number of Days]) AS [Days Taken] FROM dbo.[Employee Vacation] INNER JOIN dbo.[Employment Information] ON dbo.[Employee Vacation].[Employee ID] = dbo.[Employment Information].[Employee ID] WHERE (dbo.[Employee Vacation].[Start Date] BETWEEN CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(YEAR({ fn CURDATE() }) AS VARCHAR(30)) AND CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(YEAR({ fn CURDATE() })+ 1 AS VARCHAR(30))) GROUP BY dbo.[Employee Vacation].[Employee ID] END
ELSE BEGIN
SELECT dbo.[Employee Vacation].[Employee ID], SUM(dbo.[Employee Vacation].[Number of Days]) AS [Days Taken] FROM dbo.[Employee Vacation] INNER JOIN dbo.[Employment Information] ON dbo.[Employee Vacation].[Employee ID] = dbo.[Employment Information].[Employee ID] WHERE (dbo.[Employee Vacation].[Start Date] BETWEEN CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(YEAR({ fn CURDATE() }) - 1 AS VARCHAR(30)) AND CAST(DATEPART([month], dbo.[Employment Information].[Hire Date]) AS VARCHAR(30)) + '-' + CAST(DATEPART([day], dbo.[Employment Information].[Hire Date])- 1 AS VARCHAR(30)) + '-' + CAST(YEAR({ fn CURDATE() }) AS VARCHAR(30))) GROUP BY dbo.[Employee Vacation].[Employee ID] END
Greetings all. New user here. I'm just getting started with using sql query's with our cisco voIP call manager database. Although cisco has a utility "CDR" which runs pre-defined reports; it's not enough. So far there have been several request for specific data which "CDR" cannot compile, in which we have to use analyzer. Unfortunately I'm not versed and need a little help. It's been asked I pull a report from call manager which shows all "forced authorization Codes" cisco calls these client matter codes. Here is the query I tried using. It pulls all user data but the client matter code column is empty. I tried with * and *.* inside brackets as well.
select * from CallDetailRecord where clientmattercode=''
Can anybody help so I can get the records of who has what particular in use client matter code?
thanks to all in advance, I hope to learn as much as possible from the experts.
I am trying to run queries against any of the user tables in my MS SQL 7.0 database. I get a message the Query Designer encountered a query error. We have tried rebooting the SQL Server and I am still getting these messages. Also, the SQL error logs look fine - all database maintenance are running successfully including the DBCCs which show no errors. Any help would be greatly appreciated as we are to go into production in a few days.
Iam trying to see the rows using e/m i.e by right clicking tables and choosing return all rows option but it gives me an error but if i chose the other options(i.e. design table etc) it works well.only that return rows option in all the databases doesn't work.
Again iam able to retrieve rows from query analyzer.
The error which it gives is:
An unexpected error happened during the operation.
[Query]-Query Designer encountered a Query error:Unspecified error.
Please if anyone knows the solution mail it to l_muthu@hotmail.com
I've set the Query time-out parameter for my default MSDE instance to 1 sec (through the Enterprise Manager) and since the restart of the server I can't connect it through any client tool. Does anybody know how to return the default value of Query time-out (600s) without using enterprise manager?
We recently upgraded to Version 7.0 and the Enterprise Manager on the client is not showing the database size or the information in regards to the last backups. Has anyone else experienced this that can offer a solution?
I am trying to solve this problem for the past 1.5 weeks and it is driving me crazy :(
I can't connect to my SQL database using Enterprise Manager, Query Analyzer, Visual Studio 2003 .NET or a .aspx that I created for testing.. it runs perfectly on my domain but it doesnt work on my computer, on the browser I get this message: SQL Server unavailable or access denied. It is the same message I get when I try connecting to my db using EM, QA, VS.NET etc :(
I can ping my sql domain, so I know I have access to it and it is working.. I can successfully connect to my personal SQL server running on my machine. (SQL Server Personal Edition running on Windows XP )
I tried doing everything I could,
I already selected TCP/IP as my client only protocol; I tried installing the lastest MDAC (v 2.8); I tried every other port on my TCP/IP client setup like 14333 or 11433 or 11143 etc; I tried every other port on the alias I set for my sql address; I tried downloading the evaluation version of MS SQL2000 and updating it to SP3; I tried reinstalling several times my MS SQL2000 (I use the personal edition on my windows XP) and updating it to SP3; I tried connecting to my sql address domain and directly to its IP; And I tried a lot more resolutions that I can't remember right now, but it didn't work :(
I've been searching all forums/google etc looking for someone with my problem but I still didnt, everyone who had a problem connecting to their DB using EM or QA resolved it by doing "this or that", I did exactly the same "this or that" on my computer/config and it didn't resolve my problem. :(
What am I doing wrong? I can't connect to my database from my personal computer :(
I have never used enterprise manager before, so I don't know if it is a problem on my personal computer or on my sql server address on internet.. Is there anyway to find a free test ms sql database with like 1mb just to try and find out if I can connect to a sql database at all using EM or if the problem is here on my computer?
I cannot seem to connect to this Instance locally which was i able toin the past. I can connect to the instance from out side. The instanceis running and the DBs in the instance are available and usedsuccessfully by the applications but i cannot connect to the instanceusing the enterprise manager.the error i get isSQL Server does not exist or access deniedrubeel
I have a new installation of SQL Server 2000 Dev Edition on a Win2K3 Standard Edition Server that I used for development. I just set this machine up in th last week and installed all Win2K3 patches and then installed SQL2K and SP3a. I have a single named instance. I just noticed today that I cannot view table data or use the Query part of EM. When I right click a table and select Open Table->Return All Rows it gives me an error dialog "An unexpected error happened during this operation". While the EM is diplaying this dialog the EM screen looks like Internet Explorer and says "Action Canceled - You might not have permission to view this directory or page using the credentials you supplied." I believe that this is a EM issue as I cannot view table content on other remote server. ANy ideas? Might this be an IE security patch disallowing some connectivity ?--Frank--Message posted via http://www.sqlmonster.com
Hi Everybody,I use MS SQL server 2000 enterprise manager on a windows server 2003.I created a new database in enterprise manager, and try to restore the new database from a .bak file. I went to ---->restore database. I checked "from device" and select the right path to the .bak file.Then I click ok, but there is an error pop up." tend to override existing database", so I went to --->option, and checked "force override existing database". then I click ok, but get an error look like this http://ewwa.lo7.net/error.JPGI thought it was a problem with my .bak file. So I try to use another .bak file which I am sure it is 100% good. It prompt the restore is sucessfully. But when I checked the db, there nothing there(No tables).I think there must be something wrong with my enterprise manager's configuration.Do you know what happen with my enterprise manager? Thank you a lot in advance:)!!
I know how to create a database using enterprise manager in sql server 7 or 2000. But i want to create a database where i have to set the unicode as UTF8 for the whole database. is it possible? if yes, how can i do that.
I have Enterprise Manager on my local machine. For the last twelvemonths it has been connecting without problem to my online SQL Serverdatabase provided by my ISP.Three weeks ago the ISP applied some sort of extra securityarrangements to their SQL Server to allow access only through port1433. they have told me to configure an alias using Network Client andto register this alias in the usual way using my Enterprise Manager.My problem is that despite creating the alias in exactly the wayinstructed I just cannot connect to online database. I keep gettingthe SQL Server does not exist or access denied when I try to connect.As a result of advice obtained I have tried using the lite version ofthe 'littleADmin' tool available fromhttp://www.mylittletools.net/scripts/en/mla_sql/Using this useful looking software I find I cannot connect to theonline SQL database when it is installed on my own local machine butif I install it on my website it does connect without problem to myonline SQL. Server databaseHowever I need to use my more powerful Enterprise Manager to connect,my question (at last!) is:1. Is there likely to be a problem trying to connect to my onlinedatabase from Enterprise Manager on my local machine as a result ofthe new security arrangements. In other words, is there somethinganalagous to what is happening with the 'littleAdmin tool' wherebyusing Enterprise Manager I cannot connect from my local machine forsome reason obvious to everyone except myself??or2. Is it possible that there is some problem arising from Net Client?(the aliases appear in Enterprise Manager OK)I know my ISP should be answering this sort of question but they justdon't seem to know. I need to connect even if only to download mydatabase and take it to another ISP,Best wishes, John Morgan
I have several clients who are reporting a negative file size on their database devices in Enterprise Manager. The sp_helpdevice procedure reports the size correctly. Any suggestions?
I have just put up an Webserver with SQL Server 2000. Everything is working fine on the remote machine Thru TS .. i can do every administrative things .. But i m facing problem to connect my remote databse server thru the Enterprise Manager .. It Gives me Cant Connect Database Server " Connection Failiure " when i try to connect my remote database server thru Ip address ,.. Please some one help me out to make connecting database server thru Enterprise Manager ..
Hi,I need help. I transferred my database from one server to another byusing attach detach process.I created all the jobs which existed in theold server in the new server. Everything looked fine but a couple ofjobs is a giving me some problems. When I try to edit the job step Icould see that the database dropdown list doesn't contain the databasename(which I transferred from the old server)Does anyone have any idea about it.ThanksArun
I have SQL Server 2000 with SP4. When we take backup from Enterprise manager its fails with error "timeout expired". But when we take backup from query analyzer it works.
It seems that through enterprise manager 30 sec is the non configurable timeout parameter set. Checked sysprocesses table during backup but there was no blocking during that time at least for that spid.
Is there a way to find out which process is blocking the database backup? Also, what are the possible scenarios where database backup can be blocked.
How to delete/drop all the tables from SQL Server Database without using Enterprise Manager? I tried using DROP Tables, Truncate Database, Delete and many more but it is not working. I want to delete all tables using Query Analyzer, i.e. through SQL Query.Please help me out in this concern.Nishith Shah
I have installed RS 2005 in my machine. If some one else wants to view reportmanger in my machine, does he have to install ssrs 2005 in his machine too? I can acces my report manager from my machine by just typing http:\localhost eports or http:\mymachine eports
The other persone also wants to access my report manager by just typing http:\mymachine eports.
Also if there are more than one client machines , do they need to have RS 2005 in each machine.
