Go Premium for a chance to win a PS4. Enter to Win

x

Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Share tech news, updates, or what's on your mind.

Sign up to Post

Is there a more efficient way to get the total rows on each returned line than what I am doing in my select?

THIS

DECLARE @SalesSiteID INT = 56399;
WITH cte
AS ( SELECT   n.NotificationID ,
              n.NotificationText ,
              n.DateAdded
     FROM     ewNotifications n
              INNER JOIN ewNotificationsToSalesSites nss ON nss.NotificationID = n.NotificationID
     WHERE    n.IsGlobal = 1
              AND n.DateExpired > GETDATE()
              AND nss.SalesSiteID = @SalesSiteID
              AND nss.IsRead = 0
     GROUP BY n.NotificationID ,
              n.NotificationText ,
              n.DateAdded )
SELECT A.BadgeCount ,
       cte.NotificationID ,
       cte.NotificationText ,
       cte.DateAdded
FROM   cte
       OUTER APPLY (   SELECT COUNT(*) BadgeCount
                       FROM   cte ) A;

Open in new window


Returns this

SP
I am aware of the COUNT(*) OVER() as Total option but that seems to be excluded from the login when a select is done with a SELECT TOP (n)

Example...
The following SQL returns 100 rows of data
But SHOWS 1,754,123  as the total.

SELECT TOP 100 i.IndividualID ,
       COUNT(*) OVER () AS [Total_Rows]
FROM   Individuals i
       JOIN dbo.ewSalesSitesToIndividuals s ON i.IndividualID = s.IndividualID
       JOIN SalesSites ss ON s.SalesSiteID = ss.SalesSiteID
WHERE  ss.BusinessID = 2;

Open in new window

0
Free Tool: Site Down Detector
LVL 11
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

CREATE TABLE SSRSReport
(
    YearMonth		    INT,
    SSRSReportDateKey   INT,
    Membership		    INT,
    Admits              INT,
    AdmissionType	    VARCHAR(200),
    ManagingEntityName  VARCHAR(200)

)


INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','ALN')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','ALS')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','PHYSICIAN Primary (CDPG)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','CULLMAN Secondary CARE IPA (CULL)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','ETOWAH IPA (ETOW)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','FLN')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','GAN')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','GOTT')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','IL')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','IN (INAIND)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','INDEPENDENT PHYSICIANS (IPM)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','MA MAIND')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','MSGC - LIGHTHOUSE IPA (MSGC)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','MSJAX')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','N/A')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','NC (NCIND)')
INSERT INTO SSRSReport VALUES(201408,20140831,1,1,'CARE','PA PAIND')
INSERT INTO SSRSReport 

Open in new window

0
This stored proc was not written by me but it always shows up in my alerts where there are many query plans created for it.

Any suggestions on how to make this better.  Im pretty sure these joins and where clauses are bad..but not sure how to get around.


USE [cadoc_system]
GO
/****** Object:  StoredProcedure [dbo].[spGetMRUDocList3]    Script Date: 11/7/2017 9:43:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[spGetMRUDocList3]

		@nSubScr int
	, @cRootSite varchar(50)
	, @nMaxRecords int = 10

	
as
	set nocount on 
			
			(select distinct e.cFileAreaSiteCode, e.ctitle, e.cdocumentid,
		   c.nid, c.cName, c.cFEIN,
			 e.dCreated, d.taxyear, d.typeid, 
			   isnull(r.nRating,0) ranking, d.ndocument
			from tevents e
			join tSubscr s on e.nSubScr = s.nid
			join tSiteXCrmClient x on e.cFileAreaSiteCode = cast(x.nidSite as varchar(6))
			join cadoc_crm..tclient c on c.nid = x.nIdClient
			join table...documents d on e.cDocumentID = d.documentid

			left outer join tSubscrXDocumentRating r on r.nIdDocument = e.nid and  r.nIdSubscr = @nSubScr 
			
			
			where s.nid = @nSubScr
			and e.dCreated >= s.MRUClearDateDocuments
			and e.cdocumentid like 'DOC%'
			and e.ctitle != ''
			and e.dCreated > s.MRUClearDateDocuments 
			and d.nlinkeddoc = 0
			and d.ndocstatus != 99
			and d.sitecode =  @cRootSite
			and e.dCreated in 

			(select max(ee.dCreated) from tevents ee
			join tSubscr ss on ee.nSubScr = ss.nid
			where ss.nid = 

Open in new window

0
CREATE TABLE Report
(
    YearMonth        INT,
    ReportDateKey    INT,
    Admints          INT,
    AdmisionType     VARCHAR(200)
)
GO

INSERT INTO Report VALUES
(201701,20170131,10,'Acute Care')
 ,(201703,20170330,10,'Acute Care')
  ,(201704,20170430,70,'Acute Care')
,(201706,20170630,10,'Acute Care')
,(201702,20170228,80,'Acute Care')
,(201701,20170228,20,'LTE')
,(201702,20170228,30,'LTE')
,(201701,20170131,50,'RHEAB')
,(201702,20170228,100,'RHEAB')

Open in new window


EXEPECTED OUTPUT:-

AdmisionType	ADK	PreviousMonthADK	YTDADK"
Acute Care	80	10	90
LTE	30	20	50
RHEAB	100	50	150

Open in new window

0
hi,

anyone implement Brute-force protection in MS SQL server? how can it be done other than complex password?
0
I have a report from SSRS.   I need to include blanks...so that if a user selects all any value is returned for that column.

I am listing the dataset which populates the dropdown in SSRS.
I am listing the function used to parse the string from report server and create a comma delimited list of strings.

I am listing the where clause from the report where I call the function.
This where clause should be able to select all or just items on the list if a user selects individual values.

It was my thought that moving the 'blank' to the bottom of the sorted list in the dataset would fix this.  It is not working.


dataset - I also replaced the commas in the list ..just in case that was messing up...no luck
select row_number() OVER (order by t1.cTitle) as titlerow,replace(t1.cTitle,',','') as cTitle

			  from
			  (select distinct coalesce(n.cTitle,'') cTitle
			  from cadoc_crm..tContact n
			  )t1

			  order by CASE when  t1.cTitle = '' then 1 else 0 end

Open in new window



Here is my string parser:

USE [cadoc_crm]
GO
/****** Object:  UserDefinedFunction [dbo].[fnSplitString]    Script Date: 11/6/2017 8:58:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1

        INSERT INTO @output(splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)

    END 
    RETURN 
END

Open in new window



Finally my where clause to get the list of values including a blank....as I need all values evaluated.

( t2.Title IN ( SELECT splitdata FROM  dbo.fnSplitString(@Title,',') ) OR @Title = '' )

Open in new window

0
Good morning

how can I make a query Sql where I pass a range of dates and returns me a column with the day and another column with the day in string.


date.                        day
04/11/2017         Saturday
05/11/2017           Sunday


Thank you
0
Hi,

Attached you will find a rate table used for benefits.  I need to retrieve the BN_EMPL_RATE value for an individual.   I would like the approach to find the age on this table.  There is a field called : BN_RATE_KEY03. This corresponds to the Age.  

So - for a 52 year old,  this individual would be between BN_RATE_KEY03 = 50 (0.54320) -and BN_RATE_KEY03 = 55(0.73120) WITH BN_RATE_KEY01 (N) -smoking and BN_RATE_KEY01 (F)-female ; The BN_EMPL_RATE VALUE =  (0.54320);

My Attempt: This is how I am trying to get the AGE
AND floor( ( CAST(CONVERT(VARCHAR(8) ,getdate() ,112) AS INT)- CAST(CONVERT(VARCHAR(8) ,EMPDEP2.BIRTHDATE,112) AS INT) ) / 10000 ) BETWEEN (SUBSTRING(BRD.BN_RATE_KEY03,19,2)) AND ( 4 + SUBSTRING(BRD.BN_RATE_KEY03,19,2))

Fails because I am not getting the 30 year olds.


What technique would you use to get the age.  I am attaching the rate table to show you the data.  

Any help would be greatly appreciated,

Lucia
rate_table.JPG
0
Can someone tell me the minimum database role that is required in order for a user to be able to create/edit views?  I'm trying to avoid db_owner is possible.
0
I was provided data (believe from Oracle Server) that has several date columns. Some of the columns imported to datetime no issue while others are varchar. I need to switch the varchar to datetime. I can't determine what records are the issue. I've tried the isdate but the only records it brings back are null. I took of the time and AM hoping that was the issue but didn't work. There are over 600,000 records so how can I determine the issue? Here are a few record examples:
12/1/1986
1/1/1987
2/1/1987
4/1/1987
5/1/1987

Using SQL Server 2008 R2
0
Free Tool: Path Explorer
LVL 11
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I want the SQL Server default date to be 0000-00-00, as for some records I have following situations and not sure how can I tackle the default date for every record insert

1. Some records no date at all but I don't want to leave datetime field empty
2. Some records only year and month available
3. Some records only year available.

How can I default the missing information to just 0000 or 00 automatically whenever there is a records inserted.

Thanks
0
On my Exe3cuteNonQuery I am getting an error message in the StackTrace

The process is consuming a MS SQL Server 2012 Stored Procedure
I am passing in @25 parameters

This is the error message in StackTrace...
"Error converting nvarchar to int"

Which end of the process is which?

And is there a way tyo capture which line of the SQL the error is on?
0
Hi,  
I have a SQL Server 2008 R2 system that is running out of space on the drive.  I have found a master.mdf file that is 92GB in size and I am assuming this is my issue.  I am hoping for some guidance on how we can reduce the file size of this.  I have located under the properties of this an option that is under automatic - Auto Shrink - is set to false.  What impact will this have if I alter this to true and how often and at what times would this run?
Also would there be any other options / command that I can run to reduce the size of this file.
I have limited experience with SQL so would really appreciated the help and guidance in trying to resolve this one.

Cheers,

Jason
0
I have a simple select that returns many rows

Select id,CustName,TotalOrders, DateAdded from ewSQLSearch order by id desc

I have a tabular function I can feed in the DateAdded and get a single row with "Offset" times fordifferent time zones all over the world. (I return 10 time zones in the table all on one row)

Example  
SELECT * FROM fnServerGetOffetTimes(1,GETDATE())


How do I "join" on that and feed in the DateAdded for each row in my main select.

This obviously doesn't work
SELECT   ew.id ,
         ew.SQLProcedure ,
         ew.SQLCode,
             fn.*
FROM     dbo.ewSQLSearch ew WITH ( NOLOCK )
JOIN fnServerGetOffetTimes(ew.id,ew.DateAdded) fn ON ew.id = fn.id
ORDER BY ew.id DESC;
0
Here's my try..

SELECT SalesUserID INTO Temp001 FROM [dbo].[Organization]
WHERE NOT EXISTS 
  (SELECT * FROM [dbo].[Users] WHERE UserID = SalesUserID)

Update [Organization]
    Inner Join Temp_Organization
        On Temp_Organization.SalesUserID = [Organization].SalesUserID
Set SalesUserID = 0

Open in new window

0
I had this question after viewing cannot consistently connect to SQL Server named instance.

My situation is slightly different but similar.  I have a default instance (production data) and a named instance (test data) on a SQL Server 2008 R2 installation.  Initially, I was not able to connect locally across my internal network, until I added a firewall rule to open up UDP port 1434 on my SonicWall firewall device.  Now I can access both instances internally without issue.  I have remote users that are using RemoteApp to run my software system off my network and this works perfectly for the default instance.  Whey anyone attempts to access my "test environment" (databases on the named instance), via RemoteApp, they can NOT connect to the databases in the named instance.  This is presenting a problem for me in that my users need to either run demos or perform actual testing using the databases deployed on the named instance.  Does anyone have any suggestions as to what's causing this situation?
0
I have the following SQL Server query.

I was hoping to get help with this on performance.  The most important thing I need it to be able to sort by the 1st column in the order by so that its ascending and does not put nulls first.

I am running this in SQL mode 100 so I cannot use the nulls first or nulls last approach...I also do not seem to be able to get a case to work..

Please help.


SELECT Distinct top 2000 tContact.nid as nID,
iSNULL(tContact.cFirstName, ' ') as cFirstName,
ISNULL(tContact.cMiddleNme, '') as cMiddleNme, 
ISNULL(tContact.cLastName,' ') as cLastName,tContact.cSuffix, tContact.cDepartment, 
tContact.cTitle, tContact.cEmail as cEmail,tContact.cEmailAlt, tContact.lActive, 
 tContact.cClassify, tContact.cDesignate, tContact.cContactCompany,
 tContact.cPhone, tContact.cPhoneExt,tContact.cCellPhone,tContact.cHomePhone, ISNULL(tContact.cEIN,' ') AS cEIN,
 tSubscr.nid as nidSubscr, tSubscr.cUserID, tSubscr.lActive as lActiveSubscr, tSubscr.lAdmin as lAdminSubscr, tSubscr.dLastLogin, 
 (select cPhone1 from CADoc_CRM..tClient where nID = tContact.nIDClient) as cClientPhone, 
 Case When dupes.nIdContact is null Then cast(0 as bit) Else cast(1 as bit) End as lContactIsDuplicated, 
 Case When coalesce(changeRequest.status,1) = 0 Then Cast(1 as bit) Else Cast(0 as bit) End as lChangeRequestPending 

 FROM CADoc_CRM..tContact tContact 
 left outer join CADoc_System..tSubscrXCRMCont tSXCC on tSXCC.cIdCRMCont = tContact.nid 
 left outer join 

Open in new window

0
Hi,

We have already existing maintenance plan (Full Db backup).
now i want automate like, any newly created DB should automatically added to existing maintenance plan?
how to do this?
0
Below is an example of a SQL statement I'm using.  The date is actually "2017-10-27 17:10:12.000" but no data is returned.  If I use >= I do receive a record.  Why is the > not returning data?
SELECT F1 
FROM Table1
WHERE RequiredDate = '10/27/17'

Open in new window

0
Get your Disaster Recovery as a Service basics
LVL 1
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Hi there,
    we have a requirement where the non sysadmin user accounts shouldn't access (ie., select data)  from any system views or tables of master database.

This requirement is a part of security enhancement needs and we are using sql server 2008 R2 Enterprise edition.

thanks
Deepak
0
How to I copy database files or tables from one sql server 2008 to another same version.
0
Can you please tell me how to fix that error?
screenshot.png
0
I have 2008 sql server but it doens't have the management studio with it. How to I install it?
screenshot.png
0
i'm using sql server 2008

SELECT [OrderID]
      ,[CustomerID]
      ,[EmployeeID]
FROM [Northwind].[dbo].[Orders]
ORDER BY [CustomerID]

This is the Orders table

p1.PNG
I want to use the COUNT(*) function to get the count of Orders for each CustomerID.

How do I get the Count of orders for each CustomerID.

The result returned from the query should look lik this.
The first column would be the CustomerID and then the second column would be the count of Orders that customer had.
So then for example in the image below. ALFKI had 6 orders, ANATR 4 orders and so forth.

p2.PNG
0
I'm using sql server 2008.

I have a stored procedure that returns a column that is Bit dataype called [CurrentlyEmployed]

I'm filtering like this

WHERE   [CurrentlyEmployed] = @EmployeeStatus]


When I feed @EmployeeStatus = 1 to my stored procedure it returns all the employees that are currently employed
When I feed @EmployeeStatus = 0 to my stored procedure it returns all the employees that are not currently employed

How do i revise my were clause to return "All Employees".?
So this is all employees that are currently employed and also the ones not employed.
0

Microsoft SQL Server 2008

49K

Solutions

17K

Contributors

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.