Recently Indexed Messages:-
Power Pivot :: 2 Tables - Remove Duplicates
Jul 13, 2015I have 2 tables below:
Table 1:
Product No Quantity
A 1
B 2
C 3
Table 2:
Product No Grade Quantity
A Good
A Normal
A Bad
B Good
B Bad
C Good
C Normal
C Bad
In Table 2, Product No divided by Grade. I want to lookup the Quantity from Table 1 to Table 2. The same Product No will have 1 value, the other value is 0. The result for Column Quantity should be like this:
Table 2:
Product No Grade Quantity
A Good 1
A Normal 0
A Bad 0
B Good 2
B Bad 0
C Good 3
C Normal 0
C Bad 0
Find Duplicate Records
Apr 17, 2014I have this query that I have been using to find duplicate records works great except for now. The logic I am adding is pcs_rreas <> 'NG'. When I add this it does take out the NG, but it also excludes the reocords that have NULL data in this field. I don't want that to happen. How can I fix this? I tried adding (pcs_rreas <> 'NG' or pcs_rreas is null) but nothing is pulling now.
SELECT pcs_id1, pcs_rreas, pcs_lname, pcs_fname, pcs_minit, pcs_degree, pcs_xtyp, pcs_office, pcs_dba, pcs_dob, pcs_sex, pcs_eff, pcs_trm, pcs_spec1, pcs_spec2, pcs_spec3,
pcs_spec4, pcs_tax1, pcs_ssn, pcs_altid, pcs_upin, pcs_medic, pcs_mcaid, pcs_ecs, pcs_npi, pcs_status, pcs_dir, pcs_den, pcs_www, pcs_hold, pcs_email,
pcs_misc1, pcs_misc2, pcs_newdt, pcs_newby, pcs_chgdt, pcs_chgby, pcs_malp, pcs_pf, pcs_ctl, pcs_sys, pcs_pay, pcs_ann, pcs_bcert, pcs_pass, pcs_w9,
pcs_taxex, pcs_tax2, pcs_type, pcs_rreas, pcs_routo, pcs_rtime, pcs_rdate, pcs_force, pcs_flag, pcs_v419, pcs_bcity, pcs_bstate,
[code]...
Reporting Services :: Background Color Formatting In Matrix Control
Oct 20, 2015I have a matrix report:
My Expression in the data fields inside design mode is:
IIF(Fields!Row_num.Value mod 2 ,"White","LightGrey")
I am using a Dense rank Function at the dataset level in order to group id column wise. So Fields!Row_num.Value comes from that set.
Earlier it was BLANK values: Please see below for reference.
Tried IsNull on SQL Server already and does not work because there are no NULLs in the data I am retrieving. The empty cells happen when the matrix creates the crosstab report - where there is no data for a column. Everything else works well except the BLANK values being not colored as you see in the screen shot, im using ISNOTHING function to achieve those 0's if NULLS inside the report. But though we have a value inside the cell coming from report it does not colour the entire group.
My requirement is coloring the entire column group irrespective of the NULLs' or Blanks.
Have also tries several functions, but of no use. I am missing with a tiny thing I guess which I am unable to figure out.
Other Functions Tried:
=IIF(VAL(ReportItems!ROWCOLOR.Value) MOD 2,"WHITE","LightGrey")
=iif(RunningValue(Fields!City.Value,CountDistinct,Nothing) Mod 2, "LIGHTBLUE", "SILVER")
=iif(RunningValue(Fields!DQLogDateTime.Value,CountDistinct, Nothing) MOD 2, "LightGrey","White")
Restore Database From Backup File
May 4, 2015Now i must restore a database from a backup file of MS SQL Sever 6.5,but this file is bad. When i restore database from this file, SQL Server tell me that the format of this file is not file format of SQL Server 6.5.So that I must repair this backup file firstly. I don't know database file format of SQL Server 6.5.
View 2 RepliesComma Separated To Columns
Apr 10, 2015I have Oracle query which seperates a text with commas to column data. Can we achieve this in SQL Server?
with t as (select 'abcd,123,defoifcd,87765,aoiwerwe' as str from dual)
select level as n, regexp_substr(str,'[^,]+',1,level) as val
from t
connect by regexp_substr(str,'[^,]+',1,level) is not null;
N VAL
1abcd
2123
3defoifcd
487765
5aoiwerwe
I'm working on SQL Server 2012, Windows 7.
Find Duplicate In Column Tied To Another Column
Apr 29, 2015I have one table with two columns, GUID and PID and another table with three columns, GUID, LastName, and FirstName. For each unique PID, I need to find the PID's that have more than one GUID and then match that with their respective FirstName and LastName from the other table.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
Table2
GUID LastName FirstName
GUID1 Mulder Fox
GUID2 Scully Dana
GUID3 Skinner Walter
So I'm looking for a result like:
PID1 GUID 1 Mulder Fox
----- GUID 2 Scully Dana
SQL 2012 :: Access File Table Shares Exposed On A Server?
May 2, 2014I've been trying to access the file table shares exposed on a server (I can select, delete, update, etc via SQL) via untransacted access with no luck (the share just sits there doing something when I try to access it, no error message of any kind).
So I thought I'd look to see if there were any open file handles via
SELECT * FROM sys.dm_filestream_non_transacted_handles;
I found there were 10 non transacted handles open, and they've been open for 3 days for automated processes and the files are about 1-2Kb in size.
so I tried to kill them via
exec sp_kill_filestream_non_transacted_handles
that's normally not something that takes very long (in fact when I cleared two for a different database it took just a second or two).
So leaving the stored proc running, I figured I'd connect and fire up Adam Mechanic's sp_whoisactive.
I see the processes to kill the open file handles, with a wait state of FFT_NSO_FILEOBJECT
SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables
Oct 7, 2015I 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?
Reporting Services :: Error - Y Expression For Chart Has Scope Parameter Not Valid For Aggregate Function
May 6, 2015I am trying to create a column chart that calculates the percentage of computers in our IT environment that are Actively communicating to our SCCM Server.
I have two datasets:
1. Total_Count_Of_AD_PC DataSet.
2. PC_With_Active_SCCM_Clients dataset.
Basically i wan to calculate the percentage for each Region (i.e. AP for Asia Pacific, EMEA, Americas).
Below is the Total_Count_Of_AD_PC Dataset screenshot.
Below is the PC_With_Active_SCCM_Clients dataset.
Below is the expression that i used that is causing the error.
=CountDistinct(IIf(Fields!Region.Value="AP", "PC_With_Active_SCCM_Clients"),(Fields!Name.Value, "PC_With_Active_SCCM_Clients"),Nothing)/CountDistinct(IIf(Fields!Region.Value="AP", "Total_Count_Of_AD_PC"),(Fields!name.Value,
"Total_Count_Of_AD_PC"),Nothing)
Below is the error message....
The Y expression for the chart ‘Chart2’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a dataset.
SQL 2012 :: Exclude Login From Automated Kill Job
May 20, 2015I have this job that runs and Kills blocking sessions that are longer than 10 mins. I need to add a list of login's that are exclulded from this Job. I cannot seem to get it to exclude specific users in this script. Below is the script.
SET NOCOUNT ON
-- Table variable to hold InputBuffer data
DECLARE @Inputbuffer TABLE
(
EventType NVARCHAR(30) NULL,
Parameters INT NULL,
EventInfo NVARCHAR(4000) NULL
[code]...
Server Alias On Configure Manager Necessary?
Jun 3, 2015so you have two place to configure alias, in SQL server configuration manager under aliases tab on and on the cliconfg.exe tool on a different machine. If I want another server to see this sql server by the alias name I will need to configure the alias on the cliconfg tool on the client machine. It looks like as long as I have the alias configured in the cliconfg on the client machine, I don't need the alias to exist on the config manager, the client can still see SQLserver using just the alias on cliconfg...if so, I wondering under what situation I would really need the alias on the configuration manager?
View 7 RepliesHow To Join Last Record Of Table1 With Table2
Oct 17, 2014HOW TO SELECT LAST ROW IN GROUP BY CLAUSE AND JOIN LAST ROW WITH ANOTHER TABLE IN ONE QUERY?
INPUT:
custidlivingstatusdate
1single2014-01-01 00:00:00.000
1married2014-01-02 00:00:00.000
1married_kids2014-01-03 00:00:00.000
2married_kids2014-01-04 00:00:00.000
2married2014-01-05 00:00:00.000
2single2014-01-06 00:00:00.000
Query 1 -- select last record
SELECT *
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Custid ORDER BY Date DESC) AS Seq,*
FROM Living_Situation
)t
WHERE Seq=1
Output:
Seqcustidlivingstatusdate
11married_kids2014-01-03 00:00:00.000
12single2014-01-06 00:00:00.000
Table Customer:
custidsexesurname
1mjansen
2mpietersen
How to link Query 1 with table Customer in one query?:
Desired output in ONE query:
custidsexesurnamelivingstatus
1mjansen married_kids
2mpietersen single
Transact SQL :: Determine When Database Was Backed Up Without Using MSDB Tables?
Apr 22, 2015I was wondering if there was another method to determine when a database was last backed up without using the backup history tables in msdb? whether using DBCC DBINFO, DBCC PAGE on a specific database page...etc.
Also, when restoring a database, is there a trace flag you can use to force restore details to be written to the error log?
Visual Studio EDMX - Update Model From Database
Oct 28, 2015I have to ask first, if in the command "Update model from database" the SQL Server Data Tools are involved?
View 2 RepliesSQL Server Admin 2014 :: Rename A Live Database?
Jan 31, 2015I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
USE master
GO
EXEC sp_dboption CurDataBase, 'Single User', True
EXEC sp_renamedb 'CurDataBase', 'OldDataBase'
GO
3- Rename Newdb to current DB.
USE master
GO
EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)