Recently Indexed Messages:-
Archiving And Deleting Records (Data)?
Aug 10, 2015I wrote a script to archive and delete records rom a table back in 2005 and 2009.
I can't seem to get the syntax right. Any sample script to simply archive and delete records?
This is what I have so far.
DECLARE @ArchiveDate Datetime
SET @ArchiveDate = (SELECT TOP 1 DATEPART(yyyy,Call_Date)
FROM tblCall
ORDER BY Call_Date)
--SELECT @ArchiveDate AS ArchiveDate
DECLARE @Active bit
[Code] ....
SQL Server Admin 2014 :: Creating Named Instance On Cluster
Apr 22, 2015I need to create a named instance on a sql cluster.
How do I perform this? Is it as simple as creating a named instance on a standalone machine? Is there anything I need to consider?
SQL Server 2008 :: Table(s) Design For Messaging Functionality?
Jul 21, 2015I'm in the process of building messaging functionality in to my application where by users can contact one another, look at it as a dating site, you click on someones profile, view their profile and then send that user a message.
I started to build the table which looked like this:
Id (PK) (Increments by 1)
ToUserId (FK) -- User who they're getting in contact with
FromUserId (FK) -- User who sent the message
Content (nvarchar(3000)) -- Message being send
Status (int) -- read / new / deleted / sent
EmailDate (datetime)
EmailDeleted (datetime)
But the problem with this setup is both user's maybe sending / replying to each other so I would have multiple entries / statuses in one table which may become a nightmare to manage / control.
SQL Server 2008 :: Masking / Replacing Sensitive Data In Database?
Oct 8, 2015when prod db is restored in test server, i have to hide/replace sensitive fields (name, phone etc)Is it doable in SQL 2008 and SQL 2012?
View 5 RepliesMDX Date Range Report - Didn't Get Correct Result
Aug 5, 2014I am doing search through Date Range but didn't get correct result.
For Example if i created a Task on 5/8/2014 and when i have take Date Range from 1/8/2014 to 6/8/2014 then didn't get 5/8/2014 data in result but when i taken range from 1/8/2014 to 5/8/2014 then got the 5th Date data.
I am taking Range Inclusive Operator(:).
SELECT ( STRTOMEMBER(@FromDate) : STRTOMEMBER(@ToDate) )
SQL Server 2012 :: Query To Search Full-text Indexed Table And Return Results
Apr 19, 2014I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
Query
DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);
SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');
SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result
[Code] ....
Subquery Returned More Than 1 Value?
Mar 25, 2015i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role
My code is:
Code:
select top 100
username,
(Select b.name
from application b
inner join userroleapplication c
on b.applicationID = c.applicationID ) Application,
(select name from role d, userroleapplication gr
where gr.roleID = d.roleID and gr.userID = a.userID) role
from users a
where username in
(
'username'
)
i know where is the problem but i cant figure how to fix this
What i need as a output
Username | Application | Role
"user1" | "App1" | "Role1"
"user1" | "App1" | "Role2"
"user1" | "App2" | "Role1"
"user1" | "App3" | "Role1"
"user2" | "App1" | "Role1"
"user2" | "App3" | "Role1"
etc.
T-SQL (SS2K8) :: XML File And Conversion Datetime Offset?
Oct 13, 2015I have a store procedure who receive an xml file.
In this store procedure I try to put all record in temporary table for some treatement ..
But I have a problem with one field ...
When I execute the code below I receive this error message :
The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.
The problem comes from the field DeleteDate from the second row of the xml file
how I can get the correct conversion for this field ?
declare @tbl table(actorPersonId int, personType int, actorType int, person int
, firstName varchar(100), surname varchar(100), phone varchar(100), email varchar(100)
, publication int, logUsr varchar(20), active bit, delDate datetime )
[Code] .....
Analysis :: Calculated Member From Aggregate And Non-aggregate Factors
Sep 22, 2015I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named
This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.
The first factor, BillableLastYear is a number, aggregated (sum) from child units.
The second factor, BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.
How can I calculate This_Year_Billable_Objective_Count for each unit?
\ able 1
SELECT [UnitKey]
,[UnitID]
,[UnitName]
,[Unit2Name]
,[Unit3Name]
,[Unit4Name]
[Code] .....
SQL Server 2012 :: Finding First And Repeated Values
Aug 26, 2014I'm trying to come up with a query for this data:
CREATE TABLE #Visits (OpportunityID int, ActivityID int, FirstVisit date, ScheduledEnd datetime, isFirstVisit bit, isRepeatVisit bit)
INSERT #Visits (OpportunityID, ActivityID, FirstVisit, ScheduledEnd)
SELECT 1, 1001, '2014-08-17', '2014-08-17 12:00:00.000' UNION ALL
SELECT 1, 1002, '2014-08-17', '2014-08-17 17:04:13.000' UNION ALL
SELECT 2, 1003, '2014-08-18', '2014-08-18 20:39:56.000' UNION ALL
[Code] ....
Here are the expected results:
OpportunityIDActivityIDFirstVisitScheduledEndisFirstVisitisRepeatVisit
110012014-08-172014-08-17 12:00:00.00010
110022014-08-172014-08-17 17:04:13.00001
210032014-08-182014-08-18 20:39:56.00001
210042014-08-182014-08-18 18:00:00.00010
[Code] ....
Basically I'd like to mark the first Activity for each OpportunityID as a First Visit if its ScheduledEnd falls on the same day as the FirstVisit, and otherwise mark it as a Repeat Visit.
I have this so far, but it doesn't pick up on that the ScheduledEnd needs to be on the same day as the FirstVisit date to count as a first visit:
SELECT*,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 1
ELSE 0
END AS isFirstVisit,
CASE MIN(ScheduledEnd) OVER (PARTITION BY FirstVisit)
WHEN ScheduledEnd THEN 0
ELSE 1
END AS isRepeatVisit
FROM#Visits
Ordering Table By Alphabet
Aug 4, 2014I have one table with one column:
region
Global
USA
England
Germany
Spain
What I want is that the first entry Global is always in the first row the rest should be ordered by alphabet.
I tried this but order by works only for the result of union:
SELECT * FROM csappcidb_region WHERE region='Global'
UNION ALL
SELECT * FROM csappcidb_region WHERE region<>'Global' ORDER BY region
What else can I do?
Reporting Services :: Grouped Matrix Data Not Exporting To CSV
May 12, 2015I am trying to export report to .CSV , but I am unable to export grouped data, instead to grouped data , it exporting detail data,for example my grouped data in matrix is as below , I need to export below data to .csv
column1 colum2 year amount
1 1 2011 $1.0
2 2012 $2.0
3 2013 $3.0
2014 $4.0
but, I am getting like below-
column1 column2 year amount
1 1 2011 $1.0
1 2 2012 $2.0
1 3 2013 $3.0
1 3 2014 $4.0
Deleting Records From A Table In Server Database
Aug 24, 2015I'm trying to delete some records from some tables in a SQL Server 2008 R2 database. There's a foreign key relationship between the two tables. To make things easier here's the definition of both tables:
-- Parent table
CREATE TABLE [dbo].[PharmInvInItemPackages](
[InventoryInDetailID] [int] IDENTITY(1,1) NOT NULL,
[InventoryInID] [int] NOT NULL,
[ItemPackageID] [int] NOT NULL,
[code]....
Reporting Services :: How To Hide / Unhide Result In Excel Sheet By Using SSRS Report
May 6, 2015I have report which has 5 columns. User want to export report file in Excel format. when exported report then by default 2 columns will be hidden in excel and user can unhide columns after export in excel.I did column hide on report level and show in result set in excel sheet by using Global render format.
View 4 RepliesTransact SQL :: Referencing UDFs And Sprocs In Linked Server
Oct 7, 2015We have migrated a database (myDb2014) to a SQL Server 2014 instance. We have another on a 2008R2 instance (myDb2008). Both on the same server.
I have setup a linked server to the 2014 instance from the 2008R2 one.
I have a number of sprocs on myDb2008 that call TVFs and sprocs on myDb2014. This worked fine when they were both on the same instance.
Now however, I have an issue where I would have to update all the references in each calling query.
I looked into creating Synonyms but this only works for Tables/Views.
Is there a workaround to querying myDb2014 TVFs/sprocs without having to update each calling query in myDb2008?