I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT
ES.login_name AS LoginName,
L.request_session_id AS BlockedBy_SPID,
DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec,
DB_NAME(L.resource_database_id) AS DatabaseName,
[Code] ....
missing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event.
Hi!This is a very simple question and I'm sure you guys will help me a lot.I'm using Visual Basic 2005 for programming. I have one table on my MS SQL 2005 database that has an int column with a counter that needs to be incremented when a user registers.So when reading the value I use a simple SQL query like this:
SELECT counter FROM companies WHERE company=0
then I store the value in a local int variable and then I increment it. Then I update the incremented value.
UPDATE companies ...
I need every single customer to have an individual value. My question is how can I prevent an error, data corruption or whatever if two or more users want to register at the same time? I've been reading about lock update but I'm not sure how to implement it on Visual Basic 2005 and I don't want to store scripts on SQL Server.
I'll appreciate your comments and help on this situation.
Hi all,
I have a table, three records of which look like this:
ID PersonID FirstName LastName PostCode
1 999 Barry White BW13 8GS
2 999 <null> <null> BW13 9GS
3 999 <null> Whites <null>
Both these records refer to the same "person". The records with ID of 2 and 3 represent updates to the record with an ID of 1. The problem is, only the updated data (along with the personID) is represented in records 2 and 3. I need to write query that will return a single record that looks like this:
PersonID FirstName LastName PostCode
999 Barry Whites BW13 9GS
in other words, the most recent information we have for that person.
Does anyone have any ideas? I'd be very grateful as this is proving to be a real pain in the butt!
Kind regards,
macca
Hi,
MSSQL 2000 T-SQL
I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.
I've attached a sample of DB data below.
tran_nr ret_ref_no msg_type description
5111 12345 420 reversal
5112 12345 200 auths
5113 15236 200 auths
5114 46587 200 auths
5115 46587 420 reversal
Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.
I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.
SELECT * FROM SAMPLE
WHERE ret_ref_no in
(
SELECT ret_ref_no FROM SAMPLE
GROUP BY ret_ref_no HAVING COUNT(*) > 1
)
Results of query
5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal
If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.
Regards
Deceptive
I have a 2table like
create table test1
(col1 identity(1,1),
col2 varchar(20)
)
create table test2
(old int,
new int)
Existing record in test1 table
(1,'test')
Now I want to insert a new record into test1 and then capture the old and new identity columsn in test 2 table
i.e. after insert test1 table will be
1,test
2,test
and test2 table will be like
1,2 -- old and new identity columns are captured during insert and stored in test2 table.
If the repeat the process again test2 table will be like
1, test
2,test
3, test
and test2 table will be
2,3 --- test2 table will always have only one record. old records will be deleted.
Using output clause i can get the new identity value.. but how can i get the old identity value and insert that into test2 table..
I am not willing to do a self join after the record is inserted into test2. I wanted to get the old ident value during insert of new ident value.
We are getting a security audit for the company I work for and got this question recently, and while my answer would be "everything is possible", I know that DIFFs alone can't restore a SQL database.
Having said that (and don't want to read hypothetical comments) how can a hacker read confidential information from an unencrypted DIFF backup? Let's say he steals the DIFF backup alone.
I have a field which contains something like prj(5616) .
I have been assigned to display the actual name and not the text with the number.
Example: if prj(8616) is called Soccer , then I want display Soccer instead of prj(8616).
I need to extract the .ispac file from the SSISDB. I can retrieve the stream with catalog.get_project sp. However, the file I end up with cannot be unzipped, giving an error message. My guess is that it is meta-data on the zip/ispac file that has a problem, because I can actually unzip it with Winrar, but not with any of those I (programmatically) need to unzip it with.
Below is the code for my stored procedure - My own suspicion is in the BCP usage to turn the stream into a file.
USE [SSISDB]
GO
ALTER PROCEDURE [dbo].[spGetIspacFile]
@project VARCHAR(255) ,
@environmentFolder VARCHAR(50) ,
@ispacTempFolder VARCHAR(100) ,
@ispacFilePath VARCHAR(200) OUTPUT
[code]....
We extract 10k tables every night and I have a table that keeps track of ETL tables that fail or succeed. I would like to know if a table fails during the night and nobody kicks off another job to fix it during the day.
The table structure looks like this:
| Table_Name | Time_Start | Status | Duration | Time_End |
Table_Name = varchar(20)
Time_Start = DateTime
Status varchar(7) = Success or Error
Duration = Number
Time_End = DateTime
Select Table_Name into #MyTempTable
From ETL.STATS_Table
Where Status = 'Error'
AND Cast(Time_Start as Date) = GetDate()
How do I take the table names from #MyTempTable and find out if they where successful for the same date? Duration time and Time_End fields aren't needed.
I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:
CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,
[Code] ....
All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:
CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));
I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...
I can retrieve all the column names that are in uppercase using this:
DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;
but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):
ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade
[code]....
The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.
So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?
In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)
This is my script:
SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)
[Code] ....
My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.
I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).
The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.
The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.
The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.
Example:
Old Table
Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description
Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL
Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad
Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL
Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL
I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.
I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.
"2 8 7"
"2 8"
"2"
"22"
"3 2 8"
I have tried the following
Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,
Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,
Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),
Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.
let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?
I'm looking to dynamically remove records from tables dynamically using the information schema within SQL Server. Looking to remove records from all the tables within a schema. I have gotten as far as generating the script dynamically then using a while exist clause to execute the delete statements.
DECLARE@TargetSchema varchar(100),
--@LibNameData varchar(100),
@fnameIndex varchar(100),
--@startOFR_SCR_FILENAME_DATE varchar(25),
[code]...
Would like to execute the statements generated by the results from the information schema.
I have a group of about 5 servers (which will likely grow toabout 25 in the near future) with their names listed in a table in a database on one of the servers. I want to query all servers in that table using the following query to pull the storage drive, database name, created date, age and size of the databases for each server listed in the table:
SELECT left(mf.Physical_Name,2) AS Storage_Drive,
DB_NAME(mf.database_id) AS DatabaseName,
db.create_Date,
DateDiff(day, db.create_date, getDate()) Age,
sum((mf.size*8))/1024 SizeMB
[Code] ...
How would I best accomplish this if I want to implement it using a TSQL procedure?
Hi,
I need to extract or get the schema from tables in my databse and what is the best way to do it. Is there any third party tool to do it??
Any idea will be appreciated..
Thanks
Karen
I'm running a website on IIS 6.0 w. ASP.Net 2.0 and SQL Server Express 2005 via the native client. The connection string is fairly standard:
<add name="SiteSqlServer" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=c:DotNetNukeApp_DataASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
I find that after the website has been running for a while I can no longer open the database using SQL Server Management Studio Express (I get an error indicating that the physical file has been locked). Even if I shutdown and restart both IIS and SQL Server, it remains locked and can only be opened once I find and "End Process" all the sqlservr.exe instances from the Task Manager.
Is this a known issue? Any way to find out what is locking the database, or any specific configuration to check?
thanks
Steve
Good afternoon SQL dudes Does anyone have any experience of extracting data from IBM's UniData (http://en.wikipedia.org/wiki/UniData) (or any post-relational Pick nested relational multi-valued relational database) into a SQL Server?More info here (http://www.rpbourret.com/xml/ProdsXMLEnabled.htm), here (http://www.pick-ware.co.uk/) and here (http://en.wikipedia.org/wiki/Pick_operating_system) I don't (which is why I am asking) but I could imagine it being a right bugger. No need for detailed or technical info - I have no more info at this stage - just wondered if anyone has any similar experience. Super duper, thank you SQL troopers :)
View 2 Replies View RelatedI am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
This script gets the file information for every database on a server, and inserts it into temp table #DB_INFO. #DB_INFO is queried multiple ways to give various levels of analysis of file space usage.
This script was tested on SQL Server 7.0, 2000, and 2005.
Edit 2007/9/7: Added FILEGROUP_TYPE and DISK columns.
Edit 2007/9/17: Modified to add various ways to analyze the output and format it to make it easier to understand space usage on the server:
Show Files Details
Total by Database and File
Total by Database and Filegroup
Total by Database and Filegroup Type
Total by Disk, Database, and Filepath
Total by Disk and Database
Total by Database
Edit 2007/9/17: Modified to make the changes suggested by eyechart. I managed to defeat Snitz to get rid of the smiley face () and still have executable code by putting in some extra quotes around the database name.
use master
go
if exists ( select * from tempdb.dbo.sysobjects o
where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_FILE_INFO' ))
drop table #DB_FILE_INFO
go
if exists ( select * from tempdb.dbo.sysobjects o
where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_INFO' ))
drop table #DB_INFO
go
set nocount on
go
create table #DB_FILE_INFO (
[ID]intnot null
identity (1, 1) primary key clustered ,
[DATABASE_NAME]sysnamenot null ,
[FILEGROUP_TYPE]nvarchar(4)not null ,
[FILEGROUP_ID]smallintnot null ,
[FILEGROUP]sysnamenot null ,
[FILEID]smallintnot null ,
[FILENAME]sysnamenot null ,
[DISK]nvarchar(1)not null ,
[FILEPATH]nvarchar(260)not null ,
[MAX_FILE_SIZE]intnull ,
[FILE_SIZE]intnot null ,
[FILE_SIZE_USED]intnot null ,
[FILE_SIZE_UNUSED]intnot null ,
[DATA_SIZE]intnot null ,
[DATA_SIZE_USED]intnot null ,
[DATA_SIZE_UNUSED]intnot null ,
[LOG_SIZE]intnot null ,
[LOG_SIZE_USED]intnot null ,
[LOG_SIZE_UNUSED]intnot null ,
)
go
declare @sqlnvarchar(4000)
set @sql =
'use ['+'?'+'] ;
if db_name() <> N''?'' goto Error_Exit
insert into #DB_FILE_INFO
(
[DATABASE_NAME],
[FILEGROUP_TYPE],
[FILEGROUP_ID],
[FILEGROUP],
[FILEID],
[FILENAME],
[DISK],
[FILEPATH],
[MAX_FILE_SIZE],
[FILE_SIZE],
[FILE_SIZE_USED],
[FILE_SIZE_UNUSED],
[DATA_SIZE],
[DATA_SIZE_USED],
[DATA_SIZE_UNUSED],
[LOG_SIZE],
[LOG_SIZE_USED],
[LOG_SIZE_UNUSED]
)
selecttop 100 percent
[DATABASE_NAME] = db_name(),
[FILEGROUP_TYPE]= case when a.groupid = 0 then ''Log'' else ''Data'' end,
[FILEGROUP_ID]= a.groupid,
a.[FILEGROUP],
[FILEID]= a.fileid,
[FILENAME]= a.name,
[DISK]= upper(substring(a.filename,1,1)),
[FILEPATH]= a.filename,
[MAX_FILE_SIZE] =
convert(int,round(
(case a.maxsize when -1 then null else a.maxsize end*1.000)/128.000
,0)),
[FILE_SIZE]= a.[fl_size],
[FILE_SIZE_USED] = a.[fl_used],
[FILE_SIZE_UNUSED] = a.[fl_unused],
[DATA_SIZE]= case when a.groupid <> 0 then a.[fl_size] else 0 end,
[DATA_SIZE_USED]= case when a.groupid <> 0 then a.[fl_used] else 0 end,
[DATA_SIZE_UNUSED] = case when a.groupid <> 0 then a.[fl_unused] else 0 end,
[LOG_SIZE] = case when a.groupid = 0 then a.[fl_size] else 0 end,
[LOG_SIZE_USED] = case when a.groupid = 0 then a.[fl_used] else 0 end,
[LOG_SIZE_UNUSED] = case when a.groupid = 0 then a.[fl_unused] else 0 end
from
(
Select
aa.*,
[FILEGROUP]= isnull(bb.groupname,''''),
-- All sizes are calculated in MB
[fl_size]=
convert(int,round((aa.size*1.000)/128.000,0)),
[fl_used]=
convert(int,round(fileproperty(aa.name,''SpaceUsed'')/128.000,0)),
[fl_unused]=
convert(int,round((aa.size-fileproperty(aa.name,''SpaceUsed''))/128.000,0))
from
dbo.sysfiles aa
left join
dbo.sysfilegroups bb
on ( aa.groupid = bb.groupid )
) a
order by
case when a.groupid = 0 then 0 else 1 end,
a.[FILEGROUP],
a.name
Error_Exit:
'
--print @sql
exec sp_msforeachdb @sql
--select * from #DB_FILE_INFO
declare @DATABASE_NAME_LENvarchar(20)
declare @FILEGROUP_LENvarchar(20)
declare @FILENAME_LENvarchar(20)
declare @FILEPATH_LENvarchar(20)
select
@DATABASE_NAME_LEN= convert(varchar(20),max(len(rtrim(DATABASE_NAME)))),
@FILEGROUP_LEN= convert(varchar(20),max(len(rtrim(FILEGROUP)))),
@FILENAME_LEN= convert(varchar(20),max(len(rtrim(FILENAME)))),
@FILEPATH_LEN= convert(varchar(20),max(len(rtrim(FILEPATH))))
from
#DB_FILE_INFO
if object_id('tempdb..##DB_Size_Info_D115CA380E2B4538B6CBBB51') is not null
begin
drop table ##DB_Size_Info_D115CA380E2B4538B6CBBB51
end
-- Setup code to reduce column sizes to max used
set @sql =
'
select
[DATABASE_NAME]= convert(varchar('+@DATABASE_NAME_LEN+'), a.[DATABASE_NAME] ),
a.[FILEGROUP_TYPE],
[FILEGROUP_ID],
[FILEGROUP]= convert(varchar('+@FILEGROUP_LEN+'), a.[FILEGROUP]),
[FILEID],
[FILENAME]= convert(varchar('+@FILENAME_LEN+'), a.[FILENAME] ),
a.[DISK],
[FILEPATH]= convert(varchar('+@FILEPATH_LEN+'), a.[FILEPATH] ),
a.[MAX_FILE_SIZE],
a.[FILE_SIZE],
a.[FILE_SIZE_USED],
a.[FILE_SIZE_UNUSED],
FILE_USED_PCT=
convert(numeric(5,1),round(
case
when a.[FILE_SIZE] is null or a.[FILE_SIZE] = 0
then NULL
else (100.00000*a.[FILE_SIZE_USED])/(1.00000*a.[FILE_SIZE])
end ,1)) ,
a.[DATA_SIZE],
a.[DATA_SIZE_USED],
a.[DATA_SIZE_UNUSED],
a.[LOG_SIZE],
a.[LOG_SIZE_USED],
a.[LOG_SIZE_UNUSED]
into
##DB_Size_Info_D115CA380E2B4538B6CBBB51
from
#DB_FILE_INFO a
order by
a.[DATABASE_NAME],
case a.[FILEGROUP_ID] when 0 then 0 else 1 end,
a.[FILENAME]
'
--print @sql
exec ( @sql )
selecttop 100 percent
*
into
#DB_INFO
from
##DB_Size_Info_D115CA380E2B4538B6CBBB51 a
order by
a.[DATABASE_NAME],
case a.[FILEGROUP_ID] when 0 then 0 else 1 end,
a.[FILENAME]
drop table ##DB_Size_Info_D115CA380E2B4538B6CBBB51
set nocount off
print 'Show Details'
select * from #DB_INFO
print 'Total by Database and File'
select
[DATABASE_NAME]= isnull([DATABASE_NAME],' All Databases'),
[FILENAME]= isnull([FILENAME],''),
FILE_SIZE= sum(FILE_SIZE),
FILE_SIZE_USED= sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED),
FILE_USED_PCT=
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE= sum(DATA_SIZE),
DATA_SIZE_USED= sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED),
LOG_SIZE= sum(LOG_SIZE),
LOG_SIZE_USED= sum(LOG_SIZE_USED),
LOG_SIZE_UNUSED= sum(LOG_SIZE_UNUSED)
from
#DB_INFO a
group by
[DATABASE_NAME],
[FILENAME]
with rollup
order by
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME],
case when [FILENAME] is null then 1 else 0 end ,
[FILENAME]
print 'Total by Database and Filegroup'
select
--[Server]= convert(varchar(15),@@servername),
[DATABASE_NAME]= isnull([DATABASE_NAME],'** Total **'),
[FILEGROUP]=
case when [FILEGROUP] is null then '' when [FILEGROUP] = '' then 'LOG' else [FILEGROUP] end,
FILE_SIZE= sum(FILE_SIZE),
FILE_SIZE_USED= sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED),
FILE_USED_PCT=
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
--MAX_SIZE= SUM([MAX_FILE_SIZE]),
DATA_SIZE= sum(DATA_SIZE),
DATA_SIZE_USED= sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED),
LOG_SIZE= sum(LOG_SIZE),
LOG_SIZE_USED= sum(LOG_SIZE_USED),
LOG_SIZE_USED= sum(LOG_SIZE_UNUSED)
from
#DB_INFO A
group by
[DATABASE_NAME],
[FILEGROUP]
with rollup
order by
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME],
case when [FILEGROUP] is null then 10 when [FILEGROUP] = '' then 0 else 1 end ,
[FILEGROUP]
print 'Total by Database and Filegroup Type'
select
--[Server]= convert(varchar(15),@@servername),
[DATABASE_NAME]= isnull([DATABASE_NAME],'** Total **'),
[FILEGROUP_TYPE]= isnull([FILEGROUP_TYPE],''),
FILE_SIZE= sum(FILE_SIZE),
FILE_SIZE_USED= sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED),
FILE_USED_PCT=
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE= sum(DATA_SIZE),
DATA_SIZE_USED= sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED),
LOG_SIZE= sum(LOG_SIZE),
LOG_SIZE_USED= sum(LOG_SIZE_USED),
LOG_SIZE_USED= sum(LOG_SIZE_UNUSED)
from
#DB_INFO A
group by
[DATABASE_NAME],
[FILEGROUP_TYPE]
with rollup
order by
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME],
case when [FILEGROUP_TYPE] is null then 10 when [FILEGROUP_TYPE] = 'Log' then 0 else 1 end
print 'Total by Disk, Database, and Filepath'
select
[DISK]= isnull([DISK],''),
[DATABASE_NAME]= isnull([DATABASE_NAME],''),
[FILEPATH]= isnull([FILEPATH],''),
FILE_SIZE= sum(FILE_SIZE),
FILE_SIZE_USED= sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED),
FILE_USED_PCT=
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE= sum(DATA_SIZE),
DATA_SIZE_USED= sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED),
LOG_SIZE= sum(LOG_SIZE),
LOG_SIZE_USED= sum(LOG_SIZE_USED),
LOG_SIZE_UNUSED= sum(LOG_SIZE_UNUSED)
from
#DB_INFO a
group by
[DISK],
[DATABASE_NAME],
[FILEPATH]
with rollup
order by
case when [DISK] is null then 1 else 0 end ,
[DISK],
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME],
case when [FILEPATH] is null then 1 else 0 end ,
[FILEPATH]
print 'Total by Disk and Database'
select
[DISK]= isnull([DISK],''),
[DATABASE_NAME]= isnull([DATABASE_NAME],''),
FILE_SIZE= sum(FILE_SIZE),
FILE_SIZE_USED= sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED),
FILE_USED_PCT=
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE= sum(DATA_SIZE),
DATA_SIZE_USED= sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED),
LOG_SIZE= sum(LOG_SIZE),
LOG_SIZE_USED= sum(LOG_SIZE_USED),
LOG_SIZE_USED= sum(LOG_SIZE_UNUSED)
from
#DB_INFO a
group by
[DISK],
[DATABASE_NAME]
with rollup
order by
case when [DISK] is null then 1 else 0 end ,
[DISK],
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME]
print 'Total by Disk'
select
[DISK]= isnull([DISK],''),
FILE_SIZE= sum(FILE_SIZE),
FILE_SIZE_USED= sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED),
FILE_USED_PCT=
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE= sum(DATA_SIZE),
DATA_SIZE_USED= sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED),
LOG_SIZE= sum(LOG_SIZE),
LOG_SIZE_USED= sum(LOG_SIZE_USED),
LOG_SIZE_USED= sum(LOG_SIZE_UNUSED)
from
#DB_INFO a
group by
[DISK]
with rollup
order by
case when [DISK] is null then 1 else 0 end ,
[DISK]
print 'Total by Database'
select
--[Server]= convert(varchar(20),@@servername),
[DATABASE_NAME]= isnull([DATABASE_NAME],'** Total **'),
FILE_SIZE= sum(FILE_SIZE),
FILE_SIZE_USED= sum(FILE_SIZE_USED),
FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED),
FILE_USED_PCT=
convert(numeric(5,1),round(
case
when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0
then NULL
else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE]))
end ,1)) ,
DATA_SIZE= sum(DATA_SIZE),
DATA_SIZE_USED= sum(DATA_SIZE_USED),
DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED),
LOG_SIZE= sum(LOG_SIZE),
LOG_SIZE_USED= sum(LOG_SIZE_USED),
LOG_SIZE_UNUSED= sum(LOG_SIZE_UNUSED)
from
#DB_INFO A
group by
[DATABASE_NAME]
with rollup
order by
case when [DATABASE_NAME] is null then 1 else 0 end ,
[DATABASE_NAME]
CODO ERGO SUM
Looking for sample ETL package to extract data from SQL Sever Database and load into Oracle Database using SQL SERVER INTEGRATION SERVICES 2008. The requirement is for full load and incremental load both.
View 5 Replies View RelatedI run this code on several environments successfully but when on run it on a different environment it fails.
Its very strange as the user has database SA and OS administrator permissions.
Code...
declare @Command varchar (250)
set @Command = 'bcp "SELECT * FROM QAProcess.dbo.ReadMe_Table" queryout "THOMSONS-SQL01TempReadMe.txt" -c -T'
EXEC master..xp_cmdshell @Command
Error....
SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'T-BXSQLServerAccount'.
NULL
I need to extract the information from the following Formula:
prj(10517)(a*w/100)+prj(10742)(a*w/100)+prj(10804)(a*w/100)+prj(10808)(a*w/100)+prj(10809)(a*w/100)+prj(10810)(a*w/100)
What would be the simplest and fastest way to do this...
I did write a fetch statement but i want to do something simpler as it is a bit hectic...
Basically i require the information from prj(10517) etc.
I have a question regarding the locking behavior of indexed views. We have a 3rd party application and something like the following table:
CREATE TABLE [Person].[Person](
[BusinessEntityID] int NOT NULL,
[FirstName] varchar(20) NOT NULL,
[MiddleName] varchar(20) NULL,
[code]....
Also the 3rd party application uses an indexed view, which is based on the following query and has the same structure as the base table:
SELECT [BusinessEntityID],
[FirstName],
[MiddleName],
[LastName],
SUM([Quantity]) AS [SUMQuantity]
FROM [Person].[Person]
GROUP BY [BusinessEntityID], [FirstName], [MiddleName], [LastName]
The result is, that the indexed view has nearly the same data / entries as the base table. The indexed view is often queried like this:
SELECT SUM(SUMQuantity) FROM [vPerson] WITH(UPDLOCK, NOEXPAND) WHERE [BusinessEntityID] = 45 AND [FirstName] = 'test'
The base table is also queried very often with update locks (UPDLOCK). Because the indexed view is nearly just a copy of the base table and there is no performance gain (read), I would like to drop the indexed view. I'll then experience more locks / blocks, because now the queries are seperated on two objects.
I have create database name and userid and password and now i want to create tables on sql server through enterprise manager. I have problem to connect registration.
We have a SSIS package which loads the data from csv files to DB. It only loads the new entries ie if the row already exists in the tables than it doesn't insert it. For this we load the CSV to temp tables for respective schemas and than those are compared with base tables of respective schemas and inserted new rows. For this we use Merge statement.
View 1 Replies View RelatedI have enabled 1222 and 1204 trace and restarted sql server. then i have replicated deadlock in my local.
This is the error came in sql
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Then i opened ERRORLOG file , there is no information about deadlocks .
Where can found deadlock information and file path. is there any extra options required to enable trace?
DBCC TRACESTATUS(1222,-1)
TraceFlagStatusGlobalSession
1222110
How to find location trace log?
I have a sql query:
SELECT
'5' AS 'value/@version',
'database' AS 'value/@type',
'master' AS 'value/name',
LTRIM(RTRIM(( [Server Name] ))) AS 'value/server',
'True' AS 'value/integratedSecurity',
15 AS 'value/connectionTimeout',
4096 AS 'value/packetSize',
'False' AS 'value/encrypted',
'True' AS 'value/selected',
LTRIM(RTRIM(( [Server Name] ))) AS 'value/cserver'
FROM dbo.RedGateServerList
FOR XML PATH(''), ELEMENTS
I need to add some header information to the beginning of the query:
<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--
SQL Multi Script 1
SQL Multi Script
Version:1.1.0.34--><multiScriptApplication version="2" type="multiScriptApplication"><databaseLists type="List_databaseList" version="1">
Everything I have tried ends up as a failure, usually compile issues. My goal here is to be able to automare a configuration file for multiscript so I can keep my server list up to date.
Is there a way to identify who have changed the SQL port number other than the DBA's?
For example through c2 audit or any other triggers?
I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:
An error occurred while trying to access the database information. The msdb database could not be opened.
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies View Related