SQL Server 2008 :: Finding Database Connections Not Visible
Jul 15, 2015
I need to find a way to identify SPIDS where queries are connected to certain databases using the 4 dot notation instead of actually being connected to the database in question.
For instance, I connect and my default database is MASTER. In MASTER, instead of typing USE MyDb ahead of my query, I just type my query as:
SELECT Col1, Col2
FROM MyDB.dbo.MyTable
The above is the simple scenario. We usually have queries connecting 3 or 4 user dbs together. So database1 might have a query pointing to database2. What I need is a way to identify the SPIDS that are connecting to database2 despite being in database1.
I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.
SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t8 INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%Status%' ORDER BY schema_name, table_name;
Is there anyway to find the permissions assigned to user. What level of access the user has in each db and the tables & is he able to see jobs. Each object and permissions
In our Production db we have all most all tables have the column created or createdate.
I need to find out all tables without the created or createdate column
SELECT t8.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name FROM sys.tables AS t8 INNER JOIN sys.columns c ON t8.OBJECT_ID = c.OBJECT_ID
Below I have a query which list the relations (constraints) between tables.
I want to list all the relations which are visible in the Database Diagrams.
The list is not complete. How do I get a complete list ?
-- -- Query to show the relations (constraints) between tables. -- This does not show the complete list -- SELECT A.constraint_name, B.table_name AS Child, C.table_name AS Parent,
My backups are failing sometimes.My db size is around 400 GB and we are taking backup to the remote server. Free size available on the disk is showing 600 GB but my database full backup run more than 10 hrs and failed. The failure reason is there is not enough space on the disk.
What could be the possible failure reasons? It has more than the the database size on the backup server but why it is showed that msg on the job failures. I noticed same thing occurred couple of times in the past.
Is there any way to find how much the backup file will be generate before we run the backup job?
i.e. If we run the full backup of test1 database now, it will generate ....bak file for that test1 db
currently we are using maintenance plan and with compression (2008R2) and the database has TDE enabled
I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
select Computername from dbo.ADAssets where Computername like 'AIRLBEOF3565%'
I get both records,but if I do this
select * from dbo.ADAssets where Computername in ( 'AIRLBEOF3565 CNF:4e926e06-6f62-4864-aebd-6311543d', 'AIRLBEOF3565' )
I only get AIRLBEOF3565
So the big picture is that I need to compare 2 tables to find records that match & don't but that I get matches that shouldn't be & matches that aren't.
This question is a long shot because it requires familiarity with a specific application called ChefTec which is a program for the restaurant industry, but I'm giving it a try because I have nowhere else to go to get the information I need. I am the developer for a competitor of this program, and some ChefTec users want to switch to our product and have their data, which is in SQL Server 2005 or 2008, converted.
My plan is to create a utility that the users could run to convert the data (written in Delphi, though that doesn't really matter). The problem is that I can't determine the connection string that ChefTec uses. It doesn't seem to be stored anywhere obvious (an ini file or the registry). When ChefTec is installed it either installs SQL Server or gives the option of using an existing instance, but there's no way for me to know how it was set up on any given machine. The program must be using some internal logic to create the connection string but that's not accessible to me.
1. I created a maintenance plan using Visual Studio 2013 (nothing fancy pretty basic) 2. Using ssms 2014 I imported it (the dtsx file) under the Integration Services and it appeared there successfully 3. I connected to the Database Engine again using ssms 2014 - my expectation was to see it under the Management > Maintenance Plans folder but it was not present.
I am creating a report where there are 3 tables in one report which have visibility set as an expression so that when a certain criteria is selected in the "Report" parameter, a different table is shown below. This works as expected.
When table 3 is shown, I want a new parameter to be visible to the user called "Ward" where the user will select the ward they are interested in and the table will display data below just for the ward selected. However, I don't want the ward parameter visible when either of the other 2 options are selected. Is this possible and how to do this?
I am trying to find the list of active connections on SQL SERVER , more and more granular way ...like below , I know a way using active monitor or Profiler or some DMVs give me some list , but is there a way to find what is the server name ,instance name connectedc to it , who is the clinet connected to it , whats the application being used now?
what database it is connected to it ? what login being used to connect ? what table and column being used right now and what kind of DML or DDL are happeming ?
I want to create a report and find Dependancay evaluation so that we will re architech or re design the existing databases ...
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30 208203492013-09-032014-09-01 308203492012-09-042013-09-02 408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
I'm having a problem to which I'm sure the answer is simple...
All I want is a list of databases on my server with their allocated size and the free space within. Something similar to the first table that sp_spaceused gives you but on a server wide scale.
As I say, I'm sure there's a simple solution out there, but alas Google has failed me.
I've developed an asp.net 2.0 web app with vs 2005 and am using a SQL Server 2005 Express database. The app works fine locally, but after uploading to the remote web server the following error occures: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
How do I go about granting remote connections to SQL Server Express?
Does the web server have to have SQL Server Express installed in order to run apps that utilize SQL Server 2005 Express databases?
Do I grant access locally on my machine or do I have to be on the web server to grant remote connections?
I've been using .net 1.1 with access databases for the past couple of years, so this is all new to me.
I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:
ALTER DATABASE MYDBNAME SET PARTNER FAILOVER
, ServerB becomes the principal, it means that mirroring works correctly.
My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :
Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:
ALTER DATABASE MYDBNAME SET PARTNER FAILOVER
, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:
Microsoft SQL Native Client error '80004005'
Cannot open database "MYDBNAME" requested by the login. The login failed.
This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.
When I swap server, I execute on server ServerB command:
ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,
my IIS 6.0 application automaticly turn back to ServerA without any action on my side.
I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client
I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB] MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf') GO
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission] FROM [sys].[database_permissions] sec JOIN [sys].[database_principals] prin ON sec.[grantee_principal_id] = prin.[principal_id] WHERE sec.class = 0 ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission User1 GRANT CONNECT User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
if you can restore a database to Server B using Server A as the service. Meaning we would issue the command on Server A but somehow point to Server B as where we want the restore to happen.
The backup file would be in a location independent of both servers.
1) I can't get the 'copy database' function to work from SQL Server 2008 to SQL Server 2005. I connect ok. Everything goes to the last step and then it fails.2) I cant get a SQL server 2008 backup to restore on SQL SEerver 2005 either. The only way I know that works is to script the creation of all tables then export and import. This does work. How can I get the 'entire' database, structure and data, from 2008 to 2005? ThanksSQL newbie.
I was having issue with one of my databases in SQL EXPRESS. It is offline this morning …it said “Database 'MyDB' cannot be opened due to inaccessible files or insufficient memory or disk space. When I checked the error log ..it only said “FCB::Open failed: Could not open file D:DatabasesMyDB_Data.mdf for file number 1. OS error: 32(failed to retrieve text for this error. Reason: 15100).
I did alter database offline and and online ..it works for me( I can access the database again but I need to find the cause of this issue )I checked the max memory setting is still by default .. not limited for SQL ..it could be the cause ?
Just heard about the coming release of SQL Server 2008. Anyone here got some ideas on what are significant new features in SQL Server 2008 Database Engine compared to SQL Server 2005 Database Engine? Would be very interesting to know that.
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
Is it possible to view the Connection String information of a remote login/session? I want to know if the login is looking-up the database server via IP address, servername (NetBIOS name) or fully-qualified domain name (FQDN).
Using these DMVs I can get a lot of relevant information:
sys.dm_exec_sessions Program Name (eg. Microsoft SQL Server Management Studio), Client Interface Name (eg. .Net SqlClient Data Provider) sys.dm_exec_connections Net Transport (eg. TCP), Client Net Address and TCP Port
but not how the server's IP address was resolved. Is the connection string ever sent by the client to the server, or just used for DNS lookup?
I have created a new database using Visual Web Developer and wanted to manage it using either the SQL Server Management Tool or MS Access via ODBC. Niether of these tools will connect to the database. The database file , called srx.mdf, the connection string is Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrootSRExchangeApp_Datasrx.mdf;Integrated Security=True;User Instance=True I can however connect successfully to the master file, the conenction string for this is Data Source=.sqlexpress;Integrated Security=True Any help appreciated.
As SQL Server 2016 category is not created so I am posting this query in SQL Server 2014 window.
I have enabled Query Store in SQL Server 2016 using ALTER DATABASE and later tried with Properties of Database too. But I am not able to see the Query Store subfolder under database.
UAT environment : SQL Server 2008 R2 SP3 Enterprise Edition
SANDBOX environment : SQL Server 2008 R2 SP3 Standard Edition
I have a database backup (.bak) that was taken from UAT environment that has CDC enabled on some tables. I want to restore that database into my SANDBOX environment which does not support CDC (because of standard edition). The restore process fails due to this incompatibility. Is there any way to restore without CDC (I dont CDC enabled on my SANDBOX; just my data from the backup) ?
Our database crashed this morning and went into recovery mode.how I can track the progress of the recovery to determine how long it might take?The error log shows that it started up all the databases, then shows the recovery messages fr the msdb database, then shows that sql server is ready for client connections. I don't see any messages about my database recovery or the number of transactions to roll forward or background. If i run the sys.sp_readerrorlog and search for my database name, the only line returned is the starting up database message.
I do expect the database to take a while to recovery as it is about 8TB, there is plenty of free disk space about 3TB.The database started recovery while a transaction log backup was running so that backup failed,the last transaction log backup was taken 2 hours before recovery started. The last full backup completed about 5 days ago. The transaction log backup occurs every 2 hours and is typically around 16GB.
