Transact SQL :: Script To Collect TPS In Instance Level?
Oct 22, 2015I have a requirements to collect Transactions per second from a sql server instances level. Any script to collect TPS in the instance level ?
View 5 Replies
ADVERTISEMENT
Splitting DBA Role At Instance Level
Apr 23, 2015I work for a central IT facility which provides services to all the departments in the organisation. Management are proposing to extend the current database hosting service to a full blown DBA service as well. The role is to be split between the hosting team, who had also been providing high level functions like replication, and a dba team who will take on the database administration. There is to be a strict delineation of responsibilities with the hosting team looking after everything at the instance level and the database team managing the databases. The hosting team will retain the sysadmin role and grant sql permissions to the database team as and when they need them.
View 2 Replies View RelatedXML Schema - At Least One Instance Of Top Level Datatype Must Be Included In File
May 23, 2012I have designed an XML schema in which defines the data structures expected in an xml file that our software will import. This is used to validate the files.The current problem is that the validation passes any correctly formatted xml file that does not contain any of our data structures. Is it possible to put something in the schema that says that at least one instance of our toplevel datatype must be included in the file?. I know using minOccurs="1" but I can only see how to use with a definition of a datatype - not the xml file itself.
View 5 Replies View RelatedLimiting User Resource Consumption At The Instance Level
Oct 9, 2007We are planning a shared SQL Server 2005 enviroment where users can create databases/applications for themselves and/or departments. With the consideration that there can be multiple SQL Servers on a box, can each instance limit a user's disk space? Thanks for any enlightenment.
The Service Pack Level Of SQL Instance Is Not Upto Date
Jul 13, 2007I have received an alert on a server that is running SQL 2005. 'The service pack level of SQL instance is not upto date'.
I have right clicked the connection properties and found that current version is 9.00.1399.06 How can I tell which Service pack the SQL 2005 is currently on?
Do you know of any articles to upgrade service packs?
Thanks.
Af
SQL Server Admin 2014 :: AlwaysOn And Instance-level Items
Jul 16, 2015How you are handling the replication of the many instance-level objects/items (logins, linked servers, server roles, database mail, operators, on and on) to the replicas in an AlwaysOn topology.
I'm especially curious about DBAs managing larger SQL Server environments. In my current environment, we have approximately 80 production SQL instances containing about 650 databases that require high availability and disaster recovery.
We use mirroring today and have a solid, home-grown solution for replicating the instance-level items from production to disaster recovery. AlwaysOn changes things a bit since we'll have multiple replicas and of course the database could be active on any one of those at any time. So my concern is about instance-level items being created in one instance but never deployed to the other instances participating in the AG group.
SQL 2012 :: Report GUEST User Access Status In All Databases On Instance Level
Apr 17, 2015Below query tells us if guest user is enabled or disabled in a particular database
SELECT dp.name, CASE perms.class WHEN 0 THEN 'Yes' ELSE 'No' END AS 'Enabled'
FROM sys.database_principals dp
LEFT JOIN (SELECT grantee_principal_id, class FROM sys.database_permissions
WHERE class = 0 AND type = 'CO' AND state = 'G') AS perms
ON dp.principal_id = perms.grantee_principal_id
WHERE dp.name = 'guest';
Do we have a query which can also add the database name to above query output? The output must have columns with data against Name,Enabled,Database name
RDA Push Error Another User Has Opened The Database With Different Instance-level Initialization Properties. [ 32 ]
Mar 3, 2008
Hi all,
I'm using VS2005 + SQL Compact 3.1 and Enterprise Library 3.1 May 2007
I can successfully create a db using pull method, when I try to push back data I got the following SQLCE error:
"Another user has opened the database with different instance-level initialization properties. [ 32 ]"
Before calling the push method I did
SqlCeDatabase eCE = Helper.GetDB() as SqlCeDatabase;
eCE.CloseSharedConnection();
SqlCeConnectionPool.CloseSharedConnections();
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
rda.InternetUrl = webSynchronizationUrl;
rda.InternetLogin = webuser;
rda.InternetPassword = webpass;
rda.LocalConnectionString = eCE.ConnectionStringWithoutCredentials;
but after the assignment the LocalConnectionString is different from ConnectionStringWithoutCredentials, there are much more ssce parameters.
What I don't understand is:
why I'm getting this error even after closing the connection ?
Since the error report [32] the ssce conflicting parameters shoud be AUTO_SHRINK_THRESHOLD, FLUSH_INTERVAL and MAX_DATABASE_SIZE
I tried to cut & paste the same values from LocalConnectionString into app.config connection string, at first It seemed to work, but now I got the same error.
Any help will be greatly appreciated
Thanks
Transact SQL :: Row Level Constraint
Aug 12, 2015I want to enforce a constraint on the table where , the same MenuId cannot have 2 different prices for the same date range (accidentally).I think CHECK constraint won't work.
View 4 Replies View RelatedTransact SQL :: Row Level Locking In Server
Apr 22, 2015We have one table.We have updated the status for one column to 1 and we did not committed the transaction. Can we do an another update on another row.
In below example i am updating GEO_D and transaction is not committed. Now my requirement is we have to update other records (not Geo_D). If try to update GEo_D it should wait.
IF OBJECT_ID('TEMPDB..##TEMP_STSTUS') IS NOT NULL
DROP TABLE ##TEMP_STSTUS
CREATE TABLE ##TEMP_STSTUS
(
ID INT IDENTITY(1,1)
,NAME VARCHAR(10)
,STSTUS VARCHAR(10)
)
INSERT INTO ##TEMP_STSTUS SELECT 'GEO_D','0'
[Code] ....
Transact SQL :: Case Expressions May Only Be Nested To Level 10
Aug 5, 2015I have a query which works absolutely fine when connecting to an actual server:
WITH CLAIMDATA AS(
SELECT DISTINCT
DB_NAME() AS DBName,
'UA' AS Client,
POLICY AS KMPONO,
[code]...
If i change the connector to REPLPROD (which is a linked server): From REPLPROD.XUNMDTAUAI.dbo.UPPOREP UP INNER JOIN REPLPROD.XUNMDTAUAI.dbo.UKKMREP UK
I get the error:
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
Transact SQL :: Retrieve Descendants With Depth Level
Nov 22, 2015I have a table of Affiliates, each Affiliate can have a ParentId, which refers to another Affiliate. How can I retrieve all children of a given Affiliate, along with their depth level? I have this query which selects the total number of descendants of a given Affiliate, but I need to select all the children + their depth level, up to a certain level.
WITH Descendants AS(
SELECT RootId = Id, Id
FROM Affiliates
UNION ALL
SELECT RootId, Af.Id
FROM Descendants
[Code] .........
What I want to achieve is a statement that has 3 parameters: RefererId, StartLevel, and EndLevel.
I need a statement that selects all the specified Affiliate.ReferrerId's descendants including their level distance from the referrer, filtered by the StartLevel and EndLevel (WHERE CurrentLevel >= StartLevel AND CurrentLevel <= EndLevel ).
Transact SQL :: Group By On A Transaction At Line Item Level?
May 20, 2015I need to group by transactions at line item level. As the each item in the transaction may belong to different category and I'musing case statement to identify particular category for one column , I'm using sub-query to retrieve the results at item level.
Here is the code:
SELECT
fs.TransactionId
,fs.DateKey
,dc.FirstName
,dc.LastName
,dc.Company
[code]....
Due to case by statement and having multiple category I have to use multiple group by.
select TransactionId
,DateKey
,FirstName
,LastName
,Company
,City
,Sum(CategoryAPurchase) CategoryAPurchase
[code]....
Transact SQL :: Select Lowest Level From Hierarchical Data
Jun 22, 2015I have hierarchical data such as:
Id Level ParentId
1 0 1
2 1 1
3 2 2
4 0 4
5 1 4
6 0 6
7 1 6
8 2 7
9 3 8
10 4 9
11 0 11
As you can see even the parent element has parentId(in this case id = parentid)
How can I select the lowest level data in the hierarchy and get this result:
Id Level ParentId
3 2 2
5 1 4
10 4 9
11 0 11
Transact SQL :: Left Outer Join And Transaction Isolation Level
Nov 30, 2015We have a service that inserts some rows into a parent table (P) and child table (C). This operation is atomic and performed within a transaction.
We also have a service that queries these tables such that rows are (should only be) returned from P where there are no children for that parent.
The SQL that performs this is simplified below:
SELECT P.SomeCol
FROM P
LEFT OUTER JOIN C ON P.PKofP_Value = C.PkofP_Value
WHERE
C.PkofPValue IS NULL
AND P.SomeOtherCol=0
Our expectation is that the query service should only return rows from P where there are no rows in C.
However, this seems not to be the case, and occasionally we find that rows from P are returned where there are matching rows in C.
We are sure that the process that inserts rows into P and C does so within a single transaction.
We have traced this with SQLTrace and can see the txn stag and committing and all operations using the same transactionid within the transaction.
We are running the default isolation level committed.
In SQLTrace we can see the query process start, the inserter process start and complete and then the query process continue (after presumably being blocked).
So how can the query process "miss" the child rows and return the parent from the above query?
Is it possible that, in this isolation level, the inserter process can block the query process such that when the inserter process commits and when the query process continues it does not see the child rows inserted because they were inserted in the table/index "behind" where the query process has already read - some kind of phantom phenomenon?
A Transport-level Error Has Occurred When Receiving Results From The Server. - After Installing New Instance Of SQL Server 2005 W/NO App Changes
Sep 27, 2006We've devoted a resource to this today, but I have to believe it's something easy that we're overlooking. The scneario is that we have a production Web application that until last weekend had a SQL 2000 back end. This weekend we installed a new instance of SQL 2005 and everything works (we tested in a sandbox environment, but someone must not have load tested enough) and never saw these exceptions. So, after the upgrade we now receive 100's of thexe SQL excptions per day:A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)Does anyone know what we've overlooked that's causing this issue?Thanks for any help!
View 2 Replies View RelatedCollect DB Information
Jan 20, 2008Hi,
We have a few Analysis Server databases. I need to go to each server/database and in a Word document write the information for each server/database. For example I have a database called Analysis Services Project 1 with Data Source dsIIBSW. I need to write it in a document. Is there a fast way of doing that?
Thanks
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
Transact SQL :: How To Transfer Logins And Passwords From One Instance To Another
Jun 11, 2015I need to transfer the logins and passwords from my prod server to my DR server. I've seen a number of articles about how to transfer logins; but, I'm not finding anything on how to transfer both logins and their passwords. SQL server R2008 R2
View 4 Replies View RelatedCollect Server Information
Jul 27, 2001I am trying to develop a sql script that will select information from statistical tables on several servers and build a report based on the information collected from each. What SQL statements are used to connect to another database or does anyone have an example of a script that collectes information from several servers?
View 1 Replies View RelatedCollect All ID Of Subordinate Groups
Dec 5, 2014There is a table with the id and categories Parentid. The challenge is this: you need to collect in each category one drain all the ID that concern it. For Example:
declare @t table (
[id] [bigint],
[Parentid] [bigint],
[Name] [nvarchar](50)
)
insert into @t
[code]....
Transact SQL :: BPA On A Cluster Using Default Instance And Static Port
Aug 25, 2015Having problems running the BPA for 2012. We setup using a clustered environment, SQL Server installed using default instance, and a static port not 1433. When I try to run the BPA using the instance name it says it doesn't exist, and won't let me add a port number. However, if I use MSSQLSERVER as the instance name it runs with no port specified and returns results. I assume that it doesn't require a port because the default instance would pull from the Browser Service and the using MSSQLSERVER is the same as running the default on a standalone server. Are those assumptions correct?
View 2 Replies View RelatedTransact SQL :: Script To Set DB Compatibility To Match Version On That Instance?
May 22, 2015We have an app that supports any version of sql beyond 2008. What we are finding is that users are upgrading to new versions of sql and just restoring their db on the new version of sql and not changing the compatibility level. I would like a script that would change the db's compatibility level to match whatever version of sql that they are using.
ALTER DATABASE db_name
SET COMPATIBILITY_LEVEL = (SELECT TOP 1 compatibility_level
FROM sys.databases
WHERE name = 'master')
Transact SQL :: How To Get All Databases Names With Its Owners In Server Instance
Jul 23, 2013I want to get all databases with it's owner Name ,I think I need to join following script result with sys.databases or sys.sysdatabases.I want a result set like this:
DatabaseName db_Owner
master sa
pubs valia
pubs eli
pubs maryam
AdentureWork eli
this script get all owners of the database that is in use:
Use DatabaseName
GO
SELECT members.name MemberName, roles.name RoleName,roles.type_desc,members.type_desc
Transact SQL :: Find Single Largest Table In Entire Instance
Nov 16, 2015I usually go for the largest datafile and then query in-there...But now I need to automate it for several instances... I need to be able with one script quickly retrieve the top 5 largest tables for the entire instance,not by database...
View 3 Replies View RelatedMerge Tables And Collect Origin Info
Apr 29, 2015If you have 2 tables with the same columns and you would like to see all distinct records in a result of a select and also the information in the records which table the record comes from (for instance: from table A or from table B or bot tables contain it) what should you do?
View 5 Replies View RelatedCollect Diverse Data Whilst Database Being Created
Jun 1, 2012I have just started in a brand new role where I need to collect a large amount of diverse data. Part of this data is whether the steps in a guideline has been followed ( a simple check box or yes/no) (but there are maybe 100 guidelines)
My IT department have suggested we use SQL to create the database, but this will obviously take time. In the meantime my boss is keen that I get on with the data collection before the database is ready. I do not want to repeat steps when I create the SQL database so is there any format you would recommend I collect my data in now that can be easily applied to the SQL database - ie if I used Excel, would that be best, and should I then try to keep potential SQL table data separate?
Looking To Collect Distinct Date Part Out Of Datetime Field
Mar 11, 2006from this, circdate being a datetime field:SQLQuery = "select distinct circdate from circdata order by circdate"I need the distinct date portion excluding the time part.this has come about when I discoveredI am inserting and updating some datetime values with the same value,but for some reason, the values are always off by a few seconds. I seta variable called SetNow assigned to NOW and then set the datetimefields to this SetNow variable. Then when I collect the distinct datetime I am assuming they will have the same values recorded incircdate, but no, they are off by several seconds. Makes no sense to meat all. I tried renaming the variable several times but it makes nodifference at all.any help appreciated, thanks.
View 5 Replies View RelatedDoes Sql Server Collect Stats On Full Table Scans?
Jul 20, 2005Hi all. Anyone know if sql server collects stats on how many fulltable scans a table gets and if so how do I get at those stats? Tryingto track down poorly indexed tables / processes and I am guessing thatsql server does have this data secreted away somewhere much like mydb2 and informix databases do.
View 1 Replies View RelatedMS SQL Server 2005: Collect Procedure For Dts Pipeline Generate Error
Nov 21, 2006Dear experts,
My MS SQL Server 2005 is generating the following error. may i know what's wrong with it?
"
The Collect Procedure for the "DTSPipeline" service in DLL "XXX:Program FilesMicrosoft SQL Server (x86)90DTSBinnDTSPipelinePerf.dll" generated an exception or returned an invalid status. Performance data returned by counter DLL will be not be returned in Perf Data Block. The exception or status code returned is the first DWORD in the attached data.
"
Thanks in advance for any assistance rendered.
pat
Integration Services :: Collect Data From Multiple ODBC Source
Jun 11, 2015I am able to collect data from Progress DB, using ODBC Connectivity. The problem I am facing is, i have to iterate thru multiple servers. How do i configure ODBC source dynamically. It creates problem. Using expression, i tried to set the connectionstring dynamically, but it fails.
View 2 Replies View RelatedCollect Missing Index Data Into Central Repository From Sys.dm_db_missing_index_group_stats, Groups, Details
Nov 2, 2007
Greetings:
I am trying to gather into a central location the missing index data from the sys DMV's for dynamic index creation in the next step. In trying to use a cursor, I get the following errors:
Msg 154, Level 15, State 3, Line 20
variable assignment is not allowed in a cursor declaration.
Msg 102, Level 15, State 1, Line 94
Incorrect syntax near 'Get_Data'.
Msg 16916, Level 16, State 1, Line 2
A cursor with the name 'Get_Server' does not exist.
Msg 16916, Level 16, State 1, Line 3
A cursor with the name 'Get_Server' does not exist.
Here is the SQL:
--CREATE PROCEDURE usp_Get_Missing_Index_Data
--AS
--Declare @Sql2 nvarchar(4000)
Declare @Sql nvarchar(4000)
DECLARE Get_Server Cursor -- gets a server name from a list of servers
for
Select MachineName from rsqlaudit1.DBStatistics.dbo.servers
Open Get_Server
Declare @Server nchar(20)
Fetch Next from Get_Server Into
@Server
While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)
BEGIN
DECLARE Get_Data Cursor
FOR
select @sql= 'select distinct id.*
, gs.avg_total_user_cost
, gs.avg_user_impact
, gs.last_user_seek
,gs.unique_compiles
from '+@Server+'.master.sys.dm_db_missing_index_group_stats gs
,'+@Server+'.master.sys.dm_db_missing_index_groups g
,'+@Server+'.master.sys.dm_db_missing_index_details id
where gs.group_handle = g.index_group_handle
and id.index_handle = g.index_handle
order by gs.avg_user_impact desc'
exec (@Sql)
Open Get_Data
DECLARE @Handle int,
@database smallint,
@object int,
@equality nvarchar(4000),
@inequality nvarchar(4000),
@Included nvarchar(4000),
@statement nvarchar(4000),
@avg_user_cost float,
@avg_user_impact float,
@last_seek datetime,
@compiles bigint
Fetch NEXT FROM Get_Data INTO
@Handle,
@database,
@object,
@equality,
@inequality,
@Included,
@statement,
@avg_user_cost,
@avg_user_impact,
@last_seek,
@compiles
While (@@FETCH_STATUS = 0) --and (@@FETCH_STATUS <> -2)
BEGIN
insert into rsqlaudit1.DBStatistics.dbo.Missing_Index_data
values (@Server,
@Handle,
@database,
@object,
@equality,
@inequality,
@Included,
@statement,
@avg_user_cost,
@avg_user_impact,
@last_seek,
@compiles)
FETCH NEXT FROM Get_Data into
@Server,
@Handle,
@database,
@object,
@equality,
@inequality,
@Included,
@statement,
@avg_user_cost,
@avg_user_impact,
@last_seek,
@compiles
Fetch Next from Get_Server Into
@Server
END
CLOSE Get_Data
DEALLOCATE Get_Data
GO
CLOSE Get_Server
DEALLOCATE Get_Server
GO
Any suggestions are appreciated.
Thanks,
Derek
Column Level Or Database Level Encryption/decryption....
Jan 16, 2008I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
High Level && Detail Level Design Documents
Nov 19, 2007
Hi,
AM in need of SSRS 2005 design documents for a project purpose. Can somebody let me know where can i find these documents? Thanks in advance