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

I wonder if this is doable...I tried a pivot example and couldn't get it working. If not doable in SQL, I'll look into doing it in C#.

1. This is sample data. I have "12345" in the usage column as example.

CREATE TABLE #temptable ( [activationdate] nvarchar(20), [reportdate] nvarchar(20), [usage] money )
INSERT INTO #temptable
VALUES
( N'2018.1', N'2019.5', 12345.0000 ), 
( N'2018.2', N'2019.5', 12345.0000 ), 
( N'2018.3', N'2019.5', 12345.0000 ), 
( N'2018.4', N'2019.5', 12345.0000 ), 
( N'2018.5', N'2019.5', 12345.0000 ), 
( N'2018.6', N'2019.5', 12345.0000 ), 
( N'2018.7', N'2019.5', 12345.0000 ), 
( N'2018.8', N'2019.5', 12345.0000 ), 
( N'2018.9', N'2019.5', 12345.0000 ), 
( N'2018.10', N'2019.5', 12345.0000 ), 
( N'2018.11', N'2019.5', 12345.0000 ), 
( N'2018.12', N'2019.5', 12345.0000 ), 
( N'2019.1', N'2019.5', 12345.0000 ), 
( N'2019.2', N'2019.5', 12345.0000 ), 
( N'2019.3', N'2019.5', 12345.0000 ), 
( N'2019.4', N'2019.5', 12345.0000 ), 
( N'2019.5', N'2019.5', 12345.0000 ), 
( N'2019.6', N'2019.5', 12345.0000 ), 
( N'2019.7', N'2019.5', 12345.0000 ), 
( N'2019.8', N'2019.5', 12345.0000 ), 
( N'2019.9', N'2019.5', 12345.0000 ), 
( N'2019.10', N'2019.5', 12345.0000 ), 
( N'2018.1', N'2019.6', 12345.0000 ), 
( N'2018.2', N'2019.6', 12345.0000 ), 
( N'2018.3', N'2019.6', 12345.0000 ), 
( N'2018.4', N'2019.6', 12345.0000 ), 
( N'2018.5', N'2019.6', 12345.0000 ), 
( N'2018.6', N'2019.6', 12345.0000 ), 
( N'2018.7', N'2019.6', 12345.0000 ), 
( N'2018.8', 

Open in new window

0
Introduction to R
LVL 13
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

I want to sum the 2 select statements in the inner cursor loop.   I get this error: Invalid column name 'TotalDebits'.

I looked at example online and this looks correct. I also tried using the + operator. I removed the second "select" as a test and ran it but still the same error.

I've marked it where I get the error:

Declare @rYear int
Declare @rMonth int
Declare reportCursor Cursor
	for Select year, month from #ReportDateTable
Open reportCursor;
Fetch Next From reportCursor into @rYear, @rMonth
while @@FETCH_STATUS = 0
	Begin
		Declare @aYear int
		Declare @aMonth int
		Declare activationCursor Cursor
			for Select year, month from #ActivateDateTable
			Open activationCursor
			Fetch Next From activationCursor into @aYear, @aMonth
			while @@FETCH_STATUS = 0
				Begin
					-- Do your stuff and insert into @Usage
              -- INSERT INTO #usage (reportdate,activationdate,usage)
			       SELECT CAST(@rYear AS VARCHAR(4)) + '.' + CAST(@rMonth AS VARCHAR(2)), 
				          CAST(@aYear AS VARCHAR(4)) + '.' + CAST(@aMonth AS VARCHAR(2)),
				          SUM(TotalDebits)  ---- ******* I get the error here **************
				        (
					    	
							SELECT 
							   
								   SUM(dcp.[Cash Debit] * -1) TotalDebits 

							......
							 
                            
						UNION ***** I tried the + operator here 
						SELECT SUM([CashBalance]) TotalDebits
						...
                       )   -- union statement
							
					Fetch Next from 

Open in new window

0
We need to get a script done for our client (their developers, DBAs in the dept quit within a few weeks and no one bothered to ask them to turn over their code and scripts!)

My manager came up with this cursor loop but I get an error (I know cursors are not recommended but we have to get this done for now). Not sure where he got the script but I think the placement of closing the cursors is off somewhere..

error: A cursor with the name 'activationCursor' already exists.

Declare @rYear int
Declare @rMonth int
Declare reportCursor Cursor
	for Select year, month from #ReportDateTable
Open reportCursor;
Fetch Next From reportCursor into @rYear, @rMonth
while @@FETCH_STATUS = 0
	Begin
		Declare @aYear int
		Declare @aMonth int
		Declare activationCursor Cursor
			for Select year, month from #ActivateDateTable
			Open activationCursor
			Fetch Next From activationCursor into @aYear, @aMonth
			while @@FETCH_STATUS = 0
				Begin
					-- Do your stuff and insert into @Usage
                                      INSERT INTO #usage (reportdate,activationdate,usage)
				         SELECT
				                .....
					Fetch Next from activationCursor into @aYear, @aMonth
				End
			Close activationCursor
		 --Insert into Usage table
		 
		 Fetch Next from reportCursor into @rYear, @rMonth	
	End
	Close reportCursor

Open in new window

0
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. I have this error when building a CTE that has aggregate function and GROUP BY STATMENT

      
            WITH ProductSales      (      PoductID                  
                                                ,ProductCount
                                                ,TotalSales
                                          )
            AS
      --We Define the CTE query
      (
            SELECT                  ProductID, COUNT(*) ProductCount,SUM(LineTotal) TotalSales
            FROM                  Sales.SalesOrderDetail
            
      )
      ,CTE_ProductSales
            AS
      --Define the Outer query referencing CTE_RatingInfo
      (
            SELECT                  ProductID, COUNT(*) ProductCount, SUM(LineTotal) TotalSales
            FROM                  Sales.SalesOrderDetail
                                    GROUP BY      ProductID
                                    Order by      ProductID
            
      )

            SELECT *      FROM CTE_ProductSales

How can I implement the TOP here to remove the error ?
0
I have a high throughput c# console app which is saving around 250-350 records a sec to a log table (sql server 2017 enterprise)

We are looking at ways to increase performance and sometimes a report is run on the table and things grind to a halt
We are also struggling to keep up at busy times

We currently do not use partitions, and are investigating this
We also insert one record at a time, and attach the insertedID to the c# object of log data for further operations

I'm considering partitions, however i'm not sure what to partition on hour, day, week month
We generally store 6 months of historical data (could also be 1 month or 24 months for smaller number of IOT devices.)
data is usually queried by day, last 7 days, week, month, and sometimes custom date ranges.
I would welcome ideas and comments about what is the best option here, we write more than we query, but queries will span multiple days/weeks

I'm also considering a different approach on inserting, instead of one at a time, do a batch insert.
However i'm unsure how I can then associate the database insertedID with C# object.
Currently its one at a time, so i just add the insertedID to the object, and send that off to messages queues for further processing,

I was looking at the SQL OUTPUT and inserted.ID but not sure when batching 500 records how to marry the inserted ID with the c# object

So in summary I would welcome ideas on
1) partition setup
2) batching inserts and marrying the …
0
Hello Experts,
I have the Following SQL Table1:

Date            Lot_Num      Lot_Weight      Lot_Checked
9/22/2019      1901            22.5            Yes      
9/22/2019      1902            30.6            Yes      
9/22/2019      1903            22.8            Yes
9/22/2019      1904            19.3            No
9/23/2019      1905            18.6            Yes
9/23/2019      1906            35.7            No

I would like to find the % Lot_Checked (Yes and No) based on Lot_Weight by each Day.

For Example, on 9/22/2019 The Percent Lot_Weight Checked would be:

Yes (22.5 + 30.6 + 22.8) / (22.5 + 30.6 + 22.8 + 19.3) = .7972
No (19.3) / (22.5 + 30.6 + 22.8 + 19.3) = .2028

I would like the output to look like this using TSQL:

Date            Checked_A%      Checked_B%       
9/22/2019      .7972            .2028                  
9/22/2019      .3425            .6575


Does anyone know how to do this with as few steps as possible?

Thanks!
0
I am trying to write a SQL statement whereby I have a field named FormElementReference, which is the name of the question, and an associated field called value which holds the answer to that question.

I've written a case statement to try and achieve this, and store the separate values in separate fields for export to SSRS.

What I currently get is an row for each answer value.

Screenshot showing SQL with a row for each value.
SELECT DISTINCT
  FormResults.FormResultKey AS [FormResults FormResultKey]
  ,FormResults.FormID
  ,FormResults.FormReference
  ,UserForename + ' ' + UserSurname as [Full Name]
  ,FormResults.FormResultDate
 
 

,CASE WHEN FormResultDetails.FormElementReference = 'Date' then value else null end AS JobDATE
,CASE WHEN FormResultDetails.FormElementReference = 'Time' then value else null end AS TIMEFrom
,CASE WHEN FormResultDetails.FormElementReference = 'TEB' then value else null end AS TIMETo
,CASE WHEN FormResultDetails.FormElementReference = 'TEA' then value else null end AS TotalWorked
,CASE WHEN FormResultDetails.FormElementReference = 'JNO' then value else null end AS JobNumbers
,CASE WHEN FormResultDetails.FormElementReference = 'AA' then value else null end AS JobAddress
,CASE WHEN FormResultDetails.FormElementReference = 'Reason' then value else null end AS JobReason
,CASE WHEN FormResultDetails.FormElementReference = 'Managers' then value else null end AS ManagerAuthorised


FROM
  FormResultAnswers
  LEFT OUTER JOIN Documents
    ON …
0
Hi, we have an SQL 2008 server (version 10.0.1600.22) running on Windows 2008 R2 Server.

I'd like to upgrade to version 10.50.4000 (SQL 2008 R2 Server).

Is there a way for me to still do that? what about my SQL license? (Standard), will it be kept?
0
MS-SQL 2008 express, currently does not have SSMS installed.
Is is possible to add/install SSMS SQL Server Management Studio. I can't seem to find a satisfactory answer on line.

Secondary:
I'd like to script a re-index (attachment) of the SQL DB as the software using the SQL DB slows down and re-indexing resolves this.
RebuildTblIndexScript.txt
0
Hello Guys,

I need help from you

Please take a look at my example assembled

create table #tmp (
  numlanc int,
  numdoc  varchar(10),
  tipdoc varchar(1),
  valmov float,
  datmov datetime)


insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (1, 'a415', '4', 150, '20190810')
insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (2, 'a415', '4', 150, '20190810')
insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (3, 'a500', '4', 160, '20190810')
insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (3, 'a501', '4', 174, '20190910')
insert into #tmp (numlanc, numdoc, tipdoc, valmov, datmov) values (3, 'b604', '4', 908, '20190501')


-- First search 
select * from #tmp where 1=1 and (numdoc='a500' and valmov=160 and convert(varchar(10), datmov, 112)='20190810')

-- second search
select * from #tmp where 1=1 and (numdoc='a501' and valmov=174)

-- third search  ( here is the problem )
select * from #tmp where 1=1 and (valmov=150 and convert(varchar(10), datmov, 112)='20190810' and tipdoc<>0)

-- with all filters above I need to add more one filter where my query can only returns the register with only one row, 
-- with my query it returns me two rows, as I really have two register with the same values for my filter, I need to put 
-- the result aside. In other words, I only want register that has only one cast in my table with valmov and datmov

Open in new window


Regards
Alex
0
Introduction to Web Design
LVL 13
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Hi,

I'm migrating a Database Server that has a Transactional Replication configured. In some tables the replication is bi-directional. From the remote site the transactions come to the main site that works as a central repository and is distributed to the other sites.

I have verified that in order to be able to establish replication I need to have the Databases of Origin and Destination without information.

Is it possible to establish replication without having to empty the tables?

SQL Versions: SQL 2014 Publisher, SQL 2014 Distributor, SQL 2008 Subscriber - Main office to remote office.
SQL Versions: SQL 2008 Publisher, SQL 2008 Distributor, SQL 2014 Subscriber - Remote office to main office.
Primer-Diagrama.jpg
0
I need to apply an update to SQL server 2008 in a production environment.

Barring backing up the database would there be any other implications in doing this?
0
SQL Server v2014.  I have attempted to relocate the tempdb files from C to another drive.  The ALTER statements performed fine, and the restart completed without error as well.  But now that I am trying to adjust the size of the file on the new drive, the system tells me there is not enough space -- because it still thinks they are on the C drive.

This is the error I receive when I attempt to ALTER the size of the tempdb log file:

     Msg 5149, Level 16, State 3, Line 41
     MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file
      'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\templog.ldf'.

Yet at the very same time, sp_helpfile says the files are:
   sp_helpfile results for tempdb


I have relocated the tempdb  countless times before, but I have never run into this problem.  Are there any Experts who can assist?
0
I have two SQL tables i'm trying to join together, while presenting all of the data from one table (Material_Location), and only one field from the other table (Job.Order_Date).  Specifically, I'm trying to get the most recent order date from the Job Table for each line in the Material_Location Table.  Can someone please explain how to properly join them and limit the output?  Currently I get a new row of output for every date in the table for a given part.

SELECT        dbo.Material_Location.Material, dbo.Material_Location.Location_ID, dbo.Material_Location.On_Hand_Qty, dbo.Material_Location.Unit_Cost, dbo.Material_Location.Last_Updated
FROM            dbo.Material_Location LEFT OUTER JOIN
                         dbo.Job ON dbo.Material_Location.Material = dbo.Job.Part_Number

Open in new window


I've also attached a table of sample data showing my anticipated output.

Any assistance would be very much appreciated.
SAMPLESET.JPG
0
I have the query that return the result properly. What I am trying to do is to get the total per school. However, the schools have different IDs but this is basically the same schools. How can do some type of search to get the calculated as a same school.

	SELECT U.SchoolID,LS.SchoolNameTx
		FROM SessionMap SM
		inner join users u on u.userKey = SM.userKey
		inner join lkup_school LS on LS.SchoolID = U.SchoolID
		order by U.SchoolID

Open in new window


The result returned:
SchoolID	SchoolNameTx
429	       Quince Orchard High School
1189	Walter Johnson High School (WJHS)
1228	Academy of the Holy Cross
1395	Academny of the Holy Cross
1395	Academny of the Holy Cross
2656	Academy of Holy Cross
3144	Academy of the Holy Cross High School (AHC)
3144	Academy of the Holy Cross High School (AHC)
3144	Academy of the Holy Cross High School (AHC)
3144	Academy of the Holy Cross High School (AHC)
3144	Academy of the Holy Cross High School (AHC)
3144	Academy of the Holy Cross High School (AHC)
3450	Academy  of the Holy Cross
4892	Stonehill College

Open in new window

0
sql server 2008 r2

I have a job that runs every night.
In the sql , I need some type of email alert that will tell me if after an "insert statement" was completed;
If the records that were in a staging table has the same count as in my master table ?

I cant find any code reference to my  question ?

Thanks
fordraiders
0
I am running an SQLCMD command from DOS against a SQL Server 2008 database that occasionally outputs the following message at the end of the data:
"WARNING: Null value is eliminated by an aggregate or other SET operation."

I do not control the DB and do not want to fix the error, I just want to suppress the warning message.
I know the default ERRORLEVEL is 0.
If I add the output parameter -m1 this warning disappears.  This is what I want because the output is read by a different program and does not expect messages at the end.

But it raises a few other questions for me:
1.  What ERRORLEVEL values exist in SQL Server?
2.  Do they mean anything?
3.  Is there a maximum (if I want to suppress any/all errors)?

Thanks!
0
Hi All,
I'm trying to troubleshoot msdb.dbo.sp_send_dbmail

Below is the procedure.  I know the profile works because I can send something without an attachment and it works fine.

The original select is from a global temp table.  (EG:  Select * from ##MyTempTable).

The select is then modified to append the blank column as we needed a different char for the CSV.

This was working up until last month, and trying to figure out what's going on.  I've tried changing the select to a different table but that's not working either.

the exec returns Null for the mailID, 1 for the RC, 0 for @@error.  

Any help greatly appreciated.  (SQL Server 2008 R2)

ALTER  procedure [dbo].[sendMail] 
@from varchar(max),
@fromDisplayName varchar(max),
@to varchar(max),
@cc varchar(max),
@bcc varchar(max),
@subject nvarchar(255),
@body nvarchar(max),
@AttachmentFileName nvarchar(255),
@select nvarchar(max),
@columnSeparator char(1) = '|'
as

set @columnSeparator = isnull(@columnSeparator, char(9))

declare @fromAdd varchar(max) = @fromDisplayName + ' <' + @from  + '>'

set @select =  'set nocount on; select '''' [sep=|' + CHAR(13) + CHAR(10) + 'BLANK],' + replace(@select, 'select', '')

declare @mailID int
declare @rc int

begin try
EXEC @rc = msdb.dbo.sp_send_dbmail
    @recipients= @to,
	@copy_recipients  = @cc,
	@blind_copy_recipients = @bcc,
    @body= @body,
    @subject = @subject,
    @profile_name = 'WorkingMailProfile',
	@from_address = @fromAdd,
	@query = @select,
	

Open in new window

0
I know how to create a certain trace on the SQL Profiler but I have to....set up a SQL Server Profile trace on our prod server (SQL Server 2008) to capture execute times for a specific stored procedure, and to capture events where a query been delayed more than 2 seconds due to locks which I haven t done before.


Database: testdb
Stored proc: (sp_ex)
Elapsed: more than 300ms
Login: login\test

How would I go about this?
0
Starting with Angular 5
LVL 13
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

How can I get better performance with my sql query in a SP? if you look at below my execution pan you will see that :

IF NOT EXISTS(SELECT * FROM Common.[CustomerEntityIds] WHERE EntityType = @EntityType AND CustomerId = @CustomerId)

Open in new window


has alot of memory usage. How  can I reduce that?

ALTER PROCEDURE [Common].[SaveCustomerEntityIds] 
(
	@EntityType	NVARCHAR(128),
	@CustomerId	INT,
	@EntityId	INT OUTPUT
)
AS
BEGIN
	SET NOCOUNT ON;

	IF NOT EXISTS(SELECT * FROM Common.[CustomerEntityIds] WHERE EntityType = @EntityType AND CustomerId = @CustomerId)
	BEGIN
		INSERT INTO Common.[CustomerEntityIds]
					([EntityId]
					,[CustomerId]
					,[EntityType])
				VALUES
					(0
					,@CustomerId
					,@EntityType)
	END

	UPDATE	Common.[CustomerEntityIds]
	SET		[EntityId] = ([EntityId]) + 1
	WHERE	[EntityType] = @EntityType
			AND CustomerId = @CustomerId

	SELECT	@EntityId = EntityId
	FROM	Common.[CustomerEntityIds]
	WHERE	[EntityType] = @EntityType
			AND CustomerId = @CustomerId
END

Open in new window





picture of execution planpicture of execution planExecutionPlan2.sqlplan
0
Hi;

How can I improve performance issue ? I created below stored procedure but if I check costly stored procedure by using sql query to detect them. I realized that below query needs improvement to fix performance issue.Can you help me please?

USE [FFSNext]
GO
/****** Object:  StoredProcedure [WMS].[UpdateQuantityBySalesOrderLineIds]    Script Date: 6/12/2019 11:55:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [WMS].[UpdateQuantityBySalesOrderLineIds] 
(
	@SalesOrderLineIds VARCHAR(MAX)
)
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE	PLL
	SET		Quantity = SOL.Quantity
	FROM	WMS.PickListLine PLL

	JOIN	WMS.SalesOrderLine SOL ON SOL.Id = PLL.SalesOrderLineId
	JOIN	PIM.Product SOLP ON SOLP.Id = SOL.ProductId

	WHERE	SOLP.HasAssembly = 0 AND PLL.SalesOrderLineId IN
	(
		SELECT	CAST([value] AS INT)
		FROM	STRING_SPLIT(@SalesOrderLineIds, ',')
	)
	

	UPDATE	PLL
	SET		Quantity = (SOL.Quantity * PLLP.Count)
	FROM	WMS.PickListLine PLL

	JOIN	WMS.SalesOrderLine SOL ON SOL.Id = PLL.SalesOrderLineId
	JOIN	PIM.Product SOLP ON SOLP.Id = SOL.ProductId
	JOIN	PIM.ProductAssembly PLLP ON PLLP.AssemblyProductId = PLL.ProductId

	WHERE	SOLP.HasAssembly = 1 AND PLL.SalesOrderLineId IN
	(
		SELECT	CAST([value] AS INT)
		FROM	STRING_SPLIT(@SalesOrderLineIds, ',')
	)
END

Open in new window

0
This is a related question that Scott helped me with

https://www.experts-exchange.com/questions/29145132/Adding-up-rows.html?headerLink=workspace_answered_questions

And I'm on SQL 2012

I'm running into an issue now that I have more data. You see that #FSS table and Cross Apply? when the #FSS table is empty, I don't get any rows back. I commented that Cross Apply (and fields related to it) and I get rows back.

How can I change it to say... give me rows even if #FSS is empty?
0
Hi guys,
Got a sql server2012 running enterprise evaluation edition.  It’s a free version.
I got license for standard version of 2012 sql

hi i tried to activate through the sql server management studio. entered key and got this error below in pic.. can i ignore that error and go ahead ?
IMG_5639.jpg
or do i need to uninstall enterprise evaluation version.  and install standard version from scratch ? ?
0
Dear Experts,

Are there any housekeeping tips or best practices for an SQL server 2008 R2 (64-bit)?

The server's local hdd of 150GB is full, therefore backup always failed.
0
I have a SQL Server 2008 box that is randomly giving issues with the tempdb file.
At random occurrences the templog file fills up with different transactions executing but then at other times these transactions cause no fill-up.

The only way to bring the templog down when big growth occurs is to manually clear/ kill all open connections / sessions older than two hours that use our front end application that connects to a backend db within SQL Server 2008

tempdb config:
on one drive
 tempdb.....mdf file
 tempdb1....ndf file
 tempdb2...ndf file

templog:
On another drive
templog....ldf file.

Any recommendations?
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.